Microsoft SQL Server Transact-SQL
1.SQL
1.1数据定义语言(DDL)
create 创建数据库或数据库对象；alter 修改数据库或数据库对象；drop 删除数据库或数据库对象;
1.2数据操纵语言(DML)
select 从表或视图中检索数据;insert 将数据插入到表或视图中;update 修改表或视图中的数据;delete 从表或视图中删除数据;
1.3数据控制语言(DCL)
grant 授予权限;revoke 收回权限;drop 禁止从其他角色中继承许可权限;
2.注释及字符编码
2.1注释
--单行注释/* 多行注释*/
2.2字符编码
ASCLL字符;Unicode字符前面添加一个大写字符N
3.变量
3.1变量类型
常量;一般变量;局部变量@;全局变量@@;
3.2变量声明
--变量声明DECLARE @aa intDECLARE @bb int
3.3变量赋值
--变量赋值 set或selectSET @aa = 6SELECT @bb = 12
4.运算符和表达式
4.1算术运算符
--变量声明declare @aa int,@bb int;--变量赋值 set或selectSET @aa = 6SELECT @bb = 12--算术运算符 + - * / %SELECT @aa + @bbSELECT @aa - @bbSELECT @aa * @bbSELECT @aa / @bbSELECT @aa % @bb
4.2关系运算符
>
>=
=
<=
<
<>或！=
!<
!>
4.3逻辑运算符
AND 与运算，两个操作数均为真时，结果才为真。
OR 或运算，若两个操作数中有一个为真结果为真。
NOT 非运算，单目运算，结果值相反。
ALL 每个操作数值都为真时结果为真。
ANY 多个操作数中有一个为真结果为真。
BETWEEN若操作数在指定范围内则结果为真
EXISTS 若子查询包含一些行则结果为真
IN 若操作数值等于表达式列表中的一个则真
LIKE 若操作数与某种模式匹配则真
- % [] [^]
SOME 若在一系列操作数中有些值为真则真
+ 字符连接运算符
4.4位运算符
&位与运算 2个参与运算的位值均为1，结果为1或则0.
|位或运算 一个为1就为1或则0
^位异或运算 不相同时为1相同时为0
SELECT 170&75
SELECT 170|75
SELECT ~170,~75
SELECT 170^75
5.流程控制语句
5.1IF ELSE语句
--变量声明declare @aa int,@bb int;--变量赋值 set或selectSET @aa = 6SELECT @bb = 12--ifif @aa>@bb print 'aa>bb'
--变量声明declare @aa int,@bb int;--变量赋值 set或selectSET @aa = 6SELECT @bb = 12--if elseif @aa>@bb print 'aa>bb'else print 'bb
--if else 嵌套declare @ee intdeclare @ff intselect @ee=6select @ff=-8if @ee>0 if @ff>0 print '第一象限' else print '第四象限'else if @ff>0 print '第二象限' else print '第三象限'
5.2CASE语句
--简单CASE语句declare @a char(10)declare @b char(10)declare @c char(10)declare @d char(10)select @a='A',@b='B',@c='C',@d='D'declare @e char(10)set @e=@dselectcase when @e=@a then 'A' when @e=@b then 'B' when @e=@c then 'C' when @e=@d then 'D'end as 成绩等级
--搜索语句declare @a char(10)declare @b char(10)declare @c char(10)declare @d char(10)select @a='A',@b='B',@c='C',@d='D'declare @e char(10)set @e=nullselect 成绩等级=case when @e=@a then 'A' when @e=@b then 'B' when @e=@c then 'C' when @e=@d then 'D' ELSE '不在定义范围'End
DECLARE @Today intSET @Today = DATEPART(DW,GETDATE())SELECT 今天是 = CASE @Today WHEN 1 THEN '星期天' WHEN 2 THEN '星期一' WHEN 3 THEN '星期二' WHEN 4 THEN '星期三' WHEN 5 THEN '星期四' WHEN 6 THEN '星期五' WHEN 7 THEN '星期六'END
5.3GOTO语句
--无条件转移语句
declare @q int
set @q=1
loving:
print @q
select @q=@q+1
while @q<=3
goto loving
5.4WHILE BREAK和CONTINUE语句
--while 语句
declare @s int
declare @n int
select @s=0,@n=1
while @n<=100
begin
set @s=@s+@n
set @n=@n+1
end
print '1+2+3...+100='+convert(char,@s)
go
--break 语句一般用于退出本次循环
declare @i int
declare @s int
select @i=1,@s=0
while @i<=100
begin
set @s=@s+@i
if @s>1000
break
set @i=@i+1
end
select @i,@s
go
--continue 语句 跳出本次循环
declare @i int
declare @s int
select @i=0,@s=0
while @i<10
begin
set @i=@i+1
if @i%2=0
set @s=@s+@i
else
continue
print @i
end
print @s
5.5WAITFOR语句
--waitfor 语句 延迟一段时间执行
waitfor delay '00:00:06'
print '执行触发器或存储过程'
5.6RETURN语句
--return语句用于从过程,批处理或语句块中无条件的推出位于return语句之后语句不再执行
declare @q int
set @q=3
if @q>1
print '能输出'
return
print '不能输出'
5.7TRY CATCH语句
begin try
create database aa
end try
begin catch
print '已经存在'
drop database aa
print '已删除'
create database aa
print '再次创建'
end catch
5.7BEGIN END语句
if exists语句
--1.判断数据库是否存在 if exists (select name from sys.databases where name = 'database_name') print 'true' else print 'false';--2.判断表是否存在if exists (select name from sysobjects where id = OBJECT_ID(N'[table_name]') and OBJECTPROPERTY(id,N'IsUserTable') = 1) print 'true' else print 'false';--3.判断存储过程是否存在if exists (select * from sysobjects where id = OBJECT_ID(N'[procedure_name]') and OBJECTPROPERTY(id,N'IsProcedure') = 1) print 'true' else print 'false';--4.判断临时表是否存在if OBJECT_ID('tempdb..#temptable_name') is not null print 'true' else print 'false';--5.判断视图是否存在if exists (select object_id from sys.views where object_id = (select object_id(N'[dbo].[view_name]'))) print 'true' else print 'false';--6.判断自定义函数是否存在if exists (select * from dbo.sysobjects where id = (select object_id(N'[dbo].[Fun_name]')) and xtype in (N'FN',N'IF',N'TF')) print 'true' else print 'false';--7.判断列是否存在if exists(select * from syscolumns where id=object_id('table_name') and name='Columns_name') print 'true' else print 'false';--8.判断列是否自增列if COLUMNPROPERTY(object_id('table_name'),'col','IsIdentity')=1 print 'auto_columns' else print 'unauto_columns'--9.判断表中是否存在索引if exists(select * from sysindexes where id=object_id('table_name') and name='Index_name') print 'Index' else print 'unindex'
函数
6.1数学函数
--ABS(数值表达式)为绝对值函数,返回数值表达式的绝对值
SELECT N'-100的绝对值'=ABS(-100),N'0的绝对值'=ABS(0),N'100的绝对值'=ABS(100)
--RAND()为随机函数返回0-1之间的float数值
SELECT N'随机数'=RAND()
--PI()圆周率函数返回14位小数的圆周率常量值
SELECT N'14位小数的圆周率数值'=PI()
--SQRT(数值表达式)平方根函数返回表达式的平方根
SELECT N'64的平方根'=SQRT(64)
--EXP(数值表达式)指数函数，返回表达式的指数值
SELECT N'e的10次方'=EXP(10)
--LOG(数值表达式)自然对数函数，返回表达式的自然对数值
SELECT
N'e的10次方'=EXP(10),
N'5的自然对数'=LOG(5),
SELECT CEILING(13.4),FLOOR(13.4),ROUND(13.4567,3)
--数学函数RAND()用于返回介于0与1之间的伪随机float值;select RAND();--输出10个伪随机float值;declare @i int=1;while @i <= 10begin print RAND(); set @i += 1;end;--输出1001~1500中的数值declare @begno int = 1001;declare @endno int = 1500;declare @result int = 0;while 1=1begin set @result = CEILING(RAND()*@endno); if @result >= @begno and @result <=@endno begin print @result; break; end;end;--增加脚本的重用性，写成自定义函数create view getrandasselect RAND() as rand;create function choujian( @begno int = 1001,@endno int = 1500 ) returns intasbegin declare @result int=0; while 1=1 begin select @result=ceiling([rand]*@endno) from getrand; if @result>=@begno and @result<=@endno begin break; end;end;return @result;end;declare @i int=1;while @i <= 10begin print dbo.choujiang(1001,1500); set @i += 1;end;
6.2字符串函数
--ASCII(字符表达式)ASCII函数返回字符串表达式中最左端字符的ASCII码值
SELECT ASCII('a'),ASCII('ADD')
--CHAR(整型表达式)ASCII码转换函数，参数为介于0~255之间的整数，返回整数的ASCII码
SELECT CHAR(97)
--LOWER(字符表达式)小写字母函数，将大写字母转换为小写字母，返回表达式的小写字母表示
SELECT LOWER('A')
--UPPER(字符表达式)大写字母函数，返回指定表达式的大写字母表示
SELECT UPPER('a')
--LEN(字符表达式)字符串长度函数返回字符串表达式中字符的个数
SELECT LEN('A'),LEN('ASDC')
--LEFT(字符表达式，n)左子串函数返回从字符串左边开始的n个字符
SELECT LEFT('SFSFS',2)
--RIGHT(字符表达式，n)右子串函数返回从字符串右边开始的n个字符
SELECT RIGHT('ADASDADADA',6)
--LTRIM(字符表达式) 删除前导空格函数，返回删除了前导空格的字符表达式
SELECT LTRIM(' aaaaaaa')
--RTRIM(字符表达式) 删除尾随空格函数，返回删除所有尾随空格的字符表达式
SELECT RTRIM('AAAAAA ')
--REPLACE(字符表达式1，字符表达式2，字符表达式3)字符替换函数，
--用第三个表达式替换第一个字符串表达式中包含的所有第二个字符表达式，并返回替换后的表达式
SELECT REPLACE('AABBCCDD','BBCC','EEFF')
--STR()函数用于将数值型数据转换为字符串
--str(数字类型的表达式[,表达式总长度][,小数点后面的位数])表达式总长度和小数点后面的位数为可选择参数
--将244533.3344转换为长度为11位，小数点后有3位的字符串
SELECT STR(244533.3344,11,2),STR(1234.56789,11,2)
--SUBSTRING(字符串表达式，起始位置，长度)字符串截取函数
SELECT SUBSTRING('abcde',1,0)
SELECT SUBSTRING('abcde',0,8)
--Replicate函数用于指定的次数重复字符串表达式
SELECT replicate('loving',3)
--字符串替换函数格式：STUFF（字符表达式1，起始位置，长度，字符表达式2）
--功能：用“字符表达式2”的值替换“字符表达式1”中由“起始位置”和“长度”指明的一个子串。
select STUFF('计算机等级考试',4,2,'专业')
select STUFF('计算机等级考试',6,0,'二级')
--Charindex函数返回字符串指定表达式的起始位置。
select charindex('l','I like football') --搜索的起始位置是1
select charindex('l','I like football',4) --搜索的起始位置是4
select charindex('l','I like football',15) --搜索的起始位置是15
--EVENTDATA()函数SELECT EVENTDATA();
6.3日期和时间函数
--GETDATE(日期时间型数据)以标准格式返回本地服务器的DATETIME值
SELECT GETDATE()
--YEAR(日期时间型数据)返回指定日期的“年”部分的整数
SELECT YEAR('2018-09-10'),YEAR(GETDATE())
--MONTH(日期时间型数据)返回指定日期的“月”部分的整数
SELECT MONTH('2018-09-10'),MONTH(GETDATE())
--DAY(日期时间型数据)返回指定日期的"天"部分的整数
SELECT DAY('2018-09-10'),DAY(GETDATE())
--DATEPART(datepart,date) 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
/*
date 参数是合法的日期表达式。
datepart 参数可以是
年 yy, yyyy
季度 qq, q
月 mm, m
年中的日 dy, y
日 dd, d
周 wk, ww
星期 dw, w
小时 hh
分钟 mi, n
秒 ss, s
毫秒 ms
微妙 mcs
纳秒 ns
*/
SELECT N'日期的年'=DATEPART(YYYY,GETDATE())
SELECT N'日期的天'=DATEPART(DD,GETDATE())
--DATEADD(时间间隔，数值表达式，日期)
/*
为DATETIME值添加间隔。时间间隔项决定时间间隔的单位，可取Year、Day of year(一年的日数)、
Quarter、Month、Day、Week、Weekday(一周的日数)、Hour、Minute、Second、Millisecond。
数值表达式为加上或者减去的时间间隔
*/
SELECT N'10年后的日期'=DATEADD(YEAR,10,GETDATE()),
N'10天前的日期'=DATEADD(DAY,-10,GETDATE())
--DATEDIFF(时间间隔，日期1，日期2)
/*
计算两个DATETIME值之间指定的日期部分的差，返回数值型数据。
时间间隔项决定时间间隔的单位，取值与DATEADD中的时间间隔项相同
*/
SELECT N'日期相差的年份'=DATEDIFF(YEAR,'2018-09-10',GETDATE()),
N'日期相差的天数'=DATEDIFF(DAY,'2018-09-10',GETDATE())
6.4数据类型转换函数
--隐式转换
select str(123.456),str(123.456,5,2),STR(123.456,2)
--显式转换
select cast('123' as int)+10
select CONVERT(nvarchar(30),getdate(),102)+'转换成字符型'
CONVERT()将一种数据类型转变为另一种数据类型
CURRENT_USER()返回当前用户的名称
ISDATE()判断是否有效日期
ISNULL()指定一个值替换空值
ISNUMERIC()判断输入的是不是一个数值
6.5聚合函数
--avg：返回一组值的平均值。
--binary_checksum：返回对表中的行或者表达式列表计算的二进制校验位。
--checksum：返回在表中的行或者表达式列表计算的校验值，该函数用于生成哈希索引。
--checksum_agg：返回一组值的校验值。
--count:返回一组值中项目的数量。(返回值为int类型)。
--count_big：返回一组值中项目的数量。(返回值为bigint类型)。
--grouping：产生一个附加的列，当用cube或rollup运算符添加行时，附加的列输出为1，当添加的行不是由cube或rollup运算符产生时，附加的列输出为0。
--max：返回表达式或者项目中的最大值。
--min：返回表达式或者项目中的最小值。
--sum：返回表达式中所有项的和，或者只返回distinct值。sum只能用于数字列。
--stdev：返回表达式中所有值的统计标准偏差。
--stdevp：返回表达式中所有值的统计统计标准偏差。
--var：返回表达式中所有值的统计标准方差。
--varp：返回表达式中所有值的统计统计标准方差。
use 实例数据库
select avg(分数) as 平均成绩
from 选课表
go
use 实例数据库
select max(分数) as 最高成绩
from 选课表
go
use 实例数据库
select count(学号) as 总人数
from 选课表
go
6.6用户自定义函数
用户在编写程序的过程中，除了可以调用系统函数外，还可以根据自己的需要自定义函数。自定义函数包括表值函数和标量值函数两类，其中表值函数又包括内联表值函数和多语句表值函数。
内联表值函数：返回值为可更新表。如果用户自定义函数包含单个SELECT语句且该语句可以更新，则该函数返回的表也可以更新。
多语句表值函数：返回值为不可更新表。如果用户自定义函数包含多个SELECT语句，则该函数返回的表不可更新。
标量函数：返回值为标量值。
--1.标量函数
create function maxf (@para1 real,@para2 real)
returns real
as
begin
declare @max real
if @para1>@para2
set @max=@para1
else
set @max=@para2
return(@max)
end
--2.内嵌表值函数：只能通过select语句调用，在调用时可以省略函数的所有者
create function a(@no char(10)) returns table
as
return
(select student.学号,姓名,课程名,成绩
from student,sc,course
where student.学号=sc.学号
and sc.课程号=course.课程号
and student.学号=@no)
go
select * from a('2008056102')
--3.多语句表值函数：
create function b(@no char(10))
returns @score table
(sno char(10),
sname varchar(8),
cname varchar(30),
grade tinyint)
as
begin
insert @score
select student.学号,姓名,课程名,成绩
from student,sc,course
where student.学号=sc.学号
and sc.课程号=course.课程号
and student.学号=@no
return
end
go
select * from b('2008056103')
--4.自定义函数的调用调用标量函数，必顺提供至少由两部分组成的名称（所有者.函数名），可用print, select, exec语句调用
print dbo.maxf(12,38.6)
select N'最大数为:'+
convert(varchar(10),dbo.maxf(2.8,15676.9))
declare @m real
exec @m=dbo.maxf 12.8,789.6
select @m as '最大数为‘
declare @m real
exec @m=dbo.maxf @para1=789.6,@para2=12.8
select @m as '最大数为'
内嵌表值函数和多语句表值函数都返回表，二者不同之处在于：
内嵌表值函数没有函数体，返回的表是单个select语句的结果集。
多语句表值函数在begin….end 定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入表中，最后返回表。