Oracle之單表查詢及常用函數

標籤：acl   出現   name   day   使用   java   日期   val   asc   

1.文法：
　　select 欄位列表
　　from 表名
　　[where 查詢條件]
　　[group by 分組]
　　[having 分組條件]
　　[order by 排序]

 

select * 代表查詢所有的欄位    select id as "編號",sname 學生姓名,age "【年齡】"  --as 之後是別名 也可以直接省略    select t.*    from t_student t -- 給表取別名    where classid is null -- 空判斷    where  age not in (20,23,...) --範圍判斷    where age between 20 and 25   --區間判斷between‘A‘ and ‘Z‘    where  sname not like ‘%江%‘   --sname like ‘江%‘  -- ‘江%‘ 以江開頭 ‘%江‘ 以江結尾 ‘%江%‘ 包含江                     --模糊查詢 _一個底線表示一個位置    order by  age -- asc 升序 desc降序 預設升序    select distinct age,sex from t_student   --distinct 去掉重複記錄

統計函數：
　　count:統計條數

select count(*) from t_student    select count(id) from t_student     select count(classid) from t_student     -- 統計的是該列中非空的記錄的個數    select count(1) from t_student;    select id,sname,age,sex,classid,1,2,3 from t_student     sum:求和    select sum(age) from t_student;     min:取最小值    select min(age) from t_student;     max:取最大值    select max(age) from t_student;     avg:取平均值    select avg(age) from t_student;          select sum(age),min(age),max(age),avg(age) from t_student;

count(1)與count(*)比較：

　　如果你的資料表沒有主鍵，那麼count(1)比count(*)快
　　如果有主鍵的話，那主鍵（聯合主鍵）作為count的條件也比count(*)要快
　　如果你的表只有一個欄位的話那count(*)就是最快的啦
　　count(*) count(1) 兩者比較。主要還是要count(1)所相對應的資料欄位。
　　如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
　　因為count(*),自動會最佳化指定到那一個欄位。所以沒必要去count(?)，用count(*),sql會幫你完成最佳化的

count詳解：
　　count(*)將返回表格中所有存在的行的總數包括值為null的行，然而count(列名)將返回表格中除去null以外的所有行的總數(有預設值的列也會被計入）.
　　distinct 列名,得到的結果將是除去值為null和重複資料後的結果

　　group by :分組函數
　　　　select age,sex
　　　　from t_student
　　　　group by age,sex
　　--注意：分組函數中的 欄位列表只能出現分組的欄位和統計函數
　　-- 分組函數在沒有統計函數使用的時候作用和 distinct 是一樣的
　　　　select sex,count(sex)
　　　　　from t_student
　　　　group by sex
　　--分組函數【彙總函式】在沒有和 group by 一塊使用的時候統計的是查詢的所有的資料
　　--如果和 group by 一塊使用的化，那麼統計的是分組後的各組資料

　　

select classid,sex,count(1)    from t_student    group by classid,sex    having count(1) > 1 -- 分組後的條件    select classid,sex,count(1)    from t_student    where age > 20 -- 分組之前加條件    group by classid,sex

where 和 having 的區別
　　where 只能跟在 from 後面 表示對查詢的資料來源過濾
　　having 只能出現在 group by 後面，對分組後的資料進行過濾，

 

常用函數：
　　concat:串連函數
　　select concat(id,sname)，length(sname) from t_student
　　日期函數：
　　　　字串轉date: to_date
　　　　update t_student set birth=to_date(‘1990-01-01‘,‘yyyy-mm-dd‘)
　　　　date轉字串: to_char

select sysdate       ,to_char(sysdate,‘yyyy-mm-dd hh:mi:ss‘)--在資料庫中是HH24 mi       ,to_char(sysdate,‘yyyy-mm-dd‘)       ,to_char(sysdate,‘yyyy-mm‘)       ,to_char(sysdate,‘yyyy‘)    from dual;

months_between(sysdate,date);--兩者時間的月份數

add_months:在目前時間的基礎上增加月份數
select add_months(sysdate,12) from dual;

last_day():返回指定日期的當月的最後一天
select last_day(sysdate) from dual;

extract:截取日期指定部分的內容
select extract(DAY from sysdate) from dual; --dual是一個系統內建的虛表

nvl(column,value);如果查詢的欄位為null，就用預設值填充
select id,sname,sex,nvl(sex,‘哈哈‘) from t_student

    decode:類似於Java中的if語句    select    id,sname,sex        ,decode(sex,1,‘男‘) -- if(sex == 1){男}        ,decode(sex,1,‘男‘,2,‘女‘) -- if(){}else if(){}        ,decode(sex,1,‘男‘,2,‘女‘,‘不詳‘)--if(){}else if(){}else{}    from t_student

rowid:行id，資料存放區的位置，唯一
rownum:行號,系統自動維護的，從1開始自增，有1才有2
select t.*,rownum from t_student t

查詢出學生表中前5條的學生記錄
select t.*,rownum from t_student t where rownum <=5

查詢出學生表中第5條到第10條的記錄
select t.*,rownum from t_student t where rownum >=5 and rownum <=10 ---這是錯誤的寫法

---分頁查詢的實現方式(在Oracle中要這麼實現)    select t1.*,rownum    from     (select t.*,rownum num from t_student t) t1 --先查詢所有的資料和行號    where t1.num >= 5 and t1.num <=10           --再從中選出想要的部分    select t2.*,rownum    from     (select t1.* ,rownum num from t_student t1 where rownum <=10) t2 --先取上限的資料及行號    where t2.num >=5   --再取下限的資料

case的使用
查詢出學生表中年齡在【20歲以下】【21-25】【26以上】分別有多少人

select t.*,            case when age <= 20 then 1 else 0 end "21以下"            ,case when age >20 and age <26 then 1 else 0 end "21-25"            ,case when age >= 26 then 1 else 0 end "26以上"        from t_student t                select             sum(case when age <= 20 then 1 else 0 end ) "21以下"            ,sum(case when age >20 and age <26 then 1 else 0 end) "21-25"            ,sum(case when age >= 26 then 1 else 0 end )"26以上"        from t_student t                select             count(case when age <= 20 then 1 else null end ) "21以下"            ,count(case when age >20 and age <26 then 1 else null end) "21-25"            ,count(case when age >= 26 then 1 else null end )"26以上"        from t_student t

 

Oracle之單表查詢及常用函數