全部产品

INSERT INTO

更新时间:2019-07-02 21:36:47

INSERT INTO用于向表中插入数据,遇到主键重复时会自动忽略当前写入数据,不做更新,作用等同于INSERT IGNORE INTO

语法

  1. INSERT [IGNORE]
  2. INTO table_name
  3. [( column_name [, …] )]
  4. [VALUES]
  5. [(value_list[, …])]
  6. [query];

参数

  • IGNORE:可选参数,若系统中已有相同主键的记录,新记录不会被写入。

  • column_name:可选参数,列名。

  • query:通过定义查询,将一行或多行数据插入表中。

注意事项

如果插入数据时不指定列名,则要插入的数据必须和CREATE TABLE语句中声明的列的顺序一致。

示例

创建CUSTOMER和COURSES表。

  1. CREATE TABLE customer (
  2. customer_id bigint NOT NULL COMMENT '顾客ID',
  3. customer_name varchar NOT NULL COMMENT '顾客姓名',
  4. phone_num bigint NOT NULL COMMENT '电话',
  5. city_name varchar NOT NULL COMMENT '所属城市',
  6. sex int NOT NULL COMMENT '性别',
  7. id_number varchar NOT NULL COMMENT '身份证号码',
  8. home_address varchar NOT NULL COMMENT '家庭住址',
  9. office_address varchar NOT NULL COMMENT '办公地址',
  10. age int NOT NULL COMMENT '年龄',
  11. login_time timestamp NOT NULL COMMENT '登录时间',
  12. PRIMARY KEY (login_timecustomer_idphone_num)
  13. )
  14. DISTRIBUTED BY HASH(customer_id)
  15. PARTITION BY VALUE(YEARMONTHDAY(login_time)) PARTITIONS 30
  16. COMMENT '客户信息表';
  1. CREATE TABLE courses(
  2. id bigint AUTO_INCREMENT PRIMARY KEY,
  3. name varchar(20) NOT NULL,
  4. grade varchar(20) default '三年级',
  5. submission_date timestamp
  6. )
  7. DISTRIBUTED BY HASH(id)
  • 向CUSTOMER表中插入一条数据。

    1. INSERT INTO customer(customer_id,customer_name,phone_num,city_name,sex,id_number,home_address,office_address,age,login_time)
    2. values
    3. (002367,'杨过','13678973421','杭州',0,'987300','西湖','转塘云栖小镇',23,'2018-03-02 10:00:00');
  • 向CUSTOMER表中插入多条数据。

    1. INSERT INTO customer(customer_id,customer_name,phone_num,city_name,sex,id_number,home_address,office_address,age,login_time)
    2. values
    3. (002367,'李四','13678973421','杭州',0,'987300','西湖','转塘云栖小镇',23,'2018-03-02 10:00:00'),(002368,'张三','13878971234','杭州',0,'987300','西湖','转塘云栖小镇',28,'2018-08-01 11:00:00'),(002369,'王五','13968075284','杭州',1,'987300','西湖','转塘云栖小镇',35,'2018-09-12 08:11:00');
  • 向CUSTOMER表中插入多条数据时,可以省略列名。

    1. INSERT INTO
    2. customer values(002367,'李四','13678973421','杭州',0,'987300','西湖','转塘云栖小镇',23,'2018-03-02 10:00:00'),(002368,'张三','13878971234','杭州',0,'987300','西湖','转塘云栖小镇',28,'2018-08-01 11:00:00'),(002369,'王五','13968075284','杭州',1,'987300','西湖','转塘云栖小镇',35,'2018-09-12 08:11:00');
  • 向COURSES表中插入一条数据。

    1. insert into courses (name,submission_date) values("Jams",NOW());
  • INSERT query示例请参见INSERT SELECT FROM