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

举报
howard2005 发表于 2021/12/29 23:56:43 2021/12/29
【摘要】 /***************************  第二单元:查询与统计数据 ***************************/ /* 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

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。