Cơ sở dữ liệu - bài 6

Chia sẻ: Chu Văn Thắng Doremon | Ngày: | Loại File: PDF | Số trang:22

Thêm vào BST

Báo xấu

106
lượt xem 12
download

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

Chúng ta có cả thảy 4 câu lệnh điều hành dữ liệu: SELECT, INSERT, UPDATE, và DELETE, trong đó câu lệnh SELECT có cú pháp phức tạp nhất. Để dễ hiểu, trước hết ta đi qua một số ví dụ và tiếp đó ta xét cú pháp ở dạng đầy đủ của nó. 1.1. Các ví dụ về câu lệnh SELECT VD 1.

Chủ đề:

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

Lưu

Nội dung Text: Cơ sở dữ liệu - bài 6

B ài 6 (Nội dung tiếp bài 5) 1. Đ iều hành dữ liệu – câu lệnh SELECT Chúng ta có cả thảy 4 câu lệnh điều hành dữ liệu: SELECT, INSERT, UPDATE, và DELETE, trong đó câu lệnh SELECT có cú pháp phức tạp nhất. Để dễ hiểu, trước hết ta đi qua một số ví dụ và tiếp đó ta xét cú pháp ở dạng đầy đủ của nó. 1.1. Các ví dụ về câu lệnh SELECT VD 1. Lập danh mục Mầu và Tên thành phố đối với các phụ tùng không phải ở Paris và có trọng lượng lớn hơn 10: SELECT color, city FROM part WHERE city 'Paris' AND weight > 10; Kết quả: color city Red London Blue Rome Red London Red London Chú ý các dòng lặp lại. Nếu ta thay câu lệnh trên bằng SELECT DISTINCT color, city FROM part WHERE city 'Paris' AND weight > 10; ta sẽ được kết quả sau: color city Red London Blue Rome Đối với câu lệnh trên, kết quả không được sắp xếp. Muốn có kết quả được sắp xếp, ta thêm mệnh đề ORDER BY: Bai-6.doc *** Trang 1
SELECT DISTINCT color, city FROM part WHERE city 'Paris' AND weight > 10 ORDER BY color; và kết quả là: color city Blue Rome Red London VD 2. Lập danh sách số hiệu phụ tùng và trọng lượng của chúng đo theo gram (1 Pound = 454 gram) SELECT p_id, weight*454 AS weight_gram FROM part cho kết quả: p_id weight_gram P1 5448 P2 7718 P3 7718 P4 6356 P5 5448 P6 8626 Trong câu lệnh trên chú ý phần weight*454 AS weight_gram. Đây là trường hợp chỉ định cột tính toán và đặt tên cho cột đó. VD 3. Lấy toàn bộ thông tin của các nhà cung cấp SELECT * FROM supplier Kết quả: s_id sname status city S1 Smith 20 London S2 Jones 10 Paris S3 Blake 30 Paris S4 Clark 20 London S5 Adams 30 Athens Bai-6.doc *** Trang 2
VD 4. Lấy toàn bộ thông tin các nhà cung cấp và phụ tùng sao cho nơi đặt phụ tùng cũng là nơi đặt trụ sở các nhà cung cấp. SELECT supplier.*, part.p_id, part.pname, part.color, part.weight FROM supplier, part WHERE supplier.city = part.city; Kết quả là: s_id sname status city p_id pname color weight S1 Smith 20 London P1 Nut Red 12 S4 Clark 20 London P1 Nut Red 12 S2 Jones 10 Paris P2 Bolt Green 17 S3 Blake 30 Paris P2 Bolt Green 17 S1 Smith 20 London P4 Screw Red 14 S4 Clark 20 London P4 Screw Red 14 S2 Jones 10 Paris P5 Cam Blue 12 S3 Blake 30 Paris P5 Cam Blue 12 S1 Smith 20 London P6 Cog Red 19 S4 Clark 20 London P6 Cog Red 19 Chú ý rằng hai câu lệnh sau tương đương với câu lệnh trên: SELECT supplier.*, part.p_id, part.pname, part.color, part.weight FROM supplier INNER JOIN part USING (city); SELECT supplier.*, part.p_id, part.pname, part.color, part.weight FROM supplier NATURAL JOIN part; Ghi nhớ: Một cách đơn giản ta có thể nhận thấy rằng sau FROM là tích Đề các, sau WHERE là Restrict và sau SELECT là một phép chiếu (Project). Như vậy, tổ hợp SELECT-FROM-WHERE tương đương với phép chiếu của trích xuất tích Đề các. VD 5. Lập danh mục tên các cặp thành phố sao cho nhà cung cấp thuộc thành phố thứ nhất cung cấp thiết bị đặt ở thành phố thứ hai. SELECT DISTINCT supplier.city AS scity, part.city AS pcity FROM supplier INNER JOIN sp USING (s_id) INNER JOIN part USING (p_id); Bai-6.doc *** Trang 3
Kết quả là: scity pcity London London London Paris London Rome Paris London Paris Paris Chú ý rằng câu lệnh sau đây sẽ không cho kết quả đúng: SELECT DISTINCT supplier.city AS scity, part.city AS pcity FROM supplier NATURAL JOIN sp NATURAL JOIN part ; vì câu lệnh này sẽ sử dụng thêm trường city là trường để thực hiện phép toán JOIN. VD 6. Lập danh sách mã hiệu các cặp nhà cung cấp sao cho cả hai nhà cung cấp ở cùng thành phố. SELECT first.s_id AS first_id, second.s_id AS second_id FROM supplier AS first, supplier AS second WHERE first.city = second.city AND first.s_id < second.s_id; Kết quả là: first_id second_id S1 S4 S2 S3 VD 7. Lấy tổng số các nhà cung cấp SELECT COUNT(*) AS n FROM supplier; Kết quả là: n 5 Chú ý trường hợp này: gọi hàm COUNT. Hàm này là một trong các hàm tính giá trị gộp (aggregate functions): COUNT, SUM, AVG, MAX, MIN. Bai-6.doc *** Trang 4
Nhìn chung, ta có thể đưa thêm mệnh đ ề DISTINCT đ ể loại bỏ các b ản ghi trùng • (trừ MAX và MIN: vì mệnh đề này không có nghĩa đố i với chúng) Hàm COUNT là hàm đặc biệt, không chấp nhận đưa thêm mệnh đ ề DISTINCT • và hàm này tính cả các giá trị NULL Các hàm khác đều loại bỏ NULL trước khi tính • Nếu kết quả là tập hợp rỗ ng, hàm COUNT cho kết qu ả 0, còn các hàm khác cho • kết qu ả NULL. VD 8. Lấy số lượng cao nhất và số lượng thấp nhất của phụ tùng có mã hiệu P2 SELECT MAX(qty) AS max_qty, MIN(qty) AS min_qty FROM sp WHERE p_id = 'P2'; Kết quả là: max_qty min_qty 400 200 VD 9. Lập danh sách phụ tùng gồm số hiệu và tổng số lượng trong kho SELECT p_id, SUM(qty) AS total_qty FROM sp GROUP BY p_id; Kết quả là: p_id total_qty P1 600 P2 1000 P3 400 P4 500 P5 500 P6 100 Lập danh sách các mã phụ tùng có ít nhất 2 nhà cung cấp trở lên VD 10. SELECT p_id FROM sp GROUP BY p_id HAVING COUNT(s_id) > 1; Bai-6.doc *** Trang 5
Kết quả là: p_id P1 P2 P4 P5 1.2. Sơ lược về câu lệ nh SELECT SELECT [DISTINCT | ALL] select_expression,... [FROM table_references [WHERE where_definition] [GROUP BY ..., ...] [HAVING where_definition] [ORDER BY ... ,...] Cấu trúc câu lệnh truy vấn SELECT gồm các mệnh đề chính: • SELECT • FROM • WHERE • GROUP BY ... HAVING • ORDER BY Chúng ta chỉ đ ề cập mộ t số mệnh đề chính, vì câu lệnh SELECT có rất nhiều mệnh đêf. Các mệnh đ ề khác chỉ đ ược nhắc đến khi có các ứng dụng cần thiết. Một cách tổ ng quan chúng ta nhận thấy: mệnh đề SELECT chỉ ra các cột cần lấ y, mệnh đ ề FROM chỉ ra các b ảng cần truy xuất, mệnh đề WHERE chỉ ra các bản ghi nào cần lấy. Chỉ có mệnh đ ề SELECT và FROM là bắt buộc, còn WHERE và các mệnh đề khác có thể không cần chỉ định (xem các ví dụ trên đây). 1.3. Mệnh đề SELECT SELECT [ALL|DISTINCT] select-list select-list là danh sách các cộ t, tách nhau bởi d ấu phẩy. Các chỉ định ALL và DISTINCT không bắt buộ c. DISTINCT chỉ định rằng không có các b ản ghi trùng nhau. Mặc đ ịnh là ALL, nghĩa là lấy tất cả, kể cả các b ản ghi trùng nhau. Ký tự ‘*’ chỉ định tất cả các cột, ví dụ SELECT * FROM sp, hoặc SELECT sp.* FROM sp là lấ y tất cả các cộ t của b ảng sp. Bai-6.doc *** Trang 6
1.4. Mệnh đề FROM Mệnh đề FROM luôn luôn phải theo mệnh đ ề SELECT. Nó chỉ ra danh sách các bảng cần lấy, ví dụ: SELECT * FROM supplier hoặc: SELECT sp.*, city FROM sp, supplier WHERE sp.s_id=s.s_id ■ Tên tương quan Trong phần tên b ảng ta có thể thay tên mới, ví dụ: SELECT s.name FROM supplier s Ho ặc SELECT s.name FROM supplier AS s Tên mới đ ược gọi là tên tương quan. 1.5. Mệnh đề WHERE Sau từ khóa WHERE là một biểu thức logic. color = 'Red' Nhìn chung, biểu thức logic có các toán hạng là các cộ t của b ảng liên quan từ phần FROM và ngoài ra các toán hạng cũ ng có thể là các giá trị cụ thể. Các phép toán so sánh: • > • < >= -- lớn hơ n hoặc bằng • = value-2 AND value-1
• IN Toán tử IN kiểm tra xem giá trị chỉ ra có thuộc một danh sách hay không: value-1 [NOT] IN ( value-2 [, value-3] ... ) Điều này cũng tương đương với việc kiểm tra xem value-1 có bằng value-2 ho ặc bằng value-3, ... hay không, nó tương đương với: value-1 = value-2 [ OR value-1 = value-3 ] ... • LIKE Toán tử LIKE so sánh chu ỗi ký tự này với chuỗ i ký tự khác, trong đó có một số ký tự thay thế. Các ký tự thay thế là '%' và ký tự gạch d ưới '_'. Ký tự gạch d ưới thay thế mộ t ký tự b ất kỳ và ký tự p hần trăm thay thế một số các ký tự bất kỳ. VD: Giá trị Chuỗ i Kết quả 'abc' '_b_' True 'ab' '_b_' False 'abc' '%b%' True 'ab' '%b%' True 'abc' 'a_' False 'ab' 'a_' True 'abc' 'a%_' True 'ab' 'a%_' True Dạng thức so sánh LIKE: value-1 [NOT] LIKE value-2 [ESCAPE value-3] Các giá trị phải là chuỗi ký tự. value-2 là mẫu so sánh, value-1 là giá trị. Tùy chọn ESCAPE cho phép sử dụng ký tự kèm để biến các ký tự '%' và '_' như các ký tự bình thường. Bai-6.doc *** Trang 8
Ví dụ, tìm chuỗi x kết thúc bằng dấu phần trăm: x LIKE '%/%' ESCAPE '/' Ví dụ phức tạp hơn: y LIKE '/%//%' ESCAPE '/' là tìm chuỗi y bắt đầu bằng '%/'. Như vậy, ký tự sau ESCAPE nếu gặp lại chính bản thân nó thì cặp ký tự đó biến thành một ký tự bình thường. Toán tử NOT đ ương nhiên làm cho kết quả ngược lại: z NOT LIKE 'abc%' tương đương với: NOT (z LIKE 'abc%') Toán tử IS NULL • SQL không cho phép so sánh như sau: WHERE qty = NULL Ta phải sử dụ ng toán tử IS NULL: value-1 IS [NOT] NULL Phép tính trên kiểm tra xem value-1 có b ằng NULL hay không. Toán tử NOT đ ương nhiên là cho kết quả ngược lại : value-1 IS NOT NULL tương đương với: NOT (value-1 IS NULL) ■ Các toán tử logic • AND predicate-1 AND predicate-2 AND cho kết quả: True -- nếu cả hai toán hạng là True o False -- nếu mộ t trong hai là False o Bai-6.doc *** Trang 9
NULL -- Nếu một trong hai toán hạng là NULL và toán hạng còn lại là o True hoặc cả hai đ ều NULL Xem bảng giá trị của toán tử AND: Toán hạ ng Toán hạ ng AND T F NULL AND 1 2 T F NULL T True True True F F F F True False False NULL NULL F NULL False False False False True False NULL NULL NULL NULL True NULL NULL False False True NULL NULL False NULL False Toán tử OR • predicate-1 OR predicate-2 OR cho kết qu ả: True -- Nếu một trong hai là True o False -- Nếu cả hai là False o o NULL -- Cho các trường hợp còn lại (Mộ t trong hai toán hạng là False và toán hạng kia NULL ho ặc cả hai đều NULL) Bảng giá trị OR: Toán hạng 1 Toán hạng 2 OR TF NULL OR TT T True True True T TF NULL True False True F NULL T NULL NULL False False False False True True NULL NULL NULL NULL True True NULL False NULL True NULL True False NULL NULL AND có độ ưu tiên cao hơn OR, do đó: Bai-6.doc *** Trang 10
a OR b AND c Tương đương với: a OR (b AND c) Toán tử NOT • NOT predicate Bảng giá trị NOT: Toán hạ ng NOT NOT F True False T T False True F NULL NULL NULL NULL 1.6. Mệnh đề ORDER BY Mệnh đề ORDER BY là mệnh đề tùy chọn (không bắt buộ c phải có) như ng nếu có mặt thì nó phải là mệnh đ ề cuối cùng của câu lệnh SELECT. Mệnh đ ề này sắp xếp các bản ghi theo thứ tự nhất định. Khi không có mệnh đề ORDER BY thì kết quả sẽ không tuân theo mộ t thứ tự nào cả. Dạng thức của mệnh đề này như sau: ORDER BY column-1 [ASC|DESC] [ column-2 [ASC|DESC] ] ... trong đó column-1, column-2, ... là tên các cộ t cần sắp xếp và nó phải thuộ c vào trong phần củ a mệnh đ ề SELECT. Tùy chọn ASC chỉ đ ịnh sắp xếp tăng d ần và tùy chọ n DESC chỉ đ ịnh sắp xếp giảm dần. Mặc định là ASC. Thứ tự ưu tiên từ trái sang phải. Nghĩa là sắp xếp theo cộ t liệt kê đ ầu tiên, rồi đến cộ t tiếp theo, ... Các cột có thể liệt kê theo tên hoặc theo số thứ tự (lấ y từ mệnh đ ề SELECT) và b ắt đầu từ số 1. Ví dụ: SELECT * FROM sp ORDER BY 3 DESC; s_id p_id qty S1 P3 400 S2 P2 400 S4 P5 400 Bai-6.doc *** Trang 11
S1 P1 300 S2 P1 300 S4 P4 300 S1 P2 200 S1 P4 200 S3 P2 200 S4 P2 200 S1 P5 100 S1 P6 1 SELECT sname, city FROM supplier ORDER BY sname; sname city Adams Athens Blake Paris Clark London Jones Paris Smith London SELECT * FROM sp ORDER BY qty DESC, s_id; s_id p_id qty S1 P3 400 S2 P2 400 S4 P5 400 S1 P1 300 S2 P1 300 S4 P4 300 S1 P2 200 S1 P4 200 S3 P2 200 S4 P2 200 S1 P5 100 S1 P6 100 Bai-6.doc *** Trang 12
1.7. Các biểu thức Trong các biểu thức, các toán hạng có thể là các tên cộ t, các giá trị cụ thể, các hàm, các giá trị hệ thống, mộ t số câu lệnh đặc biệt. Ta lần lượt xét các trường hợp này - trừ trường hợp các cột vì ta đã làm quen. 1.8. Biểu diễn giá trị Biểu diên giá trị tùy thuộc vào kiểu d ữ liệu: Chuỗ i ký tự (String) -- Chuỗ i ký tự p hải nằm giữ a các dấu nháy đ ơn ('). Nếu • trong chu ỗi ký tự đó có dấu nháy đơn thì dấu nháy đơn phải lặp ('') . Số (Numeric) -- Biểu diễn theo cách thông thườ ng: • [ddd][[.]ddd][E[+|-]ddd] Thời gian (Datetime) -- Thời gian biểu diễn theo dạng thức sau: • o Date -- 'yyyy-mm-dd' o Time -- 'hh:mm:ss[.fff]' o Timestamp -- 'yyyy-mm-dd hh:mm:ss[.fff]' 1.9. Các hàm SQL • SUBSTRING(exp-1 FROM exp-2 [FOR exp-3]) Lấ y dãy ký tự từ exp-1, b ắt đầu bằng exp-2 và có độ dài là exp-3, đếm bắt đ ầu từ 1. Nếu không chỉ định exp-3, thì toàn bộ chuỗ i bắt đầu từ exp-2 đến hết sẽ được lấy. SELECT SUBSTRING('abcdefg', 2, 3); cho kết qu ả là bcd • UPPER(exp-1) Đổ i thành chư hoa. • LOWER(exp-1) Đổ i thành chữ t hường. • TRIM([LEADING|TRAILING|BOTH] [FROM] exp-1) TRIM([LEADING|TRAILING|BOTH] exp-2 FROM exp-1) Cắt các ký tự trắng bên trái, bên phải hoặc cả bên trái lẫn bên phải. Mặc đ ịnh là BOTH (nghĩa là cắt cả hai bên). • POSITION(exp-1 IN exp-2) Bai-6.doc *** Trang 13
Tìm exp-1, trong exp-2. Bắt đầu đếm từ 1. Nếu không tìm thấy, hàm cho giá trị 0 . • CHAR_LENGTH(exp-1) CHARACTER_LENGTH(exp-1) Cho độ dài chuỗ i ký tự exp-1. • OCTET_LENGTH(exp-1) Cho kết quả là số các byte trong chuỗi exp-1. • EXTRACT(sub-field FROM exp-1) Cho giá trị số lấy từ dữ liệu thời gian exp-1. Sub-field là một trong các từ khóa: YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE. Ví dụ: SELECT EXTRACT(SECOND FROM '2005-03-10 11:37:25'); cho kết qu ả là 25. 1.10. Các giá trị hệ thống USER – Tên người dùng đ ang đăng nhập • CURRENT_USER – giố ng như USER. • SESSION_USER –Tên gười dùng theo phiên làm việc hiện thờ i. • SYSTEM_USER – Tên người dùng hệ thống. • CURRENT_DATE – Ngày hiện tại. • CURRENT_TIME – Thời gian hiện tại. • CURRENT_TIMESTAMP – Timestamp hiện tại. • 1.11. Một số câu lệnh kết cấ u biểu thức SQL đặc biệt • CAST(exp-1 AS data-type) Chuyển đ ổi biểu thức exp-1, về kiểu date-type. • COALESCE(exp-1, exp-2 [, exp-3] ...) Cho exp-1 nếu không phải là null, nếu không cho exp-2 nếu nó không phải là null, .... Trả lại giá trị null nếu tất cả đều null. • CASE exp-1 { WHEN exp-2 THEN exp-3 } ... [ELSE exp-4] END CASE { WHEN predicate-1 THEN exp-3 } ... [ELSE exp-4] END Bai-6.doc *** Trang 14
Trường hợp 1 so sánh exp-1 với exp-2 với mỗ i mệnh đề WHEN. Nếu bằng nhau, thì hàm sẽ cho kết quả là exp-3 từ mệnh đề THEN. Nếu không tìm thấy thì hàm sẽ trả kết qu ả là exp-4 từ mệnh đề ELSE hoặc null nếu không chỉ đ ịnh mệnh đề ELSE. Trường hợp 2, hàm đ ánh giá predicate-1 trong mỗi mệnh đ ề WHEN. Nếu giá trị p redicate tương ứng là true, thì hàm sẽ trả giá trị exp-3 tương ứng với mệnh đề THEN. Nếu không có predicate nào cho giá trị true, thì hàm sẽ trả kết quả exp-4 từ mệnh đề ELSE hoặc trả giá trị NULL nếu mệnh đề ELSE không đ ược chỉ đ ịnh. 1.12. Toán tử trong biểu thức Toán tử chuỗi (String Operators) • 'ab' || 'cd' ==> 'abcd' Chú ý: MySQL dùng hàm CONCAT thay thế cho toán tử chuỗi. Toán tử số (Numeric operators) • o+ o- o* o/ Dùng cho kiểu dữ liệu số: TINYINT, SMALLINT, INT, BIGINT o NUMERIC, DECIMAL o FLOAT, DOUBLE, REAL o Dấu + và – có thể là dấu củ a mộ t số dương, âm. Các phép toán đó cũ ng có thể áp dụng cho kiểu dữ liệu là thờ i gian, nhưng với vài điểm cần lưu ý. Qui tắc cơ b ản là: Thời gian khi cộng trừ mộ t kho ảng nào đó, sẽ ra kết quả là thời gian. o Thời gian trừ thời gian sẽ ra kết quả là khoảng thời gian o (datetime-1 - datetime-2) interval-qualifier Chúng ta chỉ đề cập đến interval-qualifier đối với các ứng dụng cụ thể. 1.13. Liên kết bảng (Joining Tables) Trong mệnh đề FROM, người ta cho phép sử dụng danh sách nhiều b ảng. Khi bản ghi của b ảng này có mối tương quan với bản ghi của bảng kia thì ta có thể dùng toán tử liên kết (JOIN). Bai-6.doc *** Trang 15
Ví dụ: SELECT * FROM sp, part; cho kết qu ả 8 cột, 72 bản ghi (SV thử câu lệnh này). Kết quả đó chính là tích Đề các. Kết qu ả này ít có ý nghĩa trong thực tế. Câu lệnh sau có nghĩa hơn:: (SV hãy giải thích ý nghiã của câu lệnh sau) SELECT * FROM sp, part WHERE sp.p_id = part.p_id Cho kết quả: s_id sp.p_id qty part.p_id pname color weight city S1 P1 300 P1 Nut Red 12 London S1 P2 200 P2 Bolt Green 17 Paris S1 P3 400 P3 Screw Blue 17 Rome S1 P4 200 P4 Screw Red 14 London S1 P5 100 P5 Cam Blue 12 Paris S1 P6 100 P6 Cog Red 19 London S2 P1 300 P1 Nut Red 12 London S2 P2 400 P2 Bolt Green 17 Paris S3 P2 200 P2 Bolt Green 17 Paris S4 P2 200 P2 Bolt Green 17 Paris S4 P4 300 P4 Screw Red 14 London S4 P5 400 P5 Cam Blue 12 Paris Ta thấy kết quả là hai cộ t p_id đ ều giống nhau. Đây là kiểu liên kết b ảng dựa trên cột p_id và mệnh đ ề WHERE thể hiện điều đó. Phương thức liên kết này có tên gọi là inner equi-join. equi có nghĩa là sử dụng so sánh b ằng (=) để liên kết các cộ t với nhau. Các phương thức liên kết khác sử dụng các toán tử so sánh mộ t cách tổng quát (lớn hơn (>), nhỏ hơn (
Kết quả: sname qty color Smith 300 Red Smith 200 Green Smith 400 Blue Smith 200 Red Smith 100 Blue Smith 100 Red Jones 300 Red Jones 400 Green Blake 200 Green Clark 200 Green Clark 300 Red Clark 400 Blue 1.14. Liên kết ngoài (Outer Joins) Liên kết ngoài có cú pháp như sau: table-1 { LEFT | RIGHT | FULL } OUTER JOIN table-2 ON predicate-1 Trong đ ó predicate-1 là biểu thức điều kiện của OUTER JOIN. LEFT – các b ản ghi củ a bảng bên trái (table-1) được liệt kê • RIGHT – các b ản ghi củ a bảng bên phải (table-2) đ ược liệt kê • FULL – các b ản ghi củ a cả hai bảng (table-1 và table-2) đều được liệt kê • SELECT part.p_id, color, s_id, qty FROM part LEFT OUTER JOIN sp ON part.p_id = sp.p_id; (SV kiểm tra kết quả câu lệnh trên). 1.15. Liên kết với chính nó (Self Joins) Một hệ qu ả của các liên kết là ta có thể cho liên kết một b ảng vớ i chính nó. Ví dụ , liệt kê các mã phụ tùng có ít nhất hai nhà cung cấp cho phụ tùng đó. SELECT DISTINCT a.p_id FROM sp a, sp b WHERE a.p_id = b.p_id AND a.s_id b.s_id; Bai-6.doc *** Trang 17
p_id P1 P2 P4 P5 Một lần nữa, chúng ta thấy việc sử dụ ng tên tương quan thu ận lợi như thế nào. Cùng một bảng sp, ta tách ra thành hai bản sao a và b, sau đó liên kết a và b theo các điều kiện như chỉ định. 1.16. Truy vấn nhóm (Grouping Queries) Một trong những chức năng của của câu lệnh SELECT là gộp các bản ghi theo nhóm, và tính năng này có tên là truy vấn nhóm. Các bản ghi đ ược gộp theo nhóm dựa trên một đặc tính chung nào đó. Và kết qu ả của truy vấn nhóm là chỉ đưa ra kết qu ả sau khi nhóm – không đưa ra chi tiết từng b ản ghi thuộ c nhóm. Trong từ ng bản ghi kết qu ả nhóm ta có thể sử dụ ng các hàm gộp (COUNT, AVG, SUM, MAX, MIN) p_id s_id qty Nhóm theo p_id P1 S1 300 Nhóm P1 P1 S2 300 P2 S1 200 P2 S2 400 Nhóm P2 P2 S3 200 P2 S4 200 P3 S1 400 Nhóm P3 P4 S1 200 Nhóm P4 P4 S4 300 P5 S1 100 Nhóm P5 P5 S4 400 P6 S1 100 Nhóm P6 Khi nhóm, giá trị NULL đ ược xử lý một cách đặc biệt. Mỗ i một giá trị NULL lập riêng một nhóm. Đây là điểm khác thường, không giố ng với các giá trị khác. Chú ý: Cách xử lý giá trị NULL như trên là theo chuẩn SQL92 – các HQT CSDL trong thực tế có thể có cách xử lý khác. Chúng ta cần tham khảo các tài liệu hướng d ẫn sử dụ ng của các HQT CSDL đó. Bai-6.doc *** Trang 18
1.17. Mệnh đề GROUP BY Mệnh đề GROUP BY phụ c vụ truy vấn nhóm. Nếu có mệnh đ ề WHERE, thì mệnh đề GROUP BY phải đứng sau mệnh đề WHERE. GROUP BY column-1 [ASC | DESC], [, column-2 [ASC | DESC]] ... Trong đ ó column-1 và column-2 là tên các cột dùng đ ể nhóm. Chú ý rằng column-1 và column-2, ... phải là tên các cộ t. SQL92 không chấp nhận biểu thức. Ví dụ, SELECT p_id, SUM(qty) FROM sp GROUP BY p_id; p_id SUM(qty) P1 600 P2 1000 P3 400 P4 500 P5 500 P6 100 1.18. Các hàm gộp Khi truy vấn nhóm, người ta thường sử dụng các hàm gộp p_id s_id qty Nhóm theo p_id SUM(qty) P1 S1 300 Nhóm P1 600 P1 S2 300 P2 S1 200 P2 S2 400 Nhóm P2 1000 P2 S3 200 P2 S4 200 P3 S1 400 Nhóm P3 400 P4 S1 200 Nhóm P4 500 P4 S4 300 P5 S1 100 Nhóm P5 500 P5 S4 400 P6 S1 100 Nhóm P6 100 Bai-6.doc *** Trang 19
Cách gọi hàm gộ p: set-function ( [DISTINCT|ALL] column-1 ) Trong đ ó, set-function là mộ t trong các hàm: COUNT – đếm số bản ghi • SUM – Tổng trên các bản ghi • AVG – Tính giá trị trung bình cộ ng SUM()/COUNT(). • MIN – Giá trị bé nhất • MAX – Giá trị cao nhất • Hàm COUNT luôn luôn cho kết qu ả là số nguyên. Kiểu dữ liệu củ a cấc hàm khác phụ thuộ c vào kiểu dữ liệu củ a tham số. Các hàm gộp không tính giá trị NULL. Tham số của chúng có thể là tên cộ t hoặc một biểu thức. Có hai tùy chọ n là DISTINCT và ALL. Tùy chọn ALL chỉ đ ịnh là tính toàn bộ các b ản ghi - kể cả đúp. Tùy chọn DISTINCT chỉ định chỉ tính các b ản ghi không b ị lặp. Hàm COUNT có một dạng thức đặc biệt: COUNT(*) tính tất cả các bản ghi, bất kỳ b ản ghi đó chứa giá trị gì (kể cả NULL). 1.19. Mệnh đề HAVING Mệnh đề HAVING tương ứng với mệnh đ ề GROUP BY. Nó cũ ng có vai trò như là mệnh đề WHERE đố i với mệnh đ ề FROM. Mệnh đề này đ ứng đ ằng sau mệnh đề GROUP BY. HAVING predicate Giố ng như mệnh đ ề WHERE, mệnh đ ề HAVING lọ c các bản ghi thuộ c nhóm. Ví dụ: SELECT s_id, COUNT(*) parts FROM sp GROUP BY s_id HAVING COUNT(*) > 1 s_id parts S1 6 S2 2 S4 3 Bai-6.doc *** Trang 20