MySQL примеры примеры запросов mysql


Содержание

MySQL примеры: примеры запросов mysql

Подзапросы представляют выражения SELECT, которые встроены в другие запросы SQL. Рассмотрим простейший пример применения подзапросов.

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

Таблица Orders содержит данные о купленным товарам из таблицы Products.

Добавим в таблицы некоторые данные:

При добавлении данных в таблицу Orders как раз используются подзапросы. Например, первый заказ был сделан на товар Galaxy S8. Соответственно в таблицу Orders нам надо сохранить информацию о заказе, где поле ProductId указывает на Id товара Galaxy S8, поле Price — на его цену. Но на момент написания запроса нам может быть неизвестен ни Id покупателя, ни Id товара, ни цена товара. В этом случае можно выполнить подзапрос в виде

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

В примере выше подзапросы выполнялись к другой таблице, но могут выполняться и к той же, для которой вызывается основной запрос. Например, найдем товары из таблицы Products, которые имеют минимальную цену:

Или найдем товары, цена которых выше средней:

Коррелирующие и некоррелирующие подзапросы

Подзапросы бывают коррелирующими и некоррелирующими. В примерах выше команды SELECT фактически выполняли один подзапрос для всех строк, извлекаемых командой. Например, подзапрос возвращает минимальную или среднюю цену, которая не изменится, сколько бы мы строк не выбирали в основном запросе. То есть результат подзапроса не зависел от строк, которые выбираются в основном запросе. И такой подзапрос выполняется один раз для всего внешнего запроса.

Но также можно использовать и коррелирующие подзапросы (correlated subquery), результаты которых зависят от строк, которые выбираются в основном запросе.

Например, выберем все заказы из таблицы Orders, добавив к ним информацию о товаре:

В данном случае для каждой строки из таблицы Orders будет выполняться подзапрос, результат которого зависит от столбца ProductId. И каждый подзапрос может возвращать различные данные.

Коррелирующий подзапрос может выполняться и для той же таблицы, к которой выполняется основной запрос. Например, выберем из таблицы Products те товары, стоимость которых выше средней цены товаров для данного производителя:

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

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

Чтобы избежать двойственности при фильтрации в подзапросе при сравнении производителей ( SubProds.Manufacturer=Prods.Manufacturer ) для внешней выборки установлен псевдоним Prods, а для выборки из подзапросов определен псевдоним SubProds.

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

MySQL примеры: примеры запросов mysql

Освойте бесплатно наиболее простой, быстрый и гибкий способ создавать адаптивные веб-сайты.

Дизайн лендинга

Создавайте дизайн любых сайтов — для себя и на заказ!

Популярное

  • Главная
  • ->
  • Материалы
  • ->
  • БД MySQL (сложные запросы, агрегатные функции, оценка производительности)

Reg.ru: домены и хостинг

Крупнейший регистратор и хостинг-провайдер в России.

Более 2 миллионов доменных имен на обслуживании.

Продвижение, почта для домена, решения для бизнеса.

Более 700 тыс. клиентов по всему миру уже сделали свой выбор.

Бесплатный Курс «Практика HTML5 и CSS3»

Освойте бесплатно пошаговый видеокурс

по основам адаптивной верстки

на HTML5 и CSS3 с полного нуля.

Фреймворк Bootstrap: быстрая адаптивная вёрстка

Пошаговый видеокурс по основам адаптивной верстки в фреймворке Bootstrap.

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

Верстайте на заказ и получайте деньги.

Что нужно знать для создания PHP-сайтов?

Ответ здесь. Только самое важное и полезное для начинающего веб-разработчика.

Узнайте, как создавать качественные сайты на PHP всего за 2 часа и 27 минут!

Создайте свой сайт за 3 часа и 30 минут.

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

Вам останется лишь наполнить его нужной информацией и изменить дизайн (по желанию).

Изучите основы HTML и CSS менее чем за 4 часа.

После просмотра данного видеокурса Вы перестанете с ужасом смотреть на HTML-код и будете понимать, как он работает.

Вы сможете создать свои первые HTML-страницы и придать им нужный вид с помощью CSS.

Бесплатный курс «Сайт на WordPress»

Хотите освоить CMS WordPress?

Получите уроки по дизайну и верстке сайта на WordPress.

Научитесь работать с темами и нарезать макет.

Бесплатный видеокурс по рисованию дизайна сайта, его верстке и установке на CMS WordPress!

Хотите изучить JavaScript, но не знаете, как подступиться?

После прохождения видеокурса Вы освоите базовые моменты работы с JavaScript.

Развеются мифы о сложности работы с этим языком, и Вы будете готовы изучать JavaScript на более серьезном уровне.

*Наведите курсор мыши для приостановки прокрутки.

БД MySQL (сложные запросы, агрегатные функции, оценка производительности)

В этом уроке мы поговорим о следующих моментах, касающихся работы с БД MySQL: вы узнаете, как составлять сложные запросы, как использовать агрегатные функции, объединения таблиц и как оценивать производительность запросов.

Связи в БД

Связи в БД — это ассоциативное отношение между сущностями (таблицами). В первую очередь связи позволяют избегать избыточности данных.

Избыточность же — это переполнение таблиц повторяющимися данными.

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

Таблица User_docs подчинена таблице Users, поэтому в ней есть ссылка на таблицу Users (user_id_ref).

У одного пользователя может быть как один, так и много документов. Поэтому мы выносим документы в отдельную таблицу, чтобы не повторялись данные по самому пользователю. Связь таблиц User и User_docs — “один-ко-многим”.

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

Запрос из двух таблиц

Функциональность MySQL не ограничивается запросом вида SELECT * FROM table. Это самый простой запрос. Такого запроса достаточно, если весь необходимый набор данных содержится в одной таблице. Но мы учимся правильно проектировать БД, поэтому и запросы у нас будут несколько сложнее и функциональнее.

Предлагаю данный момент разобрать на примерах Интернет-каталога.

Допустим, у нас задача, реализация каталога продукции в сети Интернет. Что для этого нужно сделать? Для начала спроектируем базу данных. Для этого нужно определиться с основными сущностями будущей БД. Первая и основная сущность — это Продукт. Создадим таблицу Products:

В этой таблице мы будем хранить наши продукты. Как вы заметили, я заранее добавил в таблицу поле Group_id_ref. Это поле привязывает продукт к конкретной группе. Создадим таблицу групп товаров:

Кроме того, часто встречается ситуация, когда товары имеют дополнительные свойства, такие как Цвет, Размер и пр.

Добавим таблицу Colors:

И таблицу Sizes (Размеры):

Теперь мы можем хранить все наши данные по Продукту. Заполним таблицы тестовыми данными.

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

Добавим таблицы, связывающие товары с реквизитами:

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

Теперь наш тестовый продукт имеет два реквизита: Цвет и Размер.

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

— Record_id – уникальный идентификатор нашей таблицы. В прошлой статье я указывал на необходимость этого поля.

Product_id_ref – ссылка на продукт. Собственно “_ref” и указывает на то, что это ссылка — reference. Идентификатор товара в таблице Products (мы учимся связывать именно с помощью идентификаторов).

Value_id_ref – Ссылка на реквизиты товара.

Value_type – Тип реквизита. 1- цвет, 2- размер и пр., если у вас таковые будут.

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

Тут все просто. При помощи Group_id мы формируем ссылку на список товаров в группе. Формировать ссылку можно как в запросе, так и в скрипте, на котором написан ваш каталог.

Результат выборки выглядит так:

В каталоге на сайте такую выборку можно использовать в списке товаров. Product_id используем для формирования ссылки на конкретный товар.

Для конкретного товара запрос будет похожим, за исключением того, что мы укажем p.Product_ >

Немного поясню, что такое «р.» в данном запросе. Для СУБД запрос вида:

То есть всегда поле указывается с таблицей. В принципе, имя таблицы можно не писать, если поля ВО ВСЕХ(!) таблицах запроса именуются по-разному.

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

В этом случае p – это Products, а g – это Product_groups. Теперь в запросе нет необходимости писать имя таблицы целиком, достаточно описать только алиас.

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

Итак, для конкретного товара запрос будет таковым:

Теперь получим реквизиты товара. Список расцветок получаем запросом:

Подобным запросом получим и размеры.

Немного поясню запрос.

v.value_type = 1 — указываем тип реквизита. С типами нужно заранее определиться и, при добавлении товара, добавлять реквизит с соответствующим типом.

Запросы с JOIN

JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в раздел FROM операторов SELECT, UPDATE или DELETE. Используется при связке двух или более таблиц.

Такое объединение выдаст нам набор записей, в котором данные таблицы Colors присутствуют в таблице Product_values. То есть только те записи, которые удовлетворяют условию c.color_ >

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

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

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

Результат выборки следующий:

Как мы видим, у товара нет фотографии. NULL означает пусто.

Но, когда мы в скриптовом языке (PHP и пр.) будем выводить список, и в тег img попадет пустое значение, фото в браузере будет потеряно.

Модифицируем запрос для того, чтобы избежать этого:

IFNULL обрабатывает как раз значение NULL. Если значение пустое, можем подставить свое значение. В данном случае мы подставим «empty.jpg». Для корректного отображения на странице добавим на сайт изображение empty.jpg и теперь мы имеем красивый список.

Внимание! Старайтесь всегда обрабатывать значения NULL. Не стоит такого рода логику обрабатывать на клиентском приложении, запросами она обрабатывается значительно легче.

Теперь непосредственно про LEFT JOIN. Так называемое «левое объединение» выводит все данные основной таблицы и только те данные второй, которые удовлетворяют условию блока ON.

Есть также RIGHT и FULL JOIN. RIGHT, по сути, аналогичен LEFT, только запрос выведет все данные второй таблицы и те записи первой, которые удовлетворяют условию блока ON.

Можно всегда использовать LEFT, только менять местами таблицы.

FULL JOIN выведет все данные обеих таблиц, но практическую реализацию подобного запроса встретишь довольно редко.

Агрегатные функции

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

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

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

COUNT — выводит количество полей, которые выбрал запрос;
SUM — выводит арифметическую сумму всех выбранных значений данного поля;
MAX — выводит наибольшее из всех выбранных значений данного поля;
MIN — выводит наименьшее из всех выбранных значений данного поля;
AVG — выводит усреднение всех выбранных значений данного поля.

При написании запросов с агрегатными функциями, необходимо научиться правильным образом организовать группировку (GROUP BY).

Пример запроса с группировкой:

Запрос выведет нам список групп и количество товаров в каждой:

Остальные агрегатные функции работают аналогично, и запросы выглядят идентично:

Запрос выведет нам список групп и общую стоимость товаров в каждой.

Внимание! Агрегатные функции используются только в блоке SELECT. Если мы хотим добавить агрегатную функцию в блок WHERE, нужно использовать команду HAVING.

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

Оценка производительности запросов

Тут все настолько просто, насколько сложно. Для оценки производительности необходимо перед запросом добавить EXPLAIN EXTENDED.


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

Я преднамеренно убрал все индексы из запроса, чтобы план показал, что запрос неэффективен.

Значения полей possible_keys, key, key_len и ref не заполнены. Такой результат нас не устраивает. Поэтому добавим индексы на колонки Product_photos.product_id_ref и Products.product_id.

Внимание! Не стоит перегружать таблицу индексами. От того, что таблица будет вся проиндексирована, запрос не будет выполняться быстрее. К тому же размер индекса будет сопоставим с размерами таблицы.

Итог

В данной статье мы изучили:

— Связи в БД
— Запросы из двух и более таблиц
— Запросы с JOIN
— Агрегатные функции
— Оценку производительности запросов

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

Материал подготовил Владимир Миняйлов специально для сайта CodeHarmony.ru

Исходники:

P.S. Хотите углубить свои знания и навыки? Присмотритесь к премиум-урокам по различным аспектам сайтостроения, включая SQL и работу с БД, а также к бесплатному курсу по созданию своей CMS-системы на PHP с нуля.

Понравился материал и хотите отблагодарить?
Просто поделитесь с друзьями и коллегами!

MySQL примеры: примеры запросов mysql

В данном материале приведены примеры mysql запросов. Всего помнить невозможно, часто бывает, забываешь как подать тот или иной mysql запрос. Надеюсь mysql примеры помогут вам при создании приложений и практичны в применении. Мы рассмотрим такие примеры, как insert, select, update, delete и другие.

Пример создания таблицы в MySQL

Вот пример создания таблицы в БД MySQL. Данный запрос создаст таблицу example, в которой id — первичный ключ (ставиться автоматически), name– поле с допустимым максимальным значением 50 символов, age– максимально принимает 2 цифирных символа, info– может принимать неограниченное количество любых символов, date– будет вставлять сегодняшнюю дату.

Пример MySQL select

Выберет запись таблицы name_table, в которой idравен 16, при этом будут выбраны все поля (символ * выбирает все поля).

Тот же пример, только вернется значения полей id, field1, field2.

Пример более сложных конструкций select

Выберет все поля из nam_table где значение field1 похожее на value1 и значение field2 похожее на value2, отсортирует за датой, выведет 5 записей начиная с 3 записи (limit 3,5). Если DESC заменить на ASC, отсортирует записи в обратном порядке.

Пример MySQL insert

Вставляет в таблицу name_table в поля site и description данные значения. Помните если поле помечено как auto_increment, то оно будет заполнятся автоматически (например поле id).

Пример MySQL update

Изменяет значение поля field на value в таблице name_table где id равен 12.

Пример MySQL delete

Удаляет запись из name_table где id равен 14.

Количество записей в таблице (count)

Даст нам количество записей в name_table. Id — поле в таблице.

Даст количество пользователей, в которых возраст 12.

Данный пример даст нам количество пользователей с возрастом 12,15,18.

Пример выбора максимального или минимального значения

В первом примере, mysql запрос выбирает максимальное значение id из таблицы name_table. Во втором примере минимальное значение.

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

MySQL запросы для начинающих — часть первая

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

Подключение к серверу MySQL и выбор базы данных

Для работы с базой данных естественно нужно сначала к ней подключиться. В большинстве случаев для этого используют либо работу из консоли, подключившись к серверу хостинга по протоколу SSH, либо скрипт PHP (однако все примеры SQL-запросов, приводимые в этой статье, можно использовать и в phpMyAdmin).

Подключение из консоли по протоколу SSH:

В данном примере root — это имя пользователя (пользователя с правами на запуск mysql, многие обычно используют суперадмина), а после выполнения вышеприведенной команды вам будет предложено ввести пароль.

Подключение из скрипта PHP:

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

Пример для консоли SSH:

— получаем список всех баз данных:

— выбираем для работы базу данных example_db :

— если понадобится, то можно просмотреть структуру любой таблицы из базы данных example_db , которую вы выбрали для работы, к примеру таблицы clients :

Пример скрипта PHP:

Кстати, используя скрипт PHP также можно получать список всех баз данных на сервере, к которому вы подключаетесь:

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

SELECT — базовые запросы

Этот запрос возвращает все записи, поля и их значения из таблицы clients .

В следующем примере выбираются не все данные из таблицы clients , а только те, которые хранятся в столбцах name (имя) и address (адрес) клиентов:

Команда SELECT и виртуальная таблица DUAL

С помощью SELECT можно обращаться к виртуальной таблице DUAL (эта таблица реально НЕ существует в базе данных) и выполнять кое-какие действия, которые не имеют отношения к реальным таблицам. Например вы можете с помощью SQL-запроса произвести какие-то арифметические вычисления:

Скрипт PHP, который выполнит этот запрос и выведет на экран «500» как результат выражения (2+3)*100 :

Этот запрос вернет точное время и дату на сервере MySQL:

Приведенные в этом примере запросы можно выполнять и без указания «FROM DUAL»:

но все же, во избежание проблем и лучшего восприятия, не рекомендую использовать такой «сокращенный» вариант!

WHERE — ограничение по выбору данных

Выбираем всех клиентов, возраст которых больше 30, не равно 30, меньше 30, больше или равно 30 лет:

WHERE — ограничение по совпадению строк

Этот запрос похож на предыдущие запросы, где сравнивались числа, но здесь уже идет сравнение по строкам, и он выбирает всех клиентов, например проживающих в городе «Minsk».

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

Кроме того, в запросах со сравнением по строкам можно использовать и частичное совпадение строк (для подстановки «любых» символов используется знак процента % ). Например здесь будут выбраны все записи таблицы, в которых значение в колонке city будут начинаться с «Novo» (начало названий городов «Novopolotsk», «Novosibirsk» и т.д.):

Следующий запрос выберет все записи из таблицы clients , в которых имена клиентов заканчиваются на «лина» (например «Галина», «Ангелина»):

Ну и наконец выбор всех клиентов, проживающих в городах, названия которых содержат в любом месте подстроку «lug» (например «Kaluga», «Lugansk»)

WHERE — комбинирование условий с применением OR и AND

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

В данном примере из таблицы clients будут выбраны все клиенты, возраст которых не меньше 25 лет и при этом обязательно проживающие в городе Москва:

Здесь же, в отличие от предыдущего примера, из таблицы clients выбираются строки, в которых есть клиенты, проживающие в одном из двух городов, либо в Moscow , либо в Minsk :


Чтобы не делать из темы «MySQL запросы для начинающих» настоящую длинную «простынь», на этом первая часть обзора завершается.
Другие статьи из этой серии:
Вторая часть
Третья часть
Четвертая часть

Форум Pawn-Wiki.Ru — Воплоти мечту в реальность!: Запросы MySQL — Форум Pawn-Wiki.Ru — Воплоти мечту в реальность!

  • Группа: Заблокированные
  • Сообщений: 644
  • Регистрация: 28 Ноябрь 16

Сегодня разберем запросы MySQL. Почему-то все думают что БД способна только на регистрацию, но это не так.

И так, что же они делают

  • Первый запрос в БД говорит ей о том, какие поля нужно выгрузить из базы и какое поле должно быть обязательным. Что же делает LIMIT 1? Ну как понятно из названия — ограничивает загрузку. Почему это важно? Представим — у вас есть в базе данных 500к аккаунтов, вот зашел новый игрок, нужно найти его аккаунт. Что делает БД когда ей поступает запрос без LIMIT 1? Она находит аккаунт игрока и продолжает поиск. Почему? Ну, а вдруг есть еще запись где обязательное поле совпадает. Это создает дополнительную нагрузку на базу.

Второй запрос обновляет существующую запись. После SET указываются поля которые нужно обновить, потом обязательное поле которое должно совпадать. Потом опять LIMIT 1. Собственно зачем он нужен я объяснил выше.

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

    Что дает данный запрос?

    Он возвращает последние 3 записей и ставит их в обратном порядке. То есть у вас последние записи такие:

    (какой кошмар админ mmsg сливает сообщения пользователей)

    Вернул бы вам их в таком порядке:

    NzVyOFQvS3NST243RFROdnl0YTNoZGR5MGJWVXpETm9Xams0VVc0SlVHU2NFL2phdzZHenJaNmxZa0hFakMrMWxnWjc4OGhvMXFiZEx2UXZ2aUczTm5QdngzWEU0TUxxakc1N2ZKanNRRE5ZRVA1TlY1Z2lSb3NBOHlqLzZ6K2lkVUxjaG8wL3RzK2doRDR >
    Запрос же выше разворачивает их наоборот:

    • NzVyOFQvS3NST243RFROdnl0YTNoZGR5MGJWVXpETm9Xams0VVc0SlVHU2NFL2phdzZHenJaNmxZa0hFakMrMWxnWjc4OGhvMXFiZEx2UXZ2aUczTm5QdngzWEU0TUxxakc1N2ZKanNRRE5ZRVA1TlY1Z2lSb3NBOHlqLzZ6K2lkVUxjaG8wL3RzK2doRDR >
    • UXZ6UW55TXVlK3pwQ0FKTVJxVjE3V01KU3BGbnpkOTZ2WTViU3VNZ3lDalNLNWxaZWU3bysyL1diaTdOSVlMcmpraE5ZbWNza0drS2FjZ0pIaWxPdG5BYVNNNFE1aUpaNWlkN3JuSy9wOVV0QnRCUngvVFFlOVVrcTIvZEVRVHMyazZ4WXFVK1ErTlR1ajR3cFA2dFc3RVlMMERNUWdMQ2xjNlZvc0Q1TjFtZmNxd1Fobzhyb3cyRFI2S0VONi9SOjqwAD2wbJmJGLQ1Rt8Y/ 5rO

      ORDER BY — устанавливает по чему нужно сортировать. В данном случае по id, вы же можете поставить например время.

  • DESC/ASC — устанавливают с начала или с конца
  • Теперь пример запроса с простым поиском:

    Этот запрос возвращает похожие записи. В concat вы перечисляете поля в которых нужно производить поиск (через запятую перечисляете). После LIKE вы указываете что нужно искать между знаками %. Что удобно делать с помощью такого? Ну например делать команду поиска аккаунта игрока только по части ника, а не полностью. Например ник игрока Vasya_Pupkin. При вводе Vasya вам уже выдаст поле с его аккаунтом.

    Так же в MySQL есть всем привычные операторы которые почему-то многие не используют:

    В MySQL так же возможно использование AND — и и OR — или. Работают они так же как в ЯС Pawn && (AND) и || (OR)

    Например вам обновить поле где совпадают оба поля.

    Или если совпадает только одно из полей

    Теперь ко всем игрокам у который ник или пароль 123456 прибавится 20 к деньгам.

    Так же можно использовать скобки, например — (true) or (true).

    Теперь если одно из скобок = true (правда), то прибавится 20 к деньгам. Можно создавать так сколько угодно скобок.

    Если вам потребовалось удалить запись, можно воспользоваться оператором DELETE.

    Этот запрос удалить все записи с именем s2s.k. После DELETE так же можно указать поля как в SELECT

    Еще можно отправлять несколько запросов сразу через ; .

    Тема будет дополнятся. Пишите так же известные вам запросы.

    Запросы в MySQL

    Базы данных являются ключевым компонентом многих веб-сайтов и приложений и лежат в основе хранения и обмена данными в Интернете. Одним из наиболее важных аспектов управления базой данных является процедура извлечения данных из БД, будь то разовое обращение или часть процесса, закодированного в приложении. Существует несколько способов извлечения информации из базы данных, но один из наиболее часто используемых методов – это отправка запросов через командную строку.

    В системах управления реляционными базами данных запрос – это любая команда, используемая для извлечения данных из таблицы. В языке структурированных запросов (SQL) запросы почти всегда выполняются с помощью оператора SELECT.

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

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

    Требования

    В общем, команды и понятия, представленные в этом мануале, могут использоваться в любой операционной системе на базе Linux и в любом программном обеспечении SQL. Однако мануал был написан специально для сервера Ubuntu 18.04 и MySQL. Для работы вам понадобится:

    • Сервер Ubuntu 18.04 с пользователем sudo. Начальная настройка сервера описана здесь.
    • Предварительно установленная система MySQL. Инструкции по установке можно найти в мануале Установка MySQL в Ubuntu 18.04.

    Создание тестовой базы данных

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

    В качестве БД, которую мы будем использовать в этом руководстве, мы предлагаем следующий сценарий.

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

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

    Для начала откройте командную строку MySQL как пользователь root:

    Примечание: Если вы полностью выполнили мануал по установке MySQL в Ubuntu 18.04, вероятно, вы настроили парольную аутентификацию для пользователя root. В этом случае вы можете подключиться к командной строке с помощью следующей команды:

    CREATE DATABASE `birthdays`;

    Выберите эту БД:

    Затем создайте в этой базе данных две таблицы. Первую таблицу можно использовать, чтобы отслеживать результаты в боулинге. Следующая команда создаст таблицу под названием tourneys. В ней будут столбцы с именами подруг (name), количество выигранных ими турниров (wins), их лучшие результаты за все время (best) и размер ботинок для боулинга, которые они носят (size):


    CREATE TABLE tourneys (
    name varchar(30),
    wins real,
    best real,
    size real
    );

    Запустив эту команду и указав заголовки столбцов, вы увидите такой вывод:

    Query OK, 0 rows affected (0.00 sec)

    Теперь добавьте в таблицу какие-нибудь данные:

    INSERT INTO tourneys (name, wins, best, size)
    VALUES (‘Dolly’, ‘7’, ‘245’, ‘8.5’),
    (‘Etta’, ‘4’, ‘283’, ‘9’),
    (‘Irma’, ‘9’, ‘266’, ‘7’),
    (‘Barbara’, ‘2’, ‘197’, ‘7.5’),
    (‘Gladys’, ’13’, ‘273’, ‘8’);

    Query OK, 5 rows affected (0.01 sec)
    Records: 5 Duplicates: 0 Warnings: 0

    После этого создайте еще одну таблицу в той же базе данных. Ее можно использовать для хранения информации о любимых блюдах подруг на день рождения. Следующая команда создает таблицу dinners, где будут столбцы с именами подруг, их датой рождения (birthdate), их любимым блюдом (entree), гарниром (side) и десертом (dessert):

    CREATE TABLE dinners (
    name varchar(30),
    birthdate date,
    ntree varchar(30),
    side varchar(30),
    dessert varchar(30)
    );

    После запуска команда выведет:

    Query OK, 0 rows affected (0.01 sec)

    Теперь заполните таблицу данными:

    INSERT INTO dinners (name, birthdate, entree, side, dessert)
    VALUES (‘Dolly’, ‘1946-01-19’, ‘steak’, ‘salad’, ‘cake’),
    (‘Etta’, ‘1938-01-25’, ‘chicken’, ‘fries’, ‘ice cream’),
    (‘Irma’, ‘1941-02-18’, ‘tofu’, ‘fries’, ‘cake’),
    (‘Barbara’, ‘1948-12-25’, ‘tofu’, ‘salad’, ‘ice cream’),
    (‘Gladys’, ‘1944-05-28’, ‘steak’, ‘fries’, ‘ice cream’);
    Query OK, 5 rows affected (0.00 sec)
    Records: 5 Duplicates: 0 Warnings: 0

    Теперь у вас есть данные, на которых можно потренироваться.

    Оператор SELECT

    Как упоминалось в начале статьи, SQL-запросы почти всегда начинаются с оператора SELECT. SELECT в запросах указывает, какие столбцы из таблицы должны нужно вернуть в наборе результатов. Запросы также почти всегда включают оператор FROM, который используется для указания таблицы, к которой нужно обратиться.

    В общем SQL-запросы следуют такому синтаксису:

    SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;

    Например, чтобы извлечь столбец name из таблицы dinners, нужен такой запрос:

    SELECT name FROM dinners;
    +———+
    | name |
    +———+
    | Dolly |
    | Etta |
    | Irma |
    | Barbara |
    | Gladys |
    +———+
    5 rows in set (0.00 sec)

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

    SELECT name, birthdate FROM dinners;
    +———+————+
    | name | birthdate |
    +———+————+
    | Dolly | 1946-01-19 |
    | Etta | 1938-01-25 |
    | Irma | 1941-02-18 |
    | Barbara | 1948-12-25 |
    | Gladys | 1944-05-28 |
    +———+————+
    5 rows in set (0.00 sec)

    Вместо того чтобы называть конкретный столбец или набор столбцов, вы можете использовать оператор SELECT со звездочкой (*) – она служит заполнителем, представляющим все столбцы в таблице. Следующая команда отобразит все столбцы таблицы tourneys:

    SELECT * FROM tourneys;
    +———+——+——+——+
    | name | wins | best | size |
    +———+——+——+——+
    | Dolly | 7 | 245 | 8.5 |
    | Etta | 4 | 283 | 9 |
    | Irma | 9 | 266 | 7 |
    | Barbara | 2 | 197 | 7.5 |
    | Gladys | 13 | 273 | 8 |
    +———+——+——+——+
    5 rows in set (0.00 sec)

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

    . . . WHERE column_name comparison_operator value

    Оператор сравнения в выражении WHERE определяет способ сравнения указанного столбца со значением. Вот некоторые распространенные операторы сравнения SQL:

    Оператор Действие
    = Равно
    != Не равно
    Больше, чем
    = Больше или равно
    BETWEEN проверяет, находится ли значение в заданном диапазоне
    IN проверяет, содержится ли значение строки в наборе указанных значений
    EXISTS проверяет, существуют ли строки при заданных условиях
    LIKE проверяет, соответствует ли значение указанной строке
    IS NULL Проверяет значения NULL
    IS NOT NULL Проверяет все значения, кроме NULL

    Например, если вы хотите узнать размер обуви Ирмы, вы можете использовать следующий запрос:

    SELECT size FROM tourneys WHERE name = ‘Irma’;
    +——+
    | size |
    +——+
    | 7 |
    +——+
    1 row in set (0.00 sec)

    SQL позволяет использовать подстановочных знаков, и это особенно удобно при работе с выражениями WHERE. Знак процента (%) представляют ноль или более неизвестных символов, а подчеркивания (_) представляют один неизвестный символ. Они полезны, если вы пытаетесь найти конкретную запись в таблице, но не знаете точно, что это за запись. Чтобы проиллюстрировать это, предположим, что вы забыли любимое блюдо нескольких своих подруг, но вы уверены, что это блюдо начинается на t. Вы можете найти его название с помощью запроса:

    SELECT entree FROM dinners WHERE entree LIKE ‘t%’;
    +———+
    | entree |
    +———+
    | tofu |
    | tofu |
    +———+
    2 rows in set (0.00 sec)

    Исходя из вышеприведенного вывода, это tofu.

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

    SELECT name AS n, birthdate AS b, dessert AS d FROM dinners;
    +———+————+————+
    | n | b | d |
    +———+————+————+
    | Dolly | 1946-01-19 | cake |
    | Etta | 1938-01-25 | ice cream |
    | Irma | 1941-02-18 | cake |
    | Barbara | 1948-12-25 | ice cream |
    | Gladys | 1944-05-28 | ice cream |
    +———+————+————+
    5 rows in set (0.00 sec)

    Как видите, теперь SQL отображает столбец name как n, столбец birthdate как b и dessert как d.

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

    Агрегатные функции

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

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

    SELECT COUNT(entree) FROM dinners WHERE entree = ‘tofu’;
    +—————+
    | COUNT(entree) |
    +—————+
    | 2 |
    +—————+
    1 row in set (0.00 sec)

    Функция AVG возвращает среднее значение столбца. Например, вы можете найти средний лучший результат в турнире с помощью этого запроса:

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

    Обратите внимание, функции AVG и SUM работают правильно только с числовыми данными. Если вы попытаетесь использовать их для нечисловых данных, это приведет к ошибке или к 0, в зависимости от того, какую СУБД вы используете.

    SELECT SUM(entree) FROM dinners;
    +————-+
    | SUM(entree) |
    +————-+
    | 0 |
    +————-+
    1 row in set, 5 warnings (0.00 sec)

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

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

    В отличие от SUM и AVG, функции MIN и MAX могут использоваться как для числовых, так и для буквенных типов данных. При запуске в столбце со строковыми значениями функция MIN отображает первое значение в алфавитном порядке:

    SELECT MIN(name) FROM dinners;
    +————+
    | MIN(name) |
    +————+
    | Barbara |
    +————+
    1 row in set (0.00 sec)

    Аналогично MAX покажет последнее значение в алфавитном порядке:

    Агрегатные функции широко применяются в СУБД. Они особенно полезны в выражениях GROUP BY, которые мы рассмотрим в следующем разделе вместе с несколькими другими операторами сортировки наборов результатов.

    Управление выводом запроса

    В дополнение к FROM и WHERE есть несколько других операторов, которые используются для управления результатами запроса SELECT. В этом разделе мы объясним некоторые из наиболее часто используемых операторов и рассмотрим их на примерах.

    Одним из наиболее часто используемых операторов, кроме FROM и WHERE, является GROUP BY. Обычно он используется, когда вы выполняете агрегатную функцию для одного столбца, но в отношении сопоставления значений в другом.

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

    SELECT COUNT(name), entree FROM dinners GROUP BY entree;
    +————-+———+
    | COUNT(name) | entree |
    +————-+———+
    | 1 | chicken |
    | 2 | steak |
    | 2 | tofu |
    +————-+———+
    3 rows in set (0.00 sec)

    Оператор ORDER BY используется для сортировки результатов запроса. По умолчанию числовые значения сортируются в порядке возрастания, а текстовые значения сортируются в алфавитном порядке. Для примера в следующем запросе перечислены имена и даты рождения, результаты отсортированы по дате рождения:

    SELECT name, birthdate FROM dinners ORDER BY birthdate;
    +———+————+
    | name | birthdate |
    +———+————+
    | Etta | 1938-01-25 |
    | Irma | 1941-02-18 |
    | Gladys | 1944-05-28 |
    | Dolly | 1946-01-19 |
    | Barbara | 1948-12-25 |
    +———+————+
    5 rows in set (0.00 sec)

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

    SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;
    +———+————+
    | name | birthdate |
    +———+————+
    | Barbara | 1948-12-25 |
    | Dolly | 1946-01-19 |
    | Gladys | 1944-05-28 |
    | Irma | 1941-02-18 |
    | Etta | 1938-01-25 |
    +———+————+
    5 rows in set (0.00 sec)

    Как упоминалось ранее, WHERE используется для фильтрации результатов на основе определенных условий. Однако если вы используете WHERE с агрегатной функцией, он вернет ошибку. Для примера попробуем выяснить, какой гарнир нравится как минимум трем подругам:

    SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3;
    ERROR 1111 (HY000): Invalid use of group function

    Оператор HAVING добавлен в SQL для выполнения функций, аналогичных WHERE, но совместимых с агрегатными функциями. Разница между этими двумя операторами в том, что WHERE применяется к отдельным записям, а HAVING – к групповым. Для этого при каждом выполнении HAVING также должен присутствовать оператор GROUP BY.

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

    SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3;
    +————-+——-+
    | COUNT(name) | side |
    +————-+——-+
    | 3 | fries |
    +————-+——-+
    1 row in set (0.00 sec)

    Агрегатные функции полезны для суммирования результатов определенного столбца в данной таблице. Однако во многих случаях необходимо запросить содержимое сразу нескольких таблиц. Мы рассмотрим несколько способов сделать это в следующем разделе.

    Запрос данных из нескольких таблиц

    Чаще всего база данных содержит несколько таблиц, каждая из которых содержит разные наборы данных. SQL предоставляет несколько способов выполнения одного запроса для нескольких таблиц.

    Оператор JOIN можно использовать для объединения строк из двух или более таблиц в результате запроса. Это достигается путем нахождения одинакового столбца между таблицами и соответствующей сортировки результатов в выходных данных.

    Выражение SELECT с оператором JOIN, как правило, работает по такому синтаксису:

    SELECT table1.column1, table2.column2
    FROM table1
    JOIN table2 ON table1.related_column=table2.related_column;

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

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

    SELECT tourneys.name, tourneys.size, dinners.birthdate
    FROM tourneys
    JOIN dinners ON tourneys.name=dinners.name;
    +———+——+————+
    | name | size | birthdate |
    +———+——+————+
    | Dolly | 8.5 | 1946-01-19 |
    | Etta | 9 | 1938-01-25 |
    | Irma | 7 | 1941-02-18 |
    | Barbara | 7.5 | 1948-12-25 |
    | Gladys | 8 | 1944-05-28 |
    +———+——+————+
    5 rows in set (0.00 sec)

    Оператор JOIN, используемый в этом примере без каких-либо других аргументов, является внутренним JOIN. Это означает, что он выбирает в обеих таблицах все записи, у которых есть общее значение (в данном случае – имя), и выводит их в наборе результатов, в то время как все остальные записи исключаются. Чтобы проиллюстрировать эту функцию, давайте добавим в каждую таблицу новые строки, у которых не будет общего значения:

    INSERT INTO tourneys (name, wins, best, size)
    VALUES (‘Bettye’, ‘0’, ‘193’, ‘9’);
    INSERT INTO dinners (name, birthdate, entree, side, dessert)
    VALUES (‘Lesley’, ‘1946-05-02’, ‘steak’, ‘salad’, ‘ice cream’);

    А теперь повторите запрос:

    SELECT tourneys.name, tourneys.size, dinners.birthdate
    FROM tourneys
    JOIN dinners ON tourneys.name=dinners.name;
    +———+——+————+
    | name | size | birthdate |
    +———+——+————+
    | Dolly | 8.5 | 1946-01-19 |
    | Etta | 9 | 1938-01-25 |
    | Irma | 7 | 1941-02-18 |
    | Barbara | 7.5 | 1948-12-25 |
    | Gladys | 8 | 1944-05-28 |
    +———+——+————+
    5 rows in set (0.00 sec)

    Обратите внимание, что, поскольку в таблице tourneys нет записи для Lesley, а в таблице dinners нет записи для Bettye, эти записи отсутствуют в выходных данных.

    Однако возможно вернуть все записи из одной из таблиц, используя внешнее соединение JOIN. Внешние JOIN записываются как LEFT JOIN и RIGHT JOIN.

    Предложение LEFT JOIN возвращает все записи из левой таблицы и только совпадающие записи из правой таблицы. В контексте внешнего соединения левая таблица – это таблица, на которую ссылается FROM, а правая – любая другая таблица, на которую есть ссылка после оператора JOIN.

    Выполните предыдущий запрос еще раз, но на этот раз используйте LEFT JOIN:

    SELECT tourneys.name, tourneys.size, dinners.birthdate
    FROM tourneys
    LEFT JOIN dinners ON tourneys.name=dinners.name;

    Эта команда выведет каждую запись из левой таблицы (в данном случае tourneys), даже если в правой таблице нет совпадающей записи. Каждый раз, когда в правой таблице нет подходящей записи, она возвращается как пустое значение или NULL, в зависимости от СУБД.

    +———+——+————+
    | name | size | birthdate |
    +———+——+————+
    | Dolly | 8.5 | 1946-01-19 |
    | Etta | 9 | 1938-01-25 |
    | Irma | 7 | 1941-02-18 |
    | Barbara | 7.5 | 1948-12-25 |
    | Gladys | 8 | 1944-05-28 |
    | Bettye | 9 | NULL |
    +———+——+————+
    6 rows in set (0.00 sec)

    Повторите запрос с оператором RIGHT JOIN:

    SELECT tourneys.name, tourneys.size, dinners.birthdate
    FROM tourneys
    RIGHT JOIN dinners ON tourneys.name=dinners.name;

    Это вернет все записи из правой таблицы (dinners). Поскольку дата рождения Лесли записана в правой таблице, но для нее нет соответствующей строки в левой таблице, в столбцах name и size в этой строке будут значения NULL:

    +———+——+————+
    | name | size | birthdate |
    +———+——+————+
    | Dolly | 8.5 | 1946-01-19 |
    | Etta | 9 | 1938-01-25 |
    | Irma | 7 | 1941-02-18 |
    | Barbara | 7.5 | 1948-12-25 |
    | Gladys | 8 | 1944-05-28 |
    | NULL | NULL | 1946-05-02 |
    +———+——+————+
    6 rows in set (0.00 sec)

    Обратите внимание, что левые и правые соединения могут быть записаны как LEFT OUTER JOIN или RIGHT OUTER JOIN, хотя OUTER и так подразумевается. Аналогично, INNER JOIN дает тот же результат, что и простой JOIN.

    В качестве альтернативы JOIN для запроса записей из нескольких таблиц вы можете использовать оператор UNION.

    Оператор UNION работает немного иначе, чем JOIN: вместо вывода результатов из нескольких таблиц в виде уникальных столбцов с помощью одного оператора SELECT он объединяет результаты двух операторов SELECT в один столбец.

    Рассмотрим такой запрос:

    SELECT name FROM tourneys UNION SELECT name FROM dinners;

    Он удалит все дублируемые записи, так как это поведение UNION по умолчанию.

    +———+
    | name |
    +———+
    | Dolly |
    | Etta |
    | Irma |
    | Barbara |
    | Gladys |
    | Bettye |
    | Lesley |
    +———+
    7 rows in set (0.00 sec)

    Чтобы вывести все записи, включая повторы, используйте UNION ALL.

    SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;
    +———+
    | name |
    +———+
    | Dolly |
    | Etta |
    | Irma |
    | Barbara |
    | Gladys |
    | Bettye |
    | Dolly |
    | Etta |
    | Irma |
    | Barbara |
    | Gladys |
    | Lesley |
    +———+
    12 rows in set (0.00 sec)

    Значения и количество столбцов в результате отражают имя и количество столбцов, запрошенных первым оператором SELECT. Обратите внимание, что при использовании UNION для запроса нескольких столбцов из более, чем одной таблицы каждый оператор SELECT должен запрашивать одинаковое количество столбцов, соответствующие столбцы должны иметь одинаковые типы данных, а также идти в одном порядке в каждом операторе SELECT. В следующем примере показано, что может произойти, если вы используете предложение UNION для двух операторов SELECT, которые запрашивают разное количество столбцов:

    SELECT name FROM dinners UNION SELECT name, wins FROM tourneys;
    ERROR 1222 (21000): The used SELECT statements have a different number of columns

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

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

    SELECT name, wins FROM tourneys
    WHERE wins > (
    SELECT wins FROM tourneys WHERE name = ‘Barbara’
    );
    +———+——+
    | name | wins |
    +———+——+
    | Dolly | 7 |
    | Etta | 4 |
    | Irma | 9 |
    | Gladys | 13 |
    +———+——+
    4 rows in set (0.00 sec)

    Подзапрос в этом операторе был выполнен только один раз; нужно только найти значение из столбца wins в той строке, где в столбце name указано значение Barbara; данные, возвращаемые подзапросом и внешним запросом, не зависят друг от друга. Однако существуют случаи, когда внешний запрос должен сначала прочитать каждую строку в таблице и сравнить эти значения с данными, возвращенными подзапросом, чтобы получить требуемые данные. В этом случае подзапрос будет как коррелированным.

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

    SELECT name, size FROM tourneys AS t
    WHERE wins > (
    SELECT AVG(wins) FROM tourneys WHERE size = t.size
    );

    Чтобы обработать запрос, СУБД должна сначала собрать столбцы name и size из внешнего запроса. Затем она сравнивает каждую строку из этого набора результатов с результатами внутреннего запроса, который определяет среднее количество побед для людей с одинаковым размером обуви. Поскольку у вас есть только две подруги с одинаковым размером обуви, в наборе результатов получится только одна строка:

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

    SELECT name, entree, side, dessert
    FROM dinners
    WHERE name = (SELECT name FROM tourneys
    WHERE wins = (SELECT MAX(wins) FROM tourneys));
    +———+———+——-+————+
    | name | entree | side | dessert |
    +———+———+——-+————+
    | Gladys | steak | fries | ice cream |
    +———+———+——-+————+
    1 row in set (0.00 sec)

    Обратите внимание: этот запрос не только содержит подзапрос, но также еще один подзапрос внутри него.

    Заключение

    Запросы являются одной из наиболее часто выполняемых задач в области управления базами данных. Существует ряд инструментов администрирования баз данных (таких как phpMyAdmin и pgAdmin), которые позволяют выполнять запросы и визуализировать результаты, но выдача операторов SELECT из командной строки по-прежнему является широко распространенным рабочим процессом, который также может предоставить вам больший контроль над своими данными.

    Если вы новичок в SQL, мы рекомендуем вам ознакомиться с нашим мануалом Краткий справочник по управлению базой данных SQL и с официальной документацией MySQL. Кроме того, если вы хотите больше узнать о SQL и реляционных базах данных, вам могут быть интересны следующие мануалы:

    Профилирование запросов MySQL на примерах

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

    Профилирование рабочей нагрузки сервера MySQL

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

    С каждой следующей версией в MySQL появляется все больше инструментов, и если такая тенденция сохранится, скоро в MySQL будут великолепные инструменты для измерения наиболее важных аспектов ее производительности. Но что касается профилирования запросов и поиска самых затратных из них, нам не нужна вся эта сложность. Необходимый инструмент существует уже довольно давно. Это так на­зываемый журнал медленных запросов.

    Фиксация запросов MySQL в журнал

    В MySQL журнал медленных запросов изначально предназначался для фиксации только медленных запросов, но для целей профилирования необходима регистрация всех запросов. При этом нам требуется более тонкая детализация времени отклика, чем в MySQL 5.0 и ранних версиях. В этих версиях минимальный временной интер­вал равнялся 1 секунде. К счастью, прежние ограничения уже неактуальны.

    В MySQL 5.1 и более поздних версиях журнал медленных запросов расширен так, что переменную сервера long_query_time можно установить равной нулю, зафиксировав все запросы, а время отклика на запрос детализировано с дискретностью 1 микросекунда. Если вы используете Percona Server, этот функционал доступен уже в версии 5.0, кроме того, Percona Server дает намного больший контроль над со­держимым журнала и фиксацией запросов.

    В существующих версиях MySQL у журнала медленных запросов наименьшие из­держки и наибольшая точность измерения времени выполнения запроса. Если вас беспокоит дополнительный ввод/вывод, вызываемый этим журналом, то не тревожь­тесь. Мы провели эталонное тестирование и выяснили, что при нагрузках, связанных с вводом/выводом, издержки незначительны. (На самом деле это лучше видно в ходе работ, нагружающих процессор.) Более актуальной проблемой является заполнение диска. Убедитесь, что вы установили смену журнала для журнала медленных за­просов, если он включен постоянно. Либо оставьте его выключенным и включайте только на определенное время для получения образца рабочей нагрузки.

    У MySQL есть и другой тип журнала запросов — общий журнал, но он не так полезен для анализа и профилирования сервера. Запросы регистрируются по мере их посту­пления на сервер, поэтому журнал не содержит информации о времени отклика или о плане выполнения запроса. MySQL 5.1 и более поздние версии поддерживают так­же ведение журнала запросов к таблицам, однако это не самая удачная идея. Данный журнал сильно влияет на производительность: хотя MySQL 5.1 в журнале медленных запросов отмечает время запросов с точностью до 1 микросекунды, медленные за­просы к таблице регистрируются с точностью до 1 секунды. Это не очень полезно.

    Percona Server регистрирует в журнале медленных запросов значительно более по­дробную информацию, чем MySQL. Здесь отмечается полезная информация о плане выполнения запроса, блокировке, операциях ввода/вывода и многом другом. Эти до­полнительные биты данных добавлялись медленно, поскольку мы столкнулись с раз­личными сценариями оптимизации, которые требовали более подробных сведений о том, как запросы выполняются и где происходят затраты времени. Мы также упростили администрирование. Например, добавили возможность глобально кон­тролировать порог long_query_time для каждого соединения, поэтому вы можете заставить их запускать или останавливать журналирование своих запросов, когда приложение использует пул соединений или постоянные соединения, но не можете сбросить переменные уровня сеанса.

    В целом это легкий и полнофункциональный способ профилирования сервера и оп­тимизации его запросов.

    Допустим, вы не хотите регистрировать запросы на сервере или по какой-то причине не можете делать этого, например не имеете доступа к серверу. Мы сталкивались с такими ограничениями, поэтому разработали две альтернативные методики и до­бавили их в инструмент pt-query-digest пакета Percona Toolkit. Первая методика подразумевает постоянное отслеживание состояния с помощью команды SHOW FULL PROCESSLIST с параметром — processlist . При этом отмечается, когда запросы появ­ляются и исчезают. В некоторых случаях этот метод довольно точен, но он не может зафиксировать все запросы. Очень короткие запросы могут проскочить и завершить­ся, прежде чем инструмент их заметит.

    Второй метод состоит в фиксировании сетевого трафика TCP и его проверки, а затем декодирования протокола «клиент/сервер MySQL» (MySQL client/server protocol). Вы можете использовать утилиту tcpdump для записи трафика на диск, а затем — pt-query-digest с параметром —type=tpcdump для декодирования и анализа запросов. Это гораздо более точная методика, которая зафиксирует все запросы. Методика работает даже с расширенными протоколами, такими как бинарный про­токол, используемый для создания и выполнения подготовленных операторов на стороне сервера, и сжатый протокол. Можно также использовать MySQL Proxy со скриптом журналирования, но в практике это нам редко встречалось.

    Анализ журнала запросов

    Мы рекомендуем по крайней мере время от времени фиксировать в журнале медлен­ных запросов все запросы, выполняемые на сервере, и анализировать их. Запишите запросы, сделанные в течение репрезентативного периода времени, например за час пикового трафика. Если рабочая нагрузка однородна, достаточно будет минуты или даже меньше для нахождения плохих запросов, которые следует оптимизировать.

    Не стоит просто открывать журнал и смотреть в него — это пустая трата времени и денег. Сначала создайте профиль и, если это необходимо, просмотрите конкрет­ные выборки в журнале. Лучше всего начинать с верхнего уровня и двигаться вниз, в противном случае, как упоминалось ранее, вы можете деоптимизировать процесс.

    Для создания профиля из журнала медленных запросов требуется хороший инстру­мент анализа журналов. Мы предлагаем утилиту pt-query-digest , которая, возможно, является самым мощным инструментом анализа журнала запросов MySQL. Она под­держивает множество различных функций, включая возможность сохранять отчеты о запросах в базе данных и отслеживать изменения в рабочей нагрузке.

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

    Приведем краткий обзор отчета pt-query-digest , начиная с профиля. Ниже представ­лена полная версия профиля, который мы показали ранее в этой статье:

    Здесь показано чуть больше деталей, чем раньше. Во-первых, каждый запрос имеет идентификатор, который является хеш-подписью его цифрового отпечатка. Цифровой отпечаток — это нормализованная каноническая версия запроса с уда­ленными литералами и пробелами, переведенная в нижний регистр (обратите внимание, что запросы 3 и 4 кажутся одинаковыми, но у них разные отпечатки). Инструмент также объединяет таблицы с похожими именами в каноническую форму. Вопросительный знак в конце имени таблицы invitesNew означает, что к имени таблицы был добавлен идентификатор сегмента данных (шарда), а инструмент уда­лил его, так что запросы к таблицам, сделанные с похожими целями, объединены вместе. Этот отчет взят из сильно шардированного приложения Facebook.


    Еще один появившийся здесь столбец — отношение рассеяния к среднему значе­нию V/M. Этот показатель называется индексом рассеяния. У запросов с более высо­ким индексом рассеяния сильнее колеблется время выполнения, и они, как правило, являются хорошими кандидатами на оптимизацию. Если вы укажете параметр —explain в утилите pt-query-digest , то к таблице будет добавлен столбец с кратким описанием плана запроса EXPLAIN — своего рода неформальный код запроса. Это в со­четании со столбцом V/M позволяет быстро определить, какие запросы являются плохими и потенциально легко оптимизируемыми.

    Наконец, в нижней части есть дополнительная строка, показывающая наличие 17 других типов запросов, которые инструмент не счел достаточно важными для отдельной строки, и сводная статистика по ним. При задании параметров —limit и —outliers инструмент не будет сворачивать несущественные запросы в одну финальную строку. По умолчанию он выводит запросы, которые входят в первую десятку по затраченному времени либо время выполнения которых превысило односекундный порог во много раз. Оба этих параметра можно настроить.

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

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

    Затем представлена гистограмма времени отклика. Любопытно, что, как вы види­те под строкой Query_time distribution , у гистограммы этого запроса два пика. Обычно запрос выполняется за сотни миллисекунд, но есть также значительный всплеск числа запросов, которые были выполнены на три порядка быстрее. Если бы этот журнал был создан в пакете Percona Server, в журнале запросов был бы более богатый набор параметров. Как следствие, мы могли бы проанализировать запро­сы вдоль и поперек, чтобы понять, почему это происходит. Возможно, это были запросы к определенным значениям, которые непропорционально распределены, поэтому использовался другой индекс, или, возможно, это хиты запросов кэша. В реальных системах гистограмма с двумя пиками не редкость, особенно в случае простых запросов, которые часто имеют лишь несколько альтернативных путей выполнения.

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

    После выбора запросов, которые вы хотите оптимизировать, можете использовать этот отчет, чтобы быстро проверить выполнение запроса. Мы постоянно пользуемся этим инструментом, и потратили много времени на то, чтобы сделать его максимально эффективным и полезным. Настоятельно рекомендуем подружиться с ним. Возможно, в скором времени MySQL будет лучше оснащена встроенными инструментами профи­лирования, но на момент написания этой статьи нет инструментов лучше, чем журналиро­вание запросов с помощью журнала медленных запросов или использование tcpdump и запуск полученного журнала с помощью утилиты pt-query-digest .

    Профилирование отдельных запросов

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

    К сожалению, большинство инструментов в MySQL не очень полезны для профили­рования запросов. Ситуация меняется, но на момент написания этого блога большинство производственных серверов не поддерживают новейших функций профилирования. Поэтому при их использовании в практических целях мы сильно ограничены командами SHOW STATUS , SHOW PROFILE и изучением отдельных записей в журнале медленных запро­сов (если у вас есть Percona Server — в стандартной системе MySQL в журнале нет до­полнительной информации). Мы продемонстрируем все три метода на примере одного и того же запроса и покажем, что вы можете узнать о его выполнении в каждом случае.

    Команда SHOW PROFILE

    Команда SHOW PROFILE появилась благодаря Джереми Коулу (Jeremy Cole). Она включена в MySQL 5.1 и более поздние версии. Это единственный реальный инструмент профилирования запросов, доступный в GA-релизе MySQL на момент на­писания блога. Профилирование по умолчанию отключено, но его можно включить во время сеанса, установив значение переменной сервера:

    После этого всякий раз, когда вы посылаете выражение на сервер, он будет замерять прошедшее время и еще некоторые данные, когда запрос будет переходить из одного состояния выполнения в другое. Эта команда имеет довольно широкий функционал и была спроектирована так, что может иметь еще больше, но в следующих релизах она, по всей видимости, будет заменена или вытеснена Performance Schema. Несмо­тря на это, наиболее полезной функцией данной команды является создание профиля работы, выполняемой сервером во время реализации выражения.

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

    Запрос возвратил 997 строк примерно через 1/6 секунды. Посмотрим, что выдаст команда SHOW PROFILES (обратите внимание на множественное число):

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

    MySQL, часто недостаточно, когда вы работаете с быстрыми запросами. Теперь по­смотрим на профиль для этого запроса:

    Профиль позволяет следить за каждым шагом выполнения запроса и видеть, сколько прошло времени. Обратите внимание, что не очень легко просмотреть выведенный результат и найти, где затраты времени были максимальными: он сортируется в хро­нологическом порядке. Однако нас интересует не порядок, в котором выполнялись операции, — мы просто хотим знать, каковы были затраты времени на них. К сожа­лению, отсортировать вывод с помощью ORDER BY нельзя. Давайте перейдем к исполь­зованию команды SHOW PROFILE для запроса связанной таблицы INFORMATION_SCHEMA и формата, который выглядит как просмотренные нами ранее профили:

    Так намного лучше! Теперь мы видим, что причина, по которой этот запрос так долго выполнялся, заключалась в копировании данных во временную таблицу, на что затрачено более половины общего времени. Возможно, придется переписать этот запрос так, чтобы он не использовал временную таблицу или хотя бы делал это более эффективно. Следующий крупный потребитель времени, отправка данных, фактически является своеобразной кладовкой, которая может включать в себя любое количество различных действий сервера, в том числе поиск совпадающих строк в соединении и т. д. Трудно сказать, сможем ли мы здесь что-то сэкономить. Обратите внимание на то, что результат сортировки занимает недостаточно много времени, чтобы его можно было оптимизировать. Это довольно типично, поэтому призываем вас не тратить время на «настройку буферов сортировки» и подобные мероприятия.

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

    Команда SHOW STATUS

    Команда SHOW STATUS MySQL возвращает множество счетчиков. Существует гло­бальная область действия сервера для счетчиков, а также область сеанса, которая специфична для конкретного соединения. Например, счетчик Queries в начале вашего сеанса равен нулю и увеличивается каждый раз, когда вы делаете запрос. Выполнив команду SHOW GLOBAL STATUS (обратите внимание на добавление ключевого слова GLOBAL ), вы увидите общее количество запросов, полученных с момента его запуска. Области видимости разных счетчиков различаются — счетчики, которые не имеют области видимости на уровне сеанса, отображаются в SHOW STATUS , маски­руясь под счетчики сеансов, и это может ввести в заблуждение. Учитывайте это при использовании данной команды. Как говорилось ранее, подбор должным образом откалиброванных инструментов является ключевым фактором успеха. Если вы пытаетесь оптимизировать что-то, что можете наблюдать только в конкретном со­единении с сервером, измерения, которые «засоряются» всей активностью сервера, вам не помогут. В руководстве по MySQL есть отличное описание всех переменных, имеющих как глобальную, так и сеансовую область видимости.

    SHOW STATUS может быть полезным инструментом, но на самом деле его применение — это не профилирование. Большинство результатов команды SHOW STATUS — всего лишь счетчики. Они сообщают вам, как часто протекали те или иные виды деятель­ности, например чтение из индекса, но ничего не говорят о том, сколько времени на это было затрачено. В команде SHOW STATUS есть только один счетчик, который по­казывает время, израсходованное на операцию ( Innodb_row_lock_time ), но он имеет лишь глобальную область видимости, поэтому вы не можете использовать его для проверки работы, выполненной в ходе сеанса.

    Хотя команда SHOW STATUS не предоставляет информацию о затратах времени, тем не менее иногда может быть полезно использовать ее после выполнения запроса для просмотра значений некоторых счетчиков. Вы можете сделать предположение о том, какие типы затратных операций выполнялись и как они могли повлиять на время запроса. Наиболее важными счетчиками являются счетчики обработчи­ков запросов и счетчики временных файлов и таблиц. А сейчас приведем пример сброса счетчиков состояния сеанса до нуля, выбора из использованного нами ранее представления и просмотра счетчиков:

    Похоже, что в запросе использовались три временные таблицы — две из них на дис­ке — и было много неиндексированных чтений ( Handler_read_rnd_next ). Если бы мы ничего не знали о представлении, к которому только что обращались, то могли бы предположить, что запрос сделал объединение без индекса, возможно, из-за подза­проса, который создал временные таблицы, а затем использовал их с правой стороны в соединении. Временные таблицы, созданные для хранения результатов подзапро­сов, не имеют индексов, поэтому эта версия кажется правдоподобной.

    Используя эту методику, имейте в виду, что команда SHOW STATUS создает временную таблицу и обращается к ней с помощью обработчика операций, поэтому на полу­ченные результаты в действительности влияет и SHOW STATUS . Это зависит от версий сервера. Используя информацию о выполнении запроса, полученную от команды SHOW PROFILES , мы можем предположить, что количество временных таблиц завы­шено на 2.

    Стоит отметить, что большую часть той же информации, по-видимому, можно полу­чить, просмотрев план EXPLAIN для этого запроса. Но EXPLAIN — это оценка того, что сервер планирует делать, а просмотр счетчиков статуса — это измерение того, что он на самом деле сделал. EXPLAIN не скажет вам, например, была ли временная табли­ца создана на диске, что медленнее, чем в памяти.

    Использование журнала медленных запросов

    Что расширенный в Percona Server журнал медленных запросов расскажет об этом запросе? Вот что было зафиксировано при выполнении запроса, продемонстриро­ванного в разделе о SHOW PROFILE :

    Похоже, что запрос действительно создал три временные таблицы, которые были скрыты от представления в SHOW PROFILE (возможно, из-за особенностей способа вы­полнения запроса сервером). Две временные таблицы находились на диске. Здесь мы сократили выведенную информацию для улучшения удобочитаемости. В конце концов, данные, полученные при выполнении команды SHOW PROFILE по этому за­просу, записываются в журнал, поэтому вы можете журналировать в Percona Server даже такой уровень детализации.

    Согласитесь, эта весьма подробная запись в журнале медленных запросов содержит практически все, что вы можете видеть в SHOW PROFILE и SHOW STATUS , и еще кое-что. Это делает журнал очень полезным для поиска более подробной информации при нахождении плохого запроса с помощью утилиты pt-query-digest . Когда вы просмо­трите отчет от pt-query-digest , увидите такую строку заголовка:

    Вы можете использовать байтовое смещение для фокусировки на нужном разделе журнала следующим образом:

    Вуаля! Можно рассмотреть все подробности. Кстати, pt-query-digest понимает все добавленные пары «имя — значение» формата медленного журнала запросов Percona Server и автоматически выводит намного более подробный отчет.

    Использование Performance Schema

    На момент написания этой статьи таблицы Performance Schema, представленные в MySQL 5.5, не поддерживают профилирование на уровне запросов. Performance Schema появилась не так давно. Однако она быстро развивается, приобретая до­полнительную функциональность в каждом следующем релизе. Но даже первона­чальная функциональность MySQL 5.5 позволяет получать любопытную инфор­мацию. Например, следующий запрос покажет основные причины ожидания в системе:

    Сейчас существует несколько моментов, ограничивающих использование Per­formance Schema в качестве инструмента профилирования общего назначения. Во-первых, она не обеспечивает достаточный уровень детализации выполнения запросов и затрат времени, который можно получить благодаря существующим ин­струментам. Во-вторых, она довольно долго не использовалась и в данный момент ее применение приводит к большим издержкам, чем применение привычного для многих инструмента профилирования. (Есть основания полагать, что это будет ис­правлено в ближайшее время.)

    Наконец, иногда она слишком сложна и низкоуровнева для использования большин­ством пользователей. Функции, реализованные к настоящему моменту, в основном нацелены на то, что нужно измерить при изменении исходного кода MySQL для улучшения производительности сервера. Сюда относятся такие элементы, как ожи­дания и мьютексы. Некоторые из функций MySQL 5.5 полезны для опытных пользо­вателей, а не для разработчиков серверов. Однако пользователи все еще нуждаются в разработке удобных инструментов интерфейса. В настоящее время для написания сложных запросов к разнообразным таблицам метаданных с большим количеством столбцов требуется настоящее мастерство. Это довольно сложный для использования и понимания набор инструментов.

    Будет здорово, когда Performance Schema в более поздних версиях MySQL получит больше функциональности. И очень приятно, что Oracle реализует ее как табли­цы, доступные через SQL, тем самым пользователи могут получать данные любым удобным для них способом. Однако пока она еще не способна заменить журнал медленных запросов или другие инструменты, помогающие сразу увидеть варианты улучшения производительности сервера и выполнения запросов.

    Использование профиля для оптимизации

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

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

    Существует еще одна возможность. Предположим, вы анализируете журнал мед­ленных запросов и находите простой запрос, на несколько запусков которого за­трачено неоправданно много времени, хотя он быстро запускался в тысячах других случаев. Вы снова запускаете запрос, и он выполняется молниеносно, как и должно быть. Применяете EXPLAIN и обнаруживаете, что он правильно использует индекс. Вы пытаетесь использовать похожие запросы с разными значениями в разделе WHERE , чтобы убедиться, что запрос не обращается к кэшу, и они тоже выполняются быстро. Кажется, что с этим запросом все нормально. Что дальше?

    Если у вас есть только стандартный журнал медленных запросов MySQL без плана выполнения или подробной информации о времени, вы знаете только, что запрос плохо работал, когда был журналирован, и не можете понять, почему это произошло. Возмож­но, что-то еще потребляло ресурсы в системе, например резервное копирование или какая-то блокировка или параллелизм тормозили ход запроса. Периодически возника­ющие проблемы — это особый случай, который мы рассмотрим в следующей статье.

    Сложные запросы используемые в MySQL

    Всего лишь пару лет назад, в проектах, которые предусматривали работу с базами данных и построением статистики, основным изобилием используемых SQL-запросов, преобладало в основном множество запросов, ориентированных на стандартную выборку данных и нечасто можно было увидеть другие, которые безо всяких сомнений можно было бы отнести к “эксклюзиву”. Хотя сложность запроса и зависит от количества используемых таблиц, но если мы всего лишь возьмем и выведем данные полей трех или более таблиц имеющих стандартное объединение, то явная сложность такого запроса не выйдет за пределы стандартной.

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

    Сравнение данных за две даты

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

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

    Структура таблицы products

    Структура таблицы statistics

    Дело в том, что стандарт языка SQL допускает использование вложенных запросов везде, где разрешается использование ссылок на таблицы. Здесь вместо явно указанных таблиц, благодаря использованию псевдонимов, будут применяться результирующие таблицы вложенных запросов с имеющейся связью один – к – одному. Результатом каждой результирующей таблицы будут данные о количестве произведенных заказов некоего товара за определенную дату, полученные путем выполнения запроса на выборку данных из таблицы statistics по требуемым критериям. Иными словами мы свяжем таблицу statistics саму с собой. Пример запроса:

    В итоге имеем такой результат:

    Подстановка нескольких значений из другой таблицы

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

    Демонстрация данного запроса будет происходить на другом примере, а не на примере сетевой игры. Это чтобы не создавать заново все необходимые таблицы. В качестве данных возьмем таблицу products из примера “сравнение данных за две даты” и создадим еще одну недостающую таблицу replace_com, структура которой представлена ниже:

    Предположим, что у нас есть некий компьютерный салон и мы проводим модификации некоторых компьютерных составляющих, а все операции по замене комплектующих заносим в базу данных. В таблице replace_com интересующими нас полями являются: sProductID и rProductID. Где sProductID – идентификатор заменяемого модуля, а rProductID – идентификатор заменяющего модуля. Запрос, реализующий вывод данных о совершенных операциях выглядит следующим образом:

    Результирующая таблица данных:

    Вывод статистики с накоплением по дате

    Предположим, что у нас имеется склад с некими товарами. Товары периодически поступают, и нам бы хотелось видеть в отчете остатки товаров по дням. Поскольку данные о наличии товаров необходимо накапливать, то мы введем пользовательскую переменную. Но есть одно небольшое “но”. Мы не можем использовать в запросе переменные пользователя и группировку данных одновременно (вернее можем, но в итоге получим, не то, что ожидаем), но мы можем использовать вложенный запрос, вместо явно указанной таблицы. Данные в таблице будут предварительно сгруппированы по дате. И уже затем на основе этих данных мы произведем расчет статистики с накоплением.

    На первом этапе требуется установить переменную и присвоить ей нулевое значение:

    В следующем запросе, мы созданную ранее переменную и применим:

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

    MySQL запросы для начинающих — часть первая

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

    Подключение к серверу MySQL и выбор базы данных

    Для работы с базой данных естественно нужно сначала к ней подключиться. В большинстве случаев для этого используют либо работу из консоли, подключившись к серверу хостинга по протоколу SSH, либо скрипт PHP (однако все примеры SQL-запросов, приводимые в этой статье, можно использовать и в phpMyAdmin).

    Подключение из консоли по протоколу SSH:

    В данном примере root — это имя пользователя (пользователя с правами на запуск mysql, многие обычно используют суперадмина), а после выполнения вышеприведенной команды вам будет предложено ввести пароль.

    Подключение из скрипта PHP:

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

    Пример для консоли SSH:

    — получаем список всех баз данных:

    — выбираем для работы базу данных example_db :

    — если понадобится, то можно просмотреть структуру любой таблицы из базы данных example_db , которую вы выбрали для работы, к примеру таблицы clients :

    Пример скрипта PHP:

    Кстати, используя скрипт PHP также можно получать список всех баз данных на сервере, к которому вы подключаетесь:

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

    SELECT — базовые запросы

    Этот запрос возвращает все записи, поля и их значения из таблицы clients .

    В следующем примере выбираются не все данные из таблицы clients , а только те, которые хранятся в столбцах name (имя) и address (адрес) клиентов:

    Команда SELECT и виртуальная таблица DUAL

    С помощью SELECT можно обращаться к виртуальной таблице DUAL (эта таблица реально НЕ существует в базе данных) и выполнять кое-какие действия, которые не имеют отношения к реальным таблицам. Например вы можете с помощью SQL-запроса произвести какие-то арифметические вычисления:

    Скрипт PHP, который выполнит этот запрос и выведет на экран «500» как результат выражения (2+3)*100 :

    Этот запрос вернет точное время и дату на сервере MySQL:

    Приведенные в этом примере запросы можно выполнять и без указания «FROM DUAL»:

    но все же, во избежание проблем и лучшего восприятия, не рекомендую использовать такой «сокращенный» вариант!

    WHERE — ограничение по выбору данных

    Выбираем всех клиентов, возраст которых больше 30, не равно 30, меньше 30, больше или равно 30 лет:

    WHERE — ограничение по совпадению строк

    Этот запрос похож на предыдущие запросы, где сравнивались числа, но здесь уже идет сравнение по строкам, и он выбирает всех клиентов, например проживающих в городе «Minsk».

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

    Кроме того, в запросах со сравнением по строкам можно использовать и частичное совпадение строк (для подстановки «любых» символов используется знак процента % ). Например здесь будут выбраны все записи таблицы, в которых значение в колонке city будут начинаться с «Novo» (начало названий городов «Novopolotsk», «Novosibirsk» и т.д.):

    Следующий запрос выберет все записи из таблицы clients , в которых имена клиентов заканчиваются на «лина» (например «Галина», «Ангелина»):

    Ну и наконец выбор всех клиентов, проживающих в городах, названия которых содержат в любом месте подстроку «lug» (например «Kaluga», «Lugansk»)

    WHERE — комбинирование условий с применением OR и AND

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

    В данном примере из таблицы clients будут выбраны все клиенты, возраст которых не меньше 25 лет и при этом обязательно проживающие в городе Москва:

    Здесь же, в отличие от предыдущего примера, из таблицы clients выбираются строки, в которых есть клиенты, проживающие в одном из двух городов, либо в Moscow , либо в Minsk :


    Чтобы не делать из темы «MySQL запросы для начинающих» настоящую длинную «простынь», на этом первая часть обзора завершается.
    Другие статьи из этой серии:
    Вторая часть
    Третья часть
    Четвертая часть

    SQL — урок 8 Примеры запросов с учетом особенностей MySQL

    Программирование › Базы данных › SQL — урок 8 Примеры запросов с учетом особенностей MySQL

    В этой теме 0 ответов, 1 участник, последнее обновление Васильев Владимир Сергеевич 11 мес. назад.

    О чем поговорим?

    Выборка и вставка

    В MySQL не реализована привычная работа для SELECT INTO, если требуется создать новую таблицу как результат выборки необходимо писать запрос вида:

    CREATE TABLE NewTable SELECT * FROM players

    В MySQL конструкция SELECT INTO отрабатывает как вставка в существующую таблицу:

    Limit (Ограничение выборки по количеству выдаваемых строк)

    Постраничный режим реализуется через LIMIT
    SELECT id FROM players LIMIT 5, 10;
    где: 5 — количество пропускаемых с начала строк, 10 — количество выбираемых строк
    Приведенный ниже пример вернет первые 5 строк, без пропуска какого-либо количества.
    SELECT id FROM players LIMIT 5;

    Конкатенация строк

    SELECT CONCAT(login,’-‘, clan_name) FROM players WHERE clan_name <> »

    Sabio-SistersofMercy
    DEMON31-Sons ofdevil
    Экстра-Confederates
    Legendofgod-Sangrante
    Баргест-Restlesses

    GROUP_CONCAT

    — объединяет в одну несколько значений

    SELECT GROUP_CONCAT(login) from players WHERE id

    CtrlAltDelete,lolangel,Sabio,Витю Ты НашелЬ,ГоловУ-ТвоЮ-ШотоЛ,Джульета,Кошмар Рождества,смерть2476

    Тоже самое, но без GROUP_CONCAT
    SELECT (login) from players WHERE id

    CtrlAltDelete
    lolangel
    Sabio
    Витю Ты НашелЬ
    ГоловУ-ТвоЮ-ШотоЛ
    Джульета
    Кошмар Рождества
    смерть2476

    Работа со временем

    SELECT UNIX_TIMESTAMP(NOW()), FROM_UNIXTIME(1460632852)

    где: UNIX_TIMESTAMP — вернет время в виде числа (int), FROM_UNIXTIME — преобразует число в дату
    Результат:

    UNIX_TIMESTAMP FROM_UNIXTIME
    1460811566 14.04.2020 14:20:52

    Вставка в таблицы

    Создание таблицы как и везде:
    CREATE TABLE align (id int UNIQUE, title varchar(25));

    Вставка единичной записи
    INSERT INTO align (id,title) VALUES (10,’dark’);

    Одновременная вставка нескольких строк
    INSERT INTO align (id, title) values (20,’light’), (30, ‘chaos’), (40, ‘neutral’);

    Вставка записи, но в случае наличия дубля по ключу обновление записи
    INSERT INTO align (

    Интересный вариант вставки с IGNORE
    INSERT IGNORE INTO align (id,title) VALUES (10,’dark’);

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

    UPDATE

    Обновить title таблицы tmp значениями из таблицы align где совпадают id (реализация схемы UPDATE FROM SELECT)
    UPDATE tmp AS t INNER JOIN align AS a ON t. >

    Обновление поля значением вложенного запроса
    UPDATE tmp SET title = (SELECT title FROM align LIMIT 1) WHERE >

    Обновление поля вычисляемым значением из подзапроса
    UPDATE sections AS s INNER JOIN (SELECT COUNT( >

    Прочее

    Базовая работа метода Like как и везде:
    SELECT id, login FROM players WHERE login LIKE ‘%дед%’;
    Результат

    id login
    2661 Боевой Дед
    2558 Дед с Веслом

    GROUP BY + HAVING

    Вывести все кланы, где количество людей больше 5
    SELECT clan_name, COUNT(id) FROM players WHERE clan_name <> » GROUP BY clan_name HAVING COUNT(id) > 5

    clan_name COUNT(id)
    Burning Angels 6
    NiNo 6
    Orden des Kreuzes 6
    Order Angels 7

    LEFT JOIN

    Вывести первые 10 записей (ид, логин, имя склонности) из таблицы персонажей и связанной с ней таблицы склонностей
    SELECT p. >

    id login title
    942 A N Owen chaos
    1513 Ace HooD chaos
    862 Acheront dark
    836 Afone4ka (null)
    914 Agent Smith dark
    757 Agrael (null)
    2691 Air Wick chaos
    842 Aking light
    48 Alice2007 (null)
    908 AllTiger order

    DISTINCT

    Выбрать все уникальные названия кланов из таблицы players:
    SELECT DISTINCT clan_name FROM players p

    SistersofMercy
    Sons ofdevil
    Confederates
    Sangrante
    Restlesses
    TERMITE

    Илон Маск рекомендует:  Что такое код gzeof
    Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL