学生信息管理系统数据库设计.sql

该学生管理系统有学生学籍管理

INSERT INTO CJ_ZSH
   VALUES('050203','200507023203','张新','Photoshop','80','2')
INSERT INTO CJ_ZSH
   VALUES('050203','200507023203','张新','法律程序与法理','77','1')
INSERT INTO CJ_ZSH
   VALUES('050204','200507023204','周方圆','计算机网络技术','56','0')
INSERT INTO CJ_ZSH
   VALUES('050204','200507023204','周方圆','实用业务英语','70','3')
INSERT INTO CJ_ZSH
   VALUES('050204','200507023204','周方圆','网页制作','67','2')
INSERT INTO CJ_ZSH
   VALUES('050204','200507023204','周方圆','法律程序与法理','78','1')
INSERT INTO CJ_ZSH
   VALUES('050205','200507023205','禇楚楚','计算机网络技术','80','2')
INSERT INTO CJ_ZSH
   VALUES('050205','200507023205','禇楚楚','实用业务英语','75','3')
INSERT INTO CJ_ZSH
   VALUES('050205','200507023205','禇楚楚','Photoshop','87','2')
INSERT INTO CJ_ZSH
   VALUES('050205','200507023205','禇楚楚','法律程序与法理','78','1')
INSERT INTO CJ_ZSH
   VALUES('050206','200507023206','刘晓冰','计算机网络技术','85','2')
INSERT INTO CJ_ZSH
   VALUES('050206','200507023206','刘晓冰','实用业务英语','67','3')
INSERT INTO CJ_ZSH
   VALUES('050206','200507023206','刘晓冰','网页制作','89','2')
INSERT INTO CJ_ZSH
   VALUES('050206','200507023206','刘晓冰','大学生礼仪','78','1')
INSERT INTO CJ_ZSH
   VALUES('050207','200507023207','刘大东','计算机网络技术','50','0')
INSERT INTO CJ_ZSH
   VALUES('050207','200507023207','刘大东','实用业务英语','65','3')
INSERT INTO CJ_ZSH
   VALUES('050207','200507023207','刘大东','网页制作','70','2')
INSERT INTO CJ_ZSH
   VALUES('050207','200507023207','刘大东','大学生礼仪','76','1')
INSERT INTO CJ_ZSH
   VALUES('050207','200507023208','岳小兰','计算机网络技术','84','2')
INSERT INTO CJ_ZSH
   VALUES('050207','200507023208','岳小兰','实用业务英语','78','3')
INSERT INTO CJ_ZSH
   VALUES('050207','200507023208','岳小兰','Photoshop','81','2')
INSERT INTO CJ_ZSH
   VALUES('050207','200507023208','岳小兰','法律程序与法理','69','1')
INSERT INTO CJ_ZSH
   VALUES('050209','200507023209','马小璞','计算机网络技术','80','2')
INSERT INTO CJ_ZSH
   VALUES('050209','200507023209','马小璞','实用业务英语','76','3')
INSERT INTO CJ_ZSH
   VALUES('050209','200507023209','马小璞','网页制作 ','77','2')
INSERT INTO CJ_ZSH
   VALUES('050209','200507023209','马小璞','法律程序与法理','78','1')




-------创建基于表 XSXJ_ZSH CJ_ZSH 的视图 ENGLISH -----------
USE XSXI_ZSH
GO
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='ENGLISH'AND TYPE='V')
   DROP VIEW ENGLISH
GO 
CREATE VIEW ENGLISH WITH SCHEMABINDING
   AS
   SELECT XSXJ_ZSH.学号,XSXJ_ZSH.姓名,课程名称,分数,已得学分
   FROM dbo.XSXJ_ZSH,dbo.CJ_ZSH
   WHERE XSXJ_ZSH.学号=CJ_ZSH.学号 AND 课程名称='实用业务英语'
   WITH CHECK OPTION
GO
---------------
USE XSXJ_ZSH
GO

IF EXISTS(SELECT name FROM sysobjects
            WHERE name='KC_INFO' AND TYPE='P')
       DROP PROCEDURE KC_INFO
GO

CREATE PROCEDURE KC_INFO  @WL CHAR(20), @YY CHAR(20), @FL CHAR(20),@WY CHAR(20),@PS CHAR(20), @LY CHAR(20)
  as
  select 学号,姓名,课程名称,分数,已得学分
  FROM CJ_ZSH
  WHERE  课程名称 IN (@WL,@YY,@FL,@WY,@PS,@LY)
  GROUP BY 课程名称,学号,姓名,分数,已得学分
GO

EXEC KC_INFO '计算机网络技术','实用业务英语','法律程序与法理','网页制作 ','Photoshop','大学生礼仪'

-----------创建学生选的课程名称及考试情况的视图 XSA_CJ -----------
USE XSXI_ZSH
GO

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='XSA_CJ' )
   DROP VIEW XSA_CJ

CREATE VIEW XSA_CJ
   AS
   SELECT CJ_ZSH.考试编号,学号,姓名,课程名称,分数,已得学分
   FROM CJ_ZSH
 WITH CHECK OPTION
GO

--------------------------------创建索引--------------------------------------
------创建视图 WLJS, 创建基于视图WLJS的索引 XSXJ_IND----
USE XSXI_ZSH
GO
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='WLJS'AND TYPE='V')
   DROP VIEW WLJS
GO 
CREATE VIEW WLJS WITH SCHEMABINDING
   AS
   SELECT XSXJ_ZSH.学号,XSXJ_ZSH.姓名,课程名称,分数,已得学分
   FROM dbo.XSXJ_ZSH,dbo.CJ_ZSH
   WHERE XSXJ_ZSH.学号=CJ_ZSH.学号  AND CJ_ZSH.课程名称='计算机网络技术'
   WITH CHECK OPTION
GO

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

IF EXISTS(SELECT NAME FROM SYSINDEXES WHERE NAME='XSXJ_IND' )
   DROP INDEX VIEW1.XSXJ_IND
GO

CREATE NONCLUSTERED INDEX XSXJ_IND ON dbo.VIEW1(学号 ASC)
GO


----------对表 CJ_ZSH 创建非聚集索引----------
USE XSXI_ZSH
GO
IF EXISTS(SELECT NAME FROM SYSINDEXES WHERE NAME='CJ_IND' )
   DROP INDEX CJ_ZSH.CJ_IND

CREATE NONCLUSTERED INDEX CJ_IND ON CJ_ZSH(考试编号)

-----------对表 KC_ZSH 创建唯一聚集索引-----------
USE XSXI_ZSH
GO
IF EXISTS(SELECT NAME FROM SYSINDEXES WHERE NAME='KC_IND' )
   DROP INDEX KC_ZSH.KC_IND
CREATE UNIQUE CLUSTERED INDEX KC_IND ON KC_ZSH(课程编号) 
      /*不允许有两个相同的课程编号出现*/
GO
   
--在数据库XSXI_ZSH 中创建一触发器，当向CJ_ZSH表中插入一纪录时，检查该记录的学号
--是否在XSXJ_ZSH表中存在，检查课程名称是否在KC_ZSH中存在，若有一项为否不允许插入
USE XSXJ_ZSH
GO

IF EXISTS(SELECT name FROM sysobjects
            WHERE name='CJ_INSERT' AND TYPE='TR')
       DROP TRIGGER CJ_INSERT

CREATE TRIGGER CJ_INSERT ON CJ_ZSH
   FOR INSERT AS
   IF EXISTS(SELECT *
      FROM INSERTED CJ_ZSH
      WHERE CJ_ZSH.学号 NOT IN (SELECT XSXJ_ZSH.学号 FROM XSXJ_ZSH) OR
            CJ_ZSH.课程名称 NOT IN (SELECT KC_ZSH.课程名称 FROM KC_ZSH))
   BEGIN 
      RAISERROR ('违背数据的一致性.',16,1)
      ROLLBACK TRANSACTION
   END
GO
      
--------------------------------存储过程-------------------------------------
USE XSXJ_ZSH
GO

IF EXISTS(SELECT name FROM sysobjects
            WHERE name='CJ_INFO' AND TYPE='P')
       DROP PROCEDURE CJ_INFO

CREATE PROCEDURE CJ_INFO
   AS
   SELECT XSXJ_ZSH.学号,XSXJ_ZSH.姓名,KC_ZSH.课程名称,学分,分数,已得学分
   FROM  XSXJ_ZSH JOIN CJ_ZSH JOIN  KC_ZSH 
      ON CJ_ZSH.课程名称=KC_ZSH.课程名称 
      ON XSXJ_ZSH.学号=CJ_ZSH.学号              
GO

----------------============修改学籍信息============-----------------------

-----------添加一列 列名‘所在系部’--------
ALTER TABLE XSXJ_ZSH
   ADD
   所在系部  CHAR(10)  NULL
GO
------------删除列‘所在系部’----------
ALTER TABLE XSXJ_ZSH
   DROP 
   COLUMN 所在系部
GO
----修改表每列的信息----
UPDATE XSXJ_ZSH
     SET 所在系部='建工系',
         姓名='程夕',
         性别='1'
   WHERE 学号='200507023203'

UPDATE XSXJ_ZSH
     SET 所在系部='NULL',
         姓名='张新',
         性别='0'
   WHERE 学号='200507023203'
     

--------------===============查询学籍信息=================---------------
SELECT *
  FROM XSXJ_ZSH
  WHERE 性别='1' AND 家庭住址 LIKE '山东省%'
  ORDER BY 出生日期

---------------===============查询成绩信息=================---------------


---查询某个学生的已修总学分----
SELECT 学号,姓名,SUM(已得学分) AS '已修总学分'
  FROM XSA_CJ
  GROUP BY 姓名,学号
 
-----查询某个学生选的课程名称、课程的学分以及考试的分数----
SELECT 姓名,XSA_CJ.课程名称,学分,分数
  FROM XSA_CJ,KC_ZSH
  WHERE XSA_CJ.课程名称=KC_ZSH.课程名称

--从视图 ENGLISH 中查询分数在70分以上的女生---
SELECT ENGLISH.学号,ENGLISH.姓名,性别,课程名称,分数,已得学分
  FROM ENGLISH,XSXJ_ZSH
  WHERE  ENGLISH.学号=XSXJ_ZSH.学号 AND  分数>=70 AND 性别='0'

------查询某个学生的单科成绩及分数等--------
SELECT 学号,姓名,KC_ZSH.课程名称,学分,分数,已得学分
 FROM CJ_ZSH,KC_ZSH
 WHERE CJ_ZSH.课程名称=KC_ZSH.课程名称 
   --- AND KC_ZSH. 课程名称='法律程序与法理'  
   --- AND KC_ZSH. 课程名称='计算机网络技术'
   --- AND 学号='200507023205'