DML：INSERT, DELETE, UPDATE

1、INSERT 语句

# 功能：一次插入一行或多行数据
# 语法：
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] ... ]
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] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
     col_name=expr
        [, col_name=expr] ... ]

# 简化写法：
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

# 全值插入
mysql> insert students values(1,'wangning',18,default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
|  1 | wangning |   18 | M      |
+----+----------+------+--------+
1 row in set (0.00 sec)

# 部分列插入
mysql> insert students (name,age)values('ailun',19);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
|  1 | wangning |   18 | M      |
| 13 | ailun    |   19 | M      |
+----+----------+------+--------+
2 rows in set (0.00 sec)

2、UPDATE 语句

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


# 一定要有限制条件，否则将修改所有行的指定字段
# 可利用mysql 选项避免此错误：
mysql -U | --safe-updates| --i-am-a-dummy

vim /etc/my.cnf
[mysql]
safe-updates

3、DELETE 语句

# 删除表中数据，但不会自动缩减数据文件的大小。

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

# 可先排序再指定删除的行数
# 一定要有限制条件，否则将清空表中的所有数据

# 如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。
TRUNCATE TABLE tbl_name;

# 缩减表大小
OPTIMIZE TABLE tb_name