Shulou(Shulou.com)05/31 Report--

This article focuses on "how to use SQL to operate MySQL database", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use SQL to operate MySQL database.

Begin with this section to formally introduce the various SQL statements. This section describes SQL at the database level and related operations, such as viewing, creating, and deleting.

Display the existing database with SHOW

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, such as:

> show databases

+-+

| | Database |

+-+

| | first |

| | mysql |

| | mytest |

| | test |

| | test1 |

+-+

Mysql > show databases like 'my%'

+-+

| | Database (my%) |

+-+

| | mysql |

| | mytest |

+-+

You can also get a list of existing databases with the mysqlshow program.

Create a database with Create Dabase

Syntax: CREATE DATABASE db_name

Function: CREATE DATABASE creates a database with the given name.

If the database already exists, an error occurs.

The database in MySQL is implemented as a directory containing the files of the tables in the corresponding database. Because the database did not have any tables when it was initially created, the CREATE DATABASE statement simply creates a directory under the MySQL data directory.

For example:

Mysql > create database myfirst

Then show databases was used to observe the effect.

Delete database with DROP DATABASE

Syntax: DROP DATABASE [IF EXISTS] db_name

Function: DROP DATABASE deletes all tables and databases in the database. Use this command carefully!

DROP DATABASE returns the number of files deleted from the database directory. Typically, 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, you can use the keyword IF EXISTS to prevent an error from happening if the database does not exist.

Create and delete using the mysqladmin tool

You can use mysqladmin to create and delete databases in a command line environment.

Create the database:

Shell > mysqladmin create db_name

Delete the database:

Shell > mysqladmin drop db_name

If the following error occurs:

Mysqladmin: connect to server at localhost failed

Error: Access denied for user: (Using password: YES)

To indicate that you need a user who can connect properly, please specify the-u-p option in the same way as described in Section 3.2. You will learn about user authorization in Chapter 7.

Create or delete directly in the database directory

Using the above method to create a database, but under the MySQL data directory to create a directory with the same name as the database, the same deletion of the database is to delete this directory.

So, you can do this directly, create or delete the database, or rename the database. This makes sense for backing up and restoring backups.

Using USE to select 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 is held until the end of the session, or another USE statement is issued:

Mysql > USE db1

Mysql > SELECT count (*) FROM mytable; # selects from db1.mytable

Mysql > USE db2

Mysql > SELECT count (*) FROM mytable; # selects from db2.mytable

If you are not using the user statement, the above example should be written as follows:

Mysql > SELECT count (*) FROM db1.mytable

Mysql > SELECT count (*) FROM db2.mytable

Since use is also a command of a mysql client program, you can leave the semicolon at the end of the command line and the client program can get the results.

Summary

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

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

Program mysqladmin

Create or delete the directory of the database directly

At this point, I believe you have a deeper understanding of "how to use SQL to operate MySQL database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.