使用sql语句创建数据库
USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'MySchool')
DROP DATABASE MySchool
GO
CREATE DATABASE MySchool
ON
(
NAME = ' MySchool_data', --主数据文件的逻辑名
FILENAME = 'D:\project\MySchool_data.mdf' , --主数据文件的物理名
SIZE = 10 MB, --主数据文件初始大小
FILEGROWTH = 20 %
)
LOG ON
(
NAME = 'MySchool_log',
FILENAME = 'D:\project\MySchool_log.ldf' ,
SIZE = 3MB,
MAXSIZE = 20MB,
FILEGROWTH = 1MB
)
GO
使用sql语句创建表subject
USE MySchool
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='Subject' )
DROP TABLE Subject
GO
CREATE TABLE Subject --创建课程表
(
SubjectNo int IDENTITY(1,1) NOT NULL,
SubjectName nchar(50) NOT NULL,
ClassHour int NOT NULL,
GradeId int NOT NULL
)
GO
使用sql语句创建表result
USE MySchool
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name=' Result' )
DROP TABLE Result
GO
CREATE TABLE Result --创建成绩表
(
StudentNo int NOT NULL,
SubjectNo int NOT NULL,
StudentResult int NOT NULL,
ExamDate datetime NOT NULL
)
GO
使用sql语句脚本创建student和grade表
USE MySchool
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student
CREATE TABLE [dbo].[Student](
[StudentNo] [int] NOT NULL,
[LoginPwd] [nvarchar](50) NOT NULL,
[StudentName] [nvarchar](50) NOT NULL,
[Sex] bit NOT NULL,
[GradeId] [int] NOT NULL,
[Phone] [nvarchar](50) NULL,
[Address] [nvarchar](255) NULL,
[BornDate] [datetime] NOT NULL,
[Email] [nvarchar](50) NULL,
[IdentityCard] [varchar](18) NOT NULL
)
IF EXISTS(SELECT * FROM sysobjects WHERE name='Grade')
DROP TABLE Grade
CREATE TABLE [dbo].[Grade](
[GradeId] [int] IDENTITY(1,1) NOT NULL,
[GradeName] [nvarchar](50) NOT NULL
)
添加表约束
USE MySchool
GO
ALTER TABLE Subject --主键约束(课程编号)
ADD CONSTRAINT PK_Subject PRIMARY KEY (SubjectNo)
ALTER TABLE Subject --非空约束(课程名称)
ADD CONSTRAINT CK_SubjectName CHECK (SubjectName is not null)
ALTER TABLE Subject WITH NOCHECK --检查约束(学时必须大于等于0)
ADD CONSTRAINT CK_ClassHour CHECK (ClassHour>=0)
ALTER TABLE Subject --外键约束(主表Grade和从表Subject建立引用关系)
ADD CONSTRAINT FK_GradeId
FOREIGN KEY (GradeId) REFERENCES Grade (GradeId)
GO
USE MySchool
GO
ALTER TABLE Result --主键约束(学号、科目号、日期)
ADD CONSTRAINT PK_Result PRIMARY KEY
(StudentNo, SubjectNo, ExamDate)
ALTER TABLE Result --默认约束(日期为系统当前日期)
ADD CONSTRAINT CK_ExamDate DEFAULT (getdate()) FOR ExamDate
ALTER TABLE Result --检查约束(分数不能大于100,小于0)
ADD CONSTRAINT CK_StudentResult CHECK
(StudentResult BETWEEN 0 AND 100)
ALTER TABLE Result --外键约束(主表Student和从表Result建立关系)
ADD CONSTRAINT FK_StudentNo
FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo)
ALTER TABLE Result --外键约束(主表Subject和从表Result建立关系)
ADD CONSTRAINT FK_SubjectNo
FOREIGN KEY (SubjectNo) REFERENCES Subject (SubjectNo)
GO
USE MySchool
GO
ALTER TABLE Result -- 删除检查约束
DROP CONSTRAINT ck_studentResult
ALTER TABLE Result -- 修改成绩表中成绩列的数据类型
ALTER COLUMN StudentResult DECIMAL (5, 2)
ALTER TABLE Result -- 添加检查约束
ADD Constraint ck_studentResult CHECK
(studentresult BETWEEN 0 AND 100)
GO