MySQL多表查询

文章目录

一、什么是多表查询

1、概述

多表查询就是从多张表中去查询数据，在实际开发中大多数情况数据都是存储在多张中的，它们通过一个关联关系连接起来，这样就可以通过这个关联关系去查询到想要的数据。比如：下面两个表的数据就是通过

stu_num

这个字段关联的，通过

stu_num

即可查询到此名学生的成绩。
关联关系：可以是一对一，也可以是一对多的关系

（关于这两张表的sql在文章最后附录中）

2、笛卡尔积现象

上文提到了需要通过一个关联关系连接起来，如果不通过关系连接会出现什么情况呢？答案就是笛卡尔积现象。

(1)、使用此SQL去查询观察结果：

SELECT * FROM t_student,t_score

查询出了两个表的全部组合结果，共计49条记录。

（图片中只展示了部分数据）

(2)、使用连接关系查询结果：

SELECT * FROM t_student,t_score WHERE t_student.stu_num = t_score.stu_num

查询出期望的结果6条记录，因为关联关系是字段

stu_num

，所有可以对应查询到的结果只有6条记录。

(3)、查询出最终结果（可以设置别名查询想要的列）

SELECT a.stu_num,a.stu_name,a.stu_age,b.stu_score 
FROM t_student a,t_score b 
WHERE a.stu_num = b.stu_num   # 关联关系

二、多表查询的分类

1、等值连接 vs非等值连接

1、等值连接：就是通过一个表的字段和另一个表的字段相等来连接

SELECT a.stu_num,a.stu_name,a.stu_age,b.stu_score 
FROM t_student a,t_score b 
WHERE a.stu_num = b.stu_num

2、非等值连接：不是这种两表之间通过字段相等的连接。例如我们统计每个学生的分数在那个等级

（引入第三张表，成绩等级表）

SELECT
    * 
FROM
    t_score a,
    t_grade b
WHERE
    a.stu_score BETWEEN b.score_min and b.score_max

3、连接多张表
如果想查查询出最终的结果需要连接三张表才可以。

SELECT
    a.stu_num,
    a.stu_name,
    a.stu_age,
    b.stu_score,
    c.score_grade 
FROM
    t_student a,
    t_score b,
    t_grade c 
WHERE
    a.stu_num = b.stu_num 
    AND 
    b.stu_score BETWEEN c.score_min AND c.score_max

2、自连接 vs 非自连接

(1)、自连接：就是同一张表，利用取别名的方式虚拟成两张表以代表不同的意义的表，然后这两个表再进行内连接或者外连接。

SELECT
    a.employee_id,
    a.employee_name,
    b.employee_name as manager_name 
FROM
    t_employee a,
    t_employee b 
WHERE
    a.manager_id = b.employee_id

只有两条记录是因为只有依据连接关系只又两条记录符合条件

(2)、非自连接：不同表之间进行连接

3、内连接 vs 外连接

(1)、内连接：只查出符合关联条件的记录。上面的所有连接均为内连接
语法：
a、直接在from后面接关联表，where后面写关联关系

SELECT
    a.stu_num,
    a.stu_name,
    a.stu_age,
    b.stu_score 
FROM
    t_student a,
    t_score b 
WHERE
    a.stu_num = b.stu_num

b、

INNER JOIN 表名 ON 关联关系 WHERE 其他条件 

，其中INNER可以省略不写

SELECT
    a.stu_num,
    a.stu_name,
    a.stu_age,
    b.stu_score 
FROM
    t_student a
    INNERJOIN t_score b ON a.stu_num = b.stu_num

查询结果

(2)、外连接：外连接分为左外连接和右外连接。
左外连接：在查出符合关联条件的同时会将左表中的记录全部展示出来，没有关联到记录右表补充为null。
语法：

LEFT OUTER JOIN 表名 ON 关联关系 WHERE 其他条件

，其中OUTER可以省略

• 例一：查出所有学生的成绩

• 例一SQL:

SELECT
    a.stu_num,
    a.stu_name,
    a.stu_age,
    b.stu_score 
FROM
    t_student a
    LEFTJOIN t_score b ON a.stu_num = b.stu_num

• 例二：查出所有员工和他们的经理
• 例二SQL

SELECT
    a.employee_id,
    a.employee_name,
    b.employee_name AS manager_name 
FROM
    t_employee a
    LEFTJOIN t_employee b ON a.manager_id = b.employee_id

右外连接：在查出符合关联条件的同时会将右表中的记录全部展示出来，没有关联到记录左表补充为null。
语法：

RIGHT OUTER JOIN 表名 ON 关联关系 WHERE 其他条件

，其中OUTER可以省略

• 例三：查询所有成绩对应的学生信息

• 例三SQL

SELECT
    a.stu_num,
    a.stu_name,
    a.stu_age,
    b.stu_score 
FROM
    t_student a
    RIGHTJOIN t_score b ON a.stu_num = b.stu_num

• 例四：查询所有经理的员工
• 例四SQL

SELECT
    a.employee_id,
    a.employee_name,
    b.employee_name AS manager_name 
FROM
    t_employee a
    RIGHTJOIN t_employee b ON a.manager_id = b.employee_id

4、满外连接（FULL OUTER JOIN）

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替

三、UNION[ALL]的使用

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。其中UNION ALL不会对结果集去重，但是效率较高，日常开发中更建议使用UNION All

条件：两个表对应的列数和数据类型必须相同，并且相互对应
语法：

SELECTcolumn,...FROM table1
UNION[ALL]SELECTcolumn,...FROM table2

演示：

• 例五：查询stu_num小于5和stu_age大于10的全部记录

• 例五SQL

SELECT stu_num,stu_name,stu_age FROM t_student WHERE stu_num <5UNIONALLSELECT stu_num, stu_name, stu_age FROM t_student WHERE stu_age >10

• 例六：查询stu_num小于5和stu_age大于10的全部记录，去除重复
• 例六SQL

SELECT stu_num,stu_name,stu_age FROM t_student WHERE stu_num <5UNIONSELECT stu_num, stu_name, stu_age FROM t_student WHERE stu_age >10

四、7种SQL JOINS的实现