接著上一篇Oracle入門篇
1數據庫用戶
1.1系統常見用戶
用戶 | 說明 |
sys | 超級用戶,主要用來維護系統信息和管理實例,以SYSDBA或SYSOPER角色登錄。密碼為在安裝時設置的管理口令,如一般設置為:orcl |
system | 默認的系統管理員,擁有DBA權限,通常用來管理Oracle數據庫的用戶、權限和存儲,以Normal方式登錄。密碼為在安裝時設置的管理口令,如一般設置為:orcl |
scott | 示範用戶,使用users表空間。一般該用戶默認密碼為tiger |
1.2用戶管理
Oracle中有個模式(schema)的概念,它是用戶的所有數據庫對象的集合;一般在創建用戶的同時會自動創建一個這樣的模式,名稱和用戶名稱一樣。
1.2.1查詢系統用戶
select * from all_users; 或 select * from dba_users; --更詳細的用戶信息 |
1.2.2解鎖用戶
【語法】 ALTER USER 用戶名 ACCOUNT UNLOCK; 【示例】解鎖hr用戶 alter user hr account unlock; |
1.2.3創建用戶
【語法】 CREATE USER 用戶名 IDENTIFIED BY 密碼 DEFAULT TABLESPACE 表空間; 【示例】 CREATE USER itcast IDENTIFIED BY itcast DEFAULT TABLESPACE itcast_ts TEMPORARY TABLESPACE temp; |
1.2.4修改用戶密碼
【語法】 ALTER USER 用戶名 identified by 密碼 【示例】 ALTER USER itcast identified by it; |
1.2.5刪除用戶
【語法】 DROP USER 用戶名 CASCADE; 【示例】 DROP USER itcast CASCADE; |
2DCL數據控制語言
2.1授予
【語法1】 GRANT 角色權限(角色)[,角色權限] TO 用戶; 【示例1】 --授予CONNECT和RESOURCE兩個角色 GRANT connect, resource TO itcast; 【備註】使用如下語句可以查看resource角色下的權限 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE' 【語法2】 GRANT 操作 ON 模式.對象 TO 用戶; 【示例2】 --允許用戶查看、更新 EMP 表中的記錄 GRANT select,update ON SCOTT.emp TO itcast; --查看當前用戶的系統權限 select * from user_sys_privs; --查看當前用戶的對象權限 select * from user_tab_privs; --查看當前用戶的所有角色 select * from user_role_privs; |
2.2撤銷
【語法1】 REVOKE 角色權限(角色)[,角色權限] FROM 用戶; 【示例1】 --撤銷CONNECT和RESOURCE兩個角色 REVOKE connect, resource FROM itcast; 【語法2】 REVOKE 操作 ON 模式.對象 FROM 用戶; 【示例2】 --撤銷用戶查看、更新 EMP 表中的記錄的操作 REVOKE select,update ON SCOTT.emp FROM itcast; |
3DDL數據定義語言
3.1創建表
【語法】 CREATE TABLE <table_name>( column1 DATATYPE [NOT NULL] [PRIMARY KEY], column2 DATATYPE [NOT NULL], ... [constraint <約束名> 約束類型 (要約束的字段) ... ] ); 【說明】 DATATYPE --是Oracle的數據類型 NUT NULL --可不可以允許資料有空的(尚未有資料填入) PRIMARY KEY --是本表的主鍵 constraint --是對錶裡的字段添加約束.(約束類型有 Check,Unique,Primary key,not null,Foreign key); 【示例】 create table t_student( s_id number(8) PRIMARY KEY, s_name varchar2(20) not null, s_sex varchar2(8), clsid number(8), constraint u_1 unique(s_name), constraint c_1 check (s_sex in ('MALE','FEMALE')) ); --從現有的表創建表及複製其數據 【語法】 CREATE TABLE <table_name> as <SELECT 語句> 【示例】 create table emp as select * from scott.emp; create table emp as select empno,ename from scott.emp --表結構只有empno和ename兩個字段及該兩字段對應的數據 --如果只複製表的結構不復製表的數據則: create table emp as select * from scott.emp where 1=2; |
3.2修改表
【語法1】向表中添加新字段 ALTER TABLE <table_name> ADD (字段1 類型 [NOT NULL], 字段2 類型 [NOT NULL] ... ); 【示例1】 alter table t_student add (s_age number(3),s_address varchar2(20)); 【語法2】修改表中字段 ALTER TABLE <table_name> MODIFY(字段1 類型,字段2 類型 ... ); 【示例2】 alter table t_student modify(s_name varchar2(50),s_address varchar2(100)); 【語法3】刪除表中字段 ALTER TABLE <table_name> DROP(字段1,字段2... ); 【示例3】 alter table t_student drop(s_age,s_address); 【語法4】修改表字段名稱 ALTER TABLE <table_name> RENAME COLUMN 原字段名稱 TO 新字段名稱; 【示例4】 alter table t_student rename column s_id to s_no; |
3.3刪除表
【語法1】 --刪除表結構及數據(刪除後可在回收站查看並恢復) DROP TABLE <table_name>; --刪除表結構及數據(刪除後不可在回收站查看並恢復) DROP TABLE <table_name> PURGE; 【示例1】 drop table t_student; |
3.4回收站
3.4.1查看回收站
--查看回收站 show recyclebin; 或 select * from recyclebin; |
3.4.2清空回收站
--清空回收站 purge recyclebin; |
3.5oracle數據類型
數據類型 | 描述 | |
VARCHAR2(size) | 可變長度的字符串,其最大長度為size個字節;size的最大值是4000,而最小值是1;你必須指定一個VARCHAR2的size; | |
NVARCHAR2(size) | 可變長度的字符串,依據所選的國家字符集,其最大長度為size個字符或字節;size的最大值取決於儲存每個字符所需的字節數,其上限為4000;你必須指定一個NVARCHAR2的size; | |
NUMBER(p,s) | 精度為p並且數值範圍為s的數值;精度p的範圍從1到38;數值範圍s的範圍是從-84到127; 例如:NUMBER(5,2) 表示整數部分最大3位,小數部分為2位;NUMBER(5,-2) 表示數的整數部分最大為7其中對整數的倒數2位為0,前面的取整。NUMBER 表示使用默認值,即等同於NUMBER(5); | |
LONG | 可變長度的字符數據,其長度可達2G個字節; | |
DATE | 有效日期範圍從公元前4712年1月1日到公元后9999年12月31日 | |
RAW(size) | 長度為size字節的原始二進制數據,size的最大值為2000字節;你必須為RAW指定一個size; | |
LONG RAW | 可變長度的原始二進制數據,其最長可達2G字節; | |
CHAR(size) | 固定長度的字符數據,其長度為size個字節;size的最大值是2000字節,而最小值和默認值是1; | |
NCHAR(size) | 也是固定長度。根據Unicode標準定義 | |
CLOB | 一個字符大型對象,可容納單字節的字符;不支持寬度不等的字符集;最大為4G字節 | |
NCLOB | 一個字符大型對象,可容納單字節的字符;不支持寬度不等的字符集;最大為4G字節;儲存國家字符集 | |
BLOB | 一個二進制大型對象;最大4G字節 | |
BFILE | 包含一個大型二進制文件的定位器,其儲存在數據庫的外面;使得可以以字符流I/O訪問存在數據庫服務器上的外部LOB;最大大小為4G字節. |
4DML數據操作語言
4.1新增
【語法1】 INSERT INTO table_name (column1,column2,...) VALUES ( value1,value2, ...); 【示例1】 insert into emp (empno,ename) values(1111,'itcast'); 【語法2】 INSERT INTO <table_name> <SELECT 語句>; 【示例2】 create table t1 as select * from emp where 1=2; insert into t1 select * from emp where sal>2000; |
4.2修改
【語法1】 UPDATE table_name SET column1=new value,column2=new value,... WHERE <條件>; 【示例1】 update emp set sal=3000 where ename='itcast'; |
4.3查詢
4.3.1偽表dual
DUAL是一個虛擬表,用來構成select的語法規則,oracle保證dual裡面永遠只有一條記錄。以用它來做很多事情,如:
1.查看當前用戶
select user from dual; |
2.用來調用系統函數
--查詢系統的當前時間並格式化 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; |
3.得到序列的下一個值或當前值
--獲得序列seq的下一個值 select seq.nextval from dual; --獲得序列seq的當前值 select seq.currval from dual; |
4.可以用做計算器
select 2*8 from dual; |
4.3.2偽列rowid
rowid是物理結構上的,在每條記錄insert到數據庫中時,都會有一個唯一的物理記錄,同一條記錄在不同查詢中對應的rowid相同。
【用法】 SELECT ROWID,字段名... FROM 表名; 【示例】 select rowid, emp.* from emp; |
4.3.3偽列rownum
rownum是根據sql查詢出的結果給每行分配一個邏輯編號;每次的查詢都會有不同的編號。編號從1開始。
【用法】 SELECT ROWNUM,字段名... FROM 表名; 【注意】 ROWNUM 不能使用大於號“>” 即 select rownum, emp.* from emp where rownum > 2 是不對的,沒有任何結果 【示例】 select rownum, emp.* from emp; /* 關於分頁:由於不能使用>,所以為了達到分頁目的得如下執行;如獲取第2頁數據(每頁3條)*/ select * from (select rownum r,emp.* from emp where rownum < 7) where r > 3; /* 關於排序:由於rownum是查詢結果的行編號,排序後這個編號便有可能被打亂,如果需要該編號和排序的結果列表序號保持一致可以如下執行*/ select rownum,t.* from (select empno,ename from emp order by empno desc) t; |
4.3.4連接查詢
準備查詢數據,將scott用戶下的dept表複製到itcast用戶下。
使用sys用戶登錄系統;替itcast用戶創建dept表,表結構和數據來自scott.dept。 --執行語句如下 create table itcast.dept as select * from scott.dept; |
1、 等值查詢
--查詢emp表中各用戶對應的部門名稱 select empno,ename,dname from emp,dept where emp.deptno=dept.deptno; --練習:按部門統計員工的人數,要求顯示部門號、部門名稱、和部門人數 select d.deptno,d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno group by d.deptno,d.dname; |
2、左外/右外連接查詢:左外連接是在等號左邊的集合,無論條件是否成立均在結果集合,寫法就是在等號右邊使用(+),這個寫法是oracle專用的,如果需要全數據庫類型通用應該使用left join)
--按部門統計員工的人數,要求顯示部門號、部門名稱、和部門人數,部門下沒有人的也將顯示 select d.deptno,d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname; --上述語句的通用數據庫寫法(left join方式) select d.deptno,d.dname,count(e.empno) from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname; |
3、自連接查詢:查詢的2張表是同一張表,一般是該表的字段之間存在上下級關係
--查詢員工和老闆的上下級關係 select e.ename || ' 的老闆是: '|| b.ename from emp e,emp b where e.mgr=b.empno; 【注意】上述查詢語句中的||表示為字符的連接 |
4.3.5組合查詢
1、 計算部門工資總和,最高工資,最低工資
select deptno,sum(sal),max(sal),min(sal) from emp group by deptno; |
2、部門平均工資
--查詢部門的平均工資 select deptno,avg(sal) from emp group by deptno; --查詢平均工資大於2000的部門,並按照平均工資降序排序 select deptno,avg(sal) 平均工資 from emp group by deptno having avg(sal)>2000 order by 平均工資 desc ; --查詢除了20部門以外,平均工資大於2000的部門 select deptno,avg(sal) from emp where deptno <> 20 group by deptno having avg(sal)>2000; 【注意】SQL語句中的各子句執行順序: from->where->group by->having->select->order by |
3、 子查詢:將子查詢放入括號中;group by後不能使用子查詢;select、from、where後面都可以使用子查詢;可以將子查詢看作一張新表
--select後面的子查詢 select (select dname from dept where deptno=10),ename from emp where deptno=10; --from後面的子查詢 select * from (select ename,sal from emp); --將子查詢視為一個表 select e.ename,e.sal from (select ename,sal from emp) e; --where後面的子查詢;查詢工資比10號部門員工中任意一個員工的工資低的員工信息 select * from emp where sal < (select min(sal) from emp where deptno=10); |
4、其它查詢
--查詢姓名是5個字符的員工,且第二個字符是C,使用_只匹配一個字符並且不能標識0或多個字符 select * from emp where ename like '_C___'; --查詢員工姓名中含有‘_’的員工,使用\轉義字符 select * from emp where ename like '%\_%' escape '\'; |
4.4刪除
--根據條件刪除表數據 delete from emp where empno=0000 --清空表數據(表還在),不寫日誌,省資源,效率高,屬於數據定義語言 --先創建要清空數據的表 create table myemp as select * from emp; --清空表數據 truncate table myemp; |
5TCL事務控制語言
5.1提交
事務的提交比較簡單;直接在執行DML語句後進行提交即可,如果不提交事務則剛剛通過DML語句進行修改的內容還未保存到數據庫中,只在當前用戶的連接會話中有效。要永久變更數據需要顯示地執行提交、回滾或者退出當前回話(如退出sqlplus)。
提交的命令為:commit;
5.2保存點與回滾
保存點savepoint一般與回滾rollback配合使用。在設置了savepoint後事務的粒度可以控制的更加細化,可以回滾到特定的保存點。
【語法】保存點savepoint SAVEPOINT <savepoint_name>; 【示例】 --創建一個保存點,名稱為a savepoint a; 【注意】當創建保存點之後執行的DML操作,可以進行回滾,而保存點之前未提交的DML操作不受影響。 【語法】回滾 ROLLBACK [TO savepoint]; 【示例】 --回滾到保存點a,即在保存點a之後的所有未提交的DML都無效。 rollback to a; /*保存點與回滾完整示例*/ --1、創建保存點a savepoint a; --2、插入emp數據 it1 insert into emp(empno,ename) values(1234,'it1'); --3、創建保存點b savepoint b; --4、插入emp數據 it2 insert into emp(empno,ename) values(1235,'it2'); --5、查看emp表數據,存在it1、it2兩條數據 select ename from emp; --6、回滾到保存點b,即it2數據將消失 rollback to b; --7、查看emp表數據,存在it1的數據,it2已不在 select ename from emp; --8、提交數據 commit; --9、查看emp表數據,存在it1的數據 select ename from emp; --10、回滾到保存點a,將報錯保存點不存在的錯誤信息 rollback to a; |
6運算符
6.1算術運算符
+、-、*、/
6.2比較(關係)運算符
=、!=、<>、< 、 > 、 <= 、 >= 、 between...and... 、in 、like
、is null
6.3邏輯運算符
AND(邏輯與),表示兩個條件必須同時滿足
OR(邏輯或),表示兩個條件中有一個條件滿足即可
NOT(邏輯非),返回與某條件相反的結果
6.4連接運算符
||
【示例】 select '工號為:' || empno || ' 的員工的姓名為:'|| ename from emp; |
6.5集合運算符
union(並集無重複)
union all(並集有重複)
intersect(交集,共有部分)
minus(減集,第一個查詢具有,第二個查詢不具有的數據)
【注意】:列數相關,對應列的數據類型兼容,不能含有Long類型的列,第一個select語句的列或別名作為結果標題
--union(並集將去重複) select * from emp where deptno=10 union select * from emp where deptno=20; --intersect(交集) 查詢工資即屬於1000~2000區間和1500~2500區間的工資 select ename,sal from emp where sal between 1000 and 2000 intersect select ename,sal from emp where sal between 1500 and 2500; --minus(減集) select ename,sal from emp where sal between 1000 and 2000 minus select ename,sal from emp where sal between 1500 and 2500; |
6.6運算符優先級
優先級 | 運算符 |
1 | 算術運算符 |
2 | 連接符 |
3 | 比較符 |
4 | IS[NOT]NULL, LIKE, [NOT]IN |
5 | [NOT] BETWEEN |
6 | NOT |
7 | AND |
8 | OR |
可以使用括號改變優先級順序;OR的優先級最低,算術運算符的優先級最高。
7常用函數
7.1數值型函數
round(x[,y])
【功能】返回四捨五入後的值 【參數】x,y,數字型表達式,如果y不為整數則截取y整數部分,如果y>0則四捨五入為y位小數,如果y小於0則四捨五入到小數點向左第y位。 【返回】數字 【示例】 select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual; 返回: 5555.67 , 5600 , 5556 |
trunc(x[,y])
【功能】返回x按精度y截取後的值 【參數】x,y,數字型表達式,如果y不為整數則截取y整數部分,如果y>0則截取到y位小數,如果y小於0則截取到小數點向左第y位,小數前其它數據用0表示。 【返回】數字 【示例】 select trunc(5555.66666,2.1), trunc(5555.66666,-2.6),trunc(5555.033333)from dual; 返回:5555.66 5500 5555 |
7.2字符型函數
LENGTH(c1)
【功能】返回字符串的長度; 【說明】多字節符(漢字、全角符等),按1個字符計算 【參數】C1 字符串 【返回】數值型 【示例】 select length('半城雨落'),length('itcast半城雨落') from dual; LENGTH('半城雨落') LENGTH('ITCAST半城雨落') ------------------ ------------------------ 4 10 |
LPAD(c1,n[,c2])、RPAD(c1,n[,c2])
【功能】在字符串c1的左(右)邊用字符串c2填充,直到長度為n時為止 【說明】如果c1長度大於n,則返回c1左邊n個字符 【參數】C1 字符串 n 追加後字符總長度 c2 追加字符串,默認為空格 【返回】字符型 【示例】 select lpad('itcast',10,'*'),rpad('itcast',10,'*') from dual; |
REPLACE(c1,c2[,c3])
【功能】將字符表達式值中,部分相同字符串,替換成新的字符串 【參數】 c1 希望被替換的字符或變量 c2 被替換的字符串 c3 要替換的字符串,默認為空(即刪除之意,不是空格) 【返回】字符型 【示例】 select replace('he love you','he','i') from dual; |
SUBSTR(c1,n1[,n2])
【功能】取子字符串 【說明】多字節符(漢字、全角符等),按1個字符計算 【參數】在字符表達式c1裡,從n1開始取n2個字符;若不指定n2,則從第n1個字符直到結束的字串. 【返回】字符型 【示例】 select substr('123456789',4,4),substr('123456789',3) from dual; |
7.3日期函數
sysdate
【功能】:返回當前日期。 【參數】:沒有參數,沒有括號 【返回】:日期 【示例】select sysdate from dual; |
add_months(d1,n1)
【功能】:返回在日期d1基礎上再加n1個月後新的日期。 【參數】:d1,日期型,n1數字型 【返回】:日期 【示例】select sysdate,add_months(sysdate,3) from dual; |
months_between(d1,d2)
【功能】:返回日期d1到日期d2之間的月數。 【參數】:d1,d2 日期型 【返回】:數字 如果d1>d2,則返回正數 如果d1<d2,則返回負數 【示例】 select sysdate, months_between(sysdate,to_date('2015-01-01','YYYY-MM-DD')) 距2015元旦, months_between(sysdate,to_date('2016-01-01','YYYY-MM-DD')) 距2016元旦 from dual; |
extract(c1 from d1)
【功能】:日期/時間d1中,參數(c1)的值 【參數】:d1日期型(date)/日期時間型(timestamp),c1為字符型(參數) 【參數表】:c1對應的參數表詳見示例 【返回】:字符 【示例】 select extract(YEAR from timestamp '2015-5-1 12:26:18 ' ) 年, extract(MONTH from timestamp '2015-5-1 12:26:18 ' ) 月, extract(DAY from timestamp '2015-1-5 12:26:18 ' ) 日, extract(hour from timestamp '2015-5-1 12:26:18 ' ) 小時, extract(minute from timestamp '2015-5-1 12:26:18' ) 分鐘, extract(second from timestamp '2015-5-1 12:26:18 ' ) 秒 from dual; select extract (YEAR from date '2015-5-1' ) from dual; select sysdate 當前日期, extract(YEAR from sysdate ) 年, extract(MONTH from sysdate ) 月, extract(DAY from sysdate ) 日 from dual; --如下語句也可獲取年份、月份等 select to_number(to_char(sysdate,'yyyy')) from dual; |
7.4轉換函數
TO_CHAR(x[[,c2],C3])
【功能】將日期或數據轉換為char數據類型 【參數】 x是一個date或number數據類型。 c2為格式參數 c3為NLS設置參數 【返回】varchar2字符型 【示例】 select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM dual; select to_char(1210.7, '$9,999.00') FROM dual; |
TO_DATE(X[,c2[,c3]])
【功能】將字符串X轉化為日期型 【參數】c2,c3,字符型,參照to_char() 【返回】字符串 如果x格式為日期型(date)格式時,則相同表達:date x 如果x格式為日期時間型(timestamp)格式時,則相同表達:timestamp x 【示例】 select to_date('201212','yyyymm'), to_date('2012.12.20','yyyy.mm.dd'), (date '2012-12-20') XXdate, to_date('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'), to_timestamp('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'), (timestamp '2012-12-20 12:31:30') XXtimestamp from dual; |
TO_NUMBER(X[[,c2],c3])
【功能】將字符串X轉化為數字型 【參數】c2,c3,字符型 【返回】數字串 【示例】 select TO_NUMBER('201212') + 3,TO_NUMBER('450.05') + 1 from dual; --等同上述結果 select '201212' + 3 from dual; |
7.5聚合函數
sum:求和
avg:求平均數
count:計數
max:求最大值
min:求最小值
7.6分析函數
分析函數中瞭解rank()/dense_rank()/row_number()的使用:
--查詢部門的員工工種情況,並在部門內重新進行排序;PARTITION BY類似group by,根據ORDER BY排序字段的值重新由1開始排序。 --RANK 使用相同排序排名一樣,後繼數據空出排名;即有2個排序為1的,那麼接下來的排序號則為3 select deptno,ename,job,rank() over(partition by deptno order by job) as myRank from emp e; --DENSE_RANK使用,使用相同排序排名一樣,後繼數據不空出排名;即有2個排序為1的,那麼接下來的排序號則為2 select deptno,ename,job,dense_rank() over(partition by deptno order by job) as myDenseRank from emp e; --ROW_NUMBER使用,不管排名是否一樣,都按順序排名;即有2個排序為1的,那麼排序號不會重現重複 select deptno,ename,job,row_number() over(partition by deptno order by job) as myRowNumber from emp e; |
7.7其它函數
NVL()/NVL2()
【語法】NVL (expr1, expr2) 【功能】若expr1為NULL,返回expr2;expr1不為NULL,返回expr1。注意兩者的類型要一致 【示例】將員工的獎金如果是空的話則設置為0 select ename,sal,comm,nvl(comm,0) from emp; 【語法】NVL2 (expr1, expr2, expr3) 【功能】expr1不為NULL,返回expr2;expr2為NULL,返回expr3。 expr2和expr3類型不同的話,expr3會轉換為expr2的類型 【示例】 select ename,job,nvl2(job,'job有值','job無值') from emp; |
decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值)
【功能】根據條件返回相應值 【參數】c1, c2, ...,cn,字符型/數值型/日期型,必須類型相同或null 注:值1……n 不能為條件表達式,這種情況只能用case when then end解決 含義解釋: decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值) 該函數的含義如下: IF 條件=值1 THEN RETURN(翻譯值1) ELSIF 條件=值2 THEN RETURN(翻譯值2) ...... ELSIF 條件=值n THEN RETURN(翻譯值n) ELSE RETURN(缺省值) END IF 【示例】根據員工的部門號,條件判斷找到對應的部門名稱 select ename,deptno,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES','無部門') from emp; |
8視圖
8.1視圖簡介
視圖是由一個或者多個表組成的虛擬表;那些用於產生視圖的表叫做該視圖的基表。視圖不佔用物理空間,這個也是相對概念,因為視圖本身的定義語句還是要存儲在數據字典裡的。視圖只有邏輯定義。每次使用的時候只是重新執行SQL。一個視圖也可以從另一個視圖中產生。視圖沒有存儲真正的數據,真正的數據還是存儲在基表中。一般出於對基本的安全性和常用的查詢語句會建立視圖;並一般情況下不對視圖進行新增、更新操作。
【語法】
--創建視圖 CREATE [OR REPLACE] VIEW <view_name> AS <SELECT 語句>; --刪除視圖 DROP VIEW <view_name> ; |
8.2視圖操作
-- 授予itcast用戶 創建視圖 的權限 grant create view to itcast; -- 登錄itcast,創建視圖 create or replace view v_emp as select empno,ename from emp; --通過視圖查詢數據 select * from v_emp; --通過視圖添加數據,需要保證基表的其它數據項可以為空 insert into v_emp(empno,ename) values(3333,'itcast3'); --通過視圖修改數據 update v_emp set ename='半城雨落3' where empno=3333; --通過視圖刪除數據 delete from v_emp where empno=3333; --基於多個基表的視圖,不建議使用視圖進行增刪改操作 create or replace view v_dept_emp as select dept.deptno,dept.dname,ename from emp inner join dept on emp.deptno=dept.deptno; --查詢多個基表的視圖 select * from v_dept_emp; --創建基於視圖的視圖 create or replace view vv_emp as select ename from v_emp; --查詢基於視圖的視圖 select * from vv_emp; --刪除視圖 drop view v_emp; drop view v_dept_emp; drop view vv_emp; |
9同義詞
同義詞是數據庫模式對象的一個別名,經常用於簡化對象訪問和提高對象訪問的安全性。在使用同義詞時,Oracle數據庫將它翻譯成對應模式對象的名字。與視圖類似,同義詞並不佔用實際存儲空間,只有在數據字典中保存了同義詞的定義。在Oracle數據庫中的大部分數據庫對象,如表、視圖、同義詞、序列、存儲過程等,數據庫管理員都可以根據實際情況為他們定義同義詞。隱藏對象名稱和所有者。
9.1私有同義詞
私有Oracle同義詞由創建它的用戶所有;創建的用戶需要具有CREATE SYNONYM權限。
【語法】 CREATE SYNONYM <synonym_name> for <tablename/viewname...> 【示例】 --管理員 授權用戶itcast創建同義詞的權限 grant create synonym to itcast; --創建私有同義詞 create synonym syn_emp for emp; create synonym syn_v_emp for v_emp;--為視圖v_emp創建私有同義詞(別名) --使用私有同義詞 select empno,ename from syn_emp; update syn_emp set ename='itcast5' where empno='1234'; --刪除同義詞 drop synonym syn_emp; |
9.2公有同義詞
公有Oracle同義詞由一個特殊的用戶組Public所擁有。顧名思義,數據庫中所有的用戶都可以使用公有同義詞。公有同義詞往往用來標示一些比較普通的數據庫對象,這些對象常需要引用。公有同義詞一般由管理員用戶創建及刪除,普通用戶需要創建及刪除需要create public synonym和drop public synonym權限。
【語法】 CREATE PUBLIC SYNONYM <synonym_name> for <tablename/viewname...> --登陸sys管理員用戶,授權用戶itcast創建、刪除(公有的刪除權限需要特別給定)公有同義詞權限 grant create public synonym,drop public synonym to itcast; --revoke create public synonym,drop public synonym from itcast; --登陸itcast用戶創建公有同義詞 conn itcast/itcast; create public synonym syn_public_emp for emp; --使用公有同義詞 select * from syn_public_emp; -- 登錄system管理員 conn system/orcl; 創建itcast2並授權 --create user itcast2 identified by itcast2 default tablespace itcast_ts; --grant connect,resource to itcast2; --為其它用戶itcast2授權使用公有同義詞(需要給予使用表的權限) grant select,update on itcast.emp to itcast2; --revoke select,update on itcast.emp from itcast2; --登陸itcast2用戶下使用公有同義詞syn_public_emp select * from syn_public_emp; update syn_public_emp set ename='半城雨落5' where empno=5555; --刪除同義詞 --登陸itcast,刪除公有同義詞 drop public synonym syn_public_emp; |
10索引
索引是建立在數據庫表中的某些列的上面,是與表關聯的,可提供快速訪問數據方式,但會影響增刪改的效率;常用類型(按邏輯分類):單列索引和組合索引、唯一索引和非唯一索引。
什麼時候要創建索引
(1)在經常需要搜索、主鍵、連接的列上
(2)表很大,記錄內容分佈範圍很廣
(3)在經常需要根據範圍進行搜索的列上創建索引,因為索引已經排序,其指定的範圍是連續的
(4)在經常使用在WHERE子句中的列上面創建索引
什麼時候不要創建索引
(1)表經常進行 INSERT/UPDATE/DELETE 操作
(2)表很小(記錄超少)
(3)列名不經常作為連接條件或出現在 WHERE 子句中
(4)對於那些定義為text, image和bit數據類型的列不應該增加索引
10.1創建索引
【語法】 CREATE [UNIQUE] INDEX <index_name> ON <table_name>(字段 [ASC|DESC]); 【說明】 UNIQUE --確保所有的索引列中的值都是可以區分的。 [ASC|DESC] --在列上按指定排序創建索引。 (創建索引的準則: 1.如果表裡有幾百行記錄則可以對其創建索引(表裡的記錄行數越多索引的效果就越明顯)。 2.不要試圖對錶創建兩個或三個以上的索引。 3.為頻繁使用的行創建索引。) 【示例】 --創建單列唯一索引,表中的列值將不允許重複 create unique index index_emp_empno on emp(empno); --創建單列非唯一索引 create index index_emp_ename on emp(ename); --創建組合列、唯一索引 create unique index index_emp_ename_job on emp(ename,job); --創建組合列、非唯一索引 create index index_emp_job_sal on emp(job,sal); |
10.2刪除索引
【語法】 DROP INDEX <index_name>; 【示例】 --刪除索引 drop index index_emp_empno; drop index index_emp_ename; drop index index_emp_ename_job; drop index index_emp_job_sal; |
11序列
序列是oracle提供的一個產生唯一數值型值的機制。通常用於表的主健值,序列只能保證唯一,不能保證連續。
11.1創建序列
【語法】 CREATE SEQUENCE <sequencen_name> [INCREMENT BY n] [START WITH n] [MAXVALUE n][MINVALUE n] [CYCLE|NOCYCLE] [CACHE n|NOCACHE]; INCREMENT BY n --表示序列每次增長的幅度;默認值為1. START WITH n --表示序列開始時的序列號。默認值為1. MAXVALUE n --表示序列可以生成的最大值(升序). MINVALUE n --表示序列可以生成的最小值(降序). CYCLE --表示序列到達最大值後,在重新開始生成序列.默認值為 NOCYCLE。 CACHE n--允許更快的生成序列.預先生成n個序列值到內存(如果沒有使用完,那下次序列的值從內存最大值之後開始;所以n不應該設置太大) 【示例】 --創建遞增序列 create sequence seq_test increment by 1 start with 1 maxvalue 1000 nocycle; --創建遞減序列 create sequence seq_test2 increment by -1 start with 5 maxvalue 5 minvalue 1 nocycle; |
11.2序列使用
1、NEXTVAL 返回序列下一個值;第一次訪問時,返回序列的初始值,後繼每次調用時,按步長增加的值返回 【語法】 select <sequence_name>.nextval from dual; 【示例】 select seq_test.nextval from dual; 2、CURRVAL 返回序列的當前值.注意在剛建立序列後,序列的CURRVAL值為NULL,所以不能直接使用。使用過NEXTVAL訪問序列後才能使用 【語法】查看序列的當前值 select <sequence_name>.currval from dual; 【示例】 select seq_test.nextval from dual; select seq_test.currval from dual; |
運用序列
-- 創建序列 create sequence seq_emp_empno start with 1000 increment by 1 maxvalue 9000 minvalue 1000 nocycle; -- 使用序列作為主鍵插入emp表的empno列 insert into emp(empno,ename) values(seq_emp_empno.nextval,'itcast1'); insert into emp(empno,ename) values(seq_emp_empno.nextval,'itcast2'); -- 查看emp表數據 select empno,ename from emp; -- 查看當前序列的值 select seq_emp_empno.currval from dual; --修改序列 alter sequence seq_emp_empno maxvalue 9999 cycle; |
11.3刪除序列
【語法】 DROP SEQUENCE <sequence_name> 【示例】 drop sequence seq_test; |
11.4序列與sys_guid
sys_guid和序列都可以作為主鍵值。
--使用SYS_GUID函數,32位,由時間戳和機器標識符生成,保證唯一 select sys_guid() from dual; |
12分區表
12.1分區表用途
分區表通過對分區列的判斷,把分區列不同的記錄,放到不同的分區中。分區完全對應用透明。Oracle的分區表可以包括多個分區,每個分區都是一個獨立的段(SEGMENT),可以存放到不同的表空間中。查詢時可以通過查詢表來訪問各個分區中的數據,也可以通過在查詢時直接指定分區的方法來進行查詢。
分區表的優點:
(1)由於將數據分散到各個分區中,減少了數據損壞的可能性;
(2)可以對單獨的分區進行備份和恢復;
(3)可以將分區映射到不同的物理磁盤上,來分散IO;
(4)提高可管理性、可用性和性能。
數據量大的表,一般大於2GB;數據有明顯的界限劃分;對於Long和Long Raw類型列不能使用分區。
12.2分區表類型
一般包括範圍分區,散列分區,列表分區、複合分區(範圍-散列分區,範圍-列表分區)、間隔分區和系統分區等。
12.2.1範圍分區
範圍分區根據數據庫表中某一字段的值的範圍來劃分分區。
【語法】 在Create Table語句後增加 PARTITION BY RANGE(column_name) ( PARTITION part1 VALUES LESS THAN (range1) [TABLESPACE tbs1], PARTITION part2 VALUES LESS THAN (range2) [TABLESPACE tbs2], .... PARTITION partN VALUES LESS THAN (MAXVALUE) [TABLESPACE tbsN] ); 【說明】 MAXVALUE:當分區列值都不在設置的範圍內時,新增數據將到這個分區中 【示例】 -- 創建表,並設置分區 create table myemp ( empno number(4) primary key, ename varchar2(10), hiredate date, sal number(7,2), deptno number(2) ) partition by range(sal) ( partition p1 values less than(1000), partition p2 values less than(2000), partition p3 values less than(maxvalue) ); -- 插入數據 insert into myemp(empno,ename,hiredate,sal,deptno) select empno,ename,hiredate,sal,deptno from emp; -- 查看工資1000-2000的數據 select * from myemp partition(p2); -- 刪除工資小於1000的數據 delete from myemp partition(p1); -- 查看數據 select * from myemp; |
12.2.2列表分區
列表分區明確指定了根據某字段的某個具體值進行分區,而不是像範圍分區那樣根據字段的值範圍來劃分的。
【語法】 在Create Table語句後增加 PARTITION BY LIST(column_name) ( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), .... PARTITION partN VALUES (DEFAULT) ); 其中:column_name是以其為基礎創建列表分區的列。 part1...partN是分區的名稱。 values_list是對應分區的分區鍵值的列表。 DEFAULT關鍵字允許存儲前面的分區不能存儲的記錄。 【示例】 -- 創建表,並設置分區 create table myemp2 ( empno number(4) primary key, ename varchar2(10), hiredate date, sal number(7,2), deptno number(2) ) partition by list(deptno) ( partition dept10 values(10), partition dept20 values(20), partition dept30 values(30), partition deptx values(default) ); -- 插入數據 insert into myemp2(empno,ename,hiredate,sal,deptno) select empno,ename,hiredate,sal,deptno from emp; -- 查看部門20的數據 select * from myemp2 partition(dept20); -- 刪除部門30的數據 delete from myemp2 partition(dept30); -- 查看數據 select * from myemp2; |