【黑马】Oracle数据库学习笔记

君擎

已于 2025-04-05 14:22:56 修改

阅读量1k

点赞数 30

文章标签：数据库 oracle

于 2025-03-17 02:23:43 首次发布

本文链接：https://blog.csdn.net/qq_64225133/article/details/146305052

版权

Oracle数据库

笔记基于视频教程：黑马程序员 Oracle 数据库精讲，从 0 到 1 学会 Oracle 数据库

一、Oracle 数据库相关概念

数据库
在 MySQL 中，我们可以创建许多个数据库，而在 Oracle 中，我们只能在安装的时候创建一个数据库。
实例
在使用 Oracle 数据库的时候，如果我们需要一台机器上用多个库怎么办，是不是需要安装多个 Oracle 数据库？Oracle 数据库为了解决这个问题，引入了实例的概念，每个实例就是一个数据库，他们之间互不干扰，类似于 java 的类与对象的关系，我们写代码的使用只用定义一个类，但是可以创建这个类的多个对象。
数据文件(dbf)
数据文件就是 Oracle 数据库存储数据的文件。
表空间

表空间是 Oracle 对数据文件的逻辑映射。一个数据库在逻辑上被划分成一个到多个表空间。每个数据库至少有一个表空间。每个表空间由同一磁盘上的一个或多个文件组成。具体结构如下图：
用户

用户是在表空间下建立的，用户登录后只能看到和操作自己的表。

在 MySQL 中，一个用户可以有很多库。而在 Oracle 中，一个库可以有很多用户，一个用户只能对应一个库。

二、基于 Docker 安装 Oracle

拉取镜像

docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

下载完成之后可以查看一下是否拉取镜像成功
```
docker images
```

创建容器

#-d：后台运行容器。
#--privileged: 允许挂载数据卷，默认是读写权限rw
#-p 1521:1521：将容器的 1521 端口映射到宿主机的 1521 端口。
#--name oracle11g：为该容器命名为 oracle11g。
#oracle 数据文件挂载：-v /data/dockerData/oracle:/data/oracle，将容器中的数据文件夹 /data/oracle 挂载到宿主机对应的 /data/dockerData/oracl 文件夹中。
#registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g：使用之前拉取的镜像。
docker run   -d --privileged -p 1521:1521 --name oracle11g    --restart=always -v /home/oracle:/data/oracle  registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

进入容器内部进行配置

#1.进入容器
docker exec -it oracle11g bash

#2.切换到root用户
su root

#3.输入密码：默认helowin

#4.配置环境变量
vi /etc/profile

#5.在文末添加以下内容
#设置 Oracle 数据库的安装目录
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
#设置 Oracle 实例名
#这里设置为默认的实例名 "helowin"，我尝试修改，但是不管用
export ORACLE_SID=helowin
# 将 ORACLE_HOME/bin 添加到 PATH 环境变量中
export PATH=$ORACLE_HOME/bin:$PATH

#6.保存后，在容器内重新加载环境变量
source /etc/profile

#7.在容器内创建软连接
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin

#8.切换到oracle用户
su - oracle

#9.使用dba用户登录数据库，创建普通用户
#命令启动 sqlplus
sqlplus /nolog
#连接数据库并使用 SYSDBA 权限
conn /as sysdba
#修改管理员账户system的密码
# 这里将密码设置为 123456（可以自行修改为更复杂的密码）
alter user system identified by 123456;
#修改管理员账户sys密码
alter user sys identified by 123456;
#创建普通用户test并设置其密码为123456
create user test identified by 123456;
#这里我们为新创建的 test 用户授予了 DBA 权限，使其能够进行数据库的管理操作，可以根据实际需求调整授予的权限
grant connect, resource, dba to test;
#设置密码永不过期，Oracle 默认有一个密码过期策略，这里我们将密码策略设置为永不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
#修改数据库最大连接数，需要重启数据库后才能生效
alter system set processes=1000 scope=spfile;

#10.在容器内重启数据库
conn /as sysdba
#关闭数据库
shutdown immediate;
#启动数据库
startup;

使用数据库管理工具测试是否可以连接

三、数据库初始化流程

创建表空间

#第一次使用数据库的时候一定要给数据库创建表空间
#这个SQL的意思是创建表空间，表空间的位置是哪里，表空间的初始大小是多少，表空间满了选择扩容模式，每次扩容10M
create tablespace waterboss
datafile '数据文件名.dbf'
size 100M
autoextend on
next 10M;

创建用户,授予权限

#创建用户，指定密码，指定表空间
create user 用户名
identified by 密码
default tablespace waterboss;

#授予用户dba权限
grant dba to 用户名;

创建表

--建立价格区间表
create table t_pricetable
(
id number primary key,
price number(10,2),
ownertypeid number,
minnum number,
maxnum number
);
--业主类型
create table t_ownertype
(
id number primary key,
name varchar2(30)
);
--业主表
create table t_owners
(
id number primary key,
name varchar2(30),
addressid number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);
--区域表
create table t_area
(
id number,
name varchar2(30)
);
--收费员表
create table t_operator
(
id number,
name varchar2(30)
);
--地址表
create table t_address
(
id number primary key,
name varchar2(100),
areaid number,
operatorid number
);
--账务表--
create table t_account 
(
id number primary key,
owneruuid number,
ownertype number,
areaid number,
year char(4),
month char(2),
num0 number,
num1 number,
usenum number,
meteruser number,
meterdate date,
money number(10,2),
isfee char(1),
feedate date,
feeuser number
);
create sequence seq_account;

插入数据

--业主类型
insert into t_ownertype values(1,'居民');
insert into t_ownertype values(2,'行政事业单位');
insert into t_ownertype values(3,'商业');
--地址信息--
insert into t_address values( 1,'明兴花园',1,1);
insert into t_address values( 2,'鑫源秋墅',1,1);
insert into t_address values( 3,'华龙苑南里小区',2,2);
insert into t_address values( 4,'河畔花园',2,2);
insert into t_address values( 5,'霍营',2,2);
insert into t_address values( 6,'回龙观东大街',3,2);
insert into t_address values( 7,'西二旗',3,2);
--业主信息
insert into t_owners values(1,'范冰',1,'1-1','30406',to_date('2015-04-12','yyyy-MM-dd'),1 );
insert into t_owners values(2,'王强',1,'1-2','30407',to_date('2015-02-14','yyyy-MM-dd'),1 );
insert into t_owners values(3,'马腾',1,'1-3','30408',to_date('2015-03-18','yyyy-MM-dd'),1 );
insert into t_owners values(4,'林小玲',2,'2-4','30409',to_date('2015-06-15','yyyy-MM-dd'),1 );
insert into t_owners values(5,'刘华',2,'2-5','30410',to_date('2013-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(6,'刘东',2,'2-2','30411',to_date('2014-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(7,'周健',3,'2-5','30433',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(8,'张哲',4,'2-2','30455',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(9,'昌平区中西医结合医院',5,'2-2','30422',to_date('2016-10-11','yyyy-MM-dd'),2 );
insert into t_owners values(10,'美廉美超市',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 );
--操作员
insert into t_operator values(1,'马小云');
insert into t_operator values(2,'李翠花');
--地区--
insert into t_area values(1,'海淀');
insert into t_area values(2,'昌平');
insert into t_area values(3,'西城');
insert into t_area values(4,'东城');
insert into t_area values(5,'朝阳');
insert into t_area values(6,'玄武');
--价格表--
insert into t_pricetable values(1,2.45,1,0,5);
insert into t_pricetable values(2,3.45,1,5,10);
insert into t_pricetable values(3,4.45,1,10,null);
insert into t_pricetable values(4,3.87,2,0,5);
insert into t_pricetable values(5,4.87,2,5,10);
insert into t_pricetable values(6,5.87,2,10,null);
insert into t_pricetable values(7,4.36,3,0,5);
insert into t_pricetable values(8,5.36,3,5,10);
insert into t_pricetable values(9,6.36,3,10,null);
--账务表--
insert into t_account values( seq_account.nextval,1,1,1,'2012','01',30203,50123,0,1,sysdate,34.51,'1',to_date('2012-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','02',50123,60303,0,1,sysdate,23.43,'1',to_date('2012-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','03',60303,74111,0,1,sysdate,45.34,'1',to_date('2012-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','04',74111,77012,0,1,sysdate,52.54,'1',to_date('2012-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','05',77012,79031,0,1,sysdate,54.66,'1',to_date('2012-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','06',79031,80201,0,1,sysdate,76.45,'1',to_date('2012-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','07',80201,88331,0,1,sysdate,65.65,'1',to_date('2012-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','08',88331,89123,0,1,sysdate,55.67,'1',to_date('2012-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','09',89123,90122,0,1,sysdate,112.54,'1',to_date('2012-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','10',90122,93911,0,1,sysdate,76.21,'1',to_date('2012-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','11',93911,95012,0,1,sysdate,76.25,'1',to_date('2012-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','12',95012,99081,0,1,sysdate,44.51,'1',to_date('2013-01-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','01',30334,50433,0,1,sysdate,34.51,'1',to_date('2013-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','02',50433,60765,0,1,sysdate,23.43,'1',to_date('2013-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','03',60765,74155,0,1,sysdate,45.34,'1',to_date('2013-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','04',74155,77099,0,1,sysdate,52.54,'1',to_date('2013-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','05',77099,79076,0,1,sysdate,54.66,'1',to_date('2013-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','06',79076,80287,0,1,sysdate,76.45,'1',to_date('2013-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','07',80287,88432,0,1,sysdate,65.65,'1',to_date('2013-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','08',88432,89765,0,1,sysdate,55.67,'1',to_date('2013-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','09',89765,90567,0,1,sysdate,112.54,'1',to_date('2013-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','10',90567,93932,0,1,sysdate,76.21,'1',to_date('2013-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','11',93932,95076,0,1,sysdate,76.25,'1',to_date('2013-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,100,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-01','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,101,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2015-01-01','yyyy-MM-dd'),2 );
update t_account set usenum=num1-num0;
update t_account set money=usenum*2.45;
commit;

四、数据类型

类型
字符类型
	CHAR	固定长度的字符类型 (存储的长度不够会用空格补齐)	最大存储 2000 个字节
	VARCHAR2	可变长度的字符类型 (存储的长度不够不会用空格补齐)	最大存储 4000 个字节
	LONG	大文本类型	最大存储 2 个 G
数值型
	NUMBER	NUMBER	最大可以存储 18 位
		NUMBER(5)	最大可以存储 99999
		NUMBER(5,2)	最大可以存储 999.99
日期型
	DATE	日期时间型	精确到秒
	TIMESTAMP		精确到秒的后 9 位
二进制型
	CLOB	存储字符(为了弥补 Long 的大小缺陷)	最大可以存储 4 个 G
	BLOB	存储图像、省心、视频等二进制数据	最大可以存储 4 个 G

五、DDL 语句(数据定义语言)

创建表

--[xx]是可省略内容
create table 表名(
	列名 数据类型[长度] [约束],
	...
);

修改表

--2.1修改表名
ALTER TABLE 原表名 RENAME TO 新表名;

--2.2删除表
drop table 表名;

--2.3追加字段
alter table 表名 add(
	列名 数据类型[长度] [约束],
	...
);

--2.4修改字段
alter table 表名 modify (
	列名 数据类型[长度] [约束],
	...
);

--2.5修改字段名
ALTER TABLE 表名 RENAME column 原列名 TO 新列名;

--2.6删除字段
alter table 表名 drop column 列名1,列名2...;

六、DML 语句(数据操作语言)

增删改查都是 DML 语句，但是由于查询较为复杂，因此这里只写了增删改。

增加数据

--[xx]是可省略内容
insert into 表名 [(列名1,列名2...)] values(值1,值2,值3...);

修改数据

update 表名 set 字段1=值1,
				字段2=值2,
				...
 where 条件;

删除数据

delete from 表名 where 条件;

truncate table 表名;

/*说明：
	1.delete：删除数据是把表里的数据移动到了回滚段里面了，因此可以rollback回滚。所以delete删除可能产生碎片，并且不释放空间。
	2.truncate：是先摧毁表结构，然后再重建表结构，因此不可以回滚，也不需要提交事务。
	3.整表删除truncate效率更高。
*/

七、程序连接 Oracle 数据库

创建一个 SpringBoot 工程

引入依赖(这里用的是 java21 的)

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.4</version>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc11</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

配置数据源

spring:
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521/SID
    username: 用户名
    password: 密码
    driver-class-name: oracle.jdbc.OracleDriver

编写测试 controller

八、数据迁移-数据导出与导入

整库导出/导入命令

--整库导出 参数 full=y 表示整库导出
exp dba用户名/dba密码 full=y [file=文件名.dmp]

--整库导入 [已经存在的对象不会继续导入]
imp dba用户名/dba密码 full=y [file=文件名.dmp]

按用户导入/导出

--整库导出 参数 fowner=用户名 表示导出指定用户的下的表
exp dba用户名/dba密码 owner=用户名 [file=文件名.dmp]

--整库导入 [已经存在的对象不会继续导入]
imp dba用户名/dba密码 owner=用户名 [file=文件名.dmp]

按表导入/导出

--整库导出 参数 tables=表名1，表名2... 表示导出指定表
exp 普通用户名/密码 tables=表名1，表名2... [file=文件名.dmp]

--整库导入 [已经存在的对象不会继续导入]
imp 普通用户名/密码 tables=表名1，表名2... [file=文件名.dmp]

九、单表查询

大多数同 MySQL 一样，不过多花费时间记录。只记录之前没有了解过的。

基于伪列的查询

伪列：在的表使用过程中，我们没有自己去创建，但是实际存在的附加列。伪列只能查询不能进行增删改操作。这里只记录两个伪列的使用：RowID 和 RowNum。
1. ROWID
  
  表中的每一行数据在数据文件中都有一个物理地址。ROWID 返回的是改行的物理地址。ROWID 值可以唯一的标识表中的一行。
```
select rowid,a.* from t_pricetable a;
```
2. ROWNUM
  
  在查询的结果集中，ROWNUM 为查询结果集中每一行标识一个行号。确定结果集之后就赋予ROWNUM了
```
select rownum,a.* from t_pricetable a;
```

十、多表连接查询

内连接

--只显示两个表关联上的信息
select a.*,b.* from a,b where a.xxx=b.xxx;

左外连接

--左表信息不管是否关联上全部显示
--写法1
select a.*,b.* from a left join b on a.xxx=b.xxx where b.xxx is null;
--写法2
select a.*,b.* from a,b where a.xxx=b.xxx(+);

右外连接

--右表信息不管是否关联上全部显示
--写法1
select a.*,b.* from a right join b on a.xxx=b.xxx where a.xxx is null;
--写法2
select a.*,b.* from a,b where a.xxx(+)=b.xxx;

十一、子查询

写法和MySQL一样，不作详细记录。

where子句中的子查询
from子句中的子查询
select子句中的子查询

十二、分页查询

简单分页查询

--查询第1页记录，每页10条
SELECT *  FROM t_account  WHERE ROWNUM <= 10;
--查询第2页记录，每页10条
SELECT *  FROM t_account 
WHERE ROWNUM <= 20 
  AND ROWNUM > 10;--这个是错误的，查不出来数据
/*特别说明：rownum后面的运算符只能是 < 或<= */--问题：那么查询第2页记录，每页10条，这个该怎么用呢？
--答：可以用子查询：先查询所有rownum，然后把这个语句作为子查询
SELECT *  FROM ( SELECT ROWNUM AS r, a.* FROM t_account a ) b 
WHERE b.r > 10 
  AND b.r <= 20;

基于排序的分页查询

--要求：查询排序后查询第1页记录，每页10条
select * from (select rownum as r,a.* from t_account a order by usenum desc) b where b.r>10 and b.r<=20;--错误语句

/*说明：rownum 在确定结果之后就赋值了，优先级高于order by 因此出来的结果不对*/
--解决方法：再多套一层
SELECT *  FROM ( SELECT ROWNUM AS r, a.* FROM (select * from t_account ORDER BY usenum DESC) a ) b 
WHERE b.r <= 10;

十三、常用函数

字符函数

与字符串运算相关的函数
1. 获取字符串长度：LENGTH('字符串')
```
select length('abc') from dual;--结果：3
```
2. 截取字符串内容：substr('字符串',截取开始位置,截取长度)
```
--位置从1开始，不是从0开始
select substr('1234567890',2,3) from dual;--结果：234
```
3. 字符串拼接：concat('字符串1','字符串2')或||
```
select concat('1','23') from dual;--结果：123

--说明：也可以使用 || 进行拼接
select '1'||'23' from dual;--结果：123
```
4. 其他方法参见下面的图片，就不一一举例了

数值函数

四舍五入函数：round(数字,[保留小数位])

--1.四舍五入返回整数
select round(123.546) from dual;--返回124
--1.四舍五入返回2位小数
select round(123.546,2) from dual;--返回123.55

数字截取：trunc(数字)

--1.截取整数位
select trunc(123.546) from dual;--返回123
--1.截取整数位后的两位
select round(123.546,2) from dual;--返回123.54

取模：mod(被除数,除数)
```
select round(10,3) from dual;--返回1
```
其他方法参见下面的图片，就不一一举例了

日期函数

获取系统当前日期和时间：sysdate

select sysdate from dual;--年月日 时分秒

加月函数：add_months(时间,多少个月)

select add_months(sysdate,3) from dual;--当前时间加三个月

日期所在月的最后一天：last_day(日期)
```
select last_day(sysdate) from dual;
```

日期截取trunc(日期)

select trunc(sysdate) from dual;--截取掉时间，只剩下年月日 2025-03-19
select trunc(sysdate,'mm') from dual;--截取掉月份后面的,返回当月第一天 2025-03-01
select trunc(sysdate,'yyyy') from dual;--截取掉年后面的,返回当年第一天 2025-01-01

转换函数

数字转字符串：to_char(数字)

--方式1
select to_char(100) from dual;--将数字转为字符串

--方式2
select 100 ||'' from dual;--将数字转为字符串

日期转字符串：to_char(日期,'格式')

select to_char(sysdate，'yyyy-mm-dd') from dual;--转化为年月日
select to_char(sysdate，'yyyy') from dual;--转化为年
select to_char(sysdate，'mm') from dual;--转化为月
select to_char(sysdate，'dd') from dual;--转化为日
select to_char(sysdate，'yyyy-mm-dd hh:mi:ss') from dual;--转化为年月日

字符串转日期：to_date('日期字符串','格式')

select to_date('2025-03-19'，'yyyy-mm-dd') from dual;--转化字符串为日期

字符串转数字：to_number('字符串')

--方式1
select to_number('100') from dual;--转化字符串为数字

--方式2
select '100'+0 from dual;--转化字符串为数字

其他方法参见下面的图片，就不一一举例了

其他函数

空值处理函数：nvl(需要检测的值,为null返回值)或nvl2(需要检测的值,不为null返回值,为null返回值)

--nvl(需要检测的值,为null返回值)：如果被检测值是空，则返回设置的默认值
select nvl(null,100) from dual;--返回100

--nvl2(需要检测的值,不为null返回值,为null返回值)：如果被检测值是空的，返回一个值，如果不为空，返回另一个值
select nvl2(null,100,200) from dual;--返回200
select nvl2(399,100,200) from dual;--返回100

条件取值：decode(条件,值1,返回值1,值2,返回值2...都不合符返回的默认值)

--decode(条件,值1,返回值1,值2,返回值2...都不合符返回的默认值):比较条件跟后面的哪个值匹配，如果匹配上了返回对应的值，否则返回设置的默认值
select decode(4,
				1,100,
				2,200,
				3,300,
				500) from dual;--匹配不上，返回500

条件取值：case when then

--case when then end类似于java中的switch-case，可以达到decode的效果
--写法1
select name,(case zhiye
				when 1 then '老师'
				when 2 then '学生'
				when 3 then '宿管'
				else '其他'
				end) from t_owners；
--写法2
select name,(case
				when zhiye=1 then '老师'
				when zhiye=2 then '学生'
				when zhiye=3 then '宿管'
				else '其他'
				end) from t_owners；

十四、行列转换

可以使用decode或case-when，不满足条件的不进行计算，具体如下图

十五、分析函数

值相同，排名相同，后面的序号跳跃

说明：值相同的，他们的排名就相同，如果出现值不同的了，排名直接跳着显示。

案例：班里期末考试，1个100分，2个98分，3个95分，问98分排第几名，95分排第几名

答：98分并列第二名，95分并列第四名
```
--rank() over(排序语句)函数
select rank() over (order by usenum desc) 排名,a.* from t_account a;
```
值相同，排名相同，后面的序号连续

说明：值相同的，他们的排名就相同，如果出现值不同的了，排名接着上面的继续。

案例：班里期末考试，1个100分，2个98分，3个95分，问98分排第几名，95分排第几名

答：98分并列第二名，95分并列第3名
```
--dense_rank over(排序语句)函数
select dense_rank() over (order by usenum desc) 排名,a.* from t_account a;
```

序号连续，不管值是否相同

说明：值相同，但是排名不一样，依次排下去

案例：班里期末考试，1个100分，2个98分，3个95分，问98分排第几名，95分排第几名

答：98分排第2，3名，95分并第4，5，6名

--row_number() over(排序语句)函数
select row_number() over (order by usenum desc) 排名,a.* from t_account a;

--拓展：这种可以替代rownum进行分页查询
select * from (
select row_number() over (order by usenum desc) 排名,a.* from t_account a) 
where 排名>=10 and 排名<20;

十六、集合运算

把两个集合进行交、并、差运算

并集（不去重）：union all

两个结果集合并，结果集中包含重复元素
```
select * from a
union all
select * from b
```
并集（去重）：union

两个结果集合并，结果集中不包含重复元素
```
select * from a
union
select * from b
```
交集：intersect

获取两个结果集的重复记录
```
select * from a
intersect
select * from b
```

差集：minus

从一个结果集中减掉另一个结果集的数据

--a[1,2,3,4,5] b[4,5,6] 结果：1，2，3
select * from a
minus
select * from b

--拓展:可以用差集分页
--获取第2页数据：用前20条数据-前10条数据
select * from a where a.rownum<=20
minus
select * from a where a.rownum<=10

十七、视图

视图是一个虚拟的表，不会真正的存储数据，而是根据创建时的SQL语句去显示相应的数据。

简单来说，视图就是对一个SQL语句的封装，查询的时候不用去查询具体的SQL，直接查询视图即可。因此视图里的内容都是最新的数据。

视图的作用：

简化数据操作
可以只显示需要的数据，其他不必要或者敏感的字段不用去显示

特别说明：视图不会存储实际数据，改了视图内容，对应表的内容也会被修改

1.创建或修改视图语法

#1.创建、修改视图语句 []的参数是可选的
create [or replace] [force] view 视图名
as 查询语句
[with check option]
[with read only] 

#2.查询视图内容
跟表的使用方法一致
#3.删除视图语句
drop view 视图名

#2.选项解释
or replace : 如果创建的视图已经存在，则自动重建该语句。
force : 强制创建语句（建表的时候不去检查使用到的表是否存在）
with check option ： 设置检查，修改视图的时候，修改后的内容必须符合设置的条件
with read only : 设置视图只读（不加视图默认可以进行DML操作）

2.案例一：简单视图

#1.创建一个简单视图
create view owners1
as select * from t_owners where ownertypeid=1;

#2.查询视图内容
select * from owners1 where addressid=1;

#3.修改视图数据
update owners1 set name ='范小冰' where id =1;

#4.查询实际表的内容【会被修改】
select * from t_owners where id=1;

3.案例二：带检查约束的视图

#1.创建一个带检查约束的视图
create view owners2
as select * from t_owners where ownertypeid=1
with check option;

#2.修改约束[会报错：因为视图创建的条件是ownertypeid=1，不允许修改创建条件]
update owners2 set ownertypeid =2;

3.案例三：只读视图

#1.创建一个只读视图
create view owners3
as select * from t_owners where ownertypeid=1
with read only;

#2.会报错，因为视图只能读，不能修改
update owners3 set name='123';

4.案例四：强制创建视图

--某个表不存在，我们强制创建这个视图
create force view owners4
as select * from adcd;

4.案例五：多表关联的复杂视图

#1.创建多表关联复杂视图
create view owners5 as
  select * from t_owners a, t_ownertype b where a.ownertypeid = b.id;

#2.修改复杂视图的某列【视图主键所在表的内容可以修改】
update owners5 set name='林玲' where id='4';

#3.视图中保留的主键所在表的内容才能修改【owners5的主键是owners表的id，因此ownertype表的内容不能修改】
update owners5 set ownertypename ='行政事业单位123' where ownertypeid='3';

5.案例六、聚合统计的复杂视图

#1.创建一个聚合统计的复杂视图
create or replace view owners6
as select year,month,sum(money) money from t_account group by year,month order by year,month;

#2.聚合统计的复杂视图里面的内容不可以修改

十八、物化视图

视图是一个虚拟表不会存储内容，物化视图是一个实际表，会将查询到的内容存储到一个实际表中。因此物化视图也可以叫实体化视图。

作用：提高查询性能。多表关联创建的视图和物化视图，物化视图的性能更好。

缺点：会占用空间

#1.基本SQL结构
create materialized view 视图名
[build immediate | build deferred]
[refresh fast | complete | force]
[on [commit | demand] | start with (start_time) next (next_time)]
as 查询语句

#2.参数说明
build immediate ：创建视图时就生成数据
build deferred ：在创建视图的时候不生成数据，视图是空的
refresh xxx :视图数据刷新频率，【默认参数：force】。
	fast：增量更新，原表操作的增删改操作会生成物化日志，根据物化日志对对应的数据进行更新
	complete：全量更新，每次增删改都会删除视图的所有数据，然后重新插入数据
	force ： 自动选择增量还是全量，默认是增量
on xxx :在什么操作时刷新视图【默认参数：commit】
	commit :在提交事务时刷新
	demand :手动刷新

1.案例一：创建手动刷新的物化视图

#1.创建物化视图
create materialized view mv1
as select a.id,a.name name1,b.name name2 from t_address a,t_area b where a.areaid=b.id;

#2.删除物化视图
drop materialized view mv1;

#3.查询物化视图
select * from mv1;

#4.手动刷新视图数据
insert into t_address values (8,'西三旗',2,2);
commit;
--执行手动刷新物化视图语句【底层是调用了存储过程】
begin
	--参数：视图名，刷新频率首字母
  DBMS_MVIEW.refresh('mv1','C');
end;

2.案例二：创建自动刷新的物化视图

#1.创建物化视图[基表提交事务后自动刷新视图]
create materialized view mv2
refresh
on commit
as select a.id,a.name name1,b.name name2 from t_address a,t_area b where a.areaid=b.id;

3.案例三：创建时不包含数据的物化视图

#1.创建不包含数据的物化视图
create materialized view mv3
build deferred
refresh
on commit
as select a.id,a.name name1,b.name name2 from t_address a,t_area b where a.areaid=b.id;

#2.查询视图是否包含数据{不包含}
select * from mv3;

#3.第一次刷入数据需要执行存储过程
begin
	--参数：视图名，刷新频率首字母
  DBMS_MVIEW.refresh('mv3','C');
end;

#4.后续会根据设置刷新数据

4.方案四：创建手动增量更新的物化视图

#1.创建增量更新的物化视图前需要先创建物化视图日志
--这里根据rowid记录
create materialized view log on t_address with rowid;
create materialized view log on t_area with rowid;

#2.创建增量更新的物化视图【根据rowid创建日志时，创建视图语句也必须要有rowid】
create materialized view mv4
refresh fast
as select a.rowid arowid,b.rowid browid,a.id,a.name name1,b.name name2 from t_address a,t_area b where a.areaid=b.id;

#3.查看视图【已经有数据】
#4.增加数据，查看视图是否新增上了【不会】
#5.手动刷入数据

十九、序列

序列是一个用来产生一系列唯一数字的对象

#1.创建简单序列语句
create sequence 序列名;

#2.创建复杂序列
create sequence 序列名
	[increment by n] //每次增长的值，默认是1
	[start with n] //开始的值
	[maxvalue n] //最大值
	[minvalue n] //最小值
	[cycle | nocycle] //循环、不循环
	[cache n | nocache] //是否保存到内存中

#3.获取序列的下一个值
select 序列名.nextval from dual;

#4.获取序列的当前值
select 序列名.currval from dual;

#5.删除序列
drop sequence 序列名;

案例

#1.创建非循环有最大值的序列
create sequence seq_test1 maxvalue 20;

#2.使用序列
select seq_test1.nextval from dual;

二十、同义词

给Oracle的对象起一个别名，后续可以直接使用

#1.语法
create [public] synonym 同义词名字 for Oracle的对象;

#2.说明
默认是私有同义词，私有同义词只能创建用户使用，共有同义词可以供所有用户使用。

案例

--给表t_owners创建一个私有同义词
create SYNONYM owner for t_owners;
--使用同义词
select * from owner;

二十一、索引

索引是用于加快数据查询速度的对象。合理的使用索引可以大大降低i/o次数。

查询一个没有索引的表，会逐行扫描数据。建了索引之后会根据索引中存储的内容去查询。

#1.语法
create index 索引名 on 表名(列名...);

案例一：创建普通索引

create index index1 on t_owners(name);

案例二：创建唯一索引

create unique index index2 on t_owners(watermeter);

案例三：创建复合索引

create index index3 on t_owners(id,name,addressid);

案例三：反向键索引

在oracle数据库中如果给一组连续的数字建立索引会形成不平衡的二叉树，根节点只有左子树，没有右子树，这样查询起来还是不快。

反向键索引：将数字对应的二进制按位取反，然后取其对应的十进制数

create index 索引名 on 表名(列名) reverse;

案例四：位图索引

可以理解为将所有数据分类后放到每类对应的图片上，需要的时候直接返回图片上的所有内容。

例如：性别有两类，我们给性别建了位图索引，如果需要查询性别为男性的人员信息，直接将男性分类的内容全部返回

只用于低基数的列：列的取值范围是有限的。比如：性别、民族

缺点：

只能使用等值查询，不能范围查询

create bitmap index 索引名称 on 表名(列名);

二十二、PL/SQL

PL/SQL介绍：

是Oracle对SQL语言的过程化扩展，在SQL命令中增加了循环、判断等过程性语句，使得它可以同其他编程语言一样具有过程处理能力。

1.基本语法

[declare
	--声明变量
]
begin
	--代码逻辑
	[exception
		--处理异常
	]
end;

2.变量

声明变量的语法
```
变量名 类型(长度);
```

变量赋值语法

--语法1
变量名 :=值;

--语法2：将查询出来的值赋予某个变量[要求：查询结果必须是一行，如果是多行会报错]
select 列名1,列名2... into 变量名1，变量名2... from 表

案例

/*
题目要求:
声明变量:水费单价、水费字数、吨数、金额。
对水费单价、字数、进行赋值。
吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。
计算金额，金额=单价*吨数。
输出单价、数量和金额。
*/

--方式1
declare 
  --单价
  price number(10,2);
  --水费字数
  usenum number;
  --吨数
  heavy number(10,2);
  --金额
  amount number(10,2);
begin
  price :=5;--5元/吨
  usenum :=9213;
  heavy :=round(usenum/1000,2);
  amount :=price*heavy;
  DBMS_Output.put_line('金额' || amount);
end;

--方式2
declare 
  --单价
  price number(10,2);
  --水费字数
  usenum number;
  --吨数
  heavy number(10,2);
  --金额
  amount number(10,2);
begin
  price :=5;--5元/吨
  select usenum into usenum from t_account where year='2012' and month='01' and owneruuid=1;
  heavy :=round(usenum/1000,2);
  amount :=price*heavy;
  DBMS_Output.put_line('金额' || amount);
end;

3.属性类型

定义变量的时候不知道字段具体是什么类型，但是知道这个变量的值是根据某个表的某一列来的，那么可以使用属性类型引用，给变量引用对应字段的类型。

有两种属性类型：

引用型：给某个字段设置引用类型

语法：表名.列名%type

记录型：把整个表的变量类型值赋予某个变量，作用类似于java中的实体类

语法：表名%rowtype

使用：变量名.列名

案例

--案例1：引用型
declare 
  --单价
  price number(10,2);
  --水费字数
  usenum t_account.usenum%type;
  --吨数
  heavy number(10,2);
  --金额
  amount number(10,2);
begin
  price :=5;--5元/吨
  select usenum into usenum from t_account where year='2012' and month='01' and owneruuid=1;
  heavy :=round(usenum/1000,2);
  amount :=price*heavy;
  DBMS_Output.put_line('金额' || amount);
end;

--记录型
declare 
  --单价
  price number(10,2);
  --水费字数
  usenum t_account.usenum%type;
  --吨数
  heavy number(10,2);
  --金额
  amount number(10,2);
  --记录类型
  accountDto t_account%rowType;
begin
  price :=5;--5元/吨
  select * into accountDto from t_account where year='2012' and month='01' and owneruuid=1;
  heavy :=round(accountDto.usenum/1000,2);
  amount :=price*heavy;
  DBMS_Output.put_line('金额' || amount);
end;

4.异常

程序运行时出现的错误叫做异常。

异常有两种类型：

预定义异常：当PL/SQL程序违反Oracle数据库的规则而发生的报错，Oracle数据库中提供了21个异常
用户定义异常：可以在PL/SQL的声明部分定义异常，自定义的异常通过RAISE语句显示触发

异常处理语法

exception
	when 异常类型 then
		异常处理逻辑代码

使用案例

declare 
  --单价
  price number(10,2);
  --水费字数
  usenum t_account.usenum%type;
  --吨数
  heavy number(10,2);
  --金额
  amount number(10,2);
  --记录类型
  accountDto t_account%rowType;
begin
  price :=5;--5元/吨
  select * into accountDto from t_account where year='2012' and month='01' and owneruuid=5000;
  heavy :=round(accountDto.usenum/1000,2);
  amount :=price*heavy;
  DBMS_Output.put_line('金额' || amount);
  
  exception 
    when no_data_found then
      DBMS_Output.put_line('查询错误' || '没有查询到数据');
end;

5.条件判断

语法

--语法1
if 条件 then
	业务逻辑
end if;

--语法2
if 条件 then
	业务逻辑
else
	业务逻辑
end if;

--语法3
if 条件 then
	业务逻辑
elsif then
	业务逻辑
else
	业务逻辑
end if;

案例

/*
设置三个等级的水费
5吨以下2元/吨
5吨到10吨部分3元/吨
超过10吨部分4，
根据使用水费的量来计算阶梯水费
*/
declare 
  --单价:5吨以下2元/吨;5吨到10吨部分3元/吨;超过10吨部分4
  price1 number(10,2);
  price2 number(10,2);
  price3 number(10,2);
  --吨数
  heavy number(10,2);
  --金额
  amount number(10,2);
  --记录类型
  accountDto t_account%rowType;
begin
  price1:=2;
  price2:=3;
  price3:=4;
  heavy :=15;
  if (heavy<=5) then
     amount:=price1*heavy;
  elsif (heavy>5 and heavy<=10) then
     amount:=price1*5+price2*(heavy-5);  
  else
     amount:=price1*5+price2*5+price3*(heavy-10);
  end if;
  dbms_output.put_line('金额：' ||amount);
end;

6.循环

无条件循环

基本语法

loop
	--循环语句
	exit;--退出语句
end loop；

基本案例

--输出1-100
declare
  tempNum number;
begin
  tempNum := 0;
  loop
    if tempNum < 100 then
      tempNum := tempNum + 1;
      Dbms_Output.put_line(tempNum);
    else
      exit;
    end if;
  end loop;
end;

条件循环

基本语法

while 条件
loop
	--循环体
end loop;

基本案例

declare
  tempNum number;
begin
  tempNum := 0;
  while tempNum < 100 loop
    tempNum := tempNum + 1;
    Dbms_Output.put_line(tempNum);
  end loop;
end;

for循环

基本语法

--说明：for 后面的变量不需要被声明
for 变量 in 被循环对象
loop
	--循环体
end loop;

基本案例

begin
  for v_num in 1 .. 100 
    loop
      Dbms_Output.put_line(v_num);
    end loop;
end;

7.游标

游标就是一个结果集，用来存放查询结果，这个结果集可以用来循环，可以理解成java里面的增强for循环

基本语法

--1.声明游标
cursor 游标名 is SQL语句;

--2.带参数的游标
cursor 游标名(参数名 类型) is SQL语句;

--3.使用游标
open 游标名
loop
	fetch 游标名 into 变量；--读取一条内容到变量中
	exit when 游标名称%notfound; --游标没有内容之后关闭不在循环游标
	--相关操作
endloop;
close 游标名

基本案例【看一下就行，实际还是用for循环】

--需求：打印业主类型为1的价格表
declare
  cursor homeUserDtos is
    select * from t_pricetable where ownertypeid = 1;
  homeUserDto t_pricetable%rowtype;

begin
  open homeUserDtos;
  loop
    fetch homeUserDtos
      into homeUserDto;
    exit when homeUserDtos%notfound;
    dbms_output.put_line('业主信息：' || homeUserDto.id);
  end loop;
  close homeUserDtos;
end;

带参数的游标

--需求：打印业主类型为1的价格表
declare
  cursor homeUserDtos(v_ownertypeid number) is
    select * from t_pricetable where ownertypeid = v_ownertypeid;
  homeUserDto t_pricetable%rowtype;

begin
  open homeUserDtos(1);
  loop
    fetch homeUserDtos
      into homeUserDto;
    exit when homeUserDtos%notfound;
    dbms_output.put_line('业主信息：' || homeUserDto.id);
  end loop;
  close homeUserDtos;
end;

for循环使用游标【可以省略开关游标】

--声明部分一样，不需打开、关闭、提取【因为都是自动的】
declare
  cursor homeUserDtos(v_ownertypeid number) is
    select * from t_pricetable where ownertypeid = v_ownertypeid;
  homeUserDto t_pricetable%rowtype;

begin
  for homeUserDto in homeUserDtos(1)
  loop
    dbms_output.put_line('业主信息：' || homeUserDto.id);
  end loop;
end;

二十三、存储函数

存储函数就是我们自定义函数

基本语法结构

create [or replace] function 方法名 
(参数名1 参数类型,方法名2 参数类型...)
retrun 返回数据类型
is
	方法的声明部分;
begin
	--方法体
	retrun 返回结果变量;
	[exception 异常处理内容]
end

基本案例

--创建存储函数，根据地址I的查询地址名称
create or replace function queryAddNameById(v_id number)
return varchar2
is
   result1 varchar2(30);
begin
  select name into result1 from t_address where id =v_id;
  return result1;
end;

--使用函数
select queryAddNameById(1) from dual;

二十四、存储过程

对一套业务逻辑的封装，可以供程序调用

基本语法

--说明：参数可以传入 in，也可以传出 out，也可以即传入又传出 in out,写在参数列表参数类型前面[不写默认传入]
create [or replace] procedure 存储过程名
(参数名1 in/out 参数类型,方法名2 in/out 参数类型...)
is|As
 变量声明部分;
begin
	--逻辑部分
	[exception 异常处理部分]
end;

--调用存储过程方式1
call 存储过程名(参数...);

--调用存储过程方式2
begin
	存储过程名(参数...);
end;

基本案例

--案例：编写一个存储过程为t_owners插入数据

--创建序列
create sequence seq_owners start with 11;

--创建存储过程
create or replace procedure addOwners
(
       v_name varchar2,
       v_addressid number,
       v_housenumber varchar2,
       v_watermeter varchar2,
       v_ownertypeid number,
       resultCode out number,
       resultMessage out varchar2
)
is
begin
	insert into t_owners 
         values (seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
  select seq_owners.currval into resultCode from dual;
  resultMessage :='添加成功,id='|| resultCode;
  commit;
  exception
     WHEN OTHERS THEN
        -- 异常处理
        resultCode := 0;
        resultMessage := '添加失败: ' || SQLERRM;
        ROLLBACK;
end;

--调用存储过程
declare
   resultCode number;
   resultMessage varchar2(200); 
begin
  addOwners('马大哈',1,'114514','114515',1,resultCode,resultMessage);
  dbms_output.put_line('返回编码：'||resultCode);
  dbms_output.put_line('返回消息：'||resultMessage);
end;

JDBC调用存储过程

二十五、触发器

进行某个操作时，连锁反应。可以理解为Spring的AOP。

公司不让用，不仔细学习了，后面用到再说。

触发器类型
1. 前置触发器：触发器在某个操作之前执行
2. 后置触发器：触发器在某个操作执行后执行

基本语法

create [or replace] trigger 触发器名
	before | after --前置/后置 触发
	[delete] [[or] insert] [[or] update [of 列名]] --哪些操作会触发
	on 表名
	[for each row] --加上这个就是行级触发器
	[when (条件)] --当符合条件时才执行
declare
	声明变量;
begin
	PLSQL代码
end;

基本案例

--同步修改t_account表的本月使用数usernum字段
create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
begin
	--这里用到了伪列 :new表示新的值 :old表示原始值【语句级触发器无法使用伪列】
 :new.usernum:=:new.num1-:new.num0;
end;

‍