学生成绩管理系统 选课系统 学期末课程设计 .zip

USE [master] GO /****** Object: Database [deeptech] Script Date: 2019/1/22 11:21:39 ******/ CREATE DATABASE [deeptech] CONTAINMENT = NONE ON PRIMARY ( NAME = N'deeptech', FILENAME = N'C:\deeptech.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'deeptech_log', FILENAME = N'C:\deeptech_log.ldf' , SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [deeptech] SET COMPATIBILITY_LEVEL = 120 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [deeptech].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [deeptech] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [deeptech] SET ANSI_NULLS OFF GO ALTER DATABASE [deeptech] SET ANSI_PADDING OFF GO ALTER DATABASE [deeptech] SET ANSI_WARNINGS OFF GO ALTER DATABASE [deeptech] SET ARITHABORT OFF GO ALTER DATABASE [deeptech] SET AUTO_CLOSE ON GO ALTER DATABASE [deeptech] SET AUTO_SHRINK OFF GO ALTER DATABASE [deeptech] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [deeptech] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [deeptech] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [deeptech] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [deeptech] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [deeptech] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [deeptech] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [deeptech] SET DISABLE_BROKER GO ALTER DATABASE [deeptech] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [deeptech] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [deeptech] SET TRUSTWORTHY OFF GO ALTER DATABASE [deeptech] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [deeptech] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [deeptech] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [deeptech] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [deeptech] SET RECOVERY SIMPLE GO ALTER DATABASE [deeptech] SET MULTI_USER GO ALTER DATABASE [deeptech] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [deeptech] SET DB_CHAINING OFF GO ALTER DATABASE [deeptech] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GO ALTER DATABASE [deeptech] SET TARGET_RECOVERY_TIME = 0 SECONDS GO ALTER DATABASE [deeptech] SET DELAYED_DURABILITY = DISABLED GO USE [deeptech] GO /****** Object: UserDefinedFunction [dbo].[fun_毕业学生学分统计] Script Date: 2019/1/22 11:21:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[fun_毕业学生学分统计]() returns @table table(学生学号 varchar(20),学生姓名 varchar(10),学院 varchar(50),专业 varchar(20),学分总数 float,选课门数 int,学时总数 int,是否毕业 varchar(10)) as begin declare @学生学号 varchar(20),@学生姓名 varchar(10),@学院 varchar(50),@专业 varchar(20),@学分总数 float,@选课门数 int,@学时总数 int declare @当前学年排序 int,@当前学年代码 varchar(20),@入学学年代码 varchar(20),@是否毕业 varchar(10) select @当前学年代码=学年代码 from 学年代码表 where 排序 in (select max(排序) from 学年代码表) select @当前学年排序=排序 from 学年代码表 where 学年代码=@当前学年代码 select @入学学年代码=学年代码 from 学年代码表 where 排序=@当前学年排序-3 declare cu_毕业学生 cursor for(select 学号,姓名,学院,专业 from 学生表 where 入学学年代码=@入学学年代码) open cu_毕业学生 fetch next from cu_毕业学生 into @学生学号,@学生姓名,@学院,@专业 while (@@fetch_status=0) begin select @选课门数=count(*) from 选课表 where 选课表.学号=@学生学号 select @学时总数=sum(课程学时) from 课程表,选课表,上课表 where 上课表.授课号=选课表.授课号 and 课程表.课程号=上课表.课程号 and 选课表.学号=@学生学号 group by 学号 select @学分总数=sum(课程学分) from 课程表,选课表,上课表 where 上课表.授课号=选课表.授课号 and 课程表.课程号=上课表.课程号 and 选课表.学号=@学生学号 and 成绩>=60 and 成绩 is not null group by 学号 if @学分总数>=30 begin set @是否毕业='是' end if @学分总数<30 begin set @是否毕业='否' end insert into @table (学生学号,学生姓名,学院,专业,学分总数,选课门数,学时总数,是否毕业) values(@学生学号,@学生姓名,@学院,@专业,@学分总数,@选课门数,@学时总数,@是否毕业) fetch next from cu_毕业学生 into @学生学号,@学生姓名,@学院,@专业 end close cu_毕业学生 return; end GO /****** Object: UserDefinedFunction [dbo].[fun_单个教师工作量统计] Script Date: 2019/1/22 11:21:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFI�