ORACLE百例試煉四

Oracle系列《四》：數(shù)據(jù)庫的設(shè)計(jì)分析

一、序列的使用 

在很多數(shù)據(jù)庫系統(tǒng)中都存在一個(gè)自動(dòng)增長的列，如果在Oracle中要完成自動(dòng)增長的功能，只能依靠序列完成

1、  要有創(chuàng)建序列的權(quán)限 create sequence 或 create any sequence

2、  創(chuàng)建序列的語法

CREATE SEQUENCE sequence  //創(chuàng)建序列名稱

       [INCREMENT BY n]  //遞增的序列值是n 如果n是正數(shù)就遞增,如果是負(fù)數(shù)就遞減 默認(rèn)是1

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

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

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

       [{CYCLE | NOCYCLE}] //循環(huán)/不循環(huán)

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

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

  CURRVAL 中存放序列的當(dāng)前值

  NEXTVAL 應(yīng)在 CURRVAL 之前指定 ，二者應(yīng)同時(shí)有效

//解釋

{

Create 創(chuàng)建

Sequence 序列 seqEmop 序列名稱

Increment by 步長

Stat with 1 開始值

Maxvalue  最大值

Minvalue  最小值

Cycle 循環(huán) nocycle 不循環(huán)

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

Nextvalue 下一個(gè)

Currval 當(dāng)前值

}

//實(shí)例應(yīng)用

//實(shí)現(xiàn)id的自動(dòng)遞增

//第一步

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;

修改序列的增量, 最大值, 最小值, 循環(huán)選項(xiàng), 或是否裝入內(nèi)存

alter SEQUENCE sequence  //創(chuàng)建序列名稱

       [INCREMENT BY n]  //遞增的序列值是n 如果n是正數(shù)就遞增,如果是負(fù)數(shù)就遞減 默認(rèn)是1

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

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

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

       [{CYCLE | NOCYCLE}] //循環(huán)/不循環(huán)

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

修改序列的注意事項(xiàng)：

l   必須是序列的擁有者或?qū)π蛄杏?ALTER 權(quán)限

l   只有將來的序列值會(huì)被改變

l   改變序列的初始值只能通過刪除序列之后重建序列的方法實(shí)現(xiàn)

刪除序列

l   使用DROP SEQUENCE 語句刪除序列

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

Alter sequence seqEmp maxvalue 5;

Select seqEmp.nextval from dual;

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

SQL> SELECT SYSDATE FROM dual; 

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

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

創(chuàng)建同義詞：

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

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

SQL> CREATE SYSNONYM emp FOR scott.emp;

刪除同義詞 

SQL> DROP SYSNONYM emp; 

同義詞這種特性只適合于Oracle數(shù)據(jù)庫

三、用戶管理(*) 

創(chuàng)建用戶: CREATE USER 用戶名 IDENTIFIED BY 密碼; 

SQL> CREATE USER test IDENTIFIED BY test123; 

打開一個(gè)新的窗口使用test用戶登錄，發(fā)現(xiàn)其沒有session權(quán)限無法進(jìn)行登錄，此時(shí)要進(jìn)行授權(quán) 

SQL> GRANT CREATE SESSION TO test;

將創(chuàng)建SESSION權(quán)限給test用戶，之后該用戶可以正常登錄，但是其沒有創(chuàng)建表的權(quán)限

Oracle中可以將多個(gè)權(quán)限定義成一組角色，分配該角色給用戶即可 

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

SQL> GRANT CONNECT,RESOURCE TO test;

管理員對(duì)用戶密碼進(jìn)行修改： 

SQL> ALTER USER test IDENTIFIED BY hello; 

在一般系統(tǒng)中，在用戶進(jìn)行第一次登錄時(shí)可以修改密碼，可以使用如下方式 

ALTER USER 用戶名 PASSWORD EXPIRE; 

SQL> ALTER USER test PASSWORD EXPIRE; 

這時(shí)會(huì)提示用戶輸入舊口令及新的密碼

鎖住用戶和對(duì)用戶解鎖 

SQL> ALTER USER test ACCOUNT LOCK;

SQL> ALTER USER test ACCOUNT UNLOCK;

此時(shí)，想查詢SCOTT用戶下的表EMP，發(fā)現(xiàn)沒有權(quán)限，執(zhí)行如下命令即可 

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

收回權(quán)限的命令： 

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

嵌套表的概念(了解) 

嵌套表：在一個(gè)表中還包含另外一個(gè)子表 

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

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

該類型創(chuàng)建成后，不意味著此類型能夠直接使用，要為此類型指定一個(gè)名稱 

SQL> CREATE TYPE project_nt AS TABLE OF project_ty /

這樣就可以使用project_nt表示project_ty類型，現(xiàn)在根據(jù)此類型創(chuàng)建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;

如果要進(jìn)行數(shù)據(jù)插入的話 

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

  VALUES(  1,'tech',  project_nt( 

  project_ty(1001,'ERP',SYSDATE),   

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

查詢部門表，可以返回多個(gè)項(xiàng)目 

SQL> SELECT * FROM department;  

如果需要查看一個(gè)部門的全部項(xiàng)目的話，查詢嵌套表 

SQL> SELECT * FROM TABLE 

 (SELECT projects FROM department WHERE deptno=1); 

更新編號(hào)為1001的項(xiàng)目名稱 

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

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

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

數(shù)據(jù)庫范式的概念

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

 CREATE TABLE person( 

 pid  NUMBER(4) PRIMARY KEY,

 name VARCHAR2(50),  

 info VARCHAR(200) );

第一范式會(huì)出現(xiàn)問題，例如創(chuàng)建一張學(xué)生選課表 

CREATE TABLE selectCourse(  stuno VARCHAR2(50),  

 stuname VARCHAR2(50),  

 stuage NUMBER, 

 cname VARCHAR2(50),  

 grade NUMBER,  

 credit NUMBER );  

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

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

2、課程本身有編號(hào)，按照以上設(shè)計(jì)，課程編號(hào)肯定重復(fù) 

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

使用第二范式進(jìn)行修改 

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, 設(shè)置主-外鍵關(guān)系）； 

以上設(shè)計(jì)解決了以下問題： 

1、學(xué)生不選課，課程不會(huì)消失 

2、更新課程的時(shí)候直接更新課程表 

3、所有關(guān)聯(lián)關(guān)系在關(guān)系表中體現(xiàn)  

這里是完成了多-多關(guān)系

使用第三范式： 

按照第二范式的設(shè)計(jì)一張學(xué)生表，包括學(xué)號(hào)、姓名、年齡、所在院校、學(xué)院地址、學(xué)院電話等 

會(huì)出現(xiàn)一個(gè)學(xué)生同時(shí)在多個(gè)學(xué)院同時(shí)上課，正常應(yīng)該是：一個(gè)學(xué)院包含多個(gè)學(xué)生，一個(gè)學(xué)生屬于一個(gè)學(xué)院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),  建立主-外鍵關(guān)聯(lián) ); 

以上是很明確的1對(duì)多的關(guān)系