多表查询SQL

已于 2023-01-28 11:41:48 修改
阅读量213 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库
于 2022-11-14 23:18:45 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Lix0325/article/details/127857821
版权

目录

一、建表语句

二、多表连接查询

2.1 内连接查询

2.2 外连接查询

2.3 三表连接查询

2.4 自连接查询

三、子查询

3.1 不相关子查询

3.2 相关子查询

四、综合练习

一、建表语句

员工表

CREATE TABLE `emp` (
  `empno` int(11),
  `ename` varchar(255) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `mgr` varchar(255) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` int(11) DEFAULT NULL,
  `comm` int(11) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', NULL, '20');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', NULL, '20');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', NULL, '30');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', NULL, '10');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', NULL, NULL);
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000', NULL, '10');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', NULL, '10');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', NULL, '20');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', NULL, '20');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', NULL, '30');
INSERT INTO `test`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', NULL, '30');

部门表

CREATE TABLE `dept` (
  `deptno` int(11),
  `dname` varchar(255) DEFAULT NULL,
  `loc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`dept` (`deptno`, `dname`, `loc`) VALUES ('10', 'ACCOUNTING', 'NEW YOUK');
INSERT INTO `test`.`dept` (`deptno`, `dname`, `loc`) VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `test`.`dept` (`deptno`, `dname`, `loc`) VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `test`.`dept` (`deptno`, `dname`, `loc`) VALUES ('40', 'OPERATIONS', 'BOSTON');

薪资等级表

CREATE TABLE `salgrade` (
  `grade` int(11),
  `losal` int(11) DEFAULT NULL,
  `hisal` int(11) DEFAULT NULL,
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`salgrade` (`grade`, `losal`, `hisal`) VALUES ('1', '700', '1200');
INSERT INTO `test`.`salgrade` (`grade`, `losal`, `hisal`) VALUES ('2', '1201', '1400');
INSERT INTO `test`.`salgrade` (`grade`, `losal`, `hisal`) VALUES ('3', '1401', '2000');
INSERT INTO `test`.`salgrade` (`grade`, `losal`, `hisal`) VALUES ('4', '2001', '3000');
INSERT INTO `test`.`salgrade` (`grade`, `losal`, `hisal`) VALUES ('5', '3001', '9999');

二、多表连接查询

2.1 内连接查询

多表查询：查询员工的员工编号、姓名、薪水和部门编号、部门名称

交叉连接：笛卡尔积，没有实际的意义，有理论的意义，让我们明白连接查询底层的原理

SELECT * FROM emp CROSS JOIN dept

自然连接：会自动的匹配所有的同名列（目前同名列只有deptno），并且同名列只保留一份
最大的有点：简单

SELECT * FROM emp natural JOIN dept
SELECT empno,ename,sal,deptno,dname FROM emp natural JOIN dept

缺点：没有指明各列属于哪个表，效率地下
解决：指定表明

SELECT emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname FROM emp natural JOIN dept

缺点：表明可能比较长
解决：起别名

SELECT e.empno,e.ename,e.sal,e.deptno,d.deptno,d.dname FROM emp e natural JOIN dept d

using子句

natural自然连接的缺点：会自动的按照所有的同名列进行匹配，如果希望只按照某个同名列进行匹配，怎么办？
解决：using子句，注意natural就不要了

SELECT e.empno,e.ename,e.sal,e.deptno,d.deptno,d.dname FROM emp e JOIN dept d USING (deptno)

on子句
natural、using的缺点：必须有同名列，如果有主键、外键不同名，怎么办？
解决：on子句

SELECT e.empno,e.ename,e.sal,e.deptno,d.deptno,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno

问题：到底使用哪种连接查询

  1. cross  不用
  2. natural、using：必须有同名列
  3. on 不管是否有同名列，均可使用，可读性强
  4. 建议使用on子句

内连接查询带筛选条件

SELECT
	e.empno,
	e.ename,
	e.sal,
	e.deptno,
	d.deptno,
	d.dname
FROM
	emp e
JOIN dept d ON e.deptno = d.deptno
WHERE sal > 2000 
ORDER BY sal DESC

内连接查询：只显示匹配的数据，有些员工没有部门，这些员工不会被匹配

2.2 外连接查询

外连接查询：除了显示匹配的数据，还可以匹配全部或者部分不匹配的数据

左外连接 left join：除了显示匹配的数据，还可以显示左表的不匹配数据

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno

右外连接 right join：除了显示匹配的数据，还可以显示右表的不匹配数据

SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno

全外连接 full outer join：除了显示匹配的数据，还可以显示左、右表的不匹配数据
MySQL还不支持全外连接，Oracle支持
变通的解决方案

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
UNION
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno

2.3 三表连接查询

多表查询：查询员工的员工编号、姓名、薪水和部门编号、部门名称、薪水等级

第一步：查询员工的员工编号、姓名、薪水和部门编号、部门名称

为什么使用左外连接？SCOTT虽然没有部门，但是他有薪水

SELECT
	e.empno,
	e.ename,
	e.sal,
	e.deptno,
	d.deptno,
	d.dname
FROM
	emp e
LEFT JOIN dept d ON e.deptno = d.deptno

第二步：查询员工的员工编号、姓名、薪水和部门编号、部门名称、薪水等级

SELECT
	e.empno,
	e.ename,
	e.sal,
	e.deptno,
	d.deptno,
	d.dname,
	sg.*
FROM
	emp e
LEFT JOIN dept d ON e.deptno = d.deptno 
JOIN salgrade sg ON e.sal BETWEEN sg.losal AND sg.hisal

2.4 自连接查询

查询员工的编号、姓名、上级编号、上级姓名

第一步：查询员工的编号、姓名、上级编号

SELECT empno,ename,mgr FROM emp

第二步：查询员工的编号、姓名、上级编号、上级姓名

SELECT
	e.empno,
	e.ename,
	e.mgr,
	m.empno,
	m.ename
FROM
	emp e    -- 员工表
LEFT JOIN emp m     -- 上级表
ON e.mgr = m.empno

三、子查询

子查询：查询的嵌套 外查询 子查询
不相关子查询：子查询可以独立运行；先运行子查询，在运行外查询
相关子查询：子查询不可以独立运行；先运行外查询，在运行子查询

3.1 不相关子查询

单行子查询：直接比较即可 < > =

查询所有比CLARK工资高的员工的信息

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'ClARK')

查询工资高于平均工资的雇员名字和工资

SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp)

查询和ClARK同一部门且比他工资高的雇员名字和工资

SELECT ename,sal FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'ClARK') AND sal > (SELECT sal FROM emp WHERE ename = 'ClARK')

查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息

SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND hiredate < (SELECT hiredate FROM emp WHERE ename = 'SCOTT')

多行子查询：不可以直接比较，需要借助关键字 in any all

查询工资低于任意一个职务为CLERK的员工工资的雇员信息

SELECT * FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK') AND job != 'CLERK'

查询工资比SALESMAN部门所有的雇员都高，雇员名字和工资

SELECT empno,ename,sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE job = 'SALESMAN')
SELECT empno,ename,sal FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE job = 'SALESMAN')

查询部门20中职务同部门10的雇员职务一样的雇员信息

SELECT * FROM emp WHERE deptno = 20 AND job in (SELECT job FROM emp WHERE deptno = 10)
SELECT * FROM emp WHERE deptno = 20 AND job = ANY (SELECT job FROM emp WHERE deptno = 10)

3.2 相关子查询

相关子查询：子查询不可以独立运行；先运行外查询，在运行子查询
好处：简单、功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）

查询最高工资的员工（不相关子查询）

SELECT * FROM emp WHERE sal = (SELECT max(sal) FROM emp)

查询本部门最高工资的员工（相关子查询）

SELECT * FROM emp e WHERE sal = (SELECT max(sal) FROM emp WHERE deptno = e.deptno)

查询工资高于其所在部门的平均工资的员工信息（相关子查询）

SELECT * FROM emp e WHERE sal > (SELECT avg(sal) FROM emp WHERE deptno = e.deptno)

四、综合练习

查询每个部门平均薪水的等级（连接查询，子查询，group by）

SELECT
	das.*,sg.grade
FROM
	(
		SELECT
			deptno,
			avg(sal) asl
		FROM
			emp
		GROUP BY
			deptno
	) das
JOIN salgrade sg ON das.asl BETWEEN sg.losal AND sg.hisal

Lix0325
关注
专栏目录
sql 多表连接查询
09-11
SQL 多表连接查询 SQL 多表连接查询是指在 SQL 语句中连接多个表，以便从不同表中获取数据。多表连接查询可以分为外连接、内连接、交叉连接等类型。下面详细介绍每种类型的连接查询。 一、外连接 外连接可以分为...
SQL多表连接查询实例分析（详细图文）
09-10
本文主要列举两张和三张表来讲述多表连接查询,需要的朋友可以参考下
SQL判断语句用法和多表查询
09-09
2. **SQL多表查询** 多表查询是指在一个查询中涉及两个或更多表的操作，通常用于联接（JOIN）不同表的数据。在给定的例子中，使用了简单的内连接（INNER JOIN）来实现三表查询： ```sql SELECT u.id, n.name, u....
SQL Server多表查询优化方案集锦
09-10
在SQL Server中，多表查询优化对于提升数据库性能至关重要，特别是在大数据量的环境中。本文将深入探讨几个关键的优化策略，以帮助改善SQL Server多表查询的效率。 1. **执行计划优化**：执行计划是SQL Server解析...
一个多表查询的SQL语句
09-11
多表查询SQL语句详解 多表查询是一种常见的数据库操作，用于从多个表中提取数据。今天，我们将深入探讨多表查询的SQL语句，了解如何使用单条SQL语句来解决复杂的查询问题。 多表查询的必要性 在实际应用中，我们...
MySQL中字段类型和Java对象中的数据类型对应关系
Zhuxiaoyu_91的博客
11-12 336
MySQL中字段类型和Java对象中的数据类型对应关系
./mysqld: error while loading shared libraries: libaio.so.1: cannot open sha
qq_42112846的博客
11-12 275
使用离线方式安装：rpm -ivh --nodeps mysql* ，执行 systemctl start mysqld.service发现启动不了，通过vi /var/log/mysql.log看到如下关键字：libraries: libaio.so.1，之前也是按照网上帖子各种修改都没有解决问题，库是Linux异步I/O库，这个库对MySQL是必需的，因为它依赖于这个库来进行异步I/O操作。2，如果执行了whereis libaio.so.1，没有找到该文件，就要手动安装下libaio。
MySQL库操作
zgwnb666的博客
11-08 949
MySQL库操作
MySQL分区
这个人很懒，什么都没有留下。
11-12 584
分区实际上是提供了一种物理上的数据组织方式，使得查询操作能够更加聚焦于相关的数据段，避免了全表扫描，从而提升了查询效率，特别是在数据量非常大的情况下。这种方式特别适合于那些可以根据某些明显的业务逻辑（如时间、地区等）被分割的大表。
使用Docker-compose安装mysql5.7
州仔
11-08 276
1.首先选择一个目录用来存放docker-compse文件以及mysql的数据（例如logs、conf）docker发现没有mysql5.7这个镜像会去自动下载，执行完以后MySQL启动成功。2.填写docker-compse.yml内容。宿主机需要开放3306端口。
MySQL 索引
果酱 の 博客
11-08 922
MySQL 索引是提高数据库性能的重要手段。通过选择合适的索引类型、创建必要的索引、定期维护索引和监控索引使用情况，可以大大提高数据库的查询性能。深入理解 MySQL 索引的原理和使用方法，对于构建高效的数据库应用至关重要。
[面试]Mysql的锁机制
ouyfg的博客
11-08 641
MySQL中的锁主要用于控制多个事务对数据库资源的并发访问，以确保数据的一致性和完整性。锁机制是数据库并发控制的核心，通过锁定数据库中的资源来防止并发操作引发的数据冲突。行锁允许多个事务在同一个表中并发执行不同行上的操作，而不会互相阻塞，因此具有较高的并发性。但相比于表锁，行锁需要更多的资源来维护锁的状态，开销更大。表锁是一种较粗粒度的锁机制，会锁定整个表，而不是某个具体的行或记录。综上，MySQL的锁机制是数据库并发控制的核心。除了表级锁和行级锁，MySQL还提供了其他类型的锁来支持不同的并发控制需求。
MySQL的ibtmp1文件详解及过大处理策略
sg_knight的专栏
11-11 451
在MySQL数据库中，ibtmp1文件是InnoDB存储引擎的临时表空间文件。它主要用于存储临时数据和中间结果，例如排序操作、临时表等。这个文件通常位于MySQL的数据目录下，文件名固定为ibtmp1。ibtmp1文件是非压缩的，并且默认配置为可以自动扩展，因此在处理大量临时数据时，这个文件可能会迅速增长，占用大量磁盘空间。ibtmp1文件是MySQL InnoDB存储引擎的临时表空间文件，用于存储临时数据和中间结果。在处理大量临时数据时，这个文件可能会迅速增长并占用大量磁盘空间。
MYSQL——用户管理
weixin_74269833的博客
11-12 212
mysql需要对登陆的用户进行管理，所有用户的信息都存放在名为mysql的数据库的user表中：密码不会明文显示，都是通过对密码做md5运算得到的。
【MySQL】MySQL中的函数之REGEXP_LIKE
白萝卜弟弟的专栏
11-12 252
在 MySQL 中，函数用于检查一个字符串是否与正则表达式匹配。不过需要注意的是，并不是所有版本的 MySQL 都支持的函数。这个函数是在 MySQL 8.0 版本中引入的。
【原创】java+ssm+mysql美食论坛网系统设计与实现
最新发布
计算机软件编程，包含Java、Php、Python、前后端、数据库等实战项目
11-12 577
使用java+ssm+mysql开发的吃货联盟社区，系统包含超级管理员，系统管理员、用户角色，功能如下：用户：主要是前台功能使用，包括注册、登录；查看论坛板块和板块下帖子；可以评论收藏帖子；系统留言；关于我们；用户个人中心可以修改个人信息；修改密码；查看我的帖子；我的评论；我的关注；我的粉丝；我的收藏；我的留言；退出系统； 超级管理员：管理员管理（可以新增管理员）；用户管理；帖子管理；留言管理；公告管理；友链管理；系统配置；修改个人资料；修改密码。管理员：和超级管理功能基本一致，只是少了新增管理员；
Windows Server服务器上安装的MySQLl数据库连接遇到10055错误
猿小白的博客
11-08 611
Windows Server 2016服务器上安装的MySQLl数据库连接遇到10055错误。
mysql 两阶段锁协议（2PL)的作用
ld_long的博客
11-07 301
两阶段锁是怎么运行的，以及如何证明他产生的调度得到的最终结果一定是和某个串行执行结果相同，就不写了，两阶段锁协议是什么网上可以找得到，证明我还没找到。而非串行调度指的是运行事务1的一个操作，然后运行事务2的操作，然后由运行事务1的操作。运行事务1事务2，结果要么是B=3，A=4（先执行事务1，再执行事务2），要么是A=6，B=7，（先执行事务2，再执行事务1）。然后各自写A,和B,结果是A=6，B=3，他和任意一个串行化执行（先执行事务1再执行事务2或先执行事务2再执行事务1）都不同。
深入探讨 MySQL 配置与优化：从零到生产环境的最佳实践20241112
Narutolxy的博客
11-12 135
MySQL 的配置和优化是一门艺术，合理的配置可以最大化硬件资源的利用率，并确保数据库的稳定性与性能。在实际应用中，建议根据业务特点动态调整配置，并借助性能监控工具不断优化。本文的内容为您提供了一个从入门到进阶的全面指导，希望能帮助您在工作中轻松驾驭 MySQL。如果您有任何问题或建议，欢迎留言讨论！
SQL实验：视图与多表查询操作详解
本次实验主要围绕数据库中的视图与多表查询展开，旨在让学生深入理解SQL语言在数据库管理中的应用，包括数据定义、数据操纵以及视图的创建与使用。实验由深圳大学的学生许佳妮在马里佳老师的指导下完成，使用了...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值