Главная | Обратная связь
МегаЛекции

Теоретические сведения





Рассмотрим следующие вопросы:

· создание и выбор базы данных;

· создание таблиц;

· столбцы и типы данных в MySQL;

· создание индексов;

· удаление таблиц, индексов и баз данных;

· изменение структуры таблиц.

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

Чувствительность к регистру и идентификаторы.

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

· Все идентификаторы, кроме имен псевдонимов, могут содержать до 64 сим­волов. Имена псевдонимов могут иметь до 255 символов.

· Идентификаторы могут содержать любые допустимые символы, но имена баз данных не могут содержать символы /, \ и . , а имена таблиц – символы . и /.

· Зарезервированные слова можно использовать для идентификаторов, если заключить их в кавычки.

Комментарий в SQL. Начинается с двух дефисов (--), за которыми должен следовать пробел. Кроме того, MySQL содержит ряд собственных комментариев. Shell-комментарий # действует аналогично – все, что расположено правее его, является текстом комментария. С-комментарий /* */ является многострочным – комментарий начинается с /* и заканчивается, когда встретится завершение */.

Создание и выбор базы данных. Осуществляется с помощью оператора

create database имя_базы_данных;

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

show databases;

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

use имя_базы_данных;

Теперь все действия по умолчанию будут применяться именно к этой БД.

Создание таблиц.Используется оператор CREATE TABLE, который в общем виде выглядит следующим образом:

create [temporary] table [if not exists]

имя_таблицы (определение таблицы)



[type=тип_таблицы];

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

При использовании выражения IF NOT EXISTS таблица будет создана толь­ко в том случае, если еще нет таблицы с указанным именем.

Создать таблицу с такой же схемой, как у существующей, позволяет команда

create [temporary] table [if not exists]

имя_таблицы LIKE имя_старой_таблицы;

После имени таблицы в скобках объявляются имена столбцов, их типы и другая информация. В определение столбца можно добавить следующие описания.

· Объявить для любого столбца NOT NULL или NULL (столбцу запрещено или не запрещено содержать значения NULL). По умолчанию – NULL.

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

· Использовать ключевое слово AUTO_INCREMENT, чтобы генерировать порядковый номер. Автоматически генерируемое значение будет на единицу большим, чем наибольшее значение в таблице. Первая введенная строка будет иметь порядковый номер 1. В таблице можно иметь не более одного столбца AUTO_INCREMENT, и он должен индексироваться.

· Объявить столбец первичным ключом таблицы с помощью выражения PRIMARY KEY.

· Объявить столбец внешним ключом, используя выражение FOREIGN KEY,с ссылкой на соответствующую таблицу с помощью выражения REFERENCES.

· Индексировать столбец с помощью слов INDEX или KEY (синонимы). Такие столбцы не обяза­тельно должны содержать уникальные значения.

· Индексировать столбец с помощью слова UNIQUE, которое используется для указания того, что столбец должен содержать уникальные значения.

· Создать полнотекстовые индексы на основе столбцов типа TEXT, CHAR или VARCHAR с помощью слова FULLTEXT (только с таблицами MyISAM).

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

· MyISAM – таблицы этого типа являются «родными» для MySQL, работают очень быстро и поддерживают полнотекстовую индексацию;

· InnoDB – ACID-совместимый механизм хранения, поддерживающий транзакции, внешние ключи, каскадное удаление и блокировки на уровне строк;

· BDB (Berkeley DB) – является механизмом хранения, который обеспечивает поддержку транзакций и блокировки на уровне страниц;

· MEMORY (HEAP) – таблицы целиком хранятся в оперативной памяти и никогда не записываются на диск, поэтому работают очень быстро, но ограничены в размерах и не допускают возможности восстановления в случае отказа системы;

· MERGE – тип позволяет объединить несколько таблиц MyISAM с одной структурой, чтобы к ним можно было направлять запросы как к одной таблице;

· NDB Cluster – тип предназначен для организации кластеровMySQL, когда таблицы распределены между несколькими компьютерами, объединенными в сеть;

· ARCHIVE – тип введен для хранения большого объема данных в сжатом формате; таблицы поддерживают только два SQL-оператора: INSERT и SELECT, причем оператор SELECT выполняется по методу полного сканирования таблицы;

· CSV – формат представляет собой обычный текстовый фал, записи в котором хранятся в строках, а поля разделены точкой с запятой (широко распространен в компьютерном мире, любая программа, поддерживающая CSV-формат, может открыть такой файл);

· FEDERATED – тип позволяет хранить данные в таблицах на другой машине сети (при создании таблицы в локальной директории создается только файл определения структуры таблицы, а все данные хранятся на удаленной машине).

MySQL поддерживает следующие типы данных, допустимые для столбцов:

· числовые;

· строковые;

· календарные;

· null – специальный тип, обозначающий отсутствие информации.

Числовые типы используются для хранения чисел и представляют два подтипа:

· точные числовые типы;

· приближенные числовые типы.

К точным числовым типам (табл. 1) относятся целый тип INTEGER и его вариации, а также вещественный тип decimal (синонимы numeric и dec). Последний используется для представления денежных данных.

Числовые типы могут характеризоваться максимальной длиной М. Для типа decimal параметр м задает число символов для отображения всего числа, a d – для его дробной части. Например: b_price DECIMAL (5, 2). Цифра 5 определяет общее число символов под число, а цифра 2 – количество знаков после запятой (ин­тервал величин от –99.99 до 99.99). Можно не использовать параметры вообще, указать только общую длину или указать длину и число десятичных разрядов.

Объявления точных числовых типов можно завершать ключевыми словами UNSIGNED и (или) ZEROFILL. Ключевое слово UNSIGNED указывает, что столбец содержит только положи­тельные числа или нули. Ключевое слово ZEROFILL означает, что число будет отображаться с ведущими нулями.

Таблица 1

Тип Объем памяти Диапазон
TINYINT (M) TINYINT unsigned 1 байт от -128 до 127 (от -27 до 27-1) от 0 до 255 (от 0 до 28-1)
SMALLINT (M) SMALLINT unsigned 2 байта от -32 768 до 32 767 (от -215 до 215-1) от 0 до 65 535 (от 0 до 216-1)
MEDIUMINT (M) MEDIUMINT unsigned 3 байта от -8 388 608 до 8 388 607 (от -223 до 223-1) от 0 до 16 777 215 (от 0 до 224-1)
INT (INTEGER) (M) INT unsigned 4 байта от -2 147 683 648 до 2 147 683 647 (от -231 до 231-1) от 0 до 4 294 967 295 (от 0 до 232-1)
BIGINT (M) BIGINT unsigned 8 байт (от-263 до263-1) (от 0 до 264 -1)
BIT (M) (М+7)/8 байт От 1 до 64 битов, в зависимости от значения М
BOOL, BOOLEAN 1 байт 0 (false) либо 1 (true)
DECIMAL (M, D), NUMERUC (M, D) М + 2 байта Повышенная точность, зависит от параметров М и D

К приближенным числовым типам (табл. 2) относятся:

· FLOAT – представление чисел с плавающей запятой с обычной точностью;

· DOUBLE – представление чисел с плавающей запятой с двойной точностью.

Таблица 2

Тип Объем памяти Диапазон
FLOAT (М, D) 4 байта Минимальное по модулю значение 1.175494351*10-39 Максимальное по модулю значение 3.402823466*1038
DOUBLE (M, D), REAL (M,D), DOUBLE PRECISION (M,D) 8 байт Минимальное по модулю значение 2.2250738585072014*10-308 Максимальное по модулю значение 1.797693134862315*10308

Числовые типы с плавающей точкой также могут иметь параметр unsigned. Атрибут предотвращает хранение в столбце отрицательных величин, но максималь­ный интервал величин столбца остается прежним.

Приближенные числовые данные могут задаваться в обычной форме (например, 45.67) и в форме с плавающей точкой (например, 5.456Е-02 или 4.674Е+04).

Текстовые типы и строки (табл. 3):

· CHAR – хранение строк фиксированной длины;

· VARCHAR – хранение строк переменной длины;

· TEXT, BLOB и их вариации – хранение больших фрагментов текста;

· ENUM и SET – хранение значений из заданного списка.

Таблица 3

Тип Объем памяти Максимальный размер
CHAR(M) М символов М символов
VARCHAR(M) L+1 символов М символов
TINYBLOB, TINYTEXT L+1 символов 28-1 символов
BLOB, TEXT L+2 символов 216-1 символов
MEDIUMBLOB, MEDIUMTEXT L+3 символов 224-1 символов
LONGBLOB, LONGTEXT L+4 символов 232-1 символов
ENUM('value 1', 'value2 ', ...) 1 или 2 байта 65 535 элементов
SET('value 1', 'value2', ...) 1, 2, 3, 4 или 8 байт 64 элемента

Здесь l – длина хранимой в ячейке строки, а приплюсо­ванные к l байты – накладные расходы для хранения длины строки.

Для строк varchar требуется количество символов, равное длине строки плюс 1 байт, тогда как тип char(m), независимо от длины строки, ис­пользует для ее хранения все м символов. Тип char обрабатывается эф­фективнее переменных типов. Нельзя смешивать в таблице столбцы char и varchar. Если есть столбец переменной длины, все столбцы типа char будут приведены к типу varchar.

Типы blob и text аналогичны и отличаются в дета­лях. При выполнении операций над столбцами типа text учитывается ко­дировка, а типа blob – нет. Тип text используется для хранения больших объемов текста, тип blob – для больших двоичных объектов (электронные документы, изобра­жения, звук). Основное отличие text от char и varchar – поддержка полнотекстового поиска.

Строки типов данных enum и set принимают значе­ния из заданного списка. Значение типа enum должно содержать точно одно зна­чение из указанного множества, тогда как столбцы set могут содержать любой или все элементы заданного множества одновременно. Для типа set (как и для enum) при объявлении задается список возможных значений, но ячейка может принимать любое значение из списка, а пустая строка оз­начает, что ни один из элементов списка не выбран.

Типы enum и set задаются списком строк, но во внутреннем представлении элементы множеств сохраняются в виде чисел. Элементы типа enum нумеруются последова­тельно, начиная с 1. Под столбец может отводиться 1 байт (до 256 элементов в списке) или 2 байта (от 257 до 65536 элемен­тов в списке). Элементы типа set обрабатываются как биты, размер типа оп­ределяется числом элементов в списке: 1 байт (от 1 до 8 элементов), 2 байта (от 9 до 16 элементов), 3 байта (от 17 до 24 элементов), 4 байта (от 25 до 32 элементов) и 8 байт (от 33 до 64 элементов).

Календарные типы данных (табл. 4):

· Date – для хране­ния даты (формат YYYY-MM-DD для дат вида 2009-10-15 и формат YY-MM-DD для дат вида 09-10-15);

· Time – для хранения времени суток (формат hh:mm:ss, где hh – часы, mm – минуты, ss – секунды, например, 10:48:56);

· Datetime– для представления и даты, и времени суток;

· Timestamp – если в соответствующем столбце строки не указать конкретное значение или NULL, там будет записано время, когда соответствующая строка была создана или в последний раз изменена (в формате DATETIME);

· Year – позволяет хранить только год.

Таблица 4

Тип Объем памяти Диапазон
DATE 3 байта от '1000-01-01' до '9999-12-31'
TIME 3 байта от '-828:59:59' до '828:59:59'
DATATIME 8 байт от '1000-01-01 00:00:00' до '9999-12-31 00:00:00'
TIMESTAMP (M) 4 байта от '1970-01-01 00:00:00' до '2038-12-31 59:59:59'
YEAR(2) YEAR(4) 1 байт формат YY, диапазонот 1970 до 2069 формат YYYY, диапазон – от 1901 до 2155

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

'2009-04-06 02:04:08' '2009-4-06 02:04:08' '2009-4-6 02:04:08'

'2009-4-6 2:04:08' '2009-4-6 2:4:08' '2009-4-6 2:4:8'

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

'09-12-31 11:30:45' '09.12.31 11+30+45' '09/12/31 11*30*45'

При указании времени после секунд через точку можно указать микросекунды, т. е. использовать расширенный формат вида hh:mm:ss.ffffff, например '10:25:14.000001'. Кроме того, можно использовать краткие форматы НН:ММ и НН (вместо пропущенных величин бу­дут подставлены нулевые значения).

Если время задается в недопустимом формате, то в поле записывается нулевое значе­ние. Нулевое значение присваивается полям временного типа по умолчанию, когда им не присваивается инициирующее значение (табл. 5).

Таблица 5

Тип Нулевое значение
DATE '0000-00-00'
TIME '00:00:00'
DATATIME '0000-00-00 00:00:00'
TIMESTAMP
YEAR

Формат timestamp совпадает с datetime, но во внутреннем представлении дата хранится как число секунд, прошедших с полуночи 1 января 1970 г. (такое исчисление принято в операционной системе UNIX, а да­та 01.01.1970 считается началом эпохи UNIX и днем рождения операционной системы).

Если в таблице несколько столбцов timestamp, при модификации записи текущее время будет записываться только в один из них (первый). Можно явно указать столбец, которому необходимо назначать текущую дату при создании или изменении записи. Чтобы поля принимали текущую дату при создании записи, следует после определения столбца доба­вить default current_timestamp. Если текущее время должно выставляться при модификации записи, при использовании оператора update следует добавить on update current_timestamp.

Тип данных NULL используется, когда информации недостаточно и для части данных нельзя опреде­лить, какое значение они примут. Для указания того, что поле может принимать неопределенное значение, в определении столбца после типа данных следует указать ключевое слово null. Если поле не должно принимать значение null, следует указать ключевое слово not null.

Рекомендации по выбору типа данных.

· Обработка числовых данных происхо­дит быстрее строковых. Так как типы enum и set имеют внутреннее числовое представление, им следует отдавать предпочтение перед другими видами строковых данных, если это возможно.

· Производительность можно увеличить за счет представления строк в виде чисел. Пример – преобразование IP-адреса из строки в bigint. Это позволит уменьшить размер таблицы и значительно увеличить скорость при сортировке и выборке данных, но потребует дополнительных преобразований.

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

· Типы фиксированной длины обрабатываются быстрее типов переменной длины, т. к. в последнем случае при частых удалениях и модификациях таблицы происходит ее фрагментация.

· Если применение столбцов с данными переменной длины неизбежно, для дефрагментации таблицы следует применять команду optimize table.

Обеспечение ссылочной целостности. Задается конструкцией:

foreign key [name_key] (col1, ... ) REFERENCES tbl (tbl_col, ... )

[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}] [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}]

Конструкция позволяет задать внешний ключ с необязательным именем name_key на столбцах, которые задаются в круглых скобках (один или несколько). Ключевое слово references указывает таблицу tbl, на которую ссылается внешний ключ, в круглых скобках указываются имена столбцов. Необязательные конструкции ON DELETE и ON UPDATE позволяют задать поведение СУБД при удалении и обновлении строк из таблицы-предка. Параметры, следующие за этими ключевыми словами, имеют следующие значения:

· CASCADE – при удалении или обновлении записи в таблице-предке, содержащей первичный ключ, записи со ссылками на это значение в таблице-потомке удаляются или обновляются автоматически;

· SET NULL – при удалении или обновлении записи в таблице-предке, содержащей первичный ключ, в таблице-потомке значения внешнего ключа, ссылающегося на таблицу-предка, устанавливаются в null;

· NO ACTION – при удалении или обновлении записей, содержащих первичный ключ, с таблицей-потомком никаких действий не производится;

· RESTRICT – если в таблице-потомке имеются записи, ссылающиеся на первичный ключ таблицы-предка, при удалении или обновлении записей с таким первичным ключом возвращается ошибка;

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

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

Все необходимые индексы формируются при создании таблицы. Индексированы будут все столбцы, объявленные как PRIMARY KEY, KEY, UNIQUE или INDEX. Индекс также можно добавить с помощью оператора CREATE INDEX. Перед выполнением оператор преобразуется в оператор ALTER TABLE. Например, создание индекса с именем name на основе поля u_name из таблицы users:

create index name on users (u_name);

Перед ключевым сло­вом index может присутствовать UNIQUE, требующее уникальности ограниче­ния.

Корректность таблиц в БД можно проверить с помощью оператора

SHOW TABLES;

Более подробную информацию о структуре таблицы дает команда

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

Переименование БД. Специального оператора переименования БД нет, но можно переименовать каталог БД в системном каталоге (…\DATA).

Удаление БД. Удалить всю БД вместе с ее содержимым можно командой:

drop database [IF EXISTS] имя_базы_данных;

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

drop table [IF EXISTS] имя_таблицы;

Удалить индекс можно с помощью оператора:

drop index имя_индекса on имя_таблицы;

Изменение структуры таблиц.Изменить структуру су­ществующей таблицы можно с помощью оператора ALTER TABLE. Например, можно создать индекс name для таблицы users следующим образом:

alter table users add index name (u_name);

Оператор ALTER TABLE является исключительно гибким, поэтому он имеет огромное множество дополнительных ключевых слов.

 

Практическая работа

При выполнении лабораторной работы необходимо для заданной предметной области средствами MySQL:

· создать базу данных;

· создать таблицы, определить поля таблиц, индексы;

· определить связи между таблицами и ограничения целостности;

· составить отчет по лабораторной работе.

 





Рекомендуемые страницы:

Воспользуйтесь поиском по сайту:
©2015- 2020 megalektsii.ru Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав.