查询
条件查询
SELECT * FROM <表名> WHERE <条件>
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
投影查询
SELECT 列1, 列2, 列3 FROM ...
SELECT id, score points, name FROM students WHERE gender = 'M';
//可以重命名score为points
排序
ORDER BY
加上DESC
表示“倒序
ORDER BY score DESC, gender
表示先按score
列倒序,如果有相同分数的,再按gender
列排序
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
分页查询
LIMIT <M> OFFSET <N>
对结果集从N号记录开始,最多取M条。注意SQL记录集的索引从0开始。
OFFSET
超过了查询的最大数量并不会报错,而是得到一个空的结果集。
OFFSET
是可选的,如果只写LIMIT 15
,那么相当于LIMIT 15 OFFSET 0
- 在MySQL中,
LIMIT 15 OFFSET 30
还可以简写成LIMIT 30, 15
。 - 使用
LIMIT <M> OFFSET <N>
分页时,随着N
越来越大,查询效率也会越来越低。
聚合查询
SELECT COUNT(*) <别名> FROM <表名>;
COUNT(*)
或COUNT(id)
表示查询所有列的行数
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
函数 | 说明 |
---|---|
AVG | 计算某一列的合计值,该列必须为数值类型 |
SUM | 计算某一列的平均值,该列必须为数值类型 |
MAX | 计算某一列的最大值 |
MIN | 计算某一列的最小值 |
MAX()
和MIN()
函数并不限于数值类型。如果是字符类型,MAX()和MIN()会返回排序最后和排序最前的字符。
如果聚合查询的WHERE条件没有匹配到任何行,COUNT()
会返回0
,而SUM()、AVG()、MAX()
和MIN()
会返回NULL
请使用一条SELECT查询查出每个班级男生和女生的平均分:
SELECT AVG(score) average FROM students GROUP BY class_id , gender;
分组聚合
统计各班的男生和女生人数:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
笛卡尔查询
SELECT * FROM <表1> <表2>
结果集的列数是表1和表2的列数之和,行数是表1和表2的行数之积。
可利用投影查询“设置列的别名”:
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c; //表名也可以起别名
WHERE s.gender = 'M' AND c.id = 1; //可以添加WHERE条件
连接查询
连接查询对多个表进行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;
INNER JOIN只返回同时存在于两张表的行数据,由于students
表的class_id
包含1,2,3,classes
表的id
包含1,2,3,4,所以,INNER JOIN
根据条件s.class_id = c.id
返回的结果集仅包含1,2,3。
- 先确定主表,仍然使用FROM <表1>的语法;
- 再确定需要连接的表,使用INNER JOIN <表2>的语法;
- 然后确定连接条件,使用ON <条件…>,这里的条件是s.class_id = c.id,表示students表的class_id列与classes表的id列相同的行需要连接;
- 可选:加上WHERE子句、ORDER BY等子句。
RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在,那么结果集就会以NULL
填充剩下的字段。
LEFT OUTER JOIN则返回左表都存在的行。如果我们给students
表增加一行,并添加class_id=5
,由于classes
表并不存在id=5
的行,所以,LEFT OUTER JOIN
的结果会增加一行,对应的class_name
是NULL
假设查询语句是:
SELECT ... FROM tableA XX
JOIN tableB
ON tableA.column1 = tableB.column2;