Язык SQL
Для чтения и записи в базах данных MySQL используется структурированный язык запросов (SQL). Используя SQL, можно осуществлять поиск, вводить новые данные или удалять данные. SQL является просто основополагающим инструментом, необходимым для взаимодействия с MySQL. Даже если для доступа к базе данных вы пользуетесь каким-то приложением или графическим интерфейсом пользователя, где-то в глубине это приложение генерирует SQL-команды.
SQL является разновидностью «естественного языка». Иными словами, команда SQL должна читаться, по крайней мере на первый взгляд, как предложение английского языка. У такого подхода есть как преимущества, так и недостатки, но факт заключается в том, что этот язык очень непохож на традиционные языки программирования, такие как С, Java или Perl. Здесь мы рассмотрим язык SQL, как он реализован в MySQL.
Основы 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 был легким в изучении, но мощным языком. Он напоминает естественный язык, такой как английский, и поэтому не утомляет тех, кто не является техническим специалистом.
SQL действительно был настолько популярен среди пользователей, для которых предназначался, что в 1980-х компания Oracle выпустила первую в мире общедоступную коммерческую SQL-систему. Oracle SQL был хитом сезона и породил вокруг SQL целую индустрию. Sybase, Informix, Microsoft и ряд других компаний вышли на рынок с собственными разработками реляционных систем управления базами данных (РСУБД), основанных на SQL.
В то время когда Oracle и ее конкуренты вышли на сцену, SQL был новинкой, и для него не существовало стандартов. Лишь в 1989 году комиссия по стандартам ANSI выпустила первый общедоступный стандарт SQL. Сегодня его называют SQL89. К несчастью, этот новый стандарт не слишком углублялся в определение технической структуры языка. Поэтому, хотя различные коммерческие реализации языка SQL сближались, различия в синтаксисе делали задачу перехода с одной реализации языка на другую нетривиальной. Только в 1992 году стандарт ANSI SQL вступил в свои права.
Стандарт 1992 года обозначают как SQL92 или SQL2. Стандарт SQL2 включил в себя максимально возможное количество расширений, добавленных в коммерческих реализациях языка. Большинство инструментов, работающих с различными базами данных, основывается на SQL2 как на способе взаимодействия с реляционными базами данных. Однако, из-за очень большой широты стандарта SQL2, реляционные базы, реализующие полный стандарт, очень сложные и ресурсоемкие.
SQL2 - не последнее слово в стандартах SQL. В связи с ростом популярности объектно-ориентированных СУБД (ООСУБД) и объектно-реляционных СУБД (ОРСУБД) возрастает давление с целью принятия объектно-ориентированного доступа к базам данных в качестве стандарта SQL. Ответом на эту проблему должен послужить SQL3. Он не является пока официальным стандартом, но в настоящее время вполне определился и может стать официальным стандартом.
С появлением MySQL проявился новый подход к разработке серверов баз данных. Вместо создания очередной гигантской РСУБД с риском не предложить ничего нового в сравнении с «большими парнями», были предложены небольшие и быстрые реализации наиболее часто используемых функций 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, нечувствительны к регистру: неважно, в каком регистре вы вводите ключевые слова SQL, если орфография верна. Например, CREATE из верхнего примера можно записать и так: cREatE ТАblЕ people (name cHaR(10))
Нечувствительность к регистру относится только к ключевым словам SQL. В MySQL имена баз данных, таблиц и колонок к регистру чувствительны. Но это характерно не для всех СУБД. Поэтому, если вы пишете приложение, которое должно работать с любыми СУБД, не следует использовать имена, различающиеся одним только регистром.
Первый элемент SQL-запроса - всегда глагол. Глагол выражает действие, которое должно выполнить ядро базы данных. Хотя остальная часть команды зависит от глагола, она всегда следует общему формату: указывается имя объекта, над которым осуществляется действие, а затем описываются используемые при действии данные. Например, в запросе CREATE TABLE people (char(10)) используется глагол CREATE, за которым следует дополнение (объект) TABLE. Оставшаяся часть запроса описывает таблицу, которую нужно создать.
SQL-запрос исходит от клиента - приложения, с помощью которого пользователь взаимодействует с базой данных. Клиент составляет запрос, основываясь на действиях пользователя, и посылает его серверу SQL. После этого сервер должен обработать запрос и выполнить указанные действия. Сделав свою работу, сервер возвращает клиенту одно или несколько значений.
Поскольку основная задача SQL - сообщить серверу баз данных о том, какие действия необходимо выполнить, он не обладает гибкостью языка общего назначения. Большинство функций SQL связано с вводом и выводом из базы: добавление, изменение, удаление и чтение данных. SQL предоставляет и другие возможности, но всегда с оглядкой на то, как они могут использоваться для манипулирования данными в базе.
Типы данных в SQL
Каждая колонка таблицы имеет тип. Типы данных SQL сходны с типами данных традиционных языков программирования. В то время как во многих языках определен самый минимум типов, необходимых для работы, в SQL для удобства пользователей определены дополнительные типы, такие как MONEY и DATE. Данные типа MONEY можно было бы хранить и как один из основных числовых типов данных, однако использование типа, специально учитывающего особенности денежных расчетов, повышает легкость использования SQL.
Таблица. Наиболее употребительные типы данных, поддерживаемые MySQL.
| Тип данных | Описание |
| INT | Целое число, может быть со знаком или без знака. |
| REAL | Число с плавающей запятой. Этот тип допускает больший диапазон значений, чем INT, но не обладает его точностью. |
| CHAR(length) | Символьная величина фиксированной длины. Поля типа CHAR не могут содержать строки длины большей, чем указанное значение. Поля меньшей длины дополняются пробелами. |
| TEXT(length) | Символьная величина переменной длины. TEXT - лишь один из нескольких типов данных переменного размера. |
| DATE | Стандартное значение даты. |
| TIME | Стандартное значение времени. Этот тип используется для хранения времени дня безотносительно какой-либо даты. При использовании вместе с датой позволяет хранить конкретную дату и время. Есть дополнительный тип DATETIME для совместного хранения даты и времени в одном поле. |
MySQL поддерживает атрибут UNSIGNED для всех числовых типов. Этот модификатор позволяет вводить в колонку только положительные (беззнаковые) числа. Беззнаковые поля имеют верхний предел значений вдвое больший, чем у соответствующих знаковых типов. Беззнаковый TINYINT - однобайтовый числовой тип MySQL - имеет диапазон от 0 до 255, а не от -127 до 127, как у своего знакового аналога.
MySQL имеет больше типов, чем перечислено выше. Однако на практике в основном используются перечисленные типы. Размер данных, которые вы собираетесь хранить, играет гораздо большую роль при разработке таблиц MySQL.
Управление данными
Первое, что вы делаете, создав таблицу, это добавляете в нее данные. Если данные уже есть, может возникнуть необходимость изменить или удалить их.
Добавление данных.
Добавление данных в таблицу является одной из наиболее простых операций SQL. Несколько примеров этого вы уже видели. MySQL поддерживает стандартный синтаксис INSERT:
INSERT INTO table_name (column1, column2, ..., columnN) VALUES (value1, 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. MySQL позволяет указать значение по умолчанию при создании таблицы в команде CREATE.
Новые версии MySQL поддерживают INSERT для одновременной вставки нескольких строк:
INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39), (NULL, 'Bananas', 122, 0, 4, 29), (NULL, 'Liver', 232, 3, 15. 10)
Хотя поддерживаемый MySQL нестандартный синтаксис удобно использовать для быстрого выполнения задач администрирования, не следует без крайней нужды пользоваться им при написании приложений. Как правило, следует придерживаться стандарта ANSI SQL2 настолько близко, насколько MySQL это позволяет. Благодаря этому вы получаете возможность перейти в будущем на какую-нибудь другую базу данных. Переносимость особенно важна для тех, у кого потребности среднего масштаба, поскольку такие пользователи предполагают когда-нибудь перейти на полномасштабную базу данных.
MySQL поддерживает синтаксис SQL2, позволяющий вводить в таблицу результаты запроса:
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]
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.
Возможно, вы уже обратили внимание на предложение 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 опущено, то из таблицы будут удалены все записи!
Функции в MySQL
MySQL предоставляет возможность работы с функциями. Функции в SQL аналогичны функциям в других языках программирования, таких как С и Perl. Функция может принимать аргументы и возвращает некоторое значение. В MySQL в команде SELECT функции могут использоваться в двух местах:
Как извлекаемая величина
В этом случае функция включается в список извлекаемых колонок. Возвращаемое функцией значение, вычисляемое для каждой выбранной строки, включается в возвращаемое результирующее множество, как если бы это была колонка базы данных. Вот пример:
# Функция FROM_UnixTIME() # преобразует стандартное значение времени Unix в читаемый вид. SELECT name, FROM_UnixTIME(date) FROM events # Функция LENGTH() возвращает длину заданной строки в символах. SELECT title, text, LENGTH(text) FROM papers WHERE author = 'Stacie Sheldon'
Как часть предложения WHERE
В этом виде функция заменяет место константы при вычислении в предложении WHERE. Значение функции используется при сравнении в каждой строке таблицы. Приведем пример.
# Функция RAND() генерирует случайное число # между 0 и 1 (умножается на 34, чтобы сделать его между О # и 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)) # Функция ENCRYPT() # возвращает зашифрованную в стиле пароля Unix # заданную строку, используя 2-символьный ключ. # Функция LEFT() возвращает n левых символов переданной строки. SELECT name FROM people WHERE password = ENCRYPT(name, LEFT(name, 2))
Некоторые из функций могут вернуть значение как число или как строку, в зависимости от того, какой формат необходим пользователю. Эта возможность называется «контекстом» функции. Когда выбранные значения выводятся на дисплей, используется только текстовой контекст, но при вводе выбранных данных в поля таблиц или при использовании их в качестве аргументов других функций контекст зависит от того, что ожидается получателем данных. В частности, когда данные выбраны для их последующего ввода в поля числового типа, контекст функции будет числовым.
Кроме того, имеются агрегатные функции, выполняемые над набором данных. Обычно этот метод используется для выполнения некоторого действия над всем набором возвращаемых данных. Например, функция SELECT AVG(height) FROM kids возвращала бы среднее от значений поля height в таблице kids.
