Part 1 —— 视图、触发器trigger、存储过程、函数
今日内容概要
1、'视图(了解)'
2、'触发器trigger'
3、事务
4、'存储过程procedure'
5、函数
6、流程控制
7、函数
8、流程控制
9、索引与慢查询优化
10、索引分类
1.自行百度搜索并提炼"数据库设计三大范式"
分别是哪三大以及各自有何特征
2.整理今日内容及博客
3.自行复习python基础
储备:
1、delimiter
delimiter $$ # 临时修改语句结束符为 : $$
delimiter ; # 修改语句结束符为: ;
2、mysql变量
定义:
set [变量名]=[变量值];
查看变量值:
select @[变量名];
案例:
set res=10; #创建一个res变量,变量值为10。
select @res; #查看res的变量值,以表格的形式输出
一、视图view(了解)
视图是一个'虚拟表'(非真实存在),其本质是"根据SQL语句获取动态的数据集,并为其命名",用户使用时只需使用"名称"即可获取结果集,可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂的sql了,直接去视图中查找即可,但视图有明显地效率问题,并且视图是存放在数据库中的,如果我们程序中使用的sql过分依赖数据库中的视图,即强耦合,那就意味着扩展sql极为不便,因此并不推荐使用
将SQL语句的查询结果当做虚拟表实体化保存起来,以后可以反复使用
#1. 使用视图以后就无需每次都重写子查询的sql,但是这么效率并不高,还不如我们写子查询的效率高
#2. 而且有一个致命的问题:视图是存放到数据库里的,如果我们程序中的sql过分依赖于数据库中存放的视图,那么意味着,一旦sql需要修改且涉及到视图的部分,则必须去数据库中进行修改,而通常在公司中数据库有专门的DBA负责,你要想完成修改,必须付出大量的沟通成本DBA可能才会帮你完成修改,极其地不方便
# 视图使用频率不高,了解即可
1、创建
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
2、删除
drop view teacher2course;
二、触发器trigger(类似于后台进程)
1、介绍
触发器:满足特点条件之后自动执行"增、删、改"操作时"前后"的行为,注意:没有查询
在MySQL只有三种情况下可以触发
1.针对表的增
增前 增后
2.针对表的改
改前 改后
3.针对表的删
删前 删后
2、语法格式和使用方法
1、'完整语法结构'
delimiter $$
create trigger [触发器名字] [before/after] [insert/update/delete] on 表名 for each row
begin
sql语句; # NEW,OLD关键字填写在sql内
end $$
delimiter ;
2、'查看触发器'
show triggers;
3、'删除触发器'
drop trigger tri_after_insert_cmd;
3、mysql触发器内( NEW与OLD )解析
mysql触发器中, NEW关键字,和 MS SQL Server 中的 INSERTED 和 DELETED 类似,MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据。
具体地:
在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;
使用方法: NEW.columnName (columnName 为相应数据表某一列名)
另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用。
4、注意点
-----------------------------------"注意点"---------------------------------------
1、"触发器的命名规范"
tri_after_insert_t1
# 或者
tri_before_delete_t1
2、# 在MySQL中NEW特指数据对象可以通过点的方式获取字段对应的数据
如果有一个表为:
id name pwd hobby
1 jason 123 read
mysql>NEW.name
3、# 临时修改SQL语句的结束符,仅在当前窗口有效
"""
需要注意: 在书写sql代码的时候结束符是 ; 而整个触发器的结束也需要分号 ;
这就会出现语法冲突 需要我们临时修改结束符号
"""
5、触发器案例
# 案例:创建一个记录错误日志的表
----------------------------------'创建cmd表和errlog表'---------------------------------
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插入数据,触发器起作用'-------------------------------
#往表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; # 只向cmd表插入数据,但是触发器起作用, errlog表顺利记录2条success=no
----------------------------------'其他操作'---------------------------------
# 查看触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;
三、存储过程 procedure(mysql自定义函数) (开发不用写,DBA写)
1、介绍
# 类似于python中的自定义函数
# 操作和定义函数,调用函数一样
# 所以必须知道如何定义一个全局变量,函数的参数规范
2、语法格式和使用方法
定义:
1、'无参存储过程'
|delimiter $$
|create procedure p1()
|begin
| select * from cmd;
|end $$
|delimiter ;
2、'有参存储过程'
|delimiter $$
|"定义函数名,以及定义形参"
|create procedure p2( # p2是函数名
| [in/out/inout] [形参名1] [数据类型],
| [in/out/inout] [形参名1] [数据类型],
|)
|begin
| 'sql语句'; # 就相当于函数体代码
|end $$
|delimiter ;
'对于存储过程,可以接收参数,其参数有三类':
in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值
存储过程方法
1、调用
call [存储过程名] (参数);
2、查看某个具体存储过程
show create procedure [存储过程名];
3、查看所有的存储过程
show procedure status;
4、删除某个存储过程
drop procedure [存储过程名];
3、案例
--------------------------------------"基础表"--------------------------------------
mysql> select * from userinfo;
+----+----------+------+--------+-----------+
| id | password | name | money | food_menu |
+----+----------+------+--------+-----------+
| 1 | 111 | aaa | 10000 | |
| 2 | 222 | bbb | 10000 | |
| 3 | 333 | ccc | 10000 | |
| 4 | 123 | yly | 999999 | |
| 5 | 321 | zpx | 999999 | |
| 7 | 123 | ddd | 10000 | |
| 8 | 111 | nnn | 0 | |
+------+----------+----+--------+-----------+
7 rows in set (0.00 sec)
---------------------------------------"案例"-------------------------------------
# 1.定义无参存储过程
delimiter $$
create procedure p1()
begin
select * from cmd;
end $$
delimiter ;
# 调用
call p1()
# 2.定义有参存储过程
# mysql客户端
delimiter $$
"定义函数名,以及定义形参"
create procedure p2( # p2是函数名
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int, # n为形参,必须用
out res int # out表示这个参数可以被返回出去
# 形参还有一个inout表示即可以传入也可以被返回出去
)
begin
'sql语句 = 函数体代码'
select * from user where id > m and id < n;
set res=0; # 用来标志存储过程是否执行(也就是看是否调用函数,能否将res=10改为res=0)
end $$
delimiter ;
"调用函数"
set res=10; # 用来标志存储过程是否执行
select @res; #查看现在的变量res为10
call p2(2,5,@res); #将一个变量res传入函数
select @res; # 此时res变为了0
# python代码操作存储过程
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())
"""
查看存储过程具体信息
show create procedure [存储过程名];
查看所有存储过程
show procedure status;
调用存储过程
call [存储过程名]( 里面为参数 )
删除存储过程
drop procedure [存储过程名];
"""
拓展 —— 三种开发过程
第一种
"""
应用程序:只需要开发应用程序的逻辑 (开发人员)
mysql:编写好存储过程,以供应用程序调用 (DBA人员)
优点:开发效率,执行效率都高
"""
第二种
"""
应用程序:除了开发应用程序的逻辑,还需要编写原生sql (开发人员)
(无DBA人员)
优点:比方式1,拓展性高(非技术性的)
缺点:
1、开发效率,执行效率都不如方式1
2、编写原生sql太过于复杂,而且需要考虑到sql语句的优化问题
"""
第三种
"""
应用程序:开发应用程序的逻辑,不需要编写原生sql,是基于别人写好的框架来处理数据:ORM (django)
优点:
不用再编写纯生sql,这意味着开发效率比方式2高,同事兼容方式2扩展性高的好处
缺点:
执行效率连方式2都比不过
"""
四、函数(mysql内置方法)
# 相当于python中内置函数
# mysql有很多很多函数,这里只介绍5种,之前也讲过select last_insert_id();
1、Trim、LTrim、RTrim
2、lower、upper
3、Left、Right
4、Soundex
5、日期
"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
案例:
SELECT UPPER('Hej');
-> 'HEJ'
SELECT LOWER('QUADRATICALLY');
-> 'quadratically'
3、获取左右起始指定个数字符
Left、Right
案例:
SELECT LEFT('foobarbar', 5);
-> 'fooba'
SELECT RIGHT('foobarbar', 4);
-> 'rbar'
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计算两个日期差值
...