一、数据库创建和使用
# GO是批处理语句，用于同时执行多个语句
# 使用、切换数据库
use master
go
# 创建、删除数据库
# 方法零(最常用)：
-- 连接数据库工具，右键新建数据库，选择字符集和排序规则
-- 一般选择utf8.下面介绍一下utf8与utfmb4的区别。
# utf8mb4兼容utf8，且比utf8能表示更多的字符。至于什么时候用，看你的做什么项目了，到 http://blog.csdn.net/leelyliu/article/details/52879685 看unicode编码区从1 ～ 126就属于传统utf8区，当然utf8mb4也兼容这个区，126行以下就是utf8mb4扩充区，什么时候你需要存储那些字符，你才用utf8mb4,否则只是浪费空间。
# 排序常用如下：
-- utf8_general_ci ：不区分大小写，这个你在注册用户名和邮箱的时候就要使用。校对速度快，但准确度稍差。 （准确度够用，一般建库选择这个）
-- utf8_bin：字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容
-- utf8_unicode_ci：它和utf8_general_ci对中、英文来说没有实质的差别。准确度高，但校对速度稍慢。
# 这下面部分都很无聊，可跳过
# 方法一：
-- 判断是否存在该数据库，如果存在则删除
if (exists (select * from sys.database 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.database 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
# 方法3（设置次数据文件）
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_id 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 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相关信息
exec sp_help student;
exec sp_help classes;
