ORACLE百例試煉四

Oracle系列《四》：數據庫的設計分析

一、序列的使用 

在很多數據庫系統中都存在一個自動增長的列，如果在Oracle中要完成自動增長的功能，只能依靠序列完成

1、  要有創建序列的權限 create sequence 或 create any sequence

2、  創建序列的語法

CREATE SEQUENCE sequence  //創建序列名稱

       [INCREMENT BY n]  //遞增的序列值是n 如果n是正數就遞增,如果是負數就遞減 默認是1

       [START WITH n]    //開始的值,遞增默認是minvalue 遞減是maxvalue

       [{MAXVALUE n | NOMAXVALUE}] //最大值

       [{MINVALUE n | NOMINVALUE}] //最小值

       [{CYCLE | NOCYCLE}] //循環/不循環

       [{CACHE n | NOCACHE}];//分配并存入到內存中

  NEXTVAL 返回序列中下一個有效的值，任何用戶都可以引用

  CURRVAL 中存放序列的當前值

  NEXTVAL 應在 CURRVAL 之前指定 ，二者應同時有效

//解釋

{

Create 創建

Sequence 序列 seqEmop 序列名稱

Increment by 步長

Stat with 1 開始值

Maxvalue  最大值

Minvalue  最小值

Cycle 循環 nocycle 不循環

Cache 緩存   Cache<maxvalue-minvalue/increment by//一般不采用緩存

Nextvalue 下一個

Currval 當前值

}

//實例應用

//實現id的自動遞增

//第一步

create table cdpt(

id number(6),

name varchar2(30),

constraint pk_id primary key(id)

);

Create sequence seq_cdpt

Increment by 1

Start with 1

Maxvalue 999999

Minvalue 1

Nocycle

nocache

insert into cdpt values(seq_cdpt.nextval,‘feffefe’);

commit;

select * from cdpt;

修改序列的增量, 最大值, 最小值, 循環選項, 或是否裝入內存

alter SEQUENCE sequence  //創建序列名稱

       [INCREMENT BY n]  //遞增的序列值是n 如果n是正數就遞增,如果是負數就遞減 默認是1

       [START WITH n]    //開始的值,遞增默認是minvalue 遞減是maxvalue

       [{MAXVALUE n | NOMAXVALUE}] //最大值

       [{MINVALUE n | NOMINVALUE}] //最小值

       [{CYCLE | NOCYCLE}] //循環/不循環

       [{CACHE n | NOCACHE}];//分配并存入到內存中

修改序列的注意事項：

l   必須是序列的擁有者或對序列有 ALTER 權限

l   只有將來的序列值會被改變

l   改變序列的初始值只能通過刪除序列之后重建序列的方法實現

刪除序列

l   使用DROP SEQUENCE 語句刪除序列

l   刪除之后，序列不能再次被引用

Alter sequence seqEmp maxvalue 5;

Select seqEmp.nextval from dual;

二、同義詞的概念(了解) 

SQL> SELECT SYSDATE FROM dual; 

dual是一張虛擬表，該表在SYS用戶下有定義，可以使用以下語句查詢到 SQL> SELECT * FROM tab WHERE TNAME='DUAL'; 

此表在SYS下，但SCOTT用戶卻可以直接通過表名稱訪問，正常情況下我們是需要使用"用戶名.表名稱" 該情況就是同義詞的作用  

創建同義詞：

CREATE SYSNONYM 同義詞名稱 FOR 用戶名.表名稱';

 例如，將scott.emp 定義 emp 的同義詞 

SQL> CREATE SYSNONYM emp FOR scott.emp;

刪除同義詞 

SQL> DROP SYSNONYM emp; 

同義詞這種特性只適合于Oracle數據庫

三、用戶管理(*) 

創建用戶: CREATE USER 用戶名 IDENTIFIED BY 密碼; 

SQL> CREATE USER test IDENTIFIED BY test123; 

打開一個新的窗口使用test用戶登錄，發現其沒有session權限無法進行登錄，此時要進行授權 

SQL> GRANT CREATE SESSION TO test;

將創建SESSION權限給test用戶，之后該用戶可以正常登錄，但是其沒有創建表的權限

Oracle中可以將多個權限定義成一組角色，分配該角色給用戶即可 

在Oracle中主要提供了兩個角色：CONNECT、RESOURCE，將這兩個角色賦予test用戶 

SQL> GRANT CONNECT,RESOURCE TO test;

管理員對用戶密碼進行修改： 

SQL> ALTER USER test IDENTIFIED BY hello; 

在一般系統中，在用戶進行第一次登錄時可以修改密碼，可以使用如下方式 

ALTER USER 用戶名 PASSWORD EXPIRE; 

SQL> ALTER USER test PASSWORD EXPIRE; 

這時會提示用戶輸入舊口令及新的密碼

鎖住用戶和對用戶解鎖 

SQL> ALTER USER test ACCOUNT LOCK;

SQL> ALTER USER test ACCOUNT UNLOCK;

此時，想查詢SCOTT用戶下的表EMP，發現沒有權限，執行如下命令即可 

SQL> GRANT SELECT,DELETE ON scott.emp TO test;  

收回權限的命令： 

SQL> REVOKE SELECT,DELETE ON scott.emp FROM test;

嵌套表的概念(了解) 

嵌套表：在一個表中還包含另外一個子表 

首先為嵌套表指定類型，該類型需要單獨定義 

SQL> CREATE TYPE project_ty AS OBJECT(  priod NUMBER(4), proname VARCHAR2(30),  prodate DATE );/

該類型創建成后，不意味著此類型能夠直接使用，要為此類型指定一個名稱 

SQL> CREATE TYPE project_nt AS TABLE OF project_ty /

這樣就可以使用project_nt表示project_ty類型，現在根據此類型創建department表 

SQL> CREATE TABLE department(  

  deptno  NUMBER(2) PRIMARY KEY,

  dname  VARCHAR2(50) NOT NULL,

  projects project_nt 

)

NESTED TABLE projects STORE AS project_nt_tab_temp;

如果要進行數據插入的話 

SQL> INSERT INTO department(deptno,dname,projects) 

  VALUES(  1,'tech',  project_nt( 

  project_ty(1001,'ERP',SYSDATE),   

  project_ty(1002,'CRM',SYSDATE)  ) );

查詢部門表，可以返回多個項目 

SQL> SELECT * FROM department;  

如果需要查看一個部門的全部項目的話，查詢嵌套表 

SQL> SELECT * FROM TABLE 

 (SELECT projects FROM department WHERE deptno=1); 

更新編號為1001的項目名稱 

SQL> UPDATE TABLE (SELECT projects FROM department WHERE deptno=1) pro 

SET VALUES(pro)=project_ty('1001','APR',SYSDATE) WHERE pro.proid=1001;  

可變數組的概念：是嵌套表的升級版 ... (有用到再了解)

數據庫范式的概念

第一范式：所有的信息都集中在一張表上，例如 

 CREATE TABLE person( 

 pid  NUMBER(4) PRIMARY KEY,

 name VARCHAR2(50),  

 info VARCHAR(200) );

第一范式會出現問題，例如創建一張學生選課表 

CREATE TABLE selectCourse(  stuno VARCHAR2(50),  

 stuname VARCHAR2(50),  

 stuage NUMBER, 

 cname VARCHAR2(50),  

 grade NUMBER,  

 credit NUMBER );  

以上不僅所有的課程信息冗余了，而且還存在以下的問題： 

1、沒有學生選該門課，那么該門課就消失了 

2、課程本身有編號，按照以上設計，課程編號肯定重復 

3、要更改課程信息，則要修改許多記錄

使用第二范式進行修改 

CREATE TABLE student( 

 stuno  VARCHAR2(10) PRIMARY KEY,  

 stuname VARCHAR2(20),  

 stuage NUMBER );

CREATE TABLE course( 

 cid NUMBER(5) PRIMARY KEY,  

 cname VARCHAR2(20),  

 credit NUMBER );

CREATE TABLE selectCourse(  

stuno VARCHAR2(50),  

cid   NUMBER(5),  

grade NUMBER, 設置主-外鍵關系）； 

以上設計解決了以下問題： 

1、學生不選課，課程不會消失 

2、更新課程的時候直接更新課程表 

3、所有關聯關系在關系表中體現  

這里是完成了多-多關系

使用第三范式： 

按照第二范式的設計一張學生表，包括學號、姓名、年齡、所在院校、學院地址、學院電話等 

會出現一個學生同時在多個學院同時上課，正常應該是：一個學院包含多個學生，一個學生屬于一個學院C 

CREATE TABLE collage( 

 cid NUMBER(40) PRIMARY KEY,  

 cname VARCHAR2(50),  

 caddress VARCHAR2(20),  

 ctel VARCHAR2(20) ); 

CREATE TABLE student( 

 stuno VARCHAR2(50) PRIMARY KEY,  

 stuname VARCHAR2(50),  

 stuage  NUMBER,  

 cid NUMBER(4),  建立主-外鍵關聯 ); 

以上是很明確的1對多的關系