在我们的学习过程中经常用到的几个表格:Students表,Classes表,Scores表,Subjects表
Students表
表的结构及约束:
- 学生编号StudentNo(PK not null, )
- 学生姓名StudentName(nchar(10) ,)
- 登录密码LoginPwd(nchar(10) not null,)
- 年龄Age(int 0~100,)
- 性别Sex(bit,)
- 班级编号CLassId(FK int ,)
- 电话Phone(int(10),)
- 地址Address(nvarchar(50),)
- 生日Birthdays(date,)
- 电子邮箱Email(nvarchar(10),)
- 是否删除IsDel(default('False'))
表格创建代码如下:
use TextSchool
--Create table Students
if exists(select * from sysobjects where name = 'Students')
drop table Students
go
Create table Students
(
StudentNo int not null Primary Key ,
StudentName nvarchar(50),
LoginPwd nchar(10) not null,
Age int,
Sex bit,
Classid int,
Phone int,
Adress nvarchar(50),
Birthday date,
Email nchar(10),
IsDel bit default('False')
)
约束创建如下:
--add constraint
if exists(select * from sysobjects where name='CK_Students_Age')
alter table Students
drop constraint CK_Students_Age
alter table Students
add constraint CK_Students_Age check(0<Age and Age<100)
go
if exists(select * from sysobjects where name ='FK_Students_Classid')
alter table Students
drop constraint FK_Studnets_Classid
alter table Students
with nocheck
add constraint FK_Students_Classid foreign key(Classid) references Classes(Cid)
on delete set null
Classes表
表的结构及约束:
- 班级编号CLassId( int ,)
- 班级名称ClassName (nchar(10))
表格创建代码如下:
use TextSchool
if exists(select * from sysobjects where name = 'Classes')
drop table Classes
create table Classes
(
Classid int primary key ,
Classname nchar(10)
)
go
Scores表
表的结构及约束:
- 标识列Id(int identity(1,1),)
- 学生编号StudentId(int,)
- 课程编号SubjectId(int, FK)
- 学生分数StudentScores(int ,)
- 考试时间ExamDate(date)
表格创建代码如下:
use TextSchool
if exists(select * from sysobjects where name ='Scores')
drop table Scores
create table Scores
(
Id int identity(1,1),
StudentNo int ,
SubjectId int ,
StudentScores int,
ExamDate date
)
Subjects表
表的结构及约束:
- 课程编号SubjectId (int not null PK,)
- 课程名称SubjectName(nvarchar(10) ,)
- 课程课时ClassHour(int,)
- 课程班级编号ClassId(int )
表格创建代码如下:
use TextSchool
if exists(select * from sysobjects where name ='Subjects')
drop table Subjects
create table Subjects
(
SubjectId int not null ,
SubjectName nvarchar(10),
ClassHour int,
ClassId int
)
go
约束创建如下:
--add constraint **table Subjects
if exists(select * from sysobjects where name='PK_Subjects_SubjectId')
alter table Subjects
drop constraint PK_Subjects_SubjectId
alter table Subjects
add constraint PK_Subjects_SubjectId primary key (SubjectId)
go
详细的如何创建数据库和表格请参见: