09、视图、触发器、存储过程、函数、流程控制
一、视图（了解）
将SQL语句的查询结果保存下来，形成的表就叫视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用
# 创建视图
语法：create view 视图名称 as sql语句
use db04_1
案例：create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
# 使用视图
语法：selelct 字段名 from 视图名称
案例：select * from teacher2course
# 修改视图
语法：alter view 视图名称 as sql语句
案例：alter view teacher2course as select * from course where cid>3
# 删除视图
语法：drop view 视图名称
案例：drop view teacher2course
修改视图后，原表的记录也会跟着修改，因此我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的
二、触发器trigger
对表进行增、删、改操作前后可以自动触发的功能
触发器:满足特点条件之后自动执行
在MySQL只有三种情况下可以触发
1.针对表的增
增前 增后
2.针对表的改
改前 改后
3.针对表的删
删前 删后
1、语法
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
2、准备表
USE db06
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
);
3、触发器的创建与触发
#创建触发器
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');
#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd | err_time |
+----+-----------------+---------------------+
| 1 | cat /etc/passwd | 2017-09-14 22:18:48 |
| 2 | useradd xxx | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)
# 查看触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;
# 触发器名字在命名的时候推荐使用下列的方式
tri_after_insert_t1、tri_before_delete_t1
# 如何临时修改SQL语句的结束符
delimiter //
# 在MySQL中NEW特指数据对象可以通过点的方式获取字段对应的数据
id name pwd hobby
1 jason 123 read
NEW.name >>> jason
三、事务
# 四大特性(ACID)
A:原子性(atomicity /ˌætəˈmɪsəti/)
每个事务都是不可分割的最小单位(同一个事务内的多个操作要么同时成功要么同时失败)
C:一致性(consistency /kənˈsɪstənsi/)
事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
I:隔离性(isolation /ˌaɪsəˈleɪʃn/)
事务与事务之间彼此不干扰
D:持久性(durability /ˌdʊrəˈbɪləti/)
一个事务一旦提交，它对数据库中数据的改变就应该是永久性的
# 如何开启事务
start transaction;
# 如何回滚
rollback;
# 如何确认
commit;
#实例展示
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance) values
('jason',1000),
('tony',1000),
('oscar',1000);
# 修改数据之前先开启事操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; # 买家支付100元
update user set balance=1010 where name='tony'; # 中介拿走10元
update user set balance=1090 where name='oscar'; # 卖家拿走90元
select * from user;
+----+-------+---------+
| id | name | balance |
+----+-------+---------+
| 1 | jason | 900 |
| 2 | tony | 1010 |
| 3 | oscar | 1090 |
+----+-------+---------+
# 如何回滚
rollback;
select * from user;
+----+-------+---------+
| id | name | balance |
+----+-------+---------+
| 1 | jason | 1000 |
| 2 | tony | 1000 |
| 3 | oscar | 1000 |
+----+-------+---------+
# 如何确认
commit;
rollback;
select * from user;
+----+-------+---------+
| id | name | balance |
+----+-------+---------+
| 1 | jason | 900 |
| 2 | tony | 1010 |
| 3 | oscar | 1090 |
+----+-------+---------+
四、存储过程(了解)
1、介绍
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql # 类似于python中的自定义函数 #优点 1. 用于替代程序写的SQL语句，实现程序与sql解耦 2. 基于网络传输，传别名的数据量小，而直接传sql数据量大 # 缺点 1. 程序员扩展功能不方便
2、无参存储过程
delimiter //
create procedure p1()
begin
select * from user;
end //
delimiter ;
#在mysql中调用
call p1()
#在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())
3、有参存储过程
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 ;
#在mysql中调用
set @res=0; #0代表假（执行失败），1代表真（执行成功）
call p2(1,3,@res);
select @res;
#在python中基于pymysql调用
cursor.callproc('p3',(3,0)) #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall())
4、代码操作存储过程
# 代码操作存储过程
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())
五、函数
1、介绍
# 相当于python中内置函数
"ps:可以通过help 函数名 查看帮助信息!"
# 1.移除指定字符
Trim、LTrim、RTrim
select trim(' bar ')
'bar'
select trim(leading 'x' from 'xxxbarxxx')
'barxxx'
select trim(both 'x' from 'xxxbarxxx')
'bar'
select trim(trailing 'xyz' from 'barxxyz')
'barx'
# 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形式'''
2、案例
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计算两个日期差值
...
3、删除函数
drop function func_name;
六、流程控制
1、条件语句
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 ;
2、循环语句
# 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 ;
# repeat循环
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN
DECLARE i INT ;
SET i = 0 ;
repeat
select i;
set i = i + 1;
until i >= 5
end repeat;
END //
delimiter ;
#loop
BEGIN
declare i int default 0;
loop_label: loop
set i=i+1;
if i<8 then
iterate loop_label;
end if;
if i>=10 then
leave loop_label;
end if;
select i;
end loop loop_label;
END
七、索引与慢查询优化
1、介绍
索引:简单的理解为可以帮助你加快数据查询速度的工具
也可以把索引比喻成书的目录
算法:解决事物的办法
入门级算法:二分法
二分法前提:数据集必须有序
数据结构
二叉树(只能分两个叉)
b树 b+树(叶子节点添加了指针) b*树(枝节点也添加了指针)
# 添加指针是为了加快范围查询的速度
将某个字段添加成索引就相当于依据该字段建立了一颗b+树从而加快查询速度
如果某个字段没有添加索引 那么依据该字段查询数据会非常的慢(一行行查找)
2、创建索引
# 1、为user表的id字段创建索引，会以每条记录的id字段值为基础生成索引结构 create index 索引名 on user(id); # 使用索引 select * from user where id = xxx; # 2、为user表的name字段创建索引，会以每条记录的name字段值为基础生成索引结构 create index 索引名 on user(name); # 使用索引 select * from user where name = xxx;
3、索引的分类
1.primary key 主键索引除了有加速查询的效果之外 还具有一定的约束条件 2.unique key 唯一键索引 除了有加速查询的效果之外 还具有一定的约束条件 3.index key 普通索引 只有加速查询的效果 没有额外约束 4.foreign key # 注意外键不是索引 它仅仅是用来创建表与表之间关系的
4、如何操作索引
# 准备
use db05
create table t1(
id int,
name varchar(32),
pwd int
);
insert into t1 values
(1,'momo',123),
(2,'lily',456),
(3,'tony',789),
(4,'jason',666);
创建唯一索引需要提前排查是否有重复数据
select count(字段) from t1;
select count(distinct(字段)) from t1;
查看当前表内部索引值
show index from t1;
主键索引
alter table t1 add primary key pri_id(id);
唯一索引
alter table t1 add unique key uni_age(age)
普通索引
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...)
5、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.单独引用联合索引里非第一位置的索引列
"""
索引的创建会加快数据的查询速度 但是一定程度会拖慢数据的插入和删除速度
"""
6、隔离级别
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改 InnoDB支持所有隔离级别 set transaction isolation level 级别 1.read uncommitted(未提交读) 事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读" 2.read committed(提交读) 大多数数据库系统默认的隔离级别 一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读" 3.repeatable read(可重复读) # MySQL默认隔离级别 能够解决"脏读"问题，但是无法解决"幻读" 所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题 4.serializable(可串行读) 强制事务串行执行，很少使用该级别 https://www.cnblogs.com/Dominic-Ji/p/15560680.html
作业
1.自行百度搜索并提炼"数据库设计三大范式",分别是哪三大范式以及各自有何特征
"""
范式：关系型数据库中规范称为范式
第一范式(1NF)：
1、数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
2、两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
第二范式（2NF）：
满足1NF后要求表中的所有列，每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
第三范式（3NF）：
满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。
注意事项：
1.第二范式与第三范式的本质区别：在于有没有分出两张表。
第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，第三范式是要求已经分好了多张表的话，一张表中只能有另一张标的ID，而不能有其他任何信息，（其他任何信息，一律用主键在另一张表中查询）。
2.必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。
三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。
"""
2.整理今日内容及博客
3.自行复习python基础
