mysql

练习2

练习2
-- any_value() 是一个在 SQL 中常用的函数，特别是在 PostgreSQL中。
-- 这个函数返回其参数中的任意一个值。它通常用于那些你只关心至少有一个值返回，而不关心具体是哪个值的场景。
mysql的30个经典案例（多表查询）
首先先来添加俩表：deptA,empA表
create table `empA`(
    `id` varchar(10),
    `name` VARCHAR(25),
    `age` VARCHAR(10),
    `worktime` VARCHAR(10),
    `dept2` varchar(10),
    `incoming` int(10)
)

create table `deptA`(
`dept1` VARCHAR(10),
`dept_name` VARCHAR(20)
)
插入数据
INSERT INTO deptA (dept1,dept_name) VALUES (101,'caiwu'),
                                                                                     (102,'yunwei'),
                                                                                     (103,'xiaoshou'),
                                                                                     (104,'jidian'),
                                                                                     (105,'zhubo'),
                                                                                     (106,'HR');

 insert into empA (id,name,age,worktime,incoming,dept2) VALUES (1500,'wangyi',25,'2022/2/1',8900,101),
                                                                                                                            (1501,'hufei',27,'2022/3/1',7500,102),
                                                                                                                            (1502,'heisen',30,'2022/3/20',8900,102),
                                                                                                                            (1503,'huahudie',25,'2022/8/9',9000,102),
                                                                                                                            (1504,'baimu',25,'2022/01/01',4000,103),
                                                                                                                            (1505,'leilei',27,'2016/05/09',27800,106),
                                                                                                                            (1506,'liuxiaoyi',28,'2017/05/09',16000,105),
                                                                                                                            (1507,'haiwu',27,'2018/09/06',6300,104);

1.列出每个部门里面有那些员工及部门名称;
-- 注意left join连接的条件用on
SELECT name,dept_name FROM empa
left join depta
on empa.dept2 = depta.dept1

2.运维部门的收入总和；
SELECT dept_name,SUM(incoming) FROM empa,depta WHERE dept_name = 'yunwei' and depta.dept1 = empa.dept2

3.HR部入职员工的员工号
SELECT id FROM empa,depta WHERE depta.dept1 = empa.dept2 and dept_name = 'HR'
4.财务部门收入超过5000元的员工姓名
SELECT name FROM empa,depta WHERE empa.incoming>5000 and empa.dept2 = depta.dept1 and depta.dept_name = 'caiwu' 
5.找出销售部收入最低的员工的入职时间；
SELECT worktime FROM empa where incoming = (
                                                                    SELECT incoming FROM empa,depta 
                                                                    where empa.dept2=depta.dept1 and depta.dept_name = 'xiaoshou'
                                                                    ORDER BY empa.incoming asc limit 1)
-- select min(incoming),empa.worktime from depta 
-- RIGHT JOIN empa on depta.dept1 = empa.dept2 
-- where dept_name='xiaoshou' GROUP BY worktime
6.找出年龄小于平均年龄的员工的姓名，ID和部门名称
SELECT id,name,dept_name FROM empa
left join depta
on empa.dept2 = depta.dept1
WHERE age<(SELECT avg(age) FROM empa);

-- SELECT id,name,dept_name FROM empa
-- left join depta
-- on empa.dept2 = depta.dept1
-- WHERE age<avg(age);
-- 这个子查询试图在WHERE子句中直接使用聚合函数avg()，这是不被允许的。在SQL中，聚合函数（如avg(), sum(), count()等）不能在WHERE子句中直接使用，因为WHERE子句在聚合操作之前执行。这就是为什么第二个查询会报错。
7.列出每个部门收入总和高于10000的部门名称
-- HAVING sum(incoming)>10000: 这是一个过滤条件，它表示你只想选择那些其incoming列的总和大于10000的部门。
SELECT dept_name from empa 
LEFT JOIN depta 
on empa.dept2=depta.dept1 
GROUP BY dept_name 
HAVING sum(incoming)>10000
8.查出财务部门工资少于10000元的员工姓名
SELECT name FROM empa
left join depta
on empa.dept2 = depta.dept1
WHERE 
        depta.dept_name = 'caiwu'
    and 
        empa.incoming<10000
9.求财务部门最低工资的员工姓名；
SELECT name,incoming FROM empa
left JOIN depta
on empa.dept2 = depta.dept1
WHERE depta.dept_name = 'caiwu'
ORDER BY incoming ASC
LIMIT 1
10.找出运维部门中年纪最大的员工的姓名(如果有多人要同时查出)
SELECT name FROM empa 
left JOIN depta 
ON empa.dept2 = depta.dept1
WHERE 
age =
(SELECT age FROM empa 
left JOIN depta 
ON empa.dept2 = depta.dept1
WHERE depta.dept_name = 'yunwei'
ORDER BY age DESC
LIMIT 1 )
and depta.dept_name='yunwei'

-- SELECT empa.name from empa 
-- LEFT JOIN depta 
-- on empa.dept2 = depta.dept1 
-- where age=
--                     (SELECT max(age) from empa
--                      LEFT JOIN depta 
--                      on empa.dept2 = depta.dept1 
--                      WHERE depta.dept_name ='yunwei') 
--                      and depta.dept_name='yunwei'
11.求收入最低的员工姓名及所属部门名称：
SELECT name,dept_name FROM empa
LEFT JOIN depta
on empa.dept2 = depta.dept1
WHERE incoming = (SELECT min(incoming) FROM empa)
12.求雷蕾的收入及部门名称
SELECT incoming,dept_name FROM empa
Left join depta
on empa.dept2 = depta.dept1
where empa.name = 'leilei'
13.求员工收入小于8000元的员工部门编号名字及其部门名称；
SELECT dept1,name,dept_name FROM empa
left join depta
on empa.dept2= depta.dept1
WHERE incoming < 8000;
14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；(*)

SELECT any_value(name),max(incoming),any_value(dept_name)
 from depta
 LEFT JOIN empa on depta.dept1 = empa.dept2 
 GROUP BY dept_name
15.求出运维部门收益最高的俩位员工的姓名，工号，收益
SELECT name,id,incoming From empa
LEFT JOIN depta
on empa.dept2 = depta.dept1
WHERE depta.dept_name = 'yunwei'
ORDER BY incoming DESC
LIMIT 2
16.查询运维部低于平均收入的员工号与员工姓名：
SELECT id,name FROM empa
LEFT JOIN depta
on empa.dept2 = depta.dept1
WHERE dept_name = 'yunwei'
and incoming < 
        (SELECT avg(incoming) 
        from empa
        LEFT JOIN depta
        on empa.dept2 = depta.dept1)
17.列出部门员工数大于1个的部门名称；
SELECT DISTINCT(dept_name) 
    from depta    
    LEFT JOIN empa on depta.dept1=empa.dept2 
    where dept1 in
                                (SELECT dept2 from depta 
                                LEFT JOIN empa on depta.dept1 = empa.dept2 
                                GROUP BY dept2 
                                    HAVING(
                                    COUNT(dept2)>1
                                    )
                                )
18.列出部门员工收入不超过10000，且大于5000的员工年纪及部门编号；
SELECT name,age,dept1,incoming FROM empa
left join depta
on empa.dept2 = depta.dept1
WHERE incoming BETWEEN 5000 and 10000
19.求入职于2022年的员工所属部门名称；
SELECT name,dept_name,worktime from depta
LEFT JOIN empa
on empa.dept2 = depta.dept1
where worktime=2022
20.查找haiwu所在的部门名称；
SELECT dept_name FROM empa
left join depta 
on empa.dept2 = depta.dept1
WHERE empa.`name` = 'haiwu';
21.列出每一个部门中年纪最小的员工姓名，部门名称；
SELECT name,dept_name from dept LEFT JOIN emp on dept.dept1 = emp.dept2 where age in(SELECT min(age) from dept LEFT JOIN emp on dept.dept1 = emp.dept2 GROUP BY dept_name)
-- SELECT dept_name,name FROM empa
-- LEFT JOIN depta
-- on empa.dept2 = depta.dept1
-- WHERE age in (SELECT min(age) FROM empa GROUP BY dept2)
22.列出每一个部门的员工总收入及部门名称；
SELECT sum(incoming),any_value(dept_name)
FROM empa
LEFT JOIN depta
on empa.dept2 = depta.dept1
GROUP BY dept1
23.列出部门员工收入大于6000的员工号，部门名称；
SELECT id,dept_name,name,incoming from empa
LEFT JOIN depta
on empa.dept2 = depta.dept1
WHERE  incoming>6000
24.找出哪个部门还没有员工入职；
SELECT any_value(dept_name) from depta
left JOIN empa
on empa.dept2 = depta.dept1
GROUP BY dept1
HAVING count(id) = 0
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
SELECT * FROM empa
INNER JOIN depta
on empa.dept2 = depta.dept1
ORDER BY dept2,worktime
26.找出运维部门收入大于7500,显示他们的ID和姓名和年纪；
SELECT id,name,age FROM empa
left join depta
on empa.dept2 = depta.dept1
where dept_name = 'yunwei' and incoming>7500
27.找出入职于2022年以前的员工，且显示他们的姓名和入职时间，按照先后循序进行排序展示
SELECT name,worktime FROM empa
where worktime<2022/01/01
ORDER BY worktime DESC
28.找出入职于2022年1月以后的员工，并显示年龄，姓名，收入
SELECT name,age,incoming FROM empa
where worktime>=2022/01/01
29.查找收入大于8000元以上的人的姓名和收入
SELECT name,incoming from empa
WHERE incoming >8000
30.统计各个部门的收入总和并按照从大到小方式排序进行显示
SELECT any_value(dept_name),sum(incoming) FROM empa 
LEFT JOIN depta
on empa.dept2 = depta.dept1
GROUP BY depta.dept1
ORDER BY sum(incoming) desc