MySQL 多表查询

最新推荐文章于 2024-11-03 22:59:42 发布

　林先生

最新推荐文章于 2024-11-03 22:59:42 发布

阅读量455

点赞数 1

CC 4.0 BY-SA版权

文章标签： mysql

本文链接：https://blog.csdn.net/qq_45437885/article/details/130000780

本文详细介绍了SQL中的数据合并（UNION与UNIONALL）、连接查询（内连接、外连接、自然连接）以及子查询的使用方法，同时提供了创建数据库和表的脚本示例。内容涵盖从基础的查询操作到复杂的表间关联查询，适合学习数据库管理和SQL编程的读者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建表语句脚本放在最后

一、合并结果集

前提是两张表的列数相同

1.union 合并后去重复

SELECT * FROM student01
UNION
SELECT * FROM student02;

2.union all 合并后不去重复

SELECT * FROM student04
UNION ALL
SELECT * FROM student06;

二、连接查询

1.笛卡尔积假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)} 其中肯定有我们不要的数据，去除不重要的数据，就需要使用下一条方法

select * from emp,dept;

2.使用主外键关系做为条件来去除无用信息

将多张表连接进行查询

2.1 内连接

2.1.1 MySQL方言

select * from emp e,dept d where e.deptno = d.deptno;
# emp e ，e是别名

2.1.2 标准内连接 inner join ...on

select * from emp e inner join dept d on e.deptno = d.deptno;

2.2 外连接 left outer join...on

2.2.1 左外连接先将emp表查询出来，再查询dept表，dept不满足的用null显示

select * from emp e left outer join dept d on e.detpno =  d.deptno;

emp和dept表通过deptno列连接，左外连接即先将emp表查询出来，如图中1这部分；再查询dept表，即2这部分；由于emp中的保洁员没有deptno，所以dept表中查无此人，此时dept表中将不满足的用null显示。

2.2.2 右外连接 right outer join ... on

select * from emp e right outer join dept d on e.deptno = d.deptno;

emp和dept表通过deptno列连接，右外连接即先将dept表查询出来，如图中1这部分；再查询emp表，即2这部分；由于dept表中deptno为40，而emp表中没有这个部门的员工，此时emp表中将不满足的用null显示。

三、自然连接

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：前提是要连接的表有相同的字段名和列类型。

1.自然内连接 natural join

select * from emp natural join dept;

2.自然外连接分析过程同上

2.1 自然左外连接 natural left join

select * from emp natural left join dept;

2.2 自然右外连接 natural right join

select * from emp natural right join dept;

四、子查询

子查询就是嵌套查询，即SELECT中包含SELECT。

几个案例如下：

1.工资高于JONES的员工。

-- 工资高于JONES的员工。
select * from emp where sal >
(select sal from emp where ename = 'jones');

2.查询与SCOTT同一个部门的员工。

-- 查询与SCOTT同一个部门的员工。
select * from emp where deptno = 
(select deptno from emp where ename = 'scott');

3.工资高于30号部门所有人的员工信息

-- 工资高于30号部门所有人的员工信息
select * from emp where sal > 
(select max(sal) from emp where deptno = 30);

4.查询工作和工资与MARTIN（马丁）完全相同的员工信息

-- 查询工作和工资与MARTIN（马丁）完全相同的员工信息
select * from emp where (job,sal) in 
(select job,sal from emp where ename = 'martin')
AND ename != 'martin';

5.有2个以上直接下属的员工信息

-- 有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno IN 
(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2);

6.-- 查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

-- 查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
SELECT ename,sal,dname,loc FROM emp,dept 
WHERE emp.`deptno` = dept.`deptno` AND empno=7788;

7.-- 求7369员工编号、姓名、经理编号和经理姓名

-- 求7369员工编号、姓名、经理编号和经理姓名
SELECT e1.empno,e1.ename,e2.mgr,e2.ename FROM emp e1, emp e2 
WHERE e1.`deptno`=e2.`deptno` AND e1.empno=7369;

五、创表脚本语句

CREATE DATABASE java232002;
CREATE TABLE stu (
	sid	CHAR(6),
	sname		VARCHAR(50),
	age		INT,
	gender	VARCHAR(50)
);

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

CREATE TABLE emp(
	empno		INT,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		DECIMAL(7,2),
	deptno		INT
) ;

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);

CREATE TABLE dept(
	deptno		INT,
	dname		VARCHAR(14),
	loc		VARCHAR(13)
);

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');