d5.MySql查询进阶[分页-聚合-多表-连接]

查询进阶

• 分页查询
• 聚合查询
• 多表查询
• 连接查询
  • INNER JOIN
  • RIGHT OUTER JOIN
  • LEFT OUTER JOIN

分页查询

使用 LIMIT M OFFSET N

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

• LIMIT总是设定为pageSize；
• OFFSET计算公式为pageSize * (pageIndex - 1)。

聚合查询

可以在 where 筛选之后使用

    SELECT COUNT(*) boys FROM students WHERE gender = 'M';

• 聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

• COUNT 计算某一列符合条件的记录个数

• SUM 计算某一列的合计值，该列必须为数值类型

• AVG 计算某一列的平均值，该列必须为数值类型

• MAX 计算某一列的最大值

• MIN 计算某一列的最小值

分组

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

• class_id相同的列先分组，再分别计算
• 按照 class_id 分组 返回每个 class_id 的个数
• 在 SELECT 中添加 name 字段后 会报错，聚合查询中，只能放入分组的列

  • 但是 可以进行多个字段的分组

    SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
    

多表查询

从多个表中查询数据

• 注意 查询的结果是 笛卡尔积 两个10条数据的表 查询后 是100条 100–>10000 10000->100000000…

SELECT * FROM table1,table2

-- 可以给字段使用别名
SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;


-- 给表名也使用别名 简化输入
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

-- 使用WHERE 进行条件筛选 可以是用表名 别名
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

连接查询

连接查询是另外一种多表查询
对多个表格使用join进行运算
简单来说就是 先确定一个主表，然后把其他需要使用的表 有选择性的 添加到主表查询的结果集上

for example：
需求：
表1中 有一个字段只记录了表2中的id 限制需要把表2 中的某个信息一起展示出来

INNER JOIN
内链接

SELECT s.id,s.name,s.class_id,c.name class_name,s.gender,s.score
FROM students s
INNER JOIN class c
ON s.class_id = c.id

INNER JOIN 的使用方法：

1. 先确定主表 FROM <表1>
2. 再确定需要连接的表，使用 INNER JOIN <表2>
3. 然后确定连接条件，使用ON <条件> 上述 查询中 使用的 s.class_id = c.id，表示 students 表的 calss_id 字段与 classes 表中 id字段 相同的行需要连接
4. 可选：加上WHERE子句、ORDER BY等子句

---

new4me

RIGHT OUTER JOIN & LEFT OUTER JOIN
外连接 右连接 左连接

有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

---