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

2023-11-24 23:33 由誉尚学教育发表于 #数据库

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、添加数据，如图所示，用户选课表至少添加五条数据

一、案例题目

创建两张表并添加约束。（15分）
每张表添加5条数据（5分）
查询用户选课信息表的前3条数据（5分）
查询用户选课信息表中年龄大于20的用户信息（5分）
查询用户选课信息表中性别为女的信息（5分）
查询用户选课信息表中用户名称、用户性别和用户年龄并为查询的字段起中文别名（5分）
查询用户选课信息表的信息并按照年龄升序排列（5分）
查询用户选课信息表中所有用户的年龄总和（5分）
查询用户选课信息表中所有用户的最小年龄（5分）
查询用户选课信息表中所有用户的最大年龄（5分）
查询用户选课信息表中所有用户的平均年龄（5分）
查询用户选课信息表中一共有多少条数据（5分）
查询姓李的用户信息（5分）
查询姓张的两个字的用户信息（5分）
查询每个课程有多少人选择（5分）
查询大于平均年龄的用户信息（5分）
查询大于平均年龄的前2条信息（5分）
查询学习C#课程的人数（5分）

配套视频链接：【阶段二】 - SQLServer 基础(超级详细，口碑爆盆)_哔哩哔哩_bilibili