students表存储了学生信息：
| id | class_id | name | gender | score |
|---|---|---|---|---|
| 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 | 85 |
classes表存储了班级信息：
| id | name |
|---|---|
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
1. 基本查询
要查询数据库表的数据，我们使用如下的SQL语句：
SELECT * FROM <表名>
假设表名是students，要查询students表的所有行，我们用如下SQL语句：
SELECT * FROM students;
| id | class_id | name | gender | score |
|---|---|---|---|---|
| 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 |
使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students表。
该SQL将查询出students表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据。
SELECT语句其实并不要求一定要有FROM子句,但不常用。
SELECT 100 + 200;
不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。
2.条件查询
SELECT * FROM <表名> WHERE <条件表达式>
常用的条件表达式
| 条件 | 表达式举例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’ |
分数在80以下或者90以上，并且是男生：
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
3.投影查询
使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。
如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。
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';
4.排序
我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照id排序的，也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。例如按照成绩从低到高进行排序：
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;
如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
5.分页
分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现。
把结果集分页，每页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开始。
分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：
LIMIT总是设定为pageSize；OFFSET计算公式为pageSize * (pageIndex - 1)。
注意
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。
6.聚合查询
如果我们要统计一张表的数据量，例如，想查询students表一共有多少条记录，难道必须用SELECT * FROM students查出来然后再数一数有多少行吗？
还好对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。
仍然以查询students表一共有多少条记录为例，使用SQL内置的COUNT()函数查询：
SELECT COUNT(*) FROM students;
COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。
通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：
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';
每页3条记录，如何通过聚合查询获得总页数？
SELECT CEILING(COUNT(*) / 3) FROM students;
分组
如果我们要统计一班的学生数量，我们知道，可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。如果要继续统计二班、三班的学生数量，难道必须不断修改WHERE条件来执行SELECT语句吗？
对于聚合查询，SQL还提供了“分组聚合”的功能。
SELECT class_id,
COUNT(*) num FROM students
GROUP BY class_id;
我们想统计各班的男生和女生人数：
SELECT class_id, gender,
COUNT(*) num FROM students
GROUP BY class_id, gender;
7.多表查询
SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。
例如，同时从students表和classes表的“乘积”，即查询数据
SELECT * FROM students, classes;
这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。
可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：
SELECT
students.id sid,
students.name,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
SQL还允许给表设置一个别名:
SELECT
s.id sid,
s.name,
s.score,
c.id cid,
c.name cname
FROM students s, classes c;
注意：
- 使用多表查询可以获取M x N行记录；
- 多表查询结果集可能非常庞大，慎用之。
8.连接查询
假设查询语句是：
SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;
我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：
LEFT OUTER JOIN是选出左表存在的记录：
RIGHT OUTER JOIN是选出右表存在的记录：
FULL OUTER JOIN则是选出左右表都存在的记录：
假设我们想查看学生表中的班级ID对应的班级名字，而两个列分属两张不同的表格。这是需要连接查询.内连接如下：
SELECT
s.id,
s.name,
s.class_id,
c.name
class_name,
s.gender,
s.score
FROM students s
INNER JOIN classess c
ON s.class_id = c.id;
INNER JOIN查询的写法是：
- 先确定主表，仍然使用
FROM <表1>的语法； - 再确定需要连接的表，使用
INNER JOIN <表2>的语法； - 然后确定连接条件，使用
ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接； - 可选：加上
WHERE子句、ORDER BY等子句。
京公网安备 11010502036488号