引言
MySQL作为一种广泛使用的开源关系型数据库管理系统，其强大的查询能力是数据库管理员和开发人员必备的技能。通过解决实际问题，我们可以更好地理解SQL查询的原理和技巧。本文将提供30个经典查询例题，并对其解析，帮助读者提升数据库查询能力。
例题解析
例题1：查询所有员工的姓名和薪水
SELECT firstname, salary FROM employees;
解析：这是一个简单的查询，用于检索
employees
表中员工的姓名和薪水信息。
例题2：查询薪水大于50000的员工信息
SELECT * FROM employees WHERE salary > 50000;
解析：使用
WHERE
子句来过滤薪水大于50000的员工信息。
例题3：查询各部门的平均薪水，并按平均薪水降序排列
SELECT deptid, AVG(salary) AS avgsalary FROM employees GROUP BY deptid ORDER BY avgsalary DESC;
解析：使用
GROUP BY
和
AVG()
函数来计算每个部门的平均薪水，并使用
ORDER BY
进行降序排列。
例题4：创建一个名为
departments
的新表，包含
deptid
和
deptname
两个字段
CREATE TABLE departments (
deptid INT PRIMARY KEY,
deptname VARCHAR(50)
);
解析：使用
CREATE TABLE
语句创建一个新的表，并定义字段和主键。
例题5：向
departments
表中插入三条记录
INSERT INTO departments (deptid, deptname) VALUES (1, ‘Sales’), (2, ‘Marketing’), (3, ‘HR’);
解析：使用
INSERT INTO
语句向
departments
表中插入多条记录。
例题6：查询所有部门名称中包含”ing”的部门信息
SELECT * FROM departments WHERE deptname LIKE ‘%ing%’;
解析：使用
LIKE
子句和通配符
%
来查询包含特定文本的记录。
例题7：查询薪水在10000到20000之间的员工信息
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;
解析：使用
BETWEEN
操作符来查询特定范围内的记录。
例题8：查询所有员工的姓名，但不包括重复的姓名
SELECT DISTINCT firstname FROM employees;
解析：使用
DISTINCT
关键字来避免查询结果中出现重复的姓名。
例题9：查询员工的姓名和直接上级的姓名
SELECT e.name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.managerid = m.employeeid;
解析：使用
JOIN
操作来关联员工和他们的直接上级。
例题10：查询所有员工的姓名和他们的部门名称
SELECT e.name, d.deptname
FROM employees e
JOIN departments d ON e.deptid = d.deptid;
解析：使用
JOIN
操作来关联员工和他们的部门。
例题11：查询所有部门中员工数量大于5的部门信息
SELECT deptid, COUNT(*) AS employee_count
FROM employees
GROUP BY deptid
HAVING COUNT(*) > 5;
解析：使用
GROUP BY
和
HAVING
子句来筛选满足特定条件的记录。
例题12：查询所有薪水低于平均薪水的员工信息
SELECT * FROM employees WHERE salary < (SELECT AVG(salary) FROM employees);
解析：使用子查询来计算平均薪水，并使用
WHERE
子句来筛选低于平均薪水的员工。
例题13：查询所有员工的姓名和他们的直接上级的姓名
SELECT e.name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.managerid = m.employeeid;
解析：使用
LEFT JOIN
来确保即使某些员工没有直接上级，也能查询到所有员工的信息。
例题14：查询所有部门的名称，但不包括那些没有员工的部门
SELECT deptname
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.deptid = d.deptid
);
解析：使用
NOT EXISTS
子查询来排除没有员工的部门。
例题15：查询所有薪水高于其部门平均薪水的员工信息
SELECT e.name, e.salary, AVG(e.salary) OVER (PARTITION BY e.deptid) AS dept_avg_salary
FROM employees e;
解析：使用窗口函数
AVG() OVER (PARTITION BY ...)
来计算每个部门的平均薪水，并比较员工的薪水。
例题16：查询所有员工的姓名和他们的部门名称，包括没有部门的员工
SELECT e.name, d.deptname
FROM employees e
LEFT JOIN departments d ON e.deptid = d.deptid;
解析：使用
LEFT JOIN
来确保即使某些员工没有部门，也能查询到所有员工的信息。
例题17：查询所有部门的名称和员工数量，包括没有员工的部门
SELECT d.deptname, COUNT(e.employeeid) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.deptid = e.deptid
GROUP BY d.deptname;
解析：使用
LEFT JOIN
和
GROUP BY
来查询每个部门的名称和员工数量。
例题18：查询所有薪水在10000到20000之间的员工姓名，不包括重复的姓名
SELECT DISTINCT firstname
FROM employees
WHERE salary BETWEEN 10000 AND 20000;
解析：使用
DISTINCT
关键字和
BETWEEN
操作符来查询特定薪水范围内的员工姓名。
例题19：查询所有员工的姓名和他们的直接上级的姓名，包括没有直接上级的员工
SELECT e.name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.managerid = m.employeeid;
解析：使用
LEFT JOIN
来确保即使某些员工没有直接上级，也能查询到所有员工的信息。
例题20：查询所有部门的名称和员工数量，不包括没有员工的部门
SELECT d.deptname, COUNT(e.employeeid) AS employee_count
FROM departments d
JOIN employees e ON d.deptid = e.deptid
GROUP BY d.deptname;
解析：使用
JOIN
和
GROUP BY
来查询每个部门的名称和员工数量。
例题21：查询所有薪水低于其部门平均薪水的员工姓名和部门名称
SELECT e.name, d.deptname
FROM employees e
JOIN departments d ON e.deptid = d.deptid
WHERE e.salary < (SELECT AVG(salary) FROM employees WHERE deptid = e.deptid);
解析：使用子查询和
JOIN
来查询低于其部门平均薪水的员工姓名和部门名称。
例题22：查询所有员工的姓名和他们的直接上级的姓名，包括没有直接上级的员工
SELECT e.name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.managerid = m.employeeid;
解析：使用
LEFT JOIN
来确保即使某些员工没有直接上级，也能查询到所有员工的信息。
例题23：查询所有部门的名称和员工数量，包括没有员工的部门
SELECT d.deptname, COUNT(e.employeeid) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.deptid = e.deptid
GROUP BY d.deptname;
解析：使用
LEFT JOIN
和
GROUP BY
来查询每个部门的名称和员工数量。
例题24：查询所有薪水在10000到20000之间的员工姓名，不包括重复的姓名
SELECT DISTINCT firstname
FROM employees
WHERE salary BETWEEN 10000 AND 20000;
解析：使用
DISTINCT
关键字和
BETWEEN
操作符来查询特定薪水范围内的员工姓名。
例题25：查询所有员工的姓名和他们的直接上级的姓名，包括没有直接上级的员工
SELECT e.name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.managerid = m.employeeid;
解析：使用
LEFT JOIN
来确保即使某些员工没有直接上级，也能查询到所有员工的信息。
例题26：查询所有部门的名称和员工数量，不包括没有员工的部门
SELECT d.deptname, COUNT(e.employeeid) AS employee_count
FROM departments d
JOIN employees e ON d.deptid = e.deptid
GROUP BY d.deptname;
解析：使用
JOIN
和
GROUP BY
来查询每个部门的名称和员工数量。
例题27：查询所有薪水低于其部门平均薪水的员工姓名和部门名称
SELECT e.name, d.deptname
FROM employees e
JOIN departments d ON e.deptid = d.deptid
WHERE e.salary < (SELECT AVG(salary) FROM employees WHERE deptid = e.deptid);
解析：使用子查询和
JOIN
来查询低于其部门平均薪水的员工姓名和部门名称。
例题28：查询所有员工的姓名和他们的直接上级的姓名，包括没有直接上级的员工
SELECT e.name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.managerid = m.employeeid;
解析：使用
LEFT JOIN
来确保即使某些员工没有直接上级，也能查询到所有员工的信息。
例题29：查询所有部门的名称和员工数量，包括没有员工的部门
SELECT d.deptname, COUNT(e.employeeid) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.deptid = e.deptid
GROUP BY d.deptname;
解析：使用
LEFT JOIN
和
GROUP BY
来查询每个部门的名称和员工数量。
例题30：查询所有薪水在10000到20000之间的员工姓名，不包括重复的姓名
SELECT DISTINCT firstname
FROM employees
WHERE salary BETWEEN 10000 AND 20000;
解析：使用
DISTINCT
关键字和
BETWEEN
操作符来查询特定薪水范围内的员工姓名。
总结
通过解决这些经典查询例题，读者可以更好地理解SQL查询的原理和技巧。不断练习和探索，将有助于提升数据库查询能力。