INSERT INTO
用於向表中插入資料,遇到主鍵重複時會自動忽略當前寫入資料,不做更新,作用等同於INSERT IGNORE INTO
。
文法
INSERT [IGNORE]
INTO table_name
[( column_name [, …] )]
[VALUES]
[(value_list[, …])]
[query];
參數
IGNORE
:選擇性參數,若系統中已有相同主鍵的記錄,新記錄不會被寫入。column_name
:選擇性參數,列名。query
:通過定義查詢,將一行或多行資料插入表中。
注意事項
如果插入資料時不指定列名,則要插入的資料必須和CREATE TABLE語句中聲明的列的順序一致。
樣本
建立CUSTOMER和COURSES表。
CREATE TABLE customer (
customer_id bigint NOT NULL COMMENT '顧客ID',
customer_name varchar NOT NULL COMMENT '顧客姓名',
phone_num bigint NOT NULL COMMENT '電話',
city_name varchar NOT NULL COMMENT '所屬城市',
sex int NOT NULL COMMENT '性別',
id_number varchar NOT NULL COMMENT '社會安全號碼碼',
home_address varchar NOT NULL COMMENT '家庭住址',
office_address varchar NOT NULL COMMENT '辦公地址',
age int NOT NULL COMMENT '年齡',
login_time timestamp NOT NULL COMMENT '登入時間',
PRIMARY KEY (login_time,customer_id,phone_num)
)
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT '客戶資訊表';
CREATE TABLE courses(
id bigint AUTO_INCREMENT PRIMARY KEY,
name varchar(20) NOT NULL,
grade varchar(20) default '三年級',
submission_date timestamp
)
DISTRIBUTED BY HASH(id)
向CUSTOMER表中插入一條資料。
INSERT INTO customer(customer_id,customer_name,phone_num,city_name,sex,id_number,home_address,office_address,age,login_time) values (002367,'楊過','13900001234','杭州',0,'987300','西湖','轉塘雲棲小鎮',23,'2018-03-02 10:00:00');
使用
INSERT INTO ... ON DUPLICATE KEY UPDATE...
向CUSTOMER表中插入一條資料。INSERT INTO customer values (002367,'楊過','13900001234','杭州',0,'987300','西湖','轉塘雲棲小鎮',23,'2018-03-02 10:00:00'); ON DUPLICATE KEY UPDATE age=23;
向CUSTOMER表中插入多條資料。
INSERT INTO customer(customer_id,customer_name,phone_num,city_name,sex,id_number,home_address,office_address,age,login_time) values (002367,'李四','13900001234','杭州',0,'987300','西湖','轉塘雲棲小鎮',23,'2018-03-02 10:00:00'),(002368,'張三','13900001111','杭州',0,'987300','西湖','轉塘雲棲小鎮',28,'2018-08-01 11:00:00'),(002369,'王五','13900002222','杭州',1,'987300','西湖','轉塘雲棲小鎮',35,'2018-09-12 08:11:00');
向CUSTOMER表中插入多條資料時,可以省略列名。
INSERT INTO customer values (002367,'李四','13900001234','杭州',0,'987300','西湖','轉塘雲棲小鎮',23,'2018-03-02 10:00:00'),(002368,'張三','13900001111','杭州',0,'987300','西湖','轉塘雲棲小鎮',28,'2018-08-01 11:00:00'),(002369,'王五','13900002222','杭州',1,'987300','西湖','轉塘雲棲小鎮',35,'2018-09-12 08:11:00');
向COURSES表中插入一條資料。
INSERT INTO courses (name,submission_date) values("Jams",NOW());
INSERT query
樣本請參見INSERT SELECT FROM。