Mysql----关联查询

最新推荐文章于 2025-01-05 19:48:40 发布
最新推荐文章于 2025-01-05 19:48:40 发布
阅读量586 收藏
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/WQY992/article/details/103313054
版权

测试表

dept表

创建dept表：

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(2) NOT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

在这里插入图片描述

emp表

创建emp表：

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(10),
`job` varchar(9),
`mgr` int(4),
`hiredate` date,
`sal` int(7),
`comm` int(7),
`deptno` int(2),
PRIMARY KEY (`empno`),
KEY `fk_deptno` (`deptno`),
CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN','7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, '10');
INSERT INTO `emp` VALUES ('8002', 'IRONMAN', 'MANAGER', '7839', '1981-06-09', '1600', null, '10');
INSERT INTO `emp` VALUES ('8003', 'SUPERMAN', 'MANAGER', '7839', '1981-06-09', '1600', null, null);

在这里插入图片描述

1、笛卡尔积

在做多张表查询时，我们使用某一张表中的每一条记录都与另外一张表的所有记录进行组合。比如表A有x条记录，表B有y条记录，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。

示例

select * from emp,dept;

则一共有16*4=64条记录

2、等值连接

在做多张表查询时，这些表中会存在着有关联的两个字段。使用某一张表中的一条记录的一个字段与另外一张表中的相关字段进行匹配，组合成一条新记录。使用"="连接关联字段

示例
显示员工的信息以及该员工所属部门的信息：

SELECT * FROM emp e,dept d where e.deptno=d.deptno;

在这里插入图片描述

3、内连接

内连接返回所有满足条件的记录，查询效果与等值连接一样。
关键字：join on
用法：表A [inner] join 表B on 关联条件

示例
显示员工的信息以及该员工所属部门的信息：

SELECT * FROM emp e join dept d on e.deptno=d.deptno;

在这里插入图片描述

4、外连接

在做关联查询时，我们所需要的数据，除了找到那些满足关联条件的数据外，有时候还需要不满足关联条件的数据。此时则需要使用外连接。

会涉及到两个概念：

  • 驱动表(主表)：除了显示满足条件的数据，还需要显示不满足条件的数据的表
  • 从表(副表)：只显示满足关联条件的数据的表

注意：mysql外连接只支持左外连接，右外连接，不支持全外连接

  1. 左外连接：
    表A left [outer] join 表B on 关联条件。
    表A是驱动表，表B是从表
  2. 右外连接：
    表A right [outer] join 表B on 关联条件
    表B是驱动表，表A是从表
  3. 全外连接：
    两张表的数据不管满不满足条件，都做显示。
    表A full [outer] join 表B on 关联条件

示例
左外连接：

SELECT * FROM emp e left join dept d on e.deptno=d.deptno;

此时：
emp是驱动表，dept是从表

在这里插入图片描述

右外连接：

SELECT * FROM emp e right join dept d on e.deptno=d.deptno;
此时：
dept是驱动表，emp是从表

在这里插入图片描述

5、自连接

自连接是一种特殊的关联查询。数据的来源是同一个表，这样的表内的多个字段要存有关系。我们要使用表别名来虚拟出两个表。

示例
查询员工编号、姓名、职位及其上司的编号、姓名、职位：

SELECT e1.empno,e1.ename,e1.job,e1.mgr,e2.ename,e2.job FROM emp e1,emp e2 where e1.mgr=e2.empno;

上述语句不够严谨，当员工没有上司时候，则不显示（因此，通常做查询时，需要注意数据，并决定是否使用外连接）

SELECT e1.empno,e1.ename,e1.job,e1.mgr,e2.ename,e2.job 
FROM emp e1 
left join 
emp e2 
on e1.mgr=e2.empno;

在这里插入图片描述

查询（所有）员工编号、姓名、职位及其下属的编号、姓名、职位：

下列语句只能查询出有下属的员工的信息，没有下属的员工，则无法显示（即下列语句会丢数据）
SELECT e1.empno,e1.ename,e1.job,e2.empno,e2.ename,e2.job FROM emp e1,emp e2 where e1.empno=e2.mgr;

严谨：
SELECT e1.empno,e1.ename,e1.job,e2.empno,e2.ename,e2.job  
FROM emp e1 
left join 
emp e2 
on e1.empno=e2.mgr;

在这里插入图片描述

6、高级关联查询

有时候，我们要查询的数据，一个简单的查询语句完成不了。并且，我们使用的数据，表中不能直观体现出来，而是预先经过一次查询才会有所体现。那么先执行的查询，我们称之子查询。被子查询嵌入的查询语句称之为父查询或主查询。

子查询返回的数据特点：

  • 可能是单行单列的数据。
  • 可能是多行单列的数据
  • 可能是单行多列的数据
  • 可能是多行多列的数据

子查询所在的位置：

  • 子查询可以在where子句中
  • 子查询可以在from子句中
  • 子查询可以在having子句中
  • 子查询可以在select字句中，相当于外连接的另外一种写法

1、子查询在where子句中

查询FORD的上司信息

select * from emp where empno = (select mgr FROM emp where ename='FORD');

查询工资大于JONES工资的所有员工姓名，职位，工资

select ename,job,sal from emp where sal>(select sal FROM emp where ename='JONES');

查询部门’SALES’里的所有员工的信息

select * from emp where deptno=(select deptno FROM dept where dname='SALES');

查询和员工姓名SCOTT同职位的员工信息

select * from emp where job=(select job FROM emp where ename='SCOTT');

查询薪水比所有员工的平均薪水高的员工信息

select * from emp where sal>(select avg(ifnull(sal,0)) FROM emp);

查询出部门中的人职位有SALESMAN但是自己职位不是SALESMAN的员工信息

select * from emp where deptno in (select distinct deptno FROM emp where job='SALESMAN') and job<>'SALESMAN';

查询有员工的部门信息

select deptno,dname,loc from dept d where exists (select * from emp e where d.deptno =e.deptno);

注意：
exists 关键字
有时候，子查询需要引用主查询的字段数据，我们使用exists关键字。exists后面的子查询至少返回一条记录，则整个条件为true;

2、子查询在from子句中

查询员工的姓名，工资，及其部门号，部门的平均工资和平均奖金，按照部门号升序

select ename,sal,e1.deptno,e2.avgsal,e2.avgcomm from emp e1 
left join 
(select deptno,avg(ifnull(sal,0)) avgsal,avg(ifnull(comm,0)) avgcomm from emp group by deptno)e2 
on 
e1.deptno=e2.deptno 
order by e1.deptno ;

注意：
需要用到左外连接（left join），且emp充当驱动表，否则，无法查出所有的员工信息

查询部门号，部门名称，地址，最高工资，总人数

select d.deptno,dname,loc,maxsal,sumpeople from dept d 
left join
(select deptno,max(sal) maxsal,count(*) sumpeople from emp group by deptno) e 
on 
d.deptno=e.deptno;

查询工资大于本部门平均工资的员工的姓名、工资、部门号、部门平均工资，按照部门号降序排序

select ename,sal,e1.deptno,avgsal from 
emp e1,
(select deptno,avg(ifnull(sal,0)) avgsal from emp group by deptno) e2 
where 
e1.deptno=e2.deptno 
and 
sal>avgsal 
order by e1.deptno desc;
 
	或
 
select ename,sal,e1.deptno,avgsal from emp e1 
join 
(select deptno,avg(ifnull(sal,0)) avgsal from emp group by deptno) e2 
on 
(e1.deptno=e2.deptno and sal>avgsal) 
order by e1.deptno desc;

3、子查询在having子句中

查询部门平均工资大于30号部门平均工资的部门号、平均工资

select deptno,avg(ifnull(sal,0)) avgsal from emp 
group by deptno 
having 
avgsal>(select avg(ifnull(sal,null)) avg30 from emp where deptno=30);

4、子查询在select子句中

查询员工编号，姓名，职位，和其上司编号，姓名，职位

select e1.empno,e1.ename,e1.job,
(select e2.empno  from emp e2 where e2.empno=e1.mgr)mgrno,
(select e3.ename  from emp e3 where e3.empno=e1.mgr)mgrname,
(select e4.job  from emp e4 where e4.empno=e1.mgr)mgrjob
 from emp e1

查询每个员工的姓名，工资，部门号，及其部门的平均工资、工资之和，按照部门号升序排序

select ename,sal,deptno,
(select avg(ifnull(sal,0)) from emp where deptno = e1.deptno group by deptno) avgsal,
(select sum(sal) from emp where deptno=e1.deptno group by deptno) sumsal 
from emp e1 order by deptno asc

参考

https://blog.youkuaiyun.com/Michael__One/article/details/84870766
https://blog.youkuaiyun.com/qq_39192827/article/details/84404353

自关联查询：使用MySQL进行数据库查询
2301_79713933的博客
09-26 165
无论是查询员工及其直属经理的信息，还是查询员工及其所有下属的信息，都可以通过适当的SQL语句来实现。在上述示例中，我们创建了一个名为"employees"的表，包含了"员工ID"、"员工姓名"和"直属经理ID"等字段。通过指定一个初始条件（例如，要查询的员工ID），并逐步迭代地连接子查询，最终可以获取到该员工及其所有下属的信息。要查询员工及其所有下属的信息，我们可以使用递归查询的方式。接下来，我们将介绍两个常见的自关联查询场景：查询员工及其直属经理的信息，以及查询员工及其所有下属的信息。
mysql中的自关联详解_Mysql中的关联查询(内连接，外连接，自连接)
weixin_34837388的博客
01-28 2328
转载自 -->https://blog.youkuaiyun.com/lu0422/article/details/78892497 在使用数据库查询语句时，单表的查询有时候不能满足项目的业务需求，在项目开发过程中，有很多需求都是要涉及到多表的连接查询，总结一下mysql中的多表关联查询一，内连接查询是指所有查询出的结果都是能够在连接的表中有对应记录的。以t_employee(员工表)和t_dept(部...
MySQL 自关联查询
weixin_34364135的博客
08-24 872
定义表areas，结构如下 id atitle pid 因为省没有所属的省份，所以可以填写为null 城市所属的省份pid，填写省所对应的编号id 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息 创建areas表的语句如下： ...
MySQL查询 8:自关联查询
忘川
12-04 632
设计省信息的表结构provinces id title proid 设计市信息的表结构citys id title proid 设计区县信息表结构areas id title proid title表示名称，proid表示所属上一级的id值，比如citys.proid 是对应所属省份的id，areas.proid对应的是所属城市的id值 问题： 能不能将三个表合成一张表呢？ 思考： 观察三张表发现，表的结构都是一样的，存储的都是
mysql自关联查询的基本操作
Learn_and的博客
07-04 338
自关联查询的基本操作 准备工作： sql数据文件在下面连接自取 提取码为：n0ug 查询所有的省和自治区 select * from province where pid=0; 统计中国所有的省和自治区和直辖市： 查询广东省都有哪些城市(想象成两张表)： select p.name,s.name from province as p inner join province as s on s.pid=p.id where p.name = "广东省"; ...
mysql-8.0.33安装包.zip
09-23
从标签来看，这个安装包不仅适用于当前版本的MySQL用户，也与Java语言和早期版本的MySQL 5有关联。这表明文件的潜在用户群体不仅仅局限于使用最新版本MySQL的开发者，同时也服务于那些可能仍然在使用MySQL 5或者开发...
mysql-5.7.42-el7-x86-64.tar
12-19
MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 MySQL是可以定制的，采用了GPL（GNU General Public License）协议，你可以修改...
MySQL-Front_V5.4.4.153_Setup.rar
11-21
4. 数据可视化：可以生成各种图表，如统计图、关系图等，帮助理解数据间的关联和分布，便于数据分析。 5. 数据备份与恢复：内置了数据备份和恢复功能，可定时自动备份，确保数据安全。 6. 用户权限管理：可以设置...
MySQL-client-5.6.29-1.linux_glibc2.5.x86_64.rar
03-26
在安装"MySQL-client-5.6.29-1.linux_glibc2.5.x86_64.rpm"时，通常会使用Linux的包管理器（如`yum`或`dnf`）来处理依赖关系，并将客户端软件及其关联库正确地安装到系统中。在64位Linux环境下，这个RPM包将为系统...
mysql-8.0.30-winx64.zip
07-27
MySQL支持创建多个数据库，每个数据库可以包含多个表，表之间可以通过键（如主键和外键）建立关联，实现数据的一致性和完整性。 MySQL 8.0的特性包括： 1. **增强的性能**：通过改进查询优化器和新的InnoDB存储...
深入理解mysql的自连接和join关联
12-15
一、mysql自连接 mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。我们举例说明，下面是商品采购表，我们需要找到采购价格比惠惠高的所有信息。 一般情况我们看到这张表我们第一时间用语句进行操作： SELECT * FROM shoping WHERE price>27 可想而知，这是有多么简单，假设你并不知道数据库表详细数据或者数据量相当庞大呢？作为一个数据库管理员，我们就要用别的方式迅速找出所需要的数据。 分步查询 最简单的一种方式，也是最容易想到操作： SELECT price FROM shopping WHERE name='\u60e0\u60e0' //得出pr
【MySQL】_自连接与子查询
m0_63299495的博客
02-28 1064
基于以上内容，本篇介绍自连接与子查询、合并查询；
MySql关联查询
最新发布
weixin_53694779的博客
01-05 2089
1.MySql中表的关联方式，查询方式。 2.关联查询的语句格式，注意事项，优缺点。
MYSQL自连接查询
big data的博客
03-12 2763
自连接
mysql 多表查询、子查询、自关联查询
weixuan_的博客
05-30 1190
mysql 多表查询、子查询、自关联查询
mysql 自关联解析_MySQL 自关联查询
weixin_39605345的博客
02-27 165
MySQL3：索引什么是索引 索引是对数据库表中一列或者多列的值进行排序的一种结构,所引用于快速找出在某个列中有一特定值的行.不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行.表越大,查询数据所花 ...每天一个linux命令---mount查询挂载服务的信息,使用挂载mount命令: [wapmail@app2linux04 monitor]$ mount |grep 17...
mysql一张表自关联查询_MYSQL 一张表的自关联查询
weixin_36115763的博客
01-19 1816
SELECTa.id id,a.gmt_create gmtCreate,a.gmt_modified gmtModified,a.app_id appId,a.app_name appName,a.dept_no deptNo,a.dept_name deptName,a.aone_product_id aoneProductId,a.aone_product_name aoneProductN...
MySQL连接查询：自连接
Ricky_youngone的博客
10-07 378
这个例子是一个特殊的，这个如果用内连接的话肯定是展示不出来的，这个还需要通过外连接里面的左连接才能展示出来。例子2：查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导， 也需要查询出来。自连接也就是把一个表看作是两个作用的表就好，也就是说我把emp看作员工表，也看做领导表。例子1：查询员工 及其 所属领导的名字。
Mysql竟然不支持同一个表的关联查询
flynetcn的专栏
11-09 3670
  晕！Mysql竟然不支持将同一个表用不同的别名进行关联查询，甚至不能在一条sql语句中两次使用同一个表。错误提示： #1137 - Cant reopen table: mytablecreate TEMPORARY table temp1 asselect * from(select 1 as id,50 as ic,2007-10-8as itime,a as wd 
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值