Bài giảng SQL server - Chương 2: Tạo và quản lý cơ sở dữ liệu - Lê Thị Minh Nguyện

10/03/2015 TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH CHƯƠNG 2. TẠO VÀ QUẢN LÝ CƠ SỞ DỮ LIỆU GV: Lê Thị Minh Nguyện Email: [email protected] NỘI DUNG 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 2 1 10/03/2015 1.Cấu trúc Lưu trữ trong SQL Server  Disk Memory Database Data (file) Log (file) .mdf or .ndf .Idf Tables, Extent Indexes (8 contiguous 8-KB pages) Data Page (8 KB) 3 Max row size = 8060 bytes 3 1.Cấu trúc Lưu trữ trong SQL Server 4 2 10/03/2015 2.Các cơ sở dữ liệu được SQL hỗ trợ SQL Server hỗ trợ ba loại cơ sở dữ liệu:  Đượ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.. Cơ sở dữ liệu hệ thống  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 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 để Cơ sở dữ liệu ví dụ người sử dụng làm việc với SQL Server  Trong SQL Server là AdventureWorks 5 2.1. Các cơ sở dữ liệu hệ thống 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 6 3 10/03/2015 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 7 2.2.Cơ sở dữ liệu người dùng định nghĩa Cú pháp: 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 ) ; 8 4 10/03/2015 2.2.Cơ sở dữ liệu người dùng định nghĩa  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 9 2.2.Cơ sở dữ liệu người dùng định nghĩa Dùng FILEGROUP 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 5 10/03/2015 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 11 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  Cú pháp tăng kích thước tập tin dữ liệu: ALTER DATABASE database_name MODIFY FILE (NAME = logical_file_name, SIZE = size) Ví dụ: ALTER DATABASE QLSV MODIFY FILE (NAME = QLSV_data, SIZE = 60MB)  Cú pháp giảm kích thước tập tin của csdl hiện hành: USE database_name DBCC SHRINKFILE (NAME = logical_file_name , size) 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) 12 6 10/03/2015 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  Cú pháp Chỉnh sửa kích thước tăng trưởng : ALTER DATABASE database_name MODIFY FILE (NAME = logical_file_name, FILEGROWTH = size 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) 13 2.2.Cơ sở dữ liệu người dùng định nghĩa 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) 14 7 10/03/2015 2.2.Cơ sở dữ liệu người dùng định nghĩa  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’  Ví dụ thay đổi quyền sở hữu của CSDL: USE CUST_DB EXEC sp_changedbowner ‘sa’  Ví dụ về thiết lập các lựa chọn cho CSDL: USE CUST_DB; ALTER DATABASE CUST_DB SET AUTO_SHRINK ON 15 2.3.Cơ sở dữ liệu ví dụ  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: 16 8 10/03/2015 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 [;] 17 3. Bản chụp cơ sở dữ liệu (Database Snapshots) BảnTạoDữ liệuchụpbản đượcchụpTạo được CSDL sửa khôi đổi phục 18 9 10/03/2015 3. Bản chụp cơ sở dữ liệu (Database Snapshots) Ưu điểm Nhược điểm Bản chụp cung cấp một thuận Bản sao của bản chụp không thể tiện, một bản copy chỉ đọc của dữ được tạo ra. liệu. Bản chụp phải tồn tại trên cùng Khi một bản chụp được truy vấn, máy chủ cơ sở dữ liệu cùng với cơ không làm giảm hiệu suất của đối sở dữ liệu gốc. tượng được quan sát. Các tập tin dữ liệu của bản Một người sử dụng mới không thể chụp là nhỏ và được tạo ra rất được quyền truy cập vào dữ liệu nhanh. Nó chỉ lớn khi cơ sở dữ trong bản chụp. Quyền được thừa kế từ cơ sở dữ liệu gốc khi nó đã liệu là chủ thể thay đổi tồn tại tại thời điểm tạo ra bản thường xuyên. chụp. 19 3. Bản chụp cơ sở dữ liệu (Database Snapshots) Tạo bảng chụp CSDL CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks_Data, FILENAME = 'd:\AdventureWorks_data_1800.ss' ) AS SNAPSHOT OF AdventureWorks; Phục hồi lại cơ sở dữ liệu từ bảng chụp USE master; AdventureWorks_dbss1800 RESTORE DATABASE AdventureWorks from DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800'; 20 10 10/03/2015 4.Bảng (Table) 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 21 4.1.Khái niệm 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 22 11 10/03/2015 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ữ 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, 17 byte Từ -10^38 đến +10^38 Numeric 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 23 4.2.Kiểu dữ liệu Kiểu dữ liệu Kích Miền giá trị dữ liệu lưu trữ thước 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 24 12 10/03/2015 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 nhận biết được múi giờ và dựa trên hệ giờ 24 giờ. SET 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ờ. 25 4.3.Cấu trúc tạo bảng 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) 26 13 10/03/2015 4.3.Cấu trúc tạo bảng 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) 27 4.3.Cấu trúc tạo bảng 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) 28 14 10/03/2015 4.4.Thay đổi cấu trúc bảng . 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) 29 4.4.Thay đổi cấu trúc bảng . Đổ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’ 30 15 10/03/2015 4.5.Tính toàn vẹn trong cơ sở dữ liệu  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 31  Indexes 5.Tạo các ràng buộc 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 32 16 10/03/2015 5.1.Ràng buộc khóa chính 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) ) 33 5.2.Ràng buộc khóa chính 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 34 17 10/03/2015 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 35 5.4.Ràng buộc khóa ngoại  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)) 36 18 10/03/2015 5.4.Ràng buộc khóa ngoại 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) 37 5.5.Ràng buộc miền giá trị  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) 38 19 10/03/2015 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] 39 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 ) 40 20 10/03/2015 5.7.Rule 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 41 5.7.Rule 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’) 42 21 10/03/2015 5.7.Rule Á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’ 43 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’ 44 22 10/03/2015 5.8.Giá trị mặc định  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 45 45 „Chưa có‟ 5.8.Giá trị mặc định 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‟ 46 23 10/03/2015 5.8.Giá trị mặc định 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‟ 47 5.9.Xóa đối tượng  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 48 24 10/03/2015 6.Chỉ mục (Index) 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 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 25 10/03/2015 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 26 10/03/2015 6.3.Tổng quan về chỉ mục Cấu trúc B-tree 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 27 10/03/2015 6.4.Cấu trúc chỉ mục cơ bản 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 55 6.4.Cấu trúc chỉ mục cơ bản 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 28 10/03/2015 6.4.Cấu trúc chỉ mục cơ bản 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 29 10/03/2015 6.4.Cấu trúc chỉ mục cơ bản  Creating index  Speeds up data access  Enforces uniqueness of rows Primary and foreign keys Frequently searched in ranges Frequently accessed in sorted order  Donot creating index  Consumes disk space  Incurs overhead Seldom referenced in queries Containing few unique values Defined with bit, text, or image data types 59 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 30 10/03/2015 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 7.Khung nhìn (View) Cú pháp: 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. DROP VIEW 62 31 10/03/2015 7.Khung nhìn (View)  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ụ: CREATE VIEW V_HANG_TV WITH ENCRYPTION AS SELECT MaMH, TenMH, DonGia, Loai FROM HangHoa WHERE Loai = ‘TV’ Xem lại nội dung câu lệnh SELECT bên trong View EXEC Sp_helptext  Ví dụ: Sp_helptext KQ_DAU (kết quả trả về thông báo: “The text for object 'V2' is encrypted.”) 64 32 10/03/2015 65 TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH 33