案例:创建表books，对数据进行插入、更新和删除操作，掌握数据表的基本操作。books表结构以及表中的记录如下表：
案例操作过程：
(1)创建数据表books，并按照表8.1所示的结构定义各个字段。
(2)将表8.2中的记录插入books表中。分别使用不同的方法插入记录。
(3)将小说类型(novel)的书的价格都增加5。
(4)将名称为EmmaT的书的价格改为40，并将note说明改为drama。
(5)删除库存为0的记录。
（免费学习推荐：mysql视频教程）
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)可以看到表为空，下面向表中插入记录:
①指定所有字段名称插入记录，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)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)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)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、插入记录时可以不指定字段名称吗?
2、更新或者删除表时必须指定where子句吗？
相关免费学习推荐：mysql数据库(视频)
以上就是练习MySQL数据的插入、更新与删除的详细内容，更多请关注php中文网其它相关文章！
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
779
收藏
