【SQL SERVER练习】用企业管理器或T-SQL语句搞事儿

🍊前言

浅记录了笔者在数据库学习途中，遇到的比较有意思的习题，可作为参考可收藏嗷~

🍈题目一、T-SQL语句创建4个关系表

用企业管理器或T-SQL语句创建4个关系表

第一题蛮乖的，直接根据要求就好，注意主键的设置

📌参考代码

--1.用企业管理器或T-SQL语句创建4个关系表
create table depart
(
	班号 int primary key,
	系名 char(20)
)

create table student
(
	学号 int primary key,
	姓名 char(10),
	性别 char(2),
	出生日期 datetime,
	班号 int
)

create table book
(
	图书编号 char(20) primary key,
	图书名 char(40),
	作者 char(10),
	定价 varchar(50),
	出版社 char(30)
)

create table borrow
(
	学号 int ,
	图书编号 char(10),
	借书日期 datetime,
	primary key clustered(学号,图书编号)
)

🍅题目二、信息填充

用企业管理器或T-SQL语句输入数据，要求先输入主键表中的数据，再输入外键表的数据，数据一致不要冲突。

📌参考代码

第二题主要是要手动录入这些数据，挺麻烦的，可以买杯瑞幸，边喝边做~

--2.用企业管理器或T-SQL语句输入数据，要求先输入主键表中的数据，再输入外键表的数据，数据一致不要冲突。
insert into depart values(0501,'计算机系'),
						(0502,'计算机系'),
						(0801,'电子工程系'),
						(0802,'电子工程系')

insert into student values(1,'张任','男','1995-01-02',0501),
						(2,'程华','男','1996-01-10',0501),
						(3,'张丽','女','1995-06-07',0502),
						(4,'王英','女','1994-12-10',0502),
						(5,'李静','男','1995-04-05',0502),
						(10,'许兵','男','1995-08-10',0801),
						(11,'张功','男','1995-06-02',0801),
						(12,'李华','男','1994-10-03',0801),
						(13,'马超','男','1996-02-03',0802),
						(14,'曾英','女','1994-03-06',0802)

insert into book values('10011', 'C程序设计', '李洪',24, '清华大学出版社'),
					('10012', 'C程序设计', '李洪',24, '清华大学出版社'),
					('10013', 'C习题解答', '李洪' ,12, '清华大学出版社'),
					('10014', 'C习题解答', '李洪' ,12, '清华大学出版社'),
					('10020', '数据结构', '徐华',29, '人民邮电出版社'),
					('10021', '数据结构', '徐华',29, '人民邮电出版社'),
					('10023' , '高等数学', '王涛',30, '高等教育出版社'),
					('10034', '软件工程', '张明',34, '机械工业出版社'),
					('20025', '信息学', '张港',35, '清华大学出版社'),
					('20026', '信息学', '张港',35, '清华大学出版社'),
					('20042','电工学', '王民',30, '人民邮电出版社'),
					('20056', '操作系统', '曾平',26, '清华大学出版社'),
					('20057' , '操作系统', '曾平',26, '清华大学出版社'),
					('20058', '操作系统', '曾平',26, '清华大学出版社'),
					('20067', '数字电路', '徐汉' ,32, '高等教育出版社'),
					('20140', '数据库原理', '陈曼',32, '高等教育出版社'),
					('20090', '网络工程', '黄军',38, '高等教育出版社')

insert into borrow values(1,'10020','2013-12-05'),
						(1,'20025','2013-11-08'),
						(1,'20059','2014-04-11'),
						(2,'10011','2013-10-02'),
						(2,'10013','2014-04-03'),
						(3,'10034','2014-04-10'),
						(3,'20058','2014-04-11'),
						(4,'10012','2014-04-06'),
						(5,'10023','2014-02-03'),
						(10,'20056','2014-02-05'),
						(12,'20067','2014-03-06')

🍑题目三、对各出版社的图书比例情况进行分析

对各出版社的图书比例情况进行分析，即图书比例高于50%为“很高”，图书比例高于30%为“较高”，图书比例高于10%为“一般”。并按图书比例递增排列。

📌参考代码

这个题就是常规的查找，但是注意题目要求排序和要处理不同的情况了，开心拿分~

--3. 对各出版社的图书比例情况进行分析，即图书比例高于50%为“很高”，图书比例高于30%为“较高”，图书比例高于10%为“一般”。并按图书比例递增排列。
DECLARE @num int
SELECT @num=
(
	SELECT COUNT(*)
	FROM book
)
SELECT book.出版社 AS '出版社',
CASE
	WHEN book.rate>50.0 THEN'很高'
	WHEN book.rate>30.0 THEN'较高'
	WHEN book.rate>10.0 THEN'一般'
ELSE '较低'
END AS '图书比例情况'
FROM 
(
	SELECT 出版社 AS '出版社',
	CAST(ROUND(COUNT(*)*100.0/@num,1) AS decimal(5,1)) AS 'rate'
	FROM book
	GROUP BY 出版社
)book 
ORDER BY book.rate

🍉题目四、新建一个存储过程，根据出版社查询图书数量

直接根据要求放我的代码啦~

📌参考代码

--4.新建一个存储过程，根据出版社查询图书数量。
create proc usp_bookInformation
  @bookSum  int output,
  @chubans char(30)
as
   select *
   from Book
   where book.出版社=@chubans
  
   select @bookSum=COUNT(*)
   from Book
   where book.出版社=@chubans
go
   
 declare @bookSum int
 exec  usp_bookInformation @bookSum output,'人民邮电出版社'
 print '人民邮电出版社的图书共有'+convert(varchar(20),@bookSum)+'本'

🍇题目五、新建一个DML触发器，当删除depart表数据时，撤销删除

感觉触发器这儿挺妖艳的

📌参考代码

 --5.新建一个DML触发器，当删除depart表数据时，撤销删除，并打印消息“无法删除depart表数据”。
 create trigger T_del
 on depart
 after delete
 as
	if exists(select* from deleted)
	begin
	print'无法删除depart表数据'
	rollback transaction
	end
go

delete depart
where 班号='0501'

🍏题目六、采用游标方式输出所有图书编号，图书名，价格

这是一个选做题，但是在我们眼里，题都应该被解

📌参考代码

--6.（选做）采用游标方式输出所有图书编号，图书名，价格。
declare @图书编号 char(20),@图书名 char(40),@价格 float
declare cur_book cursor
for select 图书编号,图书名,定价 from book
open cur_book
fetch next from cur_book into @图书编号,@图书名,@价格
print '图书编号		           图书名		                       价格'
print '----------------------------------------------------------------'
while @@fetch_status=0
begin
	print cast(@图书编号 as char(20))+cast(@图书名 as char(40))+cast(@价格 as varchar)
	fetch next from cur_book into @图书编号,@图书名,@价格
end
close cur_book
deallocate cur_book

浅总结

注意题目要查找的内容，梳理好它们是在哪些表中。
其次了，舒服的代码排版也可以帮助自己理解嗷~