本文共 4049 字,大约阅读时间需要 13 分钟。
案例:
1、创建数据库test02_library
2、创建表格books
字段名 | 字段说明 | 数据类型 |
---|---|---|
b_id | 书编号 | int(11) |
b_name | 书名 | varchar(50) |
authors | 作者 | varchar(100) |
price | 价格 | float |
pubdate | 出版日期 | year |
note | 说明 | varchar(100) |
num | 库存 | int(11) |
3、使用alter语句给books按如下要求增加相应的约束
字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
---|---|---|---|---|---|---|---|
b_id | 书编号 | int(11) | 是 | 否 | 是 | 是 | 是 |
b_name | 书名 | varchar(50) | 否 | 否 | 是 | 否 | 否 |
authors | 作者 | varchar(100) | 否 | 否 | 是 | 否 | 否 |
price | 价格 | float | 否 | 否 | 是 | 否 | 否 |
pubdate | 出版日期 | year | 否 | 否 | 是 | 否 | 否 |
note | 说明 | varchar(100) | 否 | 否 | 否 | 否 | 否 |
num | 库存 | int(11) | 否 | 否 | 是 | 否 | 否 |
4、向books表中插入记录
1) 指定所有字段名称插入第一条记录
2)不指定字段名称插入第二记录
3)同时插入多条记录(剩下的所有记录)
b_id | b_name | authors | price | pubdate | note | num |
---|---|---|---|---|---|---|
1 | Tal of AAA | Dickes | 23 | 1995 | novel | 11 |
2 | EmmaT | Jane lura | 35 | 1993 | joke | 22 |
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 |
5、统计书名中包含a字母的书
6、统计书名中包含a字母的书的数量和库存总量
7、找出“novel”类型的书,按照价格降序排列
8、查询图书信息,按照库存量降序排列,如果库存量相同的按照note升序排列
9、按照note分类统计书的数量
10、按照note分类统计书的库存量,显示库存量超过30本的
11、查询所有图书,每页显示5本,显示第二页
12、按照note分类统计书的库存量,现在库存量最多的
13、查询书名达到10个字符的书,不包括里面的空格
14、查询书名和类型,其中
note值为novel显示小说,law显示法律,medicine显示医药,cartoon显示卡通,joke显示笑话
15、查询书名、库存,其中
num值超过30本的,显示滞销,大于0并低于10的,显示畅销,为0的显示需要无货
16、统计每一种note的库存量,并合计总量
17、统计每一种note的数量,并合计总量
18、统计库存量前三名的图书
19、找出最早出版的一本书
20、找出novel中最高的一本书
21、找出书名中字数最多的一本书,不含空格
参考答案:
#创建数据库test02_libraryCREATE DATABASE test02_library;#使用test02_libraryUSE test02_library;#创建表格booksCREATE TABLE books( b_id INT, b_name VARCHAR(50), `authors` VARCHAR(100), price FLOAT, pubdate YEAR, note VARCHAR(100), num INT);#给b_id增加主键约束ALTER TABLE books ADD PRIMARY KEY(b_id);#给b_id字段增加自增约束ALTER TABLE books MODIFY b_id INT AUTO_INCREMENT;#给b_name等字段增加非空约束ALTER TABLE books b_name VARCHAR(50) NOT NULL;ALTER TABLE books `authors` VARCHAR(100) NOT NULL;ALTER TABLE books price FLOAT NOT NULL;ALTER TABLE books pubdate DATE NOT NULL;ALTER TABLE books num INT NOT NULL;#指定所有字段名称插入第一条记录INSERT INTO books (b_id,b_name,`authors`,price,pubdate,note,num)VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);#不指定字段名称插入第二记录INSERT INTO books VALUE(2,'EmmaT','Jane lura',35,1993,'Joke',22);#同时插入多条记录(剩下的所有记录)。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);#查询书名中包含a字母的书SELECT * FROM books WHERE b_name LIKE '%a%';#统计书名中包含a字母的书的数量和库存总量SELECT COUNT(*),SUM(num) FROM books WHERE b_name LIKE '%a%';#找出“novel”类型的书,按照价格降序排列SELECT * 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本的SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;#查询所有图书,每页显示5本,显示第二页SELECT * FROM books LIMIT 5,5;#按照note分类统计书的库存量,现在库存量最多的SELECT note,SUM(num) FROM books GROUP BY note ORDER BY SUM(num) DESC LIMIT 0,1;#查询书名达到10个字符的书,不包括里面的空格SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(b_name,' ',''))>=10;/*查询书名和类型,其中note值为novel显示小说,law显示法律,medicine显示医药,cartoon显示卡通,joke显示笑话*/SELECT b_name AS "书名" ,note, CASE note WHEN 'novel' THEN '小说' WHEN 'law' THEN '法律' WHEN 'medicine' THEN '医药' WHEN 'cartoon' THEN '卡通' WHEN 'joke' THEN '笑话' END AS "类型"FROM books;/*查询书名、库存,其中num值超过30本的,显示滞销,大于0并低于10的,显示畅销,为0的显示需要无货,其他的显示正常*/SELECT b_name,num,CASE WHEN num>30 THEN '滞销' WHEN num>0 AND num<10 THEN '畅销' WHEN num=0 THEN '无货' ELSE '正常' END AS "库存状态"FROM books;#统计每一种note的库存量,并合计总量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(b_name,' ','')) DESC LIMIT 0,1;
转载地址:http://suzki.baihongyu.com/