Работа с сервером MySQL при помощи терминального клиента mysql.exe

Введение

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

Увидеть список команд программы mysql можно, запустив ее с параметром —help:

C:\> mysql —help

Подробные сведения и обучающий курс по работе с утилитой MySQL можно найти в документации по MySQL

Целью данной работы является изучение основных приемов работы и выполнение действий по созданию и работе с БД на сервере MySQL в терминальном режиме.

Подсоединение к серверу и отсоединение от него

При подключении к серверу с помощью mysql обычно нужно ввести имя пользователя MySQL и, в большинстве случаев, пароль. Если сервер запущен не на том компьютере, с которого вы вошли в систему, необходимо также указать имя хоста. Параметры соединения (а именно — соответствующее имя хоста, пользователя и пароль) вы сможете узнать у администратора сервера базы данных. В случае, если MySQL запускается на локальной машине то host — localhost, пользователь c правами администратора root (по умолчанию пароль не задан). Получив соответствующие параметры, подсоединиться к серверу можно, выполнив в командной строке команду:

mysql -h localhost -u root -p

После установки соединения можно в любой момент отключиться от сервера, набрав в командной строке mysql> команду QUIT или EXIT.

Создание пользователей MySQL и назначение прав доступа

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

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

grant all privileges on <имя_базы_данных>.* to <имя_пользователя>@»%» identified by ‘<пароль>’;

После выполнения этой команды у пользователя появляется возможность создать базу с именем db1.

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

SET PASSWORD FOR <имя_пользователя> = PASSWORD(‘<пароль>’);

В этой команде, в отличие от предыдущей, должна использоваться функция PASSWORD (), которая производит шифрацию пароля.

Выполнение запросов

Выполнение запросов (команд) SQL является основным назначением программы mysql.exe. Примером запроса является получение имени версии MySQL-сервера:

select version();

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

  • Команда обычно состоит из SQL-выражения, за которым следует точка с запятой. (Из этого правила есть и исключения — команды без точки с запятой. Одним из них является упомянутая выше команда QUIT).
  • Когда пользователь вводит команду, mysql отправляет ее серверу для выполнения и выводит на экран сначала результаты, а затем — новую строку mysql>, что означает готовность к выполнению новых команд.
  • mysql выводит результаты работы запроса в виде таблицы (строк и столбцов). В первой строке этой таблицы содержатся заголовки столбцов, а в следующих строках — собственно результаты. Обычно заголовками столбцов становятся имена, полученные из таблиц базы. Если же извлекается не столбец таблицы, а значение выражения (как это происходит в приведенном выше примере), mysql дает столбцу имя запрашиваемого выражения.
  • mysql сообщает количество возвращаемых строк и время выполнения запроса, что позволяет в некоторой степени составить представление о производительности сервера.
  • Для ввода ключевых слов можно использовать любой регистр символов.

Mysql можно использовать для вычисления значений выражений (в качестве калькулятора). Пример:

SELECT SIN(3*PI()/4), (4+1)*5;

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

Вот пример несложного выражения, занимающего несколько строк:

Обратите внимание на то, как изменилась метка командной строки (с mysql> на ->) после ввода первой строки этого запроса. Таким образом программа mysql показывает, что завершенного выражения она пока что не получила и ожидает его полного ввода.

В этой таблице приведены все возможные варианта вида метки командной строки и соответствующие им состояния mysql:

Метка

Значение

mysql>

Ожидание новой команды

    ->

Ожидание следующей строки многострочной команды

    ‘>

Ожидание следующей строки, сбор строкового выражения, начинающегося с одиночной кавычки (‘)

    «>

Ожидание следующей строки, сбор строкового выражения, начинающегося с двойной кавычки («)

Если вы решите отменить исполнение набираемой команды, наберите \c:

Создание и выбор базы данных

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

show databases;

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

USE <database_name>

В команде USE, как и QUIT, точка с запятой не нужна (хотя ее можно завершать точкой с запятой). Кроме того, данная команда должна задаваться одной строкой.

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

CREATE DATABASE <database_name>;

Однако для того, чтобы пользователь мог создать базу, администратор (преподаватель) должен заранее дать ему соответствующие привилегии. Таким образом, возможна ситуация когда самой базы еще нет, но привилегии на базу с определенным именем для определенного пользователя уже есть (см. п.1.3).

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

mysql -h host -u user –p database_name

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

Типы данных столбцов

Тип

Описание

Диапазон значений

Примечание

Числовые типы

INTEGER[(M)] [UNSIGNED]

Целое число

от -2147483648 до 2147483647

(без знака от 0 до 4294967295)

Синоним — тип INT;

М – количество выводимых знаков

BIGINT(M)

Большое целое число

от -9223372036854775808 до 9223372036854775807 (без знака от 0 до 18446744073709551615)

М – количество выводимых знаков

FLOAT[(M,D)] [UNSIGNED]

число с плавающей точкой обычной точности

от -3,402823466E+38 до -1,175494351E-38, 0, и от 1,175494351E-38 до 3,402823466E+38

М – количество выводимых знаков; D — количество разрядов, следующих за десятичной точкой

DOUBLE[(M,D)] [UNSIGNED]

Число с плавающей точкой удвоенной точности

от -1,7976931348623157E+308 до -2,2250738585072014E-308, 0, и от 2,2250738585072014E-308 до 1,7976931348623157E+308

М – количество выводимых знаков; D — количество разрядов, следующих за десятичной точкой

Типы для хранения даты и времени

DATE

Дата

от ‘1000-01-01’ до ‘9999-12-31’

MySQL извлекает и выводит величины DATE в формате ‘YYYY-MM-DD’

TIME

Время

от ‘-838:59:59’ до ‘838:59:59’

MySQL выводит значения TIME в формате ‘HH:MM:SS’

DATETIME

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

от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’

MySQL извлекает и выводит величины DATETIME в формате ‘YYYY-MM-DD HH:MM:SS’

TIMESTAMP[(M)]

Временная метка

от ‘1970-01-01 00:00:00’ до некоторого значения времени в 2037 году

обеспечивает тип представления данных, который можно использовать для автоматической записи текущих даты и времени при выполнении операций INSERT или UPDATE

MySQL выводит значения TIMESTAMP в форматах YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD или YYMMDD в зависимости от значений M: 14 (или отсутствующее), 12, 8, или 6

Символьные типы данных

CHAR(M) [BINARY]

Строка фиксированной длины

от 0 до 255 символов

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

Если не задан атрибут чувствительности к регистру BINARY, то величины CHAR сортируются и сравниваются как независимые от регистра в соответствии с алфавитом

VARCHAR(M) [BINARY]

Строка переменной длины

от 0 до 255

Если не задан атрибут чувствительности к регистру BINARY, то величины VARCHAR сортируются и сравниваются как независимые от регистр

BLOB, TEXT

двоичный объект большого размера

от 0 до 65535

Различие между типами BLOB и TEXT состоит в том, что сортировка и сравнение данных выполняются с учетом регистра для величин BLOB и без учета регистра для величин TEXT

MEDIUMBLOB, MEDIUMTEXT

двоичный объект большого размера

от 0 до 16777215

LONGBLOB, LONGTEXT

двоичный объект большого размера

от 0 до 4294967295

Множественные типы данных

ENUM

перечисление

Перечисление может иметь максимум 65535 элементов

Объект ENUM может иметь только одно значение, выбранное из заданного списка величин

SET

Набор

Список SET может содержать максимум 64 элемента

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

Просмотр существующих таблиц

Для просмотра имеющихся в БД таблиц (после выбора БД) используется команда:

SHOW TABLES;

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

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

CREATE TABLE имя_таблицы

(имя_столбца тип_данных [NOT NULL][UNIQUE]

[DEFAULT значение_по_умолчанию]

[AUTO_INCREMENT]

[PRIMARY KEY]

[CHECK (условие)] [,…]

[FOREIGN KEY (имя_столбца) REFERENCES имя_таблицы(имя_столбца)]);

Параметры команды:

имя_таблицы

— имя создаваемой таблицы

имя_столбца

— имя атрибута (поля)

тип_данных

— тип данных атрибута (поля)

NOT NULL

— спецификатор, указываемый для тех атрибутов (полей), которые не должны содержать пустых (NULL) значений

UNIQUE

— спецификатор, указываемый для тех атрибутов (полей), которые не должны содержать повторяющихся значений (требование уникальности)

DEFAULT значение_по_умолчанию

— значение по умолчанию, присваиваемое данному полю, используется после спецификатора DEFAULT

AUTO_INCREMENT

— спецификатор, указываемый для тех целочисленных атрибутов (полей), значения которых автоматически увеличиваются (инкрементируются) при добавлении новых строк. Обычно используется для первичных ключей

PRIMARY KEY

— спецификатор, указываемый для атрибутов (полей), являющихся первичными ключами

CHECK условие

— выражение ограничения целостности, накладываемые на значения атрибута (поля), используется после спецификатора CHECK

FOREIGN KEY REFERENCES имя_таблицы(имя_столбца)

— спецификатор, указываемый для атрибутов (полей), являющихся внешними ключами и связанными (первичным ключом) другой таблицы

Пример создания таблицы:

CREATE TABLE Parts

(Part_ID INTEGER UNSIGNED NOT NULL,

Part_name VARCHAR(80),

Material VARCHAR(80),

Weight INTEGER CHECK (WEIGHT>0));

В этом примере на поле WEIGHT накладывается ограничение целостности, которое не позволяет для данного поля задавать отрицательные значения — CHECK (WEIGHT >=0).

Просмотр структуры таблицы

Для просмотра структуры таблицы можно использовать команду:

DESCRIBE <имя_таблицы>;

Модификация структуры таблицы

Модификация структуры таблицы – достаточно частая операция, связанная с изменениями, вносимыми в структуры БД в процессе ее жизненного цикла. Синтаксис оператора ALTER TABEL модификации структуры таблиц (неполный вариант использования оператора, достаточный для выполнения лабораторных работ):

ALTER TABLE имя_таблицы

ADD COLUMN определение_столбца

или ADD PRIMARY KEY имя_столбца

или ADD FOREIGN KEY имя_столбца

или CHANGE имя_столбца определение_столбца

или DROP COLUMN имя_столбца

или DROP PRIMARY KEY имя_столбца;

В данном синтаксисе под элементом «определение_столбца» понимается описание столбца, начиная с его имени и типа данных, как в операторе CREATE TABLE.

Переименование таблицы

Для переименования таблицы используется оператор RENAME TABLE:

RENAME TABLE имя_таблицы новое_имя_таблицы;

Добавление данных в таблицу

Для добавления данных в таблицу предназначены операторы LOAD DATA INFILE и INSERT.

Для добавления данных в таблицу при помощи оператора LOAD DATA необходимо создать текстовый фай с данными, в котором каждая текстовая строка соответствует одной записи в таблице. Значения столбцов должны быть разделены символами табуляции и даны в том порядке, который был определен командой CREATE TABLE. После этого загрузить содержимое файла в таблицу можно одной командой.

Создадим текстовый файл parts.dat:

Выполним команду для загрузки данных в таблицу:

load data local infile «parts.dat» into table parts;

Если задано ключевое слово LOCAL, то файл читается с клиентского хоста. Если же LOCAL не указывается, то файл должен находиться на сервере.

При добавлении одиночных записей используется команда INSERT. В самом простом варианте ее применения необходимо задать значения каждого столбца, в том порядке, в каком они были перечислены в команде CREATE TABLE:

INSERT INTO parts VALUES (4,‘Wheel’,’Rubber’,12000);

Если порядок столбцов в таблице неизвестен, для его получения можно использовать DESCRIBE tbl_name.

Другой формой команды INSERT является синтаксис с явным указанием имен столбцов:

INSERT INTO parts (part_ID, part_name, material, weight) VALUES (5,‘Engine cap’,’Plastic’,1300);

Также возможен следующий синтаксис:

INSERT INTO имя_таблицы SET имя_столбца=(выражение | DEFAULT), …

Столбцам, значения которых не были указаны в команде INSERT присваиваются значения NULL:

INSERT INTO parts SET part_ID=6, part_name=’Screw’;

Обновление (модификация) данных в таблице

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

UPDATE имя_таблицы SET имя_столбца=выражение [, имя_столбца=выражение …] [WHERE условие];

Следующий оператор обновляет значение в одной строке таблицы:

UPDATE parts SET weight=105 WHERE part_name=’Screw’;

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

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

DELETE FROM имя_таблицы [WHERE условие];

Следующие операторы удаляют сначала одну а затем все остальные строки таблицы parts:

delete from parts where part_id=4;

delete from parts;

Удаление таблицы

Оператор DROP TABLE удаляет одну или несколько таблиц. Все табличные данные и определения удаляются, так что будьте внимательны при работе с этой командой!

Следующий оператор удаляет таблицу parts:

DROP TABLE имя_таблицы [, имя_таблицы…];

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

DROP TABLE IF EXISTS имя_таблицы [, имя_таблицы…];

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

Определение ключей в БД

Определение первичных ключей

Первичным ключом называю атрибут или набор атрибутов отношения (таблицы), однозначно идентифицирующий кортеж отношения (строку).

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

Определение первичного ключа при создании таблицы parts:

CREATE TABLE Parts

(Part_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

Part_name VARCHAR(80),

Material VARCHAR(80),

Weight INTEGER CHECK (WEIGHT>0));

Определение первичного ключа после создания таблицы parts:

ALTER TABLE Parts ADD PRIMARY KEY (Part_ID);

После определения первичного ключа для него необходимо задать атрибуты NOT NULL и AUTO_INCREMENT при помощи команды ALTER TABLE:

ALTER TABLE Parts CHANGE Part_ID Part_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT;

Описание созданной таблицы, полученное при помощи DESCRIBE:

Определение внешних ключей

Определение внешнего ключа при создании таблицы Delivery:

CREATE TABLE Delivery

(Part_ID INTEGER UNSIGNED,

Quantity INTEGER CHECK (Quantity >0),

FOREIGN KEY (Part_ID) REFERENCES Parts (Part_ID));

Определение внешнего ключа при модификации таблицы Delivery:

ALTER TABLE DELIVERY ADD FOREIGN KEY (Part_ID) REFERENCES Parts(Part_ID);

Описание созданной таблицы, полученное при помощи DESCRIBE:

Задание на самостоятельное изучение

  1. Получить у преподавателя имя хоста, на котором работает сервер MySQL, имя вашей БД, а также логин и пароль для доступа к серверу MySQL.
  2. Запустить командную строку cmd.exe и перейти в каталог, где находится файл mysql.exe.
  3. Подключиться к серверу MySQL, запустив программу mysql.exe со следующими параметрами (host – имя хоста сервера, user – Ваш логин):

mysql -h host -u user –p

  1. Установить пароль для доступа к серверу MySQL:

Set password for <имя_пользователя> = password(‘<пароль>’);

  1. Выполните запрос для получения версии MySQL-сервера, текущей даты и времени, имени текущего пользователя:

select version(), now(), current_date, current_time, user();

  1. Создать базу данных с указанным преподавателем именем:

create database <имя_базы>;

  1. Выполнить запрос для просмотра существующих БД:

show databases;

  1. Выполнить команду для подключения к вашей базе данных:

USE <имя_базы>;

  1. Закрыть соединение с сервером и выйти из программы mysql.exe

exit;

  1. Написать командный файл (*.bat) для определения параметров соединения и запуска mysql.exe. Запустить mysql.exe при помощи созданного командного файла.
  2. Написать сценарий (набор SQL-команд) для создания базы данных «Предприятие». Концептуальное описание БД «Предприятие»:
  3. База данных «Предприятие» содержит информацию о работе некоторого промышленного предприятия, которое осуществляет сборку изделий (проектов) из деталей. В базе данных присутствуют следующие сущности: Детали, Проекты, Поставщики, Поставки, Города. Таблица «Детали» содержит описания деталей. Поля: Номер_детали[1], Наименование, Материал, Вес. Таблица «Поставщики» содержит описания организаций, поставляющих детали. Поля: Номер_поставщика, Наименование, Номер_города. Таблица «Проекты» содержит описание проектов (сборочных изделий). Поля: Номер_проекта, Наименование, Номер_города. Таблица «Города» содержит описание городов, в которых могут находиться проекты и поставщики. Поля: Номер_города, Наименование. Каждый проект содержит некоторое количество различных деталей, поставляемых различными поставщиками в определенном количестве. Проект рассматривается как совокупность поставок. Таблица «Поставки» содержит записи о типе, количестве деталей и цене одной детали, поставляемых определенным поставщиком для определенного проекта. Поля: Номер_проекта, Номер_поставщика, Номер_детали, Количество, Цена, Дата_начала, Дата_конца.
  4. В сценарии необходимо определить первичные и внешние ключи в таблицах. Для генерации значений первичных ключей использовать спецификатор AUTO_INCREMENT. Задать для первичных ключей требование обязательности данных NOT NULL. Задать для полей ВЕС, КОЛИЧЕСТВО и ЦЕНА беззнаковый целочисленный тип.
  5. Добавить в начало сценария команды очистки базы данных. Внести в сценарий операторы заполнения таблиц тестовыми данными (не менее 5-и строк в каждой таблице). Запустить сценарий в пакетном режиме. При необходимости произвести отладку (устранение ошибок).
  6. Проверить работу ограничений целостности CHECK и ограничений целостности внешних ключей путем добавления в таблицы некорректных данных.

Поделитесь своим мнением или задайте вопрос