MySQL optimization--Simple syntax

Reprint Please specify source: http://blog.csdn.net/l1028386804/article/details/46559271

1. Default Constraints

    --mysql    CREATE TABLE emp    (    ID INT DEFAULT    )

2, set the self-increment column mysql must be an indexed column, set the seed value to be set after the table

  --mysql    --Set the self-increment ID to 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, can be divided into the global level and session level, if it is a conversation level, then when the user creates a new session, then the step back to the global level of MySQL can not 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 against 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)  

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

DESC EMP

4. Modify the table name

ALTER TABLE EMP RENAME EMP2

5. Modify the data type of the field to change the ID field's int type to bigint

ALTER TABLE emp2 MODIFY ID BIGINT

6, modify the field name in MySQL to modify the field name when you need to add the field data type otherwise it will be an error, and change can only modify the data type, to achieve and modify the same effect, by setting the SQL statement "new field name" and "old field name" to the same name, only changes " Data type, change the data type, such as the example just now, change the ID column to the bigint data type

ALTER TABLE EMP2 change ID ID BIGINT

7. Add fields

ALTER TABLE emp2 ADD NAME NVARCHAR ($)  NULL

8. Delete field MySQL Delete field does not need to add the column keyword

ALTER TABLE emp2 DROP NAME

9, delete foreign KEY constraint if it is a foreign key constraint, you need to use drop FOREIGN key, if the primary KEY constraint requires the use of the drop PRIMARY key
--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

DROP TABLE EMP2

But if you want to delete multiple tables at the same time or delete them before you decide

DROP TABLE IF EXISTS emp1, EMP2

Add:

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

MySQL optimization--Simple syntax