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;

--授權(quán)
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;

--專業(yè)代碼，
create table speciality(
specialityid number primary key,  --專業(yè)ID
specialityName varchar2(64) --專業(yè)名稱
);
create sequence speciality_seq;

--班級(jí)代碼，
create table classs(
classid number primary key,   --班級(jí)ID
classsName varchar2(64)   --班級(jí)名稱
);
create sequence classs_seq;  --創(chuàng)建序列

--課程代碼
create table course(
courseid number primary key,  --課程ID
courseName varchar2(64)   --課程名稱
);
create sequence course_seq;  --創(chuàng)建序列

--學(xué)生表
create table student(
id number primary key,   --學(xué)生ID
stuname varchar2(64),   --學(xué)生姓名
pwd varchar2(64),    --密碼 
academyid number,    --院系ID
specialityid number,  --專業(yè)ID
classid number        --班級(jí)ID
);
create sequence student_seq;  --創(chuàng)建序列

--成線表
create table achievement(
id number primary key,
studentid number,    --學(xué)生ID
courseid number,     --課程ID
results number,     --員工ID
userid number       --員工ID
);
create sequence achievement_seq;  --創(chuàng)建序列

--教師任課表
create table task(
id number primary key,   --教師ID
courseid number,         --課程ID
userid number,           --員工ID
classsid number          --班級(jí)ID
);
create sequence task_seq;  --創(chuàng)建序列

--員工表(教師表）
create table userinfo(
userid number primary key,    --員工ID
username varchar2(64),        --員工名稱
userpwd varchar2(64),         --員工密碼
rid number               -- 角色I(xiàn)D
);
create sequence userinfo_seq;  --創(chuàng)建序列

--權(quán)限表
create table jurisdiction(
jid number primary key,   --權(quán)限ID
jname varchar2(100),      --權(quán)限名稱
jremark varchar2(200)     --成績(jī)
);
create sequence jurisdictioin_seq;  --創(chuàng)建序列
 
--角色表
create table role(
rid number primary key,     --角色I(xiàn)D
rolename varchar2(200),     --角色名稱
jid varchar2(500),          --權(quán)限ID
jremark varchar2(220)       --成績(jī)
);
create sequence role_seq;  --創(chuàng)建序列

--約束
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);
 

------------------------------------------------------------------------------
--測(cè)試

 
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,'學(xué)生新增','student.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'學(xué)生讀取','student.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'學(xué)生修改','student.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'學(xué)生刪除','student.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'成績(jī)新增','achievement.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'成績(jī)讀取','achievement.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'成績(jī)修改','achievement.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'成績(jī)刪除','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,'班級(jí)新增','classs.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'班級(jí)讀取','classs.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'班級(jí)修改','classs.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'班級(jí)刪除','classs.do_delete');

insert into jurisdiction values(jurisdictioin_seq.nextval,'專業(yè)新增','speciality.do_create');
insert into jurisdiction values(jurisdictioin_seq.nextval,'專業(yè)讀取','speciality.do_read');
insert into jurisdiction values(jurisdictioin_seq.nextval,'專業(yè)修改','speciality.do_update');
insert into jurisdiction values(jurisdictioin_seq.nextval,'專業(yè)刪除','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;