Oracle系列《三》：表、（約束）索引、序列、視圖的使用

專注于為中小企業(yè)提供網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)化隆免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了超過千家企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

一、創(chuàng)建、刪除、修改表

建立表：Oracle中主要數(shù)據(jù)類型 VARCHAR2、NUMBER、DATE、CLOB(大量文本)、BLOB(圖片、音樂、電影)

如果只能復(fù)制一張表的結(jié)構(gòu)，但不復(fù)制內(nèi)容，則加上一個(gè)不可能成立的條件即可，例如

SQL> CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1>2)

例如創(chuàng)建表Person如下：

 CREATE TABLE person(

  pid  VARCHAR2(18),

 name  VARCHAR2(30),

  age  NUMBER(3),

  birthday DATE,

 sex  VARCHAR(2)  DEFAULT 'M'

 );

如果發(fā)現(xiàn)創(chuàng)建表后需要添加特定的列，例如address列，則可以使用ALTER TABLE命令

 SQL> ALTER TABLE person ADD(address VARCHAR2(20));

修改表中的列屬性

 SQL> ALTER TABLE person MODIFY(address VARCHAR2(30));

 在數(shù)據(jù)庫(kù)程序開發(fā)中，很少去修改表結(jié)構(gòu)，在IBM DB2中就沒有提供ALTER TABLE命令

在Oracle中提供RENAME命令對(duì)表進(jìn)行重命名

 SQL> RENAME person to personer;

在Oracle中要清空一張表的數(shù)據(jù)，但又不能回滾，立即釋放資源

SQL> TRUNCATE TABLE personer;

 (與DELETE區(qū)別：前者不能回滾，后者能回滾)

（與DROP TABLE區(qū)別：前者刪除內(nèi)容，后者刪除表）

二、表的約束

約束分類：主鍵約束、唯一約束、檢查約束、非空約束、外鍵約束

語(yǔ)法：CONSTRAINT 約束名 約束類型（字段名）

添加約束如下：

CREATE TABLE person(

 pid  VARCHAR2(18),

 name  VARCHAR2(30) NOT NULL,

 age  NUMBER(3) CHECK(age BETWEEN 0 AND 150),

 birthday DATE,

 sex  VARCHAR(2) DEFAULT 'M' ,

 CONSTRAINT person_pid_pk PRIMARY KEY(pid),

 CONSTRAINT person_name_uk UNIQUE(name),

 CONSTRAINT person_sex_ck CHECK(sex IN('M','F'))

);

以上約束可以采取自動(dòng)命名和手動(dòng)命名

現(xiàn)在要再添加一張表，使用主-外鍵約束

CREATE TABLE book(

 bid   NUMBER PRIMARY KEY,

  bname  VARCHAR(20),

  bprice  NUMBER(5,2),

  pid  VARCHAR2(18),

 CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)

);

這時(shí)候如果要?jiǎng)h除掉person表的話，就會(huì)出現(xiàn)錯(cuò)誤，此時(shí)可以使用強(qiáng)制性的刪除手段

DROP TABLE person CASCADE CONSTRAINT; 但是這種做法一般不用

如果在person 和 book 表中添加記錄，而在person表中進(jìn)行刪除一條記錄時(shí)，假設(shè)該記錄的pid被

book表引用，那么會(huì)出現(xiàn)刪除錯(cuò)誤。如果希望一個(gè)表中的數(shù)據(jù)在刪除時(shí)，能自動(dòng)刪除對(duì)應(yīng)字表的記錄， 可以使用級(jí)聯(lián)刪除的操作

（總結(jié)：主表數(shù)據(jù)被刪除，從表數(shù)據(jù)跟著刪除，在有主外約束的條件下）

CREATE TABLE book(

 bid   NUMBER PRIMARY KEY,

  bname  VARCHAR(20),

 bprice  NUMBER(5,2),

 pid  VARCHAR2(18),

 CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE

);

添加約束語(yǔ)法如下：

ALTER TABLE 表名稱 ADD CONSTRAINT 約束名稱 約束類型(約束字段);

約束類型命名規(guī)則：

PRIMARY KEY:主鍵字段_PK

UNIQUE：字段_UK

CHECK：字段_CK

FOREIGN KEY：父子段_子字段_FK

SQL> ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);

SQL> ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(name);

SQL> ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);

SQL> ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE;

刪除約束語(yǔ)法如下：

ALTER TABLE 表名稱 DROP CONSTRAINT 約束名稱;

 SQL> ALTER TABLE person DROP CONSTRAINT person_age_CK;

 SQL> ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;

案例：

主鍵約束添加刪除

1、創(chuàng)建表的同時(shí)創(chuàng)建主鍵約束

一、無命名 create table accounts ( accounts_number number primary key, accounts_balance number );

二、有命名 create table accounts ( accounts_number , accounts_balance number, constraint yy primary key(accounts_number) );

2、刪除表中已有的主鍵約束

一、無命名 SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME='ACCOUNTS';

找出主鍵名 ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;

二、有命名 ALTER TABLE ACCOUNTS DROP CONTRAINT yy;

3、向表中添加主鍵約束 ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);

******ROWNUM為例*******

SQL> SELECT ROWNUM,empno,ename FROM emp;

ROWNUM采用自動(dòng)編號(hào)的形式出現(xiàn)

假如只想顯示前5條記錄，那么ROWNUM<=5

SQL> SELECT ROWNUM,empno,ename FROM emp  WHERE ROWNUM<=5;

但是如果要查詢5-10條的記錄的話，則查詢不出，只能采用子查詢的方式

SQL> SELECT * FROM (

   SELECT ROWNUM m,empno,ename  FROM emp

   WHERE ROWNUM<=10)

tmp WHERE tmp.m>=5

集合操作：在Oracle中提供了3類集合操作:并、交、差

UNION：將多個(gè)查詢結(jié)果組合到一個(gè)查詢結(jié)果之中，沒有重復(fù)值

UNION ALL：與UNICON不同的是包含重復(fù)值

INTERSECT：返回多個(gè)查詢結(jié)果中相同的部分

MINUS：返回兩個(gè)查詢結(jié)果的差集（不相同的部分）

驗(yàn)證UNION和UNION ALL

SQL> CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; (5條記錄)

SQL> SELECT * FROM emp UNION SELECT * FROM emp20; (14條記錄)

SQL> SELECT * FROM emp UNION ALL SELECT * FROM emp20; (19條記錄)

驗(yàn)證MINUS 和 INTERSECT

SQL> SELECT * FROM emp MINUS SELECT * FROM emp20; (9條記錄)

SQL> SELECT * FROM emp INTERSECT SELECT * FROM emp20; (5條記錄)

Oracle中視圖的操作

1、創(chuàng)建視圖

CREATE VIEW 視圖名稱 AS 子查詢 這條子查詢是非常復(fù)雜的語(yǔ)句

SQL> CREATE VIEW empv20 AS

 SELECT empno,ename,job,hiredate  FROM emp

 WHERE deptno=20;

2、查詢視圖

SQL> SELECT * FROM empv20;

3、刪除視圖

SQL> DROP VIEW empv20;

如果要修改視圖，則要先刪除視圖，在Oracle為了方便用戶修改視圖，提供了一個(gè)替換的命令

CREATE OR REPLACE 視圖名稱 AS 子查詢

視圖可以封裝復(fù)雜的查詢，例如查詢部門名稱，部門的人數(shù)，平均工資以及最低工資的雇員

SQL>  CREATE OR REPLACE VIEW empv20 AS

 SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,(

 SELECT deptno,COUNT(empno) c, AVG(sal) a,MIN(sal) min  FROM emp

 GROUP BY deptno) ed,emp e

WHERE d.deptno=ed.deptno AND e.sal=ed.min;

在開中發(fā)每次都寫這么長(zhǎng)的SQL語(yǔ)句不方便，可以將其建立成視圖，以上紅色部門

4.如果對(duì)視圖進(jìn)行更新操作，在視圖中不應(yīng)該包含真實(shí)數(shù)據(jù)，按以下命令進(jìn)行操作

SQL> UPDATE empv20 SET deptno=30 WHERE empno=7369;

發(fā)現(xiàn)視圖已經(jīng)正常更新，因?yàn)閑mp表中7369編號(hào)已經(jīng)修改為30了，所以在創(chuàng)建視圖是有條件的

SQL提供了兩個(gè)重要的參數(shù)

WITH CHECK OPTION：不能更新視圖的創(chuàng)建條件

SQL> CREATE OR REPLACE VIEW empv20 AS  SELECT * FROM emp WHERE deptno=20  WITH CHECK OPTION;

創(chuàng)建條件不能進(jìn)行更新了，但其他字段仍然可以更新

SQL> UPDATE empv20 SET ename='wilson' WHERE empno=7369;

所以這時(shí)可以使用視圖的第2個(gè)條件：創(chuàng)建只讀視圖

SQL> CREATE OR REPLACE VIEW empv20 AS  SELECT * FROM emp WHERE deptno=20  WITH READ ONLY;

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。

文章題目：ORACLE百例試煉三-創(chuàng)新互聯(lián)
分享URL：http://jinyejixie.com/article48/dijjep.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供標(biāo)簽優(yōu)化、建站公司、移動(dòng)網(wǎng)站建設(shè)、品牌網(wǎng)站建設(shè)、響應(yīng)式網(wǎng)站、網(wǎng)站營(yíng)銷

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)