学生信息管理数据库表

create database studb;
use studb

--1.用户表（用户ID，用户名，密码，权限）
drop table tbl_user;
create table tbl_user(
userid int(20) not null auto_increment,
username varchar(20) not null,
password varchar(20) not null,
role int(2),
primary key(userid)
);
select * from tbl_user;
insert into tbl_user values(13401,'hang','hang',1);
insert into tbl_user values(userid,'wang','wang',1);
insert into tbl_user values(userid,'王铭航','123',2);
insert into tbl_user values(userid,'姜楠','123',2);
insert into tbl_user values(userid,'蒋春燕','123',2);
insert into tbl_user values(userid,'吴哲','123',2);

--2.学生信息表（学号，姓名，出生日期，性别，学院，专业，班级，政治面貌，民族，电话，
家庭住址，邮箱，照片，家长信息，备注）
drop table tbl_student
create table tbl_student(
stuid varchar(20) not null,
name varchar(20)not null,
rdate date,
gender varchar(2),
institute varchar(20),
major varchar(20),
clazz varchar(10),
politics varchar(8),
nationality varchar(10),
phone varchar(20),
homeaddress varchar(100),
mail varchar(50),
phote blob,
parent varchar(100),
remark varchar(200) default '暂无',
primary key(stuid)
);
select * from tbl_student
insert into tbl_student values('090210301','詹姆斯','1981-11-11','男','医学院','临床医学',
'2班','中共党员','侗族','1588888888','贵州贵阳','zms@','','父亲：詹姆第，母亲：斯密斯',remark);
insert into tbl_student values('090210302','石头','2007-06-30','男','土木学院','测绘学',
'4班','共青团员','鄂温克族','1232','河北邯郸','st@','','父亲：纷纷，母亲：落落',remark);
insert into tbl_student values('090210303','Angela','2009-01-31','女','经管学院','经济学',
'1班','中共党员','汉族','1234456','湖南长沙','ag@','','父亲：王岳伦，母亲：李湘',remark);
insert into tbl_student values('090210304','鲁尼','1983-3-23','男','计算机学院','计算机',
'3班','群众','白族','5864','曼彻斯特','ln@','','父亲：鲁鲁，母亲：妮妮',remark);
insert into tbl_student values('090210305','莎拉波娃','1982-10-9','女','土木学院','测绘学',
'1班','国民党','女娲族','15821','莫斯科','slbw@','','父亲：老沙',remark);
insert into tbl_student values('090210306','安倍','1953-11-11','男','医学院','临床医学',
'5班','自民党','狼族','250','日本东京','ab@','','父亲：wu，母亲：无',remark);
insert into tbl_student values('090210307','孙杨','1991-4-2','男','计算机学院','计算机',
'4班','中共党员','回族','3535','浙江杭州','sun@','','父亲：老孙，母亲：孙母',remark);
insert into tbl_student values('090210308','张默','1978-6-14','男','土木学院','测绘学',
'2班','群众','土家族','5223','重庆','zhangmo@','','父

亲：张国立，母亲：邓婕',remark);
insert into tbl_student values('090210309','岳灵珊','1232-5-3','女','医学院','临床医学',
'2班','中共党员','华山族','111','陕西华山','yls@','','父亲：岳不群，母亲：宁中则',remark);
insert into tbl_student values('090210310','杨延昭','1423-7-7','男','土木学院','测绘学',
'1班','群众','彝族','158246453342','河南开封','yang@','','父亲：杨业，母亲：佘赛花',remark);


--3.课程表（课程号，课程名，学分,学时）
drop table tbl_course
create table tbl_course(
subid varchar(20) primary key,
subject varchar(20),
coursenum varchar(20) not null,
coursetime varchar(10)
);
select * from tbl_course
insert into tbl_course values('1201','数学','3分','36课时');
insert into tbl_course values('1202','数据库','2.5分','42课时');
insert into tbl_course values('1203','英语','1分','42课时');
insert into tbl_course values('1204','政治','1.5分','72课时');
insert into tbl_course values('1205','大学语文','5分','36课时');
insert into tbl_course values('1206','党课','2分','72课时');

--4.成绩表（成绩表ID，学号，课程号，考试类型，成绩，学年）
drop table tbl_score
create table tbl_score(
scoreid int(10) not null auto_increment,
stuid varchar(20),
subid varchar(20),
type varchar(10),
score float,
grade varchar(20),
primary key(scoreid),
constraint fk_score_stu foreign key(stuid) references tbl_student(stuid),
constraint fk_score_cou foreign key(subid) references tbl_course(subid)
);
select * from tbl_score
insert into tbl_score values(12000,'090210301','1201','闭卷','78','2012-2013春季');
insert into tbl_score values('','090210301','1202','开卷','90','2013-2014秋季');
insert into tbl_score values('','090210301','1203','闭卷','84','2010-2011春季');
insert into tbl_score values('','090210301','1204','开卷','87','2012-2013秋季');
insert into tbl_score values('','090210301','1205','开卷','75','2012-2013秋季');
insert into tbl_score values('','090210301','1206','开卷','66','2012-2013秋季');
insert into tbl_score values('','090210302','1201','闭卷','95','2012-2013春季');
insert into tbl_score values('','090210302','1202','开卷','75','2013-2014秋季');
insert into tbl_score values('','090210302','1203','闭卷','67','2010-2011春季');
insert into tbl_score values('','090210302','1204','开卷','92','2012-2013秋季');
insert into tbl_score values('','090210302','1205','开卷','73','2012-2013秋季');
insert into tbl_score values('','090210302','1206','开卷','83','2012-2013秋季');
insert into tbl_score values('','090210303','1201','闭卷','95','2012-2013春季');
insert into tbl_score values('','090210303','1202','开卷','75','2013-2014秋季');
insert into tbl_score values('','090210303','1203','闭卷','67','2010-2011春季');
insert into tbl_score values('','090210303','1204','

开卷','92','2012-2013秋季');
insert into tbl_score values('','090210303','1205','开卷','73','2012-2013秋季');
insert into tbl_score values('','090210303','1206','开卷','83','2012-2013秋季');
insert into tbl_score values('','090210304','1201','闭卷','95','2012-2013春季');
insert into tbl_score values('','090210304','1202','开卷','75','2013-2014秋季');
insert into tbl_score values('','090210304','1203','闭卷','67','2010-2011春季');
insert into tbl_score values('','090210304','1204','开卷','92','2012-2013秋季');
insert into tbl_score values('','090210304','1205','开卷','73','2012-2013秋季');
insert into tbl_score values('','090210304','1206','开卷','83','2012-2013秋季');
insert into tbl_score values('','090210305','1201','闭卷','95','2012-2013春季');
insert into tbl_score values('','090210305','1202','开卷','75','2013-2014秋季');
insert into tbl_score values('','090210305','1203','闭卷','67','2010-2011春季');
insert into tbl_score values('','090210305','1204','开卷','92','2012-2013秋季');
insert into tbl_score values('','090210305','1205','开卷','73','2012-2013秋季');
insert into tbl_score values('','090210305','1206','开卷','83','2012-2013秋季');
insert into tbl_score values('','090210306','1201','闭卷','95','2012-2013春季');
insert into tbl_score values('','090210306','1202','开卷','75','2013-2014秋季');
insert into tbl_score values('','090210306','1203','闭卷','67','2010-2011春季');
insert into tbl_score values('','090210306','1204','开卷','92','2012-2013秋季');
insert into tbl_score values('','090210307','1201','闭卷','95','2012-2013春季');
insert into tbl_score values('','090210307','1202','开卷','75','2013-2014秋季');
insert into tbl_score values('','090210307','1203','闭卷','67','2010-2011春季');
insert into tbl_score values('','090210308','1201','闭卷','95','2012-2013春季');
insert into tbl_score values('','090210308','1202','开卷','75','2013-2014秋季');
insert into tbl_score values('','090210308','1203','闭卷','67','2010-2011春季');
insert into tbl_score values('','090210309','1201','闭卷','95','2012-2013春季');
insert into tbl_score values('','090210309','1202','开卷','75','2013-2014秋季');
insert into tbl_score values('','090210310','1203','闭卷','67','2010-2011春季');

--5.一卡通表（卡ID，学号，类别，余额，绑定银行卡号）
drop table tbl_card
create table tbl_card(
cardid int(10) not null auto_increment,
stuid varchar(20) not null,
type varchar(10) not null,
total varchar(10) not null,
bankno varchar(20),
primary key(cardid),
constraint fk_card_stu foreign key(stuid) references tbl_student(stuid)
);
select * from tbl_card
insert into tbl_card values(146300,'090210301','学生卡','66.5元','64145114144144521');
insert into tbl_card values('','090210302','学生卡','152.6元','6217585214521521');
insert into tbl_card values('','090210303','学生卡',

'202.3元','65235214616561');
insert into tbl_card values('','090210304','学生卡','2.5元','62175156512151');
insert into tbl_card values('','090210305','学生卡','69.6元','6416812054521');
insert into tbl_card values('','090210306','学生卡','241.5元','68712810588411');
insert into tbl_card values('','090210307','学生卡','120元','6217585214521521');
insert into tbl_card values('','090210308','学生卡','98.9元','62125842989884');
insert into tbl_card values('','090210309','学生卡','105.3元','68541254125654');
insert into tbl_card values('','090210310','学生卡','222元','644655467887251');

--6.奖惩信息表（奖惩ID，学号，奖惩类型，时间，奖惩情况）
drop table tbl_reward
create table tbl_reward(
rewardid int(10) not null auto_increment,
stuid varchar(20) not null,
rewardtype varchar(10),
rewardtime date,
codition varchar(100),
primary key(rewardid),
constraint fk_reward_stu foreign key(stuid) references tbl_student(stuid)
);
select * from tbl_reward
insert into tbl_reward values(30000,'090210301','奖励','2010-11-21','成绩优异，获得国家奖学金');
insert into tbl_reward values('','090210302','奖励','2013-8-31','校内三等奖学金');
insert into tbl_reward values('','090210304','惩罚','2009-12-31','记过惩罚');
insert into tbl_reward values('','090210307','奖励','2012-12-12','洪长存奖学金');
insert into tbl_reward values('','090210309','奖励','2011-5-4','三好学生标兵，国家励志奖学金，优秀学生干部');
insert into tbl_reward values('','090210310','惩罚','2008-6-25','开除学籍，留校察看');


--7.学籍异动表表（异动ID，学号，变动类型，变动时间，变动原因）
drop table tbl_change
create table tbl_change(
changeid int(20)not null auto_increment,
stuid varchar(20) not null,
changetype varchar(10) not null,
changetime date not null,
reason varchar(50),
primary key(changeid),
constraint fk_change_stu foreign key(stuid) references tbl_student(stuid)
);
select * from tbl_change
insert into tbl_change values(10001,'090210302','休学','2012-12-21','世界末日');
insert into tbl_change values('','090210303','转学','2009-11-01','学校收费太贵');
insert into tbl_change values('','090210309','休学','2013-05-31','做手术');