CHƯƠNG 2: QUẢN TRỊ CSDL
1
GVPT: NGUYỄN THỊ MỸ DUNG SỐ TC: 2 SỐ TIẾT: LT: 20; TH: 20
Biên soạn: Nguyễn Thị Mỹ Dung
Chương 1: Tổng quan về SQL Server (LT: 2) Chương 2: Tạo và quản trị CSDL (LT: 6; TH: 6) Chương 3: Transact-SQL và truy vấn dữ liệu
(LT: 6: TH: 8)
Chương 4: Lập trình với Transact-SQL (LT: 4:
TH: 4)
Chương 5: Bảo Mật và Phân Quyền (LT: 2;
TH: 2)
2
Chương 6: Kết nối CSDL (Tự học)
Biên soạn: Nguyễn Thị Mỹ Dung
1
CHƯƠNG 2: QUẢN TRỊ CSDL
3
I. Cấu trúc các file của database II. Một số vấn đề thiết kế CSDL III. Thiết kế CSDL IV. Thiết kế bảng dữ liệu V. Import/ Export CSDL Bài tập chương 2
Biên soạn: Nguyễn Thị Mỹ Dung
1. Cấu trúc CSDL
SQL Server quản lý trực tiếp các CSDL, danh sách mỗi Server sẽ gồm danh sách các tên CSDL, tên các CSDL là duy nhất, không trùng nhau.
Cơ sở dữ liệu trong SQL Server lưu trữ theo 2
phần: phần dữ liệu (gồm một tập tin bắt buộc *.mdf [và các tập tin phụ *.ndf ]) và phần nhật ký (*.ldf). Như vậy một cơ sở dữ liệu có ít nhất 2 tập tin.
Cấu trúc logic trong CSDL gồm các table, view
4
và các object khác.
Biên soạn: Nguyễn Thị Mỹ Dung
2
CHƯƠNG 2: QUẢN TRỊ CSDL
5
Cấu trúc một CSDL như sau:
Biên soạn: Nguyễn Thị Mỹ Dung
2. Sơ đồ quản trị CSDL của SQL Server SQL Server chia thành 2 loại:
Lưu trữ các thông tin login, cấu hình hệ thống, thông tin quản trị CSDL,...
6
- CSDL hệ thống (do hệ thống tạo ra) Lưu trữ các tab le tạm thời, - CSDL người dùng (do người dùng tạo ra) các thủ tục,... phục vụ cho các User
Biên soạn: Nguyễn Thị Mỹ Dung
3
CHƯƠNG 2: QUẢN TRỊ CSDL
3. Cấu trúc File Database Tập tin lưu trữ một CSDL trong SQL Server có 2
7
loại: -- Primary Primary datadata filefile: Là file chính lưu trữ dữ liệu (*.mdf = Master Data File), mỗi CSDL có một file primary, lưu trữ điểm bắt đầu của một CSDL và các điểm kết nối đến các file lưu trữ tiếp theo. -- LogLog filefile: Là loại tập tin lưu trữ thông tin nhật ký của CSDL.
Biên soạn: Nguyễn Thị Mỹ Dung
1. Phân loại dữ liệu Có ba loại: dữ liệu cơ sở (chỉ thêm vào nhưng không thay đổi), dữ liệu gốc (phát sinh rất nhiều), và dữ liệu tạm thời (có thể xóa khi không cần).
2. Thiết kế các bảng trong CSDL Xác định tên CSDL, tên bảng, tên thuộc tính,
kiểu dữ liệu, khóa chính, khóa ngoại, RBTV
8
3. Nơi lưu trữ dữ liệu 4. Cách thức trao đổi và truyền dữ liệu
Biên soạn: Nguyễn Thị Mỹ Dung
4
CHƯƠNG 2: QUẢN TRỊ CSDL
1/ Tạo CSDL
Để tạo một cơ sở dữ liệu người dùng, các
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
thông tin sau được yêu cầu: Tên của cơ sở dữ liệu
9
để lưu trữ cơ sở dữ liệu
Biên soạn: Nguyễn Thị Mỹ Dung
Cú pháp để tạo một cơ sở dữ liệu người dùng là:
10
CREATE DATABASE database_name
[ ON
[ PRIMARY ] [
Biên soạn: Nguyễn Thị Mỹ Dung
5
CHƯƠNG 2: QUẢN TRỊ CSDL
Ví dụ để tạo một CSDL:
CREATE DATABASE Customer_DB ON PRIMARY ( NAME = N’Customer_DB’, FILENAME = N’ C:\Program Files\Microsoft SQL Server\MSSQL.1\ MSSQL\Data\Customer_DB.mdf’) LOG ON ( NAME = N’Customer_DB_log’, FILENAME = N’ C:\Program Files\Microsoft SQL Server\MSSQL.1 \MSSQL\Data\Customer_DB_log.ldf’) COLLATE SQL_Latin1_General_CP1_CI_AS
11
Biên soạn: Nguyễn Thị Mỹ Dung
2/ Sao lưu CSDL
Khái niệm
Sao lưu là công việc sao chép thông tin của database vào một thời điểm nhất định vào nơi lưu trữ khác. Vấn đề
– Thật sự có nhu cầu cần sao lưu dữ liệu? – Chiến lược sao lưu dữ liệu?
Phân loại
Có 4 loại sao lưu: Sao lưu toàn phần; Sao lưu một phần; Sao lưu bảng lưu vết của giao tác (transaction log); Sao lưu các bảng sao trong dữ liệu phân tán (duplicate data).
12
Biên soạn: Nguyễn Thị Mỹ Dung
6
CHƯƠNG 2: QUẢN TRỊ CSDL
Cú pháp
BACKUP DATABASE
DIFFERENTIAL: Sao lưu một phần, mặc định [ WITH {DIFFERENTIAL |
+ DESCRIPTION: ghi chú sao lưu + NAME: tên bảng lưu + PASSWORD: mật khẩu sao lưu + RETAINDAYS: ngày hết hạn của sao lưu tính từ thời điểm sao lưu + EXPIREDATE: hạn cuối của sao lưu + STOP_ON_ERROR|CONTINUE_AFTER_ERROR: ngừng khi gặp lỗi
v à ngược lại
+ STATS: phần trăm thể hiện trong quá trình sao lưu
13
- Các tham số có thể có (cách nhau dấu “,”):
Biên soạn: Nguyễn Thị Mỹ Dung
Ví dụ
VD1: Sao lưu toàn phần
BACKUP DATABASE qldiemsv TO DISK = 'E:\qldiemsv.bak' --WITH NAME = 'qldiemsv', STATS = 10
VD2: Sao lưu một phần
BACKUP DATABASE qldiemsv TO DISK = 'E:\qldiemsv.bak'
14
--WITH DIFFERENTIAL, NAME = 'qldiem', STATS = 10
Biên soạn: Nguyễn Thị Mỹ Dung
7
CHƯƠNG 2: QUẢN TRỊ CSDL
VD3: BACKUP DATABASE QUANLYBANHANG TO DISK= 'D:\ Hoadon_BK.bak' [WIT H DESCRIPTION = 'Backup database
Hoadon vào ổ đĩa D', STATS,
15
PASSWORD = 'sa', NAME = 'QLHANGHOA']
Biên soạn: Nguyễn Thị Mỹ Dung
3/ Khôi phục CSDL - Phục hồi hệ thống khi có sự cố hoặc thời điểm
bất kỳ nào đó, dù không có sự cố xảy ra.
- Tiết kiệm chi phí và thời gian.
Cú pháp:
RESTORE DATABASE
+ STOP_ON_ERROR|CONTINUE_AFTER_ERROR + FILE + PASSWORD + RECOVERY | NORECOVERY
16
DISK = ‘path\filename.bak’
[WITH {
Biên soạn: Nguyễn Thị Mỹ Dung
8
CHƯƠNG 2: QUẢN TRỊ CSDL
Ví dụ VD1: RESTORE DATABASE qldiemsv FORM DISK =
'D:\qldiemsv.BAK‘
VD2: RESTORE DATABASE QLHANGHOA FROM DISK= 'D:\HANGHOA.bak' WITH FILE = 1, NORECOVERY, PASSWORD =
'sa', REPLACE
VD3: RESTORE DATABASE QLHANGHOA FROM DISK= 'D:\HANGHOA.bak' WITH RECOVERY, PASSWORD = 'sa', REPLACE
17
Biên soạn: Nguyễn Thị Mỹ Dung
4/ Sửa CSDL
Sửa tên Database
ALTER DATABASE
NAME =
VD: ALTER DATABASE QLDIEMSV MODIFYNAME
= QLDSV
Ví dụ thay đổi quyền sở hữu của CSDL:
18
USE QLDIEMSV EXEC sp_changedbowner ‘sa’
Biên soạn: Nguyễn Thị Mỹ Dung
9
CHƯƠNG 2: QUẢN TRỊ CSDL
5/ Xoá CSDL
DROP DATABASE TênCSDL
Ví dụ:
DROP DATABASE QLBANHANG
6/ Chọn và mở database
USE TênCSDL
Ví dụ:
19
USE QLDSV
Biên soạn: Nguyễn Thị Mỹ Dung
Ví dụ: Cho các lược đồ quan hệ sau
Sinhvien (MaSV, Hoten, Namsinh, QQ, Hocluc) Detai (MaDT, TenDT, Chunhiem, Kinhphi) SV_DT (MaSV, MaDT, Noi_AD, KQ)
Bảng dữ liệu mẫu như sau:
Sinhvien MaSv
Hoten
Namsinh
Hocluc
SV001 SV001
Thái Bình An Thái Bình An
19831983
Cần Thơ Cần Thơ
7.57.5
SV002 SV002
Lê Văn Khang Lê Văn Khang
19851985
Vĩnh Long Vĩnh Long
7.17.1
SV003 SV003
Trần Đức Thịnh Trần Đức Thịnh
19831983
Đồng Tháp Đồng Tháp
8.18.1
SV004 SV004
Bùi Quốc Vượng Bùi Quốc Vượng
19841984
Đồng Tháp Đồng Tháp
9.29.2
SV005 SV005
Lâm Nhân Nghĩa Lâm Nhân Nghĩa
19851985
Tiền Giang Tiền Giang
6.56.5
SV006 SV006
Hà Công Sự Hà Công Sự
19821982
Cần Thơ Cần Thơ
6.06.0
SV007 SV007
Hồ Phương Thanh Hồ Phương Thanh
19851985
An giang An giang
8.28.2
SV008 SV008
Vũ Thị Hảo Ý Vũ Thị Hảo Ý
19861986
Bạc Liêu Bạc Liêu
5.85.8
20
Biên soạn: Nguyễn Thị Mỹ Dung
10
CHƯƠNG 2: QUẢN TRỊ CSDL
Detai
MaDT
TenDT
Chunhiem
Kinhphi
DT001 DT001
Quản lý Nhân sự Quản lý Nhân sự
Nguyễn Thị Xuân Nguyễn Thị Xuân
1010
DT002 DT002
Web site Bán hàng qua mạng Web site Bán hàng qua mạng
Hồ Văn Quang Hồ Văn Quang
1515
DT003 DT003
Bảo mật trong thương mại điện tử Bảo mật trong thương mại điện tử
Trần Hiếu Hạnh Trần Hiếu Hạnh
2020
DT004 DT004
Quản lý lịch công tác trên mạng Quản lý lịch công tác trên mạng
Lê Đức Phúc Lê Đức Phúc
88
DT005 DT005
Bộ soạn thảo công thức toán học Bộ soạn thảo công thức toán học
Trần Hiếu Hạnh Trần Hiếu Hạnh
99
DT006 DT006
Web site đào tạo từ xa Web site đào tạo từ xa
Lê Đức Phúc Lê Đức Phúc
2525
SV_DT
MaSV
MaDT
Noi_AD
KQ
SV001 SV001
DT001 DT001
Đồng Tháp Đồng Tháp
9.59.5
SV002 SV002
DT002 DT002
Vĩnh Long Vĩnh Long
9.09.0
SV003 SV003
DT001 DT001
Trà Vinh Trà Vinh
9.09.0
SV004 SV004
DT002 DT002
Đồng Tháp Đồng Tháp
10.010.0
SV007 SV007
DT004 DT004
Trà Vinh Trà Vinh
9.09.0
SV008 SV008
DT003 DT003
Bạc Liêu Bạc Liêu
10.010.0
SV001 SV001
DT004 DT004
Đồng Tháp Đồng Tháp
9.59.5
SV008 SV008
DT002 DT002
Trà Vinh Trà Vinh
9.09.0
SV002 SV002
DT005 DT005
Cà Mau Cà Mau
8.58.5
SV001 SV001
DT005 DT005
Tiền Giang Tiền Giang
8.08.0
SV006 SV006
DT003 DT003
Đồng Tháp Đồng Tháp
9.59.5
21
Biên soạn: Nguyễn Thị Mỹ Dung
1/ Tạo bảng (Table)
CREATE TABLE
)
Các RBTV:
NOT NULL NULL UNIQUE PRIMARY KEY FOREIGN KEY / REFERENCES
22
Biên soạn: Nguyễn Thị Mỹ Dung
11
CHƯƠNG 2: QUẢN TRỊ CSDL
Ví dụ: Lấy CSDL “sinh viên thực hiện đề tài”
CREATE TABLE SINHVIEN (
MASV CHAR(10) PRIMARY KEY, HOTENSV CHAR(40), NAMSINH INT, QUEQUAN CHAR(40), HOCLUC FLOAT
) CREATE TABLE DETAI(
MADT CHAR(10) PRIMARY KEY, TENDT CHAR(40), CHUNHIEM CHAR(40), KINHPHI INT
)
23
Biên soạn: Nguyễn Thị Mỹ Dung
CREATE TABLE SV_DT (
MASV CHAR(10) NOT NULL, MADT CHAR(10) NOT NULL, NOIA_D CHAR(40), KETQUA FLOAT, PRIMARY KEY (MASV, MADT), CONSTRAINT SVDT_SV FOREIGN KEY
(MASV) REFERENCES SINHVIEN(MASV),
CONSTRAINT SVDT_DT FOREIGN KEY
(MADT) REFERENCES DE_TAI(MADT)
)
24
Biên soạn: Nguyễn Thị Mỹ Dung
12
CHƯƠNG 2: QUẢN TRỊ CSDL
2/ Thay đổi thuộc tính bảng
Thêm cột: ALTER TABLE
Xóa cột: ALTER TABLE
DROP COLUMN
Mở rộng cột: ALTER TABLE
25
ALTER COLUMN
Biên soạn: Nguyễn Thị Mỹ Dung
Thêm RBTV: ALTER TABLE ADD
CONSTRAINT ,
CONSTRAINT ,
…
Xóa RBTV:
ALTER TABLE DROP
Ví dụ:
CREATE TABLE HANGHOA(
MAHG CHAR(10), TENHANG CHAR(50), DVT CHAR(5),
26
Biên soạn: Nguyễn Thị Mỹ Dung
13
CHƯƠNG 2: QUẢN TRỊ CSDL
Thêm thuộc tính bảng:
ALTER TABLE HANGHOA ADD
DONGIA FLOAT
Mở rộng kiểu dữ liệu cho thuộc tính bảng:
ALTER TABLE HANGHOA
ALTER COLUMN DONGIA BIGINT
Xóa thuộc tính bảng:
ALTER TABLE HANGHOA
27
DROP COLUMN DVT
Biên soạn: Nguyễn Thị Mỹ Dung
3/ Xóa bảng
DROP TABLE
Ví dụ:
28
DROP TABLE HANGHOA DROP TABLE SINHVIEN
Biên soạn: Nguyễn Thị Mỹ Dung
14
CHƯƠNG 2: QUẢN TRỊ CSDL
Sinh viên tự học LưuLưu ýý:: - Import/ Export:
thực hiện đối với phiên bản SQL Enterprise,… không hỗ trợ cho phiên bản Express.
- Import/Export dữ liệu sang Excel và Access. - Import/Export dữ liệu thao tác bằng lệnh và
29
click chọn.
Biên soạn: Nguyễn Thị Mỹ Dung
- Cấu trúc file CSDL (các thành phần trong
CSDL)
- Thiết kế CSDL
+ Tạo, sửa, xóa CSDL + Backup/ Restore CSDL
- Thiết kế bảng dữ liệu
+ Tạo, thêm, sửa, xóa bảng + Tạo, thêm, sửa xóa RBTV
30
- Import/ Export dữ liệu (Excel, Access)
Biên soạn: Nguyễn Thị Mỹ Dung
15
