Modify a table in mysql

Modify Table Structure:
?

1	Alter [ignore] table <table specification> <table structure change>

This includes changing table attributes, Changing Column attributes, and changing integrity constraints.

 

1. Table attribute change

1.1 rename a table name

?

1 2	Mysql> alter table student rename to s; Query OK, 0 rows affected (0.03 sec)

You can also use the rename command to rename the name:

?

1 2	Mysql> rename table s to student; Query OK, 0 rows affected( 0.13 sec)

1.2 table sorting changes

?

1 2 3 4 5 6 7 8 9 10

Mysql> alter table student order by stu_id desc; mysql> select * from student; + -------- + ---------- + --------- + ----------- + | stu_id | stu_name | stu_tel | stu_score | + -------- + ---------- + --------- + ----------- + | 4 | d | 154 | 63 | 3 | c | 153 | 62 | 2 | B | 152 | 61 | 1 | a | 151 | 60 | + -------- + ---------- + --------- + ----------- +

2 column attribute changes

2.1 Add Columns

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Mysql> alter table student-> add sex char (1) after stu_name; Query OK, 4 rows affected (0.34 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from student; + -------- + ---------- + ------ + --------- + ----------- + | stu_id | stu_name | sex | stu_tel | stu_score | + -------- + ---------- + ------ + --------- + ----------- + | 1 | a | NULL | 151 | 60 | 2 | B | NULL | 152 | 61 | 3 | c | NULL | 153 | 62 | 4 | d | NULL | 154 | 63 | + -------- + ---------- + ------ + --------- + ----------- + 4 rows in set (0.02 sec)

The newly added columns are placed in the last column by default, and null values are filled by default. Here, after is used to specify the new column sex after stu_name. if the new column setting cannot be blank, mysql will enter the actual value based on the column data type: enter 0 for the value, and enter a null string for the string, set the date to 0000-00-00, and the time to 00:00:00.

2.2 Delete Columns

?

1 2 3 4

Mysql> alter table student-> drop sex; Query OK, 4 rows affected (0.33 sec) Records: 4 Duplicates: 0 Warnings: 0

2.3 Modify column attributes

Initial column attributes:

?

1 2 3 4 5 6 7 8

+ Bytes + ----------- + | column_name | data_type | + ------------- + ----------- + | stu_id | int | stu_name | varchar | stu_tel | int | stu_score | int | + ------------- + ----------- +

Change the stu_tel column to the tel char type and put it behind stu_score.

?

1 2 3 4 5 6 7 8 9 10 11

Mysql> alter table student-> change stu_tel char (3) after stu_score; Query OK, 4 rows affected (0.23 sec) + bytes + ----------- + | column_name | data_type | + ------------- + ----------- + | stu_id | int | stu_name | varchar | stu_score | int | tel | char | + ------------- + ----------- +

You can use the modify parameter to modify other attributes of a column without changing the column name.

Change tel column back to int type

?

1 2 3 4 5 6 7 8 9 10 11 12

Mysql> alter table student-> modify tel int (3); Query OK, 4 rows affected (0.25 sec) Records: 4 Duplicates: 0 Warnings: 0 + bytes + ----------- + | column_name | data_type | + ------------- + ----------- + | stu_id | int | stu_name | varchar | stu_score | int | tel | int | + ------------- + ----------- +

3. integrity constraints change

?

1 2 3 4 5 6 7 8

<Integrity constraint change >:= add primary key <index name> add unique <index name> add foreign key <index name> (column list) referencing <specification> add check <condition> drop primary key drop foreign key <index name> drop constraint <constraint name>

You can add [constraint ] Defines the name of the Integrity Constraint.

Delete primary key:

?

1	Mysql> alter table student drop primary key;

Add primary key:

?

1	Mysql> alter table student add primary key (stu_id );