Ściąga - SQL

0. Grupy poleceń

DDL (definicja struktury): CREATE, ALTER, DROP, TRUNCATE
DML (modyfikacja danych): INSERT, UPDATE, DELETE
DQL (zapytania): SELECT
DCL (uprawnienia): GRANT, REVOKE
TCL (transakcje): COMMIT, ROLLBACK

Dane w MySQL trzyma się w bazach (katalogi w podkatalogu data). Domyślny silnik to InnoDB (obsługuje klucze obce i transakcje).

1. SELECT, kolejność klauzul

SELECT   kolumny
FROM     tabela
WHERE    warunek_wierszy
GROUP BY kolumny
HAVING   warunek_grup
ORDER BY kolumny [ASC|DESC]
LIMIT    [offset,] ile;

WHERE filtruje wiersze (przed grupowaniem), HAVING filtruje grupy (po GROUP BY).
Alias kolumny nie działa w WHERE, działa w SELECT, HAVING, ORDER BY.
Stałe wyrażenie jest poprawne: SELECT 123+456 FROM tab; zwróci 579 dla każdego wiersza tabeli.

2. WHERE, operatory

WHERE salary > 1500
WHERE salary BETWEEN 1000 AND 2000
WHERE name IN ('sales', 'finance')
WHERE id NOT IN (1, 10, 20)
-- % dowolny ciąg, _ jeden znak
WHERE credit_rating LIKE 'EXCEL%'
WHERE country IS NULL
WHERE country IS NOT NULL
-- AND wiąże mocniej niż OR
WHERE a = 1 AND b = 2 OR c = 3

LIKE domyślnie nie rozróżnia wielkości liter. Aby rozróżniać, dodaj BINARY: LIKE BINARY 'ExCeLLeNT'.
Daty zawsze w formacie RRRR-MM-DD ('1992-01-01'). Inny format może dać błędny wynik bez ostrzeżenia.

3. ORDER BY, LIMIT, DISTINCT

SELECT * FROM emp ORDER BY salary DESC, last_name ASC;
-- pierwsze 10
SELECT * FROM emp LIMIT 10;
-- pomiń 20, weź 10
SELECT * FROM emp LIMIT 20, 10;
-- usuwa duplikaty (UNIQUE to synonim)
SELECT DISTINCT dept_id FROM emp;

4. Aliasy

SELECT first_name AS Imie,
       last_name  AS "Nazwisko",
       start_date AS "Data zatrudnienia"
FROM emp E;

AS jest opcjonalne. Warto je pisać, żeby zgubiony przecinek nie utworzył przypadkowego aliasu.
Cudzysłów potrzebny tylko gdy alias ma spację. Alias tabeli (emp E) to identyfikator, bez cudzysłowu.

5. NULL

-- nie używaj = NULL
WHERE country IS NULL
SELECT IFNULL(state, '-'), IFNULL(country, '?') FROM emp;

NULL to wartość nieznana. Porównania z NULL dają NULL (nie TRUE), dlatego IS NULL zamiast = NULL.

6. Funkcje agregujące, GROUP BY, HAVING

SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary), COUNT(salary) FROM emp;

SELECT dept_id, SUM(salary), AVG(salary)
FROM emp
WHERE salary > 800
GROUP BY dept_id
HAVING SUM(salary) > 5000
ORDER BY dept_id;

Agregat bez GROUP BY zwraca dokładnie jeden wiersz (pusta tabela: jeden wiersz z NULL).
COUNT(*) liczy wszystkie wiersze, COUNT(kolumna) pomija NULL w tej kolumnie.
W SELECT z GROUP BY mogą być tylko: funkcje agregujące, kolumny z GROUP BY, wyrażenia stałe.
AVG(cena+cena) jest poprawne (jeden argument), AVG(cena, cena) nie.

7. Złączenia tabel

-- Iloczyn kartezjański (cross join): brak warunku, kazdy z kazdym
-- |emp| * |dept| wierszy
SELECT * FROM emp, dept;

-- Inner / equi join: warunek laczenia (PK = FK)
SELECT e.first_name, d.name
FROM emp e JOIN dept d ON e.dept_id = d.id;
-- starszy zapis: FROM emp e, dept d WHERE e.dept_id = d.id;

-- Self join: tabela z sama soba (np. pracownik i jego szef)
SELECT e.last_name, s.last_name AS szef
FROM emp e JOIN emp s ON e.manager_id = s.id;

-- Outer join: zachowuje wiersze bez dopasowania (uzupelnia NULL-ami)
SELECT d.name, e.last_name
FROM dept d LEFT OUTER JOIN emp e ON d.id = e.dept_id;

SELECT d.name, e.last_name
FROM emp e RIGHT OUTER JOIN dept d ON d.id = e.dept_id;

Iloczyn kartezjański dla 15, 25, 16 rekordów: 15 * 25 * 16 = 6000.
FULL OUTER JOIN nie jest wspierany w MySQL (emuluje się go przez LEFT UNION RIGHT).

8. UNION, UNION ALL

SELECT first_name FROM emp
UNION
SELECT name FROM dept;

UNION łączy wyniki i usuwa duplikaty, UNION ALL zostawia duplikaty (szybsze).
Liczba i typy kolumn w łączonych zapytaniach muszą się zgadzać. Nagłówki bierze się z pierwszego SELECT.

9. Podzapytania

-- jeden rekord
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

-- wiele rekordów: IN / ANY / ALL
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE region_id = 5);
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE last_name LIKE 'S%');
SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = 41);

-- skorelowane: EXISTS / NOT EXISTS
SELECT id, first_name FROM emp E
WHERE EXISTS (SELECT 1 FROM ord O WHERE O.sales_rep_id = E.id);

-- podzapytanie w FROM (widok dynamiczny)
SELECT t.dept_id, t.srednia
FROM (SELECT dept_id, AVG(salary) AS srednia FROM emp GROUP BY dept_id) t
WHERE t.srednia > 1500;

> ALL to większe od wszystkich (czyli od maksimum), > ANY to większe od któregokolwiek (od minimum).
EXISTS zwraca prawdę, gdy podzapytanie zwróci choć jeden wiersz.

10. Funkcje tekstowe

-- sklejanie (bez spacji po CONCAT)
CONCAT('a', 'b')
-- podciag od pozycji 2, dlugosc 3
SUBSTR(last_name, 2, 3)
-- dlugosc
LENGTH(first_name)
UPPER(s), LOWER(s)
-- dopelnianie do dlugosci
LPAD(s, 20, '*'), RPAD(s, 20, '.')
LTRIM(s), RTRIM(s)
-- BOTH | LEADING | TRAILING
TRIM(BOTH '*' FROM '***xyz***')

11. Funkcje liczbowe

-- 2
ROUND(1.56)
-- 1.2
ROUND(1.234, 1)
-- 1
FLOOR(1.56)
-- 2
CEIL(1.56)
-- -2
FLOOR(-1.56)
-- -2
ROUND(-1.56)
-- obcina bez zaokraglania
TRUNCATE(1.567, 1)
MOD(10, 3), ABS(-5), POWER(2, 3), SQRT(9)

12. Funkcje daty i czasu

-- data i czas
NOW(), CURRENT_TIMESTAMP, SYSDATE()
-- sama data
CURDATE(), CURRENT_DATE()
-- formatowanie wg maski
DATE_FORMAT(start_date, '%d-%m-%Y')
-- tekst na date
STR_TO_DATE('31-12-2023', '%d-%m-%Y')
DATE_ADD(SYSDATE(), INTERVAL 5 DAY)
DATE_SUB(NOW(), INTERVAL 1 MONTH)
YEAR(d), MONTH(d), DAY(d)

Maski: %Y rok 4 cyfry, %m miesiac, %d dzien, %H:%i:%s czas, %W nazwa dnia, %M nazwa miesiaca.

13. INSERT

-- wszystkie kolumny w kolejnosci tabeli
INSERT INTO emp VALUES (102, 'Kowalski', '2004-05-01', ...);

-- wybrane kolumny (reszta dostaje DEFAULT lub NULL, AUTO_INCREMENT sam)
INSERT INTO emp (id, last_name, start_date) VALUES (102, 'Kowalski', '2004-05-01');

-- wiele wierszy naraz
INSERT INTO test VALUES (1), (2), (3);

-- skladnia SET
INSERT INTO emp SET first_name='Tomasz', last_name='Wisniewski';

-- z innej tabeli
INSERT INTO emp2 (imie, nazwisko) SELECT first_name, last_name FROM emp WHERE salary > 1500;

-- ignorowanie duplikatow klucza
INSERT IGNORE INTO test VALUES (1), (1), (2);

14. UPDATE

UPDATE emp
SET salary = salary * 1.1
WHERE dept_id IN (100, 101);

Bez WHERE zmodyfikuje wszystkie wiersze. UPDATE jest transakcyjny (przy InnoDB).

15. DELETE, TRUNCATE

DELETE FROM emp WHERE id = 100;
-- usuwa wszystkie wiersze (mozna cofnac w transakcji)
DELETE FROM emp;
-- szybkie czyszczenie, resetuje AUTO_INCREMENT
TRUNCATE TABLE emp;

Próba usunięcia wiersza nadrzędnego, do którego istnieje odwołanie FK, daje błąd 1451. Można to obejść klauzulą ON DELETE CASCADE przy definicji klucza obcego.

16. CREATE TABLE i typy

CREATE TABLE studenci (
   stud_id   INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
   imie      VARCHAR(20)  NOT NULL,
   nazwisko  VARCHAR(30)  NOT NULL,
   zarobki   DECIMAL(11,2),
   plec      ENUM('M','K') NOT NULL,
   data_ur   DATE
) ENGINE = InnoDB;

Typy: INT/INTEGER, DECIMAL(p,s), VARCHAR(n) (długość wymagana), CHAR(n), DATE, DATETIME, ENUM(...) (jedna wartość ze zbioru), SET(...) (wiele wartości ze zbioru).

CREATE TABLE IF NOT EXISTS ... nie zgłosi błędu gdy tabela istnieje.
ENGINE = InnoDB jest obowiązkowe przy kluczach obcych.

17. Ograniczenia (constraints)

Ograniczenie	Działanie
`NOT NULL`	kolumna nie może być pusta
`PRIMARY KEY`	unikalne i różne od NULL, jedno na tabelę
`UNIQUE`	unikalne, dopuszcza NULL, może być wiele
`FOREIGN KEY`	integralność referencyjna
`DEFAULT v`	wartość domyślna
`CHECK (war)`	warunek (w starszym MySQL ignorowany)
`ENUM` / `SET`	wartości ze zdefiniowanego zbioru

-- definicja kolumnowa
prac_id INTEGER PRIMARY KEY,
pesel   INTEGER NOT NULL UNIQUE,

-- definicja tablicowa (wymagana dla kluczy zlozonych)
PRIMARY KEY (osoba_id),
UNIQUE (imie, nazwisko, pseudo)

PRIMARY KEY vs UNIQUE: klucz główny nie dopuszcza NULL i jest tylko jeden, UNIQUE dopuszcza NULL i może być wiele. Jedna kolumna może mieć kilka ograniczeń (np. FOREIGN KEY + UNIQUE + NOT NULL).

18. Klucze obce

-- A) w CREATE TABLE (najpierw nadrzedna, potem podrzedna)
CREATE TABLE pracownicy (
   prac_id   INTEGER PRIMARY KEY AUTO_INCREMENT,
   miasto_id INTEGER,
   CONSTRAINT pracownicy_miasta_id_fk
     FOREIGN KEY (miasto_id) REFERENCES miasta (miasto_id)
) ENGINE = InnoDB;

-- B) przez ALTER TABLE (kolejnosc tabel nieistotna)
ALTER TABLE pracownicy
  ADD CONSTRAINT pracownicy_miasta_id_fk
  FOREIGN KEY (miasto_id) REFERENCES miasta (miasto_id);

Kolumna FK musi być tego samego typu co kolumna nadrzędna.
FK może dopuszczać NULL i może być UNIQUE (relacja 1:1). Kolumna może być jednocześnie PK i FK.
Nazwa ograniczenia (CONSTRAINT nazwa) jest opcjonalna. Konwencja: tabela_kolumna_fk.
Kolejność tworzenia (FK w CREATE TABLE): nadrzędna, potem podrzędna. Kasowanie: odwrotnie, najpierw podrzędna.

19. AUTO_INCREMENT

-- id samo
INSERT INTO region (name) VALUES ('Dolnoslaskie');

Wstawia max dotychczasowego id + 1 (liczy się max, nie liczba wierszy).
Nie odzyskuje skasowanych numerów.

20. Widoki (VIEW)

CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.first_name, e.last_name, d.name
FROM emp e JOIN dept d ON e.dept_id = d.id;

SELECT * FROM emp_dept_view;

Widok przechowuje tylko definicję, dane pobiera na bieżąco.
INSERT/UPDATE/DELETE na widoku są czasami możliwe (tylko proste, modyfikowalne widoki, bez agregacji, GROUP BY, DISTINCT, złączeń wielu tabel). W praktyce głównie do odczytu.

21. Indeksy

CREATE TABLE pracownicy (
   -- indeks automatyczny
   prac_id  INTEGER PRIMARY KEY,
   -- UNIQUE to tez indeks
   pseudo   VARCHAR(10) UNIQUE,
   nazwisko VARCHAR(30),
   -- INDEX = KEY (synonimy)
   INDEX (nazwisko),
   KEY (data_ur)
) ENGINE = InnoDB;

CREATE INDEX idx_naz ON pracownicy (nazwisko);

Indeks zakładany automatycznie dla PRIMARY KEY i UNIQUE. Można na dowolnym typie kolumny, na jednej lub wielu.
Koszt: zajmuje miejsce, spowalnia INSERT/UPDATE/DELETE (przebudowa). Nadmiar szkodzi.

22. ALTER TABLE

ALTER TABLE prac RENAME TO pracownicy;
ALTER TABLE prac ADD COLUMN zarobki DECIMAL(11,2) NOT NULL;
-- zmiana typu
ALTER TABLE prac MODIFY COLUMN imie VARCHAR(40) NOT NULL;
-- zmiana nazwy + typu
ALTER TABLE prac CHANGE COLUMN pseudo ksywka VARCHAR(8);
ALTER TABLE prac DROP COLUMN imie2;
ALTER TABLE prac ADD PRIMARY KEY (prac_id);
ALTER TABLE prac ADD UNIQUE (ksywka);

MODIFY zmienia typ kolumny (zachowuje nazwę), CHANGE zmienia też nazwę.

23. DROP

DROP TABLE emp;
DROP TABLE IF EXISTS child;
DROP VIEW emp_dept_view;
DROP INDEX idx_naz ON pracownicy;
DROP TRIGGER before_update_emp_audit;

Kasując tabele połączone FK, najpierw usuwa się podrzędne (child), potem nadrzędne (parent).

24. Procedury składowane

DELIMITER $$
CREATE PROCEDURE GetEmpByID (IN emp_ID INT)
BEGIN
  SELECT first_name, last_name FROM emp WHERE id = emp_ID;
END $$
DELIMITER ;

CALL GetEmpByID(11);

DELIMITER $$ zmienia separator na czas definicji (bo w ciele są średniki), potem wraca do ;.
Parametry: IN (wejściowy), OUT (wyjściowy), INOUT. Wywołanie przez CALL.

25. Wyzwalacze (triggery)

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body;

Przykład audytu zmian:

DELIMITER $$
CREATE TRIGGER before_update_emp_audit
BEFORE UPDATE ON emp FOR EACH ROW
BEGIN
  INSERT INTO emp_audit(empID, sal_old, sal_new, action)
  VALUES (OLD.id, OLD.salary, NEW.salary, 'before update');
END $$
DELIMITER ;

6 rodzajów: BEFORE/AFTER w połączeniu z INSERT/UPDATE/DELETE.
NEW.kolumna to wartość nowa (INSERT, UPDATE), OLD.kolumna to wartość stara (UPDATE, DELETE).
FOR EACH ROW znaczy, że wyzwalacz uruchamia się dla każdego zmienianego wiersza.

Pułapki egzaminacyjne (szybkie odpowiedzi)

SELECT * FROM t1, t2, t3; przy 15, 25, 16 rekordach: 6000 wierszy.
Klucz główny może być liczbowy lub znakowy.
SELECT 123+456 FROM tab; jest poprawne (579 na wiersz).
VARCHAR bez długości jest niepoprawny.
Alias tabeli nie w cudzysłowie. Alias kolumny w cudzysłowie tylko gdy ma spację.
Kopiowanie tabeli: CREATE TABLE … AS SELECT lub LIKE (nie ma DUPLICATE TABLE).
Klucz obcy musi mieć ten sam typ co kolumna nadrzędna.
Kolejność tworzenia (FK w CREATE): najpierw nadrzędna, potem podrzędna. Przy ALTER nieistotna.
Kolejność kasowania: najpierw podrzędna, potem nadrzędna.
Klucz obcy z NULL może być UNIQUE.
Nazwa ograniczenia jest opcjonalna.
AUTO_INCREMENT wstawia max + 1 i nie odzyskuje skasowanych numerów.
AVG(kol) bez GROUP BY zwraca zawsze jeden wiersz.
Poprawny agregat: AVG(cena+cena). Błędne: AVG(cena, cena).
AVG(100)+MIN(50) = 150.
AVG(salary)-SUM(salary) przy dodatnich zarobkach: ujemny.
Indeks: kolumna dowolnego typu, INDEX = KEY, automatyczny dla PK i UNIQUE.
DML na widoku: czasami możliwy.
Kolumna może być naraz kluczem głównym i obcym: prawda.
Każda tabela relacyjna może (nie musi) mieć klucz główny.
> ALL to większe od maksimum, > ANY to większe od minimum.
UNION usuwa duplikaty, UNION ALL je zostawia.
LIKE bez BINARY nie rozróżnia wielkości liter. Daty w formacie RRRR-MM-DD.
MODIFY zmienia typ kolumny, CHANGE zmienia typ i nazwę.
FULL OUTER JOIN nie istnieje w MySQL.
Wyzwalacze: 6 rodzajów (BEFORE/AFTER × INSERT/UPDATE/DELETE), NEW. i OLD., FOR EACH ROW.