To list logged-in MySQL users, you can use any of the following two methods −First MethodUse INFORMATION_SCHEMA.PROCESSLISTselect *from INFORMATION_SCHEMA.PROCESSLIST;Second MethodYou can use SHOW PROCESSLIST command as well. Following is the syntax −SHOW PROCESSLIST;Let us implement the above syntaxes in order to list logged in MySQL users −mysql> select *from information_schema.processlist;This will produce the following output -+----+-----------------+-----------------+------+---------+--------+-----------------------------+---------------------------------------------+ | ID | USER            | HOST            | DB   | COMMAND | TIME   | STATE                       | INFO           ... Read More

For this, you can use custom IF() and set a value whenever 0 appears.Let us first create a table −mysql> create table DemoTable749 (Value int); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable749 values(10); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable749 values(0); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable749 values(769); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable749 values(0); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable749 values(78); Query OK, 1 row affected (0.14 sec) ... Read More

To extract only the day instead of the entire date, you need to use DAYOFMONTH() function from MySQL.Let us first create a table −mysql> create table DemoTable747 (DueDate datetime); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable747 values('2019-01-31') ; Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable747 values('2018-12-01'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable747 values('2017-09-14'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable747 values('2016-07-21'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select ... Read More

Let us first create a table −mysql> create table DemoTable746 (    Number int ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable746 values(100); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable746 values(200); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable746 values(300); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable746 values(400); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable746;This will produce the following output -+--------+ | Number | +--------+ ... Read More

To check for NULL, use the IS NULL. For empty values, you need to check with an empty string. We will now see an example.Let us first create a table −mysql> create table DemoTable691(    PlayerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    PlayerName varchar(100),    PlayerScore int ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable691(PlayerName, PlayerScore) values('Robert', 56); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable691(PlayerName, PlayerScore) values('David', 89); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable691(PlayerName, PlayerScore) values('', 98); Query ... Read More

Let us first see a case wherein we use “create table table” while creating a table. An error will arise −mysql> create table table(    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar(100) );This will produce the following output i.e. error −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table(    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar(100) )' at line 1As you can see above, the word “table” is a reserved keyword, and we ... Read More

To select records that begin with a specific value, you need to use LIKE operator. Let us first create a table −mysql> create table DemoTable690(    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserValue varchar(100) ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable690(UserValue) values('567890'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable690(UserValue) values('789032'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable690(UserValue) values('567342'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable690(UserValue) values('890678'); Query OK, 1 row affected (0.16 sec)Display ... Read More

This kind of errors arise when you have used incorrect syntax. Let us see an example wherein we have created a table and the same error “1054” arise.Here’s the table −mysql> create table DemoTable689(    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserName varchar(100),    UserLoginDate date(100) NOT NULL );This will produce the following output i.e. an error for incorrect syntax usage −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(100) NOT NULL )' at line 5Let us now ... Read More

To find missing value between two MySQL tables, use NOT IN. Let us first create a table −mysql> create table DemoTable1(Value int); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(1); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1 values(2); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable1 values(5); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1 values(6); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1 values(8); Query OK, 1 row affected (0.16 sec)Display all records from ... Read More

Let us first create a table −mysql> create table DemoTable1(Name varchar(100)); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values('John'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1 values('Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1 values('Robert'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+--------+ | Name | +--------+ | John | | Chris | | Robert | +--------+ 3 ... Read More