练习14：查询所有图书 ， 每页显示5本 ， 显示第二页 。
mysql> SELECT *-> FROM books-> LIMIT 5,5;Empty set (0.00 sec)
练习15：按照note分类统计书的库存量 ， 显示库存量最多的 。
mysql> SELECT note,SUM(num) "sum_num" FROM books GROUP BY note ORDER BY sum_num DESC LIMIT 1;+-------+---------+| note| sum_num |+-------+---------+| novel |41 |+-------+---------+1 row in set (0.00 sec)
练习16：查询书名达到9个字符的书 ， 不包括里面的空格 。
mysql> SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name, ' ', ''))>=9;+------+------------+------------+-------+---------+----------+------+| id| name| authors| price | pubdate | note| num|+------+------------+------------+-------+---------+----------+------+|6 | The Battle | Upton Sara |30 |1999 | medicine |40 |+------+------------+------------+-------+---------+----------+------+1 row in set (0.00 sec)
练习17：查询书名和类型 ， 其中note值为novel显示小说 ， law显示法律 ， 显示医药 ， 显示卡通 ， joke显示笑话 。
mysql> SELECT name "书名",note, CASE note WHEN 'novel' THEN '小说'-> WHEN 'law' THEN '法律'-> WHEN 'medicine' THEN '医药'-> WHEN 'cartoon' THEN '卡通'-> WHEN 'joke' THEN '笑话'-> ELSE '其他'-> END "类型" FROM books;+------------+----------+------+| 书名| note| 类型 |+------------+----------+------+| Tal of AAA | novel| 小说 || EmmaT| drama| 其他 || Lovey Day| novel| 小说 || The Battle | medicine | 医药 || Rose Hood| cartoon| 卡通 |+------------+----------+------+5 rows in set (0.00 sec)
练习18：查询书名、库存 ， 其中num值超过30本的 ， 显示滞销 ， 大于0并低于10的 ， 显示畅销 ， 为0的显示需要无货 。
mysql> SELECT name "书名", num "库存", CASE WHEN num > 30 THEN '滞销'-> WHEN num > 0 AND num < 10 THEN '畅销'-> WHEN num = 0 THEN '无货'-> ELSE '正常'-> END "显示状态" FROM books;+------------+------+----------+| 书名| 库存 | 显示状态 |+------------+------+----------+| Tal of AAA |11 | 正常|| EmmaT|22 | 正常|| Lovey Day|30 | 正常|| The Battle |40 | 滞销|| Rose Hood|28 | 正常|+------------+------+----------+5 rows in set (0.00 sec)
练习19：统计每一种note的库存量 ， 并合计总量 。
mysql> SELECT IFNULL(note,'合计库存总量') AS note,SUM(num)-> FROM books-> GROUP BY note WITH ROLLUP;+--------------+----------+| note| SUM(num) |+--------------+----------+| cartoon|28 || drama|22 || medicine|40 || novel|41 || 合计库存总量 |131 |+--------------+----------+5 rows in set, 1 warning (0.00 sec)
练习20：统计每一种note的数量 ， 并合计总量 。
mysql> SELECT IFNULL(note,'合计总量') AS note,COUNT(*)-> FROM books-> GROUP BY note WITH ROLLUP;+----------+----------+| note| COUNT(*) |+----------+----------+| cartoon|1 || drama|1 || medicine |1 || novel|2 || 合计总量 |5 |+----------+----------+5 rows in set, 1 warning (0.00 sec)
练习21：统计库存量前三名的图书 。
mysql> SELECT * FROM books ORDER BY num DESC LIMIT 3;+------+------------+-----------------+-------+---------+----------+------+| id| name| authors| price | pubdate | note| num|+------+------------+-----------------+-------+---------+----------+------+|6 | The Battle | Upton Sara|30 |1999 | medicine |40 ||4 | Lovey Day| George Byron|25 |2005 | novel|30 ||7 | Rose Hood| Richard haggard |28 |2008 | cartoon|28 |+------+------------+-----------------+-------+---------+----------+------+3 rows in set (0.00 sec)