SQL

SELECT … FROM●●●

SELECT kol1, kol2 FROM tabela;

Wybiera wskazane kolumny ze wszystkich wierszy tabeli. Gwiazdka (*) oznacza wszystkie kolumny.

SELECT marka, model, cena FROM pojazdy;

Uwaga. Treść „wybierające jedynie pola…” oznacza wypisanie dokładnie tych kolumn, w tej kolejności.

DISTINCT●○○

SELECT DISTINCT kol FROM tabela;

Usuwa powtarzające się wiersze z wyniku — każda wartość pojawia się raz.

SELECT DISTINCT miasto FROM adresy ORDER BY miasto;

AS (alias)●●○

SELECT kol AS "Nazwa" FROM tabela;

Nadaje kolumnie lub wyrażeniu własną nazwę w wyniku. Tak realizuje się polecenie „nazwa kolumny (alias)”.

SELECT AVG(cena) AS "Średnia cena" FROM pojazdy;

Uwaga. Alias ze spacją albo polskimi znakami zapisz w cudzysłowie, dokładnie tak jak w treści zadania.

WHERE●●●

… WHERE warunek;

Pozostawia tylko wiersze spełniające warunek. Operatory: =, <> (lub !=), <, >, <=, >=.

SELECT nazwa FROM szczyty WHERE wysokosc > 1500;

Uwaga. Tekst i daty zapisuj w apostrofach lub cudzysłowie: WHERE marka = 'BM'. Liczby bez cudzysłowu.

AND, OR, NOT●●○

WHERE a AND b OR NOT c

Łączą warunki: AND — oba prawdziwe, OR — co najmniej jeden, NOT — negacja. AND wiąże silniej niż OR; nawiasy zmieniają kolejność.

SELECT nazwa FROM wycieczki WHERE poczatek = 'Muszyna' OR poczatek = 'Wieliczka';

BETWEEN … AND●○○

WHERE kol BETWEEN a AND b

Wartość z przedziału domkniętego (łącznie z końcami). Odpowiada: kol >= a AND kol <= b.

SELECT nazwisko, pensja FROM pracownicy WHERE staz BETWEEN 10 AND 20;

IN●○○

WHERE kol IN (w1, w2, …)

Wartość równa któremukolwiek z wymienionych. Krótszy zapis wielu warunków OR na jednej kolumnie.

SELECT imie FROM osoby WHERE miasto IN ('Lublin', 'Kielce');

LIKE●●●

WHERE kol LIKE 'wzorzec'

Dopasowanie do wzorca: % — dowolny ciąg znaków (także pusty), _ — dokładnie jeden znak.

SELECT imie, nazwisko FROM osoby WHERE imie LIKE 'J%';   -- imiona na literę J

Uwaga. Początek: 'J%'. Koniec: '%a'. Zawieranie: '%mir%'. Wzorzec zawsze w apostrofach.

IS NULL / IS NOT NULL●○○

WHERE kol IS NULL

Sprawdza brak wartości. Porównanie = NULL nigdy nie jest prawdziwe, trzeba użyć IS.

DELETE FROM nagrody WHERE cena IS NULL OR cena = 0;

ORDER BY●●○

… ORDER BY kol [ASC | DESC]

Sortuje wynik: ASC — rosnąco (domyślnie), DESC — malejąco. Można podać kilka kolumn po przecinku.

SELECT marka, model, cena FROM pojazdy ORDER BY cena DESC;

Uwaga. „Posortowane rosnąco według nazwy” = ORDER BY nazwa. „Malejąco” = DESC po nazwie kolumny.

LIMIT●○○

… LIMIT n   albo   LIMIT przesunięcie, n

Ogranicza liczbę zwróconych wierszy do n (po sortowaniu).

SELECT marka, model, cena FROM pojazdy WHERE marka = 'BM' ORDER BY cena DESC LIMIT 15;

Uwaga. „Pierwsze 15 rekordów”, „najdroższy”, „dwa dowolne rekordy” — wszystko to LIMIT, zwykle z ORDER BY.

COUNT●●○

COUNT(*)   COUNT(kol)   COUNT(DISTINCT kol)

Liczba wierszy. COUNT(*) liczy wszystkie, COUNT(kol) pomija wartości NULL, z DISTINCT liczy różne wartości.

SELECT COUNT(*) FROM klienci;

SUM●○○

SUM(kol)

Suma wartości kolumny (NULL pomijane).

SELECT SUM(koszt) AS "Całkowity koszt" FROM uczestnicy;

AVG●●○

AVG(kol)

Średnia arytmetyczna wartości kolumny.

SELECT AVG(stanWody) FROM pomiary;

Uwaga. Wynik często owija się w ROUND(…, 2) albo FLOOR(…), gdy treść każe zaokrąglić.

MIN, MAX●●○

MIN(kol)   MAX(kol)

Najmniejsza i największa wartość kolumny. „Najdroższy”, „najwyższy”, „najstarszy” to zwykle MAX albo ORDER BY … DESC LIMIT 1.

SELECT MAX(wysokosc) FROM gory;

GROUP BY●●○

SELECT kol, COUNT(*) FROM t GROUP BY kol;

Dzieli wiersze na grupy o tej samej wartości kolumny; funkcje agregujące liczą osobno dla każdej grupy.

SELECT wojewodztwa.nazwa, COUNT(miasta.nazwa) AS "Liczba miast"
FROM wojewodztwa JOIN miasta ON wojewodztwa.id = miasta.id_wojewodztwa
GROUP BY wojewodztwa.nazwa;

Uwaga. Wskazówki w treści: „dla każdego…”, „zgrupowane”, „w podziale na…”. Kolumny w SELECT poza agregatami muszą być w GROUP BY.

HAVING○○○

… GROUP BY kol HAVING warunek_na_agregacie

Filtruje grupy po obliczeniu agregatu (WHERE filtruje wiersze przed grupowaniem).

SELECT autor, COUNT(*) FROM ksiazki GROUP BY autor HAVING COUNT(*) > 3;

JOIN … ON●●●

FROM t1 JOIN t2 ON t1.id = t2.t1_id

Łączy wiersze dwóch tabel o pasujących kluczach (INNER JOIN — wiersze bez pary znikają).

SELECT nazwisko, imie, zamowienia.id
FROM klienci JOIN zamowienia ON klienci.id = zamowienia.id_klienta;

Uwaga. Gdy nazwa kolumny występuje w obu tabelach, poprzedź ją nazwą tabeli: zamowienia.id. Brak ON daje iloczyn kartezjański.

JOIN … USING

FROM t1 JOIN t2 USING(kol)

Skrót, gdy kolumna łącząca ma w obu tabelach tę samą nazwę.

SELECT model, nazwa, cena FROM buty JOIN produkt USING(model);

Występuje w rozwiązaniach wzorcowych CKE

Złączenie przez WHERE

FROM t1, t2 WHERE t1.id = t2.t1_id

Starszy zapis złączenia — tabele po przecinku, warunek łączący w WHERE. Daje ten sam wynik co JOIN … ON.

SELECT tytul, tresc FROM ogloszenie, uzytkownik
WHERE ogloszenie.uzytkownik_id = uzytkownik.id AND kategoria = 1;

Występuje w rozwiązaniach wzorcowych CKE

LEFT JOIN○○○

FROM t1 LEFT JOIN t2 ON …

Zachowuje wszystkie wiersze lewej tabeli, także bez pary (kolumny prawej tabeli mają wtedy NULL).

SELECT imie, COUNT(zamowienia.id)
FROM klienci LEFT JOIN zamowienia ON klienci.id = id_klienta
GROUP BY klienci.id;

Uwaga. Użyteczny, gdy trzeba pokazać także klientów (autorów, kategorie) bez żadnych powiązanych rekordów.

Relacja n:m●○○

t1 JOIN laczaca ON … JOIN t2 ON …

Trzy tabele: dwie encje i tabela łącząca z dwoma kluczami obcymi. Złączenie przechodzi przez tabelę łączącą.

SELECT choroby.nazwa, COUNT(objawy.id)
FROM choroby JOIN choroby_objawy ON choroby.id = id_choroby
  JOIN objawy ON objawy.id = id_objawu
GROUP BY choroby.id;

NOT IN — brak powiązanych rekordów●○○

WHERE id NOT IN (SELECT klucz_obcy FROM t2)

Wybiera rekordy, których identyfikator nie występuje w drugiej tabeli (np. osoby, które nie mają wpisu w rejestrze). Alternatywa: LEFT JOIN … WHERE t2.id IS NULL.

SELECT id, nazwisko FROM personel
WHERE id NOT IN (SELECT id_personelu FROM rejestr);

Podzapytanie●○○

WHERE kol = (SELECT …)

Zapytanie wewnątrz zapytania; wewnętrzne wylicza wartość (np. maksimum), zewnętrzne z niej korzysta.

SELECT imie, nazwisko, pensja FROM pracownicy
WHERE pensja = (SELECT MAX(pensja) FROM pracownicy);

Uwaga. Wewnętrzne zapytanie zwracające jedną wartość porównuj operatorem =, kilka wartości — IN.

Kolumna wyliczana●●○

SELECT kol1 * kol2 AS wynik …

W SELECT można używać działań + − * / na kolumnach; wynik nazywa się aliasem.

SELECT liczba_odbitek * cena AS "Do zapłaty" FROM zamowienia;

ROUND●○○

ROUND(x, miejsca)

Zaokrągla do podanej liczby miejsc po przecinku (do najbliższej wartości).

SELECT ROUND(AVG(Cena), 2) AS "Średnia_cena" FROM wyroby GROUP BY Rodzaj;

FLOOR, CEIL●○○

FLOOR(x)   CEIL(x)

FLOOR — największa liczba całkowita nie większa niż x (w dół). CEIL — najmniejsza nie mniejsza niż x (w górę).

SELECT FLOOR(AVG(rokUr)) AS "Średni rok urodzenia" FROM uczestnicy;

Uwaga. „Zaokrąglony do najbliższej mniejszej liczby całkowitej” = FLOOR.

TRUNCATE○○○

TRUNCATE(x, miejsca)

Obcina część ułamkową do podanej liczby miejsc bez zaokrąglania.

SELECT TRUNCATE(2.789, 1);   -- 2.7

RAND()●○○

ORDER BY RAND() LIMIT n

Liczba losowa z przedziału [0, 1). W ORDER BY miesza kolejność wierszy — z LIMIT daje losowe rekordy.

SELECT nazwa, opis, cena FROM nagrody ORDER BY RAND() LIMIT 5;

Uwaga. „Losujące dwa dowolne rekordy” = ORDER BY RAND() LIMIT 2. FLOOR(1 + RAND() * 10) daje liczbę całkowitą 1–10.

MOD, ABS, POWER○○○

MOD(a, b)   ABS(x)   POWER(x, y)

Reszta z dzielenia, wartość bezwzględna, potęga.

SELECT id FROM zamowienia WHERE MOD(id, 2) = 0;

CONCAT○○○

CONCAT(a, b, …)

Skleja napisy w jeden.

SELECT CONCAT(imie, ' ', nazwisko) AS osoba FROM klienci;

LENGTH, CHAR_LENGTH●○○

CHAR_LENGTH(txt)

Liczba znaków napisu (LENGTH liczy bajty — dla polskich liter wynik bywa większy).

SELECT nazwa FROM miasta WHERE CHAR_LENGTH(nazwa) > 10;

UPPER, LOWER●○○

UPPER(txt)   LOWER(txt)

Zamiana na wielkie i małe litery (synonimy: UCASE, LCASE).

SELECT LOWER(nazwa) FROM wojewodztwa;

LEFT, RIGHT○○○

LEFT(txt, n)   RIGHT(txt, n)

Pierwsze lub ostatnie n znaków napisu.

SELECT LEFT(imie, 2), RIGHT(nazwisko, 3) FROM czytelnicy;

SUBSTRING○○○

SUBSTRING(txt, od, ile)

Fragment napisu od pozycji „od” (liczonej od 1) o długości „ile”.

SELECT SUBSTRING(pesel, 1, 2) FROM osoby;

REPLACE, TRIM○○○

REPLACE(txt, co, naco)   TRIM(txt)

Zamiana ciągu znaków na inny; usunięcie spacji z początku i końca napisu.

SELECT REPLACE(telefon, '-', '') FROM klienci;

CURDATE, NOW●○○

CURDATE()   CURRENT_DATE   NOW()   CURTIME()

Bieżąca data; data i czas; bieżący czas. CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP — synonimy.

UPDATE wagi SET dzien = CURRENT_DATE, czas = CURRENT_TIME WHERE lokalizacje_id BETWEEN 2 AND 4;

Uwaga. W tabelach pomocniczych arkuszy pojawiają się też ADDDATE, ADDTIME i CONVERT_TZ.

YEAR, MONTH, DAY●○○

YEAR(data)   MONTH(data)   DAY(data)

Wydobywają rok, miesiąc i dzień z daty. Przydają się w WHERE i GROUP BY.

SELECT imie FROM swinki WHERE MONTH(data_ur) = 7;

Uwaga. Alternatywa dla LIKE: data LIKE '2021-08-%' wybiera sierpień 2021.

DATEDIFF●○○

DATEDIFF(data1, data2)

Liczba dni między dwiema datami (data1 − data2).

SELECT pseudonim, DATEDIFF(CURRENT_DATE(), data_zdobycia) AS dni FROM zawodnicy;

DATE_ADD, ADDDATE○○○

ADDDATE(data, INTERVAL n DAY)

Dodaje do daty podany odstęp (DAY, MONTH, YEAR).

SELECT ADDDATE(data_zamowienia, INTERVAL 14 DAY) FROM zamowienia;

DATE_FORMAT○○○

DATE_FORMAT(data, '%d.%m.%Y')

Formatuje datę według szablonu (%Y rok 4-cyfrowy, %m miesiąc, %d dzień, %H godzina).

SELECT DATE_FORMAT(data, '%d.%m.%Y') FROM wagi;

INSERT INTO●●○

INSERT INTO t (kol1, kol2) VALUES (w1, w2);   INSERT INTO t VALUES (NULL, w1, w2);

Dodaje wiersz. Przy zapisie bez listy kolumn podaj wartości dla wszystkich kolumn w kolejności z tabeli; NULL w miejscu AUTO_INCREMENT nada kolejny numer.

INSERT INTO logowanie (id, nick, haslo) VALUES (3, 'Jeremi', 'Jer123');

Uwaga. Wstawianą wartość tekstową otocz apostrofami, liczbę zapisz bez.

UPDATE … SET●●○

UPDATE t SET kol = wartość WHERE warunek;

Zmienia wartości w wierszach spełniających warunek. Można użyć wyrażenia na samej kolumnie (staz = staz + 1).

UPDATE zawodnicy SET klasa = '5A' WHERE klasa = '4A';

Uwaga. Bez WHERE zmieni WSZYSTKIE wiersze tabeli.

DELETE FROM●○○

DELETE FROM t WHERE warunek;

Usuwa wiersze spełniające warunek.

DELETE FROM wagi WHERE id > 22;

Uwaga. Bez WHERE usunie wszystkie wiersze (struktura tabeli zostaje).

ALTER TABLE … ADD●○○

ALTER TABLE t ADD COLUMN kol typ [AFTER inna];

Dodaje kolumnę do istniejącej tabeli. AFTER wskazuje, po której kolumnie ją umieścić.

ALTER TABLE wycieczki ADD COLUMN ocena INT;

ALTER TABLE … DROP●○○

ALTER TABLE t DROP COLUMN kol;

Usuwa kolumnę wraz z jej danymi.

ALTER TABLE uczestnicy DROP COLUMN haslo;

ALTER TABLE … CHANGE / MODIFY●○○

ALTER TABLE t CHANGE COLUMN stara nowa typ;
ALTER TABLE t MODIFY COLUMN kol nowy_typ;

CHANGE zmienia nazwę (i typ) kolumny; MODIFY zmienia tylko typ lub właściwości.

ALTER TABLE zapisy CHANGE COLUMN Id_klienta Id_sluchacza INT;

CREATE TABLE●○○

CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, nazwa VARCHAR(50) NOT NULL);

Tworzy tabelę. PRIMARY KEY — klucz główny, AUTO_INCREMENT — automatyczna numeracja, NOT NULL — wartość obowiązkowa.

CREATE TABLE kategorie (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(40) NOT NULL
);

Typy danych●○○

INT  VARCHAR(n)  TEXT  DATE  TIME  DATETIME  DECIMAL(p,s)  TINYINT(1)

INT — liczba całkowita; VARCHAR(n) — tekst do n znaków; TEXT — długi tekst; DATE — data RRRR-MM-DD; DECIMAL(p,s) — liczba dziesiętna; TINYINT(1) — wartość logiczna 0/1.

ALTER TABLE imieniny ADD COLUMN zyczenia VARCHAR(500);

DROP TABLE / DROP DATABASE○○○

DROP TABLE t;   DROP DATABASE baza;

Usuwa tabelę lub całą bazę razem z danymi — bezpowrotnie.

DROP TABLE IF EXISTS tymczasowa;

CREATE DATABASE●●●

CREATE DATABASE nazwa CHARACTER SET utf8 COLLATE utf8_polish_ci;

Tworzy bazę z zestawem znaków obsługującym polskie litery. W phpMyAdmin odpowiada przyciskowi „Nowa” — wpisujesz nazwę i wybierasz porównywanie znaków (collation) utf8_polish_ci albo utf8_unicode_ci.

CREATE DATABASE samochody CHARACTER SET utf8 COLLATE utf8_polish_ci;

Import i zrzut po imporcie●●●

phpMyAdmin → baza → Import → Wybierz plik → Wykonaj

Plik .sql z archiwum importujesz do utworzonej bazy. Zrzut ekranu „import” ma obejmować cały ekran z paskiem zadań, na którym widać poprawny import (lista tabel lub komunikat).

-- plik baza.sql zawiera polecenia CREATE TABLE i INSERT INTO

CREATE USER●○○

CREATE USER 'nazwa'@'localhost' IDENTIFIED BY 'hasło';

Tworzy konto, z którego można łączyć się z serwera lokalnego. Hasło podajesz dokładnie tak, jak w treści.

CREATE USER 'Marek'@'localhost' IDENTIFIED BY 'M@reK';

Uwaga. Konto nie ma żadnych praw, dopóki nie nadasz ich poleceniem GRANT.

GRANT●○○

GRANT prawa ON baza.tabela TO 'nazwa'@'localhost';

Nadaje uprawnienia: SELECT (odczyt), INSERT (dodawanie), UPDATE (zmiana), DELETE (usuwanie), ALL PRIVILEGES (wszystkie). baza.* oznacza wszystkie tabele bazy.

GRANT SELECT, UPDATE ON obuwie.produkt TO 'Marek'@'localhost';

Uwaga. „Wszystkie prawa do manipulowania danymi” = SELECT, INSERT, UPDATE, DELETE (bez praw do zmiany struktury).

REVOKE / DROP USER○○○

REVOKE prawa ON baza.* FROM 'u'@'localhost';   DROP USER 'u'@'localhost';

Odebranie wybranych praw; usunięcie konta.

REVOKE DELETE ON obuwie.* FROM 'Marek'@'localhost';

SHOW GRANTS●○○

SHOW GRANTS FOR 'u'@'localhost';

Wyświetla prawa konta — można użyć do udokumentowania zrzutem, że uprawnienia zostały nadane.

SHOW GRANTS FOR 'Marek'@'localhost';