Comparison with MSSQL to learn about MYSQL (1) -- Basic syntax _ MySQL

Comparison with MSSQL to learn about MYSQL (1) -- the basic syntax is mainly used to learn the basic syntax of MYSQL and will be updated in succession.

Syntax differences

Here I mainly talk about different syntaxes.

1. default constraints

Difference: The DEFAULT keyword in mysql is not followed by brackets.

-- Sqlserver
Create table emp
(
Id int default (12)
)
-- Mysql
Create table emp
(
Id int default 12
)

2. set auto-increment columns

The difference is big, but it seems that the auto-increment column of mysql cannot set the step size.

MYSQL's auto-incrementing column must also be a primary key column. if it is not a primary key column, an error is returned. you must set the seed value after the table.

-- Set auto-incrementing column
-- Sqlserver
Create table emp
(
Id int identity (1, 1)
)

-- Mysql
-- Set the auto-increment ID to start with N.
Create table emp (
Id int primary key AUTO_INCREMENT
) AUTO_INCREMENT = 100; -- (set the auto-increment ID to start from 100)

By the way, MYSQL can get the auto-increment value of the current table.

1. select max (id) FROM person

2. SELECT LAST_INSERT_ID () function

LAST_INSERT_ID is table-independent and is not recommended.

3. SELECT @ identity

@ Identity refers to the value of the auto-incrementing column corresponding to the last time data is inserted into a table with the identity attribute (that is, the auto-incrementing column). It is a global variable defined by the system.

Generally, global variables defined by the system start with @ and user-defined variables start.

The premise of using @ identity is that the connection is not closed when select @ identity is executed after the insert operation. Otherwise, the value is NULL.

4. show table status like 'person'

This method is recommended.

In the result, the corresponding table name record contains an Auto_increment field. The value of the next auto-increment ID is the maximum auto-increment ID of the current table.

3. View table definitions

SQLSERVER

EXEC sp_help 'emp'

MYSQL

DESC emp

4. modify the table name

There are also differences in the table name modification. change the table emp to emp2.

-- Sqlserver
EXEC sys. [sp_rename] @ objname = N 'emp', -- nvarchar (1035)
@ Newname = 'emp2' -- sysname
-- Mysql
Alter table emp RENAME emp2

5. modify the field data type

Change the int type of the id field to bigint.

-- Sqlserver
Alter table [dbo]. [emp2] alter column [ID] BIGINT

-- Mysql
Alter table emp2 MODIFY id BIGINT

6. modify the field name

When modifying the field name in MYSQL, you must add the field data type. Otherwise, an error is reported. you can CHANGE the data type to achieve the same effect as MODIFY.

The method is to set "new field name" and "old field name" in the SQL statement to the same name and only change "data type"

Change the data type. for example, change the id column to the bigint data type.

Alter table emp2 CHANGE id BIGINT

Modify field name

-- Sqlserver
EXEC sys. [sp_rename] @ objname = n' emp2. ID', -- nvarchar (1035)
@ Newname = 'iid', -- sysname
@ Objtype = 'column' -- varchar (13)

-- Mysql
Alter table emp2 CHANGE id iid BIGINT

7. add fields

The syntax for adding a field is similar, but the FIRST and AFTER keywords can be used in MYSQL to specify the location of the added field.

-- Sqlserver
Alter table [dbo]. [emp2] add name nvarchar (200) NULL

-- Mysql
Alter table emp2 add name nvarchar (200) NULL

8. delete fields

MYSQL does not need to add the COLUMN keyword to delete a field

-- Sqlserver
Alter table [dbo]. [emp2] DROP COLUMN NAME

-- Mysql
Alter table emp2 DROP NAME

9. delete foreign key constraints

There are also great differences between the methods for deleting constraints between MYSQL and SQLSERVER.

In SQLSERVER, check constraints and foreign key constraints can be deleted using the following SQL statement.

Alter table name drop constraint name
However, in MYSQL, if it is a foreign key constraint, you need to use the drop foreign key. if it is a primary key constraint, you need to use the drop primary key, which is a little troublesome.

-- Sqlserver
Alter table dbo. emp2 drop constraint fk_emp_dept

-- Mysql
-- Delete foreign key constraints
Alter table emp2 drop foreign key fk_emp_dept
-- Delete the primary key constraint
Alter table emp2 drop primary key pk_emp_dept

10. delete a table

The syntax for deleting a table is the same.

-- Sqlserver
Drop table [dbo]. [emp2]

-- Mysql
Drop table emp2

However, if you want to delete multiple tables at the same time or before deleting them, it is much more convenient for MYSQL.

-- Sqlserver
IF (OBJECT_ID ('dbo. emp2 ') is not null)
Drop table [dbo]. [emp2]

-- Mysql
Drop table if exists emp1, emp2

SQLSERVER requires a table to judge, and then a table to drop

MYSQL is different, and the syntax is very simple: drop table if exists emp1, emp2

Summary

This article briefly introduces the syntax differences between MYSQL and SQLSERVER.

Write later