Oracle数据库入门

风吹过的心缘

已于 2024-08-16 10:49:43 修改

阅读量2.3k

点赞数 11

分类专栏： Oracle学习文章标签： oracle

于 2024-07-29 16:55:38 首次发布

本文链接：https://blog.csdn.net/weixin_47810321/article/details/140775350

版权

Oracle学习专栏收录该内容

1 篇文章

订阅专栏

一、oracle数据库简介

1、什么是数据库？

数据库是用来存储和管理数据的软件

2、数据库的分类

关系型数据库：是目前主流的数据库类型

特点：关系型数据库中，数据一般以二维表格的形式进行存储；二维表格，只有行和列；一行表示一条完整的数据；一个列也称为一张表的一个属性，也叫它一个字段，一张表中可以有多个列。
非关系型数据库：除了关系型之外的统称非关系型数据库，比如网状模型，层次模型

3、关系型数据库有哪些软件

Oracle软件：
- 是目前市场占用率最高的软件，是由Oracle（甲骨文）公司开发的一款数据库软件。
- 是一款商业收费的软件，其特点是性能强大，安全性比较高，一般用在银行，保险，证券等对
- 数据安全要求较高的公司中
MySQL软件：
- 是目前市场占用排名第二的数据库软件，目前是由Oracle公司进行管理。
- 是一款开源免费的软件，开源是指公开其源代码，使得任何人都能够去改造MySQL数据库，生成自己专属的软件，比如华为的高斯数据库，是根据MySQL进行改造而来的。
- 其特点是免费，且性能均衡，没有明显的短板，一般用在中小型互联网公司中。
SQL Server：
- 是由微软公司独立开发的数据库软件，一般用在微软系的相关产品中

4、SQL语言

是一套关系型数据库的通用语，这些关系性数据库的操作语言大体相同，只有小部分不同。

DDL语句：数据定义语言，主要用于创建、修改和删除数据库结构的对象，包括create 创建 alter 修改 drop 删除。
DML语句：数据操纵语言，主要负责数据表的操作管理。insert 插入 update 修改 delete 删除。
DQL语句：数据查询语言，select 等一系列关键字（重点内容）
DCL语句：数据控制语言，权限管理（grant，revoke），
TCL语句：事务控制语言，事务管理（commit，rollback）

5、Oracle数据库的基本概念

Oracle服务器是一个数据管理系统（RDBMS），它提供开放的，全面的，近乎完整的信息管理。是由Oracle数据库和Oracle实例组成。

Oracle数据库：就是位于硬盘上实际存放数据的文件（控制文件，数据文件(后缀.dbf)，日志文件(.log)等等），Oracle数据库必须要与内存里的实例合作，才能对外提供数据管理服务。
Oracle实例：位于内存里的数据结构。它由一个共享的内存池和多个后台进程组成，共享的内存池可以被所有的进程访问。用户如果要存取数据库（也就是硬盘上的文件）里的数据，必须通过实例才能实现，不能直接读取硬盘上的文件。

二、SQL语句之DDL

1、表空间管理

含义

表空间是用来存储表的一个结构，数据存储在表中，表存储在表空间中。

一个表空间会对应硬盘中的一个数据文件，当我们创建一个表空间时，会在硬盘中创建一个其对应的数据文件。
创建表空间

/*
create 创建
tablespace 表空间
datafile 数据文件
size 大小
auto extend  自动扩展

语法
CREATE [smallfile/bigfile] TABLESPACE 表空间名
DATAFILE '文件数据路径'  
SIZE 文件大小 [reuse]   -- reuse  文件如果存在则自动删除（可不写）                                  
[AUTOEXTEND OFF]		-- off 关闭  on 打开 （AUTOEXTEND ON NEXT 拓展大小）	
[maxsize]				-- 上限大小  将值写为 unlimited 不设限制（系统自动判断）
*/
CREATE TABLESPACE mytest
DATAFILE 'D:\OracleDatabase\mytest.dbf'    --数据文件存放的路径
SIZE 20m                                   --数据文件的初始大小20M  
AUTOEXTEND OFF;							--不自动扩展

课堂练习：

创建一个大文件表空间tbs_users,该表空间可以自动扩展，初始大小为100M,每次扩展10m,最大限制1g。
创建一个小表空间tbs_test,该表空间数据文件初始大小为10m,自动扩展，每次扩展5m,没有最大限制。

查询所有的表空间
```
SELECT * FROM dba_tablespaces;
```
删除表空间
```
/*
drop  删除
including 包括
contents 内容
语法
DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES;
*/
DROP TABLESPACE mytest INCLUDING CONTENTS AND DATAFILES;
```
注意：如果没有加“INCLUDING CONTENTS AND DATAFILES”这句代码，那么只是把表空间从oracle中删除，但是表空间对应的数据文件是没有删除的。

2、用户的管理

创建用户

/*
语法
CREATE USER  用户名 IDENTIFIED BY 密码          
DEFAULT TABLESPACE 默认表空间(永久保存数据)
TEMPORARY TABLESPACE temp(临时存储数据);
*/
CREATE USER zhangsan IDENTIFIED BY 123456       
DEFAULT TABLESPACE mytest                      
TEMPORARY TABLESPACE temp;

修改用户密码

/*
对于已经存在的用户，我们可以去修改其密码：
语法
ALTER USER 用户名 IDENTIFIED BY 新密码;
*/
ALTER USER zhangsan IDENTIFIED BY 666666;

删除用户

/*
语法
对于没有任何对象的用户
drop user 用户名;
对于有对象的用户
drop user cascade;
*/
DROP USER zhangsan;
/*
如果用户创建的其他对象，则需要在后面加cascade关键字
*/
DROP USER zhangsan CASCADE;

授予权限
```
/*
语法
GRANT 权限名[,权限名2...] TO 用户名
*/
GRANT CONNECT TO zhangsan;
```
我们常用的权限有如下：

单个权限：

create session：进入数据库的最小权限

create view：创建视图

create table：创建表格

权限集合：

connect：允许用户创建会话权限，包含 create session 权限

resource：开发权限，包含 create table，create view 等权限

dba: 管理员权限

也可以把某个用户所创建的对象权限授予给其他用户比如
```
--对象权限
--授予查询scott用户的emp表的权限  insert ， delete  , update ,select ,  all
grant all on scott.emp  to zhangsan;
--收回权限 revoke  to： 到哪里去   from:从哪里来
revoke select on scott.emp  from zhangsan;
```

撤销权限

/*
语法
REVOKE 权限名 [,权限名2...] FROM 用户名
*/
REVOKE CONNECT  FROM zhangsan;

解锁锁定的账号
```
alter user 用户名 account unlock;
```

练习：

创建表空间 user_test，要求为小表空间，在本地存储文件，表空间大小为30m，设置自动拓展，每次拓展大小 20m，最大拓展大小1G。
创建用户 zhangsan，密码 123456，设置默认表空间为 user_test，授予登录权限，然后将其密码改为 666666，使用 Scott账号创建一张demo表，如下

create table demo (dno number(3),dname varchar2(10));

将该demo表的增删改查权限授予 zhangsan 用户，撤销其删除权限，最后删除张三用户

3、表的操作

创建表

表：基本的数据存储集合，由行列组成。oracle数据库中，我们的业务数据都是保存在数据库表中，所以为了保存数据，我们需要先创建表。
表的数据类型
字符数据类型
- char：定长字符类型，最多存储2000字节的信息
- varchar2：可变长字符类型，最多存储4000字节的信息
- varchar(10)和varchar2(10)的区别：
  - varchar2把空串等同于null处理，而varchar仍按照空串处理；‘’
- char与varchar2的区别，char表示定长，而varchar2表示可变长度，比如我们有一个数据类型为char（10），那我们存储的的数据为“abc”，因为数据只有三个，而我们用char（10）来存储，那存在数据库，必须是保证长度为10，所以也就会在abc后面再添加7个空格，但是如果我们使用varchar2（10）来存储，因为vatchar2是可变长度，如果存储的是“abc”，那么在数据库中就是三位，不会添加空格。
数值数据类型
- number：存储整数和浮点数，格式 number(p,s)
n 其中p表示有效数字长度，包括整数和小数的个数，s表示小数位数，所以整数的位数为：p-s，比如说我们定义了一个数据类型为number（4,2），那么表示小数位必须是2位，有效数字位为4位，整数位最多为：4-2=2位，也就是说number（4,2）最大存储的数字是：99.99
日期时间数据类型
- date：存储日期和时间数据
- timestamp：时间戳，将时间转换成秒值，从计算机元年（1970-1-1 00:00:00）开始到当前时间，精确到小数点后6位
LONG类型

long：可变长字符串，用于存储内容较大的数据，类型中存储的内容大小最多为2GB，多设置在备注等字段中
LOB数据类型
- BLOB：存储二进制对象
- CLOB：存储字符格式的大型对象
- BFILE：将二进制数据存储操作系统文件中
创建表语法

  /*
  方法一：
  语法
  CREATE TABLE 表名(
     列名1  数据类型1,
     列名2  数据类型2,
     …
  )
  */
  -- 比如创建一个学生表，包含的列为：学号，姓名，性别，生日，身份证号码：
  CREATE TABLE t_student(
        stuNo NUMBER(7),
        sName VARCHAR2(50),
        sex CHAR(2),
        birthday DATE,
        cordId CHAR(18)   
  );
  
  /*
  方法二：
  根据一个已经存在的表，创建一个新的表
  语法
  CREATE TABLE 表名 AS SELECT 字段名1,字段名2... FROM 表名;
  */
  CREATE TABLE student3 AS SELECT sno,NAME FROM student;

修改表结构

增加新的一列

/*
语法
alter table  表名 add  新的字段名  字段的类型；
*/
ALTER TABLE student3 ADD gender CHAR(2);

修改已存在列

/*
语法
alter table 表名 modify 字段的名  新的字段类型；
*/
ALTER TABLE student MODIFY gender CHAR(2);

删除一列

/*
语法
alter table 表名 drop column 字段的名；
*/
ALTER TABLE student DROP COLUMN cordID;

修改字段名

/*
语法： 
alter table 表名 rename column 字段名  to  新的字段名；
*/
ALTER TABLE student RENAME COLUMN gender TO sex;

修改表名

/*
语法： rename 表名(旧) to 表名(新)
*/
RENAME student TO stu1;

删除表

可被还原

/*
语法： drop table 表名
*/
DROP TABLE student3;

还原以删除的表

Flashback table student3 to before drop;

彻底删除，不能回滚
```
drop table 表名 purge;
```
只删除表的数据，不会删除表的结构
```
/*
语法：truncate table 表名；
*/
TRUNCATE TABLE stu1;
```
注意：truncate不能删除某一行，如果要删除某一行需要使用delete

4、约束

约束是表一级的限制，是指在表上强制执行的数据校验规则。确保数据的准确性和可靠性。

大部分数据库支持下面五类完整性约束：

NOT NULL --非空
UNIQUE --唯一键
PRIMARY KEY --主键
FOREIGN KEY --外键
CHECK --检查是否满足要求

约束介绍：

非空约束（NOT NULL）确保字段值不允许为空，只能在字段之后定义。

Null值的一些特性：

1、空值跟任何值进行算术运算，得到的结果都为空值

2、空值跟任何值进行关系运算，得到的结果都为不成立

3、空值不参与任何聚合运算

4、排序的时候，空值永远是最大的

NULL值，所有数据类型的值都可以是NULL

空字符串不等于NULL “” != NULL

0也不等于NULL 0 != NULL
```
name varchar2(10) not null, --非空约束
```
主键约束（PRIMARY KEY）：从功能上看相当于非空并且唯一。
一个表中只能允许一个主键（并且一定要有一个主键）
1. 主键是表中唯一确定一行数据的字段
2. 主键字段可以是单个字段或者是多个字段的组合。
3. 主键约束建议与业务无关
```
id number primary key, --主键约束
```
唯一约束（UNIQUE ）：确保所在的字段或字段组合不出现重复值，

同一张表可建多个唯一约束，

唯一约束可以由多个字段组成。

唯一约束条件的字段允许出现多个NULL。
```
email varchar2(50) unique, -- 唯一约束
```
Check约束：定义在字段上的每一个记录都要满足的条件，即可写在字段后面，也可写在字段都定义之后。
```
age number not null check(age > 18 and age < 60), --check约束
```
外键约束

外键是构建于一个表的两个字段或者两个表的两个字段之间的关系。

子（从）表外键列的值必须在主表参照列值的范围内。

外键只能参照主键或者唯一键

当主表的记录被子表参照时，主表记录不允许被删除。
```
clazz_id number not null references tb_clazz(id) --外键约束
```
默认值：跟某字段添加默认值（如果没输入，则就是默认值，如果输入，则是输入的值）
```
address varchar2(200) default '地址不详',
```

创建约束的时机：

在建表的同时创建

第一种，在创建表的时候，在对应字段后添加约束：

CREATE TABLE tb_student(
   stuId NUMBER,
   sname VARCHAR2(10) NOT NULL, --非空约束
   sex VARCHAR2(6)   NOT NULL，
   age NUMBER NOT NULL CHECK(age>18 AND age<60)，,
   email VARCHAR2(50) ,
   address VARCHAR(200) default '地址不详',
   tell VARCHAR2(11),
   clazz_id NUMBER NOT NULL 
);

缺点：无法自己命名约束名

第二种，在创建表的时候，在结尾添加约束，可自定义约束名

CREATE TABLE tb_student(
   stuId NUMBER,
   sname VARCHAR2(10) NOT NULL, --非空约束
   sex VARCHAR2(6)   NOT NULL，
   age NUMBER NOT NULL,
   email VARCHAR2(50) ,
   address VARCHAR(200),
   tell VARCHAR2(11),
   clazz_id NUMBER NOT  NULL ，
	 CONSTRAINTS  tb_student_pk PRIMARY  KEY(stuId),
	 CONSTRAINTS  tb_student_uk UNIQUE(email),
	 CONSTRAINTS  tb_student_ck_sex CHECK(sex='男' OR sex='女')，
	 CONSTRAINTS  tb_student_ck_age CHECK(age>18 AND age<60)，
	 CONSTRAINTS  tb_student_fk_class_cid FOREIGN KEY(clazz_id) REFERENCES tb_clazz(cid)
);

如果表已经创建,可以以如下方式进行添加

添加约束：

/*
语法：Alter table 表名 add constraints 约束名 约束类型
*/
alter table tb_clazz add constraints tb_clazz_pk primary key(id);

删除约束：

/*
语法：alter table 表名 drop constraints 约束名；
*/
alter table tb_clazz drop constraints tb_clazz_pk;

当表创建完了之后给列添加约束;

-- 添加非空约束
alter table 表名 modify(sname not null);
-- 添加default约束
alter table 表名 modify address default '地址不详';

修改约束名称

/*
语法：alter table 表名 rename constraint old_name  to new_name;
*/
alter table emp rename constraint emp_pk to emp_pk2;

设置外键连接删除方式（了解）

/*
如果表中有外键约束要删除数据则需要设置级联删除方式
     no action(默认，不添加设置时自动设置):不做任何处理，也就是在删除的时候如果有关联的数据则不允许删除。
	cascade: 级联删除，在删除数据的时候会将另外一张表中所关联的所有数据都删除。
	set null：允许删除，在删除之后会将外键中关联的字段设置为null
*/
Alter table emp add constraints emp_fk foreign key(deptno) references dept(deptno)  on delete cascade /on update cascade;

三、SQL语句之DML

1、插入数据

使用insert关键字来进行向表中插入数据，insert的语法如下：

/*
语法
-- 方法一:
insert into 表名(字段1,字段2,..) values(值1,值2...);

-- 方法二:
insert into 表名 select * from 表名;
*/
CREATE TABLE emp(
       empno    NUMBER(11),     -- 员工编号
       ename    VARCHAR2(22),   -- 员工姓名
       job      VARCHAR2(22),   -- 工作岗位
       mgr      NUMBER,         -- 上级领导编号
       hiredate DATE,           -- 入职日期
       sal      NUMBER(11,2),   -- 员工工资
       comm     NUMBER(11,2),   -- 佣金
       deptno   NUMBER	        -- 对应部门编号
);
/*添加数据*/
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('19-4-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('23-5-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

/*创建表 dept*/
create table dept (
	deptno number(11),
    dname  varchar2(22),
    loc    varchar2(22)
);
/*添加数据*/
INSERT INTO dept(deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept(deptno, dname, loc) VALUES (20, 'RESEARCE', 'DAKKAS');
INSERT INTO dept(deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept(deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');

/*创建表 salgrade*/
create table salgrade(
    grade  number(11),
    losal  number(11),
    hisal  number(11)
);
/*添加数据*/
INSERT INTO salgrade(grade, losal, hisal) VALUES (1, 700, 1200);
INSERT INTO salgrade(grade, losal, hisal) VALUES (2, 1201, 1400);
INSERT INTO salgrade(grade, losal, hisal) VALUES (3, 1401, 2000);
INSERT INTO salgrade(grade, losal, hisal) VALUES (4, 2001, 3000);
INSERT INTO salgrade(grade, losal, hisal) VALUES (5, 3001, 9999);

如果表名后面没有写列名，则表示对表的所有的列添加数据，赋值的顺序与创建表的列的顺序要一致。
字符和日期型数据赢包含在单引号中，数字类型可以加单引号或者不加
Oracle默认的日期格式为“DD-MMM-YYYY”，如果想自定义日期格式，可以使用to_date()函数。
INSERT INTO t_student  VALUES(1000002,'李四','男',to_date('1998-10-11','yyyy-mm-dd'),'430112450987349087');

2、修改数据

/*
语法：
update 表名 set 要修改字段名 = 修改值 where 条件
*/
UPDATE emp SET empno = 1113 WHERE ename = '李四' AND empno = 2111;
UPDATE emp SET sales = 150 WHERE empno = 1111 OR empno = 1112;

3、删除数据

/*
语法
delete from 表名 where 条件
*/
DELETE FROM emp WHERE empno = 1113;

四、SQL语句之DQL

1、SQL语句中的所有关键词

select ...   要查询的字段
from   ...   要查询的表
where  ...   过滤条件
group by ... 分组字段
having ...   过滤条件
order by ... 排序字段

2、简单查询语句

3、where 过滤条件

where中可以包含如下条件

比较运算
- =、!=、<>、<、<=、>、>=；
逻辑运算
- and 逻辑与
- or 逻辑或
- not 逻辑非
范围查询

BETWEEN…AND…； between 后面接较小的值 and 后面接较大的值
IN(set)；
ANY(set)；
ALL(set)；

空值判断

IS NULL

IS NOT NULL
模糊查询 like
1. 模糊查询需要使用关键字LIKE。
2. 通配符
  - % : 任意多个字符
  - _：任意单个字符
3. 转义字符
  - MySQL软件中转义字符使用 \ 来表
  - Oracle软件中需要自定义转义字符 escape ‘字符’
```
-- 用 ESCAPE 关键字声明转义字符
SELECT empno,ename FROM emp WHERE ename LIKE '%\_%' ESCAPE '\';
```

4、字段控制查询

去除重复记录 DISCTINCT

DISTINCT 放在所有字段之前,只能有一个DISTINCT
NVL() 函数

nvl(字段名,默认值)函数：用法当某个字段出现 null 时，使用某个默认值替代
字段之间是可以进行数学运算的

加法: + 减法: - 乘法: * 除法: /
给字段和表起别名 as as通常情况可以省略
连接
1. 使用 || 连接，可以连接多个内容
  
  SELECT ename || ‘的工资：’ || sal FROM emp;
2. 使用 concat() 函数 ,只能连接两个内容
  
  SELECT CONCAT(CONCAT(ename,‘的工资：’),sal) FROM emp;

5、排序

语法：order by 字段1 排序方式(asc,默认 / DESC), 字段2 排序方式;

select 字段1,字段2,..
from 表名
order by 2 desc; -- 按照select后的第二个字段（也就是字段2）进行降序排序

6、聚合函数

聚合函数是用来做纵向运算的函数：

COUNT(字段)：统计指定列不为NULL的记录行数；

在使用count(字段1)进行数据统计时，因为字段可能出现空值，会导致统计结果出现偏差

解决方案：
1. 我们可以使用没有空值的字段进行统计，比如主键字段，或者非空字段
2. 使用count(数字) 比如 count(1) count(0) 来统计，count(1) 相当于新增一列，所有数据值为1，再统计非空的数据
3. 使用count(*) 相当于count(字段1,字段2,…) ,所有字段中只要有一个字段的值不为空，就会被纳入统计，同样，不推荐使用这种方式，原因和 select * 一致
特殊写法：COUNT(DISTINCT 字段)：统计表中该字段值不重复的数据量
MAX(字段)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN(字段)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM(字段)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG(字段)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

7、分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。

分组字段没有先后位置的区别
如果查询语句进行了分组，那么select,having,order by后，只能填写参与分组的字段和聚合函数
如果出现 group by，那么 select 后的字段只能是分组字段，不能写没有分组的字段
聚合函数在分组之后才会运行

8、HAVING子句

having的使用语法和where相似，不同之处在于：

having不能单独使用，必须配合group by 一起使用
where 后不能使用聚合函数作为过滤条件，having后可以使用聚合函数

关键字书写顺序运行顺序
select 5
from 1
where 2
group by 3
having 4
order by 6
当where和having能够实现相同功能时，使用 where 效率更高，因为 where 先过滤再分组，会使得分组的数据量更少

-- 查询工资总和大于8000的部门编号以及工资和：
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 8000;

注意！
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

9、多表联查

9.1、连接查询

笛卡尔积现象

两张表进行表连接时，没有写连接条件，就会发生笛卡尔积现象，表A的每一条数据都和表B中的所有数据进行了连接，使得最后查询结果的数据量是表A的数据量 * 表B的数据量。在实际中需要避免出现笛卡尔积现象，因为其会使得表的连接效率降低
```
-- 笛卡尔积
SELECT e.ename, d.dname 
FROM emp e,dept d;
```
两张表（表A20条数据，表B100条数据）进行连接，连接结果最多2000条数据，最少0条数据

内连接 [inner] join

特点：查询结果只展示两张表中满足连接条件的数据

/*连接条件：
1. 等值连接
*/
-- 找出每个员工对应的部门名称
SELECT e.ename, e.deptno FROM emp e;

SELECT d.deptno,d.dname FROM dept d;

-- 写法1 SQL92语法
SELECT e.ename, d.dname 
FROM emp e,dept d
WHERE e.deptno = d.deptno;

-- 写法2 SQL99  [inner] join  on
SELECT e.ename, d.dname 
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno;

-- 2.非等值连接
-- 查询每个员工对应的工资等级
SELECT e.ename,s.grade
FROM emp e
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;

-- 3.自连接
-- 查询每个员工对应的领导姓名
SELECT e1.ename AS 员工姓名,e2.ename AS 领导姓名
FROM emp e1    -- 员工表
JOIN emp e2    -- 领导表
ON e1.mgr = e2.empno;

外连接 [outter] join

特点：将连接的两张表分为主表和从表，查询结果会展示主表中的所有数据，从表中之后展示符合连接条件的数据
1. 左外连接 left [outter] join
  
  以左表为主表，右表为从表
```
-- 新要求：显示领导姓名，即使是 null ，内连接不能满足该要求
SELECT e1.ename AS 员工姓名,e2.ename AS 领导姓名
FROM emp e1    -- 员工表
LEFT OUTER JOIN emp e2    -- 领导表
ON e1.mgr = e2.empno;
```
2. 右外连接 right [outter] join
  
  以右表为主表，左表为从表
```
SELECT e1.ename AS 员工姓名,e2.ename AS 领导姓名
FROM emp e1    -- 员工表
RIGHT JOIN emp e2    -- 领导表
ON e1.mgr = e2.empno; 
```
  连接查询心得：
  - 连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
  - 两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
```
/*
WHERE和ON的区别：
1.不管是WHERE 还是ON，Oracle都会把能过滤的条件先过滤掉，再关联。
2.但两者区别在于，如果是内关联，两种结果相同，如果是外关联，结果会不同，ON会保留主表的所有信息，而WHERE可能会过滤掉部分主表信息。
*/
SELECT d.*,e.*
FROM emp e
RIGHT JOIN dept d
ON e.deptno = d.deptno
WHERE d.deptno = 30;  -- 6条数据

SELECT d.*,e.*
FROM emp e
RIGHT JOIN dept d
ON e.deptno = d.deptno
AND d.deptno = 30;	-- 9条数据
```

-- 课堂练习
-- 1.计算出每个工资级别的人数，该级别里最高的工资是多少，最低的工资是多少
-- ,如果没有员工的工资是属于这个级别的，也要将这个级别查询出来，最高工资和最低工资就展为0

自然连接 natural join

不能写表的连接条件，数据库会自动判断两张表的连接条件，默认将两张表中字段名相同和数据类型相同的字段，作为连接条件，进行等值连接。
一般不会使用自然连接，自然连接的结果，是一个内连接的结果集
```
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;
```

全连接 full join

-- 特点：会将两张表的所有数据都展示，最后的结果是数据的并集 
SELECT e.*,d.*
FROM emp e
FULL JOIN dept d
ON e.deptno = d.deptno;

交叉连接 cross join

--  直接将两张表中的数据进行笛卡尔积操作，最后执行的条数是两张表条数的乘积。
select e.*,d.*
from emp e
cross join dept;

9.2、子查询

特点： 在一条查询语句里嵌套一条查询语句，运行时先运行子查询中的查询语句，然后再将结果集传入外层查询中

可以出现的位置：

出现在 from 后；
出现在 where 或having后，作为条件的一部分；
出现在 select 后，作为被查询的一张表；

子查询结果集的形式：

单行单列（用于条件）
处理方式，放在where或having后作为一个条件
where 字段名 = (select子查询)
字段名 > (select子查询)
字段名 < (select子查询)
单行多列（用于条件）
处理方式1，放在where或having后作为一个条件
where (列1,列2,…) = (select 字段1,字段2,…);
执行方式：
外层表中列和子查询中的字段，根据位置顺序一一对应的进行等值匹配，只有所有字段的值都相等的数据，才被保留，其余数据都被过滤

处理方式2，放在from后作为一张表
from (select 查询语句) 别名 – 子查询作为表，一定要起别名
多行单列（用于条件）
处理方式，放在where或having后作为一个集合
where 字段名 in(select子查询)
字段名 > any(select子查询)
字段名 > all(select子查询)
多行多列（用于表）
处理方式1，放在where或having后作为一个集合
where (列1,列2,…) in (select 字段1,字段2,…);

执行方式：
外层表中列和子查询中的字段，根据位置顺序一一对应的进行等值匹配，
只要和子查询中一条数据所有字段的值相等，数据就被才被保留

处理方式2，放在from后作为一张表
from (select 查询语句) 别名 – 子查询作为表，一定要起别名
子查询中字段的名字如果有聚合函数或系统函数，那么这个字段需要起别名才能使用

/*
1.工资高于ALLEN的员工。
分析：
查询条件：工资>ALLEN工资，其中ALLEN工资需要一条子查询。
*/
-- 第一步：查询ALLEN的工资
SELECT sal FROM emp WHERE ename='ALLEN'
-- 第二步：查询高于ALLEN工资的员工
SELECT * FROM emp WHERE sal > (${第一步})
-- 结果：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='ALLEN')
/*
子查询作为条件
子查询形式为单行单列
*/

/*
2.工资高于30部门所有人的员工信息
分析：
查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。
*/
-- 第一步：查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;
-- 第二步：查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (${第一步})
-- 结果：
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)
/*
子查询作为条件
子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）
*/

/*
3.查询工作和工资与MARTIN完全相同的员工信息
分析：
查询条件：工作和工资与MARTIN完全相同，这是子查询
*/
-- 第一步：查询出MARTIN的工作和工资
SELECT job,sal FROM emp WHERE ename='MARTIN'
-- 第二步：查询出与MARTIN工作和工资相同的人
SELECT * FROM emp WHERE (job,sal) = (${第一步})
-- 结果：
SELECT * FROM emp WHERE (job,sal) = (SELECT job,sal FROM emp WHERE ename='MARTIN')
/*
子查询作为条件
子查询形式为单行多列
*/
-- 4.EMP表中，找出每个雇员超过他所在部门平均工资的雇员编号、雇员名称、薪水、部门编号
select e.deptno,e.ename,e.sal 
from emp e 
join (select deptno,avg(sal) as avg_sal 
      from emp 
      group by deptno) d 
on e.deptno = d.deptno 
where e.sal > d.avg_sal 
order by e.deptno;
/*
子查询作为表
子查询形式为多行多列
*/

子查询在SELECT、UPDATE、DELETE语句内部可以出现SELECT语句。内部的SELECT语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。
子查询执行流程：先执行的是条件中的sql，如果子查询未返回任何行,则主查询也不会返回任何结果

9.3、exists关键字

语法

select t1.*
from 表1 t1
where exists(select 1/null   -- 此处字段内容不展示，可以写成1或者null
            from 表2 t2
            where t1.字段1 = t2.字段2  -- 表1和表2的连接条件
            );

exists运行原理：

EXISTS 子查询用于判断是否存在满足特定条件的数据行。它的运行原理是：对于主查询（外层查询）中的每一行，都会执行一次子查询。如果子查询返回至少一行结果，那么主查询中的当前行就被包括在结果中。如果子查询返回空结果集，那么主查询中的当前行就不会包括在结果中。
EXISTS 子查询在找到第一个匹配项后就可以立即停止执行，这使得它在处理大量数据时更高效。

in 运行规则：

先运行子查询中的数据得到结果集，外层查询中的该条数据会依次和结果集中的所有数据进行匹配，
找到值相等的数据，相比于exists其运行效率更慢（在处理大量数据时才比较明显）

-- exists 和 in
-- 查询有员工的部门部门信息
SELECT d.*
FROM   dept d
WHERE  EXISTS (SELECT 1
        FROM   emp e
        WHERE  d.deptno = e.deptno);
        
SELECT d.*
FROM   dept d
WHERE  d.deptno IN (SELECT deptno
                    FROM   emp);
-- SQL优化：在工作中使用exists查询替换in查询效率更高

9.4、联合查询（集合运算）

查询特点：联合查询就是把两个select语句的查询结果集上下合并到一起
联合查询方式:
- Union: 对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
- Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
- Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
- Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

SELECT deptno FROM emp 
UNION
SELECT deptno FROM dept;
-- 查询结果: 10,20,30,40

SELECT deptno FROM emp 
UNION ALL
SELECT deptno FROM dept;

SELECT deptno FROM emp 
INTERSECT
SELECT deptno FROM dept;  
-- 结果: 10,20,30

SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
-- 结果:40

10、伪列

10.1 什么是伪列？

伪列并不是表中真实存在的列，是系统为每一张表单独赋予的列

10.2 ROWID

ROWID特点：

字段的数据类型是字符类型，存储的数据表示该行数据位于硬盘中的物理地址。系统更加ROWID的值快速找到数据在硬盘中的位置，从而加快查找数据的效率。
每一条数据都有ROWID的值，当数据插入表中时，就会生成ROWID的值。每一条数据ROWID的值都是独一无二的，如果两条数据的ROWID相同，说明它们是同一个数据来源

ROWID作用：

一般用来删除表中重复的数据（默认情况下表示所有字段的值都相同的数据是重复数据）。删除重复数据要求保留重复数据中的一条，其余都删除。

/*
步骤一：根据表中所有字段进行分组，选取每一组中的最大或最小rowid
      查询结果是不重复的数据
      select max(rowid)
      from 表名
      group by 所有字段;

步骤二：删除不在上述查询结果中的数据
      delete from 表名
      where rowid not in(select max(rowid)
                        from 表名
                        group by 所有字段);
如果要保留最早的数据，使用min(rowid)，
如果有保留最新的数据，使用max(rowid)
*/
-- 步骤一：
SELECT MAX(ROWID)
FROM   emp e
GROUP  BY e.empno,
          e.ename,
          e.job,
          e.mgr,
          e.hiredate,
          e.sal,
          e.comm,
          e.deptno;
          
-- 步骤二：
DELETE FROM emp
WHERE  ROWID NOT IN (SELECT MIN(ROWID)
                     FROM   emp e
                     GROUP  BY e2.empno,
                               e2.ename,
                               e2.job,
                               e2.mgr,
                               e2.hiredate,
                               e2.sal,
                               e2.comm,
                               e2.deptno);
-- 使用not exists 实现
DELETE FROM emp e1
WHERE  NOT EXISTS (SELECT 1
        FROM   emp e2
        GROUP  BY e2.empno,
                  e2.ename,
                  e2.job,
                  e2.mgr,
                  e2.hiredate,
                  e2.sal,
                  e2.comm,
                  e2.deptno
        HAVING e1.rowid = MIN(e2.rowid));

-- 方式三
DELETE
FROM
    emp e1
WHERE
        rowid > (SELECT
                     min(rowid)
                 FROM
                     emp e2
                 WHERE
                       e1.empno = e2.empno
                   AND e1.ename = e2.ename
                   AND e1.job = e2.job
                   AND e1.mgr = e2.mgr
                   AND e1.hiredate = e2.hiredate
                   AND e1.sal = e2.sal
                   AND e1.comm = e2.comm
                   AND e1.deptno = e2.deptno);

10.2 ROWNUM

ROWNUM与ROWID不同，ROWID是插入记录时生成，ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。

-- 第一种:两层sql嵌套实现分页:
-- 每页显示3条记录,查询第一页记录
select * from (select rownum r,t.* from emp t where rownum <=3 ) where  r > 0;
-- 每页显示3条，查第二页记录：
select * from (select rownum r,t.* from emp t where rownum <= 6 ) where  r > 3;

-- 第二种:三层sql嵌套实现分页
-- 每页显示3条，查第一页记录：
select * from (select rownum r,t.* from  (select * from emp) t where rownum <= 3) where r > 0;
--每页显示3条，查第二页记录：
select * from (select rownum r,t.* from  (select * from emp) t where rownum<=6) where r>3;

11、系统函数

11.1 字符函数

/*
  3.1 伪表
    系统表 dual，也称为万能表，其默认结构是单行单列的
*/
-- 1.length(str)：计算字符串或字段的值的长度，函数结果是数字
-- 查看emp表中每个员工名字的长度，只显示名字长度大于5的员工
SELECT ename,LENGTH(ename)
FROM emp
WHERE LENGTH(ename) > 5;

-- 计算字符串 'hello wolrd' 的长度
SELECT LENGTH('hello wolrd') FROM dual;

-- 2.lower(str)：将字符串的字母转换成小写，函数结果是字符串
-- 将员工姓名转出小写字母
SELECT LOWER(ename)
FROM emp;

-- 3.upper(str)：将字符串的字母转换成大写，函数结果是字符串
SELECT UPPER(LOWER(ename))  -- 函数嵌套
FROM emp;

-- 4.replace(str1,str2,str3)：将str1中str2部分字符串，替换成str3字符串
-- 将字符串 'like cat,my cat,hello cat'，中的cat替换成dog
SELECT REPLACE('like cat,my cat,hello cat','cat','dog')
FROM dual;

-- 5.substr(str,position)：截取str字符串中的内容，从 position（写一个数字） 位置开始截取，截取到字符串结尾
-- 截取 'hello wolrd'中的内容，从位置4开始截取，一直到结尾
SELECT SUBSTR('hello wolrd',4) FROM dual;
-- substr(str,position,length)：截取str字符串中的内容，从 position 位置开始截取，截取length长度内容
-- 截取 'hello wolrd'中的内容，从位置4开始截取，截取长度为5的内容
SELECT SUBSTR('hello wolrd',4,5) FROM dual;

-- 6.instr(str1,str2)：搜索str1中str2首次出现的位置，默认从位置1开始搜索
-- 查询 'hello wolrd'中，字母 o 首次出现的位置
SELECT INSTR('hello wolrd','o') FROM dual;

-- instr(str1,str2,position)：搜索str1中str2首次出现的位置，从position位置开始搜索
-- 查询 'hello wolrd'中，字母 o 首次出现的位置，从位置6开始搜索
SELECT INSTR('hello wolrd','o',6) FROM dual;

ltrim()

--左截 ltrim
SELECT LTRIM('     hello   ddd    ')  FROM dual;

rtrim()

--右截 rtrim
SELECT RTRIM('     hello   ddd    ')  FROM dual;

trim()

--左右截 trim（c1 from c2）
--去掉前后空格
SELECT TRIM('     hello   ddd    ')  FROM dual;
--abbcca 把左右的a截取
SELECT TRIM('a' FROM 'abbacca')  "xx"  FROM dual;

concat() ||

--连接 concat    ||  oracle的连接 
SELECT CONCAT('hello','world') FROM dual;
SELECT 'hello' || 'world' || 'xx' FROM dual;

lpad()：左填充函数

-- 将ename字段的长度填充为6，不足部分使用'$'符号从最左边填充
select lpad(ename,6,'$') from emp;

rpad()：右填充函数

-- 将ename字段的长度填充为6，不足部分使用'$'符号从最右边填充
select rpad(ename,6,'$') from emp;

11.2 数字函数

-- 1.round(num1,num2)：对num1进行四舍五入，保留num2位小数
SELECT ROUND(1234.567) FROM dual;  -- 四舍五入取整

SELECT ROUND(1234.567,2) FROM dual; -- 四舍五入保留两位小数

-- 2.floor(num)：对数字向下取整
SELECT FLOOR(1234.567) FROM dual;
SELECT FLOOR(-1234.567) FROM dual;

-- 3.ceil(num)：对数字向上取整
SELECT ceil(1234.567) FROM dual;
SELECT CEIL(-1234.567) FROM dual;

-- 4.mod(x,y)：获取x除以y的余数
SELECT MOD(7,3) FROM dual;

-- 5.power(x,y)：获取x的y次幂
SELECT POWER(4,3) FROM dual;

-- 6.abs(num)：获取num的绝对值
SELECT ABS(-10) FROM dual;

-- 7.sqrt(x)：对x进行开方

-- ASCII码是一套基于拉丁字母的字符编码，共收录了 128 个字符，用一个字节就可以存储，它等同于国际标准 ISO/IEC 646。
-- ASCII(str) : 输入一个字符,返回字符所在的ASCII码值
SELECT ASCII('''') FROM dual; 
-- chr(num) : 输入一个ASCII码值(0-127),返回该码值所对应的字符
SELECT CHR(39) FROM dual;

-- dbms_random.value(num1,num2) : 获取num1到num2之间(取值不到num2)的一个随机数,随机数含有小数
SELECT dbms_random.value(1,10) FROM dual;

-- 获取 1到 10之间的随机整数
SELECT FLOOR(dbms_random.value(1,11)) FROM dual;
SELECT CEIL(dbms_random.value(0,10)) FROM dual;

/*
9.随机数
  在Oracle中想要使用随机数，需要使用其提供的随机数工具包
  dbms_random.value 它的值默认是一个0~1之间的随机数，值是一个小数
  dbms_random.normal 是一个正态分布的随机数，值是一个小数
                     -1 ~ 1 之间数的概率是 0.6826
                     -2 ~ 2 之间数的概率是 0.9544
  dbms_random.random 是一个随机整数，其区间范围是 -2^31 ~ 2^31-1                   

*/
-- 获取一个1到10之间的随机数，但是包含小数点
SELECT dbms_random.value(1,10) FROM dual;
-- 获取一个1到10之间的随机整数，
SELECT FLOOR(dbms_random.value(1, 11))
FROM   dual;

SELECT CEIL(dbms_random.value(0, 10))
FROM   dual;

-- 获取的随机数概率不相等1和10
SELECT ROUND(dbms_random.value(1, 10))
FROM   dual;

-- 正态分布随机数
SELECT dbms_random.normal FROM dual;

-- 随机整数
SELECT dbms_random.random FROM dual;

-- 获取一个随机大写字母
SELECT CHR(FLOOR(dbms_random.value(65, 91))) FROM dual;

-- 随机大写字母
SELECT dbms_random.string('u',1) FROM dual;

-- 随机小写字母
SELECT dbms_random.string('l',1) FROM dual;

-- 随机大小写字母
SELECT dbms_random.string('a',1) FROM dual;

-- 随机大写字母和数字的组合
SELECT dbms_random.string('x',5) FROM dual;

-- 随机大小字母，数字和符号
SELECT dbms_random.string('p',10) FROM dual;

11.3 日期函数

/*
1.如何表示一个日期
to_date(str,format)
  format: y 表示年，m 表示月，d 表示天，hh 表示小时（12进制）
          hh24 表示小时（24进制），mi 表示分组，s 表示秒
          
date 'yyyy-mm-dd' 只能精确到年月日          
*/
-- 表示日期 2023-11-20 13:20:30
SELECT to_date('2023-11-20 13:20:30','yyyy-mm-dd hh24:mi:ss') - 1
FROM dual;

SELECT to_date('20231120 13:20:30','yyyy-mm-dd hh24:mi:ss') - 1
FROM dual;

SELECT DATE '2023-11-20' - 1
FROM dual;

-- 2.获取系统当前时间  SYSDATE, 默认格式是年月日时分秒
SELECT SYSDATE FROM dual;

-- 3.日期之间的运算
-- 日期 - 日期 = 数字 ，该数字表示两个日期之间间隔多少天
SELECT DATE '2022-10-20' - DATE '2022-10-10' FROM dual;

SELECT to_date('2022-10-20 11:30:30', 'yyyy-mm-dd hh24:mi:ss') -
       to_date('2022-10-10 10:20:30', 'yyyy-mm-dd hh24:mi:ss')
FROM   dual;

-- 日期 + 日期 ,不允许
SELECT to_date('2022-10-20 11:30:30', 'yyyy-mm-dd hh24:mi:ss') +
       to_date('2022-10-10 10:20:30', 'yyyy-mm-dd hh24:mi:ss')
FROM   dual;

-- 日期 +/- 数字 = 日期，给日期增加或减少指定天数
SELECT to_date('20231120 13:20:30','yyyy-mm-dd hh24:mi:ss') - 1
FROM dual;

-- 4.add_months(date,num)：给指定日期增加或减少num月
SELECT add_months(DATE '2022-10-20',-10) FROM dual;

-- 5.months_between(date1,date2)：获取两个日期之间间隔多少月
SELECT months_between(DATE '2022-10-20',DATE '2023-09-20')
FROM dual;

-- 6.last_day(date)：获取日期所在当月最后一天
SELECT last_day(SYSDATE) FROM dual;

-- 7.next_day(date,'星期几')：获取日期之后的下一个星期几的日期，不包含当前日期
SELECT next_day(SYSDATE,'星期二') FROM dual;

-- 8.extrat(day|month|year from 日期)：其中day，month，year只能填写其中的一个，分别可以获取日期的日 月 年
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;


/* TRUNC函数用于对值进行截断。
用法有两种：TRUNC（NUMBER）表示截断数字，TRUNC（date）表示截断日期。

(1)截断数字：
格式：TRUNC（n1,n2），n1表示被截断的数字，n2表示要截断到那一位。n2可以是负数，表示截断小数点前。注意，TRUNC截断不是四舍五入。
*/
select TRUNC(15.792) from dual;
select TRUNC(15.792,1) from dual;
select trunc(15.792,-1) from dual;

-- (2)截断日期：
select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18
select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.
select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天
select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日
select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天
select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41  
select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确
select trunc(sysdate, 'iw') from dual; --本周第二天(周日为第一天)
--练习 
-- 1.查询去年本月的最后一天的日期

-- 2.计算本月还剩几天

课堂练习

-- 1.查询emp表中入职时间在1年之内的员工分别是哪些配对
SELECT e1.ename AS ename1, e2.ename AS ename2
FROM   emp e1 -- 第一个员工
JOIN   emp e2 -- 第二个员工
ON     ABS(months_between(e1.hiredate, e2.hiredate)) <= 12
AND e1.empno != e2.empno;

-- 2.查询员工入职时，入职当月还剩多少天
SELECT ename, last_day(hiredate) - hiredate
FROM emp

-- 3.查询12月份入职的员工有几人
SELECT COUNT(1)
FROM emp
WHERE EXTRACT(MONTH FROM hiredate) = 12;

-- 4.查询每个员工工作到现在的工龄有多少年，向下取整
SELECT ename,FLOOR(months_BETWEEN(SYSDATE,hiredate)/12)
FROM emp;

-- 附加题
-- 5.查询每个员工入职所在当月有几个人入职
-- 不仅要获取员工入职的月份，还要获取其对应的年份
SELECT EXTRACT(YEAR FROM hiredate), EXTRACT(MONTH FROM hiredate), COUNT(1) AS cnt
FROM   emp
GROUP  BY EXTRACT(YEAR FROM hiredate), EXTRACT(MONTH FROM hiredate);

SELECT e.*, t.cnt
FROM   emp e
JOIN   (SELECT EXTRACT(YEAR FROM hiredate) AS h_year,
               EXTRACT(MONTH FROM hiredate) AS h_month,
               COUNT(1) AS cnt
        FROM   emp
        GROUP  BY EXTRACT(YEAR FROM hiredate), EXTRACT(MONTH FROM hiredate)) t
ON     EXTRACT(YEAR FROM e.hiredate) = t.h_year
AND    EXTRACT(MONTH FROM e.hiredate) = t.h_month;

11.4 转换函数

-- to_date(str,format)

/*
to_number(str,format)
	str：一个含义数字的字符串
	format：
		9：代表一个数字
		0：强迫0显示
		$：显示美元符号
		L：强制显示一个当地的货币符号
		.：显示一个小数点
		,：显示一个千位分隔符号
*/

--转换格式的字符串  to_char(值，格式)
-- 12345678.212   --> $12,345,678.21
--9 任意一个数字
--  $12,345,678.21
SELECT to_char(12345678.212,'$999,999,999,999.99') FROM dual;
-- 0 任意一个数字
SELECT to_char(12345678.212,'$000,000,000,000.00') FROM dual;
/*
to_char()
转换数字
to_char(num,format)：将数字按照指定格式转换成字符串
  format
    0或9：可以表示任意一个位置（个位，十位，百位等）数字
    ,：表示一个千分号
    .：表示一个小数点
    $：表示美元符号
    L：表示本地货币符号，国内是人民币 

转换日期 ，结果是一个字符串
to_char(date,format)
  format：和 to_date 格式一致  
      y 表示年，m 表示月，d 表示天，hh 表示小时（12进制）
      hh24 表示小时（24进制），mi 表示分组，s 表示秒
*/
SELECT to_char(SYSDATE,'yyyy') FROM dual; -- 获取四位数年份
SELECT to_char(SYSDATE,'yyy') FROM dual;  -- 获取年份后三位
SELECT to_char(SYSDATE,'yy') FROM dual;  -- 获取年份后两位
SELECT to_char(SYSDATE,'y') FROM dual;  -- 获取年份后一位

SELECT to_char(SYSDATE,'mm') FROM dual;  -- 获取月份
SELECT to_char(SYSDATE,'d') FROM dual;  -- 获取日期是本周多少天，周日为第一天
SELECT to_char(SYSDATE,'dd') FROM dual;  -- 获取日期是当月多少天
SELECT to_char(SYSDATE,'ddd') FROM dual;  -- 获取日期是当年多少天
SELECT to_char(SYSDATE,'day') FROM dual;  -- 获取日期当天是星期几
SELECT to_char(SYSDATE,'w') FROM dual;  -- 获取日期当天是本月第几周，一个月的1号到7号为第一周
SELECT to_char(SYSDATE,'ww') FROM dual;  -- 获取日期当天是本年第几周，一年的1月1号到7号为第一周

SELECT to_char(SYSDATE,'hh') FROM dual;  -- 获取日期的小时，12进制
SELECT to_char(SYSDATE,'hh24') FROM dual;  -- 获取日期的小时，24进制
SELECT to_char(SYSDATE,'mi') FROM dual;  -- 获取日期的分钟
SELECT to_char(SYSDATE,'ss') FROM dual;  -- 获取日期的秒钟

SELECT to_char(SYSDATE,'q') FROM dual;  -- 获取日期是第几季度

课堂练习

-- 1.查询员工入职时，入职当月还剩多少天
SELECT ename, last_day(hiredate) - hiredate
FROM emp

-- 2.查询12月份入职的员工有几人
SELECT COUNT(1)
FROM emp
WHERE EXTRACT(MONTH FROM hiredate) = 12;

-- 3.查询每个员工工作到现在的工龄有多少，向下取整
SELECT ename,FLOOR(months_BETWEEN(SYSDATE,hiredate)/12)
FROM emp;

-- 附加题
-- 5.查询每个员工入职所在当月有几个人入职
-- 不仅要获取员工入职的月份，还要获取其对应的年份
SELECT e.*, t.cnt
FROM   emp e
JOIN   (SELECT EXTRACT(YEAR FROM hiredate) AS h_year,
               EXTRACT(MONTH FROM hiredate) AS h_month,
               COUNT(1) AS cnt
        FROM   emp
        GROUP  BY EXTRACT(YEAR FROM hiredate), EXTRACT(MONTH FROM hiredate)) t
ON     EXTRACT(YEAR FROM e.hiredate) = t.h_year
AND    EXTRACT(MONTH FROM e.hiredate) = t.h_month;

11.5 空值转换函数

-- nvl(expr1,expr2) expr1：字段  expr2：字段为空值时赋值为expr2

-- nvl2(expr1,expr2,expr3) expr1：字段  expr2：如果expr1不为空设置为expr2  expr3：如果expr1为空设置为expr3

-- coalesce(字段1,字段2,字段3,...) : 返回字段中首个不为空值的字段

函数大全： https://www.cnblogs.com/ghostwolf1/p/14870031.html

11.6 分支函数

case…when…

/*
1.case ... when 函数
等值比较
语法
case 字段名 
  when 值1 then
    value1
  when 值2 then
    value2
  ...
  [else
    valueN]
end 
    
含义：
  case when 函数值和字段的值密切相关，
  当 字段的值等于值1时，case when 函数的值是value1，
  当 字段的值等于值2时，case when 函数的值是value2，
  以此类推
  如果字段的值和以上值都不相同，case when函数的值是valueN    
*/
-- 给emp表中每个部门单独命名，10部门命名为总裁室，20号部门命名为销售部，30号部门命名为研发部
SELECT ename,deptno,
       CASE deptno
         WHEN 10 THEN
           '总裁室'
         WHEN 20 THEN 
           '销售部'  
         WHEN 30 THEN
           '研发部'
       END  AS dname     
FROM emp;

-- 在聚合函数中嵌套分支函数进行查询
-- 查询每个部门的人数，要求结果是一行
SELECT COUNT(CASE deptno WHEN 10 THEN 1 END) AS 部门10的人数,
       COUNT(CASE deptno WHEN 20 THEN 1 END) AS 部门20的人数,
    COUNT(CASE deptno WHEN 30 THEN 1 END) AS 部门30的人数
FROM emp;

/*      
不等值比较
语法
case 
  when 条件1 then
    value1
  when 条件2 then
    value2
  ...
  else
    valueN
end
    
    含义：
     从上往下依次进行条件判断
     当条件1的值为true，则case when函数的值是value1，同时结束后续判断；
     当条件1的值为false，再进行条件2的判断，如果条件2的值为true，则case when函数的值是value2，同时结束后续判断；
     以此类推
     如果以上条件的值都为false，case when 函数的值是valueN   
*/
-- 判断员工的收入情况，如果工资低于1500为低收入，工资大于等于1500，小于3000为中等收入，工资大于等于3000为高收入
SELECT ename,sal,
       CASE 
         WHEN sal < 1500 THEN
           '低收入'
         WHEN sal < 3000 THEN 
           '中等收入'  
         ELSE
           '高收入'
       END AS 收入情况      
FROM emp;

-- 使用分支函数查询每个部门工资分别在1500以下，大于等于1500并且小于3000 和工资大于等于3000员工的数量
SELECT deptno,
       COUNT(CASE WHEN sal < 1500 THEN 1 END) AS 小于1500,
       COUNT(CASE WHEN sal < 3000 AND sal >= 1500 THEN 1 END) AS 大于等于1500小于3000,
       COUNT(CASE WHEN sal >= 3000 THEN 1 END) AS 大于3000
FROM emp
GROUP BY deptno;

decode()函数

/*
特点：decode只能进行等值比较
语法
1.decode(字段名,值1,值2,值3)
  含义：
   当字段的值等于值1时，decode函数的值等于值2，否则等于值3

2.decode(字段名,值1,value1,
                值2,value2,
                ...
                valueN)
  含义：和case when等值比较相似
    当 字段的值等于值1时，decode 函数的值是value1，
    当 字段的值等于值2时，decode 函数的值是value2，
    以此类推
    如果字段的值和以上值都不相同，decode函数的值是valueN    
*/
-- 把20号部门撤销，原来20号部门的员工划到10号部门，其余不变
SELECT * FROM emp;
SELECT ename,deptno,
       DECODE(deptno,20,10,deptno) new_deptno
FROM emp;  
-- 练习
-- 给emp表中每个部门单独命名，10部门命名为总裁室，20号部门命名为销售部，30号部门命名为研发部
-- 查询每个部门的人数，要求结果是一行

注意：当一条SQL语句使用decode函数和case when 函数都能实现时，优先使用内置函数decode查询效率更高

12、开窗函数（Window Function）

12.1 开窗函数基本概念

开窗函数也称为分析函数，专门用来进行统计分析操作的函数

什么是开窗？
在原本表中额外新增一个窗口字段，窗口里面就是分析，统计后的数据。
开窗函数在使用后不会影响表中的数据量。

12.2 函数格式

函数名([参数]) over ([partition by 分区子句] [order by 排序子句]))

函数部分窗口函数部分
上述两个部分不能分割，必须作为一个整体进行看待和使用

函数部分：函数部分支持多种函数类型，取实现不同的功能
窗口函数部分比较固定 over([partition by <分区字段>] [order by <排序字段>])

如何理解分区和分组，partition by 和 group by ?
分区和分组的操作是互相独立进行的，互不干扰，相当于两个平行宇宙

12.3 支持的函数

聚合函数：max()/min()/count()/sum()/avg()

-- 使用开窗函数计算员工工资的总和
SELECT ename,sal,
       SUM(sal) OVER() sum_sal
FROM emp; 

SELECT SUM(sal)      
FROM emp;

-- 使用开窗函数计算员工所在部门工资的总和
SELECT ename,sal,
       SUM(sal) OVER(PARTITION BY deptno) sum_sal
FROM emp; 

SELECT deptno,SUM(sal)  
FROM emp
GROUP BY deptno;

SELECT t1.ename, t1.deptno, t2.sum_sal
FROM   emp t1
LEFT   JOIN (SELECT deptno, SUM(sal) sum_sal
             FROM   emp
             GROUP  BY deptno) t2
ON     NVL(t1.deptno, 0) = NVL(t2.deptno, 0);

-- 查询每一个部门的工资总和
SELECT deptno,SUM(sal)  
FROM emp
GROUP BY deptno;

SELECT DISTINCT deptno,
       SUM(sal) OVER(PARTITION BY deptno) sum_sal
FROM emp; 


CREATE TABLE sale(
       s_year NUMBER,
       s_month NUMBER,
       amount NUMBER
);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 1, 1234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 2, 2234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 3, 3234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 4, 4234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 5, 5234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 6, 6234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 7, 7234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 8, 8234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 9, 9234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 10, 10234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 11, 11234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2021, 12, 12234);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 1, 1000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 2, 2000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 3, 3000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 4, 4000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 5, 5000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 6, 6000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 7, 7000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 8, 8000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 9, 9000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 10, 10000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 11, 11000);
insert into sale (S_YEAR, S_MONTH, AMOUNT)
values (2022, 12, 12000);

SELECT * FROM sale;

-- 计算 2022年上半年的销售总额
SELECT s_year,SUM(amount)
FROM sale
WHERE s_year = 2022
AND s_month BETWEEN 1 AND 6
GROUP BY s_year;

-- 计算 2022年上半年的销售累计总额
SELECT s_year,s_month,
       SUM(amount) OVER(ORDER BY s_month ASC) 累计
FROM sale
WHERE s_year = 2022
AND s_month BETWEEN 1 AND 6

/*
开窗函数中 order by 的作用:
  1.会对查询后的结果进行一次排序
  2.如果开窗函数的函数部分是sum()函数,则可以进行累计求和
  
如何区分使用开窗函数和聚合函数
  1.开窗函数在使用后,不会改变查询后的数据量;
    聚合函数在使用后,会对查询结果进行聚合,改变查询后的数据量
    
  2.如果select后既有字段,又有聚合函数,那么这些字段要进行分组;
    如果select后既有字段,又有开窗函数,那么这些字段不需要进行分区   
    
  3.如果需要进行累计求和,则必须使用开窗函数  
  
  4.聚合函数在group by之后生效, 可以在 select having 和 order by 中使用,
    开窗函数在 select之后生效, 只能在select中使用,如果其他位置要使用只能写子查询
*/

课堂练习：

-- 分别使用开窗函数和聚合函数查询每个部门工资最高员工的员工信息
-- 聚合函数
SELECT *
FROM EMP
WHERE (DEPTNO,SAL) IN(SELECT DEPTNO,MAX(SAL)
                      FROM EMP
                      GROUP BY DEPTNO);
                      
-- 开窗函数
SELECT T.*
FROM (SELECT E.*,
      		 MAX(SAL) OVER(PARTITION BY DEPTNO) AS MAX_SAL
     FROM EMP E) T
WHERE T.SAL = T.MAX_SAL;

排名函数
```
/*
格式：row_number的语法
row_number()  over (partition by column1  order by column2)
表示根据column1进行分组，在分组内部根据column2排序，此函数计算的值表示每组内部排序后的顺序编号，组内编号连续
*/
```
1. row_number()：它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。 1 2 3 4
2. rank()：不会进行连续排名，如果遇到相同的名次则会跳过后续的名次，可以保证总名次等于总人数。 1 2 2 4
3. dense_rank()：会进行连续排名，如果遇到相同的值会显示相同的名次，但是不能保证总名次等于总人数。 1 2 2 3

-- 根据员工工资从高到低排序
SELECT ename,
      sal,
      row_number() OVER(ORDER BY sal DESC) rn,
      RANK() OVER(ORDER BY sal DESC) ranking,
      dense_rank() OVER(ORDER BY sal DESC) dr
FROM   emp;

-- 排序函数可以实现分区排序
-- 查询每个部门员工的工资排名
SELECT ename,sal,deptno,
      row_number() OVER(PARTITION BY deptno ORDER BY sal DESC) rn
FROM emp;  

-- 获取每个部门中工资排名前3的用户名     
SELECT ename,sal,deptno,
      row_number() OVER(PARTITION BY deptno ORDER BY sal DESC) rn
FROM emp;

SELECT t.ename
FROM (SELECT ename,sal,deptno,
      row_number() OVER(PARTITION BY deptno ORDER BY sal DESC) rn
FROM emp) t
WHERE t.rn <= 3;

移动函数（求同比和环比）
1. lag(字段,n)：将指定的字段的值下移n行
2. lead(字段,n)：将指定的字段的值上移n行
  
  格式：lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。lag() 相同

/*
注意：
 1.在使用移动函数时，窗体函数中一定要使用order by 进行排序
 2.移动函数一般用计算同比和环比，同比增长率和环比增长率

   同比增长率 = (本月的数据 - 去年同期数据) / 去年同期数据
   环比增长率 = (本月的数据 - 上个月的数据) / 上个月的数据

   计算环比相关数据，按照时间升序排序，再将数据向下移动1行
   计算同比相关数据，按照时间升序排序，再将数据向下移动12行
*/
-- 分别计算2022年数据的同比和环比，同比增长率和环比增长率
SELECT t.s_year,
       t.s_month,
       t.amount,
       (t.amount - t.tongbi) / t.tongbi 同比增长率,
       (t.amount - t.huanbi) / t.huanbi 环比增长率
FROM   (SELECT s_year,
               s_month,
               amount,
               LAG(amount, 1) OVER(ORDER BY s_year ASC, s_month ASC) huanbi,
               LAG(amount, 12) OVER(ORDER BY s_year ASC, s_month ASC) tongbi
        FROM   sale) t
WHERE  t.s_year = 2022;

-- 表结构
CREATE TABLE tb_test(
    id VARCHAR2(64) NOT NULL,		-- 记录id
    cphm VARCHAR2(10) NOT NULL,  -- 汽车牌号
    create_date DATE NOT NULL,		-- 创建时间
    primary key (id)
);
-- 初始化数据
insert into tb_test values ('1000001', 'AB7477', to_date('2015-11-30 10:18:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000002', 'AB7477', to_date('2015-11-30 10:22:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000003', 'AB7477', to_date('2015-11-30 10:28:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000004', 'AB7477', to_date('2015-11-30 10:29:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000005', 'AB7477', to_date('2015-11-30 10:39:13','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000006', 'AB7477', to_date('2015-11-30 10:45:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000007', 'AB7477', to_date('2015-11-30 10:56:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000008', 'AB7477', to_date('2015-11-30 10:57:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000009', 'AB3808', to_date('2015-11-30 11:00:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000010', 'AB3808', to_date('2015-11-30 11:10:13','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000011', 'AB3808', to_date('2015-11-30 11:15:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000012', 'AB3808', to_date('2015-11-30 11:26:12','YYYY-MM-DD HH24:mi:ss'));
insert into tb_test values ('1000013', 'AB3808', to_date('2015-11-30 11:30:12','YYYY-MM-DD HH24:mi:ss'));

-- a、获取当前记录的id，以及下一条记录的id 
select t.id id ,
lead(t.id, 1, null) over (order by t.id) next_record_id, 	
t.cphm 
from tb_test t       
order by t.id asc;

-- b、获取当前记录的id，以及上一条记录的id
select t.id id ,
lag(t.id, 1, null) over (order by t.id)  next_record_id, 	
t.cphm 
from tb_test t       
order by t.id asc;

-- c、获取号牌号码相同的，当前记录的id与，下一条记录的id（使用partition by）
select t.id id, 
lead(t.id, 1, null) over(partition by cphm order by t.id) next_same_cphm_id, 
t.cphm 
from tb_test t
order by t.id asc;

-- d、查询 cphm的记录数，当create_date与下一条记录的create_date时间间隔不超过10分钟则忽略。
SELECT
cphm,
count( 1 ) total 
FROM
(SELECT
    t.id,
    t.create_date t1,
    lead ( t.create_date, 1, NULL ) over ( partition BY cphm ORDER BY create_date ASC ) t2,
    ( lead ( t.create_date, 1, NULL ) over ( partition BY cphm ORDER BY create_date ASC ) - t.create_date ) * 86400 AS itvtime,
    t.cphm 
FROM
    tb_test t 
ORDER BY
    t.cphm,
    t.create_date ASC 
) tt 
WHERE
tt.itvtime >= 600 
OR tt.itvtime IS NULL 
GROUP BY
tt.cphm;

窗口子句不能单独出现，必须要有排序子句出现的情况下才能指定窗口范围
若出现排序子句，同时未指定窗口范围，默认的窗口范围是第一行到当前行；若未出现排序子句，同时未指定窗口范围，默认的窗口范围是第一行到最后一行
PARTITION BY 分组的范围 ROWS 统计分析的范围分析范围不会超过分组范围

计算占比函数：ratio_to_report()

/*
ratio_to_report(字段名) over() 每条数据的该字段占字段总和的占比
ratio_to_report(字段名1) over(partition by 字段名2)：根据字段2分区后，每条数据的字段1占每组中字段1总和的占比
*/
select ename,sal,
   ROUND(ratio_to_report(sal) over()*100,2) "all%",
   ROUND(ratio_to_report(sal) over(partition by deptno)*100,2) "each%"
from emp;

切片函数: ntile(n): 将数据划分成n个等级

/*
ntile(n) : 将查询结果分成 n 个等级
注意：1.在使用切片函数时，窗体函数中一定要使用order by 进行排序
     2.如果数据量除以等级能够除尽，那么每个等级中的数据量相同
     3.如果数据量除以等级不能够除尽，那么前面的等级多一条数据   
*/
SELECT ename,sal,
       NTILE(6) OVER(ORDER BY sal DESC) AS lv
FROM emp;

/*
使用场景：比如用来查询按照日期排序的前三分之一的数据
*/

12.3 开窗函数的定位框架

在使用了order by 的时候可以设置定位框架，语法：

row_number()  over (partition by 分组  order by 排序字段  [range|rows] between 边界规则1 and 边界规则2)

边界规则，按照值从小到大排序：

unbounded preceding：向上一直到第一行
n preceding:向上n行
current row:当前行
n following：向下n行
unbounded following:向下一直到最后一行

-- 查询从第一行到当前行的工资总和：
SELECT ename,sal,
       SUM(sal) OVER(ORDER BY sal ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_row
FROM emp;  
-- 表示按照sal进行排序，然后计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的和，这样的计算结果就是按照工资进行排序的工资值的累积和。

-- 把例子程序一的row换成了range，是按照范围进行定位的
select ename,sal,
       SUM(sal) OVER(ORDER BY sal ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_row
from emp
/*这个SQL语句与例1中的SQL语句唯一不同的就是“ROWS”被替换成了“RANGE”。“ROWS”
是按照行数进行范围定位的，而“RANGE”则是按照值范围进行定位的，这两个不同的定位方式
主要用来处理并列排序的情况。比如 MARTIN,WARD这两个人的工资都是1250元，如果按照
“ROWS”进行范围定位，则计算从第一条到当前行的累积和，而如果 如果按照 “RANGE”进行
范围定位，则仍然计算从第一条到当前行的累积和，不过由于等于1250元的工资有两个人，所
以计算的累积和为从第一条到1250元工资的人员结，所以对 MARTIN,WARD这两个人进行开
窗函数聚合计算的时候得到的都是4400（ “ 800+1100+1250+1250 ”）。 */

ROWS BETWEEN N PRECEDING AND N FOLLOWING  --在某一行的前N行和之后的N行
ROWS BETWEEN CURRENT ROW AND N FOLLOWING  --当前行到之后的N行
ROWS BETWEEN N PRECEDING AND CURRENT ROW  --从前N到当前行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ---第一行至当前行的一个汇总
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  --当前行到最后
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  --所有行
ROWS BETWEEN UNBOUNDED PRECEDING AND N FOLLOWING  --第一行到之后的N行
ROWS BETWEEN N PRECEDING AND UNBOUNDED FOLLOWING  --之前的N行到最后

13、行列转换

13.1 listagg() 函数

含义：将指定的列中的数据拼接成一行数据

语法： listagg(字段,‘分割符’) within group (order by 排序字段排序方式)

-- 查询每个部门的员工姓名,要求员工姓名在一行显示
SELECT deptno,
       listagg(ename,',') WITHIN GROUP (ORDER BY deptno) names
FROM emp
GROUP BY deptno; 

SELECT deptno,
       to_char(wm_concat(ename))
FROM emp
GROUP BY deptno;

注意：

listagg() 只能在select后使用
listagg() 的使用方式和聚合函数一致,当字段和listagg() 一起出现时,字段要进行分组
函数 wm_concat(字段) 可以实现相似功能,使用方式和聚合函数一致
区别在于
listagg() : 可以自定义分隔符
拼接字段可以排序
wm_concat(): 只能使用逗号作为分隔符
拼接字段不能排序
不推荐使用,原因是在Oracle的高版本,其被弃用了(12g版本开始弃用,11g版本可以使用,但是有bug，要使用to_char()转换)

13.2 行转列

pivot()行转列
语法：select * from 表名 pivot(sum(字段的值) for 转成列的列名 in(‘列名’,‘列名’,…))

/*
      学生姓名    课程名称     课程分数
      张三         语文          80
      张三         数学          70
      李四         语文          85
      李四         数学          77

  行转列
      学生姓名       语文       数学
      张三           80         70
      李四           85         77

   case when              
   decode
    方式二：pivot()函数来写  
    语法
    select *
    from 表名
    pivot(聚合函数(字段1) for 字段2 in(值1,值2,...));
    聚合函数：除了count之外的其它聚合函数
    字段1：行转列后字段的值，在原表中所在的字段
    字段2：行转列后的字段名，在原表中所在的字段
    值1：原表中要行转列字段的值   
*/       
CREATE TABLE ts_stu_course(
       NAME VARCHAR2(11),
       course VARCHAR2(11),
       score NUMBER(3)
); 
INSERT INTO ts_stu_course VALUES('张三','数学',70);
INSERT INTO ts_stu_course VALUES('张三','语文',80);
INSERT INTO ts_stu_course VALUES('张三','英语',85);
INSERT INTO ts_stu_course VALUES('李四','数学',77);
INSERT INTO ts_stu_course VALUES('李四','语文',85);
INSERT INTO ts_stu_course VALUES('李四','英语',69);

-- 方式一：case when
SELECT NAME,
       SUM(CASE WHEN course = '语文' THEN score END) AS 语文,
       SUM(CASE WHEN course = '数学' THEN score END) AS 数学, 
       SUM(CASE WHEN course = '英语' THEN score END) AS 英语       
FROM ts_stu_course 
GROUP BY NAME;   
-- 方式二：decode
SELECT NAME,
       SUM(DECODE(course,'语文',score,0)) 语文,
       SUM(DECODE(course,'数学',score,0)) 数学,
       SUM(DECODE(course,'英语',score,0)) 英语
FROM ts_stu_course  
GROUP BY NAME;   

-- pivot 语法：pivot(字段的值 for 转成列的列名 in (列名1,列名2,列名3))  
SELECT * FROM ts_stu_course PIVOT(SUM(score) FOR course IN ('语文','数学','英语'));

-- 创建 sales 表
CREATE TABLE t_sales (
    year NUMBER,
    quarter VARCHAR2(2),
    sales_amount NUMBER
);

-- 插入数据
INSERT INTO t_sales (year, quarter, sales_amount) VALUES (2023, 'Q1', 1000);
INSERT INTO t_sales (year, quarter, sales_amount) VALUES (2023, 'Q2', 1500);
INSERT INTO t_sales (year, quarter, sales_amount) VALUES (2023, 'Q3', 1200);
INSERT INTO t_sales (year, quarter, sales_amount) VALUES (2023, 'Q4', 1700);
INSERT INTO t_sales (year, quarter, sales_amount) VALUES (2024, 'Q1', 1100);
INSERT INTO t_sales (year, quarter, sales_amount) VALUES (2024, 'Q2', 1600);
INSERT INTO t_sales (year, quarter, sales_amount) VALUES (2024, 'Q3', 1300);
INSERT INTO t_sales (year, quarter, sales_amount) VALUES (2024, 'Q4', 1800);

-- 提交事务
COMMIT;
/*
给定表 t_sales，包含如下数据：

year  quarter sales_amount
2023  Q1  1000
2023  Q2  1500
2023  Q3  1200
2023  Q4  1700
2024  Q1  1100
2024  Q2  1600
2024  Q3  1300
2024  Q4  1800
请编写 SQL 语句，将数据转换为如下格式：

year  Q1  Q2  Q3  Q4
2023  1000  1500  1200  1700
2024  1100  1600  1300  1800
*/

13.2 列转行

unpivot()
语法：select * from 表名 unpivot(自定义列名1 for 自定义列名2 in(字段名,字段名,…))

/*
      学生姓名       语文       数学
      张三           80         70
      李四           85         77    
      
      列转行
        
      学生姓名    课程名称     课程分数
      张三         语文          80
      张三         数学          70
      李四         语文          85
      李四         数学          77
*/
-- 将上面行转列后的表重新列转行
CREATE TABLE ts2 AS 
SELECT NAME,
SUM(DECODE(course,'语文',score,0)) 语文,
SUM(DECODE(course,'数学',score,0)) 数学,
SUM(DECODE(course,'英语',score,0)) 英语
FROM ts_stu_course  
GROUP BY NAME;
-- 列转行
-- 方法一：
SELECT NAME,'语文' course,语文 score FROM ts2
UNION
SELECT NAME,'数学' course,数学 score FROM ts2
UNION
SELECT NAME,'英语' course,英语 score FROM ts2;

-- 方法二：unpivot()
-- 语法 select * from 表名 unpivot(字段的值 for 要转成行的列名 in (列名1,列名2...))
SELECT * FROM ts2 UNPIVOT(score FOR course IN(语文,数学,英语));

课堂练习：

-- 创建 employee_sales 表
CREATE TABLE employee_sales (
    emp_id NUMBER,
    jan_sales NUMBER,
    feb_sales NUMBER,
    mar_sales NUMBER
);

-- 插入数据
INSERT INTO employee_sales (emp_id, jan_sales, feb_sales, mar_sales) VALUES (1, 200, 210, 220);
INSERT INTO employee_sales (emp_id, jan_sales, feb_sales, mar_sales) VALUES (2, 180, 190, 200);
INSERT INTO employee_sales (emp_id, jan_sales, feb_sales, mar_sales) VALUES (3, 170, 180, 190);

-- 提交事务
COMMIT;

/*
给定表 employee_sales，包含如下数据：

emp_id  jan_sales feb_sales mar_sales
1 200 210 220
2 180 190 200
3 170 180 190
请编写 SQL 语句，将数据转换为如下格式：

emp_id  month sales
1 Jan 200
1 Feb 210
1 Mar 220
2 Jan 180
2 Feb 190
2 Mar 200
3 Jan 170
3 Feb 180
3 Mar 190
*/

14、批量更新数据

14.1 描述

有表A和表B两张表，要求将表B的数据批量更新到表A中

14.2 更新方式

全删全插

先将表A中的数据全部删除，再将表B中的数据全部插入到表A中

    1.全删全插(全量更新)
      使用场景：表A中包含了所有的数据（全量数据），比如表A是一张员工表，它里面包含了所有的员工最新状态的信息。
                现在需要将其中的所有数据全部插入到表B中。
                
      实现方式：   
        1.更新数据前，必须确保表B是一张空表。先清空表B中的所有数据
        truncate table 表B;
        
        2.将表A中的所有数据全部插入到表B中，使用 insert + select 方式
        insert into 表B 
        select * from 表A;    
        -- 表示将查询结果中的所有数据全部插入到表B中   

*/
-- 通过create table as 的方式创建表 emp_a 
CREATE TABLE emp_a 
AS
SELECT * FROM emp;

-- 通过相同方式创建emp_b
CREATE TABLE emp_b 
AS
SELECT * FROM emp
WHERE deptno = 30;

SELECT * FROM emp_a;
SELECT * FROM emp_b;

-- 表A的数据是最新最全的，表B的数据只有一部分残缺的，需要将表A的数据全量更新到表B中
-- 1.删除表B的数据
TRUNCATE TABLE emp_b;
-- 2.将表A的数据全部插入到表B中
INSERT INTO emp_b
SELECT * FROM emp_a;

增量更新

表B中的一条数据，如果表A已经存在，则跟新表A中的该条数据
表B中的一条数据，如果表A中不存在，则向表A中插入该条数据
表A中的数据如果表B不存在，则不变

/*
 2.增量更新
   增量累全
     增量数据和全量数据的区别
     全量数据：表中包含了所有的完整数据
     增量数据：表中只包含了一部分数据，一部分被修改过的数据（已有的数据发生了修改，新增的数据）
     
     比如先有一张表C，里面记录了增量更新的数据，如何将增量的新数据更新到表B中？
     
     在更新过程中需要考虑的问题
       情况一：表C中的一条数据，表B中不存在，则需要将这条数据insert 插入到表B中。
       情况二：表C中的一条数据，表B中已经存在，则需要使用 update 修改表B中这条数据字段的值（除了连接字段之外的其余字段）
       情况三：表B中的一条数据，表C中不存在，在保持不变。
     
    需要使用 merge into 进行修改
    语法
    merge into 表B b     -- 被修改数据的表
    using 表C c          -- 存放增量数据的表
    on ( b.字段1 = c.字段1 )  -- 填写表B和表C之间的连接条件，条件两端的括号一定要写上
    when matched then  -- 情况二，需要进行 update 修改操作
      update 
      set b.字段2 = c.字段2,  -- set 后面不能写关联条件的字段，即字段1不能写
          b.字段3 = c.字段3,  -- 除了关联的字段，其余字段都写上
          ...
    when not matched then -- 情况一，需要使用 insert 插入操作
      insert (b.字段1,b.字段2,...)
      values (c.字段1,c.字段2,...);
   
*/
-- 将 emp_c 的增量数据更新到 emp_b 中
SELECT * FROM emp_b;

MERGE INTO emp_b b
USING emp_c c
ON (b.empno = c.empno)
WHEN MATCHED THEN
  UPDATE
  SET    b.ename    = c.ename,
         b.job      = c.job,
         b.mgr      = c.mgr,
         b.hiredate = c.hiredate,
         b.sal      = c.sal,
         b.comm     = c.comm,
         b.deptno   = c.deptno  
WHEN NOT MATCHED THEN
  INSERT
    (b.empno, b.ename, b.job, b.mgr, b.hiredate, b.sal, b.comm, b.deptno)
  VALUES
    (c.empno, c.ename, c.job, c.mgr, c.hiredate, c.sal, c.comm, c.deptno);

五、数据表分区

5.1 含义

将表中的数据按照一些条件，存放到不同分区中，当我们需要查找某些数据时，比如查找2023年1月份的部分数据时，可以从2023年1月份分区中去查询数据，而不需要查询全表的数据，提高查询效率

5.2 作用

提高查询性能：表分区可以将数据分散存储在不同的物理存储介质上，从而可以并行地处理查询请求，提高查询性能。特别是对于大型数据库和高并发的查询场景，表分区可以显著减少查询的响应时间。
管理和维护的简化：通过表分区，可以将数据分组存储在不同的分区中，从而简化了数据的管理和维护工作。例如，可以针对特定的分区执行备份、恢复、优化和维护操作，而不需要对整个表进行操作
提高数据可用性和容错性：表分区可以增加数据的可用性和容错性。如果某个分区发生故障或数据损坏，只会影响到该分区的数据，而不会影响到其他分区的数据。这样可以更快地恢复数据，并减少数据丢失的风险。
支持数据生命周期管理：通过表分区，可以根据数据的生命周期将数据分散存储在不同的分区中。例如，可以将最新的数据存储在高性能的存储介质上，而将较旧的数据存储在低成本的存储介质上。这样可以在满足性能需求的同时，降低存储成本。
支持分布式计算：在分布式数据库系统中，表分区可以将数据分散存储在不同的节点上，从而支持分布式计算。这样可以更好地利用集群资源，并提高计算的效率和扩展性。

**表分区的设置时机：**在创建表的时候就要设置好表分区

5.3 表分区分类

范围分区，列表分区，散列分区，混合分区，间隔分区

1、范围分区

特点：根据指定字段值的范围将数据表分成多个分区
关键字：range
使用场景：如果需要进行数据的过期化处理，那么范围分区基本上是唯一的选择。
语法：

-- 创建销售表
CREATE TABLE sales (
    sales_id NUMBER PRIMARY KEY,         -- 销售ID，主键
    customer_id NUMBER,                   -- 客户ID
    product_id NUMBER,                    -- 产品ID
    sales_date DATE,                      -- 销售日期
    quantity_sold NUMBER,                 -- 销售数量
    unit_price NUMBER(10,2),              -- 单价
    total_price NUMBER(10,2)              -- 总价
);

-- 插入样本数据
INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (1, 101, 201, TO_DATE('2023-01-15', 'YYYY-MM-DD'), 5, 10.50, 52.50);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (2, 102, 202, TO_DATE('2023-02-16', 'YYYY-MM-DD'), 10, 15.75, 157.50);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (3, 103, 203, TO_DATE('2023-03-17', 'YYYY-MM-DD'), 8, 20.25, 162.00);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (4, 101, 204, TO_DATE('2023-04-17', 'YYYY-MM-DD'), 3, 18.99, 56.97);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (5, 104, 205, TO_DATE('2023-05-18', 'YYYY-MM-DD'), 12, 22.50, 270.00);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (6, 105, 206, TO_DATE('2023-06-19', 'YYYY-MM-DD'), 6, 30.00, 180.00);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (7, 106, 207, TO_DATE('2023-07-20', 'YYYY-MM-DD'), 9, 25.50, 229.50);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (8, 107, 208, TO_DATE('2023-08-21', 'YYYY-MM-DD'), 4, 35.75, 143.00);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (9, 108, 209, TO_DATE('2023-09-22', 'YYYY-MM-DD'), 7, 40.25, 281.75);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (10, 109, 210, TO_DATE('2023-10-23', 'YYYY-MM-DD'), 11, 28.99, 318.89);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (11, 110, 211, TO_DATE('2023-11-24', 'YYYY-MM-DD'), 5, 22.75, 113.75);

INSERT INTO sales (sales_id, customer_id, product_id, sales_date, quantity_sold, unit_price, total_price)
VALUES (12, 111, 212, TO_DATE('2023-12-25', 'YYYY-MM-DD'), 8, 18.50, 148.00);

/*
1.范围分区  range
特点：根据表中字段值的范围进行分划分
语法：
create table 表名(
  字段名 数据类型,
  ...
)partition by range(字段名)  -- 分区字段
(
   partition 分区名1 values less than(上限值1) [tablespace 表空间名1],
   partition 分区名2 values less than(上限值2) [tablespace 表空间名2],
   ...
   partition 分区名n values less than(上限值n) [tablespace 表空间名n]

);

注意点：
  1.范围分区每一个分区中都有一个上限值，要求分区字段的值要小于该上限值，
    取值不到上限值
  2.除了第一个分区，后续分区都有一个隐含的下限，是上一个分区的上限值
  3.分区的书写顺序要求按照上限值递增排序
  4.表中每个分区的数据可以分别存放在不同表空间中，
    实现一张表的数据存放在多个表空间中

*/
-- 授予创建表空间权限
GRANT CREATE TABLESPACE TO scott;

-- 创建表空间
CREATE TABLESPACE tbs1
DATAFILE 'C:\database\tbs1.dbf'
SIZE 5m
AUTOEXTEND OFF;
CREATE TABLESPACE tbs2
DATAFILE 'C:\database\tbs2.dbf'
SIZE 5m
AUTOEXTEND OFF;
CREATE TABLESPACE tbs3
DATAFILE 'C:\database\tbs3.dbf'
SIZE 5m
AUTOEXTEND OFF;
CREATE TABLESPACE tbs4
DATAFILE 'C:\database\tbs4.dbf'
SIZE 5m
AUTOEXTEND OFF;

-- 创建一个分区表 t_sale，使用范围分区，根据2022年每个季度进行划分
CREATE TABLE t_sale(
  ID NUMBER,
  NAME VARCHAR2(20),
  amount NUMBER(10,2),
  s_date DATE
)PARTITION BY RANGE(s_date)  -- 分区字段必须是表中的字段
(
  PARTITION q1 VALUES LESS THAN(DATE '2022-04-01') TABLESPACE tbs1,
  PARTITION q2 VALUES LESS THAN(DATE '2022-07-01') TABLESPACE tbs2,
  PARTITION q3 VALUES LESS THAN(DATE '2022-10-01') TABLESPACE tbs3,
  PARTITION q4 VALUES LESS THAN(DATE '2023-01-01') TABLESPACE tbs4

);

INSERT INTO t_sale VALUES(1,'张三',1000,DATE '2022-02-02');
INSERT INTO t_sale VALUES(2,'李四',2000,DATE '2022-05-02');
INSERT INTO t_sale VALUES(3,'王五',3000,DATE '2022-09-02');
INSERT INTO t_sale VALUES(4,'赵六',4000,DATE '2022-11-02');
INSERT INTO t_sale VALUES(6,'张三',1000,DATE '2022-06-02');

-- 查看分区表中的数据
SELECT * 
FROM t_sale PARTITION(q1) t1
JOIN t_sale PARTITION(q2) t2
ON t2.name = t1.name;

-- 如何查看表中有哪些分区
-- 方式一：对象窗口栏在tablse中找到指定的表右键选择编辑，在分区栏中查询分区信息
-- 方式二：查询数据词典，系统视图 user_tab_partitions 中记录了每一个表的分区信息
SELECT * FROM user_tab_partitions WHERE table_name = UPPER('t_sale');

注意：

每一个范围分区中有一个隐含的下限，是上一个分区的上限。
一般范围分区使用的字段是时间类型和数字类型。
可以使用maxvalue 来捕获大于最高分区的所有的数据。
对于没有设置过分区的表，创建完成后不能再新增分区，已经分区过的表之后可以在添加新分区（间隔分区中再提）。

2、列表分区

语法：

/*
 2.列表分区  list
特点：根据分区字段的值进行分区，值相同的数据再同一个分区中
语法
create table 表名(
 字段名 数据类型,
 ...
)partition by list(分区字段)
(
partition 分区名1 values(值1) [tablespace 表空间名1],
...
partition 分区名n values(值1) [tablespace 表空间名n],
);
注意：
1.列表分区中各分区之间是平等的，各分区的书写顺序没有要求

使用场景：根据地理位置分析数据时可以使用，比如银行有多个分支机构在不同的省份或市或县
        需要查询某个地区分支机构的信息，可以根据地理位置进行分区提高查询效率
       还可以根据客户的信用等级对客户进行划分，分区存储
       还可以根据交易状态进行划分，比如：成功，失败，处理中进行分区
*/

-- 创建一个客户表，要根据客户所在的省份进行分区
CREATE TABLE cust_t(
  c_id NUMBER,
  c_name VARCHAR2(20),
  c_pro VARCHAR2(10)
)PARTITION BY LIST(c_pro)
(
  PARTITION shanghai VALUES('上海市') TABLESPACE tbs_01,
  PARTITION guangdong VALUES('广东省') TABLESPACE tbs_02
);

SELECT * FROM user_tab_partitions;

-- 添加数据
INSERT INTO cust_t VALUES(1,'张三','上海市');
INSERT INTO cust_t VALUES(2,'李四','广东省');

SELECT * FROM cust_t PARTITION(shanghai);

3、散列分区（hash分区）

/*
3.散列分区(hash分区) hash
特点： 先计算分区字段的hash值（通过hash算法计算，比如MD5算法）得到字段的hash值（是一个16进制的数），
     再使用hash值除以分区数量取余，余数相同的数据再同一个分区中  
语法
create table 表名(
 字段名 数据类型,
 ...
)partition by hash(分区字段)
(
partition 分区名1 [tablespace 表空间名1],
...
partition 分区名n [tablespace 表空间名n],
); 
使用场景：可以将分区中的数据均匀分布，避免出现数据倾斜
        当表中的数据出现数据倾斜现象，可以使用hash分区规避这个问题
        数据倾斜：指的是表中的数据大量集中在某一区间内，比如1月份的数据占了表中数据的80%，其余月份一共占用20%
                  此时出现了数据倾斜，可以使用hash分区解决这问题
                        
*/

-- 在进行散列分区的时候可以让系统自动设置分区的名称。
create table 表名(
     列名  数据类型，
     ......
)partition by hash(列名)
partitions 分区个数 store in(表空间名,表空间名,...);

-- 创建一个客户表，按照客户姓名进行分区，使用散列分区，分区名称由系统来决定
CREATE TABLE cust_n_t1(
  c_id NUMBER,
  c_name VARCHAR2(20),
  c_pro VARCHAR2(10)
)PARTITION BY HASH(c_name)
PARTITIONS 4 STORE IN (tbs_01,tbs_02,tbs_03,tbs_04);

4、混合分区（组合分区）

分区方式：在分区中继续新增子分区，分区类型是以上的三种分区进行组合。
常见的组合方式：常见的组合方式有以下三种

分区	子分区	特点
范围分区	列表分区	可以使分区结果更加精细化
范围分区	散列分区	可以使每个子分区中的数据更加平均
列表分区	散列分区	可以使每个子分区中的数据更加平均

语法：

create table 表名(
	列名 数据类型，
    ...
)partition by 主分区方式(列名)
subpartition by 子分区方式(列名)
(
  partition 主分区名...
  (
  	subpartition 子分区名...,
    subpartition 子分区名...,
    ...
  ),
  partition 主分区名...
  (
  	subpartition 子分区名...,
    subpartition 子分区名...,
    ...
  ),
  ...  
)
--创建一个客户表，首先按照客户的省份进行列表分区，然后再根据客户的名称进行散列分区
CREATE TABLE cust_t1(
  c_id NUMBER,
  c_name VARCHAR2(20),
  c_pro VARCHAR2(10)
)PARTITION BY LIST(c_pro)
SUBPARTITION BY HASH(c_name)
(
  PARTITION shanghai VALUES('上海市') TABLESPACE tbs_01
  (
    SUBPARTITION sub1_01,
    SUBPARTITION sub1_02,
    SUBPARTITION sub1_03
  ),
  PARTITION guangdong VALUES('广东省') TABLESPACE tbs_02
  (
    SUBPARTITION sub2_04,
    SUBPARTITION sub2_05,
    SUBPARTITION sub2_06
  )
);

INSERT INTO cust_t1 VALUES(1,'小明','上海市');
INSERT INTO cust_t1 VALUES(2,'小红','广东省');
INSERT INTO cust_t1 VALUES(3,'小黑','上海市');
INSERT INTO cust_t1 VALUES(4,'小白','广东省');
-- 查询主分区情况
SELECT * FROM User_Tab_partitions;
-- 查询子分区情况
SELECT * FROM User_Tab_Subpartitions;

-- 查询主分区数据
SELECT * FROM cust_t1 PARTITION(guangdong);
-- 查询子分区数据
SELECT * FROM cust_t1 SUBPARTITION(sub1_01);

练习：创建一张销售表，先按照年份进行分区，然后再按照月份进行分区

5、间隔分区

特点：是Oracle数据库11g版本中添加的新功能， 间隔分区还是范围分区，只是会根据添加数据来自动的创建分区。
关键字：interval
语法：

create table 表名(
	列名 数据类型
    ...
)partition by range(列名)
interval(时间间隔函数)
(
	partition 分区名 values less than(上限)
);

-- 时间间隔函数
numtoyminterval(1,'year|month')		-- 设置间隔年或月，每年或月创建一个
numtodsinterval(1,'day|hour|minute|seconds')	-- 设置间隔天到秒
--  间隔分区必须要先存在一个分区。
-- 创建一个销售表，然后使用间隔分区按照销售时间进行分区
CREATE TABLE sale_t2(
  s_id NUMBER,
  s_name VARCHAR2(20),
  s_date DATE
)PARTITION BY RANGE(s_date)
INTERVAL(NUMTOYMINTERVAL(1,'month'))
(
  PARTITION par_01 VALUES LESS THAN(to_date('2020-1-1','yyyy-mm-dd'))
);

-- 添加数据
INSERT INTO sale_t2 VALUES(1,'笔记本电脑',to_date('2019-5-20','yyyy-mm-dd'));
INSERT INTO sale_t2 VALUES(2,'键盘',to_date('2020-6-20','yyyy-mm-dd'));
INSERT INTO sale_t2 VALUES(3,'鼠标',to_date('2020-7-20','yyyy-mm-dd'));
INSERT INTO sale_t2 VALUES(4,'手机',to_date('2020-8-20','yyyy-mm-dd'));

SELECT * FROM User_Tab_Partitions;

5.4 数据库表分区的维护

新增分区：

新增的分区必须要在现有的分区之外。
只能新增范围分区和列表分区，无法新增散列分区
范围分区新增的分区上限值必须大于现有分区上限值的最大值
语法

-- 新增主分区
alter table 表名 add partition 分区名 [values less than(上限)|values(值)] [tablespace 表空间名];
-- 给表sale_t添加一个范围分区
alter table sale_t add partition par_05 values less than(to_date('2022-4-1','yyyy-mm-dd')) tablespace tbs_01;

-- 新增子分区
alter table 表名 modify partition 主分区名 add subpartition 子分区名 [values less than(上限)|values(值)]
-- 在 sale_t1表中新增一个子分区
alter table sale_t1 modify partition par_01 add subpartition sub1_04 values(6);

删除分区：
1. 如果删除了某一个分区，则分区中的数据也会消失
2. 分区表中最少要存在一个分区，如果要删除最后一个分区只能删除表
3. 散列分区不能进行删除
  语法：
```
-- 删除主分区
alter table 表名 drop partition 分区名称;

alter table sale_t drop partition par_02;

-- 删除子分区
alter table 表名 drop subpartition 分区名称;
```

合并分区：

散列分区不支持合并。
在进行范围合并的时候不能将高分区合并到低分区，只能将低分区合并到高分区，合并的表分区要相邻，第一个写低分区，第二个写高分区。
语法

alter table 表名 merge partitions 分区名1,分区名2 into partition 分区名2
--将sale_t表中的分区进行合并
alter table sale_t merge partitions par_01,par_02 into partition par_02;

-- 将 cust_t1 表中的分区进行合并
alter table cust_t1 merge partitions shanghai,guangdong into partition shanghai;

拆分分区

/*
特点：将一个分区拆分成两个分区
alter table 表名 split partition 分区名 at(value1) into(partition 新分区名1, partition 新分区名2); 
*/
-- 拆分分区
ALTER TABLE t_sale SPLIT PARTITION q1_2 AT(DATE '2022-04-01') INTO (PARTITION q1, PARTITION q2);

截断分区：删除分区中的所有数据

/*
alter table 表名 truncate partition 分区名; 
*/
ALTER TABLE t_sale TRUNCATE PARTITION q1;

六、索引

-- 数据准备
-- 创建一张表
CREATE TABLE customer_test (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    age NUMBER,
    gender CHAR(4),
    created_date DATE,
    statue VARCHAR2(10)
);

-- 添加10w条数据
DECLARE
  v_gender CHAR(4);

  v_statue VARCHAR2(10);
BEGIN
  FOR i IN 1 .. 1000000
  LOOP
    v_gender := CASE TRUNC(dbms_random.value(0, 2))
                  WHEN 0 THEN
                   '男'
                  ELSE
                   '女'
                END;
    v_statue := CASE TRUNC(dbms_random.value(0, 3))
                  WHEN 0 THEN
                   '正常'
                  WHEN 1 THEN
                   '异常'
                  ELSE
                   '失效'
                END;
  
    INSERT INTO customer_test
    VALUES
      (i,
       dbms_random.string('l', 5),
       TRUNC(dbms_random.value(18, 40)),
       v_gender,
       TRUNC(SYSDATE) - TRUNC(dbms_random.value(1, 600)),
       v_statue);
  
  END LOOP;

END;

/*
3.1 什么是索引
索引结构可以理解为表的目录，类似于一本字典的目录，
通过索引查询表中数据可以提高查询效率

数据库查询表中数据的方式
  分为全表扫描查询和使用索引查询
  场景：现在表中有100w条数据，需要查询id字段为 10 的数据，分别使用全表扫描和索引查询实现查询结果
    全表扫描查询：数据库会从表中第一条数据开始进行查询，一直查询到结尾，一共进行了100w次查询
                  最后把所有id=10的数据查询出来，效率很慢
    使用索引进行查询：因为索引的特殊结构（B-Tree结构），可以实现通过较少的查询次数获取查询结果
                      一般是最多4次

3.2 索引的结构
1.二叉树结构                                 一个节点中有三部分组成：关键字，数据区和指针
    关键字：记录了当前字段的值
    数据区：记录了当前字段所属行rowid的值，如果有多行数据字段值相同，则有多个rowid
    指针：用来指向下一个节点的位置
 缺点：如果字段的值进行了排序，那么二叉树的左右不平衡，导致查询需要花费更多次数，
       比如查询最后一个字段的数据需要进行全表查询

2.平衡二叉搜索树
 在二叉树上进行了优化，通过左旋或右旋实现二叉树两端的平衡，
 要求左右两端二叉树的层级小于等于1            

3.B-Tree 结构
 是oracle索引的默认结构
在平衡二叉搜索树的基础上继续优化，在一个节点中增加多个枝叉实现纵向拓展，
同时要求左右两端的层级完全一致

4.B+Tree 结构
是MySQL中的普通索引默认结构
B+Tree非叶子节点中，只存有索引字段的值和指针，
只有在叶子节点的链表中，包含字段的值和表中主键字段的值 

3.3 索引的类型
以结构维度划分
1.B-Tree 索引
  特点：是oracle中的默认索引结构
  要求：索引字段的值允许少量重复或不重复
  语法
  create index 索引名 on 表名(字段名);  

2.反向键索引
 特点：是一个特殊B-Tree索引，和B-Tree索引的区别在于，将索引字段的值进行了反向 
   字段的值： 1001 1002 1003 1004 1005 2345 3456
   反向之后： 1001 2001 3001 4001 5001 5432 6543
 作用：当表中字段数据有数据倾斜时，使用反向键索引可以一定程度上避免出现索引倾斜
 语法：
 create index 索引名 on 表名(字段名) reverse;

3.位图索引  bitmap
 特点：是一个位运算的索引
 使用：如果字段的值种类很少大量重复时，可以创建位图索引
       比如：性别字段，或部门字段，或表示状态的字段（比如信息状态：成功，失败，处理中）
 语法
 create bitmap index 索引名 on 表名(字段名1,字段名2);      

创建索引的语法：
create [bitmap] index 索引名  on 表名(列名,函数(列名),....)
[reverse]  -- 反向键索引
[tablespace 表空间名]

删除索引 
drop index 索引名;
*/
-- 在emp表中创建一个普通索引
create index index_emp on emp(empno);
-- 创建一个反向键索引
create index index_emp_1 on emp(empno) reverse;
-- 给 emp 创建一个位图索引
create bitmap index index_bitmap_job on emp(job);

/*
以逻辑维度划分
普通索引: 就是一个普通的B-Tree索引
唯一索引: 是一个B-Tree索引,但是要求索引字段的值不能重复,可以是空值
       唯一索引可以由用户手动创建,或者当字段设置唯一约束时,会自动给字段创建唯一索引 
       语法
        create unique index 索引名 on 表名(字段名);  
主键索引: 是一个B-Tree索引, 但是要索引字段的值非空且不重复,
        主键索引不能手动创建, 只有当设置字段约束类型是主键时,系统默认给字段创建一个主键索引          
联合索引(组合索引): 同时给多个字段组合创建的一个索引
          比如同时给字段 a,b,c  创建一个联合索引 (a,b,c)
        语法
        create index 索引名 on 表名(字段名1,字段名2,字段名3,...);  
        联合索引的最左原则:
         创建一个联合索引 (a,b,c),相当于创建了三个索引 (a),(a,b),(a,b,c)
         遵循最左原则可以避免创建冗余索引
函数索引: 给被函数修饰过的字段创建索引
        如果给字段创建普通索引,但是在使用字段时使用系统函数(除了聚合函数)修饰,
        在查询时索引会失效, 此时需要给字段创建对应的函数索引                                  
分区索引: 可以给分区表创建对应的分区索引
        本地分区索引
          特点: 会给表中的每一个分区单独创建一个索引,每个分区中的索引只能查询本分区中的数据
          优点: 维护操作(比如添加/删除/合并分区时)通常只影响相关分区索引,而不是整个索引,
                效率更高
          缺点: 
            1.如果字段的值跨越多个分区,则需要扫描多个索引分区来获取所需数据,效率更慢      
            2.查询需要包含分区字段作为筛选条件才能利用索引,否则无法使用本地分区索引
          语法
          create index 索引名 on 表名(字段名) local;  

        全局分区索引
          特点: 创建一个索引,可以查询所有分区中的数据
          优点: 如果进行跨分区数据的查询,速度更快;
                不需要在查询中使用分区字段进行查询
          缺点: 当维护操作涉及到表分区时,全局索引更新效率更慢      
          语法
          create index 索引名 on 表名(字段名) global; 

*/
-- 在表emp创建一个函数索引
create index index_fun_ename on emp(upper(ename)); 

-- 创建一个本地分区索引
create index local_index_emp on emp1(deptno) local(partition p1,partition p2,partition p3);
-- 或者，推荐使用
create index local_index_emp on emp1(deptno) local;

-- 创建一个全局分区索引
create index local_index_emp on emp1(deptno) global;

-- 创建一个hash索引
CREATE INDEX emp_index1 
ON emp(ename) GLOBAL 
PARTITION BY HASH(ename) PARTITIONS 16;


/*
  oracle中的优化器
  CBO: cost_based Optimizer, 基于耗费的优化器 
       会自动判断每一种执行路径的耗费, 优先选取耗费最少的方式制定执行计划
  
  RBO: rule_based Optimizer, 基于规则的优化器
       按照既定规则去制定执行计划
       
  在 oracle 10g 版本之前使用的是 RBO 优化器,在 10g 后默认使用的是CBO优化器.     
  
  
  索引查询一定是最高效的查询方式吗? 不一定, 如果CBO优化器认为使用全表扫描的方式,查询速度比使用索引更快,
    那么会优先使用全表扫描, 导致出现索引失效的情况, 在工作中需要避免出现索引失效  
*/
SELECT *
FROM   emp
WHERE  sal > 1000  
       AND deptno = 10;

-- 路径1 : 先执行 sal > 1000  再执行 deptno = 10
-- 路径2 : 先执行 deptno = 10  再执行 sal > 1000 
-- 比如 表中有10w条数据
-- sal > 1000 可以过滤其中 2w条数据
-- deptno = 10 可以过滤去 9w 条数据
-- 选择路径2 进行过滤比较好,耗费资源比较少

/*
  索引失效的场景:
    1.索引字段在 where 条件中使用了 !=  <> 或 not in 操作,会使得索引失效,
      这样会使得查询结果是表中的绝大部分数据,此时使用全表扫描比使用索引查询更快
      
    2.字段1 创建了索引,字段2 没有创建  where 条件中 使用了 or 进行 过滤
       where 字段1 = 值1 or 字段2 = 值2  
       
    3.索引字段在 where 条件中使用了数学计算  + - * /
      where 字段 + 100 = 1000;  失效
      where 字段 = 1000 - 100;  生效  
      
    4.索引字段在where条件中使用系统函数导致失效  
      
    5.索引字段在 where 条件中和比较的值 数据类型不同,也会导致索引失效
      比如 索引字段是字符串类型 
       where 字段 = 2024;  
       
    6.违法联合索引的最左原则,导致索引失效  
    
    7.索引字段在where条件中使用了 空值判断  is null  或  is not null 导致索引失效
      因为索引树中不会存入null值
      
    8.索引字段使用模糊查询导致索引有可能失效
        where 字段名 like '%A';   会失效
        where 字段名 like 'A%';   不会失效
      
    9.如果CBO优化器认为使用全表扫描查询,比使用索引查询更快时,会优先使用全表扫描进行查询
      比如查询结果占据了表中的大部分数据,比如 70%,那么就会优先使用全表扫描   

*/
-- 1.
SELECT  * FROM emp WHERE ename != 'SMITH';

-- 2.
SELECT  * FROM emp WHERE ename = 'SMITH' OR mgr = 7698;

-- 3.
CREATE INDEX idx_emp_sal ON emp(sal);

SELECT * FROM emp WHERE sal + 1000 = 2000;

-- 4.
CREATE INDEX idx_emp_HIREDATE ON emp(HIREDATE);

SELECT * FROM emp WHERE to_char(HIREDATE,'yyyy') = '1980'
-- 创建对应的函数索引
CREATE INDEX idx_emp_HIREDATE1 ON emp(to_char(HIREDATE,'yyyy'));
-- 5.
SELECT * FROM emp WHERE to_char(HIREDATE,'yyyy') = 1980

-- 7.
SELECT  * FROM emp WHERE ename is NULL;

-- 8
SELECT  * FROM emp WHERE ename LIKE '%S';
-- INDEX FULL SCAN: 整个索引树都扫描一遍,相当于全表扫描了

-- 9.
SELECT * FROM customer_test WHERE ID <= 10;

/*
1.3 索引的使用场景
  1.如果表中的数据量比较少，不适合添加索引
  2.如果表中数据更新比较频繁，很少执行查询语句，也不适合创建索引，
    因为表中索引越多，会降低表的DML效率，每次插入或修改删除数据都会导致索引进行重构
    降低DML速度
  3.如果字段经常再where中进行过滤，或者group by 中进行分组，或者 order by 中进行排序
    则可以对这些字段创建索引，提高过滤速度，分组速度和排序速度 
  4.如果有冗余索引，则不推荐继续创建（比如已经有(a,b)联合索引，则不必再创建索引a）  

*/

SELECT * FROM random WHERE SERIALNO < 1000;

CREATE INDEX idx_random_SERIALNO ON random(SERIALNO);

/*
1.4 索引创建步骤：
  索引如果创建不好可能会导致生产事故，因为在对表创建索引时，
  会给表加上锁，在上锁期间不能对表进行任何操作，此时如果有操作产生，可能会导致失败
  如何规避这个问题
  比如要给表A创建索引
  1.先创建一张结构和表A完全相同的表B，
  2.将表A中的数据全表插入到表B中
  3.给表B添加对应的索引
  4.分别更换两张表的名字，将表A的名字换成其他，将表B的名字换成表A
  
  如果在给表插入数据前，表中已经有索引了怎么办？
    先删除表中原本的索引，再向表中插入数据，最后将索引重新添加回来 
*/

七、数据库优化

17.1 SQL语句优化：

/*
1.去重优先使用group by 实现，尽量不适用distinct
2.优先使用exists子查询代替in
3.where 和having都能实现过滤是时，优先使用where
4.union 和 union all都能实现时，优先使用union all
5.decode 和 case when都能实现时，优先使用decode函数
6.删除数据时，如果要删除全表数据，优先使用truncate进行删除
   drop，delete和truncate的区别
     1.delete可以删除部分数据和全部数据，
       truncate只能删除全部数据
     2.delete删除的数据可以使用事务进行回滚，撤销删除
       truncate删除的数据不能回滚
     3.truncate删除数据会重置表的高水位线
       delete删除数据不会重置高水位线 
     4.truncate删除数据效率更高 
     5.drop 是删除表结构和表中的所有数据 
*/

17.2 存储过程优化

1. 使用日志表找出存储过程中哪个步骤执行效率特别低，
2. 找到之后再使用上述方式对该步骤进行优化

八、执行计划的查看

8.1 开启执行计划的方式

选中要分析的SQL语句，按F5打开执行计划窗口，或者按右键解释计划打开
手动创建解释计划窗口，再将要分析的SQL语句输入进去
使用 explain plan 方式执行

EXPLAIN PLAN FOR
SELECT t.*,ROWID FROM table_file t;

SELECT * FROM TABLE(dbms_xplan.display);

8.2 执行计划查看的内容

1、执行计划中常用的字段

rows（基数）：Oracle预估最后返回的行数
bytes（字节）：Oracle执行该步骤之后返回的字节数
cost（耗费）：主要是预估CPU耗费，理论上CPU耗费越小越好（该值可能和实际的情况有一定的出入）
time（时间）：Oracle预估当前的操作所花费的时间

2、执行计划主要查看的内容：

表扫描方式：
1. table access full：全表扫描
  
  建议：如果表中的数据量太大，则不建议使用全表扫描，除非就是要查询的数据超过了全表数据的20%以上。
2. table access by rowid：通过rowid来扫描表中的数据。
3. table access by index scan：通过索引来扫描。
表连接方式：
1. sort merge join：排序-合并连接
  - 连接方式：将两张表先根据连接字段进行排序，将排序结果加载到内存，再进行连接条件的匹配，最后展示连接结果
  - 使用场景：一般用在两张表连接字段有索引或者连接条件是不等值连接中，连接效率比较高，但是比较耗费内存
2. hash join：哈希连接
  - 连接方式：选取一张表作为驱动表，将其所有数据都加载到内存中，同时计算两张表连接字段的hash值，再根据连接条件进行hash值的匹配，最后展示连接结果
    - hash值的特点: 不同字符串的hash值不相同, hash值相同的字符串,一定是同一个字符串
  - 使用场景：使用于两张大表进行等值匹配，连接效率比较高，但是比较耗费内存，选择两张表中数据量较小的那张表作为驱动表
3. nested loop join：嵌套循环连接
  - 连接方式：选取一张表为驱动表，每一次只加载驱动表中的一条数据进入内存，依次和另一张表的数据进行匹配，在内存中保存匹配结果，再选取驱动表中下一条数据，以此类推
  - 使用场景：适用于一张大表和一张小表进行关联，小表作为驱动表，大表作为被驱动表，在连接时内存占用比较小，但是连接效率比较慢。

3、hints：修改执行计划

hint 是 Oracle 提供的一种特殊的SQL语法，允许在SQL语句中插入一些相关的语法来改变SQL语句的执行方式。

常见的hint语法：

-- 1./*+parallel(表名,并行数)*/  设置表查询的时候并行数量。
--2./*+full(表名)*/ 指定表进行全表扫描
select /*+full(e)*/ e.* from emp e where e.empno=7369;
--3./*+use_hash(表名,表名)*/ 设置表的连接方式使用hash join
select /*+USE_HASH(e,d)*/ * from emp e,dept d where e.deptno=d.deptno;
--4./*+use_nl(表名,表名)*/ 设置表的连接方式为nested loop join
select /*+use_nl(e,d)*/ * from emp e,dept d where e.deptno=d.deptno;
--5./*+use_merge(表名,表名)*/ 设置表的连接方式为sort merge join
select /*+use_merge(e,d)*/ * from emp e,dept d where e.deptno=d.deptno;
select /*+use_merge(s,s1)*/* from student s,score s1 where s.sid=s1.student_id;
--6./*+ordered*/ 设置表的连接顺序,代表表连接的顺序就是表书写的顺序。
select /*+ordered*/ * from emp e,dept d where e.deptno=d.deptno;
--7./*+index(索引名,.....)*/设置表在扫描的时候使用索引扫描，如果设置多个索引则系统默认或选择最优的索引执行。
--如果指定的索引在where语句后面没有对应的过滤字段，则该hint会自动忽略。
select /*+index(index_ename)*/ * from emp where empno=7369;

-- 注意：
-- 1.*和+之间不能有空格
-- 2.如果表设置了别名则在写的时候必须要使用别名

九、正则表达式

-- 数据准备
create table test(mc varchar2(60));
insert into test values('13456789201');
insert into test values('112233445566778899');
insert into test values('22113344 5566778899');
insert into test values('33112244 5566778899');
insert into test values('44112233 5566 778899');
insert into test values('5511 2233 4466778899');
insert into test values('661122334455778899');
insert into test values('771122334455668899');
insert into test values('881122334455667799');
insert into test values('991122334455667788');
insert into test values('aabbccddee');
insert into test values('bbaaa_lvzx@163.cn ccddee');
insert into test values('ccabbddee');
insert into test values('ddaabbccee');
insert into test values('eeaabbccdd');
insert into test values('ab123');
insert into test values('123xy');
insert into test values('007ab');
insert into test values('abcxy');
insert into test values('The final test is is is how to find duplicate words.');
insert into test values('abcxy 13900293456 sdafw-lvzx@163.com');
insert into test values('bbaaa-lvzx@163.com ccddee');
INSERT INTO TEST VALUES('hello isisis world');
insert into test VALUES('hello is is abb');
insert into test VALUES('hello isis xabbx');
INSERT INTO test VALUES ('ae');
INSERT INTO test VALUES ('bbaaa_lvzx@163.cn ccdd cccccccc_lvzx@163.cn ee');
commit;
/*
1.正则表达式
  1.1 什么是正则表达式
      是一种用来描述字符串匹配模式的表达式，
      当需要使用复杂的模糊查询时，可以借助正则表达式进行完成
      
  1.2 正则函数
    regexp_like(str,pattern[,match])  和 like模糊查询类似，进行模糊查询
      函数的结果是一个布尔类型，也就是说函数本身就构成了一个条件
    
      str: 要进行正则匹配的字符串或字段
      pattern： 正则匹配符
      match：匹配规则，是否区分字母大小写，默认区分大小写字母，
             'i' : 不区分字母大小写
                          
  1.3 正则匹配符
    ^ ：匹配一个字符串的开始
    $ ：匹配一个字符串的结束
    
    匹配次数相关
    * ：对前一个字符匹配0次或多次
    + : 对前一个字符匹配1次或多次
    ? : 对前一个字符匹配0次或1次
    {m}: 对前一个字符匹配m次
    {m,n} : 对前一个字符匹配m次到n次
    {m,} : 对前一个字符匹配至少m次
    {,n} : 对前一个字符匹配至多n次
    
    
    匹配内容相关
    . : 表示任意单个字符，和模糊查询中的 _ 类似
    \. : 表示一个小数点
    \w ：表示任意单个大小写字母，数字，或 _
    \d : 表示任意单个0-9的数字
    [] 指定匹配列表中的内容
      [a-z] 表示只匹配小写字母
      [A-Z] 表示只匹配大写字母
      [a-zA-Z] 表示只匹配大小写字母
      [0-9] 表示只匹配数字
      [a-zA-Z0-9] 表示只匹配大小写字母和数字, 注意[]中的内容和顺序无关
    [^] 匹配任意单个不在列表中的数据  
      [^0-9] 匹配任意单个非数字的内容
    () 对字符进行分组,括号中的数据是一个整体
     (is){4,} 至少出现4次连续的is 
    | 表示 或
     (is|are) 表示匹配一个is或are     
*/

SELECT * FROM TEST;

-- 1.查询mc字段以字母a开头的数据
SELECT * 
FROM TEST 
WHERE regexp_like(mc,'^a');
-- 表示只要字段中包含字母a的数据
-- WHERE regexp_like(mc,'a')

-- 2.查询mc字段以字母a开头，字母e结尾的数据，
SELECT * 
FROM TEST 
WHERE regexp_like(mc,'^a.*e$');
-- 表示字段中的数据只有 ae 两个字母
-- WHERE regexp_like(mc,'^ae$');

-- 3.查询mc字段中只包含数字的数据
SELECT * 
FROM TEST 
WHERE regexp_like(mc,'^\d+$');

-- 4.查询mc字段中只包含小写字母的数据
SELECT * 
FROM TEST 
WHERE regexp_like(mc,'^[a-z]+$');

-- 5.查询mc字段中不包含数字的数据
SELECT * 
FROM TEST 
WHERE regexp_like(mc,'^[^0-9]+$');

-- 6.查询mc字段中包含2到3个连续的字母a的内容
SELECT * 
FROM TEST 
WHERE regexp_like(mc,'a{2,3}');

-- 7.查询mc字段中包含2到3个连续的is的内容
SELECT * 
FROM TEST 
WHERE regexp_like(mc,'(is){2,3}');

-- 8.查询mc字段中包含2到3个不连续的is的内容
SELECT * 
FROM TEST 
WHERE regexp_like(mc,'(is[^(is)]+){2,3}');

/*
查询 mc 字段中 包含 手机号的数据
手机号的规则: 
1. 长度是11位的纯数字
2. 数字1开头,第二位是3-9
3. 其他位数是任意的

查询 mc字段中包含邮箱的数据
邮箱规则
1. 邮箱必须要有@ ,@ 前面的内容是邮箱名,后面的内容是域名
2. 邮箱名命名规范
   1.包含字符 大小写字母,数字,. ,_, -  首字符必须是字母或数字
   2.邮箱长度 6到64个字符
3.域名要求
  1.小数点前的内容必须是字母或数字, 
  2.小数点后的内容 可以是 com, cn, net 
*/
-- 包含手机号
SELECT * FROM test WHERE regexp_like(mc,'1[3-9][0-9]{9}');

-- 只包含手机号
SELECT * FROM test WHERE regexp_like(mc,'^1[3-9][0-9]{9}$');

-- 包含邮箱
SELECT *
FROM   test
WHERE  regexp_like(mc,
                   '[a-zA-Z0-9][a-zA-Z0-9._-]{5,63}@[a-zA-Z0-9]+\.(com|cn|net)');

/*
其它正则函数
  regexp_instr(str,pattern,position) 功能类似 instr
   查找字符串中符合正则匹配内容首次出现的位置，函数的结果是数字
   position 是搜索位置，默认从1开始

  regexp_substr(str,pattern,position) 功能和 substr 类似
    截取字符串中符合正则匹配的内容，函数的结果是字符串
    
  regexp_replace(str,pattern,replace_str) 功能和 replace 类似
    将字符串中符合正则表达式的内容替换成  replace_str
*/
-- regexp_instr
SELECT mc,regexp_instr(mc,
                    '[a-zA-Z0-9][a-zA-Z0-9._-]{5,63}@[a-zA-Z0-9]+\.(com|cn|net)',1)
FROM   TEST;

-- regexp_substr
SELECT mc,regexp_substr(mc,
                    '[a-zA-Z0-9][a-zA-Z0-9._-]{5,63}@[a-zA-Z0-9]+\.(com|cn|net)',1)
FROM   TEST;

-- regexp_replace
SELECT mc,regexp_replace(mc,
                    '[a-zA-Z0-9][a-zA-Z0-9._-]{5,63}@[a-zA-Z0-9]+\.(com|cn|net)','邮箱名')
FROM   TEST;

十、递归查询

/*
4.递归查询
  4.1 递归查询基本概念
    节点：表中的一行数据称为一个节点
    id：当前节点的id字段
    pid：当前节点的父节点id
    
    根节点：pid的值是0或者null值的数据
    叶子节点：最低层级节点的数据
    
    如何查询某一个节点的父节点信息？
      where 当前节点pid = 父节点的id字段
      
    如何查询某一个节点的子节点信息？
      where 当前节点id = 子节点的pid字段 
      
    一级子节点：当前节点的子节点称为一级子节点
    二级字节点：当前节点子节点的子节点，称为当前节点的二级子节点
    ...    

*/
-- 建表
CREATE TABLE TB (
    ID NUMBER(10) NOT NULL, --主键
    PID NUMBER(10) , --父id
    NAME VARCHAR(128) --名称
);

-- 插数据
-- 一级节点
INSERT INTO TB VALUES (1,0,'查询');
INSERT INTO TB VALUES (2,0,'咨询');
INSERT INTO TB VALUES (3,0,'办理');


-- 二级节点
INSERT INTO TB VALUES (4,1,'余额查询');
INSERT INTO TB VALUES (5,1,'话费查询');
INSERT INTO TB VALUES (6,1,'城市查询');
INSERT INTO TB VALUES (7,1,'租房查询');
INSERT INTO TB VALUES (8,1,'公交查询');
INSERT INTO TB VALUES (9,1,'地铁查询');
INSERT INTO TB VALUES (10,2,'疑问咨询');
INSERT INTO TB VALUES (11,2,'报障咨询');
INSERT INTO TB VALUES (12,2,'话费咨询');
INSERT INTO TB VALUES (13,2,'余额咨询');
INSERT INTO TB VALUES (14,2,'活动咨询');
INSERT INTO TB VALUES (15,3,'公交办理');
INSERT INTO TB VALUES (16,3,'地铁办理');
INSERT INTO TB VALUES (17,3,'银行办理');


-- 三级节点
INSERT INTO TB VALUES (18,7,'一手房东查询');
INSERT INTO TB VALUES (19,7,'二手房东查询');
INSERT INTO TB VALUES (20,7,'三手房东查询');
INSERT INTO TB VALUES (21,7,'中介房东查询');

SELECT * FROM tb;
-- 1.查询表中所有根节点数据信息
SELECT * FROM tb WHERE pid = 0;

-- 2.查询id = 2节点的所有一级子节点信息
SELECT * FROM tb WHERE pid = 2;

-- 3.查询 id = 10 节点的父节点信息
-- 先查询节点的pid字段
SELECT pid FROM tb WHERE id =10;

-- 再制作成子查询
SELECT * FROM tb WHERE id = (SELECT pid FROM tb WHERE id =10);

-- 4.查询 id = 10 节点的兄弟节点（父节点相同）信息
SELECT * FROM tb WHERE pid = (SELECT pid FROM tb WHERE id =10) AND id != 10;

/*5.查询id = 1 节点的所有子节点信息（包括子节点的子节点一直向下直到结束）
  需要使用递归查询，connect by 
  语法
  select *
  from 表名
  where 过滤条件
  start with 递归起始位置条件 -- 从这个位置开始递归查询
  connect by prior 字段1 = 字段2;
  
  prior 决定了递归的方向，是从上往下查找子节点，还是从下往上查找父节点
    prior 后的字段可以理解为是当前节点所在字段，
    比如：要递归查询当前节点的所有子节点，当前节点的id字段 = 子节点的pid字段，
          所以 prior后面填写 id字段， connect by prior id = pid
         
          要递归查询当前节点的所有父节点，当前节点的pid字段 = 父节点的id字段，
          所以 prior后面填写 pid字段， connect by prior pid = id 
*/
SELECT *
FROM TB 
START WITH id = 1
CONNECT BY PRIOR id = pid;

-- 6.查询id=18节点的所有父节点信息
SELECT *
FROM TB t 
START WITH id = 18
CONNECT BY PRIOR pid = id;

/*
 * 7.伪列 level 
 *   含义：表示节点在递归中所处的层级
 *   在递归中起始位置的层级是1，起始位置下一个节点的层级是2，以此类推
 *   
 *   注意：起始位置不同，相同的节点层级也会不同，
 *        如果需要比较层级，要在同一个起始位置进行比较
 * 		  level只能在递归查询中使用
 *   特点：每个递归查询语句都有自己的level，且子查询和主查询中的level互不影响
 * 
 * */
SELECT t.*,level
FROM TB t 
START WITH id = 18
CONNECT BY PRIOR pid = id;

SELECT t.*,level
FROM TB t 
START WITH id = 7
CONNECT BY PRIOR pid = id;

-- 查询从根节点开始和id=7同一个层级的所有节点信息
-- 先从根节点开始，查询id=7节点所在层级
SELECT
	t.*,
	LEVEL lv
FROM
	TB t
WHERE
	id = 7
START WITH
	pid = 0
CONNECT BY
	PRIOR id = pid;

-- 格式化代码 ctr+shift+f
-- 多条执行 alt+x
-- 在查询和id= 7同一层级的数据
SELECT t.*,
       LEVEL lv
FROM   TB t
WHERE  LEVEL = (SELECT LEVEL lv
                FROM   TB t
                WHERE  id = 7
                START  WITH pid = 0
                CONNECT BY PRIOR id = pid)
       AND id != 7
START  WITH pid = 0
CONNECT BY PRIOR id = pid;

/*
 * 8.递归查询函数
 *   sys_connect_by_path(字段,'分隔符')：显示字段的递归路径，并用指定分隔符进行分隔
 *   只能在递归查询中使用
 * 
 * */
SELECT
	SYS_CONNECT_BY_PATH(name,'/')  AS path
FROM
	TB t
START WITH
	pid = 0
CONNECT BY
	PRIOR id = pid;

/* 递归查询在工作中的应用场景，在处理地理单元时需要使用递归查询将地理单元进行整理
 * 比如：省市县层级的递归
 * */