Ringkasan. dalam tutorial ini, Anda akan belajar cara menggunakan operator MySQL LIKE untuk meminta data berdasarkan pola yang ditentukan
Pengantar operator LIKE MySQL
Operator LIKE_ adalah operator logis yang menguji apakah string berisi pola yang ditentukan atau tidak
Inilah sintaks dari operator LIKE
expression LIKE pattern ESCAPE escape_character
Code language: SQL (Structured Query Language) (sql)Dalam sintaks ini, jika
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)_1 cocok denganSELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)2, operator LIKE mengembalikan 1. Jika tidak, ia mengembalikan 0MySQL menyediakan dua karakter wildcard untuk membangun pola. persentase
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)_4 dan garis bawahSELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)5- Persentase (
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)4 ) wildcard cocok dengan string nol atau lebih karakter apa pun - Karakter pengganti garis bawah (
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)5 ) cocok dengan karakter tunggal apa pun
Misalnya,
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)_8 cocok dengan string apa pun yang dimulai dengan karakterSELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)9 sepertiSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)0 danSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)1.SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)2 cocok dengan string apa pun yang dimulai denganSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)3 dan diikuti oleh karakter apa pun sepertiSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)4 danSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Saat pola berisi karakter wildcard dan Anda ingin memperlakukannya sebagai karakter biasa, Anda dapat menggunakan klausa
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)6Biasanya, Anda akan menggunakan operator LIKE dalam klausa
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)8 dari pernyataanSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)9 ,SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)0, danSELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)1Contoh operator MySQL LIKE
Mari berlatih dengan beberapa contoh penggunaan operator LIKE. Kami akan menggunakan tabel
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)_3 berikut dari database sampel untuk demonstrasiA) Menggunakan operator MySQL LIKE dengan persentase (%) contoh wildcard
Contoh ini menggunakan LIKEoperator untuk menemukan karyawan yang nama depannya dimulai dengan huruf
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)5SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)_Dalam contoh ini, MySQL memindai seluruh tabel
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)3 untuk menemukan karyawan yang nama depannya dimulai dengan hurufSELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)5 dan diikuti oleh sejumlah karakterContoh ini menggunakan operator LIKE untuk menemukan karyawan yang nama belakangnya diakhiri dengan string literal
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)9 e. g. ,SELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';
Code language: SQL (Structured Query Language) (sql)0,SELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)_Untuk memeriksa apakah string berisi substring, Anda dapat menggunakan persentase (
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)4 ) wildcard di awal dan akhir substringMisalnya, kueri berikut menggunakan operator LIKE untuk menemukan semua karyawan yang nama belakangnya berisi substring
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)9SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';
Code language: SQL (Structured Query Language) (sql)B) Menggunakan operator LIKE MySQL dengan contoh wildcard garis bawah (
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';
Code language: SQL (Structured Query Language) (sql)5 ).Untuk menemukan karyawan yang nama depannya dimulai dengan huruf
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';
Code language: SQL (Structured Query Language) (sql)6 , diakhiri dengan hurufSELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';
Code language: SQL (Structured Query Language) (sql)7, dan mengandung karakter tunggal antara e. g. ,SELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';
Code language: SQL (Structured Query Language) (sql)8 ,SELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';
Code language: SQL (Structured Query Language) (sql)9, Anda menggunakan karakter pengganti garis bawah (_) untuk membuat pola sebagai berikutSELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';
Code language: SQL (Structured Query Language) (sql)C) Menggunakan contoh operator MySQL NOT LIKE
MySQL memungkinkan Anda menggabungkan operator
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
Code language: SQL (Structured Query Language) (sql)_0 dengan operator LIKE untuk menemukan string yang tidak cocok dengan pola tertentuMisalkan Anda ingin mencari karyawan yang nama belakangnya tidak dimulai dengan huruf
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
Code language: SQL (Structured Query Language) (sql)3 sebagai berikutSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
Code language: SQL (Structured Query Language) (sql)Perhatikan bahwa polanya tidak peka huruf besar-kecil. Oleh karena itu, pola
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
Code language: SQL (Structured Query Language) (sql)4 danSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
Code language: SQL (Structured Query Language) (sql)5 mengembalikan hasil yang samaOperator MySQL LIKE dengan klausa ESCAPE
Terkadang pola mungkin berisi karakter wildcard e. g. , 10%, _20, dst
Dalam hal ini, Anda dapat menggunakan klausa
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)6 untuk menentukan karakter escape sehingga operator LIKE menginterpretasikan karakter wildcard sebagai karakter literalJika Anda tidak menentukan karakter escape secara eksplisit, karakter garis miring terbalik (
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
Code language: SQL (Structured Query Language) (sql)7) adalah karakter escape defaultMisalnya, jika Anda ingin mencari produk yang kode produknya berisi string
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
Code language: SQL (Structured Query Language) (sql)8 , Anda dapat menggunakan polaSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';
Code language: SQL (Structured Query Language) (sql)9 dengan karakter escape defaultSELECT productCode, productName FROM products WHERE productCode LIKE '%\_20%';
Code language: SQL (Structured Query Language) (sql)Alternatifnya, Anda dapat menentukan karakter pelarian yang berbeda e. g. ,
SELECT productCode, productName FROM products WHERE productCode LIKE '%\_20%';
Code language: SQL (Structured Query Language) (sql)0 menggunakan klausaSELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';
Code language: SQL (Structured Query Language) (sql)6