SQL语句
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
1. 执行where... 对全表数据做筛选,返回结果集 ①。
2. 对结果集 ② 进行分组,返回结果集 ③。
3. 对结果集 ③ 执行 having... 筛选,返回结果集 ④。
4. 对第结果集 ④ 排序。
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
1. WHERE 子句作用于基表或视图,从中选择满足条件的元祖。
2. HAVING 短语作用于组,从中选择满足条件的组。
3. WHERE在分组前进行过滤选择,HAVING在分组后进行过滤。
4. HAVING 是対分组之后的组数据进行筛选,WHERE是対分组之前的每一行数据进行筛选。
5. WHERE 后的条件表达式不允许使用聚合函数,而 HAVING 可以。
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
1. 自然连接一定是等值连接,但等值连接不一定是自然连接。
2. 等值连接要求相等的分量,不一定是公共属性;而自然连接要求相等的分量必须是公共属性。
3. 等值连接不把重复的属性除去;而自然连接要把重复的属性除去。
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
1. 需要给表起别名以示区别
2. 由于所有属性名都是同名属性,因此必须使用别名前缀
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
1. 普通连接操作只输出满足连接条件的元祖
2. 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元祖一并输出
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
1. 相关:子查询的查询条件依赖于父查询
2. 不相关:子查询的查询条件不依赖于父查询
EXISTS谓词:存在量词
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822549-653119164.gif)
带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值或逻辑假值
用EXISTS/NOT EXISTS 实现全称量词(难点)
1. SQL语言中没有全称量词
2. 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822256-1384869278.gif)
1. GROUP BY必须放到WHERE语句之后,GROUP BY 与 ORDER BY 都是对筛选后的数据进行处理,而where 是用来筛选数据的。
2. 当使用了group by 或者 聚合函数的时候,在select 查询列表中不能包含其他列名,除非该列同时也出现在了group 子句中,或者该列出现在某个聚合函数中。
3. 当某列出现null值时,count ( * ) 仍然会计算,但是count ( 列名 ) 不会。
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171822912-681533705.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171823278-73241955.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171823659-879392530.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171824028-1485647753.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171824385-129106906.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171824708-1728919489.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171824985-1814785304.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171825354-1700010441.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171825722-222725508.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171826087-1553728495.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171826450-2106355296.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171826782-1993687229.png)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
![](https://img2020.cnblogs.com/blog/1332554/202004/1332554-20200403171821899-1466514485.gif)
插入元祖:
INTO子句:属性列的顺序可与表定义中的顺序不一致。
VALUES子句:提供的值(个数、类型)必须与INTO子句匹配。
视图的特点:
1. 虚表,是从一个或几个基本表(或视图)导出的表
2. 只存放视图的定义,不存放视图对应的数据
3. 基表中的数据发生变化,从视图中查询出的数据也随之变化
(DBMS执行CREATE VIEW时只是把视图定义存入数据字典,并不执行其中的SELECT语句)