数据库笔记02：查询与统计数据
【摘要】
/***************************
第二单元：查询与统计数据
***************************/
/*
SELECT [DISTINCT][TOP n [PERCENT]] *|字段列表|表达式列表|函数列表
FROM 数据表(单表...
/***************************
第二单元：查询与统计数据
***************************/
/*
SELECT [DISTINCT][TOP n [PERCENT]] *|字段列表|表达式列表|函数列表
FROM 数据表(单表或多表)
WHERE 查询条件
ORDER BY 字段1 DESC,....,字段n DESC
难点在于正确书写查询条件。
简单条件，用到关系运算符：=、!=、>、<、>=、<=
针对字符型数据，有一个用户模糊查询的运算符：LIKE（%、_）
复杂条件，用到逻辑运算符来连接关系表达式：NOT-->AND-->OR
NOT: Complement 补集
AND: Intersection 交集 ∩
OR: Union 并集
有两个特殊的运算符：BETWEEN...AND...、IN
*/
/* 任务：查找Class表里系部号为01的记录。*/
SELECT *
FROM Class
WHERE DepartNo = '01' ;
/* 任务：查找Student表里所有姓张的学生记录。*/
SELECT *
FROM Student
WHERE StuName LIKE '张%' ;
/* 任务：查找Student表里姓张单名的学生记录。*/
SELECT *
FROM Student
WHERE StuName LIKE '张_' ;
/* 任务：查找Student表里ClassNo为20000001所有姓张的学生记录。*/
SELECT *
FROM Student
WHERE StuName LIKE '张%' AND ClassNo = '20000001' ;
/* 任务：查找Student表里ClassNo为20000004所有姓张和姓李的学生记录。*/
错误的处理方式：
正确的处理方式：
SELECT *
FROM Student
WHERE ( StuName LIKE '李%' OR StuName LIKE '张%' ) AND ClassNo = '20000004' ;
/* 练习：查询Course表里课程编号不为004,005,007的课程信息，包含课程编号与课程名字段 */
SELECT CouNo 课程编号 , CouName 课程名
FROM Course
WHERE CouNo != '004' AND CouNo != '005' AND CouNo != '007' ;
SELECT CouNo 课程编号 , CouName 课程名
FROM Course
WHERE NOT ( CouNo = '004' OR CouNo = '005' OR CouNo = '007' );
SELECT CouNo 课程编号 , CouName 课程名
FROM Course
WHERE CouNo NOT IN ( '004' , '005' , '007' );
还有没有别的处理方式？
课堂练习：查询课程编号在004与007之间的课程，包含课程编号和课程名称，字段名显示为中文。
交换004和007的位置，看看结果怎样？
结论：BETWEEN min_value AND max_value
/**********************************
统计查询，要用到五个聚合函数：
SUM()、AVG()、COUNT()、MAX()、MIN()
聚合函数的用法请大家参看教材P48
***********************************/
关于统计，有几种情况：
1、总体统计
2、总体条件统计（WHERE）
3、分组统计（GROUP）
4、分组统计有总体条件（WHERE-GROUP）
5、分组条件统计（GROUP-HAVING）
6、总体条件与分组条件统计（WHERE-GROUP-HAVING）
/* 任务：统计Course表中有多少门选修课 */
SELECT COUNT (*) 课程门数
FROM Course ;
课堂练习：统计课程编号大于007的课程门数。
/* 任务：统计Student表里班级编号为20000004的学生人数 */
SELECT COUNT (*) 学生人数
FROM Student
WHERE ClassNo = '20000004' ;
/* 任务：统计每个班级的学生人数。*/
/* 分析：要按班级编号来进行分组汇总，就要用到GROUP BY字句来实现 */
SELECT ClassNo 班级编号 , COUNT (*) 学生人数
FROM Student
GROUP BY ClassNo ;
/* 任务：统计全部课程的限选人数之和 */
SELECT SUM ( LimitNum ) 限选人数总和
FROM Course ;
/* 任务：统计信息技术类课程的限选人数之和 */
SELECT SUM ( LimitNum ) 限选人数总和
FROM Course
WHERE Kind = '信息技术' ;
/* 任务：查看所有类别课程的限选人数之和 */
SELECT Kind 课程类别 , SUM ( LimitNum ) 限选人数总和
FROM Course
GROUP BY Kind ;
/* 任务：查询Course表，了解各门课程受欢迎的程度。*/
应该选择WillNum而不是ChooseNum作为统计字段，要理解为什么。
SELECT MAX ( WillNum ) 最大报名人数 , MIN ( WillNum ) 最小报名人数 , AVG ( WillNum ) 平均报名人数
FROM Course ;
/* 问题：如何才能让平均报名人数显示为整数？ */
SELECT MAX ( WillNum ) 最大报名人数 , MIN ( WillNum ) 最小报名人数 , CAST ( AVG ( WillNum ) AS INT ) 平均报名人数
FROM Course
/* 任务：查询最受欢迎的课程名称以及报名人数 */
/* 分析：筛选报名人数等于最大报名人数的课程，而最大报名人数是前一个查询的结果，
一个查询的结果作为另一个查询条件的组成部分，这就是嵌套查询 */
SELECT CouName 课程名称 , WillNum 报名人数
FROM Course
WHERE WillNum =( SELECT MAX ( WillNum ) FROM Course ); /* 子查询 */
/* 任务：查询最受不欢迎的课程名称以及报名人数 */
SELECT CouName 课程名称 , WillNum 报名人数
FROM Course
WHERE WillNum =( SELECT MIN ( WillNum ) FROM Course ); /* 子查询 */
/* 任务：查询最受欢迎和最不受欢迎的课程名称及报名人数 */
/* 方法一 */
SELECT CouName 课程名称 , WillNum 报名人数
FROM Course
WHERE WillNum =( SELECT MAX ( WillNum ) FROM Course )
OR WillNum =( SELECT MIN ( WillNum ) FROM Course );
/* 方法二 */
DECLARE @ Max_Num AS INT , @ Min_Num AS INT
SELECT @ Max_Num = MAX ( WillNum ), @ Min_Num = MIN ( WillNum )
FROM Course
SELECT CouName 课程名称 , WillNum 报名人数
FROM Course
WHERE WillNum IN ( @ Max_Num , @ Min_Num )
GO
/***********************************************
分组查询：GROUP BY, ALL, CUBE, COMPUTE, COMPUTE BY, HAVING
具体用法，请大家参看教材P50
************************************************/
/* 任务：按课程类别分组统计课程门数，并给出总门数。*/
SELECT Kind 课程类别,COUNT(*) 课程门数
FROM Course
GROUP BY Kind WITH CUBE ;
/* 任务：统计报名人数超过15人的各类课程的最多报名人数与最少报名人数。 */
/* 总体筛选条件，针对所有记录的，通过WHERE字句来实现 */
SELECT Kind 课程类别 , MAX ( WillNum ) 最多报名人数 , MIN ( WillNum ) 最少报名人数
FROM Course
WHERE WillNum > 15 /* 总体筛选条件 */
GROUP BY Kind ;
/* 任务：查询报名人数超过15人并且平均报名人数超过30人的课程类别与平均报名人数 */
/*
总体筛选条件：报名人数超过15人，通过WHERE字句来实现
组内筛选条件：平均报名人数超过30人，通过HAVING字句来实现
*/
SELECT Kind 课程类别 , CAST ( AVG ( WillNum ) AS INT ) 平均报名人数
FROM Course
WHERE WillNum > 15 /* 总体筛选条件 */
GROUP BY Kind
HAVING AVG ( WillNum )> 30 ; /* 组内筛选条件 */
/* 任务：查询课程类别为“信息技术”与“管理”的平均报名人数，显示课程类别 */
SELECT Kind 课程类别 , CAST ( AVG ( WillNum ) AS INT ) 平均报名人数
FROM Course
WHERE Kind IN ( '信息技术' , '管理' )
GROUP BY Kind ;
SELECT Kind 课程类别 , CAST ( AVG ( WillNum ) AS INT ) 平均报名人数
FROM Course
WHERE Kind IN ( '信息技术' , '管理' )
GROUP BY ALL Kind ;
/* 任务：查看Course表中系部编号为02的课程信息，显示该系部的平均报名人数以及总数 */
SELECT *
FROM Course
WHERE DepartNo = '02'
COMPUTE AVG ( WillNum ), SUM ( WillNum );
/* 任务：按系部分组查看课程信息，并显示每个系部的平均报名人数及总数 */
SELECT *
FROM Course
ORDER BY DepartNo /* 一定要按分组计算的字段排序 */
COMPUTE AVG ( WillNum ), SUM ( WillNum ) BY DepartNo ;
说明：在SQL Server 2012中，COMPUTE已被废弃。
/************************************************************
子查询（嵌套查询）：一个查询的结果作为另外一个查询条件的组成部分
查询条件可以使用关系运算（六种）、BETWEEN...AND...、IN、EXISTS
专门讲一下EXISTS函数。EXISTS(子查询)，如果子查询结果不为空，返回真，否则返回假
****************************************************************/
/* 任务：查询报名人数超过平均报名人数的课程信息 */
SELECT *
FROM Course
WHERE WillNum >( SELECT AVG ( WillNum ) FROM Course );
/* 任务：显示平均报名人数大于30的系部开设的课程信息 */
SELECT *
FROM Course
WHERE DepartNo IN ( SELECT DepartNo
FROM Course
GROUP BY DepartNo
HAVING AVG ( WillNum )> 30 );
/* 任务：查询已经报名选修课程的学生信息，只显示学号与姓名 */
SELECT StuNo 学号 , StuName 姓名
FROM Student
WHERE EXISTS ( SELECT StuNo
FROM StuCou
WHERE StuNo = Student . StuNo AND State = '报名' );
大家思考一下，可不可以用关联查询来完成？
关联查询有两种实现方式：（1）利用where子句实现关联；（2）利用join关键字实现关联
看有重复记录，需要采用DISTINCT关键字来去重。
/*********************************
利用UNION关键字联合查询结果
——相当于集合的并集运算
**********************************/
/*任务：将系部表(DepartNo, DepartName)与班级表(ClassN0, ClassName)的内容联合起来显示 */
SELECT DepartNo , DepartName
FROM Department
UNION
SELECT ClassNo , ClassName
FROM Class
/************************************
关联查询：基于多张表的查询
有两种实现关联的方式：
1、通过WHERE字句实现关联
2、通过关键字JOIN实现关联
*************************************/
/* 任务：查询学生的学号、姓名以及所在的班级号码和班级名称 */
/* 分析：班级表与学生表是一对多的关系，有公共字段ClassNo*/
/* 方法一、通过WHERE字句实现关联 */
SELECT Student . StuNo 学号 , Student . StuName 姓名 , Student . ClassNo 班级号码 , Class . ClassName 班级名称
FROM Class , Student
WHERE Class . ClassNo = Student . ClassNo ;
/* WHERE字句具有双重功能：不仅可用于设置筛选条件，也可用于设置关联条件 */
/* 方法二、利用关键字JOIN来实现关联 */
SELECT Student . StuNo 学号 , Student . StuName 姓名 , Student . ClassNo 班级号码 , Class . ClassName 班级名称
FROM Class INNER JOIN Student ON Class . ClassNo = Student . ClassNo ;
/* 任务：查询“韩霞”同学选修课程情况，只显示姓名与课程名称两个字段 */
/*
分析：查询结果的两个字段分别来自Student表与Course表，但是这两张表没有公共字段，
因此两个表无法直接关联，多对多的关系是无法直接关联起来的，怎么办呢？
那么我们就要找到另外一张表作为桥梁让这两张表关联起来。打开数据库关系图，
dbo.Diagram_0，可以看到Student表与Course表是通过StuCou表间接关联起来的。
于是，这个任务的完成就涉及到了三表的关联查询。
Student与StuCou表的公共字段是StuNo（学号），关联条件：Student.StuNo=StuCou.StuN0
StuCou与Course表的公共字段是CouNo（课程编号），关联条件：StuCou.CouNo=Course.CouNo
*/
/* 方法一、通过WHERE字句实现三表关联 */
SELECT StuName 姓名 , CouName 课程名称
FROM Student , StuCou , Course
WHERE Student . StuNo = StuCou . StuNo AND StuCou . CouNo = Course . CouNo /* 关联条件 */
AND StuName = '韩霞' ; /* 筛选条件 */
/* 方法二、通过关键字JOIN实现三表关联 */
SELECT StuName 姓名 , CouName 课程名称
FROM Student INNER JOIN StuCou ON Student . StuNo = StuCou . StuNo /* 关联与关联条件 */
INNER JOIN Course ON StuCou . CouNo = Course . CouNo /* 关联与关联条件 */
WHERE StuName = '韩霞' ;
SELECT StuName 姓名 , CouName 课程名称
FROM Student INNER JOIN StuCou INNER JOIN Course /* 三表关联 */
ON StuCou . CouNo = Course . CouNo ON Student . StuNo = StuCou . StuNo /* 关联条件 */
WHERE StuName = '韩霞' ;
文章来源: howard2005.blog.csdn.net，作者：howard2005，版权归原作者所有，如需转载，请联系作者。
原文链接：howard2005.blog.csdn.net/article/details/79427840
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）