本次博客带领大家学习mysql数据库中的多表查询。
- 说明:
多表查询是指基于两个和两个以上的表查询,在实际应用中,查询单个表可能不能满足你的需求。
-
多表查询练习:
- 显示雇员名,雇员工资以及所在部门的名字。
SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno = dept.deptno;
- 如何显示部门号为10的部门名、员工名和工资。
SELECT dname,ename,sal FROM emp,dept WHERE emp.deptno = dept.deptno AND emp.deptno=10;
- 显示各个员工的姓名,工资,及其工资的级别。
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;
自连接
- 自连接是指在同一张表的连接查询。[将同一张表看做两张表]
- 显示公司员工和他的上级的名字。
SELECT worker.ename 职员名,boss.ename 上级名
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
- 自连接的特点:
- 把同一张表当做两张表使用。
- 需要给表取别名,表名 表别名。
- 列名不明确,可以指定列的别名,列名 as 列的别名。
子查询
-
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询。
-
单行子查询是指只返回一行数据的子查询语句。
- 如何显示与SMITH同一部门的所有员工?
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');
-
多行子查询:多行子查询指返回多行数据的子查询,使用关键字in。
- 如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号,但是不含10部门自己的雇员?
SELECT ename,job,sal,deptno FROM emp WHERE job IN ( SELECT DISTINCT job FROM emp WHERE deptno = 10) AND deptno != 10;
-
子查询可以当做临时表使用。
- 查询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;
-
在多行子查询中使用all操作符。
- 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。
SELECT ename,sal,deptno FROM emp WHERE sal > ALL( SELECT sal FROM emp WHERE deptno = 30);
-
在多行子查询中使用any操作符。
- 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号。
SELECT ename,sal,deptno FROM emp WHERE sal > ANY( SELECT sal FROM emp WHERE deptno = 30);
-
多列子查询则是指查询返回多个列数据的子查询语句。
- 如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)。
SELECT * FROM emp WHERE (deptno , job) = ( SELECT deptno , job FROM emp WHERE ename = 'allen') AND ename !='allen';
子查询的练习
- 练习一:查找每个部门工资高于本部门平均工资的人的资料。
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;
- 练习二:查找每个部门工资最高的人的详细资料。
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;
- 练习三:查询每个部门的信息(包括:部门名,编号,地址)和人员数量。
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
表的复制和去重
- 自我复制数据(蠕虫复制):有时,为了对某个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. 先创建一张表 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;
合并查询
- 有时在实际应用中,为了合并多个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';
- union:就是将两个查询结果合并,会去重。
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER';
外连接
- 左外连接:如果左侧的表完全显示我们就说是左外连接。
基本语法:select .. from 表1 left join 表2 on 条件
- 右外连接:如果右侧的表完全显示我们就说是右外连接。
基本语法:select .. from 表1 right join 表2 on 条件
- 使用左外连接(显示所有人的成绩,如果没有成绩,也要显示该人的姓名和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;
- 使用右外连接(显示所有成绩,如果没有名字匹配,显示空)
SELECT `name`,stu.id,grade
FROM stu RIGHT JOIN exam
ON stu.id=exam.id;
-
列出部门名称和这些部门的员工信息(名字和工作),同时列出那些没有员工的部门名。
- 使用左外连接实现
SELECT dept.dname,ename,job,emp.deptno FROM dept LEFT JOIN emp ON emp.deptno = dept.deptno;
- 使用右外连接实现
SELECT dept.dname,ename,job,emp.deptno FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno;