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
|
SELECT id,stuName,age,sex,gradeName FROM t_student ;
SELECT stuName,id,age,sex,gradeName FROM t_student ;
SELECT * FROM t_student;
SELECT stuName,gradeName FROM t_student;
# 范围查询
SELECT * FROM t_student WHERE id=1;
SELECT * FROM t_student WHERE age>22;
SELECT * FROM t_student WHERE age IN (21,23);
SELECT * FROM t_student WHERE age NOT IN (21,23);
SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;
# 模糊查询（可用正则表达式）
SELECT * FROM t_student WHERE stuName LIKE '张三';
SELECT * FROM t_student WHERE stuName LIKE '张三%';
SELECT * FROM t_student WHERE stuName LIKE '张三__';
SELECT * FROM t_student WHERE stuName LIKE '%张三%';
SELECT * FROM t_student WHERE sex IS NULL;
SELECT * FROM t_student WHERE sex IS NOT NULL;
SELECT * FROM t_student WHERE gradeName='一年级' AND age=23
SELECT * FROM t_student WHERE gradeName='一年级' OR age=23
# 加DISTINCT去重
SELECT DISTINCT gradeName FROM t_student;
# 默认的是ASC升序
SELECT * FROM t_student ORDER BY age ASC;
# DESC是降序
SELECT * FROM t_student ORDER BY age DESC;
SELECT * FROM t_student GROUP BY gradeName;
# 分组查询
# 使用了聚合函数GROUP_CONCAT()
SELECT gradeName,GROUP_CONCAT(stuName) AS stuName FROM t_student GROUP BY gradeName;
# 使用了聚合函数COUNT()
SELECT gradeName,COUNT(stuName) FROM AS total t_student GROUP BY gradeName;
# HAVING用于限制
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;
# WITH ROLLUP增加个总结行
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
# 分页查询，第一个参数是初始位置，从0开始，第二个参数是要查的数量
SELECT * FROM t_student LIMIT 0,5;
SELECT * FROM t_student LIMIT 5,5;
SELECT * FROM t_student LIMIT 10,5;
|
