Oracle的學習二：表管理(資料類型、建立/修改表、添加/修改/刪除資料、資料查詢)

標籤：des   io   ar   使用   sp   for   strong   on   資料   

1.Oracle表的管理

• 表名和列名的命名規則：
• 必須以字母開頭；
• 長度不能超過30個字元；
• 不能使用oracle的保留字；
• 只能使用如下字元：A-Z, a-z, 0-9, $, # 等。
• Oracle支援的資料類型：
• 字元型

char 定長 最大2000個字元

char(10)----‘小韓‘ 前四個字元放‘小韓‘，後添加6個空格補全。(查詢效率高)

varchar2(20) 變長 最大4000個字元

vachar2(10)----‘小韓‘ oracle分配四個字元，這樣可以節省空間的。

clob(character large object的縮寫) 字元型大對象 最大4G

• 數字型

number ---範圍： 負的10的38次方----10的38次方； ---可以表示整數，也可以表示小數

eg：number(5,2) ---表示一個小數有5位有效數， 2位小數； 範圍：-999.99 -- 999.99

number(5) --- 表示一個五位整數； 範圍： -99999 -- 99999

• 日期類型

date --- 包含年月日和時分秒

timestamp --- oracle9i對date資料類型的擴充，精確到毫秒級的。

• 圖片

blob --- 位元據， 可以存放圖片/聲音 4G

注意：存放圖片/聲音時，一般在資料庫中存放路徑，除非要求安全性特別高。

• 建立表
• create table 表名(列名 列類型，...);

查看錶的結構： desc 表名；

• 修改表
• 添加一個欄位：

sql>alter table 表名 add (列名， 列類型)；

• 修改欄位的長度

sql>alter table student modify (列名， 列類型);

• 修改欄位的類型/名字(不能有資料)

sql>alter table student modify (列名， 列類型);

• 刪除一個欄位(一般不建議此操作)

sql>alter table student drop column 列名;

• 修改表的名字

sql>rename 表名 to 新表名;

• 刪除表

sql>drop table 表名;

• 表查詢(後面再說)
• 插入資料
• 所有欄位都插入

insert into 表名 values(一條記錄);

提示：Oracle中預設的日期格式是：‘DD_MON(eg：5月)-YY/YYYY‘;

更改日期的預設格式：alter session set nls_date_format = ‘yyyy-mm-dd‘;

• 插入部分欄位

insert into 表名(列1，列2，列3，...) values(值1，值2，值3，...)；

• 插入空值

insert into 表名(列1，列2，列3，...) values(值1，null(空值)，值3，...)；

• 更新資料
• 改一個或多個欄位

update student set 列名1 = 實際值1，列名2 = 實際值2，... where 列名 =‘預提供值‘;

• 修改含有null值的資料
• 刪除資料
• delete from 表名； --- 刪除所有記錄，表結構還在，寫日誌，可以恢複，速度慢。

恢複資料 --- a.設定儲存點：savepoint 點名稱； b.復原到儲存點： rollback to 點名稱；

• drop table 表名； --- 刪除表的結構和資料
• delete from 表名 where 列名=‘值‘； --- 刪除一條記錄。
• truncate table 表名; --- 刪除表中的所有記錄，表結構還在，不寫日誌，無法恢複，速度快。

2.Oracle表的基本查詢

• 簡單的查詢語句
• 查看錶結構：

desc 表名;

• 查詢所有列：

select * from 表名;

tips: 1.set timing on/off; ---開啟時間消耗的開關;

2.表的列名大小寫不區分，記錄的值大小寫時區分的。

• 查詢指定列：

select 指定的列名 from 表名;

• 取消重複行：

select distinct 列名1，列名2，... from 表名;

• 使用算數運算式： + - * /
• 使用列的別名(空格 或 as)： 表的別名不用as，用空格

select ename(列名) "姓名(別名)", sal*12 as "年度營收(別名)" from emp(表名);

當使用的別名中包含大小寫、空格、特殊字元時，那麼就需要雙引號("")而不是單引號將其括起來；經常給運算式一個有意義的別名。

• 如何處理null值：

使用nvl函數來處理: nvl(列名，0) --- 若記錄對應的列為null，則列的值為0，否則為列的實際值。

• 如何連接字串(||): 字串用單引號(‘‘)括起來。

eg：select ename || ‘is a ‘ || job from emp;

• 使用where子句的查詢：

eg：日期在1982年1月1日以後：time>‘1-1月-1982‘;sal在2000到2500之間： sal>2000 and sal<2500;

• 查詢條件：

1、比較子： <、 >、 <=、 >=、 <>/!、 =

2、使用邏輯操作符：OR AND NOT

3、其它運算子：

LIKE和NOT LIKE操作符： %：任意0到多個字元； _：表示任意單個字元； NOT LIKE/LIKE ‘匹配字串‘ [ESCAPE ‘換碼字元‘] eg:like ‘%\_A%‘ ESCAPE ‘\‘;

IN()和NOT IN()：確定離散範圍。 --- where 列名 in (列值1，列值2，列值3，...);

BETWEEN a AND b NOT BETWEEN a AND b: 確定連續範圍。

IS NULL 和NOT IS NULL：測試是否為空白

注意事項：

1.在where子句中使用數字值時，既可以使用單引號也可以不使用；使用字串值、日期值時都必須使用單引號。並且字串值時大小寫敏感的，日期值是格式敏感的。

2.日期值的預設格式是DD-MM-YY，否則必須用TO_DATE函數轉換。

• 使用order by語句

order by 列名1 ASC(升序)，列名2 DESC(降序)；

注意：可以對多列進行排序；order by子句必須是最後一個子句；可以使用列的別名、列的位置進行排序；用於排序的列也可以不是查詢的列（有distinct不行）；

eg:select 列名1，列名2 as "別名"，... from 表名 order by "別名" ASC；

• 分頁查詢(三種方式) 速度：法二>法一>法三

法一：rownum分頁: rownum --行頁碼 eg: 記錄：6--10

a.做一個子查詢，並看做是一個內嵌視圖； select * from 表；

b.顯示rownum[oracle分配的]； select a1.*，rownum rn from (select * from 表) a1;

c.顯示行號小於10的記錄；select a1.*，rownum rn from (select * from 表) as a1 where rownum<=10;

d.顯示行號大於6的記錄； select * from (select a1.*，rownumrn rn from (select * from 表) as a1 where rownum<=10) where rn>6;

解釋：1.只查詢部分列資訊時，只需修改最裡層表的指定列資訊即可。

2.排序：只需最裡層表的列資訊排序即可，另外分組等也只需改動最裡層表的列資訊即可。

3.步驟a,b,c裡的rownum、rn等的位置不能變動。

法二：根據ROWID來分：

select * from 表名 where rowid in

(select rid from

(select rownum rn, rid from

(select rowid rid,cid from 表名 order by cid desc)

where rownum<10000)

where rn>9980)

order by desc;

法三：按分析函數來分：

select * from (select t.*, row_number() over(order by cid desc) rk from 表名 t) where rk<10000 and rk>9980

3.Oracle表複雜查詢

• 資料分組
• 分組函數：

max--列或運算式的最大值(資料最大、時間最晚)，<--> min，

avg--列或運算式的平均值， sum--列或運算式的合計值

count--返回記錄行的行數

variance --列或運算式的方差； stddev --列或運算式的標準差

注意：1.當在where中，列與分組函數比較查詢時，可用子查詢解決此問題。

2.分組函數只能出現在查詢列表、having、order by子句中，絕對不能出現在where子句和group by子句中；

3.在挑選清單中同時包含列、運算式和分組函數，那麼這些列和運算式都必須出現在group by子句中，否子就會出錯；

4.如果在select語句中同時包含有group by, having, order by, 那麼他們的順序是group by, having, order by；

5.除count(*)外，其它分組函數，包括count(列名)都會忽略列為NULL的列；

6.函數(distinct/all 列名)

• group by 和 having

group by --- 用於對查詢的結果分組統計；

分組方法：可以對一列或多列進行分組； 使用rollup和cube產生橫向和縱向的統計結果。

having子句 --- 用於限制分組顯示結果，組篩選--只顯示滿足條件的組；

1.having子句必須和group by子句一起使用，但group by子句可以自己使用。

eg：select ..... from 表名 where 條件 group by 列名1,列名2，...having 組篩選；

• 串連查詢

多表查詢---基於兩個或兩個以上的表或視圖的查詢。笛卡爾集規定：多表查詢的條件至少是表的個數-1(避免出現笛卡爾串連)；

• 多個不同表的查詢

select a表的列1，...，b表的列1，... from a表，b表，... where a.列=b.列 AND 其它條件等 order by 列

• 自串連 ---- 在同一張表的串連查詢。

---- 做法：將一個表賦予兩個不同的別名，其它的和多個不同表的查詢一致；

• 集合查詢

為合并多個select語句的結果，可以使用集合操作符union、union all、intersect、minus。但注意：不同結果集的列個數和對應資料類型匹配(長度可不同); 不同結果集的列的名稱可不同，採用第一個結果集的列名稱； 只能有一個order by子句；

• union

該操作符用於取得兩個結果集的並集，使用時，會自動去掉結果集中的重複行。select ...from .. union select ... order by 列名；

• union all

該操作符用於取得兩個結果集的並集，使用時，不會去掉結果集中的重複行，並且不會排序。 用法類似。

• intersect

該操作符用於取得兩個結果集的交集。 用法類似。

• minus

使用該操作符，取得兩個結果集的差集，它只會顯示存在於第一個集合但是不存在於第二個集合中的結果集。 用法類似。

• 子查詢 --- 指嵌入在其它sql語句中的select語句，也叫巢狀查詢。
• 單列子查詢：

--- 單行子查詢：只返回一行資料的子查詢語句；

①可以放在Select語句的where子句、having語句、from子句中；

②運算子： =、 >、 <、 >=、 <=、 <>

--- 多行子查詢：返回多行資料的子查詢；

①運算子：IN、NOT IN、EXISTS、NOT EXISTS、ANY和ALL(ANY和ALL必須和比較子結合使用)

②in 和 exists區別：in 是把外表和內表作hash join，而exists是對外表作loop，每次loop再對內表進行查詢。如果查詢的兩個表大小相當，那麼用in和exists差別不大。 如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in；

③not in 和not exists 的區別：如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無論哪個表大，用not exists都比not in要快。盡量不要使用not in子句。使用minus 子句都比not in 子句快，雖然使用minus子句要進行兩次查詢：

④在多行子查詢中使用all操作符； eg：sal>all(select sal ...)-- sal比右邊的所有的都大 用max也可以

⑤在多行子查詢中使用any操作符： eg：sal>any(select sal ...)-- sal中存在比右邊的大 用min也可以

• 多列子查詢：

---指查詢返回多個列資料的子查詢語句；可以成對比較，也可以非成對比較。

單行：select * from 表名 where (列1，列2，...)=（select 列1，列2，... from 表名）；

多行：用IN

tips：資料庫的執行是從右至左。

• 相互關聯的子查詢：引用了父查詢中某些表或列的子查詢；
• 標量子查詢：只返回單行單列的資料。
• 在from子句中使用子查詢

邏輯---a.查詢出各個部門的平均工資和部門號； 表 a2

b.把上一步的查詢結果看做是一張子表(內嵌視圖)；（select ... from ...) a1 --不能用as

c.用上述兩張表a2，a1 進行多表查詢

總結：擋在from子句中使用子查詢時，該子查詢會被作為一個視圖來對待，因此也叫內嵌視圖，當在from子句中使用子查詢時，必修給予子查詢指定別名(不能用as指定別名)。

• 在DDL、DML語句中使用子查詢
• 在DDL語句中使用子查詢

在create table語句中使用子查詢：

create table 表名(列名1,列名2,...) as select 列名1,列名2,... from 表名 where ...；

create table 表名 as select * from 表名 where ...;

在create view中使用子查詢：

create or replace view 視圖名 as select ...;

• 在DML語句中使用子查詢

在update中使用子查詢：

update 表名 set (...)=(select ... from ...) where 列名=(select ... from ...);

在delete中使用子查詢：

delete from 表名 where 列名=/in (select ... from ...where...);

在insert中使用子查詢：

插入全部列：insert into 表名 select .....

插入部分列：insert into 表名(部分列) select ...; 部分列中一定要有主鍵列和NOT NULL列

4.建立新的資料庫

• 法一：使用DataBase Configuration Assistant建立/刪除/設定資料庫
• 法二：可以手工步驟直接建立

Oracle的學習二：表管理(資料類型、建立/修改表、添加/修改/刪除資料、資料查詢)