Cách Sử Dụng Ký Tự đại Diện Trong SQL

Bài viết / Thứ hai, 14/09/2020 | 00:00 GMT+7 Cách sử dụng ký tự đại diện trong SQL Giống như nhiều ngôn ngữ máy tính, SQL cho phép sử dụng các ký tự đại diện khác nhau. Ký tự đại diện là các ký tự giữ chỗ đặc biệt có thể đại diện cho một hoặc nhiều ký tự hoặc giá trị khác. Đây là một tính năng tiện lợi trong SQL, vì nó cho phép bạn tìm kiếm dữ liệu của bạn trong database mà không cần biết các giá trị chính xác nằm trong nó.

Hướng dẫn này sẽ trình bày về cách truy vấn dữ liệu bằng cách sử dụng các ký tự đại diện được chỉ định của SQL.

Yêu cầu

Để làm theo hướng dẫn này, bạn cần một máy tính chạy một số loại hệ quản trị database quan hệ (RDBMS) sử dụng SQL. Các hướng dẫn và ví dụ trong hướng dẫn này đã được kiểm nghiệm bằng cách sử dụng môi trường sau:

• Server chạy Ubuntu 20.04, với user không phải root có quyền quản trị và firewall được cấu hình bằng UFW, như được mô tả trong hướng dẫn cài đặt server ban đầu của ta cho Ubuntu 20.04 .
• MySQL được cài đặt và bảo mật trên server , như được nêu trong Cách cài đặt MySQL trên Ubuntu 20.04 . Hướng dẫn này đã được xác minh với user MySQL không phải root, được tạo bằng quy trình được mô tả trong Bước 3 .

Lưu ý : Xin lưu ý nhiều RDBMS sử dụng các triển khai SQL duy nhất của riêng họ. Mặc dù các lệnh được nêu trong hướng dẫn này sẽ hoạt động trên hầu hết các RDBMS, nhưng cú pháp hoặc kết quả chính xác có thể khác nếu bạn kiểm tra chúng trên một hệ thống không phải MySQL.

Bạn cũng cần một database và bảng được tải với một số dữ liệu mẫu mà bạn có thể thực hành sử dụng với các ký tự đại diện. Nếu chưa có, bạn có thể đọc phần Kết nối với MySQL và Cài đặt Database Mẫu sau đây để biết chi tiết về cách tạo database và bảng mà hướng dẫn này sẽ sử dụng trong các ví dụ xuyên suốt.

Kết nối với MySQL và cài đặt database mẫu

Nếu hệ thống database SQL của bạn chạy trên một server từ xa, hãy SSH vào server của bạn từ máy local của bạn:

• ssh sammy@your_server_ip

Sau đó, mở dấu nhắc server MySQL, thay thế sammy bằng tên account user MySQL của bạn:

• mysql -u sammy -p

Tạo database có tên là wildcardsDB :

• CREATE DATABASE wildcardsDB;

Nếu database được tạo thành công, bạn sẽ nhận được kết quả như sau:

OutputQuery OK, 1 row affected (0.01 sec)

Để chọn database DB wildcardsDB , hãy chạy câu lệnh USE sau:

• USE wildcardsDB;

OutputDatabase changed

Sau khi chọn database , bạn có thể tạo một bảng bên trong nó bằng lệnh sau. Ví dụ: giả sử bạn muốn tạo một bảng có tên user_profiles để chứa thông tin tiểu sử của user ứng dụng. Bảng này sẽ chứa năm cột sau:

• user_id : cột này sẽ chứa các giá trị của kiểu dữ liệu int đó. Nó cũng sẽ đóng role là khóa chính của bảng, với mỗi giá trị hoạt động như một mã định danh duy nhất cho hàng tương ứng của nó
• name : name mỗi user , được thể hiện bằng kiểu dữ liệu varchar với tối đa 30 ký tự
• email : cột này sẽ chứa địa chỉ email của user , cũng được thể hiện bằng kiểu dữ liệu varchar nhưng có tối đa 40 ký tự
• birthdate : sử dụng kiểu dữ liệu date , cột này sẽ chứa ngày sinh của mỗi user
• quote : quote yêu thích của mỗi user . Để cung cấp đủ số ký tự cho dấu ngoặc kép, cột này cũng sử dụng kiểu dữ liệu varchar , nhưng với tối đa 300 ký tự

Chạy lệnh sau để tạo bảng mẫu này:

• CREATE TABLE user_profiles (
• user_id int,
• name varchar(30),
• email varchar(40),
• birthdate date,
• quote varchar(300),
• PRIMARY KEY (user_id)
• );

OutputDatabase changed

Sau đó, chèn một số dữ liệu mẫu vào bảng trống:

• INSERT INTO user_profiles
• VALUES
• (1, 'Kim', 'bd_eyes@example.com', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'),
• (2, 'Ann', 'cantstandrain@example.com', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'),
• (3, 'Phoebe', 'poetry_man@example.com', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'),
• (4, 'Jim', 'u_f_o@example.com', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'),
• (5, 'Timi', 'big_voice@example.com', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'),
• (6, 'Taeko', 'sunshower@example.com', '1953-11-28', '"You miss 100% of the shots you don\'t take." -Wayne Gretzky'),
• (7, 'Irma', 'soulqueen_NOLA@example.com', '1941-02-18', '"You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss'),
• (8, 'Iris', 'our_town@example.com', '1961-01-05', '"You will face many defeats in life, but never let yourself be defeated." -Maya Angelou');

OutputQuery OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0

Như vậy, bạn đã sẵn sàng làm theo phần còn lại của hướng dẫn và bắt đầu tìm hiểu về cách sử dụng ký tự đại diện để truy vấn dữ liệu trong SQL.

Truy vấn dữ liệu bằng ký tự đại diện

Như đã đề cập trong Phần giới thiệu, ký tự đại diện là các ký tự giữ chỗ đặc biệt có thể đại diện cho một hoặc nhiều ký tự hoặc giá trị khác.

Trong SQL, chỉ có hai ký tự đại diện được xác định:

• _ : Khi được sử dụng làm ký tự đại diện, dấu gạch dưới đại diện cho một ký tự duy nhất. Ví dụ: s_mmy sẽ trùng với sammy , sbmmy hoặc sxmmy .
• % : Ký tự đại diện dấu phần trăm đại diện cho không hoặc nhiều ký tự. Ví dụ: s%mmy sẽ trùng với sammy , saaaaaammy hoặc smmy .

Các ký tự đại diện này được sử dụng riêng trong WHERE của truy vấn với các toán tử LIKE hoặc NOT LIKE .

Để minh họa bằng cách sử dụng dữ liệu mẫu từ phần Yêu cầu , giả sử bạn biết ít nhất một trong những user được liệt kê trong bảng user_profiles có tên dài ba chữ cái và kết thúc bằng “tôi”, nhưng bạn không chắc họ là ai. Vì bạn chỉ không chắc chắn về chữ cái đầu tiên trong tên của những user này, bạn có thể chạy truy vấn sau, sử dụng ký tự đại diện _ , để tìm họ là ai:

• SELECT * FROM user_profiles WHERE name LIKE '_im';

Output+---------+------+---------------------+------------+---------------------------------------------------------------------------------+ | user_id | name | email | birthdate | quote | +---------+------+---------------------+------------+---------------------------------------------------------------------------------+ | 1 | Kim | bd_eyes@example.com | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth | | 4 | Jim | u_f_o@example.com | 1940-08-13 | "Whoever is happy will make others happy too." -Anne Frank | +---------+------+---------------------+------------+---------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

Lưu ý : Trong ví dụ này, dấu hoa thị ( * ) theo sau SELECT . Đây là cách viết tắt trong SQL và đại diện cho “mọi cột”.

Trong một số ứng dụng và ngôn ngữ lập trình, và thậm chí một số triển khai SQL, dấu hoa thị được sử dụng làm ký tự đại diện đại diện cho không hoặc nhiều ký tự, giống như dấu phần trăm được sử dụng trong ví dụ này. Tuy nhiên, dấu hoa thị trong ví dụ trên không phải là ký tự đại diện, vì nó đại diện cho một cái gì đó cụ thể - cụ thể là mọi cột trong bảng user_profiles - thay vì một hoặc nhiều ký tự không xác định.

Toán tử NOT LIKE có tác dụng ngược lại với LIKE . Thay vì trả về mọi bản ghi trùng với mẫu ký tự đại diện, nó sẽ trả về mọi hàng không trùng với mẫu. Để minh họa, hãy chạy lại truy vấn trước đó nhưng thay LIKE bằng NOT LIKE :

• SELECT * FROM user_profiles WHERE name NOT LIKE '_im';

Lần này, mọi hàng có giá trị trong cột name trùng với _im đều bị bỏ qua khỏi tập kết quả:

Output+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ | user_id | name | email | birthdate | quote | +---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ | 2 | Ann | cantstandrain@example.com | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt | | 3 | Phoebe | poetry_man@example.com | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin | | 5 | Timi | big_voice@example.com | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville | | 6 | Taeko | sunshower@example.com | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky | | 7 | Irma | soulqueen_NOLA@example.com | 1941-02-18 | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss | | 8 | Iris | our_town@example.com | 1961-01-05 | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou | +---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec)

Ví dụ khác, giả sử bạn biết một số user được liệt kê trong database có tên bắt đầu bằng “I”, nhưng bạn không thể nhớ tất cả chúng. Bạn có thể sử dụng ký tự đại diện % để liệt kê tất cả chúng, như được hiển thị với truy vấn sau:

• SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'I%';

Output+---------+------+----------------------------+ | user_id | name | email | +---------+------+----------------------------+ | 7 | Irma | soulqueen_NOLA@example.com | | 8 | Iris | our_town@example.com | +---------+------+----------------------------+ 2 rows in set (0.00 sec)

Lưu ý trong MySQL, theo mặc định, toán tử LIKE và NOT LIKE không phân biệt chữ hoa chữ thường. Điều này nghĩa là truy vấn trước đó sẽ trả về kết quả tương tự ngay cả khi bạn không viết hoa chữ “I” trong mẫu ký tự đại diện:

• SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'i%';

Output+---------+------+----------------------------+ | user_id | name | email | +---------+------+----------------------------+ | 7 | Irma | soulqueen_NOLA@example.com | | 8 | Iris | our_town@example.com | +---------+------+----------------------------+ 2 rows in set (0.00 sec)

Lưu ý các ký tự đại diện khác với các cụm từ thông dụng. Nói chung, ký tự đại diện đề cập đến một ký tự được sử dụng trong đối sánh mẫu kiểu hình cầu , trong khi biểu thức chính quy dựa vào ngôn ngữ thông thường để đối sánh các mẫu chuỗi.

Thoát khỏi ký tự đại diện

Có thể đôi khi bạn muốn tìm kiếm các mục nhập dữ liệu có chứa một trong các ký tự đại diện của SQL. Trong những trường hợp như vậy, bạn có thể sử dụng một ký tự thoát sẽ hướng dẫn SQL bỏ qua hàm ký tự đại diện của % hoặc _ và thay vào đó diễn giải chúng dưới dạng văn bản thuần túy.

Ví dụ: giả sử bạn biết rằng ít nhất một vài user được liệt kê trong database có một câu trích dẫn yêu thích bao gồm dấu phần trăm, nhưng bạn không chắc họ là ai.

Bạn có thể thử chạy truy vấn sau:

• SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';

Tuy nhiên, truy vấn này sẽ không hữu ích lắm. Vì dấu phần trăm đóng role là ký tự thay thế cho bất kỳ chuỗi ký tự nào có độ dài bất kỳ, nó sẽ trả về mọi hàng trong bảng:

Output+---------+--------+--------------------------------------------------------------------------------------------------------------------------+ | user_id | name | quote | +---------+--------+--------------------------------------------------------------------------------------------------------------------------+ | 1 | Kim | "Never let the fear of striking out keep you from playing the game." -Babe Ruth | | 2 | Ann | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt | | 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin | | 4 | Jim | "Whoever is happy will make others happy too." -Anne Frank | | 5 | Timi | "It is better to fail in originality than to succeed in imitation." -Herman Melville | | 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky | | 7 | Irma | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss | | 8 | Iris | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou | +---------+--------+--------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec)

Để thoát khỏi dấu phần trăm, bạn có thể đặt trước nó bằng dấu gạch chéo ngược ( \ ), ký tự thoát mặc định của MySQL:

• SELECT * FROM user_profiles WHERE quote LIKE '\%';

Tuy nhiên, truy vấn này cũng sẽ không hữu ích, vì nó chỉ định rằng nội dung của cột quote chỉ nên bao gồm một dấu phần trăm. Do đó, tập kết quả sẽ trống:

OutputEmpty set (0.00 sec)

Để sửa lỗi này, bạn cần bao gồm các ký tự đại diện dấu phần trăm ở đầu và cuối của mẫu tìm kiếm theo sau toán tử LIKE :

• SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%\%%';

Output+---------+--------+----------------------------------------------------------------------------+ | user_id | name | quote | +---------+--------+----------------------------------------------------------------------------+ | 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin | | 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky | +---------+--------+----------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

Trong truy vấn này, dấu gạch chéo ngược chỉ thoát khỏi dấu phần trăm thứ hai, trong khi dấu thứ nhất và thứ ba vẫn hoạt động như ký tự đại diện. Do đó, truy vấn này sẽ trả về mọi hàng có cột quote bao gồm ít nhất một dấu phần trăm.

Lưu ý bạn cũng có thể xác định các ký tự thoát tùy chỉnh bằng mệnh đề ESCAPE , như trong ví dụ sau:

• SELECT user_id, name, email FROM user_profiles WHERE email LIKE '%@_%' ESCAPE '@';

Output+---------+--------+----------------------------+ | user_id | name | email | +---------+--------+----------------------------+ | 1 | Kim | bd_eyes@example.com | | 3 | Phoebe | poetry_man@example.com | | 4 | Jim | u_f_o@example.com | | 5 | Timi | big_voice@example.com | | 7 | Irma | soulqueen_NOLA@example.com | +---------+--------+----------------------------+ 5 rows in set (0.00 sec)

Truy vấn này xác định dấu @ là một ký tự thoát và trả về mọi hàng có cột email chứa ít nhất một dấu gạch dưới. Nếu bạn xóa mệnh đề ESCAPE , truy vấn sẽ trả về mọi hàng từ bảng, vì mỗi hàng chứa ít nhất một dấu @ .

Kết luận

Bằng cách đọc hướng dẫn này, bạn đã học cách sử dụng và thoát khỏi các ký tự đại diện database dựa trên SQL. Các lệnh được nêu ở đây sẽ hoạt động trên bất kỳ hệ quản trị database nào sử dụng SQL. Lưu ý mọi database SQL sử dụng cách triển khai ngôn ngữ duy nhất của riêng nó, vì vậy bạn nên tham khảo tài liệu chính thức của DBMS để có mô tả đầy đủ hơn về từng lệnh và tập hợp đầy đủ các tùy chọn của chúng.

Nếu bạn muốn tìm hiểu thêm về cách làm việc với SQL, ta khuyến khích bạn xem các hướng dẫn khác trong loạt bài này về Cách sử dụng SQL .

Tags:

Các tin liên quan