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

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

-----------------------------如果存在库 XSXI_ZSH，删除后再创建-----------
IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = 'XSXI_ZSH')
	DROP DATABASE XSXI_ZSH
GO

------------------------------创建 学生信息数据库 XSXI_ZSH----------------------
CREATE DATABASE XSXI_ZSH
 ON
 (NAME='XSXI_ZSH_Data',
  FILENAME='C:\Program files\Microsoft SQL Server\MSSQL\data\XSXI_ZSH.mdf',
  SIZE=5MB,
  MAXSIZE=30MB,
  FILEGROWTH=10%)
LOG ON
 (NAME='XSXI_ZSH_Log',
  FILENAME='C:\Program files\Microsoft SQL Server\MSSQL\data\XSXI_ZSH.ldf',
  SIZE=2MB,
  MAXSIZE=5MB,
  FILEGROWTH=1MB)
GO
-----------------------------创建 用户表 USER_ZSH ----------------
USE XSXI_ZSH
CREATE TABLE USER_ZSH
(用户名 CHAR(12) NOT NULL CONSTRAINT USER_PK PRIMARY KEY(用户名),/*定义主键约束*/
 密码 CHAR(12) NOT NULL,
 用户类型 CHAR(10) NOT NULL  
)
GO

--------------------------向表 USER_ZSH 中添加用户信息---------------
USE XSXI_ZSH                
INSERT INTO USER_ZSH
   VALUES('12345','12345','系统管理员')
INSERT INTO USER_ZSH
   VALUES('200507023201','200507023201','学生')
INSERT INTO USER_ZSH
   VALUES('200507023202','200507023202','学生')
INSERT INTO USER_ZSH
   VALUES('200507023203','200507023203','学生')
INSERT INTO USER_ZSH
   VALUES('200507023204','200507023204','学生')
INSERT INTO USER_ZSH
   VALUES('200507023205','200507023205','学生')
INSERT INTO USER_ZSH
   VALUES('200507023206','200507023206','学生')
INSERT INTO USER_ZSH
   VALUES('200507023207','200507023207','学生')
INSERT INTO USER_ZSH
   VALUES('200507023208','200507023208','学生')
INSERT INTO USER_ZSH
   VALUES('200507023209','200507023209','学生')

---------定义存储过程 CHANGE_PASS 实现用户密码修改-----------
USE XSXI_ZSH
GO
IF EXISTS(SELECT name FROM sysobjects
          WHERE name='CHANGE_PASS'AND TYPE='P')
   DROP PROCEDURE CHANGE_PASS
GO

CREATE PROCEDURE CHANGE_PASS
@用户名 CHAR(12),@旧密码 CHAR(12),@新密码 CHAR(12)
AS
UPDATE USER_ZSH
   SET USER_ZSH.密码=@新密码
   WHERE USER_ZSH.用户名=@用户名 AND USER_ZSH.密码=@旧密码
GO

---分别创建存储过程 USER_INSERT USER_DELETE ,用于向 USER_ZSH 表中插入、删除记录，
---对于增加的普通用户通过触发器 INSERT_TRIGGER 保证学号作为用户名

---------------USER_INSERT 存储过程的定义-----------
USE XSXI_ZSH
GO
IF EXISTS(SELECT name FROM sysobjects
          WHERE name='USER_INSERT'AND TYPE='P')
   DROP PROCEDURE USER_INSERT
GO

CREATE PROCEDURE USER_INSERT
   @用户名1 CHAR(12),@用户名2 CHAR(12),@密码 CHAR(12),@用户类型 CHAR(10)
   AS
   IF EXISTS(SELECT * FROM USER_ZSH
          WHERE 用户名=@用户名1 AND 用户类型=2)
   INSERT INTO USER_ZSH(用户名,密码,用户类型)  
          VALUES(@用户名2,@密码,@用户类型)

--------------USER_DELETE 存储过程的定义----------
USE XSXI_ZSH
GO
IF EXISTS(SELECT name FROM sysobjects
          WHERE name='USER_DELETE'AND TYPE='P')
   DROP PROCEDURE USER_DELETE
GO

CREATE PROCEDURE USER_DELETE
   @用户名1 CHAR(12),@用户名2 CHAR(12)
   AS
   IF EXISTS(SELECT * FROM USER_ZSH
          WHERE 用户名=@用户名1 AND 用户类型=2)
   DELETE 
      FROM USER_ZSH
      WHERE 用户名=@用户名2

--------------- INSERT_TRIGGER 触发器的定义------------
USE XSXI_ZSH
GO
IF EXISTS(SELECT name FROM sysobjects
          WHERE name='INSERT_TRIGGER'AND TYPE='TR')
   DROP TRIGGER INSERT_TRIGGER
GO
CREATE TRIGGER INSERT_TRIGGER ON USER_ZSH
   FOR INSERT AS
  IF EXISTS(SELECT * 
     FROM INERTED
     WHERE 用户名 NOT IN (SELECT 学号 FROM XSXJ_ZSH) AND 用户类型=2)
     BEGIN 
         RAISERROR('违背数据一致性.',16,1)
         ROLLBACK TRANSACTION
     END
  

--------******************创建 学生学籍表 XSXJ_ZSH****************---------------
               
---以下定义了一个用户数据类型telphonenum,及规则tel_rule,然后将规则tel_rule绑定到用户数
---据类型telphonenum上，最后又定义了表XSXJ_ZSH，其电话号码字段的数据类型为telphonenum。
USE XSXI_ZSH
  EXEC SP_ADDTYPE TELPHONEUNM, 'CHAR(11)','NOT NULL' 
  GO
 /*以下语句为解除规则对象tel_rule与用户定义类型telphonenum的绑定关系，并删除规则对象tel_rule*/
 IF EXISTS (SELECT NAME FROM SYSOBJECTS
             WHERE NAME='TEI_RULE' AND TYPE='R')  
     BEGIN
       EXEC SP_UNBINDRULE 'telphonenum'      
       DROP RULE TEL_RULE
     END
GO

CREATE RULE TEL_RULE
  AS @联系电话 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
GO
  EXEC SP_BINDRULE 'TEL_RULE','TELPHONE'
GO

CREATE TABLE XSXJ_ZSH
(学号 char(12) NOT NULL CONSTRAINT XSXJ_PK PRIMARY KEY,
 姓名 CHAR(8)  NULL,
 性别 BIT NULL DEFAULT(0),
 出生日期 CHAR(12) NULL,
 班级 CHAR(10) NOT NULL,
 入校时间 CHAR(10) NOT NULL,
 家庭住址 CHAR(20) NOT NULL,
 联系电话 TELPHONEUNM NOT NULL CONSTRAINT DEF_VAL DEFAULT 0 
/*定义联系电话的默认值约束为0，在定义默认值的同时指定约束名*/
)
GO
----------------删除表 XSXJ_ZSH 的语句----------------
DROP TABLE XSXJ_ZSH

------------------创建 课程表 KC_ZSH---------------------------
CREATE TABLE KC_ZSH
(课程编号 CHAR(10) NOT NULL CONSTRAINT KC_PK PRIMARY KEY,
 课程名称 CHAR(20) NULL CONSTRAINT KC_UK UNIQUE,
 课程描述 CHAR(500) NULL,
 课程类型 CHAR(4) NOT NULL,
 学分 INT NOT NULL
)
GO

------------------删除表 KC_ZSH 的语句----------------
DROP TABLE KC_ZSH

-----------------创建 成绩表 CJ_ZSH---------------------------

CREATE TABLE CJ_ZSH
(考试编号 CHAR(12) NOT NULL,
 学号 CHAR(12) NOT NULL,
 姓名 CHAR(8)  NULL,
 课程名称 CHAR(30) NULL,
 分数 INT CHECK(分数>=0 AND 分数<=100) NULL,
 已得学分 INT NOT NULL
)
GO

----------------删除表 CJ_ZSH 的语句----------------
DROP TABLE CJ_ZSH


------------------------向表 XSXJ_ZSH 中添加学籍信息--------------------------
USE XSXI_ZSH                
INSERT INTO XSXJ_ZSH
   VALUES('200507023201','范思哲','1','1986-12-30','05-WLZ-2','2005-09','北京市通州区','13011941234')
INSERT INTO XSXJ_ZSH
   VALUES('200507023202','孟大波','1','1986-10-17','05-WLZ-2','2005-09','内蒙古海拉尔','13463161234')
INSERT INTO XSXJ_ZSH
   VALUES('200507023203','张新','0','1986-12-23','05-WLZ-2','2005-09','山东省潍坊市','13785611234')
INSERT INTO XSXJ_ZSH
   VALUES('200507023204','周方圆','1','1985-02-02','05-WLZ-2','2005-09','山东省临沂市','13784111234')
INSERT INTO XSXJ_ZSH
   VALUES('200507023205','禇楚楚','0','1984-06-02','05-WLZ-2','2005-09','山西省原平市','13903161234')
INSERT INTO XSXJ_ZSH
   VALUES('200507023206','刘晓冰','0','1987-04-08','05-WLZ-2','2005-09','黑龙江绥化市','13463161234')
INSERT INTO XSXJ_ZSH
   VALUES('200507023207','刘大东','1','1985-10-10','05-WLZ-2','2005-09','山东省德州市','13463161234')
INSERT INTO XSXJ_ZSH
   VALUES('200507023208','岳小兰','0','1985-01-15','05-WLZ-2','2005-09','河北省石家庄','13473681234')
INSERT INTO XSXJ_ZSH
   VALUES('200507023209','马小璞','0','1986-06-26','05-WLZ-2','2005-09','河北省保定市','13363611234')

------------------------向表 KC_ZSH 中添加课程信息-------------------------
USE XSXI_ZSH   
INSERT INTO KC_ZSH
   VALUES('0001','计算机网络技术','讲了数据通信的基础知识、网络体系结构与协议、局域网技术和网络互连技术等','必修','2')
INSERT INTO KC_ZSH
   VALUES('0002','实用业务英语','主要包括三部分：实用阅读、应用写作、实际会话','必修','3')
INSERT INTO KC_ZSH
   VALUES('1111','网页制作','本课程主要介绍了用三剑客来制作网页','选修','2')
INSERT INTO KC_ZSH
   VALUES('1112','Photoshop ','本课程将教我们处理图片、制作海报等','选修','2')
INSERT INTO KC_ZSH
   VALUES('1113','法律程序与法理','本课程让同学们了解最基本的法律知识','选修','1')
INSERT INTO KC_ZSH
   VALUES('1114','大学生礼仪','做大学生要懂的的基本礼仪','选修','1')

---------------------------向表 CJ_ZSH 中添加成绩信息------------------------------
USE XSXI_ZSH   
INSERT INTO CJ_ZSH
   VALUES('050201','200507023201','范思哲','计算机网络技术','70','2')
INSERT INTO CJ_ZSH
   VALUES('050201','200507023201','范思哲','实用业务英语','56','0')
INSERT INTO CJ_ZSH
   VALUES('050201','200507023201','范思哲','Photoshop','76','2')
INSERT INTO CJ_ZSH
   VALUES('050201','200507023201','范思哲','法律程序与法理','65','1')
INSERT INTO CJ_ZSH
   VALUES('050202','200507023202','孟大波','计算机网络技术','79','2')
INSERT INTO CJ_ZSH
   VALUES('050202','200507023202','孟大波','实用业务英语','60','3')
INSERT INTO CJ_ZSH
   VALUES('050202','200507023202','孟大波','网页制作','90','2')
INSERT INTO CJ_ZSH
   VALUES('050202','200507023202','孟大波','法律程序与法理','78','1')
INSERT INTO CJ_ZSH
   VALUES('050203','200507023203','张新','计算机网络技术','75','2')
INSERT INTO CJ_ZSH
   VALUES('050203','200507023203','张新','实用业务英语','78','3')