SQL训练营通关

SQL训练营

网站：http://sqlcamp.jdatacloud.cn/

数据库表结构

• 

基础查询

基本查询 - 查看所有员工

编写SQL查询语句，查看employees表中的所有员工信息。

使用SELECT指定要查询的内容，使用FROM指定选定的数据表：

SELECT * FROM employees;

条件查询 - 技术部员工

查询所有技术部的员工信息。

使用WHERE添加查询条件：

SELECT * FROM employees
WHERE department='技术部';

排序查询 - 按薪资排序

查询所有员工信息，按薪资从高到低排序。

使用ORDER BY根据salary进行排序，DESC表示从高到低排序：

SELECT * FROM employees
ORDER BY salary DESC;

去重查询 - 不重复的部门

查询所有不重复的部门名称。

使用GROUP BY将department分组进行聚合达到去重效果：

SELECT department FROM employees
GROUP BY department;

限制结果 - 前3名高薪员工

查询薪资最高的3名员工信息。

使用LIMIT限制输出结果：

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 3;

特定列查询 - 员工姓名和薪资

只查询员工的姓名和薪资信息。

SELECT name, salary FROM employees;

条件查询

数值范围查询 - 高薪员工

查询薪资超过8000的员工信息。

使用WHERE添加查询条件：

SELECT * FROM employees
WHERE salary > 8000;

多条件查询 - 技术部高薪员工

查询技术部中薪资超过8500的员工。

使用AND实现多条件查询：

SELECT * FROM employees
WHERE department='技术部'
AND salary > 8500;

IN查询 - 特定部门员工

查询技术部或销售部的员工信息。

使用OR实现，或者使用IN

SELECT * FROM employees
WHERE department = '技术部'
OR department = '销售部';

SELECT * FROM employees
WHERE department IN ('技术部', '销售部');

模糊查询 - 姓名包含特定字符

查询姓名中包含"王"字的员工。

使用LIKE实现模糊查询，%表示任意多个字符

SELECT * FROM employees
WHERE name LIKE '%王%';

日期查询 - 2022年入职员工

查询2022年入职的员工信息。

给hire_date限制条件：

SELECT * FROM employees
WHERE hire_date >= '2022-01-01'
AND hire_date < '2023-01-01';

聚合函数

聚合函数 - 平均薪资

计算所有员工的平均薪资。

使用AVG()函数计算平均值，使用AS创建新的列表并将结果输出：

SELECT AVG(salary) AS average_salary
FROM employees;

聚合函数 - 最高薪资

查询所有员工中的最高薪资。

使用MAX()函数计算最大值，也可以使用LIMIT限制输出结果：

SELECT MAX(salary) AS highest_salary
FROM employees;

SELECT salary AS highest_salary
FROM employees
ORDER BY salary DESC
LIMIT 1;

聚合函数 - 最低薪资

查询所有员工中的最低薪资。

使用MIN()函数计算最小值，也可以使用LIMIT限制输出结果：

SELECT MIN(salary) AS lowest_salary
FROM employees;

SELECT salary AS lowest_salary
FROM employees
ORDER BY salary ASC
LIMIT 1;

聚合函数 - 薪资总和

计算所有员工薪资的总和。

使用SUM()函数计算列数据总和：

SELECT SUM(salary) AS sum_salary
FROM employees;

条件聚合 - 技术部平均薪资

计算技术部员工的平均薪资。

使用AVG()函数计算平均值，并使用WHERE添加限定条件：

SELECT AVG(salary) AS average_salary
FROM employees
WHERE department='技术部';

分组查询

分组查询 - 各部门人数

统计每个部门的员工人数。

使用COUNT()函数计算满足查询条件的行数，在此处COUNT(*) 计算的是查询结果中的所有行数：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

分组统计 - 各部门平均薪资

统计每个部门的平均薪资。

使用AVG()函数计算平均值：

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

分组统计 - 各部门最高薪资

查询每个部门的最高薪资。

使用MAX()函数计算最大值：

SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

HAVING筛选 - 平均薪资超过7000的部门

查询平均薪资超过7000的部门及其平均薪资。

使用AVG()函数计算平均值，并使用HAVING添加限制条件：

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING average_salary > 7000;

子句	用途
WHERE	在分组前过滤原始数据
HAVING	在分组后过滤聚合结果（如 AVG()、SUM() 等）

复合分组 - 部门薪资统计

查询每个部门的员工数量、平均薪资和最高薪资。

综合以上语句可以得到：

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

连接查询

连接查询 - 员工和部门

查询员工姓名和其所在部门的管理者ID。

使用FROM设定主表并起一个别名，

使用JOIN将departments表与前面的employees表进行内连接

使用ON指定连接条件，员工表中的部门名称要与部门表中的部门名称连接上：

SELECT e.name, d.manager_id
FROM employees e
JOIN departments d
ON e.department = d.name;

内连接 - 员工部门详情

查询员工姓名、薪资和所在部门的详细信息。

使用FROM设定主表并起一个别名，

使用JOIN将departments表与前面的employees表进行内连接

使用ON指定连接条件，员工表中的部门名称要与部门表中的部门名称连接上：

SELECT e.name, e.salary, d.name
FROM employees e
JOIN departments d
ON e.department = d.name;

左连接 - 所有部门员工数

查询所有部门及其员工数量（包括没有员工的部门）。

使用FROM设定主表并起一个别名，

使用LEFT JOIN将employees表与前面的departments表进行左连接

使用ON指定连接条件，员工表中的部门名称要与部门表中的部门名称连接上：

SELECT d.name, COUNT(e.department) AS employee_count
FROM departments d
LEFT JOIN employees e 
ON d.name = e.department
GROUP BY d.name;

特性	INNER JOIN（内连接）	LEFT JOIN（左连接）
连接结果	仅返回两个表中都匹配成功的记录	返回左表所有记录，即使右表没有匹配
没有匹配时	不会出现在结果中	显示左表记录，右表字段为 NULL
应用场景	仅关注有“关联关系”的记录	还要包含“孤立”的记录（例如没有员工的部门）

三表连接 - 项目部门员工

查询所有项目及其所属部门和部门员工数。

使用FROM设定主表并起一个别名，

使用JOIN将departments表与前面的project表进行内连接

使用LEFT JOIN将employees表与前面的departments表进行左连接

使用ON指定连接条件，项目表中的部门ID要与部门表中的部门ID、员工表中的部门名称与部门表中的部门名称连接上

最后使用GROUP BY按项目名称进行分组统计：

SELECT p.name AS project_name, d.name, COUNT(e.id) AS employee_count
FROM projects p
JOIN departments d
ON p.department_id = d.id
LEFT JOIN employees e
ON d.name = e.department
GROUP BY p.name;

子查询

子查询 - 高于平均薪资的员工

查询薪资高于全公司平均薪资的员工信息。

SELECT * FROM employees
WHERE salary > (
	SELECT AVG(salary) FROM employees
	);

子查询 - 技术部最高薪资员工

查询技术部中薪资最高的员工信息。

SELECT * FROM employees
WHERE salary = (
	SELECT MAX(salary) FROM employees
	);

EXISTS子查询 - 有员工的部门

查询有员工的部门信息。

使用EXISTS ()子查询用于判断是否存在满足条件的记录

使用SELECT 1实现存在记录就返回 TRUE

SELECT *
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department = d.name
);

在 EXISTS 子查询中，只关心是否存在记录，不关心记录的内容

实用查询

实用查询 - 部门预算利用率

计算各部门项目预算总额。

需求：列出部门，左连接其预算总额：

SELECT d.name, SUM(p.budget) AS total_budget
FROM departments d
LEFT JOIN projects p
ON d.id = p.department_id
GROUP BY d.name;

实用查询 - 项目状态统计

统计各种项目状态的数量。

使用COUNT()函数计算满足查询条件的行数：

SELECT status, COUNT(*) AS project_count
FROM projects
GROUP BY status;

数据分析

数据分析 - 薪资分布

按薪资范围统计员工数量：低薪(≤6000)、中薪(6001-8500)、高薪(>8500)。

使用CASE、WHEN、THEN、ELSE、END分出三种情况：

SELECT
CASE
	WHEN salary <= 6000 THEN '低薪'
	WHEN salary BETWEEN 6001 AND 8500 THEN '中薪'
	ELSE '高薪'
END AS salary_level,
COUNT(*) AS employee_count
FROM employees
GROUP BY salary_level;

数据分析 - 部门薪资排名

查询各部门平均薪资，按平均薪资从高到低排序。

SELECT d.name, AVG(e.salary) AS average_salary
FROM employees e
JOIN departments d
ON e.department = d.name
GROUP BY d.name
ORDER BY average_salary DESC;

数据分析 - 入职年份统计

统计各年份入职的员工数量。

使用strftime('%Y',)函数用于提取格式化的日期中的年份：

SELECT strftime('%Y', hire_date) AS hire_year, COUNT(*) AS employee_count
FROM employees
GROUP BY strftime('%Y', hire_date)
ORDER BY hire_year;

strftime(format, date)

• format：格式化字符串（类似时间模板）
• date：是你要处理的日期字段，比如 hire_date

智能判断测试

灵活查询 - 员工总数

查询员工表中的总人数（测试不同COUNT写法的智能识别）。

SELECT COUNT(*) AS employee_count
FROM employees;

排序灵活性 - 部门列表

查询所有部门名称（不要求特定排序，测试排序灵活性）。

SELECT name FROM departments;

别名识别 - 员工姓名

查询所有员工姓名（测试列名别名的智能识别）。

SELECT name FROM employees;