Типовые операции с базой данных (CRUD)
Рассмотрим выполнение типовых операций с данными (CRUD — Create, Read, Update, Delete создание, чтение, обновление, удаление)
Обратите внимание, что код для них будет очень похожим
Создание (Вставка)
try {
$pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare('INSERT INTO someTable VALUES(:name)');
$stmt->execute(array(
':name' => 'Justin Bieber'
));
# Изменено строк?
echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
Обновление
$id = 5;
$name = "Joe the Plumber";
try {
$pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare('UPDATE someTable SET name = :name WHERE id = :id');
$stmt->execute(array(
':id' => $id,
':name' => $name
));
echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Удаление
$id = 5;
try {
$pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare('DELETE FROM someTable WHERE id = :id');
$stmt->bindParam(':id', $id); // Воспользуемся методом bindParam
$stmt->execute();
echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Управление поведением PDO при ошибках
Параметр выбора режима ошибок используется для определения поведения PDO в случае ошибок. Доступно три варианта: , и .
PDO::ERRMODE_SILENT
Вариант по умолчанию. PDO просто запишет информацию об ошибке, которую помогут получить методы errorCode и errorInfo.
PDO::ERRMODE_EXCEPTION
Это предпочтительный вариант, при котором в дополнение к информации об ошибке PDO выбрасывает исключение (PDOException). Исключение прерывает выполнение скрипта, что полезно при использовании транзакций PDO. Пример приведён при описании транзакций.
PDO::ERRMODE_WARNING
В этом случае PDO также записывает информацию об ошибке. Поток выполнения скрипта не прерывается, но выдаются предупреждения.
Краткое отступление
Традиционно, язык программирования PHP поддерживает работу с такой базой данных, как MySQL (это СУБД, поддерживающая структурированный язык запросов SQL). Для работы с базой данных MySQL в PHP встроены специальные функции, то есть необходимые возможности предусмотрены заранее. Эти функции позволяют:
— выполнять запросы и получать результаты;
— обрабатывать ошибки;
— читать и записывать данные;
— успешно и эффективно работать с популярной СУБД.
При подключении к MySQL соответствующий сценарий исполняет запрос и показывает результат запроса. Но самое главное заключается в том, что для работы с СУБД MySQL разработчику не придется ничего специально устанавливать, так как все нужное будет сразу доступно и включено в стандартную поставку PHP.
IDE для SQL
IDE или интегрированная среда разработки — это графический инструмент, который позволяет вам управлять всеми файлами, связанными с вашим приложением, и работать с такими инструментами, как полезные пакеты, функции автозаполнения, подсветка синтаксиса и т. Д., Чтобы улучшить ваш опыт разработки.
Хотя это правда, что вы можете создавать базы данных и таблицы и управлять ими прямо из самой командной строки, однако использование IDE всегда будет полезно для получения обзора всех баз данных, запросов, таблиц и других компонентов с высоты птичьего полета. Фактически, есть IDE, в которых есть раздел справки, в котором объясняются основные команды и их использование. Вы можете просто заполнить текстовые поля, выбрать различные предварительно отформатированные команды, нажать кнопку «ОК», и ваша работа будет выполнена. Это так просто. Более того, существуют IDE, которые также позволяют создавать резервные копии и восстанавливать базы данных и таблицы.
Следовательно, всегда разумно выбрать среду IDE, которая удовлетворяет ваши требования, прежде чем вы запачкаете руки SQL. Вот список лучших IDE, которые вы можете использовать для составления сложных SQL-запросов.
1. DBeaver
DBeaver— это среда разработки баз данных на основе Java с открытым исходным кодом. Его можно использовать бесплатно, и в нем есть мощные функции, которые обеспечат бесперебойную разработку.
Функции —
- Он позволяет экспортировать таблицы в файлы CSV и дамп, а также восстанавливать таблицы.
- Он позволяет сохранять наиболее часто используемые команды SQL. Вы можете загрузить эти сохраненные команды позже для других проектов.
- Также есть несколько цветовых тем.
- Он имеет инструмент управления сеансом.
- Он позволяет сравнивать две таблицы БД и их структуры.
- Выполненные запросы эстетично отображаются в отдельном интерфейсе.
- Он позволяет графически редактировать ячейки таблиц базы данных и фиксировать их.
2. PHPMyAdmin
PHPMyAdmin — это многофункциональный инструмент с открытым исходным кодом на основе HTML, который вы можете использовать для управления своими базами данных.
Функции —
- Это позволяет вам управлять пользователями и разрешениями.
- Он может поддерживать множество языков.
- Это позволяет создавать и редактировать запросы и столбцы результирующих строк.
- Вы можете сохранить свои запросы на более позднее время.
- IDE обладает широкими возможностями настройки для скрытия или отображения таблиц, комментариев, кодировок, временных меток и т. Д.
- Вы можете создавать резервные копии баз данных, конвертировать их в файлы CSV, импортировать дампы SQL и т. Д.
- Это позволяет вам управлять несколькими серверами.
- Вы можете использовать QBE для создания сложных запросов.
3. Adminer
Adminer можно использовать как альтернативу PHPMyAdmin. Он основан на веб-интерфейсе, поддерживает множество плагинов, позволяет работать с несколькими базами данных, такими как Oracle, SQLite и т. Д.
Особенности —
- Подключайтесь к базам данных, создавайте новые и т. Д.
- Вы можете распечатать схемы баз данных, даже если они связаны внешними ключами.
- Вы можете устанавливать и управлять разрешениями и правами пользователей и даже изменять их как администратор.
- Раздел справки неплохой, можно отображать переменные, у которых есть реферальные ссылки на документацию.
- Вы можете легко управлять разделами таблиц и событий.
Возможности MySQL
Работа с длинным текстом. Функции COMPRESS() и UNCOMPRESS() позволяют хранить в БД длинный текст без потери производительности. Так как длинный текст влияет на требования к объему дискового пространства, рекомендуется сжимать его через COMPRESS. А когда понадобится, конвертировать обратно.
С помощью функции REGEXP обеспечивается гибкое сопоставление с шаблоном регулярного выражения.
Еще одной удобной опцией баз данных MySQL является полнотекстовое индексирование полей VARCHAR и TEXT. Например, если владелец сайта хранит в базе анонсы или новостные статьи и хочет предоставить пользователю возможность поиска. Для подобных задач допустимо установить обычный поисковик, но при этом понадобится новая отдельная БД.
Управление запросами
В дополнение к предложениям и, есть несколько других предложений, которые используются для манипулирования результатами запроса . В этом разделе мы объясним и предоставим примеры для некоторых из наиболее часто используемых предложений запросов.
Одним из наиболее часто используемых предложений запроса, помимо и, является предложение . Обычно он используется, когда вы выполняете статистическую функцию для одного столбца, но в отношении сопоставления значений в другом.
Например, скажем, вы хотели знать, сколько ваших друзей предпочитают каждый из трех блюд, которые вы делаете. Вы можете найти эту информацию с помощью следующего запроса:
Предложение используется для сортировки результатов запроса. По умолчанию числовые значения сортируются в порядке возрастания, а текстовые значения сортируются в алфавитном порядке. Чтобы проиллюстрировать это, следующий запрос перечисляет столбцы и, но сортирует результаты по дате рождения:
Обратите внимание, что поведение по умолчанию состоит в сортировке набора результатов в порядке возрастания. Чтобы изменить это и отсортировать набор результатов в порядке убывания, закройте запрос с помощью :. Как уже упоминалось ранее, предложение WHERE используется для фильтрации результатов на основе определенных условий
Однако, если вы используете предложение с агрегатной функцией, оно вернет ошибку, как в случае со следующей попыткой выяснить, какие стороны являются фаворитами по крайней мере трех ваших друзей:
Как уже упоминалось ранее, предложение WHERE используется для фильтрации результатов на основе определенных условий. Однако, если вы используете предложение с агрегатной функцией, оно вернет ошибку, как в случае со следующей попыткой выяснить, какие стороны являются фаворитами по крайней мере трех ваших друзей:
Предложение было добавлено в SQL для обеспечения функциональности, аналогичной функциональности предложения WHERE, а также совместимости с агрегатными функциями. Полезно думать о разнице между этими двумя пунктами как о том, что применяется к отдельным записям, в то время как применяется к групповым записям. С этой целью каждый раз, когда вы вводите предложение , также должно присутствовать предложение.
Следующий пример — еще одна попытка найти, какие гарниры являются фаворитами как минимум трех ваших друзей, хотя этот вернет результат без ошибок:
Агрегатные функции полезны для суммирования результатов определенного столбца в данной таблице. Однако во многих случаях необходимо запросить содержимое более чем одной таблицы. Мы рассмотрим несколько способов сделать это в следующем разделе.
Чтение записей MySQL
После записи в базу данных возникает другая, не менее важная операция — чтение этих записей из таблиц. Запрос SQL для получения данных из таблицы, использующий , также выполняется при помощи функции .
Пример вывода всех существующих записей таблицы yourtable1:
<!--php-->
<?php
$sql = 'SELECT id, name FROM yourtable1';
$result = mysqli_query($link, $sql);
while ($row = mysqli_fetch_array($result)) {
echo("День: " . $row . "; Идентификатор: . " . $row . "<br>");
}
Результат выполнения из примера сохраняется в переменной . Нужно понимать, что эта переменная хранит не данные, а ссылку результатов запроса.
За получение самих данных (записей) отвечает функция , которой необходимо указать на ссылку параметром. После задания при вызове этой функции будет возвращаться последующая запись ассоциативным массивом.
Использование цикла необходимо для «прочесывания» всех записей из массива. Теперь, чтобы узнать значение поля по каждой отдельной записи можно обращением по ключу ассоциативного массива.
Получение записей в двумерном массиве
В некоторых задачах удобнее при вызове функции выводить не следующие записи по порядку, а вывести все сразу. В php для этого нужно воспользоваться функцией , которая возвратит двумерный массив по результату запроса с всеми записями.
Вид кода с показом всех присутствующих дат с использованием этой функции:
<!--php-->
<?php
$sql = 'SELECT id, name FROM yourtable1';
$result = mysqli_query($link, $sql);
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC)
foreach ($rows as $row) {
echo"День: " . $row . "; Идентификатор: . " . $row . "<br>";
}
Узнаем общее количество записей
Важной информацией может быть количество всех записей, которые выдаст исполненный запрос SQL. Она может быть использована, например, при построении навигации по страницам, или в общем — для понимания объема данных
Функция, которой предусмотрен показ числа записей — . Ей для работы необходима ссылка результата запроса.
MySQL Выбрать данные
В следующем примере из таблицы «Мои Гости» выбираем столбцы «Индентификатора»,
«Имя» и «Фамилию», для отображения на странице:
Пример MySQLi — объектно-ориентированный
<?php
// Подключение к MySQL
$servername = «localhost»; // локалхост
$username = «root»; // имя пользователя
$password = «»; // пароль если существует
$dbname = «myDB»; // база данных
// Создание соединения
$conn = new mysqli($servername, $username, $password, $dbname);
// Проверка соединения
if ($conn->connect_error) {
die(«Ошибка подключения: » . $conn->connect_error);}
// Выбрать данные
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Выводим данные каждой строки
while($row = $result->fetch_assoc()) {
echo «id:» . $row. » Имя: » . $row. » » . $row. «<br>»;
}
} else {
echo «0 результат»;}
// Закрыть подключение
$conn->close();?>
Объяснение примера:
Во-первых, мы создали SQL запрос, который выбирает ID, и из столбика.
Следующая строка кода запускает запрос и помещает полученные данные в переменную .
Затем функция проверяет наличие более чем ноля строк и возвращает их.
Если, возвращено более чем ноля строк, функция помещает все результаты в ассоциативный массив,
через который мы можем проследить. В цикл проходит через результирующий набор и выводит данные от ID,
и .
В следующем примере показано то же самое, что и в примере выше, процессуальный способом :
Пример MySQLi — процессуальный
<?php
// Подключение к MySQL
$servername = «localhost»; // локалхост
$username = «root»; // имя пользователя
$password = «»; // пароль если существует
$dbname = «myDB»; // база данных
// Создание соединения
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Проверка соединения
if (!$conn) {
die(«Ошибка подключения: » . mysqli_connect_error());}
// Выбрать данные
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Выводим данные каждой строки
while($row = mysqli_fetch_assoc($result)) {
echo «id:» . $row. » Имя Фамилия: » . $row. » » . $row. «<br>»;
}
} else {
echo «0 результат»;}
// Закрыть подключение
mysqli_close($conn);?>
Вы также можете поместить результат в таблицу HTML
Пример MySQLi — объектно-ориентированный
<?php
// Подключение к MySQL
$servername = «localhost»; // локалхост
$username = «root»; // имя пользователя
$password = «»; // пароль если существует
$dbname = «myDB»; // база данных
// Создание соединения
$conn = new mysqli($servername, $username, $password, $dbname);
// Проверка соединения
if ($conn->connect_error) {
die(«Ошибка подключения: » . $conn->connect_error);}
// Выводим данные каждой строки
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo «<table><tr><th>ID</th><th>Имя Фамилия</th></tr>»;
// Выводим данные каждой строки
while($row = $result->fetch_assoc()) {
echo «<tr><td>».$row.»</td><td>».$row.» «.$row.»</td></tr>»;
}
echo «</table>»;
} else {
echo «0 результат»;}
// Закрыть подключение
$conn->close();
?>
Понимание операторов SELECT
Как упоминалось во введении, SQL-запросы почти всегда начинаются с оператора . SELECT используется в запросах, чтобы указать, какие столбцы из таблицы должны быть возвращены в наборе результатов. Запросы также почти всегда включают , который используется для указания таблицы, к которой будет обращаться оператор.
Как правило, SQL-запросы следуют этому синтаксису:
Например, следующий оператор вернет весь столбец из таблицы:
Вы можете выбрать несколько столбцов из одной таблицы, разделяя их имена запятыми, например:
Вместо того, чтобы называть конкретный столбец или набор столбцов, вы можете следовать за оператором со звездочкой (), которая служит заполнителем, представляющим все столбцы в таблице. Следующая команда возвращает каждый столбец из таблицы :
WHERE используется в запросах для фильтрации записей, которые удовлетворяют указанному условию, и любые строки, которые не удовлетворяют этому условию, исключаются из результата. Предложение обычно соответствует следующему синтаксису:
Оператор сравнения в предложении WHERE определяет способ сравнения указанного столбца со значением. Вот некоторые распространенные операторы сравнения SQL:
| Оператор | Что он делает |
|---|---|
| = | тесты для равенства |
| != | тесты для неравенства |
| тесты для меньше, чем | |
| > | тесты для больше |
| тесты для менее чем или равный к | |
| >= | тесты для больше чем или равный к |
| BETWEEN | проверяет лежит ли в заданном диапазоне |
| IN | проверяет содержатся ли строки в наборе значений |
| EXISTS | тесты на соответствие строки существует при заданных условиях |
| LIKE | проверяет совпадает ли значение с указанной строкой |
| IS NULL | тесты для `NULL` значения |
| IS NOT NULL | тесты для всех других значений, чем `NULL` |
Например, если вы хотите найти размер обуви Ирмы, вы можете использовать следующий запрос:
SQL допускает использование подстановочных знаков, и это особенно удобно при использовании в предложениях WHERE. Знаки процента () представляют ноль или более неизвестных символов, а подчеркивания () представляют один неизвестный символ. Они полезны, если вы пытаетесь найти конкретную запись в таблице, но не уверены, что эта запись. Чтобы проиллюстрировать это, скажем, что вы забыли любимое блюдо нескольких своих друзей, но вы уверены, что это конкретное блюдо начинается с буквы «t». Вы можете найти его имя, выполнив следующий запрос:
Основываясь на вышеприведенном выводе, мы видим, что блюдо — это тофу.
Могут быть случаи, когда вы работаете с базами данных, в которых есть столбцы или таблицы с относительно длинными или трудно читаемыми именами. В этих случаях вы можете сделать эти имена более читабельными, создав псевдоним с ключевым словом . Псевдонимы, созданные с помощью , являются временными и существуют только на время запроса, для которого они созданы:
Здесь мы сказали SQL отображать столбец как, столбец как, а столбец как .
Примеры, которые мы рассмотрели до этого момента, включают в себя некоторые из наиболее часто используемых ключевых слов и предложений в запросах SQL. Они полезны для базовых запросов, но они бесполезны, если вы пытаетесь выполнить вычисление или получить скалярное значение (одно значение, а не набор из нескольких различных значений) на основе ваших данных. Это где агрегатные функции вступают в игру.
Ключевое слово WHERE
Очень часто нужно произвести какие-то действия не со всей таблицей, а только с некоторыми записями. Для
этого есть ключевое слово WHERE, после которого пишется условие. К тем записям, которые соответствуют условию,
применяется дейсвие. Условия применяются к значениям полей. Для них используются обычные операторы сравнения.
Например,
WHERE id > 5
WHERE login = ‘Nastya11’
Обратите внимание, для проверки на равенство используется один знак =. Чтобы указать «не равно» нужно написать так:
Чтобы указать «не равно» нужно написать так: <>
WHERE id <> 19
Для указания нескольких условий существуют логические операторы:
AND — логическое «и»
OR — логическое «или»
XOR — исключающее «или»
! — логическое «не»
Пример:
WHERE id >= 10 AND id <= 50
Не обязательно все условия должны относиться к одному полю. Можно применять их к разным полям
Оператор BETWEEN позволяет установить диапазон значений какого-то поля. Ко всем записям, которые попадают в
этот диапазон, будет применён запрос. Предыдущее условие можно написать так:
WHERE id BETWEEN 10 AND 50
Также можно применить действие к записям, которые, наоборот, не попадают в установленный диапазон. Для
этого перед BETWEEN пишется ключевое слово NOT. Условие пишется так:
WHERE id NOT BETWEEN 10 AND 50
Иногда нужно указать много значений и запрос получается длинный:
WHERE id=2 AND id=5 AND id=9 AND id=17
Существует оператор IN, который позволяет перечислить значения поля через
запятую.
WHERE id IN (2, 5, 9, 17)
Вставка (INSERT)
Синтаксис 1:
> INSERT INTO <table> (<fields>) VALUES (<values>)
Синтаксис 2:
> INSERT INTO <table> VALUES (<values>)
* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую.
* первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.
1. Вставка нескольких строк одним запросом:
> INSERT INTO cities (`name`, `country`) VALUES (‘Москва’, ‘Россия’), (‘Париж’, ‘Франция’), (‘Фунафути’ ,’Тувалу’);
* в данном примере мы одним SQL-запросом добавим 3 записи.
2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):
Синтаксис при копировании строк из одной таблицы в другую выглядит так:
> INSERT INTO <table1> SELECT * FROM <table2> WHERE <условие для select>;
* где 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.
