Chúng ta đã thảo luận về toán tử LIKE trong SQL, được sử dụng để so sánh một giá trị với các giá trị tương tự bởi sử dụng các toán tử wildcard.
SQL cung cấp hai toán tử wildcard để sử dụng kết hợp với toán tử LIKE:
| Toán tử Wildcard | Miêu tả |
|---|---|
| Ký hiệu % | So khớp với một hoặc nhiều ký tự. Ghi nhớ rằng, MS Access sử dụng dấu sao (*) thay cho ký hiệu phần trăm (%) |
| Dấu gạch dưới (_) | So khớp với một ký tự. Ghi nhớ rằng, MS Access sử dụng dấu hỏi (?) thay cho dấu gạch dưới (_) để so khớp với bất kỳ ký tự nào |
Ký hiệu phần trăm biểu diễn 0, 1 hoặc nhiều ký tự. Dấu gạch dưới biểu diễn một số hoặc một ký tự đơn. Các ký hiệu có thể được sử dụng kết hợp với nhau.
Cú pháp
Cú pháp cơ bản của '%' và '_' là như sau:
SELECT FROM ten_bang WHERE cot LIKE 'XXXX%' hoac SELECT FROM ten_bang WHERE cot LIKE '%XXXX%' hoac SELECT FROM ten_bang WHERE cot LIKE 'XXXX_' hoac SELECT FROM ten_bang WHERE cot LIKE '_XXXX' hoac SELECT FROM ten_bang WHERE cot LIKE '_XXXX_'
Bạn có thể kết hợp N điều kiện bởi sử dụng toán tử AND hoặc OR. Ở đây, XXX có thể là bất kỳ giá trị số hoặc chuỗi nào.
Ví dụ
Dưới đây là ví dụ minh họa mệnh đề WHERE có các mệnh đề LIKE khác nhau với các toán tử '%' và '_'.
| Lệnh | Miêu tả |
|---|---|
| WHERE HOCPHI LIKE '200%' | Tìm kiếm bất kỳ giá trị nào bắt đầu với 200 |
| WHERE HOCPHI LIKE '%200%' | Tìm kiếm bất kỳ giá trị nào mà có 200 ở bất kỳ vị trí nào |
| WHERE HOCPHI LIKE '_00%' | Tìm kiếm bất kỳ giá trị nào có 00 trong vị trí thứ hai và ba |
| WHERE HOCPHI LIKE '2_%_%' | Tìm kiếm bất kỳ giá trị nào mà bắt đầu với 2 và có độ dài tối thiểu là 3 ký tự |
| WHERE HOCPHI LIKE '%2' | Tìm kiếm bất kỳ giá trị nào mà kết thúc với 2 |
| WHERE HOCPHI LIKE '_2%3' | Tìm kiếm bất kỳ giá trị nào mà có 2 trong vị trí thứ hai và kết thúc với 3 |
| WHERE HOCPHI LIKE '2___3' | Tìm kiếm bất kỳ giá trị nào có 5 chữ số bắt đầu với 2 và kết thúc với 3 |
Còn đây là ví dụ thực sự, bạn theo dõi bảng SINHVIEN có các bản ghi sau:
+----+---------+-----+-----------+--------+ | ID | TEN | TUOI| KHOAHOC | HOCPHI | +----+---------+-----+-----------+---------+ | 1 | Hoang | 21 | CNTT | 4000000 | | 2 | Viet | 19 | DTVT | 3000000 | | 3 | Thanh | 18 | KTDN | 4000000 | | 4 | Nhan | 19 | CK | 4500000 | | 5 | Huong | 20 | TCNH | 5000000 | +----+---------+-----+-----------+---------+
Ví dụ sau sẽ hiển thị tất cả bản ghi từ bảng SINHVIEN với HOCPHI bắt đầu với 400:
SQL> SELECT * FROM SINHVIEN WHERE HOCPHI LIKE '400%';
Ví dụ trên sẽ cho kết quả:
+----+----------+-----+-----------+----------+ | ID | TEN | TUOI| KHOAHOC | HOCPHI | +----+---------+-----+-----------+---------+ | 1 | Hoang | 21 | CNTT | 4000000 | | 3 | Thanh | 18 | KTDN | 4000000 | +----+----------+-----+-----------+----------+
Truy vấn con (còn được gọi truy vấn phụ hay truy vấn lồng nhau) là một truy vấn bên trong truy vấn SQL khác và được nhúng bên trong mệnh đề WHERE.
Một truy vấn con được sử dụng để trả về dữ liệu mà sẽ được sử dụng trong truy vấn chính như là một điều kiện để thu hẹp dữ liệu được thu nhận.
Các truy vấn con có thể được sử dụng với các lệnh SELECT, INSERT, UPDATE VÀ DELETE cùng với các toán tử như: =, <, >, >=, <=, IN, BETWEEN ...
Các truy vấn con phải tuân theo các qui tắc sau:
- Các truy vấn con phải được bao trong các dấu ngoặc đơn.
- Một truy vấn con có thể chỉ có một cột trong mệnh đề SELECT, trừ khi có nhiều cột là trong truy vấn chính cho truy vấn con để so sánh các cột đã chọn của nó.
- Một ORDER BY không thể được sử dụng trong một truy vấn con, mặc dù truy vấn chính có thể sử dụng một ORDER BY. GROUP BY có thể được sử dụng để thực hiện cùng tính năng như ORDER BY trong một truy vấn con.
- Các truy vấn con, mà trả về nhiều hơn một hàng, có thể chỉ được sử dụng với các toán tử nhân, ví dụ: toán tử IN.
- Danh sách của SELECT không thể bao gồm bất kỳ tham chiếu nào tới các giá trị mà ước lượng một BLOB, ARRAY, CLOB hoặc NCLOB.
- Một truy vấn con không thể bị bao quanh một cách trực tiếp trong một hàm tập hợp.
- Toán tử BETWEEN không thể được sử dụng với một truy vấn con; tuy nhiên, toán tử BETWEEN có thể được sử dụng bên trong truy vấn con.
Truy vấn con với lệnh SELECT trong SQL
Các truy vấn con thường xuyên được sử dụng với lệnh SELECT. Cú pháp cơ bản của truy vấn con với lệnh SELECT là như sau:
SELECT ten_cot [, ten_cot ] FROM bang1 [, bang2 ] WHERE ten_cot TOAN_TU (SELECT ten_cot [, ten_cot ] FROM bang1 [, bang2 ] [WHERE])
Ví dụ
Bảng SINHVIEN có các bản ghi sau:
+----+---------+-----+-----------+--------+ | ID | TEN | TUOI| KHOAHOC | HOCPHI | +----+---------+-----+-----------+---------+ | 1 | Hoang | 21 | CNTT | 4000000 | | 2 | Viet | 19 | DTVT | 3000000 | | 3 | Thanh | 18 | KTDN | 4000000 | | 4 | Nhan | 19 | CK | 4500000 | | 5 | Huong | 20 | TCNH | 5000000 | +----+---------+-----+-----------+---------+
Bây giờ, chúng ta kiểm tra truy vấn con với lệnh SELECT như sau:
SQL> SELECT * FROM SINHVIEN WHERE ID IN (SELECT ID FROM SINHVIEN WHERE HOCPHI > 4000000) ;
Ví dụ trên sẽ cho kết quả:
+----+----------+-----+---------+----------+ | ID | TEN | TUOI| KHOAHOC | HOCPHI | +----+----------+-----+---------+----------+ | 4 | Nhan | 19 | CK | 4500000 | | 5 | Huong | 20 | TCNH | 5000000 | +----+----------+-----+---------+----------+
Truy vấn con với lệnh INSERT trong SQL
Các truy vấn con cũng có thể được sử dụng với lệnh INSERT. Lệnh INSERT sử dụng dữ liệu được trả về từ truy vấn con để chèn nó vào trong bảng khác. Dữ liệu được lựa chọn trong truy vấn con có thể được sửa đổi với bất kỳ hàm xử lý ký tự, date hoặc số nào.
Cú pháp cơ bản của truy vấn con với lệnh INSERT là như sau:
INSERT INTO ten_bang [ (cot1 [, cot2 ]) ] SELECT [ *|cot1 [, cot2 ] FROM bang1 [, bang2 ] [ WHERE GIA_TRI TOAN_TU ]
Ví dụ
Theo dõi bảng SINHVIEN_BK53 với cấu trúc tương tự như bảng SINHVIEN. Bây giờ, sao chép cả bảng SINHVIEN vào trong bảng SINHVIEN_BK53, sau đây là cú pháp.
SQL> INSERT INTO SINHVIEN_BK53 SELECT * FROM SINHVIEN WHERE ID IN (SELECT ID FROM SINHVIEN) ;
Truy vấn con với lệnh UPDATE trong SQL
Truy vấn con có thể được sử dụng kết hợp với lệnh UPDATE. Hoặc một cột đơn hoặc nhiều cột trong một bảng có thể được cập nhật khi sử dụng một truy vấn con với lệnh UPDATE trong SQL.
Cú pháp cơ bản của truy vấn con với lệnh UPDATE là như sau:
UPDATE table SET ten_cot = gia_tri_moi [ WHERE TOAN_TU [ GIA_TRI ] (SELECT TEN_COT FROM TEN_BANG) [ WHERE) ]
Ví dụ
Giả sử, chúng ta đã có bảng SINHVIEN_BK53 có sẵn, là một backup (sao lưu) của bảng SINHVIEN.
Ví dụ sau cập nhật HOCPHI * 1,2 trong bảng SINHVIEN cho tất cả sinh viên có TUOI là lớn hơn hoặc bằng 21.
SQL> UPDATE SINHVIEN SET HOCPHI = HOCPHI * 1.2 WHERE TUOI IN (SELECT TUOI FROM SINHVIEN_BK53 WHERE TUOI >= 21 );
Lệnh này sẽ tác động lên một hàng và cuối cùng bảng SINHVIEN sẽ có các bản ghi sau:
+----+---------+-----+-----------+--------+ | ID | TEN | TUOI| KHOAHOC | HOCPHI | +----+---------+-----+-----------+---------+ | 1 | Hoang | 21 | CNTT | 4800000 | | 2 | Viet | 19 | DTVT | 3000000 | | 3 | Thanh | 18 | KTDN | 4000000 | | 4 | Nhan | 19 | CK | 4500000 | | 5 | Huong | 20 | TCNH | 5000000 | +----+---------+-----+-----------+---------+
Truy vấn con với lệnh DELETE trong SQL
Truy vấn con có thể được sử dụng kết hợp với lệnh DELETE giống như bất kỳ lệnh khác đã được đề cập ở trên.
Cú pháp cơ bản của truy vấn con với lệnh DELETE là như sau:
DELETE FROM TEN_BANG [ WHERE TOAN_TU [ VALUE ] (SELECT TEN_COT FROM TEN_BANG) [ WHERE) ]
Ví dụ
Giả sử, chúng ta đã có bảng SINHVIEN_BK53 có sẵn, là một backup (sao lưu) của bảng SINHVIEN.
Ví dụ sau xóa các bản ghi của tất cả khách hàng có TUOI lớn hơn 20 từ bảng SINHVIEN.
SQL> DELETE FROM SINHVIEN WHERE TUOI IN (SELECT TUOI FROM SINHVIEN_BK53 WHERE TUOI > 20 );
Lệnh này sẽ tác động lên hai hàng và cuối cùng bảng SINHVIEN sẽ có các bản ghi sau:
+----+---------+-----+-----------+--------+ | ID | TEN | TUOI| KHOAHOC | HOCPHI | +----+---------+-----+-----------+---------+ | 2 | Viet | 19 | DTVT | 3000000 | | 3 | Thanh | 18 | KTDN | 4000000 | | 4 | Nhan | 19 | CK | 4500000 | +----+---------+-----+-----------+---------+
Một Sequence là một tập hợp các số nguyên 1, 2, 3, … mà được tạo theo nhu cầu. Các Sequence thường xuyên được sử dụng trong Database bởi vì nhiều ứng dụng yêu cầu mỗi hàng trong bảng chứa một giá trị duy nhất, và các Sequence cung cấp một cách dễ dàng để tạo chúng.
Chương này trình bày cách sử dụng của Sequence trong MySQL.
Sử dụng AUTO_INCREMENT column
Cách đơn giản nhất trong MySQL để sử dụng các Sequence là để định nghĩa một cột dạng AUTO_INCREMENT và để phần còn lại cho MySQL xử lý.
Ví dụ
Ví dụ sau sẽ tạo bảng và sau đó nó sẽ chèn một số hàng vào trong bảng này, mà không bắt buộc cung cấp bản ghi ID bởi vì nó được tự động tăng bởi MySQL.
mysql> CREATE TABLE INSECT -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO INSECT (id,name,date,origin) VALUES -> (NULL,'housefly','2001-09-10','kitchen'), -> (NULL,'millipede','2001-09-10','driveway'), -> (NULL,'grasshopper','2001-09-10','front yard'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM INSECT ORDER BY id; +----+-------------+------------+------------+ | id | name | date | origin | +----+-------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | millipede | 2001-09-10 | driveway | | 3 | grasshopper | 2001-09-10 | front yard | +----+-------------+------------+------------+ 3 rows in set (0.00 sec)
Lấy các giá trị AUTO_INCREMENT
Hàm LAST_INSERT_ID() là một hàm SQL, vì thế, bạn có thể sử dụng nó từ bên trong bất kỳ Client nào mà hiểu cách thông báo lệnh SQL. Riêng PERL và PHP Script cung cấp các hàm riêng để lấy giá trị AUTO_INCREMENT của bản ghi cuối.
Ví dụ cho PERL
Sử dụng thuộc tính mysql_insertid để lấy giá trị AUTO_INCREMENT được tạo bởi một truy vấn. Thuộc tính này được truy cập thông qua hoặc một Database Handle hoặc một Statement Handle, phụ thuộc vào cách bạn thông báo truy vấn. Ví dụ sau tham chiếu nó thông qua Database Handle.
$dbh->do ("INSERT INTO INSECT (name,date,origin) VALUES('moth','2001-09-14','windowsill')"); my $seq = $dbh->{mysql_insertid};
Ví dụ cho PHP
Sau khi thông báo một truy vấn mà tạo một giá trị AUTO_INCREMENT, lấy giá trị này bằng cách gọi hàm mysql_insert_id():
mysql_query ("INSERT INTO INSECT (name,date,origin) VALUES('moth','2001-09-14','windowsill')", $conn_id); $seq = mysql_insert_id ($conn_id);
Đánh số lại một Sequence đang tồn tại
Có một tình huống khi bạn đã xóa nhiều bản ghi từ một bảng và bạn muốn đánh số lại tất cả các bản ghi. Điều này được thực hiện bởi sử dụng một mẹo đơn giản, nhưng bạn nên cẩn thận khi thực hiện nếu trong trường hợp bảng của bạn đang kết hợp với bảng khác.
Nếu bạn quyết định rằng việc đánh số lại một cột AUTO_INCREMENT là không thể tránh khỏi, thì cách để thực hiện là xóa cột từ bảng, sau đó thêm lại. Ví dụ sau minh họa cách đánh số lại các giá trị id trong bảng INSECT bởi sử dụng kỹ thuật này.
Có một tình huống khi bạn có nhiều bản sao của các bản ghi trong một bảng. Trong khi lấy các bản ghi này, nó là hợp lý hơn khi chỉ lấy một bản ghi duy nhất thay vì lấy cả các bản sao của bản ghi.
Từ khóa DISTINCT trong SQL, đã được trình bày trong các chương trước, được sử dụng kết hợp với lệnh SELECT để loại bỏ tất cả bản sao và chỉ lấy các bản ghi duy nhất.
Cú pháp
Cú pháp cơ bản của từ khóa DISTINCT để loại bỏ các bản sao là như sau:
SELECT DISTINCT cot1, cot2,.....cotN FROM ten_bang WHERE [dieu_kien]
Ví dụ
Bảng SINHVIEN có các bản ghi sau:
+----+---------+-----+-----------+--------+ | ID | TEN | TUOI| KHOAHOC | HOCPHI | +----+---------+-----+-----------+---------+ | 1 | Hoang | 21 | CNTT | 4000000 | | 2 | Viet | 19 | DTVT | 3000000 | | 3 | Thanh | 18 | KTDN | 4000000 | | 4 | Nhan | 19 | CK | 4500000 | | 5 | Huong | 20 | TCNH | 5000000 | +----+---------+-----+-----------+---------+
Đầu tiên, chúng ta xem cách truy vấn SELECT trả về bản sao của HOCPHI:
SQL> SELECT HOCPHI FROM SINHVIEN ORDER BY HOCPHI;
Trong kết quả thu được, HOCPHI 4000000 xuất hiện hai lần, và là một bản sao từ bảng ban đầu.
+----------+ | HOCPHI | +----------+ | 3000000 | | 4000000 | | 4000000 | | 4500000 | | 5000000 | +----------+
Bây giờ, sử dụng từ khóa DISTINCT với truy vấn SELECT trên, và xem kết quả:
SQL> SELECT DISTINCT HOCPHI FROM SINHVIEN ORDER BY HOCPHI;
Trong kết quả thu được, bạn sẽ không thấy bất kỳ bản sao nào.
+----------+ | HOCPHI | +----------+ | 3000000 | | 4000000 | | 4500000 | | 5000000 | +----------+
Nếu bạn nhận User Input thông qua một Webpage và chèn nó vào trong một SQL Database, thì tình cờ, bạn đã mở rộng cửa bảo mật ra bên ngoài, mà được biết đến với tên gọi là SQL Injection.
Chương này sẽ hướng dẫn bạn cách ngăn cản tình huống này xảy ra và giúp bạn bảo vệ Script của bạn và các lệnh SQL trong Server-Side Script như PERL Script.Injection thường xảy ra khi bạn yêu cầu input từ một người dùng, như tên của họ, và thay vì cung cấp tên, họ cung cấp cho bạn một lệnh SQL mà bạn sẽ chạy trên Database của mình mà không hay biết.
Đừng bao giờ tin vào dữ liệu được cung cấp bởi người dùng, xử lý dữ liệu này, và như một qui tắc, điều này được thực hiện bởi Pattern Matching (so khớp mẫu).
Trong ví dụ dưới, name bị giới hạn là các ký tự chữ-số cộng với dấu gạch dưới và có độ dài từ 8 đến 20 ký tự (bạn có thể sửa đổi nếu thấy cần thiết).
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) { $result = mysql_query("SELECT * FROM SINHVIEN WHERE name=$matches[0]"); } else { echo "user name not accepted"; }
Để minh họa vấn đề, bạn xem phần trích sau:
// gia su ban co input la nhu sau $name = "Hoang'; DELETE FROM SINHVIEN;"; mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'");
Lời gọi hàm được xem như để lấy một bản ghi từ bảng SINHVIEN, với cột name so khớp với name đã được xác định bởi người dùng. Thông thường, $name sẽ chỉ chứa các ký tự chữ-số và có thể có khoảng trống. Nhưng ở đây, bằng việc phụ thêm một truy vấn hoàn toàn mới tới $name, lời gọi tới Database sẽ gây ra vấn đề lớn: truy vấn DELETE bị tiêm vào sẽ xóa tất cả bản ghi từ bảng SINHVIEN.
May mắn là, nếu bạn sử dụng MySQL, hàm mysql_query() không cho phép Query Stacking hoặc thực thi nhiều truy vấn SQL trong một lời gọi hàm đơn. Nếu bạn nỗ lực để thực hiện nhiều truy vấn, lời gọi hàm sẽ thất bại.
Tuy nhiên, với PHP Database, ví dụ như SQLite và PostgreSQL, lại cho thực hiện nhiều truy vấn, thực thi tất cả truy vấn được cung cấp trong một chuỗi và điều này tạo ra một vấn đề rất nghiêm trọng.
Ngăn cản SQL Injection
Bạn có thể xử lý tất cả Escape Character một cách khéo léo trong các ngôn ngữ Scripting như PERL và PHP. MySQL extension cho PHP cung cấp hàm mysql_real_escape_string() để tránh các ký tự được nhập vào mà có ý nghĩa đặc biệt với MySQL.
if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name = mysql_real_escape_string($name); mysql_query("SELECT * FROM SINHVIEN WHERE name='{$name}'");
LIKE Quandary trong SQL
Để định vị một LIKE Quandary, một kỹ thuật do người dùng tạo phải chuyển đổi các ký tự '%' và '_' do người dùng cung cấp thành literal (hằng). Sử dụng hàm addcslashes(), một hàm mà giúp bạn xác định một dãy ký tự để thoát.
$sub = addcslashes(mysql_real_escape_string("%str"), "%_"); // $sub == \%str\_ mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");