Use of the Oracle database ALTER clause

Only for learning, review, in case forget, warm so know new!

This is mainly about is the ALTER the use of words.

First of all, I have a table that is only for learning. This table does not have a primary key constraint, a non-null constraint, or a FOREIGN key constraint.

650) this.width=650; "Width=" 379 "height=" 117 "src="/e/u261/themes/default/images/spacer.gif "hspace=" style= " Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" spacer.gif "/>

now to learn Or Review The use of the ALTER TABLE statement.

• Use Alter-drop to remove the abbr column from the table above.

ALTER TABLE province DROP COLUMN ABBR;

with SELECT * fromprovince; query again, ABBR This column has been deleted.

650) this.width=650; "Width=" 338 "height=" "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

with ALTER Table-add To add a column named "ABBR"

ALTER TABLE Province ADD ABBR Varchar2 (2);

650) this.width=650; "Width=" 379 "height=" 117 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

after that, I want to give ABBR fill in a value, the Chinese pinyin abbreviation for the Beijing ABBR this column.

INSERT intop rovince (Abbr) VALUES (' BJ ');

and then look at the result of the query is wrong, BJ was inserted into a new one by itself . Row .

650) this.width=650; "Width=" 379 "height=" 124 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

because The INSERT sentence cannot be used with the WHERE clause, so the BJ is filled with the Beijing column with the UPDATE clause .

UPDATE provinceset ABBR = ' BJ '
WHERE capital = ' Beijing ';

650) this.width=650; "Width=" 381 "height=" 126 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

with DELETE clause to remove line fifth

DELETE from Province
WHERE ABBR = ' BJ ' and capital are NULL;

650) this.width=650; "Width=" 381 "height=" 106 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

with Alter---MODIFY modify The data length of the ABBR column

ALTER TABLE Province modif yabbr Varchar2 (4);

Summarize the use of the alter sentence

with alter table 4

1. Add a new column

2. Delete a column that already exists

3. Modifying the data type of a column and the range of values

4. Change a name to a column

The syntax structure of ALTER TABLE is as follows:

ALTER TABLE TABLE_ NAME 
 {
 ADD column_name data_type [Column attribute]| 
 DROP COLUMN column_name| 
 MODIFY column_name data_type [Column attribute] 
} 
  

It is important to note that the DROP when the DROP COLUMN and the name above, compared with ADD and the MODIFY come on, there's more. COLUMN this keyword.

Example of changing a column name to a table:

ALTER TABLE Province RENAME COLUMN Capital to Capitalcity;

in addition, ALTER words can also be used to modify constraints on a table, add constraints, delete constraints, enable or disable constraints.

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://10614070.blog.51cto.com/10604070/1686203

Use of the Oracle database ALTER clause