Cơ Sử Dữ Liệu SQL
Chia sẻ bởi Bạch Long Vĩ |
Ngày 29/04/2019 |
53
Chia sẻ tài liệu: Cơ Sử Dữ Liệu SQL thuộc Bài giảng khác
Nội dung tài liệu:
Đại học Công Nghệ Thông Tin
Môn: Cơ Sở Dữ Liệu
Thời lượng: 45LT+30TH
Nội dung
Bài 1: Các khái niệm của một hệ CSDL
Bài 2: Các mô hình CSDL
Bài 3: Mô hình dữ liệu quan hệ (của Codd)
Bài 4: Ngôn ngữ đại số quan hệ
Bài 5: Ngôn ngữ SQL
Bài 6: Ngôn ngữ tân từ
Bài 7: Ràng buộc toàn vẹn trong một CSDL
Bài 8: Tối ưu hóa câu hỏi bằng đại số quan hệ
Bài 1: Các khái niệm của một hệ CSDL
Bài 1: Các khái niệm của một hệ CSDL
Giới thiệu
Hệ thống tập tin (File System)
Định nghĩa một CSDL
Các đối tượng sử dụng CSDL
Hệ quản trị CSDL
Các mức của một CSDL
1. Giới thiệu
Tại sao cần phải có một cơ sở dữ liệu?
2. Hệ thống tập tin
Là tập hợp các tập tin riêng lẻ phục vụ cho một mục đích của đơn vị sử dụng.
Ưu điểm:
Triển khai ứng dụng nhanh
Khả năng đáp ứng nhanh chóng, kịp thời (vì chỉ phục vụ cho mục đích hạn hẹp)
Khuyết điểm:
Trùng lắp dữ liệu lãng phí, dữ liệu không nhất quán
Chi phí cao
Chia sẽ dữ liệu kém
3. Cơ sở dữ liệu (1)
Định nghĩa:
Cơ sở dữ liệu là một hệ thống các thông tin có cấu trúc, được lưu trữ trên các thiết bị lưu trữ nhằm thỏa mãn yêu cầu khai thác thông tin đồng thời của nhiều người sử dụng hay nhiều chương trình ứng dụng với những mục đích khác nhau
3. Cơ sở dữ liệu (2)
Ưu điểm:
Giảm trùng lắp thông tin xuống mức thấp nhất, đảm bảo tính nhất quán và toàn vẹn dữ liệu.
Đảm bảo dữ liệu được truy xuất theo nhiều cách khác nhau.
Khả năng chia sẽ thông tin cho nhiều người, nhiều ứng dụng khác nhau.
3. Cơ sở dữ liệu (3)
Những vấn đề cần giải quyết:
Tính chủ quyền dữ liệu.
Tính bảo mật và quyền khai thác thông tin của người sử dụng
Tranh chấp dữ liệu
Đảm bảo dữ liệu khi có sự cố
4. Các đối tượng sử dụng
Người sử dụng CSDL không chuyên về lĩnh vực tin học và CSDL -> cần công cụ để họ có thể khai thác CSDL khi cần.
Chuyên viên tin học xây dựng các ứng dụng để phục vụ cho các mục đích quản lý
Quản trị CSDL: tổ chức CSDL, bảo mật, cấp quyền, sao lưu, phục hồi dữ liệu, giải quyết các tranh chấp dữ liệu …
5. Hệ quản trị cơ sở dữ liệu (1)
Hệ quản trị cơ sở dữ liệu (DBMS – DataBase Management System) là hệ thống các phần mềm hỗ trợ tích cực cho các nhà phân tích, thiết kế và khai thác CSDL.
Các DBMS thông dụng: Visual FoxPro, Microsoft Access, SQL Server, DB2, Oracle … hầu hết các DBMS hiện nay đều dựa trên mô hình quan hệ.
5. Hệ quản trị cơ sở dữ liệu (2)
Một DBMS phải có:
Ngôn ngữ giao tiếp giữa người sử dụng và CSDL
Từ điển dữ liệu (Data Dictionary)
Có biện pháp bảo mật khi có yêu cầu
Cơ chế giải quyết tranh chấp dữ liệu
Có cơ chế sao lưu (backup), phục hồi (restore)
Đảm bảo tính độc lập giữa dữ liệu và chương trình
5. Hệ quản trị cơ sở dữ liệu (3)
Ngôn ngữ giao tiếp:
Ngôn ngữ mô tả dữ liệu (DDL – Data Definition Language): cho phép khai báo cấu trúc CSDL, các mối liên hệ của dữ liệu, các quy định, ràng buộc dữ liệu.
Ngôn ngữ thao tác dữ liệu (DML – Data Manipulation Language): cho phép thực hiện thao tác thêm, xóa, sửa dữ liệu.
Ngôn ngữ truy vấn có cấu trúc (SQL – Structured Query Language): cho phép người khai thác sử dụng để truy vấn thông tin cần thiết.
Ngôn ngữ quản lý dữ liệu (DCL – Data Control Language) cho phép thay đổi cấu trúc bảng, khai báo bảo mật, cấp quyền cho người sử dụng.
6. Các mức biểu diễn 1 CSDL
Mức trong: (mức vật lý – Physical) là mức lưu trữ CSDL (cần giải quyết vấn đề gì? Dữ liệu gì? Lưu trữ như thế nào? ở đâu? Cần các chỉ mục gì? Truy xuất tuần tự hay ngẫu nhiên. Dành cho người quản trị và người sử dụng chuyên môn.
Mức quan niệm: (Conception hay Logical) cần phải lưu trữ bao nhiêu loại dữ liệu? là dữ liệu gì? mối quan hệ
Mức ngoài: của người sử dụng và các chương trình ứng dụng
6. Các mức biểu diễn 1 CSDL
Cấu trúc
ngoài 1
Cấu trúc
ngoài 2
Cấu trúc
ngoài n
Chương trình ứng dụng n
Mức
quan
niệm
hoặc
mức
logic
Mức vật lý –
Cấu trúc vật lý
CSDL
Môi trường
thực thế giới
thực
NSD1
NSD2
Bài 2: Các mô hình dữ liệu
Nội dung
Giới thiệu
Mô hình dữ liệu mạng
Mô hình thực thể mối kết hợp
1. Giới thiệu
Mô hình dữ liệu là sự trừu tượng hóa của môi trường thực, biểu diễn dữ liệu ở mức quan niệm. Giới thiệu một số mô hình như:
Mô hình dữ liệu mạng
Mô hình dữ liệu phân cấp
Mô hình dữ liệu thực thể mối kết hợp
Mô hình dữ liệu quan hệ
Mô hình dữ liệu hướng đối tượng
2. Mô hình dữ liệu mạng (1)
Mô hình dữ liệu mạng (Network Data Model) còn gọi tắt là mô hình mạng hoặc mô hình lưới là mô hình được biểu diễn bởi một đồ thị có hướng.
Mẫu tin (record)
Loại mẫu tin
Loại liên hệ (set type)
Bản số
2. Mô hình dữ liệu mạng (2)
Mẫu tin: mô tả 1 đối tượng trong thế giới thực.
(‘NV001’,’Nguyen Lam’,’Nam’,’10/10/1970’,’Dong Nai’)
Loại mẫu tin: là 1 tập các mẫu tin có cùng tính chất. Ví dụ: NHANVIEN
Ký hiệu:
Loại liên hệ: mô tả sự liên kết giữa 1 loại mẫu tin chủ và 1 loại mẫu tin thành viên
Ký hiệu:
NHANVIEN
Tham gia
CONGVIEC
2. Mô hình dữ liệu mạng (3)
Bản số: chỉ ra số lượng các mẫu tin tham gia trong mối liên hệ
(1:1) (one-to-one): mỗi mẫu tin của loại mẫu tin chủ kết hợp với đúng 1 mẫu tin của loại mẫu tin thành viên.
(1:n) (one-to-many): mỗi mẫu tin của loại mẫu tin chủ kết hợp với 1 hay nhiều mẫu tin thành viên.
(n:1) (many-to-one): nhiều mẫu tin của loại mẫu tin chủ kết hợp với đúng 1 mẫu tin của loại mẫu tin thành viên.
(Recursive): một loại mẫu tin chủ cũng có thể đồng thời là loại mẫu tin thành viên với chính nó. Loại liên hệ này là Đệ quy
2. Mô hình dữ liệu mạng (4)
PHONG
CONGVIEC
NHANVIEN
LYLICH
gồm
có
quản lý
trực tiếp
cùng làm
n:1
1:1
1:n
1:n
2. Mô hình dữ liệu mạng (5)
Mô hình dữ liệu mạng:
Tương đối đơn giản
Dễ sử dụng
Không thích hợp biểu diễn CSDL có quy mô lớn
Khả năng diễn đạt ngữ nghĩa kém
2. Mô hình dữ liệu mạng (6)
Bài tập:
Xây dựng mô hình dữ liệu mạng cho cơ sở dữ liệu quản lý bán hàng trong một siêu thị (giáo viên mô tả hiện trạng, xác định các yêu cầu)
3. Mô hình thực thể mối kết hợp
3.1 Giới thiệu
3.2 Loại thực thể, thực thể
3.3 Thuộc tính của loại thực thể
3.4 Khoá của loại thực thể
3.5 Loại mối kết hợp, mối kết hợp
3.6 Thuộc tính của loại mối kết hợp
3.7 Bản số
3.8 Mô hình ER mở rộng
3.1 Giới thiệu
Mô hình thực thể mối kết hợp (Entity-Relationship Model viết tắc ER) được CHEN giới thiệu năm 1976.
Mô hình ER được sử dụng nhiều trong thiết kế dữ liệu ở mức quan niệm.
3.2 Loại thực thể
Định nghĩa: loại thực thể (Entity Type) là những loại đối tượng hay sự vật của thế giới thực tồn tại cụ thể cần được quản lý.
Ví dụ : HOCVIEN, LOP, MONHOC, …
Ký hiệu:
HOCVIEN
LOP
3.2 Thực thể (Entity)
Định nghĩa: thực thể là một thể hiện hoặc một đối tượng của một loại thực thể.
Ví dụ: Loại thực thể là HOCVIEN có các thực thể:
(‘HV001’, ‘Nguyen Nam’, ‘1/2/1987’,’Nam’)
(‘HV002’, ‘Trần Nam’, ‘13/2/1987’, ‘Nam’)
3.3 Thuộc tính của loại thực thể
(Entity Attribute)
Định nghĩa: thuộc tính là những tính chất đặc trưng của loại thực thể cần lưu trữ.
Ví dụ: Loại thực thể HOCVIEN có các thuộc tính: Mã học viên, họ tên, giới tính, ngày sinh, nơi sinh
Ký hiệu:
HOCVIEN
Hoten
Gioitinh
Mahv
Ngaysinh
Noisinh
3.3 Các loại thuộc tính (1)
Đơn trị (Simple): mỗi thực thể chỉ có một giá trị ứng với các thuộc tính.
Ví dụ: Mahv,Hoten
Đa hợp (Composite): thuộc tính có thể được tạo thành từ nhiều thành phần.
Ví dụ: DCHI(SONHA,DUONG,PHUONG,QUAN) hay thuộc tính HOTEN(HO,TENLOT,TEN).
Đa trị (Multi-valued): thuộc tính có thể có nhiều giá trị đối với một thực thể.
Ví dụ: BANGCAP ký hiệu {BANGCAP}
3.3 Các loại thuộc tính (2)
Tóm lại, các thuộc tính đa hợp và đa trị có thể lồng nhau tùy ý.
Ví dụ: thuộc tính BANGCAP của HOCVIEN là một thuộc tính đa hợp được ký hiệu bằng
{BANGCAP(TRUONGCAP,NAM,KETQUA,
CHUYENNGANH)}
3.4. Khoá của loại thực thể
(entity type key)
Khóa của loại thực thể là thuộc tính nhận diện thực thể.
Căn cứ vào giá trị của khóa có thể xác định duy nhất một thực thể.
Ví dụ:
Mỗi học viên có một mã số duy nhất => Khoá của loại thực thể HOCVIEN là Mã học viên
Định nghĩa: loại mối kết hợp là sự kết hợp giữa hai hay nhiều loại thực thể
Ví dụ: giữa hai loại thực thể HOCVIEN và LOP có loại mối kết hợp THUOC
Ký hiệu: bằng một hình oval hoặc hình thoi
3.5 Loại mối kết hợp (1)
(relationship type)
3.5 Loại mối kết hợp (2)
Giữa hai loại thực thể có thể tồn tại nhiều hơn một loại mối kết hợp.
Ví dụ
3.5 Số ngôi của loại mối kết hợp (relationship degree)
Số ngôi của loại mối kết hợp là số loại thực thể tham gia vào loại mối kết hợp đó.
Ví dụ 1: Loại mối kết hợp Thuộc kết hợp 2 loại thực thể HOCVIEN và LOP nên có số ngôi là 2.
Ví dụ 2: Loại mối kết hợp Giảng dạy kết hợp 3 loại thực thể GIAOVIEN, MONHOC, LOP nên có số ngôi là 3.
3.5 Số ngôi của loại mối kết hợp
3.6 Thuộc tính của loại mối kết hợp
(relationship type attribute)
Thuộc tính của loại mối kết hợp bao gồm các thuộc tính khoá của các loại thực thể tham gia vào loại mối kết hợp đó. Ngoài ra còn có thể có thêm những thuộc tính bổ sung khác.
Ví dụ: Loại mối kết hợp Giảng dạy giữa ba loại thực thể GIAOVIEN, MONHOC và LOP có các thuộc tính là Magv,Mamh,Malop, ngoài ra còn có thuộc tính riêng là Hocky, Nam
Hocky
Nam
3.7 Bản số
(relationship cardinality)
Loại mối kết hợp thể hiện liên kết giữa các thực thể, mỗi liên kết được gọi là một nhánh.
Định nghĩa: bản số của nhánh là số lượng tối thiểu và số lượng tối đa các thực thể thuộc nhánh đó tham gia vào loại mối kết hợp.
Ký hiệu: (bản số tối thiểu, bản số tối đa)
Ví dụ: Loại thực thể NhanVien và DeAn có loại mối kết hợp ThamGia.
(1,1)
(1,n)
(0,1)
(1,1)
3.7 Mô hình ER mở rộng
3.7.1 Chuyên biệt hoá / Tổng quát hóa
3.7.2 Mối kết hợp đệ quy
3.7.3 Loại thực thể yếu
3.7.1 Chuyên biệt hóa (tổng quát hóa)
ConNguoi
HocVi
NgayVL
NgayNH
Khóa
SoCMND
HoTen
...
3.7.2 Mối kết hợp đệ quy
Định nghĩa: là loại mối kết hợp được tạo thành từ cùng một loại thực thể (hay một loại thực thể có loại mối kết hợp với chính nó)
Ví dụ: Mỗi nhân viên có một người quản lý trực tiếp và người quản lý đó cũng là một nhân viên
Định nghĩa:
Là loại thực thể không có thuộc tính khóa
Phải tham gia trong một loại mối kết hợp xác định trong đó có một loại thực thể chủ.
Ký hiệu:
Ví dụ: loại thực thể LANTHI có thuộc tính Lần và tham gia trong loại mối kết hợp Thi với loại thực thể HOCVIEN và MONHOC là loại thực thể yếu.
Thực thể
3.7.3 Loại thực thể yếu
3.7.3 Loại thực thể yếu
HOCVIEN
LANTHI
(1,n)
(1,n)
MONHOC
(1,n)
Bài tập
Xây dựng mô hình ER
Xây dựng mô hình ER cho CSDL quản lý giáo vụ gồm có các chức năng sau:
Lưu trữ thông tin: Học viên , giáo viên, môn học
Xếp lớp cho học viên, chọn lớp trưởng cho lớp
Phân công giảng dạy: giáo viên dạy lớp nào với môn học gì, ở học kỳ, năm học nào.
Lưu trữ kết quả thi: học viên thi môn học nào, lần thi thứ mấy, điểm thi bao nhiêu.
Bài 3: Mô hình dữ liệu quan hệ
(Relational Data Model)
Giới thiệu
Các khái niệm
2.1 Thuộc tính
2.2 Quan hệ
2.3 Bộ giá trị
2.4 Thể hiện của quan hệ
2.5 Tân từ
2.6 Phép chiếu
2.7 Khóa
2.8 Lược đồ quan hệ và lược đồ CSDL
2.9 Hiện thực mô hình ER bằng mô hình dữ liệu quan hệ.
Nội dung
1. Giới thiệu
Mô hình Dữ liệu Quan hệ (Relational Data Model) dựa trên khái niệm quan hệ.
Quan hệ là khái niệm toán học dựa trên nền tảng hình thức về lý thuyết tập hợp.
Mô hình này do TS. E. F. Codd đưa ra năm 1970.
2.1 Thuộc tính (attribute)
Thuộc tính:
Tên gọi: dãy ký tự (gợi nhớ)
Kiểu dữ liệu: Số, Chuỗi, Thời gian, Luận lý, OLE.
Miền giá trị: tập giá trị mà thuộc tính có thể nhận. Ký hiệu miền giá trị của thuộc tính A là Dom(A).
Ví dụ:GIOITINH kiểu dữ liệu là Chuỗi,miền giá trị
Dom(GIOITINH)=(‘Nam’,’Nu’)
Tại một thời điểm, một thuộc tính không có giá trị hoặc chưa xác định được giá trị => giá trị Null
2.2 Quan hệ (relation)
Định nghĩa: quan hệ là một tập hữu hạn các thuộc tính.
Ký hiệu:
Trong đó Q là tên quan hệ, là tập các thuộc tính của quan hệ Q
Ví dụ:
HOCVIEN (Mahv, Hoten, Ngsinh, Gioitinh, Noisinh, Malop)
LOP (Malop, Tenlop, Siso, Trglop, Khoa)
2.3 Bộ (tuple)
Định nghĩa: Bộ là các thông tin của một đối tượng thuộc quan hệ, được gọi là mẫu tin (record), dòng.
Quan hệ là một bảng (table) với các cột là các thuộc tính và mỗi dòng được gọi là bộ.
Một bộ của quan hệ là với
Ví dụ: HOCVIEN(Mahv, Hoten, Ngsinh, Noisinh) có q=(1003,Nguyen Van Lam, 1/1/1987,Dong Nai) nghĩa là học viên có mã số là 1003, họ tên là Nguyen Van Lam, sinh ngày 1/1/1987 ở Dong Nai
2.4 Thể hiện của quan hệ (instance)
Định nghĩa: thể hiện của một quan hệ là tập hợp các bộ giá trị của quan hệ tại một thời điểm nhất định.
Ký hiệu: thể hiện của quan hệ Q là TQ
Ví dụ: THOCVIEN là thể hiện của quan hệ HOCVIEN tại thời điểm hiện tại gồm có các bộ như sau:
2.5 Tân từ
Định nghĩa: tân từ là một quy tắc dùng để mô tả một quan hệ.
Ký hiệu: ||Q||
Ví dụ: THI (Mahv, Mamh, Lanthi, Diem) ||THI||: mỗi học viên được phép thi một môn học nhiều lần, mỗi lần thi lưu trữ học viên nào thi môn gì? lần thi thứ mấy? và điểm là bao nhiêu?
2.6 Phép chiếu (1)
Phép chiếu : Dùng để trích giá trị của một số thuộc tính trong danh sách các thuộc tính của quan hệ.
Ký hiệu: phép chiếu của quan hệ R lên tập thuộc tính A là R[A] hoặc R.A.
Ví dụ:
hv1=
hv2 =
hv3 =
2.6 Phép chiếu (2)
Phép chiếu của quan hệ HOCVIEN lên thuộc tính NoiSinh của quan hệ HOCVIEN:
HOCVIEN[Noisinh] = {‘Nghe An’,’Kien Giang’,’Tay Ninh’}
Phép chiếu lên 1 tập thuộc tính K={Hoten,Noisinh} của quan hệ HOCVIEN
HOCVIEN[Hoten, Noisinh] = {{‘Ha Duy Lap’, ‘Nghe An’},{‘Tran Ngoc Han’, ‘Kien Giang’},{‘Tran Ngoc Linh’,’Tay Ninh’}}
2.6 Phép chiếu (3)
2.6 Phép chiếu (4)
Chiếu của một bộ lên tập thuộc tính: dùng để trích chọn các giá trị cụ thể của bộ giá trị đó theo các thuộc tính được chỉ ra trong danh sách thuộc tính của một quan hệ.
Ký hiệu: chiếu của một bộ giá trị t lên tập thuộc tính A của quan hệ R là tR[A] hoặc t[A]. Nếu A có 1 thuộc tính tR.A
Ví dụ: cho quan hệ HOCVIEN với tập thuộc tính HOCVIEN+={Mahv,Hoten,Gioitinh,Noisinh,Malop}, chứa 3 bộ giá trị hv1,hv2 và hv3
Phép chiếu 1 bộ lên 1 thuộc tính
hv1[Hoten] = {‘Ha Duy Lap’}
hv1=
hv2=
hv3=
2.6 Phép chiếu (5)
2.6 Phép chiếu (6)
Phép chiếu 1 bộ lên 1 tập thuộc tính
tập thuộc tính K={Hoten, Gioitinh}
hv2[K] = {‘Tran Ngoc Han’,’Nu’}
hv1 =
hv2 =
hv3 =
2.7 Khóa
2.7.1 Siêu khóa (super key)
2.7.2 Khóa (key)
2.7.3 Khóa chính (primary key)
2.7.5 Khóa ngoại (foreign key)
2.7.1 Siêu khóa (super key) (1)
Siêu khóa : là một tập con các thuộc tính của Q+ mà giá trị của chúng có thể phân biệt 2 bộ khác nhau trong cùng một thể hiện TQ bất kỳ.
Nghĩa là: t1, t2 TQ, t1[K] t2[K] K là siêu khóa của Q.
Một quan hệ có ít nhất một siêu khóa (Q+) và có thể có nhiều siêu khóa.
2.7.1 Siêu khóa (super key) (2)
Ví dụ: các siêu khóa của quan hệ HOCVIEN là: {Mahv};{Mahv,Hoten};{Hoten};{Noisinh,Hoten}…
2.7.2 Khóa (key) (1)
Khóa : K là khóa của quan hệ R, thỏa mãn 2 điều kiện:
K là một siêu khóa.
K là siêu khóa “nhỏ nhất” (chứa ít thuộc tính nhất và khác rỗng) nghĩa là
¬K1 K, K1 , K1 là siêu khóa.
Thuộc tính tham gia vào một khóa gọi là thuộc tính khóa, ngược lại là thuộc tính không khóa.
Ví dụ: các siêu khóa của quan hệ HOCVIEN là: {Mahv};{Mahv,Hoten};{Hoten};{Hoten,Gioitinh}; {Noisinh,Hoten};{Mahv,Hoten,Gioitinh,Noisinh}…
=> thì khóa của quan hệ HOCVIEN có thể là {Mahv}; {Hoten}
Ví dụ: khóa của quan hệ GIANGDAY là K={Magv,Mamh,Malop}. Như vậy thuộc tính khóa sẽ là: Magv,Mamh,Malop.
2.7.2 Khóa (key) (2)
2.7.3 Khóa chính (primary key)
Khi cài đặt trên một DBMS cụ thể, nếu quan hệ có nhiều hơn một khóa, ta chỉ được chọn một và gọi là khóa chính
Ký hiệu: các thuộc tính nằm trong khóa chính khi liệt kê trong quan hệ phải được gạch dưới.
Ví dụ:
HOCVIEN (Mahv,Hoten,Gioitinh,Noisinh,Malop)
GIANGDAY(Magv,Mamh,Malop,Hocky,Nam)
2.7.4 Khóa ngoại (1)
Cho R(U), S(V). K1U là khóa chính của R,K2V
Ta nói K2 là khóa ngoại của S tham chiếu đến khóa chính K1 của R nếu thỏa các điều kiện sau:
K1 và K2 có cùng số lượng thuộc tính và ngữ nghĩa của các thuộc tính trong K1 và K2 cũng giống nhau.
Giữa R và S tồn tại mối quan hệ 1-n trên K1 và K2,
s S, !r R sao cho r.K1=s.K2
2.7.4 Khóa ngoại (2)
Ví dụ, cho 2 quan hệ
LOP (Malop,Tenlop,Siso,Khoahoc)
HOCVIEN (Mahv,Hoten,Gioitinh,Noisinh,Malop)
Thuộc tính Malop trong quan hệ LOP là khóa chính của quan hệ LOP. Thuộc tính Malop trong quan hệ HOCVIEN là khóa ngoại, tham chiếu đến Malop trong quan hệ LOP
2.7.4 Khóa ngoại (3)
2.8 Lược đồ quan hệ (1)
Lược đồ quan hệ nhằm mục đích mô tả cấu trúc của một quan hệ và các mối liên hệ giữa các thuộc tính trong quan hệ đó.
Cấu trúc của một quan hệ là tập thuộc tính hình thành nên quan hệ đó.
Một lược đồ quan hệ gồm một tập thuộc tính của quan hệ kèm theo một mô tả để xác định ý nghĩa và mối liên hệ giữa các thuộc tính
2.8 Lược đồ quan hệ (2)
Lược đồ quan hệ được đặc trưng bởi:
Một tên phân biệt
Một tập hợp hữu hạn các thuộc tính (A1, …, An)
Ký hiệu của lược đồ quan hệ Q gồm n thuộc tính (A1, A2, ... An) là :
Q(A1, A2, ..., An)
2.8 Lược đồ quan hệ (3)
HOCVIEN(Mahv,Hoten,Gioitinh,Noisinh,Malop
Tân từ: mỗi học viên có một mã học viên để phân biệt với các học viên khác. Cần lưu trữ họ tên, giới tính, nơi sinh và thuộc lớp nào.
2.8 Lược đồ CSDL (1)
Là tập hợp gồm các lược đồ quan hệ và các mối liên hệ giữa chúng trong cùng một hệ thống quản lý.
HOCVIEN (MAHV, HO, TEN, NGSINH, GIOITINH, NOISINH, MALOP)
Tân từ: mỗi học viên phân biệt với nhau bằng mã học viên, lưu trữ họ tên, ngày sinh, giới tính, nơi sinh, thuộc lớp nào.
LOP (MALOP, TENLOP, TRGLOP, SISO, MAGVCN)
Tân từ: mỗi lớp gồm có mã lớp, tên lớp, học viên làm lớp trưởng của lớp, sỉ số lớp và giáo viên chủ nhiệm.
KHOA (MAKHOA, TENKHOA, NGTLAP, TRGKHOA)
Tân từ: mỗi khoa cần lưu trữ mã khoa, tên khoa, ngày thành lập khoa và trưởng khoa (cũng là một giáo viên thuộc khoa).
MONHOC (MAMH, TENMH, TCLT, TCTH, MAKHOA)
Tân từ: mỗi môn học cần lưu trữ tên môn học, số tín chỉ lý thuyết, số tín chỉ thực hành và khoa nào phụ trách.
DIEUKIEN (MAMH, MAMH_TRUOC)
Tân từ: có những môn học học viên phải có kiến thức từ một số môn học trước.
Lược đồ CSDL quản lý sinh viên
GIAOVIEN(MAGV,HOTEN,HOCVI,HOCHAM,GIOITINH,NGSINH,NGVL,
HESO, MUCLUONG, MAKHOA)
Tân từ: mã giáo viên để phân biệt giữa các giáo viên, cần lưu trữ họ tên, học vị, học hàm, giới tính, ngày sinh, ngày vào làm, hệ số, mức lương và thuộc một khoa.
GIANGDAY(MALOP,MAMH,MAGV,HOCKY, NAM,TUNGAY,DENNGAY)
Tân từ: mỗi học kỳ của năm học sẽ phân công giảng dạy: lớp nào học môn gì do giáo viên nào phụ trách.
KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM, KQUA)
Tân từ: lưu trữ kết quả thi của học viên: học viên nào thi môn học gì, lần thi thứ mấy, ngày thi là ngày nào, điểm thi bao nhiêu và kết quả là đạt hay không đạt.
Bài 4: Ngôn ngữ đại số quan hệ
Nội dung
Giới thiệu
Biểu thức đại số quan hệ
Các phép toán
Biểu thức đại số quan hệ
Ví dụ
1. Giới thiệu
Đại số quan hệ (ĐSQH) có nền tảng toán học (cụ thể là lý thuyết tập hợp) để mô hình hóa CSDL quan hệ. Đối tượng xử lý là các quan hệ trong cơ sở dữ liệu quan hệ.
Chức năng:
Cho phép mô tả các phép toán rút trích dữ liệu từ các quan hệ trong cơ sở dữ liệu quan hệ.
Cho phép tối ưu quá trình rút trích bằng các phép toán có sẵn của lý thuyết tập hợp.
2. Biểu thức ĐSQH
Biểu thức ĐSQH là một biểu thức gồm các phép toán ĐSQH.
Biểu thức ĐSQH được xem như một quan hệ (không có tên).
Có thể đặt tên cho quan hệ được tạo từ một biểu thức ĐSQH.
Có thể đổi tên các thuộc tính của quan hệ được tạo từ một biểu thức ĐSQH.
3. Các phép toán
3.1 Giới thiệu
3.2 Phép chọn
3.3 Phép chiếu
3.4 Phép gán
3.5 Các phép toán trên tập hợp
3.6 Phép kết
3.7 Phép chia
3.8 Hàm tính toán và gom nhóm
3.1 Giới thiệu (1)
Có năm phép toán cơ bản:
Chọn ( ) hoặc ( : )
Chiếu ( ) hoặc ( [] )
Tích ( )
Hiệu ( )
Hội ( )
3.1 Giới thiệu (2)
Các phép toán khác không cơ bản nhưng hữu ích:
Giao ( )
Kết ( )
Chia ( )
Phép bù ( )
Đổi tên ( )
Phép gán ( )
Kết quả sau khi thực hiện các phép toán là các quan hệ, do đó có thể kết hợp giữa các phép toán để tạo nên phép toán mới.
3.2 Phép chọn (Selection)
Trích chọn các bộ (dòng) từ quan hệ R. Các bộ được trích chọn phải thỏa mãn điều kiện chọn p.
Ký hiệu:
Định nghĩa: p(t):thỏa điều kiện p
Kết quả trả về là một quan hệ, có cùng danh sách thuộc tính với quan hệ R. Không có kết quả trùng.
Phép chọn có tính giao hoán
Lược đồ CSDL quản lý giáo vụ
HOCVIEN (MAHV, HO, TEN, NGSINH, GIOITINH, NOISINH, MALOP)
LOP (MALOP, TENLOP, TRGLOP, SISO, MAGVCN)
KHOA (MAKHOA, TENKHOA, NGTLAP, TRGKHOA)
MONHOC (MAMH, TENMH, TCLT, TCTH, MAKHOA)
DIEUKIEN (MAMH, MAMH_TRUOC)
GIAOVIEN(MAGV,HOTEN,HOCVI,HOCHAM,GIOITINH,NGSINH,NGVL,
HESO, MUCLUONG, MAKHOA)
GIANGDAY(MALOP,MAMH,MAGV,HOCKY, NAM,TUNGAY,DENNGAY)
KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM, KQUA)
3.2 Ví dụ phép chọn
Tìm những học viên “Nam’ có nơi sinh ở ‘TpHCM’
(Gioitinh=‘Nam’)(Noisinh=‘TpHCM’)(HOCVIEN)
3.3 Phép chiếu (Project)
Sử dụng để trích chọn giá trị một vài thuộc tính của quan hệ
Ký hiệu:
trong đó Ai là tên các thuộc tính được chiếu.
Kết quả trả về một quan hệ có k thuộc tính theo thứ tự như liệt kê. Các dòng trùng nhau chỉ lấy một.
Phép chiếu không có tính giao hoán
3.3 Ví dụ
Tìm mã số, họ tên những học viên “Nam’ có nơi sinh ở ‘TpHCM’
Mahv,Hoten(Gioitinh=‘Nam’)(Noisinh=‘TpHCM’)(HOCVIEN)
3.4 Phép gán (Assignment)
Dùng để diễn tả câu truy vấn phức tạp.
Ký hiệu: A B
Ví dụ:
R(HO,TEN,LUONG) HONV,TENNV,LUONG(NHANVIEN)
Kết quả bên phải của phép gán được gán cho biến quan hệ nằm bên trái.
3.5 Các phép toán tập hợp
3.5.1 Giới thiệu
3.5.2 Phép hội
3.5.3 Phép trừ
3.5.4 Phép giao
3.5.5 Phép tích
3.5.1 Giới thiệu
Các phép toán thực hiện trên 2 quan hệ xuất phát từ lý thuyết tập hợp của toán học: phép hội (RS), phép giao (RS), phép trừ (R-S), phép tích (RS).
Đối với các phép hội, giao, trừ, các quan hệ R và S phải khả hợp:
Số lượng thuộc tính của R và S phải bằng nhau:
R(A1,A2,…An) và S(B1,B2,…Bn)
Miền giá trị của thuộc tính phải tương thích
dom(Ai)=dom(Bi)
Quan hệ kết quả của phép hội, giao, trừ có cùng tên thuộc tính với quan hệ đầu tiên.
3.5.2 Phép hội (Union)
DOT1DOT2
Ký hiệu: RS
Định nghĩa: trong đó R,S là hai quan hệ khả hợp.
Ví dụ: Học viên được khen thưởng đợt 1 hoặc đợt 2
3.5.3 Phép trừ (Set Difference)
Ký hiệu: R-S
Định nghĩa: trong đó R,S là hai quan hệ khả hợp.
Ví dụ: Học viên được khen thưởng đợt 1 nhưng không được khen thường đợt 2
DOT1- DOT2
3.5.4 Phép giao (Set-Intersection)
Ký hiệu: RS
Định nghĩa: trong đó R,S là hai quan hệ khả hợp. Hoặc RS = R – (R – S)
Ví dụ: Học viên được khen thưởng cả hai đợt 1 và 2
DOT1 DOT2
3.5.5 Phép tích (1)
Ký hiệu: RS
Định nghĩa:
Nếu R có n bộ và S có m bộ thì kết quả là n*m bộ
KQ(A1,A2,…Am,B1,B2,…Bn) R(A1,A2,…Am) S(B1,B2,…Bn)
Phép tích thường dùng kết hợp với các phép chọn để kết hợp các bộ có liên quan từ hai quan hệ.
Ví dụ: từ hai quan hệ HOCVIEN và MONHOC, có tất cả những trường hợp nào “học viên đăng ký học môn học”, giả sử không có bất kỳ điều kiện nào
3.5.5 Phép tích (2)
HOCVIENMONHOC
3.6 Phép kết
3.6.1 Phép kết
3.6.2 Phép kết bằng, phép kết tự nhiên
3.6.3 Phép kết ngoài
3.6.1 Phép kết (Theta-Join) (1)
Theta-join (): Tương tự như phép tích kết hợp với phép chọn. Điều kiện chọn gọi là điều kiện kết.
Ký hiệu:
trong đó R,S là các quan hệ, p là điều kiện kết
Các bộ có giá trị NULL tại thuộc tính kết nối không xuất hiện trong kết quả của phép kết.
Phép kết với điều kiện tổng quát gọi là -kết với là một trong những phép so sánh (,,,,,)
3.6.1 Phép kết (2)
3.6.2 Phép kết bằng, kết tự nhiên
Nếu là phép so sánh bằng (=), phép kết gọi là phép kết bằng (equi-join).
Ký hiệu:
Nếu điều kiện của equi-join là các thuộc tính giống nhau thì gọi là phép kết tự nhiên (natural-join). Khi đó kết quả của phép kết loại bỏ bớt 1 cột (bỏ 1 trong 2 cột giống nhau)
Ký hiệu: hoặc
3.6.3 Phép kết ngoài (outer join)
Mở rộng phép kết để tránh mất thông tin
Thực hiện phép kết và sau đó thêm vào kết quả của phép kết các bộ của quan hệ mà không phù hợp với các bộ trong quan hệ kia.
Có 3 loại:
Left outer join R S
Right outer join R S
Full outer join R S
Ví dụ: In ra danh sách tất cả các học viên và điểm số của các môn học mà học viên đó thi (nếu có)
3.6.3 Phép kết ngoài (2)
HOCVIEN KETQUATHI
mahv
Định nghĩa:
R và S là hai quan hệ, R+ và S+ lần lượt là tập thuộc tính của R và S. Điều kiện S+ là tập con không bằng của R+. Q là kết quả phép chia giữa R và S, Q+ = R+ - S+
Có thể diễn đạt bằng phép toán đại số như sau:
3.7 Phép chia (Division)
3.7 Phép chia (2)
KETQUA
MONHOC
KETQUAMONHOC
3.8 Hàm tính toán và gom nhóm (1)
Hàm tính toán gồm các hàm: avg(giatri), min(giatri), max(giatri), sum(giatri), count(giatri).
Phép toán gom nhóm:
E là biểu thức đại số quan hệ
Gi là thuộc tính gom nhóm (rỗng, nếu không gom nhóm)
Fi là hàm tính toán
Ai là tên thuộc tính
3.8 Hàm tính toán và gom nhóm (2)
Điểm thi cao nhất, thấp nhất, trung bình của môn CSDL ?
Điểm thi cao nhất, thấp nhất, trung bình của từng môn ?
Bài tập
Lược đồ CSDL quản lý bán hàng gồm có các quan hệ sau:
KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK)
NHANVIEN (MANV,HOTEN, NGVL, SODT)
SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA)
HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA)
CTHD (SOHD,MASP,SL)
Mô tả các câu truy vấn sau bằng ĐSQH
In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quốc” sản xuất có giá từ 30.000 đến 40.000
In ra danh sách các khách hàng (MAKH, HOTEN) đã mua hàng trong ngày 1/1/2007.
In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quoc” sản xuất hoặc các sản phẩm được bán ra trong ngày 1/1/2007.
Tìm các số hóa đơn mua cùng lúc 2 sản phẩm có mã số “BB01” và “BB02”.
In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quoc” sản xuất không bán được trong năm 2006.
Tìm số hóa đơn đã mua tất cả các sản phẩm do Singapore sản xuất
Câu 1
In ra danh sách các sản phẩm (MASP, TENSP) do “Trung Quốc” sản xuất có giá từ 30.000 đến 40.000.
Câu 2
In ra danh sách các khách hàng (MAKH, HOTEN) đã mua hàng trong ngày 1/1/2007.
Câu 3
In ra danh sách các sản phẩm do “Trung Quoc” sản xuất hoặc các sản phẩm được bán ra trong ngày 1/1/2007.
Hoặc
Câu 4
Tìm các số hóa đơn đã mua cùng lúc các sản phẩm có mã số “BB01” và “BB02”.
Hoặc
Câu 5
In ra danh sách các sản phẩm do “TrungQuoc” sản xuất không bán được trong năm 2006.
Câu 6
Tìm số hóa đơn đã mua tất cả các sản phẩm do Singapore sản xuất
Bài 5: Ngôn ngữ SQL
Nội dung
Giới thiệu
Các ngôn ngữ giao tiếp
Ngôn ngữ định nghĩa dữ liệu
Ngôn ngữ thao tác dữ liệu
Ngôn ngữ truy vấn dữ liệu có cấu trúc
Ngôn ngữ điều khiển dữ liệu
Là ngôn ngữ chuẩn để truy vấn và thao tác trên CSDL quan hệ
Là ngôn ngữ phi thủ tục
Khởi nguồn của SQL là SEQUEL - Structured English Query Language, năm 1974)
Các chuẩn SQL
SQL89
SQL92 (SQL2)
SQL99 (SQL3)
1. Giới thiệu
2. Các ngôn ngữ giao tiếp
Ngôn ngữ định nghĩa dữ liệu (Data Definition Language - DDL): cho phép khai báo cấu trúc bảng, các mối quan hệ và các ràng buộc.
Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML): cho phép thêm, xóa, sửa dữ liệu.
Ngôn ngữ truy vấn dữ liệu (Structured Query Language – SQL): cho phép truy vấn dữ liệu.
Ngôn ngữ điều khiển dữ liệu (Data Control Language – DCL): khai báo bảo mật thông tin, cấp quyền và thu hồi quyền khai thác trên cơ sở dữ liệu.
3.1 Lệnh tạo bảng (CREATE)
3.1.1 Cú pháp
3.1.2 Một số kiểu dữ liệu
3.2 Lệnh sửa cấu trúc bảng (ALTER)
3.2.1 Thêm thuộc tính
3.2.2 Sửa kiểu dữ liệu của thuộc tính
3.2.3 Xoá thuộc tính
3.2.4 Thêm ràng buộc toàn vẹn
3.2.5 Xoá ràng buộc toàn vẹn
3.3 Lệnh xóa bảng (DROP)
3. Ngôn ngữ định nghĩa dữ liệu
3.1.1 Cú pháp
CREATE TABLE
(
[not null],
[not null],
…
[not null],
khai báo khóa chính, khóa ngoại, ràng buộc
)
3.1 Lệnh tạo bảng
3.1 Lệnh tạo bảng (2)
3.1.2 Một số kiểu dữ liệu
3.1 Lệnh tạo bảng (3)
Lược đồ CSDL quản lý bán hàng gồm có các quan hệ sau:
KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK, CMND)
NHANVIEN (MANV,HOTEN, NGVL, SODT)
SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA)
HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA)
CTHD (SOHD,MASP,SL)
3.1 Lệnh tạo bảng (4)
Create table KHACHHANG
(
MAKH char(4) primary key,
HOTEN varchar(40),
DCHI varchar(50),
SODT varchar(20),
NGSINH smalldatetime,
DOANHSO money,
NGDK smalldatetime,
CMND varchar(10)
)
3.1 Lệnh tạo bảng (5)
Create table CTHD
(
SOHD int foreign key
references HOADON(SOHD),
MASP char(4) foreign key
references SANPHAM(MASP),
SL int,
constraint PK_CTHD primary key (SOHD,MASP)
)
3.2.1 Thêm thuộc tính
ALTER TABLE tênbảng ADD têncột kiểudữliệu
Ví dụ: thêm cột Ghi_chu vào bảng khách hàng
ALTER TABLE KHACHHANG ADD GHI_CHU varchar(20)
3.2.2 Sửa kiểu dữ liệu thuộc tính
ALTER TABLE tênbảng ALTER COLUMN têncột kiểudữliệu_mới
Lưu ý:
Không phải sửa bất kỳ kiểu dữ liệu nào cũng được
3.2 Sửa cấu trúc bảng(1)
Ví dụ: Sửa Cột Ghi_chu thành kiểu dữ liệu varchar(50)
ALTER TABLE KHACHHANG ALTER COLUMN GHI_CHU varchar(50)
Nếu sửa kiểu dữ liệu của cột Ghi_chu thành varchar(5), mà trước đó đã nhập giá trị cho cột Ghi_chu có độ dài hơn 5ký tự thì không được phép.
Hoặc sửa từ kiểu chuỗi ký tự sang kiểu số, …
3.2.3 Xóa thuộc tính
ALTER TABLE tên_bảng DROP COLUMN tên_cột
Ví dụ: xóa cột Ghi_chu trong bảng KHACHHANG
ALTER TABLE NHANVIEN DROP COLUMN Ghi_chu
3.2 Sửa cấu trúc bảng(2)
3.2.4 Thêm ràng buộc toàn vẹn
ALTER TABLE
ADD CONSTRAINT
UNIQUE tên_cột
PRIMARY KEY (tên_cột)
FOREIGN KEY (tên_cột) REFERENCES tên_bảng (cột_là_khóa_chính) [ON DELETE CASCADE] [ON UPDATE CASCADE]
CHECK (tên_cột điều_kiện)
3.2 Sửa cấu trúc bảng(3)
Ví dụ
ALTER TABLE NHANVIEN ADD CONSTRAINT PK_NV PRIMARY KEY (MANV)
ALTER TABLE CTHD ADD CONSTRAINT FK_CT_SP FOREIGN KEY (MASP) REFERENCES SANPHAM(MASP)
ALTER TABLE SANPHAM ADD CONSTRAINT CK_GIA CHECK (GIA >=500)
ALTER TABLE KHACHHANG ADD CONSTRAINT UQ_KH UNIQUE (CMND)
3.2 Sửa cấu trúc bảng(4)
3.2.5 Xóa ràng buộc toàn vẹn
ALTER TABLE tên_bảng DROP CONSTRAINT tên_ràng_buộc
Ví dụ:
Alter table CTHD drop constraint FK_CT_SP
Alter table SANPHAM drop constraint ck_gia
Lưu ý: đối với ràng buộc khóa chính, muốn xóa ràng buộc này phải xóa hết các ràng buộc khóa ngoại tham chiếu tới nó
3.2 Sửa cấu trúc bảng(5)
Cú pháp
DROP TABLE tên_bảng
Ví dụ: xóa bảng KHACHHANG.
DROP TABLE KHACHHANG
Lưu ý: khi muốn xóa một bảng phải xóa tất cả những khóa ngoại tham chiếu tới bảng đó trước.
3.3 Lệnh xóa bảng
Gồm các lệnh:
4.1 Lệnh thêm dữ liệu (INSERT)
4.2 Lệnh sửa dữ liệu (UPDATE)
4.3 Lệnh xóa dữ liệu (DELETE)
4. Ngôn ngữ thao tác dữ liệu
Cú pháp
INSERT INTO tên_bảng (cột1,…,cộtn) VALUES (giá_trị_1,…., giá_trị_n)
INSERT INTO tên_bảng VALUES (giá_trị_1, giá_trị_2,…, giá_trị_n)
Ví dụ:
insert into SANPHAM values(`BC01`,`But chi`, `cay`, `Singapore`, 3000)
insert into SANPHAM(masp,tensp,dvt,nuocsx,gia) values (`BC01`,`But chi`,`cay`,`Singapore`,3000)
4.1 Thêm dữ liệu
Cú pháp
UPDATE tên_bảng
SET cột_1 = giá_trị_1, cột_2 = giá_trị_2 ….
[WHERE điều_kiện]
Lưu ý: cẩn thận với các lệnh xóa và sửa, nếu không có điều kiện ở WHERE nghĩa là xóa hoặc sửa tất cả.
Ví dụ: Tăng giá 10% đối với những sản phẩm do “Trung Quoc” sản xuất
UPDATE SANPHAM
SET Gia = Gia*1.1
WHERE Nuocsx=‘Trung Quoc’
4.2 Sửa dữ liệu
4.3 Xóa dữ liệu
Cú pháp
DELETE FROM tên_bảng [WHERE điều_kiện]
Ví dụ:
Xóa toàn bộ nhân viên
DELETE FROM NHANVIEN
Xóa những sản phẩm do Trung Quốc sản xuất có giá thấp hơn 10000
DELETE FROM SANPHAM
WHERE (Gia <10000) and (Nuocsx=‘Trung Quoc’)
5. Ngôn ngữ truy vấn dữ liệu có cấu trúc
5.1 Câu truy vấn tổng quát
5.2 Truy vấn đơn giản
5.3 Phép kết
5.4 Đặt bí danh, sử dụng *, distinct
5.5 Các toán tử
5.6 Câu truy vấn con (subquery)
5.7 Phép chia
5.8 Hàm tính toán, gom nhóm
SELECT [DISTINCT] *|tên_cột | hàm
FROM bảng
[WHERE điều_kiện]
[GROUP BY tên_cột]
[HAVING điều_kiện]
[ORDER BY tên_cột ASC | DESC]
5.1Câu truy vấn tổng quát
5.2 Truy vấn đơn giản(1)
SELECT
Tương đương phép chiếu của ĐSQH
Liệt kê các thuộc tính cần hiển thị trong kết quả
WHERE
Tương ứng với điều kiện chọn trong ĐSQH
Điều kiện liên quan tới thuộc tính, sử dụng các phép nối luận lý AND, OR, NOT, các phép toán so sánh, BETWEEN
FROM
Liệt kê các quan hệ cần thiết, các phép kết
5.2 Truy vấn đơn giản(2)
Tìm masp, tensp do “Trung Quoc” sản xuất có giá từ 20000 đến 30000
Select masp,tensp
From SANPHAM
Where nuocsx=‘Trung Quoc’
and gia between 20000 and 30000
5.3 Phép kết(1)
Inner Join, Left Join, Right Join, Full Join
Ví dụ:
In ra danh sách các khách hàng (MAKH, HOTEN) đã mua hàng trong ngày 1/1/2007.
select KHACHHANG.makh,hoten
from KHACHHANG inner join HOADON on KHACHHANG.makh=HOADON.makh
where nghd=`1/1/2007`
5.3 Phép kết (2)
Ví dụ: In ra danh sách tất cả các hóa đơn và họ tên của khách hàng mua hóa đơn đó (nếu có)
Select sohd, hoten
From HOADON left join KHACHHANG on HOADON.makh=KHACHHANG.makh
Select sohd, hoten
From HOADON ,KHACHHANG
where HOADON.makh*=KHACHHANG.makh
5.4 Đặt bí danh, sử dụng *, distinct
Đặt bí danh – Alias: cho thuộc tính và quan hệ: tên_cũ AS tên_mới
Select manv,hoten as [ho va ten] From NHANVIEN
Liệt kê tất cả các thuộc tính của quan hệ:
Select * from Nhanvien
Select NHANVIEN.* from NHANVIEN
Distinct: trùng chỉ lấy một lần
Select distinct nuocsx from SANPHAM
Sắp xếp kết quả hiển thị: Order by
Select * from SANPHAM order by nuocsx, gia DESC
5.5 Toán tử truy vấn(1)
Toán tử so sánh: =, >,<,>=,<=,<>
Toán tử logic: AND, OR, NOT
Phép toán: +, - ,* , /
BETWEEN …. AND
IS NULL, IS NOT NULL
LIKE (_ %)
IN, NOT IN
EXISTS , NOT EXISTS
SOME, ALL
5.5 Toán tử truy vấn(2)
IS NULL, IS NOT NULL
Select sohd from HOADON where makh is Null
Select * from HOADON where makh is Not Null
Toán tử so sánh, phép toán
Select gia*1.1 as [gia ban] from SANPHAM where nuocsx<>’Viet Nam’
Select * from SANPHAM where (gia between 20000 and 30000) OR (nuocsx=‘Viet Nam’)
Toán tử IN, NOT IN
Select * from SANPHAM where masp NOT IN (‘BB01’,’BB02’,’BB03’)
Toán tử LIKE
So sánh chuỗi tương đối
Cú pháp: s LIKE p, p có thể chứa % hoặc _
% : thay thế một chuỗi ký tự bất kỳ
_ : thay thế một ký tự bất kỳ
Ví dụ: Select masp,tensp from SANPHAM where masp like `B%01‘
5.5 Toán tử so sánh(3)
5.6 Câu truy vấn con (1)
In hoặc Exists
Ví dụ: Tìm các số hóa đơn mua cùng lúc 2 sản phẩm có mã số “BB01” và “BB02”.
select distinct sohd
from CTHD where masp=`BB01` and sohd IN
(select distinct sohd from CTHD where masp=`BB02`)
select distinct A.sohd
from CTHD A where A.masp=`BB01` and
EXISTS (select * from CTHD B
where B.masp=`BB02‘ and A.sohd=B.sohd)
5.6 Câu truy vấn con (2)
Not In hoặc Not Exists
Ví dụ: Tìm các số hóa đơn có mua sản phẩm mã số ‘BB01’ nhưng không mua sản phẩm mã số ‘BB02’.
select distinct sohd
from CTHD where masp=`BB01` and sohd NOT IN
(select distinct sohd from CTHD where masp=`BB02`)
select distinct A.sohd
from CTHD A where A.masp=`BB01` and
NOT EXITST (select * from CTHD B
where B.masp=`BB02‘ and A.sohd=B.sohd)
5.7 Phép chia
Sử dụng NOT EXISTS
Ví dụ: Tìm số hóa đơn đã mua tất cả những sản phẩm do “Trung Quoc” sản xuất.
Select sohd from HOADON where not exists (select * from SANPHAM
where nuocsx=‘Trung Quoc’ and not exists
(select * from CTHD where HOADON.sohd=CTHD.sohd and CTHD.masp=SANPHAM.masp))
5.8.1 Các hàm tính toán cơ bản
COUNT: Đếm số bộ dữ liệu của thuộc tính
MIN: Tính giá trị nhỏ nhất
MAX: Tính giá trị lớn nhất
AVG: Tính giá trị trung bình
SUM: Tính tổng giá trị các bộ dữ liệu
5.8 Các hàm tính toán và gom nhóm (1)
Ví dụ
Tính lương thấp nhất, cao nhất, trung bình và tổng lương của tất cả các nhân viên.
Có tất cả bao nhiêu nhân viên
Bao nhiêu nhân viên có người quản lý
Bao nhiêu phòng ban có nhân viên trực thuộc
Tính lương trung bình của các nhân viên
Tính lương trung bình của các nhân viên theo từng phòng ban
Tính lương thấp nhất, cao nhất, trung bình và tổng lương của tất cả các nhân viên.
SELECT min(luong) as thapnhat,
max(luong) as caonhat,
avg(luong) as trungbinh,
sum(luong) as tongluong
FROM NhanVien
2. Có tất cả bao nhiêu nhân viên
SELECT count(*) FROM NhanVien
3. Bao nhiêu nhân viên có người quản lý
Select count(*) FROM NhanVien WHERE manql is not null
SELECT count(Manql) FROM NhanVien
4. Bao nhiêu phòng ban có nhân viên trực thuộc
SELECT count(distinct phong) FROM NhanVien
5.8 Các hàm tính toán và gom nhóm (2)
5.8.2 Gom nhóm: mệnh đề GROUP BY
Sử dụng hàm gom nhóm trên các bộ trong quan hệ.
Mỗi nhóm bộ bao gồm tập hợp các bộ có cùng giá trị trên các thuộc tính gom nhóm
Hàm gom nhóm áp dụng trên mỗi bộ độc lập nhau.
SQL có mệnh đề GROUP BY để chỉ ra các thuộc tính gom nhóm, các thuộc tính này phải xuất hiện trong mệnh đề SELECT
5. Tính lương trung bình của các nhân viên
SELECT avg(LUONG) as LUONGTB
FROM NhanVien
6. Tính lương trung bình của các nhân viên theo từng phòng ban.
SELECT phong, avg(LUONG) as LUONGTB
FROM NhanVien
GROUP BY phong
5.8 Các hàm tính toán và gom nhóm (3)
5.8.3 Điều kiện sau gom nhóm: mệnh đề HAVING
Lọc kết quả theo điều kiện, sau khi đã gom nhóm
Điều kiện ở HAVING được thực hiện sau khi gom nhóm, các điều kiện có liên quan đến thuộc tính Group By
Ví dụ: tìm phòng có số lượng nhân viên “Nữ” trên 5 người
SELECT phong
FROM NhanVien
WHERE phai = ‘Nữ’
GROUP BY phong
HAVING count(manv) > 5
Bài 6: Ngôn ngữ tân từ
Nội dung
Giới thiệu
Cú pháp
Các định nghĩa
Diễn giải của một công thức
Quy tắc lượng giá công thức
Ngôn ngữ tân từ có biến là n bộ
Ngôn ngữ tân từ có biến là miền giá trị
1. Giới thiệu
Ngôn ngữ tân từ là ngôn ngữ truy vấn hình thức do Codd đề nghị (1972-1973) �
Môn: Cơ Sở Dữ Liệu
Thời lượng: 45LT+30TH
Nội dung
Bài 1: Các khái niệm của một hệ CSDL
Bài 2: Các mô hình CSDL
Bài 3: Mô hình dữ liệu quan hệ (của Codd)
Bài 4: Ngôn ngữ đại số quan hệ
Bài 5: Ngôn ngữ SQL
Bài 6: Ngôn ngữ tân từ
Bài 7: Ràng buộc toàn vẹn trong một CSDL
Bài 8: Tối ưu hóa câu hỏi bằng đại số quan hệ
Bài 1: Các khái niệm của một hệ CSDL
Bài 1: Các khái niệm của một hệ CSDL
Giới thiệu
Hệ thống tập tin (File System)
Định nghĩa một CSDL
Các đối tượng sử dụng CSDL
Hệ quản trị CSDL
Các mức của một CSDL
1. Giới thiệu
Tại sao cần phải có một cơ sở dữ liệu?
2. Hệ thống tập tin
Là tập hợp các tập tin riêng lẻ phục vụ cho một mục đích của đơn vị sử dụng.
Ưu điểm:
Triển khai ứng dụng nhanh
Khả năng đáp ứng nhanh chóng, kịp thời (vì chỉ phục vụ cho mục đích hạn hẹp)
Khuyết điểm:
Trùng lắp dữ liệu lãng phí, dữ liệu không nhất quán
Chi phí cao
Chia sẽ dữ liệu kém
3. Cơ sở dữ liệu (1)
Định nghĩa:
Cơ sở dữ liệu là một hệ thống các thông tin có cấu trúc, được lưu trữ trên các thiết bị lưu trữ nhằm thỏa mãn yêu cầu khai thác thông tin đồng thời của nhiều người sử dụng hay nhiều chương trình ứng dụng với những mục đích khác nhau
3. Cơ sở dữ liệu (2)
Ưu điểm:
Giảm trùng lắp thông tin xuống mức thấp nhất, đảm bảo tính nhất quán và toàn vẹn dữ liệu.
Đảm bảo dữ liệu được truy xuất theo nhiều cách khác nhau.
Khả năng chia sẽ thông tin cho nhiều người, nhiều ứng dụng khác nhau.
3. Cơ sở dữ liệu (3)
Những vấn đề cần giải quyết:
Tính chủ quyền dữ liệu.
Tính bảo mật và quyền khai thác thông tin của người sử dụng
Tranh chấp dữ liệu
Đảm bảo dữ liệu khi có sự cố
4. Các đối tượng sử dụng
Người sử dụng CSDL không chuyên về lĩnh vực tin học và CSDL -> cần công cụ để họ có thể khai thác CSDL khi cần.
Chuyên viên tin học xây dựng các ứng dụng để phục vụ cho các mục đích quản lý
Quản trị CSDL: tổ chức CSDL, bảo mật, cấp quyền, sao lưu, phục hồi dữ liệu, giải quyết các tranh chấp dữ liệu …
5. Hệ quản trị cơ sở dữ liệu (1)
Hệ quản trị cơ sở dữ liệu (DBMS – DataBase Management System) là hệ thống các phần mềm hỗ trợ tích cực cho các nhà phân tích, thiết kế và khai thác CSDL.
Các DBMS thông dụng: Visual FoxPro, Microsoft Access, SQL Server, DB2, Oracle … hầu hết các DBMS hiện nay đều dựa trên mô hình quan hệ.
5. Hệ quản trị cơ sở dữ liệu (2)
Một DBMS phải có:
Ngôn ngữ giao tiếp giữa người sử dụng và CSDL
Từ điển dữ liệu (Data Dictionary)
Có biện pháp bảo mật khi có yêu cầu
Cơ chế giải quyết tranh chấp dữ liệu
Có cơ chế sao lưu (backup), phục hồi (restore)
Đảm bảo tính độc lập giữa dữ liệu và chương trình
5. Hệ quản trị cơ sở dữ liệu (3)
Ngôn ngữ giao tiếp:
Ngôn ngữ mô tả dữ liệu (DDL – Data Definition Language): cho phép khai báo cấu trúc CSDL, các mối liên hệ của dữ liệu, các quy định, ràng buộc dữ liệu.
Ngôn ngữ thao tác dữ liệu (DML – Data Manipulation Language): cho phép thực hiện thao tác thêm, xóa, sửa dữ liệu.
Ngôn ngữ truy vấn có cấu trúc (SQL – Structured Query Language): cho phép người khai thác sử dụng để truy vấn thông tin cần thiết.
Ngôn ngữ quản lý dữ liệu (DCL – Data Control Language) cho phép thay đổi cấu trúc bảng, khai báo bảo mật, cấp quyền cho người sử dụng.
6. Các mức biểu diễn 1 CSDL
Mức trong: (mức vật lý – Physical) là mức lưu trữ CSDL (cần giải quyết vấn đề gì? Dữ liệu gì? Lưu trữ như thế nào? ở đâu? Cần các chỉ mục gì? Truy xuất tuần tự hay ngẫu nhiên. Dành cho người quản trị và người sử dụng chuyên môn.
Mức quan niệm: (Conception hay Logical) cần phải lưu trữ bao nhiêu loại dữ liệu? là dữ liệu gì? mối quan hệ
Mức ngoài: của người sử dụng và các chương trình ứng dụng
6. Các mức biểu diễn 1 CSDL
Cấu trúc
ngoài 1
Cấu trúc
ngoài 2
Cấu trúc
ngoài n
Chương trình ứng dụng n
Mức
quan
niệm
hoặc
mức
logic
Mức vật lý –
Cấu trúc vật lý
CSDL
Môi trường
thực thế giới
thực
NSD1
NSD2
Bài 2: Các mô hình dữ liệu
Nội dung
Giới thiệu
Mô hình dữ liệu mạng
Mô hình thực thể mối kết hợp
1. Giới thiệu
Mô hình dữ liệu là sự trừu tượng hóa của môi trường thực, biểu diễn dữ liệu ở mức quan niệm. Giới thiệu một số mô hình như:
Mô hình dữ liệu mạng
Mô hình dữ liệu phân cấp
Mô hình dữ liệu thực thể mối kết hợp
Mô hình dữ liệu quan hệ
Mô hình dữ liệu hướng đối tượng
2. Mô hình dữ liệu mạng (1)
Mô hình dữ liệu mạng (Network Data Model) còn gọi tắt là mô hình mạng hoặc mô hình lưới là mô hình được biểu diễn bởi một đồ thị có hướng.
Mẫu tin (record)
Loại mẫu tin
Loại liên hệ (set type)
Bản số
2. Mô hình dữ liệu mạng (2)
Mẫu tin: mô tả 1 đối tượng trong thế giới thực.
(‘NV001’,’Nguyen Lam’,’Nam’,’10/10/1970’,’Dong Nai’)
Loại mẫu tin: là 1 tập các mẫu tin có cùng tính chất. Ví dụ: NHANVIEN
Ký hiệu:
Loại liên hệ: mô tả sự liên kết giữa 1 loại mẫu tin chủ và 1 loại mẫu tin thành viên
Ký hiệu:
NHANVIEN
Tham gia
CONGVIEC
2. Mô hình dữ liệu mạng (3)
Bản số: chỉ ra số lượng các mẫu tin tham gia trong mối liên hệ
(1:1) (one-to-one): mỗi mẫu tin của loại mẫu tin chủ kết hợp với đúng 1 mẫu tin của loại mẫu tin thành viên.
(1:n) (one-to-many): mỗi mẫu tin của loại mẫu tin chủ kết hợp với 1 hay nhiều mẫu tin thành viên.
(n:1) (many-to-one): nhiều mẫu tin của loại mẫu tin chủ kết hợp với đúng 1 mẫu tin của loại mẫu tin thành viên.
(Recursive): một loại mẫu tin chủ cũng có thể đồng thời là loại mẫu tin thành viên với chính nó. Loại liên hệ này là Đệ quy
2. Mô hình dữ liệu mạng (4)
PHONG
CONGVIEC
NHANVIEN
LYLICH
gồm
có
quản lý
trực tiếp
cùng làm
n:1
1:1
1:n
1:n
2. Mô hình dữ liệu mạng (5)
Mô hình dữ liệu mạng:
Tương đối đơn giản
Dễ sử dụng
Không thích hợp biểu diễn CSDL có quy mô lớn
Khả năng diễn đạt ngữ nghĩa kém
2. Mô hình dữ liệu mạng (6)
Bài tập:
Xây dựng mô hình dữ liệu mạng cho cơ sở dữ liệu quản lý bán hàng trong một siêu thị (giáo viên mô tả hiện trạng, xác định các yêu cầu)
3. Mô hình thực thể mối kết hợp
3.1 Giới thiệu
3.2 Loại thực thể, thực thể
3.3 Thuộc tính của loại thực thể
3.4 Khoá của loại thực thể
3.5 Loại mối kết hợp, mối kết hợp
3.6 Thuộc tính của loại mối kết hợp
3.7 Bản số
3.8 Mô hình ER mở rộng
3.1 Giới thiệu
Mô hình thực thể mối kết hợp (Entity-Relationship Model viết tắc ER) được CHEN giới thiệu năm 1976.
Mô hình ER được sử dụng nhiều trong thiết kế dữ liệu ở mức quan niệm.
3.2 Loại thực thể
Định nghĩa: loại thực thể (Entity Type) là những loại đối tượng hay sự vật của thế giới thực tồn tại cụ thể cần được quản lý.
Ví dụ : HOCVIEN, LOP, MONHOC, …
Ký hiệu:
HOCVIEN
LOP
3.2 Thực thể (Entity)
Định nghĩa: thực thể là một thể hiện hoặc một đối tượng của một loại thực thể.
Ví dụ: Loại thực thể là HOCVIEN có các thực thể:
(‘HV001’, ‘Nguyen Nam’, ‘1/2/1987’,’Nam’)
(‘HV002’, ‘Trần Nam’, ‘13/2/1987’, ‘Nam’)
3.3 Thuộc tính của loại thực thể
(Entity Attribute)
Định nghĩa: thuộc tính là những tính chất đặc trưng của loại thực thể cần lưu trữ.
Ví dụ: Loại thực thể HOCVIEN có các thuộc tính: Mã học viên, họ tên, giới tính, ngày sinh, nơi sinh
Ký hiệu:
HOCVIEN
Hoten
Gioitinh
Mahv
Ngaysinh
Noisinh
3.3 Các loại thuộc tính (1)
Đơn trị (Simple): mỗi thực thể chỉ có một giá trị ứng với các thuộc tính.
Ví dụ: Mahv,Hoten
Đa hợp (Composite): thuộc tính có thể được tạo thành từ nhiều thành phần.
Ví dụ: DCHI(SONHA,DUONG,PHUONG,QUAN) hay thuộc tính HOTEN(HO,TENLOT,TEN).
Đa trị (Multi-valued): thuộc tính có thể có nhiều giá trị đối với một thực thể.
Ví dụ: BANGCAP ký hiệu {BANGCAP}
3.3 Các loại thuộc tính (2)
Tóm lại, các thuộc tính đa hợp và đa trị có thể lồng nhau tùy ý.
Ví dụ: thuộc tính BANGCAP của HOCVIEN là một thuộc tính đa hợp được ký hiệu bằng
{BANGCAP(TRUONGCAP,NAM,KETQUA,
CHUYENNGANH)}
3.4. Khoá của loại thực thể
(entity type key)
Khóa của loại thực thể là thuộc tính nhận diện thực thể.
Căn cứ vào giá trị của khóa có thể xác định duy nhất một thực thể.
Ví dụ:
Mỗi học viên có một mã số duy nhất => Khoá của loại thực thể HOCVIEN là Mã học viên
Định nghĩa: loại mối kết hợp là sự kết hợp giữa hai hay nhiều loại thực thể
Ví dụ: giữa hai loại thực thể HOCVIEN và LOP có loại mối kết hợp THUOC
Ký hiệu: bằng một hình oval hoặc hình thoi
3.5 Loại mối kết hợp (1)
(relationship type)
3.5 Loại mối kết hợp (2)
Giữa hai loại thực thể có thể tồn tại nhiều hơn một loại mối kết hợp.
Ví dụ
3.5 Số ngôi của loại mối kết hợp (relationship degree)
Số ngôi của loại mối kết hợp là số loại thực thể tham gia vào loại mối kết hợp đó.
Ví dụ 1: Loại mối kết hợp Thuộc kết hợp 2 loại thực thể HOCVIEN và LOP nên có số ngôi là 2.
Ví dụ 2: Loại mối kết hợp Giảng dạy kết hợp 3 loại thực thể GIAOVIEN, MONHOC, LOP nên có số ngôi là 3.
3.5 Số ngôi của loại mối kết hợp
3.6 Thuộc tính của loại mối kết hợp
(relationship type attribute)
Thuộc tính của loại mối kết hợp bao gồm các thuộc tính khoá của các loại thực thể tham gia vào loại mối kết hợp đó. Ngoài ra còn có thể có thêm những thuộc tính bổ sung khác.
Ví dụ: Loại mối kết hợp Giảng dạy giữa ba loại thực thể GIAOVIEN, MONHOC và LOP có các thuộc tính là Magv,Mamh,Malop, ngoài ra còn có thuộc tính riêng là Hocky, Nam
Hocky
Nam
3.7 Bản số
(relationship cardinality)
Loại mối kết hợp thể hiện liên kết giữa các thực thể, mỗi liên kết được gọi là một nhánh.
Định nghĩa: bản số của nhánh là số lượng tối thiểu và số lượng tối đa các thực thể thuộc nhánh đó tham gia vào loại mối kết hợp.
Ký hiệu: (bản số tối thiểu, bản số tối đa)
Ví dụ: Loại thực thể NhanVien và DeAn có loại mối kết hợp ThamGia.
(1,1)
(1,n)
(0,1)
(1,1)
3.7 Mô hình ER mở rộng
3.7.1 Chuyên biệt hoá / Tổng quát hóa
3.7.2 Mối kết hợp đệ quy
3.7.3 Loại thực thể yếu
3.7.1 Chuyên biệt hóa (tổng quát hóa)
ConNguoi
HocVi
NgayVL
NgayNH
Khóa
SoCMND
HoTen
...
3.7.2 Mối kết hợp đệ quy
Định nghĩa: là loại mối kết hợp được tạo thành từ cùng một loại thực thể (hay một loại thực thể có loại mối kết hợp với chính nó)
Ví dụ: Mỗi nhân viên có một người quản lý trực tiếp và người quản lý đó cũng là một nhân viên
Định nghĩa:
Là loại thực thể không có thuộc tính khóa
Phải tham gia trong một loại mối kết hợp xác định trong đó có một loại thực thể chủ.
Ký hiệu:
Ví dụ: loại thực thể LANTHI có thuộc tính Lần và tham gia trong loại mối kết hợp Thi với loại thực thể HOCVIEN và MONHOC là loại thực thể yếu.
Thực thể
3.7.3 Loại thực thể yếu
3.7.3 Loại thực thể yếu
HOCVIEN
LANTHI
(1,n)
(1,n)
MONHOC
(1,n)
Bài tập
Xây dựng mô hình ER
Xây dựng mô hình ER cho CSDL quản lý giáo vụ gồm có các chức năng sau:
Lưu trữ thông tin: Học viên , giáo viên, môn học
Xếp lớp cho học viên, chọn lớp trưởng cho lớp
Phân công giảng dạy: giáo viên dạy lớp nào với môn học gì, ở học kỳ, năm học nào.
Lưu trữ kết quả thi: học viên thi môn học nào, lần thi thứ mấy, điểm thi bao nhiêu.
Bài 3: Mô hình dữ liệu quan hệ
(Relational Data Model)
Giới thiệu
Các khái niệm
2.1 Thuộc tính
2.2 Quan hệ
2.3 Bộ giá trị
2.4 Thể hiện của quan hệ
2.5 Tân từ
2.6 Phép chiếu
2.7 Khóa
2.8 Lược đồ quan hệ và lược đồ CSDL
2.9 Hiện thực mô hình ER bằng mô hình dữ liệu quan hệ.
Nội dung
1. Giới thiệu
Mô hình Dữ liệu Quan hệ (Relational Data Model) dựa trên khái niệm quan hệ.
Quan hệ là khái niệm toán học dựa trên nền tảng hình thức về lý thuyết tập hợp.
Mô hình này do TS. E. F. Codd đưa ra năm 1970.
2.1 Thuộc tính (attribute)
Thuộc tính:
Tên gọi: dãy ký tự (gợi nhớ)
Kiểu dữ liệu: Số, Chuỗi, Thời gian, Luận lý, OLE.
Miền giá trị: tập giá trị mà thuộc tính có thể nhận. Ký hiệu miền giá trị của thuộc tính A là Dom(A).
Ví dụ:GIOITINH kiểu dữ liệu là Chuỗi,miền giá trị
Dom(GIOITINH)=(‘Nam’,’Nu’)
Tại một thời điểm, một thuộc tính không có giá trị hoặc chưa xác định được giá trị => giá trị Null
2.2 Quan hệ (relation)
Định nghĩa: quan hệ là một tập hữu hạn các thuộc tính.
Ký hiệu:
Trong đó Q là tên quan hệ, là tập các thuộc tính của quan hệ Q
Ví dụ:
HOCVIEN (Mahv, Hoten, Ngsinh, Gioitinh, Noisinh, Malop)
LOP (Malop, Tenlop, Siso, Trglop, Khoa)
2.3 Bộ (tuple)
Định nghĩa: Bộ là các thông tin của một đối tượng thuộc quan hệ, được gọi là mẫu tin (record), dòng.
Quan hệ là một bảng (table) với các cột là các thuộc tính và mỗi dòng được gọi là bộ.
Một bộ của quan hệ là với
Ví dụ: HOCVIEN(Mahv, Hoten, Ngsinh, Noisinh) có q=(1003,Nguyen Van Lam, 1/1/1987,Dong Nai) nghĩa là học viên có mã số là 1003, họ tên là Nguyen Van Lam, sinh ngày 1/1/1987 ở Dong Nai
2.4 Thể hiện của quan hệ (instance)
Định nghĩa: thể hiện của một quan hệ là tập hợp các bộ giá trị của quan hệ tại một thời điểm nhất định.
Ký hiệu: thể hiện của quan hệ Q là TQ
Ví dụ: THOCVIEN là thể hiện của quan hệ HOCVIEN tại thời điểm hiện tại gồm có các bộ như sau:
2.5 Tân từ
Định nghĩa: tân từ là một quy tắc dùng để mô tả một quan hệ.
Ký hiệu: ||Q||
Ví dụ: THI (Mahv, Mamh, Lanthi, Diem) ||THI||: mỗi học viên được phép thi một môn học nhiều lần, mỗi lần thi lưu trữ học viên nào thi môn gì? lần thi thứ mấy? và điểm là bao nhiêu?
2.6 Phép chiếu (1)
Phép chiếu : Dùng để trích giá trị của một số thuộc tính trong danh sách các thuộc tính của quan hệ.
Ký hiệu: phép chiếu của quan hệ R lên tập thuộc tính A là R[A] hoặc R.A.
Ví dụ:
hv1=
hv2 =
hv3 =
2.6 Phép chiếu (2)
Phép chiếu của quan hệ HOCVIEN lên thuộc tính NoiSinh của quan hệ HOCVIEN:
HOCVIEN[Noisinh] = {‘Nghe An’,’Kien Giang’,’Tay Ninh’}
Phép chiếu lên 1 tập thuộc tính K={Hoten,Noisinh} của quan hệ HOCVIEN
HOCVIEN[Hoten, Noisinh] = {{‘Ha Duy Lap’, ‘Nghe An’},{‘Tran Ngoc Han’, ‘Kien Giang’},{‘Tran Ngoc Linh’,’Tay Ninh’}}
2.6 Phép chiếu (3)
2.6 Phép chiếu (4)
Chiếu của một bộ lên tập thuộc tính: dùng để trích chọn các giá trị cụ thể của bộ giá trị đó theo các thuộc tính được chỉ ra trong danh sách thuộc tính của một quan hệ.
Ký hiệu: chiếu của một bộ giá trị t lên tập thuộc tính A của quan hệ R là tR[A] hoặc t[A]. Nếu A có 1 thuộc tính tR.A
Ví dụ: cho quan hệ HOCVIEN với tập thuộc tính HOCVIEN+={Mahv,Hoten,Gioitinh,Noisinh,Malop}, chứa 3 bộ giá trị hv1,hv2 và hv3
Phép chiếu 1 bộ lên 1 thuộc tính
hv1[Hoten] = {‘Ha Duy Lap’}
hv1=
hv2=
hv3=
2.6 Phép chiếu (5)
2.6 Phép chiếu (6)
Phép chiếu 1 bộ lên 1 tập thuộc tính
tập thuộc tính K={Hoten, Gioitinh}
hv2[K] = {‘Tran Ngoc Han’,’Nu’}
hv1 =
hv2 =
hv3 =
2.7 Khóa
2.7.1 Siêu khóa (super key)
2.7.2 Khóa (key)
2.7.3 Khóa chính (primary key)
2.7.5 Khóa ngoại (foreign key)
2.7.1 Siêu khóa (super key) (1)
Siêu khóa : là một tập con các thuộc tính của Q+ mà giá trị của chúng có thể phân biệt 2 bộ khác nhau trong cùng một thể hiện TQ bất kỳ.
Nghĩa là: t1, t2 TQ, t1[K] t2[K] K là siêu khóa của Q.
Một quan hệ có ít nhất một siêu khóa (Q+) và có thể có nhiều siêu khóa.
2.7.1 Siêu khóa (super key) (2)
Ví dụ: các siêu khóa của quan hệ HOCVIEN là: {Mahv};{Mahv,Hoten};{Hoten};{Noisinh,Hoten}…
2.7.2 Khóa (key) (1)
Khóa : K là khóa của quan hệ R, thỏa mãn 2 điều kiện:
K là một siêu khóa.
K là siêu khóa “nhỏ nhất” (chứa ít thuộc tính nhất và khác rỗng) nghĩa là
¬K1 K, K1 , K1 là siêu khóa.
Thuộc tính tham gia vào một khóa gọi là thuộc tính khóa, ngược lại là thuộc tính không khóa.
Ví dụ: các siêu khóa của quan hệ HOCVIEN là: {Mahv};{Mahv,Hoten};{Hoten};{Hoten,Gioitinh}; {Noisinh,Hoten};{Mahv,Hoten,Gioitinh,Noisinh}…
=> thì khóa của quan hệ HOCVIEN có thể là {Mahv}; {Hoten}
Ví dụ: khóa của quan hệ GIANGDAY là K={Magv,Mamh,Malop}. Như vậy thuộc tính khóa sẽ là: Magv,Mamh,Malop.
2.7.2 Khóa (key) (2)
2.7.3 Khóa chính (primary key)
Khi cài đặt trên một DBMS cụ thể, nếu quan hệ có nhiều hơn một khóa, ta chỉ được chọn một và gọi là khóa chính
Ký hiệu: các thuộc tính nằm trong khóa chính khi liệt kê trong quan hệ phải được gạch dưới.
Ví dụ:
HOCVIEN (Mahv,Hoten,Gioitinh,Noisinh,Malop)
GIANGDAY(Magv,Mamh,Malop,Hocky,Nam)
2.7.4 Khóa ngoại (1)
Cho R(U), S(V). K1U là khóa chính của R,K2V
Ta nói K2 là khóa ngoại của S tham chiếu đến khóa chính K1 của R nếu thỏa các điều kiện sau:
K1 và K2 có cùng số lượng thuộc tính và ngữ nghĩa của các thuộc tính trong K1 và K2 cũng giống nhau.
Giữa R và S tồn tại mối quan hệ 1-n trên K1 và K2,
s S, !r R sao cho r.K1=s.K2
2.7.4 Khóa ngoại (2)
Ví dụ, cho 2 quan hệ
LOP (Malop,Tenlop,Siso,Khoahoc)
HOCVIEN (Mahv,Hoten,Gioitinh,Noisinh,Malop)
Thuộc tính Malop trong quan hệ LOP là khóa chính của quan hệ LOP. Thuộc tính Malop trong quan hệ HOCVIEN là khóa ngoại, tham chiếu đến Malop trong quan hệ LOP
2.7.4 Khóa ngoại (3)
2.8 Lược đồ quan hệ (1)
Lược đồ quan hệ nhằm mục đích mô tả cấu trúc của một quan hệ và các mối liên hệ giữa các thuộc tính trong quan hệ đó.
Cấu trúc của một quan hệ là tập thuộc tính hình thành nên quan hệ đó.
Một lược đồ quan hệ gồm một tập thuộc tính của quan hệ kèm theo một mô tả để xác định ý nghĩa và mối liên hệ giữa các thuộc tính
2.8 Lược đồ quan hệ (2)
Lược đồ quan hệ được đặc trưng bởi:
Một tên phân biệt
Một tập hợp hữu hạn các thuộc tính (A1, …, An)
Ký hiệu của lược đồ quan hệ Q gồm n thuộc tính (A1, A2, ... An) là :
Q(A1, A2, ..., An)
2.8 Lược đồ quan hệ (3)
HOCVIEN(Mahv,Hoten,Gioitinh,Noisinh,Malop
Tân từ: mỗi học viên có một mã học viên để phân biệt với các học viên khác. Cần lưu trữ họ tên, giới tính, nơi sinh và thuộc lớp nào.
2.8 Lược đồ CSDL (1)
Là tập hợp gồm các lược đồ quan hệ và các mối liên hệ giữa chúng trong cùng một hệ thống quản lý.
HOCVIEN (MAHV, HO, TEN, NGSINH, GIOITINH, NOISINH, MALOP)
Tân từ: mỗi học viên phân biệt với nhau bằng mã học viên, lưu trữ họ tên, ngày sinh, giới tính, nơi sinh, thuộc lớp nào.
LOP (MALOP, TENLOP, TRGLOP, SISO, MAGVCN)
Tân từ: mỗi lớp gồm có mã lớp, tên lớp, học viên làm lớp trưởng của lớp, sỉ số lớp và giáo viên chủ nhiệm.
KHOA (MAKHOA, TENKHOA, NGTLAP, TRGKHOA)
Tân từ: mỗi khoa cần lưu trữ mã khoa, tên khoa, ngày thành lập khoa và trưởng khoa (cũng là một giáo viên thuộc khoa).
MONHOC (MAMH, TENMH, TCLT, TCTH, MAKHOA)
Tân từ: mỗi môn học cần lưu trữ tên môn học, số tín chỉ lý thuyết, số tín chỉ thực hành và khoa nào phụ trách.
DIEUKIEN (MAMH, MAMH_TRUOC)
Tân từ: có những môn học học viên phải có kiến thức từ một số môn học trước.
Lược đồ CSDL quản lý sinh viên
GIAOVIEN(MAGV,HOTEN,HOCVI,HOCHAM,GIOITINH,NGSINH,NGVL,
HESO, MUCLUONG, MAKHOA)
Tân từ: mã giáo viên để phân biệt giữa các giáo viên, cần lưu trữ họ tên, học vị, học hàm, giới tính, ngày sinh, ngày vào làm, hệ số, mức lương và thuộc một khoa.
GIANGDAY(MALOP,MAMH,MAGV,HOCKY, NAM,TUNGAY,DENNGAY)
Tân từ: mỗi học kỳ của năm học sẽ phân công giảng dạy: lớp nào học môn gì do giáo viên nào phụ trách.
KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM, KQUA)
Tân từ: lưu trữ kết quả thi của học viên: học viên nào thi môn học gì, lần thi thứ mấy, ngày thi là ngày nào, điểm thi bao nhiêu và kết quả là đạt hay không đạt.
Bài 4: Ngôn ngữ đại số quan hệ
Nội dung
Giới thiệu
Biểu thức đại số quan hệ
Các phép toán
Biểu thức đại số quan hệ
Ví dụ
1. Giới thiệu
Đại số quan hệ (ĐSQH) có nền tảng toán học (cụ thể là lý thuyết tập hợp) để mô hình hóa CSDL quan hệ. Đối tượng xử lý là các quan hệ trong cơ sở dữ liệu quan hệ.
Chức năng:
Cho phép mô tả các phép toán rút trích dữ liệu từ các quan hệ trong cơ sở dữ liệu quan hệ.
Cho phép tối ưu quá trình rút trích bằng các phép toán có sẵn của lý thuyết tập hợp.
2. Biểu thức ĐSQH
Biểu thức ĐSQH là một biểu thức gồm các phép toán ĐSQH.
Biểu thức ĐSQH được xem như một quan hệ (không có tên).
Có thể đặt tên cho quan hệ được tạo từ một biểu thức ĐSQH.
Có thể đổi tên các thuộc tính của quan hệ được tạo từ một biểu thức ĐSQH.
3. Các phép toán
3.1 Giới thiệu
3.2 Phép chọn
3.3 Phép chiếu
3.4 Phép gán
3.5 Các phép toán trên tập hợp
3.6 Phép kết
3.7 Phép chia
3.8 Hàm tính toán và gom nhóm
3.1 Giới thiệu (1)
Có năm phép toán cơ bản:
Chọn ( ) hoặc ( : )
Chiếu ( ) hoặc ( [] )
Tích ( )
Hiệu ( )
Hội ( )
3.1 Giới thiệu (2)
Các phép toán khác không cơ bản nhưng hữu ích:
Giao ( )
Kết ( )
Chia ( )
Phép bù ( )
Đổi tên ( )
Phép gán ( )
Kết quả sau khi thực hiện các phép toán là các quan hệ, do đó có thể kết hợp giữa các phép toán để tạo nên phép toán mới.
3.2 Phép chọn (Selection)
Trích chọn các bộ (dòng) từ quan hệ R. Các bộ được trích chọn phải thỏa mãn điều kiện chọn p.
Ký hiệu:
Định nghĩa: p(t):thỏa điều kiện p
Kết quả trả về là một quan hệ, có cùng danh sách thuộc tính với quan hệ R. Không có kết quả trùng.
Phép chọn có tính giao hoán
Lược đồ CSDL quản lý giáo vụ
HOCVIEN (MAHV, HO, TEN, NGSINH, GIOITINH, NOISINH, MALOP)
LOP (MALOP, TENLOP, TRGLOP, SISO, MAGVCN)
KHOA (MAKHOA, TENKHOA, NGTLAP, TRGKHOA)
MONHOC (MAMH, TENMH, TCLT, TCTH, MAKHOA)
DIEUKIEN (MAMH, MAMH_TRUOC)
GIAOVIEN(MAGV,HOTEN,HOCVI,HOCHAM,GIOITINH,NGSINH,NGVL,
HESO, MUCLUONG, MAKHOA)
GIANGDAY(MALOP,MAMH,MAGV,HOCKY, NAM,TUNGAY,DENNGAY)
KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM, KQUA)
3.2 Ví dụ phép chọn
Tìm những học viên “Nam’ có nơi sinh ở ‘TpHCM’
(Gioitinh=‘Nam’)(Noisinh=‘TpHCM’)(HOCVIEN)
3.3 Phép chiếu (Project)
Sử dụng để trích chọn giá trị một vài thuộc tính của quan hệ
Ký hiệu:
trong đó Ai là tên các thuộc tính được chiếu.
Kết quả trả về một quan hệ có k thuộc tính theo thứ tự như liệt kê. Các dòng trùng nhau chỉ lấy một.
Phép chiếu không có tính giao hoán
3.3 Ví dụ
Tìm mã số, họ tên những học viên “Nam’ có nơi sinh ở ‘TpHCM’
Mahv,Hoten(Gioitinh=‘Nam’)(Noisinh=‘TpHCM’)(HOCVIEN)
3.4 Phép gán (Assignment)
Dùng để diễn tả câu truy vấn phức tạp.
Ký hiệu: A B
Ví dụ:
R(HO,TEN,LUONG) HONV,TENNV,LUONG(NHANVIEN)
Kết quả bên phải của phép gán được gán cho biến quan hệ nằm bên trái.
3.5 Các phép toán tập hợp
3.5.1 Giới thiệu
3.5.2 Phép hội
3.5.3 Phép trừ
3.5.4 Phép giao
3.5.5 Phép tích
3.5.1 Giới thiệu
Các phép toán thực hiện trên 2 quan hệ xuất phát từ lý thuyết tập hợp của toán học: phép hội (RS), phép giao (RS), phép trừ (R-S), phép tích (RS).
Đối với các phép hội, giao, trừ, các quan hệ R và S phải khả hợp:
Số lượng thuộc tính của R và S phải bằng nhau:
R(A1,A2,…An) và S(B1,B2,…Bn)
Miền giá trị của thuộc tính phải tương thích
dom(Ai)=dom(Bi)
Quan hệ kết quả của phép hội, giao, trừ có cùng tên thuộc tính với quan hệ đầu tiên.
3.5.2 Phép hội (Union)
DOT1DOT2
Ký hiệu: RS
Định nghĩa: trong đó R,S là hai quan hệ khả hợp.
Ví dụ: Học viên được khen thưởng đợt 1 hoặc đợt 2
3.5.3 Phép trừ (Set Difference)
Ký hiệu: R-S
Định nghĩa: trong đó R,S là hai quan hệ khả hợp.
Ví dụ: Học viên được khen thưởng đợt 1 nhưng không được khen thường đợt 2
DOT1- DOT2
3.5.4 Phép giao (Set-Intersection)
Ký hiệu: RS
Định nghĩa: trong đó R,S là hai quan hệ khả hợp. Hoặc RS = R – (R – S)
Ví dụ: Học viên được khen thưởng cả hai đợt 1 và 2
DOT1 DOT2
3.5.5 Phép tích (1)
Ký hiệu: RS
Định nghĩa:
Nếu R có n bộ và S có m bộ thì kết quả là n*m bộ
KQ(A1,A2,…Am,B1,B2,…Bn) R(A1,A2,…Am) S(B1,B2,…Bn)
Phép tích thường dùng kết hợp với các phép chọn để kết hợp các bộ có liên quan từ hai quan hệ.
Ví dụ: từ hai quan hệ HOCVIEN và MONHOC, có tất cả những trường hợp nào “học viên đăng ký học môn học”, giả sử không có bất kỳ điều kiện nào
3.5.5 Phép tích (2)
HOCVIENMONHOC
3.6 Phép kết
3.6.1 Phép kết
3.6.2 Phép kết bằng, phép kết tự nhiên
3.6.3 Phép kết ngoài
3.6.1 Phép kết (Theta-Join) (1)
Theta-join (): Tương tự như phép tích kết hợp với phép chọn. Điều kiện chọn gọi là điều kiện kết.
Ký hiệu:
trong đó R,S là các quan hệ, p là điều kiện kết
Các bộ có giá trị NULL tại thuộc tính kết nối không xuất hiện trong kết quả của phép kết.
Phép kết với điều kiện tổng quát gọi là -kết với là một trong những phép so sánh (,,,,,)
3.6.1 Phép kết (2)
3.6.2 Phép kết bằng, kết tự nhiên
Nếu là phép so sánh bằng (=), phép kết gọi là phép kết bằng (equi-join).
Ký hiệu:
Nếu điều kiện của equi-join là các thuộc tính giống nhau thì gọi là phép kết tự nhiên (natural-join). Khi đó kết quả của phép kết loại bỏ bớt 1 cột (bỏ 1 trong 2 cột giống nhau)
Ký hiệu: hoặc
3.6.3 Phép kết ngoài (outer join)
Mở rộng phép kết để tránh mất thông tin
Thực hiện phép kết và sau đó thêm vào kết quả của phép kết các bộ của quan hệ mà không phù hợp với các bộ trong quan hệ kia.
Có 3 loại:
Left outer join R S
Right outer join R S
Full outer join R S
Ví dụ: In ra danh sách tất cả các học viên và điểm số của các môn học mà học viên đó thi (nếu có)
3.6.3 Phép kết ngoài (2)
HOCVIEN KETQUATHI
mahv
Định nghĩa:
R và S là hai quan hệ, R+ và S+ lần lượt là tập thuộc tính của R và S. Điều kiện S+ là tập con không bằng của R+. Q là kết quả phép chia giữa R và S, Q+ = R+ - S+
Có thể diễn đạt bằng phép toán đại số như sau:
3.7 Phép chia (Division)
3.7 Phép chia (2)
KETQUA
MONHOC
KETQUAMONHOC
3.8 Hàm tính toán và gom nhóm (1)
Hàm tính toán gồm các hàm: avg(giatri), min(giatri), max(giatri), sum(giatri), count(giatri).
Phép toán gom nhóm:
E là biểu thức đại số quan hệ
Gi là thuộc tính gom nhóm (rỗng, nếu không gom nhóm)
Fi là hàm tính toán
Ai là tên thuộc tính
3.8 Hàm tính toán và gom nhóm (2)
Điểm thi cao nhất, thấp nhất, trung bình của môn CSDL ?
Điểm thi cao nhất, thấp nhất, trung bình của từng môn ?
Bài tập
Lược đồ CSDL quản lý bán hàng gồm có các quan hệ sau:
KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK)
NHANVIEN (MANV,HOTEN, NGVL, SODT)
SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA)
HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA)
CTHD (SOHD,MASP,SL)
Mô tả các câu truy vấn sau bằng ĐSQH
In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quốc” sản xuất có giá từ 30.000 đến 40.000
In ra danh sách các khách hàng (MAKH, HOTEN) đã mua hàng trong ngày 1/1/2007.
In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quoc” sản xuất hoặc các sản phẩm được bán ra trong ngày 1/1/2007.
Tìm các số hóa đơn mua cùng lúc 2 sản phẩm có mã số “BB01” và “BB02”.
In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quoc” sản xuất không bán được trong năm 2006.
Tìm số hóa đơn đã mua tất cả các sản phẩm do Singapore sản xuất
Câu 1
In ra danh sách các sản phẩm (MASP, TENSP) do “Trung Quốc” sản xuất có giá từ 30.000 đến 40.000.
Câu 2
In ra danh sách các khách hàng (MAKH, HOTEN) đã mua hàng trong ngày 1/1/2007.
Câu 3
In ra danh sách các sản phẩm do “Trung Quoc” sản xuất hoặc các sản phẩm được bán ra trong ngày 1/1/2007.
Hoặc
Câu 4
Tìm các số hóa đơn đã mua cùng lúc các sản phẩm có mã số “BB01” và “BB02”.
Hoặc
Câu 5
In ra danh sách các sản phẩm do “TrungQuoc” sản xuất không bán được trong năm 2006.
Câu 6
Tìm số hóa đơn đã mua tất cả các sản phẩm do Singapore sản xuất
Bài 5: Ngôn ngữ SQL
Nội dung
Giới thiệu
Các ngôn ngữ giao tiếp
Ngôn ngữ định nghĩa dữ liệu
Ngôn ngữ thao tác dữ liệu
Ngôn ngữ truy vấn dữ liệu có cấu trúc
Ngôn ngữ điều khiển dữ liệu
Là ngôn ngữ chuẩn để truy vấn và thao tác trên CSDL quan hệ
Là ngôn ngữ phi thủ tục
Khởi nguồn của SQL là SEQUEL - Structured English Query Language, năm 1974)
Các chuẩn SQL
SQL89
SQL92 (SQL2)
SQL99 (SQL3)
1. Giới thiệu
2. Các ngôn ngữ giao tiếp
Ngôn ngữ định nghĩa dữ liệu (Data Definition Language - DDL): cho phép khai báo cấu trúc bảng, các mối quan hệ và các ràng buộc.
Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML): cho phép thêm, xóa, sửa dữ liệu.
Ngôn ngữ truy vấn dữ liệu (Structured Query Language – SQL): cho phép truy vấn dữ liệu.
Ngôn ngữ điều khiển dữ liệu (Data Control Language – DCL): khai báo bảo mật thông tin, cấp quyền và thu hồi quyền khai thác trên cơ sở dữ liệu.
3.1 Lệnh tạo bảng (CREATE)
3.1.1 Cú pháp
3.1.2 Một số kiểu dữ liệu
3.2 Lệnh sửa cấu trúc bảng (ALTER)
3.2.1 Thêm thuộc tính
3.2.2 Sửa kiểu dữ liệu của thuộc tính
3.2.3 Xoá thuộc tính
3.2.4 Thêm ràng buộc toàn vẹn
3.2.5 Xoá ràng buộc toàn vẹn
3.3 Lệnh xóa bảng (DROP)
3. Ngôn ngữ định nghĩa dữ liệu
3.1.1 Cú pháp
CREATE TABLE
(
…
khai báo khóa chính, khóa ngoại, ràng buộc
)
3.1 Lệnh tạo bảng
3.1 Lệnh tạo bảng (2)
3.1.2 Một số kiểu dữ liệu
3.1 Lệnh tạo bảng (3)
Lược đồ CSDL quản lý bán hàng gồm có các quan hệ sau:
KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK, CMND)
NHANVIEN (MANV,HOTEN, NGVL, SODT)
SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA)
HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA)
CTHD (SOHD,MASP,SL)
3.1 Lệnh tạo bảng (4)
Create table KHACHHANG
(
MAKH char(4) primary key,
HOTEN varchar(40),
DCHI varchar(50),
SODT varchar(20),
NGSINH smalldatetime,
DOANHSO money,
NGDK smalldatetime,
CMND varchar(10)
)
3.1 Lệnh tạo bảng (5)
Create table CTHD
(
SOHD int foreign key
references HOADON(SOHD),
MASP char(4) foreign key
references SANPHAM(MASP),
SL int,
constraint PK_CTHD primary key (SOHD,MASP)
)
3.2.1 Thêm thuộc tính
ALTER TABLE tênbảng ADD têncột kiểudữliệu
Ví dụ: thêm cột Ghi_chu vào bảng khách hàng
ALTER TABLE KHACHHANG ADD GHI_CHU varchar(20)
3.2.2 Sửa kiểu dữ liệu thuộc tính
ALTER TABLE tênbảng ALTER COLUMN têncột kiểudữliệu_mới
Lưu ý:
Không phải sửa bất kỳ kiểu dữ liệu nào cũng được
3.2 Sửa cấu trúc bảng(1)
Ví dụ: Sửa Cột Ghi_chu thành kiểu dữ liệu varchar(50)
ALTER TABLE KHACHHANG ALTER COLUMN GHI_CHU varchar(50)
Nếu sửa kiểu dữ liệu của cột Ghi_chu thành varchar(5), mà trước đó đã nhập giá trị cho cột Ghi_chu có độ dài hơn 5ký tự thì không được phép.
Hoặc sửa từ kiểu chuỗi ký tự sang kiểu số, …
3.2.3 Xóa thuộc tính
ALTER TABLE tên_bảng DROP COLUMN tên_cột
Ví dụ: xóa cột Ghi_chu trong bảng KHACHHANG
ALTER TABLE NHANVIEN DROP COLUMN Ghi_chu
3.2 Sửa cấu trúc bảng(2)
3.2.4 Thêm ràng buộc toàn vẹn
ALTER TABLE
ADD CONSTRAINT
UNIQUE tên_cột
PRIMARY KEY (tên_cột)
FOREIGN KEY (tên_cột) REFERENCES tên_bảng (cột_là_khóa_chính) [ON DELETE CASCADE] [ON UPDATE CASCADE]
CHECK (tên_cột điều_kiện)
3.2 Sửa cấu trúc bảng(3)
Ví dụ
ALTER TABLE NHANVIEN ADD CONSTRAINT PK_NV PRIMARY KEY (MANV)
ALTER TABLE CTHD ADD CONSTRAINT FK_CT_SP FOREIGN KEY (MASP) REFERENCES SANPHAM(MASP)
ALTER TABLE SANPHAM ADD CONSTRAINT CK_GIA CHECK (GIA >=500)
ALTER TABLE KHACHHANG ADD CONSTRAINT UQ_KH UNIQUE (CMND)
3.2 Sửa cấu trúc bảng(4)
3.2.5 Xóa ràng buộc toàn vẹn
ALTER TABLE tên_bảng DROP CONSTRAINT tên_ràng_buộc
Ví dụ:
Alter table CTHD drop constraint FK_CT_SP
Alter table SANPHAM drop constraint ck_gia
Lưu ý: đối với ràng buộc khóa chính, muốn xóa ràng buộc này phải xóa hết các ràng buộc khóa ngoại tham chiếu tới nó
3.2 Sửa cấu trúc bảng(5)
Cú pháp
DROP TABLE tên_bảng
Ví dụ: xóa bảng KHACHHANG.
DROP TABLE KHACHHANG
Lưu ý: khi muốn xóa một bảng phải xóa tất cả những khóa ngoại tham chiếu tới bảng đó trước.
3.3 Lệnh xóa bảng
Gồm các lệnh:
4.1 Lệnh thêm dữ liệu (INSERT)
4.2 Lệnh sửa dữ liệu (UPDATE)
4.3 Lệnh xóa dữ liệu (DELETE)
4. Ngôn ngữ thao tác dữ liệu
Cú pháp
INSERT INTO tên_bảng (cột1,…,cộtn) VALUES (giá_trị_1,…., giá_trị_n)
INSERT INTO tên_bảng VALUES (giá_trị_1, giá_trị_2,…, giá_trị_n)
Ví dụ:
insert into SANPHAM values(`BC01`,`But chi`, `cay`, `Singapore`, 3000)
insert into SANPHAM(masp,tensp,dvt,nuocsx,gia) values (`BC01`,`But chi`,`cay`,`Singapore`,3000)
4.1 Thêm dữ liệu
Cú pháp
UPDATE tên_bảng
SET cột_1 = giá_trị_1, cột_2 = giá_trị_2 ….
[WHERE điều_kiện]
Lưu ý: cẩn thận với các lệnh xóa và sửa, nếu không có điều kiện ở WHERE nghĩa là xóa hoặc sửa tất cả.
Ví dụ: Tăng giá 10% đối với những sản phẩm do “Trung Quoc” sản xuất
UPDATE SANPHAM
SET Gia = Gia*1.1
WHERE Nuocsx=‘Trung Quoc’
4.2 Sửa dữ liệu
4.3 Xóa dữ liệu
Cú pháp
DELETE FROM tên_bảng [WHERE điều_kiện]
Ví dụ:
Xóa toàn bộ nhân viên
DELETE FROM NHANVIEN
Xóa những sản phẩm do Trung Quốc sản xuất có giá thấp hơn 10000
DELETE FROM SANPHAM
WHERE (Gia <10000) and (Nuocsx=‘Trung Quoc’)
5. Ngôn ngữ truy vấn dữ liệu có cấu trúc
5.1 Câu truy vấn tổng quát
5.2 Truy vấn đơn giản
5.3 Phép kết
5.4 Đặt bí danh, sử dụng *, distinct
5.5 Các toán tử
5.6 Câu truy vấn con (subquery)
5.7 Phép chia
5.8 Hàm tính toán, gom nhóm
SELECT [DISTINCT] *|tên_cột | hàm
FROM bảng
[WHERE điều_kiện]
[GROUP BY tên_cột]
[HAVING điều_kiện]
[ORDER BY tên_cột ASC | DESC]
5.1Câu truy vấn tổng quát
5.2 Truy vấn đơn giản(1)
SELECT
Tương đương phép chiếu của ĐSQH
Liệt kê các thuộc tính cần hiển thị trong kết quả
WHERE
Tương ứng với điều kiện chọn trong ĐSQH
Điều kiện liên quan tới thuộc tính, sử dụng các phép nối luận lý AND, OR, NOT, các phép toán so sánh, BETWEEN
FROM
Liệt kê các quan hệ cần thiết, các phép kết
5.2 Truy vấn đơn giản(2)
Tìm masp, tensp do “Trung Quoc” sản xuất có giá từ 20000 đến 30000
Select masp,tensp
From SANPHAM
Where nuocsx=‘Trung Quoc’
and gia between 20000 and 30000
5.3 Phép kết(1)
Inner Join, Left Join, Right Join, Full Join
Ví dụ:
In ra danh sách các khách hàng (MAKH, HOTEN) đã mua hàng trong ngày 1/1/2007.
select KHACHHANG.makh,hoten
from KHACHHANG inner join HOADON on KHACHHANG.makh=HOADON.makh
where nghd=`1/1/2007`
5.3 Phép kết (2)
Ví dụ: In ra danh sách tất cả các hóa đơn và họ tên của khách hàng mua hóa đơn đó (nếu có)
Select sohd, hoten
From HOADON left join KHACHHANG on HOADON.makh=KHACHHANG.makh
Select sohd, hoten
From HOADON ,KHACHHANG
where HOADON.makh*=KHACHHANG.makh
5.4 Đặt bí danh, sử dụng *, distinct
Đặt bí danh – Alias: cho thuộc tính và quan hệ: tên_cũ AS tên_mới
Select manv,hoten as [ho va ten] From NHANVIEN
Liệt kê tất cả các thuộc tính của quan hệ:
Select * from Nhanvien
Select NHANVIEN.* from NHANVIEN
Distinct: trùng chỉ lấy một lần
Select distinct nuocsx from SANPHAM
Sắp xếp kết quả hiển thị: Order by
Select * from SANPHAM order by nuocsx, gia DESC
5.5 Toán tử truy vấn(1)
Toán tử so sánh: =, >,<,>=,<=,<>
Toán tử logic: AND, OR, NOT
Phép toán: +, - ,* , /
BETWEEN …. AND
IS NULL, IS NOT NULL
LIKE (_ %)
IN, NOT IN
EXISTS , NOT EXISTS
SOME, ALL
5.5 Toán tử truy vấn(2)
IS NULL, IS NOT NULL
Select sohd from HOADON where makh is Null
Select * from HOADON where makh is Not Null
Toán tử so sánh, phép toán
Select gia*1.1 as [gia ban] from SANPHAM where nuocsx<>’Viet Nam’
Select * from SANPHAM where (gia between 20000 and 30000) OR (nuocsx=‘Viet Nam’)
Toán tử IN, NOT IN
Select * from SANPHAM where masp NOT IN (‘BB01’,’BB02’,’BB03’)
Toán tử LIKE
So sánh chuỗi tương đối
Cú pháp: s LIKE p, p có thể chứa % hoặc _
% : thay thế một chuỗi ký tự bất kỳ
_ : thay thế một ký tự bất kỳ
Ví dụ: Select masp,tensp from SANPHAM where masp like `B%01‘
5.5 Toán tử so sánh(3)
5.6 Câu truy vấn con (1)
In hoặc Exists
Ví dụ: Tìm các số hóa đơn mua cùng lúc 2 sản phẩm có mã số “BB01” và “BB02”.
select distinct sohd
from CTHD where masp=`BB01` and sohd IN
(select distinct sohd from CTHD where masp=`BB02`)
select distinct A.sohd
from CTHD A where A.masp=`BB01` and
EXISTS (select * from CTHD B
where B.masp=`BB02‘ and A.sohd=B.sohd)
5.6 Câu truy vấn con (2)
Not In hoặc Not Exists
Ví dụ: Tìm các số hóa đơn có mua sản phẩm mã số ‘BB01’ nhưng không mua sản phẩm mã số ‘BB02’.
select distinct sohd
from CTHD where masp=`BB01` and sohd NOT IN
(select distinct sohd from CTHD where masp=`BB02`)
select distinct A.sohd
from CTHD A where A.masp=`BB01` and
NOT EXITST (select * from CTHD B
where B.masp=`BB02‘ and A.sohd=B.sohd)
5.7 Phép chia
Sử dụng NOT EXISTS
Ví dụ: Tìm số hóa đơn đã mua tất cả những sản phẩm do “Trung Quoc” sản xuất.
Select sohd from HOADON where not exists (select * from SANPHAM
where nuocsx=‘Trung Quoc’ and not exists
(select * from CTHD where HOADON.sohd=CTHD.sohd and CTHD.masp=SANPHAM.masp))
5.8.1 Các hàm tính toán cơ bản
COUNT: Đếm số bộ dữ liệu của thuộc tính
MIN: Tính giá trị nhỏ nhất
MAX: Tính giá trị lớn nhất
AVG: Tính giá trị trung bình
SUM: Tính tổng giá trị các bộ dữ liệu
5.8 Các hàm tính toán và gom nhóm (1)
Ví dụ
Tính lương thấp nhất, cao nhất, trung bình và tổng lương của tất cả các nhân viên.
Có tất cả bao nhiêu nhân viên
Bao nhiêu nhân viên có người quản lý
Bao nhiêu phòng ban có nhân viên trực thuộc
Tính lương trung bình của các nhân viên
Tính lương trung bình của các nhân viên theo từng phòng ban
Tính lương thấp nhất, cao nhất, trung bình và tổng lương của tất cả các nhân viên.
SELECT min(luong) as thapnhat,
max(luong) as caonhat,
avg(luong) as trungbinh,
sum(luong) as tongluong
FROM NhanVien
2. Có tất cả bao nhiêu nhân viên
SELECT count(*) FROM NhanVien
3. Bao nhiêu nhân viên có người quản lý
Select count(*) FROM NhanVien WHERE manql is not null
SELECT count(Manql) FROM NhanVien
4. Bao nhiêu phòng ban có nhân viên trực thuộc
SELECT count(distinct phong) FROM NhanVien
5.8 Các hàm tính toán và gom nhóm (2)
5.8.2 Gom nhóm: mệnh đề GROUP BY
Sử dụng hàm gom nhóm trên các bộ trong quan hệ.
Mỗi nhóm bộ bao gồm tập hợp các bộ có cùng giá trị trên các thuộc tính gom nhóm
Hàm gom nhóm áp dụng trên mỗi bộ độc lập nhau.
SQL có mệnh đề GROUP BY để chỉ ra các thuộc tính gom nhóm, các thuộc tính này phải xuất hiện trong mệnh đề SELECT
5. Tính lương trung bình của các nhân viên
SELECT avg(LUONG) as LUONGTB
FROM NhanVien
6. Tính lương trung bình của các nhân viên theo từng phòng ban.
SELECT phong, avg(LUONG) as LUONGTB
FROM NhanVien
GROUP BY phong
5.8 Các hàm tính toán và gom nhóm (3)
5.8.3 Điều kiện sau gom nhóm: mệnh đề HAVING
Lọc kết quả theo điều kiện, sau khi đã gom nhóm
Điều kiện ở HAVING được thực hiện sau khi gom nhóm, các điều kiện có liên quan đến thuộc tính Group By
Ví dụ: tìm phòng có số lượng nhân viên “Nữ” trên 5 người
SELECT phong
FROM NhanVien
WHERE phai = ‘Nữ’
GROUP BY phong
HAVING count(manv) > 5
Bài 6: Ngôn ngữ tân từ
Nội dung
Giới thiệu
Cú pháp
Các định nghĩa
Diễn giải của một công thức
Quy tắc lượng giá công thức
Ngôn ngữ tân từ có biến là n bộ
Ngôn ngữ tân từ có biến là miền giá trị
1. Giới thiệu
Ngôn ngữ tân từ là ngôn ngữ truy vấn hình thức do Codd đề nghị (1972-1973) �
* Một số tài liệu cũ có thể bị lỗi font khi hiển thị do dùng bộ mã không phải Unikey ...
Người chia sẻ: Bạch Long Vĩ
Dung lượng: |
Lượt tài: 1
Loại file:
Nguồn : Chưa rõ
(Tài liệu chưa được thẩm định)