›
›

Hệ quản trị cơ sở dữ liệu - Chương 2: Xây dựng và khai thác cơ sở dữ liệu

pdf 142 trang vanle 3920
Download
Bạn đang xem 20 trang mẫu của tài liệu "Hệ quản trị cơ sở dữ liệu - Chương 2: Xây dựng và khai thác cơ sở dữ liệu", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfhe_quan_tri_co_so_du_lieu_chuong_2_xay_dung_va_khai_thac_co.pdf

Nội dung text: Hệ quản trị cơ sở dữ liệu - Chương 2: Xây dựng và khai thác cơ sở dữ liệu

  1. CHƯƠNG 2 XÂY DỰNG VÀ KHAI THÁC CƠ SỞ DỮ LIỆU
  2. NỘI DUNG CƠ SỞ DỮ LIỆU (DATABASE) BẢNG (TABLE) LƯỢC ĐỒ DIAGRAM BẢNG ẢO (VIEW) TRUY VẤN DỮ LIỆU 2
  3. 1. CƠ SỞ DỮ LIỆU (DATABASE) . Mỗi database trong SQL Server chứa duy nhất một data file chính (Primary), có thể có thêm một hay nhiều data phụ (Secondary) và ít nhất một transaction log file (file nhật ký) – mặc dù chỉ cần 1 tập tin log là đủ. . Phần dữ liệu: tập tin bắt buộc (*.mdf) và tập tin phụ (*.ndf). Cơ sở dữ liệu . Phần nhật ký (*.ldf). Tập tin dữ liệu Tập tin log .mdf hay .ndf .ldf 3
  4. 1. CƠ SỞ DỮ LIỆU (DATABASE) . Primary data file (.mdf):  file chính chứa data và những system tables. . Secondary data file (.ndf):  file phụ thường chỉ sử dụng khi database được phân chia để chứa trên nhiều đĩa. . Transaction log file (.ldf):  file ghi lại tất cả những thay đổi diễn ra trong một database. 4
  5. 1. CƠ SỞ DỮ LIỆU (DATABASE) . Về mặt logic: dữ liệu trong CSDL được lưu trong các đối tượng của CSDL. . Về mặt vật lý: CSDL được lưu trên 2 hoặc nhiều tập tin. 5
  6. 1. CƠ SỞ DỮ LIỆU (DATABASE) . Một database bao gồm tối thiểu hai file: • .mdf: lưu trữ các đối tượng trong database như table, view, • .ldf: lưu trữ các quá trình cập nhật/ thay đổi dữ liệu o Hỗ trợ sao lưu dữ liệu o Hỗ trợ phục hồi dữ liệu. 6
  7. 1. CƠ SỞ DỮ LIỆU (DATABASE) Các bước tiến hành tạo database: hoạch định vị trí và không gian lưu trữ cho database – Để tránh xảy ra rủi ro lưu trữ các tập tin transaction log và các tập tin dữ liệu khác ổ đĩa. – Cần dự tính dung lượng dữ liệu ban đầu cho database sử dụng đủ trong 1 khoảng thời gian nhất định (data file và transaction log file). – Thông thường không chọn Autogrowth (tự động tăng trưởng) và Autoshrink (tự động nén). 7
  8. 1. CƠ SỞ DỮ LIỆU (DATABASE) Nếu chọn Autogrowth (hay Autoshrink) thì có thể gặp 2 vấn đề sau: – Performance hit: Ảnh hưởng đáng kể đến khả năng làm việc của SQL Server. Do nó phải thường xuyên kiểm tra xem có đủ khoảng trống cần thiết hay không và nếu không đủ nó sẽ phải mở rộng bằng cách dành thêm khoảng trống từ đĩa cứng và chính quá trình này làm chậm đi hoạt động của SQL Server. – Disk fragmentation: Việc mở rộng trên làm cho data không được liên tục mà chứa ở nhiều nơi khác nhau trong đĩa cứng ảnh hưởng lên tốc độ làm việc của SQL Server. 8
  9. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL . Để tạo một CSDL người dùng, các thông tin sau được yêu cầu: Tên CSDL Kích thước của CSDL Các tập tin và các nhóm tập tin để lưu CSDL. . Có thể tạo một CSDL bằng: Dùng SQL Server Manangement Studio. Dùng lệnh T–SQL. 9
  10. 1. CƠ SỞ DỮ LIỆU (DATABASE) Khởi động SQL Server 2008: Start All Programs Microsoft SQL Server 2008 SQL Server Management Studio  Tạo CSDL sử dụng SQL Server Management Studio B1: Chọn Database chuột phải New Database B2: Trên cửa sổ New Database chọn General 10
  11. 1. CƠ SỞ DỮ LIỆU (DATABASE) Kích thước ban đầu của tập tin 11
  12. 1. CƠ SỞ DỮ LIỆU (DATABASE) 12
  13. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Cú pháp đơn giản: CREATE DATABASE ON PRIMARY ( name= tên logic của file dữ liệu, Filename= ‘đường dẫn đến nơi lưu trữ trên đĩa’, Size= [KB|MB|GB|TB], Maxsize= , Filegrowth= ) [, ( ), , ( )] 13
  14. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Cú pháp đơn giản : LOG ON ( name= tên logic của file transaction log, Filename= ‘đường dẫn đến nơi lưu trữ trên đĩa’, Size= , Maxsize= , Filegrowth= ); GO 14
  15. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Cú pháp tổng quát: CREATE DATABASE ON PRIMARY ( ) [, Filegroup ( name = ‘tên logic của file dữ liệu 2’, Filename = ‘đường dẫn đến nơi lưu trữ trên đĩa’, Size = [KB|MB|GB|TB], Maxsize = Filegrowth = ), ] LOG ON ( ) 15
  16. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Giải thích: . Tên_database: tham chiếu đến toàn bộ CSDL. . Name: chỉ định tên logic sẽ sử dụng bên trong SQL Server để tham chiếu đến tập tin CSDL vật lý trên đĩa cứng. . File name: đường dẫn và tên tập tin gắn liền với vị trí của dữ liệu trên đĩa cứng (phải là ổ đĩa cứng cục bộ). 16
  17. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Giải thích: . FileGrowth: chỉ định độ tăng nào được sử dụng đối với việc tự động phát triển của tập tin CSDL. . Log On: mô tả tên, nơi mà tập tin nhật ký chuyển tác được tìm kiếm và kích thước của chúng là bao nhiêu. 17
  18. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Ví dụ 1: Tạo một CSDL dành ra 20MB lúc đầu cho phần dữ liệu và 5MB cho phần nhật ký. Các tập tin có thể phát triển lên đến 100MB cho phần dữ liệu và 15MB đối với nhật ký. 18
  19. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Ví dụ 1: CREATE DATABASE DB_NHANVIEN ON PRIMARY ( name = DBNV_Primary, Filename= ‘D:\ThuchanhSQL\DBNV_Primary.mdf’, Size = 20MB, Maxsize=100MB, Filegrowth= 10MB ) 19
  20. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Ví dụ 1: LOG ON ( name= DBNV_Log, Filename=‘D:\ThuchanhSQL\DBNV_Log.ldf’, Size= 5MB, Maxsize= 15MB , Filegrowth= 1MB ); GO 20
  21. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Ví dụ 2: CREATE DATABASE DB_SINHVIEN ON PRIMARY Lưu( ý : Nếu không chỉ định một transaction log file thì SQL name sẽ tự = độngDBSV_Primary, tạo ra một log file với kích thước ban Filename= ‘D:\ThuchanhSQL\DBSV_Primary.mdf’, đầu làSize bằng = 20¼MB, dung lượng file .mdf. Maxsize=100MB, Filegrowth= 10MB ); 21
  22. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Ví dụ 3: Tạo CSDL tên QLTV • Tập tin chính tên logic là qltv_data, đường dẫn là d:\qltv_data.mdf • Tập tin phụ thứ nhất có tên logic qltv_data1, đường dẫn là d:\ qltv_data1.ndf • Tập tin phụ thứ hai có tên logic qltv_data2, đường dẫn là d:\ qltv_data2.ndf • Tập tin log có tên logic là qltv_log, đường dẫn là d:\qltv_log.ldf 22
  23. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Tạo một CSDL bằng CREATE DATABASE Ví dụ 3: Tạo CSDL tên QLTV CREATE DATABASE qltv ( NAME = qltv_data2, ON primary FILENAME = ( NAME = qltv_data, 'D:\qltv_data2.ndf', FILENAME = SIZE = 4 MB, 'D:\qltv_data.mdf', MAXSIZE = 10 MB, SIZE = 4 MB, FILEGROWTH = 10% MAXSIZE = 10 MB, ) FILEGROWTH = 1MB LOG ON ), ( NAME = qltv_log, ( NAME = qltv_data1, FILENAME = 'D:\ FILENAME = qltv_log.ldf', 'D:\qltv_data1.ndf', SIZE = 4 MB, SIZE = 4 MB, MAXSIZE = 10 MB, MAXSIZE = 10 MB, FILEGROWTH = 10% FILEGROWTH = 10% ) ), 23
  24. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Mở CSDL để làm việc Cú pháp: USE database_name 24
  25. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Xem kích thước CSDL (SQL Server Management Studio) Click chuột phải vào cơ sở dữ liệu cần theo dõi kích thước Chọn Properties Chọn General tại mục Database, Size Tổng kích thước các tập tin Dung lượng còn trống 25
  26. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Xem kích thước CSDL Cú pháp: Use database_name go sp_spaceused 26
  27. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Thay đổi kích thước CSDL Click chuột phải vào cơ sở dữ liệu cần tăng kích thước Chọn Properties Chọn mục Files trên cây thư mục bên trái Thực hiện tăng/giảm kích thước của các tập tin cần thiết trong vùng Database files. 27
  28. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Thay đổi kích thước CSDL Tăng kích thước các tập tin của CSDL Alter database database_name Modify file (name = tên_file, size = số nguyên  MB) Lưu ý: Kích thước chỉ định tăng của tập tin phải lớn hơn kích thước hiện hành của tập tin đó trong cơ sở dữ liệu. 28
  29. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Thay đổi kích thước CSDL Tăng kích thước các tập tin của CSDL Ví dụ: Alter database DB_SINHVIEN Modify file (name = DBSV_Primary, size = 20MB) 29
  30. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Hiệu chỉnh thuộc tính của tập tin Cú pháp: Alter database database_name Modify file ( name = tên_file, filename = ‘đường dẫn’, size = số nguyên , maxsize = số nguyên , filegrowth = số nguyên  ) 30
  31. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Hiệu chỉnh thuộc tính của tập tin VD: Hủy khả năng tự tăng trưởng cho DB_SINHVIEN Alter database DB_SINHVIEN Modify file (name = DBSV_Primary, filegrowth = 0 ) 31
  32. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Thay đổi kích thước CSDL Giảm kích thước CSDL DBCC ShrinkDatabase ( database_name [, ] ); Ví dụ: DBCC ShrinkDatabase(DB_SINHVIEN, 15); 32
  33. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Thay đổi kích thước CSDL Giảm kích thước tập tin của CSDL DBCC ShrinkFile (name = tên file[, size = ]); Ví dụ: Giảm kích thước tập tin dữ liệu của CSDL DB_SINHVIEN xuống còn 10MB. DBCC ShrinkFile (Name = DBSV_Primary, size = 10); 33
  34. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Thêm mới tập tin trong CSDL Từ cửa sổ Database Properties nhấn nút Add để thêm tập tin mới và chỉ định các thông tin cần thiết. 34
  35. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Thêm mới tập tin trong CSDL ALTER DATABASE database_name Add File [To FileGroup filegroup_name]|Add Log File ( Name = ‘tên logic của file dữ liệu’, Filename = ‘đường dẫn đến nơi lưu trữ trên đĩa’, Size = , Maxsize = , Filegrowth = ); 35
  36. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Thêm mới tập tin trong CSDL Ví dụ: ALTER DATABASE DB_SINHVIEN Add File ( Name = DBSV_Primary2, Filename = ‘D:\Thuc hanh SQL\DBSV_Primary2.ndf', Size = 2MB, Maxsize = 5MB, Filegrowth = 2MB ); 36
  37. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Gỡ bỏ tập tin khỏi CSDL Từ cửa sổ Database Properties chọn tập tin cần hủy và nhấn nút Remove để hủy bỏ tập tin. 37
  38. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Gỡ bỏ tập tin khỏi CSDL ALTER DATABASE database_name Remove File name  Lưu ý: Tập tin phải trống trước khi gỡ bỏ Sử dụng DBCC ShrinkFile với tùy chọn EmptyFile để làm trống một tập tin. 38
  39. 1. CƠ SỞ DỮ LIỆU (DATABASE)  Gỡ bỏ tập tin khỏi CSDL Ví dụ: DBCC ShrinkFile ( DBSV_Primary2, EmptyFile ) ALTER DATABASE DB_SINHVIEN Remove File DBSV_Primary2; 39
  40. 2. BẢNG (TABLE)  Khái niệm: Bảng là đối tượng lưu trữ dữ liệu chính trong SQL Server, việc tổ chức các bảng dựa vào mô hình cơ sở dữ liệu quan hệ, và được chuẩn hoá dựa vào các dạng chuẩn để dữ liệu được lưu trữ và sử dụng một cách hợp lý, nhất quán. Ví dụ: LOP(MALOP, TENLOP, S ISO) SINHVIEN(MASV, HOTEN, NGSINH, PHAI, QUEQUAN, MALOP) 40
  41. 2. BẢNG (TABLE)  Tạo cấu trúc bảng bằng công cụ trên SQL Server Management Studio Mở rộng danh mục một CSDL nhấn chuột phải vào mục Tables chọn New Table 41
  42. 2. BẢNG (TABLE) 42
  43. 2. BẢNG (TABLE) Set Primary Key – Tạo khóa chính 43
  44. 2. BẢNG (TABLE) Lưu bảng, đặt tên bảng 44
  45. 2. BẢNG (TABLE)  Tạo cấu trúc bảng bằng T – SQL Để định nghĩa một bảng . Tên bảng . Các thuộc tính • Tên thuộc tính • Kiểu dữ liệu • Các RBTV trên thuộctính 45
  46. 2. BẢNG (TABLE)  Tạo cấu trúc bảng bằng T – SQL Cú pháp: CREATE TABLE Tên_bảng ( Tên_cột Kiểu_dữ_liệu [RBTV], Tên_cột Kiểu_dữ_liệu [RBTV], [ RBTV] ) 46
  47. 2. BẢNG (TABLE)  Tạo cấu trúc bảng bằng T – SQL Kiểu dữ liệu 47
  48. 2. BẢNG (TABLE)  Tạo cấu trúc bảng bằng T – SQL Kiểu dữ liệu 48
  49. 2. BẢNG (TABLE)  Tạo cấu trúc bảng bằng T – SQL Kiểu dữ liệu 49
  50. 2. BẢNG (TABLE)  Tạo cấu trúc bảng bằng T – SQL Ví dụ: CREATE TABLE NHANVIEN ( MANV CHAR(9), HONV VARCHAR(10), TENNV VARCHAR(10), NGSINH DATE, DCHI VARCHAR(50), PHAI CHAR(3), LUONG INT, PHG TINYINT ) 50
  51. 2. BẢNG (TABLE)  Lưu ý khi thiết kế 1 table: – Phải nắm vững về các kiểu dữ liệu. – Xác định chính xác khóa chính. – Tránh dùng cột có chứa NULL và nên luôn có giá trị Default cho các cột. – Phải biết rõ quan hệ giữa các bảng. 51
  52. 2. BẢNG (TABLE)  Tạo cấu trúc bảng bằng T – SQL  Các loại ràng buộc toàn vẹn (RBTV) • NOT NULL (không cho rỗng), NULL (cho rỗng) • UNIQUE (kiểm tra tính duy nhất) • DEFAULT (giá trị mặc định) • PRIMARY KEY (khóa chính) • FOREIGN KEY/ REFERENCES (khóa ngoại) • CHECK (kiểm tra miền giá trị ) 52
  53. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra MGT bên trong bảng: Ví dụ 1: Xét lược đồ quan hệ HANG (MAHG, TENHG, DONGIA) Khi tạo bảng cần tạo một ràng buộc kiểm tra MGT cho cột đơn giá khi thêm hay sửa phải lớn hơn 0. 53
  54. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra MGT bên trong bảng: Ví dụ 1: Xét lược đồ quan hệ HANG (MAHG, TENHG, DONGIA) Cách1 (Vị trí 1): CREATE TABLE HANG ( MAHG Char(5) Primary Key, TENHG Varchar(50), DONGIA Int CHECK(DONGIA > 0) ) 54
  55. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra MGT bên trong bảng:  Cách 2: Đặt tên cho ràng buộc (Vị trí 2)  Cú pháp: CONSTRAINT Ten_RBTV RBTV Ví dụ: CREATE TABLE HANG ( MAHG Char(5) Primary Key, TENHG Varchar(50), DONGIA Int, CONSTRAINT ck_dongia CHECK(DONGIA > 0) ) 55
  56. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra tính duy nhất (Unique): Ví dụ 2: Xét lược đồ quan hệ HANG (MAHG, TENHG, DONGIA) Tạo bảng HANG có kiểm tra tính duy nhất của TENHANG (khi thêm hay sửa dữ liệu thì tên hàng phải duy nhất). 56
  57. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra tính duy nhất (Unique): Ví dụ 2: Xét lược đồ quan hệ HANG (MAHG, TENHG, DONGIA) Cách1 (Vị trí 1): CREATE TABLE HANG ( MAHG Char(5) Primary Key, TENHG Varchar(50) UNIQUE, DONGIA Int CHECK(DONGIA > 0) ) 57
  58. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra tính duy nhất (Unique):  Cách 2: Đặt tên cho ràng buộc (Vị trí 2)  Cú pháp: CONSTRAINT Ten_RBTV RBTV CREATE TABLE HANG ( MAHG Char(5) Primary Key, TENHG Varchar(50), DONGIA Int CHECK(DONGIA > 0), CONSTRAINT uni_tenhang UNIQUE(TENHG) ) 58
  59. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra giá trị mặc định (Default): Ví dụ 3: Xét lược đồ quan hệ SINHVIEN (MASV, HOTEN, DIACHI) Tạo ràng buộc giá trị mặc định thực hiện điền vào giá trị ‘Chua xac dinh’ khi để trống cột DIACHI lúc thêm dữ liệu. 59
  60. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra giá trị mặc định (Default): Ví dụ 3: Xét lược đồ quan hệ SINHVIEN (MASV, HOTEN, DIACHI) Cách1 (Vị trí 1): CREATE TABLE SINHVIEN ( MASV Char(10) Primary Key, HOTEN Varchar(50), DIACHI Varchar(50) DEFAULT ‘Chua xac dinh’ ) 60
  61. 2. BẢNG (TABLE)  Tạo ràng buộc kiểm tra giá trị mặc định (Default):  Cách 2: Đặt tên cho ràng buộc  Cú pháp: CONSTRAINT Ten_RBTV RBTV CREATE TABLE SINHVIEN ( MASV Char(10) Primary Key, HOTEN Varchar(50), DIACHI Varchar(50) CONSTRAINT df_diachi DEFAULT ‘Chua xac dinh’ ) 61
  62. 2. BẢNG (TABLE)  Ghi chú: Câu lệnh sau SAI CREATE TABLE SINHVIEN ( MASV Char(10) Primary Key, HOTEN Varchar(50), DIACHI Varchar(50) , CONSTRAINT df_diachi DEFAULT ‘Chua xac dinh’ for DIACHI ) 62
  63. 2. BẢNG (TABLE) Ví dụ: Viết lệnh T–SQL tạo bảng sau: NHANVIEN (MANV, HONV, TENNV, NGSINH, DIACHI, PHAI, LUONG, PHG) Trong đó: MANV là khóa chính, HONV, TENNV không được rỗng, PHAI chỉ được nhập giá trị ‘Nam’ hay ‘Nữ’, LUONG được mặc định là 1150000. 63
  64. 2. BẢNG (TABLE) Ví dụ: CREATE TABLE NHANVIEN ( MANV CHAR(9) PRIMARY KEY, HONV VARCHAR(10) NOT NULL, TENNV VARCHAR(10) NOT NULL, NGSINH DATE, DCHI VARCHAR(50), PHAI CHAR(3) CHECK (PHAI IN (‘Nam’, ‘Nu’)), LUONG INT DEFAULT (1150000), PHG TINYINT ) 64
  65. 2. BẢNG (TABLE)  Thêm ràng buộc vào bảng đã tồn tại (Vị trí 3) PRIMARY KEY (tên_cột) ALTER TABLE tên_bảng UNIQUE (tên_cột) ADD [CONSTRAINT tên_ràng_buộc] FOREIGN KEY (tên_cột) REFERENCES tên_bảng (cột_là_khóa_chính) CHECK (tên_cột điều_kiện) DEFAULT biểu thức FOR tên cột
  66. 2. BẢNG (TABLE)  Quản lý RBTV Primary key Foreign key Unique Check Default
  67. 2. BẢNG (TABLE)  Xóa RBTV Mở thư mục chứa ràng buộc > nhấp chuột phải vào ràng buộc cần xoá> chọn Delete. Hoặc ALTER TABLE Tên_bảng DROP CONSTRAINT Tên_RBTV [, Tên_RBTV , ]
  68. BÀI TẬP  Cho lược đồ CSDL QLHH: quản lý hàng hóa sau HangHoa(MaHG, TenHG, DVT, DonGia, CoHang) Khach(MaKH, HoTen, DChi, DaiLy, SoDT) HoaDon(SoHD, NgayLap, NgayGiao, TriGia, MaKH) ChiTiet_HD(SoHD, MaHG, SoLuong, GiaBan) Yêu cầu: Viết câu lệnh để tạo tất cả các bảng dựa vào các LĐQH trên (với các ràng buộc khóa chính, khóa ngoại; TenHG, HoTen, DaiLy không được rỗng; CoHang chỉ nhận giá trị 0 hoặc 1; DVT chỉ nhận giá trị: Cái, Lọ, Thố; NgayLap, NgayGiao nhận giá trị mặc định là ngày hiện tại của hệ thống; SoDT có ràng buộc duy nhất; DonGia, TriGia, SoLuong, GiaBan phải có giá trị > 0). 68
  69. 2. BẢNG (TABLE)  Lệnh sửa cấu trúc bảng Được dùng để . Thay đổi cấu trúc bảng . Thay đổi RBTV Thêm cột ALTER TABLE Tên_bảng ADD Tên_cột  Kiểu_dữ_liệu  [RBTV ] 69
  70. 2. BẢNG (TABLE)  Lệnh sửa cấu trúc bảng Xóa cột ALTER TABLE Tên_bảng DROP COLUMN Tên_cột Mở rộng cột ALTER TABLE Tên_bảng ALTER COLUMN Tên_cột  Kiểu_dữ_liệu_mới  70
  71. 2. BẢNG (TABLE)  Lệnh sửa cấu trúc bảng Thêm RBTV ALTER TABLE Tên_bảng ADD CONSTRAINT Ten_RBTV RBTV, CONSTRAINT Ten_RBTV RBTV, Xóa RBTV ALTER TABLE Tên_bảng DROP Tên_RBTV 71
  72. 2. BẢNG (TABLE)  Lệnh sửa cấu trúc bảng ALTER TABLE NHANVIEN ADD NGHENGHIEP CHAR(20) ALTER TABLE NHANVIEN DROP COLUMN NGHENGHIEP ALTER TABLE NHANVIEN ALTER COLUMN NGHENGHIEP CHAR(50) 72
  73. 2. BẢNG (TABLE)  Lệnh sửa cấu trúc bảng CREATE TABLE PHONGBAN ( TENPB VARCHAR(20), MAPHG INT NOT NULL, TRPHG CHAR(9), NG_NHANCHUC DATETIME ) ALTER TABLE PHONGBAN ADD CONSTRAINT PB_MAPHG_PK PRIMARY KEY (MAPHG), CONSTRAINT PB_TRPHG FOREIGN KEY (TRPHG) REFERENCES NHANVIEN(MANV), CONSTRAINT PB_TENPB_UNI UNIQUE (TENPB) 73
  74. 2. BẢNG (TABLE)  Lệnh xóa bảng Được dùng để xóa cấu trúc bảng . Tất cả dữ liệu của bảng cũng bị xóa Cú pháp DROP TABLE Tên_bảng Ví dụ DROP TABLE NHANVIEN 74
  75. 3. LƯỢC ĐỒ DIAGRAM  Đối tượng Diagram là một lược đồ thể hiện sự liên kết các bảng trong CSDL với nhau.  Trên lược đồ Diagram chỉ thể hiện 2 loại mối liên kết là 1–n và 1–1. 75
  76. 3. LƯỢC ĐỒ DIAGRAM Liên kết là1 – n 76
  77. 3. LƯỢC ĐỒ DIAGRAM Liên kết là1 – 1 77
  78. NHẬP DỮ LIỆU VÀO BẢNG  Lưu ý: Khi nhập liệu vào bảng phải nhập theo đúng thứ tự là bảng có liên kết 1 nhập trước, bảng có liên kết nhiều nhập sau. Có 3 hình thức nhập dữ liệu vào bảng: – Nhập trực tiếp – Nhập bằng lệnh – Nhập từ một bảng khác 78
  79. NHẬP DỮ LIỆU VÀO BẢNG  Nhập trực tiếp: nhấp chuột phải vào bảng, chọn Edit Top 200 Rows 79
  80. NHẬP DỮ LIỆU VÀO BẢNG  Nhập bằng lệnh T – SQL INSERT INTO Tên_bảng [(danh sách tên cột)] VALUES (danh sách các giá trị) 80
  81. NHẬP DỮ LIỆU VÀO BẢNG  Nhập bằng lệnh T – SQL Lưu ý: . Thứ tự các giá trị phải trùng với thứ tự các cột . Có thể thêm giá trị NULL ở những thuộc tính không là khóa chính và NOT NULL . Câu lệnh INSERT sẽ gặp lỗi nếu vi phạm RBTV • Khóa chính, Tham chiếu • NOT NULL – các thuộc tính có ràng buộc NOT NULL bắt buộc phải có giá trị. 81
  82. NHẬP DỮ LIỆU VÀO BẢNG  Nhập bằng lệnh T – SQL Ví dụ: MANV HOTEN NTNS PHAI MA_NQL MaPH LUONG 001 Vuong Ngoc Quyen 22/10/1957 Nu QL 3.000.000 002 Nguyen Thanh Tung 09/01/1955 Nam 001 NC 2.500.000 003 Le Thi Nhan 18/12/1960 Nu 001 DH 2.500.000 004 Dinh Ba Tien 09/01/1968 Nam 002 NC 2.200.000 005 Bui Thuy Vu 19/07/1972 Nam DH 2.200.000 006 Nguyen Manh Hung 15/09/1973 Nam 002 NC 2.000.000 007 Tran Thanh Tam 31/07/1975 Nu 002 NC 2.200.000 008 Tran Hong Minh 04/07/1976 Nu 004 NC 1.800.000 82
  83. NHẬP DỮ LIỆU VÀO BẢNG  Một số lưu ý . Thêm ký tự N trước chuỗi Unicode (Chuỗi nhập có dấu tiếng Việt). Ví dụ: Insert into NHANVIEN Values (‘NV01’, N‘Nguyễn Văn Trường’, ‘Nam’) 83
  84. NHẬP DỮ LIỆU VÀO BẢNG  Một số lưu ý . Nhập dữ liệu khi đã có ràng buộc khóa ngoại  Cách 1: B1: Nhập PHONGBAN B2: Nhập DEAN  Cách 2: B1: Nhập DEAN, nhập phong = NULL B2: Nhập PHONGBAN B3: Cập nhật DEAN 84
  85. NHẬP DỮ LIỆU VÀO BẢNG  Một số lưu ý . Nhập dữ liệu khi đã có ràng buộc khóa ngoại  Cách 1: B1: Nhập NHANVIEN, nhập phong = NULL B2: Nhập PHONGBAN B3: Cập nhật thuộc tính phong trong NHANVIEN 85
  86. NHẬP DỮ LIỆU VÀO BẢNG  Một số lưu ý . Nhập dữ liệu khi đã có ràng buộc khóa ngoại  Cách 2: B1: Nhập PHONGBAN, nhập trphong = NULL B2: Nhập NHANVIEN B3: Cập nhật thuộc tính trphong trong PHONGBAN 86
  87. NHẬP DỮ LIỆU VÀO BẢNG  Một số lưu ý . Nhập dữ liệu khi đã có ràng buộc khóa ngoại  Cách 1: B1: Những giáo viên có MaGVQuanLi=NULL nhập trước. B2: Sau đó nhập những giáo viên mà đã nhập thông tin người quản lý của giáo viên đó. 87
  88. NHẬP DỮ LIỆU VÀO BẢNG  Một số lưu ý . Nhập dữ liệu khi đã có ràng buộc khóa ngoại  Cách 2: B1: Nhập GIAOVIEN đặt MaGVQuanLi = NULL. B2: Cập nhật cột MaGVQuanLi trong bảng GIAOVIEN. 88
  89. NHẬP DỮ LIỆU VÀO BẢNG  Nhập từ một bảng khác INSERT INTO SV_08CDTH1 SELECT MASV, HOTEN FROM SINHVIEN, LOP WHERE SINHVIEN.MALOP = LOP.MALOP AND TENLOP = ‘08CDTH1’ 89
  90. 3. BẢNG (TABLE)  Cập nhật dữ liệu vào bảng UPDATE Tên_bảng SET Tên_thuộc tính1 = GT1, , Tên_thuộc tínhN = GTN [Where Điều kiện] 90
  91. 3. BẢNG (TABLE)  Xóa dữ liệu khỏi bảng DELETE FROM Tên_bảng [Where Điều kiện chọn] Lưu ý: Câu lệnh Delete chỉ xóa dữ liệu của bảng, cấu trúc bảng tạo bởi câu lệnh Create Table vẫn giữ nguyên. 91
  92. 5. TRUY VẤN DỮ LIỆU  Cú pháp câu lệnh SELECT SELECT [*|DISTINCT] Danh sách các cột FROM Danh sách các bảng [WHERE Điều kiện chọn] [GROUP BY Danh sách cột cần nhóm] [HAVING Điều kiện nhóm] [ORDER BY Danh sách cột cần sắp xếp [ASC|DESC]] 92
  93. 5. TRUY VẤN DỮ LIỆU Trong suốt phần sau chúng ta sử dụng CSDL sau làm ví dụ minh họa: NhanVien(manv, honv, tennv, ngaysinh, diachi, phai, luong, manql, phong) PhongBan(maphong,tenphong,trphong, ngnhanchuc) DDPhong(maphong, diadiem) DeAn(tenda, mada, ddiemda, phong) PhanCong(manvien,soda, thoigian) ThanNhan(manvien,tentn, phai,ngaysinh, quanhe) 93
  94. 5. TRUY VẤN DỮ LIỆU  Là ngôn ngữ rút trích dữ liệu Thường đi kèm với một điều kiện nào đó  Dựa trên 94
  95. 5. TRUY VẤN DỮ LIỆU  TRUY VẤN ĐƠN GIẢN Gồm 3 mệnh đề • Biểu thức boolean xác định dòng nào sẽ được rút trích • Nối các biểu thức: AND, OR, NOT • Phép toán: , =, <>, =, LIKE và BETWEEN 95
  96. 5. TRUY VẤN DỮ LIỆU  TRUY VẤN ĐƠN GIẢN (tt) 96
  97. 5. TRUY VẤN DỮ LIỆU  TRUY VẤN ĐƠN GIẢN (tt) VD: Cho biết danh sách nhân viên nữ ở phòng có mã là 4 Select * From NHANVIEN Dấu * để chỉ tất cả các cột Where Phai = N’Nữ’ And Phong = 4 97
  98. 5. TRUY VẤN DỮ LIỆU  TRUY VẤN ĐƠN GIẢN (tt) • Tên bí danh: Có thể đặt lại tên cho ds các thuộc tính trong bảng kết quả (gọi là tên bí danh). Cú pháp: Select tên_cột AS tên_mới_cho_cột Ví dụ: Select honv + tennv As ‘Ho ten’, From NHANVIEN Where Phong = 4 98
  99. 5. TRUY VẤN DỮ LIỆU  TRUY VẤN ĐƠN GIẢN (tt) • Tên bí danh cho bảng: From tên_bảng tên_mới_cho_bảng Ví dụ: Liệt kê danh sách nhân viên ở phòng có tên phòng là Phòng số 4 Select nv.* From NHANVIEN nv, phongban pb Where nv.phong = pb.maphong And tenphong = N’Phòng số 4’ 99
  100. 5. TRUY VẤN DỮ LIỆU  BETWEEN (NOT BETWEEN): Để kiểm tra giá trị dữ liệu nằm trong (ngoài) một khoảng nào đó. Select * From NHANVIEN Where Luong>=20000 And Luong<=30000  Có thể viết Select * From NHANVIEN Where Luong Between 20000 And 30000 100
  101. 5. TRUY VẤN DỮ LIỆU  LIKE (NOT LIKE): Được sử dụng trong câu lệnh select nhằm mô tả dạng dữ liệu (dạng chuỗi) cần tìm kiếm.  Thường được kết hợp với các ký tự đại diện sau: • %: chuỗi ký tự bất kỳ • _: ký tự đơn bất kỳ • []: ký tự đơn bất kỳ trong giới hạn được chỉ định (ví dụ [a-f]) hay một tập (ví du: [abcdef]) • [^]: ký tự đơn bất kỳ không nằm trong giới hạn chỉ định (ví dụ [^a-f]) hay một tập (ví dụ [^abcdef]). 101
  102. 5. TRUY VẤN DỮ LIỆU VD: Liệt kê danh sách nhân viên có họ Nguyễn Select * From NHANVIEN Where honv LIKE ‘Nguyễn %’  Sử dụng NOT LIKE tương tự 102
  103. 5. TRUY VẤN DỮ LIỆU  Mệnh đề ORDER BY: Dùng để hiển thị kết quả câu truy vấn theo một thứ tự nào đó  Cú pháp: SELECT ds các cột FROM ds các bảng WHERE điều kiện ORDER BY ds các cột kiểu sắp Trong đó kiểu sắp có thể là: o ASC: tăng (mặc định) o DESC: giảm 103
  104. 5. TRUY VẤN DỮ LIỆU  Ví dụ: SELECT MANVIEN, SODA FROM PHANCONG ORDER BY MANVIEN DESC, SODA 104
  105. 5. TRUY VẤN DỮ LIỆU TRUY VẤN LỒNG  Các câu lệnh SELECT có thể lồng nhau ở nhiều mức.  Các câu truy vấn con trong cùng một mệnh đề WHERE được kết hợp bằng phép nối logic.  Câu truy vấn con thường trả về một tập các giá trị. 105
  106. 5. TRUY VẤN DỮ LIỆU TRUY VẤN LỒNG Cú pháp 106
  107. 5. TRUY VẤN DỮ LIỆU TRUY VẤN LỒNG Mệnh đề WHERE của câu truy vấn cha: WHERE đi cùng với một số toán tử: . IN, NOT IN, ALL, ANY hoặc SOME Kiểm tra sự tồn tại: . EXISTS . NOT EXISTS 107
  108. 5. TRUY VẤN DỮ LIỆU Ví dụ: Danh sách nhân viên có địa điểm phòng ở TP HCM SELECT MANV, TENNV FROM NHANVIEN, DDPhong WHERE DIADIEM = ‘TP HCM’ AND phong = maphong 108
  109. 5. TRUY VẤN DỮ LIỆU Tương đương với câu truy vấn lồng sau: SELECT MANV, TENNV FROM NHANVIEN WHERE PHONG IN (SELECT MAPHONG FROM DDPHONG WHERE DIADIEM = ‘TP HCM’) 109
  110. 5. TRUY VẤN DỮ LIỆU Ví dụ: Tìm những nhân viên không có thân nhân nào? 110
  111. 5. TRUY VẤN DỮ LIỆU Ví dụ: Tìm những nhân viên có lương lớn hơn lương của tất cả nhân viên phòng 4 111
  112. 5. TRUY VẤN DỮ LIỆU Ví dụ: Tìm những nhân viên có tuổi lớn nhất 112
  113. 5. TRUY VẤN DỮ LIỆU Nhận xét: . IN tên cột IN câu truy vấn con Thuộc tính ở mệnh đề SELECT của truy vấn con phải có cùng kiểu dữ liệu với thuộc tính ở mệnh đề WHERE của truy vấn cha. 113
  114. 5. TRUY VẤN DỮ LIỆU Nhận xét: . EXISTS Không cần có thuộc tính, hằng số hay biểu thức nào khác đứng trước. Không nhất thiết liệt kê tên thuộc tính ở mệnh đề SELECT của truy vấn con. Những câu truy vấn có = ANY hay IN đều có thể chuyển thành câu truy vấn có EXISTS. 114
  115. 5. TRUY VẤN DỮ LIỆU Hàm kết hợp: Được sử dụng trong mệnh đề SELECT Có các hàm kết hợp sau: • COUNT o COUNT(*), COUNT(Tên_thuộc_tính) o COUNT(DISTINCT Tên_thuộc_tính) • MIN, MAX, SUM, AVG 115
  116. 5. TRUY VẤN DỮ LIỆU Ví dụ: Cho biết số lượng nhân viên của phòng “Nghien cuu” 116
  117. 5. TRUY VẤN DỮ LIỆU Gom nhóm: Nhóm các bộ có cùng giá trị ở một tập các thuộc tính lại với nhau. Cú pháp: SELECT FROM WHERE GROUP BY 117
  118. 5. TRUY VẤN DỮ LIỆU Ví dụ: Cho biết số lượng nhân viên của từng phòng ban (Liệt kê: Tên phòng ban, SL nhân viên từng phòng) 118
  119. 5. TRUY VẤN DỮ LIỆU Ví dụ: Cho biết thông tin của những phòng ban có nhiều hơn 10 nhân viên? 119
  120. 5. TRUY VẤN DỮ LIỆU Cú pháp: SELECT FROM WHERE GROUP BY HAVING 120
  121. 5. TRUY VẤN DỮ LIỆU Ví dụ: Cho biết thông tin của những phòng ban có nhiều hơn 10 nhân viên? 121
  122. 5. TRUY VẤN DỮ LIỆU Mệnh đề GROUP BY Các thuộc tính trong mệnh đề SELECT (trừ những thuộc tính trong các hàm kết hợp) phải xuất hiện trong mệnh đề GROUP BY. Mệnh đề HAVING Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc trên từng bộ. Sau khi gom nhóm điều kiện trên nhóm mới được thực hiện. 122
  123. 4. BẢNG ẢO (Khung nhìn – VIEW)  Bảng ảo (View) là một đối tượng được tạo ra từ các bảng dữ liệu. Được hình thành nên từ kết quả truy vấn của câu lệnh SELECT.  Do bảng ảo cũng tương tự như bảng dữ liệu nên chúng ta vẫn có thể truy vấn, cập nhật dữ liệu như các bảng dữ liệu thông thường.  Khi dữ liệu ở các bảng dữ liệu thay đổi thì dữ liệu trong bảng ảo cũng thay đổi theo. 123
  124. 4. BẢNG ẢO (Khung nhìn – VIEW)  Bảng ảo cũng là một quan hệ – không chứa dữ liệu, nó chỉ là truy vấn kết hợp dữ liệu từ 1 hay nhiều bảng có quan hệ với nhau và được lưu thành một đối tượng của SQL Server 124
  125. 4. BẢNG ẢO (Khung nhìn – VIEW)  Ý nghĩa của View:  Làm đơn giản hóa các truy vấn phức tạp.  Tăng tính bảo mật dữ liệu.  SQL Server về bản chất chỉ lưu câu lệnh T-SQL để tạo ra view, View sẽ được dịch (truy vấn dữ liệu) mỗi khi có lệnh gọi thực hiện view.  Truy vấn dữ liệu trong view nhanh hơn là truy vấn trực tiếp từ các bảng. 125
  126. 4. BẢNG ẢO (Khung nhìn – VIEW)  Ý nghĩa của View:  Khi một câu truy vấn phức tạp trong tương lai có thể phải sử dụng lại nhiều lần tạo View để lưu câu truy vấn. Trường hợp hay xảy ra nhất là các câu truy vấn tính toán, tổng hợp dữ liệu.  Không muốn user biết được tên bảng, cấu trúc thật sự của bảng. 126
  127. 4. BẢNG ẢO (VIEW)  Tạo View bằng công cụ SQL Server Management Studio 1 B1: Chuột phải vào Views chọn New View 127
  128. 4. BẢNG ẢO (VIEW)  Tạo View bằng công cụ SQL Server Management Studio B2: Trong hộp thoại Add Table chọn các bảng cần thiết liên quan 2 đến View cần tạo Add 128
  129. 4. BẢNG ẢO (VIEW) 3  Tạo view bằng công cụ SQL Server Management Studio 129
  130. 4. BẢNG ẢO (VIEW)  Tạo view bằng công cụ SQL Server Management Studio B4: Chọn Execute SQL để xem kết quả B5: Lưu View (Ctrl – S) 130
  131. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL Cú pháp: CREATE VIEW Tên_View [(danh_sách_cột)] [WITH ENCRYPTION] AS Mã hoáCâu View lệnh: SELECT dùng WITH  ENCRYPTION  Không thể xem được nội dung View  Không thể thay đổi lại được 131
  132. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL Ví dụ 1: Tạo View chứa thông tin các nhân viên ở phòng DH trong bảng NhanVien: 132
  133. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL Ví dụ 1: Tạo View chứa thông tin các nhân viên ở phòng DH trong bảng: Create View View_1 As Select * From NhanVien Where MaPH = ‘DH’ 133
  134. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL Ví dụ 2: Tạo View cho biết số nhân viên của từng phòng ban, thông tin hiển thị gồm MaPH, TenPH, Số lượng NV của từng phòng. NhanVien PhongBan134
  135. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL Ví dụ 2: Create View View_2 As Select MaPH, TenPH, Count(*) From NhanVien nv, PhongBan pb Where nv. MaPH = pb.MaPH Group by MaPH, TenPH LỖI 135
  136. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL  Lưu ý: Trong kết quả của câu lệnh SELECT có ít nhất một cột được sinh ra bởi một biểu thức (tức là không phải là một tên cột trong bảng cơ sở) mà cột đó không được đặt tiêu đề Lỗi. 136
  137. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL Ví dụ 2: Create View View_2 As Select MaPH, TenPH, Count(*) As SLNV From NhanVien nv, PhongBan pb Where nv. MaPH = pb.MaPH Group by MaPH, TenPH 137
  138. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL  Lưu ý: Có thể viết câu truy vấn dữ liệu từ View Select danh_sách_cột From Tên_View, Tên_bảng Where Điều_kiện 138
  139. 4. BẢNG ẢO (VIEW)  Tạo View bằng lệnh T – SQL  Ví dụ: Select MaNV, HoTen From View_1 Where MaNQL Is Not Null 139
  140. 4. BẢNG ẢO (VIEW)  Sửa View bằng lệnh T – SQL Cú pháp: ALTER VIEW Tên_View[(danh_sách_cột)] AS Câu_lệnh_Select 140
  141. 4. BẢNG ẢO (VIEW)  Xóa View bằng lệnh T – SQL Cú pháp: DROP VIEW Tên_View 141