1.实验题目：数据查询
2.实验目和要求：
1)掌握SQL语句灵活的查询功能
2)熟悉SQL语言的基本查询，包括单表查询，分组统计查询，连接查询
3)掌握SQL的复杂查询，包括嵌套查询，集合查询
4）理解和掌握SQL查询语句各个子句的特点和作用，按照SQL语言规范写出具体的SQL查询语句并调试通过
3.实验步骤：
- 按实验内容要求完成各项操作
- 根据题目要求给出解决方案
- 提交实验报告
4.实验内容：
--表的建立 Create table Student (Sno char(9) PRIMARY KEY, Sname char(20) UNIQUE, Sex char(2), Age SMALLINT, Dept char(20) ); Create table Course (Cno char(4) PRIMARY KEY, Cname char(40) NOT NULL, Cpno char(4), Ccredit SMALLINT, FOREIGN KEY(Cpno) REFERENCES Course(Cno) ); Create table SC (Sno char(9), Cno char(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY (Sno)REFERENCES Student(Sno), FOREIGN KEY (Cno)REFERENCES Course(Cno) ); --表中插入数据 Insert Into Student Values('42101','赵君君','男',18,'CS'), ('42102','赵小花','女',17,'MA'), ('42103','赵小明','男',17,'MA'), ('42104','赵宇宇','男',18,'CS'), ('42105','赵兰兰','女',17,'IS'), ('42106','钱小君','男',18,'CS'), ('42107','钱大花','女',17,'MA'), ('42108','钱君','男',18,'CS'), ('42109','钱花','女',17,'MA'), ('42110','钱明','男',17,'MA'), ('42111','孙宇','男',18,'CS'), ('42112','孙兰','女',17,'IS'), ('42113','江君','男',18,'CS'), ('42114','刘花','女',17,'MA'); Insert Into Course Values('1','CS','2',4), ('2','MA',null,2), ('3','IS','1',7); Insert Into SC Values('42101','1',35), ('42102','2',99), ('42103','2',45), ('42104','1',95), ('42105','3',78), ('42106','1',75), ('42107','2',99), ('42108','1',43), ('42109','2',95), ('42110','2',73), ('42111','1',35), ('42112','3',59), ('42113','1',35), ('42114','2',96); Select * from Student; Select * from Course; Select * from SC;
1)查询学生的学号和所在系
Select Sno,Dept from Student;
2）查询管理系的学生所选修的课程和成绩
Select Student.Sno,Cpno,Grade from Student,SC,Course where Dept = 'MA'and Student.Sno = SC.Sno and SC.Cno = Course.Cno;
3）统计有学生选修的课程门数。
Select count(*)
from SC
Group by Cno;
4）统计胡老师所授每门课程的学生平均成绩。
Alter table Course add Cteacher char(40)
Update Course set Cteacher = '胡婧' where Cno='1';
Update Course set Cteacher = '王幸' where Cno='2';
Update Course set Cteacher = '李妍' where Cno='3';
select * from Course;
Select Cno,avg(Grade)
From SC
Where Cno in
(
Select Cno
From Course
Where Cteacher like '胡%'
)
group by Cno;
5）统计所有选修人数多于20的课程号和选课人数，并按人数降序排列，若人数相等，则按课程号升序排列。
Select Cno,count (Sno)
From SC
Group by Cno
having count (Sno)>'20'
order by count (Sno) desc,cno asc;
6）检索所有缓考（成绩为NULL）的同学学号、姓名和缓考课程号。
Select Student .Sno,Sname,Cno
From Student,SC
Where grade is NULL and Student.Sno = SC.Sno;
7）检索‘OS’课成绩高于该课平均成绩的同学学号。
Update Course set Cname = 'OS' where Cno = '3';
select * from Course;
Select Sno
From SC,Course
Where Cname = 'OS' and Course.Cno = SC.Cno and
grade >(select avg (grade)
From SC,Course
Where Cname = 'OS' and Course.Cno = SC.Cno
);
8）检索计算机系女生的学号和姓名。
Select Sno,Sname
From Student
Where Dept = 'CS' and Sex = '女';
9）检索全体学生都选修的课程号和课程名。
Select Cno,Cname
From Course
Where not exists
(Select *
From Student
Where not exists
(Select *
From SC
Where Student.Sno = SC.Sno and SC.Cno = Course.Cno)
);
10）检索未选修任何课程的学生学号。
Select Sno
From Student
Where Student.Sno not in
(Select distinct SC.Sno
From SC
11）检索王老师所授课程号、课程名。
Select Cno,Cname
From Course
Where Cteacher like '王%';
12）检索所有姓李同学的基本信息。
Select *
From Student
Where Sname like '李%';
13）检索选修‘数据库系统’课程的学生学号。
Select Sno
From SC,Course
Where Cname = '数据库系统' and SC.Cno = Course.Cno ;
14）检索年龄介于李萍同学年龄和28岁之间的学生基本信息。
Select *
From Student
Where Age between (
Select Age
From Student
where sname = '李萍') and 28;
15）检索至少选修了一门田老师所授课程的学生姓名、学号。
Select distinct Sname,Student.Sno
From Student,Course,SC
Where Cteacher = '田%' and Student.Sno = SC.Cno and Course.Cno = SC.Cno;
5.实验总结
此次实验主要运用了各类数据查询语句，除了常规的建表、查表之外，还涉及了update、AVG、like、not in、group by 、order by 、desc、asc、between等语句，并且对此用到多表查询的连接，涉及到两个及两个以上的表：Student.Sno = SC.Sno and Course.Cno = SC.Cno .通过这次训练，我发现对于各种SQL语句并不是非常熟练，如第四题插入教师数据时，习惯性的用insert 的语句，其实并不能用insert语句，因为alter语句相当于加入了一列空表，所以此时无法继续插入信息，而应该用update语句，及时更新数据。
6.思考
1)举例说明在分组统计中，WHERE和HAVING有何区别
答：where是过滤数据行的，having是过滤数据分组的。
where的查询的条件是不可以用聚合函数的（比如SUM(),MAX(),AVG(),COUNT(),MIN()）having是可以的。
where是在分组之前进行过滤的，having实在分组之后进行过滤的。
where是针对数据库文件过滤，having是针对查询结果过滤。
where是针对数据表中的字段直接过滤，having是针对已经查出的字段进行过滤。
where查询不可以使用字段别名，having是可以的。
查询一个name中包含‘a’的用户下有权限的菜单数量大于30个数量
SELECT
su.'name',
count(DISTINCT srm.menu_id )
FROM
sys_user su
INNER JOIN sys_user_role sur ON su.id = sur.user_id
INNER JOIN sys_role_menu srm ON sur.role_id = srm.role_id
INNER JOIN sys_menu sm ON srm.menu_id = sm.id
where su.name like '%a%'
GROUP BY
su.`name`
HAVING
count(DISTINCT srm.menu_id ) > 30;
2）请分析相干子查询和不相干子查询的区别
答：1、查询次数不同，非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。
2、查询效率不同，相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。因此非相关子查询比相关子查询效率高。
