SQL語法筆記: 8月 2009

2009年8月25日星期二

基本語法

程式碼

DECLARE

V_STRING VARCHAR2(20);

V_DIAMETER NUMBER(5, 2);

V_CIRCUMFERENCE NUMBER(5, 2);

V_RADIUS NUMBER(5, 2) := 5;

C_PI CONSTANT NUMBER(3,2) := 3.14;

V_COUNTER NUMBER(5);

BEGIN

-- THIS IS SINGLE-LINE COMMENT

THIS IS

MULTI-LINE

COMMENT

V_DIAMETER := 9;

V_CIRCUMFERENCE := V_DIAMETER * C_PI;

DBMS_OUTPUT.PUT_LINE('V_CIRCUMFERENCE = ' || V_CIRCUMFERENCE);

IF V_RADIUS IS NULL THEN

DBMS_OUTPUT.PUT_LINE('V_RADIUS IS NULL.');

ELSIF V_RADIUS > 9 THEN

DBMS_OUTPUT.PUT_LINE('V_RADIUS IS GREATER THAN 9.');

ELSIF V_RADIUS < 9 THEN

DBMS_OUTPUT.PUT_LINE('V_RADIUS IS LESS THAN 9.');

ELSE

DBMS_OUTPUT.PUT_LINE('V_RADIUS IS EQUAL TO 9.');

END IF;

V_COUNTER := 1;

LOOP

EXIT WHEN V_COUNTER >= 6;

DBMS_OUTPUT.PUT_LINE('LOOP: V_COUNTER = ' || V_COUNTER);

V_COUNTER := V_COUNTER + 1;

END LOOP;

V_COUNTER := 1;

WHILE (V_COUNTER <= 5) LOOP

DBMS_OUTPUT.PUT_LINE('WHILE LOOP: V_COUNTER = ' || V_COUNTER);

V_COUNTER := V_COUNTER + 1;

EXIT WHEN V_COUNTER >= 9;

END LOOP;

FOR V_LOCAL_COUNTER IN 1 .. 5 LOOP

DBMS_OUTPUT.PUT_LINE('FOR LOOP: V_LOCAL_COUNTER = ' || V_LOCAL_COUNTER);

END LOOP;

FOR V_LOCAL_COUNTER IN REVERSE 1 .. 5 LOOP

DBMS_OUTPUT.PUT_LINE('FOR LOOP REVERSE: V_LOCAL_COUNTER = ' || V_LOCAL_COUNTER);

END LOOP;

END;

執行結果

V_CIRCUMFERENCE = 28.26

V_RADIUS IS LESS THAN 9.

LOOP: V_COUNTER = 1

LOOP: V_COUNTER = 2

LOOP: V_COUNTER = 3

LOOP: V_COUNTER = 4

LOOP: V_COUNTER = 5

WHILE LOOP: V_COUNTER = 1

WHILE LOOP: V_COUNTER = 2

WHILE LOOP: V_COUNTER = 3

WHILE LOOP: V_COUNTER = 4

WHILE LOOP: V_COUNTER = 5

FOR LOOP: V_LOCAL_COUNTER = 1

FOR LOOP: V_LOCAL_COUNTER = 2

FOR LOOP: V_LOCAL_COUNTER = 3

FOR LOOP: V_LOCAL_COUNTER = 4

FOR LOOP: V_LOCAL_COUNTER = 5

FOR LOOP REVERSE: V_LOCAL_COUNTER = 5

FOR LOOP REVERSE: V_LOCAL_COUNTER = 4

FOR LOOP REVERSE: V_LOCAL_COUNTER = 3

FOR LOOP REVERSE: V_LOCAL_COUNTER = 2

FOR LOOP REVERSE: V_LOCAL_COUNTER = 1

HELLO

SET SERVEROUTPUT ON

DECLARE
V_STRING VARCHAR(200);
BEGIN
V_STRING := 'HELLO';
DBMS_OUTPUT.PUT_LINE(V_STRING);
END;
/

Oracle Personal Edition 安裝

http://www.oracle.com/technology/software/products/8i_personal/index.html

舉例來說，我們可能只需要知道哪些店的營業額有超過 $1,500。在這個情況下，我們不能使用 WHERE 的指令。那要怎麼辦呢？很幸運地，SQL 有提供一個 HAVING 的指令，而我們就可以用這個指令來達到這個目標。 HAVING 子句通常是在一個 SQL 句子的最後。一個含有 HAVING 子句的 SQL 並不一定要包含 GROUP BY 子句。HAVING 的語法如下：

SELECT "欄位1", SUM("欄位2")
FROM "表格名"
GROUP BY "欄位1"
HAVING (函數條件)

請讀者注意：如果被 SELECT 的只有函數欄，那就不需要 GROUP BY 子句。

在我們 Store_Information 表格這個例子中,

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

若我們要找出 Sales 大於 $1,500 的 store_name，我們就鍵入，

SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500

結果:

store_name	SUM(Sales)
Los Angeles	$1800

GROUP BY "欄位1"

記得我們用 SUM 這個指令來算出所有的 Sales (營業額)吧！如果我們的需求變成是要算出每一間店 (store_name) 的營業額 (sales)，那怎麼辦呢？在這個情況下，我們要做到兩件事：第一，我們對於 store_name 及 Sales 這兩個欄位都要選出。第二，我們需要確認所有的 sales 都要依照各個 store_name 來分開算。這個語法為：

SELECT "欄位1", SUM("欄位2")
FROM "表格名"
GROUP BY "欄位1"

在我們的範例上，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們就鍵入，

SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name

結果:

store_name	SUM(Sales)
Los Angeles	$1800
San Diego	$250
Boston	$700

當我們選不只一個欄位，且其中至少一個欄位有包含函數的運用時，我們就需要用到 GROUP BY 這個指令。在這個情況下，我們需要確定我們有 GROUP BY 所有其他的欄位。換句話說，除了有包括函數的欄位外，我們都需要將其放在 GROUP BY 的子句中。

Length(str): 找出 str 字串的長度

在 SQL 中，長度函數是用來找出一個字串的長度。在不同的資料庫中不完全一樣：

MySQL: LENGTH()
Oracle: LENGTH()
SQL Server: LEN()

長度函數的用法如下：

Length(str): 找出 str 字串的長度。

來看看幾個例子。假設我們有以下的表格：

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

例 1：

SELECT Length(store_name)
FROM Geography
WHERE store_name = 'Los Angeles';

結果:

例 2：

SELECT region_name, Length(region_name)
FROM Geography;

結果:

region_name	Length(region_name)
East	4
East	4
West	4
West	4

2009年8月16日星期日

DUAL 表格

SQL> SELECT 1 + 2 FROM DUAL;

       1+2
----------
         3

因為每個 SELECT 句子都必需有 FROM 子句，所以 Oracle 就做個 DUAL 表格來符合這個守則了。

DUAL 表格還可以用來提供一些資訊，例如使用者名稱、系統時間等。

SQL> SELECT USER FROM DUAL;

USER
------------------------------
SCOTT

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
03-NOV-02

SELECT CASE

CASE 是 SQL 用來做為 if-then-else 之類邏輯的關鍵字。 CASE 的語法如下：

SELECT CASE ("欄位名")
WHEN "條件1" THEN "結果1"
WHEN "條件2" THEN "結果2"
...
[ELSE "結果N"]
END
FROM "表格名"

"條件" 可以是一個數值或是公式。 ELSE 子句則並不是必須的。

在我們的 Store_Information 中

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

若我們要將 'Los Angeles' 的 Sales 數值乘以2，以及將 'San Diego' 的 Sales 數值乘以1.5，我們就鍵入以下的 SQL：

SELECT store_name, CASE store_name
WHEN 'Los Angeles' THEN Sales * 2
WHEN 'San Diego' THEN Sales * 1.5
ELSE Sales
END
"New Sales",
Date
FROM Store_Information

"New Sales" 是用到 CASE 那個欄位的欄位名。

結果:

store_name	New Sales	Date
Los Angeles	$3000	Jan-05-1999
San Diego	$375	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

SUBSTRING 函數

substring 函數是用來抓出一個欄位資料中的其中一部分。這個函數的名稱在不同的資料庫中不完全一樣：

MySQL: SUBSTR(), SUBSTRING()
Oracle: SUBSTR()
SQL Server: SUBSTRING()

最常用到的方式如下 (在這裡我們用SUBSTR()為例)：

SUBSTR(str,pos): 由<str>中，選出所有從第<pos>位置開始的字元。請注意，這個語法不適用於SQL Server上。

SUBSTR(str,pos,len): 由<str>中的第<pos>位置開始，選出接下去的<len>個字元。

假設我們有以下的表格：

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

例1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';

結果:

's Angeles'

例2:

SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';

結果:

'an D'

Function

PL/SQL Developer

如何關/閉 table下所有TRIGGERS?

ORA-02069: global_names parameter must be set to TRUE for this operation

SQL 語法

2009年8月6日星期四

UPDATE

UPDATE "表格名"
SET "欄位1" = [新值]
WHERE {條件}

最容易瞭解這個語法的方式是透過一個例子。假設我們有以下的表格：

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們發現說 Los Angeles 在 01/08/1999 的營業額實際上是 $500，而不是表格中所儲存的 $300，因此我們用以下的 SQL 來修改那一筆資料：

UPDATE Store_Information
SET Sales = 500
WHERE store_name = "Los Angeles"
AND Date = "Jan-08-1999"

現在表格的內容變成：

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$500	Jan-08-1999
Boston	$700	Jan-08-1999

在這個例子中，只有一筆資料符合 WHERE 子句中的條件。如果有多筆資料符合條件的話，每一筆符合條件的資料都會被修改的。

我們也可以同時修改好幾個欄位。這語法如下：

UPDATE "表格"
SET "欄位1" = [值1], "欄位2" = [值2]
WHERE {條件}

DELETE

在某些情況下，我們會需要直接由資料庫中去除一些資料。這可以藉由 DELETE FROM 指令來達成。它的語法是：

DELETE FROM "表格名"
WHERE {條件}

以下我們用個實例說明。假設我們有以下這個表格：

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

而我們需要將有關 Los Angeles 的資料全部去除。在這裡我們可以用以下的 SQL 來達到這個目的:

DELETE FROM Store_Information
WHERE store_name = "Los Angeles"

現在表格的內容變成：

Store_Information 表格

store_name	Sales	Date
San Diego	$250	Jan-07-1999
Boston	$700	Jan-08-1999

INSERT

INSERT INTO "表格名" ("欄位1", "欄位2", ...)
VALUES ("值1", "值2", ...)

假設我們有一個架構如下的表格：

Store_Information 表格

Column Name	Data Type
store_name	char(50)
Sales	float
Date	datetime

而我們要加以下的這一筆資料進去這個表格：在 January 10, 1999，Los Angeles 店有 $900 的營業額。我們就打入以下的 SQL 語句：

INSERT INTO Store_Information (store_name, Sales, Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999')

第二種 INSERT INTO 能夠讓我們一次輸入多筆的資料。跟上面剛的例子不同的是，現在我們要用 SELECT 指令來指明要輸入表格的資料。如果您想說，這是不是說資料是從另一個表格來的，那您就想對了。一次輸入多筆的資料的語法是：

INSERT INTO "表格1" ("欄位1", "欄位2", ...)
SELECT "欄位3", "欄位4", ...
FROM "表格2"

以上的語法是最基本的。這整句 SQL 也可以含有 WHERE、 GROUP BY、及 HAVING 等子句，以及表格連接及別名等等。

舉例來說，若我們想要將 1998 年的營業額資料放入 Store_Information 表格，而我們知道資料的來源是可以由 Sales_Information 表格取得的話，那我們就可以鍵入以下的 SQL：

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

在這裡，我用了 SQL Server 中的函數來由日期中找出年。不同的資料庫會有不同的語法。舉個例來說，在 Oracle 上，您將會使用 WHERE to_char(date,'yyyy')=1998。

2009年8月5日星期三

UNION 指令

UNION

UNION 指令的目的是將兩個 SQL 語句的結果合併起來。從這個角度來看， UNION 跟 JOIN 有些許類似，因為這兩個指令都可以由多個表格中擷取資料。 UNION 的一個限制是兩個 SQL 語句所產生的欄位需要是同樣的資料種類。另外，當我們用 UNION 這個指令時，我們只會看到不同的資料值 (類似 SELECT DISTINCT)。

UNION 的語法如下：

[SQL 語句 1]
UNION
[SQL 語句 2]

假設我們有以下的兩個表格，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Internet_Sales 表格

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

而我們要找出來所有有營業額 (sales) 的日子。要達到這個目的，我們用以下的 SQL 語句：

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

結果:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

有一點值得注意的是，如果我們在任何一個 SQL 語句 (或是兩句都一起) 用 "SELECT DISTINCT Date" 的話，那我們會得到完全一樣的結果。

UNION ALL

UNION ALL 這個指令的目的也是要將兩個 SQL 語句的結果合併在一起。 UNION ALL 和 UNION 不同之處在於 UNION ALL 會將每一筆符合條件的資料都列出來，無論資料值有無重複。

UNION ALL 的語法如下：

[SQL 語句 1]
UNION ALL
[SQL 語句 2]

我們用和上一頁同樣的例子來顯示出 UNION ALL 和 UNION 的不同。同樣假設我們有以下兩個表格，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Internet_Sales 表格

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

而我們要找出有店面營業額以及網路營業額的日子。要達到這個目的，我們用以下的 SQL 語句：

SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales

結果:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-07-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

如何看DBMS_OUTPUT

如何看DBMS_OUTPUT
set   serveroutput   on;
begin
dbms_output.put_line('hello!');
end;

訂閱：文章 (Atom)

SQL語法筆記

2009年8月25日星期二

基本語法

HELLO

Oracle Personal Edition 安裝

GROUP BY "欄位1" HAVING (函數條件)

GROUP BY "欄位1"

Length(str): 找出 str 字串的長度

2009年8月16日星期日

DUAL 表格

SELECT CASE

SUBSTRING 函數

Function

oracle 日期常用函數 (SYSDATE、日期格式)

PL/SQL Developer

ORA-02069: global_names parameter must be set to TRUE for this operation

SQL 語法

SQL 算排名

2009年8月6日星期四

UPDATE

DELETE

INSERT

2009年8月5日星期三

UNION 指令

如何看DBMS_OUTPUT

關於我自己

2009年8月25日 星期二

2009年8月16日 星期日

2009年8月6日 星期四

2009年8月5日 星期三

2009年8月25日星期二

2009年8月16日星期日

2009年8月6日星期四

2009年8月5日星期三