Ćwiczenia SQL + PHP – Struktura i administracja bazy danych (INF.03)

1. Tworzenie tabel

Polecenie: Utwórz w bazie biblioteka tabelę ksiazki z polami: ID, Tytul, Autor, Rok.

CREATE TABLE ksiazki (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Tytul VARCHAR(100),
  Autor VARCHAR(80),
  Rok INT
);

<?php
$conn = new mysqli("localhost", "root", "haslo", "biblioteka");
$conn->query("CREATE TABLE ksiazki (ID INT AUTO_INCREMENT PRIMARY KEY, Tytul VARCHAR(100), Autor VARCHAR(80), Rok INT)");
$conn->close();
echo "Tabela 'ksiazki' została utworzona.";
?>

Utworzono tabelę ksiazki w bazie biblioteka.

2. Tworzenie formularzy do wprowadzania danych

Polecenie: Utwórz formularz PHP do dodawania książek do tabeli ksiazki.

INSERT INTO ksiazki (Tytul, Autor, Rok)
VALUES ('Pan Tadeusz', 'Adam Mickiewicz', 1834);

<?php
if($_SERVER["REQUEST_METHOD"]=="POST"){
  $conn=new mysqli("localhost","root","haslo","biblioteka");
  $t=$_POST["tytul"]; $a=$_POST["autor"]; $r=$_POST["rok"];
  $conn->query("INSERT INTO ksiazki (Tytul,Autor,Rok) VALUES ('$t','$a',$r)");
  echo "Dodano książkę!";
  $conn->close();
}
?>
<form method="POST">
Tytuł: <input name="tytul"><br>
Autor: <input name="autor"><br>
Rok: <input name="rok"><br>
<button type="submit">Dodaj</button>
</form>

Formularz umożliwia dodawanie książek do bazy danych.

3. Rodzaje kwerend w SZBD (MySQL)

Polecenie: Wykonaj przykładowe zapytania: wybierające, aktualizujące i usuwające.

-- Wybierająca
SELECT * FROM ksiazki WHERE Rok < 1900;

-- Aktualizująca
UPDATE ksiazki SET Rok = 1901 WHERE Tytul = 'Pan Tadeusz';

-- Usuwająca
DELETE FROM ksiazki WHERE Autor = 'Nieznany';

<?php
$conn=new mysqli("localhost","root","haslo","biblioteka");
$conn->query("UPDATE ksiazki SET Rok=1901 WHERE Tytul='Pan Tadeusz'");
$conn->query("DELETE FROM ksiazki WHERE Autor='Nieznany'");
$result=$conn->query("SELECT * FROM ksiazki");
while($row=$result->fetch_assoc()){
  echo $row["Tytul"]." - ".$row["Autor"]."<br>";
}
$conn->close();
?>

Przykładowe kwerendy działają poprawnie i modyfikują dane w tabeli.

4. Tworzenie zapytań do bazy danych

Polecenie: Wyświetl wszystkie książki po 1900 roku posortowane malejąco po roku.

SELECT Tytul, Autor, Rok FROM ksiazki WHERE Rok > 1900 ORDER BY Rok DESC;

<?php
$conn=new mysqli("localhost","root","haslo","biblioteka");
$sql="SELECT Tytul,Autor,Rok FROM ksiazki WHERE Rok>1900 ORDER BY Rok DESC";
$result=$conn->query($sql);
echo "<table class='effect-table'><tr><th>Tytuł</th><th>Autor</th><th>Rok</th></tr>";
while($row=$result->fetch_assoc()){
  echo "<tr><td>{$row['Tytul']}</td><td>{$row['Autor']}</td><td>{$row['Rok']}</td></tr>";
}
echo "</table>";
$conn->close();
?>

Tytuł	Autor	Rok
Quo Vadis	H. Sienkiewicz	1896
Lalka	B. Prus	1890

5. Tworzenie i formatowanie raportów

Polecenie: Wyświetl raport z liczbą książek danego autora.

SELECT Autor, COUNT(*) AS Liczba_ksiazek FROM ksiazki GROUP BY Autor;

<?php
$conn=new mysqli("localhost","root","haslo","biblioteka");
$result=$conn->query("SELECT Autor,COUNT(*) AS Liczba FROM ksiazki GROUP BY Autor");
echo "<h3>Raport liczby książek według autora</h3>";
echo "<table class='effect-table'><tr><th>Autor</th><th>Liczba</th></tr>";
while($r=$result->fetch_assoc()){
  echo "<tr><td>{$r['Autor']}</td><td>{$r['Liczba']}</td></tr>";
}
echo "</table>";
$conn->close();
?>

Raport wygenerowano – liczba książek dla każdego autora została zliczona.

6. Modyfikowanie struktury bazy danych

Polecenie: Dodaj do tabeli ksiazki nowe pole Wydawnictwo.

ALTER TABLE ksiazki ADD COLUMN Wydawnictwo VARCHAR(80);

<?php
$conn=new mysqli("localhost","root","haslo","biblioteka");
$conn->query("ALTER TABLE ksiazki ADD COLUMN Wydawnictwo VARCHAR(80)");
$conn->close();
echo "Pole 'Wydawnictwo' zostało dodane.";
?>

Struktura tabeli została zmodyfikowana – dodano kolumnę Wydawnictwo.

7. Analizowanie struktury bazy danych

Polecenie: Wyświetl strukturę tabeli ksiazki w celu analizy pól.

DESCRIBE ksiazki;

<?php
$conn=new mysqli("localhost","root","haslo","biblioteka");
$result=$conn->query("DESCRIBE ksiazki");
echo "<table class='effect-table'><tr><th>Pole</th><th>Typ</th><th>Klucz</th></tr>";
while($r=$result->fetch_assoc()){
  echo "<tr><td>{$r['Field']}</td><td>{$r['Type']}</td><td>{$r['Key']}</td></tr>";
}
echo "</table>";
$conn->close();
?>

Analiza struktury tabeli ksiazki zakończona powodzeniem.

8. Rozbudowa struktury bazy danych

Polecenie: Dodaj tabelę czytelnicy i połącz ją relacją z tabelą ksiazki.

CREATE TABLE czytelnicy (
  ID_czytelnika INT AUTO_INCREMENT PRIMARY KEY,
  Imie VARCHAR(50),
  Nazwisko VARCHAR(50)
);

ALTER TABLE ksiazki ADD COLUMN ID_czytelnika INT;
ALTER TABLE ksiazki
ADD CONSTRAINT fk_czytelnik FOREIGN KEY (ID_czytelnika)
REFERENCES czytelnicy(ID_czytelnika);

<?php
$conn=new mysqli("localhost","root","haslo","biblioteka");
$conn->query("CREATE TABLE czytelnicy (ID_czytelnika INT AUTO_INCREMENT PRIMARY KEY, Imie VARCHAR(50), Nazwisko VARCHAR(50))");
$conn->query("ALTER TABLE ksiazki ADD COLUMN ID_czytelnika INT");
$conn->query("ALTER TABLE ksiazki ADD CONSTRAINT fk_czytelnik FOREIGN KEY (ID_czytelnika) REFERENCES czytelnicy(ID_czytelnika)");
$conn->close();
echo "Dodano tabelę 'czytelnicy' i utworzono relację.";
?>

Tabele ksiazki i czytelnicy zostały połączone relacją.

9. Usuwanie elementów struktury bazy danych

Polecenie: Usuń kolumnę Wydawnictwo z tabeli ksiazki.

ALTER TABLE ksiazki DROP COLUMN Wydawnictwo;

<?php
$conn=new mysqli("localhost","root","haslo","biblioteka");
$conn->query("ALTER TABLE ksiazki DROP COLUMN Wydawnictwo");
$conn->close();
echo "Kolumna 'Wydawnictwo' została usunięta.";
?>

Kolumna Wydawnictwo została bezpiecznie usunięta z tabeli.

10. Modyfikowanie struktury i danych

Polecenie: Zmień typ pola Rok na YEAR i zwiększ wszystkie wartości o 1.

ALTER TABLE ksiazki MODIFY Rok YEAR;
UPDATE ksiazki SET Rok = Rok + 1;

<?php
$conn=new mysqli("localhost","root","haslo","biblioteka");
$conn->query("ALTER TABLE ksiazki MODIFY Rok YEAR");
$conn->query("UPDATE ksiazki SET Rok = Rok + 1");
$conn->close();
echo "Struktura i dane w tabeli zostały zmodyfikowane.";
?>

Zmieniono typ pola Rok i zaktualizowano dane o 1 rok.