php sql запросы с переменными

MySQL — Использование переменных в запросе

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

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

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

Одним из этих средств является уникальный, нехарактерный для прочих СУБД механизм работы с переменными внутри запроса SQL. Мы можем объявить переменную внутри запроса, менять ей значение и подставлять в SELECT для вывода. Причем порядок обработки строк в запросе и, как следствие, порядок присвоения значений переменным можно задать в пользовательской сортировке!

Предупреждение. В статье подразумевается, что обработка выражений в предложении SELECT осуществляется слева направо, однако официального подтверждения такого порядка обработки в документации MySQL нет. Это необходимо иметь в виду при смене версии сервера. Для гарантии последовательности вычисления можно использовать фиктивный оператор CASE или IF.

Аналог рекурсии

Рассмотрим простой пример, который генерирует последовательность Фибоначчи (в последовательности Фибоначчи каждый член равен сумме двух предыдущих, а первые 2 равны единице):

Данный запрос генерирует 18 чисел Фибоначчи, не считая первых двух:

Разберём теперь как оно работает.

В строчках 5) 6) генерируется 9 записей. Тут ничего необычного.

В строчке 7) мы объявляем две переменные @I, @J и присваиваем им 1.

В строке 3) происходит следующее: сначала переменной @I присваивается сумма двух переменных. Затем то же самое присваиваем переменной @J, причем с учетом того, что значение @I уже поменялось.

Другими словами, вычисления в SELECT выполняются слева направо – см. также замечание в начале статьи.

Причем изменение переменных осуществляется в каждой из наших 9 записей, т.е. при обработке каждой новой строки в переменных @I и @J будут содержаться значения, вычисленные при обработке предыдущей строки.

Чтобы решить эту же задачу средствами других СУБД, нам пришлось бы писать рекурсивный запрос!

Примечание:
Переменные нужно объявлять в отдельном подзапросе (строка 7), если бы мы объявили переменную в предложении SELECT, она, скорее всего, вычислилась бы только 1 раз (хотя конкретное поведение будет зависеть от версии сервера). Тип переменной определяется значением, которым она инициализирована. Этот тип может динамически меняться. Если переменной присвоить NULL, её типом будет BLOB.

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

Аналоги аналитических функций

Переменные также можно использовать для замены аналитических функций. Далее несколько примеров. Для простоты будем считать, что все поля NOT NULL, а сортировка и партиционирование (PARTITION BY) происходят по одному полю. Использование NULL значений и более сложных сортировок сделает примеры более громоздкими, но суть не поменяет.

Для примеров создадим таблицу TestTable:

где
group_id – идентификатор группы (аналог окна аналитической функции);
order_id – уникальное поле, по которому будет производиться сортировка;
value – некоторое числовое значение.

Заполним нашу таблицу тестовыми данными:

Примеры замены некоторых аналитических функций.

1) ROW_NUMBER() OVER(ORDER BY order_id)

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10

2) ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1

3) SUM(value) OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1

4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

Для LEAD всё то же самое, только нужно сменить сортировку на ORDER BY group_id, order_id DESC

Для функций COUNT, MIN, MAX всё несколько сложнее, поскольку, пока мы не проанализируем все строчки в группе(окне), мы не сможем узнать значение функции. MS SQL, например, для этих целей «спулит» окно (временно помещает строки окна в скрытую буферную таблицу для повторного к ним обращения), в MySQL такой возможности нет. Но мы можем для каждого окна вычислить значение функции в последней строке при заданной сортировке (т.е. после анализа всего окна), а затем, отсортировав строки в окне в обратном порядке, проставить вычисленное значение по всему окну.

Таким образом, нам понадобится две сортировки. Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по полям group_id ASC, order_id DESC, затем по полям group_id ASC, order_id ASC.

5) COUNT(*) OVER(PARTITION BY group_id)

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

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

Функции MAX и MIN вычисляются по аналогии. Приведу только пример для MAX:

6) MAX(value) OVER(PARTITION BY group_id)

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)

Интересная вещь, которая отсутствует в MS SQL Server, но её можно вычислить с подзапросом, взяв MAX от RANK. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:

group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

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

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

1) Классический способ с самомоединением

Что на 10000 записей в таблице TestTable выдаёт:

Duration / Fetch
16.084 sec / 0.016 sec

2) С использованием переменных:

Duration / Fetch
0.016 sec / 0.015 sec

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

Рассмотрим более подробно на примере такой задачи:

Вывести по 2 первые строки из таблицы TestTable для каждого значения group_id, отсортированных по order_id.

Вот как эта задача решалась бы в СУБД с поддержкой аналитических функций:

Однако оптимизатор MySQL ничего не знает о том, по каким правилам мы вычисляем поле RowNum. Ему придётся пронумеровать ВСЕ строки, и только потом отобрать нужные.

Теперь представьте, что у нас 1 миллион записей и 20 уникальных значений group_id. Т.е. чтобы выбрать 40 строк, MySQL будет вычислять значение RowNum для миллиона строк! Красивого решения этой задачи одним запросом в MySQL нет. Но можно сначала получить список уникальных значений group_id, например, так:

Затем средствами любого другого языка программирования сгенерировать запрос вида:

20 лёгких запросов отработают намного быстрее, чем вычисление RowNum для миллиона строк.

Источник

SQL запросы быстро. Часть 1

Введение

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

Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join’ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.

Практика

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

Кликнуть здесь

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

Структура sql-запросов

Общая структура запроса выглядит следующим образом:

Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS’ом.

SELECT, FROM

SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.

Выбрать все (обозначается как *) из таблицы Customers:

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:

WHERE

WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

GROUP BY

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

При использовании GROUP BY обязательно:

Группировка количества клиентов по стране и городу:

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:

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

HAVING

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

Фильтрация агрегированной таблицы с количеством клиентов по городам, в данном случае оставляем в выгрузке только те города, в которых не менее 5 клиентов:

В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:

Пример запроса, содержащего WHERE и HAVING. В данном запросе сначала фильтруется исходная таблица по пользователям, рассчитывается количество клиентов по городам и остаются только те города, где количество клиентов не менее 5:

ORDER BY

ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.

Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:

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

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

Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:

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

Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:

Нередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,

Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:

Другие типы JOIN’ов можно увидеть на замечательной картинке ниже:

В следующей части подробнее поговорим о типах JOIN’ов и вложенных запросах.

При возникновении вопросов/пожеланий, всегда прошу обращаться!

Источник

Работа с MySQL в PHP

PHP поддерживает работу с базой данных MySQL.
Специальные встроенные функции для работы с MySQL позволяют просто и эффективно работать с этой СУБД: выполнять любые запросы, читать и записывать данные, обрабатывать ошибки.
Сценарий, который подключается к БД, выполняет запрос и показывает результат, будет состоять всего из нескольких строк. Для работы с MySQL не надо ничего дополнительно устанавливать и настраивать; всё необходимое уже доступно вместе со стандартной поставкой PHP.

Что такое mysqli?

mysqli (MySQL Improved) — это расширение PHP, которое добавляет в язык полную поддержку баз данных MySQL. Это расширение поддерживает множество возможностей современных версий MySQL.

Как выглядит работа с базой данных

Типичный процесс работы с СУБД в PHP-сценарии состоит из нескольких шагов:

Функция mysqli connect: соединение с MySQL

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

Базовый синтаксис функции mysqli_connect() :

Проверка соединения

Первое, что нужно сделать после соединения с СУБД — это выполнить проверку, что оно было успешным.
Эта проверка нужна, чтобы исключить ошибку при подключении к БД. Неверные параметры подключения, неправильная настройка или высокая нагрузка заставит MySQL отвеграть новые подключения. Все эти ситуации приведут к невозможности соединения, поэтому программист должен проверить успешность подключения к серверу, прежде чем выполнять следующие действия.

Соединение с MySQL и проверка на ошибки:

Функция mysqli_connect_error() просто возвращает текстовое описание последней ошибки MySQL.

Установка кодировки

Первым делом после установки соединения крайне желательно явно задать кодировку, которая будет использоваться при обмене данными с MySQL. Если этого не сделать, то вместо записей со значениями, написанными кириллицей, можно получить последовательность из знаков вопроса: ‘. ’.
Вызови эту функцию сразу после успешной установки соединения: mysqli_set_charset($con, «utf8»);

Выполнение запросов

Установив соединение и определив кодировку мы готовы выполнить свои первые SQL-запросы. Вы уже умеете составлять корректные SQL команды и выполнять их через консольный или визуальный интерфейс MySQL-клиента.
Те же самые запросы можно отправлять без изменений и из PHP-сценария. Помогут в этом несколько встроенных функций языка.

Два вида запросов

Следует разделять все SQL-запросы на две группы:

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

Добавление записи

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

Выражение INSERT INTO используется для добавления новых записей в таблицу базы данных.

Функция insert id: как получить идентификатор добавленной записи

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

Чтение записей

В этом примере показано, как вывести все существующие города из таблицы cities:

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

Цикл while здесь используется для «прохода» по всем записям из полученного набора записей.
Значение поля каждой записи можно узнать просто обратившись по ключу этого ассоциативного массива.

Как получить сразу все записи в виде двумерного массива

Иногда бывает удобно после запроса на чтение не вызывать в цикле mysqli_fetch_array для извлечения очередной записи по порядку, а получить их сразу все одним вызовом. PHP так тоже умеет. Функция mysqli_fetch_all($res, MYSQLI_ASSOC) вернёт двумерный массив со всеми записями из результата последнего запроса.
Перепишем пример с показом существующих городов с её использованием:

Как узнать количество записей

Источник

Работа с базами данных SQL в PHP для новичков

Учебник PHP

Практика

Важное

Регулярки

Работа с htaccess

Файлы, папки

Сессии и куки

Работа с БД

Практика по работе с БД в PHP

Перед чтением см. новые уроки раздела «Важное», которые появились выше.

Практика

Движок PHP

Продвинутые БД

Аутентификация

Практика

ООП и MVC

Абстрактные классы и интерфейсы

Трейты

ООП Магия

Практика

Практика: классы как набор методов

Что такое база данных

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

База данных состоит из таблиц. Что такое таблица вы знаете из жизни: это строки и столбцы. А на пересечении строк и столбцов располагаются ячейки.

В базах данных столбцы часто называют полями.

PhpMyAdmin

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

Видео на PhpMyAdmin

Задачи на PhpMyAdmin

Давайте откроем PhpMyAdmin и приступим к изучению его интерфейса.

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

AUTO_INCREMENT

Обратите внимание на то, что мы создали поле id и поставили ему галочку AUTO_INCREMENT. Это очень важный шаг!

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

При этом если мы удалим строку с каким-то id (например 1), то такого id больше никогда не будет.

Зачем нужно поле id?

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

Типы переменных

В SQL довольно много типов переменных, но чаще всего приходится пользоваться следующими:

Как работать с mySQL через PHP

Работа с БД из PHP осуществляется всего лишь с помощью трех функций:

Далее подробнее про каждую из функций.

Устанавливаем соединение с БД

Данный материал есть также в виде видео: https://youtu.be/J8GFuyA_k_8

Прежде, чем начать работать с базой данных из PHP, нужно установить соединение с сервером, на котором эта база находится.

Делается это с помощью функции PHP mysql_connect, которая принимает 3 параметра: имя хоста (сервера), имя пользователя, под которым мы работаем с базой и пароль для этого пользователя.

Давайте установим соединение с базой данных:

Посылаем запросы к базе данных

Это относиться к командам SELECT, UPDATE, FROM, DELETE, WHERE и другим такого рода.

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

Отлавливаем ошибки базы данных

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

Чтобы вывести ошибки, следует пользоваться конструкцией or die ( mysqli_error($link) ), которую необходимо добавлять к каждому запросу к БД.

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

Проблемы с кодировками

Чтобы не было проблем с кодировками следует придерживаться простых правил:

Начнем практиковаться

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

Итак, создайте свою первую базу данных с помощью PhpMyAdmin.

Создайте в этой базе новую таблицу.

Назовите ее «workers» (англ. работники).

В ней создайте 4 столбца (столбцы по другому называются поля):

Ее заполните тестовыми данными, как показано в таблице ниже (этот шаг обязателен, так как дальше все задачи будут по этой таблице):

Тестируем работоспособность

Просто скопируйте этот код и запустите его у себя:

В таком случае проверьте все еще раз, уберите последовательно все ошибки PHP, если таковые есть.

Как достать результат

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

Однако лежит он не в той форме, которая нам нужна в PHP, а в той форме, в которой его прислала нам база.

Достать результат в нормальном виде (в массиве) можно с помощью следующего кода:

Как работает последняя строка?

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

В цикле for мы считываем построчно результат из базы.

А результат из БД будет лежать в нормальном виде в массиве $data.

Источник

SQL-запрос на PHP

Так как ORM слишком тяжеловесны для моих нужд, то обычно я использовал DbSimple. Однако после знакомства с Twig, которые компилирует шаблоны в php код периодически возникала идея написать что-то аналогичное для работы с БД. И вот я это сделал. На картинке представлен запрос на PHP, который после компиляции генерирует код для создания и выполнения SQL запроса.

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

Не так давно наткнулся на библиотеку по разбору PHP кода на лексемы PHP-Parser. По работе я пишу код на языке ABAP, в котором команды по работе с БД встроены в сам язык, поэтому возникла идея «А что если сделать что-то подобное для PHP»?

Схема реализации достаточно простая и очевидная: при автозагрузке класса проверяем его наличие в директории компилированных классов. Если нет скомпилированного класса, то берем исходный файл класса, заменяем в нем все спец-команды и записываем готовый класс в директорию скомпилированных классов. Весь разбор делает библиотека PHP-Parser. Чтобы компилятор мог понять, что это именно нужная ему команда, оборачиваем все в пространство имен ML (Macro Language). К примеру в коде пишем так:

и получаем на выходе:

При этом мы можем использовать в запросе непосредственно переменные PHP (которые подставляются в запрос с защитой от SQL-инъекций в PHP и MySQL) и функцию условного формирования запроса _if. В частности такой код:

скомпилируется в такой код:

P.S. Хотя прежде чем делать ORM, добавлю команду JOIN + команды UPDATE и DELETE.

Для тех, кому интересно, небольшой тестовый полигон для компиляции кода. Если найдете какую-то ошибку, пишите в комментариях. Пока это версия 0.1 alpha так что ошибки весьма вероятны.

Источник