Skip to content

Instantly share code, notes, and snippets.

@yablko yablko/mysql-01-select.md
Last active Dec 12, 2016

Embed
What would you like to do?

Ako SQL spustat z 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%'

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

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'

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.