Oracle資料庫常用函數使用示例

来源:https://www.cnblogs.com/NiceTime/archive/2018/03/13/8562738.html
-Advertisement-
Play Games

Oracle資料庫基礎簡介及實踐 1、開始(p1~p2)2、SQL語句編寫思路(p3)3、Oracle常用運算符介紹(p4~p5)4、DML語句介紹(p6~p11)5、Oracle常用函數介紹(p12~p30)6、SQL語句函數運用(p31~p34)7、通過日誌獲取SQL語句(p35~p49)8、使 ...



Oracle資料庫基礎簡介及實踐

1、開始(p1~p2)
2、SQL語句編寫思路(p3)
3、Oracle常用運算符介紹(p4~p5)
4、DML語句介紹(p6~p11)
5、Oracle常用函數介紹(p12~p30)
6、SQL語句函數運用(p31~p34)
7、通過日誌獲取SQL語句(p35~p49)
8、使用case系統提交問題(p50~p51)
9、學習資源及答疑(p52)
10、結尾(p53)

 

1、SQL語句編寫思路
1、首先確定最終輸出結果的列，包括幾個方面：
1)這些列來自1個表還是多個表，如果是多個表則可能用到多表查詢(等值、不等值、左連接、右連接、外連接、自連接)
2)確定獲取列的值，是直接查詢得到，還是需要進行(函數、分組、運算、列的子查詢)操作才能得到，列是否需要別名
2、確定輸出列的值和查詢條件是否來自多個表，如果來自多個表則要用到多表查詢
3、確定輸出列的值，是否可以直接查詢獲得還是要通過子查詢才能獲得，如果要用到子查詢，則需要加上where
4、根據輸出列的結果和條件，判斷是否要用到分組(group by)，比如分類、統計、分組、最大、最小、平均、每個等字眼就需要用到group by
5、是否對分組後的結果進行過濾，需要則要用到having
6、是否對輸出的結果進行排序，需要則要用到order by


2、Oracle常用運算符介紹
1、使用||可連接字元
2、使用distinct可以消除重覆行
3、運算符：
1) 算術(+ - * / )
2) 比較(> >= < <= = != <> ) any(值1,值2...) all(值1,值2...) 不能單獨使用，要配合>,>=,<,<=來使用
3) 邏輯(and or not)

4、SQL操作符：
1) in(值1,值n)
2) like '模式字元串'：_代表任意一個字元，%代表0到n個字元
3) between 數值1 and 數值2：不僅可用於數值，還可以用於日期時間
4) is null、is not null：匹配空值，非空值。對於空值一定不能用=或!=
5)union (取並集，重覆的記錄行只顯示1行) 、union all(取並集)、intersect(取交集)、minus(取差集，返回左邊表差集後的記錄)
5、偽列：
rowid: Oracle內部對每個表的每一行都有一個唯一的標識
rownum: Oracle對每次查詢結果集的每一行記錄都有一個行號，對rownum只能使用<或=


3、 DML語句介紹
1、查詢(select)
select distinct * |列 as 別名|表達式|函數|列運算|子查詢
from 表1 別名1,表n 別名n |子查詢
where 條件1(> >= < <= <>) 條件2 | 範圍(between 條件1 and 條件2)
In(子查詢)
like (條件_%)
組合(or|and|not)
子查詢(select 語句)
group by列
having 分組函數(max,count,sum等) 運算符 普通值|子查詢
order by 列 別名 或 數字


2、新增(insert into)
insert into 表名(列名1,..) values(值1,..)
--新增信息
insert into check
(PRIMARY_ID,
NAME,
DISTRICT,
UPDATED_DATE)
values
(sys_guid(), sysdate);

--只複製表結構加入了一個永遠不可能成立的條件1=2，則此時表示的是只複製表結構，但是不複製表內容
create table 用戶名.目標表名 as select * from 用戶名.源表名 where 1=2;

--完全複製表(包括創建表和複製表中的記錄)
create table 用戶名.目標表名 as select * from 用戶名.源表名;

--將多個表數據插入一個表中
insert into 用戶名.目標表名(欄位1,欄位n) (select 欄位1,欄位n)
from 用戶名.源表名 union all select 欄位1,欄位n from 表;


3、修改(需加commit;關鍵字來提交)
update 表名 set 列名=列改變值(where 條件表達式);
update check_store cs set cs.DISTRICT='310113' where cs.NAME='數據包';
commit;

4、刪除(需加commit;關鍵字來提交)
delete from 表名 (where 條件表達式);
delete from check_operate co where where co.NAME='數據包';
commit;


4、Oracle常用函數介紹
1、日期字元函數
1)to_char
--2016-11-03
select to_char(sysdate, 'yyyy-mm-dd') from dual;
2)to_date
select to_date(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;
3)to_number
to_number(varchar2 or char,'format model')
9：代表一個數字
0：強迫0顯示
$：顯示美元符號
L：強制顯示一個當地的貨幣符號
.：顯示一個小數點
,：顯示一個千位分隔符號


--1234.678
select to_number('$1234.678', '$9999.999') from dual;
--15
select to_number('f', 'x') from dual;
2、聚合函數
1)最大值函數：max([distinct|all]x)，最小值函數：min ([distinct|all]x)
可以作用於數值型數據、字元串、日期時間數據類型的數據，max(null),min(null)返回null
對字元型數據的最大值，是按照首字母由A~Z的順序排列，越往後，其值越大。
對於漢字則是按照其全拼拼音排列的，若首字元相同，則比較下一個字元，以此類推。
對於日期時間類型的數據也可以求其最大/最小值，其大小排列就是日期時間的早晚，越早認為其值越小。


2)求和函數sum([distinct|all]x)，統計記錄數函數：count(*|[distinct|all]x)，求平均值函數：avg([distinct|all]x)
count(*)：將返回表格中所有存在的行的總數(包括null的行)
count(列名)：將返回表格中除去null以外的所有行的總數
count(distinct+列名)：與count(列名)相同
3、特殊函數
1) 唯一字元串函數： sys_guid()
生成由32位長度由大寫字母和數字組成的唯一字元串
select sys_guid() from dual;
結果：4061D1372CEA31E4E05318E01F0A5902


第3個參數不填寫會被填充空格
2）左填充函數：lpad(string,padded_length,[pad_string])
select lpad(1, 6, 't') from dual;--ttttt1
select lpad(1234567, 6, 't') from dual;--123456
3)右填充函數：lpad(string,padded_length,[pad_string])
select rpad(1, 6, 't') from dual;--1ttttt
select rpad('abcdefg', 6, 't') from dual;--abcdef
4)隨機包函數


1) 返回0~1間的38位精度的隨機數函數(包括0.0，不包括1.0)：dbms_random.value
--0.474199333601534
select dbms_random.value from dual;
2)返回a~b之間的隨機數的函數(包括a=1，不包括b=10)：dbms_random.value(1,10) ：
--4.63158069726374
select dbms_random.value(1,10) from dual;
3)產生正態分佈的隨機數的函數： dbms_random.normal


/*
註意：normal函數返回從正態分佈的一組數。此正態分佈標準偏差為1，期望值為0。
這個函數返回的數值中有68%是介於-1與+1之間， 95%介於-2與+2之間，99%介於-3與+3之間
*/
---0.241547252937121
select dbms_random.normal from dual;
4)返回指定長度的的字元串的函數： dbms_random.string(opt char, len NUMBER)


/*
'u'或'U'–>返回大寫字母
'l'或'L'–>返回小寫字母
'a'或'A'–>大小寫字母混合
'x'或'X'–>大寫字母和數字混合
'p'或'P'–>任意可顯示字元
*/
--LSNB
select dbms_random.string('u',4) from dual;
5)返回一個隨機數的函數： dbms_random.random


--範圍：-power(2,31) <= random < power(2,31)
--35725665
select dbms_random.random from dual;
6)返回絕對值函數：abs
--1949
select abs(-1949) from dual;
7)返回大於或等於給出數字的最小整數：ceil
--7
Select ceil(6.5) from dual;
8)返回小於或等於給出數字的最大整數：floor
--6
Select floor(6.5) from dual;

 

9) 返回保留指定位數的數字：round(number,digits)
/*
要四捨五入的數，digits是要小數點後保留的位數
如果 digits 大於 0或不填寫，則四捨五入到指定的小數位
如果 digits 等於 0，則四捨五入到最接近的整數
如果 digits 小於 0，則在小數點左側進行四捨五入
*/
--3.687
select round(3.6873,3) from dual;


10)nvl(expr1,expr2)
expr1為null，返回expr2，否則返回expr1
參數expr1、expr2可以是任何數據類型，但應該保持相同。
若兩者數據類型不一致，則Oracle資料庫會隱式的轉換其中一個的數據類型使其保持和另一個一致，若無法轉換則會返回錯誤。
--空
select nvl(null,'空') from dual;
--1
select nvl('1','空') from dual;


11)nvl2(expr1,expr2,expr3)
如果expr1不是null值，則expr2，否則就返回expr3。
參數可以返回任何數據類型的值，但是expr2和expr3不能是LONG型的數據類型
--空
select nvl2(null,'非空','空') from dual;
--非空
select nvl2('1','非空','空') from dual;


12)nullif(expr1,expr2)
參數expr1,expr2
常量、列名、函數、子查詢或算術運算符、按位運算符以及字元串運算符的任意組合，參數中不能有null。
返回類型與第一個 expr1 相同
如果兩個表達式相等，則返回空值null
如果兩個表達式不相等，則返回expre1的值
--
select nullif(1,1) from dual;
--1
select nullif('1','') from dual;
--1
select nullif('1',' ') from dual;


13)返回字元串位置的函數：instr(source,search, start_position,nth_appearance)
source：源字元串
search：被查找的字元串
start_position：從源字元串中哪個位置開始查找字元串，可省略，預設為1，正整數，從左到右查找，負整數從右到左查找
nth_appearance：第幾次出現被查找的字元串，可不填寫，預設為1，不能為負數
也可以找到單個字元
--8
select instr('2016-11-03 16:24:20', '-', 1, 2) from dual;


14) 返回截取後的字元串：substr(strings|express,start,[length])
strings|express ：被截取的字元串或字元串表達式
start：從哪個位置開始截取，正數(在字元串的指定位置開始)，負數(從字元串結尾的指定位置開始)，0(在字元串中的第1個位置處開始)
length：可選，指定要截取的字元串長度，預設時返回字元表達式的值結束前的全部字元
--01
select substr('2016-11-03',2,2) from dual;
--03
select substr('2016-11-03',-2,2) from dual;
--16-11-03
select substr('2016-11-03',3) from dual;


15) 值轉換函數：decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,預設值)
條件可以為欄位或表達式
當條件等於值1時，函數翻譯值1，條件等於值2時，函數翻譯值2，都不符合時，返回預設值
-- 9大於3
select decode(sign(9-3),1,'9大於3',-1,'9小於3',0,'9等於3','不知道') from dual;
16)判斷正負數函數：sign(變數1-變數2)
根據(變數1-變數2)的值是0、正數、負數，返回0、1、-1


17) translate(Str,fromStr,toStr)
1、將Str中的字元串，替換後返回，按fromStr與toStr一一對應的方式，如果不能一一對應則被視為空值。
2、如果fromStr字元串長度比toStr長，則fromStr字元串比toStr字元串，多出的字元將被刪除。
3、translate中的任何參數為null，那麼結果也是null。
--1 9XXX999 將數字轉換為9，其他的大寫字母轉換為X，然後返回
select translate('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') result
from dual;


--2 2229 將數字保留，將其他的大寫字母移除
select translate('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789') as result
from dual;
--3 我是Ch人,我愛Ch 按照字元來處理，不是按照位元組來處理，如果toStr的字元數比fromStr多的話，多出的字元會被刪除
select translate('我是中國人,我愛中國', '中國', 'China') "result"
from dual;


--4 I m 中國ese, I love 中國 如果fromStr的字元數大於toStr，多出的字元會被刪除
select translate('I am Chinese, I love China', 'China', '中國') as "result"
from dual;
--5 如果參數為空或空字元串，整個返回null
select translate('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', null) "結果"
from dual;
--6 **人 只顯示賬戶中的姓名最後一個字，其餘的用星號代替
select translate('中國人',
substr('中國人', 1, length('中國人') - 1),
rpad('*', length('中國人'), '*')) as "結果"
from dual;


18)排序函數：
1、row_number() over([partition by colum] order by colum)
為有序組中的每一行返回一個唯一的排序值，序號由 order by 子句指定，從 1 開始，即使具有相等的值，排位也不同。
partition by colum 按列值進行區分，各分組內在進行排序。
2、dense_rank() over([partition by colum] order by colum)
計算一個值在一個組中的地位，由 1 開頭，具有相等值得行排位相同，並且排位是連續的。
3、rank() over([partition by colum] order by colum)
計算一個值在一個組中的地位，由 1 開頭，具有相等值得行排位相同，序數隨後跳躍相應的數值。


5、SQL語句函數運用

分組排序的示例
--按日期統計(角色1) 用戶的數量
select trunc(cr.created_date, 'dd'),
count(case
when cr.role_id = 'rolea' then
1
else
null
end) as 角色1
from check_role cr
group by trunc(cr.created_date, 'dd')
order by trunc(cr.created_date, 'dd') desc;


使用偽列，字元串操作示例
--返回最新版本號
select flowId
from (select row_number() over(partition by cr.key_ order by cr.version_ desc) rnum,
substr(cr.key_,
instr(cr.key_, '_', 1, 2) + 1,
length(cr.key_)) cityId,
cr.ID_ flowId,
cr.version_
from check_record cr
where cr.key_ like 'check_test%')
where rnum = 1;


decode函數示例
--我的消息表
select decode(tn.type,1,'類型1',2,'類型2',3,'類型3',4,'類型4') as "產品類型", tn.user_id as "用戶編號", count(*) as "消息條數"
from test_news tn
where tn.user_id is not null
and tn.created_date >=
to_date('2014-11-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by decode(tn.type,1,'類型1',2,'類型2',3,'類型3',4,'類型4'), tn.user_id
order by decode(tn.type,1,'類型1',2,'類型2',3,'類型3',4,'類型4') desc, tn.user_id desc, count(*) desc

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 操作系統筆記（七）進程同步
    資源競爭 相互隔絕：兩個進程不會同時進入critical section progress: critical section之外的進程不會阻止其他進程進入他們的critical section bounded waiting: 每個進程等待有限時間即可進入critical section spee ...
  • xtrabackup使用
    轉自：https://www.cnblogs.com/waynechou/p/xtrabackup_backup.html 閱讀目錄 xtrabackup 選項 xtrabackup 全量備份恢復 xtrabackup 增量備份恢復 轉自：https://www.cnblogs.com/waynec ...
  • ubuntu配置SSH
    更新源列表 打開"終端視窗"，輸入"sudo apt-get update" 安裝ssh 打開"終端視窗"，輸入"sudo apt-get install openssh-server"-->回車-->輸入"y"-->回車-->安裝完成。 查看ssh服務是否啟動 打開"終端視窗"，輸入"sudo p ...
  • 掃盲 Linux：如何選擇發行版
    Linux 的發行版實在是太多了。初次接觸 Linux 的同學，面對這麼的發行版，估計會有點暈。所以，在寫完《新手如何搞定 Linux 操作系統》一文之後，俺接著來掃盲一下 Linux 的發行版。 ★"內核"與"發行版"的關係 對於新手而言，需要先搞清楚這兩個概念（已經明白的同學，請跳過本節）。 ◇ ...
  • 痞子衡嵌入式：飛思卡爾i.MX RT系列微控制器介紹篇（1）- 概覽
    大家好，我是痞子衡，是正經搞技術的痞子。今天痞子衡給大家介紹的是飛思卡爾i.MX RT系列MCU的基本特性。 ...
  • SQL Server等待事件—RESOURCE_SEMAPHORE_QUERY_COMPILE
    等待事件介紹 關於等待事件RESOURCE_SEMAPHORE_QUERY_COMPILE，官方的介紹如下： Occurs when the number of concurrent query compilations reaches a throttling limit. High waits ... ...
  • 安裝SQL Server 2016出錯提示：需要安裝oracle JRE7 更新 51(64位)或更高版本完美解決辦法
    錯誤提示原因：安裝時檢測出電腦沒有安裝JDK，而且是版本7(其他版本不行) 解決方法：先進下麵這個網站安裝JDK，安裝好後配置環境變數，然後重新安裝SQL Server 2016即可 http://www.oracle.com/technetwork/java/javase/downloads/ja ...
  • Spark學習筆記之RDD中的Transformation和Action函數
    總算可以開始寫第一篇技術博客了，就從學習Spark開始吧。之前閱讀了很多關於Spark的文章，對Spark的工作機制及編程模型有了一定瞭解，下麵把Spark中對RDD的常用操作函數做一下總結，以pyspark庫為例。 RDD 的操作函數(operation)主要分為2種類型 Transformati ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類