Databases Reference
In-Depth Information
mysql>
SHOW TABLES FROM music;
+-----------------+
| Tables_in_music |
+-----------------+
| album |
| artist |
| played |
| track |
+-----------------+
4 rows in set (0.01 sec)
If you've already selected the
music
database with the
USE music
command, you can use
the shortcut:
mysql>
SHOW TABLES;
+-----------------+
| Tables_in_music |
+-----------------+
| album |
| artist |
| played |
| track |
+-----------------+
4 rows in set (0.01 sec)
You can get a similar result by specifying the database name to the
mysqlshow
program:
$
mysqlshow --user=root --password=
the_mysql_root_password music
As with
SHOW DATABASES
, you can't see tables that you don't have privileges for. This
means you can't see tables in a database you can't access, even if you have the
SHOW
DATABASES
global privilege.
The
SHOW COLUMNS
statement lists the columns in a table. For example, to check the
columns of
track
, type:
mysql>
SHOW COLUMNS FROM track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int(3) | | PRI | 0 | |
| track_name | char(128) | YES | | NULL | |
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| time | decimal(5,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
The output reports all column names, their types and sizes, whether they can be
NULL
,
whether they are part of a key, their default value, and any extra information. Types,
keys,
NULL
values, and defaults are discussed further in Chapter 6. If you haven't already
chosen the
music
database with the
USE
command, then you can add the database name
before the table name, as in
music.track
. Unlike the previous
SHOW
statements, you can
always see all column names if you have access to a table; it doesn't matter that you
