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

Chương 3 HÀM SQL

- Lý thuyết: 3 tiết

3- 1

Chương 3. Hàm SQL

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

Hàm SQL

 chương trình con thực hiện một chức năng nào đó

 trả về chỉ một giá trị

3- 2

Chương 3. Hàm SQL

3.1 Tổng quan về hàm SQL

Hàm SQL

 Phân loại

 từng dòng dữ liệu: trả về một giá trị trên một dòng

 nhóm các dòng dữ liệu: trả về một giá trị trên một nhóm dòng

3- 3

Chương 3. Hàm SQL

3.1 Tổng quan về hàm SQL

Hàm SQL

 kiểu dữ liệu số

 kiểu dữ liệu ký tự

 kiểu dữ liệu thời gian

 Các hàm chuyển đổi kiểu

3- 4

Chương 3. Hàm SQL

3.2 Các hàm trên hàng đơn

Hàm SQL

 Hàm ROUND(n[,m])

 làm tròn n đến m chư˜ số thập phân (mặc định m=0)

 m<0 thì làm tròn số bên trái dấu thập phân

3.2.1 Các hàm trên kiểu số

ROUND(4.923,1), kết quả là 4.9

ROUND(4.923,-1), kết quả là 0

ROUND(6.923,-1), kết quả là 10

3- 5

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm TRUNC(n[,m])

 lấy m chữ số tính từ chấm thập phân (m=0).

 m<0 thì cắt bỏ m chư˜ số bên trái dấu thập phân.

3.2.1 Các hàm trên kiểu số

TRUNC (4.923,1), kết quả là 4.9.

TRUNC(4.923), kết quả là 4.

TRUNC(4.923,-1), kết quả là 0

3- 6

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm CEIL(n)

 trả về số nguyên nhỏ nhất lớn hơn hoặc bằng n

3.2.1 Các hàm trên kiểu số

CEIL(4.923), kết quả là 5

 Hàm FLOOR(n)

 trả về số nguyên lớn nhất nhỏ hơn hoặc bằng n

Ví dụ

3- 7

Chương 3. Hàm SQL

FLOOR(4.923), kết quả là 4

Hàm SQL

 Hàm SQRT(n)

 trả về căn bậc 2 của n, với n>=0

3.2.1 Các hàm trên kiểu số

SQRT(16), kết quả là 4

 Hàm POWER(m,n)

 trả về lũy thừa bậc n của m

Ví dụ

3- 8

Chương 3. Hàm SQL

POWER(2,3), kết quả là 8

Hàm SQL

 Hàm SIGN(n)

 Nếu n<0 thì SIGN(n)= -1

 Nếu n=0 thì SIGN(n)= 0

 Nếu n>0 thì SIGN(n)= 1

3.2.1 Các hàm trên kiểu số

SIGN(1724)=1.

SIGN(0)=0.

SIGN(-1724)=-1.

3- 9

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm ABS(n)

ABS(127)=127.

ABS(-127)=127.

 Hàm MOD(m,n)

 trả về phần dư của phép chia m cho n.

3.2.1 Các hàm trên kiểu số

MOD(5,3)=2.

MOD(4,3)=1.

3- 10

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Một số hàm khác

3.2.1 Các hàm trên kiểu số

Hàm SQL LOG(m,n)

Diễn giải Cho logarit cơ số m của n.

SIN(n) COS(n)

Trả về sin của n (n tính bằng radian). Cho cosin của n (n tính bằng radian).

TAN(n)

Trả về tang của n (n tính bằng radian).

3- 11

Chương 3. Hàm SQL

Hàm SQL

 Hàm CONCAT(char1, char2)

 kết hợp của 2 chuỗi ký tự char1 và char2

3.2.2 Các hàm trên kiểu ký tự

CONCAT(‘SQL’, ‘ORACLE’), kết quả là SQLORACLE.

 Hàm INITCAP(char)

 chuỗi với ký tự đầu các từ là ký tự hoa.

Ví dụ

INITCAP(oracle), kết quả là Oracle

3- 12

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm LOWER(char)

 chuỗi ký tự viết thường

3.2.3 Các hàm trên kiểu ký tự

Ví dụ

 Hàm UPPER(char)

 chuỗi ký tự viết hoa.

LOWER(‘ORACLE’), kết quả là oracle.

Ví dụ

3- 13

Chương 3. Hàm SQL

UPPER(oracle), kết quả là ORACLE

Hàm SQL

 Hàm REPLACE(char, search [,replace])

 Thay chuỗi search có trong char bằng chuỗi replace

3.2.3 Các hàm trên kiểu ký tự

REPLACE(‘SALESMAN’, ‘MAN’, ’ ’);

Kết quả là SALES

 Hàm ASCII(char)

 ký tự ASCII của byte đầu tiên của chuỗi char. Ví dụ

ASCII(‘TOI’); kết quả là 84

3- 14

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm SUBSTR(char, m [,n])

 chuỗi con lấy từ vị trí m về bên phải n ký tự,

 nếu không chỉ định n thì lấy cho đến cuối chuỗi

3.2.3 Các hàm trên kiểu ký tự

SUBSTR(‘ORACLE’,2,4); kết quả là RACL.

SUBSTR(‘ORACLE’,2); kết quả là RACLE.

3- 15

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm INSTR(char1, char2 [,n[,m]])

 Tìm vị trí char2 trong char1 từ vị trí n, lần xuất hiện thứ m

3.2.3 Các hàm trên kiểu ký tự

INSTR(‘ACCOUNTING’,'A'); kết quả là 1.

 Hàm LENGTH(char)

 chiều dài của chuỗi char

Ví dụ

LENGTH(‘SQL COURSE’), kết quả là 10.

3- 16

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm MONTHS_BETWEEN(d1, d2)

 số tháng giữa ngày d1 và d2

3.2.3 Các hàm trên kiểu thời gian

kết

MONTHS_BETWEEN('01-01-2000','01-05-2000'); quả 5.

 Hàm ADD_MONTHS(d, n)

 ngày d sau khi thêm n tháng

Ví dụ

ADD_MONTHS('01-05-2000’, 3); kết quả là 01-08-2000 3- 17

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm NEXT_DAY(d, char)

 ngày tiếp theo ngày d có thứ chỉ bởi char

3.2.3 Các hàm trên kiểu thời gian

NEXT_DAY(‘17-11-2000’,’Friday’); kết quả là 24-11- 2000.

 Hàm LAST_DAY(d)

 ngày cuối cùng trong tháng chỉ bởi d.

Ví dụ

LAST_DAY(‘17-11-2000’); kết quả là 30

3- 18

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

Diễn giải

ROUND(date1)

Trả về ngày date1 tại thời điểm giữa trưa 12:00 AM

ROUND(date1,’Month’)

Nếu date1 nằm trong nửa tháng đầu trả về ngày đầu tiên của tháng, ngược lại sẽ trả về ngày đầu tiên của tháng sau.

ROUND(date1,’Year’)

Nếu date1 nằm trong nửa năm đầu trả về ngày đầu tiên của tháng, ngược lại sẽ trả về ngày đầu tiên của năm sau.

TRUNC(date1, ’Month’)

Trả về ngày đầu tiên của tháng chứa date1

TRUNC(date1, ’Year’)

Trả về ngày đầu tiên của năm chứa date1

3- 19

Chương 3. Hàm SQL

3.2.3 Các hàm trên kiểu thời gian

Hàm SQL

 Hàm TO_CHAR(number|date, ‘fmt’)

 Chuyển kiểu số và ngày về kiểu ký tự theo định dạng fmt

3.2.4 Các hàm chuyển đổi kiểu

TO_CHAR(‘17-11-2000’,’DD/MM’); kết quả là 17/11.

TO_CHAR(‘17-11-2005’,’MM/YY’); kết quả là 11/05.

 Hàm TO_NUMBER(char)

 Chuyển chuỗi ký tự có nội dung số sang số

TO_NUMBER(‘12345’); kết quả là 12345.

3- 20

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm TO_DATE(‘char’,’fmt’)

 Chuyển ký tự sang kiểu ngày với định dạng fmt.

3.2.4 Các hàm chuyển đổi kiểu

TO_DATE(‘17-11-2005’,’dd-mm-yy’); kết quả 17-11-05

 Hàm GREATEST(value1, value2,…)

 giá trị lớn nhất trong dãy giá trị

Ví dụ

GREATEST(12, 45, 6, 78); kết quả là 78.

3- 21

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm DECODE(Expr, Search1, Result1, Default)

 trả về giá trị result1nếu expr = search1

 trả về giá trị default nếu expr <> search1

3.2.4 Các hàm chuyển đổi kiểu

DECODE(‘SALE’, ‘SALE’, 500,0); kết quả là 500.

DECODE(‘SALE’, ‘MANAGE’, 500,0); kết quả là 0.

3- 22

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm NULLIF

3.3 Một số hàm bổ sung

 trả về giá trị NULL nếu expr1= expr2.

 trả về giá trị của expr1 nếu ngược lại

NULLIF(expr1, expr2)

NULLIF(Length(‘ORA’), Length(‘ORA’)); kết quả là NULL

NULLIF(Length(‘SQL’), Length(‘ORAC’)); kết quả 3

3- 23

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Hàm COALESCE

3.3 Một số hàm bổ sung

 trả về giá trị của tham số đầu tiên khác NULL

COALESCE(expr1, expr2, …exprn)

Giả sử v1=null; v2=null, v3=1, v4=2.

COALESCE(v1, v2, v3)=1;

COALESCE(v1, v2, v4, v3)=2;

3- 24

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Câu lệnh CASE CASE expr

3.3 Một số hàm bổ sung

WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr]

 so sánh expr lần lượt với các comparison_exprx

 trả về return_expr tương ứng

3- 25

Chương 3. Hàm SQL

END

Hàm SQL

 Câu lệnh CASE

3.3 Một số hàm bổ sung

CASE LENGTH(‘SQL’)

WHEN 2 THEN “Do dai bang 2.”

WHEN 3 THEN “Do dai bang 3.”

WHEN 4 THEN “Do dai bang 4.”

ELSE “Khong thoa man.”

3- 26

Chương 3. Hàm SQL

Ví dụ

Hàm SQL

 Câu lệnh CASE

3.3 Một số hàm bổ sung

Ví dụ

CASE Job_Id WHEN ‘IT_PROG’ THEN 0.5*Sal,

WHEN ‘ST_SALE’ THEN 0.6*Sal,

WHEN ‘SA_MAN’ THEN 0.7*Sal,

ELSE Sal

END “AddSal” FROM Emp;

3- 27

Chương 3. Hàm SQL

SELECT Ename, Job_Id, Sal,

Hàm SQL

 Câu lệnh CASE

3.3 Một số hàm bổ sung

Ví dụ

CASE Job_Id WHEN ‘IT_PROG’ THEN 0.5*Sal,

WHEN ‘ST_SALE’ THEN 0.6*Sal,

WHEN ‘SA_MAN’ THEN 0.7*Sal,

ELSE Sal

END “AddSal” FROM Emp;

3- 28

Chương 3. Hàm SQL

SELECT Ename, Job_Id, Sal,

Hàm SQL

 Hàm tác động trên nhóm

Hàm SQL

Diễn giải

AVG([DISTINCT/ALL] n)

Giá trị trung bình của n, không kể trị null

COUNT([DISTINCT/ALL] expr)

Số hàng có expr khác null

MAX([DISTINCT/ALL] expr)

Giá trị lớn nhất của expr

MIN([DISTINCT/ALL] expr)

Giá trị nhỏ nhất của expr

STDDVE([DISTINCT/ALL] n)

Phương sai của n không kể trị null

SUM([DISTINCT/ALL] n)

Tổng của của n không kể trị null

VARIANCE([DISTINCT/ALL] n)

Variance của n không kể trị null

3- 29

Chương 3. Hàm SQL

3.4 Các hàm gộp nhóm dữ liệu

Hàm SQL

 Mệnh đề GROUP BY

3.4 Các hàm gộp nhóm dữ liệu

SELECT [DISTINCT ] {*, column [alias],...}

FROM table;

[WHERE condition]

[GROUP BY expr]

[HAVING condition]

3- 30

Chương 3. Hàm SQL

[ORDER BY expr/position [DESC/ASC]]

Hàm SQL

 Mệnh đề GROUP BY

 GROUP BY nhóm các dòng dữ liệu có cùng giá trị expr.

 HAVING dùng để đặt điều kiện cho nhóm dữ liệu.

3.4 Các hàm gộp nhóm dữ liệu

SELECT Job, MAX(Sal)

Kết quả

FROM Emp

JOB

MAX(SAL)

GROUP BY Job;

ANALYST PRESIDENT

3000 5000

HAVING MAX(Sal)>=3000

3- 31

Chương 3. Hàm SQL

Ví dụ

Câu lệnh SQL

 Trả lời câu hỏi phần 3.5 trong giáo trình

 Thực hành viết các câu lệnh truy vấn trong phần bài tập

 Chuẩn bị nội dung chương 4.

3- 32

Chương 3. Hàm SQL

Câu hỏi và bài tập

Bài giảng Cơ sở dữ liệu quan hệ và SQL: Chương 3 - CĐ CNTT Hữu nghị Việt Hàn