MySQL之insert、replace、ignore、update操作mysql> show create table t
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t;
Empty set (0.00 sec)
mysql>
insert into t(id, num)values(1, 100);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t;
+----+------+
| id | num |
+----+------+
| 1 | 100 |
+----+------+
1 row in set (0.00 sec)
(1)ignore
mysql>
insert ignore into t(id, num)values(1, 101);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+------+
| id | num |
+----+------+
| 1 | 100 |
+----+------+
1 row in set (0.00 sec)
mysql>
(2)replace
mysql>
replace into t(id, num)values(1, 101);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from t;
+----+------+
| id | num |
+----+------+
| 1 | 101 |
+----+------+
1 row in set (0.00 sec)
(3)duplicate key update
mysql>
insert into t(id, num)values(1, 101) on duplicate key update num = num + 10;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from t;
+----+------+
| id | num |
+----+------+
| 1 | 111 |
+----+------+
1 row in set (0.01 sec)
mysql> 有点意思
mysql>
insert into t(id, num)values(1, 101) on duplicate key update num = values(num);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from t;
+----+------+
| id | num |
+----+------+
| 1 | 101 |
+----+------+
1 row in set (0.00 sec)
num = num +1是把id重复的哪条记录的num加了1
num = values(num)⽤插⼊的num替换掉id重复的记录的num
mysql>
insert into t(id, num)values(1, 101),(1,102),(1,103) on duplicate key update num = values(num); Query OK, 4 rows affected (0.00 sec)
Records: 3 Duplicates: 2 Warnings: 0
mysql> select * from t;
+----+------+
| id | num |
+----+------+
| 1 | 103 |
+----+------+
1 row in set (0.00 sec)
多条的时候，以此类推。
on duplicate key update最⼤的作⽤就是可以实现：不存在则插⼊，存在则更新！
=================
MySQL增删改查之增insert、replace
⼀、INSERT语句
带有values⼦句的insert语句，⽤于数据的增加
语法：
INSERT [INTO] tbl_name[(col_name,...)]
{VALUES | VALUE} (expr ,...),(...),...
insert语句字段顺序 ①⽤来把⼀个新⾏插⼊到表中
②为和其它数据库保持⼀致，不要省略INTO关键字以及使⽤VALUES⽽不是value关键字
③插⼊⼀⾏时，要求必须对该⾏所有的列赋值。但是赋值⽅式可以是显式赋值（直接给出值）和隐式赋值（由MySQL⾃动赋值）
基本格式：
insert into <；表名> (列名1，列名2，列名3,......) values (值1，值2，值3,......);
解析：
①(列名1，列名2，列名3,......)称为列清单
②(值1，值2，值3,......)称为值清单
③列清单和值清单中的列和值必须相互对应(数据类型)
1、在表名后⾯列出所有的列名
⽰例：插⼊⼀个新的球队到teams表中
mysql> INSERT INTO teams(teamno,playerno,division) VALUES(3,6,'third');
2、在表名后⾯省略所有的列名
要求：VALUES⼦句中的值必须按照列在表结构中的顺序来⼀⼀赋值
mysql> INSERT INTO teams VALUES(4,104,'third');
3、在表名后⾯只列出部分的列名
注意：所有没有明确赋值的列，将通过隐式赋值⾃动得到null值
mysql> insert into <；表名> (列名1，列名3......) values (值1，值3,......);
添加⼀个新⾏数据，没有指定赋值的列，结果集改⾏数据的列名2处隐式赋null值
当然，可使⽤字⾯量NULL给列赋空值
mysql> insert into <；表名> (列名1，列名2，列名3) values (值1,null，值3);
4、VALUES⼦句中除了字⾯量，还可以使⽤函数、计算、标量⼦查询等
INSERT INTO <；表名1>(列1，列2)
VALUES((SELECT count(*) FROM 表名2),
(SELECT sum(列名) FROM 表名3));
注意：⼦查询必须放在单独的⼩括号中
⼀条INSERT语句可以插⼊多个⾏：
⽰例：添加4个新的球队
INSERT INTO teams(teamno, captainno,division)
VALUES (6,7,'third'),
(7,27,'fourth'),
(8,39,'fourth'),
(9,112,'sixth');
注意：这种语法只要有1⾏出错，则插⼊全部取消
INSERT语句中可以使⽤IGNORE选项来当INSERT语句出错时，不显⽰错误消息：但是INSERT语句不会执⾏
5、从其他表中复制数据：带⼦查询的INSERT语句，实现从其他表中(可按条件)复制数据进⼊⽬标表
语法：
INSERT [INTO] tbl_name[(col_name,...)]
SELECT ...
注意：
①如果在表名后⾯列出了列名，那么列的数量和数据类型必须和⼦查询的select列表相匹配
②如果在表名后⾯没有列出了列名，默认就是直接复制
< 语句常在需要进⾏数据表的备份时使⽤
⽆条件复制：将⼀个表中的原始数据记录，插⼊到另⼀个表中
按条件copy：将⼀个表查询出来的进⾏聚合操作之后的记录，插⼊到⽬标表中
当然，可以把本表中的⾏再次添加到本表中，但是必须注意主键值不要重复。
⼆、REPLACE语句：替代已有的⾏
INSERT语句的⼀个变种；
当添加新⾏时：
①如果主键值重复，那么就覆盖表中已有的⾏
②如果没有主键值重复，则插⼊该⾏
语法：
REPLACE [INTO] tbl_name [(col_name,...)]
VALUES (expr,...),(...),...
或者
REPLACE [INTO] tbl_name [(col_name,...)]
SELECT ...
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论