MySQL database creation selection and delete command detailed

To create a database:

Everything in MySQL starts with a database, which we can understand as "bookshelves", which can be understood as "books on the shelves", and the data in the table can be understood as "the contents of the book". This means that the database is a container. After we have entered the username password to connect to MySQL, we can use the CREATE DATABASE command to build a new MySQL database. For example:

The code is as follows	Copy Code
Create Database Xiaoxiaozi; /* Query OK, 1 row affected (0.06 sec) */

This creates a database with the database named "Xiaoxiaozi." In the file system, the MySQL data store will represent the MySQL database in a directory format. In other words, in fact, the database in the file system performance is "folder." So we must be careful when naming the database. Its naming code is consistent with the specification of the Red Bean directory name of the operating system.

For example: In Windows systems, files and directory names are not allowed to have ",/,:,*,?, <,>,|" These characters, which are automatically deleted in the MySQL database name. and the name of the database can not be too long (not more than 64 characters), generally, unless deliberately sabotage, no one built such a long name of the database, it is not good to remember. Names that contain special characters, or names that consist entirely of numbers or reserved words, must be enclosed in inverted quotes.

code is as follows

copy code

Create database Xiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozi; /* database name is too long ERROR 1102 (42000): Incorrect DB name ' Xiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozi ' */ CREATE database 123456; The /* Database name is a pure number and needs to be wrapped in inverted quotes ERROR 1064 (42000): You have a ERROR in your SQL syntax; check the manual that Corresp Onds to your MySQL server version for the right syntax to use near ' 123456 ' at line 1 */ Create database ' 12345 6 '; /* Correctly CREATE database names, wrap pure numbers in inverted quotes Query OK, 1 row Affected (0.00 sec) */

and the name of the database cannot be the same, if you create a library name that duplicates the existing database name, you are prompted that the database already exists and the creation fails.

The code is as follows	Copy Code
Create database ' 123456 '; /* First creation, create success Query OK, 1 row affected (0.01 sec) */ Create database ' 123456 '; /* Second creation, creation failed ERROR 1007 (HY000): Can ' t create database ' 123456 '; Database exists */

So when we create the database, how do we avoid the error message that the library name already exists? There are two ways of doing this:

• Use show databases to query existing database names before creating a database to avoid building failures.
• When creating a database, use the IF NOT EXISTS statement to indicate that it is created only if the database does not exist.

The code is as follows

Copy Code

/*if not exists*/ Create database ' 123456 '; /* The first time you create a database, success Query OK, 1 row Affected (0.00 sec) */ Create database if not exists ' 123456 '; /* If the database is not present to create, execute SQL successfully, there is a warning Query OK, 0 rows affected, 1 Warning (0.00 sec) */ /*show Databases Sample * * show databases; /* +------------+ | Database | +------------+ | 123456 | | Log | | Manager | | MySQL | | Qhcms | | Test | | Xiaoxiaozi | +------------+ 7 Rows in Set (0.00 sec) */

Select the database you want:

Using the USE statement will select a database to make it the current database for all transactions.

The code is as follows	Copy Code
Use Xiaoxiaozi; /* Prompt the database has changed Database changed */

At the same time, we can also tell MySQL what database tables we are in when we query the table.

The code is as follows

Copy Code

Select Host, Db, User from Mysql.db; /* Indicates that the Host,db,user field is queried in the Db table of the MySQL database +----------------------------+---------+-------+ | Host | Db | User | +----------------------------+---------+-------+ | % | Test | | | % | test_% | | | 192.168.0.133 | Qhcms | CMS | | www.111cn.net | Qhcms | CMS | | localhost | Log | Log | | localhost | Manager | Mambo | | localhost | Qhcms | CMS | +----------------------------+---------+-------+ 7 rows in Set (0.05 sec) */

The above statement, combined with the following two statements, is the same

code is as follows

copy code

Use MySQL /* Select MySQL database Database changed */ Select Host, DB, user from DB; /* Select Host, DB, User field in DB table br> +----------------------------+---------+-------+ | Host | Db | User | +----------------------------+---------+-------+ |% | | | | test_% | | | | 192.168.0.133 | qhcms | CMS | | www.111cn.net | qhcms | cms | | localhost | log | log | | localhost | manager | mambo | | localhost | qhcms | cms | +----------------------------+---------+-------+ 7 rows in Set (0.00 sec) */

To delete a database:

This is a dangerous action, if you want to use it, we must first confirm that the database is their own, and really want to delete, because once the database is deleted, it is even the internal table data deleted together. Once deleted is really gone, so be sure to be careful, and then be careful.

In fact, the command to delete the database is very simple drop db database_name; However, to delete a database, if it does not exist, the system will be an error, this time we can use if exists to determine whether the database exists.

code is as follows

copy code

Drop Database Xiaoxiaozi; /* Deletes the Xiaoxiaozi database for the first time Query OK, 0 rows Affected (0.00 sec) */ Drop database Xiaoxiaozi; /* Second The deletion failed because the database no longer exists ERROR 1008 (HY000): Can ' t drop database ' Xiaoxiaozi '; database doesn ' t exist */ Drop Data Base if exists Xiaoxiaozi; When /* is deleted, the first decision to save does not exist, if there is no execution delete Query OK, 0 rows affected, 1 Warning (0.00 sec) */