MySQL—关联（连接）查询（笛卡尔积、内连接、外连接、左连接、右外连接、外连接、自连接）

MySQL—关联（连接）查询

知识大纲

概述
笛卡尔积
关联条件
内连接 [INNER JOIN]
外连接 [OUTER JOIN]
左连接 [LEFT JOIN] 右外
连接 [RIGHT JOIN]
外连接 [FULL JOIN]
自连接

1.概述

关联查询一共有几种情况：

内连接：INNER JOIN 、CROSS JOIN
外连接：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN）
自连接：当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义

说明：

连接 n 个表,至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。
当两个关联查询的表如果有字段名字相同，并且要查询中涉及该关联字段，那么需要使用表名前缀加以区分
当如果表名比较长时，可以给表取别名，简化 SQL 语句

MySQL—关联（连接）查询（笛卡尔积、内连接、外连接、左连接、右外连接、外连接、自连接）

示例

#连接[关联]查询

DROP TABLE IF EXISTS souce;
DROP TABLE IF EXISTS student;

#学生表
CREATE TABLE student
(
        s_id VARCHAR(20),
        name VARCHAR(20),
        sex CHAR(2),
        age INT,
        PRIMARY KEY(s_id)
);
INSERT INTO student VALUES
    ('A1001','张三','男',15),
    ('A1002','李四','女',16),
    ('B1001','王五','男',17),
    ('C1001','赵六','女',18);

#成绩表
CREATE TABLE souce
(
        t_id VARCHAR(20),
        C FLOAT,
        MySQL FLOAT,
        Java FLOAT,
        s_id VARCHAR(20),
        PRIMARY KEY(t_id),
        FOREIGN KEY(s_id) REFERENCES student(s_id) ON UPDATE CASCADE
);
INSERT INTO souce VALUES
    ('T001',66,77,88,'A1001'),
    ('T002',65,75,85,'A1002'),
    ('T003',84,85,86,'B1001');

SELECT * FROM student;
SELECT * FROM souce;

#查询每一个参加了考试的同学 的三科测试成绩[学生学号、学生姓名、C、MySQL、Java]
SELECT student.s_id,name,C,MySQL,Java FROM student,souce 
    WHERE student.s_id=souce.s_id;
#使用别名 简化查询语句中表名的录入
SELECT st.s_id,name,C,MySQL,Java FROM student st,souce s 
    WHERE st.s_id=s.s_id;

#查询每一个所有同学 的三科测试成绩[学生学号、学生姓名、C、MySQL、Java]
SELECT st.s_id,name,C,MySQL,Java FROM student st LEFT JOIN souce s 
    ON st.s_id=s.s_id;

2.笛卡尔积

定义：将两（或多）个表的所有行进行组合，连接后的行数为两（或多）个表的乘积数.

在 MySQL 中如下情况会出现笛卡尔积，主要是因为缺少关联条件或者关联条件不准确注：外连接必须写关联条件，否则报语法错误

示例

#笛卡尔积 [原因：没有提供关联条件 或 关联条件失效]
SELECT name,Java FROM student,souce;
SELECT name,Java FROM student INNER JOIN souce;
SELECT name,Java FROM student JOIN souce;
SELECT name,Java FROM student CROSS JOIN souce;

3.关联条件

表连接的约束条件可以有三种方式：WHERE, ON, USING

WHERE：适用于所有关联查询
ON：只能和 JOIN 一起使用，只能写关联条件。虽然关联条件可以并到 WHERE 中和其他条件一起写，但分开写可读性更好
USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

示例

#WHERE [关联条件 AND 查询条件]
SELECT student.s_id,name,C,MySQL,Java FROM student,souce 
    WHERE student.s_id=souce.s_id AND student.s_id='A1001';
#JOIN ON [关联条件]
SELECT st.s_id,name,C,MySQL,Java FROM student st LEFT JOIN souce s 
    ON st.s_id=s.s_id;
#USING [关联条件]
SELECT st.s_id,name,C,MySQL,Java FROM student st LEFT JOIN souce s 
    USING(s_id);

4.内连接

有两种格式，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行

隐式：

SELECT [cols_list] from 表 1,表 2 where [condition]

显式：

SELECT [cols_list] from 表 1 INNER JOIN 表 2 ON [关联条件] where [其他筛选条件]
SELECT [cols_list] from 表 1 CROSS JOIN 表 2 ON [关联条件] where [其他筛选条件]
SELECT [cols_list] from 表 1 JOIN 表 2 ON [关联条件] where [其他筛选条件]

MySQL—关联（连接）查询（笛卡尔积、内连接、外连接、左连接、右外连接、外连接、自连接）

示例

#查询员工姓名，基本工资，部门名称
#隐式内连查询
SELECT ename,basic_salary,dname FROM t_employee e,t_department d,t_salary s
    WHERE e.dept_id=d.did AND e.eid=s.eid;

SELECT ename,basic_salary,dname FROM t_employee e,t_department d,t_salary s
    WHERE e.dept_id=d.did AND e.eid=s.eid AND ename='孙红雷';

#隐式内连查询
SELECT ename,basic_salary,dname FROM t_employee e INNER JOIN t_department d
    INNER JOIN t_salary s
    ON e.dept_id=d.did AND e.eid=s.eid;

SELECT ename,basic_salary,dname FROM t_employee e JOIN t_department d
    INNER JOIN t_salary s
    ON e.dept_id=d.did AND e.eid=s.eid;

SELECT ename,basic_salary,dname FROM t_employee e 
    CROSS JOIN t_department d
    INNER JOIN t_salary s
    ON e.dept_id=d.did AND e.eid=s.eid
    WHERE ename='孙红雷';

5.外连接

外连接分为：左外连接（LEFT OUTER JOIN），简称左连接（LEFT JOIN）

右外连接（RIGHT OUTER JOIN），简称右连接（RIGHT JOIN）

全外连接（FULL OUTER JOIN），简称全连接（FULL JOIN）。

6.左连接

MySQL—关联（连接）查询（笛卡尔积、内连接、外连接、左连接、右外连接、外连接、自连接）

7.右外连接

MySQL—关联（连接）查询（笛卡尔积、内连接、外连接、左连接、右外连接、外连接、自连接）

示例

#左连接
#查询所有部门信息以及该部门员工信息
SELECT did,dname,eid,ename FROM t_department d LEFT JOIN t_employee e
    ON d.did=e.dept_id;

SELECT did,dname,eid,ename FROM t_employee e LEFT JOIN t_department d
    ON d.did=e.dept_id;

SELECT did,dname,eid,ename FROM t_department d RIGHT JOIN t_employee e
    ON d.did=e.dept_id;

UPDATE t_employee SET dept_id=NULL where eid=5;

SELECT did,dname,eid,ename FROM t_department d LEFT JOIN t_employee e
    ON d.did=e.dept_id;
    WHERE e.dept_id IS NULL;
#查询部门信息，保留没有员工的部门信息
SELECT did,dname,eid,ename FROM t_department d RIGHT JOIN t_employee e
    ON d.did=e.dept_id;
    WHERE e.dept_id IS NULL;

SELECT did,dname,eid,ename FROM t_employee e LEFT JOIN t_department d
    ON d.did=e.dept_id;
    WHERE e.dept_id IS NULL;
    
#查询所有员工信息，以及员工的部门信息
SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d 
    ON e.dept_id=d.did;

SELECT eid,ename,did,dname FROM t_department d LEFT JOIN t_employee e
    ON e.dept_id=d.did;

#查询员工信息，仅保留没有分配部门的员工
SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d 
    ON e.dept_id=d.did
    WHERE e.dept_id IS NULL;
#查询员工信息，不保留没有分配部门的员工    
SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d 
    ON e.dept_id=d.did
    WHERE e.dept_id IS NOT NULL;

SELECT id,ename,tid,dname FROM student_info s LEFT JOIN student_test t
    ON s.id_id=t.tid
    WHERE t.tid IS NOT NULL;

SELECT * FROM student;
SELECT * FROM souce;
#分析 左右外连接
#显示所有学生的测试信息
SELECT st.s_id,name,C,Java FROM student st LEFT JOIN souce s
    ON st.s_id=s.s_id;

#显示参加考试的学生的测试信息
SELECT st.s_id,name,C,Java FROM student st RIGHT JOIN souce s
    ON st.s_id=s.s_id;

8.外连接

mysql 不支持 FULL JOIN，但是可以用 left join union right join 代替

MySQL—关联（连接）查询（笛卡尔积、内连接、外连接、左连接、右外连接、外连接、自连接）

示例

#使用UNION 实现MySQL中不支持的FULL JOIN操作[完全外连接]
#查询所有部门信息和员工信息
SELECT did,dname,eid,ename FROM t_department d LEFT JOIN t_employee e
    ON d.did=e.dept_id
    UNION #联合查询 [整合多个查询]
SELECT did,dname,eid,ename FROM t_department d RIGHT JOIN t_employee e
    ON d.did=e.dept_id;

9.自连接

当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询

#自连接
#查询员工姓名以及领导姓名，仅显示有领导的员工[自连接 内连]
SELECT e1.ename,e2.ename FROM t_employee e1, t_employee e2
    WHERE e1.mid=e2.eid;

SELECT e1.ename,e2.ename FROM t_employee e1 INNER JOIN t_employee e2
    ON e1.mid=e2.eid;

#查询所有员工姓名及其领导姓名[自连接 外连]
SELECT e1.ename,e2.ename FROM t_employee e1 LEFT JOIN t_employee e2
    ON e1.mid=e2.eid;
SELECT e1.ename,e2.ename FROM t_employee e1 RIGHT JOIN t_employee e2
    ON e1.mid=e2.eid;
    
#若涉及到多表连接查询 应使中间表连接居中
#book JOIN readbook ON xxx=xxx JOIN stuinfo ON xxx=xxx
#查询哪一本书[b_name] 在 什么时间[r_time] 被 何人所借[s_name]
SELECT b.b_name,r.r_time,s.s_name FROM book b JOIN readbook r ON b.b_id=r.b_id
    JOIN stuinfo s ON s.s_id=r.s_id;