SQL 数据库设计与查询：员工请假系统

员工请假系统 SQL 数据库设计与查询

本示例演示了如何使用 SQL 创建员工表和请假表，并提供了一些基本的 SQL 查询语句，用于分析员工请假数据。

1. 数据表设计

1.1 员工表 (emp)

工号 (empid)：主键，INT 类型
姓名 (name)：VARCHAR 类型，长度 20
手机号 (phone)：VARCHAR 类型，长度 20
部门 (dept)：VARCHAR 类型，长度 20，可取值：'行政'、'开发'、'测试'、'销售'、'财务'、'后勤'
职位 (job)：VARCHAR 类型，长度 20，可取值：'总经理'、'总监'、'组长'、'员工'
工龄 (jobyear)：INT 类型
级别 (level)：INT 类型
家庭住址 (addr)：VARCHAR 类型，长度 50

1.2 请假表 (holiday)

编号 (id)：主键，INT 类型
开始时间 (begintime)：DATE 类型
结束时间 (endtime)：DATE 类型
天数 (days)：FLOAT 类型，精确到 0.5 天
原因 (reason)：VARCHAR 类型，长度 20，可取值：'事假'、'病假'、'年假'、'其他'
员工工号 (empid)：INT 类型，外键关联 emp 表的 empid

2. 创建数据表

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

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

3. 插入示例数据

3.1 向 emp 表中插入 100 条数据

INSERT INTO emp (empid, name, phone, dept, job, jobyear, level, addr)
VALUES
  (1, '张三', '13811111111', '行政', '总经理', 10, 5, '北京市朝阳区'),
  (2, '李四', '13922222222', '开发', '总监', 8, 4, '北京市海淀区'),
  (3, '王五', '13633333333', '测试', '经理', 6, 3, '北京市丰台区'),
  ...
  (100, '赵六', '13999999999', '财务', '员工', 1, 1, '北京市东城区');

3.2 向 holiday 表中添加 100 条数据

INSERT INTO holiday (id, begintime, endtime, days, reason, empid)
VALUES
  (1, '2021-01-01', '2021-01-07', 5, '事假', 1),
  (2, '2021-01-02', '2021-01-03', 1, '病假', 2),
  (3, '2021-01-05', '2021-01-08', 2.5, '年假', 3),
  ...
  (100, '2021-09-29', '2021-09-30', 0.5, '事假', 100);

注意：

员工工号要准确
请假的结束时间不能早于开始时间
请假的结束时间和开始时间之间至少间隔半天
请假原因有 '事假'、'病假'、'年假'、'其他'

4. SQL 查询语句

4.1 9 月份请病假的人数

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

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

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

4.3 每个人的请假次数

SELECT emp.empid, emp.name, COUNT(*) AS holiday_times
FROM emp
LEFT JOIN holiday ON emp.empid = holiday.empid
GROUP BY emp.empid, emp.name;

4.4 统计每个部门有多少人

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

4.5 统计每个部门请假次数

SELECT emp.dept, COUNT(*) AS holiday_times
FROM emp
LEFT JOIN holiday ON emp.empid = holiday.empid
GROUP BY emp.dept;

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

方法 1：不匹配的记录外连接

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

方法 2：子查询 not in

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

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

SELECT reason, COUNT(*) AS holiday_times
FROM holiday
GROUP BY reason
ORDER BY holiday_times DESC
LIMIT 1;

4.8 打印每个员工的累加请假时间，按累加时间从高到低排序，格式为：工号姓名累计时间

SELECT emp.empid, emp.name, SUM(holiday.days) AS total_days
FROM emp
LEFT JOIN holiday ON emp.empid = holiday.empid
GROUP BY emp.empid, emp.name
ORDER BY total_days DESC;

4.9 请病假次数最多的月份是几月，有多少次

SELECT MONTH(begintime) AS month, COUNT(*) AS holiday_times
FROM holiday
WHERE reason = '病假'
GROUP BY month
ORDER BY holiday_times DESC
LIMIT 1;

希望以上内容能够帮助您理解如何使用 SQL 创建和查询员工请假系统数据库。您可以根据实际情况修改数据表结构和查询语句，以满足您的具体需求。