SQL(高级查询)-优快云博客

本文链接：https://blog.youkuaiyun.com/O18670279028/article/details/145807275

5.高级查询

1.统计查询

通过聚合函数实现
聚合函数的作用：将一列数据作为一个整体，进行纵向计算。
常见聚合函数
- sum()：求和
- avg()：求平均数
- max()：最大值
- min()：最小值
- count()：统计符合条件的数据的总记录数
语法
```
select 聚合函数（字段）from 表名
```
- 注意：NULL值是不参与所有聚合函数运算的
- 案例：
  - 求表里面有多少条记录
```
select count(*) from t_student
```
  - 求最大的年龄
```
select max(age) from t_student
```
  - 求最小的年龄
```
select min(age) from t_student
```
  - 求所有学生的年龄相加的和
```
select sum(age) from t_student
```
  - 求所有学生的平均年龄
```
select avg(age) from t_student
```

2.分组查询

group by 条件：
- 通过指定的条件进行分组
having：
- 对分组后的数据进行筛选

案例


    1. 求班上男女生各多少个；
    select sex,count(*) from t_student group by sex
    2. 求班上男女生学号平均值；
    select sex,avg(id) from t_student group by sex
    3. 求班上男女生各学号总和；
    select sum(id) from t_student group by sex
    4. 求班上男女生各个姓氏分别有多少人；
    select substring(sname,1,1),ssex,count(*) from t_student group by substring(sname,1,1),ssex
    5. 求各个地方各个性别多少个人；
    select addr,ssex,count(*) from t_student group by addr,ssex
    6. 求各个地方多少个人；
    select addr,count(*) from t_student group by addr
    7. 求衡阳的男女生各多少个；
    select sex,count(*) from t_student where addr='衡阳'
    8. 求姓刘的男女生各多少；
    select sex,count(*) from t_student where substring(name,1,1)='刘' group by sex
    9. 求人数在2个人以上的地方；
    select addr from t_student group by addr having count(*)>2
    10. 求男生人数在2个人以上的地方。
    select addr from t_student where sex='男' group by addr having count(*)>2

where和having的区别
- 执行时机不同：
  
  where是分组之前进行过滤，不满足where条件不参与分组；而having是分组之后对结果进行过滤
- 判断条件不同：
  
  where不能对聚合函数进行判断，而having可以
- 注意事项：
  - 分组之后查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
  - 执行顺序：where >聚合函数 >having
  - 支持多字段分组，具体语句为：group by columnA,columnB

3.联表查询

关联查询又称为联表查询或多表联查，关联查询分为：内连接、外连接和交叉连接
关联查询需要把多张表共有的字段作为关联查询的条件

1.内连接

inner join /join
内连接也叫自连接，我们经常用的查询方法，内连接查询只能查询出多张表匹配的记录，匹配不上的记录时无法查询出来的。

内连接的三种写法

select * from student s, color c where s.stuname = c.stuname；【简写方式】

select * from student s inner join color c on s.stuname = c.stuname；

select * from student s join color c on s.stuname = c.stuname；

select * from student s join color c where s.stuname = c.stuname；

注意：内连接的时候可以用where替代on，但是where没有on的效率高

2.外连接 -outer join

outer join
外连接可进一步分为左外连接(left outer join)和右外连接(right outer join)，简称左连接(left join)和右连接(right join)

2.1 左外连接 -left join

连接就是以左边的表（left join 左边的表）为主表，即使有些记录关联不上，主表的信息也能全部查询出来，也就是左边的表数据全部展示，右边表的数据符合条件的展示，不符合条件的以空值代替。语法如下：
```
select * from student s left join color c on s.stuname = c.stuname；
```
```
select * from student s left outer join color c on s.stuname = c.stuname;
```

2.2 右外连接

右连接就是以右边的表（right join 右边的表）为主表，即使有些记录关联不上，主表的信息也能全部查询出来，也就是右边的表数据全部展示，左边表的数据复合条件的展示，不符合条件的以空值代替。语法如下：
```
select * from student s right join color c on s.stuname = c.stuname；
```
```
select * from student s right outer join color c on s.stuname = c.stuname;
```

2.3全连接

全连接的查询结果是左外连接和右外连接查询结果的并集，即使一些记录关联不上，也能够把部分信息查询出来。mysql里面已经删除了全连接 full join，不过我们可以通过 UNION 拼接实现如下：
```
(select * from tb_user left join tb_shop on tb_user.shop_id = tb_shop.shop_id) #左连接两张表，没有取别名union
(select * from tb_shop right join tb_user on tb_user.shop_id = tb_shop.shop_id) #右连接两张表，没有取别名
```