Запустить сервер 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, выполните следующие действия.
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 был сконфигурирован как сервис 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 – интерфейс для создания, редактирования и выполнения инструкций 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 и переходим к подведению итогов.
В этой главе были рассмотрены СУБД MySQL и графические утилиты MySQL Administrator и MySQL Query Browser. Вы освоили достаточно сложную процедуру установки и настройки сервера MySQL, научились управлять сервером и подключаться к нему. Вы также узнали, как устроена реляционная база данных и как спроектировать собственную БД.
Итак, следующим этапом является построение базы данных в MySQL. Этому посвящена вторая глава. В ней будет рассказано, как создавать таблицы, вносить в них информацию и находить нужные сведения в базе данных.
Из этой главы вы узнаете, как работать с данными в СУБД 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-команд.
Прежде чем выполнять 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-команды, приступим к управлению данными с помощью этих команд. В первую очередь мы рассмотрим команды, предназначенные для работы с базой данных в целом.
В этом разделе вы узнаете, как создать и удалить базу данных, изменить для нее кодировку по умолчанию, выбрать текущую БД, а также просмотреть список всех баз на данном сервере 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.1.
CREATE TABLE <Имя таблицы>
(<Имя столбца 1> <Тип столбца 1> [<Свойства столбца 1>],
<Имя столбца 2> <Тип столбца 2> [<Свойства столбца 2>],
[<Информация о ключевых столбцах и индексах>])
[<Опциональные свойства таблицы>];
Как вы видите, команда создания таблицы может включать множество параметров, однако многие из них задавать необязательно (в листинге 2.1 такие параметры заключены в квадратные скобки). В действительности для создания таблицы достаточно указать ее имя, а также имена и типы всех столбцов; остальные параметры используются в случае необходимости.
Рассмотрим вначале несколько примеров, которые помогут вам освоить команду CREATE TABLE и сразу же, не изучая ее многочисленных параметров, начать создавать собственные (простые по структуре) таблицы.
Предположим, что мы строим базу данных, которую спроектировали в главе 1. Используя команды из предыдущего раздела, мы создали пустую базу данных SalesDept (Отдел продаж) и выбрали ее в качестве текущей. Теперь создадим три таблицы: Customers (Клиенты), Products (Товары) и Orders (Заказы). В листинге 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.
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.
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 предусмотрено множество типов данных для хранения чисел, даты/времени и символьных строк (текстов). Кроме того, существуют типы данных для хранения пространственных (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 в скобках указывается размер числа, то есть максимальное количество хранимых битов.
Развитие информационных технологий - одно из передовых направлений деятельности человечества. Поэтому в данную область инвестируется огромное количество средств. С момента начала и по сегодняшний день было решено много различных задач, которые серьезно облегчили повседневную жизнь многих людей. Однако с развитием технологий возник ряд проблем, одна из которых - чрезмерный объем информации, подлежащей хранению. Базы данных были призваны разрешить возникшую ситуацию.
Так как проблема хранения большого объема информации актуальна на сегодняшний день, существует несколько способов решения. Каждый из них ориентирован на задачи определенной направленности. Однако в совокупности они образуют полноценный комплекс, который позволяет упростить проблему хранения данных.
Существует определенная классификация, которая определяет необходимость использования той или иной базы данных и СУБД (системы управления базами данных). Наиболее распространенными в настоящее время принято считать клиент-серверные технологии хранения данных. К ним относятся следующие виды: Firebird, Interbase, IBM DB2, Sybase, Oracle, PostgreSQL, ЛИНТЕР, MySQL. Нас будет интересовать последний вариант - MySQL, команды которой полностью соответствуют стандартам SQL. Данная технология является одной из популярных и часто применяемых для решения как локальных прикладных, так и производственных задач малого масштаба.
Предшественниками клиент-серверной технологии являются файл-серверные СУБД, которые утратили свои позиции из-за ряда имеющихся недостатков, среди которых выделяют необходимость распространения ядра СУБД на каждом компьютере и значительную нагрузку на локальную сеть, приводящую к увеличению временных затрат. К данной группе относятся Borland Paradox.
Однако несколько лет назад появилась передовая технология, которая приобретает популярность и пользуется спросом при решении небольших локальных задач. Речь идет о встраиваемых системах управления базами данных. Главная особенность рассматриваемых СУБД - отсутствие серверной части. Сама система представляет собой библиотеку, позволяющую унифицированным образом работать с большими объемами информации, размещенными на локальном компьютере. Данный способ исключает недостатки файл-серверных средств, а также значительно превосходит по скорости клиент-серверные технологии.
В качестве примеров можно рассматривать OpenEdge, SQLite, BerkeleyDB, один из вариантов Firebird, Sav Zigzag, Compact, ЛИНТЕР, а также один из вариантов 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 * from table _ name . Отправив такой запрос серверу базы данных, клиент должен получить все записи, хранящиеся в таблице.
Многие никогда не задумываются, но при просмотре интернет-ресурсов постоянно используются MySQL команды.PHP-запросы используют синтаксис языка SQL, чтобы отображать содержимое каталогов интернет-магазинов или новости в социальных сетях. Вместо знака “*” после предложения select , как правило, приводится список основных полей таблицы или нескольких таблиц, данные из которых необходимо обработать. В случае выборки из нескольких таблиц используется специальная связка join , которая служит для их соединения по имеющимся внешним связям. Однако это условие можно изменять, самостоятельно указывая, по каким полям должна проводиться связь.
Иногда происходит ситуация, когда ответ, полученный от сервера базы данных, содержит повторяющиеся данные. Для того чтобы пользователю были видны только уникальные записи, используется предложение distinct. Оно помещается перед списком запрашиваемых полей и служит вспомогательным средством для сокрытия дубликатов.
Ниже предоставлен список наиболее полезных и часто используемых команд 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 под 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");
Ниже предоставлен список наиболее полезных и часто используемых команд 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 под 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");
Мне часто друзья задают те или иные вопросы по администрированию и я подумал, почему бы вместо того, чтобы объяснять по 100 раз одно и то же, не писать такие статьи-заметки на сайте? К тому же, это прекрасная возможность в случае чего самому воспользоваться такой заметкой, ведь Вы сами знаете, удержать в голове порой всё не возможно и так или иначе иногда приходится обращаться к настольной книге, так пусть такой книгой станет один из разделов этого сайта. Сегодня мы поговорим о том, как работать с СУБД MySQL из командной строки, да, кто-то скажет — «Накой? Ведь есть phpMyAdmin», отвечаю — зачем тратить драгоценные ресурсы сервера, устанавливая всякую дрянь по типу WebMin, phpMyAdmin и т.п., если всё тоже самое легко можно проделать надёжными, проверенными средствами. Настройка из терминала по SSH-туннелю — наш выбор!
Что ж, приступим. Соединяемся с сервером по SSH, к примеру через всеми известный PuTTY (A Free Telnet/SSH Client). Теперь перейдём непосредственно к описанию команд.
Вход на сервер MySQL программой mysql:
mysql -u root -h localhost -p
Параметр -u указывает пользователя, который будет входить в систему. Параметр -h указывает узел сети. Обычно это localhost, если вы настраиваете не удаленный сервер. Наконец, -p сообщает программе-клиенту mysql, что для доступа к базе данных будет вводиться пароль. Обратите внимание на приглашение mysql>. Именно здесь вы будете вводить все свои команды. Теперь, находясь в среде mysql в качестве корневого пользователя, мы можем начать настройку базы данных.
Мы вошли, и на экране - приглашение mysql. Сначала взглянем на список уже имеющихся баз данных. Для этого введем команду SHOW DATABASES.
Ответ дает понять, что команда выполнена без каких-либо ошибок. В данном случае, изменилась одна строка. Это относится к главной базе данных mysql, в которой содержится список всех баз данных. Но вам не нужно слишком беспокоиться о второстепенных подробностях. Последнее число означает время выполнения запроса. Убедиться, что база данных создана, мы можем, снова запустив команду SHOW DATABASES.
Чтобы заняться созданием таблиц в новой базе данных test, нам потребуется установить ее как текущую. Для этого используем команду USE. Параметром этой команды указывается название базы данных, которую нужно сделать текущей. Еще текущую базу можно устанавливать в командной строке, указывая ее название после параметра -D. Давайте продолжим и переключимся на базу данных test.
Переключение базы данных:
USE test; |
Привилегии в MySQL
Предоставление привилегий командой GRANT
.
Привилегиями определяются возможности доступа пользователей к базам данных, таблицам… почти ко всему. Сейчас только суперпользователь root из MySQL может обращаться к базе данных test, согласно данным разрешениям. Давайте создадим ещё одного пользователя, например, admin (администратор), который будет обращаться к базе данных test и работать с информацией из нее. Но прежде, чем начать, давайте взглянем на несколько упрощенный формат команды GRANT.
Примечание
: Команда GRANT считается способом создания пользователя. Поздние версии MySQL, однако, также содержат функцию CREATE_USER, хотя GRANT до сих пор предпочтительнее.
Теперь нужны привилегии, которые можно присваивать. Используя все вышесказанное, можно устанавливать следующие привилегии:
Примечание : Если вы используете MySQL для обмена данными с веб-приложением, то разъясняемые здесь привилегии CREATE, SELECT, INSERT, а также привилегии DELETE и UPDATE (описанные в руководстве по MySQL, раздел GRANT and REVOKE Syntax (англ.)) - единственные, которые, вероятно, потребуются. Многие совершают ошибку, раздавая все привилегии, когда это, в действительности, не нужно. Сверьтесь с разработчиками приложений, действительно ли такие разрешения создадут проблемы в работе.
Создание пользователя admin:
Итак, пользователи созданы; теперь протестируем их. Сначала выйдем из mysql, написав quit в строке приглашения:
Параметры объясняются в разделе команды GRANT. А сейчас мы запретим пользователю любой вид доступа. Скажем, мы выяснили, что учетная запись admin вызывает проблемы с безопасностью. Мы решаем отозвать все права. Заходим как root делаем необходимое.
Отзыв разрешений для пользователя admin:
REVOKE ALL ON test.* FROM "admin" @"localhost" ; |
Примечание : В данном случае, доступ пользователя прост, поэтому отмена прав на одну базу данных - не проблема. Но обычно вам, скорее всего, потребуется использовать *.* вместо test.*, чтобы заодно отменить доступ пользователя ко всем остальным базам данных.
Теперь взглянем, как полностью удалить учетную запись командой DELETE, и посмотрим на таблицу пользователей MySQL.
Удаление учетных записей командой DELETE
.
Таблица пользователей MySQL - это список пользователей и информации о них. Убедитесь, что вы зашли как root. Используйте основную базу данных MySQL.
Использование основной базы mysql:
Таблица user - та, что нужна. В ней 30 различных полей, и ее сложно читать. Для облегчения чтения используем третью форму команды SELECT. Искомые поля - Host (узел) и User (пользователь).
Нахождение пользователя admin в таблице user:
Теперь, получив информацию, мы можем избавиться от пользователя admin. Это делается командой DELETE; вот ее синтаксис:
Вы могли заметить, что формат DELETE чем-то схож с форматом SELECT. Укажем поле User и значение admin. Это удалит запись из таблицы user, где пользователь - admin, удаляя нашу учетную запись. Сделаем так:
Удаление пользователя admin:
Команда FLUSH PRIVILEGES нужна, чтобы обновить разрешения.
На сегодня всё. Согласитесь, всё просто! ;)
