Oracle資料庫之SQL基礎（二）

標籤：

一、約束? 1、約束概述

約束作用：

　　（1）定義規則

　　（2）確保完整性:包括資料的精確性、可靠性。以確保資料不會出錯，或者盡量減少出錯。

約束的類型：

　　（1）非空約束

　　（2）主鍵約束

　　（3）外鍵約束（右外鍵約束）（左外鍵約束）

　　（4）唯一約束

　　（5）檢查約束

? 2、非空約束

（1）在建立表時設定非空約束
　　文法：CREATE TABLE table_name(column_name datatype NOT NULL,...);
（2）在修改表時添加非空約束
　　文法：ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
　　註：添加非空約束的欄位不能有null資料存在。
（3）在修改表時去除非空約束
　　文法：ALTER TABLE table_name MODIFY column_name datatype NULL;

在修改表的時候添加非空約束時，先把原有資料刪除：
　　delete from userinfo；
然後再修改表：
　　alter table userinfo modify username varchar2（20） not null；

? 3、主鍵約束

作用：確保表中的每一行資料的唯一性。
設定了主鍵約束的欄位，這個欄位必須是非空且值是唯一的；
一個表中只能設定一個主鍵約束；
一個主鍵約束可以有多個欄位組成(這個被稱為聯合主鍵或複合主鍵)。

 在建立表時設定主鍵約束：

　　（1）在建立表時設定主鍵約束(列級)
　　　　文法：CREATE TABLE table_name(column_name datatype PRIMARY KEY,...);

建立了主鍵約束時它會自動加上非空約束。

　　（2）在建立表時設定主鍵約束（表級）
　　　　文法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT pk_name PRIMARY KEY(column_name1,...));

 

在修改表時設定主鍵約束：

　　（1）在修改表時添加主鍵約束
　　　　文法：ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column_name1,...);
　　　　註：如果表中有內容時，準備添加主鍵的欄位裡的資料必須唯一。

 

　　（2）更改約束名稱

　　　　文法：ALTER TABLE table_name RENAME CONSTRAINT old_pk_name TO new_pk_name;

　　（3）禁用主鍵約束

　　　　文法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT pk_name;

　　（4）刪除主鍵約束

　　　　文法：ALTER TABLE table_name DROP CONSTRAINT pk_name;
　　　　　　　或ALTER TABLE table_name DROP PRIMARY KEY [CASCADE]; [CASCADE]為可選項，在級聯操作中使用。

 

? 4、外鍵約束

在建立表時添加外鍵約束：外鍵約束（表示主從表之間的關係）

　　（1）在建立表時設定外鍵約束（列級）
　　　　文法：CREATE TABLE table1(column_name datatype REFERENCES table2(column_name),...); --其中table2為主表，table1為從表

　　　　注：1）設定外鍵約束時，主表欄位必須為該表的主鍵欄位
　　　　　　2）主從表中相應的欄位必須是同一個資料類型（外鍵約束的欄位和主表的主鍵欄位）
　　　　　　3）從表中外鍵欄位的值必須來自主表中相應欄位的值，或者為null值

　　（2）在建立表時設定外鍵約束（表級）
　　　　文法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table_name2(column_name)[ON DELETE CASCADE]);
　　　　注：1）table_name2為主表名；
　　　　　　2）[ON DELETE CASCADE]表示串聯刪除的意思；
　　　　　　3）約束的名字也是唯一的。

 

在修改表時添加外鍵約束:

　　文法：ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY(column_name) REFERENCES table_name2(column_name)[ON DELETE CASCADE];

　　注：1）table_name2為主表名；
　　　　2）[ON DELETE CASCADE]表示串聯刪除的意思；
　　　　3）約束的名字也是唯一的。

? 5、刪除主鍵約束

　　（1）查看約束
　　　　文法：select constrain_name,constraint_type,status from user_constraints where table_name=‘XXXX‘;
　　　　- status P代表主鍵 R代表外鍵

　　

　　（2）禁用外鍵約束
　　　　文法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT fk_name;　　

　　（3）刪除外鍵約束
　　　　文法：ALTER TABLE table_name DROP CONSTRAINT pk_name;

? 6、唯一約束

　　作用：保證設定唯一約束的欄位值的唯一性

　　唯一約束與主鍵約束的區別：
　　　（1）主鍵約束的欄位為非空的，而唯一約束只允許有一個值為null
　　　（2）一張表只能有一個主鍵約束，而唯一約束可以有多個

　　在建立表時設定唯一約束：

　　　　（1）在建立表時設定唯一約束（列級）
　　　　　　文法：CREATE TABLE table_name(column_name datatype UNIQUE,...);

　　

　　　　（2）在建立表時設定唯一約束（表級）
　　　　　　文法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT u_name UNIQUE(column_name));
　　　　　　註：1）唯一約束的名稱（u_name）必須唯一；
　　　　　　　　2）如果想設定多個唯一限制欄位，需要將子句（CONSTRAINT u_name UNIQUE(column_name)）重複書寫即可。

　　　　

　　在修改表時添加唯一約束：

　　　　文法：ALTER TABLE table_name ADD CONSTRAINT u_name UNIQUE(column_name);

　　

　　禁用唯一約束：
　　　　文法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT u_name;

　　刪除唯一約束：
　　　　文法：ALTER TABLE table_name DROP CONSTRAINT u_name;

? 7、檢查約束

　　作用：讓表中的值更具有實際意義。
　　註：檢查約束在一張表中也是可以有多個的。

在建立表時設定檢查約束：
　　（1）在建立表時設定檢查約束（列級）
　　　　文法：CREATE TABLE table_name(column_name datatype CHECK(expressions),...);
　　　　註：expressions為條件運算式

　　

　　（2）在建立表時設定檢查約束（表級）
　　　　文法：CREATE TABLE table_name(column_name datatype,...,CONSTRAINT c_name CHECK(expressions));

 　　

在修改表時添加檢查約束：

　　文法：ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(expressions);

　　

禁用檢查約束：

　　文法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT ck_name;

刪除檢查約束：

　　文法：ALTER TABLE table_name DROP CONSTRAINT ck_name;

? 8、約束小結

• 非空約束 NOT NUL （禁止插入欄位為空白）
• 主鍵約束 PRIMARY KEY （每張表只能有一個，可以由多個欄位構成）
• 外鍵約束 FOREIGN KEY （涉及兩張表之間的關係，限制欄位與外表欄位匹配，類型相同，資料必須 IN {外表資料}）
• 唯一約束 UNIQUE （保證資料的唯一性，可以由多欄位構成）
• 檢查約束 CHECK （保證資料值的安全可靠，並允許範圍內）

在建立表時設定約束：

　　只有非空約束是只有列級設定且非空約束是沒有名字的，其他約束即可以列級設定也可以表級設定。

在修改表時添加約束：

　（非空）alter table table_name modify column_name datatype not null;

　　只有非空約束是用修改欄位的方式添加約束的，其他的雷同。

更改約束的名稱：

　　非空約束是沒有名字的，

　　所以其他的約束都可以通過以下文法修改：ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name;可以通過資料字典（user_constraints）來查看。

禁用約束：

　　非空約束不需要禁用，

　　其他約束的禁用文法是：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT constraint_name;可以通過資料字典（user_constraints）來查看。

刪除約束：

　　非空約束的刪除還是通過修改欄位的方式來刪除，即在欄位類型後加個NULL即可，

　　其他的都可以通過文法：ALTER TABLE table_name DROP CONSTRAINT constraint_name;來刪除；

　　主鍵約束還可以通過文法：ALTER TABLE table_name DROP CONSTRAINT PRIMARY KEY;來刪除；

　　可以通過資料字典（user_constraints）來查看。

練習知識點：

　　Oracle中規定一張表中只能有一個主鍵，因此主鍵約束只能有一個。

　　外鍵約束涉及到兩個表，比如部門表dept包括部門編號（deptno），員工表employee中的部門（deptnum）作為外鍵參照部門表中的deptno欄位。

　　唯一約束要求每個欄位不能有重複值，可以有空值，但是空值只能有一個。

二、查詢語句

? 查詢概述

• 查詢基本語句；
• 在SQL*PLUS中設定格式（對查詢結果的格式設定）；
• 查詢表中的所有欄位及指定欄位；
• 給欄位設定別名（針對查詢結果進行的，並沒有更改欄位的名字）；
• 運算子和運算式；
• 在select語句中使用運算子；
• 帶條件的查詢；
• 模糊查詢；
• 範圍查詢；
• 對查詢結果排序；
• case...when語句的使用（條件陳述式）；
• decode函數的使用

? 1、基本查詢語句

　　文法：SELECT [DISTINCT] column_name1,...|* FROM table_name [WHERE conditions];

　　即：Select [Distinct] 列名 .. From 表名 （使用 distinct 不顯示重複資料）

? 2、在SQL*PLUS中設定格式

　　作用：配合select語句使用，設定查詢結果顯示的格式，不會修改資料庫表欄位。

　　（1）設定欄位顯示樣式
　　　　文法：COLUMN column_name HEADING new_name;
　　　　註：COLUMN 可以簡寫成COL

　　

　　（2）設定欄位的格式化樣式
　　　　文法：COLUMN column_name FORMAT dataformat;
　　　　註：如果是字元類型的值，只能設定它顯示的長度。其中a後面跟數字表示字串顯示長度，例：a10 表示顯示10位的長度的字串；數字則用‘9’代表一位，例：999.9 表示整數位為三位，小數位為一位。如果設定的長度超出資料最大長度，則用‘#’顯示。還可以在數字前面加‘$’,例：$999.9 則查詢後顯示的結果會在結果前加上‘$‘符。

　　

　　

　　

　　

　　（3）清除設定格式
　　　　文法：COLUMN column_name CLEAR;

 　　

? 3、查詢表中的所有欄位及指定欄位

　　（1）查詢所有欄位：

　　　　文法：select * from table_name;

　　

設定格式：

　　

　　（2）查詢指定欄位：
　　　　文法：select column1, ... from table_name;

　　

? 4、給欄位設定別名

　　文法：SELECT column_name AS new_name,... FROM table_name;
　　其中：as是可以省略的，用空格來隔開原欄位名和新欄位名即可。
　　注釋：
　　　　1）column(col) col_name heading new_name;//設定的是欄位的查詢結果的名字
　　　　2）column(col) col_name format datatype;//設定的是欄位的查詢結果的類型範圍
　　　　3）column(col) col_name clear; //清除所設定的格

? 5、運算子和運算式

　　（1）運算式 = 運算元 + 運算子
　　（2）oracle中的運算元可以是常量、變數和表當中的欄位
　　（3）oracle中的運算子：

　　　　　　　　算數運算子（+ - * /）；

　　　　　　　　比較子(> < = >= <= <>)；（如5>2結果為true，布爾類型 ）

　　　　　　　　邏輯運算子(and，or， not)

　　　　　　　　

在select語句中使用運算子：

　　（1）使用算數運算子

　　

所有工資結果加200，但並不會影響表中的資料。（表中資料進行算術運算後，資料值並不會正真的被改變，改變需要使用update語句）

　　（2）使用比較子

　　

注釋：不等於用！=或者<>表示

? 6、帶條件的查詢（where語句）

（1）單一條件查詢

（2）多條件的查詢(利用邏輯運算子來串連運算式)

例：查詢員工姓名是aaa，或者工資大於2000的員工資訊

可以注意：運算子是有優先順序的。

• 邏輯運算子的優先順序：按not、and、or的順序依次遞減
• 比較子的優先順序高於邏輯運算子

? 7、模糊查詢

（1）萬用字元（‘_‘,‘%‘）的使用:

　　一個‘_’代表一個字元，一個‘%’代表0到多個任一字元。

（2）使用LIKE查詢：

? 8、範圍查詢

（1）between……and

　　between ～ and ～查詢的區間包含兩個範圍限制值；
　　not between ～and～ 查詢不包含在此範圍的值；

　　範圍查詢，如尋找工資在800到2000的(包括了800和2000)：
　　　　select * from users where salary between 800 and 2000;
　　如果想要不包括800和2000這兩個數，就可以這樣寫：
　　　　select * from users where salary not between 800 and 2000;

（2）in/not in

　　select * from users where username in(‘aaa‘,‘bbb‘);
　　select * from users where username not in(‘aaa‘,‘bbb‘);

? 9、對查詢結果進行排序

文法：SELECT [DISTINCT] column_name1,...|* FROM table_name [WHERE conditions] ORDER BY column_name1 DESC|ASC,...
　　其中，DESC是降序排序，ASC是升序排序，預設情況下是升序排序

? 10、case...when語句

基本文法：([when後面跟的是判斷的條件，then是條件為真是顯示出來的東西])
文法1：CASE column_name WHEN value1 THEN result1, ... [ELSE resultn] END;
文法2：CASE WHEN column_name=value1 THEN result1, ...[ELSE resultn] END;
註：value1、result1需要加單引號,表示字串，當多個when...then...使用時，中間用空格隔開即可，不能用逗號隔開

文法1樣本：

文法二樣本：

 

? 11、decode函數的使用

與前面的case ...when .. then (else) end 類似 

文法：DECODE(column_name,value1,result1,...,defaultvalue);
（ decode與case ...when .. then (else) end 類似，但decode的條件指定為==的意思）
解釋：Select decode（columnname，值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,預設值）From talbename;

 

Oracle資料庫之SQL基礎（二）