Bài giảng Ngôn ngữ truy vấn dữ liệu

29/01/2016 1 FAA NGÔN NGỮ TRUY VẤN DỮ LIỆU 1 MỤC TIÊU Sau khi buổi học sinh viên biết được:  Về kiến thức: Cấu trúc các câu lệnh truy vấn và xử lý dữ liệu trong SQL  Về kỹ năng: xem và thay đổi được dữ liệu trên một database 2 NỘI DUNG T- SQL chia làm 3 nhóm câu lệnh:  Ngôn ngữ định nghĩa dữ liệu  Ngôn ngữ thao tác dữ liệu  Ngôn ngữ điều khiển dữ liệu 3 Độ ưu tiên Toán tử 1 * (Multiply), / (Division), % (Modulo) 2 + (Positive), - (Negative), + (Add), - (Subtract) 3 =, >, =, , !=, !>, !< (Comparison operators) 4 NOT 5 AND 6 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME 7 = (Assignment) TOÁN TỬ VÀ ĐỘ ƯU TIÊN Ngôn ngữ định nghĩa dữ liệu  1. Create: Tạo đối tượng  2. Alter: Thay đổi cấu trúc bảng  3. Drop: Xóa đối tượng 5 Lệnh CREATE Create Database TenCSDL Create Table TenBang ( Tên Cột Kiểu DL Null | Not Null, CONSTRAINT [Tên ràng buộc] CHECK (điều kiện), CONSTRAINT [Tên khóa chính] PRIMARY KEY CLUSTERED (DS CỘT), CONSTRAINT [Tên khóa ngoại] FOREIGN KEY (Cột khóa ngoại) REFERENCES Bảng LK đến (Cột LK) ON UPDATE CASCADE ON DELETE CASCADE ) 6 29/01/2016 2 Lệnh ALTER  ALTER TABLE VD: Thêm thông tin vào bảng ALTER TABLE ADD TênĐốiTượng Thông tin đối tượng VD: Chỉnh sửa thông tin bảng ALTER TABLE ADD TênĐốiTượng Thông tin đối tượng 7 Lệnh ALTER VD: Xóa đối tượng trong bảng ALTER TABLE DROP TênĐốiTượng 8 Lệnh DROP  DROP TênĐốiTượng 9 NGÔN NGỮ THAO TÁC DỮ LIỆU 1. Câu lệnh Select – Hiển thị dữ liệu 2. Câu lệnh Insert – Thêm dữ liệu 3. Câu lệnh Update – Cập nhật dữ liệu 4. Câu lệnh Delete – Xoá dữ liệu 10 1. CÂU LỆNH SELECT 11 Cú pháp: SELECT [ALL | DISTINCT | TOP n] danh_sách_chọn [INTO tên_bảng_mới] FROM danh_sách_bảng/khung_nhìn [WHERE điều_kiện] [GROUP BY danh_sách_cột] [HAVING điều_kiện] [ORDER BY cột_sắp_xếp] [COMPUTE danh_sách_hàm_gộp [BY danh_sách_cột] Danh_Sách _Chọn Chọn tất cả các cột trong bảng Cú pháp: SELECT * FROM Tên bảng VD: Hiển thị tất cả những dữ liệu của bảng DMHH Select * From T01_DMHH 12 29/01/2016 3 Danh_Sách _Chọn Lưu ý: Từ khóa DISTINCT TOP INTO 13 Danh_Sách _Chọn Chọn các cột được chỉ định Cú pháp: SELECT TenCot1, TenCot2, FROM Tên bảng VD: Hiển thị Mã hàng và Tên hàng Select MaHang, TenHang From T01_DMHH 14 Danh_Sách _Chọn Đổi tên các cột ở kết quả hiển thị Cú pháp: SELECT ‘tiêu_đề_cột’ = TenCot hoặc TenCot ‘tiêu_đề_cột’ hoặc TenCot as [tiêu_đề_cột] FROM Tên bảng 15 Danh_Sách _Chọn Thêm một Field tính toán vào kết quả VD: SELECT ThanhTien = SL*DonGia FROM Tên bảng 16 Mệnh đề FROM Ý nghĩa: Xác định table, khung nhìn và sự liên kết giữa các table khi truy vấn dữ liệu  DL truy vấn từ nhiều bảng nên dùng phép nối VD: Hiển thị toàn bộ thông tin hàng hóa từ bảng DanhMucHangHoa SELECT * FROM T01_DMHH 17 Mệnh đề WHERE Xác định điều kiện cho câu truy vấn Sau WHERE có thể là: Toán tử so sánh Giới hạn Danh sách Khuôn dạng Giá trị Kết hợp các điều kiện 18 29/01/2016 4 Mệnh đề WHERE  Các toán tử so sánh (>,) VD: Đọc thông tin toàn bộ mặt hàng có GiaBan = 10000 SELECT * FROM T01_DMHH WHERE GiaBan = 10000 19 Mệnh đề WHERE  Giới hạn khoảng tìm kiếm (BETWEEN và NOT BETWEEN) VD: Hiển thị mặt hàng có số lượng nhập từ 10.000 đến 20.000 SELECT * FROM ChiTietPhieuNX WHERE SoLuong between 10000 and 20000 AND SoPhieu Like ‘PN%’ 20 Mệnh đề WHERE  Danh sách các giá trị (IN và NOT IN) VD: Hiển thị thông tin của Khách hàng có mã khách là KH01, KH02 SELECT * FROM T02_DMKH WHERE MaKH IN (‘KH01’,’KH02’) 21 Mệnh đề WHERE Khi sử dụng ký tự đại diện ta dùng từ khoá LIKE, NOT LIKE Ký tự đại diện:  % : chuỗi ký tự bất kỳ  - : Ký tự đơn bất kỳ  [] ký tự đơn bất kỳ trong giới hạn được chỉ định  [^] ký tự đơn bất kỳ không trong giới hạn được chỉ định 22 Mệnh đề WHERE Toán tử logic AND, OR để kết hợp các điều kiện VD: Hiển thị thông tin về mặt hàng nhập kho có MaHang là B và SLNhap lớn hơn 10.000 SELECT * FROM ChiTietNhapKho WHERE Mahang ='SP_B' AND SLNhap >10000 23 Mệnh đề Group By GROUP BY để Phân nhóm dữ liệu Có thể áp dụng các hàm tổng hợp đối với những nhóm nhỏ hàng dữ liệu chọn ra. 24 29/01/2016 5 Mệnh đề Group By  SQL cung cấp các hàm gộp • SUM • AVG • COUNT • MAX • MIN 25 Mệnh đề Group By Câu lệnh thống kê chia làm 2 loại: Thống kê trên toàn bộ dữ liệu Thống kê phân nhóm dữ liệu 26 Mệnh đề Group By VD1: Nhập xuất kho bao nhiêu mặt hàng? SELECT count(distinct mahang) FROM ChiTietPhieuNhapXuat VD2: Đếm số lần nhập xuất của từng mặt hàng SELECT MaHang, COUNT(MaHang) FROM ChiTietPhieuNhapXuat GROUP BY MaHang 27 Mệnh đề HAVING Thiết lập điều kiện cho một nhóm hoặc hàm tổng hợp Phân biệt HAVING và WHERE? Lưu ý: Chỉ sau khi dữ liệu đã được nhóm và tổng hợp, điều kiện trong mệnh đề HAVING mới được áp dụng. 28 Mệnh đề HAVING VD: Hiển thị mặt hàng nhập kho nhiều lần trong kỳ SELECT MaHang, ‘Số lần nhập’= COUNT(MaHang) FROM ChiTietNhapXUAT WHERE SOPHIEU LIKE ‘PN%’ GROUP BY MaHang HAVING count(MaHang)>=2 29 Mệnh đề ORDER BY Sắp xếp theo thứ tự (tăng ASC, giảm DESC) VD: Sắp xếp theo chiều giảm dần về số lần nhập xuất kho của các mặt hàng SELECT MaHang, COUNT(*) as SoLanNhap FROM ChiTietPhieuNhapXuat GROUP BY MaHang ORDER BY COUNT(*) DESC 30 29/01/2016 6 Mệnh đề COMPUTE BY Cú pháp: COMPUTE Hàm gộp (tên_cột) [BY tên_cột] Hàm gộp: COUNT, AVG, SUM, MIN, MAX 31 Mệnh đề COMPUTE BY VD: Hiển thị thông tin về tổng số lượng nhập kho của từng mã hàng. SELECT MaHang, SLNhap FROM ChiTietPhieuNhap ORDER BY MaHang ASC COMPUTE sum (SLNhap) BY MaHang 32 Phép nối (JOIN) Inner Join (Phép nối trong) Outer Join (Phép nối ngoài)  Left Outer Join  Right Outer Join  Full Outer Join 33 Mệnh đề JOIN Inner Join: 34 Kết quả Mệnh đề JOIN Inner Join: Cú pháp: SELECT DS chọn FROM Bảng 1 [INNER] JOIN Bảng 2 ON điều kiện nối Điều kiện nối: Bảng 1. Cột tham chiếu = Bảng 2. Cột tham chiếu 35 Mệnh đề JOIN Outer join (phép nối ngoài) SELECT DS chọn FROM Bảng 1 [LEFT | RIGHT] OUTER JOIN Bảng 2 ON Điều kiện nối Điều kiện nối: Bảng 1. Cột tham chiếu = Bảng 2. Cột tham chiếu 36 29/01/2016 7 Outer join Lưu ý: Những cột mà không có giá trị phù hợp sẽ được hiển thị giá trị NULL. 37 Left outer Join 38 Kết quả Right outer Join 39 Kết quả Full outer Join 40 Kết quả Truy vấn con (Subquery) Subquery là lệnh SELECT mà kết quả trả về là 1 giá trị đơn (single value) và được đặt lồng vào bên trong các lệnh SELECT, INSERT, UPDATE, hay DELETE, hay bên trong truy vấn con khác. 41 Truy vấn con (Subquery) VD: Hiển thị các mặt hàng chưa nhập xuất trong kỳ (Có trong DMHH nhưng không có trong CTPNX) SELECT * FROM DanhMucHangHoa WHERE MaHang not in (SELECT MaHang FROM ChiTietPhieuNhapXuat) 29/01/2016 8 HIỂN THỊ THEO ĐIỀU KIỆN CÚ PHÁP SELECT , ‘TÊN TRƯỜNG’ =CASE WHEN Điều kiện 1 THEN Gía trị 1 WHEN Điều kiện 2 THEN Gía trị 2 .. ELSE Giá trị n END, . FROM WHERE . TẠO BẢNG ẢO CREATE VIEW TEN_BANG_AO AS CÂU LỆNH SELECT LẤY DL TẠO BẢNG ẢO 2. CÂU LỆNH INSERT  Thêm từng dòng dữ liệu INSERT INTO tên_bảng[(danh_sách_cột)] VALUES (danh_sách_trị) Thêm các dòng dữ liệu từ bảng khác: INSERT INTO tên_bảng[(danh_sách_cột)] câu_lệnh_SELECT 45 3. CÂU LỆNH UPDATE Cú pháp: UPDATE TenBang SET GIÁ TRỊ MỚI WHERE ĐK CẬP NHẬT VD: Đổi Tên của khách hàng 01 thành Công ty Viễn Thông UPDATE DMKH SET TenKH =N ‘Cty Viễn Thông’ WHERE MaKH =‘KH01’ 46 4. CÂU LỆNH DELETE Cú pháp DELETE FROM tên_bảng [FROM danh_sách_bảng] [WHERE điều_kiện] VD: Xóa Mặt hàng D Delete from DanhMucHangHoa where MaHang = ‘SP_D’ 47 4. CÂU LỆNH DELETE Xoá toàn bộ dữ liệu trong bảng TRUNCATE TABLE tên_bảng VD: Xóa toàn bộ dữ liệu của NhatKyChung truncate table NhatKyChung 48 29/01/2016 9 Ngôn ngữ điều khiển dữ liệu Grant Deny Revoke . 49