Tìm Hiểu Truy Vấn MySQL: Hướng Dẫn Cơ Bản Và Các Câu Lệnh Quan Trọng

Cơ sở dữ liệu đóng vai trò cốt lõi trong hầu hết các trang web và ứng dụng hiện đại, giúp lưu trữ, quản lý và trao đổi dữ liệu một cách hiệu quả. Trong quản trị cơ sở dữ liệu, một trong những nhiệm vụ quan trọng nhất là truy xuất dữ liệu – có thể là truy vấn theo yêu cầu hoặc là một phần của quy trình được tích hợp vào ứng dụng. Việc lấy dữ liệu từ cơ sở dữ liệu có thể được thực hiện theo nhiều cách khác nhau, nhưng phương pháp phổ biến nhất vẫn là sử dụng các truy vấn SQL thông qua giao diện dòng lệnh.

Trong các hệ quản trị cơ sở dữ liệu quan hệ (RDBMS), truy vấn là lệnh dùng để lấy thông tin từ một hoặc nhiều bảng dữ liệu. Ngôn ngữ truy vấn có cấu trúc (SQL – Structured Query Language) cung cấp nhiều cách để thực hiện truy vấn, trong đó câu lệnh SELECT là công cụ chính để trích xuất dữ liệu. Hiểu rõ cú pháp và cách sử dụng các toán tử, hàm SQL sẽ giúp bạn tối ưu hóa truy vấn và cải thiện hiệu suất của hệ thống.

Khám phá cách sử dụng truy vấn MySQL để quản lý dữ liệu hiệu quả! Để tối ưu hóa hiệu suất cơ sở dữ liệu, việc chọn VPS Server mạnh mẽ là rất quan trọng. Tìm hiểu các giải pháp VPS Server tại DataOnline để đảm bảo tốc độ và bảo mật cho dự án của bạn!

Tiếp theo, DataOnline sẽ cung cấp kiến thức cơ bản về cú pháp truy vấn SQL, các hàm và toán tử phổ biến, đồng thời hướng dẫn bạn thực hành với cơ sở dữ liệu MySQL sử dụng dữ liệu mẫu.

MySQL là một hệ quản trị cơ sở dữ liệu quan hệ mã nguồn mở được sử dụng rộng rãi nhờ hiệu suất cao, độ tin cậy và tính dễ sử dụng. Là một trong những hệ thống SQL phổ biến nhất, MySQL tuân theo tiêu chuẩn ANSI SQL, mặc dù có một số khác biệt trong cách thực hiện so với các hệ quản trị khác. Nắm vững MySQL không chỉ giúp bạn truy vấn dữ liệu hiệu quả mà còn là kỹ năng quan trọng trong việc xây dựng và tối ưu hóa các hệ thống cơ sở dữ liệu phức tạp.

Yêu cầu

Nhìn chung, các lệnh và khái niệm được trình bày trong hướng dẫn này có thể áp dụng cho bất kỳ hệ điều hành Linux nào chạy phần mềm cơ sở dữ liệu SQL. Tuy nhiên, hướng dẫn này được viết với giả định bạn đang sử dụng một máy chủ Ubuntu 18.04 chạy MySQL. Để thiết lập điều này, bạn sẽ cần:

● Một máy Ubuntu 18.04 với một người dùng không phải root có quyền sudo. Bạn có thể thiết lập điều này theo hướng dẫn Thiết Lập Máy Chủ Ban Đầu với Ubuntu 18.04.

● MySQL được cài đặt trên máy. Hướng dẫn Cách cài đặt MySQL trên Ubuntu 18.04 sẽ giúp bạn cài đặt.

Với cấu hình này, chúng ta có thể bắt đầu hướng dẫn.

Tạo cơ sở dữ liệu mẫu

Trước khi bắt đầu viết truy vấn SQL, trước tiên ta sẽ tạo một cơ sở dữ liệu và một vài bảng, sau đó chèn dữ liệu mẫu vào bảng. Điều này sẽ cho bạn cơ hội thực hành khi bắt đầu viết truy vấn sau này.

Giả sử cho cơ sở dữ liệu mẫu mà chúng ta sẽ sử dụng trong hướng dẫn này, hãy tưởng tượng kịch bản sau:

Bạn và một vài người bạn cùng tổ chức tiệc sinh nhật cho nhau. Vào mỗi dịp, các thành viên của nhóm cùng đến khu bowling địa phương, tham gia một giải đấu thân thiện, và sau đó tất cả về nhà bạn để bạn chuẩn bị món ăn ưa thích của người có sinh nhật.

Giờ đây, khi truyền thống này đã được duy trì một thời gian, bạn quyết định bắt đầu theo dõi kết quả của các giải đấu. Đồng thời, để việc lên kế hoạch bữa tối dễ dàng hơn, bạn quyết định ghi nhận thông tin về sinh nhật của bạn bè và các món ăn chính, món phụ, và món tráng miệng ưa thích của họ. Thay vì lưu trữ thông tin vào sổ tay, bạn quyết định vận dụng kỹ năng cơ sở dữ liệu của mình bằng cách ghi nhận dữ liệu vào một cơ sở dữ liệu MySQL.

Để bắt đầu, mở giao diện MySQL với người dùng root của MySQL:

sudo mysql

Lưu ý: Nếu bạn đã theo hướng dẫn cài đặt MySQL trên Ubuntu 18.04, bạn có thể đã cấu hình người dùng root xác thực bằng mật khẩu. Trong trường hợp này, bạn sẽ kết nối với giao diện MySQL bằng lệnh:

mysql -u root -p

Tiếp theo, tạo cơ sở dữ liệu bằng cách chạy lệnh:

CREATE DATABASE `birthdays`;

Sau đó chọn cơ sở dữ liệu vừa tạo:

USE birthdays;

Tiếp theo, tạo hai bảng trong cơ sở dữ liệu này. Chúng ta sẽ sử dụng bảng đầu tiên để theo dõi kết quả của bạn bè tại khu bowling. Lệnh sau sẽ tạo một bảng có tên tourneys với các cột: tên bạn bè, số giải đấu thắng (wins), điểm số cao nhất mọi thời đại (best), và kích cỡ giày bowling (size):

CREATE TABLE tourneys ( 
name varchar(30), 
wins real, 
best real, 
size real 
);

Sau khi chạy lệnh CREATE TABLE và tạo các cột, bạn sẽ nhận được thông báo:

Output

Query OK, 0 rows affected (0.00 sec)

Chèn dữ liệu mẫu vào bảng tourneys:

INSERT INTO tourneys (name, wins, best, size) 
VALUES ('Dolly', '7', '245', '8.5'), 
('Etta', '4', '283', '9'), 
('Irma', '9', '266', '7'), 
('Barbara', '2', '197', '7.5'), 
('Gladys', '13', '273', '8');

Output

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Tiếp theo, tạo bảng thứ hai trong cùng cơ sở dữ liệu để lưu trữ thông tin về các bữa tiệc sinh nhật – gồm tên bạn bè, ngày sinh, món ăn chính, món phụ và món tráng miệng ưa thích. Lệnh sau tạo bảng dinners:

CREATE TABLE dinners ( 
name varchar(30), 
birthdate date, 
entree varchar(30), 
side varchar(30), 
dessert varchar(30) 
);

Bạn sẽ nhận được phản hồi xác nhận rằng lệnh đã chạy thành công:

Output

Query OK, 0 rows affected (0.01 sec)

Chèn dữ liệu mẫu vào bảng dinners:

INSERT INTO dinners (name, birthdate, entree, side, dessert) 
VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'), 
('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'), 
('Irma', '1941-02-18', 'tofu', 'fries', 'cake'), 
('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'), 
('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');

Output

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Sau khi lệnh này chạy thành công, bạn đã hoàn tất việc thiết lập cơ sở dữ liệu. Tiếp theo, chúng ta sẽ tìm hiểu cấu trúc cơ bản của các câu lệnh SELECT.

Hiểu về câu lệnh select

Như đã đề cập ở phần giới thiệu, các truy vấn SQL hầu như luôn bắt đầu với câu lệnh SELECT. SELECT được dùng để chỉ định các cột từ bảng nào sẽ được trả về trong kết quả. Các truy vấn cũng hầu như luôn bao gồm mệnh đề FROM, dùng để chỉ định bảng sẽ được truy vấn.

Thông thường, cú pháp truy vấn SQL có dạng:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;

Ví dụ, câu lệnh sau sẽ trả về toàn bộ cột name từ bảng dinners:

SELECT name FROM dinners;

Output

+---------+
| name    |
+---------+
| Dolly   |
| Etta    |
| Irma    |
| Barbara |
| Gladys  |
+---------+
5 rows in set (0.00 sec)

Bạn có thể chọn nhiều cột từ cùng một bảng bằng cách phân tách tên các cột bằng dấu phẩy, như sau:

SELECT name, birthdate FROM dinners;

Output

+---------+------------+
| name    | birthdate  |
+---------+------------+
| Dolly   | 1946-01-19 |
| Etta    | 1938-01-25 |
| Irma    | 1941-02-18 |
| Barbara | 1948-12-25 |
| Gladys  | 1944-05-28 |
+---------+------------+
5 rows in set (0.00 sec)

Thay vì chỉ định một cột cụ thể hoặc một tập hợp các cột, bạn có thể theo sau câu lệnh SELECT bằng dấu sao (*) đại diện cho tất cả các cột trong bảng. Ví dụ, câu lệnh sau trả về mọi cột từ bảng tourneys:

SELECT * FROM tourneys;

Output

+---------+------+------+------+
| name    | wins | best | size |
+---------+------+------+------+
| Dolly   |    7 |  245 |  8.5 |
| Etta    |    4 |  283 |    9 |
| Irma    |    9 |  266 |    7 |
| Barbara |    2 |  197 |  7.5 |
| Gladys  |   13 |  273 |    8 |
+---------+------+------+------+
5 rows in set (0.00 sec)

Mệnh đề WHERE được dùng để lọc các bản ghi theo điều kiện chỉ định; các hàng không đáp ứng điều kiện sẽ bị loại bỏ. Cú pháp thường của WHERE như sau:

. . . WHERE column_name comparison_operator value

Các toán tử so sánh dùng trong mệnh đề WHERE xác định cách so sánh giá trị của cột với một giá trị cho trước. Dưới đây là một số toán tử so sánh thông dụng:

Ví dụ, nếu bạn muốn tìm kích cỡ giày của Irma, bạn có thể sử dụng truy vấn sau:

SELECT size FROM tourneys WHERE name = 'Irma';

Output

+------+
| size |
+------+
|    7 |
+------+
1 row in set (0.00 sec)

SQL cho phép sử dụng ký tự đại diện (wildcard) rất hữu ích khi dùng trong mệnh đề WHERE. Dấu phần trăm (%) đại diện cho không hoặc nhiều ký tự, còn dấu gạch dưới (_) đại diện cho một ký tự duy nhất. Điều này hữu ích nếu bạn không nhớ chính xác tên của mục cần tìm. Ví dụ, nếu bạn quên món ăn chính ưa thích của một số người bạn nhưng chắc chắn nó bắt đầu bằng chữ “t”, bạn có thể dùng truy vấn:

SELECT entree FROM dinners WHERE entree LIKE 't%';

Output

+--------+
| entree |
+--------+
| tofu   |
| tofu   |
+--------+
2 rows in set (0.00 sec)

Dựa trên kết quả trên, ta thấy rằng món ăn chính mà chúng ta quên là tofu.

Có những lúc bạn làm việc với cơ sở dữ liệu có các cột hoặc bảng có tên tương đối dài hoặc khó đọc. Trong những trường hợp này, bạn có thể làm cho các tên này dễ hiểu hơn bằng cách tạo một bí danh (alias) với từ khóa AS. Các bí danh được tạo ra với AS chỉ tồn tại tạm thời trong thời gian truy vấn đang được thực hiện.

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners;

Output

+---------+------------+-----------+
| n       | b          | d         |
+---------+------------+-----------+
| Dolly   | 1946-01-19 | cake      |
| Etta    | 1938-01-25 | ice cream |
| Irma    | 1941-02-18 | cake      |
| Barbara | 1948-12-25 | ice cream |
| Gladys  | 1944-05-28 | ice cream |
+---------+------------+-----------+
5 rows in set (0.00 sec)

Các ví dụ trên bao gồm một số từ khóa và mệnh đề phổ biến trong các truy vấn SQL cơ bản. Những truy vấn này hữu ích cho các tác vụ đơn giản, nhưng không đủ nếu bạn cần thực hiện các phép tính hoặc tính toán một giá trị đơn lẻ từ dữ liệu. Đây chính là lúc các hàm tổng hợp phát huy tác dụng.

Các hàm tổng hợp

Các hàm tổng hợp trong SQL giúp bạn không chỉ xem dữ liệu mà còn có thể diễn giải hoặc tính toán dựa trên dữ liệu đó thông qua một truy vấn SELECT. Những hàm này được gọi là hàm tổng hợp (aggregate functions).

1. Hàm COUNT – Đếm số hàng thỏa mãn điều kiện

Hàm COUNT được sử dụng để đếm và trả về số lượng hàng phù hợp với một tiêu chí cụ thể.

Ví dụ: Nếu bạn muốn biết có bao nhiêu người bạn thích món tofu làm món chính cho bữa tiệc sinh nhật, bạn có thể chạy truy vấn sau:

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu';

Kết quả:

+---------------+
| COUNT(entree) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

2. Hàm AVG – Tính giá trị trung bình

Hàm AVG trả về giá trị trung bình (mean) của một cột.

Ví dụ: Để tìm điểm số cao nhất trung bình của bạn bè trong các giải đấu, bạn có thể sử dụng truy vấn sau:

SELECT AVG(best) FROM tourneys;

Kết quả:

+-----------+
| AVG(best) |
+-----------+
|     252.8 |
+-----------+
1 row in set (0.00 sec)

3. Hàm SUM – Tính tổng giá trị của một cột

Hàm SUM được sử dụng để tính tổng của một cột cụ thể.

Ví dụ: Để xem tổng số trận đấu mà bạn và bạn bè đã chơi trong các giải đấu qua nhiều năm, bạn có thể chạy truy vấn sau:

SELECT SUM(wins) FROM tourneys;

Kết quả:

+-----------+
| SUM(wins) |
+-----------+
|        35 |
+-----------+
1 row in set (0.00 sec)

Lưu ý rằng các hàm AVG và SUM chỉ hoạt động chính xác với dữ liệu số. Nếu bạn cố gắng sử dụng chúng trên dữ liệu không phải số, bạn có thể gặp lỗi hoặc kết quả chỉ là 0, tùy thuộc vào hệ quản trị cơ sở dữ liệu (RDBMS) mà bạn đang sử dụng.

Ví dụ: Nếu bạn thử tính tổng giá trị của một cột chứa dữ liệu dạng chữ:

SELECT SUM(entree) FROM dinners;

Kết quả có thể là:

+-------------+
| SUM(entree) |
+-------------+
|           0 |
+-------------+
1 row in set, 5 warnings (0.00 sec)

4. Hàm MIN – Tìm giá trị nhỏ nhất

Hàm MIN được sử dụng để tìm giá trị nhỏ nhất trong một cột.

Ví dụ: Bạn có thể dùng truy vấn sau để tìm số trận thắng thấp nhất trong các giải đấu:

SELECT MIN(wins) FROM tourneys;

Kết quả:

+-----------+
| MIN(wins) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

5. Hàm MAX – Tìm giá trị lớn nhất

Hàm MAX được sử dụng để tìm giá trị lớn nhất trong một cột.

Ví dụ: Để xem ai có số trận thắng cao nhất trong các giải đấu, bạn có thể chạy truy vấn sau:

SELECT MAX(wins) FROM tourneys;

Kết quả:

+-----------+
| MAX(wins) |
+-----------+
|        13 |
+-----------+
1 row in set (0.00 sec)

Không giống như SUM và AVG, hai hàm MIN và MAX có thể được sử dụng với cả dữ liệu số và dữ liệu chữ.

Ví dụ: Khi chạy MIN trên một cột chứa dữ liệu dạng chữ, nó sẽ trả về giá trị đầu tiên theo thứ tự bảng chữ cái:

SELECT MIN(name) FROM dinners;

Kết quả:

+-----------+
| MIN(name) |
+-----------+
| Barbara   |
+-----------+
1 row in set (0.00 sec)

Tương tự, nếu chạy MAX trên cột chứa dữ liệu dạng chữ, nó sẽ trả về giá trị cuối cùng theo thứ tự bảng chữ cái:

SELECT MAX(name) FROM dinners;

Kết quả:

+-----------+
| MAX(name) |
+-----------+
| Irma      |
+-----------+
1 row in set (0.00 sec)

Ứng dụng nâng cao của hàm tổng hợp

Các hàm tổng hợp không chỉ giới hạn ở các truy vấn đơn giản mà còn cực kỳ hữu ích khi kết hợp với mệnh đề GROUP BY. Chúng giúp phân loại và tóm tắt dữ liệu theo nhóm cụ thể. Chủ đề này sẽ được trình bày trong phần tiếp theo, cùng với một số mệnh đề khác giúp sắp xếp kết quả của truy vấn.

Xử lý kết quả truy vấn

Ngoài các mệnh đề FROM và WHERE, còn có một số mệnh đề khác được sử dụng để xử lý kết quả của truy vấn SELECT. Trong phần này, chúng ta sẽ giải thích và đưa ra ví dụ cho một số mệnh đề truy vấn được sử dụng phổ biến.

Một trong những mệnh đề truy vấn được sử dụng thường xuyên, bên cạnh FROM và WHERE, là mệnh đề GROUP BY. Thông thường, mệnh đề này được sử dụng khi bạn thực hiện một hàm tổng hợp trên một cột, nhưng cần nhóm các giá trị dựa trên một cột khác.

Ví dụ, giả sử bạn muốn biết có bao nhiêu người bạn ưa thích từng món ăn chính trong ba món bạn nấu. Bạn có thể tìm thông tin đó bằng truy vấn sau:

SELECT COUNT(name), entree FROM dinners GROUP BY entree;

+-------------+---------+
| COUNT(name) | entree  |
+-------------+---------+
|           1 | chicken |
|           2 | steak   |
|           2 | tofu    |
+-------------+---------+
3 rows in set (0.00 sec)

Mệnh đề ORDER BY được sử dụng để sắp xếp kết quả của truy vấn. Theo mặc định, các giá trị số được sắp xếp theo thứ tự tăng dần, và các giá trị chuỗi được sắp xếp theo thứ tự chữ cái. Để minh họa, truy vấn sau liệt kê các cột name và birthdate, nhưng sắp xếp kết quả theo birthdate:

SELECT name, birthdate FROM dinners ORDER BY birthdate;

Output

+---------+------------+
| name    | birthdate  |
+---------+------------+
| Etta    | 1938-01-25 |
| Irma    | 1941-02-18 |
| Gladys  | 1944-05-28 |
| Dolly   | 1946-01-19 |
| Barbara | 1948-12-25 |
+---------+------------+
5 rows in set (0.00 sec)

Lưu ý rằng hành vi mặc định của ORDER BY là sắp xếp theo thứ tự tăng dần. Để đảo ngược thứ tự này và sắp xếp theo thứ tự giảm dần, bạn chỉ cần kết thúc truy vấn với từ khóa DESC:

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;

Output

+---------+------------+
| name    | birthdate  |
+---------+------------+
| Barbara | 1948-12-25 |
| Dolly   | 1946-01-19 |
| Gladys  | 1944-05-28 |
| Irma    | 1941-02-18 |
| Etta    | 1938-01-25 |
+---------+------------+
5 rows in set (0.00 sec)

Như đã đề cập trước đó, mệnh đề WHERE được sử dụng để lọc kết quả dựa trên các điều kiện cụ thể. Tuy nhiên, nếu bạn sử dụng mệnh đề WHERE cùng với một hàm tổng hợp, truy vấn sẽ trả về lỗi; như trường hợp sau khi cố gắng tìm các món ăn phụ được ưa thích bởi ít nhất ba người bạn:

SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3;

Output

ERROR 1111 (HY000): Invalid use of group function

Để khắc phục, mệnh đề HAVING được bổ sung vào SQL nhằm cung cấp chức năng tương tự như WHERE nhưng tương thích với các hàm tổng hợp. Ta có thể hiểu sự khác biệt giữa chúng rằng WHERE áp dụng cho các bản ghi riêng lẻ, trong khi HAVING áp dụng cho các nhóm bản ghi. Vì thế, bất cứ khi nào bạn sử dụng HAVING, mệnh đề GROUP BY cũng phải có mặt.

Ví dụ dưới đây là một cách khác để tìm các món ăn phụ được ưa thích bởi ít nhất ba người bạn, mà không gây ra lỗi:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3;

Output

+-------------+-------+
| COUNT(name) | side  |
+-------------+-------+
|           3 | fries |
+-------------+-------+
1 row in set (0.00 sec)

Các hàm tổng hợp rất hữu ích trong việc tóm tắt kết quả của một cột cụ thể trong bảng. Tuy nhiên, cũng có nhiều trường hợp mà bạn cần truy vấn dữ liệu từ hơn một bảng. Phần tiếp theo sẽ trình bày một số cách bạn có thể thực hiện điều này.

Truy vấn nhiều bảng

Thông thường, một cơ sở dữ liệu chứa nhiều bảng, mỗi bảng lưu trữ một tập dữ liệu khác nhau. SQL cung cấp một vài cách khác nhau để thực hiện một truy vấn đơn trên nhiều bảng.

Mệnh đề JOIN được sử dụng để kết hợp các hàng từ hai hoặc nhiều bảng trong kết quả truy vấn. Nó thực hiện việc này bằng cách tìm một cột liên quan giữa các bảng và sắp xếp kết quả một cách thích hợp trong phần output.

Các câu lệnh SELECT có chứa mệnh đề JOIN thường theo cú pháp sau:

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.related_column=table2.related_column;

Lưu ý rằng vì mệnh đề JOIN so sánh nội dung của nhiều bảng, ví dụ trên chỉ định bảng cần chọn từng cột bằng cách đặt tên bảng và dấu chấm trước tên cột. Bạn có thể chỉ định bảng cho cột theo cách này trong bất kỳ truy vấn nào, mặc dù không cần thiết khi chỉ truy vấn từ một bảng duy nhất, như đã làm ở các phần trước. Hãy cùng xem một ví dụ sử dụng dữ liệu mẫu của chúng ta.

Giả sử bạn muốn mua cho mỗi người bạn một đôi giày bowling làm quà sinh nhật. Vì thông tin về ngày sinh và kích cỡ giày của bạn bè được lưu trong các bảng riêng biệt, bạn có thể truy vấn từng bảng riêng rẽ rồi so sánh kết quả. Tuy nhiên, với mệnh đề JOIN, bạn có thể lấy tất cả thông tin bạn muốn chỉ qua một truy vấn:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
JOIN dinners ON tourneys.name=dinners.name;

Output

+---------+------+------------+
| name    | size | birthdate  |
+---------+------+------------+
| Dolly   |  8.5 | 1946-01-19 |
| Etta    |    9 | 1938-01-25 |
| Irma    |    7 | 1941-02-18 |
| Barbara |  7.5 | 1948-12-25 |
| Gladys  |    8 | 1944-05-28 |
+---------+------+------------+
5 rows in set (0.00 sec)

Mệnh đề JOIN được sử dụng trong ví dụ trên, không kèm thêm bất kỳ tham số nào khác, chính là INNER JOIN. Điều này có nghĩa là nó chỉ chọn những bản ghi có giá trị khớp ở cả hai bảng và đưa chúng vào kết quả, trong khi các bản ghi không khớp sẽ bị loại bỏ.

Để minh họa ý tưởng này, hãy thêm một hàng mới vào mỗi bảng mà không có mục tương ứng trong bảng kia:

INSERT INTO tourneys (name, wins, best, size) 
VALUES ('Bettye', '0', '193', '9');

INSERT INTO dinners (name, birthdate, entree, side, dessert) 
VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');

Sau đó, chạy lại câu lệnh SELECT với mệnh đề JOIN:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
JOIN dinners ON tourneys.name=dinners.name;

Output

+---------+------+------------+
| name    | size | birthdate  |
+---------+------+------------+
| Dolly   |  8.5 | 1946-01-19 |
| Etta    |    9 | 1938-01-25 |
| Irma    |    7 | 1941-02-18 |
| Barbara |  7.5 | 1948-12-25 |
| Gladys  |    8 | 1944-05-28 |
+---------+------+------------+
5 rows in set (0.00 sec)

Lưu ý rằng vì bảng tourneys không có mục cho Lesley và bảng dinners không có mục cho Bettye, những bản ghi đó không có trong kết quả.

Tuy nhiên, bạn có thể trả về tất cả các bản ghi từ một bảng bằng cách sử dụng mệnh đề OUTER JOIN. Trong MySQL, các mệnh đề JOIN có thể được viết là LEFT JOIN hoặc RIGHT JOIN.

• LEFT JOIN: Trả về tất cả các bản ghi từ bảng bên trái (trong câu lệnh FROM) và chỉ các bản ghi khớp từ bảng bên phải. Trong ngữ cảnh của outer joins, bảng bên trái là bảng được đề cập trong mệnh đề FROM, còn bảng bên phải là bất kỳ bảng nào được nhắc đến sau mệnh đề JOIN.

Chạy lại truy vấn trước, nhưng lần này sử dụng LEFT JOIN:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
LEFT JOIN dinners ON tourneys.name=dinners.name;

Lệnh này sẽ trả về tất cả các bản ghi từ bảng bên trái (trong trường hợp này, bảng tourneys) ngay cả khi không có bản ghi tương ứng trong bảng bên phải. Khi không có bản ghi khớp từ bảng bên phải, giá trị trả về sẽ là NULL hoặc chỉ là giá trị trống, tùy thuộc vào RDBMS của bạn.

Output

+---------+------+------------+
| name    | size | birthdate  |
+---------+------+------------+
| Dolly   |  8.5 | 1946-01-19 |
| Etta    |    9 | 1938-01-25 |
| Irma    |    7 | 1941-02-18 |
| Barbara |  7.5 | 1948-12-25 |
| Gladys  |    8 | 1944-05-28 |
| Bettye  |    9 | NULL       |
+---------+------+------------+
6 rows in set (0.00 sec)

• Tiếp theo, chạy lại truy vấn, lần này sử dụng RIGHT JOIN:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
RIGHT JOIN dinners ON tourneys.name=dinners.name;

Lệnh này sẽ trả về tất cả các bản ghi từ bảng bên phải (bảng dinners). Vì ngày sinh của Lesley được lưu trong bảng dinners, nhưng không có hàng tương ứng nào trong bảng tourneys, các cột name và size sẽ trả về giá trị NULL trong hàng đó.

Output

+---------+------+------------+
| name    | size | birthdate  |
+---------+------+------------+
| Dolly   |  8.5 | 1946-01-19 |
| Etta    |    9 | 1938-01-25 |
| Irma    |    7 | 1941-02-18 |
| Barbara |  7.5 | 1948-12-25 |
| Gladys  |    8 | 1944-05-28 |
| NULL    | NULL | 1946-05-02 |
+---------+------+------------+
6 rows in set (0.00 sec)

Lưu ý rằng các LEFT JOIN và RIGHT JOIN cũng có thể được viết dưới dạng LEFT OUTER JOIN hoặc RIGHT OUTER JOIN, mặc dù phần OUTER thường được hiểu ngầm. Tương tự, chỉ định INNER JOIN sẽ cho kết quả giống như chỉ viết JOIN.

Ngoài ra, như một lựa chọn thay thế cho việc sử dụng JOIN để truy vấn các bản ghi từ nhiều bảng, bạn có thể sử dụng mệnh đề UNION.

Toán tử UNION hoạt động hơi khác so với JOIN: thay vì hiển thị kết quả từ nhiều bảng dưới dạng các cột riêng biệt trong cùng một truy vấn SELECT, UNION kết hợp kết quả của hai câu lệnh SELECT thành một cột duy nhất.

Để minh họa, chạy truy vấn sau:

SELECT name FROM tourneys UNION SELECT name FROM dinners;

Truy vấn này sẽ loại bỏ các mục trùng lặp (đây là hành vi mặc định của toán tử UNION).

Output

+---------+
| name    |
+---------+
| Dolly   |
| Etta    |
| Irma    |
| Barbara |
| Gladys  |
| Bettye  |
| Lesley  |
+---------+
7 rows in set (0.00 sec)

Để trả về tất cả các mục (bao gồm cả mục trùng lặp), sử dụng toán tử UNION ALL:

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;

Output

+---------+
| name    |
+---------+
| Dolly   |
| Etta    |
| Irma    |
| Barbara |
| Gladys  |
| Bettye  |
| Dolly   |
| Etta    |
| Irma    |
| Barbara |
| Gladys  |
| Lesley  |
+---------+
12 rows in set (0.00 sec)

Số lượng và tên các cột trong bảng kết quả phản ánh số và tên các cột được truy vấn trong câu lệnh SELECT đầu tiên. Lưu ý rằng khi sử dụng UNION để truy vấn nhiều cột từ hơn một bảng, mỗi câu lệnh SELECT phải truy vấn cùng số lượng cột, các cột tương ứng phải có kiểu dữ liệu tương tự, và thứ tự các cột phải giống nhau. Ví dụ, nếu bạn thử:

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys;

Output

ERROR 1222 (21000): The used SELECT statements have a different number of columns

Một cách khác để truy vấn nhiều bảng là sử dụng subqueries. Subqueries (còn được gọi là inner hoặc nested queries) là các truy vấn được bao bọc bên trong một truy vấn khác. Chúng hữu ích trong những trường hợp bạn muốn lọc kết quả truy vấn dựa trên kết quả của một hàm tổng hợp riêng biệt.

Ví dụ, giả sử bạn muốn biết ai trong số bạn bè đã thắng nhiều trận hơn Barbara. Thay vì truy vấn số trận thắng của Barbara rồi chạy truy vấn khác để so sánh, bạn có thể tính cả hai với một truy vấn:

SELECT name, wins FROM tourneys 
WHERE wins > (
SELECT wins FROM tourneys WHERE name = 'Barbara'
);

Output

+--------+------+
| name   | wins |
+--------+------+
| Dolly  |    7 |
| Etta   |    4 |
| Irma   |    9 |
| Gladys |   13 |
+--------+------+
4 rows in set (0.00 sec)

Subquery trong truy vấn này chỉ được chạy một lần; nó chỉ cần tìm giá trị trong cột wins ở hàng có tên là Barbara, và dữ liệu trả về của subquery và truy vấn bên ngoài độc lập với nhau. Tuy nhiên, có những trường hợp truy vấn bên ngoài phải đọc từng hàng trong bảng và so sánh các giá trị đó với dữ liệu trả về của subquery để đưa ra kết quả mong muốn. Trong trường hợp đó, subquery được gọi là correlated subquery.

Ví dụ về correlated subquery, truy vấn sau tìm những người bạn có số trận thắng vượt qua trung bình của những người có cùng kích cỡ giày:

SELECT name, size FROM tourneys AS t 
WHERE wins > (
SELECT AVG(wins) FROM tourneys WHERE size = t.size
);

Để truy vấn hoàn thành, nó phải lấy trước các cột name và size từ truy vấn bên ngoài, sau đó so sánh từng hàng trong kết quả với kết quả của truy vấn bên trong, truy vấn bên trong tính trung bình số trận thắng của những người có cùng kích cỡ giày. Vì bạn chỉ có hai người bạn có cùng kích cỡ giày, chỉ có một hàng kết quả được trả về.

Output

+------+------+
| name | size |
+------+------+
| Etta |    9 |
+------+------+
1 row in set (0.00 sec)

Như đã đề cập trước đó, subqueries có thể được sử dụng để truy vấn dữ liệu từ nhiều bảng. Để minh họa thêm với ví dụ cuối cùng, giả sử bạn muốn tổ chức một bữa tiệc bất ngờ cho người chơi bowling xuất sắc nhất mọi thời đại. Bạn có thể tìm người bạn có kết quả tốt nhất và trả về món ăn ưa thích của họ bằng truy vấn:

SELECT name, entree, side, dessert 
FROM dinners 
WHERE name = (SELECT name FROM tourneys 
WHERE wins = (SELECT MAX(wins) FROM tourneys));

Output

+--------+--------+-------+-----------+
| name   | entree | side  | dessert   |
+--------+--------+-------+-----------+
| Gladys | steak  | fries | ice cream |
+--------+--------+-------+-----------+
1 row in set (0.00 sec)

Lưu ý rằng câu lệnh này không chỉ chứa một subquery, mà còn chứa subquery lồng bên trong subquery.

Kết luận

Thực hiện truy vấn là một trong những nhiệm vụ phổ biến nhất trong quản trị cơ sở dữ liệu. Mặc dù có nhiều công cụ quản trị như phpMyAdmin hay pgAdmin cho phép bạn thực hiện truy vấn và trực quan hóa kết quả, nhưng việc sử dụng câu lệnh SELECT từ dòng lệnh vẫn là quy trình được thực hành rộng rãi và mang lại cho bạn quyền kiểm soát tối đa.

Hiểu rõ câu lệnh MySQL giúp bạn quản lý dữ liệu dễ dàng. Để triển khai cơ sở dữ liệu trên môi trường chuyên nghiệp, hãy xem giá thuê VPS hợp lý tại DataOnline. Với giá thuê VPS cạnh tranh, bạn sẽ có nền tảng vững chắc để phát triển ứng dụng!