У начинающих веб-разработчиков, использующих современные инструменты, часто есть уровень абстракции между ними и уровнем базы данных. В конце концов, большинство новичков вынуждены заглядывать за рамки любого инструмента объектно-реляционного сопоставления (ORM), который они используют, и при этом узнают, как работать с базой данных напрямую. Хотя этот процесс помогает нам быстро научиться работать с базой данных, это плохая замена для изучения концепций более высокого уровня, которые являются важными частями работы баз данных.
В этом сообщении блога я рассмотрю несколько концепций, важных для управления базами данных. Чтобы сообщение оставалось относительно коротким, я не буду останавливаться на таких элементах, как внешние ключи, первичные ключи, ограничения базы данных или множество различных типов соединений.
Этот пост - просто несколько концепций высокого уровня, о которых я хотел бы знать, когда впервые начал работать с базами данных.
Примечание. Приведенные ниже понятия не следует понимать буквально. Напротив, это то, как люди должны думать о том, что происходит.
Концепция №1: все данные в конечном итоге считываются из одной таблицы
Первое, что необходимо подчеркнуть, - это то, что все данные считываются из таблицы. Это кажется очевидным и простым; тем не менее, если мы будем помнить об этой концепции, это поможет упорядочить наши мысли, когда нам придется иметь дело со сложными запросами, включающими предложения объединений, группировать по и т. д.
Например, предположим, что у нас есть две таблицы (также известные как отношения) - люди и собаки:
Что, если бы мы захотели задать вопрос: «Каковы имена всех людей в моей базе данных?» На этот вопрос будет дан ответ с помощью следующего запроса:
SELECT p.first_name FROM persons p;
Но что, если я захочу задать вопрос: «Каковы имена людей в моей базе данных и имена их собак?» Этот вопрос требует, чтобы мы создали новую таблицу. Почему? Потому что все данные считываются из таблицы.
Нам нужно ответить на этот вопрос следующим образом:
SELECT p.first_name, d.name FROM persons p INNER JOIN dogs d ON d.person_id = p.id;
Важно помнить, что с концептуальной точки зрения мы просто создаем новую таблицу, а затем считываем из нее данные.
Независимо от того, насколько сложными становятся наши объединения, в фоновом режиме мы можем полагаться на тот факт, что мы создаем новую таблицу, и просто сосредоточиться на том, как создается эта новая таблица.
Концепция 2: фильтрация выполняется на уровне строк
Когда мы думаем об отфильтровывании данных из нашей единой таблицы, важно помнить, что фильтрация происходит на уровне строк. Буквально в этом случае механизм базы данных будет просматривать каждую строку, сравнивать ее с набором выражений и включать ее в наш набор результатов только в том случае, если он передает все эти выражения.
Например, предположим, мы хотели задать вопрос: «Как зовут всех людей в нашей базе данных и клички их собак, за исключением Джорджа Харрисона»?
Поскольку мы знаем форму наших данных, на этот вопрос можно ответить с помощью следующего запроса:
SELECT p.first_name, p.last_name, d.name FROM persons p INNER JOIN dogs d ON d.person_id = p.id WHERE p.id <> 4;
Исключающая часть этого запроса могла быть выполнена различными способами, но суть та же: после того, как у нас есть наша единственная таблица, из которой будут извлечены все наши данные, нам просто нужно подумать о том, какие строки мы хотим исключить. (или включить, в зависимости от обстоятельств).
Концепция # 3: функция набора должна быть единственным элементом в списке выбора
Примечание: полную картину см. в Концепции 4.
Эта концепция вводит два новых термина, которые мы должны прояснить, прежде чем углубляться в них:
- Установить функцию: встроенная функция, которая принимает имя столбца в качестве параметра; он превращает данные в наших столбцах в вычисленные значения.
- Список выбора: список элементов, который следует после ключевого слова SELECT; часто список столбцов, которые вы хотите вернуть из единой таблицы.
Функции набора позволяют нам брать данные в нашу таблицу и вычислять из нее новые данные. Давайте добавим еще несколько человек в нашу таблицу лиц:
Допустим, мы хотели задать вопрос: «Сколько человек в нашей базе данных имеют имя« Джон »? Соответствующий запрос будет:
SELECT COUNT(p.first_name) FROM persons p WHERE p.first_name = 'John';
Надеюсь, вы получите следующий результат:
При использовании функций набора мы часто хотим включить другую информацию помимо вычисляемого значения. Например, предположим, что мы хотели также указать фамилию человека. Заманчиво попробовать следующий запрос:
SELECT COUNT(p.first_name), p.last_name FROM persons p WHERE p.first_name = 'John';
Конечно, этот запрос не удастся, потому что нет смысла включать другие столбцы.
Концепция # 4: Группировка по позволяет использовать функции набора с именами столбцов / данными
Давайте вернемся к вопросу в последнем разделе - мы хотим подсчитать количество людей, у которых есть имя «Джон», но мы также хотим включить информацию о фамилиях этих людей. Что на самом деле может включать имена столбцов в список выбора, если мы используем предложение Group By.
Мы можем думать о Group By как о том, что просто направляет механизм SQL на запрос к подмножеству данных, а затем объединяет их все вместе. Давайте посмотрим на пример запроса:
SELECT COUNT(p.first_name), p.last_name FROM persons p WHERE p.first_name = 'John' GROUP BY p.last_name;
Во-первых, давайте сосредоточимся на предложении FROM, которое указывает, что мы работаем с нашей новой таблицей person (с таблицей с 8 людьми). Предложение WHERE указывает, что мы имеем дело только с теми строками, которые соответствуют этому условию, то есть только с 4 строками. Итак, снова у нас есть одна таблица, из которой мы читаем данные, выглядит так:
Теперь предложение SELECT вернет вычисленное значение (простой счетчик), а также фамилию. Однако это работает только потому, что в предложении GROUP BY мы говорим, что хотим выполнить запрос для каждой фамилии. Другими словами, возьмите каждую фамилию, запустите запрос, а затем предоставьте мне результаты вместе. Ожидаемые результаты:
Концепция # 5: иметь возможность сгруппировать как место для выбора
Допустим, мы хотим отфильтровать приведенный выше набор результатов, чтобы он включал только те строки, в которых счетчик равен 2 или больше.
Опять же, возникает соблазн попробовать использовать предложение WHERE для фильтрации, поскольку это то, что мы сделали. К сожалению, если мы внимательно прочитаем концепцию №4, мы увидим, что действие предложения WHERE происходит перед предложением GROUP BY. Итак, мы не можем использовать это:
SELECT COUNT(p.first_name) as NumCount, p.last_name FROM persons p WHERE p.first_name = 'John' AND NumCount >= 2 GROUP BY p.last_name;
Предложение WHERE работает с отдельной таблицей, созданной в предложении FROM:
В этой таблице еще нет столбца NumCount.
Однако после действия предложения GROUP BY у нас есть следующая таблица. (Обратите внимание, что мы присвоили счетчику псевдоним NumCount).
Чтобы отфильтровать эту таблицу, мы используем предложение HAVING:
SELECT COUNT(p.first_name) as NumCount, p.last_name FROM persons p WHERE p.first_name = 'John' GROUP BY p.last_name HAVING NumCount >= 2;
Концепция # 6: тип данных хранится в столбце
Мой инстинкт подсказывает мне, что большинство из вас уже усвоили, что тип данных, с которыми мы имеем дело, хранится в столбце.
Веб-разработчики сталкиваются с этим фактом на раннем этапе - даже когда мы имеем дело с ORM, при создании модели на основе данных мы должны указать, какой тип данных будет иметь каждый столбец при миграции.
Хотя я «знал» этот факт, я не усвоил его. Итак, когда я увидел этот запрос:
SELECT p.first_name, p.last_name, p.salary FROM persons p WHERE p.start_date > '1/1/2016';
Я был смущен тем, как база данных узнала, что 01.01.2016 было датой, а не строкой. (Строки можно сравнить с оператором "больше"). Конечно, мне потребовалось всего 30 секунд, чтобы понять, что тип данных хранится в столбце - база данных знает, что p.start_date - это дата.
Резюме
Базы данных невероятно мощные, и есть еще много функций и концепций, о которых нам нужно знать, чтобы по-настоящему понимать базы данных. Тем не менее, эти 6 концепций - это то, что я хотел бы знать на раннем этапе, когда я впервые начал запрашивать базы данных.
