Oracle基础知识及查询优化

爱被打了一巴掌 2022-12-05 13:59 407阅读 0赞

### 文章目录 ###

*  Oracle基础知识总结
 *   *  一、基础知识（DML ）
     *   *  1、Select语句
         *   *  Order By 、Group By子句
             *  Distinct子句
             *  Where子句
             *  And子句
             *  Or子句
             *  Fetch子句
             *   *  OFFSET子句
                 *  FETCH子句
             *  IN子句
             *  Between子句
             *  Like子句
             *   *  通配符
         *  2、Insert语句
         *  3、Update语句
         *  4、Delete语句
     *  二、DDL语句(数据定义语言,Data Definition Language)
     *   *  1、数据库创建
         *  2、数据库删除
         *  3、创建表
         *  4、修改表
         *  5、删除表
         *  6、截断表（彻底释放表占用空间）
         *  7、闪回
         *  8、约束
         *  9、视图
     *  三、索引
     *   *  1、建设原则
         *  2、创建索引
     *  四、SQL优化
     *   *  1、执行顺序
         *  2、避免使用 '\*'
         *  3、索引失效的情况
         *   *  1、Not Null/Null 如果某列建立索引,当进行
             *  2、索引列上不要使用函数
             *  3、索引列上不能进行计算
             *  4、索引列上不要使用NOT （ != 、 <> ）
         *  5、用UNION替换OR(适用于索引列)
         *  6、合理利用：EXISTS、IN、NOT EXISTS、NOT IN
     *  五、Oracle常用函数
     *   *  1、SUBSTR截取字符串函数
         *  2、TRIM函数字符串去空格
         *  3、字符串处理函数
         *  4、NVL函数处理空值
         *  5、数字处理函数
         *  6、日期处理函数
         *  7、TRUNC截取日期/截取数字类型
         *  8、逻辑判断
         *  9、分组排序函数
         *   *  row\_number() over()
             *  rank() over()
             *  dense\_rank() over()

> 本文来自于同事的学习总结。

> **DML(Data Manipulation Language)数据操纵语言）**：适用范围，对数据库中的数据进行一些简单操作，如insert,delete,update,select等。  
> **DDL(Data Definition Language)数据定义语言**：适用范围，对数据库中的某些对象(例如，database,table)进行管理，如Create,Alter和Drop。

# Oracle基础知识总结 #

## 一、基础知识（DML ） ##

### 1、Select语句 ###

SELECT
      *,/*查询所有字段*/
      column_1, /*字段1*/
      column_2, /*字段2*/
      column_3 as 别名,/*给字段取别名*/
      ...
    FROM
      table_name;/*表名*/

#### Order By 、Group By子句 ####

SELECT
        column_1,
        column_2,
        column_3,
        ...
    FROM
        table_name
    ORDER BY
        column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST]/*ASC 升序可省略，默认。DESC降序*/
        ,column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST]/*NULLS FIRST 空值在前；NULLS LAST空值在后*/

having: 只在在group by后面, 分组后对结果再次过滤

#### Distinct子句 ####

SELECT DISTINCT column_1,
        		/*可以用来过滤结果集中的重复行，确保SELECT子句中返回指定的一列或多列的值是唯一的*/
        column_2,
            ...
    FROM
        table_name;

#### Where子句 ####

SELECT
        column_1,
        column_2,
        ...
    FROM
        table_name
    WHERE
        search_condition/*where后面指定SELECT语句返回符合搜索条件的行记录*/
    ORDER BY
        column_1,
        column_2;

<table> 
 <thead> 
  <tr> 
   <th>编号</th> 
   <th>运算符</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td><code>=</code></td> 
   <td>等于</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td><code>!=</code>,<code>&lt;&gt;</code></td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td><code>&gt;</code></td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td><code>&lt;</code></td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td><code>&gt;=</code></td> 
   <td>大于或等于</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td><code>&lt;=</code></td> 
   <td>小于或等于</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>IN</td> 
   <td>等于值列表中的任何值</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>ANY/SOME/ALL</td> 
   <td>将值与列表或子查询进行比较。它必须以另一个运算符(例如：<code>=</code>，<code>&gt;</code>，<code>&lt;</code>)作为前缀。</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>NOT IN</td> 
   <td>不等于值列表中的任何值</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>[NOT] BETWEEN n AND m</td> 
   <td>相当于<code>[Not] &gt;= n</code> 且 &lt;<code>= y</code>。</td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td>[NOT] EXISTS</td> 
   <td>如果子查询返回至少一行，则返回<code>true</code></td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td><code>IS [NOT] NULL</code></td> 
   <td>测试<code>NULL</code>的值</td> 
  </tr> 
 </tbody> 
</table>

#### And子句 ####

在SELECT，DELETE和UPDATE语句的WHERE子句中使用AND来形成匹配数据的条件

SELECT 
    	order_id, 
    	customer_id, 
    	status
    FROM 
    	orders
    WHERE  
    	status = 'Pending'  /*优先级：括号>And>Or*/
    	AND customer_id = 2	/*表示并且*/

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>TRUE</th> 
   <th>FALSE</th> 
   <th>NULL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TRUE</td> 
   <td>TRUE</td> 
   <td>FALSE</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>FALSE</td> 
   <td>FALSE</td> 
   <td>FALSE</td> 
   <td>FALSE</td> 
  </tr> 
  <tr> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>FALSE</td> 
   <td>NULL</td> 
  </tr> 
 </tbody> 
</table>

#### Or子句 ####

OR运算符组合了布尔表达式，如果其中一个表达式为真(true)，则返回true。

SELECT
        order_id,
        customer_id,
        status
    FROM
        orders
    WHERE
        status = 'Pending'
        OR status = 'Canceled'/*表示或者*/

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>TRUE</th> 
   <th>FALSE</th> 
   <th>NULL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TRUE</td> 
   <td>TRUE</td> 
   <td>TRUE</td> 
   <td>TRUE</td> 
  </tr> 
  <tr> 
   <td>FALSE</td> 
   <td>TRUE</td> 
   <td>FALSE</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>NULL</td> 
   <td>TRUE</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
 </tbody> 
</table>

#### Fetch子句 ####

FETCH子句在Oracle中可以用来限制查询返回的行数

[ OFFSET offset ROWS/*跳过行数*/]
     FETCH  NEXT [  row_count/*行数*/ | percent PERCENT/*百分比*/  ] ROWS  [ ONLY/*不重复*/ | WITH TIES/*可重复*/ ]

##### OFFSET子句 #####

OFFSET子句指定在行限制开始之前要跳过行数。OFFSET子句是可选的。 如果跳过它，则偏移量为0，行限制从第一行开始计算。

偏移量必须是一个数字或一个表达式，其值为一个数字。偏移量遵守以下规则：

*  如果偏移量是负值，则将其视为0。
 *  如果偏移量为NULL或大于查询返回的行数，则不返回任何行。
 *  如果偏移量包含一个分数，则分数部分被截断。

##### FETCH子句 #####

FETCH子句指定要返回的行数或百分比。

为了语义清晰的目的，您可以使用关键字ROW而不是ROWS，FIRST而不是NEXT。 例如，以下子句的行为和产生的结果相同：

FETCH NEXT 1 ROWS
    FETCH FIRST 1 ROW

#### IN子句 ####

Oracle IN运算符可以用来确定值是否与列表或子查询中的任何值相匹配

expression [NOT] IN (v1,v2,...)/*直接输入值*/

expression [NOT] IN (subquery)/*子查询语句*/

#### Between子句 ####

BETWEEN运算符可以用来在Oracle中选择值在一个范围内的行数据

expression [ NOT ] BETWEEN low AND high

*  low 和high - low和hight指定要测试的范围的下限值和上限值。low和hight值可以是文字或表达式。
 *  expression - 是low和hight定义的范围内测试的表达式。 为了能够比较，expression，low和hight的数据类型必须是相同的。
 *  AND - AND运算符充当占位符来分隔low和hight的值。

#### Like子句 ####

LIKE运算符在Oracle可以用来测试列中的值是否与指定的模式匹配

expresion [NOT] LIKE pattern [ ESCAPE escape_characters ]

*  expression：该表达式是一个列名称或一个表达式，要针对该模式(pattern)进行测试。
 *  pattern：该模式是在表达式中搜索的字符串。此模式包含以下通配符：％(百分号)匹配零个或多个字符的任何字符串。\_(下划线)匹配任何单个字符。
 *  escape\_character：escape\_character是出现在通配符前面的字符，用于指定通配符不应被解释为通配符而是常规字符。

##### 通配符 #####

% 表示匹配任意多个字符

\_表示一个字符

### 2、Insert语句 ###

insert into 表名 (列1, 列2..) values ( 值1, 值2)    /* 一一对应即可*/
    insert into 表名 values (值1, 值2, ..)                 /* 和表的列一一对应，不建议*/

### 3、Update语句 ###

update table_name set column_1='name', column_2='woman' where 条件;   /* 不加where子句, 会全部修改*/

### 4、Delete语句 ###

delete from 表名 where 条件

## 二、DDL语句(数据定义语言,Data Definition Language) ##

### 1、数据库创建 ###

CREATE DATABASE IF NOT EXISTS dbName CHARACTER SET gbk;

*  创建数据库: CREATE DATABASE 数据库名称;
 *  创建数据库，判断是否存在，不存在则创建:CREATE DATABASE IF NOT EXISTS 数据库名称;
 *  创建数据库并指定其字符集: CREATE DATABASE 数据库名称 CHARACTER SET 字符集;

### 2、数据库删除 ###

DROP DATABASE IF EXISTS 数据库名称;/*判断数据库存在，存在则删除，物理删除*/

### 3、创建表 ###

CREATE TABLE tableName(
            列名1 数据类型1,
            列名2 数据类型2,
            ....
            列名n 数据类型n,
            [添加约束...]
        );
        复制表: 
        CREATE TABLE 表名 like 被复制的表名;

### 4、修改表 ###

修改表名:
    ALTER TABLE 表名 RENAME TO 新表名;
    修改表的字符集:
    ALTER TABLE 表名 CHARACTER SET 字符集名称;
    添加一列:
    ALTER TABLE 表名 ADD 列名 数据类型
    修改列名 类型:
    ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
    只修改数据类型
    ALTER TABLE 表名 MODIFY 列名 新数据类型;
    删除列:
    ALTER TABLE 表名 DROP 列名;

### 5、删除表 ###

DROP TABLE IF EXISTS 表名;

### 6、截断表（彻底释放表占用空间） ###

TRUNCATE TABLE 表名称

### 7、闪回 ###

在oracle10g之后，为了预防用户的误删除表操作，专门提供了回收站的功能，用户所删除的表默认情况下会在一个回收站之中保存，而用户也可以通过回收站进行表的恢复，所以此技术称为闪回(FLASHBACK)。

查看回收站

SHOW RECYCLEBIN;

这个时候可以发现所有已经删除的表都在回收站之中保存，那么下面就可以使用如下的语法进行表的恢复；

FLASHBACK TABLE 表名称 TO BEFORE DROP;

### 8、约束 ###

表建立完成后，并不能检查表中的数据是否合法，如果想要针对表中的数据做一些过滤的话，则可以通过约束完成，约束的主要功能是保证表中的数据合法性，按照约束的分类，一共有五种约束：

非空约束

唯一约束

主键约束

检查约束

外键约束

非空约束/NOT NULL/NK

在建表时创建约束：

CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    SEX VARCHAR2(10) NOT NULL,
    AGE NUMBER(3),
    CONSTRAINT PK_MID PRIMARY KEY (MID),
    CONSTRAINT CK_SEX CHECK (SEX IN (‘NAN’,’NV’,’QITA’)),
    CONSTRAINT CK_AGE CHECK (AGE BETWEEN 0 AND 250)
    );

在创建表后创建约束：

ALTER TABLE MEMBER ADD CONSTRAINT PK_MID PRIMARY KEY(MID);/*添加约束*/

### 9、视图 ###

在我们之前学过的所有的SQL语法之中，查询操作是最麻烦的，为了更好更快地使用数据库，我们需要创建视图，视图中包含了一些复杂的SQL语句。视图相当于封装SQL语句。

视图创建语法：
    CREATE [OR REPLACE] VIEW 视图名称
    AS 子查询
    WITH READ ONLY/*设置为只读*/
    
    删除视图
    DROP VIEW MYVIEW;

## 三、索引 ##

如何某表的某个字段有主键约束和唯一性约束，则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。

### 1、建设原则 ###

1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%。则应该考虑。

2、对于两表连接的字段，应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

3、不应该在小表上建设索引。

优缺点:  
　1、索引主要进行提高数据的查询速度。 当进行DML时，会更新索引。因此索引越多，则DML越慢，其需要维护索引。 因此在创建索引及DML需要权衡。

### 2、创建索引 ###

单一索引:

Create Index <Index-Name> On <Table_Name>(Column_Name);

复合索引:

Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。
    
    select * from emp where deptno=66 and job='sals' ->走索引。
    
    select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引
    
    select * from emp where deptno=66 ->走索引。
    
    select * from emp where job='sals' ->进行全表扫描、不走索引。

如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引，将会进行全表扫描。

## 四、SQL优化 ##

当Oracle数据库拿到SQL语句时，其会根据查询优化器分析该语句，并根据分析结果生成查询执行计划。

也就是说，数据库是执行的查询计划，而不是Sql语句。

查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。

其中基于规则的查询优化器在10g版本中消失。

对于规则查询，其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。

### 1、执行顺序 ###

1、先执行From ->Where ->Group By->Order By

2、执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。这是为什么呢？

3、对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾，而对于多表之间的连接，则写在之前。因为这样进行连接时，可以去掉大多不重复的项。

### 2、避免使用 ‘\*’ ###

1、SELECT子句中避免使用（‘\*’）ORACLE在解析的过程中, 会将\*依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

### 3、索引失效的情况 ###

#### 1、Not Null/Null 如果某列建立索引,当进行 ####

Select * from emp where depto is not null/is null/*索引失效*/

#### 2、索引列上不要使用函数 ####

SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC' 
    或者
    SELECT Col FROM tbl WHERE name LIKE '%ABC%'/*索引失效*/

而

SELECT Col FROM tbl WHERE name LIKE 'ABC%' /*会使用索引*/

#### 3、索引列上不能进行计算 ####

SELECT Col FROM tbl WHERE col / 10 > 10/*索引失效*/

应该改成

SELECT Col FROM tbl WHERE col > 10 * 10

#### 4、索引列上不要使用NOT （ != 、 <> ） ####

SELECT Col FROM tbl WHERE col ! = 10

应该 改成：

SELECT Col FROM tbl WHERE col > 10 OR col < 10

### 5、用UNION替换OR(适用于索引列) ###

union:是将两个查询的结果集进行追加在一起，它不会引起列的变化。 由于是追加操作，需要两个结果集的列数应该是相关的。

并且相应列的数据类型也应该相当的。union 返回两个结果集，同时将两个结果集重复的项进行消除。 如果不进行消除，用UNOIN ALL。

用UNION替换WHERE子句中的OR将会起到较好的效果。 对索引列使用OR将造成全表扫描。 注意, 以上规则只针对多个索引列有效，如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低

### 6、合理利用：EXISTS、IN、NOT EXISTS、NOT IN ###

参考[《MySQL高性能优化规范建议》][MySQL]，这里没有对Oracle数据库下的情况做验证。

## 五、Oracle常用函数 ##

### 1、SUBSTR截取字符串函数 ###

substr(string , a,  b);

注：string为字符串类型，a和b为int类型  
1、string 需要截取的字符串  
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）  
3、b 要截取的字符串的长度

### 2、TRIM函数字符串去空格 ###

LTRIM(str):将字符串左边的空格移除。
    RTRIM(str): 将字符串右边的空格移除。
    TRIM(str): 将字符串首尾两端的空格移除。

### 3、字符串处理函数 ###

### 4、NVL函数处理空值 ###

nvl(str1,str2)
    如果str1为空则返回str2
    nvl(str1,str2,str3)
    如果str1位空则返回str3，如果不为空则返回str2

### 5、数字处理函数 ###

语法：
    	ROUND(number，num_digits)
    其中Number是需要进行四舍五入的数字；Num_digits为指定的位数，按此位数进行四舍五入
    	ABS(n)取绝对值
    	CEIL(n)向上取整
    	SIN(n)正弦
    	COS(n)余弦
    	SIGN(n)取符号
    	FLOOR(n)向下取整
    	POWER(m,n)m的n次幂
    	MOD(m,n)取余
    	SQRT(n)平方根

### 6、日期处理函数 ###

--months_between：获取两个日期之间的月份
    select trunc(months_between(to_date('1990-09-1','yyyy-mm-dd'),sysdate)) from dual;
    
    --add_months:几个月之后的日期(加减月份)
    select add_months(sysdate, 4) from dual;
    
    --next_day:下一次出现星期X的日期,day由1-7表示，其中1表示星期日。
    select next_day(sysdate,1) from dual;
    
    --last_day:获取当月最后一天
    select last_day(sysdate) from dual
    
    --to_char:把日期（任意类型）转换成字符串
    select to_char(sysdate,'yyyy-mm-dd') from dual;
    
    --to_date:把任意类型转成时间日期
    select to_char(to_date('1999-1-1 20:20:20','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;

### 7、TRUNC截取日期/截取数字类型 ###

--语法格式：
    --TRUNC（date[,fmt]）
    --其中：date 一个日期值；fmt 日期格式。
    Trunc(sysdate,'yyyy')  --结果为2020

--语法格式：
    --TRUNC（number[,decimals]）
    --其中： number 待做截取处理的数值；decimals 指明需保留小数点后面的位数，可选项，忽略它则截去所有的小数部分。
    --注意：截取时并不对数据进行四舍五入。
    select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去;
    select trunc(123.567,-2) from dual;--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
    select trunc(123.567) from dual;--123,默认截去小数点后面的部分;

### 8、逻辑判断 ###

(case 
     	when 判断1 then 结果1 else 结果2 
    	when 判断2 then 结果1 else 结果2 
    ...
    end )

--decode(条件,值1,赋值1,值2,赋值2,...赋值n,赋值n,x)
    SELECT ID,DECODE(inParam,'para1','值1',
        'para2','值2',
        'para3','值3',
        'para4','值4',
        'para5','值5',
        'para6') name FROM bank
    --对inParam进行判断，如果值为'para1'则重新赋值为‘值1’
    --如果不在判断范围内的值则赋值为‘para6’

### 9、分组排序函数 ###

#### row\_number() over() ####

--row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，
    --而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。
    select t.*,row_number() over(partition by accno order by createDate) row_number from Test t

#### rank() over() ####

--rank()是跳跃排序，有两个第二名时接下来就是第四名。
    select t.*,rank() over(partition by accno order by createDate) rank from Test t

#### dense\_rank() over() ####

--dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。
    select t.*,dense_rank() over(partition by accno order by createDate) dense_rank from Test t

[MySQL]: https://blog.csdn.net/dear_little_bear/article/details/108511836