В SQL City произошло убийство! Тайна убийства SQL задумана как самостоятельный урок по изучению концепций и команд SQL, так и увлекательная игра для опытных пользователей SQL по раскрытию интригующего преступления.
Пошаговое руководство для начинающих SQL
Если вы хорошо разбираетесь в SQL, вы можете пропустить эти объяснения и проверить свои навыки! Ниже мы представим некоторые базовые концепции SQL и достаточно деталей, чтобы раскрыть убийство. Если вы хотите получить более полное представление о SQL, попробуйте выбрать Star SQL.
Произошло преступление, и детективу нужна ваша помощь. Детектив дал вам отчет о месте преступления, но вы каким-то образом потеряли его. Вы смутно помните, что преступлением было убийство, которое произошло где-то 15 января 2018 года и что оно произошло в SQL City. Начните с получения соответствующего отчета о месте преступления из базы данных полицейского управления.
Все ключи к разгадке этой тайны спрятаны в огромной базе данных, и вам нужно использовать SQL для навигации по этой обширной сети информации. Ваш первый шаг к разгадке тайны - получить соответствующий отчет о месте преступления из базы данных полицейского управления. Ниже мы объясним на высоком уровне команды, которые вам нужно знать; когда вы будете готовы, вы можете начать адаптировать примеры для создания своих собственных SQL-команд в поисках подсказок - вы можете запустить любой SQL в любом из блоков кода, независимо от того, что было в нем, когда вы начинали.
Некоторые определения
Что такое SQL?
SQL, что расшифровывается как язык структурированных запросов, - это способ взаимодействия с реляционными базами данных и таблицами таким образом, который позволяет нам, людям, собирать конкретную, значимую информацию.
Подождите, что такое реляционная база данных?
Для слова "база данных" нет единого определения. В общем, базы данных - это системы для управления информацией. Базы данных могут иметь различную структуру, наложенную на данные. Когда данные более структурированы, это может помочь людям и компьютерам работать с данными более эффективно.
Реляционные базы данных, вероятно, являются наиболее известным видом баз данных. По своей сути реляционные базы данных состоят из таблиц, которые во многом похожи на электронные таблицы. Каждый столбец в таблице имеет имя и тип данных (текст, число и т.д.), И каждая строка в таблице является конкретным экземпляром того, о чем "идет речь" в таблице. "Реляционная" часть содержит конкретные правила о том, как соединять данные между разными таблицами.
Что такое ERD?
ERD, что расшифровывается как диаграмма отношений сущностей, представляет собой визуальное представление связей между всеми соответствующими таблицами в базе данных. Вы можете найти ERD для нашей базы данных SQL Murder Mystery ниже. На диаграмме показано, что у каждой таблицы есть имя (вверху поля, выделено жирным шрифтом), список имен столбцов (слева) и соответствующие им типы данных (справа, прописными буквами). На ERD также есть несколько значков золотых ключей, синих и серых стрелок. Золотой ключ указывает, что столбец является первичным ключом соответствующей таблицы, а синяя стрелка указывает, что столбец является внешним ключом соответствующей таблицы.
Первичный ключ:
уникальный идентификатор для каждой строки в таблице.
Внешний ключ:
используется для сопоставления данных в одной таблице с данными в другой таблице.
Если две таблицы связаны, совпадающие столбцы, то есть общие идентификаторы двух таблиц, соединяются серой стрелкой на диаграмме.
Вот ERD для нашей базы данных:
Что такое запрос?
Если бы вы посмотрели на данные в этой базе данных, вы бы увидели, что таблицы огромны! Точек данных так много; просто невозможно просматривать таблицы строка за строкой, чтобы найти нужную нам информацию. Что мы должны делать?
Вот тут-то и появляются запросы. Запросы - это инструкции, которые мы создаем для получения данных из базы данных. Запросы читаются как естественный английский (по большей части). Давайте попробуем выполнить несколько запросов к нашей базе данных. Для каждого из полей ниже нажмите кнопку "Выполнить", чтобы "выполнить" запрос в поле. Вы можете редактировать запросы прямо здесь, на странице, для дальнейшего изучения. (Обратите внимание, что команды SQL не чувствительны к регистру, но обычно их пишут заглавными буквами для удобства чтения. Вы также можете использовать новые строки и пробелы по своему усмотрению для форматирования команды для удобства чтения. Большинство систем баз данных требуют, чтобы вы заканчивали запрос точкой с запятой (';'), хотя система их выполнения на этой веб-странице более щадящая.)
Сколько всего людей в этой базе данных?
Не беспокойтесь о том, что именно это означает, но знайте, что вы можете изменить 'person' на любую другую таблицу из ERD, чтобы узнать, сколько строк в этой таблице. Попробуйте!
Что мы знаем об этих людях?
Если вам нужны все данные для каждой строки таблицы, используйте "*" после "ВЫБРАТЬ". Как вы только что узнали, здесь тысячи людей, поэтому вместо того, чтобы просматривать их всех, мы ограничим результаты только первыми 10. Запустите это, затем измените имя таблицы или предельный номер и посмотрите, что произойдет.
Каковы возможные значения для столбца?
При работе с данными всегда проверяйте, сможете ли вы найти документацию, объясняющую структуру базы данных (например, ERD) и допустимые значения. Но иногда это недоступно. Здесь мы покажем, как ключевое слово DISTINCT может дать вам быстрый взгляд на то, какие значения находятся в базе данных. После его запуска удалите слово DISTINCT и запустите его снова. Видите разницу? (После того, как вы попробуете это, возможно, вам захочется нажать кнопку "Сброс" и запустить еще раз, прежде чем продолжить.)
Какие элементы содержит SQL-запрос?
SQL-запрос может содержать:
Ключевые слова SQL (например, SELECT и FROM above)
Имена столбцов (например, столбец name выше)
Имена таблиц (например, таблица person выше)
Подстановочные знаки (такие как %)
Функции
Конкретные критерии фильтрации
И т. д
Ключевые слова SQL
Ключевые слова SQL используются для указания действий в ваших запросах. Ключевые слова SQL не чувствительны к регистру, но мы рекомендуем использовать все заглавные буквы для ключевых слов SQL, чтобы вы могли легко отделить их от остальной части запроса. Вот некоторые часто используемые ключевые слова:
ВЫБЕРИТЕ
SELECT позволяет нам извлекать данные для определенных столбцов из базы данных:
* (звездочка): используется после ВЫБОРА для захвата всех столбцов из таблицы;
имя_колонок: чтобы выбрать определенные столбцы, поместите имена столбцов после SELECT и разделяйте их запятыми.
От
С позволяет нам указать, какие таблицы нас интересуют; чтобы выбрать несколько таблиц, перечислите имена таблиц и разделяйте их запятыми. (Но пока вы не выучите ключевое слово JOIN, вы можете быть удивлены тем, что произойдет. Об этом позже.)
ГДЕ
Предложение WHERE в запросе используется для фильтрации результатов по определенным критериям.
Давайте попробуем некоторые из этих приемов.
Вот простой запрос, позволяющий получить все о конкретном человеке. (Не волнуйтесь — все SSN выдуманы.)
Обратите внимание, что вам нужно использовать одинарные прямые кавычки (') вокруг буквального текста, чтобы база данных могла отличить его от имен таблиц и столбцов. После запуска повторите попытку с Есенией Фоссен, Тедом Денфипом или Давиной Гангвер.
Ключевое слово AND используется для объединения нескольких критериев фильтрации, чтобы отфильтрованные результаты соответствовали каждому из критериев. (Есть также ключевое слово OR, которое возвращает строки, соответствующие любому из критериев.)
Этот запрос возвращает отчеты только об определенном типе преступлений в определенном городе.
Обратите внимание, что при запросе текстовых значений вы должны соответствовать данным, которые есть в базе данных. Попробуйте изменить 'Chicago' на 'чикаго' и запустить инструкцию. Затем посмотрите, сможете ли вы отредактировать этот оператор SQL, чтобы найти первую подсказку, основанную на приведенной выше подсказке.
Если вы еще не нашли нужный отчет о месте преступления, нажмите "показать решение" выше и замените содержимое поля только показанной командой. (Опустите начальную команду /*) Если вы разобрались с запросом, который показывает один отчет о месте преступления вместо нескольких для одного и того же города и типа, то поздравляем и не обращайте внимания на слово "неверный". Вы поймете, что поняли!
Подстановочные знаки и другие функции для частичных совпадений
Иногда вы знаете только часть необходимой вам информации. SQL может справиться с этим. Специальные символы, обозначающие неизвестные символы, называются "подстановочными знаками", и SQL поддерживает два из них. Наиболее распространенным является % подстановочный знак.
Когда вы помещаете % подстановочный знак в строку запроса, система SQL возвращает результаты, которые в точности соответствуют остальной части строки, и в которых есть что-либо (или ничего) вместо подстановочного знака. Например, 'Ca%a' совпадения Canada и California.
Другой, менее часто используемый подстановочный знак - _. Это означает "соответствовать остальной части текста, при условии, что ровно один символ находится в точной позиции _, независимо от того, что это такое. Итак, 'B_b' соответствовало бы 'Bob' and 'Bub', но не 'Babe' or 'Bb'.
Важно: При использовании подстановочных знаков вы не используете = символ; вместо этого вы используете LIKE.
Попробуйте использовать несколько подстановочных знаков.
После выполнения этой команды попробуйте такие варианты, как 'Irvin_' и 'I% e', а затем изучите еще несколько.
SQL также поддерживает числовые сравнения, такие как < (меньше) и > (больше). Вы также можете использовать ключевые слова BETWEEN и AND - и все они работают как со словами, так и с числами.
Попробуйте несколько операторов сравнения.
Мы упоминали, что команды SQL не чувствительны к регистру, но WHERE значения запроса для = и LIKE чувствительны. Иногда вы не знаете, как текст хранится в базе данных. SQL предоставляет пару функций, которые могут сгладить эту проблему для вас. Они называются UPPER() и LOWER(), и вы, вероятно, сможете понять, что они делают, особенно если ознакомитесь с приведенной ниже вставкой.
Попробуйте UPPER() и LOWER().
Копаем глубже
Агрегатные функции SQL
Иногда вопросы, которые вы хотите задать, не так просты, как поиск строки данных, соответствующей набору критериев. Возможно, вы захотите задать более сложные вопросы, такие как “Кто самый старый человек?” или “Кто самый маленький человек?” Агрегатные функции могут помочь вам ответить на эти вопросы. Фактически, вы изучили агрегатную функцию выше, COUNT.
Сколько лет самому старому человеку с водительскими правами? Имея небольшой объем данных, вы могли бы просто просмотреть их, но в drivers_license таблице тысячи записей. (ПопробуйтеCOUNT, если хотите точно знать, сколько их!) Вы не можете просто просмотреть этот список, чтобы найти ответ.
Вот несколько полезных агрегатных функций, предоставляемых SQL:
МАКС находит максимальное значение
Мин. находит минимальное значение
СУММА вычисляет сумму значений указанных столбцов
СРЕДНЕЕ значение вычисляет среднее значение указанных значений столбца
КОЛИЧЕСТВОподсчитывает количество заданных значений столбца
Запустите этот запрос, затем попробуйте некоторые другие агрегатные функции.
Есть еще один способ найти минимальные и максимальные значения, одновременно просматривая больше данных. Вы можете управлять порядком сортировки получаемых результатов. Это действительно довольно интуитивно понятно: просто используйте ORDER BY, за которым следует имя столбца. Это может быть непросто, когда данных много! (Когда люди серьезно подходят к работе с SQL, они используют инструменты получше, чем эта веб-система.) По умолчанию ПОРЯДОК ПО идет в порядке "возрастания" (от наименьшего к наибольшему или от А до Я), но вы можете указать ASC для возрастания, или вы можете изменить его с помощью DESC .
Запустите этот запрос, чтобы узнать, как управлять порядком сортировки.
После запуска измените ASC на DESC или полностью удалите эту часть, чтобы увидеть, чем отличаются результаты. Попробуйте выполнить сортировку по другим столбцам.
К настоящему моменту вы знаете достаточно SQL, чтобы идентифицировать двух свидетелей. Попробуйте!
Напишите запрос, который идентифицирует первого свидетеля.
Это можно сделать несколькими способами, так что вы можете узнать ответ, даже если результаты будут "Неверными"
Напишите запрос, который идентифицирует второго свидетеля.
Это можно сделать несколькими способами, так что вы можете узнать ответ, даже если результаты будут "Неверными"
Создание соединений
Объединение таблиц
До сих пор мы задавали вопросы, на которые можно было ответить, рассматривая данные только из одной таблицы. Но что, если нам нужно задать более сложные вопросы, которые одновременно требуют данных из двух разных таблиц? Вот тут-то и пригодится JOIN.
Более опытные специалисты по SQL используют несколько различных видов СОЕДИНЕНИЯ - вы можете услышать о ВНУТРЕННЕМ, ВНЕШНЕМ, ЛЕВОМ и ПРАВОМ соединениях. Здесь мы просто поговорим о наиболее распространенном виде СОЕДИНЕНИЯ, внутреннем СОЕДИНЕНИИ. Поскольку это распространенное явление, вы можете не указывать INNER в своих SQL-командах.
Наиболее распространенным способом объединения таблиц является использование столбцов первичного ключа и внешнего ключа. Если вы не помните, что это такое, или чтобы увидеть ключевые связи между таблицами в нашей базе данных, обратитесь к диаграмме отношений сущностей (ERD) выше. Вы можете выполнять объединения для любых столбцов, но ключевые столбцы оптимизированы для получения быстрых результатов. Вероятно, проще показать, как объединения работают с нашей интерактивной системой SQL, чем писать их.
Запустите этот запрос, чтобы определить самых крупных ежегодных получателей дохода в нашей базе данных. (Опять же, поверьте нам, все SSN выдуманы.)
Попробуйте отредактировать запрос, чтобы вернуть больше данных или найти людей с разным доходом. Попробуйте объединить другие таблицы вместе. Вы можете использовать * между SELECT и FROM here, как и в любом другом запросе, так что попробуйте и это.
Иногда требуется подключить более одной таблицы. SQL позволяет вам объединять в запросе столько таблиц, сколько вам захочется.
Давайте посмотрим, есть ли у тех, кто хорошо зарабатывает, другие общие черты.
В этом примере показано, как объединить несколько таблиц. Также, кстати, показано, как вы можете использовать "псевдонимы" для различных таблиц в вашем запросе, чтобы вам не приходилось вводить так много. Наконец, в нем показано, как вы можете изменить способ отображения имени столбца в результатах, что может быть удобно.
Иногда требуется подключить более одной таблицы. SQL позволяет вам объединять в запросе столько таблиц, сколько вам захочется.
Давайте посмотрим, есть ли у тех, кто хорошо зарабатывает, другие общие черты.
В этом примере показано, как объединить несколько таблиц. Также, кстати, показано, как вы можете использовать "псевдонимы" для различных таблиц в вашем запросе, чтобы вам не приходилось вводить так много. Наконец, в нем показано, как вы можете изменить способ отображения имени столбца в результатах, что может быть удобно.
Теперь, когда вы знаете, как объединять таблицы, вы должны быть в состоянии найти протоколы допросов двух свидетелей, которых вы идентифицировали ранее. Попробуйте!
Напишите запрос, который покажет стенограммы интервью для наших двух испытуемых.
Существует более одного способа сделать это, поэтому вы можете узнать ответ, даже если результаты говорят "Неверно". Официальное решение делает это одним запросом, но вам это не обязательно. Технически вам даже не нужно использовать JOIN для получения расшифровок, но попробуйте.
Иди и достань их!
Теперь вы знаете достаточно SQL, чтобы разгадать тайну. Вам нужно будет прочитать ERD и сделать несколько разумных предположений, но другого синтаксиса, который вам нужен, нет!
Найдите убийцу!
Потребуется более одного запроса, чтобы найти убийцу, но вы можете просто продолжать редактировать это поле, попутно делая заметки о результатах. Когда вы решите, что знаете ответ, перейдите к следующему разделу.
У автора есть возможность отправки базы данных на электронную почту подписчика - объем 3,5 Мбт. Оставляйте заявки в комментариях.