Bài giảng Cơ sở dữ liệu: Chương 4

CHƯƠNG 4 NGÔN NGỮ SQL TRUY VẤN CSDL

GV Th.S. Thiều Quang Trung Bộ môn Khoa học cơ bản Trường Cao đẳng kinh tế đối ngoại

Nội dung

• Giới thiệu chung • Giới thiệu chung

• Các nhóm lệnh DDL, DML, DCL • Các nhóm lệnh DDL, DML, DCL

2

• Câu lệnh SELECT • Câu lệnh SELECT

3

• Các hàm tập hợp • Các hàm tập hợp

• Các mệnh đề của SELECT • Các mệnh đề của SELECT

• Truy vấn sub-query • Truy vấn sub-query

GV Thiều Quang Trung 2

Giới thiệu chung

• Là ngôn ngữ chuẩn cho các CSDL quan hệ • Chuẩn ANSI SQL được công bố vào năm

1989, 1992 và 1999 • Đặc điểm của SQL:

– Ngôn ngữ tựa tiếng Anh – Ngôn ngữ phi thủ tục

GV Thiều Quang Trung 3

Giới thiệu chung • SQL chia thành 3 nhóm:

–Data Definition Language (DDL): ngôn ngữ định nghĩa dữ liệu gồm các lệnh • CREATE/ALTER/DROP DATABASE • CREATE/ALTER/DROP TABLE

GV Thiều Quang Trung 4

Giới thiệu chung

– Data manipulation Language (DML): ngôn

ngữ thao tác dữ liệu • SELECT • INSERT, UPDATE, DELETE, TRUNCATE – Data Control Language (DCL): ngôn ngữ

điều khiển dữ liệu • GRANT/REVOKE/ADD • COMMIT/ROLLBACK

GV Thiều Quang Trung 5

Truy vấn dữ liệu

• Cú pháp chung của lệnh SELECT SELECT select_list

[ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]

GV Thiều Quang Trung 6

Mệnh đề SELECT

[ WITH TIES ] ] < select_list >

• Để đưa ra danh sách các cột cần xem • Cú pháp: SELECT [ ALL | DISTINCT ][ TOP n [ PERCENT ] < select_list > ::= { *

|{ table_name | view_name | table_alias }.* | { column_name | expression } [ [ AS ] column_alias ] | column_alias = expression

GV Thiều Quang Trung 7

} [ ,...n ]

Mệnh đề SELECT

• ALL (DEFAULT): hiển thị tất cả các bản ghi kể

cả các hàng trùng nhau.

• DISTINCT: chỉ hiển thị các hàng duy nhất không trùng nhau trong bảng kết quả. • Ví dụ: liệt kê tất cả các thành phố mà hiện

thời các khách hàng đang sống SELECT DISTINCT City , Region FROM Customers

GV Thiều Quang Trung 8

Mệnh đề SELECT

• TOP n [PERCENT]: chỉ có n hàng đầu tiên hay n% của

các hàng của bảng kết quả được xuất .

• WITH TIES: cho phép 1 số hàng được thêm vào bảng kết quả gốc nếu các hàng này có cùng giá trị trong cột ORDER BY với hàng cuối cùng của n hàng đầu tiên được xác định.

• Ví dụ: liệt kê 3 hoá đơn có cước phí cao nhất

SELECT top 3 with ties OrderID, Freight from Orders order by

Freight DESC

Nếu hàng thứ tư có cùng cước phí (freight) với hàng thứ ba

trong bảng kết quả thì sao???

GV Thiều Quang Trung 9

Mệnh đề SELECT

• Trường tính toán được xây dựng từ biểu thức (Expression) có liên quan đến các trường cơ sở.

• Biểu thức bao gồm:

– Các trường – Các toán tử số học, logic, so sánh – Các hàm

GV Thiều Quang Trung 10

Database sample NorthWind

AutoNumber Text

• Bảng Categories – CategoryID – CategoryName – Dessription Memo – Picture

OLE Object

GV Thiều Quang Trung 11

Database sample NorthWind

• Bảng Customer

– CustomerID – CompanyName – ContectName – ContactTitle – Address – City – Region

Text Text Text Text Text Text Text

GV Thiều Quang Trung 12

Database sample NorthWind

• Bảng Customer

– PostalCode Text – Country Text – Phone Text – Fax Text

GV Thiều Quang Trung 13

Database sample NorthWind

• Bảng Employees AutoNumber - EmployeeID Text - LastName - Title Text - TitleOfCourtesty Text Date/time - BirthDate - HireDate Date/time - Address Text

GV Thiều Quang Trung 14

Database sample NorthWind

• Bảng Employees - City Text - Regoin Text - PostalCode Text - Country Text - HomePhone Text - Extension Text - Photo Text

GV Thiều Quang Trung 15

Database sample NorthWind

• Bảng Employees - Note Memo - ReporsTo Number

GV Thiều Quang Trung 16

Database sample NorthWind

• Bảng Order Details - OderID Number - ProductID Number - UnitPrice Currency Number - Quantity Numer - Discount

GV Thiều Quang Trung 17

Database sample NorthWind

• Bảng Order - OderID AutoNumber - CustomerID Text - EmployeeID Number Date/time - OrderDate - RequiredDate Date/time - ShippedDate Date/time - ShipVia

Numer

GV Thiều Quang Trung 18

Database sample NorthWind

• Bảng Order - Freight Currency - ShipName Text - ShipAddress Text Text - ShipCity - ShipRegion Text - ShipPostalCode Text Text - ShipCountry

GV Thiều Quang Trung 19

Database sample NorthWind

• Bảng Products - ProductIDt AutoNumber - ProductName Text - SupplierID Number Number - CategoryID - QuantityPerUnit Text - UnitPrite Currentcy - UnitsInStock Number

GV Thiều Quang Trung 20

Database sample NorthWind

• Bảng Products - UnitsOnOrder Number Number

ReOrderLevel

- Discontinued Yes/No

GV Thiều Quang Trung 21

Database sample NorthWind

• Bảng Shippers - ShipperID AutoNumber

CompanyName Text - Phone Text

GV Thiều Quang Trung 22

Database sample NorthWind

• Bảng Supplies - SupplierID AutoNumber - CompanyName Text - ContactName Text - ContactTitle Text - Address Text - City Text

GV Thiều Quang Trung 23

Database sample NorthWind

• Bảng Supplies Text - Region - PostalCode Text - Country Text - Phone Text - Fax Text - HomePage Hyperlink

GV Thiều Quang Trung 24

Mệnh đề SELECT

• Ví dụ 1: SELECT OrderID, ProductID, Amount =UnitPrice*Quantity* (1- Discount) FROM [Order Details]

GV Thiều Quang Trung 25

Các hàm tập hợp (Aggregate function) Tên hàm

Ý nghĩa

SUM

Tính tổng các số

MIN

Trả về giá trị nhỏ nhất

MAX

Trả về giá trị lớn nhất

AVG

Tính giá trị trung bình

COUNT

Đếm số phần tử

Được dùng để tổng kết các giá trị của 1 cột hay 1 nhóm cột bên trong 1 bảng và cho kết quả là 1 giá trị đơn

GV Thiều Quang Trung 26

Ví dụ các hàm gộp trong lệnh select

• SELECT sum(Freight) from dbo.Orders  Tính tổng cước phí chuyên chở của tất cả hoá đơn • SELECT count(OrderID) from dbo.Orders  Đếm số hoá đơn • SELECT SUM(UnitPrice*Quantity*(1-Discount))

FROM dbo.[Order Details]

 Tính doanh số bán hàng

GV Thiều Quang Trung 27

Mệnh đề WHERE

• Chứa điều kiện lọc dữ liệu cần trả về • Cú pháp: WHERE • Ví dụ: SELECT CustomerID, CompanyName FROM Northwind.dbo.Customers WHERE Region = 'WA'

GV Thiều Quang Trung 28

Mệnh đề ORDER BY

• Xác định thứ tự của bộ kết quả • Cú pháp [ ORDER BY { order_by_expression [ ASC | DESC

] } [ ,...n] ]

ASC (ascending) : xếp theo thứ tự tăng DESC (descending): xếp theo thứ tự giảm

GV Thiều Quang Trung 29

Nhóm dữ liệu trong bảng kết quả

• Những mệnh đề dùng để nhóm dữ trong

bảng kết quả: – GROUP BY: tổng hợp bảng kết quả theo nhóm

bằng cách dùng các hàm gộp

– COMPUTE và COMPUTE BY: mệnh đề COMPUTE

trong lệnh SELECT được dùng để phát ra các hàng tổng hợp bằng cách dùng hàm gộp. Mệnh đề COMPUTE BY được dùng để tổng hợp thêm các hàng kết quả theo cột

GV Thiều Quang Trung 30

Mệnh đề GROUP BY

• Cú pháp: [ GROUP BY [ ALL ] group_by_expression [ ,...n ]

ALL: bảng kết quả sẽ chứa tất cả các nhóm kể cả những nhóm không thỏa mãn điều kiện lọc trong trong mệnh đề WHERE, những nhóm không thoả điều kiện sẽ có giá trị null. • group_by_expression: biểu thức dùng để xác

định cột được nhóm

GV Thiều Quang Trung 31

Ví dụ mệnh đề group by

• SELECT Type, Advance = SUM (Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY Type

Advance --------------------- 25125.0000 15000.0000 19000.0000

Type ------------ business mod_cook trad_cook (3 row(s) affected)

GV Thiều Quang Trung 32

Ví dụ mệnh đề group by

• SELECT Type, Advance = SUM (Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY ALL Type

Advance --------------------- 25125.0000 15000.0000

Type ------------ business mod_cook popular_comp NULL NULL psychology 19000.0000 trad_cook UNDECIDED NULL (6 row(s) affected)

GV Thiều Quang Trung 33

GROUP BY và HAVING

• Có thể hạn chế các nhóm trong bảng kết quả bằng

mệnh đề HAVING.

• Chỉ sau khi dữ liệu đã được nhóm và tổng hợp , điều kiện trong mệnh đề HAVING mới được áp dụng.

• Không thể dùng 1 cột mà nó không tham gia vào hàm gộp của mệnh đề SELECT hay của mệnh đề GROUP BY.

• SELECT pub_id, AVG(price) FROM titles GROUP BY pub_id

HAVING (AVG(price) > 10)

GV Thiều Quang Trung 34

Sử dụng WHERE và HAVING

• Mệnh đề HAVING giống như mệnh đề WHERE

nhưng chỉ áp dụng cho cả nhóm trong khi mệnh đề WHERE áp dụng cho từng hàng.

• Một truy vấn có thể chứa cả mệnh đề WHERE và

mệnh đề HAVING. – Mệnh đề WHERE được áp dụng trước cho các hàng trong bảng được truy vấn. Chỉ những hàng nào thoả mãn điều kiện của mệnh đề WHERE mới được nhóm dữ liệu.

– Sau đó mệnh đề HAVING sẽ được áp dụng cho các

nhóm. Chỉ những nhóm thoả mãn điều kiện HAVING mới được xuất ra bảng kết quả.

GV Thiều Quang Trung 35

Ví dụ sử dụng WHERE và HAVING

• SELECT titles.pub_id, AVG(titles.price) FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id WHERE

publishers.state = 'CA' GROUP BY titles.pub_id HAVING AVG(price) > 10

GV Thiều Quang Trung 36

SELECT INTO

• Lệnh SELECT với mệnh đề INTO được dùng dể lưu trữ kết quả truy vấn vào 1 bảng mới mà không cần phải định nghĩa trước bảng.

• Cú pháp

SELECT columns_list INTO new_table_name FROM table_name1, table_name2,………, table_name n WHERE condition1, condition2,………., condition n

GV Thiều Quang Trung 37

Ví dụ SELECT INTO

• Ví dụ

SELECT Title_Id, Title INTO NewTitles FROM Titles WHERE Price > $15

GV Thiều Quang Trung 38

Mệnh đề JOIN

• Mệnh đề join dùng để liên kết dữ liệu từ

nhiều hơn 1 bảng

• Cú pháp

SELECT column_name [,n..] FROM table_name table_alias [CROSS|INNER|[LEFT | RIGHT]OUTER] JOIN

table_name table_alias

[ON table_name.ref_column_name join_operator

table_name.ref_column_name]

[WHERE search_condition]

GV Thiều Quang Trung 39

Kết nối các bảng

• Kết nối chỉ tồn tại trong thời gian truy vấn. • Kết nối không thay đổi dữ liệu trong các

bảng của cơ sở dữ liệu.

• Nên tạo bí danh (alias) cho tên bảng để

tránh gõ tên dài và làm truy vấn dễ đọc hơn

GV Thiều Quang Trung 40

Ví dụ kết nối 2 bảng

• Ví dụ

SELECT t.Title_Id, t.Title, p.Pub_Id from Titles t JOIN Publishers p on t.Pub_Id=p.Pub_Id WHERE Pub_Name='New Moon Books'

GV Thiều Quang Trung 41

Các cột tham gia kết nối

• Nếu kết nối nhiều hơn 2 bảng thì kết nối 2 bảng trước, sau đó kết nối nhóm này với bảng thứ ba.

GV Thiều Quang Trung 42

Ví dụ kết nối 3 bảng

• Ví dụ

SELECT OrderID,p.ProductID, ProductName, CategoryName ,Quantity FROM [Order Details] o JOIN Products p ON o.ProductID = p.ProductID JOIN Categories c ON p.CategoryID = c.CategoryID

GV Thiều Quang Trung 43

Các loại kết nối

• Inner Join • Outer Join • Cross Join • Equi Join • Natural Join • Self Join

GV Thiều Quang Trung 44

Kết nối nội - Inner joins

• Trong kết nối nội, dữ liệu từ nhiều bảng được hiển thị sau khi so sánh giá trị trong 1 cột chung. Chỉ những hàng mà có giá trị thoả mãn điều kiện kết nối trong cột chung đó mới được hiển thị.

• Tích Cartesian: việc kết nối nhiều bảng mà

không có điều kiện kết nối trong mệnh đề ON sẽ tạo ra tích cartesian giữa 2 bảng

GV Thiều Quang Trung 45

Kết nối nội - Inner joins

• Ví dụ: SELECT t.title,t.price, p.pub_name, p.city,

t.pubdate

FROM titles t join publishers p on t.pub_id = p.pub_id

GV Thiều Quang Trung 46

Kết nối nội với toán tử lớn hơn

• Có thể thực hiện kết nối 2 bảng với điều kiện kết nối

dùng toán tử không bằng nhau.

• Ví dụ:

SELECT p.pub_name, p.state, a.au_lname,

a.au_fname, a.state

FROM publishers p INNER JOIN authors a ON a.state > p.state WHERE p.pub_name = 'New Moon Books' ORDER BY au_lname ASC, au_fname ASC

GV Thiều Quang Trung 47

Kết nối ngoại - Outer joins

• Kết nối ngoại được dùng để cho ra kết quả chứa

tất cả các hàng của 1 bảng và các hàng trùng nhau của bảng còn lại. Những cột mà không có giá trị phù hợp sẽ được hiển thị giá trị NULL.

• Cú pháp

SELECT column_name, column_name [,column_name] FROM table_name [LEFT | RIGHT] OUTER JOIN

table_name

ON table_name.ref_column_name join_operator table_name.ref_column_name

GV Thiều Quang Trung 48

Kết nối trái - LEFT OUTER JOIN

• Tất cả các hàng từ bảng bên trái trong mối kết nối giữa 2 bảng sẽ được hiển thị trong bảng kết quả.

• Ví dụ:

SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p

ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

GV Thiều Quang Trung 49

Cross join

• Cross join trả về mọi tổ hợp có thể có của tất

cả các hàng trong các bảng kết nối.

• Cross join không có mệnh đề ON

– Nếu không mệnh đề WHERE, cross join sẽ tạo ra

tích Cartesian

– Nếu có mệnh đề WHERE, cross join sẽ thực hiện

như 1 kết nối nội

GV Thiều Quang Trung 50

Kết nối chéo - Cross join • Ví dụ 1:

SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers ORDER BY au_lname DESC

Kết quả chứa 184 hàng (authors có 23 hàng và

publishers có 8 hàng; 23 x 8 = 184 hàng).

• Ví dụ 2:

SELECT au_fname, au_lname, pub_name FROM authors

CROSS JOIN publishers WHERE authors.city = publishers.city ORDER BY au_lname DESC

SELECT au_fname, au_lname, pub_name FROM authors

INNER JOIN publishers ON authors.city = publishers.city ORDER BY au_lname DESC

GV Thiều Quang Trung 51

Truy vấn con - Subqueries

• Subquery là lệnh SELECT mà kết quả trả về là 1 giá trị đơn (single value) và được đặt lồng vào bên trong các lệnh SELECT, INSERT, UPDATE, hay DELETE, hay bên trong truy vấn con khác.

• Subquery có thể được dùng bất kỳ nơi nào

mà biểu thức được phép dùng

GV Thiều Quang Trung 52

Subqueries

• Ví dụ 1:

SELECT Ord.OrderID, Ord.OrderDate, (SELECT

MAX(OrdDet.UnitPrice)

FROM Northwind.dbo.[Order Details] AS OrdDet WHERE Ord.OrderID =OrdDet.OrderID) AS

MaxUnitPrice

FROM Northwind.dbo.Orders AS Ord

GV Thiều Quang Trung 53

Subqueries

• Subquery có thể được dùng để khôi phục dữ liệu từ nhiều bảng và có thể được dùng như 1 cách khác của join (alternative to a join)

GV Thiều Quang Trung 54

Subqueries

• Cách 1: dùng subquery.

SELECT ProductName FROM Products WHERE UnitPrice = (SELECT UnitPrice FROM

Products WHERE ProductName = 'Sir Rodney''s Scones')

• Cách 2: dùng join

SELECT Prd1.ProductName FROM Products AS

Prd1 JOIN Products AS Prd2

ON (Prd1.UnitPrice = Prd2.UnitPrice) WHERE Prd2.ProductName = 'Sir Rodney''s Scones'

GV Thiều Quang Trung 55

Subqueries

• Subquery có thể được dùng theo 1 trong các

dạng sau: – WHERE expression [NOT] IN (subquery) – WHERE expression comparison_operator [ANY |

ALL] (subquery)

– WHERE [NOT] EXISTS (subquery)

GV Thiều Quang Trung 56

Subqueries với toán tử IN

• Kết quả của subquery được dùng với IN (hay với NOT IN) thường là 1 danh sách (list) chứa từ 0 đến nhiều giá trị.

• Ví dụ:

SELECT pub_name FROM publishers WHERE

pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')

• Dùng cách 2 với mệnh đề join ???

GV Thiều Quang Trung 57

Subquery với các toán tử so sánh

• Các toán tử so sánh đơn giản: =, < >, >, > =, <, ! >, ! <, or < =

• Subquery bắt đầu với toán tử so sánh đơn

giản và trả về 1 giá trị đơn SELECT DISTINCT title FROM titles WHERE price >

(SELECT MIN(price) FROM titles)

GV Thiều Quang Trung 58

Subquery với các toán tử so sánh

• Toán tử so sánh phức (Modified Comparison

operators) là toán tử so sánh đơn giản có kèm theo các từ All, any hay some (=toán tử s/sánh+ [All,Any,some]) • Subquery bắt đầu với toán tử so sánh phức sẽ trả về 1 danh sách (list) của 0 hay nhiều gia trị và có thể bao gồm cả mệnh đề GROUP BY hay HAVING.

GV Thiều Quang Trung 59