1. 字符串函数
MySQL 的常用函数包括字符串函数、数值函数、日期时间函数、流程函数等。
SELECT ascii("abc"),char(97),concat("hello", "world"),lower("HELLO,WORLD"),char_length("hello"),octet_length("hello"),substring("hello", 1, 3),instr("hello", "el"),replace("hello", 'l', 'b'),trim("o" from "hello"),trim(" hello "),upper("hello");
结果:
各数据库支持常见的字符串函数
2. 数值函数
SELECT abs(-2),ceil(3.4),floor(3.4),mod(10, 2),rand(),round(3.6, 4),truncate(3.1415926, 4);
输出结果:
以下是 SQL 中常见的数值函数及不同数据库中的实现:
来源: /gitchat/column/5dae96ec669f843a1a4aed95/topic/5db7cdcef6a6211cb96197b6
3. 日期时间函数
在数据库中,日期时间类型存在 3 种形式:
DATE
,日期类型,包含年、月、日。可以用于存储出生日期、入职日期等。TIME
,时间类型,包含时、分、秒,以及小数秒。一般使用较少。TIMESTAMP
,时间戳类型,包含年、月、日、时、分、秒,以及小数秒。用于对时间精度要求比较高的场景,比如存储订单时间。
TIMESTAMP
和TIME
还可以添加WITH TIME ZONE
选项,用于指定一个时区偏移量。例如,UTC
标准时间的 0 点等于北京时间的早上 8 点。时区选项通常用于支持全球化的应用系统中。
以下是 4 种数据库对于日期时间类型的支持情况。
其中,Oracle
的DATE
类型包含了日期和时间两部分,但不支持TIME
类型。MySQL
还提供了DATETIME
日期时间类型。
下表列出了SQL
中常见的日期时间函数:
SELECT curdate(),curtime(),now(),unix_timestamp(curdate()),from_unixtime(curdate()),week("-02-28"),year("-02-28"),hour("20:40:20"),minute("20:40:20"),monthname("-02-28"),date_format("-02-28", "%Y_%m_%d"),datediff("-02-28", "-02-01");
输出结果:
除此之外,各种数据库还提供了一些扩展的函数:
Oracle
支持SYSDATE
、SYSTIMESTAMP
获取当前日期和时间戳;MySQL
支持CURDATE()
、CURRENTDATE()
获取当前日期,CURTIME()
、CURRENTTIME()
获取当前时间,NOW()
、CURRENT_TIMESTAMP()
获取当前时间戳;SQL Server
支持SYSDATETIME()
、SYSDATETIMEOFFSET()
获取系统当前时间戳;PostgreSQL
支持NOW()
获取系统当前时间戳。
EXTRACT(part FROM dt)
函数可以返回日期时间中的某个部分,例如年、月、小时等。以下示例查找 年入职的员工:
-- Oracle、MySQL 以及 PostgreSQL 实现SELECT emp_name, hire_dateFROM employeeWHERE EXTRACT(YEAR FROM hire_date) = ;
4. 常用函数
5. 流程函数
SQL
中的CASE
表达式可以根据不同条件产生不同的结果,实现类似于编程语言中的IF-THEN-ELSE
逻辑功能。
5.1 简单 CASE 表达式
简单CASE
表达式的语法如下:
CASE expressionWHEN value1 THEN result1WHEN value2 THEN result2...[ELSE default_result]END
首先计算expression
的值;然后依次与 WHEN 列表中的值(value1,value2,…)进行比较,找到第一个相等的值并返回对应的结果(result1,result2,…);如果没有找到相等的值,返回ELSE
中的默认结果;如果此时没有指定ELSE
,返回NULL
值。
使用示例:
SELECT name, case idwhen 1 then "第一"when 2 then "第二"when 3 then "第三"else "其它的"end as order_zoneFROM zone
结果显示:
5.2 搜索 CASE 表达式
搜索CASE
表达式的语法如下:
CASEWHEN condition1 THEN result1WHEN condition2 THEN result2...[ELSE default_result]END
按照顺序依次计算每个分支中的条件(condition1,condition2,…),找到第一个结果为真的分支并返回相应的结果(result1,result2,…);如果没有任何条件为真,返回 ELSE 中的默认结果;如果此时没有指定 ELSE,返回 NULL 值。
所有的简单CASE
表达式都可以替换为等价的搜索CASE
表达式。
SELECT name, case when id=1 then "第一"when id=2 then "第二"when id=3 then "第三"else "其它的"end as order_zoneFROM zone
6. 聚集函数
聚集函数包括AVG()
、COUNT()
、MAX()
、MIN()
、SUM()
。
聚集函数COUNT
、SUM
、AVG
、MAX
、MIN
只有COUNT
可以将*
号作为参数,其它聚合函数都不行。
MAX
、MIN
函数适用于几乎所有的数据类型的列;而SUM
/AVG
仅适用于数值类型的列
6.1 COUNT 统计数量
COUNT(*)
函数用于统计行数。
SELECT COUNT(*) FROM product;
COUNT
函数也可以统计某个字段或者表达式的数量。
SELECT COUNT(product_type) FROM product;
使用聚合函数时需要注意两点:
在聚合函数的参数中加上DISTINCT
关键字,可以在计算之前排除重复值;聚合函数在计算时,忽略输入值为NULL
的数据行;COUNT(*)
除外。
使用聚合函数删除重复的值
SELECT COUNT(DISTINCT product_type) FROM product;
6.2 AVG 函数计算平均值
SELECT AVG(price) FROM product;
其它SUM()/MAX()/MIN()/
用法和AVG()
用法类似。
7. 分组数据
分组数据主要是对过滤的数据进行分类。SQL
中的GROUP BY
子句可以将数据按照某种规则进行分组。
GROUP BY
子句中指定的列称为聚合键或者分组列,SELECT
子句中不能出现聚合键之外的列名,
SELECT product_type, COUNT(*) FROM product GROUP BY product_type;
对于GROUP BY
,如果分组字段中存在多个NULL
值,它们将被分为一个组。
SELECT purchase_price, COUNT(*) FROM product GROUP BY purchase_price;
带有WHERE
子句的GROUP BY
语句
SELECT purchase_price, COUNT(*) FROM product WHERE product_type = "办公用品" GROUP BY purchase_price;
为聚合结果指定条件:
WHERE
子句 = 指定行 所对应的条件HAVING
子句 = 指定组 所对应的条件
从性能的角度来说,应该尽量使用WHERE
条件过滤掉更多的数据,而不是等到分组之后再使用HAVING
进行过滤;但如果业务需求只能基于汇总之后的结果进行过滤,那就另当别论了。
在SQL
语句中可以使用WHERE
子句对表进行过滤,同时使用HAVING
对分组结果进行过滤。
SELECT purchase_price, COUNT(*) FROM product WHERE product_type = "办公用品" GROUP BY purchase_price HAVING COUNT(*) = 2;
对查询结果进行排序,默认按照升序排列ASC
SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price;
指定降序排列
SELECT product_id, product_name, sale_price, regist_date FROM product ORDER BY sale_price DESC;
如果想要对多个列进行按序排列,则需要对多个列指定关键字
SELECT product_id, product_name, sale_price, regist_date FROM product ORDER BY sale_price DESC, regist_date ASC;
指定多个排序键,优先使用左侧的键,如果该列存在相同值的话,再按照右侧的键
SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price, product_id;
排序键包含NULL
时,会在开头或者末尾进行汇总
SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY purchase_price;
在ORDER BY
子句中可以使用SELECT
子句中未使用的列和聚合函数
SELECT product_name, sale_price, purchase_price FROM product ORDER BY product_id;SELECT product_type, COUNT(*) FROM product GROUP BY product_type ORDER BY COUNT(*);
9. 类型转换函数
CAST(expr AS type)
函数用于将数据转换为不同的类型。以下是一个类型转换的示例:
-- Oracle 实现-- 修改日期显示格式ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';SELECT CAST('666' AS INTEGER), CAST(hire_date AS CHAR(10))FROM employeeWHERE emp_id = 1;-- SQL Server 和 PostgreSQL 实现SELECT CAST('666' AS INTEGER), CAST(hire_date AS CHAR(10))FROM employeeWHERE emp_id = 1;-- MySQL 实现SELECT CAST('666' AS SIGNED INTEGER), CAST(hire_date AS CHAR(10))FROM employeeWHERE emp_id = 1;