Oracle数据库 语法操作汇总(三)

oracle各种语法操作的汇总第三部分

左右连接
左连接 左表全部显示 select * from emp a left join emp b on a.no=b.no; 或者 select * from emp a,emp b where a.no=b.no(+)
右连接 右表全部表示 select * from emp a right join emp b on a.no=b.no; 或者 select * from emp a,emp b where a.no(+)=b.no

交叉连接 cross join 即笛卡尔乘积连接
自然连接 natural join
using子句 select 列名 from 表名 join 表名 using (共同列) eg.select * from sales join costs using (prod_id)
on子句 select 列名 from 表名 join 表名 on (条件)

组函数COUNT,MAX,MIN,AVG,SUM
GROUP BY后面接的一定要是SELECT里有的
组函数不能放到where后 要用HAVING
eg. select deptno,AVG(sal)
  from emp group by deptno HAVING AVG(sal)>2500;

行总计/列总计
ROLL UP/CUBE
eg.select groupid,job,sum(salary) from gt group by roll up(groupid,job)
先按groupid,job group by 再按groupid group by 以此顺序循环

子查询判断符号in any all
in:用于指定一个子查询的判断范围
any:三种用法 =any与in操作符完全一样  >any 比最小的大的数据  <any 比最大的小的数据

合并查询:合并多个select 语句的查询的结果
1)union 并集
2)union all 并集 不去掉重复行
3)intersect 交集
4)差集 minus

数据更新操作
插入
修改
删除

复制表
create table 新表名 as 数据库来源
eg. create table memp1 as select * from emp

数据更新:
1）insert into 表名(列名1，列名2) values(值1，值2)
eg.insert into memp1(empno) values(1234)
2)省略写法（不推荐）
insert into 表明 values(...)

修改：
update+表名+set+列名1=值1，列名2=值2
全部  update+表名+set+列名1=值1，列名2=值2
局部  update+表名+set+列名1=值1，列名2=值2 where 条件
eg. update memp1 set sal=800,comm=100;

修改日期：
set hiredate = to_data('2019-02-13','yyyy-mm-dd') where...

插入单值
insert into 表名 (select empno,ename from myemp1) values(1,'TOM')
利用临时表
insert into myemp1 (empno,empname) select 2,'allen' from dual；
利用子查询插入批量数据
insert into myemp1 select * from emp;

删除
全部删除：
delete from 表名;
局部
delete 表名 where 条件
eg.删除和7785岗位相同的员工的所有信息
DELETE FROM myemp1 WHERE job=(select job from myemp1 where empno=7785);
增加修改删除语句后一定要记得加上commit（事物处理操作）

截断表
truncate table 表名
eg.TRUNCATE  TABLE  dept;
与delete的区别 DELETE数据在未提交前可以回滚,TRUNCATE则会自动提交。

rownum表示行号
选出5-10行
select * from (select rownum m,empno from emp where rownum<=10) temp where emp.m>5

格式化查询结果
1.格式化列
 column命令
·Heading   column ename heading 员工名 justify center format a7
·Justify
·Clear
·Format

2.压缩列：重复列值的显示
BREAK ON 列名 [SKIP 值]
eg.BREAK ON deptno Skip 1 (SKIP 1 为分组后的间距)
      select deptno,ename from emp order by deptno
为了实现组函数的分组统计操作，可以与compute 结合使用
eg.break on deptno
    compute AVG LABEL '平均工资' of sal on deptno
    select deptno,ename,sal form emp order by deptno

设置标题与页脚
TITLE
eg.TTITLE CENTER 'EMPLOYEES'
      BTITLE TIGHT 'MADE BY XXX'
关闭标签：TTITLE OFF
    
层次查询：
select [level]列名
from 表名
[where 条件]
[start with 列名=值]
[connect by prior 关系]
eg.select lpad(' ',5*level-1)||empno EMPNO,lpad(' ',5*level-1)||ename ENAME
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno=mgr;