mysql(11-综合案例)

感谢宋红康老师的课程！！！！！

一 表的结构和数据

二 练习题

1、创建数据库test01_library

CREATE DATABASE IF NOT EXISTS test_library;

2、创建表 books，表结构如下：

CREATE TABLE IF NOT EXISTS books(
id INT,
`name` VARCHAR(50),
`authors` VARCHAR(100),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT);

3、向books表中插入记录

# 1）不指定字段名称，插入第一条记录
INSERT INTO books
VALUES(1,'Tal of AAA','Dickes',23,'1995','novel',11);

# 2）指定所有字段名称，插入第二记录
INSERT INTO books(id,NAME,AUTHORS,price,pubdate,note,num)
VALUES(2,'EmmaT','Jane lura',35,'1993','joke',22);

# 3）同时插入多条记录（剩下的所有记录）
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);

4、将小说类型(novel)的书的价格都增加5。

UPDATE books
SET price=price+5
WHERE note='novel';

5、将名称为EmmaT的书的价格改为40，并将说明改为drama。

UPDATE books
SET price=40,note='drama'
WHERE NAME='EmmaT';

6、删除库存为0的记录。

DELETE FROM books
WHERE num=0;

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

SELECT *
FROM books
WHERE `name` LIKE'%a%';

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

SELECT COUNT(*),SUM(num)
FROM books
WHERE `name` LIKE '%a%';

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

SELECT *
FROM books
WHERE note='novel'
ORDER BY price DESC;

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

SELECT *
FROM books
ORDER BY num DESC,note ASC;

11、按照note分类统计书的数量

SELECT note,SUM(num)
FROM books
GROUP BY note;

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

SELECT note,SUM(num)
FROM books
GROUP BY note HAVING (SUM(num)>30);

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

#分页显式公式：（当前页数-1）*每页条数，每页条数;
SELECT *
FROM books
LIMIT 5,5;

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

SELECT note,SUM(num)
FROM books
GROUP BY note
ORDER BY SUM(num) DESC
LIMIT 0,1;

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

SELECT `name`
FROM books
WHERE CHAR_LENGTH(REPLACE(`name`,' ',''))>=10;

16、查询书名和类型，其中note值为novel显示小说， law显示法律， medicine显示医药， cartoon显示卡通， joke显示笑话

SELECT `name` '书名',note, CASE note WHEN 'novel' THEN '小说'
			            WHEN 'law' THEN '法律'
			            WHEN 'medicine' THEN '医药'
			            WHEN 'cartoon' THEN '卡通'
			            WHEN 'joke' THEN '笑话'
			            ELSE '其他'
			            END '类型'
FROM books;

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

#between and 不包括前包括后 在这里不符合要求 
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;

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

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

19、统计每一种note的数量，并合计总量

SELECT IFNULL(note,'合计总量') note,COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;

20、统计库存量前三名的图书

SELECT *
FROM books
ORDER BY num DESC 
LIMIT 0,3;

21、找出最早出版的一本书

SELECT *
FROM books
ORDER BY pubdate ASC
LIMIT 0,1;

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

SELECT *
FROM books
ORDER BY price DESC
LIMIT 0,1;

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

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