Exploring Databases and Tables with SHOW and mysqlshow | 173

These are the databases that you can access with the
USE
command; as we explain in
Chapter 9, you can’t see databases for which you have no access privileges unless you have the global SHOW DATABASES
privilege. You can get the same effect from the command line using the mysqlshow program mysqlshow --user=root --password=the_mysql_root_password
You can add a
LIKE
clause to SHOW DATABASES. This is useful only if you have many databases and want a shortlist as output. For example, to see databases beginning with m, type:
mysql> SHOW DATABASES LIKE "m%";
+---------------+
| Database (m) |
+---------------+
| music |
| mysql |
+---------------+
2 rows inset sec)
The syntax of the
LIKE
statement is identical to that in its use in
SELECT
To seethe statement used to create a database, you can use the SHOW CREATE DATA
BASE
statement. For example, to see how music was created, type:
mysql> SHOW CREATE DATABASE music;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| music | CREATE DATABASE music /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row inset sec)
This is perhaps the least exciting
SHOW
statement; it only displays the statement:
CREATE DATABASE music
There are some additional keywords that are enclosed between the comment symbols and
*/
:
40100 DEFAULT CHARACTER SET latin1
These instructions contain MySQL-specific extensions to standard SQL that are unlikely to be understood by other database programs. A database server other than
MySQL would ignore this comment text, and so the syntax is usable by both MySQL
and other database server software. The optional number indicates the minimum version of MySQL that can process this particular instruction—in this case, version older versions of MySQL ignore such instructions. You’ll learn about creating databases in Chapter The SHOW TABLES
statement lists the tables in a database. To check the tables in music
,
type:

Download 4.24 Mb.

Share with your friends: