查询篇终于学习完了，里面涉及了一些算法和思维，应该是mysql里面特别重要的一部分，明天有时间的话开新篇章
/*
子查询
出现在其他语句中的select语句，称为子查询或内查询
分类：
按子查询出现的位置
select 后面
from 后面表子查询
where 或 having 后面标量子查询
exists 后面（相关子查询)
标量子查询(结果只有一行一列)
行子查询(一行多列）
列子查询（一列多行）
表子查询
*/
/*
where 或者having后面
in,some,any,all
*/
SELECT
*
FROM
`employees`
WHERE salary>
(SELECT
salary
FROM
`employees`
WHERE last_name = 'Abel')
;
SELECT
`last_name`,
`job_id`,
`salary`
FROM `employees`
WHERE
job_id=(SELECT
job_id
FROM
`employees`
WHERE `employee_id` = 141)
AND
salary>(SELECT
salary
FROM
`employees`
WHERE `employee_id` = 143) ORDER BY salary DESC;
#any/some all,in,not in 实行于子查询
SELECT `department_id` FROM `departments`
WHERE `location_id` IN (1400,1700);
SELECT
last_name
FROM
`employees`
WHERE `department_id` IN
(SELECT
`department_id`
FROM
`departments`
WHERE `location_id` IN (1400, 1700)) ;
SELECT DISTINCT salary FROM
`employees`
WHERE `job_id`='IT_PROG';
SELECT
`employee_id`,
`last_name`,
job_id,
salary
FROM
`employees`
WHERE salary <(SELECT MAX( salary) FROM
`employees`
WHERE `job_id`='IT_PROG') AND job_id<>'IT_PROG';
SELECT
a.*,
(SELECT
COUNT(*)
FROM
`employees` AS b WHERE a.`department_id` = b.`department_id`)
FROM
`departments` AS a ;
#四.exists 后面(相关子查询)
/*结果为0/1查询结果是否存在*/
SELECT EXISTS(SELECT employee_id FROM `employees`);
/*查询前n条信息
limit x,x+n
*/
SELECT * FROM `employees` LIMIT 1,5;
/*联合查询
union 联合 合并：将多条查询语句的结果合并成一个结果
语法:
union
union 的方便:
当查询的来自于多个表的时候，并且没有相关性，我们可以用Union来合并
*/
SELECT * FROM `employees` WHERE email LIKE
'%a%'
UNION
SELECT * FROM `employees` WHERE `department_id`>90;
SELECT * FROM `employees` WHERE email LIKE
'%a%' OR `department_id`>90;
