mysql-table Operation

Second, table operation

1. Create a table

Syntax: (Create TableTable name (field 1 type constraint, field 2 type constraint, field 3 type constraint,.        .    .           ， .        .    . );)1, create a table (the fields in the table have no constraints)Create Tabletb_student (IDint,//table with no constraints create nameChar( -), sex enum (' Male ', ' female ');2, create a table (add constraint: unsigned,Primary Key、 not NULL)Create Tabletb_student (IDintUnsignedPrimary Key,//There is a PRIMARY key constraint, with no symbolic constraints, where the PRIMARY key constraint can be placed at the end, the role of the PRIMARY KEY constraint: To ensure the uniqueness of the record. NameChar( -) not NULL, a primary key is a unique identifier for a row, a primary key can consist of a field or multiple fields, and a primary key can be used to uniquely determine a record in a table. Weightfloat(5,2)); 3, create tables (table-level constraints are added after all fields)Create Tabletb_student (IDintUnsigned,nameChar( -) not NULL,//a non-null constraint is a column-level constraint that can only be declared after a field weightfloat(5,2),Primary Key(ID)//Place the PRIMARY KEY constraint at the end of the field Declaration); 4, create a tableCreate Tabletb_student (IDintunsigned auto_increment, auto_increment is self-growing and must be used in conjunction with the primary key. NameChar( -) not NULL, Weightfloat(5,2) not NULL, sex enum ('male','female') not NULL,Primary Key(ID),Unique(name));

2. Delete a table

Drop  table name;

3, modify the table

1, copy table:Create TableTb2_nameSelect *  fromTb_name; 2, create a temporary table:Create Temporary TableTb_name; 3, rename the table:Alter TableOld_tb_name Rename toNew_tb_name;4, delete a tableDrop TableTb_name;5, show table structure Show columns fromTb_name; descTb_name;6, show creation process showCreate TableTb_name;7, display index related information showIndex  fromtb_name\g;8、AlterModify Operation//Delete a fieldAlter TableTb_nameDropField_name; //Add FieldAlter TableTb_nameAddField_name Field_typeconstraint; //Add multiple fieldsAlter TableTb_nameAdd(Field1_name Field1_typeconstraint, Field2_name Field2_typeconstraint ...); //Add a field to a locationAlter TableTb_nameAddField_name Field_typeconstraintAfter Field_name; 9, change use:Alter TableTb_student Change Name[To/as]Student_name Column_typeconstraint; (the same is true for renaming columns, which replaces the original field with a new field)Ten, adding descriptive information to the tableExecuteTb_student N'ms_description','Personnel Information Sheet'N'User'N'dbo'N'TABLE'N'Table name',NULL,NULL One, add descriptive information for field usernameExecuteTb_student N'ms_description','name'N'User'N'dbo'N'TABLE'N'Table name'N'column'N'Username' A, add descriptive information for field sexExecuteTb_student N'ms_description','Sex'N'User'N'dbo'N'TABLE'N'Table name'N'column'N'Sex' -, update the Description property of the column username in the table:ExecuteTb_student'ms_description','the new name','User'Dbo'TABLE','Table name','column','UserName' -, delete the Description property of the column username in the table:ExecuteTb_student'ms_description','User'Dbo'TABLE','Table name','column','Username'"link" MySQL Storage process detailed: http://Blog.csdn.net/a460550542/Article/Details/20395225"link" http://Blog.csdn.net/Icanhaha/Article/Details/46965853advantages and disadvantages of the "link" Index http://Blog.csdn.net/qq247300948/Article/Details/23675843"link" in MySQL for various engine http://Blog.csdn.net/Gaohuanjie/Article/Details/50944782 

mysql-table Operation