[*用Oracle来创建学生信息管理系统*]-CSDN博客

本文链接：https://blog.csdn.net/code_star_one/article/details/53167765

背景：学生信息管理系统

功能描述：学生基本信息管理、课程信息管理、班级信息管理、选课信息管理、教师基本信息管理

学生基本信息：学号，姓名，年龄，性别，入学日期，家庭住址
教师基本信息：教工号，姓名，年龄，担当的课程号
课程基本信息：课程号，课程名，课程学分
选课信息：学号，教工号，成绩
班级信息：班级号，系，年级，班级

关系：

一个学生只能属于一个班级，一个班级有多个学生
一个学生可以选多门课程，一门课程可以被多个学生选
一个老师只能教一门课，一门课程可以由多个老师教

创建表

创建班级表class

create table class(
clno varchar2(5) primary key,
dept varchar2(40) not null,
grade varchar2(8) not null,
branch varchar2(20) not null);

创建课程表course

create table course(
cno varchar2(5) primary key,
name varchar2(60) not null,
score number(2) not null);

创建学生表student

create table student(
sno varchar2(8) primary key,
name varchar2(12) not null,
age number(2) not null,
sex char(1) not null,
entrance date,
address varchar2(100),
clno varchar2(5),
constraint fk_clno foreign key(clno)references class(clno));

创建教师表teacher

create table teacher(
tno varchar2(5) primary key,
name varchar2(12) not null,
age number(2) not null,
cno varchar2(5) not null);

创建选课表st

create table st(
sno varchar2(8) not null,
tno varchar2(5) not null,
grade number(2) default 0);

PS:–对school方案中的基本表添加约束
–not null
–primary key
–foreign key
–unique
–check

–1、班级信息表约束

  --check
  alter table class
  add constraint ck_class_grade
  check(grade in('大一','大二','大三','大四'));

  --追加主键约束
  alter table class
  add constraint pk_class_clno primary key(clno);

–追加注释
–表注释、列注释

  comment on table class is '班级信息表';
  comment on column class.clno is '班级编码';
  comment on column class.dept is '系';
  comment on column class.grade is '年级';
  comment on column class.branch is '班';

–查看注释

  select *from user_tab_comments;

2、学生信息表约束

  --check约束
  alter table student
  add constraint ck_student_age 
  check(age>6 and age<41);
  alter table student
  add constraint ck_student_sex
  check (sex in('0','1'));

--外键约束
  alter table student 
  add constraint fk_student_clno foreign key(clno)
  references class(clno);

–学生信息表注释

comment on table student is '学生信息表';
comment on column student.sno is '学号';
comment on column student.name is '学生姓名';
comment on column student.age is '学生年龄';
comment on column student.sex is '性别，1：男，0：女';
comment on column st.grade is '成绩，默认0';

3、课程信息表约束

  alter table course
  add constraint ck_course_score check(score>0 and score<10);

4、教师信息表约束

--check
  alter table teacher 
  add constraint ck_teacher_age check (age>20 and age<66);
  
--外键
  alter table teacher 
  add constraint fk_teacher_cno foreign key(cno)
  references course(cno);

5、选课信息表约束

--主键约束
alter table st
add constraint pk_st primary key(sno,tno);

--check
  alter table st
  add constraint ck_st_grade check(grade>=0 and grade<=9);

--外键约束
  alter table st
  add constraint fk_st_sno foreign key(sno)
  references student(sno);
  alter table st
  add constraint fk_st_tno foreign key(tno)
  references teacher(tno);

–索引

1、学生表索引

--在clno上创建索引
create index idx_student_clno on student(clno);

2、教师表索引

--在cno上创建索引
create index idx_teacher_cno on teacher(cno);

3、选课信息表创建索引

--在sno,tno 创建索引
create index idx_st_sno on st(sno);
create index idx_st_tno on st(tno);

4、学生信息表的性别创建位图索引

create bitmap index idx_student_sex on student(sex);

–查看索引

select table_name,column_name from user_indexes where table_name='student';

–完成表中必要的约束的追加

alter table st add constraint st_pk on(sno,tno)

–添加数据

class表

insert into class values('1402','软件工程','大三','一班');
insert into class values('1403','软件工程','大三','二班');
insert into class values('1404','软件工程','大三','三班');
insert into class values('1405','软件工程','大三','四班');

student表

insert into student values('14620201','王一乐',18,1,'06-9月-2014','太原','1402');
insert into student values('14620202','张晓一',19,0,'06-9月-2014','北京','1402');
insert into student values('14620203','贾东敏',18,0,'06-9月-2014','长沙','1402');
insert into student values('14620204','张三峰',20,1,'06-9月-2014','天津','1402');
insert into student values('14620205','李佳尔',19,0,'06-9月-2014','北京','1402');
insert into student values('14620402','陈静以',18,0,'06-9月-2014','太原','1404');
insert into student values('14620301','梁涛二',21,0,'06-9月-2014','哈尔滨','1403');
insert into student values('14620305','张伍雪',20,0,'06-9月-2014','北京','1403');
insert into student values('14620504','王凯东',22,1,'06-9月-2014','广东','1405');
insert into student values('14620306','张寒系',20,1,'06-9月-2014','长沙','1403');

course表

insert into course values('CN001','C语言',4);
insert into course values('CN002','C++',4);
insert into course values('CN003','高数',6);
insert into course values('CN004','大学英语',6);
insert into course values('CN005','ORACEL',4);
insert into course values('CN006','管理信息系统',4);
insert into course values('CN007','建模',4);
insert into course values('CN008','物理',6);

teacher表

insert into teacher values('T0001','张吖',33,'CN001');
insert into teacher values('T0002','李红',37,'CN003');
insert into teacher values('T0003','宋是',32,'CN004');
insert into teacher values('T0004','赵好',35,'CN007');
insert into teacher values('T0005','张英',28,'CN006');
insert into teacher values('T0006','杨英',39,'CN001');
insert into teacher values('T0007','杨杰',47,'CN008');
insert into teacher values('T0008','张斌',27,'CN002');
insert into teacher values('T0010','张霞',37,'CN005');
insert into teacher values('T0011','乐倩',40,'CN002');
insert into teacher values('T0012','王慧',41,'CN001');

st表

insert into st values('14620202','T0001',6);
insert into st values('14620202','T0002',4);
insert into st values('14620203','T0004',4);
insert into st values('14620204','T0005',6);
insert into st values('14620205','T0007',4);
insert into st values('14620201','T0006',6);

PS：如果有数据插入不进去原因是外键约束可以选择以下方法：

alter table <表名>  nocheck constraint;
--插入前先关闭约束限制
alter table <表名> check constraint;
--插入后打开约束限制