【SQL Server】（四）日期函数 + 数学函数 + 字符函数 + 判断函数 +转换函数+ 视图

【SQL Server】（四）日期函数 + 数学函数 + 字符函数 + 判断函数+转换函数 + 视图

一、日期函数

如果希望使查询简单且更易维护，那么请不要在日期中使用时间部分

--（1）当前时间-GETDATE()：返回当前日期和时间
select GETDATE() as CurrentDateTime; --2021-08-22 13:09:06.147
--（2）截取时间-DATEPART(datepart, date)：返回日期/时间的单独部分
select DATEPART(yyyy, GETDATE()) as year_date,  --2021
       DATEPART(yy, GETDATE()) as year2_date,  --2021
       DATEPART(mm, GETDATE()) as month_date,  --8
       DATEPART(dd, GETDATE()) as day_date;    --22
--（3）增减时间-DATEADD(datepart, number, date)：日期中添加或减去指定的时间间隔
select DATEADD(MM, 3, GETDATE()) as monthAdd_date, --2021-11-22 13:35:14.413
       DATEADD(day, 3, GETDATE()) as dayAdd_date, --2021-08-25 13:35:14.413
       DATEADD(y, 3, GETDATE()) as dayAdd2_date,  --2021-08-25 13:35:14.413
       DATEADD(d, 3, GETDATE()) as dayAdd3_date;  --2021-08-25 13:35:14.413
--（4）时间差值-DATEDIFF(datepart, startdate, enddate)：返回两个日期之间的时间
select DATEDIFF(DAY, '2021-7-12', '2021-8-22') as diff_day, -- 41
       DATEDIFF(yy, '2021-7-12', '2021-8-22') as diff_year, -- 0
       DATEDIFF(m, '2021-9-12', '2021-8-22') as diff_month; -- -1

二、数学函数

--1、avg():平均数
select avg(scores) from score;
--2、count()：计数
select COUNT(sid) from score 
where scores > 90;
--下面四种写法查询结果均是表格的行数
select COUNT(*) from score;
select COUNT(0) from score;
select COUNT(1) from score;
select COUNT(2) from score;
--3、FIRST(col_name)：返回指定字段中第一个记录的值
-----LAST(col_name)：返回指定字段中最后一个记录的值
-----注意：SQL Server 2008不支持first()、last(),2012之后才会支持。
select FIRST(id) from student;
select Last(id) from student;
--4、MAX(col_name)：返回最大值
select MAX(scores) from score;
--5、MIN(col_name)：返回最小值
select MIN(scores) from score;
--6、SUM(col_name)：求和
select SUM(scores) from score;
--7、ROUND(col_name, decimals)：四舍五入得到指定位数的小数
select ROUND(AVG(scores), 2) from score;
--8、CEILING() 返回大于或等于所给数字表达式的最小整数
SELECT CEILING($123.45), CEILING($0.0) --124.00  0.00
--9、FLOOR() 返回小于或等于所给数字表达式的最大整数
SELECT FLOOR(123.45), FLOOR(-123.45) --123 -124

三、字符函数

SQLServer中连接符号为“+”

 

--1、ASSCII(str) 返回字符串中最左侧的字符的 ASCII 码
-----注：字符串或字符只能用单引号引住
    SELECT ASSCII('abc') --97
    SELECT ASCII(1) --49
--2、CHAR(整数表达式) 把ASCII码转换成字符
   介于 0 和 255 之间的整数。
   如果该整数表达式不在此范围内，将返回 NULL 值。
    SELECT CHAR(100) --d
    SELECT CHAR(256) --NULL
--3、CHARINDEX(expression1, expression2, startIndex),
--如果能够从expression2字符串中查找到expression1字符串则返回expression1在 expression2出现的位置；反之，返回0
    SELECT CHARINDEX('abc','abcdabcd') --1
    SELECT CHARINDEX('abc','abcdabcd', 4) --5
--4、PATINDEX('%要匹配的正则表达式%', 要匹配的字符串)，
-----注：%%一定要加上，为固定用法，如果匹配上了就会返回 所在位置，如果没能匹配上就返回 0
where PATINDEX('%[^0-9A-Za-z吖-座]%', PAY_ACCT_NAME)>0 

    
--1、UPPER(col_name)
-----SQL Server中没有UCASE()这个函数，取而代之的是UPPER()
select UPPER(name) from student;
--2、LOWER(col_name)
-----SQL Server中没有LCASE()这个函数，取而代之的是LOWER()
select LOWER(name) from student;
--3、SUBSTRING(col_name, start, [, length])：用于从文本字符中提取字符
-----SQL SERVER 2008中没有MID()，可用SUBSTRING()代替
select substring(name, 1, 1) from student;
--4、FORMAT(col_name, format)：格式化字段
-----SQL SERVER 2008中没有FORMAT()这个函数,日期的话可以用convert()转换
select FORMAT(NOW(),'YYYY-MM-DD');
--5、rtrim()、ltrim() 可去掉字段值右侧、左侧多余的空格。
--注：SQL Server 2008中 trim()不识别
--8、LEFT(s, n)、RIGHT(s, n)
--LEFT(s, n):从字符串左边取n个字符
--RIGHT(s, n):从字符串右边取n个字符
  SELECT LEFT('赵丽',1) FROM [dbo].[student2] --赵
--9、REPLACE(str, oldstr, newstr) 替换字符串
  SELECT REPLACE('abcaabbc','bc', 'ff') FROM [dbo].[student2] --affaabff
--10、REVERSE(str) 返回指定字符串反转后的新字符串
  SELECT REVERSE('abcaabbc') FROM [dbo].[student2] --cbbaacba
--11、LEN(str)返回str的长度
  SELECT LEN('abcaabbc') --8

 

四、判断函数

--1、SQL Server--> ISNULL(col_name, default_value)                            
select id, name, ISNULL(sex,'男') as sex from student;

--2、ISNUMERIC()，判断是否为数字类型，包括小数。
-----是仅SQL Server特有的函数。 如果是数字类型的，会返回 1，否则会返回 0
-----注意：像'.56'或是'78.'这样的他也会算作数字类型，所以想要判断得更严谨，还需要加上 XXX not like '.%' and XXX not like '%.'
where ISNUMERIC(PAY_ACCT_NAME)=1

五、转换函数

1、CONVERT()

--1、转换时间-CONVERT(data_type(length), data_to_be_converted, style): 把日期转换为新数据类型的通用函数。
select CONVERT(VARCHAR(10), GETDATE(), 111) as convert1; --2021/08/22

--2、CONVERT(NUMERIC(m, n), number) ，n默认值为0，注：m+n <= 38
--    可以将number转换成含有n个小数的 总长为m的数字
SELECT CONVERT(NUMERIC(38,0), (123000.3450*1.0/10000)) --12

2、CAST()

--CAST(a AS type) 将a转换成type类型的数据             
SELECT CAST('9.0' AS DECIMAL)         --9
SELECT CAST('9.2' AS DECIMAL(10, 2))  --9.20

六、视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

--1、创建视图                                                              
create view [view_student] as 
select id, name, birthday, sex from student;
select * from [view_student];
--2、更新视图                                                              
--create or replace as在SQL Server 2008中不好使,得换成alter as 
alter view [view_student] as 
select id, name from student;
--3、撤销视图                                                               
drop view view_student;