连接

连接

一、分组

(1)、分组：group by

--按 部门 查询 平均工资 
select avg(sal) from emp group by deptno; 

--按 部门 查询 平均工资，且平均工资大于2000的部门编号 
--1、先分组 后过滤 (不推荐) 
select * 
  from (select deptno, avg(sal) avsal from emp where 1 = 1 group by 
deptno) 
 where avsal > 2000; 
 
--2、过滤组 ,分组同时 过滤 
select avg(sal), deptno from emp  group by deptno having avg(sal)>2000; 

(2)、过滤组：having

--查询 最低平均工资的部门编号 
--1)、按部门求出平均薪水 
select avg(sal) from emp group by deptno; 
--2)、找出最低的平均薪水 
select min(avg(sal)) from emp group by deptno; 
--3)、过滤组 
select deptno 
  from emp 
 where 1 = 1 
 group by deptno 
having avg(sal) = (select min(avg(sal)) from emp where 1 = 1 group 
by deptno); 

注： where :过滤行记录，不能使用组函数， having:过滤组，可以使用组函数

二、行转列

id	name	course	score
1	张三	语文	81
2	张三	数学	90
3	李四	语文	23
4	王五	语文	98
5	王五	数学	100

转成如下：

姓名	语文	数学
张三	81	90
李四	23
王五	98	100

--
找出课程名(表头) 
select distinct course from tb_student; 
 
--数据(行记录)  分组（学生+行转列 decode） 
select * from tb_student; 
--1、行转列 decode 
select name,decode(course,'语文',score) 语文，
			decode(course,'数学',score) 数学, 
			decode(course,'英语',score) 英语 
			from tb_student; 
--2、分组 
select name, 
       min(decode(course, '语文', score)) 语文, 
       min(decode(course, '数学', score)) 数学, 
       min(decode(course, '英语', score)) 英语 
  from tb_student 
 group by name; 

三、rowid和rownum

(1)、rowid

用于定位数据库中一条记录的一个
相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。
ROWID 它是一个伪列，它并不实际存在于表中.我们可以
将其用于删除重复数据

-- 每条记录的唯一标识 
select s.* , rowid from tb_student s; 
--找出 保留的rowid 
select min(rowid) from tb_student group by name,course; 
--删除 
delete from tb_student 
where rowid not in (select min(rowid) from 
tb_student  group by name,course); 

(2)、rownum

ROWNUM 是一种伪列，它会根据返回记录生成一个序列化的数字。排序后的
结果集的顺序号 ，每一个结果集 都有自己顺序号 ，不能直接查询大于 1 的数。利用
ROWNUM，我们可以生产一些原先难以实现的结果输出。 例如实现分页操作。

--自己 排序后结果集的顺序号 
select e.*, rownum from (select * from emp order by sal desc) e; 
--取出工资前5名 
select e.*, rownum 
  from (select * from emp order by sal desc) e 
 where rownum <= 5; 

四、表连接

1、笛卡尔积

select * from emp , dept; 

2、等值连接(在笛卡尔积基础上 取条件列相同的值)

--找出30部门的员工名称及部门名称:先关联后过滤
select ename, dname, e.deptno from emp e, dept d where 
e.deptno=d.deptno and e.deptno=30; 

3、非等值连接 > < != <>between and

--查询员工姓名，工资及等级
 -- 数据源： emp e, salgrade s
 -- 字段： ename, grade, sal
 -- sal between losal and hisal
 
select ename, grade, sal from salgrade s, emp e where sal 
between losal and hisal; 

4、自连接: 特殊的等值连接 (来自于同一张表)

--找出 存在上级的员工姓名 及上级名称
 -- 数据来源： emp e, emp m
 -- 字段： e.ename, m.ename
 -- 条件： e.mgr=m.empno

select e.ename, m.ename from emp e, emp m where 
e.mgr=m.empno; 

5、外连接

--找出 所有的员工姓名 及上级名称 
--找出 所有部门的员工数 及部门名称 
select dname, nu from dept d, (select count(1) nu, deptno  from emp 
group by deptno) e  
       where d.deptno(+)=e.deptno; 

注：看“+”和“,” ， 主表在“,”的左边就叫左外连接， 主表在“,”的右边叫右连接

五、99连接

1、交叉连接 cross join 

select * from emp cross join dept; 

2、自然连接(主外键、同名列) natural join -->等值连接

select * from emp natural join dept; 
--在指定列过程中同名列归共同所有(*除外) 
select deptno,e.ename,d.dname from emp e natural join dept d; 
 

3、using 连接 -->等值连接

select deptno,e.ename,d.dname from emp e   join dept d 
using(deptno); 

4、[inner]join on 连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分

 --部门编号为30的员工名称 上级名称 
select e.ename,m.ename mname  from emp e join emp m 
 on e.mgr =m.empno where e.deptno =30; 

5、left|right [outer] join on|using -->外连接

--所有部门的 部门名称，员工数 
--左外 
select dname, n 
  from dept d 
  left outer join (select deptno, count(1) n from emp group by deptno) i 
on d.deptno = i.deptno; 
 
select dname, n 
  from dept d 
  left  outer join (select deptno, count(1) n from emp group by deptno) i  
using (deptno); 
--右外     
select dname, n 
  from  (select deptno, count(1) n from emp group by deptno) i 
  right outer join  dept d 
    on d.deptno = i.deptno; 

6、full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出现一次

select * 
  from (select 1 no, 'a' "name" 
          from dual 
        union 
        select 2 no, 'b' "name" from dual) a 
  full join (select 1 no, 'c' "name" 
               from dual 
             union 
             select 3 no, 'd' "name" from dual) b 
 using(no); 

六、视图

(1)、作用

1. 简化:select 查询语句
2. 重用:封装select语句 命名
3. 隐藏:内部细节
4. 区分:相同数据不同查询

注;不是所有的用户都有创建视图的权限

(2)、创建视图（必须有dba权限）

create view v_emp as select ename,job from emp;

(3)、查询视图

select * from v_emp;

七、索引

（1）、创建索引

create index 索引名 on表名 (字段列表...)
create index idx_emp on emp(sal,ename); 

（2）、删除索引

drop  index 索引名 ；
drop index idx_emp;