Bài 3 Truy vấn (query) nâng cao

Chia sẻ: Hoang Minh Minh | Ngày: | Loại File: PDF | Số trang:16

Thêm vào BST

Báo xấu

390
lượt xem 45
download

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

Giới thiệu Trong bài trước, chúng ta đã học các khái niệm thiết kế CSDL. Chúng ta cũng đã nói về E-R diagrams and chuẩn hóa CSDL. Chúng ta cũng đã học về một vài dạng chuẩn và cách áp dụng trên các bài tập cụ thể. Trong bài này, chúng ta sẽ làm quen với khái niệm subqueries và cách viết các câu lệnh T-SQL để thực thi các subqueries.

Chủ đề:

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

Lưu

Nội dung Text: Bài 3 Truy vấn (query) nâng cao

Bài 3 Truy vấn (query) nâng cao Mục tiêu bài học: Kết thúc bài này bạn có thể –  Hiểu và thực thi được các subquery  Nắm được cách sử dụng mệnh đề EXISTS  Nắm được cách thực hiện các truy vấn với các mênh đề khác nhau  Sử dụng mệnh đề DISTINCT để lọc bớt kêt quả các truy vấn  Nắm được cách sử dụng mệnh đề COMPUTE BY  Chuyển dữ liệu giữa các tables sử dụng mệnh đề SELECT INTO Giới thiệu Trong bài trước, chúng ta đã học các khái niệm thiết kế CSDL. Chúng ta cũng đã nói về E-R diagrams and chuẩn hóa CSDL. Chúng ta cũng đã học về một vài dạng chuẩn và cách áp dụng trên các bài tập cụ thể. Trong bài này, chúng ta sẽ làm quen với khái niệm subqueries và cách viết các câu lệnh T-SQL để thực thi các subqueries. Chúng ta sẽ khảo sát cách sử dụng mệnh đề EXISTS, làm sao để thực thi queries trên tập kết quả và cách lọc kết quả của các query để trả về các dòng không trùng nhau bằng cách sử dụng mệnh đề DISTINCT. Chúng ta cũng sẽ thảo luận về cách sử dụng các mệnh đề COMPUTE BY và SELECT INTO cùng với các câu lệnh T-SQL. Chúng ta cũng đã học về câu lệnh SELECT cùng với các mệnh đề trên ở các bài trước, trong bài này có thể chúng ta sẽ nhắc lại một số khái niệm, và nó sẽ giúp chúng ta củng cố lại các kiến thức trong các bài trước. 3.1 Thực hiện truy vấn trên nhiều bảng Đôi khi, chúng ta thấy cần thiết phải lấy và kết hợp dữ liệu từ nhiều bảng. Trong bài dưới đây, chúng ta sẽ nói về cách kết hợp thông tin từ nhiều bảng. Có ba cách để kết hợp dữ liệu từ nhiều bảng:  Unions – Kết hợp các dòng của nhiều bảng.  Subqueries – là các query được gói bên trong các query khác.  Joins – Kết hợp các cột từ nhiều bảng. Có khá nhiều mệnh đề có thể được sử dụng với SELECT để trả lại các kết quả khác nhau. Từ khóa DISTINCT khử các dòng trùng nhau trong kết quả của một câu lệnh SELECT. Nếu DISTINCT không được chỉ ra, tất cả các dòng thỏa mãn câu lệng SELECT được trả về, bao gồm cả các dòng trùng nhau. Khi một subquery có từ EXISTS, thì nó có chức năng giống như là kiểm tra sự tồn tại. Từ khóa EXISTS được sử dụng để kiểm tra sự tồn tại của những dòng được trả về bởi subquery. Câu lệng subquery đó sẽ trả lại dữ liệu thực sự mà thay vào đó nó trả về giá trị True hoặc False. Mệng đề COMPUTE and COMPUTE BY có thể được sử dụng để sinh ra các kết quả tổng hợp cho một tập hợp các dòng. COMPUTE trả về các dòng bình thường và thêm một dòng chữa thông tin tổng hợp. COMPUTE BY được sử dụng để sinh ra các giá trị tổng hợp dựa trên từng nhóm con. Truy vấn năng cao 33
Trong phần này chúng ta sẽ học về sub-queries và unions. 3.2 Sub-Query Tính năng subquery của SQL cho phép chúng ta sử dụng kết quả của một query như là một phần của query khác. Một subquery được viết bằng cách đặt một query vào vị trí xác định bên trong một query khác. C. J. Date chỉ ra khả năng sử dụng một query bên trong một query là đương nhiên đối với từ “structured” bên trong câu “Structured Query Language”. Tính năng subquery đóng một vai trò quan trọng trong SQL vì ba điều sau: 1. Một câu lệng SQL sử dụng subquery là cách tự nhiên nhất để diễn tả một query, bởi vì nó gần như tương đương với cấu trúc truy vấn trong tiêng anh. 2. Subqueries giúp dễ viết câu lệnh SELECT hơn, bởi vì nó cho phép chúng ta “tách một query thành nhiều phần” và sau đó “kết hợp lại với nhau”. 3. Có nhiều queries không thể diễn đạt đươc trong SQL nếu không sử dụng subquery. Khi một câu lệng SELECT được sử dụng để trả về các bản ghi nó sẽ được sử dụng bởi câu lệng SELECT khác, Câu lệnh bao ở ngoài gọi là parent query và câu lệnh ở trong gọi là subquery. Nói cách khác, trong một Subquery sẽ có một câu lệnh SELECT được lồng bên trong một câu lệnh SELECT khác. Câu lệnh SELECT bên trong sẽ trả về cột được sử dụng bởi câu lệnh SELECT bên ngoài. Câu lệnh SELECT bên trong nhất sẽ được thực hiện trước. Đây là cách để thực hiện được từng bước các thao tác mà trong trường hơp ngược lại khó thực hiện với hai query riêng biệt. Có nhiều câu lệng SQL chứa các subquery cũng giống như việc được tạo bởi joins. Ví dụ dưới đây chỉ ra các thi hành các sub-queries và cũng giới thiệu cách sử dụng nhiều mệnh đề cùng với sub-queries. Ví dụ dưới đây tìm những tiêu dề mà tác giả của nó kiếm được thù lao tối thiểu là 100. Hình 3.1: Ví dụ về một Subquery đơn giản 3.2.1 Những từ khóa sử dụng trong Sub-query 34 Thiết kế CSDL và thực thi với SQL Server
Khi sử dụng subqueries cần ghi nhớ các điểm sau:  Chúng ta có thể sử dụng một subquery để thay thế cho một giá trị ở trong mệnh đề SELECT,  Khi sử dụng các toán tử so sánh với sub-query, có một số giới hạn với việc trả lại số lượng dòng và cột của sub-query. Các giới hạn được tổng kết trong bảng 3.1. Một cột Nhiều cột Một dòng Sử dụng EXISTS Sử dụng =, >, < và các toán tử so sánh khác Nhiều dòng Sử dụng ANY, ALL, IN và Sử dụng EXISTS EXISTS Bảng 3.1 : Các từ khóa sử dụng trong Sub-query Bằng cách sử dụng mệnh đề EXISTS trong sub-query chúng ta có thể điều khiển kiểm tra sự trả về dòng trong sub-query. Biểu thức EXISTS (subquery) là ‘true’ nếu subquery trả về ít nhất một dòng, và ‘false’ nếu nố không trả về dòng. Ví dụ, để hiển thị thông tin về những nhân viên mà có ít nhất một thông tin của nhân viên khác về họ, chúng ta sẽ viết lệnh như hình 3.2: Hình 3.2: Sử dụng mệnh đề EXISTS 3.2.2 Các kiểu của Sub-queries Có hai kiểu của subquery: Nested queries và Correlated queries. Chúng ta có thể có một subquery mà gọi subquery khác. Khi đó nó được gọi là một nested subquery. Ví dụ, chúng ta có thể viết một nested subquery để tìm giá chi tiết của các đơn đặt hàng và bao gồm các sản phẩm được cung cấp tử thành phố London. Câu lệnh và kết quả sẽ được hiển thị ở trong hình 3.3. Truy vấn năng cao 35
Hình 3.3: Nested subqueries Trong những query có chứa các subquery, các subquery sẽ được thực hiện chỉ một lần để trả lại kết quả sau đó parent query sẽ sử dụng kết quả này để thực hiện. Bởi vì hầu hết các các subquery không tham chiếu tới parent query, cho nên giá trị trong chỉ là các hằng số. Dẫu sao, nếu subquery mà tham chiếu đến parent query, thì subquery cần được thực hiện lại tương ứng với mỗi lần lặp trong parent query, đấy là vì biểu thức điều kiện trong subquery phụ thuộc vào giá trị của từng bàn ghi trong parent query. Khi một subquery lấy các tham số từ parent query, nó đựoc hiểu là Correlated subquery. Giả sử rằng chúng ta muốn tìm những tên thành phố mà ở đó các sản phẩm được cung cấp trực tiếp từ các nhà cung cấp. Câu lệnh sẽ liên quan đến hai bảng: Supplier and Product. Câu lệnh và kêt quả được hiển thị trong hình 3.4. Hình 3.4: Correlated subquery 36 Thiết kế CSDL và thực thi với SQL Server
3.3 UNION Unions được sử dụng để kết hợp các dòng từ nhiều bảng. Toán tử UNION kết hợp kết quả của hai hoặc nhiều câu lệnh SELECT lại thành một tập kết quả. Mỗi câu lệnh SELECT phải có cùng cấu trúc: kiểu dữ liệu cột tương thích và cùng số lượng cột. Tên cột có thể khác nhau giữa các câu lệnh SELECT. Cú pháp: Select statement UNION [ALL] Select statement Nếu chúng ta gộp cả mệnh đề ALL, thì tất cả các dòng sẽ được đưa vào tập kết quả kể cả những dòng trùng nhau. Ví dụ, trong một ứng dụng ngân hàng ta xem xét hai bảng: Saving Account bảng này chứa thông tin của những khách hàng có tài khoản tiết kiệm và Current Account bảng này chứa thông tin của những khách hàng có tài khoản hiện thời. Giả sử chúng ta muốn xem Account_No và Name của tất cả các khách hàng của nhà băng. Để thực hiện, cần phải lấy tất cả các bản ghi của cả hai bảng trên. Giả sử những tài khoản tiết kiệm có Account No bắt đầu bởi ‘S’ và các tài khoản hiện thời có Account No bắt đầu bởi ‘C’. Cả hai bảng và dữ liệu được liệt kê trong bảng 3.2. Tài khoản tiết kiệm Table Tài khoản hiện thời Table Account No Name Account No Name S001 James C001 Michael S002 Rita C002 Robin S003 Mary S004 Valentina Bảng 3.2 Câu lệng sau sẽ kết hợp thông tin của hai bảng. SELECT Account_No, Name FROM Saving_Accounts UNION SELECT Account_No, Name FROM Current_Accounts Câu lệnh trên sẽ hiển thị kết quả như sau: Account_No Name ---------------- -------- S001 James S002 Rita S003 Mary S004 Valentina C001 Michal C002 Robin Truy vấn năng cao 37
Những tên cột trong tập kết quả sẽ giống với tên cột trong câu lệnh SELECT đầu tiên còn tên cột trong các câu lệnh SELECT khác sẽ bị bỏ qua. Mặc định toán tử UNION sẽ loại bỏ các dòng trùng nhau trong tập kết quả. Nhưng nếu chúng ta sử dụng kết hợp thêm mệnh đề ALL thì tất cả các dòng sẽ được trả về trong tập kết quả. 3.4 Những mệnh đề của câu lệnh SELECT Câu lệnh SELECT có thể được sử dụng cùng với nhiều mệnh đề. Chúng sẽ được diễn tả trong bài này. 3.4.1 Chọn tất cả các cột Dấu hoa thị (*) được sử dụng để lầy về tất cả các cột trong bảng. Cú pháp: SELECT * FROM Ví dụ, câu lệng SQL sau sẽ trả về tất cả các dòng và các cột từ bảng Authors của CSDL Pubs. Hình 3.5: Chọn tất cả các cột với SELECT 3.4.2 Chọn một số cột nhất định Để chọn một vài cột từ một bảng, liệt kê tên chúng sau từ khóa SELECT. Cú pháp: SELECT , … From Ví dụ, để hiển thị tên chi tiết của các tác giả, sử dụng câu lệnh SELECT sau: SELECT LName,fname FROM authors Kết quả của query trên được mô tả trong hình 3.6. 38 Thiết kế CSDL và thực thi với SQL Server
Hình 3.6: Chọn một vài cột nhất định Mệnh đề WHERE trong một query hoạt động giống như một bộ lọc để lọc lấy những dòng phù hợp với điều kiện chỉ ra. Cbỉ những dòng phù hợp với điều kiện mói đựoc hiển thị trong kết quả. Cú pháp: SELECT FROM WHERE Ví dụ đơn giản dứoi đây sử dụng mệnh đề WHERE để trả về tât cả những tác giả đến từ thành phố Salt Lake: SELECT * FROM authors WHERE city='Salt Lake city' Kết quả của câu lệnh này được hiển thị trong hình 3.7. Hình 3.7: Sử dụng mệng đề WHERE cùng với SELECT 3.4.3 Các toán tử Logíc Các toán tử logíc được cung cấp trong SQL là AND, OR, và NOT. Toán tử AND và OR đựoc sử dụng để kết nối các điều kiện trong mệnh đề WHERE, tuy nhiên toán tử NOT phủ định giá trị của điều kiện. Xem xét ví dụ sau: Select * from titles where NOT type = ‘business’ Câu lệnh này sẽ liệt kê chi tiết của những quyển sách mà không phải là loại ‘business’. Truy vấn năng cao 39
Hình 3.8: Sử dụng toán tử NOT Toán tử AND kết hợp hai điều kiện và chỉ trả về TRUE khi cả hai điều kiện là true. Vi dụ, nếu chúng ta muốn tìm những quyển sách loại business và có tiền nhuận bút nhiều hơn 10, câu lệnh được chỉ ra trong hình 3.9. Hình 3.9: Sử dụng toán tử AND Toán tử OR cũng kết nối hai điều kiện, nhưng nó trả về TRUE khi một trong hai điều kiện là true. Ví dụ, để hiển thị tât cả các tiêu đề mà thuộc loại sách business hoặc psychology chúng ta có thể viết câu lệnh như hình 3.10. 40 Thiết kế CSDL và thực thi với SQL Server
Hình 3.10: Sử dụng toán tử OR Khi có nhiều hơn một toán tử logíc được sử dụng trong một câu lệnh, NOT được thực hiện đầu tiên, sau đó đến AND và cuối cùng là OR. 3.5 DISTINCT và COMPUTE BY Bài này sẽ mô tả mệnh đề DISTINCT và COMPUTE BY có thể sử dụng cùng với các query và sub-queries. 3.5.1 DISTINCT Từ khóa DISTINCT khử các dòng trùng nhau từ tập kết quả của một câu lệnh SELECT. Nếu không dùng DISTINCT, tất cả các dòng sẽ dược trả về, kể cả những dòng trùng nhau. Ví dụ, nếu chúng ta chọn cột type trong bảng Titles mà không dùng từ khóa DISTINCT, nó sẽ trả lại một danh sách dài các tên kiểu. Nhưng sau khi sử dụng từ khóa DISTINCT trong query, SQL Server sẽ trả về tất cả các kiểu với mỗi thứ chỉ chiếm một dòng. Điều này dựoc chỉ ra trong hình 3.11 và hình 3.12. Mỗi kiểu được trả về nhiều lần Hình 3.11: Không sử dụng từ khóa DISTINCT Truy vấn năng cao 41
Nếu chúng ta sử dụng từ khóa DISTINCT cùng với query tren danh sách kết quả sẽ chỉ còn lại những giá trị duy nhất của cột type. Mỗi kiểu được trả về chỉ một lần Hình 3.12: Sử dụng từ khóa DISTINCT 3.5.2 Mệnh đề COMPUTE và COMPUTE BY Mệnh đề COMPUTE sinh thêm một dòng các kết quả tổng kết với một tập kết quả. Từ khóa COMPUTE có thể được sử dụng khi không dùng mệnh đề GROUP BY. Nó được sử dụng để sinh ra kết quả tổng hợp của các hàm như MAX, MIN, SUM, COUNT, và AVG. Kết quả tổng kết sẽ xuất hiện ở một dòng thêm mới. Chi tiết dòng và dòng tổng kết có thể đựoc xem trong cùng một kết quả. CÚ PHÁP: COMPUTE , Ví dụ, nếu một người muốn xem số lượng tất cả các đơn đặt hàng và số lượng lớn nhất, câu lệnh SELECT sẽ như sau: SELECT Stor_id, Qty FROM Sales COMPUTE MAX(Qty) Kết quả của câu lệnh được hiển thị trong hình 3.13. Figure 3.13: Using COMPUTE clause 42 Thiết kế CSDL và thực thi với SQL Server
COMPUTE trả về tổng kết của những hàm aggregate nguợc lại COMPUTE BY trả về tổng kết của tùng nhóm con trong tập kết quả. COMPUTE BY phải bao gồm mệnh đề ORDER BY. Nếu trong ví dụ ở trên thay vì COMPUTE, mệnh đề COMPUTE BY được sử dụng thì kết quả sẽ như hình 3.14. Hình 3.14: Sử dụng mệnh đề COMPUTE BY COMPUTE và COMPUTE BY có thể sử dụng khi không có mệnh đề GROUP BY. 3.6 Sử dụng cấu trúc SELECT INTO Trong khi câu lệnh SELECT chỉ để lấy về dữ liệu, nó không giúp chúng ta đưa kết quả của một query vào một bảng khác, Thi cấu trúc SELECT INTO lại giúp chúng ta tạo một bảng mới và đưa dữ liệu vào từ những bảng đã tồn tại. Nó thực thi một query và sau đó tạo ra một bảng mới để lưu kết quả của query đó. 3.6.1 Trường hợp sử dụng SELECT INTO Bất cứ khi nào chúng ta muốn tạo một bảng dựa trên bảng khác và điền dữ liệu cùng với kết quả lấy từ bảng đó chúng ta sử dụng cấu trúc SELECT INTO. SELECT INTO cũng có thể được sử dụng để kết hợp dữ liệu từ nhiều bảng khác nhau vào một bảng. Ví dụ dưới đây chỉ ra cách cấu trúc SELECT INTO làm việc. Ví dụ này cũng tao ra một bảng mới tên là publishers2 dựa trên bảng publishers. Bảng mới sẽ có dữ liệu được lấy từ bảng publishers. Truy vấn năng cao 43
Hình 3.15: Sử dụng cấu trúc SELECT INTO 44 Thiết kế CSDL và thực thi với SQL Server
Tóm tắt  Một query là một câu hỏi được viết trong ngôn ngữ hỏi đáp có cấu trúc (Structured Query Language – SQL) để lấy dữ liệu từ các bảng  Câu lệnh SELECT được sử dụng đẻ viết các query.  Có ba cách để kết hợp dữ liệu từ nhiều bảng: o Unions - Kết hợp các dòng của nhiều bảng. o Subqueries – là các query được gói bên trong các query khác. o Joins - Kết hợp các cột từ nhiều bảng.  Tính năng subquery cho phép chúng ta sử dụng kết quả của một query như một phần của query khác.  Bằng cách sử dụng mệnh đề EXISTS trong một sub-query chúng ta có thể điều khiển kiểm tra sự tồn tại dòng trong một subquery.  Có hai kiểu subquery: Nested queries và Correlated queries  Chúng ta có thể mở rộng subquery để có một subquery có thể gọi được subquery khác. Khi đó ta có một nested subqueries.  Khi một subquery nhận tham số từ parent query. Nó sẽ đựoc hiểu là một Correlated subquery.  Có thể kết hợp các dòng từ nhiều bảng sử dụng toán tử UNION.  Từ khoá DISTINCT khử những dòng trùng nhau từ kết quả của một câu lệnh SELECT. Nếu không sử dụng DISTINCT, tất cả các dòng sẽ đựoc trả về kể cả những dòng trùng nhau.  Mệnh đề COMPUTE sinh thêm một dòng các kết quả tổng kết với một tập kết quả. Từ khóa COMPUTE có thể được sử dụng khi không dùng mệnh đề GROUP BY. Nó được sử dụng để sinh ra kết quả tổng hợp của các hàm như MAX, MIN, SUM, COUNT, và AVG.  COMPUTE trả về tổng kết của những hàm aggregate nguợc lại COMPUTE BY trả về tổng kết của tùng nhóm con trong tập kết quả. COMPUTE BY phải bao gồm mệnh đề ORDER BY.  Cấu trúc SELECT INTO lại giúp chúng ta tạo một bảng mới và đưa dữ liệu vào từ những bảng đã tồn tại. Nó thực thi một query và sau đó tạo ra một bảng mới để lưu kết quả của query đó. Truy vấn năng cao 45
Ôn tập 1. Câu lệnh Select sau đây sẽ trả về gì? Table1 Table 2 ColumnA ColumnB ColumnB ColumnA 1 AAA 4 BBB 2 BBB 5 DDD 3 CCC 1 EEE 2 BBB a. SELECT A, B FROM Table1 UNION SELECT A, B FROM Table2 b. SELECT A, B FROM Table1 UNION SELECT B, A FROM Table2 c. SELECT * FROM Table1 UNION ALL SELECT * FROM Table2 2. Một subquery có thể trả về bao nhiêu dòng(chỉ có một cột) mà không sinh ra lỗi? a. Chỉ một dòng. b. Chỉ một dòng nếu nhiều hơn thì phải sử dụng các toán tử ANY, ALL, EXISTS, hoặc IN c. Không giới hạn d. Không giới hạn, nếu không thi phải sử dụng các toán tử ANY, ALL, EXISTS or IN 3. SELECT là một trong những câu lệnh quan trọng nhất của SQL. Sử dụng SELECT chúng ta có thể: a. Hiển thị một vài hoặc tất cả các cột từ các bảng. b. Xoá dữ liệu từ các bảng c. Cập nhật dữ liệu từ các bảng 4. Có ba cách để kết hợp dữ liệu từ nhiều bảng: a. Unions b. Subqueries c. Joins d. Roles 5. Mệnh đề _______ được sử dụng để kiểm tra sự tồn tại dòng bên trong các subquery. a. UNION b. EXISTS c. DISTINCT d. COMPUTE BY 6. Mệnh đề ______________ sinh thêm một dòng các kết quả tổng kết với một tập kết quả. a. COMPUTE b. EXISTS c. UNION 46 Thiết kế CSDL và thực thi với SQL Server
d. DISTINCT 7. Cấu trúc nào dưới đây cho phép chúng ta tạo và điền dữ liệu vào một bảng mới với dữ liệu đựoc lấy từ các bảng đã có sẵn? a. SELECT INTO b. UNION c. Subquery d. Joins 8. Khi một subquery nhận tham số từ parent query, nố được gọi là a. Correlated subquery b. Nested subquery c. Plain subquery d. Join subquery 9. Kết quả của một câu lệnh SELECT không đựoc đặt tên, bảng tạm đựoc gọi là __________. a. Results table b. Query c. Output 10. Khi chúng ta có một subquery mà gọi subquery khác nó đựoc gọi là _______________ a. nested subqueries b. coorelated subqueries c. joins d. result sets Truy vấn năng cao 47
Bài tập về nhà 1. Viết một câu lệnh T-SQL để lấy về giá chi tiết của những hoá đơn bao gồm những sản phẩm được cung cấp từ thành phố Singapore. 2. Viết một câu lệnh T-SQL để tính tổng của tiền nhuận bút royaltyper từ bảng titleauthor. 48 Thiết kế CSDL và thực thi với SQL Server