ĐĂNG KÝ ĐỂ NHẬN THÔNG TIN MỚI NHẤT

Function – Tự học SQL cơ bản (phần 3) – Sử dụng các hàm hữu ích trong SQL

Function – Tự học SQL cơ bản (phần 3) – Sử dụng các hàm hữu ích trong SQL

Trong bài viết thứ 3 của Chương trình tự học SQL cơ bản, anh em sẽ được tìm hiểu với các nội dung sau:

  • Sử dụng các hàm cơ bản có sẵn trong SQL.
  • Các hàm cho kiểu dữ liệu chuỗi và số phổ biến trong SQL.
  • Xử lý giá trị NULL trong SQL.

1. Function làm việc với chuỗi ký tự trong SQL

UPPER và LOWER trong SQL

Trên các Hê CSDL đều có 2 hàm sau đây thường được sử dụng phổ biến với chuỗi trong SQL là UPPER và LOWER. Đúng như tên gọi của chúng, 2 hàm này dùng để in hoa và in thường các chuỗi trong SQL.

Ví dụ hiển thị danh sách nhân viên với tên viết hoa với UPPER

SELECT UPPER(first_name) 
FROM employees;

Hoặc hiển thị mã công việc viết thường với LOWER

SELECT LOWER(job_id) 
FROM employees;

Nhưng tiện ích của UPPER và LOWER không chỉ dừng lại ở đó. Với các Hệ CSDL như OraclePostgreSQL, nếu muốn so sánh với chuỗi ta sẽ phải ghi chính xác chuỗi so sánh với chuỗi ở trong cột thì CSDL mới tìm được giá trị tương ứng trong bảng.

Không giống như SQL ServerMySQL, mọi ký tự trong chuỗi đều được xử lý như nhau bất kể viết hoa hay thường khi thực hiện so sánh. 

Ví dụ câu lệnh sau sẽ trả về kết quả trong SQL ServerMySQL nhưng lại không trả ra bản ghi nào trong OraclePostgreSQL vì giá trị trong cột JOB_ID đều được viết hoa.

SELECT * FROM employees
WHERE job_id LIKE 'fi%'
ORDER BY 1;

Để đơn giản hóa vấn đề này trong OraclePostgreSQL, ta chỉ cần biến toàn bộ chuỗi trong cột thành viết hoa hoặc viết thường, sau đó thực hiện so sánh với chuỗi như thông thường.

SELECT * FROM employees
WHERE LOWER(job_id) LIKE 'fi%'
ORDER BY 1;

Hoặc với ví dụ sau tìm thông tin nhân viên tên “John” bằng cách viết hoa hết cột chuỗi nhâp vào trước khi so sánh với cột.

SELECT * FROM employees
WHERE first_name = UPPER('john')
ORDER BY 1;

Cắt ghép và chỉnh sửa chuỗi với function trong SQL

Trước đó ở bài viết đầu tiên ta đã biết đến hàm CONCAT của MySQL . CONCAT cũng tồn tại trên các Hệ CSDL khác, có cú pháp giống nhau và đều dùng để ghép 2 hay nhiều chuỗi ký tự.

SELECT UPPER(CONCAT(last_name, ' ', first_name)) 
FROM employees
ORDER BY 1;

Với Oracle, CONCAT chỉ ghép được 2 chuỗi với nhau 1 lúc nên ta phải lồng nhiều hàm CONCAT với nhau nếu muốn ghép nhiều chuỗi.

SELECT CONCAT(CONCAT(last_name, ' '), first_name) 
FROM employees
ORDER BY 1;

Một hàm khác cũng hay được dùng với chuỗi trong SQL là REPLACE

  • REPACE(chuỗi gốc>, <chuỗi con cũ>, <chuỗi con mới>)

REPLACE sẽ thay thế tất cả chuỗi con xuất hiện trong chuỗi gốc bằng một chuỗi con mới. Ví dụ sau sẽ thay thế chữ “IT” thành “Information Technology” trong mô tả công việc của các nhân viên.

SELECT REPLACE(job_id, 'IT', 'Information Technology') 
FROM employees;

Hoặc với function TRIM, ta có thể loại bỏ các ký tự không mong muốn khỏi đầu và đuôi của chuỗi. Ví dụ muốn bỏ ký tự trống trong chuỗi.

SELECT TRIM(CONCAT('      ', last_name, ' ', first_name, '        ')) 
FROM employees;

Còn nhiều các hàm khác trên các Hệ CSDL để làm việc với chuỗi nhưng chúng ta chỉ quan tâm đến một vài hàm phổ biến thường được sử dụng khi viết các câu lệnh truy vấn với SQL. Anh em có thể tham khảo thêm dưới các đường link sau đây.

2. Function làm việc với số trong SQL

Sử dụng bảng DUAL trong SQL

Trước khi tìm hiểu các function làm việc với số trong SQL, ta sẽ tìm hiểu các câu lệnh SELECT mà không cần chỉ định cụ thể bảng trên các Hệ CSDL. Ví dụ như bây giờ muốn thực hiện một phép tính trên CSDL với SELECT.

SELECT 1 + 2;
SELECT 3.5 - 4.7;

Với SQL ServerPostgreSQL, ta chỉ cần viết từ khóa SELECT sau đó là biểu thức cần tính toán. Ta cũng có thể hiển thị một chuỗi bất kỳ với SELECT như dưới đây.

SELECT 'Hello World!!!' AS greeting;

Với OracleMySQL, ta cần có thêm FROM DUAL sau câu lệnh SELECT. DUAL thực chất là một bảng ảo trên CSDL cho phép người dùng thực hiện những biểu thức hoặc gọi đến một function nào đó. Bảng DUAL không tồn tại trên SQL ServerPostgreSQL

SELECT 1 + 2 FROM DUAL;
SELECT 3.5 - 4.7 FROM DUAL;
SELECT 'Hello World!!!' AS greeting FROM DUAL;

Sau đây ta sẽ sử dụng những câu lệnh như trên để xem chức năng của một vài function phổ biến hay dùng với kiểu dữ liệu dạng số trong SQL một cách trực quan hơn.

Làm tròn giá trị với function trong SQL

Giống như kiểu chuỗi, dữ liệu số trong SQL cũng có rất nhiều hàm hỗ trợ khi thực hiện truy vấn với SQL. Ví dụ các hàm sau có chức năng làm tròn số trong SQL là CEIL, FLOOR và ROUND.

CEIL trong có chức năng làm tròn giá trị lên đến số nguyên nhỏ nhất mà lớn hơn hoặc bằng giá trị trong function.

-- Oracle, MySQL
SELECT CEIL(3.7) FROM DUAL;
SELECT CEIL(4.2) FROM DUAL;

Với SQL ServerPostgreSQL, function CEIL() có tên đầy đủ là CEILING().

SELECT CEILING(3.7);
SELECT CEILING(4.2);

Ngược lại với CEIL là FLOOR, nghĩa là làm tròn giá trị xuống số nguyên lớn nhất, mà nhỏ hơn hoặc bằng giá trị trong function.

-- SQL Server, PostgreSQL
SELECT FLOOR(3.7);
SELECT FLOOR(4.2);

-- Oracle, MySQL
SELECT FLOOR(3.7) FROM DUAL;
SELECT FLOOR(4.2) FROM DUAL;

Cả CEIL là FLOOR chỉ làm tròn các giá trị thành số nguyên. Nếu muốn làm tròn giá trị đến các số thập phân ta sẽ dùng đến ROUND.

  • ROUND(<giá trị>, <số thập phân muốn làm tròn đến sau dấu phẩy>)
-- SQL Server, PostgreSQL
SELECT ROUND(3.777777, 3);
SELECT ROUND(4.23, 1);

-- Oracle, MySQL
SELECT ROUND(3.774, 2) FROM DUAL;
SELECT ROUND(6.789, 5) FROM DUAL;

ROUND cũng là function được sử dụng thường xuyên nhất khi làm việc với dữ liệu kiểu số trong các câu lệnh truy vấn với SQL.

Khác với ROUND, ta có function TRUNC chỉ cắt giá trị đến số thập phân sau dấy phẩy được chỉ định chứ không làm tròn như ROUND. TRUNC không tồn tại trên Hệ CSDL SQL ServerMySQL.

SELECT TRUNC(3.776, 2) FROM DUAL;
SELECT TRUNC(6.7895, 3) FROM DUAL;

Các function trên đều có thể sử dụng với giá trị trong bảng thông thường, miễn có cùng kiểu dữ liệu là số để CSDL có thể thực hiện tính toán được.

SELECT
   employee_id,
   ROUND((salary * 2 + 3000)/3, 2) AS bonus
FROM employees;

Còn nhiều các hàm khác trên các Hệ CSDL để làm việc với số nhưng chúng ta chỉ quan tâm đến một vài hàm phổ biến thường được sử dụng khi viết các câu lệnh truy vấn với SQL. Anh em có thể tham khảo thêm dưới các đường link sau đây.

3. Function xử lý dữ liệu NULL trong SQL

Dữ liệu dạng NULL trong SQL cũng có các function riêng để xử lý trên các Hệ CSDL. Vì NULL không thực sự chứa dữ liệu gì bên trong cả nên khi làm việc với các kiểu dữ liệu khác có thể sẽ gây nhầm lẫn nếu không được xử lý cẩn thận.

Với Oracle, ta có NVL dùng để thay thế giá trị NULL trong cột bằng một giá trị khác cùng kiểu dữ liệu được người dùng chỉ định.

SELECT 
   department_id, department_name,
   NVL(manager_id, 0) AS manager_id
FROM departments;

Với SQL ServerMySQL, ta có ISNULL và IFNULL thực hiện chức năng tương tự NVL của Oracle.

-- SQL Server
SELECT 
   department_id, department_name,
   ISNULL(manager_id, 0) AS manager_id
FROM departments;

-- MySQL
SELECT 
   department_id, department_name,
   IFNULL(manager_id, 0) AS manager_id
FROM departments;

Với PostgreSQL, ta sử dụng function COALESCE

SELECT 
   department_id, department_name,
   COALESCE(manager_id, 0) AS manager_id
FROM departments;

Điểm khác biệt của COALESCE là nó có thể nhận cùng lúc nhiều giá trị sau đó trả về giá trị đầu tiên khác NULL trong danh sách các giá trị đó. COALESCE là function có tên theo tiêu chuẩn chung của SQL và cũng có mặt trên các Hệ CSDL khác.

Một loại function nữa cũng thường được sử dụng khi truy vấn với SQL là NULLIF. NULLIF sẽ so sánh 2 giá trị trong function và trả về NULL nếu chúng bằng nhau, ngược lại sẽ trả về giá trị thứ nhất nếu khác nhau.

Ví dụ sau sẽ lấy danh sách nhân viên với mã quản lý của mình, với những ai không có mã quản lý (NULL) thì hiển thị mã quản lý chính là mã nhân viên của mình

SELECT
   employee_id, first_name,
   NULLIF(employee_id, manager_id) AS manager_id
FROM employees

Khi truy vấn dữ liệu, đôi khi có thể ta sẽ muốn lấy cả các bản ghi chứa cả chuỗi rỗng thay lấy vì mỗi NULL, vì trong thế giới thực ta cũng coi giá trị rỗng và NULL là như nhau. Nhưng trong CSDL 2 giá trị này là khác nhau, nên điều kiện WHERE <tên cột> IS NULL là chua đủ.

Để khắc phục vấn đề trên ta có thể sử dụng function NULLIF() như sau. Ví dụ lấy danh sách nhân viên không có email, cả NULL lẫn chuỗi rỗng

SELECT 
   first_name, last_name, email 
FROM employees 
WHERE NULLIF(email,'') IS NULL;

Còn nhiều các hàm khác trên các Hệ CSDL để làm việc với giá trị NULL nhưng chúng ta chỉ quan tâm đến một vài hàm phổ biến thường được sử dụng khi viết các câu lệnh truy vấn với SQL. Anh em có thể tham khảo thêm dưới các đường link sau đây.

Tiếp theo

Tự học SQL cơ bản (phần 4)

Liên Hệ với Wecommit

Nếu bạn muốn liên hệ với Wecommit, bạn có thể trao đổi trực tiếp qua Facebook cùng Trần Quốc Huy – người sáng lập của Wecommit.

Nếu bạn muốn chúng tôi đồng hành xây dựng sự nghiệp với bạn trong 01 năm (bạn sẽ có rất nhiều kiến thức, kinh nghiệm ĐỘC QUYỀN về tối ưu cơ sở dữ liệu) bạn có thể đăng ký trải nghiệm chương trình Từ điển tối ưu 100x hiệu năng của chúng tôi.

Views: 111





    Câu hỏi bảo mật

    Trả lời

    Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *