Oracle笔记（七）

--完成一个简单的应用系统开发设计
--背景：本次实践中实现学生信息管理系统的数据库结构的创建
--需求：实现学生信息管理系统中教学模块的数据库设计
--功能描述：教学模块包含学生基本信息管理，课程信息管理
--班级信息管理，选课信息管理，教师基本信息管理
--教师授课信息管理，
--详细信息描述如下
--学生基本信息包括：学号，姓名，年龄，性别，入学日期，家庭住址
--教师基本信息包括：教工号，姓名，年龄，担当的课程号
--课程基本信息包括：课程号，课程名，课程学分
--选课信息包括：学号、教工号，成绩（由于每门课可能有多个授课老师，所以在选课时需同时指定此门课程的授课老师，故在此存储的时老师的教工号）
--班级信息包括：班级号，系，年级，班级
--各实体之间的关系如下：
--一个学生只能属于一个班级，一个班级有多个学生
--一个学生可以选多门课程，一个课程可以被多个学生选
--一个老师只能教一门课，一门课程可以由多个老师教
--针对项目要求，创建一个school方案
--前期先建立方案中的基本数据对象
--建立E-R图

--pk fk 主码 外码

savepoint a; --设置一个名为a的保存点

create user school identified by zxx123;
conn sys as sysdba/manage;
grant dba to school ;

--创建class表
create table class(
clno varchar2(5),
deptno varchar2(40) not null,
grade varchar2(8) not null,
branch varchar2(20) not null
);
alter table class add constraint pk_class primary key(clno);
--创建课程表
create table course(
cno varchar2(5) not null,
name varchar2(60) not null,
score number(2) not null
);
alter table course
add constraint pk_course primary key(cno);
--创建学生表
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_stu foreign key (clno) references class(clno)
);
--教师表
create table teacher(
tno varchar2(5) primary key,
name varchar2(12) not null,
age number(2),
cno varchar2(5) not null
);
--选课表
create table st(
sno varchar2(8) not null,
tno varchar2(5) not null,
grade number(3) default 0
);

--=====================================================================

--1班级信息表添加约束
--check约束
alter table class
add constraint ck_class_grade
check (grade in ('大一','大二','大三','大四'));
--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 constraints fk_student_clno foreign key(clno)
references class(clno);
--3课程信息表约束
alter table course
add constraint ft_course 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);
--外键约束
alter table st
add constraint fk_st_sno foreign key (sno)
references student (sno);
alter table st
add constraint fk_st_sno foreign key (tno)
references teacher (tno);
--check约束
alter table st
add constraint ck_st_grade check(grade >=0 and grade <=9);

--在必要的类表上创建索引
--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);

--四种数据字典视图
--user_、all_、dba_、v$
user_tables;
user_objects;
user_catelog;
user_tab_comments;
user_col_comments;

--example
select table_name from user_tables;
select object_name from user_objects where object_type='TABLE';
select * from user_catelog;
select * from user_tab_comments;
--对表增加注释
--add comments to table
comment on table student is '学生信息比';
comment on table teacher is '教师信息表';
comment on table class is '班级信息表';
comment on table course is '课程信息表';
comment on table st is  '选课信息表';

--对表的属性列增加注释
--add comments to the columns
comment on column student.sno is '学号';
comment on column student.sex is '性别，1：男，0：女';
comment on column st.grade is '成绩，默认为0';

--属性列注释信息的查询
select table_name,column_name,comments
from user_col_comments
where table_name='STUDENT';

--索引信息
user_indexes
select table name,index_name
from user_indexes
where table_name='STUDENT';

--约束信息查询
user_constraints
select table_name constraint_name,constraint_type
from user_constraints
where table_name='STUDENT';

备份地址: 【Oracle笔记（七）】