0720 01-04 数据库BOOK/Wage/Student/Library

2024-02-17 22:20
文章标签 数据库 01 04 library student book 0720 wage

本文主要是介绍0720 01-04 数据库BOOK/Wage/Student/Library，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

  1. 使用sql脚本创建学校图书馆借书信息管理系统的三个表：

数据库名：BOOK

学生信息表：student

字段名称

数据类型

说明

stuID

char(10)

学生编号，主键

stuName

Varchar(10)

学生名称

major

Varchar(50)

专业

图书表：book

字段名称

数据类型

说明

BID

char(10)

图书编号，主键

title

char(50)

书名

author

char(20)

作者

借书信息表：borrow

字段名称

数据类型

说明

borrowID

char(10)

借书编号，主键

stuID

char(10)

学生编号，外键

BID

char(10)

图书编号，外键

T_time

datetime

借书日期

B_time

datetime

还书日期

2、向表中插入以下测试数据

--学生信息表中插入数据--

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)

3、请编写SQL语句完成以下的功能：

  1. 显示出“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示：

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

  1. 定义存储过程，实现查询任意作者的图书补借阅的情况，例如借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；参考查询结果如下图所示：

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

create database bookuse book--学校图书馆借书信息管理系统
/*
学生信息表：student
字段名称	数据类型	说明
stuID	char(10)	学生编号，主键
stuName	Varchar(10)	学生名称
major	Varchar(50)	专业
*/create table  student(
stuID	char (10)	 primary key, --学生编号stuName varchar(10),--学生名称major varchar(50) --专业
)/*
图书表：book
字段名称	数据类型	说明
BID	char(10)	图书编号，主键
title	char(50)	书名
author	char(20)	作者
*/
create table book(BID char(10) primary key,--图书编号,title char(50),--书名author char(20) --作者
)/*
借书信息表：borrow
字段名称	数据类型	说明
borrowID	char(10)	借书编号，主键
stuID	char(10)	学生编号，外键
BID	char(10)	图书编号，外键
T_time	datetime	借书日期
B_time	datetime	还书日期
*/create table borrow(borrowID	char(10) primary key, --借书编号stuID	char(10),	--学生编号 外键 BID	char(10),--图书编号 外键T_time	datetime	,--借书日期 B_time	datetime	--还书日期
)--为 borrow 表添加外键
alter table borrowadd constraint fk_borrow_student foreign key( stuID)references student(stuID)alter table borrowadd constraint fk_borrow_book foreign key(BID)references book(BID)  --向三个表中添加数据--学生信息表中插入数据--
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)select * from student 
select * from book
select * from borrow--显示出“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的
--学生编号、学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示：
select s.stuID '学生编号',s.stuName '学生姓名',bk.BID '图书编号',bk.title '图书名称',br.T_time '借出日期'
from student s inner join borrow br
on s.stuID = br.stuID inner join book bk
on br.BID = bk.BID
where s.major = '计算机' and br.t_time between '2007-12-15' and '2008-1-8'select * from student
select * from borrow--2)查询所有借过图书的学生编号、学生名称、专业；参考查询结果如下图所示：
--方法一
select distinct  s.stuID '学生编号',s.stuName '学生名称',s.major '学生专业'
from student s left join borrow br
on s.stuID= br.stuID
where br.t_time is not null--方法二
select distinct  s.stuID '学生编号',s.stuName '学生名称',s.major '学生专业'
from student s inner join borrow br
on s.stuID= br.stuID/*
3)定义存储过程，实现查询任意作者的图书补借阅的情况，
例如借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、
归还日期；参考查询结果如下图所示：
*/create procedure proc_borrowInfo@author varchar(100)
asselect s.stuName '学生姓名',bk.title '图书名称',br.t_time '借出日期',br.b_time '归还日期'from Student  s inner join borrow bron s.stuID = br.stuID inner join book bkon br.BID = bk.BIDwhere bk.author = @author
goexec  proc_borrowInfo '安意如'--查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所
--方法一
select max(s.stuName) '学生姓名' ,COUNT(*) '未还图书数量'
from student s inner join borrow br
on s.stuID = br.stuID
where br.b_time is null
group by s.stuID--方法二
select s.stuName '学生姓名' ,COUNT(*) '未还图书数量'
from student s inner join borrow br
on s.stuID = br.stuID
where br.b_time is null
group by s.stuName

 

  1. 使用sql脚本创建员工信息管理系统的一个表：

数据库名：Wage

程序员工资表：ProWage

字段名称

数据类型

说明

ID

int

自动编号，主键

PName

Char(10)

程序员姓名

Wage

int

工资

2、向表中插入以下测试数据：

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)

3、请编写T-SQL来实现如下功能：

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

例如：如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100元，直到有一半以上的人工资大于2000元，调用存储过程后的结果如图：

  1. 创建存储过程，查询程序员平均工资是否低于4500元，如果不到则每个程序员每次加200元，至到所有程序员的平均工资达到4500元。调用存储过程后的结果如图：

create database Wageuse Wage
/*
程序员工资表：ProWage
字段名称	数据类型	说明
ID	int	自动编号，主键
PName	Char(10)	程序员姓名
Wage	int	工资
*/create table ProWage(ID int identity(1,1) primary key,PName  char(10),--程序员姓名Wage int --工资 
)
drop table ProWage
--2、向表中插入以下测试数据：
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)创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，
如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，
再进行分析，直到有一半以上的人大于2000元为止，存储过程执行完后，
最终加了多少钱？ 
*/
drop procedure proc_add_wage
create procedure proc_add_wage
asdeclare @num float  --工资大于等于2000的人数declare @total float --总人数declare @rate float = 0 -- 工资超过2000的人所占的比例.declare @sal1 int --加薪前的总工资declare @sal2 int --加薪后的总工资--获得加薪前的总工资select @sal1 = SUM(Wage)from ProWage--程序员的总数select @total = COUNT(*)from ProWage--工资超过2000的人数select @num = COUNT(*)from ProWagewhere Wage >=2000--加薪前薪资超过2000的人所占比例set @rate = @num/@totalwhile(@rate<0.5)begin update ProWage set Wage = Wage +100--加薪后再计算比例 select @num = COUNT(*)from ProWagewhere Wage >=2000set @rate = @num/@totalend--获得加薪后的总工资select @sal2 =sum(Wage)from ProWageprint '一共加薪:'+convert(varchar(20),@sal2-@sal1)print '加薪后的程序员工资列表:'select * from ProWage
goselect * from ProWageexec proc_add_wage/*
2)创建存储过程，查询程序员平均工资是否低于4500元，
如果不到则每个程序员每次加200元，至到所有程序员的平均工资达到4500元。
调用存储过程后的结果如图：
*/ create procedure proc_add_sal2
asdeclare @avgSal float --员工的平均薪资declare @sal1 int --加薪前的总工资declare @sal2 int --加薪后的总工资--获得加薪前的总工资select @sal1 = SUM(wage)from ProWage--当前的平均薪资select @avgSal = AVG(Wage)from ProWagewhile(@avgSal<4500)begin--加薪update ProWage set wage = wage + 200--加薪后再求平均工资select @avgSal = AVG(Wage)from ProWageend--获得加薪后的总工资select @sal2 = SUM(Wage)from ProWageprint '一共加薪:'+convert(varchar(20),@sal2-@sal1)print '加薪后,平均薪水为:'+convert(varchar(20),@avgSal)print '加薪后的程序员工资列表:'select * from ProWage    
goexec  proc_add_sal2

 

 

  1. 使用sql脚本创建学生成绩信息三个表，结构如下： 

数据库名：Student

学生表：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

成绩

2、向表中插入以下测试数据

--课程表中插入数据--

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)

3、请编写T-SQL语句来实现如下功能：

  1. 查询四门课中成绩低于70分的学生及相对应课程名和成绩。

  1. 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。

  1. 创建存储过程，分别查询参加了1、2、3、4门考试的学生名单，要求显示姓名、学号。

use test_sql_adv/*
学生表：Member
字段名称	数据类型	说明
MID	Char(10)	学生号，主键
MName	Char(50)	姓名
*/create table Member(MID	Char(10)	primary key, --学生编号
MName	Char(50)  --学生姓名
)/*
课程表：F
字段名称	数据类型	说明
FID	Char(10)	课程，主键
FName	Char(50)	课程名
*/create table F(FID	Char(10) primary key,--课程编号FName Char(50) -- 课程名
)/*
成绩表：Score
字段名称	数据类型	说明
SID	int	自动编号，主键，成绩记录号
FID	Char(10)	课程号，外键
MID	Char(10)	学生号，外键
Score	int	成绩
*/create table Score(SID	int identity(1,1) primary key,FID	Char(10),--课程号MID	Char(10),--学生号Score int --成绩
)--添加外键
alter table Score add constraint fk_score_student foreign key(MID)references Member(MID)alter table Scoreadd constraint fk_score_F foreign key(FID)references F(FID)   --课程表(F)中插入数据--
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','历史')--学生表(Student)中插入数据--
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','香奈儿')--成绩表(Score)中插入数据--
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)查询四门课中成绩低于70分的学生及相对应课程名和成绩。
set nocount on
select m.MName '姓名',F.FName '课程名',s.Score '成绩'
from Member m inner join Score s
on m.MID= s.MID inner join F f
on s.FID = f.FID
where s.score<70--2)统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。
--方法一.
select MAX(m.MName) '姓名',AVG(s.Score) '平均分'
from Member m inner join Score s
on m.MID = s.MID
group by m.MID
order by '平均分' desc--方法二
select m.MName '姓名',AVG(s.Score) '平均分'
from Member m inner join Score s
on m.MID = s.MID
group by m.MName
order by AVG(s.Score) desc--3)创建存储过程，分别查询参加了1、2、3、4门考试的学生名单，要求显示姓名、学号。
create procedure proc_testInfo@num int
asselect max(m.MName) '姓名',m.MID '学号'from Member m inner join Score son m.MID = s.MIDgroup by m.MIDhaving count(s.score) =@num
gocreate procedure proc_show_testInfo
asdeclare @num int = 1while(@num<=4)beginexec proc_testInfo @numset @num =@num +1 end
goexec proc_show_testInfo

 

 

  1. 使用sql脚本创建学校图书馆借书信息管理系统的三个表：

数据库名：Library

学生信息表：Reader

字段名称

数据类型

说明

RID

varchar(50)

读者编号，主键，非空

RName

varchar(50)

读者姓名，非空

LendNum

int

借书数量，必须大于0，非空

图书表：Book

字段名称

数据类型

说明

BID

varchar(50)

图书编号，主键，要求以ISBN开头，非空

BName

varchar(50)

书名，非空

Author

varchar(20)

作者，非空

PubComp

varchar(20)

出版社，非空

PubDate

datetime

出版时间，必须小于当前时间，非空

BCount

int

剩余数量，必须大于或者等于1，非空

Price

float

单价，必须大于0，非空

借书信息表：Borrow

字段名称

数据类型

说明

RID

varchar(50)

读者编号，外键，引用读者表，非空

BID

varchar(50)

图书编号，外键，引用图书表, 非空

LendDate

datetime

借书日期，默认为当前时间, 非空

ReturnDate

datetime

实际归还日期，可以为空

2、向表中插入以下测试数据

--图书表中插入数据--

INSERT INTO Book VALUES('ISBN001','java','cay s.horstmann','jixiegongye',02-02-90, 100,200.0)

INSERT INTO Book VALUES('ISBN002','.net','cay s.horstmann','jixiegongye',03-03-90, 200,240.0) 

--读者表中插入数据--

INSERT INTO Reader VALUES('001','zhangYongwei','1')

INSERT INTO Reader VALUES('002','zhangDawei','2') 

--借书信息表中插入数据--

INSERT INTO Borrow(RID,BID) VALUES('001','ISBN001')

INSERT INTO Borrow(RID,BID) VALUES ('002','ISBN002')

3、请编写SQL语句完成以下的功能：

  1. 使用case-end结构显示书籍的价格等级参考查询，结果如下图所示：

  1. 定义存储过程，查询显示图书表的所有图书总额，每本书的总金额=单价*数量，并统计所有图书的现存数量，如果现存数量不足10000本，则提示’现有图书不足一万本，还需要继续购置书籍’，否则，提示’现有图书在一万本以上，需要管理员加强图书管理’；参考查询结果如下图所示：

  1. 定义存储过程，输入读者名和书名完成借书过程，先要向Borrow表中插入一条借阅记录，再将Book表对应图书的剩余数量减1，同时Reader表对应的读者信息的借书数量也要加1，整个过程应用事务控制。例如，zhangYongwei借阅了.net书籍。参考查询结果如下图所示：

create database  test_sql_adv2
use test_sql_adv2/*
学生信息表：Reader
字段名称	数据类型	说明
RID	varchar(50)	读者编号，主键，非空
RName	varchar(50)	读者姓名，非空
LendNum	int	借书数量，必须大于0，非空
*/create table Reader(RID	 varchar(50) primary key, --读者编号RName varchar(50) not null,--读者姓名LendNum int not null check(LendNum>0)	--借书数量
)/*
图书表：Book
字段名称	数据类型	说明
BID	varchar(50)	图书编号，主键，要求以ISBN开头，非空
BName	varchar(50)	书名，非空
Author	varchar(20)	作者，非空
PubComp	varchar(20)	出版社，非空
PubDate	datetime	出版时间，必须小于当前时间，非空
BCount	int	剩余数量，必须大于或者等于1，非空
Price	float	单价，必须大于0，非空
*/
create table book(BID	 varchar(50) primary key check (BID like 'ISBN%'),--图书编号BName	varchar(50)	 not null,--书名Author	varchar(20)	 not null,--作者PubComp	varchar(20)	 not null,--出版社PubDate	datetime	not null check(PubDate<getdate()),--出版时间BCount int not null  check(BCount>=1),--剩余数量 Price	float not null check(Price>0) -- 单价
)/*
借书信息表：Borrow
字段名称	数据类型	说明
RID	varchar(50)	读者编号，外键，引用读者表，非空
BID	varchar(50)	图书编号，外键，引用图书表, 非空
LendDate	datetime	借书日期，默认为当前时间, 非空
ReturnDate	datetime	实际归还日期，可以为空
*/
create table Borrow(RID	varchar(50)	 not null,--读者编号BID	varchar(50)	 not null,--图书编号LendDate	datetime not null default(getdate()),--借书日期ReturnDate	datetime --还书日期
)--添加Borrow表的外键
alter table Borrowadd constraint fk_borrow_reader foreign key(RID)references  Reader(RID)alter table Borrowadd constraint fk_borrow_book foreign key(BID)references book(BID)  --图书表中插入数据--
INSERT INTO Book VALUES('ISBN001','java','cay s.horstmann','jixiegongye',02-02-90, 100,200.0)
INSERT INTO Book VALUES('ISBN002','.net','cay s.horstmann','jixiegongye',03-03-90, 200,240.0) 
--INSERT INTO Book VALUES('ISBN003','php','cay s.horstmann','jixiegongye',03-03-90, 200,120.0) 
delete from book where BID  = 'ISBN003'--读者表中插入数据--
INSERT INTO Reader VALUES('001','zhangYongwei','1')
INSERT INTO Reader VALUES('002','zhangDawei','2') --借书信息表中插入数据--
INSERT INTO Borrow(RID,BID) VALUES('001','ISBN001')
INSERT INTO Borrow(RID,BID) VALUES ('002','ISBN002')--1)使用case-end结构显示书籍的价格等级参考查询，结果如下图所示：
set nocount on
select BID,BName,case when Price>=200 then '价格偏贵'when Price>=100 then '价格适中'else '价格便宜'
end  as '价格'
from book/*
2)定义存储过程，查询显示图书表的所有图书总额，
每本书的总金额=单价*数量，并统计所有图书的现存数量，
如果现存数量不足10000本，则提示’现有图书不足一万本，
还需要继续购置书籍’，否则，提示’现有图书在一万本以上，
需要管理员加强图书管理’；参考查询结果如下图所示：
*/create procedure proc_book_static
asdeclare @count int --总数量declare @totalPrice float --总金额 --获得书籍总数量,总金额select @count = sum(BCount),@totalPrice = SUM(Bcount*price)from book print '现存数量:'+convert(varchar(20),@count)print '总金额:'+convert(varchar(20),@totalPrice)if(@count<10000)beginprint '现有图书不足一万本，还需要继续购置书籍.'endelsebeginprint '现有图书在一万本以上,需要管理员加强图书管理'end      
goexec proc_book_staticselect * from Borrow
select * from book
select * from Reader/*
定义存储过程，输入读者名和书名完成借书过程，
先要向Borrow表中插入一条借阅记录，再将Book表对应图书的剩余数量减1，
同时Reader表对应的读者信息的借书数量也要加1，整个过程应用事务控制。
例如，zhangYongwei借阅了.net书籍。
*/
drop procedure proc_borrow_book
create procedure proc_borrow_book@reader varchar(50),@book varchar(50)
asdeclare @rid varchar(50)declare @bid varchar(50)declare @er int = 0 --错误累计变量--通过读者名获得读者的编号select @rid = RIDfrom Readerwhere RName = @reader--通过书籍名获得书籍的编号select @bid = BIDfrom bookwhere bname = @book--以下操作会改变数据库的状态，所以开启事务
begin transaction--向borrow表中插入记录insert into Borrow(RID,BID) values(@rid,@bid)set @er = @er + @@ERROR--book表中的剩余数量减1update Book set BCount = BCount - 1where BID  = @bidset @er = @er +@@ERROR--reader表中借书的数量加1update Reader set LendNum = LendNum +1where RID = @ridset @er = @er +@@ERRORif(@er>0)begin --发生错误,回滚事务print '借书失败.'rollback transaction endelsebeginprint '借书成功.'commit transaction  print '读者'+@reader+'借书情况如下:'--方法一 比较简便/* select bk.BName '书名',br.LendDate '借书日期',br.ReturnDate '归还日期' from book bk inner join borrow bron bk.BID = br.BIDwhere br.RID = @rid  */--方法二，容易理解select bk.BName '书名',br.LendDate '借书日期',br.ReturnDate '归还日期' from book bk inner join borrow bron bk.BID = br.BID inner join Reader ron br.RID = r.RIDwhere r.rname = @reader
end     
goselect * from Reader
select * from bookexec  proc_borrow_book 'zhangYongwei','.net'

 

这篇关于0720 01-04 数据库BOOK/Wage/Student/Library的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/719157。

相关文章

MySQL数据库中ENUM的用法是什么详解

MySQL数据库中ENUM的用法是什么详解

《MySQL数据库中ENUM的用法是什么详解》ENUM是一个字符串对象,用于指定一组预定义的值,并可在创建表时使用,下面：本文主要介绍MySQL数据库中ENUM的用法是什么的相关资料,文中通过代码... 目录mysql 中 ENUM 的用法一、ENUM 的定义与语法二、ENUM 的特点三、ENUM 的用法1
阅读更多...
Java中调用数据库存储过程的示例代码

Java中调用数据库存储过程的示例代码

《Java中调用数据库存储过程的示例代码》本文介绍Java通过JDBC调用数据库存储过程的方法,涵盖参数类型、执行步骤及数据库差异,需注意异常处理与资源管理,以优化性能并实现复杂业务逻辑,感兴趣的朋友... 目录一、存储过程概述二、Java调用存储过程的基本javascript步骤三、Java调用存储过程示
阅读更多...
Go语言数据库编程GORM 的基本使用详解

Go语言数据库编程GORM 的基本使用详解

《Go语言数据库编程GORM的基本使用详解》GORM是Go语言流行的ORM框架,封装database/sql,支持自动迁移、关联、事务等,提供CRUD、条件查询、钩子函数、日志等功能,简化数据库操作... 目录一、安装与初始化1. 安装 GORM 及数据库驱动2. 建立数据库连接二、定义模型结构体三、自动迁
阅读更多...
嵌入式数据库SQLite 3配置使用讲解

嵌入式数据库SQLite 3配置使用讲解

《嵌入式数据库SQLite3配置使用讲解》本文强调嵌入式项目中SQLite3数据库的重要性,因其零配置、轻量级、跨平台及事务处理特性,可保障数据溯源与责任明确,详细讲解安装配置、基础语法及SQLit... 目录0、惨痛教训1、SQLite3环境配置（1）、下载安装SQLite库（2）、解压下载的文件（3）、
阅读更多...
MySQL数据库的内嵌函数和联合查询实例代码

MySQL数据库的内嵌函数和联合查询实例代码

《MySQL数据库的内嵌函数和联合查询实例代码》联合查询是一种将多个查询结果组合在一起的方法,通常使用UNION、UNIONALL、INTERSECT和EXCEPT关键字,下面：本文主要介绍MyS... 目录一.数据库的内嵌函数1.1聚合函数COUNT([DISTINCT] expr)SUM([DISTIN
阅读更多...
MySQL追踪数据库表更新操作来源的全面指南

MySQL追踪数据库表更新操作来源的全面指南

《MySQL追踪数据库表更新操作来源的全面指南》本文将以一个具体问题为例,如何监测哪个IP来源对数据库表statistics_test进行了UPDATE操作,文内探讨了多种方法,并提供了详细的代码... 目录引言1. 为什么需要监控数据库更新操作2. 方法1：启用数据库审计日志（1）mysql/mariad
阅读更多...
postgresql数据库基本操作及命令详解

postgresql数据库基本操作及命令详解

《postgresql数据库基本操作及命令详解》本文介绍了PostgreSQL数据库的基础操作,包括连接、创建、查看数据库,表的增删改查、索引管理、备份恢复及退出命令,适用于数据库管理和开发实践,感兴... 目录1. 连接 PostgreSQL 数据库2. 创建数据库3. 查看当前数据库4. 查看所有数据库
阅读更多...
从入门到精通MySQL 数据库索引(实战案例)

从入门到精通MySQL 数据库索引(实战案例)

《从入门到精通MySQL数据库索引(实战案例)》索引是数据库的目录,提升查询速度,主要类型包括BTree、Hash、全文、空间索引,需根据场景选择,建议用于高频查询、关联字段、排序等,避免重复率高或... 目录一、索引是什么？能干嘛？核心作用：二、索引的 4 种主要类型（附通俗例子）1. BTree 索引（
阅读更多...
Oracle 数据库数据操作如何精通 INSERT, UPDATE, DELETE

Oracle 数据库数据操作如何精通 INSERT, UPDATE, DELETE

《Oracle数据库数据操作如何精通INSERT,UPDATE,DELETE》在Oracle数据库中,对表内数据进行增加、修改和删除操作是通过数据操作语言来完成的,下面给大家介绍Oracle数... 目录思维导图一、插入数据 (INSERT)1.1 插入单行数据，指定所有列的值语法：1.2 插入单行数据，指
阅读更多...
k8s上运行的mysql、mariadb数据库的备份记录(支持x86和arm两种架构)

k8s上运行的mysql、mariadb数据库的备份记录(支持x86和arm两种架构)

《k8s上运行的mysql、mariadb数据库的备份记录(支持x86和arm两种架构)》本文记录在K8s上运行的MySQL/MariaDB备份方案,通过工具容器执行mysqldump,结合定时任务实... 目录前言一、获取需要备份的数据库的信息二、备份步骤1.准备工作（X86）1.准备工作（arm）2.手
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2