Chapter 10 创建表

Chapter 10 创建表

create table product
(
 p_code Number(6),
 p_name Varchar(30),
 p_desc Varchar(100),
 p_price Number(5,2)
);

SQL> desc product;
Name    Type          Nullable Default Comments
------- ------------- -------- ------- --------
P_CODE  NUMBER(6)     Y                         
P_NAME  VARCHAR2(30)  Y                         
P_DESC  VARCHAR2(100) Y                         
P_PRICE NUMBER(5,2)   Y                         

用户名.表名

Schema(模式)学术气味更浓

create table supplier(
       s_code number(6),
       sname varchar2(25),
       contact varchar2(15),
       phone varchar2(15),
       fax varchar2(15)
);


create table ord(
       ordno number(8),
       p_code number(6),
       s_code number(6),
       ordate date,
       unit number(6),
       price number(8,2)
);


select * from cat;


create table worker
as
select e.empno, e.ename name, e.job, e.sal + nvl(e.comm,0) income
from emp e
where e.job not in ('MANAGER','PRESIDENT');

select * from worker;

alter table worker add (hiredate date);

alter table worker modify (hiredate default sysdate);

insert into worker (empno,name,job,income,hiredate) values (9000,'MARY','CLERK',1000,DEFAULT);

alter table worker drop column hiredate;

alter table worker set unused (income);

alter table worker drop unused columns;

rename worker to staff;

comment on table staff is 'aaa';

select comments
from user_tab_comments
where table_name = 'STAFF';

alter user scott account unlock;

comment on column staff.job
is 'jobjob';

select comments
from user_col_comments
where table_name = 'STAFF'
and column_name = 'JOB';


create table staff1
as
select *
from staff;


select * from staff1;

truncate table staff1;

select * from staff1;

SQL> desc staff1;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)    Y                         
NAME     VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
INCOME   NUMBER       Y                         
HIREDATE DATE         Y    

drop table staff1;

SQL> desc staff1;
Object staff1 does not exist.