mysql 语句常用

1.count

SELECT
    `KEY`,
    COUNT(*) count01, # 统计出现次数
    COUNT(`KEY`) count02, # 可以引用某个字段用于统计
    COUNT(`value`) count03, # 但是null不计算次数
    COUNT(DISTINCT `value`) count04 # 加上DISTINCT关键字，可以去除重复，得到不同值的数量
FROM
    temp
GROUP BY
    `KEY`;

2.sum和avg

SELECT
    `KEY`,
    SUM(`value`) sum01, # 对某个字段求和，null会被忽略
    SUM(1) sum02, # 也可以传递常量，sum(n)等于count(*)*n
    SUM(DISTINCT `value`) sum03, # 加上DISTINCT关键字，可以去除重复
    AVG(`value`) avg01, # 对某个字段去平均值，null会被忽略
    AVG(1) avg02, # 也可以传递常量，但是没有实际意义，avg(n)等于n
    AVG(DISTINCT `value`) avg03, # 加上DISTINCT关键字，可以去除重复
    SUM(`value`) / COUNT(`value`) sc01, # avg等效于sum/count
    SUM(DISTINCT `value`) / COUNT(DISTINCT `value`) sc02,
    SUM(`KEY`) sumstr01, AVG(`KEY`) avgstr01, # 如果传递非数值类参数，会自动转换类型
    SUM('1abc2') sumstr02, AVG(CAST('1abc2' AS SIGNED)) avgstr02 # Mysql的转换机制是"尽力而为"，无论隐式还是显式
FROM
    temp
GROUP BY
    `KEY`;

3. MIN、MAX

SELECT
    MAX(`KEY`) MAX01, # MAX用于获取最大值
    MAX(`value`) MAX02, # NULL依然会被忽略
    MAX(DISTINCT `KEY`) MAX03, # 支持DISTINCT关键字，但是没有实际意义
    MIN(`KEY`) MIN01, # MIN用于获取最小值，用法和注意事项与MAX完全一致
    MIN(`value`) MIN02,
    MIN(DISTINCT `KEY`) MIN03
FROM
    temp;

4.GROUP BY

SELECT
    `KEY`,
    COUNT(*) count01
FROM
    temp
GROUP BY
    `KEY`, CONCAT('KEY IS ', `KEY`), 'KEY' # 指定分组字段，允许多个字段组合分组，支持表达式和常量，但是传递常量无意义
    ;

 
SELECT
    `KEY` KEYWORD,
    COUNT(*) count01
FROM
    temp
GROUP BY
    KEYWORD # 也支持使用别名分组

 
SELECT
    `KEY` KEYWORD,
    COUNT(*) count01
FROM
    temp
GROUP BY
    1 # 还支持使用字段序号，从1开始
    
 
 
SELECT
    `KEY` KEYWORD,
    COUNT(*) count01
FROM
    temp
GROUP BY
    1 DESC # 还可以写入排序，简化SQL语句

 
 
SELECT
    `KEY`,
    `value`,
    COUNT(*) count01
FROM
    temp
GROUP BY
    `KEY`, `value`
WITH ROLLUP # 带上WITH ROLLUP，可以实现小计+总计的功能：分别以KEY+VALUE，KEY，NULL（可以看成是常量，所有数据在一个组中）分组

5.having

SELECT
    `KEY`,
    COUNT(*) count01
FROM
    temp
GROUP BY
    `KEY`
HAVING
    SUM(`VALUE`) > 100 # 过滤数据，语法与WHERE一致，但是HAVING是在分组聚合之后过滤，WHERE在GROUP BY之前

 
 
SELECT
    `KEY`,
    COUNT(*) count01
FROM
    temp
HAVING
    SUM(`VALUE`) > 100 # 如果在没有GROUP BY字句的情况下使用HAVING，相当于GROUP BY 常量，所有的数据会放在一个组中

 
 
SELECT
    `KEY`,
    COUNT(*) count01
FROM
    temp
GROUP BY
    `KEY`
HAVING
    `value` < 100 # 既没有出现在GROUP BY字句，又没有使用聚合函数包裹的列，不能使用HAVING过滤
    ;
/*
1054 - Unknown column 'value' in 'having clause'
*/

6 时间处理函数
6.1 当前时间

SELECT
  NOW() time01, # 获取当前时间
  SYSDATE() time02, # 获取当前时间
  SLEEP(3), # 等待3秒
  NOW() time03, # NOW获取的是语句开始执行时的时间
  SYSDATE() time04 # SYSDATE获取的是函数被执行时的时间

6.2.时间转换函数

SELECT
  DATE_FORMAT(NOW(), '%Y-%m-%d') time01, # 按参数2格式化日期，返回格式化后的文本
  DATE_FORMAT(NOW(), '今天是：%Y-%m-%d，这是利用%%Y-%%m-%%d格式化所得') time02, # 允许加入其他字符，%是转义字符
  STR_TO_DATE('2018-08-25', '%Y-%m-%d') time03 # DATE_FORMAT的反函数
  

date_from可选用的格式：

%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

6.3 时间计算函数

# 单位介绍
SELECT
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 YEAR) time01, # 年
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 QUARTER) time02, # 季度
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 MONTH) time03, # 月
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 WEEK) time04, # 星期
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 DAY) time05; # 天

 
SELECT
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 HOUR) time06, # 小时
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 MINUTE) time07, # 分钟
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 SECOND) time08, # 秒
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 MICROSECOND) time09; # 微秒

 
 
# 函数介绍
SELECT
  DATE_ADD('2018-08-25 14:00:00', INTERVAL 1 YEAR) time01, # 增加一个时间
  ADDDATE('2018-08-25 14:00:00', INTERVAL 1 YEAR) time02, # 等价于DATE_ADD
  ADDDATE('2018-08-25 14:00:00', 1) time03, # 默认单位是天
  ADDDATE('2018-08-25 14:00:00', INTERVAL '1 02:03:04' DAY_SECOND) time04, # 从DAY级到SECOND级，各自相加
  ADDTIME('2018-08-25 14:00:00', 1) time05, # 增加秒数
  DATE_SUB('2018-08-25 14:00:00', INTERVAL 1 YEAR) time06 # 减去一个时间，常用DATE_ADD并传递负数代替,对应的函数还有SUBDATE,SUBTIME
  ;

 
 
SELECT
  DATEDIFF('2018-08-25 14:00:00', '2018-08-24 01:00:00') time01, # 时间1-时间2，天后面部分舍弃（不是四舍五入）
  TIMEDIFF('2018-08-25 14:00:00', '2018-08-24 01:00:00') time02  # 时间1-时间2，得到时分秒格式

6.4时区转换

SELECT
  convert_tz( '2018-08-25 14:00:00', '+08:00', '+00:00' ) time01 # 时区转换，参数2是当前时区，参数3是目标时区

SELECT
  UNIX_TIMESTAMP() time01, # 获取当前时间戳
  UNIX_TIMESTAMP('2018-08-25 15:00:00') time02, # 转换一个时间到时间戳
  UNIX_TIMESTAMP('1970-01-01 08:00:30') time03, # 时间戳是距离格林威治时间1970-01-01 00:00:00的秒数，我的MySQL在+08:00时区
  FROM_UNIXTIME(UNIX_TIMESTAMP()) time04, # 时间戳格式化
  FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d') time05 # 指定格式的转换