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

怼烎@ 2021-09-14 22:54 528阅读 0赞

# 前言 #

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

# 实例与数据库概念 #

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

*  **实例：理解为对象,看不见的**
 *  **数据库：理解为类，看得见的**

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

![这里写图片描述][SouthEast]

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

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

![这里写图片描述][SouthEast 1]

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

![这里写图片描述][SouthEast 2]

--------------------

# 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\*\*提供了关于字符串函数、日期函数供我们对数据进行对应的操作\*\*，这里就不一一赘述了，我们到时候有需要的时候查文档就行了。

![这里写图片描述][SouthEast 3]

单引号出现的地方如下：

*  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子句中**

![这里写图片描述][SouthEast 4]

举例子：**下面这段代码是错误的！！！**

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;

![这里写图片描述][SouthEast 5]

--------------------

# 多表查询、子查询 #

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

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

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

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

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

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

--------------------

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

![这里写图片描述][SouthEast 6]

## 连接 ##

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

一般地，我们有几种连接：

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

![这里写图片描述][SouthEast 7]

![这里写图片描述][SouthEast 8]

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

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

![这里写图片描述][SouthEast 9]

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

![这里写图片描述][SouthEast 10]

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

--------------------

# Oracle分页 #

在讲解JDBC的时候，我们就已经讲过Oracle与Mysql的分页问题了….详情可以看我的博文：[http://blog.csdn.net/hon\_3y/article/details/53790092][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先获取currentPage\*lineSize条数据，从(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**

[SouthEast]: /images/20210827/7120f94b6013416fb90e0235aaeb3da0.png
[SouthEast 1]: /images/20210827/7e5c2a61507840f4a6d674f35fde86a4.png
[SouthEast 2]: /images/20210827/77acdcd165714a5ebd3d13a518d4aa8d.png
[SouthEast 3]: /images/20210827/08e823d0028e4249bcd59c15cdaf6d34.png
[SouthEast 4]: /images/20210827/1656caca63e0412b86a706c2c71fa171.png
[SouthEast 5]: /images/20210827/4fa13d4a0e094191993a6751740f5d0b.png
[SouthEast 6]: /images/20210827/9f429b5ff81c4c80a25013c84df4ee23.png
[SouthEast 7]: /images/20210827/f834f82bd27e499a8d58c4ea86dd7457.png
[SouthEast 8]: /images/20210827/5d4829ca758a437887e4bc996cd9c6aa.png
[SouthEast 9]: /images/20210827/49976e7f65554fada0b0b0815e7d9ebf.png
[SouthEast 10]: /images/20210827/4fe1a9fe6bea437ead14a8cd7dd0eb5e.png
[http_blog.csdn.net_hon_3y_article_details_53790092]: http://blog.csdn.net/hon_3y/article/details/53790092