柚子快報激活碼778899分享：Oracle常見語法

http://yzkb.51969.com/

一、求交集

SELECT column1, column2

FROM table1

INTERSECT

SELECT column1, column2

FROM table2;

INTERSECT 操作符是 Oracle 和一些其他數(shù)據(jù)庫（如 PostgreSQL 和 SQL Server）特有的集合操作符，在 MySQL 中并不直接支持。MYSQL同效果代碼：

SELECT t1.column1, t1.column2

FROM table1 t1

INNER JOIN table2 t2

ON t1.column1 = t2.column1

AND t1.column2 = t2.column2;

二、求差集

????????用于返回 第一個查詢結(jié)果 中存在，但在 第二個查詢結(jié)果 中不存在的行，即從第一個查詢中減去第二個查詢中的內(nèi)容。

SELECT column1, column2

FROM table1

MINUS

SELECT column1, column2

FROM table2;

結(jié)果：

MINUS 會返回 table1 中有，但 table2 中沒有的行。

特點：

默認去重：返回的結(jié)果不會包含重復(fù)的行。結(jié)果是基于整個行的比較，而不僅僅是單個列。MINUS 在 Oracle 和一些數(shù)據(jù)庫中是原生支持的，但在 MySQL 中不支持。

MySQL 中的替代方法

SELECT t1.column1, t1.column2

FROM table1 t1

LEFT JOIN table2 t2

ON t1.column1 = t2.column1

AND t1.column2 = t2.column2

WHERE t2.column1 IS NULL;

三、左外連接

左外連接返回左表（即 LEFT JOIN 左側(cè)的表）中的所有行，即使右表沒有匹配的行。如果右表沒有匹配的行，結(jié)果中該部分的列會顯示為 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 特有的表示 外連接 的語法，使用 (+) 來表示右表（或左表）是外部表，確保即使另一張表沒有匹配的記錄，外部表的記錄仍然會顯示在結(jié)果中?

四、右外連接

????????右外連接返回右表（即 RIGHT JOIN 右側(cè)的表）中的所有行，即使左表沒有匹配的行。如果左表沒有匹配的行，結(jié)果中該部分的列會顯示為 NULL。

語法一：

SELECT columns

FROM table1

RIGHT JOIN table2 ON table1.column = table2.column;

語法二：

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

五、交叉連接

交叉連接（或笛卡爾積）返回左表和右表的所有行的 組合，即 每一行左表 會與 每一行右表 進行匹配，結(jié)果集的行數(shù)等于兩個表的行數(shù)的乘積。

SELECT columns

FROM table1

CROSS JOIN table2;

SELECT s.student_name, c.course_name

FROM students s

CROSS JOIN courses c;

每個學(xué)生（students 表）會與每個課程（courses 表）進行組合，返回所有可能的行（笛卡爾積）。因此，結(jié)果有 2（學(xué)生） * 2（課程） = 4 行。

六、按查詢順序分配行號 ROWNUM

SELECT emp_name, salary, ROWNUM

FROM employees;

ROWNUM?可以作為篩選條件獲取前 N 行數(shù)據(jù)

獲取前兩行的數(shù)據(jù)

SELECT emp_name, salary

FROM employees

WHERE ROWNUM <= 2;

常見問題：無法跳過前幾行

假設(shè)你想獲取 第 2 到第 4 行的數(shù)據(jù)，可能會寫：

SELECT emp_name, salary

FROM employees

WHERE ROWNUM > 1 AND ROWNUM <= 4;

但這樣是錯誤的！ ROWNUM 是 逐行分配的，一旦 ROWNUM = 1 這行被過濾掉，后面的行就永遠不會得到 ROWNUM = 2，查詢會返回 空結(jié)果。

正確的做法：使用子查詢

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 及以上 版本引入的 窗口函數(shù)（Window Function），用于為查詢結(jié)果中的每一行分配唯一的行號。

SELECT emp_name, salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn

FROM employees;

ROW_NUMBER() 是窗口函數(shù)，必須與 OVER() 一起使用。每一行的 ROW_NUMBER() 值都是唯一的，即使數(shù)據(jù)相同。

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 rn

FROM 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;

九、企業(yè)代碼a解析

select

'$code' 采購方代碼,

'$name' 采購方名稱,

w.goods 產(chǎn)品代碼,

w.name 產(chǎn)品名稱,

w.spec 產(chǎn)品規(guī)格,

h.lotno 批號,

w.producer 生產(chǎn)廠家,

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 l

WHERE h.compid = 1

and h.cstid = c.cstid

and h.goodid = w.goodid

and h.goodid = l.goodid

and h.lotno = l.lotno

and w.goodid in (select goodid

from v_tz_zl_waredict@zd

where ori_ownerid = '27'

and cstname = '廠商名')

9.1字符串常量?

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

在 SQL 中，單引號用于表示字符串常量。即使字符串為空，單引號也可以表示一個空字符串（即長度為 0 的字符串）。?

'' 進貨類型,

9.2 日期類型轉(zhuǎn)換格式?

上述代碼中? to_char(l.enddate, 'yyyy/mm/dd')??用于將日期類型的數(shù)據(jù)轉(zhuǎn)換為指定格式的字符串。它通常用于將日期格式化為我們需要的輸出樣式。

將 l.enddate（可能是一個 DATE 或 TIMESTAMP 類型的字段）格式化為一個字符串，格式為 'yyyy/mm/dd’

9.3?NVL()

NVL() 是 Oracle SQL 中的一個函數(shù)，用于處理空值（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 數(shù)據(jù)庫連接

代碼中where里有這么一個表，它涉及到數(shù)據(jù)庫連接

scm_lot_list@cmsjs l

數(shù)據(jù)庫連接（Database Link）是什么？

想象一下，你有兩臺電腦：

電腦A（你的本地數(shù)據(jù)庫）電腦B（遠程數(shù)據(jù)庫，存放著你需要的數(shù)據(jù)）

如果你想要訪問 電腦B 上的文件，最簡單的方法是什么？ ? 遠程桌面（Remote Desktop） ? 網(wǎng)絡(luò)共享（Network Share）

你可以用 遠程桌面 直接操作 電腦B，或者用 網(wǎng)絡(luò)共享 讓 電腦A 直接訪問 電腦B 的文件，就像訪問本地文件一樣。

數(shù)據(jù)庫連接（Database Link） 就像這個 網(wǎng)絡(luò)共享，它讓一個數(shù)據(jù)庫（A）能夠訪問另一個遠程數(shù)據(jù)庫（B）中的數(shù)據(jù)，就像它是本地數(shù)據(jù)一樣。

SELECT * FROM scm_lot_list@cmsjs;

這個 @cmsjs 就是數(shù)據(jù)庫的“網(wǎng)絡(luò)共享路徑”。它告訴數(shù)據(jù)庫： “我要訪問 cmsjs 這個遠程數(shù)據(jù)庫中的 scm_lot_list 表。”

如果沒有 @cmsjs，那么 scm_lot_list 就是你本地數(shù)據(jù)庫的表； 加上 @cmsjs，表示去 遠程數(shù)據(jù)庫 里找 scm_lot_list 這個表。

如何創(chuàng)建數(shù)據(jù)庫連接

CREATE DATABASE LINK 連接名

CONNECT TO 遠程用戶名 IDENTIFIED BY '遠程用戶密碼'

USING '遠程數(shù)據(jù)庫地址';

示例： 假設(shè)你本地的數(shù)據(jù)庫需要訪問遠程數(shù)據(jù)庫 cmsjs，遠程數(shù)據(jù)庫的用戶是 remote_user，密碼是 password，遠程數(shù)據(jù)庫的地址是 remote_db，可以這樣創(chuàng)建連接：

CREATE DATABASE LINK cmsjs

CONNECT TO remote_user IDENTIFIED BY 'password'

USING 'remote_db';

CREATE DATABASE LINK cmsjs

這里的 cmsjs 是數(shù)據(jù)庫連接的名稱，你可以隨便取，但必須保證唯一。以后你查詢遠程數(shù)據(jù)庫的數(shù)據(jù)時，都會用 @cmsjs。 CONNECT TO remote_user IDENTIFIED BY 'password'

這里的 remote_user 是遠程數(shù)據(jù)庫的用戶名，需要有查詢權(quán)限。'password' 是遠程數(shù)據(jù)庫用戶的密碼。 USING 'remote_db'

這里的 'remote_db' 代表 遠程數(shù)據(jù)庫的連接信息，可以是：

TNS（透明網(wǎng)絡(luò)子系統(tǒng)）別名，如果已經(jīng)在 tnsnames.ora 里配置了遠程數(shù)據(jù)庫。完整的連接字符串，直接指定遠程數(shù)據(jù)庫的主機、端口、SID 或服務(wù)名。

USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))(CONNECT_DATA=(SID=orcl)))'

HOST=192.168.1.100 遠程數(shù)據(jù)庫的 IP 地址PORT=1521 遠程數(shù)據(jù)庫的端口（Oracle 默認 1521）SID=orcl 遠程數(shù)據(jù)庫的 SID

十、企業(yè)代碼b解析

SELECT trunc(h.makedate) 日期,

'$code' 銷售方代碼,

'$name' 銷售方名稱,

w.producer 生產(chǎn)廠家,

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 c

WHERE h.compid = c.compid

AND h.goodid = w.goodid

AND h.cstid = c.cstid

and w.goodid in (select goodid

from v_tz_zl_waredict@zd

where ori_ownerid = '27'

and cstname = '廠商名字')

10.1 日期截斷 trunc（）

TRUNC() 是 Oracle SQL 中的一個日期處理函數(shù)，主要用于截斷日期，它可以去除日期中的時間部分（小時、分鐘、秒等），只保留日期部分（年、月、日）。例如，如果 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 中的一個字符串函數(shù)，用于查找一個子字符串在另一個字符串中出現(xiàn)的位置。它返回子字符串在目標字符串中首次出現(xiàn)的位置（索引），如果沒有找到子字符串，則返回 0。

INSTR(string, substring [, start_position [, match_occurance [, return_option [, scan_direction [, match_character]]]]])

string：目標字符串，也就是我們要搜索的字符串。substring：要查找的子字符串。start_position：可選參數(shù)，指定從目標字符串的哪個位置開始查找。默認為 1，表示從第一個字符開始查找。match_occurance：可選參數(shù)，指定查找的子字符串出現(xiàn)的第幾次。默認為 1，表示查找第一次出現(xiàn)的子字符串。return_option：可選參數(shù)，決定返回值的類型：

0 或省略：返回子字符串的起始位置。1：返回子字符串的結(jié)束位置。scan_direction：可選參數(shù)，指定查找方向：

1：從左到右（默認）。-1：從右到左（即反向查找）。match_character：可選參數(shù)，指定大小寫敏感的字符集或字符集標志。

?假設(shè)你有以下字符串：'Hello, world!'，你想查找 'world' 在字符串中的位置：

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

返回結(jié)果：?

POSITION

--------

8

h.bthdesc 是表 st_flow 中的一個列，可能包含一些描述信息。假設(shè)它是一個文本字段，里面存儲了類似這樣的字符串：

"生產(chǎn)批號: ABC123 效期: 2025/02/10"

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

INSTR 是 Oracle SQL 中的一個字符串函數(shù)，用于返回子字符串在目標字符串中的位置。h.bthdesc 是目標字符串，'效期:' 是我們要查找的子字符串，1 表示從目標字符串的第一個字符開始查找，最后一個 1 表示只查找第一個出現(xiàn)的位置。假設(shè) h.bthdesc 的值是 "生產(chǎn)批號: ABC123 效期: 2025/02/10"，那么 INSTR(h.bthdesc, '效期:', 1, 1) 的結(jié)果會是 10，表示 '效期:' 在字符串中的起始位置是第10個字符。

10.3?將字符串轉(zhuǎn)換為日期類型?TO_DATE

TO_DATE(string, format_mask)

將給定的字符串 string 按照指定的 format_mask 格式解析，轉(zhuǎn)換為一個日期類型。這個日期類型是 Oracle 數(shù)據(jù)庫內(nèi)部用于存儲日期的格式，支持進行日期運算和比較。

1. 將字符串 '2025/02/10' 轉(zhuǎn)換為日期類型：

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

????????這個查詢將把 '2025/02/10' 這個字符串按照 年-月-日（yyyy/mm/dd）的格式轉(zhuǎn)換為 Oracle 的日期類型。轉(zhuǎn)換后的結(jié)果會是 2025年2月10日，而且可以進行日期運算（例如：加減天數(shù)、月數(shù)等）。

2. 將字符串 '10-FEB-2025' 轉(zhuǎn)換為日期類型：

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

????????這個查詢將把 '10-FEB-2025' 字符串按照 日-月-年（dd-mon-yyyy）的格式轉(zhuǎn)換為日期類型。這個格式中的 mon 是指月份的簡寫（如 JAN，F(xiàn)EB，MAR，等等）

使用 TO_DATE 時要注意的幾點：

日期格式與實際字符串的格式必須匹配：

格式模板（format_mask）必須與輸入的日期字符串格式完全一致。否則，Oracle 會拋出錯誤。 默認日期格式：

如果沒有顯式指定格式，Oracle 會使用默認的日期格式（通常是 DD-MON-YY，即 10-FEB-25）。如果不清楚輸入日期的格式，建議始終明確指定格式。 日期類型和字符串類型的區(qū)別：

TO_DATE 函數(shù)將返回一個 日期類型，而不是字符串。Oracle 會按照其內(nèi)部的日期格式存儲和處理這些數(shù)據(jù)。 時間部分的處理：

如果 string 中包含時間信息，TO_DATE 會將時間部分一起解析。如果沒有時間信息，時間部分默認為 00:00:00（即午夜）。 不同的數(shù)據(jù)庫可能有不同的默認日期格式：

不同數(shù)據(jù)庫管理系統(tǒng)（DBMS）可能有不同的默認日期格式。在 Oracle 中，通常是 DD-MON-YY，而在其他 DBMS 中可能是 YYYY-MM-DD，因此在跨數(shù)據(jù)庫開發(fā)時，需要特別注意日期格式的處理。

柚子快報激活碼778899分享：Oracle常見語法

http://yzkb.51969.com/