Начало работы с MySQL
Введение
MySQL — это популярный сервер баз данных, используемый в разных приложениях. SQL означает язык структурированных запросов — (S)tructured (Q)uery (L)anguage, который MySQL использует для коммуникации с другими программами. Сверх того, MySQL имеет свои собственные расширенные функции SQL для того чтобы обеспечить пользователям дополнительный функционал. В этом документе мы рассмотрим как провести первоначальную установку MySQL, настроить базы данных и таблицы, и создать новых пользователей. Давайте начнем с установки.
Установка MySQL
Сначала убедитесь что MySQL установлен на вашу систему. В случае если вам требуется определенная функциональность MySQL, убедитесь, что установлены необходимые USE-флаги, так как они помогут в тонкой настройке инсталляции.
По завершении установки, вы увидите следующее уведомление:
Код Сообщение einfo MySQL
You might want to run: "emerge --config =dev-db/mysql-" if this is a new install.
Так как это новая установка, мы запустим эту команду. Вам надо нажать по запросу во время конфигурации базы данных MySQL. В процессе конфигурации устанавливается основная база данных MySQL, которая содержит служебную информацию, такую как базы данных, таблицы, пользователи, разрешения и т.д. В процессе конфигурации рекомендуется чтобы вы изменили свой пароль root так быстро, как это возможно. Мы определенно это сделаем, иначе кто-нибудь сможет волей случая появиться и взломать сервер MySQL, настроенный по умолчанию.
* MySQL DATADIR is /var/lib/mysql * Press ENTER to create the mysql database and set proper * permissions on it, or Control-C to abort now... Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, issue the following commands to start the server and change the applicable passwords: /etc/init.d/mysql start /usr/bin/mysqladmin -u root -h pegasos password 'new-password' /usr/bin/mysqladmin -u root password 'new-password' Depending on your configuration, a -p option may be needed in the last command. See the manual for more details.
ЗаметкаЕсли предыдущая команда не выполнится из-за того, что имя хоста установлено в localhost, измените его на другое имя, например gentoo. Обновите файл /etc/conf.d/hostname и перезапустите /etc/init.d/hostname.
Некоторая нехарактерная для ebuild-файлов информация MySQL удалена отсюда, чтобы содержать этот документ настолько последовательным, насколько возможно.
ВажноНачиная с mysql-4.0.24-r2, пароли вводятся во время этапа конфигурации, что делает пароль root более надежным.
Сценарий конфигурации уже вывел команды, которые нам нужно запустить, чтобы настроить наш пароль, поэтому нам сейчас надо их выполнить.
Если вы используете OpenRC, выполните данную команду:
* Re-caching dependency info (mtimes differ)... * Starting mysqld (/etc/mysql/my.cnf) ...
Если вы используете systemd, вместо этого используйте следующую команду:
После этого установите пароль root:
Теперь вы можете проверить, что пароль root был успешно настроен, попытавшись войти на MySQL-сервер:
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 4.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Параметр указывает пользователя, который будет выполнять вход. Параметр указывает хост. Обычно это будет , если только вы не настраиваете удаленный сервер. И, наконец, сообщает клиенту mysql что вы будете вводить пароль для доступа к базе данных
Обратите внимание на приглашение. Это то место, где вы будете вводить все ваши команды
Теперь, когда мы в командной строке mysql в качестве пользователя root, мы можем начать настраивать нашу базу данных.
ВажноУстановка mysql по умолчанию приемлема для систем разработки. Для более безопасных значений по умолчанию можно запустить /usr/bin/mysql_secure_installation
Запрос на вставку строки
Простой запрос, который вставляет строку со столбцами 111, 222 и 333 выглядит так:
INSERT INTO table1 (a, b, c) VALUES (111, 222, 333);
Еще один способ сделать то же самое:
INSERT INTO table1 SET a=111, b=222, c=333;
Столбцы, которые вы не перечислите заполняются значениями по умолчанию, которые вы предусматриваете при создании таблицы, даже если это просто NULL.
У таблиц обычно есть поле с первичным ключом (PRIMARY KEY) таблицы. Если этому полю установлено значение AUTOINCREMENT т.е. оно заполняется автоматически, то в таком случае вы не должны его перечислять в списке столбцов оператора INSERT.
Вставка без перечисления столбцов
Если количество значений, которые мы вставляем = количеству столбцов в таблице, то можно не перечислять столбцы, и наш запрос может выглядеть так:
INSERT INTO table1 VALUES (111, 222, 333);
Этот способ крайне не рекомендуется. Дело в том, что со временем вы можете менять таблицы, например добавлять в них новые столбцы, а это значит, что все запросы записанные таким способом просто перестанут работать и вам придется менять их по всему вашему приложению.
Поэтому, навсегда забываем этот способ. Я его привел, только чтобы вы так не делали.
Нормализация
Представление о взаимоотношениях данных и наиболее эффективном способе их организации называется нормализацией. Нормализация заключается в разделении данных на основе логических взаимоотношений с целью минимизировать дублирование данных. Повторяющиеся данные понапрасну расходуют дисковое пространство сервера и затрудняют их обслуживание. При внесении изменений в повторяющиеся данные есть риск пропустить какие-то из них, что может привести к возникновению несогласованностей в базе данных.
С другой стороны, лучшее – враг хорошего: когда данные хранятся по частям в отдельных таблицах, это может потребовать слишком больших накладных расходов на их извлечение, да и запросы могут получаться чересчур замысловатыми. Главная цель – найти золотую середину.
Размещение всех данных в одной таблице может показаться заманчивым, однако такой способ приводит к напрасному расходованию пространства в базе данных и делает утомительной операцию обновления данных. С каждой новой покупкой все сведения о покупателе записываются повторно. Для каждой книги можно указать не больше двух авторов. Кроме того, если покупатель переедет и поменяет адрес, это потребует внести изменения в каждую запись, связанную с этим покупателем.
Создание и удаление таблиц
Последнее обновление: 04.05.2018
Создание таблицы
Для создания таблиц используется команда CREATE TABLE. Эта команды применяет ряд операторов, которые определяют столбцы
таблицы и их атрибуты. Общий формальный синтаксис команды CREATE TABLE:
CREATE TABLE название_таблицы (название_столбца1 тип_данных атрибуты_столбца1, название_столбца2 тип_данных атрибуты_столбца2, ................................................ название_столбцаN тип_данных атрибуты_столбцаN, атрибуты_уровня_таблицы )
После команды CREATE TABLE идет название таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть
уникальным. Затем в скобках перечисляются названия столбцов, их типы данных и атрибуты. В самом конце можно определить атрибуты для всей таблицы.
Атрибуты столбцов, а также атрибуты таблицы указывать необязательно.
Создадим простейшую таблицу. Для этого выполним следующий скрипт:
CREATE DATABASE productsdb;
USE productsdb;
CREATE TABLE Customers
(
Id INT,
Age INT,
FirstName VARCHAR(20),
LastName VARCHAR(20)
);
Таблица не может создаваться сама по себе. Она всегда создается в определенной базе данных. Вначале здесь создается база данных productsdb.
И затем, чтобы указать, что все дальнейшие операции, в том числе создание таблицы, будут производиться с этой базой данных, применяется команда
USE.
Далее собственно идет создание таблицы, которая называется Customers. Она определяет четыре столбца: Id, Age, FirstName, LastName. Первые два столбца представляют идентификатор клиента и его возраст и имеют тип , то есть будут хранить числовые значения.
Следующие столбцы представляют имя и фамилию клиента и имеют тип , то есть представляют строку длиной не более 20 символов.
В данном случае для каждого столбца определены имя и тип данных, при этом атрибуты столбцов и таблицы в целом отсутствуют.
И в результате выполнения этой команды будет создана база данных productsdb, в которой будет создана таблица Customers.
Переименование таблиц
Если после создания таблицы мы захотим ее переименовать, то для этого нужно использовать команду RENAME TABLE,
которая имеет следующий синтаксис:
RENAME TABLE старое_название TO новое_название;
Например, переименуем таблицу Customers в Clients:
RENAME TABLE Customers TO Clients;
Полное удаление данных
Для полного удаления данных, очистки таблицы применяется команда TRUNCATE TABLE. Например, очистим таблицу Clients:
TRUNCATE TABLE Clients;
Удаление таблиц
Для удаления таблицы из БД применяется команда DROP TABLE, после которой указывается название удаляемой таблицы. Например,
удалим таблицу Clients:
DROP TABLE Clients;
НазадВперед
Синтаксис
Основной синтаксис для создания таблицы может быть дан с помощью:
CREATE TABLE table_name ( column1_name data_type constraints, column2_name data_type constraints, .... );
Чтобы понять этот синтаксис, давайте создадим таблицу в нашей демонстрационной базе данных demo. Введите следующую инструкцию в инструменте командной строки MySQL и нажмите enter:
CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE );
блок 1
Приведенный выше пример создает таблицу с четырьмя столбцами id, name, birth_date и phone
Обратите внимание, что за каждым именем столбца следует объявление типа данных; в этом объявлении указывается, какой тип данных будет хранить столбец: целое число, строка, дата и т.д
Некоторые типы данных могут быть объявлены с параметром длины, который указывает, сколько символов можно сохранить в столбце. Например, VARCHAR(50) может содержать до 50 символов.
Создание БД и таблиц с использованием phpMyAdmin
Бывает полезно иметь возможность создавать БД и таблицы напрямую в PHP. Но часто проще использовать phpMyAdmin (или любую иную
утилиту администрирования MySQL), которая является стандартной на большинстве вэб-хостов и в XAMPP. Скриншот ниже показывает, как
создавать БД и таблицы с применением phpMyAdmin.
Начинаем со входа в phpMyAdmin. Часто адрес — тот же, что и для вашего MySQL-сервера (например, «http://mysql.myhost.com»), с теми же username
и password. В XAMPP адрес: http://localhost/phpmyadmin/
Если вы вошли, просто введите имя БД и нажмите кнопку «Create»:
На некоторых хостах БД уже могут быть созданы, и у вас может не быть прав создавать новые. Если это так, вы, очевидно, можете
использовать только уже назначенную БД.
Для создания таблицы щёлкните вкладку «Databases» и выберите БД, щёлкнув на ней:
Затем появится бокс «Create new table in database», где вы вводите имя таблицы и количество столбцов и нажимаете «Go»:
Далее вы можете именовать столбцы и указать типы данных etc., как в предыдущем примере с SQL.
Заметьте, что здесь мы так же устанавливаем «id» как PRIMARY KEY
используем AUTO_INCREMENT (A_I).
Теперь у вас создана собственная БД и таблица. В следующих уроках мы разберёмся, как вставлять, запрашивать и удалять данные в БД.
Вставка строк, некоторые из которых уже существуют в целевой таблице
Существование строк определяется по значению уникальных ключей. В зависимости от ситуации мы можем выбрать разные способы поведения при совпадении значений уникальных столбцов.
Игнорирование INSERT IGNORE INTO
Например если мы вставляем строку с PK = 1, и при этом в таблице уже есть PK = 1 то MySQL выдаст ошибку:
/* ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' */
Выполнение запроса на этом прервется, однако нам в некоторых случаях хотелось бы просто вставить данные, игнорируя ошибки. В этом нам поможет INSERT IGNORE INTO:
INSERT IGNORE INTO table1 (a, b, c) VALUES
(1, 222, 333),
(2, 555, 666),
(3, 555, 333);
Просто добавляем IGNORE в наш запрос и ошибки будут игнорироваться
Вставка с заменой существующих значений REPLACE INTO
REPLACE работает также INSERT, но если совпадают уникальные ключи, то старая строка (или строки!) удаляется до вставки новой.
В таком случае наш пример выглядит следующим образом:
REPLACE INTO table1 (a, b, c) VALUES
(1, 222, 333),
(2, 555, 666),
(3, 555, 333);
Обновление некоторых полей, при существовании строк ON DUPLICATE KEY UPDATE
При совпадении ключей, мы можем также заменить некоторые или все поля в строке.
Наш запрос будет выглядеть так:
INSERT INTO table1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=VALUES(c);
В данном примере если у нас какой-то уникальный ключ совпадает, то мы не производим вставку, а обновляем существующую строку или строки путем присваивания столбцу «c» значения, которое у нас перечислено в VALUES.
Иными словами, если ключ совпадает, то мы просто обновим данные столбца «с» а остальные столбцы трогать не будем.
Иногда нам нужно при совпадении ключей обновить все значения. Этом можно сделать просто перечислив все столбцы:
INSERT INTO table1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c);
При обновлении столбцов мы также можем использовать разные выражения, например:
INSERT INTO table1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Подробнее про ON DUPLICATE KEY UPDATE
Удаление таблицы в базе данных с помощью PHP (PDO)
<?php
$server = «localhost»;
$user = «root»;
$password = «MySafePass4!»;
$db_name = «Bookstore»;
try {
// Открываем соединение
$db = new PDO(«mysql:host=$server;dbname=$db_name», $user, $password);
// Создание исключения при ошибке
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Запрос на удаление таблицы
$sql = «DROP TABLE books»;
// Выполняем запрос
$db->exec($sql);
echo «Таблица успешно удалена!»;
}
catch(PDOException $e) {
echo «Ошибка при удалении таблицы в базе данных: » . $e->getMessage();
}
// Закрываем соединение
$db = null;
?>
|
1 |
<?php $server=»localhost»; $user=»root»; $password=»MySafePass4!»; $db_name=»Bookstore»; try{ // Открываем соединение $db=newPDO(«mysql:host=$server;dbname=$db_name»,$user,$password); // Создание исключения при ошибке $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); // Запрос на удаление таблицы $sql=»DROP TABLE books»; // Выполняем запрос $db->exec($sql); echo»Таблица успешно удалена!»; } catch(PDOException$e){ echo»Ошибка при удалении таблицы в базе данных: «.$e->getMessage(); } // Закрываем соединение $db=null; ?> |
Сложные mysql запросы
Как уже упоминалось раньше, сложные mysql запросы, работают более, нежели с одной таблицей БД. Данные mysql запросы, мы будем рассматривать более в индивидуальном порядке, так как они сложные и их будет немного.
SELECT DISTINCT last_name FROM person, address WHERE person.adress_no = address.address_no AND city LIKE ‘L%’;
или
SELECT DISTINCT last_name FROM person p, address adr WHERE p.adress_no = adr.address_no AND city LIKE ‘L%’;
Выводит все уникальные фамилии людей (last_name), которые живут в городе с названием на букву L. (предполагаем, что в таблице address есть поля address_no, city).
Данные примеры сложных mysql запросов, выведут один и тот же результат. Запросы не очень то и сложные, нужно только указать имя таблицы БД, а потом, через точку указать поле таблицы. Или же можно, как во втором примере, дать короткие имена таблицам (p для person, adr для address). Результат запросов будет один и тот же.
SELECT heroes.char_name, heroes.count, char_templates.ClassName FROM char_templates, heroes WHERE char_templates.ClassId = heroes.class_id Order by char_templates.ClassName;
или
SELECT char_name, count, ClassName FROM heroes left join char_templates on heroes.class_id=char_templates.ClassId;
Берем из таблицы heroes поле char_name, из heroes поле count, из таблицы char_templates поле ClassName, где char_templates.ClassId и heroes.class_id имеют общий идентификатор и сортируем запрос по имени класса героев.
Таким же образом, можно подавать сложные mysql запросы с помощью update, insert, delete и др.
69
td69
Дальше: CAPTCHA на PHP: реализация скрипта каптчи на php
Приоритет вставки INSERT LOW_PRIORITY / HIGH_PRIORITY
Установление приоритета нужно для решение проблем с конкурентными вставками. При вставках происходит блокировка строк и если 2 INSERT запроса требуют блокировки одних и тех же строк, для своего выполнения, то иногда может потребоваться повысить или понизить приоритет некоторых запросов, по отношению к другим. Это можно сделать указав приоритет LOW_PRIORITY или HIGH_PRIORITY
Наш запрос будет выглядеть так для LOW_PRIORITY:
INSERT LOW_PRIORITY INTO table1 (a, b, c) VALUES(1, 2, 3);
HIGH_PRIORITY:
INSERT HIGH_PRIORITY INTO table1 (a, b, c) VALUES(1, 2, 3);
Помогла ли Вам эта статья?
Да
Нет
Создание таблицы
Синтаксис:
> CREATE TABLE <table> (<field1> <options1>, <field2> <options2>) <table options>
Пример:
> 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=utf-8;
* где table — имя таблицы (в примере users_rights); field1, field2 — имя полей (в примере создается 3 поля — id, user_id, rights); options1, options2 — параметры поля (в примере int(10) unsigned NOT NULL); table options — общие параметры таблицы (в примере ENGINE=InnoDB DEFAULT CHARSET=utf-8).
Выборка данных по параметру + защита
Мы можем выбирать определённые данные из таблицы. Например, мы можем выбрать все записи, где pass = 123, и тому подобные.
Вот первый пример выборки по логину + защита:
$login = 'Andre'; // Подставиться вместо знака вопроса
$sql = 'SELECT * FROM users WHERE login = ?'; // Формируем запрос
$query = $pdo -> prepare($sql); // Возвращает объект
$query -> execute(); // В скобках указываем то, что заменит знак вопроса.
// Также перебираем массив, но теперь в массиве только те строки, где login = Andre
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
echo $row;
}
Можно указать несколько вопросительных знаков, а в execute перечислить замены для них через запятую.
Вот второй пример выборки по логину + защита:
$login = 'Andre'; // Подставится вместо :login
// Формируем запрос с помощью ключа
$sql = 'SELECT * FROM users WHERE login = :login';
$query = $pdo -> prepare($sql); // Возвращает объект
// В кавычках нужно указать ключ, который мы указали в запросе.
// А после передать значение, которое должно подставится.
$query -> execute();
// Также перебираем массив, но теперь в массиве только те строки, где login = Andre
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
echo $row;
}
Можно указать несколько ключей, а в execute перечислить замены для них через запятую в формате ‘ключ’ => значение.
Второй способ использовать предпочтительнее, так как читаемость и понятность кода более понятна, чем в первом примере, но использовать можно два способа.
Вывод данных из нескольких таблиц
Если у вас есть несколько связанных таблиц, то с помощью вы можете выводить их данные составляя из них одну таблицу.
Например мы имеем таблицу с книгами — books и их заказами — orders.
Выведем колонки id, book_id и status для таблицы orders.
Имея идентификаторы записей таблицы books в колонке book_id, мы можем соотнести их с колонкой id в таблице books с помощью команды . В результате мы можем узнать статус заказа для каждой книги.
Выведем колонки title, author, price из таблицы books и колонку status из таблицы orders.
Вывести колонки с одинаковыми именами, например id, в данном случае не получится, т.к. они имеются в обоих таблицах.
Вывод статистики с накоплением по дате
Предположим, что у нас имеется склад с некими товарами. Товары периодически поступают, и нам бы хотелось видеть в отчете остатки товаров по дням. Поскольку данные о наличии товаров необходимо накапливать, то мы введем пользовательскую переменную. Но есть одно небольшое “но”. Мы не можем использовать в запросе переменные пользователя и группировку данных одновременно (вернее можем, но в итоге получим, не то, что ожидаем), но мы можем использовать вложенный запрос, вместо явно указанной таблицы. Данные в таблице будут предварительно сгруппированы по дате. И уже затем на основе этих данных мы произведем расчет статистики с накоплением.
На первом этапе требуется установить переменную и присвоить ей нулевое значение:
SET @cvalue = 0
В следующем запросе, мы созданную ранее переменную и применим:
SELECT products.Name AS Name, (@cvalue := @cvalue + Orders) as Orders, Date FROM (SELECT ProductID AS ProductID, SUM(Orders) AS Orders, DATE(date) AS Date FROM statistics WHERE ProductID = '1' GROUP BY date) AS statistics JOIN products ON statistics.ProductID = products.id
Итоговый отчет:
+-----------------------+--------+------------+ | Name | Orders | Date | +-----------------------+--------+------------+ | Процессоры Pentium II | 1 | 2014-09-04 | | Процессоры Pentium II | 2 | 2014-09-12 | | Процессоры Pentium II | 4 | 2014-09-14 | | Процессоры Pentium II | 6 | 2014-09-15 | +-----------------------+--------+------------+
Получить используемую в примерах базу данных можно здесь.
