MySQL - 独门秘技

有时候你想破头都不知道这条 SQL 该怎么写

表操作

设置 created_at 与 updated_at 字段

  • 数据类型:TIMESTAMP(格式为Y-m-d H:i:s),范围:1970 ~ 2037;

  • created_at:

      ALTER TABLE [table] 
          MODIFY created_at TIMESTAMP 
          DEFAULT CURRENT_TIMESTAMP NOT NULL;
    
  • updated_at:

      ALTER TABLE [table] 
          MODIFY updated_at TIMESTAMP 
          DEFAULT CURRENT_TIMESTAMP 
          ON UPDATE CURRENT_TIMESTAMP NOT NULL;
    

设计

枚举类型不要从 0 开始

对于表示 type 等的枚举类型,一般不要从 0 开始,从 1 开始设置。

  1. 对于多数语言 0 代表 false 而非零代表 true,如果使用 tiny(1) 类型存储,MySql 会默认返回成 truefalse 而非真实值。

  2. 对于部分编程语言,原本枚举类型对应 MAP 的形式,但是由于数据库设置了 0 开始,导致部分语言默认转换成数组,因为索引数组的下标从零开始,容易导致一些人混淆或者做一些不必要的转换工作。

查询

统计同一列中各数据出现的次数

  • 效果:

      +----+------+--------+
      | id | name | data   |
      +----+------+--------+       +------+-------+       +------+-------+
      |  1 | a    |   1024 |       | name | times |       | name | times |
      |  2 | b    |   4324 |       +------+-------+       +------+-------+       +------+-------+
      |  3 | c    |   3424 |       | a    |     2 |       | b    |     3 |       | name | times |
      |  4 | d    |    342 |  ==>  | b    |     3 |  ==>  | a    |     2 |  ==>  +------+-------+
      |  5 | b    | 342322 |       | c    |     2 |       | c    |     2 |       | b    |     3 |
      |  6 | a    |     23 |       | d    |     1 |       | e    |     1 |       +------+-------+
      |  7 | c    |  23332 |       | e    |     1 |       | d    |     1 |
      |  8 | e    |   2134 |       +------+-------+       +------+-------+
      |  9 | b    |   1234 |
      +----+------+--------+
    
  • 统计次数:

      SELECT `name`, COUNT(*) AS times 
          FROM [table] 
          GROUP BY [table].`name`;
    
  • 统计排序:
      SELECT `name`, COUNT(*) AS times 
          FROM [table] 
          GROUP BY [table].`name` 
          ORDER BY times DESC;
    
  • 查询出现最大次数:
      SELECT `name`, COUNT(*) AS times 
          FROM [table] 
          GROUP BY [table].`name` 
          ORDER BY times 
          DESC LIMIT 1;
    

查找第 n 高的数据

  • 效果:

      +----+------+------+
      | id | name | data |
      +----+------+------+         +------+
      |  1 | a    | 1024 |         | max  |
      |  2 | b    | 4324 |   n=2   +------+
      |  3 | c    | 3424 |  ====>  | 3424 |
      |  4 | d    |  342 |         +------+
      |  8 | e    | 2134 |
      +----+------+------+
    
  • 巧用 max 函数(仅适用于前 3):

      SELECT max(data) max 
          FROM [table] 
          WHERE data < (SELECT max(data) FROM [table]);
    
  • 使用分页

    使用视图解决 null 问题:

      SELECT (
          SELECT data 
              FROM [table] 
              GROUP BY data 
              DESC 
              LIMIT 1 OFFSET 1
      ) AS max;
    

    使用 ifnull() 函数解决 null 问题:

      SELECT ifnull ((
          SELECT data
              FROM [table]
              GROUP BY data
              DESC
              LIMIT 1 OFFSET 1
          ), null
      ) AS max;
    

行列转换:将多行数据转换成多列数据

  • 效果:

          id  class   course_id    teacher_id
      ------  ------  ---------  ------------
          1   101             2            18         class  语文  数学  英语
          12  101             1            12         -----  ----  ----  ----
          13  101             3             1  ====>   101    12    18    1
          14  102             2             4          102    54    4     0
          15  102             1            54          103    23    0     0
          16  103             1            23          104    0     0     13
          17  104             3            13
    
  • 使用 Group By 分组

    如果需要作为分组的依据是多个字段关联,那么使用 Group By col1, col2, col3... 即可。

    注意,Group By 之后 select 后面指定的字段必须与 group by 后面的一致,或者是使用聚合函数。

    比如,这个例子中,对 class 进行 Group By 之后,不能 select id,因为同一组的 id 是不同的,MySQL 默认不知道选哪个;如果 select 了不合法的字段,MySql 会报异常。

  • 使用 IF 函数

    IF(a, b, c) 函数:若 a 为真,则 b, 否则 c。

      SELECT class,
          SUM(IF(course_id = 1, teacher_id, 0)) AS `语文`,
          SUM(IF(course_id = 2, teacher_id, 0)) AS `数学`,
          SUM(IF(course_id = 3, teacher_id, 0)) AS `英语`
      FROM test 
      GROUP BY class;
    
正在加载今日诗词....

Access Statistics

Table of Contents