怎么做网站演示,h5制作免费素材,html购物网站设计论文,数字化营销目录 一. 更复杂的新增 二. 查询
2.1 聚合查询
COUNT
SUM
AVG
MAX MIN
2.1.2 分组查询 group by 子句 2.1.3 HAVING 2.2 联合查询/多表查询 2.2.1 内连接
2.2.2 外连接
2.2.3 全外连接
2.2.4 自连接 2.2.5 子查询
2.2.6 合并查询 一. 更复杂的新增
将从表名查询到…目录 一. 更复杂的新增 二. 查询
2.1 聚合查询
COUNT
SUM
AVG
MAX MIN
2.1.2 分组查询 group by 子句 2.1.3 HAVING 2.2 联合查询/多表查询 2.2.1 内连接
2.2.2 外连接
2.2.3 全外连接
2.2.4 自连接 2.2.5 子查询
2.2.6 合并查询 一. 更复杂的新增
将从表名查询到的结果插入到表名2中 insert into 表名2 select .. from 表名1 ...; 创建一个学生表: 创建一个学生表2, 将学生表中的数据加到学生表2中: 注意: 列的类型可以匹配即可插入, 列名和列的类型不一定要完全一致 二. 查询
2.1 聚合查询
前面谈到的表达式查询, 是针对列和列之间的运算, 聚合查询, 就是针对行和行之间的运算
对于行之间的运算, sql中提供了聚合函数 来完成这里的运算, 相当于是库函数 COUNT
存在这样的表: 此时, 得到了表的全部行的数量
当前这个写法, 就相当于先执行了select * from exam_result, 再对结果进行count聚合
如果写具体的列, 就是针对这一列的结果进行聚合: 发现结果不同, 因为chinese有个空行
结论: 针对某一列进行聚合的时候, 只关注这一列有多少非NULL的结果 而针对*进行聚合时, 则不再是否非NULL, 全部都记录 如果在count和( )之间加个空格, 就会报错 SQL语言大部分情况下对于空格和换行都是很有好的, 但是聚合函数的函数名和括号之间, 不能有空格 SUM 相当于先执行了select chinese from exam_result, 再对结果进行sum聚合
括号中写表达式也可以: 相当于先执行了select chinesemathenglish from exam_result, 再对结果进行sum聚合
也可以进行带有别名: 但不能把别名写在括号中: 能用sum(*) 吗? 显然不可以 聚合函数中count(*) 其实是一种特殊情况, 只是单纯的统计行数 sum和其他的聚合函数, 涉及到相加或其他计算, 语义上没有明确定义, 不能用sum(*) 针对非数值的列, 进行加和, 虽然不会报错, 但是结果是不正确的, 会报警告: 使用show warnings; 可以查看警告 数据库试图将name转换成double类型(因为SQL是弱类型语言), 但是失败了 但是如果虽然是varchar类型, 但是你赋值为123, 那也是可以相加的, 因为可以进行类型转换 AVG MAX MIN 2.1.2 分组查询 group by 子句
指定某个列, 针对这个列, 把值相同的行, 分到同一个组中, 在针对每个组, 分别进行聚合查询
添加一个表: 查询每个角色下有几个人: 执行顺序: 先执行 select role,id from emp 再根据group by role 设定, 按照role这一列的值, 针对上述查询结果, 进行分组 分成服务员一组, 游戏陪玩一组, 游戏角色一组, 董事长一组 针对上面的组, 分别执行count聚合操作 将上面的结果整理成临时表, 返回给客户端即可
最终结果的临时表, 这几个分组的顺序谁先谁后可不一定!!
可以针对聚合后的结果进行排序, 而不是干预每个分组中数据的先后顺序: 注意: 使用 GROUP BY 进行分组查询时SELECT 指定的字段必须是“分组依据字段”即group by 后面的列, 其他字段若想出现在SELECT 中则必须包含在聚合函数中。 如果这么写: 每个显示的结果, 都是每个分组中的其中一条记录, 但是由于分组之后, 顺序是不确定的, 当前你这里显示的是哪行, 是不确定的, 存在一定的随机性, 因此就没有意义 2.1.3 HAVING
给聚合查询指定条件:
1) 聚合之前的条件
查询每个岗位的平均工资, 但是刨除马云 where写在group by 前面
2) 聚合之后的条件
查询每个岗位的平均工资, 但刨除平均工资超过10000的数据
此时, 筛选聚合之后的条件, 就不能用where, 需要用having: 此时having放在group by 后面
当然, 一个sql中, 上述两种条件, 都可以存在:
计算每个岗位的平均工资, 刨除马云, 也刨除平均工资超过10000的: 2.2 联合查询/多表查询
上面介绍的都是单表查询
想要学习多表查询, 我们先要了解一下笛卡尔积的概念
笛卡尔积, 就是得到了一个更大的表, 列数, 是原来两个表列数的和, 行数, 是原来两个表列数的积
在sql中, 我们很方便通过select 完成笛卡尔积:
先创建两个表student 和 class 笛卡尔积: 但是, 笛卡尔积是全排列的过程, 穷举出了所有的可能性, 自然就会产生以下不符合实际的情况的数据, 例如上述笛卡尔积中, classId只有一个, 只有相同的才是有效数据
那么我们可以加上条件: 这样写, 会产生二义性, 因为两个列名是相等的, 那么我们可以通过 表名. 列名 的方式访问各自的列 这个就是多表查询/联合查询的sql
像 where student.classId class.classId 这种专门用来筛选出有效数据的条件, 也称为连接条件, 上述多表查询的操作, 也可以称为连接操作.
创建四张表: 1) 查询许仙同学的成绩 第一步: 先确定要查询的信息来自哪些表 许仙同学的名字在学生表中, 成绩在分数表中, 所以要针对学生表和分数表进行联合查询 第二步: 针对这两个表进行笛卡尔积 学生表是8条记录, 分数表是20条记录, 笛卡尔积应该有160条记录 第三步: 加上连接条件, 去掉无效数据 第四步: 再根据题目, 补充其他的条件 第五步: 去掉不必要的列 因为题目要求只要许仙的成绩 2) 使用join ... on 多表查询的另一种写法 第一步: 确认信息来自学生表和分数表 第二步: 笛卡尔积(用join实现笛卡尔积) 第三步: 指定连接条件(用on 代替where) 第四步: 补充其他条件 第五步: 保留必要列, 去掉其他列 3) 查询所有同学的总成绩 第一步: 信息出自学生表 分数表 第二步: 学生表和分数表进行笛卡尔积 第三步: 指定连接条件 第四步: 添加其他条件, 此处需要添加聚合操作, 按照学生的姓名进行分组 4) 列出同学的成绩, 课程的名字, 课程的成绩 第一步: 信息来自三个表, 学生表, 课程表, 分数表 第二步: 笛卡尔积 第三步: 指定连接条件 第四步: 对列进行精简 针对多张表查询, 使用join on 可读性更好 2.2.1 内连接
上述我们所有的链接都是内连接 select * from 表1,表2 where 连接条件 select * from 表1 (inner) join 表2 on 连接条件 2.2.2 外连接 select * from 表1 left/right join 表2 on 条件....; 注意: 只能使用join ..on.., 在join的前面加上left / right 关键字, 表示左外连接 和 右外连接 外连接和内连接一样, 也是基于笛卡尔积的方式来计算的, 但是对于空值/不存在的值, 处理方式是存在区别的
1)对于数据一一对应的情况
对于学生表和分数表, 任何一个学生数据, 都能在分数表中找到分数结果 任何一个分数结果, 也能在学生表中找到名字信息
此时, 进行内连接和外连接, 效果是一样的 简单创建两张表:
内连接:
外连接:
2)对于数据非一一对应的情况 如果我们对上述数据进行调整: 此时, wangwu的分数信息, 在分数表中不存在了 分数表中, id为4的信息, 在学生表中也不存在
内连接: 此时, 得到的结果, 是包含在两个表中都有的数据
左外连接:对于左外连接, 以左侧的表为基准, (写作student left join score, 此时student就是左侧表, score就是右侧表), 保证左侧表中的每一个数据, 都会存在, 左侧表数据在右侧表不存在的列, 会用null填充
右外连接:右外连接与左外连接类似, 以右侧表为基准, 使右表中的每个数据都存在, 对应坐标中不存在的数据都用null填充
2.2.3 全外连接
结合左连接和右连接
站在集合的角度看待上述几种链接: 2.2.4 自连接
同一个表, 自己和自己计算笛卡尔积
例: 还是使用上面的表
显示所有计算机原理成绩比java成绩高的成绩信息 我们想要比较的是同一个学生的3号课程比1号课程成绩高的数据, 发现我们要比较的score在不同的行中, 之前我们进行的各种条件查询, 都是基于列和列之间的比较, 聚合函数时行和行之间的运算, 没法进行行和行之间的比较, 这时我们就要想办法将行和行之间的关系, 转化成列和列之间的关系, 此时, 自连接就出现了
当我们直接进行自连接时, 发现会报错:
此时我们要给表起个别名:
继续添加条件: 2.2.5 子查询
把多个sql嵌套成了一个sql
1) 单行子查询: 返回一行记录的查询
查询不想毕业同学的同班同学:
先知道不想毕业同学的班级, 再去找同班同学
子查询: 将得出的单条记录直接用sql语句代替
2)多行子查询: 返回多条记录的子查询 用in关键字
查询语文或英文课程的成绩信息:
先知道语文或英文的课程id, 再去找成绩:
子查询: 用in()来圈定范围
2.2.6 合并查询
把多个查询结果合并到一起, 使用union关键字
查询id3,或者名字为英文的课程 另外, union允许从不同的表分别查询, 只要每个表查询的结果集合列的类型和列的个数匹配, 都能合并, 列的名字无所谓, 但是or只能针对一个表
union在合并是会自动去重, 如果不想要去重操作, 可以使用union all
