今天小编给大家分享一下MySQL怎么实现数据插入操作的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下吧。
一、方法分类
二、具体方法
使用场景 | 作用 | 语句 | 注意 |
---|---|---|---|
常规插入 | 忽略字段名 | insert into 表名 values (值1, 值2,...,值n) | 默认value中的值依次填充所有字,如果出现唯一性冲突,就会抛出异常 |
按照字段插入 | insert into 表名(字段1, 字段2,...,字段n) values (值1, 值2,...,值n) | 字段和值一一对应 | |
一次性插入多条数据 | insert into 表名(字段1, 字段2,...,字段n) values (值a1, 值a2,...,值an),(值b1, 值b2,...,值bn) | 多行之间用逗号隔开,不需要再次写insert into语句 | |
从另一个表导入 | 导出A表的某些数据插入到B表 | insert into 表名B(字段B1, 字段B2,...,字段Bn) select 字段A1, 字段A2,...,字段An from 表名A where [执行条件] | 字段A和B可以字段名称不一样,但是数据类型必须一致 |
插入时数据重复 | 如果记录存在报错 | insert into 表名(字段1, 字段2,...,字段n) values (值1, 值2,...,值n) | 如果插入的数据记录存在,报错并捕获异常,不存在则直接新增记录 |
如果记录存在不插入记录 | insert ignore into 表名(字段1, 字段2,...,字段n) values (值1, 值2,...,值n) | 如果插入的数据记录存在就保存旧记录忽略新记录,不存在则直接新增记录 | |
不论记录是否存在都要插入记录 | replace 表名(字段1, 字段2,...,字段n) values (值1, 值2,...,值n) | 如果插入的数据记录存在就先删除再更新,不存在则直接新增记录 | |
如果记录存在更新指定字段 | insert into … on duplicate key update | 如果插入的数据记录存在就更新指定字段,不存在则直接新增记录 |
三、实例
students 表 (id表示主键,name是姓名,score是平均成绩)
id | name | score |
---|---|---|
1 | 李明 | 67 |
(1)常规插入
忽略字段名
执行后结果
id | name | score |
---|---|---|
1 | 李明 | 67 |
2 | 张三 | 74 |
按照字段插入
insert into
students(name)
values('孙华');
执行后结果
id | name | score |
---|---|---|
1 | 李明 | 67 |
2 | 张三 | 74 |
3 | 孙华 |
|
一次性插入多条数据
insert into
students(name, score)
values('刘平', '56'),('周雨', '90');
执行后结果
id | name | score |
---|---|---|
1 | 李明 | 67 |
2 | 张三 | 74 |
3 | 孙华 |
|
4 | 刘平 | 56 |
5 | 周雨 | 90 |
(2)从另一个表导入
students 表 (id表示主键,name是姓名,score是平均成绩)
id | user_name | mobile_phone_number |
---|---|---|
1 | 马化腾 | 13800000000 |
2 | 任正非 | 13800000011 |
3 | 马云 | 13800000022 |
导出users表的某些数据插入到students表
insert into
students(name,score)
select
user_name,
mobile_phone_number
from users where id <> 3;
执行结果
id | name | score |
---|---|---|
1 | 李明 | 67 |
2 | 张三 | 74 |
3 | 孙华 |
|
4 | 刘平 | 56 |
5 | 周雨 | 90 |
6 | 马化腾 | 13800000000 |
7 | 马云 | 13800000022 |
注意:只要对应字段的类型一样,字段不一样也可以导入数据,不会冲突。
(3)插入时数据重复
如果记录存在报错
insert into
students
values(1, '张三', '74');
执行结果: 报错
Duplicate entry '1' for key 'PRIMARY'
如果记录存在不插入记录
insert ignore into
students(id,name,score)
values(1, '张三', '74');
执行结果:不插入不报错
Affected rows:0
id | name | score |
---|---|---|
1 | 李明 | 67 |
2 | 张三 | 74 |
3 | 孙华 |
|
4 | 刘平 | 56 |
5 | 周雨 | 90 |
6 | 马化腾 | 13800000000 |
7 | 马云 | 13800000022 |
不论记录是否存在都要插入记录
replace
students
values(1, '张三', '74');
执行结果
id | name | score |
---|---|---|
1 | 张三 | 74 |
2 | 张三 | 74 |
3 | 孙华 |
|
4 | 刘平 | 56 |
5 | 周雨 | 90 |
6 | 马化腾 | 13800000000 |
7 | 马云 | 13800000022 |
如果记录存在更新指定字段
insert into
students(id)
values(1) on duplicate key
update
name = '李明',
score = '67';
执行结果
id | name | score |
---|---|---|
1 | 李明 | 67 |
2 | 张三 | 74 |
3 | 孙华 |
|
4 | 刘平 | 56 |
5 | 周雨 | 90 |
6 | 马化腾 | 13800000000 |
7 | 马云 | 13800000022 |
创建 students 表的代码
-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`score` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '李明', '67');
创建 users 表的代码
-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
`mobile_phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号码',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `users` VALUES (1, '马化腾', '13800000000');
INSERT INTO `users` VALUES (2, '任正非', '13800000011');
INSERT INTO `users` VALUES (3, '马云', '13800000022');
四、注意事项
(1)不写字段名,需要填充自增ID
[使用]:0或者null或者default,自增id默认从1开始。
[使用]:或者没有在自增id中出现的(不重复)数(例如-1,-2),浮点型数据例如3.4,最后显示3,会进行四舍五入。即使定义了int类型,输入‘3’或者浮点型,都会强制转化为int类型,但是输入'a'会报错。具体细节可以看源码。
问题:第一个字段id为什么可以写null?如果建表的时候写了id为自增id,而写0或者null或者default或者没有在自增id中出现的(不重复)数(例如-1,-2),系统都会自动填充id。如果建表的时候没有写明是自增id,那么主键一定是不能为空的,这个时候写null就会报错。
(2)按字段名填充,可以不录入id
[注意]:字段要与值一一对应。
其余注意事项
-
字段名可以省略,默认所有列;
-
录入值的类型和字段的类型要一致或兼容;
-
字段和值的个数必须一致。不能出现一行记录5个值,另外一行6个值的情况;
-
如果写了字段,即使是空值也不能空着,用null代替。