Лабораторная работа №3. Настройка базы данных MySQL

 

База данных MySQL

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

 

MySQL — свободная реляционная система управления базами данных.

 

MySQL является решением для малых и средних приложений. Входит в состав серверов WAMP, AppServ, LAMP и в портативные сборки серверов Денвер, XAMPP, VertrigoServ. Обычно MySQL используется в качестве сервера, к которому обращаются локальные или удалённые клиенты, однако в дистрибутив входит библиотека внутреннего сервера, позволяющая включать MySQL в автономные программы.

 

Гибкость СУБД MySQL обеспечивается поддержкой большого количества типов таблиц: пользователи могут выбрать как таблицы типа MyISAM, поддерживающие полнотекстовый поиск, так и таблицы InnoDB, поддерживающие транзакции на уровне отдельных записей. Более того, СУБД MySQL поставляется со специальным типом таблиц EXAMPLE, демонстрирующим принципы создания новых типов таблиц. Благодаря открытой архитектуре и GPL-лицензированию, в СУБД MySQL постоянно появляются новые типы таблиц.

 

Платформы

MySQL портирована на большое количество платформ: AIX, BSDi, FreeBSD, HP-UX, Linux, Mac OS X, NetBSD, OpenBSD, OS/2 Warp, SGI IRIX, Solaris, SunOS, SCO OpenServer, UnixWare, Tru64, Windows 95, Windows 98, Windows NT, Windows 2000, Windows XP, Windows Server 2003, WinCE, Windows Vista, Windows 7 и Windows 10. Существует также порт MySQL к OpenVMS. Важно отметить, что на официальном сайте СУБД для свободной загрузки предоставляются не только исходные коды, но и откомпилированные и оптимизированные под конкретные операционные системы готовые исполняемые модули СУБД MySQL.

 

Официальный сайт: http://www.mysql.com/

 

Основные объекты структуры базы данных SQL-сервера

 

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

 

Логически данные в SQL Server организованы в виде объектов. К основным объектам базы данных SQL Server относятся:

 

· Tables -Таблицы базы данных, в которых хранятся собственно данные

· Views -Просмотры (виртуальные таблицы) для отображения данных из таблиц

· Stored Procedures - Хранимые процедуры

· Triggers Триггеры – специальные хранимые процедуры, вызываемые при изменении данных в таблице

· User Defined function - Создаваемые пользователем функции

· Indexes Индексы – дополнительные структуры, призванные повысить производительность работы с данными

· User Defined Data Types -Определяемые пользователем типы данных

· Keys Ключи – один из видов ограничений целостности данных

· Constraints Ограничение целостности – объекты для обеспечения логической целостности данных

· Users - Пользователи, обладающие доступом к базе данных

· Roles - Роли, позволяющие объединять пользователей в группы

· Rules -Правила базы данных, позволяющие контролировать логическую целостность данных

· Defaults - Умолчания или стандартные установки базы данных.

Проектирование БД

 

Построение базы данных (как и любой информационной системы, любого программ-

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

 

Проектирование включает в себя следующие основные этапы:

· Определение требований к базе данных.

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

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

· Проектирование «снизу вверх», от элемента к структуре: вначале определяется, какие именно атрибуты должны храниться в базе данных, затем группы атрибутов объединяются в объекты. Этот метод годится для небольших баз данных, в которых количество атрибутов невелико.

· Проектирование «сверху вниз» начинается с выделения высокоуровневых объектов и связей между ними, затем осуществляется декомпозиция объектов и последовательная детализация модели до уровня атрибутов. Для сложных баз данных с большим количеством атрибутов такой метод более эффективен, чем метод «снизу вверх».

 

В результате мы получим предварительную структуру базы данных: список объектов

– таблиц и список атрибутов каждого объекта – столбцов таблицы. Например, на снове

требований, приведенных в п. 1, можно построить модель данных, содержащую ведения о таких объектах, как клиенты, заказы и товары.

· Для клиентов: идентификатор, имя (или название организации), номер контактного телефона, адрес, а также рейтинг, используемый для расчета скидки.

· Для товаров: идентификатор, наименование, описание, название склада, где хранится этот вид товара, и адрес склада.

· Для заказов: дату заказа, идентификатор заказанного товара, количество товаров этого наименования, общую стоимость заказа с учетом скидки, идентификатор клиента, сделавшего заказ, и адрес клиента, куда нужно доставить заказ (здесь мы предполагаем, что каждый заказ может включать только одно наименование товара).

· Нормализация.

 

Нормализация базы данных заключается в минимизации избыточности данных. Нормализация позволяет уменьшить объем БД и устранить потенциальную противоречивость данных (например, если в базе данных одна и та же информация дублируется в нескольких местах, то при ее обновлении есть риск появления разночтений).

 

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

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

 

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

 

Создание БД

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

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;

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

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

 

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

вы можете с помощью команды

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 – пустая база данных, которую можно использовать для «пробы пера» или просто удалить.

Изменения структуры таблицы

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

 

Добавить столбец вы можете с помощью команды

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

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

[FIRST или AFTER <Имя предшествующего столбца>];

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

имя и тип добавляемого столбца (о типах столбцов см. пункт «Типы данных в MySQL»).

При необходимости можно также задать свойства добавляемого столбца (см. пункт «Свойства столбцов»). Кроме того, можно определить место нового столбца среди уже существующих: добавляемый столбец может стать первым (FIRST) или следовать после указанного предшествующего столбца (AFTER). Если место столбца не задано, он становится последним столбцом таблицы.

 

Добавить первичный ключ вы можете с помощью команды

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

ADD [CONSTRAINT <Имя ключа>]

PRIMARY KEY (<Список столбцов>);

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

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

 

При добавлении в таблицу внешнего ключа мы указываем имя таблицы, в которую

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

 

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

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

ADD INDEX [<Имя индекса>] (<Список столбцов>);

 

Добавить уникальный индекс – с помощью команды

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

ADD [CONSTRAINT <Имя ограничения>]

UNIQUE (<Список столбцов>);

 

Добавить полнотекстовый индекс – с помощью команды ALTER TABLE <Имя

таблицы>

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

ADD FULLTEXT [<Имя индекса>] (<Список столбцов>);

 

При добавлении в таблицу индекса мы указываем имя таблицы, в которую нужно доба-

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

 

Изменить столбец таблицы вы можете с помощью следующих команд:

– Чтобы полностью изменить описание столбца, выполните команду:

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

CHANGE <Прежнее имя столбца >

<Новое имя столбца>

<Новый тип столбца> [<Свойства столбца>]

[FIRST или AFTER <Имя предшествующего столбца>];

 

В этой команде мы указываем имя таблицы, текущее имя столбца, новое имя столбца

(которое может совпадать с текущим), новый тип столбца (который также может совпадать с текущим типом), а также, при необходимости, свойства столбца (старые свойства при выполнении команды CHANGE удаляются). Кроме того, можно определить место столбца среди остальных столбцов таблицы: изменяемый столбец может стать первым (FIRST) или следовать после указанного предшествующего столбца (AFTER).

 

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

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

MODIFY <Имя столбца>

<Новый тип столбца> [<Свойства столбца>]

[FIRST или AFTER <Имя предшествующего столбца>];

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

переименования столбца.

 

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

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

ALTER <Имя столбца>

SET DEFAULT <Значение по умолчанию>;

 

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

 

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

ALTER TABLE <Имя таблицы> DROP PRIMARY KEY;

 

Удалить внешний ключ вы можете с помощью команды

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

DROP FOREIGN KEY <Имя внешнего ключа>;

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

 

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

команды

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

или

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

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

дования столбцов таблицы, проверить правильность изменений, внесенных в структуру таблицы с помощью команды ALTER TABLE и т. п.

 

Команда DESCRIBE выводит информацию о столбцах таблицы.

Загрузка данных из файла

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

 

Например, чтобы загрузить данные в таблицу Customers (Клиенты), выполните следующие действия.:

1. Запустите стандартную программу Windows Блокнот;

2. В окне программы Блокнот введите данные, используя для отделения значений друг

от друга клавишу Tab, а для перехода на следующую строку – клавишу Enter;

3. Для сохранения файла с данными нажмите комбинацию клавиш Ctrl+S. В стандарт-

ном окне Windows Сохранить как выберите папку, в которую нужно поместить файл (например, C: \data). Введите имя файла (например, Customers. txt) и нажмите кнопку Сохранить.

4. Для загрузки данных из созданного файла выполните команду

LOAD DATA LOCAL INFILE 'C:/data/Customers.txt'

INTO TABLE Customers

CHARACTER SET cp1251;

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

а не обратную.

 

Однако если вам нужно загрузить в таблицу данные из текстового файла, который был

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

LOAD DATA [LOCAL] INFILE 'Путь и имя файла'

[REPLACE или IGNORE]

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

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

[

FIELDS

[TERMINATED BY <Разделитель значений в строке>]

[[OPTIONALLY]

ENCLOSED BY <Символ, в который заключены значения>]

[ESCAPED BY <Экранирующий символ>]

]

[

LINES

[STARTING BY <Префикс строки>]

[TERMINATED BY <Разделитель строк>]

]

[IGNORE <Количество строк в начале файла> LINES]

[(<Список столбцов>)]

[SET <Имя столбца> = <Выражение>,...];

 

В этой команде вы можете использовать следующие параметры.

· LOCAL – укажите этот параметр, если файл с данными находится на клиентском компьютере (то есть на том компьютере, где работает клиентское приложение, в котором вы и вводите эту команду). Если файл расположен на компьютере, где работает сервер MySQL, параметр LOCAL указывать не нужно.

· 'Путь и имя файла' – введите полный путь к файлу, например C:/Data/ mytable.txt (необходимо использовать прямую косую черту вместо обратной, принятой в Windows).

· REPLACE или IGNORE – укажите один из этих параметров, чтобы сообщить программе MySQL, как нужно обрабатывать загружаемую строку, если в таблице уже есть строка с таким же значением первичного ключа или уникального индекса. Если указан параметр REPLACE, то существующая в таблице строка заменяется новой. Если указан параметр IGNORE, новая строка в таблицу не загружается.

· CHARACTER SET <Имя кодировки> – укажите кодировку данных в файле. Этот параметр используется для корректного преобразования кодировок. Предполагается, что все данные в файле имеют одну и ту же кодировку.

· FIELDS – укажите этот параметр, чтобы сообщить программе MySQL, в каком формате заданы значения в файле:

– TERMINATED BY <Разделитель значений в строке> – укажем символ, разделяющий значения в строке файла. Например, если значения разделены запятыми, укажем параметр TERMINATED BY, если значения разделены символами табуляции – TERMINATED BY \t', если значения разделены косой чертой – TERMINATED BY /;

– ENCLOSED BY <Символ, в который заключены значения> – укажем символ, которым обрамляются значения. Например, если все значения заключены в одинарные кавычки, укажем ENCLOSED BY \, если в одинарные кавычки заключены только символьные значения, укажем OPTIONALLY ENCLOSED BY \, а если никакие значения не обрамляются никакими символами, укажем ENCLOSED BY или вообще опустим этот параметр;

– ESCAPED BY <Экранирующий символ> – укажем экранирующий символ (его также называют escape-символом). Этот символ сообщает программе MySQL, что следующий за ним символ нужно интерпретировать особым образом. А именно, обычный символ, следующий после экранирующего, будет рассматриваться как специальный символ, а специальный символ, наоборот, – как обычный символ. Чаще всего экранирующим символом служит обратная косая черта, и в этом случае зададим значение ESCAPED BY \\. Тогда, например, записанное в файле значение «\N» будет прочитано и загружено в базу данных как NULL. Другой пример: если значения в файле разделены запятыми, то экранирующий символ помещается перед запятой, которая должна восприниматься как часть значения, а не как разделитель, то есть последовательность символов «\,» интерпретируется как символ запятой в значении.

 

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

 

· LINES – укажите этот параметр, чтобы сообщить программе MySQL, в каком формате заданы строки в файле:

– STARTING BY <Префикс строки> – укажем последовательность символов в начале каждой строки, которая должна игнорироваться программой вместе со всеми предшествующими символами. После префикса должны начинаться значения;

– TERMINATED BY <Разделитель строк> – укажем символ, которым заканчиваются строки. Например, если строки заканчиваются символом перевода строки, укажем параметр TERMINATED BY \n', если символом возврата каретки – укажем TERMINATED BY \r', если сочетанием этих символов – укажем TERMINATED BY \r\n', если нулевым байтом – укажем TERMINATED BY \0. Если параметр LINES не указан, программа MySQL считает, что строки в файле не имеют префикса и заканчиваются символом перевода строки «\n».

· IGNORE <Количество строк в начале файла> LINES – укажите этот параметр, если первые несколько строк в файле не содержат значений (иными словами, являются заголовком) и при загрузке их нужно пропустить.

· (<Список столбцов>) – перечислите столбцы таблицы, в которые будут загружаться данные. Это необходимо, если файл содержит данные не для всех столбцов таблицы или порядок следования значений в файле отличается от порядка столбцов в таблице.

· SET <Имя столбца> = <Выражение> – вы можете записывать в столбцы не только значения из файла, но и значения, вычисленные с помощью выражений. Для этого создадим одну или несколько переменных, присвоим им считанные из файла значения и запишем в столбец значение выражения, использующего эти переменные.

 

Вставка отдельных строк

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

INSERT [INTO] <Имя таблицы>

[(<Список столбцов>)]

VALUES

(<Список значений 1>),

(<Список значений 2>),

...

(<Список значений N>);

 

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

· Имя таблицы, в которую добавляются строки.

· Список имен столбцов, для которых будут заданы значения. Если значения будут заданы для всех столбцов таблицы, то приводить список столбцов необязательно.

Простые запросы

Для получения информации из таблиц базы данных используются запросы – SQL- команды, начинающиеся с ключевого слова SELECT.

 

Например, команда, которая выводит все данные, содержащиеся в таблице:

SELECT * FROM <Имя таблицы>;

 

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

ORDER BY <Имя столбца> [ASC или DESC]

Ключевое слово ASC означает, что сортировка выполняется по возрастанию, DESC –

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

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

SELECT <Список столбцов> FROM <Список таблиц>

WHERE <Условие отбора>;

 

Чтобы объединить несколько запросов в одну SQL-команду и, соответственно, объ-

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

 

Чтобы результат запроса был сохранен в файл, добавьте в команду SELECT выражение INTO OUTFILE 'Путь и имя файла' [FILEDS...] [LINES...]

 

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

 

При необходимости вы можете также задать параметры FILEDS и LINES, которые имеют тот же смысл, что и параметры FILEDS и LINES команды LOAD DATA (см. подраздел «Загрузка данных из файла»). Если впоследствии файл будет загружаться в базу данных MySQL с помощью команды LOAD DATA, то в команде LOAD DATA нужно будет указать те же самые значения параметров FILEDS и LINES, которые использовались при выгрузке.

 

Команды SELECT… INTO OUTFILE и LOAD DATA можно использовать для резервного копирования таблиц или для переноса данных на другой сервер MySQL.

 

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

 

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

SET <Имя столбца 1> = <Значение 1>,

...,

<Имя столбца N> = <Значение N>

[WHERE <Условие отбора>]

[ORDER BY <Имя столбца> [ASC или DESC]]

[LIMIT <Количество строк>];

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

UPDATE Customers SET phone = '444-25-27' WHERE id = 536;

В команде UPDATE используются следующие основные параметры:

· имя редактируемой таблицы;

• SET <Имя столбца 1> = <Значение 1>,..., <Имя столбца N> = <Знaчение N> – список столбцов и новых значений для этих столбцов.