My MYSQL Study Notes (1), MYSQL Study Notes (

My MYSQL Study Notes (1), MYSQL Study Notes (

This is my personal notes for learning MYSQL through online articles. I hope this will help you.
1. DEFUALT keywords

CREATE TABLE emp(id INT DEFAULT 12)

2. Set the auto-increment column (auto_increment)

create  table temp2(    id INT primary KEY auto_increment,    tname INT)

The step size cannot be set for the mysql auto-increment column.
View the default auto-incrementing column seed and step size.

SHOW GLOBAL VARIABLES LIKE 'auto_incre%'

Q: if there is a table with an auto-increment primary key whose field is id, after 10 data entries have been inserted into the table, the data with id and 10 has been deleted, restart mysql,

Then insert a data record. What is the id of the data record, which is 8 or 11?
A: If the table type is MyISAM, it is 11. If the table type is InnoDB, the id is 8.
This is because the two types of storage engines store the maximum ID records in different ways, MyISAM table records the maximum ID to the data file, the maximum ID value of the auto-incrementing primary key of mysql is not lost;
While InnoDB records the maximum ID value to the memory. Therefore, after you restart mysql or OPTIMIZE the table, the maximum ID value will be lost.
By the way, MYSQL can get the auto-increment value of the current table.
1. SELECTMAX (id) FROM person for specific tables (problems may occur in the case of multithreading)
2. SELECT LAST_INSERT_ID () function for any table (this method is used in auto_increment. Do not use it in the primary key column explicitly)
3. SELECT @ identity for any table (same as above, do not switch to LAST_INSERT_ID)
@ 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 for specific tables.
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.
(SQL statement for obtaining this value:

SELECT table_name,Auto_increment,Engine,Version,Row_format,table_rows,Avg_row_length,  Data_length,Max_data_length,Index_length,Data_free,  Create_time,Update_time,Check_time,table_collation,Checksum,  Create_options,table_commentFROM information_schema.`TABLES`WHERE Table_Schema='test'AND table_name = 'temp1'

)

3. View table Definitions

DESC tempt

4. Modify the table name

ALTER TABLE tempt RENAME temp1

5. Modify the field data type

ALTER TABLE temp1 MODIFY tname INT(20)

The MODIFY keyword can also be used to change the position of an existing column.

ALTER TABLE temp3 MODIFY tage INT AFTER tname

Before change

After the change

6. Modify the field name

ALTER TABLE temp1 CHANGE id myid BIGINT

The CHANGE command not only changes the field name but also the data type. Of course, it can only CHANGE the type.

7. Add Fields

ALTER TABLE temp3 ADD tsex INT

8. Delete Fields

ALTER TABLE temp3 DROP tsex

9. Delete Constraints

-- Delete the foreign key constraint 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

DROP TABLE temp3

Delete multiple tables at the same time and verify whether the table exists

DROP TABLE IF EXISTS temp1,temp3

11. view the actual storage length of a field

SELECT LENGTH(id) FROM temp

Refer:
Http://www.cnblogs.com/lyhabc/p/3691555.html