create tablespace studentDB
datafile 'E:\datafiles_1.dbf' size 10m;
create user Huang_Ying_Bo
identified by hyb123456
default tablespace studentDB
grant connect to Huang_Ying_Bo
grant resource to Huang_Ying_Bo
drop table TechEd_T_Students
/*创建学生信息表*/
create table TechEd_T_Students
(
Id integer,
Name varchar2(20),
Adress varchar2(40)
);
alter table TechEd_T_Students
add constraint PK_TechEd_T_Students_Id primary key(Id);
/*创建课程序列Sequence_TechEd_T_Students_Id*/
drop sequence Sequence_TechEd_T_Students_Id;
CREATE SEQUENCE Sequence_TechEd_T_Students_Id;
/*建立触发器*/
drop TRIGGER Trigger_Insert__Students_Inc;
CREATE OR REPLACE TRIGGER Trigger_Insert__Students_Inc
BEFORE INSERT ON TechEd_T_Students
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT Sequence_TechEd_T_Students_Id.NEXTVAL INTO :NEW.Id FROM DUAL;
END;
insert into TechEd_T_Students(Name,Adress) values('黄涛','重庆北碚');
insert into TechEd_T_Students(Name,Adress) values('刘册','重庆沙坪坝');
insert into TechEd_T_Students(Name,Adress) values('邓雨','重庆渝北');
insert into TechEd_T_Students(Name,Adress) values('萧何','河南沛县');
select * from TechEd_T_Students
drop table TechEd_T_Scources
/*创建课程信息表*/
create table TechEd_T_Scources
(
Id integer,
Name varchar2(20)
);
alter table TechEd_T_Scources
add constraint PK_TechEd_T_Scources_Id primary key(Id);
/*创建课程序列Sequence_TechEd_T_Scources_Id*/
drop sequence Sequence_TechEd_T_Scources_Id;
CREATE SEQUENCE Sequence_TechEd_T_Scources_Id;
/*建立触发器*/
drop TRIGGER Trigger_Insert__Scources_Inc;
CREATE OR REPLACE TRIGGER Trigger_Insert__Scources_Inc
BEFORE INSERT ON TechEd_T_Scources
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT Sequence_TechEd_T_Scources_Id.NEXTVAL INTO :NEW.Id FROM DUAL;
END;
insert into TechEd_T_Scources(Name) values('数据结构');
insert into TechEd_T_Scources(Name) values('ORACLE数据库优化');
select * from TechEd_T_Scources
drop table TechEd_T_Scores
/*创建学生分数信息表*/
create table TechEd_T_Scores
(
Id integer,
Student_Id integer,
Scource_Id integer,
Grade integer
);
alter table TechEd_T_Scores
add constraint PK_TechEd_T_Scores primary key(Id);
alter table TechEd_T_Scores
add constraint FK_Scores_Students_Id foreign key(Student_Id) references TechEd_T_Students(Id);
alter table TechEd_T_Scores
add constraint FK_Scores_Scources_Id foreign key(Scource_Id) references TechEd_T_Students(Id);
alter table TechEd_T_Scores
add constraint CK_TechEd_T_Scores_Grade check(Grade between 0 and 100);
/*创建分数序列Sequence_TechEd_T_Scores_Id*/
drop sequence Sequence_TechEd_T_Scores_Id;
CREATE SEQUENCE Sequence_TechEd_T_Scores_Id;
/*建立触发器*/
drop TRIGGER Trigger_Insert_Scores_Inc;
CREATE OR REPLACE TRIGGER Trigger_Insert_Scores_Inc
BEFORE INSERT ON TechEd_T_Scores
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT Sequence_TechEd_T_Scores_Id.NEXTVAL INTO :NEW.Id FROM DUAL;
END;
insert into TechEd_T_Scores(Student_Id,Scource_Id,Grade) values(1,1,88);
insert into TechEd_T_Scores(Student_Id,Scource_Id,Grade) values(1,2,88);
insert into TechEd_T_Scores(Student_Id,Scource_Id,Grade) values(2,1,89);
insert into TechEd_T_Scores(Student_Id,Scource_Id,Grade) values(2,2,89);
insert into TechEd_T_Scores(Student_Id,Scource_Id,Grade) values(3,1,90);
insert into TechEd_T_Scores(Student_Id,Scource_Id,Grade) values(3,2,90);
select * from TechEd_T_Scores;
select * from TechEd_T_Scources;
select * from TechEd_T_Students;
