15.1 Exploring with SHOW
The SHOW
command is useful for exploring the
details of databases, tables, indexes, and MySQL.
It's a handy tool when you're
writing new queries, modifying database structure, creating reports,
or understanding how your MySQL server is performing. The
SHOW command isn't part of the
SQL standard and is MySQL-specific. It can be used in several ways:
- SHOW DATABASES
-
Lists the databases that are accessible by the MySQL server. You will
only see those databases that you have access to, unless you have the
SHOW DATABASES privilege; privileges and user
rights are discussed later in this chapter.
- SHOW TABLES
-
Shows the tables in the database, after a database has been selected
with the use command.
- SHOW TABLE STATUS
-
Provides information about all tables in the current database,
including the table type, number of rows, how the rows are stored,
average row length, size of the datafile, next
auto_increment value (if applicable), creation
time, last modification time, and any extra options used with
CREATE TABLE.
- SHOW CREATE TABLE tablename
-
Shows the CREATE TABLE statement that was used to
create the table tablename. The output
always includes any additional information automatically added or
changed by MySQL during the creation process, such as the table type
and character set used.
- SHOW OPEN TABLES
-
Shows which tables the server currently has open and which tables are
locked.
- SHOW COLUMNS FROM tablename
-
Shows the attributes, types of attributes, key information, whether
NULL is permitted, defaults, and other information
for a table tablename. The alias
DESCRIBE table produces the
same output.
- SHOW INDEX FROM tablename
-
Presents the details of all indexes on the table
tablename, including the
PRIMARY KEY. It shows (amongst
other information) what the attributes are that form each index,
whether values in the index uniquely identify rows, how many
different values there are in the index (the cardinality), and the
index data structure used (usually a B-tree).
- SHOW PRIVILEGES
-
Lists the access privileges that can be given or denied to users of
the version of MySQL server that you've installed.
Privileges are discussed in Section 15.8.
- SHOW PROCESSLIST
-
Lists the current MySQL processes (or threads) that are running, and
what query they're carrying out on which database.
- SHOW STATUS
-
Reports details of the MySQL server performance and statistics.
Selected statistics and their use in database tuning is discussed
later in this chapter.
- SHOW TABLE TYPES
-
Lists the possible table types that are available in the version of
the MySQL server that you have installed, and notes alongside each
whether you have compiled-in support for that table type. Table types
are discussed in "Table Types."
- SHOW VARIABLES
-
Reports the values of most MySQL system variables.
- SHOW WARNING and SHOW ERRORS
-
Reports warnings or errors from the last command or statement that
was run on a table.
|
