1-MySQL - DDL操作(库/表操作)
before
本篇主要介绍MySQL中的库和表的操作。
库操作
MySQL自带的数据库
先来看MySQL中自带的库有哪些：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
各库解释如下：
-
information_schema，虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等。 -
performance_schema， MySQL 5.5开始新增一个数据库，主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象 。 -
mysql，授权库，主要存储系统用户的权限信息。 -
sys库所有的数据源来自performance_schema，目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解数据库的运行情况。 -
test，MySQL数据库系统自动创建的测试数据库。test库在MySQL中特殊存在，一般部署完mysql后应当删除该库，并规定不能创建以test和test_字符开头的数据库。因为在MySQL中，test库对任意用户都有管理员权限，因此，线上数据库不要用test。如果已有test，添加用户时想要禁止对test库的权限，可以在mysql.db表中添加这个新用户，禁止所有权限，或者删除该表中user为空的记录，并刷新权限select * from mysql.db where db='test' \G ;
创建数据库
创建数据库语法
CREATE DATABASE database_name;
一般的，在MySQL中，关键字等信息推荐大写.......
数据库的命名规范
- 可以由字母、数字、下划线、@、#、$组成。
- 区分大小写，库名不能大写，也不建议以大写字母开头。
- 首字母不能是数字。
- 数据库名具有唯一性，也就是不能重名。
- 应该避开关键字，如select等。
- 不能单独使用数字。
- 最长不超过128位。
- 一般库名应该和业务相关。
- 也不要创建以
test火test_开头的数据库，原因在前面说过了。
关于字符集
在MySQL中，数据库默认的字符集是拉丁语：
mysql> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
但我们用的更多的是utf8或者是utf8mb4，所以我们一般在创建数据库库的时候还需要指定字符集：
mysql> CREATE DATABASE t1 CHARSET utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database t1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| t1 | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
除了utf8和utf8mb4，MySQL还支持其他的字符集，我们来查看MySQL中支持的所有字符集：
mysql> SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
ci是case insensitive即"大小写不敏感", a 和 A 会在字符判断中会被当做一样的。
借此机会我们再来简单说说字符集中的校验规则(排序规则)。
查询字符集校验规则：
SHOW COLLATION;
这里以utf8来简单说说：
utf8_unicode_ci和utf8_general_ci对中英文来说没有实质的差别。utf8_general_ci:：校对速度快，但准确度稍差。utf8_unicode_ci： 准确度高，但校对速度稍慢；若数据库中有德语、法语或者俄语需求，需使用utf8_unicode_ci。utf8_bin：将字符串中的每一个字符用二进制数据存储，区分大小写。
创建大小写敏感的数据库：
CREATE DATABASE database_name CHARSET utf8mb4 COLLATE utf8mb4_bin;
关于utf8和utf8mb4的区别
MySQL在 5.5.3 之后增加了 utf8mb4 字符编码，mb4即 most bytes 4。简单说 utf8mb4 是 utf8 的超集并完全兼容utf8，能够用四个字节存储更多的字符。
但抛开数据库，标准的 UTF-8 字符集编码是可以用 1~4 个字节去编码21位字符，这几乎包含了是世界上所有能看见的语言了。然而在MySQL里实现的utf8最长使用3个字节，也就是只支持到了 Unicode 中的 基本多文本平面（U 0000至U FFFF），包含了控制符、拉丁文，中、日、韩等绝大多数国际字符，但并不是所有，最常见的就算现在手机端常用的表情字符 emoji和一些不常用的汉字，如 “墅” ，这些需要四个字节才能编码出来。
注：QQ里面的内置的表情不算，它是通过特殊映射到的一个gif图片。一般输入法自带的就是。
也就是当你的数据库里要求能够存入这些表情或宽字符时，可以把字段定义为 utf8mb4，同时要注意连接字符集也要设置为utf8mb4，否则在 严格模式 下会出现 Incorrect string value: /xF0/xA1/x8B/xBE/xE5/xA2… for column 'name'这样的错误，非严格模式下此后的数据会被截断。
create database & create schema
你在一些地方，可能会看到如下建库语句：
CREATE SCHEMA t1;
不要迷惑，关于create database和create schema语句，在MySQL中是等价的；下面摘自MySQL8.0官网的解释：
CREATE DATABASEcreates a database with the given name. To use this statement, you need theCREATEprivilege for the database.CREATE SCHEMAis a synonym forCREATE DATABASE.
至于在SQL Server中就有区别了，参考：https://www.cnblogs.com/liaojie970/p/6743986.html
删除
生产中禁止删库~
drop database t1;
修改
alter database t1 charset utf8;
注意，修改字符集时，修改后的字符集一定是原字符集的严格超集。
查看数据库
show create databse database_name; # 查看数据库创建信息
show databases; # 查看所有的数据库
use database_name; # 进入创建的数据库
use database_name; # 切换数据库,且进入某个数据库无法回退
表操作
MySQL中的表你可以理解为是一个有严格规范的excel表格：
表中的id、name、age、gender是字段，其余每一行称为记录。
创建表
基本语法：
-- 语法：
CREATE TABLE 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
注意：
- 在同一张表中，字段名是不能相同。
- 宽度和约束条件可选。
- 字段名和类型是必须的。
示例：
USE school;
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(255) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份证',
intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';
USE语句相当于进入一个目录，然后CREATE TABLE语句相当于创建一个excel表格，然后再这个表格中，针对每个字段有不同的规定和注释，最后再指定这个表格的文件系统(存储引擎)和字符编码。
注意，最后一个字段后无需跟逗号。
建表规范
- 表名小写
- 不能是数字开头
- 注意字符集和存储引擎
- 表名和业务有关
- 选择合适的数据类型
- 每个列都要有注释
- 每个列设置为非空，无法保证非空，用0来填充
查看表结构
首先，我们应该进入某个数据库中use database_name;
show tables; # 查看所有表
show create table score; # 查看指定表的表结构，信息比较全面
show create table score \G # 上同，格式化整理
show create table score \G； # 上同，结尾加分号也行，但会提示No query specified，忘掉它吧
desc score； # 查看表的字段信息
describe score; # 上同，desc为其缩写。
create table t1 like t2; -- 复制结构一样的空表
删除
use school;
drop table t1;
注意，生产中禁止使用！
修改
修改这里无非就是修改表和其中的字段。
修改表操作
- 修改表名：
ALTER TABLE <旧表名> RENAME [TO] <新表名>；
其中TO为可选参数，使用与否均不影响结果。
- 修改表字符集：
ALTER TABLE t1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
- 修改表引擎(慎重使用)：
ALTER TABLE table_name engine=innodb;
修改字段操作
- 修改字段名称和属性：
ALTER TABLE stu CHANGE sgender sg ENUM('m','f','n') NOT NULL DEFAULT 'm' COMMENT '性别';
-- change操作在修改字段名称的同时也可以修改其属性
- 添加字段：
-- 默认添加到最后
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'QQ号';
-- 添加到最前面
ALTER TABLE stu ADD nickname VARCHAR(32) NOT NULL COMMENT '昵称' FIRST;
-- 添加到指定字段之后，注意，并没有添加到指定字段之前的操作
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
- 删除字段(慎重操作)：
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
ALTER TABLE stu DROP nickname;
- 修改字段的属性：
ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL COMMENT '姓名';
-- 注意，该操作会覆盖掉原来的所有属性，所以该操作一定要把原来不变的属性都加上
-- modify操作只用来修改字段属性
- 修改字段字符集：
ALTER TABLE t1 CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
注意，修改表结构的操作，MySQL8.0之前的版本默认是会锁表的，所以，不要在业务高峰期做这些操作，而是选择在晚间进行操作，当然，我们也可以在晚间使用在线的DDL工具来处理这些事情，避免锁表对业务造成影响。
常用的在线DDL工具：
- MySQL的Online。
- pt-osc，推荐使用。
复制表
最后，再来看看复制表相关的操作：
- 复制一份一摸一样的表：
-- 创建一个a2表，表结构跟a1一样，这里复制的a2表跟a1的表结构是一样的，但不会复制a1表中的记录
CREATE TABLE a2 LIKE a1;
-- 复制一张已存在的表，并且包含表中的记录，t4表已存在，且有数据
-- 法1
CREATE TABLE t5 SELECT * FROM t4;
-- 法2
CREATE TABLE t6 LIKE t4;
INSERT INTO t6 SELECT * FROM t4;
- 创建一个表，复制另一张表的指定字段和记录。
-- 注意，自己创建的表字段一定要跟被复制表字段一致
CREATE TABLE a3(
HOST CHAR(60),
USER CHAR(16),
PRIMARY KEY(HOST,USER) -- 注意，key不会复制: 主键、外键和索引，所以这里需要自己建立主键
)
SELECT
HOST,USER
FROM mysql.user;
-- 如果出现自己创建的表字段跟被复制的表字段不一致的情况，那么创建的表就是自己创建的字段和被复制的表的指定字段同时存在
CREATE TABLE a4(
ip CHAR(60) DEFAULT '1.1.1.1',
username CHAR(16) DEFAULT 'root'
-- primary key(ip,username) -- 因为新表会拷贝记录，所以上面两个字段有默认值，但是由于建立联合主键，默认值都一样又建不了，所以，这也是要考虑的地方
)
SELECT HOST,USER
FROM mysql.user;
-- 最终，a4表会有4个字段，ip,username,host,user,即当复制的字段不一致时，被复制的表字段会追加到当前表中
- 创建一个表，复制另一张表的指定字段和记录，但是创建的表字段又想不跟被复制表字段不一致：
CREATE TABLE a5(
ip CHAR(60),
username CHAR(16),
PRIMARY KEY(ip,username)
)
SELECT HOST AS ip,USER AS username
FROM mysql.user;
记录操作(DML)
现在，就要对表中的记录进行增删改这些操作了。
还是那张表：
建表语句
USE school;
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(255) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份证',
intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';
insert
-- 按照指定的字段顺序插入一条数据，也是标准的插入数据格式
INSERT INTO stu(id,sname,sage,sg,sfz,intime) VALUE (1,'张三',18,'m','1100',NOW());
-- 如果按照字段顺序插入的话，可以省略字段，直接插入值
INSERT INTO stu VALUE(2,'张三2',18,'n','1101',NOW());
-- 针对性的插入，根据字段的属性发现，要么是自增长，要么是有默认值，所以这里直接插入必须插入的字段即可
INSERT INTO stu(sname,sage,sfz) VALUE('张三3',18,'1102');
-- 一次插入多条数据
INSERT INTO stu(sname,sage,sfz)
VALUES
('张三4',18,'1103'),
('张三5',18,'1104'),
('张三6',18,'1105'); -- 最后一个括号后不用逗号，直接跟结尾符号很分号即可
-- 可以使用下面语句查询插入的数据
SELECT * FROM stu;
update
首先来说一个不要轻易使用的命令，即对指定字段进行全表更新：
UPDATE stu SET sname='李四';
经过set之后，全表的sname都会变成李四，如果数据量较大，非常影响性能。
根据条件更新：
UPDATE stu SET sname='王五' WHERE id=3;
-- 修改id<3的记录中的sname的值，还可以使用大于符号，不等于符号
UPDATE stu SET sname='赵六' WHERE id<3;
条件选择一般选择具有唯一性的，如果条件不唯一，则所有符合条件的记录都将受影响，如：
UPDATE stu SET sname='赵六' WHERE sg='m';
注意，update必须要加where条件，而且操作要慎重。
扩展，同时更新多个字段如何搞？
-- 使用逗号隔开
UPDATE stu SET sname='赵六', sage=18 WHERE sg='m';
delete
危险命令！
DELETE FROM stu; -- 表中所有的记录都将被逐行删除，性能差，不要用
DELETE FROM stu WHERE id=1;
注意，delete命令删除是逻辑删除，不会释放磁盘空间！
现在我们再说另一条命令：
TRUNCATE TABLE stu;
-- 该命令不支持where条件
truncate命令清空表后会释放磁盘空间。
小结：
delete命令(DML操作)只是在逻辑层面删除了记录，但是占用的磁盘空间并没有释放；另外该表的建表结构还在。truncate命令(DDL操作)清空表的数据页，保留表的建表结构。drop删表操作就是逻辑和磁盘上都会删除。
为了解决delete的缺点，我们采用伪删除来解决，即使用update来代替delete，思路是为表添加一个状态字段，如果有需要删除的，就改下状态，后续的查询也根据状态来查看：
-- 1. 添加状态字段
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1;
-- 2. update替代delete
UPDATE stu SET state=0 WHERE id=3;
-- 3. 业务查询语句
SELECT * FROM stu WHERE state=1;
see also:
第二篇：库相关操作 | 【MySQL】MySQL5.7中sys库的说明 | mysql使用utf8mb4经验吐血总结 | mysql字符集 utf8 和utf8mb4 的区别 | MySQL系列：utf8_bin和utf8_general_ci编码的区别 | Mysql中utf8_unicode_ci、utf8_general_ci有什么区别？ | MySQL新建数据库时utf8_general_ci编码解释 | 关于 mysql.test 数据库 | mysql的test库 | MySQL修改表、字段、库的字符集及字符集说明 | MySQL修改已有表的引擎 | MySQL在线DDL工具pt-osc
