Oracle基礎實訓

標籤：

一、Oracle基礎SQL練習

班級表：班級編號（主鍵、識別欄位）、班級名稱、班主任、開班日期
老師表：老師編號（主鍵、識別欄位） 、姓名、性別（預設值‘男’）、社會安全號碼（必須是18位元字）、聯絡電話
學生表：學號（主鍵、固定五位元字） 、班級編號、姓名、性別、出生日期、聯絡電話、家庭地址
成績表：編號（主鍵、識別欄位） 、學號、科目名稱、分數

1、表格建立 create table();
文法
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr],
[…]
);
schema表示對象所有者的姓名，table表示表的名稱，DEFAULT表示預設值，column表示列的名稱，datatype表示資料類型及長度

2、插入資料測試
3、查詢資料測試
4、約束：a.非空約束（NOT NULL）
b.主鍵約束（PRIMARY KEY）：不可為空白
c.唯一性限制式（UNIQUE）：可以插入空值
註：增加約束的兩種方式：
方法一：在建表語句中；
方法二：
alter table t_country add constraint PK_countrycode primary key(countrycode)；

5、刪除表：DROP TABLE

6、插入資料：
a.全列插入：
b.部分列插入：
c.插入來自其他表的記錄

7、查詢資料
a.全列查詢：
b.部分列查詢：
c.條件查詢：
註：為列顯示不同名稱

8、刪除表內的資料記錄(與刪除表的區別)
逐行刪除Delete from :
直接刪除Truncate:

9、更新資料update：

10、資料類型分類：

a.文本資料類型
固定長度類型（CHAR/NCHAR)
可變長度類型（VARCHAR2/NVARCHAR2)
註：變長字串，與CHAR類型不同，它不會使用空格填充至最大長度。VARCHAR2最多可以儲存4,000位元組的資訊。

b.日期和時間
Date
Timestamp：時間可以是用小數表示的秒

c.數字
Number(p, s) --p是精度，指總的數字數 -s指小數位元，小數點右邊的數字數

d.LOB類型(1~4GB，儲存非結構化的資訊，如聲音剪輯、視頻檔案)
CLOB：儲存字元資料，如非結構化的XML文檔
BLOB：形、視訊剪輯、音效檔
BFILE：在資料庫之外的作業系統檔案中儲存二進位檔案

11、ALTER TABLE命令
a.使用者要添加新的列：
ALTER TABLE <table name> ADD(column definition…);
b.修改資料類型的寬度或資料類型本身：
ALTER TABLE <table name> MODIFY (column definition…);
eg. ALTER TABLE vendor_master MODIFY (venname VARCHAR2(25));
註：表的列不為空白時，不能減小其長度
c.新增或刪除完整性條件約束:
alter table t_country add constraint PK_countrycode primary key(countrycode)；
d.物理刪除該列列名及其對應的資料:
ALTER TABLE table_name DROP COLUMN column_name;

12、DESC查看錶結構命令:DESC table_name

13、交易處理命令
COMMIT
儲存點:SAVEPOINT savepoint_id;
ROLLBACK :ROLLBACK;ROLLBACK TO SAVEPOINT save_pt;

14、建立現有表的副本 create table as....

15、外鍵(子表的外鍵參考父表的主鍵)
插入子表資料：必須先插入父表相關記錄；
刪除父表資料：必須先刪除子表相關記錄；

練習：加入外鍵，並嘗試向子表插入父表沒有的資料，刪除父表中子表有的記錄：
CREATE TABLE T_BuyerInfo
(
buyerNoint primary key auto_increment,
chnNamevarchar(50),
countryCodevarchar(10) ,
buyerAddrvarchar(300),
buildDatedate,
yearSaledouble,
CONSTRAINT t_buyer_cc_fk FOREIGN KEY(countryCode)
REFERENCES t_country(countryCode)
);

16、關聯查詢
inner join(等值串連)只返回兩個表中連接欄位相等的行
left join(左聯結)返回包括左表中的所有記錄和右表中連接欄位相等的記錄
right join(右聯結)返回包括右表中的所有記錄和左表中連接欄位相等的記錄

13、運算子：=，>，<，>=，<=，，<>，!=，BETWEEN 和 NOT BETWEEN

14、邏輯運算式
?And : 1 AND 1 =1 ; 1 AND 0 = 0; 0 AND 0 = 0;
?Or : 1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;
?Not: select * from T_BuyerInfo where not yearsale>900000

15、萬用字元
一個字元: ‘_’
任意長度的字串: %

16、查詢細節
排序查詢: desc(降序) asc
使用AS來命名列：
使用常量列:
限制固定行數:rownum
Between
is null
in
rowid

17、函數
a.字串函數：
Length(string):求字串的長度
lower(String)：轉小寫
upper(String)：轉大寫
REPLACE(str ,from_str ,to_str )

b.日期函數
Months_between(d1,d2),: 查出兩個日期之間的月份數;
Last_day(d),d表示日期:返回月末最後一天相應的日期;
sysdate:

c. 數學函數
ABS(x) 返回x的絕對值
Power(n1,n2):求n1的n2次方
Round(n1,n2):返回n1小數點為n2位的數,四捨五入法

d. 彙總函式(常用於GROUP BY從句的SELECT查詢中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的個數
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和

e.轉換函式
To_char(d|n,fmt):將日期或數字按指定格式轉換為字串
To_char(sysdate,’yyyy”-”mm”-”dd’)
To_char(23457,’$99,999.00’)
rr:返回指定的日期的世紀年份值
To_date(s,[fmt]):
將char或varchar資料類型轉換為date資料類型
To_date(‘2-11-2004‘,‘month-dd-yyyy‘)
To_number(s):將包含數位字串轉換為可以執行算術操作的number類型

f.其他：
select case ‘1‘ when ‘0‘ then ‘男‘ when ‘1‘ then ‘女‘ end case from dual;
select decode(gender,‘0‘,‘男‘,‘1‘,‘女‘) from dual

18、序列：產生唯一、連續的整數，用於產生主索引值
a.nextval:第一次使用nextval時,將返回該序列的初始值
b.currval:返回序列的當前值,即最後一次引用nextval時返回的值

CREATE SEQUENCE <sequencename>
INCREMENT BY n
START WITH n
[MAXVALUE n] [MINVALUE n]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];
註：INCREMENT BY n:指定序列數字之間的整數間隔
START WITH n:指定要產生的第一個序號
MINVALUE n:指定序列的最小值
MAXVALUE n:指定序列可以產生的最大值
CYCLE:指定序列即使達到了最大值，還應繼續產生值,通常是迴圈從頭開始產生序列值
CACHE:允許更快地產生序號，Oracle分配序號，並將其儲存在記憶體中以便更快地訪問

c.刪除序列
文法：
DROP SEQUENCE <sequencename>;
樣本
DROP SEQUENCE member_seq;
查看序列：
select * from seq;

19、視圖：經過定製的表示方式，用來顯示來自一個或多個表的資料，也稱為“虛擬表”
CREATE [OR REPLACE] VIEW viewname[(alias,alias,…)]
AS subquery
WITH CHECK OPTION [CONSTRAINT constraintname]
WITH READ ONLY [CONSTRAINT constraintname]

20、索引（加快 SQL 陳述式的執行，謹慎，需具體情況具體分析）
a.唯一索引:確保在定義索引的列中沒有重複的值
CREATE UNIQUE INDEX 索引名 ON 表名（欄位名）;
b.複合式索引:在表的多個列上建立的索引,對於在 WHERE 子句中包含多個列的查詢，可以提高資料訪問速度
CREATE INDEX 索引名 ON 表名(列名1,列名2);
c.分區索引:索引儲存在不同的分區中並對應不同的資料表空間，與表分區一樣
CREATE INDEX indexname ON tablename(c1,c2,…) [GLOBAL/LOCAL]
PARTITION BY RANGE(c2)
PARTITION VALUES LESS THAN(v1) TABLESPACE tb1,
PARTITION VALUES LESS THAN(v2) TABLESPACE tb2;

Oracle基礎實訓