前情提要：
- 很久远的笔记，试图用于想不起来某知识点时查询，但SQL学习更重实践
- 侧重DQL，未区分工具，函数分类方式较主观
- 或有存误之处，欢迎纠错
1. 分类
- DDL：Data Definition Language 数据定义语言
- 用于定义数据段、数据库、表、列、索引等数据库对象。
- DML：Data Manipulation Language 数据操纵语句
- 用于添加、删除、更新和查询数据库记录，并检查数据完整性。
- DCL：Data Control Language 数据控制语句
- 用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
- DQL：Data Query language 数据查询语言
- 用于查询数据。
2. 数据类型
2.1 MySQL
2.1.1 Text
| 数据类型 | 简介 |
| CHAR(size) | 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。 |
| VARCHAR(size) | 保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。 |
| TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
| TEXT | 存放最大长度为 65,535 个字符的字符串。 |
| BLOB | 用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。 |
| MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
| MEDIUMBLOB | 用于 BLOBs（Binary Large OBjects）。 存放最多 16,777,215 字节的数据。 |
| LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
| LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
| ENUM(x,y,z,etc.) | 允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照您输入的顺序排序的。 可以按照此格式输入可能的值： ENUM('X','Y','Z') |
| SET | 与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。 |
2.1.2 Number
| 数据类型 | 简介 |
| TINYINT(size) | 带符号-128到127 ，无符号0到255。 |
| SMALLINT(size) | 带符号范围-32768到32767，无符号0到65535, size 默认为 6。 |
| MEDIUMINT(size) | 带符号范围-8388608到8388607，无符号的范围是0到16777215。 size 默认为9 |
| INT(size) | 带符号范围-2147483648到2147483647，无符号的范围是0到4294967295。 size 默认为 11 |
| BIGINT(size) | 带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。size 默认为 20 |
| FLOAT(size,d) | 带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DOUBLE(size,d) | 带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。 |
2.1.3 Date/Time
| 数据类型 | 描述 |
| DATE() | 日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31' |
| DATETIME() | *日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' |
| TIMESTAMP() | *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC |
| TIME() | 时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59' |
| YEAR() | 2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。 |
2.2 SQL Server
2.2.1 String
| 数据类型 | 简介 | 存储 |
| char(n) | 固定长度的字符串。最多 8,000 个字符。 | Defined width |
| varchar(n) | 可变长度的字符串。最多 8,000 个字符。 | 2 bytes + number of chars |
| varchar(max) | 可变长度的字符串。最多 1,073,741,824 个字符。 | 2 bytes + number of chars |
| text | 可变长度的字符串。最多 2GB 文本数据。 | 4 bytes + number of chars |
| nchar | 固定长度的 Unicode 字符串。最多 4,000 个字符。 | Defined width x 2 |
| nvarchar | 可变长度的 Unicode 字符串。最多 4,000 个字符。 | |
| nvarchar(max) | 可变长度的 Unicode 字符串。最多 536,870,912 个字符。 | |
| ntext | 可变长度的 Unicode 字符串。最多 2GB 文本数据。 | |
| bit | 允许 0、1 或 NULL | |
| binary(n) | 固定长度的二进制字符串。最多 8,000 字节。 | |
| varbinary | 可变长度的二进制字符串。最多 8,000 字节。 | |
| varbinary(max) | 可变长度的二进制字符串。最多 2GB。 | |
| image | 可变长度的二进制字符串。最多 2GB。 |
2.2.2 Number
| 数据类型 | 简介 | 存储 |
| tinyint | 允许从 0 到 255 的所有数字。 | 1 字节 |
| smallint | 允许介于 -32,768 与 32,767 的所有数字。 | 2 字节 |
| int | 允许介于 -2,147,483,648 与 2,147,483,647 的所有数字。 | 4 字节 |
| bigint | 允许介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。 | 8 字节 |
| decimal(p,s) | 固定精度和比例的数字。 允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 | 5-17 字节 |
| numeric(p,s) | 固定精度和比例的数字。 允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 | 5-17 字节 |
| smallmoney | 介于 -214,748.3648 与 214,748.3647 之间的货币数据。 | 4 字节 |
| money | 介于 -922,337,203,685,477.5808 与 922,337,203,685,477.5807 之间的货币数据。 | 8 字节 |
| float(n) | 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。 | 4 或 8 字节 |
| real | 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 | 4 字节 |
2.2.3 Date
| 数据类型 | 简介 | 存储 |
| datetime | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。 | 8 字节 |
| datetime2 | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。 | 6-8 字节 |
| smalldatetime | 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。 | 4 字节 |
| date | 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 | 3 bytes |
| time | 仅存储时间。精度为 100 纳秒。 | 3-5 字节 |
| datetimeoffset | 与 datetime2 相同，外加时区偏移。 | 8-10 字节 |
| timestamp | 存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 值基于内部时钟，不对应真实时间。 每个表只能有一个 timestamp 变量。 |
2.2.4 其他
| 数据类型 | 简介 |
| sql_variant | 存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。 |
| uniqueidentifier | 存储全局唯一标识符 (GUID)。 |
| xml | 存储 XML 格式化数据。最多 2GB。 |
| cursor | 存储对用于数据库操作的指针的引用。 |
| table | 存储结果集，供稍后处理。 |
3. 函数
3.1 数据类型转换
| 序号 | 函数 | 简介 |
| 1 | cast() | cast(expression as data_dype[length]) length默认为30 ①整数：cast(‘12’ as int) ②浮点数：cast(‘1.2’ as decimal(9,2)) —1.20，decimal(m,n)，m代表数字个数，n为保留n位小数，若不指定，默认为整数 ③日期：select cast('20210922' as date) --2021-09-22 select cast(',1,2,3,4,8,9,10,11,12,13,14,15,16,17,18' as varchar) select cast(',1,2,3,4,8,9,10,11,12,13,14,15,16,17,18' as varchar(50)) |
| 2 | convert() | convert(data_type(length), data_to_be_converted, style) 数据类型，需要转换的值，输出格式 style用于将日期时间或smalldatetime转换为字符数据的样式值 【示例】 select convert (int, 25.65) --25 select convert(decimal, '123.45') -- 123 select convert(decimal(9,2), '123.45') -- 123.45 select convert(varchar(25), getdate()) --09 22 2021 10:31PM select convert(varchar (24), getdate(), 120) --2021-09-22 22:32:43，最常用，保留日期时间信息 select convert(varchar(10), getdate(), 102) --2021.09.22，只保留日期信息 select convert (varchar(10), getdate(), 111) --2021/09/22，只保留日期信息 select convert(varchar(10), getdate(), 108) --22:34:06，只保留时间信息 |
3.2 字符串处理
| 序号 | 类别 | 函数 | 简介 |
| 1 | 转换 | ascii() | ascii(字段)，返回字段表达式最左段字符的ascii码值 |
| 2 | 转换 | char() | 输入0-255之间ascii值，将ascii码转换成字符；若不属于0-255或未输入，char()返回NULL |
| 3 | 转换 | soundex() | 将任何文本串转换为描述其语音表示的字母数字模式的算法，返回soundex值 |
| 4 | 转换 | str() | str(float_expression, length, decimal)：数值，字符串长度，小数位数 将数值型数据转换成字符型数据，先服从length后decimal。 如果没有指定长度，默认length为10，默认decimal为0； 如果length或decimal为负数，返回NULL； 如果length小于小数点左边的位数，返回length个*； 如果字符串位数小于length，左边补足空格。 |
| 5 | 转换 | lower() | 全小写，lcase(str) |
| 6 | 转换 | upper() | 全大写，ucase(str) |
| 7 | 去空格 | trim() | 两头去空格，trim(substr from str)，去掉str两端的substr |
| 8 | 去空格 | ltrim() | 左边去空格 |
| 9 | 去空格 | rtrim() | 右边去空格 |
| 10 | 截取 | left() | left(char_expression, integer_expression)=left(字段,个数) 返回char左边n个字符 |
| 11 | 截取 | right() | right(char_expression, integer_expression)=right(字段,个数) 返回char右边n个字符 |
| 12 | 截取 | substring() substr() | substr(expression, start_position, length)=mid(str, n, len) 返回从char左边第n个字符起，长度为length的子串 |
| 13 | 截取 | substring_index | substring(被截取字段，分隔符，位置) select substring_index(‘www.baidu.com)’, ‘.’, 2) |
| 14 | 切割 | split() | hive数据库：split(字符串,分隔符)，划分结果是array select split ('wo,shi,xiao,ming',',') -- ["wo","shi","xiao","ming"] |
| 15 | 切割 | explode() | hive数据库：一行转一列 将一行数据拆分成一列，explode(array/map类型) select explode(array("wo","shi","xiao","ming")) as word –分成一列 select explode(map('A','1','B','2','C','3')) —分2列，key、value各一列 |
| 16 | 切割 | posexplode() | hive数据库：多行转多列 select id,tim, single_id_index, single_id from test.a lateral view posexplode(split(id,',')) t as single_id_index, single_id |
| 17 | 切割 | lateral view | hive数据库：lateral view 格式：laterak view 虚拟表 虚拟表名称 as 拆分字段 用法： ①from后where前； ②必须给虚拟表命名； ③from后可以带多个lateral view； ④如果要拆分的字段有null值，需要使用lateral view outer 替代，避免数据缺失。 select name, t_course.course, t_hobby.hobby from lateral_test lateral view explode(split(course, ',')) t_course as course lateral view explode(split(hobby, ',')) t_hobby as hobby select name, course, col1 from lateral_test lateral view outer explode(array()) et as col1 |
| 18 | 拼接 | concat() | concat(字段, 符号,字段……)：用逗号分隔连接的字段或字符串 也可以用“+”（加号）拼接，如name+’A’+class as new |
| 19 | 拼接 | concat_ws() | 可以一次性指定分隔符号，分隔符号不能是NULL concat_ws(分隔符号, str1, str2) |
| 20 | 拼接 | group_concat() | 分组拼接 group_concat(分组字段 order by 排序字段 desc separator 分隔符) select name, group_concat(id) from t1 group by name select name,group_concat(id order by id desc separator '_') from tt2 group by name |
| 21 | 查找 | charindex() | charindex(substr_expression, expression)： substr_expression是要找的字符串，expression可以是字符串，也可以是列表；如果发现子串，返回子串开始出现的位置；如果没有发现子串，返回0。 不能用于text和image数据类型。 |
| 22 | 查找 | parindex() | parindex(%substr_expression%, column_name)，返回指定子串开始出现的位置，找不到则返回0。 要找的字符串前后必须有%，否则返回值为0。 可以用于char、varchar、text数据类型。 |
| 23 | 查找 | instr() | instr(str, substr)，返回在字符串str中子串substr第一个出现的位置，下标从1开始，没有返回0，hql中可以使用 |
| 24 | 查找 | locate() | locate (substr,str,pos) 从str的pos开始找substr，返回第一个出现的位置，没有返回0 hql中可以使用 |
| 25 | 查找 | position() | position(substr,str) 返回子串 substr 在字符串 str 中第一次出现的位置。 如果子串 substr 在 str 中不存在，返回值为 0,hql中不可以使用 |
| 26 | 查找 | find_in_set() | find_in_set(str1,str2) 返回str2中str1所在的位置索引，str2必须以","分割开, 如有两行数据 |
| 27 | 查找 | contains() | contains('targetStr','serachStr') 在mysql中，对普通的字符串列无法使用contains方法 |
| 28 | 比较 | like | 通配符： ①%（百分号）：代表0-n个字符； ②_（下划线）：代表1个字符； ③[]（方括号）：代表字符集，如’[JM]%’代表以J或M开头的字符串； ④^或！：与方括号搭配使用，表示否定，’[^JM]%’代表不以J或M开头的字符串；'[M-Z]inger'代表以M-Z开头的字符串 |
| 29 | 操作 | quotename() | 主要作用就是在存储过程中，给列名、表名等加个[]、''等以保证sql语句能正常执行。如表名称为123、abc时，直接查询会出错，得将其转为参数，所以需要用quotename来转义。 quotename(character_string, quote_character) character_string：字符串 quote_character ：sysname值，可以是单引号（’）、左方括号（[）、右方括号（]）、双引号（”）等，默认为方括号[] select quotename('ab[]cde') 输出结果：[ab[]]cde] select quotename('ab[]cde',']') 输出结果：[ab[]]cde] select quotename('ab[]cde','') 输出结果：[ab[]]cde] select quotename('ab[]cde','''') 输出结果：'ab[]cde' select quotename('ab[]cde','}') 输出结果：{ab[]cde} select quotename('ab{}cde','}') 输出结果：{ab{}}cde} select quotename ('测试') -- 结果 ：[测试] select quotename ('测试', '[') -- 结果 ：[测试] select quotename ('测试', '{') -- 结果 ：{测试} select quotename ('测试', '(') -- 结果 ：(测试) |
| 30 | 操作 | replicate() repeat() | repliacate(str, count)—字符串，复制次数 select replicate(‘a’,2) –返回’aa’ 返回一个重复character_expression 指定次数的字符串 |
| 31 | 操作 | reverse() | 将指定的字符串的字符排列顺序颠倒 reverse(<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值 |
| 32 | 操作 | space() | 返回一个有指定长度的空白字符串 space (<integer_expression>) 如果值为负值，则返回NULL |
| 33 | 替换 | replace() | 返回被替换了指定子串的字符串 replace(str,str1,str2)：用str2替换str中的str1 |
| 34 | 替换 | stuff() insert() | stuff (string, start, length, substring) 字符串，起始位置，长度，用于替换的字符串 用substring替换string从start开始长度为length的子串 如果起始位置为负，或长度为负，或其实位置大于string长度，返回NULL； 如果length长度大于start起始位置开始的长度，string只保留首字符 |
| 35 | 替换 | lpad() | lpad(str, length, substr)，用substr替换str左起length长度的字符。 如果substr长度大于length，长度缩短至length。 |
| 36 | 替换 | rpad() | rpad(str, length, substr) |
| 37 | 其他 | len() length() | 返回字符串长度 |
| 38 | 其他 | bit_length() | 返回字符串的比特长度 |
| 39 | 其他 | difference() | 两个字符串发音越相似（仅限于英文字符），返回值越大（返回值在0 - 4之间） DIFFERENCE( ' sun ' , ' san ' ) -- 返回值4 DIFFERENCE( ' sun ' , ' safdsdf ' ) -- 返回值3 DIFFERENCE( ' sun ' , ' dgffgfdg ' ) -- 返回值0 |
| 40 | 其他 | strcamp() | strcamp(str1,str2)：如果str1和str2相同，返回0；str1小于str2，返回-1；其他情况返回1 |
| 41 | 其他 | elt() | elt(n,str1,str2,……,strn)，返回第n个字符串 |
3.3 日期时间
| 序号 | 类别 | 函数 | 简介 | |||
| 1 | 获取日期 | curdate()或current_date() | 返回当前日期，如2023-04-11 utc_date()：返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD" utc_time()：返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD" | |||
| 2 | 获取时间 | curtime()或current_time | 返回当前时间，如15:33:11 | |||
| 3 | 获取时间 | now() | 返回当前日期+时间，如2023-04-11 15:33:11 current_timestamp()、localtime()、sysdate() | |||
| 4 | 获取年月日 | year() | year(date_expression)：返回date_expression中的年份值 YEAR("2022-04-11 15:44:28") | |||
| 5 | 获取年月日 | month() | month(date_expression)：返回date_expression中的月份值 MONTH("2022-04-11 15:44:28") | |||
| 6 | 获取年月日 | monthname() | monthname(date_expression)： 返回date_expression中的月份名称。 MONTHNAME("2022-04-11 15:44:28") -- April | |||
| 7 | 获取年月日 | day() | day(date_expression)：返回date_expression中的日期值 DAY("2022-04-11 15:44:28") | |||
| 8 | 获取时分秒 | hour() | hour(date_expression)：返回date_expression中的小时值 HOUR("2022-04-11 15:44:28") | |||
| 9 | 获取时分秒 | minute() | minute(date_expression)：返回date_expression中的分钟值 MINUTE("2022-04-11 15:44:28") | |||
| 10 | 获取时分秒 | second() | second(date_expression)：返回date_expression中的秒值 SECOND("2022-04-11 15:44:28") | |||
| 11 | 获取 | extract(type from date) | 从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、DAY_MICROSECOND | |||
| 12 | 获取星期 | dayname() | dayname(date_expression)：返回当天星期名称 DAYNAME("2022-04-11 15:44:28") – Monday | |||
| 13 | 计算日期 | date_add() add_date() | date_add(date, interval expr type) date_add(日期,interval 数字 时间单位) type：year/quarter/month/week/day/hour/minute/second/ microsecond date_add(now(), interval 1 year) date_add(now(),interval 1 month) sql server是dateadd(type, expr, date) add_date(date, interval expr type) | |||
| 14 | 计算日期 | date_sub() | date_sub(日期,interval 数字 时间单位) subdate(date, interval expr type) | |||
| 15 | 计算间隔 | datediff() | datediff(date1, date2)=date1-date2，只计算日期差异 | |||
| 16 | 计算间隔 | timestampdiff() | timestampdiff(unit, begin, end)=end-begin | |||
| 17 | 计算间隔 | unix_timestamp() | unix_timestamp()返回格林尼治标准时间1970-01-01 00:00:00到现在的秒数，unix_timestamp(date)返回格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数 | |||
| 18 | 日期顺序 | dayofyear() | dayofyear()，返回date是一年的第几天,1-366 | |||
| 19 | 日期顺序 | dayofmonth() | dayofmonth()，返回date是一个月的第几天,1-31 | |||
| 20 | 日期顺序 | dayofweek() weekday() | dayofweek()，返回date是一星期的第几天，1-7，1是周日 weekday()，返回date是一星期的第几天，0-6，0是周一 | |||
| 21 | 日期顺序 | quarter() | 返回date是一年的第几季度，1-4 | |||
| 22 | 日期顺序 | week() | 返回date是一年的第几周，1-53 | |||
| 23 | 格式化 | date_format() | date_format(date, format) date_format(now(), ‘%Y.%m.%d’) – 2023.07.01 | |||
| 年=%Y，4位，yyyy | 年=%y，2位，yy | |||||
| 月=%M，英文名 | 月=%m，数值，01-12 | |||||
| 月=%b，缩写月名 | 星期=%a，缩写星期名 | |||||
| 星期=%W，星期名 | 星期=%w，数值，0-6 | |||||
| 日=%D，带英文前缀 | 日=%d，数值，01-31 | |||||
| 日=%e，数值，1-31 | %J：年的天，001-366 | |||||
| %X：年，星期日是周的第一天，4 位，与 %V 使用 | ||||||
| %V：周 (01-53) 星期日是一周的第一天，与 %X 使用 | ||||||
| %x：年，星期一是周的第一天，4 位，与 %v 使用 | ||||||
| %v：周 (01-53) 星期一是一周的第一天，与 %x 使用 | ||||||
| %U：周 (00-53) 星期日是一周的第一天 | ||||||
| %u：周 (00-53) 星期一是一周的第一天 | ||||||
| 24 | 格式化 | time_format() | time_format(now(), ‘%h:%i:%s’) – 08:00:23 | |||
| %H：00-23 | %h：01-12 | %I：01-12 | ||||
| %k：0-23 | %l：1-12 | %i：00-59，分钟 | ||||
| %p：AM、PM | %S：00-59秒 | %s：00-59 | ||||
| %f：微妙 | ||||||
| %r：时间，12小时，hh:mm:ss AM/PM | ||||||
| %T：时间，24小时，hh:mm:ss | ||||||
| 25 | 格式化 | to_date() | to_date(datetime, format) | |||
| 26 | 格式化 | from_unixtime() | from_unixtime(unix_timestamp, format) 时间戳，时间格式 | |||
| 27 | 其他 | time_to_sec(time) sec_to_time(num) | time_to_sec(time)=3600*小时+60*分钟+秒 sec_to_time是time_to_sec的反函数 | |||
3.4 数学相关
| 序号 | 类别 | 函数 | 简介 |
| 1 | 数值编辑 | str() | str(number, length, decimal)= 数字，长度，小数位 SELECT STR( 123.34584 , 7 , 3 ) -- 返回值123. 346 当设定长度值小于整数部位长度时，字符串将返回设定长度个 * SELECT STR( 123333.34584 , 5 , 4 ) -- 返回值 ***** |
| 2 | 数值编辑 | floor() | 返回指定数字的最大整数 select floor( 123456.1234 ) -- 返回值123456 |
| 3 | 数值编辑 | ceiling() | 返回不带小数部分并且不小于其参数的值的最小数字。如果参数是一个空序列，则返回空序列 select ceiling( 123.010 ) -- 返回124 select ceiling( null ) -- 返回NULL |
| 4 | 数值编辑 | round() | 返回四舍五入后的最接近该数值的数值 select round( 126.018 , 2 ) -- 返回126. 12 |
| 5 | 数值编辑 | abs() | 绝对值 |
| 6 | 常数 | pi() | SELECT PI() -- 返回3. 14159265358979 |
| 7 | 常数 | rand() | 函数返回一个介于 0 和 1（不包括 0 和 1）之间的随机 float 值 rand(seed)：如果不指定seed，会随机分配seed |
| 8 | 数学运算 | squart() | 平方：Square(float_expression) float_expression 是 float 类型或能隐式转换为 float 类型的表达式；函数的返回值为 float 数据 select SQUARE(9) as '9 的平方' |
| 9 | 数学运算 | sqrt() | 平方根：Sqrt(float_expression) select SQRT(9) as '9 的平方根' |
| 10 | 数学运算 | power() | 乘方：Power(float_expression,y) select POWER(3,3) as '3 的 3 次方',POWER(1.5,3) as '1.5 的 3 次方' |
| 11 | 数学运算 | mod(x,y) | 返回x/y的模（余数） |
| 12 | 数学运算 | log(x,y) | logyx=返回x的以y为底的对数 |
| 13 | 数学运算 | ln(x) | 返回x的以e为底数的对数=lnx |
| 14 | 数学运算 | log10(x) | 返回x的以10为底数的对数=log10x |
| 15 | 数学运算 | mod(x,y) | 返回x/y的模（余数） |
| 16 | 数学运算 | srdev stddev_pop 标准差 | select srdev(column_name) from table_name; select stddev_pop(column_name) from table_name; sqrt(sum(power(column_name - avg(column_name), 2)) / count(*)) |
| 17 | 三角函数 | sin() | 返回指定角度的三角正弦值 |
| 18 | 三角函数 | cos() | 返回指定角度的三角余弦值 |
| 19 | 三角函数 | tan() | 返回指定角度的三角正切值 |
| 20 | 三角函数 | cot() | 返回指定角度的三角余切值 |
| 21 | 三角函数 | asin() | 返回反正弦值 |
| 22 | 三角函数 | acos() | 返回反余弦值 |
| 23 | 三角函数 | atan() | 返回反正切值 |
| 24 | 其他 | radians() | 返回x由角度转化为弧度的值 |
| 25 | 其他 | degrees() | 返回x由弧度转化为角度的值 |
| 26 | 其他 | sign() | 返回代表数字x的符号的值 |
| 27 | 其他 | greatest() | greatest(x1,x2,……xn)，返回集合中最大的值 |
| 28 | 其他 | least() | least (x1,x2,……xn)，返回集合中最小的值 |
| 28 | 其他 | format() | format(x,n)：将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回 |
3.5 聚合函数
| 序号 | 函数 | 简介 |
| 1 | avg() | 聚合开窗： select sum(score) over(partition by name) from table – name相同的分数相加 select sum(score) over(order by id) from table – 按照id的顺序升序依次相加 group by后加 with rollup会把整体再算一遍 with rollup作用：在聚合函数上，如果聚合函数是COUNT(*)则会在统计的记录中再次求COUNT(*),如果是AVG(),则会在分组 with rollup 作用在group by 的第一个字段
|
| 2 | count() | |
| 3 | min() | |
| 4 | max() | |
| 5 | sum() | |
| 6 | group_concat() | |
| 7 | group by |
3.6 窗口函数
| <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名> frame_clause <窗口大小>) |
|
| 【frame_clause】作用是对分组进一步细分，在当前分组内指定一个计算窗口。 指定了窗口之后，就不再基于分组进行计算，而是基于窗口内的数据进行计算。窗口会随着当前处理的数据行而移动，也可理解为 限制窗口框架，可以使用 rows 和 range。 rows ：将分组中包含的行指定为当前行之前或之后的行。 例如，rows N preceding （表示之前 N 行到本行） range ：按照排序列的当前值，根据相同值来确定分组中的行。 例如：range between frame_start and frame_end 【frame_start 和 frame_end】 current row：对于 rows 方式，代表了当前行；对于 range ，代表了当前行的所有对等行。 unbounded preceding：代表了分组中的第一行。 unbounded following：代表了分组中的最后一行。 n preceding：对于 rows 方式，代表了当前行之前的第 n 行；对于 range ，代表了等于当前行的值减去 n 的所有行；如果当前行的值为 NULL，代表了当前行的所有对等行。 n following：对于 rows 方式，代表了当前行之后的第 n 行；对于 range ，代表了等于当前行的值加上 n 的所有行；如果当前行的值为 NULL，代表了当前行的所有对等行。 | |
| 序号 | 函数 | 简介 |
| 1 | first_value() | 在窗口取到的第一个值 select first_value(score) over( partition by name) as first_score --根据name分区（组），取score列的第一个值 |
| 2 | last_value() | 在窗口取到的最后一个值 last_value(score) over(partition by name) as last_score --根据name分区（组），取score列的最后一个值 |
| 3 | lead() | 取当前行的上 N 条数据，并且可以设置默认值 lead(expression, offset, default_value)=列，偏移量，如果没有数值时的默认值（默认为null，可以设置为0） lead(score,1,0) over(partition by name ) as lead_score --根据name分区（组），score列当前行的上面N行，如果没有就为默认值0 |
| 4 | lag() | 取当前行的下 N 条数据，并且可以设置默认值 lag(expression, offset, default_value)=列，偏移量，如果没有数值时的默认值（默认为null，可以设置为0） lag(score,1,0) over(partition by name ) as lag_score --根据name分区（组），score列当前行的下面N行，如果没有就为默认值0 |
| 5 | row_number() | row_number ()是为每组的行设置一个连续的递增的数字（123456） row_number() over( partition by name order by score) as row_score |
| 6 | rank() | rank()是排名，也为每一组的行生成一个序号,如果有相同的值会生成相同的序号，并且接下来的序号是不连序的，如111456 rank() over(partition by name order by score asc) as rank_score |
| 7 | dense_rank() | dense_rank()是排名，如果有相同序号，序号不会间断，如112334 dense_rank() over(partition by name order by score asc) as dense_rank_score |
| 8 | ntile() | ntile(n) over(partition by expression order by expression desc/asc) n是将数据集分成的组数，partition是要分组的列，order是排序顺序。 ntile( 4 ) over ( PARTITION BY id ORDER BY sale ) AS rank1—先按sale升序排序，按id特征分组，分成4组—主打一个分桶的概念 |
| 9 | nth_value(expr,n) | nth_value(expr,n)：返回窗口中第n个expr的值 NTH_VALUE( cost, 3 ) OVER ( ORDER BY username ASC ) nth_cost -- 按username排序，排序完取cost第3行的值 nth_value(cost, 3) over(partition by username order by cost) as nth_cost -- 按username分组分别按cost升序排序，取每组排序第3的cost，如果排序在3之前，该数值为null |
| 10 | cume_dist() | 计算分组中当前行的相对排名。即计算每行数据在其分组内的累积分布，也就是排在该行数据之前的所有数据所占的比率，取值范围（0,1]。 假如一共有k个值，本行排序第n 返回值=n/k |
| 11 | percent_rank() | 当前行的百分比排名 返回值在[0,1]内，假如一共有k个值，本行排序第n 返回值=n-1/k-1 |
3.7 条件判断
| 序号 | 函数 | 简介 | |
| 1 | case when | case when 条件1 then 结果1 …… when 条件n then 结果n else 结果n+1 end | case 字段 when 值1 then 结果1 …… when 值n then 结果n else 结果n+1 end |
| 2 | if() | if(条件1,结果1,结果2) 如果条件1满足，返回结果1，否则返回结果2 | |
| 3 | ifnull() | 判断第一个参数是否为Null，ifnull(字段1, 字段2) 如果字段1不是空，返回字段1，否则返回字段2 | |
| 4 | nullif() | 判断两个参数是否相等，nullif(字段1, 字段2) 如果字段1=字段2，返回null，否则返回字段1 | |
3.8 系统信息
| 序号 | 函数 | 简介 |
| 1 | VERSION() | 看版本 |
| 2 | connnection_id() | 当前用户连接数 |
| 3 | USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()：查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的 | |
| 4 | charset(str) | 查看字符串str使用的字符集 |
| 5 | collation() | 查看字符串排列方式 |
3.9 加密函数
| 序号 | 函数 | 简介 |
| 1 | password(str) | 从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用 |
| 2 | md5(str) | 为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回 |
| 3 | encode(str,pswd_str) | 使用pswd_str作为密码，加密str |
| 4 | decide(crypt_str,pswd_str) | 使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串 |
3.10 其他
| 序号 | 函数 | 简介 |
| 1 | CONV(N,from_base,to_base) | 不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制 |
| 2 | INET_ATON(expr) | 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特 |
| 3 | INET_NTOA(expr) | 给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示 |
| 4 | BENCHMARK(count,expr) | 重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。 另一个作用是用它在MySQL客户端内部报告语句执行的时间 |
| 5 | CONVERT(str USING charset) | 使用字符集charset表示字符串str |
| 6 | ifnull(expression,值) | |
| 7 | coalesce(val1, val2, ...., val_n) | 返回第一个非空值 |
559
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
