20.3. Using SHOW and DESCRIBE to Obtain Metadata


20.3. Using SHOW and DESCRIBE to Obtain Metadata

MySQL supports a set of SHOW statements that each return one kind of metadata. This section describes a few of them.

  • SHOW DATABASES lists the names of the available databases:

     mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | menagerie          | | mysql              | | test               | | world              | +--------------------+ 

  • SHOW TABLES lists the tables in the default database, or in the named database if a FROM clause is present:

     mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | City            | | Country         | | CountryLanguage | +-----------------+ mysql> SHOW TABLES FROM mysql; +---------------------------+ | Tables_in_mysql           | +---------------------------+ | columns_priv              | | db                        | | func                      | | help_category             | | help_keyword              | | help_relation             | | help_topic                | | host                      | | proc                      | | procs_priv                | | tables_priv               | | time_zone                 | | time_zone_leap_second     | | time_zone_name            | | time_zone_transition      | | time_zone_transition_type | | user                      | +---------------------------+ 

  • SHOW COLUMNS displays column structure information for the table named in the FROM clause:

     mysql> SHOW COLUMNS FROM CountryLanguage; +-------------+---------------+------+-----+---------+-------+ | Field       | Type          | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | CountryCode | char(3)       | NO   | PRI |         |       | | Language    | char(30)      | NO   | PRI |         |       | | IsOfficial  | enum('T','F') | NO   |     | F       |       | | Percentage  | float(4,1)    | NO   |     | 0.0     |       | +-------------+---------------+------+-----+---------+-------+ 

    SHOW COLUMNS takes an optional FULL keyword that causes additional information to be displayed (collation, privileges, and comment):

     mysql> SHOW FULL COLUMNS FROM CountryLanguage\G *************************** 1. row ***************************      Field: CountryCode       Type: char(3)  Collation: latin1_swedish_ci       Null: NO        Key: PRI    Default:       Extra:  Privileges: select,insert,update,references    Comment:  *************************** 2. row ***************************      Field: Language       Type: char(30)  Collation: latin1_swedish_ci       Null: NO        Key: PRI    Default:       Extra:  Privileges: select,insert,update,references    Comment:  *************************** 3. row ***************************      Field: IsOfficial       Type: enum('T','F')  Collation: latin1_swedish_ci       Null: NO        Key:     Default: F      Extra:  Privileges: select,insert,update,references    Comment:  *************************** 4. row ***************************      Field: Percentage       Type: float(4,1)  Collation: NULL       Null: NO        Key:     Default: 0.0      Extra:  Privileges: select,insert,update,references    Comment:  

    SHOW FIELDS is a synonym for SHOW COLUMNS.

  • SHOW KEYS displays information about the indexes that a table has:

     mysql> SHOW KEYS FROM City\G *************************** 1. row ***************************        Table: City   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: ID    Collation: A  Cardinality: 4079     Sub_part: NULL       Packed: NULL         Null:    Index_type: BTREE      Comment:  

    SHOW INDEX is a synonym for SHOW KEYS.

For some SHOW statements, you can give a LIKE clause to perform a pattern-match operation that determines which rows to display. SHOW DATABASES, SHOW TABLES, and SHOW COLUMNS support this feature. For example:

 mysql> SHOW DATABASES LIKE 'm%'; +---------------+ | Database (m%) | +---------------+ | menagerie     | | mysql         | +---------------+ 

SHOW also supports the use of a WHERE clause. As with the LIKE clause, WHERE determines which rows to display, but WHERE is more flexible because you can use any kind of test, not just a pattern match:

 mysql> SHOW COLUMNS FROM Country WHERE `Default` IS NULL; +----------------+-------------+------+-----+---------+-------+ | Field          | Type        | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | IndepYear      | smallint(6) | YES  |     | NULL    |       | | LifeExpectancy | float(3,1)  | YES  |     | NULL    |       | | GNP            | float(10,2) | YES  |     | NULL    |       | | GNPOld         | float(10,2) | YES  |     | NULL    |       | | HeadOfState    | char(60)    | YES  |     | NULL    |       | | Capital        | int(11)     | YES  |     | NULL    |       | +----------------+-------------+------+-----+---------+-------+ 

In the preceding statement, the column name (Default) must be given as a quoted identifier because it is a reserved word.

SHOW statements are available for metadata other than for databases, tables, and columns. For example, SHOW CHARACTER SET displays the available character sets and SHOW COLLATION displays the collations for each character set:

 mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+--------+ | Charset  | Description                 | Default collation   | Maxlen | +----------+-----------------------------+---------------------+--------+ | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 | | dec8     | DEC West European           | dec8_swedish_ci     |      1 | | cp850    | DOS West European           | cp850_general_ci    |      1 | | hp8      | HP West European            | hp8_english_ci      |      1 | | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 | | latin1   | ISO 8859-1 West European    | latin1_swedish_ci   |      1 | | latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 | ... mysql> SHOW COLLATION; +----------------------+----------+-----+---------+----------+---------+ | Collation            | Charset  | Id  | Default | Compiled | Sortlen | +----------------------+----------+-----+---------+----------+---------+ | big5_chinese_ci      | big5     |   1 | Yes     | Yes      |       1 | | big5_bin             | big5     |  84 |         | Yes      |       1 | | dec8_swedish_ci      | dec8     |   3 | Yes     |          |       0 | | dec8_bin             | dec8     |  69 |         |          |       0 | | cp850_general_ci     | cp850    |   4 | Yes     |          |       0 | | cp850_bin            | cp850    |  80 |         |          |       0 | | hp8_english_ci       | hp8      |   6 | Yes     |          |       0 | ... 

DESCRIBE, another metadata-display statement, is equivalent to SHOW COLUMNS. The following two statements display the same information:

 DESCRIBE table_name; SHOW COLUMNS FROM table_name; 

However, whereas SHOW COLUMNS supports the optional FULL keyword, DESCRIBE does not.

Although the contents of INFORMATION_SCHEMA and its tables typically are accessed using SELECT, it's also possible to use SHOW and DESCRIBE with INFORMATION_SCHEMA, just as with any other database. For example, the output from SHOW DATABASES includes INFORMATION_SCHEMA and SHOW TABLES lists its tables:

 mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | menagerie          | | mysql              | | test               | | world              | +--------------------+ mysql> SHOW TABLES FROM INFORMATION_SCHEMA; +---------------------------------------+ | Tables_in_information_schema          | +---------------------------------------+ | SCHEMATA                              | | TABLES                                | | COLUMNS                               | | CHARACTER_SETS                        | | COLLATIONS                            | | COLLATION_CHARACTER_SET_APPLICABILITY | | ROUTINES                              | | STATISTICS                            | | VIEWS                                 | | USER_PRIVILEGES                       | | SCHEMA_PRIVILEGES                     | | TABLE_PRIVILEGES                      | | COLUMN_PRIVILEGES                     | | TABLE_CONSTRAINTS                     | | KEY_COLUMN_USAGE                      | | TRIGGERS                              | +---------------------------------------+ 

DESCRIBE shows the column definitions for any INFORMATION_SCHEMA table:

 mysql> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS; +----------------------+-------------+------+-----+---------+-------+ | Field                | Type        | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+-------+ | CHARACTER_SET_NAME   | varchar(64) | NO   |     |         |       | | DEFAULT_COLLATE_NAME | varchar(64) | NO   |     |         |       | | DESCRIPTION          | varchar(60) | NO   |     |         |       | | MAXLEN               | bigint(3)   | NO   |     | 0       |       | +----------------------+-------------+------+-----+---------+-------+ 



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net