Mysql DQL 学习随笔(3) 连接查询

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
   按年代分类:
   sql192标准：仅仅支持内连接
   sql199标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

   按功能分类：
    内连接：
      等值连接
      非等值连接
      自连接

    外连接：
      左外连接
      右外连接
      全外连接

    交叉连接

一、sql92标准

1.等值连接

语法：
select 查询列表
from 表1 别名，表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

特点：
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选。

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

SELECT NAME,boyname
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id

案例2：查询员工名和对应的部门名

SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;

为表起别名

①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

案例：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

案例：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门得到最低工资

SELECT d.department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL 
GROUP BY department_name,d.`manager_id`;

案例：查询每个工种的工种名和员工的个数，并按员工个数排序

SELECT job_title,COUNT(*) 个数
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY job_title
ORDER BY 个数 DESC;

案例：查询员工名、部门名和所在的城市

SELECT e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`

2.非等值连接

语法：
select 查询列表
from 表1 别名，表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

案例1：查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN `lowest_sal` AND `highest_sal`;

3.自连接

语法：
select 查询列表
from 表 别名1，表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

案例：查询员工名和上级的名称

SELECT e1.`employee_id` 员工编号,e1.`last_name` 员工名,e2.`employee_id` 上级编号,e2.`last_name` 上级名称
FROM employees e1,employees e2
WHERE e1.`manager_id`=e2.`employee_id`;

二、sql99语法

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

分类：
内连接：inner
外连接：
    左外：left【outer】
    右外：right【outer】
    全外：full【outer】
交叉连接：cross

（一）内连接

语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join 连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1.等值连接

案例1：查询员工名、部门名

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

案例2：查询名字中包含e的员工名和工种名（筛选）

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE '%e%';

案例3：查询部门个数>3的城市名称和部门个数（添加分组+筛选）

SELECT city,COUNT(*) 部门个数
FROM locations l
INNER JOIN departments d
ON l.`location_id`=d.`location_id`
GROUP BY city
HAVING 部门个数>3;

案例4：查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序（添加排序）

 SELECT department_name,COUNT(*) 员工个数
 FROM departments d
 INNER JOIN employees e
 ON d.`department_id`=e.`department_id`
 GROUP BY department_name
 HAVING 员工个数>3
 ORDER BY 员工个数 DESC;

案例5：查询员工名、部门名、工种名、并按部门名降序（添加三表连接）

 SELECT last_name,department_name,job_title
 FROM employees e 
 INNER JOIN departments d ON e.`department_id`=d.`department_id`
 INNER JOIN jobs j ON e.`job_id`=j.`job_id`
 ORDER BY department_name DESC;

2.非等值连接

案例1：查询员工的工资级别

SELECT last_name,salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

案例2：查询工资级别个数>2的个数，并且按工资级别降序。

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

3.自连接

案例：查询员工的名字、上级的名字

SELECT e1.last_name 员工名,e2.last_name 上级名
FROM employees e1
JOIN employees e2 
ON e1.`manager_id`=e2.`employee_id`;

（二）外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1.外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中 有而从表没有的记录

2.左外连接：left join左边的是主表
右外连接：right join 右边的是主表

3.左外和右外交换两个表的顺序，可以实现同样的效果

4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的 【MySQL不支持】

引入：查询男朋友不在男神表的女神名

SELECT b1.name
FROM beauty b1
LEFT OUTER JOIN boys b2
ON b1.`boyfriend_id`=b2.`id`
WHERE b2.`id` IS NULL; #主键不会为null

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

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

交叉连接

使用sql99语法的标准实现笛卡尔乘积

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;