在我们的学习过程中经常用到的几个表格: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
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)
ondelete 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
详细的如何创建数据库和表格请参见:
SQL语句创建表单table
SQL语句创建和删除数据库
SQL数据完整性介绍和SQL语句创建约束
领取专属 10元无门槛券
私享最新 技术干货