Oracle思维导图
U7101343043
2025-06-14
数据
图表
窗口函数
多表查询，单表查询相关内容讲解
树图思维导图提供《Oracle思维导图》在线思维导图免费制作，点击“编辑”按钮，可对《Oracle思维导图》进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：34ef0786f6bf08a5235403955f7cc3fd
思维导图大纲
相关思维导图模版
Oracle思维导图思维导图模板大纲
用户
创建用户
create user test identified by 123456;
用户赋权
grant dba to test;
删除用户
DROP USER test CASCADE;
表
创建表的语法
CREATE TABLE 表名称
表结构的数据类型
1. 字符型
（1）CHAR : 固定长度的字符类型，最多存储 2000 个字节
（2）VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节
（3）LONG : 大文本类型。 最大可以存储 2 个 G
2.数值型
NUMBER : 数值类型
例如：NUMBER(5) 最大可以存的数为 99999
NUMBER(5,2) 最大可以存的数为 999.99
3.日期型
（1）DATE：日期时间型，精确到秒
（2）TIMESTAMP：精确到秒的小数点后 9 位 # 2024-09-15 12:34:56.123456789
金融交易时间戳，需要精确时间顺序的并发操作
4.二进制型（大数据类型）
（1）CLOB : 存储字符, 最大可以存 4 个 G
（2）BLOB：存储图像、声音、 视频等二进制数据,最多可以存 4 个 G
给表添加注释的语法
comment on table 表名 is '表注释';
comment on table 表名.列名 is '列注释';
修改表结构
增加字段
ALTER TABLE 表名称 ADD( 列1 类型 [DEFAULT 默认值]...)
修改字段名
alter table 表名称 rename column 原列名 to 新列名
删除字段和修改列类型
修改列类型：alter table 表名称 MODIFY 列名 新类型
alter table t_user3 modify sex number;
删除一个字段：alter table 表名称 drop column 列名
删除多个字段：alter table 表名称 drop (列名1, 列名 2...)
约束
主键约束
CREATE TABLE t_user2 ( id NUMBER PRIMARY KEY,
非空约束
sex VARCHAR2(10) NOT NULL,
唯一约束
name VARCHAR2(50) UNIQUE,
默认值约束
sex CHAR (2) DEFAULT '女’，
联合主键
create table 表名( 字段1 类型 ， 字段2 类型， ... constraint 约束名 primary key (字段1，字段2));
alter table 表名 add constraint 约束名 primary key (字段1，字段2);
alter table emp add constraint pk_id_phone primary key (name,phone);
删除联合主键约束：alter table 表名 drop constraint 约束名;
alter table emp drop constraint pk_id_phone;
检查约束
create table 表名( 字段名1 类型 约束 字段名2 类型 check(约束) constraint 约束名 check(sex='m' or sex='f'))
alter table 表名 add constraint 约束名 check(sex='m' or sex='f');
给性别添加检查约束 只允许传入男和女：alter table employee add constraint ck_age check (sex = '男' or sex ='女')
外键约束
alter table 子表的表名 add constraint 外键的名字 foreign key（子表的列名）references 父表的表名（父表主键的列名）;
删除外键：alter table emp drop constraint 外键名;
级联删除
数据
增
单条
insert into 表名[(列名1, 列名2...)] values(值1，值2，...);
多条
inser all into 表名 values(值1，值2，...)
insert all into 表名 values(值1，值2...) into 表名 values(值1，值2...) into 表名 values(值1，值2...) select 1 from dual;
时区问题
current_date 获取当前时区的时间 默认时间是格林尼治标准时间（GMT）
Systimestamp 获取当前时间 这个是获取当前系统电脑的时间
会话级别修改时区
ALTER SESSION SET TIME_ZONE = 'Asia/Shanghai'
永久配置中国时区
改
update 表名 set 列1=值1, 列2=值2, ....where 修改条件
删
delete from 表名 where 删除条件
delete 删除的数据可以 rollback
delete 删除可能产生碎片，并且不释放空间
truncate table 表名称
truncate 是先摧毁表结构，再重构表结构
单表查询
简单查询
select [distinct] 字段名1,字段名2,... from 表名;
比较查询
select * from 表名 where age <40;
范围查询
select * from 表名 where 字段名 in (范围值1,范围值2,...);
select * from 表名 where 字段名 between 范围值1 and 范围值2;
逻辑查询
select [*|字段名1, 字段名2, ...] from 表名 where a and b;
select [*|字段名1, 字段名2, ...] from 表名 where a or b;
not
模糊查询
select * from 表名 where 字段名 like '%某个字%'; 或select * from 表名 where 字段名 like '某个字_';
非空查询
select * from 表名 where 字段名 is [not] null;
排序查询
select * from 表名 where 条件 order by 字段名 [asc|desc];
聚合查询
查询所有武将的平均武力值 保留2位的精度select round(avg(force_value),2) from three_kingdoms_generals ;
去重查询
select distinct 列名1,列名2 from 表名
子查询
查询武力值高于本国最低武力值20%的武将信息SELECT t.*FROM three_kingdoms_generals tWHERE t.force_value > (SELECT MIN(force_value) * 1.2 FROM three_kingdoms_generals WHERE country = t.country);
集合查询
集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。
UNION ALL(并集) ，返回各个查询的所有记录 ，包括重复记录。
询所有武将的并集（包含重复记录）select * from three_kingdoms_generals union all select * from three_kingdoms_generals;
UNION(并集) ，返回各个查询的所有记录 ，不包括重复记录。
查询所有武将的并集（去除重复记录）select * from three_kingdoms_generals union select * from three_kingdoms_generals
INTERSECT(交集) ，返回两个查询共有的记录。
查询同时属于魏国且武力值大于90的武将信息。 交集 满足这两个条件共同的部分
select * from three_kingdoms_generals where country = '魏' and force_value>90;
select * from three_kingdoms_generals where country = '魏'intersect select * from three_kingdoms_generals where force_value>90;
MINUS(差集) ，返回第一个查询检索出的记录减去第二个查询检索出的记录之 后剩余的记录。
查询既不属于蜀国也不属于魏国的武将信息。select * from three_kingdoms_generals minus select * from three_kingdoms_generals where country in ('蜀','魏')
查询武力值大于90或者年龄小于30的武将信息select * from three_kingdoms_generals where force_value >90 minus select * from three_kingdoms_generals where age <30;
人造列，常数列
添加一个常数列"三国时期"作为时代标识：select '三国时期' as 时代标识
添加一个人造列表示武力等级：select case when force_value>=90 then 'S级' when force_value>=80 and force_value<=89 then 'A级' else 'B级' end as 武力等级 from
多表查询
伪列查询
ROWID
物理地址：标识表中唯一行的十六进制字符串。格式：OOOOFFBBBBBBRRRR（对象号 + 文件号 + 块号 + 行号）
select rowid,表名.* from 表名;
ROWNUM
获取指定结果集的前N条数据
select * from 表名 where ROWNUM <5;
注意：在 Oracle 数据库里，ROWNUM 是一个伪列，它会为查询结果集中的每一行分配一个唯一的行号，从 1 开始。要理解为何不能直接在 WHERE 子句里使用 ROWNUM > 5，就需要了解 ROWNUM 的工作原理。 ROWNUM 是在查询结果生成时按顺序分配的，从 1 开始。当执行上述查询时，数据库会逐行检查 WHERE 子句条件。由于 ROWNUM 从 1 开始，第一行的 ROWNUM 为 1，不满足 ROWNUM > 5 的条件，就会被过滤掉。接下来的行，其 ROWNUM 依然会从 1 开始重新分配，因为前面的行未满足条件，所以始终不会有行满足 ROWNUM > 5，最终查询结果为空。
总结：ROWNUM 是在查询结果生成时动态分配的，直接在 WHERE 子句里使用 ROWNUM > n（n 大于 1）会导致没有行满足条件，查询结果为空。而使用子查询，先把 ROWNUM 存储为一个列，再在外部查询里进行筛选，就能避免这个问题。
交叉连接；笛卡尔积
（1）使用语法1查询类别表和商品表中的所有商品信息；92sql标准写法：select c.*, p.*from category c, product p;
（2）使用语法2查询类别表和商品表中的所有商品信息；99sql标准写法：select c.*, p.*from category c cross join product p;
内连接
select 字段名 from A表名, B表名 where 条件;
select 字段名 from A表名 别名 inner join B表名 别名 on 条件 [where 查询条件];
select 字段名 from A表名 别名 join B表名 别名 on 条件 [where 查询条件];
左外连接
select 字段名 from A表名 别名 left [outer] join B表名 别名 on 条件;
右外连接
select 字段名 from A表名 别名 right [outer] join B表名 别名 on 条件;
全连接
案例1：基本全连接：select c.*, p.*from category c full join product p on c.id=p.category_id;
案例2：使用union 实现全连接：select c.*, p.* from category c left join product p on c.id=p.category_idunionselect c.*, p.* from category c right join product p on c.id=p.category_id;
自连接
子查询和join的区别
执行顺序
子查询：通常先执行内层查询，然后将结果作为外层查询的条件。这意味着子查询可能会被多次执行，尤其是在相关子查询（子查询依赖于外层查询的结果）中。
JOIN：数据库会同时考虑多个表的连接条件，一次性处理多个表的数据，通常会使用更高效的算法来执行连接操作。
性能表现
子查询：如果子查询的结果集很大，或者子查询被多次执行，可能会导致性能下降。特别是在处理大数据集时，子查询的效率可能不如JOIN。
JOIN：在处理多个表之间的关联时，JOIN通常比子查询更高效。数据库可以使用索引和优化器来优化连接操作，减少磁盘 I/O 和内存使用。
可读性和可维护性
子查询：当子查询嵌套层数较多时，代码的可读性会变差，理解和维护起来也比较困难。
JOIN：JOIN的语法结构相对清晰，能够直观地展示表之间的关联关系，代码的可读性和可维护性通常较好。
使用场景
子查询：适用于需要先计算一个中间结果，然后将其作为条件用于另一个查询的场景。例如，查找大于平均值得数据。
JOIN：适用于需要同时从多个表中获取相关数据的场景。例如，在一个订单系统中，需要同时获取客户信息和订单信息。
函数
字符函数
ASCII 返回对应字符的十进制值
CHR 给出十进制返回字符
CONCAT 拼接两个字符串 ，与 || 相同
INITCAP 将字符串的第一个字母变为大写
INSTR 找出某个字符串的位置
INSTRB 找出某个字符串的位置和字节数
LENGTH 以字符给出字符串的长度
LENGTHB 以字节给出字符串的长度
LOWER 将字符串转换成小写
LPAD 使用指定的字符在字符的左边填充
LTRIM 在左边裁剪掉指定的字符
RPAD 使用指定的字符在字符的右边填充
RTRIM 在右边裁剪掉指定的字符
REPLACE 执行字符串搜索和替换
SUBSTR 取字符串的子串
SUBSTRB 取字符串的子串 (以字节)
SOUNDEX 返回一个同音字符串
TRANSLATE 执行字符串搜索和替换
TRIM 裁剪掉前面或后面的字符串
UPPER 将字符串变为大写
使用CONCAT函数拼接两个字符串 使用||运算符拼接多个字符串 CONCAT函数只能拼接两个参数，需要嵌套使用
select concat('1312','-213123') from dual;
select concat(name,addr) from EMPLOYEE;
select concat(concat(name,'-'),addr) from EMPLOYEE;
select id || '-' || NAME from CATEGORY;
数值函数
ABS(value) 绝对值
CEIL(value) 大于或等于 value 的最小整数
COS(value) 余弦
COSH(value) 反余弦
EXP(value) e 的 value 次幂
FLOOR(value) 小于或等于 value 的最大整数
LN(value) value 的自然对数
LOG(value) value 的以 10 为底的对数
MOD(value,divisor) 求模
POWER(value,exponent) value 的 exponent 次幂
ROUND(value,precision) 按 precision 精度 4 舍 5 入
SIGN(value) value 为正返回 1;为负返回-1;为 0 返回 0.
SIN(value) 余弦
SINH(value) 反余弦
SQRT(value) value 的平方根
TAN(value) 正切
TANH(value) 反正切
TRUNC(value,按 precision) 按照 precision 截取 value
VSIZE(value) 返回 value 在 ORACLE 的存储空间大小
中位数
MEDIAN
select median(num), median(num1) from ccc;
日期函数
select sysdate from DUAL; -- 获取系统时间 精确到秒select systimestamp from DUAL; -- 获取系统时间 精确到毫秒select current_date from DUAL; -- 获取当前时区的年月日时分秒
ADD_MONTHS 在日期 date 上增加 count 个月
GREATEST(date1,date2,. . .) 从日期列表中选出最晚的日期
LAST_DAY( date ) 返回日期 date 所在月的最后一天
LEAST( date1, date2, . . .) 从日期列表中选出最早的日期
MONTHS_BETWEEN(date2, date1) 给出 Date2 - date1 的月数(可以是小数)
NEXT_DAY( date,’day’) 给出日期 date 之后下一天的日期 ，这里的 day 为星期 ， 如 ：MONDAY,Tuesday 等。
NEW_TIME(date, ’this’,’other’) 给出在 this 时区=Other 时区的日期和时间
ROUND(date,’format’) 未指定 format 时 ，如果日期中的时间在中午之前 ，则 将日期中的时间截断为 12 A.M.(午夜 ，一天的开始),否 则进到第二天。
TRUNC(date,’format’) 未指定 format 时 ，将日期截为 12 A.M.( 午夜 ，一天的开始).
类型转换函数
CHARTOROWID 将 字符转换到 rowid 类型
CONVERT 转换一个字符节到另外一个字符节
HEXTORAW 转换十六进制到 raw 类型
RAWTOHEX 转换 raw 到十六进制
ROWIDTOCHAR 转换 ROWID 到字符
TO_CHAR 转换日期格式到字符串
使用格式模式：SELECT TO_CHAR(1234, '9999') FROM DUAL;
SELECT TO_CHAR(1234, '09999') FROM DUAL; -- 前导零
SELECT TO_CHAR(1234.56, '9999.99') FROM DUAL; -- 小数点
SELECT TO_CHAR(1234.56, '9,999.99') FROM DUAL; -- 千位分隔符
SELECT TO_CHAR(1234, '$9999') FROM DUAL; -- 货币符号
使用员工表示例：SELECT EMP_NAME, TO_CHAR(SALARY, 'L99,999.00') AS 格式化薪资 FROM EMPLOYEE;
TO_DATE 按照指定的格式将字符串转换到日期型
TO_MULTIBYTE 把单字节字符转换到多字节
TO_NUMBER 将数字字串转换到数字
TO_SINGLE_BYTE 转换多字节到单字节
其它函数
空值处理函数 NVL
nvl(列名，如果为空给的值)
如果奖金为NULL，则按照0计算：select BONUS,nvl(BONUS,0) from EMPLOYEE;
空值处理函数 NVL2
nvl2(字段名，如果不为空给的值,如果为空给的值)
如果满足给对应值 如果不满给对应值：select t.*,nvl2(BONUS, 111, 222) from EMPLOYEE t; select t.*,nvl2(BONUS,'不为null的值', '为null的值') from EMPLOYEE t;
等同于：select BONUS, case when BONUS is null then '为空的自定义值或者字段名' else '不为空值或者字段名' end as 判断 from EMPLOYEE;
条件取值 decode
对部门编号进行转换 方式一 case when的另外一种用法select department_id, case department_id when 10 then '财务部' when 20 then '人力资源部' when 30 then '经理办公室' when 40 then '研发部' when 50 then '市场部' when 60 then '神秘部门' else '未知部门' end as 部门名称 from EMPLOYEE;
对部门编号进行转换 方式二 decode 条件取值select decode(department_id, 10,'财务部', 20,'人力资源部', 30,'经理办公室', '未知部门') as 部门名称 from EMPLOYEE;
窗口函数
窗口范围
PARTITION BY 列名, ... 的作用是按照指定列的值对整张表的数据进行分区，OVER()中没有PARTITION BY时，整张表就是一个分区
不添加partition by 分区 是以当前全部数据为一个分区
分组后 窗口范围默认 是最开始到最最后
select dname, ename, salary, hiredate, sum(salary) over (partition by dname)from tb_emp;
分组+排序之后窗口范围（即加上order by之后） 默认 第一行到当前行
select dname, ename, salary, hiredate, sum(salary) over (partition by dname order by salary ) ss from tb_emp;
手动指定 从最开始到最后：rows between unbounded preceding and undounded folllowing 往上无止尽到往下无止境
select dname,ename,salary,hiredate, sum(salary) over( partition by dname order by salary rows between unbounded preceding and unbounded following ) sum_salary from tb_emp;
从最开始到当前行
sum(salary) over( partition by dname order by salary rows between unbounded preceding and current row)
自定义窗口
ROWS 和 RANGE 关键字，都可以用来自定义 window frame 范围：
rows
ROWS是根据分区数据排序之后，每一行的 row_number 确定每行关联的 window frame 范围
CURRENT ROW：仅代表当前行
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
2 PRECEDING 指 行号 - 2
rows between 0 preceding -- 从当前行开始 and 2 following -- 向下多少行
rows between 1 preceding and 2 folllowing从上一行开始到下2行
range
RANGE是根据分区数据排序之后，每一行的排序列的值确实每行关联的 window frame 范围
CURRENT ROW：代表和当前行排序列的值相同的所有行
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
2 PRECEDING 指 列值 - 2
聚合开窗
sum(字段名) over(partition by 字段名 order by 字段名)
max(字段) over(partition by 字段名 order by 字段名)
min(字段) over(partition by 字段名 order by 字段名)
count(字段) over(partition by 字段名 order by 字段名)
avg(字段名) over(partition by 字段名 order by 字段名)
排名开窗
考虑并列 但是排名不连续
rank()ovet()
select name, course, score, rank() over (order by score) from tb_stu ;
考虑并列 排名连续
dense_rank()over()
dense_rank() over (order by score desc)
不考虑并列 排名连续
row_number()over()
row_number() over (order by score desc)
抽样开窗
分桶发扑克牌
ntile（桶数）over（order by 字段 ）
NTILE(num_buckets) OVER (PARTITION BY column ORDER BY column)
num_buckets：要将数据划分的组数（桶数）。
PARTITION BY column：可选，用于按指定列对数据进行分区。
ORDER BY column：必需，用于指定每个分区内的排序。
NTILE(3) OVER (ORDER BY score DESC)
NTILE(3) 将数据划分为 3 组，并根据分数的降序排序为每个学生分配一个组号
首位开窗
获取分区内第一行或者最后一行数据
first_value()over()
该函数会在指定的窗口范围内，从你所提供的字段名对应的列中选取第一个出现的值
last_value()over()
在指定的窗口范围内，从你所提供的字段名对应的列中选取最后一个出现的值
两个函数必须结合 OVER 子句使用，OVER 子句定义了窗口的范围和排序规则：over(partition by 分区字段 order by 排序字段)
分布开窗
用于计算某行的百分比排名。这个函数在数据分析中常用于了解某一数据点在整体数据中的相对位置。百分比排名的取值范围是从 0 到 1，表示当前行在分区内的排名相对于分区内其他行的百分比位置。
PERCENT_RANK = (RANK - 1) / (总行数 - 1)
排序顺序：PERCENT_RANK() 的计算依赖于 ORDER BY 子句指定的排序顺序。在上面的例子中，我们按照 score 降序排列，最高分的学生的 percent_rank 是 0。
分区：如果使用了 PARTITION BY 子句，则 PERCENT_RANK() 会在每个分区内计算百分比排名，而不是在整个结果集上。
相同值：在处理相同值时，PERCENT_RANK() 会为相同值分配相同的排名百分比。
第一行和最后一行：第一行的 percent_rank 总是 0，而最后一行的 percent_rank 总是 1。
PERCENT_RANK() 常用于了解数据在分布中的相对位置，对于生成百分比排名或分位数分析非常有用。
percent_rank()over(PARTITION BY 字段 order by 字段)
PARTITION BY: （可选）用于将结果集划分为不同的分区，函数会在每个分区内计算排名。
ORDER BY: 用于指定排名依据的列，PERCENT_RANK() 函数根据该列的排序顺序计算排名。
偏移开窗
当前行的上一方或者下一行
lag(字段,偏移量，默认值) over()
LAG(column, offset, default) OVER (PARTITION BY column ORDER BY column)
lead(字段,偏移量，默认值) over()
lead(hiredate,1,'我前面1行没有值') over (partition by dname order by salary)
窗口函数应用专题训练
累计问题
ROWS 和 RANGE 关键字，都可以用来自定义 window frame 范围：
rows
ROWS是根据分区数据排序之后，每一行的 row_number 确定每行关联的 window frame 范围
CURRENT ROW：仅代表当前行
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
2 PRECEDING 指 行号 - 2
rows between 0 preceding -- 从当前行开始 and 2 following -- 向下多少行
rows between 1 preceding and 2 folllowing从上一行开始到下2行
range
RANGE是根据分区数据排序之后，每一行的排序列的值确实每行关联的 window frame 范围
CURRENT ROW：代表和当前行排序列的值相同的所有行
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
2 PRECEDING 指 列值 - 2
取值窗口函数
● LAG 函数可以返回窗口内当前行之前的第N行数据。
● LEAD 函数可以返回窗口内当前行之后的第N行数据。
● FIRST_VALUE 函数可以返回窗口内第一行数据。
● LAST_VALUE 函数可以返回窗口内最后一行数据。
● NTH_VALUE 函数可以返回窗口内第N行数据。
其中，LAG函数和LEAD函数不支持动态的窗口大小，它们以整个分区作为分析的窗口。
连续问题
正则表达式
基础语法
字符类
[ ]：匹配方括号内的任意一个字符。例如，[abc] 可以匹配字符 a、b 或 c。
[^ ]：匹配不在方括号内的任意一个字符。例如，[^abc] 可以匹配除 a、b、c 之外的任意字符。
[a - z]：匹配指定范围内的任意一个字符。例如，[a - z] 可以匹配任意小写字母。
元字符
.：匹配除换行符以外的任意单个字符。 _
*：匹配前面的字符或子表达式零次或多次。 0 到多次
+：匹配前面的字符或子表达式一次或多次。 1 到多次
?：匹配前面的字符或子表达式零次或一次。 ？ 0或者1
^：匹配字符串的开始位置。
$：匹配字符串的结束位置。
\w 代表匹配任意的单词字符，这包含字母、数字以及下划线。它等同于字符类 [a-zA-Z0-9_]。
\W 则匹配任意非单词字符，也就是不在 [a-zA-Z0-9_] 这个范围内的字符。
\s 用于匹配任意的空白字符，其中涵盖空格、制表符、换页符、换行符等。它等同于字符类 [\t\n\r\f\v]
\d 表示匹配任意的数字字符，也就是从 0 到 9 的数字。它等同于字符类 [0-9]
量词
{n}：匹配前面的字符或子表达式恰好 n 次。 {2}
{n,}：匹配前面的字符或子表达式至少 n 次。 {2，}
{n,m}：匹配前面的字符或子表达式至少 n 次，最多 m 次。 {2，5}
查看更多
数据库编程day03(oracle mysql sql)思维导图
U443303869树图思维导图提供《数据库编程day03(oracle mysql sql)》在线思维导图免费制作，点击“编辑”按钮，可对《数据库编程day03(oracle mysql sql)》进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：6a8e4c15b432901365f4b3765566e331
Oracle“入冬” 520万ERP项目失败绝非偶然 思维导图
定格苍凉树图思维导图提供《Oracle“入冬” 520万ERP项目失败绝非偶然 》在线思维导图免费制作，点击“编辑”按钮，可对《Oracle“入冬” 520万ERP项目失败绝非偶然 》进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：595ec87eb572a7f5995095f392c04463
相似思维导图模版
首页
我的文件
我的团队
个人中心