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

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

Эта запись поможет вам разобраться с тем, как составить подзапрос SELECT. Также из нее вы узнаете о том, как работает SQL оператор EXISTS и чем он может быть полезен при составлении подзапроса. Здесь вы найдете примеры составления подзапросов с использованием операторов IN и EXISTS, а завершении публикации найдете информацию о том, с какими SQL командами может быть использован оператор EXISTS.

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

Ранее мы рассмотрели оператор IN и команду SELECT, тогда мы сказали, что оператор IN может быть использован для написания подзапросов SELECT. Другими словами: если набор значений неизвестен, то для их определения мы можем использовать подзапрос SELECT, помимо оператора IN, мы можем использовать SQL оператор EXISTS для составления подзапросов SELECT.

EXISTS переводится на русский язык, как существует. Часто при работе с объектами базы данных мы используем конструкции IF EXISTS или IF NOT EXISTS, если существует и если не существует. Оператор EXISTS используется вместе с командой SELECT только вместе с уточняющими фразами и говорит о том, выводить или нет результаты выборки данных из базы данных по завершении SQL запроса SELECT.

Оператор EXISTS может быть использован и с другими логическими операторами, когда мы выполняем команду SELECT, например, с оператором отрицания NOT. Чаще всего оператор EXIST используется вместе с SELECT для составления подзапросов, но и не только. В этом мы убедимся в дальнейшем.

Давайте перейдем к рассмотрению примеров реализации подзапросов в базах данных SQLite, так как мы уже довольно подробно описали, как работает оператор EXISTS.

Примеры подзапросов SELECT в базах данных SQLite3

Но сперва мы рассмотрим примеры подзапросов SELECT без использования операторов IN и EXISTS. Подзапросы SELECT можно реализовывать и без использования вышеупомянутых операторов. Давайте попробуем продемонстрировать это на примере.  Создадим две таблицы, связанные связью одни ко многим при помощи ограничения внешнего ключа FOREIGN KEY, воспользовавшись командой CREATE:

[php]

PRAGMA foreign_keys=on;

CREATE TABLE tracks (

id INTEGER PRIMARY KEY,

title TEXT NOT NULL,

second INTEGER NOT NULL,

price REAL NOT NULL,

album_id INTEGER NOT NULL,

FOREIGN KEY (album_id) REFERENCES albums (id) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE albums (

id INTEGER PRIMARY KEY,

title TEXT NOT NULL,

artist_id INTEGER NOT NULL,

FOREIGN KEY (artist_id) REFERENCES artist (id) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE artist (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL

);

[/php]

Три таблицы, связанные связью один ко многим. Для обеспечения целостности данных у них есть ограничения уровня таблицы и ограничения уровня столбца. У таблиц есть индексы, созданные ограничением первичного ключа и внутренний индекс ROWID, который SQLite генерирует автоматически.

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

[php]

— Добавляем данные в таблицу исполнителей

INSERT INTO artist (name)

VALUES (‘Вячеслав Бутусов’);

INSERT INTO artist (name)

VALUES (‘Сплин’);

INSERT INTO artist (name)

VALUES (‘Би-2’);

— Добавляем данные в таблицу альбомы

INSERT INTO albums (title, artist_id)

VALUES (‘25-й кадр’, 2);

INSERT INTO albums (title, artist_id)

VALUES (‘Биографика’, 1);

INSERT INTO albums (title, artist_id)

VALUES (‘Би-2’, 3);

— добавляем данные в таблицу трэков

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Девушка по городу’, 193, 26.20, 2);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Песня идущего домой’, 170, 22.10, 2);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Полковнику никто не пишет’, 292, 32.15, 3);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Мой друг’, 291, 27.15, 3);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Моё сердце’, 249, 21.12, 1);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Линия жизни’, 180, 41.12, 1);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Остаемся зимовать’, 218, 17.62, 1);

[/php]

Мы наполнили три наших таблицы данными. А теперь посмотрим, как мы можем реализовать подзапрос SELECT, начнем с самого простого:

[php]

SELECT * FROM tracks

WHERE price > (SELECT 27.00);

id      title   second  price   album_id

3       Полковнику никто не пишет       292     32.15   3

4       Мой друг        291     27.15   3

6       Линия жизни     180     41.12   1

[/php]

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

[php]

SELECT * FROM tracks

WHERE album_id = (

SELECT id FROM albums

WHERE title = 'Би-2'

);

id      title   second  price   album_id

3       Полковнику никто не пишет       292     32.15   3

4       Мой друг        291     27.15   3

[/php]

Данный подзапрос позволил нам получить информацию обо всех трэках в альбоме группы Би-2 «Би-2». Обратите внимание: подзапросы SELECT реализуются вместе с SQL операторами, когда мы используем клаузулу WHERE для того, чтобы задать условие выборки данных. Хотя мы можем реализовать подзапрос SELECT и после уточняющей фразы HAVING, которая позволяет сделать дополнительную фильтрацию строк после группировки данных (GROUP BY).

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

[php]

SELECT column_name FROM (SELECT text_subqueries);

[/php]

Мы можем создавать связанные подзапросы SELECT, связанные подзапросы SELECT это такие подзапросы, которые используют значение внешнего запроса во внутреннем, давайте посмотрим:

[php]

SELECT title FROM albums

WHERE 370 < ( SELECT sum (second)

FROM tracks

WHERE album_id = albums.id);

[/php]

Этим связанным подзапросом мы получили информацию об альбомах, суммарная длительность которых больше 370 секунд. Мы видим, что подзапросы SELECT довольно мощное и интересное средство языка SQL и библиотеки SQLite.

Примеры подзапросов SELECT и оператора IN в базах данных SQLite

Как мы и обещали, перейдем к рассмотрению подзапросов SELECT в связки с логическим оператором IN. Всё очень просто: иногда нам нужно определить набор значений, который заранее неизвестен, но подзапрос SELECT позволяет нам это сделать довольно быстро и автоматически:

[php]

SELECT * FROM tracks

WHERE album_id IN (

SELECT id FROM albums

WHERE artist_id = 1

);

[/php]

Мы получили информацию о трэках Вячеслава Бутусова, использовав подзапрос SELECT и оператор IN.

Примеры оператора EXIST и подзапросов SELECT в базах данных SQLite3

А теперь рассмотрим работу SQL оператора EXISTS и SELECT на примерах. Вы помните, что оператор EXISTS делает проверку на существования, если можно так сказать. Давайте сделаем простую выборку данных из базы данных с использование оператора EXISTS, чтобы понять, как она работает:

[php]

SELECT * FROM tracks

WHERE EXISTS (SELECT id FROM albums WHERE id = 15);

[/php]

Всё, никаких результатов мы не получим от SQLite, так как оператор EXISTS используется только вместе с подзапросом SELECT, а результирующая таблица выводится только в том, случае, когда подзапрос SELECT вернет хотя бы одну строку, даже со значением NULL:

[php]

SELECT * FROM tracks

WHERE EXISTS (SELECT NULL);

id      title   second  price   album_id

1       Девушка по городу       193     26.2    2

2       Песня идущего домой     170     22.1    2

3       Полковнику никто не пишет       292     32.15   3

4       Мой друг        291     27.15   3

5       Моё сердце      249     21.12   1

6       Линия жизни     180     41.12   1

7       Остаемся зимовать       218     17.62   1

[/php]

Нам стоит отметить, что использование оператора EXISTS и подзапроса SELECT – это довольно дорогое удовольствие в плане экономии ресурсов компьютера, так как SQLite начинает выполнять оператор EXISTS для каждой строки внешнего запроса, старайтесь по мере возможности избегать использование оператора EXISTS. Давайте напишем что-нибудь полезное, используя оператор EXISTS и подзапрос SELECT:

[php]

SELECT * FROM tracks

WHERE EXISTS (

SELECT * FROM albums

WHERE tracks.price > 25 AND tracks.album_id = albums.id

);

id      title   second  price   album_id

1       Девушка по городу       193     26.2    2

3       Полковнику никто не пишет       292     32.15   3

4       Мой друг        291     27.15   3

6       Линия жизни     180     41.12   1

[/php]

В результате мы получили выборку, в которой все трэки стоят больше 25 рублей, а если бы в таблице tracks были песни без альбомов, то их бы тоже не было в результирующей таблице.

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

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

Оператор EXISTS можно использовать с другими SQL командами, реализованными в библиотеке SQLite. Не берем в расчет команды управления транзакциями (внутри транзакции можно использовать практически любые SQL предложение) и не рассматриваем команды определения доступа к данным, так как их нет в базах данных SQLite.

Оператор EXISTS можно использовать с командами определения данных в базах данных SQLite. Когда мы создаем таблицу в базе данных командой CREATE, мы можем осуществить проверку на существование оператором EXIST, аналогично и для удаления таблиц из базы данных, данная операция выполняется командой DROP. Оператор EXISTS мы не можем использовать вместе с операциями модификации таблиц и других объектов базы данных, такие операции выполняются командой ALTER.

EXISTS можно использовать со всеми командами манипуляции данными: при добавлении данных в таблицы базы данных мы можем использовать EXISTS и подзапрос SELECT. При удалении данных из таблицы базы данных вместе с командой DELETE мы можем использовать оператор EXISTS, чтобы составить запрос на удаление. Если мы изменяем данные в таблице базы данных, то мы можем использовать оператор EXISTS вместе с командой UPDATE.