MySQL查询语句汇总

# MySQL查询语句汇总

MySQL是一个广泛使用的关系型数据库管理系统（RDBMS），适用于多种应用场景。本文将全面介绍MySQL查询语句，从基础到高级，帮助读者掌握MySQL查询的各个方面。

## 1. 基础查询

### 1.1 选择列

最基本的查询是从表中选择一列或多列。

```sql
SELECT column1, column2 FROM table_name;

例如：

SELECT first_name, last_name FROM employees;

1.2 选择所有列

使用星号（*）可以选择表中的所有列。

SELECT * FROM table_name;

例如：

SELECT * FROM employees;

1.3 使用别名

可以为列或表指定别名，以提高可读性。

SELECT column1 AS alias1, column2 AS alias2 FROM table_name AS alias_table;

例如：

SELECT first_name AS fname, last_name AS lname FROM employees AS e;

2. 条件查询

2.1 WHERE子句

使用WHERE子句来指定过滤条件。

SELECT * FROM table_name WHERE condition;

例如：

SELECT * FROM employees WHERE department = 'Sales';

2.2 比较运算符

常用的比较运算符有：=，<>，>，<，>=，<=。

SELECT * FROM employees WHERE salary > 50000;

2.3 逻辑运算符

可以使用AND、OR和NOT来组合条件。

SELECT * FROM employees WHERE salary > 50000 AND department = 'Sales';

3. 模糊查询

3.1 LIKE子句

使用LIKE子句进行模式匹配，%表示任意数量的字符，_表示单个字符。

SELECT * FROM employees WHERE first_name LIKE 'J%';

3.2 IN和BETWEEN子句

IN用于匹配列表中的值，BETWEEN用于匹配范围内的值。

SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

4. 排序与分组

4.1 ORDER BY子句

使用ORDER BY对子句进行排序，默认为升序，可以使用DESC指定降序。

SELECT * FROM employees ORDER BY salary DESC;

4.2 GROUP BY子句

使用GROUP BY对结果进行分组，常与聚合函数一起使用。

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

4.3 HAVING子句

HAVING用于过滤分组后的结果。

SELECT department, COUNT(*) AS num_employees 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 10;

5. 联合查询

5.1 内连接

INNER JOIN返回两个表中匹配的行。

SELECT e.first_name, e.last_name, d.department_name 
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

5.2 左连接

LEFT JOIN返回左表中的所有行以及右表中匹配的行。

SELECT e.first_name, e.last_name, d.department_name 
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

5.3 右连接

RIGHT JOIN返回右表中的所有行以及左表中匹配的行。

SELECT e.first_name, e.last_name, d.department_name 
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

5.4 全连接

MySQL不支持直接的FULL JOIN，可以使用UNION实现。

SELECT e.first_name, e.last_name, d.department_name 
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, e.last_name, d.department_name 
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

6. 子查询

6.1 标量子查询

返回单个值的子查询。

SELECT first_name, last_name 
FROM employees 
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

6.2 行子查询

返回一行的子查询。

SELECT first_name, last_name 
FROM employees 
WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees);

6.3 表子查询

返回多行的子查询。

SELECT first_name, last_name 
FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

7. 聚合函数

7.1 常用聚合函数

• COUNT(): 计算行数
• SUM(): 计算总和
• AVG(): 计算平均值
• MAX(): 计算最大值
• MIN(): 计算最小值

SELECT COUNT(*) AS num_employees, AVG(salary) AS avg_salary 
FROM employees;

8. 窗口函数

8.1 OVER子句

窗口函数用于在查询结果集中执行计算。

SELECT first_name, last_name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS salary_rank 
FROM employees;

8.2 窗口函数示例

计算每个部门中员工的薪资排名。

SELECT first_name, last_name, salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank 
FROM employees;

希望这篇文章对你有帮助！如果有任何进一步的问题或需要更多的详细信息，请随时告诉我。