1.6 MariaDB 服务端命令（DML-DELETE、UPDATE基本用法）

 

DELETE：操作对象是行，必须要指定WHERE子句，否则将会删除整张表；
UPDATE：操作对象是行，必须要指定WHERE子句，否则将会更新表中的指定字段对应的所有数据；

DELETE语法结构：
====================

Single-table syntax:
————————–
DELETE  [LOW_PRIORITY]  [QUICK]  [IGNORE]
         FROM  tbl_name [PARTITION  (partition_list)]
         [WHERE  where_condition]
         [ORDER  BY …]
         [LIMIT  row_count]
         [RETURNING  select_expr
             [, select_expr …]]

Multiple-table syntax:
—————————–
DELETE  [LOW_PRIORITY]  [QUICK]  [IGNORE]
            tbl_name[.*] [, tbl_name[.*]] …
           FROM  table_references
           [WHERE  where_condition]

Or:
———-
DELETE  [LOW_PRIORITY]  [QUICK]  [IGNORE]
         FROM  tbl_name[.*] [, tbl_name[.*]] …
         USING  table_references
         [WHERE  where_condition]

Trimming history:
———————–
DELETE  HISTORY
         FROM  tbl_name  [PARTITION (partition_list)]
         [BEFORE  SYSTEM_TIME  [TIMESTAMP | TRANSACTION]  expression]

UPDATE语法结构：
====================

Single-table syntax:
————————–
UPDATE  [LOW_PRIORITY]  [IGNORE]  table_reference
         [PARTITION (partition_list)]
         SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] …
         [WHERE  where_condition]
         [ORDER BY …]
         [LIMIT  row_count]

Multiple-table syntax:
—————————
UPDATE [LOW_PRIORITY]  [IGNORE] table_references
        SET  col1={expr1|DEFAULT}  [, col2={expr2|DEFAULT}] …
        [WHERE  where_condition]

举例：

From  MariaDB  10.3.2, the  statement  executes  successfully:
————————————————————————————-

DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (10,10), (20,20);


UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);

SELECT * FROM t1;
+------+------+
| c1   | c2   |
+------+------+
|   10 |   10 |
|   21 |   20 |
+------+------+

From  MariaDB  10.3.2,  ORDER  BY  and  LIMIT  can  be  used  in  a  multi-table  update:
—————————————————————————————————————————–

CREATE TABLE warehouse (product_id INT, qty INT);
INSERT INTO warehouse VALUES (1,100),(2,100),(3,100),(4,100);

CREATE TABLE store (product_id INT, qty INT);
INSERT INTO store VALUES (1,5),(2,5),(3,5),(4,5);

UPDATE warehouse,store SET warehouse.qty = warehouse.qty-2, store.qty = store.qty+2 
  WHERE (warehouse.product_id = store.product_id AND store.product_id  >= 1) 
    ORDER BY store.product_id DESC LIMIT 2;

SELECT * FROM warehouse;
+------------+------+
| product_id | qty  |
+------------+------+
|          1 |  100 |
|          2 |  100 |
|          3 |   98 |
|          4 |   98 |
+------------+------+

SELECT * FROM store;
+------------+------+
| product_id | qty  |
+------------+------+
|          1 |    5 |
|          2 |    5 |
|          3 |    7 |
|          4 |    7 |
+------------+------+