Oracle總結【SQL細節、多表查詢、分組查詢、分頁】

Java3y發表於2018-03-06
OracleSQL

前言

在之前已經大概瞭解過Mysql資料庫和學過相關的Oracle知識點，但是太久沒用過Oracle了，就基本忘了…印象中就只有基本的SQL語句和相關一些概念….寫下本博文的原因就是記載著Oracle一些以前沒注意到的知識點…以後或許會有用…

例項與資料庫概念

Oracle資料庫伺服器由兩部分組成：

  • 例項：理解為物件,看不見的
  • 資料庫：理解為類，看得見的

我們在安裝Oracle的時候，已經填寫過自己資料庫的名稱了，一般例項與資料庫的名稱是一致的…

這裡寫圖片描述

如果還沒有安裝Oracle資料庫的，可以看一下我其他的博文….

那麼我們自帶的sqlplus黑色視窗與例項，資料庫之間的關係又是什麼的呢？？？我們可以看下圖：

這裡寫圖片描述

Oracle資料庫把表、檢視等都看成是物件：

這裡寫圖片描述

Oracle中的null值

Oracle中如果存在欄位是null值的話，那麼在sqlplus中它是不會顯示出來的….如果我們使用null值的資料與其他資料進行運算…那麼最終得出的結果都是null值

因此，Oracle提供了NVL(表示式1，表示式2)函式供我們使用，如果表示式1的值為null值，那麼就取表示式2的值…當然了，如果表示式1不是null，取的就是表示式1的值

還有值得注意的是：null值不能引數=號運算，null能引數number/date/varchar2型別運算

Oracle提供了 is null關鍵字來代替=號運算的問題

Oracle中的別名

我們知道在Mysql中如果要用別名的話，需要使用as關鍵字 ，後面跟著別名就行了….Oracle可以省略as關鍵字…

並且，一般地，我們使用別名都是用雙引號””把別名括起來，Oracle也支援我們直接寫別名，但是呢，如果我們不寫雙引號，那麼我們的別名是不能有空格的

還有一點的是：Oracle的別名是不能使用單引號來括起來的，Oracle預設認為單引號是字串型別和日期型別的。

IO輸入輸出SQL語句

我們可以在sqlplus中使用spool命令把SQL語句儲存在硬碟中，具體的例子：


    spool e:/oracle-day01.sql;

使用spool off命令，儲存SQL語句到硬碟檔案e:/oracle-day01.sql，並建立sql檔案，結束語句

spool off;

當然了，我們也可以把硬碟中的SQL檔案在sqlplus中執行，只要以下的命令就行了：


    @ e:/crm.sql;

轉義字元

有的時候，我們可能會模糊查詢一些資料，但是呢，在名稱中又有一些特殊的字元。那麼我們就要經過轉義….當然了，如果按照Java的來，就十分簡單了，就寫一個””就可以了。

那在Oracle中是怎麼樣轉義的呢？？我們來看下面的例子：


查詢員工姓名中含有`_`的員工，使用轉義符，讓其後的字元迴歸本來意思【like `%\_%` escape ``】

select * from emp where ename like `%\_%` escape ``;

如果名稱是`單引號呢？？？那麼兩個單引號代表著一個引號


插入一個姓名叫``的員工
insert into emp(empno,ename) values(2222,``````);

單行函式與多行函式

首先，我們要明確一個概念：

  • 單行函式：輸入一個引數，返回一個結果
  • 多行函式：掃描多個引數，返回一個結果….一般地，多行函式和分組函式的概念是差不多的…

Oracle提供了關於字串函式、日期函式供我們對資料進行對應的操作，這裡就不一一贅述了，我們到時候有需要的時候查文件就行了。

這裡寫圖片描述

單引號出現的地方如下：

  • 1）字串，例如：`hello`
  • 2）日期型，例如：`17-12月-80`
  • 3）to_char/to_date(日期,`YYYY-MM-DD HH24:MI:SS`)

雙引號出現的地方如下：

  • 1）列別名，例如：select ename “姓 名” from emp
  • 2）to_char/to_date(日期,`YYYY”年”MM”月”DD”日” HH24:MI:SS`)

GROUP BY 細節

group by 子句的細節：

  • 1）在select子句中出現的非多行函式的所有列，【必須】出現在group by子句中
  • 2）在group by子句中出現的所有列，【可出現可不現】在select子句中

這裡寫圖片描述

舉例子：下面這段程式碼是錯誤的！！！


    select max(avg(sal)) "部門平均工資的最大值",deptno "部門編號"
    from emp
    group by deptno;

為啥是錯誤的呢？？？分組中我們已經有了deptno欄位了，而我們select 後面跟著也就是多行函式和該欄位而已，為啥就錯了呢？？？？？我們如果在分組查詢的時候，使用了多行函式巢狀的話，那麼我們select欄位後面只能跟隨著它這麼一個列，而不能再多了。max(avg(sal)) 相當於又分組了一次

當然了，如果我們僅僅是求出每個部門的平均工資，也就是下面這段程式碼，是完全沒有問題的：

    select avg(sal) "部門平均工資的最大值",deptno "部門編號"
    from emp
    group by deptno;

這裡寫圖片描述

多表查詢、子查詢

當我們一張表不能把資料查詢出來的時候，就需要連線其他的表一起查詢….

當我們的查詢條件還沒知道的時候，我們就可以使用子查詢….

一般地，子查詢和多表查詢的功能都是差不多的….

子查詢出來的資料是單行單列的時候，一般我們都是用等於、大於等於、小於等操作符去限制查詢條件…

如果是單列多行的時候，我們一般都是用IN、ANY、ALL操作符去篩選條件…

如果是多行多列，我們就看成該返回查詢結果是一張表【Oracle分頁就是這個原理】

值得注意的是多表查詢的數學基礎是笛卡爾積，也就是說：如果兩張實體表進行連線，那麼它會構成一張笛卡爾積表…也就是說：最終就只有一張笛卡爾積表

這裡寫圖片描述

連線

在多表查詢的時候，我們由於會產生笛卡爾積，於是在笛卡爾積表中會存在很多無關的資料…為了剔除這些資料，我們將用到where字句將笛卡爾積表篩選成有用的資料表

一般地，我們有幾種連線：

  • 內連線

    • 等值連線【使用=號把條件篩選出來】
    • 非等值連線【使用between and等手段把條件篩選】
  • 外連線
  • 自連線

這裡寫圖片描述

這裡寫圖片描述

那現在問題來了，在Oracle中有的功能我們可以使用多表查詢來完成，有的時候我們又可以使用子查詢來完成，那麼我們一般選擇哪一個呢？？？？

我們看下圖來比較一下他們的優劣：

這裡寫圖片描述

對於索引就是一個以空間換時間的概念..在資料量很大的時候，Oracle會為我們的資料建立索引，當掃描資料的時候，就可以根據索引來直接獲取值….索引的演算法也有幾種【二叉樹、稀疏索引、點陣圖索引….等等】

這裡寫圖片描述

綜上所述：在Oracle中使用多表查詢效能可能比子查詢好一些

Oracle分頁

在講解JDBC的時候，我們就已經講過Oracle與Mysql的分頁問題了….詳情可以看我的博文：http://blog.csdn.net/hon_3y/article/details/53790092

我們在這裡還是加深一下印象：

Oracle中的分頁是依靠著rownum這個偽列來實現的，由於rownum只能使用的是<=或者<來獲取資料。。。因為rownum的值可能會經常變【加入一條資料，那麼rownum就+1，講道理rownum可以是無窮大的，因此不能使用>來進行操作】….

那麼Oracle分頁的思路是這樣子的：

  • 先在子查詢中獲取前n條記錄
  • 由於返回的是多行多列，因此我們可以看做成一張表
  • 那麼將查詢出來的資料放在from字句的後邊
  • 外套的查詢可以通過where字句來對子查詢出來的資料進行過濾
  • 那麼我們就可以查詢出想要的資料了…

公式：

  • Mysql從(currentPage-1)*lineSize開始取資料，取lineSize條資料
  • Oracle先獲取currentPagelineSize條資料，從(currentPage-1)lineSize開始取資料

小面試題

筆試題：有【1000億】條會員記錄，如何用最高效的方式將薪水欄位清零，其它欄位內容不變？

第一：從emp表中刪除sal欄位

  • alter table emp
  • drop column sal;

第二：向emp表中新增sal欄位，且內容預設0

  • alter table emp
  • add sal number(6) default 0;

操作表細節

進入回收站
drop table users;

查詢回收站中的物件
show recyclebin;

閃回，即將回收站還原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to  新表名;

徹底刪除users表
drop table users purge;

清空回收站
purge recyclebin;

為emp表增加image列，alter table 表名 add 列名 型別(寬度) 
alter table emp
add image blob;

修改ename列的長度為20個位元組，alter table 表名 modify 列名 型別(寬度) 
alter table emp
modify ename varchar2(20);

刪除image列，alter table 表名 drop column 列名
alter table emp
drop column image;

重名列名ename為username，alter table 表名 rename column 原列名 to 新列名
alter table emp
rename column ename to username;

將emp表重新命名emps，rename 原表名 to 新表名
rename emp to emps;

  • number(5)：

    • 最多5位數字

  • number(6,2)：

    • 其中2表示最多顯示2位小數，採用四捨五入，不足位數補0，同時要設定col … for …
    • 其中6表示小數+整數不多於6位
    • 其中整數位數不得多於4位，可以等於4位

  • varchar2(8)：

    • 8表示位元組

值得注意的是：修改表的時候，是不能回滾的！

Oracle中的級聯操作：

  • 【on delete cascade】級聯刪除
  • 【on delete set null】將外來鍵一方設定為null

如果文章有錯的地方歡迎指正，大家互相交流。習慣在微信看技術文章，想要獲取更多的Java資源的同學，可以關注微信公眾號:Java3y

相關文章