Ce înseamnă comanda like în sql? operatori LIKE și NOT LIKE. operatori IN și NOT IN

15.05.2020

LIKE și REGEXP _LIKE

Operatorii LIKE și REGEXP_LIKE în interogări Oracle SQL, simbolurile % și _, expresii regulateîn cereri

Foarte des în munca practica este necesar să se efectueze o căutare după un set de caractere oriunde într-o coloană - de exemplu, pentru a găsi toate înregistrările cu nume de produse care conțin un cuvânt sau pentru a căuta un cuvânt atât la singular, cât și la plural. În acest scop, puteți utiliza operatorul LIKE în Oracle SQL (acesta poate fi folosit și în SQL Server) și operatorul REGEXP _LIKE (acest operator nu este furnizat în SQL Server).

Să spunem imediat că LIKE și REGEXP _LIKE sunt folosite doar în situații relativ simple. Dacă trebuie să căutați bazat pe gramatică, filtrarea cuvintelor zgomote etc., atunci ar trebui să luați în considerare utilizarea căutării full-text.

Operatorul LIKE este folosit pentru a potrivi valorile caracterelor cu un model cu caractere metacaractere speciale (metacaractere). Rețineți că Oracle oferă patru variante ale acestui operator:

· „regular” LIKE - folosit pentru a interoga coloanele de șir cu codificări tradiționale;

· LIKEC - folosit pentru coloanele cu codare Unicode (în terminologia Oracle - Unicode complet);

· LIKE2 - pentru codificarea UCS2;

· LIKE4 - pentru codificarea UCS4.

Un exemplu de utilizare a acestui operator ar putea arăta astfel:

ca"R%";

În acest exemplu, ultimul _nume după cuvântul cheie where este coloana din care sunt preluate valorile șirului de testat, iar „R %” este condiția care trebuie testată. Trăsătură distinctivă Avantajul operatorului LIKE este că poate include caractere joker (metacaractere) speciale în condiție. Există doar două wildcard-uri disponibile pentru acest operator:

% - Reprezintă orice succesiune de zero sau mai multe caractere. În acest caz, valoarea „%” nu va fi niciodată egală cu NULL (IS NULL este folosit pentru a verifica astfel de valori);
_ - reprezintă orice caracter unic.

În exemplul nostru, am folosit caracterul joker %, astfel încât interogarea va returna toți angajații al căror nume de familie începe cu R .

Dacă dorim ca simbolurile procentuale (%) și sublinierea (_) să fie tratate de Oracle ca caractere obișnuite care pot fi căutate (mai degrabă decât ca caractere joker), avem parametrul ESCAPE. Folosind acest parametru puteți determina caracter special, după care wildcardul va fi considerat normal:

selectați last_name din hr.employees unde last_name ca„R\%” ESCAPE „\”;

În exemplul nostru, am definit caracterul \ pentru a anula valoarea wildcard și dacă avem un angajat cu numele de familie R %, atunci cererea o va returna cu siguranță. Cel mai obișnuit caracter de escape wildcard este backslash (\), care este cel mai comun sens pentru programatorii C, dar puteți folosi orice caracter definindu-l cu ESCAPE.

Condiția LIKE nu este dificil de utilizat, dar gama sa de capabilități este foarte limitată. Mult mai mare funcţionalitate furnizează condiția REGEXP _LIKE, care vă permite să specificați o condiție de căutare folosind expresii regulate standard compatibile cu POSIX. Expresiile obișnuite sunt un subiect foarte mare care depășește domeniul de aplicare al acestui lucru curs de formare. Pentru referință completă la expresiile regulate acceptate de Oracle, consultați Anexa C a cărții de referință SQL din documentația Oracle. Aici vom da doar un exemplu simplu:

SELECTAȚI prenumele FROM angajaților

WHERE REGEXP_LIKE (prenume, "^Ste(v|ph)en$");

În acest exemplu, returnăm informații despre toți angajații al căror nume începe cu Ste, se termină cu en și are între v sau ph. Rezultatul ar putea arăta astfel:

Steven

Compară o expresie șir cu un model dintr-o expresie SQL.

Sintaxă

expresie Ca "eşantion"

Sintaxa operatorului Ca include următoarele componente:

Note

Operator Ca poate fi folosit pentru a găsi valorile câmpurilor care se potrivesc cu un model specificat. Ca șablon puteți specifica o valoare completă (de exemplu, cum ar fi „Smith”) sau puteți utiliza metacaractere pentru a obține o serie de valori (de exemplu, cum ar fi „Sm*”).

În expresii operatorul Ca poate fi folosit pentru a compara o valoare de câmp cu o expresie șir. De exemplu, dacă în interogare SQL specificați Ca „C*”, interogarea va returna toate valorile câmpului care încep cu litera C. Într-o interogare cu parametri, puteți solicita utilizatorului să introducă un model de căutare.

Următorul exemplu returnează date care încep cu litera P, urmată de orice literă de la A la F și trei cifre:

Ca „P###”

Următorul tabel arată cum se utilizează operatorul Ca pentru a verifica diferite modele:

Tip de potrivire	Eşantion	Corespondenţă
Personaje multiple		aa, aBa, aBBBA
	abv, AABB, Tsab
Personaje speciale
Personaje multiple		abvwhere, abc
Un singur personaj		aaa, a3a, aBa
O cifră		a0a, a1a, a2a
Gama de caractere
În afara razei

Amestecat

Operatorul LIKE este folosit în clauza WHERE pentru a căuta un model specific într-o coloană.

Instrucțiunea SQL LIKE

Operatorul LIKE este folosit pentru a căuta un anumit model într-o coloană.

Sintaxa SQL LIKE

Versiunea demo a bazei de date

În acest tutorial vom folosi binecunoscuta bază de date Northwind.

Mai jos este o selecție din tabelul „Clienți”:

ID-ul de utilizator	Numele clientului	Persoana de contact	Adresa	oraş	Cod poștal	Ţară
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germania
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitucion 2222	Mexic D.F.	05021	Mexic
3	Antonio Moreno Taqueria	Antonio Moreno	Mataderos 2312	Mexic D.F.	05023	Mexic
4	În jurul Cornului	Thomas Hardy	120 Hanovra Sq.	Londra	WA1 1DP	Regatul Unit
5	Berglunds snabbkop	Christina Berglund	Berguvsvagen 8	Lulea	S-958 22	Suedia

Exemple de operator SQL LIKE

Următoarea instrucțiune SQL selectează toți clienții cu un oraș care începe cu litera „s”:

Sfat: Semnul „%” este folosit pentru a defini masca (litere lipsă) înainte și după desen. Veți afla mai multe despre metacaracterele în următorul capitol.

Următoarea instrucțiune SQL selectează toți clienții cu un oraș care se termină cu litera „s”:

Următoarea instrucțiune SQL selectează toți clienții cu o țară care conține modelul „terren”:

Utilizarea unui cuvânt cheie vă împiedică să selectați înregistrări care nu se potrivesc cu modelul.

Următoarea instrucțiune SQL selectează toți clienții dintr-o țară care nu conține modelul „teren”.

Utilitatea clauzei LIKE se bazează pe operatorii generici pe care îi acceptă. Clauza LIKE returnează valoarea booleană TRUE dacă comparația găsește valori care se potrivesc.

Senzitivitatea majusculelor pentru un anumit DBMS este foarte importantă pentru funcționarea clauzei LIKE. De exemplu, Microsoft SQL Server valoarea implicită nu face distincție între majuscule și minuscule (deși poate fi configurată în consecință). În acest fel, SQL Server va trata șirurile DAD și dad ca la fel. Pe de altă parte, platforma Oracle este sensibilă la majuscule și minuscule, iar șirurile DAD și dad vor fi diferite aici. Iată un exemplu pentru a ilustra mai bine acest punct.

SELECTAȚI * FROM autorii WHERE lname LIKE „LARS%”

Această interogare Microsoft SQL Server va prelua înregistrări din tabelul autorilor unde numele de familie (lname) are forma „larson” sau „lars”, deși interogarea specifică căutarea folosind majuscule („LARS%)”. În Oracle, această interogare nu va găsi numele de familie „Larson” sau „Lars”, deoarece Oracle face o comparație care face distincție între majuscule și minuscule.

DB2

Platforma DB2 acceptă sintaxa clauzei LIKE standard ANSI SQL . Secvențele de anulare sunt acceptate.

Platforma DB2 ține cont de majuscule și minuscule, astfel încât implementarea clauzei LIKE aici ține cont de majuscule și minuscule. Pentru a vă asigura că valorile din diferite registre sunt întotdeauna comparate fără a ține seama de majuscule și minuscule, trebuie să utilizați funcția UPPER sau TRANSLATE. În plus, DB2 convertește implicit pagina de cod a unui model de șir sau a secvenței de anulare în pagina de cod a unei expresii, cu excepția cazului în care este definită cu o clauză FOR BIT DATA.

MySQL

Platforma MySQL acceptă sintaxa ANSI pentru clauza LIKE. Caracterele wildcard % și liniuța de subliniere (_) sunt acceptate. Clauza ESCAPE este, de asemenea, acceptată.

În plus, MySQL acceptă funcțiile speciale REGEXP și NOT RLIKE, care sunt folosite la verificarea expresiilor regulate. MySQL după versiunea 3.23.4 nu face distincție între majuscule și minuscule în mod implicit.

Oracol

Platforma Oracle acceptă sintaxa ANSI pentru clauza LIKE. Caracterele wildcard % și liniuța de subliniere (_) sunt acceptate. Clauza ESCAPE este, de asemenea, acceptată. Sintaxa pentru clauza LIKE din Oracle este următoarea.

WHERE expresia (LIKE | LIKEC | LIKE2 |

LIKE4) string_pattern

Elementele de sintaxă specifice Oracle au următoarele semnificații.

Se folosește setul complet de caractere UNICODE.

Se folosește setul de caractere UNICODE USC2.

Se utilizează setul de caractere UNICODE USC4.

Deoarece platforma Oracle ține cont de majuscule și minuscule, trebuie să includeți o expresie, string_pattern sau ambele în funcția UPPER. În acest caz, veți compara întotdeauna ceea ce aveți nevoie.

PostgreSQL

Platforma PostgreSQL acceptă sintaxa clauzei ANSI LIKE. Caracterele wildcard % și liniuța de subliniere (_) sunt acceptate. Secvențele de anulare sunt, de asemenea, acceptate.

PostgreSQL este sensibil la majuscule în mod implicit. Pentru comparații care nu țin cont de majuscule, PostgreSQL are cuvântul cheie ILIKE. De asemenea, puteți utiliza operatorul - ca echivalent al lui LIKE și -* ca echivalent al lui ILIKE și !- și !-* ca echivalent al lui NOT LIKE și, respectiv, NOT ILIKE. Acestea sunt toate extensiile standard ANSI care există în PostgreSQL.

De exemplu, următoarele interogări sunt echivalente din punct de vedere funcțional.

SELECTAȚI * FROM autorii WHERE oraș LIKE „%ville”; SELECT * FROM autorii WHERE oraș -- ^ville";

Deoarece aceste exemple folosesc litere mici, este posibil să întâmpinați o problemă de sensibilizare a majusculelor. Adică, interogarea caută șirul „%ville” cu litere mici, iar tabelul poate conține valori cu majuscule care nu vor fi incluse în rezultate - „BROWNSVILLE”, „NASHVILLE”, „HUNTSVILLE”. Această problemă poate fi rezolvată așa cum se arată în exemplul următor.

Conversia valorilor în majuscule

SELECT * FROM autorii WHERE city LIKE UPPER("%ville");

Efectuăm o comparație fără majuscule SELECT * FROM autorii WHERE oraș ~~* "%ville";

SELECTAȚI * FROM autorii WHERE oraș LIKE „%ville”;

Ar trebui să știți (deși este dincolo de scopul acestei cărți) că PostgreSQL acceptă și expresiile regulate POSIX. Detaliile sunt furnizate în descrierea platformei.

SQL Server

Platforma SQL Server acceptă sintaxa clauzei ANSI LIKE. Secvențele de anulare sunt acceptate. Următorii operatori suplimentari de generalizare sunt, de asemenea, acceptați.

Se potrivește cu orice valoare dintr-un set specificat, cum ar fi , sau cu un interval, cum ar fi [k-p].

[L]- se potrivește cu orice caracter care nu este inclus în setul sau intervalul specificat.

Folosind instrucțiunile wildcard suplimentare ale SQL Server, obțineți caracteristici suplimentare. De exemplu, puteți prelua înregistrări pentru autori ale căror nume de familie sunt Carson, Carsen, Karson sau Karsen.

SELECTAȚI * FROM autorii WHERE au_lname LIKE „arsn”

Subiectul 3.2. Preluarea datelor utilizând clauza SELECT

Toate expresiile SQL destinate să recupereze date din tabelele bazei de date existente încep cu cuvântul cheie (operator) SELECT (selectare). Pentru a rafina interogarea, sunt utilizați operatori suplimentari, cum ar fi FROM (de la), WHERE (unde) etc.

Cea mai simplă sintaxă pentru o interogare SELECT este:

SELECTA<список столбцов>

DIN<список таблиц>

Sunt indicați operatorii care ar putea să nu fie prezenți în cerere. Acești operatori sunt utilizați pentru a rafina cererea de regăsire a datelor:

p UNDE(unde) – indică înregistrările care ar trebui incluse în tabelul rezultat (filtru de înregistrare);

p GROUP BY(grupare după) – grupează înregistrările după valorile anumitor coloane;

p AVÂND(având, furnizat) – indică grupuri de înregistrări care ar trebui incluse în tabelul rezultat (filtru de grup);

p COMANDA PENTRU(sortează după) – sortează (aranjează) înregistrările.

Operatori SELECTAŞi DIN sunt obligatorii. Cuvântul cheie SELECT spune bazei de date că această declarație este o solicitare de a prelua informații. După cuvântul SELECT, despărțit de "," sunt listate numele coloanelor al căror conținut este solicitat. După cuvântul FROM, este indicată o listă de nume de tabel (prin ",") din care sunt preluate informații.

Exemplu:

SELECTAȚI NUMELE, NUMELE

Următoarea interogare preia toate valorile NUME și PRENUME din tabelul STUDENTI. Rezultatul este un tabel format din 2 coloane.

Ordinea coloanelor din acest tabel se potrivește cu ordinea câmpurilor specificate în interogare, nu cu ordinea lor în tabelul STUDENTI.

Dacă trebuie să obțineți toate coloanele unui tabel, atunci în loc de o listă de coloane, trebuie doar să specificați simbolul ( *) .

Exemplu:

SELECTA *

Imediat după instrucțiunea SELECT înainte de lista de coloane, puteți utiliza cuvinte cheie TOATE (Toate)și DISTINCT (diferit), care specifică ce înregistrări să prezinte în tabelul rezultat. Dacă aceste cuvinte cheie nu sunt utilizate, implicația este că trebuie selectate toate înregistrările (care este, de asemenea, aceeași cu utilizarea cuvântului cheie ALL). Dacă utilizați DISTINCT, numai înregistrările unice sunt prezentate în tabelul rezultat. În plus, dacă tabelul sursă conține mai multe înregistrări identice, atunci este selectată doar prima.

Exemplu:

SELECTAȚI ORAȘUL DISTINCT

Dacă clauza FROM specifică mai multe tabele, clauza SELECT trebuie să conțină nume de coloane prefixe, indicând cărei tabel aparțin. Prefixul este separat de numele coloanei printr-un punct.

Exemplu:

Expresie STUDENTI.NUME înseamnă coloană NUME de la masă STUDENTI

Titlurile coloanelor din tabelul rezultat pot fi redefinite la discreția dvs. prin atribuirea așa-numitelor pseudonime. Pentru a face acest lucru, în lista de coloane după coloana corespunzătoare, scrieți o expresie ca: AS antet de coloană

Exemplu:

SELECTAȚI NUMELE CA Prenume, NUMELE CA Numele de familie

Aliasurile pot fi, de asemenea, specificate pentru fiecare tabel după cuvântul cheie FROM. Pentru a face acest lucru, trebuie doar să specificați alias-ul separat printr-un spațiu imediat după numele tabelului corespunzător. Aliasurile de tabel, care sunt mai scurte decât numele lor, sunt utile pentru interogări complexe.

Exemplu:

SELECTAȚI T1.NAME , T1.SURNAME, T2.SUM_STIPEND

DE LA STUDII T1, STIPENDA T2;

clauza WHERE

Condițiile de căutare din clauza WHERE sunt expresii booleene, de exemplu. acceptă una din trei valori posibile: adevărat, falsŞi NUL (acest lucru se întâmplă când unele elemente din expresie sunt NULL). Deci, în SQL avem de-a face cu logica cu trei valori.

La alcătuirea expresiilor logice, sunt folosite cuvinte cheie speciale și simboluri ale operațiilor de comparare, care sunt numite predicate:

ü predicate de comparare: (=), (<), (>), (<>), (<=), (>=);

ü LIKE, NU LIKE;

ü TOATE, UNELE, ORICE;

Exemplu:

UNDE NUME = „Petrov”;

Ca urmare a executării interogării, dintr-o coloană se va obține un tabel care conține numele tuturor elevilor cu numele de familie Petrov, care se află în tabelul STUDENTI.

Exemplu:

Scrieți o cerere pentru a primi numele și prenumele studenților care studiază în anul 3 și care primesc o bursă:

SELECTAȚI NUMELE, NUMELE

UNDE KURS=3 ȘI STIPEND>0;

Operator INTRE

Predicatul BETWEEN (între) vă permite să specificați o expresie pentru a verifica dacă o valoare este inclusă în intervalul definit de valorile limită.

Exemplu:

SELECTAȚI SUBJECT_NAME

UNDE ORELE ÎNTRE 30 SI 40;

Afișați numele subiectelor pentru care este alocat numărul de ore în intervalul de la 30 la 40.

Valorile limită sunt incluse în intervalul de valori cu care se face comparația.

O expresie cu predicate de comparație este echivalentă cu următoarele:

SELECTAȚI SUBJECT_NAME

UNDE ORE>30 SI ORE<40;

Pe lângă datele numerice, în expresiile cu BETWEEN pot fi folosite următoarele tipuri de date: caracter, bit, dată-oră.

operatori IN și NOT IN

Predicatele IN (în) și NOT IN (nu în) sunt folosite pentru a verifica apariția unei valori într-o listă dată de valori.

Un predicat construit folosind IN este considerat adevărat dacă valoarea câmpului al cărui nume este indicat în stânga lui IN se potrivește cu una dintre valorile din listă.

Exemplu:

SELECTAȚI STUDENT_ID

UNDE MARCAȚI (4, 5);

Obțineți informații din tabelul EXAM_MARKS despre studenții care au note la examene de numai 4 și 5.

NU IN– nu se potrivește cu niciuna dintre valori

Exemplu:

SELECTAȚI STUDENT_ID

UNDE NU SE MARCĂ (0, 1, 2, 3);

Obțineți informații din tabelul EXAM_MARKS despre studenții care nu au note nesatisfăcătoare la examen.

operatori LIKE și NOT LIKE

Predicatele LIKE și NOT LIKE sunt folosite pentru a testa potrivirile parțiale ale șirurilor de caractere. Acest operator analizează valorile șirurilor câmpurilor pentru a determina dacă șirul specificat în operatorul LIKE este inclus în caracterul șir_valoare al câmpului care este verificat.

Criteriul de potrivire parțială este specificat folosind două masca personaje: semn de procent (%) și liniuță (_). Semnul procentual (%) înseamnă orice set de caractere, inclusiv caracterul gol, iar liniuța de subliniere (_) înseamnă orice caracter unic.

Exemplu:

SELECTA *

UNDE PRENUME CA „P%”;

Ca rezultat al interogării, este returnat un tabel care conține date despre elevii al căror nume de familie începe cu litera „P”.

Dacă trebuie să excludeți toți studenții cu numele de familie „Petrov”, trebuie să executați următoarea interogare:

SELECTA *

UNDE NUMELE NU CA „Petrov”;

Dacă este necesar să se includă caractere de subliniere sau procente în „model”, este necesar ca astfel de caractere să nu fie percepute de interpretul SQL ca caractere wildcard. Pentru ca un procent sau un caracter de subliniere să fie luat la propriu, trebuie să fie precedat de un caracter de control special. Acest caracter poate fi definit arbitrar, atâta timp cât nu apare ca element de date.

Exemplu:

SELECTează Nume, Adresă, Reducere_Procent

DE LA Clienți

WHERE Discount_percentage LIKE „20#%”

Aici, cuvântul cheie ESCAPE este urmat de simbolul care este folosit ca control. În același mod, puteți dezactiva caracterul de control în sine.

Exemplu:

LIKE „_ \ _P"

În această expresie, caracterul „\” este declarat ca un caracter ESC folosind cuvântul cheie ESCAPE. Primul caracter „_” se va potrivi, ca și înainte, cu orice caracter din șirul care este verificat, iar al doilea caracter „_” va fi interpretat literal, ca un caracter de subliniere obișnuit.

operator IS NULL

Predicatul IS NULL este folosit pentru a identifica înregistrările în care o anumită coloană nu are valoare.

Exemplu:

Puteți obține înregistrări despre clienții pentru care adresa nu este specificată folosind următoarea solicitare:

SELECTAȚI Nume, Adresă, Regiune

DE LA Clienți

WHERE Adresa ESTE NULL;

Pentru a prelua înregistrări în care coloana Adresă conține o anumită valoare (adică non-NULL), puteți utiliza o expresie similară, dar cu operatorul logic NOT:

SELECTAȚI Nume, Adresă, Regiune

DE LA Clienți

WHERE Adresa NU ESTE NULĂ;

Predicatele de comparație nu trebuie folosite cu NULL, cum ar fi „Adresa=NULL”