Oracle SQL BASICS (1)-create and modify tables, oraclesql

Oracle SQL BASICS (1)-create and modify tables, oraclesql

1. Table creation statement

Create table fdh_client_info (

Id varchar2 (50) primary key,

Name varchar2 (30) not null,

Sex varchar (1 ),

Age number (3 ),

Address varchar2 (300)

);

2. Modify the table name

Method 1:

Alter table fdh_client_info rename to fdh_client;

Method 2: (only tables under the current schema can be modified)

RENAME fdh_client TO fdh_client_info;

Method 3:

Create table fdh_client_new AS select * from fdh_client_info;

Drop table fdh_client_info;

3. Modify columns

① Add a column

Alter table fdh_client ADD (city varchar2 (100), is_vip varchar (1) default 'n'); -- added two columns

② Delete a column

Alter table fdh_client DROP (city); -- it is required to delete only one column of parentheses;

③ Modify the column name

Alter table fdh_client rename column city TO province;

④ Modify the column type and length

Alter table fdh_client MODIFY (province varchar (80) default 'unknow' not null );

Note: a. You can modify attributes such as the type, field length, and default value constraints when modifying a column;

B. If the modified column contains the not null constraint, the corresponding fields of all records in the table cannot be blank;

C. You can delete a column and add a new column to modify the column.

4. delete a table

Drop table fdh_client_new;

Drop table cp_customer cascade constraint; -- cascade TABLE deletion (delete the TABLE associated with the foreign key to the current TABLE at the same time)

5. Add comments

Comment on table fdh_client IS 'customer test table ';

Comment on column fdh_client.province IS 'province ';