MySQL 笔记

这是一篇关于 MySQL 的语法笔记,主要供自己和新手们查看。

参考资料

写在最前面…当然是不要重复造轮子!做笔记也是如此!So ~ 先引用一发:

遣词造句

像翻译英语句子一样来记录语法。

按用户名查询用户的所有昵称

1
2
SELECT DISTINCT(nick) FROM TABLE_SAMPLE WHERE name IN
(SELECT name FROM TABLE_SAMPLE GROUP BY name)

按月以及平台查询用户数

1
2
3
SELECT
DATE_FORMAT(time, '%Y-%m') months, COUNT(DISTINCT(uid)) AS user_count
FROM TABLE_SAMPLE GROUP BY months, platforms

查询时间单位的数据总和

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 每分钟
SELECT
SUM(data) AS sum
FROM TABLE_SAMPLE GROUP BY YEAR(time), MONTH(time), DAY(time), HOUR(time), MINUTE(time)

# 每10分钟
SELECT
SUM(data) AS sum
FROM TABLE_SAMPLE GROUP BY YEAR(time), MONTH(time), DAY(time), HOUR(time), FLOOR(MINUTE(time)/10)

# 每小时
SELECT
SUM(data) AS sum
FROM TABLE_SAMPLE GROUP BY YEAR(time), MONTH(time), DAY(time), HOUR(time)

# 后面依此类推...

查询每小时的数据总和

一次查询里查询多样数据

1
2
3
SELECT
(SELECT uid FROM TABLE_SAMPLE_1 WHERE name = '我最帅') AS uid,
(SELECT height FROM TABLE_SAMPLE_2 WHERE name = '我最帅') AS height

一次更新多条数据

1
2
3
4
5
6
UPDATE TABLE_SAMPLE
SET credit = credit + CASE
WHEN name = '我最帅' THEN 1000
WHEN name = '我不帅' THEN 0 END,
time = CURRENT_DATE
WHERE name IN ('我最帅', '我不帅')

一次插入多条数据

1
2
INSERT INTO TABLE_SAMPLE (name, credit)
VALUES ('我最帅', 1000), ('我不帅', 0)

Tips

  • 字串存在: LOCATE(你要查的字符串,对应的域) > 0
  • 字串连接:CONCAT(STR1, STR2, ...)CONCAT_WS 第一个参数是分隔符
  • 时间:FROM_UNIXTIMEUNIX_TIMESTAMP
  • 查看倒数第 20 到 30 笔数据:ORDER BY id DESC LIMIT 20, 30
  • 日期:CURDATE() = DATE(NOW())