It can be done with the SHOW FULL TABLES statement. Its Syntax would be as follows −SyntaxSHOW FULL TABLES FROM db_nameHere, db_name is the name of the database from which we want to see the list of tables.ExampleWe are currently using the database named ‘query’ and the MySQL query below will show us the list of tables along with table type from the database named mysql.mysql> SHOW FULL TABLES FROM mysql; +---------------------------+------------+ | Tables_in_mysql           | Table_type | +---------------------------+------------+ | arena                     | BASE TABLE | | ... Read More

It can be done with the SHOW FULL TABLES statement. Its Syntax would be as follows −SyntaxSHOW FULL TABLESExampleIn the following example our current database is ‘query’ hence the statement below will show us the table list along with table type in the result set from this database −mysql> SHOW FULL TABLES; +-----------------------------+------------+ | Tables_in_query             | Table_type | +-----------------------------+------------+ | accounts                    | BASE TABLE | | address                     | BASE TABLE | | cars     ... Read More

With the help of following MySQL query, we can see the list of MySQL database −mysql> SELECT schema_name FROM information_schema.schemata; +--------------------+ | schema_name        | +--------------------+ | information_schema | | gaurav             | | mysql              | | performance_schema | | query              | | query1             | | sys                | | tutorials          | +--------------------+ 8 rows in set (0.00 sec)We can also use WHERE clause with this query as follows −mysql> SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE '%schema' OR schema_name LIKE '%s'; +--------------------+ | schema_name        | +--------------------+ | information_schema | | performance_schema | | sys                | | tutorials          | +--------------------+ 4 rows in set (0.00 sec)

As we know that COALESCE() function returns first non-NULL value from the list of values. The following IF-THEN-ELSE statement is equivalent to COALESCE() function.IF value1 is not NULL THEN output = value1; ELSIF value2 is not NULL THEN output = value2; ELSIF value3 is not NULL THEN output = value3; . . . ELSIF valueN is not NULL THEN output = valueN; ELSE output = NULL; END IF;

As we know that with the help of SHOW DATABASES statement we can see the list of MySQL databases. Similarly, we can use SHOW SCHEMAS as the synonym of SHOW DATABASES to get the list of databases.Examplemysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | gaurav             | | mysql              | | performance_schema | | query              | | query1             | | sys                | | tutorials          | +--------------------+ 8 rows in set (0.07 sec) mysql> SHOW SCHEMAS; +--------------------+ | Database           | +--------------------+ | information_schema | | gaurav             | | mysql              | | performance_schema | | query              | | query1             | | sys                | | tutorials          | +--------------------+ 8 rows in set (0.00 sec)

We can use MySQL COALESCE() function to get the first non-NULL value as output from a list of values. In other words, this function will check all the values until non-null value found. It can take one or more than one argument. It is having the following syntax:COALESCE(value1, value2, …, valueN)ExampleFollowing is an example to demonstrate it −mysql> Select COALESCE(NULL, NULL, NULL, 'Ram', 'Aarav', NULL); +--------------------------------------------------+ | COALESCE(NULL, NULL, NULL, 'Ram', 'Aarav', NULL) | +--------------------------------------------------+ | Ram                                              | +--------------------------------------------------+ 1 row in set (0.00 sec)

If all the values in MySQL COALESCE() function are NULL then it returns NULL as the output. It means that this function does not find any non-NULL value in the list.Examplemysql> Select COALESCE(NULL, NULL, NULL, NULL); +----------------------------------+ | COALESCE(NULL, NULL, NULL, NULL) | +----------------------------------+ |                             NULL | +----------------------------------+ 1 row in set (0.00 sec)

There may be something wrong which can happen to the database server e.g., the server was shutdown unexpectedly, error while writing data to the hard disk, etc. These situations could make the database operate incorrectly and in the worst case, it can be crashed.With the help of CHECK TABLE statement MySQL allows us to check the integrity of database tables. Its syntax would be as follows −CHECK TABLE table_nameHere, table_name is the name of the table.ExampleWe are running this statement for the table Student_info as follows −mysql> CHECK table student_info\G *************************** 1. row ***************************    Table: query.student_info       ... Read More

We can use MySQL absolute value i.e. ABS() function to select records from a table if the absolute value of the difference between two values is greater than a certain number. We are using the data from ‘Marks’ table to demonstrate it for example. Example mysql> Select * from studentmarks where abs(Hindi-English)>10; +--------+-------+---------+------+---------+-----------+ | Name | Hindi | English | Math | Physics | Chemistry | +--------+-------+---------+------+---------+-----------+ | Gaurav | 75 | 86 | 95 | 69 | 85 ... Read More

While working with the database, we have a tendency to do plenty of changes like insert, update and delete data within the table which will cause the physical storage of the table fragment. As a result, the performance of database server is degraded.MySQL provides us with OPTIMIZE TABLE statement that allows you to optimize the table to avoid this defragmenting problem. Its syntax would be as follows −OPTIMIZE TABLE table_nameHere, table_name is the name of the table.ExampleWe are running this statement for the table Student_info as follows −mysql> Optimize table student_info\G *************************** 1. row ***************************    Table: query.student_info       Op: ... Read More