Bài giảng Tin học đại cương - Chương 5: Cơ sở dữ liệu

Nội dung text: Bài giảng Tin học đại cương - Chương 5: Cơ sở dữ liệu

1. 12/7/2013 TRƯỜNG ĐẠI HỌC NÔNG NGHIỆP HÀ NỘI Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương KHOA CÔNG NGHỆ THÔNG TIN NỘI DUNG • Cơ sở dữ liệu BÀI GIẢNG • Hệ quản trị cơ sở dữ liệu TIN HỌC ĐẠI CƯƠNG • Ngôn ngữ truy vấn SQL Chương 5 – CƠ SỞ DỮ LIỆU Chương 5: Cơ sở dữ liệu 2 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương NỘI DUNG Các khái niệm cơ bản • Cơ sở dữ liệu • Cơ sở dữ liệu (database): – Các khái niệm cơ bản – Là một hệ thống thông tin có cấu trúc được lưu trữ trên các thiết bị – Lợi ích của việc sử dụng cơ sở dữ liệu nhớ thứ cấp (băng từ, đĩa từ, ) để đáp ứng nhu cầu khai thác thông tin của nhiều người sử dụng hoặc nhiều chương trình ứng dụng với – Các đối tượng sử dụng cơ sở dữ liệu nhiều mục đích khác nhau. – Các mức biểu diển của cơ sở dữ liệu • Hệ cơ sở dữ liệu: Là một hệ thống gồm 4 thành phần: – Mô hình dữ liệu – Cở sở dữ liệu – Hệ cở sở dữ liệu – Những người sử dụng cơ sở dữ liệu • Hệ quản trị cơ sở dữ liệu – Hệ quản trị cơ sở dữ liệu • Ngôn ngữ truy vấn SQL – Phần cứng Chương 5: Cơ sở dữ liệu 3 Chương 5: Cơ sở dữ liệu 4 1
2. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Tại sao cần có CSDL? Hạn chế của hướng tiếp cận hệ tập tin • Hướng tiếp cận hệ tập tin • Dư thừa và không nhất quán: – Thông tin giống nhau có thể bị trùng lặp ở một số nơi. – Dữ liệu có thể không được cập nhật đúng. • Khó khăn khi truy xuất dữ liệu – Nhập dữ liệu mất nhiều thời gian. – Dữ liệu ở những hệ thống tập tin khác nhau Chương 5: Cơ sở dữ liệu 5 Chương 5: Cơ sở dữ liệu 6 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương CSDL dùng chung Lợi ích của việc sử dụng CSDL • Giảm bớt dư thừa dữ liệu trong lưu trữ • Tránh được sự không nhất quán trong lưu trữ dữ liệu và bảo đảm được tính toàn vẹn của dữ liệu. • Có thể triển khai đồng thời nhiều ứng dụng trên cùng một CSDL. • Thống nhất các tiêu chuẩn, thủ tục và các biện pháp bảo vệ, an toàn dữ liệu. Chương 5: Cơ sở dữ liệu 7 Chương 5: Cơ sở dữ liệu 8 2
3. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Các đối tượng sử dụng CSDL Các mức biểu diễn một CSDL • Mức vật lý (mức trong): • Người dùng cuối: Khai thác CSDL thông qua các ứng dụng – Nói đến cách thức lưu trữ dữ liệu như thế nào? Ở đâu? Cần các hoặc dựa trên phần mềm quản trị CSDL. chỉ mục gì? Việc truy xuất như thế nào? • Người lập trình ứng dụng: Là người viết các chương trình • Mức logic (mức khái niệm): ứng dụng cho phép người sử dụng cuối sử dụng CSDL. – Trả lời câu hỏi cần phải lưu trữ những loại dữ liệu gì? – Mối quan hệ giữa chúng như thế nào? • Người quản trị CSDL (Database Administrator): Là người thu thập dữ liệu, thiết kế và bảo trì CSDL, thiết lập các cơ • Mức khung nhìn (mức ngoài): chế đảm bảo an toàn cho CSDL (sao lưu, phục hồi dữ liệu). – Là mức của người dùng cuối và các chương trình ứng dụng. – Mỗi NDC hay chương trình ứng dụng có thể được nhìn CSDL theo một góc độ (khung nhìn) khác nhau. Chương 5: Cơ sở dữ liệu 9 Chương 5: Cơ sở dữ liệu 10 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Các mức biểu diễn một CSDL Mô hình dữ liệu • Mô hình dữ liệu là một tập các khái niệm dùng để mô tả dữ liệu, các mối quan hệ của dữ liệu, các ràng buộc trên dữ liệu của một CSDL. • Các mô hình dữ liệu: – Mô hình dữ liệu quan hệ (Relational Data Model) – Mô hình dữ liệu mạng (Network Data Model) – Mô hình dữ liệu phân cấp (Hieracical Data Model) – Mô hình dữ liệu hướng đối tượng (Object Oriented Data Model) – Chương 5: Cơ sở dữ liệu 11 Chương 5: Cơ sở dữ liệu 12 3
4. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Mô hình dữ liệu quan hệ Các khái niệm trong mô hình dữ liệu quan hệ • Mô hình dữ liệu quan hệ được đề xuất bởi E.F.Codd vào • Quan hệ (bảng) những năm 1970 - 1972. • Thuộc tính (cột, trường) • Biểu diễn mọi dữ liệu dưới dạng các bảng, bảng được định • Bộ giá trị (hàng, bản ghi) dạng gồm các hàng và cột. • Lược đồ quan hệ • Cơ sở dữ liệu được xây dựng trên mô hình dữ liệu quan hệ được gọi là CSDL quan hệ. • Khóa Chương 5: Cơ sở dữ liệu 13 Chương 5: Cơ sở dữ liệu 14 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Quan hệ Thuộc tính • Một quan hệ là một bảng trong đó: • Thuộc tính là một tính chất riêng biệt của một đối tượng – Mỗi hàng (bản ghi) biểu diễn một bộ giá trị của quan hệ. Số các bộ được gọi là lực lượng của quan hệ. cần được lưu trữ trong CSDL để phục vụ cho việc khai thác dữ liệu về đối tượng. – Mỗi cột (trường) biểu diễn một thuộc tính/ thành phần của các bộ. Số các thành phần được gọi là bậc của quan hệ. • Thuộc tính được đặc trưng bởi: – Tên gọi: Thuộc tính được đặt tên một cách gợi nhớ và theo quy định. – Kiểu: Mỗi thuộc tính đều phải thuộc một kiểu dữ liệu nhất định. – Miền giá trị: Là tập tất cả các giá trị mà thuộc tính có thể có. Chương 5: Cơ sở dữ liệu 15 Chương 5: Cơ sở dữ liệu 16 4
5. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Bộ giá trị Lược đồ quan hệ • Tên của một quan hệ và tập các thuộc tính của nó được gọi • Mỗi dòng của một quan hệ, trừ dòng tiêu đề - tên của các là một lược đồ đối với quan hệ đó. thuộc tính, được gọi là một bộ giá trị (bản ghi) • Biểu diễn lược đồ cho một quan hệ bởi tên của quan hệ và Đặc điểm • theo sau là danh sách các thuộc tính của nó. Hai bộ bất kỳ trong quan hệ là khác nhau ở ít nhất giá trị của – • Ví dụ một thuộc tính. – Một bộ không thể được xác định nhờ vị trí của nó trong quan hệ, mà được nhận diện nhờ khóa lược đồ quan hệ: MONHOC(MaMH, TenMH, SoTC, Hocky) Chương 5: Cơ sở dữ liệu 17 Chương 5: Cơ sở dữ liệu 18 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Khóa Khóa ngoại • Khóa (đề nghị) của một quan hệ là tập nhỏ nhất các thuộc • Một/ một tập thuộc tính của quan hệ mà giá trị của nó khớp tính mà giá trị của nó có thể xác định được duy nhất một với khóa chính của một quan hệ khác thì nó được gọi là khóa ngoại của quan hệ đó. bộ giá trị của quan hệ. • Khoá ngoại dùng để biểu thị liên kết giữa quan hệ này và • Một quan hệ có thể có nhiều khóa đề nghị, khi đó sẽ chọn quan hệ khác trong mô hình quan hệ. một khóa làm khóa chính • Ví dụ: • Ví dụ: quan hệ MONHOC(MaMH, TenMH, SoTC, Hocky) KHOA(Makhoa, Tenkhoa, Diadiem, SDT) có hai khóa đề nghị: MaMH, TenMH LOP(MaLop, TenL, Siso, Makhoa) chọn MaMH làm khóa chính Trong quan hệ LOP, Makhoa là khóa ngoại vì Makhoa là khóa chính của quan hệ KHOA Chương 5: Cơ sở dữ liệu 19 Chương 5: Cơ sở dữ liệu 20 5
6. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương NỘI DUNG Khái niệm • Cơ sở dữ liệu • Hệ quản trị cơ sở dữ liệu (Database Management System - DBMS): Là một hệ thống phần mềm cho phép tạo lập cơ sở • Hệ quản trị cơ sở dữ liệu dữ liệu và điều khiển mọi truy nhập đối với cơ sở dữ liệu đó. − Khái niệm • Một số hệ quản trị csdl hiện nay: − Chức năng của hệ QTCSDL – Microsoft Access − Một số hệ QTCSDL thông dụng – SQL Server • Ngôn ngữ truy vấn SQL – Oracle – Foxpro – DB2, Chương 5: Cơ sở dữ liệu 21 Chương 5: Cơ sở dữ liệu 22 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Chức năng của hệ quản trị csdl (1) Chức năng của hệ quản trị csdl (2) • Cung cấp môi trường tạo lập cơ sở dữ liệu. • Cung cấp công cụ kiểm soát, điểu khiển việc truy cập – Cho phép khai báo kiểu và cấu trúc của dữ liệu, khai báo các vào CSDL. Hệ quản trị CSDL đảm bảo: ràng buộc trên dữ liệu được lưu trữ trong CSDL. − Phát hiện và ngăn chặn truy cập trái phép • Cung cấp môi trường cập nhật và khai thác dữ liệu. − Duy trì tính nhất quán của dữ liệu. – Cập nhật: Thêm, xóa, sửa dữ liệu. − Tổ chức, điều khiển các truy cập cùng lúc. − Khôi phục CDSL khi gặp sự cố. – Khai thác: Sắp xếp, tìm kiếm, kết xuất báo cáo, − Quản lí các mô tả dữ liệu. Chương 5: Cơ sở dữ liệu 23 Chương 5: Cơ sở dữ liệu 24 6
7. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương NỘI DUNG Ngôn ngữ truy vấn SQL (1) • Cơ sở dữ liệu • Là ngôn ngữ truy vấn dựa trên đại số quan hệ. • Hệ quản trị cơ sở dữ liệu • Cho phép người dùng giao tiếp với CSDL. • Gồm các loại: • Ngôn ngữ truy vấn SQL – Ngôn ngữ định nghĩa dữ liệu (Data Definition Language– – Ngôn ngữ định nghĩa dữ liệu (Data Definition Language – DDL): Cho phép khai báo cấu trúc các bảng của CSDL, khai DDL) báo các mối liên hệ của dữ liệu và các quy tắc áp đặt lên các Ngôn ngữ thao tác dữ liệu (Data Manipulation Language- – dữ liệu đó. DML) Chương 5: Cơ sở dữ liệu 25 Chương 5: Cơ sở dữ liệu 26 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Ngôn ngữ truy vấn SQL (2) CSDL ví dụ – Ngôn ngữ thao tác dữ liệu (Data Manipullation Language- Bài toán quản lý sinh viên có CSDL gồm các bảng như DML): Cho phép người sử dụng khai thác CSDL để truy vấn các sau: thông tin cần thiết trong CSDL. Đồng thời cho phép thêm KHOA(Makhoa, Tenkhoa, Diadiem, SDT) (insert), xoá (delete), sửa (update) dữ liệu trong CSDL LOP(MaLop, TenL, Siso, Makhoa) – Ngôn ngữ quản lý dữ liệu (Data Control Language-DCL): SINHVIEN(MaSV, Hodem, Ten, Ngaysinh, Gioitinh, Bao gồm các câu lệnh đảm bảo tính an toàn và toàn vẹn dữ liệu, cho phép cấp phát quyền truy cập vào dữ liệu Tinh, MaLop) MONHOC(MaMH, TenMH, SoTC, Dieukien) KETQUA(MaSV, MaMH, Ketqua) Chương 5: Cơ sở dữ liệu 27 Chương 5: Cơ sở dữ liệu 28 7
8. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Ngôn ngữ định nghĩa dữ liệu 1. Lệnh tạo bảng (1) 1. Lệnh tạo bảng • Định nghĩa một bảng cần: – Tên bảng 2. Lệnh sửa bảng – Các thuộc tính: 1. Lệnh thêm cột • Tên thuộc tính • Kiểu dữ liệu 2. Lệnh thay đổi kiểu dữ liệu cho cột • Ràng buộc toàn vẹn trên thuộc tính đó 3. Thêm ràng buộc toàn vẹn CREATE TABLE ( 4. Lệnh xóa cột [RBTV] , [RBTV], 3. Lệnh xóa bảng [RBTV]); Chương 5: Cơ sở dữ liệu 29 Chương 5: Cơ sở dữ liệu 30 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 1. Lệnh tạo bảng (2) 1. Lệnh tạo bảng (3) • Ràng buộc toàn vẹn: • Ví dụ: tạo bảng sinh viên – NOT NULL CREATE TABLE SINHVIEN( – NULL MaSV char(6) not null PRIMARY KEY, – IDENTITY Hodem char(20) not null, – UNIQUE TEN char(7) not null, – DEFAULT Ngaysinh datetime, – PRIMARY KEY gioitinh char(3), – FOREIGN KEY/ REFERENCES tinh varchar(30), – CHECK MaLOP char(8) not null); Chương 5: Cơ sở dữ liệu 31 Chương 5: Cơ sở dữ liệu 32 8
9. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 2.1 Lệnh thêm cột 2.2 Thay đổi kiểu dữ liệu cho cột ALTER TABLE ALTERTABLE ADD [RBTV]; ALTER COLUMN ; • Ví dụ: thêm cột Chính sách vào bảng SINHVIEN • Ví dụ: sửa kiểu dữ liệu cột Gioitinh trong bảng SINHVIEN từ ALTER TABLE SINHVIEN kiểu char sang kiểu bit [0/1] ADD Chinhsach char(10); ALTER TABLE SINHVIEN ALTER COLUMN gioitinh bit; Chương 5: Cơ sở dữ liệu 33 Chương 5: Cơ sở dữ liệu 34 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 2.3 Thêm ràng buộc toàn vẹn (1) 2.3 Thêm ràng buộc toàn vẹn (2) • Thêm khóa chính: • Tạo khóa ngoài: ALTER TABLE ALTER TABLE ADD PRIMARY KEY (DS tên cột); ADD FOREIGN KEY (DS tên cột) • Ví dụ: đặt khóa chính trên cột MaSV của bảng SINHVIEN REFERENCES (DS tên cột); ALTER TABLE SINHVIEN • Ví dụ: Đặt Malop là khóa ngoài trên bảng SINHVIEN add PRIMARY KEY (masv); ALTER TABLE SINHVIEN ADD FOREIGN KEY(malop) REFERENCES LOP (malop); Chương 5: Cơ sở dữ liệu 35 Chương 5: Cơ sở dữ liệu 36 9
10. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 2.4 Xóa cột 3. Xóa bảng • Lệnh Xóa Cột DROP TABLE ; ALTER TABLE • Ví dụ: xóa bảng SINHVIEN DROP COLUMN ; DROP TABLE SINHVIEN; • Ví dụ: xóa cột chính sách trong bảng SINHVIEN ALTER TABLE SINHVIEN DROP COLUMN Chinhsach; Chương 5: Cơ sở dữ liệu 37 Chương 5: Cơ sở dữ liệu 38 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương Ngôn ngữ thao tác dữ liệu Nhóm lệnh truy vấn dữ liệu • Nhóm lệnh truy vấn dữ liệu 1. Cú pháp tổng quát 2. Dạng đơn giản • Nhóm lệnh cập nhật dữ liệu 3. Dấu* 4. Mệnh đề WHERE 5. Truy vấn từ nhiều Bảng 6. Mệnh đề ORDER BY 7. Mệnh đề GROUP BY 8. Mệnh đề HAVING 9. Phát biểu Select với AS 10. Phát biểu Select với DISTINCT 11. Truy vấn con Chương 5: Cơ sở dữ liệu 39 Chương 5: Cơ sở dữ liệu 40 10
11. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 1. Cú pháp tổng quát 2. Dạng đơn giản SELECT SELECT FROM FROM ; WHERE lấy ra một số cột trong một bảng nào đó. GROUP BY • Ví dụ: lấy ra mã sinh viên, họ đệm và tên của các sinh viên HAVING trong bảng SINHVIEN ORDER BY SELECT MaSV, Hodem, Ten FROM SINHVIEN; Chương 5: Cơ sở dữ liệu 41 Chương 5: Cơ sở dữ liệu 42 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 3. Dấu * 4. Mệnh đề WHERE (1) • Dấu * đại diện cho tất cả các cột • Dùng để đặt điều kiện lấy dữ liệu SELECT SELECT * FROM FROM ; WHERE ; • Ví dụ: Lấy ra danh sách các khoa • Ví dụ: lấy ra mã sinh viên, họ đệm và tên của các sinh viên SELECT * nữ FROM KHOA; SELECT MaSV, Hodem, Ten FROM SINHVIEN WHERE Gioitinh = “nữ”; Chương 5: Cơ sở dữ liệu 43 Chương 5: Cơ sở dữ liệu 44 11
12. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 4. Mệnh đề WHERE (2) 4. Mệnh đề WHERE (3) • Các phép toán trong mệnh đề WHERE: • Toán tử BETWEEN AND So sánh: >, =, – • Ví dụ: Lấy ra danh sách các môn học có từ 2 đến 5 tín chỉ – Logic: And, Or, Not SELECT * • Ví dụ: lấy ra mã sinh viên, họ đệm và tên của các sinh viên FROM MONHOC nữ học lớp K55CNSHA WHERE Sotc between 2 and 5; SELECT MaSV, Hodem, Ten FROM SINHVIEN WHERE Gioitinh = “nữ” and MaLop = “K55CNSHA”; Chương 5: Cơ sở dữ liệu 45 Chương 5: Cơ sở dữ liệu 46 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 4. Mệnh đề WHERE (4) 4. Mệnh đề WHERE (5) Toán tử [NOT] IN: phép toán so sánh trong 1 tập hợp, 1 • Toán tử [NOT] LIKE: tìm một mẫu ký tự • danh sách • Sử dụng với các ký tự đại diện: _(1 ký tự), % (1 chuỗi bất kỳ) • Ví dụ: lấy ra thông tin của các lớp thuộc khoa công nghệ • Ví dụ: lấy ra danh sách Sinh viên Họ ‘Nguyễn’ thông tin, nông học và chăn nuôi thú y. SELECT * SELECT* FROM SINHVIEN FROM LOP WHERE Hodem LIKE “Nguyễn*”; WHERE Makhoa IN (“CNTT”, “NH”, “CNTY”); Chương 5: Cơ sở dữ liệu 47 Chương 5: Cơ sở dữ liệu 48 12
13. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 5. Truy vấn từ nhiều bảng 5. Truy vấn từ nhiều bảng • Khi thông tin cần lấy ra có từ nhiều bảng khác nhau, cần thực hiện truy vấn từ nhiều bảng • Ví dụ: Hiển thị thông tin về các sinh viên với các kết quả học tập của họ. Thông tin hiển thị cần (mã sinh viên, họ SELECT tên, ngày sinh, giới tính, tên môn học, kết quả): FROM SELECT SINHVIEN. MaSV, Hodem, Ten, Ngaysinh, Gioitinh, WHERE ; TenMH, Ketqua • Lưuý: FROM SINHVIEN, KETQUA, MONHOC – Cần liên kết các bảng lại với nhau. Với n bảng cần có n-1 điều WHERE SINHVIEN.MaSV = KETQUA.MaSV AND kiện liện kết. KETQUA.MaMH = MONHOC.MaMH; – Các tên cột cùng có ở nhiều bảng cần ghi rõ theo dạng [Tên Bảng].[Tên cột] Chương 5: Cơ sở dữ liệu 49 Chương 5: Cơ sở dữ liệu 50 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 6. Mệnh đề ORDER BY 7. Mệnh đề GROUP BY (1) • Sắp xếp kết quả theo thứ tự mong muốn • Nhóm dữ liệu lại theo từng nhóm để thực hiện các phép toán thống kê • ORDER BY [ASC | DESC] GROUP BY • Ví dụ: lấy ra các sinh viên nữ học lớp K55CNSHA, được sắp • Ví dụ: In ra danh sách các lớp và số sinh viên trong mỗi lớp từ xếp theo vần alphabet của họ và tên. bảng SinhVien SELECT MaSV, Hodem, Ten SELECT SINHVIEN.MaLop, LOP.TenL, COUNT(SINHVIEN.MaSV) AS 'So sinh vien' FROM SINHVIEN FROM SINHVIEN, LOP WHERE Gioitinh = “nữ” and MaLop = “K55CNSHA” WHERE SINHVIEN.MaLop = LOP.MaLop ORDER BY Ten, Hodem; GROUP BY SINHVIEN.MaLop, LOP.TenL; Chương 5: Cơ sở dữ liệu 51 Chương 5: Cơ sở dữ liệu 52 13
14. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 7. Mệnh đề GROUP BY (2) 8. Mệnh đề HAVING • Một số hàm thông dụng: • Đặt điều kiện chọn sau khi đã nhóm dữ liệu bằng mệnh đề – AVG: giá trị trung bình GROUP BY – MIN: giá trị nhỏ nhất • Ví dụ: In ra danh sách các lớp có số sinh viên >2 từ bảng SINHVIEN – MAX: giá trị lớn nhất SELECT SINHVIEN.MaLop, LOP.TenL, COUNT(SINHVIEN.MaSV) AS – COUNT: đếm số phần tử 'So sinh vien' – SUM: Tổng các phần tử FROM SINHVIEN, LOP WHERE SINHVIEN.MaLop = LOP.MaLop GROUP BY SINHVIEN.MaLop, LOP.TenL HAVING COUNT(SINHVIEN.MaSV)>=2 Chương 5: Cơ sở dữ liệu 53 Chương 5: Cơ sở dữ liệu 54 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 9. Phát biểu Select với AS 10. Phát biểu Select với DISTINCT • Đặt lại tên Trường khi hiển thị kết quả • Nếu kết quả truy vấn có nhiều bản ghi trùng nhau, để chỉ • Ví dụ: Hiển thị thông tin về các sinh viên với các kết quả lấy 1 mẫu tin ta dùng DISTINCT học tập của họ. Khi hiển thị cột Ketqua đổi tên thành cột • Ví dụ: In ra danh sách các lớp trong bảng SINHVIEN DiemTB SELECT DISTINCT MALOP SELECT SINHVIEN.MaSV, Hodem, Ten, Ngaysinh, Gioitinh, TenMH, Ketqua as DiemTB FROM SINHVIEN; FROM SINHVIEN, KETQUA, MONHOC WHERE SINHVIEN.MaSV = KETQUA.MaSV AND KETQUA.MaMH = MONHOC.MaMH; Chương 5: Cơ sở dữ liệu 55 Chương 5: Cơ sở dữ liệu 56 14
15. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 11. Truy vấn con (1) 12. Truy vấn con (2) • Dạng tổng quát: • Đôi khi ta cần sử dụng kết quả của 1 câu truy vấn để làm SELECT điều kiện cho 1 câu truy vấn khác, khi đó ta gọi là truy vấn FROM con. WHERE • Khi thực hiện, truy vấn con sẽ được thực hiện trước, rồi lấy IN ( NOT IN, =, FROM WHERE ) Chương 5: Cơ sở dữ liệu 57 Chương 5: Cơ sở dữ liệu 58 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 12. Truy vấn con (3) Nhóm lệnh cập nhật dữ liệu • Ví dụ: Hiển thị thông tin về những sinh viên đã đăng kí học 1. Lệnh thêm bản ghi và không phải thi lại môn học nào. 2. Lệnh sửa bản ghi SELECT Sinhvien.MaSV, hodem, ten, ngaysinh, maMH, ketqua 3. Lệnh xóa bản ghi FROM SINHVIEN, KETQUA WHERE (SINHVIEN.MaSV=KETQUA.MaSV) AND SINHVIEN.MaSV IN (SELECT MaSV FROM KETQUA WHERE ketqua>=5); Chương 5: Cơ sở dữ liệu 59 Chương 5: Cơ sở dữ liệu 60 15
16. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 1. Lệnh thêm bản ghi (1) 1. Lệnh thêm bản ghi (2) INSERT INTO ( ) • Nếu các giá trị sau từ khoá VALUES hoàn toàn phù hợp về thứ tự với các cột trong bảng, thì danh sách các cột có thể VALUES ( ); được bỏ qua. • Ví dụ: thêm một dòng vào bảng SINHVIEN • Ví dụ: thêm một dòng vào bảng SINHVIEN INSERT INTO SINHVIEN (MaSV, Hodem, Ten, Ngaysinh, INSERT INTO SINHVIEN Gioitinh, Tinh, MaLop) VALUES ("563495", "Nguyễn Lan", "Phương", VALUES ("561205", "Nguyễn Tiến", "Minh", "3/8/92", "13/10/1992", "Nữ", "Hà Nội", "K56CNTYA"); "Nam", "Hà Nội","K57CNSHA"); Chương 5: Cơ sở dữ liệu 61 Chương 5: Cơ sở dữ liệu 62 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 1. Lệnh thêm bản ghi (3) 1. Lệnh thêm bản ghi (4) • Ta có thể thêm các bản ghi vào bảng từ câu lệnh truy vấn • Ví dụ: thêm tất cả các dòng trong bảng DSSV vào bảng (Select) SINHVIEN: INSERT INTO ( , ) INSERT INTO SINHVIEN ; SELECT * FROM DSSV; hoặc • Lưu ý: hai bảng DSSV và SINHVIEN phải có cấu trúc giống INSERT INTO nhau ; Chương 5: Cơ sở dữ liệu 63 Chương 5: Cơ sở dữ liệu 64 16
17. 12/7/2013 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 2. Lệnh sửa bản ghi (1) 2. Lệnh sửa bản ghi (2) UPDATE • Ví dụ: sửa hộ khẩu của các sinh viên từ Hà Tây thành Hà SET = , = , Nội , UPDATE SINHVIEN = SET tinh = "Hà Nội" [WHERE ]; • Ý nghĩa: WHERE tinh = "Hà Tây"; – Giá trị của các cột , của những bản ghi thoả mãn điều kiện sau WHERE sẽ được sửa đổi thành , . – Nếu không có mệnh đề WHERE thì tất cả các bản ghi của bảng sẽ được sửa đổi. Chương 5: Cơ sở dữ liệu 65 Chương 5: Cơ sở dữ liệu 66 Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Khoa Công nghệ thông tin – Trường Đại học Nông nghiệp Hà Nội Bài giảng Tin học đại cương Bài giảng Tin học đại cương 3. Lệnh xóa bản ghi (1) 3. Lệnh xóa bản ghi (2) DELETE FROM • Ví dụ: xóa tất cả các bản ghi trong bảng DSSV [WHERE ]; DELETE FROM DSSV; • Ý nghĩa: • Ví dụ: Xóa sinh viên lớp K53MTA khỏi bảng SINHVIEN – Các bản ghi thoả mãn điều kiện WHERE sẽ bị xoá khỏi bảng. DELETE FROM SINHVIEN – Nếu không có mệnh đề WHERE thì tất cả các bản ghi của bảng WHERE MaLop = “K53MTA”; sẽ bị xóa khỏi bảng. Chương 5: Cơ sở dữ liệu 67 Chương 5: Cơ sở dữ liệu 68 17