use master

go

--判斷是否存在該數(shù)據(jù)庫，存在就刪除

if (exists (select * from sys.databases where name = 'testHome'))

    drop database testHome

go

--創(chuàng)建數(shù)據(jù)庫，設(shè)置數(shù)據(jù)庫文件、日志文件保存目錄

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

--默認(rèn)就屬于primary主文件組，可省略

on primary (    

    --數(shù)據(jù)文件的具體描述

    name = 'testHome_data',                --主數(shù)據(jù)文件的邏輯名

    fileName = 'c:\testHome_data.mdf',    --主數(shù)據(jù)文件的物理名

    size = 3MB,                        --主數(shù)據(jù)文件的初始大小

    maxSize = 50MB,                    --主數(shù)據(jù)文件增長的最大值

    fileGrowth = 10%                --主數(shù)據(jù)文件的增長率

)

--日志文件的具體描述，各參數(shù)含義同上

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

--默認(rèn)就屬于primary主文件組，可省略

on primary (    

    --數(shù)據(jù)文件的具體描述

    name = 'testHome_data',                --主數(shù)據(jù)文件的邏輯名

    fileName = 'c:\testHome_data.mdf',    --主數(shù)據(jù)文件的物理名

    size = 3MB,                        --主數(shù)據(jù)文件的初始大小

    maxSize = 50MB,                    --主數(shù)據(jù)文件增長的最大值

    fileGrowth = 10%                --主數(shù)據(jù)文件的增長率

),

--次數(shù)據(jù)文件的具體描述

(    

    --數(shù)據(jù)文件的具體描述

    name = 'testHome2_data',            --主數(shù)據(jù)文件的邏輯名

    fileName = 'c:\testHome2_data.mdf',    --主數(shù)據(jù)文件的物理名

    size = 2MB,                        --主數(shù)據(jù)文件的初始大小

    maxSize = 50MB,                    --主數(shù)據(jù)文件增長的最大值

    fileGrowth = 10%                --主數(shù)據(jù)文件的增長率

)

--日志文件的具體描述，各參數(shù)含義同上

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

--判斷某個表或?qū)ο笫欠翊嬖?/pre>
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

--判斷是否存在當(dāng)前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

--創(chuàng)建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)

--添加默認(rèn)約束

alter table student

    add constraint sex_def default 1 for sex;

--添加一個包含默認(rèn)值可以為空的列

alter table student 

    add createDate smalldatetime null

    constraint createDate_def default getDate() with values;

----- 多個列、約束一起創(chuàng)建--------

alter table student add   

    /*添加id主鍵、自增*/  

    id int identity constraint id primary key,   

    /* 添加外鍵約束*/   

    number int null    

    constraint uNumber references classes(number),  

    /*默認(rèn)約束*/  

    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;

--查詢數(shù)據(jù)

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';

--修改數(shù)據(jù)

update student set name = 'hoho', sex = 1 where id = 1;

--刪除數(shù)據(jù)（from可省略）

delete from student where id = 1;

--備份、復(fù)制student表到stu

select * into stu from student;

select * into stu1 from (select * from stu) t;

select * from stu;

select * from stu1;

--查詢student相關(guān)信息

exec sp_help student;

exec sp_help classes;