MySQL(多表查询)

 

目录

一、多表查询 

1、笛卡尔积

2、等值查询

3、自查询

4、内连接查询

5、外连接

6、子查询

---

一、多表查询 

多表查询就是从多张表中去查询数据，在实际开发中大多数情况数据都是存储在多张中的，它们通过一个关联关系连接起来，这样就可以通过这个关联关系去查询到想要的数据。

1、笛卡尔积

        表查询中的笛卡尔乘积现象：多行表在查询时，如果定义了无效连接或者漏写了连接条 件，就会产生笛卡尔乘积现象，所谓的笛卡尔乘积即是每个表的每一行都和其他表的每一 行组合。

笛卡尔乘积

SELECT * FROM emp,dept;

2、等值查询

通常是在存在主键外键关联关系的表之间的连接进行，使用"="连接相关的表

select dept.dname,emp.ename from emp,dept where dept.deptno = emp.deptno;

3、自查询

        查询当前公司员工和所属上级员工的信息

select e1.empno as 员工编号,e1.ename as 员工姓名,e2.empno as 领导编号,e2.ename as 领导姓 名 from emp as e1,emp as e2 where e1.mgr = e2.empNo;

4、内连接查询

内连接查询使用 inner join 关键字实现， inner 可以省略。内连接查询时，条件用 on 连接，多个条件使用 () 将其括起来.

--查询每个部门的所有员工

select dept.name,emp.name from emp inner join dept on emp.deptno = dept.deptno;

5、外连接

外连接分为左外连接（ left outer join ） 和右外连接（ right outer join ）其值 oute r 可以省略。外连接查询时，条件用 on 连接，多个条件使用 () 将其括起来. 左外连接表示以左表为主表，右外连接表示以右表为主表。查询时将主表信息在从表中进 行匹配

(1)左连接

left join

(2)右连接

right join

6、子查询

存在于另外一个SQL语句中、被小括号包起来的查询语句就是子查询。相对于子查询来 说，在外部直接执行的查询语句被称作主查询 子查询分为： 单列子查询: 返回单行单列数据的子查询 单行子查询: 返回单行多列数据的子查询 多行子查询: 返回数据是多行单列的数据 关联子查询: 子查询中如果使用了外部主SQL中的表或列，就说这个子查询跟外部SQL是 相关的

(1)单行单列

=

-查询软件部门下的所有员工

select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = '软 件部' );

(2)多行子查询

in 子查询中所有的记录

>any 表示大于子查询中的任意一个值，即大于最小值

>all 表示大于子查询中的所有值，即大于最大的值

(3)单行多列

-查询公司中和员工***

相同薪水和奖金的员工

select * from emp e1 where (e1.sal,e1.comm) = (select e2.sal,e2.comm from emp e2 where e2.ename = '张青');

二、练习题

1.列出每个部门的平均收入及部门名称; 

select AVG(incoming),dept_name 
FROM dept LEFT JOIN emp 
ON dept.dept1 = emp.dept2 GROUP BY dept2;

2.财务部门的收入总和； 

select SUM(incoming),dept_name
FROM dept LEFT JOIN emp 
ON dept.dept1 = emp.dept2 WHERE dept_name = '财务';

3.It技术部入职员工的员工号   

select sid,dept_name
FROM dept LEFT JOIN emp 
ON dept.dept1 = emp.dept2 WHERE dept_name = 'IT技术';

4.财务部门收入超过2000元的员工姓名 
select `name`,dept_name
FROM dept LEFT JOIN emp 
ON dept.dept1 = emp.dept2 WHERE dept_name = '财务' AND incoming > 2000;

5.找出销售部收入最低的员工的入职时间；  

select MIN(incoming),worktime_start,`name`
FROM dept LEFT JOIN emp 
ON dept.dept1 = emp.dept2 WHERE dept_name = '销售';

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称  

SELECT `name`,sid,dept_name FROM dept LEFT JOIN emp ON dept.dept1 = emp.dept2
WHERE age < (select AVG(age)FROM emp);

7.列出每个部门收入总和高于9000的部门名称  
条件：每个部门 收入高于9000
结果：部门名称
SELECT SUM(incoming) AS s,dept_name FROM dept LEFT JOIN emp ON dept.dept1 = emp.dept2
GROUP BY dept2 HAVING s > 9000;

8.查出财务部门工资少于3800元的员工姓名 

SELECT NAME FROM dept LEFT JOIN emp ON dept.dept1 = emp.dept2
WHERE incoming < 3800 AND dept_name = '财务';

9.求财务部门最低工资的员工姓名；

SELECT `name` FROM emp WHERE incoming = (SELECT MIN(incoming) FROM dept LEFT JOIN emp ON dept.dept1 = emp.dept2
WHERE dept_name = '财务');

10.找出销售部门中年纪最大的员工的姓名   

SELECT `name` FROM emp WHERE age = (SELECT MAX(age) FROM dept LEFT JOIN emp ON dept.dept1 = emp.dept2
WHERE dept_name = '销售');

11.求收入最低的员工姓名及所属部门名称：

SELECT `name`,dept_name FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1
WHERE incoming =(SELECT min(incoming)FROM  emp);

12.求李四的收入及部门名称

SELECT incoming,dept_name FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1 
WHERE `name` ='李四';

13.求员工收入小于4000元的员工部门编号及其部门名称

SELECT `name`,dept_name,dept1 FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1 
WHERE incoming < 4000;

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；

SELECT MAX(incoming),`name`,dept_name,incoming FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1 
GROUP BY dept1 ORDER BY incoming DESC;

15.求出财务部门收益最高的俩位员工的姓名，工号，收益
条件：财务 收益最高俩位
结果：姓名 工号 收益
SELECT `name`,sid,incoming FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1 
WHERE dept_name = '财务' ORDER BY incoming DESC LIMIT 2;

16.查询财务部低于平均收入的员工号与员工姓名：

SELECT sid,NAME FROM emp WHERE incoming < (SELECT AVG( incoming) FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1
WHERE dept_name = '财务');

17.列出部门员工数大于1个的部门名称； 
条件：员工数>1
结果：部门名
SELECT COUNT(*) as s,dept_name FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1
GROUP BY dept1 HAVING s > 1;

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；

SELECT age,dept2,`name` FROM emp WHERE incoming > 3000 AND incoming <7500;

19.求入职于20世纪70年代的员工所属部门名称；

SELECT dept_name,`name` FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1
where worktime_start BETWEEN '1970-01-01' AND '1979-12-31';

20.查找张三所在的部门名称；
条件：张三
结果：部门名
SELECT dept_name FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1
WHERE `name` = '张三';

21.列出每一个部门中年纪最大的员工姓名，部门名称；
条件：每个部门 年纪最大
结果：员工姓名 部门名

22.列出每一个部门的员工总收入及部门名称；
条件：每个部门
结果：总收入 部门名
SELECT MAX(incoming),dept_name FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1
GROUP BY dept2;

23.列出部门员工收入大于7000的员工号，部门名称；
条件：收入大于7000
结果：员工号 部门名
SELECT incoming,dept_name,sid FROM emp LEFT JOIN dept ON emp.dept2 = dept.dept1
WHERE incoming > 7000;

24.找出哪个部门还没有员工入职；
条件：没有员工
结果：部门名
SELECT dept_name FROM dept LEFT JOIN emp ON emp.dept2 = dept.dept1
WHERE `name` IS NULL;

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
条件：先按部门号大小排序 再按入职时间排序
结果：表
SELECT * FROM dept LEFT JOIN emp ON emp.dept2 = dept.dept1
ORDER BY dept1 DESC,worktime_start;

26.求出财务部门工资最高员工的姓名和员工号
条件：财务部 工资最高
结果：姓名 员工号
SELECT NAME,sid FROM emp WHERE incoming =(SELECT MAX(incoming) FROM dept LEFT JOIN emp ON emp.dept2 = dept.dept1
WHERE dept_name ='财务');

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
条件：工资在7500到8500 年纪最大
结果：姓名 部门
SELECT `name`,dept_name FROM dept LEFT JOIN emp ON emp.dept2 = dept.dept1
WHERE age = (SELECT MAX(age) FROM emp WHERE incoming BETWEEN 7500 AND 8500);