How to create a database in mysql

Mysql method to create a database: first log in to the MySQL service; then use the create command to create the database. The syntax is [CREATE DATABASE database name].

The database can be regarded as a container that specifically stores data objects. The data objects here include tables, views, triggers, stored procedures, etc., where the table is The most basic data object. Before creating data objects in the MySQL database, you must first create the database.

Recommended courses: MySQL Tutorial.

In MySQL, you can use the CREATE DATABASE statement to create a database. The syntax format is as follows:

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] [[DEFAULT] COLLATE <校对规则名>];

The content in [ ] is optional. The syntax is as follows:

: The name of the created database. The data storage area of ​​MySQL will represent the MySQL database in a directory format, so the database name must comply with the folder naming rules of the operating system. Note that MySQL is not case-sensitive.

IF NOT EXISTS: Make a judgment before creating the database. The operation can only be performed if the database does not currently exist. This option can be used to avoid duplicate creation errors when the database already exists.

[DEFAULT] CHARACTER SET: Specifies the default character set of the database.

[DEFAULT] COLLATE: Specifies the default collation rule for the character set.

MySQL's character set (CHARACTER) and collation rules (COLLATION) are two different concepts: the character set is used to define the way MySQL stores strings, and the collation rules define the way to compare strings and solve sorting and character grouping issues.

Character sets and collation rules have a one-to-many relationship. Each character set corresponds to at least one collation rule. MySQL supports nearly 200 collation rules in 39 character sets.

Example: The simplest statement to create a MySQL database

Create a database named test_db in MySQL. Enter the SQL statement CREATE DATABASE test_db; in the MySQL command line client to create a database. The entered SQL statement and execution results are as follows.

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.12 sec)

If you enter the above statement again, the system will give an error message, as shown below:

mysql> CREATE DATABASE test_db;
ERROR 1007 (HY000): Can't create database 'test_db'; database exists

MySQL does not allow the creation of two databases with the same name on the same system.

If you add the IF NOT EXISTS clause, you can avoid similar errors, as shown below:

mysql> CREATE DATABASE IF NOT EXISTS test_db;
Query OK, 1 row affected (0.12 sec)

Example: Specify the character set and collation rules when creating a MySQL database

Use the MySQL command line tool to create a test database, name it test_db_char, specify its default character set as utf8, and the default collation rule as utf8_chinese_ci (Simplified Chinese, case-insensitive). The input SQL statement and execution results are as follows:

mysql> CREATE DATABASE IF NOT EXISTS test_db_char
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_chinese_ci;
Query OK, 1 row affected (0.03 sec)

At this time, you can use SHOW CREATE DATABASE to view the definition statement of the test_db_char database and find that the specified character set of the database is utf8. The running results are as follows:

mysql> SHOW CREATE DATABASE test_db_char;
+--------------+-----------------------------------------------------+
| Database     | Create Database                                     |
+--------------+-----------------------------------------------------+
| test_db_char | CREATE DATABASE `test_db_char` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+-----------------------------------------------------+
1 row in set (0.05 sec)

To prevent character confusion Sometimes, MySQL needs to explicitly specify the character set when creating the database; in mainland China, commonly used character sets include utf8 and gbk.

utf8 can store all characters in the world and can be used in any country. The default collation rule is utf8_general_ci. For Chinese, utf8_general_ci can be used.

gbk can only store characters related to Chinese and is not globally applicable. The default proofreading rule is gbk_chinese_ci.

The above is the detailed content of How to create a database in mysql. For more information, please follow other related articles on the PHP Chinese website!