一、入門

oracle有四個用戶，分別為sys、system、sysman和scott,其中sys是oracle權限最高的用戶，類似于Linux

系統的root，scott是示例用戶，上課就以這個用戶里的三張員工表empno、dept、salgrade作為示例來授課。

啟動服務

1. 快捷鍵ctrl+alt+del打開任務管理器

2. 選擇服務

3. 啟動OracleServiceXXXX(XXXX是全局名字)和oracleXXXXXXXListener服務(選中一個按o快速找到哦)

這個非常重要(！important),沒有啟動一切免談(記筆記.....)

驗證數據庫是否創建成功

win+R+cmd打開windows命令行控制臺(小黑窗)

C:\User\PC>sqlplus /nolog

若出現SQL>則證明數據庫創建成功

登陸

語法格式：conn[ect] 用戶名/口令 [AS SYSDBA | AS SYSOPER]

注：中括號表示可以省略

登陸sys用戶

conn /as sysdba(用戶名和口令都為空)

只能以“sysdba”或者“sysoper”系統身份登錄 ，不能用 normal。

登陸scott用戶

剛開始scott用戶默認被鎖定，需要登陸sys用戶之后執行alter user scott account unlock;

并重新設置口令即可登陸，默認口令是tiger

登陸：conn scott/tiger(password)

虛表

虛表不是真實存在的表，只有一個字段和一行記錄，當需要查詢且不想從已存在的表中獲取數據時便可以使用虛表，更詳細的解釋你可以百度一下。

二、基礎

結構查詢語言的分類

考試好像一直考，應該蠻重要的吧。

SQL相關概念和規則

子句：關鍵字加其他語法元素，例如，select * 是一個子句，from table是一個子句。

列別名：列名 as 列別名 or 列名 列別名，需要注意的是若包含空格、需區分大寫以及含特殊字符時，需加引號

關鍵字通常使用大寫，其他語法元素小寫，通常以子句為單位進行分行。

NULL

空值是一種無效的、未被賦值的、未知的或不可用的值。任何包含空值的算術表達式運算結果均為空值。(這一點需要注意)

處理空值的函數

nvl(comm,0)：若comm不為空，就返回comm，否則返回0

nvl2(expr1,expr2,0)：若expr1不為空，就返回expr2,否則返回0

NULLIF(expr1,expr2)：數據類型需一致，相等返回null,否則返回第一個表達式

coalesce(expr1,expr2,...)返回第一個非空參數，數據類型需一致

特殊運算符

between and 判斷要比較的值是否在某個范圍內。 eg: sal [not] between 1500 and 2000

in 判斷要比較的值是否和集合列表中的任何一個值相等。 eg: sal [not] in (1500,1600,1800)

like 判斷要比較的值是否滿足部分匹配。 eg: ename [not] like '%T_' 注：'%'代表任意多字符(>=0)，下劃線代表一個任意字符

is null 判斷要比較的值是否為空值NULL 。 eg: where comm is [not] null

運算符優先級

()、算術運算符(+-*/)、連接運算符(||)、比較運算符(<> 不等于)、特殊比較運算符(like、in、between and、is null)、邏輯運算符(not>and>or)

消除重復行

在select字句中使用關鍵字DISTINCT可消除重復行。

eg ：select distinct deptno from emp;

原義字符串和連接運算符

原義字符串

原義字符串是包含在SELECT列表中的一個字符、一個數字或一個日期。

日期和字符字面值必須用單引號引起來。

每個原義字符串都會在每個數據行輸出中出現。

連接操作符

用于連接列與列、列和字符

形式上是以兩個豎杠||

用于創建字符表達式的結果列

顯示表結構

打開plsqldev(紅色油桶)，打開Command Window,或者在windows控制臺上操作也行。

SQL>desc tablename;

SQL六個常用的執行語句

select*

from table

where condition

group by column

having condition

order by column [asc|desc] asc升序，desc是降序

執行順序:from > where > group by > having > select > order by

三、函數

單行函數

1. 單行函數返回一個結果

2. 支持嵌套

3. 單行函數可以放在select、where、order by子句中

4. 單行函數對單行數據操作

字符函數(大小寫轉換函數和字符處理函數)

* lower(列名|表達式)：將大寫或大小寫混合的字符轉換成小寫

* upper(列名|表達式) ：將小寫或大小寫混合的字符轉換成大寫

* initcap(列名|表達式) ：將每個單詞的第一個字母轉換成大寫，其余的字母都轉換成小寫(這個不要漏了哦)

* concat(列名1，列名2)：連接兩個字段或表達式，類似于||

* substr(列名|表達式,n1,n2)：返回第一個參數中，從第n1位開始， 長度為n2的子串 。

* length(column | expression)：取字符長度

* instr(s1,s2,[,n1],[n2]) ：返回s1中，子串s2從n1開始，第n2次出現的位置。n1,n2默認值為1

* lpad(s1,n1,s2)：返回s1被s2從左面填充到n1長度后的字符串 。

* rpad(s1,n1,s2) ：返回s1被s2從右面填充到n1長度后的字符串。

* trim：去除字符串頭部或尾部(頭尾)的字符

格式:TRIM([leading | trailing | both] character from source) 注意第一個參數和from

* replace(s1,s2,s3) : 把s1中的s2用s3替換。注意誰替換誰

用的比較多的是concat()、substr()、instr()、length()

數值函數

round(num，n)：將num四舍五入到小數點后n位

trunc(num，n)：將num截取到小數點后n位

mod(num，n)：取num對n取余

日期函數

日期函數默認格式是DD-MM-RR，SYSDATE是系統當前時間

日期可直接加減數值，表示加減對應天數，兩個日期相減之后得到相隔的天數，除以7是week,除以24是hour,可以轉化成相應單位進行相加減

看圖判斷年份

RR 日期格式：用來判定按照DD-MM-RR格式給定的日期實際代表的日期是多少

從第一個表可以看出YY格式的年份和當前的年份屬于同一個世紀，不用做任何判斷。

RR格式的年份判斷規律：同小同大均為當前世紀，前小后大為上世紀，前大后小為下個世紀

1.當前日期為2015年，指定日期格式為DD-MON-RR,指定日期為01-1月-01，該日期實際所代表的日期為？

當前日期為小，指定日期為小，所以當前日期是2001年(當前世紀)

2.當前日期為2015年，指定日期格式為DD-MON-RR,指定日期為01-1月-95，該日期實際所代表的日期為？

當前日期為小，指定日期為大，所以實際日期是1995年(上個世紀)

3.當前日期為1998年，指定日期格式為DD-MON-RR,指定日期為01-1月-95，該日期實際所代表的日期為？

當前日期為大，指定日期為大，所以實際日期是1995年(當前世紀)

4.當前日期為1998年，指定日期格式為DD-MON-RR,指定日期為01-1月-01，該日期實際所代表的日期為？

當前日期為大，指定日期為小，所以實際日期是2001年(下個世紀)

months_between(sysdate,hiredate)：返回兩個日期類型數據之間間隔的自然月數

select ename,job,months_between(sysdate,hiredate) months --當前系統時間與入職時間的間隔月數

from emp

order by months

add_months(sysdate,n)：返回指定日期加上n個月后的日期

select empno,ename,job,add_months(hiredate,3) date --入職三個月后的日期

from emp

next_day：返回某一日期的下一個指定日期(有點繞，結合例子來看就懂了)

select next_day(sysdate,'星期一') NextMonday --下個周一是什么日期

from dual; --虛表

last_day：返回指定日期當月的最后一天的日期

select last_day(sysdate) LastDay from dual; --這個月的最后一天是什么日期

round(sysdate[,'format'])：將date按format格式進行四舍五入，默認四舍五入為最近的天

format：世紀CC,年YY，月MM,日DD，小時HH24，分MI，秒SS(這個需要稍微記一下)

select ename,job,round(hiredate,'MONTH') --入職日期按月四舍五入

from emp;

trunc(sysdate[,format])：將date按format格式進行截斷，和round差不多

--查詢81年入職的員工姓名，入職日期按月截斷的日期。

select ename,trunc(hiredate,'MONTH')

from emp

where substr(hiredate,-2,2) = '81';

extract([YEAR | MONTH | DAY] from date)：返回日期類型數據中的年份、月份和日期

--部門編號是10的部門中所有員工入職月份。

select extract(month from hiredate) from emp;

轉換函數

通常需要在字符類型、日期類型、數值類型之間進行顯式轉換，轉換函數及對應關系如下圖所示。

to_char(data,'format')：用于日期型

需要注意的是format大小寫敏感，可通過format提取日期的年or月or日

下面表格的內容可以稍微記一下

select empno,ename,job,to_char(hiredate,'DD MONTH YYYY') hiredate --17 November 1981

from emp;

to_char(number,'format')：用于數值型

select to_char(sal,'$99,999') salary

from emp;

to_number(char,'format')：要轉換的char類型數據必須是由數字組成的字符串。

to_date(char,'format')：要轉換的char類型數據必須是可以轉換成日期的字符。

select to_date('2019-1-10','YYYY-MM-DD') from dual;

通用函數

case函數

select ename,deptno,

(case deptno

when 10 then '銷售部'

when 20 then '技術部'

when 30 then '后勤部'

else '無'

end) deptname,

from emp;

decode函數

select ename,deptno,

decode(deptno,

10, '銷售部',

20,'技術部',

30,'后勤部'，

'無') deptname,

from emp;

多行函數

分組函數

分組函數是對表中一組記錄進行操作，每組只返回一個結果,即首先要對表記錄進行分組，然后再進行操作匯總，每組返

回一個結果，分組時可能是整個表分為一組，也可能根據條件分成多組。

min(column)：返回每組的最小值，適用于任何類型的數據

max(column)：返回每組的最大值，適用于任何類型的數據

avg(column)：返回每組的平均值，只能夠對數值類型的列或表達式操作

sum(column)：返回每組的總和，只能夠對數值類型的列或表達式操作

count(column)：返回滿足條件的每組記錄條數

查詢職位以SALES開頭的所有員工平均工資、最低工資、最高工資、工資和。

select avg(sal),min(sal),max(sal),sum(sal)

from emp

where job like 'SALES%';

組函數中DISTINCT

DISTINCT會消除重復記錄后再使用組函數

select count(distinct deptno) from dept;

分組函數中空值處理

除了count(*)之外，其它所有分組函數都會忽略列中的空值，然后再進行計算。

select avg(comm) from emp; --avg(comm) 500

在分組函數中使用NVL 函數可以使分組函數強制包含含有空值的記錄

select avg(nvl(comm,0)) from emp; --avg(nvl(comm,0)) ---157

需要注意的幾點

select 子句中出現的除分組函數之外的列都應寫在group by子句中

分組函數的篩選判斷放在having子句中，不能放在where子句中

上面這個圖很重要，多看幾遍

多表連接

多表連接指的是在多個表之間通過一定的連接條件，使表之間發生關聯，進而能從多個表之間獲取數據

語法格式

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

三點要求

* 在 WHERE子句中書寫連接條件。

* 如果在多個表中出現相同的列名，則需要使用表名(或表別名)作為來自該表的列名的前綴。

* N個表相連時，至少需要N－1個連接條件。

最后一個條件需要特別注意一下

笛卡爾積

什么是笛卡爾積

第一個表中的所有行和第二個表中的所有行都發生連接。

什么時候會產生笛卡爾積

連接條件被省略或者無效時，簡而言之，就是沒有連接條件。

如何避免笛卡爾積

在where子句中包含有效的連接條件

當from后面有多個表時，無論題意如何，連接條件一定要有，且需要N-1個(N為表的數量)

等值連接

兩個部門有相同的列，即可用等值連接，而且大概率會是用等值連接

select empno,ename,job,dname,loc

from emp,dept

where emp.deptno = dept.deptno;

非等值連接

兩個表不存在相同的列，但兩個表的列存在某種不可告人的關系

select ename,job,grade

from emp,salgrade

where sal between losal and hisal;--losal最低工資 hisal最高工資

自身連接

自身連接即自己跟自己連接，以別名區別

查詢每個員工的姓名和直接上級姓名？

SELECT worker.ename||' leader is '||manager.ename

FROM emp worker, emp manager

WHERE worker.mgr = manager.empno;

外部連接

在兩個表進行等值連接時，有些記錄按照連接條件沒有連接上，可在缺少匹配值的一邊加上(+)

語法

符號(+)所在邊的表增加一個“萬能”的行，這個行全部由空值組成。它可以和另一邊的表中所有不滿足連接條件的行進行連接。

由于這個“萬能”行的各列全部是空值，因此在連接結果中，來自“萬能”行屬性值全部為空值。

多表連接的寫法

分析要查詢的列都來自于哪些表，構成FROM子句;

分析這些表之間的關聯關系，如果表之間沒有直接關聯關系，而是通過另一個中間表關聯，則也要在FROM子句中補充中間關聯表;

接下來在WHERE子句中補充表之間的關聯關系，通常N個表，至少要有N-1個關聯關系;

分析是否還有其它限制條件，補充到WHERE子句的表關聯關系之后，作為限制條件;

根據用戶想要顯示的信息，補充SELECT子句。

分析是否有排序要求，如果排序要求中還涉及到其它表，則也要進行第2步補充排序字段所在的表，并且添加表之間的關聯關系;

表別名

在多表連接且有相同列時，使用表別名可以區分來自不同表但是名字相同的列。

在定義表別名之后，在對列進行限制時，只能使用表別名，不能使用表名。

SQL:1999語法的連接

語法

這張圖包含了SQL99所有連接方式的語法，看完后面的再來看這個表就不會一臉懵逼了

交叉連接

交叉連接是兩個表進行交叉相乘，和笛卡爾積的效果一樣，使用cross join子句。

SELECT emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc

FROM emp

CROSS JOIN dept;

自然連接

自然連接類似于等值連接，對兩個表之間所有相同名字和數據類型的列進行等值連接，使用natural join子句連接。

SELECT empno,ename,sal,deptno,loc

FROM emp

NATURAL JOIN dept;

USING子句

自然連接是使用所有名稱和數據類型相匹配的列作為連接條件，而USING子句可以指定用某個或某幾個相同名字和數據類型的列作為連接條件。

SELECT e.ename,e.ename,e.sal,deptno,d.loc

FROM emp e JOIN dept d USING (deptno)

WHERE deptno = 20 ;

使用USING子句創建連接時，應注意以下幾點：

如果有若干個列名稱相同但數據類型不同，自然連接子句可以用USING子句來替換，以指定產生等值連接的列。

如果有多于一個列都匹配的情況，使用USING子句只能指定其中的一列。

USING子句中的用到的列不能使用表名和別名作為前綴。

NATURAL JOIN子句和USING子句是相互排斥的，不能同時使用。

ON子句

自然連接條件基本上是具有相同列名的表之間的等值連接

如果要指定任意連接條件,或指定要連接的列，則可以使用ON子句；

用ON將連接條件和其它檢索條件分隔開，其它檢索條件寫在WHERE子句；

ON子句可以提高代碼的可讀性。

code

SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc

FROM emp e

JOIN dept d

ON (e.deptno = d.deptno); --我覺得很像where

左外連接

左外連接以FROM子句中的左邊表為基表，該表所有行數據按照連接條件無論是否與右邊表能匹配上，都會被顯示出來，是不是覺得很像外部連接。

SELECT e.ename,e.deptno,d.loc

FROM emp e

LEFT OUTER JOIN dept d

ON (e.deptno = d.deptno);

右外連接

右外連接以FROM子句中的右邊表為基表，該表所有行數據按照連接條件無論是否與左邊表能匹配上，都會被顯示出來。

SELECT e.ename,e.deptno,d.loc

FROM emp e

RIGHT OUTER JOIN dept d

ON (e.deptno = d.deptno);

全外連接

返回兩個表等值連接結果，以及兩個表中所有等值連接失敗的記錄

SELECT e.ename,e.deptno,d.loc

FROM emp e

FULL OUTER JOIN dept d

ON (e.deptno = d.deptno);