基本查询
假设表名是students,要查询students表的所有行,我们用如下SQL语句:
SELECT * FROM students;
条件查询
SELECT语句可以通过WHERE条件来设定查询条件,查询结果是满足查询条件的记录。例如,要指定条件“分数在80分或以上的学生”。
SELECT * FROM students WHERE score >= 80;
条件表达式可以用 AND 表达满足条件1并且满足条件2。例如,符合条件“分数在80分或以上”,并且还符合条件“男生”,把这两个条件写出来:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
第二种条件是 OR ,表示满足条件1或者满足条件2。例如,把上述AND查询的两个条件改为OR,查询结果就是“分数在80分或以上”或者“男生”,满足任意之一的条件即选出该记录:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
第三种条件是NOT ,表示“不符合该条件”的记录。例如,写一个“不是2班的学生”这个条件
SELECT * FROM students WHERE NOT class_id = 2;
要组合三个或者更多的条件,就需要用小括号()表示如何进行条件运算。例如,编写一个复杂的条件:分数在80以下或者90以上,并且是男生:
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
✳️如果不加括号,条件运算按照NOT、AND、OR的优先级进行,即NOT优先级最高,其次是AND,最后是OR。加上括号可以改变优先级。
投影查询
使用SELECT * FROM WHERE 可以选出表中的若干条记录。例如,从students表中返回id、score和name这三列:
SELECT id, score, name FROM students;
使用SELECT 列1, 列2, 列3 FROM ...时,还可以给每一列起个别名,这样,结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。
例如,以下SELECT语句将列名score重命名为points,而id和name列名保持不变:
SELECT id, score points, name FROM students;
投影查询同样可以接WHERE条件,实现复杂的查询:
SELECT id, score points, name FROM students WHERE gender = 'M';
排序
如果我们要根据其他条件排序可以加上ORDER BY子句。例如按照成绩从低到高进行排序:
SELECT id, name, gender, score FROM students ORDER BY score;
如果要反过来,按照成绩从高到底排序,我们可以加上DESC表示“倒序”:
SELECT id, name, gender, score FROM students ORDER BY score DESC;
如果score列有相同的数据,要进一步排序,可以继续添加列名。例如,使用ORDER BY score DESC, gender表示先按score列倒序,如果有相同分数的,再按gender列排序:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
默认的排序规则是ASC:“升序”,即从小到大。ASC可以省略,即ORDER BY score ASC和ORDER BY score效果一样。
如果有WHERE子句,那么ORDER BY子句要放到WHERE子句后面。例如,查询一班的学生成绩,并按照倒序排序:
SELECTid, name, gender, scoreFROMstudentsWHERE class_id = 1
ORDER BY score DESC;
分页查询
分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT OFFSET 子句实现。
我们把结果集分页,每页3条记录。要获取第1页的记录,可以使用LIMIT 3 OFFSET 0:
SELECTid, name, gender, scoreFROMstudentsORDER BY score DESCLIMIT3 OFFSET 0;
如果要查询第2页,那么我们只需要“跳过”头3条记录,也就是对结果集从3号记录开始查询,把OFFSET设定为3:
SELECTid, name, gender, scoreFROMstudentsORDER BY score DESCLIMIT3 OFFSET 3;
类似的,查询第3页的时候,OFFSET应该设定为6:
SELECTid, name, gender, scoreFROMstudentsORDER BY score DESCLIMIT3 OFFSET 6;
LIMIT总是设定为pageSize;
OFFSET计算公式为pageSize * (pageIndex - 1)。
聚合查询
如果我们要统计一张表的数据量,例如,想查询students表一共有多少条记录。
SELECT COUNT(*) FROM students;
通常,使用聚合查询时,我们应该给列名设置一个别名,便于处理结果:
SELECT COUNT(*) num FROM students;
聚合查询同样可以使用WHERE条件,因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等:
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
除了COUNT()函数外,SQL还提供了如下聚合函数:
函数说明
SUM
计算某一列的合计值,该列必须为数值类型
AVG
计算某一列的平均值,该列必须为数值类型
MAX
计算某一列的最大值
MIN
计算某一列的最小值
注意,MAX()和MIN()函数并不限于数值类型。如果是字符类型,MAX()和MIN()会返回排序最后和排序最前的字符。
要统计男生的平均成绩,我们用下面的聚合查询:
SELECT AVG(score) average FROM students WHERE gender = 'M';
分组
如果我们要统计一班的学生数量,我们知道,可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。如果要继续统计二班、三班的学生数量,SQL提供了“分组聚合”的功能。我们观察下面的聚合查询:
SELECT COUNT(*) num FROM students GROUP BY class_id;
num
4
3
3
但是这3行结果分别是哪三个班级的,不好看出来,所以我们可以把class_id列也放入结果集中:
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
class_idnum
1
4
2
3
3
3
也可以使用多个列进行分组。例如,我们想统计各班的男生和女生人数:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
class_idgendernum
1
M
2
1
F
2
2
F
1
2
M
2
3
F
2
3
M
1
(后补充多表查询与连接查询)
------------恢复内容开始------------
基本查询
假设表名是students,要查询students表的所有行,我们用如下SQL语句:
SELECT * FROM students;
条件查询
SELECT语句可以通过WHERE条件来设定查询条件,查询结果是满足查询条件的记录。例如,要指定条件“分数在80分或以上的学生”。
SELECT * FROM students WHERE score >= 80;
条件表达式可以用 AND 表达满足条件1并且满足条件2。例如,符合条件“分数在80分或以上”,并且还符合条件“男生”,把这两个条件写出来:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
第二种条件是 OR ,表示满足条件1或者满足条件2。例如,把上述AND查询的两个条件改为OR,查询结果就是“分数在80分或以上”或者“男生”,满足任意之一的条件即选出该记录:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
第三种条件是NOT ,表示“不符合该条件”的记录。例如,写一个“不是2班的学生”这个条件
SELECT * FROM students WHERE NOT class_id = 2;
要组合三个或者更多的条件,就需要用小括号()表示如何进行条件运算。例如,编写一个复杂的条件:分数在80以下或者90以上,并且是男生:
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
✳️如果不加括号,条件运算按照NOT、AND、OR的优先级进行,即NOT优先级最高,其次是AND,最后是OR。加上括号可以改变优先级。
投影查询
使用SELECT * FROM WHERE 可以选出表中的若干条记录。例如,从students表中返回id、score和name这三列:
SELECT id, score, name FROM students;
使用SELECT 列1, 列2, 列3 FROM ...时,还可以给每一列起个别名,这样,结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。
例如,以下SELECT语句将列名score重命名为points,而id和name列名保持不变:
SELECT id, score points, name FROM students;
投影查询同样可以接WHERE条件,实现复杂的查询:
SELECT id, score points, name FROM students WHERE gender = 'M';
排序
如果我们要根据其他条件排序可以加上ORDER BY子句。例如按照成绩从低到高进行排序:
SELECT id, name, gender, score FROM students ORDER BY score;
如果要反过来,按照成绩从高到底排序,我们可以加上DESC表示“倒序”:
SELECT id, name, gender, score FROM students ORDER BY score DESC;
如果score列有相同的数据,要进一步排序,可以继续添加列名。例如,使用ORDER BY score DESC, gender表示先按score列倒序,如果有相同分数的,再按gender列排序:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
默认的排序规则是ASC:“升序”,即从小到大。ASC可以省略,即ORDER BY score ASC和ORDER BY score效果一样。
如果有WHERE子句,那么ORDER BY子句要放到WHERE子句后面。例如,查询一班的学生成绩,并按照倒序排序:
SELECTid, name, gender, scoreFROMstudentsWHERE class_id = 1
ORDER BY score DESC;
分页查询
分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT OFFSET 子句实现。
我们把结果集分页,每页3条记录。要获取第1页的记录,可以使用LIMIT 3 OFFSET 0:
SELECTid, name, gender, scoreFROMstudentsORDER BY score DESCLIMIT3 OFFSET 0;
如果要查询第2页,那么我们只需要“跳过”头3条记录,也就是对结果集从3号记录开始查询,把OFFSET设定为3:
SELECTid, name, gender, scoreFROMstudentsORDER BY score DESCLIMIT3 OFFSET 3;
类似的,查询第3页的时候,OFFSET应该设定为6:
SELECTid, name, gender, scoreFROMstudentsORDER BY score DESCLIMIT3 OFFSET 6;
LIMIT总是设定为pageSize;
OFFSET计算公式为pageSize * (pageIndex - 1)。
聚合查询
如果我们要统计一张表的数据量,例如,想查询students表一共有多少条记录。
SELECT COUNT(*) FROM students;
通常,使用聚合查询时,我们应该给列名设置一个别名,便于处理结果:
SELECT COUNT(*) num FROM students;
聚合查询同样可以使用WHERE条件,因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等:
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
除了COUNT()函数外,SQL还提供了如下聚合函数:
函数说明
SUM
计算某一列的合计值,该列必须为数值类型
AVG
计算某一列的平均值,该列必须为数值类型
MAX
计算某一列的最大值
MIN
计算某一列的最小值
注意,MAX()和MIN()函数并不限于数值类型。如果是字符类型,MAX()和MIN()会返回排序最后和排序最前的字符。
要统计男生的平均成绩,我们用下面的聚合查询:
SELECT AVG(score) average FROM students WHERE gender = 'M';
分组
如果我们要统计一班的学生数量,我们知道,可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。如果要继续统计二班、三班的学生数量,SQL提供了“分组聚合”的功能。我们观察下面的聚合查询:
SELECT COUNT(*) num FROM students GROUP BY class_id;
num
4
3
3
但是这3行结果分别是哪三个班级的,不好看出来,所以我们可以把class_id列也放入结果集中:
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
class_idnum
1
4
2
3
3
3
也可以使用多个列进行分组。例如,我们想统计各班的男生和女生人数:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
class_idgendernum
1
M
2
1
F
2
2
F
1
2
M
2
3
F
2
3
M
1
多表查询
同时从students表和classes表的“乘积”,即查询数据,可以这么写:
SELECT * FROM students, classes;
使用多表查询可以获取M x N行记录;
多表查询的结果集可能非常巨大,要小心使用。
连接查询
选出所有学生,同时返回班级名称
SELECTs.id, s.name, s.class_id, c.name class_name, s.gender, s.scoreFROMstudents sINNER JOINclasses cON s.class_id = c.id;
假设查询语句是:
SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;
我们把tableA看作左表,把tableB看成右表,那么INNER JOIN是选出两张表都存在的记录:
LEFT OUTER JOIN是选出左表存在的记录:
RIGHT OUTER JOIN是选出右表存在的记录:
FULL OUTER JOIN则是选出左右表都存在的记录: