MySQL (iv) inserting, updating, deleting data from data sheets

Preface

Inserting, updating and deleting data sheets is easy, but it's easy to learn, and the details determine success or failure.

---WH

　　　　

First, insert data

Format: INSERT into table name (field name ...) Values (value ...);

Create an environment

Using the person table

CREATE TABLE Person

(

ID INT UNSIGNED not NULL auto_increment,

Name CHAR (+) not NULL DEFAULT ' ',

The age INT is not NULL for DEFAULT 0,

Info CHAR (+) NULL,

PRIMARY KEY (ID)

);

1.1. Insert data for all fields of the table

Mode one: INSERT into person (id,name,age,info) VALUES (1, ' Green ', +, ' Lawyer ');

　　　　　　　　　　　　　　　　　　　　

Mode two: INSERT into the person VALUES (2, ' aaa ', ' + ', ' man ');

　　　　　　　　　　　　　　　　　　

Method Three: INSERT into person (name,id,age,info) VALUES (' BBB ', 3,17, ' haha ');

　　　　　　　　　　　　　　　　　　

Summarize:

Mode one and the way three explain that inserting data can not be in the order of the table structure, as long as the given value corresponds to the previous field name one by one,

Method Two indicates that the field name can be ignored, but its value must be the same as the order of 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

In some cases, when inserting record rows into a table, the field values do not necessarily need to be inserted manually, the ID may grow automatically, and sometimes a field uses the default value, without inserting a value, and you need to insert data for the table designation field.

INSERT into person (name,age,info) VALUES (' Willam ', ' Sports ');

　　　　　　　　　　　　　　　　　　

The ID field is missing, but the ID field is auto_increment, so we don't have to insert the value manually.

1.3. Inserting multiple records at the same time

Format: INSERT into table name (field name) values (record row value 1), (record row value 2),...;

Explanation: That is, the equivalent of a statement to insert multiple records, you do not have to insert only one piece of data at a time

INSERT into person (name,age,info) VALUES (' QQQ ', ' n ', ' haha '), (' Eee ', +, ' Heihei '); Insert two data.

　　　　　　　　　　　　　　　　　　

When multiple records are inserted, there are three nouns Records: Indicates the number of records inserted duplicates: records that are ignored when table names are inserted, perhaps because they contain duplicate primary key values Warnings: Indicates problematic data values, such as data type conversions. The above is a hint of 2 records, and it is true that the number of two records is inserted

1.4. Insert the query results into the table

In some cases, it may be necessary to move the data in one table into another table, but the input records are very slow, so there is this to insert the query results into the table, that is, the query to a table in the results, all one-time inserted into another table, so it is convenient, but there are prerequisites, That is, the number of fields for the result of the query is the same as the number of fields inserted into the target table, and the data types are the same. Specifically, the following example.

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

CREATE TABLE Person_old

(

ID INT UNSIGNED not NULL auto_increment,

Name CHAR (+) not NULL DEFAULT ' ',

The age INT is not NULL for DEFAULT 0,

Info CHAR (+) NULL,

PRIMARY KEY (ID)

);

Add two Records

INSERT into person_id VALUES (one, ' Harry ', ' Studeng '), (' Beck ', ' polic ');

　　　　　　　　　　　　　　　　　　

All data in the Person_old table is now transferred to the person table.

INSERT into person (id,name,age,info) SELECT id,name,age,info from Person_old;

　　　　　　　　　　　　　　　　　　

So look at the middle of the ID is like a broken piece, in fact, you can not add the ID field, only add the value of the following three fields

INSERT into person (name,age,info) SELECT name,age,info from Person_old;

　　　　　　　　　　　　　　　　　　　　

Do not specify the ID, then the person table in accordance with the rules of the ID, auto_increment, here to explain the principle of auto_increment, first check the table of the largest ID value, and then add 1 to the top, each time will be detected the maximum ID value is first.

　　　　　　　

Second, update the data

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

Explanation: Find the row of records to update data by criteria, and then write out which field to change, and change the value, using the SET field name = value.

UPDATE person SET name = ' xxx ' WHERE name= ' AAA '; Update the record line name=aaa in the person table to name=xxx.

　　　　　　　　　　　　　　　　

Explanation: The record of the second row was name=aaa. Now the XXX is changed. You can change multiple values at the same time, not necessarily just the name. The primary key ID can also be changed, as long as the primary key does not conflict, arbitrarily changed to what value

UPDATE person SET id = 7 WHERE id = 14; Change the ID of the id=14 record line to 7

　　　　　　　　　　　　　　　　　　　　

Third, delete data

Format: DELETE from table name [WHERE <condition>];

Explanation: If the condition is not there, then all the data in the table is deleted. If there is a condition, the record line that matches the condition is deleted.

DELETE from person WHERE id = 13; Delete the record line id=13 in the person table.

　　　　　　　　　　　　　　　　

DELETE from person; Remove all data degrees from the person table

　　　　　　　　　　　　　　　　

Iv. Comprehensive Cases

Below This example wants to do the person can do, the environment has been taught to set up, these because relatively simple, I am not here to answer all.

4.1. Create an experimental environment

Create a Books table

CREATE TABLE Books

(

ID INT not NULL auto_increment PRIMARY KEY,

Name VARCHAR (+) is not NULL,

Author VARCHAR (+) not NULL,

Price INT (one) is not NULL,

Pubdate year is not NULL,

Note VARCHAR (255) NULL,

Num INT not NULL DEFAULT 0

);

　　　　　　　　　　　　

　　　　　　

4.2. Insert the roadbed in the table below into the books table, using different methods to insert the record.

　　　　　　　　　　　　　　　

4.2.1, specifying all field names to insert records

4.2.2, do not specify field names to insert records

4.2.3, inserting multiple records at the same time

4.3. Increase the price of the novel type novel book by 5

4.4. Change the price of the book named Zhao Liu to 40 and change the stock quantity num to 26

4.7, delete the inventory 5 records

MySQL (iv) inserting, updating, deleting data from data sheets