Sqlalert add, modify, and delete field syntax instances

Sqlalert syntax for adding, modifying, and deleting field syntax instance altertable: altertable & quot; table_name & quot; the detailed Writing Method of [change method] varies according to the target we want to achieve. In the changes listed above, the [change method] is as follows: add a column: add & quot; column 1 & quot; column 1 Data Type & quot; delete An SQL alert addition, modification, and deletion field syntax instance
The alter table syntax is as follows:

Alter table "table_name"
[Change method]

The detailed Writing Method of [change method] varies depending on what we want to achieve. In the changes listed above, the [change method] is as follows:

Add a column: add "column 1" "column 1 data type"
Delete a field: drop "column 1"
Change Field name: change "original column name" "new column name" "new column name data type"
Change the Data Type of the column: modify "column 1" "New data type"

Primary Key

Alter table tabelname add new_field_id int (5) unsigned default 0 not null auto_increment, add primary key (new_field_id );

// Add a new column

Alter table t2 add d timestamp;
Alter table infos add ex tinyint not null default '0 ';
// Delete a column

Alter table t2 drop column c;
// Rename a column

Alter table t1 change a B integer;

// Change the column type

Alter table t1 change B bigint not null;
Alter table infos change list tinyint not null default '0 ';

// Rename the table

Alter table t1 rename t2;
Add Index

> Alter table tablename change depno int (5) not null;
Mysql> alter table tablename add index name (field name 1 [, field name 2…]);
Mysql> alter table tablename add index emp_name (name );
Index with primary keywords

Mysql> alter table tablename add primary key (id );
Add an index with unique conditions

Mysql> alter table tablename add unique emp_name2 (cardnumber );
Delete An index

Mysql> alter table tablename drop index emp_name;
Modify Table:

Add field:

Mysql> alter table table_name add field_name field_type;
Modify the original field name and type:

Mysql> alter table table_name change old_field_name new_field_name field_type;
Delete field:

Mysql> alter table table_name drop field_name;


Alter table statement
The alter table statement is used to add, modify, or delete columns in an existing table.

SQL alter table syntax
To add columns to a table, use the following syntax:

Alter table table_name
Add column_name datatype
To delete columns in a table, use the following syntax:

Alter table table_name
Drop column column_name
Note: Some systems do not allow this method of deleting columns in database tables (drop column column_name ).

To change the data type of columns in a table, use the following syntax:

Alter table table_name
Alter column column_name datatype
Original table (used in the example ):
Persons table:

Id lastname firstname address city
1 adams john oxford street london
2 bush george fifth avenue new york
3 carter thomas changan street beijing
SQL alter table instance
Now, we want to add a new column named "birthday" to the table "persons.

We use the following SQL statement:

Alter table persons
Add birthday date
Note that the type of the new column "birthday" is date, which can be used to store dates. The data type specifies the type of data that can be stored in the column.

The new "persons" table is similar to the following:

Id lastname firstname address city birthday
1 adams john oxford street london
2 bush george fifth avenue new york
3 carter thomas changan street beijing
Change Data Type instance
Now we want to change the data type of the "birthday" column in the "persons" table.

We use the following SQL statement:

Alter table persons
Alter column birthday year
Note that the data type of the "birthday" column is year, which can be two or four-digit years.
Drop column instance
Next, we will delete the "birthday" column in the "person" table:

Alter table person
The drop column birthdaypersons table will become like this:

?>