1.创建数据库 use master go if exists(select * from sysdatabases where name='数据库名字') drop database 数据库名字 go create database 数据库名字
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 mastergoif exists(select * from sysdatabases where name='MySchool')drop database MySchoolgocreate database MySchoolon(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 MySchoolgoif exists(select * from sysobjects where name='Student')drop table Studentgocreate 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 Studentadd constraint PK_StudentNo primary key(StudentNo)--唯一约束 身份证号alter table Studentadd constraint UQ_IDEntityCard unique(IDEntityCard)--默认约束 地址不详alter table Studentadd constraint DF_Address default('地址不详') for Address--检查约束 出生日期alter table Studentadd constraint CK_BornDate check(BornDate>'1980-01-01')
use MySchoolgoif exists(select * from sysobjects where name='Subject')drop table Subjectgocreate 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 Subjectadd constraint PK_SubjectNo primary Key(SubjectNo)--检查约束alter table Subjectadd constraint CK_ClassHour check(ClassHour>0)
alter table Subjectadd constraint Ck_SubjectName check(SubjectName!=null)--外键约束
use MySchoolgoif exists(select * from sysobjects where name='Result')drop table Resultgocreate table Result(StudentNo int not null, --学号SubjectNo int not null, --课程编号StudentResult int not null, --学生成绩ExamDate datetime not null --考试日期)go
alter table Resultadd constraint PK_fuhe primary Key(StudentNo,SubjectNo,ExamDate)alter table Result--默认约束 日期add constraint DF_ExamDate default(getdate()) for ExamDatealter table Result add constraint CK_StudentResult check (100>StudentResult )alter table Result add constraint CK_StudentResult2 check (StudentResult>0 )alter table Resultadd constraint FK_SubjectNo subject是主表foreign Key(SubjectNo) references Subject(SubjectNo)alter table Resultadd constraint FK_StudentNoforeign Key(StudentNo) references Student(StudentNo)
use MySchoolgoif exists(select * from sysobjects where name='Grade')drop table Gradegocreate table Grade(GradeID int identity(1,1) not null, --年级编号GradeName nvarchar(20) not null, --年级
)go
alter table Gradeadd constraint PK_GradeID primary Key(GradeID)
--外键约束alter table Studentadd constraint FK_GradeIdforeign Key(GradeId) references Grade(GradeId)
alter table Subjectadd constraint FK_GradeId2foreign 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 Gradeselect * from Resultselect * from Studentselect * from Subject
转载于:https://www.cnblogs.com/ZkbFighting/p/8143154.html
