MySQL 一

表

规则规范

大小写

建议数据库名，表名，表别名，字段名小写

而关键字，函数名，绑定变量大写

注释

单行注释

1 2	#xxxxx -- xxxxx

多行注释

/*
xxxxxx
xxxx
xxxxx
*/

多行注释的嵌套是没有意义的

*命名

数据导入

方式1

sql通用

1	source 文件的全路径

方式1在图形化界面下是会报错的，只能在cmd使用

#注意！#该语句后不接分号；

方式2

图形化界面

如SQLyog中，工具->执行sql脚本->选择对应文件

SELECT

*基本SELECT

如

1	SELECT 1+1

等价于

1	SELECT 1+1 from dual；

其中dual是伪表

SELECT FROM

1	select xxx from yyy;

其中xxx是所需查找的字段/列，可以使用*号表示查找所有列，结果也就是显示完整的表。

注意！select语句查询的是列！

列的别名

列，也就是字段

1	select employee_id,last_name form employee;

select后返回的表称为结果集

共有三种方式起别名

1 2	select employee_id emp_id, last_name as lastname form employee;

空格和as语句都是给列起别名的操作，也就是说如此，寻找到employee_id,last_name字段后，结果集的列名将会临时显示成空格或as后的别名

最好要养成as的好习惯

实际上as是alias(别名)的简称

此外还可用“”双引号引起别名

1	select employee_id “emp_id” from employee;

这种方式一般使用在别名是多个单词时，引号还可以提高代码的可读性

1	select employee_id “emp id” from employee;

注意！

别名支持中文(UTF-8标准内)

在mysql中对于单引号和双引号的区别时不严格的，还是应该尽量遵循规则

去重

基本去重

select后，重复数据只留一条

1 2	select department_id from employees; select distinct department_id from employees;

对于代码1，查询的结果中有大量重复数据

对于代码2，在1的基础上使用了distinct关键字，作用是去掉重复的数据，对于重复数据结果集中只显示一份

联合去重

1	select distinct department_id, salary from employees;

这条语句中的distinct对后面两个字段都起作用，也就是返回 department_id相同而salary不同和department_id不同的所有结果，但实际使用的较少。

空值运算

空值，也就是null

首先null不等同于0，而是不存在的意思（Java）

所有null参与的运算，结果都为null

ex.

1
2
3

select id，salary”月工资“，
salary*（1+pet）*12”年工资“，pet”奖金“
from employee；

有pet字段表示奖金，其中表中存在无奖金也就是null的行（员工），对于上述运算，则其salary的结果为null，这显然不和常理。对于这种情况会使用if等语句解决（见单行函数）

着重

着重号”·“（键盘1左侧）

当在实际使用时，字段名和关键字等冲突时，就可使用着重号声明该字段不是一个关键字而是字段名

1 2	select * from order； select * from ·order；

常数字段

1	select ‘我是’，123 department_id from employees;

对于这句代码，department_id是存在于表中的，而前面的‘我是’，123这二者是显然不存在的，在返回结果集时，每一行结果前都会加上‘我是’和123这两个字段，这样这二者也被称为常数字段

注意，这里的‘xx’，单引号引起的是字符串

显示表结构

1	describe employees；

显示表的完整结构（字段名，字段类型，约束等）

1	desc employees；

desc是describe的简写，和describe的作用完全相同

过滤

关键字 where

1	select * from employees；

该语句得到的是完整的employees表数据。

使用where，返回符合某一条件的行，如

1	select * from employees where department_id =90;

在全表中查找，返回所有department_id为90的行。

注意，过滤结构where一定要声明在from后，在where中可以添加丰富的语句

运算

算术运算

+		加法
-		减法
*	  	乘法
/ 或 DIV	除法，其中div是取整，/为小数
% 或 MOD	求余

在sql中，注意与java区别，+‘加法符号’没有连接的作用

1 2	100+”1“=1001； //java 字符串加法使int自动提升为字符串，连接为长字符串

1
2
3

100+‘1’=101；	//sql
sql的加号只有运算的作用，并且引号内会转换为int进行运算
也就是隐式转换

sql里的字母字符，汉字字符等和数字运算时，字符被视作0（数字字符除外）

1	100+a =100；

null参与的运算结果都为null

1	100+null=null；

对于浮点类型，和java类似，涉及浮点数据的运算mysql也会自动提升转换，输出浮点数据。

在使用“/”的除法中，结果默认为浮点型（不同于java的截地板），使用DIV得到的结果则必为整型。当除法分母为0时，结果为null（MySQL中）

在取模运算，也就是取余中，得到的只是余数（类似java），其中取余结果的符号与取余符号的左侧相同。

比较运算

简述

共有三种结果，假（0），真（1），Null，类似c而不是java

比较运算符经常被用来作为SELECT查询语句的条件来使用

等号运算

等号运算符（=）判断等号两边的值、字符串或表达式是否相等，遵循以下规则

左右均为字符串，MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等
有null则null
整数和字符串共同存在则字符串转换为数字，非数字字符串会被转换成数字0，

注意！“=”是相等判断，“：=”才是赋值运算（也就是java的==）

ex.

1 2	select last_name,salary from employees where salary = 6000;

查询salary为6000的包含last_name和salary字段的结果

注意！对于null，若尝试使用null=null去查询，结果是空，因为null的任何等于运算结果都为null，而结果集只会返回比较结果为1的值，该问题可以用安全等于和符号运算等于解决。

安全等于

其实很直观的嘛，带了套就安全了

安全等于运算符“<=>”与等于运算符“=”的作用是相似的，区别在于是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为 1 ，而不为NULL；当一个操作数为NULL时，其返回值为 0 ，而不为NULL。

简单来说使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为 1 而不是NULL。

不等于

使用“！=”或<>来表示

符号运算

简述

is null系列

包括is null，is not null，isnull

is null和is not null是单目运算，如A is null，则为当A字段（值）为null返回1
isnull是一个函数，如isnull（A），当A字段（值）为null返回1

一般来说，对字段的查询多用is null，对数字多用等号和安全等号

注意！where后可接not或！表示否

ex.

1 2	select xxx from yyy where not （A <=>null）；

其中使用not时括号为了提高可读性而加的，而在使用！时必须加上，这里的A <=>null返回值会被not取反

极值

Least，求最小值

语法格式为：LEAST(值 1 ，值 2 ，…，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。

其中，当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

ex.

1 2	select least(first_name,last_name) from employees;

对于这些函数，调用时，指定传入first_name,last_name字段，此时表中拥有这两字段的行将会作为类传入这个函数中，也就是说，上面代码的意思是在整个表中，比较每一行的first_name,last_name，输出其中的较小值。

Greatest于Least大体相似

区间

between，一般和and一起使用

BETWEEN运算符使用的格式通常为

1	SELECT D FROM TABLE WHERE C BETWEEN A AND B;

此时，当C大于或等于A，并且C小于或等于B时，结果为 1 ，否则结果为 0，也就是说，这是一个闭区间。

ex.

1
2
3

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500 ;

In

包括in和not in，也就是属于和不属于，和between相似，但in更多的是对离散值的查找。

IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回 1 ，否则返回 0 。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。

ex.

1
2
3

SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN ( 100 , 101 , 201 );

而not in和in类似，如果不是IN列表中的一个值，则返回 1 ，否则返回 0 。

Like

使用

也就是模糊查询，通常用于字符串如果满足条件则返回 1 ，否则返回0 。通常如果给定的值或者匹配条件为NULL，则返回结果为NULL。

1
2
3

select last_name
from employees
where last_name = 'a';

以上是精确查询，返回所有last_name = ‘a’的行

而last常与%或_一起使用

1 2	“%”：匹配 0 个或多个字符 “_”：只能匹配一个字符

如apple和abandoned都可以表示为a%，eat可以表示为_a_或%a%

ex.

1
2
3

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

返回所有first_name为Sxxxxxxxx的结果

1
2
3

SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

返回所有满足xoxxxxxxx的结果

转义

当查询对象本身就是”_”或”%”时，使用转义字符”\"

1
2
3

SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;

也可以使用escape指定转义字符，如下代码则是让$成为了转义字符

1
2
3

SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;

逻辑运算

简述

注意！逻辑运算两侧表达式应该完整，如

1	WHERE manager_id IN 100 and manager_id IN；

非

NOT或!

表示当给定的值为 0 时返回 1 ；当给定的值为非 0 值时返回 0 ；当给定的值为NULL时，返回NULL

如

1 2	select xxx from yyy where not （A <=>null）；

1
2
3

SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

与

AND或&&

当给定的所有值均为非 0 值，并且都不为NULL时，返回1 ；当给定的一个值或者多个值为 0 时则返回 0 ；否则返回NULL

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
AND job_id LIKE '%MAN%';

或

OR或||

当给定的值都不为NULL，并且任何一个值为非 0 值时，则返回 1 ，否则返回 0 ；当一个值为NULL，并且另一个值为非 0 值时，返回 1 ，否则返回NULL；当两个值都为NULL时，返回NULL

异或

XOR

当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是 0 或者都不等于 0 时，则返回 0 ；如果一个值为 0 ，另一个值不为 0 时，则返回 1

位运算

简述

这里可以参见C

按位与

按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为 1 时，则该位返回 1 ，否则返回 0。

按位或

按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的二进制位的数值有一个或两个为 1 时，则该位返回 1 ，否则返回 0 。

按位异或

按位异或（^）运算符将给定的值对应的二进制数逐位进行逻辑异或运算。当给定值对应的二进制位的数值不同时，则该位返回 1 ，否则返回 0 。

按位取反

按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将 1 变为 0 ，将 0 变为 1 。

按位移动

按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用 0 补齐。

按位左移类似，只是方向改变

补充

语句

1	SELECT 10 & ~1;

由于按位取反（~）运算符的优先级高于按位与（&）运算符的优先级，所以 10 & ~ 1 ，首先，对数字 1 进行按位取反操作，结果除了最低位为 0 ，其他位都为 1 ，然后与 10 进行按位与操作，结果为 10。

运算优先级

数字编号越大，优先级越高

REGEXP

MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式，REGEXP运算符用来匹配字符串，语法格式为：expr REGEXP 匹配条件。如果expr满足匹配条件，返回1 ；如果不满足，则返回 0 。若expr或匹配条件任意一个为NULL，则结果为NULL。

注意！匹配条件要用‘xxxx’单引号括起

1	SELECT 'xxguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]‘

排序

排序规则

使用 ORDER BY 子句排序
- ASC（ascend）: 升序
- DESC（descend）:降序
ORDER BY 子句在SELECT语句的结尾

语法顺序应该是

select xxxx
from xxxx
where xxx in xxx
order by xxxx

执行上，顺序是

from xxx where xxxx
select（此时才可以指定别名
order by

单列排序

在没有使用排序时，是按照主键的升序来排序的

1 2	select employee_id,last_name, salary from employees;

上面是一个普通的查询语句，下面使用order by排序

1
2
3

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;

默认情况下使用的是升序（省略AES），可以在表名后加上DESC进行降序排列

1
2
3

SELECT employee_id, last_name, salary* 12 annsal
FROM employees
ORDER BY annsal;

注意！对于列的别名

只能在Order by语句中使用，在其他语句中如Where中都是无效的

多列排序

1
2
3

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

1
2
3

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id DESC, salary ASC;

这样，则是先按照department_id降序排序,当其相同再按照salary升序排序

分页

简述

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件

MySQL中使用 LIMIT 实现分页

1	LIMIT [位置偏移量,] 行数

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是 0 ，第二条记录的位置偏移量是1 ，以此类推）；第二个参数“行数”指示返回的记录条数

注意：LIMIT 子句必须放在整个SELECT语句的最后！

举例

--前 10 条记录：
SELECT * FROM 表名 LIMIT 0 , 10 ;
或者
SELECT * FROM 表名 LIMIT 10 ;

--第 11 至 20 条记录：
SELECT * FROM 表名 LIMIT 10 , 10 ;

--第 21 至 30 条记录：
SELECT * FROM 表名 LIMIT 20 , 10 ;

1
2
3

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，
意思是获取从第 5 条记录开始后面的 3 条记录，
和“LIMIT 4,3;”返回的结果相同

分页显示

分页显式公式：（当前页数- 1 ）*每页条数，每页条数

1 2	SELECT * FROM table LIMIT(PageNo - 1 )*PageSize,PageSize;

多表查询

一对多表

一个表中的字段，和其他表的字段建立了联系，表和表之间具有了一对多的关系

分表，目的是减少内存消耗，提高查询效率（参见优化）

1649562093193

笛卡尔积

简述

假设两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数

也称作交叉连接（CROSS JOIN）,其作用就是可以把任意表进行连接，即使这两张表不相关

1
2
3

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;

这里就涉及了笛卡尔积问题

可以看到每一个last_name都对应了多个department_name，这是因为last和每一个department都匹配了一次，这就是笛卡尔积错误

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM 
employees,departments;
SELECT last_name,department_name FROM 
employees CROSS JOIN departments;
SELECT last_name,department_name FROM 
employees INNER JOIN departments;
SELECT last_name,department_name FROM 
employees JOIN departments;

以上都是会出现笛卡尔积错误的情况

两表的连接条件

查询employes_id,department_name

1 2	SELECT last_name, department_name FROM employees, departments;

已知这样会产生笛卡尔积问题，则需要使用连接条件（筛选），得到想要的结果

1
2
3

SELECT last_name, department_name
FROM employees, departments
where employees.departments_name=departments.departments_name

1649562093193

如图，建立相等时的连接

指定

查询employes_id,department_name,dapartment_id；

1 2	SELECT last_name, department_name,dapartment_id FROM employees, departments;

此时则会报错，因为department_id在employees和department两表中均存在，对于这次查询来说department_id是模糊的，不知道该从那一张表查询

1649562093193

如图，同时存在，则需要指定查询表

1	employees.departments_id

指定在employees表中查询departments_id

为什么last_name, department_name可以不指定？因为他们在这次查询中是唯一存在在一张表中的不会模糊，但是实际上是否会模糊都建议加上表名（从sql优化的角度）

表的别名

表的别名可以在select和where里使用

SELECT e.employee_id, e.last_name, e.department_id,
	d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

由执行顺序

1
2
3

from xxx where xxxx
select（此时才可以指定别名
order by

可知在from中定义了表的别名后，在select和where等中都可以使用，注意！如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错

多表连接

例题

查询出公司员工的 last_name,department_name, city

SELECT employees.`department_id`,
employees.`last_name`,
departments.`department_name`,
locations.`city` 
FROM employees,
departments,locations
WHERE employees.`department_id`
= departments.`department_id`AND
departments.`location_id` = 
locations.`location_id`;

1649562093193

多（n）表连接需要至少n-1个连接条件，注意！连接条件需要逻辑运算符连接

非等值连接

在上面的连接语句中，使用的都是等于条件连接，这样的连接即等值连接，使用其他比较运算符即称为非等值连接

例

查询每位员工在Job_Grades中的等级

1
2
3

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

返回所有e.salary位于j.lowest_sal AND j.highest_sal之间的结果，当然也可以改写成大于等于小于等于符号；

最后还可以使用Order by排序

自连接

自我引用

上面的所有连接都是非自连接，是不同的表进行连接。同时，表中的数据是可以自我引用的，如

这张表里，每一个员工有一个id，员工也有一个主管，主管本身就是员工也有员工id，主管编号和员工编号构成了自我引用。

进行自连接

ex.

查询员工id，姓名和管理者的id，姓名。

这个操作可以试看成在两张employees表（物理上本质还是一张表）间连连接，左表的manager_id和右表的employee_id连接，

在操作中，为了区别左右表，需要给他们起别名

1	from employees worker,employees manager;

SELECT worker.employee_id,worker.last_name,
manager.employee_id,manager.last_name
FROM employees worker,employees manager
//建立连接
WHERE worker.`manager_id` = manager.`employee_id`;

结果

内连接和外连接

内连接

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。

简单来说就是结果集只返回满足查询条件的行。

对于上面非等值连接中的例子可知，存在没有manager的员工，也就是manager为null，这样的行，因为manager表中没有null行，所以其不会通过结果集返回。

外连接

简述

一般情况，查询所有（员工）的xxx则需要外连接

这里使用的是SQL99的语法

如左连接，多显示左表数据，这个时候右表中提取的数据数量就少了，右表不足的部分则返回空

使用的是join xxxx on语句进行外连接

左连接

左连接就是将JOIN前面的表中所有记录都展示出来。

Teachers教师表链接Students学生表，通过教师字段Tid连接。

1
2
3

SELECT *
FROM Teachers AS t，Students AS s
where t.Tid = s.Tid；

结果中只显示了张三和李四两个老师。

实际上，where就是”join”，也就是内连接，也就是说，使用where语句也能改写成

SELECT *
FROM Teachers AS t
JOIN Students AS s
ON t.Tid = s.Tid

这里的Join就是将两表连接，On，通过某某条件。

如果我们想要显示所有老师，不管这个老师有没有对应的学生。这个时候就应该用到LEFT JOIN 左连接了。将前面的Teachers表中所有记录都展示出来，也就是Join左侧的表全显示。

ex.

Teachers教师表连接Students学生表，通过教师字段Tid连接，老师要全部显示出来

SELECT *
FROM Teachers AS t
LEFT JOIN Students AS s
ON t.Tid = s.Tid

这样就建立了左连接，所有老师都显示出来了

右连接

右连接就是将JOIN后面的表中所有记录都展示出来。跟左连接是对应互通的。

比如我们可以将上面Teachers表和Students表互换，再将LEFT JOIN改成RIGHT JOIN，输出结果基本是一样的。

SELECT *
FROM Students AS s
RIGHT JOIN Teachers AS t
ON t.Tid = s.Tid

与LEFT JOIN输出的记录是一样的，唯一的区别是：

Students表中的列（Sid、Sname等）在前面，Teachers表中的列（Tid、Tname）在后面，因为Teacher在连接的右侧。

全连接

FYLL JOIN

MySQL中不支持 FULL JOIN，实际业务中，基本不用全连接。全连接的结果集结合了 LEFT JOIN 和 RIGHT JOIN 的结果集。简单来说就是不重要。

MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

Union

合并查询结果利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

1
2
3

SELECT column,... FROM table
UNION ALL
SELECT column,... FROM table

UNION 操作符返回两个查询的结果集的并集，去除重复记录。重复记录只保留一份。

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重，也就是交集部分出现两次。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。也就是说尽量使用union on；

joins

join可以连接多表，但超过三个表禁止使用join

1
2
3

select
from xxxx join xxxx on xxxx
join xxxx on xxxx；

由于mysql不支持full join，直接实现左下图和右下图是不行的。

但是使用union可以实现。

中图：内连接

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

左上图：左外连接

#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

右上图：右外连接

#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

左图：

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

对于这张图，假设A表查询其中的name，id两个字段，B表查询id，department两字段，通过id，AB表建立了连接。此时，做左连接，也就是A脚长，则id和department会使用null补偿，AB的交集必然是非null

对于上述代码，where筛选保留了department_id为null也就是补偿的行，而非null也就是AB共有的被筛去，留下A中的null；

简单来说，去掉公共部分，对于公共部分，department_id不可能为空。

右图：

SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

左下：满外连接

左图与右上图的union

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

右下：

左图与右中的union

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

自然连接

SQL 99 在 SQL 92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL 92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

在SQL 92 标准中：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL 99 中可以写成：

1 2	SELECT employee_id,last_name,department_name FROM employees e NATURAL JOIN departments d;

这样就自动实现了多表的等值连接

USING

当我们进行连接的时候，SQL 99 还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。比如：q

1
2
3

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的

1
2
3

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

也就是说，Using也是一种自动连接，输入字段名即可自动匹配（不能于自连接中使用），使用较少

成对比较

查询与 141 号员工的 manager_id,department_id
相同的其他员工的信息（同上）

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
					(SELECT manager_id, department_id
					FROM employees
					WHERE employee_id IN (141,174))
AND employee_id <> 141;

本质区别不大，前提是两个 where 的条件相同，则外查询 where 后括号内内列名可以和内查询 select 后的列名匹配，共有 where 的条件

having 子查询

只是将 where 查询改为了 having，本质区别不大

查询最低工资大于 50 号部门最低工资的部门的部门 id 和其最低工资

SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
			FROM employees
			WHERE department_id = 50);

case 子查询

显式员工的 employee_id,last_name 和 location。其中，若员工 department_id 与 location_id 为 1800 的 department_id 相同，则 location 为’Canada’，其余则为’USA’

SELECT employee_id, last_name,
		(CASE department_id
		WHEN
			(SELECT department_id FROM departments 
			WHERE location_id = 1800)
			THEN 'Canada' ELSE 'USA' END) location
FROM employees;

多行子查询

简述

也称为集合比较子查询
内查询返回多行
使用多行比较操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是 ANY 的别名，作用相同，一般常使用 ANY

这里要注意 any 和 all 的区别

这些运算符都搭配单行运算符使用

使用例

基本使用

返回其它 job_id 中比 job_id 为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及 salary

SELECT job_id,employee_id,last_name,salary
FROM employees
WHERE salary < ANY (
		SELECT salary
			FROM employees
			WHERE job_id = 'IT_PROG')
	AND job_id <> 'IT_PROG'

返回其它 job_id 中比 job_id 为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及 salary

SELECT job_id,employee_id,last_name,salary
FROM employees
WHERE salary < ALL (
		SELECT salary
			FROM employees
			WHERE job_id = 'IT_PROG')
	AND job_id <> 'IT_PROG'

当然也可使用单行函数

FROM 和子查询

注意！子查询结果可做 from 的表，或者说，查询结果就是一张表。

查询平均工资最低的部门 id

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) dept_avg_sal
			)

这里是把子查询的结果作为新表放到 from 后，在子查询结果集里查询

最内层

(
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
			) dept_avg_sal

查询平均工资，起别名为 avg_sal（这里是为了外层的 MIN 能够通过别名调用平均工资列），后以 department_id 分组，给结果集(表）起别名为 dept_avg_sal（必须给结果集起别名才能在 from 里使用）

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) dept_avg_sal
			)

在该结果集里找到最低工资（也就是最低平均工资），外层有平均工资等于该最低工资，找到最低平均工资的部门

#方式 2 ：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
)

方式二，也就是找到一个部门，他的平均工资小于等于所有（任意）部门的平均工资