Oracle-SQL知識詳解（一）

GuaKin_Huang發表於2016-10-23

OracleSQL

Oracle-SQL

結構化查詢語言

簡介
結構化查詢語言（Structured Query Language)
）簡稱SQL，是操作和檢索關係型資料庫的標準語言；
分類

資料查詢語言（DQL：Data Query Language）：語句主要包括 SELECT ，用於從表中檢索；

資料操作語言（DML：Data Manipulation Language）：主要包括 insert，update，delete，用於新增、修改和刪除表中的行資料；

事務處理語言：（TPL：Transaction Process Language）：語句主要包括 commit， rollback，用於提交和回滾；

資料控制語言：（DCL：Data Control Language）：主要包括 grant，revoke，用於進行授權和收回許可權；

資料定義語言（DDL：Data Definition language）：主要包括 create， drop， alter，用於定義、銷燬、修改資料庫物件；

簡單查詢

1、基本select 語句語法

SQL語句的書寫規則
- 不區分大小寫，也就是說SELECT，select，Select，執行時效果是一樣的。
- 可以單行來書寫，也可以書寫多行，建議分多行書寫，增強程式碼可讀性，通常以子句為單位進行分行。
- 關鍵字不可以縮寫、分開以及跨行書寫，如SELECT**不可以寫成SEL或SELE CT**等形式。
- 關鍵字最好使用大寫，其它語法元素（如列名、表名等）小寫。
- Tab和縮排的使用可以提高程式的可讀性
2、算數運算子
- 可以在SELECT語句中使用算術運算子，改變輸出結果
  ![Uploading 1477113225660_194572.png …]

3、算術運算子優先順序
- 乘除優先於加減；
- 相同優先權的表示式按照從左至右的順序依次計算；
- 括弧可以提高優先權，並使表示式的描述更為清晰；
4、空值NULL
- 空值是一種無效的、未賦值的、未知的或不可用的值；
- 空值不同於零或空格；
- 任何包含空值的算術表示式運算後的結果都為空值；
列別名
- 用來重新命名列的顯示標題；
- 如果SELECT語句中包含計算列，通常使用列別名來重；
- 新定義列標題；
使用列別名的方法
- 方式1：列名列別名
- 方式2：列名 AS 列別名
以下三種情況列別名兩側需要新增雙引號
- 列別名中包含有空格；
- 列別名中要求區分大小寫；
- 列別名中包含有特殊字元；
5、連線操作符
- 用於連線列與列、列和字元；
- 形式上是以兩個豎槓||；
- 用於建立字元表示式的結果列；
6、原義字串
- 原義字串是包含在SELECT列表中的一個字元、一個數字或一個日期。
- 日期和字元字面值必須用單引號引起來。
- 每個原義字串都會在每個資料行輸出中出現
7、消除重複行
在SELECT字句中使用關鍵字DISTINCT可消除重複行；

限定資料和對資料排序

1、選擇限定資料行

使用WHERE子句可以返回限定的資料行,語法如下：

WHERE子句緊跟在FROM子句之後condition(s)表示條件表示式，通常格式為：列名比較操作符要比較的值

2、比較操作符

字元型資料作為被比較的值時，必須用單引號引起來
字元型數值區分大小寫
日期型數值作為被比較的值時，必須用單引號引起來。
日期型數值是區分日期表達形式的，預設的日期形式是DD-MON-RR
3、特殊比較運算子

4、邏輯運算子

當需要和多個條件表示式進行比較時，需要使用邏輯運算子把多個表示式連線起來；

5、運算子的優先順序

括號’()’優先於其他操作符

ORDER BY子句

使用ORDER BY子句能對查詢結果集進行排序,語法結構如下；

其中：
- 可以按照列名、表示式、列別名、結果集的列序號排序；
- ASC: 升序，預設值 DESC: 降序；
- ORDER BY 子句必須寫在SELECT語句的最後；

排序規則
- 數字升序排列小值在前，大值在後。即按照數字大小順序由小到大排列。
- 日期升序排列相對較早的日期在前，較晚的日期在後；
- 字元升序排列按照字母由小到大的順序排列。即由A-Z排列；中文升序按照字典順序排列。
- 空值在升序排列中排在最後，在降序排列中排在最開始；

單行函式

1、SQL函式概述

2、SQL函式分類

3、單行函式語法
語法:

函式名[(引數1，引數2,…)]

其中的引數可以是以下之一：

變數
列名
表示式

4、單行函式特徵
- 單行函式對單行操作；
- 每行返回一個結果；
- 有可能返回值與原引數資料型別不一致；
- 單行函式可以寫在SELECT、WHERE、ORDER BY子句中；
- 有些函式沒有引數，有些函式包括一個或多個引數；
- 函式可以巢狀；
5、單行函式的分類

6、字元函式

主要指引數型別是字元型，不同函式返回值可能是字元型或數值型；

大小寫轉換函式

LOWER(列名|表示式)：將大寫或大小寫混合的字元轉換成小寫

UPPER(列名|表示式) ：將小寫或大小寫混合的字元轉換成大寫

INITCAP(列名|表示式) ：將每個單詞的第一個字母轉換成大寫，其餘的字母都轉換成小寫

字元處理函式

CONCAT(column1|expression1,column2|expression2) 連線兩個值 ,等同於||；

SUBSTR (column|expression,n1[,n2])
- 返回第一個引數中，從第n1位開始，長度為n2的子串。
- 如果n2省略,取第n1位開始的所有字元。
- 如果n1是負值，表示從第一個引數的後面第abs(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 trim_character From trim_source)

REPLACE(s1,s2,s3)
把s1中的s2用s3替換。

7、數值函式

ROUND : 將列或表示式所表示的數值四捨五入到小數點後的第n位。格式: ROUND(column| expression, n)；

TRUNC : 將列或表示式所表示的數值擷取到小數點後的第n位。格式：TRUNC(column| expression, n)；

MOD : 取m除以n後得到的餘數格式：MOD(m,n)；

8、日期型別數學運算

SYSDATE ：返回系統當前日期
add_months(sysdate,12) 加1年
add_months(sysdate,1) 加1月
sysdate+1 加一天sysdate+1/24 加1小時sysdate+1/(24*60) 加1分鐘
sysdate+1/(24*60*60) 加1秒鐘
EXTRACT ：返回日期型別資料中的年份、月份或者日；
MONTHS_BETWEEN ：返回兩個日期型別資料之間間隔的自然月數
NEXT_DAY：返回某一日期的下一個指定日期
LAST_DAY：返回指定日期當月最後一天的日期

9、轉換函式

隱式資料型別轉換規則
- 對於INSERT和UPDATE操作，oracle會把插入值或者更新值隱式轉換為欄位的資料型別
- 對於SELECT語句，oracle會把欄位的資料型別隱式轉換為變數的資料型別
- 當比較一個字元型和數值型的值時，oracle會把字元型的值隱式轉換為數值型
- 當比較字元型和日期型的資料時，oracle會把字元型轉換為日期型
- 用連線操作符(||)時，oracle會把非字元型別的資料轉換為字元型別
- 如果字元型別的資料和非字元型別的資料(如number、date、rowid等)作算術運算，則oracle會將字元型別的資料轉換為合適的資料型別，這些資料型別可能是number、date、rowid等；
顯式資料型別轉換

通常是在字元型別、日期型別、數值型別之間進行顯式轉換。主要有3個顯式轉換函式：
- TO_CHAR
- TO_NUMBER
- TO_DATE