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