吴声子夜歌的博客

现在有一张全部用户信息表stu_table，这张表存储了id（用户ID）​、name（姓名）​、area（区域）和sex（性别）四个字段，现在我们想知道每个区域有多少用户，该怎么实现呢？stu_table表如下所示。解题思路如下。我们想要获取的是每个区域的用户数，首先需要对区域进行分组，使用的是group by，然后对每个组内的用户进行计数聚合运算，使用的是count，最后运行结果如下表所示。2、查询每个区域的男女用户数本节使用的是1节的全部用户信息表stu_table，现在我们想知道每个区域内男生、

简单地说，索引的工作原理与C语言中的指针数组是一样的，即相比查找复杂对象的数组，查找轻量的指针会更高效（我们可以想象一下最常见的图书目录）​。简单地说，索引的工作原理与C语言中的指针数组是一样的，即相比查找复杂对象的数组，查找轻量的指针会更高效（我们可以想象一下最常见的图书目录）​。在col_1和col_2上分别建立了不同的索引，或者建立了(col_1, col_2)这样的联合索引时，如果使用OR连接条件，那么要么不会用到索引，要么用到了，但是效率比AND要差很多。

在关系模型的数据结构里，并没有“顺序”这一概念。因此，基于它实现的关系数据库中的表和视图的行和列也必然没有顺序。同样地，处理有序集合也并非SQL的直接用途。因此，SQL处理有序集合的方法，与原本就以处理顺序为目的的面向过程语言及文件系统的处理方法在性质上是不同的。具体来说，就是SQL使用了谓词逻辑中的量词和定义有序数的递归集合。不过，自20世纪90年代末SQL中引入窗口函数后，SQL处理有序数据就变得非常简单了。

四则运算里的和(UNION)、差(EXCEPT)、积(CROSSJOIN)都被引入标准SQL了，但是很遗憾，商(DIVIDE BY) 因为各种原因迟迟没能标准化（具体原因请参考1-6节里的解释）​。因此，现阶段我们需要自己写SQL语句来实现除法运算。

而且，随着表头列数的增加，性能也会恶化。但是，练习用SQL做这样的工作是本节的主旨，所以我们尝试用外连接的思路来思考，这样就可以知道，以侧栏（员工姓名）为主表进行外连接操作就可以生成表。这个查询的意思是，用表Items减去表ShopItems里各个店铺的商品，如果结果是空集，则说明该店铺库存里有表Items里的全部商品。外连接会增加只在商品主表里存在的40号商品DVD的行，所以严格地讲，行数并非没有增加，但这不会导致表SalesHistory里已有的10号或20号商品的行异常增加，进而引起结果异常。

在SQL中，过去的做法通常是在表Sales的基础上，再加一张存储了上一年数据的表(S2)，然后使用关联子查询进行比较​。造成这种差异的原因是，关联子查询总是连接多张表（即使自连接中的表在物理层面上是同一张表）​，然后使用SQL的旧功能来进行行间比较，而窗口函数是基于“行的顺序”进行操作的，直接将面向过程语言的循环操作引入到SQL中。关联子查询的结果只表明存在重叠这一实际情况，而窗口函数的结果则表明水谷和内田的住宿时间是重叠的（实际上水谷和山本的住宿时间也是重叠的，但由于结果只显示2列，所以并未显示出来。

用一句话来概括使用HAVING子句时的要点，就是要搞清楚将什么东西抽象成集合。前面我们看过的例题，其实都是把各种各样的实体当作集合来处理了，其中有像数列、班级、队伍这样本身就容易看作集合的实体，也有像店铺、生产地这样本身是原子性元素的实体，这些都被当作集合来处理了。大家需要理解的是，在SQL中一件东西能否抽象成集合，和它在现实世界中的实际意义无关，只取决于它在表中的存在形式。根据需要，我们可以把实体抽象成集合，也可以把它抽象成集合中的元素。

SQL的保留字中，有很多被归为谓词一类。例如，​“=、<、>”等比较谓词，以及BETWEEN、LIKE、IN、ISNULL等。在写SQL语句时我们几乎离不开这些谓词，那么到底什么是谓词呢？几乎每天都在用，但是突然被问起来时却答不上来的人应该不少吧。当然，我们说的谓词和主语/谓语中的谓语，以及英语中的动词是不一样的。实际上，谓词是一种特殊的函数，其返回值是真值。前面提到的每个谓词，返回值都是 true、false或者 unknown（一般的谓词逻辑里没有 unknown，但是SQL采用的是三值逻辑，因此具有三

编程语言大多是基于二值逻辑的，即逻辑真值只有真和假两个，而SQL语言则采用一种特别的逻辑体系——三值逻辑，即逻辑真值除了真和假，还有第三个值“不确定”​。三值逻辑经常会带来一些意想不到的情况，这让程序员很是烦恼。总之，数据库里只要存在一个NULL，查询的结果就可能不正确。而且，一般没有办法确定具体是哪个查询返回了不正确的结果，所以所有的结果看起来都很可疑。没有谁能保证一定能从包含NULL的数据库里查询出正确的结果。大多数编程语言包括布尔型（BOOL型、BOOLEAN型）这种数据类型。

自连接经常和非等值连接结合起来使用。自连接和GROUP BY结合使用可以生成递归集合。将自连接看作不同表之间的连接更容易理解。用逻辑而非物理的方法来思考。在使用数据库制作各种票据和统计表的工作中，我们经常需要按分数、人数或销售额等对数值进行排序。现在，我们要按照价格从高到低的顺序，对下面这张表里的商品进行排序。价格相同的商品位次也一样，在它们后一位的商品有两种排序方法，一种是跳过之后的位次，另一种是不跳过之后的位次。在现在的SQL中，如果使用窗口函数，可以像下面这样轻松实现。

首先，初次见到窗口函数的人会觉得这个名字有些不可思议。大家可能会想象这个函数是使用了某种类似于“窗口”的东西，但看到语法示例时，却发现并没有哪句代码表明了“这是窗口”​，只看到那些使用了PARTITION BY子句或ORDER BY子句的查询示例。我们来看一下窗口函数的典型用例——计算移动平均值的语法的示例。INSERT INTO Products VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2009-09-20');

CASE表达式是从SQL-92标准开始被引入的。它虽然已经被引入了二十多年，但在主流DBMS中仍然可以正常使用。不过，可能因为它是相对较新的技术，所以尽管使用起来非常便利，但人们（尤其是初级者）并不怎么理解其真正的价值。很多人不用它，或者用它的简略版函数，例如DECODE(Oracle)、IF(MySQL)等。然而，正如著名的SQL专家乔·塞尔科所说，CASE表达式也许是SQL-92标准里加入的最有用的特性。如果能用好它，那么SQL能解决的问题就会更广泛，写法也会更加漂亮。

问题：你想创建一张交叉报表，将结果集中的行转换为列。你熟悉传统的转置方法，但想尝试点儿新鲜的东西。具体地说，你想在不使用 CASE 表达式和连接的情况下，返回如下结果集。解决方案：使用 PIVOT 运算符生成所需的结果集，而不使用 CASE表达式或额外的连接操作。2、使用SQL Server运算符UNPIVOT逆转置交叉报表问题：你有一个转置得到的结果集（或事实表）​，你想对其进行逆转置。例如，当前结果集为 1 行 4 列，而你想将其转换为 4 行 2 列。对于上一节实例生成的结果集：你想将其转换成

如果EMP 建立的是递归层次结构，那么根节点将指向自己（也就是说，员工 KING 的 MGR 值将为他的 EMPNO）​。​（对于特定的员工，其管理者之上可能还有管理者，而这些管理者之上也有管理者，以此类推，形成 n 层层次结构。在本实例中，叶子节点指的是不是管理者的员工，分支节点指的是自己是管理者且还有上级管理者的员工，而根节点指的是没有上级管理者的员工。：你想在返回子记录中数据的同时，返回父记录中的信息。：你想找出 JONES 的所有下属，包括直接下属和间接下属（JONES 的下属的下属）​。

这张报表包含不同 DEPTNO 和 JOB 组合的薪水小计，不同 DEPTNO 的薪水小计，不同 JOB 的薪水小计以及整个EMP 表的薪水总计，而你想清晰地指出不同的聚合等级。换言之，你想标出各个聚合值所属的类别，即 SAL 列值表示的是不同 DEPTNO 的小计，不同 JOB 的小计，还是总计？另外，你还想返回两种小计：每个部门由管理者管理的员工的薪水总和；一个这样的结果集既包含 EMP 表中每种职位的薪水总额，也包含 EMP 表中所有薪水的总额，其中每种职位的薪水总额为小计，所有薪水的总额为总计。

这意味着对于薪水最高的员工，应将其 FORWARD 值设置为表中最低的薪水值，而对于薪水最低的员工，应将其 REWIND 值设置为表中最高的薪水值。如果你的目标是将每位员工的 SAL 值与下一位获聘的员工的 SAL 值进行比较，就需要将同一天获聘的其他员工排除在外，为此可以使用如下解决方案。换言之，你想将前面的结果集转换为下面的结果集。：你想返回部门编号为 10 和部门编号为 20 的所有员工的名字及其所属部门的信息，以及部门编号为 30 和部门编号为 40 的部门信息（但不返回这些部门的员工信息）​。

所谓表示一系列连续项目的行，指的是除第一行外，其他各行的 PROJ_START 都与其前一行的 PROJ_END 相等（这里的前一行，指的是 PROJ_ID 比当前行小 1 的行）​。请看前面要返回的最终结果集的第 1 行，其PROJ_START 为视图 V 中 PROJ_ID 1 的PROJ_START，而 PROJ_END 为视图 V 中 PROJ_ID 4的 PROJ_END。使用 LAG OVER 来判断当前行的 PROJ_START是否与前一行的 PROJ_END 相等，可以将所有的行分成多组。

问题：你想找出当前月份的第一天和最后一天。解决方案：下面介绍的解决方案找出了当前月份的第一天和最后一天，但只要稍做调整，就可用于找出任何月份的第一天和最后一天。DB2：使用函数 DAY 确定当前日期是当前月份的第几天，然后将当前日期减去这个天数并加 1，得到当前月份的第一天。为了得到当前月份的最后一天，给当前日期加上 1 个月，再减去将函数 DAY 应用于当前日期加 1 个月时返回的天数。from t1;Oracle。

例如，你想根据员工 CLARK 的 HIREDATE，返回 6 个日期：CLARK 获聘前 5 天和后 5 天，CLARK 获聘前 5个月和后 5 个月，CLARK 获聘前 5 年和后 5 年。：首先使用内置函数 GENERATE_SERIES 为一年中的每一天都生成一行数据，然后使用函数 TO_CHAR 确定各个日期都是星期几，最后计算工作日出现的次数。：要解决这个问题，诀窍是找出当前员工的 HIREDATE 后最早的 HIREDATE，然后，使用 2 节介绍的技巧确定相隔的天数即可。

然而，将列名作为函数 COUNT 的参数时，计算的是该列值非 NULL 值的个数。需要指出的是，如果出现分不出胜负的情况，即多个薪水值出现的次数都是最多的，那么使用 KEEP 的解决方案将只返回最高的薪水。我们选择的是简单的三点移动平均，但根据要分析的数据的特征，也可以使用不同的公式和不同的数据点数，以充分发挥这种方法的作用。在本例中，要计算 10 号部门的薪水占 EMP 表中薪水总额的百分比，只需先找出 10 号部门的薪水总额，将其除以整张表的薪水总额，再乘以 100 得到百分比值。

最后一个操作是用长度差值除以查找的字符串的长度，仅当要查找的字符串的长度大于 1 时，这个除法运算才是必不可少的。当前，大多数 DBMS 提供了专门为串接字符串而设计的函数，比如 MySQL 的函数 GROUP_CONCAT（最早的函数之一）或 SQL Server 的函数 STRING_ADD（SQLServer 2017 中新增的）​。与本章的其他实例一样，诀窍是使用函数TRANSLATE 将多种字符转换为特定的字符，这样无须搜索多个数字或字符，而只需搜索表示所有数字或字符的字符。

下面显示的输出是在我的笔记本计算机的 Oracle 实例中执行这些查询时得到的，你执行这些查询时，返回的结果集肯定不一样。暴露有关自己的信息时，数据库使用的正是你为自己的应用程序创建的机制——表和视图，这有点儿循环的“味道”​。查询 INFORMATION_SCHEMA.KEY_COLUMN_USAGE、INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS、INFORMATION_SCHEMA.COLUMNS 和PG_CATALOG.PG_INDEXES。

问题：你想创建一张新表，该表包含的列与一张既有表相同。例如，你想创建 DEPT 表的一个副本，并将其命名为DEPT_2。但是，你不想复制表中的行，而只想复制表的列结构。解决方案DB2：在 CREATE TABLE 命令中使用 LIKE 子句。Oracle、MySQL 和 PostgreSQL：在 CREATE TABLE 命令中使用一个不返回任何行的子查询。asselect *from deptSQL Server：在一个不返回任何行的子查询中使用 INTO 子句。select *

可惜相等连接无法让你知道哪个部门没有员工，因为在 EMP 表和 DEPT 表之间建立相等连接时，将返回满足连接条件的所有行，而你想知道的是DEPT 表中不满足连接条件的行。先将 EMP 表连接到 DEPT 表，以返回所有的员工及其所在的部门，然后外连接到 EMP_BONUS表，以返回员工获得奖金的日期。连接是一种将两张表中的行合并的操作，而相等连接是基于相等条件（比如一张表的部门编号与另一张表的部门编号相等）的连接。内连接是最基本的连接，它返回的每一行都包含来自参与连接查询的各张表的数据。

要按字符串的最后两个字符排序，可以先找到该字符串的末尾位置（字符串的长度）并减去 1。对于列值为NULL 和列值不为 NULL 的行，如果要以不同的方式排列它们（例如，你要按升序或降序排列列值不为 NULL 的行，并将所有列值为 NULL 的行都放在最后面）​，那么可以使用 CASE 表达式根据不同的情况采用不同的排序方式。除非你使用的 RDBMS 像 Oracle 那样，提供了方便的途径，无须修改不为 NULL 的列值，就能轻松地将列值为NULL 的行放在最前面或最后面，否则就需要创建一个辅助列。

这是由于 WHERE 子句是在 SELECT 子句之前评估的，因此，对于前面说明问题时列举的查询，在评估其中的 WHERE 子句时，还没有别名 SALARY 和 COMMISSION。例如，在生成结果集时，你想在员工的薪水不超过 2000美元时返回消息 UNDERPAID，在员工的薪水不低于4000 美元时返回消息 OVERPAID，在员工的薪水为2000~4000 美元时返回消息 OK。在 ORDERBY 子句中，使用该内置函数以随机的方式对行进行排 序，然后使用上一节介绍的方法来限制返回的行数。

1、平台和版本2、使用的表。