sql.sql

初学vb,做的第一个系统.一个星期内看vb并做完这个系统的.

--创建数据库
--create database std_manage;

--用户信息表
create table admin
(
	id  varchar(10),
	pwd varchar(10),
	primary key(id)
);

--民族信息表
create table nation
(
	id 		smallint,
	name	varchar(10),
	primary key(id)
);

--政治面貌
create table policy
(
	id smallint,
	name varchar(8),
	primary key(id)
);

--学院
create table college
(
	id smallint,
	name varchar(24),
	primary key(id)
)

--专业
create table major
(
	id varchar(10),
	name varchar(24),
	college smallint,
	primary key(id,college),
	foreign key(college) references college(id)
)

--学生信息表
create table stu
(
	no varchar(10),
	name varchar(10),
	sex char(2),
	nation smallint,
	policy smallint,
	birth varchar(4),
	addr varchar(50),
	post char(6),
	college smallint,
	enter_year char(4),
	major varchar(10),
	class varchar(10),
	primary key(no),
	foreign key(nation) references nation(id),
	foreign key(policy) references policy(id),
	foreign key(college) references college(id),
	foreign key(major) references major(id)
);

--课程类别
create table type
(
	id smallint,
	name char(2),
	primary key(id)
)

--科目信息表
create table subject
(
	id varchar(10),
	name varchar(20),
	type smallint,
	teacher varchar(10),
	addr varchar(20)
	primary key(id),
	foreign key(type) references type(id)
);

--成绩表
create table grade
(
	sub_id		varchar(10),
	stu_no		varchar(10),
	grade			float,
	primary key(sub_id,stu_no),
	foreign key(sub_id)references subject(id),
	foreign key(stu_no)references stu(no)
);

--插入民族信息
insert into nation values(1,'汉族');
insert into nation values(2,'蒙古族');
insert into nation values(3,'回族');
insert into nation values(4,'藏族');
insert into nation values(5,'维吾尔族');
insert into nation values(6,'苗族');
insert into nation values(7,'彝族');
insert into nation values(8,'壮族');
insert into nation values(9,'布依族');
insert into nation values(10,'朝鲜族');
insert into nation values(11,'满族');
insert into nation values(12,'侗族');
insert into nation values(13,'瑶族');
insert into nation values(14,'白族');
insert into nation values(15,'土家族');
insert into nation values(16,'哈尼族');
insert into nation values(17,'哈萨克族');
insert into nation values(18,'傣族');
insert into nation values(19,'黎族');
insert into nation values(20,'僳僳族');
insert into nation values(21,'佤族');
insert into nation values(22,'畲族');
insert into nation values(23,'高山族');
insert into nation values(24,'拉祜族');
insert into nation values(25,'水族');
insert into nation values(26,'东乡族');
insert into nation values(27,'纳西族');
insert into nation values(28,'景颇族');
insert into nation values(29,'柯尔克孜族');
insert into nation values(30,'土族');
insert into nation values(31,'达斡尔族');
insert into nation values(32,'仫佬族');
insert into nation values(33,'羌族');
insert into nation values(34,'布朗族');
insert into nation values(35,'撒拉族');
insert into nation values(36,'毛南族');
insert into nation values(37,'仡佬族');
insert into nation values(38,'锡伯族');
insert into nation values(39,'阿昌族');
insert into nation values(40,'普米族');
insert into nation values(41,'塔吉克族');
insert into nation values(42,'怒族');
insert into nation values(43,'乌孜别克族');
insert into nation values(44,'俄罗斯族');
insert into nation values(45,'鄂温克族');
insert into nation values(46,'德昂族');
insert into nation values(47,'保安族');
insert into nation values(48,'裕固族');
insert into nation values(49,'京族');
insert into nation values(50,'塔塔尔族');
insert into nation values(51,'独龙族');
insert into nation values(52,'鄂伦春族');
insert into nation values(53,'赫哲族');
insert into nation values(54,'门巴族');
insert into nation values(55,'珞巴族');
insert into nation values(56,'基诺族');

--插入政治面貌信息
insert into policy values(1,'中共党员');
insert into policy values(2,'共青团员');
insert into policy values(3,'群众');
insert into policy values(4,'其他');

--插入课程类别信息
insert into type values(1,'理');
insert into type values(2,'工');
insert into type values(3,'文');
insert into type values(4,'史');
insert into type values(5,'教');
insert into type values(6,'法');
insert into type values(7,'经');
insert into type values(8,'管');
insert into type values(9,'哲');
insert into type values(10,'医');
insert into type values(11,'艺');