MySQL SHOW, USE, DESCRIBE

The command "SHOW DATABASES" is used to list all the databases that are available in the MySQL server: 

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

In this case, the output shows four databases: information_schema, mysql, performance_schema, and sys. These are system databases that are created automatically when MySQL is installed.

The information_schema database contains information about the MySQL server, such as database metadata, table metadata, and user privileges.

The mysql database contains user account information and other system-level data.

The performance_schema database contains performance-related data, such as statistics on queries and locks.

The sys database is used by MySQL Workbench to provide additional functionality for database administration. 

mysql> SHOW DATABASES \g
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

This is the same MySQL command as before, which shows the list of databases in the current MySQL instance. The only difference is that the "\g" character sequence is used at the end of the command instead of a semicolon (;).

The "\g" sequence can be used to terminate a command and send it to the server for execution. It is an alternative to using a semicolon as a command terminator. When the "\g" sequence is used, the output of the command is displayed immediately after the command is executed, rather than waiting for the server to return control.

In this case, the output is the same as before, showing the four system databases that are available in the MySQL server. 

mysql> USE mysql;
Database changed
mysql>

This is the output of a MySQL command that changes the current database context to the "mysql" database. The "USE" command is used to select a database in MySQL.

The command "USE mysql" is executed, which selects the "mysql" database. The output shows that the database has been changed successfully.

Once a database is selected using the "USE" command, all subsequent commands that operate on tables or data in the database will be executed in the context of that database, unless a different database is selected using the "USE" command again.

mysql> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| ...                       |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.08 sec)

mysql>

The "SHOW TABLES" command is used to display a list of all the tables in a particular database.

The command "SHOW TABLES FROM mysql" is executed, which lists all the tables that exist in the "mysql" database. The output shows a list of table names, each on a separate line, that includes system tables such as "user", "db", "tables_priv", "columns_priv", and others.

These tables store information about user accounts, permissions, databases, and other metadata that are used by the MySQL server to manage and secure the system. 

mysql> DESCRIBE user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| ...                      | ...                               | ...  |     | ....                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.10 sec)

mysql>

The DESCRIBE statement is used to display the structure or schema of a table in a MySQL database. It shows the column names, data types, and any constraints on the columns such as primary key, foreign key, and nullability.

In our example, the DESCRIBE statement is used to display the structure of the user table in the mysql database. It shows that the table has 51 columns, including columns for the username, host, and various privileges. It also shows additional columns for password policies and user attributes.