DDL Statements in Oracle

DDL Statements in Oracle

sqlDLLOracle SQL StatementsSELECT Data retrievalINSERT / UPDATE / DELETE / MERGE  Data manipulation language(DML)CREATE / ALTER / DROP / RENAME / TRUNCATE  Data definition language(DDL)COMMIT / ROLLBACK / SAVEPOINT  Transaction controlGRANT / REVOKE  Data control language(DCL)

 

I. table 1. Create a table • Create a statement to create a reference

create table t_1(     id int primary key,     name varchar(20) ); 

 

• Use other tables to create references

Create table t_2 as select * from dept; ----- copy dept table create table t_2 as select * from dept where 1 = 2; ----- create a table with the same table structure as dept

 

2. delete • drop table t_1 referenced by drop table (including fields and records); • delete data (including records, without deleting fields) Reference delete from t_1; truncate table t_1; note: delete only deletes the data in the table and does not release the space occupied by the data. truncate not only deletes the data in the table, but also releases the space of the data. rename the reference rename t_1 to t; 2. Field (column) in the table)

1. add: alter table t_1 add score int; 2. delete: alter table t_1 drop (score); 3. change: alter table t_1 modify scale int 4. rename: alter table t_1 rename column scale to grade

 

3. Constraints 1. Add:

alter table t_3 modify loc not null ; alter table t_1 add constraint pk_t primary key(id) ;  alter table t_3 add constraint un_score  unique(score ); 

 

2. Rename:

alter table t rename constraint score_unique to score_uniq 

 

3. Delete:

Alter table table_name drop constraint constraint_name | primary key example 1 alter table t drop constraint score_uniq; example 2 alter table t DROP PRIMARY KEY CASCAED

 

Iv. Summary 1.

rename t_1 to t_2    rename column c1 to c2    rename constraint cn1 to cn2 

 

2. The operations on fields and constraint both start with the alter table name.

1) add field add score int Delete drop (score) Change modify score int varchar (20) 2) constraint not null: add modify score not null Delete modify score null primary key: add constraint pk primary key (score) delete drop constraint primary key cascade unique: add constraint uq unique (score) delete drop constraint uq

 

Supplement: the constraint information can be viewed in the USER_CONSTRAINTS and user_cons_columns tables.