子查询
一、where或having后面
1.标量子查询
查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
2.列子查询（多行单列子查询）
返回location_id是1400或1700的部门中的所有员工姓名
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
3、行子查询（结果集一行多列或多行多列）
查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
二、select后面
/*
仅仅支持标量子查询
*/
查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、exists后面（相关子查询）
/*
语法：
exists(完整的查询语句)
结果：
1或0
*/
查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
)
#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
sql的连接查询(sql92语法)
SELECT * FROM TABLEA A,TABLEB B ON A.Key= B.Key
Join的七种理论(sql99语法)
--MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法.
SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON A.Key= B.Key
UNION
SELECT * FROM TABLEA A RIGHT JOIN TABLEB B ON A.Key= B.key
--这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。A的独有+B的独有
SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON A.Key = B.Key WHERE B.Key IS NULL
UNION
SELECT * FROM TABLEA A RIGHT JOIN TABLEB B ON A.Key= B.Key WHERE A.Key IS NULL;
sql执行顺序
select
distinct <select_list>
form table_left
<inner join>、<left join>、<rigth join> table_right on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
order by <order_by_list>
limit offset,size(offset要显示条目的起始索引（起始索引从0开始）size 要显示的条目个数)
上面的执行过程
1.from table_left
2.on <join_condition>
3.<inner join>、<left join>、<rigth join> table_right
4.where <where_condition>
5.group by <group_by_list>
6.having <having_condition>
7.select
8.distinct <select_list>
9.order by <order_by_list>
10.limit offset,size