Giáo trình SQL Server 2000 - Phạm Thị Hoàng Nhung

211 trang phuongnguyen 4140

Download

Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình SQL Server 2000 - Phạm Thị Hoàng Nhung", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

giao_trinh_sql_server_2000_pham_thi_hoang_nhung.pdf

Nội dung text: Giáo trình SQL Server 2000 - Phạm Thị Hoàng Nhung

NỘI DUNG SSQQLL ♦ ♦ ♦ ♦ ♦ ♦ ♦ ♦ ♦ ♦ o Tổng quan SQL Server 2000 SSEERRVVEERR o Thiết kế và thực thi cơ sở dữ liệu o T-SQL Programing o Giao dịch và Khoá 22000000 o Bảo mật và Quản lý người dùng o T-SQL và SQL nâng cao ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ ◊ o Ràng buộc dữ liệu và Chỉ số o Khung nhìn và Con trỏ o Thủ tục o Trigger o Sao lưu và Phục hồi o Chuyển đổi giữa các loại cơ sở dữ liệu o Kiến trúc nhân bản GV. Phạm Thị Hoàng Nhung Bộ môn Công nghệ phần mềm Đại học Thủy lợi
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 1 CHƯƠNG 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 10 1.1 Giới thiệu SQL Server 2000 10 1.1.1 Các đặc trưng của SQL Server 2000: 10 1.1.2 Các phiên bản-edition của SQL Server 11 1.1.3 Các Version của SQL Server 12 1.2 Các thành phần quan trọng trong SQL Server 2000 12 1.2.1 Relational Database Engine 12 1.2.2 Replication - Cơ chế tạo bản sao 12 1.2.3 Data Transformation Service (DTS) - Dịch vụ chuyển dịch dữ liệu 13 1.2.4 Analysis Service - Dịch vụ phân tích dữ liệu 13 1.2.5 English Query – Truy vấn dữ liệu sử dụng tiếng Anh 14 1.2.6 Meta Data Service 14 1.2.7 SQL Server Books Online – Sách dạy SQL Server trực tuyến 14 1.3 SQL Server Tools 14 1.3.1 Enterprise Manager 15 1.3.2 Query Analyzer 15 1.3.3 SQL Profiler 15 1.4 Kiến trúc của SQL Server 16 1.4.1 Client/Server Database system 16 1.4.2 Destop Database system 16 1.5 SQL Server Database 16 1.6 Database Objects-Các đối tượng trong cơ sở dữ liệu 17 1.7 Câu hỏi trắc nghiệm 18 2 CHƯƠNG 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU 19 2.1 Cấu trúc của SQL Server 19 2.2 Cấu trúc vật lý của một SQL Server Database 19 2.3 Nguyên tắc hoạt động của transaction log trong SQL Server 20 2.4 Cấu trúc logic của một SQL Server Database 22 2.5 Các kiểu dữ liệu trong SQL Server (data types) 22 2.5.1 Integers 22 2.5.2 Decimal and Numeric 23 2.5.3 Money and Smallmoney 23 2.5.4 Approximate Numerics 23 2.5.5 Datetime and Smalldatetime 24 2.5.6 Character Strings 24 2.5.7 Unicode Character Strings 24 2.5.8 Binary Strings 25 2.5.9 Các kiểu dữ liệu khác 25 2.6 Câu hỏi trắc nghiệm 26 3 CHƯƠNG 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU- PHẦN THỰC HÀNH 27 2
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 3.1 Tạo cơ sở dữ liệu sử dụng Enterprise Manager 27 3.2 Sửa cơ sở dữ liệu 30 3.3 Xoá cơ sở dữ liệu 30 3.4 Nghiên cứu cơ sở dữ liệu Flight_Information 30 3.4.1 Cấu trúc bảng 30 3.4.2 Dữ liệu trên các bảng 34 3.5 Bài tập 39 4 CHƯƠNG 4. T-SQL PROGRAMING 40 4.1 Giới thiệu SQL Batch Processing 40 4.1.1 Cách thực Thi một nhóm lệnh (Batches) 40 4.1.2 Lệnh GO 40 4.1.3 Ví dụ về Batch: 41 4.1.4 Chú thích (comment) trong batch: 41 4.2 Câu lệnh điều khiển 41 4.2.1 Begin End 41 4.2.2 If Else 42 4.2.3 Vòng lặp While 42 4.2.4 Từ khoá GOTO 43 4.2.5 Từ khoá Return 43 4.2.6 Câu lệnh CASE 43 4.3 Biến(Variables) 44 4.3.1 Grobal variables 44 4.3.2 Local variables 45 4.4 Hàm (Functions) 46 4.4.1 Hàm Conversion 46 4.4.2 Hàm Data Parts 46 4.4.3 Hàm ngày tháng và hàm toán học 47 4.4.4 Hàm hệ thống (System Function) 47 4.4.5 Hàm nhóm 48 4.5 Câu hỏi trắc nghiệm 49 5 CHƯƠNG 5. TRANSACTIONS VÀ LOCKS 51 5.1 Giới thiệu Transactions-Giao dịch 51 5.2 Các tính chất của Transaction 51 5.2.1 Phân loại transaction 51 5.3 Các mức cô lập của Transaction 52 5.3.1 Giới thiệu Dirty Read (Đọc các dữ liệu bẩn) 52 5.3.2 Các mức cô lập 54 5.4 Locks 55 5.4.1 Khái niệm 55 5.4.2 Phân loại 55 5.5 Câu hỏi trắc nghiệm 57 6 CHƯƠNG 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) 59 3
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 6.1 Giới thiệu về SQL Server Sercurity 59 6.2 Quản lý đăng nhập (Login) 59 6.2.1 Xác thực đăng nhập 59 6.2.2 Kiểm tra quyền (Permission) 60 6.2.3 Tạo Login 60 6.3 Quản lý người dùng 61 6.3.1 SQL Server Users 61 6.3.2 Quản lý Usename và Login name 62 6.4 Quản lý Role 62 6.4.1 Database Roles 62 6.4.2 Server Roles 63 6.4.3 Thêm thành viên cho Role 64 6.5 Đối tượng và quyền trên đối tượng (Database Objects and Object Permission) 64 6.5.1 Đối tượng 64 6.5.2 Quyền 65 6.5.3 Cho phép và huỷ bỏ quyền trên đối tượng 65 6.6 Câu hỏi trắc nghiệm 67 7 CHƯƠNG 7. T-SQL PROGRAMMING, TRANSACTIONS, MANAGING SERCURITY – PHẦN THỰC HÀNH 69 7.1 Hướng dẫn trực tiếp 69 7.1.1 Transactions 69 7.1.2 Biến địa phương (local) và biến toàn cục(Global) 73 7.1.3 SQL Server Sercurity 74 7.2 Bài tập 74 8 CHƯƠNG 8. T-SQL VÀ SQL NÂNG CAO 76 8.1 Giới thiệu sơ lược về T- SQL (Transact -SQL) 76 8.1.1 Data Definition Language (DDL) 76 8.1.2 Data Control Language (DCL): 77 8.2 Data Manipulation Language (DML): 77 8.3 Các câu lệnh truy vấn dữ liệu 78 8.3.1 Thực hiện Join để kết nối các bảng 78 8.3.2 Mệnh đề Top n: 81 8.3.3 Mệnh đề INTO 81 8.3.4 Từ khoá UNION(Hợp) 82 8.3.5 Từ khoá CUBE và ROLL UP 82 8.3.6 Mệnh đề COMPUTE và COMPUTE BY 85 8.4 Câu hỏi trắc nghiệm 87 9 CHƯƠNG 9. T-SQL VÀ SQL NÂNG CAO- PHẦN THỰC HÀNH 88 10 CHƯƠNG 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 90 10.1 Ràng buộc dữ liệu 90 10.1.1 Giới thiệu 90 10.1.2 Ràng buộc thực thể 90 4
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 10.1.3 Ràng buộc miền dữ liệu 91 10.1.4 Ràng buộc tham chiếu 91 10.1.5 Ràng buộc NSD tự định nghĩa 91 10.2 Thực hiện các ràng buộc bằng T-SQL 91 10.2.1 PRIMARY KEY Constraint 91 10.2.2 UNIQUE Constraint 92 10.2.3 IDENTITY Property 92 10.2.4 DEFAULT Definition 92 10.2.5 FOREIGN Key Constraint 93 10.2.6 CHECK Constraint 93 10.2.7 NOT NULL Constraint 93 10.2.8 Rules 94 10.3 Indexes 94 10.3.1 Giới thiệu 94 10.3.2 Lời khuyên khi sử dụng indexes 95 10.3.3 Tạo Indexes 95 10.3.4 Các kiểu Indexes 96 10.3.5 Tính chất của Indexes 96 10.3.6 Hiển thị Indexes 97 10.3.7 Cách sử dụng Indexes 97 10.3.8 Xóa Indexes 97 10.3.9 Full-text Searches 98 10.3.10 Full-text Catalogs 98 10.3.11 Sử dụng Full-text Indexes 98 10.4 Câu hỏi trắc nghiệm 99 11 CHƯƠNG 11. DATA INTEGRITY AND INDEXES 101 PHầN THựC HÀNH 101 11.1 Hướng dẫn trực tiếp 101 11.1.1 Tạo ràng buộc PRIMARY KEY 101 11.1.2 Tạo ràng buộc Unique 102 11.1.3 Sử dụng thuộc tính IDENTITY 103 11.1.4 Tạo ràng buộc Default 103 11.1.5 Tạo ràng buộc FOREIGN KEY 104 11.1.6 Tạo ràng buộc Check Constraint 106 11.1.7 Tạo ràng buộc Not Null 107 11.1.8 Tạo Rules 108 11.2 Indexes 109 11.2.1 Tạo indexes 109 11.2.2 Xem và sửa Indexes 111 11.2.3 Sử dụng Indexes 112 11.3 Bài tập 114 12 CHƯƠNG 12. KHUNG NHÌN & CON TRỎ 115 (VIEWS & CURSORS) 115 5
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 12.1 View 115 12.1.1 Giới thiệu 115 12.1.2 Tạo View 116 12.1.3 Lợi ích của View đối với người sử dụng 117 12.1.4 Một số hướng dẫn khi tạo View 117 12.1.5 Sửa dữ liệu thông qua Views 117 12.1.6 Indexed Views 118 12.1.7 Distributed Partitioned Views- Khung nhìn phân tán 120 12.1.8 Sử dụng View để cập nhật dữ liệu 123 12.1.9 Sửa cấu trúc Views 123 12.1.10 Xoá Views 124 12.2 Con trỏ_Cursors 124 12.2.1 Giới thiệu 124 12.2.2 Tạo con trỏ 124 12.2.3 Các bước trong sử dụng Cursor 125 12.2.4 Truy cập dữ liệu bằng cursor 125 12.2.5 Ví dụ 126 12.3 Câu hỏi trắc nghiệm 127 13 CHƯƠNG 13. KHUNG NHÌN VÀ CON TRỎ - PHẦN THỰC HÀNH 129 13.1 Tạo View 129 13.1.1 Sử dụng Create View Wizard 129 13.1.2 Tạo View bằng T-SQL 130 13.2 Sửa View 130 13.3 Con trỏ 131 13.3.1 Khai báo con trỏ (Cursor) 131 13.3.2 Mở con trỏ 132 13.3.3 Truy vấn dữ liệu 132 13.3.4 Truy vấn dòng đầu tiên 132 13.3.5 Truy vấn dòng tiếp theo 133 13.3.6 Truy vấn dòng cuối cùng 133 13.3.7 Truy vấn đến một dòng có vị trí xác định 133 13.3.8 Truy vấn đến dòng liên quan 133 13.3.9 Đóng và xoá vùng nhớ (Deallocating) của con trỏ 134 13.4 Bài tập 135 14 CHƯƠNG 14. THỦ TỤC- STORED PROCEDURES(SPS) 136 14.1 Định nghĩa 136 14.2 Lợi ích khi quản lý dữ liệu bằng SPs 137 14.3 Các kiểu SPs 137 14.3.1 System stored procedures 138 14.3.2 User-defined Stored Procedures 139 14.4 Thông báo lỗi 142 14.4.1 Return Codes 142 6
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 14.4.2 Câu lệnh RAISERROR 143 14.5 Câu hỏi trắc nghiệm 145 15 CHƯƠNG 15. STORED PROCEDURE- PHẦN THỰC HÀNH 146 15.1 Tạo SP bằng EM. 146 15.2 Thực thi SP 147 15.3 Bài tập 147 16 CHƯƠNG 16. TRIGGER 149 16.1 Định nghĩa 149 16.2 Đặc điểm của Trigger 150 16.3 Tạo Trigger 151 16.3.1 Tạo Trigger 151 16.3.2 Hướng dẫn khi tạo Trigger 151 16.4 Các kiểu Trigger 152 16.4.1 INSERT trigger 152 16.4.2 UPDATE trigger 153 16.4.3 DELETE trigger 155 16.5 Các câu lệnh không thể sử dụng trong Triggers 156 16.6 Triggers dây chuyền - Cascading Triggers 156 16.7 Triggers lồng nhau - Nested Triggers 157 16.8 INSTEAD OF Triggers 157 16.9 Câu hỏi trắc nghiệm 159 17 CHƯƠNG 17. TRIGGER – PHẦN THỰC HÀNH 160 17.1 Tạo INSERT trigger. 160 17.2 Tạo DELETE Trigger 160 17.3 Tạo UPDATE Trigger. 161 17.3.1 Tạo Table Level UPDATE Trigger 161 17.3.2 Tạo Column Level Update Trigger 162 17.4 Tạo Trigger có lựa chọn Encryption 163 17.5 Hiển thị danh sách các trigger trong Database 163 17.6 Sử dụng Triggers để tạo ràng buộc tham chiếu (Enforce Referential Intergrity) 164 17.7 Cascade Delete sử dụng Nested trigger 164 17.8 Tạo INSTEAD OF Trigger 166 17.9 Bài tập 167 18 CHƯƠNG 18. SAO LƯU & PHỤC HỒI 169 (BACKUP & RESTORE) 169 18.1 Giới thiệu 169 18.2 Sao lưu cơ sở dữ liệu 169 18.3 Phục hồi cơ sở dữ liệu 170 18.4 Các loại Backup và Restore 173 18.4.1 Các loại sao lưu-Backups 173 18.4.2 Các mô hình khôi phục- Recovery Models 173 18.5 Full Database backup 174 7
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 18.5.1 Cách tạo Full database backup bằng EM 175 18.5.2 Khôi phục Full database backup bằng EM 177 18.6 Transaction log backup 178 18.6.1 Giới thiệu 178 18.6.2 Cắt (truncate) transaction log 179 18.6.3 Điều kiện transaction log backups 180 18.6.4 Cách tạo transaction log backup bằng EM 180 18.6.5 Khôi phục transaction log backup bằng EM 180 18.7 Differential backup 182 18.8 File hoặc Filegroup backup 183 19 CHƯƠNG 19. CHUYỂN ĐỔI GIỮA CÁC LOẠI CƠ SỞ DỮ LIỆU (DTS- DATA TRANSFORMATION SERVICE) 185 19.1 Import cơ sở dữ liệu 185 19.1.1 Import cơ sở dữ liệu từ SQL Server 2000 185 19.1.2 Import từ cơ sở dữ liệu Access 191 19.1.3 Import từ tập tin Excel 192 19.1.4 Import từ tập tin dạng Text 192 19.2 Export cơ sở dữ liệu 194 19.3 Xây dựng lịch trình Import và Export cơ sở dữ liệu 194 19.4 Những điều cần giải quyết sau khi Import hay Export 195 20 CHƯƠNG 20. KIẾN TRÚC NHÂN BẢN (REPLICATION) 196 20.1 Mục tiêu chính của nhân bản 196 20.1.1 Nhất quán dữ liệu (Data consistency) 196 20.1.2 Độc lập site (site autonomy) 197 20.2 Kiến trúc nhân bản 197 20.2.1 Các thành phần chính của nhân bản: 197 20.2.2 Chiều di chuyển dữ liệu 198 20.3 Tác nhân (Agent) 199 20.4 Các loại nhân bản 200 20.5 Nhân bản snapshot(Snapshot replication) 200 20.5.1 Giới thiệu 200 20.5.2 Tác nhân (agent) 201 20.6 Nhân bản giao dịch (transactional replication) 202 20.6.1 Giới thiệu 202 20.6.2 Tác nhân (agent) 202 20.6.3 Thu dọn trong nhân bản transaction 204 20.7 Các dạng nhân bản giao dịch 204 20.7.1 Cập nhật Subscriber lập tức(Immediate_Updating Subscriber) 204 20.7.2 Nhân bản những thực thi của Stored procedure 207 20.8 Nhân bản kết hợp (Merge replication) 207 20.8.1 Giới thiệu 207 20.8.2 Tác nhân (agent) 208 20.8.3 Giải quyết tranh chấp trong nhân bản kết hợp 209 8
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 20.9 Giải quyết tranh chấp 209 9
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 1 Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 1.1 Giới thiệu SQL Server 2000 SQL Server 2000 bao gồm một số đặc trưng tạo nên một Hệ quản trị cơ sở dữ liệu đáp ứng được yêu cầu rất cao trong thực thi cơ sở dữ liệu. 1.1.1 Các đặc trưng của SQL Server 2000: Easy Installation Integration with Internet Supports Client/Server model Operating System Compatibility Hình 1.1. Các đặc trưng của SQL Server Dễ cài đặt (Easy Installation): SQL Server cung cấp các công cụ quản trị và phát triển để cho người sử dụng dễ dàng cài đặt, sử dụng và quản lý hệ thống. Tích hợp với Internet(Integration with Internet): SQL Server 2000 database engine hỗ trợ XML. Nó được tối ưu để có thể chạy trên môi trường cơ sở dữ liệu rất lớn (Very Large Database Environment) lên đến Tera-Byte và có thể phục vụ cùng lúc cho hàng ngàn user. Mô hình lập trình (programming model) SQL Server 2000 được tích hợp với kiến trúc Windows DNA trợ giúp cho phát triển ứng dụng Web. Nó cũng hỗ trợ một số đặc tính khác như English Query để người phát triển hệ thống có thể truy vấn dữ liệu thân thiện hơn. Và Microsoft Search Services cung cấp khả năng tìm kiếm rất mạnh, đặc biệt thích hợp cho phát triển ứng dụng Web. Hỗ trợ kiến trúc Client/Server(Supports Client/Server model): Ứng dụng có thể chạy trên Client, truy cập dữ liệu được lưu trữ trên Server. Server có nhiệm vụ xử lý các yêu cầu và trả lại kết quả cho Client. Tương thích với nhiều hệ điều hành(Operating System Compatibility): Có thể cài đặt trên hầu hết các hệ điều hành của Microsoft (danh sách chi tiết kèm theo). Chú ý khi cài đặt trên Windows NT Server 4, bạn phải chạy thêm Service Pack 5(SP5). 10
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 Operating Enterprise System Enterprise Standard Personal Developer Desktop SQL Evaluation Edition Edition Edition Edition Engine Server Edition CE Microsoft Supported Supported Supported Supported Supported N/A Supported Windows® 2000 DataCenter Windows 2000 Supported Supported Supported Supported Supported N/A Supported Advanced Server Windows 2000 Supported Supported Supported Supported Supported N/A Supported Server Windows 2000 N/A N/A Supported Supported Supported N/A Supported Professional Microsoft Supported Supported Supported Supported Supported N/A Supported Windows NT® 4.0 Server, Enterprise Edition Windows NT Supported Supported Supported Supported Supported N/A Supported 4.0 Server Windows NT N/A N/A Supported Supported Supported N/A Supported 4.0 Workstation Microsoft N/A N/A Supported N/A Supported N/A N/A Windows 98 Microsoft N/A N/A N/A N/A N/A Supported N/A Windows CE 1.1.2 Các phiên bản-edition của SQL Server Enterprise: Chứa đầy đủ các đặc trưng của SQL Server và có thể chạy tốt trên hệ thống lên đến 32 CPUs và 64 GB RAM. Thêm vào đó nó có các dịch vụ giúp cho việc phân tích dữ liệu rất hiệu quả (Analysis Services). Standard: Rất thích hợp cho các công ty vừa và nhỏ vì giá thành rẻ hơn nhiều so với Enterprise Edition, nhưng lại bị giới hạn một số chức năng cao cấp khác, edition này có thể chạy tốt trên hệ thống lên đến 4 CPU và 2 GB RAM. Personal: được tối ưu hóa để chạy trên PC nên có thể cài đặt trên hầu hết các phiên bản của windows, kể cả Windows 98. 11
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 Developer: Có đầy đủ các tính năng của Enterprise Edition nhưng được chế tạo đặc biệt như giới hạn số lượng người kết nối vào Server cùng một lúc Ðây là edition mà các bạn muốn học SQL Server cần có. Edition này có thể cài trên Windows 2000 Professional hay Win NT Workstation. Desktop Engine (MSDE): Ðây chỉ là một engine chạy trên desktop và không có user interface (giao diện). Thích hợp cho việc triển khai ứng dụng ở máy client. Kích thước cơ sở dữ liệu bị giới hạn khoảng 2 GB. Win CE : Dùng cho các ứng dụng chạy trên Windows CE Trial: Có các tính năng của Enterprise Edition, download free, nhưng giới hạn thời gian sử dụng. 1.1.3 Các Version của SQL Server SQL Server của Microsoft được thị trường chấp nhận rộng rãi kể từ version 6.5. Sau đó Microsoft đã cải tiến và hầu như viết lại một engine mới cho SQL Server 7.0. Cho nên có thể nói từ version 6.5 lên version 7.0 là một bước nhảy vọt. Có một số đặc tính của SQL Server 7.0 không tương thích với version 6.5. Trong khi đó từ Version 7.0 lên SQL Server 2000 thì những cải tiến chủ yếu là mở rộng các tính năng về Web và làm cho SQL Server 2000 đáng tin cậy hơn. 1.2 Các thành phần quan trọng trong SQL Server 2000 SQL Server 2000 được cấu tạo bởi nhiều thành phần như Relational Database Engine, Analysis Service và English Query Các thành phần này khi phối hợp với nhau tạo thành một giải pháp hoàn chỉnh giúp cho việc lưu trữ và phân tích dữ liệu một cách dễ dàng. 1.2.1 Relational Database Engine Ðây là một engine có khả năng chứa dữ liệu ở các quy mô khác nhau dưới dạng table và hỗ trợ tất cả các kiểu kết nối (data connection) thông dụng của Microsoft như ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). Ngoài ra nó còn có khả năng tự điều chỉnh (tune up), ví dụ như sử dụng thêm các tài nguyên (resource) của máy khi cần và trả lại tài nguyên cho hệ điều hành khi một user log off. 1.2.2 Replication - Cơ chế tạo bản sao Giả sử bạn có một cơ sở dữ liệu dùng để chứa dữ liệu được các ứng dụng thường xuyên cập nhật. Bạn muốn có một cái cơ sở dữ liệu giống y hệt như thế trên một server khác để chạy báo cáo (report Database) (cách làm này thường dùng để tránh ảnh hưởng đến hiệu năng của server chính). Vấn đề là report server của bạn cũng cần phải được cập nhật thường xuyên để đảm bảo tính chính xác của các báo cáo. Bạn không thể dùng cơ chế back up and restore trong trường hợp này. Lúc 12
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 đó cơ chế replication của SQL Server sẽ được sử dụng để bảo đảm cho dữ liệu ở 2 cơ sở dữ liệu được đồng bộ. Hình 1.2. Tổng quan Microsoft SQL Server 1.2.3 Data Transformation Service (DTS) - Dịch vụ chuyển dịch dữ liệu Nếu bạn có dữ liệu ở các dạng khác nhau cụ thể như chứa trong Oracle, DB2 (của IBM), SQL Server, Microsoft Access , bạn muốn chuyển toàn bộ dữ liệu này sang SQL Server. Công việc này được thực hiện dễ dàng bằng cách sử dụng dịch vụ DTS. 1.2.4 Analysis Service - Dịch vụ phân tích dữ liệu Ta nhận thấy thực tế rằng, dữ liệu được lưu trữ rất nhiều, hết năm này đến năm khác, nhưng khi cần biết một “tri thức” nào từ đó thì không có. Do đó 13
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 Microsoft cung cấp cho bạn một công cụ rất mạnh giúp cho việc phân tích dữ liệu trở nên dễ dàng và hiệu quả bằng cách dùng khái niệm hình khối nhiều chiều (multi- dimension cubes) và kỹ thuật "khai phá dữ liệu" -data mining. 1.2.5 English Query – Truy vấn dữ liệu sử dụng tiếng Anh Ðây là một dịch vụ giúp cho việc truy vấn dữ liệu bằng tiếng Anh "trơn" (plain English). 1.2.6 Meta Data Service Dịch vụ này giúp cho việc chứa đựng và thao tác với Meta Data dễ dàng hơn. Meta data là những thông tin mô tả về cấu trúc của dữ liệu trong cơ sở dữ liệu như dữ liệu. Bởi vì những thông tin này cũng được chứa trong cơ sở dữ liệu nên cũng là một dạng dữ liệu nhưng để phân biệt với dữ liệu "chính thống" người ta gọi nó là Meta Data. 1.2.7 SQL Server Books Online – Sách dạy SQL Server trực tuyến Đây là cuốn sách trực tuyến được đính kèm khi cài đặt SQL Server. Nó là tài liệu không thể thiếu đối với những người muốn làm việc thực sự với hệ quản trị cơ sở dữ liệu này. Hình 1.3. Cửa sổ hướng dẫn SQL Server (Book Online) 14
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 1.3 SQL Server Tools Công cụ để quản trị SQL Server. 1.3.1 Enterprise Manager Ðây là một công cụ cho ta thấy toàn cảnh hệ thống cơ sở dữ liệu một cách rất trực quan. Nó rất hữu ích đặc biệt cho người mới học và không thông thạo lắm về SQL. Hình 1.4. Cửa sổ Enterprise Manager 1.3.2 Query Analyzer Tiếp theo là Query Analyzer. Ðối với một DBA giỏi thì hầu như chỉ cần công cụ này là có thể quản lý cả một hệ thống cơ sở dữ liệu mà không cần đến những thứ khác. Ðây là một môi trường làm việc khá tốt vì ta có thể đánh bất kỳ câu lệnh SQL nào và chạy ngay lập tức. Hình 1.5. Cửa sổ Query Analyzer 1.3.3 SQL Profiler Công cụ thứ ba cần phải kể đến là SQL Profiler. Nó có khả năng "chụp" (capture) tất cả các sự kiện hay hoạt động diễn ra trên một SQL server và lưu lại dưới dạng text file rất hữu dụng trong việc kiểm soát hoạt động của SQL Server. 15
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 Ngoài một số công cụ trực quan như trên chúng ta cũng thường hay dùng osql và bcp (bulk copy) trong command prompt. 1.4 Kiến trúc của SQL Server SQL Server được thiết kế để làm việc hiệu quả trên 2 môi trường: - Hệ thống cơ sở dữ liệu Client/Server (Client/Server Database system) - Hệ thống cơ sở dữ liệu Destop (Destop Database system) 1.4.1 Hệ thống cơ sở dữ liệu Client/Server Server Client Software SQL Server Database Software Network Connection Software Hình 1.6. Hệ thống cơ sở dữ liệu Client/Server 1.4.2 Hệ thống cơ sở dữ liệu Desktop Hình 1.7. Hệ thống cơ sở dữ liệu Desktop 16
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 1.5 SQL Server Database Ta đã biết, Database là tập hợp những dữ liệu được lưu trữ trong file nằm trên đĩa. Một Database sẽ chứa những file dữ liệu trong đó có những dữ liệu thực. SQL Server có 2 kiểu Database: System databases Sample User databases Hình 1.7. Một số cơ sở dữ liệu hệ thống và cơ sở dữ liệu ví dụ - System Databases - User Databases System Databases chứa những thông tin về SQL SERVER. SQL Server sử dụng System Databases để thực hiện và quản lý các Database người dùng (User Databases). System Databases và các ‘sample user Databases’ được tạo ra mặc định ngay khi cài đặt hệ thống. 1.6 Database Objects-Các đối tượng trong cơ sở dữ liệu Tables: Các bảng chứa dữ liệu Columns: Các cột trong bảng Rows: Các hàng trong bảng Data types: Các kiểu dữ liệu Constraints: Các ràng buộc dữ liệu Defaults: Giá trị mặc định của cột nào đó Rules: Các luật được thiết đặt trên dữ liệu Indexs: Chỉ số Views:Các khung nhìn Stored Proceduces: Các thủ tục Triggers Các đối tượng sẽ được đề cập chi tiết trong những chương sau. 17
Chương 1. TỔNG QUAN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2000 1.7 Câu hỏi trắc nghiệm 1. Thành phần nào sau đây cho phép người dùng quản lý các đối tượng trong SQL Server bằng đồ họa. A Service Manager B Query Analyzer C Enterprise Manager D Book Online 2. Thành phần nào sau đây cung cấp giao diện đồ họa (GUI) cho phép người phát triển ứng dụng và người quản trị hệ thống có thể thực hiện những công việc hằng ngày như truy vấn tables, thao tác dữ liệu trong bảng một cách dễ dàng A Service Manager B Query Analyzer C Enterprise Manager D Book Online 3. Có thể chuyển một cơ sở dữ liệu được xây dựng trên Microsoft Access sang SQL Server được không? A Có B Không 4. Bạn có thể cài đặt phiên bản Enterprise trên hệ điều hành Windows 2000 Professional không? A Có B Không 5. SQL Server 2000 có cho phép tạo bản sao của một cơ sở dữ liệu không? A Có B Không 6. Khi cài đặt SQL Server 2000 trên WinNT Server 4, bạn cần cài đặt thêm để hỗ trợ? 18
Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU 2 Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU 2.1 Cấu trúc của SQL Server Như đã trình bày ở các bài trước một trong những đặc điểm của SQL Server 2000 là Multiple-Instance nên khi nói đến một (SQL) Server nào đó là ta nói đến một Instance của SQL Server 2000, thông thường đó là Default Instance. Một Instance của SQL Server 2000 có 4 System Databases và một hay nhiều user Database. Các System Databases bao gồm: Master: Chứa tất cả những thông tin cấp hệ thống (system-level information) bao gồm thông tin về các cơ sở dữ liệu khác trong hệ thống như vị trí của các data files, các login account và các thiết đặt cấu hình hệ thống của SQL Server (system configuration settings). Tempdb: Chứa tất cả những table hay stored procedure được tạm thời tạo ra trong quá trình làm việc bởi user hay do bản thân SQL Server engine. Các table hay stored procedure này sẽ biến mất khi khởi động lại SQL Server hay khi ta disconnect. Model: Cơ sở dữ liệu này đóng vai trò như một bảng tạm (template) cho các cơ sở dữ liệu khác. Nghĩa là khi một user Database được tạo ra thì SQL Server sẽ copy toàn bộ các system objects (tables, stored procedures ) từ Model Database sang Database mới vừa tạo. Msdb: Cơ sở dữ liệu này được SQL Server Agent sử dụng để hoạch định các báo động và các công việc cần làm (schedule alerts and jobs). 2.2 Cấu trúc vật lý của một cơ sở dữ liệu SQL Server Mỗi một cơ sở dữ liệu trong SQL Server đều chứa ít nhất một data file chính (primary), có thể có thêm một hay nhiều data file phụ (Secondary) và một transaction log file. Primary data file (thường có phần mở rộng .mdf): đây là file chính chứa data và những system tables. Secondary data file (thường có phần mở rộng .ndf): đây là file phụ thường chỉ sử dụng khi cơ sở dữ liệu được phân chia để chứa trên nhiều dĩa. Transaction log file (thường có phần mở rộng .ldf): đây là file ghi lại tất cả những thay đổi diễn ra trong một cơ sở dữ liệu và chứa đầy đủ thông tin để có thể roll back hay roll forward khi cần. Data trong SQL Server được chứa thành từng Page 8KB và 8 page liên tục tạo thành một Extent như hình vẽ dưới đây: 19
Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU Hình 2.1. Cấu trúc vật lý của một cơ sở dữ liệu SQL Server Trước khi SQL Server muốn lưu dữ liệu vào một table nó cần phải dành riêng một khoảng trống trong data file cho table đó. Những khoảng trống đó chính là các extents. Có 2 loại Extents: Mixed Extents (loại hỗn hợp) dùng để chứa dữ liệu của nhiều tables trong cùng một Extent. Uniform Extent (loại thuần nhất) dùng để chứa dữ liệu của một table. Ðầu tiên SQL Server dành các Page trong Mixed Extent để chứa dữ liệu cho một table sau đó khi dữ liệu tăng trưởng thì SQL dành hẳn một Uniform Extent cho table đó. 2.3 Nguyên tắc hoạt động của transaction log trong SQL Server Transaction log file trong SQL Server dùng để ghi lại các thay đổi xảy ra trong cơ sở dữ liệu. Quá trình này diễn ra như sau: Đầu tiên khi có một sự thay đổi dữ liệu như Insert, Update, Delete được yêu cầu từ các ứng dụng, SQL Server sẽ tải (load) data page tương ứng lên memory 20
Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU (vùng bộ nhớ này gọi là data cache), sau đó dữ liệu trong data cache được thay đổi(những trang bị thay đổi còn gọi là dirty-page). Tiếp theo mọi sự thay đổi đều được ghi vào transaction log file cho nên người ta gọi là write-ahead log. Cuối cùng thì một quá trình gọi là Check Point Process sẽ kiểm tra và viết tất cả những transaction đã được commited (hoàn tất) vào đĩa cứng (flushing the page). Hình 2.2. Quá trình hoạt động của Transaction Ngoài Check Point Process những dirty-page còn được đưa vào đĩa bởi một Lazy writer. Ðây là một thành phần làm nhiệm vụ quét qua phần data cache theo một chu kỳ nhất định sau đó lại dừng để chờ lần quét tới. Check Point Process hoạt động như thế nào để có thể đảm bảo một transaction được thực thi mà không gây ra dữ liệu “bẩn”-dirty data. Trong hình vẽ bên dưới (Transaction Recovery), một transaction được biểu diễn bằng một mũi tên. Trục nằm ngang là trục thời gian. Giả sử một Check Point được đánh dấu vào thời điểm giữa transaction 2 và 3 như hình vẽ và sau đó sự cố xảy ra trước khi gặp một Check point kế tiếp. Như vậy khi SQL Server được restart nó sẽ dựa trên những gì ghi trong transaction log file để phục hồi dữ liệu (xem hình vẽ). Ðiều đó có nghĩa là SQL Server sẽ không cần làm gì cả đối với transaction 1 vì tại thời điểm Check point data đã được lưu vào đĩa rồi. Trong khi đó transaction 2 và 4 sẽ được Roll Forward vì tuy đã được commited nhưng do sự cố xảy ra trước 21
Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU thời điểm check point kế tiếp nên dữ liệu chưa kịp lưu vào đĩa. Tức là dựa trên những thông tin được ghi trên log file SQL Server hoàn toàn có đầy đủ cơ sở để viết vào đĩa cứng. Còn transaction 3 và 5 thì chưa được commited (do bị down bất ngờ) cho nên SQL Server sẽ Roll Back hai transaction này dựa trên những gì được ghi trên log file. Hình 2.3. Khôi phục Transaction 2.4 Cấu trúc logic của một SQL Server Database Hầu như mọi thứ trong SQL Server được tổ chức thành những objects ví dụ như tables, views, stored procedures, indexes, constraints Những system objects trong SQL Server thường có bắt đầu bằng chữ sys hay sp. Các objects trên sẽ được nghiên cứu lần lượt trong các bài sau. 2.5 Các kiểu dữ liệu trong SQL Server (data types) 2.5.1 Integers 2.5.1.1 Bigint Là kiểu dữ liệu Integer có miền giá trị từ -2^63 (-9223372036854775808) đến 2^63-1 (9223372036854775807). 22
Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU 2.5.1.2 Int Là kiểu dữ liệu Integer có miền giá trị từ -2^31 (-2,147,483,648) đến 2^31 - 1 (2,147,483,647). 2.5.1.3 Smallint Là kiểu dữ liệu Integer có miền giá trị từ 2^15 (-32,768) đến 2^15 - 1 (32,767). 2.5.1.4 Tinyint Là kiểu dữ liệu Integer có miền giá trị từ 0 đến 255. 2.5.1.5 Bit Chỉ có một trong hai giá trị là 0 hoặc 1 2.5.2 Decimal and Numeric 2.5.2.1 Decimal Độ chính xác được xác định và miền giá trị từ -10^38 +1 đến 10^38 –1. 2.5.2.2 Numeric Chức năng tương tự như decimal. 2.5.3 Money and Smallmoney 2.5.3.1 Money Là kiểu dữ liệu tiền tệ có miền giá trị từ -2^63 (-922,337,203,685,477.5808) đến 2^63 - 1 (+922,337,203,685,477.5807 2.5.3.2 Smallmoney Là kiểu dữ liệu tiền tệ có miền giá trị từ -214,748.3648 đến +214,748.3647 2.5.4 Approximate Numerics 2.5.4.1 Float Độ chính xác (phần thập phân) thay đổi và miền giá trị từ -1.79E + 308 đến 1.79E + 308. 2.5.4.2 Real Độ chính xác (phần thập phân) thay đổi và miền giá trị từ -3.40E + 38 đến 3.40E + 38. 23
Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU 2.5.5 Datetime and Smalldatetime 2.5.5.1 Datetime Kiểu dữ liệu ngày/tháng từ January 1, 1753, tới December 31, 9999, với độ chính xác là 3/100 của second, hoặc 3.33 milliseconds. 2.5.5.2 Smalldatetime Kiểu dữ liệu ngày/tháng từ January 1, 1900, tới June 6, 2079, với độ chính xác là 1 phút. 2.5.6 Character Strings 2.5.6.1 Char Kiểu dữ liệu character có độ dài xác định và không theo mã Unicode, có khả năng lưu trữ tối đa 8,000 characters. 2.5.6.2 Varchar Kiểu dữ liệu character có độ dài thay đổi và không theo mã Unicode, có khả năng lưu trữ tối đa 8,000 characters. 2.5.6.3 Text Kiểu dữ liệu character có độ dài thay đổi và không theo mã Unicode, có khả năng lưu trữ tối đa 2^31 - 1 (2,147,483,647) characters. 2.5.7 Unicode Character Strings 2.5.7.1 Nchar Kiểu dữ liệu character có độ dài xác định và theo mã Unicode, có khả năng lưu trữ tối đa 4,000 characters. 2.5.7.2 Nvarchar Kiểu dữ liệu character có độ dài thay đổi và theo mã Unicode, có khả năng lưu trữ tối đa 4,000 characters. 2.5.7.3 Ntext Kiểu dữ liệu character có độ dài thay đổi và theo mã Unicode, có khả năng lưu trữ tối đa 2^30 - 1 (1,073,741,823) characters. 24
Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU 2.5.8 Binary Strings 2.5.8.1 Binary Kiểu dữ liệu Binary có độ dài xác định và khả năng lưu trữ tối đa 8,000 bytes. 2.5.8.2 Varbinary Kiểu dữ liệu Binary có độ dài thay đổi và khả năng lưu trữ tối đa 8,000 bytes. 2.5.8.3 Image Kiểu dữ liệu Binary có độ dài thay đổi và khả năng lưu trữ tối đa 2^31 - 1 (2,147,483,647) bytes. 2.5.9 Các kiểu dữ liệu khác 2.5.9.1 Cursor Là một tham chiếu tới một con trỏ 2.5.9.2 Sql_variant Là kiểu dữ liệu có khả năng lưu trữ rất nhiều kiểu dữ liệu khác nhau của SQL SERVER, ngoại trừ text, ntext, timestamp, and sql_variant. 2.5.9.3 Table Là kiểu dữ liệu đặc biệt được sử dụng để lưu trữ tập kết quả của một quá trình xử lý. 2.5.9.4 Uniqueidentifier Là kiểu dữ liệu có khả năng tự động cập nhật giá trị khi có 1 bản ghi được thêm mới (tương tự như kiểu dữ liệu Autonumber của Microsoft Access) 25
Chương 2. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU 2.6 Câu hỏi trắc nghiệm 1. Cơ sở dữ liệu hệ thống được sử dụng như là template khi tạo tất cả các cơ sở dữ liệu mới. 2. Một cơ sở dữ liệu trong SQL Server chứa ít nhất file? 3. Các file chứa cơ sở dữ liệu thường được đặt ở đường dẫn ? 4. Một cơ sở dữ liệu muốn chuyển sang thực hiện trên máy tính khác có cài SQL Server, thông thường bạn phải copy đi những file ? 5. Những thay đổi trong một cơ sở dữ liệu được ghi lại ở file ? 6. Dữ liệu ở cột 1 được sử dụng để lưu trữ dữ liệu về sản phẩm của 1 cửa hàng, bạn hãy chọn kiểu dữ liệu tương ứng với nó ở cột 2. Cột 1 Đáp án Cột 2 1. Tên sản phẩm a. Int 2. Hình ảnh mô tả b. Smallmoney 3. Giá sản phẩm c. Varchar 4. Hạn sử dụng d. Datetime 5. Số lượng tồn trong kho e. Image 26
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 3 Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU- Phần thực hành Mục đích: - Sử dụng Enterprise Manager để tạo, sửa và xoá cơ sở dữ liệu (database) - Tạo, sửa và xoá bảng (table) - Thêm, sửa, xoá dữ liệu trong các bảng - Xem thông tin của bảng 3.1 Tạo cơ sở dữ liệu sử dụng Enterprise Manager Trong Enterprise Manager, chúng ta có thể tạo cơ sở dữ liệu trực tiếp hoặc sử dụng hỗ trợ Wizard. Sau đây là cách tạo trực tiếp: 1. Khởi động Service Manager (thao tác này để khởi động SQL SERVER). 2. Kích chọn Enterprise Manager trong thanh menu của Microsoft SQL Server. 3. Chọn Server chứa cơ sở dữ liệu 4. Kích chọn Action/New/Database từ menu Action 5. Nhập tên cơ sở dữ liệu (Ví dụ: Flights) ) Hình 3.1 6. Kích OK 27
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 7. Kích đúp vào đối tượng Database trong cửa sổ bên phải, chúng ta sẽ nhìn thấy cơ sở dữ liệu Flights vừa được tạo Hình 3.2 SQL Server hỗ trợ thực hiện Wizard cho một vài công việc chung. Sau đây là cách tạo cơ sở dữ liệu thực hiện Wizard: 1. Kích chọn Tools/Wizard từ menu Tool trên menu bar của Enterprise Manager. 2. Chọn Create Database Wizard (Hình 3.3) 3. Kích OK 4. Kích Next 5. Nhập tên cơ sở dữ liệu (ví dụ: Flight Information) 6. Kích Next Hình 3.3 28
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành Hình 3.4 7. Xuất hiện cửa sổ cho phép nhập tên file chứa cơ sở dữ liệu và kích thước tối đa của file. Hình 3.5 29
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 8. Kích Next. Xuất hiện cửa sổ cho phép tăng kích thước cơ sở dữ liệu khi nó quá lớn và giới hạn kích thước 9. Kích Next. Xuất hiện cửa sổ cho phép thay đổi tên và kích thước của file log (ghi lại lịch sử) của cơ sở dữ liệu 10. Kích Next. Xuất hiện cửa sổ cho phép tăng kích thước log file khi nó quá lớn và giới hạn kích thước. 11. Kích Next Hình 3.6 12. Kích Finish để hoàn thành. 3.2 Sửa cơ sở dữ liệu Sau khi tạo cơ sở dữ liệu, chúng ta có thể thay đổi định nghĩa ban đầu. Các loại thay đổi: - Mở rộng, co hẹp kích thước data file hoặc log file. - Tạo filegroups - Thay đổi tên cơ sở dữ liệu 3.3 Xoá cơ sở dữ liệu - Chọn cơ sở dữ liệu cần xoá - Sau đó Delete. 3.4 Nghiên cứu cơ sở dữ liệu Flight_Information 3.4.1 Cấu trúc bảng 1. Bảng Airlines_Master: Chi tiết của các Airlines 30
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 2. Bảng Category_master: Danh mục các Airlines 3. Bảng Class_Master: Các mức(hạng vé) airline cung cấp 4. Bảng City_Master: Các thành phố đi và đến 5. Bảng Day_Master: Các ngày có thể phục vụ 31
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 6. Bảng Meal: Các thức ăn có thể lựa chọn 7. Bảng Service: Các dịch vụ được cung cấp 8. Bảng Status_Master: Tình trạng của vé máy bay 9. Airline_Service: Dịch vụ được cung cấp trên mỗi hãng 32
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 10. Airline_Meal: Các thức ăn được phục vụ trên mỗi hãng 11. Flight: Các chuyến bay của các hãng 12. Flight_days: Ngày mỗi chuyến bay có thể phục vụ 33
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 13. Flight_details: Chi tiết của mỗi chuyến bay 14. Passenger: Chi tiết về các khách hàng 15. Reservation: Chi tiết về việc đặt vé máy bay 3.4.2 Dữ liệu trên các bảng 1. Bảng Airlines_Master 34
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 2. Bảng Category_Master 3. Bảng Class_Master 4. Bảng City_Master 5. Bảng Day_Master 6. Bảng Meal 7. Bảng Service 35
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 8. Bảng Status_Master 9. Bảng Airline_Service 10. Bảng Airline_Meal 11. Bảng Flight 36
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 12. Bảng Flight_days 37
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 13. Bảng Flight_details 14. Bảng Passenger 15. Bảng Reservation 38
Chương 3. THIẾT KẾ VÀ THỰC THI CƠ SỞ DỮ LIỆU –Phần thực hành 3.5 Bài tập 1. Tạo cơ sở dữ liệu Flight Informtion 2. Thêm dữ liệu vào các bảng (như trên) 3. Chọn Query Analyzer để thực hiện một số yêu cầu sau: a. Tăng giá tất cả các chuyến bay của hãng ‘IC01’ lên 10% b. Hãng AI (Air India) quyết định không phục vụ thức ăn có meal_code= ‘CONV’. Hãy xoá thông tin liên quan. c. Xoá cột Type của bảng Flight d. Xóa bảng Status_Master trong cơ sở dữ liệu e. Cập nhật lại cột Sex trong bảng Passanger: Nếu là F thì sửa lại Females, nếu là M thì sửa lại là Males. 39
Chương 4. T-SQL PROGRAMING 4 Chương 4. T-SQL PROGRAMING 4.1 Giới thiệu SQL Batch Processing Individual SQL commands Batch Grouped to form a batch Compiled as single execution plan Hình 4.1. Cách thực hiện của nhóm lệnh (Batches) 4.1.1 Cách thực Thi một nhóm lệnh (Batches) Khi thực thi một nhóm lệnh SQL Server sẽ phân tích và tìm biện pháp tối ưu cho các câu lệnh như một câu lệnh đơn và chứa execution plan đã được biên dịch (compiled) trong bộ nhớ sau đó nếu nhóm lệnh trên được gọi lại lần nữa thì SQL Server không cần biên dịch mà có thể thực thi ngay điều này giúp cho một batch chạy nhanh hơn. Những câu lệnh nằm trong 1 batch sẽ được xử lý cùng lúc. Nếu một trong những câu lệnh của Batch không thực hiện được thì Batch cũng không được thực hiện 4.1.2 Lệnh GO Lệnh này chỉ dùng để gửi một tín hiệu cho SQL Server biết đã kết thúc một batch job và yêu cầu thực thi. Nó vốn không phải là một lệnh trong T-SQL. 40
Chương 4. T-SQL PROGRAMING 4.1.3 Ví dụ về Batch: Use Pubs Select * from authors Update authors set phone= '890 451-7366’ where au_lname= 'White' GO 4.1.4 Chú thích (comment) trong batch: - Là chuỗi ký tự trong chương trình được compiler bỏ qua. - Sử dụng để chú thích hoặc tạm thời bỏ qua những phần trong chương trình đang được cân nhắc - Thực hiện bảo trì chương trình nguồn dễ dàng - Chú thích thường được dùng để ghi lại tên chương trình, tên tác giả, ngày của những thay đổi chính trong chương trình - Chú thích có thể sử dụng biểu diễn những tính toán phức tạp và những giải thích trong phương pháp lập trình - Các kiểu của Comment: SQL dùng dấu để đánh dấu phần chú thích cho câu lệnh đơn và dùng /* */ để chú thích cho một nhóm. Ví dụ: USE Northwind GO First line of a multiple-line comment. SELECT * FROM Employees /*This is a comment*/ 4.2 Câu lệnh điều khiển Chúng ta có thể sử dụng các câu lệnh điều khiển khi lập trình cùng với T- SQL. 4.2.1 Begin End Cú pháp: BEGIN { statement | statement_block } END 41
Chương 4. T-SQL PROGRAMING 4.2.2 If Else IF ELSE: Rẽ nhánh theo kết quả của biểu thức Logic. Cú pháp: IF Boolean_expression { sql_statement | statement_block } [ ELSE { SQL SERVER_statement | statement_block } ] Ví dụ: Hình 4.1 4.2.3 Vòng lặp While WHILE: Chúng ta có thể thực hiện câu lệnh SQL hoặc khối các lệnh dựa trên một số điều kiện. Các câu lệnh trong While được thực hiện lẵp đi lại lại trong khi biểu thức Logic còn đúng. Cú pháp: WHILE Boolean_expression { statement | statement_block } [ BREAK ] { statement | statement_block } [ CONTINUE ] Sử dụng BREAK và CONTINUE: Chúng ta có thể sử dụng từ khóa CONTINUE hoặc BREAK trong vòng lặp WHILE để điều khiển việc thực hiện các câu lệnh. Ví dụ: 42
Chương 4. T-SQL PROGRAMING USE pubs GO WHILE (SELECT AVG(price) FROM titles) $50 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear' 4.2.4 Từ khoá GOTO Chúng ta có thể thay đổi được thứ tự thực hiện bằng việc xác định vị trí (Label). Cú pháp: GOTO label 4.2.5 Từ khoá Return Chúng ta có thể sử dụng RETURN ở bất kỳ vị trí nào để thoát ra khỏi khối, thủ tục. Những câu lệnh sau lệnh Return không được thực hiện Cú pháp: RETURN [ integer_expression ] 4.2.6 Câu lệnh CASE Từ khóa Case trả lại giá trị dựa trên kết quả của biểu thức logic. Nó có thể được sử dụng ở bất kỳ vị trí nào phép toán được phép. Cú pháp: CASE expression WHEN expression1 THEN expression1 [[WHEN expression2 THEN expression2] [ ]] [ELSE expression] END Ví dụ 1: SELECT au_fname,au_lname,CASE state WHEN 'OR' THEN 'Oregon' END AS StateName FROM authors Ví dụ 2: 43
Chương 4. T-SQL PROGRAMING UPDATE publishers SET state = CASE WHEN country "USA" OR pub_id = "9999" 4.3 Biến(Variables) SQL Server hỗ trợ 2 loại biến: - Grobal variables-Biến địa phương - Local variables- Biến toàn cục Hình 4.2. Sử dụng biến toàn cục 4.3.1 Grobal variables Biến toàn cục trong SQL Server được bắt đầu bằng 2 ký hiệu @. Chúng ta có thể truy cập giá trị của bất kỳ biến nào cùng với câu lệnh SELECT đơn giản. Danh sách các biến toàn cục: Tên biến Ý nghĩa @@CONNECTIONS Số lượng những kết nối với Server từ khi Server 44
Chương 4. T-SQL PROGRAMING khởi động lại @@CPU_BUSY Thời gian (tính theo milliseconds) của hệ thống tính từ khi SQL Server khởi động @@CURSOR_ROWS Số lượng các dòng dữ liệu của con trỏ mới được mở gần đây nhất @@DATEFIRST Giá trị hiện tại của biến SET DATEFIRST – chỉ ra ngày đầu tiên của @@ERROR Mã số lỗi của câu lệnh T-SQL cuối cùng bị lỗi @@FETCH_STATUS Trạng thái truy nhập con trỏ: 0 nếu trạng thái truy nhật cuối cùng thành công; -1 nếu có lỗi @@IDENTITY Giá trị xác định (identity) cuối cùng được thêm vào @@LANGUAGE Tên ngôn ngữ hiện tại đang sử dụng @@MAX_CONNECTIONS Số lượng tối đa các kết nối có thể thực hiện đồng thời @@ROWCOUNT Số lượng các dòng kết quả của câu lệnh SQL gần đây nhất @@SERVERNAME Tên của Server địa phương @@SERVICENAME Tên của SQL Service trên máy tính hiện tại @@TRANSCOUNT Số lượng những giao dịch đang được mở @@VERSION Thông tin về phiên bản SQL Server đang sử dụng 4.3.2 Local variables Cách khai báo và thực hiện: Hình 4.3. Sử dụng biến địa phương (Local variables) 45
Chương 4. T-SQL PROGRAMING 4.4 Hàm (Functions) Hàm có thể được chia làm 3 loại như sau: Rowset Functions : Loại này thường trả về một object và được đối xử như một table. Ví dụ như hàm OPENQUERY sẽ trả về một recordset và có thể đứng vị trí của một table trong câu lệnh Select. Aggregate Functions : Loại này làm việc trên một số giá trị và trả về một giá trị đơn hay là các giá trị tổng. Ví dụ như hàm AVG sẽ trả về giá trị trung bình của một cột. Scalar Functions : Loại này làm việc trên một giá trị đơn và trả về một giá trị đơn. Trong loại này lại chia làm nhiều loại nhỏ như các hàm về toán học, về thời gian, xử lý kiểu dữ liệu String Ví dụ như hàm MONTH('2002-09-30') sẽ trả về tháng 9. 4.4.1 Hàm Conversion Hàm conversion dùng để chuyển đổi giá trị của một kiểu dữ liệu này sang kiểu dữ liệu khác. Thêm nữa, chúng ta có thể sử dụng nó để đạt được một loạt định dạng ngày tháng đặc biệt. SQL Server cung cấp hàm chuyển đổi đơn giản CONVERT(). Cú pháp: CONVERT(datatype[(length)] expression [ style]) Ví dụ: SELECT ‘EMP ID:’ + CONVERT (CHAR(4), EMPLOYEEID FROM EMPLOYEES 4.4.2 Hàm Data Parts DatePart Abbreviation Values Hour hh 0-23 Minute Mi 0-59 Second Ss 0-59 Millisecond Ms 0-999 Day of year Dy 1-366 Day Dd 1-31 Week wk 1-53 Weekday dw 1-7 Month mm 1-12 Quarter qq 1-4 Year yy 1753-9999 46
Chương 4. T-SQL PROGRAMING 4.4.3 Hàm ngày tháng và hàm toán học Hàm ngày tháng (Date Functions) Date Functions GETDATE() DATEADD(datepart,number,date) DATEDIFF(datepart,date1,date2) DATENAME(datepart,date) DATEPART(datepart,date) Hàm toán học (Mathematical) Mathematical Functions ABS(num_expr) CEILING(num_expr) FLOOR(num_expr) POWER(num_expr,y) ROUND(num_expr,length) Sign(num_expr) Sqrt(float_expr) 4.4.4 Hàm hệ thống (System Function) Function DB_ID([‘database_name’]) DB_NAME([database_id]) HOST_ID() HOST_NAME() ISNULL(expr,value) OBJECT_ID(‘obj_name’) OBJECT_NAME(object_id) SUSER_SID([‘login_name’]) SUSER_ID([‘login_name’]) SUSER_SNAME([server_user_id]) SUSER_NAME([server_user_id]) USER_ID([‘user_name’]) USER_NAME([user_id]) 47
Chương 4. T-SQL PROGRAMING 4.4.5 Hàm nhóm Sum(col_name) Avg(col_name) Count (col_name) Min(col_name) Max(col_name) 48
Chương 4. T-SQL PROGRAMING 4.5 Câu hỏi trắc nghiệm 1. Batch là một tập hợp của một hoặc nhiều câu lệnh SQL được Client gửi tới Server như một khối thống nhất. A Đúng B Sai 2. Câu lệnh nào theo sau được dùng để thoát khỏi vòng lặp While. A Close B Exit C Break D Không có lệnh nào trong 3 lệnh trên 3. Tập hợp một số câu lệnh muốn được thực hiện 10 lần, sử dụng cấu trúc nào sau đây? A IF ELSE B While C Case D Select 4. Câu lệnh nào theo sau được sử dụng để yêu cầu SQL Server đợi 15 giây trước khi nó tiếp tục? A WAITFOR ’00:00:15’ DELAY B WAITFOR DELAY BY ’00:00:15’ C WAITFOR DELAY ’00:00:15’ D WAIT FOR ’00:00:15’ 5. Kiểm tra đoạn mã sau đây: DECLARE @v_empcount INT SELECT @v_empcount = COUNT(*) FROM employee IF (@v_empcount>20) GOTO great ELSE GOTO less SELECT * FROM employee great: PRINT ‘The number is greater’ GOTO end1 GO less: PRINT ‘The number is less’ end1: GO 49
Chương 4. T-SQL PROGRAMING Câu nào là kết quả của đoạn mã trên nếu có 20 bản ghi trong bảng Employee? A Kết quả in ra là: ‘The number is great’ B Kết quả in ra là: ‘The number is less’ C Không in ra gì cả D Có thông báo lỗi 6. functions được sử dụng khi cần trả lại các thông tin vể thiết đặt (settings) của SQL Server? A Server B Aggregate C System D Text 7. Phát biểu nào sau đây là đúng cho việc chuyển đổi (conversion) kiểu dữ liệu? A Khi chuyển đổi bất kỳ một giá trị nào không phải là 0 (nonzero) sang kiểu dữ liệu bit thì dữ liệu đó sẽ trở thành 1. B Khi chuyển đổi từ kiểu dữ liệu Char thành kiểu dữ liệu Money bạn không thể có được ký hiệu $ đi kèm. C Kiểu dữ liệu Image không thể được chuyển thành kiểu dữ liệu Binary. D Nếu bạn không chỉ ra độ dài cho kiểu dữ liệu trong biểu thức chuyển đổi, SQL Server sẽ tự động gán cho nó có độ dài là 20. 8. Truy vấn nào sau đây sẽ trả lại giá trị của tháng hiện tại? A Select DATEPART(mm,getdate()) As ‘Month’ B Select DATEPART(mon,getdate()) As ‘Month’ C Select GETDATE(DATEPART(MM)) As ‘Month’ D Select GETDATE(DATEPART(MON)) As ‘Month’ 50
Chương 5. TRANSACTIONS VÀ LOCKS 5 Chương 5. TRANSACTIONS VÀ LOCKS 5.1 Giới thiệu Transactions-Giao dịch Trong cơ sở dữ liệu đa người dùng, dữ liệu được lưu trữ ở hệ thống máy chủ và các chương trình của người sử dụng có thể đồng thời thao tác trên những dữ liệu đó. Việc thực hiện một chương trình truy cập và thay đổi nội dung của cơ sở dữ liệu được gọi là Transaction. Một Transaction là một hoặc một tập các lệnh được thực hiện như một khối (unit of works), tức là nó có thể thành công hoặc thất bại hoàn toàn. Có nghĩa là, một transaction thành công nếu tất cả các lệnh trong transaction đó được thực hiện và thất bại nếu một trong số các lệnh của khối thất bại. 5.2 Các tính chất của Transaction Để đảm bảo tính toàn vẹn dữ liệu, một giao dịch phải có các tính chất sau: - Atomicity _Nguyên tử. - Consistency_ Nhất quán - Isolation _ Cô lập - Durability _Bền vững 4 tính chất trên gọi là tính ACID của giao dịch Atomicity _Nguyên tử: Đảm bảo hoặc toàn bộ các hoạt động trong giao dịch thành công hoặc thất bại Consistency_ Nhất quán: Khi transaction hoàn thành, dữ liệu phải ở trạng thái nhất quán. Ví dụ: Trong 1 transaction, tài khoản A thực hiện chuyển tiền cho 1 tài khoản B là 5 triệu, thì sau khi hoàn thành tài khoản B có số tiền là B+5triệu và A là A-5triệu. Isolation _ Cô lập: Cho dù có nhiều giao dịch có thể thực hiện đồng thời, hệ thống phải đảm bảo rằng sự thực hiện đồng thời đó dẫn đến một trạng thái hệ thống tương đương khi các giao dịch thực hiện tuần tự theo một thứ tự nào đó. Durability _Bền vững: Sau khi giao dịch thành công, giả sử xảy ra sự cố thì tất cả các dữ liệu được thay đổi trong giao dịch vẫn được hồi phục lại theo yêu cầu giao dịch. 5.2.1 Phân loại transaction Có 3 loại: - Explicit transactions (rõ ràng) - Implicit transactions (ngầm định) 51
Chương 5. TRANSACTIONS VÀ LOCKS - Auto-Commit transactions (Transaction ở chế độ tự động thực hiện) Explicit transactions: Explicit Transaction là transaction do người sử dụng tự định nghĩa. Chúng ta phải định nghĩa bắt đầu và kết thúc transaction, có dạng như sau: BEGIN TRAN INSERT RECORD DELETE RECORD COMMIT TRAN Begin transaction, commit transaction, rollbback transaction được sử dụng để định nghĩa Explicit Transaction. Trong đó BEGIN TRANSACTION là câu lệnh đánh dấu bắt đầu transaction. COMMIT TRANSACTION là câu lệnh kết thúc một transaction thành công. ROLLBACK TRANSACTION quay trở thời điểm bắt đầu của transaction (tức là tất cả những thao tác được thực hiện từ lúc Begin transaction sẽ bị huỷ bỏ) hoặc là một vị trí nào đó được đánh dấu. Implicit transactions: Là chuyển tác ngầm định. Nó không yêu cầu phát biểu chuyển tác Begin transaction. Bản thân nó tự động khởi tạo.Trong SQLServer chuyển tác ngầm định mặc định ở chế độ nghỉ. Muốn sử dụng và tắt chế độ làm việc của Implicit transaction ta thực hiện như sau: - Bật chế độ làm việc: SET implicit_transactions ON - Tắt chế độ làm việc: SET implicit_transactions OFF Auto-Commit transactions: Tất cả các câu lệnh T-SQL hoặc thực hiện thành công, hoặc thất bại hoàn toàn. Nếu câu lệnh thành công thì nó được commited, nếu thất bại nó sẽ được rollback lại. Đây là chế độ mặc định của SQL SERVER 5.3 Các mức cô lập của Transaction 5.3.1 Giới thiệu Dirty Read (Đọc các dữ liệu bẩn) Các dữ liệu bẩn (dirty data) là một thuật ngữ chung chỉ các dữ liệu được ghi bằng một giao tác nhưng còn chưa được lưu giữ lại (committed). Một dirty read dùng để 52
Chương 5. TRANSACTIONS VÀ LOCKS đọc các dữ liệu bẩn. Điều nguy hiểm của việc đọc các dữ liệu bẩn là ở chỗ một giao tác ghi nó có thể bị bỏ dở. Nếu vậy thì các dữ liệu bẩn sẽ bị đẩy ra khỏi cơ sở dữ liệu và mọi người được phép xử sự như là các dữ liệu đó chưa bao giờ tồn tại. Nếu một giao tác khác nào đó đã đọc các dữ liệu bẩn thì giao tác đó có thể lưu giữ hoặc thực hiện một hành động nào đó phản ánh sự hiểu biết của nó về dữ liệu bẩn. Đôi lúc dirty read có ý nghĩa, đôi lúc nó không có ý nghĩa. Lúc khác nó có ý nghĩa rất nhỏ đủ để tạo ý nghĩa về nguy cơ của một dirty read phụ động và như vậy làm ngăn cản: 1. Công việc tốn thời gian của hệ quản trị cơ sở dữ liệu cần để ngăn ngừa dirty read và 2. Mất tính song song gây ra từ sự chờ đợi cho đến khi không có thể có một dirty read. Sau đây là một số ví dụ về những cái có thể xảy ra khi cho phép có các dirty read. Ví dụ 1: Chúng ta hãy xem xét việc chuyển tài khoản.Giả sử rằng các vụ chuyển được thực hiện bằng một chương trình P thực hiện dãy các bước sau đây: 1. Thêm tiền vào tài khoản 2 2. Kiểm tra nếu tài khoản 1 có đủ tiền a) Nếu không có đủ tiền, lấy tiền ra khỏi tài khoản 2 và kết thúc b) Nếu có đủ tiền, trừ số tiền từ tài khoản 1 và kết thúc. Nếu chương trình P được thực hiện một cách có thứ tự thì việc chúng ta thêm tiền tạm thời vào tài khoản 2 sẽ không có ý nghĩa gì. Không ai sẽ nhìn thấy số tiền đó và và nó sẽ bị loại bỏ nếu việc chuyển tiền là không thực hiện được. Tuy nhiên, giả sử rằng có các dirty read. Hãy tưởng tượng có 3 tài khoản A1, A2, A3 với 100$, 200$ và 300$ tương ứng. Giả sử rằng giao tác T1 thực hiện chương trình P để chuyển 150$ từ A1 đến A2. Cùng một thời gian, giao tác T2 chạy chương trình P để chuyển 250$ từ A2 đến A3. Có khả năng có các dãy sự kiện sau: 1. T2 thực hiện bước 1 và thêm 250$ vào A3 và bây giờ A3 có 550$ 2. T1 thực hiện bước 1 và thêm 150$ và A2 và bây giờ A2 có 350$ 3. T2 thực hiện kiểm tra của bước 2 và tìm ra rằng A2 có đủ tiền (350$) để cho phép chuyển 250$ từ A2 sang A3. 53
Chương 5. TRANSACTIONS VÀ LOCKS 4. T1 thực hiện kiểm tra của bước 2 và tìm ra rằng T1 không có đủ tiền (100$) để cho phép chuyển 150$ từ A1 sang A2. 5. T2 thực hiện bước 2b. Nó trừ đi 250$ khỏi A2 và bây giờ A2 có 100$ và kết thúc. 6. T1 thực hiện bước 2a. Nó trừ 150$ khỏi A2, bây giờ A2 có –50$ và kết thúc. Tổng số tiền không thay đổi; trong ba tài khoản vẫn còn 600$. Nhưng bởi vì T2 đọc dữ liệu bẩn ở bước 3 trong 6 bước trên, chúng ta không bảo vệ được việc một tài khoản trở nên âm, đó là mục đích của việc kiểm tra tài khoản thứ nhất để xem tài khoản này có số tiền thích hợp hay không. 5.3.2 Các mức cô lập Dựa vào mức độ “dung thứ” đối với những dữ liệu không chính xác. Isolations được phân loại như sau: - Read Uncommitted - Read Committed - Repeatable read - Serializable Read Uncommitted: Cho phép đọc cả những dữ liệu bẩn. SQL cho phép chúng ta chỉ ra rằng các dirty read là chấp nhận được với một giao tác cho trước. Chúng ta sử dụng lệnh SET TRANSACTION. 1) SET TRANSACTION READ WRITE 2) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED Lệnh trên làm hai việc: 1. Dòng (1) khai báo rằng giao tác có thể ghi dữ liệu 2. Dòng (2) khai báo rằng giao tác có thể chạy với “ isolation level” read-uncomitted. Điều đó có nghĩa là giao tác được cho phép đọc các dữ liệu bẩn. Chú ý rằng, nếu giao tác không phải là read-only (tức là có thể sửa đổi cơ sở dữ liệu) và chúng ta chỉ ra mức cô lập (isolation level) READ UNCOMMITED thì chúng ta cũng phải chỉ ra READ WRITE. Mặc dù, ở chế độ ngầm định các giao tác là read-write. Tuy nhiên SQL có một ngoại lệ đối với trường hợp có cho phép các dirty-read. Trong trường hợp đó, giả thiết ngầm định là giao tác là read-only, bởi vì các giao tác read-write với dirty read gây ra các nguy hiểm đáng kể như chúng ta đã 54
Chương 5. TRANSACTIONS VÀ LOCKS thấy. Nếu chúng ta muốn một giao tác read-write chạy với read-uncommited như là mức cô lập thì chúng ta cần chỉ ra READ WRITE một cách rõ ràng như ở trên. Read Committed SQLSV sử dụng chia sẻ khoá trong khi đọc dữ liệu (ứng dụng này không được phép đọc những dữ liệu mà ứng dụng khác đã thay đổi nhưng chưa được commited)_đây là chế độ mặc định của SQLSV. Cú pháp: SET TRANSACTION ISOLATION LEVEL READ COMMITED ; Repeatable read: Locks sẽ được đặt trên tất cả các truy vấn đang sử dụng, và các transaction khác không thể cập nhật -> không dirty read. Serializable: Xếp hàng thứ tự (giao tác phải chạy trước hoặc sau một giao tác khác đã hoàn thành). Đây mức Isolation hạn chế nhất, dữ liệu phantom không thể xảy ra. Nó ngăn cản người dùng thêm, sửa dữ liệu cho đến khi transaction hoàn thành. 5.4 Locks 5.4.1 Khái niệm Lock là được sử dụng để hạn chế việc truy cập đến dữ liệu trong môi trường đa người dùng. Microsoft SQL Server 2000 sử dụng khoá để đảm bảo tính toàn vẹn và đúng đắn của cơ sở dữ liệu Nếu khoá không được sử dụng, dữ liệu trong CSDL có thể trở thành thiếu logic, và những truy vấn được thực hiện trên dữ liệu có thể trả về kết quả không như mong muốn Khái niệm cơ bản đằng sau việc khoá là vì user cần có những truy cập riêng biệt tới bảng, vì thế server khoá bảng lại cho từng user. 5.4.2 Phân loại Các loại Lock trong SQL SERVER - Pessimistic Lock (Khoá bi quan) - Optimistic Lock (Khoá lạc quan) - Shared Locks (Khoá chia sẻ) - Exclusive Locks (Khoá độc quyền) - Update Locks (Khoá cập nhật) 55
Chương 5. TRANSACTIONS VÀ LOCKS Pessimistic Lock: Khóa được áp dụng ngay khi dữ liệu được yêu cầu sửa chữa và khoá này chỉ giải phóng khi việc sửa chữa hoàn thành. Điều này để chắc chắn rằng không có người dùng khác đồng thời sửa dữ liệu. Optimistic Lock: Khoá này chỉ áp dụng khi việc sửa chữa được hoàn thành và dữ liệu thực sự (actual data) chuẩn bị ghi lên bảng thực sự (actual table) Shared Locks: Đây là loại khoá căn bản nhất, cho phép đọc dữ liệu, không cho phép thay đổi bất kỳ thuộc tính nào của dữ liệu. Exclusive Locks: Lock này ngăn ngừa 2 người sử dụng cùng cập nhật, xoá, đọc, thêm mẩu tin đồng thời. Update Locks: Kết hợp giữa Shared Locks và Exclusive Locks. Với phát biểu UPDATE chỉ ra mẫu tin cần cập nhật bằng lệnh Where, trong khi chưa cần cập nhật bạn ở chế độ Shared Locks, khi cần thực sự thực thi, bạn ở chế độ Exclusive Locks. 56
Chương 5. TRANSACTIONS VÀ LOCKS 5.5 Câu hỏi trắc nghiệm 1. Câu lệnh nào cho phép quay trở thời điểm bắt đầu của transaction hoặc là một vị trí nào đó được đánh dấu. A COMMIT TRANSACTION B ROLLBACK TRANSACTION C SAVE TRANSACTION D BEGIN TRANSACTION 2. Sự thực hiện của một chương trình cho phép truy cập và thay đổi nội dung của cơ sở dữ liệu gọi là gì? A UPDATE B INSERT C Transaction D Không có cái nào ở trên 3. Khi sửa chữa cơ sở dữ liệu phải tuân theo nguyên tắc “tất cả hoặc không gì cả”, thì mỗi transaction được nói tới như là: A Consistent B Durable C Isolated D Atomic 4. Explicit transaction không yêu cầu bạn định nghĩa tường minh bắt đầu và kết thúc transaction. A Đúng B Sai 5. Câu lệnh nào là câu lệnh kết thúc một transaction. A COMMIT TRANSACTION B ROLLBACK TRANSACTION C SAVE TRANSACTION D BEGIN TRANSACTION 6. Loại khóa nào sau đây ngăn ngừa 2 người sử dụng cùng cập nhật, xoá, đọc, thêm mẩu tin đồng thời. A Optimistic Lock B Pessimistic Lock C Shared Lock D Physical Lock 57
Chương 5. TRANSACTIONS VÀ LOCKS 58
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG 6 Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) Cơ sở dữ liệu trong một tổ chức có thể chứa những dữ liệu quan trọng và cần giữ bí mật. Vì thế thực hiện bảo mật cơ sở dữ liệu rất cần thiết đối với một Hệ quản trị cơ sở dữ liệu. Trong phần này chúng ta sẽ bàn về các nguyên tắc bảo mật trên SQL Server 2000 và cách thực hiện nó. 6.1 Giới thiệu về SQL Server Sercurity SQL Server Sercurity sử dụng mô hình 3 mức như sau: 1. Authentication 2. Ability to be user of one or more databases 3. Specific permissions to use against protected database objects Hình 6.1. Bảo mật 3 mức của SQL SERVER - Mức ngoài cùng là mức xác thực người dùng của NT và SQL Server - Mức thứ 2 kiểm tra người dùng có quyền sử dụng một hay nhiều Database. - Mức trong cùng xác định quyền của người dùng thực hiện những câu lệnh SQL trên những đối tượng Database được bảo vệ. 6.2 Quản lý đăng nhập (Login) SQL Server xác thực người dùng ở hai mức: - Login authentication: Xác thực đăng nhập - Permissions validation on user database: Kiểm tra quyền của đối tượng người dùng 6.2.1 Xác thực đăng nhập Ngườì dùng phải có Login account để kết nối với SQL SERVER. SQL Server xác nhận người dùng bằng 3 cơ chế: 59
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) - SQL Server Authentication: Khi sử dụng SQL Server Authentication, người sử dụng phải nhập Account và Password đã được cấp. - Windows NT Authentication: Khi sử dụng Windows NT Authentication, SQL Server sẽ xác minh lại cùng với WinNT để kiểm tra xem có trùng khớp không, nếu đúng thì cho phép đăng nhập. - Mixed Mode Authentication: Người sử dụng có thể đăng nhập sử dụng SQL Server Authentication hoặc Windows NT Authentication. 6.2.2 Kiểm tra quyền (Permission) Với mỗi cơ sở dữ liệu, người dùng được cấp một số quyền trên đó, và họ chỉ được thực hiện các thao tác trong giới hạn quyền của họ. SQL Server thưc hiện những bước sau trong khi kiểm tra quyền: - Khi người dùng thực hiện một thao tác, ví dụ như thực thi câu lệnh T- SQL hoặc lựa chọn công việc trên thanh công cụ của EM, câu lệnh T- SQL sẽ được gửi tới SQL SERVER. - Khi SQL Server nhận câu lệnh T-SQL, nó kiểm tra quyền của người dùng trên những đối tượng liên quan trong câu lệnh T-SQL. o Nếu người dùng không được phép, SQL Server sẽ báo lỗi o Ngược lại, nó thực hiện và trả về kết quả. 6.2.3 Tạo Login Chúng ta có nhiều cách để tạo Login, sử dụng EM hoặc câu lệnh T-SQL SERVER. Tạo Login bằng EM: Sử dụng Create Login Wizard và thực hiện các bước theo chỉ dẫn. Tạo Login bằng T-SQL: Để tạo một login ta có lệnh sau: EXEC sp_addlogin 'phnhung', '123456' Để xoá một login ta có lệnh sau: EXEC sp_droplogin 'Arwen' 60
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) Hình 6.2. Tạo Login 6.3 Quản lý người dùng SQL Server cho phép hai kiểu User Accounts: - System User - Database User 6.3.1 SQL Server Users Một user identifier (ID) xác định một User trong Database. Tất cả quyền và sở hữu các đối tượng trong Database được xác định bằng user accounts. User accounts được xác định cho từng Database. Ví dụ: User account: abc trong Database Book khác user account: abc trong Database Customer. Quan hệ giữa Database User và Login name: User trong Database được xác định bằng user ID, chứ không phải login ID. Bản thân login ID không cấp bất cứ quyền nào cho người dùng truy cập vào các đối tượng trong Database. Ví dụ: sa là login account ánh xạ tới người dùng tới user account đặc biệt có tên là dbo(database owner) trong bất kỳ Database nào. 61
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) 6.3.2 Quản lý Usename và Login name Ví dụ chỉ ra quá trình cấp cho một người dùng Windows 2000 truy cập Database và kết hợp với việc đăng nhập bằng user trong Database. USE master GO sp_grantlogin 'OnlineDOMAIN\Arwen' GO sp_defaultdb @loginame = 'OnlineDOMAIN\Arwen', defdb = 'books' GO USE books GO sp_grantdbaccess 'OnlineDOMAIN\Arwen', 'Arwen' GO sp_revokedbaccess 'OnlineDOMAIN\Arwen', 'Arwen' GO Trong đó: - sp_grantlogin: cho phép người dùng hoặc nhóm account của Windows NT/2000 truy cập vào Database bằng Windows authentication. - sp_defaultdb: thay đổi Database mặc định cho việc đăng nhập. - sp_grantdbaccess: thêm account và cấp quyền truy nhập cho nó. - sp_revokedbaccess: xoá account từ Database. 6.4 Quản lý Role Roles rất quan trọng vì nó là cách chính để cung cấp quyền cho người dùng. Quyền có thể được cấp cho người dùng bằng cách cấp quyền trực tiếp, cách này làm cho người quản trị hệ thống làm việc rất mất thời gian và nhàm chán vì phải cấp chi tiết từng quyền cho từng người dùng. Hoặc quyền có thể được cấp thông qua Role - nó tương tự như khái niệm group trong NT. Chúng ta gán quyền cho từng Role và sau đó người dùng được xếp vào Role đó. Như vậy, công việc của người quản trị hệ thống trở nên dễ dàng hơn nhiều lần. SQL Server có Database Roles và Server Roles. Database Roles được sử dụng để cung cấp các mức khác nhau để truy cập vào cơ sở dữ liệu. Server Roles được sử dụng để cho phép hoặc hạn chế người sử dụng thực hiện các thao tác (operations) trên cơ sở dữ liệu. 6.4.1 Database Roles Có một kiểu Database Role rất đặc biệt, đó là public role. - Nó có trong tất cả các Database. 62
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) - Nó không thể xoá. - Tất cả các người dùng đều thuộc về Public role, bao gồm cả sa account. Sau đây là một số Database role và những quyền tương ứng: - db_owner: Đây là Role cao nhất người dùng có thể có. Role này cho phép người dùng mọi quyền trên CSDL. Người dùng sa nằm trong Role này. - db_securityadmin: Cho phép người dùng quản lý mọi Roles và nhóm người dùng trong role. - db_accessadmin: Cung cấp cho người dùng quyền thêm hoặc xoá những người dùng khác trong CSDL. - db_ddladmin: Cho phép người dùng thực thi mọi nhiệm vụ trên mọi đối tượng trong CSDL: người dùng có thể tạo, sửa, xoá các đối tượng. - db_backupoperator: Cho phép người dùng thực hiện việc backup dữ liệu. - db_datareader: Cho phép người dùng xem dữ liệu trên các bảng của CSDL. - db_denydatawriter: Ngăn cản người dùng sửa bất kỳ dữ liệu nào trên bảng. - db_denydatareader: Ngăn cản người dùng xem bất kỳ dữ liệu nào trên bảng. 6.4.2 Server Roles Server roles được SQL Server 2000 cho phép: - Sysadmin: Có đầy đủ mọi quyền trên SQL Server. - Securityadmin: Cho phép tạo và quản lý việc đăng nhập cho Server. - Serveradmin: Cho phép thiết lập cấu hình của các instance trên SQL Server. - Setupadmin: Có khả năng để quản lý các thủ tục khởi động và các server được liên kết. - Processadmin: Có khả năng để quản lý các tiến trình đang chạy trên SQL Server. - Diskadmin: Có thể quản lý các file trên đĩa - Dbcreator: Cho phép tạo, sửa, và xoá CSDL 63
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) 6.4.3 Thêm thành viên cho Role Để thêm Role mới, chúng ta có thể thực hiện bằng EM hoặc sử dụng thủ tục sp_addrole, và để thêm thành viên cho Role, chungs ta sử dụng thủ tục sp_addrolemember. Ví dụ sau chỉ ra cách tạo Role và thêm các thành viên cho role. sp_addrole 'Teacher' GO sp_addrole 'Student' GO sp_addrole 'StudentTeacher' GO sp_addrolemember 'Teacher', 'NETDOMAIN\Peter' GO sp_addrolemember 'Teacher', 'NETDOMAIN\Cathy' GO sp_addrolemember 'StudentProfessor', 'NETDOMAIN\Diane' GO sp_addrolemember 'Student', 'NETDOMAIN\Mel' GO sp_addrolemember 'Student', 'NETDOMAIN\Jim' GO sp_addrolemember 'Student', 'NETDOMAIN\Lara' GO GRANT SELECT ON StudentGradeView TO Student GO GRANT SELECT, UPDATE ON ProfessorGradeView TO Professor GO 6.5 Đối tượng và quyền trên đối tượng (Database Objects and Object Permission) 6.5.1 Đối tượng Các đối tượng trong cơ sở dữ liệu: Database Object 1. Table 2.Column 3. Row 4. Data type 64
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) 5. Constraint 6. Default 7. Rule 8. Index 9. Views 10. Stored Procedure 11. Trigger 6.5.2 Quyền Object permissions: điều khiển ai có thể truy cập và thao tác với dữ liệu trên bảng (tables) và khung nhìn (views) và ai có thể được chạy các stored procedures. Statement permissions điều khiển users nào có thể xoá và tạo đối tượng trong Database. Object Type Possible Actions Table SELECT, UPDATE, DELETE, INSERT, REFERENCE Column SELECT, UPDATE View SELECT, UPDATE, INSERT, DELETE Stored procedure EXECUTE 6.5.3 Cho phép và huỷ bỏ quyền trên đối tượng User người mà tạo ra các đối tượng trong Database được gọi là object owner. Vì thế những user này phải có quyền để tạo ra những đối tượng trong Database. SQL Server sử dụng lệnh GRANT, REVOKE, và DENY để quản lý quyền: GRANT: Cho phép người dùng thực hiện thao tác như SELECT, UPDATE, INSERT, DELETE hoặc EXCUTED trên các đối tượng. Ví dụ: 65
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom Ví dụ trên thực hiện việc cấp quyền INSERT, UPDATE, DELETE trên bảng Authors cho người dùng Mary, John, Tom. REVOKE: Được sử dụng để xoá quyền của người sử dụng. DENY: Được sử dụng để ngăn cản người sử dụng thực hiện các thao tác trên các đối tượng. 66
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) 6.6 Câu hỏi trắc nghiệm 1. Thủ tục nào theo sau cho phép thêm một account và cấp quyền cho nó? A sp_grantdbacess B sp_grantlogin C sp_grantuser D sp_grantall 2. Thủ tục nào theo sau cho phép thêm một nhóm tới Server Role ? A sp_addsrvrolemember B sp_addrolemember C sp_addserverolemember 3. Role cho phép chúng ta giới hạn hoặc cho phép người dùng thực hiện hàng loạt các thao tác được gọi là roles A Server B Database C User D System 4. Những người dùng tạo ra các đối tượng trong cơ sở dữ liệu gọi là A Database Owner B Table Owner C Account user D Administrator 5. Role nào sau đây không thể bị xóa A Public B Database C Server D System 6. Permissions nào sau đây quyết định người dùng có thể tạo hoặc xóa các đối tượng trong cơ sở dữ liệu? A Statement Permissions B User Permissions C Object Permissions D Database Permissions 7. Role nào sau đây có trong tất cả các cơ sở dữ liệu? 67
Chương 6. BẢO MẬT VÀ QUẢN LÝ NGƯỜI DÙNG (USER AND SERCURITY) A Public B Database C Server 8. Về khái niệm, cái gì theo sau tương đương với group trong NT? A Login Account B Role C User Account D Permission 9. SQL Server sử dụng những lệnh , , để quản lý Permissions? A GRANT, DENY, REVOKE B ALLOW, DENY, REVOKE C ALLOW, DISALLOW, PERMIT 68
Chương 7. T-SQL PROGRAMMING, TRANSACTIONS, SERCURITY – Phần thực hành 7 Chương 7. T-SQL PROGRAMMING, TRANSACTIONS, MANAGING SERCURITY - Phần thực hành Mục đích: - Viết các câu lệnh T-SQL để thực hiện các kiểu transactions - Định nghĩa và sử dụng biến local và grobal - Tạo các logins và users 7.1 Hướng dẫn trực tiếp 7.1.1 Transactions Là nhóm các yêu cầu được thực hiện như một khối (unit). Có 3 loại transactions: - Implicit - Explicit - Auto-commit 7.1.1.1 Implicit Transactions Là chuyển tác ngầm định, bản thân nó tự động khởi tạo. Mặc định, Implicit Transactions ở chế độ OFF. Thực hiện các bước sau: 1. Mở QA 2. Thực hiện câu lệnh sau: Tạo Implicit transaction để thay đổi giá trị của cột Category_code từ ‘D’ thành ‘Do’ trong bảng Category_master(bảng cha). Đồng thời thay đổi luôn giá trị tương ứng của nó trong bảng Flight(bảng con chứa khoá ngoại). Việc thay đổi này bắt buộc phải thực hiện đồng thời trên hai bảng hoặc nếu không được thì không thay đổi gì cả . SET implicit_transactions ON Select Category_code FROM Category_master Select Category FROM Flight Update Category_master Set Category_code='Do' Where Category_code='D' Update Flight Set Category='Do' where Category='D' 69
Chương 7. T-SQL PROGRAMMING, TRANSACTIONS, SERCURITY – Phần thực hành Select Category_code FROM Category_master Select Category FROM Flight COMMIT TRANSACTION SET implicit_transactions OFF Kết quả: Hình 7.1. Tạo Implicit Transactions Chú ý khi sử dụng implicit transaction ta phải sử dụng SET ON và SET OFF. 7.1.1.2 Explicit Transactions Là transaction tường minh, chúng ta phải định nghĩa bắt đầu và kết thúc transaction. Explicit Transactions còn được gọi là User-defined transactions. Thực hiện transaction để tăng giá vé máy bay (cột fare trong bảng Flight_details) lên 200 cho hạng vé ‘Ex’ và hãng bay ‘IC04’, xem thông tin trước và sau khi cập nhật. Câu lệnh và kết quả thực hiện như sau: BEGIN TRAN Fare_Increase Select Aircraft_code, Fare FROM Flight_details 70
Chương 7. T-SQL PROGRAMMING, TRANSACTIONS, SERCURITY – Phần thực hành Where Aircraft_code='IC04' AND Class_code='Ex' Update Flight_details Set Fare=Fare+200 Where Aircraft_code='IC04' AND Class_code='Ex' Select Aircraft_code, Fare FROM Flight_details Where Aircraft_code='IC04' AND Class_code='Ex' COMMIT TRAN Hình 7.2. Tạo Implicit Transactions Sử dụng Rollback Transaction Thực hiện Transaction sau: BEGIN TRAN Use_Rollback Select Aircraft_code, Fare FROM Flight_details Where Aircraft_code='IC04' AND Class_code='Ex' Update Flight_details Set Fare=Fare+200 Where Aircraft_code='IC04' AND Class_code='Ex' 71
Chương 7. T-SQL PROGRAMMING, TRANSACTIONS, SERCURITY – Phần thực hành Select Aircraft_code, Fare FROM Flight_details Where Aircraft_code='IC04' AND Class_code='Ex' if (Select Fare FROM Flight_details Where Aircraft_code='IC04' AND Class_code='Ex')>7000 ROLLBACK TRAN Select Aircraft_code, Fare FROM Flight_details Where Aircraft_code='IC04' AND Class_code='Ex' COMMIT TRAN Kết quả thực hiện: Hình 7.3. Sử dụng Rollback Transaction 7.1.1.3 Autocommit Transaction Thực hiện những câu lệnh sau: create table Test(col1 int primary key, col2 char(3)) insert into Test values(1,'aaa') insert into Test values(2,'bbb') Transaction trên là Autocommit transaction, vì không có BEGIN TRANSACTION. Autocommit transaction sẽ tự động Commit nếu không xảy ra lỗi, 72
Chương 7. T-SQL PROGRAMMING, TRANSACTIONS, SERCURITY – Phần thực hành ngược lại nó sẽ Rollback. Vì thế, mặc dù lỗi xảy ra ở dòng 1 nhưng kết quả của những câu lệnh trước cũng không được thực hiện. 7.1.2 Biến địa phương (local) và biến toàn cục(Global) Biến là đối tượng có thể nắm giá trị dữ liệu. Chúng ta có thể sử dụng biến địa phương để biểu diễn dữ liệu trong câu lệnh SQL SERVER. Ví dụ: declare @name_pas char(50) set @name_pas='Pam Smith' Select * from Passenger where [Name]=@name_pas Kết quả: Hình 7.4. Sử dụng biến SQL Server cũng hỗ trợ một số biến toàn cục hệ thống (system grobal), nắm giữ những thông tin hữu ích đối với những người sử dụng cơ sở dữ liệu. Trước những biến này phải có ký hiệu @@. Ví dụ: 73
Chương 7. T-SQL PROGRAMMING, TRANSACTIONS, SERCURITY – Phần thực hành Hình 7.5 7.1.3 SQL Server Sercurity 7.1.3.1 Tạo logins sử dụng Enterprise Manager. Thực hiện các bước sau: 1. Kích Wizard trên Tool menu 2. Trong hộp thoại Select Wizard, mở rộng Database 3. Kích đúp vào Create Login Wizard 4. Hoàn thành các bước tiếp theo. Ngoài ra, chúng ta cũng có thể sử dụng sp_addlogin để tạo một login mới. Ví dụ: EXEC sp_addlogin ‘phnhung’, ‘123’, ‘Flight Information’ 7.2 Bài tập 1. Sử dụng một biến để lưu tình trạng đặt vé máy bay. Thực hiện truy vấn để đưa ra thông tin về mã số khách hàng (PNR_No), Số vé (Ticket_No), Tên khách hàng (Name) có tình trạng vé máy bay (Status) trong bảng Reservation bằng giá trị của biến nhập vào. 2. Hiển thị tất cả các tên khách hàng trong kiểu chữ in hoa. 3. Thực hiện các câu lệnh sau để biết kết quả: SELECT DATENAME(DW, GETDATE()) SELECT DATENAME(DY, GETDATE()) SELECT DATENAME(YYYY, GETDATE()) SELECT DATENAME(QUARTER, GETDATE()) SELECT DATENAME(HH, GETDATE()) SELECT DATEADD(DAY,25,GETDATE()) 74
Chương 7. T-SQL PROGRAMMING, TRANSACTIONS, SERCURITY – Phần thực hành 4. Thực hiện một số câu lệnh sau và cho biết kết quả: SELECT @@Language SELECT @@Servicename SELECT @@Servername SELECT @@Rowcount SELECT @@Connections 5. Thêm một tên login mới sử dụng thủ tục hệ thống sp_addlogin. 75
Chương 8. T-SQL VÀ SQL NÂNG CAO 8 Chương 8. T-SQL VÀ SQL NÂNG CAO 8.1 Giới thiệu sơ lược về T- SQL (Transact -SQL) Phần này chúng ta đã được tìm hiểu trong phần trước khi học về ngôn ngữ SQL, nên ở đây tôi chỉ giới thiệu một số ví dụ đơn giản. Transact-SQL là ngôn ngữ SQL mở rộng dựa trên SQL chuẩn của ISO (International Organization for Standardization) và ANSI (American National Standards Institute) được sử dụng trong SQL Server khác với P-SQL (Procedural- SQL) dùng trong Oracle. Trong bài này chúng ta sẽ tìm hiểu sơ qua về 3 nhóm lệnh T-SQL được thực hiện trong SQL Server. 8.1.1 Data Definition Language (DDL) Ðây là những lệnh dùng để quản lý các thuộc tính của một cơ sở dữ liệu như định nghĩa các hàng hoặc cột của một bảng, hay vị trí data file của một cơ sở dữ liệu thường có dạng: CREATE object_Name ALTER object_Name DROP object_Name Trong đó object_Name có thể là một table, view, stored procedure, indexes Chúng ta xem xét một số ví dụ sau: Lệnh Create sau sẽ tạo ra một bảng tên STUDENT với 3 cột StID, StName, StAddress. USE Student_Management CREATE TABLE Student( StID int NOT NULL PRIMARY KEY, StName varchar(40) NOT NULL, StAddress varchar(40) ) Lệnh Alter sau đây cho phép ta thay đổi định nghĩa của một bảng như thêm(hay bớt) một cột hay một Constraint Trong ví dụ này ta sẽ thêm cột StClass vào table Student. USE Student_Management ALTER TABLE Student ADD StClass varchar(20) Lệnh Drop sau đây sẽ hoàn toàn xóa bảng khỏi cơ sở dữ liệu USE Northwind 76
Chương 8. T-SQL VÀ SQL NÂNG CAO DROP TABLE Student 8.1.2 Data Control Language (DCL): Ðây là những lệnh quản lý các quyền truy cập lên từng object (table, view, stored procedure ). Thường có dạng sau: • GRANT • REVOKE • DENY Ví dụ: Lệnh sau sẽ cho phép user trong Public Role được quyền Select đối với table Student trong cơ sở dữ liệu Student_Management (Role là một khái niệm giống như Windows Group sẽ được bàn kỹ trong phần Security). USE Student_Management GRANT SELECT ON Customers TO PUBLIC Lệnh sau sẽ từ chối quyền Select đối với table Student trong cơ sở dữ liệu Student_Management của các User trong Public Role. USE Student_Management DENY SELECT ON Student TO PUBLIC Lệnh sau sẽ xóa bỏ tác dụng của các quyền được cho phép hay từ chối trước đó. USE Student_Management REVOKE SELECT ON Student TO PUBLIC 8.2 Data Manipulation Language (DML): Ðây là những lệnh phổ biến dùng để xử lý data: Update, Insert, Delete. Sau đây là một số ví dụ: Insert USE Student_Management INSERT INTO Student VALUES ( ‘TL01’, 'Phạm Đình Thuận', ‘Hà nội’, ‘43th’) Update USE Student_Management UPDATE Student SET StClass = '45th' WHERE STId = ‘TL01’ 77
Chương 8. T-SQL VÀ SQL NÂNG CAO Delete USE USE Student_Management DELETE FROM Student WHERE STId = ‘TL01’ Ðể chạy các câu lệnh thí dụ ở trên bạn cần sử dụng Query Analyser để Soạn thảo câu lệnh và thực thi. Hình 8.1. Cửa sổ soạn thảo và thực hiện câu lệnh SQL 8.3 Các câu lệnh truy vấn dữ liệu Các câu lệnh SQL đơn giản có cú pháp và cách thực hiện tương tự như đã giới thiệu. Sau đây là một số câu lệnh bổ sung và nâng cao. 8.3.1 Thực hiện Join để kết nối các bảng Ta có thể thực hiện lấy dữ liệu từ hai bảng thông qua từ khóa JOIN. 8.3.1.1 INNER JOIN: Cú pháp: 78
Chương 8. T-SQL VÀ SQL NÂNG CAO SELECT field1, field2, field3 FROM table1 INNER JOIN table2 ON table1.keyfield=table2.foreign_keyfield Ví dụ: Giả sử có hai bảng: KHACHHANG: MaKH TenKH 01 Hoàng Thanh Vân 02 Lê Thị Nhàn 03 Phan Thanh Hòa 04 Phạm Hồng Thanh DONHANG: MaSP TenSP MaKH H102 Máy in 01 H106 Bàn 03 H301 Ghế 03 Yêu cầu: Đưa ra tên khách hàng và tên sản phẩm khách hàng đó mua. SELECT KHACHHANG.TenKH, DONHANG.TenSP FROM KHACHHANG INNER JOIN DONHANG ON KHACHHANG.MaKH=DONHANG.MaKH Kết quả: TenKH TenSP Hoàng Thanh Vân Máy in Phan Thanh Hòa Bàn Phan Thanh Hòa Ghế INNER JOIN trả về tất cả các dòng từ hai bảng thỏa mãn điều kiện. Nếu những dòng dữ liệu có bên table1 mà không có trong table2 thì sẽ không được hiển thị. 8.3.1.2 LEFT OUTER JOIN Cú pháp: SELECT field1, field2, field3 FROM table1 LEFT OUTER JOIN table2 ON table1.keyfield = table2.foreign_keyfield 79
Chương 8. T-SQL VÀ SQL NÂNG CAO Ví dụ: SELECT KHACHHANG.TenKH, DONHANG.TenSP FROM KHACHHANG LEFT OUTER JOIN DONHANG ON KHACHHANG.MaKH=DONHANG.MaKH Kết quả: TenKH TenSP Hoàng Thanh Vân Máy in Lê Thị Nhàn Phan Thanh Hòa Bàn Phan Thanh Hòa Ghế Phạm Hồng Thanh LEFT OUTER JOIN trả về tất cả các dòng có ở bảng thứ nhất, mặc dù ở bảng thứ hai không thỏa mãn phép toán. Nếu dữ liệu có ở bảng thứ nhất mà không có ở bảng thứ hai thì dữ liệu vẫn hiển thị. 8.3.1.3 RIGHT OUTER JOIN Cú pháp SELECT field1, field2, field3 FROM table1 RIGHT OUTER JOIN table2 ON table1.keyfield = table2.foreign_keyfield Ví dụ SELECT KHACHHANG.TenKH, DONHANG.TenSP FROM KHACHHANG RIGHT OUTER JOIN DONHANG ON KHACHHANG.MaKH=DONHANG.MaKH Kết quả: TenKH TenSP Hoàng Thanh Vân Máy in Phan Thanh Hòa Bàn Phan Thanh Hòa Ghế RIGHT OUTER JOIN trả về tất cả các dòng có ở bảng 2, mặc dù bảng 1 không thỏa mãn phép toán. Nếu dữ liệu có ở bảng 2 mà không có ở bảng 1 thì vẫn được hiển thị. 80
Chương 8. T-SQL VÀ SQL NÂNG CAO 8.3.1.4 FULL OUTER JOIN Dùng Full Outer Join để đưa dữ liệu từ 2 hay nhiều bảng trong đó tất cả cột bên bảng thứ nhất và thứ hai đều được chọn. Các giá trị bên hai bảng trùng nhau thì chỉ lấy một lần. Ví dụ: USE Pubs SELECT a.Au_fname, a.Au_lname, p.Pub_name FROM Authors a FULL OUTER JOIN Publishers p ON a.City = p.City ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC 8.3.1.5 CROSS JOIN Dùng Cross Join ghép dữ liệu từ hai bảng trong đó số hàng thu được bằng với số hàng của bảng thứ nhất nhân với số hàng của bảng thứ hai. Ví dụ: USE pubs SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers WHERE authors.city = publishers.city ORDER BY au_lname DESC 8.3.2 Mệnh đề Top n: Nếu ta muốn select n hàng đầu tiên mà thôi ta có thể dùng từ khoá Top. Nếu có thêm ORDER BY thì kết quả sẽ được order trước sau đó mới select. Chúng ta cũng có thể select số hàng dựa trên phần trăm bằng cách thêm từ khoá Percent. Ví dụ sau sẽ select 10 hàng đầu tiên theo thứ tự: SELECT DISTINCT TOP 10 ShipCity, ShipRegion FROM Orders ORDER BY ShipCity 8.3.3 Mệnh đề INTO INTO Clause cho phép ta lấy dữ liệu từ một hay nhiều bảng, sau đó kết quả sẽ được insert vào một bảng mới. Bảng mới này được tạo ra do kết quả của câu lệnh SELECT INTO. Ví dụ: SELECT FirstName, LastName INTO EmployeeNames FROM Employers 81
Chương 8. T-SQL VÀ SQL NÂNG CAO Câu lệnh trên sẽ tạo ra một bảng mới có tên là EmployeeNames với 2 cột là FirstName và LastName. Sau đó kết quả select được từ table Employers sẽ được insert vào bảng mới này. Nếu table EmployeeNames tồn tại SQL Server sẽ báo lỗi. Câu lệnh này thường hay được sử dụng để lấy một lượng dữ liệu lớn từ nhiều bảng khác nhau vào một bảng mới (thường dùng cho mục đích tạm thời (temporary table)) mà khỏi phải thực thi câu lệnh Insert nhiều lần. Một cách khác cũng select data từ một hay nhiều bảng và insert vào một bảng khác là dùng "Insert Into Select ". Nhưng câu lệnh này không tạo ra một bảng mới. Nghĩa là table đó phải tồn tại trước. Ví dụ: INSERT INTO EmployeeNames SELECT FirstName, LastName FROM Employers Chú ý là không có chữ "Value" trong câu Insert này. 8.3.4 Từ khoá UNION(Hợp) Uninon có nhiệm vụ ghép nối kết quả của 2 hay nhiều truy vấn lại thành một kết quả. Ví dụ: Giả sử có table1(ColumnA varchar(10), ColumnB int) và table2(ColumnC varchar(10), ColumnD int). Ta muốn select data từ table1 và ghép với dữ liệu từ table2 để tạo thành một kết quả duy nhất ta làm như sau: SELECT * FROM Table1 UNION ALL SELECT * FROM Table2 Nếu không có keyword ALL thì những hàng giống nhau từ 2 table sẽ chỉ xuất hiện một lần trong kết quả. Còn khi dùng ALL thì các hàng trong 2 table đều có trong kết quả bất chấp việc lập lại. Khi Dùng Union phải chú ý hai chuyện: số cột select ở 2 queries phải bằng nhau và data type của các cột tương ứng phải compatible (tương thích). 8.3.5 Từ khoá CUBE và ROLL UP Được sử dụng để tổng kết dữ liệu ở mức cao hơn của GROUP BY. 82
Chương 8. T-SQL VÀ SQL NÂNG CAO 8.3.5.1 CUBE Select + Group By Row information + WITHCUBE Super Aggregate Row Ví dụ: use Pubs SELECT Stor_Id, Payterms, SUM(Qty) AS Total_Quantity FROM Sales GROUP BY Stor_id, Payterms WITH CUBE Kết quả: 83
Chương 8. T-SQL VÀ SQL NÂNG CAO 8.3.5.2 ROLLUP Select + Group By + Row information WITH ROLLUP Tương tự như Cube, tuy nhiên Rollup có thể tổng kết dữ liệu đến mức cuối cùng. Ví dụ: SELECT Stor_Id, Payterms, SUM(Qty) AS Total_Quantity FROM Sales GROUP BY Stor_id, Payterms WITH ROLLUP Kết quả: Khi dùng With Rollup thay Cube,84 dòng này được thêm vào
Chương 8. T-SQL VÀ SQL NÂNG CAO 8.3.6 Mệnh đề COMPUTE và COMPUTE BY Mệnh đề COMPUTE đưa ra giá trị tổng hợp (thông qua các hàm nhóm) trong một hàng mới. Kết quả chi tiết và dòng dữ liệu tổng hợp được nhìn thấy trên cùng 1 màn hình kết quả. Cú pháp: COMPUTE , Ví dụ: Hình 8.2. Thực hiện cú pháp COMPUTER Nếu thay mệnh đề COMPUTE bằng COMPUTE BY ta sẽ nhận được kết quả sau: 85
Chương 8. T-SQL VÀ SQL NÂNG CAO Hình 8.3. Thực hiện cú pháp COMPUTER BY 86
Chương 8. T-SQL VÀ SQL NÂNG CAO 8.4 Câu hỏi trắc nghiệm 1. Kết quả của truy vấn con có thể là bao nhiêu dòng mà câu lệnh không trả về lỗi? A Chỉ một B Chỉ một, trừ khi đằng trước nó có toán tử ANY, ALL, EXISTS hoặc IN. C Không giới hạn D Không giới hạn, trừ khi đằng trước nó có toán tử ANY, ALL, EXISTS hoặc IN. 2. Truy vấn con liên kết (correlated subqueries) không thể đứng độc lập. A Đúng B Sai 3. Những hàm nào sau đây không sử dụng được với kiểu dữ liệu Character? A AVG() B SUM() C MIN() D MAX() 4. Trong câu lệnh SELECT, nếu chứa các từ khoá WHERE, GROUP BY, HAVING thì chúng phải đứng theo thứ tự nào? A HAVING, GROUP BY, WHERE B WHERE, GROUP BY, HAVING C WHERE, HAVING, GROUP BY D GROUP BY, WHERE, HAVING 5. Kiểm tra truy vấn sau: SELECT batch_id, subject_id, AVG(Marks) FROM batchperformance GROUP BY batch_id, subject_id WITH ROLLUP Kết quả câu lệnh trên là gì? A Điểm trung bình của từng khoá học (Batch) trong từng môn học(Subject). B Điểm trung bình của tất cả các khoá học trong từng môn học. C Điểm trung bình của tất cả các sinh viên của một khoá học trong tất cả môn học. D Điểm trung bình của từng khoá học tính theo tất cả các môn học. 6. Mặc định, phép toán UNION giúp ta nhân đôi số lượng của tập kết quả? A Đúng B Sai 87
Chương 9. T-SQL và SQL nâng cao -Phần thực hành 9 Chương 9. T-SQL VÀ SQL NÂNG CAO Phần thực hành Thực hiện những công việc sau đây bằng Query Analyzer: 1. Hiện tất cả các giá trị khác nhau của PNR_no từ bảng Passenger 2. Lấy ra 4 hàng đầu tiên từ bảng Meal 3. Liệt kê các bản ghi trong bảng Flight có mã Aircraft_code là ‘BA01’ 4. Liệt kê Name, PP No, Meal Pref cho các hành khách có PNR_no là 1 hoặc 2 từ bảng Passenger 5. Hiển thị tất cả các tên hành khách bắt đầu bằng chữ cái ‘A’ 6. Hiển thị chi tiết tất cả chuyến bay từ thành phố có mã ‘NY’, sắp xếp theo trường source 7. Hiển thị tên của các hành khách nam thêm vào trước tên ‘ Mr’ 8. Hiển thị các thông tin chi tiết về chuyến bay như aircraft code, regular fare, discounted fare cho hạng nhất (FC). Discount (giảm giá) bằng 25% giá thông thường. Tiêu đề các cột là Aircraft, Regular First Class fare, Discounted First Class fare. 9. Hiển thị và sắp xếp chi tiết về các chuyến bay tới thành phố có mã là ‘Lon’. Các chuyến bay có có thời gian bay ít nhất được hiển thị đầu tiên. 10. Hiển thị các món ăn không phải là ăn chay (non-vegetarian) trên các chuyến bay 11. Hiển thị status_code và description trong bảng Status_master với điều kiện chữ cái cuối cùng trong trường description khác ‘d’ 12. Hiển thị aircraft_code của đường bay vào Chủ nhật (Sunday) và thứ Tư (Wednesday). 13. Hiển thị tên nước mà chuyến bay Fly Safe Airways đã đến, lưu ý là chỉ hiển thị những giá trị khác nhau. 14. Hiển thị số lượng bản ghi được hiển thị bởi truy vấn trước. 15. Hiển thị tên của dịch vụ SQL Server đang chạy trên máy tính. 16. Hiển thị tên của những món ăn (meal) được phục vụ trên British Airways (Sử dụng truy vấn con). 17. Hiển thị tên những hãng bay (airlines) có những chuyến bay (flights) nội địa xuất phát từ Mumbai (Sử dụng truy vấn con). 18. Hiển thị tuổi trung bình của những hành khách là nữ. 88
Chương 9. T-SQL và SQL nâng cao -Phần thực hành 19. Tìm tổng số hành khách được Fly Safe Airways phục vụ. 20. Tính thời gian bay nhỏ nhất, lớn nhất, và trung bình của những chuyến bay đến thành phố có mã số là NY. 21. Hiển thị số vé (tickets) được đặt của từng PNR_no. 22. Hiển thị số lượng các chuyến bay mỗi tuần của từng Aircraft. 23. Hiển thị những aircraft có tổng số ghế phục vụ >500 ghế. 24. Hiển thị những PNR number và tuổi của người già nhất. Lưu ý, chỉ hiển thị những bản ghi có tuổi người già nhất >35. 25. Hiển thị số lượng những lựa chọn bữa ăn có Non-vegetarian trên mỗi hãng bay (airline). 26. Hiển thị số lượng của những hành khách có tuổi lớn hơn 40 của từng PNR number. Phải đảm bảo rằng tất cả các PNR number đều được hiển thị. 27. Hiển thị Airline code(mã hãng bay), destition city code (mã số của thành phố đích) và số những chuyến bay của hãng đó bay tới. Kết quả bao gồm cả tổng số những chuyến bay của từng hãng và tổng số của tất cả các chuyến bay trong cơ sở dữ liệu. 89
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 10 Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 10.1 Ràng buộc dữ liệu 10.1.1 Giới thiệu Như chúng ta đã biết, thực hiện những ràng buộc dữ liệu giúp tất cả các giá trị của dữ liệu được lưu trữ đúng đắn. Tất cả dữ liệu được thêm vào cơ sở dữ liệu đều phải thảo mãn các ràng buộc. Sau đây là một số loại ràng buộc dữ liệu: Ràng buộc tham chiếu Ràng buộc thực thể Referential integrity Entity integrity Ràng buộc dữ liệu Ràng buộc miền NSD tự định nghĩa Domain integrity User-defined integrity Hình 10.1. Các loại ràng buộc dữ liệu 10.1.2 Ràng buộc thực thể Xác định một dòng dữ liệu là duy nhất trong một bảng. Còn được biết đến như ràng buộc dòng dữ liệu (Row Integrity). SQL Server có một số công cụ thực hiện ràng buộc thực thể như sau: - PRIMARY KEY constraint - UNIQUE constraint - IDENTITY property 90
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 10.1.3 Ràng buộc miền dữ liệu Là tập hợp những dữ liệu được phép nhập vào một cột trong bảng. Công cụ thực hiện: - DEFAULT definition - FOREIGN KEY constraint - CHECK constraint - NOT NULL property - Rules 10.1.4 Ràng buộc tham chiếu Là ràng buộc dữ liệu giữa khoá chính của một bảng với khoá ngoại của một bảng khác. Công cụ thực hiện: - FOREIGN KEY constraint - CHECK constraint 10.1.5 Ràng buộc NSD tự định nghĩa Cho phép người quản trị thêm vào những ràng buộc để đảm bảo sự đúng đắn của dữ liệu. Công cụ thực hiện: - Tất cả SQL Server constraints - Stored Procedures - Triggers 10.2 Thực hiện các ràng buộc bằng T-SQL - Được thiết đặt trên một hoặc một tập hợp các cột của bảng. - Nhằm thiết đặt những giới hạn cho việc nhập giá trị cho cột dữ liệu. - Có thể được định nghĩa ngay khi tạo bảng hoặc sửa cấu trúc bảng. 10.2.1 PRIMARY KEY Constraint Thiết đặt một hoặc tập hợp các cột làm khoá chính của bảng. Cú pháp: CREATE TABLE Table_name ( PRIMARY KEY) Ví dụ: 91
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ CREATE TABLE Reservation_copy ( PNR_no int PRIMARY KEY ) 10.2.2 UNIQUE Constraint Quy định cột này phải có giá trị khác nhau trên mỗi dòng Cú pháp: CREATE TABLE Table_name ( UNIQUE ) Ví dụ: CREATE TABLE passenger_copy ( [PP no] VARCHAR(20) UNIQUE) 10.2.3 IDENTITY Property Quy định giá trị của một cột nào đó trong bảng là tự động - seed_value: giá trị ban đầu - increment_value: giá trị tăng Cú pháp: CREATE TABLE Table_name (Column_name Data_Type IDENTITY [( , increment_value>)]) Ví dụ: CREATE TABLE Reservation_Copy (ticket_no INT IDENTITY(1,1)) 10.2.4 DEFAULT Definition Thiết đặt giá trị mặc định cho một cột nào đó. Nếu người sử dụng không nhập giá trị cho cột này thì nó sẽ nhận giá trị mặc định. Cú pháp: CREATE TABLE Table_name (Column_name Data_Type DEFAULT default_value) Ví dụ: CREATE TABLE employee (employee_cd char(4), employee_nm varchar(50), grade char(2), hra varchar(10) default ‘N.A.’) 92
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 10.2.5 FOREIGN Key Constraint Chỉ ra một cột làm khoá ngoại của bảng (nhằm liên kết dữ liệu trong hai bảng) Cú pháp: CREATE TABLE Table_name Column_name Data_Type, FOREIGN KEY (Column_name) REFERENCES Primarykey_Tablename) Ví dụ: CREATE TABLE Passenger (PNR_no int, ticket_no int, name varchar(15), FOREIGN KEY (PNR_no) REFERENCES Reservation) 10.2.6 CHECK Constraint Giới hạn dữ liệu được lưu trữ trong cột. Cú pháp: CREATE TABLE Table_name (Column_name Data_Type CHECK (value1, value2, ), ) Ví dụ: CREATE TABLE Reservation ( , class_code char(3) CHECK(‘EX’, ‘FC’, ‘E’), ) 10.2.7 NOT NULL Constraint Nếu một trường nào được quy định là NOT NULL, tức là không rỗng thì người sử dụng bắt buộc phải nhập dữ liệu cho trường này. Cú pháp: CREATE TABLE Table_name (Column_name Data_Type NOT NULL, ) Ví dụ: CREATE TABLE Passenger ( , name varchar(15) NOT NULL, ) 93
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 10.2.8 Rules Tương tự như CHECK constraints, nhưng Rules được tạo như một đối tượng độc lập, sau đó mới thiết đặt cho cột dữ liệu. Cú pháp: CREATE RULE rule_name AS condition_expression : SP_BINDRULE rule_name, table_name.column_name Ví dụ: CREATE RULE check_PNR AS @pnr BETWEEN 1 AND 500 : SP_BINDRULE check_PNR, Reservation.PNR_no 10.3 Indexes 10.3.1 Giới thiệu Khái niệm về index trong cơ sở dữ liệu tương tự như phần index của một cuốn sách. Khi cần tìm kiếm thông tin trên cuốn sách, ta không cần phải lật tất cả các trang trong đó, mà chỉ cần vào phần Index ở cuối quyển và dò xem thông tin mà ta cần nằm ở những trang nào.Vì lý do đó, tìm kiếm thông tin thông qua index sẽ nhanh hơn nhiều lần. Trong cơ sở dữ liệu cũng vậy, hỗ trợ index cho phép người dùng tìm dữ liệu mà không cần quét toàn bộ bảng. Index được dùng để tìm ra giá trị duy nhất. Mục đích của index để xác định dòng nào đang chứa dữ liệu cần tìm. Index được sử dụng đúng cách có thể tăng hiệu quả thực hiện trên CSDL bằng cách giảm thời gian truy cập. Index có thể được tạo bởi 1 hoặc nhiều trường. SQL Server tự động tạo chỉ số cho những trường được xác định là trường khoá hoặc ràng buộc duy nhất (UNIQUE) Tuy nhiên, những Tables có indexes yêu cầu nhiều vùng trống trên đĩa trong CSDL. Những lệnh cần thực hiện dữ liệu yêu cầu nhiều thời gian hơn bởi vì cần phải cập nhật index. Vì thế, Indexes thực sự là con dao 2 luỡi, nếu không sử dụng đúng cách, nó sẽ làm giảm tốc độ của hệ thống. 94
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 10.3.2 Lời khuyên khi sử dụng indexes Chúng ta chỉ nên thiết đặt index trên những cột: - Được sử dụng thường xuyên cho việc tìm kiếm. - Khi cột được dùng để sắp xếp dữ liệu và không nên áp dụng trong những trường hợp: - Khi cột chỉ chứa đựng vài giá trị khác nhau. - Khi bảng chỉ có vài dòng . 10.3.3 Tạo Indexes Lệnh tạo chỉ số trên bảng: CREATE INDEX Chỉ có những người dùng làm chủ bảng mới có quyền tạo chỉ số cho bảng. Cú pháp: CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name[, column_name] ) [WITH [PAD_INDEX] [[,]FILLFACTOR=x] [[,]DROP_EXISTING] ] Trong đó: Fill factor được tạo ngay khi tạo index. Khi index được tạo, bảng dữ liệu (table data) được lưu trữ trong trang dữ liệu (data page) theo thứ tự của giá trị trong cột được chỉ số.Khi bản ghi mới được thêm vào bảng hoặc một giá trị trong bảng được thay đổi, SQL Server phải tổ chức vùng trống cho bản ghi mới và cập nhật lại thứ tự sắp xếp của dữ liệu. Trong khi thêm bản ghi mới vào trang chỉ số đã đầy (full index page), SQL Server sẽ di chuyển gần đúng nửa số bản ghi của bảng sang trang mới để tạo ra vùng trống cho việc thêm bản ghi, quá trình này gọi là phân trang (page splits). Khi ở đó sẽ không có sự thay đổi dữ liệu, xác định giá trị đó là 100 thì trang sẽ đầy và sẽ chiếm một phần rất nhỏ bộ nhớ. Khi ở đó có sự thay đổi dữ liệu thường xuyên dữ liệu trong bảng, xác định giá trị của Fill Factor thấp để có nhiều hơn vùng trống cho trang dữ liệu. 95
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 10.3.4 Các kiểu Indexes Có 2 kiểu Indexes: - Clustered index: xác định thứ tự lưu trữ vật lý của dữ liệu trong bảng - Non-clustered index: xác định sắp xếp logic của dữ liệu. 10.3.4.1 Clustered index Một Table chỉ có thể có 1 clustered index. Index có thể kết hợp nhiều cột (multiple columns). Ví dụ: CREATE CLUSTERED INDEX CLINDX_titleid ON roysched (title_id) 10.3.4.2 Non-clustered index - Non clustered xác định thứ tự logic của dữ liệu. - Dữ liệu được lưu ở một vùng, index được lưu ở vùng khác, có con trỏ trỏ đến vùng lưu trữ dữ liệu. - Một Table có thể có nhiều non-clustered indexes, lớn nhất 249. - Hướng dẫn tạo Non-clustered Indexes: - Chỉ nên thêm chỉ số khi nó thực sự cần thiết. - Chỉ nên chỉ số những cột được truy cập thường xuyên. - Mặc định, câu lệnh CREATE INDEX tạo ra non-clustered index. - Sử dụng non-clustered index cho những cột có nhiều giá trị khác nhau, ví dụ như kết hợp last name và first name (nếu như clustered index đã được sử dụng cho nhưng cột khác). Ví dụ: CREATE NONCLUSTERED INDEX NCLINDX_ordnum ON sales (ord_num) 10.3.5 Tính chất của Indexes Clustered and non-clustered indexes có thể được tạo như Unique hoặc Composite. Unique indexes: không cho phép giá trị trùng nhau trong cột index. Composite indexes: cho phép hai hoặc nhiều cột kết hợp để tạo ra index. 96
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ Hướng dẫn tạo Unique Indexes: - Toàn vẹn thực thể được đảm bảo bằng unique indexes vì giá trị duy nhất tồn tại trong mỗi dòng. - A NULL value trong key column được coi như là unique value. - Unique index không thể được tạo trên cột chứa giá trị trùng nhau. Giá trị trùng nhau phải được xóa trước khi unique index được tạo. 10.3.6 Hiển thị Indexes Sau khi tạo ra indexes, chúng ta có lẽ cần biết thông tin về về indexes. Chúng ta có thể muốn nhìn thấy những indexes được tạo trên bảng và những cột tạo nên indexes trong bảng. sp_helpindex là system stored procedure đưa ra những thông tin về indexes trên bảng. Cú pháp: sp_helpindex 10.3.7 Cách sử dụng Indexes SQL Server sử dụng Query Optimizer để lựa chọn cách nào là tốt nhất để thực hiện truy vấn, bao gồm indexes nào được sử dụng. Tuy nhiên, chúng ta có thể yêu cầu truy vấn thực hiện trên index nào thông qua: Cú pháp: (INDEX=index_name) Ví dụ: SELECT * FROM sales(INDEX =nclindx_ordnum) WHERE ord_num = ‘P3087a’ 10.3.8 Xóa Indexes Indexes không cần thiết nữa có thể được xóa khỏi CSDL để giải phóng vùng nhớ bằng cách sử dụng lệnh DROP INDEX. Cú pháp: DROP INDEX table_name.index_name Ví dụ: DROP INDEX sales.NCLINDX_ordnum 97
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ Chú ý:Câu lệnh DROP INDEX không được áp dụng cho những indexes được tạo bằng PRIMARY KEY hoặc UNIQUE constraints và index trên system table. 10.3.9 Full-text Searches Full-text indexes được sử dụng trên SQL Server để thực hiện full-text searches. Indexes có thể xây dựng trên unstructured text để cho phép tìm kiếm text trên những mục xác định. Chức năng này được cung cấp bằng Microsoft Search Service cho phép thực hiện những tìm kiếm phức tạp sử dụng điều kiện tìm kiếm bằng ngôn ngữ (linguistic search criteria). Linguistic searches cho phép tìm kiếm từ hoặc cụm từ, những từ mục tiêu được chỉ ra liên quan đến một từ khác, và những dạng khác nhau của từ. 10.3.10 Full-text Catalogs Tất cả các full-text indexes được lưu trữ trong full-text catalogs. Full-text catalog là thư mục chỉ có thể được xem bằng Windows và Search Service. Mặc định, tất cả các full-text indexes trong cơ sở dữ liệu được đặt trong một full-text catalog. Người quản trị hệ thống có thể chia một catalog thành nhiều catalogs nếu indexes quá lớn. 10.3.11 Sử dụng Full-text Indexes Full-text indexes có thể được tạo thông qua Enterprise Manager, sử dụng Full-Text Indexing Wizard. Sau khi tạo, full-text queries có thể được thực hiện trên những bảng đã được tạo indexes. Người quản trị có thể thực hiện Full-text queries sử dụng hai từ khóa CONTAINS và FREETEXT. 98
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ 10.4 Câu hỏi trắc nghiệm 1. Trong khi thiết kế bảng, bạn xác định ràng buộc PRIMARY KEY cho cột Emp_No. Kiểu ràng buộc nào sau đây hỗ trợ thực hiện ví dụ trên? A User-Defined Integrity B Referential Integrity C Entity Integrity D Domain Integrity 2. Trong khi thiết kế bảng PROJECT, bạn xác định cột Emp_No là khoá ngoại của bảng, được tham chiếu từ bảng EMPLOYEE. Loại ràng buộc nào sau đấy được sử dụng? A User-Defined Integrity B Referential Integrity C Entity Integrity D Domain Integrity 3. Trong bảng EMPLOYEE, bạn cần có ràng buộc để kiểm tra cột Sex trong bảng chỉ được phép có 1 trong 2 giá trị là True hoặc False. Loại ràng buộc nào sau đấy được sử dụng? A User-Defined Integrity B Referential Integrity C Entity Integrity D Domain Integrity 4. Hướng dẫn nào sau đây là đúng khi tạo một cột có thuộc tính IDENTITY? A Có nhiều hơn 1 cột trong 1 bảng có thể có thuộc tính IDENTITY. B Thuộc tính IDENTITY có thể thiết đặt trên cột kiểu dữ liệu là Char. C Cột có 5. Khi nói về Indexes, câu nào sai trong số các câu sau đây? A Index cho phép người dùng tìm dữ liệu mà không cần quét toàn bộ bảng B Index được dùng để tìm ra giá trị duy nhất. Mục đích của index để xác định dòng nào đang chứa dữ liệu cần tìm C Index nên được tạo trên tất cả các trường của bảng D Chỉ số có thể được tạo bởi 1 hoặc nhiều trường 6. Indexing nên được áp dụng trong những trường hợp nào sau đây? A Khi cột chỉ chứa đựng vài giá trị khác nhau B Khi cột được sử dụng cho việc tìm kiếm thường xuyên 99
Chương 10. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ C Khi bảng chỉ có vài dòng D Khi cột được dùng để sắp xếp dữ liệu 7. Trong những bảng có index, những câu lệnh thực hiện dữ liệu (DML) cần ít thời gian hơn trong những bảng không có index. A Đúng B Sai 8. Số lượng lớn nhất các cột có thể bao gồm trong index kết hợp? A 2 B 16 C 4 D Không giới hạn 100
Chương 11. RÀNG BUỘC DỮ LIỆU VÀ CHỈ SỐ-Phần thực hành 11 Chương 11. DATA INTEGRITY AND INDEXES Phần thực hành Mục đích: - Sử dụng Enterprise Manager để thực hiện các Constraints và Rules - Sử dụng Enterprise Manager Wizard để tạo các chỉ số - Sử dụng QA để thêm các constraints - Sử dụng QA để tạo các rules và gán các rules cho các đối tượng - Sử dụng QA để tạo và xem chỉ số 11.1 Hướng dẫn trực tiếp 11.1.1 Tạo ràng buộc PRIMARY KEY Các bước thực hiện: - Vào Enterprise Manager - Vào phần thiết kế của bảng muốn tạo khoá chính - Bôi đen một trường hoặc nhiều trường làm khoá chính - Kích vào biểu tưởng Khoá trên thanh công cụ Hình 11.1 101