SQLServer與ORACLE數(shù)據(jù)庫實現(xiàn)子查詢方法比較

1、SQL Server 和 ORACLE 數(shù)據(jù)庫實現(xiàn)子查詢方法比較內(nèi)容摘要數(shù)據(jù)庫查詢都是通過 SELECT 語句來完成的，子查詢就是在一個 SELECT 語 句中嵌套另一個 SELECT 語句，通過子查詢的嵌套可以實現(xiàn)復雜的數(shù)據(jù)查詢功能。通過具 體實例，研究比較 SQL Server 數(shù)據(jù)庫和 ORACLE 數(shù)據(jù)庫實現(xiàn)子查詢方法的異同點，對深入 理解和實際應用數(shù)據(jù)庫子查詢大有裨益。在 SQL Server 和 ORACLE 數(shù)據(jù)庫中，數(shù)據(jù)查詢都是通過 SELECT 語句來完成的， SELECT 語法提供了強大的查詢操作能力，可以查詢一個表或多個表。甚至可以在一個 SELECT 語句中嵌套另一個

2、SELECT 語句，即進行子查詢，但子查詢比較難以理解，本文 對此問題加以研究， 并對 SQL Server 數(shù)據(jù)庫和 ORACLE 數(shù)據(jù)庫實現(xiàn)子查詢的方法進行比較。1 SQL Server 數(shù)據(jù)庫實現(xiàn)子查詢功能在 SQL Server 中， 子查詢是可以嵌套在外部SELECT 、 INSERT 、 UPDATE 或 DELETE 語句的 WHERE 或 HAVING 子句內(nèi)，或者其他子查詢中的查詢。任何允許使用表達式的地方都 可以使用子查詢。 SQL Server2005 中允許高達 32層嵌套，使用表達式的地方都可以使用子 查詢。 SQL Server 查詢語句基本格式如下：Select

3、select_listinto new_tableFrom table_sourcewhere search_conditiongroup by all group_by_expression , n with cube | rolluphaving search_conditionorder by order_expression asc | desc子查詢語句的寫法必須按照標準查詢語言的格式進行。1.1SQL Server 子查詢語句子查詢分為多行子查詢與單值子查詢。前者在通過 IN 或由 ANY 或 ALL 修改的比較運算 符引入的列表上操作和通過 EXISTS 引入的存在測試； 后者通

4、過未修改的比較運算符引入返 回單個值。包括子查詢的條件語句通常采用以下格式中的一種 1 ：WHERE expression NOT IN (subquery)WHERE expression comparison_operator ANY| ALL(subquery)WHERE NOT EXISTS （subquery）如以下代碼在學生和成績兩個表中查詢最低成績學生的學號、姓名和成績，子查詢嵌套在SELECT 語句中：Select 學號,姓名 ,（ select min（ 成績 .成績 ） from 成績WHERE 成績.學號=學生 .學號） AS 最低成績FROM 學生再如，查詢“成績表”中

5、成績大于所有課程名末尾包含“學”的同學的成績，并輸出學號、 姓名、班級編號、課程名、成績和校名。子查詢嵌套在 WHERE 語句中，代碼如下：SELECT a.學號,a姓名，a.班級編號，b.課程名，b成績,a.校名FROM 學生 a INNER JOIN 成績 b ON a.學號=b.學號WHERE b. 成績 ALL（SELECT 成績 FROM 成績 WHERE 課程名 LIKE %學' ）單值子查詢指執(zhí)行子查詢語句獲得的結果返回值是單值， 需要將一列值與子查詢返回的值進 行比較，完成外層查詢處理。當子查詢的返回值只有一個時，可以使用比較運算符（=、 、=、=、!=）將父查詢與子查

6、詢連接起來。如：查詢“學生”表中學號等于成績表中課程名為“計算機網(wǎng)絡”的學號，且按學號和姓名輸出 行記錄信息。代碼如下：SELECT 學號 ，姓名 FROM 學生WHERE 學號 =（SELECT 學號 FROM 成績WHERE 課程名 ='計算機網(wǎng)絡' ）1.2 SQL Server嵌套子查詢的實現(xiàn)子查詢能以多層嵌套的形式出現(xiàn)，子查詢的嵌套（也叫嵌套子查詢）是指在一個SELECT查詢語句中再次使用另一個 SELECT 查詢語句。如下面的例題。例：在 SQL Server 的示例數(shù)據(jù)庫 Northwind 中查詢哪些客戶訂購了 Bigfoot Breweries 的產(chǎn)品。 分析

7、：需要的數(shù)據(jù)分布在五個表：Suppliers、products、order details 、order、 Customers 中。在 SQL Server 中，可以從內(nèi)壓式 (outside-in) 寫程序，但從外壓式 (inside-out) 開始考慮非常有 幫助，即可以一次解決需要的一個語句。如果從內(nèi)到外寫起， 先從 Suppliers 表中查找產(chǎn)品名稱為 “ Bigfoot Breweries ”，同時與 Products SupplierlD相同的產(chǎn)品的 ProductID，再在Products表中用IN關鍵字與其匹配，在order details 表中查找這些 SupplierID

8、 的產(chǎn)品對應的 OrderID ，再在 Orders 表中查找與 OrderID 匹配的 CustomerID ，最后在 Customers 表中找到這些 CustomerID 對應的 CompanyName 值。代碼如 下：Use NorthwindgoSELECT DISTINCT CompanyName FROM CustomersWHERE CustomerID IN (SELECT CustomerId FROM OrdersWHERE OrderID IN (SELECT OrderID FROM order detailsWHERE ProductID IN (SELECT Pr

9、oductID FROM productsWHERE EXISTS(SELECT * FROM Suppliers WHERE CompanyName='Bigfoot Breweries' AND Suppliers.SupplierID=Products.SupplierID)ORDER BY companyName ASCGO這個例子揭示了有關 SQL Server 嵌套子查詢的基本原理，可以看出，可以用 IN() 參數(shù)替代 SELECT 語句。在本例中，有三次應用，因此創(chuàng)建了一個嵌套子查詢。2 ORACLE 數(shù)據(jù)庫子查詢方法ORACLE 數(shù)據(jù)庫子查詢或者嵌套子查詢也同樣

10、要遵守 SELECT 語句的語法格式。2.1 ORACLE 數(shù)據(jù)庫的查詢語句ORACLE 數(shù)據(jù)庫 SELECT 語句的基本語法格式為：Select all distinct select_listfromtable_name|view_name | select_statementWhere clausegroup by group_by_expressionhaving serach_conditionstart with connect byunion | union all| in tersect | minus select order by clausefor update2.2 O

11、RACLE數(shù)據(jù)庫與SQL Server數(shù)據(jù)庫子查詢的相同方法從上面的語法格式看，在SELECT、 WHERE 、 HA VING 等子句中子查詢方法與 SQL Server基本相同。(下面的例題基于 ORACLE 數(shù)據(jù)庫中示例數(shù)據(jù)庫) 。例 1在 SELECT 子句中使用子查詢。 查詢每個部門中個人工資大于該部門平均工資的員工 信息。代碼如下：SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a,(SELECT department_id, A VG(salary) salavg FROM employ

12、eesGROUP BY department_id) bWHERE a.department_id = b.department_id AND a.salary > b.salavg;例 2.在 WHERE 語句中嵌套子查詢。下面的例子查詢那些高于同類產(chǎn)品的平均價格的產(chǎn)品。SELECT product_id,product_type_id,name,priceFROM products outerWHERE price>(SELECT AVG(price) FROM products innerWHERE duct_type_id=duct_ty

13、pe_id);2.3 ORACLE 數(shù)據(jù)庫實現(xiàn)子查詢功能的不同方法要實現(xiàn)子查詢功能，除上述方法之外， ORACLE 數(shù)據(jù)庫還可以通過以下方法實現(xiàn)。(1) 使用 with 語句。下面的例子是查詢部門總薪水大于各部門平均薪水的那些部門信息。WITHdept_costs AS ( - 定義了一個臨時表： dept_costsSELECT d.department_name, SUM(e.salary) AS dept_totalFROM employees e, departments dWHERE e.department_id = d.department_idGROUP BY d.depart

14、ment_name),avg_cost AS ( - 再定義一個臨時表： avg_costSELECT SUM(dept_total)/COUNT(*) AS dept_avgFROM dept_costs) - 第二張臨時表里面引用了前面定義的臨時表和其中的列 dept_total/* 最后的查詢語句中使用了前面的臨時表 */SELECT * FROM dept_costsWHERE dept_total >(SELECT dept_avg FROM avg_cost) ORDER BY department_name;從此例可以看出，通過WITH語句，先定義一個臨時表dept_cos

15、ts，查詢出各部門名稱及各部門的總薪水，再定義一個臨時表avg_cost,查詢出各部門的平均薪水，最后的查詢中使用前面建立的臨時表， 查詢出部門總薪水大于各部門平均薪水的部門信息。 這樣實現(xiàn)了子查詢 功能，但結構更加清晰，更容易理解。(2) 在ORACLE子查詢中可以使用 start withconnect by從句。如以下代碼按照員工隸屬 關系從員工號 =1 開始查找所有員工的隸屬關系信息：SELECT employee_id, manager_id,first_name,last_nameFROM more_employeesSTART WITH employee_id = 1CONNEC

16、T BY PRIOR manager_id = employee_id ；本例實際上使用的是 ORACLE 數(shù)據(jù)庫層次樹型查詢功能，也能夠?qū)崿F(xiàn)子查詢功能。(3) ORACLE 數(shù)據(jù)庫中，用子查詢的方法查找結果集中的前幾條記錄， 其方法與 SQL Server方法不同。例如，在 emp表中查詢?nèi)肼氉钤绲那?個人，代碼如下：Select * From (Select * From (Select * From (Select * From emp Order By hiredate Asc)Where Rownum<6) Order By hiredate Desc)Where Rownum

17、=13 結語本文首先討論了 SQL Server 和 ORACLE 數(shù)據(jù)庫查詢語句的基本語法， 然后討論了兩種數(shù)據(jù) 庫下子查詢的基本方法， 并通過具體事例對兩種數(shù)據(jù)庫實現(xiàn)子查詢功能的方法進行了比較研 究。本文所使用的數(shù)據(jù)庫主要是SQL Server和Oracle系統(tǒng)自帶的示例數(shù)據(jù)庫，所有程序都經(jīng)過上機測試。 數(shù)據(jù)庫查詢是所有數(shù)據(jù)庫系統(tǒng)中非常重要的一項技術， 多表聯(lián)接查詢的效率比子 查詢效率要高，從提高效率的角度講，應該使用多表聯(lián)接查詢，另外SQL Server數(shù)據(jù)庫與ORACLE 數(shù)據(jù)庫在查詢方面還有其他的一些區(qū)別，本文不再贅述。參考文獻：1 虞益成 .SQL Server2005 數(shù)據(jù)庫應用技術 M. 北京：中國鐵道出版社，2004： 176-180.2 Robert G .Freeman.Protable DBA Oracle:Essential Techniques for the Day-to Day DBAM. 商 云飛，李勇 譯.Oracle DBA必備技能詳解M.北京：清華