For this, you can use UNION ALL along with WHERE NOT EXISTS and implement NOT IN to ignore the values already in the table. Use SELECT with UNION ALL to add values not already in the table.Let us first create a table −mysql> create table DemoTable1918    (    Value int NOT NULL AUTO_INCREMENT PRIMARY KEY    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into ... Read More

To get all rows apart from first and last, use subquery along with MIN() and MAX(). Let us first create a table −mysql> create table DemoTable1917    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentCode int,    StudentMarks int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 95); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 96); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 97); Query OK, 1 row affected (0.00 ... Read More

For this, set conditions using MySQL CASE statement −mysql> create table DemoTable1916    (    StudentName varchar(20),    StudentMarks int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1916 values('Chris', 59); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('David', 89); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Sam', 94); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Mike', 75); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Carol', 69); Query OK, 1 row affected ... Read More

To set NOT NULL, use IS NOT NULL and find the value. The syntax is as follows −select if('' is not NULL, 1, 0) as anyAliasName;Here is the working query −mysql> select if('' is not NULL, 1, 0);This will produce the following output −+------------------------+ | if('' is not NULL, 1, 0) | +------------------------+ |                      1 | +------------------------+ 1 row in set (0.00 sec)To understand the above syntax, let us create a table −mysql> create table DemoTable1915    (    Name varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert ... Read More

For this, use SET yourColumnName = NULL as in the below syntax −update yourTableName set yourColumnName=NULL where yourColumnName=yourValue;Let us first create a table −mysql> create table DemoTable1914    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Code varchar(20)    )AUTO_INCREMENT=1001; Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1914(Code) values('John101'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1914(Code) values('234David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1914(Code) values('100_Mike'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select ... Read More

To append 000, use the concept of ZEROFILL. Let us first create a table −mysql> create table DemoTable1913    (    Code int(4) ZEROFILL AUTO_INCREMENT NOT NULL,    PRIMARY KEY(Code)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1913 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(2); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(3); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(4); Query OK, 1 row affected (0.00 sec)Display all records from the table ... Read More

For this, use ORDER BY along with LIMIT. Let us first create a table wherein we have a column with User id, logged in time, and name −mysql> create table DemoTable1911    (    UserId int,    UserLoggedInTime time,    UserName varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1911 values(100, '7:32:00', 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1911 values(101, '5:00:00', 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1911 values(102, '6:10:20', 'Mike'); Query OK, 1 row affected (0.00 ... Read More

For this, you can use CASE WHEN statement. Let us first create a table −mysql> create table DemoTable1910    (    FirstName varchar(20),    Marks int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1910 values('Chris', 45); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1910 values('David', 85); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1910 values('Chris', 55); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1910 values('Chris', 98); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1910 values('David', ... Read More

To return list of databases, the syntax is as follows −select schema_name as anyAliasName from information_schema.schemata;Here is the query to return list of databases in MySQL −mysql> select schema_name as DatabaseName from information_schema.schemata;This will produce the following output −+---------------------------+ | DatabaseName              | +---------------------------+ | mysql                     | | information_schema        | | performance_schema        | | sys                       | | business                  | | sample                    | | hello                     | | test                      | | mybusiness                | | databasesample            | | schemasample              | | universitydatabase        | | education                 | | mydatabase                | | database1                 | | sampledatabase            | | test3                     | | javadatabase2             | | javasampledatabase        | | rdb                       | | onetomanyrelationship     | | webtracker                | | web                       | | commandline               | | hb_student_tracker        | | bothinnodbandmyisam       | | customertracker           | | tracker                   | | demo                      | | customer_tracker_database | | login                     | | onlinebookstore           | | customer-tracker          | | web_tracker               | | instant_app               | | 1233                      | +---------------------------+ 36 rows in set (0.00 sec)

For this, you need to use SET command only once. Let us first create a table −mysql> create table DemoTable1909    (    Id int NOT NULL,    FirstName varchar(20),    LastName varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1909 values(101, 'John', 'Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1909 values(102, 'John', 'Doe'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1909 values(103, 'Adam', 'Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1909 values(104, 'David', 'Miller'); Query ... Read More