第一種方式 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, '男' );
複製程式碼第二種方式 類似於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` = '男';
複製程式碼兩者之間的區別
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是自動遞增的, 所以能夠寫入成功