Oracle資料庫入門之查詢基礎

　　簡單的表操作

　　指令：建立簡單表：createtable student(name varchar2(20), age number(3) );

　　插入新記錄：insert into studentvalues('stone',24);

　　查看錶結構：desc student;

　　查詢記錄：select * from student;

　　刪除表：drop table student;

　　刪除表中的資料：delete from studentwhere 條件;

　　說明：SQLPlusWorksheet中用“--”作為單行注釋標記。varchar2(20)標明可變長度的字串，上限為20

　　number(3)標明age為不包含小數點的3位的整數。如果只定義成number，則系統預設為10位的整數

　　單引號引起來的字元是SQL語言中字串常量的表示方式。SQL語言中的關鍵字大小寫是不敏感的

　　資料庫開發人員喜歡使用大寫關鍵字。應用程式開發人員多數使用小寫，以實現良好的可讀性

　　插入記錄後，其實並沒有在資料庫中立即永久生效，這時可以使用commit;提交資料的更新操作

　　簡單的Select語句

　　文法格式：SELECT *|{<欄位名>,...} FROM <表名>;--欄位間用逗號隔開。在逗號後面添加空格可以增強可讀性

　　例如select * from emp;或select empno, ename, sal from emp;

　　顯示效果：Sql*plus的預設顯示：Date和character型資料靠左對齊，Numeric型資料靠右對齊，欄位標題預設顯示為大寫

　　iSql*plus的預設顯示：欄位標題預設顯示為大寫，欄位標題預設置中對齊

　　使用算術運算式

　　概述：在Select語句中，對NUMBER型資料可以使用算術運算子(+、-、*、/)建立運算式

　　舉例：select empno, ename, sal, sal*12 from emp;--查詢emp中員工的編號姓名工資和年薪

　　select empno, ename, sal, sal*12+1000 from emp;

　　select empno, ename, sql, sal*(12+1000) from emp;--可以使用括弧更改計算順序

　　串連運算子

　　概述：即“||”可以把列與字元或其它運算式串連在一起，得到一個新的字串，實現“合成”列的功能

　　舉例：select ename || ' is a ' || job from emp;

　　select empno, ename ||'''s annual salary is'|| sal*12 from emp;

　　說明：用一個單引號代表字串常量的分隔標記。用兩個單引號代表字串常量中出現的一個單引號字元

　　在查詢的時候，也不需要轉義符，直接用兩個單引號代表字串常量中出現的一個單引號即可

　　使用欄位別名

　　概述：重新命名查詢結果中的欄位，以增強可讀性。如果別名中使用特殊字元或強制輸出大小寫時需使用雙引號

　　格式：SELECT <欄位名>|<運算式> [[AS]<欄位別名>],... FROM<表名>;

　　舉例：select empno, ename "Ename", sal*12 "年 薪" from emp;

　　select empno as 員工編號, ename 員工姓名, sal*12 "年薪" from emp;

　　說明：as可以不加。別名可以用雙引號引起來。如果別名中不包含特殊字元，雙引號可以省略

　　如果別名中包含了空格，如"年 薪"，就必須加雙引號，否則將被解析成兩個成份，導致文法上出問題

　　如果Ename不加雙引號，那麼執行後會顯示為ENAME。只有給它加上雙引號之後，才會遵重大小寫來顯示

　　空值

　　概述：空值是無效的，未指定的，未知的或不可預知的值。空值不等同於0或空格

　　舉例：select empno, ename, sal, comm from emp;--執行後某些記錄的comm屬性會顯示一片空白，即空值

　　insert into student(age) values(24);--插入資料時，如果沒有指定name值，那麼預設的name就是空值

　　補充：算術運算式中如果出現空值，則整個運算式結果為空白

　　串連運算式中出現的空值被當作一個空的(長度為零的)字串處理

　　如select ename, sal, comm, ename || ' - ' || comm, sal+comm from emp;

　　在算術運算式中如果涉及到空值的話，可以使用相應的函數對可能存在空值的欄位進行預設值的設定

　　經過判斷之後，如果它是空值，則返回0，如果不是空值，則返回它真正的值，然後再進行算術運算

　　去除重複行

　　概述：預設情況下，查詢結果中顯示所有合格記錄行，包括重複行。可以使用DISTINCT可以清除重複行

　　DISTINCT的作用範圍是後面所有欄位的組合，即後面的多個欄位的組合不出現重複就可以了

　　舉例：select deptno from emp;--本意是查詢部門編號的種類，顯示的卻是所有的編號，包括重複編號

　　select distinct deptno from emp;--清除查詢結果中重複的部門編號

　　select distinct deptno, job from emp;--只要deptno和job組合後的結果不重複，那麼就都顯示輸出

　　說明：通常把極為簡單的資料處理，包括資料的查詢、組合、過濾或轉換類型等操作，均交給資料庫進行處理

　　比如分組統計、求和、求平均值、過濾空值、過濾重複值、資料開頭和結尾的空格過濾、字串轉日期型等等

　　而對於複雜些的商務邏輯，或者與商業邏輯相關的內容，建議在應用程式中處理。這樣也就實現了鬆散的耦合

　　查詢結果排序

　　概述：查詢結果預設按照記錄的插入順序進行排列。可以使用ORDER BY子句對查詢結果進行排序

　　排序方式包括升序(ASC，預設)和降序(DESC)兩種。也可以使用欄位別名排序或按多欄位排序

　　舉例：select empno, ename, sal from emp order by sal;--按sal進行升序排列，ASC可以省略不寫

　　select empno, ename, sal from emp order by sal desc;--按sal進行降序排列

　　select empno, ename, sal*12 annsal from emp order by annsal;

　　select deptno, empno, ename, sal from emp order by deptno, sal;

　　說明：多欄位排序時，先按照deptno排序，如果在得到的排列結果中存在重複的deptno記錄的話

　　就將重複的deptno記錄再按照sal排序。至於具體的排序方式，則需要對兩個欄位進行單獨指定

　　如deptno,sal表示二者均按升序排列。deptno,sal desc表示deptno按升序排列，sal按降序排列

　　如deptno desc,sal表示deptno按降序排列，sal按升序排列。依此類推

　　這種邏輯適合由資料庫在查詢階段進行處理。在應用程式中通過代碼來實現的話，就會困難一些

       條件查詢

　　說明：在查詢語句中或查詢條件中使用字串或日期的時候，字串和日期值要用單引號括起來

　　字串大小寫敏感。日期值格式敏感，預設的日期格式是DD-MON-RR

　　注意：凡涉及到字串內容的比較，大小寫都是敏感的

　　格式：SELECT *|{[DISTINCT]<欄位名>|<運算式>[<別名>],...} FROM<表名> [WHERE <查詢條件>];

　　舉例：select * from emp where ename='SMITH';--這裡所要查詢的SMITH嚴格區分大小寫

　　select * from emp where hiredate='02-4月-81';--系統會自動將符合約定格式的字串轉換成Date型

　　select sysdata from dual;--以預設的日期格式擷取當前的系統時間

　　說明：表dual中只有一行一列，它本身並不儲存系統時間，sysdate永遠代表當前的系統時間

　　通常在進行簡單的查詢或運算的時候，dual表起一個媒介的作用，使得形式上看起來像是查詢

　　dual的好處在於只有一行記錄，它一般用在練習時測試某些指令。一般不在真實的開發使用它

　　比如select 3+2 from dual;返回5。又如select 3+2 from emp;則會返回很多行的5

　　它會對emp中每一行記錄都查詢，但查的不是真正儲存的內容，而是逐行的計算一遍3+2

　　再把得到的5作為一個唯一的結果，也是唯一的一個欄位返回來，所以結果就是10多行的5

　　對於Oracle資料庫，即使是想讓它做一個簡單的常量運算式的運算，也要採用問答的形式

　　即便此時所有的資料都在使用者的手中，那也要裝作是查詢，給它一個指令，令其返回結果

　　關係運算子

　　符號：BETWEEN...AND...：界於兩值之間，包括邊界，並且必須把小的數值放在前面

　　IN(set)：出現在集合中。判斷是否存在某欄位等於集合中的值的記錄

　　LIKE：模糊查詢，也叫通配查詢

　　IS NULL：判斷空值

　　樣本：select * from emp where sal between 1600 and 2900;--查詢工資界於1600和2900之間的員工的資訊

　　select * from emp where ename in('SMITH','KING','King');--返回姓名為set中的元素的值的記錄

　　select * from emp where comm is null;--返回所有comm欄位的值為空白的記錄

　　select * from emp where comm is not null;--返回非空的。注意0和空是不同的

　　注意：凡涉及到字串內容的比較，大小寫都是敏感的。即in()中的值需要與ename值完全符合方可查詢出記錄

　　LIKE：%代表零或多個字元。_代表一個字元。可以使用ESCAPE標識特殊符號

　　select * from emp where ename like 'S%';--查詢以S開頭的姓名的記錄。這裡S仍然是大小寫敏感的

　　select * from emp where ename like '_A%';--查詢第二個字母是A的姓名的記錄

　　select * from emp where ename like '%_%' escape '';--返回姓名存在底線的記錄

　　這裡的escape標明“”起到轉義符的作用。在真實的開發中，這種轉義符用的並不多

　　Oracle中的轉義符可以隨便定義，所以例句中的“”也可以換成其它的符號，如“K”

　　說明：用一個單引號代表字串常量的分隔標記。用兩個單引號代表字串常量中出現的一個單引號字元

　　在模糊查詢的時候，也不需要轉義符，直接用兩個單引號代表字串常量中出現的一個單引號即可

　　邏輯運算子

　　概述：優先順序由高至低：NOT→AND→OR

　　舉例：select * from emp where deptno=20 and sal>2000;--返回部門編號為20而且工資大於2000的記錄

　　select * from emp where deptno=20 or sal>2000;--返回部門編號為20的人或者工資大於2000的記錄

　　select * from emp where deptno not in(20,30,40);--返回部門編號沒有出現在該集合中的人的資訊