SqlServer内置函数和使用

聚合函数

-> MAX(字段)

-> MIN(字段)

-> AVG(字段)

•-> 在计算时，对于null的数据不计入总是

-> SUM(字段)

-> COUNT(字段)

•-> count(*)计算所有的列、count(字段)不计为null的列

  聚合函数会忽略为空的值

-> select的字段可以当做变量名来进行计算

•-> select (english+math)/2 as 平均分 form score

-> 计算时null表示未知，既不是最大，也不是最小，与null的运算结果也为null

 

select name, 
    AVG(score), 
    COUNT(score), 
    COUNT(*),
    MAX(score), 
    min(score),
    SUM(score)
from
    Score
where
    name = '张三'
group by
    name;
   
select
    name, 
    score,
    AVG(score) over()
from
    Score

 

 

字符串函数

LEN() ：计算字符串长度（字符的个数。）

datalength();//计算字符串所占用的字节数，不属于字符串函数。

LOWER() 、UPPER () ：转小写、大写

LTRIM()：字符串左侧的空格去掉

RTRIM () ：字符串右侧的空格去掉

LTRIM(RTRIM('         bb        '))

LEFT()、RIGHT()  截取取字符串

•SELECT LEFT('abcdefg',2) 结果为---ab,左边开始截取2个

 SUBSTRING(string,start_position,length)

-> 参数string为主字符串，start_position为子字符串在主字符串中的起始位置，该位子从1开始

-> length为子字符串的最大长度。

-> SELECT  SUBSTRING('abcdef111',2,3)结果为--bcd

 

-- 在数据库中存储了订单的编号D000134623321
-- 订单编号以D开头并有十位数字
select 
    'D' + right('0000000000' + id, 10) as id,
    name,
    price
from
(
    values
    ('123', 'ACER台式电脑', 13566),
    ('13', '九阳豆浆机', 400),
    ('9761', '南府电池', 10)
) as tbl(id, name, price)

 

 

 

日期函数

-> 获得系统当前时间：getdate()

•select getdate()

•当前是第几天：select day(getdate())

•当前的年份：select year(getdate())

•当前的月份：select month(getdate())

-> 在当前时间加上一个时间间隔

•dateadd(在哪个部分增加,增加多少,日期)

 

select datepart(Dw, GETDATE());
--周三,显示的是4 ????

--小时＋10
select GETDATE(), DATEADD(HH,10,GETDATE());
--天＋10
select GETDATE(), DATEADD(day,10,GETDATE());

 

l-> 判断两个日期的间隔

•datediff(单位,开始的时间，结束的时间)

-> 实例：将学生按年龄分组

select datediff(year,stuBirthdate,getdate()),count(stuId )
from Student group by datediff(year,stuBirthdate,getdate()) 
order by datediff(year,stuBirthdate,getdate()) 

select
    *
from 
    Student
where
    MONTH(stuBirthdate) = 6--6月份出生的
    and
    YEAR(GETDATE()) - YEAR(stuBirthdate) between 19 and 26;

select
    *
from 
    Student
where
        MONTH(stuBirthdate) = 6
    and
    YEAR(GETDATE()) - YEAR(stuBirthdate) in (19, 21, 23, 27);    

 --为了提高查询的效率，索引字段不应添加方法处理

-- 如果在stuBirthdate上添加了索引，但是如此查询(使用datediff方法)那么索引无效

 

-> 返回日期的特定部分

•datepart()

•示例：统计学生出生年份和数量（某一年的有多少个）

select datepart(year,stuBirthdate),count(*)
from student group by datepart(year,stuBirthdate)
order by datepart(year,stuBirthdate)

 

 len和datelength区别?

-> datalength()系统函数，计算字节数

-> len()函数，计算字符数

select ASCII('abc');--97
select ASCII('我');--206
select UNICODE('abc');--97
select UNICODE('我');--25105

select CHAR(97);--a
select NCHAR(25105);--我

--len 是字符数(不算空格),datelength是字节数
select LEN('1234567890'),LEN('一二三四五六七八九十');--10,10
select DATALENGTH('1234567890'), DATALENGTH('一二三四五六七八九十');--10,20
select LOWER('aAbBcC'), UPPER('aAbBcC');--小写,大写

select len('   123')                --6
select datalength(RTRIM('123   ')); --3
select LEN('123   ');                --3
select DATALENGTH('123   ');        --6

 

 

类型转换函数

 

CAST ( expression AS data_type)

CONVERT ( data_type, expression,[style])

Select ‘您的班级编号’+ 1  错误这里+是数学运算符

•SELECT FIdNumber,

•CAST(RIGHT(sNo,3) AS INTEGER) as 后三位的整数形式,

•CAST(RIGHT(sNo,3) AS INTEGER)+1 as 后三位加1,

•CONVERT(INTEGER,RIGHT(sNo,3))/2 as 后三位除以2

•FROM student

对日期的转换。转换成各种国家格式的日期。

•select convert(varchar(20),getdate(),104)

•Style的格式，查sql帮助。（输入convert函数查询）

•将日期转换为指定格式的字符串。日期→字符串