Giáo trình cơ sở dữ liệu - Chương 4

Chương 4. NGÔN NGỮ SQL GV: Trần Ngân Bình tnbinh@cit.ctu.edu.vn 4. 2 Nội Dung „ Giới thiệu ngôn ngữ SQL „ Truy vấn dữ liệu bằng lệnh SELECT „ Cập nhật dữ liệu (INSERT – Thêm, DELETE – Xóa, UPDATE – Sửa) 4. 3 Mục tiêu của SQL „ Một cách lý tưởng, ngôn ngữ CSDL phải cho phép người dùng: )Tạo CSDL và cấu trúc quan hệ )Thực hiện việc xen, sửa, xóa dữ liệu trên các quan hệ. )Thực hiện các câu truy vấn đơn giản và phức tạp. „ Phải thực hiện các công việc này với công sức bỏ ra là nhỏ nhất và cú pháp lệnh phải dễ học. „ Có thể sử dụng như nhau trên các HQTCSDL khác nhau (~ tuân theo chuẩn nào đó). 4. 4 Lịch sử phát triển của SQL „ Năm 1974, D. Chamberlin (IBM San Jose Lab) định nghĩa ngôn ngữ SEQUEL (Structured English Query Language). „ Một phiên bản sửa chữa, SEQUEL/2, đã được đưa ra vào năm 1976 nhưng sau đó đổi tên thành SQL vì lý do pháp lý. „ Sau đó IBM tạo ra một bản mẫu HQTCSDL gọi là System R, dựa trên SEQUEL/2. „ Tuy nhiên gốc rễ của SQL là từ ngôn ngữ SQUARE (Specifying Queries as Relational Expressions), là ngôn ngữ đã có trước dự án System R. „ Vào cuối 70s, ORACLE xuất hiện và được xem là HQTCSDL thương mại đầu tiên dựa trên SQL. 4. 5 Lịch sử phát triển của SQL „ Năm 1987, ANSI và ISO công bố chuẩn đầu tiên cho SQL. „ Năm 1989, ISO công bố thêm phần phụ lục định nghĩa một ‘Nét cải tiến cho toàn vẹn’. „ Năm 1992, phiên bản chỉnh sửa đầu tiên của chuẩn này có mặt, mang tên SQL2 hay SQL-92. „ Năm 1999, SQL:1999 được phát hành với các chuẩn hỗ trợ cho quản lý dữ liệu hướng đối tượng. „ Vào cuối năm 2003, SQL:2003 ra đời. 4. 6 Ngôn Ngữ SQL „ SQL là một ngôn ngữ phi thủ tục với hai thành phần chính: )DDL (Data Definition Language) dùng để định nghĩa cấu trúc của CSDL. ANSI chia DDL gồm 2 phần: DDL gồm các lệnh để định nghĩa cấu trúc của CSDL: CREATE TABLE, CREATE VIEW, ALTER TABLE,… DCL (Data Control Language) gồm các lệnh để điều khiển quyền truy cập trên dữ liệu: GRANT, REVOKE,… )DML (Data Manipulation Language) để truy xuất và cập nhật dữ liệu: INSERT, UPDATE, DELETE, SELECT, … 4. 7 Câu lệnh SQL „ Hầu hết các phần trong câu lệnh SQL là không phân biệt chữ hoa chữ thường, trừ các ký tự trong chuỗi dữ liệu. „ Tuy câu lệnh SQL có hình thức tự do nhưng để dễ đọc, ta nên: ) Viết mỗi mệnh đề của lệnh trên một dòng riêng ) Viết bắt đầu một mệnh đề thẳng hàng với các mệnh đề khác ) Nếu một mệnh đề có nhiều vế thì nên viết mỗi vế trên một dòng và thục vào trong mệnh đề đó. „ Ví dụ: SELECT Hten_nv, Cviec FROM Nhan_vien WHERE Phai = ‘nam’ AND (Ma_phong=30 OR Ma_phong=40) „ Câu lệnh SQL gồm 2 thành phần: Từ khóa và từ do người dùng định nghĩa (tên bảng, tên cột,...) 4. 8 Cú pháp câu lệnh SQL „ Cú pháp của lệnh SQL được giới thiệu theo dạng mở rộng của ký hiệu BNF: )Chữ hoa đại diện cho từ khóa. )Chữ thường đại diện cho các từ của người dùng định nghĩa )Dấu | chỉ sự lựa chọn. )Dấu { chỉ phần tử bắt buộc phải có. )Dấu [ chỉ phần tử tùy chọn (không bắt buộc). )Dấu ... chỉ thành phần có thể lặp lại từ 0 đến nhiều lần. 4. 9 CSDL ví dụ 1. PHONG (MA_PHONG, TEN_PHONG, TRUONG_PHONG ) Mỗi phòng có một mã duy nhất, một tên phòng, trưởng phòng là mã nhân viên của trưởng phòng. 2. NHANVIEN (MA_NV, HTEN_NV, PHAI, CVIEC, LUONG, PHU_CAP, MA_PHONG) Nhân viên có một mã duy nhất, một họ tên, phái, công việc, lương, phụ cấp và thuộc một phòng nào đó. 3. TĐO_NN (MA_NV, NGOAINGU, BANG_CAP) Một nhân viên có thể biết nhiều ngoại ngữ, mỗi ngoại ngữ có thể có các bằng cấp khác nhau. 4. 10 Truy vấn đơn giản „ Truy vấn DL từ một bảng, có thể định thứ tự xuất hiện các cột SELECT [ DISTINCT | ALL] { * | [, [,...] ] } FROM )DISTINCT: Chỉ hiển thị những dòng phân biệt. )ALL: hiển thị tất cả (chế độ mặc định) )Lưu ý: Khác với phép chiếu của ĐSQH, các dòng trùng nhau của bảng kết quả không tự động bị loại bỏ. )Dấu hoa thị (*) thay thế tất cả các cột trong bảng. „ Thứ tự các mệnh đề không được thay đổi. „ SELECT và FROM là hai mệnh đề duy nhất bắt buộc phải có. 4. 11 Truy vấn đơn giản – ví dụ „ Hiển thị toàn bộ nội dung của bảng NHAN_VIEN: SELECT * FROM Nhan_vien „ Hiển thị danh sách gồm họ tên nhân viên và phái: SELECT HTen_nv, Phai FROM Nhan_vien „ Hiển thị danh sách các mức lương có thể có của nhân viên: SELECT DISTINCT Luong FROM Nhan_vien 4. 12 Sắp xếp kết quả „ Sắp xếp kết quả theo một hay nhiều cột: )Sử dụng mệnh đề ORDER BY ở cuối lệnh SELECT: ORDER BY [ASC| DESC] [, [ASC| DESC],…] „ ASC là chế độ sắp xếp mặc định „ Ví dụ: Sắp xếp kết quả theo mã phòng tăng dần, và lương giảm dần. SELECT * FROM Nhan_vien ORDER BY Ma_phong, Luong DESC 4. 13 Chọn các dòng trong bảng (1) „ Sử dụng mệnh đề WHERE sau MĐ FROM để chọn các dòng thỏa điều kiện WHERE [ AND | OR [...] ] „ Dạng ĐK 1: So sánh giá trị thuộc tính với 1 giá trị cụ thể: so sánh ) Ví dụ: Hiển thị tất cả các thông tin của nhân viên Lê Quỳnh Như SELECT * FROM Nhan_vien WHERE Hten_nv = ‘Lê Quỳnh Như’ ) Ví dụ: Hiển thị tên và tên công việc của các nhân viên nam đang làm việc trong các phòng 30, 40 SELECT Hten_nv, CVIEC FROM Nhan_vien WHERE Phai = ‘Nam’ AND (Ma_phong=30 OR Ma_phong=40) 4. 14 Chọn các dòng trong bảng (2) „ Dạng ĐK 2: So sánh giá trị của các thuộc tính với nhau: so sánh )Ví dụ: Tìm những nhân viên có lương bằng hai lần phụ cấp: SELECT Hten_nv, Luong, Phu_cap FROM Nhan_vien WHERE Luong = Phu_cap * 2 „ Dạng ĐK 3: So sánh giá trị thuộc tính với hằng có kiểu tương thích: so sánh )Ví dụ: Hiển thị họ tên các NV nam có công việc là thư ký SELECT Hten_nv FROM Nhan_vien WHERE Phai = 'Nam' AND UPPER(cviec) = ‘THU KY’ 4. 15 Chọn các dòng trong bảng (3) „ Dạng ĐK 4: So sánh cột với một tập gồm nhiều giá trị: [NOT] IN () )Ví dụ:Hiển thị họ tên các NV làm việc trong các phòng 10, 30 và 50. SELECT Hten_nv FROM Nhan_vien WHERE Ma_phong IN ( 10, 30, 50 ) „ Dạng ĐK 5: Tìm kiếm theo phạm vi: [NOT] BETWEEN AND )Ví dụ: Hiển thị họ tên các NV có mức lương từ 3500 đến 4500 SELECT Hten_nv, Luong FROM Nhan_vien WHERE Luong BETWEEN 3500 AND 4500 4. 16 Các dạng điều kiện chọn (4) „ Dạng ĐK 6: Tìm kiếm theo mẫu dạng chuỗi: [NOT] LIKE _ : đại diện cho một ký tự bất kỳ % : đại diện cho một chuỗi ký tự bất kỳ )Ví dụ: Hiển họ thị tên của các NV có tên lót là ‘Văn’ SELECT Hten_nv, Ma_phong FROM Nhan_vien WHERE Hten_nv LIKE ‘% Van %’ )Ví dụ: Hiển thị các NV có tên vần ‘ao’ như Bao, Hao, Thao,... SELECT * FROM Nhan_vien WHERE Hten_nv LIKE ‘% %_ao’ „ Dạng ĐK 7: Tìm kiếm theo trị trống NULL IS [NOT] NULL )Ví dụ: Hiển thị các NV Không được hưởng phụ cấp SELECT * FROM Nhan_vien Where Phu_cap IS NULL 4. 17 Các hàm kết tập „ Các hàm kết tập (Aggregate Functions) còn được gọi là hàm cột (Field Functions): )AVG () )SUM () )MIN (<BT số, chuỗi hay ngày có bao hàm cột kiểu tương ứng>) )MAX (<BT số, chuỗi hay ngày có bao hàm cột kiểu tương ứng>) )COUNT ( | *) 4. 18 Các hàm kết tập – cách dùng „ Hàm kết tập được dùng trong mệnh đề SELECT như sau: SELECT tên-hàm () [AS <Tên cột mới>] [,tên-hàm () [,...]] FROM tên bảng „ Lưu ý: Trong mệnh đề select của dạng này, các tên cột phải được đặt trong hàm kết tập. „ Từ khóa AS cho phép đặt lại tên cột cho các cột kết quả )Ví dụ: Có bao nhiêu NV trong hồ sơ nhân viên SELECT COUNT(*) AS Tong_So_NV FROM Nhan_vien 4. 19 Các hàm kết tập – Ví dụ „ Cho biết mức lương cao nhất, thấp nhất và trung bình SELECT MAX( Luong), MIN( Luong), AVG( Luong) FROM Nhan_vien „ Tính tổng lương phải trả cho phòng 40 SELECT SUM (Luong) FROM Nhan_vien WHERE Ma_phong=40 „ Cho biết tổng số thư ký nữ và lương trung bình của họ SELECT COUNT(*), AVG (Luong) FROM Nhan_vien WHERE Phai = 'Nu' AND Cviec = ‘Thu Ky’ „ Cho biết phụ cấp thấp nhất của các công việc quản lý SELECT MIN (Phu_cap) As PCAP_THAPNHT FROM Nhan_vien WHERE Cviec LIKE ‘Quan Ly %‘ 4. 20 Tính toán với SQL „ Các toán tử này được dùng với dữ liệu loại số và bao gồm toán tử cộng (+), trừ (-), nhân (*), chia (/). „ Các toán tử này chỉ có thể được dùng trong các mệnh đề: SELECT, HAVING và WHERE. „ Ví dụ: Hiển thị họ tên, lương, phụ cấp và thu nhập hàng năm của tất cả nhân viên, sắp xếp theo thu nhập trong năm giảm dần. SELECT Hten_nv, Luong, Phu_cap, (Luong + Phu_cap)*12 As Thu_nhap FROM Nhan_vien ORDER BY 4 DESC „ Ví dụ: Hiển thị tất cả nhân viên có phụ cấp nhiều hơn 15% mức lương. SELECT Hten_nv, Luong, Phu_cap FROM Nhan_vien WHERE Phu_cap > 0.15 *Luong 4. 21 Truy vấn con (1) „ Trong trường hợp điều kiện chọn ở mệnh đềWHERE cần truy cập thông tin ở các bảng khác với bảng đang truy vấn để kiểm tra điều kiện „ Ta có thể sử dụng một câu select khác lồng trong điều kiện ở mệnh đềWHERE. Select này được gọi là SELECT CON hay truy vấn con 4. 22 Truy vấn con (1) „ Các truy vấn con sẽ nằm trong mệnh đềWHERE của truy vấn chính. SELECT FROM WHERE ( SELECT FROM [WHERE ] ) [AND | OR ( SELECT FROM [WHERE ] ) [ ... ] ] Truy vấn con 1 Truy vấn con 2 Truy vấn chính (truy vấn cha) 4. 23 Các dạng điều kiện chứa Select con (1) „ Dạng 1: (): ĐK đúng khi giá trị của cột so sánh đúng với giá trị trả về từ select con ) Ví dụ: Hiển thị họ tên nhân viên có lương cao nhất SELECT Hten_nv FROM Nhan_vien WHERE Luong = ( SELECT MAX (Luong) FROM Nhan_vien ) „ Dạng 2: ALL (): ĐK đúng khi giá trị của cột so sánh đúng với tất cả các giá trị trả về từ select con. ) Ví dụ: Hiển thị họ tên nhân viên có lương cao hơn tất cả các nhân viên của phòng 30 SELECT Hten_nv FROM Nhan_vien WHERE Luong > ALL ( SELECT Luong FROM Nhan_vien WHERE Ma_phong =30 ) 4. 24 Các dạng điều kiện chứa Select con (2) „ Dạng 3: ANY|SOME (): ĐK đúng khi giá trị của cột so sánh đúng với bất kỳ một giá trị nào trả về từ select con. ) Ví dụ: Hiển thị họ tên trưởng phòng của tất cả các phòng SELECT HTEN_NV FROM NHAN_VIEN WHERE MA_NV = ANY ( SELECT TRUONG_PHONG FROM PHONG ) „ Dạng 4: [NOT] IN (): ĐK đúng khi giá trị của cột nằm trong tập hợp các giá trị trả về của select con. ) Ví dụ: Hiển thị họ tên trưởng phòng của tất cả các phòng SELECT HTEN_NV FROM NHAN_VIEN WHERE MA_NV IN ( SELECT TRUONG_PHONG FROM PHONG ) 4. 25 Các dạng điều kiện chứa Select con (3) „ Dạng 5: [NOT] EXISTS (): Đk đúng khi kết quả trả về của select con khác rỗng. ) Ví dụ: Hiển thị họ tên những nhân viên có công việc và lương bằng với 1 nhân viên khác. SELECT HTEN_NV FROM NHAN_VIEN AS A WHERE EXISTS ( SELECT * FROM NHAN_VIEN AS B WHERE A.Cviec = B.Cviec AND A.Luong = B.Luong AND A.Ma_NV B. Ma_NV) 4. 26 Các quy luật của truy vấn con „ Mệnh đề ORDER BY không được sử dụng trong truy vấn con (mặc dù chúng có thể được dùng trong truy vấn cha). „ Mệnh đề SELECT trong truy vấn con chỉ được bao gồm một cột duy hoặc 1 biểu thức duy nhất, ngoại trừ truy vấn con sử dụng EXISTS. „ Truy vấn con có thể truy cập các cột của các bảng ở truy vấn cha bằng cách sử dụng bí danh Select con trả về: Một cột Nhiều cột Một dòng Dạng 1 Nhiều dòng Dạng 2, 3, 4 Dạng 5 4. 27 Gom nhóm các dòng „ Đặc tính gom nhóm cho phép chúng ta thực hiện các chức năng trên một nhóm các dòng như là một dòng riêng biệt. „ Sử dụng mệnh đề GROUP BY sau mệnh đề FROM hoặc WHERE trong lệnh select SELECT , )> FROM [ WHERE ] GROUP BY [, [,...]] 4. 28 Gom nhóm các dòng „ Mệnh đề SELECT and GROUP BY liên quan chặt chẽ với nhau: mỗi mục trong danh sách SELECT chỉ được có một-giá-trị cho một nhóm. „ Vì vậy, mệnh đề SELECT chỉ có thể chứa: )Các cột phân nhóm )Các hàm kết tập )Các hằng )Các biểu thức kết hợp các loại trên. „ Ví dụ: Hiển thị mức lương trung bình của từng phòng. SELECT Ma_phong, AVG (Luong) FROM Nhan_vien GROUP BY Ma_phong 4. 29 Điều kiện trên nhóm „ Sử dụng mệnh đề HAVING theo sau mệnh đề GROUP BY để lọc ra các nhóm theo điều kiện sau khi đã phân nhóm: HAVING „ Ví dụ: Tìm các phòng có mức lương trung bình lớn hơn 3000. SELECT Ma_phong, AVG (Luong) FROM Nhan_vien GROUP BY Ma_phong HAVING AVG (Luong) > 3000 „ Ví dụ: Hãy hiển thị tên ngoại ngữ có số lượng người có bằng C là trên 20 người. SELECT Ngoai_ngu, COUNT (Ma_nv) FROM Tđo_NN WHERE Bang_cap = ‘C’ GROUP BY Ngoai_ngu HAVING COUNT(Ma_nv) > 20 4. 30 Thứ tự thực hiện các mệnh đề SELECT [DISTINCT | ALL] {* | [biểu thức cột [AS tên mới]] [,...] } FROM Tên bảng [WHERE điều kiện] [GROUP BY danh sách cột] [HAVING điều kiện nhóm] [ORDER BY danh sách cột] „ FROM Lấy các bảng cần sử dụng. „ WHERE Chọn các dòng thỏa điều kiện. „ GROUP BY Tạo thành các nhóm dòng với cùng giá trị cột. „ HAVING Chọn các nhóm thỏa điều kiện nhóm. „ SELECT Chọn lấy các cột cần xuất ra „ ORDER BY Sắp xếp thứ tự các dòng để xuất ra theo yêu cầu. 4. 31 Truy vấn trên nhiều bảng „ SELECT có chứa truy vấn con có thể kiểm tra điều kiện trên bảng khác, nhưng các cột xuất ra phải từ một bảng. „ Nếu muốn xuất ra các cột từ nhiều bảng thì phải thực hiện phép kết nối (join). „ Để thực hiện kết nối, liệt kê các bảng trong MĐ FROM cách nhau bằng dấu phẩy. Thông thường dùng MĐ WHERE để mô tả các cột kết nối. „ Ví dụ: Tìm tên của phòng mà Lê Quỳnh Như đang làm việc: SELECT NHAN_VIEN.MA_PHONG,TENPHONG FROM NHAN_VIEN, PHONG WHERE NHAN_VIEN.MA_PHONG = PHONG.MA_PHONG AND HTEN_NV = ‘Lê Quỳnh Như’ „ Lưu ý: Giữa hai bảng có thể có số điều kiện kết nối >= 1. Điều kiện kết nốiĐiều kiện chọn 4. 32 Đặt bí danh cho các bảng „ Có thể đặt bí danh cho các bảng trong mệnh đề FROM. „ Nếu đã đặt bí danh cho bảng, thì phải sử dụng nó thay cho tên bảng trong toàn bộ lệnh select. )Ví dụ: Cho biết họ tên nhân viên và tên phòng tương ứng mà nhân viên đó đang làm việc. SELECT HTEN_NV, TEN_PHONG FROM NHAN_VIEN a, PHONG b WHERE a.MA_PHONG = b.MA_PHONG )Ví dụ: Cho biết họ tên và bằng cấp tương ứng của những nhân viên có ngoại ngữ Anh văn. SELECT HTEN_NV, BANG_CAP FROM NHAN_VIEN a, TĐO_NN b WHERE a.MA_NV = b.MA_NV AND Ngoai_Ngu = ‘Anh Van’ 4. 33 Các cấu trúc kết nối khác „ SQL cung cấp một số cách khác để mô tả phép kết nối. )FROM NHAN_VIEN a JOIN PHONG b ON a.MA_PHONG = b.MA_PHONG )FROM NHAN_VIEN JOIN PHONG USING MA_PHONG )FROM NHAN_VIEN NATURAL JOIN PHONG „ Trong mỗi trường hợp, FROM thay thế FROM and WHERE. Tuy nhiên, trước hết phải tạo bảng với các cột giống nhau. 4. 34 Kết nối Ngoại – Outer Join (1) „ Nếu 1 dòng của 1 bảng được kết nối không khớp với dòng nào bên bảng kia, thì dòng đó sẽ biến mất khỏi bảng kết quả. „ Kết nối ngoài cho phép giữ lại những dòng không thỏa điều kiện kết nối. „ Có 3 loại kết nối: )FROM LEFT JOIN ON )FROM RIGHT JOIN ON )FROM FULL JOIN ON 4. 35 Kết nối Ngoại – Outer Join (2) „ Hãy liệt kê họ tên NV, trình độ ngoại ngữ, kể cả những NV không biết ngoại ngữ nào. SELECT Hten_NV, Ngoai_ngu, Bang_cap FROM Nhan_vien a LEFT JOIN Tđo_NN b ON a.Ma_nv = b.Ma_nv ) Hoặc: SELECT Hten_NV, Ngoai_ngu, Bang_cap FROM Tđo_NN b RIGHT JOIN Nhan_vien a ON a.Ma_nv = b.Ma_nv „ Hãy liệt kê họ tên thư ký của từng phòng, kể cả những phòng không có thư ký, và những thư ký chưa phân bổ cho phòng nào. SELECT Hten_NV, Ten_Phong FROM Nhan_vien a FULL JOIN Phong a ON a.Ma_phong = b.Ma_phong WHERE Cviec = ‘Thu ky’ 4. 36 Cách thực hiện phép kết nối „ Thủ tục để sinh ra kết quả cho phép kết nối như sau: 1. Tạo bảng kết quả tích DesCart của các bảng liệt kê trong MĐ FROM. 2. Nếu có MĐ WHERE, thì áp dụng điều kiện tìm kiếm trên từng dòng của bảng tích, giữ lại những dòng thỏa điều kiện. 3. Với mỗi dòng còn lại, tính giá trị cho mỗi mục trong danh sách SELECT để tạo ra 1 dòng trong bảng kết quả. 4. Nếu có DISTINCT, thì xóa những dòng trùng lại trong KQ. 5. Nếu có ORDER BY, thì sắp xếp như yêu cầu. „ SQL cung cấp 1 hình thức đặc biệt cho phép tích Descart: SELECT [DISTINCT | ALL] {* | DS cột} FROM CROSS JOIN 4. 37 TK có các phép toán tập hợp „ Phép hợp (UNION), Giao (INTERSECT), Trừ (Difference hay Except)) „ Cũng như ĐSQH, các phép toán này cũng đòi hỏi sự tương thích giữa hai bảng. Hai thuộc tính tương thích nếu chúng có cùng kiểu và độ rộng. „ Định dạng chung của phép toán tử tập hợp là: [ALL] [CORRESPONDING [BY {cột1 [, ...]}]] „ Nếu có CORRESPONDING BY, toán tử tập hợp chỉ thực hiện trên những cột được liệt kê. „ Nếu có CORRESPONDING nhưng không có BY thì phép toán chỉ thực hiện trên những cột chung. „ Nếu có ALL, thì kết quả có thể chứa dòng trùng nhau. 4. 38 Hợp hai tập kết quả truy vấn „ Ví dụ: Tìm tập hợp gồm Mã NV của các trưởng phòng và các nhân viên có trình độ C Pháp văn. (SELECT Truong_phong FROM Nhan_vien) UNION (SELECT Ma_nv FROM Tdo_NV WHERE Ngoai_ngu = ‘Phap van’ AND Bang_cap = ‘C’) „ Ví dụ: Tìm tập hợp gồm Mã NV của các thư ký và các nhân viên có trình độ B Anh văn. (SELECT * FROM Nhan_vien WHERE Cviec = ‘Thu ky’) UNION CORRESPONDING By Ma_nv (SELECT * FROM Tdo_NV WHERE Ngoai_ngu = ‘Anh van’ AND Bang_cap = ‘B’) 4. 39 Giao hai tập kết quả truy vấn (1) „ Ví dụ: Tìm các thư ký có trình độ Anh văn là B. (SELECT * FROM Nhan_vien WHERE Cviec = ‘Thu ky’) INTERSECT CORRSPONDING BY Ma_nv (SELECT * FROM Tđo_NN WHERE Ngoai_ngu = ‘Anh van’ AND Bang_cap = ‘B’) )Hoặc: SELECT * FROM Nhan_vien a, Tđo_NN b WHERE a.Ma_nv = b.Ma_nv AND Cviec = ‘Thu ky’ AND Ngoai_ngu = ‘Anh van’ AND Bang_cap = ‘B’ Khả năng viết một câu truy vấn với nhiều dạng tương đương minh họa cho thấy một trong những điểm bất lợi của SQL 4. 40 Giao hai tập kết quả truy vấn (2) „ Trả lời các câu hỏi dạng giao bằng select lồng nhau sử dụng toán tử: ) IN cho phép giao trên một cột )EXISTS cho phép giao trên nhiều cột (hoặc mở một bảng ở nhiều vùng). „ Ví dụ: SELECT * FROM Nhan_vien WHERE Cviec = ‘Thu ky’ AND Ma_nv IN (SELECT Ma_nv FROM Nhan_vien WHERE Ngoai_ngu = ‘Anh van’ AND Bang_cap = ‘B’) Hoặc: SELECT * FROM Nhan_vien a WHERE Cviec = ‘Thu ky’ AND EXISTS (SELECT * FROM Nhan_vien b WHERE a.Ma_nv = b.Ma_nv AND Ngoai_ngu = ‘Anh van’ AND Bang_cap = ‘B’) 4. 41 Giao hai tập kết quả truy vấn (3) „ Ví dụ: Tìm thông tin về các phòng có cả thư ký và quản lý văn thư: SELECT a.* FROM PHONG a, NHAN_VIEN b WHERE a. MA_PHONG = b.MA_PHONG AND CVIEC = ‘Quan Ly Van Thu’ AND MA_PHONG IN (SELECT MA_PHONG FROM NHAN_VIEN WHERE CVIEC = ‘Thu Ky’) Đk kết nối bảng Điều kiện chọn Đk chứa select con Nested Subquery 4. 42 Giao hai tập kết quả truy vấn (4) „ Tìm công việc và mã phòng của các phòng có cả nhân viên nam và nữ cùng đảm trách một công việc SELECT Cviec, Ma_phong FROM Nhan_vien a WHERE Phai = 'Nam' AND EXISTS (SELECT * FROM Nhan_vien b WHERE Phai = 'Nu' AND a.Cviec= b.Cviec AND a.Ma_phong = b.Ma_phong) correlated subquery „ Hoặc sử dụng kỹ thuật mở bảng nhiều lần để thực hiện các câu trên. SELECT Cviec, Ma_phong FROM Nhan_vien a, Nhan_vien b WHERE a.Phai = 'Nam' AND b.Phai = 'Nu' AND a.Cviec= b.Cviec AND a.Ma_phong = b.Ma_phong) Câu này phải dùng EXISTS 4. 43 Trừ hai tập kết quả truy vấn (1) „ Ví dụ: Tìm những phòng không có thư ký (SELECT * FROM Phong) EXCEPT CORRSPONDING BY Ma_phong (SELECT * FROM Nhan_vien WHERE Cviec = ‘Thu ky’) )Hoặc: (SELECT Ma_phong FROM Phong) EXCEPT (SELECT Ma_phong FROM Nhan_vien WHERE Cviec = ‘Thu ky’) 4. 44 Trừ hai tập kết quả truy vấn (2) „ Có thể trả lời các câu hỏi dạng giao bằng select lồng nhau sử dụng toán tử: )NOT IN cho phép trừ trên một cột )NOT EXISTS cho phép trừ trên nhiều cột. „ Ví dụ: (SELECT * FROM Phong WHERE Ma_phong NOT IN (SELECT Ma_phong FROM Nhan_vien WHERE Cviec = ‘Thu ky’) 4. 45 Cập nhật CSDL „Các lệnh cập nhật CSDL của SQL: )Thêm dòng (INSERT) )Xóa dòng (DELETE) )Sửa dữ liệu trong các dòng (UPDATE) 4. 46 Thêm Dòng vào Bảng (1) „ Dạng 1: cho phép xen một dòng vào bảng đã có: INSERT INTO [( [, [,...]])] VALUES ( [, [,...]]) )Tên bảng ở đây có thể là bảng hoặc khung nhìn có thể cập nhật (updatable view) )Nếu không có danh sách cột, thì SQL ngầm hiểu là tất cả các cột của bảng với trật tự ban đầu của nó. )Nếu có mô tả DS cột, thì những cột nào không có mặt thì phải là cột có thể mang trị NULL, trừ khi nó đã được định nghĩa trị mặc định lúc tạo bảng. )DS biểu thức và DS cột phải có cùng số phần tử và tương thích về kiểu theo từng cặp một. 4. 47 Thêm Dòng vào Bảng (2) „ Ví dụ: Thêm một dòng dữ liệu mới vào bảng NHAN_VIEN INSERT INTO NHAN_VIEN (MA_NV, HTEN_NV, PHAI , MA_PHONG, CVIEC) VALUES ('123', 'Nguyễn Văn An', 'Nam', 3,'Quan Ly Kho') „ Nếu dòng thêm vào có đủ giá trị cho mỗi cột dữ liệu thì: INSERT INTO NHAN_VIEN VALUES ('124', 'Nguyễn Thị Hoa', 'Nu', 'Thu Ky', 2000, 100, 5) Chỉ có kiểu số là không đặt trong dấu nháy ‘’ 4. 48 Thêm Dòng Vào Bảng (3) „ Dạng 2: INSERT kết hợp với lệnh SELECT cho phép chép nhiều dòng từ một hoặc nhiều bảng vào một bảng khác. INSERT INTO [( [, [,...]])] „ Ví dụ: Chép các nhân viên là trưởng phòng từ bảng NHAN_VIEN vào bảng THANG_CAP: INSERT INTO Thang_cap SELECT Ma_nv, Hten_nv, Ma_phong FROM Nhan_vien WHERE Cviec = ‘Truong Phong’ „ Tất cả các nhân viên đều có trình độ B Anh Văn. Hãy thêm dữ liệu vào bảng TDO_NN INSERT INTO Tdo_nn SELECT Ma_nv, ‘AV’ , ‘B’ FROM Nhan_vien 4. 49 Sửa Dữ Liệu trong Bảng „ Lệnh UPDATE cho phép sửa đổi nội dung của 1 hay nhiều cột trên một hay nhiều dòng của một bảng. UPDATE SET = [, = [,..]] [WHERE ] )Tên bảng ở đây có thể là bảng hoặc khung nhìn có thể cập nhật (updatable view) )Chỉ những cột được đề cập thì mới cập nhật )Nếu không có WHERE thì sẽ cập nhật tất cả các dòng. )Giá trị của biểu thức phải có kiểu tương thích với kiểu của cột 4. 50 Sửa Dữ Liệu trong Bảng „ Ví dụ: Tăng lương cho tất cả các nhân viên thư ký thêm 500 UPDATE NHAN_VIEN SET LUONG = LUONG + 500 WHERE CVIEC = ‘Thu Ky’ „ Ví dụ: Chuyển nhân viên Lê Quỳnh Như sang phòng 15 với công việc là Trưởng Phòng UPDATE NHAN_VIEN SET MA_PHONG = 15, CVIEC = ‘Truong Phong’ WHERE HTEN_NV = ‘Lê Quỳnh Như’ 4. 51 Xóa Dòng trong Bảng „ Lệnh DELETE xóa bỏ một hay nhiều dòng trong một bảng. DELETE [WHERE ] )Tên bảng ở đây có thể là bảng hoặc khung nhìn có thể cập nhật (updatable view) )Nếu không có WHERE thì sẽ xóa tất cả các dòng có trong bảng. Tuy nhiên, bảng sẽ không bị xóa. )Phải hết sức cẩn thận khi dùng lệnh này. 4. 52 Xóa Dòng trong Bảng „ Ví dụ: Nhân viên Trần Hồng đã nghỉ việc. Huỷ bỏ thông tin về anh ta trong bảng NHAN_VIEN: DELETE NHAN_VIEN WHERE HTEN_NV = ‘Trần Hồng’ „ Ví dụ: Công ty có quyết định nhập phòng 11 vào phòng 10, hãy thực hiện các thay đổi phù hợp: UPDATE NHAN_VIEN SET MAPHONG = 10 WHERE MA_PHONG = 11 DELETE PHONG WHERE MA_PHONG = 11