案例:创建表books，对数据进行插入、更新和删除操作，掌握数据表的基本操作。books表结构以及表中的记录如下表：
案例操作过程：
(1)创建数据表books，并按照表8.1所示的结构定义各个字段。
(2)将表8.2中的记录插入books表中。分别使用不同的方法插入记录。
(3)将小说类型(novel)的书的价格都增加5。
(4)将名称为EmmaT的书的价格改为40，并将note说明改为drama。
(5)删除库存为0的记录。
（免费学习推荐：mysql视频教程）
(1)、创建数据表books，并按照表8.1所示的结构定义各个字段。
mysql> create table books -> (
-> id int(11) not null auto_increment primary key,
-> name varchar(50) not null,
-> authors varchar(100) not null,
-> price float not null,
-> pubdate year not null,
-> discount float(3,2) not null,
-> note varchar(255) null,
-> num int(11) not null default 0
-> );Query OK, 0 rows affected (0.05 sec)mysql> select * from books;Empty set (0.05 sec)可以看到表为空，下面向表中插入记录:
(2)、将表8.2中的记录插入books表中。分别使用不同的方法插入记录。
①指定所有字段名称插入记录，SQL语句如下;
mysql> insert into books -> (id,name,authors,price,pubdate,discount,note,num)
-> values(1,'Tale of AAA','Dicks',23,'1995',0.85,'novel',11);Query OK, 1 row affected (0.05 sec)②不指定字段名称插入记录，SQL语句如下：
mysql> insert into books -> values(2,'EmmaT','Jane lura',35,'1993',0.70,'joke',22);Query OK, 1 row affected (0.05 sec)mysql> select * from books;+----+-------------+-----------+-------+---------+----------+-------+-----+| id | name | authors | price | pubdate | discount | note | num |+----+-------------+-----------+-------+---------+----------+-------+-----+| 1 | Tale of AAA | Dicks | 23 | 1995 | 0.85 | novel | 11 || 2 | EmmaT | Jane lura | 35 | 1993 | 0.70 | joke | 22 |+----+-------------+-----------+-------+---------+----------+-------+-----+2 rows in set (0.00 sec)
③同时插入多条记录
mysql> insert into books -> values(3,'Story of Jane','Jane Tim',40,'2001',0.81,'novel',0),
-> (4,'Lovey Day','George Byron',20,'2005',0.85,'novel',30),
-> (5,'Old Land','Honore Blade',30,'2010',0.60,'law',0),
-> (6,'The Battle','Upton Sara',33,'1999',0.65,'medicine',40),
-> (7,'Rose Hood','Richard Kale',28,'2008',0.90,'cartoon',28);Query OK, 5 rows affected (0.05 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from books;+----+---------------+--------------+-------+---------+----------+----------+-----+| id | name | authors | price | pubdate | discount | note | num |+----+---------------+--------------+-------+---------+----------+----------+-----+| 1 | Tale of AAA | Dicks | 23 | 1995 | 0.85 | novel | 11 || 2 | EmmaT | Jane lura | 35 | 1993 | 0.70 | joke | 22 || 3 | Story of Jane | Jane Tim | 40 | 2001 | 0.81 | novel | 0 || 4 | Lovey Day | George Byron | 20 | 2005 | 0.85 | novel | 30 || 5 | Old Land | Honore Blade | 30 | 2010 | 0.60 | law | 0 || 6 | The Battle | Upton Sara | 33 | 1999 | 0.65 | medicine | 40 || 7 | Rose Hood | Richard Kale | 28 | 2008 | 0.90 | cartoon | 28 |+----+---------------+--------------+-------+---------+----------+----------+-----+7 rows in set (0.00 sec)(3)、将小说类型(novel)的书的价格都增加5。
mysql> update books -> set price = price +5
-> where note = 'novel';Query OK, 3 rows affected (0.05 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> 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)(4)、将名称为EmmaT的书的价格改为40，并将note说明改为drama。
mysql> update books -> set price=40,note='drama'
-> where name = 'EmmaT';Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select name,price,note -> from books -> where name= 'EmmaT';+-------+-------+-------+| name | price | note |+-------+-------+-------+| EmmaT | 40 | drama |+-------+-------+-------+1 row in set (0.00 sec)(5)、删除库存为0的记录。
mysql> delete
-> from books -> where num = 0;Query OK, 2 rows affected (0.05 sec)mysql> select *
-> from books -> where num = 0;Empty set (0.00 sec)几个小问题
1、插入记录时可以不指定字段名称吗?
- 不管使用哪种insert语法，都必须给出values的正确数目。如果不提供字段名，则必须给每个字段提供一个值，否则将产生一条错误信息。
- 如果要在insert操作中省略某些字段，那么这些字段需要满足一定条件：该列定义为允许空值；或表定义时给出默认值，若不给出则使用默认值。
2、更新或者删除表时必须指定where子句吗？
- 所有的update和delete语句全都在where子句中指定了条件。如果省略where子句，则update或delete将被应用到表中所有的行。因此，除非确实打算更新或删除所有记录，否则要注意使用不带where子句的update或delete语句。
- 建议在对表进行更新和删除操作之前，使用select语句确认需要删除的记录，以免造成无法挽回的结果。
相关免费学习推荐：mysql数据库(视频)
