Learning About Databases and Tables

But what if you don't know the names of the available databases? And for that matter, how are MySQL Administrator and MySQL Query Browser able to display a list of available databases?

Information about databases, tables, columns, users, privileges, and more, are stored within databases and tables themselves (yes, MySQL uses MySQL to store this information). But these internal tables are generally not accessed directly. Instead, the MySQL SHOW command is used to display this information (information which MySQL then extracts from those internal tables). Look at the following example:

• Input

SHOW DATABASES;

• Output

+--------------------+
| Database           |
+--------------------+
| information_schema |
| crashcourse        |
| mysql              |
| forta              |
| coldfusion         |
| flex               |
| test               |
+--------------------+

• Analysis

SHOW DATABASES; returns a list of available databases. Included in this list might be databases used by MySQL internally (such as mysql and information_schema in this example). Of course, your own list of databases might not look like those shown here.

To obtain a list of tables within a database, use SHOW TABLES;, as seen here:

• Input

SHOW TABLES;

• Output

+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers             |
| orderitems            |
| orders                |
| products              |
| productnotes          |
| vendors               |
+-----------------------+

• Analysis

SHOW TABLES; returns a list of available tables in the currently selected database.

SHOW can also be used to display a table's columns:

• Input

SHOW COLUMNS FROM customers;

• Output

+---------------+-----------+------+-----+---------+----------------+
| Field         | Type      | Null | Key | Default | Extra          |
+---------------+-----------+------+-----+---------+----------------+
| cust_id       | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name     | char(50)  | NO   |     |         |                |
| cust_address  | char(50)  | YES  |     | NULL    |                |
| cust_city     | char(50)  | YES  |     | NULL    |                |
| cust_state    | char(5)   | YES  |     | NULL    |                |
| cust_zip      | char(10)  | YES  |     | NULL    |                |
| cust_country  | char(50)  | YES  |     | NULL    |                |
| cust_contact  | char(50)  | YES  |     | NULL    |                |
| cust_email    | char(255) | YES  |     | NULL    |                |
+---------------+-----------+------+-----+---------+----------------+

• Analysis

SHOW COLUMNS requires that a table name be specified (FROM customers in this example), and returns a row for each field containing the field name, its data type, whether NULL is allowed, key information, default value, and extra information (such as auto_increment for field cust_id).

Note

What Is Auto Increment? Some table columns need unique values. For example, order numbers, employee IDs, or (as in the example just seen) customer IDs. Rather than have to assign unique values manually each time a row is added (and having to keep track of what value was last used), MySQL can automatically assign the next available number for you each time a row is added to a table. This functionality is known as auto increment. If it is needed, it must be part of the table definition used when the table is created using the CREATE statement. We'll look at CREATE in Chapter 21, "Creating and Manipulating Tables."

Tip

The DESCRIBE Statement MySQL supports the use of DESCRIBE as a shortcut for SHOW COLUMNS FROM. In other words, DESCRIBE customers; is a shortcut for SHOW COLUMNS FROM customers;.

Other SHOW statements are supported, too, including

SHOW STATUS, used to display extensive server status information
SHOW CREATE DATABASE and SHOW CREATE TABLE, used to display the MySQL statements used to create specified databases or tables respectively
SHOW GRANTS, used to display security rights granted to users (all users or a specific user)
SHOW ERRORS and SHOW WARNINGS, used to display server error or warning messages

It is worthwhile to note that client applications use these same MySQL commands as you've seen here. Applications that display interactive lists of databases and tables, that allow for the interactive creation and editing of tables, that facilitate data entry and editing, or that allow for user account and rights management, and more, all accomplish what they do using the same MySQL commands that you can execute directly yourself.

Tip

Learning More About SHOW In the mysql command-line utility, execute command HELP SHOW; to display a list of allowed SHOW statements.

Note

New To MySQL 5 MySQL 5 supports a new INFORMATION_SCHEMA command that can be used to obtain and filter schema details.