Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 2: Xây dựng - Quản lí - Khai thác CSDL - Hồ Lê Thị Kim Nhung

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 2: Xây dựng - Quản lí - Khai thác CSDL - Hồ Lê Thị Kim Nhung

1. Nội dung 1 Khái niệm CSDL 2 Tạo và quản lý CSDL 3 Các kiểu dữ liệu 4 Tạo và quản lý bảng 5 Ràng buộc toàn vẹn đơn giản 6 Các thao tác trên dữ liệu www.themegallery.com
2. Khái niệm CSDL - logic vTrong SQL server: CSDL được chia thành 2 loại § CSDL của hệ thống § CSDL do người dùng tạo www.themegallery.com
3. SQL server 2008 www.themegallery.com
4. T-SQL language statements SELECT CREATE GRANT BEGIN TRAN INSERT ALTER REVOKE COMMIT TRAN UPDATE DROP DENY ROLLBACK TRAN DELETE Data Manipulation Language: ảnh hưởng trên các dòng của bảng Data Definition Language: xử lí thiết kế các đối tượng trong csdL Data Control Language: điều khiển các mức độ truy cập để bảo mật Transaction Control Language: cho phép quản lí giao tác www.themegallery.com
5. Tạo CSDL Tên DB, không trùng, tối đa 128 char CREATE DATABASE database_name Danh sách file DL nếu muốn [ ON [ [PRIMARY] [ , n ] ]] chỉ định cụ thể [ LOG ON { [ , n ] } ] Danh sách file log nếu muốn chỉ định cụ thể định nghĩa như sau Tên file dùng trong môi ( [ Name = logical_file_name , ] trường SQL Server Filename = 'os_file_name' Tên thực sự của file [ , SIZE = size ] (bao gồm đường dẫn) [ , Maxsize = { max_size | UNLIMITED } ] [ , Filegrowth = growth_increment ] ) [ , n ] www.themegallery.com
6. Ví dụ CREATE DATABASE QuanLyHangHoa ON PRIMARY ( NAME = Primary_File, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Secondary_File_1, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf', SIZE = 100MB, Có giá trị : KB, TB, MB. MAXSIZE = 200, Mặc định là MB. FILEGROWTH = 20) www.themegallery.com
7. Tăng kích thước file data ALTER DATABASE MODIFY FILE ( NAME = logical_file_name [ , NEWNAME = new_logical_name ] [ , FILENAME = 'os_file_name' ] [ , SIZE = size ] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) www.themegallery.com
8. Xem kích thước CSDL SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = ’TênCSDL‘ sp_helpdb ‘TênCSDL’ www.themegallery.com
9. Ví dụ: CSDL QLSV_2011 sp_helpdb 'qlsv_2011' www.themegallery.com
10. Quản lý CSDL vGắn CSDL vào SQL Server: EXEC sp_attach_db [@dbname = ] , [@filename=] [, n] vGở một CSDL trong SQL Server EXEC sp_detach_db [@dbname=] www.themegallery.com
11. Attach SQL Server www.themegallery.com
12. Bài tập 1 CREATE DATABASE QLSV ON (NAME = QLSV_DATA, FILENAME = 'D:\QLSV_DATA.MDF', SIZE = 1MB, FILEGROWTH = 10% ) LOG ON (NAME = QLSV_LOG, FILENAME = 'D:\QLSV_LOG.LDF', SIZE = 2MB, FILEGROWTH = 10MB) www.themegallery.com
13. Bài tập 2 CREATE DATABASE QLTV ON (NAME = QLTV, FILENAME = 'D:\CSDL.MDF', SIZE = 20MB, MAXSIZE = 100MB, FILEGROWTH = 25 ), (NAME = QTTV_1, FILENAME = 'D:\CSDL_1.NDF', SIZE = 20MB, MAXSIZE = 100MB, FILEGROWTH = 25MB ) www.themegallery.com
14. Kiểu dữ liệu System Kiểu dữ liệu cơ sở do hệ datatype quản trị cung cấp DataType User Kiểu dữ liệu do người dùng defined tự định nghĩa trên kiểu cơ datatype sở www.themegallery.com
15. Kiểu dữ liệu cơ sở vSố § Số nguyên: • Bit : 0 à 1 • Tinyint : 0 à 255 • Smallint : -32,768 à 32,767 • Int : 2-31 à 231 - 1 • Bigint : –263 à 263–1 www.themegallery.com
16. Kiểu dữ liệu cơ sở vChuỗi § char(n): 8000bytes § nchar(n) : 4000bytes § varchar(n): 8000bytes § nvarchar(n) : 4000bytes § text; ntext : 2GB Text Varchar •Dùng khi cần lưu đoạn text lớn trong CSDL •Dùng khi lưu các chuỗi có chiều dài ngắn •Không thực hiện tìm kiếm giá trị trong chuỗi •Tìm kiếm giá trị trong chuỗi •Ít truy vấn và ko thực hiện kết trên cột này •Thường dùng để truy vấn và kết •Blog comments •Usernames •Wiki pages •Page titles •www.themegallery.comCode source •Filenames
17. Kiểu tự định nghĩa vDùng thủ tục sp_addtype Tên của kiểu dữ liệu mới (do người dùng đặt) sp_addtype Kiểu dữ cơ sở (không gán [ @typename = ] type, được giá trị mặc định) [ @phystype = ] system_data_type, Cho phép null hay [ , [ @nulltype = ] 'null_type' ] không: ‘NULL’ (mặc định) hay ‘NOT NULL’ www.themegallery.com
18. Ví dụ www.themegallery.com
19. Nội dung 1 Khái niệm CSDL 2 Tạo và quản lý CSDL 3 Các kiểu dữ liệu 4 Tạo và quản lý bảng 5 Ràng buộc toàn vẹn đơn giản 6 Các thao tác trên dữ liệu www.themegallery.com
20. Schema Schema trong SQL Server để nhóm các Database Object lại với nhau cho dễ quản lý. Ví dụ: nhóm các table và Stored procedure liên quan đến bộ phận nhân sự HumanResource vào 1 Schema có tên là HumanResource. Ví dụ: 1 table là Salary đặt trong Schema HumanResource, thì ta gọi table này bằng cách như sau: 1Select columnA, columnB from HumanResouce.Salary Schema là bảo mật (Security Policy), ta có thể phân quyền ở cấp độ toàn Schema thay vì từng object riêng biệt trong Schema. èTrong 1 database ta có thể đặt tên 2 table giống nhau, với điều kiện chúng phải thuộc 2 schema khác nhau. Schema mặc định của các object trong database là dbo. www.themegallery.com
21. Schema § Bảo mật § Gom nhóm các đối tượng § Giới hạn quyền thao tác của user Tên schema CREATE SCHEMA [AUTHORIZATION ] Tên user tạo schema. Mặc định là dbo www.themegallery.com
22. Ví dụ www.themegallery.com
23. Quản lý schema vXóa schema DROP SCHEMA vChuyển đối tượng trong schema ALTER SCHEMA TRANSFER . www.themegallery.com
24. Tạo bảng vTạo bảng § Xác định cột (thuộc tính) của bảng § Xác định khóa chính § Xác định thuộc tính null/ not null § Xác định thuộc tính identity (nếu có) (phải là kiểu số nguyên) vLư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. www.themegallery.com
25. Ví dụ Create table SINHVIEN ( MaSV int not null IDENTITY (1,2), Lop char(5) not null default ’09CK1’ , HoTen nvarchar(30) not null, NgaySinh datetime not null, DiaChi nvarchar(100), constraint pk_HS primary key (MaSV) ) www.themegallery.com
26. Ví dụ Set identity seed Increment www.themegallery.com
27. Qui định vCác tên bảng, tên ràng buộc không được trùng nhau trong cùng một database. v Tên các cột trong cùng một bảng không được trùng nhau. vThông tin về các bảng, các ràng buộc được lưu trong bảng hệ thống sys.objects § Ví dụ: Đọc thông tin về các bảng trong database hiện hành: Select * from sys.objects where type = ‘U’ www.themegallery.com
28. Thủ tục của hệ thống vsp_tables [‘table_name’] [, ‘owner’] [,’database_name’][, “ ’type’ ”] Xem DS bảng § Ví dụ: (View & Table) EXEC sp_tables null, null, ‘QLSV_2011’, “‘TABLE’” www.themegallery.com
29. Thủ tục của hệ thống vsp_columns table_name [, owner] [, database] [,column] Xem DS cột § Ví dụ: trong bảng EXEC sp_columns CanBo, null, QLSV_2011 www.themegallery.com
30. Thủ tục của hệ thống vsp_help [object_name] § 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 SinhVien www.themegallery.com
31. Bài tập 3 SinhVien(MaSV, HoTen, NgaySinh, CMND, DiemTB) CanBo(MaCB, HoTen, NgaySinh, DienThoai, MaBM) DangKy(MaSV, MaMH, NgayDangKy) MonHoc(MaMH, TenMH, MaCBPhuTrach) BoMon(MaBM, MaCBQuanLy) : § DiemTB: mặc định là 0. § Ngày đăng kí mặc định là ngày hiện hành. § MaBM kiểu số tự tăng www.themegallery.com
32. Bài tập 5 § Xác định quan hệ nào thuộc giáo viên, quan hệ nào thuộc sinh viên. § Hãy chuyển các quan hệ thuộc sở hữu của sinh viên vào schema SinhVien. § Chuyển các quan hệ thuộc sở hữu của giáo viên vào schema GiaoVien. www.themegallery.com
33. Ràng buộc toàn vẹn Quan hệ chứa? Loại ràng buộc? Tên ràng buộc? Điều kiện? www.themegallery.com
34. RBTV đơn giản vSQL cung cấp sẵn cơ chế để kiểm tra vBao gồm: § Khóa chính (primary key constraint) § Khoá ngoại (foreign key constraint) § Giá trị duy nhất (unique constraint) § 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ệ) vCó thể khai báo ràng buộc trong lúc tạo bảng hoặc khi bảng đã tồn tại www.themegallery.com
35. Ví dụ Create table SinhVien ( MaSV char(10) not null, Nam tinyint, CMND char(10), constraint pk_SV primary key (MaSV), constraint u_CMND unique (CMND), constraint chk_Nam check (Nam > 0 and Nam <= 4) ) www.themegallery.com
36. Bài tập 6 v Xác định và cài đặt các ràng buộc toàn vẹn cho Bài tập 3 gồm: § Khóa ngoại. § Duy nhất. § Giá trị § Default www.themegallery.com
37. Khai báo RBTV vDEFAULT gán giá trị mặc định cho cột. § Đặc điểm ü Áp dụng trên thuộc tính cần khởi tạo giá trị ban đầu. ü Mỗi RB chỉ áp dụng trên một thuộc tính /một bảng § Cú pháp: ALTER TABLE ADD CONSTRAINT DEFAULT FOR §Lỗi tạo: mỗi cột chỉ được đặt một ràng buộc Default. www.themegallery.com
38. Ví dụ RB default www.themegallery.com
39. Ví dụ: set null www.themegallery.com
40. Ví dụ Alter table SINHVIEN with check add constraint u_CMND unique (CMND), constraint chk_Nam check (Nam in (1, 2) ), constraint fk_SinhVien_maKhoa foreign key (Khoa) references KHOA(MaKhoa) /* giả sử đã tồn tại bảng KHOA( MaKhoa, ) */ www.themegallery.com
41. Kiểm tra/ không kiểm tra Alter table Table_name {Check| Nocheck} constraint { All | constraint_name [, n] } • Ví dụ alter table SINHVIEN nocheck constraint fk_SinhVien_Khoa Chỉ áp dụng cho Check & Foreign Key www.themegallery.com
42. Xóa ràng buộc Alter table table_name Drop { [constraint ] constraint_name } [, n] • Ví dụ: Alter table SINHVIEN drop constraint u_CMND, chk_Nam www.themegallery.com
43. Thêm/ xoá/ sửa dữ liệu vChú ý khi thêm/ xóa / cập nhật § Dữ liệu nhập phải với dữ liệu các toàn vẹn § Định dạng giá trị kiểu , kiểu § Nhập giá trị rỗng (null) 86 www.themegallery.com
44. Ví dụ INSERT INTO SinhVien (Hoten, NgaySinh, DiemTB) VALUES ( N'Trương Hà‘ , '1/1/1990', 3.4), (N'Nguyễn Văn A‘ , '1/1/1992', 7.0) INSERT INTO SinhVien (Hoten, NgaySinh, DiemTB) SELECT HoTen, NgaySinh, null FROM SINH_VIEN www.themegallery.com
45. Bài tập 7 v INSERT INTO CanBo(MaCB, HoTen, NgaySinh, DienThoai) VALUES ( ‘CB001’, Nguyễn Thái Bình,1/1/1990, 0905743576), VALUES ( ‘CB001’, Nguyễn Thái Bình,'1/1/1000', 0905743576), ( ‘CB002’, ‘Nguyễn Thái Bình’,'1/1/1990', ‘0905743576’) (SELECT HoTen, NgaySinh, null FROM GIAO_VIEN) GO INSERT CanBo(MaCB, HoTen, NgaySinh, DienThoai) (SELECT MaCB, HoTen, NgaySinh FROM GIAO_VIEN WHERE NgaySinh IS NOT NULL) SELECT * FROM CanBo www.themegallery.com
46. Bài tập 8 v Xác định thứ tự nhập liệu vào quan hệ. v Nhập liệu vào quan hệ DangKy với thông tin như sau: § MaSV: lấy từ bảng SinhVien. § MaMH: lấy từ bảng môn học với tên môn học là ‘Quản trị CSDL’. v Xem dữ liệu quan hệ vừa nhập. v Nhập liệu bảng CanBo bằng cách đổ dữ liệu từ bảng Can_Bo của CSDL QLSV www.themegallery.com
47. Ví dụ vVí dụ 2 INSERT INTO LoaiDiaOc (maLoai,tenLoai) output inserted.tenLoai VALUES(15, N‘biệt thự’) vVí dụ 3 DELETE from HangHoa output deleted.DonGia WHERE DonGia < 100000 www.themegallery.com
48. Ví dụ (tt) DELETE FROM HangHoa output deleted.MaHg, deleted.DonGia INTO HH WHERE DonGia < 100000 SELECT * INTO HH Bảng HH phải chưa tồn tại trong CSDL FROM HangHoa WHERE MaHH Like ‘%2’ www.themegallery.com
49. Ví dụ (tt) Delete Top(@n) from HangHoa where DonGia < 100000 www.themegallery.com
50. Q & A • www.themegallery.com