mysql—多表查询

本次博客带领大家学习mysql数据库中的多表查询。

• 说明：

多表查询是指基于两个和两个以上的表查询，在实际应用中，查询单个表可能不能满足你的需求。

• 多表查询练习：

  • 显示雇员名，雇员工资以及所在部门的名字。

  SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno = dept.deptno;
  1

  • 如何显示部门号为10的部门名、员工名和工资。

  SELECT dname,ename,sal FROM emp,dept WHERE emp.deptno = dept.deptno AND emp.deptno=10;
  1

  • 显示各个员工的姓名，工资，及其工资的级别。

  SELECT ename,sal,grade FROM emp , salgrade WHERE sal>=losal AND sal<= hisal;
  
  SELECT ename,sal,grade FROM emp , salgrade WHERE sal BETWEEN losal AND hisal;
  1
  2
  3

自连接

• 自连接是指在同一张表的连接查询。[将同一张表看做两张表]
• 显示公司员工和他的上级的名字。

SELECT worker.ename 职员名,boss.ename 上级名 
	FROM emp worker, emp boss 
	WHERE worker.mgr = boss.empno;
1
2
3

• 自连接的特点：
  1. 把同一张表当做两张表使用。
  2. 需要给表取别名，表名 表别名。
  3. 列名不明确，可以指定列的别名，列名 as 列的别名。

子查询

• 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

• 单行子查询是指只返回一行数据的子查询语句。

  • 如何显示与SMITH同一部门的所有员工？

  SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');
  1

• 多行子查询：多行子查询指返回多行数据的子查询，使用关键字in。

  • 如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10部门自己的雇员？

  SELECT ename,job,sal,deptno FROM emp WHERE job IN (
  		SELECT DISTINCT job 
  				FROM emp 
  					WHERE deptno = 10) AND deptno != 10;
  1
  2
  3
  4

• 子查询可以当做临时表使用。

  • 查询emp表中各个部门中，工资最高的员工。

  SELECT empno,emp.deptno,ename,sal FROM (
  	SELECT deptno ,MAX(sal) max_sal FROM emp GROUP BY deptno) temp,emp 
  	WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal;
  1
  2
  3

• 在多行子查询中使用all操作符。

  • 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

  SELECT ename,sal,deptno 
  	FROM emp 
  	WHERE sal > ALL(
  		SELECT sal FROM emp WHERE deptno = 30);
  1
  2
  3
  4

• 在多行子查询中使用any操作符。

  • 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号。

  SELECT ename,sal,deptno 
  	FROM emp 
  	WHERE sal > ANY(
  		SELECT sal FROM emp WHERE deptno = 30);
  1
  2
  3
  4

• 多列子查询则是指查询返回多个列数据的子查询语句。

  • 如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)。

  SELECT * FROM emp WHERE (deptno , job) = (
  		SELECT deptno , job FROM emp WHERE ename = 'allen')
  		AND ename !='allen';
  1
  2
  3

子查询的练习

• 练习一：查找每个部门工资高于本部门平均工资的人的资料。

SELECT ename,sal,temp.avg_sal,emp.deptno FROM emp,(
	SELECT AVG(sal) avg_sal,deptno FROM emp GROUP BY deptno) temp
	WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal;
1
2
3

• 练习二：查找每个部门工资最高的人的详细资料。

SELECT * FROM (
	SELECT deptno,MAX(sal) max_sal FROM emp GROUP BY deptno) temp,emp 
	WHERE temp.deptno = emp.deptno AND emp.sal = temp.max_sal;
1
2
3

• 练习三：查询每个部门的信息(包括：部门名，编号，地址)和人员数量。

SELECT dname,dept.deptno,loc,temp.per_num FROM dept,(
	SELECT COUNT(*) per_num, deptno FROM emp GROUP BY deptno) temp
	WHERE dept.deptno = temp.deptno
1
2
3

表的复制和去重

• 自我复制数据(蠕虫复制)：有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法创建海量数据。

CREATE TABLE my_tab01 (
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT);

DESC my_tab01;

SELECT * FROM my_tab01;

-- 演示如何自我复制
-- 1. 先把emp表的记录复制到my_tab01
INSERT INTO my_tab01 (id,`name`,sal,job,deptno)
	SELECT empno ,ename, sal,job,deptno FROM emp;
-- 2. 自我复制
INSERT INTO my_tab01 SELECT * FROM my_tab01;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 如何删除掉一张表重复记录。

-- 如何删除掉一张表重复记录。
-- 1. 先创建一张表 my_tab02
-- 2. 让 my_tab02 有重复的记录。

CREATE TABLE my_tab02 LIKE emp;

INSERT INTO my_tab02 SELECT * FROM emp;

SELECT * FROM my_tab02;
-- 3.考虑去重
-- (1) 先创建一个临时表 my_tmp, 该表的结构和 my_tab02 一样。
CREATE TABLE my_tmp LIKE my_tab02;
-- (2) 把my_tab02的记录 通过distinct 关键字 处理后把 记录复制到 my_tmp;
INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02;
SELECT * FROM my_tmp;
-- (3) 清除掉 my_tab02 记录
DELETE FROM my_tab02;
-- (4) 把my_tmp 表的记录复制到 my_tab02
INSERT INTO my_tab02 SELECT * FROM my_tmp;
-- (5) drop 掉 临时表my_tmp
DROP TABLE my_tmp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

合并查询

• 有时在实际应用中，为了合并多个select 语句的结构，可以使用集合操作符号union，union all。
• union all:就是将两个查询结果合并，不会去重。

SELECT ename,sal,job FROM emp WHERE sal>2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER';
1
2
3

• union:就是将两个查询结果合并，会去重。

SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER';
1
2
3

外连接

1. 左外连接：如果左侧的表完全显示我们就说是左外连接。

基本语法：select .. from 表1 left join 表2 on 条件 
1

2. 右外连接：如果右侧的表完全显示我们就说是右外连接。

基本语法：select .. from 表1 right join 表2 on 条件 
1

• 使用左外连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空)

CREATE TABLE stu(
	id INT,
	`name` VARCHAR(32));
	
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');

SELECT * FROM stu

CREATE TABLE exam(
	id INT,
	grade INT);
	
INSERT INTO exam VALUES(1,56),(2,76),(11,8);

SELECT * FROM exam;

SELECT `name`,stu.id,grade
	FROM stu RIGHT JOIN exam
		ON stu.id=exam.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• 使用右外连接(显示所有成绩，如果没有名字匹配，显示空)

SELECT `name`,stu.id,grade
	FROM stu RIGHT JOIN exam
		ON stu.id=exam.id;
1
2
3

• 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门名。

  • 使用左外连接实现

  SELECT dept.dname,ename,job,emp.deptno 
  	FROM dept LEFT JOIN emp 
  		ON emp.deptno = dept.deptno;
  1
  2
  3

  • 使用右外连接实现

  SELECT dept.dname,ename,job,emp.deptno 
  	FROM emp RIGHT JOIN dept 
  		ON emp.deptno = dept.deptno;
  1
  2
  3

​