SQL Selects Database Names, Tables and Column Names

Query all database names

Alibaba Cloud Simple Application Server:  Anti COVID-19 SME Enablement Program
$300 coupon package for all new SMEs and a $500 coupon for paying customers.

Database, table, column, user, permission and other information are stored in the database and table, but the internal table is not directly accessed. You can use MySQL's show command to display this information (MySQL extracts this information from internal tables). SQL command:

SHOW DATABASES;

Note: this command returns a list of available databases. Included in this list may be databases used internally by mysql.

Select database

When you first connect to the database, no databases are open for you to use. Before you can perform any database operation, you need to select a database and use the use command. The command is as follows (the world database is selected here)

Use database name;

When database changed is displayed, the selection is successful!

Note: the use command does not return any results. Depending on the client used, each form of notification is displayed. You must use the use command to open the database before you can read the data in it.

Query the column names of tables in a database

To get the column names of tables in a database, use the show tables command (you must use the use command to select a database before using this command). The command is as follows (select World Database)

SHOW TABLES;

Note: this command returns the column names of the available tables in the currently selected database.

Query all column names of a table

To display the column names of a table, use the show columns or describe command. The command is as follows (display the city table in the world database)

Show columns from table name;
Describe table name;

Note: a table name is required, which returns a row for each field. The row contains the field name, data type, whether nulls are allowed, key information, default value and other information.

Other show commands:

1. Show status: displays a wide range of server status information
2. Show create database and show create table: displays the creation of a specific database or table
3. Show grants: displays the security permissions of authorized users (all users or specific users)
4. Show errors and show warnings: displays server error or warning messages
5. Help show: displays the allowed show statements