MySQL/Language/Browsing the databases

From Wikibooks, open books for an open world
< MySQL‎ | Language
Jump to: navigation, search
information_schema database into phpMyAdmin.

INFORMATION_SCHEMA[edit]

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 information_schema. You can only query the tables.

Many information_schema 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 information_schema provides a more flexible way to obtain and organize the metadata.


List databases[edit]

The INFORMATION_SCHEMA table containing the databases information is SCHEMATA.

The mysqlshow 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:

 SHOW 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[edit]

 SHOW DATABASES LIKE 'pattern';

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

 SHOW DATABASES LIKE 'my%';

Add complex filters[edit]

You can add more complex filters using the WHERE clause:

 SHOW DATABASES WHERE conditions;

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[edit]

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 mysqlshow command line tool can be used instead.

Show all tables[edit]

 USE `database`;
 SHOW TABLES;
 
 SHOW TABLES FROM `database`;

The 2 forms shown above are equivalent.

Apply a filter[edit]

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:

 SHOW TABLES LIKE `pattern`;

Also, you can apply a more complex filter to any column returned by the SHOW TABLES command using the WHERE clause:

 SHOW TABLES WHERE condition;

(see below)

Extra info[edit]

By default, SHOW TABLES returns only one column containing the name of the table. You can get extra information by using the FULL keyword:

 SHOW FULL TABLES;

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:

 SHOW FULL TABLES WHERE `Table_type`='BASE TABLE';

Or, you can only list views:

 SHOW FULL TABLES WHERE `Table_type`='VIEW';

Show only open tables[edit]

You can get a list of the non-temporary tables (not views) which are open in the cache:

 SHOW OPEN TABLES;

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[edit]

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 mysqlshow command line tool can be used instead.

DESCRIBE[edit]

 DESCRIBE `table`;
 DESCRIBE `database`.`table`;
 DESCRIBE `table` 'filter';

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':

 DESC `table` 'my%';

EXPLAIN[edit]

A synonym is:

 EXPLAIN `table`;

SHOW FIELDS[edit]

Another synonym is:

 SHOW FIELDS FROM `table`;

SHOW COLUMNS[edit]

Another synonym is:

 SHOW COLUMNS FROM `table`;
 
-- possible clauses:
 SHOW COLUMNS FROM `table` FROM `database`;
 SHOW COLUMNS FROM `table` LIKE 'pattern';
 SHOW COLUMNS FROM `table` WHERE condition;

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

 SHOW COLUMNS FROM `table` FROM `database`;

both:

 SHOW COLUMNS FROM `database`.`table`;

Extra info[edit]

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

Field Type Collation Null Key Default Extra Privileges Comment
... ... ... ... ... ... ... ... ...

List indexes[edit]

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 mysqlshow -k command line tool can be used instead.

 SHOW INDEX FROM `TABLE`;
 SHOW INDEX FROM `TABLE` FROM `databases`;

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

Result example:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
Table1 0 PRIMARY 1 id A 19 NULL NULL BTREE