[華為云在線課程][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小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。