version of SQL. For example, MySQL's functions are useful tools for
working with strings, dates and times, and math. Also, we'll show some
ways to tune MySQL in order to improve application performance.
It's important to know how to choose and design indexes for fast querying,
and how to use MySQL's query cache for fast results.
Exploring your database, tables, indexes, and performance with SHOW
More on SELECT queries, including advanced join types, aliases, nested queries,
user variables, and the limitations of MySQL.
More on manipulating data and databases, including finding out about tables
and databases, creating tables with queries, altering tables, more on the
UPDATE and DELETE statements, and bulk loading and exporting data.
Functions and operators in SQL and MySQL
Automatically running queries
MyISAM, InnoDB, and Heap table types
Backup and recovery, and transferring data between database servers
Managing database server users and privileges, and creating users for web
database applications.
Basic tuning of MySQL, including index design, using the query cache,
and, miscellaneous tips for speed.
The SHOW command is useful for exploring the details of databases, tables,
indexes, and MySQL. It's a handy tool when you're writing new queries,
modifying database structure, creating reports, or understanding how your
MySQL server is performing. The SHOW command isn't part of the SQL
standard and is MySQL-specific. It can be used in several ways:
SHOW DATABASES
Lists the databases that are accessible by the MySQL server. You will only
see those databases that you have access to, unless you have the
SHOW DATABASES privilege;
privileges and user rights are discussed later in this chapter.
SHOW TABLES
Shows the tables in the database, after a database has been
selected with the use command.
SHOW TABLE STATUS
Provides information about all tables in the current database,
including the table type, number of rows, how the rows are stored,
average row length, size of the datafile, next auto_increment value if applicable,
creation time, last modification time, and any extra options
used with CREATE TABLE.
SHOW CREATE TABLE tablename
Shows the CREATE TABLE statement that was used to create the table tablename.
The output always includes any additional information automatically added or
changed by MySQL during the creation process, such as the table type and
character set used.
SHOW OPEN TABLES
Shows which tables the server currently has open and which tables are locked.
SHOW COLUMNS FROM tablename
Shows the attributes, types of attributes, key information, whether NULL
is permitted, defaults, and other information for a table tablename.
The alias DESCRIBE table produces the same output.
SHOW INDEX FROM tablename
Presents the details of all indexes on the table tablename, including the
PRIMARY KEY. It shows amongst other information what the attributes are
that form each index, whether values in the index uniquely identify rows,
how many different values there are in the index the cardinality,
and the index data structure used usually a B-tree.
SHOW PRIVILEGES
Lists the access privileges that can be given or denied to users of the
version of MySQL server that you've installed.
SHOW PROCESSLIST
Lists the current MySQL processes or threads that are running,
and what query they're carrying out on which database.
SHOW STATUS
Reports details of the MySQL server performance and statistics.
Selected statistics .
SHOW TABLE TYPES
Lists the possible table types that are available in the version
of the MySQL server that you have installed, and notes alongside
each whether you have compiled-in support for that table type.
SHOW VARIABLES
Reports the values of most MySQL system variables.
SHOW WARNING and SHOW ERRORS
Reports warnings or errors from the last command or statement
that was run on a table
