MySQL数据库期末复习--这一篇就够了(二)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库期末复习--这一篇就够了

五、存储过程

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;

4dd559fbd1831adadb157775a2954a12_f81ba8e32d9e48e3b99b4fd1aeb96842.png

实例 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 评级;

35ecf8d1a3c800271cabb6769599944f_fdd07a743e074ef58b55413f27eb7cf2.png

六、触发器

触发器用于在 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)

95890552b7d44ba5e5c225dd0a1b3033_ba1d23aa2b84436abec28ecbe19fe98f.png

(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);

2c609c7b5f5a4ebd468ae9a9ef749088_cc92259e43df4ca08a154452d253b393.png

2、查看触发器

show triggers;

b09cdfb76fce15d7753322c7fbe410d9_a16d9b7689e041a78082bdf0ea94ed0c.png

abff8ae30697873dc82dce6f1aa1f7e5_14bec5f1c16f4c42b4de36c260adfb42.png

3、触发器的删除

#删除触发器
drop trigger gzh_trigger2;

七、一些sql语句案例

1、单表操作

(1)新增一条图书信息,'116','《Java》','吴某凡','机械工业','2022-11-22','大碗宽面'

insert into gzh_books values
('116','《Java》','吴某凡','机械工业','2022-11-22','大碗宽面')

33c4354cfea2f6f1aa32d041936285e4_a52541d885074451b23191bab4e1dc67.png

(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

dd778613bf47b79a6c682913a69bd92d_fecb6dc0ac3d48a8b0a4f20fdbb58564.png

(7) 查询管理员表中的姓名和电话

select gzh_lname as 姓名,gzh_lphone as 电话  from gzh_librarian


590fcdfd188999bb4184dca1adb740b9_23206bc3909548819c3e560bd2bbb259.png

(8)查询读者表中不同的专业

select gzh_speciality as 专业类别 from gzh_readers GROUP BY gzh_speciality

46b02483b72f32ee1c53efbf80129bc1_bfdf6528c3984debbbed7315f6436152.png

(9)查询所有图书的作者,并按作者统计不同作者下的书本数

select gzh_author as 作者,count(*) as 书本数 from gzh_books  group by gzh_author

699848e550f566ade3873c1018dd02be_ef8cfca61da14ec7a8a641d1024bb238.png

(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);


e8fd0f7bf29b2e6dfa3edd8fd316f9c0_0509999164b445c491bf05cf465c4a95.png

(11)查询所有1990-07-11之后的并且书名中包含“人”字的所有图书

select * from gzh_books  where gzh_time>= '1990-07-11' and  gzh_bname like'%人%'

709ada619c20e283b99426335628bf17_52650c68241c4233aa18866b6f98f035.png

(12)查询累计借书次数为3、6、9的所有用户

select * from gzh_readers where gzh_rnum in (3,6,9)

adfbbfb4f37b974a512588ae40007ec0_3797e1b4b75b4dc7b22e971d86dc8439.png

(13)查询所有借阅情况并按照借书日期倒序排列

select * from gzh_borrow order by gzh_jtime desc

bd0ac48ee64c56fd8b2d1b0d6ad9de30_81f229cda03e4bd091883be53739ef29.png

(14)查询包含“人”字的所有图书并按出版日期升序排列

select * from gzh_books  where  gzh_bname like'%人%' ORDER BY gzh_time

4705ea867b569bfa61b14639fe55a037_64e6d01a3e684f1a8286c51ab2e3b172.png




相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7月前
|
存储 数据库 C语言
期末速成数据库极简版【分支循环&函数】(4)
期末速成数据库极简版【分支循环&函数】(4)
70 1
|
7月前
|
数据库
期末速成数据库极简版【查询】(2)
期末速成数据库极简版【查询】(2)
63 1
|
7月前
|
存储 Linux 数据库
期末速成数据库极简版【存储过程】(5)
期末速成数据库极简版【存储过程】(5)
38 0
|
7月前
|
Linux 数据库
期末速成数据库极简版【创建】(1)
期末速成数据库极简版【创建】(1)
64 0
|
7月前
|
数据库
期末速成数据库极简版【查询】(3)
期末速成数据库极简版【查询】(3)
102 0
|
存储 安全 数据库
数据库期末复习---简答题整理
数据库期末复习---简答题整理
103 0
|
SQL 存储 安全
五小时三万字之一篇博文带你学会SQL【期末数据库稳了】
本文重点知识大多在代码的注释里,大家一定要仔细看哦!建议你们跟着我敲哦!祝你们收获满满!
220 0
五小时三万字之一篇博文带你学会SQL【期末数据库稳了】
|
数据库
阿里云数据库2019年期末成绩单,请查收!
2020年 我们的故事继续 将为客户提供更好的云数据库服务体验
1662 0
阿里云数据库2019年期末成绩单,请查收!
|
19天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1