INF.03 · Bazy danych i SQL

CREATE, ALTER, DROP: jak zbudować i zmienić strukturę bazy

Baza danych jest jak szafka z opisanymi szufladami. DDL buduje szafkę i ustala układ szuflad, a dopiero potem wkładasz do nich rzeczy. Na kiermaszu klasowym zaczynasz od tabeli Produkty. Po tygodniu potrzebna jest kategoria, więc zmieniasz tabelę. Gdy testowa tabela nie jest już potrzebna, usuwasz ją. Właśnie tak struktura bazy zmienia się razem z aplikacją.

około 35 minut
CREATE · ALTER · DROP
bazy: sklep_muzyczny, biblioteka, apteka

Po tej lekcji: dobierzesz typy danych i ograniczenia, napiszesz CREATE TABLE z kluczami, zmienisz tabelę poleceniem ALTER i odróżnisz DROP od TRUNCATE.

1 · Anatomia definicji

Każdy wiersz opisuje jedną kolumnę

DDL (Data Definition Language) to ta część SQL, która pracuje ze strukturą bazy: jakie są tabele, jakie mają kolumny i jakie reguły muszą spełniać dane. Samymi rekordami zajmuje się DML (INSERT, UPDATE, DELETE). Czytaj definicję od lewej do prawej.

produkty.sqlsql

CREATE TABLE Produkty (
    id_produktu INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100) NOT NULL,
    cena DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    liczba_sztuk INT DEFAULT 0
);

id_produktu INT AUTO_INCREMENT PRIMARY KEY nazwa kolumny, typ (liczba całkowita), numerowanie automatyczne (1, 2, 3…) i klucz główny w jednym.
nazwa VARCHAR(100) NOT NULL tekst do 100 znaków. NOT NULL oznacza, że nazwa jest obowiązkowa.
cena DECIMAL(10,2) NOT NULL DEFAULT 0.00 dokładna liczba z dwoma miejscami po przecinku. DEFAULT ustawia wartość, gdy jej nie podasz.
Przecinki i średnik. Przecinek rozdziela definicje i nie stoi po ostatniej. Średnik kończy całe polecenie.

Element	Rola	Przykład zastosowania
`INT`	liczby całkowite	liczba sztuk
`VARCHAR(100)`	tekst do 100 znaków	nazwa produktu
`DECIMAL(10,2)`	dokładna liczba z dwoma miejscami po przecinku	cena
`DATE`	data w formacie RRRR-MM-DD	data zamówienia
`NOT NULL`	wartość jest obowiązkowa	produkt musi mieć nazwę
`UNIQUE`	wartość nie może się powtórzyć	adres e-mail konta
`DEFAULT`	wartość, gdy nie podano innej	status „nowe”
`FOREIGN KEY`	łączy kolumnę z kluczem innej tabeli	produkt należy do kategorii

Typ danych mówi, co można zapisać. Ograniczenie mówi, co wolno, a czego nie.

2 · Typy danych

Jaki typ pasuje do tych danych?

Dobry typ pilnuje danych za Ciebie. Zły potrafi po cichu je zepsuć. Do każdej wartości wybierz typ kolumny i zobacz, dlaczego jedna odpowiedź jest lepsza od pozostałych.

3 · Kolejność ma znaczenie

Zbuduj bazę szkoły w dobrej kolejności

Poniżej pięć poleceń. Kliknij to, które wykonasz jako pierwsze, potem kolejne. Jeśli wybierzesz za wcześnie, baza odpowie błędem, tak jak w prawdziwej konsoli.

4 · ALTER i DROP

Struktura zmienia się razem z aplikacją

CREATE tworzy, ALTER zmienia istniejącą tabelę, a DROP usuwa obiekt razem ze strukturą i danymi. Po każdej zmianie sprawdź wynik poleceniem DESCRIBE albo SHOW CREATE TABLE.

zmiany.sqlsql

ALTER TABLE Produkty ADD COLUMN kategoria VARCHAR(50);
ALTER TABLE Produkty MODIFY COLUMN kategoria VARCHAR(80);
ALTER TABLE Produkty DROP COLUMN kategoria;

TRUNCATE TABLE Produkty;   -- usuwa wszystkie rekordy, tabela zostaje
DROP TABLE IF EXISTS Produkty;   -- usuwa tabelę razem z danymi

ADD, MODIFY, DROP COLUMN dodają kolumnę, zmieniają jej typ i usuwają ją. Zmiana typu na krótszy może obciąć dane.
IF EXISTS zapobiega błędowi, gdy usuwanego obiektu nie ma. Nie naprawia jednak istniejącej struktury.
TRUNCATE a DROP pierwsze zostawia pustą tabelę, drugie usuwa całą tabelę. Obu nie da się cofnąć bez kopii zapasowej.

5 · Zanim się pomylisz

Cztery pomyłki w DDL

Rozwiń każdą z nich. Zobaczysz komunikat z prawdziwej bazy i poprawny zapis.

Brak wybranej bazy

Tak nie

CREATE TABLE Produkty (id INT);

Błąd: ERROR 1046: No database selected. Baza nie wie, w którym miejscu ma utworzyć tabelę.

Tak

USE sklep;
CREATE TABLE Produkty (id INT);

Przed poleceniami sprawdź SELECT DATABASE(); albo wykonaj USE nazwa_bazy;.

Przecinek po ostatniej kolumnie

Tak nie

CREATE TABLE Produkty (
    id_produktu INT PRIMARY KEY,
    nazwa VARCHAR(100),
);

Błąd składni: You have an error in your SQL syntax w pobliżu ).

Tak

CREATE TABLE Produkty (
    id_produktu INT PRIMARY KEY,
    nazwa VARCHAR(100)
);

Przecinki rozdzielają definicje, ale po ostatniej nie stawiamy żadnego.

DROP, gdy chciałeś tylko wyczyścić tabelę

Tak nie

DROP TABLE Leki;

Tabela znika razem ze strukturą i danymi. Chciałeś usunąć tylko rekordy.

Tak

TRUNCATE TABLE Leki;

Po TRUNCATE tabela nadal istnieje, tylko jest pusta.

Zmiana bez kontroli

Tak nie

ALTER TABLE Produkty
    ADD COLUMN kategoria VARCHAR(50);

Polecenie się wykona, ale brak komunikatu o błędzie nie znaczy jeszcze, że struktura jest taka, jak chciałeś.

Tak

ALTER TABLE Produkty
    ADD COLUMN kategoria VARCHAR(50);
DESCRIBE Produkty;

Po każdej zmianie sprawdź strukturę. Tak pracujesz bez zgadywania.

6 · Sprawdź sam siebie

Po każdej zmianie struktury

Na egzaminie INF.03 liczy się nie tylko samo polecenie, ale też praca na właściwej bazie, zachowanie danych, poprawne klucze i udokumentowanie wyniku. Dlatego po każdej zmianie przejdź przez tę listę.

Wiem, w której bazie pracuję (USE albo SELECT DATABASE();).
Obejrzałem strukturę przez DESCRIBE albo SHOW CREATE TABLE.
Liczba rekordów w tabelach źródłowych się nie zmieniła.
Nie wykonałem TRUNCATE ani DROP na tabelach źródłowych.
Klucze główne i obce są widoczne w wyniku SHOW CREATE TABLE.

Podsumowanie

Co warto zapamiętać

DDL zmienia strukturę, a DML zmienia rekordy.
Typ danych mówi, co można zapisać, a ograniczenie (NOT NULL, UNIQUE, DEFAULT) mówi, co wolno.
Telefon to tekst, cena to DECIMAL, data to DATE.
Kolejność: baza, USE, tabele nadrzędne, tabele z kluczami obcymi, kontrola.
TRUNCATE czyści tabelę, DROP usuwa tabelę. Obu nie da się cofnąć bez kopii.
Po każdej zmianie sprawdź strukturę.

Dalej na swoim koncie

Rozgrzewki, quiz i zadania są w biurku ucznia.

Teoria jest otwarta dla wszystkich. Żeby rozwiązywać zadania, zapisywać szkice i widzieć swój postęp, zaloguj się.

Zaloguj się →

Źródła i dalsza nauka

Dokumentacja MariaDB: polecenia SQL