[华为云在线课程][7天玩转MySQL基础实战营][day03DDL和DML][学习笔记]

DDL

DDL的定义

Data Definition Language的缩写。

通过DDL语句定义不同的数据段、数据库、表、列、索引等数据库对象的元数据信息。通常由数据库管理员（DBA）使用。

主要的语法有CREATE，ALTER，DROP。主要作用的对象有database，table，view，index，column，event，trigger等。

create database if not exists library; use library; create table authors ( first_name varchar(20) character set utf8mb64, last_name varchar(20) character set utf8mb64, gender varchar(20) character set utf8mb64, age tinyint unsigned default 0, address varchar(512), phone_number varchar(20) ) engine = innodb row_format = dynamic character set = utf8mb64; create table library.books ( isbn varchar(20) not null default "" primary key, name varchar(50) not null default "", first_name varchar(20) character set utf8mb64, last_name varchar(20) character set utf8mb64, price decimal(20, 2) default 0, press varchar(512), publication_date date comment 'publication date', stock int default 0 );

DATABASE级DDL

创建

create database if not exists library default character set = utf8;

修改属性

alter database library character set = utf8mb64;

删除

drop database library;

查看

show databases; show create database library;

DATABASE级DDL注意点

数据库需保证全局唯一

字符集的选择：utf8 VS utf8mb64

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

字符集选择优先级：表>库>系统

MySQL8.0默认字符集为utf8mb64

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

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

可以考虑先DROP每个table，最后DROP DATABASE

TABLE级DDL

TABLE级DDL - 原子的DDL

原子的DDL

一个DDL操作要么执行成功，要么执行都不成功

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

MySQL5.X不支持原子的DDL

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

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

MySQL8.0支持原子的DDL

更稳定更安全

推荐使用

TABLE级DDL - 背景

表的类型

普通表/分区表/临时表

RDS for MySQL默认使用InnoDB存储引擎

也支持其他社区版提供的引擎，但是不建议使用

注意表名大小写敏感参数lower_case_table_names

对表的操作需要指定数据库和表，否则默认使用当前数据库

显示切换数据库USE library;，访问authors表

或者通过library.authors引用

DDL语句也会记录到Binlog中

采用satement格式

TABLE级DDL - CREATE

create table library.authors ( first_name varchar(20) character set utf8mb64, last_name varchar(20) character set utf8mb64, gender enum ('Male','Female'), age tinyint unsigned default 0, address varchar(512), phone_number varchar(20) ) engine = innodb row_format = dynamic character set = utf8mb64;

字段：first_name，last_name，gender，age，address，phone_number

数据类型：varchar(20)，enum，tinyint，varchar(512)

类型描述：character set utf8mb64，default 0

表属性：engine = innodb，row_format = dynamic，character set = utf8mb64

create table library.books ( isbn varchar(20) not null default "" primary key, name varchar(50) not null default "", first_name varchar(20) character set utf8mb64, last_name varchar(20) character set utf8mb64, price decimal(20, 2) default 0, press varchar(512), publication_date date comment 'publication date', stock int default 0 );

字段：isbn，name，first_name，last_name，price，press，published，stock

数据类型：varchar(20)，decimal(20,2)，date，int

类型描述：character set utf8mb64，default 0，comment ‘publication date’

索引：primary key

创建临时表：create temporapy table

TABLE级DDL - CREATE之表拷贝

克隆表结构

CREATE TABLE authors_clone LIKE authors;

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

拷贝表数据

CREATE TABLE authors_copy SELECT * FROM authors;

既拷贝表结构，也拷贝数据

TABLE级DDL - ALTER概述

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

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

ALTER语句涉及的表有多大？

ALTER语句需要运行多久？

当前的系统负载有多高，能够支持ALTER TABLE？

ALTER语句会不会影响DML？

ALTER语句通常涉及到表元数据（和表记录）的修改

有的ALTER只修改元数据

有的ALTER也需要修改表数据

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

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)

其他操作，一般都涉及数据的修改

TABLE级DDL - ALTER的算法

ALTER TABLE…,ALGORITHM=DEFAULT|COPY|INPLACE|INSTANT;

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

算法优先级INSTANT>INPLACE>COPY

选取算法之前要了解它们的差异

TABLE级DDL - ALTER改字段

加字段

ALTER TABLE authors ADD COLUMN title VARCHAR(20) ALTER address;

新字段可以指定位置

改字段

ALTER TABLE authors CHANGE COLUMN status new_status VARCHAR(20) DEFAULT ‘zzzz’;

很可能开销不大

删字段

ALTER TABLE authors DROP COLUMN title, DROP COLUMN new_status;

总是个开销大的操作

TABLE级DDL - ALTER改索引

加索引

ALTER 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;

TABLE级DDL - TRUNCATE

语法

TRUNCATE TABLE authors;

TRUNCATE TABLE被看作是DDL，而不是DML

本质上它删除表，再重建一张表

清空表内所有的行，重置AUTO_INCREMENT

版本间行为差异

5.6/5.7，崩溃之后，操作一般是重做到提交

8.0根据操作是否成功决定是提交还是回滚

TABLE级DDL - DROP

语法

DROP TABLE [IF EXISTS] authors;

DROP TABLE [IF EXISTS] authors,books;

DROP TEMPORARY TABLE …;

'IF EXISTS’能够避免表不存在的报错

谨慎使用删表操作，数据将无法轻易找回

其他常见DDL

其他常见DDL - VIEW

视图（VIEW）是关联查询的虚表

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

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

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

比如书籍价格的视图

CREATE VIEW books_price_view AS SELECT name,price FROM books;

其他常见DDL - TRIGGER

触发器（Trigger）可以定义某个表上某个事件的关联操作

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

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

常用的触发事件包括INSERT/UPDATE/DELETE

比如更新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;

其他常见DDL - PROCEDURE

存储过程（Procedure）支持整合和抽取业务逻辑到一个函数执行

支持参数传入传出

执行使用CALL procedure_name();

比如对一定库存的书籍进行打折

DROP TRIGGER 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 ;

其他常见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

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 SQL, with the DML

comprising some of the operators in the language. 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.

DML主要涉及对用户数据的增删改

有些DML语句会涉及到数据的查询加修改

MySQL的DML主要包括INSERT/UPDATE/DELETE三类

DML语句都要记录binlog，针对每一行的修改

INSERT

TABLE级的DML - INSERT概述

INSERT语句插入新的记录到指定表当中

INSERT …VALUES/INSERT …SET插入指定的数据

INSERT …SELECT插入查询出来的数据

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

插入数据的限制和转换，基于SQL_MODE不做检查

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

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

插入比如’10.34a’到数值字段，非数值部分会被截断

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

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’,‘12345678902’),(‘Linda’,‘White’,‘Female’,36,‘Room P,Building O’,‘12345678903’);

插入查询结果集

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’,‘1234’) ON DUPLICATE KEY UPDATE age=age+5;

替换或插入

REPLACE INTO…

REPLACE INTO authors VALUES(‘Robert’,‘White’,‘Male’,33,‘Room U,Building I’,‘86-1234’),(‘Linda’,‘White’,‘Female’,36,‘Room P,Building I’,'86-2345);

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会产生空洞，但不影响唯一

UPDATE

TABLE级的DML - UPDATE概述

UPDATE用来更新表内的一行或者若干行

可以指定更新条件-WHERE

可以一次更新一张表或者多张表

单表更新，可以指定LIMIT限制更新的行数

多表更新无法指定LIMIT

注意UPDATE的语义（和标准SQL不一样的地方）

UPDATE t1 SET col1=col1+1,col2=col1;

col1和col2将会有相同的值

注意UPDATE的性能

修改了二级索引包含的列会导致索引更新

修改了主键会导致所有索引都要更新

TABLE级的DML - UPDATE语句

更新所有记录：年龄增加一岁

UPDATE authors SET age=age+1;

UPDATE authors SET phone_number = ‘86-123456’ WHERE first_name = ‘John’;

TABLE级的DML - UPDATE与AUTO_INCREMENT

DELETE

TABLE级的DML - DELETE概述

删除表记录当记录不再需要

按条件删除：DELETE … WHERE …;

全表删除：DELETE FROM authors;

指定删除数量：DELETE … LIMIT n;

可以支持删除单张表或者多张表的记录

删除一条记录会影响到所有二级索引

InnoDB存储引擎删除全表记录后，AUTO_INCREMENT值不会重置

删除单条记录

DELETE FROM authors WHERE first_name = ‘Robert’;

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;

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。