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

Editor to share with you how to insert, update and delete data tables in MySQL, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Insert data

Format: INSERT INTO table name (field name...) VALUES (value...)

Create an environment

Use the person table

CREATE TABLE person

(

Id INT UNSIGNED NOT NULL AUTO_INCREMENT

Name CHAR (40) NOT NULL DEFAULT'

Age INT NOT NULL DEFAULT 0

Info CHAR (50) NULL

PRIMARY KEY (id)

)

1.1. Insert data for all fields of the table

Method 1: INSERT INTO person (id,name,age,info) VALUES.

Method 2: INSERT INTO person VALUES (2).

Method 3: INSERT INTO person (name,id,age,info) VALUES ('bbb',3,17,'')

Summary:

Mode 1 and mode 3 show that when inserting data, you don't have to follow the order in the table structure, as long as the value given corresponds to the previous field name one by one.

Method 2 indicates that the field name can be ignored, but its values must be in the same order as the fields in the table structure.

You can use these three ways to insert data for all fields.

1.2. Insert data for the specified field of the table

Sometimes, when inserting record rows into a table, all field values do not have to be inserted manually. Id may grow automatically, or sometimes a field uses a default value without inserting values, so it is necessary to specify fields for the table to insert data.

INSERT INTO person (name,age,info) VALUES ('Willam',20,'sports man')

The id field is missing here, but the id field is AUTO_INCREMENT, so we don't have to insert the value manually

1.3. Insert multiple records at the same time

Format: INSERT INTO table name (field name) VALUES (record row value 1), (record row value 2),...

Explanation: this is equivalent to inserting multiple records with one statement, so you don't have to insert only one piece of data at a time.

INSERT INTO person (name,age,info) VALUES ('qqq',19,''), (' eee',14,'heihei'); / / insert two pieces of data.

When multiple records are inserted at the same time, three nouns Records appear: Duplicates indicates the number of records inserted: records that are ignored when the table name is inserted, possibly because they contain duplicate primary key values Warnings: data values that indicate a problem, such as data type conversion. The above prompt is 2 Records, and it is true that two records are inserted.

1.4. Insert the query results into the table

Sometimes, you may need to move the data from one table to another, but it is slow to enter records one by one, so you have this to insert the query results into the table, that is to say, all the query results from one table are inserted into another table at once, which is very convenient, but there are also prerequisites. That is, the number of fields in the result of the query is the same as the number of fields inserted into the target table, and the data type should be the same. Specifically, the following example.

The above person table is not enough, now we are creating a table.

CREATE TABLE person_old

(

Id INT UNSIGNED NOT NULL AUTO_INCREMENT

Name CHAR (40) NOT NULL DEFAULT'

Age INT NOT NULL DEFAULT 0

Info CHAR (50) NULL

PRIMARY KEY (id)

);

Add two records

INSERT INTO person_id VALUES (11 penciled Harrybirds, 20 pencils studengs), (12 phams, Beckstones, 18 pencils, polices)

Now transfer all the data from the person_ old table to the person table.

INSERT INTO person (id,name,age,info) SELECT id,name,age,info FROM person_old

If you look at it this way, it seems that there is a broken piece in the middle of the id. In fact, you can add only the values of the next three fields without adding the id field.

INSERT INTO person (name,age,info) SELECT name,age,info FROM person_old;

If id is not specified, it will follow the rule of id in the person table, AUTO_INCREMENT. Here we explain the principle of AUTO_INCREMENT. First check the maximum id value in the table, and then add 1 to it. Each time you do it, you will first check what the maximum id value is.

2. Update data

Format: UPDATE table name SET field name = value, field name = value. WHERE condition

Explanation: find the row of records for which you want to update the data through the condition, and then write down which field you want to change and why, in the form of SET field name = value.

UPDATE person SET name=' xxx' WHERE name='aaa'; / / updates the record row of name=aaa in the person table to name=xxx.

Explanation: the record name=aaa in the second line originally. Now it's changed to xxx. You can change many values at the same time, not just the name. The primary key id can also be changed to any value as long as the primary key does not conflict.

UPDATE person SET id= 7 WHERE id=14; / / change the id of the record line of id=14 to 7

3. Delete data

Format: DELETE FROM table name [WHERE]

Explanation: if the condition is not available, then all data degrees in the table are deleted. If there are conditions, delete the rows of records that meet the conditions.

DELETE FROM person WHERE id=13; / / delete the record row of id=13 in the person table.

DELETE FROM person; / / deletes all data degrees from the person table

IV. Comprehensive cases

The people who want to do the following example can do it, the environment has been taught to build, because these are relatively simple, I will not answer them one by one here.

4.1. Create an experimental environment

Create a books table

CREATE TABLE books

(

Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

Name VARCHAR (40) NOT NULL

Author VARCHAR (200) NOT NULL

PRICE INT (11) NOT NULL

Pubdate YEAR NOT NULL

Note VARCHAR (255) NULL

Num INT NOT NULL DEFAULT 0

)

Insert the roadbed in the following table into the books table and use different methods to insert records.

4.2.1. Specify all field names to insert the record

4.2.2. Insert a record without specifying a field name

4.2.3. Insert multiple records at the same time

4.3. increase the price of books with novel type novel by 5.

Change the price of the book named Zhao Liu to 40 and the inventory num to 26

4.7. Delete records with inventory of 5

These are all the contents of the article "how to insert, update and delete data tables in MySQL". 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.