oracle 建表

CREATE TABLE STUDENT_INFO(
dept_no varchar2(5) null,
s_no varchar2(12) constraint s_no_p primary key,
s_name varchar2(20) null,
s_sex varchar2(2) null,
s_birthday date null,
s_address varchar2(200) null,
s_tel varchar2(50) null,
s_email varchar2(100) null,
spec_no varchar2(12) null,
code varchar2(6) null,
in_cj number(5,2) null
);


CREATE TABLE COURSE_INFO(
course_no varchar2(12) constraint course_no_p primary key,
course_name varchar2(80) not null,
dept_no varchar2(5) null,
spec_no varchar2(12) null,
course_lb_no varchar2(2) null
);

CREATE TABLE CJ_INFO(
s_no varchar2(12) constraint s_no_r references STUDENT_INFO(s_no) ,
course_no varchar2(12) constraint course_no_r references COURSE_INFO(course_no),
cj number(5,0) null,
bs varchar2(2) null,
in_time date null
);

ALTER TABLE TEST15222.STUDENT_INFO
ADD CONSTRAINT s_sex_check check (s_sex='男' or s_sex='女');

alter table TEST15222.STUDENT_INFO
add constraint s_tel_unique unique s_tel;

alter table TEST15222.STUDENT_INFO
Modify s_name constraint s_name_notnull not null;

select table_name,constraint_name,constraint_type from user_constraints
where table_name='STUDENT_INFO';

select table_name,constraint_name,constraint_type from user_constraints
where table_name='COURSE_SELECTION_INFO';




/* 1 系部信息表deptinfo*/
create table deptinfo(
dept_no varchar2(5) constraint pk_deptinfo_deptno primary key,
dept_name varchar2(50)
);



/* 2 专业表 specialty_info*/
create table specialty_info(
spec_no varchar2(12) constraint pk_specialty_info_spec_no primary key,
spec_name varchar2(12) null,
dept_no varchar2(5) constraint fk_specialty_info_dept_no references deptinfo(dept_no)
);



/* 3 学生表 student_info */
create table student_info(
dept_no varchar2(5) constraint fk_student_info_1 references deptinfo(dept_no),
s_no varchar2(12) constraint pk_student_info primary key,
s_name varchar2(20) ,
s_sex varchar2(2) constraint ch_student_info check (s_sex='女' or s_sex='男'),
s_birthday date,
s_address varchar2(200),
s_tel varchar2(50),
s_email varchar2(100),
spec_no varchar2(12) constraint fk_student_info_2 references specialty_info(spec_no),
code varchar2(6),
in_cj number(5,2)
)；


/* 4 课程类别 course_lb_info */
create table course_lb_info(
course_lb_no varchar2(2) constraint pk_course_lb_info primary key,
course_lb_name varchar2(50)
);



/* 5 课程表 course_info */
create table course_info(
course_no varchar2(12) constraint pk_course_info primary key,
course_name varchar2(80) not null,
dept_no varchar2(5) constraint fk_course_info_1 references deptinfo(dept_no),
spec_no varchar2(12) constraint fk_course_info_2 references specialty_info(spec_no),
course_lb_no varchar2(2) constraint fk_course_info_3 references course_lb_info(course_lb_no)
);



/* 6 成绩表 cj_info */
create table cj_info(
s_no varchar2(12) not null references student_in

fo(s_no),
course_no varchar2(12) not null references course_info(course_no),
cj number(5,0),
bs varchar2(2) ,
in_time date,
PRIMARY KEY ( s_no,course_no)
);



/* 7 班级表 class_info */
create table class_info(
class_no varchar2(12) constraint pk_class_info primary key,
class_name varchar2(50),
class_num number(5,0),
spec_no varchar2(12) constraint fk_class_info_1 references specialty_info(spec_no)
);



/* 8 教师表 teacher_info */
create table teacher_info(
teac_no varchar2(10) constraint pk_teacher_info primary key,
teac_name varchar2(50),
dept_no varchar2(5) constraint fk_teacher_info_1 references deptinfo(dept_no),
teac_sex varchar2(2) constraint ck_teacher_info check (teac_sex='女' or teac_sex='男')
);



/* 9 选课表 course_selection_info */
create table course_selection_info(
id varchar2(12) constraint pk_course_selection_info primary key,
course_no varchar2(12) constraint fk_ course_selection_info_1 references course_info(course_no),
class_no varchar2(12) constraint fk_course_selection_info_2 references class_info(class_no),
term_no varchar2(12),
teac_no varchar2(12),
student_num number(5,0),
oper_no varchar2(12),
oper_date date,
modified_by varchar2(12),
modified_date date
);

/* 9 选课表 course_selection_info */
create table course_selection_info(
id varchar2(12) constraint pk_course_selection_info primary key,
course_no varchar2(12) not null references course_info(course_no),
class_no varchar2(12) constraint fk_course_selection_info_2 references class_info(class_no),
term_no varchar2(12),
teac_no varchar2(12),
student_num number(5,0),
oper_no varchar2(12),
oper_date date,
modified_by varchar2(12),
modified_date date
);