数据库||数据查询

1.实验题目：数据查询

2.实验目和要求：

1)掌握SQL语句灵活的查询功能

2)熟悉SQL语言的基本查询，包括单表查询，分组统计查询，连接查询

3)掌握SQL的复杂查询，包括嵌套查询，集合查询

4）理解和掌握SQL查询语句各个子句的特点和作用，按照SQL语言规范写出具体的SQL查询语句并调试通过

3.实验步骤：

1. 按实验内容要求完成各项操作
2. 根据题目要求给出解决方案
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、查询效率不同，相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。因此非相关子查询比相关子查询效率高。