intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server: Chương 4 - Nguyễn Thị Mỹ Dung

Chia sẻ: Chuheodethuong 09 | Ngày: | Loại File: PDF | Số trang:31

Thêm vào BST
Báo xấu
39
lượt xem 6
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server: Chương 4 Lập trình với Transact-SQL cung cấp cho người học những kiến thức như: Thiết kế View, Index; Lập trình trong T-SQL; Stored Procedure; Trigger. Mời các bạn cùng tham khảo!

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server: Chương 4 - Nguyễn Thị Mỹ Dung

  1. CH4: LẬP TRÌNH TRANSACT - SQL GVPT: NGUYỄN THỊ MỸ DUNG SỐ TC: 2 SỐ TIẾT: LT: 20; TH: 20 Biên soạn: Nguyễn Thị Mỹ Dung Chương 1: Tổng quan về SQL Server (LT: 2) Chương 2: Tạo và quản trị CSDL (LT: 6; TH: 6) Chương 3: Transact-SQL và truy vấn dữ liệu (LT: 6: TH: 8) Chương 4: Lập trình với Transact-SQL (LT: 4: TH: 4) Chương 5: Bảo Mật và Phân Quyền (LT: 2; TH: 2) Chương 6: Kết nối CSDL (Tự học) Biên soạn: Nguyễn Thị Mỹ Dung 1
  2. CH4: LẬP TRÌNH TRANSACT - SQL I. Thiết kế View, Index II. Lập trình trong T-SQL III. Stored Procedure IV. Trigger Biên soạn: Nguyễn Thị Mỹ Dung 1. Thiết kế View - Là một bảng ảo được tạo ra từ tập con của các bảng (Table) thật khác. Đối với người dùng View là bảng thật. - Cũng tương tự như truy vấn dữ liệu là dùng để xem dữ liệu từ nhiều bảng khác nhau trong CSDL. - Làm giảm sự phức tạp của CSDL  bảo vệ dữ liệu đối với người dùng không được phép truy cập. - Các lệnh sử dụng trên View tương tự như trên Table trong CSDL. - Nhược điểm của View là mất thời gian truy cập dữ liệu từ bảng (table) gốc. Biên soạn: Nguyễn Thị Mỹ Dung 2
  3. CH4: LẬP TRÌNH TRANSACT - SQL  (khung nhìn): CREATE VIEW AS SELECT FROM [WHERE GROUP BY ] Biên soạn: Nguyễn Thị Mỹ Dung Ví dụ: Tạo View Ketquadetai gồm hotensv, học lực, điểm trung bình đề tài USE QLDETAISV CREATE VIEW KETQUADETAI AS SELECT SINHVIEN.MASV, HOCLUC, ROUND(AVG(KQ),2) AS DIEMTBDT FROM SINHVIEN LEFT JOIN SV_DT ON SV_DT.MASV = SINHVIEN.MASV GROUP BY SINHVIEN.MASV, HOCLUC Biên soạn: Nguyễn Thị Mỹ Dung 3
  4. CH4: LẬP TRÌNH TRANSACT - SQL  : ALTER VIEW AS SELECT FROM [WHERE GROUP BY ]  : DROP VIEW Biên soạn: Nguyễn Thị Mỹ Dung VD1: Sửa View Ketquadetai gồm hotensv, học lực từ 7 điểm trở lên và điểm trung bình đề tài. USE QUANLYDETAISV ALTER VIEW KETQUADETAI AS SELECT SINHVIEN.MASV, HOCLUC, ROUND(AVG(KETQUA),2) AS DIEMTBDT FROM SINHVIEN LEFT JOIN SV_DT ON SV_DT.MASV = SINHVIEN.MASV GROUP BY SINHVIEN.MASV, HOCLUC WHERE HOCLUC >= 7 VD2: DROP VIEW KETQUADETAI Biên soạn: Nguyễn Thị Mỹ Dung 4
  5. CH4: LẬP TRÌNH TRANSACT - SQL Lưu ý 1: - Có thể sử dụng các câu truy vấn trên view tương tự như trên bảng (Table) - Có thể truy vấn trên vừa trên bảng và vừa trên View. Ví dụ: SELECT MASV, HOTENSV, HOCLUC, DTBDT FROM KETQUADETAI KQ, SINHVIEN S WHERE KQ.MASV = S.MASV AND DTBDT >= 8 - Khi thay đổi (xóa, thêm, sửa) dữ liệu trên View thì dữ liệu trên bảng gốc cũng thay đổi theo. Biên soạn: Nguyễn Thị Mỹ Dung Lưu ý 2: - Khi tạo View có sử dụng hàm kết tập thì không thể thực hiện được các thao tác: Insert, Update, Delete. VD: CREATE VIEW KETQUADT AS SELECT S.MASV, HOTENSV, AVG(KQ) AS DTBDT FROM SV_DT SD, SINHVIEN S WHERE SD.MASV = S.MASV GROUP BY S.MASV, HOTENSV Biên soạn: Nguyễn Thị Mỹ Dung 5
  6. CH4: LẬP TRÌNH TRANSACT - SQL 2. Tạo Index (chỉ mục) Chỉ mục được tạo ra nhằm để các dòng trong bảng được truy xuất nhanh và hiệu quả hơn. Chỉ mục có thể được tạo trên một hoặc nhiều cột của bảng, và mỗi chỉ mục được đặt một tên. Người dùng không thấy được các chỉ mục này. Lưu ý: Khi bảng đã được tạo chỉ mục thì việc cập nhật hay thêm dòng mới vào bảng sẽ mất nhiều thời gian hơn là đối với bảng không có chỉ mục. Chỉ nên tạo chỉ mục cho các cột thường xuyên dùng trong các tác vụ tìm kiếm. Biên soạn: Nguyễn Thị Mỹ Dung  Chỉ mục đơn nhất (Unique Index) Cú pháp: CREATE [UNIQUE] INDEX ON UNIQUE: bắt buộc hai dòng bất kỳ của bảng sẽ không được phép mang cùng giá trị ở cột được tạo chỉ mục. Ví dụ 1: CREATE UNIQUE INDEX TENDT_ID ON DE_TAI(TENDT) Ví dụ 2: Tạo chỉ mục nhiều cột CREATE INDEX SV_Index ON SINHVIEN (Hosv, Tensv) Biên soạn: Nguyễn Thị Mỹ Dung 6
  7. CH4: LẬP TRÌNH TRANSACT - SQL Xóa chỉ mục DROP INDEX Ví dụ: DROP INDEX DE_TAITENDT_ID DROP INDEX SINHVIEN.SV_ID Biên soạn: Nguyễn Thị Mỹ Dung 1. Khái niệm Giống như NNLT khác, SQL cũng sử dụng biến, các lệnh rẽ nhánh (if.. else, Case … end) và vòng lặp (while) đơn giản. Ngoài ra, SQL cũng hỗ trợ xây dựng thủ tục, hàm. Điểm khác biệt với NNLT khác là SQL cho phép thiết lập trigger (bẫy lỗi sự kiện), cussor,… - Biến trong SQL dùng để lưu giá trị tạm thời trong quá trình xử lý tính toán. - Các lệnh cấu trúc dùng để hỗ trợ trong lập trình SQL. - Chương trình con, trigger giúp SQL thực hiện các ràng buộc dữ liệu trong CSDL. Biên soạn: Nguyễn Thị Mỹ Dung 7
  8. CH4: LẬP TRÌNH TRANSACT - SQL 2. Khai báo biến  Khai báo biến cục bộ DECLARE { @local_variable [AS] data_type, … } Trong đó: @local_variable: tên biến cục bộ, phải bắt đầu bằng ký hiệu @. data_type: kiểu dữ liệu hệ thống hoặc kiểu dữ liệu người dùng. Biên soạn: Nguyễn Thị Mỹ Dung  Sử dụng biến Câu lệnh SET hoặc SELECT (lấy giá trị từ bảng dữ liệu) dùng để gán giá trị cho biến. SET @local_variable = value OR: SELECT @local_variable = value VD1: DECLARE @x int, @y int SET @y = 5 SET @x = @y + 3 VD2: DECLARE @TBKP FLOAT SELECT @TBKP = ([SELECT] AVG(Kinhphi) FROM DETAI WHERE MADT = 'DT001') Biên soạn: Nguyễn Thị Mỹ Dung 8
  9. CH4: LẬP TRÌNH TRANSACT - SQL Hiển thị giá trị biến Sử dụng câu lệnh PRINT hoặc SELECT để hiển thị giá trị của biến. VD: PRINT @x PRINT @y SELECT @x, @y Biên soạn: Nguyễn Thị Mỹ Dung Lưu ý: - Khi gán trị cho biến có dữ liệu từ bảng dữ liệu phải sử dụng từ khóa SELECT - Khi in dữ liệu vừa có chuỗi và vừa có biến bằng lệnh PRINT phải chuyển đổi kiểu cho biến sang chuỗi với hàm CONVERT CONVERT. Cú pháp: CONVERT (, ) VD: CONVERT (CHAR(5), MAXKP) CONVERT (VARCHAR(5), SUMKP) Biên soạn: Nguyễn Thị Mỹ Dung 9
  10. CH4: LẬP TRÌNH TRANSACT - SQL VD1: Tính tổng 2 số DECLARE @tong float, @a float, @b float SET @a = 10.0 SET @b = 2.0 SET @tong = @a * @b PRINT @tong SELECT @a as so1, @b as so2, @tong as tong VD2: Tính điểm trung bình của sinh viên A02 từ bảng KETQUA DECLARE @DiemTB float SET @DiemTB=(SELECT AVG(Diem) FROM KetQua WHERE MaSV='A02') SELECT @DiemTB as DTB Biên soạn: Nguyễn Thị Mỹ Dung VD3 VD 3: Lấy tổng kinh phí tất cả các đề tài từ bảng đề tài. DECLARE @tongkp float SELECT @tongkp = sum(kinhphi) FROM DETAI VD4 VD 4: Tìm max kinh phí và min kinh phí của đề tài DECLARE @maxkp int, @minkp int SELECT @maxkp=MAX(kinhphi),@minkp = MIN (kinhphi) FROM DETAI PRINT N'Kinh phí cao nhất là: ' + CONVERT (CHAR,@maxkp) PRINT N'Kinh phí thấp nhất là: ' + CONVERT (CHAR,@minkp) Biên soạn: Nguyễn Thị Mỹ Dung 10
  11. CH4: LẬP TRÌNH TRANSACT - SQL  Biến hệ thống – Biến hệ thống là biến có sẵn và hệ thống quản lý. – Biến hệ thống trong SQL Server được đặt tên bắt đầu bởi 2 ký hiệu @. Ví dụ: PRINT @@CPU_BUSY PRINT @@VERSION SELECT @@SERVERNAME SELECT @@DATEFIRST AS SONGAYTUAN SELECT @@CONNECTIONS AS SLGNOIKET Biên soạn: Nguyễn Thị Mỹ Dung Danh sách biến và ý nghĩa một số biến hệ thống BIẾN Ý NGHĨA @@CONNECTIONS Số các kết nối đến Server @@CPU_BUSY Số lượng xử lý công việc của SQL @@CURSOR_ROWS Số bản ghi trong cursor mở gần nhất @@DATEFIRST Số ngày trong tuần @@ERROR Mã lỗi xảy ra gần nhất @@FETCH_STATUS = 0: nếu truy xuất thành công, = 1: ngược lại @@IDENTITY Giá trị identity gần nhất được sinh ra @@LANGUA GE Ngôn ngữ sử dụng @@MAX_CONNECTIONS Số lượng nối kết tối đa @@ROWCOUNT Số bản ghi bị tác động bởi SQL @@SERV ICENA ME Dịch vụ SQL trên máy chủ @@TRANSCOUNT Số giao dịch đang hoạt động trên nối kết hiện tại @@VERSION Phiên bản của SQL Biên soạn: Nguyễn Thị Mỹ Dung 11
  12. CH4: LẬP TRÌNH TRANSACT - SQL 3. Khối lệnh Một tập lệnh SQL được thực thi sẽ được đặt trong khối lệnh BEGIN … END Cú pháp: BEGIN | END VD: BEGIN DECLARE @DIEMTB_DT NUMERIC, … PRINT @DIEMTB_DT END Biên soạn: Nguyễn Thị Mỹ Dung 3. Các lệnh cấu trúc lựa chọn Chúng ta có thể thực thi các tập lệnh SQL khác nhau dựa vào các điều kiện chọn khác nhau. a. IF … ELSE Cú pháp: IF < lệnh sql1> | [ ELSE [IF < lệnh sql2>|< tập lệnh2> [ELSE]] ] Biên soạn: Nguyễn Thị Mỹ Dung 12
  13. CH4: LẬP TRÌNH TRANSACT - SQL VD1: TÌM SỐ LỚN NHẤT TRONG 3 SỐ DECLARE @A INT, @B INT, @C INT, @MAX INT SET @A = 4 SET @B = 2 SET @C = 10 SET @MAX = @A IF @A > @MAX SET @MAX = @A ELSE BEGIN IF @B > @MAX SET @MAX = @B ELSE SET @MAX = @C END PRINT N'SỐ LỚN NHẤT: '+ CONVERT(CHAR,@MAX) Biên soạn: Nguyễn Thị Mỹ Dung VD2: Tìm xem có đề tài nào có kinh phí trên 20 tr không? Nếu có in ra thông, không có hiển thị thông báo IF (SELECT COUNT(*) FROM DETAI WHERE KINHPHI > 20 ) > 0 BEGIN PRINT N'DANH SÁCH ĐỀ TÀI CÓ KINH PHÍ TRÊN 20 TRIỆU: ' SELECT MADT, TENDT, CHUNHIEM, KINHPHI FROM DETAI WHERE KINHPHI > 20 END ELSE PRINT N'KHÔNG CÓ ĐỀ TÀI NÀO TRÊN 20 TRIỆU!!!' Biên soạn: Nguyễn Thị Mỹ Dung 13
  14. CH4: LẬP TRÌNH TRANSACT - SQL b. IF với EXISTS IF có kết hợp từ khóa EXISTS Để kiểm tra sự tồn tại của các dòng dữ liệu bên trong bảng. IF EXISTS (Câu_lệnh_SELECT) Câu_lệnh1 | Khối_lệnh1 [ ELSE Câu _lệnh2 | Khối_lệnh2 ] Biên soạn: Nguyễn Thị Mỹ Dung VD1: Tìm những đề tài có kinh phí lớn hơn kinh phí trung bình tất cả các đề tài. DECLARE @TBKP FLOAT SET @TBKP = (SELECT AVG(KINHPHI) FROM DETAI) IF EXISTS (SELECT KINHPHI FROM DETAI WHERE KINHPHI > @TBKP) BEGIN PRINT N'CÁC ĐỀ TÀI CÓ KINH PHÍ CAO:' + CONVERT(CHAR, @TBKP) SELECT MADT, TENDT, CHUNHIEM, KINHPHI FROM DETAI WHERE KINHPHI > @TBKP END Biên soạn: Nguyễn Thị Mỹ Dung 14
  15. CH4: LẬP TRÌNH TRANSACT - SQL VD2: Tìm thông tin những đề tài có nhiều hơn 3 sinh viên thực hiện, nếu không có in ra thông báo để biết. IF EXISTS (SELECT MASV, COUNT (MADT) FROM SV_DT GROUP BY MASV HAVING COUNT (MADT) > 3) BEGIN PRINT N'CÁC ĐỀ TÀI CÓ SỐ LƯỢNG TRÊN 2 SINH VIÊN THỰC HIỆN!' SELECT DISTINCT D.MADT, TENDT, CHUNHIEM, KINHPHI, COUNT(SD.MASV) AS SL_SV FROM DETAI D INNER JOIN SV_DT SD ON D.MADT = SD.MADT GROUP BY D.MADT, TENDT, CHUNHIEM, KINHPHI HAVING COUNT(SD.MASV) > 3 END ELSE PRINT N'KHÔNG CÓ ĐỀ TÀI NÀO TRÊN 3 SINH VIÊN THỰC HIỆN!' Biên soạn: Nguyễn Thị Mỹ Dung 4. Câu lệnh Case… end Dùng để lựa chọn nhiều giá trị, nếu sau Case xuất hiện khi biểu thức có kiểu dữ liệu số. Case [] when then when then ….. [Else ] End Biên soạn: Nguyễn Thị Mỹ Dung 15
  16. CH4: LẬP TRÌNH TRANSACT - SQL Ví dụ1: DECLARE @TEN CHAR(3), @XUAT NVARCHAR(100) SET @TEN = 'PHI' SET @XUAT = (CASE @TEN WHEN 'MR' THEN N'Xin chào quý ông!!!' WHEN 'Mrs' THEN N'Xin chào quý bà!!!' WHEN 'Ms' THEN N'Xin chào quý cô!!!' ELSE N'XIN CHÀO MỌI NGƯỜI!!!' END) PRINT @XUAT Biên soạn: Nguyễn Thị Mỹ Dung Ví dụ 2: Xếp loại học lực cho sinh viên SELECT SINHVIEN.MASV,HOTENSV, HOCLUC, XEPLOAI= (CASE WHEN HOCLUC =5 AND HOCLUC=7 AND HOCLUC
  17. CH4: LẬP TRÌNH TRANSACT - SQL 5. Lệnh vòng lặp Các câu lệnh được thực thi nhiều lần (lặp) khi nào điều kiện vẫn còn đúng. WHILE BEGIN --Các câu lệnh END - Break: lệnh break nằm bên trong vòng lặp while dùng để kết vòng lặp. - Continue: lệnh continue nằm bên trong vòng lặp while để bỏ qua các lệnh phía sau nó và bắt đầu vòng lặp mới. Biên soạn: Nguyễn Thị Mỹ Dung VD1: In tổng từ 1 đến 10 DECLARE @TONG INT, @I INT SET @TONG = 0 SET @I = 1 WHILE (@I
  18. CH4: LẬP TRÌNH TRANSACT - SQL VD2: Tính tổng các số chẵn từ 1 đến 20 DECLA RE @TONGCHA N INT, @I INT SET @TONGCHA N = 0 SET @I = 1 WHILE (@I
  19. CH4: LẬP TRÌNH TRANSACT - SQL 2. Tạo procedure Procedure có thể sử dụng để kiểm tra tham số đầu vào, đầu ra cho các thao tác dữ liệu. - Cú pháp: CREATE PROC | PROCEDURE [()] AS --Tập lệnh SQL --… GO - Thực thi lời gọi thủ tục: EXEC | EXECUTE Biên soạn: Nguyễn Thị Mỹ Dung VD1: Viết thủ tục nhập vào một tên. Xác định: VD1 - Nếu nhập ‘Mr’ thì print ‘Xin chào quý ông!’ - Nếu nhập ‘Mrs’ thì print ‘Xin chào quý bà!’ - Nếu nhập ‘Ms’ thì print ‘Xin chào quý cô!’ --Tham khảo CREATE PROC Kt (@tensv nvarchar(3)) AS IF (@tensv='Mr') PRINT (N'Xin chào quý ông!!!') IF (@tensv='Mrs') PRINT (N'Xin chào quý bà!!!') IF (@tensv = 'Ms') PRINT (N'Xin chào quý cô!!!') GO EXECUTE kt 'Ms KIM' Biên soạn: Nguyễn Thị Mỹ Dung 19
  20. CH4: LẬP TRÌNH TRANSACT - SQL VD2 : Kiểm tra MASV, MADT khi nhập dữ liệu vào bảng SV_DT CREATE PROC PRO_SV_DT (@MASV NVARCHAR(10), @MADT NVARCHAR(10), @NOIA_D NVARCHAR(40), @KETQUA FLOAT) AS IF EXISTS (SELECT MASV FROM SINHVIEN WHERE MASV = @MASV) BEGIN IF EXISTS (SELECT MADT FROM DETAI WHERE MADT = @MADT) BEGIN INSERT INTO SV_DT (MASV, MADT, NOIA_D, KETQUA) VALUES (@MASV, @MADT, @NOIA_D, @KETQUA) PRINT N'ĐÃ THÊM THÀNH CÔNG' END END ELSE PRINT 'KHÔNG THE INSERT DO MASV HAY MADT KHÔNG TON TAI' RETURN 0 GO --Thực hiện lệnh để kiểm tra EXEC PRO_SV_DT 'SV12','DT04','AN GIANG',8 Biên soạn: Nguyễn Thị Mỹ Dung VD3: Giả sử ta cần thực hiện một chuỗi các thao tác như sau trên cơ sở dữ liệu 1. Thêm vào danh sách đề tài (MADT, TênDT, Chủ nhiệm, Kinh phí): DT1001, Xây dựng Website hỗ trợ việc làm, Trần Kiến Quốc, 20 2. Lập danh sách sinh viên thực hiện đề tài DT1001 cho tất cả sinh viên có học lực trên 8. Cách 1: Theo cách thông thường INSERT INTO DETAI VALUES (‘DT1001', N‘Xây dựng Website hỗ trợ việc làm’, N‘Trần Kiến Quốc’, 20) INSERT INTO SV_DT (MASV,MADT) SELECT MASV, ‘DT1001' FROM SINHVIEN WHERE HOCLUC>8 Biên soạn: Nguyễn Thị Mỹ Dung 20
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
5=>2