Запросы в mysql из php mysqli query. Документы и Диск Google: Функция QUERY

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

Спасибо Евгению Намоконову за помощь в подготовке материала.

Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.

Синтаксис функции:

=QUERY(данные; запрос; [заголовки])

данные - это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
заголовки - количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.

Итак, правила формирования запросов:

Запрос указывается в кавычках.
В запросе используются ключевые слова:
1. SELECT - определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
2. WHERE - ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
3. GROUP BY - группирует значения по заданным полям.
4. PIVOT - позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
5. ORDER BY - задает сортировку. Например: «ORDER BY C DESC» - сортировка по столбцу C по убыванию.
6. LIMIT - ограничивает количество возвращаемых строк. Например: «LIMIT 50».
7. OFFSET - пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
8. FORMAT - определяет формат определенных столбцов по заданному шаблону.
9. 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 Таблицам.

Думаю, все слышали о правиле Парето. В любой сфере 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 фильтровать и сортировать данные.

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() и других функциях, работающих с результатами запросов. Когда работа с результатом окончена, вы можете освободить ресурсы, используемые для его хранения, с помощью функции

В этой статье мы научимся отправлять запросы к базе данных через PHP . Данная статья очень важная, и её необходимо Вам обязательно понять. Однако, успокою Вас - материал очень простой, поэтому затруднений быть не должно.

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

Теперь переходим к отправке запросов к базе данных в PHP :

}
$mysqli->query("INSERT INTO mytable (name, email) VALUES ("MyName", "[email protected]")");
$mysqli->close();
?>

В данном примере мы подключились к базе данных, проверили успешность подключения, отправили запрос с помощью метода query() , а затем закрыли подключение. Как видите, всё очень просто. Для отправки любых SQL-запросов достаточно всего одного метода - query() , поэтому в данном случае всё безумно просто.

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

Для начала немного поясню, что из себя представляет result_set . Result_set - это таблица с результатом. У этой таблицы есть набор записей (строки таблицы). И для вывода всех записей надо перебрать каждую строку таблицы и вывести её. А теперь поясняю пример: после отправки запроса у нас формируется result_set . Затем мы в цикле присваиваем переменной row значение очередной строки, то есть одномерный массив, который возвращает метод fetch_assoc() . Когда все строки закончатся, метод fetch_assoc() вернёт false , и будет произведён выход из цикла. Внутри цикла while мы просто выводим массив с использованием отладочной функции print_r() , хотя, безусловно, можно было бы вывести с использованием foreach , но сейчас это не нужно.

Давайте кратко подведём итог, как нужно работать с result_set :

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

Как Вы заметили, метод fetch_assoc() возвращает всегда следующую запись. То есть сначала 1-ю, потом 2-ю, потом 3-ю и так далее. Если у Вас есть неплохой опыт программирования, то Вы сразу догадаетесь, что это происходит благодаря внутреннему указателю, который Вы, разумеется, можете двигать. Где это используется? Например, это может использоваться, когда Вам необходимо поработать с result_set не 1 , а 2 и более раз. Чтобы заново не формировать тот же запрос, можно просто передвинуть указатель в начало. И тогда можно снова заниматься перебором result_set с помощью метода fetch_assoc().

Для изменения положения указателя существует метод data_seek() , который принимает целое число от 0 до "количества записей - 1 ", соответственно указатель встаёт на соответствующую параметру запись:

$mysqli = @new mysqli("localhost", "Admin", "pass", "mybase");
if (mysqli_connect_errno()) {
echo "Подключение невозможно: ".mysqli_connect_error();
}
$result_set = $mysqli->query("SELECT * FROM mytable");
$result_set->num_rows;
while ($row = $result_set->fetch_assoc()) {
print_r($row);
echo "
";
}
$result_set->data_seek(0);
while ($row = $result_set->fetch_assoc()) {
print_r($row);
echo "
";
}
$result_set->close();
$mysqli->close();
?>

В этом примере мы вывели количество записей в result_set с помощью свойства num_rows . А также познакомились с методом data_seek() . То есть мы перебрали весь результат, затем вернули указатель на 0-ю запись и вновь перебрали результат.

В этой статье мы с Вами повторили подключение к базе данных и закрытие подключения. А также узнали, как отправлять запросы к базе данных через PHP . Узнали, как получать result_set и как работать с ним. Это всё, что Вам необходимо знать для успешной работы с MySQL в PHP .

Мы научились подключаться серверу 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() . Этой функции передаётся идентификатор результата запроса, а вернёт она число записей.

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

Число записей в таблице 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.