mysql-01-select.md

Ako SQL spustat z PHP:

• http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers
• https://phpdelusions.net/pdo
• http://php.net/manual/en/book.pdo.php

---

SELECT

ked vytahujeme data z databazy

SELECT * FROM movies

SELECT * FROM movies
WHERE director_id = 4

SELECT * FROM movies
WHERE director_id = 2
AND year >= 2000 AND year <= 2010

SELECT * FROM movies
WHERE director_id IN (2, 3)
AND year BETWEEN 2000 AND 2010 AND year != 2004

SELECT first_name, last_name, YEAR(birthdate)
FROM directors

SELECT first_name, last_name
FROM directors
WHERE MONTH(birthdate) = 1
AND DAY(birthdate) <= 15

SELECT NOW()

SELECT first_name, last_name, country, YEAR(NOW()) - YEAR(birthdate) AS age
FROM directors

SELECT first_name, last_name, country,
    YEAR(NOW()) - YEAR(birthdate) AS age,
    CONCAT(first_name, ' ', last_name) AS name
FROM directors
ORDER BY age ASC

(DESC)

SELECT * FROM movies
LIMIT 5

SELECT * FROM movies
LIMIT 5 OFFSET 10

SELECT MAX(gross) FROM movies

SELECT FORMAT(MAX(gross), 2) FROM movies

SELECT CONCAT('$', FORMAT(MAX(gross), 0)) AS gross FROM movies

SELECT
    CONCAT('$', FORMAT( MAX(gross), 0) ) AS max,
    CONCAT('$', FORMAT( MIN(gross), 0) ) AS min,
    CONCAT('$', FORMAT( AVG(gross), 0) ) AS avg
FROM movies
WHERE director_id = 3

SELECT 2

ked napriklad vyhladavame v databaze

SELECT * FROM directors
WHERE country = 'United States'

SELECT * FROM directors
WHERE country LIKE 'United States'

SELECT * FROM directors
WHERE country LIKE 'United%'

SELECT * FROM directors
WHERE first_name LIKE 'A%' OR first_name LIKE 'J%'

SELECT * FROM movies
WHERE title LIKE '%Avengers'

SELECT * FROM movies
WHERE title LIKE '%World%'

mysql-02-insert.md

INSERT

ked vkladame data do databazy

INSERT INTO directors (first_name, last_name, country, birthdate)
VALUES ('Paul Thomas', 'Anderson', 'United States', '1970-07-26')

INSERT INTO directors
VALUES ('Ingmar', 'Bergman', 'Sweden', '1918-07-14')

alebo

INSERT INTO directors
VALUES (NULL, 'Ingmar', 'Bergman', 'Sweden', '1918-07-14')

alebo

INSERT INTO directors
VALUES (DEFAULT, 'Andrei', 'Tarkovsky', 'Russia', '1932-04-04')

INSERT INTO directors (first_name, last_name, birthdate)
VALUES ('Krzysztof', 'Kieslowski', '1941-06-27')

INSERT INTO directors (first_name, last_name, birthdate)
VALUES ('Abbas', 'Kiarostami', '1940-06-22')

INSERT INTO movies
    (director_id, title, year, gross)
VALUES
    (9, 'The Wind Will Carry Us', 2000, 259510),
    (9, 'Certified Copy', 2011, 1373975),
    (9, 'Like Someone in Love', 2013, 239056)

SELECT 3

ked nam vzniknu diery v databaze a chceme vyberat prazdne alebo neprazdne riadky

SELECT * FROM directors
WHERE country IS NOT NULL

SELECT * FROM directors
WHERE country IS NOT NULL AND country != ''

SELECT * FROM directors
WHERE country IS NULL OR country = ''

SELECT * FROM directors
WHERE NULLIF (country, '') IS NULL

mysql-03-join.md

FUNKCIE, JOIN a GROUP BY

ked potrebujeme robit statistuku / cez agregacne funkcie

SELECT title FROM movies
SELECT 'balls' FROM movies
SELECT 1 FROM movies

SELECT COUNT(1) FROM movies

SELECT
    first_name, last_name
    CONCAT('$', FORMAT(MAX(gross), 0)) AS max,
    CONCAT('$', FORMAT(MIN(gross), 0)) AS min
FROM movies
WHERE director_id = 4

JOIN

ked potrebujeme prepojit tabulky medzi sebou

SELECT
    first_name, last_name
    CONCAT('$', FORMAT(MAX(gross), 0)) AS max,
    CONCAT('$', FORMAT(MIN(gross), 0)) AS min
FROM movies
JOIN
    directors ON directors.id = director_id
WHERE
    director_id = 4

SELECT CONCAT(first_name, ' ', last_name) AS name, title, year
FROM directors
JOIN movies ON directors.id = movies.director_id
WHERE last_name LIKE 'W%'

SELECT CONCAT(first_name, ' ', last_name) AS name, title, year
FROM directors
JOIN movies ON directors.id = movies.director_id AND last_name LIKE 'W%'

...
LEFT JOIN movies ON directors.id = movies.director_id
...
RIGHT JOIN movies ON directors.id = movies.director_id

SELECT title, year, gross, genre, first_name, last_name, country 
FROM movies
JOIN directors ON directors.id = movies.director_id
WHERE CONCAT(first_name, ' ', last_name) = 'Jim Jarmusch'

mysql-04-update-delete-group.md

UPDATE

ked upravujeme riadok v databaze

UPDATE directors SET country = 'Poland'
WHERE id = 8
LIMIT 1

UPDATE directors SET
    first_name = 'Andrej',
    last_name  = 'Tarkovskij',
    country    = 'Russia'
WHERE id = 7

UPDATE movies SET gross = gross + 0.50
WHERE id > 10

DELETE

ked mazeme z databazy

DELETE FROM movies
WHERE id = 12
LIMIT 1

GROUP BY

ked potrebujeme robit statistiky na konkretnom stlpci

SELECT DISTINCT genre FROM movies

SELECT * FROM movies
GROUP BY genre

SELECT genre FROM movies
GROUP BY genre

SELECT genre, SUM(gross) FROM movies
GROUP BY genre

SELECT genre, FORMAT(SUM(gross),0) FROM movies
GROUP BY genre