数据库系统--T-SQL数据查询功能-多表查询（超详细/设计/实验/作业/练习）

课程名：数据库系统

内容/作用：设计/实验/作业/练习

学习：T-SQL数据查询功能-多表查询

一、前言

（1）熟悉使用Select…from语句进行单表数据查询；
（2）能将这些更新操作应用于实际操作中去。
（3）掌握Where语句、order by语句、group by语句、having语句的使用方法
（4）理解多表内连接、外连接的使用
（5）理解嵌套查询的执行过程
（6）掌握多表连接查询和嵌套查询

二、环境与设备

1、设备：Windows 10
2、软件：MySQL 8.0和Navicat 12 for MySQL

三、内容

  数据库与表之间的关系：数据库是由各种数据表组成的，数据表是数据库中最重要的对象，用来存储和操作数据的逻辑结构。
  SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，是一个综合的、通用的、功能极强的，同时又简洁易学的语言。

select distinct  输出字段名列表
from 表名列表
where 筛选条件 and 连接条件
group by 分组字段
having 分组后的条件
order by 排序字段 asc|desc
limit	<偏移量（起始行号），行数>

注意：	（1）若输出表中全部字段，则可使用*代替
		（2）查询结果消除重复值distinct
		（3）limit语句中的偏移量（起始行号）默认从0开始
		（4）字段重命名：字段名|表达式   as  新名字
		（5）聚合函数的使用：count-个数、sum-求和、avg-平均值、max-最大值、min-最小值
常用函数：year()-求年份   now()—返回系统日期时间

四、内容

练习题目：

1、 查询学生选课信息，列出学号、姓名、性别、课程号、课程名、学分、成绩
2、 查询选修了1610课程且成绩及格的学生信息，列出学号、姓名、课程号、课程名、成绩
3、 查询选修的课程名中包含“应用”两字的学生选课信息。列出学号、课程号、课程名、成绩
4、 统计每位学生所选课程及格的课程门数，列出学号、姓名、及格课程门数
5、 统计每个学生所获的总学分，列出学号、姓名、总学分，按总学分降序排列
6、 统计每个学生选课信息，列出学号、姓名、选课门数、最高分、最低分、平均分、总分
7、 查询选课门数最多的学生选课信息，列出其姓名，选课门数
8、 分别统计各年份出生的男生人数和女生人数
9、 查询选修了课程的学生信息，列出学生的学号，姓名，课程号，课程名及成绩
10、 查询未选修课程的学生信息，列出学号，姓名
11、 查询未被选修的课程信息，列出课程号，课程名
12、 查询入学成绩高于平均入学成绩的学生信息，列出学号，姓名及入学成绩
13、 查询年龄最大的学生信息。列出学号，姓名及年龄
14、 查询选课成绩最高的学生信息，列出其学号，姓名、课程号及选课成绩。

对应题目答案：

1、 查询学生选课信息，列出学号、姓名、性别、课程号、课程名、学分、成绩

select *
from student,sc,course
where student.sno=sc.sno and sc.cid=course.cid

2、 查询选修了1610课程且成绩及格的学生信息，列出学号、姓名、课程号、课程名、成绩

select sc.sno,sname,sc.cid,cname,scores
from student,sc,course
where student.sno=sc.sno and sc.cid=1610 and scores>=60

3、 查询选修的课程名中包含“应用”两字的学生选课信息。列出学号、课程号、课程名、成绩

select sc.sno,sc.cid,cname,scores
from student,course,sc
where cname like '%应用%' and student.sno=sc.sno and sc.cid=course.cid

4、 统计每位学生所选课程及格的课程门数，列出学号、姓名、及格课程门数

select student.sno,student.sname,count(scores)
from student,sc
where student.sno=sc.sno and sc.scores>=60 
group by sc.sno

5、 统计每个学生所获的总学分，列出学号、姓名、总学分，按总学分降序排列

select sc.sno,student.sname,sum(credit)
from student,sc,course
where student.sno=sc.sno and sc.cid=course.cid
group by sc.sno
order by sum(credit) desc

6、 统计每个
学生选课信息，列出学号、姓名、选课门数、最高分、最低分、平均分、总分

select student.sno,student.sname,count(cid),max(scores),min(scores),avg(scores),sum(scores)
from student,sc
where student.sno=sc.sno 
group by student.sno

7、 查询选课门数最多的学生选课信息，列出其姓名，选课门数

select student.sname,count(*)
from student,sc
where student.sno=sc.sno 
group by student.sname
order by count(*) desc
limit 0,1

8、 分别统计各年份出生的男生人数和女生人数

select year(birthdate) as 年份,sex,count(*) 
from student
group by year(birthdate),sex
order by 1,3 desc

9、查询选修了课程的学生信息，列出学生的学号，姓名，课程号，课程名及成绩

select student.sno,sname,sc.cid,course.cname,scores
from sc,student,course
where student.sno=sc.sno and course.cid=sc.cid

10、 查询未选修课程的学生信息，列出学号，姓名

select sc.sno,sname
from sc right join student on sc.sno=student.sno
where sc.sno is not null
法2：select sno,sname from student where not exists(
select * from sc where sno=student.sno)

11、 查询未被选修的课程信息，列出课程号，课程名

select 
from
where

12、 查询入学成绩高于平均入学成绩的学生信息，列出学号，姓名及入学成绩

select 
from
where

13、 查询年龄最大的学生信息。列出学号，姓名及年龄

select *,year(now())-year(birthdate)
from student
order by 3 desc
limit 0,2

法2:

select sno,sname,year(now())-year(birthdate)
from student 
where year(now())-year(birthdate)=(
select max(year(now())-year(birthdate))
from student)

14、 查询选课成绩最高的学生信息，列出其学号，姓名、课程号及选课成绩。

select *
from sc
where scores>=90
union
select *
from sc
where scores<60

create view stuino as
select sno,sname from student

五、总结

好好学习，天天向上。