Oracle常见语法

一、求交集

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，因此在跨数据库开发时，需要特别注意日期格式的处理。