PHP - kolejne wskazówki i rozwiązania w PHP i MySQL

Kod:

brak kodu;

Ewentualne Objaśnienia:

Tutaj chciałem w tej jednej lekcji omówić kilka rzeczy związanych z zapytaniami, funkcjami i innymi rozwiązaniami stosowanymi w zapytaniach do bazy MySQL ( i nie tylko ). Czyli kolejna porcja takich ogólnych przydatnych rzeczy :

1). Zapewne kojarzycie bardzo przydatny operator LIKE klauzuli WHERE, jest ich jednak więcej i teraz je przedstawię :
- znaki równości, mniejszości itd czyli : =,>,<,>=,<=, !=, <>
- IS NOT NULL - sprawdza czy dane pole ma nadaną wartość
- IS NULL - sprawdza czy jest puste
- BETWEEN - na przykład wiek BTWEEN 18 AND 60 - wiadomo
- IN - czy wartość należy do określonego zbioru wartości na przykład : Nazwisko IN('Nowak','Kowalski')
- NOT IN - wiadomo
- LIKE - to już znacie
- NOT LIKE - wiadomo
- REGEXP - sprawdza czy wartość pasuje do danego wyrażenia regularnego, na przykład : Imie REGEXP '^Ma.*'

2). Pamiętacie złączenia ? Czyli sytuację kiedy na przykład chcemy wyszukać z 2 tabel, przykładowy zapis dla przypomnienia :
SELECT tresc.tytul, tresc.id FROM tresc, autorzy WHERE autorzy.nazwisko='Kowalski' AND autorzy.autorid = tresc.id

Jak pamiętacie separator kropki nie zawsze musi być używany, ale warto to robić bo :
- w tabelach mogą być pola o takich samych nazwach ( na przykład id i id )
- lepiej się w tym wszystkim połapać :)

3). Nie wspominałem wcześniej o tzw. left join . Jak wiecie bazy danych można sobie projektować bardzo rożnie, na przykład dla jakiegoś katalogu stron można zrobić dwie tabele jedna z linkami druga z kategoriami. Jak je teraz połączyć ? No choćby w tabeli z linkami dodać pole idkategorii. Jednak to nie jest dobre rozwiązanie - o wiele lepiej zrobić jeszcze jedną tabelę ( korzystamy ze złączeń tutaj tzw. wielu-do-wielu )o nazwie na przykład kategoria_link w której będziemy przechowywać pary kluczy, czyli id danego linka oraz id kategorii dla tego linka, to o wiele lepsze co nie ?
Jednak może być tak, że będziemy chcieli sobie wyświetlić na przykład linki, które nie są dopisane do żadnej kategorii itp - ogólnie mówiąc wyświetlenie danych ze sobą niezwiązanych. No i tutaj z pomocą przychodzi nam lef join - spowoduje wyszukanie wierszy spełniających zadany warunek połączenia między dwiema tabelami, ale jeżeli w drugiej nie ma odpowiadającego wiersza, do tabeli wynikowej zostanie dodany wiersz z zawartością NULL w prawej kolumnie.
Przykładowe zapytanie ( zaczerpnięte z Vademecum Profesjonalisty, wydanie III wyd.Helion ) :
SELECT klienci.klientid, klienci.nazwisko, zamowienia.zamowienieid FROM klienci lef join zamowienia on klienci.klientid=zamowienia.klientid

Pobieramy idklienta, nazwisko oraz numer (id) zamówienia z tabeli klienci poprzez LEFT JOIN z tabeli zamowienia ( specjalna klauzula ON ) naszym warunkiem jest id klienta równe id zamówienia. Łatwo wyobrazić sobie takie dwie tabele - klienci zawiera klientid, ale też zamowienia zawiera takie pole - dzięki left join jeżeli w tabeli zamowienia nie będzie id jakiegoś klienta - czyli jeszcze nie złożył zamówienia wyświetli się dla niego wartośc NULL dla pola zamowienieid, czyli tabelka typu :

Jak widać Pan Brożek zrealizował już 2 zamówienia, a Nowak i Kowalski żadnego.
Jeżeli chcielibyśmy uzyskać jedynie tych naszych obiboków, którzy nic nie zamawiają - czyli dla nich pole zamowienieid jest NULL można napisać :
SELECET klienci.klientid, klienci.nazwisko FROM klienci LEFT JOIN zamowienia using(klientid) WHERE zamowienia.zamowienieid is null; tutaj nie użyliśmy klauzuli ON jak wcześniej lecz USING - jednak pamiętajcie, że w przypadku using nie można podać nazwy tabeli, z której ma pochodzić pole łączące, pola te muszą mieć takie same nazwy w obu tabelach - bo jak wspominałem i w tabeli klienci i zamowienia jest pole o nazwie klientid - więc tutaj możemy śmiało użyć using niż pisać specjalnie, że klienci.klientid = zamowienia.klientid .
 

4). Aliasy - przy zapytaniach można użyć tzw. aliasów - odwołujemy się do tabel za pomocą innych nazw na przykład prosty przykładzik :
SELECT a.nazwisko FROM autorzy as a, tresci as t WHERE a.id=t.numer; poprzez słowo AS nadajemy odpowiedni aliasy dla tabel ( tutaj autorzy oraz tresci )

5). Bardzo przydatne są funkcje, słowa służące do grupowania i agregowania danych. Na przykład mamy jakąś tabele zawodników gdzie znajduje się kilkanaście rekordów z ich punktami, chcemy obliczyć z całej tabeli średnią punktów. Piszemy więc zapytanie :
SELECT avg(punkty) FROM zawodnicy; - słowo kluczowe AVG oblicza wartość średnią we wskazanej kolumnie, czyli tutaj w kolumnie punkty, w tabeli zawodnicy.

Ale na przykład może być tak, że w naszej tabeli jest bardzo dużo zawodników i każdy ma jeszcze przypisany iddruzyny - czyli jak się domyślacie id drużyny w jakiej ten zawodnik gra. Chcielibyśmy więc dla każdej drużyny ( czyli każdego z tych id ) obliczyć tą naszą wartość średnią więc piszemy :
SELECT iddruzyny, avg(punkty) FROM zawodnicy GROUP BY iddruzyny; - w ten sposób otrzymamy dla każdego id z tabeli obliczoną średnią wartość punktów, będziemy mogli swobodnie wyświetlić id oraz średnią

Trzeba pamiętać o pewnej rzeczy - standard ANSI SQL określa iż w klauzuli SELECT, która wykorzystuje agregację danych lub grupowanie - mogą znajdować się jedynie funkcje agregującą oraz nazwy kolumn, które będą podstawą grupowania ( nazwy te muszą być również wyszczególnione w klauzuli SELECT - tak jak w powyższym przykładzie ).
Jednak MySQL ma troszkę swobodniejsze możliwości - obsługuje tzw. składnię rozszerzoną - pozwala na ominięcie nazw kolumn w klauzuli SELECT, jeżeli nie ma potrzeby ich wyświetlania.

Istotną sprawą jest klauzula HAVING pozwalająca na określanie dodatkowych kryteriów zapytania. Powinna ona znaleźć się zaraz za klauzulą group by; działa ona tak samo jak WHERE w zapytaniach gdzie nie ma agregacji czy grupowania. Tak więc przykładowo robimy grupowanie drużyn dla nich średnie wartości, ale jedynie kiedy średnia wartość punktów jest większa od 15 :
SELECT idruzyny, avg(punkty) FROM zawodnicy GROUP BY iddruzyny HAVING avg(punkty)>15; - pamiętajcie, że having odnosi się tylko do grup danych.

Funkcje agregujące w MySQL :
- avg - wiadomo
- count - zwraca liczbę niezerowych wartości w danej kolumnie, jeżeli nazwę kolumny poprzedzimy słowem distinct to funkcja zwróci nam wartość jedynie występujących odrębnych wartości; jeżeli zamiast nazwy kolumny wpiszemy * zwrócona zostanie liczba wierszy w tabeli
- min - wiadomo
- max - wiadomo
- std - oblicza odchylenie standardowe wartości w kolumnie
- stddev - jak wyżej
- sum - suma wartości w kolumnie

6). Ciekawą sprawą są tzw. pod-zapytania czyli zagnieżdżanie jednego zapytania w drugim - jest to często bardzo przydatne, choć takie same rozwiązania można uzyskać poprzez odpowiednie zapytanie korzystające ze złączeń, jednak skorzystanie z pod-zapytania jest o wiele wygodniejsze i czytelne.
Przykładowo chcemy wyświetlić z naszej tabeli zawodników tych, którzy uzbierali największą ilość punktów. Oczywiście możemy użyć grupowania malejącego oraz na przykład limit ilości zwróconych danych, ale możemy też zapytać :
SELECT idzawodnika, punkty FROM zawodnicy WHERE punkty=(select max(punkty) from zawodnicy); - Jak widać bardzo prosta sprawa, pobieramy punkty oraz id danego zawodnika, dla którego punkty są równe max wartości z kolumny punkty - co też obliczamy zagnieżdżonym zapytaniem .

7). Kolejna sprawa to tzw. operatory pod-zapytań  - 4 z nich można użyć również w zapytaniach zwykłych, zobaczmy jakie to operatory :
- ANY - zwraca wartość TRUE jeżeli porównanie zwróci true dla dowolnego wiersza z pod-zapytania przykładowo :
SELECT p1 FROM t1 WHERE p1 > ANY (SELECT p1 FROM t2); - czyli zwraca wartość jeżeli jakiekolwiek pole p1 z tabeli t1 będzie większe od pola p1 z tabeli t2
- IN - jest to pewien równoważnik odpowiadający zapisowi " =ANY "
- SOME - to po prostu alias zapytania ANY
- ALL zwraca true, jeżeli porównanie będzie miało wartość true dla wszystkich wierszy z podzapytania

8). Mamy także tzw. podzapytania skorelowane - tutaj element z zapytania zewnętrznego może zostać użyty w zapytaniu zewnętrznym, przykładowo :
SELECT idzawodnika, nazwisko, punkty FROM zawodnicy WHERE not exists (SELECT * from osiagniecia WHERE osiagniecia.idzawodnika=zawodnicy.idzawodnika); Exists zwraca true jeżeli w podzapytaniu znajduje się co najmniej 1 wiersz pasujący ( do zapytania zewnętrznego). NOT Exists - odwrotnie rzecz jasna. Tutaj mamy działanie takie jakie uzyskiwaliśmy lewym złączeniem - chcemy pobrać tych zawodników, którzy nie mają na koncie żadnego osiągnięcia - czyli zapytanie (wynik) podzapytanie nie pasuje do wyniku z zapytania zewnętrznego

9). Przydatna rzecz to podzapytania wierszowe -  na przykład może być tak, że chcemy porównać dwa wiersze z dwóch tabel przykładowo :
SELECT pol1, pol2, pol3 FROM t1 WHERE (pol1,pol2,pol3) IN (SELECT pol1, pol2, pol3 FROM t2); - czyli jak widać zwracane są wiersze, które są identyczne w tabeli t1 i t2, IN przypominam oznacza =ANY - czylu jakikolwiek wiersz (pol1,pol2,pol3) tabeli t1 jest równy takiemu wierszowi w t2

10). Kolejna możliwość to tzw. tabela tymczasowa - możliwość wykonania zapytania na wynikach innego zapytania (pod-zapytania), które jest wtedy traktowane jako tabela tymczasowa ( jego wyniki ) przykładowo :
SELECE * FROM (select idzawodnika, nazwisko, imie from zawodnicy where wojewodzctwo='slaskie') as zawodnicy_slaskie; - jak widać podzapytanie zostało umieszczone w klauzuli FROM. Zaraz za nawiasem zamykającym podzapytanie należy podać jego wynikom alias. Ten przykład jest akurat bardzo prosty, można było taki efekt uzyskać jednym zapytaniem, jednak do zobrazowania idei taki przykład myślę jest odpowiedni.