Turn MySQL Learning (i)

The first phase is mainly to learn the basic grammar of MySQL, and there are second, third, fourth, we kindly expect O (∩_∩) o

Differences in syntax

I'm mainly talking about grammatical differences here.

1. Default Constraints

Difference: MySQL does not use parentheses after the default keyword

--sqlservercreate table emp (ID int default ())--mysqlcreate table emp (ID int default 12)

2. Set the self-increment column

MySQL's self-increment column must be an indexed column, set the seed value to be set after the table

--Set the self-Increment column--sqlservercreate table emp    (      ID int IDENTITY (1, 1)    )--mysql--set the self-increment ID start from n create TABLE emp (ID int  PRIMARY KEY auto_increment) auto_increment = 100; --(set self-increment ID starting from 100)

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

If it is a session level, then when the user creates a new session, then the STRIDE is back to the global level, so the step size of MySQL is very different from that of SQL Server.

MySQL cannot be set to table-level stride!!

The MySQL server maintains 2 types of MySQL system parameters (System variables): Global variables (Globals variables) and session variables (session variables).

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

Changes to it will affect all new sessions (the session is not affected at the time of the change) until the next MySQL server restart.

Note that the change effect cannot be restarted, and if you want to use the new value again when you restart the MySQL server, you can only specify it by specifying the variable option in the command line or by changing the options file.

And the set change is not up to the cross-restart.
Each system variable has a default value, which is determined by the time the MySQL system is compiled.

For system variables, you can typically specify options at the command line at server startup or through an option file.

Of course, most system variables can be specified by the SET command when the system is running.

View the system's current default self-added column seed value and step value

SHOW GLOBAL VARIABLES like ' auto_incre% '; --Global variables

Q: If there is a table, there is a field ID of the self-increment primary key, when the table has been inserted 10 data, deleted the ID of 8,9,10 data, and then restart MySQL,

After inserting a piece of data, what should the ID value of this data be, 8, or 11?
A: If the table type is MyISAM, then it is 11. If the table is of type InnoDB, the ID is 8.
This is because the maximum ID records stored by the two types of storage engines are different, the MyISAM table records the maximum ID into the data file, and the maximum ID value for restarting the MySQL self-increment primary key is not lost;
InnoDB, in turn, records the maximum ID value into memory, so the maximum ID value will be lost after restarting MySQL or optimize the table.

By the way, MySQL. Four ways to get the self-increment of the current table

1. SELECT MAX (ID) from the person for a specific table

2. SELECT last_insert_id () function for any table

3. SELECT @ @identity for any table

@ @identity is a system-defined global variable that represents the last time the value of the self-increment column for inserting data into a table with the identity attribute (that is, the self-increment column).

General system-defined global variables start with @@ 开头 and user-defined variables begin with @.

The @ @identity is used only if the connection is not closed when the insert operation is executed, or a null value is obtained.

4. SHOW TABLE STATUS like ' person '

This method is recommended if you are targeting a specific table

The result is a auto_increment field in the corresponding table name record, and the value of the next self-increment ID is the maximum self-increment ID of the current table.

3. View the table definition

Sql server

EXEC sp_help ' emp '

Mysql

DESC EMP

4. Modify the table name

Modifying table names is also different, changing the table emp to EMP2

--sqlserverexec sys. [sp_rename] @objname = N ' emp ',--nvarchar (1035)    @newname = ' emp2 '--sysname--mysqlalter TABLE emp Rename EMP2

5. Modify the data type of the field

Change the ID field's int type to bigint

--sqlserveralter TABLE [dbo]. [EMP2] ALTER COLUMN [id] bigint--mysqlalter TABLE emp2 MODIFY ID BIGINT

6. Modify field names

MySQL changes the field name when you need to add the field data type otherwise will be error, and change can only modify the data type, to achieve and modify the same effect

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

Change the data type, such as the example just now, to change the ID column to the bigint data type

ALTER TABLE EMP2 change ID ID BIGINT

Modify field names

--sqlserverexec sys. [sp_rename] @objname = N ' emp2.id ',--nvarchar (1035)    @newname = ' iid ',--sysname    @objtype = ' column '--varchar (1 3)--mysqlalter TABLE emp2 change ID IID BIGINT

7. Add fields

The syntax for adding fields is similar, but MySQL can use the first and after keywords to specify where to add fields

--sqlserveralter TABLE [dbo]. [EMP2] Add name NVARCHAR ($) null--mysqlalter TABLE emp2 ADD name NVARCHAR ($)  null

8. Delete fields

MySQL Delete field does not need to add the column keyword

--sqlserveralter TABLE [dbo]. [EMP2] Drop COLUMN name--mysqlalter TABLE EMP2 DROP Name

9. Delete FOREIGN KEY constraints

There's a big difference between MySQL and SQL Server's way of removing constraints.

In SQL Server, either the unique constraint, the check constraint, or the FOREIGN key constraint can be removed by using the following SQL statement

ALTER table name DROP CONSTRAINT constraint name

But inside MySQL, if it's a foreign key constraint, you need to use Drop FOREIGN key, and if it's a PRIMARY KEY constraint you need to use Drop PRIMARY key, a bit of a hassle

--sqlserveralter table dbo.emp2 Drop CONSTRAINT fk_emp_dept--mysql--Delete foreign KEY constraint alter TABLE EMP2 drop FOREIGN KEY fk_emp_dept-- Delete PRIMARY KEY constraint alter TABLE EMP2 DROP PRIMARY KEY pk_emp_dept

10. Delete a table

syntax to delete a table two of them are the same.

--sqlserverdrop TABLE [dbo]. [Emp2]--mysqldrop TABLE EMP2

However, if you want to delete multiple tables at the same time or delete before you have to judge, MySQL is much more convenient

--sqlserverif (object_id (' dbo.emp2 ') is not NULL) DROP TABLE [dbo]. [Emp2]--mysqldrop TABLE IF EXISTS emp1, EMP2

SQL Server needs a table to judge, then a table drop

MySQL is not the same, the syntax is very concise: DROP TABLE IF EXISTS emp1, EMP2

Summarize

This article simply introduces the syntax differences between MySQL and SQL Server.

I will write more about the differences between MySQL and Sqlerver, and some of my experiences during my time using MySQL, please look forward to O (∩_∩) o

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

2014-7-16 Supplement

Use test;--MyISAM engine create TABLE test (ID int unsigned NOT NULL auto_increment,name varchar (TEN) NOT NULL,  key (Name,id)) Engine=myisam auto_increment=100;--InnoDB engine Create TABLE testidentity (ID int unsigned not   null Auto_increment,nid INT UNSIGNED, name varchar (TEN) NOT NULL,  key (ID)) Engine=innodb auto_increment=100;--or primary key create TABLE testidentity ( ID int unsigned not   null Auto_increment,nid int unsigned, name varchar (TEN) NOT NULL,  key (ID)) Engine=innodb auto_i ncrement=100; [Database4] ErrorCode: -2147467259, number:1075errormessage:incorrect table definition; There can is only one auto column and it must is defined as a keyalter table testidentity modify column nid int Auto_incre ment;

There can only be one self-increment in the table of the InnoDB engine or the MyISAM engine , and the self-increment column must be an indexed column, whether it is a two-level index or a primary key index

This is not the same as SQL Server, SQL Server allows multiple self-increment columns for a table, and does not need to create an index on the self-increment column

Thanks for the reminder from the park friend Owen ~

2015-6-23 Supplement

About the questions in the article

Q: If there is a table, there is a field ID of the self-increment primary key, when the table has been inserted 10 data, deleted the ID of 8,9,10 data, and then restart MySQL,

After inserting a piece of data, what should the ID value of this data be, 8, or 11?
A: If the table type is MyISAM, then it is 11. If the table is of type InnoDB, the ID is 8.
This is because the maximum ID records stored by the two types of storage engines are different, the MyISAM table records the maximum ID into the data file, and the maximum ID value for restarting the MySQL self-increment primary key is not lost;
InnoDB, in turn, records the maximum ID value into memory, so the maximum ID value will be lost after restarting MySQL or optimize the table .

This is a bug in the InnoDB storage engine.

Please see:

http://www.cnblogs.com/justfortaste/p/3759807.html#3210323

https://bugs.mysql.com/bug.php?id=199

Turn MySQL Learning (i)