createdb.sql

适用一般于毕业设计！ VB代码源加SQL 数据库 ··

/* 创建数据库 */
CREATE DATABASE ClassManage
ON (
NAME=' ClassManage', FILENAME='E:\江莉\教学相关\教案\各学期教案\09上\实例+项目\班级信息管理系统\database\ClassManage.mdf')
LOG ON (
NAME=' ClassManageLog', FILENAME='E:\江莉\教学相关\教案\各学期教案\09上\实例+项目\班级信息管理系统\database\ClassManage.ldf')
GO

/* 设置当前数据库 */
USE ClassManage
GO

/* 创建班级信息表 */
CREATE TABLE Classes (
ClassId varchar (20) PRIMARY KEY,
ClassName varchar (50),
UpperId varchar (20)
)
GO

/* 创建学生信息表 */
CREATE TABLE Students (
	StuId varchar (20) NOT NULL PRIMARY KEY,
	StuNo varchar (20) NOT NULL,
	StuName varchar (50) NOT NULL,
	Photo image NULL,
	Sex char (2) NULL,
	Nationality varchar (20) NULL,
	Birth datetime NULL ,
	Political_Party varchar (20) NULL,
	Family_Place varchar (30)  NULL,
	Id_Card varchar (30) NULL,
	DormRoom varchar (30) NULL,
	DormRoom_phone varchar (30) NULL,
	Mobile varchar (30) NULL,
	Address varchar (100) NULL,
	PostCard varchar (10) NULL,
	Family_Phone varchar (30) NULL,
	Memo varchar (200) NULL,
	Duty varchar (30) NULL,
	ClassId varchar (20) NOT NULL
)

/* 创建学生家庭成员记录表 */
CREATE TABLE Family (
	[Id] varchar (20) NOT NULL PRIMARY KEY,
	[Name] varchar (50) NOT NULL,
	Sex char (2) NULL,
	Relationship varchar (20) NULL,
	WorkingOrg varchar (50) NULL,
	Phone varchar (30) NULL,
	Memo varchar (200) NULL,
	StuId varchar (20) NOT NULL
)

/* 创建学生奖励记录表 */
CREATE TABLE Prize (
	[ID] varchar (20) NOT NULL PRIMARY KEY,
	StuID varchar (20) NOT NULL,
	PDate datetime NULL,
	PReason varchar (200) NULL,
	PDetail varchar (200) NULL
)

/* 创建学生惩罚记录表 */
CREATE TABLE Punish (
	[ID] varchar (20) NOT NULL PRIMARY KEY,
	StuID varchar (20) NOT NULL,
	PDate datetime NULL,
	PReason varchar (200) NULL,
	PDetail varchar (200) NULL
)

/* 创建学生考工考级记录表 */
CREATE TABLE Grade (
	[ID] varchar (20) NOT NULL PRIMARY KEY,
	StuID varchar (20) NOT NULL,
	GDate datetime NULL,
	GName varchar (50) NULL,
	GMemo varchar (200) NULL
)

/* 创建学生测评记录表 */
CREATE TABLE Score (
	[ID] varchar (20) NOT NULL PRIMARY KEY,
	StuID varchar (20) NOT NULL,
	SYear1 char (4) NOT NULL,
	SYear2 char (4) NOT NULL,
	STerm char (2)NOT NULL,
	SDate datetime NULL,
	SMoral numeric(9, 4) NULL,
	SMoralPer numeric(9, 4) NULL,
	SMind numeric(9, 4) NULL,
	SMindPer numeric(9, 4) NULL,
	SGmy numeric(9, 4) NULL,
	SGmyPer numeric(9, 4) NULL,
	STotal numeric(9, 4) NULL,
	SOrder smallint NULL,
	SMemo varchar (200) NULL
)

/* 创建班级档案记录表 */
CREATE TABLE Archives (
	[ID] varchar (20) NOT NULL PRIMARY KEY,
	ArcNo varchar (30) NOT NULL,
	ArcName varchar (50) NOT NULL,
	Doc image NULL,
	DocName varchar (50) NULL,
	Keyword varchar (100) NULL,
	ArcDate datetime NULL,
	Content varchar (500) NULL,
	Memo varchar (200) NULL,
	ClassID varchar (20) NOT NULL
)

/* 创建用户信息表 */
CREATE TABLE Users (
	UserNo varchar (20) NOT NULL PRIMARY KEY,
	UserName varchar (40) NOT NULL,
	UserPwd varchar (40) NULL
)

/* 向用户信息表中插入一条默认记录 */
INSERT INTO Users ( UserNo, UserName, UserPwd )
VALUES ( 'Admin', '系统管理员', '000000' )