SQLite WSTAW, AKTUALIZUJ, USUŃ Zapytanie z przykładem

Zapytanie o wstawianie, aktualizację i usuwanie SQLite

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

Należy pamiętać, że we wszystkich poniższych przykładach należy uruchomić plik sqlite3.exe i nawiązać połączenie z przykładową bazą danych w trybie przepływowym:

Krok 1) W tym etapie,

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

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

.open TutorialsSampleDB.db

Teraz możesz uruchomić dowolny typ zapytania 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 wpisujesz listę kolumn, do których chcesz wstawić wartości.
• Możesz zignorować nazwy kolumn i nie pisać do nich.
• Jeśli nie wpiszesz nazw kolumn, wartości zostaną wstawione do wszystkich kolumn znalezionych w tabeli w tej samej kolejności, kolumny są zdefiniowane w tabeli.
• Po klauzuli VALUES należy podać wartości, które mają zostać wstawione.
• 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.

SQLite Wstaw przykład

W poniższym przykładzie wstawimy 2 wiersze do tabeli students (uczniowie), po jednym dla każdego ucznia:

INSERT INTO Students(StudentId, StudentName, DepartmentId, DateOfBirth)
              VALUES(11, 'Ahmad', 4, '1997-10-12');

INSERT INTO Students VALUES(12, 'Aly', 4, '1996-10-12');

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

Spowoduje to wstawienie dwóch uczniów:

• Pierwszy uczeń o identyfikatorze StudentId = 11, Nazwa Studenta = Ahmad, Identyfikator Wydziału = 4 i DateOfBirth = 1997-10-12.
• Drugi student ze StudentId=12, StudentName = Aly, DepartmentId = 4 i DateOfBirth = 1996-10-12.

W pierwszym poleceniu wymieniliśmy nazwy kolumn „StudentId, StudentName, DepartmentId, DateOfBirth”. Jednak w drugim poleceniu tego nie zrobiliśmy.

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

Teraz sprawdźmy, czy obaj studenci zostali wstawieni do tabeli Studenci, uruchamiając następujące polecenie pytanie:

SELECT * FROM Students;

Następnie powinieneś zobaczyć wyniki dwóch studentów zwróconych w wyniku tego zapytania:

SQLite Aktualizacja

SQLite Zapytanie 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. Poniżej przedstawiono składnię klauzuli UPDATE:

Jak następuje:

• Po „klauzuli aktualizacji” należy wpisać nazwę tabeli do aktualizacji.
• Musisz napisać „klauzulę SET”, która będzie 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 pomię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 ma wartość true. Jeśli nie określono klauzuli WHERE, wszystkie wiersze zostaną zaktualizowane.

SQLite Przykład aktualizacji

W poniższym poleceniu UPDATE zaktualizujemy DepartmentId dla Studenta ze StudentId = 6 na 3:

UPDATE Students
SET DepartmentId = 3 
WHERE StudentId = 6;

To powinno działać pomyślnie i nie powinieneś otrzymać żadnych wyników:

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

• W klauzuli WHERE przefiltrowaliśmy wszystkich uczniów, aby wybrać tylko wiersz dla StudentId = 6.
• Klauzula SET zaktualizuje wartość identyfikatora wydział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;

Teraz powinieneś zobaczyć, że wartość identyfikatora działu wynosi 3, jak pokazano poniżej:

SQLite Usunięcia

SQLite Zapytanie DELETE służy do usunięcia 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ę:

• Po klauzuli DELETE FROM należy wpisać nazwę tabeli, z której chcesz usunąć rekordy. (Uwaga: klauzula DELETE służy do usuwania wybranych rekordów z tabeli lub wszystkich rekordów i nie usuwa samej tabeli. Natomiast klauzula DROP służy do usuwania całej tabeli wraz ze wszystkimi rekordami).
• Jeśli napiszesz klauzulę DELETE w ten sposób „DELETE FROM guru”, spowoduje to usunięcie wszystkich rekordów z tabeli „guru”.
• Możesz określić warunek WHERE za pomocą wyrażenia, jeśli chcesz usunąć niektóre określone wiersze. Usunięte zostaną tylko te wiersze, dla których wyrażenie ma wartość true. 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 poleceniu usuniemy dwóch studentów o identyfikatorach StudentId 11 i 12:

DELETE FROM Students WHERE StudentId = 11 OR StudentId = 12;

Wyrażenie „StudentId = 11 OR StudentId = 12” będzie prawdziwe tylko dla studentów o identyfikatorach 11 i 12. Zatem klauzula DELETE zostanie zastosowana do obu i usunie tylko ich.

To polecenie powinno zostać pomyślnie wykonane i nie powinieneś otrzymać żadnego wyniku, takiego jak poniżej:

Możesz sprawdzić, czy obaj studenci zostali usunięci, wybierając wszystkie rekordy z tabeli Studenci w następujący sposób:

SELECT * FROM Students;

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

SQLite Klauzula konfliktu

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

Na przykład, jeśli kolumna ma ograniczenie UNIQUE i próbowałeś wstawić wartość, która już istnieje (wartość zduplikowana), co koliduje z ograniczeniem UNIQUE. Następnie klauzula KONFLIKT 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ć, czym jest transakcja bazy danych.

Transakcja w bazie danych

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

Przykład transakcji bazy 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 wpłatę ich na inne konto. Ta transakcja musi zostać całkowicie ukończona lub całkowicie anulowana i nie może zakończyć się niepowodzeniem w połowie.

Oto lista pięciu rozwiązań, które możesz wybrać w klauzuli KONFLIKT:

1. ROLLBACK – spowoduje wycofanie transakcji, w której aktualnie znajduje się SQLite instrukcja, w której występuje konflikt (spowoduje to anulowanie całej transakcji). Na przykład, jeśli próbujesz zaktualizować 10 wierszy, a piąty wiersz ma wartość sprzeczną z ograniczeniem, żadne wiersze nie zostaną zaktualizowane, a 10 wierszy pozostanie niezmienione. Zostanie zgłoszony błąd.
2. ABORT – spowoduje przerwanie (anulowanie) bieżącego SQLite tylko oświadczenie, w którym występuje konflikt, a transakcja nie zostanie anulowana. Na przykład, jeśli próbujesz zaktualizować 10 wierszy, a piąty wiersz ma wartość sprzeczną z ograniczeniem, tylko piąta wartość nie zostanie zaktualizowana, ale pozostałych 9 wierszy zostanie zaktualizowanych. Zostanie zgłoszony błąd.
3. FAIL – przerywa bieżące SQLite stwierdzenie, w którym występuje konflikt. Jednak transakcja nie będzie kontynuowana, ale poprzednie zmiany wprowadzone w wierszach poprzedzających wiersz, w którym występuje konflikt, zostaną zatwierdzone. Na przykład, jeśli próbujesz zaktualizować 10 wierszy, a piąty wiersz ma wartość sprzeczną z ograniczeniem, zaktualizowane zostaną tylko 4 wiersze, a drugi nie. Zostanie zgłoszony błąd.
4. IGNORUJ – spowoduje to pominięcie wiersza zawierającego naruszenie ograniczenia i kontynuowanie przetwarzania pozostałych następujących wierszy SQLite oświadczenie. Na przykład, jeśli próbujesz zaktualizować 10 wierszy, a piąty wiersz ma wartość sprzeczną z ograniczeniem, zaktualizowane zostaną tylko 4 wiersze, a drugi nie. Aktualizacja innych wierszy nie będzie kontynuowana i zatrzyma się na wierszu, który ma wartość konfliktu. Żaden błąd nie zostanie zgłoszony.
5. REPLACE – zależy od rodzaju ograniczenia, które narusza:
   • Gdy nastąpiło naruszenie ograniczenia UNIQUE lub PRIMARY KEY. REPLACE zastąpi wiersz powodujący naruszenie nowym wstawionym lub zaktualizowanym wierszem.
   • W przypadku naruszenia ograniczenia NOT NULL klauzula REPLACE zastąpi wartość NULL wartością domyślną tej kolumny. Jeśli kolumna nie ma wartości domyślnej, to SQLite przerwie wyciąg (wyciąg zostanie anulowany)
   • JEŻELI nastąpi naruszenie ograniczenia CHECK, klauzula zostanie przerwana.

Uwaga: Powyższe 5 rozwiązań to opcje rozwiązania konfliktu. To, co sprawdza się w przypadku jednego konfliktu, niekoniecznie sprawdzi się w przypadku innych rodzajów konfliktów.

Jak zadeklarować klauzulę KONFLIKT

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

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

PRZY KONFLIKCIE IGNORUJ Przykład

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

CREATE TABLE [Subjects] (  
    [SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE,
    [SubjectName] NVARCHAR NOT NULL
);

Zauważ, że zdefiniowaliśmy ograniczenie klucza podstawowego dla kolumny SubjectId. Ograniczenie klucza podstawowego nie pozwoli na wstawienie dwóch zduplikowanych wartości do kolumny SubjectId, dzięki czemu wszystkie wartości w tej kolumnie będą unikatowe. Zauważ również, że wybraliśmy opcję rozwiązywania konfliktów „IGNORUJ”.

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

Krok 2) Teraz wstawmy pewne wartości do nowych tematów tabeli, ale z wartością naruszającą ograniczenie klucza podstawowego:

INSERT INTO Subjects VALUES(1, 'Algebra');
INSERT INTO Subjects VALUES(2, 'Database Course');
INSERT INTO Subjects VALUES(2, 'Data Structures');
INSERT INTO Subjects VALUES(4, 'Algorithms');

W tej instrukcji INSERT próbowaliśmy wstawić dwa kursy z tym samym identyfikatorem podmiotu klucza podstawowego 2, co stanowi naruszenie ograniczenia klucza podstawowego.

Polecenia powinny działać poprawnie i nie powinieneś otrzymać żadnych błędów. Jak następuje:

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

SELECT * FROM Subjects;

Otrzymasz listę tematów:

Należy zauważyć, że wprowadzono tylko trzy przedmioty: „Algebra, Kurs baz danych i Algorithms” zamiast 4 rzędów.

Wiersz o wartości naruszającej ograniczenie klucza podstawowego, czyli „Struktury danych”, został zignorowany i nie wstawiony. Jednakże, SQLite kontynuuje wykonywanie innych instrukcji po tym wierszu.

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

DROP TABLE Subjects;

Polecenie drop usuwa całą tabelę. Tabela Tematy teraz nie istnieje.

PRZY KONFLIKCIE ZAMIEŃ Przykład

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

CREATE TABLE [Subjects] (  
    [SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
    [SubjectName] NVARCHAR NOT NULL
);

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

Zwróć też uwagę, że wybraliśmy opcję rozwiązania konfliktu „REPLACE”. Polecenie powinno zostać wykonane pomyślnie i nie powinno pojawić się żadne ostrzeżenie:

Krok 2) Teraz wstawmy pewne wartości do nowej tabeli Tematy, ale z wartością naruszającą ograniczenie klucza podstawowego:

INSERT INTO Subjects VALUES(1, 'Algebra');
INSERT INTO Subjects VALUES(2, 'Database Course');
INSERT INTO Subjects VALUES(2, 'Data Structures');
INSERT INTO Subjects VALUES(4, 'Algorithms');

W tej instrukcji INSERT próbowaliśmy wstawić dwa kursy z tym samym identyfikatorem podmiotu klucza podstawowego 2, co stanowi naruszenie ograniczenia klucza podstawowego.

Polecenia powinny działać poprawnie i nie powinieneś otrzymać żadnych błędów. Jak następuje:

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

SELECT * FROM Subjects;

Otrzymasz listę tematów:

Zwróć uwagę, że wprowadzono tylko trzy tematy: „Algebra, struktury danych i Algorithms”, podczas gdy próbowaliśmy wstawić 4 rzędy.

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

• Pierwsze dwie instrukcje wstawiania działają poprawnie i bez problemu. Dwa przedmioty Algebra i Kurs baz danych zostaną wstawione z identyfikatorami 1, 2.
• Kiedy SQLite Próbuje wykonać trzecie polecenie INSERT z SubjectId 2 i SubjectName „Struktury danych”, ale okazuje się, że istnieje już podmiot z SubjectId = 2. Stanowi to naruszenie ograniczenia klucza podstawowego zdefiniowanego w kolumnie SubjectId.
• SQLite wybierze rozwiązanie REPLACE dla tego konfliktu. Zastępuje ono wartość, która już istnieje w tabeli Subjects, nową wartością z polecenia INSERT. Zatem nazwa_przedmiotu „Kurs bazy danych” zostanie zastąpiona nazwą_przedmiotu „Struktury danych”.

FAQ