Let us first create a table −mysql> create table DemoTable845(AdmissionDate date); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command −mysql> insert into DemoTable845 values('2018-01-21'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable845 values('2016-12-12'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable845 values('2019-08-05'); Query OK, 1 row affected (0.44 sec) mysql> insert into DemoTable845 values('2019-10-15'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable845;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ | 2018-01-21    | ... Read More

Use SHA2() to convert the MD5 password to SHA256. It calculates the SHA-2 family of hash functions i.e. SHA-224, SHA-256, SHA-384, and SHA-512).Let us first create a table −mysql> create table DemoTable818(UserPassword text); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable818 values(MD5('John_123')); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable818 values(MD5('999Carol@22')); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable818;This will produce the following output −+----------------------------------+ | UserPassword ... Read More

For this, you can use subquery. Let us first create a table −mysql> create table DemoTable844(    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Amount int ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command −mysql> insert into DemoTable844(Amount) values(80); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable844(Amount) values(100); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable844(Amount) values(60); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable844(Amount) values(40); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable844(Amount) values(150); Query OK, 1 row ... Read More

Use IFNULL to check for NULL values and set a value using the SET command. Let us first create a table −mysql> create table DemoTable817(Value int); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable817 values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable817 values(null); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable817 values(20); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable817 values(null); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from ... Read More

Use INFORMATION_SCHEMA.SCHEMA_PRIVILEGES to display all grants for a user −select *from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;Let us implement the above syntax to show all grants for a user −mysql> select *from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;This will produce the following output −+-----------------------------+---------------+--------------------+-------------------------+--------------+ | GRANTEE                     | TABLE_CATALOG | TABLE_SCHEMA       | PRIVILEGE_TYPE          | IS_GRANTABLE | +-----------------------------+---------------+--------------------+-------------------------+--------------+ | 'mysql.sys'@'localhost'     | def           | sys                | TRIGGER                 | NO           ... Read More

Here, we will see an example wherein we are inserting dates and updating them while using the INSERT query.Let us first create a table −mysql> create table DemoTable816(DueDate datetime); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using the insert command. Here is the query to add (minutes/hours/days/months/ years) to date when performing INSERT −mysql> insert into DemoTable816 values(date_add(now(), interval 3 minute)); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable816 values(date_add('2018-01-21 00:00:00', interval 3 Hour)); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable816 values(date_add('2016-11-11 12:40:00', interval 3 Day)); Query OK, ... Read More

Let us first create a table −mysql> create table DemoTable815(Gender BOOLEAN); Query OK, 0 rows affected (0.84 sec)Insert some records in the table using insert command −mysql> insert into DemoTable815 values(true); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable815 values(false); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable815 values(false); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable815 values(true); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable815;This will produce the following output −+--------+ | Gender | +--------+ |      1 ... Read More

Use INTERVAL to update the days for all the dates in a MySQL table column. Let us first create a table −mysql> create table DemoTable814(ArrivalDate date); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable814 values('2018-12-02'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable814 values('2017-03-12'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable814 values('2019-12-23'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable814 values('2016-06-01'); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable814 values('2019-08-03'); Query OK, 1 row affected (0.20 ... Read More

Use GROUP_CONCAT() to perform group concatenation and set a condition. Let us first create a table −mysql> create table DemoTable813(    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserFirstName varchar(100),    UserLastName varchar(100) ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable813(UserFirstName, UserLastName) values('Adam', 'Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable813(UserFirstName, UserLastName) values('Chris', 'Brown'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable813(UserFirstName, UserLastName) values('David', 'Miller'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable813(UserFirstName, UserLastName) values('Carol', 'Taylor'); Query ... Read More

To display results on the basis of keyword, use the LIKE operator as in the following syntax −select *from yourTableName where yourColumnName LIKE '%yourValue%';Let us first create a table −mysql> create table DemoTable812(Comments text); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable812 values('Good Looking'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable812 values('Awesome'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable812 values('Looking Gorgeous'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable812 values('Good Luck'); Query OK, 1 row affected (0.20 sec) ... Read More