中南林业科技大学数据库实验五：问题分析与详解，穿插SQL规范

文章目录

一、目的与要求
二、实验准备
三、实验内容
四、思考与练习

一、目的与要求

掌握SQL语言的查询功能；
掌握SQL语言的数据操作功能；
握对象资源管理器建立查询、索引和视图的方法；

二、实验准备

了解SQL语言的查改增删四大操作的语法；
了解查询、索引和视图的概念；
了解各类常用函数的含义。

三、实验内容

使用提供的studentdb数据库文件，先附加到目录树中，再完成下列题目，SQL命令请保存到脚本文件中。

(一)SQL查询功能

1.基本查询

1️⃣ 查询所有姓王的学生的姓名、学号和性别

select St_Name,St_ID,St_Sex
from st_info
where St_Name like '王%';

2️⃣ 查询全体学生的情况，查询结构按班级降序排列，同一班级再按学号升序，并将结果存入新表new中

select St_ID,St_Name,St_Sex,Born_Date,Cl_Name,Telephone,Address,Resume
into new_table
from st_info
order by Cl_Name desc,St_ID asc;

提醒：

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：

MySQL 在解析的过程中，会通过查询数据字典将 * 按序转换成所有列名，这会大大的耗费资源和时间。

无法使用 覆盖索引

3️⃣ 对s_c_info表中选修了“体育”课的学生的平均成绩

? 法一：子查询

select avg(score)
from s_c_info
where c_no = (select C_No
		      from C_Info
		      where C_Name='体育');

? 法二：连接查询(多表查询)

select avg(score)
from s_c_info join C_info
on s_c_info.c_no = C_info.C_No
where C_info.C_Name = '体育';

看一段代码：
select avg(score)
from s_c_info,C_info
where s_c_info.c_no = C_info.C_No
and C_info.C_Name = '体育';
这段代码与法二本质是一样的。

法二写的是SQL99的规范，使用表1 join 表2 on ...来代替表1,表2 where ...。

这段代码写的是SQL92的规范，即表1，表2 where ...。

2.嵌套查询

嵌套查询指的是一个查询语块可以嵌套在另外一个查询语句块的where子句或者having子句中，前者为子查询或内查询，后者为父查询或外查询。

1️⃣ 查询其他班级中比“材料科学0601班”的学生年龄都大的学生姓名和年龄

? 我们可以先查询到“材料科学0601班”中年龄最大（即出生最早）的同学的出生日期

select min(Born_Date)
from st_info
where Cl_Name='材料科学0601班';

? 再用这个出生日期去与其他同学比较

select St_Name as name,DATEDIFF(DAY,Born_Date,GETDATE())/365 as age
from st_info
where Born_Date < (select min(Born_Date)
				   from st_info
                   where Cl_Name='材料科学0601班');

2️⃣ 用exists查询选修了“9710041”课程的学生姓名

select St_Name
from st_info
where exists (select 1 
              from s_c_info 
              where c_no=9710041 
              and s_c_info.st_id = st_info.St_ID); #相关子查询

3️⃣ 用in查询找出没有选修“9710041”课程的学生的姓名和所在班级。

? 我们先从s_c_info表查询出选修了该课程的学生的 st_id

select st_id 
from s_c_info 
where c_no=9710041;

? 再拿这上面的这个筛选出来st_id的临时表，去与st_info表的每一条记录比较，如果st_info表的某条记录的st_id在临时表中查不到，则显示该记录的学生信息

select St_Name,Cl_Name
from st_info
where St_ID not in (select st_id 
                    from s_c_info 
                    where c_no=9710041);

4️⃣ 查询选修了学号为“2001050105”的学生所选全部课程的学生姓名。

? 我们先查询学号为"2001050105"的学生A的全部所选课程的课程号，查询结果设置为临时表t

select c_no
from s_c_info
where st_id='2001050105'

? 再用s_c_info表，取别名为s表，与t表进行连接查询（多表查询），连接条件为 s.c_no = t.c_no，目的是查询到所有与学生A选修了一个或多个相同课程的学生选修课程信息。

select sc.st_id
from s_c_info sc,(select c_no
                  from s_c_info
                  where st_id='2001050105') t
where sc.c_no = t.c_no

? 这时候的sc.st_id就可能存在重复出现，因为可能有学生与学生A选修了超过一门的相同课程，这是应该如此的，我们不应该使用 distinct去重。现在就可以仔细想想：

如果sc.st_id出现了1次，说明该st_id代表的学生的选修了的课程，与学生A选修了的课程是有1门相同的
如果sc.st_id出现了2次，说明该st_id代表的学生的选修了的课程，与学生A选修了的课程是有2门相同的
如果sc.st_id出现了3次，说明该st_id代表的学生的选修了的课程，与学生A选修了的课程是有3门相同的

所以如此看来，如果sc.st_id出现的次数与学生A选修的课程数目相同，即可说明该st_id代表的学生与学生A所选修的课程完全相同。

那如何计算次数呢？

sc.st_id我们分组查询即可，即将 group by sc.st_id进行分组
学生A选修的课程数目，我们使用直接以st_id = '2001050105'为过滤条件查询s_c_info表课程数目即可

如果满足次数相同（在having中完成等值比较），就显示出sc.st_id

select sc.st_id
from s_c_info sc,(select c_no
                  from s_c_info
                  where st_id='2001050105') t
where sc.c_no = t.c_no
group by sc.st_id
having count(*) = (select COUNT(*)
                   from s_c_info
                   where st_id='2001050105')

? 上述查询构建的临时表x，即为满足条件的学生st_id，我们就只需要遍历st_info表中每一条记录，满足St_ID在临时表x中有等值的st_id，我们就显示这个同学的名字。

select St_Name
from st_info
where St_ID in (select sc.st_id
                from s_c_info sc,(select c_no
                                 from s_c_info
                                 where st_id='2001050105') t
                where sc.c_no = t.c_no
                group by sc.st_id
                having count(*) = (select COUNT(*)
                                   from s_c_info
                                   where st_id='2001050105'));

3.连接综合查询及其他

操作前的几点提醒：

我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。阿里的规范手册就强制规范了Join最多不要超过三层，那我们怎么解决这个问题呢？我们可以将原来复杂的多表查询的SQL语句拆分，分成多个小的SQL实现功能。
如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。
一旦涉及到多表查询，如果使用到表的字段，在where、on、having、select等其他无论什么地方，我们必须明确该字段来自哪个表，即表名.字段名的形式。

? 举个反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出1052 异常：Column ‘name’ in field list is ambiguous。提示也很明确，DBMS告诉我们这个name字段摸棱两可，不知道哪个表，这时候就真的蛋糕了，因此你可以看出如果不加表名，是很不利于我们后期的维护的。根据墨菲定律，是极有可能发生的? 。

1️⃣ 查询每个学生所选课程的最高成绩，要求列出学号，姓名，课程编号和分数。

? 我们先查询到每位学生的最高成绩，对这个临时表取个别名b

select st_id,max(score) score
from s_c_info
group by st_id

? 接下来我们就进行多表连接查询，这里采用SQL99的表1 join 表2 on...语法，当然也可以使用SQL92的表1,表2 where ...语法代替。

首先将s_c_info表取别名为a，将表a 与表b 连接，连接条件是 a.st_id = b.st_id and b.score = a.score，这样我们就得到了每位学生最高分数对应的学号。那之后就很简单了，在进行一次与st_info表（取别名为st）的连接，通过a.St_ID=st.st_id即可匹配到对应的学生信息。

select st.St_ID,st.St_Name,a.c_no,b.score
from s_c_info a 
join (select st_id,max(score) score
	  from s_c_info
	  group by st_id) b
on a.st_id = b.st_id and b.score = a.score 
join st_info st
on a.St_ID=st.st_id;

2️⃣ 查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生总成绩为空。

? 我们先查询出s_c_info表中记录的每名学生的总成绩，通过st_id进行分组依据。给查询到的结果设为临时表sc

select st_id,sum(score) score
from s_c_info
group by st_id

由于题目给出没有选修课程的学生总成绩为空，因此我们进行外连接。

select st.St_ID,st.St_Name,sc.score
from st_info st left join (select st_id,sum(score) score
						   from s_c_info
						   group by st_id) sc
on st.St_ID = sc.st_id;

3️⃣ 查询“大学计算机基础”课程考试成绩前三名的学生姓名和成绩。

? 我们先得到"大学计算机基础"的课程号

select C_No 
from C_Info 
where C_Name='大学计算机基础'

? 我们也可以将 s_c_info 与 st_info 进行连接查询，得到 s_c_info 表中每行记录学号对应的学生姓名

select st_info.St_Name,s_c_info.score
from s_c_info join st_info
on s_c_info.st_id = st_info.St_ID

? 紧接着利用第一步的查询结果筛选出 s_c_info 表中 c_no 等于大学计算机基础的课程号的记录

select st_info.St_Name,s_c_info.score
from s_c_info join st_info
on s_c_info.st_id = st_info.St_ID
and s_c_info.c_no = (select C_No 
                    from C_Info 
                    where C_Name='大学计算机基础')

? 我们对查询到的结果进行依照 score 进行降序排列，目的是为了接下来得到前三名成绩

select st_info.St_Name,s_c_info.score
from s_c_info join st_info
on s_c_info.st_id = st_info.St_ID
and s_c_info.c_no = (select C_No 
                    from C_Info 
                    where C_Name='大学计算机基础')
order by s_c_info.score desc;

? 最后一步，因为只需要前三名的成绩，而进行降序排列后，最高的三个人的成绩肯定是前三条记录，我们取前三条记录即可，在select后面加上top 3

select top 3 
st_info.St_Name,s_c_info.score
from s_c_info join st_info
on s_c_info.st_id = st_info.St_ID
and s_c_info.c_no = (select C_No 
                    from C_Info 
                    where C_Name='大学计算机基础')
order by s_c_info.score desc;

4️⃣ 将s_c_info中的score列的值转为等级制输出，即60分以下显示为“不及格”，60~69分显示“及格”，70~79分显示“中等”，80~81显示“良好”，90~100显示“优秀”。要求输出学号、姓名、课程名、成绩等级。(提示：在select字句中使用case…when…end语句)

case when的两种用法：

case用于实现简单的等于判断,相当于 switch … case … default。

case 字段名
when ‘字段值’ then ‘需要返回的值’
when ‘字段值’ then ‘需要返回的值’
else ‘剩余所有的需要返回的值’
end

case还可用于有条件的逻辑判断，相当于 if … else if … else。

case
when 字段名 = ‘字段值’ then ‘需要返回的值’
else ‘剩余所有的需要返回的值’
end

本题我们就采用第二种用法，同时在 end 后面加上了 score_grade 相当于给 case when 的结果取的字段名为 score_grade。

select st_info.St_ID,st_info.St_Name,C_Info.C_Name,
	case 
	when s_c_info.score >= 90 then '优秀'
	when s_c_info.score >= 80 then '良好'
	when s_c_info.score >= 70 then '中等'
	when s_c_info.score >= 60 then '及格'
	else '不及格' 
	end score_grade
from st_info,s_c_info,C_Info
where st_info.St_ID=s_c_info.st_id
and C_Info.C_No = s_c_info.c_no;

这里我们使用的SQL92语法：表1，表2 where...

(二)SQL的增删改功能

创建数据库studb，存储属性为默认，在studb数据库中建立数据表。结构如图所示：

创建数据库

#我们先删除数据库，防止由于已经存在 studb 数据库导致我们创建失败
drop database studb;

#创建数据库
create database studb;

使用数据库
```
use studb;
```

依次创建表

#创建学生表
create table S(
   	sno char(8) primary key not null,
   	sname varchar(10) not null,
   	ssex char(2) not null,
   	borndate datetime,
   	clname varchar(20),
   	enscore numeric(4,1),
   	address varchar(50)
);

#创建课程表
create table C(
	cno char(8) primary key not null,
	cname varchar(20) not null,
	cpno char(8),
	ccredit numeric(3,1)
);

#创建学生课程表
create table SC(
	sno char(8) not null,
	cno char(8) not null,
	score numeric(4,1),
	primary key(sno,cno),
	foreign key(sno) references S(sno),
	foreign key(cno) references C(cno)
);

其实在创建表的过程中，我们可以明白一个事情，学生表与课程表之间的关系是多对多。针对这种情况需要设计一个独立的表来表示，这种表一般称为中间表，也就是我们这里创建的学生课程表。

在以上建立的studb数据库中，写SQL语句实现增删改功能。
1️⃣ 在S表中增加如下记录

insert into S(sno,sname,ssex,bomdate,clname,enscore,address)
values('S3','张明华','男','1995-08-21 00:00:00.000','MA_数学',530.0,'浙江杭州');

【建议】程序端insert语句指定具体字段名称，不要写成 INSERT INTO t1 VALUES(…)

建议的写法：INSERT INTO t1(字段1，字段2，…) values(值1，值2，…)

2️⃣ 在C表中将课程名为“数据库”的学分更改为3

update C set ccredit = 3 where cname = '数据库';

2️⃣ 删除S表中S2的学生记录，请问是否能删除，为什么，要如何操作。

✏️ 不能删除，外键依赖于主键而存在，必须要删除所有的相关外键约束，即删除SC表中的sno为S2的所有记录才能删除S表的学生记录。

第一步：delete from SC where sno = ‘S2’;
第二部：delete from S where sno = ‘S2’;

外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会 因为外键约束的系统开销而变得非常慢 。所以， MySQL 允许你不使用系统自带的外键约束，在 应用层面 完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

关于外键约束，阿里规范手册就强制要求不得使用外键与级联，一切外键概念必须从应用层次上解决。

(三)索引

在studb数据库中，分别用对象资源管理器和SQL语言定义索引

索引默认是支持的升序排列。

1️⃣ 在对象资源管理器中，在T表的tname列上中建立聚集索引ix_tname，降序。查看聚集的效果。

CREATE CLUSTERED INDEX ix_tname ON T(tname desc);

2️⃣ 使用SQL语言定义TC表的(tno，cno)列上的复合索引ix_tc，tno列设为升序，cno列设为降序

CREATE CLUSTERED INDEX ix_tc on TC(tno asc,cno desc);

(四)视图

? 在studb数据库中操作。

1️⃣ 在对象资源管理中建立视图v_s_c，列出所有学生所选课程的成绩：学号，姓名，班级名，课程号，课程名，成绩。

create view v_s_c(sno,sname,clname,cno,cname,score)
as 
select S.sno,S.sname,S.clname,C.cno,C.cname,SC.score
from SC,S,C
where SC.sno = S.sno
and SC.cno = C.cno;

2️⃣ 使用SQL语言建立视图v_cjtj，列出每位同学的学号，最高成绩，最低成绩，平均成绩和总成绩，按总成绩降序排列。

这里有个非常值得注意的问题，可能对于这个题你会这样写SQL：

create view v_cjtj(sno,max_score,min_score,avg_score,sum_score)
as
select sno,max(score),min(score),avg(score),sum(score) 
from SC
group by sno
order by sum(score) desc;

❗️ 这时候就会报错：除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

我们再具体解释一下：ORDER BY子句的查询不能用作表的表达式，其中表的表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。select+order by在视图、子查询中的返回值不是表，而且是游标，所以会报错。

那怎样解决这个问题呢？从报错提示中我们其实看出，我们可以使用 TOP 关键字：

指定具体数目

我们可以用具体的数字里进行限量获取，这个和mysql的limit关键字有点像。只不过区别是top放在select后面，而limit放在整个sql语句最后。

我们举个例子实操一下，比如现在要查询学生表前10条学生记录的学号和姓名：
```
select top 10 st_id,st_name
from student;
```
指定具体百分比

如果我们的sql需要获取全部数据来做过滤，又不知道具体的数目，这个时候就可以用百分比来进行获取，其表达形式为top N percent，N为百分比数目，百分比值必须介于 0 到 100 之间，不然会报错。

我们也举一个例子实操一下，比如现在要查询学生表前一半人的信息，但又不知道这一半人到底是多少人，无法使用top的第一个用法，怎么办呢？就可以使用我们top的第二个作用——百分比的方式解决这个问题：
```
select top 50 percent st_id,st_name
from student;
```

我们刚才遇到的SQL报错问题，提到了单独使用order by返回的是游标而不是表，怎么解决呢？就需要使用top的第二个用法，指定N为100，即显示百分百全部数据，返回满足条件的所有记录，经过修正之后，SQL如下：

create view v_cjtj(sno,max_score,min_score,avg_score,sum_score)
as
select top 100 percent 
sno,max(score),min(score),avg(score),sum(score) 
from SC
group by sno
order by sum(score) desc;

在SELECT语句中，应始终将一个ORDER BY子句与该TOP子句一起使用，以指定哪些行受过TOP滤器影响。
⚠️但其实当你执行如下语句：
select * from v_cjtj;
你会发现根本就没有排序，这是为什么呢？
排序无效的原因：创建排序视图的企图本身就是错误的，因为视图表示一个表，而表是不会对行排序的。

四、思考与练习

1.视图和表有何区别？

✏️ 视图是虚拟表，本身不具有和保存数据的，数据真正保存在数据表中，占用很少的内存空间，它是SQL中的一个重要的概念。视图建立在已有表的基础上。视图的本质是select语句，可以将视图理解为存储起来的select语句。

视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

2.视图中的列都能更新吗？

✏️ 不一定能更新：

当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
但也有不能更新的视图，在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、 UNION 等，或使用了 数学表达式 、 子查询 等等情况，视图将不支持INSERT、UPDATE、DELETE。

? 虽然可以更新视图数据，但总的来说，视图作为 虚拟表 ，主要用于 方便查询 ，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

3.查询年龄最大的教师号和年龄，SQL命令如下：请问为什么报错？如何修改？

题目给的错误SQL命令：

Select tno,max(year(getdate( ))-year(tbirday))
From T

✏️ 在SELECT列表中所有未包含在聚合函数中的列都应该包含在 GROUP BY子句中，很显然上述SQL语句tno是没有进行分组的，而max又是聚合函数。

❗️ 我们先说一种错误的修改写法：在from T 后加上 group by tno

Select tno,max(year(getdate( ))-year(tbirday))
From T
group by tno

对 T 表以 tno 作为分组依据，在对每一组分别求该组的最大年龄，但很显然，在这个表中以Tno作为分组依据，每一组肯定只有一条记录，最终的查询结果不就和直接查询 T 表的所有教师年龄记录是一样的吗？能达到这道题查询最大年龄以及对应教师号的目的吗？是不能的。

【正确的修改】

? 我们先查询到 T 表中最大的年龄

Select max(year(getdate( ))-year(tbirday))
From T

? 接着再次查询 T 表，匹配教师年龄中与上述查到的最大年龄相等的记录

select tno,year(getdate( ))-year(tbirday)
from T
where year(getdate())-year(tbirday) = (Select max(year(getdate( ))-year(tbirday))
									   From T);