Advanced SQL

Many of the features shown in this chapter are specific to MySQL's
 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