[mysql]DDL,DML综合案例,
综合案例
题目如下
目录
综合案例
编辑 编辑 # 1、创#1建数据库test01_library
# 2、创建表 books，表结构如下：
# 3、向books表中插入记录库存
# 4、将小说类型(novel)的书的价格都增加5。
# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
# 6、删除库存为0的记录。
# 7、统计书名中包含a字母的书
# 8、统计书名中包含a字母的书的数量和库存总量
# 9、找出“novel”类型的书，按照价格降序排列
# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
# 11、按照note分类统计书的数量
# 12、按照note分类统计书的库存量，显示库存量超过30本的
# 13、查询所有图书，每页显示5本，显示第二页
# 14、按照note分类统计书的库存量，显示库存量最多的
# 15、查询书名达到10个字符的书，不包括里面的空格
# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
# 18、统计每一种note的库存量，并合计总量
# 19、统计每一种note的数量，并合计总量
# 20、统计库存量前三名的图书
# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
# 21、找出最早出版的一本书
# 22、找出novel中价格最高的一本书
# 23、找出书名中字数最多的一本书，不含空格
DDL创建数据库,创建表
DML增加删除修改表
我们都讲完了,现在我们来一个综合案例的练习.涉及了我们库和表的创建
# 1、创#1建数据库test01_library
CREATE DATABASE test01_library
USE test01_library
# 2、创建表 books，表结构如下：
CREATE TABLE books(
id INT,
`name` VARCHAR(50),
`authors` VARCHAR(100),
price FLOAT,
pbudate 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 (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#写更新语句的时候,记得是赋值=,不是直接写price+5
# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books SET price=40,note='drama' WHERE `name`='EmmaT'
SELECT * FROM books
# 6、删除库存为0的记录。(不要运行,写了就好)
DELETE FROM books WHERE num=0;
SELECT * FROM books
#在这之后就是查询的内容了
# 7、统计书名中包含a字母的书
SELECT name,COUNT(*) FROM books
WHERE name like '%a%'
GROUP BY name
# 8、统计书名中包含a字母的书的数量和库存总量
SELECT name,COUNT(*),SUM(num) FROM books
WHERE name like '%a%'
GROUP BY name
# 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本，显示第二页
SHOW TABLES
CREATE TABLE books
AS
SELECT * FROM atguigudb.books
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 LENGTH(REPLACE(name,' ',''))>=5
# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
SELECT name,(CASE note
WHEN 'nobel' THEN '小说'
WHEN 'law' THEN '法律'
WHEN 'medicine' THEN '医药'
WHEN 'cartoon' THEN '卡通'
WHEN 'joke' THEN '笑话'
ELSE '无'
END )
FROM books
#注意这里结尾只要添加end就可以,不用添加end case,navicat的提示是错误的
# 18、统计每一种note的库存量，并合计总量
SELECT note,SUM(num) FROM books GROUP BY note
UNION ALL
SELECT '合计',SUM(num) FROM books
# 19、统计每一种note的数量，并合计总量
SELECT note,COUNT(num) FROM books GROUP BY note
UNION ALL
SELECT '合计',COUNT(num) FROM books
# 20、统计库存量前三名的图书
SELECT name,sum(num) FROM books GROUP BY `name`
ORDER BY sum(num) DESC limit 0,3
# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT `name`,num,CASE WHEN num>30
THEN
'滞销'
WHEN num>0 AND num<10 THEN
'畅销'
WHEN num=0 THEN
'无货'
ELSE
'平平无奇'
END
FROM books
#注意如果case是范围,不是值的话,case和WHENN就要连在一起.
# 21、找出最早出版的一本书
SELECT * FROM books
WHERE pubdate=(SELECT MIN(pubdate) FROM books )
# 22、找出novel中价格最高的一本书
SELECT * FROM books
WHERE price=(SELECT MAX(price) FROM books )
# 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books
WHERE LENGTH(replace(name,' ',''))=(SELECT MAX(LENGTH(replace(name,' ',''))) FROM books )