openGauss开源数据库实战二十

已于 2024-12-13 19:18:33 修改
阅读量1.1k 收藏 12
点赞数 21
分类专栏： 数据库实战 文章标签： 数据库 开源 centos gaussdb
于 2024-12-10 00:20:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_73321443/article/details/144330529
版权

文章目录

任务二十 openGauss SELECT语句

任务目标

掌握SQL语言中SELECT语句的用法。

实施步骤

本任务采用《数据库系统概念(原书第6版)》(1SBN978-7-111-37529-6)中的大学数据库表和数据集。所有的查询都可以在openGauss数据库下运行。

一、一个完整的SELECT语句

一条完整的SELECT语句,包括以下部分:

SELECT columnlist
FROM tablelist
WHERE condition
GROUP BY columnlist
HAVING condition
ORDER BY columnlist

其执行顺序如下:
1)首先根据FROM子句计算出一个关系(表的广义笛卡儿积或者各种表连接)。
2)如果有WHERE子句,将WHERE子句中的谓词应用到FROM子句的结果关系上。
3)如果有GROUPBY子句,将满足WHERE谓词的元组通过GROUPBY子句形成分组;如果没有GROUPBY子句,将满足WHERE谓词的所有元组作为一个分组。
4)如果有HAVING子句,将HAVING子句的谓词应用到每个分组上进行分组过滤,不满足HAVING子句谓词的分组将被抛弃。
5)最后,SELECT子句利用剩下的分组,通过投影或者在每个分组上应用聚合函数,产生查询结果中的元组。

二、SQL语句的注释

SQL语句的注释可以是C语言的多行注释风格(使用/*…*/),也可以使用两根短线进行单行注释。使用Linux用户omm,在终端窗口执行如下命令进行测试:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
/*
 * 在SQL中可以使用C语言的注释风格！
 * 这是一个单表查询的例子。
 * 使用*表示显示instructor表的所有列的信息
 */
-- 也可以使用两个短线进行单行注释
-- 两个短线之后的内容表示单行注释
SELECT *            --  单行注释，*表示所有的列
FROM instructor;

三、单表查询

1.SELECT子句

SELECT子句可以完成关系代数的投影运算。单表查询中的SELECT子句,对FROM子句中列出的单个表从表的纵向进行数据过滤:只显示SELECT子句所列出的表属性,未被SELECT子句列出的属性列都被扔掉了。
执行下面的语句,在表instructor中找出所有教师的名字:

/* 只显式instructor表的name这一列的信息 */
SELECT name 
FROM instructor; 
/* 查看instructor表的多列信息：属性列ID和属性列name */
SELECT ID,name
FROM instructor;
/* SELECT子句中，使用通配符*来表示某个表的所有列 */
SELECT *
FROM instructor;
/* 建议这样写语句，将所有的列名明确地写出来 */
SELECT ID,NAME,DEPT_NAME,SALARY
FROM instructor;
/*  SELECT子句中的计算字段 */
SELECT ID,NAME,DEPT_NAME,SALARY,SALARY*1.1
FROM instructor;
/*  SELECT子句中的计算字段及其别名 */
SELECT ID,NAME,DEPT_NAME,SALARY,SALARY*1.1 AS NEW_SALARY
FROM instructor;
/* SELECT语句的查询结果集中可以有重复的记录行 */
SELECT DEPT_NAME
FROM instructor;
/* SELECT语句的查询结果集中可以有重复的记录行，使用ALL确认显示这些重复的行 */
SELECT ALL DEPT_NAME
FROM instructor;
/* 去除SELECT查询结果集中重复的记录行 */
SELECT DISTINCT DEPT_NAME
FROM instructor;
2.WHERE子句中的谓词

SELECT语句的WHERE子句完成关系代数的选择运算,主要的作用是过滤掉一些不满足WHERE子句中谓词条件的记录行。

(1)比较谓词(比较运算符)和逻辑连接词(布尔运算符)单个比较运算表达式是最简单的谓词条件。在WHERE子句中使用的比较运算符及其含义见下图。

在这里插入图片描述
执行下面的查询， 查找在计算机系工作的教师的名字 ：

SELECT name
FROM instructor
WHERE dept_name='Comp. Sci.';

这个查询只用到了单个的简单谓词条件dept_name=’ Comp.Sci.’,用 于判断教师是否工作在计算机系。
复杂谓词条件由多个比较运算表达式通过布尔运算符AND、OR和NOT连接而成。 在WHERE子句中， 可以使用下图中的三个布尔运算符。
在这里插入图片描述
组合使用多个布尔运算符，可以构建复杂的谓词条件。 例如我们想查询在计算机系工作，且工资大于70000的教师：

SELECT name
FROM instructor
WHERE dept_name='Comp. Sci.' AND salary>70000;

在SQL语句中,如果有多个布尔运算符,则NOT先被计算,其次是AND,最后是OR。如果需要改变运算的优先级,可以使用括号来改变布尔运算符的运算优先级。
例如以下的查询语句,查询的是计算机系的教师,或者是电子工程系工资大于70000的教师(计算机系的教师工资低于70000的也满足谓词要求):

SELECT name ,dept_name,salary
FROM instructor
WHERE dept_name='Comp. Sci.'
   OR  dept_name='Elec. Eng.'
  AND salary>70000;

如果我们想查询的是计算机系和电子工程系的教师,且要求他们的工资都超过70000,那么就需要添加括号来完成这个查询:

SELECT name ,dept_name,salary
FROM instructor
WHERE (dept_name='Comp. Sci.' OR dept_name='Elec. Eng.')
  AND salary>70000;

我觉得这里可能是易错处，感觉人们容易认为上面两个SQL查询是等价的情况。
干夫子：可以参考逻辑电路中的截断原理，如果OR先判断成功，那么后面的逻辑式会被截断

studentdb=> SELECT name ,dept_name,salary
FROM instructor
WHERE (1 OR  dept_name='Elec. Eng.')
  AND salary>70000;
   name   | dept_name  |  salary  
----------+------------+----------
 Wu       | Finance    | 90000.00
 Einstein | Physics    | 95000.00
 Gold     | Physics    | 87000.00
 Katz     | Comp. Sci. | 75000.00
 Singh    | Finance    | 80000.00
 Crick    | Biology    | 72000.00
 Brandt   | Comp. Sci. | 92000.00
 Kim      | Elec. Eng. | 80000.00
(8 rows)

Time: 1.305 ms
studentdb=> SELECT name ,dept_name,salary
FROM instructor
WHERE 1 OR  dept_name='Elec. Eng.' 
  AND salary>70000;
    name    | dept_name  |  salary  
------------+------------+----------
 Srinivasan | Comp. Sci. | 65000.00
 Wu         | Finance    | 90000.00
 Mozart     | Music      | 40000.00
 Einstein   | Physics    | 95000.00
 El Said    | History    | 60000.00
 Gold       | Physics    | 87000.00
 Katz       | Comp. Sci. | 75000.00
 Califieri  | History    | 62000.00
 Singh      | Finance    | 80000.00
 Crick      | Biology    | 72000.00
 Brandt     | Comp. Sci. | 92000.00
 Kim        | Elec. Eng. | 80000.00
(12 rows)

Time: 0.616 ms

在这里插入图片描述

(2)谓词中的NULL和三值逻辑NULL不是值,因为任何域都有NULL,如果NULL是值,那么请问NULL属于什么数据类型呢?因此,NULL只是一个标记符号。

NULL不是0,也不是空格或者由多个空格组成的空字符串,理解这些很重要。在数据库中,NULL用于表示现实世界中的三种含义。
NULL的第一种含义是不存在或者不适用。一个简单的理解是汽车眼睛的颜色。汽车怎么会有眼睛呢?你根本没法回答这个问题。又例如,表格上有一项是大学毕业学校,没上过大学的人就没法填写这项,只能不填,空在那儿。
NULL的第二种含义是值存在但是暂时不知道,或者是值存在但是暂时不想提供。一个简单的理解就是墨镜下眼睛的颜色。眼睛肯定有颜色,只是被墨镜遮挡住了,暂时无法知道。又例如,表格中有一项是收人情况,有些人不想填,所以就空着了。
NULL的第三种含义是值存不存在不知道,表示的含义介于前面两种含义之间:如果值不存在,就是不适用;如果值存在,就是不想提供。一个容易理解的例子是你家新房的电话号码:如果新房没有安装电话,那就是第一种含义——不存在,不适用;如果新房安装了固定电话,那就是第二种含义——存在但是不想提供。
正是因为NULL表示的现实世界的物理意义不唯一,引入NULL后带来了许多麻烦。引入NULL之后的逻辑是三值逻辑,真值表中不再是只有TRUE和FALSE,还可能是UNKNOWN。

引入NULL带来的第一个麻烦是:如果算术表达式中的任何一个输入值为NULL,则该算术表达式的计算结果一定为空值。例如,某个教师的salary值为NULL,算术表达式salary+1000的结果将是NULL。我们可以执行如下的SQL语句进行测试:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
\set AUTOCOMMIT off
INSERT INTO instructor(ID,NAME,DEPT_NAME,SALARY)
                      VALUES('88888','ZQF','Comp. Sci.',NULL);
SELECT name,salary,salary+1000 AS NewSalary
FROM instructor;
ROLLBACK;
\q

输出显示,名字叫作ZQF的教师,其工资值为NULL(NULL+1000=NULL)。
引人NULL带来的第二个麻烦是:比较表达式中如果含有NULL,其结果将是UNKNOWN。这就是引入三值逻辑的原因。下面是引入UNKNOWN后的三值逻辑真值计算规则:
在这里插入图片描述
理解NULL=NULL和NULL>NULL:两个NULL无法进行比较,比较两个空值没有意义,因为你不知道第一个NULL是三种情况中的哪一种,同样也不知道第二个NULL是三种情况中的哪一种。

理解NOT(5<NULL):5<NULL的结果是UNKNOWN,那么NOT(5<NULL)等价于NOTUNKNOWN,直观地理解,NOT UNKNOWN 就是 UNKNOWN。

数据库中含有NULL,因此SQL语言是基于三值逻辑的。在处理上,如果SQL语句中WHERE子句的谓词结果为UNKNOWN,则被当作FALSE来处理。

引入三值逻辑后,现实世界中非常自然的判断,在SQL世界就造成了很大的麻烦。例如在现实世界中,一个人的年龄要么是20岁,要么不是20岁,但是在数据库中,情况就不是这样了：一个人的年龄有三种可能,即是20岁、不是20岁和年龄不知道。我们可以做一个测试来说明这一点。
首先执行下面的命令和SOL语句,准备测试数据集:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
DROP TABLE IF EXISTS person;
CREATE TABLE person(name character(30),age int);
INSERT INTO person VALUES ('zhang',20);
INSERT INTO person VALUES ('li',25);
INSERT INTO person VALUES ('wang',null);
\q

执行下面的命令和SQL语句,进行测试:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
SELECT * FROM person;
SELECT * FROM person where age=20 or age!=20;
drop table person;
\q

在这里插入图片描述

这个查询语句的谓词是age=20 or age!=20,一个人的年龄是20岁和不是20岁,都满足这个谓词。但是名字叫wang的这个人,其年龄为NULL,NULL既不会等于20,也不会不等于20,因此谓词计算的结果为UNKNOWN,被当作FALSE来处理,因而wang这一行被排除在结果集之外了。
下面的例子再次显示了UNKNOWN被当作FALSE来处理:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
\set AUTOCOMMIT off
INSERT INTO instructor(ID,NAME,DEPT_NAME,SALARY) VALUES('88888','ZQF','Comp. Sci.',NULL);
SELECT name,salary FROM instructor;
SELECT name,salary
FROM instructor
WHERE salary<50000;
rollback;
\q

在这个例子中,教师ZQF的salary值为NULL,因此判断教师ZQF的工资是否大于50000时,其真值为UNKNOWN,在SQL语句中被当作FALSE来处理了,因此该行被排除在结果集之外。

在SQL语言中,如果要判定 WHERE子句中的一个谓词p的结果是不是UNKNOWN,需要使用谓词p is unknown或者p is not unknown来判断。

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
\set AUTOCOMMIT off
INSERT INTO instructor(ID,NAME,DEPT_NAME,SALARY) 
             VALUES('88888','ZQF','Comp. Sci.',NULL);
SELECT name,salary FROM instructor;
SELECT name,salary
FROM instructor
WHERE (salary<50000) is unknown;
rollback;
\q

因为教师ZQF的工资值是NULL,NULL<50000的真值为UNKNOWN,谓词(NULL<50000) is unknown为真。
判断一个值是不是NULL,不能使用=或者!=,必须使用ISNULL或者ISNOTNULL。下面进行测试。首先准备好测试数据,我们插入两个名叫ZQF的教师,教师工号不同,工资值都是NULL:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
\set AUTOCOMMIT off
INSERT INTO instructor(ID,NAME,DEPT_NAME,SALARY)
           VALUES('66666','ZQF','Comp. Sci.',NULL);
INSERT INTO instructor(ID,NAME,DEPT_NAME,SALARY)
           VALUES('88888','ZQF','Comp. Sci.',NULL);

SELECT    salary   FROM instructor WHERE salary = null;
SELECT    salary   FROM instructor WHERE salary != null;

我们发现没有任何记录被查询到,原因是判断数据库中某个表下某行的某列的值是否为NULL,不能使用“=”或者“!=”进行判断比较,否则比较结果永远为UNKNOWN,UNKNOWN会被当成FALSE来处理,因此不显示任何的行。
执行下面的SQL语句,测试谓词IS NULL:

SELECT id,name,salary FROM instructor WHERE salary IS NULL;

执行下面的SQL语句,测试谓词IS NOT NULL:

SELECT id,name,salary FROM instructor WHERE salary IS NOT NULL;

这两个语句中,NULL被正确地进行了测试(使用ISNULL或者ISNOTNULL),因此结果如预期的一样。
关于查询结果集中的NULL,我们来看下面的两个查询:

SELECT name,salary FROM instructor WHERE salary IS NULL;
SELECT DISTINCT name,salary FROM instructor WHERE salary IS NULL;

如果在SELECT子句中使用关键字DISTINCT,结果集中的两条记录(ZOF,NULL)和(ZQF,NULL)将被认为是相同的记录。也就是说,如果记录(更严格的话可以称为元组)在所有的属性上取值相等,即使某些属性值是NULL,我们也认为这些记录是相同的记录(元组)。这个规则同样适用于集合的并、交、差等集合运算。
执行下面的回滚语句,回滚事务,恢复表instructor的数据:

rollback;
\q
(3)谓词IN和NOTIN如果要和一个列表(List)进行比较,则必须使用谓词IN或者NOT IN

例如,我们想找出属于Comp.Sci.系和Finance系的教师,并显示他们的相关信息,那么我们可以用下面的查询来完成这个任务:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
\set AUTOCOMMIT off
SELECT * 
FROM instructor
WHERE dept_name IN ('Comp. Sci.','Finance');

例如,我们想找出除Comp.Sci.系和Finance系之外的其他教师,并显示他们的相关信息,那么可以用下面的查询来完成这个任务:

SELECT * 
FROM instructor
WHERE dept_name NOT IN ('Comp. Sci.','Finance');

我们可以查看一下所有教师的情况:

SELECT * FROM instructor;

可以看出,使用IN(Comp.Sci.Finance)和NOT IN(Comp.Sci.Finance)的查询结果的并集会等于所有教师。但是请注意,这只是在表instructor的dept_name列中没有NULL的记录时才成立。当dept_name列中有NULL时,NOT IN和IN查询结果的并集不是所有的,不会包含值为NULL的行。下面的实验证明了这个事实。

执行下面的SQL语句,插人一条在dept_name属性上值为NULL的记录行,并检查插人后所有记录的情况:

INSERT INTO instructor(ID,NAME,DEPT_NAME,SALARY) VALUES('88888','ZQF',NULL,88888);
SELECT * FROM instructor;

当前表instructor中记录了13个教师的情况,其中教师工号为88888、名字为ZQF的教师,其工作的系名不知道,其他教师的系名都是知道的。
执行下面的两个查询:

--  查询不在Comp. Sci. 系和Finance系的教师：
SELECT * FROM instructor WHERE dept_name NOT IN ('Comp. Sci.','Finance');
--  查询  在Comp. Sci. 系和Finance系的教师
SELECT * FROM instructor WHERE dept_name IN ('Comp. Sci.','Finance') ;

我们也可以使用集合运算符UNION将上述两个结果集合并在一起:

(SELECT * FROM instructor WHERE dept_name NOT IN ('Comp. Sci.','Finance') )
UNION
(SELECT * FROM instructor WHERE dept_name IN ('Comp. Sci.','Finance') );

从上面3个查询的输出可以看到,谓词NOTIN贡献了7条记录,谓词IN贡献了5条记录,这两者之和一共是12条记录,表instructor共有13条记录,谓词NOTIN和IN查询结果的并集并不是全集。这是因为教师工号为88888、名字为ZQF的教师,其dept_name列的值是NULL,被排除在谓词NOTIN和IN的查询结果之外。这是NULL带来的另外一个麻烦。
总结一下:在有NULL的情况下,谓词NOT IN和IN的查询结果的并集不是全集,这两者都排除了有值是NULL的情况;在没有NULL的情况下,谓词NOT IN和IN的查询结果的并集就是全集。
执行下面的回滚语句,回滚事务,恢复表instructor的数据:

rollback;
\q
(4)谓词BETWEENxANDy使用谓词BETWEENxANDy可以进行范围查询。请注意,

在谓词BETWEEN x AND y中,x一定要小于y。如果x大于y,谓词BETWEEN x AND y的真值将为FALSE。
执行下面的SQL语句,测试谓词BETWEEN x AND y中x<y时的情况:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
SELECT name,salary FROM instructor WHERE salary BETWEEN 80000 AND 92000;
SELECT name,salary FROM instructor WHERE salary>=80000 AND salary<=92000;
SELECT name,salary FROM instructor WHERE salary>=80000 AND salary<92000;

从输出结果可以看出,谓词BETWEEN 80000 AND 92000等价于salary>=80000 AND salary<=92000,包括上限和下限值本身。
也可以使用否定形式NOT BETWEEN x AND y,同样要求x<y。执行下面的SQL语句,测试
谓词NOT BETWEEN x AND y中x<y时的情况:

SELECT name,salary,salary FROM instructor WHERE salary NOT BETWEEN 80000 AND 92000;

执行下面的SQL语句,测试谓词BETWEEN x AND y中x>y时的情况:

SELECT name,salary FROM instructor WHERE salary BETWEEN 92000 AND 80000;

当x>y时,谓词BETWEEN x AND y的真值为FALSE,谓词NOT BETWEEN x AND y的真值为NOT FALSE,等价于TRUE,对于表instructor的每一行数据,谓词NOT BETWEEN x AND y的真值都是TRUE,因此会显示表instructor的全部行。

(5)谓词LIKE使用谓词LIKE可以进行模糊匹配查找。在谓词LIKE中,使用以下两个通配符:

1)百分号%:匹配任意长度的字符串。
2)下划线_:匹配任意1个字符。
使用谓词LIKE进行字符串模式匹配,是大小写敏感的,也就是说,同一字符的大写和小写
不会相互匹配。例如:
Intro%匹配任何以“Intro”开头的字符串。
%Comp%匹配任何包含子串“Comp”的字符串。
___匹配任何含有3个字符的字符申。
___%匹配任何至少有3个字符的字符串。
执行下面的语句,找出在楼名中含有“Watson”字符子串的大楼办公的系:

SELECT dept_name FROM department WHERE building LIKE '%Watson%';

也可以使用否定形式的LIKE谓词NOT LIKE,下面进行测试:

SELECT dept_name  FROM department WHERE building NOT LIKE '%Watson%';

如果匹配的字符串中含有反斜杠()、百分号(%)或者下划线(_),那么需要使用转义字符。测试前首先构建测试数据集并查看此时表test 中的测试数据::

DROP TABLE IF EXISTS test;
CREATE TABLE test ( col1 varchar(20));
insert into test values('ab%cd12345'); 
insert into test values('ab%de67890'); 
insert into test values('ab\\cd67890'); 
insert into test values('ab_ef67890'); 
insert into test values('ab1'); 
insert into test values('ab12'); 
SELECT col1  FROM test;

接下来执行下面的SQL语句进行测试:

SELECT col1 FROM test WHERE col1 LIKE 'ab\%cd%';  -- 表示匹配以ab%cd开头的字符串
SELECT col1 FROM test WHERE col1 LIKE '___';      -- 表示匹配长度为3个字符的字符串
SELECT col1 FROM test WHERE col1 LIKE 'ab\_%';    -- 表示匹配以ab_  开头的字符串
SELECT col1 FROM test WHERE col1 LIKE 'ab\\\\%';  -- 表示匹配以 ab\\  开头的字符串
DROP TABLE IF EXISTS test;
\q
3.ORDERBY子句

使用ORDERBY子句可以对查询的结果集进行排序。
在进行实验前,首先插人一条记录:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
\set AUTOCOMMIT off
INSERT INTO instructor(ID,NAME,DEPT_NAME,SALARY)
           VALUES('88888','ZQF','Comp. Sci.',NULL);

执行下面的SQL语句,按教师工资高低的顺序,显示教师的信息:

SELECT * FROM instructor ORDER BY salary;
SELECT * FROM instructor ORDER BY salary ASC;

比较以上两条语句的输出可以看出,ORDER BY子句默认按升序排序,而且把NULL排在后面。如果想让NULL排在前面,可以使用nulls first:

SELECT * FROM instructor ORDER BY salary ASC nulls first;

也可以在ORDERBY子句中指定以降序方式来排列输出结果集:

SELECT * FROM instructor ORDER BY salary DESC;

从上条语句的输出可以看出,ORDERBY子句如果按降序方式排序,则把NULL放在了最前面。如果想让NULL排在后面,可以使用nulls last:

SELECT * FROM instructor ORDER BY salary DESC nulls last;

下面的语句演示了字符串排序,按教师的名字排序显示教师的信息。

SELECT * FROM instructor ORDER BY name;

也可以按多个列来进行排序。执行下面的语句,先按教师所在的系名升序排序,再按教师的工资降序排序,显示教师的信息:

SELECT * FROM   instructor ORDER BY dept_name ASC, salary DESC;

这个查询也可以通过指定表列的序号(数字),而不是表列的名字来进行排序。序号值由SELECT语句的SELECT子句中所列出列名的顺序号确定。下面是第一个例子:

SELECT id,name,dept_name,salary FROM  instructor ORDER BY 3 ASC, 4 DESC;

在这个查询语句的SELECT子句(SELECT id,name,dept_name,salary)中,dept_name列是第3列,salary列是第4列,因此指定ORDERBY 3 ASC,4 DESC,就是先按deptname列升序排序,再按salary列降序排序。
下面是第二个例子:

SELECT id,name,salary,dept_name FROM instructor ORDER BY 4 DESC,3 ASC;

在这个查询语句的SELECT子句(SELECTid,name,salary,dept_name)中,dept_name列是第4列,salary列是第3列,因此指定ORDERBY4ASC,3DESC,也是先按deptname列升序排序,再按salary列降序排序。
在继续下面的实验之前,通过回滚恢复测试数据集:

rollback;
\q
4.GROUPBY子句和HAVING子句
(1)GROUPBY子句和聚合函数GROUPBY子句总是和SQL语言的聚合函数相关联。

GROUPBY子句将一个表的行按一列或者几列进行分组,然后以分组为单位作为聚合函数的输入,为每个分组聚合产生一条结果记录行。
例如,执行下面的命令和SQL语句,查询各个系教师的平均工资:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
SELECT * FROM instructor ORDER BY dept_name;
SELECT dept_name,avg(salary) as avg_salary
FROM   instructor
GROUP BY dept_name;

SELECT dept_name, avg(salary) as avg_salary FROM   instructor GROUP by dept_name;

上面的语句会将表instructor的行按照dept_name进行分组,这将分为7个组(有7个不同的系):Biology分组有1条记录,Comp.Sci.分组有3条记录,Elec.Eng.分组有1条记录,Finance分组有2条记录,History分组有2条记录,Music分组有1条记录,Physics分组有2条记录。然后会为每个分组计算 salary属性的平均值,结果如上面程序所示。

可以使用列别名(alias)给教师的平均工资取个更为清晰的名字avg_salary。

此外需要注意的是:只有在GROUP BY子句中出现的列名,才能单独出现在SELECT语句的SELECT子句中;没有出现在GROUP BY子句中的列,必须放在聚合函数中。下面的语句是错误的:

/*错误，因为id列没有出现再GROUP BY子句，不能单独出现在SELECT子句*/
SELECT dept_name,id,avg(salary) as avg_salary
FROM   instructor
GROUP BY dept_name;
\q

我觉得这是一个值得注意的地方，不然到现在我还不知道为什么经常看到有些SQL语句的GROUP BY后面跟着这么多列。

因为id列没有出现在GROUP BY子句中,不能单独出现在SELECT子句上。salary列虽然没有出现在GROUP BY子句中,但是出现在了聚合函数的参数里。
下面的语句没有使用GROUP BY子句,该查询求所有教师的平均工资:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
SELECT  avg(salary) as avg_salary FROM   instructor;

SELECT语句中如果没有使用GROUPBY子句,其实就是把整个表作为一个分组。

(2)HAVING子句 HAVING子句用来过滤分组。

例如,执行下面的SQL语句,找出平均工资大于42000的系,并显示这些系教师的平均工资:

SELECT dept_name,avg(salary) as avg_salary
FROM   instructor
GROUP BY dept_name
HAVING avg(salary)>42000;

在学习GROUP BY子句和HAVING子句之前,我们先学习SQL语言的聚合函数

(3)聚合函数中的NULL 聚合函数的输入单位是分组,聚合函数会为每个分组输出一行结果记录。聚合函数主要是一些统计函数,如sum、avg、count、max和min等。
SELECT count(*),avg(salary) as avg_salary,sum(salary) as sum_salary,min(salary),max(salary)
FROM   instructor;

除了count(*)这个聚合函数以外,其他的聚合函数都会忽略NULL。下面进行测试验证聚合函数avg会忽略NULL。首先执行下面的SQL语句,查询计算机系教师的平均工资:

SELECT dept_name,avg(salary) as avg_salary
FROM   instructor
WHERE  dept_name='Comp. Sci.'
GROUP BY dept_name;

此时,我们为表instructor插入一行,该行的salary的值为NULL,然后查看添加该行后平均工资是否发生变化:

INSERT INTO instructor(ID,NAME,DEPT_NAME,SALARY) 
           VALUES('88888','ZQF', 'Comp. Sci.', NULL);
SELECT avg(salary) as avg_salary
FROM   instructor
WHERE  dept_name='Comp. Sci.';

可以发现,插入新行后,教师工资的平均值并没有发生变化,即聚合函数avg会忽略NULL。执行下面的SQL语句,看看count函数是如何处理NULL的:

SELECT * FROM   instructor;
select count(*),count(id),count(salary),count(1) from instructor;

count(id)函数中,id是主键,主键要求值非空且唯一,因此id列不会有NULL,从而count(id)会获得表instructor的总行数。

count(1)函数中,1的含义不是第一个字段,而是表示一个固定值1,在上面的查询语句中,本意是为表instructor的每一行输出一个固定值1,count(1)表示统计固定值1的个数。当然,也可以使用任何其他数字来代替数字1,比如count(2)、count(2.1),都是一样的。因此,count(1)会获得表instructor的总行数。

count(salary)函数中,salary是一个非主键列,当该列没有NOTNULL约束时,在该行的salary列上可以取有空值(本测试的数据集中id为88888的行上salary的值为NULL)。count(salary)会忽略值为NULL的行,因此此时count(salary)只统计表instructor中salary列上值为非NULL的行。如果salary上有NOT NULL约束,count(salary)会等价于count(id)(其中id是主键),此时count(salary)会统计表instructor的总行数。

count(*)是所有函数中唯一不会忽略NULL的函数。我们可以通过一个测试来证明这一点表test只有一列且没有定义主键约束,这样我们可以插人2个NULL,然后统计表test的行数:

DROP TABLE IF EXISTS test;
CREATE TABLE test(col smallint);
insert into test values(null) ;
insert into test values(null) ;
select * from test;
-- count(*)函数不会忽略null值
select count(*) from test;

下面的SELECT语句中,SELECT子句中的2.1相当于为表test所有的行都添加1列,表test的每一行在这列上的值都是2.1:

-- 下面的SELECT语句中，select子句中2.1，相当于为表test所有的行都添加1列
-- 表test的每一行，在这列上的值都是2.1
select col,2.1 from test;

使用这个数据集,我们再次测试count函数:

select count(*),count(col),count(2.1) from test;

输出显示,count(⁎)会包括对NULL的统计,因此返回表test的总行数;count(col)会忽略NULL,因此返回的行数是0;coun(2.1)是为表的每一行添加一列,并且每行在该列的值都为2.1,不可能有NULL,因此返回表test的总行数。
以下结论请读者记住:①假如表没有主键,那么count(1)比count(⁎)快;②如果表有主键,且主键作为count的条件,此时coun(主键)是最快的;③如果表只有一个字段,那么count(*)就是最快的。
最后测试一下count函数中的ALL和DISTINCT(openGauss均不支持)。打开一个Linux终端窗口,使用Linux用户omm执行下面的SQL语句:

select count(*),count(col),count(2.1) from test;


SELECT count(*) FROM instructor;
--  在openGauss中执行错误，在oracle和MySQL中可以执行
SELECT count(ALL *) FROM instructor;
--  错误语句，在所有数据库上都不能执行
SELECT count( DISTINCT *) FROM instructor;
\q
5.SQL函数

标准的SQL函数有以下几类:数学计算类函数(如统计函数)、字符串处理类函数、日期函数和高级分析函数。
前面我们学习了聚合函数,这里主要介绍字符申函数。执行下面的命令和语句,测试字符串函数。首先执行下面的SQL语句,查看测试字符串函数的数据集:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
SELECT DEPT_NAME FROM instructor;

这个查询语句的输出DEPT_NAME列是字符串,首字母是大写,其他的是小写。如果想让所有的字符都显示为大写,执行如下的SQL语句:

SELECT upper(DEPT_NAME) FROM instructor;

系统会遍历表instructor的每一行,提取DEPTNAME列的值作为upper函数的输人,将该列的字符串都变成大写后放到结果集。

如果想让所有的字符都显示为小写,执行如下的SQL语句:

SELECT lower(DEPT_NAME) FROM instructor;

总结一下:表的每一行都会作为函数的输入,然后返回一个值。

四、多表查询

表可以表示现实世界中的实体。现实世界中的实体存在着各种联系,实体之间的联系也可以用表来表示。实体之间的这些联系,是通过在相关的每个表中包含公共列来建立的。联系经常是通过设置表间的主键-外键来建立的:其中一个表的外键引用另一个表的主键,并且它们的值来自相同的域。存在联系的多个表,可以通过查找这些主键、外键的共同值来建立表间的连接。
连接将两个表的记录组合成单个更长的记录。连接运算可以分解为:先进行笛卡儿积运算,然后在这个笛卡儿积上进行一次选择运算。只有满足连接条件的记录才会出现在结果集中。连接条件可以在WHERE子句中声明。
显然,如果两个表连接时没有使用条件,连接的结果是一个笛卡儿积,这种连接也叫作交叉连接(CrossJoin)。

Θ是比较运算符(=,<,<=,>,>=,!=),带有用这些比较运算符表示的连接条件的连接称为Θ连接(Theta连接)。
只使用等值运算符“=”作为连接条件的连接称为等值连接。
要求两个表中同名列相等的等值连接称为自然连接。
只显示满足连接条件的记录的连接,如Θ连接、等值连接、自然连接,也称为内连接。还有一种称为外连接的连接方式,它既显示满足连接条件的记录,也显示不满足连接条件的记录。

1.Θ连接

在SQL语句中,θ连接的连接条件在WHERE子句中声明。当然,在WHERE子句中可以同时指定连接条件和过滤条件。
例子:找出所有教师的姓名,以及他们所在系的名称和系所在建筑的名称。
分析:实体instructor与实体department之间有如下的联系:一个教师属于一个系,一个系有很多教师。要查找的信息是两个实体的属性,因此需要通过这两个实体之间的联系来完成这个查询。department和instructor是一对多的联系。E-R模型中的一对多联系转化为物理数据库模型时,每个实体转化为一个关系表,这两个实体之间的联系建模为属性,将多方(department)的主键嵌入到一方,称为一方的外键。这样,表instructor就有了一个名字叫dept_name的属性列,该属性列的物理意义是教师在名字为dept_name的系工作。
使用下面的语句可以完成这个任务:

SELECT name,instructor.dept_name,building
FROM instructor,department
WHERE  instructor.dept_name = department.dept_name;

例子:对于大学中所有讲授课程的教师,找出他们的姓名以及他们所教的所有课程。
分析:实体instructor与弱实体section之间具有teaches关系——一个instructor可以讲授多个section,一个section可以被多个instructor讲授。instructor和section之间是多对多的联系。E-R模型中的多对多联系转化为物理数据库模型时,每个实体转化为一个关系表(instructor和section),这两个实体之间的多对多联系建模为一个单独的关联表,将多方(instructor和section)的主键放入关联表(teaches)作为其主键。也就是说,多对多联系建模为三个关系表。

这个查询要查找的信息存在于其中的两个表instructor和teaches之中,不需要使用表section也能完成查询。
下面的查询只显示教师的名字和他们所教课程的编号:

SELECT name,course_id
FROM   instructor,teaches
WHERE instructor.id= teaches.id;

下面的查询只显示教师的所有信息和他们所教课程的所有信息:

SELECT *
FROM   instructor,teaches
WHERE instructor.id= teaches.id;

在WHERE子句表达等值连接的连接条件。查询的结果集首先显示第一个表的所有列,然后显示第二个表的所有列,公共列(id)被显示了两次。

2.自然连接

自然连接有很多种SOL书写语法。

(1)使用WHERE子句书写自然连接，Θ连接标题下的两个例子就是使用WHERE子句来书写自然连接的。

我给个小提醒吧，可能有些同学会认为只有使用了NATURAL JOIN关键字才是自然连接，实则不是，WHERE后跟的table_x.col_x == table_y_col.y也是自然连接。

(2)使用关键字NATURAL JOIN书写自然连接自然连接是一种特殊的等值连接:连接的两个表必须有同名的列(一列或者多列同名,至少需要一列同名),两个表同名列的列值相等的记录进行连接,并放入结果集。下面的例子使用关键字NATURAL JOIN重新书写Θ连接标题中的这些查询:
SELECT name,course_id FROM instructor NATURAL JOIN teaches;

使用关键字NATURAL JOIN进行的自然连接,其结果是先显示两个表的公共列,然后是第一个表的剩余的列,最后是第二个表的剩余的列。

SELECT * FROM   instructor NATURAL JOIN teaches;

在这里插入图片描述
与使用WHERE子句来书写自然连接相比较,采用NATURAL JOIN更不容易遗漏连接条件，如果遗漏连接条件将会产生笛卡儿积。请看下面遗漏连接条件的情况:

SELECT *
FROM   instructor, teaches;

可以看出,没有连接条件的两个表会生成规模庞大的笛卡儿积。下面的例子在 WHERE子句中同时加入了连接条件和过滤条件。
例子:找出Comp.Sci.系讲授课程的教师的姓名以及他们所教课程的编号

SELECT name,course_id
FROM   instructor, teaches
WHERE  instructor.ID= teaches.ID AND instructor.dept_name='Comp. Sci.';

本例的WHERE子句中,两表的连接条件是instructor.ID=teaches.ID,结果集过滤条件是instructor.dept_name=‘Comp.Sci.’(只输出计算机系的教师)。
如果采用关键字NATURALJOIN来改写,则:

SELECT name,course_id
FROM   instructor NATURAL JOIN teaches
WHERE  instructor.dept_name='Comp. Sci.';

采用这个写法,可以明确区分连接条件和结果集过滤条件:在FROM子句中声明自然连接WHERE子句中进行谓词过滤(谓词为教师是计算机系的教师)。
可以用关键字NATURAL JOIN对多个表进行连续的自然连接操作,语法如下:

SELECT A1,A2,...,An
FROM r1 NATURAL JOIN r2 NATURAL JOIN ... NATURAL JOIN rm
WHERE P;

不过要注意的是,不同表的同名列不一定具有相同的语义。不具有相同语义的同名列不能进行自然连接,因为这样的连接没有意义。下面是这样的一个例子。
例子:列出教师的名字以及他们所教课程的名称。
首先,给出正确的查询语句:

SELECT name,title
FROM   instructor NATURAL JOIN teaches,course
WHERE  teaches.course_id =course.course_id;

下面是错误的查询语句:

SELECT name,title
FROM   instructor NATURAL JOIN teaches NATURAL JOIN course;

虽然错误的语句也显示了同正确语句相同的结果,但这纯属巧合。错误的SQL查询从语义上来说不正确,原因是:表instructor和表teaches可以进行自然连接,但这个自然连接的结果集不能再和表course进行自然连接。第一个自然连接的结果集中包括ID、name、dept_name、salary、course_id和sec_id列,表course包括course_id、title、dept name和credits列,公共列为course_id和dept_name,但dept_name这个公共属性在这两个表中的语义不同:前者表示教师在这个系工作,后者表示课程是由这个系开设的。因此,不能进行自然连接。

(3)使用JOIN…USING(A1…,Am)指定连接的公共列可以使用JOIN…USING(A1…,Am)来完成下面的查询。

例子:列出教师的名字以及他们所教课程的名称。

SELECT name,title
FROM (instructor NATURAL JOIN teaches) JOIN course USING(course_id);

使用JOIN…USING(A1…,Am)时需要给出一个属性列表,连接的两个表要在这些属性上相等,即使这两个表有其他的同名属性,但不要求这些未在属性列表中的其他同名属性值相等。采用这种语法可以避免NATURAL JOIN写法在连接两个表时,有同名列语义不同的问题。

(4)使用JOIN…ON指定连接的条件

例子:对于大学中所有讲授课程的教师,找出他们的姓名以及他们所教课程的编号。
执行下面的SQL语句,查看使用关键字JOIN…ON书写的语句是如何显示结果集的:

SELECT name,course_id
FROM   instructor JOIN teaches ON instructor.id=teaches.id;

执行下面的SQL语句,查看使用关键字JOIN…ON书写的语句是如何显示结果集的:

SELECT *
FROM   instructor JOIN teaches ON instructor.id=teaches.id;

使用关键字JOIN…ON书写的语句,其输出先显示第一个表的所有列,然后显示第二个表的所有列,公共列(id)显示了两次。
下面的例子显示了使用关键字JOIN…ON书写的语句,可以区分连接条件和过滤条件:在FROM子句中指定了连接条件,在WHERE子句中指定了过滤条件。
例子:找出Comp.Sci.系讲授课程的教师的姓名以及他们所教课程的编号。

SELECT name,course_id
FROM   instructor JOIN teaches ON instructor.id=teaches.id
WHERE  instructor.dept_name='Comp. Sci.';

关键字JOIN…ON可以表达更为丰富的连接条件。在内连接(满足条件的连接)中,可以将ON后的条件直接放到WHERE子句中,即可以获得等价的SOL语句。

SELECT name,course_id
FROM   instructor,teaches 
WHERE  instructor.id=teaches.id AND instructor.dept_name='Comp. Sci.';

引人JOIN…ON语法有两个优点:
第一,虽然在内连接中使用ON和WHERE没有区别,但在外连接中ON和WHERE的表现不一样;
第二,在ON中指定连接条件,在WHERE中指定过滤条件,这样的书写方式更容易让人读懂。

3.自连接

自连接查询中,一个表会被使用两次。请看下面这个例子。
找出满足下面条件的所有教师的姓名:他们的工资至少比Biology系某一个教师的工资要高。

select distinct T.name
from instructor T, instructor S
where T.salary > S.salary and S.dept_name ='Biology';

另外一种写法是,给表取别名的时候使用关键字AS:

select distinct T.name
from instructor  as T, instructor as S
where T.salary > S.salary and S.dept_name ='Biology';

在FROM子句中给表取别名,可以使用AS,也可以省略AS。在Oracle中,使用了关键字AS就不能正常工作。openGauss没有这个问题。
其他自连接的例子有:员工表中有员工的领导(领导也是员工),查询某个员工的领导时需要用自连接;课程表中有先修课,查询某个课程的先修课时也要用自连接。

4.外连接

内连接只能产生满足条件的结果,但有时我们不仅需要查看满足条件的结果,同时还需要查看不满足条件的结果。使用外连接可以完成这个任务。
例子:显示一个包含所有学生的信息的列表,需要显示他们的id、name、dept name和totcred,以及他们所选修的课程。
由于有些学生没有选修任何课程,所以以下的查询不一定能返回全部学生的信息,只是返回了已经选修了课程的学生的信息:

SELECT  * FROM student NATURAL JOIN takes;

要完成这个任务,需要使用外连接。外连接是连接操作的扩展,用来避免信息的丢失,其结果集中除了有满足连接条件的元组信息外,还会有不满足条件的元组的信息(通过NULL来填充)。有三种类型的外连接:

1)左外连接:

左边的表是主表,右边的表是从表。左外连接将返回满足连接条件的所有记录,同时返还主表(左边的表)中不满足连接条件的记录,将从表(右边的表)中相应的列用NULL填充。

2)右外连接:

右边的表是主表,左边的表是从表。右外连接将返回满足连接条件的所有记录,同时返还主表(右边的表)中不满足连接条件的记录,将从表(左边的表)中相应的列用NULL填充

3)全外连接(FullOuter Join):

没有主表,都是从表。全外连接将返回满足连接条件的所有记录;返还左边的表中不满足连接条件的记录,将右边的表中相应的列用NULL填充;返还右边的表中不满足连接条件的记录,将左边的表中相应的列用NULL填充。

(1)左外连接 例子:显示一个包含所有学生的信息的列表,需要显示他们的id、name、dept_name和tot_cred,以及他们所选修的课程。

SELECT *
FROM   student NATURAL LEFT OUTER JOIN takes;

除了看到所有已经选课的学生的情况以外,我们还看到了名为Snow、学号为70557的学生,他没有选修任何课程。
例子:找出没有选修任何课程的学生。

SELECT *
FROM   student NATURAL LEFT OUTER JOIN takes
WHERE  course_id IS NULL;

还有另外一种做法,即使用集合减法:所有学生的集合减去选修了至少一门课的学生集合,其结果是没有选修任何课程的学生的集合。

select id,dept_name,name from student
minus
select distinct id, dept_name,name from student NATURAL JOIN takes;

(2)右外连接 左外连接和右外连接是对称的,完全可以用左外连接来取代右外连接,只需要交换两个表的位置就可以了。人们往往习惯把重要的东西放在前头,因此建议优先使用左外连接。
将上面的左外连接语句改写成右外连接语句,其输出结果不变:

SELECT *
FROM   takes NATURAL RIGHT OUTER JOIN student;

下面的查询是右外连接,这是一个有意思的右外连接,其左边的表没有不满足连接条件的行

SELECT student.id,dept_name,name,tot_cred,takes.id,course_id,sec_id,semester,year,grade
FROM   student NATURAL RIGHT OUTER JOIN takes;

该查询显示了学生选课的情况,虽然是右外连接,但是因为takes中所有的选课记录都是学生
选课产生的,因此左边的表没有不满足连接条件的行。

(3)全外连接 在实践中,使用全外连接的场景非常少。openGauss支持全外连接。

SELECT student.id,dept_name,name,tot_cred,takes.id,course_id,sec_id,semester,year,grade
FROM student NATURAL FULL OUTER JOIN takes;

还可以通过左外连接和右外连接的并集来实现全外连接:

SELECT student.id,dept_name,name,tot_cred,takes.id,course_id,sec_id,semester,year,grade
FROM   student NATURAL LEFT OUTER JOIN takes 
UNION
SELECT student.id,dept_name,name,tot_cred,takes.id,course_id,sec_id,semester,year,grade
FROM   student NATURAL RIGHT OUTER JOIN takes;

(4)在外连接中使用ON指定连接条件 ON条件是外连接声明的一部分,WHERE子句不是外连接声明的一部分。
请看下面的查询:

select * from student left outer join takes on student.ID= takes.ID;

连接条件是两个表的id列相等。左外连接的输出中左边的表中不满足条件的记录,右边的表中相应列填充NULL。
下面的查询是将上面查询的连接条件放到了WHERE子句中:

连接条件是两个表的id列相等。左外连接的输出中左边的表中不满足条件的记录,右边的表中相应列填充NULL。
下面的查询是将上面查询的连接条件放到了WHERE子句中:

select * 
from student left outer join takes on 1=1
where student.ID=takes.ID;

由于连接条件是1=1,永远为真(TRUE),所有记录都满足连接条件,这个外连接实际产生了一个笛卡儿积。然后我们在WHERE子句中进行谓词过滤,将两个表中id值不同的给过滤掉:因为在takes中没有id=70557的元组,每次当外连接中出现name=“Snow”的元组时,student.id与takes.id的取值必然是不同的,这样的元组会被WHERE子句中的谓词排除掉。因此,学生Snow不会出现在这个查询的结果中。
这个例子显示了在外连接中连接条件和过滤条件的区别:外连接中的连接条件一定要写在ON子句中,过滤条件一定要写在WHERE子句中。

5.集合运算
(1)两个基本的查询

例子:找出在2009年秋季学期开设的所有课程。

select course_id
from section
where semester = 'Fall' and year= 2009;

例子:找出在2010年春季学期开设的所有课程。

select course_id
from section
where semester = 'Spring' and year= 2010;
(2)集合并UNION

例子:找出在2009年秋季学期开课或者在2010年春季学期开课或两个
学期都开课的所有课程。

(select course_id from section where semester ='Fall' and year = 2009) 
union
(select course_id from section where semester ='Spring' and year = 2010);

UNION默认会进行去重复操作,如果不想去掉重复的结果,可以使用UNION ALL:

(select course_id from section where semester ='Fall' and year = 2009) 
union all
(select course_id from section where semester ='Spring' and year = 2010);
(3)集合交INTERSECT

例子:找出在2009年秋季和2010年春季两个学期都开课的所有课程

(select course_id from section where semester ='Fall' and year = 2009) 
intersect
(select course_id from section where semester ='Spring' and year = 2010);

也可以使用下面的等价方法(自然连接求交集)来完成集合交运算:

SELECT *
FROM
      (select course_id from section where semester ='Fall' and year = 2009) as tbl1
        NATURAL JOIN
      (select course_id from section where semester ='Spring' and year = 2010) as tbl2;

还可以使用下面的等价方法(使用相关子查询)来完成集合交运算:

select course_id
from section S
where semester = 'Fall' and year=2009 and
      exists   (  select *
                from section  T
                where semester = 'Spring' and year=2010 and
                      S.course_id= T.course_id);

对该语句的解释,请参看后面相关子查询部分。

(4)集合减MINUS

例子:找出在2009年秋季学期开课但不在2010年春季学期开课的所有课程。

(select course_id from section where semester ='Fall' and year = 2009) 
minus
(select course_id from section where semester ='Spring' and year = 2010);

也可以使用下面的等价方法(使用左外连接来实现减法)来完成集合减运算:

Select *
FROM
    (select course_id from section where semester ='Fall' and year = 2009) as tbl1
      NATURAL LEFT OUTER JOIN
    (select course_id from section where semester ='Spring' and year = 2010) as tbl2
WHERE tbl2.course_id IS NULL;

另外一种实现集合减等价运算的形式如下:

select course_id
from section s1
where (s1.semester ='Fall' and s1.year = 2009) and
   not exists
   ( select course_id 
   from section s2
   where s2.semester ='Spring' and s2.year = 2010 and
                s1.course_id=s2.course_id 
          );

如果使用MINUS,会更方便也更好理解;使用左外连接次之;使用NOT EXISTS实现,很难看出是减法操作。
如果没有NULL,可以使用NOT IN或者<>ALL来实现减法。
下面是在没有NULL的情况下,使用NOT IN实现减法:

select course_id
from section s1
where (s1.semester ='Fall' and s1.year = 2009) and
   course_id not in
   ( select course_id 
   from section s2
   where s2.semester ='Spring' and s2.year = 2010 and
                s1.course_id=s2.course_id 
          );

下面是在没有NULL的情况下,使用<>ALL实现减法:

select course_id
from section s1
where (s1.semester ='Fall' and s1.year = 2009) and
   course_id <>ALL
   ( select course_id 
   from section s2
   where s2.semester ='Spring' and s2.year = 2010 and
                s1.course_id=s2.course_id 
          );
6.子查询

一条完整的查询的一般形式如下:

SELECT columnlist
FROM tablelist
WHERE condition
GROUP BY columnlist
HAVING condition
ORDER BY columnlist

子查询可以出现在SQL语句的任何子句中。

(1)子查询作为数据源(FROM子句)

例子:找出系平均工资超过42000的那些系中教师的平均工资。
策略:通过一个子查询来产生一个临时的关系表(包含所有系的名字和相应的教师平均工资),然后查询这个临时表获得满足要求的记录。

select dept_name, avg_salary
from ( select dept_name, avg (salary) as avg_salary
      from instructor
      group by dept_name) as dept_avg
where avg_salary > 42000;

例子:找出所有系中工资总额最大的系。
策略:构造一个子查询,其结果是每个系的系名和工资总额,然后在此基础上查询工资总额最大的系。

select max(tot_salary)
from ( select dept_name, sum(salary) as tot_salary
       from instructor
       group by dept_name) dept_total;

上面的语句没有显示到底是哪个系的工资总额最大,可以在上面语句的基础上做一些修改:

select dept_name, max(tot_salary) as max_total_salary
from ( select dept_name, sum(salary) as tot_salary
       from instructor
       group by dept_name)
group by dept_name
order by 2 DESC
limit 1;
(2)子查询作为查询条件(WHERE子句或HAVING子句)

WHERE子句中的子查询通常用于:
①集合成员资格测试(IN和NOTIN)。
②集合的比较(ALL和SOME)。
③集合的基数检查——测试子查询的结果是否为空集(EXISTS和NOT EXISTS)。
1)集合成员资格测试(IN和NOT IN)。例子:找出在2009年秋季学期和2010年春季学期同时开课的所有课程。

select distinct course_id
from section
where semester = 'Fall' and year=2009 and
      course_id in ( select course_id
                     from   section
                     where  semester = 'Spring' and year=2010);

例子:找出所有在2009年秋季学期开课,但不在2010年春季学期开课的课程。

select distinct course_id
from section
where semester = 'Fall' and year=2009 and
      course_id not in ( select course_id
                     from   section
                     where  semester = 'Spring' and year=2010);

例子:找出选修了教师工号为10101的教师所教课程的(不同的)学生总数。

select count(distinct ID)
from takes
where (course_id, sec_id, semester, year) in ( select course_id, sec_id, semester, year
                                               from teaches
                                               where teaches.ID=10101);

2)集合的比较(ALL和SOME)。
例子:找出工资至少比Biology系某一个教师的工资高的所有教师的姓名。可以通过使用自连接来完成这个查询:

select distinct T.name
from instructor T, instructor S
where T.salary > S.salary and S.dept_name ='Biology';

也可以通过子查询来完成这个查询:

select name
from instructor
where salary> some ( select salary
                     from instructor
                     where dept_name='Biology');

SQL语言允许使用>some >=some <some <=some =some <>some。请注意:=some相当于IN,<>some不等价于NOT IN。ANY是SOME的同义词,不过ANY的语义比较不清楚,所以推荐只用SOME。
例子:找出工资比Biology系每一个教师的工资都要高的所有教师的姓名。

select name
from instructor
where salary> all ( select salary
                     from instructor
                     where dept_name='Biology');

SOL语言允许使用>alll >=all <all <=all =all <>all。请注意:=all相当于IN,<>all等价于NOT IN。
可以这样来理解:
①<>ALL:和所有的都不相等,也就是说没有在另一个集合中出现。
②=ALL:和所有的都相等,一般用来看两个集合是否相等。
③some:和部分不相等,一般用来看两个集合有没有不同元素。
④=some:和部分相等,一般用来看有没有交集。
例子:找出平均工资最高的系。

select dept_name
from instructor
group by dept_name
having avg (salary) >= all (select avg(salary)
                            from instructor
                            group by dept_name);

3)测试子查询的结果是否为空集(EXISTS和NOT EXISTS)。
例子:找出在2009年秋季学期和2010年春季学期同时开课的所有课程。

select course_id
from section S
where semester = 'Fall' and year=2009 and
      exists   (  select *
                from section  T
                where semester = 'Spring' and year=2010 and
                      S.course_id= T.course_id);

使用了来自外层查询的相关名称的子查询被称作**相关子查询(Correlated Subquery)**可以这么来理解这个查询:对2009年秋季学期开设的每一门课程都进行测试,如果该门课程也在2010年春季学期开设的话,将该课程放入结果集,然后继续测试下一门在2009年秋季学期开设的课程。这其实是一种求交集的方法。
例子:很多学生都会选修一些课程,其中包括Biology系开设的课程,查询哪些学生选修了Biology系的所有课程(使用关系除法)。
为了完成这个查询,需要给测试数据集添加几条记录:

\q
gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
\set AUTOCOMMIT off
insert into section values ('BIO-399','1','Fall','2010','Painter','514','A');
insert into teaches values ('76766','BIO-399','1','Fall','2010');
insert into takes values ('98988', 'BIO-399', '1', 'Fall', '2010', null);
insert into takes values ('12345', 'BIO-399', '1', 'Fall', '2010', null);

完成这个查询的第一种方法的思路如下:在外层查询中,对每个学生进行测试,用生物系开设的所有课程减去该学生选修的所有课程,如果结果是个空集,则证明该学生已经选修了生物系开设的所有课程,把该学生放入结果集。由于SQL语言不支持包含操作(A contains B),其等价的表达式为NOT EXISTS(B cxcept A),且要求集合B是非空集。
下面的SQL语句是这种思路的实现:

select distinct S.ID, S.name 
from student S 
where not exists  (
                    ( select course_id 
                      from course 
                      where dept_name='Biology')
                   minus
                    ( select T.course_id 
                      from takes T 
                       where S.ID = T.ID)
                  );

完成这个查询的第二种方法的思路如下:
首先构造Condition1——生物系开设的某门课程没有被学生s.id选修,然后再构造Condition2——不存在这样的生物系的课程使得Condition1成立。如果该学生满足条件2,就说明该学生已经选修了生物系开设的所有课程。下面是这种思路的逐步的实现过程。
第一步,构造Condition1:生物系开设了某门课程c.course_id,该课程没有被学生s,id选修

下面是这种思路的逐步的实现过程。
第一步,构造Condition1:生物系开设了某门课程c.course_id,该课程没有被学生s.id选修

c.dept_name="Biology'and
not exists(SELECT*
FROM takes x
WHERE x.id=s.id    --选修课程的学生是s.id
and x.course_id=c.course_id   --选修的是生物系的课程
)

注意:Condition1还没有指定c.course_id的范围。

第二步,构造Condition2:不存在这样的生物系的课程使得Condition1成立。

NOT EXISTS Condition1

进一步(指定c.course_id的范围)书写为:

NOT EXISTS(
	SELECT*
	FROM course c  --指定c.course_id的范围
	WHERE Condition1
	)

再进一步书写为:

NOT EXISTS(
	SELECT*
	FROM course c  --指定c.course_id的范围
	WHERE c.dept_name='Biology' and
		not exists(SELECT*
		FROM takes x
		WHERE x.id=s.id  --选修课程的学生是s.id
		and x.course_id=c.course_id  --选修的是生物系的课程
		)
	)

注意:上面的表达式还未确定s.id。

第三步,最后,我们需要添加一些字段,为查询显示额外的信息——课程名,最终的SQL语句如下:
我们来确定s.id,完成查询:

SELECT s.id, s.name
FROM student s
WHERE  NOT EXISTS(
               SELECT *
               FROM course c        --指定c.course_id的范围
               WHERE c.dept_name='Biology' and
                  not exists (  SELECT * 
                             FROM takes x 
                             WHERE x.id=s.id            --选修课程的学生是s.id
                               and x.course_id=c.course_id --选修的是生物系的课
                            )
                    );

执行下面的语句,回滚测试数据:

rollback;
q
(3)利用子查询创建一个计算的列(SELECT子句或GROUP BY子句或ORDER BY子句)

例子:列出所有的系以及它们拥有的教师数。

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
select dept_name,
       (select count(*)
        from instructor
        where department.dept_name=instructor.dept_name) as num_instructors
from department;

只返回单个属性的单个元组的子查询称为标量子查询。

这个查询是真的有说法，我给你另外一个有误导性的查询，请看：

select dept_name,
     (select count(*)
      from instructor, department
      where department.dept_name=instructor.dept_name) as num_instructors
from department;

结果完全不一样，因为这里的查询不仅是标量子查询，而且还是相关子查询(Correlated Subquery)

(4)使用WITH子句替代子查询(公用表表达式)使用WITH子句可以定义临时表。

有时也把WITH子句称为公用表表达式。
例子:找出具有最大预算值的系。

with max_budget (value) as
         (select max(budget)
          from department)
select dept_name,budget
from department, max_budget 
where department.budget = max_budget.value;

在WITH子句中可以定义多个公用表。
例子:查出所有工资总额大于所有系平均工资总额的系。

with
dept_total (dept_name, value) as
        (select dept_name, sum(salary)
         from instructor
         group by dept_name),
dept_total_avg(value) as
       (select avg(value)
       from dept_total)
select dept_name 
from dept_total, dept_total_avg 
where dept_total.value >= dept_total_avg.value;

不使用WITH语句,也可以写出这个查询,但语句会非常复杂且难于理解。

7.复杂分组聚集(多表)

例子:对于在2009年讲授的每个课程段,如果该课程段有至少2名学生选课,找出选修该课程段的所有学生的总学分(tot_cred)的平均值。

select course_id, semester, year, sec_id, avg(tot_cred)
from takes natural join student
where year=2009
group by course_id, semester, year, sec_id
having count(ID)>= 2;

注意:上述查询需要的所有信息来自关系takes和 student,尽管此查询是关于课程段的,却并不需要与section进行连接。
例子:找出每个系在2010年春季学期至少讲授一门课程的教师人数。

select dept_name, count(distinct ID) as instr_count
from instructor natural join teaches
where semester = 'Spring' and year = 2010
group by dept_name;

五、SQL语言中的等价表达式

1.双重否定

谓词p的双重否定形式如下:
not(not p)<=>p
例子:表parts有属性colour,not(parts.colour!=red)等价于parts.colour=red

2.德摩根法则

not(p and q)<=>(not p)or(not q)
not(p or q)<=>(not p) and (not q)
例子:表parts有属性colour和location,not(parts.colour=red and parts.locatioin='Beijing)等
价于((not(parts.colour=red))or(not(parts.locatioin=Beijing)),还等价于(parts.colour!=red or
parts.locatioin!=‘Beijing’)

3.p→q

p→q<=>(not p) or q
例子:如果零件是红色的(p.colour=‘red’),那么零件应该保存在北京(p.location=“Bejing)我们可以用p.colour!=‘red’ or p.location='Beijing’来替换上面的蕴含式。

4.包含(子集)

SQL语言不支持包含操作(A contains B),其等价的表达式为NOT EXISTS(B except A),且要求集合B是非空集。具体例子参见前面【(2)子查询作为查询条件(3)测试子查询的结果是否为空集】标题下第2个例子的第一种方法:查询哪些学生选修了Biology系的所有课程(使用关系除法)。

select distinct S.ID,S.name
from student S
where not exists(
	(select course_id
	from course
	where dept name-'Biology)
	minus
	(select T.course_id
	from takes T
	where S.ID=T.ID)
);

用生物系开设的所有课程减去某个学生选修的所有课程,如果结果是空集,那么说明该名学生选修的课程中包含了所有生物系开设的课程。

5.全称量词转换为存在量词

SQL语言不支持全称量词,需要将全称量词转换为存在量词。对于所有的元组t,谓词p(t)成立,其等价的存在量词形式为:不存在这样的元组t,使得谓词p(t)不成立。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值