Bài giảng Hệ quản trị cơ sở dữ liệu - Chu Thị Hường

265 trang hapham 2820

Download

Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sở dữ liệu - Chu Thị Hường", để 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:

bai_giang_he_quan_tri_co_so_du_lieu_chu_thi_huong.pdf

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu - Chu Thị Hường

BỘ GIÁO DỤC VÀ ĐÀO TẠO TRƯỜNG ĐẠI HỌC KHOA  Bài giảng Hệ quản trị cơ sở dữ liệu GV: Chu Thị Hường
Bài giảng Hệ quản trị CSDL 1 MỤC LỤC MỤC LỤC 1 Chương 1: TỔNG QUAN VỀ HỆ QUẢN TRỊ CSDL 3 1.1. Ðịnh nghĩa: 3 1.2. Các khả năng của hệ quản trị CSDL 3 1.3. Đặc điểm của một hệ quản trị CSDL 4 1.3.1. Sự trừu tượng hoá dữ liệu: 4 1.3.2. Ngôn ngữ cơ sở dữ liệu 5 1.3.3. Xử lý câu hỏi 6 1.3.4. Quản trị giao dịch 6 1.3.5. Quản lý lưu trữ 7 1.4. Kiến trúc của một hệ quản trị CSDL 7 1.5. Các chức năng của hệ quản trị CSDL quan hệ 9 1.5.1. Các khái niệm trong mô hình dữ liệu quan hệ 9 1.5.2. Các chức năng của hệ quản trị CSDL quan hệ 11 Chương 2: CÁC CÂU LỆNH SQL CƠ BẢN 14 2.1. CÁC CÂU LỆNH ĐỊNH NGHĨA DỮ LIỆU 14 2.1.1. Lệnh CREATE 14 2.1.2. Lệnh thay thế sửa đổi ALTER 15 2.1.3. Xoá cấu trúc DROP 16 2.2. CÁC CÂU LỆNH CẬP NHẬT DỮ LIỆU 16 2.2.1. Lệnh Insert 16 2.2.2. Lệnh Update 16 2.2.2. Lệnh Delete 17 2.3. KIỂM SOÁT DỮ LIỆU 17 2.3.1. Trao quyền GRANT 17 2.3.2. Thu hồi quyền REVOTE 17 2.4. TRUY VẤN DỮ LIỆU 18 2.4.1. Tìm kiếm theo câu hỏi đơn giản 18 2.4.2. Sử dụng các hàm thư viện 19 2.4.3. Tìm kiếm nhờ các mệnh đề 20 2.4.4. Câu hỏi phức tạp 21 Chương 3: HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 24 3.1. TỔNG QUAN VỀ HỆ QUẢN TRỊ SQL SERVER 24 3.1.1. Giới thiệu hệ quản trị SQL Server 24 3.1.2.Các thành phần của SQL Server 24 3.1.2.1. Các thành phần của SQL Server 2000 24 3.1.2.2. Các thành phần của SQL Server 2005 28 3.1.3. Quản lý các dịch vụ của SQL Server 32 3.1.3.1. Quản lý các dịch vụ của SQL Server 2000 32 3.1.3.2. Quản lý các dịch vụ của SQL Server 2005 36 3.2. LÀM VIỆC VỚI CÁC ĐỐI TƯỢNG TRONG SQL SERVER 44 3.2.1. Cơ sở dữ liệu - Database 45 3.2.2.Bảng - Table 59 3.2.3. View 67 Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 2 3.2.4. Chỉ mục - Index 80 3.2.5. Lược đồ - Diagrams 92 3.3. BẢO ĐẢM DỮ LIỆU TRONG SQL SERVER 99 3.3.1. Phân quyền và bảo mật trong SQL Server 99 3.3.2. Sao lưu - phục hồi CSDL 127 Chương 4. LẬP TRÌNH TRÊN SQL SERVER 141 4.1. Giới thiệu ngôn ngữ T-SQL 141 4.1.1. Khái niệm 141 4.1.2. Phát biểu truy vấn dữ liệu nâng cao 141 4.1.3. Lập trình cấu trúc trong SQL Server 149 4.2. Các store procedure – Các thủ tục 168 4.2.1. Khái niệm 168 4.2.2. Tạo store procedure 168 4.2.3.Thay đổi, xóa, xem nội dung store procedure 174 4.3. Các store function – Các hàm 176 4.3.1. Các khái niệm 176 4.3.2. Tạo các hàm 176 4.3.3. Các ví dụ tạo các hàm 178 4.3.4.Thay đổi, xóa, xem nội dung store function 181 4.4. Trigger 182 4.4.1. Khái niệm 182 4.4.2. Tạo trigger 184 4.4.3. Các thao tác quản lý trigger 193 Chương 5. SQL SERVER VÀ LẬP TRÌNH ỨNG DỤNG 197 5.1. Mô hình kết nối ứng dụng đến SQL server 197 5.1.1. Mô hình ADO 197 5.1.2. Mô hình ADO.NET 199 5.1.3. Điểm khác nhau giữa ADO và ADO.NET 204 5.2. Các lớp SqlClient trong mô hình ADO.NET 204 5.2.1. Class SqlConnection 205 5.2.2. Class SqlCommand 208 5.2.3. Class SqlDataAdapter 213 5.2.4. Class DataSet 219 5.2.5. DataView 220 5.3. Ví dụ minh họa 223 5.3.1. CSDL trong ví dụ minh họa 224 5.3.2. Xây dựng Form nhập DSSinhVien 225 5.3.3. Xây dựng Form nhập DSLop 233 5.3.4. Xây dựng Form hiển thị danh sách sinh viên. 235 5.3.5. Xây dựng báo cáo dùng Report. 241 5.3.6. Xây dựng report dùng Crystal Report 255 Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 3 Chương 1: TỔNG QUAN VỀ HỆ QUẢN TRỊ CSDL 1.1. Ðịnh nghĩa: - Hệ quản trị cơ sở dữ liệu (Database Management System - DBMS): Là một hệ thống phần mềm cho phép tạo lập cơ sở dữ liệu và điều khiển mọi truy nhập đối với cơ sở dữ liệu đó. Trên thị trường phần mềm hiện nay ở Việt Nam đã xuất hiện khá nhiều phần mềm hệ quản trị cơ sở dữ liệu như: Microsoft Access, Foxpro, DB2, SQL Server, Oracle,.v.v - Hệ quản trị cơ sở dữ liệu quan hệ (Relation Database Management System - RDBMS) là một hệ quản trị cơ sở dữ liệu theo mô hình quan hệ. 1.2. Các khả năng của hệ quản trị CSDL Có hai khả năng chính cho phép phân biệt các hệ quản trị cơ sở dữ liệu với các kiểu hệ thống lập trình khác: i. Khả năng quản lý dữ liệu tồn tại lâu dài: đặc điểm này chỉ ra rằng có một cơ sở dữ liệu tồn tại trong một thời gian dài, nội dung của cơ sở dữ liệu này là các dữ liệu mà hệ quản trị CSDL truy nhập và quản lý. ii. Khả năng truy nhập các khối lượng dữ liệu lớn một cách hiệu quả. Ngoài hai khả năng cơ bản trên, hệ quản trị CSDL còn có các khả năng khác mà có thể thấy trong hầu hết các hệ quản trị CSDL đó là: iii. Hỗ trợ ít nhất một mô hình dữ liệu hay một sự trừu tượng toán học mà qua đó người sử dụng có thể quan sát dữ liệu. iv. Ðảm bảo tính độc lập dữ liệu hay sự bất biến của chương trình ứng dụng đối với các thay đổi về cấu trúc trong mô hình dữ liệu. v. Hỗ trợ các ngôn ngữ cao cấp nhất định cho phép người sử dụng định nghĩa cấu trúc dữ liệu, truy nhập dữ liệu và thao tác dữ liệu. vi. Quản lý giao dịch, có nghĩa là khả năng cung cấp các truy nhập đồng thời, đúng đắn đối với CSDL từ nhiều người sử dụng tại cùng một thời điểm. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 4 vii. Ðiều khiển truy nhập, có nghĩa là khả năng hạn chế truy nhập đến các dữ liệu bởi những người sử dụng không được cấp phép và khả năng kiểm tra tính đúng đắn của CSDL. viii. Phục hồi dữ liệu, có nghĩa là có khả năng phục hồi dữ liệu, không làm mất mát dữ liệu với các lỗi hệ thống. 1.3. Đặc điểm của một hệ quản trị CSDL 1.3.1. Sự trừu tượng hoá dữ liệu: Ðể cho hệ thống có thể sử dụng được, hệ quản trị CSDL phải tra cứu hay tìm kiếm dữ liệu một cách có hiệu quả. Ðiều này dẫn đến việc thiết kế các cấu trúc dữ liệu phức tạp để biểu diễn dữ liệu trong CSDL này. Người phát triển che dấu tính phức tạp này thông qua một số mức trừu tượng để đơn giản hoá các tương tác của người sử dụng đối với hệ thống. Khung nhìn 1 Khung nhìn n Mức khung nhìn Mức logic Mức vật lý Hình 1.1. Ba mức trừu tượng dữ liệu - Mức vật lý: Mức thấp nhất của sự trừu tượng mô tả dữ liệu được lưu trữ một cách thực sự như thế nào. Tại mức vật lý, các cấu trúc dữ liệu mức thấp phức tạp được mô tả chi tiết. - Mức logic: Mức cao tiếp theo của sự trừu tượng hoá mô tả những dữ liệu nào được lưu trữ và các mối quan hệ nào tồn tại giữa các dữ liệu này. Mức logic của sự trừu tượng được xác định người quản trị CSDL, cụ thể phải quyết định những thông tin gì được lưu trữ trong CSDL. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 5 - Mức khung nhìn: Mức cao nhất của sự trừu tượng mô tả chỉ một phần của toàn bộ CSDL. Mặc dù sử dụng các cấu trúc đơn giản mức logic, một số phức tập vẫn còn tồn tại do kích thước lớn của CSDL. Thực chất những người sử dụng chỉ cần truy nhập đến một phần CSDL, do vậy sự tương tác của họ với hệ thống này là đơn giản hoá và mức khung nhìn của sự trừu tượng được xác định. Hệ thống có thể được cung cấp nhiều khung nhìn đối với cùng một cơ sở dữ liệu. 1.3.2. Ngôn ngữ cơ sở dữ liệu Một hệ quản trị cơ sở dữ liệu thường cung cấp hai kiểu ngôn ngữ khác nhau đó là: ngôn ngữ mô tả sơ đồ cơ sở dữ liệu và ngôn ngữ biểu diễn các truy vấn và các cập nhật cơ sở dữ liệu. - Ngôn ngữ định nghĩa dữ liệu (Data Definition Language - DDL) + Một sơ đồ CSDL đặc tả bởi một tập các định nghĩa được biểu diễn bởi một ngôn ngữ đặc biệt được gọi là ngôn ngữ định nghĩa dữ liệu. Kết quả của việc dịch các ngôn ngữ này là một tập các bảng được lưu trữ trong một tệp đặc biệt được gọi là từ điển dữ liệu hay thư mục dữ liệu. + Một từ điển dữ liệu là một tệp chứa các siêu dữ liệu có nghĩa là các dữ liệu về dữ liệu. Tệp này được tra cứu trước khi dữ liệu thực sự được đọc hay được sửa đổi trong hệ CSDL. + Cấu trúc và các phương pháp truy nhập được sử dụng bởi hệ CSDL được đặc tả bởi một tập các định nghĩa trong một kiểu đặc biệt của DDL là ngôn ngữ định nghĩa và lưu trữ dữ liệu. - Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML): + Các yêu cầu về thao tác dữ liệu bao gồm: • Tìm kiếm thông tin được lưu trữ trong CSDL. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 6 • Thêm thông tin mới vào CSDL. • Xoá thông tin từ CSDL. • Thay đổi thông tin được lưu trữ trong CSDL. + Một ngôn ngữ thao tác dữ liệu (DML) là một ngôn ngữ cho phép người sử dụng truy nhập hay thao tác dữ liệu được tổ chức bởi mô hình dữ liệu thích hợp. Có hai kiểu ngôn ngữ thao tác dữ liệu cơ bản: • Các DML thủ tục đòi hỏi người sử dụng phải đặc tả dữ liệu nào cần tìm kiếm và tìm kiếm những dữ liệu này như thế nào. • Các DML phi thủ tục đòi hỏi người sử dụng đặc tả dữ liệu nào cần tìm kiếm mà không phải đặc tả tìm kiếm những dữ liệu này như thế nào. 1.3.3. Xử lý câu hỏi Công việc của bộ xử lý câu hỏi là biến đổi một truy vấn hay một thao tác CSDL có thể được biểu diễn ở các mức cao thành một dãy các yêu cầu đối với các dữ liệu lưu trữ trong CSDL. Thường phần khó nhất của nhiệm vụ xử lý câu hỏi là tối ưu hoá câu hỏi, có nghĩa là lựa chọn một kế hoạch tốt nhất đối với hệ thống lưu trữ để trả lời truy vấn này nhanh nhất. 1.3.4. Quản trị giao dịch Thông thường một số thao tác trên CSDL hình thành một đơn vị logic công việc. Ðiều này có nghĩa là hoặc tất cả các thao tác được thực hiện hoặc không thao tác nào được thực hiện. Hơn nữa sự thực hiện các thao tác này phải đảm bảo tính nhất quán của CSDL. Một giao dịch là một tập hợp các thao tác mà xử lý như một đơn vị không chia cắt được. Các hệ quản trị CSDL điển hình cho phép người sử dụng một hay nhiều nhóm thao tác tra cứu hay thay đổi CSDL thành một giao dịch. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 7 1.3.5. Quản lý lưu trữ Các CSDL thường đòi hỏi một khối lượng lớn không gian lưu trữ. Do bộ nhớ chính của máy tính không thể lưu trữ nhiều thông tin như vậy, các thông tin này được lưu trữ ở các thiết bị nhớ ngoài như đĩa cứng, đĩa mềm,.v.v Khi xử lý, dữ liệu cần phải được di chuyển từ đĩa từ vào bộ nhớ chính; sự di chuyển này là khá chậm so với tốc độ xử lý của bộ nhớ trung tâm, do vậy các hệ CSDL phải tổ dữ liệu vật lý sao cho tốt, tối thiểu hoá số yêu cầu chuyển dữ liệu giữa đĩa từ vào bộ nhớ chính. 1.4. Kiến trúc của một hệ quản trị CSDL Chúng ta sẽ phác thảo kiến trúc và thấy cách thức của một hệ quản trị CSDL điển hình. Ta có sơ đồ kiến trúc hình 1.2: Hình 1.2. Các thành phần chính của hệ quản trị CSDL - Dữ liệu, siêu dữ liệu: Ðáy kiết trúc là thiết bị nhớ ngoài lưu trữ dữ liệu và siêu dữ liệu. Trong phần này không chỉ chứa dữ liệu được trữ trong CSDL mà chứa cả các siêu dữ liệu, tức là thông tin cấu trúc của CSDL. Ví dụ: Trong Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 8 hệ quản trị cơ sở dữ liệu quan hệ, các siêu dữ liệu bao gồm các tên của các quan hệ, tên các thuộc tính của các quan hệ, và các kiểu dữ liệu đối với các thuộc tính này. - Bộ quản lý lưu trữ: Nhiệm vụ của bộ quản lý lưu trữ là lấy ra các thông tin được yêu cầu từ những thiết bị lưu trữ dữ liệu và thay đổi những thông tin này khi được yêu cầu bởi các mức trên nó của hệ thống. - Bộ xử lý câu hỏi: Bộ xử lý câu hỏi điều khiển không chỉ các câu hỏi mà cả các yêu cầu thay đổi dữ liệu hay siêu dữ liệu. Nhiệm vụ của nó là tìm ra cách tốt nhất một thao tác được yêu cầu và phát ra lệnh đối với bộ quản lý lưu trữ và thực thi thao tác đó. - Bộ quản trị giao dịch: Bộ quản trị giao dịch có trách nhiệm đảm bảo tính toàn vẹn của hệ thống. Nó phải đảm bảo rằng một số thao tác thực hiện đồng thời không cản trở mỗi thao tác khác và hệ thống không mất dữ liệu thậm chí cả khi lỗi hệ thống xảy ra. + Nó tương tác với bộ xử lý câu hỏi, do vậy nó phải biết dữ liệu nào được thao tác bởi các thao tác hiện thời để tránh sự đụng độ giữa các thao tác và cần thiết nó có thể làm trễ một số truy vấn nhất định hay một số thao tác cập nhật để đụng độ không thể xảy ra. + Nó tương tác với bộ quản lý lưu trữ bởi vì các sơ đồ đối với việc bảo vệ dữ liệu thường kéo theo việc lưu trữ một nhật ký các thay đổi đối với dữ liệu. Hơn nữa, việc sắp thứ tự các thao tác một cách thực sự được nhật ký này sẽ chứa trong một bản ghi đối với mỗi thay đổi khi gặp lỗi hệ thống, các thay đổi chưa được ghi vào đĩa có thể được thực hiện lại. - Các kiểu thao tác đối với hệ quản trị CSDL: Tại đỉnh kiến trúc, ta thấy có 3 kiểu thao tác: + Các truy vấn: Ðây là các thao tác hỏi đáp về dữ liệu được lưu trữ trong CSDL. Chúng được sinh ra theo hai cách sau: Thông qua giao diện truy vấn chung. Ví dụ: Hệ quản trị CSDL quan hệ cho phép người sử dụng nhập các câu Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 9 lệnh truy vấn SQL mà nó được chuyển qua bộ xử lý câu hỏi và được trả lời. Thông qua các giao diện chương trình ứng dụng: Một hệ quản trị CSDL điển hình cho phép người lập trình viết các chương trình ứng dụng gọi đến hệ quản trị CSDL này và truy vấn CSDL. + Các cập nhật dữ liệu: Ðây là các thao tác thay đổi dữ liệu như xoá, sửa dữ liệu trong CSDL. Giống như các truy vấn, chúng có thể được phát ra thông qua giao diện chung hoặc thông qua giao diện của chương trình. + Các thay đổi sơ đồ: Các lệnh này thường được phát bởi một người sử dụng được cấp phép, thường là những người quản trị CSDL mới được phép thay đổi sơ đồ của CSDL hay tạo lập một CSDL mới. 1.5. Các chức năng của hệ quản trị CSDL quan hệ 1.5.1. Các khái niệm trong mô hình dữ liệu quan hệ - Miền (domain): là một tập các giá trị hoặc các đối tượng. - Thực thể: Thực thể là một đối tượng cụ thể hay trừu tượng trong thế giới thực mà nó tồn tại và có thể phân biệt được với các đối tượng khác. Ví dụ: Bạn Nguyễn Văn A là một thực thể cụ thể. Hay Sinh viên cũng là một thực thể, thực thể trừu tượng. - Thuộc tính (Attribute): Là tính chất của thực thể. + Các thực thể có các đặc tính, được gọi là các thuộc tính. Nó kết hợp với một thực thể trong tập thực thể từ miền giá trị của thuộc tính. Thông thường, miền giá trị của một thuộc tính là một tập các số nguyên, các số thực, hay các xâu ký tự. + Một thuộc tính hay một tập thuộc tính mà giá trị của nó xác định duy nhất mỗi thực thể trong tập các thực thể được gọi là khoá đối với tập thực thể này. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 10 + Mỗi một thuộc tính nhận tập số các giá trị nhất định được gọi là domain của thuộc tính đó. - Một quan hệ (Relation): Định nghĩa một cách đơn giản, một quan hệ là một bảng dữ liệu có các cột là các thuộc tính và các hàng là các bộ dữ liệu cụ thể của quan hệ. - Các liên kết: Một liên kết là một sự kết hợp giữa một số thực thể (hay quan hệ). Ví dụ: Mối liên kết giữa phòng ban và nhân viên thể hiện: Một nhân viên A sẽ thuộc một phòng ban B nào đó. + Các liên kết một – một: đây là dạng liên kết đơn giản, liên kết trên hai thực thể là một – một, có nghĩa là mỗi thực thể trong tập thực thể này có nhiều nhất một thực thể trong tập thực thể kia kết hợp với nó và ngược lại. + Các liên kết một – nhiều: Trong một liên kết một – nhiều, một thực thể trong tập thực thể A được kết hợp với không hay nhiều thực thể trong tập thực thể B. Nhưng mỗi thực thể trong tập thực thể B được kết hợp với nhiều nhất một thực thể trong tập thực thể A. + Các liên kết nhiều – nhiều: Ðây là dạng liên kết mà mỗi thực thể trong tập thực thể này có thể liên kết với không hay nhiều thực thể trong tập thực thể kia và ngược lại. Ví dụ 1.1. Các mối liên kết giữa các thực thể: LOP(MaLop, TenLop, Khoa), SINHVIEN(MaSV, Hoten, NgSinh, MaLop), MONHOC(MaMon, TenM, SDVHT) và KETQUA (MaSV, MaMon, Diem) Ta có mối quan hệ giữa các thực thể đó là: LOP SINHVIEN KETQUA MONHOC MaLop MaSV MaSV MaMon TenLop Hoten MaMon TenM Khoa NgSinh Diem SDVHT MaLop Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 11 - Mô hình dữ liệu quan hệ: Làm việc trên bảng hay trên quan hệ trong đó: Mỗi cột là một thuộc tính, mỗi dòng là một bộ (một bản ghi). + Các ưu điểm của mô hình dữ liệu quan hệ o Cấu trúc dữ liệu dễ dùng, không cần hiểu biết sâu về kỹ thuật cài đặt. o Cải thiện tính độc lập dữ liệu và chương trình. o Cung cấp ngôn ngữ thao tác phi thủ tục. o Tối ưu hoá cách truy xuất dữ liệu. o Tăng tính bảo mật và toàn vẹn dữ liệu. o Cung cấp các phương pháp thiết kế có hệ thống. Và mở ra cho nhiều loại ứng dụng (lớn và nhỏ). + Khoá của quan hệ: o Khoá của quan hệ (key): Là tập các thuộc tính dùng để phân biệt hai bộ bất kỳ trong quan hệ. o Khoá ngoại của quan hệ (Foreign Key): Một thuộc tính được gọi là khoá ngoại của quan hệ nếu nó là thuộc tính không khoá của quan hệ này nhưng là thuộc tính khoá của quan hệ khác. 1.5.2. Các chức năng của hệ quản trị CSDL quan hệ Các chức năng của hệ quản trị CSDL quan hệ có thể được phân thành các tầng chức năng như hình 1.3: - Tầng giao diện (Interface layer): Quản lý giao diện với các ứng dụng. Các chương trình ứng dụng CSDL được thực hiện trên các khung nhìn (view) của CSDL. Ðối với một ứng dụng, khung nhìn rất có ích cho việc biểu diễn một hình ảnh cụ thể về CSDL (được dùng chung bởi nhiều ứng dụng). Khung nhìn quan hệ là một quan hệ ảo, được dẫn xuất từ các quan hệ cơ sở (base relation) bằng cách áp dụng các phép toán đại số quan hệ. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 12 Quản lý khung nhìn bao gồm việc phiên dịch câu vấn tin người dùng trên dữ liệu ngoài thành dữ liệu khái niệm. Nếu câu vấn tin của người dùng được diễn tả bằng các phép toán quan hệ, câu vấn tin được áp dụng cho dữ liệu khái niệm vẫn giữ nguyên dạng này. - Tầng điều khiển (Control Layer): chịu trách nhiệm điều khiển câu vấn tin bằng cách đưa thêm các vị từ toàn vẹn ngữ nghĩa và các vị từ cấp quyền. - Tầng xử lý vấn tin (Query processing layer): chịu trách nhiệm ánh xạ câu vấn tin thành chuỗi thao tác đã được tối ưu ở mức thấp hơn. Tầng này liên quan đến vấn đề hiệu năng. Nó phân rã câu vấn tin thành một cây biểu thị các phép toán đại số quan hệ và thử tìm ra một thứ tự “tối ưu” cho các phép toán này. Kết xuất của tầng này là câu vấn tin được diễn tả bằng đại số quan hệ hoặc một dạng mã ở mức thấp. - Tầng thực thi (Execution layer): Có trách nhiệm hướng dẫn việc thực hiện các hoạch định truy xuất, bao gồm việc quản lý giao dịch (uỷ thác, tái khởi động) và động bộ hoá các phép đại số quan hệ. Nó thông dịch các phép toán đại số quan hệ bằng cách gọi tầng truy xuất dữ liệu qua các yêu cầu truy xuất và cập nhật. - Tầng truy xuất dữ liệu (data access layer): Quản lý các cấu trúc dữ liệu dùng để cài đặt các quan hệ (tập tin, chỉ mục). Nó quản lý các vùng đệm bằng cách lưu tạm các dữ liệu thường được truy xuất đến nhiều nhất. Sử dụng tầng này làm giảm thiểu việc truy xuất đến đĩa. - Tầng duy trì nhất quán (Consistency layer): chịu trách nhiệm điều khiển các hoạt động đồng thời và việc ghi vào nhật ký các yêu cầu cật nhật. Tầng này cũng cho phép khôi phục lại giao dịch, hệ thống và thiết bị sau khi bị sự cố. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 13 Hình 1.3. Các chức năng của hệ quản trị CSDL quan hệ Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 14 Chương 2: CÁC CÂU LỆNH SQL CƠ BẢN Ngôn ngữ SQL (Structured Query Language) là ngôn ngữ truy vấn có cấu trúc, dùng để thao tác với dữ liệu trong cơ sở dữ liệu cũng như tạo và thay đổi cấu trúc của các cơ sở dữ liệu. Trong chương này ta sẽ trình bày một số câu lệnh SQL cơ bản. 2.1. CÁC CÂU LỆNH ĐỊNH NGHĨA DỮ LIỆU 2.1.1. Lệnh CREATE - Ý nghĩa: Lệnh CREATE dùng để tạo các đối tượng cơ sở dữ liệu như các bảng, các view, các tệp chỉ số .v.v - Cú pháp: + CREATE TABLE ( ) + CREATE VIEW ( ) AS Q; với Q là một khối câu lệnh SELECT định nghĩa khung nhìn (view). + CREATE [UNIQUE] INDEX ON (Tên cột [ASC|DESC]) - Một số kiểu dữ liệu: Integer - số nguyên; float- dấu phảy động; char - ký tự, datetime- ngày tháng, boolean, Ví dụ 2.1. Sử dụng câu lệnh CREATE. + CREATE TABLE S (S# Integer NOT NULL, SNAME Char(30), STATUS Integer, CITY Char(50)) PRIMARY KEY (S#); + CREATE VIEW vieS (S# Integer NOT NULL, SNAME Char(30)) AS SELECT S#, SNAME FROM S; + CREATE TABLE PHONGBAN (MaPB Char(5) NOT NULL, TenPB Char(30)) PRIMARY KEY (MaPB); Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 15 + CREATE TABLE NHANVIEN (MaNV Char(5) NOT NULL, TenNV Char(30), Ngaysinh date, MaPB Char(5)) PRIMARY KEY (MaNV) FOREIGN KEY (MaPB) REFERENCES TO PHONGBAN; + CREATE INDEX index1 ON SP(S# ASC, P# DESC) 2.1.2. Lệnh thay thế sửa đổi ALTER - Ý nghĩa: Dùng để thay đổi cấu trúc lược đồ của các đối tượng CSDL. - Cú pháp: + ALTER TABLE Các lệnh trên cột có thể là: • Xóa một cột: Delete • Thêm một cột: Add • Thay đổi tên cột: Change column To • Xóa khóa chính: Drop PRIMARY KEY • Xóa khóa ngoại: Drop FOREIGN KEY • Thiết lập khóa chính: PRIMARY KEY (Tên cột) • Thiết lập khóa ngoại: FOREIGN KEY (Tên cột) REFERENCES TO + ALTER VIEW ( ) AS Q; với Q là một khối câu lệnh SELECT định nghĩa khung nhìn (view). Ví dụ 2.2. Thay đổi cấu trúc của bảng NHANVIEN ALTER TABLE NHANVIEN Add Quequan char(50); ALTER TABLE NHANVIEN Delete Ngaysinh; Ví dụ 2.3. Thay đổi khung nhìn vieS CREATE VIEW vieS AS SELECT S#, SNAME, CITY FROM S Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 16 2.1.3. Xoá cấu trúc DROP - Ý nghĩa: Dùng để xóa các đối tượng cơ sở dữ liệu như Table, View, Index, .v.v - Cú pháp: DROP TABLE DROP VIEW DROP INDEX 2.2. CÁC CÂU LỆNH CẬP NHẬT DỮ LIỆU 2.2.1. Lệnh Insert - Ý nghĩa: Dùng để chèn một hàng hoặc một số hàng cho bảng. - Cú pháp: + INSERT INTO (Danh sách các cột) VALUES (Danh sách các giá trị) hoặc + INSERT INTO (Danh sách các cột) (Các câu hỏi con); Ví dụ 2.4. Chèn dữ liệu vào bảng S. INSERT INTO S (S#, SNAME, STATUS) VALUES (s1, Smith, 20, Paris); INSERT INTO S SELECT * FROM W WHERE CITY="Paris"; 2.2.2. Lệnh Update - Ý nghĩa: Dùng để sửa đổi dữ liệu. - Cú pháp: UPDATE SET Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 17 [WHERE ] Ví dụ 2.5. Sử dụng lệnh Update UPDATE SINHVIEN SET TenSV="Nguyễn Thị Hạnh" Where MaSV="20042390" UPDATE HangHoa SET Dongia=Dongia*1.1 Where TenHH IS LIKE 'Bia %' and Dongia WHERE Ví dụ 2.6. Xoá tất cả các hàng trong bảng KETQUA có trường Diem ON TO [WITH GRANT OPITION] Các quyền có thể trao là: All, Select, update, delete, insert, index, alter, read, write, User có thể là: Public, tên một user cụ thể, - Chú ý: Nếu được trao quyền với chỉ định WITH GRANT OPITION thì anh ta có thể trao lại quyền ấy cho người khác. Ví dụ 2.7. Trao quyền Select cho acount Lannt GRANT Select ON SINHVIEN TO Lannt WITH GRANT OPITION 2.3.2. Thu hồi quyền REVOTE Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 18 - Ý nghĩa: Dùng để thu hồi quyền của một acount nào đó. - Cú pháp: REVOTE ON FROM Ví dụ 2.8. Thu hồi quyền Select của acount Lannt REVOTE Select ON SINHVIEN FROM Lannt; 2.4. TRUY VẤN DỮ LIỆU Khối câu lệnh phổ dụng: SELECT - FROM – WHERE. Ta có thể sử dụng theo cú pháp chung như sau: SELECT [*| DISTINCT] ]> FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY [ASC/DESC]] 2.4.1. Tìm kiếm theo câu hỏi đơn giản - Tìm kiếm đơn giản: + Nếu xuất hiện giá trị * nghĩa là xem toàn bộ các cột của bảng. Select * From SINHVIEN; + Nếu sử dụng DISTINCT thì sẽ lấy giá trị đại diện. Select Distinct S#, P# From SP; - Xử lý xâu: dùng toán tử [NOT] LIKE + Dùng dấu gạch dưới để thay cho một ký tự. + Dùng dấu % để thay cho một dãy các ký tự tuỳ ý. Ví dụ 2.9. Cho bảng hồ sơ sinh viên HOSOSV(MaSV, Hodem, TenSV, Ngaysinh, MaLop). Hãy cho biết mã và họ tên sinh viên có hai chữ đầu là 'Ba' Select MaSV, Hodem+TenSV as Hoten From HOSOSV Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 19 Where TenSV like 'Ba%' Ví dụ 2.10. Cho bảng hồ sơ sinh viên HOSOSV(MaSV, Hodem, TenSV, Ngaysinh, MaLop). Hãy cho biết mã và họ tên sinh viên có không có hai chữ đầu là 'Ba' Select MaSV, Hodem+TenSV as Hoten From HOSOSV Where TenSV like 'Ba%' - Sử dụng Between và IN để xác định phạm vi: Ví dụ 2.11. Cho bảng thông tin sách mượn SACHMUON(MaBD, MaSach, NgayMuon, NgayTra). Hãy cho biết mã các bạn đọc mượn sách của thư viện trong khoảng ngày {1/1/2008} và {31/3/2008} Select MaBanĐoc From SACHMUON Where NgayMuon Between {1/1/2000} and {31/3/2000} 2.4.2. Sử dụng các hàm thư viện Các hàm thư viện thực hiện các thao tác như thống kê dữ liệu, tính toán dữ liệu có sẵn như: • Count(): Dùng để đếm các bảng ghi, • Max(): Trả về giá trị lớn nhất của một tập hợp các giá trị, • Min(): Trả về giá trị lớn nhất của một tập hợp các giá trị, • Sum(): Trả về tổng giá trị của một tập hợp các giá trị, • Avg():Trả về giá trị trung bình của một tập hợp các giá trị, Ví dụ 2.12.: Cho bảng DIEM(MaSV, MaMH, DiemL1, DiemL2). Hãy xem sinh viên có mã SV061001 đã tham gia thi bao nhiêu môn: Select Count(MaMH) AS Tongso From DIEM where MaSV='SV061001'; Ví dụ 2.13: Cho biết điểm thi cao nhất lần 1 của môn có mã '03AB' Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 20 Select Max(DiemL1) as DiemCN From DIEM Where MaMH='03AB'; Ví dụ 2.14. Cho biết chênh lệnh giữa điểm thi cao nhất và thấp nhất của môn có mã môn học là '03AB' Select (Max(DiemL1) - Min (DiemL1)) As Chenh_Lenh From DIEM where MaMT='03AB'; 2.4.3. Tìm kiếm nhờ các mệnh đề - Sử dụng phân nhóm GROUP BY: Mệnh đề GROUP BY được sử dụng để tạo hiệu quả sắp xếp và tính toán theo từng phân nhóm. Ví dụ 2.15. Cho biết tình hình thi của từng sinh viên: Select MaSV, MaMH, DiemL1, DiemL2 From DIEM Group By MaSV - Sử dụng HAVING: Mệnh đề HAVING dùng để đặt điều kiện lọc cho các phân nhóm con. Ví dụ 2.16. Cho bảng mặt hàng đã được cung cấp SP(S#, P#, QTY). Tìm mã những nhà cung cấp cung cấp ít nhất 2 mặt hàng: Select S# From SP Group By S# Having Count(Distinct P#)>=2; Ví dụ 2 .17. Tìm mã các sinh viên không có môn thi nào dưới 5 Select MaSV From DIEM Group By MaSV Having Min(Diem)>=5; Chú ý: Having đi sau Group By để đặt điều kiện chọn lọc ra những phân nhóm thoả mãn điều kiện sau Having. Nếu không có từ khoá Group By thì Having sẽ tác động trên toàn bảng coi như một phân nhóm duy nhất. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 21 - Sử dụng Order By: Được sử dụng để tạo hiệu quả sắp xếp dữ liệu. Ta có thể sắp xếp theo chiều tăng (ASC) hoặc giảm (DESC). + Ta có thể tác động sắp xếp lại trên từng phân nhóm bởi Order By. Ví dụ 2.18. Cho biết tình hình thi lần 1 của mỗi sinh viên sao cho kết quả điểm thi được sắp xếp giảm dần. SELECT MaSV, MaMT, ĐiemL1 GROUP BY MaSV ORDER BY Diem DESC - Chú ý: Tương tự HAVING, nếu trước Order by không có Group By thì hiệu quả sắp xếp dữ liệu sẽ tác động trên toàn bảng và bảng được coi như một phân nhóm chính. 2.4.4. Câu hỏi phức tạp Khi thực hiện các truy vấn làm việc với dữ liệu từ 2 bảng trở nên thì điều kiện xử lý phức tạp hơn. - Tự kết nối: Ví dụ 2.19. Kiểm tra bảng kết quả thi KETQUA(SoBD, MaMT, Diem) có bị nhập trùng hay không? Nghĩa là nhập trùng MaSV, MaMT nhưng điểm thi lại khác nhau (nhập 2 lần). SELECT a.SoBD, a.MaMT, a.Diem, b.Diem From KETQUA a, KETQUA b Where (a.SoBD=b.SoBD) and (a.MaMT=b.MaMT) and (a.Diem>b.Diem) - Kết nối nhiều bảng: Ví dụ 2.20. Cho biết kết quả thi môn Toán của các sinh viên. + CREATE VIEW MaToan AS SELECT MaMT From MONTHI Where TenMon='Toán' + CREATE VIEW TAM AS SELECT a.SoBD, a.Diem From KETQUA a, MaToan b Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 22 Where a.MaMT=b.MaMT; + SELECT a.SoBD, TenSV, a.Diem From Tam a, THISINH b Where a.SoBD=b.SoBD Ví dụ 2.21. Liên kết nhiều bảng authors, titleauthor và title. SELECT au_lname, au_fname, title, price FROM authors JOIN titleauthor ON authors.au_id = titleauthor.au_id JOIN titles ON titleauthor.title_id = titles.title_id ORDER BY au_lname, au_fname - Ánh xạ lồng Ví dụ 2.22. Cho các quan hệ: S(S#, SNAME, STATUS, CITY) SP(S#, P#, QTY) P(P#, PNAME, COLOR, WEIGH) Hãy cho biết mã và tên các hãng có bán sản phẩm màu đỏ. SELECT S#, SNAME From S Where S# IN (SELECT S# From SP Where P# IN (Select P# From P Where COLOR='Red')); - Sử dụng các lượng từ: EXISTS, ANY, ALL, Ví dụ 2.23. Cho các bảng trong ví dụ 2.22. Tìm các nhà cung cấp đã cung cấp ít nhất một mặt hàng nào đó. SELECT * From S Where EXISTS (SELECT * From SP Where SP.S# =S.S#); Ta có thể thay thế bằng câu lệnh: SELECT * From S Where 0 < (SELECT Count(*) From SP Where SP.S#=S.S#); Ví dụ 2.24. Tìm tên những mặt hàng có mã số mặt hàng mà mặt hàng nào đó mà hãng S1 đã bán. SELECT PNAME From P Where S# = ANY Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 23 (SELECT P# From SP Where S#='S1'); Ví dụ 2.25. Tìm những hãng cung cấp số lượng một lần một mặt hàng nào đó > số lượng mỗi lần của các hãng cung cấp. SELECT S From SP Where QTY>= ALL (SELECT QTY From SP ); hay SELECT S From SP Where QTY= (SELECT Max(QTY) From SP ); Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 24 Chương 3: HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 3.1. TỔNG QUAN VỀ HỆ QUẢN TRỊ SQL SERVER 3.1.1. Giới thiệu hệ quản trị SQL Server Microsoft SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ (Relation Database Management System - RDBMS), cung cấp cách tổ chức dữ liệu bằng cách lưu chúng vào các bảng. Dữ liệu quan hệ được lưu trữ trong các bảng và các quan hệ đó được định nghĩa giữa các bảng với nhau. Người dùng truy cập dữ liệu trên Server thông qua ứng dụng. Người quản trị CSDL truy cập Server trực tiếp để thực hiện các chức năng cấu hình, quản trị và thực hiện các thao tác bảo trì CSDL. Ngoài ra, SQL Server là một CSDL có khả năng mở rộng, nghĩa là chúng có thể lưu một lượng lớn dữ liệu và hỗ trợ tính năng cho phép nhiều người dùng truy cập dữ liệu đồng thời. Các phiên bản của SQL Server phổ biến hiện này trên thị trường là SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008. Trong giáo trình này, tác giả giới thiệu với các bạn trên hai phiên bản SQL Server 2000, SQL Server 2005. 3.1.2.Các thành phần của SQL Server 3.1.2.1. Các thành phần của SQL Server 2000 SQL Server cung cấp một số loại thành phần khác nhau: - Nhân của nó là các thành phần server, các thành phần này được thực hiện như windows 32 bit. - Các công cụ đồ họa dựa trên client và các dòng tiện ích phục vụ cho công tác quản trị. Các công cụ và tiện ích này sử dụng sử dụng các thành phần giao tiếp client do SQL Server 2000 cung cấp. Các thành phần giao tiếp cung cấp các cách khác nhau mà Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 25 trong đó ứng dụng client có thể truy cập dữ liệu thông qua các thành phần server. a) Các thành phần server Các thành phần server của SQL Server 2000 thường được thực hiện như các dịch vụ Windows 32 bit. Do đó các dịch vụ của SQL Server và SQL Server Agent có thể chạy như các ứng dụng độc lập trên bất kỳ nền tảng nào được hỗ trợ hệ điều hành Windows. Các thành phần Server được mô tả trong bảng 3.1: Bảng 3.1. Các thành phần server của SQL Server 2000 Thành phần server Chức năng Dịch vụ MSSQLServer thực thi cỗ máy CSDL SQL Dịch vụ SQL Server Server 2000. Có một dịch vụ này cho mỗi thể hiện của SQL Server 2000. Dịch vụ về các dịch MSSQLServerOLAPService thực thi các dịch vụ vụ phân tích của phân tích của SQL Server 2000. Chỉ có một dịch vụ SQL Server 2000 không liên quan đến số thể hiện của SQL Server 2000. Dịch vụ SQL Server Dịch vụ SQLServerAgent thực thi các tác nhân chạy Agent các tác vụ quản trị đã được định thời gian biểu của SQL Server 2000. Dịch vụ tìm kiếm Dịch vụ tìm kiếm Microsoft thực thi cỗ máy tìm kiếm toàn văn bản. Chỉ có một dịch vụ, không liên quan đến số thể hiện của SQL Server 2000. Dịch vụ MS DTC - Thành phần điều phối giao dịch phân tán, quản lý Distributed các giao dịch phân tán giữa các thể hiện của SQL Transaction Server 2000. Chỉ có một dịch vụ, không liên quan Coordinator đến số thể hiện của SQL Server 2000. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 26 SQL Server 2000 cung cấp các giao diện đồ họa giúp cho người sử dụng sử dụng các dịch vụ của SQL Server 2000 (Hình 3.1. và Bảng 3.2). Hình 3.1. Các giao diện đồ họa của SQL Server 2000. Bảng 3.2 Các công cụ giao diện đồ họa của SQL Server 2000. Công cụ Chức năng SQL Server Đây là công cụ quản trị CSDL server chính, nó cung Enterprise Manager cấp một giao tiếp với người dùng Microsoft Management Console (MMC). SQL Query Analyzer Dùng để tạo và quản lý các đối tượng CSDL và kiểm tra các phát biểu Transact-SQL, các bó lệnh và các script một cách tương tác. SQL Profiler Giám sát và ghi nhận các sự kiện SQL Server 2000 đã chọn để phân tích và xem lại. SQL Server Service Ứng dụng nằm trên thanh task bar của Windows Manager được dùng để chạy, tạm dừng hoặc thay đổi các dịch vụ SQL Server 2000. Client Network Được dùng để quản lý Net-Libraries của client và Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 27 Utility định nghĩa các bí danh server. Server Network Dùng để quản lý Net-Libraries của server bao gồm Utility thiết lập mã hóa SSL. b) Các thành phần giao tiếp Client. Người dùng truy cập SQL Server 2000 thông qua các ứng dụng client, SQL Server 2000 cung cấp hai kiểu ứng dụng client chính: - Các ứng dụng CSDL quan hệ, là kiểu ứng dụng truyền thống dùng môi trường client/server 2 lớp. Các ứng dụng này gửi các phát biểu T-SQL đến cỗ máy CSDL quan hệ và nhận kết quả trả về như tập kết quả quan hệ. - Các ứng dụng Internet, chúng là thành phần của nền tảng Microsoft.NET. Chúng gửi các phát biểu T-SQL hoặc các truy vấn Xpath tới cỗ máy CSDL quan hệ và nhận về kết quả dạng XML. Các tiện ích dòng lệnh thường được sử dụng do SQL Server 2000 cung cấp cho trong bảng 3.3. Bảng 3.3 Các tiện ích dòng lệnh của SQL Server 2000. Tiện ích Chức năng Osql Tiện ích này cho phép truy vấn tương tác một thể hiện của SQL Server 2000 bằng các phát biểu T-SQL, các thủ tục và các script. Scm (Server Dùng để chạy, dừng, tạm dừng, cài đặt, xóa hoặc thay Control Manager) đổi các dịch vụ SQL Server 2000. Sqldiag Tiện ích này thu thập và lưu trữ các thông tin chuẩn đoán để xử lý và đơn giản hóa thông tin thu thập bởi dịch vụ hỗ trợ sản phẩm Microsoft. Bcp Tiện ích này sao chép dữ liệu giữa một thể hiện của SQL Server 2000 và tập tin dữ liệu theo định dạng của người dùng. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 28 Dtsrun Tiện ích này thực thi các gói được tạo bởi DTS. Sqlmaint Tiện ích này thực thi các hoạt động bảo trì trên một hoặc nhiều CSDL. Những hoạt động bao gồm việc kiểm tra tính nhất quán dữ liệu, sao lưu tập tin dữ liệu và tập tin giao dịch, cập nhật các thống kê phân tán, xây dựng lại các chỉ mục. 3.1.2.2. Các thành phần của SQL Server 2005 SQL Server 2005 nâng cao hiệu năng, độ tin cậy, khả năng lập trình đơn giản và giao diện dễ sử dụng hơn so với SQL Server 2000. SQL Server 2005 tập trung vào khả năng xử lý giao dịch trực tuyến (online transaction processing - OLTP), ứng dụng thương mại điện tử (e- ecommerce) và kho dữ liệu (data warehousing). Ngoài ra những cải tiến quan trọng trong SQL Server 2005 là thêm các dịch vụ mới như: dịch vụ báo cáo (reporting service), service broker và sự thay đổi đáng kể trong cỗ máy cơ sở dữ liệu. a) Các phiên bản của SQL Server 2005: Trước khi đi vào các thành phần của SQL Server 2005, ta xét các phiên bản của SQL Server 2005. SQL Server 2005 được sử dụng rộng rãi cho nhiều đối tượng khác nhau nên Microsoft cung cấp nhiều phiên bản khác nhau cho phù hợp với các yêu cầu về chi phí, thời gian thực hiện, của các tổ chức, cá nhân. Năm phiên bản của SQL Server 2005 là: + Microsoft SQL Server 2005 Enterprise Edition + Microsoft SQL Server 2005 Standard Edition + Microsoft SQL Server 2005 Workgroup Edition + Microsoft SQL Server 2005 Developer Edition + Microsoft SQL Server 2005 Express Edition Hầu hết các tổ chức đều chọn trong ba phiên bản SQL Server 2005 Enterprise Edition, SQL Server 2005 Standard Edition, và SQL Server 2005 Workgroup Edition. Các tổ chức chọn một trong ba phiên bản này với lý do là Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 29 chỉ có các phiên bản Enterprise, Standard, và Workgroup được cài đặt và sử dụng trong môi trường server phục vụ cho hoạt động thực tế. + SQL Server 2005 Enterprise Edition (32-bit và 64-bit) Enterprise Edition được sử dụng trong các doanh nghiệp, tổ chức có các mức yêu cầu xử lý giao dịch trực tuyến trên diện rộng (online transaction processing - OLTP), khả năng phân tích dữ liệu phức tạp cao, hệ thống kho dữ liệu (data warehousing systems) và web sites. Enterprise Edition phù hợp cho các tổ chức lớn và các yêu cầu phức tạp. + SQL Server 2005 Standard Edition (32-bit và 64-bit) Standard Edition là phiên bản phục vụ cho việc quản trị và phân tích dữ liệu phù hợp cho các doanh nghiệp, tổ chức vừa và nhỏ. Nó bao gồm các giải pháp cần thiết cho thương mại điện tử (e- commerce), kho dữ liệu (data warehousing) và dòng doanh nghiệp (line-of-business). + SQL Server 2005 Workgroup Edition (32-bit only) Workgroup Edition là giải pháp quản trị dữ liệu phù hợp cho các doanh nghiệp, tổ chức nhỏ chỉ cần một cơ sở dữ liệu không giới hạn kích thước hoặc số người sử dụng. Workgroup Edition là lý tưởng cho các mức cơ sở dữ liệu tin cậy, mạnh mẽ và dễ quản trị. + SQL Server 2005 Developer Edition (32-bit và 64-bit) Developer Edition có tất cả các tính năng của phiên bản SQL Server 2005 Enterprise Edition, nhưng nó chỉ là phiên bản sử dụng cho phát triển và kiểm tra ứng dụng. Phiên bản này phù hợp cho các cá nhân, tổ chức xây dựng và kiểm tra ứng dụng. + SQL Server 2005 Express Edition (32-bit only) SQL Server Express, dễ sử dụng và quản trị cơ sở dữ liệu đơn giản. Được tích hợp với Microsoft Visual Studio 2005, SQL Server Express trở nên dẽ dàng để phát triển các ứng dụng dữ liệu giầu khả năng, an toàn trong lưu trữ, và nhanh chóng triển khai. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 30 SQL Server Express là phiên bản miễn phí, có thể dùng như một cơ sở dữ liệu máy khách hoặc cơ sở dữ liệu máy chủ đơn giản. SQL Server Express là lựa chọn tốt cho những người dùng chỉ cần một phiên bản SQL Server 2005 nhỏ gọn, dùng trên máy chủ có cấu hình thấp, những nhà phát triển ứng dụng không chuyên hay những người yêu thích xây dựng các ứng dụng nhỏ. b) Các thành phần Server của SQL Server 2005: Các thành phần server của SQL Server 2005 được cho trong bảng 3.4. Bảng 3.4 Các thành phần server của SQL Server 2005. Thành phần Server Chức năng Cỗ máy cơ sở dữ liệu bao gồm Database Engine, lõi dịch vụ cho việc lưu trữ, xử lý và bảo mật dữ SQL Server Database liệu, sao lưu và đồng bộ (Replication), tìm kiếm toàn văn (Full-Text Search), và các công cụ cho việc quản trị dữ liệu quan hệ và XML. Analysis Services bao gồm các công cụ cho việc tạo và quản lý tiến trình phân tích trự tuyến Analysis Services (online analytical processing - OLAP) và các ứng dụng khai thác dữ liệu. Reporting Services bao gồm các thành phần server và client cho việc tạo, quản lý và triển khai Reporting Services các báo cáo. Reporting Services cũng là nền tảng cho việc phát triển và xây dựng các ứng dụng báo cáo. Dịch vụ thông báo Notification Services là nền tảng cho sự phát triển và triển khai các ứng dụng Notification Services tạo và gửi thông báo. Notification Services có thể gửi thông báo theo địch thời đến hàng ngàn người đăng ký sử dụng nhiều loại thiết bị khác nhau. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 31 Integration Services Integration Services là một tập hợp các công cụ đồ họa và các đối tượng lập trình cho việc di chuyển, sao chép và chuyển đổi dữ liệu. c) Các thành phần Client Bảng 3.5 Các thành phần client của SQL Server 2005. Thành phần Client Chức năng Là các thành phần cho việc truyền thông giữa Connectivity clients và servers, và các thư viện mạng như DB- Components Library, ODBC, and OLE DB. c) Các công cụ đồ họa Các công cụ giao diện đồ họa giúp cho việc truy xuất và quản trị SQL Server được thay đổi khá nhiều so với các phiên bản trước đó, các công cụ quản trị đó được cho trong bảng 3.6. và hình 3.2. Hình 3.2. Các giao diện đồ họa của SQL Server 2005. Bảng 3.5 Các công cụ quản trị trên SQL Server 2005. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 32 Management tools Chức năng SQL Server Management Studio (SSMS), là công cụ mới trên Microsoft SQL Server 2005, nó là một môi trường được tích hợp cho việc truy xuất, cấu hình, quản trị và phát triển tất cả các thành phần SQL Server của SQL Server. SSMS kết hợp các tính năng của Management Studio Enterprise Manager, Query Analyzer, và Analysis Manager, được bao hàm trong các phiên bản trước của SQL Server, thành một môi trường đơn mà cung cấp truy xuất SQL Server để phát triển và quản trị tất cả các mức kỹ năng trên. SQL Server Configuration Manager cung cấp các SQL Server quản trị cấu hình cơ sở cho các dịch vụ SQL Server Configuration (SQL Server services), các giao thức server (server Manager protocols), các giao thức client (client protocols) và các bí danh client (client aliases). SQL Server Profiler cung cấp giao diện người dùng SQL Server Profiler đồ họa cho việc giám sát thể hiện của Database Engine hoặc thể hiện của Analysis Services. Database Engine Tuning Advisor cố vấn, giúp tạo Database Engine các tập tối ưu các chỉ số (indexes), indexed views, Tuning Advisor và các phân vùng (partitions). 3.1.3. Quản lý các dịch vụ của SQL Server. 3.1.3.1. Quản lý các dịch vụ của SQL Server 2000 a) Sử dụng SQL Server Service Manager Dịch vụ là một chương trình hay một tiến trình thực hiện một chức năng cụ thể để hỗ trợ chương trình khác. SQL Server 2000 cung cấp các dịch vụ: - Dịch vụ SQL Server: Khi khởi động SQL Server thì dịch vụ SQL Server được khởi động trên Windows. Dịch vụ này quản lý các Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 33 tập tin CSDL, xử lý các phát biểu Transaction – SQL, cấp phát tài nguyên giữa các kết nối người dùng đồng thời, đảm bảo tính nhất quán dữ liệu,.v.v - Dịch vụ SQL Server Agent: Dịch vụ này hỗ trợ lập các chương trình, thực thi tác vụ, cảnh báo, thông báo và kế hoạch bảo trì CSDL. Nó cho phép ta thực hiện tự động hóa các tác vụ bảo trì CSDL. - Dịch vụ Distributed Transaction Coordinator: Là trình quản lý giao dịch cung cấp các khả năng bao gồm nhiều nguồn dữ liệu khác nhau kể cả các CSDL từ xa trong các giao dịch ứng dụng. Khởi động hoặc dừng các dịch vụ SQL Server sử dụng trình SQL Server Service Manager thực hiện theo các bước sau: 1. Vào start/Programs/Microsoft SQL Server/Service Manager; Hoặc dưới góc phải của màn hình trên thanh task bar hệ thống của windows, double–click vào biểu tượng Service Manager. Khi đó cửa sổ SQL Server Service Manager xuất hiện như hình 3.3. 2. Trong danh sách Server chọn tên server và danh sách Service chọn dịch vụ SQL Server. 3. Click nút Start/Continue để khởi chạy dịch vụ; Click nút Pause để tạm dừng dịch vụ, tạm dừng dịch vụ để ngăn chặn người dùng đăng nhập vào SQL Server và có thời gian cho người dùng đang kết nối có thời gian hoàn tất các tác vụ và thoát khỏi SQL Server trước khi ta đóng SQL Server; Click vào nút Stop để dừng dịch vụ. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 34 Hình 3.3. Ứng dụng SQL Server Service Manager 4. Trong khi chạy Service Manager, trạng thái hiển thị của các dịch vụ được mặc định là 5giây, để thay đổi thay đổi thời gian cập nhật, ta click chuột vào biểu tượng ở góc trên bên trái của SQL Server Service Manager, chọn Options xuất hiện hộp thoại SQL Server Service Manager Options nhập vào khoảng thời gian kiểm soát vòng mới cho các dịch vụ, chẳng hạn là 10 (Hình 3.4). Hình 3.4. SQL Server Service Manager Options b) Sử dụng Enterprise Manager Enterprise Manager là thành phần Microsoft Management Console (MMC). MMC là ứng dụng trung tâm dùng để quản lý tất cả các giao tiếp của hệ thống. Nó cho phép thực hiện các tác vụ sau: + Khởi động, dừng, tạm dừng Server. + Đăng ký server, + Cấu hình server cục bộ và từ xa + Cấu hình và quản lý các thể hiện của server, + Thiết lập bảo mật đăng nhập, thêm người dùng, người quản trị hệ thống. + Gán mật khẩu cho người quản trị hệ thống, Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 35 + Tạo và lập thời gian biểu thực thi công việc, + Thiết lập và quản lý CSDL, bảng, chỉ mục, view, stored proceduce, trigger, + Quản lý các dịch vụ SQL server khác, * Thay đổi mật khẩu mặc định Tất cả các SQL server đều có một tài khoản quản trị mặc định sẵn là sa (system administrator). Lúc mới cài tài khoản này chưa được gán mật khẩu. Để đảm bảo mức bảo mật cao nhất cho SQL server ta phải gán cho tài khoản sa một mật khẩu. Khi gán mật khẩu ta thực hiện theo các bước sau: 1. Trong Enterprise Manager chọn tên server 2. Chọn Security/Logins để hiển thị tất cả các tài khoản người dùng. 3. Right click chuột lên tài khoản sa, và chọn Properties, xuất hiện cửa sổ SQL Server Login Properties như hình 3.5. 4. Nhập mật khẩu mới vào hộp Password sau đó click OK để hiểm hị hộp thoại Confirm Password. 5. Trong hộp thoại Confirm Password nhập lại mật khẩu trên để xác nhận lại mật khẩu và chọn OK. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 36 Hình 3.5. Thay đổi mật khảu tài khoản sa. 3.1.3.2. Quản lý các dịch vụ của SQL Server 2005 a) Sử dụng SQL Server Configuration Manager SQL Server Configuration Manager là công cụ để quản lý các dịch vụ kết hợp với SQL Server, để cấu hình các giao thức mạng được sử dụng bởi SQL Server, và để quản lý cấu hình kết nối mạng từ các máy tính trạm SQL Server. SQL Server Configuration Manager kết hợp các chức năng của các công cụ trong phiên bản SQL Server 2000 là: Server Network Utility, Client Network Utility, và Service Manager. Khởi động hoặc dừng các dịch vụ SQL Server sử dụng SQL Server Configuration Manager ta thực hiện theo các bước sau: 1. Vào start/Programs/Microsoft SQL Server 2005/Configuration Tools/SQL Server Configuration Manager, xuất hiện cửa sổ SQL Server Configuration Manager 2. Chọn SQL Server 2005 Services, trong khu vực chi tiết bên phải, right click lên thể hiện của SQL Server mà ta muốn khởi chạy hoặc dừng. Giả sử SQL Server (SQLEXPRESS) như hình 3.6. + Start: Khởi chạy thể hiện của SQL Server + Stop: Dừng hoạt động của thể hiện SQL Server. + Pause: Tạm dừng hoạt động của thể hiện SQL Server + Restart: Khởi động lại thể hiện của SQL Server Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 37 Hình 3.6. Cửa sổ SQL Server Configuration Manager. 3. Muốn khởi chạy tự động dịch vụ SQL Server, trong cửa sổ trên chọn Properties. Trong hộp thoại SQL Server Properties, chọn tab Service và chọn thuộc tính Start Mode là Automatic. Hình 3.7. Cửa sổ SQL Server Properties. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 38 b) Sử dụng SQL Server Management Studio Microsoft SQL Server Management Studio là môi trường tích hợp cho việc truy cập, cấu hình, quản lý, quản trị và phát triển tất cả các thành phần của SQL Server. SQL Server Management Studio kết hợp một nhóm rộng lớn các công cụ đồ họa giầu trình biên tập (script editors) cung cấp các truy xuất đến SQL Server để phát triển và quản trị tất cả các mức kỹ năng. Và có thể dùng nó để quản trị SQL Server 2000. SQL Server Management Studio kết hợp các tính năng của Enterprise Manager, Query Analyzer, và Analysis Manager trong phiên bản trước. Thêm vào đó, SQL Server Management Studio làm việc với tất cả các thành phần cuả SQL Server như là: Reporting Services, Integration Services, SQL Server Mobile, và Notification Services. Microsoft SQL Server Management Studio bao gồm các tính năng tổng quát sau: + Cung cấp hầu hết các tác vụ quản trị cho SQL Server 2005 và SQL Server 2000. + Là môi trường đơn, tích hợp cho việc quản trị và trao quyền SQL Server Database Engine. + Các hộp thoại mới cho việc quản lý các đối tượng trong SQL Server Database Engine, Analysis Services, Reporting Services, Notification Services, và SQL Server Mobile, cho phép ta thực thi các hành động ngay lập tức, gửi chúng tới Code Editor, hoặc tạo tập lệnh cho lần thực thi tiếp theo. + Các hộp thoại cho phép truy cập đến nhiều điều khiển trong khi hộp thoại đó đang được mở. + Lập lịch cho phép ta thực thi các hành động của các hộp thoại quản trị. + Export và import đăng ký server SQL Server Management Studio từ một môi trường Management Studio này đến môi trường khác. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 39 + Save hoặc in file XML Showplan hoặc Deadlock files được sinh bởi SQL Server Profiler, xem lại, hoặc gửi chúng tới administrators để phân tích.v.v Để truy cập vào SQL Server Management Studio: 1. Để chạy SQL Server Management Studio, trên thanh taskbar, click Start/ Programs/Microsoft SQL Server 2005, và sau đó click SQL Server Management Studio. 2. Khi khởi chạy SQL Server Management Studio, một hộp thoại “Connect to Server” (Hình 3.8) xuất hiện. Ta có thể chọn một thể hiện của Server để kết nối hoặc không chọn một thể hiện nào cả. Hình 3.8. Cửa sổ Connect to Server. + Server type: Chọn Database Engine để kết nối đến cỗ máy cơ sở dữ liệu. + Server name: chọn hoặc nhập tên server + Authentication: Chọn chế độ xác thực là Windows Authentication hoặc SQL Server Authentication, nếu chọn SQL Server Authentication thì ta phải cung cấp thông tin cho các mục Login và Password. + Login: Nhập tên đăng nhập Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 40 + Password: Mật khẩu của tên đăng nhập + Remember password: Tùy chọn được chọn để là đăng nhập sau không phải đánh mật khẩu. Sau hộp thoại “Connect to Server ”cho vào cửa sổ SQL Server Management Studio (Hình 3.9) Hình 3.9. Cửa sổ SQL Server Management Studio. * Đăng ký Server Cửa sổ Registered Servers trong SQL Server Management Studio chứa danh sách các thể hiện danh sách các server đã đăng ký. Ta sử dụng cửa sổ Registered Servers nhằm mục đích: + Lưu thông tin kết nối cho các thể hiện của SQL Server trên mạng. + Hiển thị một thể hiện đang chạy hay không chạy Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 41 + Kết nối tới một thể hiện trong cửa sổ Object Explorer hoặc Query Editor + Nhóm các server Để đăng ký một thể hiện của SQL Server ta thực hiện các bước sau 1. Vào View\chọn Registered Servers để xuất hiện cửa sổ Registered Servers hình 3.10. 2. Chọn biểu tượng kiểu thể hiện muốn đăng ký, theo thứ tự từ trái sang phải là Database Engine, Analysis Services, Reporting Services, SQL Server Mobile và Integration Services. Ta chọn Database Engine. Hình 3.10. Cửa sổ Registered Servers. 3. Right click lên biểu tượng hoặc vùng trống chọn New\Server Registration xuất hiện cửa sổ New Server Registration (Hình 3.11) Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 42 Hình 3.11. Cửa sổ New Server Registration. Trong cửa sổ này ta thực hiện các lựa chọn sau: + Server Name: Nhập hoặc chọn tên thể hiện của SQL Server từ hộp danh sách Server Name, có thể chọn để tự tìm các thể hiện của SQL Server trên mạng. + Authentication: Chọn chế độ xác thực, tương tự như trong cửa sổ Connect to Server. + Registered server name: Tên của thể hiện trên cửa sổ Registered Servers. + Nút Test: Dùng để kiểm tra xem kết nối có thành công hay không? Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 43 + Nút Save: Dùng để lưu thông tin đăng ký. Để kết nối tới thể hiện của SQL Server và hiển thị nó trong cửa sổ Object Explorer hoặc tạo truy vấn mới ta thực hiện các bước sau: 1. Trong cửa sổ Registered Servers chọn biểu tượng kiểu thể hiện muốn kết nối. Ta chọn Database Engine. 2. Right click lên thể hiện muốn kết nối, chọn Connect và chọn: + New Query: để tạo một truy vấn đã kết nối tới thể hiện này. + Object Explorer: Để hiển thị nó trong cửa sổ Object Explorer. * Thay đổi mật khẩu mặc định Thay đổi mật khẩu cho tài khoản đăng nhập sa ta thực hiện theo các bước như sau: 1. Trong cửa sổ Object Explorer của SQL Server Management Studio chọn tên thể hiện của server 2. Chọn Security/Logins để hiển thị tất cả các tài khoản người dùng. 3. Right click chuột lên tài khoản sa, và chọn Properties, xuất hiện cửa sổ Login Properties như hình 3.12. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 44 Hình 3.12. Cửa sổ Login Properties. + Trong tab General: Nhập mật khẩu mới vào hộp Password và xác nhận mật khẩu vào ô Confirm Password. + Trong tab Status, mục Login chọn Enabled để cho phép có thể login vào theo tài khoản sa, hoặc Disabled để không thể login vào theo tài khoản sa. 3.2. LÀM VIỆC VỚI CÁC ĐỐI TƯỢNG TRONG SQL SERVER Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 45 3.2.1. Cơ sở dữ liệu - Database a) Các Database hệ thống Khi cài đặt SQL Server có 4 Database hệ thống được cài đặt, đó là: - master: Ghi nhận thông tin cấp hệ thống, thông tin khởi tạo SQL Server và thiết lập cấu hình SQL Server. Database này cũng ghi nhận tất cả các tài khoản đăng nhập, sự tồn tại của các Database khác, vị trí tập tin chính cho tất cả Database người dùng. - tempdb: Giữ các bảng tạm, các stored procedure tạm,.v.v Được dùng cho các nhu cầu lưu trữ tạm của SQL Server. - model: là khuôn mẫu cho tất cả các CSDL khác được tạo trên hệ thống kể cả tempdb. Database model phải được tồn tại trên hệ thống, bởi vì nó được dùng để tạo lại tempdb mỗi khi SQL server được khởi động. - msdb: Giữ các bảng mà SQL Server Agent dùng để lập thời gian biểu thực thi các công việc, các cảnh báo và các operator. b) Tạo Database Để tạo Database trong SQL Server 2000 , ta có 3 cách khác nhau để tạo: + Sử dụng Create Database Wizard + SQL Server Enterprise Manager + Dùng T-SQL Trong SQL Server 2005 , ta có 2 cách khác nhau để tạo: + SQL Server Management Studio + Dùng T-SQL * Sử dụng Create Database Wizard trên SQL Server 2000 : 1. Khởi động SQL Server Enterprise Manager, chọn tên server cục bộ và vào menu Tools\Wizards → Xuất hiện cửa sổ Select Wizard hình 3.13. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 46 Hình 3.13. Cửa sổ Select Wizard Hình 3.14. Cửa sổ Welcome to Create Database Wizard Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 47 2. Chọn Create Database Wizard và chọn OK → Xuất hiện cửa sổ Cửa sổ Welcome to Create Database Wizard hình 3.14. 3. Click Next → Xuất hiện cửa sổ 3.15. Name the Database And Specify its Location. Nhập tên Database và vị trí lưu các file cơ sở dữ liệu và file log → và chọn Next. Hình 3.15. Cửa sổ Name the Database And Specify its Location 4. Màn hình Name the Database files (hình 3.16), nhập tên tập tin dữ liệu chính (có thể dùng tên mặc) và kích thước khởi tạo cho tập tin đó. Sau đó chọn Next → Xuất hiện cửa sổ 3.17. 5. Theo tùy chọn mặc định: Tập tin CSDL tự động gia tăng và gia tăng 10%, không giới hạn dung lượng tối đa. Ta có thể thay đổi các tham số đó thông qua các tùy chọn: + Do not automatically grow the data file: Không tự động gia tăng kích thước của file dữ liệu. + Automatically grow the data file: Tự động gia tăng kích thước của file dữ liệu. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 48 + Grow the file in megabytes (MB): Gia tăng theo MB. + Grow the file by percent: Gia tăng theo phần trăm. + Unrestricted file growth: Gia tăng không giới hạn cận trên của file. + Restricted file growth to MB: Giới hạn cận trên của file theo MB. Chọn Next. Xuất hiện của sổ Name the Transaction Log Files hình 3.18. 6. Nhập tên tập tin bản ghi giao dịch và kích thước khởi tạo chúng. Chọn Next. 7. Xuất hiện cửa sổ Define the Transaction Log File Growth. Các thông tin điền tương tự như cửa sổ Define the Database file Growth. Chọn Next để tiếp tục. 8. Chọn Finish để kết thúc. Hình 3.16. Cửa sổ Name the Database files Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 49 Hình 3.17. Cửa sổ Define the Database file Growth Hình 3.18. Cửa sổ Name the Transaction Log Files Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 50 Hình 3.19. Cửa sổ Define the Transaction Log File Growth Hình 3.20. Cửa sổ hoàn thành tạo Database. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 51 * Sử dụng SQL Server Enterprise Manager trên SQL Server 2000 Khởi động SQL Server Enterprise Manager. Chọn tên Server và chọn Database. Rigth click lên mục Database và chọn New Database. Xuất hiện cửa sổ Database Properties (hình 3.21). Hình 3.21. Cửa sổ Database Properties. + Tab General: Nhập tên Database chẳng hạn QLDiemSV + Tab Data files: Đặt tên các file dữ liệu, vị trí lưu trữ chúng và các tham số growth. Chú ý, SQL Server Enterprise Manager tự động tạo tập tin dữ liệu chính QLDiemSV_Data (có thể thay đổi được tên) thuộc nhóm PRIMARY không thể thay đổi nhóm. Ta có thể tạo các tập tin dữ liệu phụ trên các nhóm khác nhau. + Tab Transaction log: Tương tự như trên nhưng đối với tập tin ghi các bản ghi giao dịch. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 52 * Sử dụng SQL Server Management Studio của SQL Server 2005: Như đã giới thiệu ở trên, SQL Server Management Studio là công cụ tích hợp SQL Server Enterprise Manager của phiên bản 2000 nên ta có thể sử dụng để tạo database tương tự như phiên bản 2000. Cách tạo như sau: 1. Mở rộng các đối tượng trên thể hiện của SQL trong cửa sổ Object Explorer muốn tạo Database. 2. Right click lên mục Database\ chọn New Database xuất hiện cửa sổ New Database (Hình 3.22). Hình 3.22. Cửa sổ New Database. Trong cửa sổ New Database, tại tab General ta thực hiện điền các thông số cho các mục như sau: + Database name: Điền tên Database muốn tạo + Owner: Chỉ định tên các Logins sở hữu Database đang tạo. Để chọn các logins ta click vào nút xuất hiện cửa sổ ‘Select Database Owner’ (Hình 3.23). Trong mục ‘Enter the Object names to select’ ta nhập tên các logins hoặc chọn nút Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 53 Browse để liệt kê và chọn các logins trong cửa sổ ‘Browse for Objects’ (Hình 3.24) Hình 3.23. Cửa sổ Select Database Owner. Hình 3.24. Cửa sổ Browse for Objects. + Database files: Thực hiện chọn các thuộc tính cho các files database, bao gồm: o Logical name: Tên logic của file database. o File Type: Kiểu file (Data, Log) Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 54 o Filegroup: Chỉ định nhóm file. o Initial Size (MB): Kích thước khởi tạo dung lượng các file, tính theo MB. o Autogrowth: Chỉ định các tham số tự động gia tăng dung lượng cho các file. Click vào nút xuất hiện cửa sổ ‘Change Autogrowth for MyDB’ (Hình 3.25) cho phép khai báo các tham số Autogrowth: Tùy chọn Enable Autogrowth cho phép tự động gia tăng; File Growth chỉ định độ tự động gia tăng theo phần trăm (In Percent) hoặc theo dung lượng chỉ định (In Megabytes); Maximum File Size định nghĩa độ gia tăng của file đến dung lượng lớn nhất. Hình 3.25. Cửa sổ Change Autogrowth for MyDB o Path: Chỉ định đường dẫn vật lý lưu trữ các files database. Click nút để thay đổi đường dẫn mặc định. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 55 * Sử dụng T-SQL Ta có thể sử dụng T-SQL hay script để tạo Database, với cách này ta cần hiểu rõ về các cú pháp câu lệnh trong SQL Server để tạo Database. CREATE DATABASE database_name [ ON [ PRIMARY ] [ [ , n ] [ , [ , n ] ] [ LOG ON { [ , n ] } ] ] ] [;] Để attach một database: CREATE DATABASE database_name ON [ , n ] FOR { ATTACH | ATTACH_REBUILD_LOG } [;] ::= { ( NAME = logical_file_name , FILENAME = 'os_file_name' [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [KB | MB | GB | TB | % ] ] ) [ , n ] } ::= { FILEGROUP filegroup_name [ DEFAULT ] [ , n ] } Các tham số trong đó: database_name: Là tên của CSDL. Nếu tên của file dữ liệu (data file) không được chỉ định thì SQL Server sử dụng database_name là tên cho logical_file_name và os_file_name. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 56 ON Chỉ định định nghĩa các file trên đĩa được sử dụng để lưu trữ các phần dữ liệu của database, data files. PRIMARY Chỉ định này liên quan đến danh sách định nghĩa primary file . File đầu tiên được chỉ định trong của nhóm filegroup primary filegroup trở thành file primary. Một database chỉ có thể duy nhất một file primary. Nếu từ khóa PRIMARY không được chỉ định thì file đầu tiên trong danh sách các fie của câu lệnh CREATE DATABASE sẽ trở thành file primary. LOG ON Chỉ định định nghĩa file log lưu trữ trên đĩa, log files. FOR ATTACH Chỉ định database được tạo băng việc attach tập các file hệ thống đã tồn tại. FOR ATTACH có các yêu cầu sau: - Các files data (MDF và NDF) phải đã tồn tại. - Nếu nhiều files log tồn tại, thì tất cả phải sẵn có. FOR ATTACH_REBUILD_LOG Chỉ định database được tạo băng việc attach tập các file hệ thống đã tồn tại. Nếu một hoặc nhiều files log giao dịch bị lỗi thì file log sẽ được xây dựng lại. Điều khiển các thuộc tính của file. - NAME logical_file_name: Chỉ định tên logical cho file. NAME được yêu cầu khi FILENAME được chỉ định. - FILENAME os_file_name: Chỉ định tên, đường dẫn file hệ điều hành (file vật lý). - SIZE size: Chỉ định kích thước file. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 57 - MAXSIZE max_size: Chỉ định kích thước lớn nhất mà file có thể phát triển đến. Từ khóa UNLIMITED chỉ định file được phát triển cho đến khi đĩa bị đầy. - FILEGROWTH growth_increment: Chỉ định độ tự đông gia tăng của file. Điều khiển các thuộc tính của filegroup. - FILEGROUP filegroup_name: Chỉ định tên logical của filegroup. - DEFAULT: Chỉ định tên filegroup là filegroup mặc định trên database. Ví dụ 3.1. Đưa ra một cách tạo Database MyDB với tập tin dữ liệu chính là MyDB_Data.mdf, dung lượng khởi tạo là 1MB và tối đa là 10MB và độ gia tăng kích thước là 10%. Tập tin bản ghi giao dịch là MyDB_Log.ldf với dung lượng ban đầu là 2MB và kích thước tối đa không giới hạn, độ gia tăng dung lượng là 10MB. Để tạo script (dùng T- SQL) ta mở cửa sổ query để soạn thảo: - Đối với SQL Server 2000: Vào Start/Programs/Microsoft SQL Server/Query Analyzer, xuất hiện hộp thoại Connect to SQL Server, trong danh sách server chọn server cục bộ máy mình và nhập đoạn mã lệnh sau. - Đối với SQL Server 2005: Trong cửa sổ SQL Server Management Studio, trên thanh Standard chọn nút New Query để tạo cửa sổ truy vấn kết nối đến thể hiện SQL đang được kết nối hoặc nút Database Engine Query để xuất hiện cửa sổ kết nối (Hình 3.8) ta thực hiện kết nối đến thể hiện SQL mà muốn thực hiện trên đó. Xuất hiện cửa sổ truy vấn ta thực hiện nhập đoạn mã lệnh sau: Use master go create database MyDBT On ( Name= MyDBT_Data, Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 58 FileName='E:\Tempt\MyDBT_Data.mdf', Size=10MB, MaxSize=100MB, FileGrowth=10% ) Log On ( Name= MyDBT_log, FileName='E:\Tempt\MyDBT_Log.ldf', Size=2MB, MaxSize=UNLIMITED, FileGrowth=10% ) - Click nút Execute hoặc F5 để chạy. c) Xóa Database * Dùng Enterprise Manager trong SQL Server 2000: Trong Enterprisse Manager, chọn nhóm server cục bộ và mở rộng mục Database. Sau đó right click lên CSDL muốn xóa và chọn Delete Database. Xuất hiện hộp thoại xác nhận xóa và chọn Yes. * Sử dụng SQL Server Management Studio của SQL Server 2005: Để xóa một database ta đăng nhập vào SQL Server Management Studio với một login có quyền xóa database đó và thực hiện các bước sau: 1. Mở rộng các đối tượng trên thể hiện của SQL trong cửa sổ Object Explorer muốn xóa Database. 2. Mở rộng mục Database và Right click lên database muốn xóa và chọn Delete xuất hiện cửa sổ ‘Delete Object’ chọn OK để xóa. * Dùng T-SQL Để xóa Database ta sử dụng cú pháp sau: DROP DATABASE database_name [ , n ] Ví dụ: nhập đoạn T-SQL sau để xóa Database MyDB. Use master Go Drop Database MyDB Go Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 59 3.2.2.Bảng - Table Trước khi đi vào các thao tác đối với bảng, ta xét các kiểu dữ liệu được sử dụng trong SQL Server. a) Các kiểu dữ liệu Kiểu dữ liệu Ý nghĩa bigint Số nguyên 8 byte. binary[(n)] Dữ liệu nhị phân có kích thước cố định n byte. Kích thước lưu trữ là n+4 byte. Với 1<n<8000 bit Dữ liệu số nguyên nhận các giá trị 0, 1 hoặc NULL char[(n)] Dữ liệu ký tự, có chiều dài n ký tự, không hỗ trợ Unicode. Với độ dài 1<n<8000. cursor Tham chiếu tới một con trỏ. Chỉ dùng cho các biến và tham số trong stored procedure. datetime Dữ liệu ngày giờ. Giá trị nhận từ 1/1/1753 đến 31/12/9999 decimal[(p,[s])] Dữ liệu số thập phân. p là tổng số ký tự số có thể được numeric[(p,[s])] lưu, s là số chữ số thập phân. float[(n)] Dữ liệu số động có phạm vi từ -1.79E+308 đến 1.79E+308 image Dữ liệu nhị phân có chiều dài thay đổi, dài hơn 8000 byte và tối đa là 2^31-1 byte. integer hoặc int Dữ liệu số nguyên từ -2^31 đến 2^31-1. Chiếm 4 byte. money Dữ liệu kiểu tiền tệ từ -2^63 đến 2^63. Chiếm 8byte. nchar[(n)] Dữ liệu ký tự, có chiều dài cố định n ký tự. Có hỗ trợ Unicode. Với 1<n<4000. Chiếm 2n byte. ntext Dữ liệu ký tự có chiều dài thay đổi, với chiều dài tối đa là Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 60 2^30-1 ký tự. Hỗ trợ unicode. nvarchar[(n)] Dữ liệu ký tự có chiều dài thay đổi với n ký tự. Có hỗ trợ Unicode. Với 1<n<4000. real Dữ liệu số thực động, phạm vi từ - 3.4E+38 đến 3.4E+38. Chiếm 4 byte. smalldatetime Dữ liệu ngày giờ từ 1/1/1900 đến 6/6/2079. Chiếm 4 byte. smallint Dữ liệu số nguyên từ -2^15 đến 2^15-1. Chiếm 2 byte. smallmoney Dữ liệu kiểu tiền tệ từ -2^31 đến 2^31. Chiếm 4 byte. sql_variant Cho phép giữ các giá trị của các kiểu dữ liệu khác nhau (tất cả các kiểu dữ liệu hệ thống khác). sysname Đây là kiểu dữ liệu đặc biệt, do SQL Server định nghĩa là kiểu nvarchar(128). Chiếm 256 byte. table Tương tự như bảng tạm, khai báo gồm danh sách các cột và các kiểu dữ liệu. text Dữ liệu ký tự có chiều dài thay đổi, dài hơn 8000byte. Có thể lưu tới 2^31 ký tự. Không hỗ trợ Unicode. timestamp Cột timestamp được cập nhật tự động mỗi khi dòng được thêm hoặc được cập nhật. Mỗi bảng chỉ có thể có 1 cột timestamp. Kích thước lưu trữ là 8 byte. tinyint Dữ liệu số nguyên từ 0 đến 255. Chiếm 1 byte. unique-identifier Giá trị nhị phân 16 byte, là số định danh duy nhất toàn cục. varbinary[(n)] Dữ liệu nhị phân n có chiều dài thay đổi. Với 1<n<8000 varchar[(n) Dữ liệu ký tự có chiều dài thay đổi với n ký tự. Không hỗ trợ Unicode. Với 1<n<4000. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 61 b) Tảo bảng. * Dùng SQL Server Enterprise Manager trong SQL Server 2000: Hình 3.26. Cửa sổ thiết Table. • Trong Enterprise Manager, mở rộng danh mục Database và mở rộng cơ sở dữ liệu QLDiemSV. • Right Click lên danh mục Tables, chọn New Table. • Xuất hiện cửa sổ thiết kế bảng như hình 3.26. Ta thực hiện thiết kế bảng HOSOSV gồm các trường (trong cột Column Name), kiểu trường (Data Type) và độ dài dữ liệu (Length) và cho phép trường đó nhận giá trị NULL hay không (Allow Nulls) hình 3.26. • Chọn dòng MaSV, sau đó click vào nút Set primary key để thiết lập trường MaSV là khóa. • Click vào nút Save, xuất hiện cửa sổ Choose Name nhập tên bảng là HOSOSV để lưu lại bảng. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 62 * Dùng SQL Server Management Studio trong SQL Server 2005: Để tạo bảng trong SQL Server Management Studio ta thực hiện: 1. Mở rộng các đối tượng trên thể hiện của SQL trong cửa sổ Object Explorer muốn tạo bảng. 2. Mở rộng mục Database và chọn cơ sở dữ liệu muốn tạo bảng. Right click lên mục Table và chọn New Table xuất hiện cửa sổ thiết kế table (Hình 3.27): + Column Name: Nhập tên các cột trong bảng. + Data Type: Chọn kiểu dữ liệu và độ dài cho kiểu. + Allow Nulls: Cho phép chấp nhận dữ liệu NULL hay không? + Column Properties: Thiết lập các thuộc tính cho cột đang được chọn. Hình 3.27. Cửa sổ thiết Table trong SQL Server Management Studio . 3. Để thiết lập khóa, chọn các trường khóa của bảng sau đó click vào nút biểu tượng khóa (Set Primary Key) hoặc right click vào các trường đó và chọn Set Primary Key. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 63 4. Dùng tổ hợp phím Ctrl+S hoặc click vào nút Save để lưu cấu trúc bảng vừa tạo trong cơ sở dữ liệu. * Dùng T-SQL Để tạo bảng dùng T – SQL ta sử dụng cú pháp sau: CREATE TABLE table_name ( { | } [ , n ] ) ::= { column_name data_type } [ { DEFAULT constant_expression | [ IDENTITY [ ( seed , increment ) ] ] } ] [ [ n ] ] ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] } ::= [ CONSTRAINT constraint_name ] { [ { PRIMARY KEY | UNIQUE } { ( column [ , n ] ) } ] | FOREIGN KEY ( column [ , n ] ) REFERENCES ref_table [ ( ref_column [ , n ] ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] } Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 64 Ví dụ 3.2. Tạo bảng a) Tạo bảng LOP dùng T-SQL. Use QLDiemSV Go Create Table DMLop (MaLop char(10) NOT NULL, TenLop nvarchar(50) NOT NULL, Khoa char(10) CONSTRAINT PK_DMLOP Primary Key (MaLop)) b) Tạo hai bảng MyCustomers và MyOrders. Bảng MyCustomers có hai cột, cột CustID vừa là cột nhận dạng IDENTITY[(seed,increment )] có giá trị khởi tạo ban đầu là 100 và bước nhảy là 1 vừa là khóa. Bảng MyOrders có khóa là OrderID và khóa ngoại là CustID tham chiếu đến bảng MyCustomers. CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY, CompanyName nvarchar (50)) CREATE TABLE MyOrders (OrderID int PRIMARY KEY, CustID int REFERENCES MyCustomers(CustID)) c) Thay đổi cấu trúc bảng * Dùng SQL Server Enterprise Manager trong SQL Server 2000: + Trong Enterprise Manager, mở rộng danh mục Database, mở rộng cơ sở dữ liệu có bảng cần thay đổi cấu trúc, chẳng hạn QLDiemSV và chọn mục Table. + Right Click lên bảng thuộc CSDL QLDiemSV cần thay đổi cấu trúc, chọn Design Table. Xuất hiện cửa sổ thiết kế bảng như hình 3.26. Ta thực hiện các thay đổi về cấu trúc: đổi kiểu dữ liệu, thêm trường, xóa trường, thiết lập khóa .v.v với bảng đã chọn đó. * Dùng SQL Server Management Studio trong SQL Server 2005: Để thay đổi cấu trúc bảng trong SQL Server Management Studio ta thực hiện các bước sau: Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 65 + Mở rộng các đối tượng trên thể hiện của SQL trong cửa sổ Object Explorer muốn thay đổi cấu trúc bảng. + Mở rộng mục Database và chọn cơ sở dữ liệu và bảng cần thay đổi cấu trúc. Right click lên bảng đó và chọn Modify xuất hiện cửa sổ thiết kế table (Hình 3.27) và ta thực hiện các thay đổi đối với cấu trúc bảng như cách tạo bảng. * Dùng T-SQL Để thay đối cấu trúc bảng bằng T-SQL ta có cú pháp câu lệnh sau: ALTER TABLE table_name { ALTER COLUMN column_name { type_name [ ( { precision [ , scale ] | max } ) ] [ NULL | NOT NULL ] } | [ WITH { CHECK | NOCHECK } ] ADD { | | } [ , n ] | DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name } [ , n ] | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ , n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ , n ] } [ ; ] Ví dụ 3.3. Thay đổi cấu trúc bảng MyCustomers ALTER TABLE MyCustomers ADD CustType CHAR(10) NULL Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 66 d) Nhập dữ liệu cho bảng * Dùng Enterprise Manager • Trong Enterprise Manager, mở rộng danh mục Database, mở rộng cơ sở dữ liệu QLDiemSV và chọn mục Table. • Right Click lên bảng thuộc CSDL QLDiem cần nhập dữ liệu, chọn Open Table\Return all row. Xuất hiện cửa sổ nhập dữ liệu cho bảng đó. * Dùng SQL Server Management Studio trong SQL Server 2005: + Mở rộng các đối tượng trên thể hiện của SQL trong cửa sổ Object Explorer muốn nhập dữ liệu cho bảng. + Mở rộng mục Database, chọn cơ sở dữ liệu và bảng cần nhập dữ liệu. Right click lên bảng cần nhập dữ liệu và chọn Open Table xuất hiện cửa sổ nhập dữ liệu cho bảng. * Dùng T-SQL Ví dụ 3.3. Chèn dữ liệu vào bảng LOP - Chèn một bản ghi: Insert Into LOP(MaLop, TenLop, Khoa) VALUES ('TH6A',N'Tin học 6A', '6') - Chèn tất cả các bản ghi từ bảng DMLOP vào bảng LOP. Insert Into LOP(MaLop, TenLop, Khoa) Select MaLop, TenLop, Khoa From DMLOP e) Xóa bảng * Dùng Enterprise Manager • Trong Enterprise Manager, mở rộng danh mục Database, mở rộng cơ sở dữ liệu QLDiemSV và chọn mục Table. • Right Click lên bảng cần xóa, chọn Delete. * Dùng SQL Server Management Studio trong SQL Server 2005: + Mở rộng các đối tượng trên thể hiện của SQL trong cửa sổ Object Explorer muốn xóa bảng. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 67 + Mở rộng mục Database, chọn cơ sở dữ liệu chứa bảng muốn xóa. Right click lên bảng muốn xóa và chọn Delete xuất hiện cửa sổ xác nhận thông tin xóa, chọn OK. * Dùng T-SQL DROP TABLE DMLOP 3.2.3. View a) Khái niệm View View là một bảng ảo được định nghĩa bởi một truy vấn với phát biểu SELECT. View được hình thành dữ liệu từ một hoặc nhiều bảng thật. Đối với người sử dụng thì view giống như một bảng thật. b) Tạo view Để tạo View trong SQL Server 2000 , ta có 3 cách khác nhau để tạo: + Sử dụng Create View Wizard + SQL Server Enterprise Manager + Dùng T-SQL Trong SQL Server 2005 , ta có 2 cách khác nhau để tạo: + SQL Server Management Studio + Dùng T-SQL * Dùng Enterprise Manager hoặc Management Studio • Trong Enterprise Manager hoặc Management Studio, mở rộng danh mục Database, mở rộng cơ sở dữ liệu muốn tạo view, chẳng hạn CSDL QLDiemSV và chọn mụcViews. • Right Click lên danh mục Views, chọn New View. Xuất hiện cửa sổ thiết kế view như hình 3.28 đối với SQL Server 2000 và hình 3.29 đối với SQL Server 2005, gồm 4 vùng sau: Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 68 Hình 3.28. Cửa sổ thiết kế view trên SQL Server 2000. Hình 3.29. Cửa sổ thiết kế view trên SQL Server 2005. + Diagram pane: Hiển thị dữ liệu nguồn có thể là các bảng, các view khác, functions để tạo view. Các cột dữ liệu của view được chọn từ vùng này. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 69 + Grid pane (Criteria pane): Hiển thị các cột của view đã được chọn từ vùng diagram. + SQL pane: Hiển thị phát biểu SQL dùng để định nghĩa view. + Results pane: Hiển thị kết quả nhận được từ view. Ta có thể ẩn hoặc hiện các cửa sổ này bằng cách click vào các nút tương ứng trên thanh công cụ của cửa sổ thiết kế view. Danh sách sau thể hiện ý nghĩa của các nút trên thanh công cụ tính từ trái sang phải: ¾ Đối với SQL Server 2000: + Save: Lưu view. + Properties: Cho phép thay đổi thuộc tính của view. + Show hide pane: các nút ẩn hoặc hiện các cửa sổ trên. + Run: Thực thi và hiển thị kết quả của view trong Results pane. + Cancel Execution And Clear Results: Xóa Results pane. + Verify SQL: Kiểm tra cú pháp của phát biểu SQL. + Remove Filter: loại bỏ tất cả các lọc dữ liệu đã được định nghĩa. + Use GROUP BY: Thêm mệnh đề group by trong câu lệnh SQL. + Add Table: Thêm các bảng, view làm nguồn dữ liệu cho view mới. ¾ Đối với SQL Server 2005: + Show Diagram pane: Ẩn hoặc hiện cửa sổ Diagram pane. + Show Criteria pane: Ẩn hoặc hiện cửa sổ Criteria pane. + Show SQL pane: Ẩn hoặc hiện cửa sổ SQL pane. + Show Results pane: Ẩn hoặc hiện cửa sổ kết quả. + Execute SQL: Thực hiện truy vấn. + Verify SQL Syntax: Kiểm tra cú pháp câu lệnh SQL. + Add Group By: Thêm mệnh đề group by trong câu lệnh SQL. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 70 + Add Table: Thêm các bảng, view, hàm làm nguồn dữ liệu cho view mới. • Click vào nút Add Table, xuất hiện hộp thoại Add Table chọn các bảng các view làm nguồn dữ liệu cho view mới. Trong ví dụ ta chọn bảng HOSOSV và LOP. • Sau khi chọn nguồn dữ liệu ta thực hiện chọn các trường làm dữ liệu trên view như hình 3.30 (SQL Server 2000 ) và 3.31 (SQL Server 2005) • Click vào nút Save, xuất hiện hộp thoại Save lưu với tên Danhsach. Hình 3.30. Cửa sổ thiết kế view Danhsach trong SQL Server 2000 . Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 71 Hình 3.31. Cửa sổ thiết kế view Danhsach trong SQL Server 2005. Sử dụng vùng Grid Pane (Criteria) để hỗ trợ cho việc thiết kế View: + Cột Column: Chứa tên các trường trong bảng\view làm nguồn dữ liệu cho truy vấn này hoặc chứa một biểu thức định nghĩa một trường mới cho view. + Cột Alias: Chỉ định bí danh cho trường trong view mới. + Cột Table: Chỉ định nguồn dữ liệu. + Cột Output: Chỉ định hiển thị hay không hiển thị trường đó. + Cột Sort Type và Sort Order: Dùng để sắp xếp dữ liệu. + Cột Criteria: Dùng để đặt điều kiện lọc cho các bản ghi. + Các cột Or: Dùng kết hợp với cột Criteria để tạo các điều kiện lọc dữ liệu phức tạp. Tính tổng trong truy vấn: Để tính tổng trong truy vấn dùng vùng Grid Pane ta có thể tiến hành phân nhóm các bản ghi và thực hiện tính toán trên từng phân nhóm đó. Để tính tổng trong truy vấn ta chọn nút Use Group By Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 72 xuất hiện cột Group By trong vùng Grid Pane. Ta thực hiện tiến hành phân các nhóm trường như sau: + Trường làm điều kiện, tiêu chuẩn tham gia phân nhóm và tính tổng: Chọn Where trong cột Group By và đặt biểu thức điều kiện trong cột Creteria. + Trường phân nhóm: Chọn Group by trong cột Group By. + Trường tính toán: Chọn một hàm có sẵn (sum, count, avg, max, min, .v.v ) trong cột Group By hoặc xây dựng một biểu thức tính toán trong cột Column. + Định tiêu chuẩn hiển thị kết quả: Đặt điều kiện ở cột Criteria tại các trường phân nhóm và trường tính toán. + Chọn thứ tự hiển thị: Dùng cột Sort Type và Sort Order tại các các trường phân nhóm và các trường tính toán. * Dùng Create view wizard • Khởi động SQL Server Enterprise Manager, chọn tên server cục bộ và vào menu Tools\Wizards. Xuất hiện cửa sổ Select Wizard hình 3.5. • Chọn Create View Wizard xuất hiện cửa sổ Welcometo the Create View Wizard, như hình 3.32. Chọn Next. • Xuất hiện cửa sổ Select Database (Hình 3.33) chọn CSDL mà view được tạo trên đó. Chọn Next. • Trong cửa sổ Select Objects chọn các bảng làm nguồn dữ liệu cho View mới (Hình 3.34). Chọn Next. • Chọn các cột dữ liệu của view (hình 3.35). • Cửa sổ tiếp theo (hình 3.36): Đặt điều kiện lọc tương tự như mệnh đề WHERE trong khối câu lệnh SELECT. Chọn Next. • Chọn tên view (hình 3.37) và chọn Finish (hình 3.38) để kết thúc. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 73 Hình 3.32.Cửa sổ Welcometo the Create View Wizard. Hình 3.33.Cửa sổ Select Database. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 74 Hình 3.34.Cửa sổ Select Objects. Hình 3.35.Cửa sổ Select Columns. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 75 Hình 3.36.Cửa sổ Define Restriction Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 76 Hình 3.37.Cửa sổ Define Restriction Hình 3.38.Cửa sổ Define Restriction Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 77 * Dùng T-SQL: Ta dùng cú pháp câu lệnh sau: CREATE VIEW [schema_name.]view_name[(column[, n])] [ WITH [ , n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ] ::= { [ ENCRYPTION ] [ SCHEMABINDING ] [ VIEW_METADATA ] } Trong đó: schema_name Là tên của lược đồ mà view thuộc lược đồ đó. view_name Là tên của view. column Là tên được sử dụng cho một cột trong view. Tên một cột chỉ yêu cầu khi cột đó được sinh ra từ một biểu thức đại số, một hàm, một hằng; khi hai hoặc nhiều cột khác nhau có cùng tên, điển hình là trong liên kết; hoặc khi một cột trong view được chỉ định tên khác với tên từ nguồn dữ liệu. Các tên cột có thể được gán trong câu lệnh SELECT. Nếu cột không được chỉ định trong view thì các cột trong view có tên cùng tên với các cột câu lệnh SELECT. select_statement Là các khối câu lệnh SELECT định nghĩa view. Các khối câu lệnh SELECT được phân cách nhau bởi mệnh đề UNION hoặc UNION ALL. Một Index được định nghĩa trên view đòi hỏi view phải được xây dựng từ một bảng đơn hoặc nhiều bảng liên kết nhau trong tổ hợp tùy chọn. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 78 CHECK OPTION Bắt buộc tất các câu lệnh sửa đổi dữ liệu thực hiện trên view phải tuân theo các điều kiện trong khối câu lệnh select_statement. ENCRYPTION Mã hóa bản text của câu lệnh CREATE VIEW trong sys.syscomments. Việc sử dụng WITH ENCRYPTION ngăn cản view bị công bố như là một phần bản sao SQL Server. SCHEMABINDING Buộc view vào một lược đồ dưới một bảng hoặc nhiều bảng. Khi SCHEMABINDING được chỉ định, bảng hoặc các bảng cơ cở không được sửa trong những cách ảnh hưởng đến định nghĩa view. VIEW_METADATA Chỉ định các thể hiện SQL Server sẽ trả về cho DB-Library, ODBC, và OLE DB APIs thông tin siêu dữ liệu về view, thay cho bảng và các bảng cơ sở khi trình duyệt siêu dữ liệu được yêu cầu cho một truy vấn tham chiếu đến view. Ví dụ 3.4. Trong CSDL Northwind có hai bảng Orders và Order Details. Ta xây dựng view tính tổng giá trị cho từng hóa đơn (SQL Server 2000). Create View TongGT as SELECT dbo.Orders.OrderID, SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS Tolal FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID GROUP BY dbo.Orders.OrderID Go Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 79 c) Thay đổi view * Dùng Enterprise Manager • Trong Enterprise Manager, mở rộng danh mục Database, mở rộng cơ sở dữ liệu muốn tạo view, chẳng hạn CSDL QLDiemSV và chọn mụcViews. • Right Click lên View, chọn Design View. Xuất hiện cửa sổ thiết kế view. • Thực hiện các thay đổi trên view và ghi lại các thay đổi đó. * Dùng SQL Server Management Studio • Trong SQL Server Management Studio, mở rộng danh mục Database, mở rộng cơ sở dữ liệu muốn tạo view, chẳng hạn CSDL QLDiemSV và chọn mụcViews. • Right Click lên View, chọn Modify. Xuất hiện cửa sổ thiết kế view. • Thực hiện các thay đổi trên view và ghi lại các thay đổi đó. * Dùng T-SQL: Ta dùng cú pháp câu lệnh sau ALTER VIEW [schema_name.]view_name [(column[, n])] [ WITH [ , n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ] ::= {[ENCRYPTION][SCHEMABINDING][VIEW_METADATA] } Ví dụ 3.5. Sửa đổi view DSSV như sau: ALTER VIEW DSSV AS Select MaSV, Hodem + ' '+ TenSV AS Hoten, Ngaysinh From HosoSV GO Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 80 d) Xóa view - Trong cửa sổ Enterprise Manager, mở rộng danh mục Database, mở rộng cơ sở dữ liệu muốn xóa view, chẳng hạn CSDL QLDiemSV và chọn mụcViews. Sau đó right click lên View muốn xóa, chọn Delete. - Trong cửa sổ SQL Server Management Studio, mở rộng danh mục Database, mở rộng cơ sở dữ liệu muốn xóa view, chẳng hạn CSDL QLDiemSV và chọn mụcViews. Sau đó right click lên View muốn xóa, chọn Delete xuất hiện cửa sổ xác nhận xóa chọn OK. - Dùng T-SQL dùng lệnh DROP VIEW theo cú pháp: DROP VIEW view_name Ví dụ 3.6. Xóa view DSSV như sau: DROP VIEW DSSV 3.2.4. Chỉ mục - Index a) Khái niệm chỉ mục Chỉ mục là một trong những công cụ mạnh có sẵn đối với người thiết kế CSDL. Một chỉ mục là một cấu trúc phụ cho phép cải thiện hiệu suất thực thi các truy vấn bằng cách giảm thiểu các hoạt động nhập/xuất dữ liệu cần thiết để được dữ liệu yêu cầu. Tùy thuộc vào kiểu của nó, mà chỉ mục được lưu với dữ liệu hoặc tách biệt với dữ liệu. - Chỉ mục khóa: Chỉ mục khóa chỉ dõ cột hoặc các cột được dùng để sinh ra chỉ mục. Nó cho phép tìm nhanh chóng dòng dữ liệu muốn tìm. Để truy cập dòng dữ liệu dùng chỉ mục, ta chỉ cần đưa ra giá trị khóa của chỉ mục hoặc đưa các giá trị vào mệnh đề WHERE trong khối câu lệnh SELECT. - Chỉ mục duy nhất: Là chỉ mục chỉ chứa một dòng dữ liệu cho mỗi khóa chỉ mục. Một chỉ mục là duy nhất nếu bản thân dữ liệu là duy nhất, nếu không duy nhất ta có thể tạo chỉ mục kết hợp trên nhiều cột để đạt được chỉ mục duy nhất. - Các kiểu chỉ mục: Các chỉ mục được lưu trữ dữ liệu dưới dạng cây nhị phân B-Tree. Có hai kiểu chỉ mục Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 81 • Chỉ mục liên cung (clustered): Là chỉ mục lưu trữ các dòng dữ liệu thực sự của bảng trong nút lá, theo thứ tự đã được sắp xếp. • Chỉ mục phi liên cung (Nonclustered): Không chứa dữ liệu trong nút lá, mà nó chứa thông tin về vị trí của dòng dữ liệu: nếu không có chỉ mục liên cung trên bảng thì nó chứa số nhận dạng dòng (Row ID); nếu có chỉ mục liên cung thì trong nút lá này sẽ chứa giá trị khóa chỉ mục liên cung cho dữ liệu đó. Chú ý: Indexes được tạo tự động khi các ràng buộc PRIMARY KEY và UNIQUE được định nghĩa trên các cột của bảng b) Tạo chỉ mục Để tạo Indexes trong SQL Server 2000 , ta có 3 cách khác nhau để tạo: + Sử dụng Create View Wizard + SQL Server Enterprise Manager + Dùng T-SQL Trong SQL Server 2005, ta có 2 cách khác nhau để tạo: + SQL Server Management Studio + Dùng T-SQL * Dùng wizard • Khởi động SQL Server Enterprise Manager, chọn tên server cục bộ và vào menu Tools\Wizards. Xuất hiện cửa sổ Select Wizard hình 3.5. • Chọn Create Index Wizard xuất hiện cửa sổ Welcometo the Create Index Wizard, như hình 3.39. Chọn Next. • Xuất hiện cửa sổ Select Database and Table (hình 3.40). Chọn CSDL và bảng dữ liệu cần tạo index. + Mục Database name: Chọn cơ sở dữ liệu + Mục Object name: Chọn bảng dữ liệu Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 82 Sau đó chọn Next. • Xuất hiện cửa sổ Current index information (hình 3.41) đưa thông tin các Index đã được tạo trước đó. • Cửa sổ tiếp theo là cửa sổ Select columns (hình 3.42). Trong cửa sổ này ta chọn các cột để tạo chỉ mục. Chọn Next. • Xuất hiện cửa sổ Specify Index Option (hình 3.43). Chỉ định các tham số cho Index như: Tạo chỉ mục duy nhất, tối ưu,.v.v . Chọn Next. • Xuất hiện cửa sổ Completing the Create Index Wizard (hình 3.44). Chọn thứ tự, đặt tên cho Index. Cuối cùng chọn Finish để kết thúc. Hình 3.39.Cửa sổ Welcometo the Create Index Wizard. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 83 Hình 3.40.Cửa sổ Select Database and Table. Hình 3.41.Cửa sổ Current index information. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 84 Hình 3.42.Cửa sổ select columns. Hình 3.43. Cửa sổ Specify Index Option. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 85 Hình 3.44. Cửa sổ Completing the Create Index Wizard * Dùng Enterprise Manager: • Trong cửa sổ Enterprise Manager, mở rộng danh mục Database, mở rộng cơ sở dữ liệu muốn tạo Index, chẳng hạn CSDL QLDiemSV và chọn mục Tables. Sau đó right click lên bảng muốn tạo chỉ mục. Chọn All Task\Manage Indexes. Xuất hiện của sổ Manage Indexes như hình 3.45. • Trong cửa sổ này chọn CSDL trong mục Database, chọn bảng hoặc View trong mục Table/view và sau đó click nút New xuất hiện cửa sổ Create New Index hình 3.46. • Trong cửa sổ này ta nhập tên của Index trong mục Index name và chọn các trường của Index trong cột Column. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 86 Hình 3.45. Cửa sổ Manage Indexes Hình 3.46. Cửa sổ Create New Index Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 87 Ngoài ra, trong cửa sổ 3.45 Manage Indexes ta có các nút thao tác quản lý chỉ mục (Index): o Nút New: Dùng để thêm một Index mới. o Nút Edit: Chọn Index đã có trong danh sách và chọn nút để Edit để thực hiện chỉnh sửa chỉ mục đã có. o Nút Delete: Chọn Index đã có trong danh sách và chọn nút để Delete để thực hiện xóa chỉ mục đã chọn. * Dùng SQL Server Management Studio • Trong SQL Server Management Studio, mở rộng danh mục Database, mở rộng cơ sở dữ liệu chứa bảng hoặc view muốn tạo chỉ số. Hình 3.47. Cửa sổ New Index Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 88 • Mở rộng mục Table hoặc View, mở rộng bảng hoặc view muốn tạo Index. Right Click lên thư mục Indexes chọn New Index, xuất hiện cửa sổ New Index như hình 3.47. gồm các tham số: + Table name (hoặc View name đối với view): Tên bảng (hoặc tên view) mà tệp chỉ số được xây dựng trên đó. + Index name: Đặt tên tệp chỉ số muốn xây dựng. + Index type: Kiểu của index (Clustered, Nonclustered) + Unique: Tùy chọn cho phép tạo tệp chỉ số này có là tệp chỉ số duy nhất hay không? + Index key columns: Xác định các trường khóa của index bằng cách click nút Add xuất hiện cửa sổ Select Columns Hình 3.48, ta chọn các cột làm khóa cho Index. Hình 3.48. Cửa sổ Select Columns Chú ý: Trong SQL Server 2005, chúng ta có thể mở rộng chức năng của các tệp chỉ số phi liên cung (nonclustered indexes) bằng cách thêm các cột không là khóa (nonkey columns) vào các nút lá của cây nonclustered index. Các cột khóa được lưu trữ tại tất cả các mức còn các cột không khóa chỉ lưu trữ tại mức lá của index. Bằng việc thêm các cột không khóa, ta có thể tạo các Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 89 tệp chỉ số phi liên cung phủ nhiều truy vấn hơn bởi vì các cột không khóa có các lợi ích sau: + Chúng có thể là các cột có kiểu dữ liệu không được phép làm các cột khóa trong index. + Chúng không được Database Engine xét khi tính đến số các cột khóa của Index hay kích thước khóa của index. Một index được bao gồm tất cả các cột không khóa có thể cải thiện đáng kể sự thực thi truy vấn khi tất cả các cột trong được bao gồm trong index (cả các cột khóa và không khóa của index). Để thêm các cột không khóa ta chọn trang Included Columns (Hình 3.49). Sau đó click nút Add để xuất hiện cửa sổ Select Columns (Hình 3.48). Hình 3.49. Cửa sổ New Index Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 90 * Dùng T - SQL - Trên SQL Server 2000: CREATE [ UNIQUE][CLUSTERED|NONCLUSTERED]INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ , n ] ) [ WITH [ , n] ] [ ON filegroup ] :: = { PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB } - Trên SQL Server 2005: CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON ( column [ ASC | DESC ] [ , n ] ) [ INCLUDE ( column_name [ , n ] ) ] [ WITH ( [ , n ] ) ] [ ON { filegroup_name|default } ] [ ; ] ::= { [ database_name. [ schema_name ] . | schema_name. ] table_or_view_name } ::= { PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | SORT_IN_TEMPDB = { ON | OFF } | IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF } | DROP_EXISTING = { ON | OFF } | ONLINE = { ON | OFF } | ALLOW_ROW_LOCKS = { ON | OFF } | ALLOW_PAGE_LOCKS = { ON | OFF } | MAXDOP = max_degree_of_parallelism } Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 91 Các tham số trong đó: UNIQUE Chỉ định tạo một unique index trên bảng hoặc trên view. Một clustered index trên view buộc phải là unique. CLUSTERED Chỉ định tạo chỉ mục liên cung. NONCLUSTERED Chỉ định tạo chỉ mục phi liên cung. Mặc định là chỉ mục NONCLUSTERED. index_name Là tên của tệp chỉ số. column Là tên cột hoặc các cột mà index dựa trên đó. INCLUDE ( column [ , n ] ) Chỉ định các cột không khóa được thêm vào mức lá của chỉ mục phi liên cung. ON filegroup_name Tạo index trên filegroup chỉ định. Nếu không có chỉ định này thì index sử dụng cùng filegroup mà table hoặc view dựa trên. ON "default" Tạo index dựa trên filegroup mặc định. Ví dụ 3.7. Tạo Index trên bảng LOP của CSDL QLDiemSV Use QLDiemSV create Unique index indTenLopind On LOp(TenLop) Ví dụ 3.8. Xây dựng lại Index TenLop_ind trên bảng LOP của CSDL QLDiemSV Use QLDiemSV create Unique index TenLop_ind On LOp(TenLop) With DROP_EXISTING Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 92 Ví dụ 3.9. Dùng từ khóa DBCC DBREINDEX Use QLDiemSV Go DBCC DBREINDEX(HOSOSV) c) Loại bỏ chỉ mục + Dùng Enterprise Manager: Trong cửa sổ Enterprise Manager, mở rộng danh mục Database, mở rộng cơ sở dữ liệu muốn xóa Index, chẳng hạn CSDL QLDiemSV và chọn mục Tables. Sau đó right click lên bảng muốn tạo chỉ mục. Chọn All Task\Manage Indexes. Xuất hiện của sổ Manage Indexes như hình 3.45. Chọn Index muốn xóa và chọn Delete. + Dùng SQL Server Management Studio: Trong SQL Server Management Studio, mở rộng danh mục Database, mở rộng cơ sở dữ liệu chứa bảng hoặc view muốn xóa chỉ số. Mở rộng mục Indexes của bảng hoặc view đó, right click lên Index muốn xóa và chọn Delete. + Dùng lệnh T-SQL: DROP INDEX | Chú ý: Đối với SQL Server 2005 ta có thể sử dụng cú pháp sau: DROP INDEX index_name ON Ví dụ 3.10. Loại bỏ chỉ mục TenLop_ind Use QLDiemSV Go DROP INDEX Lop. indTenLop Go 3.2.5. Lược đồ - Diagrams a) Tạo lược đồ * Dùng Enterprise Manager: • Trong Enterprise Manager, mở rộng danh mục Database, mở rộng cơ sở dữ liệu muốn tạo lược đồ, chẳng hạn CSDL QLDiemSV và chọn mục Diagrams. Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 93 • Right Click lên Diagrams, chọn New Database Design. Xuất hiện cửa sổ Wellcome to Create Database Wizard (Hình 3.50). Chọn Next. • Xuất hiện cửa sổ Cửa sổ Select Tables to be Added (hình 3.51). Chọn các bảng sẽ dùng để xây dựng lược đồ quan hệ. Chọn Next. • Cửa sổ Completing the Create Database Wizard (hình 3.52) chọn Finish. • Xuất hiện cửa sổ thiết kế Diagrams (hình 3.53). Trong cửa sổ này ta thực hiện thiết kế các mối quan hệ giữa các bảng bằng cách: Kéo và giữ chuột trên trường quan hệ của bảng này sau đó nhả vào trường quan hệ của bảng kia. • Xuất hiện cửa sổ Relationship (hình 3.54). Trong cửa sổ này ta chọn trường quan hệ giữa hai bảng và các điều kiện cho mối quan hệ đang tạo giữa hai bảng này. Hình 3.50. Cửa sổ Wellcome to Create Database Wizard Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin
Bài giảng Hệ quản trị CSDL 94 Hình 3.51. Cửa sổ Select Tables to be Added Hình 3.52. Cửa sổ Completing the Create Database Wizard Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin