SQL存储过程实例详解

article/2025/9/29 19:07:37

SQL存储过程实例详解

本文用3个题目，从建立数据库到创建存储过程，详细讲解数据库的功能。

题目1

学校图书馆借书信息管理系统建立三个表：
学生信息表：student

字段名称

数据类型

说明

stuID

char(10)

学生编号，主键

stuName

Varchar(10)

学生名称

major

Varchar(50)

专业

        图书表：book

字段名称

数据类型

说明

stuID

char(10)

学生编号，主键

stuName

Varchar(10)

学生名称

major

Varchar(50)

专业

        借书信息表：borrow

字段名称

数据类型

说明

borrowID

char(10)

借书编号，主键

stuID

char(10)

学生编号，外键

BID

char(10)

图书编号，外键

T_time

datetime

借书日期

B_time

datetime

还书日期

        请编写SQL语句完成以下的功能：
1) 查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示：

2) 查询所有借过图书的学生编号、学生名称、专业；参考查询结果如下图所示：

3) 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；参考查询结果如下图所示：

4) 查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所示：

附加：建表语句： 
USE master
GO
/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/
--检验数据库是否存在，如果为真，删除此数据库--
IF exists(SELECT * FROM sysdatabases WHERE name='BOOK')DROP DATABASE BOOK
GO
CREATE DATABASE BOOK
GO--建数据表--
USE BOOK
GO
CREATE TABLE student  --学生信息表
(stuID CHAR(10) primary key,  --学生编号stuName  CHAR(10) NOT NULL ,     --学生名称major  CHAR(50) NOT NULL    --专业
)
GO
CREATE TABLE book  --图书表
(BID  CHAR(10) primary key,    --图书编号title  CHAR(50) NOT NULL,  --书名author  CHAR(20) NOT NULL,  --作者
)
GO
CREATE TABLE borrow  --借书表
(borrowID  CHAR(10) primary key,    --借书编号
stuID CHAR(10) foreign key(stuID) references student(stuID), --学生编号
BID  CHAR(10) foreign key(BID) references book(BID),--图书编号T_time  datetime NOT NULL,   --借出日期B_time  datetime    --归还日期
)
GO--学生信息表中插入数据--
INSERT INTO student(stuID,stuName,major)VALUES('1001','林林','计算机')
INSERT INTO student(stuID,stuName,major)VALUES('1002','白杨','计算机')
INSERT INTO student(stuID,stuName,major)VALUES('1003','虎子','英语')
INSERT INTO student(stuID,stuName,major)VALUES('1004','北漂的雪','工商管理')
INSERT INTO student(stuID,stuName,major)VALUES('1005','五月','数学')
--图书信息表中插入数据--
INSERT INTO book(BID,title,author)VALUES('B001','人生若只如初见','安意如')
INSERT INTO book(BID,title,author)VALUES('B002','入学那天遇见你','晴空')
INSERT INTO book(BID,title,author)VALUES('B003','感谢折磨你的人','如娜')
INSERT INTO book(BID,title,author)VALUES('B004','我不是教你诈','刘庸')
INSERT INTO book(BID,title,author)VALUES('B005','英语四级','白雪')
--借书信息表中插入数据--
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T001','1001','B001','2007-12-26',null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T002','1004','B003','2008-1-5',null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T003','1005','B001','2007-10-8','2007-12-25')
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T004','1005','B002','2007-12-16','2008-1-7')
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T005','1002','B004','2007-12-22',null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T006','1005','B005','2008-1-6',null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T007','1002','B001','2007-9-11',null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T008','1005','B004','2007-12-10',null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T009','1004','B005','2007-10-16','2007-12-18')
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T010','1002','B002','2007-9-15','2008-1-5')
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T011','1004','B003','2007-12-28',null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T012','1002','B003','2007-12-30',null)
        标准答案：
-- 1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期— 
select 学生编号=stuID,学生名称=(select stuName from student where stuID=borrow.stuID),图书编号=BID,图书名称=(select title from book where BID=borrow.BID),借出日期=T_time from borrow where stuID in (select stuID from student where major='计算机') and T_time>'2007-12-15' and T_time<'2008-1-8'
-- 2)查询所有借过图书的学生编号、学生名称、专业-- 
select 学生编号=stuID,学生名称=stuName,专业=major from student where stuID in (select stuID from borrow)
-- 3)查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期-- 
select 学生名称=(select stuName from student where stuID=borrow.stuID),图书名称=(select title from book where BID=borrow.BID),借出日期=T_time,归还日期=B_time from borrow where BID in (select BID from book where author='安意如')
-- 4)查询目前借书但未归还图书的学生名称及未还图书数量-- 
select 学生名称=(select stuName from student where stuID=borrow.stuID),借书数量=count(*) from borrow where B_time is null group by stuID

题目2

程序员工资表：ProWage

字段名称

数据类型

说明

ID

int

自动编号，主键

PName

Char(10)

程序员姓名

Wage

int

工资

        创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止，存储过程执行完后，最终加了多少钱？
例如：如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100元，直到有一半以上的人工资大于2000元，调用存储过程后的结果如图：

请编写T-SQL来实现如下功能：
1) 创建存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000元之上，如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200，3000，3500，4000，5000或6000元。
2) 创建存储过程，查询程序员平均工资在4500元，如果不到则每个程序员每次加200元，至到所有程序员平均工资达到4500元。
建表语句： 
USE master
GO
/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/
--检验数据库是否存在，如果为真，删除此数据库--
IF exists(SELECT * FROM sysdatabases WHERE name='Wage')DROP DATABASE Wage
GO
CREATE DATABASE Wage
GO--建数据表--
USE Wage
GO
CREATE TABLE ProWage  --程序员工资表
(ID int identity(1,1) primary key,  --工资编号PName  CHAR(10) NOT NULL ,     --程序员姓名Wage  int NOT NULL    --工资
)
GO
--插入数据--
INSERT INTO ProWage(PName,Wage)VALUES('青鸟',1900)
INSERT INTO ProWage(PName,Wage)VALUES('张三',1200)
INSERT INTO ProWage(PName,Wage)VALUES('李四',1800)
INSERT INTO ProWage(PName,Wage)VALUES('二月',3500)
INSERT INTO ProWage(PName,Wage)VALUES('蓝天',2780)
        标准答案： 
--1、创建存储过程--
if exists (select * from sysobjects where name='Sum_wage')
drop procedure Sum_wage
GO
create procedure Sum_wage 
@PWage int,
@AWage int,
@total int
as 
while (1=1)
begin
if (select count(*) from ProWage)>2*(select count(*) from ProWage where Wage>=@PWage)
update ProWage set @total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print'一共加薪：'+convert(varchar,@total)+'元'
print'加薪后的程序员工资列表：'
select * from ProWage
--调用存储过程1--
exec Sum_wage @PWage=2000,@AWage=100,@total=0
exec Sum_wage @PWage=2200,@AWage=100,@total=0
exec Sum_wage @PWage=3000,@AWage=100,@total=0
exec Sum_wage @PWage=4000,@AWage=100,@total=0
exec Sum_wage @PWage=5000,@AWage=100,@total=0
exec Sum_wage @PWage=6000,@AWage=100,@total=0--2、创建存储过程2--
if exists (select * from sysobjects where name='Avg_wage')
drop procedure Avg_wage
GO
create procedure Avg_wage 
@PWage int,
@AWage int,
@total int
as 
while (1=1)
begin
if ((select Avg(Wage) from ProWage)<=@PWage)
update ProWage set @total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print'一共加薪：'+convert(varchar,@total)+'元'
print'加薪后的程序员工资列表：'
select * from ProWage
--调用存储过程--
exec Avg_wage @PWage=3000,@AWage=200,@total=0
exec Avg_wage @PWage=4500,@AWage=200,@total=0

题目3

学生成绩信息三个表，结构如下： 
        学生表：Member

字段名称

数据类型

说明

MID

Char(10)

学生号，主键

MName

Char(50)

姓名

        课程表：F

字段名称

数据类型

说明

FID

Char(10)

课程，主键

FName

Char(50)

课程名

        成绩表：Score

字段名称

数据类型

说明

SID

int

自动编号，主键，成绩记录号

FID

Char(10)

课程号，外键

MID

Char(10)

学生号，外键

Score

int

成绩

        请编写T-SQL语句来实现如下功能：
1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表：

姓名

语文

数学

英语

历史

张萨

78 

67

89

76

王强

89

67

84

96

李三

70

87

92

56

李四

80

78

97

66

2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。
3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。
4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。
建表语句： 
USE master
GO
/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/
--检验数据库是否存在，如果为真，删除此数据库--
IF exists(SELECT * FROM sysdatabases WHERE name='Student')DROP DATABASE Student
GO
CREATE DATABASE Student
GO--建数据表--
USE Student
GO
CREATE TABLE Member  --学生表
(MID  char(10) primary key,  --学生号MName  CHAR(50) NOT NULL  --姓名
)
GO
CREATE TABLE F  --课程表
(FID   char(10) primary key,    --课程号
FName  CHAR(50) NOT NULL --课程名
)
GO
CREATE TABLE score  --学生成绩表
(SID int identity(1,1) primary key,  --成绩记录号FID char(10)  foreign key(FID) references F(FID) ,     --课程号MID char(10)  foreign key(MID) references Member(MID) ,     --学生号Score  int NOT NULL    --成绩
)
GO
--课程表中插入数据--
INSERT INTO F(FID,FName)VALUES('F001','语文')
INSERT INTO F(FID,FName)VALUES('F002','数学')
INSERT INTO F(FID,FName)VALUES('F003','英语')
INSERT INTO F(FID,FName)VALUES('F004','历史')
--学生表中插入数据--
INSERT INTO Member(MID,MName)VALUES('M001','张萨')
INSERT INTO Member(MID,MName)VALUES('M002','王强')
INSERT INTO Member(MID,MName)VALUES('M003','李三')
INSERT INTO Member(MID,MName)VALUES('M004','李四')
INSERT INTO Member(MID,MName)VALUES('M005','阳阳')
INSERT INTO Member(MID,MName)VALUES('M006','虎子')
INSERT INTO Member(MID,MName)VALUES('M007','夏雪')
INSERT INTO Member(MID,MName)VALUES('M008','璐璐')
INSERT INTO Member(MID,MName)VALUES('M009','珊珊')
INSERT INTO Member(MID,MName)VALUES('M010','香奈儿')
--成绩表中插入数据--
INSERT INTO Score(FID,MID,Score)VALUES('F001','M001',78)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M001',67)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M001',89)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M001',76)
INSERT INTO Score(FID,MID,Score)VALUES('F001','M002',89)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M002',67)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M002',84)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M002',96)
INSERT INTO Score(FID,MID,Score)VALUES('F001','M003',70)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M003',87)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M003',92)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M003',56)
INSERT INTO Score(FID,MID,Score)VALUES('F001','M004',80)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M004',78)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M004',97)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M004',66)
INSERT INTO Score(FID,MID,Score)VALUES('F001','M006',88)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M006',55)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M006',86)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M006',79)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M007',77)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M008',65)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M007',48)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M009',75)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M009',88)
        标准答案：
-- 1)查询各个学生语文、数学、英语、历史课程成绩-- 
SELECT Member.MName AS 姓名,
英语 = SUM(CASE F.FName WHEN '语文' THEN Score.Score END), 
数学 = SUM(CASE F.FName WHEN '数学' THEN Score.Score END), 
语文 = SUM(CASE F.FName WHEN '英语' THEN Score.Score END), 
历史 = SUM(CASE F.FName WHEN '历史' THEN Score.Score END) 
FROM Score, Member,F 
WHERE F.FID = Score.FID AND Member.MID =Score.MID GROUP BY Member.MName
-- 2)查询四门课中成绩低于70分的学生及相对应课程名和成绩-- 
select 姓名=(select MName from Member where MID=Score.MID),课程名=(select FName from F where FID=Score.FID),成绩=Score from Score where Score<70
-- 3)统计各个学生四课程的平均分，且按平均分数由高到底排序-- 
select 姓名=(select MName from Member where MID=Score.MID),平均分=Avg(Score) from Score group by MID order by 平均分 desc
-- 4)创建存储过程-- 
if exists (select * from sysobjects where name='P_stu')
drop procedure P_stu
GO
create procedure P_stu
@num int
As
print'参加'+convert(varchar(5),@num)+'门课考试的学生姓名及学号：'
select 姓名=(select MName from Member where MID=Score.MID),学号=MID from Score group by MID having count(*)=@num --调用存储过程--
exec P_stu @num=2

http://chatgpt.dhexx.cn/article/aX5WOUKN.shtml

相关文章

oracle存储过程实例

oracle存储过程实例

认识存储过程和函数 存储过程和函数也是一种PL/SQL块&#xff0c;是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序&#xff0c;我们通常把PL/SQL程序称为无名块&#xff0c;而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比&#xff…
阅读更多...
MySQL存储过程实例

MySQL存储过程实例

1、简单实例 create procedure demo.p_test1() begin-- 使用 declare语句声明一个变量declare id int default 0;declare name varchar(50) default ;-- 使用set语句给变量赋值set id7521;-- 将users表中id1的名称赋值给usernameselect ename into name from demo.emp where e…
阅读更多...
存储过程详解与实例

存储过程详解与实例

存储过程 1、存储过程的优缺点 优点 通过把处理封装在容易使用的单元中&#xff0c;简化复杂的操作&#xff1b;简化对变动的管理&#xff1b;通常存储过程有助于提高应用程序的性能&#xff1b;存储过程有助于减少应用程序和数据库服务器之间的流量&#xff0c;因为应用程序…
阅读更多...
数据库存储过程讲解与实例

数据库存储过程讲解与实例

目录 1 存储过程简介 2 存储过程使用 2.1 创建存储过程 2.2 in&#xff0c;out以及inout 1 存储过程简介 SQL语句需要先编译然后执行&#xff0c;而存储过程&#xff08;Stored Procedure&#xff09;是一组为了完成特定功能的SQL语句集&#xff0c;经编译后存储在数据库中…
阅读更多...
【数据库】ACID底层实现原理

【数据库】ACID底层实现原理

前言 我们在学MySQL的时候事务是必须要知道的部分&#xff0c;也就是原子性(Atomic)、一致性(Consistency)、隔离性(isolation)和持久性(Persistence)。知道他的概念其实是远远不够的&#xff0c;现在越来越卷&#xff0c;那么就必须知道的他的原理什么&#xff1f;怎么是实现…
阅读更多...
数据库-ACID

数据库-ACID

ACID:原子性、持久性、一致性、独立性 事务的原子性(Atomicity)&#xff1a;是指一个事务要么全部执行&#xff0c;要么不执行&#xff0c;也就是说一个事务不可能只执行了一半就停止了。比如你从取款机取钱&#xff0c;这个事务可以分成两个步骤&#xff1a;1划卡&#xff0c;…
阅读更多...
Mysql ACID详解

Mysql ACID详解

ACID简述 Atomicity、Durability实现之 &#xff08;WALredo log&#xff09; Atomicity 、Isolation实现之 &#xff08;锁 OR undo logMVCC&#xff09; 一、前言 主要是后台程序员都会和数据库打交道&#xff0c;最常用的关系型数据库是MySQL&#xff0c;最常用的存储引擎是…
阅读更多...
数据库acid实现原理（二）

数据库acid实现原理（二）

一、基础概念 事务&#xff08;Transaction&#xff09;是访问和更新数据库的程序执行单元&#xff1b;事务中可能包含一个或多个sql语句&#xff0c;这些语句要么都执行&#xff0c;要么都不执行。作为一个关系型数据库&#xff0c;MySQL支持事务&#xff0c;本文介绍基于MyS…
阅读更多...
什么是 ACID

什么是 ACID

MySQL ACID及四种隔离级别的解释 脏读、非重复读、幻读 ACID&#xff0c;是指数据库管理系统&#xff08;DBMS&#xff09;在写入或更新资料的过程中&#xff0c;为保证事务&#xff08;transaction&#xff09;是正确可靠的&#xff0c;所必须具备的四个特性&#xff1a;原子性…
阅读更多...
MySQL的ACID是如何实现的？

MySQL的ACID是如何实现的？

写在前面 本文主要探讨MySQL InnoDB 引擎下ACID的实现原理&#xff0c;对于诸如什么是事务&#xff0c;隔离级别的含义等基础知识不做过多阐述。 ACID MySQL 作为一个关系型数据库&#xff0c;以最常见的 InnoDB 引擎来说&#xff0c;是如何保证 ACID 的。 &#xff08;Atomi…
阅读更多...
数据库的ACID原则

数据库的ACID原则

一、 事务的ACID属性 原子性&#xff08;Atomicity&#xff09; 原子性是指事务是一个不可分割的工作单位&#xff0c;事务中的操作要么都发生&#xff0c;要么都不发生。 一致性&#xff08;Consistency&#xff09; 事务必须使数据库从一个一致性状态变换到另外一个一致性状…
阅读更多...
ACID理论

ACID理论

ACID 理论是对事务特性的抽象和总结&#xff0c;方便我们实现事务。可以理解成&#xff1a;如果实现了操作的 ACID 特性&#xff0c;那么就实现了事务。 1. 事务是什么 事务可以看成是一个或者多个操作的组合操作&#xff0c;并且它对这个组合操作提供一个保证&#xff0c;如果…
阅读更多...
mysql acid

mysql acid

本文实验的测试环境&#xff1a;Windows 10cmdMySQL5.6.36InnoDB 一、事务的基本要素&#xff08;ACID&#xff09; 1、原子性&#xff08;Atomicity&#xff09;&#xff1a;事务开始后所有操作&#xff0c;要么全部做完&#xff0c;要么全部不做&#xff0c;不可能停滞在中间…
阅读更多...
数据库ACID四大特性到底为了啥，一文带你看通透

数据库ACID四大特性到底为了啥，一文带你看通透

小伙伴想精准查找自己想看的MySQL文章&#xff1f;喏 → MySQL江湖路 | 专栏目录 说起数据库四大特性&#xff0c;同学们张口就来&#xff0c;ACID&#xff01;那为什么要ACID&#xff1f;每种特性的原理又是什么&#xff1f;如何实现的&#xff1f;废话少说&#xff0c;哈哥今…
阅读更多...
ACID是靠什么来保证的？

ACID是靠什么来保证的？

首先&#xff0c;什么是ACID&#xff1f; 原子性&#xff08;A&#xff09;&#xff1a; 原子性就是一个事务内的操作&#xff0c;要么全部成功&#xff0c;要么全部失败。一致性&#xff08;C&#xff09; 一致性就是一个正确的结果到另一个正确的结果。换句话说就是一个事…
阅读更多...
mysql的ACID

mysql的ACID

ACID是衡量事务的四个特性&#xff1a; 原子性&#xff08;Atomicity&#xff0c;或称不可分割性&#xff09;一致性&#xff08;Consistency&#xff09;隔离性&#xff08;Isolation&#xff09;持久性&#xff08;Durability&#xff09; 原子性&#xff1a;语句要么全执行&…
阅读更多...
ACID

ACID

细节其实很多。。。 1 到底什么是ACID 首先需要说明的是&#xff0c;在IT领域&#xff0c;很多名词在不同的上下文环境中的语义是不同的。例如某些产品宣称支持“100% ACID”和“强一致性”等。那么&#xff0c;这些名词到底指的是什么&#xff1f;如果不结合具体的语境&#x…
阅读更多...
[MySQL]事务ACID详解

[MySQL]事务ACID详解

专栏简介 :MySql数据库从入门到进阶. 题目来源:leetcode,牛客,剑指offer. 创作目标:记录学习MySql学习历程 希望在提升自己的同时,帮助他人,,与大家一起共同进步,互相成长. 学历代表过去,能力代表现在,学习能力代表未来! 目录 1. 事务的概念 2. 事务的特性 3.事务控制语法…
阅读更多...
数据库的ACID是什么

数据库的ACID是什么

欢迎大家关注我的公众号【老周聊架构】&#xff0c;Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。 事务在当今的企业系统无处不在&#xff0c;即使在高并发环境下也可以提供数据的完整性。一个事务是一个只包含所有读/写操作成功…
阅读更多...
谈谈数据库的ACID

谈谈数据库的ACID

谈谈数据库的ACID 帅宏军 一.事务 定义&#xff1a;所谓事务&#xff0c;它是一个操作序列&#xff0c;这些操作要么都执行&#xff0c;要么都不执行&#xff0c;它是一个不可分割的工作单位。 准备工作&#xff1a;为了说明事务的ACID原理&#xff0c;我们使用银行账户及资金…
阅读更多...
推荐文章

Copyright @ 2022~2023