MySQL条件查询：聚合查询、分组查询、连接查询、子查询

MySQL条件查询

聚合函数

介绍

• 聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。
• 常用的聚合函数:
  • count(col): 表示求指定列的总行数
  • max(col): 表示求指定列的最大值
  • min(col): 表示求指定列的最小值
  • sum(col): 表示求指定列的和
  • avg(col): 表示求指定列的平均值
    • – 求男生的平均身高, 包含身高是null的
    • select avg(ifnull(height,0)) from students where gender = 1;
    • ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。

注意

• 聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。

分组查询

介绍

• 分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组

语法

• GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
• 列名: 是指按照指定字段的值进行分组。
• HAVING 条件表达式: 用来过滤分组后的数据。
• WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

group by的使用

• – 根据gender字段来分组
  • select gender from students group by gender;
• – 根据name和gender字段进行分组
  • select name, gender from students group by name, gender;

group by + group_concat()的使用

• group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
• – 根据gender字段进行分组， 查询gender字段和分组的name字段信息
  • select gender,group_concat(name) from students group by gender;

group by + 聚合函数的使用

• – 统计不同性别的人的平均年龄
  • select gender,avg(age) from students group by gender;
• – 统计不同性别的人的个数
  • select gender,count(*) from students group by gender;

group by + having的使用

• having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
• – 根据gender字段进行分组，统计分组条数大于2的
  • select gender,count() from students group by gender having count()>2;

group by + with rollup的使用

• – 根据gender字段进行分组，汇总总人数
  • select gender,count(*) from students group by gender with rollup;
• – 根据gender字段进行分组，汇总所有人的年龄
  • select gender,group_concat(age) from students group by gender with rollup;

连接查询

介绍

• 连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。

分类

• 内连接查询
  • 介绍
    • 查询两个表中符合条件的共有记录
  • 语法格式:
    • select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
    • inner join 就是内连接查询关键字
    • on 就是连接查询条件
  • 例1：使用内连接查询学生表与班级表:
    • select * from students as s inner join classes as c on s.cls_id = c.id;
• 左连接查询
  • 介绍
    • 以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充
  • 语法格式:
    • select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
    • left join 就是左连接查询关键字
    • on 就是连接查询条件
    • 表1 是左表
    • 表2 是右表
  • 例1：使用左连接查询学生表与班级表:
    • select * from students as s left join classes as c on s.cls_id = c.id;
• 右连接查询
  • 介绍
    • 以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充
  • 语法格式：
    • select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2
    • right join 就是右连接查询关键字
    • on 就是连接查询条件
    • 表1 是左表
    • 表2 是右表
  • 例1：使用右连接查询学生表与班级表:
    • select * from students as s right join classes as c on s.cls_id = c.id;
• 自连接查询
  • 介绍
    • 左表和右表是同一个表，根据连接查询条件查询两个表中的数据。
  • 自连接查询的用法:
    • select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = ‘山西省’;
  • 说明:
    • 自连接查询必须对表起别名
  • 小结
    • 自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
    • 自连接就是一种特殊的连接方式，连接的表还是本身这张表

子查询

介绍

• 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.
• 主查询和子查询的关系:
  • 子查询是嵌入到主查询中
  • 子查询是辅助主查询的,要么充当条件,要么充当数据源
  • 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询的使用

• 例1. 查询大于平均年龄的学生:
  • select * from students where age > (select avg(age) from students);
• 例2. 查询学生在班的所有班级名字:
  • select name from classes where id in (select cls_id from students where cls_id is not null);
• 例3. 查找年龄最大,身高最高的学生:
  • select * from students where (age, height) = (select max(age), max(height) from students);