MySQL的子查询和连接查询

子查询

一、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