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
MySQL Articles
Page 335 of 355
How to concat Values in MySQL Query and to handle Null values as well?
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 MoreHow to select all records that are 10 minutes within current timestamp in MySQL?
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 MoreHow to search for exact string in MySQL?
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 MoreMYSQL: Can you pull results that match like 3 out of 4 expressions?
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 MoreHow to change collation to utf8_bin in a single line?
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 MoreAdd 30 days to a value in a MySQL table?
To add 30 days to a value in the table, you can use ADDDATE() function with UPDATE command. The syntax is as follows:UPDATE yourTableName SET yourDateColumnName=ADDDATE(yourDateColumnName, INTERVAL 30 DAY);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table Add30DayDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> ShippingDate date, -> PRIMARY KEY(ID) -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into Add30DayDemo(ShippingDate) values('2019-02-04'); Query OK, 1 row affected ...
Read MoreMySQL DATE_FORMAT '%M' is used for short month?
The %M Date Format is not used for displaying short months like Jan for January, Feb for February, etc. You need to use DATE_FORMAT() function with %b format for short month. The syntax is as follows:SELECT DATE_FORMAT(yourColumnName, '%d-%b-%y') 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 DateFormatMonthDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> ShippingDate date, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The ...
Read MoreWhy we mention in MySQL WHERE 1=0?
The condition 1=0 can be used to stop the query from returning any rows. It returns empty set.The syntax is as follows:SELECT *FROM yourTableName WHERE 1=0;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConditionDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into ConditionDemo(Name) values('Larry'); Query OK, 1 row affected (0.10 sec) mysql> ...
Read MoreCan MySQL INT type be non-zero NULL?
You can set the INT column to value NULL.The column INT type a nullable column. The syntax is as follows:INSERT INTO yourTableName(yourIntColumnName) values(NULL);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table nullableIntDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Price int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.80 sec)Insert the record as NULL for a int column ‘Price’. The query is as follows:mysql> insert into nullableIntDemo(Price) values(NULL); Query OK, 1 row affected (0.11 sec) mysql> insert into ...
Read MoreSELECT not null column from two columns in MySQL?
There are lots of ways to select NOT NULL column from two columns. The syntaxes are as follows:Case 1: Use IFNULL() function.The syntax is as follows:SELECT IFNULL(yourColumnName1, yourColumnName2) as anyVariableName from yourTableName;Case 2: Use coalesce() function.The syntax is as follows:SELECT COALESCE(yourColumnName1, yourColumnName2) as anyVariableName from yourTableName;Case 3: Use CASE statement.The syntax is as follows:SELECT CASE WHEN yourColumnName1 IS NOT NULL THEN yourColumnName1 ELSE yourColumnName2 END AS anyVariableName FROM yourTableName;Case 4: Use only IF().The syntax is as follows:SELECT IF (yourColumnName1 ISNULL, yourColumnName2, yourColumnName1) AS NotNULLValue FROM SelectNotNullColumnsDemo;To understand the above syntax, let us create a table. The query to create a ...
Read More