My MySQL learning Experience (eight) Insert update delete

This article, "My MySQL Learning Experience (eight)" will explain MySQL insert, UPDATE, and DELETE statements

Similarly, it will only explain the different parts of SQL Server.

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)

INSERT into SELECT is also supported in SQL Server

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: Replace into mysql4.1 before using replace into
Scenario three: On DUPLICATE key update mysql4.1 introducing the on DUPLICATE key update instead of replace into

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 this side of SQL Server, if you want to ignore duplicate keys, you need to specify 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 SET IDENTITY_INSERT table name on to insert the value of the self-increment field into the table, without the 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

Insertinto person (id,name,age,info) VALUES (NULL, ' feicy ', ' student ')

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

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 ()          )

Msg 2601, Level 14, State 1, line 1th cannot insert a duplicate key in the object "Dbo.person" that has a unique index of "Ix_person_unique". The repeating key value is (<NULL>). Statement has been terminated.

The enhanced VALUES clause

SQL Server Enhanced VALUES clause

Http://www.cnblogs.com/lyhabc/articles/3960374.htmlINSERT into  [dbo].[ COUNTTB]        ([id],          [testdate]        ) VALUES  ((SELECT  a            from    [dbo].[ AAA]            WHERE   [A] = 2          ),--Id-int          GETDATE ()  --Testdate-datetime        )

MySQL Sub-query

INSERT into  Test (        name,          dd        ) VALUES  ((SELECT  name            from    test1            WHERE   id = 2          ) ,--Id-int now          ()        )        SELECT * from Testselect * from Test1sql Learning guide P172

Update

Update is relatively simple, not much to say

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

Multi-table Update
UPDATE book,book2 SET book. ' Bookcount ' =2, BOOK2. ' Bookcount ' =3
WHERE book. ' BookID ' =1 and BOOK2. ' BookID ' = 1;

Update by sort

Update Book
Set bookcount=2
where BookName in (' SS ')
ORDER BY BookID
Limit 10

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 '

Delete by sort

Delete from book
ORDER BY Bookcount Desc
Limit 50

Multi-table Delete SQL Learning guide P268
DELETE book, Book2 from book, Book2
WHERE book. ' BookID ' >1 and Book2. ' BookID ' >1

If you have a InnoDB engine and you have a foreign key constraint, you will not be able to use multiple table delete/update because the InnoDB engine does not guarantee that data modifications do not break the order in which the constraints are executed

Summarize

This section describes the insertions, updates, and deletions in MySQL, and compares the differences with SQL Server, especially the flexibility of the INSERT statement in MySQL

It's just starting to turn from SQL Server and there might be some.

My MySQL learning Experience (eight) Insert update delete