Create TABLE, add, modify introduction in MySQL

Building a table: the process of declaring a column

The code is as follows	Copy Code
CREATE TABLE Table name ( Column name 1 column 2 Type column 1 parameter, Column Name 2 column 2 Type column 2 parameter, ...... Declaration column n parameter of column n ) Engine myisam/innodb/bdb charset utf8/gbk/latin1 ...; Modify the syntax of the table: Alter [IGNORE] table Tbl_name alter_spec [, Alter_spec ...] in the MySQL alter syntax

After a table is created, it is possible to add or delete or modify columns

To add a column:

The code is as follows	Copy Code
ALTER TABLE name add column Name column type column number (added column at end of table) ALTER TABLE name add column Name column type column parameter after a column name (specified after adding to a column) ALTER TABLE name add column Name column type column parameter first; (Add the new column to the front)

To delete a column:

The code is as follows	Copy Code
ALTER TABLE name drop column name

To modify a column:

The code is as follows	Copy Code
ALTER TABLE name modify column list type column parameter;

To modify the column name and column type:

The code is as follows	Copy Code
ALTER TABLE name change old column name new column name new type new parameter;

Example 1

Add index

The code is as follows	Copy Code
Mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]);

Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee ADD index emp_name (name);

Index of the Primary keyword

The code is as follows	Copy Code
Mysql> ALTER TABLE name add primary key (field name);

Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee ADD primary key (ID);

Index with unique restriction criteria

The code is as follows	Copy Code
Mysql> ALTER TABLE name add unique index name (field name);

Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee add unique emp_name2 (cardnumber);

MySQL alter syntax Usage: View index of a table

The code is as follows	Copy Code
Mysql> Show index from table name;

Example:

The code is as follows	Copy Code
Mysql> Show index from employee;

Delete an index

The code is as follows	Copy Code
mysql> ALTER TABLE name DROP INDEX index name;

Index of PRIMARY Key 549830479

The code is as follows	Copy Code
Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);

Index with unique restrictions 549830479

The code is as follows	Copy Code
Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);

Example:

The code is as follows	Copy Code
Mysql>alter table employee DROP index emp_name;

Modify table: Add Field:

The code is as follows	Copy Code
mysql> ALTER TABLE table_name ADD field_name Field_type;

View table:

The code is as follows	Copy Code
Mysql> SELECT * FROM table_name;

To modify the original field name and type:

The code is as follows	Copy Code
mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;

To delete a field:

code is as follows	copy code
mysql ALTER TABLE table_name DROP field_name