SQL训练营TASK05学习笔记-sql基础查询5-函数、通配符

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；
常用聚合函数
基本概念与excel操作一致
建议配合excel函数进行双重学习

COUNT函数的结果根据参数的不同而不同。COUNT()会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
聚合函数会将NULL排除在外。但COUNT()例外，并不会排除NULL。
MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
在聚合函数的参数中使用DISTINCT，可以删除重复数据。

使用聚合函数删除重复值
– 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM product;

– 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM product;

算术函数
以下列举些常用函数
部分与excel和python操作一致
ABS – 绝对值
ABS( 数值 )
当 ABS 函数的参数为NULL时，返回值也是NULL。
MOD – 求余数
语法：MOD( 被除数，除数 )
MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
余数 10/3=3余1
注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
ROUND – 四舍五入
语法：ROUND( 对象数值，保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

字符串函数
部分函数与excel操作一致建议配合学习
CONCAT – 拼接
语法：CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
LENGTH – 字符串长度
语法：LENGTH( 字符串 )
LOWER – 小写转换 UPPER – 大写转换
LOWER(字符串)
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。
类似的， UPPER 函数用于大写转换。
SELECT UPPER(vend_name)
FROM vend_name
ORDER BY vend_name;
REPLACE – 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING – 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。
SUBSTRING_INDEX – 字符串按索引截取
语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)
该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
LEFT（）
返回串左边的字符
RIGHT（）
返回串右边的字符

DATE()
返回日期时间的日期部分
NOW（）
返回当前日期和实践

转换函数
CAST – 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）
– 将字符串类型转换为数值类型
– 将字符串类型转换为日期类型
SELECT CAST(‘2009-12-14’ AS DATE) AS date_col;
COALESCE – 将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
大致来说，窗口函数可以分为两类。

一是 将SUM、MAX、MIN等聚合函数用在窗口函数中

二是 RANK、DENSE_RANK等排序用的专用窗口函数
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING )

<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)
拓展
教程中额外的函数
格式化函数
DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值
FORMAT(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数
INET_ATON(ip) 返回IP地址的数字表示
INET_NTOA(num) 返回数字所代表的IP地址
TIME_FORMAT(time,fmt) 依照字符串fmt格式化时间time值
其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。
示例：
SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),’%W,%D %M %Y %r’);
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d’);
SELECT DATE_FORMAT(19990330,’%Y-%m-%d’);
SELECT DATE_FORMAT(NOW(),’%h:%i %p’);
SELECT INET_ATON(‘10.122.89.47’);
SELECT INET_NTOA(175790383);
系统信息函数
DATABASE() 返回当前数据库名
BENCHMARK(count,expr) 将表达式expr重复运行count次
CONNECTION_ID() 返回当前客户的连接ID
FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数
USER()或SYSTEM_USER() 返回当前登陆用户名
VERSION() 返回MySQL服务器的版本
示例：
SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI()));#该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。
LIKE谓词 – 用于字符串的部分一致查询
百分号（ % ）通配符
替代一个或多个字符，相当于模糊查询
例如，为了找出所有以词 jet 起头的产品
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘jet%’;
在执行这条子句时，将检索任
意以 jet 起头的词。 % 告诉MySQL接受 jet 之后的任意字符，不管它有多少字符
搜寻有中间字符
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘%anvil%’
搜索模式 ‘%anvil%’ 表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。
搜寻后边字符
SELECT prod_name
FROM products
WHERE prood_name LIKE ‘%e’
查询条件的字符串与查询对象字符串的末尾部分相同。
下划线（ _ ）通配符
下划线的用途与 % 一样，但下划
线只匹配单个字符而不是多个字符
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘_ton anvil’