SQL语句之：DML

DML语句

INSERT，DELETE，UPDATE

INSERT：

一次插入一行或多行数据

• 语法1：

  INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name [(col_name,...)]
      {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
      [ ON DUPLICATE KEY UPDATE
        col_name=expr
          [, col_name=expr] ... ]
  

  示例：

  MariaDB [test]> INSERT students VALUES(1,'sunwukong',72,'m','CHINA');
  Query OK, 1 row affected (0.00 sec)
    
  MariaDB [test]> INSERT students(name,age) VALUES('zhubajie',45);                
  Query OK, 1 row affected (0.00 sec)
  
  MariaDB [test]> INSERT students(name,age,gender) VALUES('xiaolongnv',25,'f'),('huangrong',28,'f');
  Query OK, 2 rows affected (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  
  MariaDB [test]> SELECT * FROM students;                                         
  +----+------------+-----+--------+---------+
  | id | name       | age | gender | address |
  +----+------------+-----+--------+---------+
  |  1 | sunwukong  |  72 | m      | CHINA   |
  |  2 | zhubajie   |  45 | m      | CHINA   |
  |  3 | xiaolongnv |  25 | f      | CHINA   |
  |  4 | huangrong  |  28 | f      | CHINA   |
  +----+------------+-----+--------+---------+
  4 rows in set (0.00 sec)  
  

• 语法2：

  INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name
      SET col_name={expr | DEFAULT}, ...
      [ ON DUPLICATE KEY UPDATE
        col_name=expr
          [, col_name=expr] ... ]
  

  示例：

  MariaDB [test]> INSERT students set name='guojing',age=37,gender='m';
  Query OK, 1 row affected (0.00 sec)
  

• 语法3：

  INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name [(col_name,...)]
      SELECT ...
      [ ON DUPLICATE KEY UPDATE
        col_name=expr
          [, col_name=expr] ... ]
  

  示例：

  MariaDB [test]> INSERT teachers(name,age,gender,address) SELECT name,age,gender,address FROM students WHERE gender='m'; 
  Query OK, 3 rows affected (0.00 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  MariaDB [test]> SELECT * FROM teachers;
  +----+-----------+-----+--------+---------+
  | id | name      | age | gender | address |
  +----+-----------+-----+--------+---------+
  |  1 | sunwukong |  72 | m      | CHINA   |
  |  2 | zhubajie  |  45 | m      | CHINA   |
  |  3 | guojing   |  37 | m      | CHINA   |
  +----+-----------+-----+--------+---------+
  3 rows in set (0.00 sec)
  

UPDATE

• 语法：

  UPDATE [LOW_PRIORITY] [IGNORE] table_reference
      SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]
  

  示例：

  MariaDB [test]> UPDATE students set name='zhangwuji',age=30 WHERE id=3;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  MariaDB [test]> SELECT * FROM students;                                
  +----+------------+-----+--------+---------+
  | id | name       | age | gender | address |
  +----+------------+-----+--------+---------+
  |  1 | sunwukong  |  72 | m      | CHINA   |
  |  2 | zhubajie   |  45 | m      | CHINA   |
  |  3 | zhangwuji  |  30 | m      | CHINA   |
  |  4 | xiaolongnv |  25 | f      | CHINA   |
  |  5 | huangrong  |  28 | f      | CHINA   |
  |  6 | guojing    |  37 | m      | CHINA   |
  +----+------------+-----+--------+---------+
  6 rows in set (0.01 sec)
  

注意：
一定要有限制条件，否则将修改所有行的指定字段
限制条件：WHERE
     LIMIT

安全保护机制

• Mysql 选项：
  --safe-updates | --i-am-a-dummy | -U
• 写入配置文件/etc/my.cnf

  [mysql]
  safe-updates
  

DELETE

• 单表删除：

  DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]
  

  示例：

  MariaDB [test]> DELETE FROM teachers WHERE id=2;
  Query OK, 1 row affected (0.00 sec)
  

• 多表删除1：

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

• 多表删除2：

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

注意：

• 一定要有限制条件，否则将清空表中的所有数据
  限制条件：WHERE
       LIMIT
• 多表删除十分危险，使用需谨慎

• 清空表：
  mysql> TRUNCATE TABLE tbl_name;