Databases Reference
In-Depth Information
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 short list as output. For example, to see databases beginning with
m
, type:
mysql>
SHOW DATABASES LIKE "m%";
+---------------+
| Database (m%) |
+---------------+
| music |
| mysql |
+---------------+
2 rows in set (0.00 sec)
The syntax of the
LIKE
statement is identical to that in its use in
SELECT
.
To see the 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 in set (0.00 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 sym-
bols
/*!
and
*/
:
40100 DEFAULT CHARACTER SET latin1
These instructions contain MySQL-specific extensions to standard SQL that are un-
likely 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
40100
indicates the minimum
version of MySQL that can process this particular instruction—in this case, version
4.01.00; older versions of MySQL ignore such instructions. You'll learn about creating
databases in Chapter 6.
The
SHOW TABLES
statement lists the tables in a database. To check the tables in
music
,
type:
