MySQLi Articles - Page 264 of 388

There are lots of options available to replace NULL values using select statement. You can use CASE statement or IFNULL() or COALESCE()Case 1 − Using IFNULL()The syntax of IFNULL() is as follows −SELECT IFNULL(yourColumnName, ’yourValue’) AS anyVariableName from yourTableName;Case 2 − Using COALESCE()The syntax of COALESCE() is as follows −SELECT COALESCE(yourColumnName, ’yourValue’) AS anyVariableName from yourTableName;Case 3 − Using CASE statementThe syntax of CASE statement.SELECT CASE WHEN yourColumnName IS NULL THEN ‘yourValue’ ELSE yourColumnName END AS anyVariableName FROM yourTableNameTo understand what we discussed above, let us create a table. The query to create a table is as follows −mysql> create ... Read More

In order to work with hex, use the CONV() function to convert between bases. The syntax is as follows −SET anyVariableName = CONV(yourHexValue, 16, 10);To understand the above syntax, let us create a stored procedure. The query to create a stored procedure is as follows −mysql> DELIMITER // mysql> CREATE PROCEDURE SP_HEX_TO_DEC( HEXVALUE VARCHAR(10) )    -> BEGIN    -> DECLARE Decimalvalue INTEGER;    -> SET Decimalvalue = CONV(HEXVALUE, 16, 10);    -> select Decimalvalue;    -> END;    -> // Query OK, 0 rows affected (0.19 sec) mysql> DELIMITER ;The above stored procedure converts the hexadecimal to decimal. As ... Read More

You need to use ALTER command to change collation to utf8_bin. The syntax is as follows:ALTER TABLE yourTableName COLLATE utf8_general_ci;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table CollateDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Age int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.98 sec)Check the DDL of the table. The syntax is as follows:SHOW CREATE TABLE yourTableName;Let us now check the DDL of our table:mysql> show create table CollateDemo;The following is ... Read More

You can use binary to search for exact string in MySQL. The syntax is as follows:SELECT * FROM yourTableName WHERE BINARY yourColumnName = yourStringValue;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ExactSearch    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserId varchar(10),    -> UserName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into ExactSearch(UserId, UserName) values('USER12', 'John'); Query OK, 1 row ... Read More

You can use CASE statement to get the results that match some expressions−SELECT *FROM yourTableName WHERE CASE WHEN yourColumnName1 = yourValue1 THEN 1 ELSE 0 END +    CASE WHEN yourColumnName2 = yourValue2 THEN 1 ELSE 0 END +    CASE WHEN yourColumnName3 = yourValue3 THEN 1 ELSE 0 END +    .    . CASE WHEN yourColumnNameN = yourValueN THEN 1 ELSE 0 END > = 3;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserInformation    -> (    -> Id int NOT NULL AUTO_INCREMENT, ... Read More

You can select all records that are 10 minutes within current timestamp using the following syntax−SELECT *FROM yourTableName WHERE yourColumnName > = DATE_SUB(NOW(), INTERVAL 10 MINUTE);To understand the above syntax, let us create a table. The query to create a table is as follows−mysql> create table users    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserName varchar(20),    -> UserLastseen datetime,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command. The query is as follows−mysql> insert into users(UserName, UserLastseen) values('Larry', '2019-01-15 02−45−00'); Query ... Read More

You can grab the current date with CURDATE() and the day before with MySQL using DATE_SUB() with INTERVAL 1 DAY. The syntax is as follows:SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);The syntax is as follows to get curdate and the day before with date_sub().SELECT *FROM yourTableName WHERE yourColumnName = CURDATE() OR yourColumnName = DATE_SUB(CURDATE(), INTERVAL 1 DAY);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ProductDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ProductName varchar(20),    -> ProductOfferDate datetime,    -> PRIMARY KEY(Id)    -> ... Read More

You can use CONCAT() method to concatenate values while IFNULL() method is used to handle NULL values. The syntax is as follows:SELECT CONCAT('anyStringValue:', IFNULL(yourColumnName, 'anyStringValue’)) AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConcatValues    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> FirstName varchar(20),    -> MiddleName varchar(20),    -> LastName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert ... Read More

The limit of auto_increment integer depends on column data type. Displayed as follows:The data type TINYINT range is 127 The data type UNSIGNED TINYINT range is 255 The data type SMALLINT range is 32767 The data type UNSIGNED SMALLINT range is 65535 The data type MEDIUMINT range is 8388607 The data type UNSIGNED MEDIUMINT range is 16777215 The data type INT range is 2147483647 The data type UNSIGNED INT range is 4294967295 The data type BIGINT range is 9223372036854775807 The data type UNSIGNED BIGINT range is 18446744073709551615Let us take an example of TINYINT. If you will give beyond 127, then ... Read More

The \G modifier gets the result in vertical order. If you use \g modifier, then it won’t affect the result. The \g works likesemi-colon.Let us first create a table. The query to create a table is as follows:mysql> create table DemoOfVertical    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (3.40 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into DemoOfVertical(Name) values('Bob'); Query OK, 1 row affected (0.18 sec) mysql> insert into ... Read More