MySQL запросы — как единственно верный способ вывода данных


Оглавление (нажмите, чтобы открыть):

Примеры SQL запросов к базе данных MySQL

Содержание статьи
1. Самые простые MySQL запросы
2. Простые SELECT (выбрать) запросы
3. Простые INSERT (новая запись) запросы
4. Простые UPDATE (перезаписать, дописать) запросы
5. Простые DELETE (удалить запись) запросы
6. Простые DROP (удалить таблицу) запросы
7. Сложные MySQL запросы
8. MySQL запросы и переменные PHP

1. Самые простые SQL запросы

1. Выведет список ВСЕХ баз.

2. Выведет список ВСЕХ таблиц в Базе Данных base_name.

2. Простые SELECT (выбрать) запросы к базе данных MySQL

SELECT – запрос, который выбирает уже существующие данные из БД. Для выбора можно указывать определённые параметры выбора. Например, суть запроса русским языком звучит так — ВЫБРАТЬ такие-то колонки ИЗ такой-то таблицы ГДЕ параметр такой-то колонки равен значению.

1. Выбирает ВСЕ данные в таблице tbl_name.

2. Выведет количество записей в таблице tbl_name.

3. Выбирает (SELECT) из(FROM) таблицы tbl_name лимит (LIMIT) 3 записи, начиная с 2.

4. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id по порядку.

5. Выбирает (SELECT) ВСЕ записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id в ОБРАТНОМ порядке.

6. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы users и сортирует их (ORDER BY) по полю id в порядке возрастания, лимит (LIMIT) первые 5 записей.

7. Выбирает все записи из таблицы users, где поле fname соответствует значению Gena.

8. Выбирает все записи из таблицы users, где значение поля fname начинается с Ge.

9. Выбирает все записи из таблицы users, где fname заканчивается на na, и упорядочивает записи в порядке возрастания значения id.

10. Выбирает все данные из колонок fname, lname из таблице users.

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

11. Допустим у Вас в таблице пользовательских данных есть страна. Так вот если Вы хотите вывести ТОЛЬКО список встречающихся значений (чтобы, например, Россия не выводилось 20 раз, а только один), то используем DISTINCT. Выведет, из массы повторяющихся значений Россия, Украина, Беларусь. Таким образом, из таблицы users колонки country будут выведены ВСЕ УНИКАЛЬНЫЕ значения

12. Выбирает ВСЕ данные строк из таблицы users где age имеет значения 18,19 и 21.

13. Выбирает МАКСИМАЛЬНОЕ значение age в таблице users. То есть если у Вас в таблице самое большее значение age(с англ. возраст) равно 55, то результатом запроса будет 55.

14. Выберет данные из таблицы users по полям name и age ГДЕ age принимает самое маленькое значение.

15. Выберет данные из таблицы users по полю name ГДЕ id НЕ РАВЕН 2.

3. Простые INSERT (новая запись) запросы

INSERT – запрос, который позволяет ПЕРВОНАЧАЛЬНО вставить запись в БД. То есть создаёт НОВУЮ запись (строчку) в БД.

1. Делает новую запись в таблице users, в поле name вставляет Сергей, а в поле age вставляет 25. Таким образом, в таблицу дописывается новая строки с данными значениями. Если колонок больше, то они оставшиеся останутся либо пустыми, либо с установленными по умолчанию значениями.

4. Простые UPDATE запросы к базе данных MySQL

UPDATE – запрос, который позволяет ПЕРЕЗАПИСАТЬ значения полей или ДОПИСАТЬ что-то в уже существующей строке в БД. Например, есть готовая строка, но в ней нужно перезаписать параметр возраста, так как он изменился со временем.

1. В таблице users ГДЕ id равно 3 значение поля age становится 18.

2. Всё то же самое, что и в первом запросе, просто показан синтаксис запроса, где перезаписываются два поля и более.
В таблице users ГДЕ id равно 3 значение поля age становится 18, а country Россия.

5. Простые DELETE (удалить запись) запросы к базе данных MySQL

DELETE – запрос, который удаляет строку из таблицы.

1. Удаляет строку из таблицы users ГДЕ id равен 10.

6. Простые DROP (удалить таблицу) запросы к базе данных MySQL

DROP – запрос, который удаляет таблицу.

1. Удаляет целиком таблицу tbl_name.

7. Сложные запросы к базе данных MySQL

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

Данный сложный запрос ВЫБИРАЕТ колонки id,name,country В ТАБЛИЦАХ users,admins ГДЕ registration_date (дата) не старше 14 дней И activation НЕ РАВНО , СОРТИРОВАТЬ по registration_date в обратном порядке (новое в начале).

Выше указан пример так называемого запроса в запросе в SQL. Обновить возраст среди пользователей на 18+, где пол — мужской. Подобные варианты запроса не рекомендую. По личному опыту скажу, лучше создать несколько отдельных — они будут прорабатываться быстрее.

8. Запросы к базе данных MySQL и PHP

В MySQL запросы в PHP странице можно вставлять переменные в качестве сравниваемых и тп значений. Пара примеров

1. Выбирает все записи из таблицы users, где поле fname соответствует значению переменной $name.

2. В таблице users ГДЕ id равно 3 значение поля age изменяется на значение переменной $age.

Внимание! Если Вам интересен какой-либо ещё пример, то пишите вопрос в комментарии!

Вывод данных из MySql на PHP

Создать соединение(connect) с базой данных MySql

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

Извлечь данные из таблицы MySql

После того, как мы установили соединение с БД, c помощью запроса можно получить данные из любой таблицы. А с помощью цикла while их вывести.

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

mysqli_connect

mysqli_connect( , , , ) — устанавливает соединение с базой данных.

mysqli_query

mysqli_query( , ) — выполняет запрос к БД, написанный на языке SQL.

mysqli_fetch_array

mysqli_fetch_array( ) — поочередно возвращает по одной строке из результата запроса.

Вывод переменной врутри строки


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

Понравилась или помогла статья? Самое лучшее, что ты можешь сделать — это поделиться ею в любой из своих соцсетей (даже если ты поделишься в твиттере или google+, которыми ты не пользуешься — это очень поможет развитию моего блога). Спасибо! А если ты еще и оставишь любой комментарий снизу в обсуждениях, то это будет двойное СПАСИБО!

20 советов по оптимальному использованию MySQL

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

1. Оптимизируйте ваши запросы для кэша запросов.

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

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

Причина того, что кэш запросов не работает в первом случае, заключается в использовании функции CURDATE(). Такой подход используется для всех недетерминированных функций, например, NOW(), RAND() и т.д. Так как возвращаемый результат функции может измениться, то MySQL решает не размещать данный запрос в кэше. Все что, нужно, чтобы исправить ситуацию — это добавить дополнительную строчку кода PHP перед запросом.

2. Используйте EXPLAIN для ваших запросов SELECT

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

Результат запроса EXPLAIN показывает, какие индексы используются, как таблица сканируется и сортируется, и так далее.

Возьмем запрос SELECT (предпочтительно, чтобы он был сложным, с JOIN), добавим перед ним ключевое слово EXPLAIN. Вы можете использовать PhpMyAdmin для этого. Такой запрос выведет результат в прекрасную таблицу. Допустим, мы забыли добавить индекс для столбца, который используется для JOIN:

После добавления индекса для поля group_id:

Теперь вместо сканирования 7883 строк, будут сканироваться только 9 и 16 строк из двух таблиц. Хорошим методом оценки производительности является умножение всех чисел в столбце “rows”. Результат примерно пропорционален прорабатываемому объему данных.

3. Используйте LIMIT 1, если нужно получить уникальную строку

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

В таком случае добавление LIMIT 1 к вашему запросу может улучшить производительность. При таком условии механизм базы данных останавливает сканирование записей как только найдет одну и не будет проходит по всей таблице или индексу.

4. Индексируйте поля поиска

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

Как вы можете видеть, данное правило применимо и к поиску по части строки, например, “last_name LIKE ‘a%’”. Когда для поиска используется начало строки, MySQL может использовать индекс столбца, по которому проводится поиск.

Вам также следует разобраться, для каких видов поиска нельзя использовать обычное индексирование. Например, при поиске слова ( “WHERE post_content LIKE ‘%apple%’”) преимущества индексирования будут не доступны. В таких случая лучше использовать полнотекстовый поиск mysql или построение собственных решений на основе индексирования.

5. Индексирование и использование одинаковых типов для связываемых столбцов

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

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

6. Не используйте ORDER BY RAND()

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

Если вам действительно нужно случайным образом располагать строки в результате вашего запроса, то существует множество лучших способов решить такую задачу. Конечно, это будет реализовано дополнительным кодом, но вы будете спасены от проблемы, которая растет по экспоненциальному закону вместе с ростом объема данных. Дело в том, что MySQL выполняет операцию RAND() (которая занимает время процессора) для каждой отдельной строки в таблице перед тем, как отсортировать ее и выдать вам только одну строку.

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

7. Старайтесь не использовать SELECT *

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

Хорошей привычкой является указание столбца при выполнении SELECT.

8. Старайтесь использовать поле id везде

Хорошей практикой является использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно — UNSIGNED, так как в этом случае значение не может быть отрицательным.

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

Мастер Йода рекомендует:  10 полезных утилит Linux для анализа и мониторинга системы

Кроме того механизм MySQL использует основные ключи для своих внутренних задач, и использование поля id создает оптимальные условия для их решения.

Одним возможным исключением из данного правила являются “ассоциативные таблицы”, которые используются для отношений многие-ко-многим между двумя другими таблицами. Например, таблица “posts_tags” содержит 2 столбца: post_id, tag_id. Они используются для описания отношений между двумя таблицами “post” и “tags”. Описанная таблица может иметь основной ключ, который содержит оба поля id.

9. Используйте ENUM вместо VARCHAR

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

Если у вас есть поля, которые содержат только несколько различных видов значений, используйте для них ENUM вместо VARCHAR. Например, может быть столбец с именем “status”, который будет содержать только такие значения как “active”, “inactive”, “pending”, “expired” и так далее.

MySQL может “предложить” способ изменения структуры вашей таблицы. Когда вы создаете поле VARCHAR, то наверняка «предложение» будет содержать рекомендацию сменить тип столбца на ENUM. «Предложения» получаются в ходе выполнения вызова PROCEDURE ANALYSE().

10. Изучите предложения PROCEDURE ANALYSE()

PROCEDURE ANALYSE() позволяет MySQL анализировать структуру столбцов и действительных данных в вашей таблице и на основании анализа выдавать «предложения». Это действует только если в вашей таблице есть реальные данные, так как их наличие играет существенную роль при принятии решений.

Например, если вы создали поле типа INT для основного ключа, но в таблице не так много записей, то «предложение» может содержать рекомендацию сменить тип поля на MEDIUMINT. Или если вы используете поле типа VARCHAR, то можете получить «предложение» конвертировать его в ENUM, если в нем содержится только несколько значений.

Вы также можете получить рекомендации, если нажмете ссылку “Propose table structure” (Анализ структуры таблицы) в PhpMyAdmin на закладке структуры таблицы.

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

11. Используйте NOT NULL, если это возможно

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

Спросите себя, есть ли разница между пустой строкой и значением NULL (для полей типа INT: 0 и NULL). Если нет причин использовать оба значения, то нет необходимости иметь поле NULL. (Вы знаете, что Oracle рассматривает NULL и пустую строку как одинаковые величины?)

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

Из документации MySQL:

“Столбец NULL требует дополнительного пространства в строке для записи о возможном значении NULL. Для таблиц MyISAM каждый столбец NULL использует дополнительный бит, округление проводится до ближайшего байта.”

12. Подготовленные выражения

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

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

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

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

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

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.


13. Небуферированные запросы

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

Отличное объяснение функции mysql_unbuffered_query() из документации PHP:

“mysql_unbuffered_query() отправляет SQL запрос на сервер MySQL без автоматического получения и буферирования строк результата, как это делает функция mysql_query(). Таким образом, сохраняется определенный объем памяти запросами SQL, которые выдают большой набор результата, и можно начинать работать с набором результата сразу же после получения первой строки, не дожидаясь пока запрос SQL будет полностью выполнен.”

Однако существует несколько ограничений. Вы должны либо прочитать все строки либо вызвать mysql_free_result() перед тем, как выполнить следующий запрос. Также нельзя использовать mysql_num_rows() или mysql_data_seek() для набора результата.

14. Храните IP адрес как UNSIGNED INT

Многие программисты создают поле VARCHAR(15) для хранения IP адреса, даже не задумываясь о том, что будут хранить в этом поле целочисленное значение. Если использовать INT, то размер поля сократится до 4 байт, и оно будет иметь фиксированную длину.

Нужно использовать тип UNSIGNED INT, так как IP адрес задействует все 32 бита беззнакового целого.

В запросах можно использовать функцию INET_ATON() для конвертации IP адреса в целое, и INET_NTOA() для обратного процесса. Также есть схожие функции PHP: ip2long() и long2ip().

15. Таблицы с фиксированной длиной записи (Static) работают быстрее

Когда каждый отдельный столбец в таблице имеет фиксированную длину, то вся таблица в целом рассматривается как “static” или “с фиксированной длиной записи”. Примеры типов столбцов, которые не имеют фиксированной длины: VARCHAR, TEXT, BLOB. Если вы включите хотя бы один столбец с таким типом, то таблица перестает рассматриваться как «static» и будет по-другому обрабатываться механизмом MySQL.

Таблицы «static» быстрее обрабатываются механизмом MySQL при поиске записей. Когда нужно прочитать определенную запись в таблице, то ее положение быстро вычисляется. Если размер строки не фиксирован, то для определения положения записи нужно время на поиск и сопоставление с индексом основного ключа.

Такие таблицы также проще кэшировать и проще восстанавливать при сбоях. Но они могут занимать больше места. Например, если конвертировать поле VARCHAR(20) в поле CHAR(20), то всегда будут заняты 20 байт вне зависимости от того, используются они или нет.

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

16. Вертикальное разделение

Вертикальное разделение — это действие по разделению структуры таблицы по вертикали с целью оптимизации.

Пример 1: У вас есть таблица, которая содержит домашние адреса, редко используемые в приложении. Вы можете разделить вашу таблицу и хранить адреса в отдельной таблице. Таким образом основная таблица пользователей сократится в размере. А как известно, меньшая таблица обрабатывается быстрее.

Пример 2: У вас в таблице есть поле “last_login”. Оно обновляется каждый раз, когда пользователь регистрируется на сайте. Но каждое обновление таблицы вызывает кэширование запроса, что может создать перегрузку системы. Вы можете выделить данное поле в другую таблицу, чтобы сделать обновления таблицы пользователей не такими частыми.

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

17. Разделяйте большие запросы DELETE или INSERT

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

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

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

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

18. Маленькие столбцы обрабатываются быстрее

Для механизма базы данных диск является наиболее важным узким местом. Стремление сделать все более компактным и маленьким обычно хорошо сказывается в сфере производительности за счет сокращения объема перемещаемых данных.

Документация MySQL содержит список норм хранения данных для всех типов.

Если таблица будет содержать всего несколько строк, то нет причин делать основной ключ типа INT, а не MEDIUMINT, SMALLINT или даже TINYINT. если вам нужна только дата, используйте DATE вместо DATETIME.

Нужно только помнить о возможностях роста.

19. Выбирайте правильный механизм хранения данных

Есть два основных механизма хранения данных для MySQL: MyISAM и InnoDB. Каждый имеет свои достоинства и недостатки.

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

InnoDB является более сложным механизмом хранения данных, и он может быть более медленным, чем MyISAM для большинства маленьких приложений. Но он поддерживает блокирование строк, что лучше для масштабирования таблиц. Также он поддерживает некоторые дополнительные особенности, такие как транзакции.

20. Используйте объектно-реляционное отображение

Использование объектно-реляционного отображения (ORM — Object Relational Mapper) дает ряд преимуществ. Все, что можно сделать в ORM , можно сделать вручную, но с большими усилиями и более высокими требованиями к уровню разработчика.

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

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

Для PHP можно использовать ORM Doctrine.

21. Будьте осторожны с постоянными соединениями

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

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

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

Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/
Перевел: Сергей Фастунов
Урок создан: 27 Августа 2010
Просмотров: 193481
Правила перепечатки

5 последних уроков рубрики «Разное»

Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов

Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

Разработка веб-сайтов с помощью онлайн платформы Wrike

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

20 ресурсов для прототипирования

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

Топ 10 бесплатных хостингов

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

Быстрая заметка: массовый UPDATE в MySQL

Ни для кого не секрет как в MySQL реализовать массовый INSERT, а вот с UPDATE-ом могут возникнуть сложности. Чтобы не прибегать к манипуляциям события ON_DUPLICATE можно воспользоваться специальной конструкцией CASE … WHEN … THEN.

Полный вывод запроса mysql

Пытаюсь вывести все поля из запроса select в строчку, без пробелов, так сказать одним массивом. Смотрел всякие гайды, и там обычно в строке $res = $result[. ]; идет указатель на конкретное поле. Как вывести запрос целиком?

2 ответа 2

print_r() выводит информацию о переменной в удобочитаемом виде.


Это позволит несколько полей объединить в строку.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками php mysql mysqli или задайте свой вопрос.

Похожие

Подписаться на ленту

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

дизайн сайта / логотип © 2020 Stack Exchange Inc; пользовательское содержимое попадает под действие лицензии cc by-sa 4.0 с указанием ссылки на источник. rev 2020.11.7.35374

Запросы к базе данных и команда Select

Запросы к базе данных и команда Select.

Я не ставлю целью углубляться в изучение языка SQL,об этом вы можете прочитать в любом руководстве по SQL Server,MySQL в основном поддерживает все основные команды стандарта ANSI 92,но команда Select заслуживает того,чтобы посвятить ей отдельную главу. Команда Select используется для запросов к базе данных с целью извлечения из нее информации.Синтаксис команды следующий:

SELECT [STRAIGHT_JOIN] [DISTINCT | ALL] select_expression.
[FROM tables. [WHERE where_definition] [GROUP BY column. ]
[ORDER BY column [ASC | DESC], . ] HAVING full_where_definition
[LIMIT [offset,] rows] [PROCEDURE procedure_name]]
[INTO OUTFILE ‘file_name’. ]

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

DISTINCT..
Пропускает строки,в которых все выбранные поля идентичны,то есть устраняет дублирование данных.


WHERE.

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

Выводит колонки u_id и lname из таблицы publishers для которых значение в столбце city-New York.Это дает возможность сделать запрос более конкретным.

Реляционные операторы.
Реляционный оператор — математический символ который указывает на определенный тип сравнения между двумя значениями. Реляционные операторы которыми располагает MySQL :

= Равнo
> Больше
= Больше или равно
Не равно
Эти операторы имеют стандартные значения для числовых значений.

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

SELECT * FROM Customers WHERE rating > 200;

Булевы операторы.
Основные Булевы операторы также распознаются в MySQL. Выражения Буля — являются или верными или неверными, подобно предикатам. Булевы операторы связывают одно или более верных/неверных значений и производят единственное верное или неверное значение. Стандартными операторами Буля распознаваемыми в SQL являются:AND,OR и NOT.

Предположим вы хотите видеть всех заказчиков в Далласе,которые имеют рейтинг выше 200:

SELECT * FROM Customers WHERE city = ‘Dallas’ AND rating > 200;

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

При использовании оператора OR,должно выполниться одно из условий.Например:

SELECT * FROM Customers WHERE city = ‘Dallas ‘ OR rating > 200;

В данном случае будут выбраны все заказчики из Далласа и все имеющие рейтинг больше 200,даже если они и не из Далласа.

NOT может использоваться для инвертирования значений Буля.Пример запроса с NOT:

SELECT * FROM Customers WHERE city = ‘Dallas’ OR NOT rating > 200;

При таком запросе будут выбраны все заказчики из Далласа и все заказчики,рейтинг которых меньше 200.В этом запросе оператор NOT применяется только к выражению rating >200.Можно сделать более сложный запрос:

Мастер Йода рекомендует:  GET-запросы в PHP взаимодействуем с пользователем

SELECT * FROM Customers WHERE NOT( city = ‘Dallas’ OR rating > 200 );

В этом запросе NOT применен к обеим выражениям в скобках.В данном случае,сервер читает выражения в скобках,определяет, соответствует ли истине равенство city = ‘Dallas’ или равенство rating > 200.Если любое условие верно, выражение Буля внутри круглых скобок верно. Однако, если выражение Буля внутри круглых скобок верно,предикат как единое целое неверен, потому что NOT преобразует верно в неверно и наоборот.То есть,будут выбраны все заказчики не находящиеся в Далласе и рейтинг которых меньше 200.

IN.
Оператор IN определяет набор значений в которое данное значение может или не может быть включено.Например,запрос

SELECT * FROM Salespeople WHERE city = ‘Barcelona’ OR city = ‘London’;

может быть переписан более просто:

SELECT * FROM Salespeople WHERE city IN ( ‘Barcelona’, ‘London’ );

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


BETWEEN.

Оператор BETWEEN похож на оператор IN. В отличии от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться что делает предикат верным. Вы должны ввести ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к порядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку.Например:

SELECT * FROM Salespeople WHERE comm BETWEEN .10 AND .12;
SELECT * FROM Salespeople WHERE city BETWEEN ‘Berlin’ AND ‘London’;

LIKE.
LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется чтобы находить подстроки. Т.е. он ищет поле символа чтобы видеть, совпадает ли с условием часть его строки.В качестве условия он использует групповые символы(wildkards) — специальные символы которые могут соответствовать чему-нибудь. Имеются два типа групповых символов используемых с LIKE:

символ подчеркивания ( _ ) замещает любой одиночный символ.

знак ‘%’,замещающий любое количество символов.
Если мы зададим следующие условия:

SELECT * FROM Customers WHERE fname LIKE ‘J%’;

то будут выбраны все заказчики,чьи имена начинаются на J:John,Jerry,James и т.д.

COUNT.
Агрегатная функция,производит подсчет значений в столбце или числа строк в таблице.При работе со столбцом использует DISTINCT в качестве аргумента:

SELECT COUNT ( DISTINCT snum ) FROM Orders;
При подсчете строк имеет синтаксис:

SELECT COUNT (*) FROM Customers;

GROUP BY.
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, предположим что вы хотите найти наибольшую сумму приобретений полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX () из таблицы для каждого значения поля. GROUP BY позволит Вам поместить их все в одну команду:

SELECT snum, MAX (amt) FROM Orders GROUP BY snum;

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

SELECT cid,cname,price,max(price) //max()-это тоже агрегатная функция
FROM customers HAVING max(price)>500;
HAVING действует сходно с WHERE,но с WHERE нельзя использовать агрегатные функции.

ORDER BY.
Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого,также как с GROUP BY.

EXISTS.
Используется в подзапросах.

SELECT cnum, cname, city FROM Customers WHERE EXISTS
(SELECT * FROM Customers WHERE city = » San Jose’ );
Он берет подзапрос как аргумент и оценивает его как верный если тот производит любой вывод или как неверный если тот не делает этого.Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятся в San Jose.

UNION.
UNION отличается от подзапросов тем что в нем ни один из двух ( или больше ) запросов не управляются другим запросом. Все запросы выполняются независимо друг от друга, а уже вывод их — объединяется.Например:

SELECT snum, sname FROM Salespeople WHERE city = ‘London’ UNION
SELECT cnum, cname FROM Customers
WHERE city = ‘London’;
Предложение UNION объединяет вывод двух или более SQL запросов в единый набор строк и столбцов.

DESC,ASC.
DESC-DESCEDENT,вывод данных в обратном порядке(по алфавиту и численным значениям).По умолчанию используется ASC.

Ну вот вкратце и все.MySQL поддерживает почти все основные команды SQL Server,так что более подробно о команде SELECT вы можете прочитать в любом учебнике по языку SQL.

Вывод данных из mysql в php


Рассмотрим простой пример подключения и вывода данных из базы mysql в php.

Для начала подключимся к базе данных.

Установим кодировку, чтобы исключить появление «кракозябр» при выводе данных.

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

Теперь нужно вывести наши значения в нужном формате, рассмотрим два способа.

С помощью цикла «while-do»

Мы получаем строки таблиц с помощью функции «mysql_fetch_array», которая выводит одну строку. Она работает, как счётчик, при каждом цикле выводит следующею строку, до тех пор, пока они не кончатся в нашей таблице.

С помощью функции «printf»

Функция использует метки, в нашем случае это «%s» — в них подставляются значения в последовательности, которую мы задаём, то есть:

То же самое, что и эта запись:

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

PHP и MySQL

Запросы MySQL с примерами: часть 2.

Введение

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

Теперь мы будем более глубоко изучать запросы MySQL. Итак, приступим!

Создание таблицы — CREATE TABLE

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

Вот код скрипта, который создаст нужную нам табличку:

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

Итак, таблица создана.

Добавление строк (записей) в таблицу — INSERT

Добавить новую строку в таблицу можно при помощи SQL команды insert. Вот пример:

SQL запрос состоит из команды INSERT INTO , имени базы данных users, затем в скобках идут имена полей, потом слово VALUE , после которого в скобках следуют добавляемые значения. Значения берутся в кавычки.

Синтаксис запроса выглядит так:

INSERT INTO имя_таблицы (столбец1, столбец2) VALUE (‘х1’, ‘х2’)

Кавычки во вторых скобках обязательны.

На месте значений могут быть переменные. Вот пример:

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

Существует быстрый способ вставки нескольких строк одним запросом INSERT :

Как видим, перечисляемые данные просто отделены запятыми.

Итак, при помощи команды INSERT мы научились добавлять записи в таблицу. Идём дальше.

Просмотр таблицы: команда SELECT

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

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

Запросим все данные из таблицы users:

Функция mysqli_query() вернула нам — мы его помещаем в переменную и в дальнейшем будем работать с ним при помощи других функций PHP.

Число записей в запросе

Давайте определим сколько строк в нашем запросе? Я вот запустил скрипт добавления записи в таблицу сам не помню сколько раз и теперь не знаю сколько строк в моей таблице.

Для определения числа строк в результате запроса используют функцию mysqli_num_rows() . Этой функции передаётся идентификатор результата запроса, а вернёт она число записей.

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

Число записей в таблице SELECT COUNT(*)

Чтобы узнать число записей в таблице можно воспользоваться командой SELECT COUNT(*) FROM имя_таблицы .

Обратите внимание, тут мы использовали новую функцию PHP mysqli_fetch_row() для получения данных. Эта функция возвращает ряд результата запроса в форме простого массива, в нашем случае в ряду одно поле и оно имеет индес 0.

Просмотр результата запроса в цикле

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

В PHP есть несколько функций, при помощи которых можно дла каждой строки результирующего запроса получить массив, состоящий из её полей. Для примера возьмём функцию mysqli_fetch_row() . Этой функции передают идентификатор запроса, а возвращает она массив. Так в цикле просматривается весь результат запроса, по достижению конца результата запроса функция вернёт false .

Итак, запрашиваем все данные из таблицы users ( SELECT * FROM users ).

Функция mysqli_fetch_row() возвращает простой массив. В каждой итерации цикла мы получим массив с строкой из таблицы, доступ к полям которой мы можем получить указав числовой индекс.

То же самое можно сделать используя функцию mysql_fetch_assoc() , она возвращает ассоциативный массив.

Также есть функции mysqli_fetch_array() — возвращает любой тип массива, и mysqli_fetch_object() — возвращает объект.

Запрос SELECT DISTINCT — уникальные значения полей

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

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

SQL-запрос » SELECT DISTINCT name FROM users » вернул результат со всеми уникальными именами в нашей таблице. Каждое уникальное имя в новой строке результата запроса.

Сортировка результата — ORDER BY

Добавив в SQL-запрос команду ORDER BY мы сортируем результат запроса по возрастанию (цифры и буквы по алфавиту). Вот пример, в котором можно сравнить обычный запрос и отсортированный по возрасту (поле age).

Можете заменить поле age в команде ORDER BY на поле name и посмотреть результат.


Чтобы сортировать результат запроса в обратном порядке используйте команду ORDER BY age DESC .

Соответствие условию — WHERE

Добавив в SQL-запрос команду WHERE мы запросим только те записи, которые соответствуют условию. Например, сделаем запрос на людей младше 30 лет.

Для этого используем SQL-запрос » SELECT * FROM users WHERE age «.

Также мы можем сразу отсортировать результат по возрастанию age:
» SELECT * FROM users WHERE age «.

Если мы сделаем запрос » SELECT name FROM users WHERE age «, то в результате нам вернут только значения поля «name», но они также будут отсортированы по age.

Мы можем запросить значения двух полей: » SELECT name, age FROM users WHERE age «.

Теперь запросим все пользователей, с именем «Max».

И ещё пример запроса , — выберет только имена (name) из таблицы users, все кроме Max.

SELECT name FROM users WHERE name!=’Max’

На этом с запросом WHERE всё.

Ограничение записей — LIMIT

Добавив в SQL-запрос команду LIMIT мы ограничим размер результата.

Запрос, который выводит первые три записи: » SELECT * FROM users LIMIT 3 «. Давайте посмотрим как он работает:

Также тут мы использовали запрос: » SELECT * FROM users LIMIT 3, 3 «. Вторая тройка указывает смещение в результате запроса.

Соответствие шаблону — LIKE

Язык SQL поддерживает простые шаблоны. Для этого используется команда LIKE и шаблон задаётся с использованием символа % .

Вот пример запроса, который вернёт все записи с именами, начинающимися на букву S.

SELECT * FROM users WHERE name LIKE ‘S%’

Вот пример запроса, который вернёт все записи с фамилиями, заканчивающимися на букву s.

SELECT * FROM users WHERE name LIKE ‘%s’

Соответствие условию — IN

Этот запрос с использованием команды IN вернёт только те строки, которые строго соответствую условию.

Например, нас интересуют люди с возрастом 21, 26 и 33 года.

SELECT * FROM users WHERE age IN (21,26,33)

Максимальное и минимальное значение в столбце

Выбирает максимальное значение 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, так что этот запрос не сработает на ней. Но это поле, содержащее уникальные номера строк, мы обязательно выучим.

Удалить запись — DELETE

Запрос к базе данных MySQL для удаления записи:

DELETE FROM users WHERE

Опять же, в нашей таблице нет поля id. Но мы можем удалить из неё всех людей, младше 18 лет.

DELETE FROM users WHERE age

Удалить таблицу — 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

Этот запрос MySQL добавил столбец 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

Мастер Йода рекомендует:  Unity запретила Improbable, разработчику SpatialOS, использовать свой движок

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

Итоги

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

Некоторые запросы обычно делают только из phpMyAdmin (создание и удаление баз данных например).

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

Следующим шагом будет изучение типов данных в MySQL.

Примеры SQL-запросов в MariaDB (MySQL)

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

Простые примеры использования SELECT

> SELECT FROM

[JOIN ] [ WHERE ORDER BY LIMIT ]

* где fields1 — поля для выборки через запятую, также можно указать все поля знаком *; table — имя таблицы, из которой вытаскиваем данные; conditions — условия выборки; fields2 — поле или поля через запятую, по которым выполнить сортировку; count — количество строк для выгрузки.
* запрос в квадратных скобках не является обязательным для выборки данных.

1. Обычная выборка данных

> SELECT * FROM users

* в данном примере мы получаем список всех записей из таблицы users.

2. Выборка данных с объединением двух таблиц (JOIN)

SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_ >

* в данном примере идет выборка данных с объединением таблиц users и users_rights. Объединяются они по полям user_id (в таблице users_rights) и id (users). Извлекается поле name из первой таблицы и все поля из второй.

3. Выборка с интервалом по времени и/или дате

а) известна точка начала и определенный временной интервал:

> SELECT * FROM users WHERE date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)

* будут выбраны данные за последний час (поле date).

б) известны дата начала и дата окончания:

> SELECT * FROM users WHERE date >= ‘2020-10-25’ AND date SELECT * FROM users WHERE DATE(date) BETWEEN ‘2020-03-25 00:15:00’ AND ‘2020-04-25 15:33:09’;

* выбираем данные в промежутке между 25.03.2020 0 часов 15 минут и 25.04.2020 15 часов 33 минуты и 9 секунд.

г) вытаскиваем данные за определенные месяц и год:

> SELECT * FROM study WHERE MONTH(date) = 4 AND YEAR(date) = 2020

* извлечем данные, где в поле date присутствуют значения для апреля 2020 года.

4. Выборка максимального, минимального и среднего значения

> SELECT max(area), min(area), avg(area) FROM country

* max — максимальное значение; min — минимальное; avg — среднее.

5. Использование длины строки

> SELECT * FROM users WHERE CHAR_LENGTH(name) = 5;

* данный запрос должен показать всех пользователей, имя которых состоит из 5 символов.

6. Использование лимитов (LIMIT)

Применяется для ограничения количества выводимых результатов. Синтаксис:

* где число1 — сколько результатов вернуть; число2 — сколько результатов пропустить, необязательный параметр — если его не писать, то отсчет начнется с первой строки.

а) извлечь максимум 15 строк:

> SELECT * FROM users LIMIT 15;

б) выбрать строки с 16 по 25 (запрос со смещением):

> SELECT * FROM users LIMIT 15, 10;

* 15 строк пропускаем, 10 извлекаем.

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

1. Объединение с группировкой выбранных данных в одну строку (GROUP_CONCAT)

> SELECT GROUP_CONCAT(DISTINCT CONVERT(id USING ‘utf8’) SEPARATOR ‘, ‘) as ids FROM users

* из таблицы users извлекаются данные по полю id, все они помещаются в одну строку, значения разделяются запятыми.

2. Группировка данных по двум и более полям

> SELECT * FROM users GROUP BY CONCAT(title, ‘::’, birth)

* итого, в данном примере мы сделаем выгрузку данных из таблицы users и сгруппируем их по полям title и birth. Перед группировкой мы делаем объединение полей в одну строку с разделителем ::.


3. Объединение результатов из двух таблиц (UNION)

> (SELECT id, fio, address, ‘Пользователи’ as type FROM users)
UNION
(SELECT id, fio, address, ‘Покупатели’ as type FROM customers)

* в данном примере идет выборка данных из таблиц users и customers.

4. Выборка средних значений, сгруппированных за каждый час

SELECT avg(temperature), DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’) as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’)

* здесь мы извлекаем среднее значение поля temperature из таблицы archive и группируем по полю datetimeupdate (с разделением времени за каждый час).

5. Использование операторов IF и CASE

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

а) выбрать пол мужской или женский:

SELECT IF(sex = ‘m’, ‘мужчина’, ‘женщина’) as sex FROM people

* в данном примере мы возвращаем слово «мужчина», если поле sex равно ‘m‘, иначе — «женщина».

б) заменяем идентификатор времени года более понятным человеку значением:

SELECT CASE season_id WHEN 1 THEN ‘зима’ WHEN 2 THEN ‘весна’ WHEN 3 THEN ‘лето’ WHEN 4 THEN ‘осень’ ELSE ‘неправильный идентификатор времени года’ END as season FROM ` seasons

* в данном примере мы используем оператор CASE. Если 1, то вернем слово «зима», если 2 — «весна» и так далее.

Вставка (INSERT)

> INSERT INTO

( ) VALUES ( )

> INSERT INTO

VALUES ( )

* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую.
* первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.

1. Вставка нескольких строк одним запросом:

> INSERT INTO cities (`name`, `country`) VALUES (‘Москва’, ‘Россия’), (‘Париж’, ‘Франция’), (‘Фунафути’ ,’Тувалу’);

* в данном примере мы одним SQL-запросом добавим 3 записи.

2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):

Синтаксис при копировании строк из одной таблицы в другую выглядит так:

> INSERT INTO SELECT * FROM WHERE ;

* где table1 — куда копируем; table2 — откуда копируем.

а) скопировать все без разбора:

> INSERT INTO cities-new SELECT * FROM cities;

* в данном примере мы скопируем все строки из таблицы cities в таблицу cities-new.

б) скопировать определенные столбцы строк с условием:

> INSERT INTO cities-new (`name`, `country`) SELECT `name`, `country` FROM cities WHERE name LIKE ‘М%’;

* извлекаем все записи из таблицы cities, названия которых начинаются на «М» и заносим в таблицу cities-new.

в) копирование с обновлением повторяющихся ключей.

Если копировать таблицы несколько раз, то может возникнуть проблема повторения первичного ключа. В базах данных значения таких ключей должны быть уникальными и при попытке вставить повтор мы получим ошибку «Duplicate entry ‘xxx’ for key ‘PRIMARY’». Чтобы новые строки вставить, а повторяющиеся обновить (если есть изменения), используем «ON DUPLICATE KEY UPDATE»:

> INSERT INTO cities-new SELECT * FROM cities ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `country`=VALUES(`country`);

* в данном примере, как и в предыдущих, мы копируем данные из таблицы cities в таблицу cities-new. Но при совпадении значений первичного ключа мы будем обновлять поля name и country.

Обновление (UPDATE)

> UPDATE

SET =’ ‘ WHERE

* где table — имя таблицы; field — поле, для которого будем менять значение; value — новое значение; conditions — условие (без него делать update опасно — можно заменить все данные во всей таблице).

Обновление с использованием замены (REPLACE):

UPDATE

SET = REPLACE( , ‘ ‘, ‘ ‘);

UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’);

UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’) WHERE country = ‘Россия’;

UPDATE cities SET name = REPLACE(name, ‘Ма’, ‘Мо’) WHERE name = ‘Масква’;

Если мы хотим перестраховаться, результат замены можно сначала проверить с помощью SELECT:

SELECT REPLACE(name, ‘Ма’, ‘Мо’) FROM cities WHERE name = ‘Масква’;

Удаление (DELETE)

> DELETE FROM

WHERE

* где table — имя таблицы; conditions — условие (как и в случае с UPDATE, использовать DELETE без условия опасно — СУБД не запросит подтверждения, а просто удалит все данные).

Создание таблицы

> CREATE TABLE

( , )

> CREATE TABLE IF NOT EXISTS `users_rights` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`rights` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

* где table — имя таблицы (в примере users_rights); field1, field2 — имя полей (в примере создается 3 поля — >

Использование запросов в PHP

Подключаемся к базе данных:

mysql_connect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);
mysql_select_db (‘db_name’);
mysql_query(«SET NAMES ‘utf8′»);

* где подключение выполняется к базе на локальном сервере (localhost); учетные данные для подключения — login и password (соответственно, логин и пароль); в качестве базы используется db_name; используемая кодировка UTF-8.

Также можно создать постоянное подключение:

mysql_pconnect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);


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

* в PHP выполняется автоматически, кроме постоянных подключений (mysql_pconnect).

Запрос к MySQL (Mariadb) в PHP делается функцией mysql_query(), а извлечение данных из запроса — mysql_fetch_array():

$result = mysql_query(«SELECT * FROM users»);
while ($mass = mysql_fetch_array($result)) <
echo $mass[name] . ‘
‘;
>

* в данном примере выполнен запрос к таблице users. Результат запроса помещен в переменную $result. Далее используется цикл while, каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.

Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.

Экранирование

При необходимости включения в строку запроса спецсимвола, например, %, необходимо использовать экранирование с помощью символа обратного слэша — \

> SELECT * FROM producrions WHERE kpd = ‘100\%’

* если выполнить такой запрос без экранирования, знак %, будет восприниматься как любое количество символов после 100.

Форум PHP программистов ► Разное ► Статьи

Пейджер выключен!

Сообщений: 26766
Пользователь №: 21350
На форуме:
Карма: 756

Что я могу сказать, к сожалению тема ещё актуальна 🙁 До сих пор есть ребята, для которых это непонятная тема.

Вывод данных из БД MySQL ( мускул )

Давайте создадим БД ‘mydb’, а в ней простую таблицу ‘mytable’.

Для этого воспользуемся phpMyAdmin (PMA), предпологается, что у вас уже установлен пакет «Denwer» и так же есть навыки работы на нём:

— Запустите сервер
— В адресной строке наберите http://localhost
— Внизу страницы, в разделе «Утилиты»: phpMyAdmin (http://localhost/Tools/ phpmyadmin /)

1. На главной странице есть поле с именем «Новая база данных » — вводим в это поле ‘mydb’, нажимаем «создать». База создана.
2. После создания БД — Вас автоматически перенаправит в созданную БД.
3. Воспользуемся вкладкой «SQL»:
— перед Вами текстовое поле «Выполнить SQL-запрос(ы) к базе данных mydb:»,
— давайте, с помощью SQL — запроса, создадим таблицу, с которой мы и будем дальше работать .
— скопируйте, затем вставьте ниже приведённый запрос в текстовое поле, и нажмите «ок»

Пометка: работаем в юникоде, сравнение таблицы и сопостовление соединения — utf8_general_ci

— Если всё сделано верно, то Вы увидите сообщение » SQL-запрос был успешно выполнен! «

4. Теперь у нас есть своя БД, в которой есть таблица с данными, переходим непосредственно к работе с этой БД
5. В директории своего локального сайта создайте страничку «index. php «
6. Теперь листинг этого файла:
‘;
>
>

//Вот в принципе и все. А что именно произошло спрашиваете вы себя?
//Да всё очень просто, во время работы цикла While() — данные по строчно писались в наш массив $row
// Т.е по сути выполнялся код:
// $row = array(
// «id» = > «`id` из строчки таблицы»,
// «name» => «`name` из строчки таблицы»,
// «country» => «`coyntry` из строчки таблицы»,
// «name» => «`cyty` из строчки таблицы»
// );
>

//Закрыли соединение
my sql _close ( $link );
?>

И массив заполнялся до тех пор, пока не кончились строчки в таблицы, вот мы и получили многомерный(2х) ассициотивный массив
А помогла на функция my sql _fetch_assoc(), которая работала в цикле while()

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

Для того чтобы задать условие запросе в БД, используется оператор WHERE, нам всего лишь нужно подкорректировать
Запрос $ sql , в нашем вернхнем примере

Обратите внимание, что в SQL знак равенства используется для сравнения данных , а в PHP сравнение происходит при использовании двух знаков равенства (a == равно)

Далее усложним. мы выведем только одного пользователя из России, для этого в конце запроса выставим LIMIT (на кол-во выводимых строк)

Можно даже так LIMIT 3,10 — выведет все строки с 3 по 10 (если они там есть))))

Можно даже вывести всех из таблицы, которые не из России

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

p.s — если вы указываете LIMIT = 1, то while — использовать не нужно.

Вы наверное уже сами задались вопросом: А как же сортировать данные на выводе?

Для этого в синтаксисе SQL используется команда ORDER BY, и далее указать столбец по которому сортируем, + 2 режима ASC (default) и DESC.

ASC — обычная сортировка (0-9 A-Z А-Я)
DESC — обратная сортировка (9-0 Z-A Я-А)

Обратите внимание, что при использовании сортировки и лимита необходимо соблюдать именно такой порядок

выбрать — что — в какой таблице — условие — сортировка — лимит

Ну и конечно же, переменные php внутри SQL:

Переменные — в string — необходимо обрамлять ‘ » . $string . » ‘
Числа (нужно опустить дв. кавычки, и подстраховаться указав тип переменной) ‘ . (int)$id . ‘

Во время работы с БД, а именно при составлении SQL-запросов, новичку очень легко допустит ошибку, поэтому php предусмотрены функция вывода ошибок: my sql _error();

Теперь вам в прямом смысле будет «Разжовано» — где вы допустили ошибку 😉

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

autor: ( ТС ) Игорь_Vasinsky

MySQL — использование результатов одного запроса для использования в другом запросе

Скажем, у меня есть это, что производит 50 000 строк:

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

Но я понимаю из других и ресурсов, что предложение IN не будет работать хорошо, тем более, что у меня может быть 100 000 плюс фотоидентификаторы.

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

Любая помощь по этому поводу будет с благодарностью принята.

Хорошо ли хранить фотоидентификатор из верхнего запроса в другой таблице (resultsTbl) или в очень длинной строке?

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

В очень длинной строке: Нет. Операции с строками обычно очень интенсивные.

Если да, либо я использую соединение или подвыбор, чтобы запросить эти ID (в нижнем запросе) вместо использования IN?

  • Вместо того, чтобы хранить его во временной таблице, просто выполните JOIN для начала (см. пример ниже). В некоторых случаях базы данных присоединятся к IN(select * from foo) для вас.
Добавить комментарий