Basic operation statements for Oracle tables

The content of this article includes: deleting and modifying tables, deleting and modifying fields, deleting and modifying primary keys, foreign keys, unique, non-empty, and default constraints.

View All Your tables: Select * From user_tab_comments; www.2cto.com: Create Table cqytest (ID number (1), username varchar2 (11), password varchar2 (11) -- The last field must be followed by no comma.) tablespace cqyspace; copy table: Create Table test1 as select * From cqy. cqytest; -- Copy cqy user's cqytest table to delete the table: Drop table test1 cascade constraints; Add comment to the table: Comment on table cqytest is 'my test table'; Add comment to the field: comment on column cqyt1.id is 'Primary key'; Modify Field name: alter table cqytest rename column ID to userid; add field: alter table cqytest Add email varchar2 (11); Delete field: alter table cqytest drop column email; Modify Field Type: alter table cqytest modify email varchar2 (20); query all constraints: Select constraint_name from user_cons_columns; create Table cqyt1 (ID number (11) not null primary key, -- non-null, primary key, unique (unique ), foreign key ID references cqytable www.2cto.com groupid number (11) Check (groupid> 22 and groupid <33), -- condition constraint username varchar2 (20), password varchar2 (20) tablespace cqyspace; create Table cqyt2 (ID number (11), foreign key (ID) References cqyt1 (ID), -- add the foreign key username varchar2 (20), password varchar2 (20) when creating the table )) tablespace cqyspace; after the table is created, add the primary key alter table cqyt2 modify (id Primary Key); -- without the constraint name alter table cqyt add constraint cqyt_pk_id primary key (ID ); -- cqyt_pk: add the foreign key alter table cqyt4 add constraint cqyt4_fk_cqyt1_id foreign key (ID) References cqyt1 (ID) after creating the table for the constraint name ); after creating a table at www.2cto.com, add the non-null constraint alter table cqyt2 modify (username not null). After the table is created, add the unique constraint alter table cqyt2 modify (username unique ); add the default value alter table cqyt2 modify username default 'uu 'after the table is created; Add the conditional constraint alter table cqyt2 add constraints cqyt2_ck_id check (ID> 11 and ID <20) after the table is created ); delete the constraint alter table cqyt4 drop constraint cqyt4_pk; 2. change column attributes

For example, in table 2, the column ID is int type and is changed to char type.
Alter table Table2 alter column ID char

3. Add Columns

A. Example: add the name column as char in table 2.
Alter table Table2 Add name char (8)
B. Example: add the nob column in table 2 as int type, set the auto increment to 1, and leave it empty.
Alter table Table2 add nob int identity (1, 1) not null

4. Delete Columns

For example, delete the ID column in table 2.
Alter table Table2 drop column ID. The preceding content includes "Table partition deletion and modification query" and "field partition deletion and modification query ", the "primary key, foreign key, unique, non-empty, and default constraint" exercise is to install the above content without reading the article and manually typing it out.