要求：

15个实用查询案例展示数据操作技巧，掌握SQL在企业管理系统中的应用。

一、数据库与表结构设计

1. 创建数据库及员工表

-- 创建员工数据库

CREATE DATABASE mydb8_worker;

USE mydb8_worker;

-- 创建员工信息表

CREATE TABLE t_worker(

    department_id INT(11) NOT NULL COMMENT '部门号',

    worker_id INT(11) PRIMARY KEY NOT NULL COMMENT '职工号',

    worker_date DATE NOT NULL COMMENT '工作时间',

    wages FLOAT(8,2) NOT NULL COMMENT '工资',

    politics VARCHAR(10) NOT NULL DEFAULT '群众' COMMENT '政治面貌',

    name VARCHAR(20) NOT NULL COMMENT '姓名',

    both_date DATE NOT NULL COMMENT '出生日期'

);

2. 表结构说明

字段名	类型	约束	说明
department_id	INT	NOT NULL	部门编号
worker_id	INT	PRIMARY KEY	职工号（唯一标识）
worker_date	DATE	NOT NULL	入职日期
wages	FLOAT(8,2)	NOT NULL	工资（保留2位小数）
politics	VARCHAR(10)	NOT NULL DEFAULT '群众'	政治面貌（默认群众）
name	VARCHAR(20)	NOT NULL	姓名
both_date	DATE	NOT NULL	出生日期

二、数据初始化

插入员工数据

INSERT INTO t_worker VALUES

(101, 1001, '2015-5-4', 7500.00, '群众', '张春燕', '1990-7-1'),

(101, 1002, '2019-2-6', 5200.00, '团员', '李名博', '1997-2-8'),

(102, 1003, '2008-1-4', 10500.00, '党员', '王博涵', '1983-6-8'),

(102, 1004, '2016-10-10', 5500.00, '群众', '赵小军', '1994-9-5'),

(102, 1005, '2014-4-1', 8800.00, '党员', '钱有财', '1992-12-30'),

(103, 1006, '2019-5-5', 5500.00, '党员', '孙菲菲', '1996-9-2');

员工数据预览：

部门号	职工号	工作时间	工资	政治面貌	姓名	出生日期
101	1001	2015-05-04	7500.00	群众	张春燕	1990-07-01
101	1002	2019-02-06	5200.00	团员	李名博	1997-02-08
102	1003	2008-01-04	10500.00	党员	王博涵	1983-06-08
102	1004	2016-10-10	5500.00	群众	赵小军	1994-09-05
102	1005	2014-04-01	8800.00	党员	钱有财	1992-12-30
103	1006	2019-05-05	5500.00	党员	孙菲菲	1996-09-02

三、15个查询操作详解

1. 显示所有职工的基本信息

SELECT * FROM t_worker;

2. 查询所有职工所属部门的部门号（去重）

SELECT DISTINCT department_id FROM t_worker;

结果：101, 102, 103

3. 求出所有职工的人数

SELECT COUNT(*) AS 职工总数 FROM t_worker;

结果：6

4. 列出最高工资和最低工资

SELECT

    MAX(wages) AS 最高工资,

    MIN(wages) AS 最低工资

FROM t_worker;

结果：最高工资10500.00，最低工资5200.00

5. 列出职工的平均工资和总工资

SELECT

    ROUND(AVG(wages), 2) AS 平均工资,

    SUM(wages) AS 总工资

FROM t_worker;

结果：平均工资7000.00，总工资42000.00

6. 创建新表：工作日期表

CREATE TABLE 工作日期表 AS

SELECT worker_id, name, worker_date

FROM t_worker;

7. 显示所有党员的年龄

SELECT

    name,

    TIMESTAMPDIFF(YEAR, both_date, CURDATE()) AS 年龄

FROM t_worker

WHERE politics = '党员';

结果：

• 王博涵：40岁
• 钱有财：30岁
• 孙菲菲：27岁

8. 列出工资在4000-8000之间的职工姓名

SELECT name

FROM t_worker

WHERE wages BETWEEN 4000 AND 8000;

结果：张春燕、李名博、赵小军、孙菲菲

9. 列出所有姓张和姓李的职工姓名

SELECT name

FROM t_worker

WHERE name LIKE '张%' OR name LIKE '李%';

结果：张春燕、李名博

10. 列出部门102和103的非党员职工

SELECT worker_id, name

FROM t_worker

WHERE department_id IN (102, 103)

AND politics != '党员';

结果：赵小军（1004）

11. 按出生日期排序

SELECT *

FROM t_worker

ORDER BY both_date;

排序结果（从年长到年轻）：

1. 王博涵（1983）
2. 张春燕（1990）
3. 钱有财（1992）
4. 赵小军（1994）
5. 孙菲菲（1996）
6. 李名博（1997）

12. 工资最高的前3名职工

SELECT worker_id, name

FROM t_worker

ORDER BY wages DESC

LIMIT 3;

结果：

1. 王博涵（1003）
2. 钱有财（1005）
3. 张春燕（1001）

13. 各部门党员人数

SELECT

    department_id,

    COUNT(*) AS 党员人数

FROM t_worker

WHERE politics = '党员'

GROUP BY department_id;

结果：

• 102部门：2人
• 103部门：1人

14. 各部门工资统计（保留2位小数）

SELECT

    department_id,

    ROUND(SUM(wages), 2) AS 部门总工资,

    ROUND(AVG(wages), 2) AS 部门平均工资

FROM t_worker

GROUP BY department_id;

结果：

部门	总工资	平均工资
101	12700.00	6350.00
102	24800.00	8266.67
103	5500.00	5500.00

15. 总人数≥3的部门

sql

SELECT

    department_id,

    COUNT(*) AS 总人数

FROM t_worker

GROUP BY department_id

HAVING COUNT(*) >= 3;

结果：102部门（3人）

四、高级查询技巧

1. 多条件组合查询

-- 查询30岁以上且工资高于7000的党员

SELECT *

FROM t_worker

WHERE TIMESTAMPDIFF(YEAR, both_date, CURDATE()) > 30

AND wages > 7000

AND politics = '党员';

2. 子查询应用

-- 查询高于平均工资的职工

SELECT name, wages

FROM t_worker

WHERE wages > (SELECT AVG(wages) FROM t_worker);

3. 跨表查询（关联工作日期表）

SELECT

    w.name,

    w.wages,

    d.worker_date AS 入职日期

FROM t_worker w

JOIN 工作日期表 d ON w.worker_id = d.worker_id;

五、性能优化

1. 索引优化

-- 为常用查询字段创建索引

CREATE INDEX idx_department ON t_worker(department_id);

CREATE INDEX idx_wages ON t_worker(wages);

CREATE INDEX idx_politics ON t_worker(politics);

2. 视图简化查询

-- 创建党员信息视图

CREATE VIEW party_members AS

SELECT *

FROM t_worker

WHERE politics = '党员';

3. 使用存储过程

-- 创建部门统计存储过程

DELIMITER //

CREATE PROCEDURE GetDepartmentStats()

BEGIN

    SELECT

        department_id,

        COUNT(*) AS total,

        ROUND(AVG(wages), 2) AS avg_wage

    FROM t_worker

    GROUP BY department_id;

END //

DELIMITER ;

-- 调用存储过程

CALL GetDepartmentStats();

六、总结与思考

1. 数据库基础操作：           创建库、表、插入数据

2. 核心查询技能：

   • 条件查询（WHERE）
   • 聚合函数（COUNT, SUM, AVG, MAX, MIN）
   • 分组统计（GROUP BY, HAVING）
   • 排序与限制（ORDER BY, LIMIT）
   • 数据去重（DISTINCT）
   • 模糊查询（LIKE）
   • 日期计算（TIMESTAMPDIFF）

3. 高级应用技巧：

   • 视图创建
   • 存储过程编写
   • 性能优化策略

      4. 图注：

系统中包含的两个核心实体：员工和部门

• 一个部门可以拥有多个员工（1对多关系）
• 每位员工只属于一个部门
• 通过员工表中的department_id（外键）关联到部门表的主键