mysql练习

MySQL员工工资表练习
题目要求：
现在有员工表、部门表和薪资表。
部门表depart的字段有depart_id， name;
员工表 staff 的字段有 staff_id， name， age， depart_id;
薪资表salary 的字段有 salary_id，staff_id，salary，year。
（问题a）：求每个部门’2016-09’月份的部门薪水总额
（问题b）：求每个部门的部门人数，要求输出部门名称和人数
（问题c）：求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数

1.首先创建员工工资表数据库，并使用

create database if not exists emp1;
use emp1;

2.创建部门表

create table if not exists depart(
	depart_id int primary key auto_increment,
	name varchar(10)
);

3.创建员工表

create table if not exists staff(
	staff_id int primary key auto_increment,
	name varchar(10),
	age int,
	depart_id int,
	foreign key (depart_id) references depart(depart_id)
);

4.创建工资表

create table if not exists salary(
	salary_id int primary key auto_increment,
	staff_id int,
	salary decimal(8,2),
	time timestamp,
	foreign key (staff_id) references staff(staff_id)
);

5.插入部门信息

insert into depart(name) values
	('销售部'),
	('技术部'),
	('财务部'),
	('采购部');

6.插入员工信息（名字都是瞎想的，如有重名请多担待）

insert into staff(name,age,depart_id) values
	('张红',25,1),
	('李晓',28,2),
	('刘明',23,3),
	('张丽',25,4),
	('高鹏',27,3),
	('李华',22,2),
	('赵青',26,1),
	('陈宫',20,3),
	('刘敏',25,2),
	('李红',22,4);

7.插入员工工资信息

insert into salary(staff_id,salary,time) values
	(1,10002.56,'2016-8-1'),
	(1,10002.56,'2016-9-1'),
	(1,10002.56,'2016-10-1'),
	(2,5983.8,'2016-8-1'),
	(2,5983.8,'2016-9-1'),
	(2,5983.8,'2016-10-1'),
	(3,69879,'2016-8-1'),
	(3,69879,'2016-9-1'),
	(3,69879,'2016-10-1'),
	(4,10069.36,'2016-8-1'),
	(4,10069.36,'2016-9-1'),
	(4,10069.36,'2016-10-1'),
	(5,9687.14,'2016-8-1'),
	(5,9687.14,'2016-9-1'),
	(5,9687.14,'2016-10-1'),
	(6,8957.59,'2016-8-1'),
	(6,8957.59,'2016-9-1'), 
	(6,8957.59,'2016-10-1'),
	(7,69876,'2016-8-1'),
	(7,69876,'2016-9-1'),
	(7,69876,'2016-10-1'),
	(8,10056.26,'2016-8-1'),
	(8,10056.26,'2016-9-1'),
	(8,10056.26,'2016-10-1'),
	(9,9364.56,'2016-8-1'),
	(9,9364.56,'2016-9-1'),
	(9,9364.56,'2016-10-1'),
	(10,5895.65,'2016-8-1'),
	(10,5895.65,'2016-9-1'),
	(10,5895.65,'2016-10-1');

问题1.求每个部门’2016-09’月份的部门薪水总额
将部门表，工资表，员工表进行内连接，查询条件为9月份，对部门分组进行查询

select depart.name,sum(sal.salary) from salary sal
join staff sta on sta.staff_id = sal.staff_id
join depart on depart.depart_id = sta.depart_id
where sal.time = '2016-9-1'
group by depart.depart_id;

问题2：求每个部门的部门人数，要求输出部门名称和人数
将员工表与部门表进行内连接，分组利用聚合函数count(*) 各部门统计人数

select depart.name,count(*) from staff
join depart on depart.depart_id = staff.depart_id
where staff.depart_id = depart.depart_id
group by depart.depart_id;

问题3：求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数
在问题1基础上添加按时间分组条件即可

select depart.name,sum(salary.salary),salary.time from salary
join staff on staff.staff_id = salary.staff_id
join depart on depart.depart_id = staff.depart_id
group by depart.depart_id,
salary.time;