Tenormis's Blog

Appearance

MySQL DML ​

Note：DML (Data Manipulation Language)。

1. 变量 ​

1.1. 系统变量 ​

系统变量由 MySQL 服务器提供，而非用户定义的。

  • 查看系统变量：

    SQL
    SHOW GLOBAL VARIABLES;    -- 查看所有系统变量（全局）
SHOW [SESSION] VARIABLES; -- 查看所有系统变量（会话）

SHOW GLOBAL VARIABLES LIKE '...';    -- 查询指定系统变量（全局）
SHOW [SESSION] VARIABLES LIKE '...'; -- 查询指定系统变量（会话）

SELECT @@global.variable_name;    -- 查看指定系统变量（全局）
SELECT @@[session.]variable_name; -- 查看指定系统变量（会话）

  • 设置系统变量：

    SQL
    SET GLOBAL variable_name = [0|1];    -- 设置指定系统变量值（全局）
SET [SESSION] variable_name = [0|1]; -- 设置指定系统变量值（会话）

SET @@global.variable_name = [0|1];    -- 设置指定系统变量值（全局）
SET @@[session.]variable_name = [0|1]; -- 设置指定系统变量值（会话）

1.2. 用户变量 ​

用户变量是由用户根据自己需要定义的，用户变量不需要提前声明。在使用时直接 @变量名 即可，未赋值的变量值为 NULL。作用域为当前会话。

  • 设置用户变量：

    SQL
    SET @variable_name = expr [, @variable_name = expr] ...;
SET @variable_name := expr [, @variable_name := expr] ...;

SELECT @variable_name := expr [, @variable_name := expr] ...;

SELECT field_name INTO @variable_name FROM ...;

  • 使用用户变量：

    SQL
    SELECT @variable_name;

1.3. 局部变量 ​

局部变量是根据需要定义的在局部生效的变量，使用之前需要先进行 DECLARE 声明。局部变量的作用域为在其声明所在的 BEGIN ... END 块。

  • 声明：

    SQL
    DECLARE variable_name variable_type [DEFAULT expr];

  • 赋值：

    SQL
    SET variable_name = expr;
SET variable_name := expr;

SELECT field_name INTO variable_name FROM ...;

2. 流程控制语句 ​

2.1. IF ​

SQL
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

2.2. WHEN ​

SQL
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

或：

SQL
CASE WHEN search_condition THEN statement_list
     [WHEN search_condition THEN statement_list] ...
     [ELSE statement_list]
END CASE

2.3. WHILE/REPEAT/LOOP ​

  • WHILE：

    SQL
    [begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

  • REPEAT：

    SQL
    [begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

  • LOOP：

    SQL
    [begin_label:] LOOP
    statement_list
END LOOP [end_label]

2.4. LEAVE 和 ITERATE ​

  • LEAVE 语句用于立即退出一个循环：

    SQL
    LEAVE label

  • ITERATE 语句用于跳过当前循环的剩余部分，并开始下一次循环迭代：

    SQL
    ITERATE label

2.5. DECLARE ... HANDLER 语句 ​

SQL
DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

handler_action 值表示处理程序在执行处理程序语句后采取的操作：

  • CONTINUE：当前程序的执行继续进行；
  • EXIT：当前程序在声明处理程序的 BEGIN ... END 复合语句中终止执行；

所有 Error Code 可以参阅官方文档。

  • SQLWARNING：所有以 01 开头的 SQLSTATE 代码的简写；
  • NOT FOUND：所有以 02 开头的 SQLSTATE 代码的简写；
  • SQLEXCEPTION：所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的简写；

2.6. 游标 ​

  1. 声明游标

    SQL
    DECLARE cursor_name CURSOR FOR select_statement

    游标声明必须出现在 DECLARE ... HANDLER 声明之前，以及变量和 DECLARE ... CONDITION 声明之后。

  2. 打开游标

    SQL
    OPEN cursor_name

  3. 在循环中获取记录

    SQL
    FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

    如果没有更多的行可用，就会出现 “No Data” 的情况（Condition），其 SQLSTATE 值为 02000。要检测这种情况，你可以为其（或为 NOT FOUND 条件）设置一个处理程序。

  4. 最后关闭游标

    SQL
    CLOSE cursor_name

示例：

SQL
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;

  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

3. 插入 ​

SQL
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { SELECT ... 
      | TABLE table_name 
      | VALUES row_constructor_list
    }
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

assignment:
    col_name = 
          value
        | [row_alias.]col_name
        | [tbl_name.]col_name
        | [row_alias.]col_alias

assignment_list:
    assignment [, assignment] ...

https://dev.mysql.com/doc/refman/8.4/en/insert.html

SQL
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);

4. 更新 ​

SQL
-- Single-Table Syntax

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

-- Multiple-Table Syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

https://dev.mysql.com/doc/refman/8.4/en/update.html

SQL
UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

5. 删除 ​

SQL
-- Single-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

-- Multiple-Table Syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

https://dev.mysql.com/doc/refman/8.4/en/delete.html

SQL
DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;
SQL
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;
SQL
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

6. 查询 ​

SQL
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

https://dev.mysql.com/doc/refman/8.4/en/select.html

SQL
SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;
SQL
SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

6.1. 分页（带总记录数） ​

SQL
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_found-rows

转载请注明作者和出处

Copyright © 2025-至今 Tenormis