Mysql——连接查询

一、连接查询的分类
 连接查询按功能分类，分为：
 1、内连接：
  1️⃣等值连接：表之间的连接条件是等于
  2️⃣非等值连接：表之间的连接条件是非等于(大于、小于、大于等于、不等于等)
  3️⃣自连接：连接的多个表是同一张表，可看作一种特殊的等值连接
 2、外连接：
  1️⃣左外连接
  2️⃣右外连接
  3️⃣全外连接
 3、交叉连接

 Tip：Mysql的sql99标准不支持全外连接

二、sql92标准
 sql92标准只支持内连接查询，内连接查询又分为等值连接、非等值连接、自连接。
 1、等值连接：等值连接是由第一个表挨个去匹配第二个表，然后将符合where条件的放在结果中，匹配的次数为m*n(m是表1的记录数，n为表2的记录数，即笛卡尔积)
  示例①：查询女神名和对应的男神名

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

  示例②：为表起别名，注意一旦为表起了别名就不能再使用原来的表名去限定查询字段而必须使用别名，这是因为SQL的执行顺序是由FROM子句开始的，就相当于一开始就为表起了别名，则后面都不会再认表的原始名字了

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

  Tip：where子句中支持表的别名但不支持字段的别名，原因也是因为SQL的执行顺序是先执行FROM子句，然后执行WHERE子句，然后才是SELECT子句，而表的别名是在FROM子句中加的，但是字段的别名是在SELECT子句中加的。
  示例③：加其他过滤条件

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 last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city LIKE 's%'
ORDER BY department_name DESC;

 注意：等值连接查询时如果连接条件的值为null时，是不会出现在结果中的，比如employees表中的manager_id字段值有null的情况，departments表中的manager_id也有null的情况，如果使用manager_id作为连接条件，两边的manager_id都为null时的匹配是不会出现在结果集中的

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

 2、非等值连接
  示例：查询员工的工资和工资级别

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

 3、自连接
  示例：查询员工及其上司的姓名，连接的两个表是同一张表

SELECT
	e.employee_id,
	e.last_name,
	m.employee_id,
	m.last_name
FROM
	employees e,
	employees m
WHERE
	e.manager_id = m.employee_id;

三、sql99标准
 sql99标准支持内连接、外连接、交叉连接，其语法格式如下：

SELECT 查询列表
FROM 表1 别名 
[连接类型] JOIN 表2 别名 ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 分组后筛选
ORDER BY 排序列表

 连接类型的分类：
  内连接：inner，内连接时inner关键字可省略
  外连接：outer关键字可省略
   左外连接：left outer
   右外连接：right outer
   全外连接：full outer
  交叉连接：cross

 1、内连接：内连接时表的位置可以互换，但是在多个表进行连接时需保证前面的表和后面的表有对应的连接条件
  ①等值连接
   示例1️⃣：查询员工名和部门名

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

   示例2️⃣：查询部门个数大于3的城市和对应部门个数

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

   示例3️⃣：多表内连

SELECT last_name,department_name,job_tile
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;

  ②非等值连接：inner关键字可以省略

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;

  ③自连接

SELECT
	e.last_name,
	m.last_name
FROM
	employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

 2、外连接
  外连接在查询时除了将连接表中匹配的部分展示在结果中以外，还会将主表中匹配不到的部分也显示在结果中(全外连接则会将所有表的未匹配的部分都展示在结果中)，至于哪个表是主表则需要看使用的是左外连接还是右外连接，对于左外连接left前面的表是主表，对于右外连接right表后面的表是主表。因此外连接的查询结果是包含内连接的查询结果的(内连接只是匹配到的部分)。Mysql仅支持sql99语法中的左外连接和右外连接，不支持全外连接。总结来看外连接的特点：
  1️⃣外连接的查询结果为主表中的所有记录(全外连接部分主从表，会展示两张表的所有记录)
  2️⃣若从表中有和主表匹配的结果则显示匹配的值，否则显示null
  3️⃣左外连接和右外连接可以相互转换，不影响结果
   示例：左外连接和右外连接的结果完全相同

#左外连接
SELECT
	b.`name`,
	bo.boyName
FROM
	beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id;

#右外连接
SELECT
	b.`name`,
	bo.boyName
FROM
	boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id;

 3、交叉连接：就是两个表的笛卡尔乘积，没有ON关键字，等同于sql92标准中的从两个表中查询数据

SELECT
	b.*, bo.*
FROM
	beauty b
CROSS JOIN boys bo;
#等同于
SELECT b.*,bo.*
FROM beauty b,boys bo;