SQL 中有很多非常重要且常用的语句和子句。以下是按功能分类的常用 SQL 语句：
1. 数据查询基础 (DQL)
SELECT + WHERE
-- 最基本的查询组合
SELECT name, salary, department
FROM employees
WHERE salary > 5000 AND department = 'IT';DISTINCT - 去重
-- 获取唯一的部门列表
SELECT DISTINCT department FROM employees;
-- 统计不同部门的数量
SELECT COUNT(DISTINCT department) FROM employees;ORDER BY - 排序
-- 按工资降序排列
SELECT name, salary
FROM employees
ORDER BY salary DESC;
-- 多列排序：先按部门升序，再按工资降序
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;LIMIT / TOP - 限制结果
-- MySQL, PostgreSQL, SQLite
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;
-- SQL Server
SELECT TOP 10 name, salary
FROM employees
ORDER BY salary DESC;
-- 分页查询
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 20; -- 跳过20条，取10条2. 聚合函数与分组
常用聚合函数
SELECTCOUNT(*) AS total_employees,AVG(salary) AS avg_salary,MAX(salary) AS max_salary,MIN(salary) AS min_salary,SUM(salary) AS total_salary
FROM employees;GROUP BY - 分组
-- 按部门统计
SELECTdepartment,COUNT(*) AS employee_count,AVG(salary) AS avg_salary
FROM employees
GROUP BY department;HAVING - 分组后过滤
-- 筛选出平均工资高于5000的部门
SELECTdepartment,AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;3. 数据操作 (DML)
INSERT - 插入数据
-- 插入单条记录
INSERT INTO employees (name, salary, department, hire_date)
VALUES ('张三', 8000, 'IT', '2024-01-15');
-- 插入多条记录
INSERT INTO employees (name, salary, department)
VALUES('李四', 6000, 'HR'),('王五', 7500, 'IT'),('赵六', 5500, 'Finance');UPDATE - 更新数据
-- 更新特定记录
UPDATE employees
SET salary = salary * 1.1 -- 涨薪10%
WHERE department = 'IT';
-- 更新多个字段
UPDATE employees
SET salary = 7000, department = 'Management'
WHERE name = '张三';DELETE - 删除数据
-- 删除特定记录（一定要用WHERE！）
DELETE FROM employees
WHERE salary < 3000;
-- 清空整个表
DELETE FROM employees; -- 可回滚
TRUNCATE TABLE employees; -- 不可回滚，更快4. 高级查询技巧
CASE WHEN - 条件逻辑
-- 工资等级分类
SELECTname,salary,CASEWHEN salary > 10000 THEN '高薪'WHEN salary > 5000 THEN '中薪'ELSE '低薪'END AS salary_level
FROM employees;子查询 (Subqueries)
-- 在WHERE中使用子查询
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 在SELECT中使用子查询
SELECTname,salary,(SELECT AVG(salary) FROM employees) AS company_avg_salary
FROM employees;
-- 在FROM中使用子查询
SELECT dept_stats.*
FROM (SELECTdepartment,COUNT(*) AS emp_countFROM employeesGROUP BY department
) AS dept_stats
WHERE emp_count > 5;UNION / UNION ALL - 合并结果
-- 合并两个查询结果（去重）
SELECT name FROM current_employees
UNION
SELECT name FROM former_employees;
-- 合并两个查询结果（不去重）
SELECT name FROM employees_2023
UNION ALL
SELECT name FROM employees_2024;5. 数据定义 (DDL)
CREATE TABLE - 创建表
CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100) NOT NULL,email VARCHAR(255) UNIQUE,salary DECIMAL(10,2) DEFAULT 0,department VARCHAR(50),hire_date DATE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);ALTER TABLE - 修改表结构
-- 添加新列
ALTER TABLE employees
ADD phone_number VARCHAR(20);
-- 修改列类型
ALTER TABLE employees
MODIFY salary DECIMAL(12,2);
-- 删除列
ALTER TABLE employees
DROP COLUMN phone_number;
-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);DROP TABLE - 删除表
-- 删除表（谨慎使用！）
DROP TABLE employees;
-- 如果表存在才删除
DROP TABLE IF EXISTS employees;6. 实用函数
字符串函数
SELECTUPPER(name) AS upper_name,LOWER(department) AS lower_dept,LENGTH(name) AS name_length,CONCAT(name, ' - ', department) AS full_info,SUBSTRING(name, 1, 3) AS name_prefix
FROM employees;日期函数
SELECTname,hire_date,CURDATE() AS today,DATEDIFF(CURDATE(), hire_date) AS days_worked,YEAR(hire_date) AS hire_year
FROM employees;数值函数
SELECTsalary,ROUND(salary, 0) AS rounded_salary,CEILING(salary) AS ceiling_salary,FLOOR(salary) AS floor_salary,RAND() AS random_number
FROM employees;7. 实际业务场景组合
复杂报表查询
SELECTdepartment,COUNT(*) AS total_employees,AVG(salary) AS avg_salary,SUM(salary) AS total_salary,COUNT(CASE WHEN salary > 8000 THEN 1 END) AS high_earners_count
FROM employees
WHERE hire_date >= '2023-01-01'
GROUP BY department
HAVING AVG(salary) > 5000
ORDER BY total_salary DESC
LIMIT 10;数据清理和转换
-- 批量更新和清理数据
UPDATE customers
SETemail = LOWER(TRIM(email)),phone = REPLACE(phone, ' ', '')
WHERE created_at < '2024-01-01';ps: 下面补充下GROUP BY跟update的具体细节
1. GROUP BY - 分组的作用
GROUP BY 的核心作用：将数据按照指定的列进行分组，然后对每个组进行聚合计算。
实际业务场景举例：
数据表：sales 销售表
| order_id | product | category | amount | sale_date |
|---|---|---|---|---|
| 1 | iPhone | 手机 | 5000 | 2024-01-15 |
| 2 | 华为 | 手机 | 4000 | 2024-01-16 |
| 3 | 联想 | 电脑 | 6000 | 2024-01-15 |
| 4 | 小米 | 手机 | 3000 | 2024-01-17 |
| 5 | 戴尔 | 电脑 | 5500 | 2024-01-18 |
作用1：统计汇总
-- 按产品类别统计销售情况
SELECTcategory AS 类别,COUNT(*) AS 订单数量,SUM(amount) AS 总销售额,AVG(amount) AS 平均订单金额,MAX(amount) AS 最高订单金额,MIN(amount) AS 最低订单金额
FROM sales
GROUP BY category;结果：
| 类别 | 订单数量 | 总销售额 | 平均订单金额 | 最高订单金额 | 最低订单金额 |
|---|---|---|---|---|---|
| 手机 | 3 | 12000 | 4000 | 5000 | 3000 |
| 电脑 | 2 | 11500 | 5750 | 6000 | 5500 |
作用2：数据透视
-- 按日期和类别双重分组
SELECTsale_date AS 销售日期,category AS 类别,COUNT(*) AS 订单数,SUM(amount) AS 日销售额
FROM sales
GROUP BY sale_date, category
ORDER BY sale_date, category;结果：
| 销售日期 | 类别 | 订单数 | 日销售额 |
|---|---|---|---|
| 2024-01-15 | 手机 | 1 | 5000 |
| 2024-01-15 | 电脑 | 1 | 6000 |
| 2024-01-16 | 手机 | 1 | 4000 |
| 2024-01-17 | 手机 | 1 | 3000 |
| 2024-01-18 | 电脑 | 1 | 5500 |
作用3：识别模式
-- 找出高价值客户群体
SELECTcustomer_segment AS 客户细分,AVG(order_value) AS 平均订单价值,COUNT(*) AS 订单数量
FROM orders
GROUP BY customer_segment
HAVING AVG(order_value) > 1000; -- 只显示高价值群体作用4：数据清洗和分类
-- 将员工按工资范围分组
SELECTCASEWHEN salary < 5000 THEN '低薪'WHEN salary < 10000 THEN '中薪'ELSE '高薪'END AS 薪资等级,COUNT(*) AS 人数,AVG(salary) AS 平均工资
FROM employees
GROUP BYCASEWHEN salary < 5000 THEN '低薪'WHEN salary < 10000 THEN '中薪'ELSE '高薪'END;GROUP BY 的关键特点：
分组后，每个组变成一行
SELECT 中只能出现：
GROUP BY 的列
聚合函数（COUNT, SUM, AVG, MAX, MIN等）
与 HAVING 配合使用：WHERE 过滤行，HAVING 过滤组
2. UPDATE 语句的作用对象
对于你的这个语句：
UPDATE employees
SET salary = salary * 1.1 -- 涨薪10%
WHERE department = 'IT';正确答案：这个语句更新的是 employees 表中的 salary 列。
详细解释：
UPDATE employees：指定要更新的是employees表SET salary = salary * 1.1：将salary列的值更新为原来的 1.1 倍（涨薪10%）WHERE department = 'IT'：只对部门为 'IT' 的员工进行此操作
执行前后的数据变化：
执行前：
| id | name | department | salary |
|---|---|---|---|
| 1 | 张三 | IT | 8000 |
| 2 | 李四 | HR | 6000 |
| 3 | 王五 | IT | 9000 |
执行后：
| id | name | department | salary |
|---|---|---|---|
| 1 | 张三 | IT | 8800 (8000 × 1.1) |
| 2 | 李四 | HR | 6000 (不变) |
| 3 | 王五 | IT | 9900 (9000 × 1.1) |
更多 UPDATE 示例：
-- 更新多个列
UPDATE employees
SETsalary = salary * 1.1,last_raise_date = '2024-01-20'
WHERE department = 'IT';
-- 基于条件的复杂更新
UPDATE products
SETprice = price * 0.9, -- 打9折status = 'On Sale'
WHERE category = 'Electronics'AND stock_quantity > 100;
-- 使用子查询更新
UPDATE employees
SET salary = (SELECT AVG(salary)FROM employeesWHERE department = 'IT'
)
WHERE department = 'HR' AND salary < 5000;重要提醒：
一定要用 WHERE，否则会更新整个表！
-- 危险！更新所有员工 UPDATE employees SET salary = salary * 1.1; -- 安全！只更新特定部门 UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';UPDATE 是永久性操作，在生产环境要谨慎使用
可以先 SELECT 验证：
-- 先查看会影响到哪些行 SELECT * FROM employees WHERE department = 'IT'; -- 再执行更新 UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';