Создание баз данных и таблиц
Создать базы данных очень просто. Просто введите в оболочке MySQL команду:
CREATE DATABASE dbname;
заменив dbname на имя создаваемой БД.
Также, вы можете воспользоваться программой mysqladmin, чтобы создать новую БД:
mysqladmin -u username -p create dbname;
Этот способ быстрее, если вам нужно лишь создать новую базу данных и, конечно, медленнее, если после создания БД вам нужно ввести ещё серию команд. Отчасти такому замедлению способствует необходимость каждый раз вводить пароль пользователя MySQL. В принципе, вы можете создать файл ~/.my.cnf, поместив в него ваше имя пользователя и пароль MySQL, но я не рекомендую вам этого делать, поскольку это значительно снизит уровень безопасности.
Утилиту mysqladmin вы можете использовать для решения многих административных задач. Например, вы можете запускать, останавливать и перезагружать сервер MySQL. Ну, к примеру, остановить MySQL-сервер по какой-то причине? Воспользуйтесь командой:
mysqladmin -u username -p shutdown
Если желаете ознакомиться со списком всех доступных команд mysqladmin, воспользуйтесь ключом —help:
mysqladmin --help
Чтобы создать таблицу в БД, используется инструкция CREATE TABLE table_name, после которой следует описание создаваемой таблицы. Поскольку каждая таблица обычно имеет несколько полей для хранения информации различных типов, то команды создания таблиц обычно получаются весьма длинными. Вот, например, команда, создающая таблицу для WordPress:
CREATE TABLE `wp_users` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `user_login` varchar(60) NOT NULL default '', `user_pass` varchar(64) NOT NULL default '', `user_registered` datetime NOT NULL default '0000-00-00 00:00:00', `display_name` varchar(250) NOT NULL default '', `spam` tinyint(2) NOT NULL default '0', `deleted` tinyint(2) NOT NULL default '0', PRIMARY KEY (`ID`), UNIQUE KEY `user_login` (`user_login`), KEY `user_login_key` (`user_login`), KEY `user_nicename` (`user_nicename`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf-8;
В этом примере я опустил некоторые инструкции ради краткости, однако основные моменты здесь отображены. После инструкции CREATE TABLE вы видите инструкции с описанием полей таблицы. Описание поля таблицы состоит из имени поля, типа данных, которые буду в нём храниться, а также определения, может ли поле иметь значение NULL (то есть, не содержать данных). Инструкции описания полей таблицы разделены запятыми, а последняя строка указывает MySQL, какой движок (в данном примере — MyISAM) использовать для работы с таблицей, а также опции, специфичные для таблицы в целом.
Не волнуйтесь, если для вас всё это кажется тарабарщиной. Всё, что вы сейчас увидели в примере выше фактически создаёт т. н. схему базы данных. Если вы пользуетесь приложениями, в комплекте которых поставляется схема БД для них, то вам вовсе необязательно знать, что именно делают инструкции, описанные в схеме.
Как достать результат
После того, как мы сделали запрос к базе, в переменной $result будет лежать результат этого действия.
Однако лежит он не в той форме, которая нам нужна в PHP, а в той форме, в которой его прислала нам база.
Достать результат в нормальном виде (в массиве) можно с помощью следующего кода:
Как работает последняя строка?
Функция mysqli_fetch_assoc считывает последовательно каждую строку результата, который прислала нам база.
В цикле for мы считываем построчно результат из базы.
Когда цикл дойдет до последней строки — mysqli_fetch_assoc вернет false и цикл for закончит свою работу.
А результат из БД будет лежать в нормальном виде в массиве $data.
Настройка прав доступа
Чтобы к созданной базе можно было подключиться, добавим пользователя:
> GRANT ALL PRIVILEGES ON newdb.* TO dbuser@localhost IDENTIFIED BY ‘password’ WITH GRANT OPTION;
* где newdb.* — наша база и все ее таблицы; dbuser@localhost — имя учетной записи, которая будет подключаться с локального сервера; password — придуманный нами пароль.** В данном примере, учетной записи будут предоставлены полные права (ALL PRIVILEGES). Подробнее о правах в MySQL читайте статью Как создать пользователя MySQL и дать ему права.
Посмотреть список пользователей, которые имеют доступ к базе можно командой:
> SELECT db, host, user FROM mysql.db WHERE db=’newdb’;
* в данном примере мы выведем учетные записи, которым был дан прямой доступ к созданной нами базе. В данном списке не будут отражены пользователи с глобальными правами (например, root).
Поменять пароль пользователю можно одной из команд (в зависимости от версии СУБД):
> SET PASSWORD FOR ‘dbuser’@’localhost’ = PASSWORD(‘new_password’);
> ALTER USER ‘dbuser’@’localhost’ IDENTIFIED BY ‘new_password’;
> UPDATE mysql.user SET Password=PASSWORD(‘new_password’) WHERE USER=’dbuser’ AND Host=’localhost’;
* все 3 команды меняют пароль для пользователя dbuser@localhost на новый — new_password.
При необходимости, удалить пользователя можно командами:
> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘dbuser’@’localhost’;
> DROP USER ‘dbuser’@’localhost’;
* первая команда отнимает все привилегии, выданные пользователю. Вторая удаляет самого пользователя.
Начнем практиковаться
Сейчас мы с вами начнем изучить SQL запросы на практике. Для этого нам понадобится тестовая таблица в базе данных, заполненная некоторыми данными. Сейчас мы с вами ее сделаем и заполним.
Итак, создайте свою первую базу данных с помощью PhpMyAdmin.
Назовите ее «test».
Создайте в этой базе новую таблицу.
Назовите ее «workers» (англ. работники).
В ней создайте 4 столбца (столбцы по другому называются поля):
- id – тип integer, не забудьте поставить ему галочку AUTO_INCREMENT (чтобы в этом столбце номера проставлялись автоматически).
- name (англ. имя) – тип varchar, размером в 256 знаков.
- age (англ. возраст) — тип integer.
- salary (англ. зарплата) — тип integer.
Ее заполните тестовыми данными, как показано в таблице ниже (этот шаг обязателен, так как дальше все задачи будут по этой таблице):
| id | name | age | salary |
|---|---|---|---|
| 1 | Дима | 23 | 400 |
| 2 | Петя | 25 | 500 |
| 3 | Вася | 23 | 500 |
| 4 | Коля | 30 | 1000 |
| 5 | Иван | 27 | 500 |
| 6 | Кирилл | 28 | 1000 |
Итак, у нас есть таблица с работниками фирмы, в которой указаны их имена, возрасты и зарплаты (в $). Далее мы будем работать с этой таблицей.
Создать запись в таблице
Таблицу создали, остаётся наполнить её информацией. Это делают при помощи команды INSERT. Вы вводите одной командой значения сразу для всей строки:
INSERT INTO название_таблицы (название_столбца1, название_столбца2, название_столбца3) VALUES (“значение1“, “значение2“, “значение3“);
Заметьте, что значения, которые вы хотите поместить в столбцы, нужно брать в кавычки, а названия таблиц и столбцов — нет.
Столбец id не указываем, потому что в нём будут только цифры, которые MySQL добавит автоматически, благодаря параметру AUTO_INCREMENT.
Подставим значения для первой строки:
INSERT INTO books (title,author,price) VALUES("Green Mile","Stephen King","17");
Потом добавим ещё одну строку:
INSERT INTO books (title,author,price) VALUES("The Casal Vacancy", "J.K.Rowling", "23");
И посмотрим, как теперь выглядит таблица:
SELECT * FROM books;
Результат должен быть таким:
mysql_q() — выполнение запроса и контроль ошибок
Нередко оказывается, что запрос к MySQL содержит ошибку, из-за чего приложение неправильно работает. Функция mysql_query() при этом ведет себя молчаливо, не выводя никаких сообщений. Поэтому поиск места, где ошибка произошла, часто становится проблемой.
Функция mysql_q() помогает быстро найти проблемное место — в случае ошибки в запросе она выводит на экран сообщение с информацией об ошибке и прекращает работу скрипта.
Рассмотрим пример:
<?php$query = «SELECT NOW»; // забыли поставить скобки после NOW()mysql_query($query); // ничего не происходит
mysql_q($query); // а вот mysql_q() проинформирует об ошибке?>
После работы mysql_q() на экране появится сообщение:
MySQL error in file /usr/home/www/наш_сайт/test.php at line 7 (function mysql_q):
Unknown column ‘NOW’ in ‘field list’
SELECT NOW
Функция mysql_q() пригодна для любых запросов (не только SELECT):
mysql_q(«INSERT VALUES (‘что-то’) INTO some_table»);
mysql_q(«SET @a = 1»);
mysql_q(«USE some_other_database»);
mysql_q(«TRUNCATE some_table»);
mysql_q(«DROP some_table»);
mysql_q(«START TRANSACTION»);// и др.
Скринкаст: Установка MySQL 5.7.13 в Windows из zip архива, MySQL Workbench
В скринкасте представлена портативная установка MySQL 5.7.13 в Windows из zip архива и выполнена первичная инициализация MySQL сервера, предварительная настройка конфигурации в my.ini, смена пароля root пользователя, настройка подключения к MySQL серверу при помощи MySQL Workbench. Такая установку прекрасно подойдет для локального разработческого WEB сервера и среды разработки под Apache, MySQL, PHP на Windows.
Смотреть на YouTube скринкаст: Установка MySQL 5.7.13 в Windows из zip архива, MySQL Workbench
Содержание скринкаста:
- Назначение портативной установки MySQL 5.7.13 на Windows………………….00:05
- Скачивание дистрибутивов MySQL 5.7.13 и Workbench………………………..02:12
- Распаковка и организация каталога с дистрибутивом MySQL…………………..04:01
- Создание и настойка конфигурационного файла my.ini……………………….05:45
- Инициализация MySQL сервера……………………………………………16:28
- Запуск MySQL сервера………………………………………………….19:53
- Подключение к MySQL серверу при помощи программы MySQL Workbench…………..21:43
- Краткий обзор программы MySQL Workbench…………………………………24:55
- Смена, сброс пароля root пользователя MySQL……………………………..27:46
- Создание start.bat и stop.bat файлов для запуска и остановки MySQL сервера….30:01
- Резюме по скринкасту………………………………………………….31:15
Операции соединения SQL
Операция соединения используется для извлечения данных из нескольких таблиц. Например, если есть две таблицы order и и мы хотим получить данные, то это можно сделать с помощью предложения .
Различные типы предложения JOIN следующие:
- : внутреннее соединение возвращает только те записи, значения которых совпадают в обеих таблицах;
- : перекрестное соединение возвращает только те записи, которые имеют совпадающие значения в левой или правой таблице;
- : левое соединение возвращает все записи из левой таблицы и только совпадающие записи из правой таблицы;
- : правое соединение возвращает все записи из правой таблицы и только совпадающие записи из левой таблицы.
На следующем ниже рисунке показан краткий пример для рассмотрения:
INNER JOIN
Внутреннее соединение возвращает записи, совпадающие в обеих таблицах. Например, ниже приведена таблица заказов order, используемая в приложениях электронной коммерции:
|
order_id |
customer_id |
order_amount |
order_date |
ship_id |
|
1001 |
2 |
7 |
2017-07-18 |
3 |
|
1002 |
37 |
3 |
2017-07-19 |
1 |
|
1003 |
77 |
8 |
2017-07-20 |
2 |
Далее приведем пример таблицы клиентов customer, используемой в приложениях электронной коммерции, которая содержит данные о клиентах:
|
customer_id |
name |
country |
city |
postal_code |
|
1 |
Alfreds Futterkiste |
Germany |
Berlin |
12209 |
|
2 |
Ana Trujillo |
Mexico |
Mйxico D.F. |
05021 |
|
3 |
Antonio Moreno |
Mexico |
Mйxico D.F. |
05023 |
Следующий ниже запрос будет получать все записи заказа со сведениями о клиенте. Поскольку идентификаторы клиентов 37 и 77 отсутствуют в таблице customer, будут получены только совпадающие строки, за исключением идентификаторов клиентов 37 и 77:
LEFT JOIN
Левое соединение извлекает все записи из левой таблицы и только совпадающие записи из правой таблицы. Если применить пример таблиц клиентов и заказов с левым соединением, то оно будет извлекать все записи из таблицы заказов order, даже если в правой таблице нет совпадений (customer). Чтобы получить все сведения о заказах для клиента, можно использовать следующий ниже запрос:
RIGHT JOIN
Правое соединение извлекает все записи из правой таблицы и общие записи из левой таблицы. Если применить пример таблиц клиентов и заказов с правым соединением, то оно будет извлекать все записи из таблицы клиентов customer, даже если в левой таблице (order) нет совпадений. Чтобы получить все сведения о клиентах с заказом, можно использовать следующий ниже запрос:
CROSS JOIN
Перекрестное соединение возвращает все записи, в которых есть совпадение в левой или правой записи таблицы. Если мы возьмем пример с таблицами заказов и клиентов, то оно вернет пять строк со сведениями о клиентах и заказах:
Установка MySQL
Есть несколько вариантов установки, в зависимости от того, будете ли вы использовать СУБД на своём
личном компьютере (что рекомендуется для полноценного освоения), или будете использовать компьютеры
института.
Установка на личном компьютере
Вы можете воспользоваться тем же вариантом, что и для установки на компьютер института, но проще
будет использовать специальный инсталлятор.
Для установки на личном компьютере вы просто скачиваете с сайта http://dev.mysql.com/downloads/mysql/ MySQL
installer — это стандартный установщик, при помощи которого вы установите MySQL так же, как любую
другую прогрумму Windows. В процессе установке следует выбрать вариант «Developer Default», чтобы
установить полный необходимый вам набор инстурментов. Все остальные параметры можно оставить по
умолчанию. Пароль для администратора вы можете придумать сами.
Скачивание дистрибутивов MySQL 5.7 и Workbench
Загрузить zip архив с дистрибутивом MySQL Community Server 5.7.13 (mysql-5.7.13-winx64-debug-test.zip) для выполнения portable установки можно с официального сайта, где в низу страницы представлены разные варианты дистрибутивов, в том числе и в формате zip архива. Загрузить программу MySQL Workbench (mysql-workbench-community-6.3.7-winx64-noinstall.zip) можно так же с официального сайта MySQL. На этих страницах загрузки данные дистрибутивы доступны как в варианте универсального инсталлятора для Windows, так и в виде простого zip архива.
Так же для работы MySQL 5.7 в Windows необходимо, что бы в системы были установлены следующие библиотеки:
- Microsoft .NET Framework 4 Client Profile
- Visual C++ Redistributable for Visual Studio 2013
Подстановка параметров в запрос
Есть возможность подстановки в запрос элементов ассоциативного массива. Для этого запрос нужно составить с использованием специальных меток и в качестве второго аргумента передать функции mysql_q() массив замены:
$sql = »
INSERT INTO products
SET
id = :id,
price = :price,
name = :name
«;
$params = array(
‘id’ => 12,
‘price’ => 10000,
‘name’ => ‘Телевизор’,
);
mysql_q($sql, $params);
При замене учитывается тип переменной: строки будут экранированы и заключены в кавычки, логические значения преобразованы в числовые, NULL также будет обработан нужным образом. В результате запрос примет вид:
»
INSERT INTO products
SET
id = 12,
price = 10000,
name = ‘Холодильник’
«
Если метке соответствует не скалярная величина, а массив, его элементы будут экранированы и подставлены в запрос через запятую. Код вида
$sql = »
SELECT * FROM products
WHERE id IN (:ids)
OR brand = :brand
«;$params = array(
‘brand’ => ‘Полёт’,
‘ids’ => array(
5,
115,
220,
‘сюда случайно попала строка’,
NULL
),
);
mysql_q($sql, $params);
В итоге даст запрос
»
SELECT * FROM products
WHERE id IN (5,115,220,’сюда случайно попала строка’,NULL)
OR brand = ‘Полёт’
«
Подстановка параметров в запрос поддерживается всеми функциями библиотеки (см. ниже).
Получить текст запроса после подстановки можно с помощью функции
mysql_substitute($sql, $params); (обычно это бывает полезно при отладке).
Далее речь пойдет о функциях для обработки запросов, которые возвращают результат — это, прежде всего, запросы SELECT, а также некоторые другие. Для отправки запросов все эти функции используют mysql_q().
В примерах будет использована вот такая таблица:
mysql> SELECT * FROM products;
+—-+————+——-+———————+
| id | name | price | created |
+—-+————+——-+———————+
| 1 | Веник | 100 | 2010-10-09 22:24:14 |
| 2 | Швабра | 500 | 2010-10-09 22:24:26 |
| 3 | Чайник | 1500 | 2010-10-09 22:24:37 |
| 4 | Совок | 150 | 2010-10-09 22:24:51 |
| 5 | Телевизор | 5000 | 2010-10-09 22:24:59 |
| 6 | Ведро | 150 | 2010-10-09 22:25:18 |
+—-+————+——-+———————+
6 rows in set (0.00 sec)
Примеры работы с MySQL
Показать текущую дату:
Можно выполнить запрос нескольких функций, для этого – после первого запроса не ставим закрывающие точку с запятой. Следующий код позволяет показать текущее время, пользователя и версию MySQL
Если в процессе вы передумали продолжать набор команд и хотите завершить набор – укажите с:
Что бы переключиться на использование определённой базы – используйте use:
Что бы проверить, какая в данный момент база используется – выполните:
Для добавления данных в таблицы используются несколько методов:
- с помощью оператора INSERT и значения VALUES;
- с помощью оператора INSERT и значения SET;
- с помощью оператора LOAD DATA;
- с помощью утилиты mysqlimport из файла, в консоли сервера.
Добавление с помощью оператора с помощью утилиты INSERT VALUES
Оператор INSERT имеет следующий синтаксис:
Порядок указания параметров VALUES должен соответствовать порядку столбцов в таблице, проверить которые можно командой:
Выделять значения можно как двойными кавычками ” “, так и одинарными – ‘ ‘. Столбцы, имеющие атрибут AUTO_INCREMENT заполняются значением NULL. Можно использовать множественный ввод для разных строк одной таблицы, указав значения через запятую:
Добавление с помощью оператора SET
При использовании оператора SET в запросе перечисляются все имена столбцов и значения, которые в них требуется установить:
Однако, с помощью оператора SET нельзя вставлять несколько строк, в отличии от схемы INSERT VALUES.
Добавление из файла с помощью LOAD DATA
Столбцы в файле должны быть разделены табуляцией (не пробелом!). Использовать кавычки не нужно. Значения VALUES располагаются как и при обычном методе INSERT VALUES – по очереди имеющихся столбцов в таблице.
Например – содержимое файла main_list.txt:
Если сервер выдал ошибку такого плана:
Добавьте в конфигурационный файл сервера MySQL my.cnf в блок строку:
и перезапустите сервер.
Добавление из файла с помощью mysqlimport
Фактически, mysqlimport просто выполняет оператор LOAD DATA на сервере.
Требования к файлу такие же, как и при использовании LOAD DATA и были описаны выше.
–debug-info использовать не обязательно, тут он просто для примера.
Параметры процедур и функций
Параметры процедуры или функции указываются в операторе CREATE после имени в скобках. Если параметров нет, то необходимо указывать пустые скобки. Параметры бывают типов IN, OUT INOUT (см. таблицу).
|
Режим |
Предназначение |
Использование параметра |
|
IN |
Только для чтения |
Значение параметра может применяться, но не может быть изменено в модуле |
|
OUT |
Только для записи |
В модуле можно присваивать значение параметру, но нельзя использовать его. |
|
IN OUT |
Для чтения и записи |
В модуле можно использовать и изменять значение параметра |
Пример процедуры, в которой используется один параметр IN и один параметр OUT:
CREATE PROCEDURE Parts_count(IN Mat VARCHAR(20),OUT PNum INT)
BEGIN
SELECT count(*) INTO PNum FROM Parts WHERE Material=Mat ;
END
//
CALL Parts_count(‘Rubber’,@S)//
Пример функции с параметром:
CREATE FUNCTION Hello_World(S VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
RETURN CONCAT(‘Hello ’,S,’!!!’);
END
//
Для запуска функции необходимо ввести
SELECT Hello_World(‘Root’)//
mysql_getcolumn() — получение одного столбца
Функция mysql_getcolumn() служит для получения результатов запросов, которые состоят из нескольких строк и одного столбца. mysql_getcolumn() возвращает результат запроса в виде одномерного массива:
<?php$query = «SELECT name FROM products ORDER BY price DESC»;$data = mysql_getcolumn($query);print_r($data);?>
Array
(
=> Телевизор
=> Чайник
=> Швабра
=> Совок
=> Ведро
=> Веник
)
Можно и запросы типа таких:
<?phpprint_r(mysql_getcolumn(«SHOW TABLES LIKE ‘products'»));?>
Array
(
=> products
)
Иногда бывает нужно иметь такой массив, ключи которого содержат значения какого-нибудь уникального поля (столбца) соответствующих записей. С помощью mysql_getcolumn() сделать это очень легко — нужно добавить это поле в запрос и указать в качестве второго аргумента TRUE. Ключами становятся значения того поля, которое указано в запросе первым:
<?php$query = «SELECT id, name FROM products ORDER BY price DESC»;$data = mysql_getcolumn($query, TRUE);print_r($data);?>
Array
(
=> Телевизор
=> Чайник
=> Швабра
=> Совок
=> Ведро
=> Веник
)
Параметры для подстановки передаются третьим аргументом.
Команды меню «вид»
Для доступа к меню «Вид» используется сочетание клавиш ALT + V. В следующей таблице описаны сочетания клавиш, используемые для доступа к пунктам меню в меню Вид.
| ДЛЯ ЭТОГО | НАЖАТЬ |
|---|---|
| Синхронизация обозревателей метаданных | ALT + V + Z |
| Отобразить дерево | ALT + V + H или CTRL + T |
| Отобразить часть источника | ALT + V + S |
| Показывать целевую часть | ALT + V + T |
| Отобразить панель списка ошибок. | ALT + V + E или CTRL + E |
| Отображение области вывода. | ALT + V + O или CTRL + O |
| Подменю «макеты Access». | ALT + V + L |
| Добавить текущий макет | ALT + V + L + A |
| Выбор макета по умолчанию | ALT + V + L + D или CTRL + ALT + 1 |
| Выбрать без макета проводника | ALT + V + L + W или CTRL + ALT + 2 |
| Отображение диалогового окна Управление макетами. | ALT + V + L + M |
Выполнение запросов
Установив соединение и определив кодировку мы готовы выполнить свои первые SQL-запросы. Вы уже умеете составлять корректные SQL команды и выполнять их через консольный или визуальный интерфейс MySQL-клиента.
Те же самые запросы можно отправлять без изменений и из PHP-сценария. Помогут в этом несколько встроенных функций языка.
Два вида запросов
Следует разделять все SQL-запросы на две группы:
- Чтение информации (SELECT).
- Модификация (UPDATE, INSERT, DELETE).
При выполнении запросов из среды PHP, запросы из второй группы возвращают только результат их исполнения: успех или ошибку.
Запросы первой группы при успешном выполнении возвращают специальный ресурс результата. Его, в свою очередь, можно преобразовать в ассоциативный массив (если нужна одна запись) или в двумерный массив (если требуется список записей).
Работа с базами данных
Вывод списка имеющихся БД:
SHOW DATABASES;
Каждая сборка MySQL имеет несколько стандартных баз данных, они хранятся рядом с вашими и выполняют технические функции, хотя с ними тоже можно работать.
- information_schema — хранилище метаданных о вашей базе данных, таких как наименование других баз данных, количестве столбцов, их таких и т.д.
- performance_schema — быстрая версия «information_schema«.
- mysql — еще одно хранилище служебной информации.
Выбор базы данных для работы:
USE database_name;
После выбора базы данных все команды по работе с данными будут относиться к ней и приглашение командной строки тоже обновится.
Создание новой базы данных с кодировкой «utf8_general_ci» (это не совсем кодировка, это скорее способ сравнения символов):
CREATE DATABASE new_database COLLATE utf8_general_ci;
Удаление базы данных:
DROP DATABASE new_database;
