Get information about MySQL databases and tables

Users may forget the name of a database or table, the structure of a table, or the names of columns. This problem can be solved using MySQL because it supports many statements that provide information about the databases and tables it supports.

The "SHOW DATABASES" query can be used to list all databases managed by MySQL. server. To see which database is currently in use, use the "DATABASE()" function.

Let us understand this query in the following sections -

Query

mysql> SELECT DATABASE();

Output

+---------------------+
| DATABASE()          |
+---------------------+
| databaseInUse       |
+---------------------+

If no database is selected, it will result in the output of "NULL ".

To see which tables the default database contains, you can use the following query -

Query

mysql> SHOW TABLES;

Output

+-----------------------------------+
| Tables_in_databaseInUse           |
+-----------------------------------+
| val1                              |
| val1                              |
+-----------------------------------+

The column names in the output generated by the above query is "Tables_in_databaseInUse" where databaseInUse is the name of the database being used/selected.

If the user wants to know more information about the table structure, he can use the "DESCRIBE" statement. It will display information about the columns of each table -

Query

mysql> DESCRIBE pet;

Output

+---------+-------------+-------+------+-----------+--------+
| Field   | Type        | Null  | Key  | Default   | Extra  |
+---------+-------------+-------+------+-----------+--------+
| name    | varchar(20) | YES   |      | NULL      |        |
| owner   | varchar(20) | YES   |      | NULL      |        |
+---------+-------------+-------+------+-----------+--------+

field represents the column name, 'Type' represents the data type of the column, ' NULL' indicates whether the column can contain NULL values, 'Key' indicates whether the column is indexed, and "default" specifies the default value of the column. "Extra" displays special information about the column. If the column was created with the "AUTO_INCRMENT" option, the value is "auto_increment", not empty.

The above is the detailed content of Get information about MySQL databases and tables. For more information, please follow other related articles on the PHP Chinese website!