数据库sql语句——之sql语句创建数据库并增加约束

-creat Database named MyDatabase
--Create Database MyDatabase

--delete Datacase named MyDatabase
--Drop Database MyDatabase

--use sql with Parameters to create databse
Create Database MyFirstDB
on Primary (
--name file
    name='MyFirstDB',
    filename='D:\MyFirstDB.mdf',--若是放在c盘，有可能会有权限不足的问题，需要放在c盘一个安全性为everyone的文件夹下
    maxsize=100Mb,
    filegrowth=50Mb
)
log on(
--name file
    name='MyFirstDB_log',
    filename='D:\MyFirstDB_log.ldf',
    maxsize=100Mb,
    filegrowth=50Mb
)
--Create table in  MyFirstDB
--<DepartmentTable>:DepartmentId DepartmentName
use MyFirstDB
Create table T_Department(
    DepId int identity(1,1) primary key,
    DepName nvarchar(100) not null
)
--insert datas into T_Department
insert into T_Department(DepName)values('销售部')
insert into T_Department(DepName)values('人事部')
insert into T_Department(DepName)values('商务部')
insert into T_Department(DepName)values('技术部')

--<EmploymeeTable>:EmploymeeId IdentifyNum Gender JoinDate Age Address Phone Emil Department
Create table T_EmployeeInf(
    EmpId int identity(1,1) primary key,
    EmpName nvarchar(50) not null,
    EmpIdentifyNum varchar(20) not null,
    EmpGender bit not null,
    EmpJoinData datetime,
    EmpAge int,
    EmpAddress nvarchar(50) not null,
    EmpPhone nvarchar(50),
    EmpEmail nvarchar(50),
    EmpDepartment int not null
)
insert into T_EmployeeInf(EmpIdentifyNum,EmpGender,EmpJoinData,EmpAge,EmpAddress,EmpPhone,EmpEmail,EmpDepartment) 
values('3344445454','小红',1,'2009-09-12',23,'1101010330','上海市闵行区','bugaosuni@qq.com',2)

作业：

Create database SchoolDB
on primary(
    name='School',
    filename='D:\School.mdf',
    maxsize=100Mb,
    filegrowth=50Mb
    
)
log on(
    name='School1',
    filename='D:\School1_log.ldf',
    maxsize=100Mb,
    filegrowth=50Mb
)
use SchoolDB
Create table TblClass(
--tClassId （班级编号，自动编号，主键）、tClassName（班级名称）、tClassDesc（班级简介）
    tClassId int identity(1,1) primary key,
    tClassName nvarchar(10) not null,
    tClassDesc nvarchar(100),
)
insert into TblClass(tClassName,tClassDesc)values('升华班','物理专业预留美的同学')
insert into TblClass(tClassName,tClassDesc)values('申奥班','地质专业留澳班的同学')
insert into TblClass(tClassName,tClassDesc)values('给力班','嘻哈山寨专业班的同学')
Create table TblStudent(
--TblStudent：tSId（学生编号，自动编号，主键）、tSName（学生姓名）、tSGender（性别）、tSAddress（家庭地址）
--tSPhone（电话）、tSAge（年龄）、tSBirthday（出生日期）、tSCardId（身份证号）、tClassId（班级Id）
    tSId int identity(1,1) primary key,
    tSName nvarchar(10) not null,
    tSGender bit not null,
    tSAddress nvarchar(50) not null,
    tSPhone nvarchar(20) not null,
    tSAge int not null,
    tSBirthday datetime not null,
    tSCard nvarchar(20) not null,
    tSClassId int not null
)
insert into TblStudent(tSName,tSGender,tSAddress,tSPhone,tSAge,tSBirthday,tSCard,tSClassId) 
values('王小二',1,'长沙市雨花区','18128212388','20','1994-01-18','134454545454',1)
insert into TblStudent(tSName,tSGender,tSAddress,tSPhone,tSAge,tSBirthday,tSCard,tSClassId) 
values('何何',0,'长沙市雨花区','13454542388','24','1990-01-18','89809804545454',2)
insert into TblStudent(tSName,tSGender,tSAddress,tSPhone,tSAge,tSBirthday,tSCard,tSClassId)
values('翟翟',0,'上海市闵行区','17654542388','23','1989-01-18','7909-04545454',3)

use  SchoolDB
Create table TblScore(
--tScoreId（成绩id,主键,自动编号）、tSId（学生编号）、tEnglish（英语成绩）、tMath（数学成绩）
    tScoreId int identity(1,1) primary key,
    tSId int not null,
    tEnglish int not null,
    tMath int not null,    
)
insert into TblScore(tSId,tEnglish,tMath) values(1,78,97)
insert into TblScore(tSId,tEnglish,tMath) values(2,88,99)
insert into TblScore(tSId,tEnglish,tMath) values(2,100,96)

Create table TblTeacher(
    --tTId、tTName、tTGender、tTAge、tTSalary、tTBirthday
    tTId int identity(1,1) primary key not null,
    tTName nvarchar(10) not null,
    tTGender bit not null,
    tTAge int not null,
    tTSalary int,
    tTBirthday datetime,
)
insert into TblTeacher(tTName,tTGender,tTAge,tTSalary,tTBirthday)values('康有为',1,29,90000,1987-09-13)
insert into TblTeacher(tTName,tTGender,tTAge,tTSalary,tTBirthday)values('有才',1,39,10000,1977-1-23)

 

--------------------------------增加约束-------------------------------
--TblTeacher表中
    --tTgender  控制只能是男 女，默认男 
    -- tTAge在30-40之间  默认30
use SchoolDB
alter table Tblteacher add 
    constraint DF_TblTeacher_tTGender default('男') for tTGender,
    constraint CK_TblTeacher_tTgender check(tTGender='男' or tTGender='女')

alter table Tblteacher add
    constraint DF_TblTeacher_tTAge default(30) for tTAge,
    constraint CK_TblTecher_tTAGe check(tTAge>=30 and tTAge<=40) 
--TblScore表中
    --测试外键约束：
    --在学生表（主表）中删除在成绩表中被引用的学生记录
    --成绩表中添加一条新成绩，studentId在学生表中没有
alter table TblScore add
     constraint FK_TblScore_TblStudent foreign key(tSId) references TblStudent(tSId)
     on delete cascade
     on update cascade
--TblStudent表中设置外键
alter table TblStudent add
    constraint FK_TblStudent_TblClass foreign key(tSClassId) references TblClass(tClassID)
    on delete cascade
    on update cascade