Oracle入門2

單表查詢

# 查詢一個表的所有欄位
select * from 表名;
# 指定欄位查詢
select 欄位名(，連接) from 表名;
# 可以在指定字典查詢時，使用||進行拼接
select 欄位名||欄位名 from 表名;
# 欄位的值在顯示的時候可以進行簡單的數學運算(不局限于*)
select 欄位名*n from 表名;
# 可以使用別名，來修改欄位的顯示資料(只修改顯示結果，欄位名稱不會因為查詢而改變)
select 欄位名 as 別名 from 表名;


# where條件篩選
# Oracle中所有的系統關鍵字，欄位名，表名，在編譯的時候，都會被強制轉化為大寫
# 但是欄位的值不會，嚴格區分大小寫
select * from 表名 where 欄位名=欄位值;
# and 并列關系
select * from 表名 where 欄位名>n and 欄位名<m; # 查詢欄位值在n-m之間的資訊
# or 或
select * from 表名 where 欄位名>n or 欄位名=m; # 查詢欄位值大于n或者=m的資訊
# not 取反
select * from 表名 where not 欄位名=n；# 查詢欄位名不為n的資訊
# between A and B 閉區間
select * from 表名 where 欄位名 between A and B; # 查詢欄位名在A和B之間的資訊

空值處理

? 如果某一個欄位對應的欄位值為空，可以指定值為null的資料如何顯示

# 查詢欄位值為空的資訊
select * from 表名 where 欄位值 is null;
# 查詢欄位值不為空的資訊
select * from 表名 where 欄位值 is not null;

# nvl函式，可以對空值的顯示進行處理
select 欄位1,欄位2,nvl(欄位3,0) from 表名; 
# 查詢欄位1,2,3的資訊，欄位3的資訊如果為空就顯示為0

# nvl2函式
select 欄位1,欄位2,nvl2(欄位3,a,n) from 表名; 
# 查詢欄位1,2,3的資訊，欄位3的資訊如果為空則顯示n，如果非空則顯示a 

聚合函式

? 對多行資料進行統計的韓式，如果沒有指定資料統計的范圍則默認統計所有資料

# min 統計最小
select min(欄位) from 表名;
# max 統計最大
select max(欄位) from 表名;
# avg 統計平均 (floor 對小數進行向下取整)
select floor(avg(欄位)) from 表名; # 查詢欄位的平均值，并向下取整
# sum 統計和
select sum(欄位) from 表名;
# count 統計數量 (統計的是非空欄位值的數量)
select count(欄位) from 表名;
# 大部分用于統計指定資料的行數
# count(*)是sql陳述句的標準用法
select count(*) from 表名;

group by分組

? 根據 group by 后面的欄位將所有欄位值相同的資料進行分組

# 將欄位2的值相同的資料歸為一組，查詢每一組欄位1的平均值
select avg(欄位1) from 表名 group by 欄位2;
# 不局限于求平均
select max(欄位1),min(欄位1),sum(欄位1) from 表名 group by 欄位2;
# 資料在被分組之后，求從行資料編程組資料，不可以再查詢子資料
# 在使用 group by 之后，select之后，只可以出現杯分組的欄位本身和族中聚合函式

# group by 可以基于多個欄位進行分組
select count(*) from 表名 group by 欄位1,欄位2;

條件篩選（having）

? having用于篩選分組之后的資料（組資料），where用于篩選分組之前的資料（行資料）

# 對欄位2的值為1的資料進行篩選，并對欄位4的值大于20的資料進行分組，顯示這些組的欄位1的平均值
select avg(欄位1) from 表名 where 欄位2=1 group by 欄位3 having 欄位4>20;

排序（order by）

? 按照指定的欄位值，對資料進行排序

# 根據欄位1進行升序排序,asc可以忽略
select * from 表名 order by 欄位1 asc;
# 根據欄位1進行降序排序,desc不可以忽略
select * from 表名 order by 欄位1 desc;

# 多條件排序
# 排序的時候，必須要對排序欄位的空值進行處理，因為控制在排序時，會默認無窮大
# 根據欄位1和欄位2進行降序排序，并且處理欄位2出現空值的情況（遇到空值置0）
select * from 表名 order by 欄位1,nvl(欄位2,0) desc;

distinct 去重處理

select distinct 欄位1 from 表名 order by 欄位2;
# 對欄位2進行分組，并根據欄位2進行去重處理

sql陳述句的執行順序

from -> where -> group by -> having -> distinct -> select -> order by

不相關子查詢

? 將一條陳述句的查詢結果，作為另一條陳述句的查詢條件或者是資料來源

? 子查詢不需要外部查詢提供任何條件就可以獨立運行，外部查詢會等待子查詢執行完成之后，再執行

select * from 表名 where 欄位1>(select * from 表名 where 欄位2=n);
# 查詢欄位1的值大于欄位2等于n的資料的欄位1的值的資訊

# 不相關子查詢可以進行簡單的跨表查詢
# 如果可以確定子查詢的結果為唯一值，可以使用等號
# 如果不可以，則需要使用in
select * from 表2 where 欄位2-1 in
(select 欄位1-1 from 表1 where 欄位1-2=n);
#先查詢表1中欄位1-2等于n的資料的欄位1-1的值，然后再根據表2中欄位2-1在剛才查找到的欄位1-1中的資訊

別名的使用

? 別名除了在欄位后可以使用之外，還可以在其他地方使用，但是使用的時候，必須注意辨明的定義必須在別名的使用之前，

? 別名的使用于sql陳述句的執行順序息息相關

# 查詢表1 中的欄位1和欄位2的平均值，并且按照平均值降序排序（給平均值起別名）
select 欄位1,avg(欄位2) as 別名 from 表1 order by 別名 desc;

模糊查詢

? 根據匹配情況進行查詢

# 查詢欄位1以a開頭的資訊
select * from 表1 where 欄位1 like 'a%';
# 查詢欄位1第二個字母為a的資訊
select * from 表1 where 欄位1 like '_a%';
# 查詢欄位1第二個字母為a 且倒數第二個字母為b 的資訊
select * from 表1 where 欄位1 like '_a%b_';

常用的單行函式

? dual 虛表：專門用來顯示資料的，本身并沒有資料

# 查看當前時間(sysdate)
select sysdate from dual;
# 查看日期
# add_months的引數也可以是我們表中的日期型別的欄位，也可以時我們使用to_date函式轉換所生成的日期型別資料
select sysdate,add_months(sysdate,1) from dual;
# 查詢2044年1月1日之后100個月的日期
select add_months(to_date('2044-01-01','YYYY-MM-DD'),100) from dual;
# last_day 獲取指定日期所在月的最后一天
select last_day(sysdate) from dual;

# 例子：
# emp表中有一個欄位，名為入職時間（hierdate），計算emp表中所有員工的工齡
# 1.日期型別的資料是可以直接相減，得到的時天數，除以365即可得到年數
select ename,floor((sysdate-firedate)/365) as 工齡 from emp;
# 2.使用to_char函式，將日期型別的資料轉換為字串型別
#    在Oracle中如果字串時由純數字組成的可以直接進行計算
select ename,to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY') as 工齡 from emp;
# 3.使用extract函式，從指定日期中提取年份，也可以提取日，月，時，分，秒
select ename,extract(year from sysdate)-extract(year from firedate) as 工齡 from emp;

minus union 去交集和取并集

# 如果需要做union并集的查詢結果，并不是來自于同一張表
# 需要保證兩張表的欄位數量，欄位資料型別相同即可
# 最終顯示的查詢結果的欄位名稱以第一個表的欄位名稱為標準
select * from 表1 union select * from 表2; # 表1中添加表2有表1沒有的資料，輸出所有資料（表1）

select * from 表1 minus select * from 表2; # 表1中去掉表2有表1也有的資料，輸出剩余資料（表1）

標籤：Oracle

上一篇：Oracle入門1

下一篇：干貨分享｜袋鼠云數堆疊離線開發平臺在小檔案治理上的探索實踐之路