Session 03

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

Truy vấn năng cao

33

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. 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.

34

Thiết kế CSDL và thực thi với SQL Server

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

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 dòng Nhiều dòng

Một cột Nhiều cột Sử dụng =, >, < và các toán Sử dụng EXISTS tử so sánh khác 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.

Truy vấn năng cao

35

Câu lệnh và kết quả sẽ được hiển thị ở trong hình 3.3.

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.

36

Thiết kế CSDL và thực thi với SQL Server

Hình 3.4: Correlated subquery 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 Account No S001 S002 S003 S004

Tài khoản hiện thời Table

Name James Rita Mary Valentina

Account No C001 C002

Name Michael Robin

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 ---------------S001 S002 S003 S004 C001 C002 Truy vấn năng cao

Name -------James Rita Mary Valentina Michal Robin 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’

Truy vấn năng cao

39

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’.

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 <Expression1>, <Expression2> 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 ColumnA 1 2 3

Table 2 ColumnB AAA BBB CCC

ColumnB 4 5 1 2

ColumnA BBB DDD EEE 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

46

Thiết kế CSDL và thực thi với SQL Server

c. UNION 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

---

Related Documents

Session 03

October 2019 14

Session 03

November 2019 10

03-09-09 Agenda Session

December 2019 13

03-23-09 Agenda Session

April 2020 12

Session

July 2020 19

Session

October 2019 57

---

More Documents from ""

May 2020 47

Really Like You By Fei Lun Hai

April 2020 48

Sd_eco_sustainability-10-01765.pdf

November 2019 64

07-2016- Tt-bldtbxh.signed.pdf

November 2019 55

Aya Kito/ 1 Litre Of Tears

December 2019 53

Cv09sav

May 2020 37

Copyright © 2024 PDFCOKE.