Oracle常用sql語句（一）Sql的分類

<a href="#sql%E7%9A%84%E5%88%86%E7%B1%BB">Sql的分類</a>

<a href="#ddl%E6%93%8D%E4%BD%9C%E6%95%B0%E6%8D%AE%E5%BA%93%E8%A1%A8%E5%88%97%E7%AD%89">DDL操作資料庫表列等</a>

<a href="#%E8%A1%A8%E5%9F%BA%E6%9C%AC%E7%9A%84%E6%93%8D%E4%BD%9C">表基本的操作</a>

<a href="#%E4%B8%BB%E9%94%AE">主鍵</a>

<a href="#%E7%B4%A2%E5%BC%95">索引</a>

<a href="#%E8%A7%86%E5%9B%BE">視圖</a>

<a href="#%E5%B8%B8%E7%94%A8%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B">常用資料類型</a>

<a href="#%E5%AD%97%E7%AC%A6%E4%B8%B2%E7%B1%BB%E5%9E%8B">字元串類型</a>

<a href="#%E6%95%B0%E5%AD%97%E7%B1%BB%E5%9E%8B">數字類型</a>

<a href="#%E6%97%A5%E6%9C%9F%E7%B1%BB%E5%9E%8B">日期類型</a>

<a href="#lob%E7%B1%BB%E5%9E%8B">LOB類型</a>

<a href="#long%E7%B1%BB%E5%9E%8B">LONG類型</a>

<a href="#%E6%95%B0%E6%8D%AE%E8%BD%AC%E6%8D%A2">資料轉換</a>

<a href="#character-date">character date</a>

<a href="#numbercharacter">numbercharacter</a>

<a href="#%E9%80%9A%E7%94%A8%E5%87%BD%E6%95%B0">通用函數</a>

<a href="#%E6%9D%A1%E4%BB%B6%E8%A1%A8%E8%BE%BE%E5%BC%8F">條件表達式</a>

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的基礎知識(也是常識），一定要記住。

使用的關鍵字：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&lt;僅适用于Oracle&gt;

删除表

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

查詢含下劃線的名字

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)

本月的最後一天

日期四舍五入 fmt的值（’YEAR’,’MONTH’)

日期截斷 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

秒

目前時間

修改日期格式

（預設格式為’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資料類型包括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 資料類型

二進制檔案，存儲在資料庫外的系統檔案，隻讀的，資料庫會将該檔案當二進制檔案處理

　　它存儲變長字元串，最多達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

TO_CHAR(date[,fmt])

TO_DATE(ch[, fmt])

例：

TO_CHAR(NUMBER[,fmt])

TO_NUMBER(expr[, fmt])

數字轉換格式

9

數字

零

$

美元符号

.

小數點

,

千位符

例子：

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的值

通用的

例子

oracle自己的