Mysql查缺补漏知识点小结

1、distinct 和聚合函数
select count(distinct state)
from A
where ...

1.1 DISTINCT 多列

distinct子句与多个列一起使用，在这种情况下，Mysql使用这些列中的值组合来确定结果集中行的唯一性

1.2 LIMIT获得第N个最高值

SELECT 
    select_list
FROM
    table
ORDER BY sort_expression DESC
LIMIT nth-1, count; 

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

 3、如果想要找到名字以 T开头m结尾，中间只包含一个任意字符。例如Tom，Tim  脚本如下：

 

 4、CONCAT_WS函数

CONCAT_WS函数可用于连接名字和姓氏。

SELECT 
    CONCAT_WS(', ', lastName, firstname)
FROM
    employees; 

 

5、MySQL GROUP BY 带有表达式实例
SELECT
YEAR(orderDate) AS year,
SUM(quantityOrdered * priceEach) AS total
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
WHERE
status = 'Shipped'
GROUP BY YEAR(orderDate);
6、标准SQL不允许您在GROUP BY子句中使用别名，但MySQL支持此功能。
SELECT
YEAR(orderDate) AS year, COUNT(orderNumber)
FROM
orders
GROUP BY year;

6.1 MySQL 子查询

一个MySQL子查询是嵌套在另一个查询内，子查询称为内部查询，而包含子查询的查询称为外部查询。子查询可以在任何使用表达式的地方使用，并且必须在括号中关闭。

 

 

 

 子查询可以带有 = > < 等计算运算符或者in\not in 等运算符。

7、rollup
如果要在一个查询中一起生成两个或多个分组集，使用union all
SELECT
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline
UNION ALL
SELECT
NULL,
SUM(orderValue) totalOrderValue
FROM
sales;
因为UNION ALL 要求所有查询具有相同数量的列，所以我们在第二个查询的选择列表中添加了NULL满足此要求的内容。
将NULL在productLine列中标识量线的总计。
此查询能够按产品线以及总计生成总订单值，但是有两个问题：
1、查询冗长
2、查询的性能不好，因为数据库引擎必须在内部执行两个单独的查询并将结果集合并为一个。
要解决这些问题，可以使用RollUP字句。

SELECT
productLine,
orderYear,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline,
orderYear
WITH ROLLUP;
结果：
+------------------+-----------+-----------------+
| productLine | orderYear | totalOrderValue |
+------------------+-----------+-----------------+
| Classic Cars | 2013 | 5571.80 |
| Classic Cars | 2014 | 8124.98 |
| Classic Cars | 2015 | 5971.35 |
| Classic Cars | NULL | 19668.13 |
| Motorcycles | 2013 | 2440.50 |
| Motorcycles | 2014 | 2598.77 |
| Motorcycles | 2015 | 4004.88 |
| Motorcycles | NULL | 9044.15 |
...
RollUP在每次产品线更改时生成小计行，在结果结束时生成总计。
在这种情况下的层次结构是：
productLing > orderYear

要检查NULL结果集中是否表示小计或总计，请使用GROUPING()函数。

SELECT 
    orderYear,
    productLine, 
    SUM(orderValue) totalOrderValue,
    GROUPING(orderYear),
    GROUPING(productLine)
FROM
    sales
GROUP BY 
    orderYear,
    productline
WITH ROLLUP; 

8、UNION vs JOIN
join 是水平组合结果集，union是垂直附加结果集

9、SQL INTERSECT 运算符简介

INTERSECT运算符是一个集合运算符仅返回两个查询或多个查询的不同行。

10、SQL MINUS运算符简介

SELECT id FROM t1
MINUS
SELECT id FROM t2; 

 

 查询返回t1表的查询中的不同值，这些值在表的查询结果中找不到t2

11、查找一列中的重复值

在基于一列的表中查找重复值，您可以使用以下语句：

select

col,

count(col)

from

table_name

group by col

having count(col) > 1;

12、DENSE_RANK() 窗口函数

它为分区或结果集中的每一行分配排名，而排名值没有间隙