SQL: быстрое погружение

Мы уже говорили о том, что такое реляционная база данных, каким образом данные структурируются внутри БД и как используется реляционная система управления базами данных (РСУБД) для извлечения информации (посредством написания запросов) и получения необходимых результатов. Теперь, когда мы рассмотрели основные принципы и терминологию, необходимо настроить рабочую среду SQL, чтобы потренироваться в написании запросов. В этой главе вас ждут практические задания и вопросы для самопроверки.

База данных sTunes

Представьте, что вы недавно устроились на работу в компанию, которая специализируется на розничной онлайн-продаже музыки, на должность аналитика данных SQL. Ваша цель — анализ данных о продажах. Вам предоставлен доступ к базе данных компании. Известно, что БД содержит сведения о товаре (в данном случае песни и альбомы), личную информацию о клиентах, записи о сотрудниках и данные о продажах. Руководство желает знать, содержат ли данные в БД sTunes какую-либо полезную информацию о продажах и демографических характеристиках клиентов и существуют ли способы улучшить или расширить услуги компании. Вам необходимо проанализировать базу данных компании и представить руководству любую найденную информацию. Вы будете выполнять анализ с помощью SQL. Для этого вам необходимо загрузить копию базы данных sTunes на свой компьютер.

Загрузка

Загрузите базу данных компании sTunes

Далее в этой книге мы будем использовать учебную базу данных sTunes. База данных sTunes, а также другие бесплатные цифровые материалы на сайте www.clydebankmedia.com/programming-tech-vault**. Не открывайте sTunes сразу после загрузки. Сохраните БД на своем компьютере.

Браузер базы данных для SQLite

В конце главы 1 мы уже говорили, что будем работать с РСУБД под названием SQLite — произносится как «SQL лайт». Хотя SQLite является конкретной системой управления, или протоколом, для нашей демонстрационной базы данных, нам в любом случае необходимо загрузить специальное приложение (браузер SQL) для «просмотра» нашей базы данных, точно так же как веб-браузер используется для чтения страниц в интернете. SQLite поставляется в комплекте с DB Browser. Это удобный визуальный инструмент с открытым исходным кодом; он используется для создания, проектирования и редактирования баз данных, совместимых с SQLite. DB Browser предназначен для пользователей и разработчиков, которые создают базы данных, осуществляют поиск и редактируют данные.

Установка браузера базы данных для SQLite

Чтобы установить DB Browser, перейдите по ссылке www.clydebankmedia.com/programming-tech-vault. Вы увидите несколько ссылок для загрузки, предназначенных для разных операционных систем. Обязательно выберите соответствующую операционную систему (32- или 64-битная Windows, Mac OS, Linux и т. д.). После загрузки нужного дистрибутива установите приложение.

Как проверить свои знания в SQL

Лучшее обучение — это практика. Кроме примеров из демонстрационной базы данных в следующих главах этой книги представлены два типа практических заданий для самопроверки. Упражнения в разделах «Практические задания» позволят вам попробовать себя в деле сразу после изучения материала. Это просто. Еще один тип заданий — анализ данных. Эти упражнения более сложные, и вам придется вспомнить предыдущие главы. Подробные решения для каждого контрольного задания вы найдете в приложении I.

Стратегии успеха

Прежде чем мы запустим наше программное обеспечение и откроем базу данных sTunes, я хотел бы дать несколько советов как новичкам, так и экспертам. Я преподаватель с большим опытом, поэтому призываю вас следовать моим советам: это значительно повысит ваши шансы на успех.

Пишите каждый пример кода вручную

Если вы имеете доступ к электронной версии книги, не копируйте и не вставляйте примеры кодов в браузер SQL! Это особенно касается тех, кто еще не изучал SQL. Я категорически против того, чтобы при изучении языка программирования копировали существующие примеры кода или упражнения в РСУБД, а затем анализировали их исполнение. При копировании и вставке сложно запомнить синтаксис и орфографию, поэтому пишите запросы самостоятельно. При вставке кода из других источников могут возникать ошибки форматирования, которые трудно обнаружить. Например, наличие кавычек, вставленных из текстового процессора типа Word, часто вызывает синтаксическую ошибку, поскольку браузер БД не интерпретирует эти символы как одинарные кавычки. Ошибки, которые мы делаем, так же значимы, как и наши достижения. Если при выполнении оператора SQL возникает синтаксическая ошибка, иногда проще удалить весь запрос и написать его заново.

Преобразование вопросов на естественном языке в запросы SQL

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

Эту книгу вполне можно считать справочником

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

Внимание

Сначала мы рекомендуем прочитать главу 3, так как в ней будет описано программное обеспечение, необходимое для выполнения всех приведенных в книге примеров запросов.

Резюме

• Эта книга — справочник, который позволяет сразу писать запросы SQL.

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

• Для анализа вам будет предоставлен пример реальной базы данных — sTunes.

• DB Browser для SQLite — это бесплатная общедоступная программа для работы с базами данных. Она проста в использовании и полезна для изучения файлов базы данных. Эту программу можно запустить в среде Windows или macOS.

• Мы предлагаем два варианта самопроверки. Ответы на контрольные вопросы вы найдете в приложении I.

• Для получения максимальной пользы мы рекомендуем все SQL-запросы переписывать вручную (а не копировать) и писать любой SQL-запрос, ориентируясь на вопрос на естественном языке.

** Для доступа к файлам, нажмите на значок «+» рядом с SQL QuickStart Guide.

Глава 3. Работа с базой данных в SQLite

В этой главе вы научитесь пользоваться браузером SQL для SQLite — DB Browser.

Программное окружение

Чтобы начать анализ базы данных sTunes, необходимо выполнить подготовительную работу. Теперь, когда вы установили DB Browser для SQLite и загрузили учебную базу данных sTunes, можно приступать к работе!

1. Запустите DB Browser для приложения SQLite.

а) Пользователям Mac: перейдите в Finder и в папке Applications (Приложения) дважды щелкните на DB Browser для SQLite.

б) Пользователям Windows: перейдите в меню Start (Пуск) и в списке установленных программ выберите DB Browser для приложения SQLite.

2. По умолчанию отобразится следующее окно (рис. 23).

Рис. 23

Открытие базы данных sTunes

3. В открытом DB Browser выберите пункт Open Database (Открыть базу данных).

Рис. 24

4. Откроется диалоговое окно Choose a Database File (Выбрать файл базы данных). Перейдите в папку, в которую вы загрузили учебную базу данных sTunes, и нажмите кнопку Open (Открыть).

Рис. 25

Структура базы данных

При открытии файла на вкладке Database Structure (Структура базы данных) отображаются таблицы из учебной базы данных.

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

Прежде всего мы знаем, что база данных sTunes состоит из тринадцати таблиц. Если нажать на стрелку, расположенную слева от имени таблицы, отобразятся поля, содержащиеся в каждой таблице. В таблице albums (рис. 27) это поля AlbumId, Title и ArtistId.

Поле AlbumId — целочисленный тип данных. Это означает, что поле содержит числовые данные (числа).

Поле Title — символьный тип данных (известный как NVARCHAR). Это означает, что поле содержит символы, или нечисловые данные.

Поле ArtistId — также целочисленный тип данных.

НАПОМИНАНИЕ

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

Рис. 26

Рис. 27

НАПОМИНАНИЕ

В главе 1 мы уже рассказывали о метаданных и различных типах данных, а также описывали структуру базы данных. Поле Type на панели браузера нашей базы данных (рис. 27) — хороший пример метаданных. Тип данных для каждого поля определяется исходя из практической задачи, которая выполняется посредством данного поля.

Просмотр индивидуальных записей

Вкладку Browse Data (Просмотр данных) можно использовать для просмотра записей в каждой таблице. Чтобы переключаться между таблицами и просматривать данные, используйте раскрывающийся список.

Рис. 28

На вкладке Browse Data (Просмотр данных) (рис. 28) отображаются данные, хранящиеся в таблице albums. На вкладке Database Structure (Структура базы данных) отображаются столбцы. Также мы видим фактические данные, содержащиеся в этих столбцах.

В строке 1 в каждом столбце таблицы albums содержатся следующие данные:

AlbumId = 1

Title = "For Those About to Rock We Salute You"

ArtistId = 1

НАПОМИНАНИЕ

Столбцы, которые вы видите, называются полями. Каждая строка представляет собой отдельную запись.

Изучая эту таблицу, мы также можем узнать немного о других таблицах нашей базы данных. В главе 1 мы рассмотрели концепцию внешнего ключа. AlbumId — первичный ключ для таблицы albums (для каждой записи имеется уникальный номер). Также ArtistId принимает целое число вместо фактического имени исполнителя. Это означает, что, вероятно, существует другая таблица, содержащая фактическое имя исполнителя (в формате символьного типа данных), и что ArtistId, скорее всего, является внешним ключом.

НАПОМИНАНИЕ

Внешний ключ — это поле в таблице, которое служит для указания в одной таблице на первичный ключ в другой таблице. Если мы проанализируем таблицу artists, мы можем определить, что ArtistId — первичный ключ для таблицы artists, поэтому он является внешним ключом для таблицы albums.

Вкладка Execute SQL

На вкладке Execute SQL (Выполнить SQL-запрос) мы пишем операторы SQL. Вкладка содержит три панели окон основных компонентов: панель запросов (Query Pane), панель результатов (Results Pane) и панель сообщений (Messages Pane). Давайте рассмотрим назаначение этих трех панелей с помощью следующего кода SQL на панели запросов (рис. 29):

SELECT

*

FROM

albums;

Данный пример задает выборку всех полей (символ * обозначает «все поля») из таблицы albums. Нажмите кнопку выполнения, расположенную над панелью запросов, и ниже, на панели результатов, отобразятся поля и данные этих полей.

Как показано на рис. 29, расположенная слева кнопка воспроизведения выполняет все введенные операторы SQL. Расположенная справа кнопка воспроизведения выполняет только оператор SQL, на котором находится курсор (одновременно выполняется только один оператор).

Рис. 29

Примечание

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

На панели результатов приведены выходные данные, сгенерированные нашим запросом. При получении большого количества результатов, которые не помещаются на одном экране (рис. 30), в браузере появится полоса прокрутки.

Есть еще один способ определить, сколько результатов (в данном случае на экране мы видим только пять) выдает данный запрос. Ниже панели результатов расположена панель сообщений, в которой отображается информация о нашем запросе.

• Количество строк, возвращаемых нашим оператором SQL.

• Время, необходимое на выполнение нашего запроса или оператора SQL.

• Сообщения об ошибках, если наш оператор SQL содержит ошибки.

Рис. 30

Рис. 31

В первую очередь из сообщения мы видим, что в результате нашего запроса вернулось 347 строк. Это сообщение полезно, в случае если бы мы не заметили появление полосы прокрутки. Далее мы прочтем, что наш запрос был выполнен за 9 миллисекунд (сокращенно 9 мс). Почему так важно знать, сколько времени требуется на обработку запроса? В данном случае, может, это и не особо важно. Однако при работе с большими базами данных и с более сложными запросами возврат данных может занять гораздо больше времени. Время выполнения запроса также зависит от оптимальности построения и структуры базы данных. Более подробно о структуре базы данных при работе с несколькими таблицами мы поговорим в главе 6.

Примечание

Панель сообщений — место, где также будут отображаться сообщения об ошибках. Если ваш запрос не выполняется корректно, необходимо проверить панель сообщений.