表的分组查询-group by分组查询
- 分组查询
- 用法
- 分组查询 -garoup by
- 过滤分组 -having
- select 子句顺序
分组查询
group by 只是创建分组,但并不保证分组里面的数据的排列顺序,需 要使用 order by 子句对分组里面的数据进行排序。
注弄清楚 group by /where /order by / having四个逻辑:
group by 用来分组,where用来筛选,orderby用来排序,having用来过滤分组后的数据,所以逻辑应该是,我先筛选需要分组的数据,在吧分组的数据排列所以就是,group by 子句必须出现在 where 子句之后,order by 子句之前。
用法
SELECT vend_id,COUNT(*) AS max_price FROM products GROUP BY vend_id;
过滤分组 -having
除了能用 group by 分组数据外,MySQL 还允许对分组指定条件,规定包含 哪些分组,排除哪些分组。MySQL 使用 having 子句来完成该操作。
where 子句过滤指定的行,having 子句过滤指定的分组。
SELECT vend_id,COUNT(*) AS max_price FROM products GROUP BY vend_id HAVING max_price>=3;
首先,告诉别人你要选择什么,把你的目的开门见山的说出来,无疑,SELECT打头。
2.接下来,你得知道你要选的这个列在哪个表中,或者说你要在哪个表中去选择,所以跟着FROM。
3.然后就要考虑人家的这两个条件,是先选订单单价小于等于10,还是每笔订单数大于等于2。你…
4.最后最后显示的时候需要用ORDER BY指定显示的顺序。 以上就是用可以理解的方式解释了…
--select 数据 from 数据源 where 行过滤条件 group by 分组字段,分组字段.. having 过滤组信息 order by 排序字段 desc|asc,字段2..;
--执行流程: from --> where --> -- group by --> having --> select --> order by
--组函数: 多条记录返回一个结果 count() sum() avg() max() min()
--注意: select后面如果出现组函数,只能与其他组函数或者分组字段一起使用
--where后面不能出现组函数,having后面可以使用组函数
--分组: group by 分组字段,分组字段.. having 过滤组信息
--组函数针对每一组进行计算
--查询整个 公司的平均工资
select avg(sal) from emp;
--查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno order by deptno;
--查询每个部门的平均工资,只要保留平均工资大于200的部门编号与平均工资
--select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno; 不正确
--先分组后过滤
select * from (select deptno,avg(sal) avg_sal from emp group by deptno) where avg_sal > 2000;
--分组的同时过滤
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--查询每个工种的平均工资
select avg(sal),job from emp group by job;
--查询每个工种的平均工资,除了CLERK工种的以外
--先过滤后分组
select avg(sal),job from emp where job!='CLERK' group by job;
--先分组后过滤
select avg(sal),job from emp group by job having job !='CLERK';
select avg(sal),job ,deptno from emp group by job,deptno having deptno = 30;
子查询
子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时,称之为子查询。
--子查询
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
select * from tb_student;
--往已有的表中插入数据
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
--当执行增删改操作的时候,会自动开启事务,可以以事务为单位做回滚|提交
--提交
commit;
--删除表
drop table tb_student cascade constraints;
--使用一条sql语句,查询每门课都大于80分的学生姓名
--查询的数据: name 姓名
--来源: 学生表 tb_student
--条件: 有几门课程考了基本课程 所有课程分数中的最小值>80
--1)一共存在几门课程数目
select count(distinct course) from tb_student;
--2)查询每门课都大于80分的学生姓名
select name from tb_student group by name having count(course)=(一共的课程数) and min(score)>80;
--注意: 一旦存储group by 分组,select后面只能存在分组字段或者组函数
select name
from tb_student
group by name
having count(course) = (select count(distinct course) from tb_student) and min(score) > 80;
行转列、列转行
–decode()单行函数 组函数:一组中的多个值返回一个结果
–一个字段的值如果没有显示的定义值,就为默认值null
–如果一旦分组,select后面只能为分组字段|组函数
--行专列
select * from tb_student;
--按照人名进行分组,原表中有一个人,查询的结果集中就有几条数据
select name,
decode(course, '语文', score) 语文,decode(course, '数学', score) 数学,decode(course, '英语', score) 英语
from tb_student;
--根据人头分组
select name,
max(decode(course, '语文', score)) 语文,
min(decode(course, '数学', score)) 数学,
max(decode(course, '英语', score)) 英语
from tb_student
group by name;
--decode()单行函数 组函数:一组中的多个值返回一个结果
--一个字段的值如果没有显示的定义值,就为默认值null
--如果一旦分组,select后面只能为分组字段|组函数
-多表查询 表连接 连表查询 92、99语法
1.外连接 1.左连接 left join 或 left outer join SQL语句…
2.内连接 join 或 inner join SQL语句:select * from …
3.交叉连接 cross join,没有where指定查询条件的子句的交叉联接将产生两表的…
4.结构不同的表连接 当两表为多对多关系的时候,我们需要建立一个中间表student…
5.UNION操作符用于合并两个或多个select语句的结果集。 UNION内部的…
--多表查询 表连接 连表查询
--92语法
--select 数据 from 数据源1,数据源2,.....;
--99语法
--当要查询的数据来自于多张表中,需要使用表连接
--笛卡尔积: 两个数据原中的数据分别连接,对乘的效果
select * from emp,dept;
select * from dept;
--连接条件:笛卡尔积的效果不满足要求,想要过滤调其中一些
--等值连接
--select 数据 from 数据源 1,数据源2,.... where 连接条件|行过滤条件;
--非等值连接
--查询公司所有员工的员工信息以及所在部门信息
--数据: 员工信息 所在部门信息
--来源: 员工表 部门表
--条件: 员工的部门编号 = 部门表中的数据的部门编号
select * from emp ,dept where emp.deptno = dept.deptno;
--一条sql中,出现多个数据源,建议提供表的别名
select * from emp e,dept d where e.deptno = d.deptno;
--指定查询结果集中的字段名,如果出现同名字段.需要指明出处
select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno = d.deptno;
--等值连接: 一定是两张表中的同名字段名???? 非同名字段可以做等值连接,但是应该为统一类型做连接
select * from emp e,dept d where e.ename=d.ename;
--测试数据 插入
insert into dept values(50,'SMITH','SH');
select * from salgrade;
--非等值连接
--查询员工员工信息,以及工资等级信息
select * from emp e,salgrade s where e.sal between s.losal and s.hisal;
--查询员工的信息以及所在部门信息,以及薪资等级信息
select e.*, d.dname, s.grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal;
--自连接 : 自己与自己连接
--查询有上级的员工的员工信息以及员工的上级经理人信息
-- 员工表 emp e1 经理人表 emp e2
select e1.*,e2.* from emp e1,emp e2 where e1.mgr = e2.empno;
--当有些数据,不满足连接条件 ,但是还是想要显示,可以选择使用外连接
--主表: 主表中的数据,就算不满足连接条件也能展示
--92语法中,在连接条件位置,在主表的对面添加 (+)
--左外连接 : 左连接 : 主表在左边 from后面
--右外连接 : 有连接 : 主表在右边
--查询所有员工的员工信息以及上级经理人信息
--员工表中所有的数据都想要展示,员工表作为主表 e1
select e1.*,e2.* from emp e1,emp e2 where e1.mgr = e2.empno(+); --左连接
select e1.*,e2.* from emp e1,emp e2 where e1.mgr(+) = e2.empno; --有连接
select * from emp
--表连接: 内连接(等值|非等值) | 外连接(左连接|有连接)
--注意: 同名字段,需要指明出处
select e1.*,e2.* from emp e1,emp e2 where e1.mgr = e2.empno(+);
select e1.*,e2.* from emp e1,emp e2 where e1.mgr(+) = e2.empno;