Oracle表管理ppt課件

1、Oracle表管理.主要內(nèi)容Oracle表空間常用的數(shù)據(jù)類型表的創(chuàng)建和刪除數(shù)據(jù)完好性約束對數(shù)據(jù)的CRUD操作.表空間表空間是數(shù)據(jù)庫的邏輯組成部分。從物理上講，數(shù)據(jù)庫數(shù)據(jù)存放在數(shù)據(jù)文件中；從邏輯上講，數(shù)據(jù)庫那么是存放在表空間中，表空間由一個或多個數(shù)據(jù)文件組成。默許表空間是“system .經(jīng)過表空間可以到達以下作用：1. 控制數(shù)據(jù)庫占用的磁盤空間2. dba 可以將不同數(shù)據(jù)類型部署到不同的位置，這樣有利于提高i/o 性能，同時利于備份和恢復(fù)等管理操作。.建立和運用表空間建立：create tablespace data01 datafile d:testdada01.dbf size 20m運用

2、：create table student(sno number(4), sname varchar2(14) tablespace data01;表空間稱號表空間文件名表空間的大小.擴展表空間1. 添加數(shù)據(jù)文件alter tablespace 表空間名 add datafile d:testsp01.dbf size 20m;2. 修正數(shù)據(jù)文件的大小alter database datafile d:testsp01.dbf resize 50m;留意：數(shù)據(jù)文件的大小不要超越500m。3. 設(shè)置文件的自動增長。SQL alter database datafile d:testsp01.db

3、f autoextend on next 10m maxsize 500m;.刪除表空間drop tablespace 表空間名 including contents and datafiles;闡明：including contents 表示刪除表空間時，刪除該空間的一切數(shù)據(jù)庫對象，datafiles 表示將數(shù)據(jù)庫文件也刪除。.1. 知道表空間名，顯示該表空間包括的一切表select * from all_tables where tablespace_name=表空間名;2. 知道表名，查看該表屬于那個表空間select tablespace_name, table_name from u

4、ser_tables where table_name=emp;.此處查的是scott這個用戶表空間下的一切表名select table_name from all_tables where owner=upper(scott) .表名和列的命名規(guī)那么必需以字母開頭長度不能超越30 個字符不能運用oracle 的保管字只能運用如下字符 A-Z，a-z，0-9，$,#等.Oracle常用的數(shù)據(jù)類型字符類char 定長 最大2000 個字符。varchar2/varchar 變長最大4000 個字符.留意：varchar2是oracle本人開發(fā)的，想有向后兼容的才干 ，建議運用varchar2。c

5、lob(character large object) 字符型大對象 最大4G留意：char 查詢的速度極快浪費空間，查詢比較多的數(shù)據(jù)用。varchar2 節(jié)省空間.數(shù)字型number(p,s) 范圍 -10 的38 次方 到 10 的38 次方，可以表示整數(shù)，也可以表示小數(shù)。p和s都為可選number(5,2)，表示一位小數(shù)有5 位有效數(shù)，2 位小數(shù)。范圍：-999.99 到999.99number(5)，表示一個5 位整數(shù)。范圍99999 到-99999.日期類型date 包含年月日和時分秒 oracle 默許格式 1-1 月-1999timestamp 這是oracle9i 對date

6、數(shù)據(jù)類型的擴展?？梢詼?zhǔn)確到毫秒。語法timestamp(n)，n指定秒的小數(shù)位數(shù)，取值范圍09。缺省是。 .圖片blob 二進制數(shù)據(jù) 可以存放圖片/聲音 4G留意： 普通來講，在真實工程中是不會把圖片和聲音真的往數(shù)據(jù)庫里存放，普通存放圖片、視頻的途徑，假設(shè)平安需求比較高的話，那么放入數(shù)據(jù)庫。.創(chuàng)建表實踐操作.修正表添加一個字段ALTER TABLE student add (sex char(2);修正一個字段的長度ALTER TABLE student MODIFY (sex char(5);刪除一個字段 不建議做(刪了之后，順序就變了。加就沒問題，應(yīng)為是加在后面)ALTER TABLE s

7、tudent DROP COLUMN sex;修正表的名字 很少有這種需求RENAME 原表名 TO 新表名;.刪除表DROP TABLE student;.數(shù)據(jù)完好性在oracle 中，數(shù)據(jù)完好性可以運用約束、觸發(fā)器、運用程序過程、函數(shù)三種方法來實現(xiàn)，在這三種方法中，由于約束易于維護，并且具有最好的性能，所以作為維護數(shù)據(jù)完好性的首選。.約束約束用于確保數(shù)據(jù)庫數(shù)據(jù)滿足特定的商業(yè)規(guī)那么。在oracle 中，約束包括：not null、 unique、primary key、 foreign key和check 五種。.建表時添加約束create table customer( customerI

8、d char(8) primary key, -主鍵name varchar2(50) not null, -不為空address varchar2(50), varchar2(50) unique,sex char(2) default 男 check(sex in (男，女)， cardId char(18);表是默許建在SYSTEM 表空間的.建表后添加約束運用alter table 命令為表添加約束。但是要留意：添加not null 約束時，需求運用modify 選項，而添加其它四種約束運用add 選項。1. 添加商品名也不能為空alter table stuInfo modify s

9、tuName not null;2. 添加身份證也不能反復(fù)alter table stuInfo add constraint 約束名 unique(stuName);3. 添加學(xué)生的住址只能是海淀,朝陽,東城,西城,通州,崇文,昌平；alter table stuInfo add constraint 約束名 check (address in (海淀,朝陽,東城,西城,通州,崇文,昌平);.刪除約束當(dāng)不再需求某個約束時，可以刪除。alter table 表名 drop constraint 約束稱號；留意：在刪除主鍵約束的時候，能夠會有錯誤alter table 表名 drop prima

10、ry key；這是由于假設(shè)在兩張表存在主從關(guān)系，那么在刪除主表的主鍵約束時，必需帶上cascade 選項 如像：alter table 表名 drop primary key cascade;.自動標(biāo)識列oracle里面沒有標(biāo)識列！只能添加一個自增的序列，每當(dāng)要用的時候調(diào)用這個序列！創(chuàng)建序列create sequence test-test為序列的稱號start with 1-從1開場increment by 1-每次遞增1運用序列插入數(shù)據(jù)insert into stuInfo values(test.nextval,張三);.向表中添加數(shù)據(jù)oracle 中默許的日期格式dd-mon-yy d

11、d 日子天 mon 月份 yy 2 位的年 09-6 月-99INSERT INTO student VALUES (A001, 張三, 男, 01-5 月-05, 10);運用do_date函數(shù)insert into student values(mark,to_date(08-21-2003,MM-DD-YYYY);修正日期的默許格式暫時修正，數(shù)據(jù)庫重啟后仍為默許；如要修正需求修正注冊表ALTER SESSION SET NLS_DATE_FORMAT =yyyy-mm-dd;.修正表中的數(shù)據(jù)UPDATE student SET sex = 女 WHERE xh = A001;UPDATE

12、 student SET sex = 男, birthday = 1984-04-01 WHERE xh = A001;.刪除表中的數(shù)據(jù)DELETE FROM student;刪除一切記錄，表構(gòu)造還在，寫日志，可以恢復(fù)的，速度慢。savepoint a; -創(chuàng)建保管點DELETE FROM student;rollback to a; -恢復(fù)到保管點一個有閱歷的DBA，在確保完成無誤的情況下要定期創(chuàng)建復(fù)原點。DROP TABLE student; -刪除表的構(gòu)造和數(shù)據(jù)；delete from student WHERE xh = A001; -刪除一條記錄；truncate TABLE stu

13、dent; -刪除表中的一切記錄，表構(gòu)造還在，不寫日志，無法找回刪除的記錄，速度快。.表查詢運用scott用戶中的幾張表作例如emp 雇員表dept部門表salgrade 工資級別.salgrade 工資級別表grade 級別losal 最低工資hisal 最高工資dept 部門表deptno 部門編號Dname 部門稱號loc 部門所在地點emp 雇員表Empno 員工編號Ename 員工姓名Job 任務(wù)mgr 上級的編號hiredate 入職時間sal 月工資comm 獎金deptno 部門.查詢一：SELECT ename, sal, job, deptno FROM emp;SELEC

14、T DISTINCT deptno, job FROM emp;SELECT deptno,job,sal FROM emp WHERE ename = SMITH;留意：oracle 對內(nèi)容的大小寫是區(qū)分的，所以ename=SMITH和ename=smith是不同的.如何處置null 值運用nvl 函數(shù)來處置SELECT sal*13+nvl(comm, 0)*13 年薪 , ename, comm FROM emp;SELECT ename 姓名, sal*12 AS 年收入 FROM emp;如何銜接字符串|SELECT ename | is a | job FROM emp;預(yù)設(shè)的值.

15、問題：如何查找1982.1.1 后入職的員工？SELECT ename,hiredate FROM emp WHERE hiredate 1-1 月-1982;.運用like%：表示0 到多個字符 _：表示恣意單個字符問題：如何顯示首字符為S 的員工姓名和工資？SELECT ename,sal FROM emp WHERE ename like S%;如何顯示第三個字符為大寫O 的一切員工的姓名和工資？SELECT ename,sal FROM emp WHERE ename like _O%;.問題：如何顯示empno 為7844,7839,123,456 的雇員情況？SELECT * FR

16、OM emp WHERE empno in (7844, 7839,123,456);問題：如何顯示沒有上級的雇員的情況？SELECT * FROM emp WHERE mgr is null;.查詢二：運用邏輯操作符號問題：查詢工資高于500 或者是崗位為MANAGER 的雇員，同時還要滿足他們的姓名首字母為大寫的J？SELECT * FROM emp WHERE (sal 500 or job = MANAGER) and ename LIKE J%;.問題：如何按照工資的從低到高的順序顯示雇員的信息？SELECT * FROM emp ORDER by sal;問題：按照部門號升序而雇員

17、的工資降序陳列SELECT * FROM emp ORDER by deptno, sal DESC;問題：按年薪排序select ename, (sal+nvl(comm,0)*12 年薪 from emp order by 年薪 asc;.查詢?nèi)簭?fù)雜查詢數(shù)據(jù)分組 max，min， avg， sum， count問題：如何顯示一切員工中最高工資和最低工資？SELECT MAX(sal),min(sal) FROM emp e;最高工資那個人是誰？select ename, sal from emp where sal=(select max(sal) from emp);.練習(xí)：問題：如何

18、顯示一切員工的平均工資和工資總和？問題：如何計算總共有多少員查詢最高工資員工的名字，任務(wù)崗位顯示工資高于平均工資的員工信息.group by 和 having 子句問題：如何顯示每個部門的平均工資和最高工資？SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno;問題：顯示每個部門的每種崗位的平均工資和最低工資？SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job;問題：顯示平均工資低于2000 的部門號和它的平均工資？SELECT AVG(sal)

19、, MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) all (SELECT sal FROM emp WHERE deptno = 30);大家想想還有沒有別的查詢方法。SELECT ename, sal, deptno FROM emp WHERE sal (SELECT MAX(sal) FROM emp WHERE deptno = 30);執(zhí)行效率上， 函數(shù)高得多All等價于N個And語句 .在多行子查詢中運用any 操作符問題：如何顯示工資比部門30 的恣意一個員工的工資高的員工姓名、工資和部門號？SELECT en

20、ame, sal, deptno FROM emp WHERE sal ANY (SELECT sal FROM emp WHERE deptno = 30);大家想想還有沒有別的查詢方法。SELECT ename, sal, deptno FROM emp WHERE sal (SELECT min(sal) FROM emp WHERE deptno = 30);Any等價于N個or語句 .多列子查詢查詢與SMITH 的部門和崗位完全一樣的一切雇員。a)SELECT deptno, job FROM emp WHERE ename = SMITH;b)SELECT * FROM emp W

21、HERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = SMITH);.1. 查出各個部門的平均工資和部門號SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;2. 把上面的查詢結(jié)果看做是一張子表SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno,AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.dept

22、no AND e.sal ds.mysal;.小總結(jié)：在這里需求闡明的當(dāng)在from 子句中運用子查詢時，該子查詢會被作為一個視圖來對待，因此叫做內(nèi)嵌視圖，當(dāng)在from 子句中運用子查詢時，必需給子查詢指定別名。留意：給表取別名的時候，不能加as；但是給列取別名，是可以加as 的.查詢五：分頁查詢oracle 的分頁一共有三種方式1.根據(jù)rowid 來分select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi orde

23、r by cid desc) where rownum9980) order by cid desc;執(zhí)行時間0.03 秒.2.按分析函數(shù)來分select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk9980;執(zhí)行時間1.01 秒3.按rownum 來分select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum9980;執(zhí)行時間0.1 秒

24、.1 的效率最好，3 次之，2 最差。.select * from (select a1.*, rownum rn from(select ename,job from emp) a1where rownum=5;.下面最主要引見第三種：按rownum 來分1. rownum 分頁SELECT * FROM emp;2. 顯示rownumoracle 分配的SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;注：rn 相當(dāng)于Oracle 分配的行的ID 號3.挑選出610 條記錄 ,先查出1-10 條記錄SELECT e.*, ROWNUM rn

25、FROM (SELECT * FROM emp) e WHERE ROWNUM = 10;4. 然后查出6-10 條記錄SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM = 6;.5. 幾個查詢變化a. 指定查詢列，只需求修正最里層的子查詢只查詢雇員的編號和工資SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM = 6;b. 排序查詢，只需求修正最里層的子查詢工資排序后查詢

26、6-10 條數(shù)據(jù)SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM = 6;.用查詢結(jié)果創(chuàng)建新表CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename,sal, job, deptno FROM emp;創(chuàng)建好之后，desc mytable；和select * from mytable.合并查詢有時在實踐運用中，為了合并多個select 語句的結(jié)果，可以運用集合

27、操作符號union，union all，intersect，minus多用于數(shù)據(jù)量比較大的數(shù)據(jù)局庫，運轉(zhuǎn)速度快。1). union該操作符用于獲得兩個結(jié)果集的并集。當(dāng)運用該操作符時，會自動去掉結(jié)果集中反復(fù)行。SELECT ename, sal, job FROM emp WHERE sal 2500 UNIONSELECT ename, sal, job FROM emp WHERE job = MANAGER;.2).union all該操作符與union 類似，但是它不會取消反復(fù)行，而且不會排序。SELECT ename, sal, job FROM emp WHERE sal 2500U

28、NION ALLSELECT ename, sal, job FROM emp WHERE job = MANAGER;該操作符用于獲得兩個結(jié)果集的并集。當(dāng)運用該操作符時，會自動去掉結(jié)果集中反復(fù)行。3). intersect運用該操作符用于獲得兩個結(jié)果集的交集。SELECT ename, sal, job FROM emp WHERE sal 2500INTERSECTSELECT ename, sal, job FROM emp WHERE job = MANAGER;4). minus運用改操作符用于獲得兩個結(jié)果集的差集，他只會顯示存在第一個集合中，而不存在第二個集合中的數(shù)據(jù)。SELECT

29、 ename, sal, job FROM emp WHERE sal 2500MINUSSELECT ename, sal, job FROM emp WHERE job = MANAGER;MINUS 就是減法的意思.Oracle中常用函數(shù)字符函數(shù) upper(char)：將字符串轉(zhuǎn)化為大寫的格式。length(char)：前往字符串的長度。 substr(char,m,n)：取字符串的子串 n 代表取n 個的意思，不是代表取到第n 個 replace(char1,search_string,replace_string) instr(char1,char2,n,m)取子串在字符串的位置.

30、問題：將一切員工的名字按小寫的方式顯示SQL select lower(ename) from emp;問題：將一切員工的名字按大寫的方式顯示。SQL select upper(ename) from emp;問題：顯示正好為5 個字符的員工的姓名。SQL select * from emp where length(ename)=5;問題：顯示一切員工姓名的前三個字符。SQL select substr(ename,1,3) from emp;問題：以首字母大寫,后面小寫的方式顯示一切員工的姓名。SQL select upper(substr(ename,1,1) |lower(substr

31、(ename,2,length(ename)-1) from emp;問題：以首字母小寫,后面大寫的方式顯示一切員工的姓名。SQL select lower(substr(ename,1,1) |upper(substr(ename,2,length(ename)-1) from emp;問題：顯示一切員工的姓名，用“我是老虎交換一切“ASQL select replace(ename,A, 我是老虎) from emp;.數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)的輸入?yún)?shù)和前往值的數(shù)據(jù)類型都是數(shù)字類型的。數(shù)學(xué)函數(shù)包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，a

32、sin，atan，round，.round(n,m) 該函數(shù)用于執(zhí)行四舍五入，假設(shè)省掉m，那么四舍五入到整數(shù)，假設(shè)m 是正數(shù)，那么四舍五入到小數(shù)點的m 位后。假設(shè)m 是負(fù)數(shù)，那么四舍五入到小數(shù)點的m 位前。 trunc(n,m) 該函數(shù)用于截取數(shù)字。假設(shè)省掉m，就截去小數(shù)部分，假設(shè)m是正數(shù)就截取到小數(shù)點的m 位后，假設(shè)m 是負(fù)數(shù)，那么截取到小數(shù)點的前m 位。 mod(m,n)floor(n) 前往小于或是等于n 的最大整數(shù)ceil(n) 前往大于或是等于n 的最小整數(shù)對數(shù)字的處置，在財務(wù)系統(tǒng)或銀行系統(tǒng)中用的最多，不同的處置方法，對財務(wù)報表有不同的結(jié)果。.問題：顯示在一個月為30 天的情況下，一

33、切員工的日薪金，忽略余數(shù)。SQL select trunc(sal/30), ename from emp;orSQL select floor(sal/30), ename from emp;在做oracle 測試的時候，可以運用dual 表select mod(10,2) from dual;結(jié)果是0select mod(10,3) from dual;結(jié)果是1其它的數(shù)學(xué)函數(shù)，有興趣的同窗可以本人去看看：abs(n)： 前往數(shù)字n 的絕對值select abs(-13) from dual;acos(n)： 前往數(shù)字的反余弦值asin(n)： 前往數(shù)字的反正弦值atan(n)： 前往數(shù)字的

34、反正切值cos(n)：exp(n)： 前往e 的n 次冪log(m,n)： 前往對數(shù)值power(m,n)： 前往m 的n 次冪.日期函數(shù)日期函數(shù)用于處置date 類型的數(shù)據(jù)。默許情況下日期格式是dd-mon-yy 即12-7 月-78(1)sysdate: 該函數(shù)前往系統(tǒng)時間(2)add_months(d,n)(3)last_day(d)：前往指定日期所在月份的最后一天問題：查找曾經(jīng)入職8 個月多的員工SQL select * from emp where sysdate=add_months(hiredate,8);問題：顯示滿10 年效力年限的員工的姓名和受雇日期。Generated b

35、y Foxit PDF Creator Foxit Softwarefoxitsoftware For evaluation only.SQL select ename, hiredate from emp wheresysdate=add_months(hiredate,12*10);問題：對于每個員工，顯示其參與公司的天數(shù)。SQL select floor(sysdate-hiredate) 入職天數(shù),ename from emp;orSQL select trunc(sysdate-hiredate) 入職天數(shù),ename from emp;問題：找出各月倒數(shù)第3 天受雇的一切員工。SQL select hiredate,ename fro