mysql_多表练习
一、前提
- 建表
-
部门表：dept （部门号，部门名称）;
CREATE table dept(
dept1 VARCHAR(6),
dept_name VARCHAR(20)
) default charset=utf8; -
员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
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 dept VALUES ('101','财务');
INSERT into dept VALUES ('102','销售');
INSERT into dept VALUES ('103','IT技术');
INSERT into dept VALUES ('104','行政'); - 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 ('1789','十三',24,'1990/8/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 ('1777','十一',24,'1990/7/1',2000,'102');
insert into emp VALUES ('1778','十二',24,'1990/7/1',1000,'102');
insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
insert into emp VALUES ('1800','牛十八',80,'1971/10/20',7300,'103');
insert into emp VALUES ('1880','老九',55,'1971/10/20',7500,'105');
insert into emp VALUES ('1900','老十',64,'1990/8/1',2000,'106');
二、分析表格
- dept表：部门号，部门名称，4条数据
- emp表：员工号，员工姓名，年龄，入职时间，收入，部门号，12条数据
三、做题
- 列出每个部门的平均收入及部门名称;
- 结果：avg(incoming)，dept_name
- 条件：group by dept_name
- 语句：
select dept_name,avg(incoming) from dept left join emp on dept.dept1=emp.dept2 group by dept_name
- 财务部门的收入总和；
- 结果：sum(incoming)
- 条件：dept_name="财务"
- 语句：
select sum(incoming) from dept inner join emp on dept.dept1=emp.dept2 where dept_name="财务"
- It技术部入职员工的员工号
- 结果：sid
- 条件：dept_name="IT技术"
- 语句：
select sid from emp right join dept on emp.dept2=dept.dept1 where dept_name="IT技术" - 子查询：
select sid from emp where dept2=(select dept1 from dept where dept_name="IT技术")
- 财务部门收入超过2000元的员工姓名
- 结果：name
- 条件：dept_name="财务"，incoming>2000
- 语句：
select name from emp inner join dept on emp.dept2=dept.dept1 where dept_name="财务" and incoming>2000
- 找出销售部收入最低的员工的入职时间
- 结果：woektime_start
- 条件：dept_name="销售"，incoming最低
- 语句：
select woektime_start from emp inner join dept on emp.dept2=dept.dept1 where dept_name="销售" and incoming=(select min(incoming) from emp inner join dept on emp.dept2=dept.dept1 where dept_name="销售")
- 找出年龄小于平均年龄的员工的姓名，ID和部门名称
- 结果：name，sid，dept_name
- 条件：age<avg(age)
- 语句：
select name,sid,dept_name from emp inner join dept on emp.dept2=dept.dept1 where age<(select avg(age) from emp)
- 列出每个部门收入总和高于9000的部门名称
- 结果：dept_name
- 条件：sum(incoming)>9000,group by dept_name
- 语句：
select t.dept_name from (select dept_name,sum(incoming) s from emp inner join dept on emp.dept2=dept.dept1 group by dept_name having s>9000)t - 语句2：
select dept_name from emp inner join dept on emp.dept2=dept.dept1 group by dept_name having sum(incoming)>9000
- 查出财务部门工资少于3800元的员工姓名
- 结果：name
- 条件：dept_name="财务"，incoming<3000
- 语句：
select name from emp inner join dept on emp.dept2=dept.dept1 where dept_name="财务" and incoming<3800
- 求财务部门最低工资的员工姓名
- 结果：name
- 条件：dept_name="财务"，incoming最低
- 语句：
select name from emp inner join dept on emp.dept2=dept.dept1 where dept_name="财务" and incoming=(select min(incoming) from emp inner join dept on emp.dept2=dept.dept1 where dept_name="财务")
- 找出销售部门中年纪最大的员工的姓名
- 结果：name
- 条件：dept_name="销售"，age最大
- 语句：
select name from emp inner join dept on emp.dept2=dept.dept1 where dept_name="销售" and age=(select max(age) from emp inner join dept on emp.dept2=dept.dept1 where dept_name="销售") - 语句2：
select name from dept join emp on dept.dept1=emp.dept2 where (dept_name,age) in ( select dept_name,max(age) as max_age from dept join emp on dept.dept1=emp.dept2 where dept_name='销售' );
- 求收入最低的员工姓名及所属部门名称
- 结果：name，dept_name
- 条件：incoming最低
- 语句：
select name,dept_name from emp inner join dept on emp.dept2=dept.dept1 where incoming=(select min(incoming) from emp)
- 求李四的收入及部门名称
- 结果：incoming,dept_name
- 条件：name="李四"
- 语句：
select incoming,dept_name from emp inner join dept on emp.dept2=dept.dept1 where name="李四"
- 求员工收入小于4000元的员工部门编号及其部门名称
- 结果：dept1,dept_name
- 条件：incoming<4000
- 语句：
select dept1,dept_name from emp inner join dept on emp.dept2=dept.dept1 where incoming<4000
- 列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序
- 结果：name,dept_name,incoming desc
- 条件：每个部门收入最高的员工
- 语句：
select name,dept_name,incoming from emp inner join dept on emp.dept2=dept.dept1 where (dept_name,incoming) in (select dept_name,max(incoming) from emp inner join dept on emp.dept2=dept.dept1 group by dept_name) order by incoming desc
- 求出财务部门收益最高的俩位员工的姓名，工号，收益
- 结果：name,sid,incoming
- 条件：dept_name="财务",incoming收益最高的两位
- 语句：
select name,sid,incoming from emp inner join dept on emp.dept2=dept.dept1 where dept_name="财务" order by incoming desc limit 0,2
- 查询财务部低于平均收入的员工号与员工姓名
- 结果：sid,name
- 条件：dept_name="财务",incoming低于avg(incoming)
- 语句：
select sid,name from emp inner join dept on emp.dept2=dept.dept1 where dept_name="财务" and incoming<(select avg(incoming) a from emp)
- 列出部门员工数大于1个的部门名称
- 结果：dept_name
- 条件：部门员工数>1
- 语句：
select dept_name from ( select * from emp left join dept on emp.dept2=dept.dept1 union select * from emp right join dept on emp.dept2=dept.dept1 where name is null ) ls group by dept2 having count(sid)>1【部门名称：这块需要显示部门名称】 - 语句2：
select dept_name from dept left join emp on dept.dept1=emp.dept2 GROUP BY dept_name having count(*)>1
- 列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号
- 结果：age,dept2
- 条件：incoming<=7500,incoming>3000
- 语句：
select age,dept2 from emp where incoming<=7500 and incoming>3000
- 求入职于20世纪70年代的员工所属部门名称
- 结果：dept_name
- 条件：woektime_start>=1970,woektime_start<1980
- 语句：
select dept_name from emp left join dept on emp.dept2=dept.dept1 where woektime_start>=1970 and woektime_start<1980 - 语句2：
select dept_name from dept inner join emp on dept.dept1=emp.dept2 where woektime_start like "197%" - 语句2：
select dept_name from dept where dept1 in (select distinct dept2 from emp where woektime_start between '1970/1/1' and '1979/12/31');
- 查找张三所在的部门名称
- 结果：dept_name
- 条件：name="张三"
- 语句：
select dept_name from emp inner join dept on emp.dept2=dept.dept1 where name="张三" - 语句2：
select dept_name from deptwhere dept1 = ( select dept2 from emp where name='张三');
- 列出每一个部门中年纪最大的员工姓名，部门名称
- 结果：name,dept_name
- 条件：每个部门age最大
- 语句：
select name,dept_name from emp left join dept on emp.dept2=dept.dept1 where (dept2,age) in (select dept2,max(age) from emp left join dept on emp.dept2=dept.dept1 group by dept2) - 语句2：
select name,dept_name from dept right join emp on dept.dept1=emp.dept2 where (dept2,age) = any( select dept2,max(age) from emp group by dept2);
- 列出每一个部门的员工总收入及部门名称
- 结果：incoming，dept_name
- 条件：每个部门
- 语句：
select dept_name,sum(incoming) from emp left join dept on emp.dept2=dept.dept1 group by dept_name
- 列出部门员工收入大于7000的员工号，部门名称；
- 结果：sid，dept_name
- 条件：incoming>7000
- 语句：
select sid,dept_name from emp left join dept on emp.dept2=dept.dept1 where incoming>7000
- 找出哪个部门还没有员工入职【左独有】
- 结果：dept_name
- 条件：count(sid)=0
- 语句：
select dept_name,count(sid) s from ( select * from emp left join dept on emp.dept2=dept.dept1 union select * from emp right join dept on emp.dept2=dept.dept1 where name is null ) ls group by dept2 having s=0 - 语句：
select dept_name from dept left join emp on dept.dept1=emp.dept2 where name is NULL;
- 先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
- 结果：*
- 条件：dept1 desc,woektime_start asc
- 语句：
select * from emp left join dept on emp.dept2=dept.dept1 order by dept2 desc,woektime_start asc
- 求出财务部门工资最高员工的姓名和员工号
- 结果：name,sid
- 条件：dept_name="财务",max(incoming)
- 语句：
select name,sid from emp inner join dept on emp.dept2=dept.dept1 where dept_name="财务" and incoming=(select max(incoming) from emp inner join dept on emp.dept2=dept.dept1 where dept_name="财务")
- 求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称
- 结果：name,dept_name
- 条件：incoming>7500,incoming<8500
- 语句：
select name,dept_name from emp left join dept on emp.dept2=dept.dept1 where incoming between 7500 and 8500 order by age desc limit 1【如果有两个相同的，只会显示一个，有缺陷】 - 语句2：
select name,dept_name from emp inner join dept on emp.dept2=dept.dept1 where incoming between 7500 and 8500 and age=(select max(age) from emp inner join dept on emp.dept2=dept.dept1 where incoming between 7500 and 8500) - 语句3：
select name,dept_name from emp inner join dept on emp.dept2=dept.dept1 where incoming between 7500 and 8500 and (dept2,age) in (select dept1,max(age) from emp inner join dept on emp.dept2=dept.dept1 where incoming between 7500 and 8500)
浙公网安备 33010602011771号