DML语句之增、删、改

增加 insert

INSERT的三种方式

格式一：

Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]


INSERT INTO tb_name (col1,col2....) VALUES (value1,value2...), (value1,value2...)；

mysql> insert into student (name,cid,gender,age,cid2) values ('suke',4,'M',21,3),
    -> ('beita',3,'M',24,1),
    -> ('wukong',5,'M',25,2),
    -> ('wujing',1,'M',28,null);
Query OK, 4 rows affected (0.33 sec)
Records: 4  Duplicates: 0  Warnings: 0

格式二：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

mysql> insert into student set name='wuneng',cid=4,gender='F',age=30;
Query OK, 1 row affected (0.03 sec)

格式三：

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

mysql> insert into teacher (name,age) select name,age from student where age > 20;
Query OK, 10 rows affected (0.02 sec)
Records: 10  Duplicates: 0  Warnings: 0

REPLACE

REPLACE和INSERT的格式是一样的，REPLACEkey判断是否重复，如果重复不插入，如果不重复，可以插入新的数据。

删除数据

显示表中的最后一行，显示自动增长的INSERT ID

mysql> select * from stu_test order by id desc limit 1;
+----+------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+------+-----+--------+-----+------+
| 17 | tata |   3 | F      |  33 | NULL |
+----+------+-----+--------+-----+------+
1 row in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               17 |
+------------------+
1 row in set (0.00 sec)

DELETE

DELETE FROM tb_name WHERE condition

mysql> delete from stu_test;
Query OK, 16 rows affected (0.02 sec)

使用delete语句无法删除last_insert_id，所以重新插入数据时，会接着上次的计数器插入。

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               17 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into stu_test (name,cid,gender,age) values ('tata',3,'F',33);
Query OK, 1 row affected (0.02 sec)

mysql> select * from stu_test;
+----+------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+------+-----+--------+-----+------+
| 18 | tata |   3 | F      |  33 | NULL |
+----+------+-----+--------+-----+------+
1 row in set (0.00 sec)

TRUNCATE

TRUNCATE tb_name； 清空表，并重置autoincrement计数器

mysql> truncate stu_test;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into stu_test (name,cid,gender,age) values ('tata',3,'F',33);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu_test;
+----+------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+------+-----+--------+-----+------+
|  1 | tata |   3 | F      |  33 | NULL |
+----+------+-----+--------+-----+------+
1 row in set (0.00 sec)

修改数据

UPDATE

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

mysql> update stu_test set name='lina' where id =1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0