Oracle总结【SQL细节、多表查询、分组查询、分页】

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 前言在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了...印象中就只有基本的SQL语句和相关一些概念....写下本博文的原因就是记载着Oracle一些以前没注意到的知识点.

前言

在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了...印象中就只有基本的SQL语句和相关一些概念....写下本博文的原因就是记载着Oracle一些以前没注意到的知识点...以后或许会有用...

实例与数据库概念

Oracle数据库服务器由两部分组成:

  • 实例:理解为对象,看不见的
  • 数据库:理解为类,看得见的

我们在安装Oracle的时候,已经填写过自己数据库的名称了,一般实例与数据库的名称是一致的...

这里写图片描述

如果还没有安装Oracle数据库的,可以看一下我其他的博文....

那么我们自带的sqlplus黑色窗口与实例,数据库之间的关系又是什么的呢???我们可以看下图:

这里写图片描述

Oracle数据库把表、视图等都看成是对象:

这里写图片描述


Oracle中的null值

Oracle中如果存在字段是null值的话,那么在sqlplus中它是不会显示出来的....如果我们使用null值的数据与其他数据进行运算...那么最终得出的结果都是null值

因此,Oracle提供了NVL(表达式1,表达式2)函数供我们使用,如果表达式1的值为null值,那么就取表达式2的值...当然了,如果表达式1不是null,取的就是表达式1的值

还有值得注意的是:null值不能参数=号运算,null能参数number/date/varchar2类型运算

Oracle提供了 is null关键字来代替=号运算的问题

Oracle中的别名

我们知道在Mysql中如果要用别名的话,需要使用as关键字 ,后面跟着别名就行了....Oracle可以省略as关键字...

并且,一般地,我们使用别名都是用双引号""把别名括起来,Oracle也支持我们直接写别名,但是呢,如果我们不写双引号,那么我们的别名是不能有空格的

还有一点的是:Oracle的别名是不能使用单引号来括起来的,Oracle默认认为单引号是字符串类型和日期类型的。

IO输入输出SQL语句

我们可以在sqlplus中使用spool命令把SQL语句保存在硬盘中,具体的例子:


    spool e:/oracle-day01.sql;

使用spool off命令,保存SQL语句到硬盘文件e:/oracle-day01.sql,并创建sql文件,结束语句

spool off;

当然了,我们也可以把硬盘中的SQL文件在sqlplus中执行,只要以下的命令就行了:


    @ e:/crm.sql; 

转义字符

有的时候,我们可能会模糊查询一些数据,但是呢,在名称中又有一些特殊的字符。那么我们就要经过转义....当然了,如果按照Java的来,就十分简单了,就写一个""就可以了。

那在Oracle中是怎么样转义的呢??我们来看下面的例子:


查询员工姓名中含有'_'的员工,使用\转义符,让其后的字符回归本来意思【like '%\_%' escape '\'】

select * from emp where ename like '%\_%' escape '\';

如果名称是'单引号呢???那么两个单引号代表着一个引号


插入一个姓名叫''的员工
insert into emp(empno,ename) values(2222,'''''');

单行函数与多行函数

首先,我们要明确一个概念:

  • 单行函数:输入一个参数,返回一个结果
  • 多行函数:扫描多个参数,返回一个结果....一般地,多行函数和分组函数的概念是差不多的...

Oracle提供了关于字符串函数、日期函数供我们对数据进行对应的操作,这里就不一一赘述了,我们到时候有需要的时候查文档就行了。

这里写图片描述

单引号出现的地方如下:

  • 1)字符串,例如:'hello'
  • 2)日期型,例如:'17-12月-80'
  • 3)to_char/to_date(日期,'YYYY-MM-DD HH24:MI:SS')

双引号出现的地方如下:

  • 1)列别名,例如:select ename "姓 名" from emp
  • 2)to_char/to_date(日期,'YYYY"年"MM"月"DD"日" HH24:MI:SS')

GROUP BY 细节

group by 子句的细节:

  • 1)在select子句中出现的非多行函数的所有列,【必须】出现在group by子句中
  • 2)在group by子句中出现的所有列,【可出现可不现】在select子句中

这里写图片描述

举例子:下面这段代码是错误的!!!


    select max(avg(sal)) "部门平均工资的最大值",deptno "部门编号"
    from emp
    group by deptno;

为啥是错误的呢???分组中我们已经有了deptno字段了,而我们select 后面跟着也就是多行函数和该字段而已,为啥就错了呢?????我们如果在分组查询的时候,使用了多行函数嵌套的话,那么我们select字段后面只能跟随着它这么一个列,而不能再多了。max(avg(sal)) 相当于又分组了一次

当然了,如果我们仅仅是求出每个部门的平均工资,也就是下面这段代码,是完全没有问题的:

    select avg(sal) "部门平均工资的最大值",deptno "部门编号"
    from emp
    group by deptno;

这里写图片描述


多表查询、子查询

当我们一张表不能把数据查询出来的时候,就需要连接其他的表一起查询....

当我们的查询条件还没知道的时候,我们就可以使用子查询....

一般地,子查询和多表查询的功能都是差不多的....

子查询出来的数据是单行单列的时候,一般我们都是用等于、大于等于、小于等操作符去限制查询条件...

如果是单列多行的时候,我们一般都是用IN、ANY、ALL操作符去筛选条件...

如果是多行多列,我们就看成该返回查询结果是一张表【Oracle分页就是这个原理】


值得注意的是多表查询的数学基础是笛卡尔积,也就是说:如果两张实体表进行连接,那么它会构成一张笛卡尔积表...也就是说:最终就只有一张笛卡尔积表

这里写图片描述

连接

在多表查询的时候,我们由于会产生笛卡尔积,于是在笛卡尔积表中会存在很多无关的数据...为了剔除这些数据,我们将用到where字句将笛卡尔积表筛选成有用的数据表

一般地,我们有几种连接:

  • 内连接
    • 等值连接【使用=号把条件筛选出来】
    • 非等值连接【使用between and等手段把条件筛选】
  • 外连接
  • 自连接

这里写图片描述

这里写图片描述

那现在问题来了,在Oracle中有的功能我们可以使用多表查询来完成,有的时候我们又可以使用子查询来完成,那么我们一般选择哪一个呢????

我们看下图来比较一下他们的优劣:

这里写图片描述

对于索引就是一个以空间换时间的概念..在数据量很大的时候,Oracle会为我们的数据创建索引,当扫描数据的时候,就可以根据索引来直接获取值....索引的算法也有几种【二叉树、稀疏索引、位图索引....等等】

这里写图片描述

综上所述:在Oracle中使用多表查询性能可能比子查询好一些


Oracle分页

在讲解JDBC的时候,我们就已经讲过Oracle与Mysql的分页问题了....详情可以看我的博文:http://blog.csdn.net/hon_3y/article/details/53790092

我们在这里还是加深一下印象:

Oracle中的分页是依靠着rownum这个伪列来实现的,由于rownum只能使用的是<=或者<来获取数据。。。因为rownum的值可能会经常变【加入一条数据,那么rownum就+1,讲道理rownum可以是无穷大的,因此不能使用>来进行操作】....

那么Oracle分页的思路是这样子的:

  • 先在子查询中获取前n条记录
  • 由于返回的是多行多列,因此我们可以看做成一张表
  • 那么将查询出来的数据放在from字句的后边
  • 外套的查询可以通过where字句来对子查询出来的数据进行过滤
  • 那么我们就可以查询出想要的数据了...

公式:

  • **Mysql从(currentPage-1)*lineSize开始取数据,取lineSize条数据**
  • Oracle先获取currentPagelineSize条数据,从(currentPage-1)lineSize开始取数据

小面试题

笔试题:有【1000亿】条会员记录,如何用最高效的方式将薪水字段清零,其它字段内容不变?

第一:从emp表中删除sal字段

  •   alter table emp 
  •   drop column sal;      

第二:向emp表中添加sal字段,且内容默认0

  •   alter table emp
  •   add sal number(6) default 0;

操作表细节

进入回收站
drop table users;

查询回收站中的对象
show recyclebin;

闪回,即将回收站还原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to  新表名;

彻底删除users表
drop table users purge;

清空回收站
purge recyclebin;

为emp表增加image列,alter table 表名 add 列名 类型(宽度) 
alter table emp
add image blob;

修改ename列的长度为20个字节,alter table 表名 modify 列名 类型(宽度) 
alter table emp
modify ename varchar2(20);

删除image列,alter table 表名 drop column 列名
alter table emp
drop column image;

重名列名ename为username,alter table 表名 rename column 原列名 to 新列名
alter table emp
rename column ename to username;

将emp表重命名emps,rename 原表名 to 新表名
rename emp to emps;
  • number(5):
    • 最多5位数字
  • number(6,2):
    • 其中2表示最多显示2位小数,采用四舍五入,不足位数补0,同时要设置col ... for ...
    • 其中6表示小数+整数不多于6位
    • 其中整数位数不得多于4位,可以等于4位
  • varchar2(8):
    • 8表示字节

值得注意的是:修改表的时候,是不能回滚的!

Oracle中的级联操作:

  • 【on delete cascade】级联删除
  • 【on delete set null】将外键一方设置为null

如果文章有错的地方欢迎指正,大家互相交流。习惯在微信看技术文章,想要获取更多的Java资源的同学,可以关注微信公众号:Java3y

更多的文章可往: 文章的目录导航
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
12天前
|
SQL 存储 缓存
高基数 GroupBy 在 SLS SQL 中的查询加速
本文详细介绍了SLS中的高基数GroupBy查询加速技术。
|
11天前
|
SQL 运维 程序员
一个功能丰富的SQL审核查询平台
一个功能丰富的SQL审核查询平台
|
18天前
|
SQL 数据库 Java
HQL vs SQL:谁将统治数据库查询的未来?揭秘Hibernate的神秘力量!
【8月更文挑战第31天】Hibernate查询语言(HQL)是一种面向对象的查询语言,它模仿了SQL的语法,但操作对象为持久化类及其属性,而非数据库表和列。HQL具有类型安全、易于维护等优点,支持面向对象的高级特性,内置大量函数,可灵活处理查询结果。下面通过示例对比HQL与SQL,展示HQL在实际应用中的优势。例如,HQL查询“从员工表中筛选年龄大于30岁的员工”只需简单地表示为 `FROM Employee e WHERE e.age &gt; 30`,而在SQL中则需明确指定表名和列名。此外,HQL在处理关联查询时也更为直观易懂。然而,对于某些复杂的数据库操作,SQL仍有其独特优势。
26 0
|
18天前
|
SQL 关系型数据库 MySQL
|
18天前
|
API Java 数据库连接
从平凡到卓越:Hibernate Criteria API 让你的数据库查询瞬间高大上,彻底告别复杂SQL!
【8月更文挑战第31天】构建复杂查询是数据库应用开发中的常见需求。Hibernate 的 Criteria API 以其强大和灵活的特点,允许开发者以面向对象的方式构建查询逻辑,同时具备 SQL 的表达力。本文将介绍 Criteria API 的基本用法并通过示例展示其实际应用。此 API 通过 API 构建查询条件而非直接编写查询语句,提高了代码的可读性和安全性。无论是简单的条件过滤还是复杂的分页和连接查询,Criteria API 均能胜任,有助于提升开发效率和应用的健壮性。
30 0
|
18天前
|
Java UED 开发者
当错误遇上Struts 2:一场优雅的异常处理盛宴,如何让错误信息成为用户体验的救星?
【8月更文挑战第31天】在Web应用开发中,异常处理对确保用户体验和系统稳定性至关重要。Struts 2 提供了完善的异常处理机制,包括 `exception` 拦截器、`ActionSupport` 类以及 OGNL 表达式,帮助开发者优雅地捕获和展示错误信息。本文详细介绍了 Struts 2 的异常处理策略,涵盖拦截器配置、错误信息展示及自定义全局异常处理器的实现方法,使应用程序更加健壮和用户友好。
26 0
|
18天前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
47 0
|
18天前
|
前端开发 开发者
Vaadin Grid的秘密武器:打造超凡脱俗的数据展示体验!
【8月更文挑战第31天】赵萌是一位热爱UI设计的前端开发工程师。在公司内部项目中,她面临大量用户数据展示的挑战,并选择了功能强大的Vaadin Grid来解决。她在技术博客上分享了这一过程,介绍了Vaadin Grid的基本概念及其丰富的内置功能。通过自定义列和模板,赵萌展示了如何实现复杂的数据展示。
23 0
|
18天前
|
SQL 存储 安全
Play Framework的安全面纱:揭开隐藏在优雅代码下的威胁
【8月更文挑战第31天】Play Framework 是一款高效、轻量级的 Web 开发框架,内置多种安全特性,助力开发者构建安全稳定的应用。本文详细介绍 Play 如何防范 SQL 注入、XSS 攻击、CSRF 攻击,并提供安全的密码存储方法及权限管理策略,通过具体示例代码展示实施步骤,助您有效抵御常见威胁。
33 0
|
18天前
|
SQL 关系型数据库 MySQL

推荐镜像

更多