ORACLE学习之scott用户的表结构sql

最新推荐文章于 2025-08-13 17:42:51 发布

转载最新推荐文章于 2025-08-13 17:42:51 发布 · 145 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/wang-s/p/4723945.html

文章标签：

#数据库

本文详细介绍了数据库中表格的设计与创建过程，包括工资等级表、工资表、部门表和雇员表的创建，并阐述了各表格之间的关系及关键字段的说明。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- Create table
create table SALGRADE
(
  GRADE NUMBER,
  LOSAL NUMBER,
  HISAL NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 8
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table SALGRADE
  is '工资等级表：salgrade';
-- Add comments to the columns 
comment on column SALGRADE.GRADE
  is '工资的等级';
comment on column SALGRADE.LOSAL
  is '此等级的最低工资';
comment on column SALGRADE.HISAL
  is '此等级的最高工资';

-- Create table
create table BONUS
(
  ENAME VARCHAR2(10),
  JOB   VARCHAR2(9),
  SAL   NUMBER,
  COMM  NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the table 
comment on table BONUS
  is '工资表：bonus';
-- Add comments to the columns 
comment on column BONUS.ENAME
  is '雇员姓名';
comment on column BONUS.JOB
  is '雇员职位';
comment on column BONUS.SAL
  is '雇员的工资';
comment on column BONUS.COMM
  is '雇员的奖金';

-- Create table
create table DEPT
(
  DEPTNO NUMBER(2) not null,
  DNAME  VARCHAR2(14),
  LOC    VARCHAR2(13)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 8
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table DEPT
  is '部门表：dept';
-- Add comments to the columns 
comment on column DEPT.DEPTNO
  is '表示部门编号，由两位数字所组成';
comment on column DEPT.DNAME
  is '部门名称，最多由14个字符所组成';
comment on column DEPT.LOC
  is '部门所在的位置';
-- Create/Recreate primary, unique and foreign key constraints 
alter table DEPT
  add constraint PK_DEPT primary key (DEPTNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 8
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table EMP
  is ' 雇员表：emp';
-- Add comments to the columns 
comment on column EMP.EMPNO
  is '雇员的编号，由四位数字所组成';
comment on column EMP.ENAME
  is '雇员的姓名，由10位字符所组成';
comment on column EMP.JOB
  is '雇员的职位';
comment on column EMP.MGR
  is '雇员对应的领导编号，领导也是雇员';
comment on column EMP.HIREDATE
  is '雇员的雇佣日期';
comment on column EMP.SAL
  is '基本工资，其中有两位小数，五倍整数，一共是七位';
comment on column EMP.COMM
  is '奖金，佣金';
comment on column EMP.DEPTNO
  is '雇员所在的部门编号';
-- Create/Recreate primary, unique and foreign key constraints 
alter table EMP
  add constraint PK_EMP primary key (EMPNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table EMP
  add constraint FK_DEPTNO foreign key (DEPTNO)
  references DEPT (DEPTNO);

select * from bonus t;--工资表
select * from dept t;--部门表
select * from emp t;--雇员表
select * from salgrade t;--工资等级表

转载于:https://www.cnblogs.com/wang-s/p/4723945.html