数据库原理与应用——SqlServe2012 期末练习2

最新推荐文章于 2022-07-09 16:38:34 发布

九十九度五

最新推荐文章于 2022-07-09 16:38:34 发布

阅读量433

点赞数

本文链接：https://blog.csdn.net/m0_46637061/article/details/117903953

版权

本文详细介绍了SQL查询语句的使用，包括删除前导空格、创建数据库、表及视图、数据插入、复杂查询、用户权限管理、存储过程、触发器等。通过实例展示了如何在school数据库中进行学生、课程和成绩的数据操作，以及如何实现特定功能，如查找高分学生、比较成绩、创建视图和存储过程等。

摘要由CSDN通过智能技术生成

已知字符串’ ABCDEFG’，请用查询指令删除前导空（3个空格），并使得查询结果为CDE。

select SUBSTRING (ltrim('   ABCDEFG'),3,3)

在这里插入图片描述
考察知识点：
常用系统标量函数：
a、substring 函数用法
语法格式：
substring(表达式，起始，长度）
返回表达式中指定的部分数据，参数“表达式”可为字符串、二进制串、text、image字段或表达式：“起始”、“长度”均为整型，前者指定子串的开始位置，后者指定子串的长度（要返回的字节数）。如果“表达式”是字符类型和二进制类型，则返回值类型与“表达式”的类型相同。

b、ltrim（）函数
语法格式：
LTRIM(字符表达式）
删除“字符表达式”字符串中的前导空格（即左侧空格），并返回字符串。
（相应的删除字符串右侧空格函数为RTRIM()）

使用T-SQL语句建立数据库school，它包含3个文件组：主文件组、sch1文件组、sch2文件组。主文件组包含10MB的schmgt1和10MB的schmgt2两个文件，两个文件的最大尺寸为40MB，增长长度为2MB；sch1文件组包含10MB的schmgt3和10MB的schmgt4两个文件，两个文件的最大尺寸为默认，增长长度为1MB；sch2文件组包含10MB的schmgt5一个文件，这个文件的最大尺寸为20MB，增长长度为10%。该数据库同时还包含一个日志文件schmgtlog，文件大小为20MB，增长长度为10％。（存储路径都为d:\school）

create database school2
on
(name=schmgt1,
 filename='D:\SQLSERVER\school2\schmgt1.mdf',
 size=10,
 maxsize=40,
 filegrowth=2),
 (name=schmgt2,
  filename='D:\SQLSERVER\school2\schmgt2.ndf',
  size=10,
  maxsize=40,
  filegrowth=2),
  filegroup sch1
  (name=schmgt3,
  filename='D:\SQLSERVER\school2\schmgt3.ndf',
  size=10,
  filegrowth=1),
  (name=schmgt4,
  filename='D:\SQLSERVER\school2\schmgt4.ndf',
  size=10,
  filegrowth=1),
  filegroup sch2
  (name=schmgt5,
  filename='D:\SQLSERVER\school2\schmgt5.ndf',
  size=10,
  maxsize=20,
  filegrowth=10%)
  log on
  (name=schmgtlog,
  filename='D:\SQLSERVER\school2\schmgtlog.ldf',
  size=20,
  filegrowth=10%)

在这里插入图片描述
3.在school数据库中包含有学生信息表（student）、课程信息表（course）和成绩表（score），它们的定义分别为：
student (学号 char(6) not null，姓名 char(8)，性别 char(2)，出生时间 datetime，专业 char(12)，总学分int 0到100学分之间，备注varchar(500))
course (课程号 char(3) not null，课程名 char(16)not null，开课学期tinyint default 1，学时tinyint default 0，学分tinyint default 0)
score (学号 char(6) not null，课程号 char(3) not null，成绩int default 0, 主键为学号与课程号的组合)
（1）用T-SQL语句分别创建表student 表，course表，score表，并插入相关数据，相关数据见附录。

use school2
go
create table student
(学号 char(6)  not null,
 姓名 char(8),
 性别 char(2),
 出生时间 datetime,
 专业 char(12),
 总学分 int check (总学分>=0 and 总学分 <=100),
 备注 varchar(500)
 )

 create table course
 (课程号 char(3)  not null,
 课程名 char(16) not null,
 开课学期 tinyint default 1,
 学时 tinyint default 0,
 学分 tinyint default 0)

 create table score 
 (学号 char(6)  not null,
 课程号 char(3)  not null,
 成绩 int default 0, 
 constraint sn_cou primary key (学号,课程号)
 )


 insert into student values('081101','王林','男','1990-2-10','计算机',50,
                       null)
insert into student values('081102','程明','男','1991-2-1','计算机',50,
                       null)
insert into student values('081103','王燕','女','1989-10-6','计算机',50,
                       null)
insert into student values('081104','韦严平','男','1990-8-26','计算机',50,
                       null)  
insert into student values('081106','李方方','男','1990-11-20','计算机',50,
                       null)
insert into student values('081107','李明','男','1990-5-1','计算机',54,
                       '提前修完《数据结构》，并获学分')
insert into student values('081108','林一帆','男','1989-8-5','计算机',52,
                       '已提前修完一门课')
insert into student values('081109','张强民','男','1989-8-11','计算机',50,
                       null)
insert into student values('081110','张蔚','女','1991-7-22','计算机',50,
                       '三好学生')
insert into student values('081111','赵琳','女','1990-3-18','计算机',50,
                       null)
insert into student values('081113','严红','女','1989-8-11','计算机',48,
                       '有一门课不及格，待补考')
insert into student values('081201','王敏','男','1989-6-10','通信工程',42,
                       null)  
insert into student values('081202','王林','男','1989-1-29','通信工程',40,
                       '有一门课不及格，待补考')
insert into student values('081203','王玉民','男','1990-3-26','通信工程',42,
                       null)
insert into student values('081206','李计','男','1989-9-20','通信工程',42,
                       null)
insert into student values('081210','李红庆','男','1989-5-1','通信工程',44,
                       '已提前修完一门课，并获得学分')
insert into student values('081216','孙祥欣','男','1989-3-19','通信工程',42,
                       null)
insert into student values('081218','孙研','男','1990-10-9','通信工程',42,
                       null)
insert into student values('081220','吴薇华','女','1990-3-18','通信工程',42,
                       null)  
insert into student values('081221','刘燕敏','女','1989-11-12','通信工程',42,
                       null)
insert into student values('081241','罗琳琳','女','1990-1-30','通信工程',50,
                       '转专业学习')


insert into course values('101','计算机基础',1,80,5)
insert into course values('102','程序设计与语言',2,68,4)  
insert into course values('206','离散数学',4,68,4)
insert into course values('208','数据结构',5,68,4)
insert into course values('210','计算机原理',5,85,5)  
insert into course values('209','操作系统',6,68,4)  
insert into course values('212','数据库原理',7,68,4)
insert into course values('301','计算机网络',7,51,3)  
insert into course values('302','软件工程',7,51,3)


insert into score values('081101','101',80)
insert into score values('081101','102',78)
insert into score values('081101','206',76)
insert into score values('081103','101',62)
insert into score values('081103','102',70)
insert into score values('081103','206',81)
insert into score values('081104','101',90)
insert into score values('081104','102',84)
insert into score values('081104','206',65)
insert into score values('081102','102',78)
insert into score values('081102','206',78)
insert into score values('081106','101',65)
insert into score values('081106','102',71)
insert into score values('081106','206',80)
insert into score values('081107','101',78)
insert into score values('081107','102',80)
insert into score values('081107','206',68)
insert into score values('081108','101',85)
insert into score values('081108','102',64)
insert into score values('081108','206',87)
insert into score values('081109','101',66)
insert into score values('081109','102',83)
insert into score values('081109','206',70)
insert into score values('081110','101',95)
insert into score values('081110','102',90)
insert into score values('081110','206',89)
insert into score values('081111','101',91)
insert into score values('081111','102',70)
insert into score values('081111','206',76)
insert into score values('081113','101',63)
insert into score values('081113','102',79)
insert into score values('081113','206',60)
insert into score values('081201','101',80)
insert into score values('081202','101',65)
insert into score values('081203','101',87)
insert into score values('081204','101',91)
insert into score values('081210','101',76)
insert into score values('081216','101',81)
insert into score values('081218','101',70)
insert into score values('081220','101',82)
insert into score values('081221','101',76)
insert into score values('081241','101',90)

（2）找出“计算机基础”课程成绩在90分以上的学生姓名和专业。

use school2
go
select 姓名,专业 from student,score ,course 
where student.学号=score.学号 and course.课程名='计算机基础'
  and course .课程号 =score .课程号 and score.成绩 >90

在这里插入图片描述
（3）查找比所有计算机系学生年龄都小的学生。

--自己写的过于麻烦了
use school2
go
select * from student
where  datediff(DAY ,student .出生时间 ,GETDATE() )<
(select min(DATEDIFF (day,student.出生时间 ,GETDATE() )) from student
where student.专业 ='计算机')

--以下为参考答案
select * from student
where 出生时间 >(select max(出生时间) from student 
where 专业 ='计算机')

查询结果为空
在这里插入图片描述

（4）检索选修2门以上课程的每个学生的平均成绩，并要求按平均成绩的降序排列。

select 学号,avg(成绩) AS 平均成绩 from score
group by 学号
having count(学号)>2
order by avg(成绩) desc

在这里插入图片描述
（5）从student表中查找所有男学生的姓名、学号以及其与张蔚同学的年龄差距。
自写代码：

select 姓名,学号,datediff(YEAR,出生时间,(select 出生时间 from student
where 姓名='张蔚'))
from student
where 性别='男'

在这里插入图片描述
参考答案：

select 姓名,学号,Year(出生时间)-
(
	select Year(出生时间)
	from student
	where 姓名='张蔚'
) AS 年龄差
from student
where 性别='男'

在这里插入图片描述
（6）创建计算机专业学生总成绩视图CE_SUM，包括学号（在视图中列名为num）和总成绩（在视图中列名为score_sum）。

create view CE_SUM
as
select score.学号 as num ,sum(成绩) as score_sum from score,student
where 专业='计算机'and score.学号 =student.学号 
group by score.学号

在这里插入图片描述
（7）查找不同课程成绩相同的学生的姓名、课程名和成绩。

select 姓名,c1.课程名,sc1.成绩,c2.课程名,sc2.成绩 
from student s,course c1,score sc1,course c2,score sc2
where 
     s.学号 =sc1.学号 and s.学号 =sc2.学号 
	  and c1.课程号 =sc1.课程号 and c2.课程号 =sc2.课程号 
      and  c1.课程号 <>c2.课程号  and sc1.成绩 =sc2.成绩

在这里插入图片描述

（8）创建用户定义函数，查询全体学生某门功课最高分、平均分和最低分，并调用该函数查询课程号为101课程的最高分、平均分、最低分。（函数名为MAM_FUN）

use school2
go
create function MAM_FUN(@no char(3)) returns table
as return
select 课程号 ,max(成绩)最高分,min(成绩)最低分,avg(成绩)平均分 from score
where 课程号=@no
group by 课程号

select * from dbo.MAM_FUN(101)

在这里插入图片描述
（9）创建一个存储过程CPA，比较两个学生(用学号表示)的平均成绩，若前者比后者高就输出1，否则输出0。

use school2
go
create procedure CPA @no1  char(6),@no2  char(6)
as
begin
declare @sc1 int
declare @sc2 int
set @sc1=(select avg(成绩) from score where 学号=@no1)
set @sc2=(select avg(成绩) from score where 学号=@no2)
if @sc1>@sc2 
BEGIN print '1' END
ELSE 
BEGIN print '0' END
end
go


execute CPA '081101','08110

在这里插入图片描述
（10）创建触发器score_insert，当向score表中插入数据时，检查学号字段的值在student表中是否存在，若存在，则允许插入，并提示“插入数据成功”；若不存在，则取消插入操作，并提示“该学号不存在于student表中，不能插入记录，插入将终止！”。

USE school
GO
create trigger score_insert on score
for insert
as
declare @num char(6)
select @num=student.学号
from student,inserted
where student.学号=inserted.学号
if @num is not null
print('插入数据成功')
else
begin
print('该学号不存在于student表中，不能插入记录，插入将终止！')
rollback transaction
end

在数据库school上给用户Tim和Lintao授予创建表的权限，并使他们有对student表的所有操作权限。

USE school2
go
grant create table to Tim,Lintao
grant all student to Tim,Lintao

以命令方式撤销用户Tim在score表中的SELECT、UPDATE权限。

revoke select,update on score from Tim

采用文件备份的方式将school数据库备份一份。（备份目录为d:\school_BP）

自写代码：

backup database school2
to disk='D:\SQLSERVER\school2\school2.bak'
with format,
name='school2的完整备份'

参考答案

EXEC sp_addumpdevice 'DISK','BP1','d:\school_BP\school.bak'
BACKUP DATABASE school TO BP1

附件：

附录：
student表：
insert into student values('081101','王林','男','1990-2-10','计算机',50,
                       null)
insert into student values('081102','程明','男','1991-2-1','计算机',50,
                       null)
insert into student values('081103','王燕','女','1989-10-6','计算机',50,
                       null)
insert into student values('081104','韦严平','男','1990-8-26','计算机',50,
                       null)  
insert into student values('081106','李方方','男','1990-11-20','计算机',50,
                       null)
insert into student values('081107','李明','男','1990-5-1','计算机',54,
                       '提前修完《数据结构》，并获学分')
insert into student values('081108','林一帆','男','1989-8-5','计算机',52,
                       '已提前修完一门课')
insert into student values('081109','张强民','男','1989-8-11','计算机',50,
                       null)
insert into student values('081110','张蔚','女','1991-7-22','计算机',50,
                       '三好学生')
insert into student values('081111','赵琳','女','1990-3-18','计算机',50,
                       null)
insert into student values('081113','严红','女','1989-8-11','计算机',48,
                       '有一门课不及格，待补考')
insert into student values('081201','王敏','男','1989-6-10','通信工程',42,
                       null)  
insert into student values('081202','王林','男','1989-1-29','通信工程',40,
                       '有一门课不及格，待补考')
insert into student values('081203','王玉民','男','1990-3-26','通信工程',42,
                       null)
insert into student values('081206','李计','男','1989-9-20','通信工程',42,
                       null)
insert into student values('081210','李红庆','男','1989-5-1','通信工程',44,
                       '已提前修完一门课，并获得学分')
insert into student values('081216','孙祥欣','男','1989-3-19','通信工程',42,
                       null)
insert into student values('081218','孙研','男','1990-10-9','通信工程',42,
                       null)
insert into student values('081220','吴薇华','女','1990-3-18','通信工程',42,
                       null)  
insert into student values('081221','刘燕敏','女','1989-11-12','通信工程',42,
                       null)
insert into student values('081241','罗琳琳','女','1990-1-30','通信工程',50,
                       '转专业学习')

course表：
insert into course values('101','计算机基础',1,80,5)
insert into course values('102','程序设计与语言',2,68,4)  
insert into course values('206','离散数学',4,68,4)
insert into course values('208','数据结构',5,68,4)
insert into course values('210','计算机原理',5,85,5)  
insert into course values('209','操作系统',6,68,4)  
insert into course values('212','数据库原理',7,68,4)
insert into course values('301','计算机网络',7,51,3)  
insert into course values('302','软件工程',7,51,3)

score表：
insert into score values('081101','101',80)
insert into score values('081101','102',78)
insert into score values('081101','206',76)
insert into score values('081103','101',62)
insert into score values('081103','102',70)
insert into score values('081103','206',81)
insert into score values('081104','101',90)
insert into score values('081104','102',84)
insert into score values('081104','206',65)
insert into score values('081102','102',78)
insert into score values('081102','206',78)
insert into score values('081106','101',65)
insert into score values('081106','102',71)
insert into score values('081106','206',80)
insert into score values('081107','101',78)
insert into score values('081107','102',80)
insert into score values('081107','206',68)
insert into score values('081108','101',85)
insert into score values('081108','102',64)
insert into score values('081108','206',87)
insert into score values('081109','101',66)
insert into score values('081109','102',83)
insert into score values('081109','206',70)
insert into score values('081110','101',95)
insert into score values('081110','102',90)
insert into score values('081110','206',89)
insert into score values('081111','101',91)
insert into score values('081111','102',70)
insert into score values('081111','206',76)
insert into score values('081113','101',63)
insert into score values('081113','102',79)
insert into score values('081113','206',60)
insert into score values('081201','101',80)
insert into score values('081202','101',65)
insert into score values('081203','101',87)
insert into score values('081204','101',91)
insert into score values('081210','101',76)
insert into score values('081216','101',81)
insert into score values('081218','101',70)
insert into score values('081220','101',82)
insert into score values('081221','101',76)
insert into score values('081241','101',90)