Всемогущая функция Query — подробное руководство. Добавление запросов (Power Query) Запрос SELECT DISTINCT - уникальные значения полей
Спасибо Евгению Намоконову за помощь в подготовке материала.
Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.
Синтаксис функции:
=QUERY(данные; запрос; [заголовки])
- данные - это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
- запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
- заголовки - количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.
Итак, правила формирования запросов:
- Запрос указывается в кавычках.
- В запросе используются ключевые слова:
- SELECT - определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
- WHERE - ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
- GROUP BY - группирует значения по заданным полям.
- PIVOT - позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
- ORDER BY - задает сортировку. Например: «ORDER BY C DESC» - сортировка по столбцу C по убыванию.
- LIMIT - ограничивает количество возвращаемых строк. Например: «LIMIT 50».
- OFFSET - пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
- FORMAT - определяет формат определенных столбцов по заданному шаблону.
- LABEL — позволяет переименовать столбцы в выдаче. Например, «LABEL MAX(D) ‘Среднее в 2016 году"». Вместо max 2016 в сформированной выдаче будет заголовок «Среднее в 2016 году».
Справка от Google по языку запросов API находится по ссылке: https://developers.google.com/chart/interactive/docs/querylanguage
Рассмотрим несколько примеров применения QUERY на практике.
Простой пример: выбираем книги определенной тематики из таблицы
Из простой исходной таблицы будем формировать список книг по тематике:
При этом тематику будем выбирать из выпадающего списка на отдельном листе:
Функция QUERY для решения этой задачи будет выглядеть следующим образом:
=QUERY(Книги ‘!A1:C ; «SELECT A, C WHERE B = ‘» & A1 & «‘ ORDER BY C DESC» ; 1 )
Мы извлекаем данные из столбцов A и C в диапазоне ‘Книги’!A1:C. Фильтруем данные по столбцу B (тематике) этого диапазона по выбранному критерию из выпадающего списка в ячейке A1. Сортируем по убыванию по столбцу C исходного диапазона и добавляем к нашей выборке заголовки (последний аргумент функции QUERY = 1).
Группируем данные с помощью GROUP BY и PIVOT
Сгруппировать данные, используя QUERY, можно с помощью двух ключевых слов: GROUP BY и PIVOT, ниже рассмотрим примеры с ними.
Таблица, с которой мы будем работать:
Задачей будет вывести сумму продаж по каждой тематике, то есть сгруппировать данные по столбцу B.
Начнем с GROUP BY, текст функции будет таким:
=QUERY(‘Книги ‘!A1:C6;»select B, sum(C) group by B»)
Обратите внимание: чтобы функция работала, помимо группировки (group by B) нужна хотя бы одна аггрегирующая функция, в нашем случае это sum(C). Напишу, на всякий случай, все аггрегирующие функции для QUERY: sum(), max(), min(), avg() и count().
Результат нашей формулы:
С помощью GROUP BY возможна группировка и по нескольким столбцам, для этого просто перечислите их, как в функции ниже и не забудьте добавить эти столбцы в SELECT:
Группировка с помощью PIVOT.
Обратите внимание, что здесь в SELECT не нужно писать столбец B, по которому данные будут сгруппированы.
Пока отличие в том, что сгрупированные элементы расположены по столбцам, а не по строкам, как в GROUP BY.
Добавим еще один столбец для группировки.
Видите — два сгруппированных столбца отображаются в одном поле через запятую. В этом ключевое отличие PIVOT от GROUP BY, если там каждый столбец группировки занимает отдельный столбец, то в PIVOT получается нечто вроде сводной таблицы с уникальными полями из нескольких элементов. По этим полям, кстати, потом можно довольно просто искать нужное значение с помощью ГПР или ПОИСКПОЗ.
Строим сводную таблицу со средними/максимальными значениями по тематикам
В этом примере мы построим небольшую сводную таблицу, где будут отображены средние значения по тематикам за два года:
QUERY(‘ Книги ‘!A1:D ; «SELECT avg(C), avg(D) pivot B» ; 1 )
Мы используем похожий диапазон (в отличие от предыдущего в нем есть продажи за 2015 и 2016 годы), извлекаем средние значения по столбцам C и D (SELECT avg(C), avg(D)) и группируем их по столбцу B (тематика).
Полученный результат транспонируем для удобного отображения (с помощью функции TRANSPOSE (ТРАНСП)):
Можно использовать и другие функции вместо avg (среднего), например max (максимальные значения):
Или отобразить и среднее, и максимум, но только по столбцу D:
SELECT avg(D), max(D)
Кейс «Считаем средний чек, выбирая данные с определенной даты»
На скриншоте массив данных, с которым мы будем работать:
Наша задача: отобрать строки с продажами начиная с 1 апреля и посчитать по ним средний чек, используя количество клиентов, то есть получить среднее взвешенное.
Начнем. Создадим QUERY с умножением количества клиентов (столбец B) на средний чек (столбец С) начиная с определенной даты:
Правильно использовать дату в формуле QUERY так:
- QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
- перед датой и перед апострофом нужно написать date;
- можно и не делать ссылку на ячейку с датой, а написать ее сразу в QUERY, тогда формула будет выглядеть так:
- дата с двух сторон обрамляется одиночными кавычками (‘).
Вернемся к тому, что у нас получилось. Наша формула выдала вот такой массив данных:
Это построчные произведения количества клиентов на средний чек. Нам нужно просуммировать их, для этого введем перед формулой СУММ (SUM):
Чтобы получить средний чек, получившееся число нужно разделить на общую сумму клиентов в отобранных строках. Чтобы закрепить использование QUERY, опять воспользуемся этой формулой.
Берем предыдущую формулу, меняем B*C на sum(B) и получаем такую конструкцию:
Наконец, совмещаем формулы:
Все работает, ура! 53 (этот результат видно на всплывающей подсказке в верхнем левом углу) - средний чек с учетом количества клиентов, рассчитанный через среднее взвешенное.
Кейс «QUERY и выпадающий список»
Возьмем табличку с продажами книг. На ее основе будем делать отчет с выпадающим списком, в котором будут все тематики, и формулой QUERY, выводящей книги выбранной тематики и сортирующей их по продажам.
Итак, выпадающий список. Вначале создадим новый лист (допустим, наша исходная таблица огромна, и всю аналитику мы хотим производить на другом листе). Кликаем правой кнопкой мыши на ячейку А1, выбираем Проверка данных .
В Правилах выбираем Значение из списка , перечисляем все наши тематики через запятую и нажимаем Сохранить :
Список получился вот таким:
В соседнюю ячейку А2 впишем следующую формулу:
=QUERY(‘ Книги ‘!A1:C13 , «SELECT A, C WHERE B = ‘» & A1 & «‘ ORDER BY C DESC» )
И разберем ее по частям:
- ‘Книги’!A1:C13 - исходный диапазон, таблица с продажами, книгами и тематиками.
- SELECT A, C - в сформированную функцией таблицу попадут данные из этих столбцов, то есть названия книг и продажи.
- WHERE B = ‘»&A1&»‘ отбирает только те книги, тематика (в столбце B) которых соответствует указанной в ячейке A1. Обратите внимание на синтаксис: текст из ячейки указывается между апострофов, которые относятся к тексту запроса. После них идут кавычки (мы закрываем текст запроса), амперсанд (присоединяем к тексту запроса текст из ячейки), адрес ячейки, еще один амперсанд, после которого в кавычках продолжается текст запроса.
- ORDER BY C DESC - сортируем данные по столбцу B (продажам) по убыванию.
Результат:
Изменив тематику в ячейке А1 на философию, мы получим книги только по философии, отсортированные по продажам. Удобно.
Если бы в нашей исходной таблице была дата, мы могли бы добавить ее в условие QUERY и выводить данные за выбранный день, месяц, неделю - таким образом можно получить готовый отчет по продажам, который не нужно каждый раз заново собирать.
Query по нескольким диапазонам данных
В качестве первого аргумента функции QUERY можно указать массив, состоящий из нескольких диапазонов данных. Главное, чтобы эти диапазоны были таблицами с одинаковой структурой.
Диапазоны указываются через точку с запятой в фигурных скобках:
=QUERY({Диапазон 1; Диапазон 2; Диапазон 3; Диапазон 4};…
Важно отметить: в таком случае столбцы внутри запроса обозначаются не буквами, как в других случаях (A, B, AH, CZ и так далее), а в виде ColN, где N — номер столбца.
Канал «Google Таблицы — это просто» в Телеграме
Спасибо Евгению Намоконову за помощь в подготовке кейсов для этой статьи. Мы с Евгением ведем канал в Телеграме по Google Таблицам.
Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать . Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция "Скачать и преобразовать" в Excel 2016 .
В Power Query операция Добавить создает новый запрос, содержащий все строки из первого запроса, а затем все строки из второго запроса.
Примечание по безопасности: Уровни конфиденциальности предотвращают случайное объединение данных из нескольких источников, которые могут быть частными или организационными. При использовании некоторых запросов пользователь может непреднамеренно отправить данные из частного источника данных в другой источник. Этой возможностью могут воспользоваться злоумышленники. Power Query анализирует каждый источник данных и определяет для него уровень конфиденциальности: общедоступный, организационный и частный. Дополнительные сведения об уровнях конфиденциальности см. в статье Уровни конфиденциальности .
Выполнение добавления
Вы можете выполнить операцию добавления двух видов: промежуточное добавление или встроенное добавление . При использовании промежуточного добавления для каждой операции добавления создается новый запрос. При встроенном добавлении вы добавляете данные в существующий запрос, пока не достигнете конечного результата. Результатом является новый шаг в конце текущего запроса.
Встроенное добавление
Промежуточное добавление
См. также:
Примечание: Редактор запросов отображается только при загрузке, редактировании или создании нового запроса с помощью Power Query . В видео показано окно редактора запросов , которое отображается после изменения запроса в книге Excel. Чтобы просмотреть редактор запросов , не загружая и не изменяя существующий запрос в книге, в разделе Получение внешних данных на вкладке ленты Power Query выберите Из других источников > Пустой запрос . В видео показан один из способов отображения редактора запросов .
Думаю, все слышали о правиле Парето. В любой сфере 20% усилий дают 80% результата. Например, 20% своего гардероба вы носите 80% времени, 20% ваших клиентов приносят 80% дохода. Так же и в Google Таблицах: зная 20% существующих функций, вы сможете решить 80% всех возможных задач.
Я считаю Query одной из наиболее полезных функций Google Таблиц. Но в справке Google она описывается очень поверхностно, и вся мощь данной функции не раскрыта. При более детальном знакомстве становится ясно, что она способна заменить большую часть существующих функций.
Для работы с QUERY вам понадобятся базовые знания SQL. Для тех, кто не в курсе: пугаться не надо, функция QUERY на самом деле поддерживает самые простые возможности SQL.
Синтаксис QUERY
QUERY(данные; запрос; [заголовки])- данные — это диапазон ячеек, который будет служить базой данных для SQL запроса;
- запрос — текст SQL запроса;
- заголовки — необязательный аргумент, в котором вы можете указать, сколько первых строк массива содержат заголовки.
Для максимального восприятия дальнейшей информации предлагаю открыть и скопировать себе следующую Google Таблицу
В доксе, копию которого вы только что создали, существует несколько листов. Лист DB - это база данных, к которой мы будет обращаться с помощью функции QUERY. Листы Level содержат примеры, которые мы будем рассматривать в этой статье. C каждым новым уровнем пример будет усложняться.
План SQL запроса в функции Query
Любой SQL запрос состоит из отдельных блоков, которые часто называют кляузами. В SQL для функции Query заложен синтаксис языка запросов API визуализации Google, который поддерживает следующие кляузы:
- select — перечисление полей, которые будут возвращены запросом;
- where — содержит перечень условий, с помощью которых будет отфильтрован массив данных, обрабатываемый запросом;
- group by — содержит перечень полей, по которым вы хотите группировать результат;
- pivot — помогает строить перекрестные таблицы, используя значение одного столбца в качестве названий столбцов финальной таблицы;
- order by — отвечает за сортировку результатов;
- limit — с помощью этой части запроса вы можете задать предел количеству строк, возвращаемых запросом;
- offset — с помощью этой кляузы вы можете задать число первых строк, которые не надо обрабатывать запросом;
- label — данная кляуза отвечает за название полей, возвращаемых запросом;
- format — отвечает за формат выводимых данных;
- options — дает возможность задавать дополнительные параметры вывода данных.
Hello World для функции Query (Select)
Перейдем на лист Level_1 и посмотрим формулу в ячейке A1.
Query(DB!A1:L1143;"select * limit 100")
Часть формулы «DB!A1:L1143» отвечает за базу данных, с которой мы будем делать выборку. Вторая часть «select * limit 100 » содержит непосредственно текст запроса. Символ «*» в данном случае означает возвращение всех полей, содержащихся в базе данных. С помощью «limit 100 » мы ограничиваем вывод данных в 100 строк максимум. Это пример самого простого запроса. Мы выбрали 100 первых строк из базы данных. Это своего рода «Hello world» для функции Query.
Используем фильтры и сортировку (Where, Order by)
Переходим на лист Level_2. Выберем только некоторые нужные нам поля и зададим условия фильтрации и сортировки. Например, используем данные только по кампаниям Campaign_1 и Campaign_2 за период 22-25 октября 2015 года. Отсортируем их в порядке убывания по сумме сеансов. Для фильтра и сортировки в текст запроса необходимо добавить описание кляуз Where и Order . Для вывода в результирующую таблицу описанного выше примера нам понадобятся поля Campaign, Date и Sessions. Именно их и нужно перечислить в кляузе Select .
Обращение к полям базы данных осуществляется через названия столбцов рабочего листа, на котором располагается база данных.
В нашем случае данные, расположенные на листе DB, и обращение к определенным полям прописываются как название столбцов листа. Таким образом, нужные поля располагается в следующих столбцах:
- поле Date — столбец A;
- поле Campaign — столбец B;
- поле Sessions — столбец G.
Соответственно, часть запроса, отвечающая за перечень выводимых в результате данных, будет выглядеть так:
Select A, B, G
Далее в запросе идет кляуза Where . При написании запроса кляузы обязательно должны располагаться в таком порядке, в котором были описаны в первом разделе этой статьи. После объявления Where нам необходимо перечислить условия фильтрации. В данном случае мы фильтруем данные по названию кампании (Campaign) и дате (Date). Мы используем несколько условий фильтрации. В тексте запроса между всеми условиями должен стоять логический оператор OR или AND. Фильтрация по датам немного отличается от фильтрации по числовым и текстовым значениям, для ее применения необходимо использовать оператор Date. Часть запроса, отвечающая за фильтрацию данных, будет выглядеть так:
WHERE (A >= date"2015-10-22" AND A <= date"2015-10-25") AND (B = "Campaign_1" OR B = "Campaign_2")
Мы разбили с помощью скобок фильтрацию данных на две логических части: первая фильтрует по датам, вторая — по названию кампании. На данном этапе формула, описывающая данные, которые необходимо выбрать, и условия фильтрации данных, выглядит так:
Query(DB!A1:L1143;" Select A, B, G WHERE (A >= date"2015-10-22" AND A <= date"2015-10-25") AND (B = "Campaign_1" OR B = "Campaign_2")")
Вы можете скопировать ее и вставить, например, на новый лист документа, который используется в качестве примера в этом посте, и получите следующий результат:
Помимо обычных логических операторов (=, <, >) блок WHERE поддерживает дополнительные операторы фильтрации:
- contains — проверяет содержание определённых символов в строке. Например, WHERE A contains ‘John’ вернёт в фильтр все значения из столбца A, в которых встречается John, например, John Adams, Long John Silver;
- starts with — фильтрует значения по префиксу, то есть проверяет символы в начале строки. Например, starts with ‘en’ вернёт значения engineering и english;
- ends with — фильтрует значения по окончанию строки. Например, строка ‘cowboy’ будет возвращена конструкцией «ends with ‘boy’» или «ends with ‘y’»;
- matches — соответствует регулярному выражению. Например: where matches ‘.*ia’ вернёт значения India и Nigeria.
- like — упрощённая версия регулярных выражений, проверяет соответствия строки заданному выражению с использованиям символов подстановки. На данный момент like поддерживает два символа подстановки: «%» означает любое количество любых символов в строке, и «_» — означает один любой символ. Например, «where name like ‘fre%’» будет соответствовать строкам ‘fre’, ‘fred’, и ‘freddy’.
Запрос уже отфильтровал данные за определенный период и оставил только нужные нам кампании. Остается только отсортировать результат по убыванию в зависимости от количества сеансов. Сортировка в данных запросах осуществляется традиционно для SQL с помощью кляузы Order by . По синтаксису она довольна простая: необходимо только перечислить поля, по которым требуется отсортировать результат, а также указать порядок сортировки. По умолчанию — порядок asc, то есть по возрастанию. Если укажете после название поле параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.
В нашем случае за фильтрацию будет отвечать строчка в тексте запроса:
Order by G desc
Соответственно, окончательный результат формулы на листе Level_2, решающий нужную нам задачу, выглядит так:
Query(DB!A1:L1143;" SELECT A, B, G WHERE (A >= date"2015-10-22" AND A <= date"2015-10-25") AND (B = "Campaign_1" OR B = "Campaign_2") ORDER BY G DESC")
Теперь вы умеете с помощью простейшего SQL синтаксиса и функции QUERY фильтровать и сортировать данные.
Мы научились подключаться серверу MySQL, выбирать базу данных для работы, узнали PHP-функцию отправки запросов серверу MySQL, узнали два простейших запроса (создание и удаление таблицы), ну и узнали как закрывать соединение.
Теперь мы будем более глубоко изучать запросы MySQL. Итак, приступим!
Создание таблицы - CREATE TABLE
Сейчас у нас пустая база данных, в ней нет таблиц. Поэтому сначала создадим таблицу. Мы уже знаем как это делать из первой части.
Вот код скрипта, который создаст нужную нам табличку:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))"; if (mysqli_query($link, $query)) echo "Таблица создана."; else echo "Таблица не создана: ".mysqli_error(); mysqli_close($link);
В нашей таблице только два поля: логин и пароль. Пока что нам больше не нужно, не будем усложнять процесс.
Итак, таблица создана.
Добавление строк (записей) в таблицу - INSERT
Добавить новую строку в таблицу можно при помощи SQL команды insert. Вот пример:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "INSERT INTO users (login, password) VALUE ("zeus", "pass123")"; if (mysqli_query($link, $query)) echo "Пользователь добавлен."; else echo "Пользователь не добавлен: " . mysqli_error(); mysqli_close($link);
SQL запрос состоит из команды INSERT INTO , имени базы данных users, затем в скобках идут имена полей, потом слово VALUE , после которого в скобках следуют добавляемые значения. Значения берутся в кавычки.
Синтаксис запроса выглядит так:
INSERT INTO имя_таблицы (столбец1, столбец2) VALUE ("х1", "х2")
Кавычки во вторых скобках обязательны.
На месте значений могут быть переменные. Вот пример:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $login = "zeus"; $password = "pass123"; $query = "INSERT INTO users (login, password) VALUE ("$login", "$password")"; if (mysqli_query($link, $query)) echo "Пользователь добавлен."; else echo "Пользователь не добавлен: " . mysqli_error(); mysqli_close($link);
Конечно, в этом примере мало смысла. Возможно, новичкам будет полезно услышать, что так в базу данных записываются логины и пароли, которые сообщают пользователи при регистрации. Эти данные хранятся в переменных, потом, после проверки, записываются в базу.
Существует быстрый способ вставки нескольких строк одним запросом INSERT:
INSERT INTO users (login, password) VALUE ("bob", "eee333"), ("Rooki", "12345"), ("magy", "olol88e8")
Как видим, перечисляемые данные просто отделены запятыми.
Итак, при помощи команды INSERT мы научились добавлять записи в таблицу. Идём дальше.
Просмотр таблицы: команда SELECT
Теперь у нас есть таблица users, в которой есть строки. Прошлый скрипт можно запустить несколько раз, и каждый раз он будет добавлять строку в таблицу. Теперь мы можем не знать, сколько у нас строк в таблице. А хочется знать что у нас в ней записано.
Для получения данных из таблицы используется SQL-команда SELECT . Знак * обозначает что мы запрашиваем все данные, затем после слова FROM пишем имя таблицы, из которой хотим получить данные.
Запросим все данные из таблицы users:
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "SELECT * FROM users"; $result = mysqli_query($link, $query); if (!$result) echo "Произошла ошибка: " . mysqli_error(); else echo "Данные получены"; mysqli_close($link);
Функция mysqli_query() вернула нам идентификатор результата запроса - мы его помещаем в переменную и в дальнейшем будем работать с ним при помощи других функций PHP.
Число записей в запросе
Давайте определим сколько строк в нашем запросе? Я вот запустил скрипт добавления записи в таблицу сам не помню сколько раз и теперь не знаю сколько строк в моей таблице.
Для определения числа строк в результате запроса используют функцию mysqli_num_rows() . Этой функции передаётся идентификатор результата запроса, а вернёт она число записей.
$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "SELECT * FROM users"; $result = mysqli_query($link, $query); if (!$result) echo "Произошла ошибка: " . mysqli_error(); else echo "Данные получены"; $count = mysqli_num_rows($result); echo "Всего строк в таблице: $count."; mysqli_close($link);
Если нам нужно узнать число записей в таблице, то приведённый способ не самый подходящий. Тут мы узнали число записей, найденных в запросе, но число записей в таблице ищется по другому.
Число записей в таблице SELECT COUNT(*)
Чтобы узнать число записей в таблице можно воспользоваться командой SELECT COUNT(*) FROM имя_таблицы.
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); $query = "SELECT * FROM users"; $result = mysqli_query($link, $query); if (!$result) echo "Произошла ошибка: " . mysqli_error(); else echo "Данные получены. "; $count = mysqli_fetch_row($result); echo "Всего строк в таблице: $count."; mysqli_close($link);
Обратите внимание, тут мы использовали новую функцию PHP mysqli_fetch_row() для получения данных. Эта функция возвращает ряд результата запроса в форме простого массива, в нашем случае в ряду одно поле и оно имеет индес 0.
Просмотр результата запроса в цикле
После выполнения SQL-запроса с командой SELECT и получения идентификатора результата запроса, PHP создаёт в наборе записей результата внутренний указатель. Этот указатель автоматически перемещается на следующую запись, после обращения к текущей записи. Благодаря этому механизму набор результа запроса SELECT очень удобно просматривать в цикле.
В PHP есть несколько функций, при помощи которых можно дла каждой строки результирующего запроса получить массив, состоящий из её полей. Для примера возьмём функцию mysqli_fetch_row() . Этой функции передают идентификатор запроса, а возвращает она массив. Так в цикле просматривается весь результат запроса, по достижению конца результата запроса функция вернёт false .
Итак, запрашиваем все данные из таблицы users (SELECT * FROM users).
";
while ($row = mysqli_fetch_row($result))
{
echo "Логин: $row. Пароль: $row.
";
}
mysqli_close($link);
Функция mysqli_fetch_row() возвращает простой массив. В каждой итерации цикла мы получим массив с строкой из таблицы, доступ к полям которой мы можем получить указав числовой индекс.
То же самое можно сделать используя функцию mysql_fetch_assoc() , она возвращает ассоциативный массив.
$link = mysqli_connect("localhost", "root", "");
if (!$link) die("Error");
mysqli_select_db("tester");
$result = mysqli_query($link, "SELECT * FROM users");
if (!$result) echo "Произошла ошибка: " . mysqli_error();
else echo "Данные получены.
";
while ($row = mysqli_fetch_assoc($result))
{
echo "Логин: $row. Пароль: $row.
";
}
mysqli_close($link);
Также есть функции mysqli_fetch_array() - возвращает любой тип массива, и mysqli_fetch_object() - возвращает объект.
Запрос SELECT DISTINCT - уникальные значения полей
Давайте создадим новую таблицу:
$link = mysqli_connect("localhost", "root", "");
if (!$link) die("Error");
mysqli_select_db("tester");
// удаляем существующую таблицу
mysqli_query($link, "DROP TABLE users");
// создаём новую таблицу
$query = "CREATE TABLE users(name VARCHAR(20),
surname VARCHAR(20),
age TINYINT UNSIGNED)";
if (mysqli_query($link, $query)) echo "Таблица создана.
";
else echo "Таблица не создана: " . mysqli_error();
// функция для добавления записей в таблицу
function add_new_line($link, $query)
{
if (!mysqli_query($link, $query)) echo "Пользователь не добавлен: " . mysqli_error();
}
// добавляем записи
add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Max", "Jayson", "33")");
add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Bob", "Freeman", "26")");
add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Sara", "Lopes", "65")");
add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Serg", "Pupin", "29")");
add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Serg", "Borman", "43")");
add_new_line($link, "INSERT INTO users (name, surname, age) VALUE ("Max", "Lopes", "21")");
// выводим содержание таблицы в браузер
$result = mysqli_query($link, "SELECT * FROM users");
if (!$result) echo "Произошла ошибка: " . mysqli_error();
else echo "Данные получены.
";
while ($row = mysqli_fetch_assoc($result))
{
echo "Имя: $row. Фамилия: $row. Возраст: $row.
";
}
mysqli_close($link);
Итак, мы имеем новую, более сложную таблицу с уникальными записями. Сейчас давайте посмотрим, сколько у нас имён в базе данных.
$link = mysqli_connect("localhost", "root", "");
if (!$link) die("Error");
mysqli_select_db("tester");
$result = mysqli_query($link, "SELECT DISTINCT name FROM users");
echo "Всего имён: " . mysqli_num_rows($result)."
";
echo "Список имён:
";
while ($name = mysqli_fetch_row($result))
{
echo "$name
";
}
mysqli_close($link);
SQL-запрос " SELECT DISTINCT name FROM users " вернул результат со всеми уникальными именами в нашей таблице. Каждое уникальное имя в новой строке результата запроса.
Сортировка результата - ORDER BY
Добавив в SQL-запрос команду ORDER BY мы сортируем результат запроса по возрастанию (цифры и буквы по алфавиту). Вот пример, в котором можно сравнить обычный запрос и отсортированный по возрасту (поле age).
";
}
echo "Сортируем по возрасту:
";
$result = mysqli_query($link, "SELECT * FROM users ORDER BY age");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
mysqli_close($link);
Можете заменить поле age в команде ORDER BY на поле name и посмотреть результат.
Чтобы сортировать результат запроса в обратном порядке используйте команду ORDER BY age DESC .
Соответствие условию - WHERE
Добавив в SQL-запрос команду WHERE мы запросим только те записи, которые соответствуют условию. Например, сделаем запрос на людей младше 30 лет.
Для этого используем SQL-запрос " SELECT * FROM users WHERE age
$link = mysqli_connect("localhost", "root", "");
if (!$link) die("Error");
mysqli_select_db("tester");
echo "Люди младше 30:
";
$result = mysqli_query($link, "SELECT * FROM users WHERE age<30");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
mysqli_close($link);
Также мы можем сразу отсортировать результат по возрастанию age:
" SELECT * FROM users WHERE age<30 ORDER BY age ".
Если мы сделаем запрос " SELECT name FROM users WHERE age<30 ORDER BY age ", то в результате нам вернут только значения поля "name", но они также будут отсортированы по age.
Мы можем запросить значения двух полей: " SELECT name, age FROM users WHERE age
Теперь запросим все пользователей, с именем "Max".
$link = mysqli_connect("localhost", "root", "");
if (!$link) die("Error");
mysqli_select_db("tester");
echo "Все Максы:
";
$result = mysqli_query($link, "SELECT * FROM users WHERE name="Max"");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
mysqli_close($link);
И ещё пример запроса, - выберет только имена (name) из таблицы users, все кроме Max.
SELECT name FROM users WHERE name!="Max"
На этом с запросом WHERE всё.
Ограничение записей - LIMIT
Добавив в SQL-запрос команду LIMIT мы ограничим размер результата.
Запрос, который выводит первые три записи: " SELECT * FROM users LIMIT 3 ". Давайте посмотрим как он работает:
$link = mysqli_connect("localhost", "root", "");
if (!$link) die("Error");
mysqli_select_db("tester");
echo "Содержание таблицы:
";
$result = mysqli_query($link, "SELECT * FROM users");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
echo "
Первые три записи:
";
$result = mysqli_query($link, "SELECT * FROM users LIMIT 3");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
echo "
Вторые три записи:
";
$result = mysqli_query($link, "SELECT * FROM users LIMIT 3, 3");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
mysqli_close($link);
Также тут мы использовали запрос: " SELECT * FROM users LIMIT 3, 3 ". Вторая тройка указывает смещение в результате запроса.
Соответствие шаблону - LIKE
Язык SQL поддерживает простые шаблоны. Для этого используется команда LIKE и шаблон задаётся с использованием символа % .
Вот пример запроса, который вернёт все записи с именами, начинающимися на букву S.
SELECT * FROM users WHERE name LIKE "S%"
Тестирую запрос:
$link = mysqli_connect("localhost", "root", "");
if (!$link) die("Error");
mysqli_select_db("tester");
echo "Содержание таблицы:
";
$result = mysqli_query($link, "SELECT * FROM users");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
echo "
Имена на букву S:
";
$result = mysqli_query($link, "SELECT * FROM users WHERE name LIKE "S%"");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
mysqli_close($link);
Вот пример запроса, который вернёт все записи с фамилиями, заканчивающимися на букву s.
SELECT * FROM users WHERE name LIKE "%s"
Соответствие условию - IN
Этот запрос с использованием команды IN вернёт только те строки, которые строго соответствую условию.
Например, нас интересуют люди с возрастом 21, 26 и 33 года.
SELECT * FROM users WHERE age IN (21,26,33)
Тестирую запрос:
$link = mysqli_connect("localhost", "root", "");
if (!$link) die("Error");
mysqli_select_db("tester");
echo "Содержание таблицы:
";
$result = mysqli_query($link, "SELECT * FROM users");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
echo "
Люди, с требуемыми возрастами (21, 26, 33):
";
$result = mysqli_query($link, "SELECT * FROM users WHERE age IN (21, 26, 33)");
while ($line = mysqli_fetch_row($result))
{
echo "Имя: $line. Фамилия: $line. Возраст: $line.
";
}
mysqli_close($link);
Максимальное и минимальное значение в столбце
Выбирает максимальное значение age в таблице users.
SELECT max(age) FROM users
Следующий запрос выбирает данные из таблицы users по полям name и age где age принимает минимальное значение.
SELECT name, min(age) FROM users
Обновление записи - UPDATE
Давайте Max Lopes установим возраст 15 лет. Это делается запросом MySQL:
UPDATE users SET age="15" WHERE name="Max" AND surname="Lopes"
Обратите внимание на новую команду AND (and - по английски значит "и") в запросе. Если мы не уточним фамилию, то возраст 15 лет будет установлен всем Максам в таблице.
Одним запросом можно обновить два и более полей в одной строке. Делается это следующим образом:
UPDATE users SET age = "18", surname = "Coocker" WHERE id = "3"
В нашей таблице нет поля id, так что этот запрос не сработает на ней. Но это поле, содержащее уникальные номера строк, мы обязательно выучим.
Удалить запись - DELETE
Запрос к базе данных MySQL для удаления записи:
DELETE FROM users WHERE id = "10"
Опять же, в нашей таблице нет поля id. Но мы можем удалить из неё всех людей, младше 18 лет.
DELETE FROM users WHERE age < "18"
Удалить таблицу - DROP TABLE
Запрос к базе данных MySQL который удаляет целиком таблицу users:
DROP TABLE users
Удалить столбец - ALTER TABLE ... DROP ...
Иногда может потребоваться удалить столбец из таблицы, давайте например удалим из users столбец age:
ALTER TABLE users DROP age
Этот запрос MySQL удалил столбец окончательно и безвозвратно.
Добавить столбец - ALTER TABLE ... ADD ...
Иногда может потребоваться добавить столбец в существующую таблицу, давайте например снова добавим в таблицу users столбец age:
ALTER TABLE users ADD age TINYINT UNSIGNED
Переименование столбца - ALTER TABLE ... CHANGE ...
Иногда может потребоваться переименовать столбец, например столбец age переименовать в vozrast. Делаем это так:
ALTER TABLE users CHANGE age vozrast TINYINT UNSIGNED
Этот запрос MySQL переименовал столбец age в vozrast с типом данных TINYINT UNSIGNED .
Переименование таблицы - RENAME TABLE ... TO ...
Иногда может потребоваться переименовать таблицу:
RENAME TABLE users TO peoples
Удаление базы данных - DROP DATABASE
Этот запрос может удалить базу данных с имененм tester:
DROP DATABASE tester
Создание базы данных - CREATE DATABASE
Этот запрос создаёт базу данных с имененм tester:
CREATE DATABASE tester
У меня на денвере этот запрос работает, но на хостинге может и не сработать, если у пользователя базы данных нет прав на выполнение удаления.
Итоги
Итак, в этой части мы ознакомились с запросами к MySQL. Многие из рассмотренных нами запросов нам не часто пригодятся в процессе работы, но знать их нужно, так как они точно пригодятся в процессе разработки скриптов.
Некоторые запросы обычно делают только из phpMyAdmin (создание и удаление баз данных например).
В работе сайтов обычно нужно добавить запись в таблицу, редактировать запись или удалить запись из таблицы.
Следующим шагом будет изучение типов данных в MySQL.
resource mysql_query (string query [, resource link_identifier])mysql_query() посылает запрос активной базе данных сервера, на который ссылается переданный указатель. Если параметр link_identifier опущен, используется последнее открытое соединение. Если открытые соединения отсутствуют, функция пытается соединиться с СУБД, аналогично функции mysql_connect() без параметров. Результат запроса буфферизируется.
Замечание: Строка запроса НЕ должна заканчиваться точкой с запятой.
Только для запросов SELECT, SHOW, EXPLAIN, DESCRIBE,
mysql_query()
возвращает указатель на результат
запроса, или FALSE
если запрос не был выполнен.
В остальных случаях (INSERT, UPDATE, DELETE, DROP, и т.п.),
mysql_query()
возвращает TRUE
в случае успешного
запроса и FALSE
в случае ошибки. Значение не равное FALSE
говорит о
том, что запрос был выполнен успешно. Он не говорит о количестве
затронутых или возвращённых рядов. Вполне возможна ситуация, когда
успешный запрос не затронет ни одного ряда.
Следующий запрос составлен неправильно и mysql_query() вернёт FALSE :
mysql_query() также считается ошибочным и вернёт FALSE , если у вас не хватает прав на работу с указанной в запросе таблицей.
Работая с результатами запросов, вы можете использовать функцию mysql_num_rows() , чтобы найти число, возвращённых запросом SELECT, рядов, или mysql_affected_rows() , чтобы найти число рядов, обработанных запросами DELETE, INSERT, REPLACE, или UPDATE.
Только для запросов SELECT, SHOW, DESCRIBE, EXPLAIN, функция mysql_query() возвращает указатель на результат, который можно использовать в функции mysql_fetch_array() и других функциях, работающих с результатами запросов. Когда работа с результатом окончена, вы можете освободить ресурсы, используемые для его хранения, с помощью функции