查询数据

基本查询

查询数据库表中的数据：

select * from table_name;

select关键字的另一个用途，测试数据库连接情况

select 1;

条件查询

根据条件选择性的获取指定条件的记录：

select * from table_name
where 条件表达式;

and条件查询：表示满足“条件1”并且满足“条件2”

select * from students
where score >= 80 and gender = 'M';

or条件查询：表示满足“条件1”或“条件2”

select * from students
where score >= 80 or gender = 'M';

not条件查询：不符合该条件的记录

select * from students
where not class_id = 2;

注！ 条件查询的优先级：not>and>or

多条件查询：三个/三个以上的条件

select * from students
where (score < 80 or score > 90) and gender = 'M';

投影查询

返回结果集只包含指定列：

select 列1,列2,列3... from table_name;

排序

根据列名对结果集进行升、降序排序：

select * from table_name 
where 条件表达式
order by 列名 排序规则;

从低到高排序：ASC

select * from table_name
where class_id = 1
order by score ASC;

从高到低排序：DESC

select * from students
where class_id = 2
order by score DESC;

分页查询

将数据量太大的结果集进行分页显示，每页M条记录，获取第N/M-1页的记录

select * from table_name
where 条件表达式
order by 列名 排序规则
limit M offset N;

查询第一页：

select * from students
order by score DESC
limit 3 offset 0; 

查询第二页：

select * from students
order by score DESC 
limit 3 offset 3;

查询第三页：

select * from table_name
order score ASC
limit 3 offset 6;

聚合查询

使用聚合函数进行查询，可快速获取结果

聚合函数

函数	说明
count()	计算某一列的行数（数量和）
sum()	计算某一列的合计值
avg()	计算某一列的平均值
max()	计算某一列的最大值
min()	计算某一列的最小值

使用聚合查询并设置结果集的列名为num：

select count(score) num from students;

使用集合查询计算男生的平均成绩

select avg(score) from students
where gender ='M';

分组

指定列进行分组：group by

select * from table_name
where 条件表达式
group by 列名
order by 列名 排序规则
limit M offset N;

按照class_id分组

select * from students
group by class_id;

多表查询（笛卡尔查询）

多表查询时，要使用“表名.列名”这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点

select 别名1.列名,别名2.列名
from 表名1 别名1,表名2 别名2;

select s.id,c.id
from students s,class c
where c.id = s.class_id;

连接查询

连接查询对多个表进行JOIN运算，先确定一个主表作为结果集，之后将其他表的行有选择的连接在主表的结果集上

INNER JOIN —— 内连接

1. 先确定主表，仍然使用FROM <表1>的语法；

2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；

3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；

4. 可选：加上WHERE子句、ORDER BY等子句。

eg：选出所有学生，同时返回班级名称

select s.*,c.name class_name
from students s
inner join class c
on s.class_id = c.id;

 

OUTER JOIN —— 外连接

eg：选出所有学生，同时返回班级名称

SELECT s.*,c.name class_name
FROM students s
RIGHT OUTER JOIN class c
NO s.class_id = c.id;

RIGHT OUTER JOIN、LEFT OUTER JOIN、FULL OUTER JOIN的区别是：

INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id=c.id返回的结果集仅包含1，2，3。

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL

FULL OUTER JOIN——我们使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

eg：选出所有学生，同时返回班级名称

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

图集：

假设查询语句：

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

INNER JOIN：

LEFT OUTER JOIN：

RIGHT OUTER JOIN：

FULL OUTER JOIN：