My experiences with MYSQL (1)

I have been using MYSQL for a while. Because the company uses SQLSERVER and MYSQL, and the number of servers and databases is large

It is difficult to manage. I have been comparing it with SQLSERVER during MYSQL learning.

 

The first phase is mainly to learn the basic syntax of MYSQL. The second, third, and fourth phases are coming soon.

Syntax differences

Here I mainly talk about different syntaxes.

1. Default Constraints

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

  (   

 

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.

  (,       ; 

 

 

 

 

3. View table Definitions

SQLSERVER

 sp_help 

 

MYSQL

 emp

 

4. Modify the table name

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

 sys.   N,          emp RENAME emp2

 

5. Modify the field data type

Change the int type of the id field to bigint.

  .      emp2 MODIFY id 

 

 

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.

  emp2 CHANGE id id 

 

Modify Field name

 sys.   N,       ,          emp2 CHANGE id iid 

 

7. Add Fields

The syntax for adding fields is similar, but MYSQL can useFIRSTAndAFTERKeyword specifies the location of the added Field

  .  NAME ()    emp2  NAME ()  

 

 

8. Delete Fields

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

  .    emp2  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.

Table Name 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.

  dbo.emp2    emp2     emp2    pk_emp_dept

 

 

10. delete a table

The syntax for deleting a table is the same.

  .  emp2

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

 (()     .    emp1 ,emp2

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

MYSQL is different, and the syntax is very simple: emp1, emp2

 

Summary

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

Write later