18Oracle入門
- 2021 年 8 月 4 日
- 筆記
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端：//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和子查詢返回的某一個值比較
-
