Zajęcia -Tomasz Sadowski

1. Wprowadzenie

W poprzednich lekcjach nauczyliśmy się tworzyć tabele za pomocą CREATE TABLE. Jednak bazy danych często wymagają modyfikacji po ich utworzeniu. Może się okazać, że:

Trzeba dodać nową kolumnę (np. numer telefonu w tabeli klientów).
Konieczna jest zmiana typu danych w istniejącej kolumnie.
Należy usunąć zbędną kolumnę.
Potrzebne jest dodanie klucza obcego lub indeksu.

Do tego służy ALTER TABLE – polecenie SQL, które pozwala na zmiany w już istniejących tabelach.

2. Podstawowe operacje ALTER TABLE w MySQL

Dodawanie nowej kolumny – ADD COLUMN

Aby dodać nową kolumnę do istniejącej tabeli, używamy:

sql ALTER TABLE klienci ADD COLUMN telefon VARCHAR(15);

Kolumna telefon zostanie dodana na końcu tabeli. Możemy też określić jej dokładne miejsce:

sql ALTER TABLE klienci ADD COLUMN wiek INT AFTER nazwisko;

Dzięki temu kolumna wiek pojawi się po nazwisko.

Jeśli chcemy, aby nowa kolumna była pierwsza w tabeli:

sql ALTER TABLE klienci ADD COLUMN kod_pocztowy VARCHAR(10) FIRST;

Zmiana typu danych w kolumnie – MODIFY COLUMN

Załóżmy, że pole telefon powinno mieć większą długość. Możemy to zmienić tak:

sql ALTER TABLE klienci MODIFY COLUMN telefon VARCHAR(20);

Ważne: MODIFY COLUMN działa w MySQL, ale nie jest obsługiwane w niektórych innych systemach baz danych, np. w SQL Server.

Zmiana nazwy kolumny i jej typu – CHANGE COLUMN

Jeśli chcemy zmienić zarówno nazwę kolumny, jak i jej typ danych, używamy CHANGE COLUMN:

sql ALTER TABLE klienci CHANGE COLUMN telefon numer_telefonu VARCHAR(25);

W ten sposób kolumna telefon zmieni nazwę na numer_telefonu, a jej maksymalna długość wzrośnie do 25 znaków.

Usuwanie kolumny – DROP COLUMN

Jeśli jakaś kolumna jest zbędna, możemy ją usunąć:

sql ALTER TABLE klienci DROP COLUMN wiek;

Uwaga! Usunięcie kolumny trwale kasuje jej zawartość – tej operacji nie można cofnąć.

Dodawanie kluczy i indeksów

Czasem chcemy dodać klucz obcy do już istniejącej tabeli:

sql ALTER TABLE zamowienia ADD CONSTRAINT fk_klient FOREIGN KEY (id_klienta) REFERENCES klienci(id);

Możemy także dodać indeks do często wyszukiwanej kolumny:

sql ALTER TABLE klienci ADD INDEX idx_email (email);

Jeśli chcemy oznaczyć kolumnę jako unikalną:

sql ALTER TABLE klienci ADD UNIQUE (email);

Zmiana nazwy tabeli – RENAME TO

Jeśli chcemy zmienić nazwę tabeli klienci na uzytkownicy, robimy to tak:

sql ALTER TABLE klienci RENAME TO uzytkownicy;

Usuwanie klucza obcego – DROP FOREIGN KEY

Aby usunąć klucz obcy, musimy najpierw sprawdzić jego nazwę:

sql SHOW CREATE TABLE zamowienia;

Następnie usuwamy go (załóżmy, że jego nazwa to fk_klient):

sql ALTER TABLE zamowienia DROP FOREIGN KEY fk_klient;

Ważne: W MySQL usunięcie klucza obcego nie usuwa automatycznie indeksu, więc często trzeba wykonać dodatkowe polecenie:

sql ALTER TABLE zamowienia DROP INDEX fk_klient;

Dodawanie wartości domyślnej – ALTER COLUMN SET DEFAULT

Jeśli chcemy, aby pole status_konta w tabeli klienci miało domyślną wartość „Aktywny”, robimy to tak:

sql ALTER TABLE klienci ALTER COLUMN status_konta SET DEFAULT 'Aktywny';

Aby usunąć wartość domyślną:

sql ALTER TABLE klienci ALTER COLUMN status_konta DROP DEFAULT;

3. Ćwiczenia

Ćwiczenie 1: Dodanie nowej kolumny
Dodaj do tabeli klienci nową kolumnę data_rejestracji przechowującą datę.

Ćwiczenie 2: Modyfikacja typu danych
Zmień typ danych w kolumnie telefon w tabeli klienci, aby miała długość 25 znaków.

Ćwiczenie 3: Zmiana nazwy kolumny
W tabeli zamowienia zmień nazwę kolumny produkt na nazwa_produktu, zachowując jej typ danych.

Ćwiczenie 4: Usunięcie kolumny
Usuń z tabeli pracownicy kolumnę wiek.

Ćwiczenie 5: Dodanie klucza obcego w kilku etapach

Dodaj do tabeli zamowienia nową kolumnę id_klienta typu INT, ale jeszcze nie ustawiaj jej jako klucza obcego.
Ustaw kolumnę id_klienta tak, aby nie przyjmowała wartości NULL.
Dodaj indeks do kolumny id_klienta, aby przyspieszyć operacje wyszukiwania.
Dodaj klucz obcy id_klienta, który będzie odnosił się do id w tabeli klienci.
Sprawdź poprawność powiązania klucza obcego, wykonując zapytanie SHOW CREATE TABLE zamowienia;.

Ćwiczenie 6: Zmiana nazwy tabeli w kilku etapach

Sprawdź, czy w bazie istnieje tabela klienci, używając zapytania SHOW TABLES;.
Zmień nazwę tabeli klienci na uzytkownicy.
Zweryfikuj, czy zmiana się powiodła, ponownie używając SHOW TABLES;.
Wykonaj zapytanie DESC uzytkownicy;, aby sprawdzić strukturę tabeli po zmianie nazwy.
Spróbuj wykonać zapytanie SELECT * FROM klienci; – powinno zwrócić błąd, ponieważ tabela klienci już nie istnieje.

Ćwiczenie 7: Dodanie indeksu i modyfikacja kolumny w kilku etapach

Dodaj indeks do kolumny email w tabeli klienci, aby przyspieszyć wyszukiwanie.
Zmień nazwę kolumny email na adres_email, ale nie zmieniaj jej typu danych.
Zmień typ danych kolumny adres_email na VARCHAR(100), jeśli jej długość była mniejsza.
Sprawdź strukturę tabeli, używając zapytania DESC klienci;, aby upewnić się, że zmiany zostały zastosowane.
Spróbuj wyszukać użytkownika po nowej nazwie kolumny adres_email, np. SELECT * FROM klienci WHERE adres_email LIKE '%@gmail.com%';.

Ćwiczenie 8: phpMyAdmin – ALTER TABLE w edytorze graficznym

Otwórz tabelę klienci w phpMyAdmin.
Dodaj nową kolumnę status_konta (VARCHAR(20), domyślnie „Aktywny”).
Zmień nazwę kolumny email na adres_email.
Usuń kolumnę telefon.
Dodaj klucz obcy id_klienta w zamowienia, wskazujący na id w klienci.

4. Podsumowanie

Dzięki ALTER TABLE możemy modyfikować istniejące tabele bez konieczności ich usuwania i ponownego tworzenia. Pozwala to na dużą elastyczność i łatwiejsze zarządzanie bazą danych.

Najważniejsze operacje ALTER TABLE to:

ADD COLUMN – dodawanie nowej kolumny
MODIFY COLUMN – zmiana typu danych kolumny
CHANGE COLUMN – zmiana nazwy i typu kolumny
DROP COLUMN – usuwanie kolumny
ADD CONSTRAINT – dodawanie kluczy obcych i indeksów
DROP FOREIGN KEY – usuwanie klucza obcego
RENAME TO – zmiana nazwy tabeli
SET DEFAULT / DROP DEFAULT – ustawianie i usuwanie wartości domyślnej

Ćwiczenia pozwoliły Ci utrwalić te zagadnienia zarówno w SQL, jak i w edytorze graficznym phpMyAdmin.