create database StudentInfoManagement;
use StudentInfoManagement;
#建表
#院系表
create table Department(
Dno char(12),
Dname char(12),
constraint primary key PK_Department (Dno)
);
#班级表
create table Class(
Clno char(12),
Clname char(12),
Dno char(12),
constraint primary key PK_Class (Clno),
constraint foreign key FK_Class_Department (Dno) references Department(Dno)
);
#学生表
create table Student(
Sno char(12),
Sname char(8),
Ssex char(2) check(Ssex in ('男','女')),
Sage smallint check(Sage > 0),
Clno char(12),
constraint primary key PK_Student (Sno),
constraint foreign key FK_Student_Class (Clno) references Class(Clno)
);
#课程表
create table Course(
Cno char(12),
Cname char(12),
Cteacher char(8),
Ccredit smallint check(Ccredit > 0),
constraint primary key PK_Course (Cno)
);
#选课表
create table SC(
Sno char(12),
Cno char(12),
Grade smallint check(Grade < 100 and Grade > 0),
constraint foreign key FK_SC_Student (Sno) references Student(Sno),
constraint foreign key FK_SC_Course (Cno) references Course(Cno),
constraint primary key PK_SC (Sno,Cno)
);
#用户表
create table User(
username char(12),
password char(12) not null,
level char(6) check(level in ('用户','管理员')),
constraint primary key PK_User (username)
);
#插入测试数据
insert into department
values('201501','软件学院');
insert into department
values('201502','经管学院');
insert into department
values('201503','人文学院');
insert into department
values('201504','国际学院');
insert into department
values('201505','理学院');
insert into class
values('20150103','2015-3班','201501');#软件学院
insert into class
values('20150104','2015-4班','201501');#软件学院
insert into class
values('20150201','2015-1班','201502');#经管学院
insert into class
values('20150202','2015-2班','201502');#经管学院
insert into class
values('20150301','2015-1班','201503');#人文学院
insert into class
values('20150302','2015-2班','201503');#人文学院
insert into student
values('2015010312','张明','男',20,'20150103');
insert into student
values('2015010313','秦羽','女',19,'20150103');
insert into student
values('2015010314','刘翔','男',19,'20150103');
insert into course
values('1','高等数学','张三',4);
insert into course
values('2','数据库原理设计','李四',3);
insert into course
values('3','Java程序设计','王五',4);
insert into SC
values('2015010312','1',50);
insert into SC
values('2015010312','2',80);
insert into SC
values('2015010312','3',70);
insert into SC
values('2015010313','1',90);
insert into SC
values('2015010313','2',89);
insert into SC
values('2015010313','3',59);
insert into SC
values('2015010314','1',85);
insert into SC
values('2015010314','2',75);
insert into SC
values('2015010314','3',95);
insert into user
values('admin','666','管理员');
insert into user
values('user','123','用户');
#查询示例
##查询所有课程平均分
#select sc.cno cno,cname,avg(grade) avg
#from course,sc
#where course.cno = sc.cno
#group by cno
#order by cno;
#
##查询全部课程不及格率
#select cno,(select cname from course where cno = x.cno) cname,
#cast(100.0*(select count(sno) from sc where grade < 60 and cno = x.cno)/(select count(sno) from sc where cno = x.cno) as decimal(18,2)) rate
#from sc x
#group by cno;
#
##查选修某一门课程的总人数
#select cno,count(Sno) 总人数
#from SC
#group by Cno
#having Cno = '1';
#
##查询某一门课程的全部成绩排名
#select cno,student.Sno,Dname,Clname,Sname,Ssex,Sage,grade
#from department,class,student,sc
#where student.sno = sc.sno
#and class.Clno = student.Clno
#and department.Dno = class.Dno
#and cno = '1'
#order by grade desc;
https://chenqiwei.lanzoui.com/icWCdluiurg
密码:43b9
