day:12 mysql 作业纠正

一、建表语句

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

CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charset=utf8;

INSERT into dept VALUES ('101','财务');
INSERT into dept VALUES ('102','销售');
INSERT into dept VALUES ('103','IT技术');
INSERT into dept VALUES ('104','行政');

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

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');
insert into emp VALUES ('1880','老九',55,'1971/10/20',8000,'105');

drop table dept ;

drop table emp ;

select * from dept;

select * from emp ;

二、表格

三、题目

1.列出每个部门的平均收入及部门名称
语句：
select dept.dept_name,avg(emp.incoming) from dept LEFT join emp on dept.dept1=emp.dept2 group by dept.dept_name;

2.财务部门的收入总和
语句1：
select sum(incoming) from emp
where dept2=(select dept1 from dept where dept_name="财务");

语句2：
SELECT SUM(incoming) FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2
WHERE dept_name='财务';

3.It技术部入职员工的员工号
语句1“
SELECT emp.sid from emp INNER JOIN dept on dept.dept1=emp.dept2 where dept.dept_name="IT技术";

语句2：
SELECT sid FROM emp WHERE dept2=(
SELECT dept1 from dept WHERE dept_name='IT技术');

4.财务部门收入超过2000元的员工姓名
emp incoming>2000
dept 财务

语句1：
select name from emp where dept2=(select dept1 from dept where dept_name='财务')and incoming>2000;

语句2：
SELECT name FROM dept INNER JOIN emp ON dept.dept1=emp.dept2
WHERE dept_name='财务' AND incoming>2000;

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

方法1：
select wooktime_start from dept inner join emp on dept.dept1=emp.dept2
where (dept2,incoming) in (select dept2,min(incoming)
from emp group by dept2) and dept_name='销售';

方法2：
select emp.woektime_start from dept inner join emp on dept.dept1=emp.dept2 where dept.dept_name='销售' order by emp.incoming asc limit 1;

方法3：
SELECT
woektime_start as 入职时间
FROM emp e
JoIN dept d ON e.dept2 = d.dept1
where d.dept_name='销售'
AND e.incoming = (
SELECT MIN(incoming)
FROM emp e2
JOIN dept d2 ON e2.dept2 = d2.dept1
WHERE d2.dept_name = '销售'
);

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

语句2：

7.列出每个部门收入总和高于9000的部门名称
语句 1：
SELECT dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name HAVING sum(incoming)>9000;

8.查出财务部门工资少于3800元的员工姓名
财务 dept incoming emp
语句1：
SELECT
e.name as 员工姓名
from emp e
JOIN
dept d on e.dept2 = d.dept1
where
d.dept_name='财务' and e.incoming<3800;

语句2：
select name from emp where dept2=(select dept1 from dept where dept_name='财务') and incoming<3800;

9.求财务部门最低工资的员工姓名；
语句1：
SELECT
e.name as 员工姓名
from emp e
JOIN
dept d on e.dept2 = d.dept1
where d.dept_name='财务'
and e.incoming=(SELECT min(incoming)
from emp e2
join dept d2 on e2.dept2=d2.dept1
where d2.dept_name='财务');

语句2：
SELECT name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='财务' order by incoming asc LIMIT 0,1;

语句3：
select name from emp where dept2=(select dept1 from dept where dept_name='财务') and incoming=(select min(incoming) from dept left join emp on dept.dept1=emp.dept2 where dept_name='财务');

10.找出销售部门中年纪最大的员工的姓名
结果：name
条件：dept_name="销售"
max（age）

语句1：（有缺陷）当出现重复的数据就显示一个
select emp.name,emp.age from emp inner JOIN dept on emp.dept2 = dept.dept1 where dept.dept_name="销售" ORDER BY emp.age desc LIMIT 1

语句2：
SELECT
e.name as 员工姓名
from emp e
JOIN
dept d on e.dept2 = d.dept1
where
d.dept_name='销售' and e.age=(SELECT max(age)
from emp e2
JOIN dept d2 on e2.dept2=d2.dept1
where d2.dept_name='销售');

语句3：
select name from dept inner join emp on dept.dept1=emp.dept2
where (dept2,age) in (select dept2,max(age) from emp group by dept2)
and dept_name='销售';

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

语句一：
SELECT
e.name as 员工姓名，
d.dept_name as 部门名称
FROM emp e
join dept d on e.dept2 = d.dept1
WHERE
e.incoming=(
SELECT min(incoming) from emp
);

语句2：
SELECT name,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 order by incoming asc LIMIT 0,1 ;

12.求李四的收入及部门名称
语句：
SELECT incoming,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE name="李四";

13.求员工收入小于4000元的员工部门编号及其部门名称
语句1：
select DISTINCT dept1,dept_name from dept inner join (select * from emp where incoming<4000) a on a.dept2=dept.dept1;

语句2：
select a.sid,a.dept_name FROM(
select * from dept right join emp on dept.dept1=emp.dept2
)a where incoming<4000;

语句3：
select dept.dept1,dept.dept_name from dept inner join emp on dept.dept1=emp.dept2 where emp.incoming<4000;

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
结果：name，dept_name,incoming
条件：max(incoming),order by incoming desc

语句1：
第一步：先将一个表的每个部门的最高工资和部门号显示
select dept2,max(incoming) from emp group by dept2;

图片
第二步：到整个表中匹配
select name,dept_name,incoming from dept left join emp on dept.dept1=emp.dept2
where (dept2,incoming) in (select dept2,max(incoming)as s from emp group by dept2)

第三部：求出的结果进行降序
select name,dept_name,incoming from dept left join emp on dept.dept1=emp.dept2
where (dept2,incoming) in (select dept2,max(incoming)as s from emp group by dept2) ORDER BY incoming desc

图片

语句2：

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

语句1：
SELECT
e.name as 员工姓名,
e.sid as 员工工号,
e.incoming as 员工收益
from emp e
join dept d on e.dept2 = d.dept1
where d.dept_name = '财务'
ORDER BY e.incoming DESC
LIMIT 2;

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

16.查询财务部低于平均收入的员工号与员工姓名（低于全部平均工资）
语句1:
SELECT sid,name from emp join dept on emp.dept2=dept.dept1 where dept.dept_name="财务" and incoming < (SELECT AVG(emp.incoming) from emp);

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

17.列出部门员工数大于1个的部门名称；
语句1：
select s.dept_name from (SELECT dept_name,count(sid) c from dept LEFT JOIN emp ON
dept.dept1 = emp.dept2 GROUP BY dept_name HAVING c>1)s

语句2：
SELECT dept_name from emp join dept on emp.dept2=dept.dept1 group by dept.dept_name HAVING count(sid)>1;

语句3：

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
语句1：
SELECT age,dept1 FROM dept INNER JOIN emp ON dept.dept1=emp.dept2
WHERE incoming<=7500 and incoming>3000;

19.求入职于20世纪70年代的员工所属部门名称；
语句：
SELECT name,dept_name FROM dept INNER JOIN emp ON dept.dept1=emp.dept2
WHERE woektime_start LIKE "197%";

语句2：
SELECT
e.name AS 员工姓名,

d.dept_name AS 部门名称
FROM
emp e
JOIN
dept d ON e.dept2 = d.dept1
WHERE
e.woektime_start BETWEEN '1970/1/1' AND '1979/12/31';

20.查找张三所在的部门名称；
语句1：
SELECT dept_name FROM dept INNER JOIN emp ON dept.dept1=emp.dept2
WHERE name='张三';

语句2：

SELECT dept_name FROM dept where dept1=(
SELECT dept2 from emp WHERE name='张三');

21.列出每一个部门中年纪最大的员工姓名，部门名称；
结果：name，dept_name
条件：max（age） group by

语句：
select name,dept_name from dept inner join emp on dept.dept1=emp.dept2
where (dept2,age) in (select dept2,max(age) from emp group by dept2);

22.列出每一个部门的员工总收入及部门名称；
语句：
select dept_name,sum(incoming) from dept right join emp on dept.dept1=emp.dept2 group by dept_name;

23.列出部门员工收入大于7000的员工号，部门名称；
语句：
select dept.dept_name,emp.sid,emp.incoming from dept inner join emp on dept.dept1=emp.dept2 where emp.incoming>7000;

24.找出哪个部门还没有员工入职；（左独有）
语句：
sELECT dept_name FROM dept left JOIN emp ON dept.dept1=emp.dept2
WHERE name is NULL;

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；（二次排序）
语句：
SELECT * FROM dept inner JOIN emp ON dept.dept1=emp.dept2
ORDER BY dept1 DESC,woektime_start;

26.求出财务部门工资最高员工的姓名和员工号
语句1：
SELECT name,sid,incoming from emp join dept on emp.dept2=dept.dept1 where dept.dept_name="财务" order BY incoming DESC LIMIT 1;

语句2：
SELECT name,sid from dept LEFT JOIN emp ON
dept.dept1 = emp.dept2 WHERE(dept_name,incoming)
=(SELECT dept_name,MAX(incoming) incoming from dept LEFT JOIN emp ON
dept.dept1 = emp.dept2 GROUP BY dept_name HAVING
dept_name='财务');

语句3：

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
语句1：
select name,dept_name from dept right join emp on dept.dept1=emp.dept2
where incoming between 7500 and 8500 order by age desc limit 1;

语句2：
select name,dept_name from dept inner join emp on dept.dept1=emp.dept2
where age=(select max(age) from emp where
incoming between 7500 and 8500) and incoming between 7500 and 8500 ;

select name,dept_name from dept inner join emp on dept.dept1=emp.dept2
where age=(select max(age) from emp where
incoming between 7500 and 8500) and incoming between 7500 and 8500 ;
语句3：
SELECT * FROM dept inner JOIN emp ON dept.dept1=emp.dept2
WHERE incoming BETWEEN 7500 and 8500 and (dept2,age) in (SELECT dept2,max(age) FROM emp where incoming BETWEEN 7500 and 8500 GROUP BY dept2 )

总结：

1、函数前只有分组的字段有效，其余为默认值。
2、问题中带函数时应先单独计算函数的内容
3、对于包含函数问题的三个解题思路
A、先求出带有条件的函数值，再把函数值结果新创表，在和表中匹配信息；用where（） in（）
B、先求出函数值，把函数值当作条件之一，用where a=函数值
C、最大值，最小值可以用升降序的方法，取前几行，只适用于结果只有一条数值时；group by（） limite （）