SQL知识点2 - oracle

多表连接

通过连接的条件，使表之间发生关联，从而同时获取多表的数据

笛卡尔积： 左表( a行，b列 ) 的所有行与 第二个表( c行，d列 ) 的所有行都连接 - 即输出表 (ac行，b+d列)

• 笛卡尔积出现的情况：
  1. 连接条件被省略
  2. 连接条件无效

笛卡尔积 - 交叉连接

select  *  from emp,dept,salgrade     // 即无where限定条件进行连接  

1. oracle连接写法

等值连接

where型指定列等值

符合where条件的 笛卡尔积

• 注意事项：
  • 列名相同时，需指定表前缀
  • 增加表前缀，能提高性能

select emp.*, dept.* 
    from emp, dept
    where emp.deptno = dept.deptno  

非等值连接

select emp.*, salgrade.*
    from emp, salgrade
    where sal between salgrade.losal and salgrade.hisal

外部连接 - 把等值连接某表被排除的行也能够显示出来

where中 在需要显示 被排出行的表 的 另一个表 后加 (+)

 

左外连接

select e.ename, d.deptno, d.dname
    from emp e, dept dd
    where e.deptno = d.deptno(+)    // 输出被未选中的e表的信息
    //where e.deptno(+) = d.deptno()   右连接

2. 标准ANNSI连接写法 - 任何数据库都可以用

语法：
  select 列名 from 表名
    cross join 表名   → 笛卡尔积
    natural join 表名   → 自然等值连接
    join 表名 using (列名) where ( 列名的条件 )   → 等值连接
    join 表名 on( 类似where连接条件 )   → 等值连接
    cross left/right/join join 表名 on ( 类似where连接条件 )   → 外连接

笛卡尔积-交叉连接(cross join)

 select * from emp cross join dept, salgrade;
 
 select * from emp, dept cross join salgrade;

等值连接

自然连接：只要列名、数据类型一样系统自动等值

两个表之间 相同名字 和 数据类型 的列进行的自动等值连接；
列名同、数据类型不同 → 产生错误
不能使用 *

select emp.* , dept.*
    from emp
    natural join dept;

 

自定义相同列名等值连接：join… using (列名) …

列名 与 列数据类型一致才能连接 , 否则报错

    select e.ename, d.dname, loc 
        from emp e
        join dept d using (deptno)
        // where deptno = 20;  指定的列进行连接，不写这个则只要deptno相同就连接

 

自定义不同列名等值连接：join 列名 on ( 类似where连接条件 ) → 相同列名也行

select e.ename, d.dname, d.loc
    from emp e
    join dept d
    on e.emptno = d.deptno     // 可指定列名不同进行连接，只要数据类型一致就行  
    
    //同一个select中  -  可同时写多个以下连接结构
   // join emp m 
   // on  e.mgr = m.empno

 

外连接

左/右外连接：left/right join 表名 on (连接条件)

select e.ename, d.dname, d.loc
    from emp e
    left/right join dept d
    on e.emptno = d.deptno

全外连接：full join 表名 on (连接条件)

左外连接、右外连接的合并输出 → 即 左外连接 union 右外连接

select e.ename, d.dname, d.loc
    from emp e
    full  join dept d
    on e.emptno = d.deptno

 

子查询

1. 优先执行子查询语句 - 括号的优先级最高 → 子查询能直接检索出数据行，而不依赖主语句
 
2. 相当于有 明确筛选条件 的SQL检索语句
 
3. 子查询语句可放至 from 、where、having的后面
 
4. 使用 not in 子查询中存在有空行，则最后主查询的结果也是空行 — 切记排出子查询中的空行

 
为什么出现子查询空行，而主查询一点数据都没有？

select * from emp where empno not in (select mgr from emp);   -- 输出空行，逻辑没问题

( not in ) =  ( != )     //所以子查询的    !=null在DQL语句中where中是没有的

( not in ) ≠ ( is not null  )

1. 单行子查询 - 仅能一行一列(即一个)数据

where，having 不允许多列的单行子查询输出
from 可以把单行的多列的子查询作为一个表

select * from emp
    where sal > ( select sal where emp where  empno=7369 ) 
    // where sal > (select empno, sal where emp where  empno='7369')   报错
    

// where相当与执行了两次单行子查询语句，分别是 ① > 7369  ② > 7844的  且输出的数据行不会去重 ---- - 单行数据可以这样用，多行子查询数据不建议这样用
select *
    from emp e,  (select empno, sal where emp where  empno in (7369, 7844) )  sal2
    where e > sal2.sal
   

 
 

单行子查询使用的比较运算符：

2. 多行子查询 - 多行一列

多行子查询 检索输出 多行数据
 
多行子查询使用的运算符：in( 等于其中一个 )、any( 符合其中一个 )、all( 符合全部 )

• > any (多行子查询)： 主查询 大于 子查询中的 最小值
• = any (多行子查询)： 相当于 in
• < any (多行子查询)： 主查询 小于 子查询中的 最大值
• < all (多行子查询)： 主查询 小于 子查询中的 最小值
• > all (多行子查询)： 主查询 大于 子查询中的 最大值
• = all (多行子查询)： 主查询 大于 子查询中的 最大值 – 无意义，但能用，单行查询

 

select ename, sal 
    from emp 
    where sal > all ( select sal from from emp where deptno = 10 )

3. 多列子查询

通常用 in操作符

select ename, job
    from emp 
    where ( deptno, job) in (select deptno, job from emp where to_char( hiredate, 'yyyy') = '1991' )

高级子查询-(嵌套、相关、exists子查询)

1. 嵌套子查询

• 执行顺序：
  1. 子查询执行一次
  2. 用子查询的值 确认 主查询的候选行 是否输出

// 查询比本部门平均薪水高的员工

select deptno, ename, sal
    from  emp e, ( select deptno, avg(sal) avgsal where emp group by deptno ) avg_emp
    where e.deptno = avg_emp.deptno
    and e.sal > avg_emp.avgsal;

 

2. 相关子查询

跟单行子查询差不多，单行子查询where是有显示的数据，而相关子查询where是取自主查询中的每一行

相关子查询：父查询每执行一行，则运行一次相关子查询进行 筛选当前行 ----即主表有n条数据，则执行n次子查询
单行子查询：子查询优先执行，且只执行一次子查询

// 查询比本部门平均薪水高的员工

select deptno, ename, sal
    from emp emp_outer
    where sal > ( select avg(sal) 
                      from emp 
                      where deptno=emp_outer.deptno  
                      group by deptno 
                     )
                     
// 查询所有部门的名称、人数
select deptno, ( select count(*)  from emp where  deptno=emp_outer.deptnogroup by deptno )
    from emp emp_outer;

 

exists操作符查询

此子查询操作符单纯只是检索记录是否存在( true/false )，而不会检索返回出记录 — 效率快

// 查询职位是经理的员工信息

select  * 
    from emp e
    where exists ( select '1' from emp where mgr=e.empno );       // 子查询中的 '1' 是占位符的作用

层次查询

集合运算( union、intersect、minus )

使用的原则
1. 集合的查询语句必须 列数、列数据类型一致 → 即列名可不一致
2. order by 只能在集合运算之后使用

1. union - 并集

• 特有的使用细节：
  1. 联合后按首列升序排列
  2. 不包含重复行 – 合并去重

select deptno from emp
union 
select deptno from dept
order by deptno desc;

 

2. union all

• 特有的使用细节：
  1. 包含重复行 – 合并不去重
  2. 联合后不对结果集进行排序

select deptno from emp
union all
select deptno from dept
order by deptno desc;

 

3. intersect - 交集

• 特有的使用细节：
  1. 不忽略空值
  2. 多个查询结果的公有行

// 查询 emp、dept 公有的部门号

select deptno from emp
intersect
select deptno from dept;

 

4. minus

• 特有的使用细节：
  1. 第一个查询存在，而第二个查询不存在的记录
  2. 不忽略空值

// 查询哪些员工没做过岗位调动

select empno from emp
minus 
select empno from emp_jobhistory;

 

伪列

类似表中的列、实际并 没有存储在表中的特殊列

1. RowNum

基本的使用

1. 功能：返回结果集的顺序号 → 顺序号在记录输出时才一步一步产生 — 首行为1\

2. 注意： RowNum是在输出数据时才会生成该列值，即 rownum > 2 是不可能实现的 → 永远没有结果，返回空值

3. 只要经过where筛选之后，rownum就生效了 或者 from中有子查询伪列 → 类似已存在此表中

4. 主查询的伪列rownum 不能够设置别名 → 说明子查询中可以

 

• rownum一般的使用

select rownum, emp.*  from emp;

 
   输出结果

 

• 注意rownum是在输出表时才生成，故不能rownum > n 进行筛选输出

select *  from emp where rownum > 2;     // 返回空值 - 正在生成有伪列的表，只有输出伪列才开始赋1，一直被筛选被第一条，故永远都没有伪列是2的rownum值

 

TOP-N查询

输出 order by 排序后 的前几条数据行

• 有rownum的子查询放置在from中，必须 伪列有别名才可以使用

select * 
    from (select rownum rn, emp.* from emp) newTable   //  可看成rownum列永久存在
    where rn > 3;
    
    
// select rownum rn, emp.* from emp where rn > 3   ---- 直接报错别名无效- 主查询伪列不可以设置别名 

 

分页查询

即把一个表按某列排序后、分成几个选项框( 固定行数 ) 显示出来

列名的生成是在select( DQL语句的末尾执行 )，所以当用where输出数据时，不能用列名进行where
子查询的列名名生成，主查询的where即可以使用子查询的列名
语法：

// 方法1  ----  效率较快，每次子查询都先筛选了一部分
select * 
    from ( select rownum rn, b.*
                from ( select 列名 from 表名 order by 排序列 desc/asc  ) as b
                where rownum <= （ 第n页 * 每页的行数 )  // rn列别名不可用，因为还没执行select
    )
    where rn > (n - 1) * 每页的行数;
    
//方法2   ----   效率慢，每次子查询都输出所有
select * 
    from ( select rownum rn, b.*
                from ( select 列名 from 表名 order by 排序列 desc/asc  ) as b
    )
    where rn > (n - 1) * 每页的行数 and rownum <= （ 第n页 * 每页的行数 ) ;

 

2. Sequence序列

自动增加、减少数字的数据库对象 – 类似 i++
通常使用序列生成主键值

创建序列

  语法结构

create sequence 序列名
【 increment by n 】                      // 每次的递增的增量  默认为1
【 start with n 】                          // 序列的起始值  默认为1
【maxvalue n 或者 nomaxvalue】      // 序列最大值 或者  无最大值
【 minvalue n 或者 nomivalue 】     // 序列最小值  或者  无最小值
【 cycle 或者 nocycle 】                // 序列达到最大/小值时，是否重复序列值   默认不重复序列值
【 cache n 或者 nocache 】          // 序列值被预先分配到内存中  默认为20

 

  示例

create sequence test_eq
start with 10
increment by 2
maxvalue 100
minvalue 9
cycle 
cache 10;

 

nextval、currval – 序列的伪列

序列名.nextval：序列的下一个值
序列名.currval：序列的当前值

 

• 注意事项

  • 最初 currval 被用之前，必须先使用 nextval 产生出第一个值
     

  • 能够使用该伪列的情况：

    • insert语句中的 子查询中的select列名列表、values子句中
    • update语句中的 set子句
    • select主查询的 select列名列表、但不包括其子查询中的select列表
       

  • 不能使用该伪列的情况：

    • 包含 distinct关键字 的select查询
    • 包含 group by、having、order by关键字 的select查询
    • select、update、delete 语句的子查询中

 
  使用伪列

select test_sq.nextval, emp.* from emp;

update emp set empno=test_sq.nextval 

insert into emp( empno, ename ) select test_sq.nextval, 'testname'  from emp;

insert into emp( empno, ename ) values ( test_sq.nextval, 'testname' );

select test_sq.currval from dual    // 查看当前的序列值

 

修改序列

注意事项

• 序列不能修改 start with – 即起始值
• 修改序列的最大值maxvalue 不能小于 序列当前值

  语法结构

alter sequence 序列名
【 increment by n 】                                         
【maxvalue n 或者 nomaxvalue】      
【 minvalue n 或者 nomivalue 】     
【 cycle 或者 nocycle 】              
【 cache n 或者 nocache 】       

 
  示例

alter sequence test_sq
    increment by 10
    maxvalue 100
    nocycle
    cache 50;

 

删除序列

 
  语法结构

drop sequence 序列名;

 

3. RowId

标示的是每一条数据行的物理地址 → 能快速定位一条行记录( 检索效率最快 )
 
rowid字符格式

 
  示例

select rowid, dept.* from dept;