Главная | Обратная связь | Поможем написать вашу работу!

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

⇐ ПредыдущаяСтр 3 из 12Следующая ⇒

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

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

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

· столбцы и типы данных в 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 (от -2⁷ до 2⁷-1) от 0 до 255 (от 0 до 2⁸-1)
SMALLINT (M) SMALLINT unsigned	2 байта	от -32 768 до 32 767 (от -2¹⁵ до 2¹⁵-1) от 0 до 65 535 (от 0 до 2¹⁶-1)
MEDIUMINT (M) MEDIUMINT unsigned	3 байта	от -8 388 608 до 8 388 607 (от -2²³ до 2²³-1) от 0 до 16 777 215 (от 0 до 2²⁴-1)
INT (INTEGER) (M) INT unsigned	4 байта	от -2 147 683 648 до 2 147 683 647 (от -2³¹ до 2³¹-1) от 0 до 4 294 967 295 (от 0 до 2³²-1)
BIGINT (M) BIGINT unsigned	8 байт	(от-2⁶³ до2⁶³-1) (от 0 до 2⁶⁴ -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.17549435110^-39 Максимальное по модулю значение 3.40282346610³⁸
DOUBLE (M, D), REAL (M,D), DOUBLE PRECISION (M,D)	8 байт	Минимальное по модулю значение 2.225073858507201410^-308 Максимальное по модулю значение 1.79769313486231510³⁰⁸

Числовые типы с плавающей точкой также могут иметь параметр 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 символов	2⁸-1 символов
BLOB, TEXT	L +2 символов	2¹⁶-1 символов
MEDIUMBLOB, MEDIUMTEXT	L +3 символов	2²⁴-1 символов
LONGBLOB, LONGTEXT	L +4 символов	2³²-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:

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

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

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

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

⇐ Предыдущая 1 234 5 6 7 8 9 10 Следующая ⇒

Воспользуйтесь поиском по сайту: