目录
- 字符编码与配置文件
- 存储引擎
- 创建表的完整语法
- 字段类型之整型
- 字段类型之浮点型
- 字段类型之字符类型
- 数字的含义
- 字段类型之枚举与集合
- 字段类型之日期类型
- 字段约束条件
- 约束条件
- 约束条件之外键
- Foreign Key
- 表关系之多对多
- 表关系之一对一
- 操作表的SQL语句补充
- 表查询关键字
- 查询关键字之select与from
- 查询关键字之where筛选
- 查询关键字之group by分组
- 查询关键字之having过滤
- 查询关键字之distinct去重
- 查询关键字之order by排序
- 查询关键字之limit分页
- 查询关键字之regexp关键字
- 多表查询思路
- 子查询
- 连表查询
- python操作MySQL
- SQL注入问题
- 小知识点
- 视图
- 触发器
- 事务
- MVCC多版本并发控制
- 存储过程
- 内置函数
- 流程控制
- 索引
- 索引底层原理
- 慢查询优化
- 测试索引
- 联合索引
字符编码与配置文件
\s 产看数据库基本信息（用户、字符编码）
my-default.ini # windows下MySQL默认的配置文件
拷贝上述文件并且重命名为my.ini
由于5.6的版本编码不同意 会造成乱码 我们进行统一修改>>>:utf8
添加字符编码相关的配置(百度差看即可 下方为可用配置)
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
需要注意事项如果配置文件设计到了MySQL相关的配置修改 那么需要重启服务端才可以生效
ps： 在配置文件中MySQL可以提前写好用户名和密码 之后直接MySQL登录
存储引擎
存储引擎可以简单理解为是针对相同的数据采用不同的存取策略
show engines; 查看所有的存储引擎
需要掌握的存储引擎（4种）
1.MyISAM
MySQL5.5之前版本默认的存储引擎
存取数据的速度快 但是功能较少 安全性较低
2.InnoDB
MySQL5.5之后版本默认的存储引擎
存取数据的速度没有MyISAM快 但是支持事务、行锁、外键等诸多功能
安全性较高
3.Memory
基于内存的存储引擎 存取数据极快 但是断电会立刻丢失
4.BlackHole
黑洞 任何写进去的数据都会立刻丢失 类似于垃圾站
ps：MySQL中默认是大小写不敏感的（忽略大小写）
# 不同存储引擎之间底层文件的区别
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
ps: windows cmd终端鼠标右键的意思就是粘贴
InnoDB
.frm 表结构
.ibd 表数据、表索引（加快数据查询）
MyISAM
.frm 表结构
.MYD 表数据
.MYI 表索引(加快数据查询)
Memory
.frm 表结构
BlackHole
.frm 表结构
验证查看：
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
创建表的完整语法
create table 表名(
字段名1 字段类型(数字) 约束条件，
字段名2 字段类型（数字） 约束条件，
字段名3 字段类型（数字） 约束条件
)engine=存储引擎;
1.字段名和字段类型是必须的（至少写一个）
2.数字跟约束条件是可选的（可有可无）
3.约束条件可以写多个 空格隔开即可
4.最后一个字段的结尾千万不能加逗号
字段类型之整型
1.验证整型是否自带负号
create table t6(id tinyint)
insert into t6 values(-129),(256);
ps:发现自动填写为两个边界值 数据失真 没有实际意义
上述所有的整型类型默认都会带有负号
2.自定义移除负号
'''unsigned 约束条件之一 意思是不需要负号'''
create table t7 (id tinyint unsigned)
insert into t7 values(-129),(256);
"""
插入的数据值超出了数据类型的范围 不应该让其插入并自动修改 没有意义
数据库应该直接报错（这个特性其实是有的 只是被我们改了>>>:配置文件）
方式1:命令临时修改
set session sql_mode='strict_trans_tables' 当前客户端操作界面有效
set global sql_mode='STRICT_TRANS_TABLES' 服务端不重启永久有效
方式2:配置文件永久修改
[mysqld]
sql_mode='STRICT_TRANS_TABLES'
"""
字段类型之浮点型
float
double
decimal
三者都可以存储浮点型数据 但是各自的精确度不一致
使用方式
float(255,30) # 第一个数表示总共多少位 第二个数表示小数占多少位
double(255,30)
decimal(65,30)
验证精确度问题
create table t8(id float(255,30));
create table t9(id double(255,30));
create table t10(id decimal(65,30));
insert into t8 values(1.11111111111111111111111111111);
insert into t9 values(1.11111111111111111111111111111);
insert into t10 values(1.11111111111111111111111111111);
float < double < decimal
"""
一般情况下float足够使用了
如果想追求完美的精确度 可以使用字符串来代替
"""
字段类型之字符类型
char 定长
char（4）
最大只能存储4个字符 如果超过范围则直接报错
如果不超出范围 则用空格填充至4个字符
varchar 变长
varchar（4）
最大只能存储4个字符 如果超过范围则直接报错
如果不超过范围 则有几位就存几位
验证两者的区别
create table t11(id int, name char(4));
create table t12(id int, name varchar(4));
1.结果验证 超出范围两者都会报错
注意sql_mode='strict_trans_tables'
2.验证定长和变长特性
char_length() # 统计字段数据的长度
"""
默认情况下char在存储的时候针对没有满足固定位数的字符会自动填充空格
然后在读取的时候又会自动将填充的空格移除 如果想取消该机制 需要sql_mode
set global sql_mode='strict_trans_tables,pad_char_to_full_length';
上述目录是替换 不是新增 所以之前的配置也要写上
"""
3.char vs varchar
char
整存整取 速度快
浪费存储空间
varchar
节省存储空间
存取数据的速度慢于char
"""
char(4)
a son jacktom lili
varchar(4)
1bytes+a1bytes+son1bytes+jack1bytes+tom1bytes+lili
存取数据都需要操作报头(耗时)
存储人的姓名>>>:varchar
"""
两者使用频率都很高 现在默认很多时候是varchar
数字的含义
数字大部分情况下都是用来限制字段的存储长度 但是整型除外
不是用来限制存储的长度 而是展示的长度
create table t13(id int(3));
create table t14(id int(3) zerofill);
总结： 以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数
eg：
00000000013
00123123031
字段类型之枚举与集合
枚举
多选一
eg：性别（男 女 其他）
create table t15(
id int,
name varcher(32),
gender enum('male','female','others')
);
集合
多选多(包含多选一)
eg：爱好（唱跳rap）
create table t16(
id int,
name varchar(32).
hobbies set('read','run','music','rap')
);
字段类型之日期类型
date 年月日
datemtime 年月日时分秒
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,'curry','2000-06-28','2000-06-28 6:6:6','6:6:6','1999')
字段约束条件
"""
insert into 表名 values() # 默认按照创建表的字段顺序添加
insert into 表名(字段) values() # 可以自定义字段顺序
"""
1.unsigned 无负号
id int unsigned
2.zerofull 零填充
id int zerofull
3.not full 非空
name varchar(32) not full
4.default 默认值
name varchar(32) default 'curry'
5.unique 唯一值
id int unique # 单列唯一
host varchar(32)
port int
unique(host,port) # 联合唯一
约束条件
1.primary key 主键
1.单从约束角度而言 主键等价与非空且唯一 not full unique
create table t1(
id int primary key,
name varchar(32)
);
2.InnoDB存储引擎规定一张表必须有且只有一个主键
2.1如果创建的表中没有主键也没有非空且唯一的字段 那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键（主键也可以加快数据查询：类似于新华字典的目录）
2.2如果创建的表中没有主键但是有非空且唯一的字段 那么InnoDB存储引擎会自动将该字段设置为主键
crate table t2(
nid int not null unique,
sid int not null unique,
uid int not null unique,
name varchar(32)
);
3.创建表的时候都应该有一个'id'字段 并且该字段应该作为主键
uid、sid、pid、gid、cid、id
补充说明
id int primary key 单列主键
sid int,
nid int,
primary key(sid,nid) 联合主键
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)
);
补充说明:自增的特点
自增的操作不会因为执行删除数据的操作而回退或者重置
delete from
如果非要重置主键 需要格式化表
truncate 表名; # 删除表数据并重置主键值
约束条件之外键
铁铁 前戏参考之前博客。。。
1.外键字段的含义
专门用于记录表与表之间数据的关系
2.外键字段的创建
外键字段是用来记录表与表之间数据的关系 而且数据的关系有4种
一对多关系
一对一关系
多对多关系
没有关系
3.表数据关系的判定 >>> '换为思考'
eg：针对员工表和部门表判断数据关系
1.先站在员工表的角度。。。
2.再站在部门表的角度。。。
得出结论表关系是'一对多' 部门是一 员工是多
针对'一对多'的关系 外键字段建在多的一方
ps：没有多对一 统称为'一对多'
Foreign Key
1.先写普通字段
2.然后再写外键字段
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 建立代码层面的关系
"""
表关系之多对多
以书籍表和作者表为例
1.先站在书籍表的角度
问:一条书籍数据能否对应多条作者数据
答:可以
2.再站在作者表的角度
问:一条作者数据能否对应多条书籍数据
答:可以
总结:两边都可以 那么表数据关系就是'多对多'
针对多对多表关系 外键字段不能建在任意一方!!!
create table book(
id int primary key auto_increment,
title varchar(32),
author_id int,
foreign key(author_id) references author(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
book_id int,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
需要单独开设第三张关系表 存储数据关系
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 # 级联删除
);
表关系之一对一
以用户表和用户详情表
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
);
操作表的SQL语句补充
1.修改表名
alter table 表名 rename 新表名;
2.新增字段
alter table 表名 add 字段名 字段类型(数字) 约束条件;
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已经存在的字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
3.修改字段
alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;
4.删除字段
alter table 表名 drop 字段名;
表查询关键字
1.数据准备(直接拷贝)
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
查询关键字之select与from
"""
SQL语句的关键字编写顺序与执行顺序是不一致的！！！
eg： select name from emp;
肯定是先执行from确定表 之后执行select确定字段
编写SQL语句针对select和from可以先写个固定模板
selecr * from 表名 其他操作;
select后的字段可能是实际的 也可能是通过SQL动态产生的 所以可以先用* 占位最后再修改
"""
select
自定义查询表中字段对应的数据
from
指定操作的对象(具体是那张表 也可能是多张)
查询关键字之where筛选
1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); # 简写
# 3.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# 5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工名与岗位名 针对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;
查询关键字之group by分组
分组；按照一些制定的条件将一个个的数据分成一个个整体
"""
分组之后研究的对象应该是以组为单位 不应该再直接获取单个数据项 如果获得了应该直接报错 select后面可以直接填写的字段的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取)
set global sql_mode='strict_trans_tables,only_full_group_by';
"""
select * from emp group by post;
"""
我们写SQL是否需要分组 可以在题目中得到答案
每个、平均、最大、最小
配合分组常见使用的有聚合函数
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;
查询分组之后的部门名称和每个部门下所有的学生姓名
# group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
select post,group_concat(name) from emp group by post;
查询关键字之having过滤
where与having的功能其实是一样的 都是用来筛选数据
只不过where是永无分组之前的筛选 而having是用于分组之后的筛选
为了人为的区分 取名where是"筛选" 取名having是"过滤"
eg： 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
where与having的功能其实是一样的 都是用来筛选数据
只不过where用于分组之前的筛选 而having用于分组之后的筛选
为了人为的区分 所以叫where是筛选 having是过滤
1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
where age >= 30
group by post
having avg(salary) > 10000;
查询关键字之distinct去重
去重的前提是数据必须一模一样
select distinct age from emp;
查询关键字之order by排序
select * from emp order by salary asc; #默认升序排
select * from emp order by salary desc; #降序排
select * from emp order by age desc; #降序排
#先按照age降序排，在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary asc;
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
where age > 10
group by post
having avg(salary) > 1000
order by avg(salary)
;
查询关键字之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;
查询关键字之regexp关键字
select * from emp where name regexp 'o';
多表查询思路
子查询
将一张表的查询结果用括号括起来当做另一条SQL语句的条件
连表操作
将所有涉及到结果的数据统一连接起来 然后从大表中查询
#建表
create table dep1(
id int primary key auto_increment,
name varchar(20)
);
create table emp1(
id int primary key auto_increment,
name varchar(20),
gender enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep1 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保')
;
insert into emp1(name,gender,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
子查询
# 查询jason的部门名称
1.先获取jason的部门编号
select dep_id from emp1 where name = 'jason'; # 200
2.根据部门编号获取部门名称
select name from dep1 where id = 200;
子查询
select name from dep1 where id = (select dep_id from emp1 where name = 'jason');
连表查询
select * from emp,emp1; 笛卡尔积
'''我们不会使用笛卡尔积来求数据 效率偏低 连表有专门的语法'''
inner join 内连接
只拼接两边相同的数据
left join 左连接
以左表为基准 展示所有的数据 没有的对应填充NULL
right join 右连接
以右表为基准 展示所有的数据 没有的对应填充NULL
union 全连接
将两张表的数据全部连接起来
python操作MySQL
第三方模块:pip3 install pymsql
import pymysql
# 1.连接服务器
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='666',
database='db8',
charset='utf8mb4',
autocommit=True
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写SQL语句
sql1 = 'select * from emp'
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令的执行结果
res = cursor.fetchall()
print(res)
获取结果
cursor.fetchone() # 获取结果集中一条数据
cursor.fetchall() # 获取结果集中所有数据
cursor.fetchmany() # 获取结果集中指定条的数据
SQL注入问题
SQL注入问题
select * from userinfo where name='jason' -- haha' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- heihei' and pwd=''
本质
利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
措施
针对用户输入的数据不要自己处理 交给专门的方法自动过滤
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password)) # 自动识别%s 并自动过滤各种符合 最后合并数据
补充
cursor.executemany()
小知识点
1.as语法
给字段起别名、起表名
2.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
"""
3.concat、concat_ws语法
concat用于分组之前多个字段数据的拼接
concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码
4.exists语法
select * from userinfo where exists (select * from department where id<100)
exists后面的sql语句如果有结果那么执行前面的sql语句
如果没有结果则不执行
视图
SQL语句的执行结果是一张虚拟表 我们可以基于该表做其他操作
如果这张虚拟表需要频繁使用 那么为了方避难可以将虚拟表保存起来 保存起来之后就称为'视图'
create view 视图名 as SQL语句;
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
"""
1.在硬盘中，视图只有表结构文件，没有表数据文件
2.视图通常用于查询 尽量不要修改视图中的数据
"""
视图能少用就少用 会让别人有错觉
Navicat会做出表和视图的区分
触发器
针对表中的增、改、删自动触发的功能(增前、增后、改前、改后、删前、删后)
"""
语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
"""
注意触发器内部的SQL语句需要用到分号 但是分号又是SQL语句默认的结束符
所以为了能够完整的写出触发器的代码 需要临时修改SQL语句默认的结束符
delimiter $$
编写需要用到分号的各种语句
delimiter ;
用完之后改回去！！！！！
# 案例
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('kevin','0755','ls -l /etc',NOW(),'yes'),
('kevin','0755','cat /etc/passwd',NOW(),'no'),
('kevin','0755','useradd xxx',NOW(),'no'),
('kevin','0755','ps aux',NOW(),'yes');
# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;
事务
InnoDB存储引擎中的事务完全符合ACID的特性，ACID是以下四个单词的缩写：
原子性（atomicity）
一致性（consistency）
隔离性（isolation）
持久性（durability）
原子性
原子是自然界非常小的单位，我们可以看成它是不可再分的，同时它也是事务的一个特征，任何一个事务都可以想象成一个原子，表示其不可再分。只有事务中所有的数据库操作都执行成功，才算整个事务成功，事务中任何一个sql语句执行失败，已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态。
注意：如果事务中的操作都是只读的，保持原子性比较简单，一发生错误，要么重试，要么返回错误代码即可，如果当前事务中存在插入或者更新操作，一旦失败，就会引起数据状态的变化，因此要保护系统并发用户访问受影响的部分数据。
一致性
指数据库中数据在事务操作前和操作后都必须满足业务规则约束，也就是A、B账户的总金额在转账前后必须一致，二者的总金额加起来不能多也不能少，如果有不一致，则必须是短暂的，且只有在事务提交前才会出现的。
再举一个例子，在表中有一个字段为姓名，是唯一的约束，即在表中姓名不能重复，如果有一个事务对姓名字段进行了修改，在事务提交后，表中的姓名变得非唯一了，这就破坏了事务一致性的要求，因为事务将数据库从一种状态变成了一种不一致的状态。
隔离性
隔离性还有其他称呼，比如并发控制、可串行化、锁等。通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。在转账的例子中，A向B转账时，C同时向A转账，如果同时进行，则A和B之间的一致行则不能满足，所以，当A和B执行事务的过程中，其他事务是不能访问或修改当前相关的数值。
持久性
一旦事务提交，其所做的修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。
注意：只能从事务本身的角度来保证结果是持久性，当事务提交后，所有的变化都是永久的，即使数据库崩溃需要恢复时，也可以保证恢复后的数据都不会丢失，但是如果不是数据库本身发生了问题，而是一些外部的原因，比如物理因素，自然灾害导致数据库服务器爆炸，那所有的数据可能都会丢失，因此持久性保证系统的高可靠性（High Reliability），而不是高可用性（High Availability）。
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
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元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
# 站在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;
扩展知识点(重要)
MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
事务处理中有几个关键词汇会反复出现
事务(transaction)
回退(rollback)
提交(commit)
保留点(savepoint)
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
创建占位符可以使用savepoint
savepoint sp01;
回退到占位符地址
rollback to sp01;
# 保留点在执行rollback或者commit之后自动释放
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
事务日志可以帮助提高事务的效率
存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘
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
jason01 2 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""
存储过程
类似于python中的自定义函数
delimiter 临时结束符
create procedure 名字(参数,参数)
begin
sql语句;
end 临时结束符
delimiter ;
delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname 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;
"""
触发器与存储过程的不同
相同点：1. 触发器是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段。
不同点：2. 存储器调用时需要调用SQL片段，而触发器不需要调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。
内置函数
"ps:可以通过help 函数名 查看帮助信息!"
# 1.移除指定字符
Trim、LTrim、RTrim
# 2.大小写转换
Lower、Upper
# 3.获取左右起始指定个数字符
Left、Right
# 4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
"""
# 5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
流程控制
# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
索引
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
primary key
unique key
index key
上述的三种键在数据查询的时候使用都可以加快查询的速度
primary key、unique key除了可以加快数据查询还有额外的限制
index key只能加快数据查询 本身没有任何的额外限制
真正理解索引加快数据查询的含义
索引的存在可以加快数据的查询 但是会减慢数据的增删
索引底层原理
树:是一种数据结构 主要用于优化数据查询的操作
二叉树:两个分支
B树(B-树)、B+树、B*树
B树:
除了叶子节点可以有多个分支 其他节点最多只能两个分支
所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
B+树:
只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
B*树
在树节点添加了通往其他节点的通道 减少查询次数
慢查询优化
explain SQL语句 详情可参照下列网址
https://www.cnblogs.com/Dominic-Ji/p/15560680.html
测试索引
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;
# 给id做一个主键
alter table s1 add primary key(id); # 速度很慢
select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason' # 速度仍然很慢
"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快
select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;
alter table s1 drop primary key; # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason'; # 又慢了
create index idx_name on s1(name); # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason' # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性
# 区分度低的字段不能建索引
drop index idx_name on s1;
# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3; # 快了
select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算
drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速
drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度
create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段
drop index idx_id on s1
create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉
联合索引
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3;
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3;
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3;
# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快