Hướng dẫn SQL & XQuery cho IBM DB2, Phần 3: Phép nối và phép hợp SQL

Chia sẻ: Nguyen Nhi | Ngày: | Loại File: PDF | Số trang:52

Thêm vào BST

Báo xấu

153
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Các truy vấn phức tạp bao gồm nhiều hơn một bảng quan hệ Jessica Cao, Chuyên viên phát triển các công cụ đào tạo, EMC Bruce Creighton, Chuyên viên lập kế hoạch các phần kỹ năng, EMC Pat Moffatt, Giám đốc Chương trình quản lý thông tin, Sáng kiến học đường, EMC Tóm tắt: Hướng dẫn này mô tả hai cách kết hợp dữ liệu từ các bảng khác nhau trong một cơ sở dữ liệu IBM® DB2® nối các bảng với phép nối trong hay phép nối ngoài và cách sử dụng các toán tử UNION, EXCEPT và INTERSECT...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Hướng dẫn SQL & XQuery cho IBM DB2, Phần 3: Phép nối và phép hợp SQL

Hướng dẫn SQL & XQuery cho IBM DB2, Phần 3: Phép nối và phép hợp SQL Các truy vấn phức tạp bao gồm nhiều hơn một bảng quan hệ Jessica Cao, Chuyên viên phát triển các công cụ đào tạo, EMC Bruce Creighton, Chuyên viên lập kế hoạch các phần kỹ năng, EMC Pat Moffatt, Giám đốc Chương trình quản lý thông tin, Sáng kiến học đường, EMC Tóm tắt: Hướng dẫn này mô tả hai cách kết hợp dữ liệu từ các bảng khác nhau trong một cơ sở dữ liệu IBM® DB2® nối các bảng với phép nối trong hay phép nối ngoài và cách sử dụng các toán tử UNION, EXCEPT và INTERSECT để nhận được tập kết quả trung gian từ một biểu thức truy vấn và kết hợp nó với tập kết quả từ biểu thức truy vấn khác. Hướng dẫn này là Phần 3 của loạt bài hướng dẫn . Trước khi bạn bắt đầu Về loạt bài này Loạt bài hướng dẫn này dạy các đề tài SQL từ cơ bản đến cao cấp và XQuery cơ bản đồng thời chỉ ra cách diễn đạt các câu hỏi kinh doanh thường gặp như là các truy vấn cơ sở dữ liệu khi sử dụng các truy vấn SQL hay các truy vấn XQuery. Những người phát triển và những người quản trị cơ sở dữ liệu có thể sử dụng hướng dẫn này để nâng cao các kỹ năng truy vấn cơ sở dữ liệu của họ. Các thành viên sáng kiến học đường có thể sử dụng loạt bài hướng dẫn này làm một phần của chương trình giảng dạy cơ sở dữ liệu của họ. Tất cả các ví dụ trong tài liệu này được dựa trên Aroma, một cơ sở dữ liệu mẫu có chứa các dữ liệu doanh thu về các sản phẩm cà phê và chè đã bán trong các cửa hàng khắp nước Mỹ. Mỗi ví dụ gồm có ba phần: Một câu hỏi kinh doanh, được diễn đạt bằng ngôn ngữ hàng ngày  Một hoặc nhiều ví dụ truy vấn, được biểu diễn bằng SQL hay Xquery  Một bảng các kết quả được cơ sở dữ liệu trả về 
Cách trình bày hướng dẫn này cho phép các học viên tìm hiểu về ngôn ngữ SQL và XQuery. Cũng như với bất kỳ việc học tập nào, việc bổ sung thêm các bài tập thực hành là rất quan trọng. Dữ liệu và các định nghĩa bảng tạo điều kiện thuận lợi cho các bài tập thực hành này. Đối với các sinh viên khi sử dụng tài liệu này như phần của khoá học, hãy nhận từ người hướng dẫn của mình các chỉ dẫn kết nối tới cơ sở dữ liệu Aroma và tìm hiểu về bất kỳ các sự khác nhau nào giữa hướng dẫn và cài đặt tại chỗ của bạn. Hướng dẫn này được viết cho Express-C 9 DB2 trên nền UNIX®, Linux® và Windows® (trước đây gọi là Viper). Về hướng dẫn này Hướng dẫn này mô tả hai cách để kết hợp dữ liệu từ các bảng khác nhau trong một cơ sở dữ liệu IBM DB2: Bằng cách kết nối các bảng  Bằng cách sử dụng các toán tử UNION, EXCEPT, và INTERSECT  Phần đầu tiên của hướng dẫn này trình bày các ví dụ về các kết nối bên trong và bên ngoài. Phần thứ hai phần minh họa cách kết hợp dữ liệu từ các bảng khác nhau khi sử dụng các toán tử UNION, EXCEPT, và INTERSECT, để nhận được tập kết quả trung gian từ một biểu thức truy vấn và kết hợp nó với tập kết quả từ biểu thức truy vấn khác. Nối tới một cơ sở dữ liệu Bạn cần nối tới một cơ sở dữ liệu trước khi có thể sử dụng các câu lệnh SQL để truy vấn hay xử lý dữ liệu. Lệnh CONNECT kết hợp một kết nối c ơ sở dữ liệu với một tên người sử dụng. Thông qua người hướng dẫn tìm ra tên cơ sở dữ liệu bạn cần nối tới. Đối với loạt bài này, tên cơ sở dữ liệu là aromadb. Để nối tới cơ sở dữ liệu aromadb, gõ lệnh sau đây vào trong bộ xử lý dòng lệnh DB2:
CONNECT TO aromadb USER userid USING password Thay thế "userid" và "password" bằng số ID và mật khẩu của người sử dụng mà bạn đã nhận được từ thầy hướng dẫn. Nếu máy không hỏi userid và password thì chỉ cần gõ vào lệnh sau đây: CONNECT TO aromadb Thông điệp sau báo cho bạn biết rằng đã tạo kết nối thành công: Database Connection Information Database server = DB2/NT 9.0.0 SQL authorization ID = USERID Local database alias = AROMADB Khi đã kết nối được, bạn có thể bắt đầu sử dụng cơ sở dữ liệu. Các tên và các lược đồ bảng Một lược đồ là gì?
Một lược đồ được sử dụng để tạo nhóm. Nói đơn giản nhất, một lược đồ giống như là một khu vực trong một thư viện công cộng: các cuốn sách được nhóm lại thành các khu vực khác nhau. Để tìm các sách lịch sử bạn đi đến khu vực lịch sử. Tương tự, trong một cơ sở dữ liệu (giống như thư viện), các bảng được nhóm vào trong các lược đồ khác nhau. Khi một người sử dụng đăng nhập vào cơ sở dữ liệu, nhận dạng của người sử dụng trở thành một lược đồ mặc định cho tất cả các truy vấn. Ví dụ, nếu một người sử dụng có tên DBUSER thực hiện một truy vấn như SELECT ... FROM sales hệ thống hiểu điều đó có nghĩa là SELECT ... FROM dbuser.sales Nếu không có lược đồ dbuser trong cơ sở dữ liệu hệ thống trả về một lỗi. Có hai phương pháp để tránh lỗi này. Một là sử dụng các tên bảng đầy đủ cho mỗi bảng; hai là đặt một bộ nhận biết lược đồ mặc định khác. Sử dụng các tên bảng đầy đủ Bạn có thể nói rõ tên bảng hoàn toàn đầy đủ trong mệnh đề FROM của mỗi truy vấn. Để làm điều này, bạn thêm tên của lược đồ và một dấu chấm vào trước tên từng bảng như sau SELECT ... FROM aroma.sales ...
Có hai lợi thế rõ ràng khi sử dụng các tên bảng thích hợp: 1. Bất kỳ người sử dụng nào có thể xem xét truy vấn và biết chính xác truy vấn được viết đối với lược đồ nào. 2. Bạn có thể gộp các bảng từ các lược đồ khác vào trong cùng truy vấn, với điều kiện rằng người sử dụng có quyền truy nhập dữ liệu từ các lược đồ đó, như sau: SELECT ... FROM aroma.sales, dbuser.customer ... 3. Tuy nhiên, nếu bạn sẽ thực hiện nhiều truy vấn từ chỉ một lược đồ, bạn có thể không muốn phải gõ vào tất cả các tên bảng đầy đủ. Bạn có thể tiết kiệm gõ phím bằng cách cài đặt một lược đồ mặc định mới. Thiết lập một lược đồ mặc định mới Việc thiết lập một lược đồ mới chỉ cần một câu lệnh đơn giản: SET SCHEMA ; Từ điểm này trở đi, hệ thống sử dụng tên lược đồ mới như là lược đồ mặc định, vì vậy chỉ cần sử dụng các tên bảng không kèm tên lược đồ (unqualified). Bạn vẫn còn có thể bao gồm các bảng từ các lược đồ khác bằng cách sử dụng các tên bảng đầy đủ, như:
SET SCHEMA aroma; SELECT ... FROM sales, dbuser.customer ... Tuy nhiên, người sử dụng phải được cấp phép thích hợp để làm việc trong lược đồ mới, hoặc các truy vấn và các hành động khác sẽ thất bại. Việc thiết lập lược đồ chỉ duy trì hiệu lực trong phiên hiện tại của bạn. Nếu bạn ngắt ra khỏi cơ sở dữ liệu, bạn phải chạy lại lệnh SET cho loạt các truy vấn tiếp theo của mình. Các lưu ý về cách dùng Các truy vấn trong loạt bài này sẽ sử dụng các tên bảng đầy đủ. Các bí danh của bảng Bạn cũng có thể tiết kiệm gõ phím và làm cho các truy vấn dễ đọc hơn bằng cách tận dụng các bí danh bảng. Các bí danh bảng nói chung l à một tên được rút ngắn cho một bảng, được gán trong mệnh đề FROM và được sử dụng trong suốt phần còn lại của truy vấn. Chúng thường được sử dụng nhất khi một truy vấn kết nối nhiều bảng. Ví dụ, một truy vấn nối bảng đơn giản có thể là: SELECT date, dollars FROM aroma.period, aroma.sales WHERE aroma.period.perkey = aroma.sales.perkey
AND aroma.period.month = 'JAN' AND aroma.period.year = 2006; Bạn có thể viết lại truy vấn này khi sử dụng các bí danh bảng. Trong trường hợp này, chúng ta sẽ sử dụng bí danh "a" cho bảng Period và "b" cho các bảng Sales: SELECT date, dollars FROM aroma.period a, aroma.sales b WHERE a.perkey = b.perkey AND a.month = 'JAN' AND a.year = 2006; Mỗi tên bảng đầy đủ trong mệnh đề FROM được nối tiếp theo sau bằng một khoảng trống và tên bí danh. Bạn có thể gán các bí danh cho một vài, cho tất cả các bảng hay không cho bảng nào được liệt kê trong mệnh đề FROM. Tuy nhiên, khi bạn gán một bí danh bảng trong mệnh đề FROM bạn phải sử dụng tên bí danh mỗi khi bạn tham chiếu đến bảng đó trong truy vấn. Bạn sẽ gặp các thông báo lỗi nếu bạn cố sử dụng tên bảng đầy đủ ở nơi khác trong truy vấn. Khi gán một tên bí danh, bạn cũng phải cẩn thận không sử dụng tên của một bảng hiện có trong lược đồ hoặc các kết quả của bạn có thể sai. Trong ví dụ ở trên, giả thiết rằng người sử dụng dbuser đã đăng nhập và không thay đổi lược đồ mặc định. Nếu đã có một bảng được đặt tên "a" trong lược đồ, hệ thống sẽ tìm kiếm cột dbuser.a.perkey thay vì thay thế nó bằng giá trị aroma.period.perkey. Các lưu ý về cách dùng
Hầu hết các truy vấn trong hướng dẫn này sẽ lợi dụng ưu điểm của các bí danh bảng. Sử dụng phép nối đơn giản Câu hỏi Tổng doanh thu hàng ngày của các sản phẩm Lễ phục sinh đã bán ra vào kỳ nghỉ cuối tuần theo kiểu khuyến mại 900 trong năm 2005 l à bao nhiêu và các cửa hàng nào đã ghi nhận doanh thu đó? Ví dụ truy vấn SELECT prod_name, store_name, day, dollars FROM aroma.promotion a, aroma.product b, aroma.period c, aroma.store d, aroma.sales e WHERE a.promokey = e.promokey AND b.prodkey = e.prodkey AND b.classkey = e.classkey AND c.perkey = e.perkey AND d.storekey = e.storekey AND prod_name LIKE 'Easter%' AND day IN ('SA', 'SU') AND promo_type = 900 AND year = 2005; Kết quả
Prod_Name Store_Name DayDollars Easter Sampler Olympic Coffee SA 150.00 Basket Company Về truy vấn Câu hỏi kinh doanh này đòi hỏi nối năm bảng trong lược đồ bán lẻ Aroma: bảng sự kiện Sales và các bảng Product, Period, Store, và Promotion của nó. Xem lại Phần 1 trong loạt bài hướng dẫn này để thấy sự bố trí bảng cho cơ sở dữ liệu Aroma. Để nối các bảng trong một truy vấn, bạn phải cung cấp cho máy chủ c ơ sở dữ liệu các chỉ dẫn rõ ràng về cách thực hiện kết nối. Cách nối được chỉ rõ trong mệnh đề WHERE với năm điều kiện đơn giản để nối bảng Sales qua năm cột khóa chính của nó. Bảng Product có một khóa chính hai phần, vì vậy nó được nối tới bảng Sales trên hai cột : Prodkey và Classkey. Các lưu ý về cách dùng Bất kỳ hai bảng nào cũng có thể được nối qua các cột với các kiểu dữ liệu có thể so sánh được; phép nối không phụ thuộc vào các mối quan hệ của khóa chính với khóa ngoại được dùng trong ví dụ này. Sử dụng mệnh đề ORDER BY Câu hỏi Các số doanh thu của Assam Gold Blend và Earl Grey tại các cửa hàng cà phê hoà tan trong tháng mười một năm 2005 là bao nhiêu? Sắp xếp thứ tự các số này cho mỗi sản phẩm từ cao nhất tới thấp nhất. Ví dụ truy vấn SELECT prod_name, store_name, dollars FROM aroma.store a, aroma.sales b,
aroma.product c, aroma.period d WHERE a.storekey = b.storekey AND c.prodkey = b.prodkey AND c.classkey = b.classkey AND d.perkey = b.perkey AND (prod_name like 'Assam Gold%' OR prod_name LIKE 'Earl%') AND store_name LIKE 'Instant%' AND month = 'NOV' AND year = 2005 ORDER BY prod_name, dollars DESC; Kết quả Prod_Name Store_Name Dollars Assam Gold Blend Instant Coffee 96.00 Assam Gold Blend Instant Coffee 78.00 Assam Gold Blend Instant Coffee 66.00 Assam Gold Blend Instant Coffee 58.50 Assam Gold Blend Instant Coffee 58.50
Assam Gold Blend Instant Coffee 39.00 Assam Gold Blend Instant Coffee 39.00 Assam Gold Blend Instant Coffee 32.50 Earl Grey Instant Coffee 48.00 Earl Grey Instant Coffee 45.50 Earl Grey Instant Coffee 42.00 Earl Grey Instant Coffee 32.00 Earl Grey Instant Coffee 24.00 Earl Grey Instant Coffee 20.00 Sắp xếp thứ tự bảng kết quả: mệnh đề ORDER BY Bạn có thể sử dụng mệnh đề ORDER BY để sắp xếp bảng kết quả của một truy vấn theo các giá trị trong một hoặc nhiều cột đã chỉ ra. Thứ tự sắp xếp mặc định là tăng dần (ASC); từ khóa DESC thay đổi thứ tự sắp xếp thành giảm dần cho cột đã chỉ ra như sau: ORDER BY prod_name, 3 DESC
Cú pháp của mệnh đề ORDER BY SELECT column name(s) FROM table name(s) [WHERE search_condition] [ORDER BY order_list]; Một danh sách các cột mà dữ liệu được sắp xếp thứ tự theo các cột này. Các cột trong order_list không nhất thiết cần phải xuất hiện trong order_list select_list nhưng phải tồn tại trong các bảng đ ược tham chiếu trong mệnh đề FROM. Về truy vấn Ví dụ truy vấn lấy ra con số doanh thu Assam Gold Blend và Earl Grey tại cửa hàng cà phê hoà tan trong tháng Mười Một 2005. Truy vấn sắp xếp kết quả theo sản phẩm và tổng doanh thu hằng ngày. Các lưu ý về cách dùngs Mệnh đề ORDER BY phải tiếp theo sau các mệnh đề khác trong lệnh SELECT và bao gồm một danh sách các cột sẽ được sắp xếp theo thứ tự. Một cột có thể được tham chiếu theo tên, bí danh cột hay vị trí (số thứ tự) của nó trong danh sách select. Ví dụ, mệnh đề ORDER BY trên trang hiện tại có thể được viết như như sau:
ORDER BY prod_name, 3 DESC Bằng cách chỉ rõ các cột trong order_list mà không có trong danh sách tên cột, bạn có thể sắp xếp thứ tự dữ liệu theo các cột không được hiểu thị trong bảng kết quả. Nối hai bảng Sử dụng mẫu bảng sau cho các ví dụ truy vấn nối bảng ban đầu: State Table Region Table City State City Area Jacksonville FL Jacksonville South Miami FL Miami South Nashville TN New Orleans South Ví dụ truy vấn SELECT * FROM aroma.state, aroma.region;
Tích Đề các (thuộc tính nối bảng không được chỉ rõ) City State City Area Jacksonville FL Jacksonville South Jacksonville FL Miami South Jacksonville FL New Orleans South Miami FL Jacksonville South Miami FL Miami South Miami FL New Orleans South Nashville TN Jacksonville South Nashville TN Miami South Nashville TN New Orleans South Chú ý: các kết quả của bạn có thể được trình bày với thứ tự khác. Không có mệnh đề "ORDER BY", hệ thống trả về các hàng theo trình tự bất kỳ như chúng được tìm thấy. Ví dụ truy vấn
SELECT * FROM aroma.state, aroma.region WHERE state.city = region.city; Tập con của tích Đề các (thuộc tính nối bảng được chỉ rõ) State:City State:State Region:City Region:Area Jacksonville FL Jacksonville South Miami FL Miami South Phép nối trong Đa số các truy vấn kết nối thông tin từ các bảng khác nhau. Bất kỳ hai bảng n ào cũng có thể được nối qua các cột với các kiểu dữ liệu có thể so sánh được; các kết nối không phụ thuộc vào mối quan hệ của khóa chính với khóa ngoài. Tích Đề các Khi hai hoặc nhiều bảng được tham chiếu trong mệnh đề FROM của một truy vấn, máy chủ cơ sở dữ liệu nối các bảng. Nếu mệnh đề FROM cũng như mệnh đề WHERE không chỉ rõ một thuộc tính kết nối nào, máy chủ sẽ tính toán một tích Đề các bao gồm m* n hàng, ở đây m là số lượng các hàng trong bảng đầu tiên và n là số các hàng trong bảng thứ hai. Tích này là tập hợp của tất cả các cách kết hợp có thể được, tạo thành bằng cách nối một hàng từ bảng đầu tiên với một hàng từ bảng thứ hai. Tập con của tích Đề các Nếu các bảng được nối rõ ràng qua các cột với các kiểu dữ liệu có thể so sánh được, máy chủ sẽ tính toán một tập con của tích Đề các. Tập con này chỉ chứa
đựng những hàng nào mà ở đó các giá trị trong các cột dùng để kết nối khớp với nhau. Trong thời gian truy vấn, các tập con có chức năng như là một bảng dẫn xuất và có thể được kết nối với các bảng khác hay các kết quả của các biểu thức truy vấn khác. Về truy vấn Cả hai bảng State và Region chứa các cột City được chỉ rõ như là các cột kết nối trong mệnh đề WHERE. Vậy thì, chỉ có các hàng nào của tích Đề các mà có các khóa City khớp nhau, sẽ được hiển thị trong kết quả. Trong ví dụ truy vấn, bảng kết quả chỉ chứa có hai hàng, trong khi tích Đề các đầy đủ của cả hai bảng này có chín hàng. Một cách khác để kết nối các bảng Câu hỏi Hiển thị một danh sách của tất cả tên sản phẩm bắt đầu bằng chữ cái viết hoa "A" và các kiểu lớp của chúng. Sắp xếp danh sách sản phẩm theo thứ tự abc. Ví dụ truy vấn 1 SELECT prod_name, class_type FROM aroma.product t, aroma.class c WHERE t.classkey = c.classkey AND prod_name LIKE 'A%' ORDER BY prod_name; Ví dụ truy vấn 2
SELECT prod_name, class_type FROM aroma.product t JOIN aroma.class c ON t.classkey = c.classkey WHERE prod_name LIKE 'A%' ORDER BY prod_name; Hai truy vấn; cùng kết quả PROD_NAME CLASS_TYPE Aroma Roma Bulk_beans Aroma Roma Pkg_coffee ... Phép nối trong mệnh đề FROM Bạn có thể kết nối rõ ràng các bảng trong mệnh đề FROM khi sử dụng cú pháp ON như được chỉ ra trong ví dụ ở trên. Về truy vấn Truy vấn này nối các bảng Product và Class qua các cột với các tên giống nhau. Các giá trị kết quả trong mệnh đề ON bởi vậy giống với các giá trị hiện ra trong truy vấn có ràng buộc kết nối trong mệnh đề WHERE. Các lưu ý về cách dùng Không có sự khác nhau về hiệu năng nào giữa các truy vấn ràng buộc phép nối bảng trong mệnh đề FROM, sử dụng cú pháp ON và các truy vấn với ràng buộc
trong mệnh đề WHERE. Quyết định về việc sử dụng sự ràng buộc nào là của người tạo ra truy vấn. Một số người thích cú pháp ràng buộc của mệnh đề FROM do nó tách biệt rõ ràng các sự ràng buộc được dùng để nối các bảng khỏi các ràng buộc được dùng để giới hạn các tập kết quả. Những người khác thích liệt kê tất cả các sự ràng buộc trong mệnh đề WHERE. Tuy nhiên, như các ví dụ đã cho thấy, hãy thực hiện nối các bảng qua mối quan hệ khóa chính/ khóa ngoài. Nói chung đây là cách hiệu quả nhất để nối các bảng. Để biết thêm chi tiết có thể xem trong SQL Reference Guide . Tự kết nối Các bảng đang được nối trong một truy vấn không nhất thiết phải khác biệt nhau; bạn có thể nối một bảng bất kỳ tới chính nó miễn là bạn đưa ra cho mỗi tham chiếu bảng một tên khác nhau. Tự kết nối có ích để tìm ra các mối quan hệ giữa các cột dữ liệu khác nhau trong cùng bảng. Câu hỏi Các sản phẩm nào trong bảng Product có cùng tên nhưng khác kiểu đóng gói ? Ví dụ truy vấn SELECT a.prod_name AS products, a.pkg_type FROM aroma.product a, aroma.product b WHERE a.prod_name = b.prod_name AND a.pkg_type b.pkg_type ORDER BY products, a.pkg_type; Kết quả
Product Pkg_Type Aroma Roma No pkg Aroma Roma One-pound bag Assam Gold Blend No pkg Assam Gold Blend Qtr-pound bag Assam Grade A No pkg Assam Grade A Qtr-pound bag Breakfast Blend No pkg Breakfast Blend Qtr-pound bag Cafe Au Lait No pkg Cafe Au Lait One-pound bag Colombiano No pkg Colombiano One-pound bag Darjeeling Number 1 No pkg
Darjeeling Number 1 Qtr-pound bag Darjeeling Special No pkg Darjeeling Special Qtr-pound bag Demitasse Ms No pkg Demitasse Ms One-pound bag Earl Grey No pkg Earl Grey Qtr-pound bag English Breakfast No pkg English Breakfast Qtr-pound bag Expresso XO No pkg Expresso XO One-pound bag Gold Tips No pkg Gold Tips Qtr-pound bag Irish Breakfast No pkg