createdb2.sql

数据中的一个简单的创建语句示范

drop table student cascade;
drop table course cascade;
drop table c_s cascade;

create table student
(
	id	char(8),
	name	char(9),
	gender	char(2) default '男',
	dept	char(20),
	birthday	date,

	primary key(id),
	check(gender in('男','女'))
);

create table course
(
	course_no	char(4),
	name	char(30),
	credit	integer,
	dept	char(20),

	primary key(course_no)
);

create table c_s
(
	student_id	char(8),
	course_no	char(4),
	grade	integer,

	primary key(student_id, course_no),
	foreign key(student_id) references student(id),
	foreign key(course_no) references course(course_no)
);

insert into student(id, name, gender, dept, birthday)
	values('20060401','张三','男','ma','1987-01-12');
insert into student(id, name, gender, dept, birthday)
	values('20060203','李四','女','cs','1988-10-25');
insert into student(id, name, gender, dept, birthday)
	values('20060204','王五','男','cs','1988-03-17');

insert into course(course_no, name, credit, dept)
	values('c01','数据库原理',4,'cs');
insert into course(course_no, name, credit, dept)
	values('c02','操作系统',4,'cs');
insert into course(course_no, name, credit, dept)
	values('c03','网络与通信',3,'cs');
insert into course(course_no, name, credit, dept)
	values('c11','高等数学',4,'ma');
insert into course(course_no, name, credit, dept)
	values('c12','线性代数',4,'ma');
insert into course(course_no, name, credit, dept)
	values('c25','高能物理',4,'ph');

insert into c_s(student_id,course_no,grade)
	values('20060401','c11',90);
insert into c_s(student_id,course_no,grade)
	values('20060401','c12',88);
insert into c_s(student_id,course_no,grade)
	values('20060203','c01',95);
insert into c_s(student_id,course_no,grade)
	values('20060203','c02',90);
insert into c_s(student_id,course_no,grade)
	values('20060203','c03',94);
insert into c_s(student_id,course_no,grade)
	values('20060204','c03',88);