SQL多表操作练习

建表

数据比较水,但针对题目的话勉强够用.

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `depid` int NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `depname` varchar(20) NOT NULL COMMENT '部门名称',
  `info` varchar(200) DEFAULT NULL COMMENT '部门简介',
  PRIMARY KEY (`depid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='部门';

insert  into `department`(`depid`,`depname`,`info`) values 
(1,'销售部','负责产品的销售'),
(2,'运营部','负责运营'),
(3,'研发部','负责开发产品');

`DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
  `empid` int NOT NULL AUTO_INCREMENT COMMENT '雇员编号',
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `sex` varchar(2) NOT NULL COMMENT '性别',
  `title` varchar(20) NOT NULL COMMENT '职称',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `depid` int NOT NULL COMMENT '所属部门',
  PRIMARY KEY (`empid`),
  KEY `employee_department_depid_fk` (`depid`),
  CONSTRAINT `employee_department_depid_fk` FOREIGN KEY (`depid`) REFERENCES `department` (`depid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='雇员表';
``
insert  into `employee`(`empid`,`name`,`sex`,`title`,`birthday`,`depid`) values 
(1,'张三','男','销售员','1995-04-08',1),
(2,'李四','女','客服','1991-08-16',2),
(3,'王五','男','Java开发','1985-07-24',3),
(4,'赵六','女','销售员','2021-04-30',1);

DROP TABLE IF EXISTS `salary`;

CREATE TABLE `salary` (
  `empid` int NOT NULL COMMENT '雇员编号',
  `basesalary` double NOT NULL COMMENT '基本工资',
  `deduction` double NOT NULL COMMENT '扣除',
  `titlesalary` double NOT NULL COMMENT '职务工资',
  PRIMARY KEY (`empid`),
  CONSTRAINT `salary_employee_empid_fk` FOREIGN KEY (`empid`) REFERENCES `employee` (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资表';

insert  into `salary`(`empid`,`basesalary`,`deduction`,`titlesalary`) values 
(1,5000,200,700),
(2,6000.5,0,500.05),
(3,10000,0,1000);

题目及解答

题目可能有点小问题,但没什么影响,够练习就行了。
答案不唯一，合理即可。总觉得还有很多可以优化的地方，有空回头看看吧。

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

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

# 3. 删除人事部门的部门记录
update department
set info = null
where depname = '人事';

# 4. 查询出每个雇员的雇员编号，实发工资，应发工资
# 还是没看懂啥意思
select s.empid, basesalary, titlesalary
from employee
         join salary s on employee.empid = s.empid;

# 方言写法
select employee.empid, basesalary, titlesalary
from employee,
     salary
where salary.empid = employee.empid;

# 5. 查询姓张且年龄小于 40 的员工记录
select empid, name, sex, title, birthday, depid
from employee
where name like '张%'
  and age < 40;

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

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

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

# 9. 统计各部门的部门名称，实发工资总和，平均工资
select depname, sum_money, avg_money
from ( # 按理说应该再嵌套一个子查询,先把所有工资算出来,效率高一些,为了美观暂时先这样
         select depid,
                sum(basesalary + titlesalary - deduction) sum_money,
                avg(basesalary + titlesalary - deduction) avg_money
                # 用join不太好看,就用where了
         from salary s,
              employee e
         where s.empid = e.empid
         group by depid
     ) dep_money
         join department on dep_money.depid = department.depid;

# 正常的写法
select depname, sum_money, avg_money
from (
         select depid, sum(money) sum_money, avg(money) avg_money
         from (
                  select empid, (basesalary + titlesalary - deduction) money
                  from salary
              ) as sm
                  join employee e on sm.empid = e.empid
         group by depid
     ) dep_money
         join department on dep_money.depid = department.depid;


# 10. 查询比销售部门所有员工基本工资都高的雇员姓名
select name
from salary
         join employee on salary.empid = employee.empid
where basesalary > all (
    select basesalary # 找到销售部所有人的工资
    from employee
             join department d on d.depid = employee.depid
             join salary s on employee.empid = s.empid
    where depname = '销售部');