新建一个表

创建表空间

CREATE SMALLFILE TABLESPACE "ZUOYE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZUOYE' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

 

 

 创建用户

-- Create the user

create user ZUOYE

  default tablespace ZUOYE

  identified by ZUOYE;

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges

grant connect to ZUOYE;

grant dba to ZUOYE;

 

 创建表格

-- Create table

create table T_HQ_BJCY

(

  xueh   NUMBER not null,

  xingm  VARCHAR2(10),

  xingb  NUMBER,

  sheng  NUMBER,

  jiatzz VARCHAR2(100)

)

tablespace ZUOYE

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

-- Add comments to the columns

comment on column T_HQ_BJCY.xueh

  is '学号;

comment on column T_HQ_BJCY.xingm

  is '姓名;

comment on column T_HQ_BJCY.xingb

  is '1-男，-女;

comment on column T_HQ_BJCY.sheng

  is '身高;

comment on column T_HQ_BJCY.jiatzz

  is '家庭住址;

-- Create/Recreate primary, unique and foreign key constraints

alter table T_HQ_BJCY

  add constraint PK_T_HQ_XUEH primary key (XUEH)

  using index

  tablespace ZUOYE

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

-- Create/Recreate check constraints

alter table T_HQ_BJCY

  add constraint CHECK_SHENG

  check (sheng > '140' and sheng < '220');

alter table T_HQ_BJCY

  add constraint CHECK_XINGB

  check (xingb = '1' or xingb = '2');