MySQL 学习笔记（3）— 字符串函数、数值函数、日期时间函数、流程函数、聚集函数以及分组数据

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");

结果：

各数据库支持常见的字符串函数

函数	描述	Oracle	MySQL	SQL Server	PostgreSQL
ASCII(str)	返回第一个字符的 ASCII 编码	支持	支持	支持	支持
CHR(n) / CHAR(n)	返回 ASCII 编码 n 对应的字符	CHR(n)	CHAR(n)	CHAR(n)	CHR(n)
CONCAT(str1, str2, …)	拼接字符串	支持	支持	支持	支持
LOWER(str)	返回字符串的小写形式	支持	支持	支持	支持
CHAR_LENGTH(str)	返回字符数量	LENGTH(str)	支持	LEN(str)	支持
OCTET_LENGTH(str)	返回字节数量	LENGTHB(str)	支持	DATALENGTH(str)	支持
SUBSTRING(str, n, m)	返回字符串从位置 n 开始的 m 个字符	SUBSTR(str, n, m)	支持	支持	支持
INSTR(str, sub)	返回 str 中首次出现 sub 的位置	支持	支持	PATINDEX( ‘%pattern%’, str)	POSITION(sub IN str)
REPLACE(str, old, new)	将字符串中的 old 子串替换为 new 子串	支持	支持	支持	支持
TRIM(sub FROM str)	删除字符串开头和结尾的 sub 字串	支持	支持	支持	支持
UPPER(str)	返回字符串额大写形式	支持	支持	支持	支持

2. 数值函数

SELECT abs(-2),
       ceil(3.4),
	   floor(3.4),
	   mod(10, 2),
	   rand(),
	   round(3.6, 4),
	   truncate(3.1415926, 4);

输出结果：

以下是 SQL 中常见的数值函数及不同数据库中的实现：
来源： https://gitbook.cn/gitchat/column/5dae96ec669f843a1a4aed95/topic/5db7cdcef6a6211cb96197b6

函数	描述	Oracle	MySQL	SQL Server	PostgreSQL
ABS(x)	计算 x 的绝对值	支持	支持	支持	支持
CEIL(x) / CEILING(x)	计算大于等于 x 的最小整数	支持 CEIL(x)	支持	支持 CEILING(x)	支持
EXP(x)	计算 e 的 x 次幂，e 约等于 2.71828	支持	支持	支持	支持
FLOOR(x)	计算小于等于 x 的最大整数	支持	支持	支持	支持
LN(x)	计算 x 的自然对数，底数为 e	支持	支持	LOG(x)	支持
LOG(y, x)	计算以 y 为底的 x 的对数	支持	支持	LOG(x, y)	支持
MOD(x, y)	计算 x 除以 y 的余数，即求模运算	支持	支持	x % y	支持
POWER(x, y)	计算 x 的 y 次幂	支持	支持	支持	支持
ROUND(x, y)	将 x 四舍五入到 y 位小数	支持	支持	支持	支持
SQRT(x)	计算 x 的平方根	支持	支持	支持	支持
GREAST(x, y, …)	计算参数列表中的最大值	支持	支持	不支持	支持
LEAST(x, y, …)	计算参数列表中的最小值	支持	支持	不支持	支持
RANDOM( )	返回一个大于等于 0 小于 1 的随机数	DBMS_RANDOM.VALUE	RAND()	RAND()	支持

3. 日期时间函数

在数据库中，日期时间类型存在 3 种形式：

• DATE，日期类型，包含年、月、日。可以用于存储出生日期、入职日期等。
• TIME，时间类型，包含时、分、秒，以及小数秒。一般使用较少。
• TIMESTAMP，时间戳类型，包含年、月、日、时、分、秒，以及小数秒。用于对时间精度要求比较高的场景，比如存储订单时间。

TIMESTAMP 和 TIME 还可以添加 WITH TIME ZONE 选项，用于指定一个时区偏移量。例如，UTC 标准时间的 0 点等于北京时间的早上 8 点。时区选项通常用于支持全球化的应用系统中。

以下是 4 种数据库对于日期时间类型的支持情况。

数据类型	Oracle	MySQL	SQL Server	PostgreSQL
DATE	包含额外的时、分、秒	支持	支持	支持
TIME	不支持	支持，不支持时区	支持，不支持时区	支持，支持时区
TIMESTAMP	支持，包含时区	支持，使用 UTC 时区	DATETIME2、DATETIMEOFFSET	支持，支持时区

其中，Oracle 的 DATE类型包含了日期和时间两部分，但不支持 TIME类型。MySQL 还提供了 DATETIME 日期时间类型。

下表列出了 SQL 中常见的日期时间函数：

函数	描述	Oracle	MySQL	SQL Server	PostgreSQL
CURRENT_DATE	获取系统当前日期	支持	支持	CAST(GETDATE() AS DATE)	支持
CURRENT_TIME	获取系统当前时间	支持	支持	CAST(GETDATE() AS TIME)	支持
CURRENT_TIMESTAMP	获取系统当前日期	支持	支持	支持	支持
EXTRACT(part FROM dt)	返回日期时间中的年、月、日等	支持	支持	DATEPART(part, dt)	支持
dt1 - dt2	计算两个日期之间的天数	支持	DATEDIFF(dt2, dt1)	DATEDIFF(DAY, dt1, dt2)	支持
dt + INTERVAL	日期加上一段时间间隔	支持	支持	DATEADD(part, n, dt)	支持

SELECT curdate(),
		curtime(),
        now(),
        unix_timestamp(curdate()),
        from_unixtime(curdate()),
        week("2020-02-28"),
        year("2020-02-28"),
        hour("20:40:20"),
        minute("20:40:20"),
        monthname("2020-02-28"),
        date_format("2020-02-28", "%Y_%m_%d"),
        datediff("2020-02-28", "2020-02-01")
        ;

输出结果：

除此之外，各种数据库还提供了一些扩展的函数：

• Oracle 支持 SYSDATE、SYSTIMESTAMP 获取当前日期和时间戳；
• MySQL支持 CURDATE()、CURRENTDATE() 获取当前日期，CURTIME()、CURRENTTIME() 获取当前时间，NOW()、CURRENT_TIMESTAMP() 获取当前时间戳；
• SQL Server 支持 SYSDATETIME()、SYSDATETIMEOFFSET() 获取系统当前时间戳；
• PostgreSQL 支持 NOW() 获取系统当前时间戳。

EXTRACT(part FROM dt) 函数可以返回日期时间中的某个部分，例如年、月、小时等。以下示例查找 2018 年入职的员工：

-- Oracle、MySQL 以及 PostgreSQL 实现
SELECT emp_name, hire_date
  FROM employee
 WHERE EXTRACT(YEAR FROM hire_date) = 2018;

4. 常用函数

5. 流程函数

SQL 中的 CASE 表达式可以根据不同条件产生不同的结果，实现类似于编程语言中的 IF-THEN-ELSE 逻辑功能。

5.1 简单 CASE 表达式

简单 CASE 表达式的语法如下：

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  [ELSE default_result]
END

首先计算 expression 的值；然后依次与 WHEN 列表中的值（value1，value2，…）进行比较，找到第一个相等的值并返回对应的结果（result1，result2，…）；如果没有找到相等的值，返回 ELSE 中的默认结果；如果此时没有指定 ELSE，返回 NULL 值。

使用示例：

SELECT name, 
     case id
     when 1 then "第一"
     when 2 then "第二"
     when 3 then "第三"
     else "其它的"
     end as order_zone
FROM zone

结果显示：

5.2 搜索 CASE 表达式

搜索 CASE 表达式的语法如下：

CASE
  WHEN condition1 THEN result1
  WHEN 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_zone
FROM 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 子句可以将数据按照某种规则进行分组。

1. GROUP BY子句中指定的列称为聚合键或者分组列， SELECT 子句中不能出现聚合键之外的列名，

SELECT product_type, COUNT(*) 
 FROM product 
GROUP BY product_type;	

2. 对于 GROUP BY，如果分组字段中存在多个 NULL 值，它们将被分为一个组。

SELECT purchase_price, COUNT(*) 
 FROM product 
GROUP BY purchase_price;

3. 带有 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;

4. 对查询结果进行排序，默认按照升序排列 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 employee
 WHERE emp_id = 1;

-- SQL Server 和 PostgreSQL 实现
SELECT CAST('666' AS INTEGER), CAST(hire_date AS CHAR(10))
  FROM employee
 WHERE emp_id = 1;

-- MySQL 实现
SELECT CAST('666' AS SIGNED INTEGER), CAST(hire_date AS CHAR(10))
  FROM employee
 WHERE emp_id = 1;