1-7其他(视图,触发器,事务,存储过程,函数,流程控制,索引,数据库设计三大范式)
目录
- 视图view
- 触发器trigger
- 案例
- 事务
- 隔离级别
- 存储过程
- 函数
- 流程控制
- 索引
- 索引与慢查询优化
- 索引的分类
- 如何操作索引
- explain句式
- 数据库设计三大范式
- 第一范式
- 第二范式
- 第三范式
视图view
将SQL语句的查询结果当做虚拟表实体化保存起来，以后可以反复使用
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
drop view teacher2course;
# 视图使用频率不高
触发器trigger
触发器:满足特点条件之后自动执行
在MySQL只有三种情况下可以触发
1.针对表的增
增前 增后
2.针对表的改
改前 改后
3.针对表的删
删前 删后
临时修改SQL语句的结束符 delimiter $$
# 在MySQL中NEW特指数据对象可以通过点的方式获取字段对应的数据
id name pwd hobby
1 tony 123 read
NEW.name >>> tony
# 语法结构
create trigger 触发器的名字
before/after #(之前/之后)
insert/update/delete #(增改查)
on 表名 for each row
begin
sql语句
end
##############################################
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
# 触发器名字在命名的时候推荐使用下列的方式
tri_after_insert_t1、tri_before_delete_t1
案例
###############建表###############
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
('tony','0755','ls -l /etc',NOW(),'yes'),
('tony','0755','cat /etc/passwd',NOW(),'no'),
('tony','0755','useradd xxx',NOW(),'no'),
('tony','0755','ps aux',NOW(),'yes');
#######################################
# 查询errlog表记录
select * from errlog;
# 查看触发器
show triggers \G; #\G:将查询到的横向表格纵向输出，方便阅读
# 删除触发器
drop trigger tri_after_insert_cmd;
事务
# 四大特性(ACID)
A:原子性 每个事务都是不可分割的最小单位(同一个事务内的多个操作要么同时成功要么同时失败)
C:一致性 事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
I:隔离性 事务与事务之间彼此不干扰
D:持久性 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的
# 如何开启事务
start transaction;
# 如何回滚
rollback;
# 如何确认
commit;
隔离级别
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
存储过程
# 类似于python中的自定义函数
# 1.无参存储过程
delimiter $$
create procedure p1()
begin
select * from user;
end $$
delimiter ;
# 调用
call p1()
# 2.有参存储过程
delimiter $$
create procedure p2(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select * from user where id > m and id < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 代码操作存储过程
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='123',
db='db6',
charset='utf8',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p2',(1,3,10))
# @_p1_0=1,@_p1_1=3,@_p1_2=10;
print(cursor.fetchall())
函数
# 相当于python中内置函数
"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判断
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
# while循环
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
索引
索引与慢查询优化
索引:简单的理解为可以帮助你加快数据查询速度的工具
也可以把索引比喻成书的目录
算法:解决事物的办法
入门级算法:二分法
二分法前提:数据集必须有序
数据结构
二叉树(只能分两个叉)
b树 b+树(叶子节点添加了指针) b*树(枝节点也添加了指针)
# 添加指针是为了加快范围查询的速度
将某个字段添加成索引就相当于依据该字段建立了一颗b+树从而加快查询速度
如果某个字段没有添加索引 那么依据该字段查询数据会非常的慢(一行行查找)
索引的分类
1.primary key
主键索引除了有加速查询的效果之外 还具有一定的约束条件
2.unique key
唯一键索引 除了有加速查询的效果之外 还具有一定的约束条件
3.index key
普通索引 只有加速查询的效果 没有额外约束
4.foreign key
# 注意外键不是索引 它仅仅是用来创建表与表之间关系的
如何操作索引
查看当前表内部索引值
show index from t1;
主键索引
alter table t1 add primary key pri_id(id);
唯一索引
alter table t1 add unique key uni_age(age)
创建唯一索引需要提前排查是否有重复数据
select count(字段) from t1
select count(distinct(字段)) from t1
普通索引
alter table t1 add index idx_name(name)
前缀索引(属于普通索引)
避免对大列建索引,如果有就使用前缀索引
eg:博客内容 百度搜索内容等
alter table t1 add index idx_name(name(4))
联合索引(属于普通索引)
相亲平台 搜索心仪对象的时候
# 最左匹配原则
gender money height beautiful
alter table t1 add index idx_all(name,age,sex)
删除索引
alter table t1 drop index 索引名(idx_name、idx_all...)
explain句式
全表扫描
不走索引 一行行查找数据 效率极低 生产环境下尽量不要书写类似SQL
索引扫描
走索引 加快数据查询 建议书写该类型SQL
explain就是帮助我们查看SQL语句属于那种扫描
# 常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null
从上到下，性能从最差到最好，我们认为至少要达到range级别
# 不走索引情况(起码记忆四条及以上)
1.没有查询条件，或者查询条件没有建立索引
2.查询结果集是原表中的大部分数据(25％以上)
3.索引本身失效，统计数据不真实
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
5.隐式转换导致索引失效
eg:字段是字符类型 查询使用整型
6.<> ，not in 不走索引
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit、or或in尽量改成union
7．like "%_" 百分号在最前面不走
8.单独引用联合索引里非第一位置的索引列
"""
索引的创建会加快数据的查询速度 但是一定程度会拖慢数据的插入和删除速度
"""
数据库设计三大范式
第一范式
最基本的范式
数据库表每一列都是不可分割的基本数据项，同一列中不能有多个值
简单说就是要确保每列保持原子性
第二范式
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键和某一部分相关（主要针对联合主键而言）
即在一个数据库表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
第三范式
确保数据表中的每一列数据都和主键直接相关，而不能间接相关
属性不依赖于其他非主属性