SQL语句基础3

SQL语句基础3

注：除指明表以外所有的数据来源于Oracle数据库自带的SCOTT用户表。

1. 分组

使用组函数针对每一组数据进行计算时需要先进行分组，使用having过滤组信息，具体语法如下：

select 数据 from 数据源 where 行过滤条件 group by 分组字段1,分组字段2.. having 过滤组信息  order by 排序字段1 desc|asc,排序字段2..;

执行流程：from --> where --> group by --> having --> select --> order by

--查询最低平均工资的部门编号
select deptno,avg(sal)
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);
--查询10,20部门中最高平均工资的部门名称
select dname
  from dept
 where deptno in
       (select deptno
          from emp
         group by deptno
        having deptno in(10, 20) and avg(sal) = (select max(avg(sal))
                                                  from emp
                                                 group by deptno
                                                having deptno in(10, 20)));

注： select后面如果出现组函数，只能与其他组函数或者分组字段一起使用，where后面不能出现组函数，having后面可以使用组函数。

2. 行转列

创建新的表，代码如下：

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;

行转列：

--按照人名进行分组，原表中有一个人，查询的结果集中就有几条数据，根据人分组
select name,
       max(decode(course, '语文', score)) 语文,
       min(decode(course, '数学', score)) 数学,
       max(decode(course, '英语', score)) 英语
  from tb_student
 group by name;

3. 表连接

当要查询的数据来自于多张表中，需要使用表连接，表连接包括内连接和外连接。**本文所统计的均为92语法的表连接，**语法如下：

select 数据 from 数据源1,数据源2,.....;

笛卡尔积：两个数据源中的数据分别连接，对乘的效果

表的连接条件：笛卡尔积的效果不满足要求，想要过滤其中一些数据时就需要添加表的连接条件，语法如下：

select 数据 from 数据源 1,数据源2,.... where 连接条件|行过滤条件;

3.1 内连接

内连接分为三种类型：

• 等值连接：

--查询员工的个人信息及其所在部门信息
select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

• 非等值连接：

--查询员工信息及其对应的工资等级
select e.*,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

• 自连接：

--查询有上级的员工的员工信息及其上级信息
select e1.*,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno; 

3.2 外连接

外连接分为左连接和右连接。

外连接中有主表的概念，主表即其中的数据就算不满足连接条件也能展示。使用方法是在连接条件处主表的对面结束位置添加(+)。

from后主表写在左边即左外连接，写在右边即右外连接。

--查询所有的员工信息及其上级信息（左连接 主表为e1）
select e1.*,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+); 

上述语句中因为from后e1写在左边，因而e1为主表，连接条件处在e2.empno后添加(+)。

注：如果出现同名字段需指明出处。