实验三    数据库的查询


一、实验目的及要求
1. 熟练掌握SELECT查询语句的使用
2. 掌握无条件、有条件查询及查询结果排序与分组
3. 掌握视图的用法

二、实验任务
1．掌握查询语句的一般格式。
2．掌握无条件、有条件查询及查询结果排序与分组 
3. 掌握视图的用法

三、操作要点
1.进行查询时先进入查询分析器（SQL Server Management Studio）
2.明白了解无条件、有条件查询及查询结果排序与分组
四、注意事项
1.SELECT语句的基本语法格式
2.SELECT语句的执行方法
3.SELECT语句和ORDER BY子句的作用和用法
4.SELECT语句和GROUP BY子句的作用和用法
5.WITH CHECK OPTION的作用
五、实验学时：4学时
六、实验重点及难点
1.SELECT语句的基本语法
2.掌握数据汇总的方法
3.WITH CHECK OPTION对视图使用的影响
七、实验步骤
  
1．启动SQL查询分析器（SQL Server Management Studio）；
2．选择数据库University，验证如下例题：

第一部分：
1 查询全体学生的详细记录。这是一个无条件的选择查询，其命令为：
select * from Students

2查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。这是一个无条件的投影查询，其命令为：select Sname,Sno,Sdept from Students

3 查询选修了课程的学生学号。其命令为：

--3查询选修了课程的学生学号
--因为没有选修表，先修课充当选修课
select distinct Sno as 学号 from Reports          
where Cno in(select Cno from Courses where Pre_Cno is  not  null)

4查询数学系全体学生的学号(Sno)和姓名 (Sname)。其命令为：
select Sno,Sname from Students
where Sdept='数学'

5 查询所有年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。其命令为：select Sname as 姓名,Sage as 年龄 from Students
where Sage<=22 and Sage>=18

6 查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。其命令为：
  select Sname as 姓名,Sage as 年龄 from Students
where Sage>22 or Sage<18

7 查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。其命令为：select Sno as 学号,Sname as 姓名,Ssex as 性别 from Students

where Sdept='自动化' or Sdept='数学' or Sdept='计算机'

8 查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。其命令为：
select Sno as 学号,Sname as 姓名,Ssex as 性别 from Students
where Sname like '刘%'

9 查询课程名为“DB_设计”的课程号(Cno)和学分(Credits)。其命令为：
select Cno as 课程号 , Gredits as 学分 from Courses
where Cname='DB_设计'

10 查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。其命令为：
select * from Courses
where Cname like 'DB\_%设_'ESCAPE'\'

11假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。其命令为：
select  Sno as 学生号,Cno as 课程号 from Reports
where Grade is NULL

12  查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。其命令为：
select * from Students
order by Sdept desc,Sage asc

13 查询学生总人数。其命令为：
select count(Sno) as 学生总人数 from Students

14 查询选修了课程的学生人数。其命令为：
select  count(distinct Sno) as 人数 from Reports          
where Cno in(select Cno from Courses where Pre_Cno is  not  null)

15 计算选修C01号课程的学生平均成绩。其命令为：
select avg(Grade)as C01课程平均成绩 from Reports
where Cno='C01'

16  查询选修C01号课程的学生最高分数。其命令为：
select max (Grade) as C01课程最高分 from Reports
where Cno='C01'

17 计算每门课程的平均成绩、最高分、最低分和总分。其命令为：
select Courses.Cname AS 课程名,avg(Reports.Grade) as 平均成绩,max(Reports.Grade) as 最高成绩,min(Reports.Grade) as 最低成绩
from Reports,Courses
group by Courses.Cname

第二部分：
1．从Students表中建立查询所有男（女）生信息的视图STU_SEX。视图的列名为SNO、SNAME、SSEX和SAGE。
代码:  CREATE VIEW STU_SEX(SNO,SNAME,SSSEX,SAGE)
as Select Sno,Sname,Ssex,Sage from Students

2．从Courses表中建立查询所有课程先修课信息的视图课程Courses_PRE。视图的列名为课程号、课程名称和先修课名称。
代码:CREATE VIEW Courses_PRE(课程号,课程名称,先修课名称) AS
SELECT Cno,Cname,(select Cname from Courses as Courses1 where Courses1.Cno=Courses.Pre_Cno)
 from Courses

3．从Reports表中建立查询成绩大于等于80信息的视图STU_CJ1。视图的列名为学号、课程号和成绩。
代码:CREATE VIEW STU_CJ1 as
select * FROM Reports
where Grade>80 OR Grade=80 

4．从Students、Reports和Courses三个表建立查询学生选修情况的视图STU_CJ2。视图的列名为姓名、课程名称和成绩。
代码:CREATE VIEW STU_CJ2 as
select Students.Sname as 姓名,Courses.Cname as 课程名称,Reports.Grade as 成绩
from Students,Courses,Reports
where Reports.Sno=Students.Sno and Reports.Cno=Courses.Cno 

5．从Students、Reports和Courses三个表建立查询学生选修情况并且成绩小于80的视图STU_CJ3。视图的列名为姓名、课程名称和成绩。
第5题是在第4题基础上修改的
代码:CREATE VIEW STU_CJ3(姓名,课程名称,成绩) as
select * from STU_CJ2
where 成绩<80 

6．利用Transact-SQL命令修改视图STU_SEX。把视图的列名改为学号、姓名、性别
和年龄，加上“WITH CHECK OPTION”选项。
代码: alter view STU_SEX as
select Sno as 学号, Sname as 姓名, Ssex as 性别, Sage as 年龄 from Students 
WITH CHECK OPTION

7．删除视图STU_CJ3。
drop view STU_CJ3

8.建立自动化系学生视图STU_Auto1，带有WITH CHECK OPTION。
Create view STU_Auto1 as
select * from Students where Sdept='自动化'
WITH CHECK OPTION

9.建立自动化系学生视图STU_Auto2。
该视图相比上一个视图不带WITH CHECK OPTION
Create view STU_Auto2 as
select * from Students where Sdept='自动化'

10. 往STU_Auto1添加数据：
①学号为S15、姓名为陈婷、性别为女、年龄为21，自动化系；
insert into STU_Auto1 values('S15','陈婷','女',21,'自动化')


②学号为S17、姓名为周奕、性别为女、年龄为21，计算机系。
插入失败
insert into STU_Auto1 values('S17','周奕','女',21,'计算机系')

11.往STU_Auto2添加数据：
①学号为S18、姓名为陈大敏、性别为男、年龄为21，自动化系；
insert into STU_Auto2 values('S18','陈大敏','男',21,'自动化')

②学号为S19、姓名为马小帅、性别为男、年龄为21，计算机系；
insert into STU_Auto2 values('S19','马小帅','男',21,'计算机系')

注意：比较11题、10题，如遇到问题，分析原因，并解决之。
第11题中相比第10题中2条语句都能插入成功了，主要是因为第10题中第2条语句不满足视图创建时候WITH CHECK OPTION。语句中，要满足谓词也就是子查询中的条件Sdept='自动化'，如果想要解决问题可以修改视图，修改语句是把子查询语句中条件语句where Sdept='自动化'，语句去掉，或者插入时候Sdept，要满足部门名称都是自动化系的学生
修改语句是:alter view STU_Auto1 as
select * from Students
            WITH CHECK OPTION
修改后:

12.在STU_Auto1上，修改陈婷的年龄为20。
update STU_Auto1 set Sage=20  
where Sname='陈婷'

13. 在STU_Auto2上，修改马小帅的年龄为20。
注意：比较13题、12题，如遇到问题，分析原因，并解决之。
update STU_Auto2 set Sage=20  
where Sname='马小帅'

insert into STU_Auto2 values('S19','马小帅','男',21,'计算机')

更新不成功的主要原因是，马小帅这这条实体，虽然插入时候，因为创建视图时候没有后面没有WITH CHECK OPTION语句，不用满足谓词(子查询条件),但是视图创建后，想要进行DML,但是操纵不成功,是因为视图STU_Auto2作为虚表,里面没有马小帅的记录，该虚表从基本表中映射时候，只会映射全体自动化系学生，并不会有计算机系的学生，所以更该失败。

14. 在STU_Auto1上，删除陈婷。

删除成功

15.在STU_Auto2上，删除马小帅。

删除失败
注意：比较15题、14题，如遇到问题，分析原因，并解决之。
删除失败原因上面，13题已经说明，主要因为，视图STU_Auto2表中只会有自动化系学生的全体记录:

如果想解决问题可以在实表Student中修改马小帅的系别，但是这在实际生活中不存在或者说违反逻辑思维的，可以直接在Student表中删除数据，或者修改视图的结构，再进行删除。再就是创建新的视图表，需要把计算机系包含进去，这样就可以删除了