数据库笔记02：查询与统计数据

/***************************

 第二单元：查询与统计数据

***************************/

/*

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='韩霞';