NECTOSS项目的SQL语句

select * from account ;
-- drop table parenttjj cascade constraints purge ;
drop table account cascade constraints purge ;
drop sequence s_account_id ;
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss' ;
create table account(
id number(9) constraint account_id_pk primary key not null ,-- 账务账号ID
recommender_id number(9) constraint account_r_id_fk references account(id),-- 推荐人ID
constraint account_r_id_ck check(recommender_id <> id),
login_name varchar2(30) constraint account_login_name_uk unique ,-- 登录系统的用户名
login_passwd varchar2(8) not null ,-- 登录口令
status char(1) not null constraint account_status_ck check(status in ('0','1','2')),-- 0：开通；1：暂停；2：删除 
create_date date default sysdate,-- 创建日期
pause_date date default null ,-- 暂停日期
close_date date default null,-- 删除日期
real_name varchar2(20) not null ,-- 客户姓名
idcard_no char(18) not null constraint account_idcard_no_uk unique,-- 身份证号码
birthdate date default null ,-- 出生日期
gender char(1) not null constraint account_gender_ck check(gender in ('0','1')) ,-- 性别 0:男 1:女
occupation varchar2(50) default null , -- 职业
telephone varchar2(15) not null ,-- 联系电话（座机或手机）
email varchar2(50) default null,-- 电子邮箱
mailaddress varchar2(120) default null ,-- 邮箱地址
zipcode char(6) default null,-- 邮编
qq varchar2(15) default null , -- QQ
last_login_time date default null,-- 最后一次登录时间
last_login_ip varchar2(15) default null -- 最后一次登录IP地址
) ;
create sequence s_account_id
start with 1001
increment by 1 ;
insert into account values(
s_account_id.nextval,null,'tjj1302','123456','0',sysdate,null,null,'tangjianjun','420115199001154714'
,'1990-01-15','0',null,'15102763027',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'lc1302','123456','0',sysdate,null,null,'liancong','420115198901154714'
,'1989-05-20','0',null,'18271914072',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'ylm1302','123456','0',sysdate,null,null,'yaoleiming','420115198901254714'
,'1988-04-11','0',null,'15102763526',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'lyc1302','123456','0',sysdate,null,null,'liyinchuan','420115198905154714'
,'1962-03-19','0',null,'15629149126',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'taiji001','256528','1','2008-03-15',null,null,'zhangsanfeng','410381194302256528'
,'1943-02-25','0',null,'13669351234',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'sxl1302','123456','0',sysdate,null,null,'suxiaolin','420105198901154714'
,'1960-03-29','0',null,'15623601238',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,1001,'cr1302','123456','0',sysdate,null,null,'chenrong','420115199211304714'
,'1999-04-19','0',null,'15871455288',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,1002,'zj1302','123456','0',sysdate,null,null,'zhangjun','420115199002154714'
,'2011-04-10','0',null,'15102365254',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,1002,'wj1302','123456','0',sysdate,null,null,'wangjun','420115199001254714'
,'2010-03-15','0',null,'15262541254',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'xl18z60','190613','1','2009-01-10',null,null,'guojing','330682196903190613'
,'1969-03-19','0',null,'13338924567',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,1010,'dgbf70','270429','1','2009-03-01',null,null,'huangrong','330902197108270429'
,'1971-08-27','0',null,'13637811357',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'tjj1302_3','123456','0',sysdate,null,null,'tangjianjun_3','420115199001154717'
,'2012-05-14','0',null,'15102763030',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'tjj1302_4','123456','0',sysdate,null,null,'tangjianjun_4','420115199001154718'
,'2013-01-01','0',null,'15102763031',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'tjj1302_5','123456','0',sysdate,null,null,'tangjianjun_5','420115199001154719'
,'2001-05-18','0',null,'15102763032',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,1005,'mjjzh64','041115','1',sysdate,null,null,'zhangwuji','610121198906041115'
,'1989-06-04','0',null,'13572952468',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'tjj1302_6','123456','0',sysdate,null,null,'tangjianjun_6','420115199001154729'
,'1899-03-18','0',null,'15102765002',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'tjj1302_7','123456','0','2010-03-12',null,null,'tangjianjun_7','420115199001054719'
,'1907-05-10','0',null,'15102763122',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,1011,'jmdxj00','010322','1','2011-01-01',null,null,'guofurong','350581200201010322'
,'1820-01-01','0',null,'18617832562',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,1011,'ljxj90','310346','1','2012-02-01',null,null,'luwushuang','320211199307310346'
,'1993-07-31','0',null,'13186454984',null,null,null,null,null,null) ;

insert into account values(
s_account_id.nextval,null,'kxhxd20','012115','1','2012-02-20',null,null,'weixiaobao','321022200010012115'
,'2000-10-01','0',null,'13953410078',null,null,null,null,null,null) ;

-- alter table account modify mailaddress varchar2(120) ;
-- select * from (select rownum rn ,id,recommender_id,login_name,login_passwd ,status ,create_date
--    , pause_date,close_date, real_name, idcard_no,birthdate , gender ,occupation,telephone
--    , email,mailaddress ,zipcode ,qq,last_login_time,last_login_ip from account where rownum <= 5) where rn >= 1

-- update account set recommender_id = 1002,login_name='tjj1302',login_passwd='123456' ,status='1' ,create_date='2013-06-06 17:09:17'
--    , pause_date = null,close_date=null, real_name='tang', idcard_no='420115199001154714',birthdate ='1990-01-15' , gender='0' ,occupation =null ,telephone='15102763027'
--    , email=null,mailaddress=null ,zipcode=null ,qq='941677893',last_login_time=null,last_login_ip=null where id = 1001 ;

--  select * from account order by id;
commit ;

-- update account set status = '1' where id = 1002 ;
-- update account set occupation = '1' ;
-- update account set mailaddress = '北京市海淀区北三环中路甲18号中鼎大厦' ;

--  -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- 管理员表
drop table admin_info cascade constraints purge ;
drop sequence s_admininfo_id ;
create table ADMIN_INFO
(
  id         NUMBER(11) not null,
  admincode VARCHAR2(30) not null,
  password   VARCHAR2(30) not null,
  name       VARCHAR2(30) not null,
  telephone  VARCHAR2(30),
  email      VARCHAR2(50),
  enrolldate DATE not null
);
alter table ADMIN_INFO
  add constraint ADMIN_INFO_ID_PK primary key (ID);
alter table admin_info add constraint admin_info_admincode_uk unique (admincode);
  create sequence s_admininfo_id start with 1 increment by 1 ;
  alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss' ;
insert into admin_info values(s_admininfo_id.nextval,'admin','admin','admin','13572451258','[e-mail]',sysdate);
insert into admin_info values(s_admininfo_id.nextval,'root','root','root','510251625','[e-mail]',sysdate);
insert into admin_info values(s_admininfo_id.nextval,'tarena','tarena','tarena','15125025124','[e-mail]',sysdate);
insert into ADMIN_INFO (id, admincode, password, name, telephone, email, enrolldate) values (s_admininfo_id.nextval, 'tcr110', '111111', '管理员', '13111111111', '[e-mail]', to_date('13-05-2013 08:20:20', 'dd-mm-yyyy hh24:mi:ss'));
select * from admin_info ;
commit;
-- 查询刚插入的数据的自增长值
-- select s_admininfo_id.currval from dual
select * from role ;
select * from admin_role ;
-- 分页查询管理员
-- select distinct id,admincode,password,name,telephone,email,enrolldate from 
-- (select rownum rn,id,admincode,password,a.name,telephone,email,enrolldate 
-- from admin_info a join (select * from admin_role 
-- where role_id = 1 or 1=1) b on a.id = b.admin_id join (select * from role 
-- where name='角色管理')c on b.role_id = c.id where rownum <= 5) where rn >=1;
-- 获取数据条数,用于获取分页的总页数
-- select count(distinct c.id) from admin_info c join (select * from (select * from admin_role where role_id = 1) a join (select * from role where name like '%角色%') b on a.role_id = b.id ) d on c.id = d.admin_id;

-- 管理员角色表
drop table admin_role cascade constraints purge ;
create table ADMIN_ROLE
(
  admin_id NUMBER not null constraint admin_role_role_fk references admin_info(id) on delete cascade,
  role_id  NUMBER not null constraint admin_role_admin_fk references role(id)
);
alter table ADMIN_ROLE add constraint AR_PK primary key (ADMIN_ID, ROLE_ID);
-- 创建级联更新触发器
-- Create Or Replace Trigger g_Cardapply_Tr
--  After Update Of g_State On g_Cardapply
--  For Each Row
-- Begin
--  Update g_Cardapplydetail a
--     Set a.g_State = :New.g_State
--   Where a.g_State = :Old.g_State;
-- End;

-- insert into ADMIN_ROLE (admin_id, role_id)values (1, 1);
insert into admin_role values(1,1);
insert into admin_role values(1,2);
insert into admin_role values(1,3);
insert into admin_role values(2,1);
insert into admin_role values(3,1);
select * from admin_role ;

-- 权限表 -- role_info
drop table role cascade constraints purge ;
create table ROLE
(
  id   NUMBER not null, -- 主键,角色id
  name VARCHAR2(20) -- 角色名称
);
alter table ROLE add constraint ROLE_PK primary key (ID);
  select * from role ;
  update role set name = '角色管理' where id = 1 ;
  insert into role values (0,'全部');
  insert into role values (1,'角色管理');
  insert into role values (2,'管理员管理');
  insert into role values (3,'资费管理');
  insert into role values (4,'账务账号');
  insert into role values (5,'业务账号');
  insert into role values (6,'账单管理');
  insert into role values (7,'报表');
 -- insert into ROLE (id, name) values (1, '系统管理员');
-- 角色权限表
select * from role_privilege ;
drop table role_privilege purge;
create table ROLE_PRIVILEGE
(
  role_id      NUMBER not null, -- 角色id
  privilege_id NUMBER not null    -- 权限id,对应xml中的id
);
alter table ROLE_PRIVILEGE add constraint RP_PK primary key (ROLE_ID, PRIVILEGE_ID);
insert into ROLE_PRIVILEGE (role_id, privilege_id) values (1, 1);
insert into ROLE_PRIVILEGE (role_id, privilege_id) values (1, 2);
insert into ROLE_PRIVILEGE (role_id, privilege_id) values (1, 3);
insert into ROLE_PRIVILEGE (role_id, privilege_id) values (1, 4);
insert into ROLE_PRIVILEGE (role_id, privilege_id) values (1, 5);
insert into ROLE_PRIVILEGE (role_id, privilege_id) values (1, 6);
insert into ROLE_PRIVILEGE (role_id, privilege_id) values (1, 7);
commit;
select * from admin_role ;

select * from admin_role ;

select * from role;

select * from role_privilege ;

select * from role a join role_privilege b on a.id = b.role_id ;


select * from (select rownum rn,a.id,admincode,password,a.name,telephone,email,enrolldate from admin_info a join (select * from admin_role ) b on a.id = b.admin_id join (select * from role )c on b.role_id = c.id where rownum <= 5 and b.role_id = 1) where rn >=1 ;

select * from (select rownum rn,a.id,admincode,password,a.name,telephone,email,enrolldate from admin_info a join (select * from admin_role ) b on a.id = b.admin_id join (select * from role )c on b.role_id = c.id where rownum <= 5 and name like '%1%') where rn >=1

select * from admin_info ;