Oracle11g基礎學習---------（2）SQL語言基礎

學習Oracle從哪裡入手呢，在這裡，我将帶你走進Oracle的大門，一天一章，7天入門不是夢  
1. SQL語言概述
1.1  SQL語言分類
資料定義（Data Definition Language，DDL）
用于建立、修改和删除資料庫對象，如CREATE TABLE、ALTER TABLE、DROP TABLE等。DDL語句會自動送出事務 
資料操縱語言（Data Manipulation Language，DML）
用于操縱資料庫，包括INSERT、UPDATE、DELETE、SELECT等
資料控制語言（Data Control Language，DCL）
用于執行授予權限和撤銷權限的操作，包括GRANT（授予權限）、REVOKE（撤銷權限）兩條指令。DCL語句會自動送出事務  
事務控制語言（Transactional Control Language，TCL）
用于維護資料的一緻性，包括COMMIT（送出事務）、ROLLBACK（復原事務）和SAVEPOINT（設定儲存點）3條語句
1.2 SQL語句編寫規則
SQL關鍵字不區分大小寫，既可以使用大寫格式，又可使用小寫格式，或者大小寫混用 
對象名與列名不區分大小寫 
字元值與日期值區分大小寫 
在SQL*Plus中，每條SQL語句要以分号結束。如果語句比較長，可以将語句文本分布到多行上，最後以分号結束 
           

2. 使用DDL語句
2.1 Oracle 11g常用的資料類型
字元類型
CHAR類型
CHAR表示固定長度字元串，長度不夠的用空格補充，最多可以存儲2000位元組
CHAR類型區分中英文，中文在CHAR中占兩個位元組，而英文隻占一個位元組
VARCHAR2類型
VARCHAR2表示可變長度字元串，最多可以存儲4000位元組
在定義該資料類型時，應該指定其大小。與CHAR類型相比，使用VARCHAR2可以節省磁盤空間
數值類型
NUMBER類型可以存儲正數、負數、零、定點數和精度為38位的浮點數 
NUMBER(M,N)。其中，M表示精度，代表數字的總位數；N表示小數點右邊數字的位數 
日期類型
DATE類型
DATE資料類型用于存儲表中的日期和時間資料，取值範圍是公元前4712年1月1日至公元9999年12月31日
DATE類型的長度是7，7個位元組分别表示世紀、年、月、日、時、分和秒
TIMESTAMP類型
TIMESTAMP資料類型用于存儲日期的年、月、日以及時間的小時、分和秒值
其中，秒值精确到小數點後6位，該資料類型同時包含時區資訊
2.2 CREATE TABLE指令
CREATE TABLE用于建立表。在建立表時，經常會建立該表的主鍵、外鍵、唯一限制、Check限制等
2.3  ALTER TABLE 指令
向已經建立的表中增加一個新列 
alter table tableName add columnName dataType
修改表中指定列的資料類型和類型長度 
alter table tableName modify column columnName dataType
删除表中指定的列 
alter table tableName drop columnName


2.4 TRUNCATE TABLE指令
RUNCATE TABLE指令用于删除表的所有内容，并釋放表使用的存儲空間，但不删除表結構
文法
truncate table tableName
TRUNCATE TABLE指令可以快速删除表的記錄并釋放空間，不使用事務處理，速度快且效率高，但無法復原事務
DELETE指令可以在執行删除之後通過ROLLBACK撤銷删除
2.5 DROP TABLE 指令
DROP TABLE指令用于從資料庫中删除表及全部資料
3. 使用DML語句
3.2 UPDATE指令 
3.3 DELETE 指令
DELETE指令用于删除表的内容
4. 使用SELECT語句
4.1  示例資料庫概述
安裝Oracle 11g建立資料庫的環節中，如果在“指定資料庫配置詳細資料”視窗的“示例方案”頁籤中選擇了“建立帶樣本方案的資料庫”複選框，就會安裝新的使用者示例資料庫 
Oracle的示例方案都基于一個虛拟的通過各種管道銷售物質的公司 
其中，HR方案是基本的關系資料庫方案，用于介紹最簡單和最基本的話題 
該方案中包含7張表：雇員（employees）、部門（departments）、地點（locations）、國家（countries）、地區（regions）、崗位（jobs）和工作履曆（job_history）
4.2 HR示例方案簡介
預設情況下，所有示例方案的賬戶都已被鎖定，它們的密碼是過期的，不能連接配接到資料庫
解除HR使用者的鎖定狀态
4.3 HR示例方案中的表結構
4.4 子查詢
4.5 
在DDL、DML語句中使用子查詢
在CREATE TABLE語句中使用子查詢 
在INSERT語句中使用子查詢 
在DELETE語句中使用子查詢
在UPDATE語句中使用子查詢 
5. 使用DCL資料控制語言
5.1  GRANT
如果使用者在自己的模式中建立了一張表，則該使用者即為此表的所有者，擁有對這張表的所有操作權限 
除非該使用者希望與其他使用者共享這張表，否則無須為其他使用者賦予該表的任何操作權限 
可以通過GRANT指令為其他使用者授予該表的某些操作權限，這樣的權限被稱為對象權限 
文法
GRANT privileges ON object-name TO userName
5.2 REVOKE
REVOKE指令用于撤銷已經授予的使用者對象權限 
文法
REVOKE privileges ON object-name FROM userName
6. 使用TCL事務控制語言
事物的開始和結束
事務開始
連接配接到資料庫，并開始執行一條DML（UPDATE、INSERT、DELETE）語句
前一個事務結束或者執行一條會自動送出事務的語句
事務結束
執行一條COMMIT（事務送出）或ROLBACK（事務復原）語句
執行一條會自動送出事務的語句
執行一條DML語句卻失敗了
自動送出事務
執行一條DDL語句（如CREATE TABLE、ALTER TABLE等）
執行一條DCL語句（如GRANT、REVOKE等）
斷開與資料庫的連接配接。例如，在SQL*PLUS中執行一條DISCONNECT指令，或在同一個SQL*PLUS中執行一條CONNECT指令啟動一個會話，或者執行EXIT指令退出SQL*PLUS等


6.2 使用COMMIT指令送出事務
會話A向emp表中插入一條記錄，在送出之前，另一個會話B無法檢視到新的記錄。
隻有在會話A送出事務之後，會話B才可以檢視新增加的記錄，這實際上就是事務的隔離性


6.3 使用ROLLBACK復原事務
6.4 復原部分事務
7. 使用基本函數
dual表
dual表是Oracle資料庫中的一個虛表，它有一行一列，所有者是SYS使用者，但可以被資料庫中的所有使用者使用
不能向該表插入資料，但可以使用該表來選擇系統變量，或者求一個表達式的值
單行函數
日期函數
ADD_MONTHS函數：用于傳回指定的日期加上指定的月數後的日期值
MONTHS_BETWEEN函數：用于傳回兩個日期之間的月份數
LAST_DAY函數：用于傳回指定日期對應月份的最後一天
NEXT_DAY函數：用于傳回指定下一個星期幾的日期
EXTRACT函數：用于提取日期中的特定部分


字元函數
函數名  功能  示例  輸出結果 
LOWER（c）  小寫轉換  select lower('SccE') 
from dual;  scce 
UPPER(c)  大寫轉換  select upper('scce') 
from dual;  SCCE 
LTRIM（c1 [,c2]）  去掉c1左邊所包含的c2中的任何字元。當遇到不是c2中的字元時結束，并傳回剩餘字元串。若c2省略，則預設為空格  select ltrim('adminscce','ad') from dual;  minscce 
RTRIM（c1 [,c2]）  去掉c1右邊所包含的c2中的任何字元。當遇到不是c2中的字元時結束，并傳回剩餘字元串。若c2省略，則預設為空格  select rtrim('adminscce','scce') from dual; admin 


函數名  功能  示例  輸出結果 
REPLACE(c1,c2 [,c3]) 将c1中出現的c2都替換成c3，并傳回剩餘的字元串。c3預設為null。如果c3為null，則所有出現c2的地方的字元都被删除；如果c2為null，則傳回c1；如果c1為null，則傳回null select replace('a*b*','*') from dual; ab
INSTR(c1,c2) 在c1中搜尋c2首次出現的位置，如果找不到則傳回零 select instr('abcdbc','bc') from dual; 2
SUBSTR(c,m [,n]) 傳回c的子串，其中m是子串的開始位置，n是子串的長度。如果省略n，則表示截取從m位置開始的所有子串 select Aubstr('scce',2,2) from dual; cc
CONCAT(c1,c2) 連接配接字元串 select concat('scc','e') from dual; scce
LENGTH(c) 傳回字元串長度 select length('scce') from dual; 4


數學函數
函數名  功能   示例      結果 
ABS  取絕對值  select abs(-1) from dual;   1 
CEIL  向上取整  select ceil(1.33) from dual;   2 
FLOOR  向下取整  select floor(1.33) from dual;   1 
POWER  求幂   select power(2,3) from dual;   8 
MOD  取餘   select mod(10,3) from dual;   1 
ROUND  四舍五入  select round(12.126,2) from dual;  12.13 
TRUNC  截斷   select trunc(12.126,2) from dual;  12,12 


轉換函數
TO_CHAR：用于将日期或數字以指定的格式轉換為VARCHAR2資料類型的值
文法
TO_CHAR(d|n [,fmt])
d表示日期，n表示數字，fmt是指定日期或數字的格式
轉換函數 
TO_NUMBER：将包含數字的字元串轉換為NUMBER資料類型，進而可以對該資料類型執行算術運算。
文法
TO_NUMBER(char)
通常不需要進行轉換，因為Oracle可以對數字字元串進行隐式轉換
其他函數
NVL：用于将空值替換為指定值
文法
NVL(expr1,expr2)
如果expr1不是NULL，則傳回expr1；否則傳回expr2
向HR方案中的employees表中添加一個關于獎金的字段bonus，該字段充許為空。現要求計算雇員的實發工資，實發工資=基本工資+獎金
其他函數
NVL2：與NVL類似，隻是參數有所不同
文法
NVL2(expr1,expr2,expr3)
如果expr1不是NULL，則傳回expr2的值；否則傳回expr3的值
其他函數 
DECODE：DECODE函數相當于一條件語句（IF），它将輸入數值與函數中的參數清單相比較，根據輸入值傳回一個對應值
文法
DECODE(input_value,value,result[,value,result…][,default_result]);
input_value表示試圖處理的數值。DECODE函數将該數值與一系列的序偶相比較，決定最後的傳回結果
value表示一組成序偶的數值。如果輸入數值與之比對成功，相應的結果将被傳回
result表示一組成序偶的結果值
default_result表示未能與任何一序偶比對成功時函數傳回的預設值
為employees表中的雇員按表需求增加工資，并确認更新結果.


 總結
VARCHAR2類型為Oracle常用的可變長度字元類型
CLOB資料類型用于存儲可變長度的字元資料，BLOB資料類型用于存儲較大的二進制對象
Oracle中的子查詢可以分為單行子查詢、多行子查詢、多列子查詢、相關子查詢等
GRANT指令用于授予對象權限，REVOKE指令用于撤銷權限
COMMIT指令用于送出事務，ROLLBACK指令用于復原事務
TO_CHAR函數用于将日期或數字以指定的格式轉換為VARCHAR2資料類型的值
DECODE函數相當于條件語句（IF），它将輸入數值與函數中的參數清單相比較，根據輸入值傳回一個對應值



表空間是資料庫中最大的邏輯機關，一個 Oracle 資料庫至少包含一個表空間，就是名為SYSTEM的系統表空間。


每個表空間是由一個或多個資料檔案組成的，一個資料檔案隻能與一個表空間相關聯。


表空間的大小等于構成該表空間的所有資料檔案大小之和。


建立表空間的文法是：
CREATE TABLESPACE tablespacename
DATAFILE ‘filename’ [SIZE integer [K|M]] 
[AUTOEXTEND [OFF|ON]];


要連接配接到Oracle資料庫，就需要建立一個使用者帳戶
每個使用者都有一個預設表空間和一個臨時表空間
CREATE USER指令用于建立新使用者
CREATE USER 指令的文法是：
CREATE USER MARTIN
IDENTIFIED BY martinpwd
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
建立一個名稱為 martin 的使用者，其密碼為 
martinpwd


GRANT 指令可用于為使用者配置設定權限或角色 
GRANT CONNECT TO MARTIN; CONNECT角色允許使用者連接配接至資料庫，并建立資料庫對象
GRANT RESOURCE TO MARTIN; RESOURCE角色允許使用者使用資料庫中的存儲空間


如果上機的時候，發現Scott使用者的密碼被其他同學給修改了，應該如何處理？


解決方案
1）使用匿名登陸Oracle伺服器
sqlplus / nolog
conn / as sysdba
2）修改scott使用者的密碼
Alter user scott identified by tiger;