第22课 [番外]插入数据的两种方式

第一种方式 insert into

第一种方式比较简单, 前面已经介绍过, 直接上代码

CREATE TABLE test (
    id INT auto_increment PRIMARY KEY,
    NAME VARCHAR ( 20 ) NOT NULL,
    account INT NOT NULL,
    age INT NOT NULL,
    sex CHAR ( 1 ) NOT NULL 
);

INSERT INTO `test` ( `id`, `name`, `account`, `age`, `sex` )
VALUES
    ( 1, '张三', 3000, 18, '男' );
INSERT INTO `test` ( `id`, `name`, `account`, `age`, `sex` )
VALUES
    ( 2, '李四', 4000, 28, '男' );
INSERT INTO `test` ( `id`, `name`, `account`, `age`, `sex` )
VALUES
    ( 3, '王五', 5000, 38, '男' );
INSERT INTO `test` ( `id`, `name`, `account`, `age`, `sex` )
VALUES
    ( 4, '赵六', 6000, 48, '男' );
INSERT INTO `test` ( `id`, `name`, `account`, `age`, `sex` )
VALUES
    ( 5, '孙七', 2000, 19, '男' );
INSERT INTO `test` ( `id`, `name`, `account`, `age`, `sex` )
VALUES
    ( 6, '周八', 1000, 29, '男' );
INSERT INTO `test` ( `id`, `name`, `account`, `age`, `sex` )
VALUES
    ( 7, '吴老九', 9000, 39, '男' );
INSERT INTO `test` ( `id`, `name`, `account`, `age`, `sex` )
VALUES
    ( 8, '冯老十', 8000, 49, '男' );

image.png

第二种方式 类似于update

如果更新数据需要update
代码如下:

UPDATE `test` 
SET 
`name` = '张三',
`account` = 3000,
`age` = 18,
`sex` = '男' 
WHERE
    `id` = 1;

我们的第二种插入数据的方式和update很像

INSERT INTO `test` 
SET 
    `id` = 11,
    `name` = '张三',
    `account` = 3000,
    `age` = 18,
    `sex` = '男';
    
INSERT INTO `test` 
SET 
    `id` = 12,
    `name` = '李四',
    `account` = 4000,
    `age` = 28,
    `sex` = '男';
    
INSERT INTO `test` 
SET 
    `id` = 13,
    `name` = '王五',
    `account` = 5000,
    `age` = 38,
    `sex` = '男';
    
INSERT INTO `test` 
SET 
    `id` = 14,
    `name` = '赵六',
    `account` = 6000,
    `age` = 48,
    `sex` = '男';
    
INSERT INTO `test` 
SET 
    `id` = 15,
    `name` = '孙七',
    `account` = 2000,
    `age` = 19,
    `sex` = '男';
    
INSERT INTO `test` 
SET 
    `id` = 16,
    `name` = '周八',
    `account` = 1000,
    `age` = 29,
    `sex` = '男';
    
INSERT INTO `test` 
SET 
    `id` = 17,
    `name` = '吴老九',
    `account` = 9000,
    `age` = 39,
    `sex` = '男';
    
INSERT INTO `test` 
SET 
    `id` = 18,
    `name` = '冯老十',
    `account` = 8000,
    `age` = 49,
    `sex` = '男';

image.png

两者之间的区别

1. 第一种方式支持一次插入多条

INSERT INTO `test`(`id`, `name`, `account`, `age`, `sex`) 
VALUES 
(1, '张三', 3000, 18, '男'),
(2, '李四', 4000, 28, '男'),
(3, '王五', 5000, 38, '男'),
(4, '赵六', 6000, 48, '男'),
(5, '孙七', 2000, 19, '男'),
(6, '周八', 1000, 29, '男'),
(7, '吴老九', 9000, 39, '男'),
(8, '冯老十', 8000, 49, '男');

2. 更加逆天的是, 还支持子查询....

insert into test (`name`,`account`,`sex`,`age`)
select `name`,`account`,`sex`,`age` from test;

把test表中的name,account,sex,age字段的值复制, 然后重新写入test表中, 因为id是自动递增的, 所以能够写入成功

image.png