案例:建立表格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中文網其他相關文章！
