Bài giảng hệ quản trị cơ sở dữ liệu - Nguyễn Vương Thịnh

Nội dung text: Bài giảng hệ quản trị cơ sở dữ liệu - Nguyễn Vương Thịnh

1. TRƯỜNG ĐẠI HỌC HÀNG HẢI KHOA CÔNG NGHỆ THÔNG TIN BÀI GIẢNG HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU Biên soạn: K.S Nguyễn Vương Thịnh Hải Phòng – 2008
2. 1 BỘ GIAO THÔNG VẬN TẢI TRƯỜNG ĐẠI HỌC HÀNG HẢI KHOA CÔNG NGHỆ THÔNG TIN BỘ MÔN HỆ THỐNG THÔNG TIN BÀI GIẢNG HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU TÊN HỌC PHẦN : CƠ SỞ DỮ LIỆU MÃ HỌC PHẦN : 17402 TRÌNH ĐỘ ĐÀO TẠO : ĐẠI HỌC CHÍNH QUY DÙNG CHO SV NGÀNH : CÔNG NGHỆ THÔNG TIN HẢI PHÒNG - 2008
3. 2 Tên học phần: Hệ quản trị Cơ sở dữ liệu Loại học phần: 4 Bộ môn phụ trách giảng dạy: Hệ thống Thông tin Khoa phụ trách: CNTT. Mã học phần: 17402 Tổng số TC: 4 TS tiết Lý thuyết Thực hành/ Xemina Tự học Bài tập lớn Đồ án môn học 90 45 45 0 x 0 Điều kiện tiên quyết: Không yêu cầu. Mục tiêu của học phần: Cung cấp cho sinh viên những khái niệm cơ bản về hệ quản trị cơ sở dữ liệu, vai trò và chức năng của hệ quản trị cơ sở dữ liệu cũng như quá trình tương tác giữa hệ quản trị cơ sở dữ liệu với các phần mềm ứng dụng cơ sở dữ liệu. Giúp sinh viên tiếp cận với nền tảng hệ quản trị cơ sở dữ liệu Microsoft SQL Server. Nội dung chủ yếu: Khái niệm về CSDL và hệ quản trị CSDL; Các thành phần cơ bản trong MS SQL Server; Ngôn ngữ T-SQL và các đối tượng CSDL; Các tác vụ quản trị hệ thống. Nội dung chi tiết: PHÂN PHỐI SỐ TIẾT TÊN CHƯƠNG MỤC TS LT BT TH KT Chương 1. Giới thiệu 6 1.1. Một số khái niệm cơ bản 1.2. Giới thiệu về SQL Server và mô hình Client/Server 1.3. Cài đặt và cấu hình SQL Server 1.4. Các thành phần cơ bản trong SQL Server Chương 2. Làm việc với CSDL và bảng 9 1 2.1. Làm việc với CSDL 2.1.1. Tổ chức tập tin trong CSDL SQL Server 2.1.2. Định nghĩa CSDL 2.1.3. Sửa đổi CSDL 2.1.4. Xóa CSDL 2.2. Làm việc với bảng dữ liệu 2.2.1. Các kiểu dữ liệu cơ bản 2.2.2. Ràng buộc (Constraint) và thuộc tính cột 2.2.3. Định nghĩa bảng dữ liệu 2.2.3. Sửa đổi bảng dữ liệu 2.2.4. Xóa bảng dữ liệu 2.3. Làm việc với các bản ghi 2.3.1. Thêm bản ghi mới (Insert) 2.3.2. Cập nhật bản ghi (Update) 2.3.3. Xóa bản ghi (Delete) 2.4. Khái niệm về chỉ mục (Index) Chương 3. Truy vấn dữ liệu và bảng ảo (View) 9 1 3.1. Cấu trúc truy vấn cơ bản 3.1.1. Câu lệnh SELECT 3.1.2. Mệnh đề WHERE và biểu thức điều kiện 3.1.3. Một số hàm thường dùng 3.1.4. Truy vấn thống kê với GROUP BY và HAVING 3.1.5. Sắp xếp với ORDER BY 3.2. Truy vấn dữ liệu từ nhiều bảng 3.2.1. Inner Join 3.2.2. Left Outer Join
4. 3 PHÂN PHỐI SỐ TIẾT TÊN CHƯƠNG MỤC TS LT BT TH KT 3.2.3. Right Outer Join 3.2.4. Full Outer Join 3.2.5. Cross Join 3.2.6. Self Join 3.2.7. Non - Equal Join 3.2.8. Union 3.3. Truy vấn lồng nhau (Subquery) 3.3.1. Nested Scalar Subquery 3.3.2. Correlated Subquery 3.4. Bảng ảo (View) 3.4.1. Định nghĩa View 3.4.2. Sửa đổi View 3.4.3. Xóa View Chương 4. Thủ tục thường trú và hàm người dùng 9 1 4.1. Lập trình với T - SQL 4.2. Thủ tục thường trú (Stored Procedure) 4.3. Hàm người dùng định nghĩa (User Defined Fuction) Chương 5. Trigger và Transaction 6 5.1. Trigger và ứng dụng của Trigger 5.1.1. After Trigger 5.1.2. Instead Of Trigger 5.2. Các loại Transaction và ứng dụng 5.2.1. Transaction tự động 5.2.2. Transaction tường minh 5.2.3. Transaction ẩn 5.2.4. Transaction lồng nhau Chương 6: Các tác vụ quản trị hệ thống 6 6.1. Quản lý đăng ký SQL Server (Server Registration) 6.2. Bảo mật và phân quyền người dùng 6.3. Sao lưu (Backup) và phục hồi (Restore) dữ liệu 6.4. Hoạch định tác vụ quản trị Nhiệm vụ của sinh viên: Tham dự các buổi học lý thuyết và thực hành, làm các bài tập được giao, làm các bài thi giữa học phần và bài thi kết thúc học phần theo đúng quy định. Tài liệu học tập: 1. Dương Quang Thiện, SQL Server 2000: Lập trình T - SQL, NXB Văn hóa Sài Gòn, 2007. 2. Paul Turley & Dan Wood, Beginning Transact-SQL with SQL Server 2000 and 2005, Wrox Press, 2006. 3. Brian Knightet al, Professional SQL Server 2005 Administration, Wrox Press, 2007. 4. Ray Rankins, Paul Bertucci, Chris Gallelli, Alex T.Silverstein, Microsoft SQL Server 2005 Unleashed, Sams Publishing, 2007. Hình thức và tiêu chuẩn đánh giá sinh viên: Hình thức thi: thi viết hoặc vấn đáp. Tiêu chuẩn đánh giá sinh viên: căn cứ vào sự tham gia học tập của sinh viên trong các buổi học lý thuyết và thực hành, kết quả làm các bài tập được giao, kết quả của các bài thi giữa học phần và bài thi kết thúc học phần. Thang điểm: Thang điểm chữ A, B, C, D, F. Điểm đánh giá học phần: Z = 0,4X + 0,6Y.
5. 4 Bài giảng này là tài liệu chính thức và thống nhất của Bộ môn Hệ thống Thông tin, Khoa Công nghệ Thông tin và được dùng để giảng dạy cho sinh viên. Trưởng Bộ môn Ngày phê duyệt: / / .
6. MỤC LỤC MỤC LỤC 1 MỞ ĐẦU 4 PHầN I. QUẢN TRỊ SQL SERVER 4 BẮT ĐẦU VỚI SQL SERVER 4 TÌM HIỂU VỀ HỆ QUẢN TRỊ CSDL SQL SERVER 4 MÔ HÌNH HOẠT ĐỘNG CỦA SQL SERVER TRÊN MẠNG MÁY TÍNH. 6 CÁC THÀNH PHẦN CỦA SQL SERVER. 11 CÁC THÀNH PHẦN CỦA SQL SERVER. 11 CÀI ĐẶT SQL SERVER. 13 QUẢN TRỊ SERVER 22 INSTANCE 22 ĐIỀU KHIỂN CÁC DỊCH VỤ CỦA SQL SERVER. 22 QUẢN TRỊ SERVER 26 THIẾT LẬP KẾT NỐI ĐẾN SERVER. 27 CẤU HÌNH KẾT NỐI MẠNG CỦA SERVER. 37 QUẢN TRỊ CÁC CLIENT 38 QUẢN TRỊ CƠ SỞ DỮ LIỆU 45 CẤU TRÚC CƠ SỞ DỮ LIỆU. 45 QUẢN LÝ CƠ SỞ DỮ LIỆU. 50 BẢNG DỮ LIỆU – TABLE 58 CÁC CHUẨN TẮC. 58 THIẾT KẾ BẢNG DỮ LIỆU. 60 TẠO BẢNG DỮ LIỆU. 69 KHÓA INDEX 77 THIẾT KẾ KHÓA INDEX 77 TẠO KHÓA INDEX. 79 XÓA INDEX. 81 KHUNG NHÌN – VIEW 82 KHÁI NIỆM KHUNG NHÌN. 82 TẠO KHUNG NHÌN 82 SỬ DỤNG VIEW 84 THỦ TỤC LƯU TRỮ 86 KHÁI NIỆM THỦ TỤC LƯU TRỮ VÀ HÀM 86
7. PHÂN LOẠI THỦ TỤC LƯU TRỮ 87 THIẾT LẬP THỦ TỤC LƯU TRỮ. 88 SỬA, XÓA THỦ TỤC 95 TRIGGER 96 KHÁI NIỆM TRIGGER. 96 NHỮNG TRƯỜNG HỢP SỬ DỤNG TRIGGER 96 ĐẶC ĐIỂM CỦA TRIGGER. 96 TẠO TRIGGER. 97 SỬA, XÓA TRIGGER 101 XUẤT – NHẬP DỮ LIỆU 102 SERVER LIÊN KẾT – LINKED SERVER. 102 SỬ DỤNG BCP VÀ BULK INSERT NHẬP DỮ LIỆU 110 DETTACH VÀ ATTACH CƠ SỞ DỮ LIỆU. 113 IMPORT VÀ EXPORT CƠ SỞ DỮ LIỆU. 115 EXPORT – XUẤT DỮ LIỆU. 119 SAO LƯU, KHÔI PHỤC DỮ LIỆU 120 NHỮNG LÝ DO PHẢI SAO LƯU VÀ KHÔI PHỤC DỮ LIỆU 120 CÁC LOẠI BACKUP. 120 CÁC MÔ HÌNH PHỤC HỒI DỮ LIỆU. 121 SAO LƯU CƠ SỞ DỮ LIỆU - BACKUP DATABASE. 122 KHÔI PHỤC DỮ LIỆU – RESTORE DATABASE. 123 CHƯƠNG X. PHÂN QUYỀN, BẢO MẬT 125 CHẾ ĐỘ BẢO MẬT – SECURITY MODE 125 SERVER ROLE, DATABASE ROLE. 127 QUẢN TRỊ NGƯỜI DÙNG 131 NHÂN BẢN DỮ LIỆU 133 GIỚI THIỆU VỀ NHÂN BẢN DỮ LIỆU 133 CẤU HÌNH PUBLISHER VÀ DISTRIBUTOR. 139 TẠO PUBLICATION 141 TẠO PUSH SUBSCRIPTION 143 TẠO PULL SUBSCRIPTION. 145 THỰC HIỆN ĐỒNG BỘ DỮ LIỆU. 147 PHầN II. CÂU LỆNH T-SQL 148 ĐỊNH NGHĨA DỮ LIỆU (DATA DEFINITION LAGUAGE - DDL). 148 THAO TÁC VỚI DỮ LIỆU (DATA MANIPULATION LANGUAGE - DML) 153 TRUY VẤN DỮ LỆU 165 2
8. TẠO BẢNG BẰNG LỆNH SELECT INTO. 173 LỆNH COMPUTE BY. 173 TOÁN TỬ UNION 174 TRUY VẤN DỮ LIỆU TỪ NHIỀU BẢNG. 175 TRUY VẤN TỔNG HỢP 183 TRUY VẤN LỒNG NHAU 186 UPDATE, DELETE, INSERT VỚI LỆNH TRUY VẤN LỒNG NHAU. 189 LỆNH READTEXT – ĐỌC TEXT, IMAGE. 190 THAO TÁC DỮ LIỆU NGOÀI 190 MỘT SỐ HÀM CƠ BẢN 193 TRANSACTION – PHIÊN GIAO DỊCH 199 LOCKING – KHÓA. 203 GRAND – GÁN QUYỀN. 206 REVOKE – TƯỚC QUYỀN 211 DENY – TỪ CHỐI QUYỀN. 211 TRỢ GIÚP. 212 PHầN III. PHÁT TRIỂN ỨNG DỤNG VỚI SQL SERVER 213 GIỚI THIỆU. 213 KẾT NỐI VỚI SQL SERVER BẰNG ADO. 213 KẾT NỐI VỚI SQL SERVER BẰNG SQL-DMO 233 3
9. Phần 1. QUẢN TRỊ SQL SERVER BẮT ĐẦU VỚI SQL SERVER TÌM HIỂU VỀ HỆ QUẢN TRỊ CSDL SQL SERVER Giới thiệu SQL Server. SQL Server là hệ thống quản trị cơ sở dữ liệu quan hệ (Relational DataBase Management System- RDBMS) sử dụng các lệnh giáo chuyển Transaction-SQL để trao đổi dữ liệu giữa Client Computer và Server Computer. SQL Server có một số đặc tính sau: Cho phép quản trị một hệ CSDL lớn (lên đến vài tega byte), có tốc độ xử lý dữ liệu nhanh đáp ứng yêu cầu về thời gian. Cho phép nhiều người cùng khai thác trong một thời điểm đối với một CSDL và toàn bộ quản trị CSDL (lên đến vài chục ngàn user). Có hệ thống phân quyền bảo mật tương thích với hệ thống bảo mật của công nghệ NT (Network Technology), tích hợp với hệ thống bảo mật của Windows NT hoặc sử dụng hệ thống bảo vệ độc lập của SQL Server. Hỗ trợ trong việc triển khai CSDL phân tán và phát triển ứng dụng trên Internet Cho phép lập trình kết nối với nhiều ngôn ngữ lập trình khác dùng xây dựng các ứng dụng đặc thù (Visual Basic, C, C++, ASP, ASP.NET, XML, ). Sử dụng câu lệnh truy vấn dữ liệu Transaction-SQL (Access là SQL, Oracle là PL/SQL). Các ấn bản của SQL Server. SQL Server có các ấn bản chính sau: Enterpise Manager: Là ấn bản đầy đủ của SQL Server có thể chạy trên 32CPU và 64GB RAM. Có các dịch vụ phân tích dữ liệu Analysis Service. Standard: Giống như Enterprise nhưng bị hạn chế một số tính năng cao cấp, có thể chạy trên 2CPU, 4GB RAM. 4
10. Personal: Phiên bản này chủ yếu để chạy trên PC, nên có thể chạy trên các hệ điều hành Windows 9x, Windows XP, Windows 2000, Windows 2003 Developer: Là phiên bản tương tự như Enterprise nhưng bị giới hạn bởi số user kết nối đến. Desktop Engine: Là phiên bản một engine chỉ chạy trên desktop và không có giao diện người dùng (GUI), kích thước CSDL giới hạn bởi 2GB. Win CE: Sử dụng cho các ứng dụng chạy trên Windows CE. Trial: Phiên bản dùng thử, bị giới hạn bởi thời gian. SQL Client: Là phiên bản dành cho máy khách, khi thực hiện khai thác sẽ thực hiện kết nối đến phiên bản SQL Server, phiên bản này cung cấp giao diện GUI khai thác cho người sử dụng. SQL Connectivity only: Là phiên bản sử dụng chỉ cho các ứng dụng để kết nối đến SQL Server, phiên bản này không cung cấp công cụ GUI cho người dùng khai thác SQL Server. Các phiên bản này được cài đặt phụ thộc vào bộ cài đặt mà bạn chọn hoặc lựa chọn khai cài đặt (ví dụ phiên bản Enterprise, Standard, Personal, bạn phải chọn theo bộ cài đặt, phiên bản SQL Client, Connectivity, do bạn chọn trong các hộp thoại trong quá trình cài đặt). Một số tính năng của Enterprise manager. Dễ cài đặt Hỗ trợ mô hình Client/Server. Thích hợp trên các hệ điều hành Windows. Hoạt động với nhiều giao thức truyền thông. Hỗ trợ dịch vụ Data Warehousing. Thích hợp với chuẩn ANSI/ISO SQL-92. Hỗ trợ nhân bản dữ liệu. Cung cấp dịch vụ tìm kiếm Full-Text. Sách trợ giúp- Book Online. 5
11. MÔ HÌNH HOẠT ĐỘNG CỦA SQL SERVER TRÊN MẠNG MÁY TÍNH. Mô hình chung SQL Server trên mạng. SQL Server là hệ quản trị CSDL hoạt động trên mạng, có thể thực hiện trao đổi dữ liệu theo nhiều mô hình mạng khác nhau, nhiều giao thức và phương thức truyền tin khác nhau. Trong sơ đồ trên thể hiện ba kiểu kết nối ứng dụng đến SQL Server: Kết nối trên Desktop: Có thể trên cùng máy tính với SQL Server hoặc kết nối qua mạng nội bộ. 6
12. Kết nối qua mạng diện rộng: Thông qua đường truyền mạng xa kết nối đến SQL Server. Kết nối qua mạng Internet: Các ứng dụng kết nối thông qua máy chủ Internet, dịch vụ IIS thực hiện ứng dụng trên Internet (ASP, JSP, ASP.net, ) Mô hình Desktop. Nếu xét trên một máy Desktop sơ đồ kết nối trao đổi dữ liệu được thể hiện như sau: Trên một Desktop có thể có nhiều ứng dụng, mỗi ứng dụng có thể thực hiện thao tác với nhiều CSDL. Mô hình Client/Server. Nếu xét theo mô hình client/server, ứng dụng trao đổi với SQL Server theo sơ đồ sau: 7
13. Như sơ đồ trên nhận thấy SQL Server cho phép các ứng dụng kết nối theo các phương thức sau: OLE DB, ODBC, DB-Library, Embedded SQL, đây là các phương thức kết nối hữ ích cho những nhà phát triển ứng dụng. 8
14. Nếu xem xét cụ thể hơn ta có thể xem sơ đồ sau: Trong sơ đồ trên cho thấy, SQL Server có thể thực hiện trao đổi dữ liệu với các ứng dụngt heo nhiều giao thực truyền tin khác nhau (TCP/IP, NetBeUI, Names Pipes, ), các ứng dụng có thể sử dụng nhiều phương thức kết nối khác nhau (OLE DB, ODBC, DB-Library). 9
15. Mô hình kết nối ứng dụng trên mạng Internet. Nếu xét riêng các ứng dụgn kết nối với SQL Server trên mạng Internet, các máy chủ SQL Server sẽ được quản lý thông qua các hệ thống máy chủ mạng, hệ điều hành mạng, các ứng dụng (COM+, ASP, IIS) sẽ thông qua máy chủ mạng kết nối đến SQL Server, mô hình này có thể áp dụng cho các mạng nội bộ, diện rộng, ứng dụng được khai thác trên trình duyệt Internet Browser. Xem xét mô hình dưới đây: 10
16. CÁC THÀNH PHẦN CỦA 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. Relational DataBase Engine. Đây là một engine có khả năng chứa dữ liệu dưới nhiều quy mô khác nhau, theo dạng bảng, hỗ trợ nhiều phương thức kết nối ADO, OLE DB, ODBC. Replication. Là công cụ dùng nhân bản dữ liệu, bạn có thể tạo một Server khác với bộ dữ liệu giống bộ dữ liệu trên Server chính. Công cụ tạo cơ chế tự đồng bộ dữ liệu giữa Server chính và Server nhân bản. Mục đích của việc tạo Server nhân bản là giảm tải 11
17. cho Server chính, nâng cao hiệu quả phục vụ với số lượng người, phiên giao dịch lớn. Data Transformation Service – DTS. Là công cụ giúp bạn chuyển dữ liệu giữa các Server quản trị CSDL khác nhau, DTS có thể chuyển dữ liệu từ SQL Server sang Oracle, Access, DB, trước khi chuyển dữ liệu DTS định dạng kiểu dữ liệu để chuyển sang hệ quản trị CSDL khác. Analysis service. Là công cụ giúp khai thác phân tích dữ liệu, hay khai phá dữ liệu theo phương thức đa chiều. Từ một tập dữ liệu sẵn có bạn có thể khai phá rồi từ đó đưa ra những nhận định, phân tích, đánh giá và dự đoán theo lĩnh vực nào đố, mỗi chiều trong ngữ cảnh này được coi là một tiêu chí xem xét của dữ liệu. English query. Đây là công cụ tra cứu dữ liệu bằng tiếng anh, cú pháp có thể sử dụng theo văn phạm tiếng anh thông thường. SQL Server tools. Là bộ công cụ cung cấp giao diện cho người quản trị như Enterprise amanger, Query Analyzer , SQL Server sau khi cài đặt SQL Server group gồm những thành phần cơ bản trong group như sau: Một số công cụ quan trong: Enterprise manager, Query Analyzer, Profiler , các công cụ sẽ được giới thiệu khai thác sau. 12
18. CÀI ĐẶT SQL SERVER. Chuẩn bị cài đặt. Tùy theo môi trường của máy tính của bạn mà thực hành cài đặt phiên bản nào, bảng sau là tham số với SQL Server 2000 phiên bản Standard. Computer Intel® hoặc tương đương Pentium 166 MHz hoặc cao hơn Memory (RAM) Enterprise Edition: Tối thiểu 64 MB, 128 MB hoặc nhiều hơn. Standard Edition: Tối thiểu 64 MB. Personal Edition: Tối thiểu 64 MB trên Windows 2000, tối thiểu 32 MB trên các hệ điều hành khác. Developer Edition: Tối thiểu 64 MB. Desktop Engine: Tối thiểu 64 MB trên Windows 2000, tối thiểu 32 MB trên hệ điều hành khác. Hard disk SQL Server database components: Từ 95 đến 270 MB, thông thường 250 MB. Analysis Services: Tối thiểu 50 MB, thông thường130 MB. English Query: 80 MB Desktop Engine: 44 MB Monitor VGA hoặc độ phân dải cao hơn. 800x600 hoặc độ phân dải cao hơn. 13
19. Thực hành cài đặt. - Sử dụng đĩa CD ROM có bộ cài đặt SQL Server 2000 (tuỳ theo yêu cầu của bạn là Standard, Personal hay Enterprise, ) - Chạy trình Autorun.exe (thường tự chạy khi đưa đĩa vào máy tính) Trong màn hình trên ta có một số lựa chọn: SQL Server Components: Sẽ thực hành trong bước tiếp. SQL Server 2000 Prerequisites: Dùng cài đặt những yêu cầu được cung cấp sẵn cho việc cài đặt nếu hệ thống trong máy cài đặt chưa đủ. 14
20. - Chọn SQL Server Components. - Chọn Install Database Server. 15
21. - Chọn Next. Nếu cài đặt SQL Server trên chính máy bạn đang ngồi thì sử dụng Local Computer Nếu cài đặt dùng kết nối với máy khác thì sử dụng Remote Computer sau đó nhập tên máy hoặc chọn vị trí máy bằng cách sử dụng Browse - Chọn next. 16
22. - Chọn tuỳ chọn theo chỉ dẫn (tạo mới, thay đổi cái đã có, thêm các chức năng khác, ). - Trong trường hợp chọn tạo mới (lựa chọn thứ nhất) sau đó ấn Next. - Nhập tên của bạn, tên cơ quan, sau ấn Next, Yes. 17
23. Trong cửa sổ hiện lên 3 lựa chọn: 1. Cài đặt các công cụ truy vấn: Sử dụng cho các máy khách không lưu trữ dữ liệu nhưng có chức năng truy vấn dữ liệu đến SQL Server có CSDL 2. Cài đặt Server và các công cụ truy vấn: Cài đặt SQL Server có dữ liệu và các công cụ của máy khác truy vấn dữ liệu 3. Cài đặt kết nối: Dùng cho các máy chỉ sử dụng kết nối đến Server, thường dùng cài đặt cho các máy sử dụng các ứng dụng kết nối đến server - Chọn lựa chọn 2, sau ấn Next Dùng các lựa chọn để chọn kiểu cài đặt: + Typical: Cài đặt những chức năng cơ bản được hệ thống định sẵn (chức năng thông thường). + Minimum: Cài đặt những chức năng tối thiểu của hệ thống. + Custom: Lựa chọn những chức năng cần cài đặt theo yêu cầu của người dùng. 18
24. Trong cách lựa chọn Custom ta cần thêm bước chọn các chức năng như sau: - Ấn nút Next để tiếp tục. 19
25. Trong cửa sổ trên ta cần nhập tên, mật khẩu của user được đăng ký truy nhập vùng, thông thường SQL Server cài đặt được thực hiện theo quyền Administrator của máy tính chủ, khi đó bạn lựa chọn nút chọn Use a Domain User account. - Ấn nút next để tiếp tục. Trong cửa sổ trên cho phép ta sử dụng 2 lựa chọn: + Lựa chọn thứ nhất: Người dùng sử dụng hệ thống bảo mật của Windows (hệ điều hành của máy chủ cài đặt – thông thường khi cài đặt dùng lựa chọn này). + Lựa chọn thứ hai: Người dùng sử dụng hệ thống bảo mật của Windows và của hệ quản trị CSDL SQL Server. Trong các trường hợp trên đều có thể sử dụng tên và mật khẩu được cung cấp theo vùng (domain) của hệ điều hành. Nếu sử dụng lựa chọn thứ 2 ta sử dụng tên và mật khẩu của người quản trị vùng (Administrator). Đối với SQL Server ta có thể thay tên Administrator bằng tên sa (viết tắt của từ System Administrator). Vấn đề thực hiện chọn chế độ bảo mật nào sẽ được bàn trong những bài sau. 20
26. - Ấn next để tiếp tục. Trong cửa sổ trên ta có 2 lựa chọn: + Per Seat for: Lựa chọn cho phép xác định số thiết bị (khái niệm sẽ được giới thiệu sau) trên mỗi vị trí khai thác hệ thống theo bản quyền được phép của Microsoft. + Processor License for: Xác định số Processor cho phép sử dụng theo bản quyền được cung cấp bởi Microsoft. Ngoài việc đăng kỹ bản quyền tại thời điểm này, ta có thể đăng ký bản quyền trong công cụ điều khiển của Control Panel. 21
27. QUẢN TRỊ SERVER INSTANCE SQL Server hỗ trợ nhiều hoạt động trên mạng, như các mô hình đã xem xét trước ta có thể thiết lập nhiều máy tính cài đặt SQL Server, các máy tính có thể liên kết với nhau, trao đổi dữ liệu với nhau. Tuy nhiên một máy tính cũng có thể thiết lập nhiều hệ thống SQL Server khác nhau, mỗi hệ thống đều có một tên quy định, mỗi hệ thống như vậy gọi là một Instance. Mỗi Instance trên một máy tính được coi như một hệ thống SQL Server độc lập, tương tự như các hệ thống SQL Server cài đặt trên các máy tính khác nhau. ĐIỀU KHIỂN CÁC DỊCH VỤ CỦA SQL SERVER. SQL Server sau khi cài đặt xong, khởi động máy thông thường sẽ được thiết lập có biểu tượng ở góc dưới, trái màn hình như sau: 22
28. Biểu tượng mày chỉ có với máy tính cài đặt phiên bản SQL Server và là biểu tượng của trình quản lý dịch vụ Service Manager. Gồm các dịch vụ cơ bản sau: + Distributed Transaction Coordinator - DTC. + Microsoft Search. + SQL Server. + SQL Server Agent. Các dịch vụ này ta có thể bắt đầu, tạm dựng hoặc kết thúc, mỗi dịch vụ đều điều khiển các ứng dụng, công cụ quản trị của SQL Server. Để thực hiện điều khiển dịch vụ đầu tiên ta làm như sau: Services -> Start/Continue (Pause, Stop) Để dịch vụ khởi động tự động khi khởi động hệ điều hành hãy chọn vào nút chọn Auto-start service when OS starts. 23
29. Dịch vụ MS SQLServer. Dùng quản lý tất cả các file gồm các CSDL mà SQL Server quản lý, là thành phần xử lý tất cả các lệnh của Transact-SQL được gửi từ các trình ứng dụng client, phân phối các nguồn tài nguyên khi có nhiều user cùng truy nhập một lúc. Đây là dịch vụ quản trị cơ bản, khi ngắt dịch vụ này hệ thống sẽ ngưng tất cả các công việc khai thác dữ liệu. Dịch vụ SQLServerAgent. Hỗ trợ các tính năng cho phép lập thời biểu các hoạt dộng theo từng giai đoạn tren SQL Server, hoặc thông báo cho người quản lý hệ thống về những sự cố của hệ thống, bao gồm các thành phần Jobs, Alerts, Operator. Dịch vụ Microsoft Search. Cung cấp dịch vụ tìm kiếm và tìm kiếm văn bản với các phép toán cơ bản sau: + Ký tự (chuỗi): =, >, >=, <, <= được so sánh với một chuỗi hằng. + So sánh chuỗi nhỏ trong văn bản hoặc chuỗi có kích thước lớn, văn bản. 24
30. Dịch vụ MS DTC. Là dịch vụ cho phép trong một phiên giao vận có thể sử dụng dữ liệu được phân phối trên nhiều server khác nhau, thực hiện theo các bước cơ bản sau: + Gọi các thủ tục lưu trữ trên các server xa sử dụng SQL Server + Tự động gọi hoặc tạo các phiên giao vận cục bộ và các giao vận với các máy chủ từ xa + Tạo bộ dữ liệu được cập nhật hoặc được phân phối bởi các server xa. Xem xét sơ đồ hoạt động sau: 25
31. Như sơ đồ trên khi client triệu gọi một thủ tục có sẵn đến server cục bộ, khi có yêu cầu dữ liệu trên server khác, thông qua dịch vụ MS DTC server cục bộ sẽ triệu gọi các thủ tục từ server từ xa, kết quả có thể tạo được bộ dữ liệu được tập trung từ nhiều server khác nhau. QUẢN TRỊ SERVER. Như đã nếu trên mỗi Instance được coi là một hệ thống quản trị CSDL SQL Server và có thể gọi tắt là Server. Server có chức năng quản trị toàn bộ hệ thống của SQL Server (dữ liệu, bảo mật, người dùng, tác vụ, các dịch vụ khác, ). Các ứng dụng hoặc các công cụ khai thác dữ liệu (GUI) sẽ thực hiện khai thác dữ liệu do Server quản lý khi có kết nối đến Server. Tuy nhiên một vấn đề quan trọng là Server đó phải sẵn sàng phục vụ. 26
32. Thực hiện quản trị Server là việc thực hiện các công việc sau: + Bật/tắt/tạm dừng dịch vụ của SQL Server. + Gỡ bỏ hoặc cài đặt Server (Instance). + Thay đổi, nâng cấp phiên bản. THIẾT LẬP KẾT NỐI ĐẾN SERVER. Để khai thác được dữ liệu của hệ thống SQL Server ta phải thực hiện kết nối (connect) đến Server, việc kết nối có thể thực hiện từ các vị trí: Ứng dụng, công cụ khai thác của SQL Server là SQL Client. Trong phần này ta sẽ xem xét việc kết nối từ SQL Client đến Server. 27
33. Mỗi Server khi cài đặt đã có một tên là tên của Instance được đặt. Trên một mạng máy tính nếu có đủ quyền hạn ta hoàn toàn có thể thực hiện kết nối đến Server nói trên. Từ một máy SQL Client có thể thực hiện đồng thời kết nối đến nhiều Server khác nhau, đây cũng chính là ưu điểm của SQL Server. Nếu bạn cài đặt phiên bản SQL Server trên máy tính bạn cũng phải làm tiòan bộ các bước kết nối như SQL Client, phiên bản SQL Server được coi như gồm 2 phần: Hệ thống quản trị, công cụ khai thác SQL Client. Dù sử dụng công cụ nào để khai thác đã được cài đặt trên máy tính của bạn, quyền hạn khai thác, quản trị phụ thuộc vào user thực hiện kết nối. Quản trị Server Group. Server Group là công cụ dùng quản lý các kết nối (sẽ thực hành sau) tương tự như khái niệm thư mục trong hệ điều hành, trong các Server Group chứa các Server Group con hoặc các kết nối đến Server. Các bước thực hiện như sau: - Vào chức năng Enterprise manager như hình dưới 28
34. - Di chuyển vào mức trong bằng cách nhấn vào dấu + của cây các đối tượng. - Chọn New SQL Server group để tạo group mới - Nhập tên group -> Ok 29
35. Các thao tác đổi tên, xóa được thực hiện bằng cách nhấn phải chuột vào group cần thao tác. 30
36. Thiết lập kết nối đến Server (thiết lập Server). Là bước thiết lập kết nối đến Server từ Client, Server khác. Các kết nối được thể hiện bằng tên của Server kết nối đến (hay còn là tên của Instance), chính vì vậy nên tên các kết nối trên một Client là duy nhất, không trùng nhau trong toàn bộ client. Trước khi thực hiện tạo kết nối ta phải chuản bị các tham số sau: + Tên Server (Instance) muốn kết nối đến + User name và Password của Server ta cần kết nối đến (tham số này do người quản trị Server cấp). Cách làm như sau: - Vào Enterprise và chọn Server group - Nhấn nút phải chuột vào cửa sổ bên phải, chọn New SQL Server Registration. 31
37. - Nhập tên Server. - Nhấn Add -> Next 32
38. - Chọn chế độ bào mật (thông thường chọn The Windows account information để chọn chế độ bảo mật của Widnows, phần này sẽ xem xét kỹ trong bài sau) -> Next -> Chọn Server Group. - Nhấn Finish. 33
39. Khi màn hình xuất hiện thông báo Registered successfully là việc thiết lập đã thành công. - Nhấn Close. Sau khi thiết lập xong kết nối xuất hiện trên danh sách các kết nối. 34
40. Kết nối như đã thiết lập có quyền hạn khai thác phụ thuộc vào user kết nối, trong ví dụ trên quyền hạn phụ thuộc vào user đã truy nhập vào Windows, tuy nhiên trong những bài sau sẽ giới thiệu cách thứac tạo user, sử dụng user của SQL Server để thực hiện tạo kết nối và khai thác. Để xóa hoặc sửa thông tin cho kết nối chọn Delete hoặc Edit khi nhấn nút phải chuột vào tên kết nối cần thao tác. Xem và thay đổi tham số cho Server. Để thay đổi tham số cho Server, hãy chọn tên kết nối -> nhấn nút phải chuột -> chọn Properties. 35
41. Tuy nhiên các tham số trên có thể bạn chưa xem xét, nên trong bài này chỉ giới thiệu mã sẽ xem xét một số tham số cơ bản trong như bài liên quan. Bật/tắt/tạm dừng/kết nối/ngắt kết nối Server. Ta có thể thực hiện tạm điều khiển dịch vụ MS SQL Server từ kết nối. - Chọn tên kết nố - Nhấn nút phải chuột 36
42. CẤU HÌNH KẾT NỐI MẠNG CỦA SERVER. Để các Client hoặc các ứng dụng kết nối được đến Server, ta phải cấu hình các phương thưc kết nối phù hợp với kết nối mạng. Kết nối mạng có thể sử dụng kết nối thông qua Proxy, thông qua mạng Internet. Khi sử dụng kết nối nào đi nưa thì trước hết ta phải chọn giao thức phù hợp với giao thức mạng đạng sử dụng. Thực hiện bằng cách chọn Server network utility - Chọn giao thức đưa vào danh sách enabled để sử dụng và đưa vò danh sách Disabled để không sử dụng. - Chọn Properties để chọn cổng, tham số của giao thức. - Chọn Enable WinSock proxy để thực hiện kết nối qua Proxy. - Chọn Force protocol encryption để sử dụng kết nối qua Internet không dùng Fire Wall với SQL Server. 37
43. QUẢN TRỊ CÁC CLIENT. Khi Server đã sẵn sàng cho kết nối, việc tiếp theo là xem xét đến các client kết nối đến server. Trong phần này ta sẽ xem xét cấu hình client kết nối đến server. Các client kết nối đến server đều thực hiện trên cở sở hệ thống truyền tin của mạng máy tính, tuy nhiên các ứng dụng client kết nối đến server để thực hiện khai thác dữ liệu trên server thông qua một số phương thức kết nối sau: - OLE DB: Có 2 kiểu Microsoft OLE DB Provider for SQL Server và Microsoft OLE DB Provider for ODBC. - ODBC: Kết nối thông qua SQL Server Enterprise Manager và SQL Query Analyzer sử dụng SQL Server ODBC. - DB-Library: Sử dụng lệnh SQL Server isql. Cấu hình Net-Library. Như đã xem xét trước mỗi Instance khi cấu hình xác định một địa chỉ và số hiệu cổng riêng, nên việc kết nối thông qua Net-Library là kết nối thông qua địa chỉ và như vậy kết nối đã xácđịnh được đến Instance. Trên Server thông thường được cấu hình theo TCP/IP Sockets và Named Pipes Net-Libraries, trên client thông thường cấu hình theo Thực hiện cấu hình ta sử dụng Client network utility. - Chọn Client network utility. 38
44. - Chọn giao thức và các tham số liên quan tương ứng với server, có thể thực hiện định tên Server sang tên mới trên bảng Alias. Cấu hình ODBC. ODBC viết tắt của từ Open DataBase Connectivity, là công cụ kết nối mở. ODBC được Windows cũng cấp dẵn khi cài đặt, được sử dụng làm kết nối trung gian giữa ứng dụng và các hệ quản trị CSDL (Dbase, Access, SQL Server, Oracle, ). Thông qua ODBC ứng dụng chỉ cần xác định tên của nguồn trong ODBC (gọi là Data Source) và tài khoản khi truy nhâp để thực hiện quyền mà không cần quan tâm đến cơ sở dữ liệu đang nằm ở đâu. Thông thường khi cài đặt hệ quản rị CSDL mới thì Windows sẽ tự cập nhật vào danh sách các Driver điều khiển ODBC của hệ quản trị CSDL đó. Thực hiện tạo ODBC cho SQL Server như sau: - Chọn ODBC trong Administrative tools (Control panel). 39
45. - Chọn bảng Drivers, trong danh sách kiểm tra xem đã có SQL Server chưa Nếu chưa có kiểm tra cách cài đặt SQL Server (thông thường Windows tự cập nhật). - Chọn bảng User DSN (Data Source Name) -> Add. 40
46. - Nhấn Finish. - Nhập tên DSN (đây là tên sẽ được sử dụng cho ứng dụng), thông tin mô tả, tên Server (Instance). - Nhấn Next. 41
47. Trong cửa sổ có 2 lựa chọn: Sử dụng chế độ bảo mật kế thừa của Windows NT hoặc của SQL Server (sẽ xem xét sau), trước hết bạn hãy chọn lựa chọn kế thừa của Windows NT (lúc này quyền khai thác là quyền của người truy nhập vào Windows). - Nhấn Next, chọn hộp chọn Change the default database to -> chọn cơ sở dữ liệu (việc tạo cơ sở dữ liệu sẽ xem xét bài sau, tại bước này bạn hãy chọn một cơ sở dữ liệu ví dụ có tên là Pubs, đây là cơ sở dữ liệu ví dụ do SQL Server tự thiết lập để làm mẫu). - Nhấn Next -> Finish. 42
48. Để kiểm tra kết nối có thành công không bạn nhấn Test Data Source sau đo nhấn Ok để kết thúc và thu được màn hình sau: Trên danh sách các Data Source có tên DONGDO vừa được tạo, Data Source tạo ra sẽ được sử dụng trong ứng dụng client. 43
49. Cấu hình OLE DB. OLE DB là phương thức khá quen thuộc đối với người lập tình CSDL (lập trình trên Desktop hoặc trên Internet). OLE DB sử dụng với nhiều hệ quản trị CSDL khác nhau, mỗi hệ quản trị có cú pháp riêng và chỉ định driver điều khiển cho nó. Với SQL Server thông thường sử dụng 2 phương thức kết nối sử dụng OLE DB: - Microsoft OLE DB Provider for SQL Server (SQLOLEDB): Không sử dụng ODBC, xác định driver cho SQL Server. - Microsoft OLE DB Provider for ODBC: Sử dụng ODBC đã tạo (trong phần trước). 44
50. QUẢN TRỊ CƠ SỞ DỮ LIỆU Trong chương này ta sẽ xem xét cấu trúc vật lý, tạo, xóa, sửa tham số của cơ sở dữ liệu. CẤU TRÚC CƠ SỞ DỮ LIỆU. Chắc hẳn khi nghiên cứu đến hệ quản trị CSDL SQL Server bạn đã xem xét đến các hệ quản trị CSDL như DBase hoặc Access, với hệ quản trị CSDL như trên mỗi cơ sở dữ liệu khi sử dụng (thực hiện mở CSDL) sẽ mở trực tiếp từ tập tin chứa CSDL, tập tin chứa CSDL sẽ có một tập tin chính (ví dụ *.dbf hoặc *.mdb) và tập tin phụ nhưng khi ta thao tác ta chỉ cần quan tâm đến tập tin chính. Nên trong các ứng dụng thông thường ta thường dùng các thao tác mở (open) để mở tập tin chính chứa CSDL và đóng (close) đóng tập tin chính chứa CSDL mà không cần quan tâm đến việc đã kết nối đến CSDL chưa (không có phương thức kết nối). SQL Server quản lý trực tiếp các CSDL, danh sách mỗi Server sẽ gồm danh sách các tên CSDL, tên các CSDL là duy nhất, không trùng nhau. Mỗi CSDL SQL Server sẽ quản lý các cấu trúc vật lý của nó. Chính từ cách thức quản lý như trên mà việc quản trị cơ sở dữ liệu có một số đặc điểm sau: + Để Client khai thác CSDL trước hết phải thực hiện kết nối đến Server quản trị CSDL đó. + Chỉ thực hiện khai thác với các CSDL có tên trong danh sách các CSDL mà Server quản lý. + Không có các phương thức mở CSDL trực tiếp từ tập tin như Dbase hoặc Access. + Khi đã kết nối đến Server, Client chỉ thực hiện được quyền khai thác theo quy định đã định sẵn trong CSDL (phân quyền trong CSDL). Cấu trúc cơ sở dữ liệu. Cơ sở dữ liệu trong SQL Server lưu trữ theo 2 phần: phần dữ liệu (gồm một tập tin bắt buộc *.mdf và các tập tin phụ *.ndf) và phần nhật ký (*.ldf). Như vậy một cơ sở dữ liệu có ít nhất 2 tập tin. Cấu trúc logic trong CSDL gồm các table, view và các object khác. Sau đây là cấu trúc một CSDL. 45
51. Sơ đồ quản trị cơ sở dữ liệu của SQL Server. Cơ sở dữ liệu trong SQL Server chia thành 2 loại: Cơ sở dữ liệu hệ thống (do SQL Server sinh ra khi cài đặt) và cở sở dữ liệu người dùng (do người dùng tạo ta). Cơ sở dữ liệu hệ thống gồm: - Master: Lưu trữ các thông tin login account, cấu hình hệ thống, thông tin quản trị các CSDL, là CSDL quan trọng nên thường được sao lưu để bảo đảm an toàn cho hệ thống. - Tempdb: Chứa các table tạm thời và các thủ tục được lưu trữ tạm thời. Các table và thủ tục nói trên được lưu trữ trong CSDL này phục vụ cho các user. 46
52. - Model: Được sử dụng khi template được sử dụng cho các CSDL được tạo trên một hệ thống. - Msdb: Sử dụng bởi SQL Agent. Tập tin của các CSDL nói trên như sau: Tập tin CSDL Tên tập tin vật lý Kích thước ngầm định master primary data Master.mdf 11.0 MB master log Mastlog.ldf 1.25 MB tempdb primary data Tempdb.mdf 8.0 MB tempdb log Templog.ldf 0.5 MB model primary data Model.mdf 0.75 MB model log Modellog.ldf 0.75 MB msdb primary data Msdbdata.mdf 12.0 MB msdb log Msdblog.ldf 2.25 MB Cấu trúc vật lý của CSDL. Như các cấu trúc các CSDL hệ quản trị CSDL thông thường (Dbase, Access), SQL Server cũng quản lý tập tin dữ liệu của CSDL ở dạng vật lý theo trang (page) và phân đoạn (extent). Page. SQL Server quản lý một page có kích thước là 8KB, như vậy 1MB có 128 page, trong mỗi trang có 96 byte chứa thông tin của trang. Có 8 kiểu page như sau: Tên Nội dung Data Chứa tất cả các kiểu dữ liệu loại trừ text, ntext và image Index Các khóa Index. Text/Image Text, ntext, and image data. Global Allocation Map, Chứa các thông tin định vị của các extent. Secondary Global Allocation Map Page Free Space Chứa thông tin khoảng trống của page. Index Allocation Map Chứa các thông tin về Extent đã sử dụng cho Index và Page. Bulk Changed Map Chứa thông thông tin về các lệnh BACKUP LOG. Differential Changed Chứa các thông tin lệnh BACKUP DATABASE. Map 47
53. Đối với các tập tin nhật ký (*.ldf), các bản ghi được ghi lại liên tục, không phân trang. Dữ liệu trong một trang sẽ bắt đầu lưu trữ từ sau phần thông tin Header, và lưu trữ liên tiếp, mỗi hàng có kích thước tối đa là 8060byte. Riêng đối với dữ liệu kiểu text, ntext, image đây là kiểu dữ liệu phức tạp và có kích thước lơn, SQL Server sẽ có chiến lược quản lý khác, phân tran riêng nhằm tăng hiệu quả truy vấn dữ liệu. Dữ liệu trong SQL Server được lưu trữ trên đĩa và tạo chỉ mục Index theo cấu trúc dữ liệu kiểu B-Tree Plus (có thể tham khảo thêm trong những nội dung cấu trúc dữ liệu nâng cao). Extent. Extent là đơn vị dùng chứa các table và index, mỗi extent có 8 page hay 64KB. SQL Server có 2 kiểu extent: - Uniform: Chỉ dùng lưu trữ cho một đối tượng,. - Mixform: Có thể dùng lưu trữ 8 đối tượng. Cấu trúc Extent như sau: 48
54. File. Tập tin lưu trữ một CSDL trong SQL Server có 3 loại. Primary data file: Là file chính lưu trữ dữ liệu (*.mdf = Master Data File), mỗi CSDL có một file primary, lưu trữ điểm bắt đầu của một CSDL và các điểm kết nối đến các file lưu trữ tiếp theo (sencondary). Secondary data file: Là tập tin lưu trữ dữ liệu sau Primary data file, một CSDL có thể có nhiều tập tin sencondary. Loại tập tin này cho phép một CSDL có thể phân tán dữ liệu ở nhiều nơi trên máy tính hoặc trên mạng. Log file: Là loại tập tin lưu trữ thông tin nhật ký của CSDL. Giả sử tạo một CSDL có tên MyDB, thông thường hệ thống ngầm định các tập tin như sau: 49
55. Các tập tin lưu trữ dữ liệu phân thành từng trang, các trang đánh số id liên tiếp theo từng file: File group. SQL Server sử dụng công cụ file group để giúp người dùng dễ dàng quản lý file, các file lưu trữ dữ liệu của một CSDL có thể nhóm thành từng nhóm, gồm 2 kiểu nhóm chính: - Primary: Là nhóm bắt buộc có, dùng xác định cho file primary (*.mdf) và những file khác. - User-defined: Nhóm do người dùng tạo ta, tự đặt tên để dễ quản lý. QUẢN LÝ CƠ SỞ DỮ LIỆU. Tạo cơ sở dữ liệu. Theo lý thuyết cơ sở dữ liệu, trước khi tạo CSDL ta phải thực hiện phân tích các thông tin liên quan mục đích sử dụng CSDL cho ài toán của mình: Tên CSDL, các table, ràng buộc, tuân theo các chuẩn CSDL (phần này sẽ bàn kỹ trong bài sau) Trong các thao tác với CSDL và đối tượng khác sẽ gồm 2 phần: Phần thao tác theo công cụ wizard và câu lệnh T-SQL. 50
56. Tạo theo công cụ: - Vào Enterprise Manager -> Databases. - Nhấn nút phải chuột/hoặc menu Action -> New Database - Nhập tên CSDL. 51
57. - Xác định tên logic, tên vật lý, tên nhóm của tập tin và các tham số khác. - Xác định tên logic, vật lý, tham số khác tập tin nhật ký. 52
58. Tạo theo câu lệnh. Sử dụng câu lệnh Create Database tạo CSDL, công cụ thực hiện lệnh: - Trong Enterprise Manager -> Databases -> Tools -> SQL Query Analyzer - Chọn CSDL Master. 53
59. - Soạn lệnh trong cửa sổ lệnh: USE master GO CREATE DATABASE QLTV ON ( NAME = Sales_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\qltv_dat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log', FILENAME = 'c:\program files\microsoft sql server\mssql\data\qltv_log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO - Nhấn F5 hoặc nút thực hiện. Công cụ SQL Query Analyzer cho phép bạn thực hiện từng câu lệnh bằng cách bôi đen vào đoạn lệnh cần thực hiện sau đó nhấn F5 hoặc nút thực hiện. Khi tạo CSDL mới thì ạn phải đứng ở vị trí CSDL Master, khi muốn thực hiện lệnh với một CSDL cụ thể đã có nào đó bạn phải chọn vào CSDL đó và thực hiện lệnh. 54
60. Trong đoạn lệnh trên lệnh User Master thực hiện chọn CSDL Master bằng câu lệnh; lệnh Use xác định CSDL thực hiện. Lệnh Go xác định câu lệnh kết thúc và bắt đầu câu lệnh khác, câu lệnh được hiểu là dòng lệnh. Trong lệnh T-SQL một số lệnh khác nhau vẫn có thể nằm trên một dòng lệnh nên trong một số tình huống kich bản câu lệnh không cần sử dụng lệnh Go. Xóa cơ sở dữ liệu. Xóa theo công cụ. - Chọn vào CSDL. - Nhấn nút phải chuột -> Delete. - Chọn Yes. Xóa theo câu lệnh. - Sử dụng lệnh Drop Database Drop Database QLTV Sửa tham số. Sửa theo công cụ. - Chọn CSDL. - Nhấn nút phải chuột 55
61. - Chọn Properties. - Thay đổi tham số khi cần thiết: + Restrict access: Ngăn truy nhập. + Read only: Đặt thuộc tính chỉ đọc. Sửa theo câu lệnh. - Sử dụng câu lệnh Alter Database, ví dụ sau thực hiện thêm tập tin secondary vào CSDL. ALTER DATABASE QLTV ADD FILE ( NAME = QLTV_newfile, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\newf.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO 56
62. - Sửa tham số dựa vào thủ tục hệ thống sp_dboption: + Sửa đổi thuộc tính read only: USE master EXEC sp_dboption 'qltv', 'read only', 'TRUE' + Sửa thuộc tính autoshring : USE master EXEC sp_dboption 'qltv', autoshring, TRUE + Sửa thuộc tính single user: USE master EXEC sp_dboption 'qltv', single_user Mọi câu lệnh liên quan bạn có thể tra cứu, tham khảo trong Book Onlines. 57
63. BẢNG DỮ LIỆU – TABLE CÁC CHUẨN TẮC. Trong thiết kế cơ sở dữ liệu, việc tuân thủ ngặt ngèo những chuẩn là việc hết sức quan trọng, nó giúp cho việc quản trị dữ liệu có hiệu quả, khắc phục dư thừa, thuận lợi trong quản trị dữ liệu lớn, hiệu quả với dữ liệu phức tạp. Gồm 3 chuẩn cơ bản: Chuẩn thứ nhất. Chuẩn thứ nhất xác định cấu trúc của một bảng không thể chứa các trường lặp lại. Ta có thể lấy ví dụ như sau giả sử muốn lưu trữ thông tin một quyển sách, mỗi quyển sách có thể có một hoặc nhiều tác giả tham gia biên soạn, nếu không tuân theo chuẩn thứ nhất như nếu trên thì trong một bảng dữ liệu sách có thể có nhiều trường dữ liệu xác định thông tin tác giả. ID Tên sách NXB Tác giả 1 Tác giả 2 Trong ví dụ trên bạn nhận thấy thống tin Tác giả được lặp lại 2 lần, khắ phục bằng cách tạo ra một bảng lưu trữ danh sách tác giả của sách (sẽ bàn trong chuẩn sau). Chuẩn thứ hai. Chuẩn thứ hai xác định trong các hàng dữ liệu, mỗi cột đều phụ thuộc vào cột khóa chính. Ta xem xét một trường hợp vi phạm chuẩn thứ hai như sau: Giả sử xét tình huống sinh viên mượn sách trong một thư viện, việc mượn sách được nhật ký theo bảng như sau: Id_sach Id_Sinhvien Ngày mượn Sức khỏe sinh viên Xem xét trong bảng trên ta thấy mỗi hàng phụ thuộc vào khóa id_sach và id_sinhvien, nhưng thông tin Sức khỏe sinh viên không phụ thuộc vào id_sach, nên thông tin này cần chuyển sang bảng về thông tin của sinh viên. 58
64. Chuẩn thứ ba. Chuẩn thứ ba xác định bản ghi tuân thủ theo chuẩn thứ hai và không có bất kỳ phần phụ thuộc chuyển tiếp nào. Phần phụ thuộc chuyển tiếp tồn tại khi một bảng chứa một cột đặc trưng. Cột này không phải là khóa nhưng vẫn xác định các cột khác. Ta xem xét một ví dụ vi phạm chuẩn như sau: Giả sử trong thư viện có một bảng liệt kê sách tồn trong kho, khi sinh viên mượn sách số lượng sách mà sinh viên mượn sẽ tăng, nếu nhật ký mượn sách được thực hiện theo bảng sau: Id_sach Id_Sinhvien Ngày mượn Số lượng đã mượn Bảng trên bạn thấy mỗi lần sinh viên mượn sách số lượng sách có mã id_sach mà sinh viên có mã id_sinhvien sẽ tăng lên và tổng số là Số lượng đã mượn, thông tin này là thông tin tích lũy theo id_sach, id_sinhvien, ngày mượn. Theo bảng trên ta thấy không vi phạm chuẩn thứ hai nhưng vi phạm chuẩn thứ ba vì cột Số sách đã mượn là cột phụ thuộc chuyển tiếp, cột này cần phải được chuyển sang bảng khác là bảng Sinh viên mượn sách: Id_sach Id_Sinhvien Số lượng đã mượn Khi nào cần chuẩn tắc. Một cơ sở dữ liệu cần được chuẩn tắc khi: - Dữ liệu lớn, phân tán. - Không xác định rõ nhóm dữ liệu. - Dữ liệu phức tạp. - Bước đầu tiên khi xây dựng ứng dụng. Khi nào không cần chuẩn tắc hóa. Một số tình huống sẽ không cần chuẩn tắc hóa, nếu theo như thiết kế theo chuẩn thì việc đưa ra một mẫu tin truy vấn có thể phải thực hiện truy xuất từ nhiều bảng với nhau, điều này có nghĩa ta phải thực hiện kết hợp các bảng với nhau (tuy theo luật) nên thời gian truy xuất có thể rất lớn mà yêu cầu thực tế đặt ra trong tình 59
65. huống này là phải nhanh, thì truy xuất theo một bảng đã có sẵn là nhanh hơn, sau đây là một số trường hợp không cần chuẩn tác hóa (tùy theo tình huống): - Thông tin tính toán. - Thông tin sự kiện. - Sự phân hoạch. THIẾT KẾ BẢNG DỮ LIỆU. Table (bảng dữ liệu) là một thành phần cơ bản của CSDL, một CSDL được thiết kế từ một hoặc nhiều bảng dữ liệu, mỗi bảng dữ liệu được cấu trúc từ các hàng và cột dữ liệu, mỗi hàng dùng mô tả một đối tượng, vấn đề, sự kiện, cột thể hiện thuộc tính của các đối tượng, sự kiện, của hàng. Dữ liệu cùng cột có cùng kiểu (data type). Ngoài các hàng, cột bảng còn có các khóa, liên kết, ràng buộc, Trước khi bắt tay vào thiết lập bảng dữ liệu trước hết ta phải xác định xem bảng sẽ xây dựng như thế nào, dựa trên một số thông tin sau: - Kiểu dữ liệu trong bảng. - Các cột, kiểu dữ liệu tương ứng (và độ dài nếu cần thiết). - Cột nào cho phép giá trị NULL (là giá trị mà phần dữ liệu thuộc hàng, cột xác định không được gán giá trị nào, vì vậy nên 2 phần tử có cùng giá trị NULL là không bằng nhau). - Giá trị ngầm định (là giá trị mà khi chưa nhập vào nó nhận giá trị này). - Chỉ số Index, khóa chính, khóa ngoài. Kiểu dữ liệu. SQL Server gồm những kiểu dữ liệu sau: Binary: Là kiểu dữ liệu chứa dạng số ở hệ hexa, gồm 3 kiểu dữ liệu Binary, Varbinary, Image. Text: Là kiểu ký tự, chứa chữ cái, ký hiệu, số, gồm những kiểu dữ liệu sau: - Char: Kiểu ký tự, khi xác định độ dài thì độ dài trong CSDL sẽ xác định theo độ dài đặt trước mà không theo độ dài dữ liệu thực có, không sử dụng với ký tự dạng Unicode, độ dài tối đa là 8000. - Nchar: Tương tự như Char nhưng sử dụng với ký tự Unicode, độ dài tối đa 4000. 60
66. - Nvarchar: Tương tự như NChar nhưng kích thước trong CSDL sẽ là kích thước thực dữ liệu hiện có, không tính theo kích thước đặt trước, kích thước tối đa là 4000. - Varchar: Tương tự như Nvarchar nhưng không hỗ trợ Unicode. - Text: Kiểu văn bản, chứa cả ký tự xuống dòng, lưu trữ theo dạng văn bản, có kích thước lớn, có thể lên đến vài Gb, cơ chế quản lý kiểu dữ liệu theo dạng con trỏ và cách thức chèn và cập nhật sẽ khác, kiểu dữ liệu này không hỗ trợ cho Unicode. - Ntext: Tương tự như Text nhưng có hỗ trợ Unicode. Data/Time: Kiểu dữ liệu ngày, thời gian, ngày và thời gian, gồm 2 kiểu: - DateTime: Đầy đủ cả ngày và thời gian. - SmallDataTime: Chỉ ngày hoặc thời gian. Numeric: Dữ liệu kiểu số, gồm các kiểu dữ liệu sau: - Int, smallint, tinyint, bigint: Số nguyên - Float, real, decimal, numeric: Số thực. Monetary: Tiền tệ: - Money, Smallmoney. Bit: Kiểu số 0, 1. Sql_variant: Là kiểu dữ liệu xác định theo kiểu dữ liệu khác, một cột dữ liệu được định nghĩa dữ liệu kiểu này có thể lưu trữ nhiều dữ liệu có kiểu khác nhau trong cùng một bảng. Ví dụ có thể lưu trữ nhiều kiểu dữ liệu int, binary, char, nhưng không chứa dữ liệu kiểu text, ntext, image, timestamp, sql_variant. Timestamp: Là kiểu dữ liệu có kích thước 8 byte, lưu trữ dạng số nhị phân do hệ thống tự sinh ra, mỗi giá trị timestamp trong CSDL là duy nhất. Table: Là kiểu dữ liệu đặc biệt lưu trữ tập hợp các hàng (dạng bảng), mục đích sử dụng chính là lưu trữ tạm thời tập hợp các hàng sau truy vấn. Text in row. Như xem xét trước, dữ liệu kiểu char, varchar có độ dài tối đa là 8000byte, dữ liệu kiểu text, ntext có 2 kiểu lưu trữ: lưu trữ trực tiếp, lưu trữ quản lý theo kiểu con trỏ. 61
67. - Đối với lưu trữ theo kiểu trực tiếp, kích thước tối đa đối với text là 8000, đối với ntext là 4000 (kích thước 1 ký tự ở mã Unicode là 2 byte, mã không Unicode là 1 byte). - Lưu trữ, quản lý theo con trỏ kích thước lên đến GB. Để lưu trữ dữ liệu theo kiểu con trỏ đầu tiên ta phải đặt chức năng Text in row về trạng thái On, thuộc tính này hiệu ứng cả với kiểu dữ liệu image. Sử dụng thủ tục sp_tableoption để thay đổi thuộc tính, thuộc tính thay đổi theo bảng dữ liệu. Giả sử bật chức năng text in row như sau: Sp_tableoption N'TacGia', 'text in row', 'ON' Tắt chức năng text in row như sau: Sp_tableoption N'TacGia', 'text in row', 'OFF' Để cập nhật dữ liệu khi thuộc tính được bật, ta phải dùng lệnh READTEXT, UPDATETEXT, WRITETEXT (sẽ bàn kỹ câu lệnh này sau). Auto number. Đặt cột dữ liệu kiểu số, tăng tự động khi một hàng được thêm, cột kiểu này không sửa dữ liệu. Dữ liệu kiểu này tương ứng với việc khi thêm hàng dữ liệu chèn thêm giá trị dạng số theo hàm NewID(). Ràng buộc dữ liệu. Để có một CSDL khi lưu trữ dữ liệu có độ tin cậy, độ chính xác cao, nhanh và thuận tiện trong khai thác dữ liệu thì toàn vẹn dữ liệu là vấn đề hết sức quan trọng. Khi ràng buộc được thiết lập, dữ liệu khi nhập vào CSDL sẽ được kiểm soát, độ tin cậy thông tin sẽ được bảo đảm. Có nhiều kiểu ràng buộc dữ liệu, một CSDL có thể gồm một hoặc nhiều ràng buộc, ràng buộc có thể trên một bảng, trên nhiều bảng. Toàn vẹn dữ liệu chia thành 4 loại: - Toàn vẹn thực thể (Entry integrity): Mỗi thực thể đều được xác định theo một khóa, khi biết khóa ta hoàn toàn có thể xác định được thực thể tương ứng. Khóa như vậy coi là khóa chính. 62
68. - Toàn vẹn theo miền (Domain integrity): Là loại toàn vẹn có hiệu ứng với các cột dữ liệu trong một phạm vi nào đó, ví dụ kiểu dữ liệu cũng là một dạng của toàn vẹn miền, ràng buộc theo khóa check cũng là toàn vẹn theo miền. - Toàn vẹn dạng tham chiếu (Referential integrity): Khi một bảng có quan hệ với một bảng khác theo một mối quan hệ, trong mối quan hệ đó sẽ có một khóa chính (như phần toạn vẹn thực thể) và một khóa ngoài, khóa ngoài sẽ là khóa tham chiếu của khóa chính, giá trị của khóa ngoài sẽ thuộc tập các giá trị của khóa chính hoặc giá trị NULL. Ràng buộc kiểu quan hệ (Relationship) gọi là toàn vẹn kiểu tham chiếu. - Toàn vẹn do người dùng định nghĩa (User-defined integrity): Là toàn vẹn do người dùng định nghĩa, quy định dữ liệu nhập vào theo quy cách, giá trị được kiểm soát chặt chẽ, toàn vẹn kiểu này cũng có thể xây dựng trên cơ sở các toàn vẹn trước. Reference integrity Entry integrity Data integrity User-defined Domain integrity integrity 63
69. Bốn loại toàn vẹn nói trên ta có thể thống kê tương ứng với các khóa, quy tắc, ràng buộc trong SQL Server như sau: Kiểu toàn vẹn Công cụ trong SQL Server Entry integrity 1. Ràng buộc Primary key 2. Ràng buộc Unique 3. Cột Identity Domain integrity 1. Giá trị ngầm định Default 2. Ràng buộc khóa ngoài Foreign Key 3. Ràng buộc Check 4. Thuộc tính NOT NULL Referential integrity 1. Ràng buộc Forelgn Key 2. Ràng buộc Check User-defined integrity 1. Rules 2. Stored procedures 3. Triggers Các khóa. Khóa chính – Primary Key. Là một hoặc tổ hợp nhiều cột dữ liệu xác định duy nhất trong một bảng, giá trị khóa chính luôn khác NULL. Ví dụ: Bảng danh sách tác giả viết sách. Trong ví dụ trên bảng dữ liệu có khóa chính là một cột dữ liệu id, khi cột xác định là khóa chính bên cạnh xuất hiện biểu tượng chìa khóa, thuộc tính Allow Nulls không được đanh dấu. 64
70. Ví dụ: Bảng dữ liệu lưu trữ thông tin nhật ký mượn sách. Trong ví dụ trên bảng dữ liệu có khóa chính được tổ hợp từ 3 cột dữ liệu id_sach, id_sinhvien, ngay_muon, ba cột trên xác định duy nhất một sinh viên được mượn một loại sách trong một ngày (giả sử quy chế xác định như vậy), các cột tham gia khóa chính gọi là candidate key. Khóa ngoài. Theo chuẩn thiết kế CSDL, khi lưu trữ thông tin sách phải có một cột chứa thông tin nhà xuất bản. Một nhà xuất bản có thể xuất bản nhiều quyển sách và một quyển sách chỉ xuất bản ở một nhà xuất bản. Nên trong thiết kế ta phải có: + Bảng dữ liệu lưu trữ danh sách các nàh xuất bản: Có khóa chính đại diện cho nhà xuất bản. + Bảng dữ liệu lưu trữ sách: Có chứa thông tin nhà xuất bản. + Quan hệ giữa nhà xuất bản và sách: Mã khóa nhà xuất bản thuộc bảng nhà xuất bản và thông tin nhà xuất bản thuộc bảng sách, cột thông tin nhà xuất bản thuộc bảng sách tham gia quan hệ trên gọi là khóa ngoài (Foreign key). Cột dữ liệu là khóa ngoài có thể có quan hệ với nhiều khóa chỉnh ở nhiều bảng, một bảng có thể có nhiều khóa ngoài, khóa ngoài có thể có giá trị NULL, giá 65
71. trị của khóa ngoài luôn nằm trong tập giá trị của khóa chính trong mối quan hệ đã thiết lập. Khóa ngoài và khóa chính phải có cùng kiểu dữ liệu, cùng kích thước. Ràng buộc Unique. Unique là ràng buộc xác định trên một hoặc tổ hợp cột dữ liệu, cột hoặc tổ hợp cột dữ liệu được xác định ràng buộc loại này là duy nhất. 66
72. Một bảng dữ liệu có thể có nhiều ràng buộc duy nhất, một cột trongmrang buộc loại này cho phép nhận giá trị NULL, rang buộc duy nhất có thể sử dụng làm tham chiếu cho khóa ngoài. Ràng buộc Check. Là ràng buộc khống chế dữ liệu nằm trong một phạm vi nào đó. Ràng buộc này sẽ kiểm tra dữ liệu khi nhập vào. 67
73. Giá trị ngầm định – Default. Giá trị gán cho cột dữ liệu khi thêm bản ghi và chứa nhập dữ liệu vào cột này. 68
74. TẠO BẢNG DỮ LIỆU. Sau khi đã xác định đầy đủ các thông tin thiết kế CSDL, bước tiếp theo là thực hiện tạo cấu trúc CSDL. Để tạo cấu trúc CSDL bước quan trọng là tạo bảng dữ liệu. Khi tạo CSDL hệ thống tự động tạo ra một số bảng dữ liệu ngầm định, các bảng dữ liệu này sẽ cung cấp, quản lý thông tin quản trị của CSDL, cung cấp một số hàm hệ thống trợ giúp bgười dùng. Tạo bằng công cụ. - Chọn CSDL - Chọn Tables - Nhấn phải chuột ở cửa sổ bên phải 69
75. - Chọn New Table. Đặt khóa chính. Để xác định khóa chính ta thực hiện chọn những cột tham gia khóa bằng cách giữ phím shift và chọn chuột -> nhấn chuột phải -> chọn Set primary key. 70
76. Xác định Identity. - Chọn cột dữ liệu -> Chọn yes trong mục Identity -> đặt seed (giá trị khởi đầu) -> đặt increment (bước tự động tăng). Tạo bảng bằng câu lệnh. Giả sử cần tạo bảng tên NXB có cấu trúc như sau: 71
77. Sử dụng lệnh Create table, kịch bản câu lệnh như sau: Create table NXB(id int not null primary key identity(1,1), Ten Nvarchar(100), Ghi_chu Ntext) Sửa cấu trúc bảng. Sử dụng công cụ. - Chọn bảng cần sửa đổi của CSDL. - Nhất phải chuột -> chọn Design Table. - Thực hiện sửa cấu trúc bảng. Sửa dụng câu lệnh. Để sửa cấu trúc bảng dữ liệu ta sử dụng câu lệnh Alter table. - Thêm một cột vào bảng đã có: ALTER TABLE NXB ADD Dia_chi NVARCHAR(100) NULL - Xóa cột từ bảng đã có. ALTER TABLE NXB_Drop column_Dia_chi 72
78. Xóa bảng. Sử dụng công cụ. - Chọn bảng - Nhất chuột phải - Chọn Delete -> Yes. Sử dụng lênh. (Drop Table) Drop Table NXB Bảng dữ liệu có tham gia mối quan hệ Relationship khi xóa bạn cần chú ý: Nếu bảng chứa khóa ngoài thì việc xóa thực hiện bình thường, nếu bảng chứa khóa chính của mối quan hệ thì không xóa được. Nhập dữ liệu vào bảng. Sử dụng công cụ. - Chọn bảng dữ liệu - Nhất chuột phải -> Open Table -> Return all rows 73
79. - Nhập dữ liệu theo đúng quy cách kiểu dữ liệu, ràng buộc nếu có. Việc sửa, xóa được thực hiện trực tiếp. Đối với các cột là dạng số, tăng tự động không cần nhập dữ liệu. Để lưu lại dữ liệu đã nhập bạn di chuyển con trỏ sang hàng khác. Sử dụng câu lệnh. Sử dụng lệnh Inert into. Insert into NXB(Ten, Dia_chi) values(N’Kim Đồng’, N’hà Nội’) Nếu cột dữ liệu hỗ trợ Unicode thì trước giá trị nhập vào bạn phải thêm kèm ký tự N (như ví dụ trên). Tạo, sửa ràng buộc, khóa. Phần này nhằm thực hiện thao tác với các rang buộc, khóa: ralationship, check, unique, Sử dụng công cụ. - Chọn chức năng Design table. - Chọn biểu nút Manage Indexes/Keys 74
80. - Chọn bảng tương ứng. Sử dụng câu lệnh. Để cụ thể hơn ta thực hiện theo ví dụ có sơ đồ cấu trúc sau: 75
81. Create Table NXB(id int not null primary key identity(1,1), Ten Nvarchar(100), Ghi_chu Ntext, Dia_chi nvarchar(150)) Go Create Table ChungLoai(id int not null primary key identity, ten nvarchar(50), ghi_chu nvarchar(200)) Go Create Table Sach(id int not null primary key identity, Ten nvarchar(100), so_trang int default(0), Chung_loai int references Chungloai(id), NXB int references NXB(id), Tom_tat ntext) 76
82. KHÓA INDEX THIẾT KẾ KHÓA INDEX. Index là một khóa quan trọng đối với CSDL đặc biệt là cơ sở dữ liệu lớn. Index được thiết lập từ một hoặc nhiều cột dữ liệu của bảng dữ liệu. các giá trị của khóa Index sẽ được sắp xếp và lưu trữ theo một danh sách (bảng khác). Mỗi giá trị trong khóa Index là duy nhất trong danh sách, mỗi giá trị khóa Index sẽ liên kết đến giá trị trong bảng dữ liệu (liên kết dạng con trỏ). Việc lưu trữ dữ liệu của bảng có khóa Index được thực hiện theo cấu trúc cấy B-Tree nhằm tăng tốc độ truy xuất dữ liệu đối với ổ đĩa (thiết bị thứ cấp). Khi tìm kiểm một giá trị trong cột dữ liệu, mà cột này tham gia tạo khóa Index, đầu tiên câu lệnh xác định vị trí của giá trị nằm trong khóa Index bằng phép duyệt cây, sau đó thực hiện tìm theo liên kết đến bản ghi chứa giá trị tương ứng với khóa trong bảng. 77
83. Sơ đồ ví dụ dưới đây gồm khóa Index được tạo từ cột emp_id của bảng employees. Việc thiết kế khóa Index dựa trên nhu cầu truy vấn, chèn dữ liệu trên một bảng, xác định dựa vào một số tham số sau: + Cột thường được sử dụng làm khóa truy vấn dữ liệu (xác định cột tham gia khóa Index). + Tập lệnh thường sử dụng try vấn cần tốc độ cao (xác định tập cột tham gia truy vấn). + Dữ liệu nhập vào bảng có khóa Index cần nhanh hơn hay truy vấn cần nhanh hơn (xác định đặt clustered hoặc nonclustered). + Lượng dữ liệu nhập đồng loạt nhiều hay ít (xác định tham số fillfactor). Clustered Index. Khi khóa đặt thuộc tính Clustered, dữ liệu của bảng sẽ được sắp xếp vật lý trên đĩa, như vậy khi thiết kế khóa dạng này dữ liệu được chèn và sẽ tìm đúng vị trí trên địa để lưu trữ (vùng đĩa dành cho bảng dữ liệu), chính vì vậy mà có thể xảy ra trường hợp phải dịch chuyển danh sách các giá trị đã có ở đĩa. Những việc tạo khóa Index dạng này sẽ không cần sắp xếp giá trị ở dạng logic mà khi truy nhập đĩa đã bảo đảm dữ liệu được sắo xếp. Bảng dữ liệu chỉ có thể tạo tối đa một khóa Lustered Index. 78
84. Nonclustered Index. Dữ liệu Index không sắp xếp ở dạng vật lý mà chỉ sắp xếp logic, dữ liệu của bảng lưu trữ giá trị khóa Index được sắp xếp, nhanh trong nhập dữ liệu. Unique Index. Xác định dữ liệu của cột tham gia khóa Index không lặp lại. Fill Factor. Khi tạo khóa Index, dữ liệu tham giá tạo khóa Index sẽ được phân theo mức của B-Tree, các mức được phần theo page dữ liệu, giá trị Fill factor xác định phần khoảng trống tối đa của page theo tỷ lệ phần trăm. Nhờ khoảng trống này mà tốc độ bố trí cấu trúc Index, tốc độ truy lục thông tin trong cây được cải thiện. TẠO KHÓA INDEX. Tạo theo công cụ. - Chọn chức năng Design table - Vào bảng Index manager. - New 79
85. - Chọn các cột tham gia tạo khóa Index - Đặt tham số. Tạo theo câu lệnh. - Sử dụng trong câu lệnh Create Table, Alter Table. - Sử dụng lệnh Create Index. CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ , n ] ) Ví dụ: CREATE INDEX sach_idx ON sach (id) 80
86. XÓA INDEX. Sử dụng công cụ. - Vào Index amnager - Chọn khóa Index -> Delete Sử dụng câu lệnh. Sử dụng lệnh Drop Index. Drop Index Sach(sach_idx) 81
87. KHUNG NHÌN – VIEW KHÁI NIỆM KHUNG NHÌN. Khung nhìn (View) là một bảng tạm thời, có cấu trúc như một bảng, khung nhìn không lưu trữ dữ liệu mà nó được tạo ra khi sử dụng, khung nhìn là đối tượng thuộc CSDL. Khung nhìn được tạo ra từ câu lệnh truy vấn dữ liệu (lệnh Select), truy vấn từ một hoặc nhiều bảng dữ liệu. Khung nhìn được sử dụng khai thác dữ liệu như một bảng dữ liệu, chia sẻ nhiều người dùng, an toàn trong khai thác, không ảnh hưởng dữ liệu gốc. Có thể thực hiện truy vấn dữ liệu trên cấu trúc của khung nhìn. TẠO KHUNG NHÌN. Sử dụng công cụ. - Chọn chức năng Views của CSDL. 82
88. - Nhấn phải chuột. - Chọn New View. - Thêm các bảng tham gia câu lệnh truy vấn dữ liệu cho View 83
89. - Soạn lệnh truy vấn hoặc đánh dấu các cột tham gia tạo View. - Sửa đổi lệnh Select theo ý muốn. - Ghi kịch bản -> đặt tên view. Tạo theo câu lệnh. Sử dụng lệnh Create View: CREATE VIEW VIDU as SELECT dbo.authors.au_lname, dbo.authors.au_fname, dbo.titles.title FROM dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id SỬ DỤNG VIEW. - Chọn View - Nhấn nút phải chuột. 84
90. Thực hiện các chức năng tương tự table. 85
91. THỦ TỤC LƯU TRỮ KHÁI NIỆM THỦ TỤC LƯU TRỮ VÀ HÀM. Thủ tục lưu trữ có thuật ngữ Stored Procedure, là một đối tượng của CSDL tương tự như khung nhìn, thủ tục lưu trữ có thể tạo ra từ công cụ và câu lệnh. THủ tục được thực hiện như câu lệnh (có thể thực hiện từ SQL Query analyzer, các vị trí gọi câu lệnh T-SQL). Thủ tục lưu trữ được kết cấu từ một kịch bản câu lệnh T-SQL, thủ tục có những đặc điểm cơ bản sau: + Truyền tham số. + Gọi thủ tục khác. + Trả về các giá trị tham số, chuyển giá trị tham số cho các thủ tục được gọi. + Trả về giá trị trạng thái thủ tục là thành công hay không thành công. 86
92. Thủ tục lưu trữ có nhiều ưu điểm so với thực hiện câu lệnh T-SQL từ các máy khách: + Lập trình theo module: Thủ tục được thiết lập trong từng CSDL một lần, có thể gọi thực hiện nhiều lần trong một ứng dụng, có thể gọi từ nhiều ứng dụng. + Thực hiện nhanh hơn: Khi cần thực hiện một lượng lớn câu lệnh T-SQL, thủ tục lưu trữ thực hiện nhanh hơn vì khi máy chủ nhận được nhiều cầu lệnh cùng một lúc đều phải kiểm tra tính hợp lệ quyền của tài khoản từ máy khách và các tham số khác. Khi thủ tục cần gọi nhiều lần trên các máy khách thì thủ tục thực hiện một lần đầu tiên, những lần sau máy khách sẽ chạy thủ tục đã được biên dịch. + Làm giảm lưu lượng trên mạng: Thay cho vì máy khách phải gửi nhiều dòng lệnh từ các ứng dụng đến máy chủ, khi sử dụng thủ tục thì nó chỉ cần gửi một lệnh, từ đó dẫn đến lưu lượng thông tin lệnh truyền qua mạng giảm. + An ninh bảo mật hơn: Khi không muốn cho một user trực tiếp khai thác một đối tượng hay bảng dữ liệu nào đó, mà cần cho user đó được khai thác thì thủ tục có thể giúp bạn gán quyền khai thác cho người đó. Việc gán quyền khai thác như nói trên sẽ giúp cho vấn đề an ninh bảo mật trong CSDL tốt hơn. PHÂN LOẠI THỦ TỤC LƯU TRỮ. Thủ tục lưu trữ được phân thành 5 loại như sau: System Stored Procedure. Là thủ tục được lưu trữ tỏng CSDL Master, thủ tục loại này được bắt đầu bằng chữ sp_ thủ tục loại này thường được sử dụng trong quản trị CSDL và an ninh bảo mật. Ví dụ: Muốn biết tất cả các tiến trình đang thực hiện bởi user nào: sp_who @loginame='sa' Kết quả: 87
93. Local Stored Procedure. Đây là loại thủ tục thường dùng nhất, nằm trong CSDL do người dùng tạo ra, thực hiện một công việc nào đó. Thủ tục loại này thường được tạo bởi DBA (Database Administrator) hoặc người lập trình. Temporary Stored Procedure. Có chức năng tương tự như Local Stored Prcedure nhưng thủ tục loại này tự hủy khi kết nối tạo ra nó ngắt hoặc SQL Server ngưng hoạt động và nó được tạo ra trên CSDL TempDB. Extended Stored Procedure. Đây là loại thủ tục sử dụng chương trình ngoại vi đã được biên dịch thành DLL. Tên thủ tục được bắt đầu bằng xp_. Ví dụ thủ tục xp_sendmail dùng gửi mail, thủ tục xp_cmdshell dùng thực hiện lệnh của DOS (xp_cmdshell ‘dir c:\’). Remote Stored Procedure: Là loại thủ tục sử dụng thủ tục của một server khác. THIẾT LẬP THỦ TỤC LƯU TRỮ. Sử dụng công cụ. - Chọn CSDL cần tạo thủ tục trong Enterprise Manager –> Stored Procedures - Nhấn nút phải chuột -> New Stored Procedure 88
94. - Đặt tên thủ tục, xác định role người khai thác, soạn kịch bản câu lệnh. Sử dụng câu lệnh. Sử dụng lệnh Create Procedure, để tiện xem xét ta xét theo các ví dụ , các ví dụ dưới đây thực hiện tạo thủ tục và thao tác với CSDL pubs để tiện trong dữ liệu mẫu, để tìm hiểu cú pháp câu lệnh T-SQL bạn xem phần câu lệnh T-SQL trong cùng tài liệu này. Thủ tục không có tham số. Thủ tục sau sẽ thực hiện liệt kê tất cả các tác giả, sách và nhà xuất bản mà tác giả viết sách. Use Pubs CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO 89
95. Kết quả thực hiện: Thủ tục có tham số. Thủ tục sau thực hiện lọc tìm tác giả có tên, họ truyền theo tham số. USE pubs GO CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GO 90
96. Cách truyền tham số: + Gán giá trị theo thứ tự: EXECUTE au_info 'Dull', 'Ann' + Gán giá tr ên bi ị theo t ến EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' + Gán giá tr ên bi ị theo t ến, không theo thứ tự EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' Thủ tục có tham số tùy lựa theo giá trị đưa vào. Ví dụ này sẽ đề cập đến việc truyền tham số theo mẫu, giá trị tham số được ngầm định khi tạo thủ tục và thủ tục khi thực hiện sẽ kiểm tra giá trị tham số nhập vào. USE pubs GO CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname GO Tham số % xác định giá trị tùy ý nhập vào tham số, tham số D% xác định giá trị đầu tiên của chuỗi phải bằng chữ D. Khi ngầm định các giá trị như trên tham có không được truyền giá trị sẽ tự nhận giá trị ngầm định. Cách truyền tham số như sau: + Không truy n tham s ề ố: EXECUTE au_info2 91
97. + Ch ỉ truyền tham số đầu, tham số sau sẽ nhận giá trị ng ầm định. EXECUTE au_info2 'Wh%' + Ch òn l ỉ truyền một tham số, tham số x ại sẽ nhận giá tr ị ngầm định. EXECUTE au_info2 @firstname = 'A%' + Tham s r ố thứ nhất xác định giá t ị một ký tự thuộc vị trí có [CK] ch ỉ nhận ký tự ‘C’ hoặc ‘K’, [OE] chỉ nhận giá tr ị ‘O’ hoặc ‘E’. EXECUTE au_info2 '[CK]ars[OE]n' õ giá tr + Xác định r ị tham số EXECUTE au_info2 'Hunter', 'Sheryl' + Xác định kiểu giá trị tham số. EXECUTE au_info2 'H%', 'S%' Thủ tục sử dụng tham só láy giá trị ra (tham trị). Ví dụ sau sẽ mô tả kỹ thuật sử dụng tham trị, như trong các ví dụ trước ta sử dụng tham số để truyền giá trị vào tên tham số bắt đầu bằng 1 chữ @, tham số được bắt đầu bằng 2 chữ @@. Sẽ được sử dụng trên nhiều dòng lệnh, sử dụng cùng từ khóa OUTPUT xác định là tham trị để lấy giá trị ra. Ví dụ sau thực hiện truyền tham số vào và lấy giá trị ra: USE pubs GO CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT AS SELECT 'Title Name' = title FROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price) FROM titles WHERE title LIKE @@TITLE GO Tham số bắt đầu bằng 2 ký tự @@ xác định được sử dụng cho nhiều câu lệnh, sử dụng cùng từ khóa OUTPUT xác định là biến tham trị Ví dụ trên sử dụng biến @@Title xác định điều kiện đưa ra tên sách đây là loại biến truyền vào sử 92
98. dụng cho hai câu lệnh Select, biến @@Sum xác định là biến tham trị dùng lấy giá trị ra. Cách sử dụng tham số như sau: DECLARE @@TOTALCOST money EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT IF @@TOTALCOST < 200 BEGIN PRINT ' ' PRINT 'All of these titles can be purchased for less than $200.' END ELSE SELECT 'The total cost of these titles is $' + RTRIM(CAST(@@TOTALCOST AS varchar(20))) Ví dụ trên sử dụng biến @@TOTALCOST vào vị trí biến @@SUM trong thủ tục. Kết quả thực hiện như sau: Title Name The Busy Executive's Database Guide The Gourmet Microwave The Psychology of Computer Cooking (3 row(s) affected) Warning, null value eliminated from aggregate. All of these titles can be purchased for less than $200. Thủ tục sử dụng biến OUTPUT kiểu con trỏ (Cursor). Ví dụ sau tạo thủ tục có biến kiểu Cursor, biến này sẽ quản lý một bảng dữ liệu được truy vấn bằng câu lệnh Select. CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT 93
99. AS SET @titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titles OPEN @titles_cursor GO Con trỏ được đưa vào biến kiểu Cursor có tên @Titles_cursor, hướng dịch chuyển Forward (tiến) và Static. Sử dụng biến như ví dụ sau: USE pubs GO DECLARE @MyCursor CURSOR EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursor END CLOSE @MyCursor DEALLOCATE @MyCursor GO Biến con trỏ được đưa vào viến @MyCursor, khi mở con trỏ vị trí bản ghi đầu tiên của bảng được xác định. Trong ví dụ trên sử dụng vòng lặp duyệt từng bản ghi, việc xử lý dữ liệu thực hiện trong vòng lặp. Thủ tục đặt thuộc tính ẩn kịch bản câu lệnh. Ví dụ sau sẽ đặt thuộc tính WITH ENCRYPTION ẩn văn bản trong thủ tục với người sử dụng. CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM authors 94
100. GO Khi sử dụng thủ tục hệ thống sp_helptext để xem nội dung thủ tục: EXEC sp_helptext encrypt_this Kết quả như sau: The object's comments have been encrypted. SỬA, XÓA THỦ TỤC Sử dụng công cụ. - Chọn thủ tục cần sửa, xóa -> thực hiện sửa nội dung hoặc chức năng xóa. Sử dụng câu lệnh. - Sửa sử dụng lệnh Alter Procedure - Xóa sử dụng lệnh Drop Procedure Bạn đọc có thể tự tìm hiểu về User Defined Function tong Book Online, là đối tượng gọi là hàm thuộc CSDL, có chức năng và cách thức hoạt động gần giống thủ tục. 95
101. TRIGGER KHÁI NIỆM TRIGGER. Trigger là một thủ tục đặc biệt mà việc thực thi của nó tự động khi có sự kiện xảy ra, các sự kiện gọi thủ tục đặc biệt này được định nghĩa trong câu lệnh, thông thương được thực hiện với các sự kiện liên quan đến Insert, Update, Delete dữ liệu. Trigger được sử dụng trong việc bảo đảm toàn vẹn dữ liệu theo quy tắc xác định, được quản lý theo bảng dữ liệu hoặc khung nhìn. NHỮNG TRƯỜNG HỢP SỬ DỤNG TRIGGER. - Sử dụng Trigger khi các biện pháp toàn vẹn dữ liệu như Constraint, rule, không bảo đảm. Khác với các công cụ bao đảm toàn vẹn dữ liệu đã nêu, các công cụ này sẽ thực hiện kiểm tra tính toán vẹn trước khi đưa dữ liệu vào CSDL (còn gọi là Declarative Data Integrity), còn Trigger thực hiện kiểm tra tính toàn vẹn khi công việc đã thực hiện rồi (còn gọi là Procedural Data Integrity). - Khi CSDL chưa được chuẩn hóa (Normalization) thì có thể xảy ra dữ liệu thừa, chứa ở nhiều vị trí trong CSDL thì yêu cầu đặt ra là dữ liệu cần cập nhật thống nhất trong mọi nơi. Trong trường hợp này ta phải sử dụng Trigger. - Khi thay đổi day chuyền dữ liệu giữa các bảng với nhau (khi dữ liệu bảng này thay đổi thì dữ liệu trong bảng khác cũng được thay đổi theo). ĐẶC ĐIỂM CỦA TRIGGER. - Một trigger có thể thực hiện nhiều công việc (theo kịch bản), có thể nhiều sự kiện kích hoạt thực thi trigger, có thể tách rời các sự kiện trong một trigger. - Trigger không được tạo trên bảng temprate hay system. - Trigger chỉ thực thi tự động thông qua các sự kiện mà không thực hiện bằng tay. - Trigger sử dụng được với khung nhìn. - Khi trigger thực thi theo các sự kiện Insert hoặc Delete thì dữ liệu khi thay đổi sẽ được chuyển sang các bảng Inserted Table, Deleted Tabla, là 2 bảng tạm thời chỉ chứa trong bộ nhớ, các bảng này chỉ được sử dụng với các lệnh trong trigger. 96
102. Các bảng này thường được sử dụng để khôi phục lại phần dữ liệu đã thay đổi (roll back). - Trigger chia thành 2 loại Instead of và After: Instead of là loại trigger mà hoạt động của sự kiện gọi nó sẽ bỏ qua và thay vào nó là các lệnh thực hiện trong trigger. After (tương đương với từ khóa For) đây là loại ngầm định, khác với loại Instead of thì loại trigger này sẽ thực hiện các lệnh trong nó sau khi đã thực hiện xong sự kiện gọi nó. TẠO TRIGGER. Tạo trigger được thực hiện thông công cụ và câu lệnh: Tạo trigger bằng công cụ. - Chọn bảng dữ liệu hoặc khung nhìn. - Nhấn nút phải chuột. - Chọn All tasks -> Manage Triggers - Soạn kịch bản tạo trigger. (Cú pháp cụ thể hơn bạn xem trong phần tiép theo) Tạo trigger bằng câu lệnh. Sử dụng lệnh Create Trigger, cú pháp chung như sau: 97
103. CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ n ] } ] sql_statement [ n ] } } Các tham số cơ bản: + trigger_name: Tên trigger. + table/view: Tên bảng hoặc khung nhìn. + For/After/Instead Of: Loại trigger. + { [DELETE] [,] [INSERT] [,] [UPDATE] }: Sự kiện khi tự động gọi thực thi trigger. + sql_statement [ n ]: Kịch bản các câu lệnh xử lý của trigger. Các câu lệnh sau không được thựch thi trong kịch bản các câu lệnh xử lý của trigger: ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG Để cụ thể hơn ta xét một số ví dụ sau: 98
104. Ví dụ tạo một trigger thông báo. CREATE TRIGGER reminder ON titles FOR INSERT, UPDATE AS RAISERROR (50001, 16, 10) GO Ví dụ trên tạo một thông báo cho các client khi thực hiện thêm hoặc sửa dữ liệu trên bảng Titles, mã thông báo là 50001, là mã thông báo do người dùng định nghĩa. Để tạo thông báo bạn thao tác như sau: - Vào menu Tools -> Manage SQL Server Messages - Chọn bảng Messages -> New - Đặt mã, soạn nội dung, kiểu thông báo (Serverity), mã thông báo sẽ được sử dụng trong các ứng dụng hoặc câu lệnh yêu cầu. 99
105. Ví dụ tạo trigger tự động gửi Email khi được thực thi. CREATE TRIGGER reminder ON titles FOR INSERT, UPDATE, DELETE AS EXEC master xp_sendmail 'MaryM', 'Don''t forget to print a report for the distributors.' GO Ví dụ tạo trigger kiểm soạn khoảng giá trị giữa 2 bảng. Ví dụ sau sẽ tạo trigger thực hiện kiểm soát phạm vị mức lương của một nhân viên vừa chèn vào có thuộc giá trị định mức lương trong bảng mức lương hay không. CREATE TRIGGER employee_insupd ON employee FOR INSERT, UPDATE AS DECLARE @min_lvl tinyint, @max_lvl tinyint, @emp_lvl tinyint, @job_id smallint SELECT @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_id FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id JOIN jobs j ON j.job_id = i.job_id IF (@job_id = 1) and (@emp_lvl <> 10) BEGIN RAISERROR ('Job id 1 expects the default level of 10.', 16, 1) ROLLBACK TRANSACTION END ELSE IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl) 100
106. BEGIN RAISERROR ('The level for job_id:%d should be between %d and %d.', 16, 1, @job_id, @min_lvl, @max_lvl) ROLLBACK TRANSACTION END SỬA, XÓA TRIGGER. Sử dụng công cụ. - Chọn trigger trong mục Manage Triggers - Thực hiện sửa nội dung hoặc xóa. Sửa, xóa theo câu lệnh. - Sử dụng lệnh Alter trigger để sửa. - Sử dụng lệnh Drop Trigger để xóa. (Bạn có thể tìm hiểu chi tiết hơn trong Book Online) 101
107. XUẤT – NHẬP DỮ LIỆU Trong chương này bạn đọc sẽ tìm hiểu kỹ thuật trao đổi dữ liệu với các mỗi trường ngoài Server của SQL, có thể với một CSDL khác, một Server SQL khác, một hệ quản trị CSDL khác hoặc nối ghép tập tin CSDL. SERVER LIÊN KẾT – LINKED SERVER. Tương tự như các hệ quản trị CSDL lớn khác (Access, Oracle), hệ thống cung cấp công cụ liên kết với hệ quản trị CSDL khác. Khi liên kết đã được thiết lập, với quyền hạn của user liên kết bạn có thể thực hiện khai thác dữ liệu liên kết trên SQL Server. Tạo ODBC. ODBC viết tắt của cụm từ Open DataBase Connectivity, là công cụ được Windows cung cấp với mục đích làm môi trường trao đổi dữ liệu giữa những CSDL, giữa nhiều hệ quản trị CSDL với ứng dụng. ODBC tạo những chuẩn chung nhất kết nối đến CSDL và ứng dụng. Khi thực hiện khai thác dữ liệu thông qua ODBC, ứng dụng liên kết theo tên ODBC, quyền hạn khai thác thực hiện khi tạo kết nối tự ODBC đến CSDL. Cách tạo ODBC: - Chọn ODBC trong Control panel. 102
108. - Chọn bảng User DSN -> Add - Chọn Driver của hệ quản trị CSDL của CSDL cần liên tạo ODBC. - Chọn Finish. - Nhập tên ODBC (tên này sẽ sử dụng cho ứng dụng khác, nên nhập theo chuẩn chung để dễ sử dụng), các tham số khác (ở đây trên hình sử dụng Driver của SQL Server nên bạn phải chọn Server). - Next. 103
109. - Nhập Login ID, mật khẩu -> Next. - Đánh dấu Change the default database to -> Chọn CSDL -> Next -> Finish Sau khi tạo xong trong danh sách xuất hiện ODBC bạn vừa tạo, tư danh sách bạn có thể sửa đổi, xóa ODBC khi cần thiết. 104
110. Tạo liên kết từ Access. Từ hệ quản trị CSDL Access bạn có thể tạo liên kết đến các hệ quản trị CSDL khác (Acess, Dbase, ), hoặc thông qua ODBC. Trong ví dụ minh họa sử dụng liên kết từ Access với ODBC (đối với SQL Server hoặc Oracle, My SQL thì Access phải liên kết thông qua ODBC vì các hệ quản trị CSDL này không thực hiện khai thác dữ liệu qua tập tin chỉ khai thác thông qua tên CSDL, mà Access chỉ thực hiện theo phương thức mở tập tin). Các bước thực hiện như sau: - Mở hệ quản trị CSDL Access. - Mở hoặc tạo CSDL mới từ Access - Chọn File -> Get External Data -> Link Tables. 105
111. - Chọn ODBC Databases. - Chọn ODBC cần liên kết (Authors). - Ok. - Nhập Login ID và mật khẩu. - Chọn bảng hoặc khung nhìn cần liên kết trong danh sách. 106
112. - Nhấn Ok, danh sách các bảng trong Access được khai thác tương tự như các bảng khác. 107
113. Tạo Server liên kết – Linked Server. Từ SQL Server có thể tạo liên kết trực tiếp đến các hệ quản trị CSDL khác (Access, SQL Server, Oracle, My SQL, ) mà không cần thiết phải thông qua ODBC như Access đã xét trước. Tạo bằng công cụ. - Vào mục Security -> Linked Server. - Nhấn nút phải chuột -> New Linked Server. - Nhập các tham số: 108
114. + Tên Server. + Provider (Driver của hệ quản trị CSDL cần thiết lập liên kết, trong ví dụ minh họa thực hiện với Access). - Thực hiện khai thác thông qua câu lệnh, trong câu lSQL phải chỉ đủ đường dẫn, ví dụ Select * from Biblio Authors sẽ thực hiện liệt kê toàn bộ danh sách các bản ghi của bảng authors. 109
115. Tạo bằng câu lệnh. Sử dụng lệnh sp_addlinkedserver tạo server liên kết. Ví dụ tạo Linked Server Biblio: sp_addlinkedserver 'Biblio', Access 97', 'Microsoft.Jet.OLEDB.4.0', ’ 'c:\data\biblio.mdb' (Đường dẫn phải phù hợp với Server) Xóa Linked Server. - Sử dụng công cụ: Chọn Linked Server cần xóa -> thực hiện xóa. - Sử dụng lệnh : sp_dropserver [ @server = ] 'server' [ , [ @droplogins = ] { 'droplogins' | NULL} ] SỬ DỤNG BCP VÀ BULK INSERT NHẬP DỮ LIỆU. Bcp là câu lệnh dạng command prompt, dùng xuất (export) và nhâp (import) dữ liệu giữa SQL Server và tập tin (dạng text hoặc excel). Các tập tin tham gia xuất nhập phải có cấu trúc dữ liệu kiểu bảng (hàng, cột), bảng dữ liệu của SQL Server khi thực hiện nhập dữ liệu phải có cấu trúc tương đương có sẵn. Bulk insert là câu lệnh tương tự bcp nhưng chỉ thực hiện import dữ liệu mà không export. Cú pháp lệnh bcp. Lệnh bcp được thực hiện tại cửa sổ lệnh (command prompt). bcp {[[database_name.][owner].]{table_name | view_name} | "query"} {in | out | queryout | format} data_file [-m max_errors] [-f format_file] [-e err_file] [-F first_row] [-L last_row] [-b batch_size] [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6] [-q] [-C code_page] [-t field_term] [-r row_term] 110
116. [-i input_file] [-o output_file] [-a packet_size] [-S server_name[\instance_name]] [-U login_id] [-P password] [-T] [-v] [-R] [-k] [-E] [-h "hint [, n]"] (bạn tìm hiểu thêm trong book online) Ví dụ sử dụng lệnh bcp. + Sử dụng lệnh có từ khóa out để copy toàn bộ dữ liệu từ một bảng hoặc khung nhìn ra tập tin. bcp pubs titleview out titleview.txt -c -Sservername - Uusername -Ppassword + Sử dụng lệnh Select để copy một tập ra tập tin, có từ khóa queryout. bcp "SELECT au_fname, au_lname FROM pubs authors ORDER BY au_lname" queryout c:\Authors.txt -c -Sservername - Uusername -Ppassword Kết quả thực hiện: Nội dung tập tin Authors.txt Abraham Bennet Reginald Blotchet-Halls Cheryl Carson Michel DeFrance Innes del Castillo Ann Dull Marjorie Green Morningstar Greene Burt Gringlesby Sheryl Hunter Livia Karsen 111
117. Charlene Locksley Stearns MacFeather Heather McBadden Michael O'Leary Sylvia Panteley Albert Ringer Anne Ringer Meander Smith Dean Straight Dirk Stringer Johnson White AkikoYokomoto Một số tham số cơ bản: - Out: Copy toàn bộ một Table hoặc view ra tập tin. - Queryout: Copy tập dữ liệu được truy vấn theo câu lệnh. - c: Chỉ ra rằng câu lệnh dùng kiểu ký tự để phân định các cột, nếu không chỉ thì câu lệnh tự nhận tab (\t) để phân định và dùng new line để xuống dòng mới. Cú pháp lệnh Bulk Insert. Lệnh Bulk Insert gần giống lệnh bcp nhưng Bulk Insert chỉ sử dụng để nhập dữ liệu vào SQL Server (Insert), lệnh này được thực hiện bằng SQl Query Analyzer. Cú pháp chung: BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM 'data_file' } [ WITH ( [ BATCHSIZE [ = batch_size ] ] [ [ , ] CHECK_CONSTRAINTS ] [ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ] [ [ , ] DATAFILETYPE [ = 112
118. { 'char' | 'native'| 'widechar' | 'widenative' } ] ] [ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ] [ [ , ] FIRSTROW [ = first_row ] ] [ [ , ] FIRE_TRIGGERS ] [ [ , ] FORMATFILE = 'format_file_path' ] [ [ , ] KEEPIDENTITY ] [ [ , ] KEEPNULLS ] [ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ] [ [ , ] LASTROW [ = last_row ] ] [ [ , ] MAXERRORS [ = max_errors ] ] [ [ , ] ORDER ( { column [ ASC | DESC ] } [ , n ] ) ] [ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ] [ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ] [ [ , ] TABLOCK ] ) ] Ví dụ thực hiện copy toàn bộ dữ liệu tập tin newpubs.dat vào bảng publishers2 (bảng này đã có cấu trúc tương ứng), tập tin kiểu ký tự, ngăn cách giữa các cột là dấu phẩy ‘,’, xuống dòng mới bằng ký tự ‘\n’ (xuống dòng dưới và chuyển về đầu dòng). BULK INSERT pubs publishers2 FROM 'c:\newpubs.dat' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) DETTACH VÀ ATTACH CƠ SỞ DỮ LIỆU. Mục này sẽ nói về ký thuật hủy và nối ghép tập tin CSDL với Server. Giả sử bạn đã có các tập tin của CSDL (gồm tập tin dữ liệu và nhật ký có thể được copy từ vị trí khác). Copy tập tin của CSDL. Trước tiên ta xem kỹ thuật copy các tập tin CSDL sang một vị trí khác (mà vẫn giữ vị trí), sau khi copy sang vị trí khác bạn có thể sử dụng sang Instance mới. Các bước thực hiện như sau: - Stop dịch vụ SQL của Instance có CSDL. 113
119. - Copy các tập tin của CSDL sang vị trí cần thiết. - Start dịch vụ SQL của Instance để tiếp tục làm việc. Dettach cơ sở dữ liệu. Là bước thực hiện tách CSDL khỏi Instance, Ínntance không quản lý CSDL nhưng khác với xóa CSDL là các tập tin chứa CSDL vẫn còn. - Chọn CSDL cần dettach. -> All tasks -> Dettach Database - Nhấn Ok. 114
120. Attach tập tin CSDL vào Instance. Mục này giới thiệu kỹ thuật ghép nối tập tin CSDL vào Instance, là bước tiếp theo của các bước Copy và Dettach. Các bước thực hiện như sau: - Chọn Instance cần Attach CSDL -> Databases -> all tasks -> attach database - Chọn nút browse ( ) - Chọn tập tin mdf của CSDL cần attach. - Đặt tên CSDL. - Xác định User owner. - Ok. IMPORT VÀ EXPORT CƠ SỞ DỮ LIỆU. Phần này sẽ trình bày kỹ thuật nhập và xuất dữ liệu từ CSDL với các hệ quản trị CSDL khác hoặc Instance, CSDL khác của SQL Server. Import – Nhập dữ liệu. Dùng nhập dữ liệu từ ngoài vaof CSDL từ hệ quản trị CSDL khác hoặc CSDL khác của SQL Server. - Chọn Databases -> All tasks -> Import Data 115
121. - Next -> Chọn Data Source (Có thể là SQL Server, Oracle, Access, ), trong ví dụ minh họa chọn Access. - Chọn tập tin (file name) -> Next - Chọn Instance cần chuyển dữ liệu vào, user name., tên CSDL (có trước hoặc tạo tại thời điểm này bằng cách chọn New) -> Next 116
122. - Chọn cách chuyển toàn bộ bảng dữ liệu hay thông qua câu lệnh truyên vấn (trong ví dụ minh họa chọn bảng dữ liệu) -> Next - Chọn các bảng, khung nhìn cần Import (có thể lựa chọn một số chức năng khác cụ thể hơn, bạn đọc tự tìm hiểu), tên các bảng, khung nhìn của SQL Server nhận dữ liệu -> Next. 117
123. - Chọn chức năng thực hiện ngày hay theo lịch –> Next -> Finish - Xem thông báo sau khi chuyển -> Done 118
124. EXPORT – XUẤT DỮ LIỆU. Phần này giới thiệu kỹ thuật xuất dữ liệu từ một CSDL của SQL Server ra một hệ quản trị CSDL khác hoặc một CSDL khác của SQL Server. Tương tự như Import nhưng Export thực hiện Data Source là SQL Server, còn Destination là hệ quản trị CSDL khác hoặc CSDL khác của SQL Server (phần này bạn đọc tự xem xét). 119
125. SAO LƯU, KHÔI PHỤC DỮ LIỆU Chương này sẽ giới thiệu kỹ thuật sao lưu (backup) và khôi phục (restore) dữ liệu, là kỹ thuật thường được sử dụng bảo đảm an toàn dữ liệu phòng trường hợp CSDL bị hỏng, nhật ký dữ liệu. Chức năng này được thực hiện bằng 2 phương pháp: Bằng công cụ và câu lệnh T-SQL. NHỮNG LÝ DO PHẢI SAO LƯU VÀ KHÔI PHỤC DỮ LIỆU. Trong quá trình thực hiện quản trị CSDL SQL Server thì một số nguyên nhân sau đây bắt buộc bạn phải xem xét đến kỹ thuật sao lưu và khôi phục dữ liệu: + Ổ đĩa bị hỏng (chứa các tập tin CSDL). + Server bị hỏng. + Nguyên nhân bên ngoài (thiên nhiên, hỏa hoạn, mất cắp, ) + User vô tình xóa dữ liệu. + Bị vô tình hay cố ý làm thông tin sai lệch. + Bị hack. CÁC LOẠI BACKUP. Backup dữ liệu trong SQL Server gồm các loại sau: + Full Database Backups: Copy toàn bộ CSDL (các tập tin bao gồm các bảng, khung nhìn, các đối tượng khác). + Differential Database Backups: Copy những dữ liệu thay đổi trong Data file kể từ lần full backup gần nhất. + File or file group backups: Copy một file đơn hay file group. + Differential File or File Group Backups: Thực hiện như Differential Database nhưng copy phần dữ liệu thay đổi của file đơn hoặc file group. + Transaction log backups: Ghi nhận tất cả các transaction chứa trong transaction log file kể từ lần transaction log backup gần nhất. Với loại sao lưu này ta có thể khôi phục dữ liệu tại một thời điểm. 120
126. CÁC MÔ HÌNH PHỤC HỒI DỮ LIỆU. + Full Recovery model: Là mô hình phục hồi toàn bộ hoạt động giao dịch của dữ liệu (Insert, Update, Delete, hoạt động bởi lệnh bcp, bulk insert). Với mô hình này ta có thể phục hồi dữ liệu tại một thời điểm trong quá khứ đã được lưu trong transaction log file. + Bulk-Logged Recovery Model: Mô hình này được thực thi cho các thao tác bcp, bulk insert, create index, writetext, updatetext, các hoạt động này chỉ nhật ký sự kiện vào log để biết mà không sao lưu toàn bộ dữ liệu, chi tiết như trong full recover. Các sự kiện Insert, Update, Delete vẫn được nhật ký và khôi phục bình thường. + Simple Recovery Model: Với mô hình này bạn chỉ phục hồi lại thời điểm backup gần nhất mà không theo thời điểm khác trong quá khứ. Cách đặt mô hình khôi phục: - Chọn CSDL. - Nhấn nút phải chuột -> Properties -> Options -> Recovery 121
127. Xét ví dụ sau: Giả sử ta có một CSDL được backup theo chiến lược như hình vẽ: Nhìn hình trên ta thấy CSDL được lập lịch Full Database Backup vào ngày chủ nhật, Differential Database Backup vào ngày thứ ba và thứ năm, còn Log Database Backup vào 5 ngày trong tuần, ngày thứ sáu có sự cố với CSDL data file bị hỏng, vấn đề đặt ra là phải phục hồi dữ liệu và CSDL hoạt động bình thường. Ta phải làm các bước sau: + Thực hiện Backup log file (giả sử log file không bị hỏng). + Khôi phục Full Database của ngày chủ nhật. + Phục hồi Differential Database của ngày thứ năm. + Khôi phục Transaction log backup ngày thứ năm. SAO LƯU CƠ SỞ DỮ LIỆU - BACKUP DATABASE. Trước khi xem xét kỹ thuật sao lưu CSDL, ta thống nhất một số thuật ngữ bằng tiếng Anh như sau: + Backup: Là quá trình copy toàn bộ hoặc một phần database, transaction log, file, file group thành lập một backup set được chứa trong backup media (disk hoặc tape) bằng cách sử dụng một backup device (tape drive name hoặc physical filename). + Backup Device: Một file vật lý hoặc một drive tape. 122
128. + Backup file: Một file chứa Backup set. + Backup media: LÀ Disk hoặc tape. + Backup set: Một bộ backup một lần backup đơn chứa trên backup media. Các bước thực hiện backup như sau: - Chọn CSDL cần backup. - Nhấn phải chuột -> All Tasks -> Backup Database - Nhập các tham số, lựa chọn kiểu. KHÔI PHỤC DỮ LIỆU – RESTORE DATABASE. Là chức năng thực hiện khôi phục dữ liệu đã sao lưu, tùy theo chiến lược backup mà bạn có thể phục hồi đến thời điểm nào, thu được bộ dữ liệu trong quá khứ như thế nào. Khôi phục dữ liệu được thực hiện theo thứ tự backup, thông tin này được lưu trữ trong msdb Các bước thực hiện như sau: 123
129. - Chọn mục Databases -> Nhấn nút phải chuột -> All Tasks -> Restore Database - Nhập tham số, chọn mô hình khôi phục. 124
130. PHÂN QUYỀN, BẢO MẬT Chương này sẽ giới thiệu bạn đọc kỹ thuật phân quyền, quản lý người dùng, đặt các mức bảo mật cho CSDL. CHẾ ĐỘ BẢO MẬT – SECURITY MODE. Như đã gặp trong phần cài đặt SQL Server, SQL Server có 2 chế độ bảo mật: + Windows Authentication Mode (Windows Authentication) + Mixed Mode (Windows Authentication and SQL Server Authentication) Windows Authentication. Là chế độ bảo mật mà những User truy nhập SQL Server phải là những User của Windows. Khi Server đặt ở chế độ bảo mật này, những User phải là những User được Windows quản lý mới được truy nhập. Nhìn trên hình ta thấy khi thực hiện chế độ này người sử dụng muốn khai thác SQL Server phải thông qua 4 bước xác thực (1- Domain, 2- Computer, 3- SQL Server, 4- Database). SQL Server Authentication. 125
131. Khi thiết lập ở chế độ bảo mật này, những User được quyền khai thác phải là những User do quản trị SQL Server tạo ra, mà những user của Windows không được khai thác. Tuy nhiên, SQL Server cho phép thiết lập hai chế độ Windows Authentication Mode (Windows Authentication) và Mixed Mode (Windows Authentication and SQL Server Authentication), chế độ Mixed Mode là sự kết hợp của Windows Authentication và SQL Server Authentication, ở chế độ này cả user của Windows và SQL Server để có thể thiết lập để truy nhập SQL Server. Đặt chế độ. - Nhấn phải chuột chọn tên Server (Instance). - Chọn Properties. - Chọn bảng Security. 126
132. - Chọn chế độ bảo mật -> Ok SERVER ROLE, DATABASE ROLE. Role là đối tượng xác định nhóm thuộc tính để gán quyền cho các user tham gia khai thác SQL Server. Server Role. Nhóm các quyền thực hiện quản trị hệ thống, gồm các nhóm sau: + Bulk Insert Administrators: Được phép thực hiện Bulk Insert. + Database Creators: Được phép tạo và sửa đổi cấu trúc CSDL. + Disk Administrators: Có thể quản trị các file trên đĩa. + Proccess Administrator: Quản trị các dịch vụ đang chạy của SQL Server. + Security Administrators: Quản trị hệ thống bảo mật. + Setup Administrators: Quản trị các thủ tục mở rộng (xp_). 127
133. + System Administrators: Quản trị hệ thống SQL Server. Xem cụ thể như sau: - Mở rộng Server (nhấn dấu ‘+’ phần tên Server). - Mở rộng Security. - Chọn Server Roles: Database Role. Role là đối tượng mà thông qua nó người quản trị có thể gán quyền khai thác cho người sử dụng. Role do CSDL quản lý, khi tạo CSDL hệ thống tự đặt một số Role ngầm định. 128
134. Người những Role ngầm định ta có thể tạo Role mới. Tạo Role theo công cụ. - Chọn Roles trong CSDL -> Nhấn phải chuột -> New Database Role - Đặt tên, chọn user (chọn user có thể làm sau). - Nhấn Ok. Sau khi tạo xong, thực hiện gán quyền khai thác cho Role. - Chọn Role cần gán quyền. - Chọn Perrmissions 129
135. - Đặt các quyền cho từng đối tượng trong CSDL. Nếu chọn quyền nhấn ô chọn xuất hiện dấu chọn màu xanh, nếu cấm nhấn ô chọn xuất hiện dấu màu đỏ. Có thể đặt quyền khai thác đối với role cho từng cột của bảng dữ liệu. Mọi thao tác xóa, sửa được thực hiện như các đối tượng khác. Tạo theo câu lệnh. Sử dụng câu lệnh sp_addrole [ @rolename = ] 'role' [ , [ @ownername = ] 'owner' ] Ví dụ: Thêm Role có tên Managers: EXEC sp_addrole 'Managers' 130
136. QUẢN TRỊ NGƯỜI DÙNG. Người dùng trong SQL Server được chia thành 2 mức: Người truy nhập vào SQL Server gọi là Login, người khai thác CSDL gọi là User. Login. Là đối tượng được quyền truy nhập vào SQL Server, tùy theo chế độ bảo mật của SQL Server mà những login là account của Windows NT hay của SQL Server, login do Server quản lý trực tiếp. Tạo bằng công cụ. - Chọn chức năng Security của Server -> Logins - Nhấn phải chuột -> New Login - Nhập các tham số: Nếu chọn Account của Windows NT thì bạn có thể chọn trong danh sách. Nếu tạo login của SQL Server thì bạn nhập tên mới, mật khẩu, chọn login thuộc server role nào, có thể gán quyền truy nhập khai thác CSDL nào. Tạo bằng câu lệnh. Sử dụng câu lệnh sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] 131
137. [ , [ @sid = ] sid ] [ , [ @encryptopt = ] 'encryption_option' ] Ví dụ: Tạo login có tên ‘Albert’, mật khẩu ‘corporate’ EXEC sp_addlogin 'Albert', 'food', 'corporate' Mọi thao tác sửa, xóa được thực hiện như các đối tượng khác. User. User là đối tượng khai thác CSDL, nếu login chỉ xác định truy nhập vào SQL Server thì User là login ID tham gia khai thác CSDL, user do CSDL quản lý trực tiếp. - Chọn CSDL -> users - Nhấn phải chuột -> new user - Chọn Login, nhập user name, chọn role mà user thuộc ->Ok Các thao tác xóa, sửa thực hiện như các đối tượng khác, để gán quyền cho user bạn có thể chọn lại user vừa tạo cho CSDL sau đó vào nhấn vào Permissions. 132
138. NHÂN BẢN DỮ LIỆU Chương này bạn sẽ giới thiệu với bạn kỹ thuật làm giảm lưu lượng dữ liệu giao dịch với SQL Server khi đã cấu hình nhiều Server trên mạng. GIỚI THIỆU VỀ NHÂN BẢN DỮ LIỆU. Nhân bản dữ liệu tên tiếng anh gọi là Replication, là công cụ được sử dụng copy một hoặc nhiều CSDL đến một hoặc nhiều server (SQL Server) khác, các Server được đặt trong mạng máy tính nội bộ (LAN), người khai thác có thể thực hiện truy nhập đến CSDL có trong Server được chuyển dữ liệu đến. Dữ liệu giữa các máy được thực hiện đồng bộ với nhau theo lịch hoặc theo sự kiện, khi có yêu cầu. Nhân bản dữ liệu có những ưu điểm sau: + Dữ liệu được lưu trữ ở nhiều nơi, hiệu quả trong việc có nhiều ứng dụng cùng truy nhập, khai thác. + Thích hợp các ứng dụng phân tích dữ liệu OLTP của DataWare House. + Có thể khai thác dữ liệu khi không kết nối đến Server. + Giảm thiểu xung khắc do số lượng lớn các giao dịch trên mạng. + Là một giải pháp an toàn khi Server bị lỗi hoặc bảo dưỡng. Mô hình nhân bản. Dịch vụ nhân bản dữ liệu gồm các thành phần cơ bản sau: Publisher, Distributor, Subscribers, Publications, Articles, Subscriptions. Publisher: Là server cung cấp dữ liệu nhân bản cho các server khác. Một publisher có thể thiết lập nhiều bộ dữ liệu nhân bản (gọi là publication). Distributor: Là server quản lý các thông tin nhân bản, lưu trữ dữ liệu trong các giao dịch thực hiện nhận và chuyển dữ liệu từ Publisher đến các Subscriber. Remote distributor là server tách rời khỏi publisher và được cấu hình là distributor. Local distributor là một server được cấu hình là Publisher và Distributor. Subscriber: Là server nhận dữ liệu nhân bản. Subscriber gắn liền với publication (là máy chủ nhận dữ liệu nhân bản của một bộ dữ liệu cấu hình nhân bản). Article: Là một bảng, tập dữ liệu hoặc đối tượng của CSDL cấu hình để nhân bản. Publication: Là một tập gồm một hoặc nhiều article. Subscription: Là một giao dịch yêu cầu bản sao bộ dữ liệu hoặc các đối tượng của CSDL thực hiện nhân bản. Trong mỗi giao dịch publisher thực hiện dẩy (push subscription) dữ liệu, subscriber thực hiện kéo (pull subscription). 133
139. Nhân bản dữ liệu được thực hiện theo những mô hình cơ bản sau: + Central Publisher: Là mô hình Publisher và Distributor thiết lập trên một máy. Gồm các mô hình sau: - Một Publishers và một Subscriber: - Một Publisher và nhiều Subscriber. 134
140. - Publisher và Subscriber được thiết lập trên một máy: + Publisher và Distributor không thiết lập trên một máy: + Republisher: Là mô hình Publisher xuất bản dữ liệu đến Subscriber, sau đó Subscriber được thiết lập là Publisher xuất bản dữ liệu đến Subscriber khác. 135
141. Đường truyền giữa hai máy được thiết lập là Publisher có thể tốc độ thấp, phù hợp với vị trí xa nhau. Ví dụ mô hình giữa các vùng cách xa nhau: + Central Subscriber: Là mô hình Subscriber thiết lậpn nhận dữ liệu xuất bản từ nhiều Publisher. 136
142. Những kiểu nhân bản dữ liệu. Có 3 kiểu nhân bản dữ liệu Snaphot, Transaction, Merge. Snapshot replication: Là kiểu nhân bản thực hiện sao chép, phân tán dữ liệu hoặc các đối tượng của CSDL tạo một thời điểm. Snapshot thường được sử dụng cho những tình huống sau: + Dữ liệu thường là tĩnh, ít thay đổi. + Nhân bản số lượng dữ liệu nhỏ. Transaction replication: Là kiểu nhân bản mà bắt đầu bằng nhân bản snapshot, sau đó sẽ thực hiện nhân giao dịch dữ liệu theo các sự kiện insert, update, delete và những thay đổi liên quan đến thực hiện stored procedure, index view. Nhân bản kiểu này cho phép thực hiện lọc dữ liệu tại xuất bản, cho phép user sửa đổi dữ liệu nhân bản tại subscriber và chuyển dữ liệu đã sửa đổi đến Publisher hoặc Subscriber khác, dữ liệu sửa đổi này có thể coi là dữ liệu được xuất bản. Nhân bản kiểu này được thực hiện khi: + Muốn sửa đổi dữ liệu được xuất bản chuyển đến Subscriber, thời gian thực hiện theo giây, hoặc tức thời. + Cần giao dịch trên toàn bộ hệ thống nhân bản dữ liệu (dữ liệu có thể chuyển đến tất cả các Subscriber hoặc không chuyển đến Subscriber nào). 137
143. + Subscriber thường xuyên kết nối với Publisher. Merge replication: Là kiểu nhân bản dữ liệu cho phép thực hiện nhân sửa đổi dữ liệu trên nhiều Subscriber, có thể kết nối (online) hoặc không kết nối (offline) đến 138
144. Publisher. Dữ liệu sẽ được đồng bộ theo lịch hoặc theo yêu cầu, dữ liệu cập nhật có thời điểm sau sẽ được chấp nhận. Kiểu nhân bản này thực hiện khi: + Nhiều Subscriber có nhu cầu cập nhật dữ liệu và chuyển dữ liệu cập nhật đến Publisher hoặc Subscriber khác. + Subscriber yêu cầu nhận hoặc chuyển dữ liệu khi offline, đồng bộ dữ liệu với các Subscriber và Publisher sau. CẤU HÌNH PUBLISHER VÀ DISTRIBUTOR. Trước khi thực hiện cấu hình các máy thành Publisher hay Distributor ta phải thực hiện chạy dịch vụ SQL Server Agant trong chức năng Service manager. các bước cấu hình như sau: - Chọn Server cần cấu hình -> Replication - Nhấn phải chuột -> Configure Publishing Subscription and Distribution - Thực hiện thao các bước: + Chọn thư mục Snapshot: Thư mục này sẽ sử dụng cho 139
145. - Đặt tên CSDL của Distribution. - Chọn Server cấu hình thành Publisher. 140
146. - Chọn CSDL tham gia nhân bản, kiểu nhân bản. - Chọn Server được cấu hình là Subscriber của Publisher đang thiết lập. - Kết thúc. TẠO PUBLICATION. Bước này sẽ thực hiện tạo Publication, cách thực hiện như sau: + Chọn Publication trong Replication của Publisher. + Nhấn phải chuột -> New Publication 141
147. + Thực hiện theo các bước: - Chọn CSDL cần xuất bản dữ liệu hoặc đối tượng. - Chọn kiểu nhân bản (trong ví dụ này thực hiện kiểu Merge) - Chọn phiên bản SQL Server của Subscriber. - Chọn Article tham gia Publication. 142
148. - Đặt tên cho Publication. - Kết thúc. TẠO PUSH SUBSCRIPTION. Bước này thực hiện tạo thủ tục đẩy (push) từ Publisher (Distributor trong ví dụ này) đến Subscriber, được thực hiện trên Publisher. Các bước thực hiện như sau: 143
149. - Chọn Publication của Publisher -> Nhấn phải chuột -> Push new Subscription - Chọn Subscriber. - Chọn CSDL trên Subscriber nếu đã có, nếu chưa có thực hiện chọn chức năng tạo mới. 144
150. - Chọn lịch thực hiện đồng bộ dữ liệu. - Kết thúc. Sau khi thiết lập xong trên Subscriber sẽ có CSDL theo tên đã tạo. TẠO PULL SUBSCRIPTION. Bước này thực hiện tạo công cụ kéo dữ liệu nhân bản từ Publisher về Subscriber, được thực hiện trên Subscriber. - Chọn Subscription của Subscriber -> Nhấn phải chuột -> New Pull Supcription - Thực hiện theo các bước: + Chọn Publication. 145
151. - Chọn Agent tham gia kết nối Publisher. - Chọn CSDL đích. 146
152. - Thực hiện tiếp các bước và kết thúc. Nếu đã tạo Push Subscription với một CSDL sẽ không được tạo Pull Subscription với CSDL đó. THỰC HIỆN ĐỒNG BỘ DỮ LIỆU. Sau khi thiết lập theo các mô hình nhân bản xong, bạn có thể thực hiện đồng bộ dữ liệu bằng cách: - Thực hiện theo lịch. - Theo yêu cầu: Chọn Subscription (Push hoặc Pull) -> Nhấn phải chuột -> Start Synchronizing Sau khi thực hiện xong dữ liệu sẽ được đồng bộ giữa Publisher và Subscriber. Ngoài thực hiện theo công cụ bạn có thể tìm hiểu thực hiện nhân bản theo câu lệnh T-SQL hoặc Stored Procedure. 147
153. Phần 2. CÂU LỆNH T-SQL Trong phần này sẽ giới thiệu cấu trúc, kỹ thuật soạn kịch bản lệnh T-SQL, đối với các hệ quản trị CSDL Foxfro, Access thì câu lệnh thực hiện truy vấn, khai thác CSDL là ngôn ngữ truy vấn SQL (Structure Query Language), các lệnh được thực hiện theo từng câu lệnh mà không thực hiện theo kịch bản hoặc theo tập hợp nhiều câu lệnh với nhau. Đối với hệ quản trị CSDL Oracle thì ngôn ngữ truy vấn dữ liệu là SQL/PL (SQL Plus), còn SQL Server ngôn ngữ có tên Transact-SQL viết tắt là T-SQL. ĐỊNH NGHĨA DỮ LIỆU (DATA DEFINITION LAGUAGE - DDL). Phần này sẽ xem xét các lệnh liên quan đến tạo mới, sửa đổi, xóa các đối tượng liên quan đến Table, View và các đối tượng khác. Tạo kiểu dữ liệu mới. Tạo kiểu dữ liệu dạng user-defined. Cú pháp: sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @nulltype = ] 'null_type' ] [ , [ @owner = ] 'owner_name' ] Ví dụ: sp_addtype ssn, ‘varchar(11)’, ‘NOT NULL’ Xóa kiểu dữ liệu đã tạo. Cú pháp: sp_droptype [ @typename = ] 'type' Ví dụ: Sp_droptype ssn Tạo ràng buộc (Constraint). Tạo ràng buộc được thực hiện trong 2 câu lệnh Create Table hoặc Alter Table: Check, Default, Foreign Key, Primary Key, Unique. 148