数据库的DQL语言（二）

五.连接查询

当查询的数据来自多个表时，就会用到连接查询

语法

select 查询列表 
from 表1 别名 连接类型 
join 表2 别名 
on 连接条件
where 筛选条件 
group by 分组 
having 筛选条件 
order by 排序列表

分类：

1.内连接

特点：1.多表等值连接的结果为多表的交集部分

2.n表连接，至少需要n-1个连接条件

3.多表的顺序没有要求

4.一般需要为表起别名

5.可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

分类：

1.等值连接

举例：查询员工名、部门名

SELECT last_name,department_name 
FROM departments d 
INNER JOIN employees e 
ON e.`department_id` = d.`department_id`;

2.非等值连接

举例：查询工资级别的个数>20的个数，并且按工资级别降序
 

SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC；

3.自连接

举例：查询女神名和对应的男神名

SELECT NAME,boyName 
FROM boys,beauty 
WHERE beauty.boyfriend_id= boys.id;

2.外连接

特点：1、外连接的查询结果为主表中的所有记录；
    如果从表中有和它匹配的，则显示匹配的值；
    如果从表中没有和它匹配的，则显示null；
    外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表；
   右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果 

分类：

1.左外连接

举例：查询哪个部门没有员工

SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

2.右外连接

举例：查询哪个部门没有员工

SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

3.全外连接

全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

3.交叉连接

就是笛卡尔积，在没有表明连接条件时会查询出笛卡尔积

六.子查询

出现在其他语句当中的select语句，称为子查询

分类：

按子查询出现的位置：

select后面：仅支持标量子查询

from后面：支持表子查询

where 或 having的后面：支持标量子查询，列子查询，行子查询（很少）

exist 后面：支持表子查询

按结果集行列数不同:

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

行子查询（结果集有一行多列）

表子查询（结果集一般有多行多列）

特点：

1.where 或 having 后面

   子查询放在小括号内；

   

子查询一般放在条件的右侧；

   标量子查询，一般搭配着单行操作符使用 > < >= <= = <> 列子查询，一般搭配着多行操作符使用 in、any/some、all；

   子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

举例：

1).标量子查询

谁的工资比 Abel 高

SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
	);

2).列子查询

返回location_id是1400或1700的部门中的所有员工姓名

SELECT last_name
FROM employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

3).行子查询

查询员工编号最小并且工资最高的员工信息

SELECT * FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

2.select后面

仅支持标量子查询

举例：查询每个部门的员工个数

SELECT d.*,
( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id` ) 个数 
FROM departments d;

3.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;

4.exists后面

语法：exists(查询语句)

返回结果：0(错误);1(正确）