掌握SQL基础查询：（第1部分）

作者头像东城·2026-01-31 23:19:59·
数据库系列（5部分）
📝 面向新手的DBMS面试题
📝 面向新手的10大SQL查询
掌握SQL基础：（第2部分）
掌握SQL基础查询：（第1部分）
掌握SQL连接查询：HR员工数据分析
欢迎来到我们的SQL基础查询系列，在这里我们探索分析HR数据的基本SQL技术。在本博客中，我们专注于一个员工管理数据库，包含一个包含关键员工详细信息的Worker表。我们将介绍54个SQL查询来执行基本操作，如过滤、排序、分组和数据处理。这些查询非常适合初学者、数据分析师或HR专业人员，他们希望为员工管理建立强大的SQL基础。
🧩 数据库架构概览
Worker：存储员工详细信息
列：
worker_id
first_name
last_name
salary
joining_date
department
🏗️ 创建表
  1. CREATE TABLE worker (
  2. worker_id INT PRIMARY KEY AUTO_INCREMENT,
  3. first_name VARCHAR(64),
  4. last_name VARCHAR(64),
  5. salary FLOAT,
  6. joining_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  7. department ENUM('HR', 'IT', 'ADMIN', 'ACOOUNTS', 'SALES')
  8. );
sql
📝 插入示例数据
  1. INSERT INTO worker (worker_id, first_name, last_name, salary, joining_date, department) VALUES
  2. (1, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'),
  3. (2, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'),
  4. (3, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'),
  5. (4, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'),
  6. (5, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin'),
  7. (6, 'Vipul', 'Diwan', 200000, '2014-06-11 09:00:00', 'Account'),
  8. (7, 'Satish', 'Kumar', 75000, '2014-01-20 09:00:00', 'Account'),
  9. (8, 'Geetika', 'Chauhan', 90000, '2014-04-11 09:00:00', 'Admin'),
  10. (9, 'Anil', 'Sharma', 120000, '2015-03-15 09:00:00', 'IT'),
  11. (10, 'Pooja', 'Mehta', 95000, '2016-01-22 09:00:00', 'HR'),
  12. (11, 'Rohit', 'Yadav', 150000, '2015-05-10 09:00:00', 'Admin'),
  13. (12, 'Swati', 'Gupta', 110000, '2016-02-18 09:00:00', 'HR'),
  14. (13, 'Rajesh', 'Joshi', 70000, '2016-04-01 09:00:00', 'IT'),
  15. (14, 'Priya', 'Kapoor', 80000, '2017-08-19 09:00:00', 'Account'),
  16. (15, 'Arjun', 'Deshmukh', 140000, '2018-06-23 09:00:00', 'IT'),
  17. (16, 'Meena', 'Reddy', 100000, '2018-09-12 09:00:00', 'Sales'),
  18. (17, 'Suresh', 'Nair', 105000, '2019-01-15 09:00:00', 'Sales'),
  19. (18, 'Kavita', 'Iyer', 95000, '2019-05-03 09:00:00', 'IT'),
  20. (19, 'Deepak', 'Mishra', 120000, '2019-12-07 09:00:00', 'Admin'),
  21. (20, 'Sneha', 'Shah', 115000, '2020-03-11 09:00:00', 'HR'),
  22. (21, 'Akash', 'Bhardwaj', 90000, '2020-07-14 09:00:00', 'IT'),
  23. (22, 'Lakshmi', 'Menon', 85000, '2021-01-25 09:00:00', 'Account'),
  24. (23, 'Gaurav', 'Tiwari', 95000, '2021-06-10 09:00:00', 'IT'),
  25. (24, 'Simran', 'Malhotra', 102000, '2021-11-20 09:00:00', 'Sales'),
  26. (25, 'Anjali', 'Pandey', 108000, '2022-03-18 09:00:00', 'HR'),
  27. (26, 'Aditya', 'Kulkarni', 98000, '2022-07-22 09:00:00', 'IT'),
  28. (27, 'Rani', 'Patel', 89000, '2023-01-05 09:00:00', 'Account'),
  29. (28, 'Rahul', 'Choudhary', 150000, '2023-05-19 09:00:00', 'Admin'),
  30. (29, 'Manish', 'Dutta', 115000, '2023-09-30 09:00:00', 'Sales'),
  31. (30, 'Neha', 'Bajaj', 93000, '2024-01-12 09:00:00', 'Admin');
sql
🔍 员工分析基础SQL查询
以下是54个SQL查询及其解释，旨在帮助您掌握HR数据分析的基本SQL操作。
1. 从Worker表中获取FIRST_NAME，使用别名worker_name
  1. SELECT first_name AS worker_name FROM worker;
sql
2. 从Worker表中获取大写的FIRST_NAME
  1. SELECT UPPER(first_name) FROM worker;
sql
3. 从Worker表中获取DEPARTMENT的唯一值
  1. SELECT DISTINCT department FROM worker;
sql
4. 从Worker表中获取FIRST_NAME的前三个字符
  1. SELECT LEFT(first_name, 3) FROM worker;
sql
5. 从Worker表中获取DEPARTMENT的唯一值并打印其长度
  1. SELECT DISTINCT department, LENGTH(department) FROM worker;
sql
6. 将'a'替换为'A'后打印FIRST_NAME
  1. SELECT REPLACE(first_name, 'a', 'A') FROM worker;
sql
7. 将FIRST_NAME和LAST_NAME连接成单个列COMPLETE_NAME，用空格分隔
  1. SELECT CONCAT(first_name, ' ', last_name) AS complete_name FROM worker;
sql
8. 按FIRST_NAME升序排列打印所有Worker详细信息
  1. SELECT * FROM worker ORDER BY first_name ASC;
sql
9. 按FIRST_NAME升序和DEPARTMENT降序排列打印所有Worker详细信息
  1. SELECT * FROM worker ORDER BY first_name ASC, department DESC;
sql
10. 打印FIRST_NAME为"Vipul"或"Satish"的Worker详细信息
  1. SELECT * FROM worker WHERE first_name IN ('Vipul', 'Satish');
sql
11. 打印FIRST_NAME不是"Vipul"或"Satish"的Worker详细信息
  1. SELECT * FROM worker WHERE first_name NOT IN ('Vipul', 'Satish');
sql
12. 打印DEPARTMENT名称为"Admin"的Worker详细信息
  1. SELECT * FROM worker WHERE department = 'Admin';
sql
13. 打印FIRST_NAME包含字母'a'的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE '%a%';
sql
14. 打印FIRST_NAME以字母'a'结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE '%a';
sql
15. 打印FIRST_NAME以'h'结尾且有六个字符的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE '_____h';
sql
16. 打印FIRST_NAME以'h'结尾且有五个字符的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE '____h';
sql
17. 打印FIRST_NAME以'a'开头且有五个字符的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a____';
sql
18. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
19. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
20. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
21. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
22. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
23. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
24. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
25. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
26. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
27. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
28. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
29. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
30. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
31. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
32. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
33. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
34. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
35. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
36. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
37. 打印FIRST_NAME以'a'开头和结尾的Worker详细信息
  1. SELECT * FROM worker WHERE first_name LIKE 'a%a';
sql
38. 显示部门，以及工资大于90,000的员工总数，只显示员工数量大于2的部门
  1. SELECT department, COUNT(*) FROM worker WHERE salary > 90000
  2. GROUP BY department HAVING COUNT(*) > 2;
sql
39. 显示部门和工资大于80,000的员工总数
  1. SELECT department, COUNT(worker_id) FROM worker
  2. WHERE salary > 80000 GROUP BY department;
sql
40. 使用HAVING显示最低工资大于60,000的部门
  1. SELECT department, MIN(salary) FROM worker
  2. GROUP BY department HAVING MIN(salary) > 60000;
sql
41. 按部门分组员工，只显示平均工资高于150,000的部门
  1. SELECT department, AVG(salary) FROM worker
  2. GROUP BY department HAVING AVG(salary) > 150000;
sql
42. 按部门分组员工，只显示总工资大于500,000但小于1,000,000的部门
  1. SELECT department, SUM(salary) FROM worker
  2. GROUP BY department HAVING SUM(salary) > 500000 AND SUM(salary) < 1000000;
sql
43. 显示部门名称和工资总和大于800,000的工资总和
  1. SELECT department, SUM(salary) FROM worker
  2. GROUP BY department HAVING SUM(salary) > 800000;
sql
44. 查找至少有3名员工且工资大于100,000的部门
  1. SELECT department FROM worker WHERE salary > 100000
  2. GROUP BY department HAVING COUNT(*) >= 3;
sql
45. 计算每个部门的员工总数，只显示数量大于1的部门
  1. SELECT department, COUNT(*) AS worker_count FROM worker
  2. GROUP BY department HAVING COUNT(*) > 1;
sql
46. 显示平均工资高于70,000的部门，按部门分组
  1. SELECT department, AVG(salary) AS avg_salary FROM worker
  2. GROUP BY department HAVING AVG(salary) > 70000;
sql
47. 按部门分组员工，获取每个部门员工的最低工资。只包含最低工资高于50,000的部门
  1. SELECT department, MIN(salary) AS min_salary FROM worker
  2. GROUP BY department HAVING MIN(salary) > 50000;
sql
48. 查找工资总和大于300,000且员工数量大于2的部门
  1. SELECT department, SUM(salary) AS total_salary, COUNT(*) AS worker_count FROM worker
  2. GROUP BY department HAVING SUM(salary) > 300000 AND COUNT(*) > 2;
sql
49. 按部门分组员工，只包含员工数量恰好为2的部门
  1. SELECT department, COUNT(*) AS worker_count FROM worker
  2. GROUP BY department HAVING COUNT(*) = 2;
sql
50. 计算每个部门的平均工资，显示平均工资低于80,000的部门
  1. SELECT department, AVG(salary) AS avg_salary FROM worker
  2. GROUP BY department HAVING AVG(salary) < 80000;
sql
51. 查找每个部门的总工资和平均工资，只显示总工资大于400,000的部门
  1. SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM worker
  2. GROUP BY department HAVING SUM(salary) > 400000;
sql
52. 将SALARY转换为整数，并在Worker表中显示每个员工的结果为SALARY_INT
  1. SELECT worker_id, first_name, last_name, CAST(salary AS INT) AS SALARY_INT FROM worker;
sql
53. 显示SALARY的总和作为FLOAT值
  1. SELECT SUM(salary) AS total_salary FROM worker;
sql
🎉 结论
本博客展示了54个SQL查询来对员工数据库执行基本操作，涵盖过滤、排序、分组和数据处理。这些基础SQL技术使HR专业人员和分析师能够高效地管理和分析员工数据。练习这些查询以加强您的SQL技能，并将其应用于现实世界的HR挑战。
继续探索，愉快查询！
关键词：SQL、数据库、教程、初学者、基础查询、HR数据分析