MySQL增删改查基础语法与实操指南

一、增加表中数据 (INSERT)

向表中插入数据时，需根据表结构（特别是自增列设置）选择合适的语法格式。

第1步：无自增列时的插入

目的：在没有主键自增约束的标准表中插入记录。

• 指定字段插入：仅对特定列赋值。注意，值的顺序必须与指定的列名顺序严格一致。
  语法：insert into 表名(列1, 列2,...) values(值1, 值2, ...)
• 全字段默认插入：对所有列进行赋值。此时无需写列名，但值的顺序必须与建表时定义的字段顺序完全一致。
  语法：insert into 表名 values(值1, 值2, ...)

第2步：包含自增列时的插入

目的：处理设有 auto_increment 属性的主键列。

在创建表时，通常使用以下语法定义自增主键：

CREATE TABLE IF NOT EXISTS t_person(
    p_id INT PRIMARY KEY AUTO_INCREMENT,
    -- 其他字段...
);

在此类表中插入数据需注意：

1. 指定字段插入：直接省略自增列即可，语法同无自增情况，系统会自动生成ID。
2. 全字段插入：必须手动为自增列赋予 NULL 或 0，以触发自动递增机制。
   示例：INSERT INTO t_person VALUES(NULL, 'wangwu', 32, '男', 150.9);

二、删除表中数据 (DELETE & TRUNCATE)

数据删除分为逻辑逐行删除和物理截断两种模式，性能和后果不同。

第1步：使用 DELETE 语句

语法：DELETE FROM 表名 [WHERE 条件]

• 删除所有数据：DELETE FROM 表名。这会逐行扫描并删除每一行，执行较慢，且自增ID不会重置。
• 删除指定数据：DELETE FROM 表名 WHERE 条件。例如 DELETE FROM t_person WHERE p_id=8;。

第2步：使用 TRUNCATE 语句

语法：TRUNCATE TABLE 表名

优势与区别：

• 性能更高：DELETE 是一条一条记录删除；TRUNCATE 则直接释放存储该表数据的物理空间并重新分配，速度极快。
• 自增重置：DELETE 后自增ID保留最大值继续累加；TRUNCATE 会将自增计数器重置为初始值（通常为1）。

三、修改表中数据 (UPDATE)

UPDATE 用于更改现有记录的值，务必谨慎使用 WHERE 子句以防误覆盖整表数据。

语法：UPDATE 表名 SET 列名1=新值, 列名2=新值... [WHERE 条件]

• 批量更新（慎用）：若不写 WHERE 条件，将修改表中所有行的指定列。
  示例：UPDATE t_person SET age=18;（将所有员工年龄改为18）。
• 精准更新：配合 WHERE 条件修改特定记录。
  示例：UPDATE t_person SET age=28 WHERE p_id=1;（仅修改ID为1的员工年龄）。

四、查询操作中 (SELECT)

查询是数据库最核心的功能，涵盖了从简单取值到复杂逻辑判断的各种场景。

第1步：基础查询与别名

1. 选择列

• 查询所有列：SELECT * FROM 表名;
• 查询部分列：SELECT 列名1, 列名2 FROM 表名;。显式列出字段比使用 * 维护性更好，且在涉及索引时效率略高。

2. 别名 (Alias)

使用 AS 关键字或直接空格为列或表起临时名字，提高结果可读性。

-- 列别名
SELECT employee_id AS 员工编号, salary AS 工资 FROM employees;

-- 简写形式
SELECT employee_id 员工编号, salary 工资 FROM employees;

-- 表别名
SELECT e.employee_id, e.first_name FROM employees e;

3. 数学运算与去重

• 数学运算：可在 SELECT 中对数值列进行加减乘除取模运算。
  示例：SELECT price * 1.2 AS new_price FROM products;
• 去重 DISTINCT：SELECT DISTINCT 列名 FROM 表名;。多列去重需所有组合值均相同才视为重复。

4. CASE WHEN 逻辑分支

用于实现类似编程语言中 if-else 的条件映射：

SELECT 
    employee_id,
    salary,
    CASE
        WHEN salary > 10000 THEN '高薪'
        WHEN salary > 8000 THEN '中级'
        WHEN salary > 5000 THEN '低级'
        ELSE '底层'
    END AS 薪资等级
FROM employees;

5. 查看表结构

使用 DESC 表名; 或 DESCRIBE 表名; 查看列定义和类型。

第2步：条件查询 (WHERE)

语法：SELECT 列名 FROM 表名 WHERE 条件;

1. 比较运算符

• 支持 =, !=, >, <, >=, <= 等。
• 大小写敏感：MySQL 字符串对比默认不区分大小写。若需区分，添加 BINARY 关键字。
  示例：SELECT * FROM employees WHERE BINARY first_name = 'STEVEN';

2. 逻辑组合 (AND / OR)

• AND：所有条件同时满足（相当于 &&）。
• OR：任一条件满足即可（相当于 ||）。

3. 区间与枚举

• BETWEEN ... AND ...：查找指定闭区间内的值。
  示例：SALARY BETWEEN 5000 AND 10000;
• IN (...)：匹配列表中的任意一个值。
  示例：EMPLOYEE_ID IN (100, 105, 110);

4. 空值处理

注意：不能使用 = NULL，必须使用 IS 判断。

• IS NULL：值为空。
• IS NOT NULL：值不为空。

5. 模糊查询 (LIKE)

通配符说明：

• %：代表零个或多个任意字符。
• _：代表单个任意字符。

-- 包含's'的名字
SELECT * FROM employees WHERE first_name LIKE '%s%';
-- 以's'开头
SELECT * FROM employees WHERE first_name LIKE 's%';
-- 以's'结尾
SELECT * FROM employees WHERE first_name LIKE '%s';
-- 第二个字母是's'
SELECT * FROM employees WHERE first_name LIKE '_s%';

第3步：排序 (ORDER BY)

查询结果默认是无序的，可通过 ORDER BY 指定展示顺序。

1. 单列排序

语法：SELECT ... FROM ... ORDER BY 列名 [ASC | DESC];

• ASC：升序（默认）。
• DESC：降序。
• 示例：SELECT * FROM employees ORDER BY salary DESC;（按工资从高到低）。

2. 多列排序

先按第一列排，若第一列值相同，则按第二列排。

-- 工资低到高，若工资相同，则员工ID高到低
SELECT * FROM employees 
ORDER BY salary ASC, employee_id DESC;

3. 综合执行顺序

典型查询结构：SELECT ... FROM ... WHERE ... ORDER BY ...。先通过 WHERE 过滤数据，再对结果集进行排序。