mysql高级

1.建表

employee表

create table  employee (
  empid varchar(10) NOT NULL,
  ename varchar(10) ,
  sex varchar(2) ,
  title varchar(10) ,
  birthday date,
  depid varchar(10)  ,
  PRIMARY KEY (`empid`))； 

department表

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

salary表

create table salary(
  empid  int,
    basesalary int(10),
    titlesalary int(10),
    deduction int(10));

2.题目

-- 1.修改表结构，在部门表中添加部门简介字段
alter table department add deptinfo varchar(10);
-- 2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700
update employee,salary 
set employee.title = '工程师' , salary.basesalary =3000 , salary.titlesalary =700 
where employee.empid = salary.empid and  employee.ename='李四';
-- 3. 删除人事部门的部门记录
delete from department
where depname ='人事';
-- 4. 查询出每个雇员的雇员编号，实发工资，应发工资
SELECT sa.empid as '编号',
(sa.basesalary + sa.titlesalary - sa.deduction) as '实发工资',
(sa.basesalary + sa.titlesalary) as '应发工资'
from salary as sa;
-- 5. 查询姓张且年龄小于 40 的员工记录
select * from employee where ename like '张%' and 
(year(now())-year(birthday))<40;
-- 6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资
select em.empid,em.ename,em.title,dp.depname,
(sa.basesalary + sa.titlesalary - sa.deduction) as '实发工资'
from employee em 
left join  department as dp on em.depid = dp.depid
left join salary as sa  on em.empid = sa.empid;
-- 7. 查询销售部门的雇员姓名，工资
select employee.ename,salary.basesalary 
from employee
join salary
on salary.empid=employee.empid
join department on employee.depid=department.depid
where department.depname='销售';
-- 8. 统计各职称的人数
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.ename 
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='销售');