MySQL optimization--Insert Update Delete

Reprint Please specify source: http://blog.csdn.net/l1028386804/article/details/46581769

Insert

Inserting multiple rows of query results into a table

Grammar

INSERT into Table_name1 (column_list1) SELECT (column_list2) from table_name2 WHERE (condition)

TABLE_NAME1 Specifies the table to insert data into, COLUMN_LIST1 specifies which columns in the table to insert data from, table_name2 specifies that the insert data is from

which table is queried; column_list2 specifies the query column for the data source table, which must be the same as the number of fields in the Column_list1 list, with the same data type;

Condition specifying query criteria for a SELECT statement

Query all records from the Person_old table and insert them into the person table

CREATE TABLE person (  ID int. UNSIGNED NOT NULL auto_increment,  NAME CHAR (+) NOT null DEFAULT ", age  Int. Not Null DEFAULT 0,  Info CHAR (() null,  PRIMARY KEY (ID)) CREATE TABLE person_old (  ID INT UNSIGNED NOT NULL auto_i Ncrement,  NAME char (+) NOT null default ", age  int. NOT NULL default 0,  info CHAR () null,  PRIMARY KEY ( ID) INSERT into person_oldvalues (one, ' Harry ', ' Student '), (A, ' Beckham ', +, ' police ') SELECT * from Person_old

As you can see, the insert record is successful, and the Person_old table now has two records. Next, insert all the records from the Person_oldperson_old table into the person table

You can see that the data transfer succeeds, where the ID field is the self-increment primary key, when inserting to ensure that the value of the field is unique, if not sure, you can insert the time to ignore the fields,

Insert values for other fields only

If you do it again, it will go wrong.

The difference between MySQL and SQL Server:

Distinguish one

There are three options for MySQL when there are duplicate values in the data to be imported

Scenario One: Using the Ignore keyword
Scenario two: Using replace into
Scenario Three: on DUPLICATE KEY UPDATE

The second and third programmes are not introduced here, because they are more complex and do not meet the requirements, here only the first scenario

TRUNCATE table Persontruncate table Persona_old INSERT into person_oldvalues (one, ' Harry ', ' Student '), (' Beckham '), , ' police ') # #注意下面这条insert语句是没有ignore关键字的INSERT into Person  (Id,name,age,info) SELECT Id,name,age,info from Person_old;insert into Person_old VALUES (+, ' kay ', +, ' student ') # #注意下面这条insert语句是有ignore关键字的INSERT IGNORE into person (Id,name,age,info) SELECT Id,name,age,info from Person_old;

You can see that the insert succeeded

Sql server

On the SQL Server side, if you want to ignore duplicate keys, you need to specify a with when building the table (ignore_dup_key=on) on [ PRIMARY ]

In this way, when inserting duplicate values, SQL Server retains the value for the first time, and the second time a duplicate value is found, SQL Server ignores

Difference Two

Differences when inserting an auto-increment column

SQL Server needs to use the SETidentity_insert table name on to insert the value of the self-increment field into the table, if no SET identity_insert table name on

You cannot specify the value of the self-increment field when inserting data into a table, the ID field cannot specify a value, and SQL Server automatically adds a

Insertinto person (name,age,info) VALUES (' Feicy ', ' student ')

And MySQL doesn't need it, and it's a lot of freedom.

You can specify the value of the ID field as Null,mysql will automatically help you add a

You can also specify a value

INSERT IGNORE into person (id,name,age,info) VALUES (+, ' Tom ', and ' student ')

You can also not write the value of the ID, MySQL will automatically help you add a

INSERT IGNORE into person (name,age,info) VALUES (' Amy ', ' A ', ' BB ')

You can specify the value of the ID field can also be unspecified, as long as the current ID field column does not have the value you are inserting, that is, no duplicate value can be

Very large degrees of freedom and no need to specify the SET identity_insert table name on option

Difference Three

Null value duplication problem for unique indexes

Mysql

A unique index in MySQL will invalidate the null field

INSERT into Test (a) VALUES (NULL) INSERT INTO Test (a) values (NULL)

The above INSERT statement can be inserted repeatedly (same as federated Unique index)

Sql server

SQL Server is not.

CREATE TABLE person (  ID int. not  null IDENTITY (),  NAME CHAR (+)  NULL DEFAULT ' ', age  int. NOT NULL D Efault 0,  info CHAR (+) NULL,  PRIMARY KEY (ID)) CREATE UNIQUE INDEX ix_person_unique on [dbo].[ Person] (name) inserts into [dbo]. [Person]        ([NAME], [age], [info])  VALUES  (NULL,--Name-char (1)          ,--age-int          ' AA '  --Info-char ()          ),          (NULL,---NAME- char (2)          ,--age-int          ' BB '-  -Info-char ()          )

2601  - 1 1 rows cannot insert duplicate key rows in an object "Dbo.person" that has a unique index of "Ix_person_unique". The duplicate key value is (<NULL>). Statement has been terminated. 

Update

Update is relatively simple, not much to say

UPDATE person SET info = ' Police ' WHERE ID between + 17SELECT * from person

Delete

Delete a range of data from the person table

DELETE from  person  WHERE ID between + 17SELECT * from person

If you want to delete all the records for a table, you can use the following two methods

# #方法一DELETE from     person# #方法二TRUNCATE TABLE  person

Like SQL Server, TRUNCATE table is faster than delete from table

Test results under MyISAM engine, 30 rows of records

As with SQL Server, the self-increment field is restarted from the beginning after TRUNCATE TABLE is executed.

############################### #INSERT IGNORE into person (id,name,age,info) SELECT id,name,age,info from Person_old;  SELECT * from Persontruncate TABLE  personinsert IGNORE to Person (name,age,info) VALUES (' Amy ', ' A ', ' BB ') SELECT * FROM Person

When you've just truncate the table, execute the following statement and you'll see it back from the beginning.

SHOW TABLE STATUS like ' person '

MySQL optimization--Insert Update Delete