Анатолий Мотев СанктПетербург бхвпетербург 2006 удк 681 06 ббк 32. 973. 26018. 2 М85
 Скачать 4.25 Mb.
|
|
Óðîê 3. Òèïû äàííûõ è òèïû òàáëèö 49 жете хранить высоту, как строку '2/20' , т. е. 2 метра 20 сантиметров. Это значение легко для понимания, но его нельзя использовать для математиче- ских операций. Можно создать два поля, одно будет хранить значение в мет- рах, другое в сантиметрах. Так вы сможете использовать значения для раз- личных вычислений, но все же это не настолько удобно, как если бы пара- метр хранился в одном поле. Значит, надо создать одно поле и хранить высоту товара в миллиметрах. Возможно, это не очень понятно для пользова- теля, но зато удобно для хранения в БД. Что же касается восприятия пользователем, то любые данные при выводе можно отформатировать и представить в более понятной форме. Форматиро- ванием должно заниматься приложение, с которым работает пользователь (например, web-интерфейс, написанный вами на языке PHP). Другой пример. Если вам нужно хранить цену товара, то здесь тоже есть не- сколько вариантов. Типы FLOAT и DOUBLE не очень годятся на эту роль, т. к. они округляются, а все, что касается денег, требует особой точности. Тогда можно хранить величины в полях типа DECIMAL(length,2) , выбрав подходя- щую длину (значение параметра length ). В этом случае значения не округля- ются, но операции со строками, как правило, менее эффективны, чем с чис- лами. Есть еще один вариант: можно хранить одно число — значение в ко- пейках, но в этом случае при вводе/выводе потребуется привести его к необходимому формату вывода. После выбора типа данных надо решить, какой диапазон значений будет хра- ниться, — будут ли значения находиться в пределах от 0 до 100 или дости- гать миллионов. Вы, конечно, можете использовать самый большой тип (на- пример, BIGINT , для числовых значений) и ни о чем не беспокоиться. Однако следует использовать минимальный из возможных типов, для того чтобы со- кратить объем дискового пространства, занимаемого таблицей. Это позволит повысить производительность (помните, что на жестком диске сервера вы не одиноки). Если диапазон уж совсем неизвестен, то выбирайте тип BIGINT . Ес- ли выбранный тип оказался слишком маленьким, и его не хватает для хране- ния величин, то это не так страшно — впоследствии все можно поправить с помощью оператора ALTER TABLE . Вы также должны помнить о том, что чи- словые поля по умолчанию допускают ввод отрицательных чисел и вмещают в положительном диапазоне только половину возможных значений. Чтобы решить эту проблему, используйте атрибут UNSIGNED При создании строковых полей нужно также определиться с длиной храни- мого значения. Если строки не будут превышать 256 символов, то подойдет тип CHAR , VARCHAR , TINYTEXT или TINYBLOB . Для строк большей длины исполь- зуйте тип TEXT или BLOB . Если строка — это набор фиксированных величин, то используйте тип SET или ENUM , — они позволяют выполнять цифровые опе- рации и экономят память. ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 50 Èìåíà áàç äàííûõ, òàáëèö è ïîëåé Для имен баз данных, таблиц и полей используются одни и те же правила (они были изменены начиная с версии MySQL 3.23.6). Èìåíà áàç äàííûõ В MySQL есть только два правила именования баз данных — имя БД может иметь длину до 64 символов и может содержать любые символы, кроме пря- мого слэша ( / ), обратного слэша ( \ ) и точки ( ). Èìåíà òàáëèö Имена таблиц (как и имена полей, содержащихся в таблице) в вашей базе данных должны "говорить" о том, какие данные в них хранятся. Очень под- робное имя (например, shop_customer_information ) может быть очень инфор- мативным, но с другой стороны, слишком сложным для ввода в командной строке. Для большинства людей имя shop_cust будет вполне понятным и не вызовет проблем при наборе в командной строке. Имя таблицы MySQL может иметь длину до 64 символов и не может вклю- чать символы прямого слэша ( / ), обратного слэша ( \ ) и точки ( ). В MySQL есть зарезервированные слова, которые не рекомендуется исполь- зовать в качестве имен таблиц и полей (см. приложение 1). Èìåíà ïîëåé è îáðàùåíèå ê ïîëþ Имя поля может включать любые символы (до 64). Не рекомендуется исполь- зовать имена, подобные 1е , т. к. выражение вида 1е+1 является неоднознач- ным — его можно интерпретировать как выражение и как число. Если имя поля относится к служебным словам или содержит специальные символы, то его следует заключить в апострофы при использовании в выражениях: SELECT * FROM 'select' WHERE 'select'.id_sel>20; В табл. 3.9 приведены разрешенные в MySQL формы ссылки на поле таб- лицы. Префиксы db_name и table_name следует указывать, если ссылка на поле мо- жет быть неоднозначной. Например, при выборке из таблиц table1 и table2 , содержащих поле name , следует уточнить, какая из таблиц имеется в виду, т. е. указать table1.name и table2.name При описании любого из полей таблицы необходимо указать его имя, тип, размер, а также дополнительные атрибуты (их мы рассмотрим позже). Óðîê 3. Òèïû äàííûõ è òèïû òàáëèö 51 Òàáëèöà 3.9. Äîïóñòèìûå ôîðìû ññûëêè íà ïîëå òàáëèöû Ссылка Описание column_name Поле column_name из любой используемой в за- просе таблицы table_name.column_name Поле column_name из таблицы table_name теку- щей БД db_name.table_name.column_name Поле column_name из таблицы table_name базы данных db_name (эта форма доступна начиная с MySQL версии 3.22) 'column_name' Имя поля является зарезервированным словом или содержит специальные символы ×óâñòâèòåëüíîñòü èìåí ê ðåãèñòðó áóêâ В MySQL имена баз данных и таблиц соответствуют каталогам (папкам) и файлам в каталогах. Из этого следует, что чувствительность к регистру букв операционной системы, под которой работает сервер MySQL, определяет чувствительность к регистру букв для имен баз данных и таблиц. Имена баз данных и таблиц нечувствительны к регистру букв в ОС Windows, а в боль- шинстве версий UNIX проявляют чувствительность к регистру букв. Но, хотя в Windows имена нечувствительны к регистру, не нужно ссылаться на конкретную базу данных или таблицу, используя различные регистры внутри одного и того же запроса. Следующий запрос не будет выполнен, т. к. в нем одна и та же таблица указана и как reader , и как READER : SELECT * FROM reader WHERE READER.id_reader=1; Если вы не хотите держать в памяти регистр букв имен баз данных и таблиц при их создании, то рекомендую при именовании использовать только строч- ные буквы. Òèïû òàáëèö В MySQL есть несколько типов таблиц (табл. 3.10). При создании новой таб- лицы можно указать MySQL, какой тип таблицы для нее использовать. Для таблицы и определений полей MySQL всегда создает файл с расширением FRM. Индекс и данные хранятся в других файлах (расширения их имен зави- сят от типа таблицы). Типом, принятым по умолчанию для таблиц в MySQL, является MyISAM . Пре- образовывать таблицы из одного типа в другой можно при помощи оператора ALTER TABLE . Например: ALTER TABLE table_name TYPE = MyISAM; ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 52 Òàáëèöà 3.10. Ïîääåðæèâàåìûå MySQL òèïû òàáëèö Тип таблицы Описание ISAM Оригинальное хранилище (устаревший тип таблиц) MyISAM Бинарное переносимое хранилище (замена для ISAM) HEAP Хранятся только в памяти BDB или BerkeleyDB Поддержка транзакций InnoDB Поддержка транзакций с блокировкой строк MERGE Совокупность нескольких таблиц MyISAM, используемых как одна Следует отметить, что MySQL поддерживает два различных типа таблиц — транзакционные ( InnoDB и BDB ) и без поддержки транзакций ( HEAP , ISAM , MERGE и MyISAM ). Преимущества транзакционных таблиц (Transaction-safe tables, TST): надежность — даже если произойдет сбой в работе MySQL или возникнут проблемы с оборудованием, вы сможете восстановить свои данные либо методом автоматического восстановления, либо при помощи резервной копии и журнала транзакций; можно сочетать несколько операторов и принимать все эти операторы с помощью одной команды COMMIT ; можно запустить команду ROLLBACK , чтобы отменить внесенные изменения; если произойдет сбой во время обновления, все изменения будут восста- новлены (в таблицах без поддержки транзакций все внесенные изменения не могут быть отменены). Преимущества таблиц без поддержки транзакций (Non-transaction-safe tables, NTST): работа с ними происходит намного быстрее, т. к. не выполняются допол- нительные транзакции; для них требуется меньше дискового пространства, т. к. не применяются дополнительные транзакции; для обновлений используется меньше памяти. Давайте чуть подробнее рассмотрим каждый из типов таблиц. ISAM Этот тип является устаревшим, но его еще можно использовать в MySQL версии до 5.0. Индекс хранится в файле с расширением ISM, а данные — в файле с расширением ISD. Óðîê 3. Òèïû äàííûõ è òèïû òàáëèö 53 Свойства таблиц ISAM : ключи сжатой и фиксированной длины; фиксированная и динамическая длина записи; максимальная длина ключа 256 (по умолчанию); данные хранятся в машинном формате, благодаря этому обеспечивается скорость, но возникает зависимость от ОС. Основные отличия таблиц ISAM от MyISAM : таблицы ISAM не являются переносимыми в двоичном виде с одной ОС на другую; невозможна работа с таблицами размером больше 4 Гбайт; динамические таблицы больше фрагментируются; таблицы сжимаются при помощи утилиты PACK_ISAM, а не MYISAMPACK. MyISAM Тип таблиц MyISAM принят по умолчанию в MySQL версии 3.23. Он основан на коде ISAM и обладает в сравнении с ним большим количеством полезных дополнений. Индекс хранится в файле с расширением MYI (MYIndex), а данные — в фай- ле с расширением MYD (MYData). Таблицы типа MyISAM можно прове- рять/восстанавливать при помощи утилиты MYISAMCHK. Таблицы типа MyISAM можно сжимать при помощи команды MYISAMPACK, после чего они будут занимать намного меньше места. HEAP Для HEAP -таблиц используются хэш-индексы; эти таблицы хранятся в памяти. Благодаря этому их обработка осуществляется очень быстро, однако в случае сбоя MySQL будут утрачены все данные, которые в них хранились. Тип HEAP хорошо подходит для временных таблиц, например, для подсчета и вывода статистики на web-страницу. BDB èëè BerkeleyDB Поддержка таблиц типа BDB включена в дистрибутив исходного кода MySQL начиная с версии 3.23. Тип BerkeleyDB обеспечивает транзакционный обработчик таблиц для MySQL. Использование типа BerkeleyDB повышает для ваших таблиц шанс ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 54 уцелеть после сбоев, а также предоставляет возможность осуществлять опе- рации COMMIT (фиксация) и ROLLBACK (откат) для транзакций. Информацию о данном типе таблиц можно найти на сайте http://www.sleepycat.com. InnoDB Таблицы InnoDB разработаны компанией Innobase Oy (http://www.innodb.com). Эти таблицы в MySQL снабжены обработчиком, обеспечивающим безопас- ные транзакции с возможностями фиксации транзакции, отката и восстанов- ления после сбоя. Для таблиц InnoDB осуществляется блокировка на уровне строки. Перечисленные функции позволяют улучшить взаимную совмести- мость и повысить производительность в многопользовательском режиме. Тип InnoDB предназначается для получения максимальной производительно- сти при обработке больших объемов данных. По эффективности использова- ния процессора этот тип намного превосходит другие модели реляционных баз данных с памятью на дисках. Таблицы InnoDB могут иметь любой размер даже в тех операционных систе- мах, где размер файла ограничен двумя гигабайтами. MERGE Таблицы типа MERGE (объединение) появились в версии MySQL 3.23.25. Таблица типа MERGE (или MRG_MyISAM ) представляет собой коллекцию идентич- ных таблиц MyISAM , которые могут использоваться как одна таблица. Под идентичными таблицами подразумеваются таблицы, созданные с одина- ковой структурой и ключами. Нельзя объединять таблицы, в которых столб- цы сжаты разными методами или не совпадают, либо ключи расположены в другом порядке. При создании таблицы типа MERGE будут образованы файлы определений таблиц (расширение FRM) и списка таблиц (расширение MRG). MRG-файл содержит список индексных файлов (файлы с расширением MYI), с которы- ми нужно работать как с единым файлом. Вот некоторые возможности, обеспечиваемые таблицами типа MERGE : увеличение скорости работы; более эффективный поиск — если точно известно, что вы ищете, можно производить поиск по определенным запросам только в одной из состав- ляющих MERGE -таблицу таблиц, одновременно используя таблицу MERGE для других запросов; Óðîê 3. Òèïû äàííûõ è òèïû òàáëèö 55 более простое восстановление; быстрая обработка большого количества файлов как одного; обход ограничения на размер файлов в операционных системах. Но у них есть и недостатки: для создания таблицы типа MERGE можно использовать только идентичные таблицы типа MyISAM ; поля AUTO_INCREMENT (автоматически вычисляемые) не обновляются авто- матически при применении команды INSERT ; не работает команда REPLACE ; ключи считываются медленнее — при чтении ключа обработчику таблицы типа MERGE необходимо прочитать все базовые таблицы, чтобы выяснить, какая из них больше всего соответствует указанному ключу. ÂÎÏÐÎÑÛ ÄËß ÑÀÌÎÊÎÍÒÐÎËß 4. Какие типы таблиц MySQL вы можете использовать? 5. Какой тип определен по умолчанию? 6. Какой тип можно использовать для хранения временных данных? Итак, настало время вернуться к нашей учебной БД "Библиотека" ( library ). Мы будем использовать таблицы типа MyISAM (этот тип установлен в MySQL по умолчанию). Определим типы данных полей для наших таблиц (табл. 3.11—1.18). Òàáëèöà 3.11. Ïîëÿ òàáëèöû book (ñóùíîñòü "Êíèãà") Имя поля Тип поля Комментарии id_book MEDIUMINT(5) UNSIGNED Номер книги (до 99 999) title VARCHAR(50) Название книги (до 50 символов) year_issue YEAR Год издания id_author SMALLINT(4) UNSIGNED Номер автора (предполагается, что число авторов не превысит 9999) id_publisher SMALLINT(3) UNSIGNED Номер издательства (до 999) id_section TINYINT(2) UNSIGNED Номер раздела библиотеки (до 99) ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 56 Òàáëèöà 3.12. Ïîëÿ òàáëèöû unit (ñóùíîñòü "Ýêçåìïëÿð") Имя поля Тип поля Комментарии id_unit MEDIUMINT(6) UNSIGNED Номер экземпляра (до 999 999) id_book MEDIUMINT(5) UNSIGNED Номер книги (до 99 999) Òàáëèöà 3.13. Ïîëÿ òàáëèöû author (ñóùíîñòü "Àâòîð") Имя поля Тип поля Комментарии id_author SMALLINT(4) UNSIGNED Номер автора (до 9999) author VARCHAR(60) Фамилия, имя и отчество автора (60 символов для отображения) Òàáëèöà 3.14. Ïîëÿ òàáëèöû publisher (ñóùíîñòü "Èçäàòåëüñòâî") Имя поля Тип поля Комментарии id_publisher SMALLINT(2) UNSIGNED Номер издательства (до 999) publisher VARCHAR(30) Название издательства (до 30 символов) Òàáëèöà 3.15. Ïîëÿ òàáëèöû section (ñóùíîñòü "Ðàçäåë") Имя поля Тип поля Комментарии id_section TINYINT(2) UNSIGNED Номер раздела (до 99) section VARCHAR(30) Название раздела (до 30 символов) Òàáëèöà 3.16. Ïîëÿ òàáëèöû reader (ñóùíîñòü "×èòàòåëü") Имя поля Тип поля Комментарии id_reader MEDIUMINT(5) UNSIGNED Номер читателя (до 99 999) reader VARCHAR(60) Фамилия, имя и отчество читателя (до 60 символов) id_addr MEDIUMINT(5) UNSIGNED Номер адреса (значение этого поля не может превышать число читателей, каждому читате- лю — по отдельной квартире!) Óðîê 3. Òèïû äàííûõ è òèïû òàáëèö 57 Òàáëèöà 3.17. Ïîëÿ òàáëèöû address (ñóùíîñòü "Àäðåñ ÷èòàòåëÿ") Имя поля Тип поля Комментарии id_addr MEDIUMINT(5) UNSIGNED Номер адреса (значение этого поля не может превышать число читателей) address VARCHAR(80) Адрес читателя (до 80 символов) phone VARCHAR(19) Номер телефона (с учетом кода и пробелов) Òàáëèöà 3.18. Ïîëÿ òàáëèöû abonement (ñóùíîñòü "Àáîíåìåíò") Имя поля Тип поля Комментарии id_note MEDIUMINT(5) UNSIGNED Номер записи (до 99 999) id_reader MEDIUMINT(5) UNSIGNED Номер читателя id_unit MEDIUMINT(6) UNSIGNED Номер экземпляра get_date DATE Дата выдачи exp_date DATE Дата возврата Все числовые поля мы представили как беззнаковые, добавив при описании атрибут UNSIGNED . Это не позволит в данных полях появляться отрицательно- му значению. При создании всех этих таблиц на компьютере к некоторым полям будут добавлены еще несколько дополнительных атрибутов, например AUTO_INCREMENT , NOT NULL и др. Но об этом позже. То, что у нас получилось в итоге, вы можете увидеть на рис. 3.1. Все таблицы нашей базы данных спроектированы, теперь нужно создать эти таблицы непосредственно на компьютере. Как это делается, вы узнаете в сле- дующей части книги, повествующей о языке SQL. ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 58 Рис. 3.1. Структура базы данных library ("Библиотека") ЧАСТЬ II MySQL Óðîê 4. Óñòàíîâêà MySQL ïîä Windows Óðîê 5. Óòèëèòû MySQL Óðîê 6. Èñïîëüçîâàíèå êîìàíäíîé ñòðîêè äëÿ îáðàùåíèÿ ê ÁÄ Для установки и использования на вашем компьютере СУБД MySQL под управлением ОС Windows требуется: операционная система Windows 95/98/ME/NT/2000/XP; программа-архиватор для распаковки ZIP-архивов; достаточно свободного места на жестком диске для распаковки, установки и создания базы. Если у вас нет программы-архиватора, то можно загрузить временную вер- сию архиватора WinZip с сайта http://www.winzip.com. |