Practice inserting, updating and deleting MySQL data

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)

(1 ), create the data table books, and define each field according to the structure shown in Table 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)

You can see that the table is empty. Next, insert records into the table:

(2) Insert the records in Table 8.2 into the books table. Use different methods to insert records.

①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)

(3). Increase the price of novel type books by 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). Change the price of the book named EmmaT to 40, and change the note description to 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). Delete the record with inventory of 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)

A few small questions

1. Can I not specify a field name when inserting a record?

• No matter which insert syntax is used, values ​​must be given the correct number. If you do not provide field names, you must provide a value for each field, otherwise an error message will be generated.
• If you want to omit certain fields in the insert operation, then these fields need to meet certain conditions: the column is defined to allow null values; or a default value is given when the table is defined, if not given, the default value is used .

2. Is it necessary to specify the where clause when updating or deleting a table?

• All update and delete statements specify conditions in the where clause. If the where clause is omitted, update or delete will be applied to all rows in the table. Therefore, be careful about using update or delete statements without a where clause unless you really intend to update or delete all records.
• It is recommended to use the select statement to confirm the records that need to be deleted before updating and deleting the table to avoid irreversible results.

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!