MySQL多表查询技巧

在数据库的世界里，我们经常需要处理各种各样的数据，我们需要从多个表中查询数据，这时候就需要用到MySQL的多表查询了，就让我们一起来了解一下MySQL多表查询的魅力吧！

表的关系简介

在现实世界中，实体与实体之间总是存在某种关系，比如部门和员工、老师和学生等，在设计数据库表时，应该体现这些关系。

1、一对多：班级和学生、部门和员工、分类和商品。

建表原则：在多方（从表）创建一个字段，该字段作为外键指向一方（主表）的主键。

2、多对多：老师和学生、学生和课程、用户和角色。

建表原则：需要创建第三张中间表，中间表至少包含两个字段，这两个字段分别作为外键指向各自一方的主键。

3、一对一：在实际开发中应用不多，因为一对一可以创建成一张表，实现方式是在任意一方添加唯一外键指向另一方的主键。

4、场景选择：一对一关系多用于单表拆分，将一张表的基础字段放在一张表中，其他的详情字段放在另一张表中，从而提升效率。

笛卡尔积现象

在表连接查询时，如果没有指定表的连接条件，数据库会默认将任意表的全部记录进行匹配，结果的记录数是所有表记录数的乘积，这就是笛卡尔积，因此在进行表连接时，一定要指定好表的连接条件，通常是从表的外键等于主表的主键。

1、什么是笛卡尔积现象：需求是查询所有的员工和所有的部门。

“`sql

select * from dept, emp;

“`

如果左表是部门表，右表是员工表，左表中每一行记录与右表中的每一行记录全都匹配一次，结果的行数=左表中行数x右表中行数。

2、如何清除笛卡尔积：通过设置过滤条件来消除无用的数据。

“`sql

select * from dept, emp where dept.id = emp.dept_id;

“`

或者给表起别名：

“`sql

select * from dept d, emp e where d.id = e.dept_id;

“`

内连接查询

用左边表的记录去匹配右边表的记录，如果符合条件的则显示，查询出来的记录必须符合表关联的条件。

1、隐式内连接：看不到JOIN关键字，条件使用WHERE指定。

“`sql

select column_name from table1 [table1_alias], table2 [table2_alias] where table1.column=table2.column;

“`

示例：查询小龙的信息，显示员工ID、姓名、地址、年龄和所在部门名称。

“`sql

select emp.id, emp.name, emp.address, emp.age, dept.name

from dept, emp

where dept.id = emp.dept_id

and emp.name = ‘小龙’;

“`

2、显示内连接：使用INNER JOIN … ON语句，可以省略INNER。

“`sql

select column_name from table1 INNER JOIN table2 ON table1.column=table2.column;

“`

示例同上。

外连接

外连接需要指定一张查询主表，主表的记录必须全部显示，即使不符合表关联条件。

1、左外连接：查询的数据以左表为准，即使在其他表中没有匹配的记录也会显示出来。

“`sql

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

“`

示例：在部门表中增加一个行政部，需要查询所有的部门和员工，将部门表设置成左表，员工表设置成右表。

“`sql

SELECT * FROM dept LEFT JOIN emp ON dept.id = emp.dept_id;

“`

2、右外连接：查询的数据以右表为准，即使在其他表中没有匹配的记录也会显示出来。

“`sql

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

“`

示例：在员工表中增加一个还未分配部门的员工，希望员工的信息全部显示出来。

“`sql

SELECT * FROM dept RIGHT JOIN emp ON dept.id = emp.dept_id;

“`

全连接

如果希望左表和右表的数据都能够显示全面（在对方表中没有匹配的数据就以NULL补齐），可以使用全连接（FULL JOIN），不过很遗憾，MySQL并不直接支持全连接，但可以通过UNION来实现类似功能。

SELECT * FROM dept d LEFT JOIN emp e ON d.id = e.dept_id
UNION
SELECT * FROM dept d RIGHT JOIN emp e ON d.id = e.dept_id;

自连接与联合查询

1、自连接：当前表与自身的连接查询，自连接必须使用表别名。

“`sql

SELECT a.column_name, b.column_name…

FROM table_name AS a, table_name AS b

WHERE a.common_column = b.common_column;

“`

2、联合查询：将两个或多个SELECT语句的结果集合并成一个结果集。

“`sql

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

“`

子查询

1、标量子查询：用于SELECT列表中的子查询。

“`sql

SELECT (SELECT column_name FROM table_name WHERE condition) AS alias_name;

“`

2、列子查询：用于WHERE或HAVING子句中的子查询。

“`sql

SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT column_name FROM table_name);

“`

3、行子查询：用于FROM子句中的子查询。

“`sql

SELECT column_name(s) FROM (SELECT column_name(s) FROM table_name) AS alias_name;

“`

4、表子查询：用于DELETE、INSERT、UPDATE语句中的子查询。

“`sql

DELETE FROM table_name WHERE column_name = ANY (SELECT column_name FROM table_name);

“`

FAQs:

1、如何在MySQL中进行多表查询？

要在MySQL中进行多表查询，可以使用JOIN操作符来连接多个表，常见的类型有INNER JOIN、LEFT JOIN、RIGHT JOIN等，要查询每个员工的姓名及对应的部门名称，可以使用以下SQL语句：SELECT emp.name, dept.name FROM emp INNER JOIN dept ON emp.dept_id = dept.id;。

2、什么是笛卡尔积，为什么在多表查询中要避免它？

笛卡尔积是指两个集合中所有可能的元素组合，在多表查询中，如果没有指定连接条件，数据库会默认进行笛卡尔积操作，即把两个表中的每一行都进行组合，这会导致结果集中产生大量无用的数据，影响查询性能，在进行多表查询时，应始终指定明确的连接条件以避免笛卡尔积现象。