DCL Data control language

Statement of authorization and right to receive as mentioned before
grant, revoke

Data definition language

create alter,drop statement, create table, modify table, delete

Create table

create table $tablename$(
    id int,
    name,varchar(5) --Maximum length required to declare
    money,number(6,2) --9999.00
)

-- Copy the contents and structure of other tables to generate a new table without constraints
-- Copy data 2 of other tables to the new table (the new table does not exist in advance)
-- The new table has the same structure as the query table,But there will be no constraints(Primary key, foreign key, default constraint..). 
create table $tablename$ as $othertablename$

-- Generate a new table from the content and structure of the query results
create table $tablename$ as select * from student 

View table structure

desc $tablename$

Modify table

Add constraint

Constraints are used to restrict the validity of data added to a table:

• 1) NOT NULL: non NULL constraint
  
• 2) UNIQUE: unique constraint UN table name column name specification format
• 3) PRIMARY KEY: primary key constraint PK table name column name
• 4) FOREIGN KEY: foreign key constraint FK table name column name
• 5) CHECK: check constraint CHK table name column name
• 6) DEFAULT: default constraint DF table name column name

1. Default constraint

alter table $tablename$ modify (Column names default Default value);
--example
alter table student modify (age default 18);

1. Unique constraint

alter table $tablename$ add constraint UN_$tablename_$columnname$ unique(Column names)
--example
alter table student add constarint UN_student_num unique(num);

1. Primary key constraint

alter table $tablenamae$ add constraint PK_$tablename_$columnname$ primary key(Column names)；
--example
alter table student add constraint PK_student_num primary key(num);
--Compound primary key (Union primary key)
alter table student add constraint PK_student_numandage primary key(num,age);

1. Check constraint

alter table $tablename$ add constraint CHK_$tablename_$columnname$ check(condition);--Conditional and and  or or
--example
alter table student add constraint CHK_student_age check(age>=18 and age<=22);

Delete constraint

alter table $tablename$ drop constraint Constraint name 
--example
alter table student drop CHK_student_age;

Forbidden constraint

alter table $tablename$ disable constraint Constraint name
--example
alter table student disable constraint CHK_student_age;

Modify table name

--Modify table name
alter table $oldtablename$ rename to $newtablename$;

Add column

Alter table $tablename $add $column name $$data type $;

Delete column

alter table $tablename$ drop column $Column names $;

Modify column (column data type or data maximum length)

Alter table $tablename $modify $column name $$data type$

Create tables with constraints

create table $tablename$(
    id int,
    name,varchar(5),
    money,number(6,2),
    constraint pk_CK check... 
)

--This kind of constraint name can't be specified, but is automatically generated by the system. It's troublesome to delete it
create table $tablename(
    id int not null primary key,
    name varchar(5),
)

Delete table

drop table $tablename $-- drop table in Recycle Bin 
Drop table $tablename $purge -- delete table completely
 show recyclebin; -- invisible in PLSQL Developer, need to execute in sqlplus window
 purge recyclebin; -- empty the recycle bin

Recovery table

flashback table $tablename$ to before drop;