ycsr.sql

一个OA系统

conn system/adminvb as sysdba;
--表空间
create tablespace zhoufan datafile 'c:\ycsr.ora' size 5M autoextend on;
commit;

--用户
CREATE USER zhou IDENTIFIED BY "791218" DEFAULT TABLESPACE zhoufan TEMPORARY TABLESPACE temp;
commit;

--授权
GRANT CONNECT TO ycsr;
grant resource to ycsr;
commit;
------------------------------------------------------------------------------
conn ycsr/ycsr;
--建表
--院系代码，
create table academy(
academyid number primary key,--院系ID
academyName varchar2(64)  -- 院系名称
); 
create sequence academy_seq;

--专业代码，
create table speciality(
specialityid number primary key,  --专业ID
specialityName varchar2(64) --专业名称
);
create sequence speciality_seq;

--班级代码，
create table classs(
classid number primary key,   --班级ID
classsName varchar2(64)   --班级名称
);
create sequence classs_seq;  --创建序列

--课程代码
create table course(
courseid number primary key,  --课程ID
courseName varchar2(64)   --课程名称
);
create sequence course_seq;  --创建序列

--学生表
create table student(
id number primary key,   --学生ID
stuname varchar2(64),   --学生姓名
pwd varchar2(64),    --密码 
academyid number,    --院系ID
specialityid number,  --专业ID
classid number        --班级ID
);
create sequence student_seq;  --创建序列

--成线表
create table achievement(
id number primary key,
studentid number,    --学生ID
courseid number,     --课程ID
results number,     --员工ID
userid number       --员工ID
);
create sequence achievement_seq;  --创建序列

--教师任课表
create table task(
id number primary key,   --教师ID
courseid number,         --课程ID
userid number,           --员工ID
classsid number          --班级ID
);
create sequence task_seq;  --创建序列

--员工表(教师表）
create table userinfo(
userid number primary key,    --员工ID
username varchar2(64),        --员工名称
userpwd varchar2(64),         --员工密码
rid number               -- 角色ID
);
create sequence userinfo_seq;  --创建序列

--权限表
create table jurisdiction(
jid number primary key,   --权限ID
jname varchar2(100),      --权限名称
jremark varchar2(200)     --成绩
);
create sequence jurisdictioin_seq;  --创建序列
 
--角色表
create table role(
rid number primary key,     --角色ID
rolename varchar2(200),     --角色名称
jid varchar2(500),          --权限ID
jremark varchar2(220)       --成绩
);
create sequence role_seq;  --创建序列

--约束
ALTER TABLE  student ADD CONSTRAINT FK_userinfo1 FOREIGN KEY (academyid) REFERENCES academy(academyid);
ALTER TABLE  student ADD CONSTRAINT FK_userinfo2 FOREIGN KEY (specialityid) REFERENCES speciality(specialityid);
ALTER TABLE  student ADD CONSTRAINT FK_userinfo3 FOREIGN KEY (classid) REFERENCES classs(classid);

ALTER TABLE  achievement ADD CONSTRAINT FK_achievement1 FOREIGN KEY (studentid) REFERENCES student(studentid);
ALTER TABLE  achievement ADD CONSTRAINT FK_achievement2 FOREIGN KEY (courseid) REFERENCES course(courseid);
ALTER TABLE  achievement ADD CONSTRAINT FK_achievement3 FOREIGN KEY (userid) REFERENCES userinfo(userid);

ALTER TABLE  task ADD CONSTRAINT FK_task1 FOREIGN KEY (courseid) REFERENCES course(courseid);
ALTER TABLE  task ADD CONSTRAINT FK_task2 FOREIGN KEY (userid) REFERENCES userinfo(userid);
ALTER TABLE  task ADD CONSTRAINT FK_task3 FOREIGN KEY (classsid) REFERENCES classs(classsid);

ALTER TABLE  userinfo ADD CONSTRAINT FK_userinfo_role FOREIGN KEY (rid) REFERENCES role(rid);
 

------------------------------------------------------------------------------
--测试

 
select * From tab;

insert into jurisdiction values(jurisdictioin_seq.nextval,'用户新增','userinfo.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'用户查询','userinfo.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'用户更新','userinfo.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'用户删除','userinfo.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'角色新增','role.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'角色读取','role.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'角色更新','role.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'角色删除','role.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'学生新增','student.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'学生读取','student.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'学生修改','student.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'学生删除','student.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'成绩新增','achievement.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'成绩读取','achievement.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'成绩修改','achievement.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'成绩删除','achievement.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'任课表新增','task.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'任课表读取','task.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'任课表修改','task.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'任课表删除','task.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'课程新增','course.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'课程读取','course.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'课程修改','course.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'课程删除','course.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'班级新增','classs.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'班级读取','classs.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'班级修改','classs.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'班级删除','classs.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'专业新增','speciality.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'专业读取','speciality.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'专业修改','speciality.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'专业删除','speciality.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'院系新增','academy.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'院系读取','academy.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'院系修改','academy.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'院系删除','academy.do_delete');
 




insert into role values(role_seq.nextval,'管理员2','1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36','test');

insert into userinfo values(userinfo_seq.nextval,'admin','admin',1);

select * from ACADEMY;
select * from ACHIEVEMENT;
select * from CLASSS;
select * from COURSE;
select * from JURISDICTION;
select * from ROLE;
select * from SPECIALITY;
select * from STUDENT;
select * from TASK;
select * from USERINFO;
commit;