#DDL和DML综合案例
#1:创建名为test01_library的数据库
CREATE DATABASE IF NOT EXISTS test01_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
);
SELECT *
FROM books;
#3.1:不指定字段名称，插入第一条记录
INSERT INTO books
VALUES (1,'Tal of AAA','Dickes',23,1995,'novel',11);
#3.2:指定所有字段名称，插入第二条记录
INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num)
VALUES(2,'EmmaT','Jane lura',35,1993,'joke',22);
#3.3:同时插入多条记录(插入余下所有记录)
INSERT INTO books
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';
SELECT *
FROM books;
#6: 删除库存为0的记录
DELETE FROM books
WHERE num=0;
#7:统计书名中包含a字母的书的书名
SELECT `name`
FROM books
WHERE `name` LIKE '%a%';
#8:统计书名中包含a的书的数量和库存总量
SELECT COUNT(*) '数量',SUM(num) '库存总量'
FROM books
WHERE `name` LIKE '%a%';
#9:找出novel类型的书，按照价格降序排序
SELECT `name`,note,price
FROM books
WHERE note='novel'
GROUP BY price DESC;
#10:查询图书的信息，按照库存量的降序排列
#如果库存量相同，按照note升序排列
SELECT *
FROM books
GROUP BY num DESC,note ASC;
#11:按照note分类统计书的数量
SELECT note,COUNT(*)
FROM books
GROUP BY note;
#12:按note分类,显示库存总量超过30本的
SELECT note,SUM(num)
FROM books
GROUP BY note
HAVING SUM(num)>30;
#13:查询所有图书,每页显示5本显示第二页
SELECT *
FROM books
LIMIT 5,2;
#14:查询库存量总量最高的note类型书
SELECT note,SUM(num)
FROM books
GROUP BY note
HAVING SUM(num) >= ALL(
SELECT SUM(num)
FROM books
GROUP BY note
);
#15:查询去除空格后书名长度大于10的书名
SELECT `name`
FROM books
WHERE CHAR_LENGTH(REPLACE(`name`,' ',''))>=10;
#16:查询name,note,当note类型为novel时其类型显示为小说
# 当note类型为law时显示为法律
# 当note类型为medicine时显示为医药
# 当note类型为cartoon时显示为卡通
# 当note类型为JOKE时显示为笑话
# 其它note类型显示为其它
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:查询name,num,当num>30时,其状态为滞销,当0<num<10时其显示为畅销
#当num=0时,其显示为无货,其余显示为其它
SELECT `name` '书名',num '库存', CASE WHEN num>30 THEN '滞销'
WHEN num>0 AND num<10 THEN '畅销'
WHEN num=0 THEN '无货'
ELSE '其它'
END '状态'
FROM books;
#18:查询每类书的库存数量并计算它们的合计总量
ALTER TABLE books CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
SELECT IFNULL(note,'合计总量') AS note,SUM(num)
FROM books
GROUP BY note WITH ROLLUP;
#19：查询每类书的数量并计算它们的合计总量
SELECT IFNULL(note,'合计总量') AS note,COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;
#20:找出库存量前三的书
SELECT *
FROM books
GROUP BY num DESC
LIMIT 0,3;
#21:找出价格最高的书
SELECT *
FROM books
ORDER BY pubdate ASC
LIMIT 0,1;
#22:找出价格最高的novel
SELECT *
FROM books
WHERE note='novel'
ORDER BY price DESC
LIMIT 0,1;
#23: 找出去除空格后书名最长的书的信息
SELECT *
FROM books
GROUP BY CHAR_LENGTH(REPLACE(`name`,' ','')) DESC
LIMIT 0,1;
