数据库进阶练习

进阶数据库训练任务

任务内容

数据表

	雇员表(employee):雇员编号(empid,主键)，姓名(name)，性别(sex)，职称(title)，出生日期(birthday)，所属部门(depid)
	部门(department):部门编号(depid,主键)，部门名称(depname)
	工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工资(titlesalary)，扣除(deduction)

需求

1. 修改表结构，在部门表中添加部门简介字段
2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资
为 700
3. 删除人事部门的部门记录
4. 查询出每个雇员的雇员编号，实发工资，应发工资
5. 查询姓张且年龄小于 40 的员工记录
6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资
7. 查询销售部门的雇员姓名，工资
8. 统计各职称的人数
9. 统计各部门的部门名称，实发工资总和，平均工资
10. 查询比销售部门所有员工基本工资都高的雇员姓名

任务代码

创建表

CREATE TABLE `employee` (
  `empid` varchar(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `title` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `depid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

 create table department(
 depid varchar(10) primary key,
 depname varchar(20));

create table salary(
    empid varchar(10) not null,
    basesalary decimal(19,2),
    titlesalary decimal(19,2),
    deduction decimal(19,2)
);

增加内容

insert into employee values
('01','李四',null,'师','1990-9-8','03'),
('02','王五',null,'工程师','1990-8-8','03'),
('06','张三',null,'hr','1990-7-8','01'),
('03','周元',null,'hr','1990-9-9','01'),
('04','二蛋',null,'调节师','1990-10-8','02'),
('05','麻子',null,'调节师','1990-7-8','02');
//由于5插一条
('06','张三',null,'hr','1990-7-8','03');
//由于10插一条
('07','富婆',null,'工程师','1990-7-8','03');
insert into department values
('01','人事'),
('02','销售'),
('03','技术');

insert into salary values
('01',1000,3000,200),
('02',2000,4000,100),
('03',1500,1000,300),
('04',1400,5000,200),
('05',2100,3000,500),
('06',3200,5000,300);
//由于10插一条
('07',5000,5000,300);
select * from employee;
select * from department;
select * from salary;

在2需求处理后的结果：

实现需求

1. 修改表结构，在部门表中添加部门简介字段

alter table department add department_intro varchar(10);
select * from department;

2.将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700

update employee , salary  
set 
employee.title='工程师',salary.basesalary=2000,salary.titlesalary=700
where 
employee.empid=salary.empid and employee.name='李四';

在上面的图中有

3. 删除人事部门的部门记录

delete from employee
where depid=(select depid from department where depname='人事') ;
select * from employee;

4. 查询出每个雇员的雇员编号，实发工资，应发工资(下面实发和应发写反了。。)

select employee.empid,salary.basesalary+salary.titlesalary as '实发',salary.basesalary+salary.titlesalary-salary.deduction as '应发'
from employee,salary
where employee.empid=salary.empid;

5. 查询姓张且年龄小于 40 的员工记录

select *  from employee where name like '张%' and birthday>'1980-11-24';
//真实版
select * from employee where name like '张%' and 
(year(now())-year(birthday))>40;

6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资

注意一定要加限制，不然会造成笛卡儿积（即row*row*row）

select employee.empid,employee.name,employee.title,department.depname,salary.basesalary+salary.titlesalary-salary.deduction  as '实发'
from employee,department,salary
where employee.depid=department.depid and salary.empid=employee.empid;

7. 查询销售部门的雇员姓名，工资

select employee.name,salary.basesalary 
from employee
join salary
on salary.empid=employee.empid
join department on employee.depid=department.depid
where department.depname='销售';

8. 统计各职称的人数

//title不会影响
select title,count(*) from employee group by depid,title;

9. 统计各部门的部门名称，实发工资总和，平均工资

select department.depname,
sum(salary.basesalary+salary.titlesalary-salary.deduction)  
as '实发总和',
round(avg(salary.basesalary+salary.titlesalary-salary.deduction),2)
as '平均工资'
from department,salary
join employee on salary.empid=employee.empid
where department.depid=employee.depid
group by department.depname;

10. 查询比销售部门所有员工基本工资都高的雇员姓名

//感觉应该会有更简便的，欢迎大家留言告知我
select employee.name 
from employee join salary
on employee.empid=salary.empid
join department on employee.depid=department.depid
where basesalary>any(select max(basesalary)
from employee join salary
on employee.empid=salary.empid
join department on employee.depid=department.depid
where depname='销售');

插入数据前：

插入数据后：

总结：

1、题目难度适当，除了生日获取年龄上找不到解决方案，其他的难度不大

2、只删除数据不删除表用truncate table 表名

3、对表字段操作用alter table ，对表直接针对表即可

``

插入数据前：

[外链图片转存中…(img-wFcnfxm4-1606462194016)]

插入数据后：

[外链图片转存中…(img-7XTsUSc5-1606462194017)]

总结：

1、题目难度适当，除了生日获取年龄上找不到解决方案，其他的难度不大

2、只删除数据不删除表用truncate table 表名

3、对表字段操作用alter table ，对表直接针对表即可

ps：如果觉得这篇文章能够帮到你，就赏个赞呗~~（不要脸）