Mysql多表查询

多表查询

有联合查询，连接查询，子查询

 联合查询

作用：合并结果集就是把两个select语句的查询结果合并到一起！

要求：被合并的两个结果：列数、列类型必须相同。

合并结果集有两种方式：

UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；

UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

如果多条查询语句查询出来的结果，字段数量不一致，在进行联合查询时会报错。

 连接查询

连接查询有内连接与外连接

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

 内连接

内连接是mysql的方言

内连接查询的结果必须满足所有条件

外连接 

外连接又分为左外连接和右外连接

左外连接：先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

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

右外连接：先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

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

左外连接和右外连接在少数表的时候没有什么差别，就是交换顺序就可以了，但是表多了顺序交换不了就只能用左或者右外连接了。

自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

注意事项:在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段

子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询出现的位置不同有不同的作用：

• where后，作为条件的一部分；

• from后，作为被查询的一条表；

• SELECT之后 ,作为被查询的一列;

 表的行列转换

把表的某一行或某一列转换为某一列或某一行

大多是使用case来完成实例如下

 

 

SELECT name ,
			sum(case course when 'hibernate' then  score else 0 end) hibernate ,
			sum(case course when 'spring' then  score else 0 end) spring ,
			sum(case course when 'struts' then  score else 0 end) struts
FROM tb_course GROUP BY name

 

1.列出员工表中每个部门的员工数，和部门号

Select count(1),deptno from emp group by deptno;

2.列出员工表中每个部门的员工数（员工数必须大于 3），和部门名称 

Select count(1),deptno from emp group by deptno having count(1) >3;

3.找出工资比WARD多的员工 

Select * from emp where sal >(select sal from emp where ename=’WARD’);

4.列出所有员工的姓名和其上级的姓名 

Select  e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr=e2.ename;

5.以职位分组，找出平均工资最高的两种职位

Select job From (Select AVG(sal) avg,job,@rownum:=@rownum+1 num from emp, (select @rownum:=0) t group by job order by avg DESC) b where b.num <=2;

Select AVG(sal) avg,job from emp group by job order by avg DESC limit 0,2

6.查找出不在部门 20，且比部门 20 中任何一个人工资都高的员工姓名、部门名称 

比任何人的工资高就是比工资最高的还要高 

Select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>all(select sal from emp where deptno = 20) and e.deptno<>20

7.得到平均工资大于 2000 的工作职种 

Select job from emp group by job having AVG(sal)>2000;

8.分部门得到  工资大于 2000 的所有员工的平均工资，并且平均工资还要大于 2500 

Select empno,AVG(sal) from emp where sal >2000 group by deptno having AVG(sal)>2500

9.得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 

Select d.deptno,d.dname,d.loc from (Select deptno,sum(sal) s from emp group by deptno ) e join dept d on e.deptno = d.deptno where e.s = (select min(e.s) from (Select deptno,sum(sal) s from emp group by deptno )  e);

10.分部门得到平均工资等级为 3 级（等级表）的部门编号 

SELECT deptno from (SELECT deptno,avg(sal) a from emp GROUP BY deptno) t join grade g on t.a BETWEEN g.lower and g.high where g.grade =3

11.查找出部门 10 和部门 20 中，工资最高第 3 名到工资第 5 名的员工的员工名字，部门名字，部门位置 

Select e.ename,d.dname,d.loc  from emp e join dept d on e.deptno = d.deptno where e.deptno in(20,10) order by sal desc limit 2,3;

12.查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入 

 Select e1.empno,e1.ename, sum(e1.sal+ifnull(e1.comm,0))  from emp e1 join emp e2 on e1.mgr=e2.empno where (Select sum(e1.sal+ifnull(e1.comm,0)) from emp)

 >(Select sum(e2.sal+ifnull(e2.comm,0)) from emp);

13.查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置 

Select e.ename,d.dname,d.loc

From emp e join dept d

On e.deptno = d.deptno

join grade g

on e.sal between g.lower and g.high

where g.grade <>4;

14.查找出职位和'MARTIN'  或者'SMITH'一样的员工的平均工资 

Select AVG(t.sal)

From (Select sal from emp where job in (select job from emp where ename in ('MARTIN', 'SMITH'))) t;

15.查找出不属于任何部门的员工 

Select *  from emp wherer e.deptno is null;

16.按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置） 

Select  d.dname,d.loc,count(1) num from emp e join dept d on e.deptno=d.deptno group by e.deptno ORDER BY num desc limit 1,4

1. 查询出 king 所在部门的部门号\部门名称\部门人数 

Select  e.deptno,d.dname,e.num from dept d join(SELECT COUNT(deptno) num ,deptno from emp where deptno =(Select deptno num from emp where ename='king')) e on e.deptno=d.deptno;

查询出 king 所在部门的工作年限最大的员工名字 

Select ename from emp where hiredate =

(Select min(hiredate) from emp where deptno = (select deptno from emp where ename='king')

1. 查询出工资成本最高的部门的部门号和部门名称 

Select max(e.s),d.dname

(Select deptno,sum(sal) s from emp group by deptno) e join dept d on e.deptno=d.deptno

20.查询工1500-资不在2850之间的所有雇员名及工资

Select ename,sal from emp where sal >2850 or sal <1500

• 高级SQL

1. 根据user数据表编写SQL查出所有name重复的记录且按照age降序(6)。

id	name	age
1001	zhangsan	21
1002	lisi	22
1003	zhangsan	20
1004	lisi	19
1005	wangwu	18
1006	zhaoliu	22

Select * from user u1,user u2 where u1.name = u2.name and u1.id<>u2.id order by age desc

1. 编写一条SQL实现下列功能(7)

表内容：

   日期       result

 2022-05-09 胜

 2022-05-09 胜

 2022-05-09 负

 2022-05-09 负

 2022-05-10 胜

 2022-05-10 负

 2022-05-10 负

如果要生成下列结果, 该如何写sql语句?

 

     日期      胜 负

 2005-22-09  2  2

 2005-22-10  1  2

Select t.riqi 日期, case result when ‘胜’ then @rownum:=@rownum +1 end胜, case result when ‘负 then @rownum2:=@rownum2 +1 end负 from table t ,(select @rownum:=0) c, (select @rownum2:=0) b group by t.riqi;

3. 表形式如下： (7)

Year      Salary

2018       1000

2019       2000

2020       3000

2021       4000

想得到如下形式的查询结果

Year      Salary

2018      1000

2019      3000

2020      6000

2021      10000

Select  sum(t2.year) from table t1,table t2 on t1.year >=t2.year group by t1.year

1. 怎么把表中的数据(10分)

year	month	amount
2020	1	1.1
2020	2	1.2
2020	3	1.3
2020	4	1.1
2021	1	1.2
2021	2	1.3
2021	3	1.5
2021	4	1.2

查询成如下的结果

year	m1	m2	m3	m4
2020	1.1	1.2	1.3	1.1
2021	1.2	1.3	1.5	1.2

请编写一条SQL语句实现上图的效果

Select t.year,sum(case month when 1 then amount else 0 end) m1,

sum(case month when 2 then amount else 0 end) m2,

sum(case month when 3 then amount else 0 end) m3,

sum(case month when 4 then amount else 0 end) m4

from table t group by t.year