SQL Server T-SQL 编程

by admin on 2019年9月2日

SQL代表结构化查询语言,是一种在关系数据库系统中查询和管理数据的标准语言。sql语句也有几个类别,包括定义语言(DDL),数据操作语言(DML),数据控制语言(DCL)。

集合理论

集合,我们的意思是:任意集合体M是我们感知或想到的,能够确定的、互异的对象m(称为M的元素)的整体。
——Joseph W. Dauben 和 Georg Cantor(普林斯顿大学出版社,1990年)

Ø 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'))
    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;
T-SQL语句用于管理SQL Server数据库引擎实例,创建和管理数据库对象,以及查询、插入、修改和删除数据。

Ø 变量

     1、 局部变量(Local Variable)

          局部变量是用户可以自定义的变量,它的作用范围是仅在程序内部,在程序中通常用来储存从表中查询到的数据或当做程序执行过程中的暂存变量。使用局部变量必须以@开头,而且必须用declare命令后才能使用。



          基本语法:

声明变量
declare @变量名 变量类型 [@变量名 变量类型]
为变量赋值
set @变量名 = 变量值;
select @变量名 = 变量值;


          示例:

--局部变量
declare @id char(10)--声明一个长度的变量id
declare @age int    --声明一个int类型变量age
    select @id = 22    --赋值操作
    set @age = 55    --赋值操作
    print convert(char(10), @age) + '#' + @id
    select @age, @id
go

简单hello world示例
declare @name varchar(20);
declare @result varchar(200);
set @name = 'jack';
set @result = @name + ' say: hello world!';
select @result;

查询数据示例
declare @id int, @name varchar(20);
set @id = 1;
select @name = name from student where id = @id;
select @name;

select赋值
declare @name varchar(20);
select @name = 'jack';
select * from student where name = @name;
          从上面的示例可以看出,局部变量可用于程序中保存临时数据、传递数据。Set赋值一般用于赋值指定的常量个变量。而select多用于查询的结果进行赋值,当然select也可以将常量赋值给变量。

          注意:在使用select进行赋值的时候,如果查询的结果是多条的情况下,会利用最后一条数据进行赋值,前面的赋值结果将会被覆盖。



     2、 全局变量(Global Variable)

          全局变量是系统内部使用的变量,其作用范围并不局限于某一程序而是任何程序均可随时调用的。全局变量一般存储一些系统的配置设定值、统计数据。

全局变量
select @@identity;--最后一次自增的值
select identity(int, 1, 1) as id into tab from student;--将studeng表的烈属,以/1自增形式创建一个tab
select * from tab;
select @@rowcount;--影响行数
select @@cursor_rows;--返回连接上打开的游标的当前限定行的数目
select @@error;--T-SQL的错误号
select @@procid;

--配置函数
set datefirst 7;--设置每周的第一天,表示周日
select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';
select @@dbts;--返回当前数据库唯一时间戳
set language 'Italian';
select @@langId as 'Language ID';--返回语言id
select @@language as 'Language Name';--返回当前语言名称
select @@lock_timeout;--返回当前会话的当前锁定超时设置(毫秒)
select @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数
select @@MAX_PRECISION AS 'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别
select @@SERVERNAME;--SQL Server 的本地服务器的名称
select @@SERVICENAME;--服务名
select @@SPID;--当前会话进程id
select @@textSize;
select @@version;--当前数据库版本信息

--系统统计函数
select @@CONNECTIONS;--连接数
select @@PACK_RECEIVED;
select @@CPU_BUSY;
select @@PACK_SENT;
select @@TIMETICKS;
select @@IDLE;
select @@TOTAL_ERRORS;
select @@IO_BUSY;
select @@TOTAL_READ;--读取磁盘次数
select @@PACKET_ERRORS;--发生的网络数据包错误数
select @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数


Ø 输出语句

     T-SQL支持输出语句,用于显示结果。常用输出语句有两种:

     基本语法

print 变量或表达式
select 变量或表达式


      示例

select 1 + 2;
select @@language;
select user_name();

print 1 + 2;
print @@language;
print user_name();
     print在输出值不少字符串的情况下,需要用convert转换成字符串才能正常输出,而且字符串的长度在超过8000的字符以后,后面的将不会显示。



Ø 逻辑控制语句

     1、 if-else判断语句

          语法

if <表达式>
    <命令行或程序块>
else if <表达式>
    <命令行或程序块>
else
    <命令行或程序块>
          示例

if简单示例
if 2 > 3
    print '2 > 3';
else
    print '2 < 3';

if (2 > 3)
    print '2 > 3';
else if (3 > 2)
    print '3 > 2';
else
    print 'other';

简单查询判断
declare @id char(10),
        @pid char(20),
        @name varchar(20);
set @name = '广州';
select @id = id from ab_area where areaName = @name;
select @pid = pid from ab_area where id = @id;
print @id + '#' + @pid;

if @pid > @id
    begin
        print @id + '%';
        select * from ab_area where pid like @id + '%';
    end
else
    begin
        print @id + '%';
        print @id + '#' + @pid;
        select * from ab_area where pid = @pid;
    end
go


       2、 while…continue…break循环语句

          基本语法

while <表达式>
begin
   <命令行或程序块>
   [break]
   [continue]
   <命令行或程序块>
end
          示例

--while循环输出到
declare @i int;
    set @i = 1;
while (@i < 11)
    begin
        print @i;
        set @i = @i + 1;
    end
go

--while continue 输出到
declare @i int;
    set @i = 1;
while (@i < 11)
    begin                
        if (@i < 5)
            begin
                set @i = @i + 1;
                continue;        
            end
        print @i;
        set @i = @i + 1;                
    end
go

--while break 输出到
declare @i int;
    set @i = 1;
while (1 = 1)
    begin        
        print @i;        
        if (@i >= 5)
            begin
                set @i = @i + 1;
                break;        
            end        
        set @i = @i + 1;                
    end
go


     3、 case

          基本语法

case
   when <条件表达式> then <运算式>
   when <条件表达式> then <运算式>
   when <条件表达式> then <运算式>
   [else <运算式>]
end
          示例

select *,
    case sex 
        when 1 then '男'
        when 0 then '女'    
        else '火星人'
    end as '性别'
from student;

select areaName, '区域类型' = case
        when areaType = '省' then areaName + areaType
        when areaType = '市' then 'city'
        when areaType = '区' then 'area'
        else 'other'
    end
from ab_area;


       4、 其他语句

批处理语句go
Use master
Go

延时执行,类似于定时器、休眠等
waitfor delay '00:00:03';--定时三秒后执行
print '定时三秒后执行';

T-SQL语句用于管理SQL
Server数据库引擎实例,创建和管理数据库对象,以及查询、插入、修改和删除数据。

DDL包括create,alter,drop等语句,DML用于查询和修改包括select,insert,update,delete
,DCL包括 group,revoke。

谓词逻辑

一般来说,谓词是一个属性或是一个表示“持有”或“不持有”的表达式,换句话说,也就是“真”或“假”。

 

 

 

1.集合理论

关系模型

关系模型是基于集合理论和谓词逻辑进行数据管理和操作的语义模型。

*参考:
《SQL Server 2012 T-SQL基础教程》Itzik Ben-Gan
著;张洪举,李联国,张昊天 译

Ø 变量

“互异”每一个元素必须唯一,一个教室里,人可以被认为是集合,学生或者教室都可以,因此根据选定不同角色定义不同的集合。

1、 局部变量(Local
Variable)
局部变量是用户可以自定义的变量,它的作用范围是仅在程序内部,在程序中通常用来储存从表中查询到的数据或当做程序执行过程中的暂存变量。使用局部变量必须以@开头,而且必须用declare命令后才能使用。

2.谓词逻辑

基本语法:

谓词可以理解成一个属性,简单点说就是“真”或”假“ 
但是在开发中又加了一个属性  真,假,null。

声明变量
declare @变量名 变量类型 [@变量名 变量类型]
变量赋值
set @变量名 = 变量值;
select @变量名 = 变量值;

3.关系模型

局部变量示例:
declare @id char(10)–声明一个长度的变量id
declare @age int –声明一个int类型变量age
select @id = 22 –赋值操作
set @age = 55 –赋值操作
print convert(char(10), @age) + ‘#’ + @id
select @age, @id
go

确保数据的一致性表示,最小化或者有冗余切不牺牲完整性

查询数据示例
declare @id int, @name varchar(20);
set @id = 1;
select @name = name from student where id = @id;
select @name;

4.缺失值

局部变量可用于程序中保存临时数据、传递数据。Set赋值一般用于赋值指定的常量个变量。而select多用于查询的结果进行赋值,当然select也可以将常量赋值给变量。
在使用select进行赋值的时候,如果查询的结果是多条的情况下,会利用最后一条数据进行赋值,前面的赋值结果将会被覆盖。

一个事件判断不是“真”就是“假” 但却并不能处理所有情况。类似于
把客户的手机号存入数据库字段“ml” 客户可能手机号更换 就意味着缺失
也是第三种关系 对应 null 。

2、 全局变量(Global
Variable)
全局变量是系统内部使用的变量,其作用范围并不局限于某一程序而是任何程序均可随时调用的。全局变量一般存储一些系统的配置设定值、统计数据。

5.三大范式

–全局变量
select @@identity;–最后一次自增的值
select identity(int, 1, 1) as id into tab from
student;–将student表的列,以/1自增形式创建一个tab
select * from tab;
select @@rowcount;–影响行数
select @@cursor_rows;–返回连接上打开的游标的当前限定行的数目
select @@error;–T-SQL的错误号
select @@procid;

 1NF:表与行必须是为唯一的,简单点说表正确的表示了关系就符合第一范式。

–配置函数
set datefirst 7;–设置每周的第一天,表示周日
select @@datefirst as ‘星期的第一天’, datepart(dw, getDate()) AS
‘今天是星期’;
select @@dbts;–返回当前数据库唯一时间戳
set language ‘Italian’;
select @@langId as ‘Language ID’;–返回语言id
select @@language as ‘Language Name’;–返回当前语言名称
select @@lock_timeout;–返回当前会话的当前锁定超时设置(毫秒)
select @@max_connections;–返回SQL Server
实例允许同时进行的最大用户连接数
select @@MAX_PRECISION AS ‘Max Precision’;–返回decimal 和numeric
数据类型所用的精度级别
select @@SERVERNAME;–SQL Server 的本地服务器的名称
select @@SERVICENAME;–服务名
select @@SPID;–当前会话进程id
select @@textSize;
select @@version;–当前数据库版本信息

 2NF:满足第一范式和主外键的关系。

–系统统计函数
select @@CONNECTIONS;–连接数
select @@PACK_RECEIVED;
select @@CPU_BUSY;
select @@PACK_SENT;
select @@TIMETICKS;
select @@IDLE;
select @@TOTAL_ERRORS;
select @@IO_BUSY;
select @@TOTAL_READ;–读取磁盘次数
select @@PACKET_ERRORS;–发生的网络数据包错误数
select @@TOTAL_WRITE;–sqlserver执行的磁盘写入次数

 3NF:满足第二范式 非主键属性不能依赖另外的非主键属性。

Ø 输出语句

 

T-SQL支持输出语句,用于显示结果。常用输出语句有两种:

基本语法

print 变量或表达式
select 变量或表达式

 

示例:

select 1 + 2;
select @@language;
select user_name();

print 1 + 2;
print @@language;
print user_name();

print在输出值不少字符串的情况下,需要用convert转换成字符串才能正常输出,而且字符串的长度在超过8000的字符以后,后面的将不会显示。

Ø 逻辑控制语句

1、 if-else判断语句

语法

if <表达式>
<命令行或程序块>
else if <表达式>
<命令行或程序块>
else
<命令行或程序块>

示例:

–if简单示例
if (2 > 3)
  print ‘2 > 3’;
else if (3 > 2)
  print ‘3 > 2’;
else
  print ‘other’;

–简单查询判断
declare @id char(10),
    @pid char(20),
    @name varchar(20);
set @name = ‘广州’;
select @id = id from ab_area where areaName = @name;
select @pid = pid from ab_area where id = @id;
print @id + ‘#’ + @pid;

if @pid > @id
  begin
    print @id + ‘%’;
    select * from ab_area where pid like @id + ‘%’;
  end
else
  begin
    print @id + ‘%’;
    print @id + ‘#’ + @pid;
    select * from ab_area where pid = @pid;
  end
go

2、 while…continue…break循环语句

基本语法

while <表达式>
begin
<命令行或程序块>
[break]
[continue]
<命令行或程序块>
end

 

示例:

–while循环输出
declare @i int;
set @i = 1;
while (@i < 11)
  begin
    print @i;
    set @i = @i + 1;
  end
go

–while continue
declare @i int;
set @i = 1;
while (@i < 11)
  begin
    if (@i < 5)
      begin
      set @i = @i + 1;
      continue;
      end
    print @i;
    set @i = @i + 1;
  end
go

–while break
declare @i int;
set @i = 1;
while (1 = 1)
  begin
    print @i;
    if (@i >= 5)
      begin
        set @i = @i + 1;
        break;
      end
    set @i = @i + 1;
  end
go

 

3、 case

基本语法

case
when <条件表达式> then <运算式>
when <条件表达式> then <运算式>
when <条件表达式> then <运算式>
[else <运算式>]
end

 

示例:

select *,
case sex
  when 1 then ‘男’
  when 0 then ‘女’
  else ‘火星人’
  end as ‘性别’
from student;

select areaName, ‘区域类型’ = case
  when areaType = ‘省’ then areaName + areaType
  when areaType = ‘市’ then ‘city’
  when areaType = ‘区’ then ‘area’
  else ‘other’
  end
from ab_area;

 

4、 其他语句

延时执行
waitfor delay ’00:00:03′;–定时三秒后执行
print ‘定时三秒后执行’;

 

 

 

 

 

 

本文转自:

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图