Oracle 常用sql情境應用（未完待續......）

標籤：oracle   常用sql   

--1、建立一張學生資訊表stuinfo，欄位包括學號、姓名、性別、年齡、入學日期

SQL>CREATE TABLE stuinfo (snochar(4),sname varchar2(10),sex int,age int,sdate date);

Table created

 

--2、建立stuinfo表主鍵約束

SQL>ALTER TABLE stuinfo ADD CONSTRAINT pk_stu PRIMARY KEY (SNO);

Table altered

Executed in 0.952 seconds

 

--3、將入學日期增加sysdate預設值，需要注意的是sysdate後必須加not null

SQL>ALTER TABLE stuinfo MODIFY sdate DEFAULT SYSDATE NOT NULL;

Table altered

Executed in 0.546 seconds

 

--4、添加5條類比資料

SQL> insert into stuinfo values(‘001‘,‘lilei‘,‘0‘,‘23‘,default);

SQL> insert into stuinfo values (‘002‘,‘xiaofang‘,‘1‘,‘22‘,to_date(‘2014-09-01‘,‘yyyy-mm-dd‘));

SQL> insert into stuinfo values(‘003‘,‘weiwei‘,‘1‘,‘24‘,to_date(‘2008-08-08‘,‘yyyy-mm-dd‘));

SQL> insert into stuinfo values(‘004‘,‘xiaochao‘,‘0‘,‘27‘,to_date(‘2009-10-08‘,‘yyyy-mm-dd‘));

SQL> insert into stuinfo values(‘005‘,‘tom‘,‘0‘,‘28‘,to_date(‘2007-03-08‘,‘yyyy-mm-dd‘));

 

--5、檢索stuinfo表

SQL> select * from stuinfo;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/4D/7D/wKioL1RR_FLgDNjdAACIeor74xI042.jpg" title="1.jpg" alt="wKioL1RR_FLgDNjdAACIeor74xI042.jpg" />

 

--6、建立一張成績表，列內容包括學號、課程代碼、成績，其中學號是本表的外鍵

SQL>CREATE TABLE score (gnochar(4),class int,score NUMBER(4,2),CONSTRAINT FK_NO FOREIGN KEY (gno)REFERENCES stuinfo(sno));

Table created

 

--7、測試向score表中插入sno在stuinfo表中不存在的記錄

SQL>insert into score values(‘006‘,1212,96.33);

ORA-02291: 違反完整約束條件 (EPM.FK_NO) - 未找到父項關鍵字

 

--8、對於存在外鍵的表，插入或者更新時都需要與父表關聯，所以必須在外鍵處建立索引

SQL>CREATE INDEX inx_score ON score(gno);

Index created

Executed in 0.062 seconds

 

--9、將stuinfo表重新命名為student

SQL>ALTER TABLE stuinfo RENAME TO student;

 

--10、為student表增加檢查——age between 1 and 120

SQL> ALTER TABLE student ADD CONSTRAINTage_check CHECK(age>1 and age<=120);

 

--11、測試向student表插入age是126的資料

SQL> INSERT INTO student VALUES(‘006‘,‘dushuai‘,0,‘126‘,to_date(‘1989-01-05‘,‘yyyy-mm-dd‘));

ORA-02290: 違反檢查約束條件 (EPM.AGE_CHECK)

 

--12、將student表中學號為006的學生年齡更新為26

SQL> UPDATE student SET age=26 WHEREsno=‘006‘;

1 row updated

Executed in 0.078 seconds

 

--13、測試對student進行刪除，檢查能夠正常刪除

SQL>DROP TABLE student;

ORA-02449: 表中的唯一/主鍵被外鍵引用

說明：如果表中的主鍵或唯一值被其他表外鍵引用，要麼先刪除外鍵或者在drop後加cascade constraint 代表進行串聯刪除；

正確SQL>DROP TABLE student CASCADE CONSTRAINT;

 

--14、oracle閃回技術，即從資源回收筒恢複被drop的表

SQL> FLASHBACK TABLE student TO BEFORE DROP;

Done

Executed in 0.296 seconds

 

--15、查詢資源回收筒中的內容

SQL>SELECT r.original_name,r.type,r.droptime FROM recyclebin r;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4D/7D/wKioL1RR_GKihVfiAACM4QMiNjY373.jpg" title="2.jpg" alt="wKioL1RR_GKihVfiAACM4QMiNjY373.jpg" />

 

--16、為表添加整體注釋

SQL>COMMENT ON TABLE student IS ‘學生資訊表‘;

 

--17、為student表中的age列添加註釋

SQL>COMMENT ON COLUMN student.age IS ‘年齡需要大於1歲並小於等於120歲‘;

 

--18、建立一張表，只使用student表中的表結構，而不使用資料

SQL>CREATE TABLE student_temp AS SELECT* FROM student WHERE 1=2;

說明：因為1=2條件不成立，所以只將student表的結構複製給了新表；

      相同道理，如果條件成立為真，則會將表結構和資料都會複製給新表！

      新表只能繼承舊錶的結構和資料，無法繼承主鍵、外鍵、索引等約束；

 

--19、設定表為唯讀模式

SQL>ALTER TABLE student_temp1 READ ONLY;

說明：表設定為唯讀後，無法進行插入、更新等DML操作，但允許刪除表；

SQL>INSERT INTO student_temp1VALUES(‘007‘,‘wangzi‘,‘0‘,‘98‘,TO_DATE(SYSDATE,‘YYYY-MM-DD‘));

ORA-12081: 不允許對錶"EPM"."STUDENT_TEMP1" 進行更新操作

 

SQL> DROP TABLE student_temp1;

Table dropped

Executed in 1.7 seconds

 

--20、將唯讀表恢複成讀寫狀態

SQL> ALTER TABLE student_temp1 READ WRITE;

Table altered

Executed in 0.265 seconds

 

--21、student_temp1表新增備忘列，類型為varchar2（200）

SQL> ALTER TABLE student_temp1 ADD note varchar2(200);

Table altered

Executed in 1.264 seconds

 

--22、將student_temp1表中的note表設定為不使用（unused）

SQL> ALTER TABLE student_temp1 SET UNUSED(note);

Table altered

Executed in 1.794 seconds

說明：使用set unused可以對錶中的列進行標註，同時減少drop column的大量redo

注意set unused後的列資料無法恢複（除非通過資料備份），然後在系統開銷較小的時候，對標記set unused的表列進行刪除操作，格式如下：

SQL> ALTER TABLE student_temp1 DROPUNUSED COLUMNS; 

Table altered 

Executed in 2.247 seconds

本文出自 “oralce學習之路” 部落格，請務必保留此出處http://dushuai.blog.51cto.com/9461011/1569765

Oracle 常用sql情境應用（未完待續......）