【訓練】sql訓練、建立和管理表、約束、檢視及其他資料庫物件
第1章 多表查詢
1.1. 等連線
透過兩個表具有相同意義的列,建立連線條件。查詢結果只顯示兩個列中的值是等值條件的行資料。表中同名列被選擇時必須新增表名字首進行修飾 否則無法確定這一列是屬於哪個表
HR@ENMOEDU> conn hr/hr
Connected.
HR@ENMOEDU>SELECT count(*)
FROM departments d, employees t
WHERE d.department_id = t.department_id;
COUNT(*)
----------
106
HR@ENMOEDU> --注：下面sql語句中 INNER 是可以省略不寫的。
HR@ENMOEDU>SELECT COUNT(*)
FROM departments d
INNER JOIN employees t
ON d.department_id = t.department_id;
COUNT(*)
----------
106
HR@ENMOEDU>SELECT COUNT(*)
FROM departments d
JOIN employees t
USING (department_id);
COUNT(*)
----------
106
HR@ENMOEDU>
表名字首（因為兩張表都存在欄位department_id，故報此錯誤）
HR@ENMOEDU>select department_id, employee_id, first_name, department_name
from employees d, departments t
where d.department_id = t.department_id;
select department_id, employee_id, first_name, department_name
*
ERROR at line 1:
ORA-00918: column ambiguously defined
HR@ENMOEDU>select d.department_id, employee_id, first_name, department_name
from employees d, departments t
where d.department_id = t.department_id;
DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME DEPARTMENT_NAME
----------------------- -------------------- ------------------------------
10 200 Jennifer Administration
20 201 Michael Marketing
20 202 Pat Marketing
30 114 Den Purchasing
30 115 Alexander Purchasing
30 116 Shelli Purchasing
30 117 Sigal Purchasing
30 118 Guy Purchasing
30 119 Karen Purchasing
40 203 Susan Human Resources
50 120 Matthew Shipping
HR@ENMOEDU>
請問：下面兩個sql的結果是否相同？為什麼？相同，and是並行滿足，而where是過濾只要滿足這一個條件。
SQL1:
SELECT *
FROM departments d
JOIN employees t
ON d.department_id= t.department_id
AND d.department_name = 'SALES';
SQL2:
SELECT *
FROM departments d
JOIN employees t
ON d.department_id = t.department_id
WHERE d.department_name = 'SALES';
1.2. 自然連線
自然連線只發生在兩個表中有相同名字和資料型別的列上，如果單純的列名相同而型別不同，那麼會報語法錯誤。
HR@ENMOEDU>desc departments
Name Null? Type
------------------------------ -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
HR@ENMOEDU>desc employees
Name Null? Type
--------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@ENMOEDU> SELECT d.department_name, d.location_id, t.first_name
FROM departments d NATURAL JOIN employees t;
DEPARTMENT_NAME LOCATION_ID FIRST_NAME
------------------------------ ----------- --------------------
Executive 1700 Neena
Executive 1700 Lex
IT 1400 Bruce
IT 1400 David
IT 1400 Valli
IT 1400 Diana
Finance 1700 Daniel
Finance 1700 John
Finance 1700 Ismael
Finance 1700 Jose Manuel
Finance 1700 Luis
Purchasing 1700 Alexander
Purchasing 1700 Shelli
Purchasing 1700 Sigal
Purchasing 1700 Guy
Purchasing 1700 Karen
Shipping 1500 Laura
Shipping 1500 Mozhe
HR@ENMOEDU>
1.3. using子句
1) using子句的用法：
HR@ENMOEDU>select d.department_name,d.location_id,t.first_name
from departments d join employees t using (department_id);
DEPARTMENT_NAME LOCATION_ID FIRST_NAME
------------------------------ ----------- --------------------
Administration 1700 Jennifer
Marketing 1800 Pat
Marketing 1800 Michael
Purchasing 1700 Sigal
Purchasing 1700 Karen
Purchasing 1700 Shelli
Purchasing 1700 Den
Purchasing 1700 Alexander
Purchasing 1700 Guy
Human Resources 2400 Susan
Shipping 1500 Kevin
Shipping 1500 Jean
Shipping 1500 Adam
Shipping 1500 Timothy
Shipping 1500 Ki
Shipping 1500 Girard
HR@ENMOEDU>
2) using子句的注意事項
HR@ENMOEDU> select d.department_name,d.location_id,t.first_name
from departments d join employees t
using (department_id)
where department_id=10;
DEPARTMENT_NAME LOCATION_ID FIRST_NAME
------------------------------ ----------- --------------------
Administration 1700 Jennifer
HR@ENMOEDU>select d.department_name,d.location_id,t.first_name
from departments d join employees t
using (department_id) where d.department_id=10;
using (department_id) where d.department_id=10
*
ERROR at line 3:
ORA-25154: column part of USING clause cannot have qualifier
HR@ENMOEDU>
透過上面的例子可以得出，using子句中，相同名字的欄位不允許有任何限定符，否則將報ORA-25154:錯誤，此限制同樣適用於natural join（自然連線）
1.4. 不等連線
A表中的某列資料和B表中一列或多列的關係是非等值關係,大於,小於,不等於,等條件都屬於不等連線的範疇
就是排除完全相等條件以外的 >,<,!=, <=, >=, between and主要在於不同表之間顯示特定範圍的資訊(也可以理解成包含關係)
HR@ENMOEDU>create table job_grades (s_grade varchar2(2),low_salary number,high_salary number);
insert into job_grades (s_grade, low_salary, high_salary) values ('A', 0, 10000);
insert into job_grades (s_grade, low_salary, high_salary) values ('B', 10001, 20000);
insert into job_grades (s_grade, low_salary, high_salary) values ('C', 20001, 30000);
HR@ENMOEDU> commit;
Commit complete.
HR@ENMOEDU>select first_name,salary,s_grade
from employees a,job_grades b
where salary between low_salary and high_salary and b.s_grade='B';
FIRST_NAME SALARY S_
-------------------- ---------- --
Neena 17000 B
Lex 17000 B
Nancy 12008 B
Den 11000 B
John 14000 B
HR@ENMOEDU>
between A and B (大於等於A並且小於等於B)
HR@ENMOEDU> with t as (
select rownum id from dual connect by rownum<=10)
select id from t where id between 2 and 8;
ID
---------
2
3
4
5
6
7
8
7 rows selected.
HR@ENMOEDU>
1.5. 自連線
資料都來自一張表,所以在from子句中需要對錶新增別名,新增表別名後才能合法化的引用表中的列名。本質就是將一張表虛擬成了兩張表
HR@ENMOEDU>SELECT d.last_name || ' works for ' || t.last_name
FROM employees d, employees t
WHERE d.manager_id = t.employee_id;
D.LAST_NAME||'WORKSFOR'||T.LAST_NAME
-------------------------------------------------------------
Smith works for Cambrault
Ozer works for Cambrault
Kumar works for Cambrault
……
中間省略n行
……
Hutton works for Zlotkey
Grant works for Zlotkey
Abel works for Zlotkey
106 rows selected.
HR@ENMOEDU>
1.6. 外連線
選擇出滿足等連線條件的及其以外的記錄。(+)修飾符號用法:放置在選出結果只包含等連線的列後,則另一列的結果就是等值行+非等值行
1.6.1. 左外連線
在等連線的基礎上，還包含左表中所有不符合條件的資料行，並在其中的右表列填寫NULL
1) Oracle自己的寫法（不推薦）：
HR@ENMOEDU>col id for 99
HR@ENMOEDU>col name for a4
HR@ENMOEDU> with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1,t2 where t1.id=t2.id(+);
ID NAME ID NAME
----- -------- ----- --------
2 b 2 bb
3 c 3 cc
1 a
HR@ENMOEDU>
2) SQL99寫法
HR@ENMOEDU> with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1 left outer join t2 on t1.id=t2.id;
ID NAME ID NAME
----- -------- ----- --------
2 b 2 bb
3 c 3 cc
1 a
HR@ENMOEDU>
注：outer 可以省略
請問：下面的sql結果是否相同，思考為什麼？不一樣，and是同時滿足，而where是隻滿足。
SQL1：
with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1 left join t2 on t1.id=t2.id and t2.id=2;
SQL2：
with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1 left join t2 on t1.id=t2.id where t2.id=2;
1.6.2. 右外連線
在等連線的基礎上，還包含右表中所有不符合條件的資料行，並在其中的左表列填寫NULL
1) Oracle自己的寫法（不推薦）：
HR@ENMOEDU> with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1,t2 where t1.id(+)=t2.id;
ID NAME ID NAME
----- -------- ----- --------
2 b 2 bb
3 c 3 cc
4 dd
HR@ENMOEDU>
2) SQL99寫法
HR@ENMOEDU> with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1 right outer join t2 on t1.id=t2.id;
ID NAME ID NAME
----- -------- ----- --------
2 b 2 bb
3 c 3 cc
4 dd
HR@ENMOEDU>
1.6.3. 全外連線
在等連線的基礎上，還包含兩個表中所有不符合條件的資料行，並在其中的左表、和右表列填寫NULL
HR@ENMOEDU>col n1 for a5
HR@ENMOEDU>col n2 for a5
HR@ENMOEDU>with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select nvl(t1.id,t2.id) as id ,t1.name as n1, t2.name as n2
from t1 full join t2 on t1.id=t2.id order by 1 asc;
ID N1 N2
----- ----- -----
1 a
2 b bb
3 c cc
4 dd
HR@ENMOEDU>
1.7. 交叉連線（笛卡爾）
將兩個表的所有行進行組合，連線後的行數為兩個表的乘積數。
往往有人會說要避免笛卡爾積，上學的時候，老師也把它說的很恐怖，其實則不然，我倒是認為如若弄明白它的原理，則可合理利用它。
1) 寫法一：
HR@ENMOEDU> WITH t1 AS (SELECT 'range' AS r FROM dual
UNION ALL SELECT 'list' FROM dual),
t2 AS (SELECT 'range' AS l FROM dual
UNION ALL SELECT 'list' FROM dual),
t3 AS (SELECT 'hash' AS h from dual)
SELECT r,l FROM t1 CROSS JOIN t2
UNION ALL
SELECT l,h FROM t2 CROSS JOIN t3;
R L
----- -----
range range
range list
list range
list list
range hash
list hash
6 rows selected.
HR@ENMOEDU>
2) 寫法二：
HR@ENMOEDU> WITH t1 AS (SELECT 'range' AS r FROM dual
UNION ALL SELECT 'list' FROM dual),
t2 AS (SELECT 'range' AS l FROM dual
UNION ALL SELECT 'list' FROM dual),
t3 AS (SELECT 'hash' AS h from dual)
SELECT r,l FROM t1,t2
UNION ALL
SELECT l,h FROM t2,t3;
R L
----- -----
range range
range list
list range
list list
range hash
list hash
6 rows selected.
HR@ENMOEDU>
第2章 建立和管理表
語法：
CREATE TABLE [schema.]table
(columndatatype [DEFAULT expr][, ...]);
2.1. 建立表
HR@ENMOEDU>CREATE TABLE dept2
(department_id NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.
HR@ENMOEDU>
2.2. 日期資料型別
TIMESTAMP 帶小數秒的日期型
INTERVAL YEAR TO MONTH 作為年和月的時間間隔儲存
INTERVAL DAY TO SECOND 作為天、小時、分和秒的時間間隔儲存（DAY, HOUR, MINUTE, SECOND）
HR@ENMOEDU>create table ha (id number,x INTERVAL YEAR TO MONTH,y INTERVAL DAY TO SECOND );
Table created.
HR@ENMOEDU>
--x欄位插入：5年
--y欄位插入：2天
HR@ENMOEDU>insert into ha values(1,INTERVAL '5' year,INTERVAL '2' day);
1 row created.
HR@ENMOEDU>
--x欄位插入：10個月
--y欄位插入：2小時
HR@ENMOEDU>insert into ha values(2,INTERVAL '10' month,INTERVAL '2' hour);
1 row created.
HR@ENMOEDU>
--x欄位插入：2年零6個月
--y欄位插入：1天零12小時30分鐘1秒
HR@ENMOEDU>insert into ha values(3,INTERVAL '2-6' YEAR TO MONTH,INTERVAL '1 12:30:01' DAY TO SECOND);
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
--利用系統時間加上x、y欄位來驗證這兩個資料型別
HR@ENMOEDU>select id,sysdate,sysdate+x,sysdate+y from ha;
ID SYSDATE SYSDATE+X SYSDATE+Y
--- ------------------- ------------------- -------------------
1 2012-09-09 13:19:32 2017-09-09 13:19:32 2012-09-11 13:19:32
2 2012-09-09 13:19:32 2013-07-09 13:19:32 2012-09-09 15:19:32
3 2012-09-09 13:19:32 2015-03-09 13:19:32 2012-09-11 01:49:33
HR@ENMOEDU>
2.3. 用子查詢語法建立表
HR@ENMOEDU>create table dept3 as select * from departments;
Table created.
HR@ENMOEDU>
HR@ENMOEDU>select count(*) from dept3;
COUNT(*)
----------
27
HR@ENMOEDU>desc dept3;
Name Null? Type
--------------------------------- -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
HR@ENMOEDU>
2.4. 修改表
1) 增加欄位
HR@ENMOEDU>alter table dept3 add (job_id number(5));
Table altered.
HR@ENMOEDU>desc dept3;
Name Null? Type
------------------------------ -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
JOB_ID NUMBER(5)
HR@ENMOEDU>
2) 修改欄位大小
HR@ENMOEDU>alter table dept3 modify (job_id number(10));
Table altered.
HR@ENMOEDU>desc dept3;
Name Null? Type
--------------------------------- -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
JOB_ID NUMBER(10)
HR@ENMOEDU>
3) 刪除欄位
HR@ENMOEDU>alter table dept3 drop column job_id;
Table altered.
HR@ENMOEDU>desc dept3;
Name Null? Type
--------------------------------- -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
HR@ENMOEDU>
2.5. 欄位UNUSED
UNUSED 選項標記一個或多個未使用的列。
特別注意：unused是不可逆的。
那麼為什麼還要這麼做呢，主要還是因為，這個動作是對資料字典進行了刪除，效能非常的好，對資料庫沒什麼壓力。如果資料庫壓力很大，同時又不想要某個欄位了，那麼就可以用這種方式，等壓力小了，再做清除的處理
1) 建立測試表test（兩個欄位x和y）並插入一條記錄
HR@ENMOEDU>create table test(x number(1),y number(2));
Table created.
HR@ENMOEDU>insert into test values(1,2);
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
2) 將欄位y設定為unused
HR@ENMOEDU> alter table test set unused (y);
Table altered.
HR@ENMOEDU>
3) 檢視錶，y欄位已經消失
HR@ENMOEDU>select * from test;
X
----------
1
HR@ENMOEDU>
4) 檢視字典表中，表設定為unused欄位個數，由於unused是不可逆的，所以，此處只顯示欄位個數，不顯示具體的欄位
desc user_unused_col_tabs
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
COUNT NUMBER
HR@ENMOEDU> select * from user_unused_col_tabs;
TABLE_NAME COUNT
------------------------------ ----------
TEST 1
HR@ENMOEDU>
5) 我們可以加入一個欄位y（varchar2型別），檢視test，y欄位建立成功
HR@ENMOEDU>alter table test add(y varchar2(10));
Table altered.
HR@ENMOEDU>select * from test;
X Y
---------- ----------
1
HR@ENMOEDU>
6) 刪除設定為unused的欄位
HR@ENMOEDU>alter table test drop unused COLUMNS;
Table altered.
HR@ORA11GR2>select * from user_unused_col_tabs;
no rows selected
2.6. 刪除表
HR@ENMOEDU>drop table test;
Table dropped.
HR@ENMOEDU>
注：此種方式刪除後，是進入回收站的，如果不進入回收站直接刪除的話，那麼在刪除的時候加上purge子句，詳見《閃回》章節。
2.7. 改變物件的名字
1) 檢視當前使用者下所有表物件
HR@ENMOEDU>select tname from tab where tabtype='TABLE';
TNAME
------------------------------
EMPLOYEES
DEPARTMENTS
DEPT2
......
HR@ENMOEDU>
2) 重新命名DEPT2為DEPT5
HR@ENMOEDU>rename DEPT2 to DEPT5;
Table renamed.
3) 再次檢視，DEPT2已經不見了，已經重新命名為DEPT5
HR@ENMOEDU>select tname from tab where tabtype='TABLE';
TNAME
------------------------------
EMPLOYEES
DEPARTMENTS
DEPT5
......
HR@ENMOEDU>
2.8. 截斷表
? TRUNCATE TABLE 語句
- 刪除表中所有的行
- 釋放該表所使用的儲存空間，降低高水位
? DDL操作，不能回退用 TRUNCATE 刪除的行
? 如果想刪除某幾行，那麼可以用 DELETE 語句刪除行
1) 建立測試表，並檢視分配的區
HR@ENMOEDU>create table tt_tab as select * from all_objects;
Table created.
HR@ENMOEDU>col SEGMENT_NAME for a12
HR@ENMOEDU>col SEGMENT_TYPE for a12
HR@ENMOEDU>col TABLESPACE_NAME for a15
HR@ENMOEDU>col EXTENT_ID for 999999999
HR@ENMOEDU>col BLOCKS for 999999
HR@ENMOEDU>set pagesize 300
HR@ENMOEDU>
select segment_name,segment_type,tablespace_name,extent_id,bytes/1024 as "size(k)",blocks from user_extents where segment_name=upper('tt_tab');
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID size(k) BLOCKS
------------ ------------ --------------- ---------- ---------- -------
TT_TAB TABLE USERS 0 64 8
TT_TAB TABLE USERS 1 64 8
TT_TAB TABLE USERS 2 64 8
TT_TAB TABLE USERS 3 64 8
TT_TAB TABLE USERS 4 64 8
TT_TAB TABLE USERS 5 64 8
TT_TAB TABLE USERS 6 64 8
TT_TAB TABLE USERS 7 64 8
TT_TAB TABLE USERS 8 64 8
TT_TAB TABLE USERS 9 64 8
TT_TAB TABLE USERS 10 64 8
TT_TAB TABLE USERS 11 64 8
TT_TAB TABLE USERS 12 64 8
TT_TAB TABLE USERS 13 64 8
TT_TAB TABLE USERS 14 64 8
TT_TAB TABLE USERS 15 64 8
TT_TAB TABLE USERS 16 1024 128
TT_TAB TABLE USERS 17 1024 128
TT_TAB TABLE USERS 18 1024 128
TT_TAB TABLE USERS 19 1024 128
TT_TAB TABLE USERS 20 1024 128
TT_TAB TABLE USERS 21 1024 128
22 rows selected.
HR@ENMOEDU>
2) DDL（truncate）操作後，不能回退用 TRUNCATE 刪除的行，因為是DDL操作，無需要顯示提交，當然，回滾也無濟於事。
HR@ENMOEDU>truncate table tt_tab;
Table truncated.
HR@ENMOEDU>select count(*) from tt_tab;
COUNT(*)
----------
0
HR@ENMOEDU>
--驗證DDL語句無法rollback
HR@ENMOEDU>rollback;
Rollback complete.
HR@ENMOEDU>select count(*) from tt_tab;
COUNT(*)
----------
0
HR@ENMOEDU>
3) 降低高水位，我們發現分配的區已經全部收回
HR@ENMOEDU>select segment_name,segment_type,tablespace_name,extent_id,bytes/1024 as "size(k)",blocks from user_extents where segment_name=upper('tt_tab');
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID size(k) BLOCKS
------------ ------------ --------------- ---------- ---------- -------
TT_TAB TABLE USERS 0 64 8
HR@ENMOEDU>
自測題：
請大家按照以上的方法自行驗證用delete方式清空表後（delete from tablename），區的分配情況。
第3章 內建約束
3.1. NOT NULL
注：not null約束只能在列級定義。
HR@ENMOEDU>create table t_notnull
(x number not null,
y number constraint nn_tnotnull_y not null
);
Table created.
HR@ENMOEDU>select constraint_name,constraint_type,search_condition
from user_constraints
where table_name=upper('t_notnull');
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
--------------- --------------- -------------------------
SYS_C0011054 C "X" IS NOT NULL
NN_TNOTNULL_Y C "Y" IS NOT NULL
3.2. UNIQUE
唯一鍵約束
HR@ENMOEDU>create table student1
(sno number(5) not null,
sname varchar2(20) not null constraint uk_student_sname1 unique,
idcard varchar2(18),
createtime date default sysdate not null,
constraint uk_student_idcard1 unique(idcard)
);
Table created.
HR@ENMOEDU>
HR@ENMOEDU>create table student2
(sno number(5) not null,
sname varchar2(20) not null unique,
idcard varchar2(18),
createtime date default sysdate not null,
constraint uk_student_idcard2 unique(idcard)
);
Table created.
HR@ENMOEDU>create table student3
(sno number(5) not null,
sname varchar2(20) not null unique,
idcard varchar2(18),
createtime date default sysdate not null,
unique(idcard)
);
Table created.
HR@ENMOEDU>col table_name for a12
HR@ENMOEDU>col column_name for a15
HR@ENMOEDU>col constraint_name for a20
HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('student_');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
------------ --------------- --------------------
STUDENT3 IDCARD SYS_C0011070
STUDENT3 SNAME SYS_C0011069
STUDENT3 CREATETIME SYS_C0011068
STUDENT3 SNAME SYS_C0011067
STUDENT3 SNO SYS_C0011066
STUDENT2 IDCARD UK_STUDENT_IDCARD2
STUDENT2 SNAME SYS_C0011064
STUDENT2 CREATETIME SYS_C0011063
STUDENT2 SNAME SYS_C0011062
STUDENT2 SNO SYS_C0011061
STUDENT1 IDCARD UK_STUDENT_IDCARD1
STUDENT1 SNAME UK_STUDENT_SNAME1
STUDENT1 CREATETIME SYS_C0011058
STUDENT1 SNAME SYS_C0011057
STUDENT1 SNO SYS_C0011056
15 rows selected.
HR@ENMOEDU>
3.3. PRIMARY KEY
主鍵約束
1) 行級定義主鍵，系統分配主鍵名稱
HR@ENMOEDU>CREATE TABLE t_pk1
(
sno NUMBER(5) primary key,
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>
2) 表級定義主鍵，自定義主鍵名稱
HR@ENMOEDU>CREATE TABLE t_pk2
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE,
constraint pk_t_pk2_sno primary key(sno)
);
Table created.
HR@ENMOEDU>
3) 表級定義主鍵，系統分配主鍵名稱
HR@ENMOEDU>CREATE TABLE t_pk3
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE,
primary key(sno)
);
Table created.
HR@ENMOEDU>
4) 行級定義主鍵，自定義主鍵名稱，並且指定索引使用的表空間
HR@ENMOEDU>CREATE TABLE t_pk4
(
sno NUMBER(5) constraint pk_t_pk4_sno primary KEY using index tablespace example,
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>
5) 表級定義主鍵，自定義主鍵名稱，並且指定索引使用的表空間
HR@ENMOEDU>CREATE TABLE t_pk5
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE,
constraint pk_t_pk5_sno primary key(sno) using index tablespace example
);
Table created.
HR@ENMOEDU>
6) 檢視剛剛建表的主鍵情況
HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('t_pk_');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
---------- -------------- --------------------
T_PK1 SNO SYS_C0011132
T_PK2 SNO PK_T_PK2_SNO
T_PK3 SNO SYS_C0011134
T_PK4 SNO PK_T_PK4_SNO
T_PK5 SNO PK_T_PK5_SNO
HR@ENMOEDU>
7) 檢視主鍵自動建立索引情況
HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');
INDEX_NAME UNIQUENES TABLESPACE_NAME
-------------------------- --------- ------------------------------
SYS_C0011132 UNIQUE USERS
PK_T_PK2_SNO UNIQUE USERS
SYS_C0011134 UNIQUE USERS
PK_T_PK4_SNO UNIQUE EXAMPLE
PK_T_PK5_SNO UNIQUE EXAMPLE
HR@ENMOEDU>
8) 清理環境
HR@ENMOEDU>
drop table t_pk1 purge;
drop table t_pk2 purge;
drop table t_pk3 purge;
drop table t_pk4 purge;
drop table t_pk5 purge;
Table dropped.
Table dropped.
Table dropped.
Table dropped.
Table dropped.
HR@ENMOEDU>
9) 建立測試表t_pk6，命令列增加主鍵，自定義主鍵名稱
HR@ENMOEDU>CREATE TABLE t_pk6
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>alter table t_pk6 add constraint pk_t_pk6_sno primary key (sno);
Table altered.
HR@ENMOEDU>
10) 建立測試表t_pk7，命令列增加主鍵，自定義主鍵名稱，並且指定自動建立索引的表空間
HR@ENMOEDU>CREATE TABLE t_pk7
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>
HR@ENMOEDU>
HR@ENMOEDU>alter table t_pk7 add constraint pk_t_pk7_sno primary key (sno)using index tablespace example;
Table altered.
HR@ENMOEDU>
11) 建立測試表t_pk8，命令列增加主鍵，自定義主鍵名稱，並且指定建立索引的名稱、型別及表空間
HR@ENMOEDU>CREATE TABLE t_pk8
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>
HR@ENMOEDU>alter table t_pk8 add constraint pk_t_pk8_sno primary key (sno) using index (create unique index un_t_pk8_sno on t_pk8(sno) tablespace example);
Table altered.
HR@ENMOEDU>
12) 建立測試表t_pk6，t_pk7，t_pk8，主鍵情況
HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('t_pk_');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
----------- ------------ --------------------
T_PK6 SNO PK_T_PK6_SNO
T_PK7 SNO PK_T_PK7_SNO
T_PK8 SNO PK_T_PK8_SNO
HR@ENMOEDU>
13) 建立測試表t_pk6，t_pk7，t_pk8，索引情況
HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');
INDEX_NAME UNIQUENES TABLESPACE_NAME
-------------- --------- ---------------
PK_T_PK6_SNO UNIQUE USERS
PK_T_PK7_SNO UNIQUE EXAMPLE
UN_T_PK8_SNO UNIQUE EXAMPLE
HR@ENMOEDU>
14) 主鍵的索引並一定都是唯一索引，也可以是普通的B*tree索引
HR@ENMOEDU>CREATE TABLE t_pk9
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>alter table t_pk9 add constraint pk_t_pk9_sno primary key (sno) using index (create index un_t_pk9_sno on t_pk9(sno) tablespace example);
Table altered.
HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');
INDEX_NAME UNIQUENES TABLESPACE_NAME
-------------- --------- ---------------
PK_T_PK6_SNO UNIQUE USERS
PK_T_PK7_SNO UNIQUE EXAMPLE
UN_T_PK6_SNO UNIQUE EXAMPLE
UN_T_PK9_SNO NONUNIQUE EXAMPLE
HR@ENMOEDU>
3.4. FOREIGN KEY
外來鍵約束
語法：
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id)
[ON DELETE {CASCADE|SET NULL}]
? FOREIGN KEY: 在表約束級別，定義子表中的列。
? REFERENCES: 確定父表中的表和列。
? ON DELETE CASCADE: 當父表中的行被刪除時，刪除子表中相依賴的行(級聯刪除)
? ON DELETE SET NULL: 當父表的值被刪除時，轉換外來鍵值為空。
1) 清理測試環境
HR@ENMOEDU>drop table student purge;
Table dropped.
HR@ENMOEDU>
2) 建立籍貫表，插入測試資料
HR@ENMOEDU>create table nativeplace
(
nid number(2) primary key, --主鍵id
np_name varchar2(20) not null --籍貫名稱
);
Table created.
HR@ENMOEDU>insert into nativeplace values(1,'heilongjiang');
1 row created.
HR@ENMOEDU>insert into nativeplace values(2,'jilin');
1 row created.
HR@ENMOEDU>insert into nativeplace values(3,'liaoning');
1 row created.
HR@ENMOEDU>insert into nativeplace values(4,'beijing');
1 row created.
HR@ENMOEDU>commit;
HR@ENMOEDU>select * from nativeplace;
NID NP_NAME
---------- --------------------
1 heilongjiang
2 jilin
3 liaoning
4 beijing
HR@ENMOEDU>
3) 建立學生表，插入測試資料
HR@ENMOEDU>CREATE TABLE student
(
sno NUMBER(5) constraint pk_student primary KEY using index, --學生編號（主鍵）
sname VARCHAR2(20) not null, --學生名稱
idcard VARCHAR2(18) not null, --身份證號
createtime DATE default SYSDATE not null, --建立時間
native_place number(2), --籍貫id
constraint fk_student foreign key (native_place) REFERENCES nativeplace (nid) ON DELETE SET NULL
);
Table created.
HR@ENMOEDU>
insert into student values(1,'zhangsan','123456789123456780',default,1);
1 row created.
HR@ENMOEDU>
insert into student values(2,'lisi','123456789123456781',default,2);
1 row created.
HR@ENMOEDU>
insert into student values(3,'wangwu','123456789123456782',default,3);
1 row created.
HR@ENMOEDU>
insert into student values(4,'zhaoliu','123456789123456783',default,4);
1 row created.
HR@ENMOEDU>col sno for 999
HR@ENMOEDU>col sname for a10
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
---- ---------- ------------------ ------------------- ------------
1 zhangsan 123456789123456780 2012-09-14 16:06:06 1
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13 3
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
HR@ENMOEDU>
4) 建立課程表，插入測試資料
HR@ENMOEDU>create TABLE course
(
cno NUMBER(5) CONSTRAINT pk_course PRIMARY KEY NOT NULL, --課程編號（主鍵）
cname VARCHAR2(20) NOT NULL --課程名稱
);
Table created.
HR@ENMOEDU>insert into course VALUES(1,'Oracle');
1 row created.
HR@ENMOEDU>insert into course VALUES(2,'Java');
1 row created.
HR@ENMOEDU>insert into course VALUES(3,'C++');
1 row created.
HR@ENMOEDU>select * from course;
CNO CNAME
--------- --------------
1 Oracle
2 Java
3 C++
HR@ENMOEDU>
5) 建立成績表，插入測試資料
HR@ENMOEDU>create TABLE sc
(
sno NUMBER(5), --學生編號
cno NUMBER(5), --課程編號
grade NUMBER(4,1), --成績
constraint pk_sc primary key (sno,cno),
constraint fk_sc_sno foreign key (sno) REFERENCES student (sno) ON DELETE CASCADE,
constraint fk_sc_cno foreign key (cno) REFERENCES course (cno)
);
Table created.
HR@ENMOEDU>
insert into sc values(1,1,91);
insert into sc values(1,2,92);
insert into sc values(1,3,93);
insert into sc values(2,1,88);
insert into sc values(2,2,77);
insert into sc values(2,3,99);
insert into sc values(3,1,65);
insert into sc values(3,2,75);
insert into sc values(3,3,85);
insert into sc values(4,1,80);
insert into sc values(4,2,88);
insert into sc values(4,3,89);
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>select * from sc;
SNO CNO GRADE
---- -------- ---------
1 1 91
1 2 92
1 3 93
2 1 88
2 2 77
2 3 99
3 1 65
3 2 75
3 3 85
4 1 80
4 2 88
4 3 89
12 rows selected.
HR@ENMOEDU>
6) 測試，子表插入父表不存在的資料的時候，報錯的效果（往成績表中插入資料，學生編號不存在）
HR@ENMOEDU>insert into sc values(5,1,100);
insert into sc values(5,1,100)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_SC_SNO) violated - parent key not
found
HR@ENMOEDU>
7) 往成績表中插入資料，課程編號不存在
HR@ENMOEDU>insert into sc values(1,4,100);
insert into sc values(1,4,100)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_SC_CNO) violated - parent key not
found
HR@ENMOEDU>
8) 開始測試，驗證set null，檢視錶nativeplace
HR@ENMOEDU>select * from nativeplace;
NID NP_NAME
--------- --------------------
1 heilongjiang
2 jilin
3 liaoning
4 beijing
HR@ENMOEDU>
9) 檢視錶student
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
---- ---------- ------------------ ------------------- ------------
1 zhangsan 123456789123456780 2012-09-14 16:06:06 1
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13 3
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
HR@ENMOEDU>
10) 刪除籍貫表中nid為3的記錄
HR@ENMOEDU>delete nativeplace where nid=3;
1 row deleted.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
11) 檢視籍貫表，nid為3的記錄已經刪除
HR@ENMOEDU>select * from nativeplace;
NID NP_NAME
--------- --------------------
1 heilongjiang
2 jilin
4 beijing
HR@ENMOEDU>
12) 檢視學生表，此時之前籍貫為3的籍貫id已經變為null，此時，外來鍵約束set null起了作用
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
---- ---------- ------------------ ------------------- ------------
1 zhangsan 123456789123456780 2012-09-14 16:06:06 1
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
13) 驗證級聯刪除
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
--- ---------- ------------------ ------------------- ------------
1 zhangsan 123456789123456780 2012-09-14 16:06:06 1
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
HR@ENMOEDU>select * from sc;
SNO CNO GRADE
---- -------- ---------
1 1 91
1 2 92
1 3 93
2 1 88
2 2 77
2 3 99
3 1 65
3 2 75
3 3 85
4 1 80
4 2 88
4 3 89
12 rows selected.
HR@ENMOEDU>
14) 刪除學生表中，學號為1的記錄，查詢驗證，學號為1的記錄已經刪除
HR@ENMOEDU>delete student where sno=1;
1 row deleted.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
---- -------- ------------------ ------------------- ------------
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
HR@ENMOEDU>
15) 檢視成績表，此時發現，學號為1的學生的成績已經全部刪除，此時外來鍵的級聯刪除起了作用
HR@ENMOEDU>select * from sc;
SNO CNO GRADE
---- -------- ---------
2 1 88
2 2 77
2 3 99
3 1 65
3 2 75
3 3 85
4 1 80
4 2 88
4 3 89
9 rows selected.
HR@ENMOEDU>
16) 驗證預設約束規則，提示無法刪除
HR@ENMOEDU>delete course where cno=1;
delete course where cno=1
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.FK_SC_CNO) violated - child record found
HR@ENMOEDU>
3.5. CHECK
1) 刪除環境中原來的學生表
HR@ENMOEDU>drop table student1 purge;
Table dropped.
HR@ENMOEDU>
建立學生表，要求，建表的同時，建立兩個check，一個保證身份證內容都為小寫，一個是保證所有學生的名字都為大寫
HR@ENMOEDU>CREATE TABLE student1
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18) check (idcard=lower(idcard)),
createtime DATE,
CONSTRAINT ck_student_sname CHECK (sname=upper(sname))
) ;
Table created.
HR@ENMOEDU>
2) 插入測試資料，驗證check約束
HR@ENMOEDU>insert into student1 values(1,'AA','123a',sysdate);
1 row created.
HR@ENMOEDU>insert into student1 values(2,'aa','123a',sysdate);
insert into student1 values(2,'aa','123a',sysdate)
*
ERROR at line 1:
ORA-02290: check constraint (HR.CK_STUDENT_SNAME) violated
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
請問：
下面語句是否能夠執行成功？
insert into student1 values(1,null,null,sysdate);可以，null空值是不受check約束的
注：下面兩個建表指令碼是不被允許的。
Drop table student1 purge;
CREATE TABLE student1
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE check(createtime <sysdate)
);
CREATE TABLE student1
(
sno NUMBER(5),
sname VARCHAR2(18) check(sname=idcard),
idcard VARCHAR2(18),
createtime DATE
);
如下表示式在check是不被允許的
- 涉及到 CURRVAL, NEXTVAL, LEVEL 和 ROWNUM 偽列
- 呼叫 SYSDATE, UID, USER 和 USERENV 函式
- 涉及其它行中其它值的查詢
第4章 建立檢視
語法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
|
OR REPLACE |
如果檢視已經存在重新建立它 |
|
FORCE |
建立檢視，而不管基表是否存在 |
|
NOFORCE |
只在基表存在的情況下建立檢視(這是預設值) |
|
View |
檢視的名字 |
|
Alias |
為由檢視查詢選擇的表示式指定名字(別名的個數必須與由檢視選擇的表示式的個數匹配) |
|
Subquery |
是一個完整的 SELECT 語句(對於在 SELECT 列表中的欄位你可以用別名) |
|
WITH CHECK OPTION |
指定只有可訪問的行在檢視中才能被插入或修改 |
|
Constraint |
為 CHECK OPTION 約束指定的名字 |
|
WITH READ ONLY |
確保在該檢視中沒有 DML 操作被執行 |
注：如果基表不存在，那麼
Force 可以建立成功，但會提示“編譯錯誤”
Noforce 直接提示表或檢視不存在
4.1. 簡單檢視、複雜檢視區別及測試
|
特性 |
簡單檢視 |
複雜檢視 |
|
表的數目 |
一個 |
一個或多個 |
|
包含函式 |
無 |
有 |
|
包含資料分組 |
無 |
有 |
|
透過檢視進行DML操作 |
是 |
不允許 |
1) 建立學生表
HR@ENMOEDU>CREATE TABLE student
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE default SYSDATE not null,
native_place number(2)
);
2) 學生表插入資料
insert into student values(1,'zhangsan','123456789123456780',default,1);
insert into student values(2,'lisi','123456789123456781',default,2);
insert into student values(3,'wangwu','123456789123456782',default,3);
insert into student values(4,'zhaoliu','123456789123456783',default,4);
3) 建立簡單檢視和複雜檢視 grant create view to scott;
簡單檢視：
HR@ENMOEDU>create view vw_student1 as select sno,sname,createtime from student;
View created.
HR@ENMOEDU>create view vw_student2 as select sno,sname,createtime,sysdate as dd from student;
View created.
複雜檢視：
HR@ENMOEDU>create view vw_student3 as select sno,sname,to_char(createtime,'yyyy-mm-dd') as ctime from student;
View created.
HR@ENMOEDU>create view vw_student4 as select native_place,count(*) as num from student group by native_place;
View created.
HR@ENMOEDU>
4) 插入資料測試
HR@ENMOEDU>insert into vw_student1 values(5,'test',sysdate);
1 row created.
HR@ENMOEDU>insert into vw_student2(sno,sname,createtime) values(6,'test1',sysdate);
1 row created.
HR@ENMOEDU>insert into vw_student3(sno,sname,ctime) values(7,'test2',to_char(sysdate,'yyyy-mm-dd'));
insert into vw_student3(sno,sname,ctime) values(7,'test2',to_char(sysdate,'yyyy-mm-dd'))
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
HR@ENMOEDU>insert into vw_student4 values(1,1);
insert into vw_student4 values(1,1)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
HR@ENMOEDU>
4.2. WITH CHECK OPTION
檢視中的check約束
1) 建表、插入測試資料
HR@ENMOEDU>drop table student purge;
Table dropped.
HR@ENMOEDU>CREATE TABLE student
(
sno NUMBER(5) constraint pk_student primary KEY using index,
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE default SYSDATE,
native_place number(2)
);
Table created.
HR@ENMOEDU>
insert into student values(1,'zhangsan','123456789123456780',default,1);
insert into student values(2,'lisi','123456789123456781',default,2);
insert into student values(3,'wangwu','123456789123456782',default,3);
insert into student values(4,'zhaoliu','123456789123456783',default,2);
insert into student values(5,'zhaosi','123456789123456783',default,2);
insert into student values(6,'maba','123456789123456783',default,2);
commit;
2) 建立沒有約束的簡單檢視
HR@ENMOEDU>create or replace view vw_student7
as
select sno,sname,idcard,native_place
from student where native_place=2;
View created.
HR@ENMOEDU>
3) 建立有約束的簡單檢視，（with check option，即指定只有可訪問的行在檢視中才能被插入或修改）
HR@ENMOEDU>create or replace view vw_student8
as
select sno,sname,idcard,native_place
from student where native_place=2
with check option;
View created.
HR@ENMOEDU>
4) 插入資料測試
HR@ENMOEDU>
insert into vw_student8 values(7,'zhu88','123456789123456783',3);
insert into vw_student8 values(7,'zhu88','123456789123456783',3)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
HR@ENMOEDU>
insert into vw_student7 values(7,'zhu88','123456789123456783',3);
1 row created.
HR@ENMOEDU>
insert into vw_student8 values(8,'niu99','123456789123456783',2);
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
小結：
1) 當往有約束的簡單檢視中插入不符合約束的資料時，資料庫則會丟擲錯誤，當插入符合約束條件的資料，則插入正常。
2) 當往沒有約束的檢視中插入符合標準的資料時，則可以正常插入
4.3. WITH READ ONLY
只讀檢視
HR@ENMOEDU>create or replace view vw_student9
as
select sno,sname,idcard,native_place
from student
where native_place=2
with read only;
View created.
HR@ENMOEDU>
HR@ENMOEDU>insert into vw_student9 values(9,'r_only','123456789123456783',2);
insert into vw_student9 values(9,'r_only','123456789123456783',2)
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
第5章 其他資料庫物件
5.1. 序列
(一)語法：
1) 建立序列
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
|
sequence |
是序列發生器的名字 |
|
INCREMENT BY n |
指定序列號之間的間隔，在這兒 n 是一個整數 (如果該子句被省略，序列增量為 1) |
|
START WITH n |
指定要產生的第一個序列數 (如果該子句被省略，序列從 1開始) |
|
MAXVALUE n |
指定序列能產生的最大值 |
|
NOMAXVALUE |
對於升序序列指定 10^27 為最大值，對於降序序列指定-1為最大值 (這是預設選項) |
|
MINVALUE n |
指定最小序列值 |
|
NOMINVALUE |
對於升序序列指定 1 為最小值，對於降序序列指定-(10^26)為最小值 (這是預設選項) |
|
CYCLE|NOCYCLE |
指定序列在達到它的最大或最小值之後，是否繼續產生 (NOCYCLE 是預設選項) |
|
CACHE n|NOCACHE |
指定 Oracle 伺服器預先分配多少值，並且保持在記憶體中(預設情況下，Oracle 伺服器緩衝 20 個值) |
2) 修改序列：
ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
3) 刪除序列：
DROP SEQUENCE sequence;
(二)使用NEXTVAL和CURRVAL的規則
1) 可以使用nextval和currval
? 一個不是子查詢的一部分的 SELECT 語句的 SELECT 列表
? 在一個 INSERT 語句中子查詢的 SELECT 列表
? 一個 INSERT 語句中的 VALUES 子句
? 一個 UPDATE 語句的 SET 子句
2) 不允許使用nextval和currval
? 一個檢視的 SELECT 列表
create or replace view vw_a as select seq_test.nextval,1 as id from dual;
? 一個帶 DISTINCT 關鍵字的 SELECT 語句
? 一個帶 GROUP BY、HAVING 或 ORDER BY 子句的 SELECT 語句
? 一個在 SELECT、DELETE 或 UPDATE 語句中的子句
? 在 CREATE TABLE 或 ALTER TABLE 語句中的 DEFAULT 表示式
(三)序列測試
HR@ENMOEDU>create sequence SEQ
minvalue 1
maxvalue 100
start with 2
increment by 1;
Sequence created.
--在沒有執行nextval的時候，無法執行currval
HR@ENMOEDU>select seq.currval from dual;
select seq.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session
HR@ENMOEDU>select seq.nextval from dual;
NEXTVAL
----------
2
HR@ENMOEDU>select seq.nextval from dual;
NEXTVAL
----------
3
HR@ENMOEDU>
新開啟一個會話:
HR@ENMOEDU> select seq.currval from dual;
select seq1.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session
小結：
1) 在一個新的會話中，不能直接使用currval，必須先nextval後再currval
2) 新建的一個序列，第一次nextval的值為start with的值
(四)序列和觸發器配合應用
HR@ENMOEDU>create table t_seq(nid number(10),name varchar2(10));
Table created.
HR@ENMOEDU>create sequence seq_t_seq;
Sequence created.
HR@ENMOEDU>create trigger trg_t_seq
before insert on t_seq
for each row
begin
if :new.nid is null then
select seq_t_seq.nextval
into :new.nid
from dual;
end if;
end;
/
Trigger created.
HR@ENMOEDU>insert into t_seq (name) values('a');
1 row created.
HR@ENMOEDU>insert into t_seq (name) values('b');
1 row created.
HR@ENMOEDU>
上面的insert語句，有這樣的誤解，nid不是主鍵，也非not null，插入兩條記錄很正常，那麼我們將nid修改為主鍵，如下：
HR@ENMOEDU>alter table t_seq add primary key(nid);
Table altered.
HR@ENMOEDU>insert into t_seq (name) values('c');
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
HR@ENMOEDU>select * from t_seq;
NID NAME
---------- ----------
1 a
2 b
3 c
HR@ENMOEDU>
此時我們發現，主鍵欄位也是有資料的，主鍵的資料就是來源於觸發器，這個是一個比較典型的觸發器利用序列協助寫入主鍵的例子。
(五)查詢序列
1) 檢視序列SEQ_T_SEQ
HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,cycle_flag,last_number from user_sequences where sequence_name='SEQ_T_SEQ';
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------- ---------- ---------- ------------ -----------
SEQ_T_SEQ 1 1.0000E+28 1 21
HR@ENMOEDU>
注：如果指定NOCACHE 選項，那麼LAST_NUMBER顯示序列中下一個有效的值，'SEQ_T_SEQ'這個序列的LAST_NUMBER顯示的就不是下一個有效值
2) 檢視建立SEQ_T_SEQ的指令碼，確認cache
HR@ENMOEDU>set long 999999
HR@ENMOEDU>select dbms_metadata.get_ddl(object_type => 'SEQUENCE',name => 'SEQ_T_SEQ') from dual;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'SEQUENCE',NAME=>'SEQ_T_SEQ')
------------------------------------------------------------------------
CREATE SEQUENCE "HR"."SEQ_T_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999
999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
HR@ENMOEDU>
3) 下面舉我們建立一個NOCACHE的例子
HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,cycle_flag,last_number from user_sequences where sequence_name='SEQ_T_SEQ';
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------- ---------- ---------- ------------ -----------
SEQ_T_SEQ 1 1.0000E+28 1 21
HR@ENMOEDU>create sequence seq_nocache nocache;
Sequence created.
4) 執行一次sequence，得到的結果為1，下一個值應該是2
HR@ENMOEDU>select seq_nocache.nextval from dual;
NEXTVAL
----------
1
HR@ENMOEDU>
5) 檢視序列SEQ_NOCACHE的LAST_NUMBER，此時我們發現LAST_NUMBER值為2，也就是下一個有效值
HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,last_number from user_sequences where sequence_name='SEQ_NOCACHE';
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------- ---------- ---------- ------------ -----------
SEQ_NOCACHE 1 1.0000E+28 1 2
HR@ENMOEDU>
(六)刪除序列
HR@ENMOEDU>drop sequence seq_nocache;
Sequence dropped.
HR@ENMOEDU>
5.2. 索引
(一)自動建立
建立主鍵或唯一鍵約束會在相應欄位上建立唯一索引
1) 建立測試表
HR@ENMOEDU>create table t_a_index(x number,y number);
Table created.
HR@ENMOEDU>
2) 修改表，增加主鍵，主鍵的名字為pk_t_a_index
HR@ENMOEDU>alter table t_a_index add constraint pk_t_a_index primary key(x);
Table altered.
HR@ENMOEDU>
3) 修改表，增加唯一鍵，唯一鍵的名字為uk_t_a_index
HR@ENMOEDU>alter table t_a_index add constraint uk_t_a_index unique(y);
Table altered.
HR@ENMOEDU>
4) 檢視錶T_A_INDEX的相關索引，發現有兩個與約束名稱相同的索引
HR@ENMOEDU>select index_name,index_type,table_owner,table_name,uniqueness from user_indexes where table_name='T_A_INDEX';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENES
------------ ---------- ----------- ---------- ---------
UK_T_A_INDEX NORMAL HR T_A_INDEX UNIQUE
PK_T_A_INDEX NORMAL HR T_A_INDEX UNIQUE
HR@ENMOEDU>
透過這個測試，我們瞭解到，主鍵和唯一鍵會自動建立唯一索引，索引的名稱與約束的名稱相同。
(二)手工建立
在一個或多個列上建立索引，語法：
CREATE INDEX index ON table (column[, column]...);
1) 建立測試表
HR@ENMOEDU>create table t_m_index(x number,y number,z number);
Table created.
HR@ENMOEDU>
2) 在x欄位上建立普通索引
HR@ENMOEDU>create index idx_t_m_index_x on t_m_index(x);
Index created.
HR@ENMOEDU>
3) 在y,z欄位上建立普通複合索引
HR@ENMOEDU>create index idx_t_m_index_yz on t_m_index(y,z) tablespace users;
Index created.
HR@ENMOEDU>
4) 檢視錶T_M_INDEX相關索引
HR@ENMOEDU>select index_name,index_type,table_owner,table_name,uniqueness from user_indexes where table_name='T_M_INDEX';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENES
---------------- ---------- ----------- ---------- ---------
IDX_T_M_INDEX_YZ NORMAL HR T_M_INDEX NONUNIQUE
IDX_T_M_INDEX_X NORMAL HR T_M_INDEX NONUNIQUE
HR@ENMOEDU>
注：字典表user_indexes的UNIQUENES欄位
comment on column SYS.USER_INDEXES.UNIQUENESS is 'Uniqueness status of the index: "UNIQUE", "NONUNIQUE", or "BITMAP"';
5) 檢視T_M_INDEX相關索引及索引欄位
HR@ENMOEDU>SELECT t.index_name, t.column_name, t.column_position col_pos, d.uniqueness FROM user_indexes d, user_ind_columns t
WHERE t.index_name = d.index_name
AND t.table_name = 'T_M_INDEX';
INDEX_NAME COLUMN_NAME COL_POS UNIQUENES
---------------- ------------ ---------- ---------
IDX_T_M_INDEX_X X 1 NONUNIQUE
IDX_T_M_INDEX_YZ Y 1 NONUNIQUE
IDX_T_M_INDEX_YZ Z 2 NONUNIQUE
HR@ENMOEDU>
5.3. 同義詞
同義詞，簡單點兒理解就是一個別名，一個對映的關係。
(一)同義詞的分類
Oracle同義詞有兩種型別，分別是公用Oracle同義詞與私有Oracle同義詞。
1) 公用Oracle同義
由一個特殊的使用者組Public所擁有。顧名思義，資料庫中所有的使用者都可以使用公用同義詞。公用同義詞往往用來標示一些比較普通的資料庫物件，這些物件往往大家都需要引用。
2) 私有Oracle同義詞（預設建立）
它是跟公用同義詞所對應，他是由建立他的使用者所有。當然，這個同義詞的建立者，可以透過授權控制其他使用者是否有權使用屬於自己的私有同義詞。
(二)語法
1) 建立同義詞
create [or replace] [public] synonym [schema.]synonym
for [schema.]object[@dblink];
2) 刪除同義詞
drop [public] synonym [schema.]synonym [force];
(三)先決條件
1) 要自己的schema下建立私有的同義詞，你必須擁有CREATE SYNONYM系統許可權。
2) 要在其他schema下建立一個私有同義詞，你必須有CREATE ANY SYNONYM系統許可權。
3) 建立一個公共的同義詞，你必須有CREATE PUBLIC SYNONYM系統許可權。
(四)私有同義詞測試
1) 在scott下想檢視hr下的employees，沒有許可權檢視，如果不想直接把employees的查詢許可權給scott，那麼可以考慮建立一個同義詞
SYS@ENMOEDU> create user scott identified by scott ;
User created.
SYS@ENMOEDU> grant connect,resource,create synonym to scott;
Grant succeeded.
SYS@ENMOEDU> conn scott/scott
Connected.
SCOTT@ENMOEDU> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@ENMOEDU>
2) 連線到scott使用者下建立hr使用者下的employees的同義詞syn_employees
SCOTT@ENMOEDU> conn hr/hr
Connected.
HR@ENMOEDU> grant select on employees to scott;
Grant succeeded.
HR@ENMOEDU> conn scott/tiger
Connected.
SCOTT@ENMOEDU>create synonym syn_employees for hr.employees;
create synonym syn_employees for hr.employees
*
ERROR at line 1:
ORA-01031: insufficient privileges
SCOTT@ENMOEDU >conn / as sysdba
Connected.
SYS@ENMOEDU >grant create any synonym to scott;
Grant succeeded.
SYS@ENMOEDU >conn scott/tiger;
Connected.
SCOTT@ENMOEDU >create synonym syn_employees for hr.employees;
Synonym created.
3) 在scott使用者下查詢hr下的同義詞
HR@ENMOEDU> conn scott/tiger
Connected.
SCOTT@ENMOEDU> select count(*) from syn_employees;
COUNT(*)
----------
107
SCOTT@ENMOEDU>
(五)公共同義詞測試
1) 在sys使用者下建立公共同義詞dept
SYS@ENMOEDU>create or replace public synonym dept for hr.departments;
Synonym created.
SYS@ENMOEDU>
2) 連線到scott使用者測試公共同義詞是否可以查詢
查詢結果為表或檢視不存在，之所以報這個錯誤，是因為scott沒有查詢hr.departments的許可權
SYS@ENMOEDU> conn scott/scott
Connected.
SCOTT@ENMOEDU> select * from dept;
select * from dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@ENMOEDU>
3) 我們到擁有DBA許可權的system使用者可以檢視到dept同義詞
SCOTT@ENMOEDU> conn system/oracle
Connected.
SYSTEM@ENMOEDU>select count(*) from dept;
COUNT(*)
----------
27
SYSTEM@ENMOEDU>
4) 我們可以將同義詞departments的select許可權賦予public
SYSTEM@ENMOEDU> conn hr/hr
Connected.
HR@ENMOEDU>grant select on departments to public;
Grant succeeded.
HR@ENMOEDU>
5) 我們再回到scott使用者，此時就可以查詢了
HR@ENMOEDU> conn scott/scott
Connected.
SCOTT@ENMOEDU> select count(*) from dept;
COUNT(*)
----------
27
SCOTT@ENMOEDU>
其實規則是這樣的：
- 首先檢視發出命令的使用者對錶或者檢視是否存在並且有訪問許可權
- 如果表或檢視不存在，則檢查私有同義詞
- 如果私有同義詞不存在，則訪問公共同義詞
- 如果公共同義詞仍不存在，Oracle返回訊息“ORA-00942 table or view does not exist”.
第6章 使用集合運算
6.1. UNION
A集合和B集合的合併,去掉兩集合重複的部分並且排序
HR@ENMOEDU>select department_id,first_name from employees where department_id in (10,20);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
20 Michael
20 Pat
HR@ENMOEDU>select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
30 Den
30 Alexander
30 Shelli
30 Sigal
30 Guy
30 Karen
7 rows selected.
HR@ENMOEDU>
select department_id,first_name from employees where department_id in (10,20)
union
select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
20 Michael
20 Pat
30 Alexander
30 Den
30 Guy
30 Karen
30 Shelli
30 Sigal
9 rows selected.
HR@ENMOEDU>
6.2. UNION ALL
A集合和B集合的合併，不去重，不排序
HR@ENMOEDU>
select department_id,first_name from employees where department_id in (10,20)
union all
select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
20 Michael
20 Pat
10 Jennifer
30 Den
30 Alexander
30 Shelli
30 Sigal
30 Guy
30 Karen
10 rows selected.
HR@ENMOEDU>
6.3. INTERSECT
兩個集合的交集部分，排序並去重
HR@ENMOEDU>
select department_id,first_name from employees where department_id in (10,20)
intersect
select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
HR@ENMOEDU>
6.4. MINUS
取兩個集合的差集，返回A集合中存在,B集合中不存在的資料
HR@ENMOEDU>
select department_id,first_name from employees where department_id in (10,20)
minus
select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
20 Michael
20 Pat
HR@ENMOEDU>
6.5. UNION/UNION ALL注意事項
HR@ENMOEDU>create table t_union1(col number);
Table created.
HR@ENMOEDU>
HR@ENMOEDU>create table t_union2(col number);
Table created.
HR@ENMOEDU>
思考題：
在先不考慮t_union1和t_union2兩張表中的資料的情況下，請問如下兩個sql的結果是否相同？相同
SQL1：
Select 1 as type,col from t_union1
Union
Select 2,col from t_union2;
SQL2：
Select 1 as type,col from t_union1
Union all
Select 2,col from t_union2;
1) 測試：
HR@ENMOEDU>insert into t_union1 values(1);
1 row created.
HR@ENMOEDU>insert into t_union1 values(1);
1 row created.
HR@ENMOEDU>insert into t_union2 values(1);
1 row created.
HR@ENMOEDU>insert into t_union2 values(2);
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>select * from t_union1;
COL
----------
1
1
HR@ENMOEDU>select * from t_union2;
COL
----------
1
2
HR@ENMOEDU>select 1 as type,col from t_union1;
TYPE COL
---------- ----------
1 1
1 1
HR@ORA11GR2>select 2 as type,col from t_union2;
TYPE COL
---------- ----------
2 1
2 2
HR@ENMOEDU>Select 1 as type,col from t_union1
Union
Select 2,col from t_union2;
TYPE COL
--------- ---------
1 1
2 1
2 2
HR@ENMOEDU>Select 1 as type,col from t_union1
Union all
Select 2,col from t_union2;
TYPE COL
--------- ---------
1 1
1 1
2 1
2 2
HR@ENMOEDU>
2) 小結
透過上面的測試我們發現，雖然有常量欄位“type”，但是這兩個結果集還是不一樣的，因為union的去重操作不單單是兩個結果集之間的去重，而是整個結果集的一個去除重複的動作（即先是兩個結果集的分別去重，然後再兩個結果集的合併，最後再去重排序），所以，我們在使用union和union all的時候要格外注意，特別是在效能最佳化的時候，不要不假思索的把union替換成union all。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2128374/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql】訓練五SQL
- 【sql】訓練四SQL
- 【sql】訓練三SQL
- 【sql】訓練二SQL
- 【sql】訓練一SQL
- 資料集訓練
- 使用 TensorBoard 視覺化模型、資料和訓練ORB視覺化模型
- YOLOv5模型訓練及檢測YOLO模型
- Caffe 訓練mnist資料
- 資料集訓練+1
- fashion資料集訓練
- SQL Server 資料表程式碼建立約束SQLServer
- 利用Tensorboard視覺化模型、資料和訓練過程ORB視覺化模型
- 資料模型需要多少訓練資料？模型
- 2 Day DBA-管理方案物件-練習：使用HR.EMPLOYEES表建立檢視。物件
- 訓練指南：資料訓練定期儲存【GpuMall雲平臺特價】GPU
- Caffe下自己的資料訓練和測試
- 【預訓練語言模型】 使用Transformers庫進行BERT預訓練模型ORM
- Opencv 用SVM訓練檢測器OpenCV
- 西部戰略 | GBase資料庫訓練營華西專場培訓資料庫
- OCR訓練中文樣本庫和識別
- yolov3訓練自己資料教程YOLO
- 模型訓練：資料預處理和預載入模型
- 視覺化 Keras 訓練過程視覺化Keras
- Java基礎 --- 物件導向綜合訓練Java物件
- 梯度會洩漏訓練資料？MIT新方法從梯度竊取訓練資料只需幾步梯度MIT
- acm訓練題ACM
- IOS Swift 訓練iOSSwift
- 4.17訓練賽
- 【預訓練語言模型】使用Transformers庫進行GPT2預訓練模型ORMGPT
- 自訓練 + 預訓練 = 更好的自然語言理解模型模型
- 訓練一個目標檢測模型模型
- 資料庫——一些復健（主要是約束和索引相關練習）資料庫索引
- java大資料開發訓練營--NginxJava大資料Nginx
- java大資料開發訓練營--ImpalaJava大資料
- 華南攻略 | GBase資料庫訓練營專場培訓最後一季資料庫
- 2 Day DBA-管理方案物件-關於方案物件管理許可權-管理檢視-練習：檢視檢視物件
- 訓練資料也外包？這家公司“承包”了不少註釋訓練資料，原來是這樣做的……