MySQL SQL优化（1）

绝地灬酷狼 2022-11-27 08:45 48阅读 0赞

转载自：[https://www.cnblogs.com/dw3306/p/11483932.html][https_www.cnblogs.com_dw3306_p_11483932.html]

## 叙述 ##

在这里就当时笔记，记录下相关信息

**1： Distinct**

有时需要查询出某个字段不重复的记录，这时可以使用mysql提供的distinct这个关键字来过滤重复的记录，但是实际中我们往往用distinct来返回不重复字段的条数（count(distinct id)）,其原因是distinct只能去重他的目标字段（即所有查询的字段）

**注意： distinct必须放在要查询字段的开头**

1：select distinct user\_name from xxx 这样会过滤掉user\_name 重复的，

2：如果这样写： select  distinct id, user\_name, password  from xxx    mysql会认为要过滤掉 id、user\_name 、 password 都重复（完全一样）的，不然只要有一个字段不一样都会被查询出来；

**注意：如果查询的有时间字段只有当时间的年月日时分秒都重复时才会被过滤掉**

**所以一般distinct用来查询不重复记录的条数。**

**如果要查询不重复的记录，有时候可以用group by **

**2：order by **

**注意：Mysql5.7及以上版本group by 子查询中order by 无效**

如何解决？

1： 通过limit 语句使子查询的order by生效

但是这必须保证limit的数量，所以也可以使用DISTINCT实现。

**3： group by**

**注意：使用group by 后默认返回的是每个组中id 最小的那一条记录**

问题1：  对于mysql中的group by分组后如何获取组内创建时间最大（或最小）的那行数据 ？

以最大为例：

思路： 先对要查询的数据先进行降序排序，然后在使用group by 即可，但是会使用到子查询先排序（这里需要注意上面说到的order by）

SELECT
    *
    FROM(
    
    SELECT
    DISTINCT
    *
    FROM  a 
    ORDER BY a.start_time DESC
    
    ) AS b
    GROUP BY b.id

问题2：对一个结果集分组后怎么取每个分组的两条（或多条）最大（或最小）的数据？

取最大的两条：

SELECT 
    * 
    FROM student_score AS  a
    WHERE (
     SELECT 
    COUNT(*) 
    FROM student_score AS b
        WHERE a.student_name=b.student_name AND a.score<b.score 
    )<2
     
    ORDER BY a.score DESC

取最小的两条：

SELECT 
    * 
    FROM student_score AS  a
    WHERE (
     SELECT 
    COUNT(*) 
    FROM student_score AS b
        WHERE a.student_name=b.student_name AND a.score>b.score 
    )<2
     
    ORDER BY a.score DESC

**4: update  怎样用查询出的一张表数据更新另一张表的字段？？**

用表B的数据（B1列,B2）更新表A的A1,A2列
    update A, B  set A.A1 = B.B1,A.A2=B.B2 where A.ID1 = B.ID1 and A.ID2 = B.ID2;
    
    --或
    
    update A INNER JOIN B ON A.ID1 = B.ID1 AND A.ID2= B.ID2 SET A.A1 = B.B1,A.A2=B.B2;

**5: sql 查询某个条件下多条数据中最新的一条数据或最老的一条数据**

test\_user表结构如下：

![20200814150621233.png][]

需求：查询李四、王五、李二创建的最初时间或者最新时间??

**1:查询最初的创建时间：**

SELECT 
      * 
    FROM(
        SELECT 
          * 
        FROM test_user 
    ) AS tu
    WHERE NOT EXISTS (
         SELECT * FROM(
             SELECT 
               * 
             FROM test_user 
    ) AS tu2 WHERE tu2.user_name=tu.user_name AND DATE(tu.time)>DATE(tu2.time)
    )

**查询结果如下：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbmd5dTg5MTIzMQ_size_16_color_FFFFFF_t_70][]

**2：查询最新的记录**

SELECT 
    * 
    FROM(
      SELECT 
        * 
       FROM test_user 
    ) AS tu
    WHERE NOT EXISTS (
       SELECT * FROM(
               SELECT 
                 * 
                FROM test_user 
    ) AS tu2 WHERE tu2.user_name=tu.user_name AND DATE(tu.time)<DATE(tu2.time)
    )

查询结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbmd5dTg5MTIzMQ_size_16_color_FFFFFF_t_70 1][]

**6、巧用 CASE WHEN 进行统计**

假设我们有如下的需求，希望根据左边各个市的人口统计每个省的人口

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbmd5dTg5MTIzMQ_size_16_color_FFFFFF_t_70 2][]

使用 CASE WHEN 如下：

SELECT CASE pref_name
          WHEN '长沙' THEN '湖南' 
          WHEN '衡阳' THEN '湖南'
          WHEN '海口' THEN '海南' 
          WHEN '三亚' THEN '海南'
        ELSE '其他' END AS district,
        SUM(population) 
    FROM PopTbl
    GROUP BY district;

**7、巧用 CASE WHEN 进行更新**

现在公司出台了一个奇葩的规定

1.  对当前工资为 1 万以上的员工，降薪 10%。
2.  对当前工资低于 1 万的员工，加薪 20%。

一些人不假思索可能写出了以下的 SQL:

--条件1
    UPDATE Salaries
    SET salary = salary * 0.9 WHERE salary >= 10000;
    --条件2
    UPDATE Salaries
    SET salary = salary * 1.2
    WHERE salary < 10000;

这么做其实是有问题的， 什么问题，对小明来说，他的工资是 10500，执行第一个 SQL 后，工资变为 10500 \* 0.9 = 9450, 紧接着又执行条件 2， 工资变为了 9450 \* 1.2 = 11340，反而涨薪了！

如果用 CASE WHEN 可以解决此类问题，如下：

UPDATE Salaries
    SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9
    WHEN salary < 10000 THEN salary * 1.2
    ELSE salary END;

8、**巧用 HAVING 子句**

一般 HAVING 是与 GROUP BY 结合使用的，但其实它是可以独立使用的， 假设有如下表，第一列 seq 叫连续编号，但其实有些编号是缺失的，怎么知道编号是否缺失呢，

![20200814150942157.png][]

用 HAVING 表示如下:

SELECT '存在缺失的编号' AS gap
      FROM SeqTbl
    HAVING COUNT(*) <> MAX(seq);

**灵活使用 HAVING 子句**

**修改前：**

SELECT *
      FROM (SELECT sale_date, MAX(quantity) AS max_qty
              FROM SalesHistory 
             GROUP BY sale_date) TMP
      WHERE max_qty >= 10;

**修改后：**

SELECT 
       sale_date, 
       MAX(quantity) 
    FROM SalesHistory
    GROUP BY sale_date
    HAVING MAX(quantity) >= 10;

**9、自连接**

我们经常需要按分数，人数，销售额等进行排名，有 Oracle, DB2 中可以使用 RANK 函数进行排名，不过在 MySQL 中 RANK 函数未实现，这种情况我们可以使用自连接来实现,如对以下 Products 表按价格高低进行排名

![20200814151142290.png][]

使用自连接可以这么写:

-- 排序从 1 开始。如果已出现相同位次，则跳过之后的位次 
    SELECT P1.name,
           P1.price,
           (SELECT COUNT(P2.price)
              FROM Products P2
             WHERE P2.price > P1.price) + 1 AS rank_1
      FROM Products P1 
      ORDER BY rank_1;

![20200814151215966.png][]

[https_www.cnblogs.com_dw3306_p_11483932.html]: https://www.cnblogs.com/dw3306/p/11483932.html
[20200814150621233.png]: /images/20221124/72f323ba7021420c8d0f25376a55286b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbmd5dTg5MTIzMQ_size_16_color_FFFFFF_t_70]: /images/20221124/d1a9bf3228f04e44856f4c42da170a0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbmd5dTg5MTIzMQ_size_16_color_FFFFFF_t_70 1]: /images/20221124/3d1e8c5787c34f37aa8ef262f1bd33a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbmd5dTg5MTIzMQ_size_16_color_FFFFFF_t_70 2]: /images/20221124/aeb777922fce420ba4da6ef46090919f.png
[20200814150942157.png]: /images/20221124/d7fca037f53549dcb80ca67088dbb955.png
[20200814151142290.png]: /images/20221124/fb84a80c7da04d23a4d329710e594978.png
[20200814151215966.png]: /images/20221124/857a9044580a42288a8ee56465e53188.png