MySQL数据库精研之旅第二期：库操作的深度探索

专栏：MySQL数据库成长记

个人主页：手握风云

目录

一、查看数据库

二、创建数据库

2.1. 语法

2.2. 示例

三、字符集编码和校验(排序)规则

3.1. 查看数据库支持的字符集编码

3.2. 查看数据库支持的排序规则

3.3. 不同的字串集与排序规则对数据库的影响

3.4. 查看系统默认字符集和排序规则

四、查看创建语句

4.1. 语法

五、修改数据库

5.1. 语法

六、删除数据库

6.1. 语法

6.2. 注意事项

一、查看数据库

//查看当前已有的数据库
show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

        这里需要注意，databases是复数形式，并且mysql命令行对大小写不敏感。MySQL中每条SQL语句都必须以分号结尾，否则数据库软件就会认为这不是一条完整的语句。如果我们没有写完就按回车，就会另起一行。

mysql> show databases
    ->;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

        已有库里面的“sakila”“world”这些属于测试库，我们可以进行修改或者删除的，“information_schema”“mysql”“performance_schema”“sys”这些都属于系统库，记录的是MySQL自身信息和一些配置项，其中也包含我们的用户名和登录密码。如果删除了，就会导致MySQL无法正常启动，我们就需要再重新安装MySQL。其中set表示结果集，0.00sec表示运行消耗时间为10毫秒以内。

二、创建数据库

2.1. 语法

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] 数据库名称 [create_option]

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
    | COLLATE [=] collation_name
    | ENCRYPTION [=] {'Y' | 'N'}
}

        以上大写部分为关键字；大括号里的内容必须存在；中括号里的内容可有可无；|表示任选其一；IF NOT EXISTS表示如果不存在则创建数据库；CHARACTER SET是指定数据库采⽤的字符集编码；COLLATE是指定数据库字符集的校验规则；ENCRYPTION表示数据库是否加密。

2.2. 示例

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.08 sec)

mysql> create database test3_21
    -> ;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test3_21           |
| world              |
+--------------------+
7 rows in set (0.00 sec)

        如果我们再去创建一个同名称的数据库，就会发出警告，并会告诉我们警告信息该数据库已经存在。

mysql> create database test3_21;
ERROR 1007 (HY000): Can't create database 'test3_21'; database exists

        当SQL语句在以上脚本当中运行时发生了错误，那么SQL语句的执行就会停住。那我们就可以加上一个校验结果，来阻止错误的发生。

mysql> create database if not exists test3_21;
Query OK, 1 row affected, 1 warning (0.00 sec)

        此时它就会提示我们存在一个警告，我们也可以使用SQL语句让它显示出来。

mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1007 | Can't create database 'test3_21'; database exists |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)

        如果说我们在写完一条SQL语句时，发现前面有错误，那我们就可以ctrl+C进行换行重新写。

mysql> shown databases^C
mysql>

三、字符集编码和校验(排序)规则

3.1. 查看数据库支持的字符集编码

show charset;

        对字符集的编码，相当于两个人用不同的语言进行交流，一个人用汉语，另一个人用英语，他们就相当于对相同的内容进行编码。如果用A字符集编码存储数据，用B字符集编码去解析，那么就会出现一堆乱码。

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |

        我们最熟悉的ascii对英语字母进行编码，最大长度为1字节。如果说我们要对中文进行编码，那么gb2312、gbk等进行编码，这两个最大长度为2个字节，相当于可以编写2^16个字符。而其他国家还有其他的语言，比如阿拉伯语、法语等。为了支持全世界的编码，就得对字符长度进行扩大，也就是MySQL8.0支持的默认字符集编码utf8mb4，最长可以用4个字节表示一个字符。

| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |

3.2. 查看数据库支持的排序规则

show collation;

        MySQL8.0默认的排序规则：

| utf8mb4_0900_ai_ci          | utf8mb4  | 255 | Yes     | Yes      |       0 | NO PAD        |

3.3. 不同的字串集与排序规则对数据库的影响

• ai是Accent-insensitive的缩写，表示口声不敏感
• ci是Case-insensitive的缩写表示大小写不敏感
• as是Accent-sensitive的缩写，表示口声敏感
• cs是Case-sensitive的缩写，表示大小写敏感
• bin表示二进制

3.4. 查看系统默认字符集和排序规则

        默认字符集：

mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.04 sec)

        默认排序规则：

mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | gbk_chinese_ci     |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set, 1 warning (0.00 sec)

四、查看创建语句

4.1. 语法

mysql> show create database 数据库名称;

mysql> show create database test3_21;
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database
                        |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| test3_21 | CREATE DATABASE `test3_21` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

        在“Create Database”对应的这一列里面，会出现与注释相同的符号“/**/”，但这个其实是指当前mysql版本大于4.01和 8.0.16时，分别执行对应的语句。

五、修改数据库

5.1. 语法

ALTER {DATABASE | SCHEMA} [db_name]
 alter_option ...
alter_option: {
 [DEFAULT] CHARACTER SET [=] charset_name
 | [DEFAULT] COLLATE [=] collation_name
 | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
 | READ ONLY [=] {DEFAULT | 0 | 1}
}

        对数据库的修改主要是修改数据库的字符集，校验规则。

mysql> alter database test_db character set gbk;
Query OK, 1 row affected (0.01 sec)

mysql> show create database test_db;
+----------+----------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                    |
+----------+----------------------------------------------------------------------------------------------------+
| test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

        但实际上，不建议对数据库进行修改。因为修改之后的编码不同，前面的数据还需要重新整理，效率非常低。大多数情况下还是将原来数据库的资料导出再导入到新创建的数据库里面。

六、删除数据库

6.1. 语法

DROP {DATABASE | SCHEMA} [IF EXISTS] 数据库名称;

mysql> drop database if exists test_db;
Query OK, 0 rows affected (0.04 sec)

        关于删除数据库，有些时候可能会出现一些误删数据库的操作，但也不必担心。因为数据库会定期对数据进行全量备份和增量备份，或者是通过日志进行恢复，或者是对磁盘进行物理恢复。

6.2. 注意事项

• 删除数据库是⼀个危险操作，不要随意删除数据库
• 删除数据库之后，数据库对应的⽬录及⽬录中的所有文件也会被删除
• 删除数据库之后，使⽤show databases；语句查看不到对应的数据库

        在此路径下，C:\ProgramData\MySQL\MySQL Server 8.0\Data，是MySQL的数据目录，里面包含了当前数据库服务管理的所有数据。如果我们把一个Excel文件看成是一个数据库，里面的每一行就可以看作是每一行数据。如果我们想删除一个数据库，那么对应的文件也会被删除。