#头条创作挑战赛#
聚合函数
聚合:就是将多条记录的数据变为一条记录数据
1:count:统计表中记录数据的总个数(此集合函数中只能是*或一个指定列名)
USE School;
--1 聚合函数
--1.1 count:统计表中记录数据的总个数
--统计所有列的总个数
SELECT COUNT(*) AS 所有列 FROM student
--统计指定列的总个数
SELECT COUNT(SGender) AS 指定列 FROM student
--对照数据
SELECT * FROM student 注意事项:
1)统计所有列时即使所有数据都是null,也会得出表中记录的总个数
2)统计指定列时如果,该列中有数据为null时,不会统计有null值的列
统计总个数
2)avg:获取集合函数中的平均数(集合函数中只能填写数值类的列名)
--获取年龄列的平均数
SELECT 平均年龄=AVG(SAge) FROM student
如果填写 SPhone 就会报错
SELECT 平均年龄=AVG(SPhone) FROM student 注意:此函数不会计算null值列
假设:表中数据共有15条(有两条是null值)此函数只会得到13条的的平均数,如13条总和为195,平均数为: 195/13=15 而不是 195/15=13
获取年龄列的平均数
3)sum:获取集合函数的计算总和(集合函数中只能填写数值类的列名,不会计算null值)
--获取年龄列的总和
SELECT 年龄总和=sum(SAge) FROM student 获取年龄列的总和
4)max/min:获取集合函数中的最大值与最小值(集合函数中只能填写数值类的列名)
SELECT
最大年龄=max(SAge),
最小年龄=MIN(SAge)
FROM student 获取年龄列中的最大值与最小值
注意:除了count(*)外,指定列的集合函数都不会将null值计算在内
类型转换函数
在数据库中有时需要将一种数据类型转换为另一种数据类型才能使用(如文本类型排序)
转换函数共有两种:1) cast ( 列名 as 指定类型) 2)convert ( 指定类型, 列名,[格式])
--创建一张示例表
CREATE TABLE T1
(
TId INT IDENTITY(1,1) PRIMARY KEY,--自动编号
TName VARCHAR(10),--名称
TNumber VARCHAR(10)--文本类型的编号
)
--插入数据
INSERT INTO T1(TName,TNumber)
VALUES(N'张三',N'1')--单行插入
--多行插入
INSERT INTO T1(TName,TNumber)
SELECT N'张三1',N'21' UNION
SELECT N'李四',N'35' UNION
SELECT N'王五',N'16' UNION
SELECT N'马六',N'25' UNION
SELECT N'赵四',N'7'
--查询以 TNumber 排序后的数据
SELECT * FROM T1 ORDER BY TNumber 文本类型的数字升序 排序
为使文本类型的数字按照数值类型的数字排序规则,需要将其转换类型
--查询以 TNumber 排序后的数据
SELECT * FROM T1 ORDER BY TNumber
--排序前将要排序的列转换为数值类型如int
--升序排序
SELECT * FROM T1 ORDER BY CAST(TNumber AS INT)
--降序排序
SELECT * FROM T1 ORDER BY CONVERT(INT,TNumber) DESC 转换类型后的排序数据
空值处理函数
isnull(列名,填写与列名相同类型的字符)用于处理空值情况的函数
--将赵构的SGender显示为0,SPhone显示为空号
SELECT
SName AS N'姓名',
ISNULL(SGender,0) AS N'性别',
ISNULL(SPhone,N'空号') AS N'手机号'
FROM Student
WHERE SId=22 空值函数
字符串函数
4.1获取字符串长度:1)len()返回字符个数(中英文都是一个) 2)datalength()返回字节个数(系统函数; 中文2个,英文一个)
--4字符串函数
--获取字符串长度
--4.1 len()返回字符个数(中英文都是一个)
--4.2 datalength()返回字节个数(中文2个,英文一个)
--在结果窗口中显示结果
SELECT LEN('张三zss') AS N'总长度'
--在消息窗口中显示结果
--返回字符数
PRINT '字符总长度为: '+CAST(len('张三zss') AS VARCHAR(10))
--返回字节数
PRINT '字节总长度为: '+CAST(datalength('张三zss') AS VARCHAR(10))
--查询出SName中是两个字的数据
--使用len()函数
SELECT * FROM Student
WHERE LEN(SName)=2
--使用datalength()
SELECT * FROM Student
WHERE datalength(SName)=4 获取字符串长度
4.2 转换大小写:转换大写:upper() 转换小写lower()一般用于汉字转拼音列上
PRINT upper('Welcome to China')--WELCOME TO CHINA
PRINT lower('Welcome to China')--welcome to china 4.3 去除空格: ltrim()去掉左侧空格 rtrim()去掉右侧空格
--4.3 去除字符串外侧的空格
PRINT '666'+LTRIM(' Welcome to China ')+'666'
PRINT '666'+RTRIM(' Welcome to China ')+'666'
--去除两侧空格
PRINT '666'+LTRIM(RTRIM(' Welcome to China '))+'666' 4.4 截取字符串:left(字符串,个数)right(字符串,个数)从字符串的左/右开始截取指定个数
substring(字符串,从左开始第几个开始,截取字符的格式)
PRINT LEFT('abcdefg',3)
PRINT RIGHT('abcdefg',3)
--注意:下标从 1 开始
PRINT SUBSTRING('abcdefg',2,3) 转换大小写/去除空格/截取字符串的函数
4.5 替换函数 replace(原始字符串,将要被替换字符串,替换的字符串)
PRINT replace('abcdefg','bcd','123') 替换字符串函数
日期函数
5.1 getdate()函数,获取获取当前系统的时间(示例中是电脑时间)
--获取默认格式的当前时间
PRINT GETDATE()
--通过类型转换 获取指定格式的时间
PRINT CONVERT(VARCHAR(30),GETDATE(),20) 获取当前电脑的时间
日期转换中常用的显示格式(使用时输入下面对应格式的代码即可,可惜没有中国格式)
| 不带世纪数位 (yy) 1 | 带世纪数位 (yyyy) | Standard | 输入/输出 格式 |
| - | 0 或 1001、2 | datetime 和 smalldatetime 的默认值 | mon dd yyyy hh:miAM（或 PM） |
| 1 | 101 | 美国 | 1 = mm/dd/yy 101 = mm/dd/yyyy |
| 2 | 102 | ANSI | 2 = yy.mm.dd 102 = yyyy.mm.dd |
| 3 | 103 | 英国/法国 | 3 = dd/mm/yy 103 = dd/mm/yyyy |
| 4 | 104 | 德语 | 4 = dd.mm.yy 104 = dd.mm.yyyy |
| 5 | 105 | 意大利语 | 5 = dd-mm-yy 105 = dd-mm-yyyy |
| 6 | 1061 | - | 6 = dd mon yy 106 = dd mon yyyy |
| 7 | 1071 | - | 7 = Mon dd, yy 107 = Mon dd, yyyy |
| 8 或 24 | 108 | - | hh:mi:ss |
| - | 9 或 1091、2 | 默认格式 + 毫秒 | mon dd yyyy hh:mi:ss:mmmAM（或 PM） |
| 10 | 110 | USA | 10 = mm-dd-yy 110 = mm-dd-yyyy |
| 11 | 111 | 日本 | 11 = yy/mm/dd 111 = yyyy/mm/dd |
| 12 | 112 | ISO | 12 = yymmdd 112 = yyyymmdd |
| - | 13 或 1131、2 | 欧洲默认格式 + 毫秒 | dd mon yyyy hh:mi:ss:mmm（24 小时制） |
| 14 | 114 | - | hh:mi:ss:mmm（24 小时制） |
| - | 20 或 1202 | ODBC 规范 | yyyy-mm-dd hh:mi:ss（24 小时制） |
| - | 21、25 或 1212 | time、date、datetime2 和 datetimeoffset 的 ODBC 规范（用毫秒表示）默认值 | yyyy-mm-dd hh:mi:ss.mmm（24 小时制） |
| 22 | - | 美国 | mm/dd/yy hh:mi:ss AM（或 PM） |
| - | 23 | ISO8601 | yyyy-mm-dd |
| - | 1264 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm（无空格）6 |
| - | 1278、9 | 包括时区 Z 的 ISO8601 | yyyy-MM-ddThh:mm:ss.fffZ（无空格）6 |
| - | 1301、2 | 回历 5 | dd mon yyyy hh:mi:ss:mmmAM7 |
| - | 1312 | 回历 5 | dd/mm/yyyy hh:mi:ss:mmmAM |
5.2 dateadd(单位,时间量,基准时间) 增减时间函数(时间量正数增加/负数减少)
--5.2 dateadd(单位,时间量,基准时间) 增加时间函数
--常用单位:year/month/day/hour/minute/second(年月日时分秒)
--quarter/week/millisecond(季度/周/毫秒)
--在当前时间的基础上增加3个月
PRINT '增加后的时间: '+CONVERT(VARCHAR(30),DATEADD(MONTH,3,GETDATE()),20)
--在当前时间上减去3个月
PRINT '减少后的时间: '+CONVERT(VARCHAR(30),DATEADD(MONTH,-3,GETDATE()),20)
--其他单位增减时间的实现方式以此类推 应用实例:查询出入职超过5年的数据
--1 为 Student 表新增 入职日期 列
ALTER TABLE Student
ADD SDatetime DATETIME
--2 修改日期
UPDATE Student SET SDatetime = '2022-1-1'
--3 手动修改具体记录是日期
--查询出入职超过5年的数据
SELECT * FROM student
WHERE DATEADD(YEAR,5,SDatetime)<GETDATE() 根据入职日期查询数据
5.3 datediff(日期单位,开始日期,结束日期)获取两个日期的差值
--获取当前年龄
SELECT DATEDIFF(YEAR,'1998-1-1',GETDATE()) AS N'当前年龄'
--获取 student表中所有人的工作年限
SELECT
DATEDIFF(YEAR,SDatetime,GETDATE()) AS N'工作年限'
FROM student ORDER BY N'工作年限' DESC 获取年限数据
5.4 datepart(日期单位,时间)返回一个日期的特定部分(根据日期单位)
--也可以使用Month()year()day()hour()minute()second()等函数代替
PRINT DATEPART(YEAR,GETDATE())
PRINT YEAR(GETDATE())
PRINT MONTH(GETDATE())
PRINT day(GETDATE()) 获取给定日期的指定单位的日期部分