Сложные запросы mysql. Связь PHP с базой данных MySQL. Оптимизация MySQL запросов

Оставьте комментарий 6,950

СУБД MySQL - одна из множества баз данных, поддерживаемых в PHP. Система MySQL распространяется бесплатно и обладает достаточной мощностью для решения реальных задач.

Краткое введение в MySQL

SQL - это аббревиатура от слов Structured Query Language , что означает структурированный язык запросов. Этот язык является стандартным средством для доступа к различным базам данных.

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

Для работы с базами данных удобно пользоваться средством, входящее в комплект Web-разработчика: Denwer phpMyAdmin . Здесь можно создать новую базу данных, создать новую таблицу в выбранной базе данных, заполнить таблицу данными, а также добавлять, удалять и редактировать данные.

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

Каждый столбец после своего типа данных содержит и другие спецификаторы:

Тип	Описание
NOT NULL	Все строки таблицы должны иметь значение в этом атрибуте. Если не указано, поле может быть пустым (NULL)
AUTO_INCREMENT	Специальная возможность MySQL, которую можно задействовать в числовых столбцах. Если при вставке строк в таблицу оставлять такое поле пустым, MySQL автоматически генерирует уникальное значение идентификатора. Это значение будет на единицу больше максимального значения, уже существующего в столбце. В каждой таблице может быть не больше одного такого поля. Столбцы с AUTO_INCREMENT должны быть проиндексированными
PRIMARY KEY	Столбец является первичным ключом для таблицы. Данные в этом столбце должны быть уникальными. MySQL автоматически индексирует этот столбец
UNSIGNED	После целочисленного типа означает, что его значение может быть либо положительным, либо нулевым
COMMENT	Название столбца таблицы

Создание новой базы данных MySQL CREATE DATABASE .

CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin

Создание новой таблицы осуществляется при помощи SQL-команды CREATE TABLE . Например, таблица books для книжного магазина будет содержать пять полей: ISBN, автор, название, цена и количество экземпляров:

CREATE TABLE books (ISBN CHAR(13) NOT NULL, PRIMARY KEY (ISBN), author VARCHAR(30), title VARCHAR(60), price FLOAT(4,2), quantity TINYINT UNSIGNED); Чтобы избежать сообщения об ошибке, если таблица уже есть необходимо изменить первую строчку, добавив фразу "IF NOT EXISTS": CREATE TABLE IF NOT EXISTS books ...

Для создания автообновляемого поля с текущей датой типа TIMESTAMP или DATETIME используйте следующую конструкцию:

CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Добавление данных в эту таблицу осуществляется при помощи SQL-команды INSERT . Например:

INSERT INTO books (ISBN, author, title, price, quantity) VALUES ("5-8459-0184-7", "Зандстра Мэт", "Освой самостоятельно PHP4 за 24 часа", "129", "5");

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

Символ * означает, что необходимы все поля. Например:

SELECT * FROM books;

Для получения доступа только к некоторому полю следует указать его имя в инструкции SELECT . Например:

SELECT author, title, price FROM books;

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

SELECT * FROM books WHERE price % Соответствует любому количеству символов, даже нулевых
_ Соответствует ровно одному символу

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

SELECT * FROM books ORDER BY price;

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

SELECT * FROM books ORDER BY price DESC;

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

SELECT * FROM books ORDER BY 4, 2, 3;

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

UPDATE books SET price = price * 1.1;

Конструкция WHERE ограничит работу UPDATE определенным строками. Например:

UPDATE books SET price = price * 1.05 WHERE price

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

DELETE FROM books WHERE quantity = 0;

Если нужно удалить все записи

TRUNCATE TABLE table_name

Для полного удаления таблицы используется:

DROP TABLE table_name

Связь PHP с базой данных MySQL

Поработав с phpMyAdmin над созданием базы данных, можно приступить к подключению этой базы данных к внешнему Web-интерфейсу.

Чтобы получить доступ к базе данных из Web, используя PHP, надо сделать следующие основные шаги:

Подключение к серверу MySQL.
Выбор базы данных.
Выполнение запроса к базе данных:
- добавление ;
- удаление ;
- изменение ;
- поиск ;
- сортировка .
Получение результата запроса.
Отсоединение от базы данных.

Для подключения к серверу базы данных в PHP есть функция mysql_connect() . Ее аргументы: имя компьютера, имя пользователя и пароль. Эти аргументы можно опустить. По умолчанию имя компьютера = localhost , тогда имя пользователя и пароль не требуется. Если PHP используется в сочетании с сервером Apache, то можно воспользоваться функцией mysql_pconnect() . В этом случае соединение с сервером не исчезает после завершения работы программы или вызова функции mysql_close() . Функции mysql_connect() и mysql_pconnect() возвращают идентификатор подключения, если все прошло успешно. Например:

$link = mysql_pconnect (); if (!$link) die ("Невозможно подключение к MySQL");

После того, как соединение с сервером MySQL установлено, нужно выбрать базу данных. Для этого используется функция mysql_select_db() . Ее аргумент: имя базы данных. Функция возвращает true , если указанная база данных существует и доступ к ней возможен. Например:

$db = "sample"; mysql_select_db ($db) or die ("Невозможно открыть $db");

Для добавления, удаления, изменения и выбора данных нужно сконструировать и выполнить запрос SQL. Для этого в языке PHP существует функция mysql_query() . Ее аргумент: строка с запросом. Функция возвращает идентификатор запроса.

Пример 1

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

При каждом выполнении примера 1 в таблицу будет добавляться новая запись, содержащая одни и те же данные. Разумеется имеет смысл добавлять в базу данные, введенные пользователем.

В примере 2.1 приведена HTML-форма для добавления новых книг в базу данных.

Пример 2.1

HTML-форма добавления новых книг

Результаты заполнения этой формы передаются в insert_book.php.

Пример 2.2

Программа добавления новых книг (файл insert_book.php) Пожалуйста, вернитесь назад и закончите ввод"); } $isbn = trim ($_POST["isbn"]); $author = trim ($_POST["author"]); $title = trim ($_POST["title"]) ; $isbn = addslashes ($isbn); $author = addslashes ($author); $title = addslashes ($title) ; $db = "sample"; $link = mysql_connect(); if (!$link) die ("Невозможно подключение к MySQL"); mysql_select_db ($db) or die ("Невозможно открыть $db"); $query = "INSERT INTO books VALUES ("" .$isbn."", "".$author."", "".$title."", "" .floatval($_POST["price"])."", "".intval($_POST["quantity"])."")"; $result = mysql_query ($query); if ($result) echo "Книга добавлена в базу данных."; mysql_close ($link); ?>

В примере 2.2 введенные строковые данные обработаны функцией addslashes() . Эта функция добавляет обратные слеши перед одинарными кавычками ("), двойными кавычками ("), обратным слешем (\) и null-байтом. Дело в том, что по требованиям систаксиса запросов баз данных такие символы дожны заключаться в кавычки.

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

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

В примере 3.1 приведена HTML-форма для поиска определенных книг в базе данных.

Пример 3.1

HTML-форма поиска книг

Результаты заполнения этой формы передаются в search_book.php.

Пример 3.2

Пожалуйста, вернитесь назад и закончите ввод"); $searchterm = addslashes ($searchterm); $link = mysql_pconnect (); if (!$link) die ("Невозможно подключение к MySQL"); $db = "sample"; mysql_select_db ($db) or die ("Невозможно открыть $db"); $query = "SELECT * FROM books WHERE " .$_POST["searchtype"]." like "%".$searchterm."%""; $result = mysql_query ($query); $n = mysql_num_rows ($result); for ($i=0; $i<$n; $i++) { $row = mysql_fetch_array($result); echo "

".($i+1). $row["title"]. "

"; } if ($n == 0) echo "Ничего не можем предложить. Извините"; mysql_close ($link); ?>

Альтернативный вариант

Программа поиска книг (файл search_book.php) Пожалуйста, вернитесь назад и закончите ввод"); $searchterm = addslashes ($searchterm); mysql_connect() or die ("Невозможно подключение к MySQL"); mysql_select_db ("sample") or die ("Невозможно открыть БД"); $result = mysql_query ("SELECT * FROM books WHERE ".$_POST["searchtype"]." like "%".$searchterm."%""); $i=1; while($row = mysql_fetch_array($result)) { echo "

".($i++) . $row["title"]."
"; echo "Автор: ".$row["author"]."
"; echo "ISBN: ".$row["ISBN"]."
"; echo "Цена: ".$row["price"]."
"; echo "Количество: ".$row["quantity"]."

"; } if ($i == 1) echo "Ничего не можем предложить. Извините"; mysql_close(); ?>

Итак, как работает архитектура Web-баз данных:

Web-браузер пользователя выдает HTTP-запрос определенной Web-страницы. Например, пользователь, используя HTML-форму, ищет все книги о PHP. Страница обработки формы называется search_book.php.
Web-сервер принимает запрос на search_book.php, извлекает этот файл и передает на обработку механизму PHP.
PHP выполняет соединение с MySQL-сервером и отправляет запрос.
Сервер принимает запрос к базе данных, обрабатывает его и отправляет результат (список книг) обратно механизму PHP.
Механизм PHP завершает выполнение сценария, форматирует результат запроса в HTML. После этого результат в виде HTML возвращается Web-серверу.
Web-сервер пересылает HTML в браузер, и пользователь имеет возможность просмотреть запрошенный список книг.

Использование механизма транзакций

Использование механизма транзакция на примере как передать деньги от одного человека другому

If(mysql_query ("BEGIN") && mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = "Eve"") && mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = "Ida"") && mysql_query ("COMMIT")){ echo "Успешно"; }else{ mysql_query ("ROLLBACK"); echo "Не успешно"; }

SELECT … FOR UPDATE

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

Чтобы избежать вышеупомянутой ситуации необходимо выполнить не просто SELECT запрос, а его расширенную версию, о которой многие и не подозревают: SELECT … FOR UPDATE.

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

Однако не всё так просто. Вам нужно выполнить ещё несколько условий. Во-первых, ваша таблица должна быть создана на основе архитектуры InnoDB. В противном случае блокировка просто не будет срабатывать. Во-вторых, перед выполнением выборки необходимо отключить авто-коммит запроса. Т.е. другими словами автоматическое выполнение запроса. После того как вы укажите UPDATE запрос, необходимо будет ещё раз обратиться к базе и закоммитить изменения с помощью команды COMMIT:

9 октября 2008 в 23:37

Оптимизация MySQL запросов

MySQL

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

В этой статье хотелось бы привести примеры того, как НЕ надо писать запросы.

Выборка всех полей
SELECT * FROM table
При написании запросов не используйте выборку всех полей - "*". Перечислите только те поля, которые вам действительно нужны. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вам на самом деле необходимы все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочки невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, со временем количество столбцов в вашей таблице может изменяться, и если сегодня это пять INT столбцов, то через месяц могут добавиться TEXT и BLOB поля, которые будут замедлять выборку.
Запросы в цикле.
Нужно четко представлять себе, что SQL - язык, оперирующий множествами. Порой программистам, привыкшим думать терминами процедурных языков, трудно перестроить мышление на язык множеств. Это можно сделать довольно просто, взяв на вооружение простое правило - «никогда не выполнять запросы в цикле». Примеры того, как это можно сделать:
1. Выборки
$news_ids = get_list("SELECT news_id FROM today_news ");
while($news_id = get_next($news_ids))
$news = get_row("SELECT title, body FROM news WHERE news_id = ". $news_id);
Правило очень простое - чем меньше запросов, тем лучше (хотя из этого, как и из любого правила, есть исключения). Не забывайте про конструкцию IN(). Приведенный код можно написать одним запросом:
SELECT title, body FROM today_news INNER JOIN news USING(news_id)
2. Вставки
$log = parse_log();
while($record = next($log))
query("INSERT INTO logs SET value = ". $log["value"]);
Гораздо более эффективно склеить и выполнить один запрос:
INSERT INTO logs (value) VALUES (...), (...)
3. Обновления
Иногда бывает нужно обновить несколько строк в одной таблице. Если обновляемое значение одинаковое, то все просто:
UPDATE news SET title="test" WHERE id IN (1, 2, 3).
Если изменяемое значение для каждой записи разное, то это можно сделать таким запросом:
UPDATE news SET
title = CASE
WHEN news_id = 1 THEN "aa"
WHEN news_id = 2 THEN "bb" END
WHERE news_id IN (1, 2)
Наши тесты показывают, что такой запрос выполняется в 2-3 раза быстрее, чем несколько отдельных запросов.
Выполнение операций над проиндексированными полями
SELECT user_id FROM users WHERE blogs_count * 2 = $value
В таком запросе индекс использоваться не будет, даже если столбец blogs_count проиндексирован. Для того, чтобы индекс использовался, над проиндексированным полем в запросе не должно выполняться преобразований. Для подобных запросов выносите функции преобразования в другую часть:
SELECT user_id FROM users WHERE blogs_count = $value / 2;
Аналогичный пример:
SELECT user_id FROM users WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(registered) <= 10;
Не будет использовать индекс по полю registered, тогда как
SELECT user_id FROM users WHERE registered >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
будет.
Выборка строк только для подсчета их количества
$result = mysql_query(«SELECT * FROM table», $link);
$num_rows = mysql_num_rows($result);
Если вам нужно выбрать количество строк, удовлетворяющих определенному условию, используйте запрос SELECT COUNT(*) FROM table, а не выбирайте все строки лишь для того, чтобы подсчитать их количество.
Выборка лишних строк
$result = mysql_query(«SELECT * FROM table1», $link);
while($row = mysql_fetch_assoc($result) && $i < 20) {
…
}
Если вам нужны только n строк выборки, используйте LIMIT, вместо того, чтобы отбрасывать лишние строки в приложении.
Использование ORDER BY RAND()
SELECT * FROM table ORDER BY RAND() LIMIT 1;
Если в таблице больше, чем 4-5 тысяч строк, то ORDER BY RAND() будет работать очень медленно. Гораздо более эффективно будет выполнить два запроса:
Если в таблице auto_increment"ный первичный ключ и нет пропусков:
$rnd = rand(1, query("SELECT MAX(id) FROM table"));
$row = query("SELECT * FROM table WHERE id = ".$rnd);
Либо:
$cnt = query("SELECT COUNT(*) FROM table");
$row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
что, однако, так же может быть медленным при очень большом количестве строк в таблице.
Использование большого количества JOIN"ов
SELECT
v.video_id
a.name,
g.genre
FROM
videos AS v
LEFT JOIN
link_actors_videos AS la ON la.video_id = v.video_id
LEFT JOIN
actors AS a ON a.actor_id = la.actor_id
LEFT JOIN
link_genre_video AS lg ON lg.video_id = v.video_id
LEFT JOIN
genres AS g ON g.genre_id = lg.genre_id
Нужно помнить, что при связи таблиц один-ко многим количество строк в выборке будет расти при каждом очередном JOIN"е. Для подобных случаев более быстрым бывает разбить подобный запрос на несколько простых.
Использование LIMIT
SELECT… FROM table LIMIT $start, $per_page
Многие думают, что подобный запрос вернет $per_page записей (обычно 10-20) и поэтому сработает быстро. Он и сработает быстро для нескольких первых страниц. Но если количество записей велико, и нужно выполнить запрос SELECT… FROM table LIMIT 1000000, 1000020, то для выполнения такого запроса MySQL сначала выберет 1000020 записей, отбросит первый миллион и вернет 20. Это может быть совсем не быстро. Тривиальных путей решения проблемы нет. Многие просто ограничивают количество доступных страниц разумным числом. Также можно ускорить подобные запросы использованием покрывающих индексов или сторонних решений (например sphinx).
Неиспользование ON DUPLICATE KEY UPDATE
$row = query("SELECT * FROM table WHERE id=1");
If($row)
query("UPDATE table SET column = column + 1 WHERE id=1")
else
query("INSERT INTO table SET column = 1, id=1");
Подобную конструкцию можно заменить одним запросом, при условии наличия первичного или уникального ключа по полю id:
INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

Читайте

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

Синтаксис:

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

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

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

> SELECT * FROM users

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

SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id

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

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

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

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

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

25.10.2017 и 25.11.2017 .

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

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

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

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

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

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

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

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

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

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

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

* из таблицы 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 (с разделением времени за каждый час).

Вставка (INSERT)

Синтаксис 1:

> INSERT INTO

() VALUES ()

Синтаксис 2:

> INSERT INTO

VALUES ()

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

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

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

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

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

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

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

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

Синтаксис:

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

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

UPDATE

SET = REPLACE(, "<что меняем>", "<на что>");

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

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

Удаление (DELETE)

Синтаксис:

* где 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 поля — id, user_id, rights ); options1, options2 — параметры поля (в примере int(10) unsigned NOT NULL ); table options — общие параметры таблицы (в примере ENGINE=InnoDB DEFAULT CHARSET=utf8 ).

Использование запросов в 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 . "
";
}

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

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

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

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

Например:

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

На этом все. Если Вам нужно помочь с выполнением запроса, пишите мне на почту

В данной статье мы рассмотрим способы обращения к таблицам баз данный MySQL с помощью языка запросов SQL. SQL - это аббревиатура, которая так и "раскрывается" - структуризированный язык запросов.
В языке PHP для это цели существует целый ряд функций с префиксом "mysql". Нам для рассмотрения запросов понадобится не так много из них. Функция, без которой в языке PHP выполнение SQL-запросов было бы просто невозможным:

Resource mysql_query(запрос)

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

$host="localhost"; // имя хоста (уточняется у провайдера) $database="db_name"; // имя базы данных, которую вы должны создать $user="user_name"; // заданное вами имя пользователя, либо определенное провайдером $pswd="your_pass"; // заданный вами пароль $dbh = mysql_connect($host, $user, $pswd) or die("Не могу соединиться с MySQL."); mysql_select_db($database) or die("Не могу подключиться к базе.");

Итак mysql_connect() - функция для подключения к серверу MySQL на Вашем хостинге.
А mysql_select_db() выбирает базу данных на сервере для подключения.
Иными словами подключаемся к серверу, выбираем базу и начинаем работать.
Функция die() вызывается в случае ошибки и выводит в окно браузера сообщение, которое вы указали.
Для завершения работы с базами данных используется функция:

Mysql_close($dbh);

Здесь $dbh - дескриптор, которые при соединении возвратила функция mysql_connect .
Закончив стартовый обзор, начнем рассмотрение собственно SQL-запросов.
Для этого прежде всего вам необходимо создать базу данных с определенном именем. А в ней создать таблицу, тоже с конкретным именем. В наших примерах будем обращаться к таблице my_sql_table . Чтобы создать эту таблицу давайте выполним в phpmyadmin нашего localhost следующий запрос:

CREATE TABLE `my_sql_table` (`id` INT NOT NULL , // идентификатор будущих записей таблицы `firstname` VARCHAR(50) NOT NULL , // текстовое поле VARCHAR `surname` VARCHAR(50) NOT NULL , // max длиной 50 символов PRIMARY KEY (`id`) // первичный ключ - идентификатор id);

Итак таблица создана. Выполним первый запрос, который сразу оформим в виде PHP-кода:

\n"; echo "Имя: ".$row["firstname"]."
\n"; echo "Фамилия: ".$row["surname"]."

\n"; } ?>

Разберем PHP-код файла firstsql.php . Начнем с собственно запроса к таблицам базы данных (БД).

$query = "SELECT * FROM `my_sql_table`";

Данный запрос можно расшифровать так: выбрать из таблицы my_sql_table БД все записи из всех полей. Таким образом знак * после слова SELECT означает "выбрать абсолютно все". Итак, запрос сформирован. Теперь его надо выполнить:

$res = mysql_query($query);

В случае успешного выполнения запроса функция mysql_query() вернет нам идентификатор ресурса $res .
Его мы должны передать в качестве параметра в функцию mysql_fetch_array() . Название этой функции говорит само за себя. Т.е. она формирует и выдает массив по выборке из таблицы БД. В случае нашей таблицы массив будет состоять из числа элементов, равных количествам записей (строк) в таблице и содержать значения id, firstname, surname для каждой строки таблицы. Следовательно, следующий код:

While($row = mysql_fetch_array($res)) { echo "Номер: ".$row["id"]."
\n"; echo "Имя:".$row["firstname"]."
\n"; echo "Фамилия:".$row["surname"]."

\n"; }

можно прокомментировать так: пока введенная нами переменная $row получает не нулевые результаты работы функции mysql_fetch_row следует выдать в броузер значение полей $row["id"], $row["firstname"], $row["surname"] с помощью echo .
Если запрос выполнить так:

$query = "SELECT firstname FROM `my_sql_table`";

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

$res = mysql_query($query); while($row = mysql_fetch_array($res)) { echo "Имя:".$row["firstname"]."
\n"; }

Если Вы хотите выбрать строки таблицы с конкретным значением id где фамилия (surname) будет Петров , то запрос перепишется следующим образом:

$query = "SELECT id FROM `my_sql_table` where surname="Петров"";

А вот если потребуется узнать фамилию того, кто находится под номером, к примеру, 5, то запрос будет таким:

$query = "SELECT surname FROM `my_sql_table` where id=5";

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

$res = mysql_query($query); $row = mysql_fetch_row($res); echo "Фамилия пятого человека в списке: ".$row."\n";

Здесь вместо mysql_fetch_array() мы применили mysql_fetch_row() . Т.е. получить значение поля (или полей) конкретной строки. Поскольку поле у нас было одно - surname - мы можем обратиться к единственному элементу массива $row как $row; .

Итак, рассмотрим наиболее типичные примеры запросов MySQL. Рассмотрение проведем на базе таблицы my_sql_table :
1. Добавим в таблицу my_sql_table поле middle_name (отчество) после surname :

$query = "ALTER TABLE `my_sql_table` ADD `middle_name`
VARCHAR(50) NOT NULL AFTER `surname`";

2. Теперь удалим поле surname из таблицы my_sql_table:

$query = "ALTER TABLE `my_sql_table` DROP `surname`";

3. Удаляем записи из таблицы my_sql_table с фамилией Сидоров:

$query = "DELETE FROM `my_sql_table` where surname="Сидоров"";

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

$query = "SELECT * FROM `my_sql_table` where surname like "%дор%"";

Здесь наличие "% " в начале и конце "дор" и означает, что запрос будет искать именно "дор", причем не важно в начале, конце, или середине фамилии он находится. Рассмотрим следующий пример
5. Выберем записи из таблицы my_sql_table с фамилией, которая начинается на П . Обратите внимание на расположение "% ":

$query = "SELECT * FROM `my_sql_table` where surname like "П%"";

6. Вычислим максимальное значение id :

$query = "SELECT MAX(id) FROM `my_sql_table`";

7. Вычислим количество полей в my_sql_table с фамилией, которая начинается на П .

$query = "SELECT COUNT(*) FROM `my_sql_table` where surname like "П%"";

8. Удаление таблицы my_sql_table:

$query = "DROP TABLE `my_sql_table`";

Для запросов 1-3 на языке PHP достаточно просто выполнить запрос:

Mysql_query($query);

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

Есть еще вопросы или что-то непонятно - добро пожаловать на наш

ISBN
Автор
Название
Цена
Количество