Библиотека сайта rus-linux.net
MySQL - полезные приемы работы
Оригинал: MySQL - Some Handy Know-How
Автор: Shawn Powers
Дата публикации: 30 декабря 2015 г.
Перевод: А. Панин
Дата перевода: 20 мая 2016 г.
Недавно я общался в IRC-чате с человеком, который вызвался помочь мне с исправлением ошибки в сценарии на языке PHP. В процессе работы над исправлением ошибки он попросил меня сообщить значение определенного поля записи в таблице базы данных, расположенной на сервере. Я сразу же ответил, что это займет некоторое время, так как для получения этого значения мне придется установить на сервер специализированное веб-приложение, такое, как PHPMyAdmin или Adminer. Он был очень любезен и прислал мне в ответ команду, которую нужно было выполнить в консоли для получения всей необходимой информации. Конечно же, я был благодарен ему, но в то же время немного смущен. Мне кажется, что если я не знал о таком способе получения данных от сервера MySQL, то и многие из моих читателей также пребывают в неведении. Поэтому давайте немного поработаем сервером SQL вместе.
Загрузка дампа базы данных
Оказывается, в Интернет опубликовано немало дампов простых баз данных, которые можно загружать и использовать для любых целей. К сожалению, все эти дампы являются более сложными, чем тот, который мне хотелось бы использовать для демонстрационных целей. Исходя из этого, я принял решение о создании собственной базы данных. Несмотря на то, что вы вполне можете обойтись без дампа моей базы данных при чтении данной статьи, он, несомненно окажется полезным в том случае, если вы захотите выполнить самостоятельно все описанные операции. Поэтому первым делом давайте создадим базу данных и выполним импорт моих данных из дампа.
Первой операцией, которую вам придется выполнить, будет установка сервера MySQL. В зависимости от вашего дистрибутива, она может осуществляться либо с помощью команды apt-get, либо с помощью команды dnf, либо средствами центра приложений с графическим интерфейсом. Я доверю установку сервера вам - не стесняйтесь пользоваться поисковой системой Google при возникновении каких-либо затруднений. Самое главное - вам придется запомнить пароль пользователя root сервера базы данных, который вам будет предложено ввести после окончания процесса его установки. Это не пароль пользователя root, то есть, администратора вашей системы; напротив, это пароль пользователя root, то есть, администратора вашего сервера MySQL. Если вы используете уже установленный сервер, просто создайте учетную запись пользователя с возможностью создания баз данных. Я буду считать, что вы установили сервер MySQL и знаете пароль пользователя root этого сервера.
Для взаимодействии с сервером MySQL посредством интерфейса командной строки используется утилита "mysql". Таким образом, для создания рассматриваемой в статье базы данных следует просто выполнить следующую команду:
mysql -u root -p -e "CREATE DATABASE food"
Утилита должна попросить вас ввести пароль пользователя root, который вы ввели в процессе создания учетной записи пользователя root сервера MySQL после его установки. Если в результате выводится сообщение об ошибке, указывающее на то, что база данных с таким именем уже существует, вы можете придумать новое имя для вашей базы данных. Просто осознайте тот факт, что выбранное имя базы данных будет использоваться во всех командах, связанных с обработкой данных из базы, которую я буду называть "food".
Далее вам придется загрузить в свою базу мои данные из дампа. Я опубликовал файл дампа SQL по следующему адресу. Вы можете загрузить этот файл с помощью веб-браузера или утилиты wget с интерфейсом командной строки. В случае использования утилиты wget, в качестве результирующего имени файла может быть установлено как "foodsql", так и "food.sql" в зависимости от используемой версии утилиты. Оба имени никак не повлияют на возможность использования файла дампа, при этом вам всего лишь придется немного подкорректировать одну из приведенных ниже команд перед ее исполнением. А это команды для загрузки и импорта файла дампа базы данных:
wget https://www.dropbox.com/s/a46sln544hb7yvf/food.sql?dl=0 -O food.sql mysql -u root -p food < ./food.sql
Помните о том, что если после выполнения первой команды был создан файл дампа с именем "foodsql", а не "food.sql", вы должны использовать ./foodsql вместо ./food,sql во второй команде. Оба варианта команд являются полностью работоспособными.
Что вы только что сделали?
Утилита mysql позволяет как работать в интерактивной командной оболочке, так и выполнять отдельные запросы, такие, как представленные выше. С помощью первой команды на вашем сервере MySQL осуществлялось создание базы данных с именем "food", которая будет использоваться во всех примерах данной статьи. Флаг -u позволяет вам соединиться с сервером под именем заданного пользователя - в данном случае этим пользователем является root. Флаг -p сообщает утилите mysql о необходимости осуществления запроса пароля. Вы также можете передать пароль в составе команды следующим образом:
mysql -u root -pmypassword -e "CREATE DATABASE food"
Однако, данный вариант не нравится мне по двум причинам. Во-первых, все символы пароля отображаются на экране, что пугает меня. Кроме того, вы наверняка обратили внимание на то, что между флагом -p и первым символом пароля нет символа пробела. Это не опечатка; именно в таком формате и должен передаваться пароль - это очень странная особенность. Обычно я всегда использую функцию запроса пароля. В последней части команды передается запрос, который должен быть исполнен сервером MySQL. Позднее я кратко опишу режим интерактивной командной оболочки, а в данном случае вы всего лишь сообщаете серверу о необходимости создания базы данных с именем "food", после чего утилита завершает свою работу. Слова CREATE и DATABASE не обязательно должны записываться в верхнем регистре, но такая запись является стандартной практикой в мире разработчиков SQL. Если слово является оператором или инструкцией, оно должно записываться в верхнем регистре. Если же оно является данными или именем, оно должно записываться в нижнем регистре. Это просто соглашение, которого я буду придерживаться. Вам тоже стоит придерживаться его, ведь подобное оформление запросов SQL значительно повышает их читаемость.
Следующая последовательность команд предназначена для выполнения двух операций. Этими операциями является загрузка моего файла дампа базы данных с помощью утилиты wget и добавление данных из него в созданную базу данных с именем "food". Загруженный файл является обычным текстовым файлом. Если вы откроете его в текстовом редакторе, вы увидите множество запросов SQL (с операторами в верхнем регистре). Эти запросы передаются посредством стандартного потока ввода утилиты mysql и исполняются точно также, как и запрос создания базы данных, расположенный после флага -e. В результате на вашем локальном сервере MySQL появляется база данных с именем "food", содержащая мои таблицы и данные. Давайте исследуем эти данные.
Соединение с базой данных
Если вы выполняли все приведенные выше инструкции, вы уже осуществляли соединение с сервером MySQL и работали с содержимым базы данных, но для этой цели использовались отдельные команды, которые позволяли выполнять по одному запросу за раз. Для перехода в режим интерактивной командной оболочки вы можете просто выполнить следующую команду:
mysql -u root -p food
В результате утилита попросит вас ввести пароль, после проверки которого вы будете перемещены в интерактивную командную оболочку MySQL с открытой базой данных "food", доступной для всестороннего исследования. При перемещении в интерактивную командную оболочку вы должны увидеть аналогичное приветствие:
Welcome to the MySQL monitor. Commands end with ; or \g. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Чтобы убедиться в том, что была открыта именно база данных "food" и данные из файла дампа базы данных были корректно импортированы, стоит изучить содержимое таблиц открытой базы данных. Для этого достаточно выполнить следующий запрос:
SHOW TABLES;
В результате вы должны увидеть аналогичный вывод:
mysql> SHOW TABLES; +----------------+ | Tables_in_food | +----------------+ | fruit | | vegetable | +----------------+ 2 rows in set (0.00 sec)
Обратите внимание на то, что каждый введенный вами запрос должен заканчиваться символом точки с запятой. Если вы забудете об этом символе, командная оболочка переместит курсор на следующую строку и будет ожидать продолжения ввода запроса. В подобной ситуации (с которой я сталкиваюсь при вводе практически половины запросов), вы можете просто ввести единственный символ точки с запятой в новой строке, ведь в этом случае командная оболочка посчитает, что вы ввели этот символ сразу же после строки запроса и выполнит запрос без каких-либо проблем.
Вы должны обнаружить таблицы "fruit" (фрукты) и "vegetable" (овощи) в базе данных "food" (пища). Если база данных не содержит таких таблиц, следует вернуться назад и повторно выполнить приведенные выше команды, так как очевидно, что на каком-то этапе произошла ошибка. В процессе повторного выполнения команд следует обратить особое внимание на любые сообщения об ошибках.
Следующей операцией, которую следует выполнить, является поиск данных в каждой из таблиц. Для этого вы можете использовать новый запрос, SELECT, который в интерактивном режиме просто выведет все данные, соответствующие заданным вами критериям. Выполните следующий запрос:
SELECT * FROM fruit;
В MySQL символ звездочки является специальным символом шаблона, соответствующим любым последовательностям символов. Таким образом, в результате исполнения запроса SELECT будут выведены все записи из таблицы "fruit" (FROM fruit). Исходя из этого, вы должны увидеть визуальное представление всех данных из указанной таблицы базы данных в следующем формате:
mysql> SELECT * FROM fruit; +------------+-------+--------+ | name | size | color | +------------+-------+--------+ | lemon | small | yellow | | grape | small | purple | | apple | small | red | | banana | small | yellow | | watermelon | big | green | +------------+-------+--------+ 5 rows in set (0.01 sec)
Попытайтесь проделать аналогичные действия с таблицей "vegetable" рассматриваемой базы данных. Вы должны увидеть аналогичный набор данных, относящихся к овощам, а не фруктам.
Фильтруйте информацию о продуктах!
Обычно при осуществлении манипуляций с базами данных требуется получить лишь малую часть хранящейся в них информации. Для этой цели также используются запросы, при этом не стоит обращать особое внимание на жаргон разработчиков баз данных. По сути, для фильтрации результатов в соответствии с нашими потребностями мы будем использовать практически корректные предложения на английском языке.
Предположим, что вам нужно получить список овощей малого размера. Для его получения вы можете выполнить следующий запрос:
SELECT * FROM vegetable WHERE size = "small";
В результате вы увидите аналогичный вывод:
mysql> SELECT * FROM vegetable WHERE size = "small"; +--------+-------+--------+ | name | size | color | +--------+-------+--------+ | pea | small | green | | radish | small | red | | bean | small | green | | corn | small | yellow | +--------+-------+--------+ 4 rows in set (0.01 sec)
Вы наверняка заметили, что в списке нет тыквы ("pumpkin"), ведь это овощ большого размера (поле "size" соответствующей записи содержит значение "big", а не "small").
Кроме того, вы можете осуществить вывод лишь названий овощей, соответствующих заданным вами критериям, вместо вывода значений всех полей записей. Для этого может использоваться следующий запрос:
SELECT name FROM vegetable WHERE size = "big";
В результате вы должны увидеть следующий сокращенный вывод:
mysql> SELECT name FROM vegetable WHERE size = "big"; +---------+ | name | +---------+ | pumpkin | +---------+ 1 row in set (0.00 sec)
Достаточно интересный результат, заключающийся в выводе имен всех овощей большого размера без вывода содержимого поля с информацией об их размере, не так ли? Разумеется, в подобных случаях вам придется располагать некоторым объемом информации о структуре базы данных (в данном примере о наличии столбца таблицы с информацией о размере овощей), но, в любом случае, следует помнить о том, что вы можете создавать фильтры на основе тех данных, которые в конечном счете не будут выводиться.
Изменение данных
До текущего момента вы лишь осуществляли поиск существующей информации. Утилита mysql, работающая в интерактивном режиме, также позволяет вам модифицировать существующую и добавлять новую информацию в базу данных. Для изменения содержимого произвольных полей используется оператор UPDATE. Поэтому если вы желаете изменить размер кукурузы ("corn") с малого на большой, вы можете использовать следующий запрос:
UPDATE vegetable SET size = "big" WHERE name = "corn";
В результате в поле "size" соответствующей записи будет находиться значение "big" вместо значения "small", поэтому в случае использования рассмотренного ранее запроса:
SELECT name FROM vegetable WHERE size = "big";
будет получен аналогичный вывод:
mysql> SELECT name FROM vegetable WHERE size = "big"; +---------+ | name | +---------+ | pumpkin | | corn | +---------+ 2 rows in set (0.00 sec)
Однако, вы можете не только изменять существующие данные; у вас также имеется возможность добавления новых данных в различные таблицы базы данных. Для этой цели используются более сложные запросы, которые, тем не менее, являются достаточно прозрачными. Предположим, что вам необходимо добавить в вашу базу данных информацию о дыне ("honeydew"). Для добавления новой записи в таблицу базы данных следует использовать следующий запрос:
INSERT INTO fruit (name, color, size) VALUES ('honeydew', 'green', 'big');
Если вы воспользуетесь впоследствии оператором SELECT для получения всех записей из таблицы базы данных с информацией о фруктах ("fruit"), вы должны будете увидеть аналогичный вывод:
mysql> SELECT * FROM fruit; +------------+-------+--------+ | name | size | color | +------------+-------+--------+ | lemon | small | yellow | | grape | small | purple | | apple | small | red | | banana | small | yellow | | watermelon | big | green | | honeydew | big | green | +------------+-------+--------+ 6 rows in set (0.00 sec)
И это лишь вершина айсберга!
Утилита с интерфейсом командной строки mysql, поддерживающая режим интерактивной командной оболочки, является очень мощным инструментом для осуществления доступа к базе данных с целью вывода и модификации хранящихся в ней данных без какого-либо графического интерфейса. Вы должны помнить базовые операторы для того, чтобы в ситуациях, подобных моей, иметь возможность получения любых данных от сервера MySQL без необходимости установки какого-либо специализированного программного обеспечения. Тем не менее, существующие инструменты с графическими и веб-интерфейсами являются очень удобными и могут использоваться в качестве инструментов для изучения приемов работы с базами данных. Например, на Рисунке 1 показан интерфейс веб-приложения Adminer (http://www.adminer.org) в процессе работы с базой данных. Функции поиска, фильтрации, сортировки, а также другие функции языка SQL доступны в его раскрывающихся меню. При этом действительно важной функцией приложения Adminer является вывод точной строки запроса, которая использовалась для получения результатов. Поэтому в том случае, если вы хотите использовать инструмент с веб-интерфейсом для изучения принципов построения запросов для доступа к содержимому баз данных, Adminer является отличным решением.
Рисунок 1. Adminer является на удивление мощным инструментом, который также выводит подробную информацию обо всех выполняемых операциях!
Очевидно, что рассмотренная в статье примитивная база данных с информацией о фруктах и овощах гораздо проще тех баз данных, с которыми вы будете работать в процессе отладки веб-приложений. К счастью, при работе с любыми базами данных используются одни и те же концепции вне зависимости от того, осуществляется ли поиск среди тысяч финансовых транзакций или среди нескольких наименований продуктов. Также важно понимать, что при использовании оператора SELECT для выборки записей средствами работающего сервера базы данных не осуществляется каких-либо изменений данных полей этих записей, поэтому не стоит волноваться о целостности базы данных при извлечении любой информации. Фактически, лучший подход к проверке любых ваших предположений заключается в самостоятельном построении сложных запросов для осуществления манипуляций с существующими базами данных. Если вы столкнетесь с какими-либо сложностями, вы всегда можете запустить Adminer и разобраться в том, что вы делаете не так. Удачи в работе с базами данных!