第十周内容回顾

第十周内容回顾

1、存储引擎

我们可以简单的理解为存储引擎就是针对相同的数据采用不同的存取策略

show engines;  # 查看所有的存储引擎

需要掌握的存储引擎

1. MySAM
   mySQL5.5及之前版本默认的存储引擎
   存取数据的速度快，但是功能较少 安全性较低
2. InnDB
   MySQL5.5之后版本默认的存储引擎
   存取数据的速度灭有MyISAM快 但是支持事务、行锁、外键等诸多功能 安全性较高
3. Memory
   基于内存的存储引擎 存取数据极快 但是断电立刻丢失
4. BlackHole
   黑洞 任何写进去的数据都会立刻丢失

MySQL 中默认是大小写不敏感的（忽略大小写）

2、创建表的完整语法

create table 表名(
	字段1 字段类型(数字) 约束条件,
    字段名2 字段类型(数字) 约束条件,
    字段名3 字段类型(数字) 约束条件
)engine=存储引擎；

• 字段名和字段类型是必须的(至少写一个)
• 数字跟约束条件是可选的(可有可无)
• 约束条件可以写多个 空格隔开即可
• 最后一个字段的结尾千万不能加逗号

2.1、字段类型之整型

关键字	大小	有负号	没负号
tinyint	1bytes	-128~127	0~255
smallint	2bytes	-32768~32767	0~65535
int	4bytes	-2147483648~2147483647	0·4294967295
bigint	8bytes	-9223372036854775808~9223372036854775807	0~18446744073709551615

2.2、字段类型之浮点型

关键字
floate
double
decimal

使用方式

float(255,30)  # 第一个数表示总共多少位 第二个数表示小数占多少位
double(255,30) # 第一个数表示总共多少位 第二个数表示小数占多少位
decimal(65,30) # 第一个数表示总共多少位 第二个数表示小数占多少位

精确度
float < double < decimal
一般情况下 float 足够使用了 如果想追求非常玩美的精确度 可以使用字符串来代替

2.3、字段类型之字符类型

关键字	特点
char	定长
varchar	边长

char(4)
最大只能存储四个字符 如果超出范围 则直接报错
如果 不能超出范围 则用空格填充 至四个字符

varchar（4）
最大只能存储四个字符 如果超过范围 则直接报错
如果不超出范围 则有几位就存几位

2.3.1、两者区别

结果验证 超出范围两者都会报错

验证定长和边长

char_length()  # 统计字段数据的长度

默认情况下char在存储的时候整队没有满足固定位数的字符会自动填充空格
然后在读取的时候又自动将填充 的空格移除 如果想取消该机制 需要sql_mode

set global sql_mode='strict_trans_tables,pad_char_to_full_length';
上述目录是替换 不是新增 所以之前的配置也要写上

char VS varchar

• char
  整存整取 速度快
  浪费存储空间

• Varchar

  节省存储空间
  存取数据的速度慢于char

char(4)
通过空格 将数据 填充至四个字符 存入 取得时候直接四个字符四个字符方便取
a son jacktom lili

varchar(4)
存取数据都需要操作报头(耗时)
1bytes+a1bytes+son1bytes+jack1bytes+tom

存储人得姓名>>>>:varchar
两者使用频率都很高 现在默认很多时候是varchar

3、数字的含义

数字大部分情况下都是用来限制字段的存储长度 但是整型除外
整型不是用来限制存储的长度 而是展示的长度

create table t13(id int(3)); 
create table t14(id int(3) zerofill);

总结
以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数

4、字段类型之枚举与集合

枚举
多选一：enum

• 性别（男 女 其他）

create table t15(
	id int,
    name varchar(32),
    gender enum('male','female','others')

),

集合
多选一（包含多选一）：set

• 爱好（唱 跳 rep 篮球）

create table t16(
	id int,
    name varchar(32),
    hobbies set('read','run','music','rap')
);

5、字段类型之日起类型

关键字	类型
date	年月日
datatime	年月日时分秒
time	时分秒
year	年

create table t17(
	id int,
	name varchar(32),
 	birth date,
  	reg_time datetime,
 	study_time time,
 	join_time year
);

>insert into t17 values(1,'kk','2022-8-15','2022-8-15 15:15:15','15:15:15',2022)

6、字段约束条件

可以自定义字段顺序

insert into 表名(字段2,字段1) vlaues()

无负号
unsigned

create table t(id int unsigned); # 不能添加负数

零填充
zerofill

create table t(id int zerofill);  #填入得数据 展示会自动被零填充至 展示得长度

非空
not null

create table t(name varchar(32) not null); #不能不填如入数据

默认值
default

create table t(gender varchar(32) default 'male') #数据默认为male

唯一值
unique

create table t(id int unique); #单列唯一 出现重复添加报错

create table t2(host varchar,port int,unique) #创建 联合唯一
#当 host 和 port 合并 出现重复时 添加报错 

7、约束条件

1.1、主键：primary key

单从约束角度上而言 主键等于 非空且唯一 not null unque

create table t1(
	id int primary key,
    name varchar(32)
);

InnoDB 存储引擎规定一张表 必须且只有一个主键
如果创建的表中没有主键 也没有非空且唯一的字段 那么 InnoDB存储引擎会自动采用一个隐藏的字段座位主键（主键可以加快数据查询 类似于新华字典的目录）

如果创建的表中没有主键 但是有非空且唯一的字段 那么InnoDB存储引擎会自动将该字段设置为主键

create table t2(
	nid int not null unique,
    sid int not null unique,
    uid int not null unique,
    name varchar(32)
);

创建表的时候应该有一个id字段 并且该字段 应该作为主键
uid sid pid gid cid id

补充说明
单列主键：

create table t(id int primary key ,name varchar(32))

联合主键：

create table t(sid int,nid int,name varchar(32),primary key(sid,nid))；

1.2、自增 auto_increment

约束条件 不能单独使用 必须跟在键后面 （主要配合主键一起使用）

create table t3(
	id int auto_increment
    
);

there can be only one auto column and it must be defined as a key

create table t4(
	id int primary key auto_increment;
    name varchar(32)
);

补充说明：自增的特点
自增的操作不会因为执行删除数据的操作而回退 或重置

#如果 非要重置主键 需要格式化表
truncate 表名； #删除表数据 并重置主键值
# deletet from 并不会重置 键值

8、约束条件之外键

外键前戏
需要创建一张员工表

# 创建一张员工表
create table t3(id int primary key auto_increment,gender varchar(32),dep_name varchar(32),dep_desc varchar(32);

上述表的缺陷：

1. 表结构 不清晰 到底是员工表 还是 部门表
2. 字段数据反复存取 浪费存储空间 （不严重 无所谓）
3. 表的扩展性极差 牵一发动全身（很严重 效率极低）

优化操作>>>:拆表

id	name	gender
1	kk	男

id	dep_name	dep_desc
1

拆表之后解决了上述的三个问题 但是出现了一个致命的缺陷

解决措施

id	name	gender	dep_id

添加一个部门 编号字段填写部门数据的主键值

外键字段
专门用于记录表与表之间数据的关系

8.1、外键字段得创建

外键字段是用来记录表与表之间数据得关系 而数据得关系 有四种

• 一对多关系
• 多对多关系
• 一对一关系
• 没有关系

9.2、表数据得关系判定

换位思考

针对员工表 和 部门表 判断数据关系

1. 先站在员工表的坏的有

   问：一条员工数据 能否 对应多条部门数据
   翻：一名员工能否属于多个部门
   答：不可以

2. 再站在部门表的角度

   问：一条部门数据能否对应多员工数据
   翻：一个部门能否拥有多个员工
   答：可以

完成换位思考之后得出答案 一个可以 一个不可以
那么表关系就是 一对多
部门是一 员工是多
针对 一对多 的关系 外键字段建在多的一方
没有多对一 统称为 一对多

9 、Foreign Key

先写普通字段 再写外键字段

create table emp(
	id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);

1. 创建表的时候需要先创建被关联的表（没有外键） 然后再是关联表（有外键）

2. 插入表 数据的时候 针对外键字段 只能填写被关联表字段已经出现过的数据值

3. 被关联字段无法修改和删除

   有点 不太好 操作限制性太强

级联更新、级联删除
被关联的数据一旦变动 关联的数据同步变动

create table emp1(
	id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep1(id)
    on update cascade #级联更新
    on delete cascade #级联删除
);
create table dep1(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);

扩展 :

在实际工作中 很多时候可能并不会使用外键
因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
我们为了能够描述出表数据的关系 又不想使用外键
自己通过写SQL 建立代码层面的关系

9、表关系之多对多

以书籍表和作者为例

1. 先站在书籍表的角度

   问：一条书籍 数据能否对应多条 作者数据

   答：可以

2. 再站在作者表的角度

   问：一条作者数据能否应对多条书籍数据

   答：可以

总结：两边都可以 那么表数据关系就是多对多

4.1、多对多 实现

#需要单独开设第三张关系表 存储数据关系
create table book(
	id int primary key auto_increment,
    title varchar(32)
);
create table author(
	id int primary key auto_increment,
    name varchar(32)
);
create table book2author(
	id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade  #级联更新
    on delete cascade, #级联删除
	author_id int,
    foreign key(author_id) references author(id)
    on update cascade #级联更新
    on delete cascade #级联删除
);

5、表关系之一对一

以用户表和用户详情表

1. 先站在用户表的角度

   问：一条用户数据 能否对应多个用户详情数据

   答：不可以

2. 再站在用户详情表的角度

   问：一条用户详情数据 能否对应

   答：不可以

总结：
两边都不可以 那么先考虑是不是没有关系
如果有关系 那么肯定就是一对一
针对一对一的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询

create table user(
	id int primary key auto_increment,
    name varchar(32)
    detail_id int unique,
    foreign key(detail_id) references userDetail(id)
    on update cascade #级联更新
    on delete cascade #级联删除
);
create table userDetail(
	id int primary key auto_increment,
    phone bigint
);

6、操作表的SQL语句补充

修改表名

alter table 表名 rename 新表名；

新增字段

#默认新增在末尾
alter table 表名 add 字段名 字段类型(数字) 约束条件;

#新增在该已存在字段后面
alter table 表名 add 字段名 字段类型(数字)约束条件 after 已经存在的字段;

#新增在 第一列的字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;

修改字段

# 利用 changge
alter table 表名 change  新字段 字段类型（数字） 约束条件;

#利用 modify
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;

删除字段

alter table 表名 drop 字段名;

7、查询关键字

7.1查询关键字之select与from

SQL语句的关键字编写顺序与执行顺序是不一致的！！

select name from emp;
先找到这张表 之后再取这张表里查找 name 字段

编写SQL语句针对select 和 from 可以先写个固定模板

select * from 表名 其他操作

select 后的字段可能是实际的 也可能是通过SQL动态产生的 所以可以先用*占位最后再修改

select
自定义查询表重字段对应的数据

7.2、查询关键字where筛选

查询id 大于等于3小于等于6的数据

# 方法一:
select * from emp where id >= 3 and id <= 6;

#方法二：:
select *  from emp where id between 3 and 6;

查询薪资是20000或者18000 或者17000的数据

#方法一：
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;

#方法二：
select * from emp where salary in (20000,18000,17000); #支持成员运算

查询员工姓名中包含o 字母的员工姓名和薪资
刚开始编写mysql 查询不熟练的时候 可以暗中 查询的优先顺序 编写mysql语句

%匹配任意个数的 任意该字符

#模糊查询
select name,salary from emp where name like'%o%'

查询员工姓名是右四个字符组成的员工姓名和薪资

—匹配下划线个数的任意字符

#方法一 模糊查询：
select name,salary from emp where name like'____'  #四个下划线

#方法二:
select name,salary from emp where char_length(name) = 4;

查询 id 小于 3 或者大于6 的数据

# 查询 id 小于 3 或者大于6 的数据
select * from emp where id not between 3 and 6; # 不在3与6 之间的数据

查询薪资不在 20000，18000，17000 范围的数据

select * from emp where salary not in (20000,18000,17000);

**查询岗位描述为空的员工姓名与岗位名 **
针对 null 不能用等号，只能用is

select name,post from emp where post_comment = NULL; #不能用等号 查询不到

#岗位描述 为空的
select name,post from emp where post_comment is NULL;
#岗位描述 不为空
select name,post from emp where post_comment is not NULL;

7.3、查询关键字之group分组

分组：按照一些指定的条件将单个单个数据分为一个个整体
分组周我们研究的对象应该是以组为单位 不应该再直接获取单个数据项 如果获取了应该直接报错
select 后面可以直接填写的字段名 只能是分组的依据（其他字段需要借助于一些方法才可以获取）

select posy from emp froup by post; # 获取的是单条数据项 每条 都是 该组的 第一个 是不对的 应该直接报错
# 所以需要加限制
set global sql_mode='strict_trans_tables,only_full_group_by';
# 重启客户端

以岗位分为一组

select post from emp group by post;

一般 需求中 出现 每个、平均、最大、最小、等词汇 一般 就是 需要你用到分组

配合分组使用的常见聚合函数

聚合函数	作用
max	最大值
min	最小值
sum	总和
count	计数
avg	平均

• 以组为单位 统计组内数据>>> 聚合查询（聚集到一起得到一个结果）

获取每个部门的最高薪资

# 以 部门 为单位 分组 获取各组中（各部门） 中的最高薪资
select post,max(salary) from emp group by post; 

补充：在显示的时候 我们还可以给字段取别名

select post as '部门'，max(salary) as '最高工资' from emp group by post;

as 可以省略 但是不建议省 这样 命令开起来会很乱

每个部门的最低工资

select post,min(salary) from emp group by post;

每个部门的平均工资

select post,avg(salary） from emp group by post;

每个部门的工资总和

select post,sum(salary) from emp group by post;

每个部门的人数

select post,count(id) from emp group by post;

7.3.1、group_concat

group_concat(分组之后用) 不仅可以用来显示出分组完字段 还有拼接字符串的作用

select post,group_concat(name) from emp group by post;

# 可以在名字间添加 字符
select post,group_concat(name,'_SB') from emp group by post;

select post,group_concat(name,':',salary) from emp group by post;

select post,group_concat(salary) from emp group by post;

7.4、查询关键字之having过滤

where 与 having 的功能其实是一样的 都是用来筛选数据
只不过 where 用于分组之前的筛选 而having 用于分组之后的筛选
为了人为的区分 所以叫where 是筛选 having 是过滤

统计各部门年龄在30岁以上的员工 平均工资，并且保留平均工资大于10000的部门

select post，age(salary) from emp
where age >= 30 #分组前 
group by post # group by 以部门 分组
having avg(salary) > 10000 分组后

7.5、查询关键字之distinct去重

去重的前提是数据必须一模一样

select distinct age from emp;

7.6、查询关键字之order by排序

select * from emp order by salary asc; #默认升序排
select * from emp order by salary desc; #降序排


#先按照 age降序排，在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary asc;

先按照age降序排，在年纪相同的情况下 再按照薪资升序排

select * from emp order by age desc,salary asc;

统计 各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

select post,avg(salary) from emp
where age > 10 #筛选 年龄大于10
group by post  # 以部门 分组
having avg(salary) > 1000  # 筛选 薪资大于1000的 
order by avg(salary);  # 最后 获取 薪资平均值

7.7、查询关键字之limit 分页

限制 展示条数

# 限制 展示条数
select * from emp limit 3;
# 查询工资 最高的人的详细 信息
select * from emp order by salary desc limit 1;

分页展示

select * from emp limit 0 ,5 # 第一个参数表示 起始位置，第二个参数表示的是条数，不是索引位置

select * from emp limit 5,5;

7.8、查询关键字之regexp正则

select * from emp where name regexp '^j.*(n|y)$';

8、多表查询思路

8.1、子查询

将一张表的查询结果括号括起来当作另外一条SQL’语句的条件
类似以日常生活中解决问题的方式
第一步干什么
第二步基于第一步的结果在做操作…

查询jason的部门名称

#先获取jason的部门编号
select dep_id from emp1 where name = 'jason'; #200
#根据部门编号获取部门名称
select name from dep1 where id = 200;

#子查询 
select name from dep1 where id = (select dep_id from emp1 where name = 'jaosn');

8.2、连表操作

关键字	功能
inner join	内连接
left join	左连接
right join	右连接
union	全连接

#内连接
select * from dep1 inner join emp1 on emp1.dep_id = dep1.id
#左连接
select * from dep1 left join emp1 on emp1.dep_id = dep1.id
#右连接
select * from dep1 right join emp1 on emp1.dep_id = dep1.id
#全连接
select * from dep1 left join emp1 on emp1.dep_id = dep1.id union
select * from dep1 right join emp1 on emp1.dep_id = dep1.id;

9、python 操作MySQL

第三方模块：pip3 insertall pymysql

import pymysql

# 连接服务端
conn = pymysql.connect(
	host = 127.0.0.1,
    port=3306,
    user='root',
    password='123',
    database='db2',
    charset='utf8mb4',
    autocommit = True #执行增、改、删操作自动执行 conn.commit
)
#产生一个游标对象（等输入命令）
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
#编写SQL语句
sql1 = 'select * from user'
# 发送给服务器
cursor.execute(sql1)
#获取命令的执行结果
res = cursor.fetchall() #获取所有的结果
print(res)

9.1、获取结果

cursor.fetchone()  # 获取结果集中一条数据
cursor.fetchall()  # 获取结果集中所有数据
cursor.fetchmany(1)  # 获取结果集中指定条的数据

类似于光标的概念

cursor.scroll(2,mode='relative')  # 基于当前位置往后移动
cursor. scroll(0,mode='absolute')  #基于数据集开头的位置往后移动

9.2、SQL注入问题

问题
之需要用户名即可登录
不许哟啊用户名也能登录

select * from userinfo where name='jason' --haha' and pwd='
select * from userinfo where name='xyz' or 1=1 -- heihei ' and pwd'

本质
利用一些特殊符号的组成产生了特殊的含义从而逃脱了真正的业务逻辑

措施
针对用户输入的数据不要自己处理 交给专门阿方法自动过滤

import pymysql

#连接服务器
conn = pymysql.connect(
	host = '127,0.0.1',
    port = 3306,
    user = 'root',
    password = '123',
    database = 'db2',
    charset = 'utf8mb4',
    autocommit=True #执行增 删 改 操作自动执行 conn.commit
)

#产生一个游标对象(等待输入命令) 固定搭配
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

#获取用户数据
user_name = input('username>>>').strip()
user_pwd = input('userpwd>>>>').strip()

#编写sql语句
sql1 = 'select * from user where name=%s and pwd=%s'

#发送给服务端
cursor.execute(sql1,(user_name,user_pwd))
#获取命令 的执行结果
res = cursor.fetchone()
if res:
    print('登录成功')
else:
    print('账号或密码错误')
    

补充

cursor.executemany()  # 可以一次性插入多行数据

10、关于MySQL知识点补充

as语法
给字段起别名 给表起别名

comment语法
给表、字段添加注释信息

create table server(id int) comment '这个server意思是服务器表'
	create table t1(
    	id int comment '用户编号',
       name varchar(16) comment '用户名'
    ) comment '用户表';

• 查看注释

  show create table #查看字段注释
  use information_schema #查看表注释
  

concat、concat_ws语法
concat用于分组之前多个字段数据的拼接
concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码

4.exists语法

select * from userinfo where exists (select * from department where id<100)
#exists后面的sql语句如果有结果那么执行前面的sql语句  如果没有结果则不执行

11、视图

sql语句 的执行结果是一张虚拟的表 我们可以基于该表做其他操作
如果这张表虚拟表 需要频繁使用 那么为了方便可以将虚拟表保存起来 保存起来之后 就称之为视图（本质就是一张表）

create view 视图 as SQL 与语句;

create view teacher2 course as select * from teacher inner join course on teacher.tid = course.teacher_id;

1. 在硬盘中，视图只有在表结构文件，没有表数据文件
2. 视图通常用于查询，尽量不要修改视图中的数据

视图能尽量少用就尽量少用

12、触发器

针对表数据的增、 改、删 自动触发的功能（增前、增后、改前、改后、删前、删后）

语法结构

create trigger 触发器命名 before/after insert/update/delete on  表名 for each row begin
	sql 语句 #执行 取决于上述 表 是否做了 上述操作
end

注意触发器颞部的SQL vyju需要用到分号 凡是分号又是SQL语句默认的结束符 所以为了能够 完整的写出触发器 的代码 需要临时 修改 SQL语句 默认的结束符

delimiter $$
编写需要用到分号的各种语句
delimiter ;

13、事务

事务的四大特性（ACID）

• A：原子性
  • 整个事务中的所有，要么全部完整 要么全部 不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
• C：一致性
  • 事务开始之间和事务结束之后，数据库的完整性限制未被破坏。一致性 包括两方面的内容，分别是约束一致性和数据一致性。
• |：隔离性
  • 隔离性：指的是一个书屋的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的开发事务是隔离的。
• D：持久性
  • 指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，并不会被回滚，后续的操作或故障不应该对其有任何影响。

13.1、SQL语句

修改数据之间 先开启事务所

start transaction;

修改 数据之前 先开启事务所

start transaction;

修改操作

update user set balance = 900 where name = 'jason'; #买支付100元
update user set balance = 1010 where name = 'kevin'; # 中间拿走10元
update user int balance = 1090 where name = 'tank'; #卖家拿到90元

回滚到上一个状态

rollback;

刷新到硬盘（开始事务所之后，只要 没有执行commit 操作， 数据其实没有真正刷新到硬盘）

commit;

13.2、python 角度 底层原理

try:
	update user set balance=900 where name='jason'; #买支付100元
   	update user set balance=1010 where name='kevin'; #中介拿走10元
 	update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
 	rollback;
else:
 	commit;

13.3、事务扩展知识点

MySQL提供两种 事务类型存储引擎INNodb NDB cluster及第三方XtraDB、PBXT
事务处理种有几个关键词反复出现

事务（transaction）
回退（rollback）
提交（commit）
保留点（savepoint）
为了支持回退部分事务处理，必须能在事务处理块合适的位置放置占位符，这样 如果需要回退可以回退到某个占位符（保留点）
创建占位符可以使用 savepoint
savepoint sp01;

回退到占位符地址
rollback to sp01;

保留点在执行rollback 或者 commit 之后自动释放

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务所做的修改
InnDB 支持所有隔离界别

set transaction isolation leval 级别

read uncomitted（未提交读） 【脏读】
事物中的修改即使没有提交，对其他事物也都是可见的，事物可以读取未提交的数据，这一现象也称之位脏读

read committed（提交读） 【不可重复读】
大多数 数据库系统默认的隔离级别
一个事物从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做不可重复读

repeatable read（可重复读） MySQL默认隔离界别
它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
能够解决“脏读”问题，但是无法解读“幻读”

phantom Read幻读 现象
所谓幻读指的是当某个事物在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生换行，InnoDB和XtraDB通过多版本并发控制（MVCC）及间隙繁琐策略解决该问题

serializable（可串行读）
强制事务串行执行，很少使用该级别

事务日志可以帮助提高事务的效率
存储引擎在修改表的数据时 只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，>而不用每次都将修改的数据本身持久到硬盘
事务日志采用的是追加方式 因此写日志操作 是磁盘上一小块区域内的顺序IO而不像随机IO需要在>磁盘的多个地方 移动磁头所采用的事务日志的方式相对来说要快的多
事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是 这样实>现的，通常称之位预写式日志 修改数据需要写两次磁盘

13.4、MVCC多版本并发控制

MVCC只能在 read committed（提交读）、repeatable read（可重复读）两种隔离级别下工作，其他两个不兼容（read uncommitted：总是读取最新 serializable：所有的行都加锁）

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间（或删除时间）本质是系统版本号

一个列保存了行的过期时间（或删除时间）本质是系统版本号
每开始一个新的事物版本号都会自动递增，事物开始时刻的系统版本号会作为事物的版本号用来和查询到的每行记录版本号进行比较

例如
刚插入第一条 数据的时候，我们默认事物id为1，实际是这样存储的

username	create_version	delete_version
jason	1

可以看到我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事物id
然后我们呢将jason修改为jason01，实际存储是这样的

username	create_version	delete_version
jason	1	2
jason01	2

可以看到，update的时候，会先将之前的数据delete_version 标记为当前新的事物id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事物id

当我们删除数据的时候，实际存储是这样的

username	create_version	delete_version
username	create_version	delete_version
jasion01	2	3

由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：

1. 当事物id要大于或等于当前的create_version 值，这表示在事物开始签这行数据已经存在了。
2. 当前事务id要小于delete_version 值，这表示在事务开始之后这行记录才被删除。

14、存储过程

类似于python中的自定义函数**

语法格式

delmiter 临时结束符
create procedure 名字(参数，参数)
begin
	sql语句;
end 临时结束符
delimiter ；

delimiter $$  #临时修改 结束符
create procedure p1(
	in m int, in  # in表示这个参数必须只能传入不能返回出去
    in n int， in 
    out res int  # out表示这个参数可以被返回出去，还有一个inot 表示即可传入也可以被返回出去
)
begin
	select tanme from teacher where tid > m and tid < n;
	set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ; #改回结束符  

#针对 res 需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1.5,@res) 调用
select @res 查看

查看存储过程具体信息
	show create procedure pro1;
查看你所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro1;

15、内置函数

可以通过 help 函数名 查看帮助信息

移除指定字符

select Trim()  # 默认移除两边空额

select LTrim  # 默认移除右边空格

select RTrim # 默认移除左边空格

大小写转换

select Lower 全部变小写

select Upper 全部变大写

获取左右起始 指定个数字符

select Left 从左边开始获取指定个数字符

select Right  从右边开始 获取指定个数字符

返回读音相似值（对英文效果）

where Soundex(name) = Soundex('')  # 获取发音相似的数据

• 用户 输入错误名字 可以尝试使用soundex 匹配发音来查找类似的数据

15.1、日期格式：date_format

在MySQL 中 表示 时间格式尽量采用 2022-11-11形式

按年月分组

select date_format(sub_time,'%Y-%M'),count(id) from blog group by ddate_format(sub_time,'%Y-%m'); #按年月分组 获取 年月 的文章id 计数

按年月日 筛选 获取

select * from blog where Date(sub_time) = '2015-03-01';

按年月筛选

select * from blog where Year(sub_time)=2016 AND Month(sub_time)=07;

日期处理拓展

addddate #增加一个日志
addtime  #增加一个世家
datediff #计算两个日期

16、流程控制

if 条件语句

delimiter // #临时修改结束符
create procedure proce_if()
begin
	declare i int default 0;
	set num = 0;
	whele num < 10 do
		select
			num ;
		set num = num + 1;
	end whtle ;
	
end //
delimiter;

17、索引

索引 就好比一本书的目录，它能让你更块的找到自己想要的内容
让获取的数据更有目的性， 从而提高数据检索数据的性能

索引在MySQL中也叫做键s是存储引擎用于快速找到记录的一种数据结构

• primary key 主键索引
• unique key 唯一索引
• index key 普通索引

上述的三种键 在数据查询的时候使用都可以加快查询的速度
primary key unique key 除了可以加快数据的查询还有额外的限制
index key 只能加快数据查询 本身没有任何的额外限制

真正理解索引 加快数据查询的含义
索引的存在可以加快数据的查询 但是会减慢数据的增删

索引相关概念
基于课上讲解自行总结

7.1、索引底层原理

树：是一种数据结构 主要用于数据查询的操作

二叉树：两个分支

B树（B-树）、B+树、B*树

• B树（B-树）：

  • 除了叶子节点可以有多个分支 其他节点最多只能两个分支
  • 所有节点都可以直接存放完整数据（每一个数据块是有固定大小的）

• B*树：

  • 在树节点添加了通往其他节点的通道 减少查询次数

8、慢查询优化

explain 查看SQL语句 等级
索引扫描等级

1. index
2. range
3. ref
4. eq_ref
5. const
6. system
7. null

从上到下，性能从最差到最好，我们认为只好啊要达到range级别

使用方式

 explain select name from d5 where id=1;

全盘扫描：在explain 语句结果为ALL

什么时候出现全盘扫描

1. 业务确实需要获取所有数据
2. 不走索引 导致了全盘扫描
   1. 没有索引
   2. 索引创建有问题
   3. 语句有问题

生产中，MySQL在使用全表扫描时的性能是极差的，所以MySQL尽量避免出现全表扫描