select
e.empno,e.ename,d.dname,e.deptno,d.deptno
from
emp as e,dept as d
where e.deptno=d.deptno;
除此外，一个关系与自身进行笛卡尔积运算也需要使用重命名。比如找出至少比运维部门某一位员工工资更高的所有员工姓名。
select distinct T.name
from emp as T,emp as S
where T.sal > S.sal and S.deptname = ‘运维部’;
#### 4.2 字符串运算
在SQL标准中，字符串的相等运算是大小写敏感的。但是在一些数据库中(如Mysql和SQL Server)，在匹配字符串时并不区分大小写。
字符串可以应用许多函数运算，比如连接字符串(`||`),提取子串，去字符串后空格`trim`等等。不同数据库系统提供的函数集是不同的，具体可以查阅数据库系统手册。
使用`like`可以进行模糊匹配。有两个特殊的字符串可以用来描述模式。
* 百分号`%`:匹配任意字串
* 下划线`_`：匹配任意一个字符
模式是大小写敏感的(Mysql中除外，PostgreSQL使用ilike大小写也不敏感)。
另外，为了能够使模式包含特殊字符(`%`和`_`),SQL允许使用`escape`关键字定义转义字符。如。
`like 'ab\%cd% escape '\'`表示匹配以`'ab%cd`开头的所有字符。
SQL标准还允许我们通过`not like`比较运算符来搜索不匹配想。一些实现还提供了不区分大小写的变种。
一些SQL实现，特别是PostgreSQL，提供了`similar to`运算。它具备比`like`更强大的模式匹配能力，其模式定义语法类似`UNIX`中使用的正则表达式。
#### 4.3 排序
使用`order by`关键字就可以实现排序了。`desc`表示降序，`asc`表示升序，缺省时默认升序。
select
empno, ename
from emp
where
hiredate between date '1980-01-01' and date '1981-01-01'
order by empno desc;
### 5 集合运算
SQL作用在关系上的`union`、`intersect`和`except`运算对应数学集合论中的`∪`、`∩`、`-`。
#### 5.1 并运算
union会去重。
(select deptno from emp)
union
(select deptno from dept
order by deptno);
注意，上面使用`()`是为了方便阅读，如果数据库不允许使用可以去掉。
不想去重则可以使用`union all`
#### 5.2 交运算
这个过程是如此的枯燥。
(select deptno from emp)
intersect
(select deptno from dept
order by deptno);
同理，`insect all`可以保留重复项。
#### 5.3 差运算
(select deptno from emp)
except
(select deptno from dept
order by deptno);
同理，`except all`可以保留重复项。
### 6 空值
空值给包括算数运算、比较运算和集合运算在内的关系运算带来了特殊的问题。
比如，如果算术表达式的任一输入值为空，则该算术表达式(如+,-,\*，/)结果为空。
对比较运算，这也是一个问题。考虑比较运算`1<null`的结果，这是true还是false呢？我们并不知道空值是什么，所以很难进行比较。因而SQL将任何设计空值的比较运算结果视为`unknown`。
由于where子句中的谓词可以对比较结果进行`and`,`or`和`not`的布尔运算，因此这些布尔运算的定义也被拓展成可以处理unknown值。
* and。true and unknown结果为unknown, false and unknown结果是unknown.而unknown and unknown的结果是unknown。
* or。true or unknown的结果是true，false or unknown的结果是unknown,而unknown or unknown的结果是unknown。
* not。not unknown的结果是unknown.
>
> tips:尝试理解上面的规则。不用死记硬背。
>
>
>
可以使用`is null`和`is not null`来判空和判非空。还可以使用`is unknown`和`is not unknown`来判断一个比较运算的结果是否为`unknown`,例如
select name
from instructor
where salary > 1000 is unknown;
注意，在使用`select distinct`时。重复元素如果都是空，会被判为相同去重。这与谓词中的处理有区别，因为在谓词中`null = null`会返回`unknown`。
如果元组上所有属性上取值相等，那么他们会被当做相同的元组，即使某些值为空，这种方式还适用与集合的并、交、和差运算。
### 7 聚集函数
聚集函数是以**值集**为输入并返回单个值的函数，SQL标准提供了五个标准聚集函数。平均值`avg`,最小值`min`,最大值`max`,总和`sum`和计数`count`。其中`sum`和`avg`必须输入数字集，但是其他运算符可以作用在非数字类型的集合上。
#### 7.1 基本聚集
先求个平均工资
select avg(salary) as avg_salary
from instructor
where dept_name = ‘Comp.Sci’;
注意，在求平均值时，重复值是必要的，如果先对数据进行了去重操作，求出来的平均工资就不正确了。
如果确实需要在使用聚集函数前对数据去重，可以使用`distinct`关键字。
select count(distinct ID)
from teachers
where semester = ‘Spring’ and year = 2018;
使用`count(*)`可以统计一个关系中的元组数量。
#### 7.2 分组聚集
有时候我们不仅希望将聚集函数作用在单个元组集上，而且希望将其作用在一组元组集上。在SQL上可以使用`group by`实现。在`group by`子句中可以给出一个或者多个属性用来构造分组。在分组(group by)子句中所有属性上取值相同的元组会被分在一个组内。
考虑一个示例，找出每个系的平均工资。
select deptname, avg(salary) as avg_salary
from instructor
group by dept_name;
使用聚集函数的一个重要的点是，任何没有出现在group by子句中的属性，如果出现在select子句中，它只能作为聚集函数的参数。比如，下面的查询是错误的，因为ID没有出现在group by子句中，但是出现在了select子句中，而且没有被聚集。
/* 错误查询 */
select deptname, ID,avg(salary) as avg_salary
from instructor
group by dept_name;
总结来说就是，select子句中的属性只能是分组条件相关属性和聚集函数。这是因为一个特定分组的每个教师都可以有唯一的ID，但是每个分组只输出一个元组，那就无法确定要选择哪个元组值作为唯一输出。
#### 7.3 having子句
有时候，对分组的限定条件比对元组的限定条件更有用。比如，我们只对教师的平均工资超过42000美元的那些系感兴趣，这个条件并不针对单个元组，而是针对`group by`子句构成的每个分组。我们可以使用`having`子句做到这些。SQL在形成分组以后才应用`having`子句中的谓词，因此在`having`子句中可以使用聚集函数。
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
与select子句类似，任何出现在having子句中，但是没有被聚集的属性必须出现在group by子句中。
#### 7.4 对空值和布尔值的聚集
考虑如下查询:
select sum(salary)
from instructor;
如果有教师的薪资是null，SQL将忽略其值。在聚集函数中，除count(\*)外的所有函数都会忽略输入集合中的空值。由于空值被忽略，聚集函数的输入值集合可能为空集，规定空集的count运算值为0，其它所有聚集运算会返回一个空值，在一些更加复杂的SQL结构中空值的影响会更加难以捉摸。
在SQL1999中引入了布尔数据类型，它可以取true，false和unknown三种值，聚集函数`some`和`every`可以应用于布尔值的集合，并分别计算这些值取(or)和取(and）的结构。
### 8 嵌套子查询
#### 8.1 集合成员资格
使用`in`可以测试集合成员资格，使用`not in`可以测试集合资格的缺失，其实就是可以粗浅的理解为过滤。比如查询2017年秋季的课程，在看看他们是不是也是2018年的春季的课程集合中的成员，你当然可以使用交运算完成，但你也可以使用嵌套子查询实现。注意下面使用了`distinct`。
select distinct course_id
from section
where semester = ‘Fall’ and year = 2017 and
course_id in
(select course_id
from section
where semester = ‘Srping’ and year = 2018);
#### 8.2 集合比较
之前我们有一个查询时"找出工资至少比运维部某员工的工资要高的所有员工的姓名"。之前的做法是，
select distinct T.name
from emp as T,emp as S
where T.sal > S.sal and S.deptname = ‘运维部’;
但是我们可以使用`some`关键字，结合子查询实现同样的操作。
select name from emp where sal > some
(select sal
from emp where department = '运维部‘)；
还有另外一个关键字`all`代表所有也常常用于集合比较。练习找出平均工资最高的系。
select dept_name
from instructor
group by deptname
having avg(salary) >= all
(select avg(salary) from instructor group by dep_tname);
写起来好舒服的。
#### 8.3 空关系测试
SQL中包含一个特性，测试一个子查询的结果是否存在元组，`exist`结构在作为参数的子查询非空时返回true值。我们可以用它实现"找出2017年秋季学期和2018年春季学期都开课的所有课程"。
select course_id from section as S
where semester = ‘Fall’ and year = 2017 and
exist(select *
from section as T
where semester = ‘Spring’ and year = 2018 and
S.course_id = T.course_id);
实际上它并不是特别好理解，注意比较与`in`在写法上的区别。上面的查询还说明了SQL的一个特性：来自外层的查询**相关名称**(上述查询中的S)可以用在`where`子句的子查询中。
使用了来自外层查询的相关名称的子查询被称为**相关子查询**。
在包含了子查询的查询中，在相关名称上可以应用作用域规则，根据此规则，在一个子查询中只能使用此子查询本身定义的，或者包含此子查询的任何查询中定义的相关名称，如果一个相关名称既在子查询中局部定义，有在包含该子查询的查询中全局定义，则局部定义有效。这有点类似编程语言中的局部变量的规则。
上面的相关子查询是不是觉得怪别扭的。怎么理解呢？我们来分析下它的执行步骤。
先看一个简单的例子。
select * from emp e
where sal >(select avg(sal) from emp where job = e.job);
>
> 1.首先执行外查询 select \* from emp e，
> 2.然后取出第一行数据，将数据中的JOB传递给内查询
> 内查询(select avg(sal) from emp where job = e.job) 根据外查询传递的JOB来查询平均工资，此时相当于select avg(sal) from emp where job = ‘CLERK’;
> 3.外查询取出的第一行数据比较sal是否大于内查询查出的平均工资，若大于，则保留改行作结果显示，反之则不保留
> 4.依次逐行查询、比较、是否保留；类似Java的for循环一样
>
>
>
在回过头看上面语句的执行步骤
select course_id from section as S
where semester = ‘Fall’ and year = 2017 and
exist(select *
from section as T
where semester = ‘Spring’ and year = 2018 and
S.course_id = T.course_id);
>
> 执行步骤：
>
>
> 1. 执行第一行，查询section表符合semester = 'Fall’以及 year = 2017的course\_id值
> 2.然后取出外查询的第一行数据，此时S.course\_id相当于明牌。找到符合semester = ‘Spring’ 和 year = 2018 两个条件，且course\_id与外层查询相同的课程，有则返回true，没有则返回false。
> 3.内查询返回true时，则把该行数据结果保留，返回false时，则不保留
>
>
>
理解了吧。
自然，也存在`not exist`。可以用它表达超集的概念，比如”关系A包含关系B“可以表示为
`not exist (B except A)`,现在通过`not exist`实现"找出选修了Biology系开设的所有课程的所有学生"。
是不是觉得一头雾水，别着急，我们先翻译下题意，题目换一种表达方式就是，找出选修的课程*包含*Biology的所有课程的学生。
发现A包含B结构了吗？学生选修的课程是A，Biology系开设的所有课程是B。
select S.ID,S.name
not exist
(select course_id from course
where dept_name = ‘Biology’)
except
(select T.course_id
from take as T
where S.ID = T.ID)
再来，我们前面提到了一个查询，“找出选修了ID为10101的教师所讲授的课程段的(不同)学生总数”。
回顾下我们之前怎么做的？我们使用了`in`判断集合成员资格的形式进行了实现。核心套路是，大范围是否在小范围里。
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’);
现在用子查询来做看看。核心套路是，先查后筛，先查所有课程的参与学生数，再筛选器教师是否为10101，其时间段是否一致。
select count(distinct ID)
from takes
where exist (select course_id,sec_id,semester,year
from teaches
where teaches.ID=‘10101’
and takes.course_id = teaches.course_id
and takes.sec_id = teaches_sec_id
and takes.semester = teaches_semester
and takes.year = teaches.year
);
#### 8.4 重复元组存在性测试
`unique`可以检测结果是否存在重复元组。找出2017年最多开设一次的所有课程。
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id = R.course_id and
R.year = 2017
);
当然，`not unique`也是存在的。查询2017年最少开设两次的所有课程。
select course_id
from course as T
where not unique
(select R.course_id
from section AS R
where T.course_id = R.course_id and
R.year = 2017
);
值得注意的是，即使存在一个元组存在多个副本，只要该元组至少有一个属性为空，那么unique测试结构就有可能为真。
#### 8.5 from子句中的子查询
前面的子查询都是在where子句中使用的，下面介绍在from子句中使用子查询。这里采用的主要观点是:任何`select-from-where`表达式返回的结果都是关系，因此可以被插入到另一个`select-from-where`表达式出现的任何位置。
考虑查询"找出系平均工资超过42000美元的那些系的教师平均工资"，先回顾下之前的做法。
select depet_name,avg(salary)
from instructor group by dept_name having avg(salary) > 42000;
现在我们不使用`having`子句来编写此查询。
select dept_name,avg_salary
from (select dept_name,avg(salary) as svg_salary
from instructor
group by dept_name)
where avg_salary > 42000;
看起来还挺好理解的，无非是做查询和过滤两件事。我们还可以把子查询的结果关系起个名称，并对属性进行重命名。
select dept_name,avg_salary
from
select dept_name,avg(salary)
from instructor
group by dept_name
as dept_avg(dept_name,avg_salary)
where avg_salary > 42000;
大部分(并非全部)的SQL实现都支持在from子句中嵌套子查询，但请注意，某些SQL实现(如MYSQL和PostgreSQL)要求from子句中的每个子查询的结果关系必须被命名，即使此名称从来未被引用，Oracle允许(以省略关键字as的方式)对子查询的结果关系命名，但不支持对此关系的属性进行更名。对此问题的一种简单的应对措施是在子查询的select子句中对属性进行更名。
另外举一个栗子，“找出所有系中所有教师工资总额最大的系”。此时having子句无能为力了，但是from子查询它还行。
select max(sum_sal) from
select dept_name,sum(salary) as sum_sal
from instructor
group by dept_name;
from嵌套子查询并不能够使用来自同一from子句的其他关系的相关变量。从SQL：2003开始的SQL标准允许from子句中的子查询使用关键字`lateral`作为前缀，以便访问同一个from子句中在它前面的表或者子查询的属性。例如，如果我们想打印每位教师的姓名，以及它们的工资和他们所在系的平均工资，可以编写如下查询。
select name,salary,avg_salary
from instructor I1,lateral (select avg(salary) as avg_salary
from instructor I2
where I2.dept_name = I1.dept_name);
如果没有lateral查询子句，子查询就不能访问来自外层查询的相关变量I1。
#### 8.6 with子句(SQL:1999)
`with`子句提供了一种定义临时关系的方式，这个定义只对包含with子句的查询有效。考虑下面的查询，找出具有最大预算值的那些系。
with max_budget(value) as
(select max(budget)
from department)
select budget
from department,max_budget
where department.budget = max_budget.value;
神奇吧。
该查询中with子句定义了临时关系max\_budget,此关系只能在同一查询的后面部分使用。with子句是在SQL:1999中引入的。有许多(但非所有)数据库系统对其提供了支持。
我们也可以使用from子句或者where子句中的嵌套子查询编写上述查询，但是嵌套子查询远不如上面的with子句逻辑清晰。它还允许一个查询内的多个地方使用这种临时关系。比如要找出工资总额大于所有系平均工资总额的所有系。
with dept_total (dept_name,value) as
(select dept_name,sum(salary)
from instructor
group by dept_name),
dept_total_avg as
(select avg(value)
from dept_total)
select dept_name
from dept_total,dept_total_avg
where depet_total.value > dept_total_avg.value;
是不是简单，清晰，好理解！
### 8.7 标量子查询
SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回一个包含单个属性的元组，这样的子查询成为**标量子查询**。举个栗子，列出所有的系以及每个系中的教师总数。
select dept_name,
(select count(*)
from instructor
where department.deptname = instuctor.dept_name)
as num_instructors
from department;
上面示例的子查询保证只返回单个值，因为它使用了不带group by的count(\*)的聚集函数。
在编译时并不能总是可以判断一个子查询返回的结果中是否有多个元组，如果一个子查询在执行后其结果中有不止一个元组，则会产生一个运行时错误。
**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**
**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**
**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**
组，这样的子查询成为**标量子查询**。举个栗子，列出所有的系以及每个系中的教师总数。
select dept_name,
(select count(*)
from instructor
where department.deptname = instuctor.dept_name)
as num_instructors
from department;
上面示例的子查询保证只返回单个值，因为它使用了不带group by的count(\*)的聚集函数。
在编译时并不能总是可以判断一个子查询返回的结果中是否有多个元组，如果一个子查询在执行后其结果中有不止一个元组，则会产生一个运行时错误。
[外链图片转存中...(img-r2t31Kxp-1714736053936)]
[外链图片转存中...(img-YJFjJWJF-1714736053936)]
**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**
**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**
**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**
扫一扫
1130
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
