失眠网 > MySQL-数据操作-分组查询-连接查询-子查询-分页查询-联合查询

MySQL-数据操作-分组查询-连接查询-子查询-分页查询-联合查询

时间：2023-11-20 23:56:09

文章目录

==分组查询==基础案例特点分组查询中的筛选条件分为两类注意事项分组查询案例案例 ==连接查询==概述==sql92标准==概述等值连接非等值连接自连接案例 ==sql99标准==概述内连接外连接交叉连接 ==总结==sql92 和sql99 比较join连接总结（sql99）案例 ==子查询==概述==where 或having后面==概述案例：标量子查询案例：列子查询案例：行子查询 ==select后面==概述案例：标量子查询 ==from后面==概述案例：表子查询 ==exists后面==概述案例 ==子查询案例== ==分页查询==概述案例：limit案例：stuinfo ==案例==作业案例==联合查询==概述案例

分组查询

基础

引入：求每个部门的平均工资。

语法

select 分组函数，分组后的字段（要求出现在group by的后面）from 表【where 筛选条件】group by 分组列表【having 分组后的筛选条件】【order by 排序列表】

注意

查询列表是特殊的，必须是分组函数或者 group by 后面的字段；

order by 是支持别名的，同样group by 和 having 也是支持别名的，但是不经常使用。

分组函数包括：sum、max、min、avg、count。

分组后查询使用having；

多个分组字段之间使用逗号分隔开来；

执行顺序

from → where → group by → having → select → order by

案例

特点

分组查询中的筛选条件分为两类

注意事项

分组函数做条件肯定是放在having子句中

能用分组前筛选的，就优先考虑使用分组前筛选

having子句即可包含聚合函数（分组函数）作用的字段也可以包括普通的标量字段

having子句必须与 group by 子句同时使用，不能单独使用

group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的较少）。

也可以添加排序（排序放在整个分组查询的最后）

分组查询案例

案例

连接查询

概述

概念：

又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询。

笛卡尔乘积现象表1 有m行，表2 有n行，结果有n*m行。

当查询多个表时，没有添加有效的连接条件，导致多个表所有行实行完全连接。

发生原因：没有有效的连接条件；

如何避免：添加有效的连接条件。

分类

按年代分类

sql92标准：在mysql中支持内连接，也支持一部分外连接（oracle、sqlserver支持，mysql不支持）。

sql99标准【推荐使用】：在mysql中支持内连接+外连接（左外、右外）+交叉连接。

按功能分类

内连接：等值连接、非等值连接、自连接。

外连接：左外连接、右外连接、全连接。

交叉连接。

sql92标准

概述

语法：

select 字段1，字段2....from 表1，表2.....；使用的连接方式是：sql92语法

等值连接

语法

select 查询列表from 表1 ，表2where 表1.key = 表2.key【and 筛选条件】【group by 分组字段】【having 分组后的筛选条件】【order by 排序字段】

特点

多表等值连接的结果为多表的交集部分。

n表连接，至少需要n-1个连接条件。

多表的顺序没有要求，可以调换。

一般需要为表起别名。

可以搭配前面介绍的所有子句使用，比如筛选（select）、分组（group by）、排序（order by）。

案例

备注/7/30

为表起别名，使用连接查询：

①提高语句的简洁度；

②区分多个重名的字段；

③如果对表起了别名，则查询的字段就不能用原来表名去限定了。

备注

两个表连接，连接条件中的两个表的顺序可以交换；

连接查询中，需要连接条件和筛选条件的时候，and前 = 连接条件，and后=筛选条件。

备注

等值连接可以和分组搭配使用。

备注

连接查询可以和排序一起存在；

三表连接要求，后一个表和前面已经连接成功的表之间需要有相同的字段。

非等值连接

语法

select 字段1，字段2...from 表1 别名1，表2 别名2...where 非等值连接的条件【and 筛选条件】【group by 分组条件】【having 分组后的筛选条件】【order by 排序字段】

案例

备注

非等值连接大部分筛选条件都涉及between and。

自连接

概念

将自身的表当作多张表去使用。

语法

select 字段1，字段2...from 表别名1，表别名2where 等值连接（别名1.key = 别名2.key）【and 筛选条件】【group by 分组条件】【having 分组后的筛选条件】【order by 排序字段】

案例

备注

自连接，需要为同一张表取两个别名；

案例

sql99标准

概述

语法

select 查询列表from 表1 别名1【连接类型】 join 表2 别名1on 连接条件【where 筛选条件】【group by 分组字段】【having 分组后的筛选】【order by 排序列表】

分类

内连接：关键字——【inner】

外连接：左外连接（left 【outer】）、右外连接（right 【outer】）、全连接（full 【outer】）

交叉连接：关键字——cross

内连接

场景

查询的信息存在于两个表中。

语法

select 查询列表from 表1 别名1【inner】 join 表2 别名2on 连接条件【where 筛选条件】【group by 分组条件】【having 分组后的查询】【order by 排序字段】

特点

添加筛选、分组、排序；

inner可以省略；

筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读；

inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集；

表的顺序可以调换；

n表连接至少需要n-1个连接条件；

多表连接语法

select 查询列表from 表1 别名1[type] join 表2 别名2 on 连接条件[type] join 表3 别名3 on 连接条件[where 筛选条件][group by 分组条件][having 分组后筛选条件][order by 排序条件];

分类

等值连接、非等值连接、自连接

案例：等值连接

案例：非等值连接

案例：自连接

外连接

场景

用于查询一个表中有，另一个表没有的记录。

语法

select 查询列表from 表1 别名1left | right | full 【outer】 join 表2 别名2on 连接条件【where 筛选条件】【group by 分组字段】【having 分组后的筛选条件】【order by 排序字段】【limit 起始索引，长度】；

特点

外连接的查询结果为主表中所有记录（行）

如果从表中有和它匹配的，则显示匹配的值；

如果从表中没有和它匹配的，则显示null；

外连接的查询结果 = 内连接的结果 + 主表中有而从表中没有的记录。

左外、右外、全外介绍

一般用于查询除了交集部分的剩余的不匹配的行。左外和右外交换两个表的顺序，可以实现同样的效果。

例如：表1 left 【outer】 join 表2 等价于表2 right 【outer】 join 表1全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的。

如果想实现只有后两者的情况，那么需要的就是添加where条件。主表和从表的判断，大部分信息来自于哪个表，哪个表就是主表，另一个就是从表。sql99规则不支持全外连接

案例：左外连接、右外连接

案例：全外连接

交叉连接

语法

select 查询列表from 表1 别名1cross join 表2 别名2；

特点

类似笛卡尔乘积。

案例

总结

sql92 和sql99 比较

功能：sql99支持的较多。

可读性：sql99实现连接条件和筛选条件的分离，可读性较高。

join连接总结（sql99）

案例

备注

没有使用外连接查询。

子查询

概述

概念

嵌套在其他语句中的select语句，称为子查询或内查询。

外面的语句可以是insert、update、delete、select等，一般select作为外语句较多。

外部如果是select语句，则此语句称为主查询或外查询。

分类：子查询出现的位置

分类：结果集的行列数

where 或having后面

概述

结果集

标量子查询（一行一列）、列子查询（多行一列）、行子查询（一行多列或多行多列）

没有表子查询。

特点

子查询放在小括号内。子查询一般放在条件的右侧。标量子查询，一般搭配着单行操作符使用（单行操作符> < = <= >= <>）。列子查询，一般搭配着多行操作符使用（多行操作符 in、any/some、all）。

多行操作符介绍：

举例：

假设a>any（1，2，3，4，5）——a大于括号中的任何一个，为真，否则为假；

假设a>all（1，2，3，4，5）——a大于括号中的所有，为真，否则为假；

子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果。

案例：非法子查询

备注

子查询使用的是单行操作符，则子查询必须是标量子查询，但是题目中却是列子查询（多行一列），所以报错。

案例：标量子查询

案例：列子查询

案例：行子查询

select后面

概述

结果集

仅仅支持标量子查询（一行一列）

案例：标量子查询

from后面

概述

要求把子查询的结果集当成一张表，必须给表起别名。

案例：表子查询

exists后面

概述

语法

exists(完整的查询语句)返回值是：0 或者 1

作用

用于判断子查询是否有记录。

案例

子查询案例

where后面： = >

**from后面 **

where后面：in any

where后面：标量子查询

文章目录

分页查询

概述

应用场景

当要显示的数据条目数太多，一页显示不全，需要分页提交sql请求。

语法

select 查询列表from 表1[连接类型] join 表2 # 采用sql99连接on 连接条件where 筛选条件group by 分组字段having 分组后的筛选条件order by 排序字段limit [offset,] size;# offset：要显示条目的起始索引（索引从0开始）；# size：要显示的条目数。

特点

limit语句放在查询语句的最后；执行顺序

from→on→where→group by→having→order by→select→limit要显示的页数是page，每一页的条目数是size

第一页起始0 结束9 个数10

第二页起始10 结束19 个数10

起始索引 = （page-1）*size

语法：

select 查询列表from 表limit (page - 1)*size,size;