笔记：MySQL连接查询

一，基础信息

1.含义：又称多表查询，当查询字段来自多个表时，就会用到连接查询。

2.笛卡尔乘积现象：表一有m行，表二有n行，结果为m*n行。

3.发生原因：没有有效的连接条件。

如何避免：添加有效的连接条件。

（截图来自于B站尚硅谷MySQL）

SELECT name,boyNAME
FROM boys,beauty;
/* WHERE boyfriend_id=id;会报错，记得用表名限定一下 */
WHERE beauty.boyfriend_id=boys.id;
/* beauty表的boyfriend_id和boys表的id是连接条件，值相等时匹配 */

二，分类： 

1.按年代分类：

sql92标准

#一.等值连接

（1）多表连接的结果为多表重叠部分。

（2）n表连接，至少需要n-1个连接条件。

（3）多表顺序没有要求。

（4）一般需要为表起别名。

（5）可以搭配前面所学的句子使用，比如排序，分组，筛选。

案例1：查询女神名和对应男神名（上方代码）

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

SELECT last_name,department_name
FROM employees，departments
WHERE employees.'department_id'=departments.'department_id';

#1.起别名

通过上方代码我们发现如果需要表名限定，每次都敲原有表明会很麻烦，此时我们也可以起别名以达到画面简洁，减少代码的复杂度。

案例3：查询员工名，工种号，工种名。

SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs j
WHERE e.'job_id'=jobs.'j_id';

注意： 当FROM子句中定义了 employees AS e,jobs j，SELECT子句中就不能再用employees，jobs，因为sql最先执行FROM语句，此时SELECT子句中再用employees，jobs系统不能识别 

#2.可以加筛选吗？

案例4：查询有奖金的员工名，部门名。

SELECT last_name,department_id,commission_pct
FROM employees e,department d
WHERE e.'department_id'=d.'department_id'
AND e.'commission_pct' IS NOT NULL;

案例5：查询城市名中第二个字母是O的部门名和城市名。

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

#3.可以加分组吗？

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

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

 案例7：查询有奖金的每个部门的部门名和部门领导编号，以及该部门的最低工资。

SELECT 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;

#4.加排序

案例8：查询每个工种的工种名和员工的个数，并且按员工个数降序。

SELECT job_name,COUNT(*)
FROM jobs j,emplorees e
WHERE e.'job_id'=j.'job_id'
GROUP BY job_title
ORDER BY COUNT(*) DESC;

 #5.三表连接

案例9：查询员工名，部门名和所在城市。

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

#二.非等值连接

工资等级表：

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

SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN g.'lowest_sal' AND g.'highest_sal';

#三，自连接

意义：把原来的一张表当成两张表或者多张表使用。

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

/* （1）找出员工名和其id */
SELECT employee_id,last_name 
FROM employees;

/*（2）把员工表当成领导表使用，用employee_id当作连接条件 */
SELECT employee_id,last_name,employee_id,last_name
FROM employees,employees;

/* 上处写法名称相同，可读性低且容易混淆，此时需要我们起别名来区分 */
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m; /* 此处m代表manage */
WHERE e.'manage_id'=m.'emploree_id';

sql99标准（推荐使用）

#一，语法：

/* 与sql92不同的是连接表2用JOIN,连接条件前不用WHERE用ON */
SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名 
ON 连接条件
【WHERE 筛选条件】

#二，内连接：

SELECT 查询列表
FROM 表1 别名 INNER
JOIN 表2 别名 
ON 连接条件
【WHERE 筛选条件】

#1.等值连接

(1).INNER可以省略。

(2).连接条件在ON后，筛选条件在WHERE后，提高分离性。

案例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 COUNT(*);

案例4：查询员工名，部门名，工种名，并按照部门名排序。

SELECT last_name,department_name,job_title
FROM emplorees 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.非等值连接

案例5：查询员工工资级别。

SELECT grade_level
FROM employees e
INNER JOIN job_level g
ON e.'salary' BETWEEN g.'lowest' AND g.'highest_sal';

#3.自连接 

案例6：查询姓名中含有k的员工名，以及上级的名字。

SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m; 
ON e.'manage_id'=m.'emploree_id'
WHERE e.'last_name' LIKE '%k%';

#三，外连接 

应用场景：主表有，从表没有。

#1.特点:

1.外连接的查询结果为主表中的所有记录。

如果从表中有和它匹配的，则显示匹配的值；没有则显示null。

外连接的查询结果=内连接结果+主表有而从表没有的记录。

2.左外连接，left左边的是主表；右外连接，right右边是主表。

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

4.全外连接=内连接结果+表1有但表2没有的+表2有但表1没有的（无主表和从表之分）.

#2.左外+右外

案例1：查询男朋友不在男神表的女神名。(左外+右外)

/* 左外连接实现 */
/* 查询女神名，所以用beauty表做主表 */
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.'boyfriend_id' = bo.'id'
WHERE bo.'id' IS NULL;

/* 右外连接实现 */
SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.'boyfriend_id' = bo.'id'
WHERE bo.'id' IS NULL;

案例2：查询哪个部门没有员工。(左外)

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

#3.交叉连接(笛卡尔乘积)

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

2.按功能分类：

(1).内连接(INNER)：

等值连接，非等值连接，自连接。

(2).外连接：

左外连接(LEFT OUTER)，右外连接(RIGHT OUTER)，全外连接(FULL OUTER)。

(3).交叉连接(CROSS)。