SQLServer笔记

一、ROW_NUMBER（）函数

​ 说明：返回结果集分区内行的序列号，每个分区的第一行从1开始

二、常用行为：列转行

create table course  
(  
    id int identity(1,1),--递增1,  
    stuNo varchar(50),  
    courseName varchar(50),  
    courseScore decimal   
)  
insert into course values('02','思想政治','85.5'),  
                         ('02','数学','70'),  
                         ('02','语文','80'),  
                         ('02','物理','90'),  
                         ('02','化学','65'),  
                         ('02','英语','96')  
                           
insert into course values('03','思想政治','60'),  
                         ('03','数学','65'),  
                         ('03','语文','84'),  
                         ('03','物理','70'),  
                         ('03','化学','76'),  
                         ('03','英语','54')   
select * from course

select ROW_NUMBER() over(order by stuNo asc) as ID,stuNo as '学号',
max(case courseName when '思想政治' then courseScore else 0 end) as '思想政治',
max(case courseName when '数学' then courseScore else 0 end) as '数学',
max(case courseName when '语文' then courseScore else 0 end) as '语文',
max(case courseName when '物理' then courseScore else 0 end) as '物理',
max(case courseName when '化学' then courseScore else 0 end) as '化学',
max(case courseName when '英语' then courseScore else 0 end) as '英语'
from course group by stuNo

--行转列练习
create table course  
(  
    id int identity(1,1),--递增1,  
    stuNo varchar(50),  
    courseName varchar(50),  
    courseScore decimal,
    stuName varchar(50)   
)  
insert into course values('01','思想政治','85.5','小红'),  
                         ('01','数学','70','小红'),  
                         ('01','语文','80','小红'),  
                         ('01','物理','90','小红'),  
                         ('01','化学','65','小红'),  
                         ('01','英语','96','小红')  

insert into course values('02','思想政治','85.5','小黑'),  
                         ('02','数学','70','小黑'),  
                         ('02','语文','80','小黑'),  
                         ('02','物理','90','小黑'),  
                         ('02','化学','65','小黑'),  
                         ('02','英语','96','小黑')  
                           
insert into course values('03','思想政治','60','小兰'),  
                         ('03','数学','65','小兰'),  
                         ('03','语文','84','小兰'),  
                         ('03','物理','70','小兰'),  
                         ('03','化学','76','小兰'),  
                         ('03','英语','54','小兰') 
                         
select * from course
--方法一
select stuName as '姓名',
max(case courseName when '思想政治' then courseScore else 0 end) as '思想政治',
max(case courseName when '数学' then courseScore else 0 end) as '数学',
max(case courseName when '语文' then courseScore else 0 end) as '语文',
max(case courseName when '物理' then courseScore else 0 end) as '物理',
max(case courseName when '化学' then courseScore else 0 end) as '化学',
max(case courseName when '英语' then courseScore else 0 end) as '英语'
from course1 group by stuName                         

1、使用PIVOT函数实现行专列

格式如下
PIVOT(<聚合函数>([聚合列值]) FOR [行转列前的列名] IN([行转列后的列名1],[行转列后的列名2],[行转列后的列名3],.......[行转列后的列名N]))

select ROW_NUMBER() over(order by a.stuNo asc) as ID,a.stuNo as '学号',
MAX(a.思想政治) as '思想政治',
MAX(a.数学) as '数学',
MAX(a.语文) as '语文',
MAX(a.物理) as '物理',
MAX(a.化学) as '化学',
MAX(a.英语) as '英语'
from course pivot(max(courseScore) for courseName in(思想政治,数学,语文,物理,化学,英语))a 
group by a.stuNo

三、常用行为：行转列

create table course1  
(  
    
    ID int identity(1,1), 
    学号 varchar(50),  
    思想政治 int,  
    数学 int, 
    语文 int,  
    物理 int,  
    化学 int,  
    英语 int
)  
go
select * from course1
insert into course1 
select '02',86,70,80,90,65,96
union all 
select '03',60,65,84,70,76,54
go
 
select * from course1

1、使用UNPIOVT函数实现列转行

UNPIVOT([转换为行的列值在转换后对应的列名] for [转换为行的列名在转换后对应的列名] in ([转换为行的列1],[转换为行的列2],[转换为行的列3],...[转换为行的列N]))


select ROW_NUMBER() over(order by a.学号 asc) as id,a.学号 as stuNo,a.courseName,a.courseScore from course1 unpivot(courseScore for courseName in(思想政治,数学,语文,物理,化学,英语))a

四、常用函数

-- 字符串截取 SUBSTRING(字符串,第几位开始截取,截取多少位字符)
select SUBSTRING('11110020210409',7,8)
-- 字符串转换数字
-- CAST(数值 AS 转换之后的数据类型)
select CAST('123' as int);
-- CONVERT(转换之后的数据类型,数值)
select CONVERT(int,'123');
-- DATEDIFF(计算相差单位,开始日历,结束日历) --计算相差间隔
SELECT DATEDIFF(DAY, '3-30-2021', '4-30-2021') 相差天
SELECT DATEDIFF(MONTH, '3-30-2021', '4-30-2021') 相差月
SELECT DATEDIFF(YEAR, '3-30-2020', '4-30-2021') 相差年
-- DATEADD(时间单位,要加的值，日期) --计算相加时间后是几年几月几号
SELECT DATEADD(DAY,30,'4-19-2021')
-- DATEPART(时间单位,日期) 返回那一天或那一月或那一年的值
SELECT DATEPART(MONTH,'4-19-2021')
SELECT DATENAME(MONTH,'4-19-2021')
-- 转小写
SELECT LOWER('HHHAAABBB')
--转大写
SELECT UPPER('abcdefg')
--返回字符的总长度
SELECT LEN('ABCD')
--将指定字符串替换成别的字符
SELECT REPLACE('I aint','I','You')
--创建 uniqueidentifier[唯一标识符] 类型的唯一值
--例如6C236D34-ADE7-4DC9-82E4-86AC70A5A213
SELECT NEWID()
--随机生成时间在一个时间段内，精确到秒
SELECT DATEADD(SECOND,ABS(CHECKSUM(NEWID())) %(DATEDIFF(SECOND,'起始时间','结束时间'))+1,'起始时间')
--CHECKSUM 函数返回按照表的某一行或一组表达式计算出来的校验和值。 使用 CHECKSUM 来生成哈希索引
SELECT CHECKSUM(NEWID())
--返回一个介于 0 到 1（不包括 0 和 1）之间的伪随机 float 值
SELECT RAND()
--返回指定数值表达式的绝对值（正值）的数学函数
SELECT ABS(-1)
--此函数返回大于或等于指定数值表达式的最小整数
SELECT CEILING()
--例如：SELECT CEILING($123.45) 结果 124.00