SQL函数

SQL函数

聚合函数

聚合函数对一组值进行计算并返回单一的值，通常聚合函数会与SELECT语句的GROUP BY子句一同使用，在与GROUP BY子句使用时，聚合函数会为每一个组产生一个单一值，而不会为整个表产生一个单一值。

SUM(求和)函数

SUM函数返回表达式中所有值的和或仅非重复值的和。SUM 只能用于数字列。空值将被忽略。
语法如下：
SUM ( [ ALL | DISTINCT ] expression )

AVG(平均值)函数

AVG函数返回组中各值的平均值。将忽略空值。
语法如下：
AVG ( [ ALL | DISTINCT ] expression )

MIN(最小值)函数

MIN函数返回表达式中的最小值。
语法如下：
MIN ( [ ALL | DISTINCT ] expression )

MAX(最大值)函数

MAX函数返回表达式的最大值。
语法如下：
MAX ( [ ALL | DISTINCT ] expression )

COUNT(统计)函数

COUNT函数返回组中的项数。COUNT 返回 int 数据类型值。
语法如下：
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

DISTINCT(取不重复记录)函数

DISTINCT函数，对指定的集求值，删除该集中的重复元组，然后返回结果集。
语法如下：
Distinct(Set_Expression)

HAVING(查询重复记录)函数

HAVING函数，查询数据表中的重复记录，可以借助HAVING子句实现，该子句用来指定组或聚合的搜索条件。HAVING子句只能与SELECT语句一起使用，而且，它通常在GROUP BY子句中使用。

HAVING子句语法如下：
[ HAVING <search condition> ]

数学函数

ABS(绝对值)函数

ABS函数返回数值表达式的绝对值。
语法如下：
ABS(numeric_expression)

PI(圆周率)函数

PI函数返回 PI 的常量值。
语法如下：
PI ( )
返回类型：float型

POWER(乘方)函数

POWER函数返回对数值表达式进行幂运算的结果。Power 函数的计算结果必须为整数。
语法如下：
POWER(numeric_expression,power)

RAND(随机浮点)函数

RAND函数返回从 0 到 1 之间的随机 float 值。
语法如下：
RAND ( [ seed ] )

ROUND(四舍五入)函数

ROUND函数返回一个数值，舍入到指定的长度或精度。
语法如下：
ROUND ( numeric_expression , length [ ,function ] )

SQUARE(平方)函数

SQUARE（平方）函数
SQUARE函数返回数值表达式的平方。语法说明如下：
SQUARE(numeric_expression)

SQRT(平方根)函数

SQRT（平方根）函数
SQRT函数返回数值表达式的平方根。语法如下：
SQRT(numeric_expression)

三角函数

COS函数

返回指定表达式中以弧度表示的指定角的三角余弦。
语法如下：
COS ( float_expression )

COT函数

COT函数返回指定的 float 表达式中所指定角度（以弧度为单位）的三角余切值。
语法如下：
COT ( float_expression )

SIN函数

SIN函数以近似数字 (float) 表达式返回指定角度（以弧度为单位）的三角正弦值。
语法如下：
SIN ( float_expression )

TAN函数

TAN函数返回输入表达式的正切值。
语法如下：
TAN ( float_expression )

字符串函数

字符串函数作用于char、varchar、binary和varbinary数据类型以及可以隐式转换为char或varchar的数据类型。通常字符串函数可以用在SQL语句的表达式中。

ASCII(获取ASCII码)函数

ASCII函数返回字符表达式中最左侧的字符的 ASCII 代码值。
语法如下：
ASCII ( character_expression )

CHARINDEX函数

CHARINDEX函数返回字符串中指定表达式的起始位置（如果找到）。搜索的起始位置为 start_location。
语法如下：
CHARINDEX ( expression1 ,expression2 [ , start_location ] )

LEFT函数

LEFT函数返回字符串中从左边开始指定个数的字符。
语法如下：
LEFT ( character_expression , integer_expression )

RIGHT函数

RIGHT函数返回字符表达式中从起始位置（从右端开始）到指定字符位置（从右端开始计数）的部分。
语法如下：
RIGHT(character_expression,integer_expression)

LEN函数

LEN函数返回字符表达式中的字符数。如果字符串中包含前导空格和尾随空格，则函数会将它们包含在计数内。LEN 对相同的单字节和双字节字符串返回相同的值。
语法如下：
LEN(string_expression)

REPLACE函数

REPLACE函数将表达式中的一个字符串替换为另一个字符串或空字符串后，返回一个字符表达式。语法如下：
REPLACE(character_expression,searchstring,replacementstring)

REVERSE函数

REVERSE函数按相反顺序返回字符表达式。
语法如下：
REVERSE(character_expression)

STR函数

STR函数返回由数字数据转换来的字符数据。语法如下：
STR ( float_expression [ , length [ , decimal ] ] )

SUBSTRING函数

SUBSTRING函数为字符表达式、二进制表达式、文本表达式或图像表达式的一部分。
语法如下：
SUBSTRING ( value_expression ,start_expression , length_expression )

日期时间函数

日期和时间函数主要用来操作datetime、smalldatetime类型的数据，日期和时间函数执行算术运行与其他函数一样，也可以在SQL语句的SELECT、WHERE子句以及表达式中使用。

GETDATE函数

GETDATE函数返回系统的当前日期。GETDATE 函数不使用参数。
注意：
​ GETDATE 函数的返回结果的长度为 29 个字符。
语法如下：
GETDATE()

DAY函数

DAY函数返回一个整数，表示日期的“日”部分。
语法如下：
DAY(DATE)

MONTH函数

MONTH函数返回一个表示日期中的“月份”日期部分的整数。
语法如下：
MONTH(DATE)

YEAR函数

YEAR函数用于返回指定日期的年份。
语法如下：
MONTH(DATE)

DATEDIFF函数

DATEDIFF函数用于返回日期和时间的边界数。
语法如下：
DATEDIFF (datepart,startdate,enddate)

DATEADD函数

DATEADD函数将表示日期或时间间隔的数值与日期中指定的日期部分相加后，返回一个新的 DT_DBTIMESTAMP 值。number 参数的值必须为整数，而 date 参数的取值必须为有效日期。
语法如下：
DATEADD(datepart, number, date)

转换函数

在SQL Server中数据类型转换分为两种，分别如下：
隐性转换：SQL Server自动处理某些数据类型的转换。例如，如果比较char和datetime表达式、smallint和int表达式、或不同长度的char表达式，SQL Server可将它们自动转换，这种转换称为隐性转换，对这些转换不必使用CAST函数。
显式转换：显式转换是指CAST和CONVERT函数，CAST和CONVERT函数将数值从一种数据类型（局部变量、列或其他表达式）转换到另一种数据类型。
说明：
​ 隐性转换对用户是不可见的，SQL Server自动将数据从一种数据类型转换成另一种数据类型。例如，如果一个smallint 变量和一个int变量相比较，这个smallint变量在比较前即被隐性转换成int变量。

CAST函数

CAST函数用于将某种数据类型的表达式显示转换为另一种数据类型。
语法如下：
CAST (expression AS data_type[(length)])

CONVERT函数

CONVERT函数与CAST函数的功能相似。该函数不是一个ANSI标准SQL函数，它可以按照指定的格式将数据转换为另一种数据类型。
语法如下：
CONVERT (data_type[ (length) ],expression [, style])

元数据函数

元数据函数描述了数据的结构和意义，它主要用于返回数据库中的相应信息，其中包括：
返回数据库中数据表或视图的个数和名称。
返回数据表中数据字段的名称、数据类型、长度等描述信息。
返回数据表中定义的约束、索引、主键或外键等信息。

COL_LENGTH函数

COL_LENGTH函数用于返回列的定义长度。
语法如下：
COL_LENGTH ( ‘table’ , ‘column’ )

COL_NAME函数

COL_NAME函数根据指定的对应表标识号和列标识号返回列的名称。
语法如下：
COL_NAME ( table_id , column_id )

DB_NAME函数

DB_NAME函数返回数据库名称。
语法如下：
DB_NAME ( [ database_id ] )

GROUPING SETS

GROUPING SETS、ROLLUP和CUBE运算符是GROUP BY 子句的扩展。运用ROLLUP或CUBE运算符可以生成全部分组，而使用GROUPING SETS运算符可以仅指定所需的分组。

有约束冲突却不提示错误信息

下面代码是用来检测约束冲突的，如果与check约束冲突，@@ERROR被赋值547，并提示错误信息。但使用@@ERROR却检测不到约束冲突。
IF @@ERROR = 547
print ‘A check constraint violation occurred’
​ 答：@@ERROR返回的是与它紧挨着的语句的执行情况。如果IF @@ERROR = 547与被检测语句之间有其他语句（如GO），则@@ERROR不会检测到正确的值。

SELECT * FROM CLASSINFOR
--DISTINCT:指定 SUM 返回唯一值的和。
--总和
SELECT SUM( DISTINCT (AGE)) FROM CLASSINFOR
--平均值
SELECT AVG(AGE) FROM CLASSINFOR
--最小值
SELECT MIN(AGE) FROM CLASSINFOR
--最大值
SELECT MAX(AGE) FROM CLASSINFOR
--总数
SELECT COUNT(*) FROM CLASSINFOR
--HAVING:查询数据表中的重复记录
SELECT AGE,COUNT(AGE) FROM CLASSINFOR
GROUP BY AGE
HAVING AGE>1
--绝对值
SELECT ABS(3-5) AS '绝对值'
--圆周率
SELECT PI() AS '圆周率'
--乘方
SELECT POWER(2,5) AS '乘方'
--RAND函数返回从 0 到 1 之间的随机 float 值
SELECT RAND(100)
--ROUND:四舍五入
SELECT ROUND(521.525,2)
--平方
SELECT SQUARE(2) AS '2的平方'
--平方根
SELECT SQRT(9) AS '9的平方根'
--COS函数:返回指定表达式中以弧度表示的指定角的三角余弦。
DECLARE @A FLOAT
SET @A=30
SELECT CONVERT(VARCHAR,COS(@A)) AS COS
GO
--COT函数:返回指定的 float 表达式中所指定角度（以弧度为单位）的三角余切值
DECLARE @A FLOAT
SET @A=10
SELECT CONVERT(VARCHAR,COT(@A)) AS COT
GO
--SIN函数:以近似数字 (float) 表达式返回指定角度（以弧度为单位）的三角正弦值
DECLARE @A FLOAT
SET @A=16
SELECT CONVERT(VARCHAR,SIN(@A)) AS SIN
GO
--TAN函数返回输入表达式的正切值
DECLARE @A FLOAT
SET @A=16
SELECT CONVERT(VARCHAR,TAN(@A)) AS TAN
GO
--ASCII
--DECLARE @POSITION INT,@STRING CHAR(3)
--SET @POSITION=1
--SET @STRING='nxt'
--WHILE @POSITION<=DATALENGTH(@STRING)
--BEGIN
--SELECT ASCII(SUBSTRING(@STRING,@POSITION,1))
--END
--CHARINDEX函数返回字符串中指定表达式的起始位置（如果找到）。搜索的起始位置为 start_location
SELECT CHARINDEX('跳舞','跳舞') AS '起始位置' FROM CLASSINFOR WHERE ID=10000
--LEFT函数返回字符串中从左边开始指定个数的字符。
SELECT ID,NAME FROM CLASSINFOR
SELECT LEFT(NAME,1) AS '姓氏',
COUNT (LEFT(NAME,1)) AS '数量'
FROM CLASSINFOR GROUP BY LEFT(NAME,1)
--RIGHT:返回字符表达式中从起始位置（从右端开始）到指定字符位置（从右端开始计数）的部分
SELECT ID,NAME FROM CLASSINFOR
SELECT RIGHT(ID,3) AS 'ID',NAME,SEX FROM CLASSINFOR
-- LEN函数返回字符表达式中的字符数。如果字符串中包含前导空格和尾随空格，则函数会将它们包含在计数内。
--LEN对相同的单字节和双字节字符串返回相同的值
SELECT LEN(23456) AS '字符个数'
SELECT LEN('梦不落雨林-张艺兴') AS '字符个数'
--REPLACE函数将表达式中的一个字符串替换为另一个字符串或空字符串后，返回一个字符表达式
SELECT REPLACE('ZHANGLAY','LAY','YIXING') AS '替换结果'
--REVERSE函数按相反顺序返回字符表达式
SELECT REVERSE('兴艺张') AS '反转结果'
SELECT REVERSE('YAL') AS '反转结果'
--STR函数返回由数字数据转换来的字符数据
SELECT STR(123.45)
SELECT STR(123.43,8,1)
--SUBSTRING函数为字符表达式、二进制表达式、文本表达式或图像表达式的一部分
SELECT HOBBY,SUBSTRING(Hobby,2,2) AS '编号' FROM CLASSINFOR
--GETDATE函数返回系统的当前日期。GETDATE 函数不使用参数
SELECT GETDATE() AS '当前时间'
--DAY函数返回一个整数，表示日期的“日”部分
SELECT DAY('2019-2-22') AS 'DAY'
SELECT DAY('2019/2/22') AS 'DAY'
SELECT DAY(GETDATE()) AS 'DAY'
--MONTH函数返回一个表示日期中的“月份”日期部分的整数
SELECT MONTH(GETDATE()) AS 'MONTH'
--YEAR函数用于返回指定日期的年份
SELECT YEAR(GETDATE()) AS 'YEAR'
--DATEDIFF函数用于返回日期和时间的边界数
SELECT DATEDIFF(MONTH,'2019-01-01','2019-02-01')AS '时间差'
SELECT DATEDIFF(DAY,'2019-01-01','2019-02-01')AS '时间差'
--DateADD函数
SELECT GETDATE() AS 当前系统时间
SELECT DATEADD(MONTH,1,GETDATE()) AS 下月时间
--转换函数
--CAST函数用于将某种数据类型的表达式显示转换为另一种数据类型
SELECT CAST('ZHNAGYIXING'AS NVARCHAR(8)) AS 结果
--Convert函数:可以按照指定的格式将数据转换为另一种数据类型
SELECT 
GETDATE() AS G,
CAST(GETDATE()AS datetime)AS D,
CONVERT(datetime,GETDATE(),126) AS C
--元数据函数描述了数据的结构和意义，它主要用于返回数据库中的相应信息
--COL_LENGTH函数用于返回列的定义长度
USE DB_0221
CREATE TABLE MYTABLE
(
USERID INT,
USERNAME VARCHAR(20),
USERSEX NVARCHAR(2),
USERBIRTHDAY DATETIME,
USERADDRESS TEXT
)
GO
SELECT 
COL_LENGTH('MYTABLE','USERID') AS 'INT类型长度',
COL_LENGTH('MYTABLE','USERNAME') AS 'VARCHAR类型长度',
COL_LENGTH('MYTABLE','USERSEX') AS 'NVARCHAR类型长度',
COL_LENGTH('MYTABLE','USERBIRTHDAY') AS 'DATETIME类型长度',
COL_LENGTH('MYTABLE','USERADDRESS') AS 'TEXT类型长度'
GO
--COL_NAME函数根据指定的对应表标识号和列标识号返回列的名称
USE DB_0221
GO
--当SET NOCOUNT为ON时,不返回计数(表示受Transact-SQL语句影响的行数)
--当SET NOCOUNT为OFF时,返回计数(默认为OFF)
SET NOCOUNT OFF
GO
SELECT COL_NAME(OBJECT_ID('ID'),1)
GO
--DB_NAME函数返回数据库名称
SELECT DB_NAME() AS [CURRENT DATABASE]
GO
--查询年龄最大的和最小的
SELECT ID,NAME,AGE FROM CLASSINFOR WHERE AGE=(SELECT MAX(AGE) FROM CLASSINFOR)
SELECT ID,NAME,AGE FROM CLASSINFOR WHERE AGE=(SELECT MIN(AGE) FROM CLASSINFOR)
--GROUPING SETS
--USE DB_0221
--GO
--SELECT ID,NAME,HOBBY AS 年龄 FROM CLASSINFOR GROUP BY GROUPING SETS(HOBBY,NAME)