top
Loading...
掌握SQL四條最基本的數據操作語句

掌握sql四條最基本的數據操作語句:insert,select,update和delete。

練掌握sql是數據庫用戶的寶貴財 富。在本文中,我們將引導你掌握四條最基本的數據操作語句—sql的核心功能—來依次介紹比較操作符、選擇斷言以及三值邏輯。當你完成這些學習后,顯然你已經開始算是精通sql了。

在我們開始之前,先使用create table語句來創建一個表(如圖1所示)。ddl語句對數據庫對象如表、列和視進行定義。它們并不對表中的行進行處理,這是因為ddl語句并不處理數據庫中實際的數據。這些工作由另一類sql語句—數據操作語言(dml)語句進行處理。

sql中有四種基本的dml操作:insert,select,update和delete。由于這是大多數sql用戶經常用到的,我們有必要在此對它們進行一一說明。在圖1中我們給出了一個名為employees的表。其中的每一行對應一個特定的雇員記錄。請熟悉這張表,我們在后面的例子中將要用到它。

連接查詢

通過連接運算符可以實現多個表查詢。連接是關系數據庫模型的主要特點,也是它區別于其它類型

數據庫管理系統的一個標志。

在關系數據庫管理系統中,表建立時各數據之間的關系不必確定,常把一個實體的所有信息存放在

一個表中。當檢索數據時,通過連接操作查詢出存放在多個表中的不同實體的信息。連接操作給用戶帶來很大的靈活性,他們可以在任何時候增加新的數據類型。為不同實體創建新的表,爾后通過連接進行查詢。

連接可以在select 語句的from子句或where子句中建立,似是而非在from子句中指出連接時有助于

將連接操作與where子句中的搜索條件區分開來。所以,在transact-sql中推薦使用這種方法。

sql-92標準所定義的from子句的連接語法格式為:

from join_table join_type join_table

[on (join_condition)]

其中join_table指出參與連接操作的表名,連接可以對同一個表操作,也可以對多表操作,對同一

個表操作的連接又稱做自連接。

join_type 指出連接類型,可分為三種:內連接、外連接和交叉連接。內連接(inner join)使用比

較運算符進行表間某(些)列數據的比較操作,并列出這些表中與連接條件相匹配的數據行。根據所使用的比較方式不同,內連接又分為等值連接、自然連接和不等連接三種。

外連接分為左外連接(left outer join或left join)、右外連接(right outer join或right join)

和全外連接(full outer join或full join)三種。與內連接不同的是,外連接不只列出與連接條件相匹配的行,而是列出左表(左外連接時)、右表(右外連接時)或兩個表(全外連接時)中所有符合搜索條件的數據行。

交叉連接(cross join)沒有where 子句,它返回連接表中所有數據行的笛卡爾積,其結果集合中的

數據行數等于第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數。

連接操作中的on (join_condition) 子句指出連接條件,它由被連接表中的列和比較運算符、邏輯

運算符等構成。

無論哪種連接都不能對text、ntext和image數據類型列進行直接連接,但可以對這三種列進行間接

連接。例如:

select p1.pub_id,p2.pub_id,p1.pr_info

from pub_info as p1 inner join pub_info as p2

on datalength(p1.pr_info)=datalength(p2.pr_info)

(一)內連接

內連接查詢操作列出與連接條件匹配的數據行,它使用比較運算符比較被連接列的列值。內連接分

三種:

1、等值連接:在連接條件中使用等于號(=)運算符比較被連接列的列值,其查詢結果中列出被連接

表中的所有列,包括其中的重復列。

2、不等連接: 在連接條件使用除等于運算符以外的其它比較運算符比較被連接的列的列值。這些

運算符包括>、>=、<=、<、!>、!<和<>。

3、自然連接:在連接條件中使用等于(=)運算符比較被連接列的列值,但它使用選擇列表指出查詢

結果集合中所包括的列,并刪除連接表中的重復列。

例,下面使用等值連接列出authors和publishers表中位于同一城市的作者和出版社:

select *

from authors as a inner join publishers as p

on a.city=p.city

又如使用自然連接,在選擇列表中刪除authors 和publishers 表中重復列(city和state):

select a.*,p.pub_id,p.pub_name,p.country

from authors as a inner join publishers as p

on a.city=p.city

(二)外連接

內連接時,返回查詢結果集合中的僅是符合查詢條件( where 搜索條件或 having 條件)和連接條件

的行。而采用外連接時,它返回到查詢結果集合中的不僅包含符合連接條件的行,而且還包括左表(左外連接時)、右表(右外連接時)或兩個邊接表(全外連接)中的所有數據行。

如下面使用左外連接將論壇內容和作者信息連接起來:

select a.*,b.* from luntan left join usertable as b

on a.username=b.username

下面使用全外連接將city表中的所有作者以及user表中的所有作者,以及他們所在的城市:

select a.*,b.*

from city as a full outer join user as b

on a.username=b.username

(三)交叉連接

交叉連接不帶where 子句,它返回被連接的兩個表所有數據行的笛卡爾積,返回到結果集合中的數

據行數等于第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數。

例,titles表中有6類圖書,而publishers表中有8家出版社,則下列交叉連接檢索到的記錄數將等

于6*8=48行。

select type,pub_name

from titles cross join publishers

order by type

union運算符可以將兩個或兩個以上上select語句的查詢結果集合合并成一個結果集合顯示,即執行聯

合查詢。union的語法格式為:

select_statement

union [all] selectstatement

[union [all] selectstatement][…n]

其中selectstatement為待聯合的select查詢語句。

all選項表示將所有行合并到結果集合中。不指定該項時,被聯合查詢結果集合中的重復行將只保留一

行。

聯合查詢時,查詢結果的列標題為第一個查詢語句的列標題。因此,要定義列標題必須在第一個查詢語句中定義。要對聯合查詢結果排序時,也必須使用第一查詢語句中的列名、列標題或者列序號。

在使用union 運算符時,應保證每個聯合查詢語句的選擇列表中有相同數量的表達式,并且每個查詢選擇表達式應具有相同的數據類型,或是可以自動將它們轉換為相同的數據類型。在自動轉換時,對于數值類型,系統將低精度的數據類型轉換為高精度的數據類型。

在包括多個查詢的union語句中,其執行順序是自左至右,使用括號可以改變這一執行順序。例如:

查詢1 union (查詢2 union 查詢3)

insert語句

用戶可以用insert語句將一行記錄插入到指定的一個表中。例如,要將雇員john smith的記錄插入到本例的表中,可以使用如下語句:

insert into employees values

(''smith'',''john'',''1980-06-10'',

''los angles'',16,45000);

通過這樣的insert語句,系統將試著將這些值填入到相應的列中。這些列按照我們創建表時定義的順序排列。在本例中,第一個值“smith”將填到第一個列last_name中;第二個值“john”將填到第二列first_name中……以此類推。

我們說過系統會“試著”將值填入,除了執行規則之外它還要進行類型檢查。如果類型不符(如將一個字符串填入到類型為數字的列中),系統將拒絕這一次操作并返回一個錯誤信息。

如果sql拒絕了你所填入的一列值,語句中其他各列的值也不會填入。這是因為sql提供對事務的支持。一次事務將數據庫從一種一致性轉移到另一種一致性。如果事務的某一部分失敗,則整個事務都會失敗,系統將會被恢復(或稱之為回退)到此事務之前的狀態。

回到原來的insert的例子,請注意所有的整形十進制數都不需要用單引號引起來,而字符串和日期類型的值都要用單引號來區別。為了增加可讀性而在數字間插入逗號將會引起錯誤。記住,在sql中逗號是元素的分隔符。

同樣要注意輸入文字值時要使用單引號。雙引號用來封裝限界標識符。

對于日期類型,我們必須使用sql標準日期格式(yyyy-mm-dd),但是在系統中可以進行定義,以接受其他的格式。當然,2000年臨近,請你最好還是使用四位來表示年份。

既然你已經理解了insert語句是怎樣工作的了,讓我們轉到employees表中的其他部分:

insert into employees values

(''bunyan'',''paul'',''1970-07-04'',

''boston'',12,70000);

insert into employees values

(''john'',''adams'',''1992-01-21'',

''boston'',20,100000);

insert into employees values

(''smith'',''pocahontas'',''1976-04-06'',

''los angles'',12,100000);

insert into employees values

(''smith'',''bessie'',''1940-05-02'',

''boston'',5,200000);

insert into employees values

(''jones'',''davy'',''1970-10-10'',

''boston'',8,45000);

insert into employees values

(''jones'',''indiana'',''1992-02-01'',

''chicago'',null,null);

在最后一項中,我們不知道jones先生的工薪級別和年薪,所以我們輸入null(不要引號)。null是sql中的一種特殊情況,我們以后將進行詳細的討論。現在我們只需認為null表示一種未知的值。

有時,像我們剛才所討論的情況,我們可能希望對某一些而不是全部的列進行賦值。除了對要省略的列輸入null外,還可以采用另外一種insert語句,如下:

insert into employees(

first_name, last_name,

hire_date, branch_office)

value(

''indiana'',''jones'',

''1992-02-01'',''indianapolis'');

這樣,我們先在表名之后列出一系列列名。未列出的列中將自動填入缺省值,如果沒有設置缺省值則填入null。請注意我們改變了列的順序,而值的順序要對應新的列的順序。如果該語句中省略了first_name和last_name項(這兩項規定不能為空),sql操作將失敗。

讓我們來看一看上述insert語句的語法圖:

insert into table

[(column { ,column})]

values

(columnvalue [{,columnvalue}]);

和前一篇文章中一樣,我們用方括號來表示可選項,大括號表示可以重復任意次數的項(不能在實際的sql語句中使用這些特殊字符)。value子句和可選的列名列表中必須使用圓括號。

select語句

select語句可以從一個或多個表中選取特定的行和列。因為查詢和檢索數據是數據庫管理中最重要的功能,所以select語句在sql中是工作量最大的部分。實際上,僅僅是訪問數據庫來分析數據并生成報表的人可以對其他sql語句一竅不通。

select語句的結果通常是生成另外一個表。在執行過程中系統根據用戶的標準從數據庫中選出匹配的行和列,并將結果放到臨時的表中。在直接sql(direct sql)中,它將結果顯示在終端的顯示屏上,或者將結果送到打印機或文件中。也可以結合其他sql語句來將結果放到一個已知名稱的表中。

select語句功能強大。雖然表面上看來它只用來完成本文第一部分中提到的關系代數運算“選擇”(或稱“限制”),但實際上它也可以完成其他兩種關系運算—“投影”和“連接”,select語句還可以完成聚合計算并對數據進行排序。

select語句最簡單的語法如下:

select columns from tables;

當我們以這種形式執行一條select語句時,系統返回由所選擇的列以及用戶選擇的表中所有指定的行組成的一個結果表。這就是實現關系投影運算的一個形式。

讓我們看一下使用圖1中employees表的一些例子(這個表是我們以后所有select語句實例都要使用的。而我們在圖2和圖3中給出了查詢的實際結果。我們將在其他的例子中使用這些結果)。

假設你想查看雇員工作部門的列表。那下面就是你所需要編寫的sql查詢:

select branch_office from employees;

以上select語句的執行將產生如圖2中表2所示的結果。

由于我們在select語句中只指定了一個列,所以我們的結果表中也只有一個列。注意結果表中具有重復的行,這是因為有多個雇員在同一部門工作(記住sql從所選的所有行中將值返回)。要消除結果中的重復行,只要在select語句中加上distinct子句:

select distinct branch_office

from employees;

這次查詢的結果如表3所示。

現在已經消除了重復的行,但結果并不是按照順序排列的。如果你希望以字母表順序將結果列出又該怎么做呢?只要使用order by子句就可以按照升序或降序來排列結果:

select distinct branch_office

from employees

order by branch_office asc;

這一查詢的結果如表4所示。請注意在order by之后是如何放置列名branch _office的,這就是我們想要對其進行排序的列。為什么即使是結果表中只有一個列時我們也必須指出列名呢?這是因為我們還能夠按照表中其他列進行排序,即使它們并不顯示出來。列名branch_ office之后的關鍵字asc表示按照升序排列。如果你希望以降序排列,那么可以用關鍵字desc。

同樣我們應該指出order by子句只將臨時表中的結果進行排序;并不影響原來的表。

假設我們希望得到按部門排序并從工資最高的雇員到工資最低的雇員排列的列表。除了工資括號中的內容,我們還希望看到按照聘用時間從最近聘用的雇員開始列出的列表。以下是你將要用到的語句:

select branch_office,first_name,

last_name,salary,hire_date

from employees

order by salary desc,

hire_date desc;

這里我們進行了多列的選擇和排序。排序的優先級由語句中的列名順序所決定。sql將先對列出的第一個列進行排序。如果在第一個列中出現了重復的行時,這些行將被按照第二列進行排序,如果在第二列中又出現了重復的行時,這些行又將被按照第三列進行排序……如此類推。這次查詢的結果如表5所示。

將一個很長的表中的所有列名寫出來是一件相當麻煩的事,所以sql允許在選擇表中所有的列時使用*號:

select * from employees;

這次查詢返回整個employees表,如表1所示。

下面我們對開始時給出的select語句的語法進行一下更新(豎直線表示一個可選項,允許在其中選擇一項。):

select [distinct]

(column [{, columns}])| *

from table [ {, table}]

[order by column [asc] | desc

[ {, column [asc] | desc }]];

定義選擇標準

在我們目前所介紹的select語句中,我們對結果表中的列作出了選擇但返回的是表中所有的行。讓我們看一下如何對select語句進行限制使得它只返回希望得到的行:

select columns from tables [where predicates];

where子句對條件進行了設置,只有滿足條件的行才被包括到結果表中。這些條件由斷言(predicate)進行指定(斷言指出了關于某件事情的一種可能的事實)。如果該斷言對于某個給定的行成立,該行將被包括到結果表中,否則該行被忽略。在sql語句中斷言通常通過比較來表示。例如,假如你需要查詢所有姓為jones的職員,則可以使用以下select語句:

select * from employees

where last_name = ''jones'';

last_name = ''jones''部分就是斷言。在執行該語句時,sql將每一行的last_name列與“jones”進行比較。如果某一職員的姓為“jones”,即斷言成立,該職員的信息將被包括到結果表中(見表6)。

使用最多的六種比較

我們上例中的斷言包括一種基于“等值”的比較(last_name = ''jones''),但是sql斷言還可以包含其他幾種類型的比較。其中最常用的為:

等于 =、不等于 <>、小于 <、大于 >、小于或等于 <=、大于或等于 >=

下面給出了不是基于等值比較的一個例子:

select * from employees

where salary > 50000;

這一查詢將返回年薪高于$50,000.00的職員(參見表7)。

邏輯連接符

有時我們需要定義一條不止一種斷言的select語句。舉例來說,如果你僅僅想查看davy jones的信息的話,表6中的結果將是不正確的。為了進一步定義一個where子句,用戶可以使用邏輯連接符and,or和not。為了只得到職員davy jones的記錄,用戶可以輸入如下語句:

select * from employees

where last_name = ''jones'' and first_name = ''davy'';

在本例中,我們通過邏輯連接符and將兩個斷言連接起來。只有兩個斷言都滿足時整個表達式才會滿足。如果用戶需要定義一個select語句來使得當其中任何一項成立就滿足條件時,可以使用or連接符:

select * from employees

where last_name = ''jones'' or last_name = ''smith'';

有時定義一個斷言的最好方法是通過相反的描述來說明。如果你想要查看除了boston辦事處的職員以外的其他所有職員的信息時,你可以進行如下的查詢:

select * from employees

where not(branch_office = ''boston'');

關鍵字not后面跟著用圓括號括起來的比較表達式。其結果是對結果取否定。如果某一職員所在部門的辦事處在boston,括號內的表達式返回true,但是not操作符將該值取反,所以該行將不被選中。

斷言可以與其他的斷言嵌套使用。為了保證它們以正確的順序進行求值,可以用括號將它們括起來:

select * from employees

where (last_name = ''jones''

and first_name = ''indiana'')

or (last_name = ''smith''

and first_name = ''bessie'');

sql沿用數學上標準的表達式求值的約定—圓括號內的表達式將最先進行求值,其他表達式將從左到右進行求值。

(T127)

作者:http://www.zhujiangroad.com
來源:http://www.zhujiangroad.com
北斗有巢氏 有巢氏北斗