Giáo trình Cơ sở dữ liệu - Ngành: Tin học ứng dụng, công nghệ phần mềm

ntactName AS [Contact Person] FROM Customers; Câu lệnh SQL sau tạo một bí danh có tên "Address" kết hợp bốn cột (Address, PostalCode, City và Country): SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers; 36 Ví dụ về bí danh cho bảng Câu lệnh SQL sau đây chọn tất cả các đơn đặt hàng từ khách hàng với CustomerID = 4 (Around the Horn). Chúng tôi sử dụng bảng "Customers" và "Orders" và cung cấp cho chúng bí danh bảng tương ứng là "c" và "o" (Ở đây chúng tôi sử dụng bí danh để làm cho SQL ngắn hơn): SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID; Câu lệnh SQL sau giống như trên, nhưng không có bí danh: SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID; Bí danh có thể hữu ích khi: - Có nhiều hơn một bảng liên quan đến một truy vấn - Các hàm được sử dụng trong truy vấn - Tên cột lớn hoặc không dễ đọc - Hai hoặc nhiều cột được kết hợp với nhau 2.5. Hàm MIN() và MAX() Hàm MIN() trả về giá trị nhỏ nhất của cột đã chọn. Hàm MAX() trả về giá trị lớn nhất của cột đã chọn. Cú pháp MIN() SELECT MIN(column_name) FROM table_name WHERE condition; Cú pháp MAX() SELECT MAX(column_name) FROM table_name WHERE condition; Dưới đây là một lựa chọn từ bảng "Products" trong cơ sở dữ liệu mẫu Northwind: 37 Câu lệnh SQL sau đây tìm thấy giá của sản phẩm rẻ nhất: SELECT MIN(Price) AS SmallestPrice FROM Products; Câu lệnh SQL sau đây tìm thấy giá của sản phẩm đắt nhất: SELECT MAX(Price) AS LargestPrice FROM Products; 2.6. Các hàm COUNT(), AVG() và SUM() Hàm COUNT() trả về số hàng phù hợp với một tiêu chí được chỉ định. Hàm AVG() trả về giá trị trung bình của một cột số. Hàm SUM() trả về tổng tổng của một cột số. Cú pháp COUNT() SELECT COUNT(column_name) FROM table_name WHERE condition; Cú pháp AVG() SELECT AVG(column_name) FROM table_name WHERE condition; Cú pháp SUM() SELECT SUM(column_name) FROM table_name WHERE condition; Cơ sở dữ liệu Demo Dưới đây là lựa chọn từ bảng "Products" trong cơ sở dữ liệu mẫu Northwind: 38 Ví dụ COUNT() Câu lệnh SQL sau tìm số lượng sản phẩm: SELECT COUNT(ProductID) FROM Products; Lưu ý: Giá trị NULL không được tính. Ví dụ AVG() Câu lệnh SQL sau tìm giá trung bình của tất cả các sản phẩm: SELECT AVG(Price) FROM Products; Lưu ý: Giá trị NULL bị bỏ qua. Cơ sở dữ liệu Demo Dưới đây là lựa chọn từ bảng "OrderDetails" trong cơ sở dữ liệu mẫu Northwind: Ví dụ SUM() Câu lệnh SQL sau tìm tổng của các trường "Quantity" trong bảng "OrderDetails": SELECT SUM(Quantity) FROM OrderDetails; Lưu ý: Giá trị NULL bị bỏ qua. 2.7. Từ khóa INNER JOIN Từ khóa INNER JOIN chọn các bản ghi có giá trị phù hợp trong cả hai bảng. Cú pháp INNER JOIN: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; 39 Cơ sở dữ liệu Demo: Trong hướng dẫn này, chúng tôi sẽ sử dụng cơ sở dữ liệu mẫu Northwind nổi tiếng. Dưới đây là lựa chọn từ bảng "Orders": Và lựa chọn từ bảng “Customers”: Ví dụ về INNER JOIN: Câu lệnh SQL sau chọn tất cả các đơn hàng có thông tin khách hàng: SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; Lưu ý: Từ khóa INNER JOIN chọn tất cả các hàng từ cả hai bảng miễn là có sự trùng khớp giữa các cột. Nếu có các bản ghi trong bảng "Orders" không khớp trong "Customers", các đơn đặt hàng này sẽ không được hiển thị! JOIN 3 bảng: Câu lệnh SQL sau chọn tất cả các đơn đặt hàng có thông tin khách hàng và người giao hàng: SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID); 2.8. Từ khóa ORDER BY 40 Từ khóa ORDER BY được sử dụng để sắp xếp tập kết quả theo thứ tự tăng dần hoặc giảm dần. Từ khóa ORDER BY sắp xếp các bản ghi theo thứ tự tăng dần theo mặc định. Để sắp xếp các bản ghi theo thứ tự giảm dần, hãy dùng từ khóa DESC. SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; Dưới đây là một lựa chọn từ bảng "Customers" trong cơ sở dữ liệu mẫu Northwind: Câu lệnh SQL sau chọn tất cả khách hàng từ bảng "Customers", được sắp xếp theo cột "Country": SELECT * FROM Customers ORDER BY Country; Câu lệnh SQL sau chọn tất cả khách hàng từ bảng "Customers", được sắp xếp GIẢM DẦN theo cột "Country": SELECT * FROM Customers ORDER BY Country DESC; 41 Câu lệnh SQL sau chọn tất cả khách hàng từ bảng "Customers", được sắp xếp theo cột "Country" và "CustomerName". Điều này có nghĩa là nó đặt hàng theo Quốc gia, nhưng nếu một số hàng có cùng Quốc gia, nó sẽ đặt hàng chúng theo CustomerName: SELECT * FROM Customers ORDER BY Country, CustomerName; Câu lệnh SQL sau chọn tất cả khách hàng từ bảng "Customers", được sắp xếp tăng dần theo cột "Country" và giảm dần theo cột "CustomerName": SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; 2.9. Mệnh đề GROUP BY 42 Mệnh đề GROUP BY nhóm các hàng có cùng giá trị thành các hàng tóm tắt, như "tìm số lượng khách hàng ở mỗi quốc gia". Mệnh đề GROUP BY thường được sử dụng với các hàm tổng hợp (COUNT, MAX, MIN, SUM, AVG) để nhóm kết quả được đặt bởi một hoặc nhiều cột. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); Câu lệnh SQL sau liệt kê số lượng khách hàng ở mỗi quốc gia: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; Câu lệnh SQL sau liệt kê số lượng khách hàng ở mỗi quốc gia, được sắp xếp cao đến thấp: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC; 43 2.10. Mệnh đề HAVING Mệnh đề HAVING đã được thêm vào SQL vì không thể sử dụng từ khóa WHERE với các hàm tổng hợp. Cú pháp HAVING: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); Cơ sở dữ liệu Demo: Dưới đây là lựa chọn từ bảng "Customers" trong cơ sở dữ liệu mẫu Northwind: Ví dụ về HAVING: 44 Câu lệnh SQL sau liệt kê số lượng khách hàng ở mỗi quốc gia. Chỉ bao gồm các quốc gia có hơn 5 khách hàng: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; Câu lệnh SQL sau liệt kê số lượng khách hàng ở mỗi quốc gia, được sắp xếp từ cao đến thấp (Chỉ bao gồm các quốc gia có hơn 5 khách hàng): SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC; Cơ sở dữ liệu Demo: Dưới đây là lựa chọn từ bảng "Orders" trong cơ sở dữ liệu mẫu Northwind: Và lựa chọn từ bảng "Employees": Thêm các ví dụ có HAVING: Câu lệnh SQL sau liệt kê các nhân viên đã đăng ký hơn 10 đơn đặt hàng: SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10; Câu lệnh SQL sau liệt kê nếu nhân viên "Davolio" hoặc "Fuller" đã đăng ký hơn 25 đơn đặt hàng: SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID WHERE LastName = 'Davolio' OR LastName = 'Fuller' GROUP BY LastName HAVING COUNT(Orders.OrderID) > 25; 2.11. Toán tử LIKE 45 Toán tử LIKE được sử dụng trong mệnh đề WHERE để tìm kiếm một mẫu được chỉ định trong một cột. Có hai ký tự đại diện thường được sử dụng kết hợp với toán tử LIKE: - “%” Dấu phần trăm đại diện cho các ký tự bằng không, một hoặc nhiều ký tự. - “_” Gạch dưới đại diện cho một ký tự duy nhất. SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; Dưới đây là một số ví dụ cho thấy các toán tử LIKE khác nhau có ký tự đại diện '%' và '_': Bảng dưới đây cho thấy bảng "Customers" hoàn chỉnh từ cơ sở dữ liệu mẫu Northwind: Câu lệnh SQL sau chọn tất cả khách hàng có CustomerName bắt đầu bằng "a": SELECT * FROM Customers WHERE CustomerName LIKE 'a%'; 46 Câu lệnh SQL sau chọn tất cả khách hàng có CustomerName kết thúc bằng "a": SELECT * FROM Customers WHERE CustomerName LIKE '%a'; Câu lệnh SQL sau đây chọn tất cả khách hàng có CustomerName có "hoặc" ở bất kỳ vị trí nào: SELECT * FROM Customers WHERE CustomerName LIKE '%or%'; Câu lệnh SQL sau chọn tất cả khách hàng có CustomerName có "r" ở vị trí thứ hai: SELECT * FROM Customers WHERE CustomerName LIKE '_r%'; 47 Câu lệnh SQL sau chọn tất cả khách hàng có CustomerName bắt đầu bằng "a" và dài ít nhất 3 ký tự: SELECT * FROM Customers WHERE CustomerName LIKE 'a__%'; Câu lệnh SQL sau chọn tất cả khách hàng có ContactName bắt đầu bằng "a" và kết thúc bằng "o": SELECT * FROM Customers WHERE ContactName LIKE 'a%o'; Câu lệnh SQL sau chọn tất cả khách hàng có CustomerName không bắt đầu bằng "a": SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%'; 2.12. Toán tử BETWEEN Toán tử BETWEEN chọn các giá trị trong một phạm vi nhất định. Các giá trị có thể là số, văn bản hoặc ngày tháng. Cú pháp BETWEEN SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; Cơ sở dữ liệu Demo 48 Dưới đây là lựa chọn từ bảng "Products" trong cơ sở dữ liệu mẫu Northwind: Ví dụ BETWEEN Câu lệnh SQL sau đây chọn tất cả các sản phẩm có giá GIỮA 10 và 20: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20; Ví dụ NOT BETWEEN Để hiển thị các sản phẩm nằm ngoài phạm vi của ví dụ trước, hãy sử dụng NOT BETWEEN: SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; Ví dụ BETWEEN với IN Câu lệnh SQL sau đây chọn tất cả các sản phẩm có giá GIỮA 10 và 20. Ngoài ra, không hiển thị các sản phẩm có CategoryID là 1,2 hoặc 3: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3); Ví dụ BETWEEN về giá trị văn bản Câu lệnh SQL sau chọn tất cả các sản phẩm có Tên sản phẩm GIỮA ‘Carnarvon Tigers’ và ‘Mozzarella di Giovanni’: SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName; Câu lệnh SQL sau chọn tất cả các sản phẩm có Tên sản phẩm GIỮA ‘Carnarvon Tigers’ và ‘Chef Antons Cajun Seasoning’: SELECT * FROM Products WHERE ProductName BETWEEN ‘Carnarvon Tigers’ AND ‘Chef Antons Cajun Seasoning’ ORDER BY ProductName; Ví dụ NOT BETWEEN về giá trị văn bản Câu lệnh SQL sau chọn tất cả các sản phẩm có Tên sản phẩm KHÔNG GIỮA 'Carnarvon Tigers' và 'Mozzarella di Giovanni': SELECT * FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella 49 di Giovanni' ORDER BY ProductName; Bảng mẫu Dưới đây là lựa chọn từ bảng "Orders" trong cơ sở dữ liệu mẫu Northwind: Ví dụ về BETWEEN Dates Câu lệnh SQL sau đây chọn tất cả các đơn đặt hàng có Ngày đặt hàng GIỮA '01 -July-1996 ' và '31 -July-1996': SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#; Hoặc là: SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31'; 2.13. Toán tử AND, OR và NOT Mệnh đề WHERE có thể được kết hợp với các toán tử AND, OR và NOT. Toán tử AND và OR được sử dụng để lọc các bản ghi dựa trên nhiều hơn một điều kiện: - Toán tử AND hiển thị một bản ghi nếu tất cả các điều kiện được phân tách bằng AND đều TRUE. - Toán tử OR hiển thị một bản ghi nếu bất kỳ điều kiện nào được phân tách bởi OR là TRUE. Toán tử NOT hiển thị bản ghi nếu (các) điều kiện KHÔNG ĐÚNG. Cú pháp AND SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...; Cú pháp OR SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...; Cú pháp NOT SELECT column1, column2, ... FROM table_name WHERE NOT condition; 50 Cơ sở dữ liệu Demo: Bảng "Customers" hoàn chỉnh từ cơ sở dữ liệu mẫu của Northwind: Ví dụ AND Câu lệnh SQL sau chọn tất cả các trường từ "Customers" có quốc gia là "Germany" VÀ thành phố là "Berlin": SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin'; Ví dụ OR Câu lệnh SQL sau chọn tất cả các trường từ "Customers" có thành phố là "Berlin" HOẶC "München": SELECT * FROM Customers WHERE City='Berlin' OR City='München'; Câu lệnh SQL sau chọn tất cả các trường từ "Customers" có quốc gia là "Germany" HOẶC "Spain": SELECT * FROM Customers WHERE Country='Germany' OR Country='Spain'; Ví dụ NOT Câu lệnh SQL sau chọn tất cả các trường từ "Customers" mà quốc gia KHÔNG phải là "Germany": SELECT * FROM Customers WHERE NOT Country='Germany'; Kết hợp AND, OR và NOT Bạn cũng có thể kết hợp các toán tử AND, OR và NOT. Câu lệnh SQL sau chọn tất cả các trường từ "Customers" trong đó quốc gia là "Germany" VÀ thành phố phải là "Berlin" HOẶC "München" (sử dụng dấu ngoặc đơn để tạo biểu thức phức tạp): SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München'); Câu lệnh SQL sau chọn tất cả các trường từ "Customers" trong đó quốc gia KHÔNG phải là "Germany" và KHÔNG phải "USA": SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA'; 51 2.14. Toán tử IN Toán tử IN cho phép bạn chỉ định nhiều giá trị trong mệnh đề WHERE. Toán tử IN là cách viết tắt của nhiều điều kiện OR. Cú pháp IN SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); hoặc là: SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT); Cơ sở dữ liệu Demo Bảng dưới đây hiển thị bảng "Customers" hoàn chỉnh từ cơ sở dữ liệu mẫu của Northwind: Ví dụ về toán tử IN Câu lệnh SQL sau đây chọn tất cả khách hàng ở "Germany", "France" hoặc "UK": SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); Câu lệnh SQL sau chọn tất cả khách hàng KHÔNG ở "Germany", "France" hoặc "UK": SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK'); 2.15. Toán tử ANY, ALL và truy vấn con Toán tử ANY và ALL: - Toán tử ANY và ALL được sử dụng với mệnh đề WHERE hoặc HAVING. - Toán tử ANY trả về true nếu bất kỳ giá trị truy vấn con nào đáp ứng điều kiện. - Toán tử ALL trả về true nếu tất cả các giá trị truy vấn con đáp ứng điều kiện. Cú pháp ANY: SELECT column_name(s) FROM table_name 52 WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition); Cú pháp ALL: SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition); Lưu ý: Các operator phải là một toán tử so sánh chuẩn (=, , !=, >, >=, <, or <=). Cơ sở dữ liệu Demo: Dưới đây là lựa chọn từ bảng "Products" trong cơ sở dữ liệu mẫu Northwind: Và một lựa chọn từ bảng "OrderDetails": Ví dụ về ANY Toán tử ANY trả về TRUE nếu bất kỳ giá trị truy vấn con nào đáp ứng điều kiện. Câu lệnh SQL sau trả về TRUE và liệt kê tên sản phẩm nếu nó tìm thấy BẤT KỲ bản ghi nào trong bảng OrderDetails mà số lượng = 10: SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); Câu lệnh SQL sau trả về TRUE và liệt kê tên sản phẩm nếu nó tìm thấy BẤT KỲ bản ghi nào trong bảng OrderDetails mà số lượng> 99: SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99); Ví dụ về ALL: Toán tử ALL trả về TRUE nếu tất cả các giá trị truy vấn con đáp ứng điều kiện. 53 Câu lệnh SQL sau trả về TRUE và liệt kê tên sản phẩm nếu TẤT CẢ các bản ghi trong bảng OrderDetails có số lượng = 10 (vì vậy, ví dụ này sẽ trả về FALSE, vì không phải TẤT CẢ các bản ghi trong bảng OrderDetails đều có số lượng = 10): SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); 2.16. Mệnh đề LIMIT Mệnh đề LIMIT được sử dụng để giới hạn số lượng dữ liệu được trả về bởi câu lệnh SELECT. Cú pháp Sau đây là cú pháp cơ bản của câu lệnh SELECT với mệnh đề LIMIT. SELECT column1, column2, ... FROM table_name LIMIT [number of rows] Ví dụ: SELECT * FROM COMPANY LIMIT 6; Sau đây là cú pháp của mệnh đề LIMIT khi nó được sử dụng cùng với mệnh đề OFFSET. SELECT column1, column2, ... FROM table_name LIMIT [number of rows] OFFSET [row number] Ví dụ: SELECT * FROM COMPANY LIMIT 3 OFFSET 2; Bổ sung câu hỏi Chỉnh màu Bài tập thực hành: Cho cơ sở dữ liệu QuanLyNhanSu như sau: Câu 1: Tạo cơ sở dữ liệu như trên. 54 Câu 2: Truy vấn dữ liệu với các yêu cầu như sau: 1. Hiển thị tất cả thông tin của bảng NHANVIEN 2. Hiển thị thông tin của những nhân viên ở phòng số 5 3. Hiển thị mã nhân viên, họ nhân viên, tên lót và tên nhân viên của những nhân viên ở phòng số 5 và có lương >= 3000 4. Hiển thị mã nhân viên, tên nhân viên của những nhân viên có lương từ 2000 đến 8000 5. Hiển thị thông tin của những nhân viên ở địa chỉ có tên đường là Nguyễn 6. Cho biết số lượng nhân viên 7. Cho biết số lượng nhân viên trong từng phòng ban 8. Hiển thị thông tin về mã nhân viên, tên nhân viên và tên phòng ban ở phòng kế toán 55 Tài liệu cần tham khảo: [1] Nhập môn cơ sở dữ liệu quan hệ – NXB Khoa học và Kỹ thuật – Năm 2002. [2] Phương Lan, Nguyễn Thiên Băng – Cơ sở dữ liệu – NXB Lao động và Xã hội – Năm 2005. [3] Phương Lan – Giáo trình nhập môn cơ sở dữ liệu – NXB Lao động và Xã hội – Năm 2006. [4] Nguyễn Thiện Tâm, Trần Xuân Hải – Giáo trình SQL Server 2000 – NXB ĐHQG TP. Hồ Chí Minh [5] Phạm Hữu Khang, Đoàn Thiện Ngân – SQL Server 2005 Lập trình T-SQL – NXB Lao động – Xã hội