SQL

Składnia poleceń w języku SQL (DDL, DML, DCL)

Podstawowe instrukcje, złożone skrypty, ćwiczenia – INF.03

Teoria Instrukcje SQL — DDL, DML, DCL

DDL (Data Definition Language) — polecenia tworzące i modyfikujące strukturę bazy: CREATE, ALTER, DROP
DML (Data Manipulation Language) — polecenia do zarządzania danymi: SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language) — polecenia do nadawania uprawnień: GRANT, REVOKE

SQL pozwala tworzyć, modyfikować i zarządzać bazami danych niezależnie od konkretnego SZBD.

Ćwiczenie 1 Polecenia DDL — struktura bazy

Zadanie: Utwórz bazę firmadb oraz tabele pracownicy i projekty.

Utwórz bazę danych firmadb i ją wybierz.
Dodaj tabelę pracownicy z kluczem głównym i kolumnami tekstowymi.
Dodaj tabelę projekty z kluczem głównym i kluczem obcym do pracownicy.id.

CREATE DATABASE firmadb;
USE firmadb;

CREATE TABLE pracownicy (
  id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(50),
  nazwisko VARCHAR(80),
  stanowisko VARCHAR(50)
);

CREATE TABLE projekty (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(100),
  lider_id INT,
  FOREIGN KEY (lider_id) REFERENCES pracownicy(id)
);

Ćwiczenie 2 Polecenia DML — operacje na danych

Zadanie: Wstaw pracowników, wyświetl ich listę, zaktualizuj stanowisko, usuń wybrany rekord.

Dodaj co najmniej dwóch pracowników.
Wyświetl wszystkich pracowników posortowanych po nazwisku.
Zmień stanowisko jednego z pracowników.
Usuń wskazanego pracownika po identyfikatorze.

INSERT INTO pracownicy (imie, nazwisko, stanowisko) VALUES
('Jan', 'Kowalski', 'Programista'),
('Anna', 'Nowak', 'Analityk');

SELECT * FROM pracownicy
ORDER BY nazwisko;

UPDATE pracownicy
SET stanowisko = 'Tester'
WHERE imie = 'Jan' AND nazwisko = 'Kowalski';

DELETE FROM pracownicy
WHERE id = 2;

Ćwiczenie 3 Polecenia DCL — uprawnienia i bezpieczeństwo

Zadanie: Utwórz użytkownika tester z uprawnieniem tylko do odczytu.

Stwórz nowego użytkownika bazy danych z hasłem.
Nadaj mu prawo SELECT do wszystkich tabel w bazie firmadb.
Odśwież uprawnienia serwera.

CREATE USER 'tester'@'localhost' IDENTIFIED BY 'silneHaslo!';

GRANT SELECT ON firmadb.* TO 'tester'@'localhost';

FLUSH PRIVILEGES;

Ćwiczenie 4 Złożony skrypt DDL/DML — integracja poleceń i JOIN

Zadanie: Stwórz nową tabelę zadania, dodaj przykładowe rekordy i połącz tabele przez JOIN.

Dodaj tabelę zadania z kluczami obcymi do projekty i pracownicy.
Wstaw co najmniej dwa zadania przypisane do istniejących projektów i pracowników.
Wyświetl listę zadań z imieniem pracownika i nazwą projektu przy użyciu JOIN.

CREATE TABLE zadania (
  id INT PRIMARY KEY AUTO_INCREMENT,
  opis VARCHAR(200),
  projekt_id INT,
  pracownik_id INT,
  FOREIGN KEY (projekt_id) REFERENCES projekty(id),
  FOREIGN KEY (pracownik_id) REFERENCES pracownicy(id)
);

INSERT INTO zadania (opis, projekt_id, pracownik_id) VALUES
('Analiza wymagań', 1, 2),
('Testy jednostkowe', 1, 1);

SELECT zadania.opis,
       pracownicy.imie,
       pracownicy.nazwisko,
       projekty.nazwa
FROM zadania
JOIN pracownicy ON zadania.pracownik_id = pracownicy.id
JOIN projekty   ON zadania.projekt_id   = projekty.id;

Zadanie INF.03 Mini-arkusz egzaminacyjny

Opis sytuacji: W bazie szkola znajdują się tabele uczniowie(id, imie, nazwisko, klasa) oraz oceny(id, uczen_id, przedmiot, ocena).

Wykonaj poniższe polecenia SQL:

Dodaj ucznia „Piotr Zieliński” do klasy „4TI”.
Wstaw dla niego dwie oceny z przedmiotu „INF.03”: 5 i 4.
Wyświetl średnią ocen tego ucznia z INF.03 (kolumna „srednia”).
Zmień jego klasę na „4TIP”.
Usuń wszystkie jego oceny z INF.03 (pozostawiając dane ucznia).

-- 1. Dodanie ucznia
INSERT INTO uczniowie (imie, nazwisko, klasa)
VALUES ('Piotr', 'Zieliński', '4TI');

-- (przyjmijmy, że nowy uczeń otrzymał id = 10)
-- 2. Dodanie ocen
INSERT INTO oceny (uczen_id, przedmiot, ocena) VALUES
(10, 'INF.03', 5),
(10, 'INF.03', 4);

-- 3. Średnia ocen z INF.03
SELECT u.imie, u.nazwisko,
       AVG(o.ocena) AS srednia
FROM uczniowie u
JOIN oceny o ON u.id = o.uczen_id
WHERE u.id = 10 AND o.przedmiot = 'INF.03';

-- 4. Zmiana klasy
UPDATE uczniowie
SET klasa = '4TIP'
WHERE id = 10;

-- 5. Usunięcie ocen z INF.03
DELETE FROM oceny
WHERE uczen_id = 10 AND przedmiot = 'INF.03';

Quiz Sprawdź wiedzę!

Do czego służą polecenia DDL, DML, DCL?
Które polecenie utworzy nową tabelę?
Jak wstawić rekord do tabeli z trzema kolumnami?
Jak nadać użytkownikowi prawo do odczytu danych z określonej bazy?
Jak połączyć dane z kilku tabel, aby wyświetlić powiązane informacje?

DDL – definicja struktury (bazy, tabele), DML – operacje na danych, DCL – uprawnienia użytkowników.
CREATE TABLE ...
INSERT INTO tabela (kol1, kol2, kol3) VALUES (..., ..., ...);
GRANT SELECT ON baza.* TO 'uzytkownik'@'localhost';
Przez JOIN w klauzuli FROM ... JOIN ... ON ....

Checklista Sprawdź umiejętności

Rozróżniam i opisuję polecenia DDL, DML, DCL.
Tworzę bazy danych, tabele i relacje klucz główny/obcy.
Dodaję, modyfikuję i usuwam dane przy użyciu INSERT, UPDATE, DELETE.
Tworzę zapytania z JOIN łączące dane z wielu tabel.
Nadaję podstawowe uprawnienia przy pomocy GRANT i aktualizuję je.