详解Oracle数据库中自带的所有表结构(sql代码)
2023-06-26 13:33:16
来源/作者: 互联网 / 王振洲
各位用户为了找寻关于详解Oracle数据库中自带的所有表结构(sql代码)的资料费劲了很多周折。这里教程网为您整理了关于详解Oracle数据库中自带的所有表结构(sql代码)的相关资料,仅供查阅,以下为您介绍关于详解Oracle数据库中自带的所有表结构(sql代码)的详细内容
一、tb_emp(员工表)
1、建表
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19create
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
'员工表'
;
2、导入数据
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7369,
'史密斯'
,
'店员'
,7902,
timestamp
'1980-12-17 00:00:00.000000'
,800,
null
,20);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7499,
'艾伦'
,
'售货员'
,7698,
timestamp
'1981-02-20 00:00:00.000000'
,1600,300,30);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7521,
'沃德'
,
'售货员'
,7698,
timestamp
'1981-02-22 00:00:00.000000'
,1250,500,30);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7566,
'琼斯'
,
'经理'
,7839,
timestamp
'1981-04-02 00:00:00.000000'
,2975,
null
,20);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7654,
'马丁'
,
'售货员'
,7698,
timestamp
'1981-09-28 00:00:00.000000'
,1250,1400,30);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7698,
'布莱克'
,
'经理'
,7839,
timestamp
'1981-05-01 00:00:00.000000'
,2850,
null
,30);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7782,
'克拉克'
,
'经理'
,7839,
timestamp
'1981-06-09 00:00:00.000000'
,2450,
null
,10);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7788,
'斯科特'
,
'分析师'
,7566,
timestamp
'1987-04-19 00:00:00.000000'
,3000,
null
,20);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7839,
'国王'
,
'总统'
,
null
,
timestamp
'1981-11-17 00:00:00.000000'
,5000,
null
,10);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7844,
'特纳'
,
'售货员'
,7698,
timestamp
'1981-09-08 00:00:00.000000'
,1500,0,30);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7876,
'亚当斯'
,
'店员'
,7788,
timestamp
'1987-05-23 00:00:00.000000'
,1100,
null
,20);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7900,
'詹姆斯'
,
'店员'
,7698,
timestamp
'1981-12-03 00:00:00.000000'
,950,
null
,30);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7902,
'福特'
,
'分析师'
,7566,
timestamp
'1981-12-03 00:00:00.000000'
,3000,
null
,20);
insert
into
"tb_emp"
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(7934,
'米勒'
,
'店员'
,7782,
timestamp
'1982-01-23 00:00:00.000000'
,1300,
null
,10);
3、查表
? 1select
*
from
"test"
.
"tb_emp"
二、tb_dept(部门表)
1、建表
? 1 2 3 4 5 6 7 8 9create
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
'部门表'
;
2、导入数据
? 1 2 3 4insert
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,
'运营'
,
'波士顿'
);
3、查表
? 1select
*
from
"test"
.
"tb_dept"
;
三、tb_bonus(奖金表)
1、建表
? 1 2 3 4 5 6 7 8 9 10 11create
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
'奖金表'
;
2、导入数据
空
3、查表
四、tb_salgrade(工资等级表)
1、建表
? 1 2 3 4 5 6 7 8 9create
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
'工资等级表'
;
2、导入数据
? 1 2 3 4 5insert
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);
3、查表
? 1select
*
from
test.tb_salgrade;
五、tb_users(用户表)
1、建表
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14create
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
'用户表'
;
2、导入数据
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14insert
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'
);
3、查表
? 1select
*
from
"test"
.
"tb_users"
;
六、tb_saldetail(工资详细表)
1、建表
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17create
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
'工资详细表'
;
2、导入数据
? 1 2 3 4 5 6 7 8 9 10 11 12insert
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);
3、查表
到此这篇关于oracle数据库中自带的所有表结构的文章就介绍到这了,更多相关oracle数据库所有表结构内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
原文链接:https://blog.csdn.net/qq_35161159/article/details/121282719