Shulou(Shulou.com)05/31 Report--

Editor to share with you how to insert, update and delete MySQL data, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Example: create a table books, insert, update and delete the data, and master the basic operations of the data table. The books table structure and the records in the table are as follows:

Case operation procedure:

(1) create the data table books and define the fields according to the structure shown in Table 8.1.

(2) insert the records from Table 8.2 into the books table. Insert records using different methods.

(3) increase the price of all novel books by 5.

(4) change the price of the book called EmmaT to 40 and change the note description to drama.

(5) Delete the record with 0 inventory.

(recommended for free study: 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) not null,-> note varchar (255) null,-> num int (11) not null default 0->); Query OK, 0rows affected (0.05sec) mysql > select * from books Empty set (0.05sec)

You can see that the table is empty, and the following inserts a record into the table:

Insert the records from Table 8.2 into the books table. Insert records using different methods.

① specifies that all field names are inserted into the record, and the SQL statement is as follows

Mysql > insert into books-> (id,name,authors,price,pubdate,discount,note,num)-> values (1 row affected of AAA','Dicks',23,'1995',0.85,'novel',11); Query OK, 1 row affected (0.05 sec)

② does not specify a field name to insert the record, and the SQL statement is as follows:

Mysql > insert into books-> values; 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)

③ inserts multiple records simultaneously

Mysql > insert into books-> values (3recorder story of Jane','Jane Tim',40,'2001',0.81,'novel',0),-> (4recorder Lovey Day','George Byron',20,'2005',0.85,'novel',30),-> (5recorder old Land','Honore Blade',30,'2010',0.60,'law',0)-> (6dint the Battle','Upton Sara',33,'1999') 0.65 Hood','Richard Kale',28,'2008',0.90,'cartoon',28 medical records 40),-> (7 recorder rose rose) Query OK, 5 rows affected (0.05sec) 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 | | novel | 30 | 5 | Old Land | Honore Blade | 30 | 2010 | law | 0 | 6 | The Battle | Upton Sara | 33 | 1999 | 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 all novel books by 5. Mysql > update books-> set price = price + 5-> where note = 'novel';Query OK, 3 rows affected (0.05sec) 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 called 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 records with 0 inventory. Mysql > delete-> from books-> where num = 0 select query OK, 2 rows affected (0.05 sec) mysql > select *-> from books-> where num = 0 scape empty set (0.00 sec)

A few minor problems

1. Can I insert a record without specifying a field name?

No matter which insert syntax you use, you must give the correct number of values. If you do not provide a field name, you must provide a value for each field, or an error message will be generated.

If you want to omit certain fields in the insert operation, they need to meet certain conditions: the column is defined to allow null values, or the default value is given when the table is defined, or the default value is used if not given.

2. Must I specify the where clause when updating or deleting a table?

All update and delete statements specify conditions in the where clause. If you omit the where clause, update or delete will be applied to all rows in the table. Therefore, unless you really intend to update or delete all records, be careful to use update or delete statements without where clauses.

It is recommended that you use select statements to confirm the records that need to be deleted before updating and deleting the table, so as to avoid irreparable results.

The above is all the contents of the article "how to insert, update and delete in MySQL data". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.