Ćwiczenia SQL i PHP (INF.03)
1. Składnia poleceń SQL
Polecenie: Napisz zapytanie wybierające imię i nazwisko uczniów z klasy 5INF.
Pokaż kod SQL
SELECT imie, nazwisko
FROM uczniowie
WHERE klasa = '5INF';
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "szkola");
$sql = "SELECT imie, nazwisko FROM uczniowie WHERE klasa = '5INF'";
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
echo $row["imie"] . " " . $row["nazwisko"] . "<br>";
}
$conn->close();
?>
Efekt:
imie nazwisko Anna Kowalska Piotr Nowak Kasia Wiśniewska
2. Tworzenie struktury bazy danych
Polecenie: Utwórz bazę danych szkola i tabelę uczniowie , tak jak w poleceniu.
Pokaż kod SQL
CREATE DATABASE szkola;
USE szkola;
CREATE TABLE uczniowie (
id_ucznia INT PRIMARY KEY AUTO_INCREMENT,
imie VARCHAR(30),
nazwisko VARCHAR(50),
klasa VARCHAR(10),
srednia_ocen DECIMAL(3,2)
);
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "");
if ($conn->query("CREATE DATABASE szkola") === TRUE) {
echo "Baza szkola została utworzona!<br>";
}
$conn->select_db("szkola");
$sql = "CREATE TABLE uczniowie (
id_ucznia INT PRIMARY KEY AUTO_INCREMENT,
imie VARCHAR(30),
nazwisko VARCHAR(50),
klasa VARCHAR(10),
srednia_ocen DECIMAL(3,2)
)";
if ($conn->query($sql) === TRUE) {
echo "Tabela uczniowie została utworzona!";
}
$conn->close();
?>
Baza danych: szkolaStruktura tabeli uczniowie:
3. Wyszukiwanie w bazie danych
Polecenie: Zwróć nazwiska uczniów z klasy 5INF o średniej ocen powyżej 4.0.
Pokaż kod SQL
SELECT nazwisko
FROM uczniowie
WHERE klasa = '5INF' AND srednia_ocen > 4.0;
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "szkola");
$sql = "SELECT nazwisko FROM uczniowie WHERE klasa = '5INF' AND srednia_ocen > 4.0";
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
echo $row["nazwisko"] . "<br>";
}
$conn->close();
?>
Efekt:
nazwisko Kowalska Wiśniewska
4. Aktualizacja bazy danych
Polecenie: Zmień średnią ocen ucznia „Nowak” na 4.8.
Pokaż kod SQL
UPDATE uczniowie
SET srednia_ocen = 4.8
WHERE nazwisko = 'Nowak';
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "szkola");
$sql = "UPDATE uczniowie SET srednia_ocen = 4.8 WHERE nazwisko = 'Nowak'";
$conn->query($sql);
$conn->close();
echo "Zmieniono średnią ucznia Nowak!";
?>
Efekt działania:
nazwisko srednia_ocen Nowak 4.8
5. Tworzenie skryptów SQL
Polecenie: Utwórz w bazie tabelę nauczyciele oraz przedmioty.
Pokaż kod SQL
CREATE TABLE nauczyciele (
id_nauczyciela INT PRIMARY KEY AUTO_INCREMENT,
imie VARCHAR(30),
nazwisko VARCHAR(50)
);
CREATE TABLE przedmioty (
id_przedmiotu INT PRIMARY KEY AUTO_INCREMENT,
nazwa VARCHAR(30)
);
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "szkola");
if ($conn->query("CREATE TABLE nauczyciele (id_nauczyciela INT PRIMARY KEY AUTO_INCREMENT, imie VARCHAR(30), nazwisko VARCHAR(50))") === TRUE) {
echo "Tabela nauczyciele została utworzona!<br>";
}
if ($conn->query("CREATE TABLE przedmioty (id_przedmiotu INT PRIMARY KEY AUTO_INCREMENT, nazwa VARCHAR(30))") === TRUE) {
echo "Tabela przedmioty została utworzona!";
}
$conn->close();
?>
Tabele: nauczyciele (id_nauczyciela, imie, nazwisko),
6. Projektowanie relacyjnych baz danych
Polecenie: Utwórz tabelę oceny z kluczami obcymi do uczniów i przedmiotów.
Pokaż kod SQL
CREATE TABLE oceny (
id_oceny INT PRIMARY KEY AUTO_INCREMENT,
id_ucznia INT,
id_przedmiotu INT,
ocena DECIMAL(2,1),
data_wystawienia DATE,
FOREIGN KEY (id_ucznia) REFERENCES uczniowie(id_ucznia),
FOREIGN KEY (id_przedmiotu) REFERENCES przedmioty(id_przedmiotu)
);
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "szkola");
$sql = "
CREATE TABLE oceny (
id_oceny INT PRIMARY KEY AUTO_INCREMENT,
id_ucznia INT,
id_przedmiotu INT,
ocena DECIMAL(2,1),
data_wystawienia DATE,
FOREIGN KEY (id_ucznia) REFERENCES uczniowie(id_ucznia),
FOREIGN KEY (id_przedmiotu) REFERENCES przedmioty(id_przedmiotu)
)";
if ($conn->query($sql) === TRUE) {
echo "Tabela oceny została utworzona!";
}
$conn->close();
?>
Tabela oceny: id_oceny | id_ucznia (FK) | id_przedmiotu (FK) | ocena | data_wystawienia
7. Definiowanie typów danych
Polecenie: Określ odpowiednie typy danych dla kolumn w tabeli oceny.
Pokaż kod SQL
id_oceny INT PRIMARY KEY AUTO_INCREMENT,
id_ucznia INT,
id_przedmiotu INT,
ocena DECIMAL(2,1),
data_wystawienia DATE
Pokaż kod PHP
<?php
echo "Typy danych w tabeli oceny to: INT, INT, INT, DECIMAL(2,1), DATE";
?>
Typy danych: INT, INT, INT, DECIMAL(2,1), DATE
8. Wprowadzanie danych do bazy
Polecenie: Dodaj nowego ucznia do tabeli uczniowie.
Pokaż kod SQL
INSERT INTO uczniowie (imie, nazwisko, klasa, srednia_ocen)
VALUES ('Anna', 'Kowalska', '5INF', 4.5);
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "szkola");
$sql = "INSERT INTO uczniowie (imie, nazwisko, klasa, srednia_ocen) VALUES ('Anna', 'Kowalska', '5INF', 4.5)";
$conn->query($sql);
$conn->close();
echo "Uczeń został dodany!";
?>
imie nazwisko klasa srednia_ocen Anna Kowalska 5INF 4.5
9. Skrypt automatyzujący tworzenie bazy
Polecenie: Przygotuj skrypt tworzący bazę szkola i najważniejsze tabele.
Pokaż kod SQL
CREATE DATABASE szkola;
USE szkola;
CREATE TABLE uczniowie (...);
CREATE TABLE nauczyciele (...);
CREATE TABLE przedmioty (...);
CREATE TABLE oceny (...);
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "");
$sql = "
CREATE DATABASE szkola;
USE szkola;
CREATE TABLE uczniowie (...);
CREATE TABLE nauczyciele (...);
CREATE TABLE przedmioty (...);
CREATE TABLE oceny (...);";
if ($conn->multi_query($sql)) {
echo "Utworzono cały schemat bazy!";
}
$conn->close();
?>
Baza szkola utworzona. Utworzono wszystkie potrzebne tabele.
10. Importowanie i eksportowanie danych
Polecenie: Zaimportuj dane z pliku CSV oraz wyeksportuj dane do pliku tekstowego.
Pokaż kod SQL
-- Import:
LOAD DATA INFILE 'uczniowie.csv'
INTO TABLE uczniowie
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
-- Eksport:
SELECT * FROM uczniowie
INTO OUTFILE 'uczniowie_export.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
Pokaż kod PHP
<?php
$conn = new mysqli("localhost", "user", "password", "szkola");
// Import
$conn->query("LOAD DATA INFILE 'uczniowie.csv' INTO TABLE uczniowie FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES");
// Eksport
$conn->query("SELECT * FROM uczniowie INTO OUTFILE 'uczniowie_export.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'");
$conn->close();
echo "Import i eksport danych zakończony.";
?>
Zaimpotowano dane z pliku uczniowie.csv .uczniowie_export.csv