Unlimited Plugins, WordPress themes, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Code
  2. Databases
Code

SQL для начинающих Часть 2

by
Length:LongLanguages:

Russian (Pусский) translation by Anna Goorikova (you can also view the original English article)

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

Что вам понадобится

Пожалуйста, обратитесь к разделу «Что вам понадобится» в первой статье здесь: SQL для начинающих (часть 1).

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

  1. Откройте консоль MySQL и войдите в систему.
  2. Если вы еще этого не сделали, создайте базу данных с именем «my_first_db» запросом CREATE.
  3. Переключитесь в базу данных с помощью оператора USE.

Индексы базы данных

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

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

Наиболее распространенными причинами индексирования столбцов базы данных являются:

  • Почти каждая таблица должна иметь индекс PRIMARY KEY, обычно как столбец «id».
  • Если ожидается, что столбец содержит уникальные значения, он должен иметь индекс UNIQUE.
  • Если вы часто будете выполнять поиск по столбцу (с использованием WHERE), он должен иметь регулярный индекс.
  • Если столбец используется для связи с другой таблицей, он должен иметь FOREIGN KEY, если это возможно, или иметь обычный индекс в противном случае.

PRIMARY KEY

Почти каждая таблица должна иметь PRIMARY KEY, в большинстве случаев как INT с опцией AUTO_INCREMET.

Если вы вспомнили первую статью, мы создали поле user_id в таблице users, и это был PRIMARY KEY. Таким образом, в веб-приложении мы можем ссылаться на всех пользователей по их номерам идентификаторов.

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

Давайте посмотрим пример запроса, создав таблицу для списка штатов США:

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

UNIQUE

Поскольку мы ожидаем, что поле name будет уникальным значением, мы должны немного изменить предыдущий пример запроса:

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

Теперь индекс называется «state_name» вместо «name».

INDEX

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

Я просто добавил столбец join_year и проиндексировал его. Этот тип индекса не имеет ограничения уникальности.

Вы также можете назвать его KEY вместо INDEX.

Подробнее о производительности

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

Пример таблицы

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

Это будет список штатов США с их датами присоединения (дата, когда государство приняло Конституцию Соединенных Штатов или было принято в Союз) и их нынешнее население. Вы можете скопировать в консоль MySQL следующее:

GROUP BY: Группирование данных

GROUP BY группирует результирующие строки данных в группы. Вот пример:

Так что же произошло? У нас есть 50 строк в таблице, но 34 результата были возвращены этим запросом. Это связано с тем, что результаты были сгруппированы по столбцу «join_year». Другими словами, мы видим только одну строку для каждого отдельного значения join_year. Так как в некоторых штатах есть одно и то же join_year, мы получили менее 50 результатов.

Например, для 1787 года была только одна строка, но в этой группе есть 3 штата:

Итак, здесь есть три штата, но только имя Delaware появилось после запроса GROUP BY. Фактически, это мог быть любой из трех штатов, и мы не можем полагаться на эту часть данных. Тогда в чем смысл использования предложения GROUP BY?

Это было бы бесполезно, если бы не использовать функцию агрегат, такую как COUNT(). Давайте посмотрим, что делают некоторые из этих функций и как они могут получить нам полезные данные.

COUNT(*): Подсчет строк

Это, пожалуй, наиболее часто используемая функция вместе с запросами GROUP BY. Он возвращает количество строк в каждой группе.

Например, мы можем использовать его для просмотра количества состояний для каждого join_year:

Группируем все

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

Количество всех строк в таблице:

Количество строк, удовлетворяющих WHERE:

MIN(), MAX() и AVG()

Эти функции возвращают минимальные, максимальные и средние значения:

GROUP_CONCAT()

Эта функция объединяет все значения внутри группы в одну строку с заданным разделителем.

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

Если изображение с измененным размером трудно читать, вот запрос:

SUM()

Это можно использовать для суммирования числовых значений.

IF() & CASE: управление потоком

Подобно другим языкам программирования, SQL поддерживает поддержку управления потоком.

IF()

Это функция, которая принимает три аргумента. Первый аргумент - это условие, второй аргумент используется, если условие истинно, а третий аргумент используется, если условие ложно.

Вот практический пример, где мы используем его с помощью функции SUM():

Первый вызов SUM() подсчитывает количество больших штатов (население более 5 миллионов), а второе - количество малых штатов. Вызов IF() внутри этих вызовов SUM() возвращает 1 или 0 на основе условия.

Вот результат:

CASE

Это работает аналогично операторам switch-case, которые могут быть знакомы из программирования.

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

Как вы можете видеть, мы можем фактически сгруппировать по значениям, возвращаемым из оператора CASE. Вот что происходит:

HAVING: Условия на скрытых полях

Конструкция HAVING позволяет применять условия к «скрытым» полям, таким как возвращаемые результаты агрегатных функций. Поэтому она обычно используется вместе с GROUP BY.

Например, давайте посмотрим на запрос, который мы использовали для подсчета числа штатов по году:

В результате 34 строки.

Однако предположим, что нас интересуют только строки с числом выше 1. Мы не можем использовать WHERE для этого:

Здесь HAVING становится полезным:

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

Вложенные запросы

Можно получить результаты одного запроса и использовать его для другого запроса.

В этом примере мы получим штат с самым высоким населением:

Внутренний запрос возвращает наибольшее население среди всех штатов. И внешний запрос снова будет искать по таблице, используя это значение.

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

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

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

На этот раз в результатах есть две строки. Если бы мы использовали запрос ORDER BY ... LIMIT 1 здесь, мы бы не получили тот же результат.

IN()

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

Следующий запрос находит годы, когда несколько штатов присоединились к Союзу и возвращает список этих штатов:

Еще больше вложенных запросов

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

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

UNION: объединение данных

С помощью запроса UNION мы можем объединить результаты нескольких запросов SELECT.

Этот пример объединяет штаты, начинающиеся с буквы «N» и штаты с большими группами населения:

Обратите внимание, что Нью-Йорк является большим и его имя начинается с буквы «N». Но он появляется только один раз, потому что дублирующиеся строки удаляются из результатов автоматически.

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

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

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

INSERT Продолжение

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

INSERT ... ON DUPLICATE KEY UPDATE

Это почти как условное утверждение. Сначала запрос пытается выполнить заданный INSERT, и если он терпит неудачу из-за двойного значения для PRIMARY KEY или UNIQUE KEY, вместо этого он выполняет UPDATE.

Давайте сначала создадим тестовую таблицу.

Это таблица для хранения продуктов. Столбец «stock» - это количество продуктов, которые мы имеем на складе.

Теперь попробуйте вставить дублирующее значение и посмотреть, что произойдет.

Мы получили сообщение об ошибке, как ожидалось.

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

REPLACE INTO

Это работает точно так же, как INSERT с одним важным исключением. Если найдена повторяющаяся строка, он сначала удаляет ее, а затем выполняет INSERT, поэтому мы не получаем сообщений об ошибках.

Обратите внимание: поскольку это действительно совершенно новая строка, идентификатор увеличился.

INSERT IGNORE

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

Ошибок не было, и строки не обновлялись.

Типы данных

Каждый столбец таблицы должен иметь тип данных. До сих пор мы использовали типы INT, VARCHAR и DATE, но мы не говорили о них подробно. Также есть несколько других типов данных, которые мы должны изучить.

Сначала начнем с числовых типов данных. Мне нравится помещать их в две отдельные группы: целые числа и нецелые.

Целочисленные типы данных

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

MySQL поддерживает 5 типов целых чисел с различными размерами и диапазонами:

Не целочисленные числовые типы данных

Эти типы данных могут содержать десятичные числа: FLOAT, DOUBLE и DECIMAL.

FLOAT - 4 байта, DOUBLE - 8 байтов, и они работают аналогично. Однако DOUBLE имеет лучшую точность.

DECIMAL(M,N) имеет различный размер, основанный на уровне точности, который можно настроить. M - максимальное количество цифр, а N - число цифр справа от десятичной точки.

Например, DECIMAL(13,4) имеет максимум 9 целых цифр и 4 дробных цифры.

Строковые типы данных

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

CHAR(N) может содержать до N символов и имеет фиксированный размер. Например, CHAR(50) всегда будет содержать 50 символов в строке, независимо от размера строки в ней. Абсолютный максимум - 255 символов

VARCHAR(N) работает так же, но размер хранилища не фиксирован. N используется только для максимального размера. Если строка, длина которой меньше N символов, будет сохранена на жестком диске, это займет меньше места. Абсолютный максимальный размер - 65535 символов.

Вариации типа данных TEXT более подходят для длинных строк. TEXT имеет предел 65535 символов, MEDIUMTEXT - 16,7 миллиона символов, а LONGTEXT - 4,3 миллиарда символов. Обычно MySQL хранит их в разных местах на сервере, так что основное хранилище для таблицы остается относительно небольшим и быстрым.

Типы даты

DATE хранит даты и отображает их в этом формате «YYYY-MM-DD», но не содержит информацию о времени. Он имеет диапазон от 1001-01-01 до 9999-12-31.

DATETIME содержит как дату, так и время и отображается в этом формате: «YYYY-MM-DD HH:MM:SS». Он имеет диапазон «1000-01-01 00:00:00» до «9999-12-31 23:59:59». Он занимает 8 байт.

За несколькими исключениями TIMESTAMP работает как DATETIME. Он занимает всего 4 байта, а диапазон - '1970-01-01 00:00:01' UTC по '2038-01-19 03:14:07' UTC. Так, например, это может быть плохо для хранения дат рождения.

TIME хранит время, и YEAR сохраняет только год.

Другие

Существуют и другие типы данных, поддерживаемые MySQL. Вы можете увидеть их список здесь. Здесь вы также должны проверить размеры хранилища для каждого типа данных.

Заключение

Благодарю вас за чтение статьи. SQL является важным языком и инструментом в арсенале веб-разработчиков.

Пожалуйста, оставьте ваши комментарии и вопросы и хорошего дня!

  • Следуйте за нами в Твиттере или подпишитесь на Nettuts+ RSS Feed для учебных пособий по веб-разработке в Интернете. Готовы

Готовы ли вы перевести свои навыки на следующий уровень и начать получать прибыль от ваших скриптов и компонентов? Посмотрите наш рынок, CodeCanyon.

CodeCanyon

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.