Case: Create the books table, insert, update and delete data, and master the basic operations of the data table. The structure of the books table and the records in the table are as follows:
Case operation process:
(1) Create the data table books and define each field according to the structure shown in Table 8.1.
(2) Insert the records in Table 8.2 into the books table. Use different methods to insert records.
(3) Increase the price of novel type books by 5.
(4) Change the price of the book named EmmaT to 40, and change the note description to drama.
(5) Delete records with inventory of 0.
(Free learning recommendation: mysql video tutorial)
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)
You can see that the table is empty. Next, insert records into the table:
①Specify all field names to insert records, the SQL statement is as follows;
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)
②Insert records without specifying field names, the SQL statement is as follows:
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)
③Insert multiple records at the same time
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)
A few small questions
1. Can I not specify a field name when inserting a record?
2. Is it necessary to specify the where clause when updating or deleting a table?
Related free learning recommendations: mysql database(Video)
The above is the detailed content of Practice inserting, updating and deleting MySQL data. For more information, please follow other related articles on the PHP Chinese website!