oracle表新增列自增,ORACLE表建立自增列

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;