Базы данных: MySQL

Данная статья не является полным руководством по администрированию MySQL-сервера, здесь скорее представлены первичные, самые необходимые данные для новичков. А посему рассматриваться будет сервер для ОС Windows, хотя большинство команд справедливы и для *nix-систем. В работе используется в основном консоль, т.к. команды, вводимые в консоли, можно легко перенести, допустим, в web-интерфейс (например, в PHP для этого существуют специальные функции).

Для чего нужна база данных? Реляционные базы данных представляют собой не что иное, как просто несколько таблиц с записями. СУБД предназначены для управления этими таблицами, т.е. создания новых таблиц, удаления ненужных, вставки в таблицы новых записей, изменения записей и, конечно же, обработки запросов, а также некоторых иных, менее часто используемых функций. Собственно, ведь и базы данных необходимы для того, чтобы оперативно выдавать информацию, причем в определенном порядке. СУБД существует великое множество (хотя бы всем известная Microsoft Access). Но на принципах работы мы останавливаться не будем. Пожалуй, наиболее распространенной СУБД в Интернете является MySQL, которая из-за своей быстроты и удобства в использовании получила широкое распространение. Да, и большое значение имеет то, что она совершенно бесплатная, т.е. распространяется по GNU GPL (в общем это значит, что вы можете скачать исходные коды и откомпилировать их у себя или вообще сделать СУБД "под себя", если, конечно, являетесь достаточно грамотным программистом. Правда, если это так, данная статья не для вас:-)).

Итак, в теперешней "лабораторной работе" понадобится: компьютер, собственно сам сервер СУБД MySQL, ярлык консоли под рукой (собственно, он всегда должен быть под рукой: хоть удобство работы под cmd и значительно уступает работе в консольке в Linux, но очень много вещей можно сделать из консоли быстрее, чем лезть через 3-5 менюшек за нужной опцией). Много вещей будет делаться именно через консоль. Конечно, существует множество "юзеро-ориентированных" программ, предназначенных для управления сервером, но рассматривать какую-либо одну не имеет смысла. Если вы будете знать основные команды, использовать MySQL в web-приложениях станет не просто легко, а очень просто! Установка MySQL. Устанавливать и использовать мы будем MySQL-сервер 3.23.38. Зачем вообще нужен SQL? На первый взгляд, для создания более-менее приличного сайта не требуется особых наворотов (каковыми некоторые считают PHP и SQL), но даже просто лог счетчика после пары-другой тысяч "оборотов" вам смотреть интересно не будет. Ежели в лог счетчика писать только время-дату, IP и имя компьютера посетителя, то лог в несколько тысяч строчек не только вытягивать каждый раз с сервера надоест, но и оперативно посмотреть, кто был, допустим, в ночь с четверга на пятницу на вашем, безусловно, замечательном сайте, будет очень трудно. А форум писать только, допустим, на PHP не просто довольно сложно, но и "неприятно".

Итак, инсталляция. Как таковая инсталляция не требует особо больших танцев с бубном. Жмем энное количество раз на кнопочку Next — и наслаждаемся. Пару слов о том, как хранятся данные в MySQL. По умолчанию каждая база данных хранится в отдельной папочке, название которой совпадает с названием этой базы данных, которые, в свою очередь, хранятся (опять же, по умолчанию) в папке DATA директории, в которой установлен сервер. Изменить дефолтовое местоположение данных можно следующим образом. Например, вы хотите создать базу данных, которая будет храниться, скажем, в E:/data/database_name, где database_name — имя базы данных. Тогда вы создаете файл database_name.sym в директории DATA и записываете в него всего одну строчку, которая содержит путь к местоположению вашей базы данных: "E:/data/database_name", и сохраняете. Если заглянуть в папки, в которых хранятся данные, то вы увидите там 3 файла. Данные хранятся в "оптимизированном" виде, т.е. не в виде простого текста — так просто их просмотреть не удастся. Итак, три файла: с расширениями .frm — в нем хранится информация о таблицах, содержащихся в БД, .myd — собственно данные и .myi — индексные данные. Теперь займемся безопасностью. Данные о пользователях MySQL хранит в специальной базе данных, которая называется mysql (оригинально, ничего не скажешь:-)). В ней 5 таблиц, в которых хранятся логины и пароли в зашифрованном виде, а также атрибуты доступа к таблице. Эти таблицы называются:

+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| host            |
| tables_priv     |
| user            |
+-----------------+

В таблицах columns_priv, tables_priv хранятся привилегии пользователей для доступа, соответственно, к колонкам и таблицам, в db — привилегии для доступа к базам данных, в host — информация о компьютерах, с которых подключаются юзвери к MySQL, и, наконец, в таблице user хранится информация о пользователях.
Более подробные сведения можно получить из мануала, который есть в установочном пакете (после установки он находится в mysql/Docs). Это почти "двухметровый" документ, в котором написано практически все, что нужно знать на первых порах, и не только на первых:-). Нас интересует, каким образом сменить юзверя с логином root (те, кто знаком с *nux, знают, что это такое, а для остальных поясняю: суперпользователь с неограниченными правами), а также сделать юзверя, из-под имени которого мы будем работать с сервером. В идеале такой пользователь должен быть только один. Остальные пользователи должны быть с ограниченными правами (главный принцип администрирования — что не разрешено, то запрещено). Дело в том, что по умолчанию суперпользователь не имеет пароля!! И если вы, например, занесете в свою базу данных какие-то сверхсекретные сведения, посмотреть, удалить, изменить их сможет каждый:-). Итак, сервер мы установили, начинаем администрирование. Находим директорию, в которую мы установили наш сервер (по умолчанию это C:\mysql). Находим директорию C:\mysql\bin и видим множество "экзешников". Для начала нам потребуется mysql.exe. Запускаем. Да, опции запуска из консоли можно посмотреть, написав что-то вроде

C:\mysql\bin\mysql.exe -h

после чего будет выведена справка. Но пока они нам не понадобятся. Все sql-команды вводятся в командной строке и должны завершаться ";" или "\g". Для того, чтобы использовать базу данных, необходимо сначала ее выбрать. Для этого существует команда USE. Нам необходима БД "mysql":

mysql> use mysql;

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

mysql> show databases;

Просмотр имеющихся таблиц так же прост, как и просмотр имеющихся БД:

mysql> show tables from databaseNAME;

где databaseName — имя базы данных. Посмотрим, кто у нас числится в юзверях нашего сервера. Пишем

mysql> select user,host,password from user;

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

+---------------+-----------------+------------------+
| user          | host            | password         |
+---------------+-----------------+------------------+
| root          | localhost       |                  |
|               | %               |                  |
|               | localhost       |                  |
| root          | %               |                  |
| mentalzavr    | localhost       |5075638d2dafd717  |
+---------------+-----------------+------------------+

Команда SELECT — она из самых частоиспользуемых команд. С ее помощью мы можем показать, какие данные нас интересуют, и попросить вывести ее на экран. Синтаксис ее таков:

SELECT интересующие_столбцы_в_таблице FROM название_таблицы <параметры>;

Вместо "интересующие_столбцы_в_таблице" можно поставить "*", тогда нам будут выданы значения всех колонок, которые присутствуют в таблице. Чаще всего вместо "<параметры>" пишут еще одно ключевое слово — WHERE. Синтаксис таков:

WHERE название_колонки_таблицы='значение'

Так, теперь пишем:

mysql>UPDA-TE user SET Password=PASSWORD ('NEWpasword') WHERE user='root';

где NEWpassword — новый пароль для суперпользователя. К слову, таким образом можно изменить пароль для любого юзверя. Немного поясню, что значат эти команды. Функция PASSWORD('string') шифрует строку по определенному алгоритму, а команда UPDATE изменяет значение записи WHERE (где) в таблице user. К слову, все пароли в MySQL хранятся в зашифрованном виде.
Теперь нам нужно запретить суперпользователю подключаться с удаленных компьютеров. Для этого удаляем строчки со знаком "%". Пишем "DE-LETE FROM user WHERE Host='%';". Команда DELETE имеет похожий синтаксис с командой SELECT:

SELECT FROM название_таблицы WHERE название_колонки_таблицы='значение';

Я надеюсь, понятно, что команды необходимо набирать без кавычек. Да, последней командой мы также удалили и "пустого" юзверя. Теперь у нас только два пользователя с неограниченными правами: root и mentalzavr, пароль которого, к слову, вы должны были задать при первой загрузке WinMySQLAdmin (теперь он должен висеть в трее этаким светофором, зеленый свет на котором означает нормальную работу сервера, желтый — переходное состояние, а красный — останов сервера). Для того, чтобы наши изменения вступили в силу, необходимо либо перезапустить сервер, либо дать команду "FLUSH PRIVILEGES;". Дело в том, что большинство серверов самого различного назначения читают свой конфиг, где хранится большинство их настроек, только один раз при загрузке. Так и сервер MySQL читает базу данных с параметрами доступа к базам данных при загрузке либо принудительно после специальной команды. Перезапустить сервер можно из панели управления, если вы работаете в Windows NT-серии, к которой относятся Windows XP, 2000 и собственно сама NT:-), либо из той же самой пресловутой командной строки. Сначала посмотрим, как называется наша служба. Для этого воспользуемся так называемой расширенной консолью WMIC (WMI Command-line). Если вы до этого ею не пользовались, система вам ее установит (XP). Это очень мощный инструмент. Но не тема данной статьи:-). Итак, пишем

wmic:root\cli>service list

Видим название службы "MySql". Выходим из WMIC:

wmic:root\cli> exit

Теперь останавливаем службу

C:\>net stop mysql

и стартуем снова:

c:\>net stop mysql

Вы спрашиваете, зачем я все это рассказываю? Одна из причин — таким же образом можно останавливать и запускать любые другие службы. А вторая... Ну, представьте, что у вас стоит сервер MySQL где-нибудь в локальной сети, а ваш компьютер находится в другом здании, и вам необходимо срочно переконфигурировать его. Бежать к серверу и вживую его настраивать? Ну, вы как хотите, а мне лично лень. Коннектимся к серверу и удаленно админим:-). Кстати, программка mysql.exe, которой мы пользовались для подключения к серверу (а именно это мы и делали), может работать и с удаленным сервером. Это легко увидеть, запустив mysql.exe следующим образом:

c:\>mysql -h IP_adresss

где вместо "IP_address" — либо имя компьютера, к которому мы подключаемся, либо его IP-адрес:-). Ну вот, сервер мы перезапустили (или просто написали "FLUSH PRIVILEGES;"). Теперь, ежели все правильно, вы не сможете подключиться к своему серверу, просто запустив mysql.exe. Необходимо ввести пароль. Делается это таким образом:

c:\>mysql -u root -p

Вас попросят ввести пароль на root, которые мы с вами задавали выше. Если все нормально, появится приглашение "mysql>", как и ранее. Теперь попробуем создать еще юзверей. Делается это так. Создадим троих юзверей с разными правами: mysql> GRANT ALL PRIVILEGES ON *.* TO ferst@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO ferst@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysql> GRANT USAGE ON *.* TO doom@localhost;

Кстати, если вы дадите команду "mysql> status", сервер вам выдаст некоторое количество полезной информации. Итак, мы создали трех юзверей. Первый — ferst — получает все права и может подключаться к серверу как с локального компьютера, так и удаленно, пароль для доступа ему назначен sam_pass. Второй юзверь — admin — получает права на reload и refresh, но может подключаться только с локального компьютера (что означает значок "%"), пароль на вход не назначен. Третий юзверь с именем doom может подключаться с локального компьютера, и он создан, так сказать, "бесправным", т.е. все глобальные привилегии ему не даны. То же самое можно было бы сделать по-другому. Например, вот так: mysql> INSERT INTO user VALUES ('localhost','ferst',PASSWORD('some_pass'), 'Y','Y','Y','Y','Y','Y','Y', 'Y','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO user VALUES('%','ferst',PASSWORD('some_pass'), 'Y','Y','Y','Y','Y','Y','Y', 'Y','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO user SET Host='localhost',User='admin',Reload_priv='Y', Process_priv= 'Y'; mysql> INSERT INTO user(Host, User,Password) VALUES('localhost','doom',''); mysql> FLUSH PRIVILEGES;

Наверное, необходимо пояснить, что это значит. Команда INSERT вставляет строчку в таблицу, название которой следует после слова INTO, в скобках даны названия столбцов, имеющихся в этих таблицах. После VALUES следуют значения, которые заносятся в соответствующие колонки. Значения необходимо заключать в кавычки. Последняя команда обновляет права доступа. Буква 'Y' означает включение соответствующей привилегии, 'N' — соответственно выключение. Таблица user имеет следующие столбцы (даны в том же порядке, что и в реальности):
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |

Я думаю, что в пояснениях данная таблица не нуждается. Названия колонок говорят сами за себя (если вы, конечно, знаете английский, как я — на уровне 5-го класса хотя бы:-)). Естественно, что, ежели в предыдущих командах буквы 'Y' заменить на 'N', соответствующий пользователь соответствующего права и не получит:-). Теперь разберемся, каким образом можно изменять права юзверей при подключении к базе данных. Для этого существует команда GRANT.

mysql> GRANT SELECT,INSERT,UPDATE, DELETE,CREATE,DROP
    ON bankaccount.*
    TO custom@localhost
    IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE, DELETE,CREATE,DROP
    ON expenses.*
    TO custom@whitehouse.gov
    IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE, DELETE,CREATE,DROP
    ON customer.*
    TO custom@'%'
    IDENTIFIED BY 'stupid';

Поясню, что делают эти команды. После команды GRANT следует список привилегий, которые необходимо предоставить юзверю custom. После слова ON идет название базы данных, после точки можно указать конкретную таблицу в этой базе данных, а звездочка означает "любая". А после слова TO идет имя_юзверя@имя_ компьютера, с которого этот юзверь сможет подключаться. Знак процентов означает "любой_компьютер". После слов IDENTIFIED BY следует указать в кавычках пароль, который будет требоваться у юзверя при входе. В данном случае это 'stupid'. Конечно, подобного результата можно добиться и непосредственной модификацией таблицы user. Например, вот так:
mysql> INSERT INTO user (Host,User, Password) VALUES('localhost','custom',PASSWORD('stupid'));

Я думаю, понятно, что делает данная команда. Она вставляет в таблицу user запись со значениями в столбцах, которые записаны после слова VALUES. Соответствующей модификацией этой команды можно достичь тех же результатов, что и использованием команд, изложенных выше. И не забудьте после того, как внесете изменения, дать команду FLUSH PRIVILEGES;. Теперь, после того, как вы успешно создали юзеров, переходим к более интересной части — собственно созданию базы данных. Для создания базы данных необходимо из той же директории, что и программу mysql.exe, запустить программу mysqladmin.exe. Если вы запустите эту программу просто без опций, она выдаст вам список параметров запуска. Собственно, и все использование этой программы сводится к запуску ее с соответствующими опциями. Итак, нас прежде всего интересует создание и удаление баз данных, а также подключение через логин и пароль к удаленному серверу (лень — воистину двигатель прогресса!). Подключаемся к удаленному серверу hostname с использованием логина root пароля, который у нас после спросят.

C:\mysql\bin>mysqladmin -h hostname -u root -p OPTIONS

C:\mysql\bin>mysqladmin -h mentalzavr -u mentalzavr -p create database_name

Удаление базы данных database_name после подтверждения и ввода пароля:

C:\mysql\bin>mysqladmin -h mentalzavr -u mentalzavr -p drop database_name

Существуют также и другие параметры запуска, на которых мы особо останавливаться не будем, хотя они не менее важны. Сведения о них вы получите, как я вам уже сказал, запустив mysqladmin без опций. Итак, мы создали базу данных. Теперь необходимо создать таблицы, в которых, собственно, и будут храниться данные. Любая таблица должна относиться к какой-либо базе данных. Поэтому запускаем mysql.exe и выбираем только что созданную базу данных "mysql> use database_name" Для создания таблицы существует команда CREATE. Синтаксис таков:

mysql>create table table_name(name int(3),name char(15));

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

mysql>drop table table_name

Таблиц в базе данных может быть несколько. Теперь вкратце остановимся на типах данных в таблице. Их довольно много, и поэтому я думаю на первых порах (а данная статья предназначена для предоставления только НАЧАЛЬНЫХ сведений об администрировании СУБД MySQL, и ни в коем разе не является полным руководством) ограничиться нижеизложенным. Пожалуй, наиболее часто встречающимся полем в таблицах является порядковый номер. Это довольно удобно. Так, в MySQL существует возможность создать таблицу со столбцом, значение которого будет изменяться на единицу с каждой новой записью. Теперь о том, как это делается. Как обычно, пишете в консоли:
mysql>use test; mysql> CREATE TABLE table_name (No-mer int auto_increment,text char (10),key (Nomer));

Данные команды выбирают в качестве текущей базу данных test и создают таблицу с именем table_name с двумя колонками. Первая колонка имеет атрибут Extra "auto_increment". Это значит, что при добавлении записи в эту таблицу значение (число) в этом столбце будет автоматически увеличиваться на единицу. Посмотреть свойства колонок таблицы можно следующим образом (дав соответствующую команду):

mysql> desc table_name;

Так, теперь коснемся остальных типов данных. Числовые. Их присутствует большое количество, основными являются (на мой взгляд) INT и FLOAT. Те, кто знаком хотя бы немного с языком программирования С++, поймут сразу же, что первый из них — это целочисленный тип данных. FLOAT — тип данных, используемый для хранения чисел с плавающей точкой. Типы данных даты и времени. К ним относятся DATA, TIME, YEAR, а также некоторые другие. Формат хранения данных в типе DATA — "YYYY-MM-DD", в типе TIME — "HH:MM:SS", в типе YEAR — "YYYY". И практически основным типом данных является CHAR. К текстовым типам также относятся TEXT (65535 символов максимально) и некоторые другие.

И в заключение я хотел бы дать несколько полезных команд, которые помогут вам использовать консоль более эффективно. Все советы действительны для cmd.exe — командного процессора Windows. Для начала вам необходимо включить использование мыши в консоли. Для этого щелкните правой кнопкой мыши на заголовке открытого окна консоли и выберите Свойства. И на первой закладке поставьте галочки напротив пунктов Выделение мышью и Быстрая вставка, а также напротив Отбрасывать повторения. Теперь вы можете в консоли использовать все прелести работы с буфером обмена. Выделение мышью происходит как обычно, копирование в буфер обмена — при нажатии на правую кнопку. Вставка производится в место нахождения курсора также правой кнопкой. Пара слов об автоматизации процесса первичного занесения данных в таблицы. Если вы создаете базу данных с нуля, то никаких проблем практически не возникает (ну, если не считать большого количества организационной работы и непосредственно кодинга, в процесс которого входит создание хотя бы web-интерфейса:-). Конечно, пару-другую строчек можно занести и в консоли. Ну, а ежели у вас имеется какой-то текстовый файл с записями, данные из которых нужно перевести в БД MySQL? Тут воевать только буфером обмена с бесконечным повторением действий "копирование-вставка" может только ну очень большой… трудоголик. Ну, а мы как умные люди можем поступить проще (правда, все в этом мире относительно). Дело в том, что можно в качестве аргумента в команде

mysql> \. File_name

передать имя файла "File_name", и этот файл будет выполнен. Что он собой представляет? Это просто последовательный список SQL-команд. Теперь нам остается только сгенерировать такой файл, что в каждом отдельно взятом случае делается по-разному — тут нужно смотреть по обстановке. Конечно, можно пойти еще дальше и написать программу для автоматического занесения данных в БД. Но для простых задач можно просто даже текстовый файл с написанными в нем командами скопировать в буфер и вставить в приглашение MySQL. "Вводы" будут восприниматься как знак завершения команды. Только не забывайте ставить в конце каждой строчки ";", ведь в mysql.exe команды можно вводить и в многострочном режиме, поэтому могут случаться накладки. Правда, есть и альтернативный способ. Если у вас есть текстовый файл с записями в виде таблицы, и они в нем разделены при помощи запятых и знаков окончания строки либо каких-то иных символов (тогда следует внести изменения в параметры следующей команды, заменив запятую и "\n" на соответствующие знаки), можно использовать команду
mysql> LOAD DATA INFILE "data.txt" INTO TABLE my_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Результатом данной команды должна явиться таблица. В файле поля должны быть разделены запятыми, а каждая запись — начинаться с новой строки. Следует помнить еще и о такой вещи, как кодировка. Если все данные вносить через консоль, то они будут храниться в DOS-кодировке, и при выводе таких данных — например, если в html-странице используют PHP — вы увидите "абракадабру". Чтобы этого не происходило, заносите данные только в одной кодировке или перекодируйте их с помощью специальных функций (для PHP это функция $text_after=convert_cyr_string ($text,"w","d"), где два последних параметра указывают соответственно на то, что исходная кодировка — Windows, а конечная после перекодировки — DOS). И напоследок. Гораздо приятней работать с консолью, так всеми нелюбимой, ежели сделать что-то вроде этого:

C:\>title LINUX