A
database can be regarded as a container for storing data objects. The data objects here include tables, views, triggers, stored procedures, etc., among which tables are the most basic data objects. Before creating data objects in the
MySQL database, you must first create the database.
1. CREATE DATABASE Create a database
The syntax format is as follows:
CREATE DATABASE [IF NOT EXISTS] <database name>
[[DEFAULT] CHARACTER SET <character set name>] [[DEFAULT] COLLATE <collation rule name>];
The content in [] is optional. The syntax description is as follows:
<database name>: The name of the database to be created. The MySQL data storage area will represent the MySQL database in a directory, so the database name must conform to the operating system's folder naming rules. Note that case insensitive in MySQL.
IF NOT EXISTS: Make a judgment before creating a database, and perform operations only when the database does not currently exist. This option can be used to avoid the error of repeated creation of the database already exists.
[DEFAULT] CHARACTER SET: Specify the default character set of the database.
[DEFAULT] COLLATE: Specify the default collation rules for the character set.
MySQL's character set (CHARACTER) and collation rule (COLLATION) are two different concepts: character set is used to define the way MySQL stores strings. The collation rule defines the way to compare strings and solves the problems of sorting and character grouping.
Character sets and collation rules are in 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 1: 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 sentence 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 two databases with the same name to be created 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 2: 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 statements 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 result is as follows:
mysql> SHOW CREATE DATABASE test_db_char;
+--------------+---------------------------------- -------------------+
| Database | Create Database Create Database |
+--------------+---------------------------------- -------------------+
| test_db_char | CREATE DATABASE `test_db_char` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+---------------------------------- -------------------+
1 row in set (0.05 sec)
In order to prevent the occurrence of character confusion, MySQL sometimes needs to specify the character set explicitly when creating the database; in mainland China, the commonly used character sets are 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, and for Chinese, you can use utf8_general_ci.
gbk can only store characters related to Chinese, which is not universal. The default proofreading rule is gbk_chinese_ci.
2. SHOW DATABASES View or display databases within the scope of current user authority
The syntax format for viewing the database is:
SHOW DATABASES [LIKE'Database name'];
The syntax description is as follows:
The LIKE clause is optional and used to match the specified database name. The LIKE clause can be matched partially or completely.
The database name is surrounded by single quotes''.
Example 1: View all databases
List all the databases that the current user can view:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys ▫ ▫
| world ``
+--------------------+
6 row in set (0.22 sec)
Example 2: Create and view the database
First create a database named test_db:
mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.12 sec)
Then use the SHOW DATABASES statement to display all database names within the scope of authority, as shown below:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys ▫ ▫
| test_db |
| world ``
+--------------------+
7 row in set (0.22 sec)
You see, the database just created has been displayed.
Example 3: Use the LIKE clause
First create three databases, named test_db, db_test, db_test_db.
1) Use the LIKE clause to view the database that exactly matches test_db:
mysql> SHOW DATABASES LIKE'test_db';
+--------------------+
| Database (test_db) |
+--------------------+
| test_db |
+--------------------+
1 row in set (0.03 sec)
2) Use the LIKE clause to view the database that contains test in the name:
mysql> SHOW DATABASES LIKE'%test%';
+--------------------+
| Database (%test%) |
+--------------------+
| db_test |
+--------------------+
| db_test_db |
+--------------------+
| test_db |
+--------------------+
3 row in set (0.03 sec)
3) Use the LIKE clause to view the database whose name starts with db:
mysql> SHOW DATABASES LIKE'db%';
+----------------+
| Database (db%) |
+----------------+
| db_test |
+----------------+
| db_test_db |
+----------------+
2 row in set (0.03 sec)
4) Use the LIKE clause to view the database whose name ends with db:
mysql> SHOW DATABASES LIKE'%db';
+----------------+
| Database (%db) |
+----------------+
| db_test_db |
+----------------+
| test_db |
+----------------+
2 row in set (0.03 sec)
