Create, select, and delete commands for MySQL Databases

CREATE a DATABASE in mysqk and use create database directly. You can select the DATABASE to use databasename and delete the DATABASE by using the drop command. The following describes their usage in detail.

Create a database:

MySQL starts with a database. We can think of a database as a "bookshelf", and a table as a "book on the shelf ", the data in the table can be understood as "content in the book ". That is to say, the database is a container. After entering the username and password to connect to MySQL, you can use the create database Command to CREATE a new MySQL DATABASE. For example:

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

In this way, a database named "xiaoxiaozi" is created ". In the file system, the data storage area of MySQL represents the MySQL database as a directory. That is to say, the database in the file system is actually represented as "folder ". Therefore, we must be careful when naming databases. The naming rules are the same as those for the operating system's red bean directory name.

For example, in Windows, files and directories cannot contain ",/,:,*,?, <,>, | "These characters are automatically deleted from MySQL database names. In addition, the database name cannot be too long (cannot exceed 64 characters). Generally, unless it is intentionally damaged, it is hard to remember that no one builds a database with such a long name. The names that contain special characters or names that are all composed of numbers or reserved words must be enclosed in reverse quotation marks.

The Code is as follows:

Copy code

Create database success; /* The database name is too long. ERROR 1102 (42000): Incorrect database name 'hangzhou' */ Create database 123456; /* The database name is a pure number and must be enclosed in reverse quotation marks. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '000000' at line 1 */ Create database '20140901 '; /* Create a correct database name and enclose the numbers in reverse quotation marks. Query OK, 1 row affected (0.00 sec) */

The database name cannot be the same. If the name of the created database is the same as that of the existing database, the system prompts that the database already exists and the Creation fails.

The Code is as follows:	Copy code
Create database '20140901 '; /* The first creation is successful. Query OK, 1 row affected (0.01 sec) */ Create database '20140901 '; /* The second creation failed. ERROR 1007 (HY000): Can't create database '000000'; database exists */

How can we avoid this error message when creating a database? There are two methods:

• Use the show databases statement to query existing database names before creating a database to avoid creation failures.
• When creating a database, use the if not exists statement to specify that the database is created only when the database does not exist.

The Code is as follows:

Copy code

/* If not exists */ Create database '20140901 '; /* Database created for the first time. Query OK, 1 row affected (0.00 sec) */ Create database if not exists '123 '; /* If the database does not exist before creation, the SQL Execution is successful, with a warning Query OK, 0 rows affected, 1 warning (0.00 sec) */ /* Show databases example */ Show databases; /* + ------------ + | Database | + ------------ + | 1, 123456 | | Log | | Manager | | Mysql | | Qhcms | | Test | | Xiaoxiaozi | + ------------ + 7 rows in set (0.00 sec) */

Select the required database:

The USE statement selects a database to make it the current database for all transactions.

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

At the same time, we can also tell MySQL which databases are the tables when querying the tables.

 

The Code is as follows:

Copy code

Select Host, Db, User From mysql. db; /* Specifies that the Host, db, and User fields are queried in the Db table of the mysql database. + ---------------------------- + --------- + ------- + | Host | Db | User | + ---------------------------- + --------- + ------- + | % | Test | | % | Test _ % | | 192.168.0.133 | qhcms | cms | | Www. bKjia. c0m | qhcms | cms | | Localhost | log | | Localhost | manager | mambo | | Localhost | qhcms | cms | + ---------------------------- + --------- + ------- + 7 rows in set (0.05 sec) */

The preceding statement is the same as the combination of the following two statements.

The Code is as follows:

Copy code

Use mysql; /* Select mysql database Database changed */ Select Host, Db, User From db; /* Select the Host, db, and User fields in the Db table. + ---------------------------- + --------- + ------- + | Host | Db | User | + ---------------------------- + --------- + ------- + | % | Test | | % | Test _ % | | 192.168.0.133 | qhcms | cms | | Www. bKjia. c0m | qhcms | cms | | Localhost | log | | Localhost | manager | mambo | | Localhost | qhcms | cms | + ---------------------------- + --------- + ------- + 7 rows in set (0.00 sec) */

Delete database:

This is a dangerous action. If you want to use it, you must first confirm that the database is your own and you want to delete it, because once you delete the database, is deleted together with its internal table data. Once deleted, it will be gone, so be sure to be careful.

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

The Code is as follows:

Copy code

Drop database xiaoxiaozi; /* Delete xiaoxiaozi database for the first time Query OK, 0 rows affected (0.00 sec) */ Drop database xiaoxiaozi; /* The second deletion failed because the database does not exist. ERROR 1008 (HY000): Can't drop database 'xiaoxiaozi'; database doesn' t exist */ Drop database if exists xiaoxiaozi; /* When deleting a bucket, check whether the bucket does not exist. If the bucket does not exist, delete the bucket. Query OK, 0 rows affected, 1 warning (0.00 sec) */