Study Notes of MySQL 2 —— Manipulation, Definition and Transaction Control

• DML (Data Manipulation Language)
• DDL (Data Definition Language)
• TCL (Transaction Control Language)

[toc]

DML (Data Manipulation Language)

Insert

• Classic Way

  /*
  语法：
  	INSERT INTO 表名（列名，...）
  	VALUES （值1，...）
  注意：
  	1. 插入的值的类型要与列的类型一致或者兼容
  	2. 不可以为NULL的列必须插入值
  	3. 可以为NULL的列插入值的方式：
  		列名写上，值填写 NULL
  		列名与值均直接省略
  	4. 列的顺序可以调换
  	5. 列的个数和值的个数必须匹配
  	6. 可以省略列名，默认所有列，顺序与表中顺序一致
  */
  
  # e.g.
  INSERT INTO beauty
  VALUES(18, '张飞', '男', NULL, '119', NULL, NULL)

• Streamlined Way

  /*
  语法：
  	INSERT INTO 列名
  	SET 列名 = 值, 列名 = 值
  */
  
  # e.g.
  INSERT INTO bueaty
  SET id = 19, NAME = '刘涛', phone = '999';

• Classic Way VS Streamlined Way

  # 方式一支持插入多行，方式二不支持
  INSERT INTO players
  VALUES (33, 'Larry Bird')
  ,(21, 'Tim Duncan');
  
  # 方式一支持子查询，方式二不支持
  INSERT INTO beauty(id, NAME, phone)
  SELECT id, boy_name
  FROM boys
  WHERE id < 3;

Update

/*
修改 *单表* 语法：
	UPDATE 表名
	SET 列 = 值，列 = 值
	WHERE 筛选条件；
	
修改 *多表* 92 语法：
	UPDATE 表名1, 表名2
	SET 列 = 值, ...
	WHERE 连接条件
	AND 筛选条件; 
	
修改 *多表* 99 语法：
	UPDATE 表1
	INNER/LEFT/RIGHT JOIN 表2
	ON 连接条件
	SET 列 = 值, ...
	WHERE 筛选条件;
*/

# 修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b
ON bo.'id' = b.'boyfriend_id'
SET b.'phone' = '114'
WHERE bo.'boyName' = '张无忌';

Delete & Truncate

• Delete

  /*
  DELETE 单表删除 语法：
  	DELETE FROM 表名 
  	WHERE 筛选条件;
  	
  DELETE 多表删除 92 语法：
  	DELETE 表1的别名，表2的别名（删除谁写谁的别名）
  	FROM 表1 别名, 表2 别名
  	WHERE 连接条件
  	AND 筛选条件;
  	
  DELETE 多表删除 99 语法：
  	DELETE 表1的别名，表2的别名（删除谁写谁的别名）
  	FROM 表1
  	INNER/LEFT/RIGHT JOIN 表2
  	WHERE 筛选条件;
  */

• Truncate

  /*
  TRUNCATE 单表删除 语法：
  	TRUNCATE TABLE 表名 
  注意：
  	一删全删，不能加 WHERE
  */

• Delete vs Truncate

  • 假如删除的表中有自增长列
    • 如果用 delete 删除之后，再插入数据，自增长列的值从断点开始
    • 如果用 truncate 删除后，再插入数据，自增长列的值从 1 开始
  • 返回值
    • delete 有返回值
    • truncate 无返回值
  • 回滚
    • delete 删除不能回滚
    • truncate 删除可以回滚

---

DDL (Data Definition Language)

库的管理

• 创建

  /*
  语法：
  	CREATE DATABASE 库名;
  */
  
  # e.g.
  CREATE DATABASE IF NOT EXISTS books;

• 修改

  /*
  用途：
  	修改库的字符集
  语法：
  	ALTER DATABASE books CHARCTER SET gbk;
  */

• 删除

  /*
  语法：
  	DROP DATABASE 库名;
  */
  
  # e.g.
  DROP DATABASE IF EXISTS books;

表的管理

• 创建

  /*
  语法：
  	CREATE TABLE 表名(
  		列名 列类型(长度) 列的约束,
  		列名 列类型(长度) 列的约束,
  		...
  		列名 列类型(长度) 列的约束);
  */
  
  # 创建表 book
  CREATE TABLE book(
  	id INT, # number
  	BName, VARCHAR(20), # book name
  	author VARCHAR(20), # author name
      authorId INT, # number of the author
  	publishId DATETIME # date of publish
  );

• 修改

  /*
  修改 列名 语法：
  	ALTER TABLE 表名 
  	CHANGE COLUMNS 旧列名 新列名 新列类型;
  */
  ALTER TABLE book 
  CHANGE COLUMNS publishdate pubDate DATETIME;
  
  /*
  修改 列的类型或者约束 语法：
  	ALTER TABLE 表名
  	MODIFY COLUMNS 列名 新列类型;
  */
  ALTER TABLE book
  MODIFY COLUMNS pubdate TIMESTAMP;
  
  /*
  添加 新列 语法：
  	ALTER TABLE 表名 
  	ADD COLUMNS 列名 列类型;
  	[# FIRST/AFTER 字段名]
  */
  ALTER TABLE author 
  ADD COLUMNS annul DOUBLE
  AFTER t2; 
  
  /*
  删除 列 语法：
  	ALTER TABLE 表名
  	DROP COLUMNS 列名;
  */
  ALTER TABLE author
  DROP COLUMNS annual;
  
  /*
  修改 表名 语法：
  	ALTER TABLE 表名 
  	RENAME TO 新表名
  */
  ALTER TABLE author 
  RENAME TO book_author

• 删除

  /*
  语法：
  	DROP TABLE 表名;
  */
  DROP TABLE IF EXISTS book_author;

• 复制

  /*
  复制表的 结构 语法：
  	CREATE TABLE 新表名 LIKE 旧表名;
  */
  CREATE TABLE copy LIKE author;
  
  /*
  复制表的 结构 + 数据 语法：
  	CREATE TABLE 新表名 
  	SELECT * FROM 旧表名;
  */
  CREATE TABLE copy2 
  SELECT * FROM author;
  
  # 复制部分数据
  CREATE TABLE copy2 
  SELECT id, author_name
  FROM author
  WHERE nation = 'China';
  
  # 复制部分结构（部分字段）
  CREATE TABLE copy 
  SELECT id, author_name
  FROM author
  WHERE 1 = 2; # WHERE 0;

数据类型

• 数值型 - 整型

整数类型	字节数	范围
Tinyint	1	有符号：-128 -127 无符号：(0-255)
Smallint	2	有符号：-32768 - 32767 无符号：(0 - 65535)
Mediumint	3	很大
Int, integer	4	很大
Bigint	8	很大

# 设置无符号（默认有符号）
CREATE TABLE IF EXISTS tab_int(
	t1 INT;
    t2 INT UNSIGNED
);

# 如果插入数值超出范围：插入临界值
# 如果不设置长度就是默认长度
# 设置了长度不改变范围，只改变显示长度，真是的范围只由整数类型决定（搭配 ZEROFILL 使用可以用 0 在左侧填充）

• 小数 - 定点数

浮点数类型	默认	字节	范围
float(M,D)	根据插入的值来确定精度	4	很大
double(M,D)	根据插入的值来确定精度	8	很大

• 小数 - 浮点数

定点数类型	默认	字节	范围
DEC(M,D) DECIMAL(M,D)	M=10, D=0	M+2	最大取值范围与double相同，给定decimal的有效取值范围由M和D决定

# M：整数部位和小数部位总长度
# D：小数点后几位
# M 和 D 都可以省略
CREATE TABLE tab_float(
	f1 FLOAT(5,2),
    f1 DOUBLE(5,2),
    f1 DECIMAL(5,2),
);

• 字符型 - 较短的文本

字符串类型	差别	最多字符数	描述
char(M)	可变类型	M	M 为 0-255 之间的整数
varchar(M)	不可变类型	M	M 为 0-255 之间的整数
binary & varbinary	/	/	保存较短的二进制
enum	/	/	用于保存枚举
set	/	/	用于保存集合

• 字符型 - 较长的文本
  • text
  • blob（较长的二进制数据）
• 日期类型

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	1970010108001	2038年的某个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

常见约束

• 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

• 语法

  CREATE TABLE 表名(
  		字段名 字段类型 约束
  	);

• 分类：六大约束

  • NOT NULL：非空，用于保证该字段的值不为空（姓名，学号）
  • DEFAULT：默认，用于保证该字段有默认值（性别）
  • PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空（学号，编号）
  • UNIQUE：唯一，用于保证搞字段的值具有唯一性，可以为空（座位号）
  • CHECK：检查约束（MySQL中不支持）
  • FORRIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主键的关联列的值，在从表中添加外键约束，用于引用主表中的列值（学生表的专业编号，员工表的部门编号）

• 添加约束的时机

  • 创建表时
  • 修改表时

• 约束的添加分类

  • 列级约束
    • 六大约束都可以写（语法上都支持）
    • 外键约束无效果
  • 表级约束
    • 除了非空/默认，其他都支持

• 创建表时添加 列级 约束

  /*
  创建 *表* 时添加 *列级* 约束
  */
  USE students;
  
  CREATE TABLE stuinfo(
  	id INT PRIMARY KEY, # 主键
      stuName VARCHAR(20) NOT NULL, # 非空
      gender CHAR(1) CHECK(gender='Male' OR gender='Female'), # 检查
      seat INT UNIQUE, # 唯一
      age INT DEFAULT 18, # 默认
      majorId INT FOREIGN KEY REFERENCES major(id) # 外键
  );

• 创建 表 时添加 表级 约束

  /*
  创建 *表* 时添加 *表级* 约束
  语法：在各个字段的最下面
  	[constraint 约束名] 约束类型（字段名）
  */
  DROP TABLE IF EXISTS stuinfo;
  
  CREATE TABLE stuinfo(
  	id INT,
      stuName VARCHAR(20),
      gender CHAR(1),
      seat INT,
      age INT,
      majorId INT,
      
      CONSTRAINT pk PRIMARY KEY(id), # 主键
      CONSTRAINT up UNIQUE(seat), # 唯一
      CONSTRAINT ck CHECK(gender='Male' OR gender='Female'), # 检查
      CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外键
  );
  
  # 通用写法
  CREATE TABLE stuinfo(
  	id INT PRIMARY KEY, # 主键
      stuName VARCHAR(20) NOT NULL, # 非空
      gender CHAR(1),
      seat INT UNIQUE, # 唯一
      age INT DEFAULT 18, # 默认
      majorId INT,
      CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外键
  );

• 修改 表 时添加 列级 约束

  /*
  修改 *表* 时添加 *列级* 约束
  	alter table 表名 modify column 字段名 字段类型 新约束;
  */
  # 非空约束
  ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
  # 默认约束
  ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
  # 主键约束-列级
  ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
  # 唯一约束-列级
  ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

• 修改 表 时添加 表级 约束

  /*
  修改 *表* 时添加 *表级* 约束
  	alter table 表名 add [constraint 约束名] 约束类型（字段名）;
  */
  # 主键约束-表级
  ALTER TABLE stuinfo ADD PRIMARY KEY(id);
  # 唯一约束-表级
  ALTER TABLE stuinfo ADD UNIQUE(seat);
  # 外键约束
  ALTER TABLE stuinfo ADD [CONSTRAINT fk_stuinfo_major] FOREIGN KEY(majorid) REFERENCES major(id);

• 修改 表 时删除约束

  /*
  修改 *表* 时删除约束
  */
  # 删除非空约束
  ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
  # 删除默认约束
  ALTER TABLE stuinfo MODIFY COLUMN age INT;
  # 删除主键
  ALTER TABLE stuinfo DROP PRIMARY KEY;
  # 删除唯一约束
  ALTER TABLE stuinfo DROP INDEX seat;
  # 删除外键约束
  ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

标识列

• 标识列：又称为自增长列

• 含义

  • 可以不用手动的插入值，系统提供默认的序列值

• 特点

  • 标识类不必须和主键搭配，但要求是一个 KEY
  • 一个表中只能有一个标识列
  • 标识列只能是数值型
  • 可以设置步长（auto_increment_increment = 3）
  • 可以通过手动插入值，来设置起始值

• 创建表时设置标识列

  # 创建表时设置标识列
  CREATE TABLE tab_identity(
  	id INT PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(20)
  );
  
  # 可以通过手动插入值，来设置起始值
  INSERT INTO tab_identity(id, NAME) VALUES(10, 'john');
  INSERT INTO tab_identity(id, NAME) VALUES(NULL, 'john');

• 修改表时设置标识列

  ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

• 修改表时删除标识列

  ALTER TABLE tab_identity MODIFY COLUMN id INT;

---

TCL (Transaction Control Language)

Basic Terminology

• 事务：一个或者一组 sql 语句组成的一个执行单元。这个执行单元要么全部执行，要么全部不执行。如果单元中的某条 SQL 语句执行失败，那么整个单元将会回滚。

• 存储引擎

  • 概念：在 MySQL 中的数据用各种不同的技术存储在文件或者内存中。
  • 通过 Show Engines 来查看 MySQL 支持的存储引擎
  • 在 MySQL 中用的最多的存储引擎有：innodb, myisam, memory 等。其中 innodb 支持事务。

• 事务的 ACID 属性

  • Atomicity 原子性：事务是一个不可分割的工作单位
  • Consistency 一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态。
  • Isolation 隔离性：事务的执行不能被其他的事务干扰，即一个事务内部的操作以及使用的数据对并发的其他事物是隔离的，并发执行的各个事物之间是不能互相干扰的。
  • Durability 持久性：事务一旦提交，对数据库的改变就是永久性的。

事务的创建

• 隐式事务：事务没有明显的开启和结束的标记

  • 比如：insert / update / delete

• 显式事务：事务具有明显的开启和结束的标记

  # 前提：必须先设置自动提交功能为禁用
  set autocommit = 0;
  # 可选
  START TRANSACTION;
  # 结束事务
  COMMIT; # 提交事务
  ROLLBACK; # 回滚事务
  
  # e.g.
  SET autocommit = 0;
  START TRANSACTION;
  UPDATE account SET balance = 500 WHERE username = 'Tim Duncan';
  UPDATE account SET balance = 1500 WHERE username = 'Larry Bird';
  COMMIT;
  
  # e.g.
  SET autocommit = 0;
  START TRANSACTION;
  UPDATE account SET balance = 1000 WHERE username = 'Tim Duncan';
  UPDATE account SET balance = 1000 WHERE username = 'Larry Bird';
  ROLLBACK;

数据库的隔离级别

隔离级别	描述
READ UNCOMMITTED 读未提交数据	允许事务读取未被其他事物提交的变更。 脏读，幻读，不可重复读的问题都会出现。
READ COMMITTED 读已提交数据	只允许事务读取已经被其他事务提交的变更。 可以避免脏读，但不可重复读和幻读仍然可能出现。
REPEATABLE READ 可重复读	确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。 可以避免脏读和不可重复读，但幻读仍然存在。
SERIALIZABLE 串行化	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除。 所有并发问题可以避免。

• 如果没有设置隔离机制

  • 脏读：T1 读取了已经被 T2 更新但是还没有被提交的字段。
  • 不可重复读：T1读取了一个字段，然后 T2 更新了该字段之后，T1 再次读取了同一个字段，值就不同了。
  • 幻读：T1从一个表中读取了某一个字段，T2 在该表中插入了一些新的行，如果 T1 再次读取同一个表，就会多出几行。

• MySQL的默认事务隔离级别：REPEATABLE READ

• 设置当前 MySQL 连接的隔离级别

  set transaction isolation level read committed

• 设置数据库系统的全局的隔离级别

  set global transaction isolation level read committed

回滚点

SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
SAVEPOINT a; # 设置保存点
DELETE FROM account WHERE id = 29;
ROLLBACK TO a; # 回滚到保存点

视图

• 含义：虚拟表，和普通表一样使用（MySQL 15.1 版本出现的新特性，是通过表动态生成的数据）。只保存 SQL 逻辑，不保存查询结果。

• 应用场景

  • 多个地方用到同样的查询结果
  • 该查询结果使用的 SQL 语句比较复杂

• 特性

  • 复用 SQL 语句
  • 简化复杂的 SQL 操作，不必知道查询细节
  • 保护数据，提高安全性

• 视图的创建和使用

  /*
  创建 视图 语法：
  	CREATE VIEW 视图名
  	AS
  	查询语句;
  
  使用 视图 语法：
  	SELECT * 
  	FROM 视图名;
  */
  
  # 查询张姓同学的姓名和专业
  SELECT stuname. majorname
  FROM stuinfo s
  INNER JOIN major m 
  ON s.'majorid' = m.'id';
  
  SELECT * 
  FROM v1 
  WHERE stuname LIKE '张%';

• 视图的修改

  /*
  方式一：
  	CREATE OR REPLACE VIEW 视图名
  	AS
  	查询语句;
  */
  
  CREATE OR REPLACE VIEW myv3
  AS
  SELECT * FROM employees;
  
  /*
  方式二：
  	ALTER VIEW 视图名
  	AS
  	查询语句;
  */
  
  ALTER VIEW myv3
  AS
  SELECT * FROM employees;

• 视图的删除

  /*
  语法：
  	DROP VIEW 视图名, 视图名;
  */
  
  DROP VIEW myv1, myv2;

• 视图的查看

  DESC myv3;
  SHOW CREATE VIEW myv3;

• 视图的更新

  # 插入  
  INSERT INTO myv1 VALUES('张飞','1234@sina.com')

  修改

  UPDATE myn1 SET last_name = ‘Tim Duncan’ WHERE last_name = ‘Larry Bird’;

  删除

  DELETE FROM myv1 WHERE last_name = ‘Tim Duncan’;

    
    - **具备以下关键词的视图不允许更新**
      - 包含以下关键字的 SQL 语句：```分组函数```，```distinct```，```group by```，```having```，```union``` 或者 ```union all```
      - 常量视图：```SELECT 'John' NAME;```
      - ```select``` 中包含子查询
      - ```join```
      - ```from ``` 一个不能更新的视图
      - ```where``` 子句的子查询引用了 ```from``` 子句中的表
  
  - **视图与表的对比**
    - 视图不占用实际的物理空间，表占用
    - 视图保存语句逻辑，表保存了实际结果数据
    - 视图一般不进行增删改