sql语句基础练习2

2021-11-28 09:03:06

财务管理系统-数据库模块

业务目标

根据需求完善 sql 语句

技能目标

掌握 SQL 语句的 CRUD

数据表

雇员表(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.查询比销售部门所有员工基本工资都高的雇员姓名

任务过程

了解数据表以及各列的含义
分析需求，梳理数据表新职课教研教学中心
根据语法，结合需求操作数据库

涉及知识点

MySql 数据表的创建
MySql CRUD 操作

源码如下

/**
 * @Author:cheng
 * @Date:2021-8-8
 */
-- 创建名为kkb01的库
create database if not exists kkb01;
/*
    雇员表(employee):雇员编号(empid,主键)，姓名(name)，性别(sex)，职称(title)，出生日期(birthday)，所属部门(depid)
 */
create table employee(
    empid int not null primary key ,
    name varchar(20),
    sex enum('男', '女'),
    title varchar(20),
    birthday date,
    depid varchar(20)
);
/*
    部门(department):部门编号(depid,主键)，部门名称(depname)
 */
create table department(
    depid int not null primary key ,
    depname varchar(20)
);
/*
    工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工资(titlesalary)，扣除(deduction)
 */
create table salary(
    empid int not null ,
    basesalary int,
    titlesalary int,
    deduction int
);

-- 各需求解决如下
-- 1. 修改表结构，在部门表中添加部门简介字段
alter table department ADD deprofile varchar(20);
-- 2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700
select * from employee e , salary s where name = '李四' and e.empid = s.empid;
update employee e set e.title = '工程师' where e.name = '李四';
update salary s set s.basesalary = 2000, s.titlesalary = 700 where
s.empid = (select a.empid from(select e.empid from employee e where e.name = '李四') as a);
-- 3. 删除人事部门的部门记录
delete from department where depname = '人事部';
-- 4. 查询出每个雇员的雇员编号，实发工资，应发工资
select s.empid as '编号',
       (s.basesalary + s.titlesalary - s.deduction) as '实发工资',
       (s.basesalary + s.titlesalary) as '应发工资'
from salary as s;
-- 5. 查询姓张且年龄小于 40 的员工记录
select e.* from employee e where e.name like '张%' and TIMESTAMPDIFF( YEAR, e.birthday, CURDATE()) < 40;
-- 6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资
select e.empid, e.name, e.title, d.depname,
       (s.basesalary + s.titlesalary - s.deduction) as '实发工资'
from employee e
         left join department d on e.depid = d.depid
         left join salary s on e.empid = s.empid;
-- 7. 查询销售部门的雇员姓名，工资
select e.name, s.basesalary from employee e, salary s, department d
where d.depname = '销售部' and e.depid = d.depid and e.empid = s.empid;
-- 8. 统计各职称的人数
select e.title, COUNT(*) from employee e group by e.title;
-- 9. 统计各部门的部门名称，实发工资总和，平均工资
select d.depname,
       SUM(s.basesalary + s.titlesalary - s.deduction) as '实发工资总和',
       AVG(s.basesalary + s.titlesalary - s.deduction) as '平均工资'
from
    department d, employee e, salary s
    where e.depid = d.depid and e.empid = s.empid
    group by d.depname;
-- 10. 查询比销售部门所有员工基本工资都高的雇员姓名
select e.name from employee e
where e.empid = (select s.empid from salary s
where s.basesalary > (select MAX(s.basesalary) from employee e, salary s
where e.empid = s.empid and e.depid = (select d.depid from department d where d.depname = '销售部')));

码农公寓