Learn about Mysql in contrast to MSSQL (eight)--insert Update Delete _mysql

In the same way, it only explains different places from 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 Specify which table to insert data in, Column_list1 specify which columns to insert into the table to insert; table_name2 Specifies that the insert data is from the

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

Condition specify the query criteria for the 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 (m) null,
 PRIMARY KEY (ID)

CREATE TABLE person_old (
 ID INT UNSIGNED not N Ull auto_increment,
 NAME char NOT NULL default ", age
 int. NOT NULL default 0,
 info CHAR () NULL,
 PRIMARY KEY (ID)
)

INSERT into Person_old
VALUES (one, ' Harry, ', ' Student '), ("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

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

SELECT * FROM person 

You can see that the data transfer is successful, where the ID field is an increased primary key, and you want to ensure the uniqueness of the value when inserting, and if you are unsure, you can omit the field when you insert it.

Insert values from other fields only

If you do it again, it will go wrong.

The difference between MySQL and SQL Server:

Distinguish one

When there are duplicate values in the data to be imported, MySQL will have three different scenarios

Scenario One: Using the Ignore keyword
Option two: Use 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, and only the first option is spoken here

TRUNCATE table Person

TRUNCATE table Persona_old 

inserts into Person_old
VALUES (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 ', num, ' student ')

# #注意下面这条insert语句是有ignore关键字的
insert IGNORE into Person (id,name,age,info)
SELECT id,name,age,info from Person_old;

You can also specify a value

INSERT IGNORE into person (id,name,age,info) VALUES ("Tom", "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 ', ', ' BB ')

You can specify that 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 values can be

The degree of freedom is very large and there is no need to specify the SET identity_insert table name on option

Difference Three

Null value repeat problem for unique index

Mysql

The unique index in MySQL will invalidate the null field

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

The above INSERT statement can be inserted repeatedly (same as Union unique index)

Sql server

SQL Server is not

CREATE TABLE person (
 ID int. NOT NULL IDENTITY (1,1),
 NAME CHAR null default ", age
 INT NOT NULL default 0 ,
 info CHAR (x) 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
     )

Message 2601, Level 14, State 1, row 1th
cannot insert a duplicate key in an object "Dbo.person" with a unique index "Ix_person_unique". The duplicate key value is (<NULL>). The
statement was terminated.

Update

It's easy to update, so don't say much.

UPDATE person SET info = ' police ' WHERE ID BETWEEN

SELECT *

Delete

Delete a range of data in the person table

DELETE from person WHERE ID BETWEEN

SELECT * from person

If you want to delete all 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 the MyISAM engine, 30-line Records

As with SQL Server, when you finish truncate TABLE, the Self-add field starts again.

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

SELECT * FROM person

TRUNCATE TABLE person

INSERT IGNORE to Person (name,age,info) VALUES (' Amy ', ', ' BB ')

SELECT * FROM person

When you've just truncate the table, execute the following statement and you'll see a new start

Show TABLE STATUS like ' person '

Summarize

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

Just beginning to turn around from SQL Server may have some not adapted

If there is a wrong place, welcome to shoot Bricks O (∩_∩) o