Bài giảng Cơ sở dũ liệu - Chương 7: Ngôn ngữ SQL

 ] column_alias ] | column_alias = expression }    [ ,...n ] 7Mệnh đề SELECTALL (DEFAULT): hiển thị tất cả các bản ghi kể cả các hàng trùng nhau.DISTINCT: chỉ hiển thị các hàng duy nhất không trùng nhau trong bảng kết quả. Ví dụ: liệt kê tất cả các thành phố mà hiện thời các khách hàng đang sốngSELECT DISTINCT City , Region FROM Customers8Mệnh đề SELECTTOP n [PERCENT]: chỉ có n hàng đầu tiên hay n% của các hàng của bảng kết quả được xuất .WITH TIES: cho phép 1 số hàng được thêm vào bảng kết quả gốc nếu các hàng này có cùng giá trị trong cột ORDER BY với hàng cuối cùng của n hàng đầu tiên được xác định.Ví dụ: liệt kê 3 hoá đơn có cước phí cao nhấtSELECT top 3 with ties OrderID, Freight from Orders order by Freight DESCNếu hàng thứ tư có cùng cước phí (freight) với hàng thứ ba trong bảng kết quả thì sao???9Mệnh đề SELECTTrường tính toán được xây dựng từ biểu thức (Expression) có liên quan đến các trường cơ sở. Biểu thức bao gồm:Các trườngCác toán tử số học, logic, so sánhCác hàm 10Mệnh đề SELECTVí dụ 1:SELECT OrderID, ProductID, Amount =UnitPrice*Quantity* (1- Discount)FROM [Order Details]11Các hàm tập hợp (Aggregate function)Tên hàmÝ nghĩaSUMTính tổng các sốMINTrả về giá trị nhỏ nhấtMAXTrả về giá trị lớn nhấtAVGTính giá trị trung bìnhCOUNTĐếm số phần tửĐược dùng để tổng kết các giá trị của 1 cột hay 1 nhóm cột bên trong 1 bảng và cho kết quả là 1 giá trị đơn12Ví dụ các hàm gộp trong lệnh selectSELECT sum(Freight) from dbo.Orders Tính tổng cước phí chuyên chở của tất cả hoá đơnSELECT count(OrderID) from dbo.Orders Đếm số hoá đơn SELECT SUM(UnitPrice*Quantity*(1-Discount)) FROM dbo.[Order Details] Tính doanh số bán hàng13Mệnh đề WHEREChứa điều kiện lọc dữ liệu cần trả vềCú pháp: WHERE Ví dụ:SELECT CustomerID, CompanyName FROM Northwind.dbo.Customers WHERE Region = 'WA' 14Mệnh đề ORDER BYXác định thứ tự của bộ kết quảCú pháp[ ORDER BY { order_by_expression [ ASC | DESC ] }     [ ,...n] ] ASC (ascending) : xếp theo thứ tự tăngDESC (descending): xếp theo thứ tự giảm15Nhóm dữ liệu trong bảng kết quảNhững mệnh đề dùng để nhóm dữ trong bảng kết quả:GROUP BY: tổng hợp bảng kết quả theo nhóm bằng cách dùng các hàm gộpCOMPUTE và COMPUTE BY: mệnh đề COMPUTE trong lệnh SELECT được dùng để phát ra các hàng tổng hợp bằng cách dùng hàm gộp. Mệnh đề COMPUTE BY được dùng để tổng hợp thêm các hàng kết quả theo cột16Mệnh đề GROUP BYCú pháp:[ GROUP BY [ ALL ] group_by_expression [ ,...n ] ALL: bảng kết quả sẽ chứa tất cả các nhóm kể cả những nhóm không thỏa mãn điều kiện lọc trong trong mệnh đề WHERE, những nhóm không thoả điều kiện sẽ có giá trị null. group_by_expression: biểu thức dùng để xác định cột được nhóm17Ví dụSELECT Type, Advance = SUM (Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY TypeType 	Advance------------ 	---------------------business 	25125.0000mod_cook 	15000.0000trad_cook 	19000.0000(3 row(s) affected)18Ví dụ 2SELECT Type, Advance = SUM (Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY ALL TypeType 	Advance------------ 	---------------------business 	25125.0000mod_cook 	15000.0000popular_comp 	NULLpsychology 	NULLtrad_cook 	19000.0000UNDECIDED 	NULL(6 row(s) affected)19GROUP BY và HAVINGCó thể hạn chế các nhóm trong bảng kết quả bằng mệnh đề HAVING. Chỉ sau khi dữ liệu đã được nhóm và tổng hợp , điều kiện trong mệnh đề HAVING mới được áp dụng. Không thể dùng 1 cột mà nó không tham gia vào hàm gộp của mệnh đề SELECT hay của mệnh đề GROUP BY. SELECT pub_id, AVG(price) FROM titles GROUP BY pub_id HAVING (AVG(price) > 10) 20Sử dụng WHERE và HAVINGMệnh đề HAVING giống như mệnh đề WHERE nhưng chỉ áp dụng cho cả nhóm trong khi mệnh đề WHERE áp dụng cho từng hàng. Một truy vấn có thể chứa cả mệnh đề WHERE và mệnh đề HAVING. Mệnh đề WHERE được áp dụng trước cho các hàng trong bảng được truy vấn. Chỉ những hàng nào thoả mãn điều kiện của mệnh đề WHERE mới được nhóm dữ liệu.Sau đó mệnh đề HAVING sẽ được áp dụng cho các nhóm. Chỉ những nhóm thoả mãn điều kiện HAVING mới được xuất ra bảng kết quả. 21Ví dụSELECT titles.pub_id, AVG(titles.price) FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id WHERE publishers.state = 'CA' GROUP BY titles.pub_id HAVING AVG(price) > 10 22SELECT INTOLệnh SELECT với mệnh đề INTO được dùng dể lưu trữ kết quả truy vấn vào 1 bảng mới mà không cần phải định nghĩa trước bảng. Cú phápSELECT columns_listINTO new_table_nameFROM table_name1, table_name2,,table_name nWHERE condition1, condition2,.,condition nVí dụSELECT Title_Id, Title INTO NewTitlesFROM Titles WHERE Price > $1523Mệnh đề JOINMệnh đề join dùng để khôi phục dữ liệu từ nhiều hơn 1 bảngCú phápSELECT column_name [,n..] FROM table_name table_alias[CROSS|INNER|[LEFT | RIGHT]OUTER] JOIN table_name table_alias[ON table_name.ref_column_name join_operator table_name.ref_column_name][WHERE search_condition]24Kết nối các bảngKết nối chỉ tồn tại trong thời gian truy vấn. Kết nối không thay đổi dữ liệu trong các bảng của cơ sở dữ liệu. Nên tạo bí danh (alias) cho tên bảng để tránh gõ tên dài và làm truy vấn dễ đọc hơnVí dụ SELECT t.Title_Id, t.Title, p.Pub_Id from Titles tJOIN Publishers p on t.Pub_Id=p.Pub_IdWHERE Pub_Name='New Moon Books'25Các cột tham gia kết nốiNếu kết nối nhiều hơn 2 bảng thì kết nối 2 bảng trước, sau đó kết nối nhóm này với bảng thứ ba.Ví dụ SELECT OrderID,p.ProductID, ProductName,CategoryName ,QuantityFROM [Order Details] o JOIN Products p ON o.ProductID = p.ProductIDJOIN Categories c ON p.CategoryID = c.CategoryID26Các loại kết nốiInner JoinOuter JoinCross JoinEqui JoinNatural JoinSelf Join27Kết nối nội - Inner joinsTrong kết nối nội, dữ liệu từ nhiều bảng được hiển thị sau khi so sánh giá trị trong 1 cột chung. Chỉ những hàng mà có giá trị thoả mãn điều kiện kết nối trong cột chung đó mới được hiển thị. Tích Cartesian: việc kết nối nhiều bảng mà không có điều kiện kết nối trong mệnh đề ON sẽ tạo ra tích cartesian giữa 2 bảng28Kết nối nội - Inner joinsVí dụ:SELECT t.title,t.price, p.pub_name, p.city, t.pubdatefrom titles t join publishers pon t.pub_id = p.pub_id29Kết nối nội với toán tử lớn hơnCó thể thực hiện kết nối 2 bảng với điều kiện kết nối dùng toán tử không bằng nhau.Ví dụ:SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state FROM publishers p INNER JOIN authors a ON a.state > p.state WHERE p.pub_name = 'New Moon Books' ORDER BY au_lname ASC, au_fname ASC 30Kết nối ngoại - Outer joinsKết nối ngoại được dùng để cho ra kết quả chứa tất cả các hàng của 1 bảng và các hàng trùng nhau của bảng còn lại. Những cột mà không có giá trị phù hợp sẽ được hiển thị giá trị NULL.Cú phápSELECT column_name, column_name [,column_name]FROM table_name [LEFT | RIGHT] OUTER JOIN table_nameON table_name.ref_column_namejoin_operator table_name.ref_column_name31Kết nối trái - LEFT OUTER JOINTất cả các hàng từ bảng bên trái trong mối kết nối giữa 2 bảng sẽ được hiển thị trong bảng kết quả. Ví dụ:SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 32Cross joinCross join trả về mọi tổ hợp có thể có của tất cả các hàng trong các bảng kết nối. Cross join không có mệnh đề ONNếu không mệnh đề WHERE, cross join sẽ tạo ra tích CartesianNếu có mệnh đề WHERE, cross join sẽ thực hiện như 1 kết nối nội33Kết nối chéo - Cross joinVí dụ 1:SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers ORDER BY au_lname DESC Kết quả chứa 184 hàng (authors có 23 hàng và publishers có 8 hàng; 23 x 8 = 184 hàng).Ví dụ 2:SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers WHERE authors.city = publishers.city ORDER BY au_lname DESC SELECT au_fname, au_lname, pub_name FROM authors INNER JOIN publishers ON authors.city = publishers.city ORDER BY au_lname DESC34Truy vấn con - SubqueriesSubquery là lệnh SELECT mà kết quả trả về là 1 giá trị đơn (single value) và được đặt lồng vào bên trong các lệnh SELECT, INSERT, UPDATE, hay DELETE, hay bên trong truy vấn con khác. Subquery có thể được dùng bất kỳ nơi nào mà biểu thức được phép dùng35SubqueriesVí dụ 1:SELECT Ord.OrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM Northwind.dbo.[Order Details] ASOrdDet WHERE Ord.OrderID =OrdDet.OrderID) AS MaxUnitPrice FROM Northwind.dbo.Orders AS Ord 36SubqueriesSubquery có thể được dùng để khôi phục dữ liệu từ nhiều bảng và có thể được dùng như 1 cách khác của join (alternative to a join)37SubqueriesCách 1: dùng subquery.SELECT ProductName FROM Products WHERE UnitPrice = (SELECT UnitPrice FROM Products WHERE ProductName = 'Sir Rodney''s Scones')Cách 2: dùng join SELECT Prd1.ProductName FROM Products AS Prd1 JOIN Products AS Prd2 ON (Prd1.UnitPrice = Prd2.UnitPrice) WHERE Prd2.ProductName = 'Sir Rodney''s Scones' 38SubqueriesSubquery có thể được dùng theo 1 trong các dạng sau:WHERE expression [NOT] IN (subquery)WHERE expression comparison_operator [ANY | ALL] (subquery)WHERE [NOT] EXISTS (subquery) 39Subqueries với toán tử INKết quả của subquery được dùng với IN (hay với NOT IN) thường là 1 danh sách (list) chứa từ 0 đến nhiều giá trị. Ví dụ:SELECT pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business') Dùng cách 2 với mệnh đề join???40Subquery với các toán tử so sánhCác toán tử so sánh đơn giản (unmodified comparison operator)=, , >, > =, , ! (SELECT MIN(price) FROM titles) 41Subquery với các toán tử so sánhToán tử so sánh phức (Modified Comparison operators) là toán tử so sánh đơn giản có kèm theo các từ All, any hay some(=toán tử s/sánh+ [All,Any,some])Subquery bắt đầu với toán tử so sánh phức sẽ trả về 1 danh sách (list) của 0 hay nhiều gia trị và có thể bao gồm cả mệnh đề GROUP BY hay HAVING.42Subquery với các toán tử so sánh>ALL có nghĩa lớn hơn mọi giá trị.>ANY có nghĩa lớn hơn ít nhất 1 giá trị Vd: >ALL (1, 2, 3) lớn hơn 3 >ANY (1, 2, 3) lớn hơn 1 Ví dụ:SELECT au_lname, au_fname FROM authors WHERE city ALL (SELECT city FROM publishers) 43Subquery với các toán tử so sánh44