Ngôn ngữ truy vấn có cấu trúc hay SQL là một ngôn ngữ dựa trên các truy vấn đơn giản, dễ đọc và dễ viết. Đây là một trong những ngôn ngữ phổ biến nhất trên thế giới.
Trong thế giới ngày nay, tất cả chúng ta đều biết dữ liệu quan trọng như thế nào. Trong bài viết này, chúng ta sẽ tìm hiểu các truy vấn SQL phổ biến nhất thông qua các ví dụ.
Mục đích của bài viết này là để cho bạn biết các truy vấn cơ bản và nâng cao được sử dụng trong khoa học dữ liệu.
Lưu ý: Tất cả các truy vấn được viết bằng PostgreSQL.
10 câu lệnh SQL bạn cần ghi nhớ
1. Chọn tất cả các cột
Nếu bạn muốn chọn tất cả các trường có sẵn trong bảng, hãy sử dụng cú pháp sau: * giúp chúng ta chọn tất cả các cột trong bảng.
SELECT * FROM employees
2. Where
Chúng ta có thể sử dụng mệnh đề WHERE, mệnh đề này lọc dữ liệu dựa trên câu lệnh đã cho.
Ví dụ: Viết câu truy vấn để in ra tất cả các biến cho Sports.
SELECT * FROM employees
WHERE department = 'Sports'
3. Group by và mệnh đề Having
Mệnh đề Group By nhóm các hàng có cùng giá trị.
Ví dụ: Tổng lương cho từng bộ phận là bao nhiêu?
SELECT SUM(salary) as total_salary,department FROM employees GROUP BY department
Trong SQL, không thể sử dụng các hàm tổng hợp như SUM, AVG, MAX, MIN và COUNT trong mệnh đề WHERE. Nếu chúng ta muốn lọc bảng của mình bằng một hàm tổng hợp, chúng ta cần sử dụng mệnh đề HAVING.
Ví dụ: Những phòng ban nào có hơn 50 nhân viên?
SELECT count(*) as total_employee,department FROM employees GROUP BY department HAVING COUNT(*) > 50
4. Order By và Limit
Ví dụ: Tìm tổng lương theo từng bộ phận và sắp xếp giảm dần theo cột tổng lương.
SELECT SUM(salary) as total_salary,department FROM employees GROUP BY department ORDER BY total_salary desc
Lệnh Limit được sử dụng để chỉ định số lượng bản ghi cần trả về.
Ví dụ: Viết một câu truy vấn tìm 5 nhân viên đầu tiên có first_name, bộ phận và mức lương của họ và được sắp xếp theo first_name.
Lưu ý: Mệnh đề Order By mặc định sắp xếp kết quả theo thứ tự ASCENDING.
SELECT first_name,department,salary from employees ORDER BY first_name LIMIT 5
5. Hàm Date
Trong PostgreSQL, bạn có thể dễ dàng trích xuất các giá trị từ các cột ngày tháng. Bạn sẽ thấy các hàm date được sử dụng nhiều nhất bên dưới.
SELECT
date_part('year',hire_date) as year,
date_part('month',hire_date) as month,
date_part('day',hire_date) as day,
date_part('dow',hire_date) as dayofweek,
to_char(hire_date, 'Dy') as day_name,
to_char(hire_date,'Month') as month_name,
hire_date
FROM employees
6. Kết hợp Inner, Left hoặc Right
Mệnh đề Inner Join tạo một bảng mới bằng cách kết hợp các hàng có giá trị phù hợp trong hai hoặc nhiều bảng.
Ví dụ: Truy vấn tất cả thông tin nhân viên và các bộ phận của họ.
Lưu ý: Bảng màu xanh là bảng đầu tiên và bảng màu xanh lá cây là bảng thứ hai của chúng ta.
SELECT * FROM employees e INNER JOIN departments d ON e.department = d.department
Left Join trả về tất cả các hàng từ bảng bên trái và các hàng phù hợp từ bảng bên phải. Nếu không tìm thấy hàng phù hợp nào trong bảng bên phải, thì NULL được sử dụng. (ngược lại cho Right Join)
Ví dụ: Viết truy vấn in tất cả các phòng ban từ nhân viên và khớp các phòng ban từ bảng phòng ban.
SELECT e.department,d.department FROM employees e LEFT JOIN departments d ON e.department = d.department
7. Truy vấn con
Truy vấn con là một truy vấn SQL được lồng bên trong một truy vấn lớn hơn.
Một truy vấn con có thể xảy ra trong:
- một mệnh đề SELECT
- một mệnh đề FROM
- một mệnh đề WHERE
Ví dụ: Truy vấn first_name, bộ phận và mức lương của từng nhân viên và cả mức lương tối đa được đưa ra.
SELECT first_name,department,salary,(SELECT max(salary) FROM employees) FROM employees
8. Truy vấn con có liên quan
Truy vấn con có liên quan là một cách để đọc mọi hàng trong bảng và so sánh các giá trị trong mỗi hàng với dữ liệu có liên quan. Nó được sử dụng bất cứ khi nào một truy vấn con phải trả về một kết quả khác hoặc tập hợp các kết quả cho mỗi hàng ứng viên được truy vấn chính xem xét.
Ví dụ: Viết câu truy vấn tìm tên, lương, bộ phận và mức lương trung bình theo bộ phận.
SELECT first_name,salary,department,round((SELECT AVG(salary)
FROM employees e2
WHERE e1.department = e2.department
GROUP BY department )) as avg_salary_by_department
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.department = e2.department
GROUP BY department )
ORDER BY salary
9. Mệnh đề Case
Câu lệnh CASE được sử dụng để triển khai logic nơi bạn muốn đặt giá trị của một cột tùy thuộc vào giá trị trong các cột khác.
Nó tương tự như câu lệnh IF-ELSE trong Excel.
Ví dụ: Viết một truy vấn để in ra tên, mức lương và mức lương trung bình cũng như một cột mới cho biết liệu mức lương của nhân viên có cao hơn mức trung bình hay không.
SELECT first_name,salary,(SELECT ROUND(AVG(salary)) FROM employees) as average_salary, (CASE WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'higher_than_average' ELSE 'lower_than_average' END) as Salary_Case FROM employees
10. Hàm Windows
Các hàm Windows áp dụng các hàm tổng hợp và xếp hạng trên một cửa sổ cụ thể (tập hợp các hàng). Mệnh đề OVER được sử dụng với các hàm Windows để xác định Windows đó. Mệnh đề OVER thực hiện hai điều:
- Phân vùng các hàng để tạo thành tập hợp các hàng (sử dụng PARTITION BY).
- Sắp xếp các hàng trong các phân vùng đó thành một thứ tự cụ thể (sử dụng mệnh đề ORDER BY).
Các hàm tổng hợp khác nhau như SUM(), COUNT(), AVERAGE(), MAX() và MIN() được áp dụng trên một cửa sổ cụ thể (tập hợp các hàng) được gọi là các hàm cửa sổ tổng hợp.
10.1. Ví dụ về Aggregation
Truy vấn sau đây sẽ cung cấp cho bạn mức lương trung bình cho từng bộ phận.
SELECT first_name,salary,department, ROUND(AVG(salary) OVER(PARTITION BY department)) as avg_sales_by_dept FROM employees ORDER BY salary DESC
10.2. Xếp hạng các giá trị
Hàm Rank() là một hàm cửa sổ chỉ định thứ hạng cho mỗi hàng trong một phân vùng của tập kết quả.
Ví dụ sau đây sắp xếp bảng theo mức lương (giảm dần). Giá trị 1 là giá trị lương cao nhất.
SELECT first_name,salary,RANK() OVER(ORDER BY salary DESC) FROM employees
