1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
| # WHERE子句
# 搭配SELECT,UPDATE,DELETE等
SELECT * FROM player WHERE level > 1 AND (level < 5 OR exp > 1) AND exp < 5;
# null的特判
SELECT * FROM player WHERE email is null;
SELECT * FROM player WHERE email is NOT null;
SELECT * FROM player WHERE email = ''; # 空字符串，并非空值NULL
# IN
SELECT * FROM player WHERE level IN (1,3,5);
# BETWEEN
SELECT * FROM player WHERE level BETWEEN 1 AND 10;
# NOT
SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10;
# LIKE
# %任意个字符，_任意一个字符
SELECT * FROM player WHERE name LIKE '%王%';
SELECT * FROM player WHERE name LIKE '王_';
# REGEXP
# 正则表达式：. 任意一个字符 ^ 开头 $ 结尾 [abc] 其中任意一个字符 [a-z] 范围内任意一个字符 A|B A或B
SELECT * FROM player WHERE name REGEXP '^[王张].$' # 王或张姓二字
SELECT * FROM player WHERE email REGEXP '^zhangsan';
SELECT * FROM player WHERE email REGEXP '^[abc]';
SELECT * FROM player WHERE email REGEXP 'net$';
# ORDER BY
SELECT * FROM player ORDER BY level; # 默认升序
SELECT * FROM player ORDER BY level DESC; # 降序
SELECT * FROM player ORDER BY level DESC, exp ASC; # 先level降序，然后exp升序
# 聚合函数
# AVG() COUNT() MAX() MIN() SUM()...
SELECT COUNT(*) FROM player;
SELECT AVG(level) FROM player;
# GROUP BY
SELECT * FROM player GROUP BY sex;
SELECT level, COUNT(*) FROM player GROUP BY level; # 查看每个等级玩家数量
# HAVING 筛选分组后的数据
SELECT level, count(level) from player group by level having count(level) > 4 order by count(level) DESC; # 查看大于4每个等级玩家数量并按人数降序排序
# LIMIT
SELECT * FROM player LIMIT 10; -- 返回前10条
SELECT * FROM player LIMIT 4, 3; -- 偏移4条，返回3条（第5-7条）
SELECT * FROM player LIMIT 3 OFFSET 4; -- 现代推荐写法，同上
# 复杂些的例子：
SELECT substr(name,1,1),count(substr(name,1,1)) from player
GROUP BY substr(name,1,1)
HAVING count(substr(name,1,1)) >= 5
ORDER BY count(substr(name,1,1)) DESC
LIMIT 4,3;
# DISTINCT 去重复
SELECT DISTINCT sex from player;
# UNION 两个查询结果并集（会去重） UNION ALL 不去重
SELECT * FROM player WHERE level BETWEEN 1 AND 3
UNION (ALL)
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
# INTERSECT 两个查询结果交集
SELECT * FROM player WHERE level BETWEEN 1 AND 3
INTERSECT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
# EXCEPT 两个查询结果差集
SELECT * FROM player WHERE level BETWEEN 1 AND 3
EXCEPT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
# 常用函数
# 字符串函数
SELECT CONCAT('等级:', level) FROM player;
SELECT LENGTH(name), UPPER(name), LOWER(name) FROM player;
SELECT SUBSTRING(name, 1, 2) FROM player; -- 截取前两个字符
SELECT REPLACE(name, '张', '李') FROM player;
# 日期函数
SELECT NOW(), CURDATE(), CURTIME();
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM player;
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM player;
# 数值函数
SELECT ROUND(gold, 1), CEIL(gold), FLOOR(gold) FROM player;
SELECT RAND(); -- 0~1 随机数
# 空值处理
SELECT IFNULL(email, '未填写') FROM player;
SELECT COALESCE(email, phone, '未填写') FROM player; -- 返回第一个非 NULL 值
# 条件判断
SELECT name, CASE WHEN level >= 10 THEN '高手' WHEN level >= 5 THEN '老手' ELSE '新手' END AS grade FROM player;
|
