Bài giảng Database

1Chương 2: DATABASE Giáo viên: Tạ Thúc Nhu Khoa CNTT trường Đại Học Lạc Hồng HỆ QUẢN TRỊ CSDL SQL SERVER SQL SERVER2 NỘI DUNG I. CÁC THÀNH PHẦN CỦA MỘT CSDL II. LOẠI FILE LƯU TRỮ DATABASE III. TẠO DATABASE IV. XÓA DATABASE V. TÁCH DATABASE KHỎI SQL SERVER VI. GẮN DATABASE ĐÃ TÁCH VÀO SQL SERVER VII. THAY ĐỔI THÔNG TIN CSDL VIII. XEM THÔNG TIN CSDL SQL SERVER3 I- CÁC THÀNH PHẦN CỦA MỘT CSDL 1. Tables : lưu trữ dữ liệu cần quản lý. Table còn có các thành phần liên quan như : a. Constraint – Ràng buột: chỉ định ràng buột dữ liệu trong bảng hoặc giữa các bảng. b. Triggers – Bẫy Lỗi: Trigger thường chứa các mã lệnh kiểm tra dữ liệu, có tính năng tự động thực hiện khi có hành động nào đó xảy ra đối với dữ liệu trong Table như Insert, Update, Delete. c. Indexs – Chỉ mục : Hổ trợ cho việc sắp xếp và tìm kiếm nhanh thông tin trên table. 2. Database Diagram – Sơ đồ CSDL: Thể hiện mối quan hệ dữ liệu giữa các table. 3. Views – Khung nhìn hay table ảo: dùng hiển thị dữ liệu được rút trích từ các Table. 4. Stored Procedure – Thủ tục nội: Chứa các lệnh T-SQL dùng thực hiện một số tác vụ nào đó. Các Stored Proc hệ thống có tiền tố “sp_” rất có ích cho việc quản trị. 5. Functions : Hàm do người dùng định nghĩa 6. Type : Kiểu dữ liệu do người dùng tự định nghĩa 7. Rules : Chứa các qui tắc ràng buộc dữ liệu được lưu trữ trên Table 8. Defaults : Chứa các khai báo giá trị mặc định được sử dụng trong CSDL 9. Full Text Catalogs : Tập phân loại dữ liệu Text. 10.Users : Chứa danh sách User sử dụng CSDL. 11.Roles : Các qui định vai trò và chức năng của User trong CSDL SQL SERVER4 II- LOẠI FILE LƯU TRỮ DATABASE 2SQL SERVER5 1. File dữ liệu: – File chính (Primary data file): Chứa thông tin hệ thống và một số đối tượng chính của database. Mỗi CSDL chỉ có 1 file dữ liệu chính có phần mở rộng thông thường là *.MDF. – Các file dữ liệu phụ (Secondary data files): Do người dùng tạo thêm (nếu cần). Đối với các CSDL lớn không chứa đủ trên một ổ đĩa, khi đó cần có các file phụ lưu trên các ổ đĩa khác. Các file dữ liệu phụ tiếp theo nên có phần mở rộng là NDF để phân biệt. Các file dữ liệu được lưu theo từng nhóm (File Groups). Mỗi CSDL có một nhóm file chính (PRIMARY) chứa file dữ liệu chính và một số file dữ liệu phụ. 2. File nhật ký (Log files): Có ít nhất 1 file nhật ký, dùng lưu nhật ký thao tác cập nhật dữ liệu (Transaction log) trên các table của CSDL, nhằm mục đích phục hồi dữ liệu vừa thay đổi khi có yêu cầu. SQL SERVER6 II- Các CSDL Hệ Thống Của SQL SERVER: • Master: có tên file là Master.MDF. Là CSDL lưu trữ các thông tin hệ thống cho một thể hiện của SQL Server: Các tài khoản đăng nhập, cấu hình hệ thống, thông tin về các CSDL đã tạo, các thủ tục hệ thống thực hiện các tác vụ quản trị hệ thống, các thủ tục mở rộng của người dùng tạo thêm… • MSDB: có tên file là MSDBData.MDF, dùng hổ trợ dịch vụ lập lịch cảnh báo và thực hiện công việc. Nếu xóa CSDL này, bạn phải cài đặt lại nó khi cần dùng hoặc khi hệ thống yêu cầu. • Model : có tên file là Model.MDF, chứa các template (như kích thước CSDL, các chọn lựa trên CSDL) dùng làm mẫu để tạo CSDL mới. • Tempdb: lưu trữ các đối tượng tạm thời hoặc tập kết quả trung gian của các hoạt động trên SQL. CSDL tempdb tự khởi tạo lại mỗi khi SQL Server được khởi động lại. SQL SERVER7 III- TẠO ĐỐI TƯỢNG DATABASE 1. Sử dụng Object Explorer : 2. Bằng Lệnh CREATE DATABASE: SQL SERVER8 1- Sử dụng Object Explorer: Bước 1: Click phải vào Databases, chọn New Database 3SQL SERVER9 Bước 2: Khai báo các thông tin cần thiết cho CSDL mới và click nút OK. – Database Name: Tên CSDL phân biệt – Logical Name: Tên đại diện của file – Path và File Name: Tên lưu trên đĩa của file – File Type: Loại file dữ liệu (Data) hay nhật ký (Log) – Filegroup : Nhóm chứa file dữ liệu. – Initial Size: Kích thước (MB) ban đầu của File – AutoGrowth: chỉ định chế độ tăng tự động kích thước file SQL SERVER10 2- Bằng Lệnh CREATE DATABASE CREATE DATABASE database_name [ ON [PRIMARY] ( ) , … n ] [ , FILEGROUP filegroup_name ( ) , … n ] ] [ LOG ON ( ) , … n ] ] Trong đó : = ( NAME = logical_name , FILENAME = 'd:\Path\FileName' , [ , SIZE = size [KB | MB | GB | TB] ] [ , MAXSIZE = { max_size [KB|MB|GB|TB] | UNLIMITED} ] [ , FILEGROWTH = growth_increment [KB|MB|GB|TB| %] ] ) SQL SERVER11 THÔNG TIN FILE • Database_name : Tên Database phải phân biệt trên cùng server (tối đa 128 ký tự) • ON : Khai báo thông tin các file dữ liệu và nhóm file • LOG ON : Khai báo thông tin các file nhật ký. Nếu không chỉ định LOG ON, SQL tự tạo một file nhật ký cho database. • PRIMARY: chỉ định các file theo sau thuộc nhóm file chính. Trong các file đó, file đầu tiên là file dữ liệu chính. • FILEGROUP: Khai báo thêm nhóm file mới chứa các file dữ liệu phụ. • NAME : Tên logic của File. Tên này được sử dụng trong các phát biểu của T- SQL. Yêu cầu phân biệt. • FILENAME : Tên và địa chỉ file lưu trên đĩa. • SIZE : Kích thước ban đầu của file, mặc định 2 MB. • MAXSIZE : kích thước tối đa của file. Nếu không chỉ định thì kích thước file sẽ tăng cho đến khi đĩa đầy (UNLIMITED). • FILEGROWTH : Khai báo số gia khi tăng kích thước File. Mặc định là 10%. SQL SERVER12 Ví dụ 1: CREATE DATABASE BanHang ON ( NAME = ‘Sale_dat’, FILENAME = ‘c:\data\BH.mdf’, SIZE = 15MB, MAXSIZE = 50MB, FILEGROWTH = 20% ) LOG ON ( NAME = ‘Sale_log’, FILENAME = ‘c:\data\BH.ldf’, SIZE = 5MB, MAXSIZE = 20MB, FILEGROWTH = 1MB ) Để liệt kê thông tin CSDL BANHANG dùng lệnh: SELECT * FROM SYS.DATABASES WHERE NAME = ‘BANHANG’ 4SQL SERVER13 VD 2: Tạo CSDL có nhiều file CREATE DATABASE BanHang ON PRIMARY ( NAME = Sales_dat1, FILENAME = 'C:\DATA\SALE1.MDF', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) , ( NAME = Sales_dat2, FILENAME ='C:\DATA\SALE2.NDF', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log1', FILENAME = 'C:\DATA\SALE1.LDF', SIZE = 5, MAXSIZE = 20, FILEGROWTH = 5 ) , ( NAME = 'Sales_log2', FILENAME = 'C:\DATA\SALE2.LDF', SIZE = 5, MAXSIZE = 20, FILEGROWTH = 5 ) SQL SERVER14 VD3: Tạo CSDL có nhiều nhóm file CREATE DATABASE BanHang ON PRIMARY ( NAME = Sales_dat1, FILENAME = 'C:\DATA\SALE1.MDF', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), FILEGROUP MYGROUP ( NAME = Sales_dat2, FILENAME ='C:\DATA\SALE2.NDF', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log1', FILENAME = 'C:\DATA\SALE1.LDF', SIZE = 5, MAXSIZE = 20, FILEGROWTH = 5 ), ( NAME = 'Sales_log2', FILENAME = 'C:\DATA\SALE2.LDF', SIZE = 5, MAXSIZE = 20, FILEGROWTH = 5 ) SQL SERVER15 V- XÓA DATABASE 1. Sử dụng Object Explorer 2. Bằng lệnh DROP DATABASE SQL SERVER16 1- Sử dụng Object Explorer: Click phải vào tên CSDL cần xóa, chọn Delete. 5SQL SERVER17 2- Bằng lệnh DROP DATABASE Cú pháp: DROP DATABASE database_name [, … n] Ví dụ: Xóa 2 CSDL: SieuThi và BanHang DROP DATABASE SieuThi, BanHang Đoạn lệnh kiểm tra sự tồn tại của CSDL trước khi xóa: If Exists(Select ‘True’ From Sys.Databases Where Name = ’BanHang’) Drop Database BanHang SQL SERVER18 V- Tách CSDL Khỏi SQL Server Mục đích: Để di chuyển CSDL sang máy chủ SQL Server khác 1. Sử dụng Object Explorer 2. Sử dụng thủ tục sp_detach_db SQL SERVER19 1- Sử dụng Object Explorer Click phải vào tên CSDL, chọn Tasks Detach … SQL SERVER20 2- Bằng thủ tục sp_detach_db Cú pháp: sp_detach_db 'TênCSDL‘ Ví dụ: Exec sp_detach_db ‘BanHang‘ 6SQL SERVER21 VI- Gắn CSDL Đã DETACH Vào SQL Server Để tái tạo CSDL đã Detach, ta cần có các file dữ liệu. Các file log tự động tạo mới nếu không có. • Sử dụng Object Explorer • Bằng lệnh Create Database SQL SERVER22 1- Bằng Object Explorer: Bước 1: Click phải chuột vào Databases và chọn Attach… SQL SERVER23 Bước 2: Click nút Add để mở hộp thoại chọn file chính của CSDL SQL SERVER24 Bước 3: Click nút Ok để gắn CSDL vào SQL Server 7SQL SERVER25 2- Bằng lệnh Create Database CREATE DATABASE DatabaseName ON ( ) FOR ATTACH CREATE DATABASE SieuThi ON PRIMARY (FILENAME = 'C:\Data\BanHang.MDF') FOR ATTACH SQL SERVER26 VII- SỬA THÔNG TIN CSDL Mục đích: Đổi tên CSDL, thêm hay xóa file và nhóm file hoặc thay đổi các thông tin của file. 1. Sử dụng Object Explorer 2. Bằng lệnh ALTER DATABASE: sử dụng cú pháp: ALTER DATABASE TênCSDL MODIFY NAME = TênMớiCSDL | MODIFY FILE (Name = LogicalName , Thông tin file>) | ADD FILE [,...n] | ADD LOG FILE [,...n] | REMOVE FILE SQL SERVER27 1- Sử dụng Object Explorer Click phải chuột vào tên CSDL, chọn Properties. SQL SERVER28 2- Bằng lệnh ALTER DATABASE ALTER DATABASE database_name MODIFY NAME = new_database_name | MODIFY FILE (Name = logical_name , ) | ADD FILE [,...n] | ADD LOG FILE [,...n] | REMOVE FILE logical_name 8SQL SERVER29 2.1 Đổi tên CSDL : ALTER DATABASE databasename MODIFY NAME = new_dbname Ví dụ: Đổi tên CSDL BANHANG thành QLHOADON ALTER DATABASE BANHANG MODIFY NAME = QLHOADON Chú ý: Có thể sử dụng thủ tục : EXEC SP_RENAMEDB ‘OldName’, ‘NewName’ SQL SERVER30 2.2 Sửa thông tin file : ALTER DATABASE database_name MODIFY FILE (Name = LogicalName , ) Thông tin file: • NEWNAME = new_logical_name • FILENAME = ‘Disk:\Path\TenFile’ • SIZE = size [ KB | MB | GB | TB ] • MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } • FILEGROWTH = growth_num[ KB | MB | GB | TB| % ] Mỗi câu lệnh chỉ được phép thay đổi 1 trong các thành phần này. SQL SERVER31 Ví dụ • Tăng kích thước file Sale_dat.MDF có tên logic là sale_dat ALTER DATABASE BanHang MODIFY FILE (NAME = Sale_dat, SIZE = 20MB) • Di chuyển file sang thư mục khác hoặc đổi tên file: ALTER DATABASE BanHang MODIFY FILE (NAME = Sale_dat, FILENAME = 'E:\Data\Sale1.mdf') • Đổi tên logic của file dữ liệu hoặc file log: ALTER DATABASE BanHang MODIFY FILE (NAME = Sale_dat, NEWNAME = BH_dat). SQL SERVER32 2.3 Thêm file chứa dữ liệu cho database ALTER DATABASE databasename ADD FILE [,...n] [ TO FILEGROUP filegroup_name ] Ví dụ: Thêm file phụ chứa dữ liệu ALTER DATABASE BanHang ADD FILE ( NAME = datBH2, FILENAME = 'c:\data\BH2.ndf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ) 9SQL SERVER33 2.4 Thêm File Log cho Database ALTER DATABASE databasename ADD LOG FILE [,...n] Ví dụ: Thêm 2 file Log kích thước 5-MB cho Database ALTER DATABASE BanHang ADD LOG FILE ( NAME = BHlog2, FILENAME = 'C:\Data\BH2.ldf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB), ( NAME = Testlog3, FILENAME = 'c:\Data\BH3.ldf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) SQL SERVER34 2.5 Xoá data file hoặc log file của CSDL ALTER DATABASE databasename REMOVE FILE Ví dụ: Xóa file có Name = Sale_dat2 của CSDL BANHANG ALTER DATABASE BanHang REMOVE FILE Sale_dat2 SQL SERVER35 VIII- XEM THÔNG TIN CSDL: 1. Sử dụng Object Explorer 2. Sử dụng table hệ thống 3. Sử dụng các thủ tục hệ thống SQL SERVER36 1- Sử dụng Object Explorer Click phải vào tên CSDL và chọn Properties… 10 SQL SERVER37 2- Sử dụng table hệ thống: Thông tin của các Database được lưu trữ trong các table hệ thống bao gồm: Name, size, owner, ID, creation date, và các thông tin trạng thái. 1. Bảng Sys.Databases: Select * From Sys.Databases 2. Bảng SysDatabases trong CSDL Master: Select * From Master.dbo.SysDatabases SQL SERVER38 2- Sử dụng các thủ tục hệ thống: Kích thước của current database VD: Use SieuThi EXEC SP_SPACEUSED SP_SPACEUSED Hiển thị thông tin của database được chỉ định, gồm : name, size, owner, ID, creation date, and status information. Ngoài ra còn cho biết chi tiết các file dữ liệu và log file. VD: EXEC SP_HELPDB ‘SieuThi’ SP_HELPDB database_name Hiển thị thông tin của tất cả Databases trên Server gồm: name, size, owner, ID, creation date, and status information. VD: EXEC SP_HELPDB SP_HELPDB DescriptionSystem stored procedure SQL SERVER39 Yêu cầu: • Tìm hiểu cấu trúc của Sys.Databases • Tìm hiểu cấu trúc của table Sysdatabases trong CSDL Master. Hãy so sánh với cấu trúc Sys.Databases.