- Making the Connection
- Selecting a Database
- Learning About Databases and Tables
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Learning About Databases and Tables
But what if you don’t know the names of the available databases? And for that matter, how are MySQL Administrator and MySQL Query Browser able to display a list of available databases?
Information about databases, tables, columns, users, privileges, and more, are stored within databases and tables themselves (yes, MySQL uses MySQL to store this information). But these internal tables are generally not accessed directly. Instead, the MySQL SHOW command is used to display this information (information which MySQL then extracts from those internal tables). Look at the following example:
/ Input
SHOW DATABASES;
/ Output
+--------------------+ | Database | +--------------------+ | information_schema | | crashcourse | | mysql | | forta | | coldfusion | | flex | | test | +--------------------+
/ Analysis
SHOW DATABASES; returns a list of available databases. Included in this list might be databases used by MySQL internally (such as mysql and information_schema in this example). Of course, your own list of databases might not look like those shown here.
To obtain a list of tables within a database, use SHOW TABLES;, as seen here:
/ Input
SHOW TABLES;
/ Output
+-----------------------+ | Tables_in_crashcourse | +-----------------------+ | customers | | orderitems | | orders | | products | | productnotes | | vendors | +-----------------------+
/ Analysis
SHOW TABLES; returns a list of available tables in the currently selected database.
SHOW can also be used to display a table's columns:
/ Input
SHOW COLUMNS FROM customers;
/ Output
+---------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | | | | cust_address | char(50) | YES | | NULL | | | cust_city | char(50) | YES | | NULL | | | cust_state | char(5) | YES | | NULL | | | cust_zip | char(10) | YES | | NULL | | | cust_country | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | | cust_email | char(255) | YES | | NULL | | +------------ --+-----------+------+-----+---------+----------------+
/ Analysis
SHOW COLUMNS requires that a table name be specified (FROM customers in this example), and returns a row for each field containing the field name, its data type, whether NULL is allowed, key information, default value, and extra information (such as auto_increment for field cust_id).
Other SHOW statements are supported, too, including
SHOW STATUS, used to display extensive server status information
SHOW CREATE DATABASE and SHOW CREATE TABLE, used to display the MySQL statements used to create specified databases or tables respectively
SHOW GRANTS, used to display security rights granted to users (all users or a specific user)
SHOW ERRORS and SHOW WARNINGS, used to display server error or warning messages
It is worthwhile to note that client applications use these same MySQL commands as you’ve seen here. Applications that display interactive lists of databases and tables, that allow for the interactive creation and editing of tables, that facilitate data entry and editing, or that allow for user account and rights management, and more, all accomplish what they do using the same MySQL commands that you can execute directly yourself.