MySQL面试

高阶函数

select ROW_NUMBER() over(partition by t3.customerId order by t3.uid) as rownum

select rank() over(partition by t3.customerId order by t3.uid) as rownum

PIVOT行转列,

UNPIVOT列转行

查找相关
COALESCE函数
COALESCE()函数返回列表中的第一个非空值。
语法：COALESCE(val1, val2, …, val_n)
查询示例

W3Schools.com

SELECT COALESCE(NULL, NULL, NULL, ‘W3Schools.com’, NULL, ‘Example.com’);

1

SELECT COALESCE(NULL, 1, 2, ‘W3Schools.com’);
Top
进制相关
BIN函数
BIN()函数返回数字的二进制表示形式，作为字符串值。
语法：BIN(number)
查询示例：
SELECT BIN(8);
SELECT BIN(15);
SELECT BIN(111);

BINARY函数
BINARY函数将值转换为二进制字符串。
此功能相当于使用CAST（值AS BINARY）。
语法：

1 在这里，MySQL执行“HELLO”和“hello”的逐字符比较并返回1（因为在逐个字符的基础上，它们是等效的）：

SELECT “HELLO” = “hello”;

0 这里MySQL执行“HELLO”和“hello”的逐字节比较并返回0（因为在逐个字节的基础上，它们不等效）：

SELECT BINARY “HELLO” = “hello”;
CONV函数
CONV()函数将一个数字从一个数字基系统转换为另一个，并将结果作为字符串值返回。
注意：如果任何参数为NULL，则此函数返回NULL。
语法：CONV(number, from_base, to_base)
转换示例

从2进制转为10进制

SELECT CONV(1111, 2, 10);

从10进制转为16进制

SELECT CONV(88, 10, 16);

Top
转换相关
CAST函数
CAST()函数将（任何类型的）值转换为指定的数据类型。
语法：CAST(value AS datatype)
查询示例

YYYY-MM-DD 日期

SELECT CAST(“2017-08-29” AS DATE);

YYYY-MM-DD HH:MM:SS 日期时间型

SELECT CAST(“2017-08-29 08:20:33” AS DATETIME);

HH:MM:SS 时间

SELECT CAST(“08:20:33” AS TIME);

字符型

SELECT CAST(150 AS CHAR);

整数

SELECT CAST(5-10 AS SIGNED);

无符号整数

SELECT CAST(12 AS UNSIGNED);

浮点数

SELECT CAST(12.1 AS DECIMAL);

二进制

SELECT CAST(“12” AS BINARY);

CONVERT函数
CONVERT()函数将值转换为指定的数据类型或字符集。
语法：
CONVERT(value, type)
OR:
CONVERT(value USING charset)
转换类型示例

YYYY-MM-DD 日期

SELECT CONVERT(“2017-08-29” , DATE);

YYYY-MM-DD HH:MM:SS 日期时间型

SELECT CONVERT(“2017-08-29 08:20:33” , DATETIME);

HH:MM:SS 时间

SELECT CONVERT(“08:20:33” , TIME);

字符型

SELECT CONVERT(150 , CHAR);

整数

SELECT CONVERT(5-10 , SIGNED);

无符号整数

SELECT CONVERT(12 , UNSIGNED);

浮点数

SELECT CONVERT(12.1 , DECIMAL);

二进制

SELECT CONVERT(“12” , BINARY );

转换字符集示例
SELECT CONVERT(“W3Schools.com” USING utf8);
SELECT CONVERT(“W3Schools.com” USING utf8mb4);
SELECT CONVERT(“W3Schools.com” USING latin1);

Top
判断相关
CASE函数
语法：
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
查询示例：
CASE语句通过条件并在满足第一个条件时返回一个值（如IF-THEN-ELSE语句）。 因此，一旦条件成立，它将停止读取并返回结果。
SELECT
CASE
WHEN 5 > 2 THEN “case 1”
WHEN 2 = 5 THEN “case 2”
ELSE “case else”
END
如果条件不满足，则它将返回ELSE子句中的值。
SELECT
CASE
WHEN 2 = 5 THEN “case 2”
ELSE “case else”
END
如果没有ELSE部分且没有条件为真，则返回NULL。
SELECT
CASE
WHEN 2 = 5 THEN “case 2”
END
在排序中使用

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);

IF函数
IF()函数在条件为TRUE时返回一个值，如果条件为FALSE则返回另一个值。
语法：IF(condition, value_if_true, value_if_false)
查询示例：

YES

SELECT IF(500<1000, “YES”, “NO”);

NO

SELECT IF(500>1000, “YES”, “NO”);

NO 注：STRCMP(str1, str2)比较两个字符串，如果这两个字符串相等返回0

SELECT IF(STRCMP(“hello”,“bye”) = 0, “YES”, “NO”);
IFNULL函数
如果表达式为NULL，则IFNULL()函数返回指定的值。如果表达式为NOT NULL，则此函数返回表达式。
语法：IFNULL(expression, alt_value)
使用示例：

W3Schools.com

SELECT IFNULL(NULL, “W3Schools.com”);

Hello

SELECT IFNULL(“Hello”, “W3Schools.com”);
ISNULL函数
ISNULL()函数返回1或0，具体取决于表达式是否为NULL。如果expression为NULL，则此函数返回1.否则，返回0。
语法：ISNULL(expression)
使用示例：

1

SELECT ISNULL(NULL);

0

SELECT ISNULL("");

0

SELECT ISNULL(350);

0

SELECT ISNULL(“Hello world!”);

NULLIF函数
NULLIF()函数比较两个表达式，如果它们相等则返回NULL。 否则，返回第一个表达式。
语法：NULLIF(expr1, expr2)
使用示例：

25

SELECT NULLIF(25, “Hello”);

Hello

SELECT NULLIF(“Hello”, “world”);

NULL

SELECT NULLIF(“2017-08-25”, “2017-08-25”);
Top
服务器相关
CONNECTION_ID函数
CONNECTION_ID()函数返回当前连接的唯一连接ID。
语法：CONNECTION_ID()
SELECT CONNECTION_ID();
CURRENT_USER函数
CURRENT_USER()函数返回服务器用于验证当前客户端的MySQL帐户的用户名和主机名。
结果作为UTF8字符集中的字符串返回。
语法：CURRENT_USER()
SELECT CURRENT_USER();
DATABASE函数
DATABASE（）函数返回当前数据库的名称。
如果没有当前数据库，则此函数返回NULL或“”。
语法：DATABASE()
SELECT DATABASE();
Top
杂项
LAST_INSERT_ID函数
LAST_INSERT_ID()函数返回已在表中插入或更新的最后一行的AUTO_INCREMENT标识。
语法：LAST_INSERT_ID(expression)
SELECT LAST_INSERT_ID();

分节点部署

子查询排序
添加limit或者group by，对查询结果进行聚合操作