一、DML语言

DML又被称为数据库操作语言

分类：
插入：insert
修改：update
删除：delete

1. 插入insert

1.1 方式一：普通方式

语法：

  1. INSERT INTO 表名(列名1,列名2,...,列名n) VALUES(值1,值2,...,值n);

示例：

  1. #1.插入的值的类型要与列的类型一直或兼容
  2. USE girls;
  3. INSERT INTO beauty 
  4. (id,`name`,sex,borndate,phone,photo,boyfriend_id)
  5. VALUES (null,'王冰冰','女','1996-06-01',13097217207,null,22);
  6. #2. 不能为null的字段必须插入值，可以为null的字段如果插入
  7. ##方式，在字段对应的值处写上null
  8. INSERT INTO beauty 
  9. (id,`name`,sex,borndate,phone,photo,boyfriend_id)
  10. VALUES (15,'王大拿','女','1996-06-01',13097217207,null,22);
  11. ## 方式二：不写相应的字段
  12. INSERT INTO beauty 
  13. (id,`name`,sex,borndate,phone,boyfriend_id)
  14. VALUES (16,'李婷婷','女','1996-06-01',13097217207,22);
  15. # 3. 列的顺序是否可以调换
  16. INSERT INTO beauty(`name`,sex,id,phone)
  17. VALUES('蒋欣','女',17,13097217207)# 可以
  18. # 4. 列数和值的个数必须一致
  19. # 5. 可以省略列名，默认是所有列，而且列的顺序和表中列的顺序一致
  20. INSERT INTO beauty 
  21. VALUES (18,'王冰晶','女','1996-06-01',13097217207,null,22);

1.2：方式二：set方式

语法

INSERT INTO 表名
SET 列名1=值1,列名2=值2,...

示例：

# 第二种方式插入
INSERT INTO beauty
SET id=19,`name`='刘涛',phone=999

1.3 两种方式对比

# 1. 方式一支持多行插入，方式二不支持
INSERT INTO beauty 
(id,`name`,sex,borndate,phone,photo,boyfriend_id)
VALUES 
  (20,'唐艺昕','女','1996-06-01',13097217207,null,22),
  (21,'董卿','女','1996-06-01',13097217207,null,22);

# 2. 方式一支持子查询，方式二不支持，即将别的表中的数据存入当前表中
INSERT INTO beauty 
(id,`name`,sex,borndate,phone,photo,boyfriend_id)
SELECT 22,'宋茜','女','1996-06-01',13097217207,null,22; # 没有values关键字，查出来的列数和需要存入的列数相同
## 也可以插入多条数据
INSERT INTO beauty(id,name)
SELECT NULL,boyName FROM boys;

2. 修改 update

2.1 修改单表的记录

语法：

UPDATE 表名
SET 列1=新值1,列2=新值2,...,列n=新值n
WHERE 筛选条件

示例：

## 修改单表的记录
# 案例：修改beauty表中的姓唐的女神的电话为13899999
UPDATE beauty
SET phone = '13899999'
WHERE `name` LIKE '唐%';
## 案例2修改boys表中id号为2的数据，名字改成张飞，魅力值为10
UPDATE boys
SET boyName = '张飞',userCP=10
WHERE id = 2;

2.2 修改多表的记录【级联更新】

语法：

## 92 语法（只支持内连接）
UPDATE 表1 别名,表2 别名
SET 列1=值1，列2=值2,...
WHERE 筛选条件
## 99语法
update 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列1=值1,列2=值2
WHERE 筛选条件

示例

## 修改周杰伦女朋友的电话号码为114
UPDATE boys b
LEFT JOIN beauty be
ON b.id=be.id
SET phone = '114'
WHERE boyName = '周杰伦';
## 将没有男朋友的女神的男朋友都改成张飞
UPDATE beauty be
LEFT JOIN boys b
on be.boyfriend_id = b.id
SET be.boyfriend_id = 2
WHERE b.id IS NULL

3. 删除 delete

3.1 方式一：delete关键字

语法：

# 1. 单表的删除
DELETE FROM 表名 WHERE 筛选条件
#2.多表的删除、
## sql92 语法
DELETE 表1的别名,表2的别名
FROM 表1 别名,表2 别名
WHERE 连接条件
AND 筛选条件
##sql99语法
DELETE  表的别名1,表的别名2  FROM 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
WHERE 筛选条件

如果不加筛选条件默认删除所有的数据

案例：

#单表的删除:
## 案例1:删除手机号以9结尾的女神的信息
DELETE FROM beauty
WHERE phone LIKE '%9'
# 多表的删除
## 案例：删除周杰伦的女朋友
DELETE be FROM beauty be
LEFT JOIN boys b
ON be.boyfriend_id = b.id
WHERE b.boyName = '周杰伦'

多表的删除，要确定删除的是哪张表中的数据，所以delete后面要跟表的别名，可以跟多个，以确定是删除连接的表中的哪些表的数据要删除。

3.2 方式二：truncate 关键字

语法：

TRUNCATE table 表名;

不能加筛选条件，默认删除表中的所有数据

案例：

# 删除表中的所有数据，清空语句
TRUNCATE TABLE boys;

3.2 delete 关键字与truncate关键字的区别

  1. delete可以加where条件，truncate不能加
  2. truncate删除，效率高一点
  3. 加入要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始；而truncate删除后，再插入数据，自增长列的值从1开始
  4. truncate删除不能回滚，delete删除可以回滚。

    二、DDL语言

    数据定义语言，涉及到了库和表的管理 创建：create 修改：alter 删除：drop

1. 库的管理

1.1 数据库的创建

语法：

CREATE DATABASE 库名;

案例：

CREATE DATABASE IF NOT EXISTS books;

增加容错性，如果存在就不创建数据库

1.2 更改数据库

修改数据库的字符集

案例：

ALTER DATABASE books CHARACTER set gbk;

1.3 库的删除

DROP DATABASE IF EXISTS books;

对数据库的操作不常用！

2. 表的管理

2.1 表的创建

语法：

CREATE TABLE IF NOT EXISTS 表名(
  列名1  列的类型【(长度) 约束】
  列名2  列的类型【(长度) 约束】
  列名3  列的类型【(长度) 约束】
  ……
  列名n  列的类型【(长度) 约束】
);

案例

# 创建书的表
CREATE TABLE book(
  id INT, #书的编号
  bName VARCHAR(20),# 图书名
  price DOUBLE,# 价格
  authod_id INT,#作者编号
  publishDate  DATETIME#出版日期

)
#创建作者表
CREATE TABLE author(
  id INT,
  au_name VARCHAR(20),
  nation VARCHAR(10)
)

2.2 表的修改

2.2.1 修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

修改列名时，一定样加上类型，可以省略 COLUMN 关键字

2.2.2修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

2.2.3 添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE;

2.2.4 删除列

ALTER TABLE author DROP COLUMN annual;

2.2.5 修改表名

ALTER TABLE author RENAME TO book_author;

2.2.6总结

语法：

## 对列修改的总结
ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名 【新列名 列类型 约束】
## 列相关操作
ALTER TABLE author 操作列的关键字 COLUMN 列名
## 如果是修改列的名字 需要提供新的列名和数据类型
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
## 如果只是修改列的类型，就只要把关键字换成modify，加上新的数据类型就可以了
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
## 如果是添加列，就将关键字修改为 add，然后加上类型就可以了
ALTER TABLE author ADD COLUMN annual DOUBLE;
## 如果是删除表，修改关键字为 drop ，数据类型也不需要
ALTER TABLE author DROP COLUMN annual;
## 如果是修改表名 ，把关键字 换成 RENAME TO 并且不需要 CONLUMN 关键字
ALTER TABLE author RENAME TO book_author;

3.表的删除

DROP TABLE IF EXISTS book_author;

建库建表的通用写法

DROP DATABASE IF EXISTS 旧库名
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧库名
CREATE TABLE 新库名;

4. 表的复制

##1. 仅仅复制表的结构
CREATE TABLE girl LIKE beauty;
## 2.复制表的结构和数据
CREATE TABLE girl2 SELECT * FROM beauty;
##3. 只复制部分数据
CREATE TABLE girl3
SELECT `name` from beauty;

只复制表的结构 like 后面跟表名
在复制表的同时还复制数据，实际上复制的是一张虚拟表，也就是查询得到的结果集。复制后的表名 后面直接跟 select语句。显然查询的任何语句都可以

三、数据类型

数值型：

  • 整型
  • 小数
    • 定点数
    • 浮点数

字符型：

  • 较短的文本
  • 较长的文本

日期型

1. 整型

旧版笔记 MySQL基础(DML) - 图1
字节数1，2，3，4，8。
无符号和有符号

CREATE TABLE tab_int(
    t1 INT,
    t2 INT UNSIGNED
    )
DESC tab_int;

旧版笔记 MySQL基础(DML) - 图2
特点：

  1. 如果不设置无法好还是有符号，默认是有符号，如果想设置无符号，需要添加关键字 unsigned
  2. 如果插入的数值超过了整型的返回，会报 out of range 异常，插入失败。
  3. 如果不设置长度会有默认长度，也可以自己设置长度。

  4. 类型后面的长度不是数据的范围，数据的范围是由字节数决定的，长度的含义是在最大的显示宽度。如果不够就会自动在左边填充0，但是必须在类型后面加上 zerofill 关键字。如果加上这个关键字，那么这个整型就是无符号整型。

    2. 小数

    旧版笔记 MySQL基础(DML) - 图3
    浮点数只是接近真实值，而定点数类型更为精确，所以需要更为精确的值，一般使用定点数。 DEC是DECIMAL的简写。 float和double设置的时候也是 float(M,D)，double(M,D)
    特点：

  5. M表示整数部位+小数部位，D表示小数部位，如果M的位数超过范围，直接插入失败。如果D的位数超过范围只截取小数点后前D个小数，并且会四舍五入，所有小数类型都是这样。

  6. M和D都可以省略
  7. DEC默认认是DEC(10,0)，整数部分超过就报错，小数部分超过就4舍5入。
  8. float 和double默认情况下没有限制，不超过取值范围就行。

类型选择的原则：所选择的类型越简单越好，能保存数值的类型越小越好。

3. 字符型

较短的文本：
char
varchar
较长的文本：
text
blob（较大的二进制）
较短的文本类型
旧版笔记 MySQL基础(DML) - 图4
用于保存MySQL中较短的字符串。
特点：

  1. char代表的固定长度的字符，设定是多少长度，就会给多少长度
  2. varchar 是不定长度的字符类型，如果没有存储的数据，小于varchar规定的长度（M），那么就会只给存储进来的字符的长度的内存
  3. char的效率比varchar的效率高。
  4. 如果存储的是固定长度的字符，就用char，如果是不定长字符的就用 varchar。

枚举类型
旧版笔记 MySQL基础(DML) - 图5
存入的值只能是枚举列表中的值。
set类型

枚举只能插入列表中的1个，set可以插入多个。大写会被转换为小写。

4. 日期型

四、常见的约束

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

分类：六大约束
NOT NULL：非空约束，用于保证该字段的值不能为空，比如姓名、学号 DEFAULT：默认，用于保证该字段有默认值，比如性别
PRIMARY KEY：主键，用于保证该字段的值具有唯一性
UNIQUE：唯一，用于抱枕该字段的值具有唯一性，可以为空
CHECK：检查约束（MySQL中不支持），比如限定年龄的范围
FOREIGN KEY：外键约束，用于限定两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表中添加外键约束，引用从表的字段中的值。
添加约束的时机：

  1. 创建表时
  2. 创建表之后，数据添加之前对表进行修改。

约束的添加分类：
列级约束：六大约束都可以写，但外键约束没有效果
表级约束：除了非空和默认，其它的都支持

1. 创建表时添加约束

CREATE TABLE stu_info(
    id INT PRIMARY KEY,# 唯一约束
    stuName VARCHAR(20) NOT NULL ,# 非空约束
    gender char(1) CHECK(gender='男' OR gender = '女'),#检查约束
    stu_phone char(11) UNIQUE,#唯一约束
    age INT DEFAULT 18 ,# 默认约束
    major_id INT  REFERENCES major(id)# 外键约束

)
CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);
DESC stu_info;

语法：
直接在字段名和类型后面追加约束类型即可。
只支持：默认、非空、主键、唯一。
外键约束添加在列上没有意义和效果，检查约束MySQL不支持。

2. 添加表级约束

CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender char(1),
    seat INT,
    age INT,
    majorid int,

    ## 开始添加约束
    CONSTRAINT pk primary KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat),#唯一键
    CONSTRAINT ck CHECK(gender='男' OR gender='女'),
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
)
#可以省略约束的名字，系统会主动赋予一个名字
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender char(1),
    seat INT,
    age INT,
    majorid int,

    ## 开始添加约束
    primary KEY(id),#主键
    UNIQUE(seat),#唯一键
    CHECK(gender='男' OR gender='女'),
    FOREIGN KEY(majorid) REFERENCES major(id) #外键
)

表级约束不支持非空和默认。

语法：

在各个字段的最下面
【constraint 约束名】 约束类型（字段名）

3. 通用的写法

# 通用写法
CREATE TABLE IF NOT EXISTS stuinfo(
    id int primary key,
    stuname varchar(20) NOT NULL,
    gender char(1),
    seat INT UNIQUE,
    majorid int,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
)

外键一般用表级约束，其它的约束，一般用列级约束。

4. 主键和唯一约束的对比

  1. 主键唯一非空；唯一约束，唯一，但是可以为空。且可以有多个空值（5.7）
  2. 主键一张表中只能有一个；唯一约束一个表中可以有多个

  3. 可以有联合主键和联合唯一键。

    CREATE TABLE stuinfo(
 id INT,
 stuname VARCHAR(20),
 gender char(1),
 seat INT,
 seat2 INT,
 age INT,

 ## 开始添加约束
 primary KEY(id,stuname),#主键
 UNIQUE(seat,seat2),#唯一键
 CHECK(gender='男' OR gender='女')
)

    联合唯一约束中，null值还是可以重复，不论是一个null还是两个null。 联合主键中，必须两个一摸一样才会被限制插入，不然就视为不重复。

5.外键的特点

  1. 要求在从表设置外键关系
  2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容
  3. 主表的关联列必须是一个key（主键约束的字段、唯一约束的字段），也就是关联唯一。
  4. 插入数据时，应该先插入主表，再插入从表（拿 专业—学生 举例，先添加专业才能添加专业中所属的学生）
  5. 删除数据时，先删除从表，再删除主表。

问题：为什么实际的业务场景中，不适合使用外键约束？
引言
其实这个话题是老生常谈，很多人在工作中确实也不会使用外键。包括在阿里的JAVA规范中也有下面这一条

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

但是呢，询问他们原因，大多是这么回答的

每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦,测试数据极为不方便。

坦白说，这么说也是对的。但是呢，不够全面，所以开一文来详细说明。
正文
首先我们明确一点，外键约束是一种约束，这个约束的存在，会保证表间数据的关系“始终完整”。因此，外键约束的存在，并非全然没有优点。
比如使用外键，可以

  • 保证数据的完整性和一致性
  • 级联操作方便
  • 将数据完整性判断托付给了数据库完成，减少了程序的代码量

然而，鱼和熊掌不可兼得。外键是能够保证数据的完整性，但是会给系统带来很多缺陷。正是因为这些缺陷，才导致我们不推荐使用外键，具体如下

性能问题

假设一张表名为user_tb。那么这张表里有两个外键字段，指向两张表。那么，每次往user_tb表里插入数据，就必须往两个外键对应的表里查询是否有对应数据。如果交由程序控制，这种查询过程就可以控制在我们手里，可以省略一些不必要的查询过程。但是如果由数据库控制，则是必须要去这两张表里判断。

并发问题

在使用外键的情况下，每次修改数据都需要去另外一个表检查数据,需要获取额外的锁。若是在高并发大流量事务场景，使用外键更容易造成死锁。

扩展性问题

这里主要是分为两点

  • 做平台迁移方便，比如你从Mysql迁移到Oracle，像触发器、外键这种东西，都可以利用框架本身的特性来实现，而不用依赖于数据库本身的特性，做迁移更加方便。
  • 分库分表方便，在水平拆分和分库的情况下，外键是无法生效的。将数据间关系的维护，放入应用程序中，为将来的分库分表省去很多的麻烦。

    技术问题

    使用外键，其实将应用程序应该执行的判断逻辑转移到了数据库上。那么这意味着一点，数据库的性能开销变大了，那么这就对DBA的要求就更高了。很多中小型公司由于资金问题，并没有聘用专业的DBA，因此他们会选择不用外键，降低数据库的消耗。
    相反的，如果该约束逻辑在应用程序中，发现应用服务器性能不够，可以加机器，做水平扩展。如果是在数据库服务器上，数据库服务器会成为性能瓶颈，做水平扩展比较困难。

    6. 修改表时添加约束

    语法
    #列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 列类型 约束;
#表级约束
ALTER TABLE 表名 ADD  约束名(列名);
    举例
    CREATE TABLE stuinfo(
  id INT,
  stuname VARCHAR(20),
  gender char(1),
  seat INT
)
#列级约束的写法
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) UNIQUE;
#表级约束的写法
ALTER TABLE stuinfo ADD  UNIQUE(seat);
# 添加外键，添加外键时，必须保证主表存在，并且关联的字段是唯一字段，并且类型一致或者兼容
ALTER TABLE stuinfo ADD 
CONSTRAINT fk_stuinfo_major 
FOREIGN KEY(majorid) REFERENCES major(id);

    7. 删除约束

    语法
    ##删除非空
ALTER TABLE stuinfo MODIFY 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

    五、标识列

    又称为自增长列 含义：可以不用手动的插入值，系统提供默认的序列值

创建表时设置标识列

CREATE TABLE stuinfo(
    id INT PRIMARY KEY auto_increment,
    stuname VARCHAR(20),
    gender char(1),
    seat INT
);
INSERT INTO stuinfo VALUES(null,'l1','男',10);
INSERT INTO stuinfo VALUES(null,'l2','男',10);
INSERT INTO stuinfo VALUES(null,'l3','男',10);
INSERT INTO stuinfo VALUES(null,'l4','男',10);

设置自增长的步长

# 查看自增长的起始值 offset 和步长 increment
SHOW VARIABLES LIKE '%auto_increment%';
# 起始值不可被修改，但是步长可以被修改
SET auto_increment_increment=3;

需要删除之前表中的全部数据，才会有这样的效果。

特点：

  1. 标识列必须是一个键，主键或者是唯一键
  2. 仅仅只能有一个增长列
  3. 标识列的类型必须是数值类型（小数和整数都可以）
  4. 标识列可以设置步长，但是不支持设置起始值，不过可以通过手动插入第一条数据的时候给予值的形式来给定值。

修改表时设置标识列

ALTER TABLE stuinfo MODIFY COLUMN id INT auto_increment;

修改表时删除标识列

ALTER TABLE stuinfo MODIFY COLUMN id INT;

六、TCL语言

Transaction Control Language，事务控制语言 事务： 一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

1. 存储引擎【了解】

概念：在MySQL中数据用各种不同的技术存储在文件（或内存）中
通过show engines; 来查看MySQL支持的存储引擎
在MySQL中用的最多的存储引用有innodb，myisam，memory等。其中innodb支持事务，而myisam、memory等不支持事务。

2. 事务的特性（ACID）

  1. 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  2. 一致性（Consistency） 事务必须使数据库从一个一致性状态下变换到另一个一致性状态 理解：如果事务的操作不符合规范，会破会系统的正确性，这个事务就不会被执行，而回回滚到未执行前的状态。事务通过其它三个特性来保证这个特性。
  3. 隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作即使用的数据对并发的其他事务是隔离的，并发的各个事务之间不能相互干扰

  4. 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

    3. 事务的创建

    隐式的事务：事务没有明显的开启和结束的标志。比如insert、update、delete语句都是隐式事务。
    查看自动提交是否开启：

    SHOW VARIABLES LIKE 'autocommit';

    显式事务：事务具有明显的开启和结束的标记。
    前提：必须先设置自动提交功能为禁用。

    ## 步骤1
set autocommit = 0;
start transaction;#可选，这句话可写可不写
## 步骤2，编写事务中的sql语句（DML+DQL，别的不支持）
sql1;
sql2;
...
##步骤3：结束事务
commit;提交事务
rollback;回滚事务

    示例

    SET autocommit=0;
UPDATE account
SET balance = balance + 500
WHERE username = '张无忌';
UPDATE account
SET balance = balance-500
WHERE username = '赵敏';
ROLLBACK;
commit;

  5. 事务的结束有两种，commit或者是rollback

  6. 选择commit会将事务执行的结果持久化存入数据库中。rollback则会退回到事务执行之前。
  7. 对于MySQL数据库来说，事务的执行无论有没有错误，执行commit都会将结果持久化，而执行rollback也不管事务是不是执行顺利，会将事务进行回滚。

  8. 事务要配合应用程序使用，让应用程序去判断事务中每条语句的执行结果，如果没有问题，最后选择commit进行提交，如果出现了错误，就使用rollback进行提交。

    4. 事务的并发产生的问题

    对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题

  9. 脏读：对于两个事务T1，T2。T1读取了已经被T2跟新但还没有被提交的字段。之后若T2回滚，T1读取的内容就是临时且无效的。

  10. 不可重复读：对于两个事务T1，T2。T1读取了一个字段，然后T2更新了改字段。之后，T1再次读取同一个字段，值就不同了。

  11. 幻读：对于两个事务T1，T2.T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后T1再次读取同一个表，就会多出几行。 理解：就是在事务没有结束的情况下，能够读到别人插入的数据。 前面的脏读是读到别人没有提交的数据 不可重复读是读到别人提交的数据，包括增删改，可重复读是指同一条数据不会发生改变，但是对于插入的新数据，不去管它。 而幻读是只读到别人插入的数据，不包括增删。

    5. 事务的隔离级别

    数据库系统必须既有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。

  12. 读未提交 允许事务读取未被其他事务提交的数据。这是最低的隔离级别，所以之前所有的问题都会出现。

  13. 读已提交 只允许事务读取已经被其它事务提交的数据，可以避免脏读，但是不可重复读和幻读问题仍然可能出现
  14. 可重复读 确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其它事务对这个字段进行更新，可以避免脏读和不可重复读。
  15. 串行化 却表事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所用并发问题都能避免。 会阻塞其它事务的操作，等待当前事务结束

不同的数据库对隔离级别的支持

  • Oracle支持2中事务隔离级别：读已提交、串行化。默认为读已提交
  • MySQL支持的4中隔离界别，默认为可重复读。
    ## 查询隔离级别
SELECT @@tx_isolation;
## 设置当前的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别; 
##例子
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;  
##  设置数据库的全局的隔离级别
SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL 隔离级别;  
read uncommitted
read committed
repeatable read
serializable

    6. 回滚点

    #3. 演示 savepoint 的使用
SET autommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
SAVEPOINT a;
DELETE FROM account WHERE id = 28;
ROLLBACK TO a;## 回滚到a点

    回滚点 savepoint只能配合 rollback 语句使用，rollback是默认回滚到事务开启前的数据，而回滚点则可以设置回滚的时机。

7. delete 和 truncate 在事务使用时的区别

delete可以被回滚
truncate 不能被回滚

七、视图

MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定视图的查询中使用的表，并且在使用视图时动态生成，只保存了sql逻辑，不保存查询结果。

理解：比如我现在做一个业务，是关联到多张表的，这个时候我要把查询的结果放在同一张虚拟表中。视图就是这么一张虚拟表，但是视图存储的不是这张表查询的数据，而是查询的逻辑过程，当数据发生改变时，视图中的数据也会相应的发生改变。 总结：视图就是对sql进行的封装。
应用场景

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

    1. 视图的创建和使用

    语法：
    CREATE VIEW 视图名字
AS
查询语句;
    示例：
    CREATE VIEW myview1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
    结果：

视图的使用

SELECT * FROM myview1 WHERE last_name LIKE '%a%';

和普通表的使用没有任何区别。

视图的好处

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

    2. 视图的修改

    # 方式一：如果视图不存在就创建，如果存在就修改
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
# 方式二： 
ALTER VIEW 视图名
as
查询语句;

    3. 删除视图

    DROP VIEW 视图名1,视图名2,...;

    4. 更新视图

    CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary
FROM employees;
SELECT * FROM myv1;
# 1. 向视图中插入数据
INSERT INTO myv1 VALUE('张飞','780574861@qq.com',1200);
#2. 修改视图中的数据
UPDATE myv1
SET last_name='张无忌'
WHERE last_name = '张飞';
#3. 删除视图
DELETE FROM myv1 WHERE last_name = '张无忌';
    特点：
  1. 可以操作普通表的语句也同样可以操作视图
  2. 在对视图中的数据进行修改的同时，也会对视图相关的表中的数据进行修改
  3. 视图中的数据不是都能修改成功，如果字段具备如下特点，就不能进行修改了
  • 包含：分组函数、distinct、group by、having、union、union all
  • 常量视图
  • select 中包含子查询
  • join（可以修改，不能删除和插入）
  • from 一个不能更新的视图
  • where 子句的子查询引用了from子句中的表
  1. 因此这样的话，视图就存在一定的安全隐患，也失去了保护数据安全的功能。

  2. 可以赋予视图不同的权限，来确保数据的安全性

    5. 视图和表的对比

  3. 创建的语法 视图 create view ，表 create table

  4. 视图没有保存逻辑，只保存了sql的逻辑，而表是真正表存了数据的
  5. 视图一般不能增删改，表可以

    八、MySQL的变量

    MySQL中的变量可以分为：系统变量和自定义变量。系统变量又可以分为：全局变量、会话变量。自定义变量可以分为：用户变量、局部变量

1. 系统变量

系统变量由系统提供，不是用户定义，属于服务器层面使用的语法：

# 1. 查看所有的系统变量
SHOW GLOBAL|【SESSION】 VARIABLES;
## 什么关键字都不写默认是会话变量，只在一次会话
## 全局变量在别的连接一样起作用，session变量只在此次会话起作用。
 # 2. 查看盘主条件的部分系统变量
SHOW GLOBAL|【SESSION】 VARIABLES LIKE VARIABLES LIKE '%char%';
#3. 查看某个指定的系统变量的值
SELECT @@global|【SESSION】.系统变量;
# 4. 为某个系统变量赋值
SET GLOBAL|【SESSION】 系统变量名-值;
#或者
SET @@global|【SESSION】.系统变量名 = 值;

2. 自定义变量

变量是用户自定义的，不是系统自带的 作用域：针对当前会话（连接）有效

2.1 用户变量

#1. 声明并初始化 
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名:=值;
# 赋值（更新用户变量的值）
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名:=值;
## 用户变量为弱类型，所以可以
SET @woaini = 'lyx';
SET @woaini = 100;
# 赋值的另一种方式
SELECT COUNT(*) INTO @count FROM employees;

作用域：可以用在任何地方。

##2. 赋值
# 方式一：
SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT @局部变量名:=值;
# 方式二
SELECT COUNT(*) INTO 局部变量名 FROM employees;

应用：begin end 中的第一句话。

2.3 例子

## 1. 用户变量
SET @m = 1;
SET @n = 2;
SET  @sum=@m+@n;
SELECT @sum;
## 2. 局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECIMAL sum INT ;
SET sum = m+n;
SELECT sum;## 语法报错，因为局部变量只能用在begin end中

九、存储过程和函数

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

好处：

  1. 提高代码的重用性
  2. 简化操作
  3. 减少了编译次数和数据库连接次数，提高了效率

    1. 语法

    ```

    创建语法

    CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体（一组合法的SQL语句） END /* 注意：
  4. 参数列表包含三部分 参数模式 参数名 参数类型 举例： IN stuname VARCHAR(20)
  5. 参数模式包括 IN：该参数可以作为输入，也就是该彩塑需要条用方传入值 OUT：该参数可以作为输出，也就是该参数可以作为返回值 INOUT：该参数既可以作为输入，又可以作为输出
  6. 如果存储过程体仅仅只有一句话，begin end可以省略。
  7. 存储过程体中的每条SQL语句的结束要求必须加分号。
  8. 存储过程的结尾可以使用 delimiter 重新设置。 语法： delimiter 结束标记 delimiter $ */

    调用语法

    CALL 存储过程名(实参列表);
    <a name="kfvgN"></a>
## 2. 案例

    空参列表

    案例1：插入到admin表中五条记录

    DELIMITER $ CREATE PROCEDURE myp1() BEGIN INSERT INTO admin(username,password) VALUES(‘lyx’,’777’),(‘lzh’,’999’),(‘lzh’,’999’),(‘lzh’,’999’),(‘lzh’,’999’); END $

    调用

    delimiter 执行之后，就会将结束符定义为 $ 符号，但是 navicat 会自动转回 ;

    只有在命令行的时候才会在修改后所有的sql语句的结束符号都为 $

    CALL myp1()$

    有参无返回值

    案例1：创建存储过程，根据女神名，查询对应的男神信息

    CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty ON bo.id = b.boyfriend_id WHERE b.name = beautyName; END $ CALL myp2(‘周芷若’)$

    案例2：创建存储过程实现，用户是否登陆成功

    案例2：创建存储过程实现，用户是否登陆成功

    CREATE PROCEDURE myp3(IN username VARCHAR(20),IN password VARCHAR(20)) BEGIN SELECT IF(COUNT()>0,’登陆成功’,’登陆失败’) AS result FROM admin WHERE admin.username = username AND admin.password = password; # 同名变量采用就近原则，因此想要指定区分表中的字段，可以使用 表名.变量名 END $ CALL myp3(‘lyt’,’6666’)$ CREATE PROCEDURE myp3(IN username VARCHAR(20),IN password VARCHAR(20)) BEGIN DECLARE result INT DEFAULT 0; SELECT COUNT() INTO result FROM admin WHERE admin.username = username AND admin.password = password; SELECT IF(result>0,’登陆成功’,’登陆失败’); END $ CALL myp3(‘lyt’,’6666’)$

    创建带out模式的存储过程

    案例1：根据女神名返回对应的男神名

    CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyName INTO boyName FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName;

END $ CALL myp4(‘周冬雨’,@boyName)$ SELECT @boyName $

案例2：根据女神名，返回对应的男神名和男神魅力值

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT) BEGIN SELECT bo.boyName,bo.userCP INTO boyName,userCP FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName; END $ CALL myp5(‘周冬雨’,@boyName,@userCP)$ SELECT @boyName,@userCP $

带 INOUT模式的存储过程

案例1：传入a和b两个值，最终a和b都翻倍并返回

CREATE PROCEDURE myp6(INOUT a int ,INOUT b INT) BEGIN SET a = a2; SET b = b2; END $ SET @a = 10$ SET @b = 10$ CALL myp6(@a,@b)$ SELECT @a,@b$

<a name="HSpQ5"></a>
## 3. 删除存储过程

语法： DROP PROCEDURE 存储过程名； 举例 DROP PROCEDURE myp6;

> 存储过程一次只能删除一个

<a name="2lAuF"></a>
## 4. 查看存储过程

语法： SHOW CREATE PROCEDURE 存储过程名; 举例 SHOW CREATE PROCEDURE myp2;

<a name="GkET1"></a>
## 5. 存储过程的修改
> 存储过程中的sql不能被修改，只能删除之后重建，不过可以修改存储过程的一些属性。

<a name="cEsNC"></a>
# 十、函数
> 和存储过程类似，不过不同与存储过程的地方在于，**函数只能有一个返回**，不能像存储过程那样有多个返回

<a name="kyuhI"></a>
## 1. 语法

创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END

调用语法

SELECT 函数名(参数列表);

**注意：**

- 参数列表包含两部分：参数名和参数类型
- 函数体：肯定会有return语句，如果没有会报错。如果return语句没有放在函数体的最后也不报错，但是没有意义。
- 如果函数体中只有一句话，begin end 可以省略
- 使用delimiter 语句设置借宿标记。
<a name="CZxCy"></a>
## 2. 案例

1. 无参有返回

案例：返回公司员工个数

CREATE FUNCTION fun1() RETURNS INT BEGIN DECLARE c INT DEFAULT 0;#定义变量声明 SELECT COUNT(*) INTO c FROM beauty; RETURN c; END $ SELECT fun1();

2. 有参有返回

案例1：根据员工名，返回它的工资

CREATE FUNCTION fun2(last_name VARCHAR(20)) RETURNS DOUBLE BEGIN DECLARE salary DOUBLE DEFAULT 0; SELECT e.salary INTO salary FROM employees e WHERE e.last_name = last_name; return salary; END $ SELECT fun2(‘Russell’)$

<a name="TN0i8"></a>
## 3. 函数的查看和删除

查看函数

SHOW CREATE FUNCTION fun2;

删除函数

DROP FUNCTION fun2;

<a name="FZMaE"></a>
# 十一、流程控制结构
<a name="RQyCE"></a>
## 1. 分支结构
<a name="paBWo"></a>
### 1.1 IF函数

IF(表达式1，表达式2,表达式3)

含义：如果表达式1成立，那么返回表达式2的值；如果表达式1不成立，则返回表达式3的值。<br />应用场景：任何可以使用函数的地方
<a name="SFLH4"></a>
### 1.2 case结构
作用：

1. 类似于Java中的switch语句，一般用于实现等值判断
1. 类似于Java中的多重if语句，一般用于实现区间判断

**语法：**

1. 类似与Java中switch语句的用法

case 变量|表达式|字段 when 要判断的值 then 返回的值1 when 要判断的值 then 返回的值2 … else 要返回的值n end

2.类似与Java中多重if的用法

case when 要判断的条件1 then 返回的值1 when 要判断的条件2 then 返回的值2 … else 要返回的值n end

3. 上面都是配合select语句来使用的，也可以单独使用（在存储过程或函数中）

case 变量|表达式|字段 when 要判断的值 then 执行的语句1; # 每条when语句都要加上分号 when 要判断的值 then 执行的语句2; … else 执行的语句3 end case; # end后面加上case并且加上分号

case when 要判断的条件1 then 执行的语句1; when 要判断的条件2 then 执行的语句2; … else 执行的语句n end case;

**特点**：

1. 可以作为表达式，嵌套在其它语句中使用，可以放在任何地方。
1. 可以作为独立的语句去使用，只能放在begin end 中。
1. 如果when中的值或者条件成立，则执行then后面的语句，并且结束case
1. else可以省略，如果else省略了，并且所有when条件都不满足，则返回null

**案例：**

CREATE PROCEDURE test_case(IN score INT,OUT slevel char(1)) BEGIN CASE WHEN score BETWEEN 90 AND 100 THEN SET slevel = ‘A’; WHEN score BETWEEN 80 AND 90 THEN SET slevel = ‘B’; WHEN score BETWEEN 60 AND 80 THEN SET slevel = ‘C’; ELSE SET slevel = ‘D’; END CASE; END $ CALL test_case(97,@level)$ SELECT @level $

<a name="6FhU5"></a>
### 1.3 if结构
> 实现多重分支

**语法：**

IF 条件1 THEN 语句1; ELSEIF 条件2 THEN 语句2; … [ELSE 语句n;] END IF;

应用场景：只能用在存储过程或者是函数当中<br />**案例：**

CREATE FUNCTION test_if(score INT) RETURNS char(1) BEGIN DECLARE level char(1) DEFAULT ‘D’; IF score BETWEEN 90 AND 100 THEN SET level = ‘A’; ELSEIF score BETWEEN 80 AND 90 THEN SET level = ‘B’; ELSEIF score BETWEEN 60 AND 80 THEN SET level = ‘C’; ELSE SET level = ‘D’; END IF; RETURN level; END $ SELECT test_if(89)$

<a name="LuhwI"></a>
## 2. 循环结构
> 分类：while、loop、repeat 循环跳出：iterate类似于continue；leave类似于break;

<a name="rijbv"></a>
### 2.1 while 循环
**语法：**

【标签:】 WHILE 循环条件 DO 循环体 END WHILE; 【标签】

> 标签用于循环控制

**例子：**

案例1：普通玄幻

CREATE PROCEDURE test_while(in num INT) BEGIN a:WHILE num>0 DO INSERT INTO admin(username,password) values(‘lzh’,num); set num = num-1; END WHILE a; END $ CALL test_while(5)$

案例2：添加循环控制的循环语句

CREATE PROCEDURE test_while(in num INT) BEGIN a:WHILE num>0 DO

INSERT INTO admin(username,password) values('lzh',num);
set num = num-1;
IF num = 2 THEN LEAVE a;
END IF;

END WHILE a; END $

案例3 iterate

CREATE PROCEDURE test_while(in num INT) BEGIN a:WHILE num>0 DO set num = num-1; IF num = 2 THEN ITERATE a; END IF; INSERT INTO admin(username,password) values(‘lzh’,num);

END WHILE a; END $

<a name="BsPKI"></a>
### 2.2 loop循环
**语法：**

【标签:】 loop 循环体; end loop 【标签】;

> 如果没有标签就会是一个死循环

<a name="i7jCe"></a>
### 2.3 repeat循环
**语法：**

【标签:】 repeat 循环体; until 循环结束的条件 end repeat 【标签】 ```

2.4 总结

旧版笔记 MySQL基础(DML) - 图6