class1.sql

一个学籍管理系统 以在某高校投入使用了

if EXISTS (select * from sysdatabases where name = 'Class')
drop database Class
go 
create database Class
go
use Class
create table ClassInfo
(
	ClassID int primary key identity(1,1),
	ClassName varchar(20) not null,
	SpecialtyName varchar(20) not null,
	ClassNumber varchar(20)not null,
	Length int not null,
	Teacher varchar(20) not null,
	BeginTime DateTime not null,
	Memo varchar(40)
)
create table CourseInfo
(
	CourseID int primary key identity(1,1),
	ClassID int not null,
	Course varchar(20) not null,
	Semester varchar(20) not null
)
create table ScoreInfo
(
	ScoreID int primary key identity(1,1),
	Semester varchar(20) not null,
	StudentNumber varchar(20) not null,
	Course varchar(20) not null,
	Score float not null
)
create table SpecialtyInfo
(
	SpecialtyId int identity(1,1),
	SpecialtyName varchar(20) primary key, 
	Memo varchar(40)
)
create table StudentInfo
(
	StudentID int primary key identity(1,1),
	StudentNumber varchar(20) not null,
	_Name varchar(20) not null,
	Sex varchar(2) not null,
	Rece varchar(20) not null,
	Address varchar(30) not null,
	Telephone varchar(20) not null,
	Role varchar(20) not null,
	PostCode varchar(10) not null,
	ClassID int not null,
	Memo varchar(40)
)
create table UserInfo
(
	UserID varchar(20) primary key,
	UserPwd varchar(20) not null,
	UserPur varchar(10) not null
)
drop table UserInfo
insert into UserInfo values('admin','0','超级用户')
select * from UserInfo
select * from ClassInfo
drop table ClassInfo
select * from CourseInfo
insert into Class..ClassInfo values('11','管理学院','001',4,'啊啊','2007-4-20','')
select * from Class..ClassInfo where SpecialtyName = '管理学院' and year(BeginTime)='2007'
select * from Class..StudentInfo where ClassID =(select ClassID from ClassInfo where ClassName = '2007管理1班')
select * from Class..StudentInfo where ClassID =(select ClassID from ClassInfo where ClassName = '2007历史1班')

select * from ClassInfo where ClassName = '2007管理1班'
select * from SpecialtyInfo