use master
go

--判断是否存在该数据库，存在就删除
if (exists (select * from sys.databases where name = 'testHome'))
    drop database testHome
go
--创建数据库，设置数据库文件、日志文件保存目录
create database testHome
on(
    name = 'testHome',
    filename = 'c:\data\students.mdf'    
)
log on(
    name = 'testHome_log',
    filename = 'c:\data\testHome_log.ldf'
)
go

if (exists (select * from sys.databases where name = 'testHome'))
    drop database testHome
go
create database testHome
--默认就属于primary主文件组，可省略
on primary (    
    --数据文件的具体描述
    name = 'testHome_data',                --主数据文件的逻辑名
    fileName = 'c:\testHome_data.mdf',    --主数据文件的物理名
    size = 3MB,                        --主数据文件的初始大小
    maxSize = 50MB,                    --主数据文件增长的最大值
    fileGrowth = 10%                --主数据文件的增长率
)
--日志文件的具体描述，各参数含义同上
log on (
    name = 'testHome_log',
    fileName = 'c:\testHome_log.ldf',
    size = 1MB,
    fileGrowth = 1MB
)
go

if (exists (select * from sys.databases where name = 'testHome'))
    drop database testHome
go
create database testHome
--默认就属于primary主文件组，可省略
on primary (    
    --数据文件的具体描述
    name = 'testHome_data',                --主数据文件的逻辑名
    fileName = 'c:\testHome_data.mdf',    --主数据文件的物理名
    size = 3MB,                        --主数据文件的初始大小
    maxSize = 50MB,                    --主数据文件增长的最大值
    fileGrowth = 10%                --主数据文件的增长率
),
--次数据文件的具体描述
(    
    --数据文件的具体描述
    name = 'testHome2_data',            --主数据文件的逻辑名
    fileName = 'c:\testHome2_data.mdf',    --主数据文件的物理名
    size = 2MB,                        --主数据文件的初始大小
    maxSize = 50MB,                    --主数据文件增长的最大值
    fileGrowth = 10%                --主数据文件的增长率
)
--日志文件的具体描述，各参数含义同上
log on (
    name = 'testHome_log',
    fileName = 'c:\testHome_log.ldf',
    size = 1MB,
    fileGrowth = 1MB
),
(
    name = 'testHome2_log',
    fileName = 'c:\testHome2_log.ldf',
    size = 1MB,
    fileGrowth = 1MB
)
go

--判断某个表或对象是否存在
if (exists (select * from sys.objects where name = 'classes'))
    print '存在';
go
if (exists (select * from sys.objects where object_id = object_id('student')))
    print '存在';
go
if (object_id('student', 'U') is not null)
    print '存在';
go
 
--判断该列名是否存在，如果存在就删除
if (exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard'))
    alter table student drop column idCard
go
if (exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
    alter table student drop column tel
go

--判断是否存在当前table
if (exists (select * from sys.objects where name = 'classes'))
    drop table classes
go
create table classes(
    id int primary key identity(1, 2),
    name varchar(22) not null,
    createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where object_id = object_id('student')))
    drop table student
go
--创建table
create table student(
    id int identity(1, 1) not null,
    name varchar(20),
    age int,
    sex bit,
    cid int
)
go

--添加字段
alter table student add address varchar(50) not null;
--修改字段
alter table student alter column address varchar(20);
--删除字段
alter table student drop column number;
 
--添加多个字段
alter table student 
add address varchar(22),
    tel varchar(11),
    idCard varchar(3);
 
--判断该列名是否存在，如果存在就删除
if (exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard'))
    alter table student drop column idCard
go
if (exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
    alter table student drop column tel
go

--添加新列、约束
alter table student 
    add number varchar(20) null constraint no_uk unique;  
--增加主键
alter table student  
    add constraint pk_id primary key(id);  
--添加外键约束
alter table student
    add constraint fk_cid foreign key (cid) references classes(id)
go
--添加唯一约束
alter table student
    add constraint name_uk unique(name);
--添加check约束
alter table student with nocheck   
    add constraint check_age check (age > 1);
alter table student
    add constraint ck_age check (age >= 15 and age <= 50)
--添加默认约束
alter table student
    add constraint sex_def default 1 for sex;
--添加一个包含默认值可以为空的列
alter table student 
    add createDate smalldatetime null
    constraint createDate_def default getDate() with values;
 
----- 多个列、约束一起创建--------
alter table student add   
    /*添加id主键、自增*/  
    id int identity constraint id primary key,   
    /* 添加外键约束*/   
    number int null    
    constraint uNumber references classes(number),  
    /*默认约束*/  
    createDate decimal(3, 3)  
    constraint createDate default 2010-6-1  
go 
 
--删除约束
alter table student  drop constraint no_uk;

insert into classes(name) values('1班');
insert into classes values('2班', '2011-06-15');
insert into classes(name) values('3班');
insert into classes values('4班', default);
 
insert into student values('zhangsan', 22, 1, 1);
insert into student values('lisi', 25, 0, 1);
insert into student values('wangwu', 24, 1, 3);
insert into student values('zhaoliu', 23, 0, 3);
insert into student values('mazi', 21, 1, 5);
insert into student values('wangmazi', 28, 0, 5);
insert into student values('jason', null, 0, 5);
insert into student values(null, null, 0, 5);
 
insert into student 
select 'bulise' name, age, sex, cid 
from student 
where name = 'tony';
    
--多条记录同时插入
insert into student
    select 'jack', 23, 1, 5 union
    select 'tom', 24, 0, 3 union
    select 'wendy', 25, 1, 3 union
    select 'tony', 26, 0, 5;

--查询数据
select * from classes;
select * from student;
select id, 'bulise' name, age, sex, cid from student 
where name = 'tony';
select *, (select max(age) from student) from student 
where name = 'tony';
 
--修改数据
update student set name = 'hoho', sex = 1 where id = 1;
 
--删除数据（from可省略）
delete from student where id = 1;

--备份、复制student表到stu
select * into stu from student;
select * into stu1 from (select * from stu) t;
select * from stu;
select * from stu1;--备份、复制student表到stu
select * into stu from student;
select * into stu1 from (select * from stu) t;
select * from stu;
select * from stu1;

--查询student相关信息
exec sp_help student;
exec sp_help classes;