Chương 2 Tạo và quản trị Cơ sở dữ liệu Nội dung Tạo và quản trị Database Các kiểu dữ liệu Tạo và quản lý bảng Ràng buộc toàn vẹn đơn giản Chỉ mục Databases in SQL Server Database Storing data and other database objects Database Snapshot Maintain historical data for report generation SQL Server Enterprise Edition Safeguard data against administrative error Safeguard data against user error Microsoft Database in SQL Server Overview of Database Objects Tables Indexes Views Programmability • Data storage & • Improves • Logical result • Stored Retrieval query sets Procedures • Referential performance • Based on • Functions integrity • Clustered SELECT • Triggers • Non-clustered queries • Constraints 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, … Microsoft Database in SQL Server Có tối thiểu 2 tập tin trong CSDL: • 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.
Microsoft Database in SQL Server Lưu trữ vật lý Database Data (file) Log (file) .Idf Tables, Extent Indexes (8 contiguous 8-KB pages) Data Page (8 KB) Max row size = 8060 bytes Microsoft Database in SQL Server • Đơ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 Microsoft Database in SQL Server 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 ,… Microsoft Database in SQL Server • 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 Microsoft Files and 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. Có 3 loại: Primary filegroup, user-define filegroups và default filegroup Microsoft Files and Filegroups • Primary FileGroup: chứa file dữ liệu chính (.mdf) và bất cứ file thứ cấp nào (. Tất cả các bảng hệ thống phải nằm trong primary filegroup.
• User-defined filegroup: do người dùng xác định trong lệnh CREATE/ALTER DATABASE • Default filegroup: là bất kỳ filegroup nào trong DB. Thường thì primary filegroup chính là default filegroup nhưng owner có quyền thay đổi. Tất cả bảng và index mặc định đều được tạo ra trong default filegroup. Microsoft Creating a New Database Cú pháp lệnh tạo CSDL : CREATE DATABASE database_name [ ON [ < filespec > [ ,.n ] ] ] [ LOG ON { < filespec > [ ,.n ] } ] Cú pháp Filespec: (NAME = logical_name, FILENAME = 'path\filename', SIZE = size_in_MB, MAXSIZE = size_in_MB | UNLIMITED, FILEGROWTH = %_or_MB) Microsoft Creating a New Database Some arguments: The name of the database The size of the database The files where the database will reside CREATE DATABASE Sample ON PRIMARY ( NAME=SampleData, FILENAME='c:\Program Files\.mdf', SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=SampleLog, FILENAME= 'c:\Program Files\.ldf', SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB) COLLATE SQL_Latin1_General_Cp1_CI_AS Microsoft Creating a New Database CREATE DATABASE Sales ON PRIMARY ( NAME = Sales1_dat, FILENAME = 'D:\BTSQL\Sales1_dat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), ( NAME = Sales2_dat, FILENAME = 'D:\BTSQL\Sales2_dat.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), FILEGROUP SalesGroup1 ( NAME = Sales3_dat, FILENAME = 'D:\BTSQL\Sales3_dat.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = Sales4_dat, FILENAME = 'D:\BTSQL\Sales4_dat.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log', FILENAME = 'D:\BTSQL\salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) Microsoft Creating a New Database (từ menu) Microsoft Attaching an Existing Database Microsoft Creating a Snapshot Database Microsoft Managing Databases Managing Data and Log File Growth Monitoring and Expanding a Transaction Log Shrinking a Database or File Dropping a Database Microsoft Managing Database Hiển thị thông tin DB Mở CSDL USE TenCSDL Ví dụ: use Sales 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 Microsoft Managing Databases Cú pháp lệnh thay đổi cấu trúc CSDL 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] Microsoft Managing Databases Ví dụ thay đổi cấu trúc 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 =Sales5_dat, Filename ='D:\BTSQL\Sales5_dat.mdf',SIZE =10 MB, Maxsize =20MB) Microsoft Managing Data and Log File Growth Using Automatic File Growth Expanding Database Files Adding Secondary Database Files ALTER DATABASE Sample MODIFY FILE ( NAME = 'SampleLog', SIZE = 15MB) GO ALTER DATABASE Sample ADD FILE (NAME = SampleData2, FILENAME='c:\Program Files\.ndf', SIZE=15MB, MAXSIZE=20MB) Microsoft GO Managing Database Xem các thuộc tính của CSDL SELECT DATABASEPROPERTYEX('databasename', 'property') Property: IsAutoShrink, IsCloseCursorsOnCommitEnabled, Recovery, Updateability, UserAccess Trị trả về: 1 = TRUE, 0 = FALSE, NULL = Input not valid select DATABASEPROPERTYEX('Sales', 'IsAutoShrink') Microsoft Managing Data and Log File Growth Thay đổi thuộc tính DB 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 Microsoft Managing Data and Log File Growth Đổi tên cơ sở dữ liệu: sp_renamedb [ @dbname = ] 'old_name', [ @newname = ] 'new_name‘ VD: Sp_ReNamedb 'Sales', 'Banhang' Microsoft Managing Data and Log File Growth Xóa cơ sở dữ liệu: 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 Chú ý: Không thể xóa các CSDL master, model, tempdb Microsoft Monitoring and Expanding a Transaction Log Monitoring the log Monitoring situations that produce extensive log activity Mass loading of data into indexed table Large transactions Performing logged text or image operations Expanding the log when necessary Microsoft Outline Data Definition Language Managing Databases Data Types Managing Tables Microsoft System Data Types Có 2 nhóm: System-Supplied datatype: Các kiểu dữ liệu cơ bản được hỗ trợ bởi SQL Server.
User-defined datatype: Các kiểu dữ liệu của người dùng tự định nghĩa dựa trên các kiểu dữ liệu cơ bản. Microsoft System Data Types Microsoft System Data Types Microsoft User-defined Data Type Tạo một User-Defined Data Type Dùng thủ tục hệ thống sp_addtype để tạo một user- defined data type. sp_addtype type, system_data_type [,'NULL' | 'NOT NULL'] Ví dụ 1: Tạo kiểu dữ liệu tên là isbn với kiểu dữ liệu cơ bản là smallint và không chấp nhận giá trị Null EXEC sp_addtype isbn, 'smallint', 'NOT NULL' Microsoft User-defined Data Type Ví dụ 2: Tạo kiểu dữ liệu tên là zipcode với kiểu dữ liệu cơ bản là char, độ dài tối đa là 10 và chấp nhận giá trị Null EXEC sp_addtype zipcode, 'char(10)', NULL Ví dụ 3: Tạo kiểu dữ liệu tên là longstring với kiểu dữ liệu cơ bản là varchar, độ dài tối đa là 63 và chấp nhận giá trị Null EXEC sp_addtype longstring, 'varchar(63)', NULL Microsoft User-defined Data Type – Tạo từ menu Nhắp phải tại Programmability \Types\User- Defined Data Types, chọn New User Defined Data Type…) Microsoft User-defined Data Type Xem các user-defined data types trong CSDL hiện hành: Dùng thủ tục sp_help hoặc truy vấn trong information_schema.domains Ví dụ: Use Sales Sp_help hoặc SELECT domain_name, data_type, character_maximum_length FROM information_schema.domains ORDER BY domain_name Microsoft User-defined Data Type Xoá một User-Defined Data Type: dùng thủ tục hệ thống sp_droptype để xóa một user-defined data type từ bảng systypes. Một user-defined data type không thể xóa được nếu nó được tham chiếu bởi các bảng và những đối tượng khác.
Cú pháp: sp_droptype type Ví dụ: EXEC sp_droptype isbn Microsoft Bảng dữ liệu - Table Bảng là một đối tượng của CSDL được dùng để lưu trữ dữ liệu. Dữ liệu trong bảng được tổ chức thành các hàng (rows) và cột (columns). Mỗi hàng trong bảng biểu diễn một bản ghi (record) duy nhất. Mỗi cột biểu diễn một thuộc tính (attribute).
Tên cột trong 1 bảng không được trùng nhau nhưng cho phép tên cột có thể trùng nhau trong những bảng khác nhau của cùng 1 CSDL. SQL Server cho phép: Tối đa 2 triệu bảng trong 1 CSDL. Tối đa 1024 cột trong 1 bảng Tối đa 8060 bytes trong 1 hàng Microsoft Bảng dữ liệu - Table Các bước tạo một bảng Bước 1: Xác định kiểu dữ liệu của các cột. Bước 2: Xác định các cột có thể hoặc không thể có giá trị rỗng (null value).
Bước 3: Xác định các cột phải có các giá trị duy nhất. Bước 4: Xác định khóa chính – khóa ngoại. Bước 5: Xác định các giá trị mặc định. Bước 6: Xác định các ràng buộc trên các cột (mô tả miền trị).
Bước 7: Tạo bảng và các chỉ mục của bảng. Microsoft Tạo bảng - CREATE TABLE CREATE TABLE [ database_name.] table_name ({ < column_definition > |column_name AS computed_column_expression | < table_constraint > ::= [ CONSTRAINT constraint_name ] } [ { PRIMARY KEY | UNIQUE } [ ,.n ] ) [ ON { filegroup | DEFAULT } ] [ TEXTIMAGE_ON { filegroup | DEFAULT } ] Microsoft Tạo bảng - CREATE TABLE Cú pháp CREATE TABLE <Table_Name> (<Column_Name> <Data_Type>,….) Ví dụ CREATE TABLE Sanpham ( MaSP CHAR(5), TenSP VARCHAR(15), Dvt VARCHAR(10), DonGia SMALLMONEY, SLTon INT ) Microsoft Tạo bảng - CREATE TABLE IDENTITY [ ( seed , increment )] Tạo giá trị gia tăng duy nhất cho 1 cột, và cột này thường được dùng khoá chính cho bảng.