五、存储过程
1、存储过程
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。简单的说就是专门干一件事的一段sql语句。
2、存储过程特点
（1）执行效率非常快！存储过程是在数据库的服务器端执行。
（2）移植性很差！不同的数据库的存储过程是不能移植的。
3、创建存储过程
begin后面可以跟多条语句，增删改查语句都可以，每句一定要以分号结尾。in表示参数输入，out表示参数输出。
#根据读者号查询该读者的所有信息 delimiter $$ CREATE PROCEDURE lib_findById(in rid int) begin SELECT *from gzh_readers where gzh_rno=rid; END $$ delimiter;
4、调用存储过程
call lib_findById(10);
5、删除存储过程
drop PROCEDURE lib_findById;
6、参考案例
实例1：存储过程实现输入数字，输出一个对应的星期数
delimiter $$ create procedure week_testIf(in num int,out str varchar(20)) begin if num=1 then set str='星期一'; elseif num=2 then set str='星期二'; elseif num=3 then set str='星期三'; elseif num=4 then set str='星期四'; elseif num=5 then set str='星期五'; elseif num=6 then set str='星期六'; elseif num=7 then set str='星期天'; else set str='输入错误'; end if; end $$ delimiter; -- 执行存储过程 call week_testIf(7,@str); select @str;
实例 2：学校图书馆出台了一项借阅评级政策，想通过借阅量评借阅之星，评级如下：
#借阅3本以下，评为普通借阅者
#借阅3-5本，评为优质借阅者
#借阅5-9本，评为优秀借阅者
#借阅9本以上，评为借阅之星
要求使用存储过程，当输入姓名时，输出评级，比如：云凌可是借阅之星，郭山彤是普通借阅者
注：mysql的参数赋值语句必须是只能够选出一行（包括存储过程），这样的语句必须要加上limit 1才可以，否则报Result consisted of more than one row错误。
delimiter $$ CREATE PROCEDURE lib_bestReaders(in rname varchar(20),out str varchar(20)) begin declare num int; select gzh_rnum into num from gzh_readers where gzh_rname=rname ; if num>9 then set str='借阅之星'; elseif num>=5 and num<=9 then set str='优秀借阅者'; elseif num>=3 and num<5 then set str='优质借阅者'; else set str='普通借阅者'; end if; end $$ delimiter; call lib_bestReaders('云凌可',@str); select @str as 评级; ------------------------------------ call lib_bestReaders('郭山彤',@str); select @str as 评级; ----------------------------------------- call lib_bestReaders('蒋进如',@str); select @str as 评级;
六、触发器
触发器用于在 MySQL 执行插入、更新或删除语句时，自动触发执行其他SQL代码。触发器可以在执行语句前或执行后触发其他 SQL 代码运行。触发器可以读取触发语句改变了哪些数据，但是没有返回值。因此可以使用触发器加强业务逻辑的约束而不需要在应用程序写对应的代码。
1、触发器的创建
（1）简单触发器
创建一个简单的触发器gzh_trigger，当执行插入语句之后激活触发器，如果超过某个自己设定的阙值，触发器被触发并给出相应的提示信息
delimiter $$ CREATE TRIGGER gzh_trigger #触发器的名称 AFTER INSERT #当执行插入语句之后激活触发器 ON gzh_readers #作用于那个表 FOR EACH ROW #触发器作用在每条记录上 #以上都是固定的写法，可以直接套 # 触发器需要执行的操作 if NEW.gzh_rnum>50 then SIGNAL SQLSTATE '45000' #错误状态信息 set message_text="你输入的数字过大，检查后输入"; end if $$ delimiter; insert into gzh_readers values (29,'刘小燕','女','42123456789','计算机应用','超时还书',105)
（2）复杂触发器
创建一个复杂的触发器gzh_trigger2并且单独创建一个表来保存触发信息
create table readers_log( operation VARCHAR(50) not null, operation_time TIMESTAMP not null ); delimiter $$ CREATE TRIGGER gzh_trigger2 #触发器的名称 AFTER INSERT #当执行插入语句之后激活触发器 ON gzh_readers #作用于那个表 FOR EACH ROW #触发器作用在每条记录上 # 触发器需要执行的操作 BEGIN set @r_name = new.gzh_rname; #当插入一条读者记录就向日志表插入一条记录此次操作 INSERT INTO readers_log(operation,operation_time) VALUES (concat("被操作人:",@r_name),NOW()); END $$ delimiter; insert into gzh_readers values (23,'刘小燕','女','42123456789','计算机应用','超时还书',7);
2、查看触发器
show triggers;
3、触发器的删除
#删除触发器 drop trigger gzh_trigger2;
七、一些sql语句案例
1、单表操作
（1）新增一条图书信息，'116','《Java》','吴某凡','机械工业','2022-11-22','大碗宽面'
insert into gzh_books values ('116','《Java》','吴某凡','机械工业','2022-11-22','大碗宽面')
（2）将读者表中名字有“谢”的读者的累积借书增加1次
update gzh_readers set gzh_rnum=gzh_rnum+1 where gzh_rname like '%谢%'
修改之前：
修改之后：
（3）删除书号为115和116的图书
delete from gzh_books where gzh_bno in('115','116')
删除之前：
删除之后：
4）删除最早出版的三本图书信息
#先查出最早出版的三本图书的信息，然后删除 DELETE FROM gzh_books WHERE gzh_time =( SELECT gzh_time FROM gzh_books ORDER BY gzh_time ASC LIMIT 0,3 )
先查出最早出版的三本图书的信息：
然后再进行删除：
（5）修改《我不是潘金莲》的作者为武松、出版社为机械工业
update gzh_books set gzh_author='武松',gzh_press='机械工业' where gzh_bname='《我不是潘金莲》'
修改之前：
修改之后：
（6）查询读者表中的所有数据
select * from gzh_readers
（7） 查询管理员表中的姓名和电话
select gzh_lname as 姓名,gzh_lphone as 电话 from gzh_librarian
（8）查询读者表中不同的专业
select gzh_speciality as 专业类别 from gzh_readers GROUP BY gzh_speciality
（9）查询所有图书的作者，并按作者统计不同作者下的书本数
select gzh_author as 作者,count(*) as 书本数 from gzh_books group by gzh_author
（10）使用子查询，查询累计借书次数在某个范围之间的所有读者的姓名，例如借书次数3-9次的所有读者的姓名
SELECT gzh_rname AS 读者的姓名,gzh_rnum AS 借书次数 FROM gzh_readers WHERE gzh_rnum in( SELECT gzh_rnum AS 借书次数 FROM gzh_readers WHERE gzh_rnum BETWEEN 3 AND 9);
（11）查询所有1990-07-11之后的并且书名中包含“人”字的所有图书
select * from gzh_books where gzh_time>= '1990-07-11' and gzh_bname like'%人%'
（12）查询累计借书次数为3、6、9的所有用户
select * from gzh_readers where gzh_rnum in (3,6,9)
（13）查询所有借阅情况并按照借书日期倒序排列
select * from gzh_borrow order by gzh_jtime desc
（14）查询包含“人”字的所有图书并按出版日期升序排列
select * from gzh_books where gzh_bname like'%人%' ORDER BY gzh_time
