Quản trị CSDL SQL Server và ngôn ngữ T-SQL - Bài 2: Quản lý và tạo CSDL

CHƢƠNG I 
QUẢN TRỊ CSDL SQL SERVER 
VÀ NGÔN NGỮ T-SQL 
BÀI 2: Quản lý và tạo CSDL 
1. Tổng quan về CSDL 
2. Kiến trúc CSDL quan hệ 
3. Quản lý CSDL SQL Server 
4. Các thao tác trên tập tin CSDL 
 Một Database tƣơng tự một tập tin dữ liệu, không trình bày 
thông tin một cách trực tiếp đến ngƣời dùng mà ngƣời dùng chạy 
một ứng dụng để truy xuất dữ liệu từ Database. 
 Trong SQL Server, để làm việc với dữ liệu trong một Database, 
bạn phải dùng một tập các lệnh và câu lệnh đƣợc hỗ trợ bởi 
DBMS. Bạn có thể dùng vài ngôn ngữ khác nhau nhƣng thông 
thƣờng là SQL. 
 CSDL trong SQL Server là CSDL quan hệ. 
 Có hai loại CSDL trong SQL Servser: CSDL hệ thống và CSDL 
ngƣời dùng. 
Quản lý CSDL SQL Server 
 Một cài đặt mới SQL Server 2000 tự động có 6 Database: 
 Master: ghi nhận toàn bộ hệ thống thông tin bao gồm tất cả 
các Database khác, các tài khoản đăng nhập và các cài đặt cấu 
hình hệ thống. 
 Tempdb: Lƣu tất cả các bảng tạm, các thủ tục. 
 Model: Là một database đƣợc dùng để tạo một Database mới. 
 Msdb: Để lập biểu các alerts và jobs và ghi nhận các toán tử. 
 Pubs, Norwind: là hai database ví dụ đƣợc dùng trong các tài 
liệu của SQL Server. 
Quản lý CSDL SQL Server 
SQL SERVER 
Master Tempdb Model Msdb 
System databases 
Payroll Sales Employees 
User databases 
Các Database hệ thống và Database của người dùng 
Quản lý CSDL SQL Server 
  Trong SQL Server 2000 dữ liệu đƣợc lƣu trữ trong các 
Database. 
 Về mặt vật lý: một Database bao gồm hai hay nhiều hơn hai 
tập tin trên một hay nhiều đĩa. Chỉ thấy đƣợc bởi nhà quản 
trị và nó trong suốt đối với ngƣời sử dụng 
 Về mặt Logic: một database đƣợc xây dựng thành các thành 
phần mà đƣợc hiển thị với ngƣời dùng nhƣ Table, View, 
Procedure, … 
Quản lý CSDL SQL Server 
Cấu trúc vật lý của một CSDL 
Một Database bao gồm ít nhất một tập tin dữ liệu (data file) và một tập 
tin log (Transaction log file). Các tập tin này không đƣợc chia sẻ với 
các database khác. 
Cấu trúc Logic của một CSDL 
Dữ liệu trong SQL đƣợc tổ chức trong một Database gồm các 
thành phần. 
Ràng buộc (Constraint) Bảng (Table) 
Mặc định (Default) Bẫy lỗi (Trigger) 
Chỉ mục (Index) Kiểu dữ liệu do ngƣời dùng định 
nghĩa(User_defined data type) 
Khóa (Key) Hàm do ngƣời dùng định 
nghĩa(User_defined function) 
Thủ tục (Stored procedure) Khung nhìn (View) 
Các tập tin CSDL SQL Server 
 CSDL Server 2000 cho phép 3 loại tập tin: 
 File dữ liệu cơ bản (Primary data file) (.mdf): mỗi CSDL chỉ 
có duy nhất 1 file cơ bản (mặc định), dùng để ghi nhận lại tất cả 
những tập tin khác trong CSDL và lƣu trữ dữ liệu. 
 Các file thứ cấp (Secondary data files) (.ndf) (tuỳ chọn): một 
CSDL có thể có hay không có nhiều file thứ cấp, dùng để lƣu 
các đối tƣợng của CSDL. 
 File nhật ký giao dịch (Transaction log file) (.ldf): mỗi 
CSDL có từ 1 hay nhiều file nhật ký, dùng để chứa những 
thông cần thiết cho việc phục hồi tất cả những giao tác 
(transaction) trong CSDL. 9 
Database 
Extent 
(8 contiguous 
8-KB pages) 
Page (8 KB) 
Tables, 
Indexes 
Data 
Max row size = 8060 bytes 
Data (file) 
.mdf or .ndf 
Log (file) 
.Idf 
P25 
Lưu trữ vật lý - Physical Storage 
Lưu trữ vật lý - Physical Storage 
 Đơn vị cơ bản để lƣu trữ CSDL là trang (page) 
 Trang là 1 khối 8KB nằm liên tục trên đĩa 
 File log không chứa các trang, nó là 1 chuỗi các record 
 Các trang dữ liệu chứa tất cả các dữ liệu của hàng (row), 
ngoại trừ kiểu dữ liệu text và image nằm trên các trang 
riêng. 
 Các hàng (row) của bảng không thể quá lớn để kéo dài từ 
trang này sang trang khác, vì vậy mỗi hàng bị giới hạn 
không thể lớn hơn 8KB 
 Các hàng dữ liệu nằm tuần tự trên mỗi trang ngay sau tiêu 
đề (header) của trang 
11 
Lưu trữ vật lý - Physical Storage 
 Page header chiếm 96 byte chứa thông tin hệ thống nhƣ loại 
trang, số không gian còn trống ,… 
12 
Hình ảnh 1 trang dữ liệu 
Lưu trữ vật lý - Physical Storage 
 Các trang đƣợc tổ chức thành các extent 
 Một extent là 1 tập hợp 8 trang liên tục nhau. 
 Một CSDL có 16 extents (128 pages) trên 1 MB 
 Extent đầu tiên của mỗi file CSDL đƣợc dùng riêng bởi 
SQL server để theo dõi việc phân phối dữ liệu trên đĩa 
13 
Files và filegroups 
 Khi tạo 1 CSDL, thì các file dữ liệu và log đƣợc tạo ra tại vị 
trí do ta xác định. 
 Các file này có nằm trên những đĩa vật lý khác nhau để cải 
thiện việc thực thi của hệ thống. 
 Filegroup có thể chứa 1 hay nhiều file. Một CSDL có thể 
đƣợc chứa trong 1 hay 1 số filegroup 
14 
Tạo CSDL bằng Enterprise 
Manager 
 Chọn ActionsNew Database 
 Hoặc nhấp phải chuột tại folder Databases, chọn lệnh New 
Database 
15 
Tạo CSDL bằng Enterprise Manager 
 Mở rộng nhánh Server group. 
 Mở rộng Server. 
 Click menu Tools, Click Wizard. 
 Mở rộng Database. 
 Double Click Create Database Wizard. 
 Finish steps in Wizard. 
Tạo CSDL bằng Database Wizard 
Tạo CSDL bằng Query Analyzer 
 Cú pháp lệnh tạo CSDL : 
CREATE DATABASE database_name 
[ ON 
[ [ ,...n ] ] 
[ , [ ,...n ] ] 
] 
[ LOG ON { [ ,...n ] } ] 
 Cú pháp Filespec: 
(NAME = logical_name, 
FILENAME = 'path\filename', 
SIZE = size_in_MB, 
MAXSIZE = size_in_MB | UNLIMITED, 
FILEGROWTH = %_or_MB) 
18 
CREATE DATABASE Sales 
ON PRIMARY 
( NAME = SPri1_dat, FILENAME = 
‘D:\BTSQL\SPri1dat.mdf', 
 SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), 
( NAME = SPri2_dat, FILENAME = 
‘D:\BTSQL\SPri2dt.ndf', 
 SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), 
FILEGROUP SalesGroup1 
( NAME = SGrp1Fi1_dat, FILENAME = 
‘D:\BTSQL\SG1Fi1dt.ndf', 
 SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) 19 
Tạo CSDL bằng Query Analyzer 
( NAME = SGrp1Fi2_dat, 
 FILENAME = ‘D:\BTSQL\SG1Fi2dt.ndf', 
 SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) 
LOG ON 
( NAME = 'Sales_log', 
 FILENAME = ‘D:\BTSQL\salelog.ldf', 
 SIZE = 5MB, 
 MAXSIZE = 25MB, 
 FILEGROWTH = 5MB ) 
GO 
20 
Tạo CSDL bằng Query Analyzer 
Kiểm tra sự tồn tại của CSDL 
 Cách 1: Tại cửa sổ Enterprise Manager. 
 Kiểm tra sự tồn tại của CSDL trong nhánh Database. 
 Cách 2: Tại cửa sổ Query Analyzer 
 Thực hiện câu lệnh Sp_helpdb 
 Ví dụ: Sp_helpdb Sales 
21 
Xem và thay đổi thuộc tính của 
CSDL 
 Cách 1: Dùng Enterprise Manager 
 Mở nút Database, Right_Click tại tên CSDL cần xem hay 
hiệu chỉnh. 
 Chọn Properties. 
 Thay đổi T_SQL 
22 
 Cách 2: Tại cửa sổ Query Analyzer, dùng lệnh T-SQL 
 ALTER DATABASE database_name 
 ADD FILE filespec [TO FILEGROUP filegroup_name] 
 ADD LOG FILE filespec 
 | REMOVE FILE logical_filename 
 | ADD FILEGROUP filegroup_name 
 | REMOVE FILEGROUP filegroup_name 
 | MODIFY FILE filespec 
 | MODIFY FILEGROUP filegroup_name 
 filegroup_property 
 |SET optionspec [WITH termination] 
23 
Xem và thay đổi thuộc tính của CSDL 
a) Chỉnh sửa Size của tập tin 
 ALTER DATABASE Sales 
 MODIFY FILE (NAME = ‘Sales_log’, size =10MB) 
b) Bổ sung thêm một tập tin dữ liệu 
 ALTER DATABASE Sales 
 ADD File (Name =Sales_data2, Filename 
=‘D:\BTSQL\Sales_data2.mdf,SIZE =10 MB, Maxsize 
=20MB) 
Xem và thay đổi thuộc tính của CSDL 
ALTER DATABASE database_name 
SET option [, status] 
Option 
AUTO_SHRINK 
CURSOR_CLOSE_ON_COMMIT 
RECOVERY FULL | BULK_LOGGED | SIMPLE 
SINGLE_USER | RESTRICTED_USER | ULTI_USER 
READ_ONLY | READ_WRITE 
 Example: 
ALTER DATABASE Sales 
SET Read_Only 
Xem và thay đổi thuộc tính của CSDL 
Hiển thị thông tin CSDL 
 Xem các thuộc tính của CSDL 
SELECT DATABASEPROPERTYEX(‘databasename’, 
‘property’) 
 Property: IsAutoShrink, IsCloseCursorsOnCommitEnabled, 
Recovery, Updateability, UserAccess 
 Kiểm tra sự tồn tại của CSDL 
 sp_helpdb TenCSDL 
 Kiểm tra không gian sử dụng của CSDL 
 sp_spaceused 
Đổi tên CSDL - Renaming a 
database 
 Đổi tên cơ sở dữ liệu: 
sp_renamedb [ @dbname = ] 'old_name', [ 
@newname = ] 'new_name‘ 
 VD: Sp_ReNamedb ‘Sales’, ‘Banhang’ 
27 
Mở CSDL - Openning a database 
 Mở cơ sở dữ liệu: 
USE 
 VD: Use master 
28 
Xoá CSDL - Dropping a database 
 Khi 1 CSDL bị xóa thì tất cả các file vật lý của nó sẽ bị xóa 
 Cú pháp: 
 DROP DATABASE database_name 
 Ví dụ: 
 Drop database Banhang 
 Không thể xóa các CSDL master, model, tempdb 
29 
Tạo một script cho CSDL và các đối 
tượng của CSDL 
- Mở rộng một server 
group, mở rộng một 
server. 
- Mở rộng nhánh Database, 
click phải tại CSDL muốn 
tạo script, chọn All Tasks, 
chọn General SQL 
Script… 
Tạo một script cho CSDL và các đối 
tượng của CSDL 
- Sử dụng Scrip vừa tạo: 
- Chuyển đến vị trí mới cần tái tạo lại CSDL/các đối 
tƣợng CSDL. 
- Vào cửa sổ Query Analazer, mở tập tin Script. 
- Hiệu chỉnh các vị trí vật lý nếu cần. 
- Cho thực thi đoạn Script. 
- Kiểm tra kết quả.