Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 243 of 547
How to use user variables in MySQL LIKE clause?
Using the CONCAT() function, we can work with user variables in LIKE clause. The syntax is as follows.set @anyVariableName='anyValue'; select yourColumnName1, yourColumnName2, yourColumnName3, ...N from yourTableName whereyourColumnName like CONCAT('%', @anyVariableName, '%');To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table UserVariableInLike -> ( -> id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.83 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into UserVariableInLike values(101, 'John', 23); Query OK, 1 row affected (0.23 sec) mysql> ...
Read MoreConcat a string to SELECT * in MySQL?
To concat a string, use the CONCAT() function from MySQL as shown in the below syntaxSELECT CONCAT(yourColumnName1, ’anyConcatenationString’), CONCAT(yourColumnName2, ’anyC oncatenationString’), ....N from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as followsmysql> create table selectConcat -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (1.32 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into selectConcat values(1, 'Carol', 23); Query OK, 1 row affected (0.19 sec) mysql> insert into selectConcat values(2, 'John', 24); ...
Read MoreHow to set max_connections in MySQL Programmatically?
To set max_connections in MySQL programmatically, you can use SET command. The syntax is as follows −SET GLOBAL max_connections=yourIntegerValue;Let us implement the above query to set maximum connections. The query is as follows −mysql> set global max_connections=1000; Query OK, 0 rows affected (0.04 sec)Check maximum connections are set or not, using the show variables command. The query is as follows.mysql> show variables like 'max_connections';The following is the output.+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1000 | +-----------------+-------+ 1 row in set (0.18 sec)
Read MoreHow do you force MySQL LIKE to be case sensitive?
To force MySQL LIKE to be case sensitive with the help of LIKE BINARY, the following is the syntax −select yourColumnName like binary 'anyStringValue' from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table LikeBinaryDemo −> ( −> Name varchar(200) −> ); Query OK, 0 rows affected (0.58 sec)Now you can insert records with small letters to force the MySQL LIKE to be case sensitive −mysql> insert into LikeBinaryDemo values('john'); Query OK, 1 row affected (0.12 sec)Display the records in the table. The query ...
Read MoreMySQL datatype to store month and year only?
You need to store the date as the complete date time rather than storing only month and year. If you declare as a datetime then you can extract the month and year using MONTH() and YEAR() function from MySQL.The syntax is as follows −select MONTH(yourDateTimeColumnName) as anyVariableName1, YEAR(yourDateTimeColumnName) as anyVariableName2 from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OnlyMonthandYear -> ( -> DueDateTime datetime -> ); Query OK, 0 rows affected (0.56 sec)Insert date in the table using insert command. ...
Read MoreHow can I simulate a print statement in MySQL?
To simulate a print statement in MySQL, you can use select statement. The syntax is as follows −SELECT ‘anyStringValue’ as ’ ‘;You can check the above syntax at the MySQL command line client.Case 1To print a string.mysql> select 'HELLO MYSQL' as ' ';Output+-------------+ | | +-------------+ | HELLO MYSQL | +-------------+ 1 row in set (0.00 sec)Case 2a) To print integer, use the following query −mysql> select 23 as ' ';Output+----+ | | +----+ | 23 | +----+ 1 row in set (0.00 sec)b) To print float or double type, use the following ...
Read MoreAvoid placing password on command line with MySQL Utilities?
First you need to reach the location of “my.cnf” with the help of below query for MySQL Utilities. The query is as follows −mysql> select @@datadir;The following is the output that display where “my.conf” is −+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Follow the above path in order to open the “my.cnf” file. The snapshot of “my.cnf” location is as follows −Now you can put the below MySQL Utilities in my.cnf which is as ...
Read MoreHow to resolve the ERROR 1115 (42000): Unknown character set: 'utf8mb4'?
You will get this type of error when your MySQL version is below 5.5.3. This is because “utf8mb4” introduced in MySQL version 5.5.3.Firstly, you need to check the current version. If its less than 5.5.3, then you need to upgrade to solve the above error.Check the current version −mysql> select version();Here, our MySQL version is over 5.5.3 −+-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)Now the same query that gave an error 1115, will display correct result. To check all character set in MySQL now, use the below query.mysql> show character set;The ...
Read MoreHow to subtract 3 hours from a datetime in MySQL?
Subtract 3 hours from DateTime in MySQL, using any of the following ways. The first approach is as follows −Case 1 − Using DATE_ADD()select date_add(yourColumnName, interval -3 hours) from yourTableName;Case 2 − Using DATE_SUB()select date_sub(yourColumnName, interval 3 hours) from yourTableName;Firstly, use now() to get the current date-time −mysql> select now();The following is the output −+---------------------+ | now() | +---------------------+ | 2018-11-30 10:13:23 | +---------------------+ 1 row in set (0.00 sec)DATE_ADDThe query to subtract 3 hours from DateTime is as follows. With date_add, we have set a negative date −mysql> select date_add(now(), interval -3 ...
Read MoreSimple way to toggle a value of an int field in MySQL
To toggle a value of an int field, you can use update command with if(). The syntax is as follows −update yourTableName set yourColumnName = IF(yourColumnName = 0, 1, 0);To understand the above toggle syntax, create a table with some int value. The query to create a table is as follows −mysql> create table ToggleDemo −> ( −> IsOnOrOff int −> ); Query OK, 0 rows affected (0.53 sec)Let us insert int values in the table with the help of insert command. The query is as follows −mysql> insert into ToggleDemo values(1); Query OK, 1 ...
Read More