• Глава №6. Диалект SQL, используемый в MySQL и mSQL
• Основы SQL
• Создание и удаление таблиц
• Типы данных в SQL
• Индексы
• Последовательности и автоинкрементирование
• Управление данными
• Расширения языка

Глава №6.

Диалект SQL, используемый в MySQL и mSQL

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

SQL является разновидностью «естественного языка». Иными словами, команда SQL должна читаться, по крайней мере на первый взгляд, как. предложение английского языка. У такого подхода есть как преимущества, так и недостатки, но факт заключается в том, что этот язык очень непохож на традиционные языки программирования, такие как С, Java или Perl.

В этой главе мы рассмотрим язык SQL, как он реализован в MySQL и mSQL. По большей части, диалект MySQL является надмножеством диалекта mSQL. Мы старательно отметим те случаи, где два диалекта расходятся. Однако в основном эта глава относится и к одной, и к другой СУБД.

Основы SQL

SQL «структурирован» в том отношении, что он следует определенному набору правил. Компьютерной программе легко разобрать на части сформулированный запрос SQL. Действительно, в книге издательства O'Reilly «lex & у асе», написанной Джоном Ливайном, Тони Мэйсоном и Дугом Брауном (John Levine, Tony Mason, Doug Brown), реализована грамматика SQL для демонстрации процесса создания программы, интерпретирующей язык! Запрос (query) - это полностью заданная команда, посылаемая серверу баз данных, который выполняет запрошенное действие. Ниже приведен пример SQL-запроса:

SELECT name FROM people WHERE name LIKE 'Stac%'

Как можно видеть, это предложение выглядит почти как фраза на ломаном английском языке: «Выбрать имена из список люди, где имена похожи на Stac». SQL в очень незначительной мере использует форматирование и специальные символы, обычно ассоциируемые с компьютерными языками. Сравните, к примеру, «$++;($*++/$|);$&$^л„;$!» в Perl и «SELECT value FROM table» в SQL.

История SQL

В IBM изобрели SQL в начале 1970-х, вскоре после введения д-ром Е. Ф. Коддом (Е. F. Codd) понятия реляционной базы данных. С самого начала SQL был легким в изучении, но мощным языком. Он напоминает естественный язык, такой как английский, и поэтому не утомляет тех, кто не является техническим специалистом. В 1970-х это достоинство было еще более важным, чем сегодня.

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

SQL действительно был настолько популярен среди пользователей, для которых предназначался, что в 1980-х компания Oracle выпустила первую в мире общедоступную коммерческую SQL-систему. Oracle SQL был хитом сезона и породил вокруг SQL целую индустрию. Sybase, Informix, Microsoft и ряд других компаний вышли на рынок с собственными разработками реляционных систем управления базами данных (РСУБД), основанных на SQL.

В то время когда Oracle и ее конкуренты вышли на сцену, SQL был новинкой, и для него не существовало стандартов. Лишь в 1989 году комиссия по стандартам ANSI выпустила первый общедоступный стандарт SQL. Сегодня его называют SQL89. К несчастью, этот новый стандарт не слишком углублялся в определение технической структуры языка. Поэтому, хотя различные коммерческие реализации языка SQL сближались, различия в синтаксисе делали задачу перехода с одной реализации языка на другую нетривиальной. Только в 1992 году стандарт ANSI SQL вступил в свои права.

Произносится как «сиквел» или «эс-кю-эль». Некоторые люди относятся с благоговением к произношению SQL. He обращайте на них внимания. Однако нужно заметить, что в MySQL и mSQL правильное произношение -«эс-кю-эль».

Стандарт 1992 года обозначают как SQL92 или SQL2. Стандарт SQL2 включил в себя максимально возможное количество расширений, добавленных в коммерческих реализациях языка. Большинство инструментов, работающих с различными базами данных, основывается на SQL2 как на способе взаимодействия с реляционными базами данных. Однако, из-за очень большой широты стандарта SQL2, реляционные базы, реализующие полный стандарт, очень сложные и ресурсоемкие.

SQL2 - не последнее слово в стандартах SQL. В связи с ростом популярности объектно-ориентированных СУБД (ООСУБД) и объектно-реляционных СУБД (ОРСУБД) возрастает давление с целью принятия объектно-ориентированного доступа к базам данных в качестве стандарта SQL. Ответом на эту проблему должен послужить SQL3. Он не является пока официальным стандартом, но в настоящее время вполне определился и может стать официальным стандартом где-то в 1999 году.

С появлением MySQL и mSQL проявился новый подход к разработке серверов баз данных. Вместо создания очередной гигантской РСУБД с риском не предложить ничего нового в сравнении с «большими парнями», были предложены небольшие и быстрые реализации наиболее часто используемых функций SQL.

Архитектура SQL

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

CREATE TABLE people (name CHAR(10))

глагол дополнение расширенное определение

INSERT INTO people VALUES('me')

глагол косвенное прямое

дополнение дополнение

SELECT name FROM people WHERE name LIKE '%e'

глагол прямое косвенное придаточное дополнение дополнение предложение

Большинство реализаций SQL, включая MySQL и mSQL, нечувствительны к регистру: неважно, в каком регистре вы вводите ключевые слова SQL, если орфография верна. Например, CREATE из верхнего примера можно записать и так:

cREatE ТАblЕ people (name cHaR(10))

Нечувствительность к регистру относится только к ключевым словам SQL. Ради удобочитаемости все ключевые слова SQL в этой книге записаны заглавными буквами. Мы рекомендуем такой стиль как хороший, «опробованный на практике» способ. В MySQL и mSQL имена баз данных, таблиц и колонок к регистру чувствительны. Но это характерно не для всех СУБД. Поэтому, если вы пишете приложение, которое должно работать с любыми СУБД, не следует использовать имена, различающиеся одним только регистром.

Первый элемент SQL-запроса - всегда глагол. Глагол выражает действие, которое должно выполнить ядро базы данных. Хотя остальная часть команды зависит от глагола, она всегда следует общему формату: указывается имя объекта, над которым осуществляется действие, а затем описываются используемые при действии данные. Например, в запросе CREATE TABLE people (char(10)) используется глагол CREATE, за которым следует дополнение (объект) TABLE .Оставшаяся часть запроса описывает таблицу, которую нужно создать.

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

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

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

Успешно установив MySQL или mSQL, вы можете приступить к созданию своей первой таблицы. Таблица, структурированное вместилище данных, является основным понятием реляционных баз. Прежде чем начать вводить данные в таблицу, вы должны определить ее структуру. Рассмотрим следующую раскладку:

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

Общий синтаксис для создания таблиц следующий:

CREATE TABLE table_name (colutnn_namel type [modifiers] [, column_name2 type [modifiers]] )

Какие идентификаторы - имена таблиц и колонок - являются допустимыми, зависит от конкретной СУБД. mSQL обеспечивает поддержку имен в объеме, близком к минимальному. В качестве идентификатора он допускает любую последовательность букв набора ISO 8859-1 (Latin 1), цифр и знака «-», длиной до 20 символов. Идентификатор должен начинаться с буквы. Проблемы вызывает ограничение на использование только ISO 8859-1. Для хорошей переносимости SQL нужно избегать имен, начинающихся не с допустимой буквы. MySQL предоставляет больше возможностей. Длина идентификатора может быть до 64 символов, допустим символ «$», и первым символом может быть цифра. Более важно, однако, что MySQL допускает использование любых символов из установленного в системе локального набора.

Колонка - это отдельная единица данных в таблице. В таблице может содержаться произвольное число колонок, но использование больших таблиц бывает неэффективным. Вот здесь правильное проектирование базы данных, обсуждавшееся в главе 2, начинает играть важную роль. Создав правильно нормализованные таблицы, можно объединять их («join») для осуществления поиска в данных, размещенных в нескольких таблицах. Механику объединения таблиц мы обсудим позднее в данной главе.

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

DROP TABLE Lable_name

Эта команда не оставит и следа от таблицы в базе данных. MySQL уничтожит все данные удаленной таблицы. Если у вас не осталось резервной копии, нет абсолютно никакого способа отменить действие данной операции. Мораль этой истории: всегда храните резервные копии и будьте очень внимательны при удалении таблиц. В один «прекрасный» день это вам пригодится.

В MySQL можно одной командой удалить несколько таблиц, разделяя их имена запятыми. Например, DROP TABLE people, animals, plants удалит эти три таблицы. Можно также использовать модификатор IF EXISTS для подавления ошибки в случае отсутствия удаляемой таблицы. Этот модификатор полезен в больших сценариях, предназначенных для создания базы данных и всех ее таблиц. Прежде чем создавать таблицу, выполните команду DROP TABLE table_name IF EXISTS.

Типы данных в SQL

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

В главе 15 «Справочник по SQL» дается полное справочное руководство по типам SQL, поддерживаемым MySQL и mSQL. В таблице 6-1 дан сокращенный список, состоящий из наиболее употребительных типов, поддерживаемых в обоих языках.

Таблица 6-1. Наиболее употребительные типы, данных, поддерживаемые как MySQL, так и mSQL

«MySQL поддерживает атрибут UNSIGNED для всех числовых типов. Этот модификатор позволяет вводить в колонку только положительные (беззнаковые) числа. Беззнаковые поля имеют верхний предел значений вдвое больший, чем у соответствующих знаковых типов. Беззнаковый TINYINT - однобайтовый числовой тип MySQL - имеет диапазон от 0 до 255, а не от -127 до 127, как у своего знакового аналога.

Та и другая СУБД имеют больше типов, чем перечислено выше. Особенно большое число типов поддерживает MySQL. Однако на практике в основном используются перечисленные типы. В mSQL выбор типа данных сводится к выбору типа, наиболее близкого к данным, которые вы собираетесь хранить. Размер данных, которые вы собираетесь хранить, играет гораздо большую роль при разработке таблиц MySQL.

Числовые типы данных

Прежде чем создавать таблицу, вы должны хорошо представить себе, какого рода данные вы будете в ней хранить. Помимо очевидного решения о том, будут это числовые или символьные данные, следует выяснить примерный размер хранимых данных. Если это числовое поле, то каким окажется максимальное значение? Может ли оно измениться в будущем? Если минимальное значение всегда положительно, следует рассмотреть использование беззнакового типа. Всегда следует выбирать самый маленький числовой тип, способный хранить самое большое мыслимое значение. Если бы, к примеру, требовалось хранить в поле численность населения штата, следовало бы выбрать беззнаковый INT. Ни в каком штате не может быть отрицательной численности населения, и для того, чтобы беззнаковое поле типа INT не могло вместить число, представляющее его население, численность населения этого штата должна примерно равняться численности населения всей Земли.

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

С символьными типами работать немного труднее. Вы должны подумать не только о максимальной и минимальной длине строки, но также о среднем размере, частоте отклонения от него и необходимости в индексировании. В данном контексте мы называем индексом поле или группу полей, в которых вы собираетесь осуществлять поиск — в основном, в предложении WHERE. Индексирование, однако, значительно сложнее, чем такое упрощенное определение, и мы займемся им далее в этой главе. Здесь важно лишь отметить, что индексирование по символьным полям происходит значительно быстрее, если они имеют фиксированную длину. В действительности, mSQL даже не позволяет индексировать поля переменной длины. Если длина строк не слишком колеблется или, что еще лучше, постоянна, то, вероятно, лучше выбрать для поля тип CHAR. Пример хорошего кандидата на тип CHAR — код страны. Стандартом ISO определены двухсимвольные коды для всех стран (US для США, FR для Франции и т. д.). Поскольку эти коды состоят ровно из двух символов, CHAR(2) будет правильным выбором для данного поля.

Чтобы подходить для типа CHAR, поле необязательно должно быть фиксированной длины, но длина не должна сильно колебаться. Телефонные номера, к примеру, можно смело хранить в поле CHAR(13), хотя длина номеров различна в разных странах. Просто различие не столь велико, поэтому нет смысла делать поле для номера телефона переменным по длине. В отношении поля типа CHAR важно помнить, что, вне зависимости от реальной длины хранимой строки, в поле будет ровно столько символов, сколько указано в его размере — не больше и не меньше. Разность в длине между размером сохраняемого текста и размером поля заполняется пробелами. Не стоит беспокоиться по поводу нескольких лишних символов при хранении телефонных номеров, но не хотелось бы тратить много места в некоторых других случаях. Для этого существуют текстовые поля переменной длины.

Хороший пример поля, для которого требуется тип данных с переменной длиной, дает URL Интернет. По большей части адреса Web занимают сравнительно немного места - http://www.ora.com, http:// www.hughes.com.au, http://www.mysql.com - и не представляют проблемы. Иногда, однако, можно наткнуться на адреса подобного вида: http://www.winespectator.com/Wine/Spectator/ _notes\5527293926834323221480431354? Xvl I =&Xr5=&Xvl =& type-region-search- code=&Xal 4=flora+springs&Xv4=.

Если создать поле типа CHAR длины, достаточной для хранения этого URL, то почти для каждого другого хранимого URL будет напрасно тратиться весьма значительное пространство. Поля переменной длины позволяют задать такую длину, что оказывается возможным хранение необычно длинных значений, и в то же время не расходуется напрасно место при хранении обычных коротких величин. В MySQL и mSQL подход к этой проблеме различный.

Поля переменной длины в MySQL

Если вы используете только mSQL, этот раздел можно пропустить. Преимуществом текстовых полей переменной длины в MySQL является то, что они используют ровно столько места, сколько необходимо для хранения отдельной величины. Например, поле типа VARCHAR(255) , в котором хранится строка «hello, world», занимает только двенадцать байтов (по одному байту на каждый символ плюс еще один байт для хранения длины).

В отличие от стандарта ANSI, в MySQL поля типа VARCHAR не дополняются пробелами. Перед записью из строки удаляются лишние пробелы.

Сохранить строки, длина которых больше, чем заданный размер поля, нельзя. В поле VARCHAR(4) можно сохранить строку не длиннее 4 символов. Если вы попытаетесь сохранить строку «happy birthday», MySQL сократит ее до «happ». Недостатком подхода MySQL к хранению полей переменной длины, в сравнении с mSQL, является то, что не существует способа сохранить необычную строку, длина которой превосходит заданное вами значение. В таблице 6-2 показан размер пространства, необходимого для хранения 144-символьного URL, продемонстрированного выше, и обычного, 30-символьного URL,

Таблица 6-2. Пространство памяти, необходимое для различных символьных типов MySQL

Если через годы работы со своей базой данных вы обнаружите, что мир изменился, и поле, уютно чувствовавшее себя в типе VARCHAR(25) , должно теперь вмещать строки длиной 30 символов, не все потеряно. В MySQL есть команда ALTER TABLE , позволяющая переопределить размер поля без потери данных.

ALTER TABLE mytable MODIFY tnycolumn LONGTEXT

Поля переменной длины в mSQL

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

Остановимся немного на последствиях различий в подходе. Чтобы сохранить все вышеперечисленные URL в поле типа CHAR, потребуется колонка типа CHAR(144). При таком развитии событий четыре упомянутые URL займут 576 байт (144 х 4), хотя фактически хранится только 216 байт данных. Остальные 360 байт- просто потерянное пространство. Если помножить эту цифру на тысячи и миллионы строк, то можно понять, что это представляет собой серьезную проблему. Если же использовать поле переменной длины типа ТЕХТ(30), то для хранения 216 байт данных требуется только 234 байта (30 X 3 + 144). Всего лишь 18 байт потеряно. Экономия составила 41%!

Двоичные типы данных

В mSQL нет поддержки двоичных данных. В MySQL, напротив, есть целый ряд двоичных типов данных, соответствующих своим символьным аналогам. Двоичными типами, поддерживаемыми MySQL, являются CHAR BINARY , VARCHAR BINARY , TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB. Практическое отличие между символьными типами и их двоичными аналогами основано на принципе кодировки. Двоичные данные просто являются куском данных, которые MySQL не пытается интерпретировать. Напротив, символьные данные предполагаются представляющими текстовые данные из используемых человеком алфавитов. Поэтому они кодируются и сортируются, основываясь на правилах, соответствующих рассматриваемому набору символов. Двоичные же данные MySQL сортирует в порядке ASCII без учета регистра.

Перечисления и множества

MySQL предоставляет еще два особых типа данных, не имеющих аналога в mSQL. Тип ENUM позволяет при создании таблицы указать список возможных значений некоторого поля. Например, если бы у вас была колонка с именем «фрукт», в которую вы разрешили бы помещать только значения «яблоко», «апельсин», «киви» и «банан», ей следовало бы присвоить тип ENUM:

CREATE TABLE meal(meal_id INT NOT NULL PRIMARY KEY,

фрукт ENUM('яблоко', 'апельсин', 'киви', 'банан'))

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

Тип MySQL SET работает аналогично, но позволяет одновременно хранить в поле несколько значений.

Другие типы данных

Любые мыслимые данные можно хранить с помощью числовых или символьных типов. В принципе, даже числа можно хранить в символьном виде. Однако то, что это можно сделать, не означает, что это нужно делать. Рассмотрим, к примеру, как хранить в базе данных денежные суммы. Можно делать это, используя INT или REAL. Хотя интуитивно REAL может показаться более подходящим - в конце концов, в денежных суммах нужны десятичные знаки, - на самом деле более правильно использовать INT. В полях, содержащих значения с плавающей запятой, таких как REAL, часто невозможно найти число с точным десятичным значением. Например, если вы вводите число 0.43, которое должно представлять сумму $0.43, MySQL и mSQL могут записать его как 0.42999998. Это небольшое отличие может вызвать проблемы при совершении большого числа математических операций. Сохраняя число как INT и устанавливая десятичную точку в нужное место, можно быть уверенным, что его значение представляет именно то, что вам требуется.

К чему такие хлопоты? Не лучше ли было бы, если бы MySQL и mSQL обеспечивали некий тип данных, специально предназначенный для денежных сумм? MySQL и в меньшей степени mSQL предоставляют специальные типы данных для таких случаев. Одним из них является тип MONEY, другим- DATE. Полное описание всех типов данных можно найти в главе 17 «Программы и утилиты для MySQL и mSQL».

Индексы

Хотя MySQL и mSQL обеспечивают более высокую производительность, чем любые большие серверы баз данных, некоторые задачи все же требуют осторожности при проектировании базы данных. Например, если таблица содержит миллионы строк, поиск нужной строки в ней наверняка потребует длительного времени. Как указывалось в главе 2, в большинстве баз данных поиск облегчается благодаря средству, называемому индексом.

Индексы способствуют хранению данных в базе таким образом, который позволяет осуществлять быстрый поиск. К несчастью, ради скорости поиска приходится жертвовать дисковым пространством и скоростью изменения данных. Наиболее эффективно создавать индексы для тех колонок, в которых вы чаще всего собираетесь осуществлять поиск. MySQL и mSQL поддерживают одинаковый синтаксис для создания индексов:

CREATE INDEX index_name ON tablename (column1,

column2,

columnN)

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

CREATE TABLE materials (id INT NOT NULL,

name CHAR(50) NOT NULL,

resistance INT,

melting_pt REAL,

INDEX indexl (id, name),

UNIQUE INDEX index2 (name))

В этом примере для таблицы создается два индекса. Первый индекс indexl состоит из полей id и name. Второй индекс включает в себя только поле name и указывает, что значения поля name должны быть уникальными. Если вы попытаетесь вставить в поле name значение, которое уже есть в этом поле в какой-либо строке, операция не будет осуществлена. Все поля, указанные в уникальном индексе, должны быть объявлены как NOT NULL .

Хотя мы создали отдельный индекс для поля name, отдельно для поля id мы не создавали индекса. Если такой индекс нам понадобится, создавать его не нужно - он уже есть. Когда индекс содержит более одной колонки (например, name, rank, nserial_number), MySQL читает колонки в порядке слева направо. Благодаря используемой MySQL структуре индекса всякое подмножество колонок с левого края автоматически становится индексом внутри «главного» индекса. Например, когда вы создаете индекс name, rank, serial_number, создаются также «свободные» индексы name и name вместе с rank. Однако индексы rank или name и seri-al_number не создаются, если не потребовать этого явно.

MySQL поддерживает также семантику ANSI SQL для особого индекса, называемого первичным ключом. В MySQL первичный ключ - это уникальный индекс с именем PRIMARY. Назначив при создании таблицы колонку первичным ключом, вы делаете ее уникальным индексом, который будет поддерживать объединения таблиц. В следующем примере создается таблица cities с первичным ключом id.

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY,

name VARCHAR(100),

pop MEDIUMINT,

founded DATE)

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

Последовательности и автоинкрементирование

Лучше всего, когда первичный ключ не имеет в таблице никакого иного значения, кроме значения первичного ключа. Для достижения этого лучшим способом является создание числового первичного ключа, значение которого увеличивается при добавлении в таблицу новой строки. Если вернуться к примеру с таблицей cities, то первый введенный вами город должен иметь id, равный 1, второй - 2, третий - 3, и т. д. Чтобы успешно управлять такой последовательностью первичных ключей, нужно иметь какое-то средство, гарантирующее, что в данный конкретный момент только один клиент может прочесть число и увеличить его на единицу. В базе данных с транзакциями можно создать таблицу, скажем, с именем sequence , содержащую число, представляющее очередной id. Когда необходимо добавить новую строку в таблицу, вы читаете число из этой таблицы и вставляете число на единицу большее. Чтобы эта схема работала, нужно быть уверенным, что никто другой не сможет произвести чтение из таблицы, пока вы не ввели новое число. В противном случае два клиента могут прочесть одно и то же значение и попытаться использовать его в качестве значения первичного ключа в одной и той же таблице.

Ни MySQL, ни mSQL не поддерживают транзакции, поэтому описанный механизм нельзя использовать для генерации уникальных чисел. Использовать для этих целей команду MySQL LOCK TABLE обременительно. Тем не менее обе СУБД предоставляют свои варианты понятия последовательности, позволяющие генерировать уникальные идентификаторы, не беспокоясь о транзакциях.

Последовательности в MySQL

При создании таблицы в MySQL можно одну из колонок специфицировать как AUTO_INCREMENT . В этом случае, при добавлении новой строки, имеющей значение NULL или 0 в данной колонке, автоматически будет происходить замена на значение на единицу больше, чем наибольшее текущее значение в колонке. Колонка с модификатором AUTO_INCREMENT должна быть индексирована. Ниже приведен пример использования поля типа AUTOJNCREMENT :

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100),

pop MEDIUMINT,

founded DATE)

Когда вы первый раз добавляете строку, поле id получает значение 1, если в команде INSERT для него используется значение NULL или 0. Например, следующая команда использует возможность AUTO_INCREMENT:

INSERT INTO cities (id, name, pop)

VALUES (NULL, 'Houston', 3000000)

Если вы выполните эту команду, когда в таблице нет строк, поле id получит значение 1, а не NULL. В случае, когда в таблице уже есть строки, полю будет присвоено значение на 1 большее, чем наибольшее значение id в данный момент.

Другим способом реализации последовательностей является использование значения, возвращаемого функцией LAST_INSERT_ID :

UPDATE table SET id=LAST_INSERT_ID (id+1);

Последовательности в mSQL

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

CREATE SEQUENCE ON table_name [VALUE start STEP incr]

Начальное значение задается числом start, а шаг увеличения при каждом последующем обращении - числом incr. По умолчанию, последовательность начинается с 1 и каждый раз возрастает на 1. Например:

CREATE SEQUENCE ON mytable VALUE 100 STEP 5

Создается последовательность в таблице mytable, начальным значением которой будет 100, а при каждом обращении оно будет увеличиваться на 5. Вторым значением, следовательно, будет 105.

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

SELECT _seq FROM table_name

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

Управление данными

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

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

Добавление данных в таблицу является одной из наиболее простых операций SQL. Несколько примеров этого вы уже видели. Как MySQL, так и mSQL поддерживают стандартный синтаксис INSERT:

INSERT INTO table_name (columnl, column2, ..., columnN)

VALUES (value!, value2, .... valueN)

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

INSERT INTO addresses (name, address, city, state, phone, age)

VALUES( 'Irving Forbush', ' 123 Mockingbird Lane', 'Corbin', 'KY', '(800) 555-1234', 26)

Кроме того, управляющий символ - по умолчанию '\' — позволяет вводить в литералы одиночные кавычки и сам символ '\':

# Ввести данные в каталог Stacie's Directory, который находится

# в c:\Personal\Stacie

INSERT INTO files (description, location)

VALUES ('Stacie\'s Directory', 'C: \\Personal\\Stacie')

MySQL позволяет опустить названия колонок, если значения задаются для всех колонок и в том порядке, в котором они были указаны при создании таблицы командой CREATE. Однако если вы хотите использовать значения по умолчанию, нужно задать имена тех колонок, в которые вы вводите значения, отличные от установленных по умолчанию. Если для колонки не установлено значение по умолчанию и она определена как NOT NULL , необходимо включить эту колонку в команду INSERT со значением, отличным от NULL. В mSQL значение по умолчанию всегда NULL. MySQL позволяет указать значение по умолчанию при создании таблицы в команде CREATE.

Более новые версии MySQL поддерживают нестандартный вызов INSERT для одновременной вставки сразу нескольких строк:

INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39),

(HULL, 'Bananas', 122, 0, 4, 29), (NULL, 'Liver', 232, 3, 15, 10)

Хотя поддерживаемый MySQL нестандартный синтаксис удобно использовать для быстрого выполнения задач администрирования, не следует без крайней нужды пользоваться им при написании приложений. Как правило, следует придерживаться стандарта ANSI SQL2 настолько близко, насколько MySQL и mSQL это позволяют. Благодаря этому вы получаете возможность перейти в будущем на какую-нибудь другую базу данных. Переносимость особенно важна для тех, у кого потребности среднего масштаба, поскольку такие пользователи обычно предполагают когда-нибудь перейти на полномасштабную базу данных.

MySQL поддерживает синтаксис SQL2, позволяющий вводить в таблицу результаты запроса SELECT:

INSERT INTO foods (name, fat)

SELECT food_name, fat_grams FROM recipes

Обратите внимание, что число колонок в INSERT соответствует числу колонок в SELECT. Кроме того, типы данных колонок в INSERT должны совпадать с типами данных в соответствующих колонках SELECT. И, наконец, предложение SELECT внутри команды INSERT не должно содержать модификатора ORDER BY и не может производить выборку-из той же таблицы, в которую вставляются данные командой INSERT.

Изменение данных

Добавление новых строк в базу данных - лишь начало ее использования. Если ваша база не является базой данных «только для чтения», вам, вероятно, понадобится периодически изменять данные. Стандартная команда SQL для изменения данных выглядит так:

UPDATE table_name

SET column1=value1, column2=value2, ..., columnN=valueN

[WHERE clause]

В mSQL значение, присваиваемое колонке, должно быть литералом и иметь тот же тип, что и колонка. MySQL, напротив, позволяет вычислять присваиваемое значение. Можно даже вычислять значение, используя значение другой колонки:

UPDATE years

SET end_year - begin_year+5

В этой команде значение колонки end_year устанавливается равным значению колонки begin_year плюс 5 для каждой строки таблицы.

Предложение WHERE

Возможно, вы уже обратили внимание на предложение WHERE. В SQL предложение WHERE позволяет отобрать строки таблицы с заданным значением в указанной колонке, например:

UPDATE bands

SET lead_singer = 'Ian Anderson'

WHERE band_name = 'Jethro Tull'

Эта команда — UPDATE - указывает, что нужно изменить значение в колонке lead_singer для тех строк, в которых band_name совпадает с «Jethro Tull.» Если рассматриваемая колонка не является уникальным индексом, предложение WHERE может соответствовать нескольким строкам. Многие команды SQL используют предложение WHERE, чтобы отобрать строки, над которыми нужно совершить операции. Поскольку по колонкам, участвующим в предложении WHERE, осуществляется поиск, следует иметь индексы по тем их комбинациям, которые обычно используются.

Удаление

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

DELETE FROM table_name [WHERE clause]

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

Запросы

Самая часто используемая команда SQL - та, которая позволяет просматривать данные в базе: SELECT. Ввод и изменение данных производятся лишь от случая к случаю, и большинство баз данных в основном занято тем, что предоставляет данные для чтения. Общий вид команды SELECT следующий:

SELECT column1, column2, ..., columnN

FROM table1, table2, .... tableN

[WHERE clause]

Этот синтаксис, несомненно, чаще всего используется для извлечения данных из базы, поддерживающей SQL. Конечно, существуют разные варианты для выполнения сложных и мощных запросов, особенно в MySQL. Мы полностью осветим синтаксис SELECT в главе 15.

В первой части команды SELECT перечисляются колонки, которые вы хотите извлечь. Можно задать «*», чтобы указать, что вы хотите извлечь все колонки. В предложении FROM указываются таблицы, в которых находятся эти колонки. Предложение WHERE указывает, какие именно строки должны использоваться, и позволяет определить, каким образом должны объединяться две таблицы.

Объединения

Объединения вносят «реляционность» в реляционные базы данных. Именно объединение позволяет сопоставить строке одной таблицы строку другой. Основным видом объединения является то, что иногда называют внутренним объединением. Объединение таблиц заключается в приравнивании колонок двух таблиц:

SELECT book, title, author, name

FROM author, book

WHERE book, author = author, id

Рассмотрим базу данных, в которой таблица book имеет вид, как в таблице 6-3.

Таблица 6-3. Таблица книг

А таблица авторов author имеет вид таблицы 6-4.

Таблица 6-4. Таблица авторов

В результате внутреннего объединения создается таблица, в которой объединяются поля обеих таблиц для строк, удовлетворяющих запросу в обеих таблицах. В нашем примере запрос указывает, что поле author в таблице book должно совпадать с полем id таблицы author. Результат выполнения этого запроса представлен в таблице 6-5.

Таблица 6-5. Результаты запроса с внутренним объединением

В этих результатах нет автора с именем Neil Gaiman, поскольку его author, id не найден в таблице book, author. Внутреннее объединение содержит только те строки, которые точно соответствуют запросу. Позднее в этой главе мы обсудим понятие внешнего объединения, которое оказывается полезным в случае, когда в базу данных внесен писатель, у которого нет в этой базе книг.

Псевдонимы

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

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

# Псевдоним колонки

SELECT long_field_names_are_annoying AS myfield

FROM table_name

WHERE myfield = 'Joe'

# Псевдоним таблицы в MySQL

SELECT people.names, tests.score

FROM tests, really_long_people_table_name AS people

# Псевдоним таблицы в mSQL

SELECT people.names, tests.score

FROM tests, really_long_people_table_name=people

mSQL полностью поддерживает псевдонимы для таблиц, но не поддерживает псевдонимы для колонок.

Группировка и упорядочение

По умолчанию порядок, в котором появляются результаты выборки, не определен. К счастью, SQL предоставляет некоторые средства наведения порядка в этой случайной последовательности. Первое средство -упорядочение - есть и в MySQL, и в mSQL. Вы можете потребовать от базы данных, чтобы выводимые результаты были упорядочены по некоторой колонке. Например, если вы укажете, что запрос должен упорядочить результаты по полю last_name , то результаты будут выведены в алфавитном порядке по значению поля last_name . Упорядочение осуществляется с помощью предложения ORDER BY:

SELECT last_name, first_name, age

FROM people

ORDER BY last_name, first_name

В данном случае упорядочение производится по двум колонкам. Можно проводить упорядочение по любому числу колонок, но все они должны быть указаны в предложении SELECT. Если бы в предыдущем примере мы не выбрали поле last_name , то не смогли бы упорядочить по нему.

Группировка — это средство ANSI SQL, реализованное в MySQL, но не в mSQL. Поскольку в mSQL нет агрегатных функций, то группировка просто не имеет смысла. Как и предполагает название, группировка позволяет объединять в одну строки с аналогичными значениями с целью их совместной обработки. Обычно это делается для применения к результатам агрегатных функций. О функциях мы поговорим несколько позднее.

Рассмотрим пример:

mysql> SELECT name, rank, salary FROM people\g

5 rows in set (0.01 sec)

После группировки по званию (rank) выдача изменяется:

mysql> SELECT rank FROM people GROUP BY rank\g

3 rows in set (0.01 sec)

После применения группировки можно, наконец, найти среднюю зарплату (salary) для каждого звания. О функциях, используемых в этом примере, мы поговорим позднее.

mysql> SELECT rank, AVG(salary) FROM people GROUP BY rank\g

3 rows in set (0.04 sec)

Мощь упорядочения и группировки в сочетании с использованием функций SQL позволяет производить большой объем обработки данных на сервере до их извлечения. Но этой мощью нужно пользоваться с большой осторожностью. Хотя может показаться, что перенос максимального объема обработки на сервер базы данных дает выигрыш в производительности, на самом деле это не так. Ваше приложение-клиент обслуживает потребности отдельного клиента, в то время как сервер совместно используется многими клиентами. Из-за большого объема работы, который должен производить сервер, почти всегда более эффективно возложить на сервер минимально возможную нагрузку. MySQL и mSQL, возможно, наиболее быстрые из имеющихся баз данных, но не нужно использовать эту скорость для той работы, к которой лучше приспособлено клиентское приложение.

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

Расширения языка

Как MySQL, так и mSQL обладают некоторыми витиеватыми расширениями, аналогов которым вы не найдете в других базах данных. Большинство расширений, имеющихся в MySQL, в целом согласуется со стандартом ANSI SQL. Расширения mSQL связаны просто с особыми переменными, к которым можно обращаться при работе с базой данных mSQL.

Возможности MySQL

MySQL превосходит mSQL в поддержке SQL, предоставляя возможность работы с функциями и в некоторой мере — с внешними объединениями. Функции в SQL аналогичны функциям в других языках программирования, таких как С и Perl. Функция может принимать аргументы и возвращает некоторое значение. Например, функция SQRT(16) возвращает 4. В MySQL в команде SELECT функции могут использоваться в двух местах:

Как извлекаемая величина

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

# Выбрать название каждого события (event), а также его дату

# в удобном для чтения формате из всех событий, более свежих,

# чем указанная дата. Функция FROM_UnixTIME()

# преобразует стандартное значение времени Unix

# в читаемый вид.

SELECT name, FROM_UnixTIME(date)

FROM events

WHERE time > 90534323

# Выбрать заглавие статьи, полный текст ее,

# и длину (в байтах) полного текста для всех

# статей, автор которых Stacie Sheldon.

# Функция LENGTHO возвращает длину заданной

# строки в символах.

SELECT title, text, LENGTH(text)

FROM papers

WHERE author = 'Stacie Sheldon'

Как часть предложения WHERE

В этом виде функция заменяет место константы при вычислении в предложении WHERE. Значение функции используется при сравнении в каждой строке таблицы. Приведем пример.

# Случайным образом выбрать название объекта из общего числа 35.

# Функция RAND() генерирует случайное число

# между 0 и 1 (умножается на 34, чтобы сделать его между 0

# и 34, и увеличивается на 1 , чтобы сделать его между 1 и

# 35). Функция ROUND() возвращает данное число округленным

# до ближайшего целого, что приводит к целому числу

# между 1 и 35, которое должно соответствовать одному

# из чисел ID в таблице.

SELECT name

FROM entries

WHERE id = ROUND( (RAND()*34) + 1 )

# Можно использовать функции одновременно в списке значений

# и предложении WHERE. В этом примере выбираются имя и дата

# всех событий, происшедших более суток назад. Функция UNIX_TIMESTAMP()

# без аргументов возвращает текущее время

# в формате Unix.

SELECT name, FROM_UnixTIME(date)

FROM events

WHERE time > (Unix_TIMESTAMP() - (60 * 60 * 24) )

# Функция может использовать значение поля таблицы.

# В этом примере возвращаются имена всех,

# кто использовал свое имя в качестве пароля. Функция ENCRYPTO

# возвращает зашифрованную в стиле пароля Unix

# заданную строку, используя 2-символьный ключ.

# Функция LEFT() возвращает п самых левых символов

# переданной строки.

SELECT name

FROM people

WHERE password = ENCRYPT(name, LEFT(name, 2))

Наконец, MySQL поддерживает более сильный тип объединения, чем простое внутреннее объединение, которое мы до сих пор использовали. Именно, MySQL поддерживает так называемое левое внешнее объединение (известное также просто как внешнее объединение). Объединение этого типа похоже на внутреннее объединение, за исключением того, что в него включаются данные из левой колонки, которым нет соответствия в правой колонке. Если вы обратитесь к нашим таблицам с авторами и книгами, то вспомните, что в наше объединение не вошли авторы, у которых в базе данных не было книг. Часто вы можете пожелать вывести записи из одной таблицы, для которых нет соответствия в другой таблице, с которой производится объединение. Это можно сделать с помощью внешнего объединения:

SELECT book.title, author.name

FROM author

LEFT JOIN book ON book.author = author.id

Обратите внимание, что во внешнем объединении вместо WHERE используется ключевое слово ON. Результат нашего запроса будет выглядеть так:

MySQL делает следующий шаг, позволяя использовать естественное внешнее объединение (natural outer join). Естественное внешнее объединение соединяет строки двух таблиц, в которых две колонки имеют одинаковые имена и тип, и значения в этих колонках совпадают:

SELECT my_prod.name

FROM my_prod

NATURAL LEFT JOIN their_prod

Особенности mSQL

В mSQL есть пять «системных переменных», которые можно включить в любой запрос. Об одной из этих переменных, _seq, мы уже говорили. Остальные переменные следующие:

_rowid

Уникальный идентификатор возвращенной строки данных. Для повышения производительности можно использовать эту переменную в командах UPDATE или DELETE. Однако такой подход нельзя рекомендовать определенно, поскольку разные клиенты могут помешать друг другу. Например, два клиента могут выбрать одну и ту же строку. Первый клиент удаляет ее, а затем третий клиент добавляет новую строку. Новая строка может получить то же значение _rowid, что и удаленная строка. Если теперь второй клиент попытается отредактировать или удалить строку, используя данное значение _rowid, то результат будет совсем не тот, на который он рассчитывал.

_timestamp

Время последней модификации строки. В текущей версии mSQL имеет стандартный формат времени Unix. В будущих версиях формат может измениться, поэтому использовать эту переменную следует только для сравнения временных меток разных строк.

_sysdate

Возвращает значение текущей даты на сервере mSQL. Может использоваться для синхронизации времени в базе данных, даже если у клиентов на машинах стоит разное время. Имеет стандартный формат Unix.

_user

Содержит имя клиента текущего соединения. Как и _-sysdate, не зависит от таблицы, из которой выбирается.