SQL jest pozwalającym zarządzać danymi poprzez komendy INSER, UPDATE i DELETE. Polecenia te służą do manipulacjami danymi w ramach tabeli. Kurs SQL INSERT | DELETE | UPDATE pokaże jak zarządzać danymi w bazie danych a dokładniej:
- Pokażę jak dodawać rekordy do tabeli dzięki instrukcji SQL: INSERT
- Błąd: ORA-00001 – naruszono więzy unikatowe
- Błąd: ORA-32795 – GENERATED ALWAYS
- Błąd: ORA-02291 – naruszono więzy spójności
- Przedstawię sposób usuwania danych przy pomocy polecenia SQL DELETE
- Błąd: ORA-02292: naruszono więzy spójności
- Poznasz jak modyfikować dane w tabeli instrukcją: SQL UPDATE
- Update kilku kolumn
Ten kurs jest częścią cyklu Kurs SQL
Zapisz się na autorskie Szkolenie SQL
Przygotowanie bazy danych
Jeżeli dopiero zaczynasz z bazami danych to polecam na początku zapoznać się z kursem wprowadzającym do baz danych Bazy Danych – Podstawy, Definicje, Przykład
Następnie zainstalowanie własnej bazy danych na komputerze zgodnie z kursem SQL – Baza Danych, jak zacząć
Aby manipulować danymi w bazie danych potrzebujesz tabeli w której te dane może przechowywać. Aby lepiej zrozumień ten kurs polecam zapoznać się z kurami które pokażą jak przygotować tabelę:
- Typy danych w Oracle SQL
- SQL DROP | CREATE TABLE (Dodawanie i usuwanie tabel)
Aby utworzyć bazę danych na której będziemy pracować wykonaj poniższe skryptem SQL:
CREATE TABLE CENNIK (
ROZMIAR VARCHAR2(3 CHAR)
, CENA NUMBER
, CONSTRAINT CENNIK_PK PRIMARY KEY (ROZMIAR)
);
CREATE TABLE MENU (
ID NUMBER GENERATED ALWAYS AS IDENTITY
, NAZWA VARCHAR2(20 CHAR)
, ROZMIAR VARCHAR2(3 CHAR)
, CONSTRAINT MENU_PK PRIMARY KEY (ID)
, CONSTRAINT MENU_CENNIK_FK FOREIGN KEY (ROZMIAR) REFERENCES CENNIK(ROZMIAR)
);
CREATE TABLE KLIENT (
ID NUMBER GENERATED ALWAYS AS IDENTITY
, IMIE VARCHAR2(20 CHAR)
, NAZWISKO VARCHAR2(20 CHAR)
, CONSTRAINT KLIENT_PK PRIMARY KEY (ID)
);
CREATE TABLE ADRES (
ID_ADRESU NUMBER GENERATED ALWAYS AS IDENTITY
, ULICA VARCHAR2(20 CHAR)
, NR_BLOKU VARCHAR2(10 CHAR)
, NR_MIESZKANIA VARCHAR2(10 CHAR)
, MIASTO VARCHAR2(20 CHAR)
, CONSTRAINT ADRES_PK PRIMARY KEY (ID_ADRESU)
);
CREATE TABLE ZAMOWIENIA (
ID_PIZZA NUMBER
, ID_KLIENTA NUMBER
, ID_ADRESU NUMBER
, DATA_ZAMOWIENIA DATE
, CONSTRAINT ZAM_PIZZA_FK FOREIGN KEY (ID_PIZZA) REFERENCES MENU(ID)
, CONSTRAINT ZAM_KLIENT_FK FOREIGN KEY (ID_KLIENTA) REFERENCES KLIENT(ID)
, CONSTRAINT ZAM_ADRES_FK FOREIGN KEY (ID_ADRESU) REFERENCES ADRES(ID_ADRESU)
);Powyższy skrypt utworzy bazę danych
Mając już przygotowany schemat tabel bazy danych pizzerii możemy rozpocząć pracę z danymi.
SQL INSERT – dodanie danych do tabeli
Polecenie SQL: INSERT służy do dodania danych to tabeli. Polecenie to wykonuje się poprzez:
INSERT INTO nazwa_tabeli (kolumna_1, kolumna_2, ... kolumna_n)
VALUES (wartosc_kolumna_1, wartosc_kolumna_2, ... wartosc_kolumna_n);Skoro wiemy jak wykorzystać polecenie INSERT INTO dodajmy pierwszy rekord do tabeli CENNIK:
INSERT INTO CENNIK(ROZMIAR, CENA) VALUES ('L', 15);Po wykonaniu powyższego polecenia, powinniśmy otrzymać komunikat:
1 row inserted.
W tym momencie prawie mamy dodany rekord do tabeli. Pozostaje jedynie małe ale a mianowicie rekord został dodany do tabeli ale tylko w ramach naszej sesji. Oznacza to, że nikt poza nami nie zobaczy w bazie tego rekordu i gdy wznowimy połączenie do bazy danych to tego rekordu już tam nie będzie. Aby rekord został na stałe dodany do tabeli należy powyższe polecenie zatwierdzić poleceniem:
COMMIT;W tym momencie do tabeli CENNIK został dodany rekord z ceną dla pizzy w rozmiarze L i wszyscy mają dostęp do tego rekordu. Wykonajmy więc polecenie sprawdzające zawartość tabeli CENNIK:
SELECT
*
FROM CENNIK;
Polecenie SELECT służy do wyciągania danych między innymi w tabelach. Widzimy, że udało nam się dodać rekord do tabeli CENNIK.
Poniższej przedstawiam listę najpopularniejszych błędów przy dodawaniu rekordów do tabel.
ORA-00001: naruszono więzy unikatowe
Spróbujmy do tabeli CENNIK dodać jeszcze jeden rekord:
INSERT INTO CENNIK(ROZMIAR, CENA) VALUES ('L', 20);Po próbie dodania takiego rekordu otrzymamy poniższy błąd:
rror starting at line : 8 in command -
INSERT INTO CENNIK(ROZMIAR, CENA) VALUES ('L', 20)
Error report -
ORA-00001: naruszono więzy unikatowe (ORACLEDEV.CENNIK_PK)Błąd ORA-00001 oznacza, że próbujemy dodać rekord do tabeli w której występują ograniczenia na unikalność kolumny. W tabeli CENNIK kolumna ROZMIAR jest PRIMARY KEY co oznacza, że mogą w niej występować tylko unikalne wartości. Dlatego też dodając kolejny rekord z wartością “L” do kolumny ROZMIAR baza danych zwróciła błąd.
ORA-32795: GENERATED ALWAYS
Spróbujemy do tym razem uzupełnić tabelę MENU wartościami przy pomocy poniższego polecenia SQL:
INSERT INTO MENU(ID, NAZWA, ROZMIAR) VALUES (1, 'Marherita', 'L');Po próbie dodania powyższego rekordu otrzymamy z bazy danych komunikat:
Error starting at line : 17 in command - INSERT INTO MENU(ID, NAZWA, ROZMIAR) VALUES (1, 'Marherita', 'L') Error at Command Line : 17 Column : 18 Error report - SQL Error: ORA-32795: nie można wstawić do kolumny tożsamości utworzonej jako GENERATED ALWAYS 32795.0000 - "cannot insert into a generated always identity column" *Cause: An attempt was made to insert a value into an identity column created with GENERATED ALWAYS keywords. *Action: A generated always identity column cannot be directly inserted. Instead, the associated sequence generator must provide the value.
Powyższy błąd oznacza, że próbujemy dodać rekord w którym uzupełniliśmy wartość oznaczoną jako “GENERATED AKWAYS”. I faktycznie tak jest ponieważ kolumna ID w tabeli MENU oznaczona jest jako “NUMBER GENERATED ALWAYS AS IDENTITY” co oznacza, że przy dodawaniu rekordu baza danych sama ją uzupełni kolejnym numerem.
Tak więc aby powyższy rekord dodać do bazy danych należy z polecenia usunąć kolumnę ID jak w poniższym SQL:
INSERT INTO MENU(NAZWA, ROZMIAR) VALUES ('Marherita', 'L');
COMMIT;ORA-02291: naruszono więzy spójności
Zobaczmy co się stanie gdy spróbujemy dodać rekord z powiązaniem do innej tabeli którego nie ma jak poniższy do tabeli MENU:
INSERT INTO MENU(NAZWA, ROZMIAR) VALUES ('Peperoni', 'XL');Oraz komunikat błędu:
Error starting at line : 19 in command -
INSERT INTO MENU(NAZWA, ROZMIAR) VALUES ('Peperoni', 'XL')
Error report -
ORA-02291: naruszono więzy spójności (ORACLEDEV.MENU_CENNIK_FK) - nie znaleziono klucza nadrzędnegoTym razem do tabeli MENU staramy się dodać pizzę w rozmiarze “XL”. Kolumna ROZMIAR jest kluczem do tabeli CENNIK w której nie występuje rozmiar XL dlatego też baza danych zwraca powyższy błąd. Aby więc móc dodać powyższy rekord w pierwszej kolejności należy dodać do tabeli CENNIK pizzę z rozmiarem “XL”
SQL DELETE – usuwanie danych z tabeli
W powyższego rozdziału dowiedziałeś się jak dodawać dane do tabeli oraz na jakie błędy możesz się natknąć. Zobaczmy więc co zrobić aby usunąć dodane wcześniej dane z tabeli.
Do usuwania danych z tabeli w SQL służy polecenie DELETE które ma następującą składnię:
DELETE FROM nazwa_tabeli
WHERE warunki_które_rekordy_usunąć; <--- sekcja z WHERE jest opcjonalna.Aby usunąć dane pierw dodajmy kilka rekordów do tabeli:
INSERT INTO KLIENT (IMIE, NAZWISKO) VALUES ('Jan', 'Kowalski');
INSERT INTO KLIENT (IMIE, NAZWISKO) VALUES ('Jan', 'Nowak');
INSERT INTO KLIENT (IMIE, NAZWISKO) VALUES ('Anna', 'Zaradna');
INSERT INTO KLIENT (IMIE, NAZWISKO) VALUES ('Miś', 'Uszatek');
COMMIT;Postarajmy się więc usunąć z powyższej tabeli Anne. Aby ją usunąć należy wykonać poniższe polecenie:
DELETE FROM KLIENT
WHERE IMIE = 'Anna';
COMMIT;Po wykonaniu powyższego polecenia otrzymamy komunikat z bazy danych:
1 row deleted. Commit complete.
Powyższe oznacza, że skutecznie usunęliśmy rekord z tabeli KLIENT.
W poleceniu DELETE sekcja WHERE jest opcjonalna. Wykonanie polecenia DELETE bez warunków WHERE spowoduje usunięcie wszystkich danych z tabeli jak w poleceniu SQL:
DELETE FROM KLIENT;
COMMIT;Oraz wynik z bazy danych:
3 rows deleted. Commit complete.
Co oznacza, że skutecznie wyczyściliśmy tabelę z danych.
ORA-02292: naruszono więzy spójności
W przykładzie z dodawaniem rekordów dodaliśmy po jednym wierszu do tabel MENU i CENNIK powiązanych kolumną ROZMIAR z wartością “L”. Spróbujmy więc usunąć rekord z tabeli CENNIK do której referuje tabela MENU poleceniem:
DELETE FROM CENNIK;Otrzymamy błąd:
Error starting at line : 37 in command - DELETE FROM CENNIK Error report - ORA-02292: naruszono więzy spójności (ORACLEDEV.MENU_CENNIK_FK) - znaleziono rekord podrzędny
Powyższy błąd mówi, że staramy się usunąć z tabeli CENNIK rekord do którego referuje przynajmniej jeden rekord przy użyciu ograniczenia MENU_CENNIK_FK który jest nałożony na tabeli MENU na kolumnie ROZMIAR. Nie możemy go usunąć ponieważ tabela MENU nie referowałaby finalnie do żadnej wartości a jest to naruszenie integralności danych.
Aby więc usunąć wartość z tabeli CENNIK w pierwszej kolejności należy usunąć wszystkie rekordy z tabeli MENU z wartością “L” przy użyciu SQL:
DELETE FROM MENU
WHERE ROZMIAR = 'L';
DELETE FROM CENNIK;
COMMIT;Otrzymamy z bazy danych odpowiedź:
1 row deleted. 1 row deleted. Commit complete.
Co oznacza, że oba rekordy zostały usunięte.
SQL UPDATE – zmiana danych w tabeli
Ostatnim poleceniem służącym do zarządzania danymi w tabeli jest polecenie UPDATE. Polecenie to służy do zmian danych w tabeli. Jego składnia wygląda następująco w SQL:
UPDATE nazwa_tabeli
SET nazwa_kolumny = wartość
WHERE warunki <-- sekcja opcjonalna.Zanim więc będziemy edytować dane dodajmy jakieś rekordy do bazy przy użyciu poniższego polecenia:
INSERT INTO ADRES (ULICA, NR_BLOKU, NR_MIESZKANIA, MIASTO) VALUES ('JP2', 2, 1, 'Warszawa');
INSERT INTO ADRES (ULICA, NR_BLOKU, NR_MIESZKANIA, MIASTO) VALUES ('Długa', 10, 10, 'Warszawa');
INSERT INTO ADRES (ULICA, NR_BLOKU, NR_MIESZKANIA, MIASTO) VALUES ('Prosta', 21, 1, 'Poznań');
COMMIT;Mając już dane w tabeli ADRES możemy zmodyfikować przykładowe wartości w tabeli. Zmieńmy więc nazwę ulicy “Długa” na “Krótka” przy pomocy polecenia SQL:
UPDATE ADRES
SET ULICA = 'Krótka'
WHERE ULICA = 'Długa';
COMMIT;oraz komunikat z bazy danych:
1 row updated. Commit complete.
Oznacza, to że udało nam się zmodyfikować dane w tabeli ADRES. ZObaczmy te dany przy pomocy SQL:
SELECT
*
FROM ADRES;Oraz wynik:
Podobnie jak w przypadku INSERT i DELTE przy wykonywaniau UPDATE należy zwrócić szczególną uwagę na edycję kolumn na których nałożone są CONSTRAINTY.
Update kilku kolumn
W SQL istnieje możliwość zmiany kilku kolumn na raz. W tym celu należy nieco zmodyfikować klauzulę UPDATE na zgodną z poniższym przykładem:
UPDATE ADRES
SET NR_MIESZKANIA = 0
, MIASTO = 'POZNAN'
WHERE ID_ADRESU = 1;Podsumowanie
- W SQL instrukcja INSERT INTO służy do dodawania rekordów do tabeli
- ORA-00001 – oznacza, że dodajesz wartość zdublowaną wartość w kolumnie która nie dopuszcza dubli
- ORA-32795 – oznacza, że dodajesz wrtość do kolumny która ma automatycznie generowane wartości
- ORA-02291 – oznacza, że dodajesz wartość do kolumny która jest kluczem do innej tabeli
- Instrukcja DELET FROM służy do usuwania rekordów z tabeli
- ORA-02292: oznacza, że chcesz usunąć rekord od którego są zależne rekordy w innych tablach
- Instrukcja UPDATE służy aktualizacji rekordów w tabeli
Zadanie
Należy dodać rekord do każdej z tabel z schematu z rozdziału “Przygotowanie bazy danych”
Rozwiązanie:
INSERT INTO CENNIK (ROZMIAR, CENA) VALUES ('L', 15);
INSERT INTO CENNIK (ROZMIAR, CENA) VALUES ('XL', 20);
INSERT INTO CENNIK (ROZMIAR, CENA) VALUES ('XXL', 25);
INSERT INTO MENU (NAZWA, ROZMIAR) VALUES ('Margherita', 'XXL');
INSERT INTO MENU (NAZWA, ROZMIAR) VALUES ('Margherita', 'L');
INSERT INTO MENU (NAZWA, ROZMIAR) VALUES ('Pepperonia', 'L');
INSERT INTO MENU (NAZWA, ROZMIAR) VALUES ('Hawajska', 'L');
INSERT INTO MENU (NAZWA, ROZMIAR) VALUES ('Margherita', 'XL');
INSERT INTO MENU (NAZWA, ROZMIAR) VALUES ('Wiejska', 'XXL');
INSERT INTO KLIENT (IMIE, NAZWISKO) VALUES ('Jan', 'Kowalski');
INSERT INTO KLIENT (IMIE, NAZWISKO) VALUES ('Piotr', 'Nowak');
INSERT INTO KLIENT (IMIE, NAZWISKO) VALUES ('Anna', 'Zaradna');
INSERT INTO KLIENT (IMIE, NAZWISKO) VALUES ('Kamila', 'Zaradna');
INSERT INTO ADRES (ULICA, NR_BLOKU, NR_MIESZKANIA, MIASTO) VALUES ('Hery', 5, null, 'Warszawa');
INSERT INTO ADRES (ULICA, NR_BLOKU, MIASTO) VALUES ('Domaniewska', 2, 'Poznań');
INSERT INTO ADRES (ULICA, MIASTO) VALUES ('JP2', 'Warszawa');
INSERT INTO ADRES (ULICA, MIASTO) VALUES ('JP2', 'Wrocław');
INSERT INTO ZAMOWIENIA (ID_PIZZA, ID_KLIENTA, ID_ADRESU, DATA_ZAMOWIENIA) VALUES (1, 1, 1, '2019-01-01');
INSERT INTO ZAMOWIENIA (ID_PIZZA, ID_KLIENTA, ID_ADRESU, DATA_ZAMOWIENIA) VALUES (2, 2, 2, '2019-01-02');
INSERT INTO ZAMOWIENIA (ID_PIZZA, ID_KLIENTA, ID_ADRESU, DATA_ZAMOWIENIA) VALUES (3, 2, 2, '2019-01-02');
INSERT INTO ZAMOWIENIA (ID_PIZZA, ID_KLIENTA, DATA_ZAMOWIENIA) VALUES (4, 3, '2019-01-03');
INSERT INTO ZAMOWIENIA (ID_PIZZA, ID_KLIENTA, DATA_ZAMOWIENIA) VALUES (5, 3, '2019-01-03');
INSERT INTO ZAMOWIENIA (ID_PIZZA, ID_KLIENTA, DATA_ZAMOWIENIA) VALUES (6, 3, '2019-01-03');
INSERT INTO ZAMOWIENIA (ID_PIZZA, ID_KLIENTA, ID_ADRESU, DATA_ZAMOWIENIA) VALUES (2, 3, 3, '2019-01-05');
INSERT INTO ZAMOWIENIA (ID_PIZZA, ID_KLIENTA, ID_ADRESU, DATA_ZAMOWIENIA) VALUES (3, 4, 4, '2019-01-06');
COMMIT;