Comparison with MSSQL to learn about MYSQL (8) -- insert, update, delete, and mssqlmysql

Comparison with MSSQL to learn about MYSQL (8) -- insert, update, delete, and mssqlmysql

Similarly, it will only explain the difference between SQL Server and SQL Server.

Insert

Insert multi-row query results into the table

Syntax

INSERT INTO table_name1 (column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)
INSERT INTO SELECT is also supported in SQLSERVER

table_name1 specifies the table to be inserted into the data; column_list1 specifies which columns of the data to be inserted into the table to be inserted; table_name2 specifies the data to be inserted from

Which table is queried; column_list2 specifies the query column of the data source table. The list must have the same number of columns and the same data type as the column_list1 list.

condition specifies the query condition of 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 (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_old
VALUES (11, 'Harry', 20, 'student'), (12, 'Beckham', 31, 'police')

SELECT * FROM person_old

 It can be seen that the record is successfully inserted, and person_old appears to have two records. Next, insert all 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. The id field here is an auto-incrementing primary key. When inserting it, you must ensure the uniqueness of the field value. If you are not sure, you can ignore the field when inserting

Insert only values from other fields

If you run it again, you will get an error

The difference between MYSQL and SQLSERVER:

Difference one

When there are duplicate values in the data to be imported, MYSQL will have three solutions

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

The second and third options are not described here because they are more complicated and do not meet the requirements. Here we will only talk about the first option

TRUNCATE TABLE person

TRUNCATE TABLE persona_old

INSERT INTO person_old
VALUES (11, 'Harry', 20, 'student'), (12, 'Beckham', 31, 'police')

## Note that this 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 this insert statement has the ignore keyword
INSERT IGNORE INTO person (id, NAME, age, info)
SELECT id, NAME, age, info FROM person_old;

 

You can see that the insertion was successful

SQLSERVER

On the SQLSERVER side, if you want to ignore duplicate keys, you need to specify WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]

In this way, when inserting duplicate values, SQLSERVER retains the value for the first time, and the second time it finds duplicate values, SQLSERVER ignores it.

Difference two

Difference when inserting autoincrement columns

SQLSERVER needs to use the SET IDENTITY_INSERT table name ON to insert the value of the self-incrementing field into the table. If the SET IDENTITY_INSERT table name is not ON

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

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

MYSQL is not needed, and has a lot of freedom

You can specify the value of the id field as NULL, MYSQL will automatically add one for you

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


You can also specify values

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

You can also not write the id value, MYSQL will automatically add one for you

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

You can specify the value of the id field or not. When specified, as long as the current id field column does not have the value you are inserting, that is, there is no duplicate value.

Very high degrees of freedom without having to specify the SET IDENTITY_INSERT table name ON option

Difference three

Duplicate NULL values for unique indexes

MYSQL

UNIQUE index will invalidate null fields in MYSQL

insert into test (a) values (null)
 
insert into test (a) values (null)
 
The above insert statement can be repeatedly inserted (the same is true for the joint unique index)

SQLSERVER

SQLSERVER 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, State 1, Line 1
Cannot insert rows with duplicate keys in object "dbo.person" with unique index "IX_person_unique". The duplicate key value is (<NULL>).
The statement has terminated.
 
Update

Update is relatively simple, so I wo n’t say much

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

SELECT * FROM person

delete

Delete a certain range of data in the person table

DELETE FROM person WHERE id BETWEEN 14 AND 17

SELECT * FROM person

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

##method one
DELETE FROM person

##Method Two
TRUNCATE TABLE person
Like SQLSERVER, TRUNCATE TABLE is faster than DELETE FROM TABLE

Test results under the MYISAM engine, 30 lines of records

 
Like SQLSERVER, after executing TRUNCATE TABLE, the auto-incrementing field starts again from the beginning.

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

SELECT * FROM person

TRUNCATE TABLE person

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

SELECT * FROM person

When you just truncate the table and execute the following statement, you will see that it restarts from the beginning

SHOW TABLE STATUS LIKE 'person'

Summary

This section describes the insert, update, and delete operations in MYSQL, and compares them with SQLSERVER, especially the flexibility of insert statements in MYSQL.

Some problems may occur when I transfer data from SQLSERVER at the beginning.

If anything is wrong, you are welcome to make a brick o

What is the difference between SQL and MySQL?

Difference between mssql and mysql
Mssql is the Microsoft SQL Server that runs on windows and platforms.

Mysql is an open-source database Server that runs on windows and unix \ linux platforms. Its Standard Edition is free of charge. You can visit www.mysql.com

Asp \ php is just an explanatory language. It is not necessarily because mssql cannot use php or mysql must use php, but asp-mssql and php-mysql are a common combination.

Full sqldatabase manual _ 1

SQL is the abbreviation of Structured Quevy Language (Structured Query Language. SQL is a set of operation commands for databases. It is a fully functional database language. When using it, you only need to issue the "what to do" command, and the "how to do" command does not need to be considered by the user. SQL is powerful, easy to learn, and easy to use. It has become the foundation for database operations, and almost all databases now support SQL.
#1 II. SQL database data architecture
The data architecture of SQL database is basically a *** structure, but the terms used are different from those of traditional relational models. In SQL, the relational mode (mode) is called the "base table", the storage mode (internal mode) is called the "storage file", and the sub mode (external mode) the attribute is called "view", the tuples are called "row", and the attribute is called "column ). The name is symmetric, for example, ^ 0020.9a ^:
#1 III. Composition of the SQL language
Before learning the SQL language, let's first have a basic understanding of the SQL language and introduce the composition of the SQL language:
1. a SQL database is a set of tables defined by one or more SQL modes.
2. An SQL table consists of row sets. A row is a sequence of columns (SET), and each column corresponds to a data item of the row.
3. A table, a basic table, or a view. A basic table is a table actually stored in a database, and a view is a definition of a table composed of several basic tables or other views.
4. A basic table can store one or more basic tables across one or more storage files. Each storage file corresponds to a physical file on the external storage.
5. You can use SQL statements to query views and basic tables. From the user's perspective, the view is the same as the basic table, and there is no difference between them. They are all Relational Tables ).
6. SQL users can be applications or end users. SQL statements can be embedded in programs in the host language. The host languages include FORTRAN, COBOL, PASCAL, PL/I, C, and Ada. SQL users can also act as independent user interfaces for end users in the interactive environment.
#1 4. operate databases
SQL includes all database operations and consists of four parts:
1. Data Definition: This part is also called "SQL DDL". It defines the logical structure of the database, including the database, basic table, view, and index.
2. Data manipulation: This part is also called "SQL DML", which includes two types of operations: Data Query and data update. Data update includes insert, delete, and update operations.
3. Data Control: the control of user access data includes authorization of basic tables and views, description of integrity rules, and transaction control statements.
4. Embedded SQL language usage rules: rules for using SQL statements in programs in the host language.
Next we will introduce them separately:
#2 (1) Data Definition
SQL data definition functions include database definition, basic... the remaining full text>

Database Operations: Query-update; Delete-insert which method is highly efficient or scientific? Mysql memory table

It is better to update the database. After each deletion and insertion, a number is added to the primary key, and the deletion and insertion operations are performed twice on the database, while the update operation is only performed once.