MySQL笔记——查询数据

基本查询

SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据，查询的结果是一个二维表。

条件查询

SELECT * FROM <表名> WHERE <条件表达式>，查询的结果是满足查询条件的记录。

条件表达式

• 可以用<条件1> AND <条件2>表达满足条件1且满足条件2
• 可以用<条件1> OR <条件2>表示满足条件1或满足条件2
• 可以用NOT <条件>表示不符合该条件的记录，也可以用<>表示不等于
• 优先级由高到低依次是NOT，AND，OR，加上括号可以改变优先级

投影查询

只返回某些列的数据，而不是所有列的数据，这种操作成为投影查询。并且，结果集列的顺序和原表可以不一样。

SELECT 列1别名1，列2别名2，列3别名3 FROM...，也可以在后面接WHERE条件，实现复杂的查询。

排序

可以使用ORDER BY子句进行条件排序，DESC表示倒序，ASC表示升序，升序可省略。如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。

例如

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

如果查询结果集数据量很大，则可以实现分页功能，实际上也就是从结果集中“截取”出第M~N条记录。通常通过LIMIT <M> OFFSET <N>来实现。

分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize，然后根据当前页的索引pageIndex，确定LIMIT和OFFSET应该设定的值。

• LIMIT总是设定为pageSize
• OFFSET计算公式为pageSize*（pageIndex-1）

例如每页3条记录，获取第一页的记录可用LIMIT 3 OFFSET 0

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

聚合查询

使用SQL提供的聚合查询，可以方便地计算总数COUNT()、合计值SUM()、平均值AVG()、最大值MAX()和最小值MIN()；聚合查询也可添加WHERE条件。

SELECT COUNT(*) FROM students //使用聚合查询查询students表内一共多少条记录
SELECT COUNT(*) num FROM students //使用聚合查询并设置结果集的列名为num
SELECT COUNT(*) boys FROM students WHERE gender = 'M' //使用聚合查询并设置WHERE条件 
SELECT AVG(score) average FROM students WHERE gender = 'M'; //使用聚合查询计算男生平均成绩 

分组聚合通过GROUP BY来实现，先对指定的列进行分组再分别计算。

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id; //按class_id分组

多表查询

SELECT * FROM <表1> <表2>，结果集的列数是两表列数之和，行数是两表行数之积。这种多表查询又称笛卡尔查询，返回值数目庞大，不建议使用。

连接查询

连接查询是另一种类型的多表查询，连接查询对多个表进行JOIN运算，就是先确定一个主表作为结果集，然后把其他表的行有选择地连接在主表结果集上。

INNER JOIN是最常用的一种JOIN查询，它的语法是SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>;也可加上WHERE子句、ORDER BY等子句。

//选出所有学生，同时返回班级名称
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就会存在RIGHT OUTER JOIN、LEFT OUTER JOIN以及FULL OUTER JOIN，它们的区别是：

• INNER JOIN值返回同时存在与两张表的行数据
• RIGHT OUTER JOIN返回右表都存在的行，如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段
• LEFT OUTER JOIN返回左表都存在的行
• FULL OUTER JOIN把两张表的所有记录全部选择出来，并且自动把双方不存在的列填充为NULL