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'