一、sqlwhen函数
1、sqlwhen函数用法:根据表达式的值进行一项或多项操作,其基本语法如下:
SELECT
...
,
CASE value_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_expression ]
END
...
2、sqlwhen函数实例:
SELECT
P.ProductID,
P.Name,
P.ListPrice,
CASE P.Color
WHEN 'Red' THEN 'R'
WHEN 'Silver' THEN 'S'
ELSE 'N/A'
END AS ColorGroup
FROM
Production.Product P;
二、sql中mod函数的使用方法
1、sql中mod函数的用法:计算两个表达式的除数和被除数之间的余数,基本语法如下:
SELECT MOD (number,divisor) FROM table_name;
-- 或者
SELECT number MOD divisor FROM table_name;
2、sql中mod函数实例:
SELECT
MOD(count(*),4) as result
FROM
Orders;
-- 结果为 1
三、sqlsum函数
1、sqlsum函数用法:求和函数,它将返回数值列的总和,基本语法如下:
SELECT SUM(column_name) FROM table_name;
2、sqlsum函数实例:
SELECT
SUM(TotalDue) as 'Total Sales'
FROM
Sales.SalesOrderHeader;
四、sql函数in
1、sql函数in用法:判断某个字段的值是否在给定的值列表中,基本语法如下:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
2、sql函数in实例:
SELECT
CustomerID,
OrderDate
FROM
Sales.SalesOrderHeader
WHERE
CustomerID IN (10051,10063,10073);
五、sqlover函数
1、sqlover函数用法:返回某个条件的结果为true时,对应的返回值;否则,返回另一个表达式的结果,基本语法如下:
SELECT
...
, IIF ( boolean_expression, true_value, false_value )
FROM
...;
2、sqlover函数实例:
SELECT
AVG(IIF(salary > 10000, salary, salary*1.1))
FROM
employees;
六、sql函数大全
1、sql函数大全实例:
SELECT
product_name,
CONCAT(category, " ", product_name) as full_name,
IF(price >= 50, "Expensive", "Cheap") as price_level,
UPPER(product_name) as upper_name,
CHAR_LENGTH(product_name) as name_length
FROM
products;
七、sqlite函数大全
1、sqlite函数大全实例:
SELECT
LENGTH("Hello World") as string_length,
SUBSTR("Hello World", 1, 5) as first_five_chars,
UPPER("Hello World") as upper_case,
LOWER("Hello World") as lower_case,
TRIM(" Hello ") as trimmed_string
FROM
tablename;
八、sql函数
1、sql函数用法:函数是SQL语句中的特殊指令,用于完成特定的操作。SQL定义了很多内置函数,此外还支持自定义函数,基本语法如下:
SELECT function_name(parameters) FROM tablename;
2、sql函数实例:
SELECT
LEFT('Techonthenet.com', 6) as site_name,
RIGHT('Techonthenet.com', 3) as domain_name;
九、函数sqlserver
1、函数sqlserver实例:
SELECT
DATENAME(month, GETDATE()) as Month,
DATENAME(year, GETDATE()) as Year,
DATEPART(year, GETDATE()) as Yr;
十、accesssql函数
1、accesssql函数实例:
SELECT
UCASE(Name) as NameUC,
LCASE(Name) as NameLC,
Len(Name) as NameLength,
InStr(Name, 'G') as G_Char_Index
FROM
Customers;
十一、sqlmod函数
1、sqlmod函数用法:计算两个表达式的除数和被除数之间的余数,基本语法如下:
SELECT MOD (number,divisor) FROM table_name;
-- 或者
SELECT number MOD divisor FROM table_name;
2、sqlmod函数实例:
SELECT
MOD(count(*),4) as result
FROM
Orders;
-- 结果为 1