31.1. INFORMATION_SCHEMA Access Syntax


31.1. INFORMATION_SCHEMA Access Syntax

This section briefly summarizes the syntax for accessing the contents of the INFORMATION_SCHEMA database. See Chapter 20, "Obtaining Database Metadata," for additional detail.

INFORMATION_SCHEMA is a "virtual database" in the sense that it is not stored anywhere on disk. But like any other database, it contains tables, and its tables contain rows and columns that can be accessed by means of SELECT statements.

To retrieve the contents of an INFORMATION_SCHEMA table, you must know what tables are available. This statement displays their names:

 mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES     -> WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'; +---------------------------------------+ | TABLE_NAME                            | +---------------------------------------+ | 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                              | +---------------------------------------+ 

To see the names of the columns in an INFORMATION_SCHEMA table (for example, the CHARACTER_SETS table), use a statement like this:

 mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS     -> WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'     -> AND TABLE_NAME = 'CHARACTER_SETS'; +----------------------+ | COLUMN_NAME          | +----------------------+ | CHARACTER_SET_NAME   | | DEFAULT_COLLATE_NAME | | DESCRIPTION          | | MAXLEN               | +----------------------+ 

The preceding queries actually provide metadata about the metadata database, because they describe some of the structure of INFORMATION_SCHEMA itself. Of course, you can select information about other databases as well. The following statement displays a summary of some of the characteristics of world database tables:

 mysql> SELECT TABLE_NAME, TABLE_ROWS, TABLE_COLLATION     -> FROM INFORMATION_SCHEMA.TABLES     -> WHERE TABLE_SCHEMA = 'world'; +-----------------+------------+-------------------+ | TABLE_NAME      | TABLE_ROWS | TABLE_COLLATION   | +-----------------+------------+-------------------+ | City            |       4079 | latin1_swedish_ci | | Country         |        239 | latin1_swedish_ci | | CountryLanguage |        984 | latin1_swedish_ci | +-----------------+------------+-------------------+ 



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