1 Oracle的服務
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和子查詢回傳的某一個值比較
-
all和子查詢回傳的所有值比較
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/308504.html
標籤：Oracle
上一篇：ORCAL 臨時表創建，洗掉