To add a day to a DATETIME format value, you can use DATE_ADD() function from MySQL.The syntax is as follows −select date_add(now(), interval 1 day) as anyVariableName;Now you can implement the above syntax in order to add a day to a datetime format.mysql> select date_add(now(), interval 1 day) as Adding1DayDemo;The following is the output −+---------------------+ | Adding1DayDemo | +---------------------+ | 2018-12-07 20:06:59 | +---------------------+ 1 row in set (0.00 sec)If you want to add a day only to a date, you can use curdate() function. The query is as follows −mysql> select date_add(curdate(), interval 1 ... Read More

In order to get all characters before space in MySQL, you can use left() function from the MySQL. The syntax is as follows −select left(yourColumnName, LOCATE(' ', yourColumnName) - 1) as anyVariableName from yourTableName;To understand the above concept, let us create a table.The query to create a table is as follows −mysql> create table AllCharacterBeforeSpace −> ( −> FirstNameAndLastName varchar(200) −> ); Query OK, 0 rows affected (0.51 sec)Now you can insert some records in the table.The query to insert records is as follows −mysql> insert into AllCharacterBeforeSpace values('John Smith'); Query ... Read More

To get today’s date, use in-built function CURDATE(). The CURDATE() gives only current date not time. With that, to get the records for the same day, you can try the following syntax −select yourColumnName1, yourColumnName2, ......, yourColumnNameN, DATE_FORMAT(yourDateColumnName, '%Y-%m-%d') from yourTableName WHERE DATE(yourDateColumnName) = CURDATE();To understand the above concept, let us create a table. The query to create a table is as follows. One of these columns will have datetime datatype to display dates −mysql> create table GmailSignIn −> ( −> UserId int, −> UserName varchar(200), −> DateOfSignIn ... Read More

To create a table in MySQL that matches with another table, use CREATE TABLE command with LIKE operator. The syntax is as follows −create table yourNewTableName like yourOldTableName;The above syntax creates structure of the table.If you want all records then use INSERT INTO…...SELECT *FROM command. The syntax is as follows −insert into yourNewTableName select *from yourOldTableName.I have an old table and some data −mysql> create table WholeWordMatchDemo −> ( −> Words varchar(200) −> ); Query OK, 0 rows affected (0.84 sec)First, we will create a table structure. The query is as ... Read More

To get dates older than 1 week, you can use the following syntax −select *from yourTableName where yourColumnName < now() - interval 1 week;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table DatesOfOneWeek −> ( −> ArrivalTime datetime −> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table −mysql> insert into DatesOfOneWeek values(date_add(now(), interval 2 week)); Query OK, 1 row affected (0.11 sec) mysql> insert into DatesOfOneWeek values('2018-11-04'); Query OK, 1 row affected ... Read More

To implement GROUP BY on two fields and count, let us create a table. The following is the query to create a table −mysql> create table GroupByTwoFieldsDemo    −> (    −> Id int,    −> Name varchar(200)    −> ); Query OK, 0 rows affected (0.53 sec)Let us insert some records in the table −mysql> insert into GroupByTwoFieldsDemo values(1, 'John'); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByTwoFieldsDemo values(10, 'Johnson'); Query OK, 1 row affected (0.16 sec) mysql> insert into GroupByTwoFieldsDemo values(9, 'Carol'); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More

To count distinct values, you can use distinct in aggregate function count().The syntax is as follows −select count(distinct yourColumnName) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table DistinctDemo −> ( −> Name varchar(200) −> ); Query OK, 0 rows affected (0.58 sec)For our example, let us insert duplicate records in the table. The query to insert records is as follows −mysql> insert into DistinctDemo values('John'); Query OK, 1 row affected (0.15 sec) mysql> ... Read More

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 More

To delete under safe mode, you can use the below query −SET SQL_SAFE_UPDATES = 0;To understand the above query, let us create a table. The following is the query to create a table −mysql> create table SafeDeleteDemo    −> (    −> Price int    −> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into SafeDeleteDemo values(100); Query OK, 1 row affected (0.11 sec) mysql> insert into SafeDeleteDemo values(200); Query OK, 1 row affected (0.19 sec) mysql> insert into SafeDeleteDemo ... Read More

You can group month and year with the help of function DATE_FORMAT() in MySQL. The GROUP BY clause is also used.The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, '%m-%Y') from yourTableName GROUP BY MONTH(yourColumnName), YEAR(yourColumnName)DESC;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table GroupMonthAndYearDemo −> ( −> DueDate datetime −> ); Query OK, 0 rows affected (1.49 sec)Insert records in the table using the following query −mysql> insert into GroupMonthAndYearDemo values(now()); Query OK, 1 row affected (0.11 sec) ... Read More