2. Создайте внутреннее соединение между таблицами albums и tracks и отобразите названия альбомов и названия треков в едином наборе результатов.
3. Создайте третье внутреннее соединение — с таблицей genres, которую вы нашли, отвечая на вопрос 1. Включите в ваш набор результатов поле Name из этой таблицы.
Резюме
• Соединения используются для соединения данных из разных таблиц.
• При написании соединений полезно использовать ER-диаграмму.
• При одновременном выборе полей из нескольких таблиц для указания исходной таблицы необходимо использовать псевдонимы.
• Внутренние соединения не включают строки, для которых нет соответствующих данных.
• Внешние соединения включают все строки одной из таблиц, даже если между таблицами нет соответствующих данных. Несовпадающие строки будут отображаться как Null.
• Для проверки нулевых значений необходимо использовать специальные операторы IS и NOT.
• Правые соединения могут использоваться в реализациях SQL, отличных от SQLite. Чтобы в SQLite выполнить эквивалент правого соединения, просто поменяйте местами две сравниваемые таблицы в операторе запроса.
Глава 7. Функции языка SQL
Если вы проверяли свои знания с помощью контрольных вопросов в конце каждой главы, вы, возможно, заметили, что для ответа на некоторые вопросы требуются дополнительные действия после написания запроса. Например, в главе 4 мы задали вопрос: сколько фамилий клиентов начинаются с буквы B? Или попросили найти итоговые суммы счетов в определенном ценовом диапазоне. Чтобы ответить на такие вопросы, требовалось вручную подсчитывать результаты запроса. Чтобы получить все фамилии, начинающиеся на букву B, при помощи информации из главы 4 вы могли выбрать поле LastName в таблице клиентов, отсортировать по фамилии, прокрутить вниз до тех, что начинаются с буквы B, а затем подсчитать их вручную. Если применить материал из главы 5, можно немного упростить задачу, ограничив набор данных, чтобы возвращались только фамилии, начинающиеся с буквы B (запрос WHERELastNameLIKE'B%'). Однако вам все равно придется считать вручную. В этой главе мы расскажем, как упростить вычисления с помощью функций.
Добавление вычислений к запросам
Вычисления (например, подсчет количества полученных записей) можно выполнять, добавляя функции к запросам. Используя функцию COUNT(), можно произвести подсчет LastName и использовать псевдоним NameCount для возврата значения. Добавим функцию к базовому оператору SELECT:
SELECT
COUNT(LastName) AS [NameCount]
FROM
customers
WHERE
LastName LIKE 'B%'
Рис. 85
Для суммирования всех полей в таблице customers, удовлетворяющих условию WHERE, мы можем использовать функцию COUNT(), а не перечислять все записи, начинающиеся с буквы B (чтобы их затем подсчитать). Конечный результат — это поле с псевдонимом и количество фамилий, начинающихся с буквы B.
Это всего лишь один пример того, как использование функций может сэкономить время, выполняя за нас часть работы. В данной главе мы рассмотрим три различных типа функций и проиллюстрируем наиболее полезные.
Типы функций в SQL
Функции в SQL — это специальные ключевые слова, которые принимают определенные параметры, выполняют определенные операции (например, вычисление или изменение данных в поле) и возвращают результат в виде значения. В главе 5 мы уже рассматривали функцию DATE(). В качестве параметров были заданы данные в формате DATETIME, выполнена операция (отредактирован формат написания времени), а затем в результате был возвращен только определенный фрагмент даты. Как мы уже упоминали в главе 5, в SQL для выполнения задач предусмотрено множество типов функций. На рис. 86 показаны наиболее распространенные и полезные.
Примечание
Этот список неполный. Все функции, упомянутые в этой книге, поддерживаются SQLite. Другие реализации базы данных содержат другие функции. Полный список функций, поддерживаемых SQLite, доступен на веб-странице SQLite [10].
Рис. 86
Как видно на рис. 86, в SQL существуют функции трех разных типов.
Строковые функции изменяют символьные и текстовые данные.
Функции даты и времени изменяют данные времени и даты.
Агрегатные функции выполняют математические операции.
Примечание
На первый взгляд может показаться, что эти три типа функций работают только с соответствующими им типами данных (символами, датами и числами). Однако вспомните первый пример с использованием функции COUNT(). Мы смогли использовать агрегатную функцию для арифметического подсчета символьных данных. В определенных случаях мы можем использовать эти функции с разными типами данных. Дополнительные сведения о типах данных см. в главе 1.
Функции в SQL работают подобно функциям в программах для работы с электронными таблицами и в других языках программирования. Если вы когда-либо использовали функцию SUM() в Microsoft Excel, то вы понимаете, для чего она нужна. Еще одно сходство этих функций с функциями Excel — это подсказка в виде всплывающего окна, которое появляется, когда мы вводим функцию в браузере SQL. Рассмотрим следующий пример. На панели Execute SQL (Выполнить SQL-запрос) начните вводить имя функции с одной открытой скобкой:
UPPER(
Под названием функции появится следующий текст:
The UPPER(X) function returns a copy of input string X in which all lowercase ASCII characters are converted to their uppercase equivalent.
(Функция UPPER(X) возвращает копию входной строки X, в которой все символы ASCII нижнего регистра преобразованы в их эквивалент в верхнем регистре.)
Примечание
Часть функции в круглых скобках (X) называется аргументом функции. Некоторые функции могут содержать более одного аргумента.
Большинство функций в DB Browser содержат всплывающие подсказки, которые очень полезны для определения аргументов, принимаемых функцией, и описания работы функции. Я рекомендую изучить и другие функции.
Практические задания
• Функция UPPER() принимает только один аргумент (X). Сколько аргументов принимает функция REPLACE()?
• Прочитайте всплывающую подсказку (на экране в DB Browser) и объясните, как работает функция TRIM().
Внимание
Если всплывающая подсказка не появилась, убедитесь, что вы правильно набираете функцию. Подсказка не появится, если вы просто скопируете и вставите функцию и открытую скобку. Это еще одна причина, по которой всегда необходимо вводить запросы вручную. Никаких ускоренных методов!
Управление текстовыми данными с помощью строковых функций
Строка — это еще одно название для текстовых данных. Строковые функции позволяют форматировать и изменять текст. Чтобы понять, как они работают, вспомним типы данных в SQLite. В главе 3, анализируя структуру базы данных, мы видели, что текстовые данные были сохранены в формате NVARCHAR(X). NVARCHAR — это строка переменной длины, где X представляет максимальную длину строки.
НАПОМИНАНИЕ
Для символьных данных фиксированной длины (например, почтовых индексов, содержащих буквы) можно использовать другой тип данных — с фиксированной длиной. Однако в базе данных sTunes символьный тип данных NVARCHAR используется для всех текстовых данных.
Возможность манипулировать текстовыми строками важна, так как поля в базе данных не всегда организованы удобно для нас. Допустим, нас попросили создать список рассылки по клиентам из США. Для этого необходимо получить имена и адреса клиентов. Используем оператор SELECT.
SELECT
FirstName,
LastName,
Address
FROM
customers
WHERE
Country = 'USA'
При выполнении запроса получим следующий результат:
Рис. 87
Однако возникают некоторые проблемы. Данные адреса разбиты на части. Просто запросить поле адреса недостаточно. Нам также необходимо выбрать поля City, State и PostalCode. Другая проблема: все эти данные содержатся в отдельных полях. Если необходимо создать простой список рассылки, где каждая строка представляет собой полное имя и адрес клиента, то для этой цели полученный шаблон не годится.
Практическое задание
• Попробуйте скопировать результат последнего запроса из DB Browser в текстовый редактор. Что вы видите?
К счастью, для манипулирования текстовыми строками существует несколько отличных инструментов, так что мы можем получить результат в удобном для нас виде. Рассмотрим первый из этих инструментов — конкатенацию.
Конкатенация строк
Конкатенация — объединение двух или более строк. Для слияния двух полей вместе используется символ ||. Например, следующий код выполняет объединение полей FirstName и LastName.
SELECT
FirstName || LastName
FROM
customers
WHERE
CustomerId = 1
Получится следующий результат:
Рис. 88
Оператор конкатенации || просто соединил оба поля вместе без пробелов. Для удобства чтения мы можем последовательно использовать две конкатенации и заключить пробел в одинарные кавычки. Тогда запрос будет выглядеть следующим образом:
SELECT
FirstName,
LastName,
FirstName || ' ' || LastName
FROM
customers
WHERE
Country = "USA"
При выполнении запроса получим следующий результат:
Рис. 89
Практическое задание
• Сделайте результаты вывода более читабельными, создав псевдоним FullName для нашего объединенного поля.
Примечание
Функция конкатенации || не похожа на остальные функции, которые мы будем рассматривать в этой главе. В других реализациях SQL существует функция CONCAT() или используется символ +. Синтаксис может отличаться в зависимости от того, какую РСУБД вы используете, но эффект будет одинаковым.
Рассмотрим следующий пример, где для создания в одной строке имени и адреса клиента используется множественная конкатенация.
SELECT
FirstName || ' ' || LastName || ' ' || Address
|| ', ' || City || ', ' || State || ' ' ||
PostalCode AS [MailingAddress]
FROM
customers
WHERE
Country = "USA"
При выполнении запроса получим следующий результат:
Рис. 90
Практическое задание
• После выполнения запроса скопируйте полученный в DB Browser результат в текстовый редактор. Он будет намного читабельнее, чем до выполнения конкатенации.
Примечание
Пробелы между символами || не обязательны. В предыдущем примере, чтобы получить желаемый формат, мы использовали запятую, а затем пробел. Вы можете добавить любой текст, поместив его в кавычки.