Databases and the Catalog






Databases and the Catalog

In MySQL, databases are stored in the catalog table called INFORMATION_SCHEMA.

1. Show the names of all databases.

SELECT   SCHEMA_NAME
FROM     INFORMATION_SCHEMA.SCHEMATA

The result is:

SCHEMA_NAME
------------------
information_schema
mysql
tennis
test

Explanation A catalog table called DATABASES does not exist. Instead, this table is called SCHEMATA. This is somewhat confusing. MySQL is one of the few products that uses the terms and schema interchangeably.

The previous result contains four databases. The first two were created by MySQL during the installation: INFORMATION_SCHEMA and MYSQL. If you remove these databases, MySQL no longer can function. The last two databases were created separately.

The tables of a database can be retrieved by querying the catalog table TABLES and specifying the database name or schema name in the condition.

Figure. Show the names of the tables belonging to the TENNIS database.

SELECT   TABLE_NAME
FROM     INFORMATION_SCHEMA.TABLES
WHERE    TABLE_SCHEMA = 'TENNIS'
ORDER BY 1

The result is:

TABLE_NAME
-----------------
COMMITTEE_MEMBERS
PENALTIES
PLAYERS
TEAMS
MATCHES



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows