MySQL Optimization-Delete Insert update and mysql optimization insert update

MySQL Optimization-Delete Insert update and mysql optimization insert update

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46581769

Insert

Insert multi-row query results into the table

Syntax

INSERT INTO table_name1(column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)

Table_name1 specifies the table to be inserted; column_list1 specifies the columns to be inserted into the table to be inserted; table_name2 specifies that the inserted data is from

Which table is queried? column_list2 specifies the query column of the data source table. The list must be the same as the number of fields in the column_list1 list, and the data type must be the same;

Condition specifies the query conditions of the SELECT statement

Query all records from the person_old table and insert them to 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))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))INSERT INTO person_oldVALUES (11,'Harry',20,'student'),(12,'Beckham',31,'police')SELECT * FROM person_old

As you can see, the record is successfully inserted, and person_old is represented in two records. Next, insert all the records in the person_oldperson_old table to the person table.

INSERT INTO person(id,NAME,age,info)SELECT id,NAME,age,info FROM person_old;SELECT * FROM person 

We can see that the data transfer is successful. The id field here is the auto-incrementing primary key. during insertion, ensure that the value of this field is unique. If you cannot determine it, you can ignore this field during insertion,

Insert only values of other fields

An error occurs if you execute the command again.

 

Differences between MYSQL and SQLSERVER:

Difference 1

When there are duplicate values in the data to be imported, MYSQL has three solutions.

Solution 1: Use the ignore keyword
Solution 2: Use replace
Solution 3: ON DUPLICATE KEY UPDATE

The second and third solutions are not introduced here, because they are complicated and do not meet the requirements. Here we only talk about the first solution.

Truncate table personTRUNCATE TABLE persona_old insert into person_oldVALUES (11, 'Harry ', 20, 'student'), (12, 'beckham ', 31, 'police ') # note that the following insert statement does not have the ignore Keyword: insert into person (id, NAME, age, info) SELECT id, NAME, age, info FROM person_old; insert into person_old VALUES (13, 'kay ', 26, 'student') # note that the following insert statement contains the ignore keyword insert ignore into person (id, NAME, age, info) SELECT id, NAME, age, info FROM person_old;

 

The insert operation is successful.

SQLSERVER

To ignore duplicate keys in SQLSERVER, you must specify WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY] when creating a table.

In this way, SQLSERVER retains the value for the first time when inserting duplicate values. If duplicate values are found for the second time, SQLSERVER ignores them.

Difference 2

Differences during auto-increment column insertion

SQL Server needs to use SETIDENTITY_INSERT table name ON to insert the value of the auto-increment field to the table. If the SET IDENTITY_INSERT table name ON is not added

When you insert data into a table, you cannot specify the value of the auto-increment field. Then, the id field cannot specify the value. SQLSERVER will automatically add one for you.

INSERTINTO person(NAME,age,info) VALUES ('feicy',33,'student')

MYSQL does not need it, and it has a high degree of freedom.

You can specify the value of the id field as NULL. MYSQL will automatically add one

INSERTINTO person(id,NAME,age,info) VALUES (NULL,'feicy',33,'student') 

You can also specify a value.

INSERT IGNORE INTO person(id,NAME,age,info) VALUES (16,'tom',88,'student')

You can also leave the id value Unspecified. MYSQL will automatically add one for you.

INSERT IGNORE INTO person(NAME,age,info) VALUES ('amy',12,'bb')

You can specify or not specify the value of the id field. When specifying this parameter, you only need to specify that the current id field column does not have the value you are inserting, that is, there is no duplicate value.

The degree of freedom is very large, and you do not need to specify the SET IDENTITY_INSERT table name ON option.

Difference 3

Duplicate NULL values of unique Indexes

MYSQL

In MYSQL, the UNIQUE index will invalidate the null field.

insert into test(a) values(null) insert into test(a) values(null)

The preceding insert statement can be inserted repeatedly (the same can be done with unique indexes)

SQLSERVER

SQL Server does not work.

CREATE TABLE person (  id INT  NOT NULL IDENTITY(1,1),  NAME CHAR(40)  NULL DEFAULT '',  age INT NOT NULL DEFAULT 0,  info CHAR(50) NULL,  PRIMARY KEY (id))CREATE UNIQUE INDEX IX_person_unique ON [dbo].[person](name)INSERT INTO [dbo].[person]        ( [NAME], [age], [info] )VALUES  ( NULL, -- NAME - char(40)          1, -- age - int          'aa'  -- info - char(50)          ),          ( NULL, -- NAME - char(40)          2, -- age - int          'bb'  -- info - char(50)          )

Message 2601, Level 14, status 1, and 1st rows cannot insert duplicate keys in the "dbo. person" object with the unique index "IX_person_unique. The duplicate key value is (<NULL> ). The statement has been terminated.

 

Update

The update is relatively simple and I will not talk about it much.

UPDATE person SET info ='police' WHERE id BETWEEN 14 AND 17SELECT * FROM person

Delete

Delete a certain range of data in the person table

DELETE FROM  person  WHERE id BETWEEN 14 AND 17SELECT * FROM person

You can use the following two methods to delete all records of a table:

# Method 1 delete from person # method 2 truncate table person

Like SQLSERVER, truncate table is faster than delete from table.

Test results of the MYISAM engine, 30 rows of records

 

 

Like SQLSERVER, after the truncate table is executed, the auto-increment field starts from the beginning again.

################################INSERT IGNORE INTO person(id,NAME,age,info)SELECT id,NAME,age,info FROM person_old;SELECT * FROM personTRUNCATE TABLE  personINSERT IGNORE INTO person(NAME,age,info) VALUES ('amy',12,'bb')SELECT * FROM person

After you truncate the table, execute the following statement and you will see a new one from the beginning.

SHOW TABLE STATUS LIKE 'person'