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

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

SQL запросы SELECT в базах данных SQLite

В этой записи мы сперва поговорим про назначение запроса SELECT, потом рассмотрим синтаксис SQL запроса в базах данных под управлением SQLite3. А далее начнем рассматривать особенности SELECT в SQL и базах данных SQLite: как составить условие при помощи всевозможных клаузул, как и какие SQL операторы можно использовать вместе с SELECT, как объединить два запроса, как объединить две таблицы в запросе SELECT, как правильно составить подзапрос SELECT. В общем, информации будет много, читайте, разбирайтесь, спрашивайте.

Использование и назначение SQL запросов SELECT в базах данных

Реляционные базы данных – это один из самых естественных способов описать предмет реального мира: столбцы – это характеристики предмета, строки – это информация о предмете. Мы уже научились работать с объектами баз данных: создавать таблицы в базе данных и другие объекты, удалять объекты базы данных, модифицировать таблицы в базе данных. Мы умеем манипулировать данными в таблицах: добавлять строки в таблицы, удалять строки из таблиц и изменять данные в таблицах.

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

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

Итак, мы уже разобрались с тем, что SQL запрос SELECT позволяет получать данные из базы данных или иначе: SQL запрос SELECT делает выборку данных из базы данных. Мы будем разбираться с командой SELECT на протяжении 15 последующих частей, каждая часть – это небольшой пример того, как работает SELECT и что можно сделать при помощи SQL запроса SELECT, поверьте, сделать  можно немало и в этом вы скоро убедитесь.

Мы чуть не забыли о самом главном, нам следует сказать, что результатом работы SQL запросы SELECT всегда является таблица. Если вы выполняете команду SELECT, то любая СУБД вам вернет таблицу при условии, что запрос составлен верно и в базе данных есть данные, удовлетворяющие вашему запросу, в противном случае вы ничего не получите или получите ошибку.

Синтаксис SQL запроса SELECTв базах данных SQLite

Синтаксис SQL запроса SELECT громоздкий и требует от разработчика соблюдения определенных правил и определённого порядка в использование ключевых слов. Ниже на рисунке представлен общий синтаксис SQL запроса SELECT в базах данных SQLite3.

Синтаксис SQL запроса SELECT в базах данных SQLite3

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

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

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

SQL запрос SELECT имеет два модификатора: ALL и DISTINCT. По умолчанию SQLite использует модификатор ALL, поэтому его можно не писать. Особенностью модификатора ALL является то, что в этом случае SQL запрос выполняется и SQLite в результирующей таблице выводит нам все строки, даже те, которые повторяются.

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

Клаузулы и предикаты. Уточняющие фразы для SQLзапроса SELECTв SQLite

Первое, о чем стоит упомянуть, так это о том, что SQL запрос SELECT позволяет использовать всевозможные клаузулы и предикаты. Клаузула или предикат – это уточняющая фраза, правильнее все-таки использовать термин клаузула. Уточняющие фразы помогают сделать точечную выборку из таблицы базы данных SQLite3 и других реляционных СУБД.

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

1. Клаузула WHERE. WHERE позволяет наложить условие на выборку данных из таблицы базы данных под управлением SQLite
2. Предикат GROUP BY. GROUP BY позволяет сделать группировку выборки данных в базах данных SQLite.
3. Уточняющая фраза HAVING. HAVING используется для фильтрации группировки данных.
4. Клаузула ORDER BY. ORDER BY позволяет упорядочить результаты выборки данных.
5. Предикат LIMIT. LIMIT используется для ограничения количества строк в таблице, которая получается в результате выборки.

Чтобы успешно составлять сложные SQL запросы вам нужно запомнить порядок использования уточняющих фраз и назначение каждой фразы. Кстати, никто не запрещает вам использовать сложные выражения.

Объединение двух и более SQL запросов SELECT в базах данных SQLite

SQL запросы SELECT бывают громоздкими не только из-за того, что используют сразу все уточняющие фразы, но и из-за того, чтоSQLite3 дает возможность объединять SQL запросы SELECT, вернее будет сказать объединять результаты двух запросов SELECT при помощи ключевого слова UNION. Зачастую использование UNION и SELECT могут дать очень интересные результаты. Синтаксис использования UNION в SQLite3 вы найдете на рисунке ниже.

Синтаксис UNION в SQL запросе SELECT

Таким образом, мы можем написать SQL запрос SELECT используя все уточняющие фразы, затем написать UNION и написать еще один десятиэтажный SELECT, и SQLite выполнит такой запрос, а у вас в результате будет одна таблица значения в которой будут из двух таблиц: сначала будут выведены строки из первой таблице, а затем к этим строкам будут добавлены строки из второй таблице. Но никто не запрещает вам объединять три и более запросов

Сравнение результатов двух SQL запросов SELECT в базах данных SQLite

SQL запрос SELECT всегда возвращает нам таблицу, это очень важно для понимания его работы. Потому что SQL запрос SELECT не только делает выборку данных, но и позволяет сравнивать результаты выборки данных при помощи ключевых слов EXCEPT и INTERSECT. Первая ключевая фраза дает возможность получить только уникальные строки из двух или более промежуточных таблиц, а вторая позволяет записать в результирующую таблицу только повторяющиеся строки из двух таблиц, полученных в подзапросах.

Когда вы будете использовать SQL запрос SELECT, вы неизбежно столкнетесь с тем, что SQLite (на месте SQLite может быть любая другая СУБД) будет сравнивать значения в таблицах или сравнивать строки. Поэтому, во-первых, вам нужно понимать, как SQLite сравнивает значения. А во-вторых, знать про типы данных в SQLite:

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

Обо всем этом мы говорили ранее и даже очень подробно.

Использование SQL операторов вместе с SELECT в базах данных SQLite

Для усложнения логики запросов на выборку данных мы можем использовать различные SQL операторы, о них мы говорили ранее и перечисляли их. SELECT может использовать логические выражение AND и OR, чтобы сделать условия выборки более точными. В SQL запросах SELECT мы можем использовать оператор BETWEEN. Оператор BETWEEN и SELECT позволяют получить значения в диапазоне, который как раз-таки и задается оператором BETWEEN.

Еще у нас есть возможность использовать оператор LIKE. Оператор LIKE и SELECT позволяют сделать поиск по шаблону или еще можно сказать, что при помощи комбинации LIKE мы можем осуществить сравнение строк с заданным шаблоном. А результатом выборки будет таблица, содержащая только те строки, в которых есть подстрока, указанная в шаблоне LIKE.

Иногда бывает нужно, чтобы в результирующей таблице, полученной с помощью SQL запроса SELECT, были строки, содержащие в своих столбцах только определенный набор значений, для того, чтобы сделать такую выборку нам нужно использовать оператор IN. Результирующая таблица, полученная после комбинации оператор IN и SELECT будет содержать строки из определённого набора значений.

Подзапросы SELECT в базах данных SQLite

Подзапросы SELECT реализуются довольно просто: мы пишем основной или внешний запрос SELECT, далее задаем условие (если мы задаем условие, то неизбежно используем SQLоператор), но в качестве правого операнда мы задаем не какое-то конкретное значение, а пишем запрос SELECTв скобках.

Подзапросы получили такое название от того, что для их реализации используется две или более команды SELECT, получается так, что, например, один SELECTбудет вложен в другой. Первая команда SELECTназывается внешним запросом, а второй SQL запрос SELECT–внутренним подзапросом.

Чаще всего подзапросы SELECT используются с оператором EXISTS или с оператором IN. Если вы пишите подзапрос с использованием EXISTS, то вы увидите результат только в том случае, когда правый операнд (собственно, подзапрос или внутренний запрос) будет иметь значение TRUE, кстати, если подзапрос вернет NULL, то внешний запрос будет выполнен.

Другими словами: если подзапрос вернет значение FALSE, то внешний запрос даже не будет выполняться.

Объединение двух таблиц SQLзапросом SELECTв базах данных SQLite

Самая приятная часть SQL запроса SELECT, но в то же время и самая сложная, хотя сложность ее скорее надуманная, чем действительная. SQL запросы SELECT позволяют объединять таблицы базы данных. Объединение таблиц в SQL осуществляется при помощи ключевого слова JOIN. Стандарт SQL выделяет несколько видов объединения таблиц JOIN:

1. Внутреннее объединение: INNER JOIN.
2. Полное объединение: FULL JOIN.
3. Правое внешнее объединение: RIGHT OUTER JOIN.
4. Левое внешнее объединение: LEFT OUTER JOIN.
5. Перекрестное объединение: CROSS JOIN.

Для внешнего объединение можно опускать ключевое слово OUTER, по крайней мере SQLite это позволяет сделать. Но, есть один минус у SQLite, в данной СУБД реализовано только три объединения: LEFT, CROSS и INNER. Вот такое вот ограничение творчества при написании SQL запросов SELECT ввели разработчики SQLite3.

Использование SQL запроса SELECT с другими командами SQLite

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

Команды определения данных:

1. Мы можем создавать таблицы в базе данных при помощи команды CREATE TABLE, но также SQLite дает нам возможность создавать таблицы с использованием ключевого слова AS и SQL запроса SELECT.
2. Мы не можем использовать SQL запрос SELECT, когда хотим удалить таблицу из базы данных при помощи команды DROP.
3. Также мы не можем использовать запрос SELECT, если мы хотим модифицировать таблицу или другой объект базы данных при помощи команды ALTER.

Последних два пункта обусловлены тем, что перечисленные выше команды работают на уровне объектов, а SELECT работает с данными.

Команды манипуляции данными:

1. Когда мы добавляем строки в таблицу базы данных при помощи команды INSERT, то можем это делать при помощи подзапроса.
2. Если у нас есть необходимость удалить строки из таблицы базы данных, мы используем команду DELETE не так часто, но всё же, мы можем выбрать строки для удаления при помощи SQL запроса SELECT.
3. Для модификации данных в базе данных используется команда UPDATE. Чтобы она работала, мы должны выбрать строки, которые необходимо модифицировать. А что может быть лучше для выбора строк, чем SQL запрос SELECT?

Ускорение SQL запросов SELECT в реляционных базах данных

Мы можем оптимизировать работу команды SELECT в SQLite и других реляционных СУБД. Ускорение выборки данных происходит за счет создания индексов в таблице базы данных. Индексы очень сильно ускоряют выборку данных, но в замен они делают другие операции манипуляции данными более дорогими. Мы ранее уже очень подробно говорили про индексы в базах данных SQLite3.

Напомним, что SQLite создает внутренний индекс для каждой таблицы (столбец ROWID), который может совпадать с индексом, созданным при помощи ограничения первичного ключа PRIMARY KEY (немного теории про ключи и ключевые атрибуты). Так же мы можем организовывать связь между таблицами при помощи ограничения внешнего ключа FOREIGN KEY, такой подход не только обеспечивает целостность данных в базе данных, но и ускоряет выборку данных. Это еще одно применение ограничений уровня таблицы в SQL и реляционных базах данных.

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