Выполнение запросов в MySQLi. Создание базы данных и таблиц

Работаете c программами на PHP в Linux и у вас есть задача по интегрированию MySQL на сайт на PHP? Тогда это руководство для вас. 😉

В этой статье я покажу, как создать базу данных (также известную как schema, схема), таблицы (с типами данных) и объясню, как выполнять операции языка управления данными (Data Manipulation Language (DML)) на MySQL / MariaDB сервере.

Подразумевается, что вы уже установили MySQL или MariaDB сервер.

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

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

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

Введение

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

Для того, чтобы выполнить все дальнейшие действия, у вас должны быть:

а) доступ к серверу на базе Linux, на котором работает MySQL/MariaDB;
б) название базы данных и данные доступа к ней.

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

Их используют для:

1. Регистрации и авторизации на сайте (хранят регистрационные данные).
2. Хранения статей и комментариев к ним.
3. Организации поиска по веб-сайту.

В этой статье мы научимся вставлять, изменять, получать и защищать данные из БД MySQL, используя PHP.

Есть несколько вариантов подключения к базе данных MySQL, но самые лучшие варианты — mysqli PDO

Основная разница в том, что PDO может работать с огромным количеством баз данных: MySQL, Cubrid, Oracle, SQlite и так далее. А mysqli может работать только с MySQL.

В своей статье я покажу, как работать с PDO. 

Примеры показывать буду на локальном сервере (xampp

Последнее обновление: 04.06.2021

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

Нам откроется следующее окно, где мы можем увидеть поле с названием запущенного локально экземпляра MySQL:

Нажмем на него, и нам отобразится окно для ввода пароля:

Здесь надо ввести пароль, который был установлен для пользователя root при установке MySQL.

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

Теперь посмотрим, как мы можем выполнять в этой программе запросы к бд.
Вначале создадим саму БД. Для этого нажмем над списком баз данных на значок «SQL» с плюсом:

После этого в центральной части программы откроется окно для ввода скрипта SQL. Введем в него следующую команду:

CREATE DATABASE usersdb;

Для выполнения скрипта в панели инструментов нажмем на значок молнии:

После этого внизу программы в поле вывода в случае удачного выполнения мы увидим зеленый маркер и отчет о выполнении.

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

USE usersdb;
CREATE TABLE users (	id INTEGER AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30), age INTEGER
);
INSERT INTO users (firstname, age) VALUES ('Tom', 34);

USE usersdb;
SELECT * FROM users;

Таким образом, мы можем осуществлять запросы к БД в программе MySQL Workbench CE.

Последнее обновление: 08.06.2021

Выполнение запросов

Объектно-ориентированный подход

Для выполнения запросов у объекта вызывается метод , в который передается выполняемая команда SQL:

$conn = new mysqli("localhost", "root", "mypassword");
$conn->query(команда_sql);

Если выполнение запроса прошло успешно, то метод возвращает true, если неудачно, то возвращается false.

Процедурный подход

При процедурном подходе для выполнения вызывается функция , в которую передается объект подключения и
выполняемая команда SQL:

$conn = mysqli_connect("localhost", "root", "mypassword");
mysqli_query($conn, $query);

Если выполнение запроса прошло успешно, то функция возвращает true, если неудачно, то возвращается false.

Создание базы данных

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

Объектно-ориентированный подход

<?php
$conn = new mysqli("localhost", "root", "mypassword");
if($conn->connect_error){ die("Ошибка: " . $conn->connect_error);
}
// Создаем базу данных testdb2
$sql = "CREATE DATABASE testdb2";
if($conn->query($sql)){ echo "База данных успешно создана";
} else{ echo "Ошибка: " . $conn->error;
}
$conn->close();
?>

Здесь создается база данных testdb2. Если при выполнении команды возникнет ошибка, то мы можем получить информацию о ней через свойство $conn->error.

Процедурный подход

<?php
$conn = mysqli_connect("localhost", "root", "mypassword");
if (!$conn) { die("Ошибка: " . mysqli_connect_error());
}
// Создаем базу данных testdb3
$sql = "CREATE DATABASE testdb3";
if(mysqli_query($conn, $sql)){ echo "База данных успешно создана";
} else{ echo "Ошибка: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

Здесь создается база данных testdb3. Если при выполнении команды возникнет ошибка, то мы можем получить информацию о ней через функцию
mysqli_error(), в которую передается объект подключения.

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

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

CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER);

Объектно-ориентированный подход

<?php
$conn = new mysqli("localhost", "root", "mypassword", "testdb2");
if($conn->connect_error){ die("Ошибка: " . $conn->connect_error);
}
$sql = "CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER);";
if($conn->query($sql)){ echo "Таблица Users успешно создана";
} else{ echo "Ошибка: " . $conn->error;
}
$conn->close();
?>

Если мы обращаемся уже непосредственно к базе данных, то ее имя передается в качетстве четвертого параметра в конструктор :
new mysqli("localhost", "root", "mypassword", "testdb2");.

Процедурный подход

<?php
$conn = mysqli_connect("localhost", "root", "mypassword", "testdb3");
if (!$conn) { die("Ошибка: " . mysqli_connect_error());
}
$sql = "CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER);";
if(mysqli_query($conn, $sql)){ echo "Таблица Users успешно создана";
} else{ echo "Ошибка: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

Если мы подключаемся к конкретной базе данных, то ее имя передается в качетстве четвертого параметра в функцию :
mysqli_connect("localhost", "root", "mypassword", "testdb3").

Введение в типы данных MariaDB

Как сказано ранее, таблицы – это объекты базы данных, где мы будет хранить постоянную информацию. Каждая таблица состоит из колонок. Колонка содержит данные определённого типа.

Самыми распространёнными типами данных в MariaDB являются следующие

• BOOLEAN значение 0 считается false (т.е. ложь), а любые другие данные расцениваются как true (т.е. истина).
• TINYINT (tiny integer, т.е. буквально крошечное целое число), можно использовать как SIGNED (т.е. со знаком), тогда этим типом охватываются числа от -128 до 127, также можно использовать как UNSIGNED (т.е. без знака), тогда в охватываемый диапазон входят целые числа от 0 до 255.
• SMALLINT (small integer, т.е. буквально маленькие целые числа), опять же, если используется с SIGNED, то этим типом охватывается диапазон от -32768 до 32767. Диапазон UNSIGNED от 0 до 65535.
• MEDIUMINT (medium integer, т.е. буквально средние целые числа, с SIGNED это от -8388608 до 8388607. Без знака это диапазон от 0 до 16777215.
• INT (integer, буквально целое число), если используется с SIGNED, охватывает диапазон от -2147483648 до 2147483647, и от 0 до 4294967295 в противном случае.
• BIGINT (big integer, т.е. буквально большое целое число), со знаком это диапазон от -9223372036854775808 до 9223372036854775807. Без знака от 0 до 18446744073709551615.

Помните: В TINYINT, SMALLINT, MEDIUMINT, INT и BIGINT, SIGNED предполагается по умолчанию.

• DOUBLE(M, D), где M – это общее количество цифр, а D – это количество цифр после десятичной точки, представляет собой числа с двойной точностью с плавающей запятой. Если указана UNSIGNED, отрицательные значения не разрешены.

• VARCHAR(M) представляет строку переменной длины, где M – это максимально разрешённая длина колонки в байтах (65,535 в теории). В большинстве случае количество байтов идентично количеству символов, кроме символов, которым может потребоваться до 3 байтов (utf8). Например, испанская буква ñ представляет собой один символ, но использует 2 байта.
• TINYTEXT – это текстовые значения, с максимальной длиной 255 символов. Эффективная максимальная длина меньше, если значение содержит многобайтную кодировку.
• TEXT(M) представляет колонку с максимальной длиной в 65,535 символов. Тем не менее, как и с VARCHAR(M), реальная максимальная длина уменьшается при сохранении многобайтных символов. Если M указана, создаётся самая маленькая колонка типа TEXT, которая достаточно большая для хранения значения длиной в M символов.
• MEDIUMTEXT(M) и LONGTEXT(M) похожи на TEXT(M), разница только в максимально разрешённой длине значения, которая составляет, соответственно 16,777,215 и 4,294,967,295.

Дата и время:

• DATE представляет дату в формате YYYY-MM-DD.
• TIME представляет время в формате HH:MM:SS.sss (чисы, минуты, секунды и милисекунды).
• DATETIME является комбинацией DATE и TIME в формате YYYY-MM-DD HH:MM:SS.
• TIMESTAMP используется для определения момента, когда строка была добавлена или обновлена.

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

Например, имя человека может легко поместиться в VARCHAR(50), в то время как сообщение в блоге потребует тип TEXT (можно указать M под специфические нужды).

Создаём новую базу

Последнее, что нам осталось сделать, — создать новую базу данных. 

Все команды в MySQL называются запросами и пишутся внутри её собственной командной строки. Для перехода в неё пишем команду mysql -u root:

У нас поменялся внешний вид приветствия в командной строке: вместо имени пользователя и текущей папки мы видим строку mysql> — это значит, что мы сейчас общаемся с базой данных и она готова принимать и выполнять команды.Создадим новую базу с названием thecodeDB командой CREATE DATABASE:

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

Убедимся, что база создана, — выполним команду SHOW DATABASES;

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

Создание таблицы с Primary и Foreign ключами

Перед тем как всё-таки перейдём к созданию таблицы, нужно ознакомиться с двумя фундаментальными концепциями реляционных баз данных, это primary и foreign ключи.

Ключ primary (главный) содержит значение, которое индивидуально определяет каждый ряд или запись в таблице. А foreign (буквально «внешний») используется для создания связи между данными в двух таблицах, и контролирования данных, которые могут сохраняться в таблице, где размещён foreign ключ. Обычно для primary и foreign ключей выбирают тип INT.

Для иллюстрации, давайте воспользуемся BookstoreDB и создадим две таблицы с именами AuthorsTBL и BooksTBL как показано ниже. Константа NOT NULL означает, что данное поле требует значение и не может быть NULL (не заданным).

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

Если сейчас мы начнём вводить данные, СУБД не будет знать, для какой базы данных они предназначаются. На сервере может быть множество БД – в какой из них пользователь создаёт таблицы? Чтобы разрешить эту неопределённость используется команда USE, после которой указывается имя базы данных, с который вы собираетесь работать:

USE BookstoreDB;

Посмотрите на изменившееся приглашение командной строки:

Теперь все введённые команды и данные будут относиться к БД BookstoreDB.

Скопируйте целиком и вставьте следующие команды в приглашение командной строки:

CREATE TABLE AuthorsTBL (	AuthorID INT NOT NULL AUTO_INCREMENT,	AuthorName VARCHAR(100),	PRIMARY KEY(AuthorID)
);
CREATE TABLE BooksTBL (	BookID INT NOT NULL AUTO_INCREMENT,	BookName VARCHAR(100) NOT NULL,	AuthorID INT NOT NULL,	BookPrice DECIMAL(6,2) NOT NULL,	BookLastUpdated TIMESTAMP,	BookIsAvailable BOOLEAN,	PRIMARY KEY(BookID),	FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);

Как видим, команда создания таблицы имеет вид

CREATE TABLE имя_таблицы (	имя_колонки1 ТИП ДАННЫХ,	имя_колонки2 ТИП ДАННЫХ
);

Команду можно было вводить построчно или скопировать и вставить за один раз. При построчном вводе СУБД в качестве окончания вводимой команды ожидает точки с запятой (;). Также команду можно было записать в одну строку, например:

CREATE TABLE имя_таблицы (имя_колонки1 ТИП ДАННЫХ, имя_колонки2 ТИП ДАННЫХ);

Никакой разницы нет.

Теперь мы может продолжить и начать вводить данные в AuthorsTBL и BooksTBL.

Связанные статьи

• Как импортировать и экспортировать базы данных в MySQL или MariaDB (100%)
• Как установить веб-сервер Apache с PHP 7, MariaDB/MySQL и phpMyAdmin (LAMP) на Ubuntu (100%)
• 20 команд MySQL (mysqladmin) для администратора базы данных в Linux (100%)
• Как переместить директорию с данными MySQL/MariaDB в новое расположение на Ubuntu 16.10 или 17.04 (100%)
• Как сбросить пароль root для MySQL или MariaDB (100%)
• Ошибка phpMyAdmin «Существует несоответствие между HTTPS, указанным на сервере и клиенте» (РЕШЕНО) (RANDOM — 50%)

Необходимые условия

PHP и установленный веб-сервер (например, Apache).

Подключение к БД с помощью PHP

Создаём файл index.php и прописываем:

$password = ''; // пароль$db = 'mydb'; // название бд$host = 'localhost'; // хост$charset = 'utf8'; // кодировка// Создаём подключение

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

Создание пользователя для доступа к безе данных

CREATE USER bookstoreuser@localhost IDENTIFIED BY 'ЗдесьВашПароль';
GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
FLUSH PRIVILEGES;

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

Проверяем, есть ли база данных на сервере

Некоторые виртуальные серверы уже идут в комплекте с установленной базой данных MySQL, и в этом случае ничего дополнительно устанавливать не придётся. Проверить, если ли MySQL на сервере, можно командой mysql -V:

Сервер ответил, что не знает этой команды, — это значит, что база MySQL не установлена. 

Дальше мы будем использовать команды установщика для Ubuntu, потому что именно эта операционная система стоит на нашем сервере. Если у вас другая версия линукса, замените наши команды установки apt install на свои.

Create and show database

First, let’s see what kind of database you already have. When you type
show databases; in MySQL, you will see this.

means, I already have 3 databases(database1, database2, database3) here.

Now let’s create a new database. Let’s name our database as ‘kim’. This time, you type create database kim;

mysql> create database kim;
Query OK, 1 row affected (0.01 sec)

Then again, type show databases;

Now we have our new database ‘kim’ in your databases.

Выбор, вставка, обновление и удаление рядов

Начнём с заполнения таблица AuthorsTBL. Почему? Нам нужно иметь значения AuthorID перед вставкой записей в BooksTBL.

Выполните следующий запрос к MariaDB:

INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Команда вставки строк в таблицу называется INSERT и имеет общий вид:

INSERT INTO имя_таблицы (имя_поля1, имя_поля2) VALUES ('значение_первого_поля', 'значение_второго_поля');

В результате этой команды была бы вставлена одна новая строка. За один раз можно вставить сразу несколько строк. Команда вставки нескольких строк имеет вид:

INSERT INTO имя_таблицы (имя_поля1, имя_поля2) VALUES ('значение первого поля в первой строке', 'значение второго поля в первой строке'), ('значение первого поля во второй строке', 'значение второго поля во второй строке');

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

INSERT INTO AuthorsTBL (AuthorID, AuthorName) VALUES ('1', 'Agatha Christie'), ('2', 'Stephen King'), ('3', 'Paulo Coelho');

Но поскольку в таблице AuthorsTBL при характеристике типа AuthorID был установлен флаг AUTO_INCREMENT, который означает автоматическую установку уникального номера, то мы смогли из нашей команды убрать информацию, относящуюся к AuthorID. СУБД всё сделала сама: сама заполнила эти поля значениями 1, 2, 3.

Чтобы в этом убедиться, давайте посмотрим на нашу таблицу командой SELECT:

SELECT * FROM AuthorsTBL;

Команда SELECT используется для получения (выбора) записей из таблицы.

Общий синтаксис команды:

SELECT поле1, поле2 FROM таблица WHERE условие;

Как мы могли убедиться, часть WHERE условие; является опциональной. Если она не определена, то выбираются все строки. После SELECT можно указать название полей, которые вас интересуют, например:

SELECT AuthorID, AuthorName FROM AuthorsTBL;

Символ звёздочки (*) означает сразу все поля, т.е. вывод последней команды полностью идентичен  SELECT * FROM AuthorsTBL;

Можно указать (через запятую) любой набор полей по желанию:

SELECT AuthorName FROM AuthorsTBL;

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

SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Теперь сделаем вставку (INSERT) записей в таблицу BooksTBL, мы будем использовать соответствующий AuthorID автора для каждой книги. Значение 1 в BookIsAvailable говорит о наличии книги, а 0 – об её отсутствии:

INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);

Посмотрим содержимое таблицы BooksTBL:

SELECT * FROM BooksTBL;

Мы допустили ошибку, у книги The Alchemist должна быть цена 22.75. Для изменения данных в таблице используется команда UPDATE. Её общий синтаксис:

UPDATE таблица SET столбец=новое значение WHERE условие;

Для того, чтобы книги, у которой BookID равен 6 присвоить столбцу BookPrice новое значение 22.75 выполним следующую команду:

UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;

SELECT * FROM BooksTBL WHERE BookID=6;

Как можно убедиться, данные изменились:

При желании удалить запись (строку), можно воспользоваться командой DELETE. Синтаксис этой команды:

DELETE FROM таблица WHERE условие

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

DELETE FROM BooksTBL WHERE BookID=6;

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

Показ всех баз данных

Чтобы увидеть все доступные базы данных, можно использовать любую из этих команд, они равнозначны:

SHOW DATABASES;
# ИЛИ
SHOW SCHEMAS;

Подключение к базе данных

Запросы к базе данных можно передавать из большинства языков программирования, например, из PHP, а также возможно напрямую. Для непосредственного подключения к СУБД MariaDB введите:

mysql -u root -p

После появления запроса, введите пароль, приглашение оболочки Bash смениться на приглашение MariaDB:

Именно сюда мы будет вводить последующие команды.

Еще одна программа на PHP

И вот еще одна программа на PHP для добавления новых записей в таблицу, попробуйте.

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

create table book(Acc_no INTEGER(20),Title varchar(50),Author varchar(50),Publisher varchar(50),Edition INTEGER(3));

<html>
<head><title>Deatails</title></head>
<body>
<form action = "phpfile.php" method=”post”>
<h1>ENTER DETAILS</h1>
ACCNO<input type="text"name="Acc_no"> <br>
<br>
TITLE<input type="text"name="Title"> <br>
<br>
AUTHOR<input type="text"name="Author"> <br>
<br>
EDITION<input type="text"name="Edition"> <br>
<br>
PUBLICATION<input type="text"name="Publisher"> <br>
<br>
<input type="submit"value="SUBMIT">
<input type="reset"value="CLEAR">
</form>
</body>
</html>

Читайте нас в Telegram, VK и Яндекс.Дзен

What is the ‘Database’ and ‘ Table’?

‘Database’ is the place where you save the information, and the ‘Table’ is where you can actually organize the information.
According to my teacher, you can think of database as your room and the table is like your wardrobe. if you try to put all your cloths in the room without organizing, the room would get so messy, that’s the when you need a wardrobe, so you can organize the cloths by its type or use. In MySQL, cloths will be the information!

Дополнительные подсказки MySQL

Для очистки приглашения MariaDB, можно воспользоваться сочетанием клавиш Ctrl+l или набрать следующую команду и нажать Enter:

\! clear

Если вы ещё не догадались, последовательность символов \! отправляет последующую команду в шелл Linux (а не в СУБД) и выводят их результат на экран. После \! можно использовать любые команды Bash.

MariaDB [BookstoreDB]> \! pwd
/home/mial
MariaDB [BookstoreDB]> \! ls -l
итого 48
drwxr-xr-x 7 mial mial 4096 дек 25 09:13 bin
-rw-r--r-- 1 mial mial 8980 дек 22 08:40 examples.desktop
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Видео
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Документы
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Загрузки
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Изображения
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Музыка
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Общедоступные
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Рабочий стол
drwxr-xr-x 2 mial mial 4096 дек 22 08:58 Шаблоны

Для анализа конфигурации данной таблицы сделайте:

SHOW COLUMNS IN [имя таблицы];

SHOW COLUMNS IN BooksTBL;

Для SHOW COLUMNS IN можно использовать сокращение DESC:

DESC BooksTBL;

Быстрая проверка обнаружила, что поле BookIsAvailable может иметь значение NULL. Поскольку мы не хотим разрешать это, мы изменим таблицу командой ALTER:

ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

При повторной проверке теперь YES на пересечении BookIsAvailable и Null смениться на NO.

Используем phpMyAdmin

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

После внесения нужных изменений нажмите «Вперед» и выберите, куда хотите поместить созданный файл. Все, экспорт базы данных был успешно выполнен.

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

Выберите файл для импорта на вашем компьютере и проверьте настройки. Скорее всего, они подойдут для импортирования вашего файла, но при желании их можно изменить. Нажмите кнопку «Вперед» — и будет выполнен импорт файла. Вы увидите надпись вроде такой:

Импорт успешно завершён, выполнено 32 запроса.

Ниже в красной рамке могут идти сообщения о возникших ошибках (например, о дублировании).

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

Код для создания таблицы при загрузке сайта

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

Как было указано в необходимых условиях, в качестве хоста будет использоваться Apache2, поэтому выполняем такие команды:

cd /var/www/html
vim sqltest.php

С их помощью создается файл и открывается редактор Vim.

Копируем и вставляем в редактор следующий код:

В строках 7, 8 и 9 объявляются переменные, в которых сохраняются имя пользователя, пароль и localhost. Дальше они задействуются в строке 10 и передаются в качестве параметров в команду mysqli_connect, которой инициируется соединение с сервером MySQL. Если соединение установить не удалось, для отображения ошибки используется оператор if и mysqli_error(). Затем пишется запрос на создание таблицы, который присваивается переменной sql. И запускается запрос для выбора базы данных test. После чего запускается mysqli_query для создания таблицы посредством передачи в качестве параметров переменных sql и connection.

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

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

И выполняем в терминале следующую команду:

php -S localhost:8000

Теперь сообщение об успешном создании таблицы будет видно.

Проверим, выполнив в терминале команду:

use test;
desc student;

Создание и удаление базы данных

Последнее обновление: 04.05.2018

Создание базы данных

Для создания базы данных используется команда . Она имеет следующий синтаксис:

CREATE DATABASE [IF NOT EXISTS] имя_базы_даных;

В конце команды указывается имя базы данных.

Первая форма CREATE DATABASE имя_базы_даных пытается создать базу данных, но если такая база данных уже существует, то операция возвратит ошибку.

Вторая форма CREATE DATABASE IF NOT EXISTS имя_базы_даных пытается создать базу данных, если на сервере отсутствует бд с таким именем.

Например, в MySQL Workbench CE (или в MySQL Command Line Client) выполним следующую команду:

CREATE DATABASE productsdb;

Она создаст на сервере бд productsdb.

Установка базы данных

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

USE productsdb;

Удаление базы данных

Для удаления базы данных применяется команда , которая имеет следующий синтаксис:

DROP DATABASE [IF EXISTS] имя_базы_даных;

Первая форма DROP DATABASE имя_базы_даных пытается удалить базу данных, но если такая база данных отсутствует на сервере, то операция возвратит ошибку.

Вторая форма DROP DATABASE IF EXISTS имя_базы_даных пытается удалить базу данных, если на сервере имеется бд с таким именем.

Например, удалим выше созданную базу данных productsdb:

DROP DATABASE productsdb;

Что понадобится

Чтобы всё получилось, нам будут нужны:

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

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

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

Теперь мы имеем готовую БД и таблицу для работы с ней.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Вход под другим пользователем

exit

или нажмите Ctrl + d

mysql –u имя_пользователя -p

В нашем случае:

mysql -u bookstoreuser -p

После ключа -p можно сразу указать пароль. Между ключами -u и -p и следующими за ними именем пользователя и паролем необязательно ставить пробел.

Т.е. можно так:

mysql -ubookstoreuser -p

Что дальше

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

Выборка данных по параметру + защита

Мы можем выбирать определённые данные из таблицы. Например, мы можем выбрать все записи, где pass = 123, и тому подобные.

$login = 'Andre'; // Подставиться вместо знака вопроса$query = $pdo -> prepare($sql); // Возвращает объектexecuteзнак вопроса// Также перебираем массив, но теперь в массиве только те строки, где login = Andre

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

$login = 'Andre'; // Подставится вместо :login// Формируем запрос с помощью ключа$query = $pdo -> prepare($sql); // Возвращает объект// В кавычках нужно указать ключ, который мы указали в запросе.// А после передать значение, которое должно подставится.$login// Также перебираем массив, но теперь в массиве только те строки, где login = Andre 

Можно указать несколько ключей, а в execute перечислить замены для них через запятую в формате ‘ключ’ => значение

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

Используем консоль

Для экспорта базы данных введите вот такую команду:

mysqldump -u имя_пользователя -p название_БД > data-dump.sql

в которой нужно ввести имя пользователя с необходимым доступом, название нужной вам базы данных, а также data-dump.sql – файл в текущей директории, куда будут сохранены данные.

После ввода этой команды вы не увидите никакого вывода на экране, однако вы можете проверить содержимое файла data-dump.sql для того, чтобы убедиться, что теперь он является резервной копией вашей базы данных.

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

-- MySQL dump 10.13 Distrib 5.7.16, for Linux (x86_64)
--
-- Host: localhost Database: database_name
-- ------------------------------------------------------
-- Server version 5.7.16-0ubuntu0.16.04.1

Если во время процесса экспорта будут какие-нибудь ошибки, утилита mysqldump выведет на экран сообщение о них.

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

Сначала подключитесь к базе данных в качестве root-пользователя (либо другого пользователя, который сможет создать новую базу данных):

После того, как вы подключились к консоли MySQL, создайте новую базу данных (в данном случае new_database):

mysql> CREATE DATABASE new_database;

После этого на экране появился следующий вывод:

Output
Query OK, 1 row affected (0.00 sec)

Теперь для выхода из консоли MySQL нажмите CTRL+D. Далее переходите к самому импорту. Сделать это можно, введя вот такую команду:

$ mysql -u имя_пользователя -p new_database < data-dump.sql

Команда очень похожа на команду экспорта, вам нужно ввести имя пользователя, название новой базы данных, куда вы будете импортировать данные (в качестве примера new_database), и название самого файла, который вы собираетесь импортировать (data-dump.sql).

Если команда выполнена корректно, то никакого вывода на экране вы не увидите; на экране могут отобразиться только сообщения о каких-то ошибках. Как и в случае с экспортом, проверить, точно ли все прошло успешно, вы можете путем подключения к MySQL и просмотра данных. Сделать это можно, к примеру, используя команды USE и SHOW. Команда use определяет, какая база данных будет использоваться в дальнейших запросах. Введите:

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

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

Допустим, нам нужно посмотреть, список таблиц в базе. Для этого вводим:

Хотите увидеть список столбцов в какой-то определенной таблице? Используйте команду SHOW COLUMNS FROM и название нужно вам таблицы:

SHOW COLUMNS FROM название_таблицы

Статистику по работе сервера можно получить в ответ на команду:

mysql> SHOW GLOBAL STATUS;

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Появление возможных ошибок, и как с ними справиться

1. При выполнении команды создания проверяйте, те ли кавычки используете. Ведь ` и ‘ — это разные знаки.
2. Если при выполнении программы есть какая-либо ошибка в SQL-запросе, связанная с форматом, то при присваивании $sql

$sql=”CREATE TABLE student(“.“USN INT NOT NULL,”.“Name VARCHAR(100) NOT NULL,”.“Marks VARCHAR(40) NOT NULL);”;

поставьте в конце запроса вместо точки точку с запятой.

3. Если возникли проблемы с командами MySQLi, выполните следующие команды:

Затем запишите свою версию PHP. Для этого в первой из следующих четырех команд замените x.x на свою версию PHP:

sudo apt-get install phpx.x-mysqli
sudo service apache2 restart
sudo phpenmod mysqli
sudo service apache2 restart

Так, если у вас версия PHP 7.4, то команда будет sudo apt-get install php7.4-mysqli.

4. Каждый раз, когда заходите на свой сайт на PHP, запускайте в каталоге, где находится программа на PHP, следующую команду:

php -S localhost:8000

И при получении какой-либо ошибки, например такой:

Запускайте эту команду:

И после выполнения php -S localhost:8000 он заработает. Так как процесс, задействующий этот порт, будет уничтожен и его можно будет использовать.

Выборка данных

Здесь я сделаю небольшое отступление. Выбирать данные можно:

• Ассоциативным массивом

PDO::FETCH_ASSOC — возвращает массив с названиями столбцов в виде ключей.

• Объектом

PDO::FETCH_OBJ — возвращает анонимный объект со свойствами, соответствующими именам столбцов.

Fetch() — каждый раз извлекает следующую строку.

// Создаём запрос// Перебираем способом ассоциативного массива

Здесь мы обращаемся к массиву $row по индексу login.

// Перебираем способом объекта

Здесь мы обращаемся к объекту $row по login.

Create Table & desc

I am going to make a table in the database named kim. So we have to type
use databaseName; and to see if there’s any column type show tables;

mysql> use kim;
Database changed
mysql> show tables;
Empty set (0.01 sec)

There’s nothing so you see empty.

So let’s create one. I am going to put id, name, phone, age in my table, and name my table ‘student’.

mysql> create table student(
 -> id varchar(8),
 -> name varchar(10),
 -> phone varchar(13),
 -> age int
 -> );
Query OK, 0 rows affected (0.04 sec)

Next to the columns you’re seeing ‘varchar’ and ‘int’.
Varchar means the data type of its column is letters. And the numbers in the next to the varchar means it’s maximum length. So there’s id varchar(8) , It means if you put more than 8 letters in the id, there will be an error.
Int means the data type of its column is number. But as you can see phone column is also set with varchar. Isn’t the phone ‘number’ is number?
Why? It’s easy if you think int as the numbers that you can calculate. We are definitely not gonna subtract the phone numbers, so that’s why we use varchar here.

To see the result of what we’ve done, put desc databaseName.tableName;

There, we made these database and table very simply.

Создание новой базы данных

Для создание новой базы данных с название BooksDB, введите в приглашение командной строки MariaDB следующую команду:

CREATE DATABASE BookstoreDB;

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

Как показать все таблицы в базе данных

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

SHOW TABLES

Также можно непосредственно указать интересующую базу данных после FROM или IN. Например, я хочу увидеть таблицы в базе данных db_softocracy.ru, тогда я выполняю:

SHOW TABLES IN `db_softocracy.ru`;

Вставка данных по параметру + защита

$login = 'Lorem'; // Подставиться вместо :login$pass = 444; // Подставиться вместо :pass// Формируем запрос с помощью ключей$query = $pdo -> prepare($sql); // Возвращает объект// В кавычках нужно указать ключи, которые мы указали в запросе.// А после передать значение, которое должно подставится.$login, 'pass' => $pass

Теперь можем проверить таблицу. Видим, что всё сработало правильно.

Удаление базы данных

База данных удаляется командой DROP DATABASE:

DROP DATABASE имя_БД;

DROP DATABASE BookstoreDB;

Если вы не уверены, существует ли база данных, которую вы хотите удалить, то в этой ситуации можно использовать конструкцию:

DROP DATABASE IF EXISTS имя_БД;

DROP DATABASE IF EXISTS BookstoreDB;

Если БД существует, то она будет удалена, если она отсутствует, то данная команда не вызовет ошибку.

Необходимый минимум для связи с сервером

Чтобы сервером можно было управлять со своего домашнего компьютера, нам понадобится настроить SSH — программу для безопасной связи между компьютерами по сети. Так как почти все серверы в интернете работают на линуксе без графической оболочки, команды для управления будем вводить в терминале (или командной строке, кому какое название больше нравится).

Перед тем как продолжить, проверяем, что у нас работает доступ к серверу по SSH:

Базу данных необязательно устанавливать на сервер — на домашнем компьютере она тоже будет работать. Самый простой способ это сделать — поставить виртуальную машину с Ubuntu и установить базу там. Единственный минус — удалённо поработать с такой базой будет сложно, но для тренировки и изучения SQL-запросов этого хватит.

Обновление данных по параметру + защита

$id = 7; // Подставиться вместо :id$pass = 222; // Подставиться вместо :pass// Формируем запрос с помощью ключей$query = $pdo -> prepare($sql); // Возвращает объект// В кавычках нужно указать ключи, которые мы указали в запросе.// А после передать значение, которое должно подставится.

Теперь можем проверить таблицу. Видим, что всё сработало правильно.

Создание таблицы при загрузке сайта

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

Вот такой ответ должен прийти:

Теперь установим сервер MySQL. Для этого открываем терминал и запускаем:

sudo apt-get install mysql-server

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

Проверяем, установлен ли MySQL, этой командой:

Если установка прошла успешно, выводится примерно следующее:

Теперь для доступа к базе данных с использованием программы на PHP нужно передать в качестве аргументов в SQL-запросе имя пользователя и пароль. Поэтому создадим пользователя с паролем и предоставим ему права доступа к базе данных.

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

Теперь предоставим пользователю разрешения на доступ к базе данных, а заодно убедимся, что эти права доступа отображаются (для этого задействуем команду flush):

И для доступа к MySQL в Google Cloud:

Для привязки ко всем адресам запускаем это:

#bind-address = 127.0.0.1

Для создания нового пользователя и пароля нужно выполнить указанные выше команды. После добавления нового пользователя выходим и перезапускаем сервер MySQL:

exit
service mysql restart

Обратите внимание: в случае с PHP версией 7+ для правильного выполнения команд MySQL необходимо использовать расширение MySQLi. Функции MySQLi позволяют получать доступ к серверам баз данных MySQL.

Для установки расширения MySQLi запускаем:

Sudo apt Install php-mysqli

Теперь все готово к выполнению программ на PHP с интегрированием MySQL. Переходим к решению сформулированной выше задачи.

Первым делом создадим базу данных на сервере MySQL. Для этого подключимся к серверу MySQL:

Для того, чтобы создать таблицу, сначала нужно создать базу данных. Следующей командой создаем базу данных с названием «test»:

create database test;

Дальше эту базу данных нужно выбрать:

Устанавливаем базу данных

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

Для установки обеих частей выполним команду apt install mysql-server mysql-client. Сервер соберёт данные об установке и скажет, что ему понадобится около 250 мегабайт на диске — для установки нужно будет ввести английскую букву «y» и нажать энтер:

Когда установка закончится, снова проверим, знает ли сервер о том, что у него теперь есть MySQL, командой mysql -V:

Сервер ответил, что у нас установлена версия 8.0.29, — это значит, что мы всё сделали правильно.

Объединение вывода из нескольких таблиц

Если вы хотите объединить два (или более) полей, в том числе разных таблиц, вы можете использовать оператор CONCAT. Например, допустим мы хотим получить результат, состоящий из полей имени книги и автора, в виде “The Alchemist (Paulo Coelho)” и другой клонки с ценой.

Команду можно прочитать как SELECT (выбрать) поле1, поле2, поле3 FROM (из) таблицы1 JOIN (объединённой с) таблицей2 ON (по общим полям) таблица1.поле = таблица2.поле):

SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Как мы видим, CONCAT позволяет объединить несколько строковых выражений, разделённых запятой. Также для представления результата объединения мы выбрали псевдоним Description.

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

Создание баз данных, таблиц и авторизированных пользователей

Под базой данных понимается набор организованной информации. MariaDB является системой управления базами данных (СУБД). Для создания, модификации и управления данными используется язык структурированных запросов, по-английски structured query language, т.е. SQL. Именно основы этого языка мы и будем осваивать в этой статье. Чтобы не путаться, помните, что MariaDB использует термины «база данных» и «схема» как взаимозаменяемые.

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

Заключение

Кстати, полезную информацию о базах данных я также нашел в Справочном центре Timeweb.

Заключение

В этой статье мы разобрались, как выполнять DML операции и как создать базу данных, таблицы, отдельного пользователя базы данных в MariaDB. Дополнительно, мы рассмотрели несколько подсказок, которые могут сделать вашу жизнь системного администратора / администратора базы данных проще.

Заключение

Как видите, работать с PDO довольно легко. Теперь вы сами можете создавать веб-сайты с базами данных.

Надеюсь, что вам понравилась статья, и вы получили новые знания.

Всем спасибо за внимание!