玩轉oracle學習第五天，oracle學習第五天

玩轉oracle學習第五天，oracle學習第五天


1.上節回顧
2.維護資料的完整性
3.管理索引
4.系統管理權限和角色

 
 1.掌握維護oracle資料完整性的技巧
 2.理解索引的概念，會建立索引
 3.管理oracle的許可權和角色
 
介紹：維護資料的完整性
資料完整性用於確保資料庫資料遵從一定的商業和邏輯guize，
在oracle中，資料完整性可以使用約束，觸發器，
應用程式（過程，函數）三種方式來實現，在這三種方法中，因為約束易於維護，
並且具有最好的效能，所以作為維護資料完整性的首選

約束：約束用於確保資料庫資料滿足特定的商業規則，
在oracle中，約束包括：not null,unique,primary key,
foreign key和check五種

not null(非空)
 如果定義了not null，那麼當插入資料時，必須為列提供資料
 
 unique(唯一):
  當定義了唯一約束後，該列值是不能重複的，但可以為null
 
primary key：

check：用於強制行資料必須滿足一定的規則

商店售貨系統資料表設計案例：
商品表：（goods）

商品號：goodsId
商品名：goodsName
單價：unitprice
商品類別：category
供應商：provider

客戶表：（customer）

客戶號：customerId
姓名：name
住址：address
電子郵件：email
性別：sex
身份證：cardId

購買：（purchase）

客戶號：customerId
商品號：goodsId
購買數量：nums  

請用sql語言完成下列功能：
1.建表，在定義中要求聲明：
（1）建表
（2）客戶的姓名不可為空
（3）單價必須大於0，購買數量必須在1到30之間
（4）電子郵件不能夠重複
（5）客戶的性別必須是男或者女，預設為男

create table goods(goodId char(8) primary key,
 goodsName varchar2(30),
 unitprice number(10,2) check(unitprice > 0),
 category varchar2(8),
 provider varchar2(30)
);

create table customer(customerId char(8) primary key,
name varchar2(50) not null,
address varchar2(50),
email varchar2(50) unique,
sex char(2) default '男' check(sex in ('男','女'))，
cardId char(18)
);

create table purchase(customerId char(8) references customer(customerId),  //外鍵
goodsId char(8) references goods(goodsId),
nums numbers(10) check (nums between 1 and 30)
);

商店售貨系統資料表設計案例（2）
如果在建表時忘記建立必要的約束，則可以在建表後使用 alter table進行修改，

alter table goods modify goodsName not null;

alter table customer add constraint cardunique unique(cardId);//修改一個表，增加唯一性限制式，並位唯一性取名字cardunique

alter table customer add constraint addresscheck check (address in ('東城','西城'))；

刪除約束：
alter table 表名 drop constraint 約束名稱;

顯示約束資訊：
1.顯示約束資訊：
通過查詢資料字典視圖user_constraints,可以顯示目前使用者所有
的約束的資訊
select constraint_name,constraint_type,status,validated from
user_constraints where table_name='表名';

2.顯示約束列
通過查詢資料字典視圖user_cons_columns,可以顯示約束所
對應的表列資訊

select column_name,position from user_cons_columns where constraint_name='約束名';

3.當然也有更容易的方法，直接用PL/SQL developer查看即可

維護資料完整性 -表級定義和列級定義
列級定義：
列級定義是在定義列的同時定義約束：
create table department4
(
dept_id number(2) constraint pk_department primary key,
name varchar2(12),
ioc varchar2(12)
);

表級定義：
表級定義是在定義了所有列後，再定義約束，這裡需要注意：
not null 約束只能在列級上定義
create table employee2
(
emp_id number(4),name varchar2 (15),dept_id number(2),
constraint pk_employee primary key (emp_id),
constraint fk_department foreign key (dept_id) references department4(dept_id)
);

管理索引：原理介紹
索引是用於加速資料存放區的資料對象，合理的使用索引可以大大
降低i/o次數，從而提高資料訪問效能，索引有很多種
為什麼添加索引後，會加快查詢速度呢？

視圖是為了將資料進行分類，歸檔，然後得到資料的索引，通過
索引就可找到資料了
索引提高了資料的尋找速度，但是索引並不是建的越多越好

索引分類：
單列索引:基於單個列所建的索引
create index nameIndex on customer(name);

複合索引：基於兩列或是多個列的索引，在同一張表上可以有多個索引，但是要求
列的組合必須不同，比如：
create index emp_index1 on emp(ename,job);
和
create index emp_index1 on emp(job,ename);
是兩個不同的索引

sql語句的執行時從右至左的進行掃描的

使用原則：
（1）在大表上建立索引
（2）在where子句或是串連條件上經常引用的列上建立索引
（3）索引的層次不要超過4層

索引的缺點：
1.建立索引，系統要佔用大約為表的1.2倍的硬碟和記憶體空間來儲存索引
2.更改資料的時候，系統必須要有額外的時間來同時對索引進行更新，以鑑效組資料和索引的一致性

按照資料存放區方式，可以分為B*樹，通過查詢資料字典視圖dba_indexs和
user_indexa,可以顯示索引的資訊

系統管理權限和角色

oracle中的許可權：
（1）系統許可權：oracle提供了140多種系統許可權
什麼是系統許可權？
 操作資料庫系統的許可權
系統許可權有哪些？
如何賦予系統許可權？
授予系統許可權一般是dba來完成的
create user ken identified by m123;
create user tom identified by m123;

conn system/manager as sysydba;
grant create session,create table to ken with admin option;(許可權可以轉移授權)

grant create view to ken;（許可權只能自己有，不能授予其他人）

回收系統許可權：使用revoke
revoke create session from ken;
注意：系統許可權不是級聯回收的
create是系統許可權

（2）對象許可權：
什麼是系統許可權？
 操作資料庫資料對象的許可權
系統許可權有哪些？
如何賦予系統許可權？
方案：當建立一個使用者以後，資料庫就會為該使用者指派一個方案，方案名稱和
使用者名稱稱是一致的，方案中包括各種各樣的資料對象，表，視圖，索引，觸發器
角色：oracle提供了大概25總角色，每種角色包含了一大批許可權
常見對象許可權：
alter delete select insert
update index references execute

dba_tab_privs;//顯示對象許可權
授予對象許可權：
create user monkey identified by m123;
grant create session to monkey;

grant select on emp to monkey;
grant update on emp to monkey;
grant select on emp to monkey;
或者
grant all on emp to monkey;

grant update on emp(sal) to monkey; //monkey使用者只可以修改scott使用者的emp表的sal欄位
grant update on emp(ename,sal) to monkey;

grant index on scott.emp to blake with grant option;
使用with grant option選項
該選項用於轉授對象許可權，但是該選項只能被授予使用者，而不能授予角色

許可權可以授予使用者，也可以授予角色
回收對象的許可權：對象許可權的回收是級聯回收的,而對象許可權是不進行級聯回收的

系統許可權轉移派發使用 with admin option
對象許可權轉移派發使用 with grant option
conn scott/m123;

oracle有兩大許可權，系統許可權和對象許可權
角色就是相關許可權命令的集合，使用角色的目的就是為了簡化許可權的管理

角色分為預定義角色和自訂角色，預定義角色有25種，
常用的三種預定義角色
（1）connect角色
 含有8種角色
 alter session
 create cluster
 create database link
 create session
 create table
 create view
 create sequence
（2）resource角色
注意：resource角色隱含例如unlimited tablespace系統許可權
 create cluster
 create indextype
 create table
 create sequence
 create type
 create procedure
 create trigger
（3）dba角色
 基本上擁有所有系統許可權，及with admin option
 沒有啟動和關閉資料庫的許可權
 
 自訂角色
 1）建立角色
 （1）建立角色（不驗證）
 啟動資料庫服務，啟動監聽以後，才可以建立資料庫連接
 如果角色是公有角色，沒有設定密碼
 create role myrole1 not identified;
 （2）建立角色（資料庫驗證）
 建立角色設定密碼
 create role 角色名稱 identified by ***;
 
 角色授權
 當建立角色後，角色時沒有任何許可權的，需要對其進行授權才可以的
 
 grant create session to 角色名稱 with admin option;
 grant select on emp to 角色名稱；
 grant update on emp to 角色名稱；
 grant delete on emp to 角色名稱；
 
 1）分配角色
 如何分配自訂角色？？？
 create user along identified by m123;
 grant myrole1 to along;
 
 2)刪除角色
 刪除角色，要使用資料庫系統角色來刪除的
 conn system/manager
 drop role 角色名稱；
 注意：如果角色被刪除，則使用者將沒有該對應的角色
 1.顯示所有角色
 select * from dba_roles;
 2.顯示角色具有的系統許可權
 
 3.顯示角色具有的對象許可權
 
 4.顯示使用者具有的角色，及預設角色
 select granted_role,default_role from dba_role_privs where
 grantee='scott';

精細存取控制：
 是指使用者可以使用函數，策略實現更加細微的安全存取控制