Chương 3 Hàm SQL

Chương 3. Hàm SQL 3- 1 Chương 3 HÀM SQL - Lý thuyết: 3 tiết - Thực hành: 2 tiết Chương 3. Hàm SQL 3- 2 3.1 Tổng quan về hàm SQL  chương trình con thực hiện một chức năng nào đó  trả về chỉ một giá trị Hàm SQL Chương 3. Hàm SQL 3- 3 3.1 Tổng quan về hàm SQL  Phân loại  từng dòng dữ liệu: trả về một giá trị trên một dòng  nhóm các dòng dữ liệu: trả về một giá trị trên một nhóm dòng Hàm SQL Chương 3. Hàm SQL 3- 4 3.2 Các hàm trên hàng đơn  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 Hàm SQL Chương 3. Hàm SQL 3- 5 3.2.1 Các hàm trên kiểu số  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 thì làm tròn số bên trái dấu thập phân Ví dụ 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 Hàm SQL Chương 3. Hàm SQL 3- 6 3.2.1 Các hàm trên kiểu số  Hàm TRUNC(n[,m])  lấy m chữ số tính từ chấm thập phân (m=0).  m<0 thì cắt bỏ m chư˜ số bên trái dấu thập phân. Ví dụ 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 Hàm SQL Chương 3. Hàm SQL 3- 7 3.2.1 Các hàm trên kiểu số  Hàm CEIL(n)  trả về số nguyên nhỏ nhất lớn hơn hoặc bằng n Ví dụ 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ụ FLOOR(4.923), kết quả là 4 Hàm SQL Chương 3. Hàm SQL 3- 8 3.2.1 Các hàm trên kiểu số  Hàm SQRT(n)  trả về căn bậc 2 của n, với n>=0 Ví dụ 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ụ POWER(2,3), kết quả là 8 Hàm SQL Chương 3. Hàm SQL 3- 9 3.2.1 Các hàm trên kiểu số  Hàm SIGN(n)  Nếu n<0 thì SIGN(n)= -1  Nếu n=0 thì SIGN(n)= 0  Nếu n>0 thì SIGN(n)= 1 Ví dụ SIGN(1724)=1. SIGN(0)=0. SIGN(-1724)=-1. Hàm SQL Chương 3. Hàm SQL 3- 10 3.2.1 Các hàm trên kiểu số  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. Ví dụ MOD(5,3)=2. MOD(4,3)=1. Hàm SQL Chương 3. Hàm SQL 3- 11 3.2.1 Các hàm trên kiểu số  Một số hàm khác Hàm SQL Trả về tang của n (n tính bằng radian).TAN(n) Cho cosin của n (n tính bằng radian). COS(n) Trả về sin của n (n tính bằng radian).SIN(n) Cho logarit cơ số m của n.LOG(m,n) Diễn giải Hàm SQL Chương 3. Hàm SQL 3- 12 3.2.2 Các hàm trên kiểu ký tự  Hàm CONCAT(char1, char2)  kết hợp của 2 chuỗi ký tự char1 và char2 Ví dụ 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 Hàm SQL Chương 3. Hàm SQL 3- 13 3.2.3 Các hàm trên kiểu ký tự  Hàm LOWER(char)  chuỗi ký tự viết thường Ví dụ LOWER(‘ORACLE’), kết quả là oracle.  Hàm UPPER(char)  chuỗi ký tự viết hoa. Ví dụ UPPER(oracle), kết quả là ORACLE Hàm SQL Chương 3. Hàm SQL 3- 14 3.2.3 Các hàm trên kiểu ký tự  Hàm REPLACE(char, search [,replace])  Thay chuỗi search có trong char bằng chuỗi replace Ví dụ 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 Hàm SQL Chương 3. Hàm SQL 3- 15 3.2.3 Các hàm trên kiểu ký tự  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 Ví dụ SUBSTR(‘ORACLE’,2,4); kết quả là RACL. SUBSTR(‘ORACLE’,2); kết quả là RACLE. Hàm SQL Chương 3. Hàm SQL 3- 16 3.2.3 Các hàm trên kiểu ký tự  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 Ví dụ 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. Hàm SQL Chương 3. Hàm SQL 3- 17 3.2.3 Các hàm trên kiểu thời gian  Hàm MONTHS_BETWEEN(d1, d2)  số tháng giữa ngày d1 và d2 Ví dụ MONTHS_BETWEEN('01-01-2000','01-05-2000'); kết 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 Hàm SQL Chương 3. Hàm SQL 3- 18 3.2.3 Các hàm trên kiểu thời gian  Hàm NEXT_DAY(d, char)  ngày tiếp theo ngày d có thứ chỉ bởi char Ví dụ 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 Hàm SQL Chương 3. Hàm SQL 3- 19 3.2.3 Các hàm trên kiểu thời gian Hàm SQL Trả về ngày đầu tiên của năm chứa date1 TRUNC(date1, ’Year’) Trả về ngày đầu tiên của tháng chứa date1TRUNC(date1, ’Month’) 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. ROUND(date1,’Year’) 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,’Month’) Trả về ngày date1 tại thời điểm giữa trưa 12:00 AM ROUND(date1) Diễn giảiHàm SQL Chương 3. Hàm SQL 3- 20 3.2.4 Các hàm chuyển đổi kiểu  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 Ví dụ 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. Hàm SQL Chương 3. Hàm SQL 3- 21 3.2.4 Các hàm chuyển đổi kiểu  Hàm TO_DATE(‘char’,’fmt’)  Chuyển ký tự sang kiểu ngày với định dạng fmt. Ví dụ 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. Hàm SQL Chương 3. Hàm SQL 3- 22 3.2.4 Các hàm chuyển đổi kiểu  Hàm DECODE(Expr, Search1, Result1, Default)  trả về giá trị result1nếu expr = search1  trả về giá trị default nếu expr search1 Ví dụ DECODE(‘SALE’, ‘SALE’, 500,0); kết quả là 500. DECODE(‘SALE’, ‘MANAGE’, 500,0); kết quả là 0. Hàm SQL Chương 3. Hàm SQL 3- 23 3.3 Một số hàm bổ sung  Hàm NULLIF NULLIF(expr1, expr2)  trả về giá trị NULL nếu expr1= expr2.  trả về giá trị của expr1 nếu ngược lại Ví dụ NULLIF(Length(‘ORA’), Length(‘ORA’)); kết quả là NULL NULLIF(Length(‘SQL’), Length(‘ORAC’)); kết quả 3 Hàm SQL Chương 3. Hàm SQL 3- 24 3.3 Một số hàm bổ sung  Hàm COALESCE COALESCE(expr1, expr2, …exprn)  trả về giá trị của tham số đầu tiên khác NULL Ví dụ Giả sử v1=null; v2=null, v3=1, v4=2. COALESCE(v1, v2, v3)=1; COALESCE(v1, v2, v4, v3)=2; Hàm SQL Chương 3. Hàm SQL 3- 25 3.3 Một số hàm bổ sung  Câu lệnh CASE CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END  so sánh expr lần lượt với các comparison_exprx  trả về return_expr tương ứng Hàm SQL Chương 3. Hàm SQL 3- 26 3.3 Một số hàm bổ sung  Câu lệnh CASE Ví dụ 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.” Hàm SQL Chương 3. Hàm SQL 3- 27 3.3 Một số hàm bổ sung  Câu lệnh CASE Ví dụ SELECT Ename, Job_Id, Sal, 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; Hàm SQL Chương 3. Hàm SQL 3- 28 3.3 Một số hàm bổ sung  Câu lệnh CASE Ví dụ SELECT Ename, Job_Id, Sal, 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; Hàm SQL Chương 3. Hàm SQL 3- 29 3.4 Các hàm gộp nhóm dữ liệu  Hàm tác động trên nhóm Hàm SQL Variance của n không kể trị nullVARIANCE([DISTINCT/ALL] n) Tổng của của n không kể trị null SUM([DISTINCT/ALL] n) Phương sai của n không kể trị nullSTDDVE([DISTINCT/ALL] n) Giá trị nhỏ nhất của exprMIN([DISTINCT/ALL] expr) Giá trị lớn nhất của exprMAX([DISTINCT/ALL] expr) Số hàng có expr khác nullCOUNT([DISTINCT/ALL] expr) Giá trị trung bình của n, không kể trị null AVG([DISTINCT/ALL] n) Diễn giảiHàm SQL Chương 3. Hàm SQL 3- 30 3.4 Các hàm gộp nhóm dữ liệu  Mệnh đề GROUP BY SELECT [DISTINCT ] {*, column [alias],...} FROM table; [WHERE condition] [GROUP BY expr] [HAVING condition] [ORDER BY expr/position [DESC/ASC]] Hàm SQL Chương 3. Hàm SQL 3- 31 3.4 Các hàm gộp nhóm dữ liệu  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. Ví dụ SELECT Job, MAX(Sal) FROM Emp GROUP BY Job; HAVING MAX(Sal)>=3000 Hàm SQL Kết quả JOB MAX(SAL) ANALYST 3000 PRESIDENT 5000 Chương 3. Hàm SQL 3- 32 Câu hỏi và bài tập  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. Câu lệnh SQL