实验三-----数据库

一、实验目的

1.掌握SQL Server Management Studio中SQL 查询操作；
2.掌握SQL 的单表查询命令；
3.掌握SQL 的连接查询操作；
4.掌握SQL 的嵌套查询操作；
5.掌握SQL 的集合查询操作。

二、实验环境

1．实验室名称：软件实验室
2．主要仪器设备：PC机、SQL Server2008环境

三、实验内容

1. 对学生-课程MyDb_学号数据库，应用SQL语句实现以下查询要求：

1. 查询数学系学生的学号和姓名；
2. 查询选修了课程的学生学号；
3. 查询选修了1号课程的学生学号和成绩，并要求结果按成绩降序排列，如果成绩相同，则按学号升序排列；
4. 查询选修了1号课程且成绩在80-90分之间的学生学号和成绩，并将成绩乘以系数0.8输出；
5. 查询数学系或计算机系姓张的学生的信息；
6. 查询缺少了成绩的学生的学号和课程号；
7. 查询每个学生的情况以及他（她）所选的课程；
8. 查询学生的学号、姓名、选修的课程名称及成绩；
9. 查询选修了“数据库”课程且成绩在90分以上的学生学号、姓名和成绩；
10. 查询每门课程的间接先行课的课程名称。

2．对学生-课程MyDb_学号数据库，应用嵌套查询实现以下查询要求：

1） 查询选修了“高等数学”的学生学号和姓名；
2） 查询“高等数学”的成绩高于张三的学生学号和成绩；
3） 查询其他系中年龄小于计算机系年龄最大者的学生；
4） 查询其他系中比计算机系学生年龄都小的学生；
5） 查询选修了“信息系统”课程的学生姓名；
6） 查询没有选修“信息系统”课程的学生姓名；
7） 查询选修了全部课程的学生姓名；
8） 查询至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。

3．对图书读者数据库，应用SQL语句实现以下查询要求：

1） 查询计算机类和机械工业出版社出版的图书；
2） 查询“机械工业出版社”出版的各类图书的平均定价，用GROUP BY 表示；
3） 查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍；
4） 列出计算机类图书的书号、名称、价格及数量，最后给出总册数和总价格；
5） 列出各出版社计算机类图书的具体书号、名称、价格和销售数量，并求出各出版社这类书的平均价格和销售总量。
6） 列出各出版社图书的具体书号、名称、价格和销售数量，并求出各出版社这类书的平均价格和销售总量。

四、实验调试分析

1. having必须和group by一起用，且在group by后面
2. group by、having、order by的使用顺序：group by 、having、order by

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING
子句][ORDER BY 子句][LIMIT 子句]

3. where/having区别
   Where

1. 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
2. where 后面不可以使用聚合函数
3. 过滤行
   Having
4. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
5. having 后面可以使用聚合函数
6. 过滤组
7. 支持所有WHERE操作符

五、实验结果

1.

（1）

（2）

（3）

（4）

（5）

（6）

（7）

（8）

（9）

（10）

2.

（1）

（2）

（3）

（4）

（5）

（6）

（7）

（8）

3.

（1）

（2）

（3）

（4）

（5）
暂时不会
（6）
暂时不会

六、实验源代码

1.

/*
	查询1
*/
select * from Student;
select * from Course;
select * from Sc;
--1.查询数学系学生的学号和姓名
select Sno,Sname from Student where Sdept='数学'; 
--2.查询选修了课程的学生学号
select Sno from SC;
--3.查询选修了1号课程的学生学号和成绩，并要求结果按成绩降序排列，如果成绩相同，则按学号升序排列
select Sno,Grade from SC where Cno = 1 order by Grade desc, Sno;
--4.查询选修了1号课程且成绩在80-90分之间的学生学号和成绩，并将成绩乘以系数0.8输出
select Sno,Grade*0.8 '成绩乘0.8' from SC where Cno = 01 and Grade between 80 and 90;
--5.查询数学系或计算机系姓张的学生的信息
select * from Student where Sdept = 'MA' or Sname LIKE '张%';
--6.查询缺少了成绩的学生的学号和课程号
select Sno , Cno from SC where Grade is null;
--7.查询每个学生的情况以及他（她）所选的课程
select Student.*,Cname from Student,SC,Course where Student.Sno = SC.Sno and SC.Cno = Course.Cno;
select Student.Sno,Sname,Sage,Sdept,Course.Cno,Grade,Cname from Student left outer join SC on(Student.Sno = SC.Sno) join Course on (SC.Cno = Course .Cno);
--8.查询学生的学号、姓名、选修的课程名称及成绩
select Student.Sno,Sname,Grade,Cname from Student,SC,Course where Student.Sno = SC.Sno and SC.Cno = Course.Cno;
select Student.Sno,Sname,Grade,Cname from Student left outer join SC on(Student.Sno = SC.Sno) join Course on (SC.Cno = Course .Cno);
--9.查询选修了“数据库”课程且成绩在90分以上的学生学号、姓名和成绩
select  Student.Sno,Sname,Cname,Grade from Student left outer join SC on(Student.Sno = SC.Sno)
	join Course on (SC.Cno = Course .Cno) where Cname = '数据库' and Grade >=90;
--10.查询每门课程的间接先行课的课程名称
select first.Cno,second.Cpno from Course first,Course second where first.Cpno = second.Cno;

2.

/*
	查询2
*/
select * from Student;
select * from Course;
select * from Sc;
--1.查询选修了“高等数学”的学生学号和姓名；
select Student.Sno,Sname from Student left outer join SC on(Student.Sno = SC.Sno)
	join Course on (SC.Cno = Course .Cno) where Cname = '高等数学';
--2.查询“高等数学”的成绩高于张三的学生学号和成绩；
select Student.Sno,Grade from Student left outer join SC on(Student.Sno = SC.Sno)
	join Course on (SC.Cno = Course .Cno) where Course.Cname = '高等数学' and SC.Grade >
	(select Grade from SC where Cno = 2 and  Sno = (select Sno from Student where Sname = '张三'));
--3.查询其他系中年龄小于计算机系年龄最大者的学生；
select Student.* from Student where Sdept != '计算机系' and  Sage<
	(select MAX(Sage) from Student where Sdept = '计算机系');
--4.查询其他系中比计算机系学生年龄都小的学生；
select Student.* from Student where Sdept != '计算机系' and  Sage<
	ANY(select MIN(Sage) from Student where Sdept = '计算机系');
--5.查询选修了“信息系统”课程的学生姓名；
select Sname from Course,SC,Student where Student.Sno = SC.Sno and SC.Cno = Course.Cno and Cname = '信息系统';
--6.查询没有选修“信息系统”课程的学生姓名；
select Sname from Student where not exists 
	(select * from SC where Sno =Student.Sno and Cno = (select Cno from Course where Cname='信息系统'));
--7.查询选修了全部课程的学生姓名；
select Sname from Student where not exists(select * from Course where not exists
	(select * from SC where Sno = Student.Sno and Cno = Course.Cno));
--8.查询至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。
select distinct Sno from SC SCX where 
	not exists(select * from SC SCY where SCY.Sno = '95002' and not exists(select *  from SC SCZ where SCZ.Sno = SCX.Sno and SCZ.Cno = SCY.Cno));

3.

/*
	查询3
*/
select * from 图书;
select * from 读者;
select * from 借阅;
--1.查询计算机类和机械工业出版社出版的图书；
select 书名 from 图书 where 类型 = '计算机类' and 出版社 = '机械工业出版社';
--2.查询“机械工业出版社”出版的各类图书的平均定价，用GROUP BY 表示；
select 类型,avg(定价) '图书平均定价' from 图书 where 出版社 = '机械工业出版社' group by 类型;
--3.查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍；
select 类型,MAX(定价) 最高图书定价,AVG(定价) 图书平均定价 from 图书 group by 类型 having MAX(定价)>= all(
	select AVG(定价)*2 价格 from 图书 group by 类型);
--4.列出计算机类图书的书号、名称、价格及数量，最后给出总册数和总价格； 
select 书号,数量,sum(数量),sum(定价)from 图书 where 类型='计算机类' group by 书号,数量; 
--5.列出各出版社计算机类图书的具体书号、名称、价格和销售数量，并求出各出版社这类书的平均价格和销售总量。
select 书号,书名 名称,定价 价格,数量 销售数量,avg(定价),sum(数量) from 图书  compute count(书号),sum(价格);
--6.列出各出版社图书的具体书号、名称、价格和销售数量，并求出各出版社这类书的平均价格和销售总量
select 书号,书名,定价,数量,销售数量,avg(价格)  from 图书 ;