MySQL/Language/Browsing the databases

INFORMATION_SCHEMA
is a virtual database provided by MySQL 5 and later, that contains metadata about the server and the databases.

You can't modify structure and data of. You can only query the tables.

Many  tables provide the same data you can retrieve with a SHOW statement. While using SHOW commands is faster (the server responds much faster and you type less characters), the  provides a more flexible way to obtain and organize the metadata.

List databases
The INFORMATION_SCHEMA table containing the databases information is SCHEMATA.

The  command line tool (DOS/Unix) can be used instead. You can't show databases if the server has been started with the --skip-all-databases option.

If you don't have the 'SHOW DATABASES' privilege, you'll only see databases on which you have some permissions.

The following SQL commands provide information about the databases located on the current server.

Show all databases: The SCHEMA keywords can be used in place of DATABASES. MySQL doesn't support standard SQL SCHEMAs, so SCHEMA is a synonym of database. It has been added for compatibility with other DBMSs.

Add a filter on the databases names
The LIKE operator here works as in normal SELECTs or DML statements. So you can list all databases whose name starts with 'my':

Add complex filters
You can add more complex filters using the WHERE clause: WHERE clause allows you to use regular expressions, '=', '<' and '>' operators, string functions or other useful expressions to filter the records returned by SHOW DATABASES.

List tables and views
The following SQL commands provide information about the tables and views contained in a database. The INFORMATION_SCHEMA tables containing this information are `TABLES` and `VIEWS`.

Since the following statements provide very little information about views, if you need to get metadata about them you'll probably prefer to query the VIEWS table.

The  command line tool can be used instead.

Show all tables
The 2 forms shown above are equivalent.

Apply a filter
You can apply a filter to the tables names, to show only tables whose name match a pattern. You can use the LIKE operators, as you do in SELECTs or in the DML statements: Also, you can apply a more complex filter to any column returned by the SHOW TABLES command using the WHERE clause: (see below)

Extra info
By default, SHOW TABLES returns only one column containing the name of the table. You can get extra information by using the FULL keyword: This will add a column called `Table_type`. This can have three values: 'BASE TABLE' for tables, 'VIEW' for views and 'SYSTEM VIEW' for special tables created by the server (normally used only INFORMATION_SCHEMA tables).

So you can only list tables: Or, you can only list views:

Show only open tables
You can get a list of the non-temporary tables (not views) which are open in the cache: This command has the same parameters as SHOW TABLES, except for FULL (useless in this case). You can't get this information from the INFORMATION_SCHEMA.

List fields
The following SQL commands provide information about the columns in a table or in a view. The INFORMATION_SCHEMA table containing this information is COLUMNS.

The  command line tool can be used instead.

DESCRIBE
DESC can be used as a shortcut for DESCRIBE.

'filter' can be a column name. If a column name is specified, only that column will be shown. If 'filter' contains the '%' or the '_' characters, it will be evaluated as a LIKE condition. For example, you can list all fields which start with 'my':

EXPLAIN
A synonym is:

SHOW FIELDS
Another synonym is:

SHOW COLUMNS
Another synonym is: FIELDS and COLUMNS are synonyms. EXPLAIN is a synonym of SHOW COLUMNS / FIELDS too, but it doesn't support all of its clauses.

A databases name can be specified both in the form both:

Extra info
Using the FULL keyword, extra info can be retried: the columns' collation, privileges you have on the column and the comment.

List indexes
The following SQL commands provide information about the indexes in a table. Information about keys is contained in the `COLUMNS` table in the INFORMATION_SCHEMA.

The  command line tool can be used instead. The KEYS reserved word can be used as a synonym of INDEX. No other clauses are provided.

Result example:


 * Remark: with phpMyAdmin it's easy to create the same index multiple times, which slows the requests.

To remove an index:

MySQL/Parcourir les bases de données