Managing & Using MySQL, 2nd Edition

Table Management

You should now feel comfortable connecting to a database on a MySQL server. For the rest of the chapter, you can use either the test database that comes with MySQL or your own play database. Using the SHOW command, you can display a list of tables in the current database the same way you used it to show databases. In a brand new installation, the test database has no tables. The following shows the output of the SHOW TABLES command when connected to the mysql system database:

mysql> USE mysql;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)

These are the six system tables MySQL requires to do its work. To see what one of these tables looks like, you can use the DESCRIBE command:

mysql> DESCRIBE db; +-----------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------------+------+-----+---------+-------+ | Host | char(60) binary | | PRI | | | | Db | char(64) binary | | PRI | | | | User | char(16) binary | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') ...