数据表:
雇员表(employee):雇员编号(empid,主键),姓名(name),性别(sex),
职称(title),出生日期(birthday),所属部门(depid)
部门(department):部门编号(depid,主键),部门名称(depname)
工资表(salary):雇员编号(empid),基本工资(basesalary),职务工
资(titlesalary),扣除(deduction)
需求:
- 修改表结构,在部门表中添加部门简介字段
- 将李四的职称改为“工程师”,并将她的基本工资改成 2000,职务工资
为 700 - 删除人事部门的部门记录
- 查询出每个雇员的雇员编号,实发工资,应发工资
- 查询姓张且年龄小于 40 的员工记录
- 查询雇员的雇员编号,姓名,职称,部门名称,实发工资
- 查询销售部门的雇员姓名,
- 统计各职称的人数
- 统计各部门的部门名称,实发工资总和,平均工资
- 查询比销售部门所有员工基本工资都高的雇员姓名
-- 雇员表
CREATE TABLE employee(
empid int auto_increment PRIMARY key,
ename VARCHAR(4),
sex enum('男','女'),
title VARCHAR(5),
birthday date,
depid int
) ENGINE = INNODB DEFAULT charset = utf8mb4;
-- 部门
CREATE TABLE department(
depid int PRIMARY key,
depname VARCHAR(10)
)ENGINE = INNODB DEFAULT charset = utf8mb4;
-- 工资表
CREATE TABLE salary(
empid int ,
basesalary VARCHAR(10),
titlesalary VARCHAR(10),
deduction VARCHAR(10)
)ENGINE = INNODB DEFAULT charset = utf8mb4;
employee
+-------+-------+------+--------+------------+-------+
| empid | ename | sex | title | birthday | depid |
+-------+-------+------+--------+------------+-------+
| 1 | 张三 | 男 | 程序员 | 1999-02-14 | 1 |
| 2 | 李四 | 女 | 宣传员 | 1999-03-14 | 2 |
| 3 | 王五 | 女 | 文艺员 | 1999-04-14 | 3 |
+-------+-------+------+--------+------------+-------+
mysql> insert into employee values(null,'张三','男','程序员','1999-02-14',1);
mysql> insert into employee values(null,'李四','女','宣传员','1999-03-14',2);
mysql> insert into employee values(null,'王五','女','文艺员','1999-04-14',3);
salary
+-------+------------+-------------+-----------+
| empid | basesalary | titlesalary | deduction |
+-------+------------+-------------+-----------+
| 1 | 3000 | 500 |