mysql多表查询

最新推荐文章于 2022-09-13 23:04:19 发布
最新推荐文章于 2022-09-13 23:04:19 发布
阅读量119 收藏
点赞数
分类专栏： Mysql学习 文章标签： mysql 数据库 dba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43761240/article/details/125252328
版权

多表查询

distinct(去除重复记录)

//去除前
mysql> select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+
14 rows in set (0.03 sec)

//去除后
mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.03 sec)
distinct只能出现在所有字段最前方
//错误写法
mysql> select ename,distinct job from emp;
//正确写法，去除job和deptno都相等的重复数据
mysql> select distinct job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+
9 rows in set (0.03 sec)
统计工作岗位的数量
//去除重复工作岗位后
mysql> select count(distinct job) from emp; 
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.13 sec)
//去除前
mysql> select count(job) from emp; 
+------------+
| count(job) |
+------------+
|         14 |
+------------+
1 row in set (0.02 sec)

连接查询

什么是连接查询？

从一张表中单独查询，称为单表查询。

emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。

这种跨表查询，多张表联合起来查询数据，被称为连接查询。

连接查询分类？

根据语法的年代分类：

  1. sql92：1992年的时候出现的语法

  2. sql99：1999年的时候出现的语法

    我们这里重点学习sql99。

根据表连接的方式分类：

  1. 内连接
    1. 等值连接
    2. 非等职连接
    3. 自连接
  2. 外连接
    1. 左外连接(左连接)
    2. 右外连接(右连接)
  3. 全连接
内连接
内连接之等值连接
查询每个员工所在部门名称，显示员工名和部门编号。
//sql92语法
//缺点：结构不清晰，表的连接条件和后期的进一步筛选条件都放到了where后面。
mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.58 sec)
//sql99语法
//优点：连接的条件是独立的，连接之后如果还需要继续筛选，再往后继续添加where
//(inner表示内连接，可以省略)
mysql> select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.04 sec)
内连接之非等值连接
找出每个员工的薪资等级，要求显示员工姓名，薪资，薪资等级
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.16 sec)
内连接之自连接
查询员工的上级领导，要求显示员工名和对应的领导名
mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+
14 rows in set (0.03 sec)
//技巧：将一张表看作两张表
mysql> select a.ename as '员工名',b.ename as '领导名' from emp a join emp b on a.mgr=b.empno;
+--------+--------+
| 员工名  | 领导名  |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
13 rows in set (0.05 sec)
//因为king没有领导，所以只有13条记录
外连接
右外连接

select e.ename,d.dname from emp e right outer join dept d on e.deptno=d.deptno;

right表示什么：right表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联左边的表。

在外连接中，两张表连接产生了主次关系。

outer表示外连接，可以省略，带着可读性高

查询每个员工所在部门名称，显示员工名和部门编号。
mysql>  select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.03 sec)
左外连接
mysql> select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.03 sec)
三张表的连接

多表连接语法：

  • select …
  • from a join b
  • on a和b的连接条件
  • join c
  • on a和c的连接条件
  • join d
  • on a和d的连接条件

一条sql中内连接和外连接可以混合，都可以出现。

找出每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资，薪资等级
mysql> select e.ename,d.dname,e.sal,s.grade
    -> from
    -> emp e join dept d on e.deptno=d.deptno
    -> join
    -> salgrade s on e.sal between s.losal and s.hisal;
+--------+------------+---------+-------+
| ename  | dname      | sal     | grade |
+--------+------------+---------+-------+
| SMITH  | RESEARCH   |  800.00 |     1 |
| ALLEN  | SALES      | 1600.00 |     3 |
| WARD   | SALES      | 1250.00 |     2 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| MARTIN | SALES      | 1250.00 |     2 |
| BLAKE  | SALES      | 2850.00 |     4 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
| TURNER | SALES      | 1500.00 |     3 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| JAMES  | SALES      |  950.00 |     1 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| MILLER | ACCOUNTING | 1300.00 |     2 |
+--------+------------+---------+-------+
14 rows in set (0.03 sec)
子查询

什么是子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

select

​ …(select)

from

​ …(select)

where

​ …(select)

where语句中的子查询
找出比最低工资高的员工姓名和工资
mysql> select ename,sal from emp where sal>(select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.03 sec)
from子句中中的子查询

**注意：from后面的子查询，可以将子查询的查询结果当作一张临时表。

找出每个部门的平均工资的薪资等级
//第一步：找出每个部门的平均工资
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.02 sec)
//第二步：找出平均工资的薪资等级
mysql> select e.deptno,s.grade from (select deptno,avg(sal) as avg from emp group by deptno) e join salgrade s on e.avg between losal and hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     20 |     4 |
|     30 |     3 |
|     10 |     4 |
+--------+-------+
3 rows in set (0.04 sec)
select后面出现的子查询
找出每个员工的部门名称，要求显示员工名，部门名，部门编号。
mysql> select e.ename,e.deptno,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+
14 rows in set (0.38 sec)

union合并查询结果集

查询工作岗位是MANAGER和SALESMAN的员工
mysql> select ename,job from emp where job ='MANAGER' or job='SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.03 sec)

mysql> select ename,job from emp where job in ('MANAGER','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.03 sec)

mysql> select ename,job from emp where job ='MANAGER' union select ename,job from emp where job ='SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.03 sec)

使用union的优点：

union的效率要高一些，对于表的连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。。。但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。

  • a 连接 b 连接 c

  • a 10条记录

  • b 10条记录

  • c 10条记录

    匹配次数：1000；

  • a 连接 b一个结果：10*10=100；

  • a连接c一个结果：10*10=100；

    使用union的话是：100次+100次=200次；

    union在使用时的注意事项

    union在进行结果集合并的时候，要求两个结果集的列数相同。

    mysql> select ename,job from emp where job ='MANAGER' 
	   union 
	   select ename from emp where job ='SALESMAN';
1222 - The used SELECT statements have a different number of columns
沐雨~橙风
关注
专栏目录
MySQL多表查询
m0_51779342的博客
03-14 2115
MySQL多表查询 多表查询就是从多张表查询数据 若不加限制条件，直接使用select语句进行多表查询，将会产生笛卡尔积 笛卡尔积就是把所有集合元素的所偶有组合都列举出来，即A的个数乘上B的个数 -- 删除表 DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY auto_increment, -- 部门编号,主键自增 dep_n
mysql 多表查询效率优化_详解Mysql多表联合查询效率分析及优化_MySQL - join
weixin_29827279的博客
01-26 1927
...。MySQL数据库支持如下的联接查询：CROSS JOIN(交叉联接)INNER JOIN(内联接)OUTER JOIN(外联接)其它在进行各种联接操作时，一定要回忆一下在《SQL逻辑查询语句执...代码如下:SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.columnb. RIG...
mysql多表查询简单使用
满堂花醉三千客,一剑霜寒十四州
08-26 181
文章目录多表查询内连接左连接右连接完全连接 创建张表 mysql> select * from biao1; +------+------+------+ | a | b | c | +------+------+------+ | 1 | a | one | | 1 | a | one | | 1 | a | one | | 1 | a | one | | 1 | b | two | | 1 | b
MySQL 子查询
weixin_65637841的博客
06-18 2789
1..子查询介绍 2.需求分析与问题解决 3. 单行子查询 4. 多行子查询
Mysql将多表合并为一张表查询其共有的字段（列）信息_联合查询 UNION
qq_51444577的博客
06-04 1108
可以查询每个表的数据，然后两表间就可通过 union 来把多表合为一表查询 如：有两张表 student x
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
MySQL多表查询大全（超精确）
qq_53518630的博客
05-02 7275
目录 1、等值查询 2、内连接 3、外连接 3.1、左外连接 3.2、右外连接 3.3、完全外连接 4、交叉连接 5、自连接 6、联合查询 如果我们要查询的数据分布在不同的表时，那么需要连接多张表进行多表查询。 --而这些表当中，必须存在主外键关系（直接关联） --如果不存在直接关系，则必定存在间接关系，否则业务上是没有意义的 1、等值查询 select 字段集合 from 表1,表2,...,表n where 条件 (连接条件，一般使用主外键) #注意： 条件..
Mysql 多表查询详解
热门推荐
Jintao_Ma的博客
04-27 12万+
纸上得来终觉浅 1.前言：上篇讲到Mysql中关键字执行的顺序，只涉及了一张表；实际应用大部分情况下，查询语句都会涉及到多张表格 : 1)多表连接有哪些分类； 2)针对这些分类有哪些连接方法？ 3)这些连接方法分别作用于哪些应用场景？ 这篇针对这三个点通过实例来讲述，目的是穷尽所有的场景和所有的方法，并且对每个方法的使用做实例。 2.假设有两张表格A和B，把表格当作一个集合，那么表格中
Mysql详细教程（三）多表查询
qq_38650031的博客
10-22 372
多表查询 查询语法 select 列表名称 from 表名列表 where ... 准备sql # 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'); # ...
学习MySQL-第五章
qq_52905520的博客
09-13 1256
学习MySQL-第五章
011MySQL子查询 和 多表查询 删除
gh_xiaohe的博客
04-15 2239
常用的 SQL 标准有哪些 笛卡尔积（或交叉连接） 多表查询的好处 多表查询分类讲解 等值连接非等值连接 自连接 vs 非自连接 内连接 vs 外连接 SQL92 SQL99 SQL92：使用(+)创建连接 SQL99语法实现多表查询 7种SQL JOINS的实现 SQL99语法新特性 自然连接 USING连接
mysql查询三个表同一字段_MYSQL 查询一个表内同字段的所有信息
weixin_34954308的博客
01-19 916
house表+--------+---------+-------+-----------------+---------+|id|catid|title|url|username|updatetime|+--------+---------+-------+-----------------+---------+111aaawww.c...house表+--------+---------+--...
mysql一张表多个字段关联另一张表查询
tongluren381的博客
03-17 3095
mysql一张表多个字段关联另一张表查询 如下：一张订单表多个字段关联用户表： 1.链表查询 SELECT cu.id AS 'id', cu.version AS 'version', cu.cid AS 'cid', cu.uid AS 'uid', cu.shopName AS 'shopName', cu.address AS 'address', cu.totalPrice AS 'totalPrice', cu.orderType AS 'orderType', cu.state AS
mySql多表高级查询总结
James_liPeng的博客
05-28 604
mySql多表查询总结 （需要有一定的sql基础） 简单的多表查询 1.1. 多表查询语法 select 字段1,字段2... from 表1,表2... [where 条件] 例子： 查询人员和部门所有信息 select * from person, dept where person.did = dept.did; 多表链接查询 2.1. 多表连接查询语法(重点) SELECT 字......
mysql 多张表联合查询、一个字段 结构相同
soekchl521的博客
02-07 2630
select * from table1 union all select * from table2
MySQL多表查询深入解析：SQL1999语法实战
"MySQL多表查询详解下" 在MySQL中，多表查询是数据库操作的核心技能之一，它允许我们从多个相关的表中获取数据，以满足复杂的业务需求。本篇文章主要聚焦于SQL1999标准的多表查询语法，包括各种连接类型，如内连接...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值