MySQL数据库设计与SQL练习：员工表、请假表及数据操作

一、数据库表设计

员工表 (emp)

| 字段名 | 数据类型 | 说明 | |---|---|---| | empid | INT | 员工工号（主键） | | name | VARCHAR(50) | 员工姓名 | | phone | VARCHAR(20) | 手机号码 | | dept | VARCHAR(20) | 部门 (行政、开发、测试、销售、财务、后勤) | | job | VARCHAR(20) | 职位 (总经理、总监、组长、员工) | | jobyear | INT | 工龄 | | level | VARCHAR(20) | 级别 | | addr | VARCHAR(100) | 家庭住址 |

请假表 (holiday)

| 字段名 | 数据类型 | 说明 | |---|---|---| | begintime | DATE | 开始时间 (精确到年月日) | | endtime | DATE | 结束时间 (精确到年月日) | | days | DECIMAL(4,1) | 天数 (精确到0.5天) | | reason | VARCHAR(20) | 请假原因 (事假、病假、年假、其他) | | empid | INT | 员工工号 (外键，关联员工表) |

二、数据插入

@1 创建两张表，每个表都要有主键

CREATE TABLE emp (
    empid INT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(20),
    dept VARCHAR(20),
    job VARCHAR(20),
    jobyear INT,
    level VARCHAR(20),
    addr VARCHAR(100)
);

CREATE TABLE holiday (
    begintime DATE,
    endtime DATE,
    days DECIMAL(4,1),
    reason VARCHAR(20),
    empid INT,
    PRIMARY KEY (begintime, empid),
    FOREIGN KEY (empid) REFERENCES emp(empid)
);

@2 向emp中插入100条数据

-- 插入100条员工数据
-- 请根据实际情况修改数据内容
INSERT INTO emp (empid, name, phone, dept, job, jobyear, level, addr) VALUES
(1, '张三', '13812345678', '开发', '员工', 3, '初级', '北京市'),
(2, '李四', '15612345678', '测试', '组长', 5, '中级', '上海市'),
(3, '王五', '18712345678', '销售', '员工', 2, '初级', '广州市'),
-- ... 继续插入其他数据
;

@3 向holiday中添加100条数据

-- 插入100条请假数据
-- 请根据实际情况修改数据内容
INSERT INTO holiday (begintime, endtime, days, reason, empid) VALUES
('2023-09-01', '2023-09-03', 2.5, '病假', 1),
('2023-09-10', '2023-09-12', 2, '年假', 2),
('2023-09-15', '2023-09-16', 1, '事假', 3),
-- ... 继续插入其他数据
;

三、SQL语句练习

9月份请病假的人数

SELECT COUNT(DISTINCT empid) FROM holiday
WHERE reason = '病假' AND MONTH(begintime) = 9;

开发部9月份请假的人名和电话号码

SELECT e.name, e.phone
FROM emp e
JOIN holiday h ON e.empid = h.empid
WHERE e.dept = '开发' AND MONTH(h.begintime) = 9;

每个人的请假次数

SELECT e.empid, e.name, COUNT(*) AS 请假次数
FROM emp e
JOIN holiday h ON e.empid = h.empid
GROUP BY e.empid, e.name;

统计每个部门有多少人

SELECT dept, COUNT(*) AS 人数
FROM emp
GROUP BY dept;

统计每个部门请假次数

SELECT e.dept, COUNT(*) AS 请假次数
FROM emp e
JOIN holiday h ON e.empid = h.empid
GROUP BY e.dept;

从来没请过假的员工编号和人名

方法一：外连接

SELECT e.empid, e.name
FROM emp e
LEFT JOIN holiday h ON e.empid = h.empid
WHERE h.empid IS NULL;

方法二：子查询

SELECT empid, name
FROM emp
WHERE empid NOT IN (SELECT DISTINCT empid FROM holiday);

哪个类型的请假次数最多【病假？年假？】

SELECT reason, COUNT(*) AS 请假次数
FROM holiday
GROUP BY reason
ORDER BY 请假次数 DESC
LIMIT 1;

四、注意事项

员工工号要准确
请假的结束时间不能早与开始时间
请假的结束时间和开始时间之间至少间隔半天
请假原因有事假、病假、年假、其他
请根据实际情况修改数据内容