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 у тому ж порядку, у якому визначено ці стовпці.

Тепер давайте перевіримо, що два студенти були вставлені в таблицю Students, виконавши наступне запит:

SELECT * FROM Students;

Тоді ви повинні побачити двох студентів, повернутих із цього запиту, як показано нижче:

SQLite Оновити

SQLite Запит UPDATE використовується для зміни існуючих записів у таблиці. Ви можете використовувати речення 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.

Тепер давайте перевіримо, що студент з ідентифікатором 6 оновлений, виконавши таку команду:

SELECT * FROM Students WHERE StudentId = 6;

Тепер ви повинні побачити, що ідентифікатор відділу тепер дорівнює 3, як показано нижче:

SQLite видаляти

SQLite Запит DELETE використовується для видалення наявних записів із зазначеної таблиці. Ви можете використовувати речення WHERE із запитами DELETE, щоб видалити вибрані рядки.

Речення DELETE має такий синтаксис:

• Після речення DELETE FROM потрібно написати назву таблиці, з якої потрібно видалити записи. (Примітка: речення DELETE використовується для видалення деяких записів з таблиці або всіх записів, але не для видалення самої таблиці. Однак речення DROP використовується для видалення всієї таблиці з усіма записами в ній.)
• Якщо ви напишете речення DELETE так: «DELETE FROM guru», це видалить усі записи з таблиці «guru».
• Ви можете вказати умову WHERE за допомогою виразу, якщо ви хочете видалити певні рядки. Буде видалено лише ті рядки, для яких вираз має значення true. Наприклад, «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 ми спробували вставити два курси з однаковим ідентифікатором предмета первинного ключа 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 ми спробували вставити два курси з однаковим ідентифікатором предмета первинного ключа 2, що є порушенням обмеження первинного ключа.

Команди повинні працювати добре, і ви не повинні отримати жодних помилок. Як наступне:

Крок 3) Виберіть усі предмети з таблиці наступним чином:

SELECT * FROM Subjects;

Це дасть вам список предметів:

Зверніть увагу, що було вставлено лише три предмети: «Алгебра, структури даних та Algorithms”, тоді як ми намагалися вставити 4 рядки.

Рядок, значення якого порушує обмеження первинного ключа, тобто «Структури даних», замінив значення «Курс бази даних» наступним чином:

• Перші два оператори вставки працюють без проблем. Два предмети «Алгебра» та «База даних» буде вставлено з ідентифікаторами 1, 2.
• Коли SQLite намагається виконати третій оператор вставки з SubjectId 2 та SubjectName “Data Structures“, виявляється, що вже існує тема з SubjectId = 2. Це є порушенням обмеження первинного ключа, визначеного у стовпці SubjectId.
• SQLite вибере метод REPLACE для вирішення цього конфлікту. Він замінює значення, яке вже існує в таблиці subjects, новим значенням з оператора insert. Таким чином, SubjectName з таблиці «Курс бази даних» буде замінено на SubjectName з таблиці «Структури даних».

Поширені запитання