Important
Traducerea este un efort al comunității, la care puteți să vă alăturați. În prezent, această pagină este tradusă 95.74%.
15.4. Lesson: Queries
Când scrieți o comandă SELECT ... interogați baza de date pentru informații.
Scopul acestei lecții: De a afla cum să creați interogări, care vor returna informații utile.
Notă
Dacă nu ați făcut asta în lecția precedentă, adăugați următoarele obiecte persoană în tabela people. Dacă primiți erori legate de constrângerile de cheie externă, va trebui să adăugați mai întâi obiectul «Main Road» în tabela de străzi.
insert into people (name,house_no, street_id, phone_no)
values ('Joe Bloggs',3,2,'072 887 23 45');
insert into people (name,house_no, street_id, phone_no)
values ('Jane Smith',55,3,'072 837 33 35');
insert into people (name,house_no, street_id, phone_no)
values ('Roger Jones',33,1,'072 832 31 38');
insert into people (name,house_no, street_id, phone_no)
values ('Sally Norman',83,1,'072 932 31 32');
15.4.1. Ordonarea Rezultatelor
Haideți să obținem o listă de persoane ordonate după numerele caselor lor:
select name, house_no from people order by house_no;
Rezultat:
name | house_no
--------------+----------
Joe Bloggs | 3
Roger Jones | 33
Jane Smith | 55
Sally Norman | 83
(4 rows)
Puteți sorta rezultatele după valorile a mai mult de o coloană:
select name, house_no from people order by name, house_no;
Rezultat:
name | house_no
--------------+----------
Jane Smith | 55
Joe Bloggs | 3
Roger Jones | 33
Sally Norman | 83
(4 rows)
15.4.2. Filtrare
Foarte des nu veți vedea fiecare înregistrare din baza de date - în mod special există mii de înregistrări și sunteți interesat doar de una sau două.
Iată un exemplu de filtru numeric care întoarce doar obiecte ale cărui house_no este mai mic de 50:
select name, house_no from people where house_no < 50;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Puteți combina filtre (definite utilizănd clauza WHERE) cu sortare (definită folosind clauza ORDER BY):
select name, house_no from people where house_no < 50 order by house_no;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Puteți filtra, de asemenea, pe baza datelor de text:
select name, house_no from people where name like '%s%';
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Am folosit clauza LIKE pentru a găsi toate numele care conțin un s. De remarcat că această interogare ține cont de capitalizare, deci înregistrarea Sally Norman nu a fost întoarsă.
Dacă doriți să căutați un șir de caractere indiferent de capitalizare, puteți executa o căutare care nu ține cont de capitalizare folosind clauza ILIKE:
select name, house_no from people where name ilike '%r%';
name | house_no
--------------+----------
Roger Jones | 33
Sally Norman | 83
(2 rows)
Acea interogare a returnat fiecare obiect people care conține un r sau un R în nume.
15.4.3. Îmbinări
Dar dacă doriți să vedeți detaliile persoanei și numele străzii în loc de ID-ul acesteia? Pentru a face asta, trebuie să legați cele două tabele într-o singură interogare. Să vedem un exemplu:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Notă
Cu legături, veți spune întotdeauna din ce tabele se extrage informația, în aceest caz persoane și străzi. De asemenea va trebui să precizați care chei trebuie să corespundă (cheia externă și cheia primară). Dacă nu faceți această precizare, veți obține o listă cu toate combinațiile posibile de persoane și străzi, dar nu veți putea ști de fapt cine pe ce stradă locuiește!
Așa ar trebui să arate rezultatul SQL corect:
name | house_no | name
--------------+----------+-------------
Joe Bloggs | 3 | Low Street
Roger Jones | 33 | High street
Sally Norman | 83 | High street
Jane Smith | 55 | Main Road
(4 rows)
Vom reveni la legături când vom creea interogări mai complexe în continuare. Pentru moment rețineți că permit o metodă simplă de a combina informații din două sau mai multe tabele.
15.4.4. Sub-Selectarea
Sub-selecțiile permit selectarea obiectelor dintr-un tabel, pe baza datelor dintr-un alt tabel de care este legat printr-o relație la cheia sa externă. În cazul nostru, dorim să găsim persoanele care locuiesc pe o anumită stradă.
În primul rând, să facem un pic de reglare a datelor noastre:
insert into streets (name) values('QGIS Road');
insert into streets (name) values('OGR Corner');
insert into streets (name) values('Goodle Square');
update people set street_id = 2 where id=2;
update people set street_id = 3 where id=3;
Haideți să aruncăm o privire rapidă la datele noastre în urma modificărilor: putem refolosi interogarea din secțiunea anterioară:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Rezultat:
name | house_no | name
--------------+----------+-------------
Roger Jones | 33 | High street
Sally Norman | 83 | High street
Jane Smith | 55 | Main Road
Joe Bloggs | 3 | Low Street
(4 rows)
Acum, vom efectua o sub-selecție asupra acestor date. Vrem să arătăm doar persoanele care locuiesc în street_id numărul 1:
select people.name
from people, (
select *
from streets
where id=1
) as streets_subset
where people.street_id = streets_subset.id;
Rezultat:
name
--------------
Roger Jones
Sally Norman
(2 rows)
Deși acesta este un exemplu foarte simplu și inutil pentru seturile de date restrânse, el ilustrează utilitatea și importanța sub-selecțiilor în cazul interogărilor efectuate asupra seturilor de date mari și complexe.
15.4.5. Agregarea Îmbinărilor
Una dintre cele mai puternice caracteristici ale unei baze de date o reprezintă capacitatea sa de a sintetiza datele din tabelele pe care le conține. Aceste sinteze sunt denumite interogări agregate. Iată un exemplu tipic, care ne spune cât de multe obiecte de tipul om sunt în tabela de personal:
select count(*) from people;
Rezultat:
count
-------
4
(1 row)
Dacă dorim un rezumat după numele străzii, putem proceda astfel:
select count(name), street_id
from people
group by street_id;
Rezultat:
count | street_id
-------+-----------
2 | 1
1 | 3
1 | 2
(3 rows)
Notă
Pentru că nu am folosit clauza ORDER BY, ordinea rezultatelor dvs. ar putea să nu se potrivească ce ceea ce este prezentat aici.
Try Yourself: ★★☆
Rezumați persoanele după numele străzii și afișați numele reale ale străzilor în loc de street_ids.
Răspuns
Iată instrucțiunea SQL corectă pe care ar trebui să o utilizați:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
Rezultat:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
Veți observa că avem nume de câmpuri prefixate cu nume de tabel (de exemplu, persoane.nume și străzi.nume). Acest lucru trebuie făcut ori de câte ori numele câmpului este ambiguu (adică nu este unic în toate tabelele din baza de date).
15.4.6. În concluzie
Ați văzut cum se utilizează interogările pentru a returna datele din baza de date într-un mod care permite extragerea de informații utile.
15.4.7. Ce urmează?
Mai departe veți vedea cum să creați vizualizări, pornind de la interogările scrise.