1.字符串函数
1 --ascii函数,返回字符串最左侧字符的ascii码值
2 SELECT ASCII('dsd') AS asciistr
3 --ascii代码转换函数,返回指定ascii值对应的字符
4 SELECT CHAR(97) AS strs
5 --left(c1,n)左子串函数,返回字符串c1中从左边开始的n个字符
6 SELECT LEFT('helloworld',4) AS lefts
7 --len(c1)返回字符串c1的字符(而不是字节)数,其中不包含尾随空格
8 SELECT LEN('helloworld') AS lens
9 --lower(c1)
10 SELECT LOWER('HELLOWORLD') AS lowers
11 --LTRIM(c1)删除前导空格,返回删除了前导空格的字符串
12 SELECT LTRIM(' asafas') AS lt
13 --REPLACE(C1,C2,C3)
14 SELECT REPLACE('ZHESSDF-DFDSS','-','@') AS RE
15 --REPLICATE(C1,N)复制函数,以指定的次数n重复字符串c1
16 SELECT REPLICATE('这是复制函数-',3) AS rep
17 --right(c1,n)右子串函数,返回字符串c1中从右边开始的n个字符串
18 SELECT RIGHT('asdfggh',4) AS strs
19 --space(n) 空格函数,返回由n个重复的空格字符串
20 SELECT SPACE(7) AS spaces
21 --str(f,n,m)返回由数值数据f转换来的字符串,字符串的长度有参数n决定,字符串中保留的小数位书由M决定
22 SELECT STR(12.1234,9,6) AS strs
23 --SUBSTRING(c1,n1,n2)求子串函数,返回字符串c1从n1开始,长度为n2的子串
24 SELECT SUBSTRING('mylightshina',4,5)
25 --upper(c1)
26 SELECT UPPER('asdff') AS up
2.日期函数
1 --时间日期
2 SELECT 'today''s date is '+ CONVERT(VARCHAR(12), GETDATE(),102)AS da
1 SELECT GETDATE() 当前日期时间,
2 GETUTCDATE() ut日期时间,
3 DATEPART(YEAR, GETDATE()) 年,
4 DATENAME(YEAR, GETDATE()) 年名,
5 DATEPART(MONTH, GETDATE()) 月,
6 DATENAME(MONTH, GETDATE()) 月份名,
7 DATEPART(DAY, GETDATE()) 日,
8 DAY(GETDATE()) 日,
9 MONTH(GETDATE()) 月,
10 YEAR(GETDATE()) 年,
11 DATEADD(DAY, 5, GETDATE()) 新日期,
12 DATEDIFF(DAY, GETDATE(), '2018-12-17') AS 日期间隔;
3.系统函数
1 --转换函数 cast(),convert()
2 SELECT CAST('2018' AS DECIMAL(10, 5)) AS deci,
3 CONVERT(VARCHAR, GETDATE(), 101) AS '101',
4 CONVERT(VARCHAR, GETDATE(), 102) AS '102',
5 CONVERT(VARCHAR, GETDATE(), 103) AS '103',
6 CONVERT(VARCHAR, GETDATE(), 104) AS '104',
7 CONVERT(VARCHAR, GETDATE(), 105) AS '105',
8 CONVERT(VARCHAR, GETDATE(), 106) AS '106',
9 CONVERT(VARCHAR, GETDATE(), 107) AS '107',
10 CONVERT(VARCHAR, GETDATE(), 108) AS '108',
11 CONVERT(VARCHAR, GETDATE(), 109) AS '109',
12 CONVERT(VARCHAR, GETDATE(), 100) AS '100',
13 CONVERT(VARCHAR, GETDATE(), 120) AS '120',
14 CONVERT(VARCHAR, GETDATE(), 121) AS '121',
15 CONVERT(VARCHAR, GETDATE(), 126) AS '126',
16 CONVERT(VARCHAR, GETDATE(), 130) AS '130';
17
18
19 --创建uniqueidentifier类型的唯一值
20 SELECT NEWID() AS Id
21 --ISNULL(c1,c2)
22 SELECT ISNULL(ParentId,'########'),* FROM dbo.Module
23 --isdate(exp)判断exp是否为有效日期
24 SELECT ISDATE('2018-12-11 12:2:1')
25 --ISNUMERIC(c1)判断c1是否是数字,是则返回1,否返回0
26 SELECT ISNUMERIC('1') 是,ISNUMERIC('a') 否
4.流程控制语句
1 --begin...end语句块
2 BEGIN
3
4 SELECT *
5 FROM dbo.Role;
6
7 END;
8 --if...else语句
9 IF 1 > 4
10 SELECT *
11 FROM dbo.Role;
12 ELSE
13 SELECT *
14 FROM dbo.FlowInstance;
15 --简单case表达式
16 SELECT CASE ActivityType WHEN 2 THEN 'qqq' ELSE 'www' END ss ,* FROM dbo.FlowInstance
17 --搜索case表达式
18 SELECT CASE WHEN ActivityType>2 THEN 'qqq'ELSE 'www' END ss ,* FROM dbo.FlowInstance
19
20 --waitfor语句
21 WAITFOR DELAY '00:00:03';
22 SELECT *
23 FROM dbo.Role;
24 --在下午八点查询输出
25 WAITFOR TIME '20:00:00';
26 SELECT *
27 FROM dbo.Form;
28 --while语句
29 DECLARE @sum INT,
30 @i INT;
31 SET @i = 0;
32 SET @sum = 0;
33 WHILE @i <= 100
34 BEGIN
35 SET @i = @i + 1;
36 IF ((@i % 2) = 0)
37 CONTINUE;
38 SET @sum = @sum + @i;
39 IF (@i > 50)
40 BREAK;
41 END;
42 PRINT '1-50中的奇数和为' + CONVERT(CHAR(6), @sum);