汇总部分内容来自网络(作者 :zhtbs)，比较基础的东西，能够了解比较基础的一些东西。
Select语句概要
数据库中数据的提取(查询)使用select 语法，主要有以下几点作用
l 提取的数据（搜索）
l 提取的数据进行排序（排序）
l 执行计算或汇总
Select文表达方法
SQL文处理Select 语句顺序步骤
我们将说明数据库在处理查询的时候,不同阶段都会产生中间结果表.这些产生出来的中间结果表都是在数据库后台运行的我们无法观察到.我们最终看到的数据就是最后的中间结果表.从句from从数据库中检索出来第一个中间结果表开始,到select从句结束.分为以下几步
|
1通过from关键字将要查询的表中的所有数据读
取到中间结果表。（包括表中所有数据行与列，行
数不变，列数不变）
|
2在where关键字后面加上要读取数据的条件，生
成一个新的中间结果表。
|
3 如果业务需要分组，可以通过Group by 将视图进
行分组，在次生成一个新的中间结果表。
|
4 使用having关键字为分组后的逻辑视图进行条件
筛选，来生成一个中间结果表。
|
5 通过select语句在最终的中间结果表中选择列。
查询数据时,数据库后台中间结果表变化过程
我们将从数据库在执行SQL查询计划的时候,数据库内部的变化与运行的原理来理解SQL语句执行过程.(数据库中分为2种视图,1为物理视图通过create view 语句创建,2为逻辑视图,就是我们提到的中间结果表)
(人员信息表)
|
编号 |
名称 |
部门 |
职务 |
年龄 |
性别 |
电话 |
|
122 |
李晶晶 |
人力 |
职员 |
26 |
女 |
2234555 |
|
123 |
张金 |
人力 |
职员 |
29 |
男 |
3322123 |
|
124 |
王力 |
机材 |
经理 |
35 |
男 |
2245553 |
|
125 |
李心 |
机材 |
职员 |
21 |
女 |
3332233 |
例:给出所有职务为职员的用户名称,部门,电话.
步骤一 执行from 表名称(人员信息表),数据库会在(人员信息表)表中进行一个全表扫描,将所有表信息放入一个逻辑视图中, 逻辑视图包括表中的全部的行于列的数据.
步骤一 产生的中间结果表
|
122 |
李晶晶 |
人力 |
职员 |
26 |
女 |
2234555 |
|
123 |
张金 |
人力 |
职员 |
29 |
男 |
3322123 |
|
124 |
王力 |
机材 |
经理 |
35 |
男 |
2245553 |
|
125 |
李心 |
机材 |
职员 |
21 |
女 |
3332233 |
步骤二 通过where职务=职员 的判断条件将步骤一中产生的逻辑视图的数据进行过滤,将满足条件的行产成一个新的逻辑视图.
步骤二 产生的中间结果表
|
122 |
李晶晶 |
人力 |
职员 |
26 |
女 |
2234555 |
|
123 |
张金 |
人力 |
职员 |
29 |
男 |
3322123 |
|
125 |
李心 |
机材 |
职员 |
21 |
女 |
3332233 |
步骤三 通过select 名称,部门,电话 语句把步骤二的逻辑视图中,名称,部门,电话列的数据取出生成一个新视图.select 语句指定那些列必须出现在最终逻辑视图中.
步骤三 产生的中间结果表
|
李晶晶 |
人力 |
2234555 |
|
张金 |
人力 |
3322123 |
|
李心 |
机材 |
3332233 |
步骤四 将产生的逻辑视图发送到客户端.完成本次SQL查询计划.
SQL编写顺序与逻辑视图生成过程.
|
多表信息查询表达方法
当多个表进行联合查询的时候,会发生一张表(A)中的数据行乘以别一张表(B)中的数据行,也就是A*B=所有查询数据.该结果产生的合并表数据被我们称为笛卡儿积.通常笛卡儿积会产生很多重复行的数据,我们要使用连接条件也就是A表和B表中指定的连接列来过滤掉重复和多余的笛卡儿积.
1 通过from关键字后的表名称,到数据库中将
表A和表B两个表中所有表数据取出到两个对应
的中间结果表中.
2 数据库会将两个中间结果表合成一个中间结果表,
而生成的这个中间结果表就是我们说的笛卡儿积.它
的数据内容就是A表的中间结果*B表的中间结果.为
A*B的数学关系
3 在笛卡儿积中间结果表中通过where关键字选择符
合列连接条件或者满足条件的行,将符合条件的行再
生成一个中间结果表.
4 在中间结果表中通过select关键字选择列,生成最终
中间结果表,把它传给客户端.
多表合并查询时数据库后台中间结果表变化过程
(人员信息表)
|
编号 |
名称 |
部门ID |
年龄 |
性别 |
电话 |
|
122 |
李晶晶 |
1 |
26 |
女 |
2234555 |
|
123 |
张金 |
1 |
29 |
男 |
3322123 |
|
124 |
王力 |
2 |
35 |
男 |
2245553 |
|
125 |
李心 |
3 |
21 |
女 |
3332233 |
( 部门信息表)
|
部门ID |
部门名称 |
部门简称 |
|
1 |
人力资源部 |
人力 |
|
2 |
运行维护部 |
运维 |
|
3 |
财务部 |
财务 |
例:查询部门简称为财务的人员信息,信息包括人员名称,部门简称, 部门名称,性别,电话
步骤一 执行from 人员信息表,部门信息表.数据库对(人员信息表), ( 部门信息表)2张表进行一个全表扫描.然后将2个表的数据生成一个笛卡儿积的逻辑视图.执行这个(select * from人员信息表,部门信息表 )SQL不加连接列条件我们就可以看到一个笛卡儿积数据.
步骤一 产生的中间结果表的笛卡儿积
|
编号 |
名称 |
部门ID |
年龄 |
性别 |
电话 |
部门ID |
部门名称 |
部门简称 |
|
122 |
李晶晶 |
1 |
26 |
女 |
2234555 |
1 |
人力资源部 |
人力 |
|
122 |
李晶晶 |
1 |
26 |
女 |
2234555 |
2 |
运行维护部 |
运维 |
|
122 |
李晶晶 |
1 |
26 |
女 |
2234555 |
3 |
财务部 |
财务 |
|
123 |
张金 |
1 |
29 |
男 |
3322123 |
1 |
人力资源部 |
人力 |
|
123 |
张金 |
1 |
29 |
男 |
3322123 |
2 |
运行维护部 |
运维 |
|
123 |
张金 |
1 |
29 |
男 |
3322123 |
3 |
财务部 |
财务 |
|
124 |
王力 |
2 |
35 |
男 |
2245553 |
1 |
人力资源部 |
人力 |
|
124 |
王力 |
2 |
35 |
男 |
2245553 |
2 |
运行维护部 |
运维 |
|
124 |
王力 |
2 |
35 |
男 |
2245553 |
3 |
财务部 |
财务 |
|
125 |
李心 |
3 |
21 |
女 |
3332233 |
1 |
人力资源部 |
人力 |
|
125 |
李心 |
3 |
21 |
女 |
3332233 |
2 |
运行维护部 |
运维 |
|
125 |
李心 |
3 |
21 |
女 |
3332233 |
3 |
财务部 |
财务 |
步骤二 通过连接列条件取出步骤一逻辑视图中符合条件的行,生成一个新的逻辑视图. 连接列条件为 where人员信息表.部门ID=部门信息表. 部门ID
步骤二产生的中间结果表
|
编号 |
名称 |
部门ID |
年龄 |
性别 |
电话 |
部门ID |
部门名称 |
部门简称 |
|
122 |
李晶晶 |
1 |
26 |
女 |
2234555 |
1 |
人力资源部 |
人力 |
|
123 |
张金 |
1 |
29 |
男 |
3322123 |
1 |
人力资源部 |
人力 |
|
124 |
王力 |
2 |
35 |
男 |
2245553 |
2 |
运行维护部 |
运维 |
|
125 |
李心 |
3 |
21 |
女 |
3332233 |
3 |
财务部 |
财务 |
步骤三 通过判断条件 and 部门信息表.部门简称=财务 取出上一个逻辑视图中符合条件的行.生成一个新的逻辑视图
步骤三 产生的中间结果表
|
编号 |
名称 |
部门ID |
年龄 |
性别 |
电话 |
部门ID |
部门名称 |
部门简称 |
|
125 |
李心 |
3 |
21 |
女 |
3332233 |
3 |
财务部 |
财务 |
步骤四 通过select 人员信息表.人员名称,部门信息表.部门简称,人员信息表.部门信息表.部门名称, 人员信息表.性别, 人员信息表.电话 选择要列,生成一个最终逻辑视图.
步骤四 最终中间结果表
|
名称 |
部门名称 |
部门简称 |
性别 |
电话 |
|
李心 |
财务部 |
财务 |
女 |
3332233 |
步骤五 将产生的逻辑视图发送到客户端.完成本次SQL查询计划.
SQL编写顺序与逻辑视图生成过程.
|
总结
通过上面的介绍,我们知道了数据库在执行查询SQL计划时逻辑视图变化过程.我们在编写查询计划的时候,需要按照逻辑视图变化过程来编写,先写from 再写where最后select这个顺序.在多表的时候,需要where 来指定表的接合条件来过滤笛卡儿积.
多表合并查询时候的内连接与外连接
在上面我们使用多表查询的时候,对笛卡儿积的处理是使用where 加列连接条件.这种方法也被称为隐性连接.在SQL标准语句中有一种from 从句的扩展方法可以直接完成上面的功能. 这种从句的扩展方法也被成为显性内连接.关键字为 inner join on后边指定连接条件。
Inner join 表达方法
数据库中有以下2张表
(人员信息表)
|
编号 |
名称 |
部门ID |
年龄 |
性别 |
电话 |
|
122 |
李晶晶 |
1 |
26 |
女 |
2234555 |
|
123 |
张金 |
1 |
29 |
男 |
3322123 |
|
124 |
王力 |
2 |
35 |
男 |
2245553 |
|
125 |
李心 |
3 |
21 |
女 |
3332233 |
( 部门信息表)
|
部门ID |
部门名称 |
部门简称 |
|
1 |
人力资源部 |
人力 |
|
2 |
运行维护部 |
运维 |
|
3 |
财务部 |
财务 |
运行以下SQL文
-获得结果-
|
编号 |
名称 |
部门ID |
年龄 |
性别 |
电话 |
部门ID |
部门名称 |
部门简称 |
|
122 |
李晶晶 |
1 |
26 |
女 |
2234555 |
1 |
人力资源部 |
人力 |
|
123 |
张金 |
1 |
29 |
男 |
3322123 |
1 |
人力资源部 |
人力 |
|
124 |
王力 |
2 |
35 |
男 |
2245553 |
2 |
运行维护部 |
运维 |
|
125 |
李心 |
3 |
21 |
女 |
3332233 |
3 |
财务部 |
财务 |
From 可以通过内连接直接获得到要得到的数据结果,而不产生笛卡儿积现象.
|
我们也可以通过上面WHERE 列连接条件的方法得到一个与内连接(inner join on) 相同的数据结构.可以说WHERE 列连接条件和(inner join on)都是内连接.Where是隐性连接, (inner join on)为显性连接,大家可以根据个人习惯来选择那种方式来进行多表连接.
内连接分为2种连接方法
1 显性连接，FROM INNER JOIN ON
2 隐性连接，where 列的关联条件。
在低版本数据库中，显性连接和隐性连接执行效率上会有差别，在现在的数据库中基本没有区别了。完全根据个人习惯或者项目要求来选择使用哪种连接方式。
外连接
虽然我们学会使用了内连接,但是还有很多复杂情况内连接无法解决.SQL还给我们提供了一种连接方法叫外连接.在外连接的表合并过程中,我们要定义一个驱动表,以驱动表为基础与其他表进行多表合并的过程叫外连接. 外连接分为左连接和右连接,
显性连接 from (LEFT JOIN ON 列连接条件 ,RIGHT JOIN ON列连接条件)
隐性连接 where 列连接条件(+)=列连接条件,列连接条件=列连接条件(+) 表示.
SQL表达方式
显性连接
隐性连接
如果你想以表1为驱动表就在 where 表1=表2(+) 为左表连接
如果你想以表2为驱动表就在 where 表1(+)=表2 为右表连接
数据库有以下3张表
(人员信息表)
|
编号 |
名称 |
部门ID |
年龄 |
职务ID |
性别 |
电话 |
|
122 |
李晶晶 |
1 |
26 |
1 |
女 |
2234555 |
|
123 |
张金 |
1 |
29 |
null |
男 |
3322123 |
|
124 |
王力 |
2 |
35 |
1 |
男 |
2245553 |
|
125 |
李心 |
3 |
21 |
2 |
女 |
3332233 |
|
126 |
鲁兰 |
null |
232 |
null |
女 |
3486443 |
( 职务信息表)
|
职务ID |
职务名称 |
|
1 |
职员 |
|
2 |
经理 |
( 部门信息表)
|
部门ID |
部门名称 |
|
1 |
人力资源部 |
|
2 |
运行维护部 |
|
3 |
财务部 |
执行以下SQL时
|
-获得结果-
|
编号 |
名称 |
部门ID |
年龄 |
职务ID |
性别 |
电话 |
职务ID |
职务名称 |
|
122 |
李晶晶 |
1 |
26 |
1 |
女 |
2234555 |
1 |
职员 |
|
123 |
张金 |
1 |
29 |
null |
女 |
3322123 |
null |
null |
|
124 |
王力 |
2 |
35 |
1 |
男 |
2245553 |
1 |
职员 |
|
125 |
李心 |
3 |
21 |
2 |
女 |
3332233 |
2 |
经理 |
|
126 |
鲁兰 |
null |
232 |
null |
女 |
3486443 |
null |
null |
我们以人员信息表为驱动表进行多表合并查询. 驱动表所有信息全部显示,和职务表没有关联条件人员信息也将被查询出来.如果我们使用内连接,和职务表没有关联的那条信息将无法被查询出来。上面的结果粉色部分数据在内连接的时候不能被查询出来。只有我们以人员信息表为驱动表进行外连接才可以得到上面的数据。
例:查询出部门名称不能为空的所有女性的员工名称, 部门名称,职务名称信息.
SQL执行过程如下图所示
隐性连接SQL文
|
显性连接SQL文
|
步骤一 在(人员信息表)与 (部门信息表)表中执行一个内连接,内连接会将人员表中部门ID为空的人员信息从中间结果表中过滤掉.
步骤一 产生的中间结果表
|
编号 |
名称 |
部门ID |
年龄 |
职务ID |
性别 |
电话 |
部门ID |
部门名称 |
|
122 |
李晶晶 |
1 |
26 |
1 |
女 |
2234555 |
1 |
人力资源部 |
|
123 |
张金 |
1 |
29 |
女 |
3322123 |
运行维护部 |
||
|
124 |
王力 |
2 |
35 |
1 |
男 |
2245553 |
1 |
运行维护部 |
|
125 |
李心 |
3 |
21 |
2 |
女 |
3332233 |
2 |
财务部 |
步骤二 中间结果表于( 职务信息表) 执行一个外连接,中间结果表为驱动表. 驱动表中即使职务id为空,新生成的中间结果表也会这条信息保存下来.
步骤二 产生的中间结果表
|
编号 |
名称 |
部门id |
年龄 |
职务id |
性别 |
电话 |
部门id |
部门名称 |
职务id |
职务名称 |
|
122 |
李晶晶 |
1 |
26 |
1 |
女 |
2234555 |
1 |
人力资源部 |
1 |
职员 |
|
123 |
张金 |
1 |
29 |
女 |
3322123 |
运行维护部 |
||||
|
124 |
王力 |
2 |
35 |
1 |
男 |
2245553 |
1 |
运行维护部 |
1 |
职员 |
|
125 |
李心 |
3 |
21 |
2 |
女 |
3332233 |
2 |
财务部 |
2 |
经理 |
步骤三 选择性别为女的所有行生成中间结果表.虽然职务有空值出现,但是通过外联它还是可以不被过滤掉.
步骤三 产生的中间结果表
|
122 |
李晶晶 |
1 |
26 |
1 |
女 |
2234555 |
1 |
人力资源部 |
1 |
职员 |
|
123 |
张金 |
1 |
29 |
女 |
3322123 |
运行维护部 |
||||
|
125 |
李心 |
3 |
21 |
2 |
女 |
3332233 |
2 |
财务部 |
2 |
经理 |
步骤四 选择要显示的列,生成最终中间结果表,将显示给客户端.
步骤四 产生的中间结果表
|
名称 |
部门名称 |
职务名称 |
|
李晶晶 |
人力资源部 |
职员 |
|
张金 |
运行维护部 |
|
|
李心 |
财务部 |
经理 |
Select 表达式
select表达式也是一种常量,它会对应某种数据类型.如数字,字符串,日期,时间等.这些有自己属性类型的表达式,也叫标量表达式.除了标量表达式之外,还有行表达式和表表达式.行表达式是由一组标量表达式组成的,每个行表达式都有一个或多个标量表达式.有多个或者一个行表达式组成的表达式为表表达式也叫表值.
表达式的分类
l 标量表达式
l 行表达式
l 表表达式
( 人员工资表)
|
人员名称 |
基本工资 |
奖金 |
罚款 |
补助 |
|
张淋淋 |
1200 |
750 |
50 |
120 |
|
马越 |
800 |
200 |
10 |
120 |
|
李洋 |
900 |
350 |
40 |
120 |
SELECT 人员名称(标量表达式),基本工资(标量表达式),奖金(标量表达式),补助(标量表达式)
FROM 人员工资表
Select 关键字选择的列部分就是一个标量表达式.你可以将中间结果表每个列单元格看成为一个标量表达式.
-获得结果-
|
人员名称 |
基本工资 |
奖金 |
补助 |
|
张淋淋 |
1200 |
750 |
120 |
|
马越 |
800 |
200 |
120 |
|
李洋 |
900 |
350 |
120 |
由多个标量表达式组成的一行数组,为行表达式
|
张淋淋 |
1200 |
750 |
120 |
这4个标量表达式组成了一个行表达式
由一个或多个行表达式构成了表表达式
|
张淋淋 |
1200 |
750 |
120 |
||
|
马越 |
800 |
200 |
120 |
||
|
李洋 |
900 |
350 |
120 |
我们对行表达式和表表达式也可以叫做复合行表达式.
标量表达式操作
标量表达式就是select关键字后面的列要表达的信息部分.如果2个标量表达式类型相同可以进行运算,合并或者拼接成字符串.如果标量表达式类型不一样,也可以将它们拼接成字符串.注意select后面的列不可以出现复合行(行表达式,表表达式)
例 给出所有人员名称和他们当月工资总合.
SQL文
|
步骤一 from关键字将人员工资表中的信息,取到一个中间结果表中.
步骤一 产生的中间结果表
|
人员名称 |
基本工资 |
奖金 |
罚款 |
补助 |
|
张淋淋 |
1200 |
750 |
50 |
120 |
|
马越 |
800 |
200 |
10 |
120 |
|
李洋 |
900 |
350 |
40 |
120 |
步骤二 SELECT关键字表示了我们要生成一个有人员名称和基本工资列+奖金列+补助列总和为一列的最终结果表.首先数据库会把中间结果表中的每个行表达式中基本工资列上的数据,奖金列上的数据,补助列上的数据进行相加.把相加的结果放在一个新列中.生成一个新的中间结果表。
步骤二 产生的中间结果表
|
人员名称 |
基本工资 |
奖金 |
补助 |
产生的新列 |
|
张淋淋 |
1200 |
750 |
120 |
2070 |
|
马越 |
800 |
200 |
120 |
1120 |
|
李洋 |
900 |
350 |
120 |
1370 |
步骤三 在新的中间结果表中抽取人员名称列和新产生的列为最终结果表。发送给客户端。
-获得结果-
|
人员名称 |
(基本工资+奖金+补助) |
|
张淋淋 |
2070 |
|
马越 |
1120 |
|
李洋 |
1370 |
大家可以清楚的明白了每个行中的标量表达式之间是可以进行运算的。也可以通过SELECT关键字中间结果表中的行表达式中创建新的列。
例 给出人员名称和他们当月工资总合(减去罚款金额)，将它们生成在一个新列中表示.
|
步骤一 from关键字将人员工资表中的信息,取道一个中间结果表中.
步骤一 产生的中间结果表
|
人员名称 |
基本工资 |
奖金 |
罚款 |
补助 |
|
张淋淋 |
1200 |
750 |
50 |
120 |
|
马越 |
800 |
200 |
10 |
120 |
|
李洋 |
900 |
350 |
40 |
120 |
步骤二 通过SELECT关键字，中间结果表中的每行上面的(基本工作列+奖金列+补助列)-罚款列生成一个总和数。人员名称列上的数据要和新生成的总和数据合并成一个新列（新的标量表达式），直接合并是不可以的，因为2个列的属性（也叫常量）是不一样的 。我们要使用（人员名称||‘’||新列）方法来合并它，因为 ||‘’||会将两边的表量表达式默认成为字符串类型。2个字符串类型的表量表达式可以合并成一个新的表量表达式。
步骤二 产生的中间结果表
|
人员名称 |
基本工资 |
奖金 |
罚款 |
补助 |
产生的新列 |
|
张淋淋 |
1200 |
750 |
50 |
120 |
张淋淋2020 |
|
马越 |
800 |
200 |
10 |
120 |
马越1110 |
|
李洋 |
900 |
350 |
40 |
120 |
李洋1330 |
步骤三 在新的中间结果表中抽取新产生的列为最终结果表。发送给客户端
-获得结果-
|
人员名称||’’||… |
|
张淋淋2020 |
|
马越1110 |
|
李洋1330 |
现在我们只展示了标量表达式的列合并和列运算过程,它的作用范围就在行表达式中.
在SQL最终结果表中只支持标量表达式
现在的SQL数据库中最终结果表(最后传到客户端中的表),列单元中只能存放标量表达式.但是在中间结果表列单元中是可以出现复合行(行表达式,表表达式)的.有很多情况我们需要在中间结果表的列中的存放复合行信息.但是这些信息被传到最终结果表会出现异常,不会被识别.所以我们就要使用到聚合函数将中间结果表有复合行的列转换成标量表达式,传给最终结果表.保证最终结果表的列中不会出现复合行,发送给客户端.
例 给出所有人员名称和本人工资总合和全体人员的工资总和
|
( 人员工资表)
|
人员名称 |
基本工资 |
|
张淋淋 |
1200 |
|
马越 |
800 |
|
李洋 |
900 |
步骤一 取得人员工资表中间结果表.
步骤一 产生的中间结果表
|
人员名称 |
基本工资 |
|
张淋淋 |
1200 |
|
马越 |
800 |
|
李洋 |
900 |
步骤二 select关键字会生成一个新中间结果表,中间结果表中每行都创建出一个新列.新列单元格中装入一个(SELECT 基本工资 FROM 人员工资表)语句生成的表表达式.中间结果表
中有几行就要执行几次SQL(SELECT 基本工资 FROM 人员工资表)语句,每次执行都要走一次SELECT生成过程.
|
步骤二 产生的中间结果表
|
人员名称 |
基本工资 |
新生成列 |
||||||
|
张淋淋 |
1200 |
|||||||
|
马越 |
800 |
|
||||||
|
李洋 |
900 |
步骤三 将中间结果表生成最终结果表,生成过程中数据库执行出现异常
在执行这条SQL查询计划时出现错误 ,因为在最终结果表列部分有一个表表达式出现,SQL不支持在传给客户端的最终结果表中有复合行信息出现在列部分.我们为了得到正确的结果,需要将这个复合行(表表达式)转换成为标量表达式,它才能在SQL的列中正确执行.
SELECT 人员名称 ,
(基本工作+奖金+补助)-罚款,
|
(SELECT (基本工作+奖金+补助)- 罚款 FROM 人员工作表) ← 表表达式不可以出现在列部分
From 人员工作表
我们使用SUM()函数将表表达式中的数据转换成标量
表达式,我们这么做的目的就是将表表达式存在的多个
数据行数据结构,变成一个数据行和列的数据结构.
正确的SQL
步骤一 取得人员工资表中间结果表.
步骤一 产生的中间结果表
|
人员名称 |
基本工资 |
|
张淋淋 |
1200 |
|
马越 |
800 |
|
李洋 |
900 |
步骤二 select关键字会生成一个新中间结果表,中间结果表中每行都创建出一个新列.新列单元格中装入一个(SELECT 基本工资 FROM 人员工资表)语句生成的表表达式.中间结果表
中有几行就要执行几次SQL(SELECT 基本工资 FROM 人员工资表)语句,每次执行都要执行一次SELECT生成过程.通过SUM函数将表表达式转换成一个标量表达式.
步骤二 产生的中间结果表
|
人员名称 |
基本工资 |
新生成列 |
||||||
|
张淋淋 |
1200 |
4460 |
||||||
|
马越 |
800 |
4460 |
||||||
|
李洋 |
900 |
4460 |
步骤三 将中间结果表生成最终结果表,传给客户端.
-获得结果-
|
人员名称 |
工资 |
所有人工资总和 |
|
张淋淋 |
2070 |
4460 |
|
马越 |
1120 |
4460 |
|
李洋 |
1370 |
4460 |
通过上面的介绍我们现在知道了在SQL查询计划中 SELECT 列部分只能以标量表达式的形式传送给客户端.如果有复合行情况出现,我们要使用函数或者其他手段将它变成一个标量表达式.不同标量表达式都有自己的属性,相同属性的标量表达式可以进行运算或者合并.
表达式常量类型
数字常量 字符串常量 日期常量 时间常量
时戳常量 布尔常量 十六进制常量
不同数据库为处理结果表中列的标量表达式都提供了功能强大的函数,这些函数如何的使用和学习不在本次学习的范围.
表表达式与子查询
我们已提及每个表表达式的值都是有一组行构成.如果我们将表表达式放入到From中在又from生成一个表表达式.而这个重新生成的过程叫做子查询.
(人员信息表)
|
编号 |
名称 |
部门 |
职务 |
年龄 |
性别 |
电话 |
|
122 |
李晶晶 |
人力 |
职员 |
26 |
女 |
2234555 |
|
123 |
张金 |
人力 |
职员 |
29 |
男 |
3322123 |
|
124 |
王力 |
机材 |
经理 |
35 |
男 |
2245553 |
|
125 |
李心 |
机材 |
职员 |
21 |
女 |
3332233 |
例 给出年龄小于30的女性人员名称.
步骤一 通过from关键字 将人员信息表中的所有信息放入一个中间结果表中.
步骤一 产生的中间结果表
|
编号 |
名称 |
部门 |
职务 |
年龄 |
性别 |
电话 |
|
122 |
李晶晶 |
人力 |
职员 |
26 |
女 |
2234555 |
|
123 |
张金 |
人力 |
职员 |
29 |
男 |
3322123 |
|
124 |
王力 |
机材 |
经理 |
35 |
男 |
2245553 |
|
125 |
李心 |
机材 |
职员 |
21 |
女 |
3332233 |
步骤二 使用where 判断将年龄大于30的人员信息行选择出来,生成一个新的中间结果表.
步骤二 产生的中间结果表
|
122 |
李晶晶 |
人力 |
职员 |
26 |
女 |
2234555 |
|
123 |
张金 |
人力 |
职员 |
29 |
男 |
3322123 |
|
125 |
李心 |
机材 |
职员 |
21 |
女 |
3332233 |
步骤三 通过select 关键字 选择每行要保留的列部分,生成一个中间结果表.这个select 选择了名称和性别2个列.
步骤三 产生的中间结果表
|
李晶晶 |
女 |
|
张金 |
男 |
|
李心 |
女 |
步骤四 中间结果表结构=表表达式数据结构,我们将这个表表达式再传给一个from 让它重新生成一个中间结果表.这个传递重新生成的过程就叫子查询.产生的2个中间结果表内容都是一样的,但是在数据库下步操作中无法区分要操作那个中间结果表.为了解决这个问题我们需要给其中的一个中间结果表起一个别名,通过操作这个别名数据库就知道要处理那个中间结果表中的数据了.
步骤四 子查询产成的中间结果表别名为(人员表1)
|
李晶晶 |
女 |
|
张金 |
男 |
|
李心 |
女 |
步骤五 使用where人员表1.性别=女 为条件别名为人员表1的中间结果表中,选择符合条件的行.生成一个中间结果表.
步骤五 产生的中间结果表
|
李晶晶 |
女 |
|
李心 |
女 |
步骤六 再通过select 关键字 选择名称列在生成一个最终结果表.发送给客户端
-获得结果-
|
人员名称 |
|
李晶晶 |
|
李心 |
从上面的例子我们可以看到, 表表达式也可包含其他的表表达式, 表表达式是可以嵌套的. 表表达式是在FROM 中进行从新定义和处理的.
SELECT *
FROM ( SELECT * FROM (
SELECT * FROM(
SELECT * FROM 人员信息表) AS A3) AS A2 )AS A1
表表达式允许这样嵌套下去.
表表达式可以成为判断条件
表表达式也可以成为where 判断和对比的条件信息.数据库为我们提供exists和in()这些方法,它们可以以被查询出来的表表达式为参照条件进行判断,选择那些符合条件的行,来生成新的中间结果表. Where 不光可以一对一的判断,也可以一对多的判断. 一对多就是以一个复合行的数据结构(表表达式)为判断条件, 一对一就是以一个标量表达式为判断标准.
(人员信息表)
|
人员ID |
名称 |
部门 |
职务 |
年龄 |
性别 |
|
1 |
张淋淋 |
人力 |
职员 |
26 |
女 |
|
2 |
马越 |
人力 |
职员 |
29 |
男 |
|
3 |
李洋 |
机材 |
经理 |
35 |
男 |
|
4 |
王晓晴 |
机材 |
职员 |
21 |
女 |
( 人员工资表)
|
人员ID |
名称 |
基本工资 |
奖金 |
罚款 |
补助 |
|
1 |
张淋淋 |
1200 |
750 |
50 |
120 |
|
2 |
马越 |
800 |
200 |
10 |
120 |
|
3 |
李洋 |
900 |
350 |
40 |
120 |
例: 给出职务是经理的员工当月工资和员工名称
使用内连接处理一对一判断方法
|
使用一对多处理方法
表表达式也可以成为判断条件.在有些特殊的业务
需求中,我们的判断条件不是一对一的形式出现,
而是需要一对多的判断.这个时候我们就需要使用
到表表达式了.一般在where (in,all,any,exists)
判断函数中我们可以使用到表表达式
步骤一 通过from 关键字将人员工资表中的信息拿到一个中间结果表中.
步骤一 产生的中间结果表
|
人员ID |
名称 |
基本工资 |
奖金 |
罚款 |
补助 |
|
1 |
张淋淋 |
1200 |
750 |
50 |
120 |
|
2 |
马越 |
800 |
200 |
10 |
120 |
|
3 |
李洋 |
900 |
350 |
40 |
120 |
步骤二 通过where 关键字中的exists方法会将中间结果表中,每行人员ID 列上的信息都拿到一个新的SQL语句中,作为where判断条件.在数据库中执行一次这个SQL,查询人员信息表,将得到的最终结果表返回给exists函数, exists函数判断这个结果表是否有值.如果有值就将这行放入一个新的中间结果表中.
中间结果表中有3行,数据库就执行3次SQL查询语句.来查询人员信息表中是否有符合人员ID 列和职务是经理的数据信息.
|
1 |
张淋淋 |
1200 |
750 |
50 |
120 |
|
2 |
马越 |
800 |
200 |
10 |
120 |
|
3 |
李洋 |
900 |
350 |
40 |
120 |
每次执行SQL查询人员信息表数据库都会有以下步骤
1 通过from把人员信息表信息放入一个中间结果表.
2 通过where人员信息表.人员ID=人员工资表中的人员ID AND 人员信息表.职务=经理 条件判断选择那些行生成新的中间结果表.
3 将中间结果表生成最终结果表传递回exists函数中.
步骤二 产生的中间结果表
|
3 |
李洋 |
900 |
350 |
40 |
120 |
步骤三 通过select 关键字生成一个有人员名称和基本工资列+奖金列+补助列-罚款总和为一个新列,最终结果表的行中只保留名称列和新列.
步骤三 产生的中间结果表
|
李洋 |
1330 |
步骤四 将最终结果表传给客户端
-获得结果-
|
人员名称 |
工资 |
|
李洋 |
1330 |
如果我们用一对一的判断方法需要把人员信息表和人员工作表建立一个内连接.在内连接生成的中间结果表中一对一的判断选择符合条件的行信息.把符合条件的行生成一个新的中间结果表传给客户端.我们现在可以使用以表表达式为判断条件的方法(一对多)进行判断, 只需要将人员工资表的中间结果表中每行为判断条件的列信息,拿到人员表产生的中间结果表中进行判断,如果有符合条件的表表达式产生,人员工作表的中间结果表就会将这个行保存到新的中间结果表中 ,数据库就会将新的中间结果表传递给到下个步骤中.2种对比方法在不同条件下各有各的优点,只能通过开发者的经验来判断在什么情况下该使用方法.
表表达式我们可以给它看成一个集合,这个集合的数据结构很想我们的多维数组.它只能出现在数据库后台执行过程,我们不能在前端运行的select列中看到它. 表表达式可以存在于SQL 每个阶段SELECT 中 from 中 where 中 都可以出现表表达式.
|
Select 生成一个量标表达式
|
||||||||||||||
|
||||||||||||||
From
|
Where 以表表达式为判断条件
为
行表达式的使用
前面说过行表达式为一组横向的标量表达式。数据结构（'张**', '人力资源部门''女'）
它们会出现在 insert into 表名 values (行表达式) 语句中。
(人员信息表)
|
人员ID |
名称 |
部门 |
年龄 |
性别 |
|
1 |
张淋淋 |
人力 |
26 |
女 |
|
2 |
马越 |
人力 |
29 |
男 |
|
3 |
李洋 |
机材 |
35 |
男 |
|
4 |
王晓晴 |
机材 |
21 |
女 |
例：向人员信息表中插入一条人员信息。
Group by
Group by为分组查询关键字.通过from得到中间结果表,在中间结果表中某一个列有很多重复的数据.我们希望将这些重复的数据合并成一个数据行,那么group by 就可以帮助我们把这个列中的重复数据合并成一条,而这一条原来对应的其他列数据都合并在一个行中.分组查询就是将中间结果表信息改变成行表达式.在使用聚合函数将复合条件的行表达式转换成标量达式.
Group by 表达方法
(工资表)
|
名称 |
日期 |
工资金额 |
|
张淋淋 |
2012-08 |
1260 |
|
张淋淋 |
2012-09 |
1160 |
|
张淋淋 |
2012-10 |
1230 |
|
张淋淋 |
2012-11 |
1170 |
|
李洋 |
2012-09 |
1400 |
|
李洋 |
2012-10 |
1470 |
|
李洋 |
2012-11 |
1305 |
|
王晓林 |
2012-09 |
1350 |
|
王晓林 |
2012-10 |
1320 |
|
王晓林 |
2012-11 |
1300 |
例 给出所有开过工资人员名称, 人员名称之能出现一次.
|
步骤一 使用from 关键字扫描工资表.然后在数据库中生成一个中间结果表.
步骤一 中间结果表结构
|
名称 |
日期 |
工资金额 |
|
张淋淋 |
2012-08 |
1260 |
|
张淋淋 |
2012-09 |
1160 |
|
张淋淋 |
2012-10 |
1230 |
|
张淋淋 |
2012-11 |
1170 |
|
李洋 |
2012-09 |
1400 |
|
李洋 |
2012-10 |
1470 |
|
李洋 |
2012-11 |
1305 |
|
王晓林 |
2012-09 |
1350 |
|
王晓林 |
2012-10 |
1320 |
|
王晓林 |
2012-11 |
1300 |
步骤二 分组group by 关键字将名称列,名称相同的数据行进行分组合并,生成一个新的中间结果表. 新的中间结果表在名称列中有一个值,而其他列可以包含一个或者多个值.
步骤二 中间结果表结构
|
名称 |
日期 |
工资金额 |
|
张淋淋 |
2012-08 |
1260 |
|
2012-09 |
1160 |
|
|
2012-10 |
1230 |
|
|
2012-11 |
1170 |
|
|
李洋 |
2012-09 |
1400 |
|
2012-10 |
1470 |
|
|
2012-11 |
1305 |
|
|
王晓林 |
2012-09 |
1350 |
|
2012-10 |
1320 |
|
|
2012-11 |
1300 |
步骤三 select 关键字选择名称列,生成最终结果表.日期列和工资金额列不可以在最终结果表出现,因为他们中有复合行.
步骤三 中间结果表结构
|
名称 |
|
张淋淋 |
|
李洋 |
|
王晓林 |
步骤四 将最终结果表传给客户端.
例 给出每位员工姓名和他们的工资总合.
SQL文
|
步骤一 使用from 关键字扫描工资表.然后在数据库中生成一个中间结果表.
步骤一 中间结果表结构
|
名称 |
日期 |
工资金额 |
|
张淋淋 |
2012-08 |
1260 |
|
张淋淋 |
2012-09 |
1160 |
|
张淋淋 |
2012-10 |
1230 |
|
张淋淋 |
2012-11 |
1170 |
|
李洋 |
2012-09 |
1400 |
|
李洋 |
2012-10 |
1470 |
|
李洋 |
2012-11 |
1305 |
|
王晓林 |
2012-09 |
1350 |
|
王晓林 |
2012-10 |
1320 |
|
王晓林 |
2012-11 |
1300 |
步骤二 分组group by 关键字将名称列,名称相同的数据行进行分组合并,生成一个新的中间结果表. 新的中间结果表在名称列中有一个值,而其他列可以包含一个或者多个值.
步骤二 中间结果表结构
|
名称 |
日期 |
工资金额 |
|
张淋淋 |
2012-08 |
1260 |
|
2012-09 |
1160 |
|
|
2012-10 |
1230 |
|
|
2012-11 |
1170 |
|
|
李洋 |
2012-09 |
1400 |
|
2012-10 |
1470 |
|
|
2012-11 |
1305 |
|
|
王晓林 |
2012-09 |
1350 |
|
2012-10 |
1320 |
|
|
2012-11 |
1300 |
步骤三 通过SELECT关键字将中间结果表中的名称列中的数据拿到一个新的中间结果表中,通过SUM函数生成一个新列,这个新列里的数据就是每行中工资金额列上的数据总和.在把这个新列放到新的中间结果表中.
中间结果表创建新列过程
|
名称 |
日期 |
工资金额 |
SUM函数 |
新列 |
|
张淋淋 |
2012-08 |
1260 |
工资列相加 |
4820 |
|
2012-09 |
1160 |
|||
|
2012-10 |
1230 |
|||
|
2012-11 |
1170 |
|||
|
李洋 |
2012-09 |
1400 |
工资列相加 |
4175 |
|
2012-10 |
1470 |
|||
|
2012-11 |
1305 |
|||
|
王晓林 |
2012-09 |
1350 |
工资列相加 |
3970 |
|
2012-10 |
1320 |
|||
|
2012-11 |
1300 |
步骤三 产成的中间结果表
|
名称 |
新列 |
|
张淋淋 |
4820 |
|
李洋 |
4175 |
|
王晓林 |
3970 |
步骤四 将产生的结果表,传给客户端.
HAVING 分组判断
Having 功能和使用方法和where 很象,它主要的作用范围是在分组后产生的行中,这些行中,有的列单元中出现了复合行.我们可以使用Having来对这写有复合行的列进行判断和筛选.
(工资表)
|
名称 |
日期 |
工资金额 |
|
张淋淋 |
2012-08 |
1260 |
|
张淋淋 |
2012-09 |
1160 |
|
张淋淋 |
2012-10 |
1230 |
|
张淋淋 |
2012-11 |
1170 |
|
李洋 |
2012-09 |
1400 |
|
李洋 |
2012-10 |
1470 |
|
李洋 |
2012-11 |
1305 |
|
王晓林 |
2012-09 |
1350 |
|
王晓林 |
2012-10 |
1320 |
|
王晓林 |
2012-11 |
1300 |
例 给出所有工资总和大于4000的员工姓名与工资总和.
-获得结果-
|
名称 |
新列 |
|
张淋淋 |
4820 |
|
李洋 |
4175 |
SQL文
|
步骤一 ,二 产生的结果同上
步骤三 使用HAVING关键字判断每行工资金额列所有数据相加的总和是否大于4000,如果大于4000的就放入到一个新的中间表中.
步骤三 判断过程
|
名称 |
日期 |
工资金额 |
SUM函数 |
|
张淋淋 |
2012-08 |
1260 |
SUM(工资金额) =4820 判断它是否大于 4000 |
|
2012-09 |
1160 |
||
|
2012-10 |
1230 |
||
|
2012-11 |
1170 |
||
|
李洋 |
2012-09 |
1400 |
SUM(工资金额) =4175 判断它是否大于 4000 |
|
2012-10 |
1470 |
||
|
2012-11 |
1305 |
||
|
王晓林 |
2012-09 |
1350 |
SUM(工资金额) =3970 判断它是否大于 4000 |
|
2012-10 |
1320 |
||
|
2012-11 |
1300 |
步骤三 产生的中间结果表结构
|
名称 |
日期 |
工资金额 |
|
张淋淋 |
2012-08 |
1260 |
|
2012-09 |
1160 |
|
|
2012-10 |
1230 |
|
|
2012-11 |
1170 |
|
|
李洋 |
2012-09 |
1400 |
|
2012-10 |
1470 |
|
|
2012-11 |
1305 |
步骤四 通过SELECT关键字将中间结果表中的名称列中的数据拿到一个新的中间结果表中,通过SUM函数生成一个新列,这个新列里的数据就是每行中工资金额列上的数据总和.在把这个新列放到新的中间结果表中.
步骤四 中间结果表创建新列过程
|
名称 |
日期 |
工资金额 |
SUM函数 |
新列 |
|
张淋淋 |
2012-08 |
1260 |
工资列相加 |
4820 |
|
2012-09 |
1160 |
|||
|
2012-10 |
1230 |
|||
|
2012-11 |
1170 |
|||
|
李洋 |
2012-09 |
1400 |
工资列相加 |
4175 |
|
2012-10 |
1470 |
|||
|
2012-11 |
1305 |
步骤四 中间结果表结构
|
名称 |
新列 |
|
张淋淋 |
4820 |
|
李洋 |
4175 |
步骤五 将最终结果表传给客户端.
通过上面的例子大家可以看到了HAVING一定要在group by分组后使用，主要的作用就是判断分组后产生的复合行。
HAVING嵌套判断
(工资表)
|
名称 |
部门 |
工资金额 |
|
张淋淋 |
人力 |
1100 |
|
王媛媛 |
人力 |
1160 |
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
方芳 |
财务 |
1400 |
|
李静 |
财务 |
1470 |
|
张萍 |
财务 |
1305 |
|
王晓林 |
部门1 |
1570 |
|
王彤 |
部门1 |
1200 |
例 给出每个部门大于部门平均工资的人员名字，部门，工资金额。那么通过我们现在的知识来解决这个问题。
SQL文
步骤一 通过FROM 产生一个中间结果表。为了不让数据库和我们将要生成了别一个中间结果表发生混乱我们给这个中间结果表起一个别名，别名名称为工资表别名
步骤一 产生的中间结果表
|
名称 |
部门 |
工资金额 |
|
张淋淋 |
人力 |
1100 |
|
王媛媛 |
人力 |
1160 |
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
方芳 |
财务 |
1400 |
|
李静 |
财务 |
1470 |
|
张萍 |
财务 |
1305 |
|
王晓林 |
部门1 |
1570 |
|
王彤 |
部门1 |
1200 |
步骤二 将（工资表别名）中间结果表每行的部门列上的部门信息和工资金额列上的工资金额为条件传给WHERE 关键字中EXISTS函数要执行的SQL查询语句中，作为查询判断条件。在EXISTS函数中如果有符合条件的中间结果表生成，那么这行数据就放入一个新的中间结构表中。
中间结果表每行都会执行如下过程
|
张淋淋 |
人力 |
1100 |
select *
from 工资表
where 工资表.部门= 人力 行中的部门名称为嵌套语句的部门条件
group by 工资表.部门
having avg(工资表. 工资金额)> 1100 行中的工资金额为嵌套语句的比较平均工作的表件
1 FROM 将工资表装入一个中间表中。
2 where 将部门为人力的行装入一个中间结果表中（它的条件是上个别名表传入的）。
3 group by 在将2的中间结果表分组以部门为条件，生成一个有复合行的中间结果表。
3 having 将传入的工资金额与中间结果表每行的工资金额列通过平均函数生成的数据进行比较。有符合条件的就加入到一个新的中间结果表中。
4 SELECT 将中间表所有列都放入最终结果表。
5 将这个表表达式反给EXISTS函数，如果表表达式不为空就说话别名表这行数据符合条件。
步骤二 产生的中间结果表
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
方芳 |
财务 |
1400 |
|
李静 |
财务 |
1470 |
|
王晓林 |
部门1 |
1570 |
步骤三 SELECT 关键字选择所有列，在生成一个最终结果表。
步骤三产生的中间结果表
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
方芳 |
财务 |
1400 |
|
李静 |
财务 |
1470 |
|
王晓林 |
部门1 |
1570 |
步骤四 将信息发送给客户端。
行转列介绍
在很多时候会有一些行转列方面的业务需求。例如
(工资表)
|
名称 |
月份 |
工资金额 |
|
张淋淋 |
1 |
1200 |
|
张淋淋 |
2 |
1210 |
|
张淋淋 |
3 |
1220 |
|
张淋淋 |
4 |
1230 |
|
张淋淋 |
5 |
1240 |
|
李静 |
1 |
1110 |
|
李静 |
2 |
1120 |
|
李静 |
3 |
1130 |
|
李静 |
4 |
1140 |
我们需要通过SQL查询出一个这样的结果表业务。
|
名称 |
1 |
2 |
3 |
4 |
5 |
|
张淋淋 |
1200 |
1210 |
1220 |
1230 |
1240 |
|
李静 |
1110 |
1120 |
1130 |
1140 |
步骤一 通过FROM 产生一个中间结果表。
|
名称 |
月份 |
工资金额 |
|
张淋淋 |
1 |
1200 |
|
张淋淋 |
2 |
1210 |
|
张淋淋 |
3 |
1220 |
|
张淋淋 |
4 |
1230 |
|
张淋淋 |
5 |
1240 |
|
李静 |
1 |
1110 |
|
李静 |
2 |
1120 |
|
李静 |
3 |
1130 |
|
李静 |
4 |
1140 |
步骤二 GROUP BY 以名称为分组条件。得到一个新的中间结果表
|
名称 |
月份 |
工资金额 |
|
张淋淋 |
1 |
1200 |
|
2 |
1210 |
|
|
3 |
1220 |
|
|
4 |
1230 |
|
|
5 |
1240 |
|
|
李静 |
1 |
1110 |
|
2 |
1120 |
|
|
3 |
1130 |
|
|
4 |
1140 |
步骤三 我们通过SELECT 关键字在新的中间结果表中增加5个新列，每个列上的数据都是通过判断函数decode，判断指定条件得到的数据。判断月份，如果复合行中的月份与判断条件相同，数据库会将与这个月份所对应的工资金额拿出来.将它放到新列,新列有可能出现复合行所有我们也需要一个sum聚合函数来把这个列合并成一个标量表达式
中间表创建过程。
|
名称 |
月份 |
工资金额 |
1 |
2 |
….. |
|
张淋淋 |
1 |
1200 |
Decode（月份，1，工资金额）如果月份列等于1，就把于1月份对应的工资金额信息拿到新列中 在sum聚合成一个标量 |
Decode（月份，2，工资金额）如果月份列等于2，就把于2月份对应的工资金额信息拿到新列中 在sum聚合成一个标量 |
|
|
2 |
1210 |
||||
|
3 |
1220 |
||||
|
4 |
1230 |
||||
|
5 |
1240 |
||||
|
李静 |
1 |
1110 |
Decode（月份，1，工资金额）如果月份列等于1，就把于1月份对应的工资金额信息拿到新列中 在sum聚合成一个标量 |
Decode（月份，2，工资金额）如果月份列等于2，就把于2月份对应的工资金额信息拿到新列中 在sum聚合成一个标量 |
|
|
2 |
1120 |
||||
|
3 |
1130 |
||||
|
4 |
1140 |
步骤三 中间结果表
|
名称 |
1 |
2 |
3 |
4 |
5 |
|
张淋淋 |
1200 |
1210 |
1220 |
1230 |
1240 |
|
李静 |
1110 |
1120 |
1130 |
1140 |
步骤四 将中间结果表信息，发送给客户端。
SQL 文
我们需要注意的是，要想在中间结果表中增加或者减少列，只能通过select 关键字来做。也就是说只有在SELECT 这个步骤的时候我们才有机会在中间结果表中有增减列的行为。在现在的SQL标准中还不能做到把GROUP BY 分组产生的复合行列直接在SELECT关键字步骤中遍历为列。只能通过我们自己手写SELECT 关键字部分来达到对中间结构表增减列。所以在行转列的时候，就需要我们事先知道行中的信息，再通过SELECT 关键字把行中的信息拿到列中使用判断函数取出符合条件的复合行,最后再聚合函数将它们生成新的列。注意不管抽取的列信息是不是复合行,都需要用聚合函数合并成标量表达式.
GROUP BY 子查询 WHERE综合应用
现在我们要来解决这个业务。给出每个部门工资排名前3名的员工信息。
(工资表)
|
名称 |
部门 |
工资金额 |
|
张淋淋 |
人力 |
1100 |
|
王媛媛 |
人力 |
1160 |
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
曾飞 |
人力 |
1140 |
|
韩东 |
人力 |
1200 |
|
马林林 |
人力 |
1200 |
|
方芳 |
财务 |
1400 |
|
金树林 |
财务 |
1350 |
|
邢星 |
财务 |
1700 |
|
李静 |
财务 |
1470 |
|
张萍 |
财务 |
1400 |
|
王晓林 |
后勤 |
1570 |
|
王彤 |
后勤 |
1200 |
|
李文 |
后勤 |
1100 |
这个表中在相同部门下有很多人的工资金额是相同的。我们先以部门和工资金额为分组条件生成一个中间结果表。
中间结果表
|
名称 |
部门 |
工资金额 |
|
张淋淋 |
人力 |
1100 |
|
王媛媛 |
人力 |
1160 |
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
曾飞 |
人力 |
1140 |
|
韩东 |
人力 |
1200 |
|
马林林 |
||
|
方芳 |
财务 |
1400 |
|
张萍 |
||
|
邢星 |
财务 |
1700 |
|
李静 |
财务 |
1470 |
|
金树林 |
财务 |
1350 |
|
王晓林 |
后勤 |
1570 |
|
王彤 |
后勤 |
1200 |
|
李文 |
后勤 |
1100 |
我们将名称使用SELECT 关键字将名称列删除。得到这样一个中间结果表
|
部门 |
工资金额 |
|
人力 |
1100 |
|
人力 |
1160 |
|
人力 |
1230 |
|
人力 |
1170 |
|
人力 |
1140 |
|
人力 |
1200 |
|
财务 |
1400 |
|
财务 |
1700 |
|
财务 |
1470 |
|
财务 |
1350 |
|
后勤 |
1570 |
|
后勤 |
1200 |
|
后勤 |
1100 |
我们可以在这样的中间结果表中使用WHERE 关键字以部门和工资金额为判断条件。只要我们给出人员所在部门名称和工资金额，就可以知道的它在这个部门工资排在第几位。部门和工资金额这写判断条件,可以以其它中间结果表获得.
判断SQL文
步骤一 通过FROM 关键子将工资表中的信息放入一个中间结果表中。
步骤一 产生的中间结果表
|
名称 |
部门 |
工资金额 |
|
张淋淋 |
人力 |
1100 |
|
王媛媛 |
人力 |
1160 |
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
曾飞 |
人力 |
1140 |
|
韩东 |
人力 |
1200 |
|
马林林 |
人力 |
1200 |
|
方芳 |
财务 |
1400 |
|
金树林 |
财务 |
1350 |
|
邢星 |
财务 |
1700 |
|
李静 |
财务 |
1470 |
|
张萍 |
财务 |
1400 |
|
王晓林 |
后勤 |
1570 |
|
王彤 |
后勤 |
1200 |
|
李文 |
后勤 |
1100 |
步骤二 WHERE 3>SQL语句中，如果这个SQL返回的表表达式不为空，就将这行数据放入一个中间结构表中。我们将每行中的部门名称和工资金额为条件放入到上面的判断SQL文中来查询这个员工工资在部门排在第几位。
|
张淋淋 |
人力 |
1100 |
SELECT COUNT(*)
FROM(
SELECT 部门，工资金额
FROM 工资表
GROUP BY 部门，工资金额
) 工资表别名
WHERE 工资表别名.部门=人力
AND 工资表别名.工资金额>1100
中间结果表中有几行数据,我们就需要几次这样的判断.每次这样判断都要使用到行中的部门名称和工资金额为条件，对数据库进行一次SQL语句操作.可以获得这行员工在自己所在部门的工资金额排名位子。在将这个排位为3进行比较大小,如果大于3就将这行信息放入中间结果表中。注意工资表中有几行就需要执行几次SQL查询判断。
步骤二 产生的中间结果表
|
名称 |
部门 |
工资金额 |
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
韩东 |
人力 |
1200 |
|
马林林 |
人力 |
1200 |
|
方芳 |
财务 |
1400 |
|
邢星 |
财务 |
1700 |
|
李静 |
财务 |
1470 |
|
张萍 |
财务 |
1400 |
|
王晓林 |
后勤 |
1570 |
|
王彤 |
后勤 |
1200 |
|
李文 |
后勤 |
1100 |
步骤三 SELECT 关键字对列没有增减把中间结果表传给下个步骤。
步骤三 产生的中间结果表
|
名称 |
部门 |
工资金额 |
|
李冶 |
人力 |
1230 |
|
赵淑 |
人力 |
1170 |
|
韩东 |
人力 |
1200 |
|
马林林 |
人力 |
1200 |
|
方芳 |
财务 |
1400 |
|
邢星 |
财务 |
1700 |
|
李静 |
财务 |
1470 |
|
张萍 |
财务 |
1400 |
|
王晓林 |
后勤 |
1570 |
|
王彤 |
后勤 |
1200 |
|
李文 |
后勤 |
1100 |
步骤四 通过ORDER BY 部门，工资金额 DESC 将信息进行排序。把排序结果放入一个新的中间结果表。
步骤四 产生的中间结果表
|
名称 |
部门 |
工资金额 |
|
李冶 |
人力 |
1230 |
|
韩东 |
人力 |
1200 |
|
马林林 |
人力 |
1200 |
|
赵淑 |
人力 |
1170 |
|
邢星 |
财务 |
1700 |
|
李静 |
财务 |
1470 |
|
方芳 |
财务 |
1400 |
|
张萍 |
财务 |
1400 |
|
王晓林 |
后勤 |
1570 |
|
王彤 |
后勤 |
1200 |
|
李文 |
后勤 |
1100 |
步骤五 将最终结果表中的信息发送给客户端.
给出每个部门工资前3名的SQL文
例 给出员工所在部门是人力又是财会的同时存在2个部门的人员名单.
(人员部门职务表)
|
名称 |
部门 |
职务 |
|
张淋淋 |
人力 |
科员 |
|
张淋淋 |
人力 |
前台 |
|
张淋淋 |
财会 |
科员 |
|
张淋淋 |
后勤 |
科员 |
|
李静 |
人力 |
科员 |
|
李静 |
后勤 |
科长 |
|
李文 |
财会 |
出纳 |
|
李文 |
财会 |
会计 |
|
张萍 |
人力 |
科长 |
|
张萍 |
财会 |
科长 |
|
方芳 |
人力 |
科员 |
|
方芳 |
人力 |
副科长 |
步骤一 FROM关键字将人员部门职务表中的信息放入一个中间结果表中
步骤一 产生的中间结果表
|
名称 |
部门 |
职务 |
|
张淋淋 |
人力 |
科员 |
|
张淋淋 |
人力 |
前台 |
|
张淋淋 |
财会 |
科员 |
|
张淋淋 |
后勤 |
科员 |
|
李静 |
人力 |
科员 |
|
李静 |
后勤 |
科长 |
|
李文 |
财会 |
出纳 |
|
李文 |
财会 |
会计 |
|
张萍 |
人力 |
科长 |
|
张萍 |
财会 |
科长 |
|
方芳 |
人力 |
科员 |
|
方芳 |
人力 |
副科长 |
步骤二 GROUP BY 将人员名称为分组条件,生成一个新的中间结果表.
步骤二 产生的中间结果表中
|
名称 |
部门 |
职务 |
|
张淋淋 |
人力 |
科员 |
|
人力 |
前台 |
|
|
财会 |
科员 |
|
|
后勤 |
科员 |
|
|
李静 |
人力 |
科员 |
|
后勤 |
科长 |
|
|
李文 |
财会 |
出纳 |
|
财会 |
会计 |
|
|
张萍 |
人力 |
科长 |
|
财会 |
科长 |
|
|
方芳 |
人力 |
科员 |
|
人力 |
副科长 |
步骤三 使用 HAVING 关键字来判断那些行符合条件. Decode(部门,人力,1)判断每行的部门列(复合行)中是否有人力存在,如果存在返回1.即使行中出现多个人力部门信息Decode函数也将返回1,在使用同样的方法判断财会部门.在将2个函数的结果相加.通过判断它们的总和是否大于1,大于1就将这行数据放入中间结果表中.
having (sum(decode(部门,'人力',1))+sum(decode(部门,'财会',1)))>1
步骤三 产生的中间结果表中
|
名称 |
部门 |
职务 |
|
张淋淋 |
人力 |
科员 |
|
人力 |
前台 |
|
|
财会 |
科员 |
|
|
后勤 |
科员 |
|
|
张萍 |
人力 |
科长 |
|
财会 |
科长 |
步骤四 SELECT 选择名称列,生成中间结果表
步骤四 产生的中间结果表中
|
名称 |
|
张淋淋 |
|
张萍 |
SQL文
由于时间原因就先写到这里. 大家对上面的知识有什么更好的见解,或者有什么宝贵的意见可以通过邮箱与我进行沟通. 如果大家认为我写的东西不错,对大家还是有帮助的,大家还需要对数据库的SQL文有更深入的了解.我将再写一篇讲解SQL的中级篇. 在此感谢同事老胡和小孟对本文提出的宝贵意见. 本文遵循开源原则,拒绝一切商业用途.