MySQL SELECT 查询（一）：基础查询语句的实现

1. SELECT 结构

1.1 基本语法结构

SELECT 列名1, 列名2, ... FROM 表名;

1.2 常见结构与问题

• 列的别名

  • 使用空格 + 别名：

    SELECT 12 * salary AS "ANNUAL SALARY"
    FROM employees e;

  • 使用 AS 关键字 + 别名：

    SELECT 12 * salary AS annual_salary
    FROM employees e;

• DISTINCT 去重

  • 正确示范：

    SELECT DISTINCT department_id 
    FROM employees e;

  • 错误示范：

    SELECT employee_id, DISTINCT department_id 
    FROM employees e;  -- 此处 DISTINCT 使用错误

• NULL值问题

  • NULL 不是 0。
  • NULL 不能参与运算。

• 着重号 (``)

  • 当列名与关键字冲突时使用。

• 显示表结构

  DESC employees;

2. WHERE 语句

• 过滤条件

  SELECT *
  FROM employees e 
  WHERE last_name = 'king';

  执行时间为最先执行（为第一道筛网）。

2.1 算术运算符

• 伪表 dual

  SELECT 3 * 4 AS result FROM dual;

• 基本运算符：
  • +, -, *, / (或 DIV)
    • 结果类型为浮点型。
  • % (或 MOD):
    • 结果的符号只与前一个数有关。

2.2 比较运算符

• 基本（与 NULL 无关）：

  • =, <> (或 !=), <, <=, >, >=, <=>（NULL 安全比较）。

• 与 NULL 有关：

  • IS NULL, IS NOT NULL, ISNULL()。

2.3 特殊函数和判断

• LENGTH() - 判断字符串长度。

• LEAST() - 返回最小值。

• GREATEST() - 返回最大值。

• BETWEEN - 判断在区间内，包含边界：

  SELECT *
  FROM employees e 
  WHERE salary BETWEEN 6000 AND 8000;

• IN (set) 或 NOT IN (set)：

  SELECT *
  FROM employees e 
  WHERE salary IN (6000, 7000, 8000);

2.4 模糊查询

• LIKE 用于模糊匹配。

• % 表示任意字符：

  SELECT *
  FROM employees e 
  WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

• _ 表示单个字符：

  SELECT *
  FROM employees e 
  WHERE last_name LIKE '_a%';

• 查找字符 _：

  SELECT *
  FROM employees e 
  WHERE last_name LIKE '_\_%' ESCAPE '\';

  ESCAPE关键字用于指定 LIKE 语句中通配符的替代字符，从而避免匹配特定的字符。

2.5 正则表达式

• 使用示例：

  SELECT 'shkstart' REGEXP '^shk', 'shkstart' REGEXP 't$'
  FROM dual;
  
  SELECT *
  FROM your_table
  WHERE your_column REGEXP '^a.*b$';
  
  SELECT *
  FROM your_table
  WHERE your_column RLIKE 'ex[^a-zA-Z]*';

2.6 逻辑运算符

• 基本：
  • AND (或 &&), OR (或 ||), NOT (或 !)。

2.7 位运算符

• 基本运算符：

运算符	作用	示例
&	按位与	SELECT A & B
|	按位或
~	按位取反
^	按位异或 (XOR)
>>	按位右移
<<	按位左移

3. 排序 ORDER BY

• ORDER BY 关键字

  • ASC 升序（默认）：

    SELECT *
    FROM employees e 
    ORDER BY salary;

  • DESC 降序：

    SELECT *
    FROM employees e 
    ORDER BY salary DESC;

  • ORDER BY 与 WHERE 的顺序：

    • WHERE 要写在 ORDER BY 前，FROM 后：

      SELECT *
      FROM employees e 
      WHERE department_id IN (30, 40, 50)
      ORDER BY salary DESC;

  • 连续排序：

    SELECT *
    FROM employees e 
    WHERE department_id IN (30, 40, 50)
    ORDER BY salary DESC, department_id ASC;

4. 分组GROUP BY

在 MySQL 中，分组功能通过 GROUP BY 子句实现，它允许用户根据一个或多个列对结果集进行分组。分组通常与聚合函数（如 COUNT、SUM、AVG、MAX、MIN 等）结合使用，以汇总各组的数据。

4.1 基本语法

SELECT 列名1, 聚合函数(列名2)
FROM 表名
WHERE 条件
GROUP BY 列名1;

• 列名1: 用于分组的列，通常是需要汇总的属性。
• 聚合函数: 用于计算每个组的统计值。例如：
  • COUNT()：计算行数。
  • SUM()：计算总和。
  • AVG()：计算平均值。
  • MAX()：找到最大值。
  • MIN()：找到最小值。
• FROM: 指定要查询的表。
• WHERE: 可选，过滤条件，限制查询的行。
• GROUP BY: 指定按哪个列进行分组。

4.2 示例

假设有一个 employees 表，结构如下：

employee_id	last_name	department_id	salary
1	Smith	10	6000
2	Johnson	20	7500
3	Williams	10	8000
4	Brown	30	5000
5	Jones	20	9500

• 查询每个部门的员工数量

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

结果:

department_id	employee_count
10	2
20	2
30	1

4.3 与聚合函数结合使用

• 查询每个部门的平均薪水

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

  结果:

  department_id	average_salary
  10	7000
  20	8500
  30	5000

4.4 多重分组

可以按多个列进行分组。例如，查询每个部门中每个职位的员工数量：

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

4.5 与Having结合

• 数据

  department_id	job_id	employee_count
  10	1	2
  20	2	2
  30	3	1

• 查询员工数量大于 1 的部门

  SELECT department_id, COUNT(*) AS employee_count
  FROM employees
  GROUP BY department_id
  HAVING COUNT(*) > 1;

• 结果:

  department_id	employee_count
  10	2
  20	2

5. 分页

• LIMIT

  • 默认偏移量为 0：

    SELECT *
    FROM employees e 
    LIMIT 20, 20;  -- 偏移量，数量

  • 有序声明：

    SELECT *
    FROM employees e 
    WHERE department_id IN (50, 60, 70)
    ORDER BY salary
    LIMIT 20, 20;

  • 8.0 版本的新特性：

    SELECT *
    FROM employees e 
    LIMIT 20 OFFSET 20;  -- 数量, 偏移量