门户网站建设的请示,江西事件最新消息新闻,鄂州手机网站建设,宁波建设规划网目录
聚合查询
聚合函数
group by 子句
执行流程图
联合查询
笛卡尔积
内连接 外连接
左外连接
右外连接
自连接
子查询
单行子查询
多行子查询
EXISTS 关键字
合并查询
union on 和 union 的区别 聚合查询 聚合函数 函数说明COUNT([DISTINCT] expr)返回查询到…目录
聚合查询
聚合函数
group by 子句
执行流程图
联合查询
笛卡尔积
内连接 外连接
左外连接
右外连接
自连接
子查询
单行子查询
多行子查询
EXISTS 关键字
合并查询
union on 和 union 的区别 聚合查询 聚合函数 函数说明COUNT([DISTINCT] expr)返回查询到的数据的数量SUM([DISTINCT] expr)返回查询到的数据的总和AVG([DISTINCT] expr)返回查询到的数据的平均值MAX([DISTINCT] expr)返回查询到的数据的最大值MIN([DISTINCT] expr)返回查询到的数据的最小值 注意 求和、平均值、最大、最小 这几个函数都需要针对数字类型的列 实例理解 此处我们创建一个 exam_result 表并插入几条数据 使用 count 查询返回结果的行数 select count(*) from exam_result; 执行结果 使用 sum 查询 chines 字段数据的和针对某一列计算 select sum(chinese) from exam_result; 执行结果 使用 avg 查询 chines math eglish 这三个字段数据的平均值针对表达式计算 select avg(chinese math english) from exam_result; 执行结果 group by 子句 使用 group by 子句可以对指定列进行分组查询不使用 group by 分组的时候就相当于只要一组把所有的行进行聚合引入 group by 就可以针对不同的组来分别进行聚合 实例理解 此处我们创建一个 emp 表并插入几条数据 此处我们想根据 role 字段分别查询各个职位的平均薪水 select role,avg(salary) from emp group by role; 执行结果 分组查询 也是可以指定条件的 三种情况 分组之前 指定条件即先筛选再分组where分组之后 指定条件即先分组再筛选having分组之前和之后都指定条件 实例理解 查询每个岗位的平均薪资但是需筛选掉 employ 岗位中 name haoran 的薪水使用 where select role,avg(salary) from emp where name ! haoran group by role; 执行结果 查询每个岗位的平均薪水但是最后筛选掉平均薪水大于 10000 的岗位使用 having 执行结果 查询每个岗位的平均薪资但是在计算平均薪水之前 需筛选掉 employ 岗位中 name haoran 的薪水在计算平均薪水后 需筛选掉平均薪水大于 10000 的岗位where 和 having 结合使用 select role,avg(salary) from emp where name ! haoran group by role having avg(salary) 10000; 执行结果 执行流程图 联合查询 笛卡尔积 多表查询的基本执行过程笛卡尔积 实例理解一 此处我们创建两个表分别为 student 表 和 class 表并插入几条数据 此时我们想得到 student 表 和 class 表的笛卡尔积 select * from student, class; 执行结果 笛卡尔积得到一个更大的表列数为两个表列数之和行数为两个表行数之积但是仔细观察笛卡尔积里的结果很多都是无效数据只有一部分是有意义的因此我们需要把 无意义的数据给筛选掉此处无意义的数据行为 classId 既为 1 又为 2 的 select * from student, class where student.classId class.classId; 执行结果 加上条件后我们所查询到的数据便都是合法的了此处我们将用来筛选有效数据的条件 称为连接条件 实例理解二 此处我们创建四个表分别为 classes 表、student 表、course 表、score 表并插入多条数据 分析 学生 和 班级一对多关系学生 和 课程多对多关系要想表示这个多对多关系就需要引入个关联表score 表正好描述了 学生 和 课程 之间的关联关系顺便也把分数也给列出来了 1查询 许仙 同学的成绩 先计算笛卡尔积加上连接条件 select * from student,score where student.id score.student_id; 执行结果 根据需求我们仅需得到 name 许仙 的数据行 select * from student,score where student.id score.student_id and name 许仙; 执行结果 再针对查询的列进行精简此处只保留 name 字段 和 score 字段 select student.name,score.score from student,score where student.id score.student_id and name 许仙; 2使用 join 关键字查询 许仙 同学的成绩 写法一 select student.name,score.score from student join score on student.id score.student_id and student.name 许仙; 写法二 select student.name,score.score from student inner join score on student.id score.student_id and student.name 许仙; 执行结果 这两种写法的执行结果相同 注意 上述 直接 from 多个表和使用 join on 关键字虽然均能实现查询许仙同学成绩但是 直接 from 多个表只能实现 内连接而 join on 既可以实现 内连接 也能实现 外连接 3查询所有同学的总成绩及同学的个人信息 select student.name,sum(score.score) as 总分 from student,score where student.id score.student_id group by student.name; 执行结果 4查询所有学生的成绩信息及同学的个人信息 期望查询结果中包含个人信息、课程名字、分数 写法一 直接 from 多个表 select student.name,course.name as course_name,score.score from student,course,score where student.id score.student_id and score.course_id course.id; 写法二 使用 join 关键字 select student.name,course.name as course_name,score.score from student join score on student.id score.student_id join course on score.course_id course.id; 执行结果 内连接 外连接 内连接 和 外连接在大多数情况下是没区别的比如 要连接的两个表其里面的数据都是相互对应的这时候二者没区别如果不是 相互对应的那么此时 内连接 和 外连接 就有区别了 实例理解一 此处我们创建两个表分别为 student 表 和 score 表并插入多条数据此时我们两个表中的数据为 相互对应的 使用 直接 from 多个表的方式 查询学生成绩 select * from student,score where student.id score.student_id; 执行结果 使用 join 关键字的方式 查询学生成绩 select * from student join score on student.id score.student_id; 执行结果 我们可以对比观察两种方式的查询结果发现查询结果相同且此处的连接为 内连接 实例理解二 此处我们将 score 表中的 score 70 的 student_id 改为 4此时 student 表中的 王五 同学是没有分数的 score 表中 student_id 4 的分数不知道是哪位同学的 使用 直接 from 多个表的方式 查询学生成绩 select * from student,score where student.id score.student_id; 执行结果 此处的查询结果为 两个表中能相互对应上的数据 使用 join 关键字的方式 查询学生成绩 select * from student join score on student.id score.student_id; 执行结果 此处的查询结果为 两个表中都有的数据即能对应上的数据 上述的两种写法均为 内连接 此时我们通过 join 关键字来实现外连接写法为在 join 前面加个 left 或 right 分别对应着 左外连接 和 右外连接 左外连接 select * from student left join score on student.id score.student_id; 执行结果 左外连接 会把左表的结果尽量列出来哪怕在右表中没有对应的记录就使用 NULL 填充 右外连接 select * from student right join score on student.id score.student_id; 执行结果 右外连接 会把右表的结果尽量列出来哪怕在左表中没有对应的记录就使用 NULL 填充 自连接 自己和自己进行笛卡尔积自连接的效果就是把 行 转成 列sql 中无法针对 行和行 之间使用条件比较但是有的需要中又需要行和行比较就可以使用 自连接 把行转成列 实例理解 此处我们还是使用下图所示的四张表 举例说明 1查询计算机原理成绩比 Java 高的同学 id 首先我们进行笛卡尔积并简单筛选 无效数据 select * from score as s1,score as s2 where s1.student_id s2.student_id; 执行结果 插叙出来的数据行还是比较多的此处我们仅截取 1号同学 分析 我们可以看到这里的每一行就是在针对同一个同学的各种课程 id 进行了排列组合同时也是针对分数进行 排列组合上图红框部分数据行 其 course_id 分别为 3 和 1即代表 计算机原理 和 Java上图 红框部分 和 绿框部分 为重复行所以我们还需进行去重 select * from score as s1,score as s2 where s1.student_id s2.student_id and s1.course_id 3 and s2.course_id 1; 执行结果 此处我们再进一步指定查询 s1 表 course_id 3 且 s2 表 course_id 1 的数据行即 s1 表仅保留 计算机原理的成绩s2 表仅保留 Java 的成绩既满足了 仅需比较 计算机原理 和 Java 这两门课的成绩的要求也达到了去重的目的 最后我们再进行最后的筛选只留下计算机原理成绩 比 Java 高 的数据行 select * from score as s1,score as s2 where s1.student_id s2.student_id and s1.course_id 3 and s2.course_id 1 and s1.score s2.score; 执行结果 子查询 子查询本质上就是套娃即把多个 sql 组合成了一个在实际开发中子查询要慎用因为子查询可能会构造出非常复杂、不好理解的 sql对于 代码的可读性 和 sql的执行效率 很可能是毁灭性的打击 单行子查询 返回一行记录的子查询 实例理解 此处我们还是使用下图所示的四张表 举例说明 1查询 name 不想毕业 的同班同学的姓名 步骤一先查询到 name 不想毕业 同学的班级 id select classes_id from student where name 不想毕业; 执行结果 步骤二根据查询到的班级 id 来查询该班级的所有学生姓名排除掉 name 不想毕业 同学 select name from student where classes_id 1 and name ! 不想毕业; 执行结果 我们子查询就是将上述的两个步骤合二为一 select name from student where classes_id (select classes_id from student where name 不想毕业) and name ! 不想毕业; 执行结果 多行子查询 返回多行记录的子查询 实例理解 还是使用上文 的四张表来举例 1查询 语文 或 英语 课程的成绩信息 步骤一先根据名字 查询出课程 id select id from course where name 语文 or name 英文; 执行结果 步骤二 根据课程 id 查询出课程分数 select * from score where course_id in(4,6); 执行结果 使用子查询将上述两个步骤合并为一 select * from score where course_id in(select id from course where name 语文 or name 英文); 执行结果 EXISTS 关键字 该关键字的可读性比较差 且 执行效率也大大低于 in 关键字写法但使用 exists 关键字可以解决一些特殊场景如 当我们使用 in 关键字进行查询时其查询结果在内存中如果查询结果太大以至于内存都放不下了此时就不能使用 in 关键字需转而使用 exists 关键字代替实际上处理上述场景 更推荐的是多步完成查询没必要强行合成一个exists 关键字的本质也就是让数据库执行多个 查询操作 合并查询 本质上就是把两个查询的结果集 合并成一个要求这两个结果集的列相同才能合并即 列的类型 列的个数 列的名字 相同 实例理解 此处我们还是使用下图所示的四张表 举例说明 1查询 id 3 或者名字为 英文 的课程 此处使用 union 关键字进行 合并查询 select * from course where id3 union select * from course where name 英文; 执行结果 注意 虽然在该场景下使用 or 关键字也能完成查询但是用 or 关键字 时你的查询只能来自于同一张表如果使用 union 关键字查询结果你的查询可以来自于不同的表只要保证查询结果的列相同即可 union on 和 union 的区别 union on 和 union 这两个关键字在大多数场景下都是差不多的但 union 会进行去重操作即重复的行只会保留一份而 union on 可以保留多份重复的数据行不会去重
