一、题目:
图书表
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;