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

曾经终败给现在 2023-01-18 06:44 155阅读 0赞

### 目录 ###

*  问题描述：
 *  一、后台数据库的设计
 *   *  1）设计数据库
     *  2）插入数据
     *  3）查询数据
     *  4）修改数据
     *  5）管理数据
     *   *  1.导出数据到各种异构数据源、导出脚本
         *  2.附加和分离数据库
 *  二、前端界面的开发（后期我们可以用dNet开发）
 *  总结

# 问题描述： #

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

# 一、后台数据库的设计 #

**为了简化起见，我们暂讨论“学生成绩管理”子模块，该模块具有成绩输入，成绩查询，名次排序，分数统计等功能。**  
        **现要求对“学生成绩管理”子模块进行数据库的设计并实现，数据库保存在: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.附加和分离数据库 ###

**附加数据库：**  
选中数据库，然后鼠标右击，选择【附加】  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70]  
然后点【添加】，找到数据库保存的路径，然后确定即可  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 1]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 2]

【任务】—【分离】  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 3]  
勾选要分离的数据库，再确定即可  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 4]

# 二、前端界面的开发（后期我们可以用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 …

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70]: /images/20221021/0e6e0168b2274868ab4ce2f918ae7281.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 1]: /images/20221021/1c0b0557a20248c7ad44cc9f1f88c65d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 2]: /images/20221021/06684b130ad648a099d52788a17e7835.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 3]: /images/20221021/a60259d0b0dd458f8e3312e8d184c8ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 4]: /images/20221021/8e369d210af847978c714d0bce71e714.png