Mysql增删改语句

Insert、Delete和Update

增加（insert语句）

INSERT 语句是 MySQL 中用于向表中插入新数据的语句。它允许将一行或多行数据插入到表中

  • 插入单行数据

    INSERT INTO 表名 (列1, 列2, 列3) 
VALUES (值1, 值2, 值3);

INSERT INTO users (name, age, email) 
VALUES ('Alice', 25, 'alice@example.com');

    如果省略列名，则必须为所有列提供值，且顺序与表定义一致

  • 插入多行数据

    INSERT INTO 表名 (列1, 列2) 
VALUES 
  (值1, 值2),
  (值3, 值4),
  (值5, 值6);
  
INSERT INTO products (name, price) 
VALUES 
  ('Laptop', 999.99),
  ('Phone', 599.99);

  • 插入查询结果，INSERT 语句可以将查询结果插入到表中

    INSERT INTO 目标表 (列1, 列2) 
SELECT 列A, 列B 
FROM 源表 
WHERE 条件;

INSERT INTO archive_orders (order_id, amount)
SELECT id, total 
FROM orders 
WHERE order_date < '2023-01-01';

  • 插入时忽略错误，使用 INSERT IGNORE 可以忽略插入时的错误（如主键冲突）

    INSERT IGNORE INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe');

  • 插入时更新重复记录，使用 ON DUPLICATE KEY UPDATE 可以在插入时更新重复记录

    INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 5000)
ON DUPLICATE KEY UPDATE salary = 5000;

#如果 employee_id 为 1 的记录已存在，则更新 salary 为 5000

  • 插入默认值，如果某些列有默认值，可以使用 DEFAULT 关键字插入默认值

    INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', DEFAULT);

  • 插入 NULL 值，如果某些列允许 NULL，可以显式插入 NULL 值

    INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', NULL);

  • 插入时使用函数，可以在 VALUES 子句中使用函数生成值

    INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('John', 'Doe', NOW());

  • 插入时使用子查询，可以在 VALUES 子句中使用子查询生成值

    INSERT INTO employees (first_name, last_name, department_id)
VALUES ('John', 'Doe', (SELECT department_id FROM departments WHERE department_name = 'Sales'));

  • 插入时指定列的顺序，列的顺序可以与表定义不一致，只要值与列名对应即可

    INSERT INTO employees (last_name, first_name, salary)
VALUES ('Doe', 'John', 5000);

  • 插入时处理自增列，如果表中有自增列（AUTO_INCREMENT），可以省略该列的值

    INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 5000);

插入语句注意事项

  • 表名 (列1, 列2, 列3)应该和VALUES (值1, 值2, 值3)长度对应，否则会报错，即向其中三个列中赋值应该给三个具体的值

  • 插入的数据应该与字段的数据类型相同并且长度在规定范围内

  • 字符和日期型数据应该包含在单引号（'）中

    • 根据 SQL 标准，字符串和日期类型的值必须使用单引号包裹

      INSERT INTO users (name, birthdate) VALUES ('John Doe', '1990-01-01');

    • 单引号是 SQL 标准，而双引号的行为依赖于 MySQL 的配置（如 ANSI_QUOTES 模式），这可能导致代码在不同数据库或不同配置下表现不一致

    • 避免使用双引号，除非你明确知道 MySQL 的配置（如 ANSI_QUOTES 模式）并且有特殊需求

    • 如果字符串中包含单引号，可以使用以下方式

      • 使用反斜杠（\）转义单引号

        INSERT INTO users (name) VALUES ('O\'Reilly');

      • 使用双单引号（''）

        INSERT INTO users (name) VALUES ('O''Reilly');

      • 如果字符串中包含双引号，可以直接使用单引号包裹

        INSERT INTO users (name) VALUES ('John "The Boss" Doe');

  • 当不给某个字段赋值时则默认赋值为null，如果设置该列不能有null值则需要保证该字段有默认值，否则会报错，自增的列可以赋值为null

  • 批量插入：使用多行 VALUES 减少事务提交次数，单次插入 1000 行比 1000 次单行插入快约 10 倍

  • 事务控制：批量操作时显式启用事务

  • unique字段处理冲突，使用ON DUPLICATE KEY UPDATE

    INSERT INTO users (id, name) 
VALUES (1, 'Alice') 
ON DUPLICATE KEY UPDATE name = ""; -- 保持 name 不变
    • ON DUPLICATE KEY UPDATE 依赖于主键或唯一约束。如果表中没有定义主键或唯一约束，该语句将无法判断重复，会直接插入新记录
    • 如果表中有多个唯一约束，MySQL 会按照第一个触发的唯一约束来判断重复

删除（delete语句）

DELETE 语句是 MySQL 中用于从表中删除数据的语句。它允许根据条件删除一行或多行数据

  • 基本语法

    DELETE FROM table_name
[WHERE condition];
    • DELETE FROM：指定要删除数据的表
    • WHERE：可选，用于指定删除条件。如果省略，则删除表中的所有数据

  • 删除表中的所有数据

    DELETE FROM employees;

    • 删除 employees 表中的所有行，但表结构保留

    • 这种方式会逐行删除数据，性能较低。如果需要快速清空表，可以使用 TRUNCATE TABLE

    • TRUNCATE TABLE employees;

    • TRUNCATE TABLE 会直接删除表的所有数据，且不可回滚

  • 删除时使用子查询，可以在 WHERE 子句中使用子查询来指定删除条件

    DELETE FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
);

  • 删除时使用 LIMIT，LIMIT 子句用于限制删除的行数

    DELETE FROM employees
WHERE salary < 3000
LIMIT 10;
删除 salary 小于 3000 的前 10 行。

  • 删除时使用 ORDER BY，ORDER BY 子句用于指定删除的顺序

    DELETE FROM employees
WHERE salary < 3000
ORDER BY hire_date
LIMIT 10;
删除 salary 小于 3000 且入职时间最早的 10 名员工。

  • 删除时使用别名，可以为表指定别名

    DELETE e
FROM employees e
WHERE e.salary < 3000;
在e表中删除指定条件的记录

  • 删除时使用 JOIN，可以使用 JOIN 删除多个表中的数据

    DELETE e, d
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

删除语句注意事项

  • 删除数据不会重置自增列的值。如果需要重置自增列，可以使用以下语句

    ALTER TABLE employees AUTO_INCREMENT = 1;

  • 删除数据会更新表的索引，可能会影响性能。在大数据量删除时，建议分批删除

  • 不能直接通过视图删除数据，除非视图是单表视图且满足特定条件

  • 在复制环境中，DELETE 操作会记录到二进制日志中，并复制到从库

更新（update语句）

UPDATE 语句是 MySQL 中用于修改表中现有数据的语句。它允许根据条件更新一行或多行数据

  • 基本语法

    • UPDATE：指定要更新的表。
    • SET：指定要更新的列及其新值。
    • WHERE：可选，用于指定更新条件。如果省略，则更新表中的所有行

  • 更新时使用子查询，可以在 SET 或 WHERE 子句中使用子查询

    UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE department_id = 5;

  • 更新时使用 LIMIT和ORDER BY

    LIMIT 子句用于限制更新的行数，ORDER BY 子句用于指定更新的顺序

    UPDATE employees
SET salary = 5000
WHERE department_id = 5
LIMIT 10;
将 department_id 为 5 的前 10 名员工的 salary 更新为 5000

UPDATE employees
SET salary = 5000
WHERE department_id = 5
ORDER BY hire_date
LIMIT 10;
将 department_id 为 5 且入职时间最早的 10 名员工的 salary 更新为 5000

  • 更新时处理外键约束

    • 如果更新的数据被其他表的外键引用，更新操作可能会失败
    • 级联更新：在定义外键时使用 ON UPDATE CASCADE，更新主表数据时自动更新从表数据。
    • 手动处理：先更新从表数据，再更新主表数据

  • 更新时使用 JOIN，可以使用 JOIN 更新多个表中的数据

    UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.salary = 5000, d.budget = 100000
WHERE d.department_name = 'Sales';
将 Sales 部门的员工的 salary 更新为 5000，并将部门的 budget 更新为 100000

where子句

WHERE 子句用于过滤数据，它支持多种 条件运算符 和 逻辑运算符，用于构建复杂的查询条件

条件运算符

条件运算符用于比较列值与指定值或表达式之间的关系

  1. 等于 (=)

  2. 不等于 (!= 或 <>)

  3. 范围 (BETWEEN ... AND ...)判断列值是否在指定范围内（包含边界值）

    SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;

  4. 列表匹配 (IN)，判断列值是否在指定列表中

    SELECT * FROM employees WHERE department_id IN (1, 2, 3);

  5. 模糊匹配 (LIKE)，判断列值是否匹配指定模式

    • 通配符：

      • %：匹配任意长度的任意字符。

      • _：匹配单个任意字符

      SELECT * FROM employees WHERE last_name LIKE 'S%';
返回 last_name 以 S 开头的员工

  6. 空值判断 (IS NULL 和 IS NOT NULL)，判断列值是否为 NULL 或非 NULL

    SELECT * FROM employees WHERE hire_date IS NULL;

逻辑运算符

逻辑运算符用于组合多个条件，构建复杂的查询逻辑

  1. 逻辑与 (AND)，所有条件都必须为真

  2. 逻辑或 (OR)，至少一个条件为真

  3. 逻辑非 (NOT)，否定条件

    SELECT * FROM employees WHERE department_id = 5 AND salary > 3000;

SELECT * FROM employees WHERE department_id = 5 OR salary > 3000;

SELECT * FROM employees WHERE NOT department_id = 5;

  4. ALL和ANY

    • ALL

      • ALL 用于将主查询中的值与子查询返回的所有值进行比较。只有当主查询的值与子查询的所有值都满足比较条件时，才返回 TRUE

      • 如果子查询返回空集，ALL 会返回 TRUE

      • 如果子查询为空集，则主查询返回全部值

        SELECT *
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE job_title = 'Manager'
);
查询工资高于所有部门经理的员工

    • ANY

      • ANY 用于将主查询中的值与子查询返回的任意一个值进行比较。只要主查询的值与子查询的至少一个值满足比较条件，ANY 就返回 TRUE

      • 如果子查询返回空集，ANY 会返回 FALSE，因为没有任何值满足条件

      • 返回 FALSE：由于没有值可以比较，ANY 无法找到满足条件的值，因此最终结果为 FALSE

      • 如果子查询为空集，则主查询不会返回任何值

        SELECT *
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE job_title = 'Manager'
);
查询工资高于任意一个部门经理的员工

  5. EXISTS，判断子查询是否返回结果

    • EXISTS 是 MySQL 中的一个 子查询运算符，用于检查子查询是否返回任何结果。它通常与 WHERE 子句一起使用，用于判断主查询中的某一行是否满足子查询中的条件。EXISTS 返回一个布尔值

      • 如果子查询返回至少一行结果，则 EXISTS 返回 TRUE。

      • EXISTS 找到第一个匹配项后就会停止搜索

      • 如果子查询没有返回任何结果，则 EXISTS 返回 FALSE

        SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);
posted @ 2025-03-17 21:25  QAQ001  阅读(118)  评论(0)    收藏  举报