3. 子查询、事务

1. 子查询

1.1 子查询是什么

​ 子查询就是在查询中嵌套另外一个查询的查询。子查询需要使用括号括起来。

​ 按照子查询出现的位置，我们可以将子查询分成4类，select型子查询、where型子查询、from型子查询、exists型子查询。

​ 按照子查询返回的结果集，我们可以将子查询分成

​ 标量子查询：返回单一值的标量，最简单的形式。

​ 列子查询：返回的结果集是 N 行一列。

​ 行子查询：返回的结果集是一行 N 列。

​ 表子查询：返回的结果集是 N 行 N 列。

​ 子查询不仅可以出现在select语句中、还可以出现在insert、update、delete、create table语句中。

1.2 select型子查询

​ select型子查询就是子查询出现在主查询的select字段列表中。

​ 例如有一个部门表、和员工表，部门表的部门ID字段与员工表中的部门ID字段存在1对多的关联。

drop table if exists dept;
create table dept (
  	dept_id int primary key auto_increment,
	dept_name varchar(20)
);

drop table if exists empl;
create table empl (
  	empl_id int primary key auto_increment,
	empl_name varchar(20),
	dept_id int
);

insert into dept(dept_name) values('开发部'), ('测试部'), ('实施运维部');
insert into empl(empl_name, dept_id) values('赵大',1), ('钱二',2), ('张三',3), ('李四', 1);

​ 需求：要查出每个部门的员工数有多少。

SELECT d.dept_name, ( 
    SELECT COUNT(*)
    FROM empl e
    WHERE e.dept_id = d.dept_id
 ) empl_num
 FROM dept d;

​ 当然，我们可以使用关联查询来处理，多数使用子查询的场合都可以使用关联查询来替代，具体使用哪种方式，取决于哪种方式更加清晰直观、运行速度更快。

​ select型子查询只支持标量子查询，子查询的数量没有限制。

select dept_name, (select 1) as a1, (select 2) as a2 from dept;
select dept_name, (select 1,2) as a1, (select 2) as a2 from dept;
-- 第2句sql是错的，Operand should contain 1 column(s)

1.3 where型子查询

​ 顾名思义，where型子查询就是子查询出现在主查询的where条件子句中。

​ 比如说where条件中，需要某字段值=从子查询中查出来的某一个值（标量子查询）；某字段的值，在子查询查出来的值区段内，in / not in 列子查询。

​ 需求：列出开发部的员工列表

select empl_id, empl_name from empl
where dept_id = (select dept_id from dept where dept_name like '%开发部%');

​ 需求，列出开发部与测试部的员工列表

select empl_id, empl_name from empl
where dept_id in (select dept_id from dept
  where dept_name like '%开发部%' or dept_name like '%测试部%');

​ 需求，列出不是开发部与测试部的员工列表

select empl_id, empl_name from empl
where dept_id not in (select dept_id from dept
  where dept_name like '%开发部%' or dept_name like '%测试部%');

​ where 型子查询，如果是 where 列 =（内层 sql） 则内层 sql 返回的必须是单行单列，单个值（标量子查询）。

​ where 型子查询，如果是 where 列 in / not in（内层 sql） 则内层 sql 返回的必须是单列，可以多行（列子查询）。

​ in / not in 不能使用索引，大数据量下通常会严重影响性能，慎重使用。

1.4 from型子查询

​ 需要理解一个概念：查询结果集在结构上可以当成表看，那就可以当成临时表对他进行再次查询：

​ 需求：列出开发部和测试部的姓李的员工列表

select empl_id, empl_name
from (select empl_id, empl_name from empl, dept
      where empl.dept_id = dept.dept_id
        and dept_name in('开发部','测试部') ) ta
where ta.empl_name like '李%';

​ 某些时候，单个sql无法完成的结果集，通过from型子查询，可以比较简明清晰地逼近需求结果，最终完成。

​ 在上面的例子中，from型子查询，是表子查询。但也可以是标量子查询。

select aaa from (select 1 as aaa) ta;

​ 也可以列子查询和行子查询。

​ 注意：将子查询结果充当一张表，要求必须起别名。

1.5 exists型子查询

​ exists型子查询，又叫相关子查询。

​ 例如，查询存在实际员工的部门名称

SELECT dept_name
FROM dept d
WHERE EXISTS(
    SELECT *
    FROM empl e
    WHERE d.dept_id=e.dept_id
);

​ 其语法是在where子句中，exists(完整的查询语句）。这时外层父查询先执行，在处理每一条记录时，执行子查询，子查询有结果返回，不是0条时，则此记录进入返回的结果集，否则将被剔除。

​ 又如：查询没有女朋友的男生信息

SELECT b.*
FROM boys b
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM girls g
    WHERE b.b_id=g.boyfriend_id
);

1.6 any、some和all关键字

​ 我们在where型子查询中，使用了 in 与 not in 关键字。这是一种集合处理。还有集合处理的关键字，也经常用到子查询中，就是 any 和 all 关键字（some是 any的同义词，较少使用）。

运算符\关键字	ANY	ALL
> 、>=	最小值	最大值
<、<=	最大值	最小值
=	任意值	\
<>、!=	\	任意值

​ 我们来看个例子

-- 创建一个职位表
CREATE TABLE salary_table(
  id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  position VARCHAR(40) NOT NULL,
  salary INT);
insert salary_table(position,salary) values('JAVA',8000),('Java',8400),('Java',9000),('Python',6500),('Python',10000),('Python',8900);
select *  from salary_table;

​ 需求：查找出高于 Python 职位任一职位工资的其它职位

SELECT * FROM salary_table
  WHERE salary > ANY( SELECT salary FROM salary_table WHERE position = 'Python')
    and position not like '%Python%';

​ 需求：查找出高于 Python 职位任何职位工资的其它职位

SELECT * FROM salary_table
  WHERE salary > all( SELECT salary FROM salary_table WHERE position = 'Python')
    and position not like '%Python%';

​ 需求：查找出等于Python 职位任何职位工资的其它职位

SELECT * FROM salary_table
  WHERE salary = any( SELECT salary FROM salary_table WHERE position = 'Python')
    and position not like '%Python%';

注意：= any 等价于 in，!= all 等价于 not in，= all 是没有 意义的。

2. 常用函数与分页查询

​ 函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。

​ 函数与存储过程的区别：函数只会返回一个值，不允许返回一个结果集。存储过程没有返回值，只是可以带返回参数。

2.1 数学函数

数学函数是MySQL中常用的一类函数。其主要用于处理数字，包括整型和浮点数等等。

函数	说明
ABS(X)	返回X的绝对值。
FLOOR(X)	返回不大于X的最大整数。
CEIL(X)、CEILING(X)	返回不小于X的最小整数。
TRUNCATE(X,D)	返回数值X保留到小数点后D位的值，截断时不进行四舍五入。
ROUND(X)	返回离X最近的整数，截断时要进行四舍五入。
ROUND(X,D)	保留X小数点后D位的值，截断时要进行四舍五入。
RAND()	返回0~1的随机数。
SIGN(X)	返回X的符号(负数，零或正)对应-1，0或1。
PI()	返回圆周率的值。默认的显示小数位数是6位。
POW(x,y)、POWER(x,y)	返回x的y次乘方的值。
SQRT(x)	返回非负数的x的二次方根。
EXP(x)	返回e的x乘方后的值。
MOD(N,M)	返回N除以M以后的余数。
LOG(x)	返回x的自然对数，x相对于基数2的对数。
LOG10(x)	返回x的基数为10的对数。
RADIANS(x)	返回x由角度转化为弧度的值。
DEGREES(x)	返回x由弧度转化为角度的值。
SIN(x)、ASIN(x)	前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦。
COS(x)、ACOS(x)	前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦。
TAN(x)、ATAN(x)	前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切。
COT(x)	返回给定弧度值x的余切。

-- rand获取到的是一个 0 ~ 1 之间的小数
select rand() from dual;
-- 如果要特定范围的随机整数，可以将此小数乘以特定值后取整
select round(rand()*100) from dual;

2.2 字符串函数

字符串函数是MySQL中最常用的一类函数。字符串函数主要用于处理表中的字符串。

函数	说明
CHAR_LENGTH(str)	计算字符串字符个数。
LENGTH(str)	返回值为字符串str的长度，单位为字节。
CONCAT(s1,s2，…)	返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL。
SUBSTRING(s,n,len)、MID(s,n,len)	两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串。
CONCAT_WS(x,s1,s2,…)	返回多个字符串拼接之后的字符串，每个字符串之间有一个x。
INSERT(s1,x,len,s2)	返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符。
INSTR(s1, s2)	返回字符串s2，在字符串s1中开始的位置，下标从1开始，没有找到返回0。
LOWER(str)、LCASE(str)	将str中的字母全部转换成小写。
UPPER(str)、UCASE(str)	将字符串中的字母全部转换成大写。
LEFT(s,n)、RIGHT(s,n)	前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符。
LPAD(s1,len,s2)、RPAD(s1,len,s2)	前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符。
LTRIM(s)、RTRIM(s)	前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除。
TRIM(s)	返回字符串s删除了两边空格之后的字符串。
TRIM(s1 FROM s)	删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格。
REPEAT(s,n)	返回一个由重复字符串s组成的字符串，字符串s的数目等于n。
SPACE(n)	返回一个由n个空格组成的字符串。
REPLACE(s,s1,s2)	返回一个字符串，用字符串s2替代字符串s中所有的字符串s1。
STRCMP(s1,s2)	若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1。
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)	三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）。
REVERSE(s)	将字符串s反转。
ELT(N,str1,str2,str3,str4,…)	返回第N个字符串。
FIELD(s,s1,s2,…)	返回第一个与字符串s匹配的字符串的位置。
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置。
MAKE_SET(x,s1,s2,…)	按x的二进制数从s1，s2…，sn中选取字符串

重要的函数示例

SELECT CONCAT('aaa', 'bbb', 'ccc');
-- concat是连接字符串，mysql中concat支持3个参数，oracle中只有2个参数的版本
select substring('12345', 2) from dual;
-- substring带两个参数时，表示从第一个参数字符串中截取，从第二个参数位置字符开始（包含第二个参数位置的字符），到字符串尾

select substring('12345', 2,2) from dual;
-- substring带两个参数时，表示从第一个参数字符串中截取，从第二个参数位置字符开始（包含第二个参数位置的字符）
-- 截取第三个参数所指长度的字符

2.3 日期和时间函数

日期和时间函数是MySQL中另一最常用的函数。其主要用于对表中的日期和时间数据的处理。

函数	说明
CURDATE()、CURRENT_DATE()	返回当前日期，格式：yyyy-MM-dd。
CURTIME()、CURRENT_TIME()	返回当前时间，格式：HH:mm:ss。
**NOW()、CURRENT_TIMESTAMP()、**LOCALTIME()、SYSDATE()、LOCALTIMESTAMP()	返回当前日期和时间，格式：yyyy-MM-dd HH:mm:ss。
DATEDIFF(d1,d2)	计算日期d1与d2之间相隔的天数。
ADDDATE(d,n)	计算起始日期d加上n天的日期，n为正数向后推，负数向前。
DATE_FORMAT(d,f)	按照表达式 f 的要求显示日期d。
TIME_FORMAT(t,f)	按照表达式 f 的要求显示时间t。
STR_TO_DATE(s,f)	按照表达式 f 的要求，转换字符串s成日期时间类型。
UNIX_TIMESTAMP()	返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数。
UNIX_TIMESTAMP(date)	返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数。
FROM_UNIXTIME(date)	和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间。
UTC_DATE()	返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。
UTC_TIME()	返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中
MONTH(d)	返回日期d中的月份值，范围是1~12。
MONTHNAME(d)	返回日期d中的月份名称，如：January、February等。
DAYNAME(d)	返回日期d是星期几，如：Monday、Tuesday等。
DAYOFWEEK(d)	返回日期d是星期几，如：1表示星期日，2表示星期一等。
WEEKDAY(d)	返回日期d是星期几，如：0表示星期一，1表示星期二等。
WEEK(d)	计算日期d是本年的第几个星期，范围是0~53。
WEEKOFYEAR(d)	计算日期d是本年的第几个星期，范围是1~53。
DAYOFYEAR(d)	计算日期d是本年的第几天。
DAYOFMONTH(d)	计算日期d是本月的第几天。
YEAR(d)	返回日期d中的年份值。
QUARTER(d)	返回日期d是第几季度，范围是1~4。
HOUR(t)	返回时间t中的小时值。
MINUTE(t)	返回时间t中的分钟值。
SECOND(t)	返回时间t中的秒钟值。
EXTRACT(type FROM date)	从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
TIME_TO_SEC(t)	将时间t转换为秒。
SEC_TO_TIME(s)	将以秒为单位的时间s转换为时分秒的格式。
TO_DAYS(d)	计算日期d至0000年1月1日的天数。
FROM_DAYS(n)	计算从0000年1月1日开始n天后的日期。
ADDDATE(d,INTERVAL expr type)	计算起始日期d加上一个时间段后的日期。
DATE_ADD(d,INTERVAL expr type)	同ADDDATE(d,INTERVAL expr type)
SUBDATE(d,n)	计算起始日期d减去n天的日期。
SUBDATE(d,INTERVAL expr type)	计算起始日期d减去一个时间段后的日期。
ADDTIME(t,n)	计算起始时间t加上n秒的时间。
SUBTIME(t,n)	计算起始时间t减去n秒的时间。
DATE_FORMAT(d,f)	按照表达式 f 的要求显示日期d。
TIME_FORMAT(t,f)	按照表达式 f 的要求显示时间t。
GET_FORMAT(type, s)	根据字符串s获取type类型数据的显示格式。

参数说明：

type格式：

SECOND 秒 SECONDS

MINUTE 分钟 MINUTES

HOUR 时间 HOURS

DAY 天 DAYS

MONTH 月 MONTHS

<