Oracle Database Build table, delete fields, add fields, modify fields, modify fields ...

1. Create a table with Oracle:

Sql> Create TableLoginuser (2Id Number(Ten,2),  3Usernamevarchar( -),  4Passwordvarchar( -),  5Emailvarchar( -),  6Descriablevarchar2( $),  7RegDate date);

Syntax for creating tables:

CREATE TABLE table_name (    columns_name datatype,......);

Display information for creating tables:

The table is created. SQL> descUserInfo; is the name empty? type----------------------------------------- -------- ----------------------------Id Number(6) USERNAMEVARCHAR2( -) PASSWORDVARCHAR2( -) EMAILVARCHAR2( -) regdate DATE

2. Add a field information to the table:

syntax for adding fields to a table:

　　

ALTER TABLE table_name     ADD column_name datatype;

Sql> Alter TableUserInfo2  AddRemarksvarchar2( -); The table has changed. SQL> descUserInfo; is the name empty? type----------------------------------------- -------- ----------------------------Id Number(6) USERNAMEVARCHAR2( -) PASSWORDVARCHAR2( -) EMAILVARCHAR2( -) regdate DATE REMARKSVARCHAR2( -)

3. Modify the field data information in the table:

Grammar:

　　

ALTER TABLE table_name    MODIFY column_name datatype;

Sql> Alter TableUserInfo2Modify password Number(6,0); The table has changed. SQL> descUserInfo; is the name empty? type----------------------------------------- -------- ----------------------------Id Number(6) USERNAMEVARCHAR2( -) PASSWORD Number(6) EMAILVARCHAR2( -) regdate DATE REMARKSVARCHAR2( -)

4. Delete The fields in the table:

Delete syntax:

　　

ALTER TABLE table_name     DROP COLUMN column_name;

Sql> Alter TableUserInfo2  Drop columnremarks; The table has changed. SQL> descUserInfo; is the name empty? type----------------------------------------- -------- ----------------------------Id Number(6) USERNAMEVARCHAR2( -) PASSWORD Number(6) EMAILVARCHAR2( -) regdate DATE

5. Modify the field name:

To modify the syntax:

　　

ALTER TABLE  COLUMN to New_column_name;

Sql> Alter TableUserInfo2RenamecolumnEmail toNew_email; The table has changed. SQL> descUserInfo; is the name empty? type----------------------------------------- -------- ----------------------------Id Number(6) USERNAMEVARCHAR2( -) PASSWORD Number(6) New_emailVARCHAR2( -) regdate Datesql>

6. Change the name of the table:

To modify the syntax:

　　

 to New_table_name;

Sql>Rename UserInfo tonew_userinfo; The table has been renamed. SQL> descUserInfo; Error:ora-04043: Object userinfo does not exist SQL> descNew_userinfo; is the name empty? type----------------------------------------- -------- ----------------------------Id Number(6) USERNAMEVARCHAR2( -) PASSWORD Number(6) New_emailVARCHAR2( -) regdate Datesql>

Oracle Database Build table, delete fields, add fields, modify fields, modify fields ...