详解Oracle数据库中自带的所有表结构(sql代码)

create table "test" . "tb_emp" ( 

     "empno" number(4,0) primary key not null ,

     "ename" varchar2(10),

     "job" varchar2(9),

     "mgr" number(4,0),

     "hiredate" date ,

     "sal" number(7,2),

     "comm" number(7,2),

     "deptno" number(2,0)

);

comment on column "test" . "tb_emp" . "empno" is '员工编号' ;

comment on column "test" . "tb_emp" . "ename" is '姓名' ;

comment on column "test" . "tb_emp" . "job" is '职位' ;

comment on column "test" . "tb_emp" . "mgr" is '领导编号' ;

comment on column "test" . "tb_emp" . "hiredate" is '入职时间' ;

comment on column "test" . "tb_emp" . "sal" is '基本工资' ;

comment on column "test" . "tb_emp" . "comm" is '奖金' ;

comment on column "test" . "tb_emp" . "deptno" is '部门编号' ;

comment on table "test" . "tb_emp"   is '员工表' ;

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369, '史密斯' , '店员' ,7902, timestamp '1980-12-17 0.000000' ,800, null ,20);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499, '艾伦' , '售货员' ,7698, timestamp '1981-02-20 0.000000' ,1600,300,30);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7521, '沃德' , '售货员' ,7698, timestamp '1981-02-22 0.000000' ,1250,500,30);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566, '琼斯' , '经理' ,7839, timestamp '1981-04-02 0.000000' ,2975, null ,20);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7654, '马丁' , '售货员' ,7698, timestamp '1981-09-28 0.000000' ,1250,1400,30);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698, '布莱克' , '经理' ,7839, timestamp '1981-05-01 0.000000' ,2850, null ,30);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782, '克拉克' , '经理' ,7839, timestamp '1981-06-09 0.000000' ,2450, null ,10);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7788, '斯科特' , '分析师' ,7566, timestamp '1987-04-19 0.000000' ,3000, null ,20);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7839, '国王' , '总统' , null , timestamp '1981-11-17 0.000000' ,5000, null ,10);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7844, '特纳' , '售货员' ,7698, timestamp '1981-09-08 0.000000' ,1500,0,30);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, '亚当斯' , '店员' ,7788, timestamp '1987-05-23 0.000000' ,1100, null ,20);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, '詹姆斯' , '店员' ,7698, timestamp '1981-12-03 0.000000' ,950, null ,30);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7902, '福特' , '分析师' ,7566, timestamp '1981-12-03 0.000000' ,3000, null ,20);

insert into "tb_emp" (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, '米勒' , '店员' ,7782, timestamp '1982-01-23 0.000000' ,1300, null ,10);

select * from "test" . "tb_emp"

create table "test" . "tb_dept" (

     "deptno" number(2,0) primary key not null , 

     "dname" varchar2(14),

     "loc" varchar2(13)

  );

comment on column "test" . "tb_dept" . "deptno" is '部门编号' ;

comment on column "test" . "tb_dept" . "dname" is '部门名称' ;

comment on column "test" . "tb_dept" . "loc" is '部门所在位置' ;

comment on table "test" . "tb_dept"   is '部门表' ;

insert into "tb_dept" (deptno,dname,loc) values (10, '会计' , '纽约' );

insert into "tb_dept" (deptno,dname,loc) values (20, '研究' , '达拉斯' );

insert into "tb_dept" (deptno,dname,loc) values (30, '销售' , '芝加哥' );

insert into "tb_dept" (deptno,dname,loc) values (40, '运营' , '波士顿' );

select * from   "test" . "tb_dept" ;

create table test. "tb_bonus" ( 

"ename" varchar2(10),

"job" varchar2(9),

"sal" number,

"comm" number

);

comment on column "test" . "tb_bonus" . "ename" is '姓名' ;

comment on column "test" . "tb_bonus" . "job" is '职位' ;

comment on column "test" . "tb_bonus" . "sal" is '基本工资' ;

comment on column "test" . "tb_bonus" . "comm" is '奖金' ;

comment on table "test" . "tb_bonus"   is '奖金表' ;

create table "test" . "tb_salgrade" (

     "grade" number,

     "losal" number,

     "hisal" number

);

comment on column "test" . "tb_salgrade" . "grade" is '工资等级' ;

comment on column "test" . "tb_salgrade" . "losal" is '最低工资' ;

comment on column "test" . "tb_salgrade" . "hisal" is '最高工资' ;

comment on table "test" . "tb_salgrade"   is '工资等级表' ;

insert into test.tb_salgrade (grade,losal,hisal) values (1,700,1200);

insert into test.tb_salgrade (grade,losal,hisal) values (2,1201,1400);

insert into test.tb_salgrade (grade,losal,hisal) values (3,1401,2000);

insert into test.tb_salgrade (grade,losal,hisal) values (4,2001,3000);

insert into test.tb_salgrade (grade,losal,hisal) values (5,3001,9999);

select * from test.tb_salgrade;

create table "test" . "tb_users" (   

     "id" varchar2(10) primary key not null , 

     "username" varchar2(64),

     "password" varchar2(64),

     "age" number(3,0),

     "sex" varchar2(1)

);

comment on column "test" . "tb_users" . "id" is '用户唯一id' ;

comment on column "test" . "tb_users" . "username" is '用户名' ;

comment on column "test" . "tb_users" . "password" is '密码' ;

comment on column "test" . "tb_users" . "age"   is '年龄' ;

comment on column "test" . "tb_users" . "sex"   is '性别' ;

comment on table "test" . "tb_users"   is '用户表' ;

insert into "tb_users" (id,username, password ,age,sex) values ( '1' , '史密斯' , '123456' ,23, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '2' , '艾伦' , '123456' ,18, '0' );

insert into "tb_users" (id,username, password ,age,sex) values ( '3' , '沃德' , '123456' ,28, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '4' , '琼斯' , '123456' ,19, '0' );

insert into "tb_users" (id,username, password ,age,sex) values ( '5' , '马丁' , '123456' ,25, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '6' , '布莱克' , '123456' ,27, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '7' , '克拉克' , '123456' ,29, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '8' , '斯科特' , '123456' ,32, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '9' , '国王' , '123456' ,90, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '10' , '特纳' , '123456' ,52, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '11' , '亚当斯' , '123456' ,46, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '12' , '詹姆斯' , '123456' ,34, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '13' , '福特' , '123456' ,65, '1' );

insert into "tb_users" (id,username, password ,age,sex) values ( '14' , '米勒' , '123456' ,75, '1' );

select * from "test" . "tb_users" ;

create table "uclm" . "tb_saldetail" (

     "salno" number(4,0) primary key not null ,

     "ename" varchar2(10),

     "salyear"   varchar2(10),

     "salmonth" varchar2(4),

     "sal" number(7,2),

     "comm" number(7,2),

     "empno" number(4,0)

);

comment on column "uclm" . "tb_saldetail" . "salno" is '工资编号' ;

comment on column "uclm" . "tb_saldetail" . "ename" is '姓名' ;

comment on column "uclm" . "tb_saldetail" . "salyear" is '发薪年份' ;

comment on column "uclm" . "tb_saldetail" . "salmonth" is '发薪月份' ;

comment on column "uclm" . "tb_saldetail" . "sal" is '基本工资' ;

comment on column "uclm" . "tb_saldetail" . "comm" is '奖金' ;

comment on column "uclm" . "tb_saldetail" . "empno" is '员工编号' ;

comment on table "uclm" . "tb_saldetail"   is '工资详细表' ;

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (1, '史密斯' , '2020' , '01' , 800, 0, 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (2, '史密斯' , '2020' , '02' , 801.14, 300, 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (3, '史密斯' , '2020' , '03' , 804.21, null , 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (4, '史密斯' , '2020' , '04' , 806.41, null , 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (5, '史密斯' , '2020' , '05' , 800.55, 100, 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (6, '史密斯' , '2020' , '06' , 806.14, 200, 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (7, '史密斯' , '2020' , '07' , 800.55, null , 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (8, '史密斯' , '2020' , '08' , 806.84, null , 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (9, '史密斯' , '2020' , '09' , 800.77, null , 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (10, '史密斯' , '2020' , '10' , 806.85, null , 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (11, '史密斯' , '2020' , '11' , 800.83, 0, 7369);

insert into test.tb_saldetail (salno, ename, salyear, salmonth, sal, comm, empno) values (12, '史密斯' , '2020' , '12' , 806.14, 100, 7369);