students表
idclass_idnamegenderscore
1
1
小明
M
90
2
1
小红
F
95
3
1
小军
M
88
4
1
小米
F
73
5
2
小白
F
81
6
2
小兵
M
55
7
2
小林
M
85
8
3
小新
F
91
9
3
小王
M
89
10
3
小丽
F
88
classes表
idname
1
一班
2
二班
3
三班
4
四班
①基本查询
SELECT * FROM students;
SELECT * FROM classes;
查询出students表,classes表的所有数据。注意:查询结果也是一个二维表,它包含列名和每一行的数据。
②条件查询
常用的条件表达式
条件表达式举例1表达式举例2说明
使用=判断相等
score = 80
name = 'abc'
字符串需要用单引号括起来
使用>判断大于
score > 80
name > 'abc'
字符串比较根据ASCII码,中文字符比较根据数据库设置
使用>=判断大于或相等
score >= 80
name >= 'abc'
使用
score < 80
name <= 'abc'
使用<=判断小于或相等
score <= 80
name <= 'abc'
使用<>判断不相等
score <> 80
name <> 'abc'
使用LIKE判断相似
name LIKE 'ab%'
name LIKE '%bc%'
%表示任意字符,例如'ab%'将匹配'ab','abc','abcd'
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
查询出students表符合条件“分数在80分或以上”,并且还符合条件“男生”
SELECT * FROM students WHERE NOT class_id = 2;
查询出students表“不是2班的学生”
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
查询出students表分数在80以下或者90以上,并且是男生
③投影查询
SELECT id, score points, name FROM students;
从students表中返回id、score和name这三列,列名score重命名为points,而id和name列名保持不变
④排序
SELECT id, name, gender, score FROM students ORDER BY score;
查询出students表按照成绩从低到高进行排序,加上DESC表示“倒序”
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
查询出students表使用ORDER BY score DESC, gender表示先按score列倒序,如果有相同分数的,再按gender列排序
SELECT id, name, gender, score FROM students WHERE class_id = 1 ORDER BY score DESC;
查询出students表有WHERE子句,那么ORDER BY子句要放到WHERE子句后面。例如,查询一班的学生成绩,并按照倒序排序
⑤分页查询
SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;
查询出students表把结果集分页,每页3条记录。要获取第1页的记录,可以使用LIMIT 3 OFFSET 0
可见,分页查询的关键在于,首先要确定每页需要显示的结果数量pageSize(这里是3),然后根据当前页的索引pageIndex(从1开始),确定LIMIT和OFFSET应该设定的值:
LIMIT总是设定为pageSize;
OFFSET计算公式为pageSize * (pageIndex - 1)。
这样就能正确查询出第N页的记录集。
⑥聚合查询
SELECT COUNT(*) num FROM students;
查询students表一共有多少条记录
SELECT AVG(score) average FROM students WHERE gender = 'M';
查询students表统计男生的平均成绩
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
查询students表各班的男生和女生人数
SELECT class_id,AVG(score) point FROM students GROUP BY class_id;
查询查出每个班级的平均分
SELECT class_id,gender,AVG(score) FROM students GROUP BY class_id,gender;
查询查出每个班级男生和女生的平均分
聚合查询的WHERE条件没有匹配到任何行,COUNT()会返回0,而SUM()、AVG()、MAX()和MIN()会返回NULL
⑦连接查询
假设我们希望结果集同时包含所在班级的名称,上面的结果集只有class_id列,缺少对应班级的name列。
现在问题来了,存放班级名称的name列存储在classes表中,只有根据students表的class_id,找到classes表对应的行,再取出name列,就可以获得班级名称。
这时,连接查询就派上了用场。我们先使用最常用的一种内连接——INNER JOIN来实现
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
查询查出students表的所有学生信息包含所在班级的名称
INNER JOIN查询的写法是:
先确定主表,仍然使用FROM 的语法;
再确定需要连接的表,使用INNER JOIN 的语法;
然后确定连接条件,使用ON ,这里的条件是s.class_id = c.id,表示students表的class_id列与classes表的id列相同的行需要连接;
可选:加上WHERE子句、ORDER BY等子句。
多数内容转载自廖雪峰老师文章,仅供学习参考使用