›
›

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

ppt 96 trang phuongnguyen 5710
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Cơ sở dữ liệu - Bài 2: Ngôn ngữ truy vấn SQL", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pptbai_giang_co_so_du_lieu_bai_2_ngon_ngu_truy_van_sql.ppt

Nội dung text: Bài giảng Cơ sở dữ liệu - Bài 2: Ngôn ngữ truy vấn SQL

  1. NGÔN NGỮ TRUY VẤN SQL
  2. Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu Cập nhật dữ liệu Truy vấn dữ liệu Một số hàm thông dụng 2
  3. Giới thiệu SQL (Structured Query Language) - Ngôn ngữ cấp cao - Người sử dụng chỉ cần đưa ra nội dung cần truy vấn - Được phát triển bởi IBM (1970s), được gọi là SEQUEL (Structured English Query Language) - Được ANSI công nhận và phát triển thành chuẩn  SQL-86  SQL-92  SQL-99 3
  4. Giới thiệu (tt) SQL gồm các câu lệnh cho phép - Định nghĩa dữ liệu DDL (Data Definition Language) - Thao tác dữ liệu DML (Data Manipulation Language) - Ràng buộc toàn vẹn - Định nghĩa khung nhìn - Phân quyền và bảo mật - SQL sử dụng thuật ngữ - Bảng ~ quan hệ - Cột ~ thuộc tính - Dòng ~ bộ 4
  5. Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu - Kiểu dữ liệu - Các lệnh định nghĩa dữ liệu Truy vấn dữ liệu Cập nhật dữ liệu Một số hàm thông dụng 5
  6. Định nghĩa dữ liệu Là ngôn ngữ mô tả - Lược đồ cho mỗi quan hệ - Miền giá trị tương ứng của từng thuộc tính - Ràng buộc toàn vẹn - Chỉ mục trên mỗi quan hệ Gồm - CREATE TABLE (tạo bảng) - DROP TABLE (xóa bảng) - ALTER TABLE (sửa bảng) - CREATE DATABASE - 6
  7. Kiểu dữ liệu Số - SMALLINT - INT - NUMERIC - DECIMAL - REAL - FLOAT - . 7
  8. Kiểu dữ liệu (tt) Chuỗi ký tự - CHAR, VARCHAR - NCHAR, NVARCHAR (gõ dấu tiếng Việt Unicode) - Chuỗi bit - BIT - BITINT - Ngày giờ - DATETIME - SMALLDATETIME 8
  9. Lệnh tạo bảng Để định nghĩa một bảng - Tên bảng - Các thuộc tính  Tên thuộc tính  Kiểu dữ liệu  Các RBTV trên thuộc tính Cú pháp CREATE TABLE ( [ ], [ ], [ ] ) 9
  10. Ví dụ - Tạo bảng CREATE TABLE NHANVIEN ( HONV NVARCHAR (20), TENLOT NVARCHAR (50), TENNV NVARCHAR (20), MANV NVARCHAR (20), NGSINH SMALLDATETIME, DCHI NVARCHAR (50), PHAI NVARCHAR (10), LUONG INT, MA_NQL NVARCHAR (20), PHG INT ) 10
  11. Lệnh tạo bảng (tt) - NOT NULL - UNIQUE - DEFAULT - PRIMARY KEY - FOREIGN KEY / REFERENCES - CHECK Đặt tên cho RBTV CONSTRAINT 11
  12. Ví dụ - Tạo bảng có RBTV CREATE TABLE NHANVIEN ( HONV NVARCHAR (20) NOT NULL, TENLOT NVARCHAR (50) NOT NULL, TENNV NVARCHAR (20) NOT NULL, MANV NVARCHAR (20) PRIMARY KEY, NGSINH SMALLDATETIME, DCHI NVARCHAR(50), PHAI NVARCHAR(10) CHECK (PHAI IN (‘Nam’, ‘Nu’)), LUONG INT DEFAULT (1000000), MA_NQL NVARCHAR(20), PHG INT ) 12
  13. Ví dụ - Tạo bảng có RBTV (tt) CREATE TABLE PHONGBAN ( TENPHG NVARCHAR(40) UNIQUE, MAPHG INT PRIMARY KEY, TRPHG NVARCHAR(20), NG_NHANCHUC SMALLDATETIME DEFAULT (GETDATE()) ) CREATE TABLE DIADIEM_PHG( MAPHG INT NOT NULL, DIADIEM NVARCHAR(50) NOT NULL, CONSTRAINT PK_DIADIEM_PHG PRIMARY KEY (MAPHG , DIADIEM) ) 13
  14. Ví dụ - Tạo bảng có RBTV (tt) CREATE TABLE DEAN ( TENDA NVARCHAR(40) UNIQUE, MADA INT PRIMARY KEY, DDIEM_DA NVARCHAR(50), PHONG INT ) CREATE TABLE PHANCONG ( MA_NVIEN NVARCHAR(20) FOREIGN KEY (MA_NVIEN) REFERENCES NHANVIEN(MANV), SODA INT FOREIGN KEY (SODA) REFERENCES DEAN(MADA), THOIGIAN NUMERIC(3,1) ) 14
  15. Ví dụ - Đặt tên cho RBTV CREATE TABLE NHANVIEN ( HONV NVARCHAR(20) CONSTRAINT NV_HONV_NN NOT NULL, TENLOT NVARCHAR(50) NOT NULL, TENNV NVARCHAR(20) NOT NULL, MANV NVARCHAR(20) CONSTRAINT NV_MANV_PK PRIMARY KEY, NGSINH SMALLDATETIME, DCHI NVARCHAR(50), PHAI NVARCHAR(10) CONSTRAINT NV_PHAI_CHK CHECK (PHAI IN (‘Nam’, ‘Nu’)), LUONG INT CONSTRAINT NV_LUONG_DF DEFAULT (10000), MA_NQL NVARCHAR(20), PHG INT ) 15
  16. Ví dụ - Đặt tên cho RBTV (tt) CREATE TABLE PHANCONG ( MA_NVIEN NVARCHAR(20), SODA INT, THOIGIAN NUMERIC(3,1), CONSTRAINT PC_MANVIEN_SODA_PK PRIMARY KEY (MA_NVIEN, SODA), CONSTRAINT PC_MANVIEN_FK FOREIGN KEY (MA_NVIEN) REFERENCES NHANVIEN(MANV), CONSTRAINT PC_SODA_FK FOREIGN KEY (SODA) REFERENCES DEAN(MADA) ) 16
  17. Lệnh sửa bảng Dùng để: thay đổi cấu trúc bảng, thay đổi RBTV Thêm cột ALTER TABLE ADD [ ] - Ví dụ ALTER TABLE NHANVIEN ADD NGHENGHIEP NVARCHAR(50) Xóa cột ALTER TABLE DROP COLUMN - Ví dụ ALTER TABLE NHANVIEN DROP COLUMN NGHENGHIEP 17
  18. Lệnh sửa bảng (tt) Hiệu chỉnh cột ALTER TABLE ALTER COLUMN - Ví dụ ALTER TABLE NHANVIEN ALTER COLUMN NGHENGHIEP NVARCHAR(70) 18
  19. Lệnh sửa bảng (tt) Thêm RBTV ALTER TABLE ADD CONSTRAINT , CONSTRAINT , - Ví dụ ALTER TABLE PHONGBAN ADD CONSTRAINT PB_MAPHG_PK PRIMARY KEY (MAPHG), CONSTRAINT PB_TRPHG FOREIGN KEY (TRPHG) REFERENCES NHANVIEN(MANV), CONSTRAINT PB_NGNHANCHUC_DF DEFAULT (GETDATE()) FOR NG_NHANCHUC, CONSTRAINT PB_TENPB_UNI UNIQUE (TENPB) 19
  20. Lệnh sửa bảng (tt) Xem các RBTV SP_HELPCONSTRAINT - Ví dụ SP_HELPCONSTRAINT PHONGBAN Xóa RBTV ALTER TABLE DROP - Ví dụ ALTER TABLE PHONGBAN DROP PB_MAPHG_PK ALTER TABLE PHONGBAN DROP PB_TRPHG ALTER TABLE PHONGBAN DROP PB_NGNHANCHUC_DF ALTER TABLE PHONGBAN DROP PB_TENPB_UNI 20
  21. Lệnh xóa bảng Được dùng để xóa cấu trúc bảng - Tất cả dữ liệu của bảng cũng bị xóa Cú pháp DROP TABLE Ví dụ DROP TABLE NHANVIEN DROP TABLE PHONGBAN DROP TABLE PHANCONG 21
  22. Lệnh xóa bảng (tt) NHANVIEN HONV TENLOT TENNV MANV NGSINH DCHI PHAI LUONG MA_NQL PHG PHONGBAN TENPHG MAPHG TRPHG NG_NHANCHUC 22
  23. Lệnh tạo kiểu dữ liệu mới Tạo ra một kiểu dữ liệu mới kế thừa những kiểu dữ liệu có sẵn Cú pháp CREATE TYPE FROM - Ví dụ CREATE TYPE Kieu_Ten FROM NVARCHAR(30) Xóa kiểu dữ liệu tự định nghĩa DROP TYPE - Ví dụ DROP TYPE Kieu_Ten 23
  24. Sử dụng DEFAULT và RULE Default và Rule là đối tượng có thể kết với một hoặc nhiều cột - Tạo Default CREATE DEFAULT AS - Ràng buộc Default vào cột sp_bindefault , ‘ ’ - Gỡ bỏ Default khỏi cột sp_unbindefault ‘ ’ - Ví dụ CREATE DEFAULT NV_LUONG_DF AS 10000 sp_bindefault NV_LUONG_DF, ‘NHANVIEN.LUONG’ sp_unbindefault ‘NHANVIEN.LUONG’ 24
  25. Sử dụng DEFAULT và RULE (tt) - Tạo Rule CREATE RULE AS - Ràng buộc Rule vào cột sp_bindrule , ‘ ’ - Gỡ bỏ Rule khỏi cột sp_unbindrule ‘ ’ - Ví dụ CREATE RULE R_LUONG AS @LUONG>=10000 sp_bindrule R_LUONG , ‘NHANVIEN.LUONG’ sp_unbindrule ‘NHANVIEN.LUONG’ - Xóa Defaule, Rule DROP DEFAULT DROP DEFAULT NV_LUONG_DF DROP RULE DROP RULE R_LUONG 25
  26. Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu Cập nhật dữ liệu - Thêm (insert) - Xóa (delete) - Sửa (update) Truy vấn dữ liệu Một số hàm thông dụng 26
  27. Lệnh INSERT Dùng để thêm 1 hay nhiều dòng vào bảng Để thêm dữ liệu - Tên bảng - Danh sách các thuộc tính cần thêm dữ liệu - Danh sách các giá trị tương ứng Cú pháp (thêm 1 dòng) INSERT INTO [( )] VALUES ( ) 27
  28. Ví dụ INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV) VALUES (N’Nguyễn’, N’Trọng’, N’Hòa’, ‘123’) INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI) VALUES ( N’Nguyễn’, N’Thanh’, N’Tùng’, ‘333’, NULL) INSERT INTO NHANVIEN VALUES (N’Trần’, N’Thanh’, N’Tâm’, ‘453’, ’7/31/1962’, N’Mai Thị Lựu’, ‘Nam’, 25000,’333’,5) 28
  29. Lệnh INSERT (tt) Nhận xét - Thứ tự các giá trị phải trùng với thứ tự các cột - Có thể thêm giá trị NULL ở những thuộc tính không là khóa chính và cho phép NULL - Câu lệnh INSERT sẽ gặp lỗi nếu vi phạm RBTV  Khóa chính  Tham chiếu  NOT NULL - các thuộc tính có ràng buộc NOT NULL bắt buộc phải có giá trị 29
  30. Lệnh INSERT (tt) Cú pháp (thêm nhiều dòng) INSERT INTO [( )] 30
  31. Ví dụ CREATE TABLE THONGKE_PB ( TENPHG NVARCHAR(20), SL_NV INT, LUONG_TC INT ) INSERT INTO THONGKE_PB(TENPHG, SL_NV, LUONG_TC) SELECT TENPHG, COUNT(MANV), SUM(LUONG) FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG 31
  32. Lệnh DELETE Dùng để xóa các dòng của bảng Cú pháp DELETE FROM [WHERE ] 32
  33. Ví dụ DELETE FROM NHANVIEN WHERE HONV=‘Tran’ DELETE FROM NHANVIEN WHERE MANV=‘333’ DELETE FROM NHANVIEN 33
  34. Lệnh DELETE (tt) Nhận xét - Số lượng số dòng bị xóa phụ thuộc vào điều kiện ở mệnh đề WHERE - Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất cả các dòng trong bảng sẽ bị xóa - Lệnh DELETE có thể gây ra vi phạm RB tham chiếu  Không cho xóa  Xóa luôn những dòng có giá trị đang tham chiếu đến CASCADE  Đặt NULL cho những giá trị tham chiếu 34
  35. Lệnh DELETE (tt) MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 5 453453453 Tran Thanh Tam 07/31/1972 543 MTL Q1 Nu 25000 333445555 5 999887777 Bui Ngoc Hang 07/19/1968 33 NTH Q1 Nu 38000 987654321 4 987654321 Le Quynh Nhu 07620/1951 219 TD Q3 Nu 43000 888665555 4 987987987 Tran Hong Quang 04/08/1969 980 LHP Q5 Nam 25000 987654321 4 888665555 Pham Van Vinh 11/10/1945 450 TV HN Nam 55000 NULL 1 MA_NVIEN SODA THOIGIAN 333445555 10 10.0 888665555 20 20.0 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 453453453 1 20.0 35
  36. Lệnh DELETE (tt) TENPHG MAPHG MA_NVIEN NG_NHANCHUC Nghien cuu 5 333445555 05/22/1988 Dieu hanh 4 987987987 01/01/1995 Quan ly 1 888665555 06/19/1981 MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 NULL5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 NULL5 453453453 Tran Thanh Tam 07/31/1972 543 MTL Q1 Nu 25000 333445555 NULL5 999887777 Bui Ngoc Hang 07/19/1968 33 NTH Q1 Nu 38000 987654321 4 987654321 Le Quynh Nhu 07620/1951 219 TD Q3 Nu 43000 888665555 4 987987987 Tran Hong Quang 04/08/1969 980 LHP Q5 Nam 25000 987654321 4 888665555 Pham Van Vinh 11/10/1945 450 TV HN Nam 55000 NULL 1 36
  37. Lệnh UPDATE Dùng để thay đổi giá trị của thuộc tính cho các dòng của bảng Cú pháp UPDATE SET = , = , [WHERE ] 37
  38. Ví dụ UPDATE NHANVIEN SET NGSINH=’08/12/1965’ WHERE MANV=‘333445555’ UPDATE NHANVIEN SET LUONG=LUONG*1.1 38
  39. Ví dụ Với đề án có mã số 10, hãy thay đổi nơi thực hiện đề án thành ‘Vung Tau’ và phòng ban phụ trách là phòng 5 UPDATE DEAN SET DIADIEM_DA=’Vung Tau’, PHONG=5 WHERE MADA=10 39
  40. Lệnh UPDATE Nhận xét - Những dòng thỏa điều kiện tại mệnh đề WHERE sẽ được cập nhật giá trị mới - Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất cả các dòng trong bảng sẽ bị cập nhật - Lệnh UPDATE có thể gây ra vi phạm RB tham chiếu  Không cho sửa  Sửa luôn những dòng có giá trị đang tham chiếu đến CASCADE 40
  41. Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu Cập nhật dữ liệu Truy vấn dữ liệu - Truy vấn cơ bản - Tập hợp, so sánh tập hợp và truy vấn lồng - Hàm kết hợp và gom nhóm - Một số kiểu truy vấn khác Một số hàm thông dụng 41
  42. Truy vấn dữ liệu Là ngôn ngữ rút trích dữ liệu thỏa một số điều kiện nào đó Dựa trên Phép toán ĐSQH + Một số bổ sung - Cho phép 1 bảng có nhiều dòng trùng nhau - Bảng là bag quan hệ là set 42
  43. Truy vấn cơ bản Gồm 3 mệnh đề SELECT FROM WHERE -  Tên các cột cần được hiển thị trong kết quả truy vấn -  Tên các bảng liên quan đến câu truy vấn -  Phép toán: +, -, *, /, %  Phép so sánh: =, , >= , <>, !=  Phép toán logic: and, or, not  Phép toán tập hợp: all, any, in, like, between, exists 43
  44. Ví dụ Lấy tất cả các cột của quan hệ kết quả SELECT * FROM NHANVIEN WHERE PHG=5 MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 5 44
  45. Mệnh đề SELECT SELECT MANV, HONV, TENLOT, TENNV FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HONV TENLOT TENNV 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung 45
  46. Mệnh đề SELECT (tt) Tên bí danh SELECT MANV, HONV AS HO, TENLOT AS ‘TEN LOT’, TENNV AS TEN FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HO TEN LOT TEN 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung 46
  47. Mệnh đề SELECT (tt) Mở rộng SELECT MANV, HONV + ‘ ’ + TENLOT + ‘ ’ + TENNV AS ‘HO TEN’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HO TEN 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung 47
  48. Mệnh đề SELECT (tt) Mở rộng SELECT MANV, LUONG*1.1 AS ‘LUONG10%’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV LUONG10% 333445555 33000 987987987 27500 48
  49. Mệnh đề SELECT (tt) Loại bỏ các dòng trùng nhau SELECT DISTINCTLUONG LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ LUONG 30000 25000 2500038000 38000 49
  50. Mệnh đề WHERE SELECT MANV, TENNV Biểu thức luận lý FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG TRUE TRUE 50
  51. Mệnh đề WHERE (tt) Độ ưu tiên SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) AND PHG=MAPHG 51
  52. Mệnh đề WHERE (tt) BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>=20000 AND LUONG<=30000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 30000 52
  53. Mệnh đề WHERE (tt) NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 30000 53
  54. Mệnh đề WHERE (tt) LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen _ _ _ _’ SELECT MANV, TENNV Ký tự bất kỳ FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen %’ Chuỗi bất kỳ 54
  55. Mệnh đề WHERE (tt) NOT LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE ‘Nguyen’ SELECT MANV, TENNV FROM NHANVIEN WHERE HONV NOT NOT LIKE ‘Nguyen’ 55
  56. Mệnh đề WHERE (tt) Ngày giờ SELECT MANV, TENNV FROM NHANVIEN WHERE NGSINH BETWEEN ’12/8/1955’ AND ‘7/19/1966’ 56
  57. Mệnh đề WHERE (tt) NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOT NULL 57
  58. Mệnh đề FROM Không sử dụng mệnh đề WHERE SELECT MANV, MAPHG FROM NHANVIEN, PHONGBAN WHERE TRUE MANV MAPHG 333445555 1 333445555 4 333445555 5 987987987 1 987987987 4 987987987 5 58
  59. Mệnh đề FROM (tt) Tên bí danh SELECT TENPHG, DIADIEM FROM PHONGBAN,PHONGBAN ASDDIEM_PHGPB, DDIEM_PHG AS DD WHERE MAPHG=MAPHGPB.MAPHG=DD.MAPHG SELECT TENNV, NGSINH,NV.NGSINH, TENTN, TENTN, NGSINH TN.NGSINH FROM NHANVIEN,NHANVIEN NV,THANNHAN THANNHAN TN WHERE MANV=MA_NVIEN 59
  60. Mệnh đề ORDER BY Dùng để hiển thị kết quả câu truy vấn theo một thứ tự nào đó Cú pháp SELECT FROM WHERE ORDER BY - ASC: tăng (mặc định) - DESC: giảm 60
  61. Mệnh đề ORDER BY (tt) Ví dụ SELECT MA_NVIEN, SODA FROM PHANCONG ORDER BY MA_NVIEN DESC, SODA MA_NVIEN SODA 999887777 10 999887777 30 987987987 10 987987987 30 987654321 10 987654321 20 987654321 30 61
  62. Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu Cập nhật dữ liệu Truy vấn dữ liệu - Truy vấn cơ bản - Tập hợp, so sánh tập hợp và truy vấn lồng - Hàm kết hợp và gom nhóm - Một số dạng truy vấn khác Một số hàm thông dụng 62
  63. Phép toán tập hợp trong SQL SQL có cài đặt các phép toán - Hội (UNION) - Giao (INTERSECT) - Trừ (EXCEPT) Kết quả trả về là tập hợp - Loại bỏ các bộ trùng nhau - Để giữ lại các bộ trùng nhau  UNION ALL  INTERSECT ALL  EXCEPT ALL 63
  64. Phép toán tập hợp trong SQL (tt) Cú pháp SELECT FROM WHERE UNION [ALL] SELECT FROM WHERE SELECT FROM WHERE INTERSECT [ALL] SELECT FROM WHERE SELECT FROM WHERE EXCEPT [ALL] SELECT FROM WHERE 64
  65. Phép toán tập hợp trong SQL (tt) SELECT * SELECT * FROM NHANVIEN FROM NHANVIEN WHERE PHG = 4 AND PHAI=‘Nu’ WHERE PHG = 4 UNION INTERSECT SELECT * SELECT * FROM NHANVIEN FROM NHANVIEN WHERE PHG = 5 AND PHAI=‘Nam’ WHERE PHAI=‘Nam’ SELECT * FROM NHANVIEN EXCEPT SELECT * FROM NHANVIEN WHERE PHAI=‘Nam’ AND PHG = 4 65
  66. Truy vấn lồng SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG SELECT Câu truy vấn cha (Outer query) FROM WHERE ( SELECT Câu truy vấn con FROM (Subquery) WHERE ) 66
  67. Truy vấn lồng (tt) Các câu lệnh SELECT có thể lồng nhau ở nhiều mức Câu truy vấn con thường trả về một tập các giá trị Các câu truy vấn con trong cùng một mệnh đề WHERE được kết hợp bằng phép nối logic Mệnh đề WHERE của câu truy vấn cha - - So sánh tập hợp thường đi cùng với một số toán tử  IN, NOT IN  ALL  ANY hoặc SOME - Kiểm tra sự tồn tại  EXISTS  NOT EXISTS 67
  68. Truy vấn lồng (tt) Có 2 loại truy vấn lồng - Lồng phân cấp  Mệnh đề WHERE của truy vấn con không tham chiếu đến thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha  Khi thực hiện, câu truy vấn con sẽ được thực hiện trước - Lồng tương quan  Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha  Khi thực hiện, câu truy vấn con sẽ được thực hiện nhiều lần, mỗi lần tương ứng với một bộ của truy vấn cha 68
  69. Ví dụ - Lồng phân cấp SELECT MANV, TENNV FROM NHANVIEN, DIADIEM_PHG WHERE DIADIEM=‘TP HCM’ AND PHG=MAPHG SELECT MANV, TENNV FROM NHANVIEN WHERE PHG IN ((1, 5) SELECT MAPHG FROM DIADIEM_PHG WHERE DIADIEM=‘TP HCM’ ) 69
  70. Ví dụ - Lồng tương quan SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG SELECT MANV, TENNV FROM NHANVIEN WHERE EXISTS ( SELECT * FROM PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG ) 70
  71. Nhận xét IN và EXISTS IN - IN - Thuộc tính ở mệnh đề SELECT của truy vấn con phải có cùng kiểu dữ liệu với thuộc tính ở mệnh đề WHERE của truy vấn cha EXISTS - Không cần có thuộc tính, hằng số hay biểu thức nào khác đứng trước - Không nhất thiết liệt kê tên thuộc tính ở mệnh đề SELECT của truy vấn con - Những câu truy vấn có = ANY hay IN đều có thể chuyển thành câu truy vấn có EXISTS 71
  72. Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu Cập nhật dữ liệu Truy vấn dữ liệu - Truy vấn cơ bản - Tập hợp, so sánh tập hợp và truy vấn lồng - Hàm kết hợp và gom nhóm - Một số dạng truy vấn khác Một số hàm thông dụng 72
  73. Hàm kết hợp COUNT - COUNT(*) đếm số dòng - COUNT( ) đếm số giá trị khác NULL của thuộc tính - COUNT(DISTINCT ) đếm số giá trị khác nhau và khác NULL của thuộc tính MIN MAX SUM AVG Các hàm kết hợp được đặt ở mệnh đề SELECT 73
  74. Ví dụ Cho biết số lượng nhân viên của từng phòng ban PHG SL_NV 5 3 4 3 1 1 MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 5 453453453 Tran Thanh Tam 07/31/1972 543 MTL Q1 Nu 25000 333445555 5 999887777 Bui Ngoc Hang 07/19/1968 33 NTH Q1 Nu 38000 987654321 4 987654321 Le Quynh Nhu 07620/1951 219 TD Q3 Nu 43000 888665555 4 987987987 Tran Hong Quang 04/08/1969 980 LHP Q5 Nam 25000 987654321 4 888665555 Pham Van Vinh 11/10/1945 450 TV HN Nam 55000 NULL 1 74
  75. Gom nhóm Cú pháp SELECT FROM WHERE GROUP BY Sau khi gom nhóm - Mỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom nhóm 75
  76. Ví dụ Với mỗi nhân viên cho biết mã số, họ tên, số lượng đề án và tổng thời gian mà họ tham gia MA_NVIEN SODA THOIGIAN 123456789 1 32.5 123456789 2 7.5 333445555 2 10.0 333445555 3 10.0 333445555 10 10.0 888665555 20 20.0 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 987654321 20 15.0 453453453 1 20.0 453453453 2 20.0 76
  77. Ví dụ Cho biết những nhân viên tham gia từ 2 đề án trở lên MA_NVIEN SODA THOIGIAN 123456789 1 32.5 123456789 2 7.5 333445555 2 10.0 333445555 3 10.0 333445555 10 10.0 888665555 20 20.0 bị loại ra 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 987654321 20 15.0 453453453 1 20.0 453453453 2 20.0 77
  78. Điều kiện trên nhóm Cú pháp SELECT FROM WHERE GROUP BY HAVING 78
  79. Nhận xét Mệnh đề GROUP BY - Các thuộc tính trong mệnh đề SELECT (trừ những thuộc tính trong các hàm kết hợp) phải xuất hiện trong mệnh đề GROUP BY Mệnh đề HAVING - Sử dụng các hàm kết hợp trong mệnh đề SELECT để kiểm tra một số điều kiện nào đó - Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc trên từng bộ - Sau khi gom nhóm điều kiện trên nhóm mới được thực hiện 79
  80. Nhận xét (tt) Thứ tự thực hiện câu truy vấn có mệnh đề GROUP BY và HAVING - (1) Chọn ra những dòng thỏa điều kiện trong mệnh đề WHERE - (2) Những dòng này sẽ được gom thành nhiều nhóm tương ứng với mệnh đề GROUP BY - (3) Áp dụng các hàm kết hợp cho mỗi nhóm - (4) Bỏ qua những nhóm không thỏa điều kiện trong mệnh đề HAVING - (5) Rút trích các giá trị của các cột và hàm kết hợp trong mệnh đề SELECT 80
  81. Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu Cập nhật dữ liệu Truy vấn dữ liệu - Truy vấn cơ bản - Tập hợp, so sánh tập hợp và truy vấn lồng - Hàm kết hợp và gom nhóm - Một số dạng truy vấn khác Một số hàm thông dụng 81
  82. Một số dạng truy vấn khác Truy vấn con ở mệnh đề FROM Điều kiện kết ở mệnh đề FROM - Phép kết tự nhiên - Phép kết ngoàI Cấu trúc CASE SELECT INTO 82
  83. Truy vấn con ở mệnh đề FROM Kết quả trả về của một câu truy vấn phụ là một bảng - Bảng trung gian trong quá trình truy vấn - Không có lưu trữ thật sự Cú pháp SELECT FROM R1, R2, ( ) AS tên_bảng WHERE Ví dụ SELECT MANV, TENNV FROM NHANVIEN, (SELECT MAPHG FROM PHONGBAN WHERE TENPHG= ‘Nghien cuu’) AS B WHERE PHG = MAPHG 83
  84. Điều kiện kết ở mệnh đề FROM Kết bằng SELECT FROM R1 [INNER] JOIN R2 ON WHERE Ví dụ SELECT MANV, TENNV FROM NHANVIEN INNER JOIN PHONGBAN ON PHG = MAPHG WHERE TENPHG= ‘Nghien cuu’ 84
  85. Điều kiện kết ở mệnh đề FROM Kết ngoài SELECT FROM R1 LEFT|RIGHT|FULL JOIN R2 ON WHERE Ví dụ SELECT MANV, TENNV FROM NHANVIEN LEFT JOIN PHONGBAN ON PHG = MAPHG 85
  86. Cấu trúc CASE Cho phép kiểm tra điều kiện và xuất thông tin theo từng trường hợp Cú pháp CASE WHEN THEN WHEN THEN [ELSE ] END 86
  87. Cấu trúc CASE (tt) SELECT MANV, TENNV , PHONG= CASE PHG WHEN 1 THEN N'MộT' WHEN 2 THEN N'HAI' WHEN 3 THEN N'BA' WHEN 4 THEN N'BốN' WHEN 5 THEN N'NĂM' END FROM NHANVIEN 87
  88. Select into Dùng để tạo ra một bảng mới và đưa dữ liệu vào bảng mới SELECT [INTO ] FROM [WHERE ] Ví dụ: tạo bảng gồm những nhân viên nam SELECT * INTO NHANVIEN_NAM FROM NHANVIEN WHERE PHAI=‘NAM’ 88
  89. Kết luận SELECT [INTO ] FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] 89
  90. Nội dung chi tiết Giới thiệu Định nghĩa dữ liệu - Kiểu dữ liệu - Các lệnh định nghĩa dữ liệu Truy vấn dữ liệu Cập nhật dữ liệu Một số hàm thông dụng - Hàm toán học - Hàm chuỗi - Hàm ngày tháng - Hàm chuyển đổi kiểu 90
  91. Hàm toán học Hàm Diễn giải Ví dụ Kết quả Power(X,Y) Tính X lũy thừa Y Select Power(2,5) 32 Round(X,n) Làm tròn X còn n số lẻ Round(123.4567,2) 123.46 Square(X) Tính bình phương của X Square(5) 25 SQRT(X) Tính căn bậc 2 của X SQRT(16) 4 Sum(cột) Tính tổng cột Sum(luong) Count(cột) Đếm số phần tử khác Null Count(MANV) Count(*) Đếm số dòng Count(*) Max(cột) Cho giá trị lớn nhất Max(luong) Min(cột) Cho giá trị nhỏ nhất Min(luong) Avg(cột) Tính trung bình cột Avg(luong) 91
  92. Hàm chuỗi Hàm Diễn giải Ví dụ Kết quả Ascii(C) Trả về mã Ascii của ký tự C select ASCII(‘A’) 65 Char(N) Trả về ký tự có mã Ascii là N Char(66) B Len(S) Trả về chiều dài S Len(‘abc xyz’) 7 Lower(S) Chuyển S sang chữ thường Lower(‘abcXYZ’) abcxyz Upper(S) Chuyển S sang chữ hoa Upper(‘abcXYZ’) ABCXYZ LTrim(S) Cắt khoảng trắng bên trái LTrim(‘ abc’) abc Rtrim(S) Cắt khoảng trắng bên phải RTrim(‘abc ’) abc Left(S,n) Trích n ký tự bên trái S Left(‘abcxyz’,4) abcx Right(S,n) Trích n ký tự bên phải S Right(‘abcxyz’,4) cxyz 92
  93. Hàm chuỗi (tt) Hàm Diễn giải Ví dụ Kết quả S1+S2 Nối S1 với S2 Select ‘abc’+’XYZ’ abcXYZ CharIndex Trả về vị trí đầu tiên của CharIndex 2 (S1,S) S1xuất hiện trong S (’bc’,‘abcxyzabc’) SubString Trích n ký tự từ vị trí p của S SubString bcxy (S,p,n) (‘abcxyz’,2,4) Replace Thay tất cả S2 trong S1 bằng Replace xyxyc (S1,S2,S3) S3 (‘ababc’,’ab’,’xy’) Reverse(S) Trả về chuỗi đảo ngược S Reverse(‘abcxyz’) zyxcba 93
  94. Hàm ngày tháng Các hàm có sử dụng tham số DatePart, giá trị của DatePart được cho như bảng sau DatePart Giá trị Diễn giải DD 1-31 Ngày trong tháng MM 1-12 Tháng trong năm QQ 1-4 Quý trong năm DW 1-7 (Sun-Sat) Thứ trong tuần YY 1753-9999 Năm 94
  95. Hàm ngày tháng (tt) Hàm Diễn giải Ví dụ Kết quả GetDate() Trả về ngày giờ Select GetDate() 2007-06-18 hiện hành 08:34:44.107 Day(date) Trích phần ngày Day(GetDate()) 18 Month(date) Trích phần tháng Month(Getdate()) 6 Year(date) Trích phần năm Year(GetDate()) 2007 DatePart Trích DatePart DatePart(mm, 6 (DatePart,date) của date GetDate()) DateAdd Thêm n DatePart DateAdd (dd,-2, 2007-06-16 (DatePart, n,date) vào date Getdate()) DateDiff(DatePart, Trả về số DateDiff(mm, date1,date2) DatePart giữa 2 ‘2007-02-16’, 4 ngày Getdate()) 95
  96. Hàm chuyển đổi kiểu Hàm Diễn giải Ví dụ Kết quả Cast(exp AS Chuyển giá trị Cast(‘123’ AS Int) 123 data_type) exp sang kiểu Cast(123 AS ‘123’ data_type Varchar(10)) Convert(data_type, Chuyển giá trị Convert(Varchar(20), 18/06/2007 exp [,style]) exp sang kiểu GetDate(),103) data_type theo Convert(Varchar(20), 06/18/2007 style GetDate(),101) 96