用T-SQL创建数据库，数据表，添加约束，数据备份

--以操作系统命令行解释器的方式执行给定的命令字符串，
--并以文本行的方式返回任何输出
--在使用xp_cmdshell之前，需要执行 sp_configure以启用xp_cmdshell
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go

--删除一个文件夹
exec xp_cmdshell 'rd d:\MySchool'

--创建一个文件夹
exec xp_cmdshell 'mkdir d:\MySchool'


--创建一个数据库
use master
go
if exists (select * from sysdatabases where name='MySchool1')
drop database MySchool1
create database MySchool1
on primary
(
	name='MySchool1_data',
	filename='d:\MySchool1_data.mdf',
	size=10MB,
	maxsize=100MB,
	filegrowth=15%
)
log on 
(
	name='MySchool1_log',
	filename='d:\MySchool1_data.ldf',
	size=2MB,
	filegrowth=1MB
)
go


--创建一张表并且添加约束
use MySchool
go
if exists(select * from sysobjects where name='Student1')
drop table student1
go
create table Student1
(
	StudentNo int primary key check (len(StudentNo)>=6),
	StudentName nvarchar(50) default '无名氏' not null,
	LoginPwd nvarchar(50) check(len(LoginPwd)>=6),
	Sex char(2) check (Sex='男' or Sex='女'),
	Phone nvarchar(50) null,
	BornDate date not null check (BornDate>='1990-01-01'),
	Address nvarchar(255) default '地址不详',
	gradeId int
)

--创建年级表，给Student表外键约束使用
use MySchool
go
if exists(select * from sysobjects where name='grade')
drop table grade
create table grade
(
	gradeId int primary key,
	gradeName nvarchar(50) not null
)


--给Student表中增加一个列
alter table Student1
add idCard nvarchar(50) null


--给Student表中的idCard添加约束
alter table Student1
add constraint DF_idCard default '00000000' for idCard

--外键约束
alter table Student1
add constraint PK_gradeId foreign key(gradeId) references grade(gradeId)

--【扩展】
--查询Stduent1表中所有的约束名
use MySchool
exec sp_helpconstraint @objname=Student1
go

--数据库的备份

--备份
use master
backup database MySchool1 to disk='d:\MySchool' with format

----还原
use master 
restore database MySchool1 from disk='d:\MySchool' with replace