SQL-函數及多表關聯

来源:https://www.cnblogs.com/carlosouyang/archive/2019/05/15/10866279.html
-Advertisement-
Play Games

函數一般是在數據上執行的，它給數據的轉換和處理提供了方便。只是將取出的數據進行處理，不會改變資料庫中的值。函數根據處理的數據分為單行函數和聚合函數（組函數），組函數又被稱作聚合函數，用於對多行數據進行操作，並返回一個單一的結果，組函數僅可用於選擇列表或查詢的having子句；單行函數對單個數值進行操 ...


函數一般是在數據上執行的，它給數據的轉換和處理提供了方便。只是將取出的數據進行處理，不會改變資料庫中的值。函數根據處理的數據分為單行函數和聚合函數（組函數），組函數又被稱作聚合函數，用於對多行數據進行操作，並返回一個單一的結果，組函數僅可用於選擇列表或查詢的having子句；單行函數對單個數值進行操作，並返回一個值。

dual是一個系統表。註意用於測試。

1 字元相關

 1 -- 1.字元串連接
 2 select concat('aa','12') from dual;
 3 select 'aa'||'12' from dual;
 4 
 5 -- 2.首字母大寫
 6 select initcap('abc') from dual;
 7 --- 把大寫轉化小寫
 8 select lower('ABc') from dual;
 9 select upper('abc') from dual;
10 
11 -- 把所有員工的姓名小寫輸出
12 select lower(e.ename),e.empno
13 from emp e
14 
15 -- 3.填充字元lpad/rpad
16 select lpad('sxt',5,'*') from dual;
17 select rpad('sxt',5,'*') from dual;
18 
19 -- 4.去掉空白字元
20 select '  kallen' from dual;
21 select ltrim('  kallen',' ') from dual;
22 select rtrim('  kallen  ',' ') from dual;
23 -- trim 刪除左右兩邊的字元
24 select trim('a' from 'abc') from dual;
25 
26 -- 5.求子串 substr(str,loc,len)-->loc從1開始
27 select substr('abcd',2,2) from dual;
28 
29 -- 6.查找字元串
30 /*
31 如果找到返回>=1的索引；如果沒找到返回0
32 */
33 select instr('abcd','b') from dual;
34 
35 -- 7.求長度
36 select length('abcd') from dual;

2 數值型函數

 1 -- 四捨五入round(x,y)對x保留y為小數
 2 select round(23.652) from dual;
 3 select round(23.652,1) from dual;
 4 select round(25.652,-1) from dual;
 5 
 6 -- 返回x按精度y截取後的值
 7 select trunc(23.652) from dual;
 8 select trunc(23.652,2) from dual;
 9 select trunc(23.652,-1) from dual;
10 
11 -- mod（x，y）求餘數
12 select mod(9,2) from dual;
13 
14 -- ceil 向上取整
15 select ceil(1.9) from dual;
16 -- floor 向下取整
17 select floor(1.9) from dual;

3 日期時間函數

 1 -- 返回系統當前時間
 2 select sysdate from dual;
 3 -- 返回當前會話時區中的當前日期 
 4 select current_date from dual;
 5 
 6 -- 添加月數
 7 select add_months(sysdate,1) from dual;
 8 -- 返回兩個時間相差的月數
 9 select months_between(sysdate,add_months(sysdate,2)) from dual;
10 
11 -- 需求:查詢工作年限在30年以上
12 select e.ename,e.hiredate
13 from emp e
14 where months_between(sysdate,e.hiredate)/12 > 30
15 
16 -- 返回date下一月份的最後一天
17 select last_day(add_months(sysdate,1)) from dual;
18 -- next_day(date1，week) 返回date1下周星期幾的日期
19 select sysdate "當時日期",next_day(sysdate,'Monday') "下周星期一" from dual;

日期計算相關

1 --兩個時間進行四則運算的單位是天
2 select sysdate+2 from dual;
3 select sysdate-2 from dual;

4 類型轉換

4.1 隱式類型轉換

1 --字元和數字/日期之間的隱式轉換
2 -- 字元隱式轉換成數值
3 select '100' - 10 from dual;
4 
5 -- 字元隱式轉化為日期
6 -- DD-MON-RR 預設的日期格式
7 select 1 from dual
8 where sysdate > '13-May-19';

4.2 顯示類型轉換

4.2.1 to_char()

把日期轉化成字元串，格式元素列表如下

1 -- 把日期轉化成字元
2 -- 按照預設格式DD-MON-RR
3 select to_char(sysdate) from dual;
4 -- 按指定格式
5 select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') as t from dual;
6 -- 格式內要打中文（非給定允許的時間分隔符）需要用雙引號引起來

把數值格式化成字元串，控制符如下表

1 -- 把數值格式化成字元串
2 select to_char(12345,'99999.99') from dual;
3 select to_char(12345,'99,999.99') from dual;
4 -- 沒有數的位置補空格
5 select to_char(12345,'999,999.99') from dual;
6 -- 沒有數的位置補0
7 select to_char(12345,'000,000.00') from dual;
8 -- 格式化成美元顯示
9 select to_char(12345,'$000,000.00') from dual;

4.2.2 to_number 、to_date

 1 -- to_number
 2 select to_number('$12,345','$99,999') from dual;
 3 select to_number('$12,345.12','$99,999.99') from dual;
 4 
 5 -- to_date
 6 select to_date('14-May-19','DD-MON-RR') from dual;
 7 select to_date('2004-09-19','YYYY-MM-DD') from dual;
 8 
 9 -- 查詢雇用期滿6個月的下一個周一的日期。
10 select e.ename,e.hiredate,next_day(add_months(e.hiredate,6),'Monday')
11 from emp e
12 where months_between(sysdate,e.hiredate) > 6

5 decode/case when

decode(條件，值1,“返回值1"，值2,“返回值2”,,,“預設值”)

1 -- 需求:查詢員工所在的部門名稱
2 select 
3 e.ename,
4 e.deptno,
5 decode(e.deptno,10,'部門1',20,'部門2',30,'部門3','未知')
6 from emp e;

case when - 值匹配

 1 select 
 2 e.ename,
 3 e.deptno,
 4 case e.deptno
 5   when 10 then '部門1'
 6   when 20 then '部門2'
 7   when 30 then '部門3'
 8   else '未知'
 9 end
10 from emp e;

case when - 條件匹配

 1 -- 需求:根據工資分佈輸出以下信息
 2 /*
 3  <1000 真屌絲
 4  (1001,2000] 屌絲
 5  (2001,3000] 白領 
 6  (3001,5000] 高富帥
 7  (5001,10000] 土豪
 8 */
 9 
10 select 
11 e.ename "姓名",
12 e.sal "工資",
13 case
14   when e.sal <= 1000 then '真屌絲'
15   when e.sal <= 2000 then '屌絲'
16   when e.sal <= 3000 then '白領'
17   when e.sal <= 5000 then '高富帥'
18   when e.sal <= 10000 then '土豪'
19   else '未知'
20 end "描述"
21 from emp e;

6 組函數

組函數把多行數據經過運算後返回單個值。也稱聚合函數。

 1 -- 求公司雇員的數量
 2 select count(*)
 3 from emp e;
 4 
 5 select count(e.empno) 
 6 from emp e;
 7 
 8 select count(1)
 9 from emp e;
10 
11 -- avg:對多個記錄的某個欄位求平均值
12 -- 需求:求底薪的平均值
13 select avg(e.sal)
14 from emp e;
15 
16 -- 需求:求雇員的最高薪資/最低薪資
17 select max(e.sal),min(e.sal),avg(e.sal)
18 from emp e;
19 
20 -- 需求:求公司一個月的員工基本開銷
21 select sum(e.sal)
22 from emp e;
  1. 組函數或聚合函數是對一個數據集(表數據、查詢出來的表、分組的表)進行聚合。（組函數僅可用於選擇列表或查詢的having子句）
  2. 聚合函數對欄位是 null 的值進行忽略。
  3. max/min 適合任意數據類型，sum/avg 只適用於數值類型。

聚合函數的結果可以作為其他查詢條件。

1 -- 最早入職的員工
2 select e.ename,e.hiredate
3 from emp e
4 where e.hiredate = (select min(e.hiredate) from emp e);

7 分組

在處理統計或聚合數據時，很多時候需要對數據進行分組。語法

1 select field1,...
2 from tableName
3 group by field1[,field2,…]

按照field1[,field2,…] 分組，欄位值相同的記錄分到一組。

7.1 分組和聚合函數

 1 -- 需求:統計部門10的人數
 2 select count(1)
 3 from emp e
 4 where e.deptno = 10;
 5 
 6 -- 需求:求各個部門的平均薪資
 7 select e.deptno,avg(e.sal)
 8 from emp e
 9 group by e.deptno
10 
11 -- 需求:求各個部門的月收入平均值
12 select e.deptno,avg(e.sal+nvl(e.comm,0))
13 from emp e
14 group by e.deptno

7.2 null 值會歸為一組

1 -- 特例:按照津貼分組
2 select e.comm,count(1)
3 from emp e
4 group by e.comm;

7.3 having （重點）

如果需要對分組後的數據進行條件過濾，必須使用having。

 1 -- group by having
 2 -- 查詢部門平均薪資大於3000的部門
 3 select e.deptno
 4 from emp e
 5 group by e.deptno
 6 having avg(e.sal) > 3000
 7 
 8 -- 查詢部門薪資大於3000的雇員按部門分組的平均薪資
 9 select e.deptno,avg(e.sal)
10 from emp e
11 where e.sal > 3000
12 group by e.deptno;
  1. Where過濾行，having過濾分組。
  2. Having支持所有where操作符。

8 排序 (order by)

當需要對數據集進行排序操作時，語法

1 select field1, field2,...
2 from tablename
3 order by field1,field2

對數據集進行排序，先按 field1 排序，如果 field1 排序相同，按照 field2 排序，以此類推。

  • asc 升序,預設
  • desc 降序
 1 -- order by
 2 -- 按雇員薪資排序
 3 select e.ename,e.sal
 4 from emp e
 5 order by e.sal desc
 6 
 7 -- 按薪資升序，名稱降序
 8 select e.ename,e.sal
 9 from emp e
10 order by e.sal,e.ename desc;

綜合運用

1 --薪資大於1200的雇員的平均薪資大於1500的部門，按照平均薪資升序排序 
2 select e.deptno,avg(e.sal)
3 from emp e
4 where e.sal > 1200
5 group by e.deptno
6 having avg(e.sal) > 1500
7 order by avg(e.sal) asc

9 select 語言的執行順序

  1. 讀取from子句中的基本表、視圖的數據，[執行笛卡爾積操作]。
  2. 選取滿足where子句中給出的條件表達式的元組
  3. 按group子句中指定列的值分組，同時提取滿足Having子句中組條件表達式的那些組
  4. 按select子句中給出的列名或列表達式求值輸出
  5. Order by子句對輸出的目標表進行排序。

10 多表關聯

10.1 關聯原理

1 -- 笛卡爾積
2 select *
3 from emp,dept

10.2 等值連接

1 -- 等值連接
2 -- 需求:查詢雇員的部門名稱
3 select e.ename,e.deptno,d.dname
4 from emp e,dept d
5 where e.deptno = d.deptno

10.3 不等值連接

1 -- 查詢每個雇員的薪資等級
2 select e.ename,e.sal,sg.grade
3 from emp e,salgrade sg
4 where e.sal >= sg.losal and e.sal <= sg.hisal
5 -- where e.sal between sg.losal and sg.hisal

10.4 外連接

左外連接：左邊的表作為主表，右邊表作為從表，主表數據都顯示，從表數據沒有的位置，用null填充，用 （+） 在 where 中作為尾碼標識主表。

1 -- 需求:查詢所有部門的雇員
2 select *
3 from dept d,emp e
4 where d.deptno = e.deptno(+)

右外連接: 右邊的表作為主表，左邊表作為從表，主表數據都顯示，從表數據沒有的位置，用null填充，用 （+） 在 where 中作為尾碼標識主表。

1 -- 右外連接（B）
2 select *
3 from emp e,dept d
4 where e.deptno(+) = d.deptno;

10.5 自連接

一個表自身連接自身時，稱為自連接。自連接以不同的視角看待同一張表。

1 -- 查詢每個雇員的上級領導
2 select e.ename "雇員",m.ename "領導"
3 from emp e,emp m
4 where e.mgr = m.empno

10.6 多於兩張表的查詢

如果有多個表參與查詢，先把 t1 x t2 笛卡爾積得到一個大表 T1，再把 T1 x t3 笛卡爾積得到一個另外的大表T2，依次類推。所有的多表查詢最終都是兩種表的查詢。


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

-Advertisement-
Play Games
更多相關文章
  • Nginx應用優化
    案例環境： 一、Nginx隱藏版本號 方式一：修改配置文件 配置版本號隱藏 方式二：修改源碼包 二、網頁緩存、連接超時、網頁壓縮傳輸 1.網頁緩存： 作用：頁面緩存一般針對靜態網頁進行設置，對動態網頁不用設置緩存時間。方便客戶端在日後進行相同內容的請求時直接返回，以避免重覆請求，加快了訪問速度 配置 ...
  • 第十一周LINUX 學習筆記
    keepalived keepalived: 基於vrrp(虛擬冗餘路由協議)的實現 virtual server: 對於IPVS vrrp_script: 調用外部腳本 nginx haproxy等輕量級的實現 所謂的vrrp就是為瞭解決前端路由器如果發生單點故障導致後方伺服器全部不能與外部通信的 ...
  • Nginx應用詳解及配置
    一、Nginx簡介 概述：Nginx是一款由俄羅斯開發的開源的高性能HTTP伺服器和反向代理伺服器，同時支持IMAP/POP3/SMTP代理服務，其性能優勢著為顯著，官網上稱：單台nginx伺服器可以處理50000併發； 特點：高性能、穩定、消耗硬體資源小、能夠處理大併發，主要用於靜態的解析，動靜頁 ...
  • Maven的配置文件-settings.xml內容分解
    本文轉載：https://www.cnblogs.com/jingmoxukong/p/6050172.html 概要 settings.xml有什麼用？ 如果在Eclipse中使用過Maven插件，想必會有這個經驗：配置settings.xml文件的路徑。settings.xml文件是乾什麼的，為 ...
  • UCOSII 之 任務統計
    UCOSII 使用空閑任務的計數值（OSIdleCtr）來實現CPU使用率的統計，首先統計一個固定時間內的計數值保存下來為 （MAX），然後再開啟一個固定的時間段，當時間到達時得到另外一個（OSIdleCtr）的計數值保存為（ctr）。在這一段時間中，當運行用戶任務時空閑任務因為優先順序低無法執行那麼 ...
  • 前一天或後一天
    Insus.NET把它們寫成自定義函數，再程式中直接套用即可。 前一天： SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- -- Author: Insus.NET -- Create date: 2019-05-15 -- Update dat ...
  • 判斷一個日期是不是今天
    以前寫過一個方法，先去參考《T-SQL判斷是否為今天》https://www.cnblogs.com/insus/archive/2012/03/22/2411209.html 今天使用一個更加簡單的方法來實現： 實例演示： ...
  • 比較2個日期是否相同
    以前寫過2篇，關於日期比較的，需求不同，每一篇均有參考價值。 《MS SQL日期比較函數》https://www.cnblogs.com/insus/archive/2011/06/24/2089005.html 《MS SQL兩個日期比較，獲取最大或最小的日期》https://www.cnblog ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類