С каждым днем — а точнее с каждой секундой — объем данных увеличивается. За время, которое вам понадобится, чтобы дочитать это предложение, будет отправлено более 500 000 поисковых запросов Google. За одну минуту на YouTube загружается более 300 часов видеоконтента [1]. Наши возможности хранения данных продолжают разрастаться [2]. И причина тому не только совершенствование смартфонов и рост социальных сетей. Мы сами — обычные люди — генерируем данные, постоянно создавая новые записи, отражающие наши интересы, действия, мысли и чувства. Предприятия и правительственные учреждения признают тот факт, что максимальная эффективность и прибыль не могут быть достигнуты без использования больших объемов данных.

Рис. 1 [3], [4]

Хотя объем генерируемых данных удивляет сам по себе, еще более поразительно, что мы только начинаем их использовать. Анализу подвергается не более полупроцента от всех собираемых данных. Если бы отдельные люди, компании, правительства и другие организации эффективнее использовали собранные данные, то потенциал роста стал бы безграничным [5]. Прибыль и эффективность увеличились бы. Маркетологи смогли бы лучше понимать свою целевую аудиторию. Мошенничество и преступления могли бы быть предотвращены гораздо успешнее. И это только начало! Желание более полно анализировать данные приводит к росту спроса на квалифицированных специалистов по работе с базами данных. И это — вы.

Итак, вы читаете эту книгу, чтобы изучить SQL (Structured Query Language — язык структурированных запросов). Вы уже осознали важную роль данных в мире и хотите максимально эффективно их использовать — мудрое решение! Кто-то из вас изучал SQL в школе или колледже. Возможно, вы работаете в сфере бизнеса или государственного управления и хотите развить навыки, необходимые для продвижения вашей карьеры. Как бы то ни было, практический подход к SQL, изложенный в этой книге, окажется для вас бесценным ресурсом.

SQL (правильно произносится «эс-кью-эль», хотя зачастую говорят «сиквел») — это язык программирования, который используется для работы с базами данных. SQL — главный инструмент оптимизации и обслуживания баз данных, который дает возможность обычным пользователям, даже без опыта программирования, работать с базой данных и превращать большие данные в практически значимую информацию. Уже в течение восемнадцати лет я работаю с SQL и другими системами управления базами данных, но до сих пор помню, каково это — быть новичком. Мне потребовалось время, чтобы разобраться в управлении данными. Когда я впервые познакомился с Microsoft Access, управление данными я рассматривал просто как еще один пункт в области информационных технологий. Тогда еще количество полученных данных было относительно небольшим. Однако в настоящее время каждый сделанный в Google поисковый запрос, каждый опубликованный в Facebook пост и каждый обзор в любой категории товаров на Amazon в конечном итоге хранится где-то на сервере и может быть запрошен. SQL — это основной аналитический инструмент, используемый для расшифровки больших данных, поэтому и возникла огромная потребность в специалистах, знающих язык программирования SQL. Язык данных — это язык, который в той или иной степени считается основой каждой современной технологически ориентированной компании. Изучив материал этой книги, вы научитесь свободно оперировать языком данных.

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

В процессе учебы я понял следующее: нужно больше практики, чтобы стать отличным специалистом в области баз данных. Для вас это не будет так сложно, как для меня. Фактически я хотел, упорядочив все полученные знания, разработать простой и понятный подход к SQL. В книгах по SQL обычно много времени отведено на объяснение истории и теории информатики, лежащих в основе баз данных и языка запросов. В этой книге подробно раскрываются только вопросы, затрагивающие работу реляционных баз данных, а все остальное дано обзорно. Создание подобного ресурса, который может использовать даже новичок в SQL, — достаточно амбициозная задача. Но я преподаю SQL уже много лет: я взаимодействовал со многими студентами, изучающими SQL, и этого достаточно, чтобы понять, что работает, а что нет.

Умение работать с SQL обязательно привлечет к вам людей. Если вы освоите навык работы с SQL, ваше мнение будут ценить и станут обращаться к вам за советом. Это может быть руководитель, которому нужны целевые данные о последней маркетинговой кампании, или коллега-программист, который обратится к вам за помощью в составлении запроса. Бизнес по обучению языку программирования SQL и бизнес по визуализации данных — это главное дело моей жизни. История его развития началась в кофейне в Трибеке, штат Нью-Йорк, куда я пришел со своим ноутбуком и подготовленными учебными материалами по SQL. Я не знал, появится ли кто-нибудь. Я, возможно, недооценил потребность людей в изучении SQL, так как пришло очень много людей. И их поток не кончался. Наконец я понял, что у меня есть свое дело.

С годами я обнаружил, что мне действительно нравится преподавать. Я вырос среди людей, ценящих образование, и теперь убедился, что передача знаний развивает уверенность и способствует позитивному личностному росту. До сих пор радуюсь, когда вижу, как у студентов светятся глаза, исчезают тревога и страх! Я никогда не устаю наслаждаться такими моментами. Я улучшаю и совершенствую программу обучения, работая со множеством студентов разного уровня подготовки и набором навыков. Думаю, было бы классно провести несколько курсов в Тринидаде и Гренаде, откуда я родом. Здорово поделиться знаниями и опытом, приобретенными в Соединенных Штатах, а также разработать франшизу на Карибах! Можно одновременно мечтать и двигаться навстречу своей цели!

Чтобы добиться успеха в изучении SQL, вам необходимо запастись терпением. Я верю в каждого своего ученика. Мой успех как преподавателя — это достижения моих учеников. Новичкам в SQL наука управления базами данных покажется сложной. Примите это, учитесь прилежно, и вы обязательно добьетесь успеха! Вот несколько важных фактов, о которых следует помнить, если вы еще недостаточно знакомы с SQL.

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

• Относитесь к этой книге как к учебному пособию. Выделяйте, подчеркивайте, пишите на полях. SQL необходимо изучать в теории и на практике. Специально для этой книги я разработал упражнения на основе реальных практических задач. Упражнения дополняют друг друга, что позволит вам закрепить знания, изученные в предыдущей главе. Если вы считаете, что новая концепция сложна, имеет смысл вернуться назад и снова проработать упражнения.

• Приятного вам обучения! Никогда не упускайте из виду тот факт, что информационные технологии — самая динамично развивающаяся сфера. Изучая языки программирования, вы развиваете востребованный навык, который позволит радикально изменить мир. Если после всего сказанного вы немного взволнованны — это нормально!

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

Обратите внимание, что в первую очередь я рассказываю о базовых инструментах SQL, необходимых для понимания и получения полезной информации из баз данных. Стандартные методы запросов, описанные в этой книге, можно выполнять, не боясь при этом изменить базу данных. В одной из глав мы кратко рассмотрим добавление, изменение и удаление записей из базы данных (DML — язык управления данными). Однако для извлечения информации из базы данных изучать DML не обязательно, но полезно знать, как выполняется процесс. Эта глава может оказаться полезной тем, кто рассматривает возможность карьерного роста в сфере администрирования баз данных.

SQL — один из наиболее востребованных языков программирования. Он применяется для работы в области администрирования баз данных и во множестве других технических областей, включая разработку и тестирование программного обеспечения, бизнес-анализ и прочее. Рассмотрим профессии, где требуется знание SQL.

 Администратор баз данных (DBA). Отвечает за разработку требований к базе данных, ее проектирование, реализацию, использование и сопровождение. Администраторы баз данных играют ключевую роль при приобретении или модификации аппаратных и программных средств, входящих в инфраструктуру баз данных компании. Администраторы баз данных также несут ответственность за контроль доступа к БД. Они устанавливают и ограничивают доступ, управляют паролями и т. д.

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

 Data Scientist. Он должен уметь извлекать необходимую информацию из самых разнообразных источников, видеть логические связи в системе собранной информации и на основе количественного анализа разрабатывать эффективные бизнес-решения. В Amazon, например, такой специалист может разрабатывать систему контекстной рекламы.

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

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

Если вы будете постоянно развивать и демонстрировать свои навыки в SQL, вы всегда сможете рассчитывать на достойную зарплату. В США в 2018 году средняя зарплата специалиста по SQL превышала 80 000 долларов. Прежде чем компания наймет вас на полный рабочий день, вам необходимо освоить теорию и немного практики [6]. Многие компании предлагают оплачиваемые стажировки, благодаря которым вы сможете проверить свои навыки работы с SQL на реальном проекте.

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

Язык структурированных запросов SQL используется в сфере технологий практически во всех областях. Если компании извлекают выгоду из хранения и анализа данных, то им выгодно использовать SQL. Найдите время и подумайте, кому требуется анализ данных. Это несложно. На самом деле гораздо сложнее найти компании, которым невыгодно нанимать специалистов по работе с базами данных. Спрос на таких специалистов существует уже давно, однако в настоящее время потребности бизнеса в работе с данными постоянно растут, а следовательно, растет и спрос на специалистов, работающих в сфере баз данных.

При желании всегда можно освоить новый навык. Я обнаружил, что для данного конкретного навыка лучший способ — одновременно изучать теорию и практиковаться. Эта книга позволит вам как можно быстрее научиться писать запросы. Книга состоит из трех основных частей, каждая включает три-четыре главы.

• Часть 1 «Создание среды обучения SQL» — краткое знакомство с терминологией и структурой баз данных, а также возможность попрактиковаться в настройке конкретного программного обеспечения для работы с базами данных, которое мы рассматриваем в этой книге. Даже если вы убеждены, что знаете и понимаете основы SQL, мы все равно настоятельно рекомендуем вам последовательно изучать материал книги, поскольку в этом разделе описаны конкретные инструменты, методы и стратегии SQL.

• Глава 1 «Структура базы данных». В этой главе вы изучите структуру реляционной базы данных, а также типы данных. Также здесь дан краткий обзор некоторых терминов. В конце главы вы сможете выполнить практические задания.

• Глава 2 «Инструменты и стратегии SQL». Здесь вы научитесь использовать бесплатно загружаемое программное обеспечение SQL (SQLite), а также работать с учебной базой данных SQL, чтобы закрепить и проверить усвоенные знания. В конце главы даны практические задания и вопросы для самопроверки.

• Глава 3 «Работа с базой данных в SQLite». Вы научитесь работать с учебной базой данных в браузере SQL, использовать браузер для навигации по общей структуре базы данных, для просмотра данных в отдельных таблицах, познакомитесь с вкладкой Execute SQL (Выполнить SQL-запрос).

• Часть 2 «Операторы SQL». Здесь описаны операторы и команды, предназначенные для написания простых запросов.

• Глава 4 «Работа с запросами». Вы познакомитесь с основным оператором SELECT, узнаете, как получить доступ к сохраненной в таблицах информации с помощью оператора FROM. Также научитесь сортировать эти данные в алфавитном порядке с помощью оператора ORDERBY, а затем использовать оператор LIMIT для ограничения числа записей.

• Глава 5 «Преобразование данных в информацию». Вы узнаете, как использовать оператор WHERE, а также операторы сравнения, логические и арифметические операторы. Также вы познакомитесь с оператором LIKE, научитесь использовать специальные символы и работать с функцией DATE(), операторами AND, OR и оператором CASE.

• Глава 6 «Работа с несколькими таблицами». Здесь рассказано об операторах для соединения двух или нескольких таблиц. Операторы INNERJOIN, LEFTJOIN и RIGHTJOIN позволяют возвращать и сравнивать данные из нескольких таблиц.

• Глава 7 «Функции языка SQL» познакомит вас с функциями языка SQL, включая функции агрегирования, строки и функции даты и времени.

• Часть 3 «Расширенные возможности языка SQL» знакомит с более сложными, но очень полезными методами, используемыми для повышения эффективности запросов. В этой части вы познакомитесь с основами языка управления (манипулирования) данными (DML), который, в отличие от ранее изученных операторов SQL, изменяет информацию в базе данных.

• Глава 8 «Подзапросы» посвящена концепции вложенных запросов или подзапросов. В этой главе показано, как использовать подзапросы с различными операторами SQL, которые вы уже знаете. Кроме того, вы изучите новый оператор DISTINCT.

• Глава 9 «Представления». Здесь рассказывается о виртуальных таблицах, известных как представления. Это запросы, которые сохраняются и могут по мере необходимости выполняться повторно или использоваться в качестве подзапросов в других операторах SQL.

• Глава 10 «DML — язык управления данными». В этой главе вы познакомитесь с основами языка управления данными — DML. Также вы изучите новые операторы INSERT, UPDATE и DELETE.

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

Данные — это часть информации [7]. Данные находятся повсюду и содержатся везде, но на практике термин «данные» обычно относится к информации уже записанной или той, которую можно записать. Таблица — один из самых простых инструментов, используемых для записи и визуализации данных. Таблица — это двухмерная сетка, состоящая из строк и столбцов.

Примечание

При работе с базами данных таблица также может называться «базовой относительной переменной», хотя в этой книге мы будем придерживаться термина «таблица». Сводная терминология представлена на рис. 5.

Рис. 2

На рис. 2 представлена таблица, содержащая различные типы данных. К данным могут относиться имена, числа, даты, символы (например, «+» или «-») или любые другие форматы. Данные — это просто информация. Следовательно, при обработке данных нам следует соответствующим образом ограничить их. На рис. 2 показана таблица, в которой хранится основная информация о пациентах. Данные о пациентах заданы в различных форматах — числа, имена и даты, а в поле BloodType (группа крови) представлена строка из двух символов (буква и символ «+» или «-»). Форматы, используемые для визуализации данных, не случайны. Все базы данных содержат метаданные, то есть данные, описывающие структуру и форматирование самих данных, часто называемые «данными о данных». Например, поле DateOfBirth (дата рождения) может содержать метаданные, которые преобразовывают информацию к формату мм/дд/гггг. Метаданные, содержащиеся в поле Height (рост), могут ограничивать длину данных двумя знаками, если рост измеряется в дюймах.

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

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

Пример

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

Рис. 3

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

Рис. 4

Строки в таблице называются записями. Также их можно называть кортежами. Столбцы в таблице, как правило, называются полями. Также их можно назвать атрибутами. Поля/атрибуты — это категории, используемые для определения данных в записи (строке).

Примечание

В этой книге для описания строк в таблице мы будем использовать термин «записи», а для описания столбцов — «поля». См. рис. 5 — основная терминология.

Рис. 5

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

Рис. 6

Реляционная база данных — это конструкция базы данных, которая в 1969 году была официально утверждена ученым из IBM Эдгаром Франком Коддом. В следующем году Кодд опубликовал статью под названием «Реляционная модель данных для больших, совместно используемых банков данных» [8]. Девять лет спустя несколько крупных игроков в сфере технологий, в том числе IBM и Relational Software Inc. (позже ставшая Oracle), начали использовать реляционные базы данных в коммерческих целях. Четыре десятилетия спустя реляционная модель становится наиболее распространенной формой проектирования баз данных.

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

Реляционная база данных будет содержать множество таблиц, аналогичных таблице patient_info (рис. 7). Таблицы связаны друг с другом с помощью ключевых полей. Как вы видите на рисунке, таблица patient_info содержит поля, являющиеся первичным ключом и внешним ключом. Каждая таблица в реляционной базе данных должна содержать первичный ключ. Первичный ключ — это уникальный идентификатор записи в таблице. Первичный ключ каждой записи должен быть уникальным и не должен быть нулевым (пустым). Обратите внимание на поле PatientID в таблице patient_info. Поскольку это поле используется как первичный ключ, у каждой записи в таблице значение данного поля должно быть уникально. Иными словами, никакие две записи не могут иметь один и тот же PatientID.

Хотя первичный ключ (в данном случае PatientID) должен быть уникальным, другие поля могут содержать данные, повторяющиеся более чем в одной записи. Рассмотрим поле PrimaryCareDoctorID. Например, если Dr. Waynewright, ID 106547 (см. первую строку рис. 7), лечит нескольких пациентов из базы данных, то его имя и идентификатор могут встречаться в нескольких записях таблицы.

Примечание

В реляционной базе данных таблицы часто называют «связями», так как они содержат набор записей (строк), связанных с различными полями (столбцами). Однако в этой книге мы будем использовать термин «таблица». См. рис. 5 — «Основная терминология».

Внешний ключ — это поле в таблице, значение которого соответствует первичному ключу в другой таблице. Предположим, что в дополнение к нашей таблице patient_info в базе данных существует еще одна таблица с именем primary_care_doctors, в которой в качестве первичного ключа используется поле PrimaryCareDoctorID. В таблице primary_care_doctors строка Dr. Waynewright с ID 106547 появится только в одной записи. Именно совпадение различных ключевых полей в разных таблицах обеспечивает исключительно важную взаимосвязь в реляционной базе данных. Как правило, эти связи отображаются в виде схемы базы данных, также известной как диаграмма «сущность — связь» (ERD, Entity Relationship Diagram), которая служит своего рода эскизом для базы данных.

Рис. 7

Рис. 8

Пока не беспокойтесь, что означают символы 1 и ∞. Очень скоро мы к ним вернемся. А сейчас рассмотрим схему и ее взаимосвязи. В данной схеме всего четыре таблицы, и таблицы связаны друг с другом посредством одного или нескольких общих полей. Поле PatientID — это первичный ключ для таблицы patient_info и одновременно внешний ключ для таблицы lab_orders. Точно так же поле HospitalId — первичный ключ для таблицы hospitals, но это внешний ключ для таблицы primary_care_doctors. Очень просто, правда? Давайте рассмотрим другую схему.

Схема на рис. 9 описывает базу данных для обработки заказов и доставки товаров клиентам. И здесь пора поговорить о символах 1 и ∞, которые находятся на схеме на концах соединительных линий. Эти символы описывают связи между таблицами. Когда на одном конце соединительной линии находится символ 1, а на другом — символ ∞, это означает связь между полями таблиц «один-ко-многим».

Рассмотрим подробнее таблицу products (рис. 10). В ней представлены данные о различных товарах и их атрибутах.

Рис. 9

Рис. 10

Поле ProductId — первичный ключ для этой таблицы (обозначено значком ключа). Каждая запись в таблице будет содержать уникальный идентификационный номер товара. Фактически основная цель этой таблицы — каталогизировать атрибуты различных товаров базы данных. Теперь давайте проанализируем связи между таблицами products и suppliers.

Рис. 11

Рис. 12

Между таблицами suppliers и products существует связь «один-ко-многим», основанная на данных поля SupplierId. В таблице suppliers каждая запись будет иметь уникальный идентификационный номер для каждого поставщика. В таблице products может быть несколько записей с одним и тем же идентификационным номером поставщика.

Значок ключа, расположенный рядом с полем SupplierId в таблице suppliers, означает, что SupplierId — это первичный ключ для этой таблицы. В базе данных, безусловно, может содержаться большое количество разных товаров (каждый будет иметь уникальный идентификационный номер), поступающих от одного поставщика и каталогизированных в таблице products. Взгляните на таблицу suppliers, где должен быть только один уникальный идентификационный номер поставщика для каждой записи.

Рис. 13

Примечание

Данные в поле supplierId могут повторяться в нескольких записях в таблице products, но не в таблице suppliers.

Давайте проанализируем взаимосвязь между таблицами products, order_details и orders (рис. 14).

Рис. 14

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

Комбинация данных в полях, используемая для формирования составного ключа, работает как уникальный идентификатор для любой записи в таблице. Другими словами, если запись OrderId в таблице order_details равна 101, а ProductId той же записи — P006, то мы можем предположить, что никакая другая запись в таблице не будет иметь такую же комбинацию данных этих двух полей. Могут существовать одна или несколько других записей с OrderId, равным 101, а также одна или несколько других записей с ProductId, равным P006, но только одна запись может иметь и 101 в качестве своего OrderId, и P006 в качестве своего ProductId. Эта комбинация данных работает как составной ключ, который, как и любой первичный ключ, обеспечивает уникальный идентификатор для каждой записи в таблице.

Рис. 15

В связи «один-ко-многим» стандартный первичный ключ находится на стороне «один». Например, в таблице orders мы видим, что первичный ключ, поле OrderId, предоставляет уникальный идентификатор для каждой записи в таблице. Сторона связи «ко-многим» находится в таблице order_details. Как вы думаете, почему?

Давайте рассуждать логически. Можно сделать вывод, что роль таблицы order_details — предоставить информацию о различных заказанных товарах. Также допустимо, что любой товар может быть заказан несколько раз несколькими заказчиками при самых разных обстоятельствах, с разными ценами и т. д. Следовательно, поле ProductId не может использоваться само по себе в качестве первичного ключа для таблицы order_details. Кроме того, любой заказ может включать в себя несколько товаров, и если мы проанализируем другие поля, используемые в таблице order_details — UnitPrice, Quantity и Discount, тогда мы четко поймем, что эти поля обозначают свойства определенного товара, а не заказ в целом. Также поле OrderId не может использоваться само по себе в качестве первичного ключа для таблицы order_details. Решение состоит в том, чтобы объединить ProductId и OrderId в составной ключ, тем самым гарантируя, что данные, содержащиеся в столбцах UnitPrice, Quantity и Discount, будут соответствовать уникальному и определенному заказу и уникальному и определенному товару в этом заказе.

SQL применяется в целом ряде программных пакетов, известных как реляционные системы управления базами данных(РСУБД). Эти системы упрощают применение SQL, когда пользователь дает команды и задает вопросы базе данных. Наиболее распространенные СУБД — это Oracle Database, Microsoft SQL Server, MySQL, IBM Db2 и SQLite.

Рис. 20

Примечание

Программное обеспечение РСУБД часто называют базой данных. Это не совсем верно. Правильнее сказать, что РСУБД предоставляет интерфейс (обычно известный как SQL-браузер) для взаимодействия пользователя с данными, хранящимися в базе данных.

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

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

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

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

В SQL Server необходимо ввести следующее:

SELECT TOP 10 *

FROM

products;

В MySQL это будет выглядеть следующим образом:

SELECT *

FROM

products

LIMIT 10;

Если бы мы сформулировали запрос в MySQL так, как в примере для SQL Server, браузер SQL выдал бы синтаксическую ошибку и запрос не был бы выполнен. Единственное различие между этими двумя реализациями SQL заключается в том, каким образом мы указываем браузеру SQL ограничить наши результаты десятью первыми записями. В остальном запросы одинаковы. Различия между РСУБД обычно весьма незначительны. Если выражать в процентах, то эти различия составляют менее 10 %. Простые декларативные принципы SQL работают в большинстве РСУБД. Следовательно, если вы будете изучать базовую логику SQL в рамках какой-либо определенной РСУБД, то вы сможете быстро адаптировать свои знания основ SQL к любой другой РСУБД.

Если ранее вы уже работали с SQL, вы, возможно, встречались с терминами «запрос», «оператор», «условие» и «ключевое слово». SELECT — это специальное ключевое слово в SQL, также его называют оператором SELECT, условием SELECT или запросом SELECT. Так в чем же разница? Давайте двигаться от наиболее широкого толкования к более узкому.

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

Рис. 21

Слова, написанные ЗАГЛАВНЫМИ буквами, — ключевые слова SQL.

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

Примечание

На рис. 21 показан пример использования оператора SQL и полного запроса. Запрос может содержать несколько условий, каждое из которых начинается с ключевого слова.

Теперь, когда вы уже знакомы с основами архитектуры базы данных и способами взаимодействия с ней, давайте перейдем к практике решения реальных задач. Существуют различные версии РСУБД. Нам кажется неэффективным рассказывать о функциях применительно к каждой РСУБД в отдельности. В качестве стандартной РСУБД для этой книги мы выбрали SQLite, которую считаем имеющей практическую ценность и доступной для новичков. SQLite — это программный продукт с открытым исходным кодом, поэтому он доступен для использования. Примерно 99 % изученного материала, касающегося SQLite, применимо к большинству других РСУБД. Кроме того, SQLite считается одной из наиболее широко используемых систем РСУБД в мире. Она применяется в компьютерах, мобильных устройствах и даже автомобилях [9]. Более подробную информацию можно найти на сайте: https://www.sqlite.org/index.html.

Рис. 22

Примечание

Окончание Lite («легкий», «облегченный») не относится к возможностям данного программного обеспечения, а скорее к его настройке, административным накладным расходам и использованию ресурсов.

• Таблица — это двумерная сетка строк и столбцов, содержащая данные.

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

• Метаданные описывают характер и формат данных, включая минимальную/максимальную длину строки или допустимые числа, буквы или специальные символы.

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

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

• Взаимосвязь между таблицами и их первичными и внешними ключами называется схемой базы данных и может быть изображена с помощью схемы «сущность — связь» (ERD).

• Существует множество различных реляционных систем управления базами данных (РСУБД), например Oracle Database, Microsoft SQL Server, MvSQL, IBM Db2 и SQLite. Хотя они во многом отличаются, все они в качестве основы используют язык структурированных запросов.

• SELECT — это наиболее распространенная команда SQL.

• Операторы SQL могут содержать несколько условий с использованием разных операторов SQL.

• В этой книге мы взяли за основу SQLite. Однако все полученные навыки можно легко использовать на других платформах РСУБД.

* В англоязычных странах целая часть отделяется от дробной точкой, а в России — запятой. — Примеч. ред.

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

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

Загрузка

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

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

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

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

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

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

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

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

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

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

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

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

В этой главе вы научитесь пользоваться браузером 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

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 (Выполнить 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.

Примечание

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

Примечание

Теперь пришло время первых контрольных вопросов! Они приводятся в конце каждой главы. Контрольные вопросы и практические задания — это основа закрепления теоретических знаний.

Используя вкладки Database Structure (Структура базы данных) и Browse Data (Просмотр данных), ответьте на следующие вопросы.

1. Сколько таблиц в нашей базе данных?

2. Сколько полей в таблице tracks?

3. Какие типы данных указаны в этой таблице?

4. Как выглядят данные в таблице?

НАПОМИНАНИЕ

В приложении I в конце книги приведены ответы на контрольные вопросы.

• DB Browser для SQLite (выбранное нами программное обеспечение) открывается, как и любая другая компьютерная программа.

• Нажав кнопку Open Database (Открыть базу данных), вы сможете выбрать файл базы данных на вашем компьютере.

• Структуру файла базы данных вы можете изучить на вкладке Database Structure (Структура базы данных).

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

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

• Панель результатов содержит данные, возвращаемые запросом.

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

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

Прежде чем составить свой первый SQL-запрос, рассмотрим, как писать комментарии. Комментарии — это простые предложения, которые помогают понять логику вашего SQL-запроса. Использование комментариев в SQL очень полезно, так как они поясняют сложную логику запроса.

В SQL комментарии бывают двух типов. Однострочные начинаются с двух дефисов (-). Пример ниже демонстрирует комментарий, созданный в строке 1 (рис. 32).

Многострочные комментарии начинаются с сочетания символов /* и заканчиваются символами */. Все, что находится между открывающим и закрывающим символами, — это комментарии (рис. 33).

Рис. 32

Рис. 33

В примере приведена стандартная информация, которую полезно включать в блок комментариев. Разработчик, дата разработки и описание имеют большое значение для всех, кто сталкивается с SQL-запросом или сценарием.

На мой взгляд,

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

Написать запрос — это то же самое, что задать вопрос на любом человеческом языке. Большое значение имеют формулировка, детали и порядок слов. Чем детальнее наш вопрос, тем точнее будет ответ.

При создании SQL-запроса необходимо учитывать следующие пять моментов.

1. С какой базой данных мы работаем?

2. Из какой таблицы в этой базе данных нам необходимо извлечь данные?

3. Какие поля в этой таблице нас интересуют?

4. Хотим ли мы исключить какие-либо данные, отфильтровать или исключить какой-либо диапазон или период времени?

5. Как сформулировать наш запрос одним простым предложением на человеческом языке?

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

Примечание

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

Как уже упоминалось в главе 1, все запросы должны соответствовать определенному синтаксису, чтобы их мог понять браузер SQL. Однако при написании запросов необходимо учитывать еще кое-что. Важно, чтобы другие пользователи базы данных могли легко понять ваши запросы. Набор принципов, которые задают стиль, методы написания запросов и т. д., известен как соглашение о кодировании. Для разных сред баз данных соглашения о кодировании различаются. В этом разделе мы приводим соглашения о кодировании, используемые в этой книге.

В предыдущих примерах после ключевого слова SELECT мы использовали символ * вместо указания отдельных полей. С помощью специального символа * осуществляется выборка всех записей из таблицы. В некоторых случаях этот символ полезен. Однако чаще рекомендуется указывать необходимые поля.

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

В нашем примере в условии SELECT мы определили три поля для отображения. Каждое поле необходимо отделять запятой (кроме последнего). Отсутствие запятой между полями или наличие ее после последнего поля — это распространенные синтаксические ошибки, о которых вы получите сообщение на панели результатов.

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

НАПОМИНАНИЕ

Условие — это часть инструкции SQL, которая начинается со специального ключевого слова (SELECT, FROM и т. д.) и может включать дополнительные параметры и операторы.

В предыдущих примерах мы осуществляли выборку всех записей из таблицы customers. Хотя мы ограничили наши записи тремя полями и отсортировали эти поля, в панели сообщений мы видим, что каждый раз мы возвращаем 59 строк. Если нет необходимости в просмотре всех 59 записей, мы можем ограничить наши результаты определенным количеством строк. Это бывает полезно при сортировке по количеству (которое мы продемонстрируем позже), например по самой высокой цене или самым большим продажам. Если после условия ORDERBY мы добавим ключевые слова LIMIT10, в результате вернутся только первые десять записей в указанном порядке сортировки. Можно указать любое число записей, которое необходимо отобразить (при условии, что в таблице имеется такое количество записей).

/*

CREATED BY: Уолтер Шилдс

CREATED ON: 13.03.2018

DESCRIPTION: Данный запрос осуществляет выборку первых 10 записей из таблицы customers (клиентов), отсортированных сначала по имени (по возрастанию), а затем по фамилии (по убыванию).

*/

SELECT

FirstName AS [First Name],

LastName AS [Last Name],

Email AS [EMAIL]

FROM

customers

ORDER BY

FirstName ASC,

LastName DESC

LIMIT 10

Рис. 39

На нашей панели сообщений (рис. 39) теперь появилось сообщение: 10 rows returned in 1ms (10 строк вернулось за 1 мс). Сначала запрос выполняет условие ORDERBY, а затем применяет ограничение.

Примечание

Нет необходимости всегда использовать условие LIMIT вместе с ORDER BY. В большинстве случаев имеет смысл упорядочить результаты по определенным критериям, прежде чем ограничивать их. Если условие ORDER BY не используется, результаты оператора LIMIT будут возвращены в том порядке, в котором они были изначально добавлены в таблицу.

1. Напишите запрос, чтобы узнать количество клиентов, фамилии которых начинаются с буквы B.

2. Какая компания при сортировке в порядке убывания появляется в верхней строке таблицы customers?

3. Какое количество клиентов не указали почтовый индекс?

• Рекомендуется добавлять комментарии к запросам. Базовый блок комментариев может содержать имя разработчика, дату создания запроса и краткое описание запроса.

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

• Запрос обычно начинается с условия SELECT, которое указывает, какие поля выводить из таблицы, указанной в условии FROM.

• Псевдонимы в SQL используются для присвоения таблице или столбцу в таблице временного имени. Псевдонимы позволяют сделать имена столбцов более удобочитаемыми. Псевдоним существует только на время выполнения запроса.

• Использование условия ORDERBY позволяет сортировать записи в алфавитном порядке (ASC) или в обратном порядке (DESC).

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

На этом этапе обучения мы покажем, как, используя основной оператор SELECT, получить любой набор полей из таблицы базы данных и при необходимости упорядочить их. Отображение и сортировка полей — это важный первый шаг обучения. В дальнейшем, чтобы вы могли задавать более конкретные вопросы, понадобятся более точные инструкции. В предыдущей главе в разделе «Контрольные вопросы» мы спросили, сколько фамилий клиентов начинаются с буквы B. Если вы выполнили упражнение, то знаете, что в результате выполнения запроса вернулось не очень много записей. В данном случае легко отсортировать данные в алфавитном порядке по фамилии, а затем просто вручную подсчитать, сколько из них начинается на букву B. Но что делать, если база данных содержит миллион клиентов? Вы действительно думаете, что сможете вручную подсчитать все фамилии на букву B? К счастью, в SQL существуют инструменты, которые не только позволяют сузить набор результатов (то есть результаты запроса) до очень конкретных данных, но также упорядочить и отфильтровать данные в зависимости от заданных пользователем условий.

Примечание

С этого момента мы не будем показывать окна с выходными данными браузера DB Browser (только если мы специально не ссылаемся на функцию браузера). Теперь мы будем показывать результаты наших запросов в табличной форме (рис. 40).

Рис. 40

Мы также можем использовать операторы для возврата определенного текста подобно тому, как мы делали с числами. Рассмотрим пример с использованием операторов сравнения. Ответим на следующий вопрос: сколько счетов было выставлено в городе Тусон (Tucson)?

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

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE

BillingCity = 'Tucson'

ORDER BY

Total

Рис. 48

В результате получено только семь счетов для города Тусон.

Примечание

При использовании текста в качестве критерия в условии WHERE указанные текстовые значения должны быть заключены в одинарные кавычки (BillingCity = 'Tucson').

НАПОМИНАНИЕ

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

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE

BillingCity IN ('Tucson', 'Paris', 'London')

ORDER BY

Total

Рис. 49

В предыдущих примерах для поиска необходимого текста мы использовали оператор =. SQL также позволяет искать фрагменты текстовой строки с помощью оператора LIKE. Это особенно полезно, когда мы не знаем, как именно было записано в базе данных текстовое значение. Кроме того, бывают случаи, когда текстовое значение было написано с ошибками. Если понадобится найти все счета, выставленные в городах, название которых начинается с буквы T, в условии WHERE придется изменить параметры.

Оператор LIKE использует подстановочные знаки, представленные символом % (символ процента). То, что следует за знаком =, это единственное значение, которое вы увидите в своем наборе результатов. С помощью оператора LIKE и подстановочного знака вы можете определить варианты ввода.

Примечание

Подстановочные знаки всегда заключаются в одинарные кавычки. Не заключенный в кавычки символ % — это просто арифметический оператор (показан ранее в этой главе в таблице операторов). Текстовый поиск не чувствителен к регистру. При использовании в запросе строчной или заглавной буквы результаты будут одинаковы.

С помощью подстановочного знака можно задать любое количество символов любого типа. Как показано в примере ниже, запрос ищет любые счета, выставленные в городах на букву T. В результат теперь попали Торонто и Тусон.

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE

BillingCity LIKE 'T%'

ORDER BY

Total

Рис. 50

Добавление еще одного символа % перед буквой T изменит условие поиска на поиск счета, выставленного в городе, название которого содержит букву T.

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE

BillingCity LIKE '%T%'

ORDER BY

Total

Рис. 51

Примечание

Разумеется, при этом в результатах появятся и города, названия которых начинаются или заканчиваются строчной буквой t. Символ % может представлять любую букву (буквы), включая строчную t.

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

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE

BillingCity NOT LIKE '%T%'

ORDER BY

Total

Из примеров видно, что существует множество способов использования оператора с подстановочными знаками. Рассмотрим наиболее распространенные.

Рис. 52

Рис. 53

Примечание

Символ % можно интерпретировать как «что угодно». Например, когда вы указываете '%T%', вы имеете в виду: «Меня не беспокоит, какие символы содержатся в строке до или после буквы T (буква T при этом не первая и не последняя)».

При работе с датами в SQL можно использовать ряд функций, которые помогают получать более точные результаты. Из предыдущего примера видно, что столбец InvoiceDate таблицы invoices имеет тип данных DATETIME. Поэтому, когда мы указывали значение даты в услови WHERE, мы включали время (2009-01-03 00:00:00). Функция DATE()позволяет исключить время при указании параметров даты.

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

Invoices

WHERE

DATE(InvoiceDate) = '2009-01-03'

ORDER BY

Total

Рис. 55

Результат этого запроса идентичен результату предыдущего запроса. Однако использование функции DATE() позволяет получить результат быстрее, когда параметры времени либо отсутствуют, либо не актуальны.

Примечание

В SQL доступно множество функций. Функция DATE () особенно полезна при использовании условия WHERE для сортировки записей по дате. В главе 7 мы более подробно рассмотрим другие функции.

Оператор OR позволяет найти записи, соответствующие любому из заданных вами условий. В следующем запросе выполняется поиск всех счетов, выставленных в городах, названия которых начинаются с буквы P или с буквы D.

Рис. 57

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE

BillingCity LIKE 'p%' OR BillingCity LIKE 'd%'

ORDER BY

Total

1. Создайте запрос для таблицы invoices, включающий оператор CASE, который будет отмечать все продажи из США — страны, откуда выставлен счет — как Domestic Sales (Продажи на внутреннем рынке), а все другие продажи — как Foreign Sales (Продажи за рубежом). После оператора ENDAS создайте новое поле SalesType.

2. Отсортируйте эти данные по новому полю SalesType.

3. Сколько счетов от продаж на внутреннем рынке превышает сумму $15?

• Операторы — это специальные ключевые слова SQL, которые используются с условиями SQL для фильтрации данных в зависимости от определенных условий.

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

• Функция DATE() позволяет исключить время при указании параметров даты.

• Порядок операций при использовании логических операторов (таких как AND/OR) устанавливается с помощью круглых скобок ().

• Оператор CASE позволяет отмечать записи специальным именем поля в зависимости от заданных пользователем логических условий.

*** Существует некая путаница в терминологии. В английском языке есть два разных слова, statement и operator, которые чаще всего переводят одинаково — оператор. Встречается также перевод слова operator (в применении к арифметическим, логическим и сравнения) как «операция», а перевод слова statement как «инструкция». Мы будем использовать термин «оператор» как наиболее привычный. — Примеч. ред.

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

Соединение (join) — это операция, которая объединяет поля двух или более таблиц реляционной базы данных. Рассмотрим очень простой пример использования таблицы invoices в базе данных sTunes. В предыдущих главах мы много работали с таблицей invoices, поэтому она хорошо нам знакома. На вкладке Browse Data (Просмотр данных) браузера SQL (рис. 62) видно, что таблица invoices состоит из девяти полей. Поле InvoiceId содержит идентификационный номер каждого счета. Поле CustomerId — идентификационный номер каждого клиента (которому выставлен счет). Таблица invoices также содержит поля с информацией о дате и сумме счета. Остальные поля в этой таблице предназначены для адреса плательщика.

Рис. 62

Допустим, отделу маркетинга компании sTunes необходимо более детально проанализировать клиентскую базу. В этом случае он запросит полный список имен клиентов (имя и фамилию) со всеми счетами, выставленными каждому клиенту. Как мы напишем такой запрос, используя только таблицу invoices? Нам не удастся ответить на этот вопрос, используя только один запрос и ранее полученные навыки. Таблица invoices не содержит имена клиентов. Однако таблица invoices содержит поле CustomerId. Чтобы отобразить список счетов с именами клиентов, которым выставлены данные счета, нам необходимы таблицы invoices и customers.

Мы видим, что таблица customers (рис. 63) содержит требуемую нам информацию — имена и фамилии всех клиентов компании sTunes. Данная таблица также содержит поле CustomerId. Если мы внимательно посмотрим на значки в таблице customers, то увидим, что рядом с полем CustomerId расположен небольшой значок ключа. В главе 1 мы говорили о том, что значок ключа — это символ первичного ключа, уникального поля идентификации для конкретной таблицы.

НАПОМИНАНИЕ

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

Рис. 63

Поскольку CustomerId — это первичный ключ таблицы customers, а таблица invoices содержит аналогичное поле с тем же именем, то эти два поля CustomerId — это связь, необходимая для одновременного доступа к обеим таблицам. Теперь у нас есть вся информация, необходимая для объединения этих двух таблиц и создания списка счетов с именами клиентов.

Рассмотрим пример использования операции соединения JOIN, объединяющей эти таблицы.

SELECT

*

FROM

invoices

INNER JOIN

customers

ON

invoices.CustomerId = customers.CustomerId

Примечание

В данном примере мы используем соединение, называемое INNER JOIN (внутреннее соединение). Далее мы рассмотрим несколько различных типов соединений. Каждое работает по своему правилу. На данный момент вам лишь необходимо знать, что соединения позволяют нам получать доступ к полям из разных таблиц.

Вам уже знакомы многие операторы данного запроса. Запрос начинается с SELECT, как и все наши предыдущие запросы. Мы используем символ *, чтобы получить все поля в таблице. Выберем все поля из таблицы invoices и объединим их со всеми полями в таблице customers. Ключевое слово ON используется в запросе для связи двух таблиц через поле CustomerId. Поскольку существует два варианта поля CustomerId (по одному в каждой таблице), нам нужно сообщить браузеру SQL, какой из вариантов использовать. Для этого существует специальная нотация (в форме tablename.FieldName). Устанавливаем равенство между полем CustomerId из таблицы invoices (записанное как invoices.CustomerId) и полем CustomerId из таблицы customers (customers.CustomerId). Результат показан на рис. 64.

Рис. 64

Примечание

Используя символ *, мы объединили девять полей таблицы invoices с тринадцатью полями таблицы customers. В результате получилось двадцать два поля. Для удобства печати мы убрали некоторые из этих полей, но мы можем просмотреть все двадцать два поля в браузере, используя горизонтальную полосу прокрутки на панели результатов на вкладке Execute SQL (Выполнить SQL-запрос).

Из первого примера видно, что при обращении к полям соединения JOIN имеют особый синтаксис. Поскольку две таблицы в любой базе данных могут содержать поля с одинаковыми именами, при создании соединений необходимо для указания определенного поля задать имя таблицы, чтобы браузер SQL точно знал, какое поле имеется в виду. По правилам синтаксиса необходимо сначала указать имя таблицы, а затем через точку — имя поля. Чтобы уменьшить объем текста и повысить удобочитаемость, в соединениях часто используются псевдонимы. Следующие два соединения идентичны по функциям.

SELECT

*

FROM

invoices

INNER JOIN

customers

ON

invoices.CustomerId = customers.CustomerId

Как мы уже говорили ранее в этой главе, левое внешнее соединение извлекает все данные из левой таблицы и всю соответствующую информацию из правой таблицы. Это полезно для анализа базы данных и проверки неполноты информации. Допустим, компания sTunes проводит внутренний аудит, чтобы уточнить, сколько у нее в ассортименте альбомов и отдельных треков. Руководство sTunes просит создать перечень всех исполнителей, которые не имеют альбомов. Анализируя предыдущую ER-диаграмму, мы можем предположить, что необходимая информация будет храниться в таблицах artists и albums. Давайте рассмотрим взаимосвязь между этими таблицами.

Рис. 80

Таблица artists содержит поле ArtistId (первичный ключ) и поле для имени исполнителя (рис. 80). Из ER-диаграммы видно, что таблица artists связана с таблицей albums связью «один-ко-многим». Эта связь имеет смысл, так как исполнитель может записать несколько альбомов. В таблице albums имеется собственный первичный ключ AlbumId, а также поле ArtistId как внешний ключ.

Используя соединение LEFTOUTERJOIN для таблицы artists (левая таблица), мы вернем все данные из таблицы artists с соответствующими записями (если они есть) в таблице albums. С помощью левого соединения все поля, не имеющие названий альбомов, будут заполнены значениями null. Теперь мы можем создать запрос.

Примечание

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

SELECT

ar.ArtistId AS [ArtistId From Artists Table],

al.ArtistId AS [ArtistId From Albums Table],

ar.Name AS [Artist Name],

al.Title AS [Album Title]

FROM

artists AS ar

LEFT OUTER JOIN

albums AS al

ON

ar.ArtistId = al.ArtistId

Запрос возвращает 418 записей, и вначале все результаты кажутся корректными. Поле ArtistId из таблицы artists соответствует полю ArtistId из таблицы albums. Большинство имен исполнителей связаны с названиями альбомов. Однако далее (рис. 81) мы обнаруживаем значения null.

Чтобы решить поставленную нам задачу и получить список исполнителей, у которых нет альбома, необходимо добавить условие WHERE, в котором следует указать только записи со значением NULL в таблице albums. Существуют определенные ключевые слова SQL, которые мы используем для работы со значениями NULL.

• ISNULL в условии WHERE вернет только нулевые значения.

• NOTNULL вернет только значения, которые не были нулевыми.

Рис. 81

Раздел WHEREal.ArtistIdISNULL вернет список исполнителей без названий альбомов.

Внимание

При работе со значениями NULL необходимо использовать операторы IS и NOT, а не оператор равенства =. Нулевые значения указывают на недостаток данных. Оператор = сравнивает значения двух элементов. Нулевые значения не содержат значений, поэтому их нельзя сравнивать, используя оператор =. Использование оператора = приведет к ошибке.

SELECT

ar.ArtistId AS [ArtistId From Artists Table],

al.ArtistId AS [ArtistId From Albums Table],

ar.Name AS [Artist Name],

al.Title AS [Album]

FROM

artists AS ar

LEFT OUTER JOIN

albums AS al

ON

ar.ArtistId = al.ArtistId

WHERE

al.ArtistId IS NULL

Полученные результаты (рис. 82) содержат 71 запись, в которой нет альбомов и имен исполнителей.

Рис. 82

1. Используя DB Browser и вкладку Browse Data (Просмотр данных) или ER-диаграмму (рис. 65), проанализируйте таблицу tracks. Определите, какие поля в этой таблице будут внешними ключами в другой таблице. На основании определенных вами внешних ключей определите, какие таблицы связаны с таблицей tracks.

2. Создайте внутреннее соединение между таблицами albums и tracks и отобразите названия альбомов и названия треков в едином наборе результатов.

3. Создайте третье внутреннее соединение — с таблицей genres, которую вы нашли, отвечая на вопрос 1. Включите в ваш набор результатов поле Name из этой таблицы.

• Соединения используются для соединения данных из разных таблиц.

• При написании соединений полезно использовать ER-диаграмму.

• При одновременном выборе полей из нескольких таблиц для указания исходной таблицы необходимо использовать псевдонимы.

• Внутренние соединения не включают строки, для которых нет соответствующих данных.

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

• Для проверки нулевых значений необходимо использовать специальные операторы IS и NOT.

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

Если вы проверяли свои знания с помощью контрольных вопросов в конце каждой главы, вы, возможно, заметили, что для ответа на некоторые вопросы требуются дополнительные действия после написания запроса. Например, в главе 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.

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

Вложенная функция — это та, которая содержится в другой функции. Одна из целей использования вложенных функций — модифицировать формат внутренней функции. Если мы проанализируем предыдущий пример, в котором мы использовали функцию AVG(), то увидим, что Average Sales (средний объем продаж) содержит слишком много десятичных знаков. Такой формат обычно не используется для денежных единиц. Функция ROUND(), хотя и не агрегатная, очень полезна при выполнении каких-либо математических операций или если требуется привести в порядок результаты. Для этой цели функцию AVG() можно поместить в функцию ROUND() (это и есть вложение) и указать количество десятичных знаков, до которого мы хотим округлить результат.

Рис. 104

SELECT

AVG(Total) AS [Average Sales],

ROUND(AVG(Total), 2) AS [Rounded Average Sales]

FROM

invoices

Рис. 105

Внимание

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

Если кратко, то разница между условиями WHERE и HAVING заключается в том, что WHERE предназначено для фильтрации неагрегатных данных, а HAVING — для фильтрации результатов, содержащих агрегаты. Если более подробно, то два типа фильтрации возникают, когда в запрос включены как условие WHERE, так и условие HAVING. Условие WHERE указывает запросу, какую информацию следует исключить из таблицы, а затем, после фильтрации данных и применения к полям агрегатных функций, условие HAVING действует как дополнительный фильтр для агрегатных данных. Давайте повторим предыдущий запрос, но на этот раз выберем только города, начинающиеся с буквы B, а затем из этого списка отфильтруем счета, среднее значение которых больше пяти (рис. 110).

SELECT

BillingCity,

AVG(Total)

FROM

invoices

WHERE

BillingCity LIKE 'B%'

GROUP BY

BillingCity

HAVING

AVG(Total) > 5

ORDER BY

BillingCity

В запросе мы выполнили ту же задачу, но на этот раз добавили условие WHERE для фильтрации результатов только по городам, начинающимся с буквы B. Данный этап фильтрации выполняется до обработки условий HAVING и ORDERBY. Так как нам необходимо выполнить фильтрацию, прежде чем мы сможем группировать, то порядок условий фильтрации важен, а условие WHERE всегда предшествует HAVING.

Рис. 110

В условиях GROUPBY можно одновременно указывать столько столбцов, сколько вам требуется. Предположим, необходимо получить более подробную разбивку средних значений счетов. Мы можем написать запрос так, чтобы агрегированные данные были сначала сгруппированы по странам, а затем по городам. В приведенном ниже примере мы добавим в условие GROUPBY еще одно поле, BillingCountry. Давайте посмотрим, как работает запрос.

SELECT

BillingCountry,

BillingCity,

AVG(Total)

FROM

invoices

GROUP BY

BillingCountry, BillingCity

ORDER BY

BillingCountry

На рис. 111 мы видим, что у нас имеется несколько записей для одной страны выставления счета, а отдельные города указаны в соседнем столбце. Группировка по нескольким столбцам может быть очень полезна, когда необходимо получить более детальную информацию.

НАПОМИНАНИЕ

Файлы базы данных могут содержать орфографические и/или ошибки, связанные с регистром.

Рис. 111

Цель этой главы — познакомить вас с функциями. Мы описали возможности некоторых функций, их способность превращать данные в информацию и решать практические задачи. Если бы пришлось рассматривать все функции в SQLite и иллюстрировать работу каждой из них, то эту книгу мне никогда не удалось бы закончить. К счастью, в интернете можно найти массу информации о функциях SQL и их использовании. Я всегда рекомендую студентам поискать информацию о функциях в интернете, чтобы увидеть разные примеры их использования. Надеюсь, что вы продолжите обучение самостоятельно и узнаете еще больше о том, как использовать эти мощные инструменты.

1. Создайте однострочный список рассылки для всех клиентов из США, включая полные имена, написанные заглавными буквами, и полные адреса с пятизначными почтовыми индексами, в следующем формате:

FRANK HARRIS 1600 Amphitheatre Parkway, Mountain View, CA 94043

2. Каковы средние годовые продажи клиентам из США согласно имеющимся данным за все годы?

3. Каков общий объем продаж компании за все время?

4. Кто входит в десятку лучших клиентов с точки зрения совершенных ими покупок? Подсказка: чтобы ответить на этот вопрос, необходимо использовать соединение (глава 6).

• Функции позволяют изменять, форматировать и выполнять вычисления с данными в таблицах.

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

• Запросы, содержащие текст, можно разделить, объединить, использовать для них заглавные буквы и т. д.

• После агрегирования данные можно дополнительно отсортировать с помощью условий GROUPBY и HAVING.

• Условие HAVING работает с агрегатными полями так же, как условие WHERE с неагрегатными.

• Условие HAVING можно использовать в запросе, только если имеется условие GROUPBY.

• Условие GROUPBY можно использовать с несколькими полями, чтобы еще больше детализировать агрегатные данные.

Подзапрос — это просто один запрос, вложенный в другой. Подзапрос обычно добавляется в условия SELECT, FROM или WHERE. Использование подзапросов полезно, когда запрос, который мы хотим создать, требует нескольких дополнительных шагов или вычислений для создания ожидаемого набора данных. Например, подзапросы очень полезны в сценариях, когда мы хотим просмотреть или сравнить запрос по условию, для вычисления которого требуется собственный запрос. Чтобы не писать один запрос, а затем копировать результаты в следующий, мы можем использовать подзапрос, выполняющий обе операции одновременно. Подзапросы также предоставляют нам другой метод одновременного доступа к данным из нескольких таблиц. Хотя использование подзапроса не так эффективно, как соединение (join), он позволяет нам выполнить вычисление в одной таблице, а затем использовать это вычисление в другой таблице. Начнем с использования подзапросов с агрегатными функциями.

Рассмотрим простой оператор SELECT, который мы использовали в предыдущей главе для возврата среднего значения суммы счета из таблицы invoices:

SELECT

ROUND(AVG(Total), 2) AS [Average Total]

FROM

invoices

Рис. 112

Напоминание

Чтобы округлить значение функции AVG() до двух значащих цифр, добавим функцию ROUND().

Мы видим, что в примере запроса средняя сумма счета из таблицы invoices составляет $5,65. Предположим, компания sTunes попросила нас собрать данные обо всех счетах, сумма которых меньше этого среднего значения. Прежде всего, необходимо использовать оператор SELECT, который выводит отдельные поля счета (например, InvoiceDate, BillingAddress, BillingCity и, конечно же, Total). Затем отфильтруем полученные результаты, сравнив их с агрегатной функцией. Используем условие WHERE, чтобы сравнить поле Total с полем AVG(Total). В предыдущей главе вы узнали, что попытка использования условия WHERETotal<AVG(Total) в условии WHERE для прямого сравнения приводит к ошибке misuseofaggregatefunction (неправильное использование агрегатной функции). Итак, нам надо взять указанный выше запрос и вставить его в другой запрос, который сортирует счета по итоговой сумме. И, к счастью, есть простой способ сделать это.

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

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE Total <

(SELECT

AVG(Total)

from

invoices)

ORDER BY

Total DESC

Запрос, заключенный в круглые скобки, называется внутренним запросом, он и станет частью условия WHERE нашего внешнего запроса.

Рис. 113

На рис. 113 показано, что оператор SELECT и другие операторы во внешнем запросе написаны прописными, а все операторы и функции во внутреннем запросе — строчными буквами. Не существует универсального правила, каким образом следует писать операторы или функции. Я лично считаю, что указанный способ написания операторов и функций внешних запросов и подзапросов упрощает восприятие, поскольку помогает визуально различать внешние и внутренние операторы запроса.

Подзапрос не всегда содержит агрегатную функцию. Следующий запрос отображает дату конкретной транзакции.

SELECT

InvoiceDate

FROM

invoices

WHERE

InvoiceId = 251

Рис. 117

Если необходимо узнать, получены ли какие-либо другие счета после указанного выше счета, мы добавим подзапрос, заключенный в круглые скобки, а затем добавим внешний запрос.

SELECT

InvoiceDate,

BillingAddress,

BillingCity

FROM

invoices

WHERE

InvoiceDate >

(select

InvoiceDate

from

invoices

where

InvoiceId = 251)

Рис. 118

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

SELECT

InvoiceDate

FROM

invoices

WHERE

InvoiceId IN (251, 252, 255)

Рис. 119

В предыдущем запросе для возврата трех дат из таблицы invoices используется условие IN: 2012-01-09, 2012-01-22 и 2012-01-24. Теперь предположим, что нам нужна информация о покупках за эти три дня. Если необходимо выбрать все счета за эти три дня, мы можем написать новый запрос или просто использовать предыдущий в качестве подзапроса, например:

SELECT

InvoiceDate,

BillingAddress,

BillingCity

FROM

invoices

WHERE

InvoiceDate IN

(SELECT

InvoiceDate

from

invoices

where

InvoiceId in (251, 252, 255))

Преобразование существующего запроса в подзапрос полезно, когда вы «играете» со своими данными. Чтобы еще больше сузить область поиска, этот метод позволяет повторно использовать существующий запрос и изменять его.

Рис. 120

Другие примеры в этой главе показывают, что подзапросы очень полезны для сценариев, где надо просмотреть или сравнить запрос по условию, для вычисления которого требуется собственный запрос. Как вы уже знаете из главы 1, в каждой таблице есть одно уникальное поле, первичный ключ, содержащее уникальный номер для каждой записи, но другие поля могут содержать избыточную информацию. Для удобства работы с избыточной информацией стоит отфильтровать данные, чтобы они отображали только уникальные значения. В этом случае полезно условие DISTINCT. Лучше понять подзапросы и использование условия DISTINCT нам помогут таблицы tracks и invoice_items.

Таблица invoice_items показывает, какие треки какому счету соответствуют. Если мы создадим запрос, отображающий поля InvoiceId и TrackId, упорядоченные по TrackId, мы увидим, что определенные номера треков были заказаны несколько раз в разных счетах.

SELECT

InvoiceId,

TrackId

FROM

invoice_items

ORDER BY

TrackId

Так, треки № 2 и № 8 появляются в нескольких счетах, что означает, что они были заказаны несколько раз (рис. 121). Однако для трека № 7 счет отсутствует, поэтому мы можем сделать вывод, что никто не приобретал его. Руководство sTunes хочет знать о треках, которые не продаются. Нам необходимо найти таблицу, связывающую поля TrackId с InvoiceId. Для перечисления всех треков (по композитору и названию), которые не отображаются в таблице invoice_items, мы можем использовать подзапросы.

Рис. 121

Рис. 122

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

SELECT

DISTINCT TrackId

FROM

invoice_items

ORDER BY

TrackId

Из примера видно, что некоторые номера TrackId (например, № 7) не появляются ни в одном счете, но списки треков, которые появляются в нескольких счетах, сокращены до одного. Теперь нам необходимо написать запрос, перечисляющий все треки из таблицы tracks, которые не входят (NOTIN) в созданный первым запросом список.

SELECT

TrackId,

Composer,

Name

FROM

tracks

WHERE

TrackId NOT IN

(select distinct

TrackId

from

invoice_items)

Итак, у нас есть список песен, которых не было ни в одном счете (рис. 123). Анализируя полученные результаты, мы видим, что трек № 7 находится в самой верхней части списка непродаваемых треков. Теперь отдел продаж sTunes имеет четкое представление о том, какие песни непопулярны.

Рис. 123

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

Примечание

Подзапросы — это альтернативный способ взаимодействия таблиц, имеющих общие ключевые поля. Но если нам нужно много работать с обеими таблицами, то вместо подзапросов эффективнее создать join-соединение. Например, гораздо эффективнее создать соединение между полем TrackId и таблицей tracks (вместо использования подзапросов), чтобы отображать всю информацию рядом.

1. Сколько счетов превышают среднюю сумму счетов, выставленных в 2010 году?

2. Какие клиенты получили эти счета?

3. Сколько клиентов живут в США?

• Подзапросы позволяют выполнять в одном запросе несколько операторов SQL.

• Подзапросы состоят из двух или более отдельных операторов SQL, которые образуют внутренние и внешние запросы.

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

• Условие DISTINCT позволяет игнорировать избыточные данные в записях и искать только уникальные значения.

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

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

SELECT

ROUND(AVG(Total), 2) AS [Average Total]

FROM

invoices

Мы можем преобразовать этот оператор в представление, добавив над верхней строкой запроса команду CREATEVIEWV_AvgTotalAS:

CREATE VIEW V_ AvgTotal AS

SELECT

ROUND(AVG(Total), 2) AS [Average Total]

FROM

invoices

Итак, мы создали представление V_AvgTotal.

Примечание

При именовании представлений используется V_ в начале названия. После символа подчеркивания идет краткое описание того, как работает представление, при этом при необходимости используются дополнительные символы подчеркивания. Также можно назвать это представление V_AvgTotal_Rounded. Вы можете выбрать другое название.

При выполнении этого оператора мы получим следующее сообщение: QueryexecutedsuccessfullyCREATEVIEWV_AvgTotalAS (Запрос успешно выполнен: CREATEVIEWV_AvgTotalAS). Представление V_AvgTotal можно увидеть в разделе Views (Представления) на вкладке Database Structure (Структура базы данных) в DB Browser.

Рис. 124

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

Если выбрать пункт меню Browse Table (Просмотр таблицы) и перейти на соседнюю вкладку Browse Data (Просмотр данных), то можно просмотреть содержимое представления (как для любой таблицы базы данных). Из этого меню мы также можем удалить представление или сгенерировать копию кода (который ввели ранее для создания представления).

Рис. 125

Примечание

На рис. 125 пункт меню Modify View (Изменить представление) на момент публикации неактивен и недоступен. Изменение существующих представлений не поддерживается данной версией DB Browser. Однако в других реализациях SQL, таких как SQL Server, можно изменять существующие представления. Позже в этой главе мы объясним, как изменить представление в SQLite.

Использование представлений в базах данных полезно по ряду причин, но в первую очередь из-за удобства. Если вам постоянно нужен один и тот же запрос или вы постоянно ссылаетесь на конкретное соединение, которое показывает взаимодействие двух таблиц, такой запрос удобно сохранить как представление, и тогда он будет доступен при необходимости в любое время. Кроме того, когда запрос сохраняется как представление, его можно вызвать как подзапрос, выбрав имя представления.

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

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE Total <

(SELECT

*

from

V_AvgTotal)

ORDER BY

Total DESC

Примечание

Хотя представление — это полноценный SQL-запрос, мы все равно должны ссылаться на него в операторе SELECT, если будем использовать представление вместе с подзапросами. В операторах SELECT можно использовать символ *, чтобы представление, на которое мы ссылаемся, возвращало все строки. В рассматриваемом примере строка только одна — агрегированная сумма.

Если какой-либо запрос часто используется в качестве подзапроса, то, сохранив его в качестве представления, мы можем упростить код и сделать более понятными для других наши действия. Кто угодно, например наш коллега, может просто перейти на вкладку Database Structure (Структура базы данных) и изучить, как работают наши представления. Использование представлений сокращает время написания запроса, особенно когда запросы становятся длинными и сложными.

Представления очень полезны для хранения длинных или сложных запросов. В главе 6 мы рассказывали о соединениях. Соединения помогают создавать связи между таблицами, обычно это довольно длинные запросы, поэтому их можно сохранить в качестве представлений, чтобы не вводить заново. В последней главе, посвященной подзапросам, мы использовали вместе таблицы invoice_items и tracks, чтобы выяснить, какие песни из таблицы tracks никогда не были заказаны. Нам бы пригодилось представление, связывающее эти две таблицы. На него можно было бы ссылаться в подзапросе либо просто сохранить как представление, чтобы использовать при необходимости.

Чтобы создать представление для этих двух таблиц, сначала необходимо решить, какое соединение мы хотим использовать. Поскольку мы ищем коррелирующие поля, то будем использовать условие INNERJOIN для таблиц invoice_items и tracks.

SELECT

ii.InvoiceId,

ii.UnitPrice,

ii.Quantity,

t. Name,

t. Composer,

t. Milliseconds

FROM

invoice_items ii

INNER JOIN

tracks t

ON ii.TrackId = t.TrackId

Напоминание

При создании соединения для каждой таблицы мы используем короткие псевдонимы, а затем, выявив общее поле, связываем таблицы друг с другом. В нашем случае зададим t для таблицы tracks и ii для таблицы invoice_items, так как i уже используется для таблицы invoices.

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

CREATE VIEW V_Tracks_InvoiceItems AS

SELECT

ii.InvoiceId,

ii.UnitPrice,

ii.Quantity,

t. Name,

t. Composer,

t. Milliseconds

FROM

invoice_items ii

INNER JOIN

tracks t

ON ii.TrackId = t.TrackId

Теперь возьмем из главы 6 соединение, объединяющее таблицы invoices, customers и employees, и сохраним как представление.

CREATE VIEW V_inv_cus_emp AS

SELECT

i. InvoiceId,

i. InvoiceDate,

i. Total,

i. CustomerId,

c. FirstName,

c. LastName,

c. SupportRepId,

e. EmployeeId,

e. LastName,

e. FirstName,

e. Title

FROM

invoices AS i

INNER JOIN

customers AS c

ON

i. CustomerId = c.CustomerId

INNER JOIN

employees AS e

ON

e. EmployeeId = c.SupportRepId

ORDER BY

InvoiceDate

Примечание

В главе 6 мы немного изменили это соединение. Из условия SELECT мы удалили символ *. Смысл представлений — показать именно то, что необходимо. Таким образом, мы включили только необходимые поля из таблиц invoices, customers и employees.

Мы видим, что оба этих соединения сохранены как представления на вкладке Database Structure (Структура базы данных) (рис. 126).

Рис. 126

Теперь, когда у нас имеются эти два представления, мы используем их во внутреннем соединении:

SELECT *

FROM

V_Tracks_InvoiceItems ii

INNER JOIN

V_inv_cus_emp ice

ON

ii.InvoiceId = ice.InvoiceId

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

Примечание

Соединенные представления будут работать только в том случае, если ключевые поля, общие для всех этих таблиц, были включены в первоначальное соединение.

Ранее мы уже описали один способ удаления представления — щелчок правой кнопкой мыши по имени представления на вкладке Database Structure (Структура базы данных). Представление также можно удалить с помощью команды DROP. Это выглядит следующим образом:

DROP VIEW

V_AvgTotal

Она удалит представление V_AvgTotal. Из базы данных удаляется только представление — синтаксис DROPVIEW не влияет на данные.

Примечание

Если вы удалите представление, на которое ссылаются другие представления, эти представления больше не будут работать.

Внимание

DROP — это команда для удаления любых элементов. Она может также удалить из базы данных таблицу. В следующей главе вы изучите инструменты, с помощью которых можно редактировать или удалять данные без возможности восстановления. Как вы уже знаете, представление также можно удалить из базы данных, щелкнув по нему правой кнопкой мыши и выбрав опцию Remove View (Удалить представление).

Для этого задания используем запрос из главы 8:

SELECT

BillingCity,

AVG(Total) AS [City Average],

(SELECT

avg(total)

from

invoices) AS [Global Average]

FROM

invoices

GROUP BY

BillingCity

ORDER BY

BillingCity

1. Из запроса SELECT возьмите внутренний запрос и создайте из него представление. Сохраните представление с именем V_GlobalAverage.

2. Удалите подзапрос из приведенного выше кода и замените его вновь созданным представлением V_GlobalAverage.

3. Сохраните этот новый запрос как представление с именем V_CityAvgVsGlobalAvg.

4. Удалите представление V_GlobalAverage. Как будет работать V_CityAvgVsGlobalAvg?

• Представления — это виртуальные запросы, созданные с помощью SQL, на которые можно ссылаться в других запросах.

• Представления создаются посредством добавления в начало запроса следующего условия: CREATEVIEWV_VIEWNAMEAS.

• Представления особенно полезны при написании длинных запросов.

• Представления можно изменять и удалять с помощью функций из вашей версии СУБД или с помощью команд SQL.

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

Внимание

С этими командами лучше попрактиковаться в «песочнице», например на нашей учебной базе данных. Использование DML в рабочей базе с реальными данными может привести к фатальным последствиям.

До сих пор мы говорили о том, что основная цель SQL-запросов — извлечение существующих данных из базы данных и преобразование их в полезную информацию (на примере компании sTunes). Но, как мы упоминали во введении, SQL выполняет гораздо более сложные задачи, чем просто превращение данных в значимую информацию. Существуют такие специалисты, как разработчики баз данных и администраторы баз данных, контролирующие рост, улучшение и управление базой данных компании. Функции этих специалистов различаются в зависимости от компании и реализации базы данных. Даже опытные пользователи SQL расходятся во мнениях о том, является ли язык DML (Data Manipulation Language — язык управления данными) отдельной областью изучения или его следует изучать совместно с операторами SQL, предназначенными только для извлечения информации.

На мой взгляд,

новичкам могут быть непонятны различия в ролях тех, кто работает с базами данных. Язык DML отлично подходит для администрирования и разработки баз данных. Если компания небольшая и в ней используется только одна база данных, роли аналитика, разработчика и администратора могут быть назначены одному человеку, и этим человеком можете быть вы! Так что даже если ваша основная цель — просто научиться писать необходимые запросы, важно понять работу DML.

Данными в РСУБД можно управлять с помощью следующих DML-команд: INSERT, UPDATE и DELETE. Как следует из названий, эти команды**** могут использоваться для добавления, изменения и удаления данных из таблиц в базе данных. Для нашей базы sTunes мы продемонстрируем, как выполнить задание от руководства компании на добавление новых исполнителей к нашей музыкальной базе, добавление новых записей, а затем их удаление.

Примечание

В DB Browser при внесении любых изменений в базу данных sTunes с использованием DML отображается сообщение с предложением сохранить изменения или закрыть файл базы данных без сохранения изменений. Вы можете сделать копию исходного файла базы данных, чтобы попрактиковаться в работе с базой данных, не опасаясь потерять оригинал.

1. Добавьте в базу данных нового клиента.

2. Создайте счет для этого клиента.

3. Удалите этого клиента из базы данных.

• Операторы, рассмотренные в этой главе, изменяют информацию в базах данных. Перед использованием операторов внимательно проверьте правильность и необходимость их использования.

• Данные в РСУБД управляются с помощью следующих DML-команд: INSERT, UPDATE и DELETE.

• Оператор INSERT используется для добавления новых записей в базу данных.

• Оператор UPDATE используется для изменения существующих записей.

• Оператор DELETE используется для удаления записей.

**** INSERT, UPDATE и DELETE могут называться как командами, так и операторами. — Примеч. ред.

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

Я надеюсь, что благодаря этой книге вы познакомились со множеством различных применений SQL. Некоторые вам пригодятся, а другие — нет. Когда восемнадцать лет назад я начал самостоятельно изучать удивительный мир баз данных, компьютерные технологии стремительно развивались. В то время я работал с Visual Basic и уже разрабатывал приложения (внешний интерфейс или визуальный интерфейс программных систем). В конце концов мне пришлось в эти приложения включить базы данных. Когда я начал изучать, как Microsoft Access позволяет наглядно представлять данные и связь между таблицами, я четко понял, как работают данные, и это расширило мое представление о базах данных. Со временем я понял, что данные — это основа моей работы. Более глубокое изучение мира больших данных стало для меня целью. Это может произойти и с вами, и я надеюсь, что это действительно случится. Я призываю своих студентов изучать все смежные дисциплины программирования, которые им могут понадобиться. Подумайте, какая специальность подходит вам лучше всего! Ваш путь никогда не будет прямым. Все трудности, ошибки и промахи на этом пути помогут вам понять, что для вас истинно ценно и важно.

Хотя мы рассказывали в основном о работе аналитика баз данных (именно здесь пригодятся навыки в составлении запросов, применении операторов и получении ответов на практические вопросы), также весьма востребованы проектировщики баз данных. Вы когда-нибудь задумывались, кто решает, какие поля будут содержаться в той или иной таблице или как таблицы будут связаны друг с другом? Это работа проектировщика, или разработчика, баз данных. Например, в главе 6 мы рассказывали о предотвращении избыточности данных в нескольких таблицах. Управление базами данных также связано с ограничениями доступа, резервным копированием и аварийным восстановлением. Эти темы не рассматриваются в моей книге. Если вам интересно узнать больше о том, как создавать и обслуживать базы данных, то, возможно, вам стоит заняться проектированием баз данных.

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

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

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

Что необходимо знать, прежде чем использовать свои навыки с SQL другой версии? Часто этот вопрос задают мне студенты, ищущие сферу применения полученных навыков. Возможно, в требованиях о приеме на работу указано, что требуется опыт работы с конкретной реализацией SQL, например с SQL Server. И в каждой реализации базы данных имеются различия. Я призываю моих студентов не разочаровываться и не зацикливаться на знаниях конкретной версии SQL. Основные принципы, изложенные в этой книге, помогут вам работать с любой базой данных. Преимущества SQL в том, что это универсальный язык данных. Не беспокойтесь, если вам придется работать не с SQLite. Каждая реализация содержит такие же основные атрибуты, как и те, о которых вы узнали в этой книге. В каждой версии есть панель для ввода запросов, кнопка для выполнения операторов, форма обратной связи по вашему запросу и панель, где указано время обработки. Полученный результат всегда представлен в столбцах и строках. Структура реляционной базы данных, о которой вы узнали в главе 1, является отраслевым стандартом, и база данных будет организована в соответствии со стандартами независимо от того, работаете ли вы с Oracle, IBM, Microsoft SQL Server или любыми другими реализациями. Различные реализации (СУБД) — это как марки разных автомобилей. Кнопки, переключатели и подстаканники могут быть расположены немного в другом месте, но основные механизмы, тормоз и педаль газа, будут в одном и том же месте и работать одинаково во всех моделях. Цель SQL — выполнять запросы. Если вы настроитесь именно на составление запросов, все остальное станет на свои места и будет более понятным. Дополнительную информацию о реализациях SQL вы можете найти на сайте https://db-engines.com/en/ranking. На сайте вы также узнаете, какие реализации баз данных в настоящее время используются наиболее часто.

Если основная часть моего профессионального опыта связана с областью, далекой от SQL или программирования, как мне убедить работодателя дать мне шанс попробовать себя в качестве аналитика данных? Этот вопрос я слышу от многих моих студентов, у которых совершенно другой профессиональный опыт, и они только начинают свой путь в области анализа данных. Данные так или иначе связаны с вашей предыдущей профессией, хоть вы можете этого не замечать! Допустим, вы были водителем автобуса. Сначала вы подумаете, что такая профессия далека от профессии аналитика данных, но сейчас мы попробуем убедить вас в обратном. В течение каждой поездки вы видите, на каких остановках толпится больше людей, какие маршруты наиболее эффективны и т. д. В любой профессии всегда присутствуют данные. Вы можете начать собирать информацию о каждой поездке на автобусе и предоставлять эту информацию своему работодателю. Даже если вы далеки от технической сферы, вы можете использовать навыки, полученные благодаря этой книге. Собирайте данные о любой сфере жизни и формируйте базу! Если базы данных не существует, вы всегда можете ее создать!

Зачастую студенты задают мне вопросы о том, как убедить их компанию предоставить им возможность доступа к базе данных, особенно если они начали работу в компании на другой должности. Студенты объясняют работодателю, что их запросы никоим образом не изменят базу данных, но те по-прежнему не хотят предоставлять им даже доступ для чтения. Как их убедить? Компания может ограничить доступ к базе данных. Ограничения могут возникать по разным причинам, но в таких случаях важно преподать правильно свои стремления и настаивать на ценности преобразования данных в значимую информацию, как мы показали в этой книге. Объясните, как ваша работа поможет компании сэкономить деньги. В большинстве случаев работа с базами данных разделяется на три этапа: разработка, тестирование и эксплуатация. Вы можете запросить доступ к среде разработки или, если база данных небольшая, запросить копию. Но существует и более важный вопрос. Не позволяйте препятствиям мешать вам в достижении вашей цели. Данные есть везде. Как сказал Боб Марли, «когда одна дверь закрыта, не сомневайтесь — другая открыта». Возможно, вы сможете найти аналогичную базу данных в той же области, где работает и ваша компания. Существуют общедоступные хранилища данных (например, data.gov для США), которые помогут вам практиковать ваши навыки.

Наука о данных развивается гораздо быстрее, чем SQL. Для студентов, желающих расширить свои навыки и выйти за рамки написания запросов в текстовом браузере SQL, визуализация данных считается хорошим вариантом. Получение практически значимой информации для многих людей бывает проблемой. Как вы уже знаете из использования DB Browser и реализации SQLite, SQL все еще существует в очень функциональном, но визуально непривлекательном мире скриптовых языков программирования.

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

Рис. 128

В интернете есть множество статей, где предлагается перечень технических тем, которые вы «должны изучить» перед собеседованием, устраиваясь на любую работу, связанную с SQL. Зачастую эти «десять основных технических навыков, которые вам необходимо знать» примерно так же полезны, как списки «десяти самых опасных животных, которые вас могут убить». Я не считаю, что на технических собеседованиях надо просить соискателя запомнить синтаксис и применить его к очень конкретному сценарию. Я полагаю, что гораздо лучше выяснить, есть ли у него общее представление о предмете, решает ли он технические задачи и может ли описать этапы, необходимые для достижения желаемого результата. Если возможность карьерного роста зависит только от того, как вы способны запоминать информацию, это может показывать, как компания относится к созданию новых технических решений, и должно настораживать. Намного лучше, если работодатель будет больше заинтересован тем, как вы решаете проблемные вопросы, чем тем, как хорошо вы сможете запомнить синтаксис. Некоторые интервьюеры могут сосредоточиться на конкретном инструменте, таком как синтаксис представления, или могут попросить вас решить задачу с помощью определенного оператора SQL. Этот метод интервьюирования, на мой взгляд, недальновиден, он фокусируется на механической памяти. Гораздо важнее увидеть, насколько креативен потенциальный сотрудник при применении представления или другого SQL-решения для ответа на поставленный вопрос.

Существует множество различных программ сертификации по SQL и администрированию баз данных. Примеры наиболее распространенных сертификатов — это Microsoft Certified Solutions Associate (MCSA) и Microsoft Certified Solutions Expert (MCSE). Однако Microsoft — не единственный вариант. Существуют и другие платформы баз данных, такие как Oracle и IBM, которые также являются крупными игроками в сфере баз данных, предлагающими сертификаты. Действительно ли сертификаты необходимы? Сертификация — не единственный путь в вашей карьере в области SQL. Я верю, что вы можете получить столько же знаний, если не больше, когда станете практиком. Для меня способность использовать язык на практике более важна, чем сертификация. Если ваша компания использует IBM, пройдите сертификацию. Но если вы не уверены, просто практикуйтесь в решении реальных вопросов с любой необходимой реализацией SQL.

Я искренне надеюсь, что вам понравилась книга и что я смог вас увлечь этой темой. Дополнительную информацию о деятельности моей компании по визуализации данных и об учебных курсах, которые я предлагаю, вы можете найти на http://datadecided.com. Мне было приятно сопровождать вас в этом увлекательном путешествии в мире баз данных.

Используя вкладку Database Structure (Структура базы данных) и вкладку Browse Data (Просмотр данных), ответьте на следующие вопросы.

Вопрос 1. Сколько таблиц в нашей базе данных?

Решение. В DB Browser перейдите на вкладку Database Structure (Структура базы данных), где количество таблиц указано в скобках (). В нашей базе данных тринадцать таблиц.

Рис. 129

Вопрос 2. Сколько полей в таблице tracks?

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

Рис. 130

Из примера следует, что таблица tracks состоит из девяти столбцов.

Вопрос 3. Какие типы данных указаны в этой таблице?

Решение. На рис. 130 видно, что столбец TrackId принимает данные типа INTEGER, а столбец Name принимает данные типа NVARCHAR. Остальные столбцы также принимают типы INTEGER и NVARCHAR за исключением UnitPrice, который принимает тип данных NUMERIC.

Вопрос 4. Как выглядят данные в таблице?

Решение. Перейдите на вкладку Browse Data (Просмотр данных) и проанализируйте таблицу. Необходимо в раскрывающемся меню выбрать таблицу tracks. Анализ данных в таблице показывает, почему тип данных INTEGER используется для таких столбцов, как TrackId и AlbumId, в то время как символьный тип данных имеет больше смысла для столбцов Name и Composer. Наконец, для UnitPrice необходим тип данных с десятичными знаками, а целочисленного типа данных для этого столбца недостаточно.

Рис. 131

Вопрос 1. Напишите запрос, чтобы узнать количество клиентов, фамилии которых начинаются с буквы B.

Решение. Чтобы ответить на этот вопрос, сначала надо написать запрос для отображения конкретной информации, которую мы ищем. В данном случае нас интересуют фамилии. Фамилии содержатся в таблице клиентов в поле LastName.

Следующий запрос отображает все фамилии.

SELECT

LastName

FROM

customers

В результате данного запроса отображаются все фамилии, но они не расположены в нужном порядке. Чтобы расположить их по алфавиту, мы можем использовать оператор ORDERBY. Обратите внимание, что нам не нужно указывать A — Z, так как по умолчанию данные отображаются в порядке возрастания. Если бы нам требовалось получить имена, начинающиеся с буквы Z, мы использовали бы оператор DESC.

SELECT

LastName

FROM

customers

ORDER BY

LastName ASC

Рис. 132

Теперь наши результаты расположены в алфавитном порядке, и мы можем видеть, что четыре записи начинаются с буквы B. Обратите внимание, что мы все еще посредством визуального наблюдения определяем, сколько записей начинается с буквы B. Другие способы мы рассмотрим ниже.

Вопрос 2. Какая компания при сортировке в порядке убывания появляется в верхней строке таблицы customers?

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

SELECT

Company

FROM

customers

ORDER BY

Company DESC

Получим следующий результат.

Рис. 133

Мы видим, что компания Woodstock Discos — первая в списке по убыванию.

Вопрос 3. Какое количество клиентов не указали почтовый индекс?

Решение. Мы могли бы ответить на этот вопрос, прокручивая данные в разделе Browse Data (Просмотр данных), но есть более эффективный способ. Используя условие SELECT, мы можем перечислить все данные в порядке возрастания. Но на этот раз нам необходимо перечислить более одного столбца, чтобы мы могли видеть, каким именам клиентов не соответствуют данные почтового индекса. Таким образом, мы выберем поля FirstName, LastName и PostalCode, а затем отсортируем результаты по PostalCode.

SELECT

FirstName,

LastName,

PostalCode

FROM

customers

ORDER BY

PostalCode

В результате отобразятся четыре записи, которые не имеют почтовых данных, что обозначено значением null в столбце PostalCode (рис. 134).

Рис. 134

Примечание

Если поля перечислить в порядке убывания, придется прокрутить вниз полосу прокрутки, чтобы увидеть нулевые значения.

Вопрос 1. Создайте запрос для таблицы invoices, включающий оператор CASE, который будет отмечать все продажи из США — страны, откуда выставлен счет, как Domestic Sales (Продажи на внутреннем рынке), а все другие продажи — как Foreign Sales (Продажи за рубежом). После оператора ENDAS создайте новое поле SalesType.

Решение. Чтобы отобразить эту информацию, необходимо применить фильтрацию, используя оператор CASE. Поскольку мы классифицируем наш оператор CASE по стране, где был выставлен счет, необходимо добавить это поле в условие SELECT.

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

BillingCountry,

Total,

CASE

WHEN BillingCountry = 'USA' THEN 'Domestic Sales'

ELSE 'Foreign Sales'

END AS SalesType

FROM

invoices

Рис. 135

Вопрос 2. Отсортируйте эти данные по новому полю SalesType.

Решение. Чтобы отобразить все внутренние продажи в одной группе и все зарубежные продажи в другой группе, необходимо к уже существующему запросу добавить условие ORDERBY (используя новое поле):

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

BillingCountry,

Total,

CASE

WHEN BillingCountry = 'USA' THEN 'Domestic Sales'

ELSE 'Foreign Sales'

END AS SalesType

FROM

invoices

ORDER BY

SalesType

На рис. 136 показаны результаты данного запроса, где сначала отображаются Domestic Sales (Продажи на внутреннем рынке). Если вы запустите этот запрос и прокрутите вниз, вы увидите, что все страны, кроме США, отмечены как Foreign Sales (Продажи за рубежом).

Рис. 136

Вопрос 3. Сколько счетов от продаж на внутреннем рынке превышают сумму $15?

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

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

BillingCountry,

Total,

CASE

WHEN BillingCountry = 'USA' THEN 'Domestic Sales'

ELSE 'ForeignSales'

END AS SalesType

FROM

invoices

Where

SalesType = «Domestic Sales» AND Total > 15

Рис. 137

Вопрос 1. Используя DB Browser и вкладку Browse Data (Просмотр данных) или ER-диаграмму (рис. 65), проанализируйте таблицу tracks. Определите, какие поля в этой таблице будут внешними ключами в другой таблице. На основании определенных вами внешних ключей определите, какие таблицы связаны с таблицей tracks.

Решение. Анализируя таблицу tracks, мы видим три поля с целочисленными значениями, которые могут быть внешними ключами.

Поля AlbumId, MediaTypeId и GenreId соответствуют таблицам albums, media_types и genres соответственно.

Рис. 138

Вопрос 2. Создайте внутреннее соединение между таблицами albums и tracks и отобразите названия альбомов и названия треков в едином наборе результатов.

Решение:

SELECT

t. composer AS "Artist Name",

a. title AS "Album Title",

t. Name AS "Track Name"

FROM

albums a

INNER JOIN

tracks t

ON

a. AlbumId = t.AlbumId

Вопрос 3. Создайте третье внутреннее соединение — с таблицей genres, которую вы нашли, отвечая на вопрос 1. Включите в ваш набор результатов поле Name из этой таблицы.

Решение:

SELECT

g. name AS Genre,

t. composer AS "Artist Name",

a. title AS "Album Title",

t. Name AS "Track Name"

FROM

albums a

INNER JOIN

tracks t

ON

a. AlbumId = t.AlbumId

INNER JOIN

genres g

ON

g. GenreId = t.GenreId

Вопрос 1. Создайте однострочный список рассылки для всех клиентов из США, включая полные имена, написанные заглавными буквами, и полные адреса с пятизначными почтовыми индексами, в следующем формате:

FRANK HARRIS 1600 Amphitheatre Parkway, Mountain View, CA 94043

Решение. В приведенном выше формате имя и фамилия должны быть написаны в верхнем регистре, поэтому для этих двух полей мы воспользуемся функцией UPPER(). Для объединения остальных полей используем символ ||, добавляя, где необходимо, пробелы и запятые.

SELECT

UPPER(FirstName) || ' ' || UPPER(LastName) || ' '

|| Address || ', ' || City || ', ' || State || ' '

|| SUBSTR(PostalCode,1,5) AS [MailingAddress]

FROM

customers

WHERE

Country = 'USA'

Рис. 139

Вопрос 2. Каковы средние годовые продажи клиентам из США согласно имеющимся данным за все годы?

Решение. Если мы просто ищем агрегатную функцию для одной страны, то можем выбрать страну, где был выставлен счет, и среднее значение от общей суммы, используя условие WHERE, чтобы ограничить наши результаты для одной страны — США.

SELECT

BillingCountry,

AVG(Total)

FROM

invoices

WHERE

BillingCountry = 'USA'

Рис. 140

Напоминание

Чтобы сократить количество возвращаемых знаков после запятой, мы можем использовать функцию ROUND() вне функции AVG().

Вопрос 3. Каков общий объем продаж компании за все время?

Решение. Поскольку в данном запросе задается общая сумма счетов, условие SELECT выглядит довольно просто:

SELECT

SUM(Total)

FROM

invoices

Рис. 141

Вопрос 4. Кто входит в десятку лучших клиентов с точки зрения совершенных ими покупок? Подсказка: чтобы ответить на этот вопрос, необходимо использовать соединение (глава 6).

Решение. Значение общей суммы найдено. Теперь необходимо отобразить первую десятку клиентов, приносящих наибольшую прибыль. Поскольку мы ищем данные из одной таблицы, которые соответствуют данным из другой таблицы во взаимно однозначном отношении, мы используем внутреннее соединение.

SELECT

SUM(Total)AS [Revenue Total],

c. FirstName,

c. LastName

FROM

invoices i

INNER JOIN

customers c

ON

i. CustomerId = c.CustomerId

GROUP BY c.CustomerId

ORDER BY SUM(Total) DESC

Вопрос 1. Сколько счетов превышает среднюю сумму счетов, выставленных в 2010 году?

Решение. Чтобы ответить на этот вопрос, необходимо решить две задачи. Во-первых, следует найти среднюю сумму счета-фактуры, сгенерированную в 2010 году. Во-вторых, необходимо сравнить это значение с каждым счетом в таблице, чтобы увидеть, сколько из них превышает среднюю стоимость счета-фактуры за 2010 год.

Сначала напишем следующий подзапрос:

select

avg(total)

from

invoices

where

InvoiceDate between '2010-01-01' and '2010-12-31'

В результате выполнения данного запроса мы получим среднее значение $5,80. Теперь необходимо написать внешний запрос для выбора счетов, превышающих средний показатель за 2010 год.

SELECT

InvoiceDate,

Total

FROM

invoices

WHERE

Total >

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

SELECT

BillingCity,

AVG(Total) AS [City Average],

(select

avg(total)

from

invoices) AS [Global Average]

FROM

invoices

GROUP BY

BillingCity

ORDER BY

BillingCity

Вопрос 1. Из запроса SELECT возьмите внутренний запрос и создайте из него представление. Сохраните его с именем V_GlobalAverage.

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

Решение. Во внутреннем запросе в первую строку добавим синтаксис представления.

CREATE VIEW V_GlobalAverage AS

select

avg(total)

from

invoices AS [Global Average]

Вопрос 2. Удалите подзапрос из приведенного выше кода и замените его вновь созданным представлением V_GlobalAverage.

Решение. При использовании представления в условии SELECT мы используем символ *.

SELECT

BillingCity,

AVG(Total) AS [City Average],

(select

*

from

V_GlobalAverage) AS [Global Average]

FROM

invoices

GROUP BY

BillingCity

ORDER BY

BillingCity

Вопрос 3. Сохраните этот новый запрос как представление с именем V_CityAvgVsGlobalAvg.

Решение. Копируем код из вопроса 2 и в верхнюю часть запроса добавляем оператор CREATEVIEW.

CREATE VIEW V_CityAvgVsGlobalAvg AS

SELECT

BillingCity,

AVG(Total) AS [City Average],

(select

*

from

V_GlobalAverage) AS [Global Average]

FROM

invoices

GROUP BY

BillingCity

ORDER BY

BillingCity

Вопрос 4. Удалите представление V_GlobalAverage. Как будет работать V_CityAvgVsGlobalAvg?

Решение. Чтобы удалить наше представление, воспользуемся условием DROPVIEW. Кроме того, на вкладке Database Structure (Структура базы данных) в DB Browser мы можем щелкнуть правой кнопкой мыши по представлению и удалить его.

DROP VIEW V_GlobalAverage

Теперь, чтобы увидеть, как это повлияет на наши предыдущие операторы, необходимо написать условие SELECT для выбора виртуальной таблицы.

V_CityAvgVsGlobalAvg

SELECT

*

FROM

V_CityAvgVsGlobalAvg

В результате мы получим следующее сообщение об ошибке:

no such table: main.V_GlobalAverage:

(таблица не существует: main.V_GlobalAverage:)

Вопрос 1. В таблицу customers добавьте новую запись.

Решение. Сначала необходимо добавить новую запись в таблицу customers. Клиент может существовать сам по себе, не будучи упомянутым в какой-либо другой таблице (если он еще не совершил покупку). Для начала вставьте запись в таблицу customers.

INSERT INTO

customers

VALUES ('60', 'New', 'Customer', '', '123 Day Street', 'New York', 'NY', 'USA', '11201', '(347) 525-8688', '', 'nc@gmail.com', '1');

Примечание

Некоторые поля оставим пустыми, поставив рядом две одинарные кавычки. Запустим запрос на выполнение и проанализируем результаты.

SELECT

*

FROM

customers

WHERE

FirstName = 'New'

Примечание

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

Рис. 144

Вопрос 2. Создайте счет для этого клиента.

Решение. Чтобы создать запись счета для нашего нового клиента, нам необходимо обратить особое внимание на поля в таблице invoices, которые соответствуют таблице customers. Например, в наших счетах используется тот же адрес, который отображается в таблице customers.

INSERT INTO

invoices

VALUES ('413', '60', '2019-10-04 00:00:00', '123 Day Street', 'New York', 'NY', 'USA', '10201', '50.00')

Вопрос 3. Удалите этого клиента из базы данных.

Решение. Как мы упоминали в главе 10, рекомендуется просмотреть данные, которые необходимо удалить. В этом случае данные, которые мы удаляем, содержатся в двух таблицах, поэтому для просмотра всех необходимых нам данных мы воспользуемся условием INNERJOIN.

SELECT

c. FirstName,

c. LastName,

i. Total,

i. InvoiceId

FROM

invoices i

INNER JOIN

customers c

ON i.CustomerId = c.CustomerId

WHERE c.CustomerId = 60

Теперь, когда мы подтвердили данные, мы можем использовать оператор DELETE.

DELETE FROM

invoices

WHERE CustomerId = 60

Примечание

Операторы в SQL используются в условиях SQL.

Рис. 145

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

Total

FROM

invoices

WHERE

Total = 1.98 — Возвращает только записи, где Total = 1.98

ORDER BY

InvoiceDate

Примечание

Оператор RIGHT JOIN не поддерживается в SQLite, но поддерживается в других реализациях СУБД.

Внутреннее соединение

SELECT

i. InvoiceId, — Указывает ссылку на поле в таблице

c. CustomerId,

c. Name,

c. Address,

i. InvoiceDate,

i. BillingAddress,

i. Total

FROM

invoices AS i

INNER JOIN

customers AS c

ON i.CustomerId = c.CustomerId

Рис. 148

Примечание

SQLite распознает гораздо больше функций, чем включено в эту главу. Более подробную информацию вы можете найти на сайте https://www.sqlite.org/lang_corefunc.html.

Базовый подзапрос:

Рис. 149

Рис. 150

Условие DISTINCT:

SELECT

DISTINCT TrackId

FROM

invoice_items

ORDER BY

TrackId

CREATE VIEW V_ViewName AS [Alias Name]

Внимание

Язык управления данными (DML) позволяет изменять базу данных. С этими командами лучше попрактиковаться в «песочнице», например на учебной базе данных. Использование DML в рабочей базе с реальными данными может привести к фатальным последствиям.

INSERT INTO

artists (Name)

VALUES ('Bob Marley')