MySQL Learning Series 1: Simple Syntax

Because the company uses SQLSERVER and MYSQL, and the number of servers and databases is relatively large

It is difficult to manage. During the period of learning MYSQL, I have been comparing with SQLSERVER

The first period is mainly about learning the basic syntax of MYSQL

Differences in syntax

1. Default constraints

Differences : There is no need to add brackets after the DEFAULT keyword in mysql

2. Set the auto-increment column

There must be an auto-increment column in MYSQL For indexed columns, the seed value must be set at the end of the table

Set the step size of the auto-increment column, which can be divided into global level and session level

If it is the session level, then when the user creates a new session, the step size returns to the global level, so the step size of mysql is very different from the step size of sqlserver

mysql cannot be set is the step size of the table level! !

The mysql server maintains two types of mysql system parameters (system variables): global variables and session variables.

Their meanings and differences are as shown in their respective names. Session variables are at the session level, and changes to them will only affect this session; global variables are at the system level,

Changes to it will affect all new sessions (existing sessions will not be affected when the change is made) until the next time mysql server is restarted.

Note that the impact of its changes cannot span restarts. If you want to use the new value when the mysql server restarts, you can only specify it by specifying variable options on the command line or changing the option file.

And changes through SET cannot span restarts.

Each system variable has a default value, which is determined when compiling the mysql system.

To specify system variables, you can generally specify options on the command line when the server starts or through the option file.

Of course, most system variables can be specified when the system is running. , specify its value through the set command.

View the system's current default auto-increment column seed value and step value

Q: If there is a table with an auto-increment column whose field is id Add the primary key. After 10 pieces of data have been inserted into the table, delete the data with IDs 8, 9, and 10, restart mysql, and insert another piece of data after

. Then the id of this data What should the value be, 8, or 11?

Answer: If the table type is MyISAM, then it is 11. If the type of table is InnoDB, the id is 8.

This is because the two types of storage engines store the maximum ID record in different ways. The MyISAM table records the maximum ID in the data file, and the maximum ID value of the auto-incremented primary key will not be lost when restarting mysql. ;

InnoDB records the maximum ID value in memory, so after restarting mysql or performing OPTIMIZE operation on the table, the maximum ID value will be lost.

By the way, there are four methods for MYSQL to obtain the auto-increment value of the current table

1. SELECT MAX(id) FROM person for a specific table

2. SELECT LAST_INSERT_ID() The function is for any table

3. SELECT @@identity is for any table

@@identity represents the latest data inserted into the table with the identity attribute (i.e. auto-increment column). The value of the auto-increment column is a system-defined global variable.

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

The prerequisite for using @@identity is that after performing the insert operation, the connection is not closed when executing select @@identity, otherwise the result will be a NULL value.

4. SHOW TABLE STATUS LIKE 'person'

If you are targeting a specific table, it is recommended to use this method

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

3. View the table definition

SQLSERVER

MYSQL

4. Modify the table name

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

5. Modify the data type of the field

Change the int type of the id field to bigint

6. Modify the field name

When modifying the field name in MYSQL, you need to add the data type of the field, otherwise an error will be reported, and CHANGE can also only modify the data type to achieve the same effect as MODIFY

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

Change the data type, for example, in the example just now, change the id column Change to bigint data type

Modify field name

7. Add field

The syntax for adding fields is similar, but in MYSQL you can use the FIRST and AFTER keywords to specify the location of the added fields

8. Delete fields

MYSQL deletes fields without adding the COLUMN keyword

9. Delete Foreign key constraints

The methods of deleting constraints in MYSQL and SQLSERVER are also very different

In SQLSERVER, whether it is a unique constraint, a check constraint or a foreign key constraint, you can use the following SQL statement Delete

But in MYSQL, if it is a foreign key constraint, you need to use DROP FOREIGN KEY. If it is a primary key constraint, you need to use DROP PRIMARY KEY, which is a bit troublesome

10. Delete a table

The syntax for deleting a table is the same

But if you want to delete multiple tables at the same time or make a judgment before deleting, MYSQL is much more convenient

SQLSERVER needs one table at a time Judge, then drop one table one by one

MYSQL is different, the syntax is very concise: DROP TABLE IF EXISTS emp1, emp2

Summary

This article is just a brief introduction Let’s take a look at the syntax differences between MYSQL and SQLSERVER

I will write more articles about the differences between MYSQL and SQLERVER in the future, as well as some of my experiences using MYSQL during this period. Please stay tuned o(∩_∩ )o

If there is anything wrong, please feel free to comment o(∩_∩)o

2014-7-16Added

No matter innodb engine or MYISAM engine table, there can only be one auto-increment column, and the auto-increment column must be an index column, whether it is a secondary index or a primary key index

This is the same as SQLSERVER Different, SQLSERVER allows a table to have multiple auto-increment columns, and there is no need to create indexes on the auto-increment columns.

The above is the content of MySQL Learning Series 1: Simple Syntax. For more related content, please pay attention to PHP Chinese website (www.php.cn)!