該專案記錄了Oracle相關的速查知識彙總，主要涉及了oracle基礎使用、SQL基礎、oracle函數、oracle觸發器、oracle高階查詢、PL/SQL程式設計基礎、PL/SQL儲存過程等。若有新增，還將不斷新增中。
Oracle Database，又名Oracle RDBMS，或簡稱Oracle，是甲骨文公司的一款關係數據庫管理系統。本課程主要介紹Oracle的SQL基礎，包括表空間的概念，如何登錄Oracle數據庫，如何管理表及表中的數據，以及約束的應用。爲後續課程的學習打下一個良好的基礎。
開啓sqlplus，輸入使用者名稱system或sys（後者有最高許可權）和自己設定的口令就可以登錄了。
系統使用者有哪些？
sys,system 前者高於後者，前者必須以管理員許可權登錄
sysman 操作企業管理的使用者，也是管理員使用者
scott 普通使用者
前三者使用者的密碼是安裝時設定的，scott的預設密碼是tiger
登錄通用語句：
[username/password][@server][as sysdba|sysoper]
如果是遠程登錄，則需要輸入IP地址。
也可以在前面加一個connect，比如connect as sysdba;
數據字典是Oracle存放有關數據庫資訊的地方，其用途是用來描述數據的。比如一個表的建立者資訊，建立時間資訊，所屬表空間資訊，使用者存取許可權資訊等。當使用者在對數據庫中的數據進行操作時遇到困難就可以存取數據字典來檢視詳細的資訊。
dba_開頭的是管理員才能 纔能檢視的數據字典，users_開頭的是都能檢視的數據字典。
數據字典或表前加上desc可以檢視他們的結構。
比如desc dba_users檢視數據字典的資訊。
select username from dba_users;
可以從數據字典裏面檢視使用者的名字。
dba_users、user_users用來檢視不同許可權級別的數據字典。使用範例如：
select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM';
select file_name from dba_data_files where tablespace_name = 'TEST1_TABLESPACE';
得到結果：
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\HP\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
類似的字典 dba_temp_files（檢視臨時表空間檔案的）
預設情況下是鎖定的。
啓用語句：
alter user username(可以替換) account unlock;
表空間就是在數據庫中開闢的一個空間用來儲存我們的數據庫物件，一個數據庫可以由多個表空間構成。
表空間由一個或多個數據檔案構成，大小可以由使用者來定義。
表空間的分類：
永久表空間主要儲存數據庫中要永久儲存的物件，表、檢視、儲存過程。臨時表空間儲存數據庫操作當中中間執行的過程，執行結束之後自動釋放掉，不進行永久性儲存。UNDO表空間用於儲存事務所修改數據的舊值，可以做回滾操作。
數據字典：dba_tablespaces（針對管理員級別的使用者）、user_tablespaces（針對普通使用者的數據字典）
dba的表空間中，system用於管理員，也叫系統表空間，sysaux爲範例的輔助表空間，undotbs1用於儲存復原資訊的，temp用於儲存處理的表和索引資訊的臨時表空間，users用於儲存使用者建立的數據庫物件，example表空間等。
alter user username default|temporary tablespace tablespace_name;
alter user system default tablespace system;
create [temporary] tabalspace tablespace_name tempfile|datafile 'xx.dbf' size xx;
create tablespace test1_tablespace datafile 'test1file.dbf' size 10M;
create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10M;
alter tablespace tablespace_name online|offline; --離線狀態不能使用它
dba_tablespaces字典下面 下麪的status狀態可以檢視狀態。
select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';
alter tablespace tablespace_name read only|read write; --前提是表空間是一定是線上狀態
alter tablespace tablespace_name add datafile 'xx.dbf' size xx;
新增後使用dba_data_files來查詢。
select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
alter tablespace tablespace_name drop datafile 'xx.dbf'; --注意不能刪除建立表空間時候的第一個檔案，如果需要刪除則必須要把表空間刪掉。
drop tablespace tablespace_name [including contents]; --如果需要把數據檔案也刪除則把後面加上。
表是基本儲存單位，要把數據都存在表中，oracle中的表都是二維結構。
一行也可以叫做記錄，一列也可以叫做域或者欄位。
約定：要求每一列需要有相同的數據型別。
列名要是唯一的。
每一行的數據是唯一的。
create table table_name( --在同一個使用者下表明要是唯一的。
column_name datatype,...
);
如
create table userinfo
(id number(6,0),
username varchar2(20),
userpwd varchar2(20),
email varchar2(30),
regdate date);
建立完之後如果需要檢視欄位的資訊使用desc userinfo即可檢視。
alter table table_name add column_name datatype;
如
alter table userinfo add remarks varchar2(500);
alter table table_name modify column_name datatype; --改長度或者更換數據型別
如
alter table userinfo modify remarks varchar2(400);
alter table table_name drop column column_name;
alter table table_name rename column column_name to new_column_name;
rename table_name to new_table_name;
truncate table table_name; --刪除表中的全部數據，沒有刪除表，比delete快很多。
drop table table_name; 數據和結構都刪掉。
insert into table_name (column1,column2,...) values (value1,value2,...); --如果是所有欄位都新增值，則表明後面的小括號可以省略。
如
insert into userinfo values (1,'xxx','123','[email protected]',sysdate); --sysdate可以獲取當前系統日期
查詢select * from userinfo;
又如
insert into userinfo (id,username,userpwd) values (2,'yyy','123');
如
create table userinfo1(id number(6,0),regdate date default sysdate); --使用default關鍵詞，雖然指定了預設值，但是在新增的時候還是要指定欄位名才行
在建立表以後新增預設值：
alter table userinfo modify email default '無'; --如果在新加記錄的時候不想要預設值了，則按正常的新增方式新增了就可以替換預設值了
create table table_new as select column1,...|* from table_old; --可以選擇要複製的欄位也可以複製所有
如：
create table userinfo_new as select * from userinfo; --userinfo是被複制的表
create table userinfo_new1 as select id,username from userinfo;
insert into table_new [(column1,...)] select column1,...|* from table_old; --順序和數據型別要完全一致
如
insert into userinfo_new select * from userinfo;
又如
insert into userinfo_new (id,username) select id,username from userinfo;
update tabel_name set column1=value1,...[where conditions];
update userinfo set userpwd = '111111';
update userinfo set userpwd='123456' where username ='xxx';
delete from table_name; --刪除全部數據，效率慢些
delete from table_name [where conditions];
delete from testdel;
delete from userinfo where username='yyy';
約束的作用是定義規則（最重要），確保完整性。
create table table_name(column_name datatype not null,...);
如：
create table userinfo_1 (id number(6,0),
username varchar2(20) not null,
userpwd varchar2(20) not null);
alter table tabel_name modify column_name datatype not null;
如
alter table userinfo modify username varchar2(20) not null; --在修改之前表裏面不要有任何數據
alter table tabel_name modify column_name datatype null;
必不可少，確定每一行數據的唯一性。
一張表只能設計一個主鍵約束。
主鍵約束可以由多個欄位構成，稱爲聯合主鍵或者複合主鍵。
create table table_name(column_name datatype primary key,...);
如
create table userinfo_p(id number(6,0) primary key,
username varchar2(20),
userpwd varchar2(20));
如果用約束的話：
constraint constraint_name primary key (column_name1,...); --一般用來建立聯合主鍵
如
create table userinfo_p1(id number(6,0),
username varchar2(20),
userpwd varchar2(20),
constraint pk_id_username primary key(id,username));
如果忘記了約束的名字，可以到user_constraints數據字典查詢constraint_name.
如
select constraint_name from user_constraints where table_name='USERINFO_P1';
如果沒有用約束來建立主鍵，則系統會自動命名約束的名稱，可以看這個：
select constraint_name from user_constraints where table_name='USERINFO_P';
結果爲：
CONSTRAINT_NAME
------------------------------
SYS_C0011189
add constraint constraint_name primary key(column_name1,...); --主鍵名一般以pk_開頭
alter table userinfo add constraint pk_id primary key(id); --設定約束之前，如果已經有值了，必須唯一，且不能爲空。
alter table table_name rename constraint old_name to new_name;
alter table table_name disable|enable constraint constraint_name; --禁用|啓用約束，不刪除
檢視狀態：
select constraint_name,status from user_constraints where table_name='USERINFO';
alter table table_name drop constraint constraint_name;
drop primary key[cascade]; --刪除主鍵約束，如果存在外來鍵約束，填寫cascade，可以把其他表中參照該主鍵約束的一起刪掉
兩個表之中欄位關係的約束。
--分開建立時
create table table1(column_name datatype references table2(column_name),...);
--table2爲主表，table1爲從表，也叫主從表。主表當中的欄位必須是主表中的主鍵欄位，主從表的欄位要設定成同一個數據型別。在向設定了外來鍵約束的表輸入值的時候，從表中外來鍵欄位的值必須來自主表中的相應欄位的值，或者爲null值。
create table typeinfo(typeid varchar2(10) primary key,
typename varchar2(20));
create table userinfo_f(id varchar2(10) primary key,
username varchar2(20),
typeid_new varchar2(10) references typeinfo(typeid));
insert into typeinfo values(1,1);
insert into userinfo_f(id,typeid_new) values (1,2);
則2在主表中沒有找到，會報錯。需要填寫
insert into userinfo_f(id,typeid_new) values (1,1);
纔可以，或者那個部分留空值：
insert into userinfo_f(id,typeid_new) values (2,null);
--定義完所有的欄位之後設定的約束
constraint constraint_name foreign key(column_name) references table_name(column_name) [on delete cascade];
--後面的中括號是級聯刪除，表示主表當中的一條數據被刪除的時候，從表當中使用了這條數據的欄位所在的行也會被一起刪除掉，這樣確保了主從表數據的完整性。
如：
create table userinfo_f2 (id varchar2(10) primary key,
username varchar2(20),
typeid_new varchar2(10),
constraint fk_typeid_new foreign key (typeid_new) references typeinfo(typeid));
create table userinfo_f3 (id varchar2(10) primary key,
username varchar2(20),
typeid_new varchar2(10),
constraint fk_typeid_new1 foreign key (typeid_new) references typeinfo(typeid) on delete cascade);
alter table tabel_name add constraint constraint_name foreign key(column_name) references table_name(column_name) [on dedelete cascade];
disable|enable constraint constraint_name;
drop constraint constraint_name;
作用是保證欄位的唯一性，和主鍵約束的區別是，主鍵約束必須是非空的，而唯一約束允許有一個空值。主鍵約束在一張表中只能有一個，唯一約束可以有多個。
create table tabel_name(column_name datatype unique,...);
constraint constraint_name unique(column_name); --如果需要設定多個欄位爲唯一約束，要寫多個constraint子句。
add constraint constraint_name unique(column_name);
禁用：
disable|enable constraint constraint_name;
完全刪除：
drop constraint constraint_name;
檢查約束，讓表當中的值更具有實際意義，能夠滿足一定的條件，具有實際意義。
create table tabel_name(column_name datatype check(expression),...);
如：
create table userinfo_c (id varchar2(10) primary key,
username varchar2(20),
salary number(5,0) check(salary>0));
比如輸入insert into userinfo_c values(1,'aaa',-50);就會報錯。
constraint constraint_name check(expression);
如：
create table userinfo_c1(id varchar2(10) primary key,
username varchar2(20),
salary number(5,0),
constraint ck_salary check(salary>0));
add constraint constraint_name check(expression);
禁用：
disable|enable constraint constraint_name;
刪除：
drop constraint constraint_name;
在建立表時設定約束：
只有非空約束只能在列級設定約束，不能在表級設定約束，其他的都是兩者都可以的。非空約束是沒有名字的。
在修改表時新增約束，也是隻有非空約束不同，修改表時用的語句是
alter table talbe_name modify column_name datatype not null;
更改約束的名稱：數據字典（user_constraints檢視名稱）
rename constraint old_name to new_name;
刪除約束，非空約束較特殊
alter table tabel_name modify column_name datatype null;
其他的如果是禁用的話使用
disable|enable constraint constraint_name;
如果要永久刪除可以用
drop constraint constraint_name;
刪除主鍵約束還能用
drop primary key;
select [distinct] column_name1,...|* from table_name [where conditions]; --distinct可以不顯示重複的行。
column column_name heading new_name;
如
col username heading 使用者名稱; --執行成功的話不會有回顯
--column可以簡寫成col，設定新的欄位名（別名），使用select語句來查詢的時候就可以看到變化了，但使用desc看結構還依然不變化。
設定結果顯示的格式：
column column_name format dataformat;
注意：字元型別只能設定它的長度。 --字元格式用a開頭，後面跟它要的長度。
如
col username format a10;
如果是數值型別用，9表示一位數位，比如
col salary format 9999.9;
可以保留4位元數和一位小數。
如果
col salary format 999.9;
但如果數據中有四位的數，超過這個長度的就用#####表示了，與excel一致。
如果使用col salary format $9999.9;則數位前面加了美元符號。
清除之前設定過的格式：
column column_name clear;
如
col salary clear;
select * from table_name;
查詢指定的欄位：比如
select username,salary from users;
不會更改欄位的名字，可以爲多個欄位設定別名
select column_name as new_name,... from table_name; --其中as可以省略，但最好加上
如
select id as 編號, username as 使用者名稱, salary 工資 from users;
檢視唯一值：
select distinct username as 使用者名稱 from users;
運算子大家都比較熟悉了，而表達式=運算元+運算子組成。
oracle中的運算元可以有變數、常數、欄位。
運算子有算術運算子（+、-、*、/），比較運算子（>,>=,<,<=,=,<>都是用在where條件裏面的，兩個數進行比較得到的結果是布爾型別的，真或者假），邏輯運算子（and,or,not）
在查詢結果中，給每個員工的工資加上200元，但數據本身沒變。
如
select id,username,salary+200 from users;
使用比較運算子：
查詢工資高於800元的員工的姓名；
如
select username from users where salary > 800;
使用邏輯運算子：
如
select username from users where salary > 800 and salary <>1800.5;
select username from users where salary > 800 or salary <>1800.5;
如
select salary from users where username='aaa';
select username,salary from users where id=3;
多條件如
select * from users where username='aaa' or salary<=2000 and salary>800;
邏輯運算子的優先順序順序：not,and,or
比較運算子優先順序高於邏輯運算子
not的例子：
select * from users where not(username='aaa');
like關鍵字，也可以歸入比較運算子當中。
萬用字元的使用（_表示一個字元，%表示0到多個任意字元）
如
select * from users where username like 'a%'; --以a開頭的行
select username from users where username like '%a%'; --含有a的
between…and --表示從什麼到什麼之間。查詢結果是含頭又含尾的區間。
如果不在這個之間的，在它們前面加上not
如
select * from users where salary not between 800 and 2000;
in/not in 後面跟着小括號，裏面是一個列表的值，一個具體的值。
如
select * from users where username in ('aaa','bbb');
select * from users where username not in ('aaa','bbb');
select...from...[where...] order by column1 desc/asc,... --desc爲降序排列，asc升序
case column_name when value1 then result1,...[else result] end;
如
select username,case username when 'aaa' then '計算機部門'
when 'bbb' then '市場部門' else '其他部門' end as 部門
from users;
另一種形式：
case when column_name=value1 then result1,...[else result] end;
如
select username,case when username='aaa' then '計算機部門'
when username='bbb' then '市場部門' else '其他部門' end as 部門
from users;
如
select username,case when salary<800 then '工資低' when salary>5000 then '工資高' end as 工資水平 from users;
decode(column_name,value1,result1,...,defaultvalue)
如
select username,decode(username,'aaa','計算機部門','bbb','市場部門','其他') as 部門 from users;
=====================================================================================
round(n[,m])
如
select round(23.4),round(23.45,1),round(23.45,-1)from dual;
ceil(n) --取最大值
floor(n) --取最小值
select ceil(23.45),floor(23.45) from dual;
結果：
CEIL(23.45) FLOOR(23.45)
----------- ------------
24 23
abs(n) --取絕對值
如
select abs(23.45),abs(-23),abs(0) from dual;
=====
mod(m,n) --取餘數
如
select mod(5,2) from dual;
=====
power(m,n) --取m的n次冪
如
select power(2,3),power(null,2) from dual;
=====
sqrt(n) --求平方根
如
select sqrt(16) from dual;
=====
sin(n),asin(n)
cos(n),acos(n)
tan(n),atan(n) --提供弧度參數
如
select sin(3.124) from dual;
upper(char) --轉換爲大寫
lower(char) --轉換爲小寫
initcap(char) --首字母大寫
如
select upper('abde'),lower('ADe'),initcap('asd') from dual;
substr(char,[m[,n]]) --獲取子字元，分別是從哪取，從哪個位置開始取以及取出多少位，n省略時，從m的位置擷取到結束，m從1開始如果m寫0也是從第一個字元開始。如果m爲負數時，從字串的尾部開始擷取
如
select substr('abcde',2,3),substr('abcde',2),substr('abcde',-2,1) from dual;
length(char) --會包含空格的長度
如
select length('acd ') from dual;
concat(char1,char2) --與||作用一樣
如
select concat('ab','cd') from dual;
trim(c2 from c1) --代表從c1中去除c2字串，就是子文字替換，要求c2中只能是一個字元
如
select trim ('a' from 'abcde') from dual;
=====
ltrim(c1[,c2]) --從c1中去除c2，從左邊開始去除，要求第一個就是要去除的字元，有多少個重複的該字元就會去除多少次
如
select ltrim('ababaa','a') from dual;
=====
rtrim(c1[,c2]) --從c1中去除c2，要求右側第一個就是要去除的字元，有多少個重複的該字元就會去除多少次
=====
trim(c1) --代表去除首尾的空格，刪首尾空，同理ltrim和rtrim只有一個參數時。
=====
replace(char,s_string[,r_string]) --替換函數，省略第三個參數則用空白替換
如
select replace('abcde','a','A') from dual;
select replace('abcde','c') from dual;
select replace('abced','ab','A') from dual;
sysdate 預設格式：DD-MON-RR 天-月-年
add_months(date,i) --用於新增指定的月份，返回在指定的日期上新增的月份，i可以是任意整數，如果i是負數，則是在原有的值上減去該月份了
如
select add_months(sysdate,3),add_months(sysdate,-3) from dual;
=====
next_day(date,char) --第二個參數指定星期幾，在中文環境下輸入星期X即可，返回下一個周幾是哪一天。
如
select next_day(sysdate,'星期一') from dual;
=====
last_day(date) --用於返回日期所在月的最後一天
如
select last_day(sysdate) from dual;
=====
month_between(date1,date2) --計算兩個日期之間間隔的月份，前者減後者
如
select months_between('20-5月-15','10-1月-15') from dual;
=====
extract(date from datetime) --返回相應的日期部分
如
select extract(year from sysdate) from dual; --可以改month或者day
select extract(hour from timestamp '2015-10-1 17:25:13') from dual;
=====
用於擷取日期時間的trunc函數
用法：trunc（欄位名，精度）
具體範例：
在表table1中，有一個欄位名爲sysdate，該行id=123，日期顯示：2016/10/28 15:11:58
1、擷取時間到年時，sql語句如下：
select trunc(sysdate,'yyyy') from table1 where id=123; --yyyy也可用year替換
顯示：2016/1/1
2、擷取時間到月時，sql語句：
select trunc(sysdate,'mm') from table1 where id=123;
顯示：2016/10/1
3、擷取時間到日時，sql語句：
select trunc(sysdate,'dd') from table1 where id=123;
顯示：2016/10/28
4、擷取時間到小時時，sql語句：
select trunc(sysdate,'hh') from table1 where id=123;
顯示：2016/10/28 15:00:00
5、擷取時間到分鐘時，sql語句：
select trunc(sysdate,'mi') from table1 where id=123;
顯示：2016/10/28 15:11:00
6、擷取時間到秒暫時不知道怎麼操作
7、不可直接用trunc(sysdate,‘yyyy-mm-dd’)，會提示「精度說明符過多」
8.如果不填寫第二個參數，則預設到DD，包含年月日，不包含時分秒。
to_char(date[,fmt[,params]]) --date爲需要轉換的日期，fmt爲轉換的格式，params爲轉換的語言（通常預設會自動選擇，可以省略，與安裝語言一致）
預設格式：DD-MON-RR
可以定義的格式：
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
=====
to_date(char[,fmt[,params]])
如
select to_date('2015-05-22','yyyy-mm-dd') from dual; --注意顯示的時候仍然按照時間的預設格式來顯示
=====
to_char(number[,fmt])
fmt列表：
select to_char(12345.678,'$99,999.999') from dual;
select to_char(12345.678,'s99,999.999') from dual;
=====
to_number(char[,fmt])
fmt是轉換的格式，可以省略
如
select to_number('$1,000','$9999') from dual;
如
substr(char[,m[,n]])
=====================================================================================
本部分需要有如下兩個部分的基礎
分組函數作用於一組數據，並對一組數據返回一個值。
結構：
select [column,] group function(column),...
from table
[where condition]
[group by column]
[order by column];
select count(distinct deptno) from emp;
如
select deptno 部門號,wm_concat(ename) 部門中員工的姓名 from emp group by deptno;
select sum(sal)/count(*) 一,sum(sal)/count(sal) 二,avg(sal) 三 from emp;
結果一樣
select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三 from emp;
二和三結果一樣，一不一樣，因爲在獎金列裏面含有空值，count的時候數數不一樣
所以分組函數會自動忽略空值，可以在分組函數中使用nvl函數來使分組函數無法忽略空值
如
select count(*),count(nvl(comm,0)) from emp;
group by 子句
select avg(sal) from emp group by deptno;
使用多個列分組
如
select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
要求所用包含於select列表中，而未包含於組函數中的列都必須包含於group by子句中。
select deptno,count(ename)
from emp;
這裏的deptno沒有包含在group by子句中，所以會報錯。
[having group_condition]select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
注意不能在where子句中使用組函數（注意）。
可以在having子句中使用組函數。
如果在能使用where的場景下，從SQL優化的角度來看，儘量使用where效率更高，因爲having是在分組的基礎上過濾分組的結果，而where是先過濾，再分組。要處理的記錄數不同。所以where能使分組記錄數大大降低，從而提高效率。
select deptno,avg(sal) from emp group by deptno order by avg(sal);
select deptno,avg(sal) 平均工資 from emp group by deptno order by 2; --也可以填寫序號
select max(avg(sal)) from emp group by deptno;
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
結果：
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 CLERK 4541
10 MANAGER 6455
10 PRESIDENT 9711
10 20707
20 CLERK 8235
20 ANALYST 12360
20 MANAGER 7032.5
20 27627.5
30 CLERK 4117.5
30 MANAGER 6895
30 SALESMAN 18648
30 29660.5
77995
rollup就可以實現上述的效果。小計、總計的效果，可以用在報表裏面。
再次優化，先執行：
break on deptno skip 2
再執行上面的程式碼即可。
如
ttitle col 15 '我的報表' col 35 sql.pno --15表示空15列，sql.pno表示報表頁碼
col deptno heading 部門號 --設定別名
col job heading 職位
col sum(sal) heading 工資總額
break on deptno skip 1 --deptno只顯示一次，部門間間隔一行
結果：
我的報表 1
部門號 職位 工資總額
---------- --------- ----------
10 CLERK 4541
MANAGER 6455
PRESIDENT 9711
20707
20 CLERK 8235
ANALYST 12360
MANAGER 7032.5
27627.5
我的報表 2
部門號 職位 工資總額
---------- --------- ----------
30 CLERK 4117.5
MANAGER 6895
SALESMAN 18648
29660.5
77995
按數據庫設計原則，員工表中只有部門的編號資訊，部門的詳細資訊會存放在部門表中。
什麼是多表查詢：就是從多個表中獲取數據。
前提是有一個外來鍵約束來表示員工是哪個部門的，有個一個部門號來聯結。
有了它纔有多表查詢的存在。笛卡爾集的列數等於每張表列數的相加，行數等於每張表的行數相乘。比如emp*dept有六列六行。裏面的每一條記錄不一定都是對的。多表查詢就是要從笛卡爾集中選擇出正確的記錄。需要一個連線條件，比如部門號相等。有了連線條件，就能避免使用笛卡爾全集。在實際執行環境下，應提供where連線條件，避免使用笛卡爾全集。連線條件至少有要連線表數-1個。
建立笛卡爾集可以使用全連線：
FULL JOIN
如
select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;
如
SELECT e.empno,e.ename,e.sal,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
如
SELECT d.deptno 部門號,d.dname 部門名稱,COUNT(e.empno) 人數 FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,d.dname; --漏了一個部門
核心：通過外連線，把對於連線條件不成立的記錄，仍然包含在最後的結果中。
左外連線（LEFT [OUTER] JOIN）：當連線條件不成立的時候，等號左邊的表仍然被包含
右外連線（RIGHT [OUTER] JOIN）：當連線條件不成立的時候，等號右邊的表仍然被包含
因此上述表達式改爲：
改爲右外連線 方法是在相反的方向的等值連線結尾加上(+),比如右外連線就是加在左邊的最後。
SELECT d.deptno 部門號,d.dname 部門名稱,COUNT(e.empno) 人數 FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname;
或者寫成：
SELECT d.deptno 部門號,d.dname 部門名稱,COUNT(e.empno) 人數 FROM emp e,dept d WHERE e.deptno right join d.deptno GROUP BY d.deptno,d.dname;
得到結果：
部門號 部門名稱 人數
------ -------------- ----------
10 ACCOUNTING 3
40 OPERATIONS 0
20 RESEARCH 5
30 SALES 6
INNER JOIN如
SELECT e.ename 員工姓名,b.ename 老闆姓名 FROM emp e,emp b WHERE e.mgr=b.empno;
儘管是查詢一張表，但本質上仍然是多表查詢，會產生笛卡爾集。
可以通過這個看笛卡爾集有多少條記錄select count(*) from emp e,emp b;表越多，次方越多。比如員工表中有一億條記錄，如果看成三張表，就有一億的立方的笛卡爾集，所以自連線不適合查詢大表。
所以要使用解決方法： 層次查詢 （單表查詢，只有在一張表時纔不會查詢笛卡爾集，在某些情況下可以取代自連線）。
層次查詢的原理：可以把前面的結果變成分level的一棵樹。這棵樹的根是沒有上司的king，也就是mgr就是NULL。
如：
SELECT level,empno,ename,sal,mgr FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ORDER BY 1;
自連線的優點：結果直觀。缺點：不適合操作大表。
層次查詢的優點：適合單表查詢，不會產生笛卡爾集。缺點：並沒有自連線那麼直觀。
爲什麼要學習子查詢：子查詢可以解決不能一步求解的問題
子查詢的語法：其實也就是select語句的巢狀
select select_list
from table
where expr operator
(select select_list
from table);
如
select * from emp where sal > (select sal from emp where ename='SCOTT');
語法中一定要有小括號，不然是錯的。
該換行的換行，該縮排的索引，可以便於閱讀。
select後面使用，要求一定要只返回一條記錄，要是單行子查詢才行，多行子查詢不行。
如
SELECT empno,ename,sal,(SELECT job FROM emp WHERE empno=7839) 第四列
FROM emp;
在having後面使用：
如
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) > (SELECT MAX(sal)
FROM emp
WHERE deptno=30);
在from後面放置：
非常的重要，很多問題都是在from後面方式子查詢來解決的
如
SELECT * from(SELECT empno,ename,sal FROM emp);
如
SELECT AVG(sal)
FROM emp
GROUP BY (SELECT deptno FROM emp); --會報錯，這裏不允許出現子查詢表達式
如
SELECT *
FROM (SELECT empno,ename,sal,sal*12 annsal FROM emp);
如
SELECT * FROM emp WHERE deptno=
(SELECT deptno
FROM dept
WHERE dname='SALES');
多表查詢程式碼：
SELECT e.*
FROM emp e,dept d
WHERE e.deptno=d.deptno AND d.dname='SALES';
哪種查詢方式好呢？從理論上來講，儘量使用多表查詢比較好，因爲子查詢需要對數據庫存取兩次，而多表查詢只需要對數據庫存取一次。但實際情況下有可能不一樣，因爲多表查詢的笛卡爾集可能很大所以慢了。
比如找到員工表中工資最高的前三名。
rownum行號，是一個僞列，表上沒有這一列，當做一些特殊操作的時候，oracle自動加上。行號需要注意的問題：行號永遠按照預設的順序生成；行號只能使用<，<=，不能使用>或者>=這樣的符號。
如
SELECT ROWNUM,empno,ename,sal
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM<=3;
相關子查詢的表必須設定一個別名，然後把主查詢的內容傳入到子查詢中進行查詢。
如
SELECT empno,ename,sal,(SELECT AVG(sal) FROM emp WHERE deptno=e.deptno) avgsal
FROM emp e
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno=e.deptno);
這裏就把主查詢e表中的部門號傳入子查詢中進行查詢了。
如
SELECT * FROM emp
WHERE job = (SELECT job FROM emp WHERE empno=7566)
AND sal > (SELECT sal FROM emp WHERE empno=7782);
又如
SELECT * FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp);
如
SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal)
FROM emp
WHERE deptno=20);
非法使用單行子查詢：
如
select empno,ename from emp where sal=(select min(sal)
from emp
group by deptno); --因爲子查詢返回了不止一行，所以是非法使用單行子查詢。
in：
如
SELECT * FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE dname='SALES' OR dname='ACCOUNTING');
又如
SELECT e.* FROM emp e,dept d
WHERE e.deptno=d.deptno AND (d.dname='SALES' OR d.dname='ACCOUNTING');
any：
如
SELECT * FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=30);
等價於
SELECT * FROM emp
WHERE sal > (SELECT min(sal) FROM emp WHERE deptno=30);
all：
如
SELECT * FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=30);
等價於：
SELECT * FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno=30);
單行子查詢中返回空值，要使用in之類的關鍵字，等於號的話永遠爲空。
多行子查詢中，如查詢不是老闆的員工
如：
SELECT * FROM emp
WHERE empno NOT IN (SELECT mgr FROM emp); --會不返回結果，因爲當子查詢中包含空值的時候，不能使用not in，因爲not in等同於不等於所有（永遠爲假）。
所以修改爲：
SELECT * FROM emp
WHERE empno NOT IN (SELECT mgr FROM emp
WHERE mgr IS NOT NULL);
所以分頁查詢：
SELECT r,empno,ename,sal
FROM(SELECT ROWNUM r,empno,ename,sal
from(SELECT ROWNUM,empno,ename,sal FROM emp ORDER BY sal DESC) e1
WHERE ROWNUM<=8) e2
WHERE r>=5;
如
SELECT e.empno,e.ename,e.sal,d.avgsal
FROM emp e,(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) d
WHERE e.deptno=d.deptno AND e.sal>d.avgsal; --多表查詢
如果需要查詢執行計劃看效能的話，則在語句前面加上EXPLAIN PLAN FOR
執行一遍之後，執行select * from table(dbms_xplan.display);即可檢視效能分析，看消耗的CPU的多少來判定效能的優劣。
SELECT COUNT(*) Total,
SUM(DECODE(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
SUM(DECODE(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
SUM(DECODE(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
SUM(DECODE(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
FROM emp;
使用子查詢方法：
SELECT
(SELECT COUNT(*) FROM emp) Total,
(SELECT COUNT(*) FROM emp WHERE to_char(hiredate,'yyyy')='1980') "1980",
(SELECT COUNT(*) FROM emp WHERE to_char(hiredate,'yyyy')='1981') "1981",
(SELECT COUNT(*) FROM emp WHERE to_char(hiredate,'yyyy')='1982') "1982",
(SELECT COUNT(*) FROM emp WHERE to_char(hiredate,'yyyy')='1987') "1987"
FROM dual;
新建兩個表，然後按要求查到相關的內容
第一個表：
CI_ID STU_IDS
-------------------- --------------------------------------------------------------------------------
1 1,2,3,4
2 1,4
表結構：
Name Type Nullable Default Comments
------- ------------- -------- ------- --------
CI_ID VARCHAR2(20)
STU_IDS VARCHAR2(100) Y
第二個表：
STU_ID STU_NAME
-------------------- --------------------
1 張三
2 李四
3 王五
4 趙六
表結構：
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
STU_ID VARCHAR2(20)
STU_NAME VARCHAR2(20) Y
提示：
1.需要進行兩個表的連線查詢，爲兩個表都取別名
2.使用instr(a,b)函數，該函數的含義爲：如果字串b在字串a的裏面，則返回的是b在a中的位置，及返回值大於0
3.需要用到分組查詢
4.使用wm_concat(cols)函數對學生姓名用逗號進行拼接
--結果查詢語句
SELECT ci_id,wm_concat(stu_name) stu_name
FROM pm_stu,pm_ci
WHERE INSTR(stu_ids,stu_id)>0
GROUP BY ci_id;
得到正確結果：
CI_ID STU_NAME
-------------------- --------------------------------------------------------------------------------
1 張三,趙六,王五,李四
2 張三,趙六
同時學會了一個，如果在oracle中，需要實現如果表已經存在則先刪除表的操作，寫法爲：
--如果已經存在表則先刪除表
DECLARE
k NUMBER;
BEGIN
select count(*) INTO k from all_tables where table_name='PM_CI';
IF k=1 THEN
execute IMMEDIATE 'DROP TABLE pm_ci';
END IF;
END;
/
其中查詢的表名和drop的表名變成你要檢測的表名即可。
=====================================================================================
使用PLSQL語言操作Oracle數據庫的效率最高。
之前用sql語句是命令式的語言，但如果案例是複雜的，比如需要分條件來做不同的事情的，就需要PL/SQL效率會更高，不需要用其他的程式語言。
全稱是Procedure Language/SQL，是oracle對sql語言的過程化擴充套件
指在SQL命令語言中增加了過程處理語句（如分支、回圈等），使SQL語言具有過程處理能力。
（1）是sql的擴充套件，支援sql語句。
（2）是程序導向的語言。
declare
--說明部分（變數說明、遊標申明、例外說明）
begin
--程式體（DML語句）
dbms_output.put_line('Hello World');
exception
--例外處理語句
end;
/
/ --這個正斜槓用來退出前面的程式碼編寫並且執行語句
--檢視程式包的結構
desc 程式包名字
set serveroutput on
舉例：名字在片面，變數是在後面，:=爲賦值符號不是單=號
var1 char(15);
married bollean := true;
psal number(7,2); --說明有兩位小數
my_name emp.ename%type; --參照ename的型別，ename是啥型別變數就是啥型別
第二種賦值方式：
select ename,sal into pename,psal from emp where empno=7839;
這裏的into就可以賦值，是一一對應的。
emp_rec emp%rowtype;
--取表中一行的型別，作爲變數的型別，可以理解爲陣列，如果需要取用到列裏面的某一行，就像如下寫法：
emp_rec.ename := 'ADAMS'; --ename是列的名字。
情況一
if 條件 then 語句1;
語句2;
end if;
情況二
if 條件 then 語句序列1;
else 語句序列2;
end if;
情況三
if 條件 then 語句;
elsif 語句 then 語句; --特別注意下elsif
else 語句;
end if;
while total <= 25000 loop
...
total := total + salary;
end loop;
--在控制遊標的時候比較簡單
loop
exit[when 條件];
......
end loop;
for i in 1..3 loop --表示連續區間用這種寫法
語句序列;
end loop;
select如果返回的結果有多行的話就會出錯，所以需要引入遊標，遊標cursor就是一個結果集。也叫遊標。
cursor 遊標名 [(參數名 數據型別[,參數名 數據型別]...)] is select 語句;
如
cursor c1 is select ename from emp;
此外，遊標是可以帶參數的。
open c1;(開啓遊標執行查詢)
close c1;(關閉遊標釋放資源)
fetch c1 into pename; (取一行到變數中)
（1）把當前指針指向的記錄返回；
（2）將指針指向下一條記錄。
預設情況下，oracle數據庫只允許在同一個對談中開啓300個遊標
alter system set open_cursors=400 scope=both;
scope是範圍，取值有三個：both, memory（只更改當前範例）, spfile（只更改參數檔案，數據庫需要重新啓動）
例外是程式設計語言提供的一種功能，用來增強程式的健壯性和容錯性。
定義變數，型別是exception
使用raise關鍵字拋出自定義例外
瀑布模型：
需求分析
設計1.概要設計2.詳細設計
編碼Coding
測試Testing
上線
以上步驟就像水流一下，最忌諱一上來就編碼。
想明白SQL語句、變數。
變數：1.初始值是多少2.最終值如何得到
when zero_divide then dbms_output.put_line('1:0不能做被除數');
dbms_output.put_line('2:0不能做被除數');
這裏兩句話都會被列印出來。
=====================================================================================
觸發器是一個特殊的儲存過程。是一個與表相關聯的、儲存的PL/SQL程式。
作用：每當一個特定的數據操作語句（insert、update、delete，注意沒有select）在指定的表上發出時，oracle自動地執行觸發器中定義的語句序列。
在指定的操作語句操作之前或之後執行一次，不管這條語句影響了多少行。
語句級觸發器針對表，只會觸發一次
觸發語句作用的每一條記錄都被觸發。在行級觸發器中使用:old和:new僞記錄變數，識別值的狀態。如果有for each row就表示行級觸發器。
如
insert into emp10 select * from emp where deptno=10;
會查出3條記錄。
行級觸發器針對行，有多少條記錄就觸發多少次。
每當成功插入新員工後，自動列印一句話，「成功插入新員工」。單詞trigger
例如：
create trigger saynewemp
after insert
on emp
declare
begin
dbms_output.put_line('成功插入新員工');
end;
/
（1）複雜的安全性檢查 --比如週末不允許操作數據庫
（2）數據的確認 --漲後的工資大於漲前的工資
（3）數據庫審計 --跟蹤表上所做的數據操作，什麼時間什麼人操作了什麼數據，操作的人是什麼。基於值的審計
（4）數據的備份和同步 --異地備份，把主數據的數據自動同步到備數據庫中
create [or replace] trigger 觸發器名
{before|after}
{delete|insert|update [of 列名]}
on 表名
[for each row [when 條件]]
plsql塊
:old和:new的使用要注意。create [or replace] PROCEDURE 過程名(參數列表)
AS
PLSQL子程式體; （關鍵字可以小寫）（如果不傳參，則參數列表的小括號也可以省略）
這個PLSQL子程式體;一般爲：
begin
程式碼...
end;
/
如果寫的是儲存函數，那麼這裏的PROCEDURE需要改成FUNCTION，而且必須在參數列表和AS之間新增一句：RETURN 函數值型別。而且需要在子程式體需要返回的時候寫return 返回值。
寫好之後先編譯，然後呼叫、執行。
as後面跟的是說明部分，相當於declare。
as也可以寫成is。
呼叫方式有兩種：
（1）exec 過程名();
（2）
begin
呼叫語句;
end;
/
在參數列表中，如果是輸入參數，可以寫入eno in number，in是關鍵，in前面是變數名，後面是變數型別。
如果是輸出參數，寫eno out number，out是關鍵，out前面是變數名，後面是變數型別。
儲存過程和儲存函數都可以有out參數。
他們都可以有多個out參數。
儲存過程可以通過out參數來實現返回值。
查詢某個員工姓名、月薪和職位
create or replace procedure queryempinform(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
--得到該員工的姓名、月薪和職位
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
案例：查詢某個部門中所有員工的所有資訊。
包頭（負責宣告包中的結構）：
CREATE OR REPLACE PACKAGE MYPACKAGE AS
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
END MYPACKAGE;
注意：包頭中也可以不定義儲存過程，只定義遊標那一行。
包體（負責寫需要實現包頭中宣告的所有方法）：
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS
procedure queryEmpList(dno in number,empList out empcursor) AS
BEGIN
--開啓遊標型別（是一個集合，意味着可以返回許多資訊的集合）
open empList for select * from emp where deptno=dno;
END queryEmpList;
END MYPACKAGE;
注意：包體裏面的儲存過程也可以不寫在包體內部也可以一樣呼叫包頭中定義的遊標。
哪個許可權沒有就到sqlplus輸入如下程式碼：
grant 要調的許可權（中間用逗號分隔） to 使用者名稱;
一些不知道插進過去的哪些筆記的筆記就放在這裏吧~
以
select * from A where exists(select * from B where A.a=B.a)
爲例,
exists表示,對於A中的每一個記錄,如果,在表B中有記錄,其屬性a的值與表A這個記錄的屬性a的值相同,則表A的這個記錄是符合條件的記錄,
如果是NOT exists,則表示如果表B中沒有記錄能與表A這個記錄連線,則表A的這個記錄是符合條件的記錄。
而且必須在參數列表和AS之間新增一句：RETURN 函數值型別。而且需要在子程式體需要返回的時候寫return 返回值。**
寫好之後先編譯，然後呼叫、執行。
as後面跟的是說明部分，相當於declare。
as也可以寫成is。
呼叫方式有兩種：
（1）exec 過程名();
（2）
begin
呼叫語句;
end;
/
在參數列表中，如果是輸入參數，可以寫入eno in number，in是關鍵，in前面是變數名，後面是變數型別。
如果是輸出參數，寫eno out number，out是關鍵，out前面是變數名，後面是變數型別。
儲存過程和儲存函數都可以有out參數。
他們都可以有多個out參數。
儲存過程可以通過out參數來實現返回值。
查詢某個員工姓名、月薪和職位
create or replace procedure queryempinform(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
--得到該員工的姓名、月薪和職位
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
案例：查詢某個部門中所有員工的所有資訊。
包頭（負責宣告包中的結構）：
CREATE OR REPLACE PACKAGE MYPACKAGE AS
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
END MYPACKAGE;
注意：包頭中也可以不定義儲存過程，只定義遊標那一行。
包體（負責寫需要實現包頭中宣告的所有方法）：
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS
procedure queryEmpList(dno in number,empList out empcursor) AS
BEGIN
--開啓遊標型別（是一個集合，意味着可以返回許多資訊的集合）
open empList for select * from emp where deptno=dno;
END queryEmpList;
END MYPACKAGE;
注意：包體裏面的儲存過程也可以不寫在包體內部也可以一樣呼叫包頭中定義的遊標。
哪個許可權沒有就到sqlplus輸入如下程式碼：
grant 要調的許可權（中間用逗號分隔） to 使用者名稱;
一些不知道插進過去的哪些筆記的筆記就放在這裏吧~
以
select * from A where exists(select * from B where A.a=B.a)
爲例,
exists表示,對於A中的每一個記錄,如果,在表B中有記錄,其屬性a的值與表A這個記錄的屬性a的值相同,則表A的這個記錄是符合條件的記錄,
如果是NOT exists,則表示如果表B中沒有記錄能與表A這個記錄連線,則表A的這個記錄是符合條件的記錄。