Table structure example of ALTER ALTER MYSQL database

In the process of using a database, it is sometimes necessary to change its table structure, including changing the name of the field, or even changing the relationship between different database fields. The command that can achieve this change is alter, whose basic syntax is as follows:
Altertabletable_namealter_spec[,alter_spec ...
1: Delete Column

Altertable "table name" DROP "column name"

2: Adding columns

Altertable "table name" ADD "column name" intnotnullcomment ' annotation description '
3: Modify the column's type information
Altertable "Table name" Change "column name" new column name (this can be used with the same name as the original column) "Bigintnotnullcomment ' comment description '
4: Renaming a column
Altertable "Table name" Change "column Name" "New column Name" bigintnotnullcomment ' annotation description '
5: Renaming tables
Altertable "table name" RENAME "Table new name"
6: Delete the primary key in the table
Altertable "Table name" Dropprimarykey
7: Add primary key
Altertable "Table name" Addconstraintpk_sj_resource_chargesprimarykey (Resid,resfromid)
8: Add index
Altertable "Table name" Addindexindex_name (name);
9: Add a unique restricted condition index
Altertable "Table name" Adduniqueemp_name2 (Cardnumber);
10: Delete Index
Altertable "table name" Dropindexemp_name;

Add

1.alter Action table field

(1) Add field

Altertable table name Add field name segment type;

Altertablestudentaddnamevarchar (10);

(2) Modify the field

altertable Table name change old field name new field name segment type;

Altertable Table name Modify field name segment type;//modify field type

Altertablestudentchangenamenamevarchar (m) notnulldefault ' liming '//Modify field type default is

Default value for field

Altertablestudentchangenamename1varchar (m) notnulldefault ' liming ';//Modify field name

(3) Delete field

Altertable Table Name drop field name;

Altertablestudentdropname;

2.alter Index operation

(1) Add index

altertable table name Addindex index name (field name 1, field Name 2 ....) ）；

Altertablestudentaddindexstu_name (name);

(2) Delete index

altertable table name Dropindex index name;

Altertablestudentdropindexstu_name;

(3) View the index of a table

Showindexfrom table name;

(4) Increase the index of the unique restriction condition

altertable table name Addunique index name (field name);

3. Primary KEY operation

To add a primary key:

altertable Table name Addprimarykey (field name);

Delete primary key:

altertable table name Dropprimarykey (primary key is not automatic growth)

Altertable Table name Modify Field field type, Dropprimarykey (primary key is automatic growth)

Altertable123modifyidint,dropprimarykey;