Ngôn ngữ SQL

I. Tổng Quát

Một cách tổng quát, khối select gồm có 3 mệnh đề chính:
Select: Xác định các cột cần đưa ra kết quả.
From: Xác định các bảng cần lấy thông tin ra.
Where: Xác định các mẫu tin thỏa yêu cầu chọn lọc để đưa ra kết quả.
Ngoài ra, để mở rộng khả năng của ngôn ngữ, khối select-from-where còn được bổ sung thêm
các mệnh đề group by, having, order by; các hàm hỗ trợ tính toán: max, min, count, sum,avg.
Sau đây là cú pháp tổng quát của câu truy vấn dữ liệu:
 

SELECT [tính chất] FROM [WHERE <điều kiện_1>] [GROUP BY ] [HAVING <điều kiện_2>] [ORDER BY

Diễn giải :

1. Tính chất : Một trong các từ khóa:

 - ALL (chọn ra tất cả các dòng trong bảng),

- DISTINCT(lọai bỏ các cột trùng lắp thông tin),

- DISTINCTROW (lọai bỏ các dòng trùng lắp thông tin),

- TOP (chọn n dòng đầu tiên thỏa mãn điều kiện).

2. Danh sách các thuộc tính_1: tên các thuộc tính cho biết thông tin cần lấy.
Chú ý: Các thuộc tính cách nhau bởi dấu  ","

     Nếu lấy tất cả các thuộc tính của 1 bảng tbl thì dùng: tbl.*
     Nếu sau FROM chỉ có 1 table và lấy tất cả các field của table đó thì dùng select *
     Nếu tồn tại 1 thuộc tính sau select xuất hiện ở 2 table sau FROM thì phải chỉ định rõ thuộc tính đó thuộc table nào.

3. Danh sách các table: các table chứa thông tin cần lấy. Khi tìm kiếm thông tin trên nhiều hơn 2 table thì phải kết các table lại với nhau (điều kiện kết đặt sau where)

4. Alias: bí danh (tên tắt) của bảng dùng cho các bảng có tên quá dài.

5. Điều kiện_1: là điều kiện để lọc dữ liệu.

6. Danh sách các thuộc tính_2: dữ liệu sẽ được gom nhóm theo các cột này, ưu tiên từ trái sang.

7. Điều kiện_2: điều kiện lọc lại dữ liệu sau khi đã thực hiện tính tóan trên dữ liệu. Điều kiện này được áp dụng trên dữ liệu thỏa mãn điều kiện_1.

8. Danh sách các thuộc tính_3:sắp xếp dữ liệu theo cột nào, thứ tự là tăng (ASC) hoặc giảm (DESC). Mặc định là dữ liệu được sắp theo thứ tự tăng dần. Việc sắp xếp được thực hiện theo thứ tự ưu tiên từ trái qua phải.
II. Truy vấn đơn giản

SELECT

FROM tên_bảng

Sau select, * được dùng với ý nghĩa lấy toàn bộ các cột của bảng.
Dùng từ khoá distinct để loại bỏ các bộ trùng nhau và all để lấy tất cả các bộ dữ liệu. Mặc định không để gì cả chính là có dùng từ khóa all.
Sau select có thể dùng các biểu thức số học như: +, -, *, / ; và có thể thực hiện các toán tử trên thuộc tính.
VD:
Cho biết danh sách tất cả các nhân viên với tất cả các thông tin

SELECT * FORM NHANVIEN

A. Tìm kiếm có sắp xếp

để sắp xếp thứ tự dữ liệu, ta sử dụng mệnh đề ORDER BY:

SELECT ... FROM .... ORDER BY thuộc_tính_1 [ASC|DESC], thuộc_tính_2 [ASC|DESC],...

Tập_thuộc_tính gồm 1 thuộc tính hoặc nhiều thuộc tính và độ ưu tiên tính từ trái sang phải.
 

Với câu lệnh: select * from Table1 order by B desc,A asc trên bảng dưới đây:

A	B
An	8
Binh	8
Chi	9
Hung	10

Ta sẽ được kết quả sau:

A	B
Hung	10
Chi	9
An	8
Binh	8

Đầu tiên là xếp thứ tự theo B trước, sau đó, với những giá trị B ngang nhau thì sẽ xếp theo A.

VD 

+ Cho biết danh sách các nhân viên sắp tên theo thứ tự ALphabet

SELECT MANV,TENNV,PHAI,LUONG

FROM NHANVIEN

ORDER BY TENNV

+ Cho biết danh sách các nhân viên theo từng phòng ban, trong từng phòng ban tên nhân viên sắp thep thứ tự

SELECT PHG,MANV,TENNV,PHAI,LUONG

FROM NHANVIEN

ORDER BY PHG,TENNV

B. Tìm kiếm với điều kiện đơn giản

để hỗ trợ tìm kiếm có điều kiện, sử dụng mệnh đề WHERE trong câu lệnh SELECT với vị trí như sau:

1. AND và OR

SELECT ... FROM ... WHERE (DK1) AND/OR ....(ĐKn)

ví dụ SINHVIEN(MASV,HOTEN,NGSINH,LOP), tìm sinh viên lớp th01 và có năm sinh 2000

SELECT * FROM SINHVIEN WHERE LOP='th01' AND YEAR(NAMSINH)=2000

2. BETWEEN...AND

Cho biết các nhân viên sinh trong khoảng năm 1955 đến 1960

SELECT * FROM NHANVIEN WHERE YEAR(NGSINH) BETWEEN 1955 AND 1960

3. IS NULL VÀ IS NOT NULL

IS NULL và IS NOT NULL : Để kiểm tra một giá trị có phải là NULL | NOT NULL
hay không
Cho biết các nhân viên không có người quản lý trực tiếp
 

SELECT HONV,TENNV FROM NHANVIEN WHERE MANGUOIQUANLY IS NULL

4. IN VÀ NOT IN

IN VÀ NOT IN dùng để kiểm tra một giá trị nằm trong hay Không nằm trong một tập hợp nào đó hay không.
 Cho biết các đơn đặt hàng có đặt mặt hàng H1, H2, H3.

SELECT MADH FORM DONHANG WHERE MAHH IN ('H1','H2','H3')

C. Tìm kiếm có xử lý  xâu ký tự

Để xử lý với các dữ liệu thuộc dạng xâu ký tự, ngôn ngữ SQL có hỗ trợ phép LIKE. Thông thường khi so sánh thuộc tính có kiểu dữ liệu thuộc dạng xâu ký tự thì người ta thường dùng LIKE chứ không dùng phép bằng =
VD:
 Hiện ra các sinh viên tên Trang
SELECT * FROM SINHVIEN WHERE HOTEN LIKE '%Trang'

      %: dùng để đại diện cho nhiều ký tự đứng trước từ 'Trang'.

      Ngoài ra còn các ký tự sau để mô tả mẫu cần tìm: _thay thế cho ký tự bất kỳ

Chú ý:
Like “ab\%cd%” cho ra những chuỗi bắt đầu với “ab%cd”
Like “ab\\cd%” cho ra những chuỗi bắt đầu với “ab\cd”
 

D. Tìm kiếm có điều kiện liên quan đến ngày tháng

VD:
DDH(MADH, NGAYDH, MAKH)
CTDH(MADH, MAHH, SOLUONG, DONGIA)
       Cho biết những đơn đặt hàng đặt trước ngày 01/01/2001
 SELECT MADH,NGAYDH FROM DONHANG WHERE DATEDIFF (D,NGAYDH,'01/01/2001')>0

      Cho biết các nhân viên sinh ngày 30/4/1975
    SELECT * FORM NHANVIEN WHERE DATEDIFF (D, NGAYSINH, '1/1/1965')=0

E. Sử dụng các hàm trong khi tìm kiếm
- Sử dụng hàm trong mệnh đề where
- Sử dụng hàm trong mệnh đề select : Trong mệnh đề select ngoài việc được sử dụng các toán tử như +, -, *, / ta còn có thể sử dụng hàm đối với các thuộc tính.
o Các hàm về ngày tháng
o Các hàm về chuổi
o Các hàm chuyển đổi kiểu dữ liệu
o Các hàm toán học
o …
Để xem thông tin chi tiết về các hàm có thể sử dụng Book Onlines
Cho biết họ tên nhân viên và tuổi của nhân viên
SELECT * ,DATEDIFF(YYYY,NGSINH, GETDATE()) AS TUOI FROM NHANVIEN

CHO BIẾT TÊN ĐẦY ĐỦ CỦA CÁC NHÂN VIÊN

SELECT HONV+ ' ' + TENLOT + '  ' + TENNV AS HOVATEN FROM NHANVIEN

 F. Tìm kiếm từ nhiều bảng

       Để tìm kiếm thông tin mà thông tin đó nằm ở nhiều bảng khác nhau thì khai báo sử dụng các bảng đó tại mệnh đề FROM. Tùy theo thông tin cần hiển thị mà chúng ta sẽ sử dụng điều kiện tại mệnh đề WHERE sao cho thích hợp.
VD:
Cho biết mã nhân viên, tên nhân viên, tên phòng ban mà nhân viên trực thuộc.

SELECT * FROM NHANVIEN, PHONGBAN

WHERE NHANVIEN.MAPB = PHONGBAN.MAPB

G. DÙNG TOÁN TỬ SOME, ALL, EXISTS, NOT EXISTS

III. CÂU TRUY VẤN SỬ DỤNG Group By

1. Các hàm tính toán

SQL sử dụng các hàm sau: Count, Max, Min, Sum, Avg. Hàm Count dùng đối số * có nghĩa là đếm tất cả các mẫu tin thỏa điều kiện đếm mà không cần quan tâm đến bất kỳ cột  nào.
 Có tất cả bao nhiêu sinh viên trong lớp th01

select count(*) from sinhvien where lop='th01'

2. Mệnh đề group by
Dùng để gom nhóm dữ liệu, thường dùng kết hợp với một hàm tính toán kể trên.
+ Tính điểm trung bình của từng sinh viên, biết rằng điểm số lưu trong bảng KETQUA(MASV, MAMH, DIEM)

select masv, avg(diem) from ketqua group by masv

+ cho biết lương lớn nhất trong từng phòng ban

select phg, max(luong) from nhanvien group by ph

3. Mệnh đề Having

Mệnh đề HAVING thường được sử dụng cùng với mệnh đề GROUP BY. Sau HAVING là biểu thức điều kiện. Biểu thức điều kiện này không tác động vào toàn bảng được chỉ ra ở mệnh đề from mà chỉ tác động lần lượt từng nhóm các mẫu tin đã chỉ ra trong mệnh đề group by.
+ Cho biết các sinh viên có điểm trung bình lớn hơn hoặc bằng 8.0
 

select masv,avg(diem) from ketqua group by masv having avg(diem)>=8.0

IV Truy vấn lồng

Ví dự cho danh sách các nhân viên có ít nhất 1 thân nhân

SELECT MANV,TENNV FROM NHANVIEN

WHERE (SELECT COUNT(*)

FROM THANNHAN

WHERE THANNHAN.MA_NVIEN=NHANVIEN.MANV)>0

+ Cho biết sinh viên có điểm trung bình cao nhất

SELECT MASV, AVG(DIEM)

FROM KETQUA

FROUP BY MASV

WHERE AVG(DIEM)>= ALL (SELECT AVG(DIEM)

FROM KETQUA 

GROUP BY MASV)

LỒNG PHÂN CẤP

+ Tìm những nhân viên có lương lớn hơn lương của tất cả nhân viên ở phòng 4

SELECT MANV,TENNV

FROM NHANVIEN

WHERE LUONG > (SELECT MAX(LUONG)

                                FROM NHANVIEN

                               WHERE PHG=4)

LỒNG TƯƠNG QUAN

Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha.
Khi thực hiện, câu truy vấn con sẽ được thực hiện nhiều lần, mỗi lần tương ứng với một bộ của truy vấn cha.

+ Tìm những nhân viên không có thân nhân nào

SELECT MANV,TENNV

FROM NHANVIEN N

WHERE NOT EXITS 

(SELECT * D

FROM THANNHAN T

WHERE T.MANV=N.MANV)

V. PHÉP CHIA

Có 2 cách thực hiện:

Cách 1: Sử dụng NOT EXISTS + NOT IN hoặc NOT EXISTS + NOT EXISTS 

Cách 2: Sử dụng mệnh đề GROUP BY + HAVING

VD:
+ Tìm nhân viên được phân công làm việc trong tất cả các đề án do phòng Nghiên cứu
quản lí
Cách 1:
Sử dụng NOT EXISTS + NOT IN

SELECT N.TENNV

FROM NHANVIEN N

WHERE NOT EXISTS (

                                     SELECT * 

                                     FROM PHONGBAN PV, DEAN D

                                    WHERE D.MAPHONG=PB.MAPHONG

                                    AND PB.TENPG='NGHIÊN CỨU' AND D.MADA NOT IN 

                                                                                                                             (

                                                                                                                              SELECT PC.MADA

                                                                                                                             FROM PHANCONG PC

                                                                                                                            WHERE PC.MA_NVIEN = N.MANV)

                                                                                                                            )

                                        )

 

Sử dụng NOT EXISTS + NOT EXISTS
 

SELECT N.TENNV

FROM NHANVIEN N, PHANCONG PC, PHONGBANPB1, DEAN D1

WHERE N.MANV=PC.MA_NV AND PC.MADA =D1.MADA AND D1.PHONG=PB1.MAPHG AND PB1.TENPHG= ' NGHIÊN CỨU' 

GROUP BY N.MANV,N.TENNV

HAVING COUNT( DISTINCT PB.MADA) = (SELECT COUNT (DISTINCT D2.MADA FROM DEAN D2, PHONGBAN PB2

                                                                     WHERE D2.PHONG=PB2.MAPHG AND PB2.TENPHG = ' NGHIÊN CỨU)

 

VI. CẤU TRÚC CASE

cho biết họ tên nhan vên đã đến tuổi về hưu ( nam 60 nữ 55)

SELECT TENNV

FROM NHANVIEN

WHERE YEAR(GETDATE()- YEAR(NGSINH)>= (CASE PHAI WHEN 'Nam' THEN 60 WHEN 'Nu' THEN 55 END)