Finding Information About Tables


You aren't always dealing with databases and tables that you have created. Here are some handy commands for helping you explore tables.

To list all of the tables in the default database, run

SHOW TABLES; 


The command should output something like the following:

+-------------------+ | Tables_in_library | +-------------------+ | book              | | person            | | loan              | +-------------------+ 3 rows in set (0.00 sec) 


To view tables in a database other than the default database, use a FROM clause with the name of the database from which you want to view the tables. For example, to show a list of all tables in the test database, run this command:

SHOW TABLES FROM test; 


To view the definition of a table, several tools are at your disposal. To only view information on the columns in the table, use the DESCRIBE command. Syntax for the command is

DESCRIBE [database_name.]table_name; 


To show information on the columns in table book, run

DESCRIBE book; 


The output of the command is quite wide. Run the command yourself and then follow along as the DESCRIBE command is discussed.

The information that DESCRIBE generates has the following meaning:

  • Field The name of the column being described. Arguably, this should be called column rather than field.

  • Type A description of the type and maximum width of the column. The format is the name of the type, followed by the width enclosed in parentheses. Column types and, in part, widths have already been discussed. Note here that some columns for which you did not define a width are shown as having widths. Each column type has a default width and a maximum width. If a column is defined without a width, the default width is assigned. For integer-type columns, the width is the maximum width of the largest number that can be stored in the column. In general, you only need to define explicit widths for CHAR or VARCHAR columns and FLOAT- or DECIMAL-type columns.

  • Null NULL is a value that represents nothing. NULL values are often used to represent cases such as a column never having been filled in or an unknown quality. The author avoids using them, as they require a set of special operators to deal with and test them. By default, columns in MySQL allow NULL values. In the future, in most cases, you will define columns explicitly as NOT NULLdoing so saves a little bit of storage and gives you simpler tables with which to work.

  • Key Key contains information about the indexes on the column. Primary keys are marked PRI. Other values are MUL, indicating that the column is part of an index that allows duplicate values in the column; and UNI, that indicates that the column is part of an index that only allows unique values.

    Note that a column that is part of a unique index might still be flagged as MUL if the column can contain NULL values or is part of a multicolumn UNIQUE index.

  • Default Each column in MySQL has a default value associated with it. Default values are used in cases where an INSERT statement does not specify a value for a column. If you were to insert data into the book table, for example, using this query:

    INSERT book (title) VALUES ('Inferno'); 

    the columns not explicitly mentioned in the query (book_id, author, and cond) would have the default value for the column inserted. Try it and see what the result looks like; run the preceding command and then run

    SELECT * FROM book WHERE author IS NULL; 

    The output should resemble:

    +---------+---------+--------+-----------+ | book_id | title   | author | condition | +---------+---------+--------+-----------+ |       5 | Inferno | NULL   | NULL      | +---------+---------+--------+-----------+ 1 row in set (0.00 sec) 

    Since you don't need this information, you should get rid of it:

    DELETE FROM book WHERE author IS NULL; 

  • Extra Contains extra information on a column, such as if it auto-increments or is unique.



MySQL Phrasebook. Essential Code and Commands
MySQL Phrasebook
ISBN: 0672328399
EAN: 2147483647
Year: 2003
Pages: 130

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net