Powtórzenie zagadnień z MySQL

SELECT

1. Podstawowa składnia SELECT

SELECT kolumna1, kolumna2
FROM nazwa_tabeli
WHERE warunek
ORDER BY kolumna;
  • SELECT — wybiera kolumny do wyświetlenia,
  • FROM — wskazuje tabelę, z której pobieramy dane,
  • WHERE — warunek filtrowania danych,
  • ORDER BY — sortowanie wyników.

2. INNER JOIN – łączenie tabel

SELECT a.kolumna, b.kolumna
FROM tabela_a a
INNER JOIN tabela_b b ON a.id = b.id;
  • INNER JOIN zwraca tylko te rekordy, które mają dopasowanie w obu tabelach.
  • Używany, gdy dane są rozdzielone między kilka tabel (np. zamówienia i klienci).

3. GROUP BY – grupowanie danych

SELECT kolumna_agregowana, COUNT(*)
FROM tabela
GROUP BY kolumna_agregowana;
  • Grupuje dane na podstawie wartości w kolumnie.
  • Często używane z funkcjami agregującymi.

4. Funkcje agregujące

  • SUM(kolumna) – suma wartości,
  • AVG(kolumna) – średnia,
  • COUNT(*) – liczba wierszy,
  • MAX(kolumna) – największa wartość,
  • MIN(kolumna) – najmniejsza wartość.

5. ORDER BY – sortowanie wyników

SELECT * FROM tabela
ORDER BY kolumna ASC; -- lub DESC
  • ASC – rosnąco (domyślnie),
  • DESC – malejąco,

  • RAND() – losowo, przelosowanie rekordów.

Kod tworzący tabele i dane

-- Klienci
CREATE TABLE klienci (
    id INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(50),
    status VARCHAR(20),
    data_rejestracji DATE
);

-- Produkty
CREATE TABLE produkty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100),
    kategoria VARCHAR(50),
    cena DECIMAL(10,2),
    ilosc_magazynowa INT
);

-- Zamowienia
CREATE TABLE zamowienia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_klient INT,
    wartosc_zamowienia DECIMAL(10,2),
    data_zamowienia DATE,
    FOREIGN KEY (id_klient) REFERENCES klienci(id)
);

-- Wstawianie przykładowych danych
INSERT INTO klienci (imie, nazwisko, status, data_rejestracji) VALUES
('Anna', 'Nowak', 'aktywny', '2024-01-15'),
('Tomasz', 'Kowalski', 'zawieszony', '2023-09-10'),
('Maria', 'Wojcik', 'aktywny', '2024-03-05');

INSERT INTO produkty (nazwa, kategoria, cena, ilosc_magazynowa) VALUES
('Laptop Lenovo', 'Elektronika', 4500.00, 10),
('Kawa', 'Spozywcze', 18.90, 50),
('Myszka', 'Elektronika', 89.99, 25);

INSERT INTO zamowienia (id_klient, wartosc_zamowienia, data_zamowienia) VALUES
(1, 4500.00, '2024-02-01'),
(2, 18.90, '2024-02-10'),
(1, 89.99, '2024-03-01');

Ćwiczenia praktyczne

Ćwiczenie 1

Wyświetl wszystkie dane z tabeli klienci.

Ćwiczenie 2

Wyświetl imię, nazwisko i status tylko tych klientów, którzy mają status aktywny.

Ćwiczenie 3

Wyświetl wszystkie zamówienia posortowane malejąco według wartosc_zamowienia.

Ćwiczenie 4

Wyświetl nazwisko klienta oraz wartość zamówienia, używając INNER JOIN między klienci i zamowienia.

Ćwiczenie 5

Oblicz sumę wartości wszystkich zamówień.

Ćwiczenie 6

Pogrupuj zamówienia według id_klient i oblicz łączną wartość zamówień dla każdego klienta.

Ćwiczenie 7

Policz, ilu jest klientów o każdym statusie (GROUP BY + COUNT()).

Ćwiczenie 8

Wyświetl wszystkie produkty z kategorii Elektronika, których cena jest większa niż 500 zł.

Ćwiczenie 9

Oblicz średnią wartość zamówienia z tabeli zamowienia.

Ćwiczenie 10

Wyświetl imię, nazwisko klienta oraz sumę jego zamówień, posortowane malejąco według tej sumy.

Dodatkowe ćwiczenia z egzaminu:

  • Zapytanie wybierające jedynie niepowtarzające się wartości z pola wpis dla zadań z pierwszych 7 dni lipca roku 2020 (od 1 do 7 lipca) oraz takich, gdzie pole wpis nie jest puste
  • Zapytanie liczące średnią arytmetyczną cen usług oraz liczbę usług jedynie dla usług kosmetycznych
  • Zapytanie wybierające jedynie pola cel i cena z tabeli wycieczki oraz odpowiadające im pole podpis z tabeli zdjecia, dla wycieczek, których cena jest wyższa niż 1300 zł. Zapytanie wykorzystuje relację
  • Zapytanie liczące dla wszystkich uczestników: średni koszt konferencji z nazwą kolumny (alias) „Średni koszt”, sumę kosztów z nazwą kolumny (alias) „Całkowity koszt”, liczbę zapisanych w bazie uczestników z nazwą kolumny (alias) „Liczba uczestników”
  • Zapytanie wybierające losowo dokładnie pięć rekordów z tabeli nagrody zawierających jedynie pola nazwa, opis i cena
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie

ALTER TABLE

Instrukcja ALTER TABLE pozwala na modyfikowanie istniejącej tabeli w bazie danych MySQL. Dzięki niej możesz:

  • dodawać nowe kolumny,
  • usuwać istniejące kolumny,
  • zmieniać typ danych kolumny,
  • zmieniać nazwę kolumny lub tabeli,
  • dodawać/usuwac indeksy i klucze obce,
  • ustawiać/usuwać domyślne wartości kolumn.

Najważniejsze zastosowania ALTER TABLE

1. Dodawanie kolumny

ALTER TABLE nazwa_tabeli ADD kolumna typ_danych;

2. Usuwanie kolumny

ALTER TABLE nazwa_tabeli DROP COLUMN kolumna;

3. Zmiana typu danych kolumny

ALTER TABLE nazwa_tabeli MODIFY kolumna nowy_typ;

4. Zmiana nazwy kolumny

ALTER TABLE nazwa_tabeli CHANGE stara_nazwa nowa_nazwa typ_danych;

5. Zmiana nazwy tabeli

RENAME TABLE stara_nazwa TO nowa_nazwa;

6. Dodawanie wartości domyślnej

ALTER TABLE nazwa_tabeli ALTER kolumna SET DEFAULT 'wartosc';

7. Usuwanie wartości domyślnej

ALTER TABLE nazwa_tabeli ALTER kolumna DROP DEFAULT;

8. Dodawanie indeksu

ALTER TABLE nazwa_tabeli ADD INDEX (kolumna);

9. Dodawanie klucza obcego

ALTER TABLE zamowienia ADD CONSTRAINT fk_klient_id FOREIGN KEY (klient_id) REFERENCES klienci(id);

Kod do utworzenia tabeli przykładowej

CREATE TABLE pracownicy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(50),
    dzial VARCHAR(50),
    zatrudniony DATE
);

Ćwiczenia praktyczne z ALTER TABLE

Ćwiczenie 1

Dodaj nową kolumnę email typu VARCHAR(100) do tabeli pracownicy.

Ćwiczenie 2

Zmień typ kolumny dzial na VARCHAR(100).

Ćwiczenie 3

Zmień nazwę kolumny zatrudniony na data_zatrudnienia.

Ćwiczenie 4

Usuń kolumnę email z tabeli.

Ćwiczenie 5

Dodaj nową kolumnę pensja typu DECIMAL(10,2) z domyślną wartością .

Ćwiczenie 6

Ustaw wartość domyślną brak dla kolumny dzial.

Ćwiczenie 7

Usuń wartość domyślną z kolumny dzial.

Ćwiczenie 8

Zmień nazwę tabeli pracownicy na zatrudnieni.

Ćwiczenie 9

Dodaj indeks na kolumnie nazwisko.

Ćwiczenie 10

Przywróć nazwę tabeli zatrudnieni do pierwotnej pracownicy.

Dodatkowe ćwiczenia z egzaminu:

  • Zapytanie usuwające tabelę uzytkownik
  • Zapytanie dodające do tabeli wycieczki pole liczbaDni przyjmujące tylko liczby naturalne, pole powinno być wstawione po polu dataWyjazdu
  • Zapytanie dodające do tabeli Ryby kolumnę dobowy_limit typu numerycznego, o rozmiarze pozwalającym na wpisanie jedynie liczb z przedziału
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie

  • Zapytanie

INSERT INTO

Polecenie INSERT INTO służy do wstawiania nowych danych do istniejącej tabeli. To jedna z najczęściej używanych instrukcji w SQL.

Podstawowa składnia

Wstawienie danych do wszystkich kolumn (w tej samej kolejności jak w tabeli):

INSERT INTO nazwa_tabeli VALUES (wartosc1, wartosc2, ...);

Wstawienie danych tylko do wybranych kolumn:

INSERT INTO nazwa_tabeli (kolumna1, kolumna2) VALUES (wartosc1, wartosc2);

Wstawienie wielu wierszy na raz:

INSERT INTO nazwa_tabeli (kolumna1, kolumna2) 
VALUES 
    (wartosc1a, wartosc2a),
    (wartosc1b, wartosc2b);

Uwagi praktyczne

  • Nie trzeba podawać kolumny AUTO_INCREMENT, np. id, jeśli nie chcemy przypisywać jej ręcznie.
  • Można łączyć INSERT INTO z innymi poleceniami, np. SELECT, ale tego nie omawiamy w tej lekcji.

Kod tworzący tabelę do ćwiczeń

CREATE TABLE produkty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100),
    kategoria VARCHAR(50),
    cena DECIMAL(10,2),
    ilosc INT
);

Ćwiczenia praktyczne z INSERT INTO

Ćwiczenie 1

Wstaw do tabeli produkty nowy produkt: "Laptop", "Elektronika", 4500.00, 10.

Ćwiczenie 2

Wstaw dwa produkty jednocześnie: "Kawa", "Spozywcze", 18.90, 50 oraz "Myszka", "Elektronika", 89.99, 25.

Ćwiczenie 3

Wstaw produkt "Długopis" tylko z kolumnami nazwa, kategoria. Pozostałe kolumny pozostaw puste (NULL lub domyślne).

Ćwiczenie 4

Wstaw produkt "Krzeslo" z kategorią "Meble", ceną 120.00 i ilością 30.

Ćwiczenie 5

Dodaj produkt "Monitor", "Elektronika", 999.99, 15.

Ćwiczenie 6

Wstaw produkt "Sok jabłkowy", "Spozywcze", 4.50, 100.

Ćwiczenie 7

Dodaj produkt z niestandardową nazwą: "Telefon \"Galaxy\"" i kategorią "Elektronika".

Ćwiczenie 8

Spróbuj wstawić produkt z literówką w nazwie kolumny (np. cnena zamiast cena). Zapisz i omów komunikat błędu.

Ćwiczenie 9

Wstaw pięć produktów naraz z różnych kategorii.

Ćwiczenie 10

Użyj INSERT INTO z podaniem tylko nazwa, ilosc, np. "Kabel USB", 100. Pozostałe wartości pomiń.

Dodatkowe ćwiczenia z egzaminu:

  • Zapytanie wstawiające do tabeli wagi rekord z danymi: lokalizacje_id: 5, waga: losowa liczba z przedziału 1..10 (wygenerowana funkcją), rejestracja: DW12345, aktualna data (do pola dzien) i aktualny czas (do pola czas). Klucz główny nadawany automatycznie. Zapytanie ma charakter uniwersalny, zawsze wstawia wartość aktualnej daty i czasu
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie

  • Zapytanie

UPDATE

Polecenie UPDATE służy do modyfikowania istniejących danych w tabeli. Pozwala zmieniać wartości w jednym lub wielu rekordach zgodnie z określonym warunkiem.

Podstawowa składnia

UPDATE nazwa_tabeli
SET kolumna1 = nowa_wartosc1, kolumna2 = nowa_wartosc2
WHERE warunek;
  • SET — określamy, które kolumny mają być zaktualizowane.
  • WHERE — warunek, który wskazuje, które rekordy mają zostać zmodyfikowane. Bez WHERE zmienią się wszystkie rekordy.

Uwagi praktyczne

  • Zawsze używaj WHERE, chyba że świadomie chcesz zmienić wszystkie rekordy.
  • Można aktualizować wiele kolumn naraz.
  • Można używać operatorów (+, -, *, /) do obliczeń w SET.

Kod tworzący tabelę do ćwiczeń

CREATE TABLE produkty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100),
    kategoria VARCHAR(50),
    cena DECIMAL(10,2),
    ilosc INT
);

INSERT INTO produkty (nazwa, kategoria, cena, ilosc) VALUES
('Laptop', 'Elektronika', 4500.00, 10),
('Kawa', 'Spozywcze', 18.90, 50),
('Myszka', 'Elektronika', 89.99, 25),
('Dlugopis', 'Biuro', 2.50, 100),
('Krzeslo', 'Meble', 120.00, 30),
('Monitor', 'Elektronika', 999.99, 15),
('Sok jablkowy', 'Spozywcze', 4.50, 100),
('Telefon Galaxy', 'Elektronika', 3499.00, 8),
('Kabel USB', 'Elektronika', 12.00, 100),
('Notes', 'Biuro', 5.00, 60);

Ćwiczenia praktyczne z UPDATE

Ćwiczenie 1

Zmień cenę produktu "Myszka" na 79.99.

Ćwiczenie 2

Zwiększ ilość Laptop o 5 sztuk.

Ćwiczenie 3

Zmień kategorię wszystkich produktów "Spozywcze" na "Artykuly spozywcze".

Ćwiczenie 4

Zmień nazwę "Telefon Galaxy" na "Smartfon Galaxy".

Ćwiczenie 5

Ustaw cenę wszystkich produktów z kategorii "Biuro" na 6.00.

Ćwiczenie 6

Obniż cenę "Monitor" o 10%.

Ćwiczenie 7

Zmień nazwę "Sok jablkowy" na "Sok 100% jablkowy" i ustaw jego ilość na 80.

Ćwiczenie 8

Zwiększ ilość wszystkich produktów o 10, ale tylko tych, które mają mniej niż 50 sztuk.

Ćwiczenie 9

Ustaw cenę 0.00 dla wszystkich produktów z ceną poniżej 5.00.

Ćwiczenie 10

Dla wszystkich produktów z kategorii "Elektronika", zwiększ cenę o 50.

Dodatkowe ćwiczenia z egzaminu:

  • Zapytanie aktualizujące tabelę wagi. Rekordy, w których pole lokalizacje_id jest równe: 2, 3, 4 mają zmienione datę i czas na wartości aktualne. Zapytanie ma charakter uniwersalny, zawsze zmienia wartość na aktualną datę / czas
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie
  • Zapytanie

Oto lekcja powtórzeniowa z SELECT w MySQL, uwzględniająca:

  • podstawy SELECT,
  • INNER JOIN,
  • GROUP BY,
  • funkcje agregujące (SUM(), AVG(), COUNT(), MAX(), MIN()),
  • sortowanie danych (ORDER BY),
  • kod tworzący tabele i dane,
  • 10 ćwiczeń praktycznych.

Użytkownicy i uprawnienia

1. Tworzenie użytkownika

Aby dodać nowego użytkownika:

CREATE USER 'nazwa_uzytkownika'@'localhost' IDENTIFIED BY 'haslo';

Przykład:

CREATE USER 'student'@'localhost' IDENTIFIED BY 'Test123!';

2. Usuwanie użytkownika

Usunięcie użytkownika z bazy danych:

DROP USER 'nazwa_uzytkownika'@'localhost';

Przykład:

DROP USER 'student'@'localhost';

3. Nadawanie uprawnień

Użytkownikowi można nadać pełne lub częściowe uprawnienia do bazy lub tabeli:

GRANT SELECT, INSERT ON nazwa_bazy.nazwa_tabeli TO 'uzytkownik'@'localhost';

Przykład:

GRANT SELECT, INSERT ON sklep.klienci TO 'student'@'localhost';

Nadanie wszystkich uprawnień do całej bazy:

GRANT ALL PRIVILEGES ON sklep.* TO 'admin'@'localhost';

4. Odbieranie uprawnień

REVOKE INSERT ON sklep.klienci FROM 'student'@'localhost';

Można też odebrać wszystkie uprawnienia:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'student'@'localhost';

5. Zastosowanie zmian

W niektórych wersjach MySQL należy odświeżyć uprawnienia:

FLUSH PRIVILEGES;

Ćwiczenia praktyczne

Ćwiczenie 1:
Utwórz użytkownika nauczyciel z hasłem MocneHaslo123.

Ćwiczenie 2:
Utwórz użytkownika gosc z hasłem WitajGosciu, który może się łączyć tylko z localhosta.

Ćwiczenie 3:
Nadaj użytkownikowi nauczyciel pełne uprawnienia do bazy sklep.

Ćwiczenie 4:
Nadaj użytkownikowi gosc tylko możliwość SELECT na tabeli produkty z bazy sklep.

Ćwiczenie 5:
Sprawdź, jakie uprawnienia ma użytkownik gosc. (Użyj zapytania do INFORMATION_SCHEMA)

Ćwiczenie 6:
Odbierz użytkownikowi gosc prawo do SELECT z tabeli produkty.

Ćwiczenie 7:
Utwórz użytkownika magazynier z hasłem Magazyn123, który ma prawo SELECT, INSERT i UPDATE do tabeli produkty.

Ćwiczenie 8:
Zmień hasło użytkownika nauczyciel na NoweHaslo2025.

Ćwiczenie 9:
Nadaj użytkownikowi magazynier uprawnienie do usuwania (DELETE) danych z tabeli produkty.

Ćwiczenie 10:
Usuń użytkownika gosc z serwera MySQL.

Oto lekcja powtórzeniowa na temat widoków (VIEW) w MySQL, z częścią teoretyczną, przykładami i 10 ćwiczeniami. Przygotowana z myślą o uczniach, którzy znają podstawy SQL i pracują na bazie typu „sklep” (np. klienci, produkty, zamowienia).

Widoki (VIEW)

1. Co to jest widok (ang. VIEW)

Widok to zapisane zapytanie SELECT, które można traktować jak wirtualną tabelę. Widoki służą do:

  • uproszczenia skomplikowanych zapytań,
  • ograniczenia dostępu do danych (np. ukrycia niektórych kolumn),
  • zwiększenia przejrzystości kodu.

2. Tworzenie widoku

CREATE VIEW nazwa_widoku AS
SELECT kolumny
FROM tabela
WHERE warunki;

Przykład:

CREATE VIEW aktywni_klienci AS
SELECT imie, nazwisko, data_rejestracji
FROM klienci
WHERE status = 'aktywny';

3. Przeglądanie widoku

Widok działa jak tabela, więc możemy:

SELECT * FROM aktywni_klienci;

4. Usuwanie widoku

DROP VIEW aktywni_klienci;

5. Modyfikowanie widoku

MySQL nie obsługuje ALTER VIEW w każdej wersji, dlatego modyfikuje się widok przez ponowne jego stworzenie:

CREATE OR REPLACE VIEW aktywni_klienci AS
SELECT imie, nazwisko
FROM klienci
WHERE status = 'aktywny';

Ćwiczenia praktyczne

Ćwiczenie 1:
Utwórz widok drogie_produkty, który zawiera wszystkie produkty o cenie powyżej 1000.

Ćwiczenie 2:
Utwórz widok produkty_magazyn, który pokazuje nazwa, kategoria i ilosc_magazynowa tylko dla produktów z ilością większą niż 10.

Ćwiczenie 3:
Utwórz widok klienci_data, który zawiera imie, nazwisko i data_rejestracji klientów zarejestrowanych po 1 stycznia 2024.

Ćwiczenie 4:
Utwórz widok zamowienia_klientow, który łączy tabele klienci i zamowienia i pokazuje: imie, nazwisko, data_zamowienia, wartosc_zamowienia.

Ćwiczenie 5:
Utwórz widok elektronika, który zawiera wszystkie produkty z kategorii 'Elektronika’.

Ćwiczenie 6:
Utwórz widok sumy_zamowien, który grupuje zamówienia po kliencie i pokazuje id_klient oraz sumę wszystkich jego zamówień.

Ćwiczenie 7:
Stwórz widok nowi_klienci, który zawiera klientów zarejestrowanych w ciągu ostatnich 30 dni (użyj CURDATE()).

Ćwiczenie 8:
Zmień widok drogie_produkty, aby zawierał tylko produkty powyżej 2000 zł (użyj CREATE OR REPLACE).

Ćwiczenie 9:
Usuń widok produkty_magazyn.

Ćwiczenie 10:
Utwórz widok klienci_zamowienia, który pokazuje tylko tych klientów, którzy złożyli co najmniej jedno zamówienie (użyj INNER JOIN).