1) Modyfikatory SELECT (tuż po słowie SELECT)

1.1 DISTINCT

usuwa duplikaty wyników (unikalne wiersze / unikalne kombinacje kolumn).

SELECT DISTINCT email FROM users;

1.2 ALL (domyślne) – nie wpisujemy, ale jest

zwraca wszystkie wiersze (z duplikatami).

SELECT ALL email FROM users;

2) Co może być na liście SELECT (kolumny i wyrażenia)

2.1 Nazwy kolumn

wybór konkretnych kolumn.

SELECT id, name FROM users;

2.2 Gwiazdka *

wybór wszystkich kolumn z tabeli.

SELECT * FROM users;

2.3 Stałe (literały) jako kolumny wyniku

można zwrócić stały tekst/liczbę jako kolumnę – takie dodanie dodatkowej stałej kolumny.

SELECT 'PL' AS kraj, 1 AS wersja FROM users;

2.4 Wyrażenia arytmetyczne + – * / %

obliczenia na liczbach/kolumnach.

SELECT price_net * 1.23 AS price_gross FROM products;

2.5 Nawiasy (kolejność działań)

wymuszenie kolejności działań w obliczeniach.

SELECT (a + b) * c AS wynik FROM t;

2.6 Alias kolumny: AS

nadaje nazwę wynikowej kolumnie.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

3) Funkcje tekstowe (częste w SELECT)

3.1 CONCAT(a, b, …)

skleja tekst w jedną wartość.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

3.2 CONCAT_WS(sep, …)

skleja tekst z separatorem (np. “ – ”).

SELECT CONCAT_WS(' - ', id, email) AS opis FROM users;

3.3 LENGTH(str) / CHAR_LENGTH(str)

długość tekstu (LENGTH – w bajtach, CHAR_LENGTH – w znakach).

SELECT CHAR_LENGTH(name) AS ile_znakow FROM users;

3.4 LOWER(str) / UPPER(str)

zmienia litery na małe/duże.

SELECT LOWER(email) AS email_lower FROM users;

3.5 TRIM(str) + LTRIM/RTRIM

usuwa spacje z początku/końca.

SELECT TRIM(name) AS name_clean FROM users;

3.6 SUBSTRING(str, pos, len) (alias: SUBSTR)

wycina fragment tekstu.

SELECT SUBSTRING(email, 1, 5) AS prefix FROM users;

3.7 REPLACE(str, from, to)

zamienia fragment tekstu na inny.

SELECT REPLACE(phone, ' ', '') AS phone_no_spaces FROM users;

4) Funkcje liczbowe (w tym ROUND) 

4.1 ROUND(x, d)

zaokrągla liczbę do d miejsc po przecinku (lub do całości, gdy brak d).

SELECT ROUND(price, 2) AS price_2dp FROM products;

4.2 CEIL(x) / CEILING(x)

zaokrągla w górę do najbliższej liczby całkowitej.

SELECT CEIL(price) AS price_up FROM products;

4.3 FLOOR(x)

zaokrągla w dół.

SELECT FLOOR(price) AS price_down FROM products;

4.4 ABS(x)

wartość bezwzględna.

SELECT ABS(balance) AS balance_abs FROM accounts;

4.5 MOD(a, b) (lub operator %)

reszta z dzielenia.

SELECT MOD(id, 2) AS parzystosc FROM users;

5) Daty i czas (mogą występować w SELECT) 

5.1 NOW() / CURRENT_TIMESTAMP

aktualna data i czas serwera bazy.

SELECT NOW() AS teraz FROM users;

5.2 CURDATE() / CURRENT_DATE

aktualna data serwera bazy.

SELECT CURDATE() AS dzis FROM users;

5.3 DATE(x) / TIME(x)

wyciąga samą datę lub sam czas z typu datetime/timestamp.

SELECT DATE(created_at) AS data_utworzenia FROM orders;

5.4 DATE_FORMAT(date, format)

formatuje datę do tekstu (np. YYYY-MM-DD).

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS data FROM orders;

6) NULL i wartości zastępcze

6.1 COALESCE(a, b, …)

zwraca pierwszą wartość, która nie jest NULL.

SELECT COALESCE(nickname, first_name) AS display_name FROM users;

6.2 IFNULL(a, b)

jeśli a jest NULL, zwróć b.

SELECT IFNULL(phone, 'brak') AS phone FROM users;

6.3 NULLIF(a, b)

jeśli a = b, zwraca NULL, w przeciwnym razie a.

SELECT NULLIF(status, '') AS status_null_if_empty FROM t;

7) Warunkowe (logika w SELECT)

7.1 CASE WHEN … THEN … ELSE … END

warunek wielowariantowy zwracający wartość.

SELECT
  CASE
    WHEN age >= 18 THEN 'adult'
    ELSE 'minor'
  END AS typ
FROM users;

7.2 IF(condition, a, b)

prosty warunek (jeżeli warunek prawdziwy → a, inaczej → b).

SELECT IF(active = 1, 'TAK', 'NIE') AS aktywny FROM users;

Mini-przykład łączący kilka elementów (SELECT przed FROM):

SELECT DISTINCT
  CONCAT_WS(' ', UPPER(first_name), last_name) AS full_name,
  ROUND(salary, 2) AS salary_2dp,
  IFNULL(phone, 'brak') AS phone,
  DATE_FORMAT(created_at, '%Y-%m-%d') AS created_date
FROM employees;

baza do ćwiczeń – pobierz plik txt

SELECT DISTINCT email FROM users;

SELECT id, email FROM users;

SELECT * FROM users;

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

SELECT CONCAT_WS(' - ', id, email) AS opis FROM users;

SELECT LOWER(email) AS email_lower FROM users;

SELECT UPPER(last_name) AS last_upper FROM users;

SELECT TRIM(first_name) AS first_clean FROM users;

SELECT TRIM(last_name) AS last_clean FROM users;

SELECT CHAR_LENGTH(first_name) AS len_first FROM users;

SELECT SUBSTRING(email, 1, 3) AS email_3 FROM users;

SELECT SUBSTRING(last_name, 1, 5) AS last_5 FROM users;

SELECT REPLACE(phone, ' ', '') AS phone_no_spaces FROM users;

SELECT COALESCE(nickname, first_name) AS display_name FROM users;

SELECT IFNULL(phone, 'brak') AS phone_or_brak FROM users;

SELECT
  CASE
    WHEN age IS NULL THEN 'unknown'
    WHEN age >= 18 THEN 'adult'
    ELSE 'minor'
  END AS age_type
FROM users;

SELECT IF(active = 1, 'TAK', 'NIE') AS aktywny FROM users;

SELECT CONCAT(TRIM(first_name), '.', TRIM(last_name)) AS login_like FROM users;

SELECT 'uzytkownik' AS typ, email FROM users;

SELECT 1 AS jeden, id FROM users;

SELECT price_net * (1 + vat_rate) AS price_gross FROM products;

SELECT ROUND(price_net * (1 + vat_rate), 2) AS gross_2dp FROM products;

SELECT ROUND(price_net) AS net_rounded FROM products;

SELECT CEIL(price_net) AS net_ceil FROM products;

SELECT FLOOR(price_net) AS net_floor FROM products;

SELECT CEIL(price_net) - FLOOR(price_net) AS spread FROM products;

SELECT name, price_net, ROUND(price_net * (1 + vat_rate), 2) AS price_gross FROM products;

SELECT 'produkt' AS typ, name FROM products;

SELECT CHAR_LENGTH(name) AS name_len FROM products;

SELECT SUBSTRING(name, 1, 4) AS name_4 FROM products;

SELECT balance, ABS(balance) AS balance_abs FROM accounts;

SELECT IF(balance < 0, 'minus', 'ok') AS saldo_typ FROM accounts;

SELECT ROUND(balance) AS balance_round FROM accounts;

SELECT 'konto' AS typ, balance FROM accounts;

SELECT CONCAT_WS(' | ', user_id, balance) AS opis FROM accounts;

SELECT created_at, DATE(created_at) AS data FROM orders;

SELECT created_at, TIME(created_at) AS czas FROM orders;

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS data_fmt FROM orders;

SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS dt FROM orders;

SELECT ROUND(total, 2) AS total_2dp FROM orders;

SELECT DISTINCT
  CONCAT_WS(' ', UPPER(first_name), last_name) AS full_name,
  ROUND(salary, 2) AS salary_2dp,
  IFNULL(phone, 'brak') AS phone,
  DATE_FORMAT(created_at, '%Y-%m-%d') AS created_date
FROM employees;

SELECT salary, CEIL(salary) AS salary_ceil, FLOOR(salary) AS salary_floor FROM employees;

SELECT UPPER(last_name) AS last_upper, LOWER(first_name) AS first_lower FROM employees;

SELECT CONCAT_WS(' / ', id, first_name, last_name, ROUND(salary, 2)) AS opis FROM employees;

SELECT IF(phone IS NULL, 'NIE', 'TAK') AS ma_telefon FROM employees;

SELECT (a + b) * c AS wynik FROM t;

SELECT NULLIF(status, '') AS status_null_if_empty FROM t;

SELECT IF(status = '', 'pusty', status) AS status_opis FROM t;

SELECT id, MOD(id, 2) AS mod2 FROM t;

SELECT
  CASE
    WHEN MOD(id, 2) = 0 THEN 'parzysty'
    ELSE 'nieparzysty'
  END AS parzystosc
FROM t;