一、练习要求
① 创建数据表books，并按照表定义各字段
② 将记录表中的记录插入books表中，分别使用不同的方法插入记录
③ 将小说类型（novel）的书籍的价格都增加5元
④ 将名称为EmmaT的书的价格改为40，并将说明改为drama
⑤ 删除库存为0的记录
books表结构
| 字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
|---|---|---|---|---|---|---|---|
| b_id | 书编号 | INT(11) | 是 | 否 | 是 | 是 | 否 |
| b_name | 书名 | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
| authors | 作者 | VARCHAR(100) | 否 | 否 | 是 | 否 | 否 |
| price | 价格 | FLOAT | 否 | 否 | 是 | 否 | 否 |
| pubdate | 出版日期 | YEAR | 否 | 否 | 是 | 否 | 否 |
| note | 说明 | VARCHAR(100) | 否 | 否 | 否 | 否 | 否 |
| Num | 库存 | INT(11) | 否 | 否 | 是 | 否 | 否 |
books表内容
| b_id | b_name | authors | price | pubdate | note | Num |
|---|---|---|---|---|---|---|
| 1 | Tale of AAA | Dickes | 23 | 1995 | novel | 11 |
| 2 | EmmaT | Jane Iura | 35 | 1993 | joke | 22 |
| 3 | Story of Jane | Jane Time | 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 Haggaed | 28 | 2008 | cartoon | 28 |
二、操作记录
创建数据表books，并按照表定义各字段
mysql> CREATE TABLE books
-> (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(40) NOT NULL,
-> authors VARCHAR(200) NOT NULL,
-> price INT(11) NOT NULL,
-> pubdate YEAR NOT NULL,
-> note VARCHAR(255) NULL,
-> num INT NOT NULL DEFAULT 0
-> );
Query OK, 0 rows affected (0.29 sec)
mysql>
mysql>
mysql>
mysql> SELECT * FROM books;
Empty set (0.00 sec)
mysql>
将记录表中的记录插入books表中，分别使用不同的方法插入记录
方式1：
mysql> INSERT INTO books
-> (id, name, authors, price, pubdate,note,num)
-> VALUES(1, 'Tale of AAA', 'Dickes', 23, '1995', 'novel',11);
Query OK, 1 row affected (0.08 sec)
mysql>
方式2：
mysql> INSERT INTO books
-> VALUES (2,'EmmaT','Jane lura',35,'1993', 'joke',22);
Query OK, 1 row affected (0.06 sec)
mysql>
mysql> SELECT * FROM books;
+----+-------------+-----------+-------+---------+-------+-----+
| id | name | authors | price | pubdate | note | num |
+----+-------------+-----------+-------+---------+-------+-----+
| 1 | Tale of AAA | Dickes | 23 | 1995 | novel | 11 |
| 2 | EmmaT | Jane lura | 35 | 1993 | joke | 22 |
+----+-------------+-----------+-------+---------+-------+-----+
2 rows in set (0.00 sec)
mysql>
方式3：
mysql> 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',33,'1999', 'medicine',40),
-> (7,'Rose Hood','Richard Kale',28,'2008', 'cartoon',28);
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM books;
+----+---------------+--------------+-------+---------+----------+-----+
| id | name | authors | price | pubdate | note | num |
+----+---------------+--------------+-------+---------+----------+-----+
| 1 | Tale 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 | 33 | 1999 | medicine | 40 |
| 7 | Rose Hood | Richard Kale | 28 | 2008 | cartoon | 28 |
+----+---------------+--------------+-------+---------+----------+-----+
7 rows in set (0.00 sec)
mysql>
将小说类型（novel）的书籍的价格都增加5元
mysql> UPDATE books SET price = price + 5 WHERE note = 'novel';
Query OK, 3 rows affected (0.09 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT id, name, price, note FROM books WHERE note = 'novel';
+----+---------------+-------+-------+
| id | name | price | note |
+----+---------------+-------+-------+
| 1 | Tale of AAA | 28 | novel |
| 3 | Story of Jane | 45 | novel |
| 4 | Lovey Day | 25 | novel |
+----+---------------+-------+-------+
3 rows in set (0.00 sec)
mysql>
将名称为EmmaT的书的价格改为40，并将说明改为drama
mysql> UPDATE books SET price=40,note= 'drama 'WHERE name= 'EmmaT ';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT name, price, note FROM books WHERE name='EmmaT';
+-------+-------+--------+
| name | price | note |
+-------+-------+--------+
| EmmaT | 40 | drama |
+-------+-------+--------+
1 row in set (0.00 sec)
mysql>
删除库存为0的记录
mysql> SELECT * FROM books WHERE num=0;
+----+---------------+--------------+-------+---------+-------+-----+
| id | name | authors | price | pubdate | note | num |
+----+---------------+--------------+-------+---------+-------+-----+
| 3 | Story of Jane | Jane Tim | 40 | 2001 | novel | 0 |
| 5 | Old Land | Honore Blade | 30 | 2010 | law | 0 |
+----+---------------+--------------+-------+---------+-------+-----+
2 rows in set (0.00 sec)
mysql> DELETE FROM books WHERE num=0;
Query OK, 2 rows affected (0.09 sec)
mysql> SELECT * FROM books WHERE num=0;
Empty set (0.00 sec)
mysql>
