SQLite INSERT, UPDATE, DELETE Zapytanie z przykładem

Spisie treści:

Anonim

Klauzule modyfikacji danych w SQLite to instrukcje INSERT, UPDATE i DELETE. Służy do wstawiania nowych wierszy, aktualizowania istniejących wartości lub usuwania wierszy z bazy danych.

W tym samouczku nauczysz się:

  • WSTAWIĆ
  • Aktualizacja
  • Kasować
  • Klauzula dotycząca konfliktu

Zauważ, że we wszystkich poniższych przykładach musisz uruchomić sqlite3.exe i otworzyć połączenie z przykładową bazą danych w sposób ciągły:

Krok 1) Na tym etapie

  1. Otwórz Mój komputer i przejdź do następującego katalogu „ C: \ sqlite ” i
  2. Następnie otwórz „ sqlite3.exe ”:

Krok 2) Otwórz bazę danych „ TutorialsSampleDB.db ” za pomocą następującego polecenia:

.open TutorialsSampleDB.db

Teraz jesteś gotowy do uruchamiania dowolnego typu zapytań w bazie danych.

SQLite INSERT

SQLite INSERT służy do wstawiania rekordów do określonej tabeli bazy danych. musisz użyć klauzuli „INSERT”. Składnia klauzuli INSERT jest następująca:

  • Po klauzuli INSERT należy określić, do której tabeli należy wstawić wartości.
  • Po nazwie tabeli napiszesz listę kolumn, do których chcesz wstawić wartości.
  • Możesz zignorować nazwy kolumn i nie pisać do nich.
  • Jeśli nie wpiszesz nazwy kolumn, wartości zostaną wstawione do wszystkich kolumn znajdujących się w tabeli w tej samej kolejności, kolumny są zdefiniowane w tabeli.
  • Po klauzuli VALUES należy podać wartości do wstawienia.
  • Każda klauzula INSERT wstawia tylko jeden wiersz. Jeśli chcesz wstawić wiele wierszy, powinieneś napisać wiele klauzul INSERT, po jednej dla każdego wiersza.

Przykład wstawiania SQLite

W poniższym przykładzie wstawimy 2 wiersze do tabeli uczniów, po jednym dla każdego ucznia:

INSERT INTO Students (StudentId, StudentName, DepartmentId, DateOfBirth)VALUES (11, „Ahmad”, 4, „1997-10-12”);WSTAW WARTOŚCI UCZNIÓW (12, „Aly”, 4, „1996-10-12”);

Powinno to działać pomyślnie i nie ma żadnych danych wyjściowych:

Spowoduje to wstawienie dwóch uczniów:

  • Pierwszy uczeń z StudentId = 11, StudentName = Ahmad, DepartmentId = 4 i DateOfBirth = 1997-10-12.
  • Drugi uczeń z StudentId = 12, StudentName = Aly, DepartmentId = 4 i DateOfBirth = 1996-10-12 '.

W pierwszym zestawieniu wymieniliśmy kolumny o nazwach „ StudentId, StudentName, DepartmentId, DateOfBirth”. W drugim jednak tego nie robiliśmy.

Cztery wartości „ 12, 'Aly', 4, '1996-10-12' ” zostaną wstawione we wszystkich czterech kolumnach tabeli Studenci w tej samej kolejności, w jakiej kolumny są zdefiniowane.

Teraz sprawdźmy, czy dwóch uczniów zostało wstawionych do tabeli Students, uruchamiając następujące zapytanie:

WYBIERZ * OD Studentów;

Następnie powinieneś zobaczyć dwóch uczniów zwróconych z tego zapytania w następujący sposób:

Aktualizacja SQLite

Zapytanie SQLite UPDATE służy do modyfikowania istniejących rekordów w tabeli. Możesz użyć klauzuli WHERE z zapytaniem UPDATE, aby zaktualizować wybrane wiersze. Klauzula UPDATE aktualizuje tabelę, zmieniając wartość dla określonej kolumny. Oto składnia klauzuli UPDATE:

W następujący sposób:

  • Po „klauzuli update” należy wpisać nazwę tabeli do aktualizacji.
  • Musisz napisać „klauzulę SET”, która jest używana do zapisania nazwy kolumny do aktualizacji i wartości do zaktualizowania.
  • Możesz zaktualizować więcej niż jedną kolumnę. Możesz użyć przecinka między każdą linią.
  • Możesz określić klauzulę WHERE, aby określić tylko niektóre wiersze. Aktualizowane są tylko wiersze, dla których wyrażenie przyjmuje wartość „prawda”. Jeśli nie określisz klauzuli WHERE, wszystkie wiersze zostaną zaktualizowane.

Przykład aktualizacji SQLite

W poniższej instrukcji UPDATE zaktualizujemy DepartmentId dla studenta z StudentId = 6 na 3:

ZAKTUALIZUJ uczniówSET DepartmentId = 3GDZIE StudentId = 6;

Powinno to działać pomyślnie i nie powinno być żadnych wyników:

W klauzuli UPDATE określiliśmy, że chcemy zaktualizować tabelę Students.

  • W klauzuli WHERE odfiltrowaliśmy wszystkich uczniów, aby wybrać tylko wiersz dla StudentId = 6.
  • Klauzula SET zaktualizuje wartość identyfikatora działu dla wybranych studentów na 3.

Teraz sprawdźmy, czy uczeń o identyfikatorze 6 został zaktualizowany, uruchamiając następujące polecenie:

SELECT * FROM Students WHERE StudentId = 6;

Powinieneś teraz zobaczyć, że wartość identyfikatora działu wynosi teraz 3 w następujący sposób:

SQLite Delete

Zapytanie SQLite DELETE służy do usuwania istniejących rekordów z określonej tabeli. Możesz użyć klauzuli WHERE z zapytaniami DELETE, aby usunąć wybrane wiersze.

Klauzula DELETE ma następującą składnię:

  • Musisz wpisać nazwę tabeli po klauzuli DELETE FROM, z której chcesz usunąć rekordy. ( Uwaga: że DELETE klauzuli . Służy do usuwania niektóre rekordy z tabeli lub usunąć wszystkie rekordy i nie usunie samej tabeli Jednak klauzula DROP służy do usuwania całą tabelę z wszystkich rekordów na nim. )
  • Jeśli napiszesz klauzulę DELETE, taką jak „DELETE FROM guru”, usunie to wszystkie rekordy z tabeli „guru”.
  • Możesz określić warunek WHERE za pomocą wyrażenia, jeśli chcesz usunąć określone wiersze. Tylko wiersze, dla których wyrażenie ma wartość „prawda”, zostaną usunięte. Na przykład „DELETE FROM guru WHERE id> 5” - spowoduje to usunięcie tylko rekordów, które mają identyfikator większy niż 5.

Przykład

W poniższym zestawieniu usuniemy dwóch uczniów z StudentId 11 i 12:

USUŃ OD Studentów, GDZIE StudentId = 11 LUB StudentId = 12;

Wyrażenie „ StudentId = 11 OR StudentId = 12 ” będzie prawdziwe tylko dla uczniów z identyfikatorami 11 i 12. Dlatego klauzula DELETE zostanie zastosowana do obu i tylko je usunie.

To polecenie powinno działać pomyślnie i nie powinieneś otrzymywać żadnych danych wyjściowych w następujący sposób:

Możesz sprawdzić, czy dwóch uczniów zostało usuniętych, wybierając wszystkie rekordy z tabeli Studenci w następujący sposób:

WYBIERZ * OD Studentów;

Nie powinieneś widzieć dwóch uczniów z identyfikatorami 11 i 12 w następujący sposób:

Klauzula konfliktu SQLite

Załóżmy, że masz kolumnę, która ma jedno z następujących ograniczeń kolumny: UNIQUE, NOT NULL, CHECK lub PRIMARY KEY. Następnie próbowałeś wstawić lub zaktualizować wartość w tej kolumnie wartością, która jest w konflikcie z tym ograniczeniem.

Na przykład, jeśli kolumna ma ograniczenie UNIQUE i próbowano wstawić wartość, która już istnieje (wartość zduplikowana), co powoduje konflikt z ograniczeniem UNIQUE. Następnie klauzula CONFLICT pozwala wybrać, co zrobić w takich przypadkach, aby rozwiązać ten konflikt.

Zanim będziemy kontynuować wyjaśnianie, w jaki sposób klauzula CONFLICT rozwiązuje konflikt. Powinieneś zrozumieć, co to jest transakcja w bazie danych.

Transakcja w bazie danych:

Termin transakcja bazy danych to lista operacji SQLite (wstawianie, aktualizowanie lub usuwanie). Transakcja bazy danych musi zostać wykonana jako jedna jednostka, wszystkie operacje zostały wykonane pomyślnie lub nie zostały wykonane wcale. Wszystkie operacje zostaną anulowane, jeśli jedna z nich nie zostanie wykonana.

Przykład transakcji w bazie danych:

Transakcja przelewu pieniędzy z jednego konta bankowego na drugie będzie obejmować kilka czynności. Ta operacja transakcyjna obejmuje wypłatę pieniędzy z pierwszego konta i zdeponowanie ich na innym koncie. Ta transakcja musi zostać w pełni zakończona lub całkowicie anulowana i nie może zawieść w połowie.

Oto lista pięciu rozdzielczości, które możesz wybrać w klauzuli CONFLICT:

  1. ROLLBACK - spowoduje to wycofanie transakcji, w której bieżąca instrukcja SQLite ma konflikt (anuluje całą transakcję). Na przykład, jeśli próbujesz zaktualizować 10 wierszy, a piąty wiersz ma wartość, która koliduje z ograniczeniem, żadne wiersze nie zostaną zaktualizowane, a 10 wierszy pozostanie bez zmian. Zostanie wyrzucony błąd.
  2. PRZERWIJ - spowoduje to przerwanie (anulowanie) bieżącej instrukcji SQLite, która ma konflikt, a transakcja nie zostanie anulowana. Na przykład, jeśli próbujesz zaktualizować 10 wierszy, a piąty wiersz ma wartość, która koliduje z ograniczeniem, tylko piąta wartość nie zostanie zaktualizowana, ale pozostałe 9 wierszy zostanie zaktualizowanych. Zostanie wyrzucony błąd.
  3. FAIL - przerywa bieżącą instrukcję SQLite, w której występuje konflikt. Jednak transakcja nie będzie kontynuowana, ale zostaną zatwierdzone poprzednie zmiany wprowadzone w wierszach poprzedzających wiersz, w którym występuje konflikt. Na przykład, jeśli próbujesz zaktualizować 10 wierszy, a piąty wiersz ma wartość, która koliduje z ograniczeniem, tylko 4 wiersze zostaną zaktualizowane, a drugi nie. Zostanie wyrzucony błąd.
  1. IGNORE - spowoduje to pominięcie wiersza zawierającego naruszenie ograniczenia i kontynuowanie przetwarzania innych kolejnych wierszy instrukcji SQLite. Na przykład, jeśli próbujesz zaktualizować 10 wierszy, a piąty wiersz ma wartość, która koliduje z ograniczeniem, tylko 4 wiersze zostaną zaktualizowane, a drugi nie. Nie będzie kontynuowane, aby zaktualizować inne wiersze i zatrzymać się w wierszu, który ma wartość powodującą konflikt. Żaden błąd nie zostanie zgłoszony.
  1. REPLACE - zależy to od rodzaju ograniczenia, którego dotyczy naruszenie:
  • Gdy występuje naruszenie ograniczenia dla ograniczenia UNIQUE lub PRIMARY KEY. REPLACE zamieni wiersz, który powoduje naruszenie, nowym wstawionym lub zaktualizowanym wierszem.
  • W przypadku naruszenia ograniczenia NOT NULL klauzula REPLACE zastąpi wartość NULL wartością domyślną z tej kolumny. Jeśli kolumna nie ma wartości domyślnej, SQLite przerwie instrukcję (instrukcja zostanie anulowana)
  • Jeśli dojdzie do naruszenia ograniczenia CHECK, klauzula zostanie przerwana.

Uwaga: powyższe 5 rozwiązań to opcje, w jaki sposób chcesz rozwiązać konflikt. Niekoniecznie musi być to, co ma zastosowanie do rozwiązania jednego konfliktu, ma zastosowanie do rozwiązania innych typów konfliktów.

Jak zadeklarować klauzulę CONFLICT

Klauzulę ON CONFLICT można zadeklarować podczas definiowania ograniczenia dla definicji kolumny w klauzuli CREATE TABLE. Używając następującej składni:

Możesz wybrać jedną z pięciu rezolucji, aby rozwiązać konflikt, jak wyjaśniono wcześniej.

PRZYKŁAD NA KONFLIKT IGNORUJ

Krok 1) Utwórz nowy temat tabeli w następujący sposób:

UTWÓRZ TABELĘ [Tematy] ([SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE,[SubjectName] NVARCHAR NOT NULL);

Zwróć uwagę, że zdefiniowaliśmy ograniczenie PRIMARY KEY w kolumnie SubjectId. Ograniczenie klucza podstawowego nie pozwoli na wstawienie dwóch zduplikowanych wartości do kolumny SubjectId, tak aby wszystkie wartości w tej kolumnie były unikalne. Zwróć również uwagę, że jako rozwiązanie konfliktu wybieramy „ IGNORUJ ”.

Polecenie powinno działać pomyślnie i nie powinieneś otrzymać żadnych błędów:

Krok 2) Teraz wstawmy kilka wartości do nowych tematów tabeli, ale z wartością, która narusza ograniczenie klucza podstawowego:

WSTAWIĆ DO TEMATÓW WARTOŚCI (1, 'Algebra');INSERT INTO Subjects VALUES (2, 'Kurs w bazie danych');INSERT INTO Subjects VALUES (2, 'Struktury danych');INSERT INTO Subjects VALUES (4, 'Algorytmy');

W tych instrukcjach INSERT próbowaliśmy wstawić dwa kursy z tym samym podstawowym kluczem podmiotu o identyfikatorze 2, co jest naruszeniem ograniczenia klucza podstawowego.

Polecenia powinny działać poprawnie i nie powinieneś otrzymywać żadnych błędów. W następujący sposób:

Krok 3) Wybierz wszystkie przedmioty z tabeli w następujący sposób:

SELECT * FROM Subjects;

To da ci listę przedmiotów:

Zauważ, że tylko trzy przedmioty zostały wstawione „ Algebra, Kurs bazy danych i Algorytmy ” zamiast 4 wierszy.

Wiersz, którego wartość narusza ograniczenie klucza podstawowego, czyli „Struktury danych”, został zignorowany i nie został wstawiony. Jednak SQLite kontynuuje wykonywanie innych instrukcji po tym wierszu.

Krok 4) USUŃ tematy tabeli, aby utworzyć ją ponownie z inną klauzulą ​​ON CONFLICT dla następującego przykładu, uruchamiając następujące polecenie:

DROP TABLE Tematy;

Polecenie drop usuwa całą tabelę. Tematy tabeli teraz nie istnieją.

ON CONFLICT REPLACE Przykład

Krok 1) Utwórz nowy temat tabeli w następujący sposób:

UTWÓRZ TABELĘ [Tematy] ([SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,[SubjectName] NVARCHAR NOT NULL);

Zauważ, że zdefiniowaliśmy ograniczenie PRIMARY KEY w kolumnie SubjectId. Ograniczenie klucza podstawowego nie pozwoli na wstawienie dwóch zduplikowanych wartości do kolumny SubjectId, tak aby wszystkie wartości w tej kolumnie były unikalne.

Zwróć również uwagę, że jako opcję rozwiązywania konfliktów wybieramy „ REPLACE ”. Polecenie powinno działać pomyślnie i nie powinieneś otrzymać żadnych błędów:

Krok 2) Teraz wstawmy kilka wartości do nowej tabeli Przedmioty, ale z wartością, która narusza ograniczenie klucza podstawowego:

WSTAWIĆ DO TEMATÓW WARTOŚCI (1, 'Algebra');INSERT INTO Subjects VALUES (2, 'Kurs w bazie danych');INSERT INTO Subjects VALUES (2, 'Struktury danych');INSERT INTO Subjects VALUES (4, 'Algorytmy');

W tych instrukcjach INSERT próbowaliśmy wstawić dwa kursy z tym samym podstawowym kluczem podmiotu o identyfikatorze 2, co jest naruszeniem ograniczenia klucza podstawowego.

Polecenia powinny działać poprawnie i nie powinieneś otrzymywać żadnych błędów. W następujący sposób:

Krok 3) Wybierz wszystkie przedmioty z tabeli w następujący sposób:

SELECT * FROM Subjects;

To da ci listę przedmiotów:

Zwróć uwagę, że tylko trzy tematy zostały wstawione „ Algebra, struktury danych i algorytmy ”, podczas gdy próbowaliśmy wstawić 4 wiersze.

Wiersz, którego wartość narusza ograniczenie klucza podstawowego, czyli „ Struktury danych ”, zastąpił wartość „ Kurs bazy danych ” w następujący sposób:

  • Pierwsze dwie instrukcje wstawiania działają bezproblemowo. Dwa przedmioty Algebra i Kurs baz danych zostaną wstawione z identyfikatorami 1, 2.
  • Gdy SQLite próbuje uruchomić trzecią instrukcję wstawiania z SubjectId 2 i SubjectName „ Struktury danych ”, dowiaduje się, że istnieje już podmiot z SubjectId = 2. Co jest naruszeniem ograniczenia klucza podstawowego zdefiniowanego w kolumnie SubjectId.
  • SQLite wybierze rozwiązanie REPLACE dla tego konfliktu. Zastępuje wartość, która już istnieje w tabeli tematów, nową wartością z instrukcji insert. Dlatego nazwa podmiotu „ Kurs bazy danych ” zostanie zastąpiona nazwą podmiotu „ Struktury danych ”.

Podsumowanie:

Klauzule INSERT, UPDATE i DELETE służą do modyfikowania danych w bazie danych SQLite. Klauzula CONFLICT to potężna klauzula do rozwiązywania wszelkich konfliktów między danymi a danymi do zmodyfikowania.