›

Quản trị Cơ sở dữ liệu và Phần mềm ứng dụng - Chương 3: Ngôn ngữ SQl

pdf 83 trang huongle 5420
Download
Bạn đang xem 20 trang mẫu của tài liệu "Quản trị Cơ sở dữ liệu và Phần mềm ứng dụng - Chương 3: Ngôn ngữ SQl", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

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

  • pdfquan_tri_co_so_du_lieu_va_phan_mem_ung_dung_chuong_3_ngon_ng.pdf

Nội dung text: Quản trị Cơ sở dữ liệu và Phần mềm ứng dụng - Chương 3: Ngôn ngữ SQl

  1. Quản trị Cơ sở dữ liệu và Phần mềm ứng dụng Bộ môn CNTT – TMĐT Khoa Thương mại điện tử
  2. Chương III Ngôn ngữ SQL
  3. Chương III: Ngôn ngữ SQL 1. Đại số quan hệ và ngôn ngữ SQL 2. Lệnh định nghĩa dữ liệu 3. Lệnh cập nhật dữ liệu 4. Lệnh truy vấn dữ liệu Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 3
  4. Chương III 1. Đại số quan hệ và ngôn ngữ SQL 1.1. Đại số quan hệ 1.2. Ngôn ngữ SQL 2. Lệnh định nghĩa dữ liệu 3. Lệnh cập nhật dữ liệu 4. Lệnh truy vấn dữ liệu Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 4
  5. 1.1.Đại số quan hệ Đại số quan hệ cung cấp tám phép toán tác động trên các quan hệ và cho kết quả cũng là một quan hệ gồm:  Các phép toán tập hợp: Hợp, trừ, giao, tich Đề các.  Các phép toán quan hệ: Chọn, chiếu, kết nối, chia. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 5
  6. Đại số quan hệ(t) Quan hệ khả hợp  Định nghĩa: Hai quan hệ là khả hợp nếu chúng được xác định trên cùng tập thuộc tính và các thuộc tính cùng tên có cùng miền giá trị. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 6
  7. a. Phép hợp (union) Định nghĩa:  Phép hợp của hai quan hệ khả hợp r và s, ký hiệu là r U s, là tập tất cả các bộ thuộc r hoặc thuộc s hoặc thuộc cả hai quan hệ. Ví dụ: A B C A B C A B C a1 b1 c1 a1 b1 c1 a1 b1 c1 = a1 b1 c2 U a1 b2 c1 a1 b1 c2 a1 b2 c2 a1 b2 c2 a1 b2 c1 a2 b2 c2 a1 b2 c2 a3 b2 c2 a2 b2 c2 Bài giảng CSDL và Phần mềm ứng a3 b2 c2 03/11/2008 dụng 7
  8. b. Phép giao Định nghĩa:  Phép giao của hai quan hệ khả hợp r và s, ký hiệu là r s, là tập tất cả các bộ thuộc cả hai quan hệ r và s. Ví dụ: A B C A B C A B C a1 b1 c1 U a1 b1 c1 a1 b1 c1 = a1 b1 c2 a1 b2 c1 a1 b2 c2 a1 b2 c2 a1 b2 c2 a2 b2 c2 a3 b2 c2 Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 8
  9. c. Phép trừ Định nghĩa:  Phép trừ của hai quan hệ khả hợp r và s, ký hiệu là r-s, là tập tất cả các bộ thuộc r nhưng không thuộc s. Ví dụ: A B C A B C A B C a1 b1 c1 a1 b1 c1 a1 b1 c2 = a1 b1 c2 - a1 b2 c1 a2 b2 c2 a1 b2 c2 a1 b2 c2 a3 b2 c2 a2 b2 c2 a3 b2 c2 Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 9
  10. d. Phép tích Đề các Định nghĩa:  Cho quan hệ r xác định trên tập thuộc tính {A1, , An} và quan hệ s xác định trên tập thuộc tính {B1, , Bm}. Tích Đề các của hai quan hệ r và s ký hiệu là r x s là tập tất cả các (m+n)-bộ có n thành phần đầu tiên là một bộ thuộc r và m thành phần sau là một bộ thuộc s. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 10
  11. Phép tích Đề các(t)  Ví dụ: A B C D E A B C D E a1 b1 1 1 e1 a1 b1 1 1 e1 = a2 b2 2 x 2 e2 a1 b1 1 2 e2 a3 b3 3 3 e3 a1 b1 1 3 e3 a2 b2 2 1 e1 a2 b2 2 2 e2 a2 b2 2 3 e3 a3 b3 3 1 e1 a3 b3 3 2 e2 a3 b3 3 3 e3 Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 11
  12. e. Phép chiếu Định nghĩa;  Cho quan hệ r xác định trên tập thuộc tính U={A1, , An}. X U. Phép chiếu của r trên tập thuộc tính X , ký hiệu là Πx(r), là tập các bộ của r xác định trên X. Ví dụ: A B Π (r) = Π (s) = C A,B a1 b1 C a1 b2 c1 a2 b2 c2 a3 b2 Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 12
  13. Phép chiếu trên một quan hệ thực chất là phép toán loại bỏ đi một số thuộc tính và chỉ giữ lại những thuộc tính còn lại của quan hệ đó. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 13
  14. f. Phép chọn Định nghĩa:  Cho r là một quan hệ, F là một biểu thức điều kiện. Phép chọn trên r với biểu thức chọn F, ký hiệu σF(r), là tập tất cả các bộ của r thỏa mãn điều kiện F. Ví dụ: σA = a1(r)= σA = a1 ^ C= c 2(r)= A B C A B C a1 b1 c1 a1 b1 c2 a1 b2 c2 a1 b2 c2 a1Bài gib1ảng CSDLc2 và Phần mềm ứng 03/11/2008 dụng 14
  15. Phép chọn là phép toán lọc ra một tập con các bộ của quan hệ đã cho thỏa mãn một điêu kiện xác định. Điều kiện dó được gọi là điều kiện chọn hay biểu thức chọn. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 15
  16. g. Phép chia Định nghĩa:  Cho hai quan hệ r xác định trên n thuộc tính, s xác định trên m thuộc tính (n>m, s ≠ ø), phép chia quan hệ r cho quan hệ s, ký hiệu r ÷ s, là các bộ t sao cho với mọi bộ v thuộc s thì t ghép với v thuộc r. A B B Ví dụ: A a1 b1 ÷ b1 = a1 a1 b2 b2 a1 b3 b3 a2 b4 Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 16
  17. h. Phép kết nối Khái niệm:  Phép kết nối hai quan hệ r, s, ký hiệu ∆ r ∆ s là phép ghép các cặp bộ của hai quan hệ thỏa mãn một điều kiện kết nối hay một biêu thức kết nối F. Ví dụ: F= (C≤D) A B C D E A B C D E a1 b1 1 1 e1 ∆ a1 b1 1 ∆ 1 e1 a1 b1 1 2 e2 = a2 b2 2 2 e2 a1 b1 1 3 e3 a3 b3 3 3 e3 a2 b2 2 2 e2 a2 b2 2 3 e3 Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng a3 b3 3 3 e317
  18. Biểu diễn câu hỏi bằng đại số quan hệ Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 18
  19. III.1.2. Ngôn ngữ SQL SQL ( Structured Query Language) : Ngôn ngữ truy vấn có cấu trúc  Tiền thân SEQUEL, IBM phát triển cho hệ CSDL thử nghiệm System/R, 1974  1986, ANSI/ISO công nhận một chuẩn ngôn ngữ sử dụng trên csdl quan hệ. SQL_86 (ANSI) SQL_89 (sửa đổi nhỏ) SQL_92 (chuẩn hiện tại, ANSI/ISO) SQL_99 (mở rộng – đối tượng) SQL_2003 (hỗ trợ XML và nhiều tính năng khác) SQL_2006 Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 19
  20. Các hệ quản trị cơ sở dữ liệu quan hệ thương mại hiện có như Oracle, SQL Server, Informix, DB2, đều chọn SQL làm ngôn ngữ cho sản phẩm của mình. SQL cài đặt trong các hệ quản trị CSDL thương mại có một số khác biệt so với SQL do ANSI/ISO đề xuất. Các câu lệnh SQL cung cấp có thể được nhúng vào trong các ngôn ngữ lập trình nhằm xây dựng các ứng dụng tương tác với cơ sở dữ liệu. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 20
  21. a. Các thành phần Ngôn ngữ định nghĩa dữ liệu (DDL)  Định nghĩa/thay đổi/ xóa lược đồ quan hệ  Định nghĩa khung nhìn  Đặc tả quyền truy nhập  Đặc tả ràng buộc toàn vẹn Ngôn ngữ thao tác dữ liệu (DML)  Thêm/xóa/sửa/truy vấn các bộ giá trị trong quan hệ Ngôn ngữ điều khiển giao dịch  Đặc tả sự bắt đầu và kết thúc giao dịch  Điều khiển tương tranh Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 21
  22. b. Các dạng quan hệ Bảng cơ sở  Quan hệ được lưu trữ vật lý trong csdl Kết quả truy vấn  Kết quả truy vấn trên các quan hệ là một quan hệ. Khung nhìn  Quan hệ ảo được định nghĩa bởi một biểu thức truy vấn Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 22
  23. Chương III 1. Đại số quan hệ và ngôn ngữSQL 2. Lệnh định nghĩa dữ liệu 2.1. Tao csdl, xóa csdl 2.2. Tạo bảng 2.3. Sửa đổi cấu trúc bảng 2.4. Xoá bảng 2.5. Tạo khung nhìn 3. Lệnh cập nhật dữ liệu 4. Lệnh truy vấn dữ liệu Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 23
  24. 2.1.Tạo csdl, xóa csdl Tạo csdl  CREATE DATABASE Xóa csdl  DROP DATABASE Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 24
  25. 2.2. Tạo bảng Cú pháp CREATE TABLE ( [NOT NULL], [CONSTRAINT ]) Ví dụ: Tạo bảng về khách hàng có các trường là mã khách hàng, tên khách hàng, địa chỉ , số điện thoại và khóa chính là mã khách hàng. CREATE TABLE KHACH_HANG (MaKH char(5) NOT NULL, TenCongTy varchar(40), DiaChi varchar (60), DienThoaiCD char(7), CONSTRAINT khoa_chinh PRIMARY KEY (MaKH)) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 25
  26. 2.3. Sửa đổi cấu trúc bảng Cú pháp Ví dụ Thêm cột ALTER TABLE Thêm cột giá vào bảng ADD COLUMN CUNG_UNG ứng ALTER TABLE CUNG_UNG ADD COLUMN Gia int Xóa cột ALTER TABLE Xóa cột giá trong bảng DROP COLUMN CUNG_UNG ALTER TABLE CUNG_UNG DROP COLUMN Gia Sửa kiểu ALTER TABLE Sửa đổi kiểu dữ liệu của cột giá dữ liệu CHANGE COLUMN ALTER TABLE CUNG_UNG CHANGE COLUMN Gia real Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 26
  27. Sửa đổi cấu trúc bảng(t) Cú pháp Ví dụ Thêm ALTER TABLE ADD chiếu tới mã MaNCC của bảng buộc CONSTRAINT ADD CONSTRAINT khoa_ngoai FOREIGN KEY (MaNCC) REFERENCES NHA_CUNG_CAP(MaNCC) Xóa ALTER TABLE DROP Sự cung ứng buộc CONTRAINT DROP CONSTRAINT khoa_ngoai Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 27
  28. 2.4. Xóa bảng Cú pháp DROP TABLE Ví dụ: Xóa bảng KHACH_HANG DROP TABLE KHACH_HANG Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 28
  29. 2.5. Tạo khung nhìn Cú pháp:  CREAT VIEW [(danh sách tên cột)] AS Ví dụ  Bảng cơ sở: NHAN_VIEN(MaNV, TenNV, DiaChi, MaPhong, Luong, NamLenLuong, DanhGia) PHONG_BAN(MaPhong, TenPhong, NguoiQuanLy) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 29
  30. Tạo khung nhìn(t) Khung nhìn: phục vụ cho đối tượng là trưởng phòng của phòng kỹ thuật CREAT VIEW KT(TênNV, Đchỉ, Lương, NămLênlương, Đánh giá) AS SELECT TenNV, DiaChi, MaPhong, Luong, NamLenLuong, DanhGia FROM NHAN_VIEN WHERE MaPhong IN SELECT MaPhong FROM PHONG_BAN WHERE TenPhong = ‘Kỹ thuật’ Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 30
  31. Chương III 1. Đại số quan hệ và ngôn ngữ SQL 2. Lệnh định nghĩa dữ liệu 3. Lệnh cập nhật dữ liệu 3.1. Thêm một bộ giá trị 3.2. Xóa bộ giá trị 3.3. Thay đổi thuộc tính các bộ 4. Lệnh truy vấn dữ liệu Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 31
  32. 3.1.Thêm bộ giá trị Cú pháp:  INSERT INTO [(danh sách tên cột)] { VALUES (các giá trị)| } Ví dụ: Thêm một bộ giá trị vào bảng NHA_CUNG_CAP.  INSERT INTO NHA_CUNG_CAP (MaNCC, TenCongTy, DiaChi) VALUES ('S1', 'Hải Hà', 'Hà Nội') Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 32
  33. 3.2. Xóa bộ giá trị Cú pháp:  DELETE FROM { } [WHERE ] Ví dụ: Xóa các bộ có mã nhà cung cấp = S1 tại hai bảng CUNG_UNG và NHA_CUNG_CAP  DELETE * FROM CUNG_UNG WHERE MaNCC = 'S1'  DELETE * FROM NHA_CUNG_CAP WHERE MaNCC = 'S1' Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 33
  34. 3.3. Thay đổi giá trị thuộc tính các bộ Cú pháp:  UPDATE SET = [WHERE ] Ví dụ: Thay đổi thuộc tính tên của bộ giá trị có MaHang là = P1 trong bảng HANG_HOA.  UPDATE Items SET TenHang = 'Bánh ngọt' WHERE MaHang= 'P1' Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 34
  35. Lược đồ csdl quan hệ của siêu thị M EMPS(ENAME, SALARY, DNAME) DEPTS(DNAME, DEPT#, MGR) ITEMS(INAME, ITEM#, DNAME) CUSTOMERS(CNAME, CADDR, BALANCE) SUPPLIERS(SNAME, SADDR) ORDERS(O#, DATE, CNAME) SUPPLIES(SNAME, INAME, PRICE) INCLUDES(O#, INAME, QUANTITY) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 35
  36. Lược đồ csdl quan hệ của siêu thị M NHAN_VIEN (TenNV, Luong, TenPhong) PHONG_BAN(TenPhong, MaPhong, NguoiQuanLy) MAT_HANG(TenHang, MaHang, TenPhong) KHACH_HANG(TenKH, DiaChi, SoDuTK) NHA_CUNG_CAP(TenNCC, DiaChi) DON_DAT_HANG(MaDDH, NgayLap, TenKH) CUNG_UNG(TenNCC, TenHang, Gia) CHI_TIET_DON_HANG(MaDDH, TenHang, SoLuong) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 36
  37. Thay đổi Nhân viên được quản lý theo mã nhân viên. Phòng ban được quản lý theo mã phòng ban. Mặt hàng được quản lý theo mã hàng. Khách hàng được quàn lý theo mã khách hàng. Nhà cung cấp được quản lý theo mã nhà cung cấp. Các mặt hàng phân thành nhiều nhóm hàng. Mỗi gian hàng quản lý/bán một nhóm hàng. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 37
  38. Lược đồ csdl quan hệ mới của siêu thị M NHAN_VIEN (MaNV,Ho, Ten, Luong, MaPhong, ) PHONG_BAN(MaPhong, TenPhong, NguoiQL, ) MAT_HANG(MaHang,TenHang, MaNhom, ) NHOM_HANG(MaNhom, TenNhom, MaPhong, ) KHACH_HANG(MaKH, TenKH, DiaChi, SoDuTK, ) NHA_CUNG_CAP(MaNCC, TenNCC, DiaChi, ) DON_DAT_HANG(MaDDH, NgayLap, MaKH, ) CUNG_UNG(MaNCC, MaHang, Gia, ) CHI_TIET_DON_HANG(MaDDH, MaHang, SoLuong, ) Chú thích: “ ” lược đồ có thể bổ sung thêm một số thuộc tính khác (không quan trọng) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 38
  39. Tạo cơ sở dữ liệu cho siêu thị M theo lược đồ đã được thay đổi Bảng PHONG_BAN CREATE TABLE PHONG_BAN (MaPhong char(5) not null, TenPhong nvarchar(100), NguoiQL char(5), CONSTRAINT khoa_chinhP PRIMARYKEY (MaPhong)) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 39
  40. Bảng NHAN_VIEN CREATE TABLE NHAN_VIEN (MaNV char(7) NOT NULL, Ho nvarchar(50), Ten nvarchar(50), Luong real, MaPhong char(5), CONSTRAINT khoa_chinhNV PRIMARYKEY(MaNV), CONSTRAINT khoa_ngoaiNP FOREIGNKEY(MaPhong) REFERENCES PHONG_BAN(MaPhong) ON DELETE CASCADE ON UPDATE CASCADE) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 40
  41. Bảng KHACH_HANG CREATE TABLE KHACH_HANG (MaKH char(5) NOT NULL, TenCongTy nvarchar(100), DiaChi nvarchar(100), CONSTRAINT khoa_chinhK PRIMARY KEY (MaKH)) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 41
  42. Bảng NHA_CUNG_CAP CREATE TABLE NHA_CUNG_CAP (MaNCC char(5) NOT NULL, TenCongTy nvarchar(100), DiaChi nvarchar(100), CONSTRAINT Khoa_chinhC PRIMARY KEY (MaNCC)) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 42
  43. Bảng NHOM_HANG CREATE TABLE NHOM_HANG (MaNhom char(5) NOT NULL, TenNhom nvarchar(100), MaPhong char(5), CONSTRAINT Khoa_chinhNH PRIMARY KEY (MaNhom), CONSTRAINT duy_nhatNH UNIQUE (MaPhong), CONSTRAINT Khóa_ngoaiNH FOREIGN KEY (MaPhong) REFERENCES PHONG_BAN(MaPhong)) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 43
  44. Bảng MAT_HANG CREATE TABLE MAT_HANG (MaHang char(5) NOT NULL, TenHang nvarchar(100), NhomHang char(5), CONSTRAINT Khoa_chinhH PRIMARY KEY (MaHang), CONSTRAINT Khóa_ngoaiH FOREIGN KEY (NhomHang) REFERENCES NHOM_HANG(MaNhom) ON UPDATE CASCADE ON DELETE CASCADE) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 44
  45. Bảng DON_DAT_HANG CREATE TABLE DON_DAT_HANG (SoHieuDH char(7) NOT NULL, MaKH char(5), NgayDat datetime, CONSTRAINT Khoa_chinhD PRIMARY KEY (SoHieuDH), CONSTRAINT Khoa_ngoaiD FOREIGN KEY (MaKH) REFERENCES KHACH_HANG(MaKH) ON UPDATE CASCADE ON DELETE CASCADE) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 45
  46. Bảng CHI_TIET_DON_HANG CREATE TABLE CHI_TIET_DH (SoHieuDH char(7) NOT NULL, MaHang char(5) NOT NULL, SoLuong int, Gia real, CONSTRAINT Khoa_chinhCT PRIMARY KEY (SoHieuDH, MaHang), CONSTRAINT Khóa_ngoaiCD FOREIGN KEY (SoHieuDH) REFERENCES DON_DAT_HANG(SoHieuDH) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT Khóa_ngoaiCM FOREIGN KEY (MaHang) REFERENCES MAT_HANG(MaHang) ON UPDATE CASCADE ON DELETE CASCADE) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 46
  47. Bảng CUNG_UNG CREATE TABLE CUNG_UNG (MaNCC char(5) NOT NULL, MaHang char(5) NOT NULL, Gia real, CONSTRAINT khoa_chinhCC PRIMARY KEY (MaNCC, MaHang), CONSTRAINT khoa_ngoaiCC FOREIGN KEY (MaNCC) REFERENCES NHA_CUNG_CAP(MaNCC) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT khoa_ngoaiCH FOREIGN KEY (MaHang) REFERENCES MAT_HANG(MaHang) ON UPDATE CASCADE ON DELETE CASCADE) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 47
  48. Thay đổi ràng buộc trong bảng PHONG_BAN Tạo một ràng buộc sao cho cột NguoiQL nhận những giá trị không trung lặp. Tạo một ràng buộc khóa ngoài giữa cột NguoiQL và cột MaNV trong bảng NHAN_VIEN Tạo mối liên kết 1- 1 giữa hai bảng PHONG_BAN và NHAN_VIEN với các trường(cột) liên kết là NguoiQL và MaNV ALTER TABLE PHONG_BAN ADD CONSTRAINT duy_nhat UNIQUE(NguoiQL), CONSTRAINT Khoa_ngoai FOREIGN KEY (NguoiQL) REFERENCES NHAN_VIEN(MaNV) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 48
  49. Chương III 1. Đại số quan hệ và ngôn ngữ SQL 2. Lệnh định nghĩa dữ liệu 3. Lệnh thao tác dữ liệu 4. Lệnh truy vấn dữ liệu 4.1. Mệnh đề truy vấn tổng quát 4.2. Câu truy vấn lồng nhau Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 49
  50. 4.1. Mệnh đề truy vấn tổng quát SELECT[DISTINCT] |*| FROM | [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY | [ASC|DESC]] [UNION|INTERSECT|MINUS ] Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 50
  51. a. Mệnh đề SELECT Mệnh đề SELECT tương ứng với phép chiếu trong đại số quan hệ, được sử dụng để liệt kê các thuộc tính mong muốn Mênh đề SELECT cho phép:  Lựa chọn một/nhiều/tất cả (*) các thuộc tính Hiển thị tất cả thông tin của nhân viên trong bảng NHAN_VIEN SELECT * FROM NHAN_VIEN  Lấy các bộ giá trị không trùng nhau (DISTINCT) Cho biết họ của các nhân viên SELECT DISTINCT Ho FROM NHAN_VIEN Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 51
  52. Mệnh đề SELECT(t) Lấy kết quả từ các biểu thức số học (phép tính +,-,*,/) của hằng hoặc các thuộc tính  Cho biết danh mục mặt hàng và giá của mặt hàng đó (tính theo việt nam đồng): SELECT MAT_HANG.TenHang, CUNG_UNG.Gia *16.02 FROM MAT_HANG, CUNG_UNG WHERE MAT_HANG.MaHang = CUNG_UNG.MaHang  Kết quả của biểu thức số học là NULL nếu một giá trị đầu vào là NULL. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 52
  53. SQL sử dụng cách viết . để che dấu tính lập lờ trong trường hợp tên các thuộc tính trong các quan hệ trùng nhau. SQL sử dụng các phép nối logic NOT, AND OR. Toán hạng của các phép nối logic có thể là các biểu thức chứa các toán tử so sánh >, >=, <>, <, <=. Toán tử BETWEEN được dùng để chỉ các giá trị nằm giữa các khoảng giá trị. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 53
  54. b. Mệnh đề FROM Mệnh đề FROM tương ứng với phép tích Đề các của các quan hệ được xét. Ví dụ:Tìm giá mua vào của các mặt hàng thuộc loại máy tính xách tay: SELECT MAT_HANG.TenHang, CUNG_UNG.Gia*16.02 FROM CUNG_UNG, NHOM_HANG, MAT_HANG WHERE ((MAT_HANG.MaHang=CUNG_UNG.MaHang) AND (NHOM_HANG.MaNhom = MAT_HANG.MaNhom) AND (TenNhom='laptop')) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 54
  55. c. Mệnh đề WHERE Mệnh đề WHERE tương ứng chọn dựa trên các thuộc tính của các quan hệ xuất hiện sau FROM. Ví dụ: 1.Tìm các mặt hàng có số lượng đặt hàng lớn hơn 1 trăm SELECT MAT_HANG.TenHang, CHI_TIET_DH.SoLuong FROM CHI_TIET_DH, MAT_HANG WHERE (MAT_HANG.MaHang = CHI_TIET_DH.MaHang) AND (SoLuong>100) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 55
  56. Mệnh đề WHERE (t) Kết quả của biểu thức điều kiện là TRUE hoặc FALSE Nếu 1 trong những đầu vào của biểu thức điều kiện có giá trị NULL thì kết quả trả về là FALSE  WHERE[NOT]  WHERE[NOT]  WHERE[NOT] {AND|OR}[NOT]  WHERE[NOT] [NOT] LIKE Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 56
  57. Mệnh đề WHERE(t)  WHERE[NOT] [NOT] IN ({danh sách | câu truy vấn})  WHERE[NOT] EXISTS ( )  WHERE[NOT] {SOME|ANY|ALL (Câu truy vấn con)}  WHERE IS NULL Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 57
  58. Các phép đổi tên Mệnh đề AS cho phép đổi tên các bảng hoặc các cột  AS Đổi tên cột:  Các bảng trong mệnh đề FROM cùng tên  Sử dụng biểu thức số học trong câu lệnh SELECT  Thay đổi tên cột trong bảng kết quả  Ví dụ: Đổi tên cột dữ liệu hiển thị SELECT MAT_HANG.TenHang, CUNG_UNG.Gia*16.02 AS GiaSanPham FROM CUNG_UNG, NHOM_HANG, MAT_HANG WHERE ((MAT_HANG.MaHang=CUNG_UNG.MaHang) AND (NHOM_HANG.MaNhom = MAT_HANG.MaNhom) AND (TenNhom='laptop')) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 58
  59. Các phép đổi tên(t) Đổi tên bảng  So sánh các bộ (dòng) trong cùng một bảng  Ví dụ: Lấy tên của các hãng cung ứng có cung ứng các sản phẩm màn hình LCD + TV (mã MH013) rẻ hơn hãng hula (mã là CHULA). SELECT T.MaNCC FROM CUNG_UNG AS T, CUNG_UNG AS S WHERE T.MaHang='MH013' AND T.Gia < S.Gia AND S.MaNCC = 'CHULA’ AND S.MaHang = ‘MH013’ Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 59
  60. Các phép toán trên chuỗi Toán tử LIKE và NOT LIKE trong mệnh đề WHERE cho phép đối sánh giá trị của một cột với một mẫu khi tìm kiếm. Mẫu có thể được biểu diễn bởi các ký tự thay thế:  Ký tự (%): Tương ứng với một chuỗi con bất kỳ.  Ký tự (_): Tương ứng với một ký tự bất kỳ.  Nếu trong xâu mẫu có chứa các ký tự thay thế (%, _, \) thì đặt ký tự \ trước các ký tự đó. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 60
  61. Các phép toán trên chuỗi(t) Ví dụ: Mẫu  ‘% Nam An %’: Tương ứng với các xâu ký tự có chứa xâu con là ‘Nam An’.  ‘__%’: Tương ứng với các xâu ký tự có ít nhất hai ký tự.  ‘20\%%’: Tương ứng với các xâu ký tự bắt đầu bằng 20% Ví dụ: Tìm tên chính xác của nhân viên tiếp xúc phía khách hàng có chứa xâu ‘Hoa’.  SELECT NguoiLL FROM KHACH_HANG WHERE NguoiLL LIKE ‘%Hoa%’ Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 61
  62. Các hàm thư viện Hàm thư viện lấy một tập các giá trị làm đầu vào và trả kết quả là một giá trị đơn. Các hàm tính gộp:  Tính trung bình: AVG([DISTINCT] )  Tính tổng SUM([DISTINCT] )  Tính Min, Max Min(  Đếm COUNT([DISTINCT] |*) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 62
  63. Các hàm thư viện(t) Tất cả các hàm tính gộp (trừ Count(*) bỏ qua các giá trị đầu vào NULL COUNT() = 0 Ví dụ: Kiểm kê lượng hàng hoá theo loại mặt hàng còn trong kho SELECT MaNhom, Sum(TonKho) FROM MAT_HANG GROUP BY MaNhom Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 63
  64. d. Mệnh đề GROUP BY và HAVING Mệnh đề GROUP BY nhóm các bộ có cùng giá trị trên các thuộc tính nào đó Ví dụ: In danh sách loại hàng hóa cùng lượng hàng hoá theo loại mặt hàng còn trong kho với điều kiện lượng hàng lớn hơn 100. SELECT MaNhom, Sum(TonKho) FROM MAT_HANG GROUP BY MaNhom HAVING Sum(TonKho) > 100 Mệnh đề HAVING xuất hiện sau khi tạo nhóm, đưa ra điều kiện cho nhóm. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 64
  65. Mệnh đề GROUP BY và HAVING(t) Nếu WHERE và HAVING cùng ở trong một câu truy vấn thì:  Biểu thức điều kiện trong WHERE sẽ được thực hiện trước.  Các bộ thỏa mãn điều kiện trong WHERE sẽ được nhóm vào bởi GROUPBY.  Mệnh đề HAVING (nếu có) sẽ được áp dụng trên mỗi nhóm. Các nhóm không thỏa mãn mệnh đề HAVING sẽ bị xóa bỏ. Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 65
  66. e. Mệnh đề ORDER BY Mệnh đề ORDER BY cho phép trình bày kết quả câu truy vấn theo thứ tự.  Mặc định liệt kê theo thứ tự tăng Ví dụ: Đưa ra danh sách họ tên nhân viên với mức lương từ cao tới thấp. SELECT Ho, Ten, Luong FROM NHAN_VIEN ORDER BY Luong DESC Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 66
  67. f.Tìm kiếm nhờ các phép toán tập hợp Phép UNION tương ứng với phép hợp trong đại số quan hệ.  Ví dụ: Tìm các hãng là đối tác của siêu thị (khách hàng hoặc nhà cung cấp) (SELECT TenCongTy FROM KHACH_HANG) UNION (SELECT TenCongTy FROM NHA_CUNG_CAP) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 67
  68. Tìm kiếm nhờ các phép toán tập hợp(t) Phép INTERSECT tương ứng với phép giao trong đại số quan hệ  Ví dụ:Tìm các hãng vừa là khách hàng vừa là nhà cung cấp cho siêu thị. (SELECT TenCongTy FROM KHACH_HANG) INTERSECT (SELECT TenCongTy FROM NHA_CUNG_CAP) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 68
  69. Tìm kiếm nhờ phép toán tập hợp(t) Phép MINUS tương ứng với phép trừ trong đại số quan hệ  Ví dụ:Tìm các hãng chỉ là khách hàng (không là nhà cung cấp) của siêu thị. (SELECT MaKH FROM KHACH_HANG) MINUS (SELECT MaKH FROM CUNG_UNG Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 69
  70. 4.2. Các câu truy vấn lồng nhau Câu truy vấn lồng là một câu truy vấn có chứa câu truy vấn con. Câu truy vấn con là một biểu thức truy vấn (SELECT-FROM-WHERE) lồng trong một truy vấn khác như:  SELECT  INSERT INTO  UPDATE  DELETE  Câu truy vấn con khác Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 70
  71. Các câu truy vấn lồng nhau(t) Cú pháp tạo truy vấn con i) [NOT] IN ( ) ii) [NOT] EXISTS ( ) iii) {SOME|ANY|ALL} ( ) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 71
  72. Mệnh đề WHERE  WHERE[NOT] [NOT] IN ({danh sách | câu truy vấn con})  WHERE[NOT] EXISTS ( )  WHERE[NOT] {SOME|ANY|ALL}( ) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 72
  73. a. [NOT] IN ( ) Xác định kết quả trong truy vấn cha (không) phải thuộc tập hợp các bộ là kết quả của truy vấn con.
  74. Ví dụ  IN Ví dụ: Tìm những MaHang từ bảng CUNG_UNG mà mặt hàng đó có lượng lưu kho >0.  SELECT MaHang FROM CUNG_UNG WHERE MaHang IN (Select MaHang from HANG_HOA where TonKho >0)  NOT IN Ví dụ: Tìm những MaHang từ bảng CUNG_UNG mà mặt hàng đó có lượng lưu kho 0) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 74
  75. b. [NOT] EXISTS ( ) Kiểm tra quan hệ là rỗng hay không. Nếu kết quả trả về của truy vấn con chứa ít nhất một dòng thì điều kiện tồn tại thoả mãn.
  76. Ví dụ trong câu lệnh SELECT Hiển thị thông tin của những nhà cung cấp cung cấp ít nhất một sản phẩm SELECT * FROM NHA_CUNG_CAP WHERE EXISTS (select * from CUNG_UNG where NHA_CUNG_CAP.MaNCC = CUNG_UNG.MaNCC Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 76
  77. Hiển thị thông tin của những khách hàng đã có yêu cầu về hàng hoá SELECT * FROM KHACH_HANG WHERE EXISTS (Select MaKH from DON_DAT_HANG Where KHACH_HANG.MaKH= DONG_DAT_HANG.MaKH) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 77
  78. Ví dụ trong câu lệnh DELETE Xóa trong bảng Sự cung ứng (nếu có) tất cả những nhà cung cấp vừa là nhà cung cấp vừa là là khách hàng. DELETE * FROM NHA_CUNG_CAP WHERE EXISTS (select * from KHACH_HANG where KHACH_HANG.MaKH = NHA_CUNG_CAP.MaNCC) Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 78
  79. Ví dụ trong câu lệnh INSERT • Thêm một bộ giá trị vào bàng Customer và kiểm tra xem mã của khách hàng = 'CQTHG' đã tồn tại hay chưa. INSERT INTO KHACH_HANG (MaKH, TenCongty, ThanhPho ) SELECT DISTINCT 'CQTHG', 'Công ty Quốc tế Hoàng Gia', 'Hà Nội' FROM KHACH_HANG WHERE NOT EXISTS (select * from KHACH_HANG where MaKH = 'CQTHG') Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 79
  80. c. {SOME|ANY|ALL} ( ) So sánh kết quả với truy vấn con
  81. Ví dụ SOME/ANY  { =| >| SOME  NOT IN  =SOME  IN  Ví dụ: Hiện thông tin về việc cung ứng các mặt hàng có mã MH013 có giá nhỏ hơn giá của nhà cung cấp có mã CHULA cung ứng. SELECT * FROM CUNG_UNG WHERE MaHang='MH013' and Gia < SOME (select CUNG_UNG.Gia from CUNG_UNG where MaNCC = 'CHULA' ); Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 81
  82. Ví dụ ALL  { =| >| ALL  NOT IN  Ví dụ: Tìm nhà cung ứng cung ứng mặt hàng laptop với giá trung bình thấp nhất SELECT MaNCC FROM CUNG_UNG where MaNhom =12 GROUP BY MaNCC HAVING avg (Gia) < ALL (select avg(Gia) from CUNG_UNG inner join MAT_HANG on CUNG_UNG.MaHang = MAT_HANG.MaHang where MaNhom =12 group by MaNCC); Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 82
  83. Phụ lục Kiểu dữ liệu:  char(n): Xâu ký tự có độ dài cố định  varchar(n): Xâu ký tự có độ dài thay đổi với độ dài tối đa là n  int: Số nguyên  smallint: Số nguyên nhỏ  numeric (p,d): Số thập phân: phần thập phân p, prhần nguyên d  real, double precision: Số thập phân dấu phẩy động và số thập phân dấu phẩy động với độ chính xác kép  float(n): Số thập phân dấu phẩy động với độ chính xác ít nhất là n chữ số  date: Ngày gồm năm, tháng, ngày  time: Thời gian gồm giờ, phút, giây  NULL Bài giảng CSDL và Phần mềm ứng 03/11/2008 dụng 83