Chương 2. Tạo và quản lý cơ sở dữ liệu

10/03/2015 1 CHƯƠNG 2. TẠO VÀ QUẢN LÝ CƠ SỞ DỮ LIỆU TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH GV: Lê Thị Minh Nguyện Email: leminhnguyen@itc.edu.vn NỘI DUNG 2 Cấu trúc Lưu trữ trong SQL Server Các cơ sở dữ liệu được SQL hỗ trợ Bản chụp cơ sở dữ liệu (Database Snapshots) Bảng (Table) Tạo các ràng buộc Chỉ mục Khung nhìn 10/03/2015 2 1.Cấu trúc Lưu trữ trong SQL Server 3 3 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  Disk Memory 4 1.Cấu trúc Lưu trữ trong SQL Server 10/03/2015 3 2.Các cơ sở dữ liệu được SQL hỗ trợ 5 SQL Server hỗ trợ ba loại cơ sở dữ liệu: Cơ sở dữ liệu hệ thống Cơ sở dữ liệu người dùng định nghĩa Cơ sở dữ liệu ví dụ  Được tạo ra bởi SQL Server. Các cơ sở dữ liệu này được sử dụng để lưu thông tin về SQL Server..  Hơn nữa, các cơ sở dữ liệu này còn được sử dụng để quản lý các cơ sở dữ liệu người dùng .  Người sử dụng SQL Server có thể tạo các cơ sở dữ liệu người dùng định nghĩa  Mục đích của các cơ sở dữ liệu này là để lưu dữ liệu của người sử dụng  SQL Server cung cấp các cơ sở dữ liệu mẫu để người sử dụng làm việc với SQL Server  Trong SQL Server là AdventureWorks 2.1. Các cơ sở dữ liệu hệ thống 6 Cơ sở dữ liệu Mô tả master Cơ sở dữ liệu này lưu trữ các tất cả các thông tin về hệ thống của SQL Server. msdb Cơ sở dữ liệu này được sử dụng bởi dịch vụ SQL Server Agent để nhắc nhở các thực hiện công việc theo lịch biểu và các công việc khác. model Cơ sở dữ liệu này được sử dụng như là cơ sở dữ liệu mẫu cho tất cả các cơ sở dữ liệu được người dùng tạo ra sau này trong SQL Server. tempdb Các kết quả tạm thời, đối tượng tạm thời được tạo ra trong quá trình xử lý tính toán được lưu trong cơ sở dữ liệu này 10/03/2015 4 7 2.2.Cơ sở dữ liệu người dùng định nghĩa  Để tạo một cơ sở dữ liệu người dùng, các thông tin sau được yêu cầu:  Tên của cơ sở dữ liệu  Người sở hữu hoặc người tạo cơ sở dữ liệu  Kích thước của cơ sở dữ liệu  Các tập tin và nhóm các tập tin được sử dụng để lưu trữ cơ sở dữ liệu 8 CREATE DATABASE Tên_CSDL On Primary ( Name=logical_file_name , -- Tên file logic FileName='os_file_name ' -- Tên file vật lý Size=size [ KB | MB | GB | TB ] ] , -- Kích thước tập tin ban đầu MaxSize=max_size [ KB | MB | GB | TB ] | UNLIMITED], -- Kích thước tối đa FileGrowth=growth_increment -- Kích thước tăng trưởng ) Log On ( Name= logical_file_name, FileName=‘os_file_name', Size=size, MaxSize=max_size, FileGrowth= growth_increment ) ; Cú pháp: 2.2.Cơ sở dữ liệu người dùng định nghĩa 10/03/2015 5 9  Ví dụ: Tạo csdl có tên là QLSV CREATE DATABASE QLSV On Primary ( Name=QLSV_data, -- Tên file logic: QLSV_data FileName=‘D:\QLSV.mdf', -- Tên file vật lý: QLSV.mdf Size=50MB, -- Kích thước tập tin ban đầu:50MB MaxSize= Unlimited, -- Kích thước tối đa: Không giới hạn FileGrowth=10MB -- Kích thước tăng trưởng: 10 MB ) Log On ( Name=QLSV_log, FileName=‘D:\QLSV.ldf', Size=50MB, MaxSize=100MB, FileGrowth=10MB ) ; Go 2.2.Cơ sở dữ liệu người dùng định nghĩa 10 Dùng FILEGROUP 2.2.Cơ sở dữ liệu người dùng định nghĩa CREATE DATABASE MyDB ON PRIMARY ( NAME='MyDB_Primary', FILENAME= ‘D:\B_Prm.mdf', SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB), FILEGROUP MyDB_FG1 (NAME = 'MyDB_FG1_Dat1', FILENAME =‘D:\MyDB_FG1_1.ndf', SIZE = 1MB,MAXSIZE=10MB, FILEGROWTH=1MB), ( NAME = 'MyDB_FG1_Dat2', FILENAME = ‘D:\MyDB_FG1_2.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB) LOG ON ( NAME='MyDB_log', FILENAME = ‘D:\MyDB.ldf', SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB); 10/03/2015 6 11 2.2.Cơ sở dữ liệu người dùng định nghĩa  Lệnh mở CSDL để làm việc USE database_name  Lệnh xóa CSDL DROP DATABASE database_name  Đổi tên CSDL ALTER DATABASE database_name MODIFY NAME = new_database_name 12 2.2.Cơ sở dữ liệu người dùng định nghĩa Thay đổi kích thước các tập tin của CSDL ALTER DATABASE database_name MODIFY FILE (NAME = logical_file_name, SIZE = size)  Cú pháp tăng kích thước tập tin dữ liệu: Ví dụ: ALTER DATABASE QLSV MODIFY FILE (NAME = QLSV_data, SIZE = 60MB) USE database_name DBCC SHRINKFILE (NAME = logical_file_name , size)  Cú pháp giảm kích thước tập tin của csdl hiện hành: Ví dụ: Giảm kích thức tập tin dữ liệu của csdl QLSV xuống 20MB USE QLSV DBCC SHRINKFILE (NAME = QLSV_data , 20) 10/03/2015 7 13 2.2.Cơ sở dữ liệu người dùng định nghĩa  Chỉnh sửa kích thước tăng trưởng ALTER DATABASE database_name MODIFY FILE (NAME = logical_file_name, FILEGROWTH = size  Cú pháp Chỉnh sửa kích thước tăng trưởng : Ví dụ: Lệnh hủy khả năng tự tăng trưởng cho csdl QLSV ALTER DATABASE QLSV MODIFY FILE (NAME = QLSV_data, FILEGROWTH = 0) 14 Thêm một file dữ liệu vào file group ALTER DATABASE database ADD FILE(Name= logical_file_name, filename= 'os_file_name ‘, Size= size, MaxSize=max_size ) Ví dụ: Thêm một file dữ liệu vào file group PRIMARY ALTER DATABASE MyDB ADD FILE (NAME = ‘MyDB_FG1_Dat3' , FILENAME=D:\MyDB_FG1_3.ndf ', SIZE=10MB , MAXSIZE=20MB) 2.2.Cơ sở dữ liệu người dùng định nghĩa 10/03/2015 8 15 2.2.Cơ sở dữ liệu người dùng định nghĩa  Ví dụ thay đổi quyền sở hữu của CSDL: USE CUST_DB EXEC sp_changedbowner ‘sa’ USE CUST_DB; ALTER DATABASE CUST_DB SET AUTO_SHRINK ON  Ví dụ về thiết lập các lựa chọn cho CSDL:  Quyền sở hữu của một cơ sở dữ liệu người dùng có thể thay đổi bằng cách sử dụng thủ tục sp_changedbowner sp_changedbowner [ @loginame = ] ‘login’ 2.3.Cơ sở dữ liệu ví dụ 16  AdventureWorks là một cơ sở dữ liệu.  Một công ty giả sử được gọi là công ty Adventure Works Cycles , Adventure Works Cycles là một công ty lớn sản xuất hàng hóa đa quốc gia .  Cơ sở dữ liệu mẫu này gồm các phần sau:  AdventureWorks: cơ sở dữ liệu xử lý giao dịch trực tuyến (OLTP – Online Transaction Processing)  AdventureWorksDW: cơ sở dữ liệu kho (Data warehouse)  AdventureWorksAS: cơ sở dữ liệu dịch vụ phân tích (Analysis Services) Download tại: 10/03/2015 9 17 3. Bản chụp cơ sở dữ liệu (Database Snapshots)  Bản chụp cơ sở dữ liệu là bản chỉ đọc, khung nhìn tĩnh của một cơ sở dữ liệu SQL Server. Trong trường hợp một người dùng thực hiện một thao tác nhầm lẫn với cơ sở dữ liệu gốc, cơ sở dữ liệu gốc này có thể được phục hồi về trạng thái trước đó khi mà bản chụp đã được tạo.  Tạo một bản chụp CSDL: Cú pháp: CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name, FILENAME = ‘os_file_name’ ) [ ,...n ] AS SNAPSHOT OF source_database_name [;] 18 Tạo CSDL Tạo bản chụp Dữ liệu được sửa đổi Bản chụp được khôi phục 3. Bản chụp cơ sở dữ liệu (Database Snapshots) 10/03/2015 10 19 Ưu điểm Nhược điểm Bản chụp cung cấp một thuận tiện, một bản copy chỉ đọc của dữ liệu. Khi một bản chụp được truy vấn, không làm giảm hiệu suất của đối tượng được quan sát. Các tập tin dữ liệu của bản chụp là nhỏ và được tạo ra rất nhanh. Nó chỉ lớn khi cơ sở dữ liệu là chủ thể thay đổi thường xuyên. Bản sao của bản chụp không thể được tạo ra. Bản chụp phải tồn tại trên cùng máy chủ cơ sở dữ liệu cùng với cơ sở dữ liệu gốc. Một người sử dụng mới không thể được quyền truy cập vào dữ liệu trong bản chụp. Quyền được thừa kế từ cơ sở dữ liệu gốc khi nó đã tồn tại tại thời điểm tạo ra bản chụp. 3. Bản chụp cơ sở dữ liệu (Database Snapshots) 20 Tạo bảng chụp CSDL Phục hồi lại cơ sở dữ liệu từ bảng chụp CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks_Data, FILENAME = 'd:\AdventureWorks_data_1800.ss' ) AS SNAPSHOT OF AdventureWorks; USE master; AdventureWorks_dbss1800 RESTORE DATABASE AdventureWorks from DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800'; 3. Bản chụp cơ sở dữ liệu (Database Snapshots) 10/03/2015 11 4.Bảng (Table) 21 Khái niệm Kiểu dữ liệu Cấu trúc tạo bảng Thay đổi cấu trúc bảng Tính toàn vẹn trong cơ sở dữ liệu 4.1.Khái niệm 22 Dùng để lưu trữ các thông tin dữ liệu của những đối tượng, thực thể trong thế giới thực vào máy tính  Tên bảng (table name): dài tối đa 128 ký tự  Tên cột (column name)  Kiểu dữ liệu (Data type): quy định kiểu dữ liệu mà cột sẽ lưu trữ bên trong bảng 10/03/2015 12 4.2.Kiểu dữ liệu 23 Kiểu dữ liệu Kích thước Miền giá trị dữ liệu lưu trữ Số nguyên Int 4 bytes Từ -2,147,483,648 đến +2,147,483,648 Smallint 2 bytes Từ -32,768 đến + 32,767 Tinyint 1 byte Từ 0 đến 255 Bit 1 byte 0,1 hoặc Null Các kiểu dữ liệu dạng số thập phân Decimal, Numeric 17 byte Từ -10^38 đến +10^38 Các kiểu dữ liệu dạng số thực Float 8 bytes Từ -1.79E + 308 đến +1.79E + 308 Real 4 bytes Từ -1.79E + 308 đến +1.79E + 308 24 4.2.Kiểu dữ liệu Kiểu dữ liệu Kích thước Miền giá trị dữ liệu lưu trữ Các kiểu dữ liệu dạng chuỗi Char N bytes Từ 1 đến 8,000 ký tự, độ dài cố định Varchar N bytes Từ 1 đến 8,000 ký tự, độ dài biến đổi Text N bytes Từ 1 đến 2,147,483,647 ký tự Nchar 2* n bytes Unicode, từ 1 đến 4,000 ký tự, mỗi ký tự 2 bytes Nvarchar 2* n bytes Từ -10^38 đến +10^38 Ntext 2* n bytes từ 1 đến 1,073,741,823 ký tự, mỗi ký tự 1 byte Các kiểu dữ liệu dạng chuỗi nhị phân Image N byte Từ 1 đến 2,147,483,647 bytes Binary N byte Từ 1 đến 8,000 10/03/2015 13 25 4.2.Kiểu dữ liệu Các kiểu dữ liệu dạng ngày giờ datetime 8 bytes Từ 01/01/1753 đến 31/12/9999 smalldatetime 4 bytes Từ 01/01/1900 đến 06/06/2079 Date 3 bytes Từ 01-01-0001 đến 12-31-9999 Time 3-5 bytes 00:00:00.0000000 tới 23:59:59.9999999 DATETIMEOFF SET nhận biết được múi giờ và dựa trên hệ giờ 24 giờ. DATETIME2 là sự kết hợp của kiểu dữ liệu DATE và TIME mới và sử dụng 6 đến 8 byte Hàm SYSDATETIME trả về thời gian hiện tại của hệ thống mà không có múi giờ với độ chính xác 10 mili giây. Hàm SYSDATETIMEOFFSET giống với hàm SYSDATETIME, tuy nhiên hàm này bao gồm cả múi giờ. 4.3.Cấu trúc tạo bảng 26 Tạo cấu trúc bảng đơn giản CREATE TABLE ( [NOT NULL] [NOT NULL] [NOT NULL]) Ví dụ: CREATE TABLE LOP( Malop char(6) NOT NULL, TenLop varchar(20) NOT NULL, SiSo smallInt NOT NULL) 10/03/2015 14 4.3.Cấu trúc tạo bảng 27 Tạo cấu trúc bảng có giá trị mặc định CREATE TABLE ( Default gia_trị|hàm [NOT NULL] [NOT NULL]) Ví dụ: CREATE TABLE LOP( Malop char(6) NOT NULL, TenLop varchar(20) NOT NULL, SiSo smallInt Default 0) 4.3.Cấu trúc tạo bảng 28 Tạo cấu trúc bảng có cột định danh CREATE TABLE ( Identity[(số_bắt_đầu,chỉ_số_tăng)] [NOT NULL] [NOT NULL]) Ví dụ: CREATE TABLE LOP( Malop int Identity(1000,5), TenLop varchar(20) NOT NULL, SiSo smallInt NOT NULL) 10/03/2015 15 4.4.Thay đổi cấu trúc bảng 29  Thêm một cột mới vào bảng • ALTER TABLE Tên_bảng ADD Tên_cột kiểu_dữ_liệu • Ví dụ: ALTER TABLE LOP ADD Nien_khoa int  Hủy bỏ cột hiện có bên trong bảng • ALTER TABLE Tên_bảng DROP COLUMN Tên_cột • Ví dụ: ALTER TABLE LOP DROP COLUMN Nien_khoa  Sửa đổi kiểu dữ liệu của cột • ALTER TABLE Tên_bảng ALTER COLUMN Tên_cột • Ví dụ: ALTER TABLE LOP ALTER COLUMN Nien_khoa char(9) 4.4.Thay đổi cấu trúc bảng 30  Đổi tên cột, tên bảng dữ liệu • EXEC sp_rename ‘Tên_bảng[.tên_cột]’, ‘Tên_mới’ [, ‘COLUMN’] • Ví dụ 1: đổi tên cột Nien_khoa thành khoa_hoc trong bảng LOP EXEC sp_rename ‘LOP.nien_khoa’, ‘khoa_hoc’ • Ví dụ 2: thay đổi tên LOP thành LOPHOC EXEC sp_rename ‘LOP’, ‘LOPHOC’ 10/03/2015 16 4.5.Tính toàn vẹn trong cơ sở dữ liệu 31  Ràng buộc toàn vẹn (RBTV) dữ liệu thực sự là việc đặt ra các quy tắc trong một cơ sở dữ liệu nhằm kiểm tra các giá trị của dữ liệu trước khi lưu trữ phải đảm bảo tính chính xác và hợp lý bên trong một cơ sở dữ liệu.  Các Phương Pháp Ðảm Bảo Data Integrity  Data Type  Not Null Definitions  Default Definitions  Identity Properties  Constraints  Rules  Triggers  Indexes 5.Tạo các ràng buộc 32 Ràng buộc khóa chính (Primary Key Constraint) Ràng buộc duy nhất (Unique Constraint) Ràng buộc khóa ngoại (Foreign Key Constraint) Ràng buộc niềm giá trị (Check Constraint) Kiểu dữ liệu do người dùng định nghĩa Rule Giá trị mặc định (Default value) Xóa các đối tượng ràng buộc 10/03/2015 17 5.1.Ràng buộc khóa chính 33 Primary Key Constraint CREATE TABLE PhongBan( MaPhong Char(5) NOT NULL , TenPhg VARCHAR(20) ) Hay CREATE TABLE PhongBan( MaPhong Char(5), TenPhg VARCHAR(20)) CONSTRAINT pk_PhongBan PRIMARY KEY (MaPhong) ) 34 Hay ALTER TABLE ADD CONSRAINT <tên RBTV> PRIMARY KEY(thuộc tính khoá chính) Ví dụ: ALTER TABLE PhongBan ADD CONSRAINT PK_PhongBan PRIMARY KEY(MaPhong) Chú ý: khi thêm khoá chính thì cột MaPhong trong bảng PhongBan buộc phải khác NULL 5.2.Ràng buộc khóa chính 10/03/2015 18 35 5.3.Ràng buộc duy nhất Unique Constraint  Cách tạo ra Unique Constraint cũng tương tự như Primary Key Constraint chỉ việc thay chữ Primary Key thành Unique  Ví dụ: CREATE TABLE PhongBan( MaPhong Char(5) PRIMARY KEY, TenPhong VARCHAR(30) UNIQUE, MaTrphg char(10) UNIQUE)  Chỉ có Một PRIMARY KEY CONSTRAINT trong một table trong khi ta có thể có NHIỀU UNIQUE CONSTRAINT trên một table 5.4.Ràng buộc khóa ngoại 36  Foreign Key Constraint  CREATE TABLE NHANVIEN( MANV CHAR(10) PRIMARY KEY, MAPHONG CHAR(5) REFERENCES PHONGBAN(MaPhong)) Hay CREATE TABLE NHANVIEN( MANV CHAR(10) PRIMARY KEY, MAPHONG CHAR(5), CONSTRAINT FK_NHANVIEN_MAPHONG FOREIGN KEY (MAPHONG) REFERENCES PHONGBAN (MaPhong)) 10/03/2015 19 37 Hay ALTER TABLE ADD CONSRAINT <tên RBTV> FOREIGN KEY(các cột khoá ngoại) REFERENCES (các cột khoá chính) [on update cascade][on delete cascade] [on update set null][on delete set null] Ví dụ: ALTER TABLE NHANVIEN ADD CONSRAINT fk_nhanvien_maphong FOREIGN KEY(MAPHONG) REFERENCES PHONGBAN(MaPhong) 5.4.Ràng buộc khóa ngoại 5.5.Ràng buộc miền giá trị 38  Check Constraint (ràng buộc miền giá trị) CREATE TABLE Table1( Col1 INT PRIMARY KEY, Col2 INT CHECK (Col2 BETWEEN 0 AND 1000), Col3 VARCHAR(30)) Hay CREATE TABLE Table1 ( Col1 INT PRIMARY KEY, Col2 INT, Col3 VARCHAR(30), CONSTRAINT Chk_col2 CHECK (Col2 BETWEEN 0 AND 1000)) Hay: Alter table add constraint check Ví dụ: Alter table Table1 add constraint chk_col2 check(cot2 between 0 and 1000) 10/03/2015 20 39 5.6.Kiểu dữ liệu do người dùng định nghĩa Mục đích đảm bảo cho cấu trúc dữ liệu bên trong CSDL được nhất quán và dễ sửa đổi. Cú pháp: EXEC sp_addtype Tên_kiểu_dl_mới, „kiểu_dữ_liệu_cơ_sở‟ [,NULL| NOT NULL] 40 5.6.Kiểu dữ liệu do người dùng định nghĩa Ví dụ: EXEC sp_addtype uddt_Soluong, ‘Decimal(15, 2)’, ‘NOT NULL’ Khi tạo lập cấu trúc bảng TONKHO chúng ta có thể sử dụng CREATE TABLE TONKHO( MaVT char(4), Ngay datetime, SLDK uddt_Soluong, TSLNhap uddt_Soluong, TSLXuat uddt_Soluong, SLCK uddt_Soluong ) 10/03/2015 21 5.7.Rule 41 Kiểm tra các cột bên trong bảng phải thoả mãn điều kiện nào đó khi dữ liệu bị sửa đổi hoặc thêm vào. Tính năng hoạt động của nó gần giống như CHECK constraint Cú pháp: CREATE RULE Tên_qui_tắc AS Biểu_thức Tên_quy_tắc: kiểm tra miền giá trị dữ liệu được tạo mới Biểu thức: biểu thức luận lý hoặc so sánh. Có 3 thành phần: •Tên biến hình thức (phải bắt đầu bằng @) •Toán tử so sánh •Giá trị so sánh 42 Ví dụ 1: Để tạo quy tắc kiểm tra miền giá trị dữ liệu cột số lượng phải dương. CREATE RULE rule_Soluong_duong AS @Soluong>0 Ví dụ 2: Để tạo quy tắc kiểm tra miền giá trị dữ liệu cột đơn vị tính của vật tư chỉ thuộc trong các từ: cái, bộ, kg, m2, m3. CREATE RULE rule_Dvtinh_Hople AS @Dvt IN (‘Cái’, ‘Bộ’, ‘Kg’, ‘m2’, ‘m3’) 5.7.Rule 10/03/2015 22 43 Áp dụng quy tắc kiểm tra rule  Cú pháp: EXEC sp_bindrule Tên_qui_tắc, Tên_đối_tượng  Ví dụ 1: Áp dụng quy tắc kiểm tra có tên rule_Dvtinh_Hople cho cột đơn vị tính trong bảng VATTU EXEC sp_bindrule rule_Dvtinh_Hople,‘VATTU.Dvtinh’  Ví dụ 2: Áp dụng quy tắc kiểm tra có tên là rule_Soluong_Duong cho kiểu dữ liệu mới có tên là uddt_soluong EXEC sp_bindrule rule_Soluong_Duong, ‘Uddt_Soluong’ 5.7.Rule 44 5.7.Rule Gỡ bỏ quy tắc kiểm tra  Cú pháp: EXEC sp_unbindrule Tên_đối_tượng  Ví dụ EXEC sp_unbindrule „uddt_Soluong‟ EXEC sp_unbindrule ‘VATTU.Dvtinh’ 10/03/2015 23 5.8.Giá trị mặc định 45  Cú pháp: CREATE DEFAULT tên_giá_trị_mặc_định AS Biểu_thức Trong đó:  Tên_giá_trị_mặc_định: tên giá trị mặc định phải duy nhất trong CSDL.  Biểu thức: là một giá trị cụ thể nào đó như số, ngày, chuỗi hoặc một hàm, biểu thức tính toán.  Ví dụ: tạo giá trị mặc định cho các cột điện thoại là chuỗi “chưa có” CREATE DEFAULT Def_Dienthoai AS „Chưa có‟ 45 46 Liên kết các giá trị mặc định vào dữ liệu Cú pháp: EXEC sp_bindefault Tên_mặc_định, Tên_đối_tượng  Ví dụ: Để liên kết giá trị mặc định vừa tạo def_Dienthoai vào cột DienThoai có trong bảng NHACC EXEC sp_bindefault Def_Dienthoai, „NHACC.Dienthoai‟ 5.8.Giá trị mặc định 10/03/2015 24 47 Không liên kết giá trị mặc định vào cột Cú pháp: EXEC sp_unbindefault tên_đối_tượng  Ví dụ: để huỷ bỏ giá trị mặc định đã liên kết cho cột điện thoại trong bảng NHACC trước đây. EXEC sp_unbindefault „NHACC.Dienthoai‟ 5.8.Giá trị mặc định  Xóa rule DROP Rule Tên_rule  Ví dụ: Drop Rule rule_Soluong_Duong  Xóa Default DROP DEFAULT Tên_gt_mặc_định [,]  Ví dụ: DROP DEFAULT def_Dienthoai 5.9.Xóa đối tượng 48 10/03/2015 25 Cấu trúc lưu trữ của bản ghi(Record) Khái niệm chỉ mục Tổng quan về chỉ mục Cấu trúc chỉ mục cơ bản Cú pháp tạo chỉ mục 6.Chỉ mục (Index) 49 6.1.Cấu trúc lưu trữ của bản ghi Tất cả các record trong 1 bảng được tổ chức trong 1 file và được quản lý bởi hệ quản trị cơ sở dữ liệu (không như các file bình thường được quản lý bởi hệ điều hành). Mỗi khi có 1 yêu cầu truy vấn dữ liệu thì hệ quản trị cơ sở dữ liệu (DBMS) sẽ xác định và trả về những dòng của các bảng quan hệ đáp ứng được câu truy vấn của người dùng, cách thức xử lý yêu cầu phụ thuộc vào cách tổ chức lưu trữ các record trong các file này. Tạo chỉ mục để lưu thứ tự sắp xếp các bản ghi theo giá trị tăng dần của các cột được cho trong danh sách 50 10/03/2015 26 6.2.Khái niệm chỉ mục Chỉ mục (Index) là một đối tượng trong Datatable (hệ quản trị cơ sở dữ liệu) – là 1 cấu trúc dữ liệu được sử dụng nhằm tăng tốc độ tìm kiếm trong 1 table, nó giống như phần mục lục của mỗi cuốn sách giúp ta đến chổ cần đọc nhanh hơn. 51 6.3.Tổng quan về chỉ mục Cấu trúc của 1 chỉ mục bao gồm ít nhất 2 phần là Khóa tìm kiếm và con trỏ trỏ tới mẫu tin tương ứng trong bảng dữ liệu. 52 10/03/2015 27 Cấu trúc B-tree 6.3.Tổng quan về chỉ mục 53 6.4.Cấu trúc chỉ mục cơ bản Cấu trúc của chỉ mục (Index) có thể được chia làm 2 loại  Clustered Index  Non-clustered Index. 54 10/03/2015 28 Clustered Index: chỉ có thể tạo một cluster index duy nhất cho một bảng dữ liệu.  Mặc định khóa chính sẽ thành cluster index  Dữ liệu của bảng sắp xếp theo thứ tự của cluster index 6.4.Cấu trúc chỉ mục cơ bản 55 Non Cluster Index: có thể tạo 249 non cluster index cho một bảng dữ liệu.  Dữ liệu của bảng không sắp theo thứ tự của non-cluster index.  Thường tạo index cho các cột dữ liệu dùng để join hay trong điều kiện where hoặc giá trị cột này thường xuyên thay đổi. 56 6.4.Cấu trúc chỉ mục cơ bản 10/03/2015 29 Cú pháp tạo Index  Cú pháp: CREATE [CLUSTERED |NONCLUSTERED] INDEX index_name ON table_name (column_name[,column_name])  Ví dụ: CREATE NONCLUSTERED INDEX index_mh ON MonHoc(TenMH) 57 6.4.Cấu trúc chỉ mục cơ bản Nhận xét Nhận xét  Tìm kiếm nhanh trong trường hợp so sánh với hằng số và phép kết  Làm chậm đi các thao tác thêm, xóa và sửa  Tốn chi phí • Lưu trữ chỉ mục • Truy xuất đĩa nhiều Chọn lựa cài đặt chỉ mục hợp lý??? 58 6.4.Cấu trúc chỉ mục cơ bản 10/03/2015 30 Primary and foreign keys Frequently searched in ranges Frequently accessed in sorted order Seldom referenced in queries Containing few unique values Defined with bit, text, or image data types  Creating index  Speeds up data access  Enforces uniqueness of rows  Donot creating index  Consumes disk space  Incurs overhead 59 6.4.Cấu trúc chỉ mục cơ bản Bài tập Trong CSDL có bảng SinhVien(MaSV,TenSV, TuoiSV, DiaChi) MaSV là khóa chính. Khóa chính thường dùng để join với các bảng khác, khóa chính và tên thường xuất hiện trong điều kiện where của các câu truy vấn thông tin. Hãy xác định một cluster và một non-cluster index cho bảng SinhVien. Viết câu lệnh SQL để tạo bảng và tạo các index tương ứng. 60 10/03/2015 31 7.Khung nhìn (View) Bảng là một quan hệ được tổ chức lưu trữ vật lý trong CSDL. Khung nhìn cũng là một quan hệ  Không được lưu trữ vật lý (bảng ảo)  Không chứa dữ liệu  Đối tượng bên trong nó chỉ lưu trữ duy nhất một câu lệnh SELECT  Các dữ liệu được lấy từ bảng cơ sở  Có thể thêm, xóa, sửa được trong bảng ảo 61 Cú pháp: DROP VIEW 62 CREATE VIEW [(tên_các_cột)] [WITH ENCRYPTION] //mã hoá nội dung câu lệnh AS Câu lệnh SELECT [WITH CHECK OPTION]// ngăn cản các thao tác cập nhật dữ liệu (thêm, sửa) tác động trực tiếp vào bảng ảo không thoả điều kiện trong mệnh đề WHERE. 7.Khung nhìn (View) 10/03/2015 32 Một số từ khoá chuẩn không được dùng trong VIEW:  ORDER BY  COMPUTE (thống kê dữ liệu cuối cùng)  COMPUTE BY (thống kê dữ liệu theo từng nhóm)  SELECT INTO  Ví dụ: CREATE VIEW V_HANG_TV AS SELECT MaMH, TenMH, DonGia, Loai FROM HangHoa WHERE Loai = ‘TV’  Xem kết quả của View: Select * from  Ví dụ: Select * KQ_DAU 63 7.Khung nhìn (View) Ví dụ: Xem lại nội dung câu lệnh SELECT bên trong View EXEC Sp_helptext  Ví dụ: Sp_helptext KQ_DAU 64 7.Khung nhìn (View) CREATE VIEW V_HANG_TV WITH ENCRYPTION AS SELECT MaMH, TenMH, DonGia, Loai FROM HangHoa WHERE Loai = ‘TV’ (kết quả trả về thông báo: “The text for object 'V2' is encrypted.”) 10/03/2015 33 65 TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH