笔记三 - 《MySQL 学习笔记》

mysql常⽤函数汇总
深⼊了解连接查询及原理
⼦查询
子查询效率
细说NULL导致的神坑

mysql常⽤函数汇总

数值型函数
- abs 求绝对值 select abs(5),abs(-2.4),abs(-24),abs(0);
- sqrt 求⼆次⽅根 select sqrt(25),sqrt(120),sqrt(-9);，负数没有平⽅根，返回结果为 NULL
- mod 求余数 select mod(63,8),mod(120,10),mod(15.5,3);，对于带有⼩数部分的数值也起作用
- ceil和ceiling 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整 select ceil(-2.5),ceiling(2.5);
- floor 向下取整，返回值转化为⼀个BIGINT select floor(5),floor(5.66),floor(-4),floor(-4.66);
- rand ⽣成⼀个0~1之间的随机数；传⼊整数参数会将该值作为随机数发生器的种子，⽤来产⽣可以复现的序列；可以使用 ORDER BY RAND() 来对一组记录进行随机化排列 select rand(), rand(2), rand(1);
- round 对所传参数进⾏四舍五⼊，round(-6.66),round(3.33,3),round(88.66,-1),round(88.46,-2);，ROUND(x,y) 返回值保留⼩数点后⾯指定的y位，y默认为0
- sign 返回参数的符号，x 的值为负、零和正时返回结果依次为 -1、0 和 1
- pow和power 两个函数的功能相同，都是所传参数的次⽅的结果值
- sin 求正弦值，SIN(x) 其中 x 为弧度值
- asin 求反正弦值，与函数SIN互为反函数
- cos 求余弦值
- acos 求反余弦值，与函数COS互为反函数
- tan 求正切值
- atan 求反正切值，与函数TAN互为反函数
- cot 求余切值
字符串函数
- length 返回字符串直接⻓度，即字节长度，使⽤ uft8 编码字符集时，⼀个汉字是 3 个字节，⼀个数字或字母是⼀个字节 select length('javacode2018'),length('路⼈甲Java'),length('路⼈');，结果是 12 13 6
- concat 合并字符串，CONCAT(sl, s2, ...) 函数返回结果为连接参数产⽣的字符串，或许有⼀个或多个参数。若有任何⼀个参数为 NULL，则返回值为 NULL。
- insert 替换字符串，INSERT(s1, x, len, s2) 返回字符串 s1，⼦字符串起始于 x 位置，并且⽤ len 个字符长的字符串代替 s2
  - x的值从1开始，第⼀个字符的x=1，若超过字符串长度，则返回值为原始字符串
  - 假如 len 的长度大于其他字符串的长度，则从位置 x 开始替换。
  - 若任何⼀个参数为 NULL，则返回值为 NULL
- lower：LOWER(str) 可以将字符串 str 中的字母字符全部转换成⼩写
- upper：UPPER(str) 可以将字符串 str 中的字母字符全部转换成⼤写
- left：LEFT(s，n) 函数返回字符串 s 最左边的 n 个字符，s=1表⽰第⼀个字符
- right：RIGHT(s，n) 函数返回字符串 s 最右边的 n 个字符
- trim：TRIM(s) 删除字符串 s 两侧的空格
- replace：REPLACE(s，s1，s2) 使⽤字符串 s2 替换字符串 s 中所有的字符串 s1
- substr 和 substring，substr()是substring()的同义词
  - substr(str,pos) 或 substr(str from pos) 字符串str从位置pos开始返回⼀个⼦字符串
  - substr(str,pos,len) 或 substr(str from pos for len) 字符串str从位置pos开始返回长度为len的⼦字符串
  - 使⽤FROM的形式是标准的SQL语法
  - 也可以对pos使⽤负值，在这种情况下，⼦字符串的开头是字符串末尾的pos字符，⽽不是开头。在这个函数的任何形式中pos可以使⽤负值
  - 对于所有形式的substring()，从中提取⼦串的字符串中第⼀个字符的位置被认为是1
- reverse：REVERSE(s) 可以将字符串 s 反转，返回的字符串的顺序和 s 字符串的顺序相反

-- insert 语法
-- 路**va 路⼈甲Java 路⼈**
select insert('路⼈甲Java', 2, 4, '**') AS col1,
-- 将“路⼈甲Java”从第 2 个字符开始长度为 4 的字符串替换为 **
insert('路⼈甲Java', -1, 4,'**') AS col2,
-- 起始位置 -1 超出了字符串长度，直接返回原字符串
insert('路⼈甲Java', 3, 20,'**') AS col3;
-- 替换长度超出了原字符串长度，则从第 3 个字符开始，截取后面所有的字符，并替换为指定字符 **
-- substr 和 substring 语法
/** 第三个字符之后的⼦字符串：inese **/
SELECT substring('chinese', 3);
/** 倒数第三个字符之后的⼦字符串：ese **/
SELECT substring('chinese', -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese', 3, 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese', -3, 2);
/** 第三个字符之后的⼦字符串：inese **/
SELECT substring('chinese' FROM 3);
/** 倒数第三个字符之后的⼦字符串：ese **/
SELECT substring('chinese' FROM -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese' FROM 3 FOR 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese' FROM -3 FOR 2);

⽇期和时间函数
- curdate 和 current_date：两个函数作⽤相同，返回当前系统的⽇期值，将当前⽇期按照“YYYY-MMDD”或“YYYYMMDD”格式的值返回，具体格式根据函数⽤在字符串或数字语境中⽽定，返回的date类型
- curdme 和 current_dme：获取系统当前时间，将当前时间以“HH：MM：SS”或“HHMMSS”格式返回，具体格式根据函数⽤在字符串或数字语境中⽽定，返回time类型
- now 和 sysdate：获取当前时间⽇期，格式为 “YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具体格式根据函数⽤在字符串或数字语境中⽽定，返回datetime类型
- unix_dmestamp：UNIX_TIMESTAMP(date) 若⽆参数调⽤，返回⼀个⽆符号整数类型的 UNIX 时间戳（’1970-01-01 00:00:00’GMT之后的秒数）
- from_unixdme：FROMUNIXTIME(unixtimestamp[,format]) 函数把 UNIX 时间戳转换为普通格式的⽇期时间值，与 UNIX_TIMESTAMP () 函数互为反函数
  - unix_timestamp：时间戳（秒）
  - format：要转化的格式⽐如“1568710866”, select from_unixtime(1568710866),from_unixtime(1568710866,'%Y-%m-%d %H:%i:%s'); 这样格式化之后的时间就是 2019-09-17 17:01:06
- month：MONTH(date) 函数返回指定 date 对应的⽉份，范围为 1～12。 select month('2017-12-15'),month(now()) 结果为 12 9
- monthname：MONTHNAME(date) 函数返回⽇期 date 对应⽉份的英⽂全名
- dayname：DAYNAME(date) 函数返回 date 对应的⼯作⽇英⽂名称，例如 Sunday、Monday等
- dayofweek：DAYOFWEEK(d) 函数返回 d 对应的⼀周中的索引（位置）。1 表⽰周⽇，2 表示周⼀，……，7 表⽰周六。这些索引值对应于ODBC标准
- week：WEEK(date[,mode]) 函数计算⽇期 date 是⼀年中的第⼏周。WEEK(date,mode)函数允许指定星期是否起始于周⽇或周⼀，以及返回值的范围是否为 0～52 或 1～53
  - date是要获取周数的⽇期
  - mode是⼀个可选参数，⽤于确定周数计算的逻辑。它允许您指定本周是从星期⼀还是星期⽇开始，返回的周数应在0到52之间或0到53之间。如果忽略mode参数，默认情况下WEEK函数将使⽤default_week_format系统变的值，查看命令 SHOW VARIABLES LIKE 'default_week_format';
- dayofyear：DAYOFYEAR(date) 函数返回 d 是⼀年中的第⼏天，范围为 1～366
- dayofmonth：DAYOFMONTH(date) 函数返回 d 是⼀个⽉中的第⼏天，范围为 1～31
- year：YEAR() 函数可以从指定⽇期值中来获取年份值
- timetosec：TIMETOSEC(time) 函数返回将参数 time 转换为秒数的时间值，转换公式为“⼩时×3600+ 分钟 ×60+ 秒”
- sectotime：SECTOTIME(seconds) 函数返回将参数 seconds 转换为⼩时、分钟和秒数的时间值
- date_add和adddate：向⽇期添加指定时间间隔 DATE_ADD(date,INTERVAL expr type)
  - date：参数是合法的⽇期表达式。expr 参数是您希望添加的时间间隔
  - type 可以是 MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
- date_sub和subdate：⽇期减法运算 DATE_SUB(date,INTERVAL expr type)
- addtime：ADDTIME(time,expr) 函数⽤于执⾏时间的加法运算。添加 expr 到 time 并返回结果。其中：time 是⼀个时间或⽇期时间表达式，expr 是⼀个时间表达式
- subdme：SUBTIME(time,expr) 函数⽤于执⾏时间的减法运算
- datediff：DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数
- date_format：DATE_FORMAT(date，format) 函数是根据 format 指定的格式显⽰ date 值
  - date 是要格式化的有效⽇期值format：是由预定义的说明符组成的格式字符串，每个说明符前⾯都有⼀个百分⽐字符(%)。
  - format：格式和上⾯的函数from_unixtime中的format⼀样，可以参考上⾯的
- weekday：WEEKDAY(date) 返回date的星期索引(0=星期⼀，1=星期⼆, ……6= 星期天)

-- 2019-09-17  2019-09-17  20190918
select curdate(),current_date(),current_date()+1;
-- 16:11:25  16:11:25  161126
select curtime(),current_time(),current_time()+1;
-- 2019-09-17 16:13:28  2019-09-17 16:13:28
select now(),sysdate();
-- 1612687535  1612687535  2021-02-07 16:45:35.0  1568692800
select unix_timestamp(),unix_timestamp(now()),now(),unix_timestamp('2019-09-17 12:00:00');
-- 2019-09-19 00:01:00  15:41:36
select addtime('2019-09-18 23:59:59','0:1:1'), addtime('10:30:59','5:10:37');

聚合函数见笔记2
流程控制函数
- if: IF(expr,v1,v2) 当 expr 为真是返回 v1 的值，否则返回 v2
- ifnull：IFNULL(v1,v2)：v1为空返回v2，否则返回v1
- case：类似于java中的if..else if..else

-- 1  x  yes
-- STRCMP(str1, str2) 比较两个字符串，如果这两个字符串相等返回0，如果第一个参数是根据当前的排序顺序比第二较小则返回-1，否则返回1
select if(1<2,1,0) c1,if(1>5,'√','×') c2,if(strcmp('abc','ab'),'yes','no') c3;
-- 路⼈甲Java ⾮空
select ifnull(null,'路⼈甲Java'),ifnull('⾮空','为空');
CASE <表达式>
WHEN <值1> THEN <操作>
WHEN <值2> THEN <操作>
...
ELSE <操作>
END CASE;
-- 或
CASE
WHEN <条件1> THEN <命令>
WHEN <条件2> THEN <命令>
...
ELSE commands
END CASE;
SELECT
t.name 姓名,
(CASE t.sex
WHEN 1 THEN '男'
WHEN 2 THEN '⼥'
ELSE '未知'
END) 性别
FROM t_stu t;
-- 或
SELECT
t.name 姓名,
(CASE
WHEN t.sex = 1 THEN '男'
WHEN t.sex = 2 THEN '⼥'
ELSE '未知'
END) 性别
FROM t_stu t;

其他函数

-- 数据库版本号，5.7.25-log
SELECT version();
-- 当前的数据库，javacode2018
SELECT database();
-- 当前连接⽤户，root@localhost
SELECT user();
-- 返回字符串密码形式，*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
SELECT password('123456');
-- 返回字符串的 md5数据，e10adc3949ba59abbe56e057f20f883e
SELECT md5('123456');
-- AES加密解密函数
-- MySQL数据库提供了AES加密和解密的函数，所以数据的加密解密非常容易实现
-- AES解密要使用与加密相同的秘钥，才能解密出原始数据
AES_ENCRYPT(原始数据, 秘钥字符串);
<!--显示时加密结果是2机制数据，会出现乱码；使用HEX函数将其转成16进制来显示-->
SELECT HEX(AES_ENCRYPT('你好世界', 'ABC123456'));
AES_DECRYPT(加密结果，秘钥字符串)
<!--UNHEX将16进制数据转换回2进制-->
SELECT AES_DECRYPT(UNHEX('E85A104B6142A7375E53C0545CAD48EE'), 'ABC123456');

深⼊了解连接查询及原理

笛卡尔积：有两个集合A和B，笛卡尔积表⽰A集合中的元素和B集合中的元素
任意相互关联产⽣的所有可能的结果
内连接
- select 字段 from 表1 inner join 表2 on 连接条件;
- select 字段 from 表1 join 表2 on 连接条件;
- select 字段 from 表1, 表2 [where 关联条件];
- 内连接相当于在笛卡尔积的基础上加上了连接的条件
- 当没有连接条件的时候，内连接上升为笛卡尔
- 内连接建议使⽤第3种语法，简洁：select 字段 from 表1, 表2 [where 关联条件];

-- 有连接条件，查询员⼯及所属部门
select t1.emp_name,t2.team_name from t_employee t1 inner join
t_team t2 on t1.team_id = t2.id;
-- or
select t1.emp_name,t2.team_name from t_employee t1 join t_team
t2 on t1.team_id = t2.id;
-- or
select t1.emp_name,t2.team_name from t_employee t1, t_team t2
where t1.team_id = t2.id;
-- ⽆连接条件，上升为笛卡尔积
select t1.emp_name,t2.team_name from t_employee t1 inner join
t_team t2;
-- 组合条件进⾏查询
-- on中使⽤了组合条件
select t1.emp_name,t2.team_name from t_employee t1 inner join
t_team t2 on t1.team_id = t2.id and t2.team_name = '架构组';
-- or，在连接的结果之后再进⾏过滤，相当于先获取连接的结果，然后使⽤where中的条件再对连接结果进⾏过滤
select t1.emp_name,t2.team_name from t_employee t1 inner join
t_team t2 on t1.team_id = t2.id where t2.team_name = '架构组';
-- or，直接在where后⾯进⾏过滤
select t1.emp_name,t2.team_name from t_employee t1, t_team t2
where t1.team_id = t2.id and t2.team_name = '架构组';

外连接
- 外连接涉及到2个表，分为：主表和从表，要查询的信息主要来⾃于哪个表，谁就是主表
- 外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。
- 外连接查询结果 = 内连接的结果 + 主表中有的⽽内连接结果中没有的记录
- 外连接分为2种
  - 左外链接：使⽤left join关键字，left join左边的是主表
  - 右外连接：使⽤right join关键字，right join右边的是主表
- 左连接 select 列 from 主表 left join 从表 on 连接条件;
- 右连接 select 列 from 从表 right join 主表 on 连接条件;

-- 查询员⼯姓名、组名，返回组名不为空的记录
SELECT
    t1.emp_name,
    t2.team_name
FROM
    t_employee t1
LEFT JOIN
    t_team t2
ON
    t1.team_id = t2.id
WHERE
    t2.team_name IS NOT NULL;
-- 使⽤右连接来实现上⾯左连接实现的功能
SELECT
    t2.team_name,
    t1.emp_name
FROM
    t_team t2
RIGHT JOIN
    t_employee t1
ON
    t1.team_id = t2.id
WHERE
    t2.team_name IS NOT NULL;

⼦查询

数据见：dump-javacode2018_employees-202102081532.sql
⼦查询：出现在select语句中的select语句，称为⼦查询或内查询；外部的select查询语句，称为主查询或外查询
⼦查询分类
- 按结果集的⾏列数不同分为4种
  - 标量⼦查询（结果集只有⼀⾏⼀列）
  - 列⼦查询（结果集只有⼀列多⾏）
  - ⾏⼦查询（结果集有⼀⾏多列）
  - 表⼦查询（结果集⼀般为多⾏多列）
- 按⼦查询出现在主查询中的不同位置分
  - select后⾯：仅仅⽀持标量⼦查询
  - from后⾯：⽀持表⼦查询
  - where或having后⾯：⽀持标量⼦查询（单列单⾏）、列⼦查询（单列多⾏）、⾏⼦查询（多列多⾏）
  - exists后⾯（即相关⼦查询）：表⼦查询（多⾏、多列）
列⼦查询，⼀般搭配着多⾏操作符使⽤
- in：in常⽤于where表达式中，其作⽤是查询某个范围内的数据
- any和some⼀样：可以与=、>、>=、<、<=、<>结合起来使⽤，分别表⽰等于、⼤于、⼤于等于、⼩于、⼩于等于、不等于其中的任何⼀个数据
- all：可以与=、>、>=、<、<=、<>结合是来使⽤，分别表⽰等于、⼤于、⼤于等于、⼩于、⼩于等于、不等于其中的其中的所有数据
exists后⾯（也叫做相关⼦查询）
- exists查询结果：1或0，exists查询的结果⽤来判断⼦查询的结果集中是否有值
- ⼀般来说，能⽤exists的⼦查询，绝对都能⽤in代替，所以exists⽤的少
- 先执⾏主查询，然后主查询查询的结果，再根据⼦查询进⾏过滤，⼦查询中涉及到主查询中⽤到的字段，所以叫相关⼦查询
NULL大坑
- 字段值为NULL的时候，not in查询有⼤坑
- 建议创建表的时候，列不允许为空

-- select后⾯的⼦查询
-- 查询每个部门员⼯个数
SELECT a.*,
    (SELECT count(*) FROM employees b
    WHERE b.department_id = a.department_id) AS 员⼯个数
FROM departments a;
-- 查询员⼯号=102的部门名称
SELECT (SELECT a.department_name
    FROM departments a, employees b
    WHERE a.department_id = b.department_id
    AND b.employee_id = 102) AS 部门名;
-- from后⾯的⼦查询（将⼦查询的结果集充当⼀张表，要求必须起别名，否者这个表找不到。然后将真实的表和⼦查询结果表进⾏连接查询）
-- 查询每个部门平均⼯资
SELECT
    t1.department_id,
    sa AS '平均⼯资',
    t2.grade_level
FROM (SELECT
    department_id,
    avg(a.salary) sa
    FROM employees a
    GROUP BY a.department_id) t1, job_grades t2
WHERE
    t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
-- where后的标量⼦查询
-- ⼀般标量⼦查询，查询谁的⼯资⽐Abel的⾼
SELECT *
FROM employees a
WHERE a.salary > (SELECT salary
    FROM employees
    WHERE last_name = 'Abel');
-- 多个标量⼦查询，返回jobid与141号员⼯相同，salary⽐143号员⼯多的员⼯、姓名、jobid和⼯资
SELECT
    a.last_name 姓名,
    a.job_id,
    a.salary ⼯资
FROM employees a
WHERE a.job_id = (SELECT job_id
        FROM employees
        WHERE employee_id = 141)
    AND
        a.salary > (SELECT salary
        FROM employees
        WHERE employee_id = 143);
-- ⼦查询+分组函数
-- 查询最低⼯资⼤于50号部门最低⼯资的部门id和其最低⼯资【having】
SELECT
    min(a.salary) minsalary,
    department_id
FROM employees a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT min(salary)
        FROM employees
        WHERE department_id = 50);
-- 错误的标量⼦查询
-- 将上⾯的⽰例中⼦查询语句中的min(salary)改为salary，⼦查询返回的结果超过了1⾏记录
SELECT
    min(a.salary) minsalary,
    department_id
FROM employees a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT salary
        FROM employees
        WHERE department_id = 500000);
-- 列⼦查询(⼦查询结果集⼀列多⾏)
-- 列⼦查询需要搭配多⾏操作符使⽤：in(not in)、any/some、all。
-- 为了提升效率，最好去重⼀下distinct关键字，用于返回唯一不同的值
-- 示例：返回location_id是1400或1700的部门中的所有员⼯姓名
SELECT a.last_name
FROM employees a
WHERE a.department_id IN (SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN (1400, 1700));
SELECT a.last_name
FROM employees a
WHERE a.department_id = ANY (SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN (1400, 1700));
-- 拓展，下⾯与not in等价
SELECT a.last_name
FROM employees a
WHERE a.department_id <> ALL (SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN (1400, 1700));
-- 示例：返回其他⼯种中⽐jobid为'ITPROG'⼯种任意⼯资低的员⼯的员⼯号、姓名、job_id、salary
SELECT
    last_name,
    employee_id,
    job_id,
    salary
FROM employees
WHERE salary < ANY (SELECT DISTINCT salary
        FROM employees
        WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
-- 或
SELECT
    last_name,
    employee_id,
    job_id,
    salary
FROM employees
WHERE salary < (SELECT max(salary)
        FROM employees
        WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
-- 返回其他⼯种中⽐jobid为'ITPROG'部门所有⼯资低的员⼯的员⼯号、姓名、job_id、salary
SELECT
    last_name,
    employee_id,
    job_id,
    salary
FROM employees
WHERE salary < ALL (SELECT DISTINCT salary
        FROM employees
        WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
-- 或
SELECT
    last_name,
    employee_id,
    job_id,
    salary
FROM employees
WHERE salary < (SELECT min(salary)
        FROM employees
        WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
-- ⾏⼦查询（⼦查询结果集⼀⾏多列）
-- 查询员⼯编号最⼩并且⼯资最⾼的员⼯信息
SELECT *
FROM employees a
WHERE a.employee_id = (SELECT min(employee_id)
        FROM employees)
    AND salary = (SELECT max(salary)
        FROM employees);
- exists 相关⼦查询
-- 查询所有员⼯的部门名称
SELECT department_name
FROM departments a
WHERE exists(SELECT 1
    FROM employees b
    WHERE a.department_id = b.department_id);
-- 或使⽤in实现
SELECT department_name
FROM departments a
WHERE a.department_id IN (SELECT department_id
    FROM employees);
-- 查询没有员⼯的部门
SELECT *
FROM departments a
WHERE NOT exists(SELECT 1
    FROM employees b
    WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL);
-- 或使⽤in实现
SELECT *
FROM departments a
WHERE a.department_id NOT IN (SELECT department_id
    FROM employees b
    WHERE b.department_id IS NOT NULL);
-- 使用IS NOT NULL是因为⼦查询中列的值为NULL的时候，外查询的结果为空；建议是建表事，列不允许为空

子查询效率

子查询是一种查询中嵌套查询的语句
子查询可以写在三个地方：WHERE子句、FROM子句、SELECT子句，但是只有FROM子句子查询是最可取的
WHERE子查询：这种查询最简单，最容易理解，但是却是效率很低的子查询
FROM子查询：这种子查询只会执行一次，所以查询效率很高
SELECT子查询：这种子查询每输出一条记录的时候都要执行一次，查询效率很低
单行子查询&多行子查询
- 单行子查询的结果集只有一条记录，多行子查询结果集有多行记录
- 多行子查询只能出现在WHERE子句和FROM子句中
WHERE子句中的多行子查询
- WHERE子句中，可以用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断
- EXISTS：把在子查询之外的条件判断，写到了子查询的里面

-- WHERE子查询
-- 查询底薪超过公司平均底薪的员工的信息
-- 比较每条记录都要重新执行子查询
SELECT empno, ename, sal
FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp);
-- FROM子查询
SELECT e.empno, e.ename, e.sal, t.avg
FROM t_emp e JOIN
(SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal >= t.avg;
-- SELECT子查询
SELECT e.empno, e.ename, (SELECT dname FROM t_dept WHERE deptno = e.deptno) AS dname
FROM t_emp e;
-- 用WHERE子查询查找FORD和MARTIN两个的同事
SELECT ename
FROM t_emp
WHERE
deptno IN
(SELECT deptno FROM t_emp WHERE ename IN("FORD", "MARTIN"))
AND ename NOT IN("FORD", "MARTIN");
-- 查询比FORD和MARTIN底薪都高的员工信息，使用ANY意思就是比任何一个人高就可以
SELECT ename FROM t_emp
WHERE sal >= ALL
(SELECT sal FROM t_emp WHERE ename IN("FORD", "MARTIN"))
AND ename NOT IN("FORD", "MARTIN");
-- EXISTS语法如下
SELECT ... FROM 表名 WHERE [NOT] EXISTS(子查询);
-- 查询工资等级是3级或者4级的员工信息
SELECT empno, ename, sal
FROM t_emp
WHERE EXISTS(
    SELECT * FROM t_salgrade WHERE sal BETWEEN losal AND hisal
    AND grade IN(3, 4)
);

细说NULL导致的神坑

⽐较运算符中使⽤NULL：任何值和NULL使⽤运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/
some、all）⽐较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0
IN、NOT IN和NULL⽐较
- 当IN和NULL⽐较时，⽆法查询出为NULL的记录
- 当NOT IN后⾯有NULL值时，不论什么情况下，整个sql的查询结果都为空
判断NULL只能⽤IS NULL、IS NOT NULL
聚合函数中NULL的坑：count(字段)⽆法统计字段为NULL的值，count(*)可以统计值为null的⾏
NULL不能作为主键的值

-- ⽐较运算符中使⽤NULL
-- NULL
select 1>=NULL;
-- NULL  NULL  NULL  NULL
select 1 in (null),1 not in (null),null in (null),null not in (null);
-- NULL  NULL
select 1=any(select null),null=any(select null);
-- NULL  NULL
select 1=all(select null),null=all(select null);
-- IN和NULL⽐较
-- Empty set
select * from test1 where a in (null);
-- [[1,1],[1,null]]
select * from test1 where a in (null,1);
-- NOT IN和NULL⽐较
-- Empty set
select * from test1 where a not in (null);
-- Empty set
select * from test1 where a not in (null,2);
-- [[1,1],[1,null]]
select * from test1 where a not in (2);
-- EXISTS、NOT EXISTS和NULL⽐较
-- 复制表test1创建表test2,查询语句中使⽤exists、notexists对⽐test1.a=test2.a，因为=不能⽐较NULL
-- [[1,1],[1,null]]
select * from test1 t1 where exists (select * from test2 t2 where t1.a = t2.a);
-- [null,null]
select * from test1 t1 where not exists (select * from test2 t2 where t1.a = t2.a);
-- 判断NULL只能⽤IS NULL、IS NOT NULL
-- 1
select 1 is not null;
-- 0
select 1 is null;
-- 1
select null is null;
-- 0
select null is not null;
-- 聚合函数中NULL的坑（count(字段)⽆法统计字段为NULL的值，count(*)可以统计值为null的⾏）
-- [2, 1, 3]，a字段为NULL的1行没有统计出来，b为NULL的2⾏记录没有统计出来
select count(a),count(b),count(*) from test1;
-- [NULL, NULL]
select * from test1 where a is null;
-- 0
select count(a) from test1 where a is null;

总结
- NULL作为布尔值的时候，不为1也不为0
- 任何值和NULL使⽤运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all），返回值都为NULL
- 当IN和NULL⽐较时，⽆法查询出为NULL的记录
- 当NOT IN 后⾯有NULL值时，不论什么情况下，整个sql的查询结果都为空
- 判断是否为空只能⽤IS NULL、IS NOT NULL
- count(字段)⽆法统计字段为NULL的值，count(*)可以统计值为null的⾏
- 当字段为主键的时候，字段会⾃动设置为not null
- NULL导致的坑让⼈防不胜防，强烈建议创建字段的时候字段不允许为NULL，给个默认值