多测师课堂009_mysql之多表（002练习题1）高级讲师肖sir

已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）

1：dept表中有4条记录：

     部门号(dept1)  部门名称(dept_name )
     101     财务            
     102     销售            
     103     IT技术         
     104     行政            
2：emp表中有6条记录：

      员工号 员工姓名 年龄 入职时间 收入 部门号对应字段名称为： (sid name age worktime_start incoming dept2)
      1789    张三 35 1980/1/1 4000 101
      1674    李四 32 1983/4/1 3500 101
      1776    王五 24 1990/7/1 2000 101
      1568    赵六 57 1970/10/11 7500 102

      1564    荣七 64 1963/10/11 8500 102

      1879    牛八 55 1971/10/20 7300 103

 

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

方法一;
1) SELECT AVG(incoming) dept_name FROM emp RIGHT JOIN dept ON emp.dept2 = dept.dept1 GROUP BY dept_name;

 

方法二：

2)select s.dept_name, m.n from dept s left join (select avg(incoming)n ,dept2 from emp group by dept2 ) m on s.dept1=m.dept2;

2.财务部门的收入总和； 
SELECT dept_name ,SUM(incoming) FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 WHERE dept_name = "财务";

2)select sum(incoming) from  emp where   dept2=(select  dept1  from  dept  where  dept_name="财务");

3.It技术部入职员工的员工号   
SELECT dept_name ,sid  FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 WHERE dept_name = "IT技术";

2)select emp.sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name="技术 ";

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

select name from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name=”财务 ” and incoming>2000；

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

FROM

(SELECT * FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1  ORDER BY incoming)a

WHERE dept_name = "销售"

LIMIT 1;

select  s.woektime_start from  (select woektime_start,min(incoming) from (select * from emp INNER JOIN dept on dept.dept1=emp.dept2)q where q.dept_name="销售")s ;

方法三

select woektime_start from emp,dept where dept.dept1=emp.dept2 and
incoming in (select min(incoming)  from   emp  where dept2=(select  dept1 from  dept where  dept_name="销售")) and  dept_name ="销售" ;


6.找出年龄小于平均年龄的员工的姓名，ID和部门名称  
SELECT name,sid,dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 where age< (SELECT AVG(age) FROM emp);

2)select dept_name,sid,name from  emp INNER JOIN dept on dept1=emp.dept2 and age < (select avg(age) from emp join dept on dept1=emp.dept2);

7.列出每个部门收入总和高于9000的部门名称  
SELECT

dept_name

FROM

( SELECT sum( incoming ) a, dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 GROUP BY dept_name ) q

WHERE

a > 9000;

select dept.dept_name from dept  inner join ( select sum(incoming)s  , dept2  from  emp   group by  dept2  having  s>9000) c on dept.dept1=c.dept2 ;

方法三：

select dept_name from emp left join dept on emp.dept2=dept.dept1 group by dept_name having sum(incoming)>9000;


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

FROM

( SELECT name,dept_name,incoming FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 ) a

WHERE

dept_name = "财务"

AND incoming < 3800;

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

FROM

( SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 WHERE dept_name = "财务" ORDER BY incoming) a

LIMIT 1;

 

 

select name  from dept inner join emp on dept.dept1=emp.dept2 where dept2=(select dept1 from dept where dept_name=”财务”and incoming=(select min(incoming) from emp ,dept where emp.dept2=dept.dept1 and  dept_name="财务");

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

( SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 WHERE dept_name = "销售" ORDER BY age DESC) a

LIMIT 1;

select name from emp join dept on dept1=emp.dept2 where dept_name="销售" and age=(   select max(age) from emp join dept on dept1=emp.dept2 where dept_name="销售")


11.求收入最低的员工姓名及所属部门名称：
SELECT name ,dept_name FROM(SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2)a WHERE incoming =( SELECT MIN(incoming) FROM emp);

//方法二

SELECT NAME,

dept_name

FROM

( SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2  ORDER BY incoming ) a

LIMIT 1;


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

条件： name =“李四”

结果：incoming   ，dept_name

方法一：

SELECT incoming,dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 WHERE NAME = "李四";

方法二：

select dept_name,incoming from dept left join emp on dept1=dept2 where name="李四" ；

方法三：

select s.incoming ,dept_name from dept,(select incoming, dept2 from emp where name='李四')s where dept.dept1=s.dept2 ;

方法四：

select s.dept_name , s.incoming from (
select * from dept left join emp on dept1=dept2 )s where s.name="李四" ;

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

条件：incoming<4000

结果：dept1, dept_name

方法一：SELECT dept1,dept_name    FROM   ( SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2  ) a  WHERE incoming < 4000;

方法二：select dept_name ,dept1  from emp ,dept where  dept1=emp.dept2 and incoming < 4000;

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

第一种方法：SELECT name ,  dept_name,incoming   FROM

( SELECT * FROM emp RIGHT JOIN dept ON dept.dept1 = emp.dept2  ORDER BY incoming DESC ) a

GROUP BY dept_name   ORDER BY incoming DESC;

第二种方法：

diyuSELECT name,incoming,dept_name from
(SELECT name,dept_name,incoming,dept2 from emp right JOIN dept on dept.dept1=emp.dept2 ORDER BY incoming DESC) b
 GROUP BY
 dept2
 ORDER BY
 incoming desc;

15.求出财务部门收益最高的俩位员工的姓名，工号，收益

条件： dept_name="财务" ， max（incoming）或排序方法取值

结果：name，sid ，incoming

方法一：先临时表里面合表在排序（降序），在从临时表中取值两行

理解：可以认为在一个单表中查询数据（单表结构（单表中查询的是多表，所以要先合表））

SELECT name ,  sid,incoming   FROM     ( SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 WHERE dept_name = "财务" ORDER BY incoming DESC ) a     LIMIT 2;

方法二:先合表，在排序（降序），在取值两行
select name,sid,incoming from dept inner join emp on dept1=dept2  where  dept_name='财务' order by incoming desc limit 2;

 方法三：先子查询把财务部门的编号求出；在用emp表dept2关联子查询中dept1，求出我们结果

子查询

select name,sid,incoming from  emp   where  dept2=(select dept1 from dept where dept_name='财务') order by incoming desc limit 2;

16.查询财务部低于平均收入的员工号与员工姓名：(平均工资指全体平均工资)

条件：dept_name="财务部"  ，   avg（*） 或avg（incomg）或avg（1）   dept

结果：sid ，name           emp

 方法一：
SELECT a.name,a.sid       FROM     (SELECT name ,sid,incoming FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 WHERE dept_name = "财务")a    WHERE incoming <(SELECT AVG(incoming) FROM emp);

方法二：

select name,sid from dept left join emp on dept1=dept2 where incoming<(select avg(incoming)from dept left join emp on dept1=dept2) and dept_name="财务";

方法三：

select sid,name from dept,emp where dept1=dept2 and incoming<(select avg(incoming) from emp) and dept_name='财务'

方法四：

select sid,name from  emp  where incoming<(select avg(incoming)from emp) and dept2=(select dept1 from dept where dept_name='财务');

17.列出部门员工数大于1个的部门名称； 

方法一：
SELECT dept_name ,n    FROM   (SELECT COUNT(dept_name) as n,dept_name FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 GROUP BY dept_name )a     WHERE n >1;

方法二： SELECT     dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2    GROUP BY    dept_name   HAVING      COUNT(dept2)>1;

方法三：select dept_name from dept where dept1 in (select dept2 from  emp group by dept2 having count(dept2)>1);

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

条件： dept_nem         incoming<7500    incoming>3000  

结果：age ,dept2

方法一：
SELECT age,dept1   FROM    (SELECT * FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 )a

WHERE incoming <=7500 and incoming >3000;

方法二：

SELECT   age,dept2 from emp   WHERE     incoming>'3000' and incoming<='7500'

方法三：

select age,dept2 from emp inner join dept on dept.dept1=emp.dept2 where incoming between 3000  and 7500;

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

条件：  20世纪70年代     197%

结果：dept_name

方法一：

SELECT dept_name   FROM    (SELECT * FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 )a

WHERE woektime_start LIKE "197%";

方法二：

SELECT dept_name FROM emp right JOIN dept on dept.dept1=emp.dept2 where woektime_start BETWEEN 1970 and 1979;

方法三:

SELECT dept_name FROM emp right JOIN dept on dept.dept1=emp.dept2 where woektime_start > 1969 and woektime_star <1980;

方法四：

20.查找张三所在的部门名称；

条件： name =“张三”

结果：dept_name

方法一：SELECT dept_name   FRO   (SELECT * FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 )a   WHERE name = "张三";

方法二：select dept_name from dept left join emp on dept.dept1=emp.dept2 where name=”张三

方法三：select  dept_name  from  dept   where  dept1=(select   dept2  from  emp   where   name= "张三")

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

条件：每个部门  group     max（age）

结果：name  ，dept_name

方法一：
SELECT dept_name,name

FROM

(SELECT * FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 ORDER BY age desc )a

GROUP BY dept_name;

SELECT

MAX(age),dept_name,`name` from emp RIGHT JOIN dept on dept.dept1=emp.dept2

GROUP BY

dept_name;

方法二：

select   dept_name ,name   from (select dept_name,name ,age from dept left join emp on dept1=dept2 where age in (select max(age) from dept left join emp on dept1=dept2 group by dept_name)  order by  age DESC)s group by  dept_name  ;

方法三：错误方法

select   dept_name ,name from (select dept_name,name ,age from dept left join emp on dept1=dept2 where age in (select max(age) from dept left join emp on dept1=dept2 group by dept_name)  )s group by  dept_name   having   max(age) ;

 

方法四：

select  t.dept_name ,t.name from ( select   dept_name ,name  ,max(age) from (select dept_name,name ,age from dept left join emp on dept1=dept2 where age in (select max(age)  c from dept left join emp on dept1=dept2 group by dept_name)  )s group by  dept_name  )t ;

 

在解答：

22.列出每一个部门的员工总收入及部门名称；

条件：group  by       sum（incoming）

结果：sum（incoming） ，dept_name

方法一：

SELECT SUM(incoming) as a,dept_name FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 GROUP BY dept_name;

方法二：select  s.a,dept.dept_name  from   dept left join (select sum(incoming) a,dept2  from  emp  group  by  dept2

)s on  dept.dept1=s.dept2 ;

23.列出部门员工收入大于7000的员工号，部门名称；

条件： incoming> 7000

结果：sid    ,dept_name

方法一：

SELECT a.sid,a.dept_name    FROM   (SELECT * FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 WHERE incoming >7000 )a;

方法二：

select sid,dept_name FROM(SELECT * fROM emp INNER JOIN dept ON emp.dept2 = dept.dept1)a where incoming>7000

方法三：

SELECT sid,dept_name     FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 WHERE incoming >7000 ；

24.找出哪个部门还没有员工入职；

条件：dept_name  name

 

 

方法一：

SELECT dept_name   FROM    (SELECT * FROM emp RIGHT JOIN dept ON emp.dept2 = dept.dept1  )a

WHERE  sid IS NULL;

方法二：（所有的另一个表为空）

select dept_name from dept left join emp on dept1=dept2 where sid is null and name is null and age is null and woektime_start is null and incoming is null;

方法三：

SELECT * FROM dept   left JOIN emp ON emp.dept2 = dept.dept1  where   name is null；
方法四：

select dept_name from dept where dept1 not in (select dept2 from emp);

方法五：

select dept_name from dept where dept1 !=all (select dept2 from emp);

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；

方法一：

SELECT    *    FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1     ORDER BY     dept1 DESC,

woektime_start ASC;

方法二：

select  *  from emp  order by  dept2 desc ,woektime_start asc ;

方法三：错误方法
select*from (select*from dept left join emp on dept1=dept2 order by dept1     desc)a order by woektime_start asc;

26.求出财务部门工资最高员工的姓名和员工号

第一方法：（如果重复最高，只能取一个）
SELECT name,sid  FROM    (SELECT * FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1  WHERE dept_name = "财务" ORDER BY incoming DESC)a   LIMIT 0,1

第二方法：（更精确）

select name ,sid from emp join dept on dept1=emp.dept2 where dept_name="财务" and incoming=(   select max(incoming) from emp join dept on dept1=emp.dept2 where dept_name="财务")

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名

第一方法：

SELECT name,dept_name FROM(SELECT * FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1   ORDER BY age DESC)a   WHERE incoming>=7500 AND incoming <=8500  LIMIT 1;

第二种方法：

select  dept_name ,name from   emp,dept WHERE  dept.dept1=emp.dept2  AND  age in (select  MAX(age) from  emp  where  incoming  between  7500  and  8500  )  and  incoming between  7500  and  8500 ;

 

错误写法一：

select dept_name,name from dept left join emp on dept1=dept2 where incoming between 7500 and 8500 and age in(select max(age) from dept left join emp on dept1=dept2 group by dept_name);

 

 

 

 

 

归纳：

固定套路：

SELECT

需要展示的字段

FROM

（SELECT * FROM ）a包含全部字段的联合表，并进行分组以及排序将联合表

WHERE

条件字段;

 

 

#建表

CREATE table dept1(dept1 VARCHAR(6),dept_name VARCHAR(20));
 INSERT into dept VALUES ('101','财务');

-- INSERT into dept VALUES ('102','销售');

-- INSERT into dept VALUES ('103','IT技术');

-- INSERT into dept VALUES ('104','行政');

-- INSERT into  dept  VALUES  ('104','销售');

CREATE table emp (sid VARCHAR(20),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6));

 

insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');

 

insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');

 

insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');

 

insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');

 

insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');

 

insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');