SQL-TSQL

一、系统存储过程 常用

 sp_helptext

--查看可编程性（存储过程、函数、触发器、规则、默认值），表中（约束、触发器）
EXEC sp_helptext f_M_Student

 

二、全局变量  常用

 @@IDENTITY

--最后插入的值ID主键
INSERT INTO    UserTable
(
    -- UserID -- this column value is auto-generated
    UserName,
    NewClo
)
VALUES
(
    '西伯利亚的狼',
    1
)
SELECT @@IDENTITY AS 'Identity'

 

--先执行这一段脚本
INSERT INTO UserTable
(
    -- UserID -- this column value is auto-generated
    UserName,
    NewClo
)
VALUES
(
    '你好呀1',
    2
)
WAITFOR DELAY '00:00:10'
--返回的主键ID任然为这一个插入的ID
SELECT @@identity

--在执行这一段脚本
INSERT INTO UserTable
(
    -- UserID -- this column value is auto-generated
    UserName,
    NewClo
)
VALUES
(
    '你好呀2',
    2
)

 

@@ROWCOUNT

--上一条语句影响的行数
SELECT * FROM Student AS s WHERE s.StuName LIKE '%名字%'
SELECT @@ROWCOUNT

@@ERROE

DECLARE @ErrorFlag INT
RAISERROR ('不好意思，你没有权限！',16,1)
SET @ErrorFlag=@@ERROR  --全局错误只能在紧接着的下一步获取到，然后有默认设置为0
SELECT @@ERROR
SELECT @ErrorFlag

 

SET NOCOUNT ON;
SET XACT_ABORT ON;  --执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚

BEGIN TRY
    BEGIN TRANSACTION T

   INSERT INTO    UserTable
(
    -- UserID -- this column value is auto-generated
    UserName,
    NewClo
)
VALUES
(
    '起实物测试1',
    2
)

    RAISERROR ('不好意思，你没有权限！',16,1)    --会影响到PL/SQL的流程, 比如你若在TRY块中RAISERROR了一个严重程度小于11的, 他会直接反馈给调用者, 而不会去走CATCH块了

    SELECT GETDATE()
    
    COMMIT TRANSACTION T
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(2000)=ERROR_MESSAGE()    --将捕捉到的错误信息存在变量@msg中               
    RAISERROR (@msg,16,1)    --此处才能抛出(好像是这样子....)
    ROLLBACK TRANSACTION T　　--出錯回滾事務
END CATCH

 

三、常用函数

 长度分析函数

--所占的字符数，不含尾部空格
SELECT DATALENGTH('你好呀') 
--字符数，不含尾部空格  
SELECT LEN('你好呀')
--子串，第二个参数开始位置，第三个参数长度 
--开始位置为1 
SELECT SUBSTRING('西伯利亚的狼',1,3)
--左部截取,第二个参数未截取的数量
SELECT LEFT('西伯利亚狼',3)  
--右部截取
SELECT RIGHT('西伯利亚的狼',3)

基本字符串操作

--替换
UPDATE UserTable
SET
    UserName =replace(UserName,'1','2')
--转为大写
SELECT UPPER('qwe1')
--转为小写
SELECT LOWER('QWE23')
--生成空格
SELECT '1'+SPACE(40)+'2'
--第一个参数:待截取的字符串
--第二个参数:截取开始位置
--第三个参数:截取的长度
--第四个参数:把截取好的参数拼加在他的后面
SELECT STUFF('西伯利养的狼',2,3,'我是一个新的')
--字符串反转
SELECT REVERSE('西伯利亚的狼')
--删除左侧空格
SELECT LTRIM('   123   ')
--删除右侧空格
SELECT RTRIM('   123   ')

字符串查找

--第一个参数在第二个参数中的起始位置,没有匹配到返回0
SELECT CHARINDEX('123','13234561223')
--匹配，可以加通配符         0没有匹配到返回0
SELECT PATINDEX('%0%','1234567890')
SELECT PATINDEX('%0','1234567890')
SELECT PATINDEX('_0','1234567890')
SELECT PATINDEX('0','1234567890')
SELECT PATINDEX('0%','1234567890')

GUID

SELECT NEWID()
SELECT *,NEWID() orID FROM Student AS s ORDER BY orID

数学函数（三角函数未列出）

--绝对值
SELECT ABS(-1)--1
--大于等于  的最小整数
SELECT CEILING(10.3)--11
SELECT CEILING(10)--10
--小于等于  的最大值
SELECT FLOOR(10.3)--10
SELECT FLOOR(10)--10
--次幂   2的3次方
SELECT POWER(2,3) --2*2*2=8
--次幂  以e为底
SELECT EXP(3) --e*e*e
--自然对数值
SELECT LOG(EXP(3))  --3
--以10为底的对数值
SELECT LOG10(100)    --2
--四舍五入  精确位数
SELECT ROUND(10.2345,3)  --10.2350
--返回一个0-1之间的随机值
SELECT RAND(100)--0.715436657367485(随机的)
--判断正负0数
SELECT SIGN(10) --1
SELECT SIGN(-10) --   -1
SELECT SIGN(0)  --0
--平方根
SELECT SQRT(100)  --10

日期函数

SELECT GETDATE()
--sqlserver 的日期部分   datepart
--日期部分           写法           取值范围
--YEAR               yy                1753-9999
--quarter（季度      qq                1-3
--MONTH              mm                 1-12
--Dayofyear          dy                1-366
--DAY                dd                1-31
--week               wk                1-54
--weekday            dw                1-7(Mon-Sun)
--Hour               hh                0-23
--Minute             mi                0-59
--SECOND            ss                0-59
--Millisecond        ms                0-999
--返回日期中的指定部分   根据环境转化成适合的名字（比如下面的星期五）
SELECT DATENAME(quarter,GETDATE())--3
SELECT DATENAME(qq,GETDATE())     --3
SELECT DATENAME(dw,GETDATE())     --星期五
--返回日期中的指定部分
SELECT DATEPART(dw,GETDATE())     --5
--时间差    
SELECT DATEDIFF(yy,GETDATE(),'2016/01/01')
--时间增加
SELECT DATEADD(dd,5,GETDATE())
--返回日期值
SELECT DAY(GETDATE())

SELECT YEAR(GETDATE())

SELECT MONTH(GETDATE())

 

四、常用逻辑关键字

BEGIN END

语句块

 

CASE WHEN THEN

--WHEN NULL 不起作用
SELECT *,CASE s.StuName WHEN '默认名字' THEN '默认' when NULL then '我是NULL' else '不是默认' end FROM Student AS s

 

--最好用case when 字段 条件（比较灵活）
SELECT *,CASE WHEN s.StuName='默认名字' THEN '默认' when s.StuName is NULL then '我是NULL' else '不是默认' end FROM Student as s

IF BEGIN END ELSE BEGIN END

DECLARE @iielseFlag INT;
SET @iielseFlag = 10

IF (@iielseFlag <= 0)
BEGIN
    PRINT('小于0');
END
ELSE
BEGIN
    PRINT('(0,+oo)')
END

DECLARE @iielseFlag INT;
SET @iielseFlag = 10

IF (@iielseFlag <= 0)
BEGIN
    PRINT('小于0');
END
ELSE
    IF(@iielseFlag <= 5)
BEGIN
    PRINT('(0,5]')
END
ELSE
    BEGIN
        PRINT('其他')
    END

 

WHILE BEGIN END

DECLARE @WHILEFLAG INT =0;
WHILE (@WHILEFLAG<10) 
BEGIN
    PRINT(@WHILEFLAG);
    SET @WHILEFLAG=@WHILEFLAG+1;
END

 GOTO

　　不使用，破环结构。

RETURN

--在批处理 存储过程 触发器中无条件退出，不在执行本语句之后的任何语句。
RETURN;
会返回一个整数，代表着不同的状态  0成功，其他负数都是错误的。
0成功
-1找不到对象
-2数据类型错误
-3死锁
-4违反权限原则
-5语法错误
-6用户造成的一般错误
-7资源错误，如磁盘空间不足
-8非致命内部错误
-9已达到系统的极限
-10 -11致命的内部不一致性错误
-12表或指针破坏
-13数据库破环
-14硬件错误

WAITFOR

　　　　　　SQLserver有两种方法定时指定任务1，代理，2，waitfor

WAITFOR ｛DELAY 'time'|TIME 'time'｝

WAITFOR DELAY '00:00:10'
SELECT * FROM Student AS s

--根据当前时间设置    等到下午4点51执行
WAITFOR TIME '16:51' 
SELECT * FROM Student AS s

批处理（GO）

一条或者多条T-SQL语句的集合，SQLserver会把他当成一个整体，进行分析，在优化，编译，执行。

　　分类

1，应用程作为一个执行单元发出的所有SQL语句构成一个批处理，并且生成单个执行计划。
2，存储过程或者触发器内部所有语句构成一个批处理，他们都会自动编译为一个还行计划。
3、由EXEC执行的字符串是一个批处理。exec('select * from student')，并编译为执行计划。
4、系统存储过程sp_executesql执行的字符串是一个批处理，并便以为执行计划。 exec sp_executesql N'select * from student'

　　结束退出

1、GO批处理结束标志，编译器执行到ＧＯ时，前面的所有语句当成一个批处理执行，ＧＯ不是Ｔ－ＳＱＬ语句，而是可以被SQLserver 分析器识别的命令。
2、GO不能和T-SQL处于一行。但是这一行可以有注释。
3、局部变量只局限在一个批处理中，不会再GO以后引用。