一文掌握MySQL多表查询技巧：告别繁琐操作，轻松搞定数据查询！ |
您所在的位置：网站首页 › 怎么用函数汇总众多日期中出现的名子和数字不一样 › 一文掌握MySQL多表查询技巧：告别繁琐操作，轻松搞定数据查询！ |
一文掌握MySQL多表查询技巧：告别繁琐操作，轻松搞定数据查询！
|
在数据库的世界里，我们经常需要处理各种各样的数据。有时候，我们需要从多个表中查询数据，这时候就需要用到MySQL的多表查询了。今天，就让我们一起来了解一下MySQL多表查询的魅力吧！ 一、表的关系简介现实生活中，实体与实体之间肯定是有关系的，比如：部门和员工，老师和学生等。在设计表的时候，就应该体现出表与表之间的这种关系。 1.1 一对多一对多：班级和学生，部门和员工，分类和商品。
一对多（1:n）建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。 1.2 多对多多对多：老师和学生，学生和课程，用户和角色。
多对多（m:n）建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。 SQL实现代码： 在实际的开发中应用不多，因为一对一可以创建成一张表。 实现方式： 可以在任意一方添加唯一外键指向另一方的主键（一般情况下，直接合成一张表即可）。 场景选择： 多用于单表的拆分，将一张表的基础字段放在一张表中，其他的详情字段放在另一张表中，从而提升效率，比如说：如果叫我们描述一个人，我们能从它的身体的基本信息来描述（身高，体重，姓名，年龄），也可以使用受教育信息（小学，中学，大学）等等。
在表连接查询时，由于没有指定表的连接条件，那么数据库默认情况下会将任意表的全部记录都匹配一边任意表。 这样达到的结果总的记录就是所有表的记录数的乘积就是笛卡尔积；所以在表连接时，一定要指定好表的连接条件，一般情况下是从表的外链 = 主表的主键。
需求：查询所有的员工和所有的部门。 select * from dept,emp;结果如下：
结果的行数=左表中行数x右表中行数 结果分析： 我们发现不是所有的数据组合都市有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的，所以需要通过条件过滤掉没用的数据。 设置过滤条件 指定过滤条件 主表.主键=从表.外键 select * from dept,emp where dept.id = emp.dept_id;可以给表起别名 select * from dept d,emp e where d.id = e.dept_id;
你还在苦恼找不到真正免费的编程学习平台吗?可以试试云端源想！课程视频、在线书籍、在线编程、实验场景模拟、一对一咨询……你想要的全部学习资源这里都有，重点是统统免费！点这里即可查看 三、内连接查询用左边表的记录去匹配右边表的记录，如果符合条件的则显示。特点： 查询出来的记录必须符合表关联的条件。 隐式内连接 看不到 join 关键字，条件使用where指定 select 列名 from 左表 [左表别名], 右表 [右表别名] where 主表.主键=从表.外键; select * from dept,emp where dept.id=emp.dept_id;相当于查询A,B（员工表和部门表）的交集数据； 使用where条件消除无效数据； 字段要由对应的表名或别名调用。 显示内连接无论是显示内连接还是隐式内连接查询结果是一样的，只是写法不同。 使用 inner join … on 语句，可以省略 inner； select 列名 from 左表 inner join 右表 on 主表.主键=从表.外键;如果是表关联的条件推荐使用on关键字； 如果是其他普通的条件使用where。 案例：查询小龙的信息，显示员工id，姓名，地址，年龄和所在的部门名称，我们发现需要联合2张表同时才能查询出需要的数据，使用内连接。 1）确定需要查询的表（dept、emp表）； 2）确定连接条件（过滤掉不需要的数据，消除笛卡尔积）； 3）确定查询条件，我们查询的是小龙的信息，员工表.name=‘小龙’； 4）确定查询的列名（员工id，姓名，地址，年龄，部门名称）。 代码实现如下：
结果如下： 特点：需要指定一张查询主表，查询主表的记录必须全部显示，即使不符合表关联的条件 左外连接左外连接：查询的数据以左表为准，即使在其他表中没有匹配的记录也会显示出来。 使用 left outer join on，outer可以省略 相当于查询A表所有数据和交集部分数据 select 列名 from 左表 left join 右表 on 表连接条件 需求：在部门表中增加一个行政部，需要查询所有的部门和员工，将部门表设置成左表，员工表设置成右表。 select * from dept; insert into dept (name) values ('行政部'); -- 使用内连接查询 -- 需要查询所有的部门和员工，无论这个部门下有没有员工 select * from dept inner join emp on dept.id = emp.dept_id;结果如下：
右外连接：查询的数据以右表为准，即使在其他表中没有匹配的记录也会显示出来。 使用right outer join … on，outer可以省略 相当于查询B表所有数据和交集部分数据 select 列名 from 左表 left join 右表 on 表连接条件 需求：在员工表中增加一个员工，但该员工还未分配部门 select * from emp; -- 不在任何一个部门 INSERT INTO `emp` VALUES (6, '小赵', '吉安', 26, '男', null); -- 希望员工的信息全部显示出来 -- 使用内连接查询 select * from dept inner join emp on dept.id = emp.dept_id;结果如下：
使用右外连接查询： -- 使用右外连接查询 select * from dept right join emp on dept.id = emp.dept_id;
左连接是无论如何左表的数据都能够显示全，右连接是右表的数据无论如何都能够显示全面，那么如果希望左表和右表的数据都能够显示全面呢（在对方表中没有匹配的数据就以null补齐）？这种连接查询我们称之为全连接（full join），但是很遗憾，MySQL并没有提供全连接，但Oracle支持； 虽然MySQL不支持全连接，但是我们可以利用MySQL提供的其它功能来完成全连接的功能： left join + right join 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;全连接查询的结果如下： Tips： union关键字可以将两个或多个SQL语句的结果集拼接成一个结果集，前提是这些SQL语句的结果集列数必须相同； union关键字自带去重功能，即去除重复的数据； 如果需要保留重复的数据则可以使用union all关键字来连接多个SQL语句的结果集。 六、子查询一个查询语句结果做为另一个查询语句的条件； 查询语句有嵌套，里面的查询称为子查询，外面的查询称为父查询； 子查询要使用括号括起来。 准备数据： 子查询结果是单行单列的：子查询可以作为条件值，使用运算符去判断。 select * from 表名 where 字段名 = (子查询);需求： 查询年龄最高的员工是谁？ -- 1. 查询年龄大的员工的详细信息 select max(age) from emp; -- 2. 根据最高年龄到员工表查询到对应的员工信息 select * from emp where age = 26; -- 使用子查询 select * from emp where age = (select max(age) from emp);
子查询结果是多行单列的：子查询可以作为条件值，使用运算符 in/any/all 去判断： select * from 表名 where 字段名 IN/ANY/ALL(子查询)下面看两个案例： 需求1：查询年龄大于23的员工的部门信息 -- 关联查询 SELECT d.* FROM dept d INNER JOIN emp e ON d.id = e.dept_id WHERE e.age > 23 -- 子查询 -- 1. 首先查询年龄大于23岁的员工所在的部门id select dept_id from emp where age > 23; -- 2. 再查询在这些部门id中部门的名字 -- ERROR 1242 (21000): Subquery returns more than 1 row 子查询返回了多行数据 select * from dept where id = (select dept_id from emp where age > 23); -- 采用in 取结果集中的数据 in (1,2,3) select * from dept where id in (select dept_id from emp where age > 23);
需求2： 查询年龄大于1号部门所有员工的人 -- 1. 查询1号部门所有员工的年龄，得到多行单列 select age from emp where dept_id=1; -- 2. 当结果集返回多行时不能使用比较运算符 -- ERROR 1242 (21000): Subquery returns more than 1 row select * from emp where age > (select age from emp where dept_id=1); select * from emp where age > all (select age from emp where dept_id=1); 等价于: select * from emp e where e.age > (select max(age) from emp e where e.dept_id=1); -- 比1号部门任意一个大就行 select * from emp where age > any (select age from emp where dept_id=1); 等价于： select * from emp e where e.age > (select min(age) from emp e where e.dept_id=1);
如果子查询的结果是多行多列，父查询可以将这个查询结果做为一个虚拟表，进行第2次查询。不是放在where后面，而是放在from的后面；子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段。 需求： 查询出年龄大于23岁的员工信息和部门名称 -- 1. 在员工表中查询年龄大于23岁的员工 select * from emp where age > 23; -- 2.查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id select * from dept d, emp e where d.id = e.dept_id; -- 隐式内连接 select e.*,d.name 部门名称 from dept d, (select * from emp where age > 23) e where d.id = e.dept_id; -- 隐式内连接 -- 也可以使用表连接 select e.*,d.name 部门名称 from dept d, emp e where d.id = e.dept_id and e.age > 23;
单行单列：父查询使用比较运算符(>， |
如果左表是部门表，右表是员工表，左表中每一行记录与右表中的每一行记录全都匹配一次。
注：以上过滤以后的结果称为隐式内连接。
使用左外连接查询：
子查询结果是多行多列的：子查询可以作为一张虚拟表
【本文地址】
今日新闻 |
推荐新闻 |