Giáo trình Ngôn ngữ truy vấn dữ liệu

Nội dung text: Giáo trình Ngôn ngữ truy vấn dữ liệu

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