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

Môn CƠ SỞ DỮ LIỆUChương 3: Ngôn ngữ truy vấn SQLNội dungGiới thiệu SQLMôi trường SQLDùng DDL để định nghĩa CSDLDùng DML để thao tác trên CSDLINSERTDELETEUPDATESELECTCác phép kết dữ liệu trên nhiều bảngSubquery – truy vấn con2Structured Query Language – Ngôn ngữ truy vấn có cấu trúcNgôn ngữ chuẩn cho các RDBMS (relational database management systems - hệ quản trị CSDL quan hệ)Chuẩn SQL-92:Đị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Định nghĩa cấu trúc dữ liệuHai chuẩn: level 1 (minimal) và level 2 (complete)Khả năng mở rộng1. SQL là gì?3Data 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ộcData Manipulation Language (DML): Các lệnh thao tác truy vấn dữ liệuData 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 user2. Môi trường SQL4Data Definition Language (DDL)Lệnh tạo bảng: CREATE TABLELệnh sửa bảng: ALTER TABLELệnh xóa bảng: DROP TABLE3. Dùng DDL để định nghĩa CSDL5Cú pháp lệnh CREATE TABLECác bước tạo bảng:Kiểu dữ liệu cho các thuộc tínhCột nào được/không được phép nullCác cột giá trị duy nhất (khoá dự tuyển)Khoá chính – khóa ngoạiGiá trị mặc địnhRàng buộc miền trịTạo bảng và chỉ mục3. Dùng DDL để định nghĩa CSDL (tt)63. Dùng DDL để định nghĩa CSDL (tt)Lệnh ALTER TABLE dùng để thay đổi các mô tả của bảngALTER TABLE CUSTOMER_T ADD (TYPE VARCHAR(2))Lệnh DROP TABLE dùng để xoá bảng khỏi CSDLDROP TABLE CUSTOMER_T7Thêm dữ liệu (bản ghi, hàng) vào bảngThê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’;4. DML - Lệnh INSERT84. DML - Lệnh DELETE - Lệnh UPDATELệnh Delete: Xoá dữ liệu (bản ghi, hàng) trong bảngXoá 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;9Truy vấn (xem) dữ liệu trong các bảng, viewCá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ấnFROM: Danh sách các bảng hoặc view để lấy dữ liệuWHERE: Điều kiện lựa chọn các hàng trong bảngGROUP BY: Nhóm các hàng dữ liệu theo loạiHAVING: Đ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 được4. DML - Lệnh SELECT10Thứ tự xử lý các mệnh đề trong lệnh SELECT11Tìm những sản phẩm có giá 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” 4.(tt)Lệnh SELECT dùng toán tử luận lý14Sắ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, DESCSELECT CUSTOMER_NAME, CITY, STATEFROM CUSTOMER_VWHERE 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 OR4. (tt) Lệnh SELECT dùng ORDER BY15Dùng với các hàm tập hợpSELECT STATE, COUNT(STATE) FROM CUSTOMER_VGROUP BY STATE;4. (tt) Lệnh SELECT dùng GROUP BYMuố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 16Chỉ 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_VGROUP BY STATEHAVING COUNT(STATE) > 1;4. (tt) Lệnh SELECT dùng HAVING 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ẽ17Cho biết mọi thông tin cần thiết để xuất hoá đơn có số 1006SELECT 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_TWHERE 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;Phép kết 4 bảng dữ liệuMỗ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 đề WHERE5. Phép kết nhiều bảng18Subquery = đặ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àiCorrelated – 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ử EXISTS6. Subquery – Truy vấn con19Cho biết các khách hàng nào có hoá đơnSELECT CUSTOMER_NAME FROM CUSTOMER_TWHERE 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ài6. Subquery – Ví dụ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 subquery20Quá trình xử lý một non-correlated subquerySubquery được thực thi chỉ một lần21Cho biết các hoá đơn có sản phẩm được hoàn tất trong natural ashSELECT DISTINCT ORDER_ID FROM ORDER_LINE_TWHERE 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àiEXISTS cho giá trị TRUE nếu kết quả của subquery khác rỗng, ngoài ra là FALSE6. (tt) Thí dụ correlated subquery22Quá trình xử lý một correlated subquerySubquery thực thi đối với mỗi hàng dữ liệu từ câu truy vấn ngoài23Cho biết các sản phẩm có giá cao hơn giá trung bình SELECT PRODUCT_DESCRIPTION, STANDARD_PRICE, AVGPRICEFROM(SELECT AVG(STANDARD_PRICE) AVGPRICE FROM PRODUCT_T),PRODUCT_TWHERE 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ừ subqueryCột subquery là một hàm tập hợp có tên alias, tên này được dùng trong câu truy vấn ngoàiSubquery tạo nên bảng dẫn xuất được dùng trong mệnh đề FROM câu truy vấn ngoài6. (tt)Kết quả subquery làm bảng24