MySQL学习-DML与DDL的练习

圆之蓝调
250 阅读5分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第23天，点击查看活动详情

前言

上篇我们学习了MySQL的DML添加，更新，删除数据。有兴趣的小伙伴可以阅读(# MySQL学习-DML添加，更新，删除数据）。
下面针对DML与DDL做一些练习。

练习一

创建数据库test01_library

CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';

练习二

创建表books,表结构如下：

字段名字段说明数据类型
id书编号INT
name书名VARCHAR(50)
authors作者VARCHAR(100)
price价格FLOAT
pubdate出版日期YEAR
note说明VARCHAR(100)
num库存INT
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表中插入记录

  1. 不指定字段名称，插入第一条记录。
  2. 指定所有字段名称，插入第二条记录。
  3. 同时插入多条记录（剩下的所有记录）。 表格数据如下：
idnameauthorspricepubdatenotenum
1Tal of AAADickes231995novel11
2EmmaTJane lura351993joke22
3Story of JaneJane Tim402001novel0
4Lovey DayGeorge Byron202005novel30
5Old landHonore Blade302010law0
6The BattleUpton Sara301999medicine40
7Rose HoodRickard haggard282008cartoon28

第一条：

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', 'Rickard haggard', 28, '2008', 'cartoon', 28);

练习四

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

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

练习五

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

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

练习六

删除库存为0的记录。

DELETE FEOM books
WHERE num = 0;

练习七

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

SELECT NAME
FROM books
WHERE NAME LIKE '%a%';

练习八

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

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

练习九

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

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

练习十

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

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

练习十一

按照note分类统计书的数量。

SELECT note, COUNT(*)
FROM books
GROUP BY note;

练习十二

按照note分类统计书的库存量，显示库存量超过30本的书。
分析： 这里先按照note进行分组，分组后过滤条件使用HAVING。

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

练习十三

查询所有图书，每页显示5本，显示第二页。
分析： 这里分页使用LIMIT。

SELECT *
FROM books
LIMIT 5, 5;

练习十四

按照note分类统计书的库存量，显示库存最多的。
分析： 这里使用ORDER BY排序后，再使用LIMIT显示第一个。

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

练习十五

查询书名达到10个字符的书，不包含里面的空格。
分析： 这里使用REPLACE函数，先将字符中间的空格去掉，再使用CHAR_LENGTH函数计算字符的长度，再与10进行比较，得出结果。

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

练习十六

查询书名和类型，其中note的值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话。
分析： 显示不同的名称，这里使用CASE WHEN THEN ELSE END。

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的显示无货。
分析： 这里判断不同的情况，也需要使用CASE WHEN THEN ELSE END。

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

练习十八

统计每一种note的库存量，并合计总量。
分析： 合计总量使用WITH ROLLUP。

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

但是这样查出的结果note字段展示NULL，我们还需要处理NULL的情况。使用IFNULL函数判断NULL的情况，显示另外的名称。

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

练习十九

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

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

练习二十

统计库存量前三名的图书。

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

练习二十一

找出最早出版的一本书。

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

练习二十二

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

SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC
LIMIT 0, 1;

练习二十三

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

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

今天先学习到这里，明天继续。

avatar
文章
阅读
粉丝