›
›

Giáo trình Cơ sở dữ liệu (Phần 2)

pdf 147 trang hapham 2730
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Cơ sở dữ liệu (Phần 2)", để 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:

  • pdfgiao_trinh_co_so_du_lieu_phan_2.pdf

Nội dung text: Giáo trình Cơ sở dữ liệu (Phần 2)

  1. TRƯỜNG ĐẠI HỌC CÔNG NGHIỆP TP/HCM TRUNG TÂM CÔNG NGHỆ THÔNG TIN -o0o- PPHHẦẦNN 22 SSQQLL SSEERRVVEERR
  2. Giáo trình SQL Server2000 Trang 1 MỤC LỤC BÀI 1: GIỚI THIỆU MICROSOFT SQL SERVER 2000 4 1.1 Tổng quan về SQL Server 2000. 4 1.2 Các thành phần của SQL Server 2000 7 1.3 Kiến trúc của CSDL quan hệ (relational Database Architecture) 7 1.4 Giới thiệu về Transact-Sql 11 BÀI 2: CÀI ĐẶT SQL SERVER 2000 12 2.1 Lập kế hoạch cài đặt SQL SERVER 2000 12 2.2 Quyết định những chọn lựa cấu hình cài đặt SQL SERVER 2000 12 2.2.1 Xác định tài khoản người dùng (User account) thích hợp cho SQL Server Sevice và SQL Server Agent Service 12 2.2.2 Chọn một chế độ xác thực (Authentication Mode) 13 2.2.3 Xác định Collation 13 2.2.4 Chọn Network Libraries 13 2.2.5 Xác định client Licensing Mode 13 2.3 Các bước cài đặt SQL Server 2000. 13 2.4 Tạo tập tin cài đặt không tham dự (unattended) và cài đặt từ xa (Remote) SQL Server 2000 20 2.5 Kết quả của việc cài đặt SQL SERVER 2000 21 BÀI 3: CÁC CÔNG CỤ CỦA SQL SERVER 22 3.1 SQL ENTERPRISE MANAGER 22 3.2 SQL SERVER SERVICES MANAGER 22 3.2.1 Các dịch vụ của SQL Server 23 3.2.2 Khởi động, tạm ngưng, dừng các dịch vụ của SQL Server 24 3.3 OSQL 24 3.4 SQL QUERY ANALYZER 25 3.4.1 Giới thiệu 25 3.4.2 Khởi động Query Analyzer 26 3.4.3 Thành phần chính của Query Analyzer 26 3.4.4 Một vài phím nóng dùng trong Query Analyzer 28 BÀI 4: LÀM VIỆC VỚI CỞ SỞ DỮ LIỆU SQL SERVER 29 4.1 Thiết kế một cơ sở dữ liệu 29 4.2 Cơ sở dữ liệu của SQL SERVER 2000 31 4.3 Tạo, hiệu chỉnh cở sở dữ liệu SQL SERVER 34 4.3.1 Giới thiệu 34 4.3.2 Tạo cơ sở dữ liệu 34 4.3.3 Thao tác trên cơ sở dữ liệu của SQL Server 37 BÀI 5: KIỂU DỮ LIỆU – LÀM VIỆC VỚI BẢNG 41 5.1 Kiểu dữ liệu (data type) 41 5.1.1 System-Supplied Datatype. 41 5.1.2 User-defined datatype. 42 5.2 Làm việc với bảng của SQL Server 43 5.2.1 Tạo một bảng mới 43 5.2.2 Hiệu chỉnh bảng 45 5.2.3 Xóa bảng khỏi cơ sở dữ liệu 46 5.3 Bảng tạm (Temporary Tables) 46 BÀI 6: TOÀN VẸN DỮ LIỆU 48 6.1 Giới thiệu toàn vẹn dữ liệu (data Integrity) 48 Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  3. Giáo trình SQL Server2000 Trang 2 6.2 Tìm hiểu các toàn vẹn dữ liệu. 48 6.2.1 Định nghĩa NULL/NOT NULL 48 6.2.2 Giá trị mặc định (Default Values) 49 6.2.3 Thuộc tính Identity: 52 6.2.4 Check 53 6.2.5 Primary key Constraint 54 6.2.6 Unique Constraints 57 6.2.7 Foreign Key Constraint 58 BÀI 7: TRUY XUẤT CƠ SỞ DỮ LIỆU CỦA SQL SERVER 61 7.1 Câu lệnh SELECT 61 7.2 Sử dụng JOINS để truy xuất dữ liệu 66 7.3 Dùng Sub-Queries 68 7.4 Hiệu chỉnh dữ liệu trong cơ sở dữ liệu của SQL SERVER 69 7.4.1 Chèn (INSERT) dữ liệu vào CSDL 69 7.4.2 Cập nhật (UPDATE) dữ liệu vào CSDL 71 7.4.3 Xóa dữ liệu trong cơ sở dữ liệu 71 BÀI 8: KHUNG NHÌN - VIEW 73 8.1 Giới thiệu về View 73 8.2 Tạo, hiệu chỉnh, xóa View 73 8.3 Tạo Partition view 74 8.4 Truy xuất dữ liệu thông qua View 75 8.4.1 Xem dữ liệu thông qua view. 75 8.4.2 Hiệu chỉnh dữ liệu thông qua View. 75 BÀI 9: CHUYỂN ĐỔI DỮ LIỆU 77 9.1 Khái niệm chuyển đổi và biến đổi dữ liệu 77 9.1.1 Import/Export dữ liệu 77 9.1.2 Biến đổi dữ liệu (Data Transformations) 77 9.1.3 Các công cụ chuyễn đổi dữ liệu (Data transfer tools) 77 9.2 Dịch vụ chuyển đổi dữ liệu DTS (Data Transformation Services - DTS) 78 9.2.1 DTS Package 78 9.2.2 DTS Connections 78 9.2.3 DTS Tasks 78 9.2.4 DTS Package Workflow 80 9.2.5 DTS Package Storage 80 9.3 Thực hiện việc biến đổi và chuyển đổi dữ liệu bằng công cụ đồ họa DTS 80 9.3.1 DTS Import/Export Wizard 80 9.3.2 DTS Designer 81 9.4 Dùng BULK COPY (BCP) và BULK INSERT 88 BÀI 10: CƠ BẢN VỀ LẬP TRÌNH BẰNG TRANSACT- SQL 91 10.1 Khái niệm cơ bản 91 10.1.1 Định danh -IDENTIFIERS. 91 10.1.2 Tham chiếu đến các đối tượng trong SQL Server. 91 10.1.3 Kiểu dữ liệu (DATA TYPE) 92 10.1.4 Batch 92 10.1.5 Kịch bản - SCRIPT 92 10.2 Biến (VARIABLES) 92 10.3 Cấu trúc điều khiển 97 10.3.1 Khối BEGIN END 97 10.3.2 Phát biểu PRINT 97 10.3.3 Cấu trúc điều khiển IF ELSE 97 Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  4. Giáo trình SQL Server2000 Trang 3 10.3.4 Biểu thức CASE 99 10.3.5 Cấu trúc vòng lập WHILE 100 10.3.6 Lệnh RETURN 101 10.3.7 Lệnh WAITFOR 101 10.3.8 Lệnh RAISERROR 101 BÀI 11: PROCEDURES, FUNCTIONS 104 11.1 STORED PROCEDURES 104 11.1.1 Giới thiệu Stored proccedures 104 11.1.2 Tạo, thực thi, hiệu chỉnh, xóa stored procedures. 104 11.1.3 Tham số và biến trong Stored procedures 106 11.2 FUNCTIONS 110 11.2.1 Scalar Functions 111 11.2.2 Table-valued Functons 111 BÀI 12: TRANSACTIONS – LOCK 114 12.1 TRANSACTIONS 114 12.2 LOCK 116 BÀI 13: SỬ DỤNG CURSORS ĐỂ TRUY XUẤT DỮ LIỆU 118 13.1 Khái niệm 118 13.2 Làm việc với T-SQL server cursors 119 13.3 Ví dụ. 121 BÀI 14: BẨY LỖI - TRIGGER 123 14.1 Giới thiệu về trigger 123 14.2 Tạo và quản lý các trigger 124 14.2.1 Tạo trigger 124 14.2.2 Quản lý trigger 125 14.3 Vài ví dụ về trigger 125 BÀI 15: BẢO MẬT TRONG SQL SERVER 128 15.1 Khái niệm về bảo mật 128 15.1.1 Mô hình truy cập bảo mật của SQL Server 128 15.1.2 Các chế độ bảo mật 128 15.1.3 Tìm hiểu các Server-Wide Permission. 130 15.1.4 Tìm hiểu các quyền (Permission) chỉ định trên cở sở dữ liệu. 131 15.1.5 Fixed Database Roles 132 15.2 Tạo tài khoản đăng nhập (Login). 133 15.2.1 Dùng Create Login Wizard 133 15.2.2 Dùng Enterprise Manager để tạo một Login 137 15.2.3 Tạo Login bằng T-SQL 141 Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  5. Giáo trình SQL Server2000 Trang 4 BÀI 1: GIỚI THIỆU MICROSOFT SQL SERVER 2000 1.1 Tổng quan về SQL Server 2000. Microsoft SQL Server 2000 là một hệ quản trị cơ sở dữ liệu quan hệ (Relational database management system - RDBMS), nó cung cấp các dịch vụ quản lý và lưu trữ dữ liệu cho các tổ chức thương mại lớn, cùng với việc truy xuất dữ liệu hỗ trợ đối với các người dùng thông qua internet. Nó cũng hỗ trợ khả năng truy xuất một cách dễ dàng đối với các tổ chức nhỏ hơn và các người dùng riêng biệt. SQL Server 2000 chấp nhận và thực thi các yêu cầu của các khách hàng (Client) đối với việc hiệu chỉnh và xoá dữ liệu, cũng như các lệnh tạo các đối tượng như là các cơ sở dữ liệu (database) và các bảng (Table). SQL Server cho phép người dùng truy xuất và sắp xếp dữ liệu theo cách quan hệ, việc lưu trữ dữ liệu một cách hiệu quả theo dạng dòng và cột. Các lệnh của người dùng được gửi như là các câu lệnh truy vấn giao tác (Transact-SQL). Transact-SQL (T-SQL) là một ngôn ngữ dùng bởi SQL Server 2000 để truy vấn một cơ sở dữ liệu hoặc hiệu chỉnh nội dung của nó. Một client từ một chương trình ứng dụng hoặc một người dùng gửi các câu lệnh T-SQL (các truy vấn hoặc hiệu chỉnh) thông qua mạng đến SQL Server để xử lý. Các chương trình ứng dụng mà gửi các câu lệnh có thể được viết bằng các ngôn ngữ như Visual Basic, Visual C++, hoặc Java. Chương này sẽ giúp bạn làm quen với Microsoft SQL Server cũng như thảo luận về các thành phần và thiết kết của nó. Microsoft SQL Server phát triển một cách nhảy vọt trong nhiều năm. Phiên bản mới nhất, SQL Server 2000 hỗ trợ nổi bật hơn về tính bảo mật, lưu trữ và tính sẳn dùng của dữ liệu. SQL Server 2000 được tích hợp chặt chẽ với hệ điều hành Windows 2000, việc nâng cao bảo mật và các công cụ dựa trên giao diện đồ họa đã cung cấp trong SQL Server 2000 là cho các tác vụ quản trị trở nên dễ dàng hơn. Các câu lệnh T-SQL dùng để hiệu chỉnh hoặc truy xuất dữ liệu có thể được chia thành ba nhóm khác nhau: - Ngôn ngữ định nghĩa dữ liệu (Data Definition Language - DDL) được dùng để định nghĩa các đối tương của cơ sở dữ liệu như các kiểu dữ liệu, bảng và các truy vấn. - Ngôn ngữ thao tác dữ liệu (Data Manipulation Language -DML) được dùng để chọn, chèn, cập nhật và xóa dữ liệu trong các đối tượng của CSDL. - Ngôn ngữ điều khiển dữ liệu (Data Control Language - DCL) được dùng để gán hoặc hủy bỏ các quyền từ các người dùng của CSDL. SQL Server 2000 chạy trên hệ thống mạng Windows NT 4 hoặc Windows 2000 Server bằng cách dùng nhiều bộ xử lý và có thể được cài đặt như một hệ thống CSDL cá nhân (personal desktop) trên các máy trạm Windows NT 4, Windows 2000 Professional, Windows 98, và Windows Me. Bạn có thể dùng cùng một CD để cài đặt phiên bản Server SQL Server 2000 hoặc Personal SQL Server 2000. Ngoài ra bạn có thể cài đặt nhiều thể hiện (instance) của SQL Server 2000 trên cùng một máy tính, mỗi thể hiện có một người chủ (owner) và tập các người dùng (user) của nó. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  6. Giáo trình SQL Server2000 Trang 5 Cơ sở dữ liệu (CSDL) - Databases Một CSDL hay là một database tương tự một tập tin dữ liệu, nơi để lưu trữ dữ liệu. Giống như một tập tin dữ liệu, một CSDL không trình bày thông tin một cách trực tiếp đến người dùng; muốn xem dữ liệu, người dùng phải chạy một ứng dụng truy xuất dữ liệu từ CSDL và khi đó dữ liệu được trình bày trong một dạng dễ hiểu và thuận tiện. Khi dữ liệu được lưu trong các tập tin dữ liệu thì phải có ứng dụng (được lập trình) để làm việc với cấu trúc đặc biệt của mỗi tập tin dữ liệu. Ngược lại, một CSDL chứa dựng một catalog mà các ứng dụng có thể dùng để xác định dữ liệu được tổ chức như thế nào. Các ứng dụng CSDL cùng loại có thể dùng catalog để trình bày đến người dùng các dữ liệu từ các CSDL khác nhau một cách linh động mà không bị phụ thuộc vào một dạng mẫu đặc biệt. Các hệ thống CSDL mạnh tức là các tập tin dữ liệu được tổ chức theo một mức độ cao. Một CSDL được thiết kế tốt, thì không có sự trùng lắp thông tin khi người dùng hoặc ứng dụng cập nhật tại cùng thời điểm. Các mãnh thông tin liên quan được kết nhóm với nhau trong một cấu trúc đơn hoặc mẫu tin và các mối quan hệ có thể được định nghĩa giữa các cấu trúc này và các mẫu tin này. CSDL quan hệ (Relational Database) Mặc dù có nhiều cách khác nhau để tổ chức dữ liệu trong một CSDL, các CSDL quan hệ là CSDL hiệu quả nhất. Các hệ thống CSDL quan hệ là một ứng dụng thuộc về lý thuyết tập hợp toán học đối với vấn đề tổ chức dữ liệu một cách hiệu quả. Trong một CSDL quan hệ, dữ liệu được thu thập vào các bảng (table). Một bảng trình bày lớp các đối tượng quan trọng đối với một tổ chức. Mỗi bảng được xây dựng thành các cột và các dòng (được gọi là các thuộc tính và các bộ trong lý thuyết quan hệ). Mỗi cột trình bày vài thuộc tính của đối tượng được thể hiện trong bảng. Ví dụ, bảng NHANVIEN sẽ có các cột điển hình đối với các thuộc tính như Họ, Tên, mã nhân viên, phòng ban, bậc lương, công việc Mỗi dòng trình bày một thể hiện của đối tượng thể hiện trong bảng. Ví dụ, một dòng trong bảng NHANVIEN trình bày thông tin về nhân viên có mã nhân viên là 12345. Khi tổ chức dữ liệu vào các bảng, bạn có nhiều cách khác nhau để định nghĩa các bảng. Lý thuyết CSDL quan hệ định nghĩa một phương thực gọi là dạng chuẩn (normalization) mà nó đảm bảo rằng tập các bảng mà bạn định nghĩa sẽ được tổ chức dữ liệu một cách hiệu quả. SQL Để làm việc với dữ liệu trong một CSDL, bạn phải dùng một tập các lệnh và câu lệnh (một ngôn ngữ) được hỗ trợ bởi DBMS. Bạn có thể dùng vài ngôn ngữ khác nhau với CSDL quan hệ; thông thường nhất là SQL. Ngôn từ của SQL được hỗ trợ bởi SQL Server được gọi Transact-SQL, và Transact-SQL là ngôn ngữ chính được dùng bởi hệ quản trị CSDL SQL Server. XML Extensible Markup Language (XML) là một ngôn ngữ lập trình siêu văn bản, dùng để mô tả nội dung của của tập dữ liệu và cách dữ liệu sẽ được kết xuất ra thiết bị hoặc hiển thị trên trang Web. SQL Server 2000 bắt đầu hỗ trợ cho XML. Các đặc tính mới này bao gồm: Khả năng truy xuất đến SQL Server thông qua URL. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  7. Giáo trình SQL Server2000 Trang 6 Hỗ trợ cho lượt đồ dữ liệu XML (XLM-Data schemas) và khả năng để chỉnh định các truy vấn XPath đối với các lược đồ này. Khả năng truy xuất và ghi dữ liệu XML: Truy xuất dữ liệu XML bằng cách dùng câu lệnh SELECT và mệnh đề FOR Ghi dữ liệu XML bằng OpenXML rowset provider. Microsoft SQL Server 2000 OLEDB provider (SQLOLEDB) cho phép tài liệu XML được gán như văn bản lệnh và trả về các tập kết quả như một luồng. Các ấn bản của SQL Server 2000 Microsoft® SQL Server™ 2000 sẳn có các ấn bản sau: SQL Server 2000 Enterprise Edition Dùng như một sản phẩm database server. Hỗ trợ tất cả các đặc tính có sẳn trong SQL Server 2000, và phân chia các mức hiệu năng để hỗ trợ các trang Web lớn và các tiến trình giao tác trực tuyến của doanh nghiệp (online transaction processing OLTP) và các hệ thống kho dữ liệu. SQL Server 2000 Standard Edition Dùng như một database server cho các nhóm và bộ phận nhỏ. SQL Server 2000 Personal Edition Dành cho các người dùng lưu động - những người có thời gian không kết nối với network nhưng vẫn chạy ứng dụng mà đòi hỏi sự lưu trữ dữ liệu SQL Server. Cũng sử dụng khi đang chạy một ứng dụng stand-alone mà yêu cầu việc lưu trữ dữ liệu SQL Server trên máy tính client. SQL Server 2000 Developer Edition Danh cho các nhà lập trình phát triển ứng dụng mà dùng SQL Server 2000 như là nơi lưu trữ dữ liệu. Mặc dù Developer Edition hỗ trợ tất cả các đặc tính của Enterprise Edition cho phép người phát triển viết và kiểm tra các ứng dụng có thể sử dụng các đặc tính, Developer Edition chỉ cấp quyền sử dụng như là hệ thống phát triển hoặc kiểm tra ứng dụng, không phải là một sản phẩm server. SQL Server 2000 Windows CE Edition Microsoft® SQL Server 2000™ Windows® CE Edition (SQL Server CE) được dùng khi lưu trữ dữ liệu trên thiết bị Windows CE. Có khả năng đồng bộ hóa dữ liệu với bất kỳ ẩn bản của SQL Server 2000 để sự đồng bộ hóa dữ liệu Windows CE với CSDL chính. SQL Server 2000 Enterprise Evaluation Edition Ấn bản với đầy đủ đặc tính, có thể tãi về từ trang Web. Với mục tiêu chỉ cho sử dụng ước lượng các đặc tính của SQL Server; phiên bản này sẽ ngưng chạy sau 120 ngày tãi về. Ngoài các ấn bản này của SQL Server 2000, SQL Server 2000 Desktop Engine là một thành phần mà cho phép người phát triển ứng dụng phân tán một bản sao của SQL Server 2000 relational database engine với ứng dụng của họ. Trong khi các chức năng của database engine trong SQL Server 2000 Desktop Engine là tương tự như database engine trong các ấn bản SQL Server, kích cở của các Desktop Engine databases không vượt quá 2 GB. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  8. Giáo trình SQL Server2000 Trang 7 1.2 Các thành phần của SQL Server 2000 Các thành phần của Server SQL Server service Thực thi SQL Server database engine. Có một SQL Server service cho mỗi thể hiện (instance) của SQL Server đang chạy trên máy tính. SQL Server Agent service Thực thi các tác nhân mà chạy các tác vụ quản trị SQL Server theo thời lịch. Chỉ có một SQL Server Agent service cho mỗi instance của SQL Server đang chạy trên máy tính. SQL Server Agent cho phép định nghĩa và lập lịch các tác vụ mà chạy dựa trên thời lịch hoặc tuần hoàn. Microsoft Search service (chỉ ở Windows NT và Windows 2000) Thực thi bộ máy tìm kiếm full-text (full-text search engine). Chỉ có một dịch vụ bất chấp số các instance SQL Server trên máy tính. MSDTC service (Chỉ ở Windows NT và Windows 2000) Quản trị các giao tác phân tán. Chỉ có một service, bất chấp số các instance SQL Server trên máy tính. MSSQLServerOlAPService service (chỉ ở Windows NT và Windows 2000) Thực thi SQL Server 2000 Analysis Services. Chỉ có một service, bất chấp số các instance SQL Server trên máy tính. Các công cụ đồ họa (Graphical Tools) SQL Server Enterprise Manager: công cụ quản trị CSDL và server chính, nó cung cấp một giao diện Microsoft Management Console (MMC). SQL Profiler: tạo cơ hội các người quản trị một công cụ tinh vi để theo dõi và phân tích giao thông mạng đến và đi từ một server đang chạy SQL Server 2000. SQL Query Analyzer: dùng để tạo và quản trị các đối tượng CSDL và kiểm tra các câu lệnh Transact-SQL, các batch, script một cách tương tác. SQL Server Service Manager: được dùng để start, stop, và pause các dịch vụ của SQL Server. Client Network Utility: dùng để quản trị các client Net-Libraries và định nghĩa các bí danh server bao gồm các tham số kết nối server tuy chọn nếu cần. Server Network Utility: dùng để quản trị các server Net-Libraries. SQL Server Books online: là một tài liệu trực tuyến hỗ trợ với Microsoft® SQL Server™ 2000. Bạn có thể tìm thông tin trong SQL Server Books Online bằng cách: Điều hướng thông qua bảng nội dung. Gõ một từ khóa trong index. Gõ một từ hoặc một cụm từ và thực hiện việc tìm kiếm. 1.3 Kiến trúc của CSDL quan hệ (relational Database Architecture) Trong SQL Server 2000, dữ liệu được lưu trong các CSDL. Về vật lý, một CSDL bao gồm hai hoặc nhiều hơn hai tập tin trên một hoặc nhiều đĩa, sự thể hiện vật lý này chỉ thấy được bởi nhà quản trị và nó trong suốt đối với người dùng. Sự chọn lựa vật lý của CSDL là trách nhiệm chính của người quản trị CSDL. Về mặc logic, một CSDL được xây Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  9. Giáo trình SQL Server2000 Trang 8 dựng thành các thành phần (component) được hiển thị đối với người dùng, chẳng hạn các bảng (Table), các khung nhìn (view) và các thủ tục (stored procudure). Sự chọn lựa logic của CSDL (chẳng hạn như thiết kế các bảng và các chỉ mục) là trách nhiệm chính của người thiết kế CSDL. Các CSDL chứa các dữ liệu của người dùng được gọi là CSDL của người dùng (user database). Ngoài ra, các hoạt động của SQL Server tùy thuộc vào bốn CSDL hệ thống (system Database): master, model, Tempdb, và msdb. Các CSDL này tồn tại trong mỗi thể hiện của SQL Server. CSDL hệ thống và CSDL người dùng CSDL Master: đuợc dùng để điều khiển các thao tác và thông tin mức hệ thống của SQL Server. Lưu trữ thông tin hệ thống trong 16 bảng hệ thống, gọi là system catalog. System catalog lưu trữ các thông tin tài khoản người dùng, bao gồm các mục như là bảo mật, ID, mật khẩu, các CSDL lưu trên server, các biến môi trường, các thông điệp lỗi hệ thống, và các thủ tục hệ thống. CSDL Model: được dùng như là một CSDL mẫu cho tất cả các CSDL mới được tạo ra trong hệ thống. CSDL Model có thể được tùy biến để tạo một cấu trúc CSDL mặc định mới cho CSDL mới. Ví dụ, nếu một bảng cần phải tồn tại trong CSDL mới sau này thì các bảng đó nên được tạo trong CSDL model. Các CSDL mới vừa được tạo sẽ có kích cở ít nhất bằng kích cở CSDL model. CSDL Tempdb: được dùng như là vùng lưu chứa tạm thời đới với các bảng và các thủ tục tạm. SQL Server 2000 hỗ trợ hai loại bảng tạm: bảng tạm toàn cục (global temporary table) và bảng tạm cụ bộ (local temporary table). Tên của bảng tạm toàn cục được bắt đầu ##, có thể được truy xuất đối với tất cả các client, trong khi tên bảng tạm cụ bộ bắt đầu #, chỉ có thể truy xuất đối với những client mà tạo chúng. Làm việc trong Tempdb rất nhanh vì các hoạt động không được ghi nhận lại. Tuy nhiên, khi client kết thúc kết nối đến Server thì toàn bộ các bảng và thủ tục trong TempDB sẽ bị xóa. CSDL Msdb: SQL Server Agent dùng CSDL msdb cho các tác vụ khác nhau, như lập biểu, cảnh báo và ghi nhận các thao tác. Dữ liệu được lưu trong các bảng hệ thống trong CSDL msdb. Các bảng hệ thống được dùng bởi SQL Server Agent là sysalerts, syscategories, sysdownloadlist, sysjobhistory, sysjobs, sysjobschedules, sysjobservers, sysjobsteps, sysnotifications, sysoperators, systargetservergroupmembers, systargetservergroups, systargetservers và systaskids. Ngoài ra, CSDL msdb có 7 bảng dùng cho thao tác dự phòng và phục hồi dữ liệu: backupfile, backupmediafamily, backupmediaset, backupset, restorefile, restorefilegroup, và restorehistory. SQL Server có thể tự động tăng hoặc giảm kích cở của CSDL khi các dòng được thêm vào hoặc xóa đi. CSDL Pubs, Northwind: là hai CSDL ví dụ được dùng trong các tài liệu của SQL Server Các CSDL khác là các CSDL do người dùng tạo ra. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  10. Giáo trình SQL Server2000 Trang 9 Hình 1: Các CSDL hệ thống và CSDL của người dùng Khái niệm về cơ sở dữ liệu quan hệ: Databases, Tables, Columns, Views, Datatypes, và Database Schemas Dữ liệu trong một RDBMS như SQL Server 2000 được sắp xếp trong một số đối tượng, dễ thấy nhất là bảng (table). Dữ liệu được lưu trong các Table theo dòng và cột. Dữ liệu liên quan đến các mục thực tế như các nhân viên, các sản phẩm, việc gửi hàng, người tham gia . Được lưu trong các table riêng biệt. Ví dụ, hệ thống quản lý nhân viên của một công ty có thể có một bảng gọi là nhân viên, bảng này dùng để lưu chi tiết tất cả của các nhân viên trong công ty. Chi tiết của nhân viên bao gồm Họ nhân viên, tên nhân viên, địa chỉ, thành phố, số chứng minh nhân dân Thỉnh thoảng cần ẩn các thông tin riêng tư từ người dùng bằng cách dùng một truy vấn. Các truy vấn là các câu lệnh SQL lưu trong CSDL và có thể được tham chiếu đến các câu lệnh SQL theo cách như các table. SQL Server cũng sử dụng các kiểu dữ liệu, mỗi cột có thể có các kiểu khác nhau. Tất cả các kiểu khác nhau này của các đối tượng, giống như bảng, truy vấn và kiểu dữ liệu được lưu trong một CSDL. Trong môi trường SQL Server, một lược đồ CSDL (database schema) là tập hợp các đối tượng CSDL có liên quan đến việc sử dụng một tên duy nhất và nó thuộc về một người dùng đơn lẻ. Quản lý dữ liệu chính là lưu trữ với sự trợ giúp của một số đối tượng được cung cấp cung cấp bởi SQL Server 2000. Bảng 2 liệt kê các đối tượng chính trong một database của SQL Server 2000. Bảng 1: Đối tượng Mô tả Bảng - Table Một table là một tập hợp của dữ liệu lưu trữ trong nhiều dòng nhiều cột. Mặc định – Một default là một cài đặt hệ thống, nghĩa là không được chỉ định bởi Default người dùng. Cột - Column Cột là các đối tượng dùng lưu trữ các phân tử riêng biệt bên trong một dòng dữ liệu. Dòng – Row Dòng là một mục đơn của dữ liệu bên trong một bảng, nó bao gồm tập các cột. Kiểu dữ liệu - Kiểu dữ liệu là các đối tượng dùng để xác định loại của dữ liệu có thể Datatype được lưu trong cột. Các loại dữ liệu khác nhau là tinyInt, smallInt, int, real, float, smalldatetime, datetime, smallmoney, money, char và sql_variant. Chỉ mục – Index Chỉ mục là một đối tượng của CSDL, dùng để tăng tốc các truy vấn bằng cách dò tìm dữ liệu theo giá trị khóa thay cho việc quét toàn bộ bảng. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  11. Giáo trình SQL Server2000 Trang 10 SQL Server 2000 hỗ trợ các chỉ mục clustered và non-clustered. Chỉ mục Clustered (Clustered indexe) là các chỉ mục mà sắp xếp các dòng theo cách dùng B-Tree. Bẩy lỗi – Trigger Trigger được ưa chuộng bởi vài nhà lập trình. Chúng chính là các thủ tục mà có thể chạy khi dữ liệu trong bảng được hiệu chỉnh như là cập nhập, xóa, chèn. Các trigger thường được dùng để đảm bảo tính thống nhất giữa các bảng liên kết. Các trigger có thể được tạo trên bảng hoặc truy vấn. Quy tắt - Rule Rule là đối tượng CSDL, nó được gắn kết với một cột hoặc một định nghĩa người nhằm hạn chế các giá trị. Ví dụ, một rule có thể đảm bảo số chứng minh nhân dân chỉ gồm các chữ số. Thủ tục - Stored Thủ tục là một tập các câu lệnh T-SQL được dịch trước (pre-compile) và Procedures được thực thi như một đối tượng. Do chúng đã được dịch trước nên các thủ tục sẽ thực thi nhanh. Khung nhìn - View là một cách nhìn khác của table. Dùng để hiển thị các dữ liệu trong View một hoặc nhiều table. Để sử dụng được view, người dùng phải có quyền trên view và tất cả các đối tượng phụ thuộc. Cấu trúc vật lý của một CSDL Mỗi CSDL bao gồm ít nhất một tập tin dữ liệu (data file) và một tập tin log (transaction log file). Các tập tin này không được chia xẻ với các CSDL khác. Để tối ưu hiệu năng và để hỗ trợ khả năng chịu lỗi (fault tolerance), tập tin dữ liệu và log được trãi dài trên nhiều đĩa và thông thường dùng một Raid (Redundant array of independent disks) Hình 2: Khung nhìn người dùng và triển khai vật lý của một CSDL Nhân bản - Replication Nhân bản cho phép nhiều thể hiện của SQL Server ở vị trí từ xa có cùng dữ liệu. Vị trí từ xa có thể bao gồm các người dùng di động hoặc các site kết nối thông quan internet, dial- up hay intranet. Sự phân chia vật lý của dữ liệu cải thiện hiệu năng của tổ chức khi dữ liệu cần được xử lý tại các nơi khác để trình bày ở site khác để tham chiếu. Bảo mật trong SQL Server. SQL Server 2000 dùng hai mức bảo mật khi kiểm tra sự hợp lệ của một người dùng. Đó là chứng thực (authentication) và authorization Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  12. Giáo trình SQL Server2000 Trang 11 Authentication Để kết nối đến một thể hiện của SQL Server 2000 thì người dùng phải chỉ định một định danh đăng nhập hợp lệ (ID). Một ID đăng nhập là một tài khoản định danh điều khiển sự truy xuất đến một thể hiện của SQL Server 2000. SQL Server kiểm tra ID đăng nhập có được phép kết nối đến thể hiện của SQL Server hay không. Sự kiểm tra của một ID đăng nhập được gọi là chứng thực (authentication). SQL Server 2000 dùng hai loại chứng thực: Windows authentication và SQL Server authentication. Hai chế độ chứng thực (Authentication Mode): Windows Authentication mode, mixed mode. Authorization Sau khi một ID - tài khoản đăng nhập được chứng thực, SQL Server 2000 xác định ID có được ủy nhiệm để thi hành các thao tác trong CSDL hay không. Một ID đăng nhập tự nó không đưa ra các quyền (permission) truy xuất đến các đối tượng trong một CSDL. Một ID phải có một sự ủy nhiệm hoặc có quyền hợp lệ. Điều này ngăn chặn một đăng nhập tự động truy xuất đến các CSDL trong một thể hiện của SQL Server 2000. Chúng ta sẽ tìm hiểu bảo mật trong SQL Server kỹ hơn ở trong bài bảo mật. 1.4 Giới thiệu về Transact-Sql Transact_SQL là ngôn ngữ dùng chủ yếu trong SQL Server. Có đầy đủ tính chất của một ngôn ngữ lập trình. SQL là một chuẩn do IBM đề ra và tất cả ngôn ngữ lập trình bổ sung thêm một số tính năng riêng của ngôn ngữ lập trình đó. Đối với SQL Server thì các ứng dụng muốn truyền thông với SQL Server đều phải các câu lệnh T-SQL đến Server. Câu lệnh T-SQL là tập hợp các đoạn mã mà thực thi một vài hành động lên các đối tượng hoặc dữ liệu của một CSDL. SQL Server cung cấp 3 loại câu lệnh T- SQL: DDL, DCL, và DML Ngôn ngữ định nghĩa dữ liệu (Data Definition Language – DDL): Dùng để định nghĩa và quản lý các đối tượng và đặc tính của đối tượng một CSDL. Lệnh DDL hỗ trợ việc định nghĩa, khai báo và chỉnh sửa các đối tượng của CSDL như Databases, Tables, Views. Mỗi lớp các đối tượng thì DDL có các lệnh như CREATE, ALTER, VÀ DROP (Ví dụ CREATE TABLE, ALTER TABLE, vaØ DROP TABLE). Ngôn ngữ điều khiển dữ liệu (Data Control Language – DCL): Dùng để hiệu chỉnh các quyền (Permission) trên các đối tượng của CSDL. Các permission được điều khiển bởi lệnh các lệnh GRANT (gán quyền), REVOVE (huỹ bỏ quyền), DENY (từ chối quyền) Ngôn ngữ thao tác dữ liệu (Data Manipulation Language – DML): Các lệnh này được dùng để truy vấn (SELECT), chèn (INSERT), cập nhật (UPDATE), xóa (DELETE) các dữ liệu của các đối tượng được tạo bởi DDL. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  13. Giáo trình SQL Server2000 Trang 12 BÀI 2: CÀI ĐẶT SQL SERVER 2000 2.1 Lập kế hoạch cài đặt SQL SERVER 2000 Các yêu cầu về phần cứng Phần cứng Yêu cầu tối thiểu Intel hoặc tương thích. Máy tính Pentium 166 MHz hoặc cao hơn. Enterprise Edition: 64 MB; 128 MB hoặc hơn Standard Edition: 64 MB. Personal Edition: 64 MB đối với Windows 2000; 32 MB đối với các Bộ nhớ (RAM) hệ điều hành khác. Developer Edition: 64 MB. Desktop Engine: 64 MB đối với Windows 2000; 32 MB đối với các hệ điều hành khác. Các thành phần CSDL SQL Server: 95 đến 270 MB; 250 MB điển hình. Không gian đĩa Analysis Services: 50 MB; 130 MB điển hình. cứng English Query: 80 MB. Desktop Engine only: 44 MB. VGA hoặc độ phân giải cao hơn. Màn hình 800 x 600 hoặc độ phân giải cao hơn SQL Server. Thiết bị chuột Microsoft Mouse hoặc tương thích. CD 2.2 Quyết định những chọn lựa cấu hình cài đặt SQL SERVER 2000 2.2.1 Xác định tài khoản người dùng (User account) thích hợp cho SQL Server Sevice và SQL Server Agent Service Mỗi dịch vụ của SQL Server 200 chạy trong một ngữ cảnh bảo mật của một user account. Bạn có thể chọn hoặc local system account hoặc domain user account. Thông thường các Sevice đều chạy chung một user account. Local system account là một account hệ thống của Win NT hoặc Win 2000 với đầy đủ các quyền quản trị trên máy tính cục bộ. Tuy nhiên, Account này không có Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  14. Giáo trình SQL Server2000 Trang 13 quyền truy cập mạng. Tuy nhiên, trong môi trường Client/Server, bạn nên tạo và dùng một domain user account có tính chuyên biệt dành cho các dịch vụ. Sự lựa chọn một domain user account cho phép những dịch vụ của SQL Server truyền thông với những cài đặt SQL Server khác, tài nguyên mạng (như là chia sẻ tập tin) trên những máy tính khác trong môi trường domain. Domain user account mà bạn chọn phải có đầy đủ các quyền truy xuất trên máy tính cục bộ, nhưng không cần phải là thành viên của nhóm Administrator cục bộ hoặc dommain administrator. Những quyền được chỉ định này phải bao gồm quyền log on như là một dịch vụ, quyền truy xuất và thay đổi thư mục SQL Server, quyền truy xuất và thay đổi tập tin CSDL, đọc và ghi các khóa bất kỳ nào đó trong Registry của Windows. Bạn không cần lo lắng việc gán các quyền này, chương trình cài đặt SQL Server 2000 sẽ gán những quyền này một cách tự động cho domain user account mà bạn chỉ định. 2.2.2 Chọn một chế độ xác thực (Authentication Mode) SQL server 2000 hỗ trợ 2 chế độ xác thực: Windows authentication Mode và Mixed mode. Mặc định là Authentication Mode. Chế độ Windows Authentication Mode chỉ cho phép các user của hệ điều hành mới có thể kết nối với SQL Server. Chế độ Mixed mode cho phép tất cả các user của hệ điều hành hoặc SQL server đều có thể kết nối đến SQL Server. Nếu bạn đang trong giai đoạn tìm hiểu và thử nghiệm SQL Server thì bạn nên chọn Mixed Mode. Nếu bạn triển khai ứng dụng thì bạn nên sử dụng Windows authentication mode, với chế độ này tính bảo mật được tăng cao nhờ tích hợp thêm các khả năng bảo mật của hệ điều hành. 2.2.3 Xác định Collation Collation là tập hợp những qui tắt quản lý, nó sẽ ảnh hưởng đến cách lưu trữ dữ liệu, thứ tự sắp xếp Ví dụ khi bạn cài đặt hệ điều hành, bạn chọn lựa ngôn ngữ, bàn phím được sử dụng. Mỗi ngôn ngữ sẽ có bộ ký tự khác nhau do đó có những code page khác nhau và dựa trên cơ sở này hệ điều hành sẽ cài đặt các numbers, currencies, Times, Dates khác nhau. Khi bạn cài đặt SQL Server, chương trình cài đặt sẽ xác định một collation mặc định cho SQL Server dựa trên các cài đặt của hệ điều hành. 2.2.4 Chọn Network Libraries SQL Server sử dụng Network libraries để gửi các packet giữa SQL Server khách và chủ. Server và Client phải có ít nhất một Network Libraries chung. Các Network Libraries: NEBEUI, TCP/IP, IPX/SPX, Apple Talk ADSP, Banyan Vines, VIA giginet San. 2.2.5 Xác định client Licensing Mode SQL Server cung cấp 2 kiểu client licensing: Per processor và Per seat. 2.3 Các bước cài đặt SQL Server 2000. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  15. Giáo trình SQL Server2000 Trang 14 Bạn đưa điã CD chứa chương trình cài đặt SQL Server hoặc chạy tập tin Setup từ một vị trí nào đó trên mạng. Hình 3: Màn hình đầu tiên khi đưa đĩa SQL Server Persional Edition vào Chọn SQL Server 2000 Components Hình 4: Chọn thành phần cần cài đặt Chọn Install Database Server Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  16. Giáo trình SQL Server2000 Trang 15 Hình 5: Màn hình Welcome của MicroSoft SQL Server Chọn Next Hình 6: Chọn tên máy tính nơi mà sẽ cài 1 instance mới hoặc hiệu chỉnh instance có sẳn Local Computer : Cài trên máy Local Remote Computer : Cài đặt từ xa Virtual Server : Cài trên Server ảo Chọn Local Computer, chọn Next Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  17. Giáo trình SQL Server2000 Trang 16 Hình 7: Chọn hình thức cài đặt Create a new instance of SQL Server, or install client tools: Cài một instance mới trên server hoặc cài các client tools Upgrade, remove, or add components to an existing instance of SQL Server: Nâng cấp, hủy bỏ, hoặc thêm các component vào instance có sẳn. Advanced option: Một số chức năng nâng cao hoặc tạo tập tin cài đặt không tham dự. Chọn Create a new instance of SQL Server, or install client tools, chọn Next Hình 8: Thông tin của người dùng Chọn Next Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  18. Giáo trình SQL Server2000 Trang 17 Hình 9: Thông báo Lincense Chọn next Hình 10: Xác định công cụ cài đặt Client Tools Only : Chỉ cài đặt các tools của client Server and Client Tools : Cài đặt các tools của Server và Client Connectivity Only : Cài đặt các thành phần truy xuất dữ liệu và các thư viện network Chọn Server and ClientTools Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  19. Giáo trình SQL Server2000 Trang 18 Hình 11: Qui định tên của instance Gỏ vào Server01, chọn Next Hình 12: Chọn loại cài đặt Chọn Typical, chọn Next Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  20. Giáo trình SQL Server2000 Trang 19 Hình 13: xác định Serverice Account để chạy các Service Chọn Next Hình 14: Xác định chế độ chứng thực Windows Authentication Mode: chế độ xác thực của Windows Mixed Mode: Chế độ xác thực vừa của Windows và vừa của SQL Server Chọn Mixed Mode Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  21. Giáo trình SQL Server2000 Trang 20 Hình 15: Thông báo là SQL Server sẽ bắt đầu được cài đặt Chọn Next, tiến trình cài đặt bắt đầu Hình 16: Kết thúc cài đặt 2.4 Tạo tập tin cài đặt không tham dự (unattended) và cài đặt từ xa (Remote) SQL Server 2000 Có 3 cách để tạo ra tập tin khởi đầu cài đặt dành cho việc cài SQL Server 2000 không tham dự. Cách 1: Chương trình Setup SQL Server 2000 cung cấp lựa chọn Advanced Option để ghi nhận lại tập tin không tham dự .ISS. Nếu lựa chọn này được chọn thì quá trình cài đặt sẽ được ghi nhận lại thành tập tin .ISS trong thư mục \Winnt, và SQL Server không được cài đặt thực sự trong tiến trình này. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  22. Giáo trình SQL Server2000 Trang 21 Cách 2: tập tin .ISS được cung cấp trong đĩa SQL Server 2000, trong thư mục gốc. Bạn có thể dùng trực tiếp từ các tập tin này hoặc hiệu chỉnh bằng các trình soạn thảo văn bản. Tập tin đó là SQLINS.ISS, SQLCLI.ISS; SQLCST.ISS Cách thứ 3: Tập tin .ISS được tự động tạo ra ngay sau khi mỗi lần bạn cài đặt SQL Server, nó nằm trong \WinNt. Tuy nhiên, nếu dùng tập tin này thì bạn phải chỉnh sửa nó nằng một trình soạn thảo văn bản để thêm phần [SdFinish-0] 2.5 Kết quả của việc cài đặt SQL SERVER 2000 Trình cài đặt SQl server tạo ra một loạt các thư mục để lưu các tập tin thi hành và cấu hình khác nhau. Theo ngầm định, SQL Server được cài đặt trên ổ đĩa C: trong thư mục MSSQL. Dưới thư mục MSSQL là vài thư mục khác: Backup Thư mục vẫn còn trống ngay sau khi cài đặt. Thư mục này dùng để lưu trữ các tập tin dự phòng. BIN Các thư viện mạng phía client BINN Các tập thi hành và các tập tin kết hợp. Kể cả các tập tin thi hành chính của SQL Server, và mọi công cụ điều hành được nạp. CHARSETS Các bộ ký tự sắp xếp cho các kiểu cài đặt khác nhau. DATA Vị trí ngầm định cho các CSDL. INSTALL SQL Server Books Online và các chỉ mục. LOG Các sổ theo dõi lỗi SQL Server. Các tập tin văn bản tương đương với sổ theo dõi sự kiện Windows NT, nhưng chi tiết hơn. REPLDATA Vị trí ngầm định cho dữ liệu tạm thời được dùng trong khi sao lặp. SNMP Các MIB (Management Information Bases = cơ sở thông tin quản trị) cho SQL Server. SQLOLE các mẫu về các dùng OLE automation của Visual Basic để quản lý SQL Server. SYSBOLS Gỡ rối các ký hiệu do các lập trình viên sử dụng. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  23. Giáo trình SQL Server2000 Trang 22 BÀI 3: CÁC CÔNG CỤ CỦA SQL SERVER 3.1 SQL ENTERPRISE MANAGER Enterprise Manager còn gọi tắt là EM, là một công cụ chính dành cho nhà quản trị server và CSDL. Enterprise Manager cho phép bạn dừng và khởi động một Server, cũng như cho phép bạn thực hiện các tác vụ sau: Đăng ký một server. Cấu hình các server cục bộ hoặc từ xa. Cấu hình và quản lý một cài đặt với nhiều server (multiple-server). Cài đặt các bảo mật đăng nhập (login security), thêm các người dùng (user), các nhà quản trị hệ thống (system administrator), và các điều hành viên. Gán một password nhà quản trị hệ thống. Tạo và lập biểu cho các công việc (job). Tạo các cảnh báo và cấu hình giao tiếp đến nhà quản trị hệ thống thông qua e- mail. Cài đặt và quản trị các CSDL, các bảng (table), các chỉ mục (index), các truy vấn (view), các thủ tục (stored procedure), các qui tắt (rule), các bẩy lỗi (trigger), các mặc định (default), các thiết bị dự phòng (backup device), và các vết lỗi (error log). Quản lý các service khác. Hình 17: Cửa sổ Enterprise Manager của SQL Server 3.2 SQL SERVER SERVICES MANAGER SQL Server Manager là một công cụ cho phép khởi động, tạm dừng hoặc dừng các dịch vụ (service) trong SQL Server. SQL Server hoạt động được thông qua các dịch mà nó tự cung cấp. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  24. Giáo trình SQL Server2000 Trang 23 3.2.1 Các dịch vụ của SQL Server Một dịch vụ (service) là một chương trình hoặc tiến trình thực thi một chức năng đặc biệt nào đó nhằm hỗ trợ cho các chương trình khác. SQL Server cung cấp các dịch vụ sau: SQL Server Service, SQL Server Agent Service, MicroSoft Search Service và Microsoft Distributed Transaction Coordinator. Mỗi dịch vụ có chức năng và nhiệm vụ riêng, hỗ trợ cho các hoạt động của SQL Server. Nếu bạn cài đặt một hoặc nhiều hơn thể hiện (instance) của SQL Server thì tên của service cho mỗi instance của SQL Server là MSSQL$InstanceName, $InstanceName là một tên của instance mà bạn chỉ định ở lần cài đặt. Ứng với mỗi SQL Server Agent service cho mỗi instance được gọi là SQLAGENT$InstanceName. Tuy nhiên, nhiều instances của SQL Server, sẽ chỉ có một Microsoft Distributed Transaction Coordinator và Microsoft Search. SQL SERVER SERVICE Khi bạn khởi động SQL Server có nghĩa là dịch vụ SQL Server service được khởi động ở Windows NT hoặc Windows 2000. Dịch vụ này quản lý các tập tin CSDL, xử lý các câu lệnh T-SQL, định vị tài nguyên giữa các kết nối của người dùng hiện hành, đảm bảo tính nhất quán dữ liệu, và nhiều hơn nữa. SQL SERVER AGENT SERVICES SQL Server Agent hỗ trợ việc lập biểu và thực thi các công việc (job), các cảnh báo (alert), thông báo, và kế hoạch duy trì CSDL. Không có service này, công việc quản trị của bạn sẽ trở nên khó khăn nhiều. SQL Server Agent cho phép bạn thực hiện tự động các thao tác nhằm duy trì CSDL. Ví dụ: bạn có thể tạo một job để thực hiện tự động dự phòng (backup) dữ liệu mỗi đêm vào lúc 1 giờ sáng và một job khác thực hiện dự phòng transaction log mỗi 30 phút một lần. Để kiểm tra hiệu năng hệ thống của bạn cảnh báo hiện trạng hiệu năng để báo cho bạn nếu server CPU đã hoặt động trên 90%. SQL Server Agent phải chạy để thực thi các tác vụ kiểu như vậy. Dịch vụ này có thể được cấu hình khởi chạy một các tự động hoặc chạy một cách thủ công. Bạn nên cấu hình cho nó khởi động tự động để đảm bảo rằng các job, alerts, và notification sẽ có thể được thực thi. MICROSOFT DISTRIBUTED TRANSACTION COORDINATOR. Dùng quản lý các giao tác phân tán. MICROSOFT SEARCH. Dịch vụ Microsoft Search cho phép tạo một chỉ mục full-text và cơ chế tìm kiếm. Chuẩn SQL-92 định nghĩa các khả năng tìm kiểm với phép so sánh ký tự bằng, nhỏ hơn, lớn hơn một hằng số ký tự, giá trị ký tự có chứa mẫu chuỗi. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  25. Giáo trình SQL Server2000 Trang 24 Dùng dịch vụ Microsoft Search cho phép Microsoft® SQL Server™ 2000 và SQL Server 7.0 được hỗ trợ nhiều tìm kiếm tinh vi trên cột chuỗi ký tự. 3.2.2 Khởi động, tạm ngưng, dừng các dịch vụ của SQL Server Để khởi động hay dừng các dịch vụ SQL Server bằng cách dùng SQL Server Service Manager thì thực hiện các bước sau: 1. Click chọn Start Programs Microsoft SQL Server, và chọn Service Manager để hiển thị công cụ SQL Service Manager như hình Hình 18: SQL Server Service Manager. 2. Tên server cục bộ xuất hiện trong mục Server và các dịch vụ của SQL Server xuất hiện trong mục Service. Trong danh sách xổ xuống, chọn tên của server và dịch vụ mà bạn muốn điều khiển. Bạn có thể khởi động (start) và dừng (stop) các service đang được chọn bằng cách click vào ứng tương ứng. Nếu Service ở trạng thái dừng thì click vào Start/Continue để chạy tiếp. 3. Auto-start service when OS Server: cấu hình tự động chạy dịch vụ khi hệ điều hành khởi động. Lưu ý: Nếu SQL Server và SQL Server Agent service không được cấu hình chạy tự động thì bạn phải khởi động nó một cách thủ công 3.3 OSQL OSQL là một dấu nhắc tiện ích dùng để truy vấn một instance của SQL Server 2000 một cách tương tác bằng T-SQL, thủ tục hệ thống, tập tin lệnh. Nó cũng được dùng để xem xét các công việc hoặc bó lệnh, kể cả các lệnh của hệ điều hành đối với SQL Server 2000. Bạn có thể dùng osql bằng cách kết nối vào server và Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  26. Giáo trình SQL Server2000 Trang 25 thực thi các lệnh trong chế độ tương tác hoặc bằng cách kết nối vào và thực thi các lệnh như một phần của cú pháp lệnh osql. Cú pháp lệnh của osql như sau: Osql –S servername – U login_id – P password (1) Osql –S servername – E (2) Chúng ta dùng (1) khi bạn kết nối đến server bằng một tài khoản đăng nhập của SQL server, dùng (2) khi bạn kết nối bằng tài chế độ chứng thực là Windows. Khi dùng Osql để kết nối đến SQL Server 2000, có nhiều tham số mà bạn có thể dùng như là một phần của chuỗi kết nối. Nên nhớ rằng, Osql phân biệt chữ thường, chữ hoa. - S servername Chỉ định tên của Server của SQL Server mà bạn muốn kết nối. - U login_id Chỉ định tên của tài khoản đăng nhập - P password Chỉ định password của tài khoản đăng nhập nếu có - E Kết nối bằng tài khoản đăng nhập hiện hành của windows Khi bạn dùng osql trong chế độ tương tác, nó hiện thị thứ tự các dòng. Chúng ta gõ các câu lệnh SQL, mỗi câu lệnh trên mỗi dòng. Osql không thực thi câu truy vấn của bạn cho đến khi bạn gõ từ khóa GO trên một dòng. Không làm việc bằng osql, gõ EXIT để tắt kết nối và đóng tiện ích osql. Ví dụ: Hình 19: Minh họa tiện ích osql 3.4 SQL QUERY ANALYZER 3.4.1 Giới thiệu. SQL Query Analyzer là giao diện người dùng đồ họa (Graphical User Interface – GUI) dành cho các nhà lập trình. Query Analyzer cho phép thực hiện: Tạo các truy vấn (query), bó lệnh (script) và thực thi (execute) chúng để tác động đến CSDL của SQL Server. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  27. Giáo trình SQL Server2000 Trang 26 Tạo các đối tượng của CSDL một cách nhanh chóng từ những script được định nghĩa trước. Sao chép nhanh chóng các đối tượng của CSDL Tạo và thực thi các thủ tục (Stored procedures), hàm người dùng (user-defined function) Tìm lỗi (Debug) các thủ tục. Tìm lỗi (Debug) các vấn đề hiệu năng của của truy vấn (Show Execution Plan, Show Server Trace, Show Client Statistics, Index Tuning Wizard). Định vị các đối tượng trong các CSDL, xem và làm việc với các đối tượng. Chèn, cập nhật, xóa các mẫu tin trong table một cách nhanh chóng. 3.4.2 Khởi động Query Analyzer SQL Query Analyzer có thể được khởi động từ SQL Server Enterprice Manager hoặc từ Start Menu hoặc từ của sổ Command bằng cách thực thi tiện ích ISQLW. Khi bạn khởi động SQL Query Analyzer, thì hộp thoại Connect to SQL Server xuất hiện. Khi đó bạn phải xác định chế độ chứng thực được dùng để kết nối tới SQL Server. Hình 20: Hộp thoại kết nối Bạn có thể mở cùng lúc nhiều cửa sổ Query Analyzer để cho phép bạn làm việc trong các CSDL khác nhau hoặc thực thi các script khác nhau trong cùng một thời điểm. Mỗi cửa sổ tạo kết nối riêng biệt đến server. Những kết nối duy trì các cài đặt khác nhau và mỗi cửa sổ có CSDL hiện hành. Nếu bạn cố gắng thực thi một thao tác dành riêng trên một CSDL từ một query analyzer trong khi một query analyzer khác đang dùng CSDL, thao tác sẽ bị lỗi. 3.4.3 Thành phần chính của Query Analyzer SQL Query Analyzer bao gồm các cửa sổ, hộp thoại, hướng dẫn (wizard) giúp chúng ta thiết kế các tác vụ (Task) cần thiết để tạo các CSDL, lưu trữ, khai thác dữ liệu trong các CSDL đó. Cửa số Query Analyzer Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  28. Giáo trình SQL Server2000 Trang 27 Thanh tiêu đề (Title bar): Hiển thị tên của Server, CSDL hiện hành, và tài khoản kết nối tới. Công cụ Database trên thanh công cụ, cho biết và cho phép bạn thay đổi CSDL được kết nối hiện hành. Editor pane: dùng để đưa vào các câu lệnh và thực thi các câu lệnh T-SQL Màu của mã lệnh trong Query Analyzer: Màu Ý nghĩa Đỏ Chuỗi ký tự Đỏ đậm Thủ tục Xanh lá Bảng hệ thống Xanh lá đậm Chú giải Đỏ tươi Hàm hệ thống Xanh Từ khóa Xám Toán tử Màu của mã lệnh bạn có thể biết là bạn nhập câu lệnh vào là đúng hay sai. Bạn có thể đổi màu qui định bằng các chọn trang fonts trong hộp thoại Tools Option. Results pane: hiển thị kết quả của truy vấn được thực thi. Một hoặc nhiều hơn một trang được hiển thị trong results pane. Trang Messages: Hiển thị thông báo và các lổi gửi trả từ server. Trang Results: Hiển thị những kết quả như văn bản tự do. Trang Results Grid: Hiển thị các kết quả trong bảng kẻ lưới. Dữ liệu trong lưới chỉ để xem, không thể hiệu chỉnh được. Một vài truy vấn yêu cầu server trả về nhiều hơn một tập kết quả thì sẽ có nhiều hơn một trang Results Grid sẽ được hiển thị. Nếu lưới kết quả trống sau khi thi hành một truy vấn thì truy vấn không được trả về một bảng kết quả Trang Execution Plan: Hiển thị một biểu đồ của kế hoạch thực thi của truy vấn hiện hành. Bật tắt Execution Plan ta chọn từ thực đơn Query Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  29. Giáo trình SQL Server2000 Trang 28 Hình 21: Thành phần chính của Query Analyzer. Lưu ý: • Bạn có thể nhập vào chuỗi các câu lệnh mới hoặc mở một tập lệnh có sẳn. Khi bạn đã làm việc xong với tập lệnh bạn có thể lưu nó thành một tập tin để dàng dùng lại sau (tập tin có phần mở rộng là .SQL). • Truy vấn có thể là một câu lệnh đơn hoặc nhiều câu lệnh. Những câu lệnh có thể không thể thực thi như là một phần của cùng một truy vấn với những câu lệnh khác. Trong trường hợp này được viết cách nhau bởi từ khóa GO. • Những câu lệnh có thể gõ trên cùng một hàng hoặc trãi dài qua nhiều hàng. Do câu lệnh T-SQL thì quá dài để đặt trên một dòng, nên ta gõ chúng trên nhiều dòng, điều này sẽ làm cho chúng ta dễ đọc các câu lệnh. • Nếu không có lệnh được chọn thì khi bạn thi hành truy vấn thì toàn bộ nội dung của query pane sẽ được thực thi 3.4.4 Một vài phím nóng dùng trong Query Analyzer Hành động Phím nóng Thực thi CTRL+E or F5 Tìm kiếm CTRL+F Chuyển văn bản đang chọn thành chữ hoa CTRL+SHIFT+U Chuyển văn bản đang chọn thành chữ thường CTRL+SHIFT+L Các kết quả dạng văn bản CTRL+T Những kết quả trong lưới CTRL+D Giúp đỡ về Query Analyzer F1 Giúp đỡ một câu lệnh T-SQL được chọn SHIFT+F1 Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  30. Giáo trình SQL Server2000 Trang 29 BÀI 4: LÀM VIỆC VỚI CỞ SỞ DỮ LIỆU SQL SERVER 4.1 Thiết kế một cơ sở dữ liệu. Thiết kế CSDL là một bước vô cùng quan trọng trong tiến trình phát triển ứng dụng. Trong quá trình thiết kế, bạn phải quyết định những table nào cần để lưu trữ các dữ liệu của bạn. Quá trình thiết kế một CSDL logic được thực hiện độc lập với các hệ quản trị CSDL. Một số lưu ý khi thiết kế một CSDL logic: Các bảng và tên của chúng (còn gọi là các thực thể). Tên các cột (các thuộc tính) của mỗi bảng. Các đặc tính của cột như là giá trị duy nhất, cho phép null hay không, và kiểu của dữ liệu mà cột sẽ lưu chứa. Khóa chính (Primary key) cho mỗi bảng. Đó là một cột hoặc tập các cột mà chứa các giá trị được định nghĩa không trùng lặp ở các dòng trong bảng. Mỗi bảng chỉ có thể có một khóa chính. Mặc dù khóa chính là không yêu cầu bắt buộc phải có nhưng các bảng nên luôn luôn có. Các mối quan hệ (Relationship) giữa các bảng. Các dòng trong một bảng phụ thuộc một hoặc nhiều dòng khác trong bảng khác. Những phụ thuộc trong những bảng này được gọi là mối quan hệ. Để định nghĩa mối quan hệ, một cột hoặc tập các cột trong một bảng được gọi là khóa ngoại (foreign key) nếu nó tham chiếu đến khóa chính của bảng khác. Ví dụ: mỗi dòng trong một bảng đơn hàng (DONHANG) phụ thuộc vào một dòng trong bảng khách hàng (KHACHHANG) bởi vì mỗi đơn hàng phải được đặt bởi một khách hàng. Điều này chính là nối quan hệ giữa bảng DONHANG và bảng KHACHHANG. Bảng đơn hàng phải có một cột lưu giữ các giá trị được tham chiếu đến một dòng riêng lẻ trong bảng KHACHHANG. Các dòng trong bảng HOADON phải được đảm bảo chỉ tham chiếu đến một khách hàng vì vậy mối quan hệ nên dựa trên cơ sở khóa chính của bảng KHACHHANG. Cột của bảng đơn hàng mà tham chiếu đến khóa chính của bảng khách hàng được gọi là khóa ngoại. Các kiểu mối quan hệ: Ba kiểu của mối quan hệ có thể giữa các bảng: One-to-One. Mỗi dòng trong bảng chính có quan hệ đến chỉ một dòng trong bảng quan hệ. Một mối quan hệ one-to-one là được thực hiện bởi định nghĩa khoá ngoại là duy nhất (không trùng). One-to-Many. Mỗi dòng trong bảng chính được liên quan đến một hoặc nhiều dòng trong bảng quan hệ. Ví dụ một khách hàng có thể đặt nhiều đơn hàng, nhưng một đơn hàng không thể được đặt bởi nhiều khách hàng. Many-to-Many. Nhiều dòng trong một bảng liên quan đến nhiều dòng trong bảng khác. Ví dụ: một tác giả có thể viết nhiều quyễn sách và một quyễn sách có thể được viết bởi nhiều hơn một tác giả. Mối quan hệ many-to-many giữa 2 bảng là thực hiện bằng cách tạo một bảng thứ ba và tạo một mối quan hệ one-to-many đến bảng chức năng này từ mỗi bảng ban đầu. Một CSDL sau khi thiết kế được đánh giá thông qua các dạng chuẩn. CSDL đạt chuẩn cao thì CSDL đó lưu trữ đầy đủ thông tin, không bị trùng lấp, có tính nhất quán cao. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  31. Giáo trình SQL Server2000 Trang 30 Các dạng chuẩn của CSDL quan hệ Dạng chuẩn 1: Tất cả các thuộc tính đều được định nghĩa ở dạng 1 giá trị đơn hoặc không ở dạng repeating group. Ví dụ: Các quan hệ sau không đạt dạng chuẩn 1 OrderID CustomID OrderDate Items OrderTotal 1111 101 1/1/02 4 apples 1000 1111 103 1/2/02 5 bananas 900 Không đạt chuẩn 1 vì thuộc tính Items không là giá trị đơn OrderId CustomId Items Quarter1 Items Quarter2 Items Quarter3 Không đạt chuẩn 1 vì thuộc tính Items và Quarter có dạng repeating group Dạng chuẩn 2: Tất cả các thuộc tính không khóa phụ thuộc đầy đủ vào khóa. ProductName SupplerName CompanyName SupplerPhoneNumber Giả sử ProductName không trùng, ProductName là khóa. Vi phạm chuẩn 2 vì SupplerPhoneNumber chỉ phụ thuộc vào SupplerName mà không phụ thuộc vào ProductName (khóa) Dạng chuẩn 3: Tất cả thuộc tính không khóa phụ thuộc đầy đủ và không phụ thuộc bắt cầu vào khóa. CompanyName Address City Region Postcode Giả sử CompanyName là không trùng Vi phạm chuẩn 3 vì từ City và Region thì ta sẽ biết được Postcode, như vậy PostCode phụ thuộc vào City và Region. Trong đó CompanyName là một khóa dự tuyển (Candidate) Lưu ý: Một cột (field) có giá trị lặp đi lặp lại một giá trị thì giá trị đó gọi là dữ liệu dư thừa. Các giá trị này người sử dụng có thể gõ sai chính tả khi đó nên suy nghĩ để quyết định có nên nảy sinh thêm 1 table hay không. Hoặc trong tương lai có thể nảy sinh ra các giá trị mới của field đó thì việc phát sinh Table là ưu việt. Khi xây dựng dữ liệu của khóa chính, có 2 trường phái: khóa có ngữ nghĩa và khóa không có ngữ nghĩa. Để giải quyết các lỗi phát sinh khi dùng giá trị khoá có ngữ nghĩa, các chuyên gia khuyên là ngữ nghĩa của khoá chỉ dùng để tham khảo và không nên dùng nó khi viết các ứng dụng. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  32. Giáo trình SQL Server2000 Trang 31 4.2 Cơ sở dữ liệu của SQL SERVER 2000 Một CSDL của SQL Server là tập hợp các bảng (Table) dùng để chứa các dữ liệu và những đối tượng khác, chẳng hạn Views, indexes, store procedures, và triggers Chúng được ấn định để hỗ trợ các hoạt động được thực hiện với dữ liệu. Dữ liệu được chứa trong một CSDL thường liên quan đến một chủ đề hay tiến trình đặc biệt. SQL Server có thể hỗ trợ nhiều CSDL. Mỗi một CSDL có thể liên quan hay không liên quan đến các CSDL khác. Ví dụ một SQL Server có thể có CSDL chứa dữ liệu về nhân sự, và một CSDL khác chứa các đơn hàng. Khi bạn tạo một CSDL, bạn phải xây dựng cấu trúc lưu trữ dữ liệu. Các trúc này bao gồm ít nhất một tập tin dữ liệu (data file) và một tập tin vết giao tác (transaction log file). Bạn nên hiểu cách thức Microsoft SQL Server 2000 lưu trữ dữ liệu, cũng như chức năng của transaction log trước khi bạn làm việc với CSDL của SQL Server. Cách dữ liệu được lưu trữ trong CSDL của SQL Server: Hình 22: Cách lưu trữ dữ liệu Các tập tin CSDL SQL Server Một CSDL được lưu chứa trong các tập tin vật lý trên đĩa cứng, một CSDL trãi dài trên ít nhất là hai tập tin. Một vài tập tin dữ liệu (data file), và một tập tin vết (transaction log file). Những tập tin này được chỉ định khi CSDL được tạo hay hiệu chỉnh. SQL Server 2000 cho phép ba loại tập tin CSDL: Primary data files: một CSDL có một primary data file dùng để ghi nhận lại tất cả những tập tin khác trong CSDL, và lưu trữ dữ liệu. Theo ngầm định, tên của primary data file có phần mở rộng là .MDF. Secondary data files: một CSDL có thể không có hoặc có nhiều secondary data files, dùng để lưu các đối tượng của CSDL. Theo ngầm định, tên của secondary data file có phần mở rộng là .NDF. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  33. Giáo trình SQL Server2000 Trang 32 Log files: một CSDL có ít nhất một log file dùng chứa những thông tin cần thiết cho việc phục hồi tất cả những giao tác (transaction) trong CSDL. Theo ngầm định, log file có phần mở rộng là .LDF. Mỗi tập tin CSDL có năm thuộc tính: tên tập tin logic, một tên tập tin vật lý, một kích thuớc (size) ban đầu, một kích thước tối đa (maximum size), và gia số tăng kích thước (growth increment). Các thuộc tính của mỗi tập tin, theo cùng với những thông tin khác được ghi chú trong bảng hệ thống sysfiles, một dòng ứng với mỗi tập tin đuợc dùng trong một CSDL. Nhóm tập tin (Filegroups) Các Filegroup cho phép bạn kết nhóm các tập tin nhằm mục đích quản trị và sắp xếp dữ liệu. Các Filegroup có thể cải thiện hiệu năng CSDL bằng cách cho phép một CSDL được tạo ngang qua nhiều đĩa hoặc hệ thống RAID. Bạn có thể tạo các bảng và các chỉ mục trên đĩa được chỉ rõ bằng cách dùng filegroup. Có 3 dạng filegroup. Primary filegroup bao gồm primary data file và tất cả những tập tin khác không đặt trong những filegroup khác. Các bảng hệ thống (System table) – Dùng định nghĩa các người dùng (user), các đối tượng (object), và các quyền (permission) đối với một CSDL – là được đặt trong primary filegroup của CSDL đó. SQL Server tự động tạo các bảng hệ thống của từng CSDL khi chúng ta tạo CSDL. User-defined filegroups là bất kỳ các filegroup xác định mặc định bởi người dùng trong suốt tiến trình tạo hoặc hiệu chỉnh CSDL. Một bảng hoặc một chỉ mục có thể được tạo và đặt trong một user-defined filegroup chỉ định. Default filegroup: Chứa tất cả đối tượng các trang (page) của các bảng và các chỉ mục mà không được chỉ định filegroup khi chúng được tạo ra. Theo mặc nhiên, Default filegroup là primary filegroup. Các thành viên của role db_owner database có thể chuyển đổi trạng thái default từ một filegroup này cho filegroup khác. Tại một thời điểm chỉ có thể có một default filegroup. Nếu default filegroup không được chỉ định thì primary filegroup là default filegroup một các tự động. Lệnh hiệu chỉnh CSDL (ALTER DATABASE) được dùng để thay đổi default filegroup. ALTER DATABASE database_name MODIFY FILEGROUP filegroup_name DEFAULT Cách cấp phát khoảng không để lưu trữ Dữ liệu được lưu trữ trong các khối 8Kb liền nhau của không gian của đĩa được gọi là trang (page). Có nghĩa là một CSDL có thể lưu trữ 128 page mỗi megabyte (MB). Các dòng không thể trãi dài trên các page và phải nằm gọn trong 1 page. Tổng số lớn nhất của dữ liệu trong một dòng đơn là 8060 bytes (8192, trừ overhead). Tổng số khoảng không lớn nhất mà có thể sử dụng bởi các dòng trên một trang là 8094 bytes. Các bảng, các chỉ mục, được lưu trữ trong các extent. Một extent có 8 page kề nhau, hoặc 64 KB. Vì vậy, một CSDL có 16 extent mỗi megabyte. Có thể có đến 8 đối tượng nhỏ có thể chia sẻ trong một extent (Mixed Extent). Khi một bảng tăng lên 8 page, nó dùng extent đồng dạng (Uniform extent). SQL Server dùng 2 kiểu đồ thị định vị (allocation map) để ghi nhận lại định vị của các extent: Global Allocation Map (GAM) Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  34. Giáo trình SQL Server2000 Trang 33 Các trang GAM ghi nhận các extent đã được cấp phát. Mỗi một GAM kiểm soát 64000 extent, hoặc gần 4 GB dữ liệu. GAM có một bit ứng với mỗi extent trong vùng mà có kiểm soát. Nếu bit đó mang giá trị là 1 thì extent là trống; nếu bit đó mang giá trị 0 thì extent đã được cấp phát. Shared Global Allocation Map (SGAM) Các trang SGAM ghi nhận các extent được sử dụng như là các mixed extent và có ít nhất một trang chưa dùng. Mỗi SGAM kiểm soát 64000 extent, hay gần 4 GB dữ liệu. SGAM có một bit ứng với extent trong vùng mà nó kiểm soát. Nếu bit mang giá trị 1 thì extent được đang sử dụng như là một mixed extent và hiện có trang trống (free page); nếu mang giá trị 0, thì extent đã được dùng như là mixed extent hay nó là một mixed extent mà tất cả các trang đều được dùng. Mỗi extent có một giá trị bit đặt trong GAM và SGAM dựa trên cơ sở có đang được dùng: Sử dùng hiện thời của extent Bit GAM Bit SGAM Trống, chưa được dùng 1 0 Uniform extent, hoặc mixed extent đầy 0 0 Mixed extent với các free page 0 1 Các extent được quản lý với một thuật toán đơn giản. Để định một extent đồng dạng, SQL Server tìm trên GAM một bit 1 và đặt nó thành bit 0. để tìm một mixed extent với những free page, SQL Server tìm trên SGAM một bit 1. Để định một mixed extent, SQL Server tìm trên GAM một bít 1, đặt nó thành 0, và đặt vào bit tương ứng trên SGAM giá trị 1. Một extent trống, SQL Server đảm bảo bít trên GAM mang giá trị 1 và trên SGAM mang giá trị 0 Transaction Log làm việc như thế nào? Transaction log ghi nhận sự hiệu chỉnh dữ liệu – các câu lệnh INSERT, UPDATE, và DELETE –khi chúng được thi hành. Tiến trình ghi vết ghi nhận lại: Một sự thay đổi dữ liệu được gửi từ ứng dụng. Khi một sự thay đổi được thực hiện thì các trang dữ liệu ảnh hưởng được tải lên từ tập tin dữ liệu trong bộ nhớ (gọi là data cache), nếu các trang không sẳn sàng trong data cache từ truy vấn trước đó. Mỗi câu lệnh hiệu chỉnh dữ liệu thì luôn luôn được ghi trong log như nó được tạo. Thay đổi thì luôn luôn ghi nhận lại thành vết và được ghi vào tập tin log (log file) trước khi thay đổi đó được tác động trong CSDL. Kiểu của log này gọi là write- ahead log. Khi các trang dữ liệu hiện nằm trong data cache, và những trang log được ghi nhận lại trên đĩa trong một tập tin transaction log thì tiến trình checkpoint ghi tất cả các transaction đã hoàn tất (committed transaction) vào CSDL trên đĩa. Một transaction đơn có thể có nhiều hiểu chỉnh dữ liệu. Mỗi transaction bắt đầu với một lệnh BEGIN TRANSACTION. Nếu ứng dụng hoàn tất tất cả sự hiệu chỉnh dữ liệu một Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  35. Giáo trình SQL Server2000 Trang 34 cách thành công thì transaction kết thúc với lệnh COMMIT TRANSACTION (như là một transaction được nối là transactin hoàn tất- committed transaction). Trong suốt quá trình hoạt động, tiến trình checkpoint đều đặn thường xuyên kiểm tra các transaction đã hoàn tất mà sự hiệu chỉnh dữ liệu chưa được ghi vào tập tin dữ liệu. Tiến trình checkpoint ghi những hiệu chỉnh này vào tập tin dữ liệu và checkpoint các transaction cho biết rằng nó đã được viết vào tập tin dữ liệu chưa. Nếu hệ thống bị hỏng hóc, tiến trình phục hồi (recovery process) tự động chạy khi SQL Server được khởi động lại. Tiến trình này sử dụng transaction log để quay ngược lại đến các transaction hoàn tất mà chưa từng được "checkpointed" và xoá bỏ đến (roll back) các transaction chưa hoàn tất. Cơ chế tự động ghi nhận vết trong SQL Server là không là một lựa chọn (option) (có nghĩa là bạn không thể tắt nó đi), tất cả các hiệu chỉnh dữ liệu đều phải đi qua transaction log (Có 2 phương pháp tải một lượng dữ liệu lớn mà có thể được thực hiện mà không dùng transaction log, đó là chương trình bulk copy và lệnh SELECT INTO). Dữ liệu vrất quan trọng nên transaction log không bao giờ đầy (full) bởi điều này sẽ ngăn chặn hiệu chỉnh dữ liệu trong CSDL. 4.3 Tạo, hiệu chỉnh cở sở dữ liệu SQL SERVER 4.3.1 Giới thiệu Để tạo một CSDL, trước hết bạn phải định nghĩa một tên cho CSDL, kích cở của nó, và các tập tin primary data file, secondary data file và file group dùng để lưu trữ nó. Bạn nên xem xét vài nhân tố sau trước khi bạn tạo CSDL: Quyền để tạo một CSDL mặc nhiên phải là thành viên của sysadmin và DBCreator fixed server role, mặc dù quyền này có thể gán cho bất kỳ user nào. User - người tạo ra CSDL trở thành chủ (owner) của CSDL. Có thể có tối đa 32767 CSDL có thể tạo trong một server. Tên của CSDL phải đặt theo qui tắt định danh. Khi tạo CSDL bạn nên chỉ định dung lượng lớn nhất có thể có của một CSDL, điều này sẽ ngăn chặn sự gia tăng không kiểm soát kích thước của CSDL. SQL Server tạo CSDL thông qua 2 bước: - SQL Server sử dụng một bản sao của CSDL Model để khởi tạo CSDL mới và biến đổi nó. - Sau đó SQL Server nhồi đầy phần còn lại của CSDL bởi các trang trống. Các phương pháp tạo, hiệu chỉnh một CSDL của SQL Server. Cách 1: dùng SQL Enterprise Manager Cách 2: dùng Create Database Wizard. Cách 3: dùng câu lệnh CREATE DATABASE. 4.3.2 Tạo cơ sở dữ liệu Tạo bằng Database Wizard (ơÛ tại cửa sổ Enterprise Manager) 1. Mở rộng server group, sau đó nới rộng server nơi mà sẽ tạo CSDL. 2. Chọn thực đơn Tools Wizards. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  36. Giáo trình SQL Server2000 Trang 35 3. Mở rộng Database. 4. Nhấp phải chuột tại Create Database Wizard. 5. Hoàn tất các bước trong Wizard. Tạo bằng Enterprise Manager (ở tại cửa sổ Enterprise Manager) 1. Mở rộng server group, sau đó nới rộng server nơi mà sẽ tạo CSDL. 2. Nhắp nút phải chuột tại nút Database, chọn New DataBase. 3. Khai báo các thông tin cần thiết, sau đó chọn OK: Trang General + Name: Trang Data Files + Location : + Initial size: + File Group : + File properties: Khai báo một số thuộc tính khác như tỉ lệ gia tăng (File Growth), kích cở tối đa (maximun size) Trang Transaction log: Tương tự như trang Data files nhưng khai báo cho tập tin log. Hình 23: Hộp thoại xem thuộc tính của SQL Server Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  37. Giáo trình SQL Server2000 Trang 36 Tạo bằng câu lệnh Create Database (gõ lệnh trong cửa sổ Query Analyzer). Cú pháp CREATE DATABASE database_name [ ON [ [, n ] ] [, [, n ] ] ] [ LOG ON { [, n ] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ] ::= [ PRIMARY ] ([ NAME = logical_file_name, ] FILENAME = 'os_file_name' [, SIZE = size ] [, MAXSIZE = { max_size | UNLIMITED } ] [, FILEGROWTH = growth_increment ]) [, n ] ::= FILEGROUP filegroup_name [, n ] Thực hiện: (1) Gõ lệnh trong cửa sổ Query Analyzer (2) Gọi thực thi câu lệnh Ví dụ: Tạo CSDL có tên là SalesDB, tập tin dữ liệu tên là SalesDB_dat.mdf đặt trong C:\Data, kích cở khởi tạo là 10MB, kích thước tối đa là 50MB, tỉ lệ gia tăng là 5MB, và tập tin vết tên là SalesDB_log.ldf đặt trong C:\Data, kích thước khởi tạo là 5MB, kích thước tối đa là 25MB, tỉ lệ gia tăng là 10%. CREATE DATABASE SalesDb ON (NAME = SalesDb_dat, FILENAME = 'c:\data\salesDB_dat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) LOG ON (NAME = 'SalesDb_log', FILENAME = 'c:\data\salesDB_log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 10%) GO Lưu ý: - Thư mục Data phải hiện hữu trong C:\ Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  38. Giáo trình SQL Server2000 Trang 37 - Sao khi gõ câu lệnh, đánh dấu chọn khối lệnh và nhấn F5 để thực thi. 4.3.3 Thao tác trên cơ sở dữ liệu của SQL Server Khi làm việc với CSDL, bạn có thể thực hiện trực tiếp trong cửa sổ Enterprise Manager hoặc dùng các câu lệnh T-SQL trong cở sổ Query Analyzer. 4.3.3.1 Kiểm tra sử tồn tại của cơ sở dữ liệu Cách 1: Tại cửa sổ EM, kiểm tra sự tồn tại của CSDL trong nhánh DataBase. Cách 2: Tại cửa sổ QA, thực hiện câu lệnh Sp_helpdb Ví dụ: Sp_helpDB SalesDB 4.3.3.2 Xem, thay đổi thuộc tính của cở sở dữ liệu. Cách 1: Dùng Enterprise Manager Mở nút DataBase, R_Click tại tên CSDL cần xem hoặc hiệu chỉnh Chọn Properties Thay đổi tuỳ ý Cách 2: Dùng T_SQL Cú pháp ALTER DATABASE database { ADD FILE [, n ] [ TO FILEGROUP filegroup_name ] | ADD LOG FILE [, n ] | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name } | SET [, n ] [ WITH ] | COLLATE } ::= (NAME = logical_file_name [, NEWNAME = new_logical_name ] [, FILENAME = 'os_file_name' ] [, SIZE = size ] [, MAXSIZE = { max_size | UNLIMITED } ] [, FILEGROWTH = growth_increment ]) Ví dụ 1: Chỉnh sửa kích cở của tập tin log file của SalesDb thành 10MB ALTER DATABASE SalesDb MODIFY FILE (NAME='salesdb_log', size=10MB) Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  39. Giáo trình SQL Server2000 Trang 38 Ví dụ 2: Bổ sung thêm một tập tin dữ liệu SalesDB_data2 ALTER DATABASE SalesDB ADD FILE (NAME=SalesDB_data2, FILENAME='C:\data\SalesDb2.mdf',SIZE=10 MB, MAXSIZE=20MB) 4.3.3.3 Xóa cơ sở dữ liệu. Cách 1: Dùng Enterprise Manager Nhấn nút phải chuột tại tên CSDL, chọn Delete Cách 2: Dùng câu lệnh T-SQL DROP DATABASE database_name [, n ] Ví dụ: DROP DATABASE SalesDB 4.3.3.4 Đổi tên cơ sở dữ liệu. Dùng hàm sp_renamedb theo cú pháp sau sp_renamedb [ @dbname = ] 'old_name', [ @newname = ] 'new_name' 4.3.3.5 Tạo một script cho CSDL và các đối tượng của CSDL. Đôi khi, bạn cần sao chép cấu trúc của CSDL hoặc cấu trúc các đối tượng của CSDL, thì bạn sẽ thực hiện tạo script cho chúng. Khi có script bạn sẽ mở và thực thi đoạn script tại của sổ Query Analyzer để tái tạo lại các đối tượng. Các bước thực hiện: - Mở rộng một server group; mở rộng một server. - Mở rộng nhánh DataBase, nhấp phải tại CSDL muốn tạo script, trỏ đến All Tasks, nhấp General SQL SQL Script - Khai báo các lựa chọn thích hợp. + Trang General: chọn đối tượng cần tạo csript. + Trang Formating: chọn các tùy chọn địng dạng script. Generate the CREATE command for each object: Tạo script theo cách sử dụng định nghĩa đang có của nó. Lựa chọn này được chọn theo mặc định. Generate the DROP command for each object: Bổ sung vào script cho mỗi đối tượng câu lệnh drop khi tạo script. Lựa chọn này được chọn theo mặc định. General scripts for all dependent objects: Tự động tạo các script cho các đối tượng có liên quan với đối tượng đang tạo script. Include descriptive headers in the script files: Thêm lời chú thích được bổ sung vào tập tin script cho mỗi đối tượng tạo script. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  40. Giáo trình SQL Server2000 Trang 39 + Trang options: hãy chọn các tùy chọn security-related, table-related, và script file-related - Trên trang General, nhấp PreView để xem trước nội dung của script được tạo ra. Hình 24: Trang General của hộp thoại phát sinh script các đối tượng của CSDL Hình 25: Trang Formating của hộp thoại phát sinh script các đối tượng của CSDL Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  41. Giáo trình SQL Server2000 Trang 40 Hình 26: Trang Option của hộp thoại phát sinh script các đối tượng của CSDL Sử dụng Script vừa tạo: - Chuyển đến vị trí mới cần tài tạo lại CSDL/ các đối tượng CSDL. - Vào cửa sổ Query Analazer, mở tập tin Script. - Hiệu chỉnh các vị trí vật lý nếu cần. - Cho thực thi đoạn Script - Kiểm tra kết quả. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  42. Giáo trình SQL Server2000 Trang 41 BÀI 5: KIỂU DỮ LIỆU – LÀM VIỆC VỚI BẢNG 5.1 Kiểu dữ liệu (data type) Kiểu dữ liệu là một đặc tính của một cột (Column). Nó định rõ loại dữ liệu và dạng dữ liệu được nhập vào cột. Có 2 nhóm: System-Supplied datatype: Các kiểu dữ liệu cơ bản được hỗ trợ bởi SQL Server. User-defined datatype: Các kiểu dữ liệu của người dùng tự định nghĩa dựa trên các kiểu dữ liệu cơ bản. 5.1.1 System-Supplied Datatype. System-Supplied Datatype là kiểu dữ liệu cơ bản được hỗ trợ bởi SQL Server. Các đối tượng lưu chứa dữ liệu đều có một kiểu dữ liệu để lưu, các đối tượng đó có thể là - Các cột (Column) trong các bảng. - Các tham số (parameters) trong strored procedures. - Các biến (Variables) trong stored procedure, function, script, batch. - Các hàm T_SQL trả về một hoặc nhiều giá trị thuộc một kiểu dữ liệu nào đó. Ta có thể dùng các kiểu dữ liệu để tạo các ràng buộc toàn vẹn dữ liệu. Ví dụ cột TENNV thì không thể được định nghĩa với kiểu dữ liệu là Date, vì cột Date chỉ chấp nhận giá trị ngày. Khi ta gán kiểu dữ liệu cho một đối tượng nào đó thì ta cần quan tâm đến các tính chất sau: - Loại dữ liệu được chứa đựng bởi đối tượng. - Chiều dài lưu trữ giá trị hoặc là kích cở của nó. - Tính đúng của số (đối với các kiểu số). Các kiểu dữ liệu cơ bản: Loại Kiểu dữ liệu cơ sở Kích cở Vùng giá trị Mô tả Binary Binary 8 KB “0” ”9”, “a” ”f”, “A” ”F” Chứa các bit thông Varbinary 8 KB “0” ”9”, “a” ”f”, “A” ”F” tin Image 2^31 -1 bytes 2^31 –1 bytes Dữ liệu hình ảnh Character Char 255 bytes 1 8000 ký tự Ký tự hoặc chuỗi Varchar 255 bytes 1 8000 ký tự Ký tự hoặc chuỗi Text 2147483647 bytes 2^31-1 ký tự (2147483647) Ký tự hoặc chuỗi Date and Datetime 8 bytes 01/01/1753->31/12/9999 Chuỗi biểu diễn Time ngày giờ Smalldatetime 4 bytes 1/1/1900 -> 6/6/2079 Chuỗi biểu diễn ngày giờ Decimal Decimal 17 bytes -10^38-1 -> 10^38-1 Số thực Numeric 17 bytes -10^38-1 -> 10^38-1 Số thực Foating Float 8 bytes -1.79E+308 -> 1.79E+308 Số thực point Real 4 bytes -3.40E+38 ->3.40E+38 Số thực Integer Bigint 8 bytes -2^63 -> 2^63 Số nguyên Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  43. Giáo trình SQL Server2000 Trang 42 Int 4 bytes -2^31 -> 2^31-1 Số nguyên Smallint 2 bytes -2^15 -> 2^15-1 Số nguyên Tinyint 1 bytes 0 255 Số nguyên Monetary Money 8 bytes -2^63 -> 2^63-1 Dữ liệu tiền tệ Smalmoney 4 bytes -214748.3648 -> 214748.3648 Dữ liệu tiền tệ Special Bit 1 bytes 0 hoặc 1 Dữ liệu có một trong hai trạng thái 0 hoặc 1 Cursor Kiểu DL cho biến hoặc giá trị trả về của procedure, tham chiếu đến 1 mẫu tin Timestamp 8 bytes Chuỗi có dạng: Theo dõi mẫu tin 0x000000100000a90 nào bị thay đổi dữ liệu Uniqueidentifier 16 bytes Số thập lục phân SQL_variant Là kiểu dữ liệu có thể chứa bất kỳ loại dữ tùy ý của SQL Server ngoại trừ text, ntext, image, and the timestamp data type Table Unicode Nchar 4000 ký tự Ký tự hoặc chuỗi Nvarchar 4000 ký tự Ký tự hoặc chuỗi Ntext 2^30-1 ký tự Ký tự hoặc chuỗi 5.1.2 User-defined datatype. Người sử dụng có thể dựa yêu cầu cần lưu trữ và các kiểu dữ liệu cơ bản để định nghĩa ra một kiểu dữ liệu của người dùng dùng để lưu trữ một dữ liệu đặc biệt nào đó. SQL Sever cho phép bạn cải tiến các kiểu dữ liệu để đảm bảo tính nhất quán khi làm việc trong môi trường dữ liệu đa dạng trong các bảng hay các CSDL khác nhau. User-defined data type không cho phép bạn định nghĩa các kiểu dữ liệu phức hoặc có cấu trúc. Mỗi một User-defined data type có thể được định nghĩa riêng cho một CSDL hoặc cho toàn bộ các CSDL. Nếu User-defined data type được định nghĩa trong CSDL Master thì nó được dùng chung cho toàn bộ các CSDL. Các User-defined data type mà bạn tạo trong CSDL model thì sẽ có trong tất cả các CSDL mới tạo một cách tự động. Mỗi user-defined data type được lưu thành một mẫu tin trong bảng systypes. Bạn có thể tạo và xóa user-defined data type bằng các thủ tục hệ thống. Tên của kiểu dữ liệu phải tuân thủ qui tắt định danh và phải là duy nhất trong mỗi CSDL. Định nghĩa mỗi user-defined data type trong giới hạn của các kiểu dữ liệu cơ bản. Phải chỉ định mặc định là chấp nhận giá trị NULL hay NOT NULL khi đối tượng không có giá trị. Tạo một User-Defined Data Type Dùng thủ tục hệ thống sp_addtype để tạo một user-defined data type. sp_addtype type, system_data_type [,'NULL' | 'NOT NULL'] Ví dụ 1: Tạo kiểu dữ liệu tên là isbn với kiểu dữ liệu cơ bản là smallint và không chấp nhận giá trị Null Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  44. Giáo trình SQL Server2000 Trang 43 EXEC sp_addtype isbn, ‘smallint’, NOT NULL Ví dụ 2: Tạo kiểu dữ liệu tên là zipcode với kiểu dữ liệu cơ bản là char, độ dài tối đa là 10 và chấp nhận giá trị Null EXEC sp_addtype zipcode, 'char(10)', NULL Ví dụ 3: Tạo kiểu dữ liệu tên là longstring với kiểu dữ liệu cơ bản là varchar, độ dài tối đa là 63 và chấp nhận giá trị Null EXEC sp_addtype longstring, 'varchar(63)', NULL Xoá một User-Defined Data Type: dùng thủ tục hệ thống sp_droptype để xóa một user-defined data type từ bảng systypes. Một user-defined data type không thể xóa được nếu nó được tham chiếu bởi các bảng và những đối tượng khác. Sp_droptype type Ví dụ: EXEC sp_droptype isbn Xem các user-defined data types trong CSDL hiện hành: dùng thủ tục sp_help hoặc truy vấn trong information_schema.domains Ví dụ: Use SalesDB Sp_help hoặc SELECT domain_name, data_type, character_maximum_length FROM information_schema.domains ORDER BY domain_name 5.2 Làm việc với bảng của SQL Server Bảng là một đối tượng của CSDL và là nơi chứa đựng các dữ liệu về một thực thể nào đó ví dụ Khách hàng, đơn đặt hàng, tồn kho, Một bảng là một tập hợp các cột (Column). Mỗi một cột đại diện cho một thuộc tính của dữ liệu trong bảng. Khi bạn tạo một table, bạn phải chỉ định rõ tên của bảng, tên cột, kiểu dữ liệu của cột. Tên cột phải duy nhất trong một bảng, có thể dùng tên trùng nhau ở các bảng khác nhau trong cùng một CSDL. Phải chỉ rõ một kiểu dữ liệu cho mỗi cột và những lựa chọn khác nếu cần. Bạn có thể tạo: Tối đa 2 tỉ table cho mỗi CSDL. Tối đa 1024 cột trong mỗi bảng. 8060 bytes mỗi dòng (kiểu image và text dùng 16 bytes mỗi dòng) Hai cách cơ bản làm việc trên bảng: Enterprise Manager (tự nghiên cứu), câu lệnh T- SQL. 5.2.1 Tạo một bảng mới Cú pháp lệnh CREATE TABLE [ database_name.[ owner ] . | owner. ] table_name ({ Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  45. Giáo trình SQL Server2000 Trang 44 | column_name AS computed_column_expression | ::= [ CONSTRAINT constraint_name ] } | [ { PRIMARY KEY | UNIQUE } [, n ] ) [ ON { filegroup | DEFAULT } ] [ TEXTIMAGE_ON { filegroup | DEFAULT } ] ::= { column_name data_type } [ COLLATE ] [ [ DEFAULT constant_expression ] | [ IDENTITY [ (seed, increment) [ NOT FOR REPLICATION ] ] ] ] [ ROWGUIDCOL] [ ] [ n ] ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor ] [ON {filegroup | DEFAULT} ] ] ] | [ [ FOREIGN KEY ] REFERENCES ref_table [ (ref_column) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] ] | CHECK [ NOT FOR REPLICATION ] (logical_expression) } ::= [ CONSTRAINT constraint_name ] { [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] { (column [ ASC | DESC ] [, n ]) } [ WITH FILLFACTOR = fillfactor ] [ ON { filegroup | DEFAULT } ] ] | FOREIGN KEY [ (column [, n ]) ] REFERENCES ref_table [ (ref_column [, n ]) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] (search_conditions) } Ví dụ 1: USE SalesDb GO CREATE TABLE Employees Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  46. Giáo trình SQL Server2000 Trang 45 ( EmployeeID SMALLINT IDENTITY(1,1) NOT NULL, FirstName NVARCHAR(30) NOT NULL, LastName NVARCHAR(30) NOT NULL, Address1 NVARCHAR(60) NOT NULL, City NVARCHAR(15) NOT NULL, State CHAR(2) NOT NULL, Phone VARCHAR(24) NOT NULL, DOB DATETIME NOT NULL, HireDate DATETIME NOT NULL, PositionID TINYINT NOT NULL ) 5.2.2 Hiệu chỉnh bảng Thao tác cơ bản hiệu chỉnh bảng gồm thêm cột, xóa cột, thay đổi thuộc tính của cột. Để thực hiện ta sẽ dùng câu lệnh ALTER TABLE1. Thêm các cột ALTER TABLE ADD [, n] Ví dụ: Thêm cột Address vào bảng Employees ALTER TABLE Employees ADD Address2 NVARCHAR(6) NOT NULL DEFAULT 'N/A' Lưu ý: Nếu bảng đã có sẳn dữ liệu và cột thêm vào được định nghĩa là NOT NULL thì ta phải điền dữ liệu của các dòng ở cột mới thêm vào là một giá trị mặc định nào đó để tránh giá trị Null. Xóa các cột ALTER TABLE DROP COLUMN [, n] Ví dụ: ALTER TABLE Employees DROP COLUMN Address2 Lưu ý: Lệnh trên sẽ không thực hiện được vì khi tạo cột Address2 ta đã khai báo giá trị mặc định nên SQL Server đã tạo ra một đố tượng ‘Defaul Constraint’. Do đó, muốn xóa cột thì phải xóa tất cả các đối tượng liên quan đến cột cần xóa rồi mới xóa cột đó. Ví dụ: ALTER TABLE Employees DROP CONSTRAINT DF_Employees_Addre_1372D2FE ALTER TABLE Employees DROP COLUMN Address2 1 Bạn tham khảo cú pháp lệnh đầy đủ trong Books-Online Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  47. Giáo trình SQL Server2000 Trang 46 Lưu ý: DF_Employees_Addre_1372D2FE là tên của Defaul Constraint do SQL Server tự đặt. Thay đổi kiểu dữ liệu cho cột ALTER TABLE ALTER COLUMN Ví dụ: ALTER TABLE Employees ALTER Address NVARCHAR(20) Xoá toàn bộ dữ liệu trong Table TRUNCATE TABLE Ví dụ: TRUNCATE TABLE Employees Lưu ý: Nếu bảng muốn xóa là một bảng con (child table) thì bạn có thể xóa dữ liệu của nó bất kỳ lúc nào bạn thích, nhưng nếu nó là một bảng cha (Parent Table) thì bạn phải xóa dữ liệu ở bảng con trước, kế tiếp xóa khoá ngoại (Foreign key constraint) giữa 2 bảng, cuối cùng mới xóa dữ liệu ở bảng cha. 5.2.3 Xóa bảng khỏi cơ sở dữ liệu. Xóa một bảng là gỡ bỏ định nghĩa bảng và tất cả dữ liệu, cũng như các quyền (permission) định cho bảng đó. Trước khi xóa một bảng, bạn phải gỡ bỏ tất cả các phụ thuộc giữa bảng đó và những đối tượng khác. DROP TABLE Ví dụ: DROP TABLE Employees 5.3 Bảng tạm (Temporary Tables). Bạn cũng có thể tạo các bảng tạm. Các bảng tạm tương tự như các bảng bình thường, ngoại trừ việc các bảng tạm thời được chứa trong CSDL TempDb và được xóa một cách tự động khi không còn sử dụng nữa. Có hai loại bảng tạm: bảng tạm cục bộ (Local) và bảng tạm tổng thể (global). Chúng khác nhau về tên, tính hiển thị, và tính có sẳn. Bảng tạm cụ bộ: bảng tạm cục bộ có một dấu # là ký tự đầu tiên trong tên của chúng; chúng chỉ hiển thị đối với nối kết hiện hành dành cho người sử dụng, và chúng được xóa khi người dùng ngắt nối kết với các thể hiện của SQL Server. Ví dụ: Tạo bảng tạm tên là #MyLocalTempTable CREATE TABLE #MyLocalTempTable ( ID INT PRIMARY KEY, ColA VARCHAR(30) NULL, ColB VARCHAR(30) NULL, ColC VARCHAR(30) NULL ) Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  48. Giáo trình SQL Server2000 Trang 47 Nếu một bảng tạm được tạo trong tiến trình của một thủ tục thì bảng tạm đó sẽ bị xóa khi thủ tục hoàn tất. Trong trường hợp một tiến trình A gọi thủ tục B mà B có tạo một bảng tạm thì chỉ có B mới dùng được bảng tạm đó còn tiến trình A không nhìn thấy bảng tạm đó. Trong trường hợp thủ tục B được gọi cùng một lúc bởi nhiều tiến trình khác nhau thì nó sẽ có nhiều bảng tạm giống nhau khi đó SQL Server sẽ giải pháp giải quyết bằng cách thêm một hậu tố (suffix) vào tên của các bảng tạm cùng tên sao cho các bảng này có tên là khác nhau (tên của bảng tạm tối đa là 116 ký tự) Bảng tạm toàn cục: Các bảng tạm toàn cục thì có 2 dấu ## là ký tự đầu tiên trong tên của chúng; chúng hiển thị đối với bất kỳ người sử dụng nào sau khi chúng được tạo; và chúng được xóa khi tất cả những người sử dụng đang tham chiếu table ngắt kết nối với SQL Server. Ví dụ: CREATE TABLE ##MyGlocalTempTable ( ID INT PRIMARY KEY, ColA VARCHAR(30) NULL, ColB VARCHAR(30) NULL, ColC VARCHAR(30) NULL ) Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  49. Giáo trình SQL Server2000 Trang 48 BÀI 6: TOÀN VẸN DỮ LIỆU 6.1 Giới thiệu toàn vẹn dữ liệu (data Integrity) Toàn vẹn dữ liệu là đề cập đến trạng thái của tất cả các giá trị dữ liệu lưu trữ trong CSDL là đúng. Nếu dữ liệu không đúng mà đã được lưu trữ trong CSDL thì được gọi là vi phạm toàn vẹn dữ liệu. Các bảng trong CSDL của SQL Server có một số loại toàn vẹn dữ liệu khác nhau. Ví dụ định nghĩa NOT NULL, định nghĩa DEFAULT, thuộc tính IDENTITY, CONSTRAINTS, RULES, TRIGGERS, INDEXES. Xác định đúng kiểu dữ liệu của cột hoặc biến cũng là một cách thúc ép tính toàn vẹn dữ liệu. Ví dụ bạn không thể chấp nhật giá trị của cột CustomName là một giá trị dạng ngày giờ cũng như ngược lại. Để tạo hoặc thêm các ép thỏa toàn vẹn dữ liệu, chúng ta có thể thực hiện trong các câu lệnh. Định nghĩa ràng buộc: Create Table : Định nghĩa trong lúc thiết kế cấu trúc bảng. Alter Table : Định nghĩa trong khi hiệu chỉnh bảng. Để kiểm tra hoặc xem các toàn vẹn dữ liệu Sp_HelpConstraint Hoặc Bật cửa số Object Browser của Query Analyzer, mở nhánh Constraint của từng bảng. Xóa các toàn vẹn dữ liệu ALTER TABLE DROP CONSTRAINT 6.2 Tìm hiểu các toàn vẹn dữ liệu. 6.2.1 Định nghĩa NULL/NOT NULL Một giá trị không biết, chưa xác định chúng ta quy là giá trị NULL. Khả năng null của một cột được xem là khả năng của cột chấp nhận hoặc không chấp nhận giá trị null. Bạn có thể định nghĩa giá trị của một cột không là null. Một giá trị null không đồng nhất với giá trị 0, khoảng trắng, chuỗi rỗng. Null có nghĩa là không có thao tác nhập nào thực hiện được. Sự tồn tại của Null thường cho biết rằng giá trị chưa được biết rõ hay chưa xác định. Chẳng hạn, một giá trị Null trong cột price của bảng Items không có nghĩa là mặt hàng này không có giá hoặc là giá bằng 0. Nói chung, hãy tránh chấp nhận giá trị Null bởi vì chúng gây ra nhiều phức tạp hơn trong các truy vấn cũng như cập nhật dữ liệu. Việc chỉ định một cột không chấp nhận giá trị Null có thể giúp duy trì tính toàn vẹn dữ liệu. Thông thường để khai báo một cột có thể chấp nhận giá trị null, chúng ta sẽ khái báo trong khi định nghĩa hoặc hiệu chỉnh cột. Tức là dùng trong câu lệnh Create Table/Alter Table. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  50. Giáo trình SQL Server2000 Trang 49 Ví dụ: USE SalesDb GO DROP TABLE Product_Info GO CREATE TABLE Product_Info ( Product_ID smallint NOT NULL, Product_Name char(20) NOT NULL, Description char(30) NULL, Price smallmoney NOT NULL ) GO 6.2.2 Giá trị mặc định (Default Values) Trong số các đặc tính của cột, chúng ta xét thấy giá trị có thể null và giá trị mặc định. Cả hai đặc tính này định ra giá trị chèn vào một cột khi nó không được chỉ định trong câu lệnh INSERT. Các trường hợp này có thể xảy ra khi giá trị cột không được đưa vào trong câu lệnh INSERT: - Khi cột được định nghĩa như là chấp nhận giá trị NULL và không có giá trị mặc định, giá trị của cột là NULL. - Khi cột được định nghĩa không chấp nhận giá trị NULL và không có giá trị mặc định, một lỗi sẽ xảy ra. - Khi cột có một giá trị mặc định Như vậy, mỗi một cột trong một mẫu tin của bảng đều phải chứa một giá trị, ngay cả khi giá trị đó là NULL. Có những trường hợp bạn cần phải tải một hàng dữ liệu vào một bảng nhưng bạn không biết giá trị dành cho cột hay giá trị này không tồn tại. Nếu cột chấp nhận các giá trị Null, bạn có thể tải hàng có giá trị Null. Thông thường, các cột chấp nhận giá trị Null có thể không phải là các cột cần thiết nên giải pháp tốt hơn hết là ấn định một giá trị mặc nhiên (không nhập giá trị vào thì cột sẽ chấp nhận giá trị mặc định). Việc đó chính là định nghĩa DEFAULT cho cột ở những nơi thích hợp. Chẳng hạn, người ta thường chỉ định 0 là giá trị mặc định cho các cột số, hoặc N/A là giá trị mặc định cho các cột chuỗi khi không có giá trị nào được chỉ định). Khi bạn nhập vào một mẫu tin của bảng có một định nghĩa Default dành cho một cột bạn đang gián tiếp hướng dẫn SQL Server nhập một giá trị mặc định trong cột khi bạn không chỉ định một giá trị cho cột đó. SQL Server 2000 có hai cách để triển khai các giá trị mặc định cho các cột: Default Constraint và Default Object. 6.2.2.1 Default Constraint Default constraint có thể được tạo tại thời điểm tạo bảng, thêm sau khi bảng được tạo. Giá trị Default được dùng để gán giá trị hằng số cho một cột. Chỉ có một giá trị Default có thể được tạo cho một cột. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  51. Giáo trình SQL Server2000 Trang 50 Các cột TIMESTAMP, IDENTITY và ROWGUIDCOL không thể có default constraint, vì giá trị của chính đã tự động xác định. Giá trị default có thể là một hằng số; một hàm hệ thống, chẳng hạn Getdate(); một biến toàn cục, như @@trancount; hoặc một hàm do người dùng định nghĩa. Khai báo default constraint Định nghĩa Default constraing trong khi tạo bảng CREATE TABLE tablename( columnname datatype [NULL | NOT NULL] [CONSTRAINT constraintname] DEFAULT expression [, ]) Định nghĩa Default constraint đối với một bảng đã tồn tại. ALTER TABLE tablename ADD [ CONSTRAINT constraintname ] DEFAULT expression FOR columnname Ví dụ 1: Tạo bảng Events với các default constraint CREATE TABLE Events ( EventID int IDENTITY (1, 1) NOT NULL , EventType nvarchar (10) NOT NULL, EventTitle nvarchar (100) NULL , EventDescription ntext NULL , EventLanguage nvarchar (2) NULL , EventDate smalldatetime NULL DEFAULT GETDATE(), EventEndDate smalldatetime NULL DEFAULT DATEADD(day, 1, GETDATE()), EventCreator nvarchar (50) NOT NULL DEFAULT SYSTEM_USER ) Ví dụ 2: Tạo bảng Events không có default constraint CREATE TABLE Events ( EventID int IDENTITY (1, 1) NOT NULL, EventType nvarchar (10) NOT NULL, EventTitle nvarchar (100) NULL, EventDescription ntext NULL, EventLanguage nvarchar (2) NULL, EventDate smalldatetime NULL, EventEndDate smalldatetime NULL, EventCreator nvarchar (50) NOT NULL ) Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  52. Giáo trình SQL Server2000 Trang 51 Thêm các default constraint cho bảng Events ALTER TABLE Events ADD DEFAULT ‘Party’ FOR EventType ALTER TABLE Events ADD CONSTRAINT EVentDate_DF DEFAULT GETDATE() FOR EventDate Kiểm tra constraint Sp_helpConstraint Events chèn một mẫu tin trống vào bảng Events INSERT Events DEFAULT VALUES SELECT * FROM Events Kết quả Xoá default constraint ALTER TABLE Events DROP CONSTRAINT DF__Events__EventTyp__7E6CC920 ALTER TABLE Events DROP CONSTRAINT EVentDate_DF 6.2.2.2 Default Object Default object là một cách khác để định nghĩa một giá trị mặc định cho một cột. Các Default Object được gọi là “default” có đầu tiên trong phiên bản 2000 của SQL Server. Các Default không là một phần của toàn vẹn khai báo bởi vì chúng không là một của cấu trúc bảng; chúng thực sự là một phần của lược đồ CSDL. Quá trình khai báo một Default như sau: - Định nghĩa Default. - Kết Default vào cột của bảng hoặc kiểu dữ liệu. - Nếu muốn dùng Default thì sẽ gỡ bỏ khỏi cột hoặc kiểu dữ liệu. - Không cần Default nữa thì xoá khỏi CSDL Định nghĩa default CREATE DEFAULT default AS constant_expression Kết đính default với cột: sp_binddefault defaultname, tablename.columnname Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  53. Giáo trình SQL Server2000 Trang 52 Kết đính default với user-defined datatype sp_binddefault defaultname, datatypename [, futureonly] Futureonly chỉ định rằng các cột đã tồn tại có liên quan đến kiểu dữ liệu sẽ không kế thừa giá trị mặc định mới. Cờ này chỉ có thể dùng kkhi kết giá trị mặc định cho kiểu dữ liệu. Ví dụ: CREATE DEFAULT CalifDef AS ‘CA’ GO sp_bindefault ‘CalifDef’, ‘Orders.ShipRegion’ Gở bỏ kết đính một deault với cột sp_unbindefault tablename.columnname Gở bỏ kết đính một deault với User-defined datatype sp_unbindefault datatypename [, futureonly] Xóa một Default DROP DEFAULT defaultname Lưu ý: Chỉ xóa được những Defaut không được kết với cột hoặc kiểu dữ liệu. 6.2.3 Thuộc tính Identity: Identity là một thuộc tính của cột, nó không là một constraint. Tuy nhiên, Identity dùng để ràng buộc sự tồn tại dữ liệu. Một bảng chỉ có duy nhất một cột kiểu Identity. Kiểu dữ liệu của cột Identity phải là bigint, int, smallint, hoặc tinyint. Giá trị của cột Identity sẽ tự động tăng. Một cột Identity được tạo khi bảng được tạo bằng lệnh Create Table, hoặc khi hiệu chỉnh cột trong bảng bằng lệnh Alter table. Ví dụ: CREATE TABLE Table1 ( ID INT IDENTITY, FirstName VARCHAR(30) NOT NULL, LastName VARCHAR(30) NOT NULL ) GO Chèn dữ liệu vào Table1, không cần đưa giá trị cho cột ID INSERT Table1 (FirstName, LastName) VALUES (‘Minh’, ‘Thu’) Tuy nhiên, đôi khi bạn cần chỉ định giá trị cho cột có định nghĩa Identity, bạn thực hiện tuần tự các bước sau: Bật chế độ chèn dữ liệu cho cột Identity cho bảng, chèn dữ liệu, tắt chế độ chèn cho cột Identity nếu cần. Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  54. Giáo trình SQL Server2000 Trang 53 Ví dụ: SET IDENTITY_INSERT Table1 ON INSERT Table1 (ID, FirstName, LastName) VALUES (99, 'Thuy',’Tien’) SELECT * FROM Table1 6.2.4 Check Kiểu dữ liệu và giá trị default ép thỏa ràng buộc miền giá trị. Các Check giới hạn các giá trị có thể đưa vào cột. Chúng sẽ xác định các giá trị nào là hợp lệ. Một cột có thể có nhiều hơn một check constraint, chúng được lượng giá theo thứ tự được tạo. Các check constraint giới hạn các giá trị được phép bằng cách định nghĩa: - Một vùng hoặc nhiều vùng các giá trị có thể chấp nhận được. - Danh sách các giá trị. - Một mẫu định trước. Bạn có thể qui định nhiều constraint check cho một cột đơn, chúng được lượng giá theo thứ tự được tạo. 6.2.4.1 Check Constraint Check Constraint là một của định nghĩa bảng. Chúng có thể được định nghĩa trong khi tạo bảng, hiệu chỉnh bảng, và có thể xóa bất kỳ lúc nào. Chúng có thể được vô hiệu hoá (disabled) hoặc làm có hiệu lực (enabled) khi cần. Một cột có thể có nhiều hơn một check constraint. Chúng được lượng giá theo thứ tự được tạo. Check constraint: Lượng giá thành một biểu thức logic, như là biểu thức của mệnh đề WHERE. Có thể tham chiếu đến các cột khác trong cùng một bảng. Định nghĩa Check Constraint khi tạo bảng - Định nghĩa ở mức cột CREATE TABLE tablename (columname datatype [ CONSTRAINT constraintname ] CHECK [NOT FOR REPLICATION] (logical_expression) - Định nghĩa ở mức bảng CREATE TABLE tablename (columname datatype [, ], [ CONSTRAINT constraintname ] CHECK [NOT FOR REPLICATION] (logical_expression) Định nghĩa CHECK CONSTRAINT với bảng đã tồn tại ALTER TABLE tablename [ WITH CHECK | WITH NOCHECK ] ADD [ CONSTRAINT constraintname ] CHECK [NOT FOR REPLICATION] (logical_expression) Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  55. Giáo trình SQL Server2000 Trang 54 Ví dụ: ALTER TABLE Chucvu ADD CONSTRAINT NV_HSPC_Chk CHECK (HSPC>=0.1 AND HSPC<0.5) 6.2.4.2 RULE Rule là một tính năng tương thích ngược để định nghĩa các qui tắc hợp lệ mà có thể kết buộc vào các cột của bảng hoặc các kiểu dữ liệu do người dùng định nghĩa. Giống như đối tượng Default, Rule được tạo trên chính nó trước khi được kết buộc vào đối tượng khác. Để tạo một Rule, ta sẽ dùng lệnh CREATE RULE. Một cột chỉ có thể có một Rule được kết buộc, bạn có thể kết buộc một Rule với cột đã có định nghĩa Check Constraint. Cả hai đều có giá trị nhưng sẽ ưu tiên Check Constraint. Định nghĩa Rule CREATE RULE rulename AS condition_expression Kết buộc Rule vào một cột sp_bindrule rulename, tablename.columnname Kết buộc Rule vào user-defined datatype sp_bindrule rulename, datatypename [, futureonly] Futureonly chỉ định rằng các cột tồn tại sẳn mà có dùng kiểu dữ liệu này thì không kế thừa rule mới. Cờ này chỉ sử dụng khi kết Rule với kiểu dữ liệu, đối với cột thì không. Ví dụ: Tạo một rule kiểm tra ngày và kết nó vào cột OrderDate cho bảng Orders CREATE RULE ActiveDate AS @Date BETWEEN ‘01/01/70’ AND GETDATE() AS sp_bindrule ActiveDate, ‘Orders.OrderDate’ Các biểu thức dùng trong Rule giống các nguyên tắc như các điều kiện của check costraint và cũng tương tự biểu thức trong mệnh đề Where, ngoài bạn không thể tham chiếu đến các cột CDSL khác trong các Rule. Nếu bạn so sánh cú pháp của lệnh Check và biểu thức rule, hai sự khác biệt chính là: - Biểu thức Rule dùng một biến (bắt đầu với một ký hiệu @) mà sẽ được thay thế bởi giá trị cột khi được đính vào cột. - Một biểu thức Rule không thể tham chiếu đến các cột của bảng. Điểm thứ hai là hành vi khác biệt lớn nhất giữa Check constraint và các Rule: Rule chỉ tương tương với Check constraint ở mức cột. 6.2.5 Primary key Constraint Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  56. Giáo trình SQL Server2000 Trang 55 Một bảng thường có một hay nhiều cột với các giá trị riêng để nhận biết mỗi hàng trong bảng. Các cột này được gọi là khóa chính (Primary key) của bảng và bảo đảm tính toàn vẹn thực thể của bảng. SQL Server tự động tạo một chỉ mục cho bảng ứng với các cột tham gia primary key constraint. Một bảng chỉ có một constraint Primay key. Một cột nằm trong constraint Primay key không thể chấp nhận giá trị Null, trùng lắp. Bởi vì Primay key constraint bảo đảm tính duy nhất của dữ liệu nên chúng thường được ấn định cho cột nhận dạng (identity column). Nếu một Primay key constraint được ấn định trên nhiều cột, các giá trị có thể được lặp lại trong một cột, nhưng mỗi sự kết hợp giá trị từ tất cả các cột trong Primay key constraint phải là sự kết hợp duy nhất. Tạo Primary Key Constraint Có thể tạo constraint Primay key trong khi tạo table hoặc thêm constraint Primay key cho table có sẳn Để hiệu chỉnh constraint Primay key của một table thì bạn phải xóa constraint Primay key và tạo lại. Định nghĩa Primary Key Constraint khi tạo bảng: - Định nghĩa ở mức cột CREATE TABLE tablename (columname datatype [ CONSTRAINT constraintname ] PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor ] [ON {filegroup | DEFAULT} ] [, ] - Định nghĩa ở mức bảng CREATE TABLE tablename (columname datatype [, ], [CONSTRAINT constraintname ] PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] { ( column [ ASC | DESC ] [ , n ] ) } [ WITH FILLFACTOR = fillfactor ] [ ON { filegroup | DEFAULT } ] Ví dụ 1: Vừa tạo bảng vừa định nghĩa Primary Key Constraint Cách 1: Định nghĩa ở mức cột CREATE TABLE Table1 (col1 INT NOT NULL PRIMARY KEY, col2 VARCHAR(30) ) Kiểm tra constraint EXEC Sp_helpconstraint Table1 Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  57. Giáo trình SQL Server2000 Trang 56 Cách 2: Định nghĩa ở mức bảng CREATE TABLE Table2 (col1 INT NOT NULL, col2 VARCHAR(30) CONSTRAINT Table2_PK PRIMARY KEY(col1) ) EXEC Sp_helpconstraint Table1 Định nghĩa Primary Key Constraint với bảng đã tồn tại: - Định nghĩa ở mức cột ALTER TABLE tablename ADD columname datatype [CONSTRAINT constraintname ] PRIMARY KEY [ON {filegroup | DEFAULT} ] [, ] - Định nghĩa ở mức bảng ALTER TABLE tablename ADD[CONSTRAINT constraintname] PRIMARY KEY { ( column [ ASC | DESC ] [ , n ] ) } [ ON { filegroup | DEFAULT } ] Ví dụ 2: Thêm một PRIMARY KEY constraint cho một bảng có sẳn CREATE TABLE Table3 ( col1 INT NOT NULL, col2 VARCHAR(30) ) ALTER TABLE Table3 ADD CONSTRAINT table3_PK PRIMARY KEY (col1) EXEC Sp_helpconstraint Table3 Lưu ý: Khi một Primary Key Constraint được thêm vào một bảng với cột có sẳn trong bảng thì SQL Server sẽ kiểm tra dữ liệu hiện có có tuân theo các qui tắc của một Primary key hay không: Không Null, Không trùng lắp. Nếu không thỏa qui tắc thì sẽ không tạo được Primary Key Constraint. Xóa một Primary Key Constraint ALTER TABLE Table2 DROP CONSTRAINT table2_PK Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  58. Giáo trình SQL Server2000 Trang 57 Lưu ý: Không thể xóa một Primary Key constraint nếu nó được tham chiếu bởi một Foreign key Constraint của một bảng khác, muốn xóa thì phải xóa Foreign key Constraint trước. 6.2.6 Unique Constraints Unique Constraints dùng để đảm bảo không có giá trị trùng ở các cột. Mặc dù cả Unique constraint và Primary key constraint đều tuân theo tính duy nhất, nhưng hãy sử dụng Unique contarint khi bạn muốn bảo đảm tính duy nhất của: Một cột, hay sự kết hợp giữa các cột, vốn không phải là khóa chính. Một cột chấp nhận giá trị null, trong khi đó constraint primary key không thể ấn định trên cột này. Một bảng có thể có nhiều Unique constraint. Định nghĩa Unique Constraint: Định nghĩa UNIQUE CONSTRAINT khi tạo bảng - Định nghĩa ở mức cột CREATE TABLE tablename (columname datatype [ CONSTRAINT constraintname ] UNIQUE [ CLUSTERED | NONCLUSTERED ] [ON {filegroup | DEFAULT} ] [, ] - Định nghĩa ở mức bảng CREATE TABLE tablename (columname datatype [, ], [ CONSTRAINT constraintname ] UNIQUE [ CLUSTERED | NONCLUSTERED ] { ( column [ ASC | DESC ] [ , n ] ) } [ ON { filegroup | DEFAULT } ] ) Định nghĩa UNIQUE CONSTRAINT với bảng đã tồn tại - Định nghĩa ở mức cột ALTER TABLE tablename ADD columname datatype [ CONSTRAINT constraintname ] UNIQUE [ CLUSTERED | NONCLUSTERED ] [ON {filegroup | DEFAULT} ] [, ] - Định nghĩa ở mức bảng ALTER TABLE tablename ADD [ CONSTRAINT constraintname ] UNIQUE [ CLUSTERED | NONCLUSTERED ] { ( column [ ASC | DESC ] [ , n ] ) } [ ON { filegroup | DEFAULT } ] Ví dụ: Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  59. Giáo trình SQL Server2000 Trang 58 ALTER TABLE Nhanvien ADD Scmnd CHAR(15) CONSTRAINT NV_CMND_UNIQUE UNIQUE 6.2.7 Foreign Key Constraint Một khóa ngoại (Foreign key) là một cột hay sự kết hợp của nhiều cột được thiết lập và tuân theo một liên kết giữa các dữ liệu trong hai bảng. Một liên kết được tạo ra giữa hai bảng bằng cách bổ sung một hay nhiều cột có chứa giá trị khóa primay key của một bảng vào một bảng khác. Các cột này trở thành khóa ngoại của bảng thứ hai. Ta có thể quy định khóa ngoại bằng các ấn định một constraint Foreign key khi bạn tạo hay thay đổi một bảng. Một constraint Foreign key không bắt buộc phải liên kết với chỉ một constraint Foreign key trong một bảng khác, nó cũng có thể được ấn định để tham chiếu các cột của một constraint Foreign Unique trong một bảng khác. Mặc dù mục đích chính của một constraint Foreign key là điều khiển dữ liệu có thể được chứa trong bảng khóa ngoại, nhưng nó cũng điều khiển các thay đổi đối với bảng khóa chính. Chẳng hạn, nếu một mẫu tin phòng ban bị xóa ra khỏi bảng Phongban, và mã phòng ban được sử dụng cho các nhân viên trong bảng NhanVien, tính toàn vẹn trong mối quan hệ giữa hai bảng này sẽ bị phá vỡ. Các dòng nhân viên có mã phòng ban bị xóa sẽ nằm mồ côi trong bảng NhanVien mà không có liên kết với dữ liệu trong bảng PhongBan. Một constraint Foreign key sẽ ngăn chặn tình trạng mồ côi dữ liệu. Constraint Foreign key sẽ bảo đảm không cho phép bạn xóa dữ liệu trong bản chính nếu các dữ liệu này có sự liên kết với dữ liệu trong bảng khóa ngoại. Định nghĩa FOREIGN KEY CONSTRAINT khi tạo bảng - Định nghĩa ở mức cột CREATE TABLE tablename (columname datatype [ CONSTRAINT constraintname ] [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] [, ] - Định nghĩa ở mức bảng CREATE TABLE tablename (columname datatype [, ], [ CONSTRAINT constraintname ] FOREIGN KEY [ ( column [ , n ] ) ] REFERENCES ref_table [ ( ref_column [ , n ])] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] Trung Tâm CNTT - Trường ĐHCN Tp.HCM
  60. Giáo trình SQL Server2000 Trang 59 Ví dụ: CREATE TABLE PhongBan ( MaPb INT, TenPb VARCHAR(30) CONSTRAINT Pb_Pk (MaPb) PRIMARY KEY(MaPb) ) GO CREATE TABLE NhanVien ( MaNv INT, TenNV VARCHAR(30), MaPB int, MaCv int CONSTRAINT Nv_Pk PRIMARY KEY(MaNv) CONSTRAINT Nv_Fk FOREIGN KEY (MaPb) REFERENCES PhongBan(MaPb) ) GO Xem các constraint SP_HELPCONSTRAINT NHANVIEN GO Định nghĩa Foreign Key Constraint với bảng đã tồn tại: ALTER TABLE tablename [ WITH CHECK | WITH NOCHECK ] ADD [ CONSTRAINT constraintname ] FOREIGN KEY [ ( column [ , n ] ) ] REFERENCES ref_table [( ref_column[ , n ] )] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] Trong đó: WITH CHECK: trước kho tạo ràng buộc, SQL Server sẽ kiểm tra dữ liệu hiện có có vi phạm ràng buộc hay không, nếu có sẽ không tạo constraint. WITH NOCHECK: Tạo Constraint mà không cần kiểm tra dữ liệu hiện có có vi phạm ràng buộc hay không Ví dụ: CREATE TABLE ChucVu ( MaCv INT PRIMARY KEY, TenCv VARCHAR(30), HSPC Real Trung Tâm CNTT - Trường ĐHCN Tp.HCM