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';
```