SQL Server 数据库

USE master -- 设置当前数据库为master，以解除对Students的占用
GO
IF  EXISTS(SELECT * FROM sys.databases WHERE name='Students')
DROP DATABASE Students

--创建一个Students数据库
CREATE DATABASE Students
on primary
(
	 -- 数据文件的具体描述
	 NAME='Students_data',
	 FILENAME='D:\Students_data.mdf',
	 SIZE=10MB,
	 MAXSIZE=100MB,
	 FILEGROWTH = 10%
)
LOG ON
(
	 --日志文件的具体描述，各参数含义同上
	 NAME='Students_log',
	 FILENAME='D:\Students_log.ldf',
	 SIZE=10MB
)
GO

GO
IF EXISTS(SELECT * FROM sys.sysobjects WHERE xtype = 'U' AND name='StuInfo')
DROP TABLE StuInfo 

USE Students -- 设置当前数据库为Students
CREATE TABLE StuInfo            
(
	-- 创建学生信息表
	StuID int NOT NULL PRIMARY KEY,        -- 学生学号，主键
	StuName varchar(10) NOT NULL,  -- 学生姓名，非空
	StuSex char(2) NOT NULL DEFAULT('男'),        -- 学生性别，非空	
	CHECK (StuSex ='男' or StuSex='女' ) 
)
GO

GO
IF EXISTS(SELECT * FROM sys.sysobjects WHERE xtype = 'U' AND name='StuMarks')
DROP TABLE StuMarks

USE Students -- 设置当前数据库为Students
CREATE TABLE StuMarks
(
	-- 创建学生成绩表
	ExamNo int identity(1,1) primary key, -- 考试编号，标识
	StuID int NOT NULL references StuInfo(StuID),  -- 考生学号，外键
	Course varchar(10) not null, -- 考试科目
	Score int default(0),  -- 考试成绩
)
GO

GO
USE Students -- 设置当前数据库为Students
INSERT INTO StuInfo (StuID,StuName,StuSex)
SELECT  1,'张三','男' UNION
SELECT  2,'李四','男' UNION
SELECT  3,'钱七','女' UNION
SELECT  4,'王五','女' UNION
SELECT  5,'赵六','男' 
GO

GO
USE Students -- 设置当前数据库为Students
INSERT INTO StuMarks (StuID,Course,Score)
SELECT  1,'语文',70 UNION
SELECT  1,'数学',89 UNION
SELECT  2,'语文',33 UNION
SELECT  2,'数学',50 UNION
SELECT  3,'语文',79 UNION
SELECT  3,'数学',90 UNION
SELECT  4,'语文',88 UNION
SELECT  4,'数学',74 UNION
SELECT  5,'语文',64 UNION
SELECT  5,'数学',92 
GO

GO
SELECT * FROM StuInfo;