Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В процессе добавления записей в раздел библиотека SQLite. Я каким-то странным образом забыл опубликовать шестую часть темы работа с таблицами базы данных, в которой хотел рассказать про обновление и модификацию данных в таблицах баз данных под управлением SQLite. Из-за этого немного нарушится структура рубрики, так как это упущение я намерен все-таки исправить. Мы уже рассмотрели все возможные операции, которые можем совершать над таблицами базы данных SQLite и даже начали разговор про обеспечение целостности данных в базах данных. Поэтому мне осталось исправить свою невнимательность и рассказать о том, как изменять данные в таблицах базы данных на примере библиотеки SQLite3.

Модификация и обновление данных в таблицах базы данных SQLite

В этой записи мы с вами разберемся с синтаксисом операции изменения данных в таблицах и рассмотрим несколько примеров. Сперва будет пара простых примеров модификации данных в таблицах SQLite3. Затем мы посмотрим, как отфильтровать данные, перед тем, как будем их модифицировать. После чего мы с вами разберемся с тем, как изменить данные сразу в нескольких столбцах таблицы базы данных SQLite. Далее будет пример изменения значений во всех строках таблицы. И напоследок мы рассмотрим, как упорядчить ограничить количество строк для модификации данных.

Надеюсь, что публикация получилась понятной и написана она на доступном языке, но, как обычно, если есть какие-либо уточнения или что-то было непонятно — пишите в комментариях, обсудим вместе.

Изменение и модификация данных в таблицах базы данных SQLite3

Мы рассмотрели работу различных операторов манипуляции данными и операторов определения данных в базах данных SQLite, но при работе с таблицами базы данных SQLite мы можем еще изменять и модифицировать данные. Пожалуйста, не путайте операцию модификации данных в таблицах баз данных с операцией модификации таблиц, последняя выполняется при помощи команды ALTER.

Так же не стоит путать SQL команды с SQL операторами, среди русскоговорящих разработчиков принято заменять термин команда на термин оператор, хотя это и не одно и то же, если быть формально точным.  Модификация или обновления данных в базах данных SQLite выполняется командой UPDATE, хотя вы можете использовать словосочетание оператор UPDATE, большой ошибки не будет.

В SQLite можно реализовать каскадные операции по модификации данных, но об этом мы поговорим, когда будем рассматривать ограничения уровня таблицы в теме обеспечения целостности данных, сейчас нам нужно разобраться с тем, как выполняются обычные операции модификации данных в таблицах базы данных SQLite.

Синтаксис операции изменения данных в таблицах баз данных под управлением SQLite представлен на рисунке ниже. Заметим, что синтаксис модификации данных в таблицах SQLite поддерживает операции, позволяющие избежать конфликтов модификации, но об этом мы поговорим в специально отведенной теме.

Синтаксис обновления и модификации данных в таблицах баз данных SQLite3

Ключевое слово UPDATE говорит SQLite о том, что мы хотим изменить данные в таблице, далее нам нужно указать SQLite3 имя таблицы, данные которой мы будем модифицировать, в том случае когда у вас подключено несколько баз данных, вы можете использовать квалификатор или полное имя таблицы, который помимо имени таблицы содержит еще и имя базы данных этой таблицы.

После имени таблицы следует ключевое слово SET, которое позволяет указать имя столбца, в котором мы хотим изменить данные. После ключевого слова SET идет знак равно, после которого мы пишем новое значение, которое хотим поместить в базу данных. А далее самое интересное: клаузула WHERE (уточняющая фраза WHERE), с помощью которой мы задаем условия, которые позволят выбрать строки, в которых данные будут модифицированы.

Перед изменением данных в таблице SQLite позволяет эти данные упорядочить и ограничить количество строк, которые будут модифицированы. SQLite3 позволяет модифицировать данные сразу в нескольких столбцах таблицы при этом столбцы и их модфикационные значения разделяются запятой, а условия выбора строки для модификации следует после перечисления всех необходимых столбцов.

В базах данных SQLite в качестве нового значения не обязательно указывать какую-то конкретную строку или столбец, можно использовать сложные выражения и даже SQL запросы, например, вместо значения вы можете использовать команду SELECT, причем SELECT может быть сколь угодно длинным.

Используя сложные выражения вам нужно быть аккуратным, так как SQLite – СУБД с динамической типизацией данных, поэтому тип данных в SQLite не является ограничение уровня столбца, в отличии от других реляционных СУБД. И вместо строки вы легко можете записать в столбец число, а SQLite даже не предупредит вас о том, что произошла ошибка. Давайте рассмотрим несколько примеров модификации данных в базах данных SQLite.

Простые примеры модификации данных в базах данных SQLite

Начнем с простых примеров модификации данных в таблицах баз данных SQLite. Для начала нам нужно создать таблицу при помощи команды CREATE:

Мы создали таблицу table1, которая, по всей видимости, служит для хранения данных о сотрудниках компании. Данной таблице мы даже задали несколько ограничений уровня столбца, а также ограничения уровня таблицы – ограничение первичного ключа (если бы у нас было две таблицы, то, возможно, мы бы еще использовали внешний ключ, но не будем усложнять примеры).

Давайте теперь добавим строки в таблицу, в SQLite для этого используется команда INSERT:

Будьте аккуратны, добавляя строки в таблицы баз данных SQLite, хоть мы и объявили аффинированный тип данных для каждого столбца, но в SQLite понятие тип заменено на класс данных и используются классы данных лишь для сравнения значений.

Убедимся, что данные были добавлены в таблицу, сделав выборку данных из базы данных, воспользовавшись командой SELECT:

Давайте модифицируем нашу таблицу, допустим бухгалтер вышла замуж и стала Иванова, воспользуемся командой UPDATE:

Действительно, данные во второй строке таблицы были модифицированы, и мы получили значение «Людмила Иванова» вместо «Людмила Яковлева». Теперь напишем пример модификации данных в таблице базы данных SQLIte3 с использованием квалификатора. Обратите внимание: этот пример будет работать только в том случае, если вы будете работать сразу с несколькими базами данных:

Мы рассмотрели простые примеры модификации данные в базах данных под управлением SQLite, давайте перейдем к чему-нибудь более сложному.

Фильтрация данных перед их изменением в базах данных SQLite3

Иногда бывает нужно сделать фильтрацию данных перед тем, как внести изменения. Давайте посмотрим, как отфильтровать данные, а потом изменить данные в таблицах базы данных SQLite. Для этого добавим несколько строк в нашу таблицу:

Убедимся в этом, воспользовавшись SQL запросом SELECT:

Видим, что изменения в таблицы базы данных были внесены успешно и зарплата программистов действительно увеличилась на 5%, для нового значения мы использовали математическое выражение. Раз заказов стало больше, то и документооборот расширился, в компанию наняли двух бухгалтеров:

Компания заботится о воспитании молодых кадров, поэтому было принято решение повысить зарплату на 3000 рублей всем сотрудникам, не достигшим 30 лет и с зарплатой меньше 40 000 рублей. Давайте внесем изменение в нашу таблицу базы данных под управлением SQLite3:

Проверим результаты изменения данных в таблице командой SELECT:

Изменения были внесены в таблицу успешно, а для фильтрации данных мы использовали два условия, задействовав SQL  оператор AND.

Модификация данных в нескольких столбцах базы данных SQLite

Мы можем модифицировать данные сразу в нескольких столбцах таблицы базы данных. Для такой модификации нужно перечислить столбцы и их новые значения через запятую, после чего сделать фильтрацию строк. Давайте реализуем пример модификации данных в нескольких столбцах. Хитрая Оксана Кулакова вышла замуж за директора и стала главным бухгалтером: она сменила фамилию, у нее изменилась должность и повысилась зарплата в три раза, модифицируем:

Мы модифицировали успешно данные в нескольких столбцах таблицы базы данных.

Изменение всех строк в таблице базы данных SQLite

Мы посмотрели уже несколько вариантов использования команды UPDATE и уже умеем по-разному изменять данные в строках таблицы базы данных SQLite, но бывают ситуации, когда нам нужно изменить значения во всех строках таблицы базы данных, давайте внесем такое изменение.

В стране кризис, партнеры компании сокращают издержки, уменьшается объем и директору не хватает денег «на хлеб». Поэтому, скрипя зубами и двигая извилиной, он принимает решение платить серую зарплату, переводя всех сотрудников на белый оклад 10 000 рублей и черную премию, давайте изменим данные во всех строках нашей таблицы:

Убедимся в том, что значения всех строк таблицы были изменены:

Чтобы сделать изменение во всех строках таблицы нам нужно не использовать клаузулу WHERE, тогда мы сможем изменить значения сразу всех строк таблицы.

Пример сортировки строк перед модификацией данных в таблице базы данных SQLite3

Когда мы делаем модификацию данных в таблице, мы можем сначала отсортировать данные, а потом их удалить, но сортировка данных не имела бы смысла без ограничения количества строк перед их модификацией. За сортировку строк отвечает клаузула ORDER BY, за ограничение строк отвечает клаузула LIMIT.

Кризис усиливается, денег не хватает… Директор видит, что в его компании развелось этих программистов и решает оптимизировать штат, уволив самого старого, так как не видит в нем перспективы. Естественно, он не знает, как зовут программиста и не знает под каким номером программист записан в базе данных. Давайте реализуем данную операцию модификации данных при помощи сортировки и ограничения:

Мы модифицировали строку таблицы базы данных, но перед этим SQLite3 выполнила сортировку данных и затем ограничила количество модифицируемых строк до одной.

Мы рассмотрели различные способы модификации и изменения данных и строк в таблицах базы данных SQLite и на этом закончили тему работа с таблицами в базах данных SQLite.