Oracle常见语法

embedded/2025/2/11 7:54:55/

一、求交集

SELECT column1, column2
FROM table1
INTERSECT
SELECT column1, column2
FROM table2;

INTERSECT 操作符是 Oracle 和一些其他数据库（如 PostgreSQL 和 SQL Server）特有的集合操作符，在 MySQL 中并不直接支持。MYSQL同效果代码：

SELECT t1.column1, t1.column2
FROM table1 t1
INNER JOIN table2 t2ON t1.column1 = t2.column1AND t1.column2 = t2.column2;

二、求差集

        用于返回 第一个查询结果 中存在，但在 第二个查询结果 中不存在的行，即从第一个查询中减去第二个查询中的内容。

SELECT column1, column2
FROM table1
MINUS
SELECT column1, column2
FROM table2;

结果：

MINUS 会返回 table1 中有，但 table2 中没有的行。

特点：

  • 默认去重：返回的结果不会包含重复的行。
  • 结果是基于整个行的比较，而不仅仅是单个列。
  • MINUS 在 Oracle 和一些数据库中是原生支持的，但在 MySQL 中不支持。

MySQL 中的替代方法

SELECT t1.column1, t1.column2
FROM table1 t1
LEFT JOIN table2 t2ON t1.column1 = t2.column1AND t1.column2 = t2.column2
WHERE t2.column1 IS NULL;

三、左外连接

左外连接返回左表（即 LEFT JOIN 左侧的表）中的所有行，即使右表没有匹配的行。如果右表没有匹配的行，结果中该部分的列会显示为 NULL。

第一种语法： 

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

第二种语法：

SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id(+);

        这种写法是 Oracle 特有的表示 外连接 的语法，使用 (+) 来表示右表（或左表）是外部表，确保即使另一张表没有匹配的记录，外部表的记录仍然会显示在结果中 

四、右外连接

        右外连接返回右表（即 RIGHT JOIN 右侧的表）中的所有行，即使左表没有匹配的行。如果左表没有匹配的行，结果中该部分的列会显示为 NULL。

语法一：

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

语法二：

select 列名 from 表名,表名2 where 表1.name(+)=表2.name

五、交叉连接

交叉连接（或笛卡尔积）返回左表和右表的所有行的 组合，即 每一行左表 会与 每一行右表 进行匹配，结果集的行数等于两个表的行数的乘积。

SELECT columns
FROM table1
CROSS JOIN table2;


SELECT s.student_name, c.course_name
FROM students s
CROSS JOIN courses c;

  • 每个学生（students 表）会与每个课程（courses 表）进行组合，返回所有可能的行（笛卡尔积）。因此，结果有 2（学生） * 2（课程） = 4 行。

六、按查询顺序分配行号 ROWNUM

SELECT emp_name, salary, ROWNUM 
FROM employees;

ROWNUM 可以作为筛选条件获取前 N 行数据

获取前两行的数据

SELECT emp_name, salary
FROM employees
WHERE ROWNUM <= 2;

常见问题：无法跳过前几行

假设你想获取 第 2 到第 4 行的数据，可能会写：

SELECT emp_name, salary 
FROM employees 
WHERE ROWNUM > 1 AND ROWNUM <= 4;

但这样是错误的！ ROWNUM 是 逐行分配的，一旦 ROWNUM = 1 这行被过滤掉，后面的行就永远不会得到 ROWNUM = 2，查询会返回 空结果。

正确的做法：使用子查询

SELECT * FROM (SELECT emp_name, salary, ROWNUM AS rn FROM employees
) WHERE rn BETWEEN 2 AND 4;

七、按特定排序分配行号 ROW_NUMBER()

7.1排序分配行号

   ROW_NUMBER() 是 Oracle 11g 及以上 版本引入的 窗口函数（Window Function），用于为查询结果中的每一行分配唯一的行号。

SELECT emp_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

ROW_NUMBER() 是窗口函数，必须与 OVER() 一起使用。每一行的 ROW_NUMBER() 值都是唯一的，即使数据相同。

7.2 先分组再排序编号

如果我想先分组再排序编号，就需要下面这样：

SELECT emp_name, salary, dept_id,ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees;

PARTITION BY dept_id：按照 dept_id（部门）进行分组，每个部门的编号 从 1 开始 重新计算

7.3 分页查询

SELECT * FROM (SELECT emp_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rnFROM employees
) WHERE rn BETWEEN 3 AND 4;

在外层查询 WHERE rn BETWEEN 3 AND 4 只取 第 3 ~ 4 行（相当于 OFFSET 2 LIMIT 2）

八、RANK( )和 DENSE_RANK()

8.1直接排名

SELECT emp_name, salary,RANK() OVER (ORDER BY salary DESC) AS rank_num,DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

8.2 先分组后排名

SELECT emp_name, salary, dept_id,RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_num,DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank_num
FROM employees;

九、企业代码a解析

select '$code' 采购方代码,'$name' 采购方名称,w.goods 产品代码,w.name 产品名称,w.spec 产品规格,h.lotno 批号,w.producer 生产厂家,to_char(l.enddate, 'yyyy/mm/dd') 有效期,'' 进货类型,nvl(w.ratifier, w.register) as 批准文号FROM st_pur h, t_clients c, t_waredict w, scm_lot_list@cmsjs lWHERE h.compid = 1and h.cstid = c.cstidand h.goodid = w.goodidand h.goodid = l.goodidand h.lotno = l.lotnoand w.goodid in (select goodidfrom v_tz_zl_waredict@zdwhere ori_ownerid = '27'and cstname = '厂商名')

9.1字符串常量 

在 SQL 语句中，'$code' 和 '$name' 只是普通的字符串常量，也就是固定的值。 

在 SQL 中，单引号用于表示字符串常量。即使字符串为空，单引号也可以表示一个空字符串（即长度为 0 的字符串）。 

 '' 进货类型,

9.2 日期类型转换格式 

上述代码中  to_char(l.enddate, 'yyyy/mm/dd')  用于将日期类型的数据转换为指定格式的字符串。它通常用于将日期格式化为我们需要的输出样式。

将 l.enddate（可能是一个 DATE 或 TIMESTAMP 类型的字段）格式化为一个字符串，格式为 'yyyy/mm/dd’

9.3 NVL()

NVL() 是 Oracle SQL 中的一个函数，用于处理空值（NULL），如果指定的表达式为 NULL，则返回一个替代值。

NVL(expression1, expression2)

expression1: 需要检查的值。如果这个值为 NULL，则返回 expression2。

NVL(w.ratifier, w.register)

在上述的 SQL 中，NVL(w.ratifier, w.register) 表示：

  • 首先检查 w.ratifier 的值：
    • 如果 w.ratifier 不为 NULL，则返回 w.ratifier 的值。
    • 如果 w.ratifier 为 NULL，则返回 w.register 的值。

也就是说，NVL(w.ratifier, w.register) 用来选择 w.ratifier（批准人）的值，如果它为空，则选择 w.register（注册人）的值。

9.4 数据库连接

代码中where里有这么一个表，它涉及到数据库连接

scm_lot_list@cmsjs l

数据库连接（Database Link）是什么？

想象一下，你有两台电脑：

  • 电脑A（你的本地数据库）
  • 电脑B（远程数据库，存放着你需要的数据）

如果你想要访问 电脑B 上的文件，最简单的方法是什么？
✅ 远程桌面（Remote Desktop）
✅ 网络共享（Network Share）

你可以用 远程桌面 直接操作 电脑B，或者用 网络共享 让 电脑A 直接访问 电脑B 的文件，就像访问本地文件一样。

数据库连接（Database Link） 就像这个 网络共享，它让一个数据库（A）能够访问另一个远程数据库（B）中的数据，就像它是本地数据一样。

SELECT * FROM scm_lot_list@cmsjs;

这个 @cmsjs 就是数据库的“网络共享路径”。它告诉数据库： “我要访问 cmsjs 这个远程数据库中的 scm_lot_list 表。”

如果没有 @cmsjs，那么 scm_lot_list 就是你本地数据库的表；
加上 @cmsjs，表示去 远程数据库 里找 scm_lot_list 这个表。

如何创建数据库连接

CREATE DATABASE LINK 连接名
CONNECT TO 远程用户名 IDENTIFIED BY '远程用户密码'
USING '远程数据库地址';

示例： 假设你本地的数据库需要访问远程数据库 cmsjs，远程数据库的用户是 remote_user，密码是 password，远程数据库的地址是 remote_db，可以这样创建连接：

CREATE DATABASE LINK cmsjs
CONNECT TO remote_user IDENTIFIED BY 'password'
USING 'remote_db';

  • CREATE DATABASE LINK cmsjs

    • 这里的 cmsjs 是数据库连接的名称，你可以随便取，但必须保证唯一。
    • 以后你查询远程数据库的数据时，都会用 @cmsjs。

  • CONNECT TO remote_user IDENTIFIED BY 'password'

    • 这里的 remote_user 是远程数据库的用户名，需要有查询权限。
    • 'password' 是远程数据库用户的密码。

  • USING 'remote_db'

    • 这里的 'remote_db' 代表 远程数据库的连接信息，可以是：
      • TNS（透明网络子系统）别名，如果已经在 tnsnames.ora 里配置了远程数据库。
      • 完整的连接字符串，直接指定远程数据库的主机、端口、SID 或服务名。
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))(CONNECT_DATA=(SID=orcl)))'
  • HOST=192.168.1.100 远程数据库的 IP 地址
  • PORT=1521 远程数据库的端口（Oracle 默认 1521）
  • SID=orcl 远程数据库的 SID

十、企业代码b解析

SELECT trunc(h.makedate) 日期,'$code' 销售方代码,'$name' 销售方名称,w.producer 生产厂家,to_date(substr( h.bthdesc, instr( h.bthdesc, '效期:', 1, 1 ) + 3, 10 ), 'yyyy/mm/dd')  有效期,'' 销售类型,h.addressname as 收货地址,nvl(w.ratifier, w.register) as 批准文号FROM st_flow h, t_waredict w, t_clients cWHERE h.compid = c.compidAND h.goodid = w.goodidAND h.cstid = c.cstidand w.goodid in (select goodidfrom v_tz_zl_waredict@zdwhere ori_ownerid = '27'and cstname = '厂商名字')

10.1 日期截断 trunc（）

TRUNC() 是 Oracle SQL 中的一个日期处理函数，主要用于截断日期，它可以去除日期中的时间部分（小时、分钟、秒等），只保留日期部分（年、月、日）。例如，如果 h.makedate 的值是 2025-02-10 15:30:45，使用 TRUNC(h.makedate) 后会变成 2025-02-10，即时间部分被去掉。

10.2 instr（）

 to_date(substr( h.bthdesc, instr( h.bthdesc, '效期:', 1, 1 ) + 3, 10 ), 'yyyy/mm/dd')  有效期,

  INSTR 是 Oracle SQL 中的一个字符串函数，用于查找一个子字符串在另一个字符串中出现的位置。它返回子字符串在目标字符串中首次出现的位置（索引），如果没有找到子字符串，则返回 0。

INSTR(string, substring [, start_position [, match_occurance [, return_option [, scan_direction [, match_character]]]]])
  • string：目标字符串，也就是我们要搜索的字符串。
  • substring：要查找的子字符串。
  • start_position：可选参数，指定从目标字符串的哪个位置开始查找。默认为 1，表示从第一个字符开始查找。
  • match_occurance：可选参数，指定查找的子字符串出现的第几次。默认为 1，表示查找第一次出现的子字符串。
  • return_option：可选参数，决定返回值的类型：
    • 0 或省略：返回子字符串的起始位置。
    • 1：返回子字符串的结束位置。
  • scan_direction：可选参数，指定查找方向：
    • 1：从左到右（默认）。
    • -1：从右到左（即反向查找）。
  • match_character：可选参数，指定大小写敏感的字符集或字符集标志。

 假设你有以下字符串：'Hello, world!'，你想查找 'world' 在字符串中的位置：

SELECT INSTR('Hello, world!', 'world') AS position FROM dual;

返回结果： 

POSITION
--------8

h.bthdesc 是表 st_flow 中的一个列，可能包含一些描述信息。假设它是一个文本字段，里面存储了类似这样的字符串：

"生产批号: ABC123 效期: 2025/02/10"

instr(h.bthdesc, '效期:', 1, 1)：

  • INSTR 是 Oracle SQL 中的一个字符串函数，用于返回子字符串在目标字符串中的位置。
  • h.bthdesc 是目标字符串，'效期:' 是我们要查找的子字符串，1 表示从目标字符串的第一个字符开始查找，最后一个 1 表示只查找第一个出现的位置。
  • 假设 h.bthdesc 的值是 "生产批号: ABC123 效期: 2025/02/10"，那么 INSTR(h.bthdesc, '效期:', 1, 1) 的结果会是 10，表示 '效期:' 在字符串中的起始位置是第10个字符。

10.3 将字符串转换为日期类型 TO_DATE

TO_DATE(string, format_mask)

将给定的字符串 string 按照指定的 format_mask 格式解析，转换为一个日期类型。这个日期类型是 Oracle 数据库内部用于存储日期的格式，支持进行日期运算和比较。

1. 将字符串 '2025/02/10' 转换为日期类型：

TO_DATE('2025/02/10', 'yyyy/mm/dd')

        这个查询将把 '2025/02/10' 这个字符串按照 年-月-日（yyyy/mm/dd）的格式转换为 Oracle 的日期类型。转换后的结果会是 2025年2月10日，而且可以进行日期运算（例如：加减天数、月数等）。

2. 将字符串 '10-FEB-2025' 转换为日期类型：

TO_DATE('10-FEB-2025', 'dd-mon-yyyy')

        这个查询将把 '10-FEB-2025' 字符串按照 日-月-年（dd-mon-yyyy）的格式转换为日期类型。这个格式中的 mon 是指月份的简写（如 JAN，FEB，MAR，等等）

使用 TO_DATE 时要注意的几点：

  1. 日期格式与实际字符串的格式必须匹配：

    • 格式模板（format_mask）必须与输入的日期字符串格式完全一致。否则，Oracle 会抛出错误。

  2. 默认日期格式：

    • 如果没有显式指定格式，Oracle 会使用默认的日期格式（通常是 DD-MON-YY，即 10-FEB-25）。如果不清楚输入日期的格式，建议始终明确指定格式。

  3. 日期类型和字符串类型的区别：

    • TO_DATE 函数将返回一个 日期类型，而不是字符串。Oracle 会按照其内部的日期格式存储和处理这些数据。

  4. 时间部分的处理：

    • 如果 string 中包含时间信息，TO_DATE 会将时间部分一起解析。如果没有时间信息，时间部分默认为 00:00:00（即午夜）。

  5. 不同的数据库可能有不同的默认日期格式：

    • 不同数据库管理系统（DBMS）可能有不同的默认日期格式。在 Oracle 中，通常是 DD-MON-YY，而在其他 DBMS 中可能是 YYYY-MM-DD，因此在跨数据库开发时，需要特别注意日期格式的处理。


http://www.ppmy.cn/embedded/161273.html

相关文章

DeepSeek + IDEA 辅助编程王炸组合

DeepSeek + IDEA 辅助编程王炸组合

DeepSeek + IDEA 辅助编程王炸组合 2025年的春节可以说是人工智能在中国史上飘红的一段历史时刻，年后上班的第一天，便马不停蹄的尝试新技能。今天的科技在飞速发展，编程领域的人工智能工具犹如雨后春笋般涌现。 其中，DeepSeek 则以其卓越的性能和智能化的功能，迅速在众多…
阅读更多...
【PG】DROP TABLE ... CASCADE

【PG】DROP TABLE ... CASCADE

问题 ERROR: cannot drop table wx_user_tag because other objects depend on it DETAIL: default value for column id of table wx_user_tag depends on sequence wx_user_tag_id_seq HINT: Use DROP … CASCADE to drop the dependent objects too. 解决 这个错误消息表…
阅读更多...
Ollama+Chatbox本地部署运行deepseek

Ollama+Chatbox本地部署运行deepseek

文章目录 文件准备安装OllamaSetup加载deepseek模型运行模型安装chatbox运行对比 文件准备 OllamaSetup.exe&#xff1a;链接&#xff1a;https://pan.baidu.com/s/1eXtbNBsxSCr_6_RxinUB_A 提取码&#xff1a;j1lw Chatbox-1.9.1-Setup.exe&#xff1a;链接&#xff1a;https…
阅读更多...
全面支持DeepSeek接入，聚铭网络以「AI+安全」重新定义网络防御新范式

全面支持DeepSeek接入，聚铭网络以「AI+安全」重新定义网络防御新范式

当DeepSeek掀起AI浪潮&#xff0c;网络安全如何乘势进化&#xff1f; 春节假期刚刚结束&#xff0c;除了广受好评的电影《哪吒》外&#xff0c;AI领域也迎来了一颗新星——DeepSeek。这款产品在国外被誉为“神秘东方力量”。然而&#xff0c;就在DeepSeek成为全球焦点之际&…
阅读更多...
RocketMQ的缺点是什么？Kafka的缺点是什么？使用场景有什么区别？

RocketMQ的缺点是什么？Kafka的缺点是什么？使用场景有什么区别？

目录 RocketMQ 的缺点 Kafka 的缺点 RocketMQ 和 Kafka 的使用场景区别 RocketMQ 的使用场景 Kafka 的使用场景 总结 RocketMQ 的缺点 Kafka 的缺点 1. 消息顺序保证能力有限 2. 消息事务功能相对较弱 3. 消息回溯能力有限 使用场景区别 1. RocketMQ 的适用场景 …
阅读更多...
博客项目-day02（登录功能）

博客项目-day02（登录功能）

登录功能 这里使用JWT令牌技术进行登录功能的实现 JWT介绍 需要导入jjwt的依赖 先看接口 传入账号密码返回token 先导入一个JWT工具类 public class JWTUtils {private static final String jwtToken "123456Mszlu!###$$";public static String createToken(Long…
阅读更多...
19.1.1 DDL

19.1.1 DDL

版权声明&#xff1a;本文为博主原创文章&#xff0c;转载请在显著位置标明本文出处以及作者网名&#xff0c;未经作者允许不得用于商业目的。 DDL主要用于数据库、表、存储等对象的创建修改和删除。 使用DDL语句创建一个数据表&#xff0c;常用的语法是&#xff1a; CREATE…
阅读更多...
Linux（CentOS）安装 Nginx

Linux（CentOS）安装 Nginx

CentOS版本&#xff1a;CentOS 7 Nginx版本&#xff1a;1.24.0 两种安装方式&#xff1a; 一、通过 yum 安装&#xff0c;最简单&#xff0c;一键安装&#xff0c;全程无忧。 二、通过编译源码包安装&#xff0c;需具备配置相关操作。 最后附&#xff1a;设置 Nginx 服务开…
阅读更多...
最新文章

Copyright @ 2022~2023