Home > Articles

Working with MySQL

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account