Mysql练习-数据表数据操作

一、练习要求

① 创建数据表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>