ORACLE数据库总结04

最新推荐文章于 2024-09-14 22:32:55 发布
最新推荐文章于 2024-09-14 22:32:55 发布
阅读量181 收藏
点赞数
分类专栏： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011464124/article/details/54618567
版权

子查询(有函数要用别名)

子查询是嵌套在其他SQL语句之中的，为外层SQL语句提供数据。

 

查看谁的工资比CLARK高？

SELECT ename,sal

FROM emp_brown

WHERE sal>(SELECT sal FROM emp_brown WHERE ename='CLARK')

 

查看与JONES相同部门的员工

SELECT ename,deptno

FROM emp_brown

WHERE deptno=(SELECT　deptno FROM emp_brown WHERE ename='JONES')

 

 

 

 

DDL中使用子查询

可以给予一个查询结果集快速创建一张表

 

创建employee，表中的数据来源于emp表

要求的字段:

          empno,ename,sal,deptno,dname,loc

          

CREATE TABLE emp_b

AS

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc

FROM emp_brown e JOIN dept_brown d

ON e.deptno=d.deptno(+)

 

或者

 

CREATE TABLE emp_b

AS

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc

FROM emp_brown e ,dept_brown d

WHERE e.deptno=d.deptno(+)

 

select *from emp_b

 

当使用子查询创建表时，若SELECT中的字段含有函数或者表达式

必须指定别名

 

 

 

DML中使用子查询

将JONES部门所有人工资提高10%

UPDATE emp_b

SET sal=sal*1.1

WHERE deptno=(SELECT deptno

              FROM emp_b

              WHERE ename='JONES')

              

 

查找薪水比整个机构平均薪水高的员工：

SELECT deptno, ename, sal

FROM emp_brown e

WHERE sal>(SELECT AVG(sal) FROM emp_brown )

 

select * from emp_brown

select * from dept_brown

 

多行单列子查询要配合IN，ANY,ALL进行过滤(不能使用'=')

 

查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：

SELECT ename,job,deptno

FROM emp_brown

WHERE deptno IN (SELECT deptno FROM emp_brown WHERE job='SALESMAN')

AND job<>'SALESMAN'

 

查看比职位是CLERK与SALESMAN工资都高的员工--职位(WHERE子查询) 工资(WHERE)

SELECT ename,sal

FROM emp_brown

WHERE sal>ALL(SELECT sal FROM emp_brown WHERE job IN ('CLERK','SALESMAN'))

 

 

EXISTS关键字(前面可以加NOT)

EXISTS后面跟一个子查询，当该子查询可以查询出至少一条记录时返回真

 

哪些有员工的部门信息

SELECT deptno,dname 

FROM dept_brown d

WHERE EXISTS(SELECT * FROM emp_brown e

             WHERE d.deptno=e.deptno)

 

查看有下属的员工(先看领导再看下属)

SELECT m.ename,m.mgr,m.empno

FROM emp_brown m

WHERE EXISTS(SELECT * FROM emp_brown e

             WHERE e.mgr=m.empno)

 

查看公司部门的最低薪水,前提是(HAVING)该薪水要高于30号部门的最低薪水

 

SELECT MIN(sal)

FROM emp

WHERE deptno=30

 

SELECT MIN(sal),deptno

FROM emp

WHERE GROUP BY deptno

 

SELECT MIN(sal),deptno

FROM emp_brown

GROUP BY deptno

HAVING MIN(sal)>(SELECT MIN(sal)

                FROM emp_brown

                WHERE deptno=30)

                

 

 

 

FROM当中使用子查询，就是将子查询的结果集

当成一张表看待。

 

查看哪些员工比自己所在部门平均工资高？

1查看每个部门的平均工资

SELECT AVG(sal) avg_sal,deptno

FROM emp

GROUP BY deptno

 

假设有T表

SELECT e.ename,e.sal,e.deptno

FROM emp e,T t

WHERE e.deptno=t.deptno

AND e.sal>avg_sal

即：

SELECT e.ename,e.sal,e.deptno

FROM emp_brown e,(SELECT AVG(sal) avg_sal,deptno

                  FROM emp

                  GROUP BY deptno) t

WHERE e.deptno=t.deptno

AND e.sal>avg_sal

 

 

子查询在SELECT子句中的使用(类似左右外连接)

SELECT e.ename,e.sal,(SELECT d.dname 

                      FROM dept_brown d

                      WHERE d.deptno = e.deptno) dname

FROM emp_brown e                     

 

 

 

 

分页查询

 

当数据库中查询的数据量过大时，会使用分页查询，一次只查询出部分数据，

这样做可以降低系统资源消耗，提高数据库响应速度，减少网络传输等

 

分页查询标准SQL中没有为其定义语法，所以不用的数据库中分页语句写法不同

 

 

 

ROWNUM:伪列

ROWNUM不存在于任何表，但是任何表都可以查询该字段，

该字段的值随着查询结果集的产生而动态生成值。

当查询语句从表中查询出一条记录时，该记录的ROWNUM字段值从1开始，

每当查询出一条记录ROWNUM自增1

 

SELECT ROWNUM,ename,sal,deptno

FROM emp_brown

 

SELECT ROWNUM,ename,sal,deptno

FROM emp_brown

WHERE ROWNUM>6

在使用ROWNUM为结果集编号的查询过程中

不应使用ROWNUM做>1以上数字的判断为过滤条件，否则得不到任何结果。

 

查看公司工资排名的6-10名:

(ORDER BY 执行顺序靠后)

SELECT *

FROM(SELECT ROWNUM rn,ename,sal,deptno

     FROM emp_brown)

WHERE rn BETWEEN 6 AND 10

 

 

select * from emp_brown

(1.先排序)

SELECT ename,sal,deptno

FROM emp_brown

ORDER BY sal desc

(2.编号 3.取范围)

SELECT * 

FROM(SELECT ROWNUM rn,t.* 

     FROM(SELECT * FROM emp_brown 

         ORDER BY sal DESC)t)

WHERE rn BETWEEN 6 AND 10 

 

优化执行速度(2.编号步骤时，10以后的就不用编号了)

SELECT * 

FROM(SELECT ROWNUM rn,t.* 

     FROM(SELECT * FROM emp_brown 

         ORDER BY sal DESC)t

         WHERE ROWNUM <=10)

WHERE rn >= 6

 

 

 

使用子查询进行分页

 

每页显示的条目数:pageSize

页数:page

例如:

pageSize:5

page:2

start:(page-1)*pageSize+1

end=pageSize*page

 

 

 

DECODE函数

DECODE(expr, search1, result1[, search2, result2…][, default])

当第一个参数等于第二个参数值，函数返回第三个参数值，否则判断是否等于第四个参数值，

若相等则返回第五个参数值，以此类推。最后可以单独制定一个参数，当前面的所有判断都不成立时

返回最后单独的参数，若不制定，函数返回NULL。函数要求至少传入三个参数。

SELECT ename,job,sal,

      DECODE(job,

            'MANAGER',sal*1.2,

            'ANALYST',sal*1.1,

            'SALESMAN',sal*1.05,

            sal) bonus

FROM emp_brown

 

和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。

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_brown;

 

DECODE在GROUP BY中的应用

将MANAGER与ANALYST看做一组，其他职位看做另一组分别统计人数？

SELECT

    COUNT(*),DECODE(job,

                    'MANAGER','VIP',

                    'ANALYST','VIP',

                    'OTHER') job

FROM emp_brown

GROUP BY DECODE(job,

                    'MANAGER','VIP',

                    'ANALYST','VIP',

                    'OTHER');

 

 

DECODE函数也可以按字段内容排序，例如：Dept表中按“OPERATIONS”、“ACCOUNTING”、“SALES”排序

SELECT deptno, dname, loc

FROM dept_brown

ORDER BY 

      DECODE(dname, 'OPERATIONS',1,'ACCOUNTING',2,'SALES',3), loc;

      

 

 

      

排序函数

排序函数可以将结果集按照制定的字段分组

在按照制定的字段对组内记录排序，最终生成组内编号。

 

 

ROW_NUMBER

组内部排序后的顺序编号，组内连续且唯一。

表示根据col1分组，在分组内部根据col2排序。

ROW_NUMBER() OVER(

          PARTITION BY col1 ORDER BY col2)

 

查看每个部门的工资排名      

SELECT ename,sal,deptno,

ROW_NUMBER() OVER(

                  PARTITION BY deptno

                  ORDER BY sal DESC) rank

FROM emp_brown;

 

RANK

组内部排序后的顺序编号，组内不连续且不唯一。

SELECT ename,sal,deptno,

RANK() OVER(

            PARTITION BY deptno

            ORDER BY sal DESC) rank

FROM emp_brown;

 

 

 

 

DENSE_RANK

生成组内连续但不唯一的数字

SELECT ename,sal,deptno,

DENSE_RANK() OVER(

                  PARTITION BY deptno

                  ORDER BY sal DESC) rank

FROM emp_brown; 

 

 

 

集合操作

UNION,UNION ALL

并集(会去掉合并后重复的记录)

SELECT ename,job,sal From emp

WHERE job = 'MANAGER'

UNION

SELECT ename,job,sal From emp

WHERE sal>2500;

并集(不会去掉合并后重复的记录)

SELECT ename,job,sal From emp

WHERE job = 'MANAGER'

UNION ALL

SELECT ename,job,sal From emp

WHERE sal>2500;

 

交集(同时存在于两个结果集中才会显示出)

SELECT ename,job,sal From emp

WHERE job = 'MANAGER'

INTERSECT

SELECT ename,job,sal From emp

WHERE sal>2500;

 

差集(在第一个结果集中存在，第二个结果集中不存在的。相当于2在1中的补集)

SELECT ename,job,sal From emp

WHERE job = 'MANAGER'

MINUS

SELECT ename,job,sal From emp

WHERE sal>2500;

 

 

 

高级分组函数

 

CREATE TABLE sales_brown (

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_brown

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;

 

desc sales_brown;

select * from sales_brown;

 

营业额按年月日分组排序

SELECT year_id,month_id,day_id,sales_value

FROM sales_brown

ORDER BY year_id,month_id,day_id;

 

每天的营业额？

SELECT year_id,month_id,day_id,SUM(sales_value)

FROM sales_brown

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_brown

GROUP BY year_id,month_id

ORDER BY year_id,month_id;

 

每年的营业额？

SELECT year_id,SUM(sales_value)

FROM sales_brown

GROUP BY year_id

ORDER BY year_id;

 

总营业额？

SELECT SUM(sales_value)

FROM sales_brown

 

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_brown

GROUP BY ROLLUP(year_id,month_id,day_id)

ORDER BY year_id,month_id,day_id;

 

CUBE函数

CUBE会将每个参数的组合都进行一次分组

分组次数为2的参数个数次方

GROUP BY CUBE(a,b,c)

相当于:

a,b,c

a,b

a,c

b,c

a

b

c

全表

 

SELECT year_id,month_id,day_id,SUM(sales_value)

FROM sales_brown

GROUP BY CUBE(year_id,month_id,day_id)

ORDER BY year_id,month_id,day_id;

 

 

 

 

GROUPING SETS()函数

GROUPING SET的每一个参数是一个分组方式

该函数会将这些分组统计的结果并在一个结果集显示

 

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

SELECT year_id,month_id,day_id,SUM(sales_value)

FROM sales_brown

GROUP BY GROUPING SETS(

                      (year_id,month_id,day_id),

                      (year_id,month_id)

)

ORDER BY year_id,month_id,day_id;

 

LuckyBrown17
关注
专栏目录
五月:Oracle数据库总结（一）
12-14
【Oracle数据库概述】 Oracle数据库是全球广泛使用的商业关系型数据库管理系统之一，由甲骨文公司开发和维护。它提供了一种高效、安全且可靠的方式来存储和管理大量的结构化数据。Oracle数据库不仅支持标准的SQL...
Oracle数据库总结
03-17
Oracle 数据库总结 Oracle 数据库是甲骨文公司的一款关系数据库管理系统，是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。下面是 Oracle 数据库的总结...
关系型数据库基础及应用（四）——SQL查询（以Oracle为主）
huang_yx的博客
05-08 341
SQL（高级查询）子查询子查询在where中的应用· 在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定值，而是来自于另一个查询的结果；· 为了给查询提供数据而首先执行的查询语句叫做子查询；· 子查询是嵌入在其他SQL语句中的SELECT语句，大部分时候出现在WHERE子句中；· 子查询嵌入的语句称为主查询或者父查询；· 主查询可以是SELECT语句，也可以是其他类型的语句，比如：...
oracle数据库状态总结
zhanglinna1136的博客
10-15 1526
Oracle数据库有四种状态：shutdown（完全关闭），nomount（未加载），mount（已加载），open（完全打开） ◎Shutdown：完全关闭状态，关闭命令是shutdown immediate ◎Nomount：在这个状态下，oracle会打开参数文件，分配SGA并打开后台进程 这种状态下，可以查询后台进程和实例信息 Select * from vbgporcess；Select∗fromvbgporcess； Select * from vbgporcess；Select∗fromvi
Oracle数据库语句总结
niubaosama的博客
02-25 1万+
Oracle数据库基础语句总结
Oracle数据库
laizhenghua的博客
03-09 1万+
Oracle Database，又名Oracle RDBMS，简称Oracle。直至2022年仍是当今使用最广泛最流行的关系型数据库，由甲骨文公司开发、运营与维护。在数据库领域中一直处于领先地位，系统可移植性好、使用方便、功能与性能强大、效率高，适用于大、中、小软件开发。放眼当下因高效率（适应高吞吐量）、可靠性好仍是开发商业软件最好的选择（就是有点贵）。
Oracle数据库技术深度学习与实践
聚焦于深度解析最新的编程语言特性、框架升级、架构设计、开发工具和最佳实践，涵盖Web前端（如Vue3, React, Angular等）、后端开发（如Node.js, Java, Python等）、移动端开发（iOS, Android原生及跨平台开发）
03-06 3万+
Oracle数据库技术作为行业内的领先技术，具有广泛的应用前景。通过本文的学习和实践，相信读者已经对Oracle数据库有了更深入的了解和掌握。希望读者能够继续深入学习Oracle数据库，不断提升自己的技能水平。
MySQL数据库总结
热门推荐
匿名攻城狮
11-25 12万+
一、数据库简介 数据库(Database，DB)是按照数据结构来组织，存储和管理数据的仓库。 典型特征：数据的结构化、数据间的共享、减少数据的冗余度，数据的独立性。 关系型数据库：使用关系模型把数据组织到数据表(table)中。现实世界可以用数据来描述。 主流的关系型数据库产品：Oracle(Oracle)、DB2(IBM)、SQL Server(MS)、MySQL(Oracle)。 数据表：数...
ORACLE数据库并发
但行好事，莫问前程
02-05 1801
Oracle数据库并发、事务、锁
oracle数据库安装和配置详细讲解
yang_xs的博客（只讲干货）
09-14 1214
Oracle 数据库是全球广泛使用的关系型数据库管理系统 (RDBMS)，提供高性能、可靠性、安全性和可扩展性，广泛应用于企业关键任务系统。下面详细介绍如何在 CentOS 系统上安装和配置 Oracle 数据库。在 CentOS 上安装和配置 Oracle 数据库涉及多个步骤，包括系统准备、依赖包安装、数据库安装与配置，以及网络监听与数据库创建。掌握这些步骤后，你可以轻松地在 CentOS 系统上部署 Oracle 数据库，并进行日常管理。
Oracle数据库对象
m0_63223374的博客
05-02 1735
同义词 序列 视图 索引
Oracle数据库知识点总结 Oracle数据库架构与组件学习攻略
10-13
Oracle数据库知识点总结 章节目录 一、Oracle数据库概述 二、Oracle数据库架构与组件 三、SQL语言基础 四、PL/SQL编程 五、数据库对象管理 六、数据备份与恢复 七、性能优化与安全管理 八、如何学习Oracle数据库 九...
Oracle数据库的启停
09-21
Oracle 数据库的启停 Oracle 数据库的启停是数据库管理员的重要任务之一。启停数据库的过程可以细分为三个步骤：启动实例、挂载数据库、打开数据库。下面将详细讲解 Oracle 数据库的启停过程。 启动数据库 启动...
Oracle数据库备份与恢复.pdf
03-29
### Oracle数据库备份与恢复 #### 一、Oracle数据库概述 Oracle数据库是一款由甲骨文公司开发的关系型数据库管理系统，以其卓越的性能、可扩展性和安全性在全球范围内被广泛应用。Oracle数据库的特点包括： - **...
基于FPGA的智能车牌检测系统设计与实现
11-08
内容概要：本文介绍了一种基于FPGA的智能车牌检测系统。该系统采用了OV5640摄像头进行图像采集，通过FPGA集成化开发环境进行图像处理，主要包括图像格式转换、图像灰度化、图像增强、边缘检测、腐蚀膨胀、投影定位等技术步骤。该系统能够在复杂环境中快速实现车牌的图像采集及定位，提高了车牌检测的效率和准确性。 适合人群：具备一定嵌入式系统和图像处理基础的研究人员和技术人员。 使用场景及目标：适用于智慧交通管理系统，尤其是停车场、高速公路、智能制造等领域，主要用于实现实时的车牌检测与识别。 其他说明：系统采用Sobel算子进行边缘检测，中值滤波进行图像增强，投影定位确定车牌位置，整体处理效率较高，适用于复杂光照条件下的车牌检测。
【java毕业设计】springbootJava学生选课系统(springboot+vue+mysql+说明文档).zip
11-08
项目经过测试均可完美运行！ 环境说明： 开发语言：java 框架：ssm jdk版本：jdk1.8 数据库：mysql 5.7+ 数据库工具：Navicat11+ 管理工具：maven 开发工具：idea/eclipse 部署容器：tomcat7+
JDK-API-1-6-zh-CN
最新发布
11-08
JDK API 1.6 中文版，附使用说明
【java毕业设计】springbootJava校车管理信息系统(springboot+vue+mysql+说明文档).zip
11-08
项目经过测试均可完美运行！ 环境说明： 开发语言：java 框架：ssm jdk版本：jdk1.8 数据库：mysql 5.7+ 数据库工具：Navicat11+ 管理工具：maven 开发工具：idea/eclipse 部署容器：tomcat7+
Oracle数据库基础知识总结
Oracle数据库知识笔记汇总1 Oracle数据库是关系数据库管理系统（RDBMS），它由Oracle Corporation开发，广泛应用于企业级数据库管理。以下是Oracle数据库知识笔记汇总1的详细知识点： 一、Oracle数据库安装 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值