Phát triển ứng dụng cơ sở dữ liệu với C# và Net Framework

là Enterprise Manager và Query Analyzer. SQL Server 2005, với công cụ quản lý mới là SQL Server Management Studio đã thay thế hoàn toàn 2 công cụ trên của SQL 2000. Công cụ này cho phép quản lý nhiều thể hiện SQL Server dễ dàng hơn. Từ một giao diện, có thể quản lý nhiều thể hiện của cỗ mãy CSDL SQL Server, Analysis Services, Intergration Services và Reporting Services. - Công cụ mới SQL Server Configuration Manager cho phép kiểm soát các dịch vụ kết hợp với SQL Server 2005. Nó có thể thay thế cho Services Manager và công cụ cấu hình mạng cho Server và Client. Chúng ta cũng có thể kiểm soát một số dịch vụ khác như: SQL Server, SQL Agent, SQL Server Analysis Services, DTS Server (Cho SQL Server Integration Services), Full - Text Search, SQL Browser. b. Profiler Cho phép phân tích những vấn đề về hiệu suất thực thi trong SQL Server 2005. Ví dụ, Profiler mở các tập tin truy vết mà chúng ta đã lưu trong hệ thống tập tin để xem lại và phân tích các quá trình SQL Server cần quan tâm. Profiler có thể biểu diễn thông tin truy vết ở dạng đồ thị để có thể dễ dàng xem điều gì đã xảy ra. Nó có thể nhận dữ liệu được ghi lại bởi Windows Performance Monitor. Chúng ta có thể hiển thị dữ liệu dạng đồ thị, xem hiệu suất thực thi trên khoảng thời gian đã chọn. Từ đồ thị, ó thể truy cập đến điểm có vấn đề. c. SQL Server Agent Những khả năng của SQL Server Agent, thành phần hỗ trợ cho các tác vụ đã được lập thời gian biểu, được nâng cao. Ví dụ, số tác vụ đồng thời mà SQL Server Agent có thể chạy được tăng lên. SQL 2000 chỉ dùng SQL Agent trong những tác vụ liên quan đến cỗ máy CSDL. Còn trong 2005, SQL Server Agent thực thi các tác vụ cho Analysis Services và Integration Services. SQL Server Agent dùng ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 154 Windows Management Instrumentation (WMI), cho phép viết mã tránh thực thi tác vụ, như khi đĩa cứng đầy thì các tác vụ vẫn được thực thi thành công. d. Cấu hình động Trong SQL Server 2005, có thể thực hiện bất kì thay đổi cấu hình nào mà không cần khởi động lại SQL Server, kể cả khi đang chạy trên Windows Server 2003. Bên cạnh đó, chúng ta cũng có thể thay đổi áp lực CPU và I/O nếu cần, có thể thêm nóng bộ nhớ cho Server nếu có phần cứng thích hợp. e. Full-text search (CSDL lưu trữ thông tin siêu dữ liệu) Có thể sao lưu dự phòng và khôi phục các nhóm Full-text search theo cùng cách như đối với các CSDL khác. g. SQL Server Service broker SQL Server Service broker cho phép tạo các ứng dụng xử lý thông điệp không đồng thời. Tính không đồng thời của các thông điệp service Broker nghĩa là việc xử lý các tác vụ không cần hoàn thành tất cả cùng lúc vì điều này sẽ làm server bị chậm. Thay vào đó các thông điệp được đưa vào hàng đợi xử lý khi server nhẹ tải hơn, như vậy sẽ cải thiện được hiệu suất tổng thể. h. Kết nối liên tục cho người quản trị Đây không chỉ là kết nối cho người chuyên làm công việc quản trị CSDL mà còn là kết nối chỉ dành cho người quản trị dùng. Kết nối được dùng khi một quá trình trên server bị đứng, cần phải truy cập va cỗ máy CSDL để hủy quá trình đó. Kết nối đặc biệt này cho phép người quản trị nhận chia sẻ các chu kỳ CPU để hủy quá trình bị đứng mà không cần phải khởi động lại server ngay cả trong trường hợp quá trình đó sử dụng gần như 100% chu kỳ CPU. i. Công cụ dòng lệnh (SQL CMD) Như đã giới thiệu ở trên SQL CMD là một tiện ích dòng lệnh trong SQL Server 2005, có thể viết lệnh dùng SQL CMD hoặc thực thi các mã kịch bản T- SQL. SQL CMD cũng cho phép sử dụng tham số dòng lệnh để thay thế các biến trong T-SQL. k. Cập nhật dễ dàng hơn Các đối tượng hệ thống nằm trong CSDL resource của SQL Server 2005, khi service pack hoặc cập nhật khác được áp dụng cho thể hiện SQL Server thì tất cả các đối tượng hệ thống được thay thế. Điều này giúp việc nâng cấp dễ quản lý hơn. ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 155 l. Replication (thiết lập và quản lý sao lưu đồng bộ) Việc này được thực hiện bởi SQL Server Management Studio, một công cụ sẽ giám sát tình trạng hệ thống cho phép kiểm tra các thiết lập replication và hiệu suất thực thi. m. Cấu hình Window Management Instrumentation (WMI) WMI cho phép thực hiện một bó tác vụ cấu hình, chẳng hạn thiết lập cấu hình mạng và client. Ngoài ra, còn có thể xác định tác vụ được thực hiện có hiệu quả và an toàn ở một không gian xác định hay không. Ví dụ, kiểm tra xem d0ĩa cứng còn đủ vùng trống để sao lưu dự phòng hay không. n. Gửi mail từ CSDL Đây là tính năng khá mới mẻ trong SQL Server 2005. Nó thay thế SQL Mail trong SQL Server 2000. Database Mail sử dụng giao thức Simple Mail Transfer Protocol (SMTP). Không còn bất kỳ phụ thuộc nào với Messaging Application Programming Interface(MAPI) và cũng không còn đòi hỏi phải có Outlook. Việc loại bỏ những phụ thuộc này tránh được hiều vấn đề mà người dùng SQL Server 2000 gặp phải với SQL Mail. Ngoài ra, Database Mail cũng hỗ trợ hoạt động liên tiếp, ghi tập tin Log và kiểm tra hoạt động. 3.1.2.5. Nâng cao độ sẵn sàng của CSDL a. Truy cập dữ liệu đồng thời Nghĩa là có thể nhận được dữ liệu trong khi người khác cũng đang truy cập dữ liệu đó. - Tạo ảnh dữ liệu: tạo bản sao logic của CSDL ở thời điểm xác định. Người dùng có thể sử dụng ảnh dữ liệu (snapshot) làm nguồn cho các Reporting Service mà không cần nạp bản sao trực tuyến của CSDL. - Cô lập ảnh dữ liệu: đây là cấp cô lập giao dịch mới để cải thiện độ sẵn sàng của CSDL cho các ứng dụng đọc, bởi vì cơ chế ghi không khóa cơ chế đọc. Các ứng dụng ghi bắt buộc phải dò tìm xung đột. - Lập chỉ mục trực tuyến: trong thời gian chỉ mục được xây dựng lại, nó vẫn có thể được dùng cho việc truy xuất dữ liệu. Điều này xem như cải thiện hiệu suất thực thi trong khoảng thời gian đó. b. Sẵn sàng sau khi server bị hỏng ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 156 Một số tính năng được thêm vào liên kết lỗi server đã được hỗ trợ ở SQL Server 2000: - Khôi phục nhanh hơn: sau khi server bị hỏng, CSDL được làm để sẵn sàng cho người dùng nhanh hơn trong quá trình khôi phục. - Tạo bản sao CSDL trực tuyến (Database Mirroring): sử dụng phần cứng chuẩn, server lưu bản sao (mirror server) luôn được duy trì bản sao mới nhất. Khi server chính bị lỗi, server lưu bản sao sẵn sàng để sử dụng chỉ trong vài giây và server chính lúc này được ẩn đi đối với người dùng. - Định hướng ngầm lại kết nối cho client: khi khôi phục, server lưu bản sao dùng định hướng ngầm lại kết nối cho client (Transparent Client Redirect). Lớp MDAC (Microsoft Data Access Component) ghi nhận mirror server khi kết nối tới server chính. Nếu server chính bị hỏng, MDAC định hướng kết nối tới mirror server. c. Sẵn sàng trong khi bảo trì CSDL Quá trình sao lưu và khôi phục phải tin cậy để đảm bảo việc khôi phục dữ liệu hiệu quả. Một số tính năng được cung cấp trong SQL Server 2005: - Sữa chữa trực tuyến từng phần(Finer grained Online repairs): khôi phục dữ liệu filegroup với CSDL đang sẵn sàng sau khi filegroup chính được khôi phục. - Nâng cao chức năng kiểm tra sao lưu dự phòng (Enhanced Backup Verification): với cú pháp RESTORE VERIFY ONLY, mọi thức có thể được kiểm tra, rút ngắn quá trình ghi bản sao lưu dự phòng xuống server. - Tạo bản sao của bản sao lưu dự phòng (Backup Media mirroring): mục đích để giảm thiểu khả năng khôi phục không thành công hoặc tạo bản sao lưu ngoài. - Kiểm tra tổng trang CSDL (Database Page Checksums): mục đích để dò rìm lỗi trong từng trang dữ liệu để tránh sai sót. - Kiểm tra tổng bản sao lưu dự phòng (Backup Checksums): cơ chế dò tìm lỗi trong khi thực hiện sao lưu dự phòng. Nó có thể tìm lỗi trước và sửa lỗi sau. - Sao lưu dự phòng dữ liệu cùng lúc với sao lưu dự phòng tập tin log (Backup Data and Logs). 3.1.2.6. Nâng cao khả năng mở rộng CSDL - Khả năng cài đặt tự hành vào một liên cung (cluster) ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 157 - Phân vùng dữ liệu - Giám sát điều chỉnh cỗ máy CSDL Chức năng giám sát điều chỉnh cỗ máy CSDL của SQL Server 2005 là Database Engine Tuning Advisor (DTA) thay thế cho SQL Server 2000 Index Tuning Wizard. - Hỗ trợ gắn nóng bộ nhớ giúp cải thiện hiệu suất thực thi dưới tải cao và tránh được thới gian chết máy. - Nâng cao hiệu suất sao chép và đồng bộ (Replication). 3.1.2.7. Tăng cường khả năng khai thác thông tin (Business Intelligence) Khai thác thông tin là sức mạnh đáng chú ý của SQL Server 2005. Các tính năng được thêm vào Analysis Services, Intergration Services (thay thế cho Data Transformation Services) và Reporting services được xây dựng trên cơ sở các tính năng của Reporting Services trong SQL Server 2000. Khai thác thông tin trong SQL Server 2005 sử dụng các mẫu Analyze, Intergrate và Report. Dùng Intergration Services để kết hợp dữ liệu từ nhiều nguồn lại với nhau; dùng Analysis Services để có cái nhìn chi tiết bên trong dữ liệu; dùng Reporting Services để tạo các báo cáo thể hiện các phân tích với người dùng. 3.2. Quản trị CSDL bằng SQL Server 2005 Phần này chúng ta sẽ làm việc trên thể hiện SQL Server Management Studio của SQL Server 2005. SQL Server Management Studio (thay thế cho công cụ Enterprise Management trong SQL Server 2000) cho phép truy cập các đối tượng CSDL như table, view, store procedure, index… thông qua Object Explorer 3.2.1. Một số thuật ngữ cần tìm hiểu trong CSDL. 3.2.1.1. Mẩu tin, trường và giá trị Bảng (table) được tạo thành từ mẩu tin (record) và trường (field). Một mẩu tin là tất cả thông tin về một trong những thực thể trong bảng. Trường là một phần thông tin cụ thể được lưu trong bảng. Tùy thuộc vào mục đích của việc sử dụng bảng mà đôi khi việc tương tác với mẩu tin sẽ thuận lợi hơn đối với trường và ngược lại. Khi kiểm tra một trường cụ thể trong một bảng cụ thể, chúng ta xem giá trị của trường đó trong mẩu tin đó. Để hiểu rõ hơn, chúng ta xem bảng minh họa dưới đây: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 158 Trong ví dụ minh họa trên: + Tên bảng là Docgia + Trường là: những thông tin từng phần về Ma_docgia, Họ, Tên_lot… và được xác định theo cột. + Mẩu tin: là tất cả những thông tin về một trong những độc giả trong bảng Docgia, bao gồm những thông tin về Ma_docgia, và được xác định theo dòng + 1, 2, Tran,… là các giá trị được xác định trong trường và mẩu tin. 3.2.1.2. Dòng và cột Các mẩu tin được biểu diển theo dòng và các trường được biểu diễn theo cột. Nội dung của bảng được hiển thi trên lưới tạo bởi dòng và cột. Tài liệu Microsoft SQL Server thường dùng thuật ngữ dòng và cột, nhưng một số tài liệu về CSDL nói chung thì sử dụng thuật ngữ mẩu tin và trường. 3.2.1.3. Giá trị Null Một giá trị là dữ liệu thật sự được lưu trong một trường xác định của một bảng xác định. Vậy điều gì sẽ xảy ra khi không có dữ liệu? Một gía trị Null thể hiện sự thiếu thông tin, đó là cách CSDL cho chúng ta biết rằng nó không biết dữ liệu thuộc cột đó là dữ liệu gì. 3.2.1.4. Thuộc tính và cột Không phải tất cả các cột đều được tạo ra như nhau, việc diễn tả sự khác nhau đó được SQL Server chỉ ra thông qua thuộc tính cột. Mỗi cột trong bảng thường có các thuộc tính sau: - Column name: tên cột, cho phép tham chiếu đến cột đó trong bảng - Data type: kiểu dữ liệu, cho phép ràng buộc dữ liệu lưu trong cột. Thuộc tính length của cột chỉ ra lượng dữ liệu tối đa có thể lưu giữ trong cột đó. - Allow Nulls: cho biết giá trị Null có được phép lưu trong cột hay không. Bằng cách sử dụng thuộc tính cột để phân biệt một cột với cột khác, làm cho cột gọn gàng và có trật tự. 3.2.1.5. Khóa và quan hệ ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 159 Khóa chính: là thông tin nhận dạng duy nhất cho phép bạn tìm ra chính xác một dòng dữ liệu trong bảng. Không thể có 2 dòng trong cùng một bảng có cùng giá trị trong cột khóa chính. Khóa chính có thể được tạo thành từ một cột đơn hoặc từ nhiều cột trong bảng. Ngoài khóa chính, còn có khóa ngoại. Khóa ngoại cho phép chúng ta lấy dữ liệu từ các bảng káhc trong cùng một CSDL. 3.2.1.6. Chỉ mục và ràng buộc Chỉ mục (index) trên bảng dùng để xác định nhanh chóng các dòng dữ liệu cụ thể. Chúng ta sẽ chọn một hoặc nhiều cột trong bảng để đánh chỉ mục. Chỉ mục có thể duy nhất hoặc không. Chỉ mục duy nhất hạn chế dữ liệu thêm vào bảng. Một chỉ mục cũng có thể là liên cung hoặc phi liên cung.Thuật ngữ này nói đến thứ tự lưu trữ vật lý của bảng. Lưu ý: Mặc dù một bảng có thể có nhiều chỉ mục nhưng chỉ có thể có một chỉ mục liên cung. SQL Server cung cấp một kiểu chỉ mục khác gọi là full-text. Không giống với chỉ mục thông thường được lưu với bảng, chỉ mục full-text được lưu trong các đối tượng đặc biệt gọi là catalogs. Chỉ mục full-text không được cập nhật tự động. Nó có thể được cập nhật bằng cách chạy một tác vụ đánh chỉ mục đặc biệt trên server hoặc chúng ta cấu hình để nó được cập nhật tự động khi bảng thay đổi. Tuy nhiên khả năng tìm kiếm của full-text kém chính xác hơn chỉ mục thông thường. Đối với chỉ mục thông thường, để xác định dòng dữ liệu, cần cung cấp chính xác giá trị đã đặt trong chỉ mục. Đối với full-text có thể tìm kiếm theo cách tự nhiên hơn. Ràng buộc là những quy tắc áp dụng cho dữ liệu trong bảng. Khi đã cái đặt ràng buộc cho dữ liệu trong bảng, bất kỳ hành động thêm hoặc sửa đổi dữ liệu mà pha 1vỡ ràng buộc này đều bị server CSDL từ chối. 3.2.1.7. Biểu thức và giá trị mặc định Hai đối tượng khác kết hợp với bảng là qui tắc và mặc định. Qui tắc là một biểu thức có thể được đánh giá là True hoặc False khi áp dụng vào giá trị của cột. Ví dụ, một qui tắc xác định giá trị của cột có nằm giữa giá trị 0 và 100 hay không. ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 160 Nếu áp dụng qui tắc này cho một cột cụ thể, server CSDL sẽ không cho phép nhập vào giá trị ngoài phạm vi trên. Giá trị mặc định (default): là một đối tượng riêng biệt chỉ ra một giá trị đơn. Bằng cách kết hợp giá trị mặc định với cột của bảng thì giá trị của cột đó trong dòng dữ liệu mới được thêm vào bảng bằng với giá trị mặc định trong trường hợp không cung cấp giá trị cho cột đó. Mặc dù SQL Server hỗ trợ cả quy tắc và giá trị mặc định nhưng chỉ còn tương thích với các phiên bản cũ. Đối với phát triển mới thì quy tắc được thay thế bằng ràng buộc và giá trị mặc định được thay thế bằng thuộc tính giá trị của cột. 3.2.2. Quản lý Database trong SQL Server 2005 Phần này sẽ giới thiệu một số thao tác cơ bản trên Database như: tạo mới, thay đổi, xóa, lưu trữ dự phòng và phục hồi CSDL. Để thực hiện các thao tác này, chúng ta có thể dùng công cụ trong Object Explorer hoặc tập lệnh T-SQL. 3.2.2.1. Dùng công cụ trong Object Explorer a. Tạo mới Database Tạo mới Database tức là tạo một data file và transaction log. Người cần tạo Database phải có quyền tạo Database. Các thông tin về Database: tên, thuộc tính, vị trí của file Database. Chỉ định các lựa chọn trong việc tạo Database: - Primary file - Secondary file - Transaction log - File name và thư mục lưu file - Kích thước file, sự tăng kích thước của file và kích thước tối đa của file Khởi chạy SQL Server Management Studio, trong Object Explorer, chọn Databases và nhấn chuột phải, trong trình đơn chọn New Database. Xem minh họa bằng hình vẽ dưới đây: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 161 Hộp thoại New Database xuất hiện như hình vẽ dưới đây: Ở trang General, chúng ta cần cung cấp các thông tin sau: - Database name: nhập tên Database, ví dụ QuanLyThuVien Nhấn vào đây để thay đổi kích thước tập tin Kéo thanh trượt ngang sang phải để thay đổi thư mục lưu trữ tập tin ở cột Path ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 162 - Owner: chọn chủ sở hữu cho Database, nếu để chế độ default, mặc định là sa; để chọn giá trị khác nhấn button , hộp thoại Select Database Owner sẽ xuất hiện, nhấn tiếp Browse để chọn. - Database files: liệt kê các tập tin của Database vừa tạo, trong ví dụ này, tập tin QLKH chứa dữ liệu của Database, tập tin QLKH_log chứa thông tin log. Chúng ta có thể thay đổi kích thước tập tin khởi tạo trong cột Initial Size (MB); nếu muốn thay đổi thư mục lưu tập tin, kéo thanh ngang sang phải đến cột Path rồi nhấn để chọn lại đường dẫn mới. Đường dẫn mặc định là: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data Trang Options cho phép chúng ta chọn lựa một số thiết lập cho Database, thông thường chúng ta không thay đổi ở trang này. Nhấn OK ở trang General để tạo Database. Để xác nhận Database vừa tạo, vào cửa sổ Object Explorer nhấn chuột phải vào Databases, chọn Refresh, Database mới tạo sẽ xuất hiện trong danh sách Databases, trong ví dụ này là Database QuanLyThuVien. ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 163 Theo đường dẫn tìm đến thư mục chứa các tập tin của Database QLKH, chúng ta sẽ thấy 2 tập tin mới được tạo là QuanLyThuVien.mdf và QuanLyThuVien_log.ldf Các thuộc tính mặc định của Database vừa tạo được thừa hưởng từ Database Model, đây là Database mẫu dùng để tạo Database mới của SQL Server 2005. Chúng ta có thể tìm thấy Model trong System Databases. Chú ý: Trước khi tạo Database mới nên sao lưu Database Master. Master chứa thông tin về toàn bộ Database được quản lý bởi thể hiện của SQL Server 2005. Việc sao lưu Master sẽ giúp chúng ta khỏi những ảnh hưởng của việc chọn lựa không đúng và cho phép khôi phục lại tình trạng trước khi tạo Database mới. b. Thay đổi, xóa Database Trong cửa sổ Object Explorer, chọn Database vừa tạo, nhấn các lệnh Rename hoặc Delete nếu muốn thay đổi tên Database hoặc xóa Database vừa tạo. ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 164 c. Lưu trữ, phục hồi Database Phục hồi (Backup) Database Một vấn đề chúng ta cần phải chú ý, đó là không phải lúc nào CSDL cũng được đảm bảo sự an toàn mà có thể xảy ra những rủi ro như mất dữ liệu, dữ liệu bị biến dạng, và điều này chúng ta không thể dự đoán trước được. Vì vậy, chúng ta phải có một chiến lược cho việc backup dữ liệu. Các dạng biến cố hay tai họa có thể xảy ra là: - Ðĩa chứa data file hay Transaction Log File hay system file bị mất - Server bị hư hỏng - Những thảm họa tự nhiên như bão lụt, động đất, hỏa hoạn - Toàn bộ server bị đánh cắp hoặc phá hủy - Các thiết bị dùng để backup - restore bị đánh cắp hay hư hỏng - Những lỗi do vô ý của user như lỡ tay delete toàn bộ table chẳng hạn - Những hành vi mang tính phá hoại của nhân viên như cố ý đưa vào những thông tin sai lạc. - Bị hack (nếu server có kết nối với internet). Chúng ta phải phải luôn có biện pháp đề phòng cụ thể cho từng trường hợp cụ thể. Ngoài ra, phải phải xác định thời gian tối thiểu cần phục hồi dữ liệu và đưa server trở lại hoạt động bình thường. ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 165 Các loại Backup: - Full Database Backups: Copy tất cả data files trong một database . Tất cả những user data và database objects như system tables, indexes, user-defined tables đều được backup. - Differential Database Backups: Copy những thay đổi trong tất cả data files kể từ lần full backup gần nhất. - File or File Group Backups: Copy một data file đơn hay một file group. - Differential File or File Group Backups: Tương tự như differential database backup nhưng chỉ copy những thay đổi trong data file đơn hay một file group. - Transaction Log Backups: Ghi nhận một cách thứ tự tất cả các transactions chứa trong transaction log file kể từ lần transaction log backup gần nhất. Loại backup này cho phép ta phục hồi dữ liệu trở ngược lại vào một thời điểm nào đó trong quá khứ mà vẫn đảm bảo tính đồng nhất (consistent). Trong lúc backup SQL Server cũng copy tất cả các hoạt động của database kể cả hoạt động xảy ra trong quá trình backup cho nên ta có thể backup trong khi SQL đang chạy mà không cần phải ngưng lại. Chúng ta cần làm quen với một số thuật ngữ khi backup database: STT Thuật ngữ Giải thích 1 Backup Quá trình copy toàn bộ hay một phần của database, transaction log, file hay file group hình thành một backup set. Backup set được chứa trên backup media (tape or disk) bằng cách sử dụng một backup device (tape drive name hay physical filename 2 Backup Device Một file vật lý (như C:\SQLBackups\Full.bak) hay tape drive cụ thể dùng để record một backup vào một backup media. 3 Backup File File chứa một backup set 4 Backup Media Disk hay tape được sử dụng để chứa một backup set. Backup media có thể chứa nhiều backup sets (ví dụ như từ nhiều SQL Server backups và từ nhiều ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 166 Windows backups 5 Backup set Một bộ backup từ một lần backup đơn được chứa trên backup media. Chúng ta có thể tạo một backup device cố định (permanent) hay tạo ra một backup file mới cho mỗi lần backup. Thông thường chúng ta sẽ tạo một backup device cố định để có thể dùng đi dùng lại đặc biệt cho việc tự động hóa công việc backup. Để backup database, chọn database cần backup, click phải chọn Task -> Backup sẽ xuất hiện hộp thoại Back Up Database – ‘tên database’, trong ví dụ này là database QuanLyThuVien: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 167 Mặc định, backup file là QuanLyThuVien.bak, lựa chọn các thông tin cần thiết xong, nhấn OK sẽ xuất hiện hộp thoại thông báo: Lưu trữ (Restore) Database Trước khi restore database ta phải xác định được thứ tự file cần restore. Các thông tin này được SQL Server chứa trong msdb database và sẽ cho ta biết backup device nào, ai backup vào thời điểm nào. Ðể restore, chọn database cần restore, click phải chọn Task -> Restore, chọn Database, sẽ xuất hiện hộp thoại Restore Database – ‘tên database’: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 168 Nếu restore thành công sẽ xuất hiện hộp thoại thông báo: 3.2.2.2. Dùng T-SQL a. Tạo mới Database Cú pháp tạo Database: CREASE DATABASE database_name [ON] [ [ ,…n]] [LOG ON { [ ,…n]}] [FOR ATTACH] ::= [PRIMARY] ([NAME = ‘logical_file_name’,] FILENAME = ‘os_file_name’ [ , SIZE = size] [ , MAXSIZE = {maxsize | UNLIMITED}] [ , FILEGROWTH = growth_increment]) [ ,…n] ) Giải thích: - For attach: chỉ định rằng database này được tạo từ một database được attach - Size: kích thước ban đầu của file, tính bằng KB, MB, GB, TB. Default là 1MB - Max_size: chỉ định kích thước tối đa của file, tính bằng KB, MB, GB, TB, default là MB. Nếu không chỉ định option này, kích thước file sẽ lớn dần theo dung lượng đĩa cứng - Growth_increment: dung lượng tăng tính bằng KB, MB hoặc %, default là MB. Nếu không chỉ định option này, file sẽ tăng default là 10% Ví dụ tạo Database QuanLyThuVien: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 169 Create Database QuanLyThuVien ON PRIMARY (Name = QLKH_DAT, filename = ‘c:\Program File\Microsoft SQL Server\MSSQL.1\MSSQL\Data\QuanLyThuVienDAT.mdf’, size = 10, maxsize = 50, filegrowth = 5) LOG ON (Name = QLKH_LOG, filename = ‘c:\Program File\Microsoft SQL Server\MSSQL.1\MSSQL\Data\QuanLyThuVienLOG.ldf’, size = 5MB, maxsize = 25MB, filegrowth = 5MB) b. Xóa Database Cú pháp Xóa database: DROP DATABASE database_name Ví dụ: lệnh Drop Database QuanLyThuVien sẽ xóa database QuanLyThuVien khỏi server c. Lưu trữ, phục hồi Database Phục hồi database Cú pháp để backup database: BACKUP DATABASE {database_name} TO [ ,… n] ::= { {logical_backup_device_name} | {DISK | TAPE} = {physical_backup_device_name} } Lưu trữ database RESTORE DATABASE {database_name} [FROM [ ,… n]] 3.2.3. Quản lý Table trong SQL Server 2005 Nội dung: - Tạo table - Sửa cấu trúc table ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 170 - Xóa table Sau khi tạo CSDL mới, cần thiết kế và tạo table để lưu giữ dữ liệu. Một bảng là một lưới hai chiều tạo thành cột (row) và dòng (column). Mỗi column trong table chứa thông tin về một đặc tính riêng biệt, dữ liệu trong mỗi column có kiểu dữ liệu xác định. Row chứa tất cả thông tin về một dữ liệu nhập xác định của một hoặc nhiều row. Mỗi row trong bảng chứa thông tin về một hạng mục. Để tạo, sửa hoặc xóa Table, chúng ta có thể dùng công cụ trong Object Explorer hoặc bằng lệnh T-SQL. 3.2.3.1. Dùng công cụ trong Object Explorer Trong cửa sổ của Object Explorer, sổ danh mục của đối tượng Database, chọn CSDL đã tạo, mở rộng CSDL này, chúng ta sẽ thấy có các đối tượng trong danh mục của CSDL: Database Diagrams, Tables, Views, Synonyms,… Chọn và nhấn phải chuột lên Tables, nhấn chọn New Table sẽ xuất hiện bảng Table – dbo.Table_1 hiển thị bên phải màn hình Management Studio. Bảng này gọi là Table Designer, chứa các cột: - Column Name: ten cột cần tạo - Data Type: kiểu dữ liệu của cột - Allow Nulls: nếu được chọn thì cột này cho phép giá trị NULL Như vậy, mỗi dòng của Table Designer chứa các thông tin đặc tả cho một cột của bảng cần tạo. Nhập tên các cột và kiểu dữ liệu của nó cho bảng muốn tạo. Ví dụ tạo 2 bảng Docgia, Tuasach trong CSDL QuanLyThuVien với các thông tin sau: Docgia Column Name Data Type Allow Nulls Ma_docgia Int Not null Ho Varchar (50) Null Ten_lot Varchar (50) Null Ten Varchar (50) Null Ngay_sinh smalldatetime Null ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 171 Nhấn phải chuột lên dòng Ma_docgia rồi chọn Set Primary Key để cho biết cột này là khoá chính hoặc nhấn nút hình chìa khóa trên thanh công cụ. Save cấu trúc bảng vừa tạo vào CSDL, hộp thoại Choose Name xuất hiện để nhập tên bảng cần tạo, ở đây là bảng Docgia, nhấn OK. Bảng vừa tạo được lưu trong CSDL QuanLyThuVien thuộc nhóm Tables. Để kiểm tra bảng Docgia cùng với những thông tin vừa tạo, mở rộng Tables trong CSDL QuanLyThuVien, đối tượng dbo.Docgia sẽ xuất hiện trong danh mục của Tables, chúng ta có thể xem hình vẽ dưới đây: Bảng Docgia vừa tạo chưa có CSDL nhưng chúng ta có thể truy vấn theo cách sau: chọn table Docgia, nhấp chuột phải và chọn Open Table, sẽ xuất hiện một bảng Table – dbo.Docgia bên phải màn hình Management Studio như hình dưới đây: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 172 Tương tự, chúng ta tạo bảng Tuasach trong CSDL QuanLyThuVien, với cột Ma_tuasach là khóa chính: Tuasach Column Name Data Type Allow Nulls Ma_tuasach Int Not null Tua_sach Varchar (50) Null Tac_gia Varchar (50) Null Tom_tat Varchar (100) Null * Định nghĩa quan hệ cho bảng Các CSDL quan hệ hoạt động dùng quan hệ được định nghĩa giữa các bảng chứa trong mỗi CSDL. Quan hệ giúp tránh việc nhập trùng thông tin, như vậy giảm được nguy cơ dữ liệu không nhất quán. Chúng ta chỉ ra quan hệ bằng cách dùng khóa chính và khóa ngoại trong các bảng quan hệ. Nội dung dữ liệu bảng Docgia xuất hiện khi nhấp Open Table ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 173 Khóa chính là dấu hiệu duy nhất để xác định một dòng trong bảng. Nếu bảng có quan hệ với bảng khác, nó sẽ có một cột là khóa chính trong một bảng khác, cột đó được gọi là cột khóa ngoại. Để tạo quan hệ bảng, trong CSDL phải có ít nhất 2 bảng trở lên, ví dụ trong CSDL QuanLyThuVien, chúng ta tạo thêm bảng Dausach, với các thông tin dữ liệu như sau: Dausach Column Name Data Type Allow Null Isbn Varchar (20) Not null Ma_tuasach int null Ngon_ngu Varchar (50) Null Bia Varchar (50) Null Trang_thai int Null Thiết lập cột Isbn là khóa chính. Để tạo quan hệ giữa 2 bảng Tuasach và Dausach với cột Ma_tuasach là khóa ngoại trong bảng Dausach, chúng ta thực hiện như sau: - Nhấn phải chuột lên bảng Dausach và chọn Modify, bảng Dausach hiện chế độ thiết kế ở bên phải màn hình Management Studio. - Nhấn phải chuột lên dòng bất kỳ của bảng Dausach và chọn Relationship từ trình đơn ngữ cảnh. Hộp thoại Foreign Key Relationship xuất hiện. - Nhấn nút Add để tạo quan hệ khóa ngoại. Ở khu vực bên phải hộp thoại nhấn của dòng Tables and Columns Specification: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 174 Sau khi nhấn nút, hộp thoại Tables and Columns xuất hiện. Trong hộp danh sách Primary key table chọn bảng Tuasach, nhấn chuột ở dòng bên dưới hộp danh sách này để chọn cột khóa chính của bảng Tuasach: Ma_tuasach. Trong hộp văn bản Foreign key table mặc định chứa tên bảng Dausach, nhấn chuột ở dòng bên dưới hộp văn bản để chọn cột khóa ngoại mà nó chứa: Ma_tuasach. - Nhấn OK đóng hộp lại. Thực hiện các thao tác tiếp theo để đóng hộp thoại Foreign Key Relationship, save các thao tác vừa thực hiện. Để kiểm tra, vào Tables, chọn và mổ rộng bảng Dausach, vào Column chúng ta sẽ thấy xuất hiện biểu tượng khóa ngoại (FK) của Ma_tuasach trong bảng Dausach: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 175 * Nhập dữ liệu vào bảng vừa tạo Sau khi tạo bảng, chúng ta có thể nhập dữ liệu vào bảng vừa tạo như sau: - Chọn bảng muốn thêm dữ liệu, ví dụ bảng Docgia - Nhấn phải chuột lên bảng, chọn Open Table Bảng sẽ xuất hiện ở cửa sổ bên phải của giao diện Management Studio. Chúng ta có thể nhập trực tiếp dữ liệu vào từng cột của bảng. Nếu cột nào chấp nhận giá trị Null thì không cần nhập giá trị cho nó. Lưu ý: Nếu đang thêm dữ liệu vào bảng có khóa ngoại, chúng ta không thể thêm dữ liệu nếu không có khóa chính tương ứng trong bảng mà nó có quan hệ tới. Nghĩa là giá trị nhập vào cột khóa ngoại phải tồn tại trong cột khóa chính của bảng có cột đó là khóa chính. * Cập nhật, xóa dữ liệu trong bảng Phần này sẽ giới thiệu một số thao tác để sửa đổi hoặc xóa dữ liệu đang tồn tại trong bảng. Cập nhật dữ liệu ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 176 Để thêm hoặc sửa đổi dữ liệu trong bảng, vào Tables, chọn bảng cần thêm hoặc sử đổi dữ liệu, nhấn chuột phải chọn Modify, bên phải giao diện Management Studio sẽ xuất hiện lại bảng thiết kế, chúng ta có thể thêm hoặc sửa đổi dữ liệu tùy theo yêu cầu cần cập nhật. Hình vẽ dưới đây minh họa cho việc cập nhật dữ liệu trên bảng Dausach của CSDL QuanLyThuVien: Xóa dữ liệu trong bảng Để xóa dữ liệu đang tồn tại trong bảng, thực hiện các thao tác giống như cập nhật dữ liệu vào bảng, khi bảng thiết kế dữ liệu xuất hiện sau khi nhấn lệnh Modify, muốn xóa dữ liệu ở dòng nào, dùng chuột nhấp lên cột đầu tiên màu xám, khi đó cả dòng sẽ được tô sáng, click phải chuột chọn Delete Column. Nếu muốn xóa nhiều dòng dữ liệu cùng lúc, nhấn và giữ chuột trên vùng chọn dòng và rê sang dòng khác. Chúng ta xem ví dụ minh hoạ ở hình vẽ dưới đây: Thêm dữ liệu vào bảng Click chuột vào đây để sửa đổi dữ liệu trong bảng ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 177 3.2.3.2. Dùng T-SQL Cú pháp tạo bảng: CREATE TABLE [Database_name. [owner].| owner.] table_name ({ | column_name AS computed_column_expression | :: = [CONSTRAINT constrain_name]} | [{ PRIMARY KEY | UNIQUE } [,…n] ) Giải thích: - [Database_name. [owner].| owner.]: chỉ ra bảng thuộc CSDL nào, phần này có thể không cần thiết lập - Table_name: tên bảng, - Column_definition: định nghĩa các cột: ::= {column_name data_type} ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 178 [[DEFAULT constant_expression] | [IDENTITY [(seed, increment)] [ [,…n]] - Column_name: tên field - Computed_column_expression: biểu thức tính toán từ các field trong cùng table cho field tính toán. - Default: giá trị mặc định cho cột - Identity: giá trị của cột sẽ được tự động tăng theo seed và increment, chỉ dùng cho field kiểu int - Constraint: ràng buộc cho bảng Constraint cho phép định nghĩa các rule để bảo toàn các ràng buộc dữ liệu trong database. Có 2 loại constraint: + Column constraint: được định nghĩa như một thuộc tính của field, chỉ áp dụng và tham chiếu trên field đó: ::= [CONSTRAINT constraint_name] {[NULL | NOT NULL] | [{PRIMARY KEY | UNIQUE} [CLUSTERED | NONCLUSTERED] [ WITH FILLFACTOR = fillfactor] [ON {fillgroup | DEFAULT}] ] | [[FOREIGN KEY] REFERENCES ref_table ([ref_column]) [ON DELETE {CASCADE | NO ACTION}] [ON UPDATE {CASCADE | NO ACTION}] [ NOT FOR REPLICATION] (local_expression) } ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 179 + Table constraint: được khai báo độc lập với các field trong table và có thể tham chiếu đến nhiều cột trong table đó: ::= [CONSTRAINT constraint_name] {[{PRIMARY KEY | UNIQUE} [CLUSTERED | NONCLUSTERED] {(column [ASC |DES] [,…n])} [ WITH FILLFACTOR = fillfactor] [ON {fillgroup | DEFAULT}] ] | [[FOREIGN KEY] REFERENCES ref_table ([ref_column]) [ON DELETE {CASCADE | NO ACTION}] [ON UPDATE {CASCADE | NO ACTION}] [ NOT FOR REPLICATION] ] | [CHECK [NOT FOR REPLICATION] (local_expression) } Ví dụ tạo bảng Docgia, Dausach trong CSDL QuanLyThuVien: Use QuanLyThuVien Go Create table Docgia (Ma_docgia int identity (1,1) primary key, Ho varchar (50), Ten_lot varchar(50), Ten varchar (50), Ngay_sinh smalldatetime) Create table Dausach (Isbn varchar(50) primary key, Ma_tuasach int , Ngon_ngu varchar(50), Bia varchar(20), Trang_thai int) * Thêm, sửa, xóa dữ liệu trong bảng Để thêm hoặc sửa dữ liệu vào bảng, chúng ta dùng cú pháp lệnh: ALTER TABLE….. ADD…. Để xóa field trong table ta dùng cú pháp lệnh ALTER TABLE….. DROP…. Để xóa table dùng lệnh ALTER TABLE ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 180 Lưu ý: chúng ta không thể xóa 1 table mà được tham chiếu (references) bởi 1 ràng buộc (constraint) foreign key . Ràng buộc foreign key hoặc table tham chiếu phải được xóa trước. Ví dụ: - Thêm field Ngon_ngu vào bảng Dausach: Alter table Dausach Add Ngon_ngu varchar (50) - Thêm khóa chính cho column Isnb: Alter table Dausach Add primary key (Isbn) - Tạo khóa ngoại cho column Ma_tuasach: Alter table Dausach Add foreign key (Isbn) references Tuasach (Ma_tuasach) - Xóa column Ngon_ngu: Alter table Dausach Drop column Ngon_ngu - Xóa ràng buộc khóa ngoại Ma_tuasach: Alter table nocheck constraint FK_Dausacu_Tuasach 3.2.4. Quản lý View trong SQL Server 2005 View là một table ảo, dùng để lưu trữ một truy vấn được định trước, được xem là một đối tượng trong database. Các table tham gia truy vấn được gọi là base table: - Dữ liệu trong View được lấy từ các bảng - Là sự kết hợp từ một hoặc nhiều bảng - Là sự quan hệ giữa một hoặc nhiều bảng - Có thể chứa các tính toán - Có thể kết hợp giữa các bảng và các View khác View hoạt động cũng giống như bảng. Nó cũng chứa dữ liệu theo dòng và cột, cho phép chúng ta nhận giá trị của một cột xác định trong một dòng xác định. Tuy nhiên, View không cho phép việc lưu giá trị. Nó chỉ chứa thông tin hướng dẫn cho server CSDl biết cách nhận dữ liệu đó. Khi mở View, server thực thi những hướng dẫn này và tạo ra bảng ảo từ View. Bảng ảo này chỉ tồn tại khi chúng ta làm việc với nó, nó không bao giờ được lưu giữ trên đĩa cứng. ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 181 Docgia Ma_docgia Ho Tên_lot Tên Ngay_sinh 1 Tran Tan An … 2 Pham Van Anh … 3 Nguyen Hai Binh … Docgia Ho Tên_lot Tên Tran Tan An Pham Van Anh Nguyen Hai Binh Lợi ích của View: - Tập trung vào dữ liệu đang cần - Che những phần dữ liệu chưa cần dùng trong database - Đơn giản hóa việc quản lý quyền user - Cải thiện việc thực thi - Tổ chức lại dữ liệu để xuất ra cho các ứng dụng bên ngoài. Cũng giống như Database, Table, chúng ta có thể dùng công cụ Object Explorer hoặc dùng tập lệnh T-SQL để quản lý View. 3.2.4.1. Dùng công cụ Object Explorer a. Tạo View Để tạo View bằng công cụ, ta thực hiện các bước sau: - Trong cửa sổ Object Explorer, chọn CSDL muốn tạo View, nhấp phải chuột lên Views, chọn New View, hộp thoại Add Table xuất hiện. Trong hộp thoại Add Table chọn bảng muốn tạo View rồi nhấn Add, sau khi chọn xong, nhấn Close để đóng hộp thoại. Use QuanLyThuVien Go Create view dbo.DocgiaView As Select Ho, Ten_lot, Ten from Docgia User’s view ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 182 Trong hình vẽ trên, chúng ta chọn 3 bảng Cuonsach, Docgia và Quatrinhmuon để tạo View Quatrinhmuonsach. Mục đích: lấy dữ liệu của các cuốn sách với thông tin tương ứng về quá trình mượn sách và độc giả mượn sách. - Trong cửa sổ View, kéo cột Ma_cuonsach trong bảng Cuonsach, thả vào cột Ma_cuonsach trong bảng Quatrinhmuon; kéo cột Ma_docgia trong bảng Quatrinhmuon thả vào cột Ma_docgia trong bảng Docgia để tạo liên kết giữa 3 bảng. Nhấn chọn hộp kiểm phía trước tên cột ở nhưng cột muốn tạo View. Các tên cột đã chọn được thêm vào các bảng tiêu chuẩn ở bên dưới các View. Để xem thử kết quả dữ liệu trả về, nhấn F5 hoặc Execute trên thanh công cụ (biểu tượng ). Nhấn save để lưu các thao tác vừa thực hiện. Để xem kết quả, trong cửa sổ Object Explorer, chọn Views sẽ thấy View vừa tạo. Chúng ta có thể xem qua hình vẽ minh họa dưới đây: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 183 b. Thay đổi, xóa View Nếu muốn thay đổi một View đang tồn tại, thực hiện như sau: Nhấn phải chuột lên View muốn thay đổi, chọn Modify từ trình đơn ngữ cảnh. Thực hiện thay đổi rồi nhấn nút save để lưu. Nhấn Close để đóng. Để xóa View, chọn View cần xóa, nhấp chuột phải chọn Delete từ trình đơn ngữ cảnh. 3.2.4.2. Dùng T-SQL a. Tạo View Cú pháp tạo View: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 184 Các tham số sử dụng trong cú pháp tạo View: Ví dụ tạo View vw_rptQuaTrinhMuonSach bằng lệnh T-SQL: Create View vw_rptQuaTrinhMuonSach As Select C.Isbn, C.Ma_cuonsach, C.Tinh_trang, D.Ma_docgia, Q.ngay_hethan, Q.ngay_giotra, Q.tienmuon, Q.tien_datra, Q.tien_datcoc ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 185 From (Cuonsach C inner join Quatrinhmuon Q on C.isbn = Q.isbn) INNER JOIN Docgia D ON D.ma_docgia = Q.ma_docgia Select *from vw_rptQuaTrinhMuonSach (lệnh này để xem view vừa tạo) Luu ý: - Để tạo View phải có quyền CREATE VIEW, và quyền SELECT TABLE trong các table cơ sở. - Tên View lưu trữ trong sysobjects table, tên cột của View được lưu trữ trong syscolumns, text dùng để tạo View lưu trữ trong syscomments table. b. Sửa đổi, xóa View Để sửa cấu trúc View ta dùng cú pháp lệnh: Để xóa View ta dùng lệnh: Drop View view_name 3.2.5. Quản lý Store Procedure trong SQL Server 2005 Store Procedure là một tập các lệnh Transact SQL được đặt tên và lưu trữ trong database server. Store Procedure có thể nhận tham số vào và tham số trả giá trị về. Trả về trạng thái thực thi của procedure là thành công hay không thành công. Các loại store procedure: - System (sp_): có trong master database, được truy xuất từ bất kỳ database nào, nhằm cung cấp các thông tin system catalog hoặc thực hiện các nhiệm vụ của adminstration. - Local: được tạo từ user - Temporary: có tên bắt đầu bằng # (local) hoặc ## (global). Không còn tồn tại sau khi SQL Server shutdown. - Remote: giới hạn việc thực hiện một store procedure trên remote SQL Server Alter View [(column [,…n])] [WITH [,…n]] AS Select_statement [WITH CHECK OPTION] ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 186 - Extended (xp_): chỉ được có trong master database, được thể hiện bởi các ngôn ngữ khác và được gọi là các DLL. Sau khi viết xong extended store procedure, sysadmin đăng ký extended store procedure với SQL Server và sau đó gán quyền cho user khác để thực hiện. Lợi ích của Store Procedure: - Cho phép lập trình theo hướng modular (modular programming) - Thực thi nhanh hơn, giảm được việc chiếm dụng đường truyền mạng - Bảo mật - Xử lý các chức năng và chia xẻ với các ứng dụng khác. Cú pháp tạo Store Procedure Giải thích: - procedure_name: tên store procedure - @paramater: tham số, có tối đa 2.100 parameters trong 1 procedure - data_type: kiểu dữ liệu của tham số, bao gồm tất cả kiểu dữ liệu trong SQL Server, tuy nhiên kiểu cursor chỉ được dùng cho output parameter - default: giá trị mặc định, nếu tham số có giá trị mặc định, thì khi thực hiện procedure, có thể user không cần truyền tham số vào khi thực thi (nó sẽ có giá trị (default). - OUTPUT: chỉ định rằng đây là output parameter - RECOMPLIE: procedure sẽ được dịch lại mỗi khi thực thi - ENCRYPTION: mã hóa mã lệnh của lệnh CREATE PROCEDURE khi lưu vào table syscomment. Lưu ý: - Để tạo procedure, user phải có quyền CREATE PROCEDURE (sysadmin, hoặc database owner) create procedure procedure_name [{@parameter Data_type} [ VARYING ] [ = default ] [ OUTPUT ] ] [,…n] [ WITH {RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION}] AS SQL STATEMENT ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 187 - Có thể tham chiếu đến các table, view, procedure khác cũng như các temporary table - Kích thước tối đa của procedure là 128 MB - Có thể lồng 32 cấp procedure - Dùng procedure sp_helptext để hiển thị nội dung text của store procedure mà user dã tạo. - Không thể kết hợp lệnh CREATE PROCEDURE với các lệnh SQL khác để tạo thành một bó lệnh. Ví dụ: Tạo Procedure với tên sp_ThongTinDocGia trong CSDL QuanLyThuVien Nội dung: liệt kê những thông tin của độc giả tương ứng với mã độc giả. Nếu độc giả là người lớn thì hiển thị thông tin độc giả và thông tin trong bảng người lớn. Nếu độc giả là trẻ em thì hiển thị những thông tin độc giả và thông tin trong bảng trẻ em. Câu lệnh tạo như sau: ALTER procedure sp_ThongTinDocGia @ima_docgia int as declare @flag smallint set @flag = null select top 1 @flag = 1 from Nguoilon where ma_docgia = @ima_docgia if (@flag is not null) select D.ma_docgia, D.ho, D.ten_lot, D.ten, D.ngay_sinh, N.sonha, N.duong, N.quan, N.dienthoai,N.han_sd from Docgia D inner join Nguoilon N on D.ma_docgia=N.ma_docgia where D.ma_docgia=@ima_docgia else begin select top 1 @flag = 1 from Treem where ma_docgia = @ima_docgia if (@flag is not null) select D.ma_docgia,D.ho,D.ten_lot,D.ten,D.ngay_sinh,T.ma_docgia from Docgia D inner join Treem T on D.ma_docgia=T.ma_docgia where D.ma_docgia=@ima_docgia end ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 188 Nếu thành công phía dưới cửa sổ Query, sẽ xuất hiện thông báo: Command (s) completed successfully Để kiểm tra, vào CSDL QuanLyThuVien, chọn Programmability, vào Stored Procedures, sẽ thấy procedure sp_ThongTinDocGia. Để thực thi procedure, chúng ta dùng lệnh EXECUTE procedure_name Ví dụ: Exec sp_ThongTinDocGia ‘01’ Go 3.2.6. Quản lý Trigger trong SQL Server 2005 Trigger là một store procedure đặc biệt được gọi tự động khi user cập nhật dữ liệu trên một table, nghĩa là thay vì được thực thi bởi user, trigger được thực thi bởi server CSDL khi một hoạt động xác định được thực hiện trên bảng. Trigger được định nghĩa trên một table cụ thể. Khác với procedure, trigger không được gọi trực tiếp, không nhận tham số. Trigger là một phần của Transaction: những lệnh trong trigger được xem là một single transaction, có thể được rollback từ bất kỳ chỗ nào trong trigger. Mục đích sử dụng trigger: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 189 - Để thực hiện cascade updates và cascade deletes qua các table quan hệ trong database - Ép buộc tính toàn vẹn dữ liệu phức tạp: thực hiện các ràng buộc có tham chiếu đến các column trong nhiều table - Định nghĩa Custom Error Message: Dùng trigger để trả về các chuỗi thông báo trạng thái của một hành động nào đó. - Bảo trì các dữ liệu không được chuẩn hóa. Trigger hoạt động như thế nào? - Trigger được thực hiện tự động sau khi lệnh INSERT, UPDATE hoặc DELETE được thực hiện trên một table mà trigger đó được định nghĩa. Còn các CONSTRAINT và INSTEAD OF trigger sẽ được kiểm tra trước khi lệnh INSERT, UPDATE hoặc DELETE được thực hiện. CONSTRAINT sẽ được kiểm tra trước trigger. - Một table có thể có nhiều trigger cho một hành động, một trigger có thể được định nghĩa cho nhiều hành động. - Khi có nhiều trigger trong một table thì table owner có thể dùng procedure hệ thống sp_settriggeroder để chỉ định trigger đầu và trigger cuối để thực thi. Thứ tự của các trigger còn lại không thể sắp xếp được. User phải có quyền để thực hiện tất cả các lệnh mà được định nghĩa trong trigger. - Table owners không thể tạo ra các trigger trên View hoặc Temporary tables nhưng có thể tham chiếu đến View và temporary. - Trigger không trả kết quả về - Trigger có thể điều khiển multi-row actions: một hành động INSERT, UPDATE hoặc DELETE gọi một trigger có thể ảnh hưởng lên nhiều dòng dữ liệu. Ta có thể chọn xử lý theo 2 cách: + Xử lý tất cả các dòng cùng với nhau trong trường hợp các dòng ảnh hưởng phải thỏa điều kiện của trigger. + Xử lý từng dòng thỏa điều kiện. Hoạt động của trigger với vai trò là logic tables: ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 190 - Khi có hành động insert, table inserted sinh ra, có cấu trúc giống với cấu trúc table được insert, có dữ liệu là record đang được insert. - Khi có hành động delete, table delete sinh ra, có cấu trúc giống với cấu trúc table bị deleted, có dữ liệu là record đang bị xóa. - Khi có hành động update, sẽ có 2 table inserted và deleted được sinh ra. Cú pháp tạo Trigger: Giải thích: - Table/View: tên table, view được thực hiện khi có hành động tương ứng. - With Encryption: mã hóa nội dung text của lệnh create trigger trong table syscomments - After: Trigger được gọi chỉ khi tất cả các hành động đã thực hiện xong. Các kiểm tra constraint và cascade sẽ được kiểm tra hoàn thành trước khi trigger thực hiện. Default là After nếu chỉ có từ khóa For được chỉ định. After trigger không thể được định nghĩa trên View. - Instead of: chỉ định trigger được thực hiện thay cho hành động của trigger. Instead of trigger không cho phép cập nhật dữ liệu trên View có With Check Option. ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 191 - Delete, insert, update: chỉ định hành động gắn với trigger. Đối với instead of trigger, Delete không cho phép trên table mà có relationship mà chỉ định Cascade On Delete. Tương tự, hành động Update không cho phép trên trên table mà có relationship Cascade On Update. - Table deleted và inserted là logical tables. Chúng có cấu trúc giống với table mà trigger được định nghĩa, chứa các dòng giá trị cũ hoặc mới mà có thể thay đổi bởi hành động của user. Chúng ta có thể truy xuất dữ liệu trong 2 table này trong định nghĩa trigger. - Các giá trị text, ntext hoặc image trong table inserted và deleted không truy xuất được. Khi trigger ở mức 65, giá trị null sẽ được trả về cột có kiểu text, ntext hoặc image trong table inserted hoặc deleted nếu cột cho phép null; chuỗi zero-length được trả về nếu cột có thể null. - With Apend: chèn thêm trigger này vào các trigger đã có trước đó. - If update (column): kiểm tra hành động Update trên cột được chỉ định, không dùng cho hành động delete. Lưu ý: Các lệnh sau đây không được dùng trong định nghĩa trigger: - Create, Alter, Drop, Load, Restore Database - Load, Restore Log - Disk Init - Disk Resize - Reconfigure Ví dụ: Trong CSDL QuanLyThuVien, tạo trigger có tên tg_updCuonSach vói nội dung: khi thuộc tính tình trạng trên bảng Cuonsach được cập nhật thì trạng thái của bảng Dausach cũng được cập nhật. Create trigger [tg_updCuonsach] on [dbo].[Cuonsach] for update as declare @isbn varchar(50) declare my_cursor cursor for select isbn from inserted open my_cursor ĐỒ ÁN CƠ SỞ Nguyễn Minh Châu _ Lê Quân – Lớp C06TH21 - Khoa Công nghệ thông tin – Đại học KTCN 192 fetch next from my_cursor into @isbn while (@@fetch_status = 0) begin exec sp_CapnhatTrangthaiDausach @isbn fetch next from my_cursor into @isbn end close my_cursor deallocate my_cursor Sửa đổi cấu trúc, xóa Trigger: Cú pháp để sửa đổi cấu trúc của Trigger như sau: Để xóa Trigger ta dùng cú pháp lệnh: DROP TRIGGER trigger_name