[TOC]
## 一、 創建數據庫
### 1.創建數據庫語法：`create database [if not exists] db_name [create_specification];`
基本語法創建數據庫 mytest，例：
```sql
mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
| school |
| school_01 |
| shop |
| sys |
+--------------------+
8 rows in set (0.00 sec)
```
#### a、可選項 `if not exists`
創建數據庫時若加上選項 `if not exists` ，如果這個數據庫**沒有存在**就順利創建：
```sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| school_01 |
| shop |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> create database if not exists test2;
Query OK, 1 row affected (0.00 sec)
```
創建數據庫時若加上選項 `if not exists` ，如果**已經存在**這個數據庫就不會創建，并給出一個警告 warning：
```sql
mysql> create database if not exists test2;
Query OK, 1 row affected, 1 warning (0.00 sec)
```
查看給出的警告：
```sql
mysql> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------+
| Note | 1007 | Can't create database 'test2'; database exists |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)
```
創建數據庫時若不加選項 `if not exists` ，如果這個數據庫沒有存在就順利創建；如果已經存在這個數據庫就會創建失敗，并報錯提示：
```sql
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create database test3;
ERROR 1007 (HY000): Can't create database 'test3'; database exists
```
#### b、可選項 `create_specification`
數據庫創建選項 create_specification 有兩個待設置的內容：
1. `character set`：字符集，定義了字符以及字符的編碼。常見的如 latin1、gbk、utf8、utf8mb4 等。
2. `collate`：字符校對規則，字符序，定義了字符的比較規則。它們以其相關的字符集名開始，通常包括一個語言名，并且以 `_ci`（大小寫不敏感）、`_cs`（大小寫敏感）或 `_bin`（二元法，二進制編碼）結束。建議使用 bin 類型，相對更準確。
例，創建數據庫 mytest 時設置 character set ：
```sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| school_01 |
| shop |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> create database mytest character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database mytest;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
```
例，創建數據庫 test2時設置 character set 和 collate ：
```sql
mysql> create database test2 character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> show create database test2;
+----------+---------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------+
| test2 | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
## 二、修改數據庫選項
**基本語法為：** `alter database db_name [alter_specification] `
例如，修改數據庫 test2 的字符集為 utf8 ：
```sql
mysql> show create database test2;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test2 | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database test2 character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database test2;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| test2 | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
```
例如，修改數據庫 test2 的字符集為 gbk 和字符序為 gbk _bin ：
```sql
mysql> alter database test2 character set gbk collate gbk_bin;
Query OK, 1 row affected (0.00 sec)
mysql> show create database test2;
+----------+-------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------+
| test2 | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET gbk COLLATE gbk_bin */ |
+----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
## 三、刪除數據庫
### 1.刪除數據庫語法：`drop database db_name;`
例如，刪除數據庫 mytest ：
```sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
| school |
| school_01 |
| shop |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql> drop database mytest;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| school_01 |
| shop |
| sys |
+--------------------+
7 rows in set (0.00 sec)
```
