SQL alert adding, modifying, and deleting field syntax instances

SQL alert adding, modifying, and deleting field syntax instances

The syntax for ALTER TABLE is as follows:

ALTER TABLE "TABLE_NAME"

[Change Mode]

The detailed wording of the [change mode] will vary according to the goal we want to achieve. In the changes listed above, the [change mode] is as follows:

Add a field: Add "Field 1" "Field 1 Data category"

Delete a field: Drop "Field 1"

Changing field Name: Change "original field name" "New Field Name" "New Field name data type"

Change field Data type: Modify "Field 1" "New data category"

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 Column

ALTER TABLE T2 drop column C;

renaming columns

ALTER TABLE T1 change a b integer;

Change the type of a column

ALTER TABLE T1 change b b bigint not null;

ALTER TABLE infos change list tinyint not null default ' 0 ';

Renaming tables

ALTER TABLE t1 rename T2;

Add index

MySQL tutorial > ALTER TABLE tablename change depno depno int (5) is not null;

Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);

Mysql> ALTER TABLE tablename Add index Emp_name (name);

Index of the Primary keyword

Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);

Index with unique restriction criteria

Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);

Delete an index

Mysql>alter table tablename DROP index emp_name;

To modify a table:

Add fields:

Mysql> ALTER TABLE table_name add Field_name field_type;

To modify the original field name and type:

Mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;

To delete a 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

If you want to add columns to the table, use the following syntax:

ALTER TABLE TABLE_NAME

Add column_name datatype

To delete a column from a table, use the following syntax:

ALTER TABLE TABLE_NAME

Drop Column column_name

Note: Some database tutorial systems do not allow such a way to delete columns in a database table (drop column column_name).

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

ALTER TABLE TABLE_NAME

ALTER COLUMN COLUMN_NAME datatype

The original table (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" in Table "persons".

We use the following SQL statements:

ALTER TABLE persons

Add Birthday Date

Note that the type of the new column "Birthday" is date and can hold the date. Data types Specify the types of data that can be stored in a column.

The new "persons" table looks like this:

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

Changing data type instances

Now we want to change the data type of the "Birthday" column in the "Persons" table.

We use the following SQL statements:

ALTER TABLE persons

Alter column birthday year

Note that the data type of the "Birthday" column is year, which can hold 2-bit or 4-bit years.

Drop Column Instance

Next, we delete the "Birthday" column in the person table:

ALTER TABLE person

The Drop Column Birthdaypersons table becomes this way:

?>