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

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

Уолтер Шилдс

2022

Переводчик А. Павлов

СПб.: Питер, 2022.

ISBN 978-5-4461-1835-9

© ООО Издательство "Питер", 2022

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

Хочу выразить особую благодарность моей семье: Жюльену, Максу, Эльке и Норме. Я не смог бы написать ни строчки без их терпения и поддержки.

Введение

С каждым днем — а точнее с каждой секундой — объем данных увеличивается. За время, которое вам понадобится, чтобы дочитать это предложение, будет отправлено более 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 — один из наиболее востребованных языков программирования. Он применяется для работы в области администрирования баз данных и во множестве других технических областей, включая разработку и тестирование программного обеспечения, бизнес-анализ и прочее. Рассмотрим профессии, где требуется знание 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.

Часть I. Создание среды обучения SQL

Глава 1. Структура базы данных

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