第七单元 条件查询，分组查询

1. 条件查询

条件查询是通过where子句进行检索的查询方式。

select 字段名1, 字段名2, …, 字段名n 
from 数据表名 
where 查询条件

 

1. 如何使用排序（升序，降序）？

asc:表示升序（默认排序方式） desc:降序

排序语法：select ... from 表名 [条件] order by 要排序的字段 asc/desc

-- 将成绩从高到低进行排序
select * from StudentScore order by (theoryScore+skillScore) 
​
​
-- 查询学生表，按姓名升序显示
select * from StudentInfo order by stuName asc -- asc 可以省略

 

 

2. 多字段如何排序？

语法: select ... from 表名 [条件] order by 第一要排序的字段 asc/desc ， 第二要排序的字段 asc/desc

 -- 查询成绩表，先按学生编号从低到高排序，再按成绩从高到低显示
 select * from StudentScore order by stuId asc,(theoryScore+skillScore) desc


 -- 查询学生信息，先按班级进行升序，再按生日进行降序
 select * from StudentInfo order by ClassId asc ,stuBirthday desc

 

 

3. 如何使用关系表达式查询（大于，小于，等于）？

 -- 查询 班级编号 在 1,3,5,7 里的学生信息
 select * from StudentInfo where ClassId in(1,3,5,7)


-- 查询班级编号 不是偶数的学生信息
select * from StudentInfo where ClassId%2!=0

-- 查询技能成绩大于90分的成绩信息
select * from StudentScore where skillScore>90

 

 

4. 如何使用between...and 的语法？

between ... and :在...之间，应用范围：数字类型

-- 查询技能成绩在60-80之间的成绩信息
-- 写法1
select * from StudentScore where  skillScore between 60 and 80
-- 写法2
select * from StudentScore where  skillScore>=60 and skillScore<=80

 

 

5. 如何统计个数，平均分，最大数，最小数，求和？

函数	字段类型	描述
Min(字段)	数字类型	最小值
Max(字段)	数字类型	最大值
Sum(字段)	数字类型	总和
count(字段)	无限制	个数
count(distinct 字段)	无限制	去重后，求个数
avg(字段)	数字类型	平均数

-- 统计学生的个数
 select count(stuId) from StudentInfo

 -- 统计女生的个数
 select count(stuId) as 女生个数 from StudentInfo where stuSex='女'



 -- 查询学生平均分
 select avg(skillScore+theoryScore) from StudentScore

 -- 查询课程名称为'C# 入门编程'的平均分
  select avg(skillScore+theoryScore) from StudentScore where CourseName='C# 入门编程'

 -- 查询班级编号最大的班级
 -- 写法1
 select max(classId) from StudentInfo
 -- 写法2
select top 1 (skillScore+theoryScore) from StudentScore order by (skillScore+theoryScore) desc
 
 -- 计算最低分
 -- 写法1
select min(skillScore+theoryScore) from StudentScore
-- 写法2
select top 1 (skillScore+theoryScore) from StudentScore order by (skillScore+theoryScore) asc



 -- 计算技能总分
 select sum(skillScore) from StudentScore

-- 计算学生编号为180325011 的技能总分
select sum(skillScore) from StudentScore where stuId='180325011'


-- 查询有多少个人参加了考试
select count(distinct stuId) from StudentScore
 

 

2. 聚合函数

6. 能否将所有聚合函数写在一起？

select 
count(distinct stuId) 学生数量,
Min(skillScore) 最小值,
Max(skillScore) 最大值,
Sum(skillScore) 总和
from StudentScore

 

3. exists 查询

功能：用于嵌套查询 exists后的查询语句有结果则为真，无结果则为假,如果为真，则执行外层查询，否则外层查询不执行 语法格式：

select 字段名1, 字段名2, …, 字段名n 
from 数据表名 
where exists 
（select 字段名 from 数据表名 where …） 

-- 如果存在大于20岁,则查询结果,
select * from StudentInfo where exists
(
    select * from StudentInfo where year(getdate())-year(stuBirthday)>20
)

 
--如果班级里面有两个以上的老王,则把老王的信息查询出来
select * from StudentInfo where exists
(
    select * from
    (
        select count(stuId) as 数量 from StudentInfo where stuName like '王%'
    ) a where a.数量>=1
)
 

 

4. 分组查询

8.如何使用分组查询?他们的关键字分别是什么？

-语法：select 要分组的字段，聚合函数 from 表名 group by 要分组的字段

-group by:分组

小技巧： 每什么就以哪个字段进行分组

--9.计算每门课程的平均分
select CourseName, avg(skillScore) as 平均分 from StudentScore group by CourseName


--10.统计每个学生的平均分
select stuId,avg(skillScore+theoryScore) from StudentScore group by stuId

-- 统计每个班级有多少个学生
select ClassId,Count(stuId) as 个数 from StudentInfo group by ClassId

 
-- 统计每门课程有多少位同学在学习
select CourseName,count(stuId) from StudentScore group by CourseName


-- 统计每个学生学习了多少门课程
select stuId,count(CourseName) from StudentScore group by stuId


--11.查看每一门课程的平均分,总分,最高分,最低分
select CourseName,avg(skillScore),sum(skillScore),max(skillScore),min(skillScore)
from StudentScore group by CourseName

-- 11.1 查询每门课程，每个学生的最低分
select CourseName,stuId,min(skillScore) from StudentScore group by CourseName,stuId

--12.统计每门课程的最低分,并且查询出70分以上的
-- having:在分组的基础之上进行数据过滤
select CourseName,min(skillScore) from StudentScore group by CourseName
having min(skillScore)>70


--13.统计每门课程,但不包含课程C# 入门编程的最低分,并且查询出70分以上的

--  13.1统计每门课程最低分
select CourseName,min(skillScore) from StudentScore group by CourseName
--  13.2 在统计之前加上where 课程!=C# 入门编程
select CourseName,min(skillScore) from StudentScore where CourseName!='C# 入门编程' 
group by CourseName 
--  13.3 在统计之后加上having 最低分>=70
select CourseName,min(skillScore) from StudentScore where CourseName!='C# 入门编程' 
group by CourseName  having min(skillScore)>70

--14.查询每门课程学习的人数大于等于2人的课程名称与学习人数。
select CourseName,count(stuId) from StudentScore group by CourseName
having count(stuId)>=2

-- 15.查询不只学了一门课程的学生编号与学习的课程数
-- 翻译成人话：每个学生学习的课程数，并且学习的数量大于1
select stuId,count(CourseName) from StudentScore group by stuId
having count(CourseName)>1

 

5. 作业

在线教育平台，网站首页显示所有课程分类，将分类后的信息显示出来供用户选择，创建数据库Education_DB，创建表（根据说明创建符合规范的表），如图所示

课程表：

列名	数据类型	说明
CourseId	int	课程编号，自增1,主键
CourseName	char(20)	课程名称，非空

 

用户选课表

列名	数据类型	说明
Customer_Id	int	用户编号、自增1,主键
Customer_Name	Varchar(20)	用户名称、非空
Customer_Sex	Varchar(2)	用户性别、只能是男或女
Customer_Age	int	用户年龄、年龄>=0
Customer_Phone	Varchar(20)	用户手机号码、唯一
Customer_Address	Varchar(20)	用户籍贯、非空、默认北京
CourseId	int	课程编号、外键

 

1、添加数据，如图所示，用户选课表至少添加五条数据

 

一、案例题目

1. 创建两张表并添加约束。（15分）

2. 每张表添加5条数据（5分）

3. 查询用户选课信息表的前3条数据（5分）

4. 查询用户选课信息表中年龄大于20的用户信息（5分）

5. 查询用户选课信息表中性别为女的信息（5分）

6. 查询用户选课信息表中用户名称、用户性别和用户年龄并为查询的字段起中文别名（5分）

7. 查询用户选课信息表的信息并按照年龄升序排列（5分）

8. 查询用户选课信息表中所有用户的年龄总和（5分）

9. 查询用户选课信息表中所有用户的最小年龄（5分）

10. 查询用户选课信息表中所有用户的最大年龄（5分）

11. 查询用户选课信息表中所有用户的平均年龄（5分）

12. 查询用户选课信息表中一共有多少条数据（5分）

13. 查询姓李的用户信息（5分）

14. 查询姓张的两个字的用户信息（5分）

15. 查询每个课程有多少人选择（5分）

16. 查询大于平均年龄的用户信息（5分）

17. 查询大于平均年龄的前2条信息（5分）

18. 查询学习C#课程的人数（5分） 

 

配套视频链接：SQLServer 入门基础 - 网易云课堂 (163.com)