笔记：ORACLE数据库基础学习 第四天

于 2017-08-17 19:39:13 发布
阅读量177 收藏
点赞数
文章标签： oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/baidu_29472129/article/details/77340216
版权 
--子查询
--子查询是一条查询语句，它是嵌套在其他SQL语句中的。
--目的是将查询的结果提供给外层SQL语句。

--查询谁的工资高于CLARK的工资？
SELECT ename,sal
FROM emp_gl
WHERE sal>(SELECT sal FROM emp_gl 
           WHERE ename='CLARK')

--谁和CLARK同部门？
SELECT ename,deptno
FROM emp_gl
WHERE deptno=(SELECT deptno FROM emp_gl
              WHERE ename='CLARK')
              
--查看高于公司平均工资的员工
SELECT ename,sal
FROM emp_gl
WHERE sal>(SELECT AVG(sal) FROM emp_gl)

--在DDL中使用子查询，可以将一个子查询的结果集当做表快速创建出来。
CREATE TABLE employee_gl AS
  SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc
  FROM emp_gl e,dept_gl d
  WHERE e.deptno=d.deptno

DESC employee_gl

SELECT * FROM employee_gl

--在使用子查询创建表的时候，查询多少个字段创建的表就有
--多少个字段，字段名与查询的字段名字一致（不含有表别名）
--类型，长度也一致。但是需要注意的是，若查询的字段是函数
--或者表达式，那么这个字段必须指定别名，并且生成的表对应
--的该字段名就是这个别名。

--DML中使用子查询

--将CLARK所在部门的员工工资上浮10%
UPDATE emp
SET sal=sal*1.1
WHERE deptno=(SELECT deptno FROM emp_gl WHERE ename='CLARK')

SELECT ename,sal
FROM emp_gl
WHERE deptno=10

--子查询根据查询结果分为：
--单行单列，多行单列，多行多列子查询
--其中单列子查询常用在过滤条件中，多列子查询常被当作表使用

--对于多行单列子查询在进行过滤判断时，需要配合IN，ANY，ALL使用

--查看与职位是SALESMAN同部门的员工？
SELECT ename,job ,deptno
FROM emp_gl
WHERE deptno IN(SELECT deptno FROM emp_gl 
                WHERE job='SALESMAN')

--查看比职位是SALESMAN和CLERK 工资都高的员工？
SELECT ename,sal,job
FROM emp_gl
WHERE sal>ALL(SELECT sal FROM emp_gl
              WHERE job IN('SALESMAN','CLERK'))
              
SELECT deptno,dname,loc
FROM dept_gl d
WHERE EXISTS(SELECT * FROM emp_gl e
             WHERE e.deptno=d.deptno)

--EXISTS关键字
--EXISTS用在WHERE中进行过滤，其后根一个子查询

--查看有下属的员工？
SELECT m.ename,m.sal,m.job
FROM emp_gl m
WHERE EXISTS(
  SELECT * FROM emp_gl e
  WHERE e.mgr=m.empno
)

--HAVING使用子查询：
--查看每个部门的最低薪水，前提是要高于30号部门的最低薪水
SELECT MIN(sal),deptno
FROM emp_gl
GROUP BY deptno HAVING MIN(sal)>(
  SELECT MIN(sal) FROM emp_gl
  WHERE deptno=30
)

--在FROM中使用子查询
--通常多列子查询在FROM子句中出现，作用是当做一张表使用

--查看高于自己所在部门平均工资的员工？
SELECT e.ename,e.sal,e.deptno
FROM emp_gl e,(SELECT deptno,AVG(sal) avg_sal
               FROM emp_gl
               GROUP BY deptno) x
WHERE e.deptno=x.deptno
AND e.sal>x.avg_sal

--子查询在SELECT部分
SELECT e.ename,e.sal,(
  SELECT d.dname FROM dept_gl d
  WHERE d.deptno=e.deptno
) dname
FROM emp_gl e

--分页查询
--分页查询就是将表中要查询出来的数据分批分段查询出来
--这样做的好处就是当查询的数据量过大时可以减少内存开
--销，提高系统响应速度，减少网络传输。
--分页是方言，不同数据库都提供了分页查询的机制，但是
--语法定义各不相同。

--ORACLE是依靠提供了一个伪列：ROWNUM
--ROWNUM不存在于任何一张表中，但是每张表都可以查询
--该字段，该字段在结果集中的值就是每条记录的行号。
--而行号的生成是伴随查询过程进行的，只要可以从表中查
--询出一条记录，ROWNUM字段就是为该条记录在结果集中
--产生对应的行号，从1开始递增。
SELECT ROWNUM,ename,job,sal
FROM emp_gl
SELECT ROWNUM,deptno,dname,loc
FROM dept_gl

--需要注意，在使用ROWNUM对结果集编行号的过程中，不要
--使用ROWNUM做大于1以上数字的判断，否则得不到结果。

SELECT *
FROM (SELECT ROWNUM row_num,ename,job,sal
      FROM emp_gl) e
WHERE e.row_num BETWEEN 6 AND 10

--查看工资排名的第6-10个员工
SELECT * 
FROM (SELECT ROWNUM rn,ename,job,sal
      FROM(SELECT ename,job,sal
           FROM emp_gl
           ORDER BY sal DESC
           )
      WHERE ROWNUM <=10
     ) 
WHERE rn >=6

--换算范围的公式：
--PageSize:每页可以显示的条目数
--Page：页数

--start:(Page-1)*PageSize+1
--end:PageSize*Page

--DECODE 函数
--DECODE 函数可以实现分支结构

SELECT ename,sal,job ,
       DECODE(job,
              'MANAGER',sal*1.2,
              'ANALYST',sal*1.1,
              'SALESMAN',sal*1.05,
               sal) bonus
FROM emp_gl

--CASE 语句
SELECT ename,sal,job,
       CASE job WHEN 'MANAGER' THEN sal*1.2
                WHEN 'ANALYST' THEN sal*1.1
                WHEN 'SALESMAN' THEN sal*1.05
                ELSE sal END
       bonus
FROM emp_gl

--DECODE 在GROUP BY中可以实现将字段不同的值的记录分为一组。

SELECT COUNT(*),DECODE(job,
                'MANAGER','VIP',
                'ANALYST','VIP',
                'OTHER') 
FROM emp_gl
GROUP BY DECODE(job,
                'MANAGER','VIP',
                'ANALYST','VIP',
                'OTHER')


SELECT deptno,dname,loc
FROM dept_gl
ORDER BY DECODE(dname,'OPERATIONS',1,
                      'ACCOUNTING',2,
                      'SALES',3)

--排序函数
--排序函数可以将结果集按照指定的字段分组
--然后组内再按照指定的字段排序后对每组的
--记录产生一个行号。

--ROW_NUMBER()：生成组内连续且唯一的数字

--查看每个部门的工资排名？
SELECT ename,sal,deptno,
       ROW_NUMBER() OVER(
       PARTITION BY deptno
       ORDER BY sal DESC
       ) RANK
FROM emp_gl

--RANK()：生成组内不连续不唯一的数字
SELECT ename,sal,deptno,
       RANK() OVER(
       PARTITION BY deptno
       ORDER BY sal DESC
       ) rank
FROM emp_gl

--DENSE_RANK()：生成组内连续但不唯一的数字
SELECT ename,sal,deptno,
       DENSE_RANK() OVER(
       PARTITION BY deptno
       ORDER BY sal DESC
       ) rank
FROM emp_gl

-----------------------------------------------------
CREATE TABLE sales_tab_gl(
  year_id NUMBER NOT NULL,
  month_id NUMBER NOT NULL,
  day_id NUMBER NOT NULL,
  sales_value NUMBER(10,2)NOT NULL
)

INSERT INTO sales_tab_gl
SELECT TRUNC(DBMS_RANDOM.value(2010,2012))AS year_id,
       TRUNC(DBMS_RANDOM.value(1,13))AS month_id,
       TRUNC(DBMS_RANDOM.value(1,32))AS day_id,
       ROUND(DBMS_RANDOM.value(1,100))AS sales_value
FROM  dual
CONNECT BY level<=1000;

DROP TABLE sales_tab_gl
-----------------------------------------------------

--并集
SELECT ename,job,sal FROM emp_gl
WHERE job ='MANAGER'
UNION
SELECT ename,job,sal FROM emp_gl
WHERE sal>2500

--全并集
SELECT ename,job,sal FROM emp_gl
WHERE job ='MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp_gl
WHERE sal>2500

--交集
SELECT ename,job,sal FROM emp_gl
WHERE job='MANAGER'
INTERSECT
SELECT ename,job,sal FROM emp_gl
WHERE sal>2500

--差集
SELECT ename,job,sal FROM emp_gl
WHERE job='MANAGER'
MINUS
SELECT ename,job,sal FROM emp_gl
WHERE sal>=2500


--高级分组函数

--ROLLUP () 函数
--ROLLUP中的每一个参数就是需要分组的字段
--ROLLUP会将参数字段逐个递减直至一个没有
--然后将这些分组统计的结果并在一个结果集中显示

--GROUP BY ROLL(a,b,c)
--等价于：
--GROUP BY a,b,c
--UNION ALL
--GROUP BY a,b
--UNION ALL
--GROUP BY a
--UNION ALL
--全表

--在一个结果集中看到每天，每月，每年以及总共的营业额
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab_gl
GROUP BY ROLLUP(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id

--CUBE()函数，将每个参数的每种组合都进行一次分组
--然后将所有统计的结果并在一个结果集中显示，所以
--CUBE分组次数为2的参数个数次方
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab_gl
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id

--GROUPING SETS()
--GROUPING SETS的每一个参数是一种分组方式
--它会将这些分组统计的结果并在一个结果集中显示。

--查看每天与每月营业额？

SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab_gl
GROUP BY GROUPING SETS(
(year_id,month_id,day_id),
(year_id,month_id)
)
ORDER BY year_id,month_id,day_id
--1:查看与CLARK相同职位的员工
SELECT ename,job,deptno
FROM emp_gl 
WHERE job=(SELECT job FROM emp_gl WHERE ename='CLARK');
--2:查看低于公司平均工资的员工
SELECT ename,sal
FROM emp_gl
WHERE sal<(SELECT AVG(sal) FROM emp_gl);
--3:查看与ALLEN同部门的员工
SELECT ename,deptno
FROM emp_gl
WHERE deptno=(SELECT deptno FROM emp_gl WHERE ename='ALLEN');
--4:查看平均工资低于20号部门平均工资的部门平均工资
SELECT AVG(SAL),deptno
FROM emp_gl
GROUP BY deptno HAVING AVG(sal)<(
SELECT AVG(sal) FROM emp_gl WHERE deptno=20);
--5:查看低于自己所在部门平均工资的员工
SELECT e.ename,e.sal,e.deptno
FROM emp_gl e,(SELECT AVG(sal) avg_sal,deptno FROM emp_gl GROUP BY deptno) d
WHERE e.deptno=d.deptno
AND e.sal
素年锦时琉璃醉
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值