📄 oracle.sql
字号:
create table ROLES
(
ID NUMBER primary key,
NAME VARCHAR2(20) not null unique,
PID NUMBER(20) not null,
REMARK VARCHAR2(1000)
);
insert into roles values(1,'user',274877906943,'admin');
comment on column ROLES.NAME is '角色名';
comment on column ROLES.PID is '权限值';
comment on column ROLES.REMARK is '备注';
insert into ROLES values(2,'管理员',274877906943,'mark');
create table ADMINS
(
ID NUMBER primary key,
LOGIN_NAME VARCHAR2(20) not null unique,
REAL_NAME VARCHAR2(20) ,
LOGIN_PASSWORD VARCHAR2(20) not null,
EMAIL VARCHAR2(50) not null unique,
SEX NUMBER(1),
ADDRESS VARCHAR2(200) not null,
REGISTER_DATE DATE,
TELEPHONE VARCHAR2(20),
POSTCODE VARCHAR2(10),
REMARK VARCHAR2(1000),
ROLEID NUMBER(10) references ROLES(ID)
);
comment on column ADMINS.LOGIN_NAME is '登陆名';
comment on column ADMINS.REAL_NAME is '真实姓名';
comment on column ADMINS.LOGIN_PASSWORD is '登陆密码';
comment on column ADMINS.EMAIL is 'email';
comment on column ADMINS.SEX is '性别 0：男 1：女';
comment on column ADMINS.ADDRESS is '地址';
comment on column ADMINS.REGISTER_DATE is '注册日期';
comment on column ADMINS.TELEPHONE is '电话';
comment on column ADMINS.POSTCODE is '邮政编码';
comment on column ADMINS.REMARK is '备注';
comment on column ADMINS.ROLEID is '角色';
insert into ADMINS values(1,'briup','briup','briup','briup@briup.com',0,'上海',sysdate,'2222222','200001','mark',1);
create table USERS
(
ID NUMBER primary key,
LOGIN_NAME VARCHAR2(20) not null unique,
REAL_NAME VARCHAR2(20) ,
LOGIN_PASSWORD VARCHAR2(20) not null,
EMAIL VARCHAR2(50) not null unique,
SEX NUMBER(1),
ADDRESS VARCHAR2(200) not null,
REGISTER_DATE DATE,
TELEPHONE VARCHAR2(20),
POSTCODE VARCHAR2(10),
REMARK VARCHAR2(1000),
ROLEID NUMBER(10) references ROLES(ID)
);
comment on column USERS.LOGIN_NAME is '登陆名';
comment on column USERS.REAL_NAME is '真实姓名';
comment on column USERS.LOGIN_PASSWORD is '登陆密码';
comment on column USERS.EMAIL is 'email';
comment on column USERS.SEX is '性别';
comment on column USERS.ADDRESS is '地址';
comment on column USERS.REGISTER_DATE is '注册日期';
comment on column USERS.TELEPHONE is '电话';
comment on column USERS.POSTCODE is '邮政编码';
comment on column USERS.REMARK is '备注';
comment on column USERS.ROLEID is '角色';
insert into USERS values(1,'briup','briup','briup','briup@briup.com',0,'上海',sysdate,'2222222','200001','mark',1);
create table PRODUCTSTYPE
(
ID NUMBER primary key,
NAME VARCHAR2(32) not null unique,
REMARK VARCHAR2(2000)
);
insert into PRODUCTSTYPE values(1,'按月租收费','按月租收费');
insert into PRODUCTSTYPE values(2,'按小时收费','按小时收费');
insert into PRODUCTSTYPE values(3,'按流量收费','按流量收费');
insert into PRODUCTSTYPE values(4,'按月租和小时收费','按月租和小时收费');
create table PRODUCTS
(
ID NUMBER primary key,
NAME VARCHAR2(32) not null unique,
BASE_FEE NUMBER(8,2) default 0,
RATE_FEE NUMBER(8,2) default 0,
DAILY_LIMIT NUMBER,
MONTH_LIMIT NUMBER,
UP_LIMIT NUMBER,
DOWN_LIMIT NUMBER,
SFEE NUMBER(8,2),
REMARK VARCHAR2(2000),
PRODUCTSTYPEID NUMBER references PRODUCTSTYPE(ID)
);
select * from products;
insert into PRODUCTS values(3,'网通',22,22,22,22,11111,2222222,200001,'mark',1);
comment on column PRODUCTS.NAME is '资费名称';
comment on column PRODUCTS.BASE_FEE is '月租费';
comment on column PRODUCTS.RATE_FEE is '每小时的费率';
comment on column PRODUCTS.DAILY_LIMIT is '日限时间，单位（秒）';
comment on column PRODUCTS.MONTH_LIMIT is '月限时间，单位（秒）';
comment on column PRODUCTS.UP_LIMIT is ' 上行速率（单位：字节）';
comment on column PRODUCTS.DOWN_LIMIT is '下行速率（单位：字节）';
comment on column PRODUCTS.SFEE is '流量费用 （单位：元／M)';
comment on column PRODUCTS.REMARK is '备注';
comment on column PRODUCTS.PRODUCTSTYPEID is '产品类型ID（外键）';
create table NASDAY
(
ID NUMBER primary key,
NASIPADDRESS VARCHAR2(15) not null,
ONDATE DATE,
TIME_DURATION NUMBER not null,
FLUX NUMBER not null,
ONLINE_USERS NUMBER not null
);
insert into NASDAY values(1000,'192.168.0.200',sysdate+1/24,4332.5,53333,100);
insert into NASDAY values(1001,'192.168.0.200',sysdate+2/24,232.5,13333,200);
insert into NASDAY values(1002,'192.168.0.200',sysdate+3/24,1432.5,32233,140);
insert into NASDAY values(1003,'192.168.0.200',sysdate+4/24,3432.5,2833,70);
insert into NASDAY values(1004,'192.168.0.200',sysdate+5/24,9432.5,32333,120);
insert into NASDAY values(1005,'192.168.0.200',sysdate+6/24,9432.5,62333,800);
insert into NASDAY values(1006,'192.168.0.200',sysdate+7/24,3232.5,32333,500);
insert into NASDAY values(1007,'192.168.0.200',sysdate+8/24,9432.5,1333,440);
insert into NASDAY values(1008,'192.168.0.200',sysdate+9/24,8432.5,52333,300);
insert into NASDAY values(1009,'192.168.0.200',sysdate+10/24,9432.5,42333,800);
insert into NASDAY values(1010,'192.168.0.200',sysdate+11/24,5432.5,32333,230);
insert into NASDAY values(1011,'192.168.0.200',sysdate+12/24,7432.5,22333,560);
insert into NASDAY values(1012,'192.168.0.200',sysdate+13/24,9432.5,11113,100);
insert into NASDAY values(1013,'192.168.0.200',sysdate+14/24,9432.5,92333,100);
insert into NASDAY values(1014,'192.168.0.200',sysdate+15/24,9432.5,32333,100);
insert into NASDAY values(1015,'192.168.0.200',sysdate+16/24,9432.5,77773,100);
insert into NASDAY values(1016,'192.168.0.200',sysdate+17/24,9432.5,42333,100);
insert into NASDAY values(1017,'192.168.0.200',sysdate+18/24,9432.5,352333,100);
insert into NASDAY values(1018,'192.168.0.200',sysdate+19/24,9432.5,62333,100);
insert into NASDAY values(1019,'192.168.0.200',sysdate+20/24,9432.5,72333,100);
insert into NASDAY values(1020,'192.168.0.200',sysdate+21/24,9432.5,45333,100);
insert into NASDAY values(1021,'192.168.0.200',sysdate+22/24,9432.5,98333,100);
insert into NASDAY values(1022,'192.168.0.200',sysdate+23/24,9432.5,22333,100);
create table NASMONTH
(
ID NUMBER primary key,
NASIPADDRESS VARCHAR2(15) not null,
ONDATE DATE,
TIME_DURATION NUMBER not null,
FLUX NUMBER not null,
ONLINE_USERS NUMBER not null
);
comment on column NASMONTH.NASIPADDRESS is 'nas ip 地址';
comment on column NASMONTH.TIME_DURATION is '时长总和';
comment on column NASMONTH.FLUX is '流量总和';
comment on column NASMONTH.ONLINE_USERS is '在线用户数量和';
insert into NASMONTH values(1000,'192.168.0.200',sysdate,4332.5,53333,100);
insert into NASMONTH values(1001,'192.168.0.200',sysdate+1,232.5,13333,200);
insert into NASMONTH values(1002,'192.168.0.200',sysdate+2,1432.5,32233,140);
insert into NASMONTH values(1003,'192.168.0.200',sysdate+3,3432.5,2833,70);
insert into NASMONTH values(1004,'192.168.0.200',sysdate+4,9432.5,32333,120);
insert into NASMONTH values(1005,'192.168.0.200',sysdate+5,9432.5,62333,800);
insert into NASMONTH values(1006,'192.168.0.200',sysdate+6,3232.5,32333,500);
insert into NASMONTH values(1007,'192.168.0.200',sysdate+7,9432.5,1333,440);
insert into NASMONTH valdetailues(1008,'192.168.0.200',sysdate+8,8432.5,52333,300);
insert into NASMONTH values(1009,'192.168.0.200',sysdate+9,9432.5,42333,800);
insert into NASMONTH values(1010,'192.168.0.200',sysdate+10,5432.5,32333,230);
insert into NASMONTH values(1011,'192.168.0.200',sysdate+11,7432.5,22333,560);
insert into NASMONTH values(1012,'192.168.0.200',sysdate+12,9432.5,11113,100);
insert into NASMONTH values(1013,'192.168.0.200',sysdate+13,9432.5,92333,100);
insert into NASMONTH values(1014,'192.168.0.200',sysdate+14,9432.5,32333,100);
insert into NASMONTH values(1015,'192.168.0.200',sysdate+15,9432.5,77773,100);
insert into NASMONTH values(1016,'192.168.0.200',sysdate+16,9432.5,42333,100);
insert into NASMONTH values(1017,'192.168.0.200',sysdate+17,9432.5,352333,100);
insert into NASMONTH values(1018,'192.168.0.200',sysdate+18,9432.5,62333,100);
create table NASYEAR
(
ID NUMBER primary key,
NASIPADDRESS VARCHAR2(15) not null,
ONDATE DATE,
TIME_DURATION NUMBER not null,
FLUX NUMBER not null,
ONLINE_USERS NUMBER not null
);
comment on column NASYEAR.NASIPADDRESS is 'nas ip 地址';
comment on column NASYEAR.TIME_DURATION is '总时长';
comment on column NASYEAR.FLUX is '总流量';
comment on column NASYEAR.ONLINE_USERS is '在线用户数量';
insert into NASYEAR values(1000,'192.168.0.200',add_months(sysdate,1),4332.5,53333,100);
insert into NASYEAR values(1001,'192.168.0.200',add_months(sysdate,2),232.5,13333,200);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -