Mysql's some common SQL statement collation _mysql

Show the existing database by showing

Syntactic:

Show DATABASES [like Wild]

If you use the like wild section, the wild string can be a string that uses the SQL "%" and "_" wildcard characters.

Features: Show databases lists the databases on the MySQL server host.

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

mysql>show databases;

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

| Database |

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

| |

mysql |

| mytest |

| Test  |

| test1

| +----------+

mysql>show databases like ' my% ';

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

| Database (my%) |

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

| mysql   |

| mytest   |

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

You can also get a list of existing databases using the Mysqlshow program.

Creating a database with Create Dabase

Syntactic:

CREATE DATABASE db_name

Function: Create database creates a data base with the given name.

An error occurred if the database already exists.

The database in MySQL is implemented as a directory containing the files in the corresponding database tables. Because the database was initially created without any tables, the CREATE DATABASE statement simply creates a directory below the MySQL data directory.

For example:

Mysql>create database Myfirst;

Then use show databases to observe the effect.

Deleting databases with drop database

Syntactic:

DROP DATABASE [IF EXISTS] Db_name

Function: Drop database deletes all tables and databases in the databases. Be careful to use this command!

The drop database returns the number of files that were deleted from the database directory. Typically, this is 3 times times the number of tables, because each table corresponds to a ". MyD "file, one". Myi "File and a. frm" file.

In MySQL 3.22 or later, you can use the keyword if exists to prevent an error from occurring 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.

To create a database:

Shell> mysqladmin Create Db_name

To delete a database:

shell> mysqladmin Drop Db_name

If the following error occurs:

Copy Code code as follows:

Mysqladmin:connect to server in ' localhost ' failed error: ' Access denied for User: ' Root@localhost ' (Using password:yes)

Create or delete directly in the database directory

Use the above method to create a database, just under the MySQL data directory to create a directory with the same name as the database, also delete the database is to delete this directory.

So, you can do this directly, create or delete a database, or rename the database. This has some meaning for backup and recovery backups.

Using use to choose Database

Syntactic:

Use Db_name

The use Db_name statement tells MySQL that the Db_name database is used as the default database for subsequent queries. The database remains at the end of the session, or another use statement is issued:

mysql> use DB1; Mysql> SELECT Count (*) from mytable;  # selects from db1.mytable mysql> to 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 as:

Mysql> SELECT Count (*) from db1.mytable; Mysql> SELECT Count (*) from db2.mytable;

Since use is also a command for a MySQL client, you can end up with a semicolon at the command line, and the client program can get the result.

Summarize

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

SQL statement:

Create/drop Database,show Databases,use

Program Mysqladmin

Create or delete a directory of databases directly