Bài giảng Cơ sở dữ liệu quan hệ và SQL: Chương 5

Chương 5 TRUY VẤN CON

- Lý thuyết: 3 tiết

- Thực hành: 2 tiết

5 - 1

Chương 5. Truy vấn con

Truy vấn con

5.1 Định nghĩa

5.2 Truy vấn con hàng đơn

5.3 Truy vấn con nhiều hàng

5.4 Truy vấn con trên nhiều cột

5.5 Truy vấn con tương quan

5.6 Truy vấn con phân cấp

5 - 2

Chương 5. Truy vấn con

Truy vấn con

5.1 Định nghĩa

câu lệnh SELECT được lồng vào trong một mệnh đề của câu lệnh khác.

SELECT select_list

FROM table

WHERE expr operator (SELECT sel_list FROM table);

5 - 3

Chương 5. Truy vấn con

Truy vấn con

5.1 Định nghĩa

 Mục đích

 Cung cấp giá trị cho WHERE, HAVING và START WITH

 Định nghĩa một tập hợp các hàng để chèn vào bảng trong câu lệnh INSERT hay CREATE TABLE.

 Định nghĩa một hay nhiều giá trị để gán cho một hàng đã tồn tại trong câu lệnh UPDATE.

5 - 4

Chương 5. Truy vấn con

Truy vấn con

5.1 Định nghĩa

 Một số quy tắc

 trong cặp dấu ngoặc đơn ().

 bên phải điều kiện so sánh.

 Mệnh đề ORDER BY trong câu lệnh truy vấn con là không cần thiết trừ khi chúng ta thực hiện phân tích top-n.

 Sử dụng các phép toán.

5 - 5

Chương 5. Truy vấn con

Truy vấn con

5.1 Định nghĩa

 Trong mệnh đề WHERE

Ví dụ

Tìm những nhân viên làm cùng công việc với BLAKE

SELECT Fname, JobId FROM Emp

WHERE JobId =

(SELECT JobId FROM Emp WHERE Fname = ‘BLAKE’);

Fname JobId

5 - 6

Chương 5. Truy vấn con

BLAKE MANAGER CLARK MANAGER

Truy vấn con

5.1 Định nghĩa

 Trong mệnh đề HAVING

Ví dụ

Tìm phòng có mức lương tb > mức lương tb của phòng

30.

DEPTNO AVG(SAL) 10 20 2916.66667 2175

SELECT DeptId, AVG(Sal) FROM Emp GROUP BY DeptId HAVING AVG(Sal) > (SELECT AVG(Sal)

5 - 7

Chương 5. Truy vấn con

FROM Emp WHERE DeptId =30);

Truy vấn con

5.2 Truy vấn con hàng đơn

 chỉ trả về một hàng từ câu lệnh SELECT con.

Ví dụ

Tìm nhân viên có mã công việc giống với nhân viên số

141.

SELECT Ename, job_id FROM Emp WHERE job_id = (SELECT job_id

5 - 8

Chương 5. Truy vấn con

FROM Emp WHERE emp_id = 141);

Truy vấn con

5.2 Truy vấn con hàng đơn

 Chú ý: Một số lỗi thường gặp

 Câu lệnh con trả về nhiều hơn một hàng.

 Câu lệnh con không trả về hàng nào.

Các phép toán thường dùng

Toán tử

Diễn giải

=

Bằng

>

Lớn hơn

>=

Lớn hơn hoặc bằng

<

Nhỏ hơn

<=

Nhỏ hơn hoặc bằng

<>

Khác

5 - 9

Chương 5. Truy vấn con

Truy vấn con

5.3 Truy vấn con nhiều hàng

 kết quả trả về nhiều hơn một hàng.

Một số toán tử thường được sử dụng

Toán tử Diễn giải

ANY

ALL

5 - 10

Chương 5. Truy vấn con

EXISTS Nằm trong danh sách So sánh với bất kỳ giá trị nào trong kết quả của lệnh truy vấn con So sánh với tất cả các giá trị trong kết quả lệnh truy vấn con Trả về TRUE nếu có tồn tại

Truy vấn con

5.3 Truy vấn con nhiều hàng

Ví dụ: Tìm những nhân viên có thu nhập bằng với mức lương tối thiểu của các phòng ban.

SELECT ename, salary, dept_id FROM Emp WHERE salary IN (SELECT MIN(salary)

5 - 11

Chương 5. Truy vấn con

FROM emp GROUP BY dept_id);

Truy vấn con

5.3 Truy vấn con nhiều hàng Ví dụ: Tìm nhân viên không phải lập trình viên (ST_PROG’) và mức lương thấp hơn bất kỳ một lập trình viên nào (sử dụng toán tử ANY).

SELECT emp_id, ename, job_id, salary FROM Emp WHERE salary < ANY (SELECT salary

FROM emp WHERE job_id = 'ST_PROG')

5 - 12

Chương 5. Truy vấn con

AND job_id <> ‘ST_PROG’;

Truy vấn con

5.3 Truy vấn con nhiều hàng

 Chú ý:

 < ANY: nhỏ hơn phần tử lớn nhất

 > ANY: lớn phần tử nhỏ nhất

 = ANY: tương đương với bất kỳ phần tử nào

 < ALL: nhỏ hơn phần tử nhỏ nhất

 > ALL: lớn hơn phần tử lớn nhất

 điều kiện so sánh với giá trị NULL trả về NULL (dùng IN)

5 - 13

Chương 5. Truy vấn con

Truy vấn con

5.4 Truy vấn con trên nhiều cột

Mỗi hàng trong câu truy vấn chính được so sánh với giá trị của câu truy vấn con nhiều hàng và nhiều cột.

SELECT col1, col2, ... FROM table WHERE (col1, col2, ...) IN

(SELECT col1, col2, ... FROM table WHERE condition);

5 - 14

Chương 5. Truy vấn con

Truy vấn con

5.4 Truy vấn con trên nhiều cột

(SELECT manager_id, dept_id FROM Emp WHERE emp_id IN (178,174)) AND emp_id NOT IN (178,174);

5 - 15

Chương 5. Truy vấn con

Ví dụ: Hiển thị các nhân viên do cùng một người quản lý và làm việc cùng phòng với nhân viên có mã số 178 hoặc 174. SELECT emp_id, manager_id, dept_id FROM Emp WHERE (manager_id, dept_id) IN

Truy vấn con

5.5 Truy vấn con tương quan

được sử dụng trong trường hợp xử lý từng hàng

5 - 16

 Lấy một hàng ứng viên.  Thực thi câu lệnh con sử dụng giá trị của các hàng ứng viên.  Sử dụng kết quả từ câu lệnh con để loại hoặc không các hàng ứng viên.  Lặp lại cho đến khi không còn hàng ứng viên nào. Chương 5. Truy vấn con

Truy vấn con

5.5 Truy vấn con tương quan

SELECT column1, column2, ...

FROM table1 outer

WHERE column1 operator

(SELECT colum1, column2

FROM table2

WHERE expr1 = outer.expr2);

5 - 17

Chương 5. Truy vấn con

có thể sử dụng toán tử ANY và ALL trong câu lệnh truy vấn con tương quan.

Truy vấn con

5.5 Truy vấn con tương quan

Ví dụ: Tìm tất cả các nhân viên có thu nhập cao hơn mức lương trung bình trong phòng ban của họ.

SELECT ename, salary, deptid FROM Emp outer WHERE salary > (SELECT AVG(salary)

5 - 18

Chương 5. Truy vấn con

FROM Emp WHERE deptid = outer.dept_id);

Truy vấn con

5.5 Truy vấn con tương quan

 Tương quan UPDATE

cập nhật các hàng trong một bảng dựa trên cơ sở các hàng từ bảng khác

UPDATE table1 alias1 SET col = (SELECT expression

FROM table2 alias2 WHERE alias1.col = alias2.co);

5 - 19

Chương 5. Truy vấn con

Truy vấn con

5.5 Truy vấn con tương quan

 Tương quan UPDATE

Ví dụ Cập nhật dữ liệu cột Deptid của bang Emp dựa vào bảng Dept.

UPDATE Emp e

SET deptid =

(SELECT deptid

FROM Dept d

5 - 20

Chương 5. Truy vấn con

WHERE e.deptid = d.deptid);

Truy vấn con

5.5 Truy vấn con tương quan

 Tương quan DELETE

xóa các hàng trong một bảng dựa trên cơ sở các hàng từ bảng khác

DELETE FROM table1 alias1

WHERE col operator

(SELECT expression

FROM table2 alias2

5 - 21

Chương 5. Truy vấn con

WHERE alias1.col = alias2.col);

Truy vấn con

5.5 Truy vấn con tương quan

 Tương quan DELETE

Ví dụ: Xóa những hàng trong bảng EMP mà các hàng đó tồn tại trong bảng EMP_HISTORY.

DELETE FROM Emp E

WHERE emp_id =

(SELECT emp_id

FROM emp_history

5 - 22

Chương 5. Truy vấn con

WHERE emp_id = E.emp_id);

Truy vấn con

5.6 Truy vấn phân cấp

một bảng có thể thể hiện cấu trúc hình cây nhờ truy vấn phân cấp

SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)];

5 - 23

Chương 5. Truy vấn con

Truy vấn con

5.6 Truy vấn phân cấp

 Cột giả LEVEL cho biết cấp của mẫu tin hay cấp của nút trong quan hệ thừa kế.

 START WITH Xác định điểm đầu của hình cây (bắt buộc).

 CONNECT BY PRIOR Chỉ định cột và định hướng trong mối liên hệ hình cây (bắt buộc).

5 - 24

Chương 5. Truy vấn con

Trong đó:

Truy vấn con

5.6 Truy vấn phân cấp

5 - 25

Chương 5. Truy vấn con

Ví dụ: Hiển thị danh sách những người quản lý bắt đầu với nhân viên tên ‘King’. (Thứ tự trên xuống) SELECT ename||' reports to '|| PRIOR ename "Walk Top Down“ FROM emp TART WITH ename = 'King’ CONNECT BY PRIOR emp_id = manager_id;

Truy vấn con

5.6 Truy vấn phân cấp

Ví dụ: Lập báo cáo hiển thị các cấp quản lý công ty, bắt đầu từ cấp cao nhất và thụt đầu dòng theo từng cấp.(Định dạng phân cấp báo cáo sử dụng LEVEL và LPAD)

SELECT LPAD(ename, LENGTH(ename) + (LEVEL*2) - 2,'_') AS org_chart FROM Emp

START WITH ename='King'

5 - 26

Chương 5. Truy vấn con

CONNECT BY PRIOR emp_id=manager_id