创建数据库test01_library

create database if not exists test01_library character set 'utf8';

创建表 books，表结构如下：

use test01_library;
create table if not exists books (
    id int,
    `name` varchar(50),
    `authors` varchar(100),
    price float,
    pubdate year,
    note varchar(100),
    num int
);

向books表中插入数据

要求：

• 不指定字段名称，插入第一条记录
• 指定所有字段名称，插入第二记录
• 同时插入多条记录（剩下的所有记录) ```sql use test01_library;

insert into books values (1, ‘Tal of AAA’, ‘Dickes’, 23, ‘1995’, ‘novel’, 11);

insert into books(id,name,authors,price,pubdate,note,num) values(2,’EmmaT’,’Jane lura’,35,’1993’,’joke’,22);

insert into books(id,name,authors,price,pubdate,note,num) values (3,’Story of Jane’,’Jane Tim’,40,2001,’novel’,0), (4,’Lovey Day’,’George Byron’,20,2005,’novel’,30), (5,’Old land’,’Honore Blade’,30,2010,’Law’,0), (6,’The Battle’,’Upton Sara’,30,1999,’medicine’,40), (7,’Rose Hood’,’Richard haggard’,28,2008,’cartoon’,28);

<a name="Ia0a9"></a>
# 将小说类型(novel)的书的价格都增加5
```sql
use test01_library;
update books
set price = price + 5
where note = 'novel';

将名称为EmmaT的书的价格改为4日，并将说明改为drama

use test01_library;
update books
set price = 40, note = 'drama'
where `name` = 'EmmaT';

删除库存为0的记录

use test01_library;
delete from books
where num = 0;

统计书名中包含a字母的书

use test01_library;
select `name`
from books
where `name` like '%a%';

统计书名中包含a字母的书的数量和库存总量

use test01_library;
select count(*), sum(num)
from books
where `name` like '%a%';

找出“novel”类型的书，按照价格降序排列

use test01_library;
select *
from books
where note = 'novel'
order by price desc;

查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列

use test01_library;
select *
from books
order by num desc, note;

按照note分类统计书的数量

use test01_library;
select note, count(*)
from books
group by note;

按照note分类统计书的库存量，显示库存量超过30本的

use test01_library;
select note, sum(num)
from books
group by note
having sum(num) > 30;

查询所有图书，每页显示5本，显示第二页

use test01_library;
select *
from books
limit 5,5;

按照note分类统计书的库存量，显示库存量最多的

use test01_library;
select note, sum(num)
from books
group by note
having sum(num) >= all (
    select sum(num)
    from books
    group by note
);
# 或者
select note, sum(num)
from books
group by note
order by sum(num) desc
limit 0,1;

查询书名达到10个字符的书，不包括里面的空格

use test01_library;
select `name`
from books
where length(replace(`name`,' ', '')) >= 10;

查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药

use test01_library;
select `name`, note, case note 
                                            when 'novel' then '小说'
                                            when 'law' then '法律'
                                            when 'medicine' then '医药'
                                            when 'cartoon' then '卡通'
                                            when 'joke' then '笑话'
                                            else '其它'
                                            end '类型'
from books;

查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货

use test01_library;
SELECT NAME AS "书名",num AS "库存", CASE WHEN num > 30 THEN '滞销'
                      WHEN num > 0 AND num < 10 THEN '畅销'
                      WHEN num = 0 THEN '无货'
                      ELSE '正常'
                      END "显示状态"
FROM books;

统计每一种note的库存量，并合计总量

SELECT IFNULL(note,'合计库存总量') AS note,SUM(num)
FROM books
GROUP BY note WITH ROLLUP;

统计库存量前三名的图书

use test01_library;
select *
from books
order by num desc
limit 0,3;

找出最早出版的一本书

use test01_library;
select *
from books
order by pubdate
limit 0,1;

找出novel中价格最高的一本书

use test01_library;
select *
from books
where note = 'novel'
order by price desc
limit 0,1;

找出书名中字数最多的一本书，不含空格

use test01_library;
SELECT *
FROM books
ORDER BY CHAR_LENGTH(REPLACE(NAME,' ','')) DESC
LIMIT 0,1;