Bài giảng SQL - Chương 2: Ngôn ngữ truy vấn SQL

NGÔN NGỮ TRUY VẤN SQLNội dung chi tiếtGiớ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ụng2Giới thiệuSQL (Structured Query Language)Ngôn ngữ cấp caoNgườ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ẩnSQL-86SQL-92SQL-993Giớ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ìnPhân quyền và bảo mậtSQL sử dụng thuật ngữBảng ~ quan hệCột ~ thuộc tínhDòng ~ bộ4Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuKiểu dữ liệuCác lệnh định nghĩa dữ liệuTruy vấn dữ liệu Cập nhật dữ liệu Một số hàm thông dụng5Định nghĩa dữ liệuLà 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ínhRàng buộc toàn vẹnChỉ mục trên mỗi quan hệ GồmCREATE TABLE (tạo bảng)DROP TABLE (xóa bảng)ALTER TABLE (sửa bảng)CREATE DATABASE6Kiểu dữ liệuSốSMALLINTINTNUMERICDECIMALREALFLOAT.7Kiểu dữ liệu (tt)Chuỗi ký tựCHAR, VARCHARNCHAR, NVARCHAR (gõ dấu tiếng Việt Unicode)Chuỗi bitBITBITINTNgày giờDATETIME SMALLDATETIME8Lệnh tạo bảngĐể định nghĩa một bảngTên bảngCác thuộc tínhTên thuộc tínhKiểu dữ liệuCác RBTV trên thuộc tínhCú phápCREATE TABLE ( [], [],  [])9Ví dụ - Tạo bảngCREATE 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)10Lệnh tạo bảng (tt) NOT NULLUNIQUEDEFAULTPRIMARY KEYFOREIGN KEY / REFERENCESCHECKĐặt tên cho RBTVCONSTRAINT 11Ví dụ - Tạo bảng có RBTVCREATE 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)12Ví 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)) 13Ví 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))14Ví dụ - Đặt tên cho RBTVCREATE 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)15Ví 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))16Lệnh sửa bảngDùng để: thay đổi cấu trúc bảng, thay đổi RBTVThêm cộtVí dụXóa cộtVí dụALTER TABLE ADD []ALTER TABLE NHANVIEN ADD NGHENGHIEP NVARCHAR(50)ALTER TABLE DROP COLUMN ALTER TABLE NHANVIEN DROP COLUMN NGHENGHIEP17Lệnh sửa bảng (tt)Hiệu chỉnh cộtVí dụALTER TABLE NHANVIEN ALTER COLUMN NGHENGHIEP NVARCHAR(70)ALTER TABLE ALTER COLUMN 18Lệnh sửa bảng (tt)Thêm RBTVVí dụALTER TABLE ADD CONSTRAINT , CONSTRAINT , 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)19Lệnh sửa bảng (tt)Xem các RBTVVí dụXóa RBTVVí dụALTER TABLE DROP SP_HELPCONSTRAINT SP_HELPCONSTRAINT PHONGBANALTER TABLE PHONGBAN DROP PB_MAPHG_PK ALTER TABLE PHONGBAN DROP PB_TRPHGALTER TABLE PHONGBAN DROP PB_NGNHANCHUC_DFALTER TABLE PHONGBAN DROP PB_TENPB_UNI20Lệnh xóa bảngĐược dùng để xóa cấu trúc bảngTất cả dữ liệu của bảng cũng bị xóaCú phápVí dụDROP TABLE DROP TABLE NHANVIENDROP TABLE PHONGBANDROP TABLE PHANCONG21Lệnh xóa bảng (tt)NHANVIENTENNVHONVTENLOTMANVNGSINHDCHIPHAILUONGMA_NQLPHGPHONGBANTRPHGTENPHGMAPHGNG_NHANCHUC22Lệnh tạo kiểu dữ liệu mớiTạo ra một kiểu dữ liệu mới kế thừa những kiểu dữ liệu có sẵnCú phápVí dụXóa kiểu dữ liệu tự định nghĩaVí dụCREATE TYPE FROM CREATE TYPE Kieu_Ten FROM NVARCHAR(30)DROP TYPE DROP TYPE Kieu_Ten 23Sử dụng DEFAULT và RULEDefault và Rule là đối tượng có thể kết với một hoặc nhiều cộtTạo Default Ràng buộc Default vào cộtGỡ bỏ Default khỏi cộtVí dụCREATE DEFAULT AS CREATE DEFAULT NV_LUONG_DF AS 10000sp_bindefault NV_LUONG_DF, ‘NHANVIEN.LUONG’ sp_unbindefault ‘NHANVIEN.LUONG’ sp_bindefault , ‘’sp_unbindefault ‘’24Sử dụng DEFAULT và RULE (tt)Tạo Rule Ràng buộc Rule vào cộtGỡ bỏ Rule khỏi cộtVí dụXóa Defaule, RuleCREATE RULE AS CREATE RULE R_LUONG AS @LUONG>=10000sp_bindrule R_LUONG , ‘NHANVIEN.LUONG’ sp_unbindrule ‘NHANVIEN.LUONG’ sp_bindrule , ‘’sp_unbindrule ‘’DROP DEFAULT DROP RULE DROP DEFAULT NV_LUONG_DF DROP RULE R_LUONG 25Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuCập nhật dữ liệu Thêm (insert)Xóa (delete)Sửa (update)Truy vấn dữ liệuMột số hàm thông dụng26Lệnh INSERTDùng để thêm 1 hay nhiều dòng vào bảngĐể thêm dữ liệuTên bảngDanh sách các thuộc tính cần thêm dữ liệuDanh sách các giá trị tương ứngCú pháp (thêm 1 dòng)INSERT INTO [()]VALUES ()27Ví dụINSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV)VALUES (N’Nguyễn’, N’Trọng’, N’Hòa’, ‘123’)INSERT INTO NHANVIENVALUES (N’Trần’, N’Thanh’, N’Tâm’, ‘453’, ’7/31/1962’, N’Mai Thị Lựu’, ‘Nam’, 25000,’333’,5)INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI)VALUES ( N’Nguyễn’, N’Thanh’, N’Tùng’, ‘333’, NULL)28Lệnh INSERT (tt)Nhận xétThứ tự các giá trị phải trùng với thứ tự các cộtCó thể thêm giá trị NULL ở những thuộc tính không là khóa chính và cho phép NULLCâu lệnh INSERT sẽ gặp lỗi nếu vi phạm RBTVKhóa chínhTham chiếuNOT NULL - các thuộc tính có ràng buộc NOT NULL bắt buộc phải có giá trị29Lệnh INSERT (tt)Cú pháp (thêm nhiều dòng)INSERT INTO [()] 30Ví 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 TENPHG31Lệnh DELETE Dùng để xóa các dòng của bảngCú phápDELETE FROM [WHERE ]32Ví dụDELETE FROM NHANVIENWHERE HONV=‘Tran’DELETE FROM NHANVIENWHERE MANV=‘333’DELETE FROM NHANVIEN33Lệnh DELETE (tt) Nhận xétSố lượng số dòng bị xóa phụ thuộc vào điều kiện ở mệnh đề WHERENế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óaLệnh DELETE có thể gây ra vi phạm RB tham chiếuKhông cho xóaXóa luôn những dòng có giá trị đang tham chiếu đếnCASCADEĐặt NULL cho những giá trị tham chiếu34Lệnh DELETE (tt) TENNVHONVNGSINHDCHIPHAILUONGPHGTungNguyen12/08/1955638 NVC Q5Nam400005HungNguyen09/15/1962Ba Ria VTNam380005333445555987987987MANVMA_NQL888665555333445555TENLOTThanhManhHangBui07/19/196833 NTH Q1Nu380004999887777987654321NgocNhuLe07620/1951219 TD Q3Nu430004987654321888665555QuynhVinhPham11/10/1945450 TV HNNam550001888665555NULLVanSODATHOIGIANMA_NVIEN1010.03334455552020.08886655553020.0987654321120.0453453453TamTran07/31/1972543 MTL Q1Nu250005453453453333445555ThanhQuangTran04/08/1969980 LHP Q5Nam250004987987987987654321Hong1035.0987987987305.098798798735Lệnh DELETE (tt) TENNVHONVNGSINHDCHIPHAILUONGPHGTungNguyen12/08/1955638 NVC Q5Nam40000HungNguyen09/15/1962Ba Ria VTNam38000333445555987987987MANVMA_NQL888665555333445555TENLOTThanhManhHangBui07/19/196833 NTH Q1Nu380004999887777987654321NgocNhuLe07620/1951219 TD Q3Nu430004987654321888665555QuynhVinhPham11/10/1945450 TV HNNam550001888665555NULLVanTamTran07/31/1972543 MTL Q1Nu25000555453453453333445555ThanhQuangTran04/08/1969980 LHP Q5Nam250004987987987987654321HongNULLNULLNULL05/22/1988333445555Nghien cuu5NG_NHANCHUCMA_NVIEN01/01/199506/19/1981987987987888665555TENPHGMAPHGDieu hanh4Quan ly136Lệnh UPDATE Dùng để thay đổi giá trị của thuộc tính cho các dòng của bảngCú phápUPDATE SET =, =, [WHERE ]37Ví dụUPDATE NHANVIENSET NGSINH=’08/12/1965’WHERE MANV=‘333445555’UPDATE NHANVIENSET LUONG=LUONG*1.138Ví 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 5UPDATE DEANSET DIADIEM_DA=’Vung Tau’, PHONG=5WHERE MADA=1039Lệnh UPDATE Nhận xétNhững dòng thỏa điều kiện tại mệnh đề WHERE sẽ được cập nhật giá trị mớiNế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ậtLệnh UPDATE có thể gây ra vi phạm RB tham chiếuKhông cho sửaSửa luôn những dòng có giá trị đang tham chiếu đếnCASCADE40Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuCập nhật dữ liệu Truy vấn dữ liệuTruy vấn cơ bảnTập hợp, so sánh tập hợp và truy vấn lồngHàm kết hợp và gom nhómMột số kiểu truy vấn khácMột số hàm thông dụng41Truy vấn dữ liệuLà ngôn ngữ rút trích dữ liệu thỏa một số điều kiện nào đóDựa trênCho phép 1 bảng có nhiều dòng trùng nhauBảng là bag  quan hệ là setPhép toán ĐSQHMột số bổ sung42Truy vấn cơ bảnGồm 3 mệnh đềTên các cột cần được hiển thị trong kết quả truy vấnTên các bảng liên quan đến câu truy vấnPhép toán: +, -, *, /, %Phép so sánh: =, , >= , , != Phép toán logic: and, or, notPhép toán tập hợp: all, any, in, like, between, existsSELECT FROM WHERE 43Ví dụSELECT *FROM NHANVIENWHERE PHG=5Lấy tất cả các cột của quan hệ kết quảTENNVHONVNGSINHDCHIPHAILUONGPHGTungNguyen12/08/1955638 NVC Q5Nam400005HungNguyen09/15/1962Ba Ria VTNam380005333445555987987987MANVMA_NQL888665555333445555TENLOTThanhManh44Mệnh đề SELECTSELECT MANV, HONV, TENLOT, TENNVFROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’TENNVHONVTungNguyenHungNguyenTENLOTThanhManh333445555987987987MANV45Mệnh đề SELECT (tt)SELECT MANV, HONV AS HO, TENLOT AS ‘TEN LOT’, TENNV AS TENFROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’TENHOTungNguyenHungNguyenTEN LOTThanhManh333445555987987987MANVTên bí danh46Mệnh đề SELECT (tt)SELECT MANV, HONV + ‘ ’ + TENLOT + ‘ ’ + TENNV AS ‘HO TEN’FROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’HO TENNguyen Thanh TungNguyen Manh Hung333445555987987987MANVMở rộng47Mệnh đề SELECT (tt)SELECT MANV, LUONG*1.1 AS ‘LUONG10%’FROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’LUONG10%3300027500333445555987987987MANVMở rộng48Mệnh đề SELECT (tt)SELECT LUONGFROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’Loại bỏ các dòng trùng nhauLUONG30000250002500038000LUONG300002500038000SELECT DISTINCT LUONGFROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’49Mệnh đề WHERESELECT MANV, TENNV FROM NHANVIEN, PHONGBANWHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHGBiểu thức luận lýTRUETRUE50Mệnh đề WHERE (tt)SELECT MANV, TENNV FROM NHANVIEN, PHONGBANWHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) AND PHG=MAPHGĐộ ưu tiên51Mệnh đề WHERE (tt)SELECT MANV, TENNV FROM NHANVIENWHERE LUONG>=20000 AND LUONGFROM WHERE ORDER BY 60Mệnh đề ORDER BY (tt)Ví dụSELECT MA_NVIEN, SODAFROM PHANCONGORDER BY MA_NVIEN DESC, SODASODA1030999887777999887777MA_NVIEN103098798798798765432198798798710209876543213098765432161Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuCập nhật dữ liệuTruy vấn dữ liệuTruy vấn cơ bảnTập hợp, so sánh tập hợp và truy vấn lồngHàm kết hợp và gom nhómMột số dạng truy vấn khácMột số hàm thông dụng62Phép toán tập hợp trong SQLSQL có cài đặt các phép toánHội (UNION)Giao (INTERSECT)Trừ (EXCEPT)Kết quả trả về là tập hợpLoại bỏ các bộ trùng nhauĐể giữ lại các bộ trùng nhauUNION ALLINTERSECT ALLEXCEPT ALL63Phép toán tập hợp trong SQL (tt)Cú phápSELECT FROM WHERE UNION [ALL]SELECT FROM WHERE SELECT FROM WHERE INTERSECT [ALL]SELECT FROM WHERE SELECT FROM WHERE EXCEPT [ALL]SELECT FROM WHERE 64Phép toán tập hợp trong SQL (tt)SELECT *FROM NHANVIENWHERE PHG = 4 AND PHAI=‘Nu’UNIONSELECT *FROM NHANVIENWHERE PHG = 5 AND PHAI=‘Nam’SELECT *FROM NHANVIENWHERE PHG = 4INTERSECTSELECT *FROM NHANVIENWHERE PHAI=‘Nam’SELECT * FROM NHANVIENEXCEPTSELECT * FROM NHANVIENWHERE PHAI=‘Nam’ AND PHG = 4 65Truy vấn lồngSELECT MANV, TENNV FROM NHANVIEN, PHONGBANWHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHGSELECT FROM WHERE ( SELECT FROM WHERE )Câu truy vấn cha (Outer query)Câu truy vấn con (Subquery)66Truy vấn lồng (tt)Các câu lệnh SELECT có thể lồng nhau ở nhiều mứcCâ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 logicMệ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 INALLANY hoặc SOMEKiểm tra sự tồn tạiEXISTSNOT EXISTS67Truy vấn lồng (tt)Có 2 loại truy vấn lồngLồng phân cấpMệ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 chaKhi thực hiện, câu truy vấn con sẽ được thực hiện trướcLồng tương quanMệ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 chaKhi 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 cha68Ví dụ - Lồng phân cấpSELECT MANV, TENNV FROM NHANVIENWHERE PHG IN ( SELECT MAPHG FROM DIADIEM_PHG WHERE DIADIEM=‘TP HCM’ )(1, 5)SELECT MANV, TENNV FROM NHANVIEN, DIADIEM_PHGWHERE DIADIEM=‘TP HCM’ AND PHG=MAPHG69Ví dụ - Lồng tương quanSELECT MANV, TENNV FROM NHANVIEN, PHONGBANWHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHGSELECT MANV, TENNV FROM NHANVIENWHERE EXISTS ( SELECT * FROM PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG )70Nhận xét IN và EXISTSIN 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 chaEXISTSKhông cần có thuộc tính, hằng số hay biểu thức nào khác đứng trướcKhông nhất thiết liệt kê tên thuộc tính ở mệnh đề SELECT của truy vấn conNhững câu truy vấn có = ANY hay IN đều có thể chuyển thành câu truy vấn có EXISTS71Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuCập nhật dữ liệuTruy vấn dữ liệuTruy vấn cơ bảnTập hợp, so sánh tập hợp và truy vấn lồngHàm kết hợp và gom nhómMột số dạng truy vấn khácMột số hàm thông dụng72Hàm kết hợpCOUNTCOUNT(*) đếm số dòngCOUNT() đếm số giá trị khác NULL của thuộc tínhCOUNT(DISTINCT ) đếm số giá trị khác nhau và khác NULL của thuộc tínhMINMAXSUMAVGCác hàm kết hợp được đặt ở mệnh đề SELECT73Ví dụCho biết số lượng nhân viên của từng phòng banSL_NV5433PHG11TENNVHONVNGSINHDCHIPHAILUONGPHGTungNguyen12/08/1955638 NVC Q5Nam400005HungNguyen09/15/1962Ba Ria VTNam380005333445555987987987MANVMA_NQL888665555333445555TENLOTThanhManhTamTran07/31/1972543 MTL Q1Nu250005HangBui07/19/196833 NTH Q1Nu380004453453453999887777333445555987654321ThanhNgocNhuLe07620/1951219 TD Q3Nu430004987654321888665555QuynhQuangTran04/08/1969980 LHP Q5Nam250004VinhPham11/10/1945450 TV HNNam550001987987987888665555987654321NULLHongVan74Gom nhómCú phápSau khi gom nhómMỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom nhómSELECT FROM WHERE GROUP BY 75Ví 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 giaSODATHOIGIAN132.527.5123456789123456789MA_NVIEN210.0310.03334455553334455551010.03334455552020.01035.0888665555987987987305.09879879873020.09876543212015.0987654321120.0453453453220.045345345376Ví dụCho biết những nhân viên tham gia từ 2 đề án trở lênSODATHOIGIAN132.527.5123456789123456789MA_NVIEN210.0310.03334455553334455551010.03334455552020.01035.0888665555987987987305.09879879873020.09876543212015.0987654321120.0453453453220.0453453453bị loại ra77Điều kiện trên nhómCú phápSELECT FROM WHERE GROUP BY HAVING 78Nhận xétMệ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 BYMệnh đề HAVINGSử 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 79Nhậ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 đề SELECT80Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuCập nhật dữ liệuTruy vấn dữ liệuTruy vấn cơ bảnTập hợp, so sánh tập hợp và truy vấn lồngHàm kết hợp và gom nhómMột số dạng truy vấn khácMột số hàm thông dụng81Một số dạng truy vấn khácTruy vấn con ở mệnh đề FROM Điều kiện kết ở mệnh đề FROMPhép kết tự nhiên Phép kết ngoàICấu trúc CASESELECT INTO82Truy vấn con ở mệnh đề FROMKết quả trả về của một câu truy vấn phụ là một bảngBảng trung gian trong quá trình truy vấnKhông có lưu trữ thật sựCú phápVí dụSELECT FROM R1, R2, () AS tên_bảngWHERE SELECT MANV, TENNV FROM NHANVIEN, (SELECT MAPHG FROM PHONGBAN WHERE TENPHG= ‘Nghien cuu’) AS BWHERE PHG = MAPHG83Điều kiện kết ở mệnh đề FROMKết bằngVí dụSELECT FROM R1 [INNER] JOIN R2 ON WHERE SELECT MANV, TENNV FROM NHANVIEN INNER JOIN PHONGBAN ON PHG = MAPHGWHERE TENPHG= ‘Nghien cuu’84Điều kiện kết ở mệnh đề FROMKết ngoàiVí dụSELECT FROM R1 LEFT|RIGHT|FULL JOIN R2 ON WHERE SELECT MANV, TENNV FROM NHANVIEN LEFT JOIN PHONGBAN ON PHG = MAPHG85Cấu trúc CASECho phép kiểm tra điều kiện và xuất thông tin theo từng trường hợpCú phápCASE WHEN THEN WHEN THEN [ELSE ]END86Cấ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' ENDFROM NHANVIEN87Select intoDùng để tạo ra một bảng mới và đưa dữ liệu vào bảng mớiVí dụ: tạo bảng gồm những nhân viên namSELECT [INTO ]FROM [WHERE ]SELECT * INTO NHANVIEN_NAMFROM NHANVIENWHERE PHAI=‘NAM’88Kết luậnSELECT [INTO ]FROM [WHERE ][GROUP BY ][HAVING ][ORDER BY ]89Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuKiểu dữ liệuCác lệnh định nghĩa dữ liệuTruy vấn dữ liệu Cập nhật dữ liệu Một số hàm thông dụngHàm toán họcHàm chuỗiHàm ngày thángHàm chuyển đổi kiểu90Hàm toán họcHàmDiễn giảiVí dụKết quảPower(X,Y)Tính X lũy thừa YSelect Power(2,5)32Round(X,n)Làm tròn X còn n số lẻRound(123.4567,2)123.46Square(X)Tính bình phương của XSquare(5)25SQRT(X)Tính căn bậc 2 của XSQRT(16)4Sum(cột)Tính tổng cộtSum(luong)Count(cột)Đếm số phần tử khác NullCount(MANV)Count(*)Đếm số dòngCount(*)Max(cột)Cho giá trị lớn nhấtMax(luong)Min(cột)Cho giá trị nhỏ nhấtMin(luong)Avg(cột)Tính trung bình cộtAvg(luong)91Hàm chuỗiHàmDiễn giảiVí dụKết quảAscii(C)Trả về mã Ascii của ký tự Cselect ASCII(‘A’)65Char(N)Trả về ký tự có mã Ascii là NChar(66) BLen(S)Trả về chiều dài SLen(‘abc xyz’)7Lower(S)Chuyển S sang chữ thườngLower(‘abcXYZ’)abcxyzUpper(S)Chuyển S sang chữ hoaUpper(‘abcXYZ’)ABCXYZLTrim(S)Cắt khoảng trắng bên tráiLTrim(‘ abc’)abcRtrim(S)Cắt khoảng trắng bên phảiRTrim(‘abc ’)abcLeft(S,n)Trích n ký tự bên trái SLeft(‘abcxyz’,4)abcxRight(S,n)Trích n ký tự bên phải SRight(‘abcxyz’,4)cxyz92Hàm chuỗi (tt)HàmDiễn giảiVí dụKết quảS1+S2Nối S1 với S2Select ‘abc’+’XYZ’abcXYZCharIndex(S1,S)Trả về vị trí đầu tiên của S1xuất hiện trong SCharIndex(’bc’,‘abcxyzabc’)2SubString(S,p,n)Trích n ký tự từ vị trí p của SSubString(‘abcxyz’,2,4)bcxyReplace (S1,S2,S3)Thay tất cả S2 trong S1 bằng S3Replace(‘ababc’,’ab’,’xy’)xyxycReverse(S)Trả về chuỗi đảo ngược SReverse(‘abcxyz’)zyxcba93Hàm ngày thángCác hàm có sử dụng tham số DatePart, giá trị của DatePart được cho như bảng sauDatePartGiá trịDiễn giảiDD1-31Ngày trong thángMM1-12Tháng trong nămQQ1-4Quý trong nămDW1-7 (Sun-Sat)Thứ trong tuầnYY1753-9999Năm94Hàm ngày tháng (tt)HàmDiễn giảiVí dụKết quảGetDate()Trả về ngày giờ hiện hànhSelect GetDate()2007-06-18 08:34:44.107Day(date)Trích phần ngàyDay(GetDate())18Month(date)Trích phần thángMonth(Getdate())6Year(date)Trích phần nămYear(GetDate())2007DatePart(DatePart,date)Trích DatePart của dateDatePart(mm,GetDate())6DateAdd(DatePart, n,date)Thêm n DatePart vào dateDateAdd (dd,-2,Getdate())2007-06-16DateDiff(DatePart, date1,date2)Trả về số DatePart giữa 2 ngàyDateDiff(mm,‘2007-02-16’,Getdate())495Hàm chuyển đổi kiểuHàmDiễn giảiVí dụKết quảCast(exp AS data_type)Chuyển giá trị exp sang kiểu data_typeCast(‘123’ AS Int) Cast(123 AS Varchar(10))123‘123’Convert(data_type,exp [,style])Chuyển giá trị exp sang kiểu data_type theo styleConvert(Varchar(20),GetDate(),103)Convert(Varchar(20),GetDate(),101)18/06/200706/18/200796