Bài giảng Hệ quản trị cơ sở dữ liệu 2 (SQL server) - Nguyên Trí Nhân

TRƯỜNG ĐẠI HỌC PHẠM VĂN ĐỒNG
KHOA CÔNG NGHỆ THÔNG TIN
HẸ QUẢN TRỊ CƠ SỞ DỮ LIỆU 2
SQL SERVER
(Dùng cho bậc Cao đẳng)
Giảng viên: Th.S Nguyên Trí Nhân
Quảng Ngãi, Spring-2014
MỤC LỤC
Tạo mới mô hình quan hệ dữ liệu:	88
BÀI TẬP THỰC HÀNH	92
Chương 5. TẠO VIEW (BẢNG ẢO)	97
KHÁI NIỆM VIEW	97
SỬ DỤNG T - SQL	97
SỬ DỤNG ENTERPRISE MANAGER	102
BÀI TẬP THỰC HÀNH	105
Chương 6. STORE PROCEDURE	106
KHÁI NIỆM STORE PROCEDURE	106
Thủ tục nội là gì ?	106
Các Procdure Store hệ thống:	106
Các lợi ích khi sử dụng Procdure Store:	107
TẠO STORE PROCEDURE BẰNG LỆNH CREATE	107
THI HÀNH STORE PROCEDURE	110
CÁC PHÁT BIÊU ĐIÈU KHIÊN	110
Biểu thức Case	110
Cấu trúc rẽ nhánh IF... ELSE	114
Cấu trúc lặp WHILE	117
SỬA, XÓA, ĐỔI TÊN THỦ TỤC (STORE PROCEDURE)	121
Hủy bỏ Procdure Store:	121
Thay đổi nội dung của Procdure Store:	121
XỬ LÝ LỎI TRONG STORE PROCEDURE (SP)	123
Mã trả về	123
Câu lệnh RAISERROR	124
BÀI TẬP THỰC HÀNH	125
Chương 7. CHUYÊN TÁC, BẢY LỎI VÀ KIÊU CON TRỎ TRONG SQL SERVER
	...	 ........	.	..........	126
CHUYÊN TÁC (TRANSACTION)	126
Khái niệm	126
Cách tạo và ứng dụng	126
BẢY LỎI (TRIGGER)	133
Khái niệm	133
Cách tạo và ứng dụng	133
KIÊU CON TRỎ (SQL CURSOR)	134
Khái niệm	134
Cách tạo và ứng dụng	134
BÀI TẬP THỰC HÀNH	136
Chương 8. QUẢN LÝ BẢO MẬT VÀ NGƯỜI DÙNG TRONG SQL SERVER ... 137
KHÁI NIỆM	137
TẠO VÀ QUẢN LÝ NGƯỜI DÙNG ĐĂNG NHẬP	137
Sử dụng Enterprise Manager	137
Sử dụng Query Analyzer:	139
CẤP PHÁT QUYÈN CHO NGƯỜI DÙNG	139
Sử dụng Enterprise Manager	139
Sử dụng Query Analyzer	140
THU HỒI QUYÈN ĐÃ CẤP PHÁT CHO NGƯỜI DÙNG	142
Xóa quyền truy xuất CSDL hiện hành:	142
Xóa bỏ quyền thực thi đã cấp hoặc từ chối trên CSDL:	142
Xóa bỏ quyền tạo đối tượng đã cấp hoặc từ chối trên CSDL:	143
TÀI LIỆU THAM KHẢO	144
LỜI NÓI ĐẦU
Ngôn ngữ hỏi có cấu trúc (SQL), có tiền thân là SEQUEL, là một ngôn ngữ được IBM phát triển và sử dụng trong hệ cơ sở dữ liệu thử nghiệm có tên là System/R vào năm 1974, chính thức được ANSI/ISO công nhận là một chuẩn ngôn ngữ sử dụng trong cơ sở dữ liệu quan hệ vào năm 1986. Cho đen hiện nay, SQL đã được sử dụng phổ biển trong các hệ quản trị cơ sở dữ liệu thương mại và có vai trò quan trọng trong những hệ thống này.
Được sự động viên của các đồng nghiệp trong Khoa Công nghệ Thông tin, Trưòng Đại học Phạm Văn Đồng, chúng tôi mạnh dạn viết và giới thiệu Bài giảng Hệ quản trị cơ sở dữ liệu 2 SQL Server cho sinh viên bậc Cao đẳng ngành Công nghệ thông tin đen bạn đọc. Trong bài giảng này, chúng tôi không có tham vọng đề cập đen mọi khía cạnh của SQL mà chỉ mong muốn rằng đây sẽ là tài liệu tham khảo tương đối đầy đủ về các câu lệnh thường được sử dụng trong SQL. Bài giảng được chia thành tám chương với nội dung như sau:
Chương 1 TỔNG QUAN VỀ SQL SERVER 2000
Chương 2 GIỚI THIỆU NGÔN NGỮ TRANSACT - SQL
Chương 3 TẠO CƠ SỞ DỮ LIỆU TRÊN SQL SERVER
Chương 4 TẠO VÀ QUẢN LÝ BẢNG (TABLE)
Chương 5 TẠO VIEW (BẢNG ẢO)
Chương 6 STORE PROCEDURE
Chương 7 CHUYÊN TÁC, BẪY LỒI VÀ KIÊU CON TRỎ TRONG SQL SERVER
Chương 8 QUẢN LÝ BẢO MẬT VÀ NGƯỜI DÙNG TRONG SQL SERVER
So với chuẩn SQL do ANSI/ISO đề xuất, bản thân các hệ quản trị cơ sở dữ liệu quan hệ thương mại lại có thể có một số thay đổi nào đó; Điều này đôi khi dẫn đen sự khác biệt, mặc dù không đáng kể, giữa SQL chuẩn và SQL được sử dụng trong các hệ quản trị cơ sở dữ liệu cụ thể. Trong bài giảng này, chúng tôi chọn hệ quản trị cơ sở dữ liệu SQL Server 2000 của hãng Microsoft để sử dụng cho các ví dụ minh hoạ cũng như lời giải của các bài tập.
Chúng tôi hi vọng rằng bài giảng này sẽ thực sự có ích đối với bạn đọc. Chúng tôi rất mong nhận được sự cổ vũ và những ý kiến đóng góp thẳng thắn của các bạn. Cuối cùng, xin gởi lời cảm ơn đen các thầy cô, đồng nghiệp và các bạn sinh viên đã động viên và giúp đỡ chúng tôi hoàn thành bài giảng này.
Quảng Ngãi, 2014
Nguyễn Trí Nhân
Chương 1. TỔNG QUAN VỀ SQL SERVER 2000
GIỚI THIỆU SQL SERVER
Giới thiệu chung về hệ quản trị cơ sở dữ liệu
Hệ quản trị cơ sở dữ liệu (tiếng Anh: Database Management System - DBMS), là phần mềm hay hệ thống được thiết ke để quản trị một cơ sở dữ liệu. Cụ thể, các chương trình thuộc loại này hỗ trợ khả năng lưu trữ, sửa chữa, xóa và tìm kiếm thông tin trong một cơ sở dữ liệu (CSDL). Có rất nhiều loại hệ quản trị CSDL khác nhau: từ phần mềm nhỏ chạy trên máy tính cá nhân cho đen những hệ quản trị phức tạp chạy trên một hoặc nhiều siêu máy tính.
Tuy nhiên, đa số hệ quản trị CSDL trên thị trường đều có một đặc điểm chung là sử dụng ngôn ngữ truy vấn theo cấu trúc mà tiếng Anh gọi là Structured Query Language (SQL). Các hệ quản trị CSDL phổ biến được nhiều người biết đen là MySQL, Oracle, PostgreSQL, SQL Server, DB2, Infomix, v.v. Phần lớn các hệ quản trị CSDL kể trên hoạt động tốt trên nhiều hệ điều hành khác nhau như Linux, Unix và MacOS ngoại trừ SQL Server của Microsoft chỉ chạy trên hệ điều hành Windows.
Ưu điểm của HQTCSDL:
+ Quản lý được dữ liệu dư thừa.
+ Đảm báo tính nhất quán cho dữ liệu.
+ Tạo khả năng chia sẻ dữ liệu nhiều hơn.
+ Cải tiến tính toàn vẹn cho dữ liệu.
Nhược điếm:
+' HQTCSDL tốt thì khá phức tạp.
+ HQTCSDL tốt thường rất lớn chiếm nhiều dung lượng bộ nhớ.
+ Giá cả khác nhau tùy theo môi trường và chức năng.
+ HQTCSDL được viết tổng quát cho nhiều người dùng thì thường chậm.
Giới thiệu về phiên bản SQL Server
Microsoft SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ (relational database management system - RDBMS) do Microsoft phát triển.
SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ mạng máy tính hoạt động theo mô hình khách chủ cho phép đồng thời cùng lúc có nhiều người dùng truy xuất đen dữ liệu, quản lý việc truy nhập hợp lệ và các quyền hạn của từng người dùng trên mạng.
Ngôn ngữ truy vấn quan trọng của Microsoft SQL server là Transact-SQL
Lần lượt các phiên bản của Microsoft SQL Server đã ra đời sau sự kiện này, từ 4.2 sau đó được nâng cấp thành 4.21, 6.0, 6.5, 7.0 và hiện giờ là Microsoft SQL Server 2000, 2005 và mới nhất là Microsoft SQL Server 2008, Microsoft SQL Server 2012, Microsoft SQL Server 2013 .
SQL Server 2005: SQL Server 2005, được phát hành vào tháng 11 năm 2005, là phiên bản tiếp theo của SQL Server 2000. Sử dụng trên nền Net Framework 2.0
SQL ^Server 2008: Đây là phiên bản mới nhất của SQl Server. Ngày 27/02/2008, sử dụng trên nền Net Framework 3.0.
SQL Server 2012: Ngày 6/3/2012, tập đoàn Microsoft tuyên bố phiên bản mới nhất Microsoft SQL Server 2012, nền tảng dữ liệu được sử dụng nhiều nhất trên the giới, sẵn. SQL Server 2012 giúp giải quyết các thách thức liên quan đen sự gia tăng khối lượng dữ liệu bằng cách chuyển các dữ liệu này thành các tầm nhìn mang tính quyết định nhanh chóng, giúp khách hàng quản lý bất kì dữ liệu nào, với bất kì kích thước nào, tại cả cơ sở và trên điện toán đám mây. SQL Server 2012 đưa đen một nền tảng với các công cụ quen thuộc để quản lý dữ liệu dù ở định dạng nào, kích thước lớn tới đâu.
Các thành phần trong SQL Server
SQL Server được cấu thành bởi nhiều thành phần khác nhau, các thành phần có mối quan hệ trong một hệ thống, phối hợp với nhau để tạo thành một giải pháp hoàn chỉnh, nâng cao hiệu quả quản trị, phân tích, lưu trữ dữ liệu:
SQL Server Database: Là cỗ máy cơ sở dữ liệu bao gồm Database Engine, lõi dịch vụ cho việc lưu trữ, xử lý và bảo mật dữ liệu, sao lưu và đồng bộ (Replication), tìm kiếm toàn văn (Full-Text Search) và các công cụ cho việc quản trị dữ liệu quan hệ và XML.
Analysis services: Bao gồm các công cụ cho việc tạo và quản lý tiến trình phân tích trực tuyến (online analytical processing - OLAP) và các ứng dụng khai thác dữ liệu.
Reporting Services: Bao gồm các thành phần server và client cho việc tạo, quản lý và triển khai các báo cáo. Reporting Services cũng là nền tảng cho việc phát triển và xây dựng các ứng dụng báo cáo.
Notification Services: Là nền tảng cho sự phát triển và triển khai các ứng dụng tạo và gửi thông báo. Notification Services có thể gửi thông báo theo địch thời đen hàng ngàn người đăng ký sử dụng nhiều loại thiết bị.
Integration Services: Là một tập hợp các công cụ đồ họa và các đối tượng lập trình cho việc di chuyển, sao chép và chuyển đổi dữ liệu.
Connectivity Components: Là các thành phần cho việc truyền thông giữa client và server, và các thư viện mạng như DB-Library, ODBC và OLEDB.
SQL Server Management Studio (SSMS): Là môi trường được tích hợp cho việc truy xuất, cấu hành, quản trị và phát triển tất cả các thành phần của SQL Server. SSMS kết hợp tất cả các tính năng của Enterprise Manager, Query Analyzer và Analysis Manager, được bao hàm trong các phiên bản trước của SQL Server, thành một môi trường đơn mà cung cấp truy xuất SQL Server để phát triển và quản trị tất cả các mức kỹ năng trên.
Server SQL Configuration Manager: Cung cấp các quản trị cấu hình cơ sở cho các dịch vụ SQL Server (SQL Server services), các giao thức server (server protocols), các giao thức client (client protocols) và các bí danh client (client aliases).
SQL Server Profiler: Cung cap giao diện người dùng đồ họa cho việc giám sát thể hiện của Database Engine hoặc thể hiện của Analysis Services.
- Database Engine Tuning Advisor: giúp tạo các tập tối ưu chỉ so (indexes), indexed views và các phân vùng (partitions).
CÀI ĐẶT SQL SERVER
Khác với một số phần mềm khác như Microsoft Office, Visual Studio, Delphi,... việc cài đặt Microsoft SQL Server không đơn giản. Do đó, sẽ hướng dẫn bạn các bước để tự cài đặt cơ sở dữ liệu Microsoft SQL Server Personal.
Đây là phiên bản làm việc trong môi trường Windows Me, Windows 98, Windows NT Workstation 4.0 hoặc Windows 2000 Professional. Bạn có thể cài đặt và sử dụng Microsoft SQL Server trên máy tính đơn (Stand-Alone) mà không cần phải cài đặt Windows NT Server hoặc không cần trang bị một hệ thống mạng máy tính.
Cấu hỉnh máy tối thiểu để cài đặt SQL Server 2000:
CPU: Pentium 166 Mhz hoặc Pentium Pro.
RAM: 64MB cho Windows 2000 Professional (128MB thì tốt hơn).
Dung lượng đĩa trống: 95MB - 270MB với phiên bản đầy đủ (Full); 50MB với phiên bản tối thiểu (Minimun); 130MB với phiên bản thường dùng (Typical).
Hệ điều hành: Windows Me, Windows 98, Windows NT Workstation 4.0, Windows 2000 Professional.
Màn hình: VGA với độ phân giải 800 x 600 hoặc cao hơn.
Internet Browser: Microsoft Internet Explorer 5.0 trở lên.
Các bước thực hiện việc cài đặt:
Bước 1: Đưa đĩa Microsoft SQL Server Personal vào ổ đĩa CDROM. Neu ổ đĩa không tự động chạy, bạn double click chuột vào tập tin autorun.exe để khởi động chương trình cài đặt.
Bưởc 2: Trong màn hình khởi động cài đặt Microsoft SQL Server Personal, bạn chọn mục SQL Server 2000 Components để bắt đầu việc cài đặt.
Microsoft
SQL Server2000
Personal Edition
SQL Server 200a £tM-npan«ntj I	Browse 5 ecu p.r Upgrade Me Ip
Ộ5ÈÍ SQL Server 200a Ererequlsrtes	Bend the Release Hates
visit Our Web Site
Exit
Hình 1.1.	Màn hình khởi động cài đặt SQL
Bưởc 3: Bạn chọn chức năng Install Database Server để cài đặt cơ sở dữ liệu Microsoft SQL Server Personal.
Hình 1.2.	Màn hình chọn thành phần cài đặt SQL
Đối với hai thành phần còn lại, được dùng để cài đặt các thành phần liên quan đen việc xử lý phân tích dữ liệu trực tuyến (OLAP - Online Analytical Processing) và xử lý truy vấn dữ liệu bằng ngôn ngữ tiếng Anh. Hai thành phần này không nhất thiết phải được cài đặt chung với cơ sở dữ liệu Microsoft SQL Server Personal.
Hình 1.3. Trình cài đặt tiến hành sao chép một số file cần thiết
Hình 1.4.	Chọn Next để tiếp tục cài đặt.
Bưởc 4: Trong màn hình Welcome, nhấn nút Next để tiếp tục việc cài đặt. Chọn Local Computer để cài đặt cơ sở dữ liệu ngay trên máy tính hiện hành, nhấn Next để tiếp tục.
Hình 1.5.	Màn hình chọn cách thức cài đặt SQL
Trong trường hợp bạn muốn thực hiện việc cài đặt cơ sở dữ liệu cho một máy chủ từ xa bên trong một hệ thống mạng cục bộ, bạn chọn chức năng Remote Computer. Khi đó, bạn sẽ cung cấp thêm tài khoản người dùng, mật khẩu và domain của người dùng thuộc nhóm người quản trị để chương trình cài đặt có thể truy xuất vào máy chủ trong quá trình cài đặt.
Bưởc 5: Trong màn hình lựa chọn cài đặt (Installation Selection), bạn chọn chức năng Create a new instance of SQL Server để đơn giản trong quá trình cài đặt, nhấn nút Next để tếp tục.
Hình 1.6.	Màn hình lựa chọn cài đặt SQL
Bước 6: Trong màn hình thông tin người dùng (User Information), bạn nhập vào tên của mình và tên cơ quan. Có thể bỏ trống tên cơ quan. Nhấn nút Next để tiếp tục.
Hình 1.7.	Màn hình nhập thông tin người dùng
Bưởc 7: Trong màn hình thỏa ước bản quyền phần mem (Software License Agreement), Microsoft đưa ra một số thỏa ước cho người dùng khi sử dụng phần mềm. Nhấn Yes để đồng ý các thỏa ước này và tiếp tục quá trình cài đặt.
Hình 1.8. Màn hình thỏa ước bản quyền phần mềm
Hình 1.9.	Màn hình nhập số CDKey phần mềm
Chọn Next để tiếp tục cài đặt.
Bưởc 8: Trong màn hình định nghĩa loại cài đặt (Installation Definition), chọn Server and Client Tools để chỉ định việc cài đặt cơ sở dữ liệu và các tiện ích khác. Nhấn nút Next để tiếp tục.
Hình 1.10.	Màn hình định nghĩa loại cài đặt SQL
Bưởc 9: Trong màn hình định nghĩa tên thể hiện (Instance Name), chọn ô kiểm tra Default nếu muốn tên thể hiện của Microsoft SQL Server Personal trùng với tên của máy tính bạn đang cài đặt. Nhấn nút Next để tiếp tục.
Hình 1.11.	Màn hình nhập tên thể hiện
Trong trường hợp bạn muốn đặt tên khác thì bỏ chọn ô kiểm tra Default, nhập vào tên thể hiện tại mục Instance Name.
Bưởc 10: Trong màn hình chọn loại cài đặt (Setup Type) bạn nên chọn loại thường dùng là Typical nếu dung lượng đĩa cứng còn trống lớn hơn 130MB, ngược lại nếu dung lượng đĩa cứng còn trống lớn hơn 50MB thì nên chọn loại tối thiểu (Minimum). Nhấn nút Next để tiếp tục cài đặt.
Hình 1.12.	Màn hình chọn loại cài đặt SQL
Mặc định thư mục C:\Programs\Microsoft SQL Server sẽ chứa các tập tin sau khi cài đặt, bao gồm các tập tin chương trình và tập tin dữ liệu. Bạn nên chọn một thư mục khác để lưu trữ các tập tin cơ sở dữ liệu với mục đích an toàn hơn. Bạn có thề nhấn nút Browser để thay đổi các thư mục khác nếu muốn.
Bước 11: Trong màn hình chọn tài khoản người dùng khởi động dịch vụ (Services Accounts), cho phép bạn chỉ định tên tài khoản người dùng để có thể khởi động hai dịch vụ chính yếu của Micorsoft SQL Server khi hoạt động là: SQL Server và SQL Server Agent trong trường hợp Microsoft SQL Server chạy trong môi trường Windows NT. Nhấn nút Next để tiếp tục.
Hình 1.13.	Màn hình chọn tài khoản người dùng
Neu hệ điều hành máy tính bạn cài đặt là Windows 98 thì màn hình này sẽ không xuất hiện. Bạn nên chọn sử dụng tài khoản người dùng hệ thống cục bộ (Use the Local System Account) để khởi động cùng lúc cả 2 dịch vụ (Use the same Account for each service) đối với phiên bản Microsoft SQL Server Personal.
Bưởc 12: Trong màn hình chỉ định cơ che xác thực đăng nhập (Authentication Mode), bạn chọn Mixed Mode để chỉ định việc đăng nhập (Login) vào Microsoft SQL Server Personal bang 2 cách: tài khoản người dùng của Windows hoặc tài khoản người dùng trong Microsoft SQL Server. Bạn nhập tài khoản SA cho tài khoản người dùng SA trong Microsoft SQL Server. Nhấn nút Next đề tiếp tục.
Hình 1.14.	Màn hình cơ ... nRowCount 1)
Begin
RollBack Tran
Return -999
End
-- Lấy số chứng từ mà chúng ta đã tăng thành công Select @nSoctuke = Soctu,
@sKytu = Kytu
From Cap_soctu
Where Tenbang= @sTenBang
--Kiểm tra việc lấy dữ liệu có thành công không?
Select @nError = @@Error,
@nRowCount = @@RowCount
--Nếu có lỗi hoặc không lấy được mẫu tin nào if @nError 0 Or @nRowCount 1
Begin
RollBack Tran
Return -998
End
--Tính số chứng từ khi không có lỗi nào hết
Set @sChuoiTam = LTrim(Str(@nSoctuke)) set @sSoctuke = @sKytu +
Replicate('0', 3 - len(@sChuoiTam))+@sChuoiTam
Commit Tran
Return 0
go
Gọi thực hiện thủ tục trên để có được số chứng từ kế tiếp cho bảng PXUAT.
DECLARE @sSoctu Char(4), @nGttv Int EXEC @nGttv = SPUD_CAP_SOCTU_KE 'PXUAT',
@sSoctu output
if @nGttv 0
Print N'Có lỗi cấp chứng từ, xem lại...'
else
Print N'SỐ chứng từ mới là: ' + @sSoctu
Ket quả trả về:
Số chứng từ mới là : X101
Tóm lại: việc sử dụng đối tượng Procdure Store để cung cấp các dữ liệu, các tính toán trên các màn hình nhập liệu, báo cáo bên trong ứng dụng sẽ làm cho tốc độ các xử lý tại nhánh máy chủ được nhanh hơn trong các ứng dụng mô hình khách chủ.
BẪY LỖI (TRIGGER)
Khái niệm
Trigger là một trường hợp đặc biệt của store procedure, nó sẽ có hiệu lực khi chúng ta thay đổi dữ liệu trên một bảng dữ liệu cụ thể, hoặc các xử lý làm thay đổi dữ liệu của các lệnh: insert, update, delete. Trigger có the chứa các lệnh truy vấn từ các bảng khác hoặc bao gồm những lệnh SQL phức tạp.
Một số thuận lợi khi sử dụng trigger:
Trigger chạy một cách tự động: chúng được kích hoạt ngay tức thì khi có sự thay đổi dữ liệu trên bảng dữ liệu. Trigger có thể thực hiện cascade khi việc thi hành có ảnh hưởng đen những bảng liên quan.
Trigger có những hiệu lực ít bị hạn che hơn so với ràng buộc giá trị nghĩa là có thể ràng buộc tham chiếu đen những cột của những bảng dữ liệu khác.
Khi trigger được kích hoạt bởi 1 lệnh Transact-SQL insert để thêm một bộ mới vào bảng AAA thì bộ mới này được lưu tạm thời vào một bảng tạm có tên là inserted có cùng cấu trúc với bảng AAA. Khi kết thúc trigger này thì bộ dữ liệu mới thật sự lưu xuống CSDL.
Tương tự đối với lệnh delete, các bộ dữ liệu bị xóa sẽ chuyển tạm vào bảng tạm deleted.
Cách tạo và ứng dụng
Lệnh tạo Trigger
Create Trigger trigger_name on table_name
For [insert, update, delete]
As
Begin
{Khai báo các bien xử lý}
{Các lệnh Transact-SQL}
End
Lệnh xóa Trigger
Drop Trigger trigger_Name
Ví dụ: Tạo trigger cho thao tác xóa một đầu sách trong bảng Muon.
CREATE TrIGgER tg_delMuon ON muon
FOR delete
AS
Begin
DECLARE @isbn int, @ma_cuonsach smallint
SELECT @isbn = isbn, @ma_cuonsach = ma_cuonsach
FROM deleted
UPDATE cuonsach
SET tinhtrang = yes
WHERE isbn = @isbn AND ma_cuonsach = @ma_cuonsach
End
KIỂU CON TRỎ (SQL CURSOR)
Khái niệm
Neu giải thích một cách ngắn gọn thì cursor tương tự như recordset hay dataset trong các ngôn ngữ lập trình. Nghĩa là chúng ta trích lọc một số dữ liệu vào bộ nhớ, sau đó có thể lần lượt làm việc với từng bản ghi bằng cách di chuyển lần lượt qua từng bản ghi (Move Next...).
Có 3 loại cursors là Transact-SQL Cursors, API Cursors và Client Cursors. Trong đó Transact-SQL và API thuộc loại Server Cursors nghĩa là cursors được load lên và làm việc bên phía server. Trong khuôn khổ bài học này ta chỉ nghiên cứu Transact-SQL cursors.
Cách tạo và ứng dụng
Transact-SQL cursors được tạo ra trên server bằng các câu lệnh Transact-SQL và chủ yếu được dùng trong stored procedure và trigger. Trước het hãy xem qua một ví dụ về cursor:
Viết thủ tục sử dụng Cursor để in ra lần lượt danh sách khách hàng, nhà cung cấp trong danh mục đối tượng (thủ tục đặt tên là usp_DmDtPrint).
CREATE PROCEDURE [dbo].[usp_DmDtPrint]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @_Ma_Dt NVARCHAR(40),
@_Ten_Dt NVARCHAR(20)s
DECLARE DmDtCursor CURSOR FOR
SELECT Ma_Dt, Ten_Dt FROM DmDt
OPEN DmDtCursor
FETCH NEXT FROM DmDtCursor INTO @_Ma_Dt, @_Ten_Dt
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'Đối tượng: ' + RTRIM(@_Ma_Dt) + ' - ' + LTRIM(@_Ten_Dt)
FETCH NEXT FROM DmDtCursor INTO @_Ma_Dt, @_Ten_Dt
END
CLOSE DmDtCursor
DEALLOCATE DmDtCursor
END
Trong ví dụ ở trên ta sẽ trích chọn Ma_Dt và Ten_Dt từ bảng DmDt của Database QLHTK và nạp vào DmDtCursor, sau đó lần lượt in tên của các đối tượng ra màn hình. Đe làm việc với một cursor ta cần theo các bước sau:
Dùng câu lệnh DECLARE CURSOR để khai báo một cursor. Khi khai báo ta cũng phải cho biết câu lệnh SELECT sẽ được thực hiện để lấy dữ liệu.
DECLARE DmDtCursor CURSOR FOR
SELECT Ma_Dt, Ten_Dt FROM DmDt
Dùng câu lệnh OPEN để đưa dữ liệu lên bộ nhớ ảo (memory). Đây chính là lúc thực hiện câu lệnh SELECT vốn được khai báo ở trên.
OPEN DmDtCursor
Dùng câu lệnh FETCH để lấy từng hàng data từ record set. Cụ thể là ta phải gọi câu lệnh FETCH nhiều lần. FETCH tương tự như lệnh Move trong ADO recordset ở chỗ nó có thể di chuyển tới lui bằng câu lệnh FETCH FIRST, FETCH NEXT, FETCH PRIOR, FETCH LAST, FETCH ABSOLUTE n, FETCH RELATIVE n nhưng khác ở chỗ là nó lấy data bỏ vào trong variable (FETCH...FROM...INTO variable_name). Thông thường ta FETCH data trước sau đó loop cho tới record cuối của Cursor bằng vòng lặp WHILE bằng cách kiểm tra global variable @ @FETCH_STATUS (=0 nghĩa là thành công).
FETCH NEXT FROM DmDtCursor INTO @_Ma_Dt, @_Ten_Dt
Khi ta di chuyển tới từng bản ghi ta có the UPDATE hay DELETE tùy theo nhu cầu (trong thí dụ này chỉ dùng lệnh PRINT)
PRINT N'Đối tượng: ' + RTRIM(@_Ma_Dt) + ' - ' + LTRIM(@_Ten_Dt)
Dùng câu lệnh CLOSE để đóng cursor. Một số tài nguyên (memory resource) sẽ được giải phóng nhưng cursor vẫn còn được khai báo và có the OPEN trở lại.
CLOSE DmDtCursor
Dùng câu lệnh DEALLOCATE để phóng thích hoàn toàn các tài nguyên dành cho cursor (kể cả tên của cursor).
DEALLOCATE DmDtCursor
Lưu ý: Trong ví dụ ở trên trước khi dùng Cursor chúng ta phải khai báo trước hai biến @_Ma_Dt và @_Ten_Dt để chứa các giá trị lấy được từ cursor.
BÀI TẬP THỰC HÀNH
Hãy thêm trường ModifiedAt vào tất cả các bảng trong Database QLHTK sau đó viết Trigger để lưu lại thời gian cập nhật vào trường này.
Hãy viet Trigger đổi mã cho tất cả các danh mục.
Chương 8. QUẢN LÝ BẢO MẬT VÀ NGƯỜI DÙNG TRONG SQL SERVER
KHÁI NIỆM
Mỗi CSDL có 1 hay nhiều users được chỉ định quyền truy xuất dữ liệu. Người quản trị có thể cấp quyền truy xuất CSDL bằng cách tạo một tài khoản đăng nhập (login) SQL Server cho User, thêm User vào CSDL và gán quyền cho User trên CSDL đó. Bao gồm các loại quyền:
Quyền truy cập vào SQL Server.
Quyền truy xuất CSDL.
Quyền thực hiện trên các đối tượng của CSDL.
Quyền xử lý dữ liệu.
TẠO VÀ QUẢN LÝ NGƯỜI DÙNG ĐĂNG NHẬP
Sử dụng Enterprise Manager
Tạo tài khoản đăng nhập (Login Acount):
Mở mục Security, click phải mục Login và chọn New Login...
Trên trang General, nhập tên đăng nhập, chế độ xác nhận, CSDL mặc định.
(Hình 8.1)	(Hình 8.2)
Chú ý: Đăng nhập với SQL Server thường dùng cho việc kết nối quay số và mạng peer-to- peer.
Trang Server Roles: Chọn vai trò quản trị mức Server cho tài khoản đăng nhập
System Administrators Security Administrators Server Administrators Setup Administrators Database Creator Disk Administrators Process Administrators Bulk Administrators
Đặc quyền cao nhất; cho phép thực hiện mọi tác vụ trên SQL Quản lý các server logins.
Cho phép bạn định cấu hình những cài đặt server-wide.
Cho phép thêm và xóa các linked servers, và truy xuất vài SP Tạo và hiệu chỉnh databases.
Quản lý các files trên đĩa.
Quản lý tiến trình đang chạy trong một thể hiện của SQL Server. Thực hiện phát biểu BULK INSERT.
Chú ý: Bất kỳ Users của Windows NT thuộc nhóm BUILTIN\Administrators đều có vai trò sysadmin.
(4) Trang Database Access: Chọn CSDL được phép truy xuất và vai trò của nó trong từng CSDL được chọn.
Fixed Database
Role Description
Public
Vai trò chung cho tất cả người dùng.
db_owner
Quyền cao nhất trong database.
db_accessadmin
Điều khiển truy xuất, cài đặt hoặc xóa user accounts.
db_datareader
Đọc tất cả dữ liệu trên database.
db_datawriter
Thêm, sửa, xóa dữ liệu trên các tables người dùng trong database.
db_addladmin
Thêm, sửa, xóa các đối tượng objects (runs all DDLs).
db_securityadmin
Quản lý các roles, các thành viên của role, giấy phép trong database.
db backupoperator
Cho phép back up database.
db denydatareader
Từ chối quyền truy vấy dữ liệu trong database.
db denydatawriter
Từ chối quyền thay đổi dữ liệu trong database.
Sau khi tạo login, nó tự động nhập vào tập Users của mỗi database được chọn, với tên User trùng với tên Login. Bạn có thể thay đổi thu hồi vai trò của nó trên từng CSDL bằng cách sửa đổi thuộc tính của Login, hoặc chuyển đen tập Users của database và thay đổi thuộc tính hoặc xoá user nào mà bạn không muốn cho truy xuất data của bạn.
Các Login được lưu trong table SysLogins của CSDL Master:
If Exists(Select 1 From Master..SysLogins Where Name = 'Login')
Các User trong một CSDL được lưu trong table SysUsers của CSDL đó
If Exists(Select 1 From SysUsers Where Name = 'User01')
Thay đổi thuộc tính cho Login:
Bấm đúp vào tên Login hoặc click phải và chọn mục Properties
Sử dụng Query Analyzer:
8.2.2.1 Thêm Tài khoản với chế độ xác nhận SQL Server:
SP_ADDLOGIN [ @login = ] 
[, @password = ] 
[, @defdb = ] 
[, @deflanguage = ] 
[, @sid =] 
[, @encriptopt = 'skip_encription' ]
o @defdb: tên CSDL mặc định được mở khi đăng nhập. Neu không chỉ định mặc định là CSDL Master.
o @deflanguage: Ngôn ngữ mặc định
o @sid: varbinary(16): Mã nhận dạng của hệ thong. Neu không chỉ định, hệ thống tự tạo một mã số mới.
o @encriptopt varchar(20): Mặc định Password sẽ được mã hóa khi lưu trong các table hệ thống. Ngoại trừ bạn gán giá trị skip_encryption cho tham số này.
o Thủ tục trả về giá trị 0 nếu thành công, ngược lại trả về giá trị 1.
Ví dụ: Tạo tài khoản tên 'myname', mật khẩu 'mypwd' vào CSDL 'QLDeTai'
Sp_AddLogin 'user01', '01', 'QLDeTai'
Ket quả trả về: New loigin created
Cấp quyền kết nối cho User hay nhóm User của Windows kết nối đến SQL Server:
SP_GRANTLOGIN [@loginame =] 'login'
'Login': |\
Ví dụ: Thêm tài khoản cho user Windows NT [Server4\User01] ket nối đen SQL Server.
EXEC sp_grantlogin ' Server4\User01'
Hay EXEC sp_grantlogin [Server4\User01]
Chú ý: Chỉ được thức hiện bởi những thành viên có vai trò sysadmin và securityadmin.
CẤP PHÁT QUYỀN CHO NGƯỜI DÙNG
Sử dụng Enterprise Manager
Cấp quyền thực thi trên mỗi CSDL:
Chọn database, trong mục Users bấm đúp vào tên User cần hiệu chỉnh (Login-ID). Click nút Permission để chỉ định quyền truy cập dữ liệu trên từng Table, View. Quyềnkiểm tra RB tham chiếu (DRI - Declarative Referential Integrity). Quyền thực hiện các Procdure Store trử.
Chú thích: Quyền kiếm tra RB tham chiếu được sử dụng khi Table A được cấp quyền Update hoặc Insert. Table A có RB FOREIGN KEY với table B, mà table B không được cấp quyền SELECT.
Cấp quyển tạo đối tượng trên CSDL:
o Click phải vào tên CSDL, chọn Properties
o Trong HT Properties, chọn trang Permissions
o Đánh dấu chọn các phát biểu được cần cấp quyền thực hiện cho các User.
Xóa User trên một CSDL:
Click phải vào tên user và chọn Delete.
Tạo User với Login đã có:
Click phải vào mục Users và chọn New Database User...
Chú ý: Bạn có thê thay đôi tên User bâng cách xóa và tạo lại với login cũ.
(Hình 10.3)
Xóa Tài Khoản Đăng Nhập:
Click phải vào tên Login và chọn Delete
Với cách này sẽ xóa bỏ các user trong các CSDL đã chọn cho tài khoản này.
Sử dụng Query Analyzer
Cấp quyền truy xuất CSDL hiện hành cho Login:
Sp_GrantDBAccess [ @loginname = ] 'Tênđăngnhập'
[ [, @name_in_db = ] 'TênUser' ]
Ví dụ:	Use QLDeTai
Go sp_GrantDBAccess 'user01', 'Anh'
Cấp quyền thực thi trên CSDL:
Bao gồm các quyền: Select, Insert, Update, Delete, Reference, Excecute.
GRANT ALL I [,...]
ON [(,..)] | 
TO [,...]
[WITH GRANT OPTION]
[AS ]
o All: Cap tất cả các quyền thực thi Select, Insert, Update, Delete hay Reference trên table hay view; quyền Excecute cho SP.
o TO : Khi cấp quyền cho nhóm hay user của Windows NT, phải chỉ định: l\.
Đe cấp quyền cho nhóm cục bộ Windows NT built-in, dùng từ khóa BUILTIN thay the tên domain hoặc computer name.
Quyền thực thi đã cấp cho role Public được áp dụng cho tất cả users trong CSDL. Quyền thực thi đã cấp cho user Guest được sử dụng cho tất cả Users không được phân quyền truy xuất trên CSDL.
Ví dụ:	USE QLDeTai
Grant
All On DeTai To User01
GO
GRANT
SELECT
ON DeTai
TO public
GO
GRANT
INSERT
, UPDATE,
DELETE ON DeTai TO Mary, [
Corporate\BobJ]
GO
o WITH GRANT OPTION: Cho phép Login cấp quyền đã chỉ định trên đối tượng cho Login khác.
o AS {group | role}: Được dùng khi quyền thực thi trên một đối tượng đã cấp cho nhóm hoặc role, và một User của nhóm hoặc Role muốn cấp quyển thực thi cho User khác không là thành viên của nhóm hoặc Role.
Ví dụ:
table NhatKy được tạo bởi user Lac. Lac cấp quyền SELECT table NhatKy cho Role BanBe với mệnh đề WITH GRANT OPTION để các user thành viên của Role BanBe có thể nhường quyền này cho các user khác không thuộc Role BanBe.
User Hong, là thành viên của BanBe, muốn nhường quyền SELECT table NhatKy cho user Khoa, không là thành viên của role BanBe.
/* User Lac */
GRANT SELECT ON NhatKy TO BanBe WITH GRANT OPTION
/* User Hong */
GRANT SELECT ON NhatKy TO Khoa AS BanBe
Cấp Quyền tạo đối tượng trong CSDL:
GRANT ALL I [,...]
TO [,...]
Bao gồm các lệnh: CREATE DATABASE; CREATE DEFAULT;
CREATE PROCEDURE; CREATE RULE; CREATE TABLE; CREATE VIEW;
BACKUP DATABASE; BACKUP LOG
Ví dụ:GRANT CREATE DATABASE, CREATE TABLE
TO Mary, John, [Corporate\BobJ]
Từ Chối quyền thực thi trên CSDL:
DENY ALL I [,...]
[ (column [,...n ]) ] ON { table I view }
I ON table I view [ (column [,...n ]) ]
I ON stored_procedure
TO [,...] [Cascade]
Ví dụ: USE QLDeTai
GO
GRANT SELECT ON DeTai TO public
GO
DENY SELECT, INSERT, UPDATE, DELETE
ON DeTai TO Mary, John, Tom
THU HỒI QUYỀN ĐÃ CẤP PHÁT CHO NGƯỜI DÙNG
Xóa quyền truy xuất CSDL hiện hành:
Sp_RevokeDBAccess [ @name_in_db = ] 'TênUser'
Ví dụ: Sp_RevokeDBAccess 'Anh'
Xóa bỏ quyền thực thi đã cấp hoặc từ chối trên CSDL:
REVOKE [ GRANT OPTION FOR ]
{ ALL I permission [,...n ] }
[ (column [,...n ]) ] ON { table I view }
I ON { table I view } [ (column [,...n ]) ]
I ON { stored_procedure I extended-procedure }
I ON { user_defined_function }
TO | FROM security _account [,...n ]
[CASCADE]
[ AS { group I role } ]
Ví dụ: Xóa bỏ quyền Select đã từ chối cho User Mary trên table DeTai.
REVOKE SELECT ON DeTai TO Mary
Xóa bỏ quyền tạo đối tượng đã cấp hoặc từ chối trên CSDL:
REVOKE{ ALL | statement [,...n ] } FROM security_account [,...n ]
Ví dụ: REVOKE CREATE TABLE FROM Joe, [Corporate\BobJ]
TÀI LIỆU THAM KHẢO
. Marcilina S. Garcia, Jamie Reding, Edward Whalen, Steve Adrien DeLuca, SQL Server 2000 Administrator’s Companion, Microsoft Press, 2000.
. Microsoft SQL Server 2000 System Administration Training kit
. Trần Nguyên Phong, Bài giảng SQL Server , Đại học khoa học, Đại học Huế.
TRƯƠNG ĐẠI HỌC PHẠM VĂN ĐỒNG
KHOA CÓNG NGHỆ THÔNG TIN
HẸ QUẢN TRỊ CƠ SỞ DỮ LIỆU 2
SQL SERVER
Giảng viên: Th.S Nguyễn Trí Nhân
Quảng Ngãi, 12/2013
	Ể