mysql> UPDATE books SET price=40,note='drama' WHERE name='EmmaT';Query OK, 1 row affected (0.00 sec)Rows matched: 1Changed: 1Warnings: 0
练习6：删除库存为0的记录 。
mysql> DELETE FROM books WHERE num=0;Query OK, 2 rows affected (0.00 sec)mysql> SELECT * FROM books;+------+------------+-----------------+-------+---------+----------+------+| id| name| authors| price | pubdate | note| num|+------+------------+-----------------+-------+---------+----------+------+|1 | Tal of AAA | Dickes|28 |1995 | novel|11 ||2 | EmmaT| Jane lura|40 |1993 | drama|22 ||4 | Lovey Day| George Byron|25 |2005 | novel|30 ||6 | The Battle | Upton Sara|30 |1999 | medicine |40 ||7 | Rose Hood| Richard haggard |28 |2008 | cartoon|28 |+------+------------+-----------------+-------+---------+----------+------+5 rows in set (0.00 sec)
练习7：统计书名中包含a字母的书 。
mysql> SELECT name FROM books WHERE name LIKE '%a%';+------------+| name|+------------+| Tal of AAA || EmmaT|| Lovey Day|| The Battle |+------------+4 rows in set (0.00 sec)
练习8：统计书名中包含a字母的书的数量和库存总量 。
mysql> SELECT COUNT(name), SUM(num) FROM books WHERE name LIKE '%a%';+-------------+----------+| COUNT(name) | SUM(num) |+-------------+----------+|4 |103 |+-------------+----------+1 row in set (0.00 sec)
练习9：统计书名中包含a字母的书的数量和库存总量 。
mysql> SELECT COUNT(name), SUM(num) FROM books WHERE name LIKE '%a%';+-------------+----------+| COUNT(name) | SUM(num) |+-------------+----------+|4 |103 |+-------------+----------+1 row in set (0.00 sec)
练习10：找出“novel”类型的书 ， 按照价格降序排列 。
mysql> SELECT * FROM books WHERE note='novel' ORDER BY price DESC;+------+------------+--------------+-------+---------+-------+------+| id| name| authors| price | pubdate | note| num|+------+------------+--------------+-------+---------+-------+------+|1 | Tal of AAA | Dickes|28 |1995 | novel |11 ||4 | Lovey Day| George Byron |25 |2005 | novel |30 |+------+------------+--------------+-------+---------+-------+------+2 rows in set (0.00 sec)
练习11：查询图书信息 ， 按照库存量降序排列 ， 如果库存量相同的按照note升序排列 。
mysql> SELECT * FROM books ORDER BY num DESC, name ASC;+------+------------+-----------------+-------+---------+----------+------+| 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 ||2 | EmmaT| Jane lura|40 |1993 | drama|22 ||1 | Tal of AAA | Dickes|28 |1995 | novel|11 |+------+------------+-----------------+-------+---------+----------+------+5 rows in set (0.00 sec)
练习12：按照note分类统计书的数量 。
mysql> SELECT note,COUNT(*) FROM books GROUP BY note;+----------+----------+| note| COUNT(*) |+----------+----------+| novel|2 || drama|1 || medicine |1 || cartoon|1 |+----------+----------+4 rows in set (0.00 sec)
练习13：按照note分类统计书的库存量 ， 显示库存量超过30本的 。
mysql> SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;+----------+----------+| note| SUM(num) |+----------+----------+| novel|41 || medicine |40 |+----------+----------+2 rows in set (0.00 sec)