Home arrow MySQL arrow Data Definition Language, Part 2

Data Definition Language, Part 2

Studying for the MySQL Certification exam? This article, the second of two parts, covers the remainder of roughly 20 percent of the material that will appear on the exam, and includes several sections with questions and answers covering both articles. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

  1. Data Definition Language, Part 2
  2. 4.12 Exercises
  3. Exercises, Questions 31-60
  4. Exercises, Questions 61-90
  5. Exercises, Questions 91-114
  6. Answers to Exercises, 31-60
  7. Answers to Exercises, 61-90
  8. Answers to Exercises, 91-114
By: Sams Publishing
Rating: starstarstarstarstar / 15
January 26, 2005

print this article



4.11 Using SHOW and DESCRIBE to Review Table Structures

The SELECT statement retrieves the information containedin your databases. You can also ask MySQL to show you information aboutyour databases, such as database and table names or information about the columns or indexes in a table. This section discusses the SHOW and DESCRIBE statements, which provide the following types of information:

DESCRIBE table_name;

You're already familiar with the DESCRIBE statement. Its output format was discussed in the "Introduction" and it has been used in several examples earlier in this study guide.

4.11.1 Listing Database or Table Names

To determine the databases or tables that exist on your server, use the SHOW statement. SHOW is a versatile statement that has several variations for displaying many types of information.

SHOW DATABASES displays a list of the databases that your server manages:

+-------------+ | Database | +-------------+ | menagerie | | mysql | | test | | world | +-------------+

The mysql and test databases are created during MySQL installation, so you're likely to see both of them in the output from the SHOW DATABASES statement. The mysql database contains the grant tables and should always be present because the grant tables contain user account information that the server uses to control access to the databases. The test database will be present unless someone has removed it.

The output of the SHOW DATABASES statement depends on whether you have the SHOW DATABASES privilege. If you have the privilege, the statement shows the names of all existing databases. Otherwise, it shows only those databases to which you have access.

To determine the tables a particular database contains, use SHOW TABLES:

+-----------------+ | Tables_in_world | +-----------------+ | City | | Country | | CountryLanguage | +-----------------+

The FROM clause names the database whose table names you want to determine. With no FROM clause, SHOW TABLES displays the names of the tables in the default database. If there is no default database, an error occurs:

SHOW TABLES;ERROR 1046: No Database Selected

SHOW DATABASES and SHOW TABLES can each take a LIKE 'pattern 'clause (note the quotes). With LIKE, the statement performs a pattern-matching operation and displays information only about databases or tables with names that match the pattern. Pattern matching is discussed in section 6.2, "Using LIKE for Pattern Matching."

mysql> S
+---------------+ | Database (m%) | +---------------+ | menagerie | | mysql | +---------------+ mysql> SHOW TABLES FROM world LIKE '%tr%';
+------------------------+ | Tables_in_world (%tr%) | +------------------------+ | Country | | CountryLanguage | +------------------------+
4.11.2 Getting Table Information

To obtain information about the structure of a given table, use DESCRIBE or one of the forms of the SHOW statement that displays the kind of table information in which you're interested.

Information about a table's columns can be obtained using DESCRIBE. For example:

DESCRIBE CountryLanguage;
+------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | Country | char(3) | | PRI | | | | Language | char(30) | | PRI | | | | IsOfficial | enum('T','F') | | | F | | | Percentage | float(3,1) | | | 0.0 | | +------------+---------------+------+-----+---------+-------+

The format of DESCRIBE was discussed in the "Introduction." DESCRIBE table_name is a synonym for SHOW COLUMNS FROM table_name or SHOW FIELDS FROM table_name. These statements are equivalent:

DESCRIBE CountryLanguage;
SHOW COLUMNS FROM CountryLanguage;
SHOW FIELDS FROM CountryLanguage;

SHOW CREATE TABLE shows the CREATE TABLE statement that corresponds to a table's definition, including its columns, indexes, and any table options the table has:

SHOW CREATE TABLE CountryLanguage\G*************************** 
1. row *************************** Table: CountryLanguage Create Table: CREATE TABLE ´countrylanguage´ ( ´Country´ char(3) NOT NULL default '', ´Language´ char(30) NOT NULL default '', ´IsOfficial´ enum('T','F') NOT NULL default 'F', ´Percentage´ float(3,1) NOT NULL default '0.0', PRIMARY KEY (´Country´,´Language´) ) TYPE=MyISAM

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: