Bài giảng Cơ sở dữ liệu - Chương 2: Xây dựng, quản lý và khai thác Cơ sở dữ liệu - Phạm Nguyên Thảo

Trường Đại học Khoa học Tự nhiên Khoa Công nghệ Thông tin Bộ môn Hệ thống Thông tin Chương 2: Xây dựng, quản lý và khai thác Cơ sở dữ liệu Phạm Nguyên Thảo pnthao@fit.hcmuns.edu.vn 2 Nội dung • Khái niệm Database • Tạo và quản lý 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 • Các thao tác trên dữ liệu 3 CSDL - Logic • Một database là một tập hợp chứa: – Các bảng (tables) chứa dữ liệu có cấu trúc và các ràng buộc(constraint) định nghĩa trên các bảng – Các khung nhìn (view) – Các thủ tục/ hàm – Các vai trò (role) và người dùng (user) – • Trên một server có thể có tối đa 32.767 CSDL 4 CSDL – lưu trữ vật lý • Một database của SQL Server được lưu trữ bởi 3 loại tập tin: – Tập tin dữ liệu (data file) 1 tập tin dữ liệu chính (primary data file), thường có đuôi “mdf” – Chứa các dữ liệu khởi đầu của database  0-n tập tin dữ liệu thứ cấp (secondary data file), thường có đuôi “ndf” – Chứa các dữ liệu không lưu trữ hết trong tập tin dữ liệu chính. 5 CSDL – lưu trữ vật lý – Tập tin nhật ký giao tác (transaction log file) 1-n tập tin nhật ký, thường có đuôi “ldf” – Chứa các thông tin về nhật ký giao tác, dùng để phục hồi database sau khi xảy ra sự cố. 6 Nội dung • Khái niệm Database • Tạo và quản lý Database • Kiểu dữ liệu • Tạo và quản lý bảng • Ràng buộc toàn vẹn đơn giản • Các thao tác trên dữ liệu 7 Tạo Database • Cú pháp lệnh tạo Database Create Database database_name [ On [Primary] { file_spec [,n] } ] [ Log on { file_spec [,n] } ] • Lưu ý: Cú pháp đầy đủ của lệnh nên được xem thêm trong Book Online, vào phần index, gõ câu lệnh cần tra cứu. 8 Tạo Database (tt) Với file_spec :: = ( Name = logical_file_name , Filename = 'os_file_name ' [ , Size = size [ KB | MB | GB | TB ] ] [ , Maxsize = { max_size | Unlimited } ] [ , Filegrowth = growth_increment ] ) • Mặc định, các tập tin dữ liệu và log được lưu trong thư mục MSSQL\ Data của thư mục cài đặt SQL Server 9 Tạo Database – ví dụ • Ví dụ 1: Create Database QLSinhVien • Ví dụ 2: Create Database QLSinhVien On ( Name = QLSV_Data Filename = „C:\ ...\ QLSV_Data.mdf ‟, Size = 1, Filegrowth = 10% ) 10 Tạo Database – ví dụ (tt) • Ví dụ 3 Create Database QLSinhVien On ( Name = QLSV_Data1, Filename = „C:\ ...\ QLSV_Data.mdf ‟, Size = 1, Maxsize = 10 MB, Filegrowth = 1 MB ) , ( Name = QLSV_Data2 , Filename = „C:\...\QLSV_Data1.ndf‟ ) Log on ( Name = QLSV_Log, Filename = „D:\...\QLSV_Log.ldf‟ ) 11 Thay đổi và xoá Database • Xoá một CSDL đã tồn tại: Drop Database database_name • Thay đổi một CSDL Alter Database database_name . – Thêm/xoá/ thay đổi các các tập tin – Thay đổi các tùy chọn cho database 12 Nội dung • Khái niệm Database • Tạo và quản lý Database • Kiểu dữ liệu • Tạo và quản lý bảng • Ràng buộc toàn vẹn đơn giản • Các thao tác trên dữ liệu 13 Kiểu dữ liệu được SQL Server cung cấp • Số – Số nguyên: bit, tinyint, smallint, int, bigint – Số thực Floating point: – float(n) – real = float(24) Fixed point – Decimal(p,s) – Numeric(p,s) 14 Kiểu dữ liệu được SQL Server cung cấp (tt) • Chuỗi char(n); nchar(n) varchar(n); nvarchar(n)  text; ntext • Ngày giờ datetime; smalldatetime 15 Kiểu người dùng tự định nghĩa • Định nghĩa một kiểu dữ liệu: sp_addtype type_name, system_type [, „null_type‟ ] – Ví dụ: Exec sp_addtype MyString, nvarchar(88), „NULL‟ • Xóa một kiểu dữ liệu người dùng định nghĩa: sp_droptype „type_name’ 16 Nội dung • Khái niệm Database • Tạo và quản lý Database • Kiểu dữ liệu • Tạo và quản lý bảng • Ràng buộc toàn vẹn đơn giản • Các thao tác trên dữ liệu 17 Tạo bảng • Tạo bảng – Xác định các cột (các thuộc tính) của bảng – Xác định khóa chính – Xác định các thuộc tính null/ not null – Xác định thuộc tính identity (nếu có) (bắt buộc phải là kiểu số nguyên mới có thuộc tính này) • Lưu ý: – Luôn tạo khóa chính cho một bảng – Ràng buộc khóa ngoại nên được tạo sau khi đã tạo xong tất cả các bảng liên quan. 18 Tạo bảng (tt) – Cú pháp lệnh tạo bảng Create table Table_name ( { Column_name Data_type [null | not null] [default default_value ] [identity [( seed, increment)] ] } [,n] [, [constraint constraint_name] primary key ( Column_name [,n] ) ] [, [constraint constraint_name] foreign key ( Column_name [,n] ) referencesTable_name ] ) – Lưu ý có thể tạo khóa chính/khóa ngoại trực tiếp khi có một thuộc tính, e.g. “column_name type primary key” và “column_name type references table_name” – Tên constraint là không bắt buộc – Có thể tạo khóa ngoại ngay khi tạo bảng 19 Tạo bảng – ví dụ Create table HOCSINH ( STT tinyint not null, Lop char(5) not null default ‟11A1‟ , HoTen nvarchar(30) not null, NgaySinh datetime not null, DiaChi nvarchar(100), constraint pk_HS primary key (STT, Lop) ) 20 Thay đổi cấu trúc bảng / xóa bảng • Thay đổi cấu trúc bảng Alter table – Thêm/ xoá/ cập nhật kiểu dữ liệu của một cột (column) – Thêm/ xoá/ kiểm tra/ không kiểm tra ràng buộc (constraint) – Enable/ disable trigger • Xóa bảng Drop table – Nếu không có bảng này sẽ báo lỗi bảng không tồn tại. – Có thể kiểm tra nếu có thì mới xóa để tránh lỗi bằng lệnh if exists(SFW) dùng table hệ thống SysObjects. 21 Thay đổi cấu trúc bảng – ví dụ Alter table HOCSINH Add DanToc nvarchar(20) null default „Kinh‟ Alter table HOCSINH Alter column NgaySinh SmallDatetime not null 22 Quản lý bảng (tt) • Các tên bảng, tên ràng buộc không được trùng nhau trong cùng một database. • Tên các cột trong cùng một bảng không được trùng nhau. • Thông tin về các bảng, các ràng buộc được lưu trong bảng hệ thống sysobjects – Ví dụ: đọc thông tin về các bảng trong database hiện hành: Select * from sysobjects where type = „U‟ 23 Quản lý bảng (tt) • Một số thủ tục SQL Server cung cấp để quản lý bảng và cấu trúc bảng: – sp_databases – sp_tables [„table_name‟] [, „owner‟][,‟database_name‟][, “ ‟type‟ ”] Ví dụ: Exec sp_tables null, null, null, “ „TABLE‟ ” – sp_help [object_name]  sp_help cho biết các thông tin về đối tượng bất kỳ trong database (đối tượng có chứa trong sysobjects)  Ví dụ: Exec sp_help HOCSINH – sp_columns object [, owner] [, database] [,column] – sp_helpconstraint „table_name‟ – 24 Nội dung • Khái niệm database • Tạo và quản lý database • Kiểu dữ liệu • Tạo và quản lý bảng • Ràng buộc toàn vẹn đơn giản • Các thao tác trên dữ liệu 25 Ràng buộc toàn vẹn đơn giản • SQL cung cấp sẵn cơ chế để kiểm tra • Bao gồm: – Có thể chứa giá trị rỗng hay không: null/not null Giá trị mặc định: default(value) Các RB này được khai báo ngay khi tạo bảng và khai báo cho từng cột. – Khóa chính (primary key constraint) – Khoá ngoại (foreign key constraint) – Giá trị duy nhất (unique constraint) Hữu dụng khi cài đặt thêm các khóa ứng viên khác ngoài khóa chính. – Check constraint ( Kiểm tra ràng buộc miền giá trị hoặc ràng buộc liên thuộc tính trên một quan hệ) • Có thể khai báo ràng buộc trong lúc tạo bảng hoặc sau khi bảng đã tồn tại 26 Khai báo ràng buộc trong lúc tạo bảng • Cú pháp: Create table Table_name ( [, [constraint Constraint_name ] { primary key (Column_name [,n]) | unique ( Column_name [,n]) | check ( logical_expression ) } | foreign key ( Column_name [,n]) references Table ( Column_name [,n]) on_delete_update_handling ] [n] ) 27 Khai báo ràng buộc trong lúc tạo bảng – Ví dụ Create table SinhVien ( MaSV char(10) not null, HoTen nvarchar(30) not null, Nam tinyint, CMND char(10), Khoa char(5), constraint pk_SV primary key (MaSV), constraint u_CMND unique (CMND), constraint chk_Nam check (Nam > 0 and Nam <= 4) ) 28 Khai báo ràng buộc trên bảng đã tồn tại • Cú pháp: Alter table table_name [with check| with nocheck] Add { constraint constraint_name { primary key ( column_name [,n] ) | unique ( column_name [,n] ) | check ( logical_expression ) | foreign key ( column_name [,n] ) references ref_table ( ref_column [,n] ) [ on delete {cascade| no action | set null | set default } ] [ on update {cascade| no action | set null | set default } ] } [,n] 29 Khai báo ràng buộc trên bảng đã tồn tại – Ví dụ Alter table SINHVIEN with check add constraint u_CMND unique (CMND), constraint chk_Nam check (Nam in (1, 2, 3, 4) ), constraint fk_SV_maKhoa foreign key (Khoa), references KHOA(MaKhoa) /* giả sử đã tồn tại bảng KHOA( MaKhoa, ) */ 30 Kiểm tra / không kiểm tra ràng buộc • Cú pháp : Alter table Table_name {Check| Nocheck} constraint { All | constraint_name [,n] } • Ví dụ alter table SINHVIEN nocheck constraint u_CMND, chk_Nam 31 Xoá ràng buộc • Cú pháp Alter table table_name Drop { [constraint ] constraint_name } [,n] • Ví dụ: Alter table SINHVIEN drop constraint u_CMND, chk_Nam 32 Rule • Rule là một qui định chung được tạo ra trong một database • Một rule có thể được áp dụng cho nhiều thuộc tính của nhiều bảng khác nhau, hoặc cho các kiểu dữ liệu người dùng định nghĩa trong database . 33 Tạo rule • Cú pháp: Create rule rule_name as logical_expression – “logical_expression” phải chứa một biến. Biến này tương ứng với đối tượng sẽ được áp dụng rule. • Ví dụ: create rule r_SoDuong as @value >0 34 Kết buộc/ gỡ kết buộc rule • Kết buộc rule sp_bindrule „rule_name‟, „object‟, [ „futureonly‟ ] Tùy chọn futureonly chỉ dùng khi kết buộc rule với kiểu dữ liệu người dùng định nghĩa, có nghĩa các cột thuộc kiểu dữ liệu này trước đó không bị ảnh hưởng bởi rule Ví dụ: sp_bindrule „r_SoDuong‟, „SinhVien.Nam‟ Rule mới kết buộc sẽ ngầm gỡ rule cũ trên đối tượng • Gỡ kết buộc sp_unbindrule „object‟, [ „futureonly‟ ] Ví dụ: sp_unbindrule „SinhVien.Nam‟ 35 Xoá rule • Cú pháp Drop rule {rule_name} [,n] • Chỉ xóa được rule khi nó không còn kết buộc với đối tượng nào. • Nếu ngược lại, khi còn đang kết buộc nhưng vẫn còn xóa đi, phải gỡ hết các kết buộc mới có thể xóa. 36 Nội dung • Khái niệm database • Tạo và quản lý database • Kiểu dữ liệu • Tạo và quản lý bảng • Ràng buộc toàn vẹn đơn giản • Các thao tác trên dữ liệu 37 Thêm/ xoá/ cập nhật dữ liệu • Chú ý khi thêm/ xóa / cập nhật – Dữ liệu nhập phải phù hợp với kiểu dữ liệu – Đảm bảo các ràng buộc toàn vẹn – Chú ý định dạng của các giá trị: kiểu chuỗi unicode N‟giá trị chuỗi Tiếng Việt có dấu hoặc Unicode‟ kiểu datetime „13 Jan 09‟ hay ‟13/01/09‟ hay „01/13/09‟ biểu thức nào đúng? Nên dùng set dateformat dmy; để định dạng kiểu ngày tháng dùng ở Việt Nam. – Nhập giá trị rỗng dùng từ khóa null (không phải chuỗi „null‟) 38 Các dạng lệnh insert • Thêm từng dòng dữ liệu vào bảng Insert [into] Table_name[ (column_name[,n] )] values ( value [,n] ) [, values (value [,n] ), [,n] ] • Thêm các dòng dữ liệu từ bảng khác Insert [into] Table_name Select_statement • Lưu ý: trong câu select, ta có thể đọc dữ liệu từ các bảng trong database khác. Khi đó, tên bảng được viết đầy đủ như sau: Database_name.Owner.Table_name – Ví dụ: select * from QLSinhVien.dbo.SinhVien 39 Truy vấn