Chương 2 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU

1. KHOA CÔNG NGHỆ THÔNG TIN Chương 2NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU

2. I. LÀM VIỆC VỚI DATABASE 1. TẠO DATABASE Cú pháp lệnh tạo Database CREATE DATABASE DatabaseName [ON <Thông tin File DữLiệu>) ,… ] [LOG ON ( <Thông tin File Log>) ,… ] Trong dó : <Thông tin File> là: ( FILENAME = 'd:\Path\FileName' [, NAME = LogicalName ] [, SIZE = <Size MB or KB>] [, MAXSIZE = < MaxSize > ] [, FILEGROWTH = <No of KyloByte or Percentage>] ) •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.

3. 1. TẠO DATABASE Giải thích: Database_name : Tên Database tối đa 128 ký tự • ON : Khai báo các file dữu liệu, File đầu tiên được liệt kê trong phát biểu là “primary file.” các File tiếp theo là “Secondary File” • NAME = ‘LogicalName’ : Tên logic của File lưu trữ CSDL. Tên này được sử dụng trong câu lệnh T-SQL, Yêu câu phân biet. • FILENAME = 'FileName' : Tên lưu trên đĩa. Bao gôm đường dẫn • SIZE = <size MB or KB> : Kích thước File ban đầu theo đơn vị MB, Thấp nhất 1MB. • MAXSIZE= max_size : Chỉ định kích thước tối đa mà file có thể tăng. Nêu không chỉ định, kích thức file sẽ tăng cho đên khi đia đây. • FILEGROWTH : Khai báo số gia khi tăng kích thước File, không được lớn hơn MaxSize. Mặc định là 10% và giá trị nhỏ nhât là 64 KB. • LOG ON : Khai báo các file dùng lưu trữ nhật ký thao tác trên database. Nêu không chỉ định LOG ON, SQL tự tạo một file nhật ký có size bang 25 percent của tổng kích thước của tât cả các data files trên database.

4. 1. TẠO DATABASE 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% )

5. TẠO DATABASE Ví dụ 3 Create Database QLSinhVien On ( Name = QLSV_Data1, Filename = “D:\LT_CT11 \ QLSV_Data1.mdf “, Size = 1, Maxsize = 10 MB, Filegrowth = 1 MB ) , ( Name = QLSV_Data2 , Filename = “D:\LT_CT11\QLSV_Data1.ndf” ) Log on ( Name = QLSV_Log, Filename = “D:\LT_CT11\QLSV_Log.ldf” )

6. 2. XÓA, ĐỔI TÊN, SỬA ĐỔI DATABASE Xóa DataBase Cú pháp: DROP DATABASE <DatabaseName> [, …] Đổi tên DataBase (dùng thủ tục SP_ReNameDB) EXEC SP_RENAMEDB ‘OldName’, ‘NewName’ EXEC lệnh dùng để chạy một thủ tục, hàm Sửa đổi Database Để thêm hay xóa file và nhóm file hoặc thay đổi các thuoc tính của file và nhóm file, như thay đổi tên và dung lượng của file Cú pháp: ALTER DATABASE databasename ADD FILE <Thông tin File Dữ Lieu> [,...n] ADD LOG FILE <Thông tin file Log > [,...n] REMOVE FILE <Tên logic> MODIFY FILE <Thông tin file>

7. II. LÀM VIỆC VỚI BẢNG 1. TẠO BẢNG Cú pháp lệnh tạo bảng Create Table <Table_name > ( { <Column 1> <Data_type> [null | not null] [Các thuộc tính,] [<Column 2> , <Data_type> [null | not null] [Các thuộc tính,] [Column n,…]} [Constraint Constraint_name [Các ràng buộc] [,…n] )

8. 2. Thuộc tính Khai báo thuộc tính trên mỗi cột: Sau lời khai báo tên và kieu của cột Các thuộc tính • DEFAULT <Exp> : Giá trị mặc định của cột trên mẫu tin mới • IDENTITY [(seed, increment )] : Thường được dùng khai báo cho cột kieu sô nguyên, là khoá chính của table. Chỉ định giá trị được ghi trên mẫu tin mới là một sô . Trong dó : Seed -giá trị khởi đâu và Increment -sô gia.

9. Create table SINHVIEN ( STTInt Not Null IDENTITY (1,1), MaSV char(10) Not Null, MaLop char(5) Not Null Default ‘CT11', HoTen nvarchar(30) Not Null, NgaySinh smalldatetime Not Null, DiaChi nvarchar(100) ) Ví dụ: Xây dựng cấu trúc bảng SINHVIEN trong đó có cột STT là kiểu nguyên dữ liệu tự động là 1,2,3,4, …. Malop có giá trị mặc định là ’CT11’

10. 3. Ràng buộc Primary Key Khoá chính của một bảng là một hoặc một tập nhiều cột mà giá trị của chúng là duy nhất trong bảng. Đặc điểm: - Mỗi bảng chỉ duy nhất một khóa chính. - Giá trị trong khóa chính không được trùng nhau. - Khóa chính không nhận giá trị NULL. Cú pháp: [CONSTRAINT <tên_ràng_buộc>] PRIMARY KEY [(danh_sách_cột)] Tên ràng buộc: tên logic đặt tên cho biểu thức khóa thường sử dụng trong lập trình. Danh sách cột: Dãy các cột cách nhau bởi dấu phẩy

11. Ví dụ: Create Table SINHVIEN ( STTInt Not Null IDENTITY (1,1), MaSV char(10) Not Null, MaLop char(5) Not Null Default ‘CT11', HoTen nvarchar(30) Not Null, NgaySinh smalldatetime Not Null, DiaChi varchar(100), ) CONSTRAINT pk_sv_masv PRIMARY KEY(MASV)

12. CREATE TABLE DIEMTHI (MAMON VARCHAR(10) NOT NULL ,MASV VARCHAR(10) NOT NULL ,DIEMHP NUMERIC(4, 2) Default 5, CONSTRAINT pk_diemthi PRIMARY KEY(MaMonHoc,Masv) ) Ví dụ 2: Tạo bảng DIEMTHI với khóa là bộ (MAMONHOC, MASV)

13. 4. Ràng buộc UNIQUE Trong một bảng chỉ có một khóa chính, nhưng có thể có nhiều cột hoặc tập cột mà giá trị của chúng duy nhất. Gọi là khóa phụ của bảng. - Các giá trị trong khóa phụ là duy nhất - Khóa phụ có thể chứa giá trị NULL Cú pháp: [CONSTRAINT tên_ràng_buộc] UNIQUE [(danh_sách_cột)]

14. Ví dụ: CREATE TABLE DMLOP ( MALOP VARCHAR(10) NOT NULL,TENLOP NVARCHAR(30) NOT NULL,KHOAHOC SMALLINT NULL,HEDAOTAO NVARCHAR(25) NULL NAMNHAPHOC INT NULL MAKHOA VARCHAR(5) NULL, CONSTRAINT pk_lop PRIMARY KEY (malop), CONSTRAINT unique_lop_tenlop UNIQUE(tenlop) ) Ví dụ: Tạo DMLOP với khóa chính là MALOP và khóa duy nhất là TENLOP

15. 5. Ràng buộc FOREIGN KEY Khóa ngoại của một bảng A là một cột hoặc một tập nhiều cột không là khóa chính của bảng A nhưng lại là khóa chính của một bảng B, nhằm tạo nên mối quan hệ giữa 2 bảng A và B trong một cơ sở dữ liệu Trong bảng DIEMTHI: khóa chính là (MAMONHOC,MASV) MAMONHOC không là khóa chính của bảng DIEMTHI nhưng lại là khóa chính của bảng MONHOC => MAMONHOC là khóa ngoại của bảng DIEMTHI MASV không là khóa chính của bảng DIEMTHI nhưng lại là khóa chính của bảng SINHVIEN => MASV là khóa ngoại của bảng DIEMTHI

16. Chú ý • Cột được tham chiếu trong bảng tham chiếu phải là khoá chính hoặc là khoá phụ. TRong quan hệ 1-n, Bảng tham chiểu sẽ có quan hệ 1, và bảng có khóa ngoại là bảng nhiều • Cột được tham chiếu (khóa chính/khóa phụ) phải có cùng kiểu dữ liệu và độ dài với cột tương ứng trong khóa ngoài. • Bảng tham chiếu phải được định nghĩa trước. Đảm bảo cùng giá trị với khóa ngoại.

17. Cú pháp lệnh: [CONSTRAINT tên_ràng_buộc] FOREIGN KEY [(danh_sách_cột)] REFERENCES tên bảng tham chiếu (ds cột tham chiếu) [ON DELETE CASCADE| NO ACTION | SET NULL | SET DEFAULT] [ON UPDATE CASCADE| NO ACTION| SET NULL | SET DEFAULT] Giải thích CASCADE: Tự động xoá (cập nhật) nếu bản ghi trong bảng chính (tham chiếu) bị xoá (cập nhật). NO ACTION: (Mặc định) Nếu bản ghi trong bảng tham chiếu đang được tham chiếu bởi một bản ghi bất kỳ trong bảng được định nghĩa thì bàn ghi đó KHÔNG được phép xoá hoặc cập nhật (đối với cột được tham chiếu). SET NULL: Cập nhật lại khoá ngoài của bản ghi thành giá trị NULL (nếu cột cho phép nhận giá trị NULL). SET DEFAULT: Cập nhật lại khoá ngoài của bản ghi nhận giá trị mặc định (nếu cột có qui định giá trị mặc định).

18. CREATE TABLE DIEMTHI ( MaMon VARCHAR(10) NOT NULL , Masv VARCHAR(10) NOT NULL , Diemhp NUMERIC(4, 2), CONSTRAINT pk_diemthi PRIMARY KEY(mamonhoc,masv), CONSTRAINT fk_diemthi_mamh FOREIGN KEY(MaMon) REFERENCES DMMON(MaMon) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_diemthi_masv FOREIGN KEY(Masv) REFERENCES SINHVIEN(Masv) ON DELETE CASCADE ON UPDATE CASCADE )

19. 6. Ràng buộc CHECK Kiểm tra điều kiện nhập dữ liệu [CONSTRAINT tên_ràng_buộc] CHECK (điều_kiện) Điều_kiện là một biểu thức Logic chứa các phép toán sau: - Phép so sánh <, <=, !<, >, >=, !>, =, <> hay != - Các phép toán lôgic: NOT, AND, OR. - Phép toán tập hợp IN, LIKE hoac NOT LIKE. - Các phép toán phạm vi: IS NULL / NOT NULL IN (<danh sách giá tr>)/ NOT IN (<danh sách giá tr>) BETWEEN <Min> AND <Max> / NOTBETWEEN <Min> AND <Max>

20. Create table SINHVIEN ( STT int not null IDENTITY (1,1), MaSV char(10) not null, MaLop char(5) not null default ‘CT11', HoTen nvarchar(30) not null, NgaySinh smalldatetime not null, DiaChi varchar(100), CONSTRAINT pk_sv_masv PRIMARY KEY(MaSV) ) Ví dụ: Trong bảng SINHVIEN với điều kiện Diachi là các huyện ‘QN’, ‘TP’, ‘PC’, ‘PM’, ‘TS’ CONSTRIANT chk_diachi CHECK DiaChi IN (‘QN’, ‘TP’, ‘PC’,‘PM’, ‘TS’)

21. CREATE TABLE diemthi ( MaMonHoc VARCHAR(10)NOT NULL , Masv VARCHAR(10)NOT NULL , DiemLan1 NUMERIC(4, 2), DiemLan2 NUMERIC(4, 2), CONSTRAINT pk_diemthi PRIMARY KEY(mamonhoc,masv), CONSTRAINT fk_diemthi_mamh FOREIGN KEY(mamonhoc) REFERENCES monhoc(mamonhoc) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_diemthi_masv FOREIGN KEY(masv) REFERENCES sinhvien(masv) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT chk_DLan1 CHECK(DiemLan1>=0 AND DiemLan1<=10), CONSTRAINT chk_DLan2 CHECK(DiemLan2>=0 AND DiemLan2<=10) ) Ví dụ: Trong DIEMTHI có điều kiện DiemThiLan1 và DimThiLan2 là số >=0 và <=10

22. XEM THÔNG TIN VỀ BẢNG 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’ 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’ ”] sp_help [object_name] sp_columns object [, owner] [, database] [,column] sp_helpconstraint ,table_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_tables null, null, null, “ ’TABLE’ ” Exec sp_help HOCSINH

23. 7. SỬA ĐỔI BẢNG Các yêu cầu cần sửa: • Bổ sung một cột vào bảng. • Thay đổi định nghĩa của một cột trong bảng. • Xoá một cột khỏi bảng. • Xoá bỏ hoặc bổ sung các ràng buộc cho bảng Cú pháp lệnh ALTER TABLE Tên_Bảng ADD Định_nghĩa_Cột |ALTER COLUMN TênCột KiểuDữliệu [NULL | NOT NULL] |DROP COLUMN TênCột |ADD CONSTRAINT TênRàngbuộc ĐịnhNghĩaRàngbuộc |DROP CONSTRAINT Tênràngbuộc

24. Ví dụ: Trong bảng SINHVIEN, thêm cột điện thoại kiểu chuỗi gồm 7 chữ số. ALTER TABLE SINHVIEN ADD Dienthoai Varchar(7) NULL, ADD Gioitinh bit NULL, CONSTRAINT chk_nhanvien_dienthoai CHECK (Dienthoai Like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9]’) • Ví dụ: Trong bảng SINHVIEN, Thay đổi kiểu của cột Masv VARCHAR(10) ALTER TABLE SINHVIEN ALTER COLUMN Masv varchar(10)

25. Chú ý: • Muốn xoá một cột đang được ràng buộc bởi một ràng buộc hoặc đang được tham chiếu bởi một khoá ngoài, ta phải xoá ràng buộc • Nếu bổ sung thêm ràng buộc cho một bảng đã có dữ liệu và ràng buộc cần bổ sung không được thoả mãn bởi các bản ghi đã có trong bảng thì câu lệnh ALTER TABLE không thực hiện được.

26. 8. XÓA BẢNG Cú pháp lệnh DROP TABLE tên_bảng Chú ý: Câu lệnh DROP TABLE không thể thực hiện được nếu bảng cần xoá đang được tham chiếu bởi một ràng buộc FOREIGN KEY. Trong trường hợp này, ràng buộc FOREIGN KEY đang tham chiếu hoặc bảng đang tham chiếu đến bảng cần xoá phải được xoá trước.

27. Ví dụ: Xóa bảng DMLOP Trước hết bạn phải Xóa ràng buộc khóa ngoại của bảng SINHVIEN ALTER TABLE SINHVIEN DROP CONSTRAINT fk_Malop DROP TABLE DMLOP

28. Bài tập về nhà: Bài 1: Dùng lệnh Create Table tạo các bảng trong CSDL bán hàng Bảng KHACHHANG: MaKH là khóa chính, TenCuahang là khóa duy nhất. Diachi có giá trị mặc định là “Quy Nhơn” Bảng nhân viên: MaNV là khóa chính, cột Phucap chấp nhận giá trị NULL, Bảng NHACUNGCAP: MaNhaCC là khóa chính, TenNhaCC là khóa duy nhất Bảng HANGHOA: MaH là khóa chính, MaNhaCC là khóa ngoại, Donvitinh là một trong các giá trị (Lốc, hộp, Lon, Chai, Thùng), Giá trị mặc định là “Lốc”, Soluong>0, GiaHang>0 Bảng DONDATHANG: MaHD là khóa chính, MaKH là khóa ngoại, MaNV là khóa ngoại. Bảng CHITIET: bộ (MaHD, MaH) là khóa chính, MaHD, MaH là khóa ngoại.

29. Bài 2: Dùng câu lệnh ALTER TABLE để sửa đổi các bảng trong CSDL BANHANG theo yêu cầu sau:\ Bổ sung thuộc tính: thiết lập giá trị mặc định bằng 1 cho cột SOLUONG và bằng 0 cho cột MUCGIAMGIA trong bảng CHITIETDATHANG Bổ sung cho bảng DONDATHANG ràng buộc kiểm tra ngày giao hàng và ngày chuyển hàng phải sau hoặc bằng với ngày đặt hàng. Bổ sung ràng buộc CHECK cho bảng NHANVIEN để đảm bảo rằng một nhân viên chỉ có thể làm việc trong công ty khi đủ 18 tuổi và không quá 60 tuổi. Với các bảng đã tạo được, câu lệnh: DROP TABLE NhaCungcap có thể thực hiện được không? Tại sao?