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后添加(+)。
注:如果出现同名字段需指明出处。