创建学生信息数据库

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name=N'studentcourse')
DROP DATABASE studentcourse
GO
CREATE DATABASE studentcourse
ON PRIMARY
( NAME =N'studentcourse',
FILENAME=N'E:\DATA\studentcourse.mdf',
SIZE = 3072KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB ),
FILEGROUP [group1]
( NAME = N'secondsc',
FILENAME = N'E:\DATA\secondsc.ndf' ,
SIZE = 3072KB ,
MAXSIZE =51200KB,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'studentcourse_log',
FILENAME=N'E:\DATA\studentcourse_log.ldf' ,
SIZE = 1024KB ,
MAXSIZE = 20480KB ,
FILEGROWTH = 10%)
GO


USE studentcourse
GO
CREATE TABLE S (
学号 Char (6) NOT NULL DEFAULT ('J0401'),
姓名 Char (8) NOT NULL,
性别 Char (2) NOT NULL,
出生日期 Datetime NOT NULL DEFAULT ('19800101'),
系 Varchar (20) NOT NULL,
电话 Char (8) NULL,
CONSTRAINT CK_SNO CHECK (学号 Like '[A-Z][0-9][0-9][0-9][0-9]'),

CONSTRAINT CK_SEX CHECK (性别 ='女' OR 性别 ='男'),

CONSTRAINT CK_TEL CHECK (电话 Like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

CONSTRAINT PK_S PRIMARY KEY CLUSTERED ( 学号 ASC)

) ON [PRIMARY]
GO
insert into S(学号,姓名,性别,出生日期,系,电话)
values('J0401 ','李丽','女','1980-02-12 00:00:00.000','管理信息系','931-1234')
insert into S(学号,姓名,性别,出生日期,系,电话)
values('J0402 ','马俊萍','女','1970-12-02 00:00:00.000','管理信息系','931-1288')
insert into S(学号,姓名,性别,出生日期,系,电话)
values('J0403 ','王永明','男','1985-12-01 00:00:00.000','管理信息系','571-2233')
insert into S(学号,姓名,性别,出生日期,系,电话)
values('J0404 ','姚江','男','1985-08-09 00:00:00.000','管理信息系','571-8848')
insert into S(学号,姓名,性别,出生日期,系,电话)
values('Q0401 ','陈小红','女','1980-02-12 00:00:00.000','汽车系','571-1122')
insert into S(学号,姓名,性别,出生日期,系,电话)
values('Q0403 ','张干劲','男','1978-01-05 00:00:00.000','汽车系','571-1111')
GO




USE studentcourse
GO
CREATE TABLE C (
课程号 Char (3) NOT NULL,
课程名 Varchar (20) NOT NULL,
学分 Smallint NULL,
预选课程号 Char (3) NULL,
教师 Char (8) NULL,
CONSTRAINT FK_Pcno FOREIGN KEY( 预选课程号 ) REFERENCES C (课程号),

CONSTRAINT CK_Cno CHECK ( 课程号 Like 'C[0-9][0-9]'),

CONSTRAINT PK_C PRIMARY KEY CLUSTERED (课程号 ASC )

) ON [PRIMARY]
GO
insert into C(课程号,课程名,学分,预选课程号,教师)
values('C04','计算机应用基础',2,NULL,'李学成')
insert into C(课程号,课程名,学分

,预选课程号,教师)
values('C01','数据库',3,'C04','陈弄清')
insert into C(课程号,课程名,学分,预选课程号,教师)
values('C02','C语言',4,'C04','应刻苦')
insert into C(课程号,课程名,学分,预选课程号,教师)
values('C03','数据结构',3,'C02','管功臣')
insert into C(课程号,课程名,学分,预选课程号,教师)
values('C05','网络技术',NULL,'C04','马努力')
GO



USE studentcourse
GO
CREATE TABLE SC (
学号 Char (6) NOT NULL,
课程号 Char (3) NOT NULL,
成绩 Smallint NULL,
FOREIGN KEY(课程号) REFERENCES C (课程号),

FOREIGN KEY( 学号 ) REFERENCES S (学号),

CONSTRAINT CK_CJ CHECK (成绩>=(0) AND 成绩<=(100) OR 成绩 IS NULL),

CONSTRAINT PK_SC PRIMARY KEY CLUSTERED (学号 ASC ,课程号 ASC)
)
GO
insert into sc(学号,课程号,成绩)
values('J0401 ','C01',88)
insert into sc(学号,课程号,成绩)
values('J0401 ','C02',93)
insert into sc(学号,课程号,成绩)
values('J0401 ','C03',99)
insert into sc(学号,课程号,成绩)
values('J0401 ','C04',89)
insert into sc(学号,课程号,成绩)
values('J0401 ','C05',86)
insert into sc(学号,课程号,成绩)
values('J0402 ','C01',90)
insert into sc(学号,课程号,成绩)
values('J0402 ','C02',85)
insert into sc(学号,课程号,成绩)
values('J0402 ','C03',77)
insert into sc(学号,课程号,成绩)
values('J0402 ','C05',70)
insert into sc(学号,课程号,成绩)
values('J0403 ','C01',76)
insert into sc(学号,课程号,成绩)
values('J0403 ','C02',67)
insert into sc(学号,课程号,成绩)
values('J0403 ','C03',58)
insert into sc(学号,课程号,成绩)
values('J0403 ','C04',55)
insert into sc(学号,课程号,成绩)
values('J0403 ','C05',82)
insert into sc(学号,课程号,成绩)
values('Q0401 ','C01',90)
insert into sc(学号,课程号,成绩)
values('Q0401 ','C05',92)
insert into sc(学号,课程号,成绩)
values('Q0403 ','C01',77)
insert into sc(学号,课程号,成绩)
values('Q0403 ','C05',65)
GO


SELECT * FROM s
GO
SELECT * FROM c
GO
SELECT * FROM sc
GO