cw9.pdf

- 1 -

Język baz danych - SQL

1. Przypomnienie

Na początek musimy się połączyć z bazą world .

W:\>mysql -h elmo -u labor -p

Enter password: *****

mysql>use world;

Do pobierania danych z tabel służy polecenie SELECT.

SELECT <lista_pól> FROM <nazwa_bazy>.<nazwa_tabeli>;

SELECT <lista_pól> FROM <nazwa_tabeli>;

WHERE – określamy, które rekordy chcemy wyświetlić np.:

SELECT * FROM City WHERE CountryCode=”POL”

ORDER BY – sortujemy wyniki (ASC i DESC):

SELECT * FROM City WHERE id BETWEEN 1 AND 10 ORDER BY Name ASC;

LIMIT - pozwala ograniczyć liczbę wierszy wyniku zapytania:

SELECT * FROM City ORDER BY Population DESC LIMIT 3;

AND, OR i NOT – operatory logiczne:

SELECT * FROM City WHERE CountryCode=”POL” AND District=”Malopolskie”;

SELECT * FROM City WHERE District=”Malopolskie” OR District=”Slaskie”;

SELECT * FROM City WHERE CountryCode=”POL” AND District NOT IN(”Malopolskie”,

”Slaskie”);

Ćwiczenie 1. Wykonaj poniższe kwerendy

 Wypisz miasta hiszpańskie.

 Znajdź 5 najludniejszych miast świata.

 Wypisz kraje, które uzyskały niepodległość po 1989 roku?

 Znajź 5 największych obszarowo krajów Europy.

2. Funkcje grupujące

Funkcje grupujące to funkcje, które zwracają jedną wartość obliczoną ze zbioru

danych wskazanych parametrem.

1) Funkcja AVG()

Za pomocą funkcji AVG() obliczamy wartość średnią dla podanych wyrażeń.

Argumentami funkcji AVG() muszą być dane liczbowe.

Przykład:

Jaka jest średnia długość życia?

SELECT AVG(LifeExpectancy) FROM Country;

Ćwiczenie 2. Oblicz jaka jest średnia długość życia w Europie, a jaka w Afryce? (2

zapytania).

- 2 -

2) Funkcja COUNT()

Funkcja zlicza w przekazanym zbiorze wartości wystąpienia różne od NULL, chyba

że jako argumentu użyto znaku * (gwiazdka).

Przykład:

Ile jest państw w Afryce.

SELECT Count(Name) From Country Where Continent="Africa";

Ćwiczenie 3. W ilu krajach językiem oficjalnym mówi > 95% populacji?

Domyślnie funkcje grupujące nie eliminują powtarzających się wierszy. Jeżeli

chcemy ograniczyć wyniki do unikatowych wartości wierszy, należy użyć

kwalifikatora DISTINCT .

Przykład:

Ilu języków używa się na świecie?

SELECT Count(DISTINCT(Language)) From CountryLanguage;

3) Funkcje Min() I Max()

Funkcje te służą do znajdowania najmniejszej i największej wartości w zbiorze

rekordów.

Przykład:

Znajdź państwo o największym Produkcie Krajowym Brutto (Gross National

Product).

SELECT MAX(GNP) FROM Country;

Ćwiczenie 4. Znajdź jaki jest największy oraz najmniejszy obszar państwa

w bazie.

+------------------+

| Max(SurfaceArea) |

+------------------+

| 17075400.00 |

+------------------+

Funkcje te działają nie tylko na wartościach liczbowych. Spóbuj działanie tych

funkcji typie znakowym (np. pole Name).

4) Funkcja SUM()

Za pomocą funkcji SUM() sumowane są wszystkie wartości rekordów wybranych w

zapytaniu i zwracany jest wynik. Argumentami funkcji SUM() mogą być wyłącznie

liczby.

Przykład:

Ilu mieszkańców zamieszkuje miasta Małopolski

SELECT Sum(Population) FROM City WHERE District="Malopolskie";

Ćwiczenie 5. Policz jaką powierzchnię zajmują państwa Europy.

- 3 -

5) Funkcja STDDEV_POP()

Funkcja ta oblicza odchylenie standardowe zbioru danych, np. aby obliczyć

odchylenie standardowe średniej długości życia należy wpisać.

SELECT STDDEV_POP(LifeExpectancy) FROM Country;

Ćwiczenie 6. Oblicz odchylenie standardowe średniej długości życia w Europie.

+----------------------------+

| STDDEV_POP(LifeExpectancy) |

+----------------------------+

| 4.43363 |

+----------------------------+

6) Funkcja GROUP_CONCAT()

Funkcja ta łączy wszystkie argumenty w jeden ciąg znaków.

Przykład:

Wyświetl w jednym polu wszystkie języki używane w Polsce.

SELECT GROUP_CONCAT(Language) FROM CountryLanguage

WHERE CountryCode="POL";

3. Grupowanie danych

Klauzula GROUP BY umożliwia grupowanie wyników względem wybranych kolumn.

W ten sposób możemy pogrupować dane w tabeli wg danych pól.

Przykład:

Sprawdź jakie są kontynenty w bazie.

SELECT Continent FROM Country GROUP BY Continent;

Polecenie to zazwyczaj stosuje się w połączeniu z funkcjami grupującymi, które po

grupowaniu obliczają wartości dla poszczególnych grup.

Przykład:

W poprzednim ćwiczeniu oblicz średnią powierzchnię kraju na każdym kontynencie;

SELECT Continent, AVG(SurfaceArea) AS Srednia_powierzchnia

FROM Country GROUP BY Continent;

+---------------+----------------------+

| Continent | Srednia_powierzchnia |

+---------------+----------------------+

| Asia | 389822.750000 |

| Europe | 688371.706452 |

| North America | 728524.875000 |

| Africa | 491379.468750 |

| Oceania | 44571.055556 |

| Antarctica | 2626420.200000 |

| South America | 505383.166667 |

+---------------+----------------------+

Polecenie AS powoduje, że kolumna nazywa się Srednia_powierzchnia.

- 4 -

Ćwiczenie 7. Oblicz jaka jest suma mieszkańców miast polskich z podziałem na

województwa.

+---------------------+-----------------+

| District | SUM(Population) |

+---------------------+-----------------+

| Dolnoslaskie | 976924 |

| Kujawsko-Pomorskie | 818820 |

| Lodzkie | 800110 |

| Warminsko-Mazurskie | 300686 |

| Wielkopolskie | 683540 |

| Zachodnio-Pomorskie | 529363 |

+---------------------+-----------------+

Ćwiczenie 8. Policz ile jest państw w poszczególnych kontynentach.

4. Klauzula HAVING

Klauzula ta służy do wybierania grup spełniających pewne warunki. Jest to

polecenie analogiczne do WHERE z tym, że dotyczy grup a nie pojedynczych

rekordów.

Przykład:

Zmodyfikujmy przykład z pkt. 6). Mieliśmy tam obliczyć średnią powierzchnię kraju

na każdym kontynencie – dodajmy warunek, aby były wyświetlone tylko te

kontynenty (grupy) dla których średnia powierzchnia krajów jest większ niż 500tys.

SELECT Continent, AVG(SurfaceArea) AS Srednia_powierzchnia

FROM Country GROUP BY Continent HAVING Srednia_powierzchnia>500000;

W efekcie otrzymaliśmy listę kontynentów spełniających ten warunek.

+---------------+----------------------+

| Continent | Srednia_powierzchnia |

+---------------+----------------------+

| Europe | 688371.706452 |

| North America | 728524.875000 |

| Antarctica | 2626420.200000 |

| South America | 505383.166667 |

+---------------+----------------------+

5. DODATEK: Nazwy pól w tabelach bazy Word.

City (`ID`, `Name`, `CountryCode`, `District`, `Population`)

Country (`Code`, `Name`, `Continent`, `Region`, `SurfaceArea`, `IndepYear`,

`Population`, `LifeExpectancy`, `GNP`, `GNPOld`, `LocalName`,

`GovernmentForm`, `HeadOfState`, `Capital`, `Code2`)

CountryLanguage (`CountryCode, `Language`, `IsOfficial`, `Percentage`)

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: