二、SQL Server 数据库基础编程

▓▓  Go批处理语句

     用于同时执行多个语句 

▓▓  使用、切换数据库

use master

go  

▓▓  创建、删除数据库

方法1、

--判断是否存在该数据库，存在就删除

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

 

 

方法2（设置文件大小）、

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

 

 

方法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

 

▓▓  基本数据类型

精确数字类型

类型
    

描述

bigint
    

bigint 数据类型用于整数值可能超过 int 数据类型支持范围的情况，范围：-2^63 到 2^63-1，存储空间8字节

int
    

整数数据类型，范围在-2^31 到 2^31-1，存储空间4字节

smallint
    

整数，范围在-2^15 到 2^15-1，存储空间2字节

tinyint
    

范围在0 到 255，存储空间1字节

bit
    

可以取值为 1、0 或 NULL 的整数数据类型，每8个bit占一个字节，16bit就2个字节，24bit就3个字节

decimal
    

带固定精度和小数位数的数值数据类型，有效值从 - 10^38 +1 到 10^38 - 1

numeric
    

同上

money
    

货币或货币值的数据类型，范围在-922,337,203,685,477.5808 到 922,337,203,685,477.5807

smallmoney
    

货币类型，-214,748.3648 到 214,748.3647

 

近似数字类型

类型
    

描述

float
    

表示浮点数值数据的大致数值数据类型。浮点数据为近似值；范围-1.79E + 308 至 -2.23E - 308、0 以及 2.23E - 308 至 1.79E + 308

real
    

real 的 SQL-92 同义词为 float(24)，范围在-3.40E + 38 至 -1.18E - 38、0 以及 1.18E - 38 至 3.40E + 38

 

日期时间类型

类型
    

描述

datetime
    

表示某天的日期和时间的数据类型，范围在1753 年 1 月 1 日到 9999 年 12 月 31 日

smalldatetime
    

范围在1900 年 1 月 1 日到 2079 年 6 月 6 日

 

字符串类型

类型
    

描述

char
    

固定长度或可变长度的字符数据类型，范围在范围为 1 至 8,000字节

text
    

最大长度为 2^31-1

varchar
    

固定长度或可变长度的字符数据类型，最大存储大小是 2^31-1 个字节

 

Unicode字符串类型

类型
    

描述

nchar
    

字符数据类型，长度固定，在必须在 1 到 4,000 之间

nvarchar
    

可变长度 Unicode 字符数据。最大存储大小为 2^31-1 字节

ntext
    

长度可变的 Unicode 数据，最大长度为 2^30 - 1 (1,073,741,823) 个字符

 

二进制字符串类型

类型
    

描述

binary
    

长度为 n 字节的固定长度二进制数据，范围从 1 到 8,000 的值。存储大小为 n 字节。

varbinary
    

可变长度二进制数据。n 可以取从 1 到 8,000 的值。最大的存储大小为 2^31-1 字节

image
    

长度可变的二进制数据，从 0 到 2^31-1 (2,147,483,647) 个字节

 

▓▓  判断表或其他对象及列是否存在

--判断某个表或对象是否存在

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

-- EXISTS 是判断是否存在,和in类似,但效率要比in高

    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相关信息

exec sp_help student;

exec sp_help classes;