Baza danych:
Podczas tych i kolejnych zajęć korzystać będziemy z przykładowej bazy danych "Sakila" dostępnej na serwerze uczelnianym torus.uck.pk.edu.pl. Adres, nazwy urzytkowników, hasła oraz sposób połącznia z wykorzystaniem MySQL Workbench tak jak poprzednio z tą różnicą że łączymy się z bazą o nazwie sakila_DBii gdzie ii to numer użytkownika. Jakiekolwiek problemy z logowaniem proszę zgłaszać na zajęciach lub poprzez kontakt mailowy.
# kod pythona do połączenia z bazą i wykonywania zapytań sql w jupyter
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:*****@127.0.0.1:3306/sakila')
%load_ext sql_magic
%config SQL.conn_name = 'engine'
Są to funkcje umożliwiające wykonanie pożądanych operacji na całych tabelach lub tylko niektórych zbiorach danych, zwracające ogólne wyniki dotyczące np. liczebności czy średnich wartości.
%%read_sql
SELECT COUNT(*) FROM film
Liczba niepustych wartości w kolumnie title
%%read_sql
SELECT COUNT(title) FROM film
Liczba unikalnych wartości w kolumnie rating
%%read_sql
SELECT COUNT( DISTINCT rating) FROM film
Funkcje zwracające odpowiednio wartość średnią, maksimum, minimum, sumę oraz odchylenie standardowe z podanej kolumny.
Przykład:
Wartość średnia, maksimum, minimum, suma oraz odchylenie standardowe długości filmów w tabeli film
%%read_sql
SELECT AVG(length), MAX(length), MIN(length), SUM(length), STDDEV(length) FROM film
Oczywiście możliwe jest dołożenie do zapytania warunku klauzuli WHERE
%%read_sql
SELECT AVG(length), MAX(length), MIN(length), SUM(length), STDDEV(length) FROM film
WHERE rating = 'G'
Jak widać na powyższych przykładach generowane automatycznie nazwy kolumn nie zawsze są przyjemne. Dlatego możemy nadać im własne nazwy za pomocą słowa kluczowego AS.
Przykład:
Wartość średnia, maksimum, minimum, suma oraz odchylenie standardowe długości filmów w tabeli film. Kolumny nazwane odpowiednio srednia, maksimum, minimum, suma, std.
%%read_sql
SELECT AVG(length) AS srednia, MAX(length) AS maksimum, MIN(length) AS minimum, SUM(length) AS suma, STDDEV(length) AS std
FROM film
W SQL'u możliwe jest też wykonywania działań arytmetycznych na kolumnach takich jak +, -, *, /.
Przykłady:
Długość filmów w godzinach
%%read_sql
SELECT length AS dl_minuty, (length/60.0) AS dl_godziny FROM film
LIMIT 6
Przykładowa operacja na dwóch kolumnach
%%read_sql
SELECT length, length*rental_rate AS 'len*ren' FROM film
LIMIT 6
Sortownie wyniku zapytania w mysql odbywa się poprzez użycie klauzuli ORDER BY. Sortować możemy rosnąco ASC lub malejąco DESC. Domyślnie sortujemy rosnąco.
Przykłady:
Tytułu filmów w porządku 'Z-A'. Pierwsze 10 rekordów.
%%read_sql
SELECT title FROM film
ORDER BY title DESC
LIMIT 10
Sortowanie po kilku kolumnach, np. według rental_duration rosnąco, następnie według długości malejąco. Pierwsze 10 rekordów.
%%read_sql
SELECT title, rental_duration, length FROM film
ORDER BY rental_duration, length DESC
LIMIT 10
Kluzula GROUP BY umożliwia wykonywanie funkcji grupujących na grupie wartości wyodrębnionych na podstawie wartości innej kolumny. Aby użyć kolumny do grupowania, musimy pamiętać, aby umieścić ją w liście kolumn, zaraz po słowie SELECT. W przeciwnym razie nie będzie ona dostępna dla klauzuli grupującej GROUP BY.
Przykłady:
Średnia długość filmów o tym samym ratingu.
%%read_sql
SELECT rating, AVG(length) AS srednia FROM film
GROUP BY rating
Grupować można również po kilku kolumnach, np. najpierw po rental_duration a później każdą z tych grup po rating. Możemy również takie grupy sortować.
%%read_sql
SELECT rental_duration, rating, AVG(length) AS srednia FROM film
GROUP BY rental_duration, rating
ORDER BY rental_duration DESC
Klauzula HAVING, znajdująca się zaraz po GROUP BY, służy do określania dodatkowych kryteriów przy grupowaniu (jest to odpowiednik warunku WHERE w zapytaniach bez funkcji grupujących).
Przykład:
Średnia długość filmów o tym samym rental_duration, ale tylko tych gdzie średnia długość jest większa od 115 minut
%%read_sql
SELECT rental_duration, AVG(length) AS srednia FROM film
GROUP BY rental_duration
HAVING AVG(length) > 115
Tworzenie kolumn, które przyjmują wartości w zależności od spełnionych warunków.
Przykład:
Chcemy podzielić filmy na trzy kategorie, krótki, średni i długi. W tym celu najpierw stworzymy nową kolumnę w której będą własnie takie wartości w zależności od długości filmu.
%%read_sql
SELECT length,
CASE WHEN length > 120
THEN 'dlugi'
WHEN length > 60
THEN 'sredni'
ELSE 'krotki' END
AS etykieta
FROM film
LIMIT 10
Teraz możemy rozszeżyć zapytanie o grupowanie danych względem utworzonej etykiety i policzyć np. ilość filmów w danej grupie
%%read_sql
SELECT COUNT(*) AS ilosc,
CASE WHEN length > 120
THEN 'dlugi'
WHEN length > 60
THEN 'sredni'
ELSE 'krotki' END
AS etykieta
FROM film
GROUP BY etykieta
ORDER BY ilosc