Cơ sở dữ liệu - Chương 3: Ngôn ngữ truy vấn SQL

Nội dung text: Cơ sở dữ liệu - Chương 3: Ngôn ngữ truy vấn SQL

1. Môn CƠ SỞ DỮ LIỆU Chương 3: Ngôn ngữ truy vấn SQL
2. Nội dung 1. Giới thiệu SQL 2. Môi trường SQL 3. Dùng DDL để định nghĩa CSDL 4. Dùng DML để thao tác trên CSDL u INSERT u DELETE u UPDATE u SELECT 5. Các phép kết dữ liệu trên nhiều bảng 6. Subquery – truy vấn con 2
3. 1. SQL là gì? ◼ Structured Query Language – Ngôn ngữ truy vấn có cấu trúc ◼ Ngôn ngữ chuẩn cho các RDBMS (relational database management systems - hệ quản trị CSDL quan hệ) ◼ Chuẩn SQL-92: u Định nghĩa cấu trúc/ngữ nghĩa của việc định nghĩa/thao tác trên dữ liệu u Định nghĩa cấu trúc dữ liệu u Hai chuẩn: level 1 (minimal) và level 2 (complete) u Khả năng mở rộng 3
4. 2. Môi trường SQL ◼ Data Definition Language (DDL): Các lệnh mô tả CSDL, gồm tạo, sửa, xoá các bảng và ràng buộc ◼ Data Manipulation Language (DML): Các lệnh thao tác truy vấn dữ liệu ◼ Data Control Language (DCL): Các lệnh điều khiển CSDL, gồm việc quản lý quyền hạn của user 4
5. 3. Dùng DDL để định nghĩa CSDL ◼ Data Definition Language (DDL) ◼ Lệnh tạo bảng: CREATE TABLE ◼ Lệnh sửa bảng: ALTER TABLE ◼ Lệnh xóa bảng: DROP TABLE 5
6. 3. Dùng DDL để định nghĩa CSDL (tt) Các bước tạo bảng: Cú pháp lệnh CREATE TABLE 1. Kiểu dữ liệu cho các thuộc tính 2. Cột nào được/không được phép null 3. Các cột giá trị duy nhất (khoá dự tuyển) 4. Khoá chính – khóa ngoại 5. Giá trị mặc định 6. Ràng buộc miền trị 7. Tạo bảng và chỉ mục 6
7. 3. Dùng DDL để định nghĩa CSDL (tt) ◼ Lệnh ALTER TABLE dùng để thay đổi các mô tả của bảng ALTER TABLE CUSTOMER_T ADD (TYPE VARCHAR(2)) ◼ Lệnh DROP TABLE dùng để xoá bảng khỏi CSDL DROP TABLE CUSTOMER_T 7
8. 4. DML - Lệnh INSERT ◼ Thêm dữ liệu (bản ghi, hàng) vào bảng ◼ Thêm dữ liệu vào tất cả các cột trong bảng: INSERT INTO CUSTOMER_T VALUES (001, ‘CONTEMPORARY Casuals’, 1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601); ◼ Thêm dữ liệu vào các cột được chỉ định rõ trong bảng, các cột còn lại nhận giá trị mặc định hoặc NULL: INSERT INTO PRODUCT_T (PRODUCT_ID, PRODUCT_DESCRIPTION,PRODUCT_FINISH, STANDARD_PRICE, PRODUCT_ON_HAND) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8); ◼ Thêm dữ liệu từ bảng khác: INSERT INTO CA_CUSTOMER_T SELECT * FROM CUSTOMER_T WHERE STATE = ‘CA’; 8
9. 4. DML - Lệnh DELETE - Lệnh UPDATE ◼ Lệnh Delete: Xoá dữ liệu (bản ghi, hàng) trong bảng ◼ Xoá các hàng theo điều kiện: DELETE FROM CUSTOMER_T WHERE STATE = ‘HI’; ◼ Xoá tất cả các hàng trong bảng: DELETE FROM CUSTOMER_T; ◼ Lệnh Update: Sửa dữ liệu (bản ghi, hàng) trong bảng: UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7; 9
10. 4. DML - Lệnh SELECT ◼ Truy vấn (xem) dữ liệu trong các bảng, view ◼ Các mệnh đề của lệnh SELECT: SELECT: Danh sách các cột kết quả của lệnh truy vấn FROM: Danh sách các bảng hoặc view để lấy dữ liệu WHERE: Điều kiện lựa chọn các hàng trong bảng GROUP BY: Nhóm các hàng dữ liệu theo loại HAVING: Điều kiện cho các nhóm để lựa chọn nhóm kết quả ORDER BY: Sắp xếp dữ liệu thu được 10
11. Thứ tự xử lý các mệnh đề trong lệnh SELECT 11
12. 4. DML - Ví dụ ◼ Tìm những sản phẩm có giá < $275 SELECT PRODUCT_NAME, STANDARD_PRICE FROM PRODUCT_V WHERE STANDARD_PRICE < 275 12
13. 4. DML - Lệnh SELECT dùng ALIAS, hàm ◼ Alias là tên thay thế cho bảng hoặc cột, khi đó cột kết quả lấy tên alias: SELECT CUST.CUSTOMER AS NAME, CUST.CUSTOMER_ADDRESS FROM CUSTOMER_V CUST WHERE NAME = ‘Nguyen Van A’; ◼ Dùng hàm tập hợp COUNT để tìm tổng số các hoá đơn có trong bảng hoá đơn SELECT COUNT(*) FROM ORDER_LINE_V WHERE ORDER_ID = 1004 13
14. 4.(tt)Lệnh SELECT dùng toán tử luận lý ◼ Các toán tử AND, OR và NOT dùng trong mệnh đề WHERE SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH, STANDARD_PRICE FROM PRODUCT_V WHERE (PRODUCT_DESCRIPTION LIKE ‘%Desk’ OR PRODUCT_DESCRIPTION LIKE ‘%Table’) AND UNIT_PRICE > 300; LIKE dùng để so sánh chuỗi. Dấu % trong chuỗi ‘%Desk’ chỉ mọi chuỗi kết thúc bằng “Desk” 14
15. 4. (tt) Lệnh SELECT dùng ORDER BY ◼ Sắp xếp kết quả theo thứ tự STATE tăng dần, cùng state thì sắp theo CUSTOMER_NAME tăng dần. Dùng từ khóa ASC, DESC SELECT CUSTOMER_NAME, CITY, STATE FROM CUSTOMER_V WHERE STATE IN (‘FL’, ‘TX’, ‘CA’, ‘HI’) ORDER BY STATE, CUSTOMER_NAME; IN lựa chọn các hàng có giá trị STATE là FL, TX, CA, hoặc HI. Tốt hơn OR 15
16. 4. (tt) Lệnh SELECT dùng GROUP BY ◼ Dùng với các hàm tập hợp SELECT STATE, COUNT(STATE) FROM CUSTOMER_V GROUP BY STATE; Muốn dùng cột đơn trị với hàm tập hợp thì phải đưa cột này vào mệnh đề GROUP BY 16
17. 4. (tt) Lệnh SELECT dùng HAVING ◼ Chỉ dùng với GROUP BY, làm điều kiện lựa chọn các nhóm hàng SELECT STATE, COUNT(STATE) FROM CUSTOMER_V GROUP BY STATE HAVING COUNT(STATE) > 1; Lựa chọn những nhóm có nhiều hơn 1 hàng. Giống mệnh đề WHERE nhưng hoạt động trên nhóm chứ không phải từng hàng riêng rẽ 17
18. 5. Phép kết nhiều bảng ◼ Cho biết mọi thông tin cần thiết để xuất hoá đơn có số 1006 Phép kết 4 bảng dữ liệu SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_ADDRESS, CITY, SATE, POSTAL_CODE, ORDER_T.ORDER_ID, ORDER_DATE, QUANTITY, PRODUCT_NAME, UNIT_PRICE, (QUANTITY * UNIT_PRICE) FROM CUSTOMER_T, ORDER_T, ORDER_LINE_T, PRODUCT_T WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_LINE.CUSTOMER_ID AND ORDER_T.ORDER_ID = ORDER_LINE_T.ORDER_ID AND ORDER_LINE_T.PRODUCT_ID = PRODUCT_PRODUCT_ID AND ORDER_T.ORDER_ID = 1006; Mỗi cặp bảng cần một điều kiện so sánh bằng giữa khoá chính và khoá ngoại trong mệnh đề WHERE 18
19. 6. Subquery – Truy vấn con ◼ Subquery = đặt một câu truy vấn (SELECT) bên trong một câu truy vấn khác ◼ Đặt ở: Trong điều kiện của mệnh đề WHERE Trong mệnh đề FROM Trong mệnh đề HAVING ◼ Hai loại subquery: Non correlated – chỉ thực thi một lần, không phụ thuộc vào dữ liệu từ câu truy vấn ngoài Correlated – thực thi đối với mỗi hàng trả về từ câu truy vấn ngoài, có thể dùng với toán tử EXISTS 19
20. 6. Subquery – Ví dụ ◼ Cho biết các khách hàng nào có hoá đơn IN kiểm tra giá trị CUSTOMER_ID của một hàng có nằm trong danh sách kết quả của subquery SELECT CUSTOMER_NAME FROM CUSTOMER_T WHERE CUSTOMER_ID IN (SELECT DISTINCT CUSTOMER_ID FROM ORDER_T); Subquery nằm trong dấu ngoặc đơn, kết quả từ subquery được dùng trong mệnh đề WHERE của câu truy vấn ngoài 20
21. Quá trình xử lý một non- correlated subquery Subquery được thực thi chỉ một lần 21
22. 6. (tt) Thí dụ correlated subquery ◼ Cho biết các hoá đơn có sản phẩm được hoàn tất trong natural ash EXISTS cho giá trị TRUE nếu kết quả của subquery khác rỗng, ngoài ra là FALSE SELECT DISTINCT ORDER_ID FROM ORDER_LINE_T WHERE EXISTS (SELECT * FROM PRODUCT_T WHERE PRODUCT_ID = ORDER_LINE_T.PRODUCT_ID AND PRODUCT_FINISH = ‘Natural ash’); Subquery kiểm tra các giá trị có từ câu truy vấn ngoài 22
23. Quá trình xử lý một correlated Subquery thực thi đối với mỗi hàng subquery dữ liệu từ câu truy vấn ngoài 23
24. 6. (tt)Kết quả subquery làm bảng ◼ Cho biết các sản phẩm có giá cao hơn giá trung bình Subquery tạo nên Cột subquery là một bảng dẫn xuất được hàm tập hợp có tên dùng trong mệnh đề alias, tên này được FROM câu truy vấn dùng trong câu truy vấn ngoài ngoài SELECT PRODUCT_DESCRIPTION, STANDARD_PRICE, AVGPRICE FROM (SELECT AVG(STANDARD_PRICE) AVGPRICE FROM PRODUCT_T), PRODUCT_T WHERE STANDARD_PRICE > AVG_PRICE; Mệnh đề WHERE không thể chứa các hàm tập hợp, nhưng trường hợp này dùng kết quả từ subquery 24