MySQL Data Manipulation Statements

1.CALL Syntax
调用PROCEDURE

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+--------------------+------------+
| @version           | @increment |
+--------------------+------------+
| 8.0.3-rc-debug-log |         11 |
+--------------------+------------+

2.DELETE 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]

3.DO Syntax
执行语句,但没有返回结果(select有返回结果),比select快,用在没有返回的语句比较好

mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        0 |
+----------+
1 row in set (5.02 sec)

mysql> DO SLEEP(5);
Query OK, 0 rows affected (4.99 sec)

4.HANDLER Syntax
HANDLER命令，而此命令非SQL标准语法，可以降低优化器对于SQL语句的解析与优化开销，从而提升查询性能。

5.IMPORT TABLE Syntax

6.INSERT Syntax
1)常用方法

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

2)结合select

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

3)有则更新,无则插入

# a is declared as UNIQUE and contains the value 1
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

7.LOAD DATA Syntax
导入数据

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

8.LOAD XML Syntax

mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml

9.REPLACE Syntax
和insert语句类似,不同的是如果主键或者唯一索引存在,则会删除旧数据

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

10.SELECT Syntax(JOIN Syntax,UNION Syntax)

11 Subquery Syntax

12 UPDATE Syntax

UPDATE t1 SET col1 = col1 + 1;

13 WITH Syntax (Common Table Expressions)
用于子查询,相当于将一个查询的结果存起来,供其他语句来使用

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

参考:
https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-data-manipulation.html