insert.md

# 插入

Goods 数据库中有一个表：

```sql
create table book(
    id int primary key auto_increment,
    title varchar(200) not null ,
    description varchar(1000) default '',
    price decimal(12, 4),
    isbn char(16) not null ,
    publish_at date not null 
);
create unique index idx_book_isbn on book(isbn);
```

那么下列哪个选项的代码可以执行成功？

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill){target="_blank"}。

* `show databases;` 列出所有数据库
* `show tables;` 列出所有表

## 答案

```sql
insert into book(title, price, isbn, publish_at) select 'a book title', 25.4, 'xx-xxxx-xxxx', '2019-12-1';
insert into book(title, price, isbn, publish_at) select 'a other book title', 25.4, 'yy-yyyy-xxxx', '2019-12-1'; 
```

## 选项

### 唯一键冲突

```sql
insert into book(title, price, isbn, publish_at) select 'a book title', 25.4, 'xx-xxxx-xxxx', '2019-12-1';
insert into book(title, price, isbn, publish_at) select 'a other book title', 35.4, 'xx-xxxx-xxxx', '2019-12-1'; 
```

### 缺少必要的列

```sql
insert into book(price, isbn, publish_at) select 25.4, 'xx-xxxx-xxxx', '2019-12-1';
insert into book(price, isbn, publish_at) select 35.4, 'yy-yyyy-xxxx', '2019-12-1';
```

### 类型错误

```sql
insert into book(title, price, isbn, publish_at) select 'a book title', 'unknown', 'xx-xxxx-xxxx', '2019-12-1';
insert into book(title, price, isbn, publish_at) select 'a other book title', 'unknown', 'xx-xxxx-xxxx', '2019-12-1';
```

### 违反非空约束

```sql
insert into book(title, price, isbn, publish_at) select null, 'unknown', 'xx-xxxx-xxxx', '2019-12-1';
insert into book(title, price, isbn, publish_at) select null, 'unknown', 'xx-xxxx-xxxx', '2019-12-1';
```