Wstawianie rekordów – INSERT

Zapytanie INSERT INTO w SQL to jedno z fundamentalnych poleceń, które służy do dodawania nowych danych (rekordów) do tabeli bazy danych. Można to porównać do sytuacji, w której wypełniamy nowy wiersz w arkuszu kalkulacyjnym – dodajemy pełny zestaw informacji opisujących jakiś obiekt, np. nowego ucznia, produkt, klienta, film czy zamówienie. Albo kiedy zakładamy konto w dowolnym serwisie internetowym – za akt “stworzenia” nowego użytkownika w bazie odpowiadać będzie skrypt wykonujący zapytanie INSERT.

Podstawowa, “książkowa” składnia kwerendy INSERT prezentuje się następująco:

INSERT INTO nazwa_tabeli (kolumna1, kolumna2, kolumna3) 
VALUES (wartość1, wartość2, wartość3);

• INSERT INTO – wiadomo: oznacza, że chcemy dodać (włożyć) nowe dane do konkretnej tabeli
• VALUES (...) – wartości, które dodajemy, oczywiście jeśli kolumna przechowuje tekst, to łańcuchy wpisujemy w apostrofach lub z użyciem cudzysłowu – np. 'Polska' lub "Polska"

Pora na konkretny przykład. Załóżmy, że chcemy dodać nowy film do bazy danych vod, do tabeli filmy. Oczywiście wszystkie bazy danych do pracy w kursie znajdziesz tutaj: Pliki SQL z bazami danych do pracy w tym kursie.

Przypomnijmy jak wygląda tabela filmy:

Dodajmy nowy rekord, zgodnie ze wcześniej opisaną składnią:

INSERT INTO filmy (ID_filmu, Tytul, Kraj_produkcji, Gatunek, Cena_w_zl) 
VALUES ('CC2010', 'Incepcja', 'USA', 'sci-fi', 12);

• ID_filmu jest kluczem głównym (podstawowym) w tabeli, lecz ponieważ jest to kombinacja dwóch liter porządkowych oraz czterech cyfr oznaczających rok produkcji, to podajemy całą wartość sami. W przypadku klucza będącego liczbą całkowitą oraz przy włączonym dla klucza AUTO_INCREMENT moglibyśmy jako wartość posłać NULL, a system sam wybrałby pierwszą wolną liczbę.
• Apostrofy stosujemy dla tekstów, a liczbę 12 (oznaczającą cenę wypożyczenia) wpisaliśmy bez apostrofów.

Podstawowe błędy przy wykonaniu INSERT

Przedstawmy naturę kilku najczęściej występujących błędów podczas wykonania kwerendy:

• Duplicate entry – rekord o takiej wartości identyfikatora (klucz podstawowy) najpewniej już istnieje! Klucz główny musi być unikalny, stąd brak zgody na wstawienie nowych wartości łamiących tę zasadę.
• Data too long – przekazana w zapytaniu wartość jest “za długa” – nie mieści się w typie danych danej kolumny. Na przykład tekst nie zmieścił się w VARCHAR(n), ponieważ liczba znaków przekroczyła n.
• Column count doesn’t match – podano za mało albo zbyt wiele wartości – sprawdźmy czy liczba kolumn i wartości jest taka sama.

Klucz podstawowy posiadający AUTO_INCREMENT

Rozważmy dodanie nowego rekordu do tabeli przedszkola, w bazie danych rekrutacja. Tabela prezentuje się następująco:

Jeśli tabela posiada kolumnę ustawioną jako klucz podstawowy oraz dodatkowo ma włączony dla tego klucza AUTO_INCREMENT – a taka sytuacja ma miejsce dla kolumny Id_przedszkola w tabeli przedszkola, to możemy tej kolumnie przypisać w zapytaniu INSERT wartość NULL:

INSERT INTO przedszkola (Id_przedszkola, Nazwa_przedszkola, Liczba_miejsc) 
VALUES (NULL, 'Nowe przedszkole', 50)

System sam wybiera wtedy wartość Id_przedszkola. Jaką konkretnie? Możemy to sprawdzić w phpMyAdmin na zakładce Operacje tabeli przedszkola:

Alternatywnie, w sytuacji gdy obowiązuje dla klucza AUTO_INCREMENT możemy też w ogóle pominąć istnienie takiej auto-inkrementowanej kolumny w zapytaniu:

INSERT INTO przedszkola (Nazwa_przedszkola, Liczba_miejsc) 
VALUES ('Kolejne nowe przedszkole', 99)

Nowa i unikalna wartość dla auto-inkrementowanego klucza podstawowego Id_przedszkola także zostanie poprawnie przypisana, mimo że niejako “zapomnieliśmy” o istnieniu tej kolumny w kwerendzie. Ot, cała siła i wygoda używania AUTO_INCREMENT.

Brak listy kolumn przed klauzulą VALUES

Co ciekawe, możemy w zapytaniach INSERT pominąć listę kolumn, czyli zamiast tworzenia klasycznego zapytania do tabeli filmy w bazie vod:

INSERT INTO filmy (ID_filmu, Tytul, Kraj_produkcji, Gatunek, Cena_w_zl) 
VALUES ('CC2010', 'Incepcja', 'USA', 'sci-fi', 12);

Możemy także stworzyć alternatywną wersję krótszą (również w pełni działającą):

INSERT INTO filmy VALUES ('CC2010', 'Incepcja', 'USA', 'sci-fi', 12);

Kiedy możemy zatem pominąć listę kolumn (atrybutów)? Możemy tak postąpić tylko wtedy, gdy podajemy dokładnie tyle wartości, ile kolumn istnieje w tabeli – ani więcej, ani mniej. A ponadto, kolejność wartości musi być identyczna jak oryginalna kolejność kolumn w tabeli. W przeciwnym razie wystąpi błąd – baza danych nie będzie wiedziała, która wartość ma trafić do której kolumny!

Reasumując w przypadku powyższej kwerendy: kolumn w tabeli jest pięć, stąd rezygnując z listy musimy podać także pięć wartości – i to w dokładnie takiej kolejności, w jakiej kolumny zostały zdefiniowane w tabeli w bazie danych!

Na egzaminie INF.03 – a także w profesjonalnych projektach – zdecydowanie zaleca się zawsze podawać listę kolumn. Z kilku praktycznych powodów:

• Poprawia to czytelność zapytania – od razu widzimy, które wartości trafiają do których kolumn.
• Chroni nas to przed błędami, jeśli struktura tabeli zmieni się w przyszłości (np. ktoś doda nową kolumnę – zapytanie bez listy przestanie działać lub zachowa się nieprzewidywalnie) – tworząc więc kwerendę bez listy kolumn niejako sami zastawiamy na siebie pułapkę!
• Podanie listy kolumn zapobiega przypadkowemu nadpisaniu danych lub błędnemu przypisaniu wartości (np. tytuł filmu podany błędnie jako jego gatunek).
• Podanie listy kolumn pozwala nam też zdefiniować własną ich kolejność – może być zupełnie inna niż oryginalnie w tabeli, bo komputer poradzi sobie z poprawnym przypisaniem wartości kierując się kolejnością podaną w kwerendzie, zamiast tą istniejącą w tabeli.
• W niektórych przypadkach chcemy dodać wartości tylko do wybranych kolumn – wtedy pominięcie listy nie wchodzi w grę!

Na co w szczególności należy uważać?

• Nie pomijajmy listy kolumn, jeśli tabela ma kolumny “opcjonalne” – np. z domyślnymi wartościami lub z powodu istnienia AUTO_INCREMENT dopuszczające NULL.
• Jeśli korzystamy z AUTO_INCREMENT dla klucza podstawowego to najlepiej pominąć tę kolumnę – wpisywanie konkretnej wartości ręcznie (innej niż NULL) może w oczywisty sposób prowadzić do konfliktów (“duplicate entry”).
• Stosujmy pełne zapytania z nazwami kolumn na egzaminie – to pewniejsze i bezpieczniejsze – lepiej zawsze więc listy kolumn użyć!

Dodawanie wielu rekordów jednocześnie

Pora na dość logiczny wniosek – szczególnie gdy zajrzymy do plików SQL zawierających eksport (zrzut, ang. dump) tabel – jednym zapytaniem INSERT możemy wstawiać wiele rekordów do tabeli. Listę kolumn podajemy oczywiście tylko raz, natomiast każdy wstawiany rekord zamykamy w nawiasach okrągłych i rozdzielamy je przecinkami:

INSERT INTO filmy (ID_filmu, Tytul, Kraj_produkcji, Gatunek, Cena_w_zl) 
VALUES 
('DD1994', 'Forrest Gump', 'USA', 'dramat', 8),
('EE2019', 'Joker', 'USA', 'thriller', 12),
('FF2021', 'Diuna', 'USA', 'sci-fi', 15);

Jest to rzecz jasna szybsze aniżeli wykonywanie trzech osobnych zapytań INSERT, ponieważ baza przetwarza wszystko naraz. Szczególnie w przypadku przywracania tysięcy rekordów z wyeksportowanego wcześniej pliku SQL – powtarzanie tak wiele razy listy kolumn byłoby bezcelowe i niepotrzebnie zwiększałoby wagę bazodanowego zrzutu.

Alternatywna składnia: INSERT z klauzulą SET

Oprócz najczęściej stosowanego sposobu dodawania rekordów do tabeli za pomocą klauzuli VALUES, możemy skorzystać także z alternatywnej składni z użyciem klauzuli SET. Taka forma przypomina trochę składnię instrukcji UPDATE stąd wydaje się bardzo czytelna, szczególnie gdy istnieje dużo kolumn. Zobaczmy, jak wygląda zapis naszego zapytania INSERT kierowanego do tabeli filmy w bazie vod w tej wersji:

INSERT INTO filmy 
SET 
    ID_filmu = 'CC2010',
    Tytul = 'Incepcja',
    Kraj_produkcji = 'USA',
    Gatunek = 'sci-fi',
    Cena_w_zl = 12;

Co się tutaj dzieje? Po słowie kluczowym SET przypisujemy wartości do konkretnych kolumn w “starym stylu”, czyli atrybut = wartość. Co ważne, oczywiście nie musimy trzymać się kolejności występowania kolumn w tabeli – komputer nie będzie mieć przecież w takim zapisie problemu z ustaleniem gdzie mają trafić poszczególne wartości. Ten sposób działa tylko w MariaDB – nie jest standardem SQL, więc nie zadziała np. w PostgreSQL.

Zalety tej metody:

• Czytelność – łatwo zrozumieć, która wartość trafia do której kolumny – szczególnie przy wielu atrybutach w rekordzie.
• Elastyczność – możemy dowolnie zmieniać kolejność kolumn bez ryzyka błędu.
• Wygodne kopiowanie – składnia bardzo podobna do zapytań UPDATE, więc łatwo jest “przerobić” kod jednego zapytania na kwerendę drugiego rodzaju.

Minusy tej metody:

• To nie jest standard SQL – zadziała tylko w silnikach zgodnych z MySQL (np. MariaDB). W innych systemach, takich jak PostgreSQL czy SQLite, zapytanie nie zostanie wykonane!
• Mniej znana forma – nie wszyscy programiści ją znają, więc może wprowadzać w błąd przy współpracy zespołowej.
• Jednorazowość – zapis z klauzulą SET pozwala dodać tylko jeden rekord na raz – jeśli chcemy dodać kilka rekordów jedną kwerendą INSERT, to musimy już użyć klasycznego zapisu z VALUES.

Na egzaminie zdecydowanie warto zastosować klasyczną (oraz uniwersalną, tzn. znaną w standardzie SQL) wersję z VALUES – to właśnie ona będzie obecna w arkuszach oraz kluczach odpowiedzi. Forma z użyciem SET jest dopuszczalna technicznie w XAMPP z MariaDB, ale egzaminatorzy mogą jej nie zaakceptować, jeśli nie pasuje do polecenia lub klucza odpowiedzi – szkoda stracić w ten sposób cenne punkty!

Podsumowanie – co zapamiętać do egzaminu?

Pora na wyciągnięcie wniosków z tej lekcji:

• INSERT INTO służy do dodawania nowych danych do tabeli – tak jakbyśmy dopisywali nowy wiersz w arkuszu kalkulacyjnym czy dodawali nowego użytkownika witryny internetowej.
• Klasyczna składnia: INSERT INTO nazwa_tabeli (kolumna1, kolumna2) VALUES (wartość1, wartość2);
• Dla tekstów używamy apostrofów: 'Polska', a liczby możemy podajemy bez nich: 12
• Najczęstsze błędy przy INSERT:
  • Duplicate entry – powtórzona wartość klucza głównego.
  • Data too long – za “długa” wartość (np. tekst).
  • Column count doesn’t match – niezgodność liczby kolumn i wartości w zapytaniu.
• Gdy kolumna posiada AUTO_INCREMENT, możemy:
  • Podać NULL jako wartość
  • Pominąć ją w ogóle na liście kolumn w zapytaniu
• Całą listę nazw kolumn (przed klauzulą VALUES) w zapytaniu INSERT można pominąć tylko jeśli podajemy wartości dla wszystkich atrybutów i zachowujemy ich kolejność taką jak w tabeli. Zaleca się jednak zawsze podawać listę kolumn – jest to czytelniejsze i bezpieczniejsze.
• Można wstawiać wiele rekordów naraz: INSERT INTO filmy (...) VALUES (...), (...), (...);

• Alternatywna składnia zapytania INSERT z wykorzystaniem SET prezentuje się następująco: INSERT INTO nazwa_tabeli SET kolumna1 = wartość1, kolumna2 = wartość2; lecz uwaga:
  • Ta postać działa tylko w MariaDB (nie w PostgreSQL czy SQLite).
  • Możemy dodać tylko jeden rekord naraz!
• Na egzaminie INF.03 najlepiej stosować klasyczny zapis z VALUES – jest zgodny ze standardem i występuje w kluczach odpowiedzi!