数据库高级任务

 

建表语句

-- 雇员表
CREATE TABLE `employee` (
  `empid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `depid` int(11) DEFAULT NULL,
  PRIMARY KEY (`empid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- 部门表
CREATE TABLE `department` (
  `depid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `depname` varchar(50) NOT NULL,
  PRIMARY KEY (`depid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- 工作表
CREATE TABLE `salary` (
  `empid` int(11) DEFAULT NULL,
  `basesalary` decimal(10,2) DEFAULT NULL,
  `titlesalary` decimal(10,2) DEFAULT NULL,
  `deduction` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

查询语句

-- 1. 修改表结构，在部门表中添加部门简介字段 
alter table department add introduction varchar(100);


-- 2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资 为 700
update employee set title='工程师' where name="李四";
update salary set basesalary=2000,titlesalary=700 
    where empid = (select empid from employee where name="李四");
 

-- 3. 删除人事部门的部门记录 
delete from department where depname = "人事部门";
delete from employee where depid = (
    select depid from department where depname="人事部门"
);
delete from salary where empid in (
    select empid from employee where depid = (
        select depid from department where depname="人事部门"
    )
);

-- 4. 查询出每个雇员的雇员编号，实发工资，应发工资 

select a.empid as '雇员编号',
(a.basesalary+a.titlesalary-a.deduction) as '实发工资',
(a.basesalary+a.titlesalary) as '应发工资'
from salary as a;

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

select employee.*,salary.basesalary,salary.titlesalary,salary.deduction,department.depname,department.introduction from employee
join salary on salary.empid = employee.empid
join department on department.depid = employee.depid
where employee.name like '张%' and year(curdate())-year(employee.birthday)>40;

-- 6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资 
select employee.empid,employee.name,employee.title,department.depname,(salary.basesalary+salary.titlesalary-salary.deduction) as '实发工资'
from employee
join department on employee.depid=department.depid
join salary on employee.empid=salary.empid
order by employee.empid;

-- 7. 查询销售部门的雇员姓名，工资
select employee.name,employee.title,salary.basesalary,salary.titlesalary,salary.deduction from employee
join salary on employee.empid=salary.empid
where employee.depid in (select depid from department where depname='销售部门');

-- 8. 统计各职称的人数 
select title,count(*) from  employee group by title;

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

select temp.depname,sum(actualsalary) as '工资总和',avg(actualsalary) as '平均工资'
from (
    select newsalary.*,department.depname
    from(
        select salary.empid,(salary.basesalary+salary.titlesalary-salary.deduction) as actualsalary 
        from salary
    ) as newsalary
    join employee on newsalary.empid=employee.empid
    join department on employee.depid=department.depid
) as temp
group by temp.depname;


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

select employee.name from (
    select salary.empid from salary where salary.basesalary>(
        select max(salary.basesalary) from salary where salary.empid in (
            select employee.empid from employee
            left join department on employee.depid=department.depid
            where department.depname='销售部门'
        )
    )
)as temp
left join employee
on employee.empid=temp.empid;