SQL-CheetSheet

在线联系网站

• 自学SQL http://xuesql.cn/

  我的Anwser: Github 仓库

• 国外SQL自学：https://sqlbolt.com/

• 格式化工具：https://tool.lu/sql/

执行顺序

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

按照每组最大的值去筛选结果

参考：Get records with max value for each group of grouped SQL results

Person | Group | Age
---
Bob  | 1     | 32  
Jill | 1     | 34  
Shawn| 1     | 42  
Jake | 2     | 29  
Paul | 2     | 36  
Laura| 2     | 39  

筛选出每组中年龄最大的人的信息

Shawn | 1     | 42    
Laura | 2     | 39  

代码

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT `Group`, MAX(Age) AS max_age
    FROM yourTable
    GROUP BY `Group`
) t2
    ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age;

创建 create_time 和 update_time

DEFAULT STRING

Navicat等数据库工具 default 默认值填写

DEFAULT CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

添加/修改

ALTER TABLE `table`
ADD[MODIFY] COLUMN `created_at` datatime[timestamp] NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `last_column`
ADD[MODIFY] COLUMN `updated_at` datatime[timestamp] NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`