Sql的分類

---

DDL （Data Definition Language）：數據定義語言，用來定義數據庫對象：庫、表、列等； CREATE、 ALTER、DROP
DML（Data Manipulation Language）：數據操作語言，用來定義數據庫記錄（數據）； INSERT、 UPDATE、 DELETE
DCL（Data Control Language）：數據控制語言，用來定義訪問權限和安全級別；
DQL（Data Query Language）：數據查詢語言，用來查詢記錄（數據）。
SELECT
*注意：sql語句以 ; 結尾
這些都是數據庫的SQL的基礎知識(也是常識），一定要記住。

DDL:操作數據庫、表、列等

---

使用的關鍵字：CREATE、 ALTER、 DROP

表基本的操作

• 創建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根據已有的表創建新表：

select * into table_new from table_old (使用舊表創建新表)

create table tab_new as select col1,col2… from tab_old definition only<僅適用于Oracle>

• 刪除表

drop table tablename

• 修改表

rename 舊表名 to 新表名 修改表名

alert table tabname rename column oldColumn to newColumn 修改列名

alter table tabname add column col type 添加一列

alter table tabname drop column colname 刪除一列

alter table tabname modify（ ） 修改表中列的值

主鍵

添加主鍵：

Alter table tabname add primary key(col)

刪除主鍵：
Alter table tabname drop primary key(col)

索引

創建索引：

create [unique] index idxname on tabname(col….)

刪除索引：

drop index idxname

注：索引是不可更改的，想更改必須刪除重新建。

視圖

創建視圖：

create view viewname as select statement

刪除視圖：

drop view viewname

常用數據類型

字符串類型

CHAR類型 CHAR(size [BYTE | CHAR])

　　CHAR類型，定長字符串，會用空格填充來達到其最大長度。非NULL的CHAR（12）總是包含12字節信息。CHAR字段最多可以存儲2,000字節的信息。如果創建表時，不指定CHAR長度，則默認為1。

VARCHAR類型

　　不要使用VARCHAR數據類型。使用VARCHAR2數據類型。雖然VARCHAR數據類型目前是VARCHAR2的同義詞，VARCHAR數據類型將計劃被重新定義為一個單獨的數據類型用于可變長度的字符串相比，具有不同的比較語義。

* VARCHAR2類型*

　　變長字符串，與CHAR類型不同，它不會使用空格填充至最大長度。VARCHAR2最多可以存儲4,000字節的信息。

NVARCHAR2類型

　　這是一個包含UNICODE格式數據的變長字符串。 NVARCHAR2最多可以存儲4,000字節的信息。

---

函數：

---

• 連接符||及CONCAT函數

可以連接兩個列名或者常量。

注：如果姓名為中文還好，但是如果是英文的，這樣連接會導致讀起來比較困難，所以可以在中間加上常量“空格”；CONCAT函數符合ANSI SQL標準，所以適合更多不同的數據庫，||是Oracle專有的，使用起來更簡潔。

• 格式統一：RPAD和LPAD

RPAD允許在列的右邊填充一組字符，填充的字符可以為任何字符。LPAD從左邊添加。

使用方式：

RPAD(string,length[,’set’])

LPAD(string,length[,’set’])

這里的string是數據庫中的字符串列或常量，length是填充后的長度，set是用來填充的字符串。如果方括號中的內容省略了，會默認使用空格填充。

• 修剪：LTRIM,RTRIM,TRIM

LTRIM和RTRIM從串的左邊或右邊刪除不需要的字符。

使用方式：

RTRIM(string[,’set’])

LTRIM(string[,’set’])

如果沒有設置要刪除的值，默認刪除空格。

• 大小寫轉換：LOWER、UPPER和INITCAP

LOWER把串或列種的任意字母轉換為小寫。

UPPER與LOWER相反。

INITCAP將串或列中每個單詞的首字母轉換成大寫。

它們經常一起使用。

使用格式：

LOWER(string)

UPPER(string)

INITCAP(string)

• 子串：SUBSTR

使用SUBSTR函數可以提取出串的一部分。

使用格式：

SUBSTR(string,start[,count])

這個函數告訴Oracle提取string的一個子串，從start位置開始，長度為count個字符。如果不指定count，將從start開始一直到這個串結束。

• 索引位置：INSTR

INSTR可以告訴你要搜索的字符（串）在串種的位置。

使用格式：

INSTR(string,set[,start[,occurrence]])

string為要尋找的列或常量；set為要指定的要尋找的值；start可選，默認為從串的第一個位置開始搜索；occurrence可選，為指定字符串出現的第occurrence次的位置。

• like

  查詢含下劃線的名字

SELECT * FROM emp
where ename LIKE '%/_%'  escape '/';

數字類型

NUMBER類型

　　NUMBER(P,S)是最常見的數字類型，可以存放數據范圍為10^130~10^126（不包含此值)，需要1~22字節(BYTE)不等的存儲空間。

　　P 是Precison的英文縮寫，即精度縮寫，表示有效數字的位數，最多不能超過38個有效數字

　　S是Scale的英文縮寫，可以使用的范圍為-84~127。Scale為正數時，表示從小數點到最低有效數字的位數，它為負數時，表示從最大有效數字到小數點的位數

INTEGER類型

　　INTEGER是NUMBER的子類型，它等同于NUMBER（38,0），用來存儲整數。若插入、更新的數值有小數，則會被四舍五入。

浮點數

BINARY_FLOAT

　　BINARY_FLOAT 是 32 位、 單精度浮點數字數據類型。可以支持至少6位精度,每個 BINARY_FLOAT 的值需要 5 個字節，包括長度字節。

BINARY_DOUBLE

　　BINARY_DOUBLE 是為 64 位，雙精度浮點數字數據類型。每個 BINARY_DOUBLE 的值需要 9 個字節，包括長度字節。

　　在數字的列中，浮點數有小數精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中，浮點數有二進制的精度。二進制浮點數支持的特殊值無窮大和 NaN （不是數字）。

FLOAT類型

　　FLOAT類型也是NUMBER的子類型。

　　Float(n),數 n 指示位的精度，可以存儲的值的數目。N 值的范圍可以從 1 到 126。若要從二進制轉換為十進制的精度，請將 n 乘以 0.30103。要從十進制轉換為二進制的精度，請用 3.32193 乘小數精度。126 位二進制精度的最大值是大約相當于 38 位小數精度。

---

函數：

---

函數	說明
ROUND(date, fmt)	四舍五入
TRUNC(date, fmt)	截斷
MOD(n1, n2)	求余
CEIL(n)	向上取整
FLOOR(n)	向下取整
GREATEST(expr1, … exprn)	返回參數中最大的數
LEAST(expr1, … exprn)	返回參數中最小的數

隨機數

SELECT TRUNC(dbms_random.value(a,b)) FROM dual

產生 [a,b)之間的數

日期類型

　　日期類型用于存儲日期數據，但是并不是使用一般的格式（2012-08-08）直接存儲到數據庫的。

DATE類型

　　DATE是最常用的數據類型，日期數據類型存儲日期和時間信息。雖然可以用字符或數字類型表示日期和時間信息，但是日期數據類型具有特殊關聯的屬性。為每個日期值，Oracle 存儲以下信息： 世紀、 年、 月、 日期、 小時、 分鐘和秒。一般占用7個字節的存儲空間。

TIMESTAMP類型

　　這是一個7字節或12字節的定寬日期/時間數據類型。它與DATE數據類型不同，因為TIMESTAMP可以包含小數秒，帶小數秒的TIMESTAMP在小數點右邊最多可以保留9位

TIMESTAMP WITH TIME ZONE類型

　　這是TIMESTAMP類型的變種，它包含了時區偏移量的值

---

函數：

---

函數	說明
MONTHS_BETWEEN(date1, date2)	兩個日期相差的月數
ADD_MONTHS(date, int)	向指定日期中加上若干月數
NEXT_DAY(date, ch)	指定日期的下一個日期(從星期日和1開始計算）
LAST_DAY(date)	本月的最后一天
ROUND(date, fmt)	日期四舍五入 fmt的值（’YEAR’,’MONTH’)
TRUNC(date, fmt)	日期截斷 fmt的值（’YEAR’,’MONTH’)
EXTRACT(time_unit FROM date)	從date中提取time_unit指定格式的日期數據

日期格式

格式	說明	舉例
YYYY	年份的數字格式全稱	2017
YEAR	年的英文全稱	twenty seventeen
MM	月份（數字格式）	10
MONTH	月得全稱	10月
DY	星期幾	星期四
DAY	星期幾	星期四
DD	一個月的第幾天	05
HH12	小時（12）	1
HH24	小時（24）	13
MI	分鐘	12
SS	秒	12

• 當前時間

SELECT SYSDATE 
FROMdual;
SELECTSYSTIMESTAMP 
FROMdual;-- 精確到毫秒

SELECTTO_CHAR( SYSDATE, 'yyyy-mm-dd hh24:mi:ss' ) 
FROMdual;
-- 昨天 今天   明天
SELECT( SYSDATE - 1 ) 昨天,SYSDATE 今天,( SYSDATE + 1 ) 明天 
FROMdual;

修改日期格式
（默認格式為’DD-MON-RR’)

SELECT * FROM v$nls_parameters;

ALTER SESSION SET nls_date_format=’yyyy-mm-dd’;
– 改回默認
ALTER SESSION SET nls_date_format=’DD-MON-RR’;

LOB類型

　　內置的LOB數據類型包括BLOB、CLOB、NCLOB、BFILE（外部存儲）的大型化和非結構化數據，如文本、圖像、視屏、空間數據存儲。BLOB、CLOB、NCLOB類型

CLOB 數據類型

它存儲單字節和多字節字符數據。支持固定寬度和可變寬度的字符集。CLOB對象可以存儲最多 (4 gigabytes-1) * (database block size) 大小的字符

NCLOB 數據類型

它存儲UNICODE類型的數據，支持固定寬度和可變寬度的字符集，NCLOB對象可以存儲最多(4 gigabytes-1) * (database block size)大小的文本數據。

BLOB 數據類型

它存儲非結構化的二進制數據大對象，它可以被認為是沒有字符集語義的比特流，一般是圖像、聲音、視頻等文件。BLOB對象最多存儲(4 gigabytes-1) * (database block size)的二進制數據。

BFILE 數據類型

二進制文件，存儲在數據庫外的系統文件，只讀的，數據庫會將該文件當二進制文件處理

LONG類型

　　它存儲變長字符串，最多達2G的字符數據（2GB是指2千兆字節， 而不是2千兆字符），與VARCHAR2 或CHAR 類型一樣，存儲在LONG 類型中的文本要進行字符集轉換。ORACLE建議開發中使用CLOB替代LONG類型。支持LONG 列只是為了保證向后兼容性。CLOB類型比LONG類型的限制要少得多。 LONG類型的限制如下：

• 一個表中只有一列可以為LONG型。

• LONG列不能定義為主鍵或唯一約束，

• 不能建立索引．

• LONG數據不能指定正則表達式。

• 函數或存儲過程不能接受LONG數據類型的參數。

• LONG列不能出現在WHERE子句或完整性約束（除了可能會出現NULL和NOT NULL約束）

數據轉換

數據轉換分為

• 隱式轉換 varchar《==》number oracle數據庫自動轉換

• 顯示轉換 number《==》character《==》date

character 《==》 date

• TO_CHAR(date[,fmt])

• TO_DATE(ch[, fmt])

例：

SELECTTO_CHAR( SYSDATE, 'dy' ) 
FROMdual;

number《==》character

• TO_CHAR(NUMBER[,fmt])

• TO_NUMBER(expr[, fmt])

數字轉換格式

格式	說明
9	數字
0	零
$	美元符號
.	小數點
,	千位符

例子：

-- 查詢員工的薪水：兩位小數，千位符，本地貨幣
SELECTTO_CHAR( sal, 'L9,999.99' ) 
FROMemp;
-- ￥7,000.00

通用函數

函數	說明
NVL(expr1, expr2)	把列expr1的null值修改為expr2
NVL2(expr1, expr2, expr3)	把列expr1的null值修改為expr2，不為null修改為expr3
NULLIF(expr1, expr2)	判斷expr1和expr2是否相等，相等返回null，反之返回expr2
COALESCE(expr1, … exprn)	從左到右找到第一個不為null的值

條件表達式

通用的

CAST expr WHEN comparison_expr1 THEN return_expr1[WHEN comparison_expr1 THEN return_expr1ELSE else_expr]
END

例子

SELECTename,job,sal 漲前,
CASEjob WHEN '學生' THENsal + 100 WHEN '教師' THENsal + 800 ELSE sal + 50 END 張后 
FROMemp;

oracle自己的

DECODE(COLUMN,search, result [[,search, result]*, default]) oracle自己的

例子：

SELECTename,job,sal 漲前,DECODE( JOB, '學生', sal + 100, '教師', sal + 800, sal + 50 ) 張后 
FROM
emp;