SQL分组查询group by

表的分组查询-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;