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.



     
    ASPTreeView.com
     
    Evaluation has ШНЗВexpired.
    Info...