/*
用Transact-SQL代码实现创建数据库、数据库表、以及设置数据库表的约束
1、下面是一个学校要实现对学生、教师、课程以及学生选课、学生课程成绩、教师上课、教师上课报酬等信息进行统一管理,并开发一个教学信息系统
*/
用Transact-SQL代码实现创建数据库、数据库表、以及设置数据库表的约束
1、下面是一个学校要实现对学生、教师、课程以及学生选课、学生课程成绩、教师上课、教师上课报酬等信息进行统一管理,并开发一个教学信息系统
*/
CREATE
DATABASE TeachingManageSYS
ON PRIMARY
( NAME = TeachingManageSys,
FILENAME = 'E:\TeachingManageSYS\TeachingManageSYS.mdf',
SIZE = 3072KB,MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = 'TeachingManageSYS_log',
FILENAME = 'E:\TeachingManageSYS\TeachingManageSYS_log.ldf',
SIZE = 1024KB,MAXSIZE = 2048GB, FILEGROWTH = 10% )
GO
USE TeachingManageSYS
GO
CREATE TABLE CourseInfo /*课程信息表*/
(
Cno char(4) PRIMARY KEY NOT NULL,
Cname char(10) NOT NULL,
Credit real NOT NULL,
CouresHour int NOT NULL
)
GO
CREATE TABLE RankPayMent /*职别课酬*/
(
Trank char(6) PRIMARY KEY NOT NULL,
Payment real NOT NULL
)
GO
CREATE TABLE StuAchievement /*成绩表*/
(
Sno char(6) PRIMARY KEY NOT NULL,
Sfundamental real NULL,
Sminor real NULL
)
GO
CREATE TABLE StudentCourse /*学生选课表*/
(
Sno char(6) NOT NULL,
Cno char(4) NOT NULL,
Score real NULL
)
GO
CREATE TABLE StudentInfo /*学生信息表*/
(
Sno char(6) PRIMARY KEY NOT NULL,
Sname char(10) NOT NULL,
Ssex char(2) NOT NULL,
Sage int NOT NULL,
Saddress varchar(30) NULL,
Sphoto p_w_picpath NULL,
Smemory varbinary(100) NULL
)
GO
CREATE TABLE Teach /*教学表*/
(
Tno char(6) PRIMARY KEY NOT NULL,
Cno char(4) NOT NULL,
Ssatisfact real NULL
)
GO
CREATE TABLE TeacherInfo /*教师信息表*/
(
Tno char(6) PRIMARY KEY NOT NULL,
Tname char(10) NOT NULL,
Tsex char(2) NOT NULL,
Tage int NOT NULL,
Trank char(6) NOT NULL,
Taddress varchar(30) NOT NULL,
Tphoto p_w_picpath NULL,
Tmemory varchar(100) NULL
)
GO
/**
设置约束
1、需要对TeacherInfo(教师信息表)的Tname字段进行约束,
即根据学校实际情况规定教师姓名不能重复
*/
CREATE UNIQUE NONCLUSTERED INDEX UN_TeacherInfo
ON dbo.TeachInfo
(
Tname
)
GO
/**设置外键*/
--USE TeachingManageSYS
--GO
/* StudentCourse(外键 Sno)--StudentInfo(主键 Sno);*/
ALTER TABLE StudentCourse WITH CHECK ADD
CONSTRAINT FK_StudentCourse_StudentInfo
FOREIGN KEY (Sno)
REFERENCES StudentInfo (Sno)
GO
/* StudentCourse(外键 Cno)--CourseInfo(主键 Cno).*/
ALTER TABLE StudentCourse WITH CHECK ADD
CONSTRAINT FK_StudentCourse_CourseInfo
FOREIGN KEY (Cno)
REFERENCES CourseInfo (Sno)
GO
/*TeacherInfo(外键 Trank)--RankPayment(主键 Trank)*/
--USE TeachingManageSYS
--GO
ALTER TABLE TeacherInfo WITH CHECK ADD
CONSTRAINT FK_TeacherInfo_RankPayMent
FOREIGN KEY (Trank)
REFERENCES RankPayment (Trank)
GO
/*设计CHECK值
规定StudentInfo表的Sage(年龄)必须在18岁到20岁之间。
*/
ALTER TABLE StudentInfo
ADD CONSTRAINT Sage_check
CHECK (Sage>=18 AND Sage<=20)
GO
/*设计默认值
教师大部分家庭住址都是在“四川成都”,
可以在TeacherInfo(教师信息表)的Taddress字段处定义默认值为“四川成都”
*/
ALTER TABLE dbo.TeacherInfo
ADD CONSTRAINT DF_TeacherInfo_Taddress
DEFAULT '四川成都' FOR Taddress
GO
/*输入数据
以StudentInfo表为例,采用INSERT命令输入三条记录数据,由于记录手机比较多,
我们不在列出全部的命令。
*/
INSERT INTO StudentInfo
(Sno,Sname,Ssex,Sage,Saddress)
VALUES
('010101','蔡依林','女','25','台湾台北')
INSERT INTO StudentInfo
(Sno,Sname,Ssex,Sage,Saddress)
VALUES
('010102','刘欢','男','45','北京海淀')
INSERT INTO StudentInfo
VALUES
('010103','马化腾','男','39','深圳南山')
GO
ON PRIMARY
( NAME = TeachingManageSys,
FILENAME = 'E:\TeachingManageSYS\TeachingManageSYS.mdf',
SIZE = 3072KB,MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = 'TeachingManageSYS_log',
FILENAME = 'E:\TeachingManageSYS\TeachingManageSYS_log.ldf',
SIZE = 1024KB,MAXSIZE = 2048GB, FILEGROWTH = 10% )
GO
USE TeachingManageSYS
GO
CREATE TABLE CourseInfo /*课程信息表*/
(
Cno char(4) PRIMARY KEY NOT NULL,
Cname char(10) NOT NULL,
Credit real NOT NULL,
CouresHour int NOT NULL
)
GO
CREATE TABLE RankPayMent /*职别课酬*/
(
Trank char(6) PRIMARY KEY NOT NULL,
Payment real NOT NULL
)
GO
CREATE TABLE StuAchievement /*成绩表*/
(
Sno char(6) PRIMARY KEY NOT NULL,
Sfundamental real NULL,
Sminor real NULL
)
GO
CREATE TABLE StudentCourse /*学生选课表*/
(
Sno char(6) NOT NULL,
Cno char(4) NOT NULL,
Score real NULL
)
GO
CREATE TABLE StudentInfo /*学生信息表*/
(
Sno char(6) PRIMARY KEY NOT NULL,
Sname char(10) NOT NULL,
Ssex char(2) NOT NULL,
Sage int NOT NULL,
Saddress varchar(30) NULL,
Sphoto p_w_picpath NULL,
Smemory varbinary(100) NULL
)
GO
CREATE TABLE Teach /*教学表*/
(
Tno char(6) PRIMARY KEY NOT NULL,
Cno char(4) NOT NULL,
Ssatisfact real NULL
)
GO
CREATE TABLE TeacherInfo /*教师信息表*/
(
Tno char(6) PRIMARY KEY NOT NULL,
Tname char(10) NOT NULL,
Tsex char(2) NOT NULL,
Tage int NOT NULL,
Trank char(6) NOT NULL,
Taddress varchar(30) NOT NULL,
Tphoto p_w_picpath NULL,
Tmemory varchar(100) NULL
)
GO
/**
设置约束
1、需要对TeacherInfo(教师信息表)的Tname字段进行约束,
即根据学校实际情况规定教师姓名不能重复
*/
CREATE UNIQUE NONCLUSTERED INDEX UN_TeacherInfo
ON dbo.TeachInfo
(
Tname
)
GO
/**设置外键*/
--USE TeachingManageSYS
--GO
/* StudentCourse(外键 Sno)--StudentInfo(主键 Sno);*/
ALTER TABLE StudentCourse WITH CHECK ADD
CONSTRAINT FK_StudentCourse_StudentInfo
FOREIGN KEY (Sno)
REFERENCES StudentInfo (Sno)
GO
/* StudentCourse(外键 Cno)--CourseInfo(主键 Cno).*/
ALTER TABLE StudentCourse WITH CHECK ADD
CONSTRAINT FK_StudentCourse_CourseInfo
FOREIGN KEY (Cno)
REFERENCES CourseInfo (Sno)
GO
/*TeacherInfo(外键 Trank)--RankPayment(主键 Trank)*/
--USE TeachingManageSYS
--GO
ALTER TABLE TeacherInfo WITH CHECK ADD
CONSTRAINT FK_TeacherInfo_RankPayMent
FOREIGN KEY (Trank)
REFERENCES RankPayment (Trank)
GO
/*设计CHECK值
规定StudentInfo表的Sage(年龄)必须在18岁到20岁之间。
*/
ALTER TABLE StudentInfo
ADD CONSTRAINT Sage_check
CHECK (Sage>=18 AND Sage<=20)
GO
/*设计默认值
教师大部分家庭住址都是在“四川成都”,
可以在TeacherInfo(教师信息表)的Taddress字段处定义默认值为“四川成都”
*/
ALTER TABLE dbo.TeacherInfo
ADD CONSTRAINT DF_TeacherInfo_Taddress
DEFAULT '四川成都' FOR Taddress
GO
/*输入数据
以StudentInfo表为例,采用INSERT命令输入三条记录数据,由于记录手机比较多,
我们不在列出全部的命令。
*/
INSERT INTO StudentInfo
(Sno,Sname,Ssex,Sage,Saddress)
VALUES
('010101','蔡依林','女','25','台湾台北')
INSERT INTO StudentInfo
(Sno,Sname,Ssex,Sage,Saddress)
VALUES
('010102','刘欢','男','45','北京海淀')
INSERT INTO StudentInfo
VALUES
('010103','马化腾','男','39','深圳南山')
GO
转载于:https://blog.51cto.com/todaycgs/1166771