Home / Tài liệu CNTT / SQL / Các câu lệnh truy vấn SQL nâng cao Pdf

Các câu lệnh truy vấn SQL nâng cao Pdf

SQL

Trong quá trình truy vấn dữ liệu, không ít lần bạn phải loay hoay về việc dữ liệu trên bảng kết quả trả về cực kỳ lộn xộn.
Dữ liệu này được hiển thị không trực quan gây khó khăn cho quá trình làm việc hoặc tốn thêm nhiều công sức để xử lý chúng theo ý muốn.
Cái mình muốn nói ở đây là hiện thị dữ liệu trong bảng kết quả theo một thứ tự nào đó như tính năng Filler trong Excel.
Do đó, bài viết này chúng ta sẽ tìm hiểu về các kỹ thuật truy vấn nâng cao để có được kết quả tốt hơn.
[Một số bảng, thuộc tính, dữ liệu trong các ví dụ không có sẵn. Bạn hãy thêm vào để kiểm tra nhé.]

1. ORDER BY

Với MS – SQL Server, bạn có thể sắp xếp dữ liệu kết quả theo thứ tự tăng dần (ASC) hoặc giảm dần (DESC) trên một hoặc nhiều cột bằng cách sử dụng kết hợp ORDER BY trong các câu lệnh SELECT.
Mặc định ORDER BY sắp xếp kết quả theo thứ tự tăng dần (ASC), do đó nếu muốn hiển thị kết quả theo thứ tự tằng dần thì bạn không cần làm gì cả.
Nếu có nhiều thuộc tính sau ORDER BY, sắp xếp được thực hiện ưu tiên trên cột đầu tiên, kế đến cột 2, …, cột n.
Ví dụ: Hiển thị tên và địa chỉ phi công theo thứ tự alpha – beta:

SELECT hoten, dchi

FROM PHICONG
ORDER BY hoten DESC;

Các phép toán trên tập hợp (tương ứng với kiến thức trong Đại số quan hệ):

  • Phép hợp (ᴗ) là UNION
  • Phép giao (ᴖ) là INTERSECT
  • Phép trừ (\) là EXCEPT

Cú pháp:

<câu truy vấn 1>
UNION [ALL]  |  INTERSECT [ALL]  |  EXCEPT [ALL] <câu truy vấn 2>

Ví dụ: Tìm mã phi công từng làm việc cho công ty 1 hoặc công ty 2 (mã phi công duy nhất, không trùng lặp)

SELECT MPC FROM LAMVIEC WHERE MCT = 1
UNION

SELECT MPC FROM LAMVIEC WHERE MCT = 2;

Nếu cho phép trùng lặp, bạn chỉ cần sử dụng UNION ALL là được.
Tiếp theo, thử nghiệm với phép giao.
Ví dụ: Tìm mã phi công từng làm việc cho cả công ty 1 và công ty 2

SELECT MPC FROM LAMVIEC WHERE MCT = 1
INTERSECT

SELECT MPC FROM LAMVIEC WHERE MCT = 2;

Ví dụ: Tìm các mã phi công làm cho công ty 2 và không làm cho công ty 3

SELECT MPC FROM LAMVIEC WHERE MCT = 2
EXCEPT
SELECT MPC FROM LAMVIEC WHERE MCT = 3;

2. Truy vấn đơn giản trên nhiều bảng

Trong SQL, chúng ta được phép tìm kiếm, truy xuất dữ liệu từ nhiều bảng khác nhau trong cùng một CSDL – chỉ cần các bạn có liên kết với nhau.
Danh sách các bảng được liệt kê trong mệnh đề FROM.
Ví dụ: Tìm mã và họ tên phi công có số ngày làm việc cho một công ty bất kỳ lớn hơn 20 ngày

SELECT DISTINCT p.MPC, hoten
FROM LAMVIEC lv, PHICONG pc
WHERE lv.MPC = pc.MPC

AND songay > 20 ;

Trong đó: lv.MPC = pc.MPC là điều kiện để nối kết các bảng. songay > 20 là điều kiện chọn (lọc) dữ liệu tìm kiếm.
Điều kiện kết nối:

  • Nếu có n bảng thì có ít nhất n-1 điều kiện nối kết
  • Nối kết giữa thuộc tính khóa chính của bảng cha và thuộc tính khóa ngoài của bảng con
  • Liên kết các điều kiện nối kết bởi toán tử AND

Ví dụ: Tìm tên công ty mà phi công Patrick Cortier đã làm việc

SELECT DISTINCT tencty
FROM LAMVIEC AS lv, PHICONG AS pc, CONGTY AS ct
WHERE (lv.MPC = pc.MPC) AND (lv.MCT = ct.MCT)

AND hoten = ‘Patrick Cortier’;

Nếu không có điều kiện kết nối, ta chỉ có thể lấy về kết quả là một tích Descartes (CROSS JOIN) của các bảng trong mệnh đề FROM

SELECT *

FROM PHICONG AS pc CROSS JOIN LAMVIEC AS lv;
SELECT *
FROM PHICONG AS pc, LAMVIEC AS lv;

3. Phép kết nối

SQL cho phép kết hợp (JOIN) các dòng giữa hai hay nhiều bảng dựa trên các cột giống nhau giữa các bảng này.
Khái niệm JOIN này có thể dễ hiểu hơn là việc gộp tất cả các điều kiện chọn và điều kiện nối kết trong mệnh đề WHERE.
Ba kiểu nối kết :

  • Phép kết nối tự nhiên NATURAL JOIN
  • Phép nối kết đơn giản INNER JOIN
  • Các phép nối kết OUTER JOIN:

LEFT JOIN (mở rộng trái)

RIGHT JOIN (mở rộng phải)

FULL JOIN (mở rộng hai bên)

3.1. NATURAL JOIN

Tương tự đại số quan hệ, nối kết tự nhiên cho phép nối kết giữa hai bảng

  • Không yêu cầu chỉ ra điều kiện nối kết
  • Nối kết ngầm định giữa hai thuộc tính cùng tên giữa 2 bảng
  • Hai thuộc tính cùng tên này chỉ xuất hiện 1 lần trong quan hệ kết quả

Ví dụ: Tìm mã và họ tên phi công có số ngày làm việc cho một công ty bất kỳ lớn hơn 20 ngày

SELECT DISTINCT pc.MPC, hoten
FROM LAMVIEC AS lv NATURAL JOIN PHICONG AS pc
WHERE songay > 20 ; — ????

Nếu 2 thuộc tính không cùng tên -> có thể đổi tên trước khi nối kết.
Có thể xảy ra lỗi khi có 2 thuộc tính cùng tên ở 2 bảng nhưng không phải là hai thuộc tính để nối kết (thuộc tính khoá chính và khoá ngoài)

LƯU Ý: SQL Server không hỗ trợ NATURAL JOIN 😊))

3.2. INNER JION

Là loại nối kết đơn giản được dùng nhiều nhất không chỉ riêng MS – SQL Server mà hầu hết các hệ quản trị CSDL khác – cũng là lựa chọn yêu thích nhất của dân Data và Dev.
Cú pháp:

SELECT <tên cột> [ ,…] FROM <bảng 1>
INNER JOIN | JOIN <bảng 2>
ON <bảng 1>.<tên cột>  =  <bảng 2>.<tên cột>;

Cách nối kết:
Đối với mỗi dòng của bảng 1, tìm các dòng tương ứng trong bảng 2 -> sự tương ứng này được thực hiện dựa trên các cột chung của 2 bảng, đó là các cột dùng để nối kết:

  • Nếu không tìm được, dòng này (<bảng 1>) không được thêm vào kết quả.
  • Nếu tìm được, một dòng sẽ được thêm vào kết quả (dòng này bao gồm sự kết hợp các cột ở cả hai bảng.
  • Nếu tìm được nhiều dòng tương ứng ở <bảng 2>, nhiều dòng sẽ được thêm vào kết quả (giá trị các cột của <bảng 1> lặp lại nhiều lần).

Ví dụ: Tìm họ tên các phi công có số ngày làm việc cho một công ty nào đó là 20 ngày

SELECT hoten
FROM PHICONG AS pc JOIN LAMVIEC AS lv
ON pc.MPC = lv.MPC
WHERE songay = 20;

3.3. LEFT JOIN

Cú pháp LEFT JOIN:

SELECT <tên cột> [ ,…] FROM <bảng 1>
LEFT JOIN | LEFT OUTER JOIN <bảng 2>
ON <bảng 1>.<tên cột>  =  <bảng 2>.<tên cột>;

Kết quả bao gồm:

  • Các dòng tương ứng ở cả 2 bảng (kết quả INNER JOIN)
  • Và các dòng thuộc bảng <bảng 1> không tương ứng với <bảng 2>. Các cột tương ứng của <bảng 2> sẽ mang giá trị NULL

Ví dụ: Tìm họ tên các phi công, mã công ty và số ngày làm việc cho công ty nào đó kể các các phi công chưa làm việc cho công ty nào.

SELECT hoten, MCT, songay
FROM PHICONG AS pc
LEFT JOIN LAMVIEC AS lv
ON pc.MPC = lv.MPC;

3.4. RIGHT JOIN

Cú pháp RIGHT JOIN:

SELECT <tên cột> [ ,…] FROM <bảng 1>
RIGHT JOIN | RIGHT OUTER JOIN <bảng 2>
ON <bảng 1>.<tên cột>  =  <bảng 2>.<tên cột>;

Kết quả bao gồm :

  • Các dòng tương ứng ở cả 2 bảng (kết quả INNER JOIN)
  • Và các dòng thuộc bảng <bảng 2> không tương ứng với <bảng 1>. Các cột tương ứng của <bảng 1> sẽ mang giá trị NULL

Ví dụ: Tìm họ tên, mã phi công, địa chỉ và số ngày làm việc của các phi công sống ở Anh làm việc cho công ty có mã số là 1 kể cả trường hợp công ty này không có phi công ở Anh làm việc.

SELECT lv.MPC, hoten,dchi, songay
FROM (SELECT * FROM PHICONG WHERE nuoc LIKE ‘Anh’) AS pc
RIGHT JOIN (SELECT * FROM LAMVIEC where MCT = 1) AS lv
ON pc.MPC = lv.MPC;

3.5. FULL JOIN

Cú pháp FULL JOIN:

SELECT <tên cột> [ ,…] FROM <bảng 1>
FULL JOIN | FULL OUTER JOIN <bảng 2>
ON <bảng 1>.<tên cột>  =  <bảng 2>.<tên cột>;

Kết quả bao gồm :

  • Các dòng tương ứng ở cả 2 bảng (kết quả INNER JOIN)
  • Và các dòng thuộc bảng <bảng 1> không tương ứng với <bảng 2>. Các cột tương ứng của <bảng 2> sẽ mang giá trị NULL
  • Và các dòng thuộc bảng <bảng 2> không tương ứng với <bảng 1>. Các cột tương ứng của <bảng 1> sẽ mang giá trị NULL

Ví dụ: Tìm họ tên, MPC, địa chỉ và số ngày làm việc của các phi công sống ở Anh làm việc cho công ty mã số 1 kể cả trường hợp các phi công ở Anh không làm việc cho công ty 1 và cả trường hợp công ty này không có phi công ở Anh làm việc.

SELECT lv.MPC, hoten,dchi, songay
FROM (SELECT * FROM PHICONG WHERE nuoc LIKE ‘Anh’) AS pc
FULL JOIN (SELECT * FROM LAMVIEC where MCT = 1) AS lv
ON pc.MPC = lv.MPC ;

4. Truy vấn con

Một truy vấn con là một câu lệnh SELECT được lồng trong một SELECT, INSERT, DELETE, UPDATE hoặc một SELECT con khác.
Kết quả của truy vấn con có thể là một giá trị hoặc nhiều giá trị, truy vấn con sinh ra một điều kiện trong mệnh đề.
Cú pháp:

SELECT … FROM …
WHERE [… AND] <điều kiện với truy vấn con>
(SELECT con)

Ví dụ 1: Tìm họ tên của các phi công có số ngày làm việc lớn hơn 20?

SELECT hoten
FROM PHICONG
WHERE MPC IN
(SELECT MPC FROM LAMVIEC WHERE songay > 20) ; — trả về nhiều giá trị

Ở đây, chương trình sẽ tìm số phi công có ngày làm việc lớn hơn 20. Sau đó, trong tập kết quả trả về lại tìm họ tên của các phi công này.

Ví dụ 2: Tìm họ tên của các phi công có số ngày làm việc nhiều nhất?

SELECT hoten FROM PHICONG
WHERE MPC IN
(SELECT MPC FROM LAMVIEC
WHERE songay =
(SELECT MAX(songay) FROM LAMVIEC)); — trả về một giá trị

Truy vấn con có ràng buộc:

  • Mệnh đề ORDER BY không được dùng trong truy vấn con
  • Câu truy vấn con phải được bao trong cặp dấu ngoặc đơn ()
  • Mệnh đề SELECT của truy vấn con chỉ bao gồm một thuộc tính duy nhất trừ trường hợp dùng EXISTS
  • Thuộc tính trong điều kiện với truy vấn con và thuộc tính trong mệnh đề SELECT của truy vấn con phải tương thích trừ trường hợp dùng EXISTS
  • Các thuộc tính được định nghĩa trong SELECT chính có thể được sử dụng trong SELECT con
  • Nhưng các thuộc tính được định nghĩa trong SELECT con không thể được sử dụng trong SELECT chính

Truy vấn con có điều kiện và toán tử:

  • Nếu truy vấn con trả về một giá trị, các toán tử như >, >=, <, … có thể được sử dụng trong điều kiện với truy vấn con
  • Nếu truy vấn con trả về một tập các giá trị, phải sử dụng các toán tử như ANY, ALL, EXISTS trong điều kiện với truy vấn con:

ANY: Trả về true nếu một trong các giá trị của truy vấn con đúng

ALL: Trả về true nếu tất cả các giá trị của truy vấn con đúng

EXISTS: Trả về true nếu truy vấn con trả về ít nhất một dòng

Ví dụ 3: Tìm các loại máy bay của hãng Boeing mà có số chỗ lớn hơn ít nhất một loại nào đó của hãng Airbus

SELECT loai FROM LOAIMAYBAY
WHERE NSX = ‘Boeing’
AND socho > ANY

(SELECT socho FROM LOAIMAYBAY WHERE NSX = ‘Airbus’);

Ví dụ 4: Tìm các kiểu máy bay của hãng Airbus mà có số chỗ lớn hơn số chỗ của tất cả các kiểu của hãng Boeing

SELECT loai FROM LOAIMAYBAY
WHERE NSX = ‘Airbus’
AND socho > ALL

(SELECT socho FROM LOAIMAYBAY WHERE NSX = ‘Boeing’);

Ví dụ dụ 5: Tìm các máy bay thực hiện ít nhất một chuyến bay đến Paris?

SELECT * FROM MAYBAY
WHERE EXISTS (SELECT MMB FROM CHUYENBAY
WHERE MAYBAY.MMB = CHUYENBAY.MMB
AND noiden=’Paris’);
—  Cũng có thể sử dụng IN hoặc = ANY thay cho EXISTS

5. Truy vấn sử dụng các hàm kết tập

SQL hỗ trợ sử dụng các hàm kết tập để truy vấn dữ liệu

Ví dụ 6: Tìm xem có bao nhiêu phi công ở nước Pháp

SELECT COUNT(*) FROM PHICONG WHERE nuoc = ‘Phap’;

Ví dụ 7: Tính tổng khoảng cách đã bay, đường bay ngắn nhất, dài nhất và độ dài trung bình các chuyến bay của phi công mã số 20.

SELECT SUM(khoangcach) AS Tong, MIN(khoangcach) Nho_nhat,
MAX(khoangcach) AS Lon_nhat, AVG(khoangcach) AS Trung_binh
FROM CHUYENBAY
WHERE MPC = 20;

5.2. Gom nhóm – GROUP BY

Mệnh đề GROUP BY được dùng kết hợp với các hàm kết tập để nhóm kết quả theo một hoặc nhiều cột.
NOTE: mỗi thuộc tính trong mệnh đề SELECT phải bao hàm trong hàm kết tập hoặc trong mệnh đề GROUP BY
Cú pháp:

SELECT <tên cột 1> [, tên cột 2> ,…], <hàm kết tập(tên cột 1)>, [, <hàm kết tập(tên cột 2)>, …] FROM <tên bảng 1> [, <tên bảng 2>, …] [ WHERE <điều kiện> ] GROUP BY <tên cột 1> [, <tên cột 2>, …];

Ví dụ 8: Tìm số phi công của mỗi nước (nhóm theo từng nước)

SELECT nuoc, count(*) so_phi_cong
FROM PHICONG
GROUP BY nuoc;

5.3. GROUP BY – HAVING

Mệnh đề WHERE không thể được dùng với các hàm kết tập -> Điều kiện trên nhóm với hàm kết tập -> dùng HAVING (WHERE : điều kiện trên dòng).
HAVING chỉ hiểu các điều kiện trên các hàm kết tập.

Ví dụ 9: Số phi công của mỗi nước lớn hơn 3 phi công

SELECT nuoc, count(*) so_phi_cong
FROM PHICONG
GROUP BY nuoc HAVING count(*) >= 3;

6. Lưu kết quả với SELECT

6.1. SELECT INTO

SELECT INTO cho phép lưu kết quả truy vấn vào một bảng mới hoặc một biến (1)

SELECT <tên cột> [, …] INTO <tên bảng mới>
FROM <tên bảng>;

SQL Server còn cho phép lưu kết quả vào biến tạm (2)

SELECT <tên cột> [, … ] INTO <#Tên biến tạm>
FROM <tên bảng>;

Ví dụ 10:

SELECT * INTO temp
FROM CONGTY; —  trường hợp (1)
SELECT * INTO #temp
FROM CONGTY; —  trường hợp (2)

6.2. INSERT INTO – Thêm dữ liệu cho bảng

INSERT INTO cho phép thêm các dòng vào bảng từ kết quả truy vấn SELECT:

INSERT INTO <tên bảng> [ ( <tên cột> [, …]) ] <Lệnh SELECT>;

Ví dụ 11: Thêm dữ liệu phi công Pháp vào một bảng mới.

CREATE TABLE PHICONG_PHAP(
MPC smallint PRIMARY KEY,
hoten varchar(30),
dchi varchar(30)
);
INSERT INTO PHICONG_PHAP
SELECT MPC, hoten, dchi
FROM PHICONG
WHERE nuoc = ‘Pháp’;

6.3. SELECT TOP – Giới hạn kết quả hiển thị

Trong SQL Server, để giới hạn số dòng kết quả, ta sử dụng SELECT TOP(<số dòng>) và kết hợp với một số lệnh / mệnh đề khác.

Ví dụ 12: Sắp xếp các phi công theo họ tên và hiển thị 2 người đầu tiên

SELECT TOP(2) * FROM PHICONG
ORDER BY hoten;

Tổng kết

Như vậy khi kết thúc phần truy vấn SQL nâng cao này bạn hoàn toàn có thể tự tin áp dụng các kiến thức này vào việc thiết kế – truy vấn hay thao tác với CSDL.
Tất cả những gì được chia sẻ trên đây hoàn toàn là kiến thức cốt lõi, chưa thể đầy đủ hết nhưng nó sẽ là chìa khoá giúp bạn sử dụng SQL thuận lợi hơn trong tương lai.

Download: PDF

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Giới thiệu / Liên hệ / Chính sách / ❤ Góc chia sẻ
© Copyright 2021, Góc Chia Sẻ DMCA.com Protection Status