在关系数据库中,最常用的操作就是查询。
要查询数据库表的数据,我们使用如下的SQL语句:
SELECT * FROM <表名>
假设表名是students,要查询students表的所有行
使用SELECT * FROM students时,SELECT是关键字,表示将要执行一个查询,*表示“所有列”,FROM表示将要从哪个表查询,本例中是students表。
该SQL将查询出students表的所有数据。注意:查询结果也是一个二维表,它包含列名和每一行的数据。
要查询classes表的所有行,我们用如下SQL语句:
SELECT * FROM classes;
SELECT语句其实并不要求一定要有FROM子句。
SELECT 100+200;
上述查询会直接计算出表达式的结果。虽然SELECT可以用作计算,但它并不是SQL的强项。但是,不带FROM子句的SELECT语句有一个有用的用途,就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。
使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。
SELECT查询的结果是一个二维表。
使用SELECT * FROM <表名>可以查询到一张表的所有记录。但是,很多时候,我们并不希望获得所有记录,而是根据条件选择性地获取指定条件的记录,例如,查询分数在80分以上的学生记录。在一张表有数百万记录的情况下,获取所有记录不仅费时,还费内存和网络带宽。
SELECT语句可以通过WHERE条件来设定查询条件,查询结果是满足查询条件的记录。例如,要指定条件“分数在80分或以上的学生”,写成WHERE条件就是SELECT * FROM students WHERE score >= 80。
其中,WHERE关键字后面的score >= 80就是条件。score是列名,该列存储了学生的成绩,因此,score >= 80就筛选出了指定条件的记录:
SELECT * FROM students WHERE score >= 80;
因此,条件查询的语法就是:
SELECT * FROM <表名> WHERE <条件表达式>
条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如,符合条件“分数在80分或以上”,并且还符合条件“男生”,把这两个条件写出来:
条件1:根据score列的数据判断:score >= 80;
条件2:根据gender列的数据判断:gender = ‘M’,注意gender列存储的是字符串,需要用单引号括起来。
就可以写出WHERE条件:score >= 80 AND gender = ‘M’:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
第二种条件是<条件1> OR <条件2>,表示满足条件1或者满足条件2。例如,把上述AND查询的两个条件改为OR,查询结果就是“分数在80分或以上”或者“男生”,满足任意之一的条件即选出该记录:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
很显然OR条件要比AND条件宽松,返回的符合条件的记录也更多。
第三种条件是NOT <条件>,表示“不符合该条件”的记录。例如,写一个“不是2班的学生”这个条件,可以先写出“是2班的学生”:class_id = 2,再加上NOT:NOT class_id = 2:
SELECT * FROM students WHERE NOT class_id = 2;
上述NOT条件NOT class_id = 2其实等价于class_id <> 2,因此,NOT查询不是很常用。
要组合三个或者更多的条件,就需要用小括号()表示如何进行条件运算。例如,编写一个复杂的条件:分数在80以下或者90以上,并且是男生:
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
如果不加括号,条件运算按照NOT、AND、OR的优先级进行,即NOT优先级最高,其次是AND,最后是OR。加上括号可以改变优先级。
常用的条件表达式
使用=判断相等
使用>判断大于
使用>=判断大于或相等
使用<判断小于
使用<=判断小于或相等
使用<>判断不相等
使用LIKE判断相似
字符串比较根据ASCII码,中文字符比较根据数据库设置
name LIKE ‘ab%’ name LIKE ‘%bc%’ %表示任意字符,例如’ab%‘将匹配’ab’,‘abc’,‘abcd’
小结
通过WHERE条件查询,可以筛选出符合指定条件的记录,而不是整个表的所有记录。
使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的,即结果集的所有列与原表的所有列都一一对应。
如果我们只希望返回某些列的数据,而不是所有列的数据,我们可以用SELECT 列1, 列2, 列3 FROM …,让结果集仅包含指定列。这种操作称为投影查询。
例如,从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';
小结
使用SELECT *表示查询表的所有列,使用SELECT 列1, 列2, 列3则可以仅返回指定列,这种操作称为投影。
SELECT语句可以对结果集的列进行重命名。
我们使用SELECT查询时,细心的读者可能注意到,查询结果集通常是按照id排序的,也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办?可以加上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子句后面。例如,查询一班的学生成绩,并按照倒序排序:
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
这样,结果集仅包含符合WHERE条件的记录,并按照ORDER BY的设定排序。
小结
使用ORDER BY可以对结果集进行排序;
可以对多列进行升序、倒序排序。
使用SELECT查询时,如果结果集数据量很大,比如几万行数据,放在一个页面显示的话数据量太大,不如分页显示,每次显示100条。
要实现分页功能,实际上就是从结果集中显示第1~ 100条记录作为第1页,显示第101~200条记录作为第2页,以此类推。
因此,分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT OFFSET 子句实现。我们先把所有学生按照成绩从高到低进行排序:
SELECT id, name, gender, score FROM students ORDER BY score DESC;
现在,我们把结果集分页,每页3条记录。要获取第1页的记录,可以使用LIMIT 3 OFFSET 0:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
上述查询LIMIT 3 OFFSET 0表示,对结果集从0号记录开始,最多取3条。注意SQL记录集的索引从0开始。
如果要查询第2页,那么我们只需要“跳过”头3条记录,也就是对结果集从3号记录开始查询,把OFFSET设定为3:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
类似的,查询第3页的时候,OFFSET应该设定为6:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;
查询第4页的时候,OFFSET应该设定为9:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;
由于第4页只有1条记录,因此最终结果集按实际数量1显示。LIMIT 3表示的意思是“最多3条记录”。
可见,分页查询的关键在于,首先要确定每页需要显示的结果数量pageSize(这里是3),然后根据当前页的索引pageIndex(从1开始),确定LIMIT和OFFSET应该设定的值:
LIMIT总是设定为pageSize;
OFFSET计算公式为pageSize * (pageIndex - 1)。
这样就能正确查询出第N页的记录集。
如果原本记录集一共就10条记录,但我们把OFFSET设置为20,会得到什么结果呢?
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 20;
OFFSET超过了查询的最大数量并不会报错,而是得到一个空的结果集。
注意
OFFSET是可选的,如果只写LIMIT 15,那么相当于LIMIT 15 OFFSET 0。
在MySQL中,LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
使用LIMIT OFFSET 分页时,随着N越来越大,查询效率也会越来越低。
小结
使用LIMIT OFFSET 可以对结果集进行分页,每次查询返回结果集的一部分;
分页查询需要先确定每页的数量和当前页数,然后确定LIMIT和OFFSET的值。