Запуск сервера mysql из командной строки windows. Основы администрирования MySQL при помощи командной строки. Работа с командной строкой

Ниже предоставлен список наиболее полезных и часто используемых команд MySQL с примерами.

mysql в начале строки означает, что команда выполняется после входа вMySQL .

Символ # или $ в начале строки означает, что команда выполняется из командной строки.

Что бы проверить статус сервера MYSQL выполните:

для FreeBSD :

# service mysql-server status

в CentOS / RHEL :

# service mysqld status

MySQL из консоли, если сервер MySQL находится на том же хосте:

Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на удаленном хосте db1.example.com:

$ mysql -u username -p -h db1.example.com

Работа с базами, таблицами - просмотр, удаление, редактирование записей. Консоль

Создать базу данных на MySQL сервере:

Mysql create database

Показать список всех баз данных на сервере MySQL :

Mysql use ;

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

Mysql show tables;

Просмотреть формат таблицы в базе:

Mysql describe ;

Удалить базу:

Mysql drop database ;

Удалить таблицу из базы:

Mysql drop table ;

Показать все содержимое таблицы:

Mysql SELECT * FROM ;

Отобразить столбцы и содержимое столбцов в выбранной таблице:

Mysql show columns from ;

Отобразить строки в определенной таблице, содержащие " whatever ":

Mysql SELECT * FROM WHERE = "whatever";

Отобразить все записи в определенной таблице, содержащие " Bob " и телефонный номер " 3444444:

Mysql SELECT * FROM WHERE name = " Bob " AND phone_number = " 3444444 ";

Отобразить все записи, НЕ содержащие имя " Bob " и телефонный номер " 3444444 ", отсортированные по полю phone_number:

Mysql SELECT * FROM WHERE name != " Bob " AND phone_number = " 3444444 " order by phone_number;

Показать все записи, начинающиеся с букв " bob " и телефонного номера " 3444444 " в определенной таблице:

Mysql SELECT * FROM WHERE name like " Bob %" AND phone_number = " 3444444 ";

Показать все записи, начинающиеся с букв " bob " и телефонного номера " 3444444 ", ограничиваясь записями с 1-ой до 5-ой:

Mysql SELECT * FROM WHERE name like " Bob %" AND phone_number = " 3444444 " limit 1,5;

Использование регулярных выражений ("REGEXP BINARY") для поиска записей. Например, для регистро-независимого поиска - найти все записи, начинающиеся с буквы А:

Mysql SELECT * FROM WHERE rec RLIKE "^ a ";

Показать все уникальные записи:

Mysql SELECT DISTINCT FROM ; mysql SELECT , FROM ORDER BY DESC;

Показать количество строк в таблице:

Mysql SELECT COUNT(*) FROM ;

Mysql SELECT SUM(*) FROM ;

Удаление столбца:

Mysql alter table drop column ;

Добавление колонки в базу данных:

Mysql alter table add column varchar (20);

Изменение имени столбца:

Mysql alter table change varchar (50);

Создать столбец с уникальным именем, что бы избежать дубликатов в названиях:

Mysql alter table add unique ();

Изменение размера столбца:

Mysql alter table modify VARCHAR(3);

Удаление столбца из таблицы:

Mysql alter table drop index ;

Mysql LOAD DATA INFILE " /tmp/filename.csv " replace INTO TABLE FIELDS TERMINATED BY "," LINES TERMINATED BY "n" (field1,field2,field3);

Пользователи, пароли сервера MySQL - добавление, изменение пользователей и паролей. Консоль

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

# mysql -u root -p mysql use mysql; mysql INSERT INTO user (Host,User,Password) VALUES("%"," username ", PASSWORD(" password ")); mysql flush privileges;

Изменений пользовательского пароля из консоли на удаленном хосте db1.example.org:

# mysqladmin -u username -h db1.example.org -p password " new-password "

Изменение пользовательского пароля из консоли MySQL - подключение под root, обновление пароля, обновление привилегий:

# mysql -u root -p mysql SET PASSWORD FOR " user "@" hostname " = PASSWORD(" passwordhere "); mysql flush privileges;

Восстановление/изменение пароля root сервера MySQL - остановка MySQL , запуск без таблиц привилегий, подключение под root, установка нового пароля, выход и перезапуск MySQL .

# /etc/init.d/mysql stop # mysqld_safe -skip-grant-tables & # mysql -u root mysql use mysql; mysql update user set password=PASSWORD(" newrootpassword ") where User="root"; mysql ; flush privileges; mysql quit # /etc/init.d/mysql stop # /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Обновление пароля root:

# mysqladmin -u root -p oldpassword newpassword

Установка права на подключение к серверу с хоста localhost с паролем " passwd " - подключение подroot, переключение к базе данных, установка привилегий, обновление привилегий:

# mysql -u root -p mysql use mysql; mysql grant usage on *.* to bob @localhost identified by " passwd "; mysql flush privileges;

Установка привилегий пользователю на использование базы данных - подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:

# mysql -u root -p mysql use mysql; mysql INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ("%","databasename","username","Y","Y","Y","Y","Y","N"); mysql flush privileges;

Mysql grant all privileges on databasename .* to username @localhost; mysql flush privileges;

Обновление информации в базе данных:

Mysql UPDATE SET Select_priv = "Y",Insert_priv = "Y",Update_priv = "Y" where = user";

Удаление строки в таблице:

Mysql DELETE from where = "whatever";

Обновление привилегий в базе данных:

Mysql flush privileges;

Резервные копии - создание, восстановление бд. Консоль

Создать резервную копию (dump) всех баз данных в файл alldatabases.sql:

# mysqldump -u root -p password -opt ; /tmp/alldatabases.sql

Создать резервную копию одной базы данных в файл databasename.sql:

# mysql dump -u username -p password -databases databasename ; /tmp/databasename.sql

Создать резервную копию одной таблицы в файл databasename.tablename.sql:

# mysql dump -c -u username -p password databasename tablename ; /tmp/databasename.tablename.sql

Восстановление базы данных (или таблицы) из резервной копии:

# mysql -u username -p password databasename < /tmp/databasename.sql

Создание таблиц БД. Консоль

маленькими буквами указаны имена столбцов;
ПРОПИСНЫМИ буквами - типы и атрибуты столцов;
в (скобках) - значение типа столбца.

Создать таблицу, пример 1:

mysql CREATE TABLE (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp TIME, pgpemail VARCHAR(255));

Создать таблицу, пример 2:

Mysql create table (personid INT(50) NOT NULL AUTO_INTCREMENT PRIMARY KEY, firstname VARCHAR(35), middlename VARCHAR(50), lastname VARCHAR(50) default "bato");

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

Краткий обзор систем управления базами данных

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

Существует определенная классификация, которая определяет необходимость использования той или иной базы данных и СУБД (системы управления базами данных). Наиболее распространенными в настоящее время принято считать клиент-серверные технологии хранения данных. К ним относятся следующие виды: Firebird, Interbase, IBM DB2, Sybase, Oracle, PostgreSQL, ЛИНТЕР, MySQL. Нас будет интересовать последний вариант - MySQL, команды которой полностью соответствуют стандартам SQL. Данная технология является одной из популярных и часто применяемых для решения как локальных прикладных, так и производственных задач малого масштаба.

Предшественниками клиент-серверной технологии являются файл-серверные СУБД, которые утратили свои позиции из-за ряда имеющихся недостатков, среди которых выделяют необходимость распространения ядра СУБД на каждом компьютере и значительную нагрузку на локальную сеть, приводящую к увеличению временных затрат. К данной группе относятся Borland Paradox.

Новые технологии, набирающие популярность

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

В качестве примеров можно рассматривать OpenEdge, SQLite, BerkeleyDB, один из вариантов Firebird, Sav Zigzag, Compact, ЛИНТЕР, а также один из вариантов MySQL, команды которого ничем не отличаются от используемых в клиент-серверных СУБД. Однако встраиваемые системы могут легко утратить свою актуальность, если задача перестанет носить локальный характер.

Основные преимущества СУБД MySQL

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

Главным плюсом на российском рынке считается ее доступность, так как она бесплатна в большинстве случаев. Второй особенностью является быстродействие. Одной из самых популярных систем является MySQL. Команды в ней выполняются быстро, с минимальным временем отклика. Не оказывает влияния на скорость обработки команд подключение к серверу нескольких клиентов в многопоточном режиме за счет использования механизма InnoDB для быстрой поддержки транзакций.

Наличие ODBC драйвера позволяет упростить разработчикам решение многих задач. К преимуществам также стоит отнести наличие возможности осуществления записей фиксированной и переменной длины. Но главная функция, которая очень ценится в кругу программистов, - интерфейс с языками С и РНР. Возможности, предоставляемые MySQL, позволили использовать данную СУБД для огромного числа провайдеров услуг интернет-хостинга.

А для простого обывателя, проявляющего интерес к современным технологиям хранения информации, необходимо изучать MySQL, и их синтаксис потому, что специалисты в данном направлении являются широко востребованными и высокооплачиваемыми в любой точке мира. Поэтому, если кого-нибудь интересует данное направление, не стоить медлить. Необходимо переходить к изучению прямо сейчас.

Что необходимо для изучения

На первый взгляд может показаться, что данная область сложна в самостоятельном изучении и требует постоянной связи со специалистом. Однако это далеко не так. Изучать архитектуру и особенности MySQL, основные команды языка запросов и все, что с этим связано, можно самостоятельно, не прибегая при этом к помощи консультанта. Для этого достаточно иметь желание и прилагать усилия для того, чтобы двигаться дальше. Только саморазвитие и изучение этой области позволят приобрести новые знания, закрепить приобретенные навыки и, возможно, начать строить карьеру и продвигаться в данном направлении.

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

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

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

Основные консольные команды

Плавно переходя от основных особенностей, возможностей и преимуществ, мы подобрались к списку основных команд. Утилита MySQL изначально содержит только одного пользователя, который как раз и используется для изучения. Это пользователь с именем root , пароль для которого полностью идентичен имени.

При первом и последующих запусках консоли MySQL пользователю необходимо будет вводить пароль root для дальнейшей работы с СУБД MySQL. Команды консоли будут доступны только после прохождения аутентификации.

После успешной проверки для удобства можно отобразить существующие команды при помощи команды help . После этого на консоли будут представлены все существующие команды и краткое описание к ним.

и отображения

Теперь необходимо перейти к следующему шагу. Для этого необходимо выбрать существующую или создать новую базу данных. Для выбора существующей базы необходимо использовать команду use. И через пробел ввести имя базы данных. Изначально в утилите она существует только одна - с именем test . Поэтому запрос будет выглядеть следующим образом: use test .

Для создания базы данных необходимо воспользоваться командой create , указав ей ключевое слово database и задав соответствующее имя. Структура примет следующий вид: create database Name_of_database . Для работы с созданной базой к ней необходимо обратиться при помощи команды use .

В среде предусмотрена функция, предназначенная для отображения существующих баз данных, таблиц, первичных ключей или внешних связей и вывода имеющейся о них информации на MySQL консоль. Команды в этом случае должны всегда начинаться с предложения show . Например, для того чтобы отобразить список имеющихся баз данных для текущего пользователя, достаточно ввести следующий запрос: show databases . Для отображения таблицы достаточно после ключевого слова изменить объект отображения, вписав tables .

Команды управления таблицами

Прежде чем перейти дальше, необходимо ещё раз напомнить, что соответствие стандартам языка SQL предоставляет широкие возможности разработчикам, независимо от используемых СУБД и операционных систем. Запросы, разработанные в любой среде, которая поддерживает стандарт SQL, успешно отработают (при наличии базы данных и таблиц) в среде MySQL. Команды консоли Windows ничем не отличаются от используемых в других операционных системах.

Для работы с таблицами существует ряд определенных команд, которые, если перевести их с английского, говорят сами за себя. О команде create было упомянуто выше. Она также может быть задействована для добавления таблиц в ранее созданную базу данных. Для удаления объектов баз данных, в частности и таблиц, используется команда drop , к которой добавляется имя уничтожаемого объекта. Пример: drop name _ of _ your _ table .

Синтаксис обращений к серверу БД всегда имеет общую структуру. Поэтому запрос, разработанный в Windows, успешно отработает и в консоли MySQL Linux. Команды, обработанные сервером без ошибок в одной операционной системе, не могут привести к возникновению ошибок в других.

Команда Select

Но самая важная для работы с таблицами команда, синтаксис которой достаточно прост, - команда select . Именно она применяется для выбора данных из базы. Первоначальный синтаксис имеет следующую структуру: select * from table _ name . Отправив такой запрос серверу базы данных, клиент должен получить все записи, хранящиеся в таблице.

Многие никогда не задумываются, но при просмотре интернет-ресурсов постоянно используются MySQL команды.PHP-запросы используют синтаксис языка SQL, чтобы отображать содержимое каталогов интернет-магазинов или новости в социальных сетях. Вместо знака “*” после предложения select , как правило, приводится список основных полей таблицы или нескольких таблиц, данные из которых необходимо обработать. В случае выборки из нескольких таблиц используется специальная связка join , которая служит для их соединения по имеющимся внешним связям. Однако это условие можно изменять, самостоятельно указывая, по каким полям должна проводиться связь.

Задание ограничений выборки

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

Популярная система управления базами данных MySQL широко применяется для различных нужд, в первую очередь как стандарт де-факто в области интернет хостинга. Не менее широко распространен пакет для управления данной СУБД - phpMyAdmin . Без сомнения, это хороший, удобный продукт, но очень часто случается так, что навыки работы с этим продуктом полностью заменяют навыки работы с самой СУБД. Поэтому в данном материале мы решили познакомить наших читателей с тем, как выполнять основные задачи администрирования MySQL из командной строки.

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

Но есть одно большое отличие: специалист, умеющий работать с командной строкой, используя панель, не заменяет ей умение работать с продуктом, а только облегчает себе выполнение повседневных задач. А новичок, привыкший совершать все действия в панели, в случае ее недоступности впадает в тихую панику, ведь теперь нужно вводить какие-то "заклинания" в эту непонятную черную консоль...

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

Управление базами данных и пользователями в MySQL

Если танцевать следует начинать от печки, то работу с СУБД следует начинать с создания баз данных и пользователей этих баз. Задачи по сути своей простые и прекрасно, а главное - просто, решаются из консоли. Для работы с сервером MySQL предназначена одноименная утилита mysql , работа с которой происходит в интерактивном режиме, поэтому вначале подключимся к серверу:

Mysql -u root -p

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

Работа в данной среде имеет свои особенности: каждая команда должна завершаться символом ; или \g , о чем, кстати, написано в первой строке приветствия. Выход из данного режима осуществляется командой:

Сразу об одной очень распространенной ошибке: забыли поставить точку с запятой в конце команды. Что делать? Ничего страшного, просто добейте недостающие символы в следующей строке.

Для начала посмотрим список баз:

Show databases;

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

Узнать список пользователей немного сложнее, он хранится в виде таблицы служебной базы mysql , поэтому пишем небольшой запрос:

Select user,host from mysql.user;

Команда select выбирает указанные колонки user, host , опция from указывает откуда мы их выбираем, а именно из таблицы user базы данных mysql .

Первая колонка указывает пользователя, вторая - хост, на котором данному пользователю разрешены подключения, % - обозначает любое значение. Обратите внимание, что если ivanov имеет в настройках localhost , то при подключении к mysql по адресу 127.0.0.1 он не сможет получить доступ, это нужно учитывать, когда вы указываете данные подключения к серверу СУБД в своих приложениях.

Посмотреть владельцев баз можно следующим запросом:

Select host,db,user from mysql.db;

Особых комментариев здесь не требуется. Например, ivanov имеет права на базы с префиксом ivanov_ при подключении через localhost , а andrey на базы с префиксом andrey_ на любом хосте. Посмотреть привилегии пользователя можно командой:

Show grants for "ivanov"@"localhost";

Имя пользователя и хост оборачиваем одинарными кавычками.

Первая строка сообщает, что у указанного пользователя отсутствуют привилегии (USAGE ) на любую таблицу любой базы (*.* ), вторая строка говорит о всех основных привилегиях для всех таблиц всех баз с префиксом ivanov_ .

Подробный разбор системы прав MySQL выходит далеко за пределы данной статьи, скажем только что ALL PRIVELEGES дает пользователь все права на свои базы, но не позволяет управлять правами доступа для других пользователей. Для этого используется набор прав ALL PRIVELEGES WITH GRANT OPTION , который имеет по умолчанию root . Для простого пользователя такой набор прав избыточен.

Попробуем создать нового пользователя:

Create user "petrov"@"localhost" identified by "password";

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

Grant usage on *.* to "petrov"@"localhost";

Затем можем выставлять права по своему усмотрению, например, выдача полных прав на базы с шаблоном имени petrov_ :

Grant all privileges on `petrov\_%`.* to "petrov"@"localhost";

Обратите внимание, что шаблон следует обернуть символами грависа (` ), которые расположены на клавише с русской буквой Ё.

Выдать права на отдельную базу можно так:

Grant all privileges on andrey_drupal8.* to "petrov"@"localhost";

Для отбора прав служит команда REVOKE, которая имеет аналогичный синтаксис, только to (кому ), заменяем на from (у кого ). Например:

Revoke all privileges on andrey_drupal8.* from "petrov"@"localhost";

Для того чтобы MySQL сервер применил права следует заставить его перезагрузить кэш привилегий командой:

Flush privileges;

Также вам может понадобиться сменить пароль пользователя:

Set password for "petrov"@"localhost" = password("newpassword");

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

Rename user "petrov"@"localhost" to "petr"@"127.0.0.1";

Ну и наконец удалить учетную запись:

Drop user "petr"@"127.0.0.1";

Перейдем от пользователей к базам данных, в простейшем случае для создания новой базы достаточно команды:

Create database petrov_newdb;

Это создаст базу данных с кодовой страницей и кодировкой сравнения по умолчанию. Если сервер СУБД отдельно не настраивался, то такой кодировкой скорее всего будет latin1_swedish_ci , что в ряде случаев может вызвать проблемы, если не сейчас, то в дальнейшем, поэтому правилом хорошего тона будет явно указывать кодировку при создании базы. Для UTF-8 это будет так:

Create database petrov_newdb default character set utf8 collate utf8_general_ci;

Для Windows-1251:

Create database petrov_newdb default character set cp1251 collate cp1251_general_ci;

Для удаления базы используйте:

Drop database petrov_newdb;

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

По мере активной работы MySQL базы могут фрагментироваться, а также содержать ошибки в данных таблиц. Мы не говорим сейчас о серьезных сбоях, такие ситуации следует рассматривать индивидуально, а о простейших ошибках, которые успешно устраняются средствами самой СУБД. Для проверки, ремонта и оптимизации удобно использовать утилиту mysqlcheck.

Для проверки базы данных выполните, где andrey_drupal8 - имя базы:

Mysqlcheck -u root -p --check andrey_drupal8

Сразу все базы можно проверить командой:

Mysqlcheck -u root -p --check --all-databases

А так как весь вывод на экран скорее всего не поместится, то есть смысл перенаправить его команде less :

Mysqlcheck -u root -p --check --all-databases | less

Обратите внимание, что less позволяет прокручивать вывод как вниз, так и вверх, используя стрелки, для выхода нажмите q .

Если в какой-либо из баз были обнаружены ошибки стоит попытаться их исправить, для этого укажите:

Mysqlcheck -u root -p --auto-repair andrey_drupal8

Для оптимизации используйте ключ --optimize , оптимизировать можно отдельную базу или несколько, для этого перечислите их после ключа --databases :

Mysqlcheck -u root -p --optimize --databases andrey_drupal8 petrov_newdb

а также сразу все:

Mysqlcheck -u root -p --optimize --all-databases

Выгрузка и загрузка дампов БД MySQL

Еще одна распространенная задача при администрировании любой СУБД, дампы баз используются как для резервного копирования, так и для переноса или создания копий. Если резервное копирование - процесс автоматизированный, то создание копий для переноса на другой сервер или перед какими-то существенными вмешательствами в структуру базы приходится делать вручную.

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

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

Mysqldump -u root -p andrey_drupal8 > ~/drupal8.sql

Чтобы выгрузить сразу несколько баз используйте ключ --databases или --all-databases для создания дампа сразу всех баз. Вывод команды следует направить в файл и указать его расположение, в нашем случае это файл drupal8.sql в домашней директории. Также можно передать вывод по конвейеру архиватору и получить сразу архив:

Mysqldump -u root -p andrey_drupal8 | gzip > ~/drupal8.sql.gz

В целом мы не советуем использовать один дамп сразу для нескольких баз, лучшим вариантом будет свой дамп для каждой базы, в тоже время использование ключа --all-databases оправдано в случаях, когда вам надо быстро сделать резервную копию всего сервера, например, при обновлении или переустановке СУБД, чтобы можно было быстро восстановить информацию если что-то пойдет не так.

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

Mysql -u root -p andrey_drupal8 < ~/drupal8.sql

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

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

Mysql -u root -p < ~/all.sql

В этом случае каждая база будет загружена в свой источник, если база источник не существует, то она будет создана.

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

Восстановление забытого пароля root MySQL

Еще одна очень популярная задача. Скажем сразу, восстановить пароль суперпользователя MySQL, как и любого другого пользователя, нельзя, но можно его сменить. Для этого нужно обладать правами суперпользователя операционной системы. Сначала остановим службу СУБД:

Service mysql stop

Затем запускаем ее в безопасном режиме с пропуском таблиц привилегий:

Mysqld_safe --skip-grant-tables &

Обратите внимание, что после выполнения данной команды приглашение командной строки пропадет, останется один мигающий курсор. Пишем прямо туда:

Mysql -u root

и попадаем в среду mysql с правами root без ввода пароля.

Нам кажется, что многие уже догадались, что делать дальше, однако выполнить set password в данном режиме не получится, поэтому надо идти другим путем. Вспоминаем, что информация о пользователях хранится в таблице user служебной БД mysql . После чего выполним следующий запрос:

Update mysql.user set password = password ("newpassword") where user="root";

Важно! В новых версиях MySQL вместо колонки password таблицы user используется колонка authentication_string , поэтому вместо set password следует указывать set authentication_string .

Данный запрос установит новый пароль newpassword для всех строк в колонке user которых значится root .

Обновим кэш привилегий:

Flush privileges;

Выйдем из режима:

Остановим службу и запустим в обычном режиме:

Service mysql stop
service mysql start

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

• Теги:

Please enable JavaScript to view the

Утилита командной строки mysql является простой SQL-оболочкой (с возможностями библиотеки readline проекта GNU). Она поддерживает интерактивный и неинтерактивный режимы. В интерактивном режиме результаты запроса представляются в ASCII-формате. При использовании в неинтерактивном режиме (например, в качестве фильтра) результат представляется в текстовом формате с символом табуляции в качестве разделителя (выходной формат можно изменить при помощи параметров командной строки). Сценарии можно запускать, как показано ниже:

Shell> mysql database output.tab

Если возникают проблемы из-за недостатка памяти на данном клиенте, применяйте параметр --quick ! Это заставит mysql использовать функцию mysql_use_result() вместо функции mysql_store_result() для получения результирующей выборки данных.

Использовать mysql очень легко. Запустите mysql database или mysql --user=user_name --password=your_password databas e. Наберите SQL-команду прямо в командной строке, завершив ее одним из символов: `;" , `\g" или `\G" , и нажмите клавишу ``Ввод"".

Утилита командной строки mysql поддерживает следующие параметры:

Help Вывод справочной информации об использовании программы и выход из нее. -A, --no-auto-rehash Отключает автоматическое рехеширование. rehash следует использовать для получения хеша таблиц и полей. Это обеспечивает более быстрый старт mysql . --prompt=... Устанавливает приглашение на ввод команд в заданном формате. -b, --no-beep Выключает звуковой сигнал об ошибке. -B, --batch Выводит результаты в пакетном режиме с символом табуляции в качестве разделителя, каждая строка с новой строки. Файл истории не используется. --character-sets-dir=... Директория, где находятся наборы символов. -C, --compress Использовать сжатие данных в протоколе сервер/клиент. -#, --debug[=...] Журнал отладки. Значение по умолчанию - "d:t:o,/tmp/mysql.trace". -D, --database=... Имя используемой базы данных. Большей частью применяется в конфигурационном файле `my.cnf" . --default-character-set=... Установить набор символов по умолчанию. -e, --execute=... Выполнить команду и завершить программу (вывод результата как и для --batch). -E, --vertical Вывести результаты запроса (строки) по вертикали. Можно произвести вывод подобным образом и без данного параметра, завершая команды символами \G . -f, --force Продолжать обработку даже при обнаружении ошибки SQL. -g, --no-named-commands Выключает именованные команды. Следует использовать только команды вида \* либо применять именованные команды только в начале строки, заканчивающейся символом `;" . Начиная с версии 10.9 клиент запускается с этой опцией, включенной по умолчанию! С опцией -g , однако, длинные команды все еще работают с первой строки. -G, --enable-named-commands Разрешает именованные команды. Допускаются длинные команды, а также укороченные команды вида \*. -i, --ignore-space Игнорировать пробел после имен функций. -h, --host=... Подсоединиться к базе данных на указанном хосте. -H, --html Вывести выходные данные в виде HTML. -L, --skip-line-numbers Не указывать номера строк для ошибок. Полезно для сравнения результирующих файлов, включающих сообщения об ошибках. --no-pager Блокирует пейджер (программа постраничного вывода) и выводит результат в стандартный вывод stdout (в Unix). Смотрите также команду \ h (интерактивная помощь). --no-tee Блокирует выходной файл. Смотрите также команду \h (интерактивная помощь). -n, --unbuffered Очищать буфер после каждого запроса. -N, --skip-column-names Не указывать имена столбцов в результатах. -O, --set-variable var=option Установить значение переменной. Список используемых переменных выводится через --help . -o, --one-database Обновить только базу данных, установленную по умолчанию. Позволяет пропускать обновления другой базы данных в журнале обновления. --pager[=...] Устанавливает тип данных вывода. По умолчанию это переменная окружения PAGER . Ее возможные значения - less, more, cat [> имя файла], и т.д. См. также команду \h (интерактивная помощь). Этот параметр не работает в пакетном (batch) режиме. Пейджер работает только под Unix. -p, --password[=...] Пароль, используемый при подсоединении к серверу баз данных. Если в командной строке пароль не указан, то он запрашивается у пользователя. При использовании краткой формы -p не оставляйте пробел между параметром и значением пароля. -P --port=... Номер порта TCP/IP, используемый для подсоединения. -q, --quick Не кэшировать результат. Выводить его строка за строкой так, как он приходит от сервера. Это может замедлить скорость работы сервера, если вывод результата будет приостановлен. Файл истории не используется. -r, --raw Показывать значения столбцов без какого-либо преобразования. Используется с --batch . -s, --silent Режим молчания. Выводить только сообщения об ошибках. -S --socket=... Файл сокета, используемый для подсоединения. -t --table Выводить результат в табличном формате. Установлено по умолчанию для непакетного режима. -T, --debug-info Выводить некоторые отладочные данные при выходе из программы. --tee=... Присоединить что-либо к выходному файлу. Смотрите также команду \h (интерактивная помощь). Этот параметр не работает в пакетном режиме. -u, --user=# Имя пользователя MySQL, если этот пользователь не является активным в данное время. -U, --safe-updates[=#], --i-am-a-dummy[=#] Разрешает выполнять только операции UPDATE и DELETE , используя ключи. Более полная информация об этом параметре приведена ниже. Можно сбросить данный параметр, установив в конфигурационном файле `my.cnf" значение аргумента --safe-updates=0 . -v, --verbose Более расширенный режим вывода результатов (-v -v -v дает формат вывода таблицы). -V, --version Вывод информации о версии и выход из программы. -w, --wait Если соединение с сервером упало, подождать и попытаться восстановить его, вместо того, чтобы прервать работу.

Через параметры командной строки -O или --set-variable можно также установить следующие переменные:

Если ввести в командной строке help , программа mysql выведет список поддерживаемых ею команд:

Mysql> help Команды MySQL help (\h) Выводит данный текст. ? (\h) Синоним для help. clear (\c) Команда очистки. connect (\r) Снова подключиться к серверу. Дополнительные аргументы - db и host. edit (\e) Редактировать текущую команду с помощью $EDITOR. ego (\G) Послать текущую команду MySQL серверу и вывести результат по вертикали. exit (\q) Выйти из программы. То же что и quit. go (\g) Послать текущую команду MySQL серверу. nopager (\n) Блокировать пейджер, выводить через stdout. notee (\t) Не добавлять записи в выходной файл outfile. pager (\P) Установить PAGER . Выводить результаты запроса через PAGER. print (\p) Вывести текущую команду. prompt (\R) Изменить формат приглашения на ввод команд mysql. quit (\q) Выйти из программы. rehash (\#) Восстановить таблицу хэшей. source (\.) Запустить на выполнение файл с SQL-сценарием. Указать имя файла в качестве аргумента. status (\s) Получить информацию о статусе сервера. tee (\T) Установить параметр outfile . Присоединить что-либо к данному выходному файлу. use (\u) Использовать другую базу данных. Указать имя базы данных в качестве аргумента.

Команда pager работает только под Unix.

Результат этого следующий:

• Не разрешено выполнять команды UPDATE или DELETE , если не указаны ограничения по ключам в секции WHERE . Однако можно заставить выполняться команды UPDATE / DELETE , используя оператор LIMIT: UPDATE table_name SET not_key_column=# WHERE not_key_column=# LIMIT 1;
• Все слишком большие результаты ограничены строками #select_limit# .
• SELECT ы, которые могут потребовать для исполнения количество комбинаций строк более, чем #max_join_size# , будут прерваны.

Несколько полезных советов по использованию клиента mysql:

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

Mysql> SELECT * FROM mails WHERE LENGTH(txt) sbj: UTF-8 txt: >>>>> "Thimble" == Thimble Smith writes: Thimble> Hi. I think this is a good idea. Is anyone familiar with UTF-8 Thimble> or Unicode? Otherwise, I"ll put this on my TODO list and see what Thimble> happens. Yes, please do that. Regards, Monty file: inbox-jani-1 hash: 190402944 1 row in set (0.09 sec)

• Для входа в систему можно использовать опции команды tee. Она может быть запущена с помощью параметра --tee=... для mysql или интерактивно из командной строки вводом команды tee . Все представляемые на экране данные будут также добавлены к заданному файлу. Это может быть очень полезно для целей отладки программы. Утилиту tee можно блокировать из командной строки командой notee . Повторный запуск команды tee снова включит журналирование. Если при этом параметр для команды tee не указан, то будет использоваться предыдущий файл. Следует учесть, что команда tee будет записывать результаты в файл после каждой выполненной команды, как раз перед появлением командной строки для ввода очередной команды.
• При помощи опции --pager[=...] стал возможным просмотр или поиск результатов в интерактивном режиме с помощью Unix-программ less , more или иных подобных. Если явно не указать аргумент в этом параметре, клиент mysql будет искать переменную окружения PAGER и установит значение pager . Программу pager также можно запустить из интерактивной командной строки командой pager и остановить командой nopager . Команда может принимать аргумент, который является необязательным; pager будет установлена в значение этого аргумента.. Команда pager может быть вызвана и без аргумента, но это требует использования опции --pager или соответствующей установки по умолчанию стандартного вывода stdout . Команда pager работает только в Unix, поскольку использует функцию popen() , отсутствующую в Windows. Вместо этого в Windows можно использовать параметр tee , хотя в ряде ситуаций это менее удобно, чем применение команды pager .
• Несколько советов касательно команды pager: Ее можно использовать для записи в файл: mysql> pager cat > /tmp/log.txt и результаты будут направлены только в файл. Вызываемые командой pager программы могут принимать любые допустимые опции: mysql> pager less -n -i -S Обратите особое внимание на опцию -S в вышеприведенном примере. Она может быть очень полезна при просмотре результатов. Попробуйте применить ее с горизонтальным выводом (завершайте команды символами "\g", or ";") и с вертикальным (в конце команд - "\G"). Очень громоздкие результаты вывода иногда трудно бывает прочесть с экрана, в этом случае команда less с опцией -S позволит просмотреть результаты в интерактивном режиме слева направо, при этом при появлении строк с длиной больше, чем ширина экрана, их вывод будет продолжен вывод с новой строки. Вывод данных в таких случаях получается более удобочитаемым. При интерактивном вызове команды less с опцией "-S" можно переключать режим ее работы (включено/выключено) из командной строки. Чтобы получить дополнительную информацию относительно less, обращайтесь к описанию команды "h".
• В заключение отметим (если вы этого еще не поняли из предыдущих примеров), что существует возможность комбинировать очень сложные способы обработки результатов. Так, в следующем примере результаты будут посланы в два различных каталога, смонтированных на двух различных жестких дисках в /dr1 and /dr2, и, несмотря на это, результаты можно увидеть на экране посредством команды less: mysql> pager cat | tee /dr1/tmp/res.txt | \ tee /dr2/tmp/res2.txt | less -n -i -S
• Приведенные выше функции можно тоже комбинировать: запустив tee и установив pager в less , можно просматривать результаты с помощью Unix-команды less и при этом одновременно производить запись в файл. Разница между служебной Unix-утилитой tee , используемой в программе pager , и встроенной в клиент mysql командой tee заключается в том, что встроенная команда tee работает даже в том случае, если в Unix утилита tee недоступна. Встроенная команда tee также ведет запись всего, что выводится на экран, тогда как утилита Unix tee , используемая с pager , не делает этого в достаточном объеме. Последнее, но тем не менее важное обстоятельство состоит в том, что интерактивная команда tee более удобна для переключения режимов работы включено/выключено, если при записи в файл иногда возникает необходимость отключить эту функцию.

Можно изменить формат приглашения в командной строке клиента mysql .

Возможны следующие опции приглашения:

Опция	Описание
\v	версия mysqld
\d	имя используемой базы данных
\h	имя хоста, к которому производится подсоединение
\p	номер порта, через который производится подсоединение
\u	имя пользователя
\U	полный адрес username@host
\\	обратный слэш `\"
\n	символ новой строки
\t	табуляция
\	пробел
\_	пробел с подчеркиванием
\R	время по военному часовому поясу (0-23)
\r	время по стандартному часовому поясу (1-12)
\m	минуты
\y	два разряда года
\Y	четыре разряда года
\D	полный формат даты
\s	секунды
\w	день недели в трехбуквенном формате (Mon, Tue, ...)
\P	Время до полудня/после полудня (am/pm)
\o	месяц в числовом формате
\O	месяц в трехбуквенном формате (Jan, Feb, ...)
\c	Счетчик, подсчитывающий количество вводимых команд

Символ `\" за которым следует любая другая буква, просто дополняет эту букву.

Установить параметры приглашения можно следующими способами:

В переменных окружения Можно установить переменную окружения MYSQL_PS1 для строки приглашения. Например: shell> export MYSQL_PS1="(\u@\h) [\d]> " `my.cnf" `.my.cnf" Можно установить опцию prompt в любом конфигурационном файле MySQL в группе mysql . Например: prompt=(\u@\h) [\d]>\_ В командной строке Можно установить опцию --prompt из командной строки утилиты mysql . Например: shell> mysql --prompt="(\u@\h) [\d]> " (user@host) > В интерактивном режиме Можно также использовать команду prompt (или \R) для изменения настроек приглашения в интерактивном режиме. Например: mysql> prompt (\u@\h) [\d]>\_ PROMPT set to "(\u@\h) [\d]>\_" (user@host) > (user@host) > prompt Возвращение к исходным (по умолчанию) настройкам PROMPT в утилите mysql> mysql>

Запуск и остановка сервера MySQL из командной строки

Запустить сервер MySQL вручную можно одним из двух способов:

Дважды щелкните на значке файла mysqld-nt.exe, расположенного в подкаталоге bin каталога, где установлена программа MySQL.

Откройте окно командной строки Windows. Для этого нажмите кнопку Пуск, в меню выберите пункт Выполнить, в появившемся окне Запуск программы в поле Открыть введите команду cmd и нажмите кнопку OK. На экране появится окно командной строки (рис. 1.25).

Рис. 1.25. Окно командной строки

В командной строке введите команду

mysqld-nt

и нажмите клавишу Enter. Сервер MySQL будет запущен.

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

C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt

Если вы хотите просматривать в окне командной строки диагностические сообщения о работе сервера, вместо mysqld-nt введите

mysqld-nt –console

Внимание!

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

Чтобы установить пароль root, откройте новое окно командной строки и введите следующую команду:

mysqladmin -u root password <пароль>

(или C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin -u root password <пароль>, если путь к подкаталогу bin не был добавлен в значение системной переменной Path при настройке сервера) и нажмите клавишу Enter.

В дальнейшем, если потребуется сменить пароль пользователя root, выполните такую же команду, только с использованием опции – р:

mysqladmin -u root -p password <новый пароль>

После появления приглашения Enter password (Введите пароль) укажите прежний пароль и нажмите клавишу Enter.

Наконец, если необходимо остановить сервер MySQL, выполните команду

mysqladmin -u root -p shutdown

и в ответ на приглашение Enter password (Введите пароль) введите пароль пользователя root. Нажмите клавишу Enter. Сервер MySQL будет остановлен.

Для запуска и остановки сервера MySQL можно также использовать графическую утилиту MySQL Administrator.

Запуск и остановка сервера MySQL с помощью MySQL Administrator

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

1. Запустите программу MySQL Administrator (Пуск → Все программы → MySQL → MySQL Administrator). На экране появится окно соединения с сервером (рис. 1.26).

Рис. 1.26.

2. Нажмите клавишу Ctrl и, удерживая ее, щелкните на кнопку Skip (Пропустить), появившуюся в правом нижнем углу окна вместо кнопки Cancel (Отмена). На экране появится главное окно MySQL Administrator (рис. 1.27).

Рис. 1.27. Главное окно MySQL Administrator

4. Если сервер MySQL не был сконфигурирован как сервис Windows, то кнопка Start Service (Запустить сервис), расположенная в правой области окна, недоступна. Необходимо выполнить следующие предварительные действия:

1) перейдите на вкладку Configure Service (Настройка сервиса). Найдите внизу вкладки кнопку Install new Service (Установить новый сервис) и нажмите ее;

2) в появившейся диалоговой панели укажите название сервиса и нажмите кнопку OK;

3) в поле Config Filename (Имя конфигурационного файла) введите путь к конфигурационному файлу my.ini (рис. 1.28), например C: \Program Files\ MySQL\MySQL Server 5.0\my.ini. Красный цвет шрифта означает, что файл не найден; если цвет сменился на обычный, то путь указан верно;

4) в поле Path to binary (Путь к исполняемому файлу) введите путь к файлу mysqld-nt.exe, например C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt;

6) вернитесь на вкладку Start/Stop Service (Запуск/остановка сервиса).

Рис. 1.28. Закладка Configure Service 5. Нажмите кнопку Start Service (Запустить сервис). Сервер MySQL будет запущен.

Внимание!

Если при настройке сервера MySQL вы не указывали пароль пользователя root, то необходимо установить его при первом запуске сервера (иначе кто угодно может управлять сервером под именем root без пароля). В текущей версии MySQL Administrator установка пароля root недоступна, и для выполнения этой операции нужно использовать утилиту командной строки mysqladmin (см. подраздел «Запуск и остановка сервера MySQL из командной строки»).

Чтобы остановить сервер MySQL с помощью MySQL Administrator, выполните следующие действия.

1. Запустите программу MySQL Administrator (Пуск → Все программы → MySQL → MySQL Administrator). На экране появится окно соединения с сервером (см. рис. 1.26).

Server Host (Имя хоста) – значение localhost (локальный компьютер);

Port (Порт) – номер порта, выбранный при настройке сервера (по умолчанию – 3306);

Usename (Имя пользователя) – значение root;

Password (Пароль) – пароль пользователя root. Нажмите кнопку OK.

3. В главном окне MySQL Administrator в левой области щелкните пункт Service Control (Управление сервисом).

4. В правой области окна нажмите кнопку Stop Service (Остановить сервис). Сервер MySQL будет остановлен.

В следующем подразделе вы узнаете, как запустить сервер MySQL с помощью средств администрирования Windows.

Запуск и остановка сервера MySQL с панели управления

Если сервер MySQL был сконфигурирован как сервис Windows с помощью мастера настройки (см. подраздел «Настройка сервера MySQL») или с помощью утилиты MySQL Administrator (см. подраздел «Запуск и остановка сервера MySQL с помощью MySQL Administrator»), то запускать и останавливать его можно с помощью компонента Службы панели управления.

Чтобы вызвать компонент Службы, нажмите кнопку Пуск, в меню выберите пункт Панель управления, затем в панели управления дважды щелкните на значке Администрирование и, наконец, в окне средств администрирования дважды щелкните на значке Службы. На экране появится окно Службы (рис. 1.29) со списком всех локальных служб.

Рис. 1.29. Сервис MySQL в панели управления

В окне Службы щелкните на названии сервиса MySQL (название определяется при создании сервиса в мастере настройки или в MySQL Administrator). Затем щелкните на нужную ссылку под названием сервиса: Запустить службу, Остановить службу или Перезапустить службу.

После того как сервер MySQL запущен, к нему можно подключиться. В следующих подразделах вы узнаете, как это сделать.

Подключение к серверу из командной строки

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

1. Откройте окно командной строки Windows. Для этого нажмите кнопку Пуск, в меню выберите пункт Выполнить, в появившемся окне Запуск программы введите в поле Открыть команду cmd и нажмите кнопку OK.

2. В командной строке (см. рис. 1.25) введите команду

mysql -h <Имя компьютера> -u <Имя пользователя> -p

(где <Имя компьютера> – это имя компьютера, на котором работает сервер) и нажмите клавишу Enter. После появления приглашения Enter password (Введите пароль) введите пароль пользователя.

Если требуется подключиться к серверу MySQL, работающему на этом же компьютере, имя компьютера (localhost) можно не указывать, например

mysql -u root -p

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

Рис. 1.30. Соединение с сервером MySQL из командной строки

Чтобы отключиться от сервера, просто наберите в командной строке команду

и нажмите клавишу Enter.

Альтернативный способ подключения к серверу MySQL предоставляет графическая утилита MySQL Query Browser.

Подключение к серверу с помощью MySQL Query Browser

Утилита MySQL Query Browser – интерфейс для создания, редактирования и выполнения инструкций SQL. Она удобнее, чем командная строка. Если вы решили использовать для работы с базой данных MySQL Query Browser, то для подключения к серверу выполните следующие действия.

1. Запустите программу MySQL Query Browser (Пуск → Все программы → MySQL → MySQL Query Browser). На экране появится окно соединения с сервером (рис. 1.31).

Рис. 1.31. Окно соединения с сервером MySQL

2. В поля окна соединения с сервером введите параметры соединения:

Server Host (Имя хоста) – имя компьютера, на котором работает сервер MySQL;

Port (Порт) – номер порта, выбранный при настройке сервера (по умолчанию – 330 6);

Username (Имя пользователя) – имя пользователя;

Password (Пароль) – пароль пользователя;

Default Schema (Схема по умолчанию) – имя базы данных, с которой вы будете работать (это может быть как существующая, так и новая база данных).

3. Нажмите кнопку OK. Если вы ввели имя новой базы данных, то в появившейся диалоговой панели нажмите кнопку Yes (Да) для создания этой базы данных.

После подключения к серверу на экране появится главное окно MySQL Query Browser (рис. 1.32). В нем вы можете выполнять любые операции с базой данных: добавлять таблицы, вводить и запрашивать данные, регистрировать новых пользователей и др.

Рис. 1.32. Главное окно MySQL Query Browser

Внимание!

Шрифт, который по умолчанию используется в MySQL Query Browser для отображения SQL-запросов, не поддерживает русские буквы. Чтобы вводить русские буквы в текстах запросов, необходимо выбрать другой шрифт (например, Arial или Book Antiqua). Для этого в главном окне MySQL Query Browser откройте меню Tools (Сервис) и выберите пункт Options (Параметры). В появившемся окне Options (Параметры) в левой области щелкните пункт General Options (Общие параметры) и в правой области в поле Code Font (Шрифт кода) выберите из списка нужный шрифт. Нажмите кнопку Apply (Сохранить).

Чтобы отключиться от сервера, просто закройте окно MySQL Query Browser.

На этом мы заканчиваем знакомство с MySQL и переходим к подведению итогов.

1.6. Резюме

В этой главе были рассмотрены СУБД MySQL и графические утилиты MySQL Administrator и MySQL Query Browser. Вы освоили достаточно сложную процедуру установки и настройки сервера MySQL, научились управлять сервером и подключаться к нему. Вы также узнали, как устроена реляционная база данных и как спроектировать собственную БД.

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

Глава 2
Управление базой данных с помощью SQL

Из этой главы вы узнаете, как работать с данными в СУБД MySQL, как определять их структуру, а также как добавлять, изменять и удалять данные. Эти операции выполняет SQL – универсальный язык структурированных запросов, являющийся стандартным средством доступа к реляционным базам данных.

Для выполнения SQL-команд вы можете использовать любое из многочисленных клиентских приложений сервера MySQL. В этой главе не будут рассматриваться приложения сторонних разработчиков. Вы познакомитесь только с приложениями, созданными компанией MySQL AB: утилитой командной строки mysql и графической утилитой MySQL Query Browser.

В обеих утилитах доступны все операции с данными. В MySQL Query Browser удобно работать с базой данных: ее компоненты наглядно представлены, можно непосредственно редактировать данные (без использования SQL-оператора UPDATE), работать с запросами, например строить их с помощью специального инструмента (при этом названия таблиц и столбцов вводить вручную не нужно), сохранять запросы в файле, экспортировать результаты запросов и многое другое. Вы можете узнать о всех возможностях MySQL Query Browser, обратившись к документации на русском языке, найти которую можно по ссылке http://dev.mysql.com/doc/query-browser/ru/index.html .

Вначале вы узнаете, как выполнять SQL-команды в MySQL Query Browser и в командной строке, а в дальнейшем будет рассмотрен только синтаксис SQL-команд.

2.1. Выполнение SQL-команд

Прежде чем выполнять SQL-команды, необходимо подключиться к работающему серверу MySQL (как это сделать, рассказывалось в главе 1). В этом разделе вы узнаете, как создавать SQL-команды и передавать их серверу для выполнения.

Если вы используете командную строку, то для выполнения SQL-команды введем ее текст в окне командной строки и нажмем клавишу Enter для отправки команды на сервер. Чтобы избежать проблем с кодировкой русскоязычных данных, перед началом работы с данными выполните команду

SET NAMES cp866;

Результат выполнения этой команды вы видите на рис. 2.1.

Рис. 2.1. Установка кодировки в командной строке

Команду SET NAMES необходимо повторять при каждом подключении к серверу с помощью командной строки. Эта команда указывает серверу, что данное клиентское приложение (утилита mysql) использует кодировку CP-866 (это кодировка командной строки Windows), и сервер будет автоматически выполнять преобразование кодировок при обмене данными с клиентским приложением.

После смены кодировки вы можете вводить в командной строке любые SQL-команды. Сообщение о результате выполнения команды, а также запрошенные данные выводятся непосредственно в окне командной строки (рис. 2.2).

Рис. 2.2. Выполнение SQL-запроса в командной строке

Утилита mysql позволяет вводить и многострочные команды (на рис. 2.3 таким образом введена команда SHOW DATABASES). Если не введена точка с запятой – признак конца команды, то при нажатии клавиши Enter утилита не отправляет команду на сервер, а предлагает продолжить ввод команды. Если вы хотите отменить ввод многострочной команды, наберите \c (рис. 2.3).

Рис. 2.3. Многострочная команда

Если вы используете MySQL Query Browser, то кодировку устанавливать не нужно – эта программа работает в кодировке UTF-8 и сама сообщает об этом серверу. Однако в MySQL Query Browser существует проблема отображения русских букв в области запросов (области, куда вводится текст SQL-команд, рис. 2.4). Для решения этой проблемы необходимо изменить шрифт, используемый в области запросов (как это сделать, рассказывалось в конце предыдущей главы). Выполнить смену шрифта достаточно один раз.

Рис. 2.4. Выполнение SQL-запроса в MySQL Query Browser

В области запросов вы можете ввести сразу несколько SQL-команд, как показано на рис. 2.4. Текущая команда (на одной из ее строк установлен курсор) выделена белым цветом фона, остальные команды отображены на светло-сером фоне. Чтобы выполнить текущую команду, вы можете нажать либо кнопку Execute, расположенную справа от области запросов, либо комбинацию клавиш Ctrl+Enter. После выполнения команды запрошенные данные выводятся в области результатов, а сообщение о результате выполнения команды – в нижней части этой области.

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

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

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

Чтобы создать базу данных, выполним команду

CREATE DATABASE <Имя базы данных>;

Например, команда

CREATE DATABASE SalesDept;

создает базу данных с именем SalesDept (Отдел продаж).

Если вам по каким-либо причинам нужно для новой базы данных установить кодировку по умолчанию, отличную от кодировки, указанной при настройке MySQL, то при создании базы данных вы можете указать нужную кодировку (character set) и/или правило сравнения (сортировки) символьных значений:

CREATE DATABASE <Имя базы данных>

CHARACTER SET <Имя кодировки>

COLLATE <Имя правила сравнения>;

Например, если вы будете в новую базу импортировать данные, которые находятся в кодировке CP-1251, то укажем эту кодировку при создании базы данных таким образом:

CREATE DATABASE SalesDept

CHARACTER SET cp1251 COLLATE cp1251_general_ci;

Совет

Чтобы просмотреть список используемых в MySQL кодировок, выполним команду SHOW CHARACTER SET; а чтобы увидеть список правил сравнения символьных значений – команду SHOW COLLATION;. При этом можно использовать оператор LIKE: например, чтобы увидеть все правила сравнения для кодировки CP-1251, выполним команду SHOW COLLATION LIKE %1251 %;. Окончание «_ci» (case insensitive) в названии правил сравнения означает, что при сравнении и сортировке регистр символов не учитывается, окончание «_cs» (case sensitive) – регистр учитывается, окончание «_bin» (binary) – сравнение и сортировка выполняются по числовым кодам символов. Для большинства правил сравнения вы можете найти описание (то есть порядок следования символов, в соответствии с которым будут упорядочиваться текстовые значения) на веб-странице http://www.collation-charts.org/mysql60/ .

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

Изменить кодировку и/или правило сравнения символьных значений для базы данных вы можете с помощью команды

ALTER DATABASE <Имя базы данных>

CHARACTER SET <Имя кодировки>

COLLATE <Имя правила сравнения>;

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

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

DROP DATABASE <Имя базы данных>;

Внимание!

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

Одну из баз, созданных на данном сервере MySQL, вы можете выбрать в качестве текущей базы данных с помощью команды

USE <Имя базы данных>;

Например,:

USE SalesDept;

После этого вы можете выполнять операции с таблицами этой базы данных, не добавляя имя базы в виде префикса к имени таблицы. Например, для обращения к таблице Customers (Клиенты) базы данных SalesDept (Отдел продаж) можно вместо SalesDept.Customers писать просто Customers. Указав текущую базу, вы можете обращаться и к таблицам других баз данных, однако использование имени базы данных в виде префикса при этом обязательно. Выбор текущей базы сохраняется до момента отсоединения от сервера или до выбора другой текущей базы данных.

Чтобы увидеть список всех баз, существующих на данном сервере MySQL, выполните команду

SHOW DATABASES;

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

INFORMATION_SCHEMA – информационная база данных, из которой вы можете получить сведения о всех остальных базах, о структуре данных в них и о всевозможных объектах: таблицах, столбцах, первичных и внешних ключах, правах доступа, хранимых процедурах, кодировках и др. Эта база данных доступна только для чтения и является виртуальной, то есть она не хранится в виде каталога на диске: вся информация, запрашиваемая из этой БД, предоставляется динамически сервером MySQL.

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

Test – пустая база данных, которую можно использовать для «пробы пера» или просто удалить.

Итак, вы освоили основные операции, выполняемые с базой данных как единым целым: команды CREATE DATABASE (создание), ALTER DATABASE (изменение), DROP DATABASE (удаление), USE (выбор текущей базы данных) и SHOW DATABASES (просмотр списка баз данных). Далее мы рассмотрим операции с таблицами. При этом будем считать, что вы выбрали какую-либо базу данных в качестве текущей и работаете с ее таблицами.

2.3. Работа с таблицами

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

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

Чтобы создать таблицу, выполните команду, представленную в листинге 2.1.

Листинг 2.1. Команда создания таблицы

CREATE TABLE <Имя таблицы>

(<Имя столбца 1> <Тип столбца 1> [<Свойства столбца 1>],

<Имя столбца 2> <Тип столбца 2> [<Свойства столбца 2>],

[<Информация о ключевых столбцах и индексах>])

[<Опциональные свойства таблицы>];

Как вы видите, команда создания таблицы может включать множество параметров, однако многие из них задавать необязательно (в листинге 2.1 такие параметры заключены в квадратные скобки). В действительности для создания таблицы достаточно указать ее имя, а также имена и типы всех столбцов; остальные параметры используются в случае необходимости.

Рассмотрим вначале несколько примеров, которые помогут вам освоить команду CREATE TABLE и сразу же, не изучая ее многочисленных параметров, начать создавать собственные (простые по структуре) таблицы.

Предположим, что мы строим базу данных, которую спроектировали в главе 1. Используя команды из предыдущего раздела, мы создали пустую базу данных SalesDept (Отдел продаж) и выбрали ее в качестве текущей. Теперь создадим три таблицы: Customers (Клиенты), Products (Товары) и Orders (Заказы). В листинге 2.2 представлена команда создания таблицы Customers.

Листинг 2.2. Команда создания таблицы Customers

CREATE TABLE Customers

(id SERIAL,

name VARCHAR(100),

phone VARCHAR(20),

address VARCHAR(150),

rating INT,

PRIMARY KEY (id))

В этой команде использовались параметры: во-первых, название таблицы и, во-вторых, названия и типы столбцов, из которых будет состоять таблица (см. также табл. 1.1 в главе 1).

Id – идентификатор записи. Этому столбцу вы назначили тип SERIAL, позволяющий автоматически нумеровать строки таблицы. Ключевое слово SERIAL расшифровывается как BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. Это означает, что в столбец можно вводить большие целые (BIGINT) положительные (UNSIGNED) числа, при этом автоматически контролируется отсутствие неопределенных и повторяющихся значений (NOT NULL UNIQUE). Если при добавлении строки в таблицу вы не укажете значение для этого столбца, то программа MySQL внесет в этот столбец очередной порядковый номер (AUTO_INCREMENT).

Примечание

NULL – это константа, указывающая на отсутствие значения. Если в столбце находится значение NULL, то считается, что никакого определенного значения для этого столбца не задано (поэтому мы также называем NULL неопределенным значением). Не следует путать NULL с пустой строкой («») или числом 0. Значения NULL обрабатываются особым образом: большинство функций и операторов возвращают NULL, если один из аргументов равен NULL. Например, результат сравнения 1 = 1 – истинное значение (TRUE), а результат сравнения NULL = NULL – неопределенное значение (NULL), то есть два неопределенных значения не считаются равными.

Nam – имя клиента, phone – номер телефона и address – адрес. Вы присвоили этим столбцам тип VARCHAR, поскольку они будут содержать символьные значения. В скобках указывается максимально допустимое количество символов в значении столбца.

В-третьих, вы указали, что столбец id будет первичным ключом таблицы, включив в команду создания таблицы определение PRIMARY KEY (id).

В-четвертых, вы задали для этой таблицы два опциональных параметра. Параметр ENGINE определяет тип таблицы. Таблице Customers вы присвоили тип InnoDB, так как только этот тип обеспечивает поддержание целостности связей между таблицами (более подробно о типах таблиц будет рассказано в пункте «Опциональные свойства таблицы»). Параметр CHARACTER SET определяет кодировку по умолчанию для данных в таблице. Поскольку вы не задали кодировку отдельно для столбцов name, phone и address, данные в этих столбцах будут храниться в кодировке UTF-8, которая назначена в качестве кодировки по умолчанию для таблицы Customers.

Следующий пример, который мы рассмотрим, – команда создания таблицы Products (Товары), представленная в листинге 2.3.

Листинг 2.3. Команда создания таблицы Products

CREATE TABLE Products

(id SERIAL,

description VARCHAR(100),

details TEXT,

price DECIMAL(8,2),

PRIMARY KEY (id))

ENGINE InnoDB CHARACTER SET utf8;

Эта команда очень похожа на команду создания таблицы Customers и отличается от нее только названием таблицы и набором столбцов. Столбцы id (номер товара) и description (наименование товара) таблицы Products имеют уже знакомые нам типы. Столбец details (описание) имеет тип TEXT. Этот тип удобно использовать вместо типа VARCHAR, если столбец будет содержать длинные значения: суммарная длина значений всех столбцов с типом VARCHAR ограничена 65 535 байтами для каждой таблицы, а на общую длину столбцов с типом TEXT ограничений нет. Недостатком типа TEXT является невозможность включать такие столбцы во внешний ключ таблицы, то есть создавать связь между таблицами на основе этих столбцов.

Столбец price (цена) имеет тип DECIMAL, предназначенный для хранения денежных сумм и других значений, для которых важно избежать ошибок округления. В скобках мы указали два числа: первое из них определяет максимальное количество цифр в значении столбца, второе – максимальное количество цифр после десятичного разделителя. Другими словами, цена товара может содержать до шести цифр в целой части (6 = 8–2) и до двух цифр в дробной части.

И, наконец, последний пример – команда создания таблицы Orders (Заказы), представленная в листинге 2.4.

Листинг 2.4. Команда создания таблицы Orders

CREATE TABLE Orders

(id SERIAL,

date DATE,

product_id BIGINT UNSIGNED NOT NULL,

qty INT UNSIGNED,

amount DECIMAL(10,2),

customer_id BIGINT UNSIGNED,

PRIMARY KEY (id),

FOREIGN KEY (product_id) REFERENCES Products (id)

ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY (customer_id) REFERENCES Customers (id)

ON DELETE RESTRICT ON UPDATE CASCADE)

ENGINE InnoDB CHARACTER SET utf8;

Особенностью таблицы Orders является наличие внешних ключей: столбец product_id (товар) содержит номера товаров из таблицы Products, а столбец customer_id (клиент) – номера клиентов из таблицы Customers (см. также табл. 1.2 в главе 1). Поскольку номера товаров и клиентов являются большими целыми положительными числами, столбцам product_id и customer_id мы назначили тип BIGINT UNSIGNED.

Далее, чтобы обеспечить автоматическое поддержание целостности связей (о целостности мы рассказывали в главе 1), мы сообщили программе MySQL, какому первичному ключу соответствует каждый внешний ключ. Так, конструкция FOREIGN KEY (customer_id) REFERENCES Customers (id) означает, что в столбце customer_id могут содержаться только значения из столбца id таблицы Customers и неопределенные значения (NULL), а остальные значения запрещены. Для столбца product_id мы задали аналогичное ограничение и присвоили этому столбцу свойство NOT NULL, чтобы запретить регистрировать заказы с неопределенным товаром. Дополнительно мы указали для каждой из связей правила поддержания целостности (их мы также рассматривали в главе 1). Правило ON DELETE RESTRICT означает, что нельзя удалить запись о клиенте, если у этого клиента есть зарегистрированный заказ, и нельзя удалить запись о товаре, если этот товар был кем-то заказан. Правило ON UPDATE CASCADE означает, что при изменении номера клиента в таблице Customers или номера товара в таблице Products соответствующие изменения вносятся и в таблицу Orders.

Примечание

Обратите внимание, что таблицу Orders мы создали в последнюю очередь, так как первичные ключи в таблицах Customers и Products должны быть созданы раньше, чем ссылающиеся на них внешние ключи в таблице Orders. Впрочем, можно было бы создать таблицы без внешних ключей в любой последовательности, а затем добавить внешние ключи с помощью команды ALTER TABLE, которую мы рассмотрим в подразделе «Изменение структуры таблицы».

В наших примерах мы рассмотрели лишь некоторые параметры команды создания таблицы. Теперь мы перечислим все основные параметры, которые могут вам пригодиться при создании таблиц. В пункте «Типы данных в MySQL» речь пойдет о типах столбцов, в пункте «Свойства столбцов» – о настройке ключевых столбцов и, наконец, в пункте «Ключевые столбцы и индексы» – об опциональных свойствах таблицы.

Типы данных в MySQL

Как вы уже знаете, при создании таблицы нужно указать тип данных для каждого столбца. В MySQL предусмотрено множество типов данных для хранения чисел, даты/времени и символьных строк (текстов). Кроме того, существуют типы данных для хранения пространственных (spatial) объектов, которые в этой книге рассматриваться не будут.

Рассмотрим числовые типы данных.

BIT[(<Количествобитов>)].

Битовое число, содержащее заданное количество битов. Если количество битов не указано, число состоит из одного бита.

Целое число в диапазоне либо от -128 до 127, либо (если указано свойство UNSIGNED) от 0 до 255.

BOOL или BOOLEAN.

Являются синонимами к типу данных TINYINT(1) (число в скобках – это количество отображаемых цифр, см. примечание ниже). При этом ненулевое значение рассматривается как истинное (TRUE), нулевое – как ложное (FALSE).

Целое число в диапазоне либо от -32 768 до 32 767, либо (если указано свойство UNSIGNED) от 0 до 65 535.

Целое число в диапазоне либо от -8 388 608 до 8 388 607, либо (если указано свойство UNSIGNED) от 0 до 16 777 215.

INT или INTEGER.

Целое число в диапазоне либо от -2 147 483 648 до 2 147 483 647, либо (если указано свойство UNSIGNED) от 0 до 4 294 967 295.

Целое число в диапазоне либо от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807, либо (если указано свойство UNSIGNED) от 0 до 18 446 744 073 70 9 551 615.

Синоним выражения BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE (большое целое число без знака, принимающее автоматически увеличиваемые уникальные значения; значения NULL запрещены). Используется для автоматической генерации уникальных значений в столбце первичного ключа. Описание свойств UNSIGNED и AUTO_INCREMENT вы найдете в этом подразделе, а свойств NOT NULL и UNIQUE – в пункте «Свойства столбцов».

Примечание

Для всех целочисленных типов данных, кроме BOOL (BOOLEAN) и SERIAL, можно в скобках указать количество отображаемых цифр, которое используется совместно с параметром ZEROFILL: если число содержит меньшее количество цифр, то при выводе оно дополняется слева нулями. Например, если столбец таблицы определен как INT(5) ZEROFILL, то значения «1234567» и «12345» отображаются «как есть», а значение «123» – как «00123». Для типа данных BIT в скобках указывается размер числа, то есть максимальное количество хранимых битов.