SQL实例

一、题目：

图书表

id book_id author_id book_name pages press

奖项表

id book_id author_id cup_type cup_time

作者表

id author_id author_name content

一、设计表，写出建表语句

二、设计索引,写出创建索引的语句

三、完成以下SQL

1. 查询姓王的作者有多少

2. 查询页数最多的前5名作者姓名和书名

3. 查询获奖最多的作者姓名，获奖时间

4. 查询获奖作者总人数

5. 查询最近获奖的一本书名和出版社

6. 查询同时获得过金奖、银奖的作者姓名

7. 查询获得金奖的图书有多少本，银奖的有多少本

8. 查询最近一年内获过奖的作者姓名

9. 查询每位作者各自出版的图书名

#-----**************Starting****************-----

创建作者表

CREATE TABLE tb_author(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '作者自增id',
author_id VARCHAR(20) NOT NULL COMMENT '作者编号',
author_name VARCHAR(20) NOT NULL COMMENT '作者名字',
content varchar(40) NOT NULL COMMENT '作者简介',
INDEX idx_author_name (author_name), 
UNIQUE INDEX idx_author_id (author_id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT '作者表';

创建图书表

CREATE TABLE tb_book(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '图书自增id',
book_id VARCHAR(20) NOT NULL COMMENT '图书编号',
book_name VARCHAR(40) NOT NULL COMMENT '图书姓名',
author_id INT UNSIGNED NOT NULL COMMENT '作者自增编号',
pages INT UNSIGNED NOT NULL COMMENT '图书页数',
press VARCHAR(20) NOT NULL COMMENT '图书出版社',
INDEX idx_book_name (book_name), 
UNIQUE INDEX idx_book_id (book_id),
INDEX idx_author_id (author_id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT '图书表';

创建获奖表

CREATE TABLE t_cap(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '获奖自增id',
book_id INT UNSIGNED COMMENT '图书自增id',
author_id INT UNSIGNED COMMENT '作者自增id',
cup_type VARCHAR(20) NOT NULL COMMENT '奖项类型',
cup_time DATE NOT NULL COMMENT '获奖时间',
INDEX idx_book_id (book_id),
INDEX idx_author_id (author_id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT '获奖表';

# 查询姓王的作者有多少

select count(*)
from t_author
where author_name like '王%';

# 查询页数最多的前5名作者姓名和书名

select author_name,book_name
from t_author inner join t_book on t_author.id = t_book.author_id order by pages desc limit 1;

# 查询获奖最多的作者姓名，获奖时间
# 1、先找到获奖最多的作者id - 先对author_id分组，后对每一个分组进行排序
# 2、根据id去t_author表中查询作者信息

select author_name,cup_time
from t_cap left join t_author on t_author.id = t_cap.author_id
where t_cap.author_id = 
(
select author_id 
from t_cap group by author_id order by count(author_id) desc limit 1 
);

# 查询获奖作者总人数 - 去重复id 

方法一、推荐使用

select count(*)
from ((select author_id from t_cap group by (author_id)) as tb_cup_author);

方法二、

select count(DISTINCT author_id)
from t_cap;

# 查询最近获奖的一本书名和出版社

select book_name,press
from t_cap left join t_book on t_cap.book_id = t_book.id order by cup_time desc limit 1;

# 查询同时获得过金奖、银奖的作者姓名

select author_name
from t_author
where id in (select GoldInfor.author_id from 
(
	(select author_id from t_cap where cup_type = '金奖') As GoldInfor #获得金奖表
		inner join 
	(select author_id from t_cap where cup_type = '银奖') As SilverInfor #获得银奖表
		on GoldInfor.author_id = SilverInfor.author_id)
);

# 查询获得金奖的图书有多少本，银奖的有多少本
方法一：推荐使用

select cup_type,count(id)
from t_cap 
where cup_type in ('金奖','银奖') group by cup_type;

方法二、

select cup_type,count(id)
from t_cap 
where cup_type = '金奖' or cup_type = '银奖' group by cup_type;

# 获得金奖的图书有多少本，银奖的有多少本,并输出获奖个数超过一次的类型

select cup_type,count(id)
from t_cap 
where cup_type = '金奖' or cup_type = '银奖' group by cup_type having count(id) > 1;

# 查询最近一年内获过奖的作者姓名

select author_name
from t_author inner join t_cap on t_author.id = t_cap.author_id
where t_cap.cup_time between date_sub(Now(),interval 1 Year) and Now();

# 查询每位作者各自出版的图书名
# author_name可以使得同一作者在一起输出

select author_name,book_name
from t_book left join t_author on t_book.author_id = t_author.id order by author_name;