阶段项目:学生信息管理系统数据库设计

问题描述：

       上海某高校拟开发一套高校信息管理系统，该系统包括学生档案管理、学生成绩管理、教员信息管理、教务排课等模块，实现学生档案录入、修改、查询，成绩录入、修改、查询，教务排课管理等功能。
       开发此模块涉及两大部分：后台数据库的设计、前端界面的开发

一、后台数据库的设计

       为了简化起见，我们暂讨论“学生成绩管理”子模块，该模块具有成绩输入，成绩查询，名次排序，分数统计等功能。
        现要求对“学生成绩管理”子模块进行数据库的设计并实现，数据库保存在:E\MyApp目录下，文件增长率为10% 。

1）设计数据库

use master
if exists(select * from sysdatabases where name='student_manage')  --判断student_manage库是否存在，若存在则先删除
   drop database student_manage

create database student_manage                    --创建数据库student_manage
on primary
(
name='student_manage',
filename='E\MyApp\student_manage.mdf',
size=3,
filegrowth=10%
)
log on
(
name='student_manage_log',
filename='E\MyApp\student_manage.ldf',
size=2,
filegrowth=10%
)

--创建学生信息表
use student_manage
create table stuInfo(stuName varchar(10) not null,
stuNo varchar(20) not null,
stuSex varchar(5) not null,
stuAge int not null,
stuSeat int not null,
stuAddress text null
)

--创建学生成绩表
create table stuMarks(examNo varchar(15) not null,
stuNo varchar(20) not null,
writtenExam int null,
labExam int null
)
--创建约束
alter table stuInfo add constraint pk_stuName primary key(stuName)
alter table stuInfo add constraint ck_stuNo check (stuNo like 's253[0-9][0-9]')
alter table stuInfo add constraint ck_stuSex check (stuSex in ('男','女'))
alter table stuInfo add constraint df_stuSex default '男' for stuSex
alter table stuInfo add constraint ck_stuAge check (stuAge between 15 and 50)
alter table stuInfo add constraint ck_stuSeat check (stuSeat between 0 and 30)
alter table stuInfo add constraint df_stuAddress default '地址不详' for stuAddress

alter table stuMarks add constraint uq_examNo unique (examNo)
alter table stuMarks add constraint ck_examNo check (examNo like 'E200507[0-9][0-9][0-9][0-9]')
alter table stuMarks add constraint df_writtenExam default 0 for writtenExam
alter table stuMarks add constraint ck_writtenExam check (writtenExam between 0 and 100)
alter table stuMarks add constraint df_labExam default 0 for labExam
alter table stuMarks add constraint ck_labExam check (labExam between 0 and 100)

2）插入数据

insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuSeat,stuAddress)
values('张秋丽','s25301','男',18,2,'北京海淀')
insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuSeat)
values('李文才','s25302','男',31,5)
insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuSeat,stuAddress)
values('李斯文','s25303','女',22,3,'河南洛阳')
insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuSeat,stuAddress)
values('欧阳俊雄','s25304','男',28,7,'新疆克拉玛依')

insert into stuMarks(examNo,stuNo,writtenExam,labExam)
values('E2005070001','s25301',80,58)
insert into stuMarks(examNo,stuNo,writtenExam)
values('E2005070002','s25302',50)
insert into stuMarks(examNo,stuNo,writtenExam,labExam)
values('E2005070003','s25303',97,82)

3）查询数据

1.查询两表的数据

select*from stuInfo
select*from stuMarks 

2.查询男学员名单

select *from stuInfo where stuSex='男'

3.查询笔试成绩优秀的学员情况（成绩在75～100之间)

Select stuName,stuInfo.stuNo,stuSex,stuAge,stuSeat,stuAddress,examNo,writtenExam,labExam
from stuInfo join stuMarks on stuInfo.stuNo=stuMarks.stuNo
where writtenExam between 75 and 100  

4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩

select stuName,writtenExam,labExam
from stuInfo join stuMarks on stuInfo.stuNo=stuMarks.stuNo

5.统计笔试考试平均分和机试考试平均分

select avg(writtenExam)as '笔试平均分',avg(labExam)as'机试平均分' from stuMarks  

6.统计参加本次考试的学员人数

select count(examNo)as'参加考试人数' from stuMarks  

7.查询没有通过考试的人数（笔试或机试小于60分）

select count(examNo)as'未通过考试人数' from stuMarks where writtenExam<60 or labExam<60

8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分

select stuNo,writtenExam,labExam,(writtenExam+labExam)/2as avgExam
from stuMarks

9.排名次（按平均分从高到低排序），显示学号、平均分

select stuNo,(writtenExam+labExam)/2as avgExam
from stuMarks
order by avgExam desc   

10.排名次(按平均分从高到低排序),显示姓名,笔试成绩,机试成绩,平均分

select stuName,writtenExam,labExam,(writtenExam+labExam)/2as avgExam
from stuInfo join stuMarks on stuInfo.stuNo=stuMarks.stuNo
order by avgExam desc

11.根据平均分,显示前两名信息,包括姓名、笔试成绩、机试成绩、平均分

select top 2 stuName,writtenExam,labExam,(writtenExam+labExam)/2as avgExam
from stuInfo join stuMarks on stuInfo.stuNo=stuMarks.stuNo
order by avgExam desc

4）修改数据

笔试成绩都提5分

update stuMarks set writtenExam=writtenExam+5 select *
from stuMarks 

100分封顶（加分后超过100分的，按100分计算）

update stuMarks set writtenExam=100 where writtenExam>100 

查看修改后的数据

select * from stuMarks 

5）管理数据

1.导出数据到各种异构数据源、导出脚本

1.1 在SQL Server数据库之间进行数据导入导出

可以使用select into语句导出数据：
        在SQL Server中使用最广泛的就是通过select into语句导出数据，select into语句同时具备两个功能：
       ①根据select后跟的字段以及into后面跟的表名建立空表（如果select后是*, 空表的结构和from所指的表的结构相同);
        ②将select查出的数据插入到这个空表中。在使用select into语句时，into后跟的表必须在数据库不存在，否则出错。

还可以使用insert into 和 update插入和更新数据

1.2 使用opendatasource和openrowset在不同类型的数据库之间导入导出数据
       在异构的数据库之间进行数据传输，可以使用SQL Server提供的两个系统函数opendatasource和openrowset,其中opendatasource可以打开任何支持OLE DB的数据库，并且可以将opendatasource做为select、updte、insert和delete后所跟的表名。

2.附加和分离数据库

附加数据库：
选中数据库，然后鼠标右击，选择【附加】

然后点【添加】，找到数据库保存的路径，然后确定即可

分离数据库：
选中要分离的数据库，鼠标右击

【任务】—【分离】

勾选要分离的数据库，再确定即可

二、前端界面的开发（后期我们可以用dNet开发）

略~~~

总结

通过本次项目案例练习了：
       在SQL Server Management Studio中进行常用的数据库操作
       编写SQL语句执行增、删、改、查操作
巩固的知识：
       数据库的常用操作：建库、建表、加约束、建关系
       常用的约束类型：主键、外键、非空、默认值、检查约束
       insert 语句: Insert into 表名(字段1，字段2，…) values (值1, 值2,…)
       update语句: Update 表名 set 字段＝值 where …
       条件查询：select …from 表名 where …
       排序：select …from 表名 order by …
       Top语句： select top 2….from 表名…
       统计函数：select avg( …) as 平均分 ….
       选择查询： select ….where labExam between 70 and 90
       内部连接：select …from 表1 inner jion 表2 …