Use SQL to operate MySQL Databases

This section introduces various SQL statements. This section describes database-level SQL statements, operations such as viewing, creating, and deleting. Use SHOW to display the existing database Syntax: SHOWDATABASES [LIKEwild] If the LIKEwild part is used, the wild string can be a string that uses the "%" and "_" wildcards of SQL. Function: S

This section introduces various SQL statements. This section describes database-level SQL statements, operations such as viewing, creating, and deleting. Use SHOW to display the existing database Syntax: show databases [LIKE wild] If you use the LIKE wild part, the wild string can be a string that uses the "%" and "_" wildcards of SQL. Function: S

This section introduces various SQL statements. This section describes database-level SQL statements, operations such as viewing, creating, and deleting.

SHOW existing databases

Syntax: show databases [LIKE wild]

If you use the LIKE wild part, the wild string can be a string that uses the "%" and "_" wildcards of SQL.

Function: show databases lists the DATABASES on the MySQL server host.

You can try the following example to observe the output result, for example:

Mysql> show databases;

+ ---------- +

| Database |

+ ---------- +

| First |

| Mysql |

| Mytest |

| Test |

| Test1 |

+ ---------- +

Mysql> show databases like 'My % ';

+ ---------------- +

| Database (my %) |

+ ---------------- +

| Mysql |

| Mytest |

+ ---------------- +

You can also use the mysqlshow program to obtain the list of existing databases.

Use Create Dabase to Create a database

Syntax: create database db_name

Function: create database creates a DATABASE with a given name.

If the database already exists, an error occurs.

In MySQL, the database is implemented as a directory containing the files in the corresponding database tables. Because the DATABASE does not have any tables during initial creation, the create database statement only creates a directory under the MySQL data directory.

For example:

Mysql> create database myfirst;

Then use show databases to observe the effect.

Use drop database to delete a DATABASE

Syntax: drop database [if exists] db_name

Function: DROP the DATABASE to delete all tables and databases in the DATABASE. Be careful when using this command!

Drop database returns the number of files deleted from the DATABASE directory. Generally, this is three times the number of tables, because each table corresponds to a ". MYD" file, a ". MYI" file, and a ". frm" file.

In MySQL 3.22 or later versions, you can use the keyword if exists to prevent an error from occurring IF the database does not exist.

Create and delete data using mysqladmin

In the command line environment, you can use mysqladmin to create and delete databases.

Create a database:

Shell> mysqladmin create db_name

Delete database:

Shell> mysqladmin drop db_name

If the following error occurs:

mysqladmin: connect to server at 'localhost' failederror: 'Access denied for user: 'root@localhost' (Using password: YES)'

Indicates that you need a user that can be connected normally. Specify the-u-p option. The method is the same as that described in section 3.2. In chapter 7, you will learn about user authorization.

Directly create or delete a database directory

The above method is used to create a database, but a directory with the same name as the database is created under the MySQL data directory. Deleting a database also deletes this directory.

Therefore, you can directly create or delete a database or rename the database. This is of some significance for backup and recovery.

USE a database

Syntax: USE db_name

The USE db_name statement tells MySQL to USE the db_name database as the default database for subsequent queries. The database persists until the session ends or issues another USE statement:

mysql> USE db1;mysql> SELECT count(*) FROM mytable; # selects from db1.mytablemysql> USE db2;mysql> SELECT count(*) FROM mytable; # selects from db2.mytable

If you are not using the USE statement, the above example should be written:

mysql> SELECT count(*) FROM db1.mytable;mysql> SELECT count(*) FROM db2.mytable;

Because use is also a command of the mysql client program, you can get the result without adding any extra points at the end of the command line.

Summary

This section describes SQL statements and utilities related to database operations, including:

SQL statement: CREATE/DROP DATABASE, SHOW DATABASES, USE

Program mysqladmin

Directly create or delete the database directory