CODE CARROTS

SQL

 

 

edycja 1.  Warszawa 2015

Link do prezentacji

http://goo.gl/2OhxjI

Notacja z kropką

(Dot Notation)

Jak odwołać się do tabeli z innej bazy lub gdy baza nie została wybrana?

SELECT title FROM sakila.film

Jak odwołać się do kolumny konkretnej tabeli?

SELECT film.title, film.rating
FROM sakila.film

Aliasy

czyli jak ułatwić sobie życie

SELECT film.title, film.rating
FROM sakila.film
SELECT f.title, f.rating
FROM sakila.film AS f

Alias dla nazwy tabeli

SELECT
    f.title AS tytuł,
    f.rating AS kategoria_wiekowa
FROM 
    sakila.film AS f

Alias dla nazwy kolumny

Klucze

Klucz główny

Klucz obcy

(KEY)

(PRIMARY KEY)

(FOREIGN KEY)

Klucz Główny

PRIMARY KEY

SELECT country_id, country 
FROM country
ORDER BY country_id
LIMIT 10

country

Klucz Główny

PRIMARY KEY

  • jednoznacznie identyfikuje rekord (wiersz) tabeli

  • wartość klucza jest unikalna

  • może składać się z jednej lub więcej kolumn

  • najczęściej używa się kolejnych liczb naturalnych

Klucz Obcy

FOREIGN KEY

SELECT city_id, city, country_id
FROM city
WHERE country_id <= 10
ORDER BY city_id
LIMIT 10

city

Klucz Obcy

FOREIGN KEY

  • zawiera wartości klucza głównego powiązanej tabeli

  • wartości nie muszą być unikalne

  • może składać się z jednej lub więcej kolumn

Relacja jeden-do-wielu

one-to-many

city

country

Relacja jeden-do-wielu

one-to-many

city

country

Relacja jeden-do-wielu

one-to-many

Relacja jeden-do-wielu

one-to-many

  • pojedynczemu rekordowi z tabeli A (country) odpowiada jeden lub więcej rekordów z tabeli B (city)

  • pojedynczemu wierszowi z tabeli B (city) odpowiada dokładnie jeden wiersz z tabeli A (country)

Kobiety i Mężczyźni

Żony i Mężowie

Relacja jeden-do-jednego

one-to-one

country

capital

Relacja jeden-do-jednego

one-to-one

country

capital

Relacja jeden-do-jednego

one-to-one

Relacja jeden-do-jednego

one-to-one

  • pojedynczemu rekordowi z tabeli A (country) odpowiada dokładnie jeden rekord z tabeli B (capital)

  • pojedynczemu wierszowi z tabeli B (capital) odpowiada dokładnie jeden wiersz z tabeli A (country)

Relacja wiele-do-wielu

many-to-many

actor

film

Relacja wiele-do-wielu

many-to-many

actor

film

film_actor

Relacja wiele-do-wielu

many-to-many

actor

film

film_actor

Relacja wiele-do-wielu

many-to-many

Relacja wiele-do-wielu

many-to-many

  • pojedynczemu rekordowi z tabeli A (actor) odpowiada jeden lub więcej rekordów z tabeli B (film)

  • pojedynczemu wierszowi z tabeli B (film) odpowiada jeden lub więcej wierszy z tabeli A (actor)

  • potrzebna jest tabela pomocnicza łącząca tabele A i B

Klauzula JOIN

Najprostsze zapytanie do dwóch tabel

SELECT *
FROM staff, store

(CROSS JOIN)

Najprostsze zapytanie do dwóch tabel

SELECT 
    *
FROM
    staff,
    store
WHERE
    staff.store_id = store.store_id

(niejawny warunek złączenia)

Najprostsze zapytanie do dwóch tabel

SELECT 
    *
FROM
    staff AS f
JOIN
    store AS s 
    ON f.store_id = s.store_id

(jawny warunek złączenia)

(INNER JOIN)

Tylko wybrane kolumny w wyniku

SELECT 
    f.staff_id, f.first_name, f.last_name,
    s.store_id, s.manager_staff_id, s.address_id
FROM
    staff AS f
JOIN
    store s 
    ON f.store_id = s.store_id

Lista pracowników z nazwiskami managerów sklepów w których pracują

SELECT 
    e.first_name, e.last_name,
    m.first_name AS manager_first_name, 
    m.last_name AS manager_last_name
FROM
    staff AS e
JOIN
    store AS s 
    ON e.store_id = s.store_id
JOIN
    staff AS m
    ON s.manager_staff_id = m.staff_id

SELECT
    c.city,
    s.first_name, s.last_name
FROM
    city c
JOIN address a
    ON c.city_id = a.city_id
JOIN customer s
    ON s.address_id = a.address_id
WHERE
    c.city = "Aurora"
ORDER BY
    s.last_name DESC,
    s.first_name DESC

Lista klientów z miasta Aurora posortowanych malejąco wg nazwiska i imienia

Lista filmów z kategorii Drama, posortowanych malejąco wg stawki za wypożyczenie i rosnąco wg tytułu

SELECT
    f.title, f.rental_rate, c.name AS category
FROM
    film f
JOIN
    film_category fc
    ON f.film_id = fc.film_id
JOIN
    category c
    ON c.category_id = fc.category_id
WHERE
    c.name = "Drama"
ORDER BY
    f.rental_rate DESC,
    f.title ASC

Klauzula LEFT JOIN

Historia wypożyczeń wszystkich kopii filmu "Academy Dinosaur"

SELECT 
    f.title, i.inventory_id, r.*
FROM 
    inventory i
JOIN 
    film f 
    ON i.film_id = f.film_id
LEFT JOIN 
    rental r 
    ON i.inventory_id = r.inventory_id
WHERE 
    f.title = "Academy Dinosaur"

Lista wszystkich kopii filmów ze stawką 4.99, z historią ich wypożyczeń przez pracownika o ID 2

SELECT 
    i.inventory_id, f.title, i.store_id, r.*
FROM 
    inventory i
JOIN 
    film f 
    ON i.film_id = f.film_id
LEFT JOIN 
    rental r
    ON i.inventory_id = r.inventory_id 
        AND r.staff_id = 2
WHERE
    f.rental_rate = 4.99;

Lista wszystkich kopii filmów ze stawką 4.99, które nigdy nie zostały wypożyczone przez pracownika o ID 2

SELECT 
    i.inventory_id, f.title, i.store_id, r.*
FROM 
    inventory i
JOIN 
    film f 
    ON i.film_id = f.film_id
LEFT JOIN 
    rental r
    ON i.inventory_id = r.inventory_id 
        AND r.staff_id = 2
WHERE
    f.rental_rate = 4.99
    AND r.rental_id IS NULL;

NULL

SELECT 1 = 1, 1 = 0, "a" = "a", "a" = "b", "" = "";

SELECT 1 = NULL, 0 = NULL, "a" = NULL, "" = NULL, NULL = NULL;

SELECT NULL IS NULL;

SELECT 1 + NULL, 1 - NULL, 1 * NULL, 1 / NULL;

NULL to brak wartości

  • NULL to nie zero
  • NULL to nie pusty string
  • operacje arytmetyczne z NULLem dają w wyniku NULL

BETWEEN

Lista wszystkich wypożyczeń pomiędzy 24 a 31 lipca 2005, które zwrócono pomiędzy 7 a 14 sierpnia, posortowana malejąco wg dat

SELECT *
FROM rental
WHERE
  rental_date BETWEEN "2005-07-24" AND "2005-07-31 23:59:59"
  AND return_date BETWEEN "2005-08-07" AND "2005-08-14 23:59:59"
ORDER BY
  rental_date DESC,
  return_date DESC;

x BETWEEN min AND max

  • jest równoważne: x >= min AND x <= max
  • x, min i max powinny być tych samych typów

LIKE

wyrażenie LIKE "wzorzec"

  • % - zastępuje dowolną ilość znaków
  • _ - zastępuje dokładnie jeden znak

(operator porównania ze wzorcem)

Szukamy filmów

- zaczynają się na literę "L"

- kończą się na literę "T"

- druga litera tytułu to "O"

- opis zawiera słowo "mad"

SELECT *
FROM film
WHERE title LIKE "L%"
  AND title LIKE "%T"
  AND title LIKE "_O%"
  AND description LIKE "%MAD%";

IN()

wyrażenie IN (wartość1, wartość2, ...)

jest równoważne:
wyrażenie = wartość1
OR wyrażenie = wartość2
...

Szukamy filmów

- długość to 90 lub 120

- czas wypożyczenia to nie 4 i nie 6

- bonusy nie zawierają usuniętych scen

SELECT *
FROM film
  WHERE length IN (90, 120)
  AND rental_duration NOT IN (4, 6)
  AND special_features NOT LIKE "%deleted scenes%";

Ćwiczenia

Lista klientów, którzy nie wypożyczali żadnego filmu w czerwcu 2005, i których nazwisko zaczyna się na literę "C"

Lista filmów (tytuł, nr kopii),
które nie były wypożyczane pomiędzy 10 a 20

kategoria wiekowa to "PG-13" lub "NC-17"

opis filmu nie zawiera słowa "story"

koszt zastąpienia pomiędzy 26 a 29

stawka za wypożyczenie nie jest pomiędzy 1 i 4

Q & A

Made with Slides.com