Truy vấn thông tin

Cú pháp chung - câu lệnh SELECT.

- câu lệnh SQL không phân biệt chứ in hoa, viết thường. (Nhưng kiểu dữ liệu Char = xâu kí tự thì có phân biệt).

- dấu cách có thể là : các khoảng trắng, dấu table, dấu xuống dòng.

- kết thúc câu lệnh có thể là : dấu chấm phẩy hoặc không có gì tuỳ theo từng hệ thống.

Ví dụ

SELECT NAME, STARTTERM, ENDTERM

FROM PRESIDENTS

WHERE NAME = 'LINCOLN'

Select name, startterm, endterm

From presidents

Where name = 'LINCOLN'

Select name, startterm, endterm From presidents Where name = 'LINCOLN'

Các thành phần cơ bản.

Các từ khoá + các biểu thức -> các điều khoản

Các điều khoản -> câu lệnh SQL

select <danh sách tên cột>from <tên bảng> where <điều kiện tìm kiếm>

điều khoản Selectđiều khoản Fromđiều khoản Where

Danh sách tên cột :

các tên cột cách nhau dấu phẩy

thứ tự giống như đã liệt kê, có thể khác với thứ tự vốn có trong bảng.

* = tất cả các cột theo thứ tự vốn có

Vi dụ :

Giả sử có bảng tên là Employee với các trường (các cột) tên là Badge, Name, Departement, BirthYear, Salary. Sau này ta sẽ viết ngắn gọn như sau : Employee ( Badge, Name, Departement, BirthYear, Salary ).

Câu lệnh sau đây sẽ hiện toàn thể nội dung của bảng.

SELECT * FROM EMPLOYEE

Badge	Name	Department	Birth year	Salary
1834	Bob Smith	Sales	1970	24000.00
1051	Fred Sanders	Sales	1965	36000.00
3211	Stan Humphries	Field Service	1974	22000.00
6732	Fred Stanhope	Field Service	1958	56000.00
4411	Sue Sommers	Logistics	1965	40000.00
5522	Lance Finepoint	Library	1970	24000.00
1997	Mark McGuire	Field Service	1966	36000.00
9998	Sally Springer	Sales	1973	22000.00
7773	Ludmilla Valencia	Software	1969	36000.00
8883	Barbara Lint	Field Service	1966	40000.00
8005	Jeffrey Vickers	Mailroom	1965	42000.00
7779	Jim Walker	Unit Manager	1971	24000.00

Từ khoá DISTINCT / ALL

DISTINCT = chỉ lấy những dòng khác nhau

Nghĩa là nếu có nhiều dòng giống nhau chỉ lấy một đại diện.

ALL = tất cả các dòng, kể cả trùng nhau

đây là mặc định , không cần gõ ALL.

Cú pháp

Select DISTINCT <danh sách tên cột> ...

Select ALL <danh sách tên cột> ...

Ví dụ

SELECT DISTINCT Departement

FROM EMPLOYEE

Department

Sales

Field Service

Logistics

Library

Software

Mailroom

Unit Manager

Biểu thức :

Là một tập hợp các toán hạng và toán tử, trả lại một giá trị : số, lôgic, xâu

Ví dụ

Biểu thức số : (Salary * 1.05)

Biểu thức lôgic : Name = ' Barbara Lint' , Salary <= 50,000.00

Biểu thức xâu : Name + Phone.

Các biểu thức được sử dụng trong câu lệnh QSL :

- Trong điều khoản SELECT để kết xuất thông tin trực tiếp

- Làm điều kiện tìm kiếm trong điều khoản WHERE, HAVING ...

Biểu thức điều kiện & Điều khoản Where.

Điều kiện là một biểu thức lôgic.

Một biểu thức điều kiện được dùng kèm với từ khoá Where để tạo thành điều khoản Where, ấn định tiêu chuẩn tìm kiếm thông tin xuất ra. Chỉ những dữ liệu thảo điều kiện đã nêu mới được đưa vào kết quả truy vấn.

Ví dụ. Câu lệnh sau đây chỉ tìm và xuất ra các thông tin về cán bộ thuộc phòng bán hàng - Sales.

SELECT Name, Departement, Salary

FROM EMPLOYEE

WHERE Departement = 'Sales'

Name	Department	Salary
Bob Smith	Sales	24000.00
Fred Sanders	Sales	36000.00
Sally Springer	Sales	22000.00

Toán tử số học.

Là các thành phần để xây dựng biểu thức.

Các phép toán số học, phép so sánh, phép toán xâu kí tự.

Toán tử số học : cộng, trừ , nhân, chia, modulo ( a % b hoặc Mod (a,b) )

Các biểu thức số học, biểu thức xâu có thể sử dụng trong điều khoản SELECT để kết xuất thông tin trực tiếp.

Ví dụ 1: giả sử mọi người đều được phụ cấp thêm 5 phần trăm lương. Có thể hiển thị mức thực lĩnh như sau.

SELECT Name, Salary * 1.05

FROM EMPLOYEE

Ví dụ 2 : Giả sử có tệp csdl PRICE về giá hàng hoá gồm các trường Item - tên hàng, Wholesale - giá bán buôn. Nếu quy định giá bán lẻ là cộng thêm 0.15 thì có thể hiển thị bằng câu lênh sau

SELECT Item, Wholesale, Wholesale + 0.15

FROM PRICE

ALIAS - Tên hiệu.

Trong các ví dụ trên tên cột sẽ được hiển thị là Salary * 1.05 hoặc Wholesale + 0.15 không được đẹp mắt và gợi tả nội dung cho lắm.

Có thể đặt lại tên cột cho sát nội dung, gợi tả hơn : thay Salary *1.05 bằng Newamount - số thực lĩnh, thay wholesale + 0.15 bằng retail - giá bán lẻ.

SELECT Name, Salary * 1.05 Newamount

FROM EMPLOYEE

SELECT Item, Wholesale, Wholesale + 0.15 Retail

FROM PRICE

Các tên cột mới Newamount, Retail gọi là Alias -Tên hiệu, tên thay thế.

Lưu ý : cú pháp sử dụng Alias là

<biểu thức tên cột> <Alias> cách nhau khoảng trắng

Một số hệ thống có thể quy định khác, ví dụ dùng cú pháp với dấu bằng

<biểu thức tên cột> = <Alias>

Toán tử so sánh

Để xây dựng biểu thức lôgic.

kí hiệu	í nghĩa
=	Bằng
!=	Khác
<>	Khác
<	Nhỏ hơn
>	Lớn hơn
<=	Nhỏ hơn hay bằng
>=	Lớn hơn hay bằng

Các ví dụ & điều khoản where.

Điều kiện tìm kiếm được thể hiện trong diều khoản Where. Không chỉ có so sánh bằng nhau mà có thể dùng các phép so sánh bất kì hoặc biểu thức lôgic phức tạp.

Ví dụ : - tìm những người lương lớn hơn 30000.00

SELECT Name, Salary

FROM EMPLOYEE

WHERE Salary > 30000.00

- tìm những người lương lớn hơn 30000.00 mà không ở phòng bán hàng

SELECT Name, Salary , Departement

FROM EMPLOYEE

WHERE (Salary > 30000.00) AND (Departement <> 'Sales' )

Toán tử xâu -

LIKE : khi không biét thật chính xác một phần của xâu hoặc chỉ quan tâm đến một phần noà đó của xâu, có thể dùng Like với các kí tự đại diện %, _

Kí tự đại diện % thay cho một xâu con bất kì

Kí tự dại diện _ thay cho một kí tự bất kì.

Ví dụ : bảng mã vùng điện thoại AREACODE gồm có các trường : TênTỉnh, MãVùng,

MãVùng	TênTỉnh
034	HaTay
036	HaiDuong
04	HaNoi
08	TPHCM

Có thể hiển thị tất cả các tỉnh bắt đầu bằng Ha

SELECT *

PROM AREACODE

WHERE TenTinh LIKE 'Ha%'

Phép nối | |

Dùng để nối các xâu lại tạo thành biểu thức xâu lớn hơn.

Ví dụ có bảng FRIENDS gồm các trường FirstName, LastName, TelNum ...

Có thể hiên thị tên đầy đủ bằng câu lệnh

SELECT FirstName || LastName FullName

FROM FRIENDS

Toán tử lôgic

AND, OR, NOT,

Các phép toán lôgic có thể dùng để xây dựng các biểu thức điều kiện phức tạp hơn trong điều khoản Where.

Ví dụ

select *

from employee

where department = "field service"

or department = "logistics"

or department = "software"

Các phép toán tập hợp.

Hai bảng phải có cấu trúc giống nhau

UNION : hợp hai kết quả select, có loại bỏ trùng nhau.

UNION ALL : hợp hai kết quả select, không loại bỏ trùng nhau.

INTERSECT : giao hai kết quả select

MINUS : hiệu hai kết quả.

các phép toán khác

BETWEEN để xác định một đoạn con trong vùng giá trị có thể của một cột

IN để xác định một danh sách các giá trị có thể

SELECT column_name_1,...column_name_n

FROM table_name_1,...table_name_n

WHERE column_name IN (value_1, ...value_n)

Ví dụ

select *

from employee

where badge between 2000 and 7000

Các hàm xử lí tập thể - Aggregate functions.

Các câu lệnh SQL thông thường két xuất dữ liệu lần lượt theo từng dòng của bảng.

Các hàm tập đoàn - Aggegate function - Xử lí dữ liệu thuộc mọi dòng trong toàn bộ một cột ( hoặc một nhóm dòng thoả điều kiện nào đó) và cho ra chỉ một kết quả. Chúng dôi khi cũng đợc gọi là hàm nhóm - Group function.

Count : đếm số dòng thoả mãn điều khoản Where.

Ví dụ: đếm số nhân viên của phòng bán hàng

select count (*)

from employee

where Department = 'Sales'

Nếu không có điều khoản Where thì là đếm toàn bộ nhân viên.

SUM : tính tổng một cột

Cột phải là kiểu số

Bị lỗi Nếu không phải kiểu số .

Ví dụ

select SUM (Salary)

from employee.

AVG : Tính trung bình cộng của cột

Cột phải là kiểu số

Bị lỗi Nếu không phải kiểu số .

Select AVG (Salary)

from employee.

MAX : cho giá trị cực đại trong cột.

Có thể áp dụng cho cột kỉểu số và cả cột kiểu Char

Select MAX(Salary)

from employee.

MIN : cho giá trị cực tiểu trong cột

Có thể áp dụng cho cột kiểu số và cả cột kiểu Char.

Select MIN(Name)

from employee.

VARIANCE :

Cho bình phương của độ lêch tiêu chuẩn

Chỉ áp dụng được cho cột kiểu số.

Select VARIANCE(Salary)

from employee.

STDDEV : độ lệch tiêu chuẩn - Standard Deviation

Chỉ áp dụng cho cột kiểu số.

Ngày tháng và Thời gian.

SYSDATE : Thời gian hệ thống.

Dùng để so sánh : tìm các công việc chưa bắt đầu, đã kết thúc ...

ADD_MONTHS :

Dịch lùi lại, cộng thêm một số tháng từ một ngày cho trước

Ví dụ : Giả sử có CSDL về quản lí dự án gồm các trường Công việc, Ngày bắt đầu, ngày kết thúc ...

PROJECT ( Task, StartDate, EndDate, ...). Giả sử có sự cố làm dự án chậm mất hai tháng. Để hiển thị các công việc còn chưa két thúc được cần dùng hàm Add_month ( Enddate, 2) .

Select Task, StartDate, EnDDate Original_End, ADD_MONTHS (EndDate, 2)

From PROJECT

MONTH_BETWEEN :

Đếm số tháng giữa hai mốc ngày.

Ví dụ.

Select Task, StartDate, EndDate ,

MONTHS_BETWEEN( StartDate, EndDate) Duration

From PROJECT

Các hàm số học.

ABS : giá trị tuyệt đối

CEIL : số trần - số nguyên nhỏ nhất lớn hơn hay bằng đối số

FLOOR : số sàn - số nguyên lớn nhất bé hơn hay bằng đối số

Cac hàm lượng giác

COS , COSH, SIN, SINH, TAN, TANH

đối số là Radian

EXP

LN

LOG (x, b) : log b x.

POWER ( a, b) : a b

MOD ( a,b) : a mod b

SIGN : cho dấu = -1 nếu đối số là âm

= 0 nếu đối số = 0

= 1 nếu đối số là dương.

SQRT : căn bậc hai

các hàm xử lí kí tự và xâu kí tự.

CHR : chuyển đối số là một số nguyên thành kí tự có mã tương ứng

Bảng mã có thể là ASCII

CONCAT : nối hai xâu

INITCAP : chuẩn hoá xâu, kí tự đầu là in hoa, các kí tự sau là chữ thường.

LOWER, UPPER : chuyển đổi cả xâu (mọi kí tự) thành chữ in thường, in hoa

LPAD, RPAD : độn thêm kí tự vào bên trái, bên phải của xâu cho đủ độ rộng.

Lpad ( xâu, độ rộng, kí tự mẫu để điền thêm)

Ví dụ

Néu chỉ có hai đối là xâu và độ rộng, không cho kí tự mẫu thì mặc định là khoảng trống.

LTRIM, RTRIM :

Ltrim ( xâu, kí tự cần xoá bỏ) .

Nếu chỉ có một đối xâu, không cho kí tự mẫu thì mặc định là khoảng trống.

REPLACE : thay thế một (các) xâu con bằng một xâu khác

Replay (xâu, xâu con cần tìm, xâu thay thế)

Tìm xâu con và thay thế mỗi khi gặp bằng xâu thay thế.

Nếu không có đối thứ 3 - không cho xâu thay thế, thì mặc định là rỗng.

SUBSTR : Trích ra xâu con

SubStr ( xâu, pos , num)

Pos = vị trí bắt đầu

Num = số kí tự trích ra = độ dài xâu con.

Nếu pos là số âm thì tính từ cuối xâu mẹ. Lưu ý rằng độ rộng trường ấn định trước nên có thể tính lùi.

TRANSLATE. Dịch từng kí tự thành kí tự mã hoá tương ứng

Translate (xâu đích, xâu kí tự nguồn, xâu kí tự mã hoá)

Ví dụ : để mã hoá chuyển mọi kí số thành N, kí tự chữ cái thành C

Translate (Firstname, 0123456789ABCDE...

NNNNNNNNCCCC....

INSTR : tìm kiếm một kí tự mẫu, cho biết vị trí trong xâu

Instr( xâu, mẫu tìm kiếm, vị trí bắt đầu, mẫu thứ mấy)

Ví dụ instr (LastName, 'O', 2, 1)

Bắt dầu từ vị trí thứ 2 trong LastName, tìm vị trí đầu tiên xuất hiện của kí tự O,

LENGTH : cho độ dài xâu

Các hàm chuyển đổi.

TO_CHAR : chuyển thành xâu kí tự

Chuyển một số nguyên thành xâu kí số biểu diễn số đó

TO_NUMBER

Chuyển xâu kí số thành giá trị số.

Các hàm khác

GREATEST : cho hạng tử lớn nhất trong dãy các đối

LEAST cho hạng tử nhỏ nhất trong dãy các đối

USER : cho biết tên của người đang sử dụng DB.

Điều khoản WHERE.

Một biểu thức điều kiện được dùng kèm với từ khoá Where để tạo thành điều khoản Where, ấn định tiêu chuẩn tìm kiếm thông tin xuất ra. Chỉ những dữ liệu thoả điều kiện đã nêu mới được đưa vào kết quả truy vấn.

Biểu thưc diều kiện trong điều khoản Where có thể dùng bất kì các phép so sánh số học, các phép toán lôgic nào.

Điều khoản STARTING WITH.

Giống như LIKE ( <biểu thức> %)

Điều khoản ORDER BY

Dùng để két xuất thông tin ra theo thứ tự của một cột được chọn làm khoá sắp xếp.

Có thể sắp theo nhiều, trước hết sắp theo cột thứ nhất trong danh sách khoá, sau đó trong từng nhóm cùng cột thứ nhất lai sắp theo cột thứ 2...

select *

from employee

order by department, badge desc

ASC : tăng dần, là lựa chon mặc định

DESC : giảm dần

Có thể thay tên cột bằng số thứ tự cột trong bảng.

Điều khoản GROUP BY

Khác với ORDER BY in ra mọi dòng theo nhóm không loại bỏ các giá trị trùng lặp, xử lí cả nhóm dòng cùng giá trị như một đơn vị.

select department, count(*) headcount

from employee

group by department

GROUP BY thường dùng với các hàm xử lí nhóm dữ liệu.

Ví dụ

select department, AVG(Salary)

from employee

group by department

Điều khoản HAVING

Where không làm việc với các hàm xử lí (tập đoàn) nhóm như AVG, SUM...

Phaỉ dùng Having

select department, SUM(Salary)

from employee

group by department

having SUM (Salary) > ...

Kết hợp các điều khoản.

Có thẻ phối hợp nhiều điều khoản để thực hiện các truy vấn tin tuỳ ý thoả mãn yêu cầu của NSD.

Ví dụ. Có bảng CHECKS ( Payee, Amount). Câu lệnh sau sẽ xuất ra từng người nhận séc với tổng số tiền và số lần nhận nhưng loại bỏ những người mà có tổng số tiền quá nhỏ (<= 50).

Select Payee,

SUM(Amount) Total,

COUNT (Payee) Checks_written

From checks

Group by Payee

Having SUM (Amount) > 50

Join : nối tự nhiên hai bảng

select *

from table_1, table_2

đây là phép nối tự nhiên, không có điều kiện gì (thiếu điều khoản Where).

kết quả của nối tự nhiên là tích Đề các.

Nếu bảng 1 có m dòng, bảng 2 có n dòng thì kết quả sẽ gồm m*n dòng

Nối có điều kiện

Việc chuẩn hoá loại bỏ dư thừa dữ liệu làm cho bảng nhỏ hơn và cơ sở dữ liệu gồm nhiều bảng hơn. Rất thông thường, các truy vấn SQL lien quan đến nhiều bảng. Điều khoản Where thể hiện mối liên hệ này.

Ví dụ : Có CSDL gồm ba bảng

CUSTOMER (Name, Address, Phone)

ODERS ( OrderOn, Name, PartNum,Quantity)

PART (PartNum, Description, Price)

select o.orderon, o.name, o.partnum,

p.description, o.quantity * p.price total

from order o, part p

where o.partnum = p.partnum

Có thể bổ xung thêm điều kiện vào điều khoản Where để thực hiện các truy vấn chi tiết hơn ví dụ

where o.partnum = p.partnum

and p.price > ...

Câu lệnh con. SubQueries

Là một truy vấn khác, đóng vai trò là một phần của điều khoản Where.

Ví dụ.

select *

from employee

where department = (

select department

from employee

where name = "bob smith")

Điều kiện lôgic = ở trên đòi hỏi kết quả của câu lệnh con phải đơn trị, nghĩa là chỉ trả về một dòng. Nếu thay câu lệnh con như sau

(

select department

from employee

where name like "% smith")

Thì có thể mắc lỗi khi có nhiều người tên là smith và ở các phòng chức năng khác nhau.

Ví dụ2 :

select o.orderon, o.name, o.partnum,

p.description, o.quantity * p.price total

from order o, part p

where o.partnum = p.partnum

and o.quantity * p.price >

(select AVG(o.quantity * p.price)

from order o, part p

where o.partnum = p.partnum )

Các câu lệnh con lồng nhau.

Các Subqueries có thể lồng nhau nhiều mức, tuỳ theo khả năng cụ thể của từng hệ thống SQL.

Ví dụ 3

Select c.name, c.address, c.phone

From customer c

Where c.name IN

(select o.name

from order o, part p

where o.partnum = p.partnum

and o.quantity * p.price >

(select AVG(o.quantity * p.price)

from order o, part p

where o.partnum = p.partnum ))

Lưu ý rằng ở đây điều kiện lôgic là IN nên câu lệnh con có thể trả về nhiều dòng.

Sử dụng EXits, ANY, ALL

EXIST : kiểm tra kết quả của câu lệnh con như là một tham đối. Nếu câu lệnh con có trả về một kết quả gì đó thì điều kiện được coi là TRUE, trái lại thì điều kiện là FALSE.

Ví dụ.

Select name, orderon

From orders

Where EXITS

(select *

from orders

where name = 'xxxxx' )

Lưu ý EXIST không quan tâm đến số cột trả về trong câu lệnh con.

ANY hoặc SOME : kiểm tra lần luợt từng dòng. Nếu giá trị cột có mặt trong kết quả trả về của câu lệnh con thì điều kiện được coi là TRUE.

Như vậy câu lệnh con có thể trả về nhiều giá trị dù rằng điều kiện ở đây là =.

Có thể thấy nó có tác dụng giống như IN

Ví dụ.

Select name, orderon

From orders

Where name = ANY

(select name

from orders

where name = 'xxxxx')

Tuy nhiên, có trrường hợp không thể dùng IN,

Trong ví dụ sau ANY được dùng với phép so sánh <, > trong khi IN có thể hiểu như nhiều dấu bằng.

Select name, orderon

From orders

Where name > ANY

(select name

from orders

where name = 'xxxxx')

ALL : điều kiện chỉ được coi là đúng nếu mọi kết quả trả về của câu lệnh con đều thoả mãn.

Select name, orderon

From orders

Where name <> ALL

(select name

from orders

where name = 'xxxxx')

Câu lệnh trên có kết quả là mọi người, trừ ông 'xxxxx'.