歡迎您光臨本站 註冊首頁

SQL循序漸進

←手機掃碼閱讀     火星人 @ 2014-03-12 , reply:0
  來源:www.linuxsir.net資料庫版

介紹SQL

  SQL(Structured Query Language,結構查詢語言)是一個功能強大的資料庫語言。SQL通常使用於資料庫的通訊。ANSI(美國國家標準學會)聲稱,SQL是關係資料庫管理系統的標準語言。SQL語句通常用於完成一些資料庫的操作任務,比如在資料庫中更新數據,或者從資料庫中檢索數據。使用SQL的常見關係資料庫管理系統有:Oracle、 Sybase、 Microsoft SQL Server、 Access、 Ingres等等。雖然絕大多數的資料庫系統使用SQL,但是它們同樣有它們自立另外的專有擴展功能用於它們的系統。但是,標準的SQL命令,比如 "Select"、 "Insert"、 "Update"、 "Delete"、 "Create"和 "Drop"常常被用於完成絕大多數資料庫的操作。



  但是,不象其它的語言,如C、Pascal等,SQL沒有循環結構(比如if-then-else、do-while)以及函數定義等等的功能。而且SQL只有一個數據類型的固定設置,換句話說,你不能在使用其它編程語言的時候創建你自己的數據類型。

SQL功能強大,但是概括起來,它可以分成以下幾組:

DML(Data Manipulation Language,數據操作語言):用於檢索或者修改數據;

DDL(Data Definition Language,數據定義語言): 用於定義數據的結構,比如 創建、修改或者刪除資料庫對象;

DCL(Data Control Language,數據控制語言):用於定義資料庫用戶的許可權。

DML組可以細分為以下的幾個語句:

SELECT:用於檢索數據;

INSERT:用於增加數據到資料庫;

UPDATE:用於從資料庫中修改現存的數據

DELETE:用於從資料庫中刪除數據。

DDL語句可以用於創建用戶和重建資料庫對象。下面是DDL命令:

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE INDEX

DROP INDEX

DCL命令用於創建關係用戶訪問以及授權的對象。下面是幾個DCL命令:

ALTER PASSWORD

GRANT

REVOKE

CREATE SYNONYM

為了讓你對SQL有一個直觀的認識,下面先給出一個簡單SQL語句的例子:

我們使用SQL語句來從Employees中檢索Department ID為CS的姓名:

SELECT Employees.Name

FROM Employees

WHERE Employees.DeptID = "CS"

可能你現在一開始不太理解這些語句,也許你會一頭霧水,不要緊的,通過本教程的學習后,你會發現這段語句是多麼的普通。為了不讓你困惑,下面我也進行一番解釋:

先對FROM子句吧,語句中的FROM Employees意思是從Employees表中檢索數據。

而語句WHERE Employees.DeptID = "CS"意思是檢索Employees的DeptID列為」CS」的行,這樣SQL語句檢索的結果將是DeptID為CS的列的所有數據,比如:

EmpID Name Dept
123 Purple CS
124 Zsc CS


最後,我們來解釋一個SELECT子句,它指定了從Name列檢索來的所有數據,比如

Name
Purple
Zsc

好吧,開始我們下一節的教程-------表的基本知識。



SQL循序漸進(2)-------表的基礎知識

表的基礎知識

  關係資料庫通常包含多個表。資料庫實際上是表的集合,資料庫的數據或者信息都是存儲在表中的。表是對數據進行存儲和操作的一種邏輯結構,每一個表都代表一個對用戶意義的對象。例如,一個公司資料庫中,會有僱員表、部門表、庫存表、銷售表、工資表等等。我們經常見到的成績表就是一種表,它是有行和列組成的,我們並且可以通過名字來識別數據。列包含了列的名字、數據類型以及列的其它屬性;行包含了列的記錄或者數據。下面給出一個成績單,其中姓名、語文、數學、英語都是列,而行包含了這個表的數據,即每個人的各科成績:


姓名 語文 數學 英語
王小童 78 100 87
張柳風 85 92 95
紫雲飛 65 89 86
黃天龍 98 67 75


SQL循序漸進(3)-------數據檢索

數據檢索

在SQL中SELECT語句通常用於檢索資料庫,或者檢索滿足你設定條件的數據,以下是簡單的SELECT語句的格式:

select "column1"[,"column2",etc] from "tablename"

[where "condition"];

[] = optional

其中列的名字跟著SELECT關鍵字,它決定了哪一列將被作為結果返回。你可以任意指定多個列,或者你可以使用"*"來選擇所有的列。

表的名字是緊跟著FROM關鍵字的,它指出了哪個表格將作為最後結果被查詢。

而WHERE子句(可選)指出哪個數據或者行將被返回或者顯示,它是根據關鍵字WHERE後面描述的條件而來的。

在WHERE子句中可以有以下的條件選擇:

= 等於

> 大於

< 小於

>= 大於等於

<= 小於等於

<> 不等於

LIKE 參見以下註釋

註釋:LIKE 模式匹配操作符同樣可以使用在WHERE子句的條件條件中。LIKE是一個功能強大的操作符,它可以讓你選擇你"喜歡"指定的行。百分號"%" 可以被用來匹配任何可能的字元,它可以出現在指定字元的前面或者後面,例如:

select first, last, city

from empinfo

where first LIKE 'Er%';

以上這條SQL語句將會匹配任何名字以'Er'開始的名字,這裡必須使用單引號。

或者你也可以使用"%"在字元的前面,例如:

select first, last

from empinfo

where last LIKE '%s';

這條SQL語句將會匹配任何名字以's'結尾的名字。這個"%"的作用就跟DOS命令的"*"號很相似。

select * from empinfo

where first = 'Eric';

以上的SQL語句只選擇first名字為'Eric'的行。


SQL循序漸進(4)-------創建表


創建表

這個create table語句是用於創建一個新的表格。以下是一個簡單創建表格語句的格式:

create table "tablename"

("column1" "data type",

"column2" "data type",

"column3" "data type");

如果你想使用可選的約束,創建表格的格式為:

create table "tablename"

("column1" "data type" [constraint],

"column2" "data type" [constraint],

"column3" "data type" [constraint]);

[ ] = optional

這裡注意:你可以任意創建多列的表格,這個條件是可選的。

為了更好的理解,下面舉個例子:

create table employee

(first varchar(15),

last varchar(20),

age number(3),

address varchar(30),

city varchar(20),

state varchar(20));

為了創建一個新表格,你可以在關鍵字create table之後跟著表的名字,然後一個圓左括弧」(」,然後是第一列的名字,再是這一列的數據類型,接著是任意的可選約束,最後是圓右括弧」)」。確保在開始表格內容之前使用圓左括弧並且在表的最後一列定義之後使用圓右括弧是相當重要的。你還要保證每一個列定義之間有逗號分隔。最後在SQL語句結束時候加上分號";"。

表格和列名必須以字母開頭,第二個字元開始可以是字母、數字或者下劃線,但是要保證名字的總長度不要超過30個字元。在定義表格和列名不要使用不要使用 SQL預定的用於表格或者列名的關鍵字(比如"select"、"create"、 "insert"等等),以避免錯誤的發生。

SQL循序漸進(5)-------插入數據到表

插入數據到表

Insert語句用於往表格中插入或者增加一行數據,它的格式為:

insert into "tablename"

(first_column,...last_column)

values (first_value,...last_value);

[] = optional

簡單舉個例子:

insert into employee

(first, last, age, address, city)

values ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co');

這裡要注意:每一個字元竄都要用單引號括起來。

為了往表中插入數據,要在關鍵字insert into之後緊跟著表名,然後是左圓括弧,接著是以逗號分開的一系列的列名,再是一個右圓括弧,然後在關鍵字values之後跟著一系列用圓括弧括起的數值。這些數值是你要往表格中填入的數據,它們必須與指定的列名相匹配。字元串筆譯用單引號括起來,而數字就不用。在上面的例子中,'Luke'必須與列 first相匹配,而45必須與列age相匹配。

假如你想往employee表格中插入以下數據;

Zhang Weiguo,28,北京601信箱,北京

那麼你要使用以下的SQL語句:

insert into employee

(first, last, age, address, city)

values (' Zhang', ' Weiguo' ,28, '北京601信箱', '北京');


SQL循序漸進(6)-------刪除表

刪除表

Drop table命令用於刪除一個表格或者表中的所有行。其語法格式為:

drop table "tablename"

下面舉個例子:

drop table employee;

為了刪除整個表(包括所有的行),可以使用drop table命令后加上tablename。Drop table命令跟從表中刪除所有記錄是不一樣的:

  刪除表中的所有記錄是留下表格(只是它是空的)以及約束信息;而drop table是刪除表的所有信息,包括所有行、表格以及約束信息等等。


SQL循序漸進(7)-------更新記錄

更新記錄

Update語句用於更新或者改變匹配指定條件的記錄,它是通過構造一個where語句來實現的。其語句格式如下:

update "tablename"

set "columnname" = "newvalue"[,"nextcolumn" = "newvalue2"...]

where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"];

[] = optional

下面舉個例子來說明:

update phone_book

set area_code = 623

where prefix = 979;

以上語句是在phone_book表中,在prefix=979的行中將area_code設置為623。

update phone_book

set last_name = 'Smith', prefix=555, suffix=9292

where last_name = 'Jones';

而以上的這段語句是在phone_book中,在last_name= 'Jones'的行中將last_name 設置為 'Smith', prefix為555, suffix為9292。

update employee

set age = age+1

where first_name='Mary' and last_name='Williams';

這段語句是在employee表中,在first_name='Mary' 和last_name='Williams'的行中將age加1。

作為每課一練,你在結束本教程之後要好好作以下的練習:

1 因為Jonie Weber 已經跟Bob Williams結婚,所以它需要將它的last名更新為Weber-Williams。

2 Dirk Smith的生日是今天,所以他的年齡應該加1。

3 所有的秘書都叫做"Administrative Assistant".所以要將所有的標題標題都相應地修改。

就作這幾個練習,千萬不可大意喲。

SQL循序漸進(8)-------刪除記錄

刪除記錄

Delete語句是用來從表中刪除記錄或者行,其語句格式為:

delete from "tablename"

where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"];

[ ] = optional

下面還是舉個例子:

delete from employee;

這條語句沒有where語句,所以它將刪除所有的記錄,因此如果沒有使用where的時候,要千萬小心。

如果你只要刪除其中一行或者幾行,可以參考以下的語句:

delete from employee

where lastname = 'May';

這條語句是從emplyee表中刪除lastname為'May'的行。

delete from employee

where firstname = 'Mike' or firstname = 'Eric';

這條語句是從emplyee表中刪除firstname為'Mike'或者'Eric'的行。

為了從表中刪除一個完整的記錄或者行,就直接在"delete from"後面加上表的名字,並且利用where指明符合什麼條件的行要刪除即可。如果你沒有使用where子句,那麼表中的所有記錄或者行將被刪除。

SQL循序漸進(9)-------SELECT語句

SELECT語句

  在上面的教程中已經有用到SELECT語句。在本節教程中將詳細對它進行闡述。SELECT語句是SQL的核心,在你的SQL語句中可能用的最多的就是SELECT語句了。由於大量的選項可以用於SELECT語句,所以整個教程好象就是圍這SELECT語句轉。當我們構造SQL查詢語句(利用了 SELECT語句)的時候,認識所有的可能選項和最好的或者最有效率的方法來實現是很有用的。這個教程將為你提供這些技能。

  SELECT語句用於查詢資料庫並檢索匹配你指定條件的選擇數據。SELECT語句有五個主要的子句子你可以選擇,而FROM是唯一必須的子句。每一個子句有大量的選擇項、參數等等。這些子句將羅列在下面,而且它們每一個都將在以後的教程有更為詳細的描述。

以下是SELECT語句的格式:

SELECT [ALL | DISTINCT] column1[,column2]

FROM table1[,table2]

[WHERE "conditions"]

[GROUP BY "column-list"]

[HAVING "conditions]

[ORDER BY "column-list" [ASC | DESC] ]

下面舉個例子:

SELECT name, age, salary

FROM employee

WHERE age > 50;

上面的這個語句將從employee表中選擇age大於50的所有的name、age和salary列的數值。

注意:一定要在SQL語句末尾加上一個分號。這個分號提示SQL語句已經結束並準備被解釋。

以下的表格給出了各種比較運算符號:

= 等於

> 大於

< 小於

>= 大於等於

<= 小於等於

<> 不等於

LIKE 字元串比較測驗



舉個例子吧:

SELECT name, title, dept

FROM employee

WHERE title LIKE 'Pro%';

上面的語句是從employee表中選擇title是以'Pro'為開頭的name、title和dept列中的所有行或者數值。

另外ALL和DISTINCT也是SQL中的關鍵字,它們用於在你的查詢結果中選擇ALL(預設)或者"distinct"或者單一記錄。如果你想在指定的列中檢索單一記錄,你可以使用"DISTINCT" 關鍵子。 因為DISTNCT 將會丟棄所有你在SELECT指定的列複製的記錄,比如 :

SELECT DISTINCT age

FROM employee_info;

這條語句將返回所有在employee_info表中單一的age數據。

而ALL就將顯示所有指定的類,包括所有的複製數據。在沒有指定的時候,這個ALL關鍵字是預設的。

SQL循序漸進(10)------合計函數


合計函數

所有的合計函數如下表所示:

MIN 返回一個給定列中最小的數值

MAX 返回一個給定列中最大的數值

SUM 返回一個給定列中所有數值的總和

AVG 返回一個給定列中所有數值的平均值

COUNT 返回一個給定列中所有數值的個數

COUNT(*) 返回一個表中的行數


合計函數用於從SELECT語句中計算一個」返回列的數據」。它們是總結了所選數據列的結果。雖然它們需要"GROUP BY"子句(後面一個教程介紹),但是這些函數也可以在不用使用"GROUP BY"子句的情況被使用,比如 :

SELECT AVG(salary)

FROM employee;

這條語句將返回單一的結果,它包含了從employee表中所有salary列數據的平均值。為了更好的理解,我們再舉個例子:

SELECT AVG(salary)

FROM employee;

WHERE title = 'Programmer';

以上這條語句將返回employee表中所有title列為'Programmer'的數據的平均值。

下面的例子中使用的語句跟其它合計函數有點不用,因為沒有一個類被指定給COUNT函數。這條語句實際上將返回employee表的行數,如下:

SELECT Count(*)

FROM employees;


最後給出本節教程的配套練習:

1) 作一個公司的銷售表items_ordered,裡面有price、product和amount。

從items_ordered表中選擇price最大的數據。這裡提示:使用MAX函數。

2) 計算items_ordered表中的行數。

SQL循序漸進(11)------GROUP BY子句

GROUP BY子句

首先講講GROUP BY 子句語法:

SELECT column1, SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list";

這個GROUP BY子句將集中所有的行在一起,它包含了指定列的數據以及允許合計函數來計算一個或者多個列。當然最好解釋的方法是給出一個例子啦:

假設我們將從employee表中搜索工資最高的列,可以使用以下的SQL語句:

SELECT max(salary), dept

FROM employee

GROUP BY dept;

這條語句將在每一個單獨的部門中選擇工資最高的工資。結果他們的salary和dept將被返回。


SQL循序漸進(12)------HAVING子句

HAVING子句

下面先給出HAVING子句的語法:

SELECT column1, SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list"

HAVING "condition";

這個HAVING子句允許你為每一個組指定條件,換句話說,可以根據你指定的條件來選擇行。如果你想使用HAVING子句的話,它應該處再GROUP BY子句之後。

下面將以一個例子來解釋HAVING子句。假設我們的employee表中包含僱員的name、departmen、salary和age。如果你想為每個部門中每個僱員選擇平均工資的話,你可以使用下面的SQL語句:

SELECT dept, avg(salary)

FROM employee

GROUP BY dept;

當然,如果你還想只計算和顯示salary大於20000的平均工資的話,你還可以加上HAVING子句:

SELECT dept, avg(salary)

FROM employee

GROUP BY dept

HAVING avg(salary) > 20000;


SQL循序漸進(13)------ORDER BY子句 [

ORDER BY子句

ORDER BY子句的語法為:

SELECT column1, SUM(column2)

FROM "list-of-tables"

ORDER BY "column-list" [ASC | DESC];

[ ] = optional

ORDER BY是一個可選的子句,它允許你根據指定要order by的列來以上升或者下降的順序來顯示查詢的結果。例如:

ASC = Ascending Order ? 這個是預設的

DESC = Descending Order

下面舉個例子:

SELECT employee_id, dept, name, age, salary

FROM employee_info

WHERE dept = 'Sales'

ORDER BY salary;

這條SQL語句將從employee_info表中列dept等於'Sales'選擇employee_id,、dept、 name、 age和 salary,並且根據他們的salary按升序的順序來列出檢索結果。

如果你想對多列排序的話,那麼在列與列之間要加上逗號,比如 :

SELECT employee_id, dept, name, age, salary

FROM employee_info

WHERE dept = 'Sales'

ORDER BY salary, age DESC;


SQL循序漸進(14)------組合條件和布爾運算符

組合條件和布爾運算符

以下的SQL語句中就含有組合條件:

SELECT column1, SUM(column2)

FROM "list-of-tables"

WHERE "condition1" AND "condition2";

AND運算符可以在WHERE子句中連接兩個或者多個條件。AND條件的兩旁必須都為true(真),即兩個條件都同時滿足的時候,這些行才將被顯示。

當然,你也可以使用OR運算符,它也可以在WHERE子句中連接兩個或者多個條件。但是,只要OR運算符兩旁有一個為true的時候條件就滿足了,因此行才會被顯示。所以你使用OR運算符的時候,可以是OR運算符兩旁只有一個為true或者兩旁都為true。

下面舉個例子吧:

SELECT employeeid, firstname, lastname, title, salary

FROM employee_info

WHERE salary >= 50000.00 AND title = 'Programmer';

這條SQL語句是從employee_info表中選擇salary大於等於50000.00並且title等於'Programmer'的列 employeeid、 firstname、 lastname、 title和 salary。此時必須AND運算符兩旁的條件都為真,行才會最為檢索結果返回。如果其中有一個條件為假,那麼就什麼都沒有顯示。

你可以使用圓括弧將條件括起來,雖然它們也不一定是必須的,但是括起來看起來更清晰一些,這是一個編程習慣的問題。比如 :

SELECT employeeid, firstname, lastname, title, salary

FROM employee_info

WHERE (salary >= 50000.00) AND (title = 'Programmer');

下面再舉個例子:

SELECT firstname, lastname, title, salary

FROM employee_info

WHERE (title = 'Sales') OR (title = 'Programmer');

這條語句將從employee_info表中選擇title等於'Sales'或者等於'Programmer'的列firstname、 lastname, title和 salary。

SQL循序漸進(15)------IN 和 BETWEEN 條件運算符


IN 和 BETWEEN 條件運算符

下面是IN條件運算符的SQL語句:

SELECT column1, SUM(column2)

FROM "list-of-tables"

WHERE column3 IN (list-of-values);

下面是BETWEEN條件運算符的SQL語句:

SELECT column1, SUM(column2)

FROM "list-of-tables"

WHERE column3 BETWEEN value1 AND value2;

實際上,IN條件運算符是一個設置成員測試運算符,也就是說,它用於測試是否一個數值處在IN關鍵字之後提供的數值之中。舉個例子如下:

SELECT employeeid, lastname, salary

FROM employee_info

WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

這條語句是從employee_info表中選擇lastname等於Hernandez、Jones、 Roberts或者 Ruiz名字之一的列employeeid、 lastname和 salary。如果它在其中就將返回行。

IN條件運算符可以使用混合條件來替代,比如你可以使用等號運算符或者使用OR運算符等等,但是結果是一樣的,例如:

SELECT employeeid, lastname, salary

FROM employee_info

WHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts' OR lastname = 'Ruiz';

你可以觀察到,利用IN運算符時語句會更加簡短並且容易讀,特別是在你測試兩個或者三個數值以上的時候尤為突出。

當然你也可以使用NOT IN 來在你的列表中排除行的。


而BETWEEN條件運算符是用與測試一個數值是否處在BETWEEN關鍵字兩邊指定數值的中間,比如:

SELECT employeeid, age, lastname, salary

FROM employee_info

WHERE age BETWEEN 30 AND 40;

這條SQL語句是從employee_info表中選擇age處於30到40歲之間(包括30歲和40歲)的列employeeid、age、 lastname和salary。

這條語句同樣可以不用BETWEEN運算符,而使用混合條件來替代,例如:

SELECT employeeid, age, lastname, salary

FROM employee_info

WHERE age >= 30 AND age <= 40;

當然,你也可以類似於NOT IN的方法,使用NOT BETWEEN來排除一些數據。

SQL循序漸進(16)------數學運算符

數學運算符

標準的ANSI SQL-92支持下面四個基本的算術運算符:


+


-


*


/


%
求余


其中求余運算符決定除法的餘數。這個運算符不是ANSI SQL支持的,但是,絕大多數的資料庫支持他。下面是一些有用的數學函數,因為可能要用到它,所以我這裡要集中提一下。在ANSI SQL-92中不支持這些函數,但是它們可能對於某些特殊的RDBMS是有效的。然而它們對於幾個主要的資料庫系統都是有效的。下面就說說這些數學函數吧:


ABS(x)
返回x的絕對值

SIGN(x)
當x為負數、零、正數的時候分別返回x的符號-1、0或者1

MOD(x,y)
返回x除以y的餘數,跟x%y作用一樣

FLOOR(x)
返回小於等於x的最大整數

CEILING(x) 或 CEIL(x)
返回大於等於x的最小整數

POWER(x,y)
返回x的y次方的數值

ROUND(x)
返回最接近於x的數

ROUND(x,d)
返回小數點數為4的接近於x的數

SQRT(x)
返回x的平方根


下面舉個例子:

SELECT round(salary), firstname

FROM employee_info

上面這條語句將從employee_info表中選擇salary最接近的數以及firstname列。

SQL循序漸進(17)------JOIN子句 [

JOIN子句

  不知你有沒有發現直到現在我們利用SELECT語句來檢索的時候只能從一個表中進行。如果你想從兩個表或者更多的表中進行檢索,該怎麼辦呢?好在我們可以使用SQL和關係資料庫系統的一個很有用的特性,即"Join"。為了簡單說明,實際上"Join"就是使得關係資料庫系統相關的東東。"Join" 允許你從兩個表或者更多的表連接數據進行數據檢索,而只需要利用一個SELECT語句。如果在FROM關鍵字之後有多個表的話,"Join"可以在SQL SELECT語句中識別它們。

下面舉個例子:

SELECT "list-of-columns"

FROM table1,table2

WHERE "search-condition(s)"

"Join"

通過示範當你只處理一個表的時候會發生什麼事情可以使得"Join"的解釋更簡單,所以這裡我沒有使用"Join"。這個單一的資料庫有事也被稱為 "flat table"(平表)。現在你有一個表的資料庫用來檢索所有顧客的信息以及他們從你的商店買了什麼,下面就是這個表的所有列:

id first last address city state zip date item price


每次一個新行被插入到表中,所有的列都將被更新,這樣就導致了不必要的」多餘數據」。比如,每次Jenny買東西,下面的行都將被插入到表中:


id first last address city state zip date item price

10982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 snowboard 25.00

10982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 shovel 15.00

10982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 gloves 15.00

10982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 lantern 35.00

10982 Wolfgang Jenny 300 N. 1st Ave Yuma AZ 85002 032299 tent 95.00

SQL循序漸進(18)------索引

索引

索引允許DBMS更快地訪問數據。系統創建了這個內部地數據結構(即索引),它導致當查詢以列為索引的時候搜索行,這樣查詢會快得多。這個索引對於給定列索引數值的時通知DBMS找到表中某一行,這有點象書的索引,它告訴你對於給定的字你能找到哪一頁。下面讓我們在AntiqueOwners列中為 OwnerID創建索引:

CREATE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID);

下面語句是為名字創建所以:

CREATE INDEX NAME_IDX ON ANTIQUEOWNERS (OWNERLASTNAME, OWNERFIRSTNAME);

為了刪除索引,你可以利用DROP:

DROP INDEX OID_IDX;

就象前面教程中,我們也可以"drop"(刪除)一個表。上面第二個例子中,是在兩列上創建索引的。

有些DBMS不強迫要求主鍵,換句話說就是,類的唯一性不會自動強制。這是什麼意思呢,好象聽起來雲里霧裡的。好吧,再給你舉個例子,如果你象插入另外一行到AntiqueOwners表中,比如這個OwnerID是02,有些系統可以讓你這樣做即使我們要求所有行的數值都要是不同的。為了避免兩行有相同的值,我們有一種方法來克服,就是在列上創建唯一的索引,而在這個列上我們需要它成為主鍵,這樣就可以系統不會出現複製的情況:

CREATE UNIQUE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID);

SQL循序漸進(19)------DISTINCT和排除複製

DISTINCT和排除複製

假如你象列出所有買過古董的ID和名字,很明顯,你可能會將所有的顧客都列出來而沒有考慮有些顧客是買過多講古董的,所以這時你會發現有些數據是重複的。這就意味著你需要通知SQL來排除複製的行,而不管這個顧客買過多少個古董只需要列出一次即可。為了實現這個目的,你可以使用DISTINCT關鍵字。

首先我們需要為AntiqueOwners表來一個equijoin以得到顧客的LastName和First的詳細數據。但是,你要考慮到 Antiques表中的SellerID列是AntiqueOwners表的一個外碼,所以顧客只能在AntiqueOwners表列出ID和名字的行才被列出。我們還想在列的數據中排除SellerID複製的發生,所以我們要在發生重複的列上使用DISTINCT 。

為了防止複製的發生,我們還想將LastName以字母順序排列,然後在按字母順序排列FirstName最後排列OwnerID,因此我們還必須使用ORDER BY子句,具體語句如下:

SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME

FROM ANTIQUES, ANTIQUEOWNERS

WHERE SELLERID = OWNERID

ORDER BY OWNERLASTNAME, OWNERFIRSTNAME, OWNERID

在這個例子中,因為每個人都買都一個件古董,所以我們將Lasname以字母順序列出所有的古董擁有者

SQL循序漸進(20)------Aliases 、In以及子查詢


Aliases 、In以及子查詢

在本節教程中,我們將要介紹Aliases、 In以及子查詢的用法。首先我們看一下一個查詢語句,它搜索所有已經定貨的顧客的LastName以及他們定什麼貨,語句如下:

SELECT OWN.OWNERLASTNAME Last Name, ORD.ITEMDESIRED Item Ordered

FROM ORDERS ORD, ANTIQUEOWNERS OWN

WHERE ORD.OWNERID = OWN.OWNERID

AND ORD.ITEMDESIRED IN


(SELECT ITEM

FROM ANTIQUES);

這條查詢語句的結果為:

Last Name Item Ordered

--------- ------------

Smith Table

Smith Desk

Akins Chair

Lawson Mirror

下面好好解釋一下上面的這條語句:

"Last Name" 和"Item Ordered"給出了報告的數據頭。

OWN & ORD是aliases(別名),它們使用在FROM子句中,可在它們的後面加一個點號再加列名就可以進行查詢了。這樣做就避免了模稜兩可的情況,特別是在equijoin WHERE子句中當兩個列都名為OwenerID的時候,而點號就通知SQL我們使用是兩個不同表的不同OwnerID。

這裡要注意,在FROM子句中Orders表被首先列出,並且確保AntiqueOwners表只用於詳細的信息(Last Name)。更為重要的,在WHERE子句中的AND強迫In子查詢被調用("= ANY" or "= SOME" 都等價於使用IN)。但這到底做了些什麼呢?當這個子查詢完成了,它就返回Antiques表的所有Items因為這裡沒有WHERE子句。然後,對於從 Orders表列出的行,ItemDesired必須在從Antiques表中返回的Items列表中,然後在定貨可以有另外的擁有者填寫的情況下列出一個item。你可以想想這個方法:子查詢從Orders 表中的每一個ItemDesired被比較中返回一系列的Items;如果ItemDesired是在從Antiques表中返回的,那麼條件才為真。

SQL循序漸進(21)------更多的子查詢

更多的子查詢

我們可以使用在SELECT查詢語句中再包括一個SELECT子查詢語句。舉個例子吧,首先我們列除所有購買貴重物品的顧客,貴重物品的標準是比所有賣出的物品價錢的平均值多100元的物品。具體語句如下:

SELECT OWNERID

FROM ANTIQUES

WHERE PRICE >

(SELECT AVG(PRICE) + 100

FROM ANTIQUES);

上面子查詢語句是計算物品的平均價格再加100元,並搜索所有在ANTIQUES表中PRICE大於這個數值的OWNERID。這裡你可以使用DISTINCT OWNERID來排除複製的現象。

下面的語句列出了所有在AntiqueOwners表中的有買過物品的人的LastName:

SELECT OWNERLASTNAME

FROM ANTIQUEOWNERS

WHERE OWNERID =


(SELECT DISTINCT BUYERID

FROM ANTIQUES);

這個子查詢返回了一系列的顧客,當且僅當物品擁有者的ID出現在子查詢的列表中,古董的擁有者的LastName才會顯示出來。

為了更新這個例子,我們假設有一個買過bookcase的顧客,他的FirstName在資料庫中出錯了,應該為John:

UPDATE ANTIQUEOWNERS

SET OWNERFIRSTNAME = 'John'

WHERE OWNERID =


(SELECT BUYERID

FROM ANTIQUES

WHERE ITEM = 'Bookcase');

上面的語句中的子查詢首先搜索買過bookcase的顧客的BuyerID,然後在外層的查詢中來更新他的FirstName。

SQL循序漸進(22)------EXISTS 和 ALL

EXISTS 和 ALL

EXISTS使用了一個子查詢作為條件,只有當子查詢返回行的時候這個條件才為真,如果子查詢不返回任何的行條件就為假。如果商店在處理Chair的時候,有個顧客想看看所有擁有者的列表,就可以使用EXSIST,語句如下:

SELECT OWNERFIRSTNAME, OWNERLASTNAME

FROM ANTIQUEOWNERS

WHERE EXISTS


(SELECT *

FROM ANTIQUES

WHERE ITEM = 'Chair');

如果在Antiques列中有Chair,那麼子查詢就會返回一行或者多行,就使得EXISTS子句為真,然後讓SQL列出擁有者來。如果沒有搜索到Chair,則沒有行被返回,條件就為假。

ALL是另外一個不尋常的關鍵字,因為ALL查詢通常可以用不同的方法來進行,並且可能是一種更為簡單的方法。舉個例子來說明吧:

SELECT BUYERID, ITEM

FROM ANTIQUES

WHERE PRICE >= ALL


(SELECT PRICE

FROM ANTIQUES);

上面這條語句將返回最高價格的Item以及它的買方。子查詢返回了Antiques表中的所有的Price列,而外層的查詢逐行查詢Antiques表,並且如果它的Price大於等於(或者ALL)列中的Prices,它就會被列出,它就是最好價格的Item。這裡必須使用">="的原因是最高價格的Item要等於列表中的最高價格,因為這個Item在Price列中。

SQL循序漸進(23) ------UNION 和 外部連接

UNION 和 外部連接

有些時候,你可以想一起看多個查詢的結果、組合它們的輸出,你可以使用UNION關鍵字。為了合併以下兩個查詢的輸出:顯示所有買方的ID和已經有定貨的顧客,你可以使用以下語句:

SELECT BUYERID

FROM ANTIQUEOWNERS

UNION

SELECT OWNERID

FROM ORDERS;

這裡要注意SQL要求SELECT的列表必須匹配,即列於數據類型匹配。在本例子中,BuyerID 和OwnerID都是相同的數據類型,同為Interger(整型)。同時還有一提的是,SQL但使用UNION的使用會進行自動複製排除。而在單一的查詢中,你就必須使用DISTINCT。

Outer Join(外部連接)通常是在JOIN查詢被聯合,而行沒有包括到JOIN中的時候使用,特別是在常量文本"flags"被包括的時候尤為有用。下面我們看看這個查詢先:

SELECT OWNERID, 'is in both Orders & Antiques'

FROM ORDERS, ANTIQUES

WHERE OWNERID = BUYERID

UNION

SELECT BUYERID, 'is in Antiques only'

FROM ANTIQUES

WHERE BUYERID NOT IN


(SELECT OWNERID

FROM ORDERS);

第一個查詢做了一個連接以列出兩個表中的每個owener,並且在ID後面放置一個標記線來重複引用。這個UNION合併了這個列表以及以下第二個的列表。第二個列表是列出不是在Orders 表的ID,這樣就產生了在JOIN查詢之外的ID列表,它是利用引用標籤列出的。這可能是一種最容易的方法來產生這個列表。

這個概念對於主鍵跟外碼有關的狀況是很有用的,但是有些主鍵的外碼值是NULL。比如,在一個表中,主鍵是salesperson,而在其它的表中主鍵是 customers,並且它們的salesperson列在相同的行。然而,如果salesperson沒有customers的時候,這個人的名字就不會出現在customer表中。如果所有salespersons的列表要顯示出來,那麼就要外部連接了。


SQL循序漸進(24) ------嵌入SQL

嵌入SQL

為了更好的理解嵌入SQL,本節利用一個具體例子來說明。嵌入SQL允許程序連接資料庫並且包括SQL代碼到程序中,這樣在程序中就可以對資料庫進行使用、操作以及處理數據等等。以下是用C語言編寫的使用嵌入SQL的常式,它將列印一個報告;這個程序必須在普通的編譯之前先預編譯SQL語句。嵌入SQL 對於不同系統是不一樣的,所以在不同的系統中對以下的程序稍作修改,特別是變數的聲明以及過程記錄等。在嵌入SQL時,考慮網路、資料庫管理系統、操作系統是相當重要的。

以下是詳細的代碼:

#include

/* 以下這部分是聲明主機變數,它將使用於程序中*/

EXEC SQL BEGIN DECLARE SECTION;

int BuyerID;

char FirstName[100], LastName[100], Item[100];

EXEC SQL END DECLARE SECTION;


/* 以下包括SQLCA變數,它可以用來進行錯誤檢查 */

EXEC SQL INCLUDE SQLCA;

main() {

/* 以下連接資料庫*/

EXEC SQL CONNECT UserID/Password;

/* 以下是連接資料庫並檢查是否有錯誤產生T */ if(sqlca.sqlcode) {

printf(Printer, "Error connecting to database server.\n");

exit();

}

printf("Connected to database server.\n");

/* 下面聲明一個 "Cursor"。它將在查詢結果多於一行的時候使用*/

EXEC SQL DECLARE ItemCursor CURSOR FOR

SELECT ITEM, BUYERID

FROM ANTIQUES

ORDER BY ITEM;

EXEC SQL OPEN ItemCursor;

/* 你可以在這裡還可以加入另外一些錯誤檢查的內容,這裡就省略了*/

/* 當這個CURSOR沒有數據, sqlcode將被產生以允許我們退出循環。這裡注意,為了簡單起見,我們使程序遇到錯誤的時候就退出任何的sqlcode。*/

EXEC SQL FETCH ItemCursor INTO :Item, :BuyerID;

while(!sqlca.sqlcode) {

EXEC SQL UPDATE ANTIQUES

SET PRICE = PRICE + 5

WHERE ITEM = :Item AND BUYERID = :BuyerID;

EXEC SQL SELECT OWNERFIRSTNAME, OWNERLASTNAME

INTO :FirstName, :LastName

FROM ANTIQUEOWNERS

WHERE BUYERID = :BuyerID;

printf("%25s %25s %25s", FirstName, LastName, Item);


EXEC SQL FETCH ItemCursor INTO :Item, :BuyerID;

}

/* 關閉CURSOR,提交變化並退出程序。*/

EXEC SQL CLOSE DataCursor;

EXEC SQL COMMIT RELEASE;

exit();

}








相關鏈接


· 更多的有關 K-資料庫-資料庫基礎
· 新聞提供 黃葉


--------------------------------------------------------------------------------

人氣最高的文章在 K-資料庫-資料庫基礎:
資料庫約束






[火星人 ] SQL循序漸進已經有472次圍觀

http://coctec.com/docs/linux/show-post-74137.html