Лекция 9. Структура языка SQL. Типы данных. Язык для взаимодействия с БД SQL появился в середине 70-х и был разработан в рамках проекта экспериментальной реляционной СУБД System R. Исходное название языка SEQUEL (Structered English Query Language) только частично отражает суть этого языка. Конечно, язык был ориентирован главным образом на удобную и понятную пользователям формулировку запросов к реляционной БД, но на самом деле уже являлся полным языком БД, содержащим помимо операторов формулирования запросов и манипулирования БД средства определения и манипулирования схемой БД; определения ограничений целостности и триггеров; представлений БД; возможности определения структур физического уровня, поддерживающих эффективное выполнение запросов; авторизации доступа к отношениям и их полям; точек сохранения транзакции и откатов. В языке отсутствовали средства синхронизации доступа к объектам БД со стороны параллельно выполняемых транзакций: с самого начала предполагалось, что необходимую синхронизацию неявно выполняет СУБД.

Структурированный язык запросов SQL позволяет выполнять операции: -над таблицами (создание, удаление, изменение структуры) -над данными таблиц (выборка, изменение, добавление и удаление) - а также производить некоторые сопутствующие операции. SQL основан на реляционном языке исчисления кортежей и является непроцедурным языком, т. е. не содержит операторов управления, организации подпрограмм, ввода-вывода и т. п. В связи с этим SQL автономно не используется, обычно он погружен в среду встроенного языка программирования СУБД. В различных СУБД состав языка может несколько меняться, мы будем придерживаться принятого в MySQL 5.0.

Состав SQL Выделяют 5 составляющих языка: 1) Data Definition Language (DDL – язык описания данных) состоит из команд, создающих объекты БД: таблицы, поля, первичные и вторичные ключи, индексы и т.д. 2) Data Manipulation Language (DML – язык манипулирования данными) состоит из команд, позволяющих изменять информацию в БД – добавлять, удалять, изменять. 3) Data Control Language (DCL – язык управления данными) – команды, определяющие права доступа на получение и модификацию информации в БД, проведение транзакций. 4) Transaction Control Language (TCL – язык управления транзакциями) – команды предназначены для управления транзакциями. 5) Data Retrieval Language (DRL – язык получения данных) – вывод данных.

Интерактивный (встроенный) и динамический SQL Выделяют два подмножества: интерактивный (ISQL) и динамический (DSQL) SQL. Первый используется в диалоговом режиме на уровне: ввод операторов – выполнение. Второй – для использования языка SQL внутри других языков программирования, называют вложенным. Встроенный SQL Основная проблема встраивания SQL в язык программирования состояла в том, что SQL - реляционный язык, т.е. его операторы большей частью работают со множествами, в то время как в языках программирования основными являются скалярные операции. Решение SQL состоит в том, что в язык дополнительно включаются операторы, обеспечивающие покортежный доступ к результату запроса к БД. Для этого в язык вводится понятие курсора, с которым связывается оператор выборки. Над определенным курсором можно выполнять оператор OPEN, означающий материализацию отношения-результата запроса, оператор FETCH, позволяющий выбрать очередной кортеж результирующего отношения в память программы, и оператор CLOSE, означающий конец работы с данным курсором. Дополнительную гибкость при создании прикладных программ со встроенным SQL обеспечивает возможность параметризации операторов SQL значениями переменных включающей программы.

Динамический SQL Для упрощения создания интерактивных SQL-ориентированных систем в SQL System R были включены операторы, позволяющие во время выполнения транзакции откомпилировать и выполнить любой оператор SQL. Оператор PREPARE вызывает динамическую компиляцию оператора SQL, текст которого содержится в указанной переменной символьной строке включающей программы. Текст может быть помещен в переменную при выполнении программы любым допустимым способом, например, введен с терминала. Оператор DESCRIBE служит для получения информации об указанном операторе SQL, ранее подготовленном с помощью оператора PREPARE. C помощью этого оператора можно узнать, во-первых, является ли подготовленный оператор оператором выборки, и во- вторых, если это оператор выборки, получить полную информацию о числе и типах столбцов результирующего отношения. Для выполнения ранее подготовленного оператора SQL, не являющегося оператором выборки, служит оператор EXECUTE. Для выполнения динамически подготовленного оператора выборки используется аппарат курсоров с некоторыми отличиями по части задания адресов переменных включающей программы, в которые должны быть помещены значения столбцов текущего кортежа результата.

Операторы SQL. ВидНазваниеНазначение DDL СREAT TABLE DROP TABLE ALTER TABLE CREAT INDEX DROP INDEX CREAT VIEW DROP VIEW CREAT DOMAIN ALTER DOMAIN DROP DOMAIN RENAME Создание таблицы Удаление таблицы Изменение структуры таблицы Создание индекса Удаление индекса Создание представления Удаление представления Создание домена Изменение домена Удаление домена Переименование DRL SELECT TRANSFORM UNION Выборка записей Создание перекрестного запроса Создание запроса на объединение DML UPDATE INSERT DELET Изменение записей Вставка новых записей Удаление записей TCL START TRANSACTION COMMIT ROLLBACK Начало транзакции Успешное завершение транзакции Откат транзакции DCL GRANT REVOKE Передача привилегий Изъятие привилегий Запросы SQL представляют собой инструкции, состоящие из фраз (предложений). Инструкции называются по имени оператора, определяющего суть инструкции. Такой оператор практически всегда следует первым. Фразы, в свою очередь, также называются по имени ключевого слова, с которого они начинаются.

Запросы DDL предназначены для определения структуры данных, т. с. позволяют создавать домены, таблицы, индексы, представления, а также позволяют обеспечить целостность данных посредством задания ограничителей целостности. Запросы DRL (Data Retrieval Language) – вывод данных Основа - SELECT. Запросы DML различаются на запросы действий и на специальные запросы. Выходной набор может быть редактируемым, т. е. изменения, сделанные в выходном наборе, будут произведены и в таблицах, на которых основан запрос, если соблюдены определенные (различные для каждой СУБД) требования. Специальные запросы, к которым относятся запрос на объединение, запрос на создание перекрестной таблицы и некоторые другие, также возвращают таблицу как результат своего действия. Запросы действий, напротив, оказывают лишь некоторое действие над данными. Результат этого действия можно просмотреть, открыв соответствующие таблицы. Запросы TCL предназначены для управления транзакциями. Запросы DCL предназначены для обеспечения управления доступа к данным.

Типы данных языка. При создании таблицы нужно указать тип данных для каждого столбца. В MySQL 5.0 предусмотрено множество типов данных для хранения чисел, даты /времен и символьных строк (текстов). Кроме того, существуют типы данных для хранения пространственных (spatial) объектов. Рассмотрим числовые типы данных. BIT [( )]. Битовое число, содержащее заданное количество битов. Если количество битов не указано, число состоит из одного бита. TINYINT. Целое число в диапазоне либо от -128 до 127, либо (если указано свойствоUNSIGNED) от 0 до 255. BOOL или BOOLEAN. Являются синонимами к типу данных TINYINT (1) (число в скобках это количество отображаемых цифр, см. примечание ниже). При этом ненулевое значение рассматривается как истинное (TRUE), нулевое как ложное (FALSE).

SMALLINT. Целое число в диапазоне либо от до , либо (если указано свойство UNSIGNED) от 0 до MEDIUMINT. Целое число в диапазоне либо от до , либо (если указано свойство UNSIGNED) от 0 до INT или INTEGER. Целое число в диапазоне либо от до , либо (если указано свойство UNSIGNED) от 0 до BIGINT. Целое число в диапазоне либо от до , либо (если указано свойство UNSIGNED) от 0 до SERIAL. Синоним выражения BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE (большое целое число без знака, принимающее автоматически увеличиваемые уникальные значения; значения NULL запрещены). Используется для автоматической генерации уникальных значений в столбце первичного ключа.

ПРИМЕЧАНИЕ Для всех целочисленных типов данных, кроме BOOL и SERIAL, можно в скобках указать количество отображаемых цифр, которое используется совместно с параметром ZEROFILL: если число содержит меньшее количество цифр, то при выводе оно дополняется слева нулями. Например, если столбец таблицы определен как INT(5) ZEROFILL, то значения « » и «12345» отображаются «как есть», а значение «123» как «00123». Для типа данных BIT в скобках указывается размер числа, то есть максимальное количество хранимых битов.

FLOAT. Число с плавающей точкой в диапазоне от -3, до -1, и от 1, до 3, (а также значение 0) с точностью около 7 значащих цифр (точность зависит от возможностей вашего компьютера). DOUBLE, DOUBLE PRECISION или REAL. Число с плавающей точкой в диапазоне от - 1, до -2, и от 2, до 1, (а также значение 0) с точностью около 15 значащих цифр (точность зависит от возможностей вашего компьютера). FLOAT ( ). При значении точности от 0 до 24 этот тип данных эквивалентен типу FLOAT, при значении от 25 до 53 – типу DOUBLE. DECIMAL, DEC, NUMERIC или FIXED. Точное (неокругляемое) число с фиксированной точкой. Может содержать до 65 значащих цифр и до 30 цифр после десятичного разделителя (по умолчанию – 10 значащих цифр и 0 после десятичного разделителя).

ПРИМЕЧАНИЕ Для всех десятичных (нецелочисленных) типов данных, кроме FLOAT( ), можно в скобках указать точность и шкалу, то есть максимальное количество хранимых значащих цифр и максимальное количество хранимых цифр после десятичного разделителя. Например, если для столбца задан тип данных FLOAT (7,5), это означает, что в столбец нельзя добавить значение с более чем двумя (2 = 7-5) цифрами в целой части и все введенные значения будут округляться до 5 знаков после десятичного разделителя. Для чисел с плавающей точкой можно указать точность до 255 и шкалу до 30, однако указывать слишком большую точность и шкалу не имеет смысла, так как в базе данных сохраняются приближенные значения, которые совпадают с реальными лишь в первых 7 (для типа FLOAT) или 15 (для типа DOUBLE) значащих цифрах, последующие цифры при сохранении могут быть искажены. Для чисел с фиксированной точкой можно указать точность до 65 и шкалу до 30. Если точность и шкала не указаны, то они равны, соответственно, 10 и 0. При сохранении чисел с фиксированной точкой искажений не происходит.

Завершая рассмотрение числовых типов данных, обсудим три свойства, которые можно указать для числовых столбцов: UNSIGNED – данное свойство означает, что в столбце запрещены отрицательные (со знаком «-») значения. Указывать это свойство можно для любых столбцом с числовым типом данных, кроме BIT, BOOL и SERIAL. Для целочисленных столбцов при добавлении свойства UNSIGNED максимально допустимое значение столбца увеличивается вдвое. ZEROFILL – данное свойство означает, что значения при отображении будут дополнены нулями. Целые числа дополняются нулями слева в соответствии с указанным количеством отображаемых цифр, десятичные слева и справа в соответствии с указанными точностью и шкалой. Указывать свойство ZEROFILL можно для любых столбцов с числовым типом данных, кроме BIT, BOOL и SERIAL. AUTO_INCREMENT – данное свойство обеспечивает автоматическую нумерации строк таблицы. Это означает, что при добавлении в столбец неопределенного (NULL) или нулевого значения оно автоматически заменяется следующим номером, на единицу больше предыдущего (нумерация по умолчанию начинается с единицы, установить другой начальный номер можно с помощью соответствующего свойства таблицы). Указывать это свойство можно для любых столбцов с числовым типом данных, кроме BIT и DECIMAL. В таблице может быть только один столбец с таким свойством, и для него должен быть создан ключ или индекс.

Далее рассмотрим типы данных, используемые при хранении даты и времени. Для столбца, который будет содержать дату и/или время, вы можете использовать один из следующих типов данных. DATE. Дата в формате «YYYY-MM-DD», в диапазоне от « » до « ». DATETIME. Дата и время в формате «YYYY-MM-DD HH:MM:SS» в диапазоне от « :00:00» до « :59:59». TIMESTAMP. Отметка времени в формате «YYYY-MM-DD HH:MM:SS» в диапазоне от « :00:00» до некоторой даты в 2038 г. При добавлении или изменении строки таблицы в столбце с типом TIMESTAMP автоматически устанавливается дата и время выполнения операции (если значение этого столбца не указано явно или указано неопределенное значение). Если нужно, чтобы отметка времени проставлялась только при добавлении строки, после слова TIMESTAMP добавим СВОЙСТВО DEFAULT CURRENT_ TIMESTAMP.

TIME. Время в формате «HH:MM:SS» в диапазоне от «-838:59:59» до «838:59:59». YEAR, YEAR (2), YEAR (4). Год в формате «YYYY» или «YY» (если количество цифр не указано, используется формат «YYYY»). Диапазон значений от 1901 до 2155, если используется формат «YYYY», или от 70 (соответствует 1970 г.) до 69 (соответствует 2069 г.), если используется формат «YY». Отметим, что MySQL воспринимает даты не только в указанном выше формате. Вы можете ввести дату с любым знаком препинания в качестве разделителя, например 23%59%59, или без разделителя, например Более того, если в столбец с типом даты или времени вносится символьное или Числовое значение в одном из таких форматов, MySQL автоматически преобразует это значение в дату и/или время.

Завершая изучение типов данных, рассмотрим символьные типы. Столбцам, которые будут содержать текст, можно присвоить один из следующих типов данных. CHAR ( ) или NATIONAL CHAR ( ). Символьная строка фиксированной длины. В таком столбце всегда хранится указанное количество символов, при необходимости значение дополняется справа пробелами. Вы можете задать, количество символов от 0 до 255. Если количество символов не задано, используется длина строки по умолчанию 1 символ. Тип данных NATIONAL CHAR отличается от CHAR тем, что для столбцом с типом NATIONAL CHAR используется кодировка UTF-8, в то время как для столбцов с типом CHAR можно указать любую кодировку, поддерживаемую MySQL. VARCHAR ( ) или NATIONAL VARCHAR ( ). Символьная строка переменной длины, содержащая не более указанного количества символов. Вы можете указать максимальное количество символов от 0 до , но не более байтов в сумме для всех столбцов таблицы с типом CHAR, VARCHAR, BINARY или VARBINARY.

BINARY ( ). Байтовая (бинарная) строка фиксированной длины. Этот тип аналогичен типу CHAR, только строка содержит не символы, а байты, и значение меньшей длины дополняется справа не пробелами, а нулевыми байтами. VARBINARY ( ). Байтовая (бинарная) строка переменной длины. Этот тип аналогичен типу VARCHAR, только строка содержит не символы, а байты. TINYBLOB. Байтовая (бинарная) строка переменной длины. Максимальная длина. 255 байтов. TINYTEXT. Символьная строка переменной длины. Максимальная длина – 255 байтов (не символов). ПРИМЕЧАНИЕ Обратите внимание, что для типов данных TINYTEXT, ТЕХТ, MEDIUMTEXT или LONGTEXT длина значения ограничена максимальным количеством байтов, а не символов.

BLOB [ ( )]. Байтовая (бинарная) строка переменной длины. Если количество байтов не указано, то значение столбца ограничено байтами. Если количество байтов указано, то создается столбец с типом данных TINYBLOB, BLOB, MEDIUMBLOB или LOHGBLOB: выбирается тип данных с наименьшим размером, достаточным для хранения этого количества байтов. ТЕХТ [( )]. Символьная строка переменной длины. Если количество символов не указано, то значение столбца ограничено байтами. Если количество символов указано, то создается столбец с типом данных TINYTEXT, ТЕХТ, MEDIUMTEXT или LONGTEXT: выбирается тип данных с наименьшим размером, достаточным для хранения этого количества символов. MEDIUMBLOB. Байтовая (бинарная) строка переменной длины. Максимальная длина байтов.

MEDIUMTEXT. Символьная строка переменной длины. Максимальная длина байтов. LOHGBLOB. Байтовая (бинарная) строка переменной длины. Максимальная длина не более байтов (4 Гбайт), в зависимости от используемого протокола взаимодействия с сервером MySQL и доступных системных ресурсов. LONGTEXT. Символьная строка переменной длины. Максимальная длина не более байтов (4 Гбайт), в зависимости от используемого протокола взаимодействия с сервером MySQL и доступных системных ресурсов. ENUM (' ',' ',...). Строка, содержащая ровно один элемент из заданного списка. Например, если столбец определен как ENUM (' а ', ' b ' ), то допустимыми значениями этого столбца являются значения а, b и NULL. В список вы можете включить до элементов.

SET (' ',' ',...). Строка, содержащая любой набор элементов из заданного списка (в том числе пустой). Например, если столбец определен как SET (' а ', 'b '), то он может содержать значения «» (пустая строка), а, b, аb и NULL. В список вы можете включить до 64 элементов. Элементы списка не должны содержать запятых. Каждый из элементов может присутствовать в значении столбца только один раз, причем элементы могут следовать только в том порядке, в котором они перечислены в списке.

В заключение отметим, что в MySQL вы можете указать кодировку отдельно для каждого символьного столбца. А именно, вы можете задать свойство CHARACTER SET и/или COLLATE. Например, чтобы имена клиентов хранились в кодировке СР-1251, тогда как кодировкой по умолчанию для таблицы Customers (Клиенты) является UTF-8, столбец name (имя) можно определить следующим образом: name VARCHAR (100) CHARACTER SET СР-1251 COLLATE СР- 1251_general_ci Если кодировка для столбца не задана, то используется кодировка, заданная для таблицы в целом. Если не задана кодировка и для таблицы, то используется кодировка, установленная для базы данных. Наконец, если и для базы данных не была указана кодировка, то используется кодировка, установленная по умолчанию при настройке MySQL.