【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day03
【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day03
第三天的内容主要是：SQL语句（DML/DDL）
目标：了解MySQL的DML语句的支持范围，以及各种DML使用的注意事项；了解DDL的种类，以及各种DDL的机制对执行时间、复制等的影响。
一、DDL
1、DDL的定义
DDL是Data Definition Language的缩写。
通过DDL语句定义不同的数据段、数据库、表、列、索引等数据库对象的元数据信息。通常由数据库管理员（DBA）使用。主要语法有CREATE, ALTER, DROP。主要作用的对象有database， table， view， index， column， event， trigger等。
2、DATABASE级DDL -- 原子的DDL
原子的 DDL
一个 DDL 操作要么执行成功，要么执行都不成功
不可能存在元数据和数据文件不一致的状态
MySQL 5.X 不支持原子的 DDL
尽量保证 DDL 执行过程不出现系统崩溃
万一遇到 DDL 崩溃带来的不一致，需要人工介入，甚至需要重建库
MySQL 8.0 支持原子的 DDL
更稳定更安全
推荐使用
DATABASE级DDL注意点：
数据库名需保证全局唯一
字符集的选择：utf8 VS utf8mb4
utf8 实际上就是 utf8mb3 ，支持编码最大字符长度为 3 字节； utf8mb4 是 4 个字节， utf8mb4 是utf8 的超集，能够存储 Emoji 表情，生僻的汉字等
字符集选择优先级：表 > 库 > 系统
MySQL 8.0 默认字符集为 utf8mb4
ALTER DATABASE 语句影响整个数据库下面的新增表，谨慎操作
DROP DATABASE 同时会删除库下所有的表，属于高危操作
可以考虑先 DROP 每个 Table ，最后 DROP DATABASE
3、TABLE级DDL
创建
CREATE DATABASE IF NOT EXISTS library DEFAULT CHARACTER SET = utf8
修改属性
ALTER DATABASE library CHARACTER SET = utf8mb4
删除
DROP DATABASE library
查看
SHOW DATABASES
SHOW CREATE DATABASE library
3.1 TABLE级DDL背景
表的类型
普通表 / 分区表 / 临时表
RDS for MySQL 默认使用 InnoDB 存储引擎
也支持其它社区版提供的引擎，但是不建议使用
注意表名大小写敏感参数 lower_case_table_names
对表的操作需要指定数据库和表，否则默认使用当前数据库
显示切换数据库 USE library；，访问 authors 表
或者通过 library.authors 引用
DDL 语句也会记录到 Binlog 中
采用 statement 格式
3.2 TABLE级DDL--CREATE
3.3 TABLE级DDL--CREATE之表拷贝
克隆表结构
CREATE TABLE authors_clone LIKE authors;
只拷贝表定义，不拷贝数据
拷贝表数据
CREATE TABLE authors_copy SELECT * FROM authors;
即拷贝表结构，也拷贝数据
3.4 TABLE级DDL--ALTER概述
当现有表的结构不满足业务需求时，需要对表结构进行调整
修改前需要重点评估 ALTER 对现有业务的影响?
ALTER 语句涉及的表有多大?
ALTER 语句需要运行多久?
当前的系统负载有多高，能否支持 ALTER TABLE ?
ALTER 语句会不会影响 DML ?
ALTER 语句通常涉及到表元数据（和表记录）的修改
有的 ALTER 只修改元数据
有的 ALTER 也需要修改表数据
影响到修改表的执行复杂度
3.5 TABLE级DDL--ALTER不改数据
目前一般有以下 ALTER 操作是指修改元数据不修改表数据
RENAME TABLE （ALTER TABLE ... RENAME TO...）
SET DEFAULT
DROP DEFAULT
MODIFY COLUMN
CHANGE COLUMN （虚拟列生成算法）
Change index option
ADD virtual column, DROP virtual column
ADD COLUMN (非生成列, 8.0 )
其它操作，一般都涉及数据的修改
3.6 TABLE级DDL--ALTER的算法
ALTER TABLE..., ALGORITHM = DEFAULT | COPY | INPLACE | INSTANT;
如果不指定，或者指定DEFAULT，会自动选择最合适的算法
算法优先级INSTANT > INPLACE > COPY
算法间的差异如下图所示：
3.7 TABLE级DDL--ALTER改字段
加字段
ALTER TABLE authors ADD COLUMN title varchar(20)AFTER address;
新字段可以指定位置
改字段
ALTER TABLE authors CHANGE COLUMN status new_status VARCHAR(20) DEFAULT 'zzzzzzzz';
很可能开销不大
删字段
ALTER TABLE authors DROP COLUMN title, DROP COLUMN new_status;
总是个大开销的操作
3.8 TABLE级DDL--ALTER改索引
加索引
RLATER TABLE authors ADD INDEX (phone_number);
CREATE INDEX phone_number_idx ON authors(phone_number);
删索引
ALTER TABLE authors DROP INDEX phone_number;
DROP INDEX phone_number_idx ON authors;
需要的考量
索引会加大 DML 的开销
增加合适的索引加速查询
不用的索引可以删除
添加或者修改主键索引的开销会很大
8.0 上如何更好的评估索引—— INVISIBLE INDEX;
ALTER TABLE authors ADD INDEX (phone_number) INVISIBLE;
ALTER TABLE authors ADD INDEX (phone_number) VISIBLE;
3.9 TABLE级DDL--TRUNCATE
语法
TRUNCATE TABLE authors;
TRUNCATE TABLE 被看做是 DDL，而不是 DML
本质上它删除表，再重建一张新表
清空表内所有的行，重置 AUTO_INCREMENT
版本间行为差异;
5.6/5.7 ，崩溃之后，操作一般是重做到提交
8.0 根据操作是否成功决定是提交还是回滚
3.10 TABLE级DDL--DROP
语法
DROP TABLE [IF EXISTS] authors;
DROP TABLE [IF EXISTS] authors, books;
DROP TEMPORARY TABLE ...;
‘IF EXISTS' 能够避免表不存在的报错
谨慎使用删表操作，数据将无法轻易找回
4）其他常见DDL
4.1 其他常见DDL--VIEW
视图（VIEW）是关联查询的虚表
定义会持久化，只和创建时的表快照相关
其查询出来的数据仍在原表中
原表可以是普通表或临时表
比如书籍价格的视图
CREATE VIEW books_price_view AS SELECT name, price FROM books;
可以看到，上图中原表的数据更新了，所创建的视图数据也会相应的更新！
4.2 其他常见DDL--TRIGGER
触发器（Trigger）可以定义某个表上某个事件的关联操作
同一个表上触发器的类型和个数没有限制
可以定义触发器发生的顺序
常用的触发事件包括 INSERT / DELETE / UPDATE
比如更新 authors 后同步更新 books 表
DROP TRIGGER IF EXISTS auto_update_books_trig; DELIMITER $$ CREATE TRIGGER auto_update_books_trig AFTER UPDATE ON library.authors FOR EACH ROW BEGIN IF (Old.first_name <> New.first_name or Old.last_name <> New.last_name) THEN UPDATE library.books SET first_name=New.first_name, last_name=New.last_name WHERE first_name=Old.first_name AND last_name=Old.last_name; END IF; END$$ DELIMITER; SHOW TRIGGERS;
4.3 其他常见DDL--PROCEDURE
存储过程（Procedure）支持整合和抽取业务逻辑到一个函数执行
支持参数传入传出
执行使用CALL procedure_name();
案例：对一定库存的书籍进行打折
DROP PROCEDURE IF EXISTS discount_books_proc; DELIMITER $$ CREATE PROCEDURE discount_books_proc(IN max_stock INT, IN discount FLOAT) BEGIN UPDATE library.books SET price=price*discount WHERE stock>max_stock; END$$ DELIMITER;
4.5 其他常见DDL--EVENT
定时器（Event）支持指定任务的定时调度
可以定义定时器发生时间，频率，有效期
可以定义定时器，但是先不让它执行——设置为 DISABLE
定时器不支持参数传递，但是可以调用存储过程
可以使用 ALTER EVENT 来修改定时器的定义
需设置 event_scheduler=on; 来开启所有生效的定时器
案例：每周对库存超过 10 本的书籍打 9 折
DROP EVENT IF EXISTS discount_books_weekly_event; CREATE EVENT discount_books_weekly_event ON SCHEDULE EVERY 1 WEEK ON COMPLETION PRESERVE DISABLE DO CALL discount_books_proc(10,0.9); ALTER EVENT discount_books_weekly_event ON COMPLETION PRESERVE ENABLE; SHOW EVENTS;
二、DML
1、DML的定义
A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying
(updating) data in a database. A DML is often a sublanguage of a broader database language such as SOL, with the DML comprising
some of the operators in the language."1l Read-only selecting of data is sometimes distinguished as being part of a separate data
query language (DQL), but it is closely related and sometimes also considered a component of a DML; some operators may perform
both selecting (reading) and writing.
一一摘自维基百科；Data manipulation language;
DML 主要涉及对用户数据的增删改;
有些 DML 语句会涉及到数据的查询加修改;
MySQL 的 DML 主要包括 INSERT / UPDATE / DELETE 三类;
DML 语句都要记录 binlog，针对每一行的修改;
2、INSERT
2.1 TABLE 级的 DML——INSERT 概述
INSERT 语句插入新的记录到指定表当中
INSERT ... VALUES / INSERT ... SET 插入指定的数据
INSERT ... SELECT 插入查询出来的数据
INSERT ... ON DUPLICATE KEY UPDATE 在唯一索引或者主键索引冲突时可以更新原有行
插入数值的限制和转换，基于 SQL_MODE 不做检查
插入 NULL 值到 NOT NULL 字段，数值一般转换成字段的默认值
插入数值型范围之外的值，会截断到最近的范围值
插入比如 '10.34 a' 到数值字段，非数值部分会被截断
插入字符串类型，如果长度超过，会被截断到合理长度
...
2.2 TABLE 级的 DML——INSERT 概述
插入单条记录
INSERT INTO authors VALUES('John', 'Smith', 'Male', 30, 'Room X, Building Y', '12345678901')；
插入多条记录
INSERT INTO authors VALUES('Robert, "White', 'Male', 33, 'Room U, Building I', '12345678903'),
("Linda', "White', 'Female', 36, 'Room P, Building O', '12345678904');
插入查询结果集
CREATE TABLE authors_bak LIKE authors; INSERT INTO authors_bak SELECT * FROM authors;
重复主键或唯一键执行更新操作
INSERT INTO authors VALUES('John', 'Smith', 'Male', 30, 'Room X, Building Y', '12345678901') ON
DUPLICATE KEY UPDATE age = age + 5;
替换或插入
REPLACE INTO ...
REPLACE INTO authors VALUES('Robert', "White', 'Male', 33, 'Room U, Building I', "86-12345678903'),
"Linda", "White', 'Female', 36, 'Room P, Building O', '86-12345678904');
2.3 TABLE 级的 DML——INSERT 与AUTO_INCREMENT
对于使用了 AUTO_INCREMENT 自增字段作为主键索引的表，它和 DML 的交互需要仔细考虑
AUTO INCREMENT 自动递增字段可以建表的时候指定也可以动态添加
ALTER TABLE authors ADD COLUMN id INT AUTO_INCREMENT FIRST,ADD PRIMARY KEY(id), DROP PRIMARY KEY
建表时要考虑可能插入的数据量，避免字段值越界导致无法插入
INSERT 可以不需要指定该字段的值，它会自动递增生成，保证唯一
如果 INSERT 指定该字段的值，则之后的 INSERT 会从表内该字段的最大值继续递增
INSERT INTO ... SELECT 会产生空洞，但不影响唯一性
3、UPDATE
3.1 TABLE 级的 DML——UPDATE概述
UPDATE 用来更新表内的一行或者若干行
可以指定更新条件—— WHERE
可以一次更新一张表或者多张表
单表更新，可以指定 LIMIT 限制更新的行数
多表更新无法指定 LIMIT
注意 UPDATE 的语义（和标准 SQL 不一样的地方）！
UPDATE t1 SET col1 = col1 + 1, col2 = col1
col1 和 col2 将会有相同的值
注意 UPDATE 的性能
修改了二级索引包含的列会导致索引更新
修改了主键会导致所有索引都要更新
3.2 TABLE 级的 DML——UPDATE语句
更新所有记录：年龄增加一岁
UPDATE authors SET age = age + 1;
更新单条记录：设置特定作者的书籍价格
UPDATE authors SET phone_number = '86-12345678901' WHERE first_name = 'John';
3.3 TABLE 级的 DML——UPDATE与AUTO_INCREMENT
可以通过如下SQL对比了解UPDATE 与 AUTO_INCREMENT关系
4、DELETE
4.1 TABLE 级的 DML——DELETE概述
删除表记录当记录不再需要
按条件删除： DELETE ... WHERE ...
全表删除：DELETE FROM authors
指定删除数量： DELETE ... LIMIT n
可以支持删除单张表或者多张表的记录
删除一条记录会影响到所有二级索引
InnoDB 存储引擎删除全表记录后， AUTO_INCREMENT 值不会重置
删除单条记录
DELETE FROM authors WHERE first_name = ' Robert '
4.2 TABLE 级的 DML——DELETE实操
下图中我们可以看到执行DELETE操作后，红框中的数据被删除了
4.3 TABLE 级的 DML——DELETE优化
全表删除应该用 TRUNCATE TABLE 来替代
性能更好
系统开销更少，尤其是日志 IO 和存储开销
当要删除一个 InnoDB 大表中绝大部分数据
INSERT INTO t_copy SELECT * FROM t WHERE ... ; RENAME TABLE t TO t_old, t_copy TO t; DROP TABLE t_old;
********************************************************************************************************************************
《7天玩转MySQL基础实战营》全系列笔记：
【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day01
【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day02
********************************************************************************************************************************
- 点赞
- 收藏
- 关注作者
评论（0）