How to use SQL alert table to modify the table structure
The alter table statement is used to add, modify, or delete columns in an existing TABLE.
ADD [COLUMN] column name (column definitions) [FIRST or AFTER column_name]
Add index [index_name] (column_list)
Add primary key (column_list)
Add unique [index_name] (column_list)
ALTER [COLUMN] column_name {set default default_value or drop default}
CHANGE [COLUMN] old_col_name create_definition
DROP [COLUMN] col_name
DROP PRIMARY KEY
Drop index index_name
MODIFY [COLUMN] create_definition
RENAME [AS] new_tbl_name
The IGNORE keyword causes rows with duplicate values in unique keys to be deleted;
Otherwise, nothing happens.
Alter table employee add column Account_Number INT
Alter table employee add index (ID)
Alter table employee add primary key (ID)
Alter table employee add unique (ID)
Alter table employee change id salary INT
Alter table employee DROP Customer_ID
Alter table employee DROP PRIMARY KEY
Alter table employee drop index Customer_ID
Alter table employee MODIFY First_Name varchar (100)
Alter table employee RENAME Customer
Add a primary key to the table
Mysql tutorial>
Mysql>
Mysql> create table myTable (
-> ID SMALLINT
-> );
Query OK, 0 rows affected (0.02 sec)
Mysql>
Mysql> desc myTable;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| ID | smallint (6) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
1 row in set (0.00 sec)
Mysql>
Mysql> alter table myTable
-> Add column Quantity smallint unsigned not null,
-> Modify id smallint unsigned not null,
-> Add primary key (ID );
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql>
Mysql> desc myTable;
+ ---------- + ---------------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ---------------------- + ------ + ----- + --------- + ------- +
| ID | smallint (5) unsigned | NO | PRI |
| Quantity | smallint (5) unsigned | NO |
+ ---------- + ---------------------- + ------ + ----- + --------- + ------- +
2 rows in set (0.02 sec)
Mysql>
Mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
Mysql>
Alter table column name
Mysql>
Mysql> alter table myTable
-> Drop column Quantity,
-> Drop primary key;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0