SQLite INSERT, UPDATE, DELETE Заявка с пример

Вмъкване, актуализиране и изтриване на заявка в SQLite

Клаузите за промяна на данни в SQLite са оператори INSERT, UPDATE и DELETE. Използва се за вмъкване на нови редове, актуализиране на съществуващи стойности или изтриване на редове от базата данни.

Обърнете внимание, че за всички следващи примери трябва да стартирате sqlite3.exe и да отворите връзка към примерната база данни като текуща:

Стъпка 1) В този етап,

• Отворете „Моят компютър“ и отидете в следната директория „C:\sqlite“ и
• След това отворете „sqlite3.exe“:

Стъпка 2) Отворете базата данни „TutorialsSampleDB.db“ със следната команда:

.open TutorialsSampleDB.db

Сега сте готови да изпълните всякакъв тип заявка в базата данни.

SQLite INSERT

SQLite INSERT се използва за вмъкване на записи в определена таблица на базата данни. трябва да използвате клаузата „INSERT“. Синтаксисът на клаузата INSERT е както следва:

• След клаузата INSERT трябва да посочите в коя таблица трябва да вмъкнете стойностите.
• След името на таблицата пишете списъка с колони, в които искате да вмъкнете стойностите.
• Можете да игнорирате името на колоните и да не пишете в тях.
• Ако не напишете името на колоните, стойностите ще бъдат вмъкнати във всички колони, намерени в таблицата, в същия ред, колоните са дефинирани в таблицата.
• След клаузата VALUES трябва да посочите стойностите, които да бъдат вмъкнати.
• Всяка клауза INSERT вмъква само един ред. Ако искате да вмъкнете няколко реда, трябва да напишете няколко клаузи INSERT, по една за всеки ред.

SQLite Вмъкване на пример

В следващия пример ще вмъкнем 2 реда в таблицата на учениците, по един за всеки ученик:

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

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

Това трябва да работи успешно и няма резултат за това:

Това ще вмъкне двама студенти:

• Първият студент с StudentId=11, StudentName = Ahmad, DepartmentId = 4 и DateOfBirth = 1997-10-12.
• Вторият студент със StudentId=12, StudentName=Aly, DepartmentId=4 и DateOfBirth=1996-10-12'.

В първото изречение изброихме имената на колоните „StudentId, StudentName, DepartmentId, DateOfBirth“. Във второто изречение обаче не го направихме.

Четирите стойности „12, 'Aly', 4, '1996-10-12'“ ще бъдат вмъкнати във всичките четири колони на таблицата „Студенти“ в същия ред, в който са дефинирани колоните.

Сега нека проверим дали двамата студенти са били вмъкнати в таблицата Students, като изпълним следното заявка:

SELECT * FROM Students;

След това трябва да видите двамата студенти, върнати от тази заявка, както следва:

SQLite Актуализация

SQLite UPDATE Query се използва за модифициране на съществуващите записи в таблица. Можете да използвате клауза WHERE със заявка UPDATE, за да актуализирате избрани редове. Клаузата UPDATE актуализира таблица чрез промяна на стойност за конкретна колона. Следва синтаксисът на клаузата UPDATE:

Както следва:

• След „клаузата за актуализиране“ трябва да напишете името на таблицата, която да актуализирате.
• Трябва да напишете „клауза SET“, която се използва за запис на името на колоната за актуализиране и стойността за актуализиране.
• Можете да актуализирате повече от една колона. Можете да използвате запетая между всеки ред.
• Можете да посочите клауза WHERE, за да посочите само някои редове. Само редовете, които изразът оценява като true, се актуализират. Ако не сте посочили клауза WHERE, всички редове ще бъдат актуализирани.

SQLite Пример за актуализиране

В следния оператор UPDATE ще актуализираме DepartmentId за ученика с StudentId = 6 на 3:

UPDATE Students
SET DepartmentId = 3 
WHERE StudentId = 6;

Това трябва да работи успешно и не трябва да получавате никакви резултати:

В клаузата UPDATE уточнихме, че искаме да актуализираме таблицата Students.

• В клаузата WHERE филтрирахме всички ученици, за да изберем само реда за StudentId = 6.
• Клаузата SET ще актуализира стойността на идентификатора на отдела за избраните студенти, за да бъде 3.

Сега нека проверим дали студентът с ID 6 е актуализиран, като изпълним следната команда:

SELECT * FROM Students WHERE StudentId = 6;

Сега трябва да видите, че стойността на идентификатора на отдел вече е 3, както следва:

SQLite Изтрий

SQLite Заявката DELETE се използва за премахване на съществуващи записи от определена таблица. Можете да използвате клаузата WHERE със заявки DELETE, за да изтриете избраните редове.

Клаузата DELETE има следния синтаксис:

• Трябва да напишете име на таблица след клаузата DELETE FROM, от която искате да изтриете записи. (Забележка: клаузата DELETE се използва за изтриване на някои записи от таблица или за изтриване на всички записи и няма да изтрие самата таблица. Клаузата DROP обаче се използва за изтриване на цялата таблица с всички записи в нея.)
• Ако напишете клаузата DELETE по следния начин „DELETE FROM guru“, това ще изтрие всички записи от таблицата „guru“.
• Можете да зададете условие WHERE с израз, ако искате да изтриете някои конкретни редове. Ще бъдат изтрити само редовете, за които изразът е верен. Например „DELETE FROM guru WHERE id > 5“ – това ще изтрие само записите, които имат id по-голям от 5.

Пример

В следното изявление ще изтрием двама студенти с StudentId 11 и 12:

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

Изразът „StudentId = 11 ИЛИ StudentId = 12“ ще бъде верен само за студенти с идентификатори 11 и 12. Така клаузата DELETE ще бъде приложена и към двата и ще изтрие само тях.

Тази команда трябва да се изпълни успешно и не трябва да получавате изход, както следва:

Можете да проверите дали двамата студенти са били изтрити, като изберете всички записи от таблицата Студенти, както следва:

SELECT * FROM Students;

Не трябва да виждате двамата студенти с идентификатори 11 и 12, както следва:

SQLite Конфликтна клауза

Да предположим, че имате колона, която има едно от следните ограничения за колони: UNIQUE, NOT NULL, CHECK или PRIMARY KEY. И тогава сте се опитали да вмъкнете или актуализирате стойност в тази колона със стойност, която е в конфликт с това ограничение.

Например, ако колона има ограничение UNIQUE и сте се опитали да вмъкнете стойност, която вече съществува (дублирана стойност), което е в конфликт с ограничението UNIQUE. Тогава клаузата CONFLICT ви позволява да изберете какво да правите в такива случаи, за да разрешите този конфликт.

Преди да продължим да обясняваме как клаузата CONFLICT разрешава конфликта. Трябва да разберете какво е транзакция на база данни.

Транзакция с база данни

Терминът транзакция на база данни е списък от SQLite операции (вмъкване, актуализиране или изтриване). Транзакцията на базата данни трябва да се изпълни като една единица, или всички операции да бъдат изпълнени успешно, или изобщо да не бъдат изпълнени. Всички операции ще бъдат отменени, ако една от тях не успее да се изпълни.

Пример за транзакция на база данни

Транзакцията за прехвърляне на пари от една банкова сметка в друга ще включва няколко дейности. Тази транзакционна операция включва теглене на пари от първата сметка и депозирането им в друга сметка. Тази транзакция трябва да бъде напълно завършена или напълно анулирана и да не се провали наполовина.

Ето списъка с пет решения, които можете да изберете в клаузата CONFLICT:

1. ОТКАЗВАНЕ – това ще отмени транзакцията, в която е текущата SQLite изявление, което има конфликт (ще анулира цялата транзакция). Например, ако се опитвате да актуализирате 10 реда и петият ред има стойност, която е в конфликт с ограничение, тогава нито един ред няма да бъде актуализиран, 10-те реда ще останат същите. Ще бъде изведена грешка.
2. ПРЕКРАТИ – това ще прекъсне (отмени) текущото SQLite само изявление, което има конфликт и транзакцията няма да бъде анулирана. Например, ако се опитвате да актуализирате 10 реда и петият ред има стойност, която е в конфликт с ограничение, тогава само петата стойност няма да бъде актуализирана, но останалите 9 реда ще бъдат актуализирани. Ще бъде изведена грешка.
3. FAIL – прекъсва текущото SQLite изявление, което има конфликт. Транзакцията обаче няма да продължи, но предишните промени, направени в редове преди реда, който има конфликт, ще бъдат ангажирани. Например, ако се опитвате да актуализирате 10 реда и петият ред има стойност, която е в конфликт с ограничение, тогава само 4 реда ще бъдат актуализирани, а другият няма. Ще бъде изведена грешка.
4. ИГНОРИРАЙ – това ще пропусне реда, който съдържа нарушението на ограничението, и ще продължи обработката на следващите редове на SQLite изявление. Например, ако се опитвате да актуализирате 10 реда и петият ред има стойност, която е в конфликт с ограничение, тогава само 4 реда ще бъдат актуализирани, а другият няма. Няма да продължи, за да актуализира други редове и да спре на реда, който има стойността на конфликта. Няма да бъде изведена грешка.
5. ЗАМЕСТВАНЕ – зависи от типа ограничение, което има нарушение:
   • Когато има нарушение на ограничението за ограничението UNIQUE или PRIMARY KEY. REPLACE ще замени реда, който причинява нарушението, с новия вмъкнат или актуализиран ред.
   • Когато има нарушение на ограничението NOT NULL, клаузата REPLACE ще замени NULL стойността със стойността по подразбиране на тази колона. Ако колоната няма стойност по подразбиране, тогава SQLite ще прекъсне изявлението (изявлението ще бъде отменено)
   • АКО възникне нарушение на ограничението CHECK, клаузата ще бъде прекратена.

Забележка: Горните 5 решения са варианти за това как искате да разрешите конфликта. Не е задължително това, което е приложимо за разрешаване на един конфликт, да е приложимо и за разрешаване на други видове конфликти.

Как да декларирам клаузата CONFLICT

Можете да декларирате клаузата ON CONFLICT, когато дефинирате ограничение за дефиниция на колона в рамките на клаузата CREATE TABLE. Използвайки следния синтаксис:

Можете да изберете една от петте решения, за да разрешите конфликта, както е обяснено по-горе.

ПРИ ИГНОРИРАНЕ НА КОНФЛИКТ Пример

Стъпка 1) Създайте нова тема на таблицата, както следва:

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

Обърнете внимание, че сме дефинирали ограничение PRIMARY KEY за колоната SubjectId. Ограничението за първичен ключ няма да позволи вмъкването на две дублирани стойности в колоната SubjectId, така че всички стойности в тази колона трябва да бъдат уникални. Също така, обърнете внимание, че сме избрали разрешаване на конфликти да бъде „IGNORE“.

Командата трябва да се изпълни успешно и не трябва да получавате грешки:

Стъпка 2) Сега нека вмъкнем някои стойности в новите теми на таблицата, но със стойност, която нарушава ограничението на първичния ключ:

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');

В тези оператори INSERT се опитахме да вмъкнем два курса с един и същи първичен ключ Subject id 2, което е нарушение на ограничението на първичния ключ.

Командите трябва да работят добре и не трябва да получавате грешки. Както следва:

Стъпка 3) Изберете всички предмети от таблицата, както следва:

SELECT * FROM Subjects;

Това ще ви даде списък с теми:

Обърнете внимание, че са добавени само три предмета „Алгебра, Курс по бази данни и Algorithms” вместо 4 реда.

Редът, който има стойност, която нарушава ограничението на първичния ключ, което е „Структури на данни“, беше игнориран и не беше вмъкнат. обаче SQLite продължава да изпълнява други оператори след този ред.

Стъпка 4) ИЗТРИЙТЕ субектите на таблицата, за да я създадете отново с различна клауза ON CONFLICT за следния пример, като изпълните следната команда:

DROP TABLE Subjects;

Командата drop изтрива цялата таблица. Таблица Теми сега не съществува.

ПРИ КОНФЛИКТ ЗАМЕНИТЕ Пример

Стъпка 1) Създайте нова тема на таблицата, както следва:

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

Забележете, че дефинирахме ограничение PRIMARY KEY в колоната SubjectId. Ограничението на първичния ключ няма да позволи две дублирани стойности да бъдат вмъкнати в колоната SubjectId, така че всички стойности в тази колона да бъдат уникални.

Също така, обърнете внимание, че избираме опция за разрешаване на конфликти „ЗАМЕСТВАНЕ“. Командата би трябвало да се изпълни успешно и не би трябвало да получавате никакви грешки:

Стъпка 2) Сега нека вмъкнем някои стойности в новата таблица Subjects, но със стойност, която нарушава ограничението на първичния ключ:

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');

В тези оператори INSERT се опитахме да вмъкнем два курса с един и същи първичен ключ Subject id 2, което е нарушение на ограничението на първичния ключ.

Командите трябва да работят добре и не трябва да получавате грешки. Както следва:

Стъпка 3) Изберете всички предмети от таблицата, както следва:

SELECT * FROM Subjects;

Това ще ви даде списък с теми:

Обърнете внимание, че са въведени само три предмета „Алгебра, структури от данни и Algorithms”, докато ние се опитахме да вмъкнем 4 реда.

Редът, който има стойността, нарушаваща ограничението на първичния ключ, а именно „Структури от данни“, замени стойността „Курс на базата данни“, както следва:

• Първите два оператора за вмъкване работят добре без проблем. Ще бъдат вмъкнати два предмета Алгебра и Курс по база данни с идентификатори 1, 2.
• Кога SQLite При опит за изпълнение на третата команда за вмъкване с SubjectId 2 и SubjectName „Структури от данни“ установява, че вече съществува subject с SubjectId = 2. Което е нарушение на ограничението за първичен ключ, дефинирано в колоната SubjectId.
• SQLite ще избере REPLACE решение за този конфликт. Заменя стойността, която вече съществува в таблицата subjects, с новата стойност от оператора insert. Така „Database Course“ SubjectName ще бъде заменено с „Data Structures“ SubjectName.

Въпроси и Отговори