SQL语句创建--约束--插入数据

1.创建数据库

use master
go
if exists(select * from sysdatabases where name='数据库名字')
drop database 数据库名字
go
create database 数据库名字
on primary
(
name = 'MySchool_data',
filename = 'D:\project\MySchool_data',--主数据文件
size = 10mb,
maxsize = 100mb,
filegrowth=15%
)
log on
(
name = 'MySchool_log',
filename = 'D:\project\MySchool_log',--日志文件
size = 3mb,
maxsize = 20mb,
filegrowth=1mb
)

2.五大约束

1.—-主键约束（Primay Key constraint） 唯一性，非空性
2.—-唯一约束 （Unique constraint）唯一性，可以空，但只能有一个
3.—-检查约束 (Check constraint) 对该列数据的范围、格式的限制（如：年龄、性别等）
4.—-默认约束 (Default constraint) 该数据的默认值
5.—-外键约束 (Foreign Key constraint) 需要建立两表间的关系并引用主表的列
3.五大约束的语法示例

1.—-添加主键约束（将stuNo作为主键）

alter table stuInfo
add constraint PK_stuNo primary key (stuNo)

2.—-添加唯一约束（身份证号唯一，因为每个人的都不一样）

alter table stuInfo
add constraint UQ_stuID unique(stuID)

3.—-添加默认约束（如果地址不填 默认为“地址不详”）

alter table stuInfo
add constraint DF_stuAddress default (‘地址不详’) for stuAddress

4.—-添加检查约束 （对年龄加以限定 15-40岁之间）

alter table stuInfo
add constraint CK_stuAge check (stuAge between 15 and 40)

alter table stuInfo
add constraint CK_stuSex check (stuSex=’男’ or stuSex=’女′)

5.—-添加外键约束 (主表stuInfo和从表stuMarks建立关系，关联字段stuNo)

alter table stuInfo
add constraint FK_stuNo foreign key(stuNo)references stuinfo(stuNo)

4.直接在建表的时候添加约束

create table UserType--用户类别表
(
TypeID int not null primary key identity(1,1),
TypeName varchar(20) not null
)
create table Users--用户表
(
UserID int not null primary key identity(1,1),
UserName varchar(20) not null,
UserPwd int not null,
UserTyID int not null foreign key references UserType(TypeID)--用户类别
)
create table VisitArea--访问区域表
(
VisitID int primary key identity(1,1),
VisitName varchar(20)
)
create table UserTypeA--用户类别与区域表
(
TypeAID int not null primary key identity(1,1),
UserTypeID int not null foreign key references UserType(TypeID),
VisitID int not null foreign key references VisitArea(VisitID)
)
create table UsersLog--用户登录记录表
(
LogID int not null primary key identity(1,1),
LogName varchar(20) not null,
LogUserID int not null foreign key references Users(UserID),
LogTime date not null
)
create table VisitRecord--访问记录表
(
VRID int not null primary key identity(1,1),
VUserID int not null foreign key references Users(UserID),
VVisitID int not null foreign key references VisitArea(VisitID),
VisitTime date not null,
VUserType int not null foreign key references UserType(TypeID)
)
create table UpGrade
(
UGID int primary key not null identity(1,1),
UGUserID int not null foreign key references Users(UserID),
UpDown int not null , --升降标识
UpTypeID int not null check(UpTypeID>1 and UpTypeID<4 ),
DownTypeID int not null check(DownTypeID>1 and DownTypeID<4 ),
UpDownTime date not null,
)

--------------------------------------------------------------------------------------------------------------------------------

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=5MB,
filegrowth=15%
)
log on
(
name='MySchool_log',
filename='D:\project\MySchool_log.ldf',
size=5mb,
filegrowth=1mb
)
go

use MySchool
go
if exists(select * from sysobjects where name='Student')
drop table Student
go
create table Student
(
StudentNo int not null, --学号
LoginPwd nvarchar(20) not null, --登录密码
StudentName varchar(20) not null, --学生姓名
Sex bit not null, -- 性别
GradeId int not null, --年级
Phone varchar(20) not null, --电话号码
Address nvarchar(100), --地址
BornDate datetime not null, --出生日期
Email nvarchar(50), --邮箱
IDEntityCard varchar(18) --身份证号
)
go
--主键约束 学生标号
alter table Student
add constraint PK_StudentNo primary key(StudentNo)
--唯一约束 身份证号
alter table Student
add constraint UQ_IDEntityCard unique(IDEntityCard)
--默认约束 地址不详
alter table Student
add constraint DF_Address default('地址不详') for Address
--检查约束 出生日期
alter table Student
add constraint CK_BornDate check(BornDate>'1980-01-01')

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 nvarchar(20) not null, --课程名称
ClassHour int not null, --课时
GradeId int not null --年级
)
go

--主键约束
alter table Subject
add constraint PK_SubjectNo primary Key(SubjectNo)
--检查约束
alter table Subject
add constraint CK_ClassHour check(ClassHour>0)

alter table Subject
add constraint Ck_SubjectName check(SubjectName!=null)
--外键约束

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

alter table Result
add constraint PK_fuhe primary Key(StudentNo,SubjectNo,ExamDate)
alter table Result--默认约束 日期
add constraint DF_ExamDate default(getdate()) for ExamDate
alter table Result
add constraint CK_StudentResult check (100>StudentResult )
alter table Result
add constraint CK_StudentResult2 check (StudentResult>0 )
alter table Result
add constraint FK_SubjectNo subject是主表
foreign Key(SubjectNo) references Subject(SubjectNo)
alter table Result
add constraint FK_StudentNo
foreign Key(StudentNo) references Student(StudentNo)

use MySchool
go
if exists(select * from sysobjects where name='Grade')
drop table Grade
go
create table Grade
(
GradeID int identity(1,1) not null, --年级编号
GradeName nvarchar(20) not null, --年级

)
go

alter table Grade
add constraint PK_GradeID primary Key(GradeID)

--外键约束
alter table Student
add constraint FK_GradeId
foreign Key(GradeId) references Grade(GradeId)

alter table Subject
add constraint FK_GradeId2
foreign Key(GradeId) references Grade(GradeId)


--向Grade表插入数据
INSERT INTO Grade VALUES('S1')
--向Subject表插入数据
INSERT INTO Subject VALUES('Winforms',20,1)
--向Student表插入数据
INSERT INTO Student VALUES('10000','GuoJing','郭靖',1,1,02088762106,'天津市河西区','1987-09-08 00:00:00','GuoJing@sohu.com',111111)
--向Result表插入数据
INSERT INTO Result VALUES('10001',2,70.6,'2013-02-15 00:00:00')

select * from Grade
select * from Result
select * from Student
select * from Subject