SQL

1. SQL

2. SQL

• Википедия гласит, что SQL — это декларативный язык программирования, применяемый
для создания, модификации и управления данными в реляционной базе данных,
управляемой соответствующей системой управления базами данных. Не самое
удобоваримое определение.
• Декларативный язык программирования говорит, что должно быть сделано, а не как это
необходимо сделать. Ещё один пример декларативного языка — HTML. Рассмотрим такой
код:
• <div class=”className”> <input type=”button” value=”Ясно. Понятно.”></input> </div>
• С его помощью мы заявляем (declaration — заявление) браузеру, что хотим увидеть блок с
классом className и кнопкой с текстом «Ясно. Понятно.» внутри. Для этого мы не
создаём каких-либо переменных, циклов, условий. Мы знаем, что браузер нас понял, сам
разберёт команду и вернёт результат или ошибку.
• Здесь смысл довольно прост: мы даём команду и получаем результат. Мы не описываем,
как эту команду выполнять. Чтобы понять, что такое реляционная база данных, разберём,
что такое база данных в принципе. Декомпозируем это понятие на «база» и «данные».

3. Данные

• В контексте баз данных под данными понимают набор значений, который собирается
в строки и столбцы, тем самым представляя таблицу. Представим, что у нас есть
каталог мебельного магазина. Нам нужно сохранить все данные из раздела «Шкафы»
этого каталога в таблицу. Мы решили, что все шкафы отличаются друг от друга
характеристиками:
• название производителя;
• название модели;
• высота;
• длина;
• цвет;
• количество дверей.
• Составим таблицу и вобьём в неё выдуманные данные.

4.

5. Данные

• У нас есть таблица с данными. Столбцами мы показываем, как они будут
храниться. В примере я указал, что мы будем хранить информацию в
структуре: производитель, модель, высота, длина, цвет, количество дверей.
Иными словами, я создал структуру таблицы.
• Добавляя в таблицу строки, я вводил в неё данные, ориентируясь на
структуру, заданную в столбцах. Чем больше строк, тем больше данных. Чем
больше столбцов, тем подробнее будут эти данные.
• Ещё есть такое понятие, как «значение» — это пересечение столбца и строки.
Например, у последней строки в столбце «Цвет» написано «хаки». Здесь
«хаки» — значение. Если мы начнём группировать таблицы и добавим
возможность манипулирования ими, то получим базу данных.

6. Теперь про базы

• Получается, что БД — это совокупность данных, представленных
определённым образом (в нашем случае — таблицей), и набор инструментов
для манипулирования ими.
• Данные могут быть сгруппированы не только в таблицы, но и в коллекции. У
каждой базы есть свой инструмент для создания таблиц/коллекций,
добавления, удаления или изменения данных, а также для составления
выборки. В статье мы рассмотрим базы, которые состоят из таблиц, а
инструментом манипулирования данными будет язык SQL.
• Таблицы между собой могут объединяться в схемы — в одной базе данных их
может быть несколько, а может и не быть деления на схемы вообще. Это
зависит от БД.

7.

• Вернёмся к определению из Википедии и вспомним про слово
«реляционные». Реляционные (от англ. relation — отношения) — это базы
данных, таблицы которых могут выстраиваться в различных отношениях.
Возьмём предыдущий пример и добавим в него тех самых «отношений».
Создадим таблицу «Производитель», а ту, что в примере, обозначим как
«Каталог».
• Таблица «Производитель»:

8. Теперь таблицу «Каталог» можно оформить в другом виде:

9.

• Получилось так, что у таблиц «Каталог» и «Прозводитель» появились
отношения. Значения из столбца «Каталог» ссылаются на строки из таблицы
«Производитель». Добавлением отношения мы решили нескольких проблем:
• Избавились от избыточных данных. Каталог стал занимать меньше места.
Вместо хранения целой строки мы используем только номер строки из
таблицы «Производитель».
• Снизили вероятность ошибиться. При смене названия производителя нам
достаточно отредактировать строку в таблице «Производитель», «Каталог»
останется без изменений.

10.

• Это не все проблемы, которые мы решили добавлением отношений. Для
понимания других проблем необходимо углубиться в тему баз данных.
Разделение данных на таблицы с отношениями — это процесс нормализации.
Так можно достигать различных нормализованных форм данных. При
достижении каждой из нормализованных форм мы избавляем данные от
дополнительных проблем.

11. Вернёмся к SQL

• Если читателю показалось, что мы ушли в сторону от SQL, так оно и есть. Но
очень трудно понять, что такое SQL, не зная, с чем он работает.
• Выходит, что SQL — это язык запросов , необходимый для написания команд
к БД, после выполнения которых она вернёт результат. Результат будет
зависеть от команды, написанной на SQL. в SQL есть операторы для работы с
данными, из которых складываются команды. Операторы распределены по
четырём языкам:

12.

• DDL — Data Definition Language;
• DML — Data Manipulation Language;
• DCL — Data Control Language;
• TCL — Transaction Control Language.

13. DDL 

DDL
• DDL (Data Definition Language, язык описания данных) — язык, включающий
операторы для работы со структурой данных. Операторы DDL нужны для
реализации этих возможностей:
• Создание объектов базы данных (таблиц, схем). Оператор: CREATE.
• Удаление объектов базы данных. Оператор: DROP.
• Изменение объектов базы данных. Оператор: ALTER.
• DDL используется, когда нужно создать структуру для хранения данных. Он
не отвечает за сами данные — только за то, как они будут разделены по
таблицам и схемам.

14. DML

• DML (Data Manipulation Language, язык манипуляции данными) — язык,
который нужен для добавления, удаления, изменения данных и для выборки
их из базы. Иными словами, для манипулирования данными. Пройдёмся по
операторам:
• Оператор SELECT позволяет выбрать данные.
• Оператор INSERT — добавить новые.
• Оператор UPDATE — изменить существующие.
• Оператор DELETE — удалить.

15. DCL

• DCL (Data Control Language, язык управления доступом к данным) — набор
операторов, необходимых для предоставления доступа к данным. Кроме
данных, в БД есть такие сущности, как пользователи. Нужно обязательно
иметь возможность ограничить пользователям доступ к данным. Например,
мы не хотим, чтобы менеджер проекта мог редактировать данные или их
структуру. Для этого есть три группы операторов.
• GRANT — оператор предоставления пользователю или группе набор каких-
либо разрешений;
• REVOKE — оператор отзыва разрешений;
• DENY — задаёт запрет. Приоритет оператора DENY выше, чем у разрешения,
выданного оператором GRANT.

16. TCL

• Есть такое понятие, как транзакции. Это набор команд (там может быть и всего одна),
который завершается успешно тогда, когда правильно выполнены все команды из
него. В случае неудачного завершения одной команды из транзакции, она вся
откатывается (отменяются результаты выполнения предыдущих команд), реализуя
принцип атомарности. Обычно в транзакцию включаются DML-команды.
• Для управления транзакциями существует TCL (Transaction Control Language — язык
управления транзакциями). Операторы здесь следующие:
• BEGIN TRANSACTION — необходим для обозначения начала транзакции;
• COMMIT TRANSACTION — применяет изменения команд внутри транзакции;
• ROLLBACK TRANSACTION — откатывает транзакцию;
• SAVE TRANSACTION — указывает промежуточную точку сохранения внутри
транзакции.
• TCL есть только в тех БД, которые поддерживают транзакции.

17. Виды СУБД

• Познакомимся с новым понятием — СУБД, системой управления базой данных.
• Сергей Кузнецов в книге «Основы баз данных» описал СУБД как комплекс программ,
позволяющих создать базу данных (БД) и манипулировать данными (вставлять,
обновлять, удалять и выбирать). Система обеспечивает безопасность, надёжность
хранения и целостность данных, а также предоставляет средства для
администрирования БД.
• Получается что, СУБД — это SQL плюс комплекс программного обеспечения. Очень
часто базы данных путают с системой управления базой данных. Это нормально:
понятия неразрывны, сама по себе БД без системы управления мало чем отличается
от текстового файла со строчками. Важно не только хранить данные, но и управлять
ими. СУБД применяются везде, где нужно структурировано хранить данные — от
простого блога до проектов Data Science.

18. MySQL

• MySQL — свободная реляционная СУБД. Разрабатывалась как легковесная
замена тяжёлым СУБД, которую можно было установить на маломощный
сервер, без сильных потерь в возможностях. MySQL трудится под капотом
таких гигантов, как YouTube, Facebook, Twitter, GitHub.
• СУБД написана на C и C++. MySQL породил множество ответвлений, которые
сейчас стали самостоятельными СУБД, например Percona и MariaDB.

19. Oracle Database

• История Oracle Database начинается с 1977 года. Это объектно-реляционная
система управления данными. Это довольно тяжёлая СУБД, поддерживает
системы любой сложности, например, в банковской или финансовой сферах.
У неё нет бесплатной лицензии. Процедурный SQL — PL/SQL. Языки
написания СУБД — Java/C/С++

20. Microsoft SQL Server

• Microsoft SQL Server — система управления реляционными базами данных,
разработанная Microsoft. Первая версия SQL Server появилась 29 апреля 1989
года. Это конкурент Oracle Database. Есть бесплатная лицензия для
разработчиков, но не для коммерческого использования. Процедурный SQL
— Transact-SQL. СУБД написана на C/C++/C#.

21. PostgreSQL

• PostgreSQL — это самая продвинутая РСУБД, ориентирующаяся в первую очередь на
полное соответствие стандартам и расширяемость. PostgreSQL, или Postgres,
пытается полностью соответствовать SQL-стандартам ANSI/ISO.
• PostgreSQL отличается от других РСУБД тем, что обладает объектно-
ориентированным функционалом, в том числе полной поддержкой концепта
ACID (Atomicity, Consistency, Isolation, Durability).
• Будучи основанным на мощной технологии Postgres отлично справляется с
одновременной обработкой нескольких заданий. Поддержка конкурентности
реализована с использованием MVCC (Multiversion Concurrency Control), что также
обеспечивает совместимость с ACID.
• Хотя эта РСУБД не так популярна, как MySQL, существует много сторонних
инструментов и библиотек для облегчения работы с PostgreSQL.
• .

22. Преимущества Недостатки

• Полная SQL-совместимость.
• Сообщество: PostgreSQL поддерживается опытным сообществом 24/7.
• Поддержка сторонними организациями: несмотря на очень продвинутые функции, PostgreSQL
используется в многих инструментах, связанных с РСУБД.
• Расширяемость: PostgreSQL можно программно расширить за счёт хранимых процедур.
• Объектно-ориентированность: PostgreSQL — не только реляционная, но и объектно-
ориентированная СУБД.
• Производительность: В простых операциях чтения PostgreSQL может уступать своим соперникам.
• Популярность: из-за своей сложности инструмент не очень популярен.
• Хостинг: из-за вышеперечисленных факторов проблематично найти подходящего провайдера.

23. Когда стоит использовать PostgreSQL

Когда стоит использовать PostgreSQL
• Целостность данных: если приоритет стоит на надёжность и целостность
данных, PostgreSQL — лучший выбор.
• Сложные процедуры: если ваша БД должна выполнять сложные процедуры,
стоит выбрать PostgreSQL в силу её расширяемости.
• Интеграция: если в будущем вам предстоит перемещать всю базу на другое
решение, меньше всего проблем возникнет с PostgreSQL.

24. Когда не стоит использовать PostgreSQL

Когда не стоит использовать PostgreSQL
• Скорость: если всё, что нужно — это быстрые операции чтения, не стоит
использовать PostgreSQL.
• Простые ситуации: если вам не требуется повышенная надёжность,
поддержка ACID и всё такое, использование PostgreSQL — это стрельба из
пушки по мухам.

25. SQLite

• SQLite — это изумительная библиотека, встраиваемая в приложение, которое
её использует. Будучи файловой БД, она предоставляет отличный набор
инструментов для более простой (в сравнении с серверными БД) обработки
любых видов данных.
• Когда приложение использует SQLite, их связь производится с помощью
функциональных и прямых вызовов файлов, содержащих данные (например,
баз данных SQLite), а не какого-то интерфейса, что повышает скорость и
производительность операций.

26. Поддерживаемые типы данных

• NULL: NULL-значение.
• INTEGER: целое со знаком, хранящееся в 1, 2, 3, 4, 6, или 8 байтах.
• REAL: число с плавающей запятой, хранящееся в 8-байтовом формате IEEE.
• TEXT: текстовая строка с кодировкойUTF-8, UTF-16BE или UTF-16LE.
• BLOB: тип данных, хранящийся точно в таком же виде, в каком и был
получен.

27. Преимущества

• Файловая: вся база данных хранится в одном файле, что облегчает
перемещение.
• Стандартизированная: SQLite использует SQL; некоторые функции
опущены (RIGHT OUTER JOIN или FOR EACH STATEMENT), однако, есть и
некоторые новые.
• Отлично подходит для разработки и даже тестирования: во время этапа
разработки большинству требуется масштабируемое решение. SQLite, со
своим богатым набором функций, может предоставить более чем
достаточный функционал, при этом будучи достаточно простой для работы
с одним файлом и связанной сишной библиотекой.

28. Недостатки

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

29. Когда стоит использовать SQLite

Когда стоит использовать SQLite
• Встроенные приложения: все портируемые не предназначенные для
масштабирования приложения — например, локальные
однопользовательские приложения, мобильные приложения или игры.
• Система доступа к дисковой памяти: в большинстве случаев приложения,
часто производящие прямые операции чтения/записи на диск, можно
перевести на SQLite для повышения производительности.
• Тестирование: отлично подойдёт для большинства приложений, частью
функционала которых является тестирование бизнес-логики.

30. Когда не стоит использовать SQLite

• Многопользовательские приложения: если вы работаете над приложением,
доступом к БД в котором будут одновременно пользоваться несколько
человек, лучше выбрать полнофункциональную РСУБД — например, MySQL.
• Приложения, записывающие большие объёмы данных: одним из
ограничений SQLite являются операции записи. Эта РСУБД допускает
единовременное исполнение лишь одной операции записи.

31.

32. Подключение к БД, создание и удаление таблиц

• В данном случае – это файл saper.db, который должен располагаться в том же
каталоге, что и файл программы на Питоне. В качестве расширений этого
файла, обычно, используют следующие:
• *.db, *.db3, *.sqlite и *.sqlite3
• При выполнении команды connect файл saper.db либо будет открыт, либо
будет создан, если он не существует. В результате создается (или
открывается) БД с именем saper.db.
• При успешном соединении с БД метод connect возвращает экземпляр
объекта Connection, на который ссылается переменная con. И, далее, мы
должны использовать объект Cursor для взаимодействия с БД и выполнения
SQL-запросов. Например, это можно сделать с помощью метода execute,
которому в качестве аргумента как раз и передается строка с SQL-запросом.

33.

• Однако, соединяться с БД лучше все-таки через менеджер контекста:
• Он автоматически сохраняет данные в БД (вызывает метод commit()) даже
при возникновении ошибочных ситуаций. Конечно, после него, когда
соединение с БД уже не нужно, его нужно закрыть все той же командой
close().

34.

Создание и удаление таблиц
• Мы здесь указываем команду CREATE TABLE (создать таблицу), затем, имя
таблицы – users и, далее, в круглых скобках через запятую перечисляем поля
с указанием их типов. Названия команд и типов принято писать заглавными
буквами, а имена таблиц и их полей – строчными.

35. Создание и удаление таблиц

• Запустим программу, снова откроем БД в DB Browser и увидим созданную
таблицу заданной структуры. Это будет наше первое хранилище данных.
• Однако, смотрите, если запустить программу еще раз, то появится ошибка,
т.к. мы пытаемся создать таблицу, которая уже существует. Поэтому SQLзапрос лучше записать в таком виде:

36.

• То есть, создавать таблицу только если она не существует. Теперь, запуская
программу, никаких ошибок появляться не будет и, кроме того, мы точно
будем уверены, что таблица users присутствует в нашей БД.
• Давайте в программе DB Browser добавим несколько записей. Для этого
нужно нажать на кнопку «Добавить запись» и ввести значения:
• Алексей
Миша
Федор
Маша
1
1
1
2
22
19
26
18
1000
800
1100
1500
• После этого обязательно нажать на кнопку «Записать изменения» и данные
будут сохранены в БД.

37.

• Затем, в этой же программе на вкладке «SQL», мы можем прописывать SQL-
запросы и выполнять их. В качестве примера выполним очень
распространенную команду SELECT для выбора записей из таблицы users. В
самом простом варианте это можно сделать так:
• Здесь * указывает взять все поля из таблицы users. Подробнее мы еще
поговорим об этом запросе, а пока я покажу следующее. Каждая таблица
SQLite содержит скрытое поле rowid, хранящее уникальный идентификатор
записи. Выведем его на экран с помощью запроса:

38.

• Используя rowid, в частности, можно осуществлять связывание нескольких
таблиц между собой для формирования сводного отчета. И в дальнейшем мы
увидим как это делается.
• Наконец, если требуется удалить таблицу, то для этого прописывается такой
SQL-запрос:

39.

PRIMARY KEY, AUTOINCREMENT, NOT
NULL и DEFAULT
• При необходимости можно самостоятельно создать поле подобное rowid в
любой таблице. Для этого, при создании ее структуры, указывается
следующая строчка:
• user_id INTEGER PRIMARY KEY AUTOINCREMENT
• Здесь ограничитель PRIMARY KEY (первичный ключ) означает, что поле
user_id должно содержать уникальные значения, а ограничитель
AUTOINCREMENT указывает СУБД автоматически увеличивать значение
user_id при добавлении новой записи.

40. PRIMARY KEY, AUTOINCREMENT, NOT NULL и DEFAULT

Команды SELECT и INSERT
• INSERT – добавление записи в таблицу;
• SELECT – выборка данных из таблиц (в том числе и при создании сводной
выборки из нескольких таблиц).
• INSERT
• Начнем с первой команды – добавления записей. Ее синтаксис следующий:
• INSERT INTO <table_name> (<column_name1>, <column_name2>, ...) VALUES
(<value1>, <value2>, …)
• или так:
• INSERT INTO <table_name> VALUES (<value1>, <value2>, …)

41. Команды SELECT и INSERT

• Здесь table_name – имя таблицы, за которым в круглых скобках указываются
столбцы (поля), в которые будет происходить добавление информации при
создании новой записи. Остальные столбцы будут принимать или значение
NULL, или значение по умолчанию, если в структуре поля был указан
ограничитель DEFAULT. Во втором варианте можно не перечислять поля
таблицы, тогда предполагается, что после ключевого слова VALUES будут
указаны данные для каждого поля по порядку, начиная с первого и до
последнего.

42.

• Давайте посмотрим на работу SQL-запроса в программе DB Browser. Откроем
нашу БД saper.db и выполним следующий запрос:
• Или так

43.

SELECT
• Следующая команда наиболее часто используется при составлении SQL-
запросов. Она отвечает за выборку данных из таблицы. В самом простом
случае она записывается по такому синтаксису:
• SELECT col1, col2, … FROM <table_name>
• На выходе получим выборку из трех столбцов и набора всех записей из
таблицы users:

44. SELECT

• Если нужно выбрать все столбцы, то вместо их перебора можно просто
записать звездочку:

45.

Оператор WHERE
• Если нам нужно добавить фильтр для выбираемых записей, то это делается с
помощью ключевого слова WHERE, которое записывается после имени
таблицы:
• SELECT col1, col2, … FROM <table_name> WHERE <условие>
• Например, отберем все записи со значением очков меньше 1000:

46. Оператор WHERE

• Как видите из этого примера, после слова WHERE записывается логическое
выражение и в качестве сравнения можно использовать следующие операторы:
• = или ==, >, <, >=, <=, !=, BETWEEN
• Выбираются записи с игроками возрастом 32 года. Кстати, вместо обычного равно
также можно записывать и два равно. Это будет одно и то же. Два равно введено для
привычки программистов записывать оператор сравнения в таком виде, т.к. одно
равно во многих языках программирования – это оператор присваивания. Но в SQL =
и == это операторы сравнения и можно использовать любой из них.

47.

Составные условия
• Часто при описании фильтра требуется учитывать значения сразу нескольких
столбцов. Например, выбрать всех игроков старше 20 лет и с числом очков более 300.
Здесь уже нужно использовать составное условие. Для этого дополнительно
используются следующие ключевые слова:
• AND – условное И: exp1 AND exp2. Истинно, если одновременно истинны exp1 и exp2.
• OR – условное ИЛИ: exp1 OR exp2. Истинно, если истинно exp1 или exp2 или оба
выражения.
• NOT – условное НЕ: NOT exp. Преобразует ложное условие в истинное и, наоборот,
истинное – в ложное.
• IN – вхождение во множество значений: col IN (val1, val2, …)
• NOT IN – не вхождение во множество значений: col NOT IN (val1, val2, …)

48. Составные условия

• Выберет все записи из таблицы users. Здесь фильтр будет работать так:
выбираются игроки возрастом 19 или 32 года и числом очков более 300 или
те, у которых мужской пол (sex = 1). И, так как у нас все игроки имеют мужской
пол, то все они и будут отображены в результатах отбора. Этот пример
показывает важность приоритетов: приоритет у операции AND выше, чем у
OR, поэтому AND выполняется раньше OR.

49.

• Если нам нужно записать условие выбора игроков возрастом 19 или 32 года
или мужского пола и, вместе с тем, чтобы число очков у них было более 300,
то следует использовать круглые скобки для OR:

50.

• То есть, круглые скобки меняют приоритет и сначала выполняется проверка
по OR, а затем, результат сравнивается по AND.
• Самый высокий приоритет имеет операция NOT, она выполняется в первую
очередь. Например:

51.

Сортировка ORDER BY
• После условия в команде SELECT можно дополнительно указывать
сортировку записей по определенному столбцу. Предположим, что мы хотим
выбрать всех игроков с числом очков менее 1000 и отсортировать их по
возрастанию возраста. Это можно сделать так:
• Здесь после оператора ORDER BY указывается поле, по которому
производится сортировка записей в выборке. По умолчанию сортировка
делается по возрастанию:

52. Сортировка ORDER BY

• Если нужно отсортировать данные по убыванию, то после имени поля
следует указать флаг DESC:
• Кстати, если нужно явно показать, что сортировка производится по
возрастанию, то можно записать флаг ASC:

53.

Ограничение выборки LIMIT
• В реальных таблицах число записей может быть очень большим. Например, число
игроков может достигать тысяч или даже десятков тысяч. И, представьте, что из всего
этого числа нам нужно взять первых 10 игроков с наибольшим числом очков, чтобы
сформировать ТОП-10 лучших. Для этого в команде SELECT используется еще один
оператор – LIMIT, который записывает в самом конце и имеет следующие синтаксисы:
• LIMIT <max> [OFFSET offset]
• LIMIT <offset, max>
• Для демонстрации работы этого оператора, добавим в таблицу еще несколько записей:
• Мария
2 18 400
Сергей 1 33 2000
Владимир 1 43 100
Елена
2 17 500
Юля 2 23 700
• И, сформируем запрос на выбор лучших пяти игроков:

54. Ограничение выборки LIMIT

• Мы здесь указываем, что число очков должно быть не менее 100, затем,
данные сортируются по убыванию очков и отбираются первые пять записей:
• Дополнительный параметр OFFSET позволяет пропускать несколько первых
записей. Например, если записать запрос в таком виде:

55.

Работа с выборкой в программе на Python
Здесь используется метод fetchall для получения результатов отбора SQL-запроса. В
результате, result будет ссылаться на упорядоченный список, состоящий из кортежей с
данными таблицы:
[('Сергей', 1, 33, 2000), ('Михаил', 1, 19, 1000), ('Юля', 2, 23, 700), ('Михаил', 1, 19, 500),
('Елена', 2, 17, 500)]
Или, перебрать последовательно, используя Cursor в качестве итерируемого объекта:

56. Работа с выборкой в программе на Python

• Этот последний вариант более предпочтителен когда число выбираемых
записей может быть велико. Тогда мы не формируем список, а
последовательно выбираем из БД и тут же их обрабатываем. Такой подход
существенно экономит память.
• Есть еще два метода, которые выдают результат выборки из таблицы:
• fetchmany(size) – возвращает число записей не более size;
• fetchone() – возвращает первую запись.
В консоли увидим строки:
('Сергей', 1, 33, 2000)
[('Михаил', 1, 19, 1000), ('Юля', 2, 23, 700)]

57.

Update & Delete
• UPDATE – изменение данных в записях;
• DELETE – удаление записей из таблицы.
• Начнем с команды UPDATE, которая имеет следующий синтаксис:
• UPDATE имя_таблицы SET имя_столбца = новое_значение WHERE условие
• И, например, с ее помощью выполним обнуление очков у всех пользователей:

58. Update & Delete

• Конечно, на практике подобный SQL-запрос встречается крайне редко, т.к. он
затрагивает изменение всех записей в таблице. Обычно, он записывается с
весьма строгим фильтром, например, так:
• Или же, можно всем игрокам женского пола увеличить число очков на 500:

59.

• Смотрите, конструкция score = score+500 позволяет добавить к уже
существующим данным в поле score 500 очков. Наконец, мы можем
обратиться к игрокам по имени и указать им определенное число очков:
• Здесь ключевое слово LIKE возвращает True, если поле name содержит имя
«Федор». В этой строке можно использовать специальные символы:
• % - любое продолжение строки;
• _ - любой символ;

60.

• Здесь шаблон «'С_рг%» означает первую букву «С», за которой может идти
произвольный символ, далее буквы р и г и, затем, произвольный набор
символов. Этому выражению, в частности, соответствует имя «Сергей».
• Однако, на практике злоупотреблять сравнением строк не стоит, т.к. это
относительно ресурсоемкая операция: гораздо проще выполнить сравнение
чисел и именно так нужно стараться задавать фильтры.

61.

По аналогии, с помощью команды UPDATE можно менять значения сразу
несколько столбцов записи, перечисляя их через запятую:

62.

DELETE
• Синтаксис следующей команды для удаления записи из БД, следующий:
• DELETE FROM имя_таблицы WHERE условие
• Здесь все очень просто: указывается имя таблицы, из которой
осуществляется удаление и, затем, условия для выбора удаляемых записей.
Обычно, в качестве фильтра задается строгое условие, чтобы случайно не
удалить «лишние» данные и для этого хорошо подходит скрытое поле rowid.

63. DELETE

Агрегирующие функции в SELECT
• Здесь count(название столбца) – это агрегирующая функция, которая
подсчитывает число выбранных записей. Результатом будет одна запись со
значением 3:

64. Агрегирующие функции в SELECT

• Почему мы указали столбец user_id? В действительности, можно указать
любой другой, разницы не будет, так как число записей для любых столбцов
будет равно трем. Можно даже ничего не указывать и просто записать
count(). Также обратите внимание, на название столбца: count(user_id).
Использовать такое имя в дальнейшем не очень удобно. Для таких случаев
придумали такую вещь как алиас (синоним), который можно указать так:

65.

• В общем случае в команде SELECT можно использовать следующие наиболее
распространенные агрегирующие функции:
• count() – подсчет числа записей;
• sum() – подсчет суммы указанного поля по всем записям выборки;
• avr() – вычисление среднего арифметического указанного поля;
• min() – нахождение минимального значения для указанного поля;
• max() – нахождение максимального значения для указанного поля.

66.

• Смотрите, мы здесь перед полем user_id указали ключевое слово DISTINCT,
которое указывает СУБД выбирать записи с уникальными user_id. Затем, с
помощью агрегирующей функции count мы вычисляем число таких записей и
получаем количество уникальных игроков. Чтобы все это было понятнее,
можно выполнить запрос без агрегирующей функции:

67.

• Далее, предположим, что нам нужно подсчитать суммарное число очков,
которое набрал игрок с user_id = 1 за все игры. Такой запрос будет выглядеть
так:

68.

• Язык SQL позволяет вызывать агрегирующие функции не для всех записей в
выборке, а локально для указанных групп. Например, мы хотим на основе
нашей таблицы games создать список ТОП лучших игроков. Для этого нужно
вызвать функции sum для каждого уникального user_id.

69.

• Это делается с помощью оператора
• GROUP BY <имя поля>
• который группирует записи по указанному столбцу. В этом случае
агрегирующие функции будут работать в пределах каждой группы:

70.

• Обратите внимание на порядок записи операторов в команде SELECT и
порядок их работы. Сначала будут отобраны все записи с числом очков более
300, затем, они сгруппируются и отсортированы по убыванию очков. Именно
поэтому теперь для user_id = 2 число очков стало 1100 (было 1400), а для
user_id = 1 число очков равно 400.

71.

Список таблиц
• Чтобы вывести список всех
таблиц в базе данных
SQLite3, нужно обратиться к
таблице sqlite_master, а
затем использовать fetchall()
для получения результатов
из оператора SELECT.
• Sqlite_master - это главная
таблица в SQLite3, в которой
хранятся все таблицы.

72. Список таблиц

Исключения SQLite3
SQLite3 у есть следующие основные исключения Python:
DatabaseError
Любая ошибка, связанная с базой данных, вызывает ошибку DatabaseError.
IntegrityError
IntegrityError является подклассом DatabaseError и возникает, когда возникает проблема целостности данных, например, когда внешние
данные не обновляются во всех таблицах, что приводит к несогласованности данных.
ProgrammingError
Исключение ProgrammingError возникает, когда есть синтаксические ошибки или таблица не найдена или функция вызывается с
неправильным количеством параметров / аргументов.
OperationalError
Это исключение возникает при сбое операций базы данных, например, при необычном отключении. Не по вине программиста.
NotSupportedError
При использовании некоторых методов, которые не определены или не поддерживаются базой данных, возникает исключение
NotSupportedError.

73. Исключения SQLite3

Массовая вставка строк в Sqlite

74. Массовая вставка строк в Sqlite

Cвязи между таблицами

75. Cвязи между таблицами

Оператор JOIN
• Пришло время разобраться как выполняется объединение данных из разных
таблиц для формирования сводного отчета. Для этого используется
оператор
• JOIN <таблица> ON <условие связывания>который следует сразу после
FROM. Например, у нас есть две таблицы games и users. Таблица games
содержит поле user_id (идентификатор пользователя), которое
является внешним ключом для связывания с таблицей users по полю rowid,
которое является первичным ключом:

76. Оператор JOIN

• И мы хотим получить сводные данные с полями:
• name, sex, score
• причем, поле score следует брать из таблицы games, а не users. Запрос будет
выглядеть так:

77.

• Здесь мы указываем поля сводной таблицы, причем, поле score записано как
games.score, тем самым уточняя, что его следует брать из таблицы games.
Если этого не сделать, то СУБД возвратит ошибку неопределенности имени
поля, т.к. score присутствует в обеих таблицах.
• Далее указывается главная таблица games, с которой будут ассоциированы
данные из второй таблицы users. Условие связывания прописано после
оператора ON как равенство полей games.user_id и users.rowid выбираемой
записи.
• То есть, сначала отбирается запись из главной таблицы games и, затем,
берется запись из таблицы users, у которой rowid равен user_id. В итоге
получается следующая сводная выборка:

78.

• То есть, первые две игры сыграл Михаил с числом очков 200 и 300, затем, Яна
и набрала 500 очков, потом, снова Михаил и так далее. Благодаря
объединению данных мы видим не просто id игрока, а его имя, что гораздо
удобнее для человеческого восприятия.

79.

• Объединять данные из таблиц можно и без оператора JOIN, просто указав их
имена после FROM, например, так:

80.

• Здесь была взята первая запись из таблицы users и ей в соответствие были
поставлены все записи из второй таблицы games.
• И так для всех трех. В итоге произошло такое своеобразное соединение двух
таблиц без какого-либо критерия. Но, так тоже можно делать и знать про этот
функционал полезно, т.к. иногда он тоже используется.

81.

• Теперь давайте вернемся к рассмотрению оператора JOIN в
действительности, когда мы его вот так записывали в SQL-запросе:

82.

Пример

83. Пример

• В этом SQLite примере INNER JOIN будут возвращены все строки из
таблиц employees и positions, где в таблицах employees и positions есть
совпадающее значение position_id.
• Давайте посмотрим на некоторые данные, чтобы понянть, как работает
INNER JOINS:
• У нас есть таблица с именем employee и четырьмя полями
(employee_id, last_name, first_name и position_id). Она содержит следующие
данные:

84.

• Строка для employee_id 10003 из таблицы employees была бы опущена, так
как соответствующий position_id не существует в таблице positions. Строка
для position_id, равная 4, из таблицы positions будет опущена, поскольку
этот positions не существует в таблице employees.

85.

• В команде SELECT можно указывать несколько операторов JOIN для
объединения данных из трех, четырех и большего числа таблиц:
• SELECT <поля> FROM <таблица 1>
JOIN <таблица 2> JOIN <таблица 3> … JOIN <таблица N>
ON <условие связывания>

86.

87.

Оператор UNION объединения
нескольких таблиц
• Предположим, у нас имеются две таблицы с одинаковой структурой:

88. Оператор UNION объединения нескольких таблиц

• Имеем таблицу с шестью записями, причем, строки из исходных таблиц tab1 и
tab2 объединялись по значениям первого поля: сначала 100, потом 200 для
tab1 и 200 для tab2 и так далее.
• Но, если указать только первые столбцы из наших таблиц в SQL-запросе:

89.

• Здесь остались только уникальные (неповторяющиеся) значения. Вообще,
оператор UNION оставляет только уникальные значения записей.

90.

• Если же у первой таблицы во всех полях from укажем имя tab2:
UPDATE tab1 SET `from` = 'tab2'
• то исходный запрос:
SELECT score, `from` FROM tab1 UNION SELECT val, type FROM tab2
• Приведет к результату:
• здесь тоже были оставлены только уникальные записи.

91.

• Вернем записям первой таблицы значения tab1:
UPDATE tab1 SET `from` = 'tab1'
• И сформируем вот такой запрос:
SELECT score, 'table 1' as tbl FROM tab1 UNION SELECT val, 'table 2' FROM tab2
• Смотрите, мы здесь явно указали значение второго поля как 'table 1' для
первой таблицы и 'table 2' – для второй. И чтобы СУБД «знала» какое имя
присвоить второму столбцу, оно указано в виде алиаса tbl. На выходе
получим следующий результат:

92.

• Также можно добавить сортировку сводной таблицы, например, по полю
score:
SELECT score, 'table 1' as tbl FROM tab1 UNION SELECT val, 'table 2' FROM tab2 ORDER BY score
DESC

93.

• Или, добавить фильтр и ограничение максимального числа записей:
SELECT score, 'table 1' as tbl FROM tab1 WHERE score IN(300, 400) UNION
SELECT val, 'table 2' FROM tab2 ORDER BY score DESC LIMIT 3

94.

Вложенные SQL-запросы

95. Вложенные SQL-запросы

• Первая students содержит информацию о студентах, а вторая marks – их
отметки по разным дисциплинам. Каждый из студентов (кроме четвертого)
проходил язык Си.
• От нас требуется выбрать всех студентов, у которых оценка по языку Си
выше, чем у Маши (студент с id = 2). По идее нам тут нужно реализовать два
запроса: первый получает значение оценки для Маши по языку Си:
SELECT mark FROM marks WHERE id = 2 AND subject LIKE 'Си'

96.

• А второй выбирает всех студентов, у которых оценка по этому предмету
выше, чем у Маши:
SELECT name, subject, mark FROM marks JOIN students ON
students.rowid = marks.id WHERE mark > 3 AND subject LIKE 'Си'

97.

• в языке SQL эти два запроса можно объединить, используя идею вложенных
запросов:
SELECT name, subject, mark FROM marks JOIN
students ON students.rowid = marks.id WHERE mark >
(SELECT mark FROM marks WHERE id = 2 AND subject
LIKE 'Си') AND subject LIKE 'Си'
• Мы здесь во второй запрос вложили первый для определения оценки Маши
по предмету Си. Причем, этот вложенный запрос следует записывать в
круглых скобках, говоря СУБД, что это отдельная конструкция, которую
следует выполнить независимо. То, что будет получено на его выходе и будет
фигурировать в качестве Машиной оценки.

98.

• Но, что если вложенный запрос вернет несколько записей (оценок),
например, если записать его вот так:
SELECT name, subject, mark FROM marks JOIN students ON
students.rowid = marks.id WHERE mark > (SELECT mark FROM marks
WHERE id = 2 ) AND subject LIKE 'Си'
• В этом случае будет использован только первый полученный результат,
другие попросту проигнорируются и результат будет тем же (так как первое
значение – это оценка Маши по предмету Си).
• Если же вложенный SELECT ничего не находит (возвращает значение NULL),
то внешний запрос не будет возвращать никаких записей.

99.

• Также следует обращать внимание, что подзапросы не могут обрабатывать
свои результаты, поэтому в них нельзя указывать, например, оператор
GROUP BY. Но агрегирующие функции вполне можно использовать,
например, так:
SELECT name, subject, mark FROM marks JOIN students ON
students.rowid = marks.id WHERE mark > (SELECT avg(mark) FROM marks
WHERE id = 2 ) AND subject LIKE 'Си'

100.

Вложения в команде INSERT
• Вложенные запросы можно объявлять и в команде INSERT.
Предположим, что у нас имеется еще одна таблица female вот с такой
структурой:

101. Вложения в команде INSERT

• Она идентична по структуре таблице students со списком студентов. Наша
задача добавить в female всех студентов женского пола.
• Для начала запишем запрос выбора девушек из таблицы students:
SELECT * FROM students WHERE sex = 2
• А, затем, укажем, что их нужно поместить в таблицу female:
INSERT INTO female SELECT * FROM students WHERE sex = 2

102.

• Но если выполнить запрос еще раз, то возникнет ошибка, т.к. мы попытаемся
добавить строки с уже существующими id, что запрещено по структуре этого
поля – оно определено как главный ключ и содержит только уникальные
значения.
• Чтобы поправить ситуацию, можно вложенный запрос написать так:
INSERT INTO female SELECT NULL, name, sex, old FROM students WHERE sex = 2

103.

• Мы здесь в качестве значения первого поля указали NULL и, соответственно,
СУБД вместо него сгенерирует уникальный ключ для добавляемых записей.
Теперь таблица female выглядит так:

104.

Вложения в команде UPDATE
• Похожим образом можно создавать вложенные запросы и для команды
UPDATE. Допустим, мы хотим обнулить все оценки в таблице marks, которые
меньше или равны минимальной оценки студента с id = 1. Такой запрос можно
записать в виде:
UPDATE marks SET mark = 0 WHERE mark <= (SELECT min(mark) FROM marks WHERE id = 1)

105. Вложения в команде UPDATE

• И на выходе получим измененную таблицу:

106.

Вложения в команде DELETE
• Ну, и наконец, аналогичные действия можно выполнять и в команде DELETE.
Допустим, требуется удалить из таблицы students всех студентов, возраст
которых меньше, чем у Маши (студента с id = 2). Запрос будет выглядеть так:
DELETE FROM students WHERE old < (SELECT old FROM students WHERE id = 2)

107. Вложения в команде DELETE

EXCEPT
• Оператор EXCEPT позволяет найти разность двух выборок, то есть те строки
которые есть в первой выборке, но которых нет во второй. Для его
использования применяется следующий формальный синтаксис:
• SELECT_выражение1
• EXCEPT SELECT_выражение2

108. EXCEPT

Для примера возьмем таблицы

109. Для примера возьмем таблицы

• Таблица employees содержит данные обо всех сотрудниках банка, а таблица
clients - обо всех клиентах. Но сотрудники банка могут также быть его
клиентами. И допустим, нам надо найти всех клиентов банка, которые не
являются его сотрудниками:

110.

INTERSECT
• Оператор INTERSECT позволяет найти общие строки для двух выборок, то
есть данный оператор выполняет операцию пересечения множеств. Для его
использования применяется следующий формальный синтаксис:
• SELECT_выражение1
• INTERSECT SELECT_выражение2

111. INTERSECT

Для примера возьмем таблицы

112. Для примера возьмем таблицы

• В таблице clients хранятся все клиенты банка, а в таблице employees - все его
сотрудники. Но сотрудники могут быть одновременно и клиентами банка,
поэтому их данные могут храниться сразу в двух таблицах. Найдем всех
сотрудников банка, которые одновременно являются его клиентами. То есть
нам надо найти общие элементы двух выборок:

113.

UNIQUE
• Ограничение UNIQUE указывает, что столбец может хранить только
уникальные значения.

114. UNIQUE

CHECK
• Ограничение CHECK задает ограничение для диапазона значений, которые
могут храниться в столбце. Для этого после CHECK указывается в скобках
условие, которому должен соответствовать столбец или несколько столбцов.
Например, возраст пользователей не может быть меньше 0 или больше 100:

115. CHECK

Команда REPLACE
• При вставке данных может нарушаются ограничения UNIQUE или PRIMARY
KEY, например, когда мы пытаемся добавить для столбца, который должен
иметь уникальные значения, данные, которые уже есть в таблице. Этот
конфликт ограничений призвана разрешить команда REPLACE.
• Эта команда сначала удаляет строку, которая вызвала конфликт на
уникальность данных, и затем вместо нее вставляет новую строку. То есть
фактически все выглядит как замена строки.

116. Команда REPLACE

117.

Теперь попробуем добавить те же самые
данные, только с помощью команды
REPLACE:

118. Теперь попробуем добавить те же самые данные, только с помощью команды REPLACE:

Методы execute, executemany и
executescript
• Давайте добавим в таблицу cars несколько записей. В самом простом случае
это можно сделать так:
cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
cur.execute("INSERT INTO cars VALUES(5,'Bentley',350000)")

119. Методы execute, executemany и executescript

• Однако, когда мы программируем на Python, то данные, как правило,
хранятся в каких-либо коллекциях, например, так:
cars = [
('Audi', 52642),
('Mercedes', 57127),
('Skoda', 9000),
('Volvo', 29000),
('Bentley', 350000)
]
• мы бы хотели брать значения из этого списка и передавать их в SQL-запрос.
Для этого запрос следует записывать в виде следующего шаблона:
cur.execute("INSERT INTO cars VALUES(NULL, ?, ?)", cars[0])
• Здесь вместо знаков вопроса будут подставлены соответствующие данные из
первого кортежа списка.

120.

• Соответственно, весь набор ранее приведенных строчек, можно заменить
циклом:
for car in cars:
cur.execute("INSERT INTO cars VALUES(NULL, ?, ?)", car)
• Или, еще проще, воспользоваться методом executemany, который
специально для этого и существует:
cur.executemany("INSERT INTO cars VALUES(NULL, ?, ?)", cars)

121.

executescript
• Далее, если нужно выполнить несколько отдельных SQL-команд, то можно
передать их СУБД с помощью метода executescript:
Cur.executemany("""INSERT INTO cars
VALUES(NULL, ?, ?) ""“, cars)
cur.executescript("""
DELETE FROM cars WHERE model LIKE 'A%’;
UPDATE cars SET price = price+1000 """)
• Result=cur.fetchall()
• A=list(result[0]) [log,pas]
• B=list(result[1]) [log1,pas1]
• A[0]=log A[1]=pas

122. executescript

методы fetchall, fetchmany, fetchone,
iterdump
• пару слов о способе извлечения данных из запросов. Об этом мы уже
говорили на одном из предыдущих занятий, когда рассматривали методы:
• fetchall() – возвращает число записей в виде упорядоченного списка;
• fetchmany(size) – возвращает число записей не более size;
• fetchone() – возвращает первую запись.

123. методы fetchall, fetchmany, fetchone, iterdump

124.

• Чтобы в программе на Python получить доступ к сформированной выборке,
как раз и нужно воспользоваться функциями:
• fetchall, fetchmany или fetchone

125.

• Преимущество такого подхода в экономии памяти при большом числе
записей в выборке. Здесь на каждой итерации цикла мы выбираем только
одну следующую запись, а не храним их сразу целиком в памяти в виде
списка. Это часто бывает эффективно и очень удобно.
• Далее, иногда более предпочтительным вариантом выходных данных
является не кортеж с данными, а словарь, позволяющий обращаться к
элементам по именам полей. Для этого после установления соединения с БД
следует прописать вот такую строчку:

126.

con=sqlite3.connect("Test.db")
con.row_factory=sqlite3.Row
cur=con.cursor()
cur.execute("""SELECT model, price FROM cars""")
r=cur.fetchall()
for row in r:
print(f"{row['model']},{row['price']}")

127.

• Теперь, при выполнении программы увидим, что переменная result в цикле
ссылается на объект Row, а не кортеж:
• <sqlite3.Row object at 0x00000185B226B8B0>
• И через этот объект доступ к данным осуществляется с помощью имен полей
таблицы cars:

128.

Хранение изображений в БД
• Часто в БД требуется хранить небольшие изображения, например, аватары
пользователей. Для этого имеется специальный тип данных BLOB. Давайте
создадим таблицу users с полем ava:
• Далее напишем вспомогательную функцию считывания изображения из
файла:

129. Хранение изображений в БД

• Если изображение было успешно прочитано, то функция возвратит набор
двоичных данных, иначе значение False. Затем, в менеджере контекста БД
вызовем ее и при успешном чтении данных записываем в таблицу users:

130.

• Обратите внимание, прежде чем бинарные данные передавать в поле BLOB
их нужно закодировать в бинарный объект модуля SQLite. Для этого и
вызывается метод Binary, которому передается последовательность
прочитанных байт изображения.
• Запустим программу и перейдем в приложение DB Browser. Откроем там
таблицу users. И при выборе поля BLOB справа увидим изображение, которое
там хранится:

131.

132.

• Давайте теперь прочитаем изображение из этого поля. Выполним
следующий запрос:
• и с помощью метода fetchone обратимся к первой (и единственной) записи и
возьмем данные из поля ava:

133.

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

134.

def readAva(n):
try:
with open(f"avas/{n}.png","rb") as f:
return f.read()
except IOError as e:
print(e)
return False
def writeAva(name,data):
try:
with open(name,"wb")as f:
f.write(data)
except IOError as e:
print(e)
con=sqlite3.connect("Test.db")
con.row_factory=sqlite3.Row
cur=con.cursor()
cur.execute("""SELECT ava FROM use LIMIT 1""")
img=cur.fetchone()['ava']
writeAva("itog.png",img)
img=readAva(2)
if img:
binary=sq.Binary(img)
cur.execute("""INSERT INTO use VALUES('Nikolay',?,1000)""",(binary,))
con.commit()
print("ok")

135.

Внешние ключи FOREIGN KEY
• Внешние ключи позволяют установить связи между таблицами. Внешний
ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и
указывает на один из столбцов из главной таблицы. Как правило, внешний
ключ указывает на первичный ключ из связанной главной таблицы.

136. Внешние ключи FOREIGN KEY

• Для создания ограничения внешнего ключа после FOREIGN KEY указывается
столбец таблицы, который будет представляет внешний ключ. А после
ключевого слова REFERENCES указывается имя связанной таблицы, а затем в
скобках имя связанного столбца, на который будет указывать внешний ключ.
После выражения REFERENCES идут выражения ON DELETE и ON UPDATE,
которые задают действие при удалении и обновлении строки из главной
таблицы соответственно.

137.

• В данном случае определены таблицы companies и users. companies является
главной и представляет компании, где может работать пользователь. users
является зависимой и представляет пользователей. Таблица users через
столбец company_id связана с таблицей companies и ее столбцом id. То есть
столбец company_id является внешним ключом, который указывает на
столбец id из таблицы companies.

138.

ON DELETE и ON UPDATE
• С помощью выражений ON DELETE и ON UPDATE можно установить действия,
которые выполняются соответственно при удалении и изменении связанной строки
из главной таблицы. В качестве действия могут использоваться следующие опции:
• CASCADE: автоматически удаляет или изменяет строки из зависимой таблицы при
удалении или изменении связанных строк в главной таблице.
• SET NULL: при удалении или обновлении связанной строки из главной таблицы
устанавливает для столбца внешнего ключа значение NULL. (В этом случае столбец
внешнего ключа должен поддерживать установку NULL)
• RESTRICT: отклоняет удаление или изменение строк в главной таблице при наличии
связанных строк в зависимой таблице.
• NO ACTION: то же самое, что и RESTRICT.
• SET DEFAULT: при удалении связанной строки из главной таблицы устанавливает для
столбца внешнего ключа значение по умолчанию, которое задается с помощью
атрибуты DEFAULT.

139. ON DELETE и ON UPDATE

Каскадное удаление
• Каскадное удаление позволяет при удалении строки из главной таблицы
автоматически удалить все связанные строки из зависимой таблицы. Для
этого применяется опция CASCADE:

140. Каскадное удаление

Встроенные функции
Функции CASE и IIF
• CASE
• Выражение CASE проверяет истинность набора условий и в зависимости от
результата проверки может возвращать тот или иной результат.
• Выражение CASE последовательно просматривает все выражения с
операторами WHEN. После оператора WHEN указывается условие. Если оно
истинно, то возвращается результат, который указан после оператора THEN.
Если условие ложно, то выражение CASE переходит к следующего оператору
WHEN.
• В конце можно указать необязательный оператор ELSE, который возвращает
результат, если условие всех предыдущих операторов WHEN оказалось
ложным.Завершается определение CASE оператором END.

141. Встроенные функции Функции CASE и IIF

142.

143.

Функция IIF
• Функция IIF в зависимости от результата условного выражения возвращает
одно из двух значений. Общая форма функции выглядит следующим
образом:
• IIF(условие, значение_1, значение_2)
• Если условие, передаваемое в качестве первого параметра, верно, то
возвращается первое значение, иначе возвращается второе значение.

144. Функция IIF

Функции IIF могут быть вложенными:

145. Функции IIF могут быть вложенными:

Создание бэкапа БД
• Класс Cursor содержит один весьма полезный метод iterdump()
• возвращающий итератор для SQL-запросов, на основе которых можно
воссоздать текущую БД. Если просто вывести в консоль возвращаемых строк:

146. Создание бэкапа БД

147.

Перед выполнением этой программы
удалим файл cars.
• С помощью этих запросов можно точно воссоздать таблицы БД, то есть, их
можно рассматривать как некий дамп БД.
• Чтобы наша программа выглядела более функциональной, сохраним все эти
строчки в отдельном файле:
• Теперь, чтобы восстановить БД с помощью этого файла можно
воспользоваться методом executescript, о котором мы уже говорили: