Oracle的監聽服務：OralceOraDB12Home1TNSListener 需要通過程式連結資料庫進行開發的時候，此服務必須開啟，如果只是在本機使用，此服務可不啟動
Oracle 的例項服務：OracleServiceORCL，預設啟動
1.1資料庫的連結訪問
執行SQL Plus，輸入使用者名稱和密碼。
如果需要解鎖賬戶，開啟cmd，輸入sqlplus / as sysdba。在SQL提示符後面輸入alter user system identified by root;alter user system account unlock;
再次輸入使用者名稱和密碼，顯示成功。
訪問oracle的web端：https://localhost:5500/em/login
如果需要登入sys使用者，使用者名稱輸入sys /as sysdba。
這樣，就建立了虛擬機器上的oracle連線。
2.Oracle資料庫介紹
oracle檔案中，字尾名為ora或dbf的表示資料檔案，為ctl的是控制檔案，為log的
是日誌檔案。
Oracle資料庫指的是一個庫。
一個oracle例項有一系列的後臺程式和記憶體結構組成。一個Oracle資料庫可以有多個例項。
使用者是建立在例項下的，不同的例項可以建立相同名字的使用者 。
表空間是oracle對物理資料庫上相關資料的邏輯對映，一個資料庫在邏輯上可以被劃分為一個或者若干個表空間，每一個表空間包含了在邏輯上相關的一組結構。
每一個資料庫例項至少有一個表空間（system表空間），每一個表空間有同一磁碟上的一個或多個檔案組成，這些檔案叫資料檔案，一個資料檔案只能屬於一個表空間。
資料檔案是資料庫的物理儲存單位。資料庫的資料是儲存在表空間中，實際是在一個或者多個資料檔案中。一旦資料檔案被加入到某一個表空間後，就不能刪除這個檔案。
如果要刪除某個資料檔案，只能刪除其所屬的表空間才可以刪除表的資料，是由使用者放入某一個表空間的，而這個表空間會隨機的把這些資料放到一個或者多個資料檔案中。
oracle數由使用者和表空間對資料進行管理和存放的。表不是由表空間查詢的，而是由使用者去查詢。
oracle資料庫對例項：一對多
表空間和使用者隸屬於例項。
使用者對錶空間：多對一
表空間對資料檔案：一對多
表對資料檔案：多對多
3.Oracle12C建立例項資料庫
3.1建立使用者並分配許可權
登入客戶端，新建命令視窗。
-- 建立scott使用者 create user scott identified by tiger; --為scoot賦予許可權 Grant connect,RESOURCE,UNLIMITED TABLESPACE TO scott; --設定使用者使用的表空間 ALTER USER scott default tablespace users; --設定使用者的臨時表空間 Alter user scott Temporary tablespace temp;
create user 使用者名稱 identified by 密碼;
grant xxx to 使用者名稱;xxx可以是連線許可權，資源許可權，無限制的表空間許可權。如果沒有分配無限制表空間，則必須制定一個值。
alter user 使用者名稱 default tablespacce 預設表空間名。
alter user 使用者名稱 temporary tablespacce 暫時表空間名。
3.2建立例項庫和增加示例資料
前提：該使用者必須具備建立表的許可權。
oracle的資料型別
varchar2(size) 可變長字串
char(size) 定長字串
number(p,n) 可變長數值型別
date 日期型
long 可變長字元資料，最大可達2G
clob 字元資料，最大可達4G
blob 二進位制資料，最大可達4G
3.3DDL語句
修改表：alter table
追加列：add(列名 列型別 限定條件);
修改列：modify(列名 列型別 限定條件);
刪除列：drop column列名;
重新命名列：rename column 原來列名 to 新列名;
刪除表：
drop table 表名;
清空表：
delete from 表名;
truncate table 表名;
delete和truncate的區別：delete刪除的資料在未提交之前，可以回滾，不釋放佔有的空間和資源；truncate刪除的資料，刪除之後不可回滾，釋放佔有的儲存空間和資源。
重新命名錶：
rename 表名 to 新表名;
4 SQL Select
4.1 SQL概述
SQL是一門語言 ：結構化查詢語言
SQL是最重要的關係型資料庫操作語言，是所有的關係型資料庫管理系統的標準語言。
關係型資料庫： oracle mysql sqlserver
nosql ： redis mongdb Hbase
SQL 語言是一種非過程化語言，只需要提出做什麼，而不需要指明怎麼
SQL語言的作用：
1 可以對資料庫的資料進行增刪改查操作（CRUD）
2 資料庫的物件進行建立 修改 刪除操作。
3 使用者賦許可權 取消許可權 角色賦許可權 取消許可權 給使用者關聯角色
4 事務控制
4.2 SQL的分類
DML:資料操縱語言
DDL：資料定義語言
DCL：資料控制語言
4.2.1 DML
DML 用於查詢與修改資料記錄 包括：
-
insert 新增資料到資料庫中
-
update 更新資料庫中的資料
-
delete 刪除資料庫中的資料
-
select 查詢資料庫中的資料
4.2.2.DDL
用於定義資料看的結構 比如建立 修改或刪除資料庫物件。包括：
-
Create Table 建立資料庫表
-
ALTER TABLE ：更改表結構 新增 刪除 修改列
-
DROP Table 刪除表
-
CRATE INDEX :在表上建立索引
-
drop index 刪除索引
-
CREATE VIEW 建立試圖
-
drop view 刪除試圖
4.2.3 DCL
用來控制資料庫的訪問，包括：
-
GRANT 授予許可權
-
REVOKE 撤銷、回收許可權
-
COMMIT 提交事務
-
Rollback 回滾事務
-
SAVEPOINT 設定儲存點
-
lock：對資料庫的特定部分進行鎖定
4.3 SQL語句的書寫規範
1 使用大小寫規範提供詞義的識別能力
-
在名稱中僅使用字母 數字 下劃線
-
列明 引數 變數等標量小寫
-
模式物件名首字母大寫：表 試圖 儲存過程 函式 觸發器
-
保留關鍵字大寫
2 使用空格提供良好的語言標記區分
等號的兩邊要使用空格
在逗號的後面使用空格
3 使用縮排提高語句的邏輯層次表達能力
4 使用垂直空白提供關鍵字和引數的區分能力
5 註釋：
單行註釋：--
多行註釋：/* */
mysql中的註釋是：#
5 基本的Select 語句
select *| { [ditinct] columnname|expression [alias],..} from tablename;
SELECT 標識選擇的列
FROM 標識從那個表查詢
5.1選擇全部列
SELECT * FROM dept;--* 表示查詢所有的列 SELECT deptno,dname,loc from dept;
5.2 選擇特定的列
SELECT empno,ename,job,sal from emp;
注意：
-
SQL語言大小寫不敏感
-
SQL可以寫在一行或者多行
-
關鍵字不能被縮寫也不能分行
-
各個子句一般要分行寫
-
使用縮排來提供程式碼的可讀性
5.3 使用運演算法
5.3.1 數學運算子：
數字和日期使用的算術運算子： + - * /
-- 查詢所有的員工的姓名 職位 薪資 為每個員工漲薪1000元 SELECT ename, job, sal, sal + 1000 FROM emp;
5.4 操作符的優先順序
+ - * /
-
先乘除後加減
-
同一優先順序運算子從左往右依次運算
-
括號內的運算先執行
--查詢所有的員工的姓名 職位 薪資 為每個員工漲薪1000元 並計算漲薪後的年薪 SELECT ename,job ,sal, (sal + 1000) * 12 FROM emp;
5.5 定義空值
-
空值指的是null
-
空值是無效的 ，如果一個列沒有指定值，或者是未知的 或者是不可預知的值
-
空值不是空格也不是0
-- 查詢所有的員工的姓名 職位 薪資 和獎金 SELECT ename, job, sal,comm FROM emp;
5.5.1 空值在數學運算中的使用
-- 查詢所有的員工的姓名 職位 薪資 和獎金 以及年收入（薪資 * 12 ） + 獎金 SELECT ename,job,sal,comm, sal * 12 + comm FROM emp;
包含空值的數學表示式的值為空值
5.6 列的別名
別名緊跟列明之後，之間需要保留一個空格。也可以在別名和列明之間加入關鍵字 AS 別名可以使用雙引號，以便在別名中包含空格或特殊的字元 並區分大小寫
--查詢所有的員工的姓名 職位 薪資 和獎金 以及年收入（薪資 * 12 ） + 獎金 SELECT ename AS name, job,sal salary,comm, sal *12 AS "year salary" FROM emp;
5.7. 連線符 ||
--查詢所有員工的姓名 職位 薪資 作為員工的個人基本資訊 拼接在一起 SELECT ename || '-' ||job || '-' ||sal AS "Employee info" FROM emp;
拼接後的值成為一列
5.8 字串
-
字串可以是SELECT語句中的一個字元 數字 日期
-
日期 和字元只能出現在單引號中
-
每當返回一行時，字串被輸出一次
5.9 重複行
--查詢EMP表中所有僱員的部門編號 distinct SELECT distinct deptno from emp;
6.SQL 和SQL plus
SQL：一種語言 控制資料庫中的資料和結構的定義
SQL *PLUS 一種環境，提供了sql語句執行的一個環境
6.2 字元和日期
字元和日期 要包含在單引號中
字元大小寫敏感的 日期格式敏感
預設的日期格式 ：DD-MON月-RR
--查詢20部門的所有僱員資訊
select * from emp where deptno=20; -- 查詢姓名為SMITH的僱員的基本資訊 此時的字串的匹配 是嚴格匹配大寫小的 select * from emp where ename='SMITH'; -- 查詢入職日期為17-12月-80的僱員資訊 SELECT * FROM emp WHERE hiredate='17-12月-80';
6.3 比較運算
與java中的關係運算子型別，注意相等判斷是=。
6.4 其他比較運算子
between ... and 介於...之間
in(值1,值2,值3，...) 等於其中的任意一個
like 模糊查詢 匹配查詢
is null 值為空
6.5 邏輯運算
and 且
or 或
not 非
sql語法中沒有短路與和短路或
6.6運算子的優先順序：
算術運算子 < 連線符 < 比較符 < is null 、like ，in < BETWEEN AND < NOT <AND <OR
7排序
ORDER BY子句
ASC:升序 從小到大
DESC： 降序 從大到小
order by 子句在select 語句的結尾
8 SQL函式
函式就是java中的方法 ：函式有輸入有輸出 輸入就是引數 輸出就是結果 而且輸入可以是多個 輸出的結果只有一個
SQL函式分為：單行函式和多行函式
8.1單行函式
單行函式：只能對一行進行變換，每行返回一個結果。可以轉換資料型別 在使用的時候 還可以巢狀。引數可以是一列或一個值。
單行函式： 字元 數值 日期 轉換 通用
8.2字元函式
-
控制大小寫： LOWER UPPER INITCAP
-
分別返回全小寫、全大寫、首字母大寫的值
-
-
字元的控制函式： CONCAT SUBSTR LENGTH TRIM LTRIM RTRIM LENGTHB
-
拼接、子串、長度、修剪空格
-
SUBSTR(c1,n1[,n2]) 取c1字串的子串，從n1位置開始取，取n2個，沒有n2引數為取全部。取到n1+n2-1下標的位置。
-
LENGTH：全形、漢字按1個字元算 LENGTHB：全形、漢字按2個字元算
-
SUBSTR和SUBSTRB同理
-
NLS_LOWER、NLS_UPPER 、NLS_INITCAP，第二個引數是Nls_param
-
Length函式返回字元的個數，使用定義是給定的字符集來計算字元的個數；LENGTHB給出該字串的byte；LENGTHC使用純Unicode；LENGTH2使用UCS2；LENGTH4使用UCS4
-
-
INSTR、INSTRB、LPAD、RPAD REPLACE
-
INSTR(C1,C2[,I[,J]])：查詢c2在c1中出現的位置，i代表從第幾個下標開始查，j代表出現第幾次 i和j預設為1
-
LPAD(c1,n[,c2])：在左邊用c2填充c1，使其長度達到n。c2預設為空格
-
RPAD(c1,n[,c2])同理
-
REPLACE(c1,c2[,c3])：在c1中，將c2替換為c3，預設c3為空即刪除c2
-
-
SOUNDX、TRANSLATE
-
SOUNDEX(c1)返回字串的語音表示形式
-
TRANSLATE(c1,c2,c3)：在c1中，將c2順序換成對應的c3順序。是部分的替換。
-
-
ASCII CHR
-
ASCII(x1)：返回x1的ASCII碼，是數值型。如果最左端是漢字，只取漢字最左半邊字元的ASCII 碼
-
CHR(n1）：返回n1對應的字元，是字元型。n1屬於[0,255]
-
8.3數值函式
-
ABS SIGN MOD
-
abs(x)：返回絕對值
-
sign(x)：符號函式，返回1，-1，0。
-
mod(x,y)：返回x mod y
-
-
ROUND TRUNC CEIL FLOOR
-
round(x,[y])：返回四捨五入的值。如果y為正數，四捨五入為y位小數。如果y為負數，整數部分低y位四捨五入。
-
trunc(x,[y]):返回x按精度y擷取後的值,但是不四捨五入
-
ceil(x):返回大於等於x的最小整數值
-
floor(x):返回小於等於x的最大整數值
-
-
POWER EXP LOG LN SQRT
-
power(x,y)：返回x的y次冪
-
exp(y)：返回e的y次冪
-
log(x,y)：返回以x為底的y的對數
-
ln(y)：返回以e為底的y的對數
-
sqrt(x)：返回x的平方根
-
-
三角函式
-
返回一個數字的正弦sin、雙曲正弦sigh、餘弦cos、反餘弦cosh、正切tan、雙曲正切tanh、反正弦asin、反餘弦acos、反正切值atan
-
8.4日期函式
-
add_months last_day months_between next_day
-
add_months(d1,n1)：返回日期d1加上n1個月後的日期
-
last_day(d1)：返回日期d1所在月份最後一天的日期
-
months_between(d1,d2)：返回d1日期和d2日期之間的月數
-
next_day(d1[,c1])：返回d1日期下週，星期是c1字串的日期。
-
-
new_time round trunc extract localtimestamp
-
new_time(dt1,c1,c2)：給出時間c1時區的dt1時間，對應c2時區的日期和時間
-
round()：給出日期d1按期間(引數c1)四捨五入後的期間的第一天日期
-
trunc(d1[,c1]):返回日期d1所在期間(引數c1)的第一天日期，c1可以是day，month，q，year等等。
-
localtimestamp:返回會話中的日期和時間
-
extract(c1 from d1):日期/時間d1中，引數c1的值。比如
select extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小時 from dual;
-
-
current_time current_date sysdate
-
sysdate 返回當前日期
-
current_time :以timestamp with time zone資料型別返回當前會話時區中的當前日期
-
current_date:返回當前日期
-
-
dbtimezone sessiontimezone interval
-
dbtimezone：返回時區
-
sessiontimezone：返回會話時區
-
INTERVAL c1 set1:變動時期時間數值。c1 ”量“，set1起作用的時間單位
-
8.5轉換函式
-
chatorowid rowidtochar convert hextoraw
-
chatorowid(c1): 轉換varchar2型別為rowid型別。每一條記錄都有一個rowid，rowid在整個資料庫中是唯一的，rowid確定了每條記錄是在Oracle中的哪一個資料檔案、塊、行上。
-
rowidtochar(rowid):轉換rowid值為varchar2型別
-
CONVERT(c1,set1,set2):將字串c1從字符集set2轉換到另一個字符集set1。
-
hextoraw(c1)：講一個十六進位制構成的字串轉換為二進位制
-
-
to_char to_date to_number to_multi_byte to_single_byte
-
to_char(x[[,c2],C3]):將日期或資料轉換為char型別。x是日期或資料，c2是格式引數，c3是nls設定引數。
-
to_date(X[,c2[,c3]]):將字串x根據c2的格式轉換為日期型別
-
to_number(X[[,c2],c3])：將字串x根據c2的格式轉換為數字型別
-
to_multi_byte(c1)：單位元組轉換為多位元組（半形轉換為全形）
-
to_single_byte(c1)：多位元組轉換為單位元組（全形轉換為半形）
-
-
nls_charset_id nls_charset_name
-
nls_charset_id(c1)：返回字符集名稱對應的id
-
nls_charset_name(id):返回字符集id對應的名稱
-
8.5.1 to_char詳細使用
to_char函式對日期的轉換：to_char(date,'format_model')
必須包含單引號 其中的format_model是大小寫敏感
可以包含任意的日期格式
數值型轉換為字元型 to_char：to_char(123.45,'$999,999.00')->￥123.45
9 表示數字 0 表示0 $ 表示美元 L 表示本地貨幣 .表示小數點
8.6聚組函式（多行函式）
-
avg sum stddev variance count max min
-
avg([distinct|all]x)：返回平均值。all表示對所有的值求平均值,distinct只對不同的值求平均值，預設為all。如果有引數distinct或all，需有空格與x(列)隔開。
-
sum([distinct|all]x):返回總和
-
stddev([distinct|all]x)：返回標準誤差（標準差）
-
variance([distinct|all]x)：返回方差
-
count(*|[distinct|all]x)：返回個數
-
max([distinct|all]x)：返回最大值
-
min([distinct|all]x)：返回最小值
-
-
注意事項
-
group by語句中：如果查詢結果中有列，那麼列要麼是以聚組函式的引數出現，要麼是以group by後面的列名存在。
-
where後面不能出現group by和聚組函式 因為違反了先分組後過濾的原則
-
分組過濾：having having的過濾條件出現在group by後
-
-
在組函式中使用nvl函式 count(nvl(comm,0)) ，會將被忽略的null值轉化為數值參與運算
-
去重統計count(distinct deptno)
8.7分析函式
-
值域窗(RANGE WINDOW) range n preceding 僅對數值或日期型別有效,選定窗為排序後當前行之前,某列(即排序列)值大於/小於(當前行該列值 –/+ N)的所有行,因此與ORDER BY子句有關係。
-
行窗(ROW WINDOW) rows n preceding 選定窗為當前行及之前N行。 還可以加上BETWEEN AND 形式,例如row between m preceding and n following
-
使用函式分析窗中的內容
-
分組求和：group by
-
grouping sets(x,xx,xxx):等效於分別按照x,xx,xxx分組得出結果後，再並起來的結果
-
rollup(x,xx,xxx)：等效於分別按照(x,xx,xxx),(x,xx),(x),null分組得出結果後，再並起來的結果
-
cube(x,xx,xxx)：等效於grouping sets()，引數是它的所有子集
-
grouping ：用於測試該行是不是前三種方式新增出來的，是則值為1，不是值為0。
-
8.7.1 sum(...) over(....)
sum(exp)over(分割槽/排序)：連續求和，只有最後一行的值才是sum()。第二行就是第一行+第二行。
8.7.2 rank() dense_rank()
rank()over(條件/排序/分割槽)
dense_rank()over(條件/排序/分割槽)
dense_rank在並列關係是，相關等級不會跳過。rank則跳過。 rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內） dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名。
8.7.3 row_number()
row_number()over(排序)：先執行排序，再返回排序後的行號
select name,seqno,description from(select name,seqno,description,row_number() over (partition by name order by seqno) id from table_name) where id<=3;
按照名字分割槽，再按照seqno升序排序得到排名id，只返回id前2的結果，最終返回過濾id後的結果
8.7.4 lag() lead()
lag(expr,offset,default) lead(expr,offset,default)
lag(列)：取上一個該列值作為值
lead(列)：取下一個該列值作為值
OFFSET是預設為1 的正數，表示相對行數。希望檢索的當前行分割槽的偏移量
default是超出範圍時自動返回的值，預設是null
8.8其它函式
-
DUMP greatest least nvl nvl2
-
dump(w[,x[,y[,z]]])：返回資料型別、位元組長度和在內部的儲存位置.
-
greatest(exp1,exp2,exp3,……,expn)：返回表示式中最大的一個。如果表示式型別不相同， 會隱含轉換為第一個表示式的型別
-
least(exp1,exp2,exp3,……,expn):返回表示式中最小的一個。如果表示式型別不相同， 會隱含轉換為第一個表示式的型別
-
nvl(exp1,exp2):返回不為null的值
-
nvl2(expr1, expr2, expr3) ：expr1不為NULL，返回expr2；expr2為NULL，返回expr3。expr2和expr3型別不同的話，expr3會轉換為expr2的型別
-
-
user uid userenv
-
user：返回當前會話的資料庫使用者名稱
-
uid：返回當前會話的資料庫使用者id
-
userenv(param):返回當前會話上下文屬性的值
-
-
nullif coalesce
-
nullif(expr1, expr2):expr1和expr2相等返回null，不相等返回expr1。
-
coalesce(c1, c2, ...,cn)：返回列表中第一個非空的表示式，如果所有表示式都為空值則返回1個空值
-
-
rownum bfilename vsize
-
rownum：返回當前行號
-
bfilename:函式返回一個空的BFILE位置值指示符，函式用於初始化BFILE變數或者是BFILE列。
-
vsize：返回x的大小（位元組）數
-
-
decode case...when...then...end
-
decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,預設值)
decode (條件，
值1，翻譯值1，
值2，翻譯值2，...
值n，翻譯值n，
預設值)
-
case [<表示式>] when <表示式條件值1> then <滿足條件時返回值1> [when <表示式條件值2> then <滿足條件時返回值2> …… [else <不滿足上述條件時返回值>]] end
-
case有兩種形式，一種是提前列名，case 列名，when後面只有值。另一種是case後面接when when後面是表示式 列名=值
-
-
sys_guid sys_context dbms_random
-
sys_guid():生產32位，十六進位制的隨機數字（A-F大寫）
-
sys_context(c1,c2)：返回系統c1對應的c2的值
-
dbms_random包
-
dbms_random.value:無參方法，這種用法沒有引數，會返回一個具有38位精度的數值，範圍從0.0到1.0，但不包括1.0。第二種value帶有兩個引數，第一個指下限，第二個指上限，將會生成下限到上限之間的數字，但不包含上限
-
dbms_random.string(c1,len):第一個引數有'u'-大寫，'l'-小寫，'a'-混合大小寫，‘x’-大寫字母和數字字元,'p'-返回的都是可讀字元
-
dbms_random.random：返回一個隨機大小的，binary_integer值資料
-
dbms_random.normal：返回符合正態分佈的數。這個函式返回的數值中有68%是介於-1與+1之間，95%介於-2與+2之間，99%介於-3與+3之間。
-
dbms_random.send：用於生成一個隨機數種子,設定種子的目的是可以重複生成隨機數，用於除錯。否則每次不同，難以排程。
-
-
-
獲取主機名和IP地址 utl_inaddr包：
-
utl_inaddr.get_host_address：獲取IP
-
utl_inaddr.get_host_name：獲取主機名
-
utl_inaddr包需要給使用者分配ACL許可權並建立ACL檔案。
-
9多表查詢
多表查詢要克服笛卡爾積的問題，需要使用where設定關聯條件。
等值連線：where後面的邏輯運算子是=。
非等值連線：where後面使用其他邏輯運算子。
內連線：結果集中只包括匹配且關聯列不為null的行。
外連線：結果集中也可以匹配關聯列為null的行。
外連結的實現：（1）關鍵字left outer join /right outer join/full outer join
（2）在where子句的列後面加上'(+)'，表示這個表可以有null，另一個表全部顯示
全部顯示的表是主表，另一個表是從表。
(+)在左邊是右外連線。
（3）join 關鍵字後面用on不用where。(+)前面只能用where
叉集：cross join 得到笛卡爾積
自然連線：natural join 以表中具有相同名字的列作為條件，建立等值連線。
使用using子句建立連線：using(列名)，using與join連用，不需要給表取別名
使用on子句建立連線：on與join連用，可以進行多表連線
10子查詢
子查詢：where後面的子句是另一個查詢的結果，稱為子查詢
子查詢優先於父查詢執行
多行子查詢、巢狀子查詢
子查詢修飾符：
-
in等於列表中的任意一個
-
any和子查詢返回的某一個值比較
-