SQL语法——细节问题(DISTINCT、ORDER BY、IN、ESCAPE)

MySQL DISTINCT 和 NULL值

如果字段中有NULL值并且要对该列使用DISTINCT 子句，则MySQL仅保留一个NULL值，因为DISTINCT 子句将所有NULL值视为相同的值。

select state from customers;

select distinct state from customers;

DISTINCT语句与GROUP BY 语句

distinct就是group by的一种特例，group by对结果集做了排序,而distinct没有。

要注意的是 MySQL 8.0 开始，移除掉了 group by 的排序，因此如果你用 MySQL 8.0 的话，得到的结果集是没有排序的。

SELECT DISTINCT
    state
FROM
    customers
-- VS
SELECT 
    state
FROM
    customers
GROUP BY state;

总的来说，MySQL8.0 以下，如果不加索引的话，还是 distinct 会快一些，因为它没有对结果集做排序。

MySQL DISTINCT和聚合函数

将DISTINCT子句与聚合函数（例如，SUM，AVG和COUNT ）一起使用，将聚合函数应用于结果集之前删除重复的行。

SELECT
    COUNT(DISTINCT state)
FROM
    customers 
WHERE
    country = 'USA'; 

MySQL DISTINCT 和 LIMIT子句

将DISTINCT 子句与LIMIT子句一起使用，MySQL会在找到LIMIT子句中指定的唯一行数时立即停止搜索。

SELECT DISTINCT
    state
FROM
    customers 
WHERE
    state IS NOT NULL
·LIMIT 5; 

MySQL ORDER BY 使用自定义排序顺序

FIELD()函数：

FIELD()函数返回值列表中值的索引位置。

此函数执行不区分大小写的搜索。

注意：如果在值列表中找不到指定的值，则此函数将返回0.如果value为NULL，则此函数将返回0。

语法：FIELD(value, val1, val2, val3, ...)

1. value：要在列表中搜索的值
2. val1,val2,val3… ：要搜索的值列表

select FIELD('a','c','b','a') # 3

自定义排序：

ORDER BY 子句可以使用FIELD() 函数为列中的值定义自己的自定义排序顺序。

SELECT
	orderNumber, status
FROM
	orders
ORDER BY FIELD(status,
	'In Process',
	'On Hold',
	'Cancelled',
	'Resolved',
	'Disputed','Shipped');

IN运算符逻辑

语法：

SELECT 
    column1,column2,...
FROM
    table_name
WHERE 
 (expr|column_1) IN ('value1','value2',...); 

IN运算符逻辑是：column 在指定列表中则返回1 ，不在其中则返回0。

当列表中的值都是常量时，MySQL执行以下步骤：

1. 首先，评估基于所述值类型的的column_1所述的或expr 表达的结果 。
2. 其次，对值进行排序。
3. 第三，使用二进制搜索算法搜索值。因此，使用IN 运算符查询执行带有常量列表的速度非常快。

注意：如果expr列表中的任何值NULL，则IN 运算符返回NULL。

MySQL LIKE 与ESCAPE子句

有时我们要匹配的模式包含通配符，例如10％，_20等。在这种情况下，您可以使用ESCAPE子句指定转义字符，以便MySQL将通配符解释为文字字符。如果未明确指定转义字符，则反斜杠字符\是缺省转义字符。

SELECT 
    productCode, 
    productName
FROM
    products
WHERE
    productCode LIKE '%\_20%';

-- 或

SELECT 
    productCode, 
    productName
FROM
    products
WHERE
    productCode LIKE '%$_20%' ESCAPE '$';