ORACLE---Unit04： SQL（高级查询）

最新推荐文章于 2024-05-25 10:57:37 发布
最新推荐文章于 2024-05-25 10:57:37 发布
阅读量100 收藏
点赞数
文章标签： java 数据库
原文链接：http://www.cnblogs.com/sneg/p/7813308.html
版权

---（重点复习：子查询、分页查询、decode）
--- SQL（高级查询）

--- 子查询
--- 子查询是嵌套在其它SQL语句当中的，目的是为嵌套的SQL提供数据，以便其执行。

---查看谁的工资高于CLARK?
---1.先查CLARK的工资
SELECT sal FROM emp_RR WHERE ename='CLARK';
--->2450
---2.工资高于CLARK的员工信息
SELECT ename,sal FROM emp_RR WHERE sal>2450;
--->5条

SELECT ename,sal FROM emp_RR WHERE sal>
(SELECT sal FROM emp_RR WHERE ename='CLARK');
---子查询加括号，放在括号里面。为了外层数据，而提供数据的。

---查询谁跟FORD一个部门的
SELECT ename,deptno FROM emp_RR WHERE deptno=
(SELECT deptno FROM emp_RR dept_RR WHERE ename='FORD'); ---查看20号部门的员工
--->4条记录
SELECT * FROM dept_RR;

---查看工资高于公司平均工资的员工？
SELECT ename,sal FROM emp_RR WHERE sal>
(SELECT AVG(sal) FROM emp_RR );

---查看公司最高工资是谁？
SELECT ename,sal FROM emp_RR WHERE sal=
(SELECT MAX(sal) FROM emp_RR); ---最高工资5000

---查看部门的平均工资，前提是该部门的平均工资要高于30号部门的平均工资？
SELECT AVG(sal) FROM emp_RR WHERE deptno=30;
--->1566.666666666666666666666666666666666667

SELECT AVG(sal),deptno FROM emp_RR
GROUP BY deptno HAVING AVG(sal)>
(SELECT AVG(sal) FROM emp_RR WHERE deptno=30);

---在DDL与DML中也可以使用子查询
---1：在DDL中使用子查询，可以将一个子查询的结果集当做表快速创建。

---创建一张表，含有empno,ename,sal,job,deptno,dname,loc.
---并且数据就是现有表emp,dept中的数据？

CREATE TABLE emp_RRYY AS
SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc FROM emp_RR e,dept_RR d
WHERE e.deptno=d.deptno(+);
--->少了scott
--->table EMP_RRYY 已创建。
DESC emp_RRYY
SELECT * FROM emp_RRYY;
DROP TABLE emp_RRYY;
--->table EMP_RRYY已删除。

CREATE TABLE emp_RRYY AS
SELECT e.empno id,e.ename name ,e.sal,e.job,d.deptno,d.dname,d.loc FROM emp_RR e,dept_RR d
WHERE e.deptno=d.deptno(+);
---在子查询中，如果字段取了别名，新创建的表对应的字段就是用的别名。没有别名，就是用字段名。

---DML中使用子查询
---将SIMTH所在部门所有员工工资上浮10%
SELECT deptno FROM emp_RR WHERE ename='SMITH'; ---SMITH部门号是20

UPDATE emp_RR SET sal=sal*1.1 WHERE deptno=
(SELECT deptno FROM emp_RR WHERE ename='SMITH');
--->4 行已更新。
ROLLBACK;
--->回退完成。
SELECT * FROM emp_RR;

---将SIMITH所在部门的员工都删了
DELETE FROM emp_RR WHERE deptno=
(SELECT deptno FROM emp_RR WHERE ename='SMITH');
--->4 行已删除。(只有10条记录了)
ROLLBACK;
--->回退完成。


--- 一、子查询 (像Java中的内部类，嵌套在其它SQL语句当中的)
--- 1.子查询在WHERE子句中
--- 1.1）在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果
--- 1.2）为了给查询提供数据而首先执行的查询语句叫做子查询
--- 1.3）子查询是嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中
--- 1.4）子查询嵌入的语句称作主查询或父查询
--- 1.5）主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句


--- 1.6）根据饭后结果的不同，子查询可分为单行子查询、多行子查询及多列子查询
--- 1）单行单列子查询：一行一列就是一个值。
--- 单列大多数当做过滤条件，放在where、having里去使用。
--- 2）多行单列子查询：多行一列就是好几个值。
--- 3）多行多列子查询：多行多列就是两个维度。
--- 多列大多数当做一张表使用，表进行查询工作。
--- 1.7）
---查找和SCOTT同职位的员工：
SELECT e.ename,e.job FROM emp_RR e
WHERE e.job=
(SELECT job FROM emp_RR WHERE ename='SCOTT'); ---先执行子查询得到结果后再进行主查询
---查找薪水比整个机构平均薪水高的员工
SELECT deptno,ename,sal
FROM emp_RR e
WHERE sal>(SELECT AVG(sal)FROM emp_RR);

---子查询根据查询结果不同，分为：单行单列子查询、多行单列子查询和多行多列子查询。
---其中单列子查询常用在过滤条件中，而多列子查询通常当做表看待。

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

---查看与SALESMAN同部门的其他职位员工？
SELECT deptno,ename FROM emp_RR WHERE job='SALESMAN';

SELECT ename,job,deptno FROM emp_RR WHERE deptno=
(SELECT deptno FROM emp_RR WHERE job='SALESMAN');
--->ORA-01427: 单行子查询返回多个行

SELECT ename,job,deptno FROM emp_RR WHERE deptno IN
(SELECT deptno FROM emp_RR WHERE job='SALESMAN');

SELECT ename,job,deptno FROM emp_RR WHERE deptno IN
(SELECT deptno FROM emp_RR WHERE job='SALESMAN')
AND job<>'SALESMAN';

---查看比职位是SALESMAN和CLERK工资都高的员工？
SELECT ename,sal FROM emp_RR
WHERE sal>ALL(SELECT sal FROM emp_RR WHERE job IN('SALESMAN','CLERK'));


--- 1.8）如果子查询返回多行，主查询中要使用多行比较操作符
--- 1.9）多行比较操作符包括IN、ALL、ANY。其中ALL和ANY不能单独使用，
--- 需要配合单行比较操作符>、>=、<、<=一起使用
---查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：
SELECT empno,ename,job,sal,deptno
FROM emp_RR
WHERE deptno IN
(SELECT deptno FROM emp_RR WHERE job='SALESMAN') ---子查询的结果是多个值
AND job<>'SALESMAN';

--- 2.0）在子查询中需要引用到主查询的字段数据，使用EXISTS关键字
--- 2.1）EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE.
--- 2.2）列出来那些有员工的部门信息
SELECT deptno,dname FROM dept_RR d
WHERE EXISTS
(SELECT * FROM emp_RR e WHERE d.deptno=e.deptno);

---EXISTS关键字
---EXISTS关键字用在过滤条件中，其后需要跟一个子查询，当该子查询可以查询出至少一条记录时，即判断为满足条件。

---查看有员工的部门？
SELECT d.deptno,d.dname,d.loc FROM dept_RR d ---查出dept表中的所有信息
WHERE EXISTS
(SELECT * FROM emp_RR e WHERE e.deptno=d.deptno);
--->40号部门没有

SELECT d.deptno,d.dname,d.loc FROM dept_RR d
WHERE NOT EXISTS
(SELECT * FROM emp_RR e WHERE e.deptno=d.deptno);
--->40 OPERATIONS BOSTON

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


--- 2.子查询在HAVING子句中
---查询列出最低薪水高于部门30的最低薪水的部门信息
SELECT deptno,MIN(sal) min_sal FROM emp_RR
GROUP BY deptno
HAVING MIN(sal)>
(SELECT MIN(sal)FROM emp_RR WHERE deptno=30);

--- 3.子查询在FROM部分
--- 3.1)FROM子句用来指定要查询的表
--- 3.2)如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或匿名视图
--- 3.3）把子查询当做视图对待，但视图没有名字，只能在当前的SQL语句中有效

---查询出薪水比本部门平均薪水高的员工信息
SELECT e.deptno,e.ename,e.sal FROM emp_RR e,
(SELECT deptno,AVG(sal) avg_sal FROM emp_RR GROUP BY deptno)x
WHERE e.deptno=x.deptno and e.sal>x.avg_sal
ORDER BY e.deptno;

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

---查看谁的工资高于自己所在部门平均工资？
---1.每个部门的平均工资是多少
SELECT AVG(sal),deptno FROM emp_RR GROUP BY deptno;
--->有4个部门的平均工资数

SELECT deptno,AVG(sal)FROM emp_RR GROUP BY deptno;

---SELECT e.ename,e.sal,e.deptno FROM emp_RR e,T t
---WHERE e.deptno=t.deptno
---AND e.sal>t.AVG(sal);

SELECT e.ename,e.sal,e.deptno FROM emp_RR e,(SELECT deptno,AVG(sal)FROM emp_RR GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.AVG(sal);
--->ORA-00904: "T"."AVG": 标识符无效
--->聚合函数：不能出现变量值。

SELECT e.ename,e.sal,e.deptno FROM emp_RR e,(SELECT deptno,AVG(sal) avg_sal FROM emp_RR GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.avg_sal;

---查看每个部门最高工资是谁？
SELECT e.ename,e.sal,e.deptno FROM emp_RR e,(SELECT deptno,MAX(sal) max_sal FROM emp_RR GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal=t.max_sal;


--- 4.子查询在SELECT部分(把查询的结果当成字段来用)
--- 把子查询放在SELECT 子句部分，可以认为是外连接的另一种表现形式，使用更灵活
SELECT e.ename,e.sal,
(SELECT d.deptno FROM dept_RR d WHERE d.deptno=e.deptno) deptno
FROM emp_RR e;


SELECT e.ename,e.sal,
(SELECT d.dname FROM dept_RR d WHERE d.deptno=e.deptno) dname
FROM emp_RR e;
---外连接的写法
---把查询的结果当成字段值来用(这种查询将来用的少，大部分子查询都是用在WHERE,HAVING里)


---分页查询
---当一个查询语句查询的数据量非常大时，需要分批分段将数据查询出来，这样做可以减少系统资源开销，提高数据库响应速度。
---分页由于在标准SQL中没有定义，所以不同的数据库有不同的语法，即：方言。

---ROWNUM：伪列
---ORACLE提供了伪列：ROWNUM来配合完成分页查询。
---ROWNUM不存在与任何表中，但是所有的表都可以查询该字段。该字段的值在结果集中就是每条记录的行号。
---ROWNUM的值是动态生成的，是伴随查询的过程中生成的。即：每当可以从表中查询出一条记录时，
---ROWNUM会为该条记录生成行号，从1开始逐一递增。
SELECT ROWNUM ename,sal,job,deptno FROM emp_RR;

---查询6到10？？？正常是一条记录都没有。
SELECT ROWNUM ename,sal,job,deptno FROM emp_RR WHERE ROWNUM BETWEEN 6 AND 10;
--->假的SQL?O(∩_∩)O哈哈~why？下午再说PM SEE YOU LA LA


---在使用ROWNUM对结果集编号的过程中不要使用ROWNUM做>1以上数字的判断，否则查询不出任何结果。
SELECT ROWNUM ename,sal,job,deptno FROM emp_RR WHERE ROWNUM>1;
SELECT ROWNUM ename,sal,job,deptno FROM emp_RR WHERE ROWNUM<10;

SELECT * FROM(SELECT ROWNUM,ename,sal,job,deptno FROM emp_RR)WHERE ROWNUM BETWEEN 6 AND 10;
--->然并卵！外层的rownum不是里面的字段
SELECT * FROM(SELECT ROWNUM rn,ename,sal,job,deptno FROM emp_RR)WHERE rn BETWEEN 6 AND 10;

---工资排名6到10的人。
SELECT * FROM(SELECT ROWNUM rn,ename,sal,job,deptno FROM emp_RR)WHERE rn BETWEEN 6 AND 10
ORDER BY sal DESC;
--->ORDER BY是最后干。

SELECT *
FROM(SELECT ROWNUM rn,t. *
FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t)
WHERE rn BETWEEN 6 AND 10;
---先排序，后编号，再取范围。套两个子查询。虽然查出来，但是效率较低。
---排序一定要干的，编号上可以让它效率更高一些，

SELECT *
FROM(SELECT ROWNUM rn,t. *
FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t WHERE ROWNUM <=10)
WHERE rn >=6;
---在里层编号的内容，多余的就可以省去了。提高效率。

--- 二、分页查询(分段查询---淘宝、京东搜一个U盘有成千上万个，不会在同一页...56K猫，一拨号上网，电话就不能打了。多图杀猫。擒拿少取)
---ORACLE的分页和MySQL的分页写法不一样
--- 1.ROWNUM
--- 1.1) 被称作伪列，用于返回标识行数据顺序的数字
SELECT ROWNUM,empno,ename,sal FROM emp_RR;
--- 1.2) 只能从1计数，不能从结果集中直接截取
SELECT ROWNUM,empno,ename,sal FROM emp_RR WHERE rownum>3; ---查询不到结果
--- 1.3）利用ROWNUM截取结果集中的部分数据，需要用到行内视图
SELECT * FROM
(SELECT ROWNUM rn,e.*FROM emp e) ---给ROWNUM一个别名
WHERE rn BETWEEN 8 AND 10;

--- 2.使用子查询进行分页
--- 2.1)分页策略：每次只取一页的数据。每次换页，取下一页的数据。
--- 2.2）在oracle中利用ROWNUM的功能可用来进行分页
--- 2.3）假设结果集共105条，每20条分为一页，则：
--- Page1:1至20
--- Page2:21至40
--- ...
--- PageN:(n-1)*pageSize+1至n*pageSize
--- 共6页

--- 3.分页与ORDER BY
--- 3.1)按薪水倒序排列，取出结果集中第8到第10条的记录
SELECT * FROM (SELECT ROWNUM rn,t.*FROM
(SELECT empno,ename,sal FROM emp_RR ORDER BY sal DESC)t)
WHERE rn BETWEEN 8 AND 10

--- 3.2）根据要查看的页数，计算起点值((n-1)*pageSize+1)和终点值(n*pageSize),
--- 替换掉BETWEEN和AND的参数，即得到当前页的记录

---在将来实际开发中，不可能让用户去写这两个数的。例如：上淘宝只需要写上一页、下一页或则跳到第几页，最多能显示显示1能显示多少条。
---从来不会让你写第几条到第几条。

---pageSize：每页显示的条目数
---page：页数

---pageSize：5 page：2 --->也就是6到10条

---算法：现成的推导
---start：(page-1)*pageSize+1
---end:pageSize*page

---在Java中怎么干？将来是动态的，让Java去执行SQL语句。动态的把SQL语句拼出来。
---伪代码，理解就行。（下周就写）

---******************************************************************************
package oracle;

import java.util.Scanner;

public class Test {

public static void main(String[] args) {
Scanner scanner= new Scanner(System.in);
System.out.println("请输入每页要显示的条目数：");
int pageSize=Integer.parseInt(scanner.nextLine());

System.out.println("请输入要查看的页数");
int page=Integer.parseInt(scanner.nextLine());

int start=(page-1)*pageSize+1;
int end=pageSize * page;

String sql="SELECT *"+
"FROM(SELECT ROWNUM rn,t. *"+
" FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t WHERE ROWNUM <="+end+")"+
" WHERE rn >="+start;
System.out.println(sql);
}

}
//输出后得到如下内容：（将来把这条语句发送到数据库，相当于在数据库里执行。）
//SELECT *
//FROM(SELECT ROWNUM rn,t. *
//FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t WHERE ROWNUM <=10)
//WHERE rn >=6;
---*******************************************************************************************
---到数据库显示：
SELECT *
FROM(SELECT ROWNUM rn,t. *
FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t WHERE ROWNUM <=10)
WHERE rn >=6;

***********************************************************************************************
---当有了框架后，就不是写在Java只中，而是写在配置文件中，配置文件就可以编写SQL语句，让Java去执行。

 


--- 三、DECODE函数
--- 1.DECODE函数基本语法
--- 1.1)DECODE(expr,search1,result1[,serch2,result2...][,default]) ---至少传3个以上参数

--- 1.2)DECODE用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果
--- 1.3）可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值
--- 1.4）default参数是可选的，如果所有提供default参数值，当没有匹配到时，将返回NULL.

---查询职员表，根据职员的职位计算奖励金额，当职位分别是'MANAGER'、'ANALYST'、'SALESMAN'时，
---奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值
SELECT ename,job,sal,
DECODE(job,'MANAGER',sal*1.2,'ANALYST',sal*1.1,'SALESMAN',sal*1.05,sal)bonus FROM emp_RR;

SELECT ename,job,sal,
DECODE(job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05)bonus
FROM emp_RR;
--->不返回正常工资，而返回空值null。


---和DECODE 函数功能相似的有CASE语句，实现类似于if-else的操作。（更像Java中的switch...case...）
SELECT ename,job,sal,
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_RR;

--- 2.DECODE函数中分组查询中的应用
--- 2.1按字段内容分组
--- 场景：计算职位的人数，analyst/manager属于vip，其余是普通员工operation,无法用GROUP BY简单实现
SELECT DECODE(job,'ANALYST','VIP','MANAGER','VIP','OPERATION')job,
COUNT(1)job_cnt FROM emp_RR
GROUP BY DECODE(job,'ANALYST','VIP','MANAGER','VIP','OPERATION');

--- 2.2按字段内容排序
--- 场景：Dept表中按"OPERATIONS"、"ACCOUNTING"、"SALES"排序，无法按照字面数据排序
SELECT deptno,dname,loc FROM dept_RR
ORDER BY
DECODE(dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3);
---自由定义排序规则.数据量少的话，可以排。
---DECODE函数不需要所有的参数，都是同一类型的，比较的是相同类型的，返回的也是相同类型的，即可。

---在分组中使用DECODE 函数可以将字段值不同的记录划分为一组(将来也实用)
---将MANAGER和ANALYST看作一组，其他职位看作另一组，分别统计各组人数

SELECT ename,job FROM emp_RR ORDER BY job;
SELECT COUNT(1),job FROM emp_RR GROUP BY job;
---统计人数的时候，count什么都行。出来的是有几组职位，每个职位出来的计数。

SELECT ename,job,
DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
FROM emp_RR;


SELECT COUNT(1),
DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER') count_name
FROM emp_RR
GROUP BY DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER');

 

--- 四、排序函数(没那么常用)
--- ROW_NUMBER、RANK、DENSE_RANK
---1.ROW_NUMBER
--- 1.1）ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2)
--- 1.2）表示根据col1分组，在分组内部根据col2排序
--- 1.3）此函数计算的值就表示每组内部排序后的顺序编号，组内连续其唯一
--- 1.4）Rownum是伪列，ROW_NUMBER功能更强，可以直接从结果集中取出子集

--- 场景：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码
SELECT deptno,ename,empno,
ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY empno)AS emp_id FROM emp_RR;


---排序函数：将来在开发中解决特殊问题，它能帮你把整个的结果集按照指定的字段分组，然后在组内帮你排序。然后生成一个组内行号（编号）。
---rownum是对整个结果集123456的排序，而它不是。它是每组123456，再一组123456.

---排序函数
---排序函数可以对结果集按照指定的字段分组，然后在组内按照指定的字段排序，然后生成组内的行号。
---ROW_NUMBER:生成组内连续且唯一的数字
---查看每个部门的工资排名：
SELECT ename,sal,deptno,
ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal DESC) rank
FROM emp_RR;
--->就像Justin biber的歌 背呗背呗... ...
---有两人不爽：
---WARD 1250 30 4
---MARTIN 1250 30 5


---RANK：生成组内不连续也不唯一的数字
SELECT ename,sal,deptno,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rank
FROM emp_RR;
---WARD 1250 30 4
---MARTIN 1250 30 4
---JAMES 950 30 6--->变5

---DENSE_RANK:生成组内连续但不唯一的数字
SELECT ename,sal,deptno,
DENSE_RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rank
FROM emp_RR;
---WARD 1250 30 4
---MARTIN 1250 30 4
---JAMES 950 30 5

 

---2.RANK
--- 2.1)PANK()OVER(PARTITION BY col1 ORDER BY col2)
--- 2.2)表示根据col1分组，在分组内部根据col2给予等级标识
--- 2.3）等级标识即排名，相同的数据返回相同排名
--- 2.4）跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名
--- 2.5）和ROW_NUMBER的区别是有重复值，而ROW_NUMBER没有

--- 场景：按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示
SELECT deptno,ename,sal,comm,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC,comm) "Rank_ID" FROM emp_RR;

---3.DENSE_RANK
--- 3.1)DENSE_RANK()OVER(PARTITION BY col1 ORDER BY col2)
--- 3.2)表示根据col1分组，在分组内部根据col2给予等级标识
--- 3.3）即排名，相同的数据返回相同排名
--- 3.4）连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同,RANK是跳跃排序

---场景：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪水
SELECT d.dname,e.ename,e.sal,
DENSE_RANK()OVER(PARTITION BY e.deptno ORDER BY e.sal)
AS drank
FROM emp_RR e join dept_RR d
on e.deptno=d.deptno;


----随机插入一千条数据在里面：（随机数,随机生成。）
---ROLLUP(续3)
CREATE TABLE sales_tab_RR(
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_RR
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),2) AS sales_value
FROM dual
CONNECT BY level<=1000;
--->table SALES_TAB_RR 已创建。
--->1,000 行已插入。

 

 

 

--- 五、集合操作（并、交、差）
--- 1.UNION、UNION ALL
--- 1.1）为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差
--- 1.2）集合操作符包括UNION、UNION ALL、INTERSECT和MINUS
--- 1.3）多条作集合操作的SELECT语句的列的个数和数据类型必须匹配
--- 1.4）ORDER BY子句只能放在最后的一个查询语句中
--- 1.5）集合操作的语法如下：
---SELECT statement1
---[UNION|UNION ALL|INTERSECT|MINUS]
---SELECT statement2;

--- 1.6）用来获取两个或两个以上结果集的并集
--- 1.7）UNION操作符会自动去掉合并后的重复记录
--- 1.8）UNION ALL返回两个结果集中的所有行，包括重复的行。
--- 1.9）UNION操作符对查询结果排序，UNION ALL不排序

--- 2.0）合并职位是'MANAGER'的员工和薪水大于2500的员工集合，查看两种方式的结果差别
SELECT ename,job,sal FROM emp_RR
WHERE job='MANAGER'
UNION
SELECT ename,job,sal FROM emp_RR
WHERE sal>2500;
--->6条记录

SELECT ename,job,sal FROM emp_RR
WHERE job='MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp_RR
WHERE sal>2500;
--->8条记录

--- 2.INTERSECT
--- 2.1)获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出
--- 2.2）使用INTERSECT操作符后的结果集会以第一列的数据作升序排列
--- 2.3）显示职位是'MANAGER'的员工薪水大于2500的员工的交集
SELECT ename,job,sal FROM emp_RR
WHERE job='MANAGER'
INTERSECT
SELECT ename,job, sal FROM emp_RR
WHERE sal>2500;

--- 3.MINUS
--- 3.1)获取两个结果集的差集
--- 3.2）只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集的结果
--- 3.3）列出职位是MANAGER但薪水低于2500的员工记录
SELECT ename,job,sal FROM emp_RR
WHERE job='MANAGER'
MINUS
SELECT ename,job,sal FROM emp_RR
WHERE sal>=2500;

SELECT * FROM emp_RR;

SELECT * FROM dept_RR;

 

--- 六、高级分组函数
--- ROLLUP、CUBE、GROUPING SETS
--- 1.ROLLUP
--- 1.1)ROULLUP、CUBE和GROUPING SETS运算符是GROUP BY子句的扩展，可以生成与使用UNION ALL来组合单个分组查询时
--- 相同的结果集，用来简化和高效的实现统计查询
--- GROUP BY ROLLUP(a,b,c)
--- GROUP BY CUBE(a,b,c)
--- GROUP BY GROUPING SETS((a),(b))

--- 1.2)假设有表test,有a、b、c、d四个列。
---SELECT a,b,c,SUM(d)FROM test
---GROUP BY ROLLUP(a,b,c);
---等价于：
---SELECT a,b,c,SUM(d)FROM test GROUP BY a,b,c
--- UNION ALL
---SELECT a,b,null,SUM(d)FROM test GROUP BY a,b
--- UNION ALL
---SELECT a,null,null,SUM(d)FROM test GROUP BY a
--- UNION ALL
---SELECT null,null,null,SUM(d)FROM test
--- 1.3)对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组（也就是全表分组）
--- 1.4）对于n个参数的ROLLUP,有n+1次分组
--- 1.5）准备数据：2010-2011年每月每天的销售额

 

 


----随机插入一千条数据在里面：（随机数,随机生成。）
---ROLLUP(续3)
CREATE TABLE sales_tab_RR(
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_RR
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),2) AS sales_value
FROM dual
CONNECT BY level<=1000;
--->table SALES_TAB_RR 已创建。
--->1,000 行已插入。


SELECT year_id,month_id,day_id,sales_value
FROM sales_tab_RR;
---一个店铺、商店每天营业的流水情况。
SELECT year_id,month_id,day_id,sales_value
FROM sales_tab_RR
ORDER BY year_id,month_id,day_id;
---给它排序。

---查看每天营业额？
SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id;

---查看每月营业额？（把天去了，就是年、月相同的记录看成一组，对其求和）
SELECT year_id,month_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id,month_id
ORDER BY year_id,month_id;

---查看每年营业额？
SELECT year_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id;

---总共的营业额？
SELECT SUM(sales_value)
FROM sales_tab_RR;

--- 在一个结果集里这几种情况都出现：（每、年、月、日、总共都看）
SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id,month_id,day_id
UNION ALL
SELECT year_id,month_id,null,SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id,month_id
UNION ALL
SELECT year_id,null,null, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id
UNION ALL
SELECT null,null,null,SUM(sales_value)
FROM sales_tab_RR;
--->太长了，恶心！

---ROLLUP()函数
---GROUP BY ROLLUP(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_RR
GROUP BY ROLLUP(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id;

SELECT year_id,month_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY ROLLUP(year_id,month_id)
ORDER BY year_id,month_id;
--->参数每次都是逐一递减，先按月，再按年，最后年月。


---CUBE()函数
---CUBE()函数会将所有参数的每种组合多作为一次分组方式进行统计然后将结果并在一起，
---所以分组次数为2的参数个数次方。

---CUBE立方体，所有维度都来一遍，所有组合都做一遍。
---GROUP BY CUBE(a,b,c)
---abc
---ab
---ac
---bc
---a
---b
---c
---全表
---加起来总共8种。

---四的立方就是16种。
SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id;

SELECT year_id,month_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY CUBE(year_id,month_id)
ORDER BY year_id,month_id;

---rowup还是cube都内定了分组的方式了。
---rowup字段逐一递减,cube所有组合都来一遍。
---rowup片面格式固定，cube太全面了。
---现在有一需求，想看每天和每月的，没意义的组合不想看。在一个结果集里。

---GROUPING SETS（比前两个要频繁，但这三个都不太用）
---允许自己去指定分组方式，然后帮你统计分组，再并到一个结果集里。

---GROUPING SETS()
---每一个参数是一种分组方式，然后将这些分组统计的结果并在一个结果集中显示。
---查看每天与每月营业额？
---SELECT year_id,month_id,day_id, SUM(sales_value)
---FROM sales_tab_RR
---GROUP BY GROUPING SETS(year_id,month_id,day_id)
---ORDER BY year_id,month_id,day_id;


---GROUPING SETS(year_id,month_id,day_id)
---等同于
---GROUP BY year_id
---UNION ALL
---GROUP BY month_id

SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id))
ORDER BY year_id,month_id,day_id;
--->放在一个括号里,表一个参数。

转载于:https://www.cnblogs.com/sneg/p/7813308.html

weixin_34258078
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle SQL Developer unit test
07-22
简明扼要介绍了如何使用Sql developer去组织Oracle plsql的单元测试
oracle-04063,ora-12012 ora-04063 报错处理
weixin_36087577的博客
04-07 413
数字校园数据库告警日志：Wed Apr 2 16:09:59 2014Errors in file /data/oracle/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_j000_29421.trc:ORA-12012: error on auto execute of job 42567ORA-04063: ORA-04063: packa...
ora-06552：pl/sql:Compilation unit analysis terminated ora-06553:pls-553:character set name is not re
a18210148948的博客
06-13 3158
ORA-06552: PL/SQL: Compilation unit analysis terminated ORA-06553: PLS-553: character set name is not recognized首先，确认字符集是否修改的不彻底。SELECT DISTINCT (NLS_CHARSET_NAME(CHARSETID)) CHARACTERSET,DECODE(TYPE#...
oracle 找不到程序单元,WebAdi使用时的ORA-06508:PL/SQL:无法找到正在调用 的程序单元 错误...
weixin_36415206的博客
04-10 1409
Applies to:Oracle Web Applications Desktop Integrator – Version: 11.5.10.2This problem can occur on any platform.SymptomsIn custom Web ADI integrator, when code changes are made to the API, then loadi...
转：oracle-ora 各种sql异常描述
CBoom的专栏
08-21 4241
java.sql.SQLException错误！ ORA-00904:     --invalid column name 无效列名 ORA-00942: 　--table or view does not exist 表或者视图不存在 ORA-01400: 　--cannot insert NULL into () 不能将空值插入 ORA-00936:　--缺少表达式
Oracle根据时间查询
热门推荐
Lifelong learning
01-26 2万+
1. 查询时间段之内的数据 查询2021-01-01 至 2021-01- 02 的数据 SELECT * FROM t_table1 t WHERE t.d_time >= to_date('2021-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND t.d_time <= to_date('2021-01-02 23:59:59', 'yyyy-mm-dd hh24:mi:ss'); 以下SQL，只会查询2021-01-01和2021-1-2
plsql-unit-test:猴子补丁测试
05-21
大多数测试框架是由Test :: Unit提供的，而这个gem仅添加了一些方便的方法，这些方法在单元测试PLSQL代码时非常有用。 依存关系 此宝石取决于： 为Oracle提供JDBC接口 提供一种生成测试数据的机制 Oracle ojdbc6....
Apps Unit-开源
04-17
Apps Unit工具箱是一个库，可帮助测试您的Oracle:registered:电子商务套件代码。 该工具包可帮助您在Multi-Org电子商务设置中测试视图，pl / sql程序包，并发请求等。 无需在“应用程序”服务器上安装任何软件。
utPLSQL:PLSQL测试框架
04-13
介绍utPLSQL是用于Oracle PL / SQL和SQL的单元测试框架。 该框架遵循行业标准和现代单元测试框架（如和最佳模式主要特征与比较数据的多种方法复杂类型（对象/集合/光标）的本地比较深入一致地报告测试失败和错误通过...
香蕉：D Dapper的CRUD助手集合
02-05
开发商：Lio.Huang 包NuGet稳定NuGet预发行资料下载香蕉哇项目介绍 :backhand_index_pointing_right: 基于Dapper封装的仓储，工作单元，支持SQL Server，MySQL，Sqlite，Postgresql，Oracle ...一，使用说明1.注册...
android高斯模糊填充imageview背景
cf8833的博客
05-21 390
说明：最近碰到一个需求，安卓app显示在线的url图片，然后imageview没占满的部分，使用该图片的模糊背景填充。
docker中安装jenkins，并在node和cloud上跑通基于源码控制SCM的pipeline
qq_29022265的博客
05-19 1221
从安装jenkins到创建jenkins的node和cloud，最后再将scm和FreeStyle两种流水线跑通。
java学习和项目总结
2301_79390585的博客
05-24 284
JRE：JRE是Java 运行时环境，主要包括了两个运行需要的组件：JVM 和 Java 核心类库，如果不进行java开发只想运行就可以只下载JRE。而Java语言，它的代码会先通过javac编译成字节码，再通过jvm将字节码转换成机器码（0和1）执行，即解释运行和编译运行配合使用，所以是混合型。在程序运行之前，通过编译器将源程序编译成机器码可运行的二进制，以后执行这个程序时，就不用再进行编译了。JVM：JVM是java进行编译的虚拟机，是Java 能够跨平台运行的核心。特点：执行速度慢、效率低；
分布式锁2-Zookeeper分布式锁实战
qq_43676797的博客
05-19 453
使用curator操作Zookeeper进行实战；：Apache Curator包含一套高级API框架和工具类，它 是Apache ZooKeeper 的Java 客户端库。
SpringBoot Validation自定义注解之校验指定最小整数
thinkers
05-21 124
1，引入核心关键依赖。
【java-数据结构19-队列的模拟实现】
2302_80519942的博客
05-21 711
（这个才是重点）同上，没有元素直接返回即可，只有一个元素（全部置空），普通情况（rear等于前驱，rear置为空），
深入理解Java的垃圾回收机制（GC）实现原理
最新发布
微笑听雨
05-25 725
GC是Java虚拟机的重要组成部分，通过自动内存管理，GC提高了Java程序的稳定性和安全性。理解GC的工作原理和不同收集器的特点，有助于选择合适的GC策略，优化应用性能。通过合理配置JVM参数，可以提高GC效率，减少应用停顿时间，提升系统的整体性能。
微信小程序uniapp+django洗脚按摩足浴城消费系统springboot
QQ1304979694的博客
05-24 394
Python语言官方规范访问数据库的统一接口规范(Python DB-API)，防止在使用不同数据库时，由于底层数据库技术不同造成接口程序紊乱的问题。（3）项目初期开发，美工设计，前端实现，开发信息系统的用户功能的实现，完善界面排版，实现信息提取发送等一系列功能。项目系统最后阶段的修改及最终测试运行。当然，在研究过程中也可以直接调查，对调查搜集到的资料进行综合分析、比较，调查法是科学研究中普遍的研究方法。（5）系统测试：对设计好的程序进行最终调试，通过调试发现存在的问题并解决，从而达到完善系统的目的。
Spring6基础笔记
质胜文则野，文胜质则史。文质彬彬，然后君子。
05-21 835
Spring 是一款主流的 Java EE 轻量级开源框架 ，Spring 由“Spring 之父”Rod Johnson 提出并创立，其目的是用于简化 Java 企业级应用的开发难度和开发周期。Spring的用途不仅限于服务器端的开发。从简单性、可测试性和松耦合的角度而言，任何Java应用都可以从Spring中受益。Spring 框架除了自己提供功能外，还提供整合其他技术和框架的能力。Spring 自诞生以来备受青睐，一直被广大开发人员作为 Java 企业级应用程序开发的首选。时至今日，Spring 俨然
定时任务出错，ORA-06508 PL/SQL: could not find program unit being called
06-09
这个错误通常出现在一个程序单元（package, procedure, function等）被调用但是该程序单元不存在或者没有被正确...如果以上方法都无法解决问题，你可以尝试在Oracle的官方论坛或者社区中提问，获得更专业的帮助和建议。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值