常用SQL速查表

一、基础查询语法

1.1 列选择与别名

SELECT name AS "姓名" FROM users;

• 功能：从 users 表中选择 name 列，并将其显示为“姓名”。
• 详解：
  • SELECT：指定要查询的列。
  • name：要查询的列名。
  • AS "姓名"：为查询结果指定别名，使结果更易读。
  • FROM users：指定数据来源表。

1.2 条件过滤

SELECT name FROM users WHERE age > 30;

• 功能：从 users 表中选择年龄大于30的用户的名字。
• 详解：
  • SELECT name：选择 name 列。
  • FROM users：指定数据来源表。
  • WHERE age > 30：过滤条件，只返回年龄大于30的行。

1.3 排序

SELECT name FROM users ORDER BY age DESC;

• 功能：从 users 表中选择名字，并按年龄降序排列。
• 详解：
  • SELECT name：选择 name 列。
  • FROM users：指定数据来源表。
  • ORDER BY age DESC：按 age 列降序排列结果。

1.4 分页

SELECT name FROM users LIMIT 10, 10;

• 功能：从 users 表中选择名字，返回第2页，每页10条。
• 详解：
  • SELECT name：选择 name 列。
  • FROM users：指定数据来源表。
  • LIMIT 10, 10：从第11条记录开始，返回10条记录。

二、表连接

2.1 左外连接

SELECT a.name, b.order_id FROM users a LEFT JOIN orders b ON a.user_id = b.user_id;

• 功能：从 users 表和 orders 表中选择数据，返回所有用户及其订单（包括没有订单的用户）。
• 详解：
  • SELECT a.name, b.order_id：选择 users 表的 name 列和 orders 表的 order_id 列。
  • FROM users a：指定 users 表为左表，并命名为 a。
  • LEFT JOIN orders b ON a.user_id = b.user_id：将 users 表与 orders 表进行左连接，连接条件为 user_id。

三、聚合查询

3.1 聚合函数

SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department;

• 功能：按部门统计员工数量和平均工资。
• 详解：
  • SELECT department, COUNT(*), AVG(salary)：选择 department 列，统计每组的行数（COUNT(*)），计算每组的平均工资（AVG(salary)）。
  • FROM employees：指定数据来源表。
  • GROUP BY department：按 department 列分组。

3.2 分组过滤

SELECT department FROM employees GROUP BY department HAVING AVG(salary) > 5000;

• 功能：按部门分组，只返回平均工资大于5000的部门。
• 详解：
  • SELECT department：选择 department 列。
  • FROM employees：指定数据来源表。
  • GROUP BY department：按 department 列分组。
  • HAVING AVG(salary) > 5000：过滤条件，只保留平均工资大于5000的组。

四、子查询

4.1 标量子查询

SELECT name FROM users WHERE age = (SELECT MAX(age) FROM users);

• 功能：选择年龄最大的用户的名字。
• 详解：
  • SELECT name：选择 name 列。
  • FROM users：指定数据来源表。
  • WHERE age = (SELECT MAX(age) FROM users)：子查询 (SELECT MAX(age) FROM users) 返回 users 表中最大的年龄值，主查询根据这个值过滤出对应的用户。

4.2 表子查询

SELECT name FROM users WHERE department IN (SELECT department FROM departments WHERE location = 'New York');

• 功能：选择位于纽约的部门中的用户的名字。
• 详解：
  • SELECT name：选择 name 列。
  • FROM users：指定数据来源表。
  • WHERE department IN (SELECT department FROM departments WHERE location = 'New York')：子查询 (SELECT department FROM departments WHERE location = 'New York') 返回位于纽约的部门列表，主查询根据这个列表过滤出对应的用户。

五、条件表达式

5.1 CASE表达式

SELECT name, CASE WHEN age > 30 THEN 'Senior' ELSE 'Junior' END AS category FROM users;

• 功能：根据年龄将用户分为“Senior”或“Junior”。
• 详解：
  • SELECT name：选择 name 列。
  • CASE WHEN age > 30 THEN 'Senior' ELSE 'Junior' END AS category：条件表达式，根据 age 列的值返回“Senior”或“Junior”，并命名为 category。
  • FROM users：指定数据来源表。

六、数据定义

6.1 创建表

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

• 功能：创建一个名为 users 的表，包含用户ID、姓名和年龄列。
• 详解：
  • CREATE TABLE users：创建新表 users。
  • user_id INT PRIMARY KEY：定义 user_id 列为整数类型，并设置为主键。
  • name VARCHAR(50)：定义 name 列为长度不超过50的字符串。
  • age INT：定义 age 列为整数类型。

七、数据操作

7.1 插入数据

INSERT INTO users (user_id, name, age) VALUES (1, 'John Doe', 30);

• 功能：向 users 表中插入一条新记录。
• 详解：
  • INSERT INTO users：指定插入数据的表。
  • (user_id, name, age)：指定要插入的列。
  • VALUES (1, 'John Doe', 30)：指定插入的值。

7.2 更新数据

UPDATE users SET age = 31 WHERE user_id = 1;

• 功能：更新 users 表中用户ID为1的用户的年龄。
• 详解：
  • UPDATE users：指定要更新的表。
  • SET age = 31：设置 age 列的值为31。
  • WHERE user_id = 1：过滤条件，只更新用户ID为1的行。

7.3 删除数据

DELETE FROM users WHERE user_id = 1;

• 功能：从 users 表中删除用户ID为1的记录。
• 详解：
  • DELETE FROM users：指定要删除数据的表。
  • WHERE user_id = 1：过滤条件，只删除用户ID为1的行。

八、集合运算

8.1 UNION ALL

SELECT name FROM users WHERE age > 30
UNION ALL
SELECT name FROM former_users WHERE age > 30;

• 功能：合并 users 表和 former_users 表中年龄大于30的用户的名字。
• 详解：
  • SELECT name FROM users WHERE age > 30：选择 users 表中年龄大于30的用户的名字。
  • UNION ALL：合并两个查询结果，保留重复值。
  • SELECT name FROM former_users WHERE age > 30：选择 former_users 表中年龄大于30的用户的名字。

九、动态条件过滤

SELECT name FROM users WHERE department IN ('Sales', 'Marketing');

• 功能：选择部门为“Sales”或“Marketing”的用户的名字。
• 详解：
  • SELECT name：选择 name 列。
  • FROM users：指定数据来源表。
  • WHERE department IN ('Sales', 'Marketing')：过滤条件，只返回部门为“Sales”或“Marketing”的行。

十、静态行添加

SELECT 'John Doe' AS name, 30 AS age FROM DUAL;

• 功能：添加一个静态行，包含名字“John Doe”和年龄30。
• 详解：
  • SELECT 'John Doe' AS name：选择静态值“John Doe”并命名为 name。
  • 30 AS age：选择静态值30并命名为 age。
  • FROM DUAL：DUAL 是一个虚拟表，用于支持静态查询。

十一、排序与过滤

11.1 最终过滤

SELECT name FROM users WHERE age > 30;

• 功能：过滤出年龄大于30的用户的名字。
• 详解：
  • SELECT name：选择 name 列。
  • FROM users：指定数据来源表。
  • WHERE age > 30：过滤条件，只返回年龄大于30的行。

11.2 最终排序

SELECT name FROM users ORDER BY age DESC;

• 功能：按年龄降序排列用户的名字。
• 详解：
  • SELECT name：选择 name 列。
  • FROM users：指定数据来源表。
  • ORDER BY age DESC：按 age 列降序排列结果。