【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day03

举报
真爱无敌 发表于 2020/07/22 10:43:30 2020/07/22
【摘要】 【7天玩转MySQL】华为云RDS for MySQL学习笔记 Day03第三天的内容主要是:SQL语句(DML/DDL)目标:了解MySQL的DML语句的支持范围,以及各种DML使用的注意事项;了解DDL的种类,以及各种DDL的机制对执行时间、复制等的影响。一、DDL1、DDL的定义DDL是Data Definition Language的缩写。通过DDL语句定义不同的数据段、数据库、表...

【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

    1. 一个 DDL 操作要么执行成功,要么执行都不成功

    2. 不可能存在元数据和数据文件不一致的状态

  • MySQL 5.X 不支持原子的 DDL

    1. 尽量保证 DDL 执行过程不出现系统崩溃

    2. 万一遇到 DDL 崩溃带来的不一致,需要人工介入,甚至需要重建库

  • MySQL 8.0 支持原子的 DDL

    1. 更稳定更安全

    2. 推荐使用

DATABASE级DDL注意点:

  • 数据库名需保证全局唯一

  • 字符集的选择:utf8 VS utf8mb4

    1. utf8 实际上就是 utf8mb3 ,支持编码最大字符长度为 3 字节; utf8mb4 是 4 个字节, utf8mb4 是utf8 的超集,能够存储 Emoji 表情,生僻的汉字等

    2. 字符集选择优先级:表 > 库 > 系统

    3. MySQL 8.0 默认字符集为 utf8mb4

  • ALTER DATABASE 语句影响整个数据库下面的新增表,谨慎操作

  • DROP DATABASE 同时会删除库下所有的表,属于高危操作

    1. 可以考虑先 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

image.png

image.png

3.3 TABLE级DDL--CREATE之表拷贝

  • 克隆表结构

    CREATE TABLE authors_clone LIKE authors;

    只拷贝表定义,不拷贝数据

  • 拷贝表数据

    CREATE TABLE authors_copy SELECT * FROM authors;

    即拷贝表结构,也拷贝数据

3.4 TABLE级DDL--ALTER概述

  • 当现有表的结构不满足业务需求时,需要对表结构进行调整

  • 修改前需要重点评估 ALTER 对现有业务的影响?

    1. ALTER 语句涉及的表有多大?

    2. ALTER 语句需要运行多久?

    3. 当前的系统负载有多高,能否支持 ALTER TABLE ?

    4. ALTER 语句会不会影响 DML ?

  • ALTER 语句通常涉及到表元数据(和表记录)的修改

    1. 有的 ALTER 只修改元数据

    2. 有的 ALTER 也需要修改表数据

    3. 影响到修改表的执行复杂度

3.5 TABLE级DDL--ALTER不改数据

  • 目前一般有以下 ALTER 操作是指修改元数据不修改表数据

    1. RENAME TABLE (ALTER TABLE ... RENAME TO...)

    2. SET DEFAULT

    3. DROP DEFAULT

    4. MODIFY COLUMN

    5. CHANGE COLUMN (虚拟列生成算法)

    6. Change index option

    7. ADD virtual column, DROP virtual column

    8. ADD COLUMN (非生成列, 8.0 )

  • 其它操作,一般都涉及数据的修改

3.6 TABLE级DDL--ALTER的算法

  • ALTER TABLE..., ALGORITHM = DEFAULT | COPY | INPLACE | INSTANT;

    1. 如果不指定,或者指定DEFAULT,会自动选择最合适的算法

    2. 算法优先级INSTANT > INPLACE > COPY

  • 算法间的差异如下图所示:

    image.png

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改索引

  • 加索引

    1. RLATER TABLE authors ADD INDEX (phone_number);

    2. CREATE INDEX phone_number_idx ON authors(phone_number);

  • 删索引

    1. ALTER TABLE authors DROP INDEX phone_number;

    2. DROP INDEX phone_number_idx ON authors;

  • 需要的考量

    1. 索引会加大 DML 的开销

    2. 增加合适的索引加速查询

    3. 不用的索引可以删除

    4. 添加或者修改主键索引的开销会很大

  • 8.0 上如何更好的评估索引—— INVISIBLE INDEX;

    1. ALTER TABLE authors ADD INDEX (phone_number) INVISIBLE;

    2. 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)是关联查询的虚表

    1. 定义会持久化,只和创建时的表快照相关

    2. 其查询出来的数据仍在原表中

    3. 原表可以是普通表或临时表

  • 比如书籍价格的视图

    CREATE VIEW books_price_view AS SELECT name, price FROM books;

image.png

可以看到,上图中原表的数据更新了,所创建的视图数据也会相应的更新!

4.2 其他常见DDL--TRIGGER

  • 触发器(Trigger)可以定义某个表上某个事件的关联操作

    1. 同一个表上触发器的类型和个数没有限制

    2. 可以定义触发器发生的顺序

    3. 常用的触发事件包括 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)支持指定任务的定时调度

    1. 可以定义定时器发生时间,频率,有效期

    2. 可以定义定时器,但是先不让它执行——设置为 DISABLE

    3. 定时器不支持参数传递,但是可以调用存储过程

    4. 可以使用 ALTER EVENT 来修改定时器的定义

    5. 需设置 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 语句插入新的记录到指定表当中

    1. INSERT ... VALUES / INSERT ... SET 插入指定的数据

    2. INSERT ... SELECT 插入查询出来的数据

    3. INSERT ... ON DUPLICATE KEY UPDATE 在唯一索引或者主键索引冲突时可以更新原有行

  • 插入数值的限制和转换,基于 SQL_MODE 不做检查

    1. 插入 NULL 值到 NOT NULL 字段,数值一般转换成字段的默认值

    2. 插入数值型范围之外的值,会截断到最近的范围值

    3. 插入比如 '10.34 a' 到数值字段,非数值部分会被截断

    4. 插入字符串类型,如果长度超过,会被截断到合理长度

    5. ...


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关系

image.png


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操作后,红框中的数据被删除了

image.png


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



********************************************************************************************************************************


【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。