Как и в большинстве языков программирования, в SQL существуют функции для обработки данных. Стоит отметить, что в отличие от SQL-операторов, функции не стандартизованы для всех видов СУБД, то есть для выполнения одних и тех же операции над данными, разные СУБД имеют свои собственные имена функций. Это означает, что код запроса написан в одной СУБД может не работать в другой, и это нужно учитывать в дальнейшем. Больше всего это касается функций для обработки текстовых значений, преобразования типов данных и манипуляций над датами.
Обычно СУБД поддерживается стандартный набор типов функций, а именно:
Реализация SQL в СУБД Access имеет следующие функции для обработки текста:
Переведем названия товаров в верхний регистр с помощью функции UCase() :
SELECT Product, UCase(Product) AS Product_UCase FROM Sumproduct
Выделим первые три символа в тексте с помощью функции LEFT() :
SELECT Product, LEFT(Product, 3) AS Product_LEFT FROM Sumproduct
Функции обработки чисел предназначены для выполнения математических операций над числовыми данными. Эти функции предназначены для алгебраических и геометрических вычислений, поэтому они используются значительно реже функций обработки даты и времени. Однако числовые функции наиболее стандартизированными для всех версий SQL. Давайте взглянем на перечень числовых функций:
Мы привели лишь несколько основных функций, однако вы всегда можете обратиться к документации вашей СУБД, чтобы увидеть полный перечень функций, которые поддерживаются с их подробным описанием.
Например, напишем запрос для получения корня квадратного для чисел в столбце Amount с помощью функции SQR() :
SELECT Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct
Функции манипулирования датой и временем являются одними из важнейших и часто используемых функций SQL. В базах данных значения дат и времени хранятся в специальном формате, поэтому их невозможно использовать напрямую без дополнительной обработки. Каждая СУБД имеет свой набор функций для обработки дат, что, к сожалению, не позволяет переносить их на другие платформы и реализации SQL.
Список некоторых функций для обработки даты и времени в СУБД Access :
Посмотрим на примере как работает функция DatePart() :
SELECT Date1, DatePart("m", Date1) AS Month1 FROM Sumproduct
Функция DatePart () имеет дополнительный параметр, который нам позволяет отобразить необходимую часть даты. В примере мы использовали параметр "m" , который отображает номер месяца (таким же образом мы можем отразить год - "yyyy" , квартал - "q " , день - " d " , неделю - " w " , час - " h " , минуты - "n" , секунды - "s" и т.д.).
Статистические функции помогают нам получить готовые данные без их выборки. SQL-запросы с этими функциями часто используются для анализа и создания различных отчетов. Примером таких выборок может быть: определение количества строк в таблице, получение суммы значений по определенному полю, поиск наибольшего /наименьшего или среднего значения в указанном столбце таблицы. Также отметим, что статистические функции поддерживаются всеми СУБД без особых изменений в написании.
Список статистических функций в СУБД Access :
COUNT() :
SELECT COUNT(*) AS Count1 FROM Sumproduct - возвращает количество всех строк в таблице
SELECT COUNT(Product) AS Count2 FROM Sumproduct - возвращает количество всех непустых строк в поле Product
Мы намеренно удалили одно значение в столбце Product , чтобы показать разницу в работе двух запросов.
Примеры использования функции SUM() :
SELECT SUM(Quantity) AS Sum1 FROM Sumproduct WHERE Month = "April "
Данным запросу мы отразили общее количество проданного товара в апреле.
SELECT SUM(Quantity*Amount) AS Sum2 FROM Sumproduct
Как видим, в статистических функциях мы можем осуществлять вычисления над несколькими столбцами с использованием стандартных математических операторов.
Пример использования функции MIN() :
SELECT MIN(Amount) AS Min1 FROM Sumproduct
Пример использования функции MAX() :
SELECT MAX(Amount) AS Max1 FROM Sumproduct
Пример использования функции AVG() :
SELECT AVG(Amount) AS Avg1 FROM Sumproduct
Встроенные функции SQL предназначены для облегчения и ускорения обработки данных. Особенностью является то, что они могут указываться непосредственно в выражении. Все встроенные функции можно условно разделить на группы.
Математические функции :
ABS (значение ) – возвращает абсолютное значение числа;
Round (значение, точность ) – возвращает числовое значение, округленное до указанного аргументом точность количества десятичных разрядов;
SIGN (значение ) – возвращает минус, если число отрицательное, и плюс – в противном случае;
POWER (значение, степень ) – возводит число в степень;
SQRT (значение ) – извлекает квадратный корень числа;
CEILING (значение) – возвращает ближайшее целое число большее или равное значению;
- FLOOR (значение) – возвращает ближайшее целое число меньшее или равное значению.
Строковые функции:
ASCII (строка ) – возвращает ASCII код первого символа строки;
CH A R (число )– возвращают символ по ASCII коду;
LEN (строка )– возвращает длину строки в символах, исключая конечные пробелы;
L TRIM (строка) / RTRIM (строка)- удаляет пробелы в начале/конце строки;
LEFT (строка, число) / R IGHT (строка, число) – возвращает указанное аргументом число количество символов строки, начиная с левого/правого края;
SUBSTRING (строка, позиция, длина ) – возвращает подстроку указанной длины из строки, начиная с указанной позиции;
LOWER (строка) / UPPER (строка ) – возвращает строку, преобразованную в нижний / верхний регистр и т.д.
Функции для работы с датами:
GETDATE () – возвращает значение, которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server;
DAY (значение_дата) –возвращает число из указанной даты;
MONTH (значение_дата) – возвращает номер месяца из указанной даты;
YEAR (значение_дата) – возвращает значение года из указанной даты;
DATENANE(часть, значение_дата ) – возвращает символьную строку, представляющую указанную часть (Day , Month , Hour и т.д. ) из указанной даты;
DATEPART(часть, значение_дата ) – возвращает целое число, представляющее указанную часть (Day , Month , Hour и т.д. ) из указанной даты.
Функции преобразования типов данных
CAST (значение AS тип_данных )
CONVERT (тип_данных , значение )
Аргумент значение в функциях задает величину, которую необходимо преобразовать.
Язык определения данных содержит команды, которые предназначены для создания, изменения и удаления базы данных и ее объектов.
Создание таблицы
Создание новой таблицы выполняется командой CREATE TABLE. В команде выполняется описание структуры таблицы, каждого столбца таблицы и ограничений целостности, которые должны устанавливаться для таблицы.
Синтаксис команды:
CREATE TABLE имя_таблицы ({ описание_столбца | имя_вычисляемого_столбца AS выражение | ограничения_целостности_уровня_таблицы} [, ...])
Именем таблицы является идентификатор длинной не более 128 символов.
Таблица может содержать вычисляемый столбец, тогда значение столбца определяется выражением, которое хранится в структуре таблицы. Изменять данные вычисляемого столбца нельзя, поэтому для него не могут быть установлены ограничения целостности NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY и значение DEFAULT.
Синтаксис описания столбца таблицы имеет вид:
имя_столбца тип_данных [(размер) ]
[{DEFAULT значение_по_умолчанию | IDENTITY [(значение, шаг) ]}]
[ограничения_целостности_уровня_столбца]
DEFAULT - позволяет задать значение, присваиваемое столбцу во вновь добавляемой записи.
IDENTITY – указывает, что создается столбец с поддержкой автоматической нумерации (столбец-счетчик). В таблице может быть определен только один столбец-счетчик. Параметр значение задает начальное значение счетчика, а параметр шаг – шаг приращения. Если эти параметры не заданы, то они имеют значение 1. IDENTITY может быть задано только для тех столбцов, которые имеют целочисленные или десятичные типы. Вставка значений в столбец IDENTITY запрещена.
Существует две группы ограничений целостности, обрабатываемых СУБД:
Декларативные ограничения целостности, которые объявляются при создании или изменении таблицы;
Процедурные ограничения целостности, которые обрабатываются триггерами.
Декларативные ограничения целостности могут быть ограничениями на уровне таблицы и ограничениями на уровне таблицы. Ограничения на уровне столбца применяется только к одному столбцу. Каждому декларативному ограничению целостности может быть присвоено имя.
Описание ограничений уровня столбца имеет следующий синтаксис:
{{PRIMARY KEY | UNIQUE | NOT NULL } |FOREIGN KEY REFERENCES имя_таблицы(имя_столбца )
|CHECK логическое_выражение}
Имя ограничения целостности данных должно быть уникальным в пределах базы данных. Рассмотрим ограничения, которые могут быть определены на уровне столбца:
Ограничение по первичному ключу PRIMARY KEY. Все значения первичного ключа таблицы должны быть уникальными и отличаться от значения Null. В таблице может быть только один первичный ключ. Если он является составным, то ограничения целостности по первичному ключу задаются на уровне таблицы;
Ограничения уникальности значения столбца UNIQUE. Это означает, что в таблице не может быть двух записей, имеющих одно и то же значение в этом столбце;
Ограничение NOT NULL, запрещающее хранить в столбце значение NULL;
Ограничение по внешнему ключу FOREIGN KEY (ограничение ссылочной целостности). Для столбца, который является внешним ключом, с помощью REFERENCES указывается имя таблицы, с которой устанавливается связь, и имя столбца этой таблицы, по которому будет устанавливаться связь. Такая таблица является главной (родительской) по отношению к создаваемой таблице. Для столбца главной таблицы, по значениям которого устанавливается связь, должно быть установлено ограничение PRIMARY KEY.
Если ключ внешней таблицы состоит из нескольких полей, то ограничение FOREIGN KEY должно задаваться на уровне таблицы. При этом следует перечислить все столбцы, входящие во внешний ключ, указать имя главной таблицы и имена столбцов главной таблицы, на которые ссылается внешний ключ.
Ссылочная целостность устанавливает правила добавления и изменения данных в таблице при помощи внешнего ключа и соответствующего ему ограничения первичного ключа. Предложения ON UPDATE и ON DELETE для внешнего ключа определяют следующие правила изменения связанных данных:
NO ACTION – разрешает изменять (удалять) только те значения в главной таблице, которые не имеют соответствующих значений внешнего ключа в дочерней таблице. Данное правило действует по умолчанию;
CASCADE означает, что каждое значение внешнего ключа дочерней таблицы будет автоматически изменяться (удаляться) при модификации значения первичного ключа родительской таблице;
SET NULL означает, что в случае изменения (удаления) первичного ключа родительской таблицы, во всех ссылающихся строках дочерней таблицы значениям внешнего ключа будут автоматически присвоены значения NULL;
SET DEFAULT означает, что в случае изменения (удаления) первичного ключа родительской таблицы, во всех ссылающихся строках дочерней таблицы значениям внешнего ключа будут автоматически присвоены значения по умолчанию.
Дополним пример учебной базы данных «Университет», проектирование которой рассматривалось в гл. 4.3 таблицами ДИСЦИПЛИНА и ОБЩАЯ ВЕДОМОСТЬ. В таблицах 6,7 описана логическая структура таблиц.
Таблица 6
Логическая структура информационного объекта ДИСЦИПЛИНА
Таблица 7
Логическая структура информационного объекта ОБЩАЯ ВЕДОМОСТЬ
|
Признак ключа |
Формат поля |
||||
|
Наименование |
Точность |
||||
|
Номер зачетной книжки |
Зарегистрированный номер зачетной книжки студента |
текстовый | |||
|
Код дисциплины |
Код дисциплины |
Числовой |
Длинное целое | ||
|
числовой | |||||
Приведем запросы на создание таблиц в соответствии с приведенной на рис. 35 инфологической моделью БД.
Рис. 35. Схема базы данных «Университет»
Как видно из схемы БД таблица ФАКУЛЬТЕТ является независимой таблицей, поэтому она создается первой. Запрос на создание таблицы с учетом описания логической структуры в табл. 4 (стр.61) будет иметь вид:
CREATE TABLE факультет
([номер факультета] tinyint PRIMARY KEY , [наименование факультета] char(50))
Таблица СПЕЦИАЛЬНОСТЬ также является независимой, ее создаем второй. При создании запроса использует описание логической структуры в табл. 5 (стр.62).
CREATE TABLE [специальность] (
[номер специальности] int PRIMARY KEY,
[наименование специальности] char (60),
[стоимость обучения] )
Таблица ГРУППА является зависимой от ФАКУЛЬТЕТА и СПЕЦИАЛЬНОСТИ таблицей. Используем таблицу 3 (стр. 61) при создании запроса и учтем, что столбцы номер факультета и номер специальности являются внешними ключами:
CREATE TABLE [группа] (
[номер группы] smallint PRIMARY KEY,
[номер специальности] int FOREIGN KEY REFERENCES специальность(номер специаль - ности )ON DELETE CASCADE ON UPDADE CASCADE,
[номер факультета] tinyint FOREIGN KEY REFERENCES факультет(номер факультета ) ON DELETE CASCADE ON UPDADE CASCADE, [номер курса] tinyint)
Таблица СТУДЕНТ является зависимой от ГРУППЫ таблицей. На основании данных таблицы 2 (стр. 60) составим запрос. Также учтем, что столбец номер группы является внешними ключами:
CREATE TABLE [студент] (
[номер группы] smallint NOT NULL FOREIGN KEY REFERENCES группа(номер группы ) ,
[фамилия] char(15) NOT NULL ,
[дата рождения] datetime NOT NULL ,
[коммерческий] bit NOT NULL ,
[имя регистрации] char(9))
Данные таблицы ОБЩАЯ ВЕДОМОСТЬ зависят от таблиц СТУДЕНТ и ДИСЦИПЛИНА. В этой таблице первичный ключ составной и каждый из столбцов первичного ключа является внешним ключом (см. табл. 7 и рис. 35).
Воспользуемся описанием логической структуры таблицы дисциплина, приведенной в таблице 6 и составим запрос:
CREATE TABLE [дисциплина] (
[код дисциплины] int PRIMARY KEY,
[наименование дисциплины] char(50))
Теперь можно создать запрос на создание таблицы общая ведомость. Так как первичный ключ таблицы является составным, то ограничение PRIMARY KEY должно задаваться на уровне таблицы. Для примера зададим ограничения FOREIGN KEY также на уровне таблицы. Запрос будет иметь вид:
CREATE TABLE [общая ведомость] (
[код дисциплины] int,
[номер зачетной книжки] char(8),
[оценка] NOT NULL , PRIMARY KEY ([код дисциплины],[номер зачетной книжки]), FOREIGN KEY ([код дисциплины]) REFERENCES [дисциплина] ([код дисциплины]), FOREIGN KEY ([номер зачетной книжки]) REFERENCES [студент] ([номер зачетной книжки]))
Изменение структуры таблицы
Изменение структуры таблицы выполняется командой ALTER TABLE. С помощью команды можно выполнять изменение свойств существующих столбцов, удалять их или добавлять в таблицу новые столбцы, управлять ограничениями целостности, как на уровне столбцов, так и на уровне таблицы. Назначение многих параметров и ключевых слов аналогично назначению соответствующих параметров и ключевых слов команды CREATE TABLE.
Удаление таблицы
Удаление таблицы выполняется при помощи команды DROP TABLE. Синтаксис команды:
DROP TABLE таблица
Например, запрос на удаление таблицы СТУДЕНТ имеет следующий вид:
DROP TABLE Студент
При удалении таблицы СЛЕДУЕТ учитывать связи установленные в базе данных между таблицами. Если на удаляемую таблицу с помощью ограничения целостности FOREIGN KEY ссылается другая таблица, то СУБД не разрешит ее удаление.
Создание индекса
Индексы используют для ускорения доступа к конкретным данным в таблице базы данных. Индекс является структурой, которая упорядочивает значения в одном или нескольких столбцах таблицы базы данных, например, в столбце Фамилий таблицы СТУДЕНТ. Если проводится поиск конкретного студента по фамилии, индекс помогает получить нужные сведения быстрее по сравнению с поиском по всем строкам таблицы.
Индекс обеспечивает указатели на значения данных, сохраненные в определенных столбцах таблицы, и упорядочивает эти указатели согласно заданному порядку сортировки. Поиск данных в таблице с использованием индекса аналогичен поиску в предметным указателе в книге. Сначала проводится поиск конкретного значения в предметном указателе, а затем выполняется соответствующий переход по указателю на строку, содержащую это значение.
Создание индекса выполняется командой CREATE INDEX:
CREATE INDEX
имя_ индекса ON имя_таблицы (столбец [,…])
где UNIQUE – указывает на то, что индекс должен хранить только уникальные значения.
Индекс может быть создан по одному или нескольким столбцам (составной индекс). Составные индексы позволяют различать записи, в которых в одном столбце имеются одинаковые значения.
Пример: Создать составной индекса в таблице СТУДЕНТ для полей Фамилия и Дата рождения
CREATE INDEX Ind_Fam ON
Студент(Фамилия, [Дата рождения] DESC)
Индекс для таблицы следует создавать только в том случае, если предполагается часто выполнять запросы по данным в индексированных столбцах. Индексы занимают место на диске и замедляют операции добавления, удаления и обновления строк.
Удаление индекса таблицы
Удаляет индекс из таблицы команда DROP. Синтаксис команды DROP на удаление индекса:
DROP INDEX индекс ON таблица
Перед удалением индекса из таблицы или самой таблицы ее необходимо закрыть.
Пример: Удалить индекс Ind_Fam из таблицы СТУДЕНТ
DROP INDEX Ind_Fam ON Студент
Мы продолжаем изучение языка запросов SQL, и сегодня мы с Вами будем разговаривать о строковых функциях SQL . Мы рассмотрим основные и часто используемые строковые функции, такие как: LOWER, LTRIM, REPLACE и другие, все рассматривать мы будем, конечно же, на примерах.
SELECT name || surname AS FIO FROM table
Или чтобы отделить пробелом введите
SELECT name || " " || surname AS FIO FROM tableт.е. две вертикальные черты объединяют два столбца в один, а чтобы отделить их пробелом я поставил между ними пробел (можно использовать любой символ, например тире или двоеточие ) в апострофах и объединил также двумя вертикальными чертами (в Transact-SQL вместо двух вертикальных черточек используется знак + ).
Дальше идет также очень полезная функция, INITCAP – которая возвращает значение в строке, в которой каждое слово начинается с заглавной буквы, а продолжается маленькими. Это нужно для того, если у Вас в той или иной колонке не соблюдают правила заполнения и для того чтобы вывести все это дело в красивом виде можно использовать данную функцию, например, у Вас в таблице записи в колонке name следующего вида: ИВАН иванов или петр петров, Вы применяете данную функцию.
SELECT INITCAP (name) AS FIO FROM table
И у Вас получится вот так.
Похожая функция, только возвращает все символы с заглавной буквы, это UPPER .
SELECT UPPER (name) AS FIO FROM table
Сразу рассмотрим обратную функцию. RPAD – действие и синтаксис тот же что и у LPAD, только дополняются символы справа (в LPAD слева ).
SELECT RPAD (name, 20, "-") AS name FROM table
| Иван—————- |
| Сергей————— |
Далее идет тоже в некоторых случаях полезная функция, LTRIM – эта функция удаляет крайние левые символы, которые Вы укажите. Например, у Вас в базе есть колонка «город», в которой город указан в виде «г.Москва», а также есть города которые указанны в виде просто «Москва». Но Вам нужно вывести отчет только в виде «Москва» без «г.», но как это сделать, если есть и такие и такие? Вы просто указываете своего рода шаблон «г.» и если крайние левые символы начинаются с «г.», то эти символы просто не будут выводиться.
SELECT LTRIM (city, "г.") AS gorod FROM table
Данная функция просматривает символы слева, если символов по шаблону нет в начале строки, то она возвращает исходное значение ячейки, а если есть, то удаляет их.
Также давайте сразу рассмотрим обратную функцию. RTRIM – то же самое что и LTRIM только символы ищутся справа.
Примечание! В Transact-SQL функции RTRIM и LTRIM удаляют пробелы справа и слева соответственно.
Теперь рассмотрим такую интересную функцию как REPLACE – она возвращает строку, в которой все совпадения символов, заменяются на Ваши символы, которые Вы укажите. Для чего ее можно использовать, например, у Вас в базе есть колонки, в которых встречаются некие разделительные символы, допустим «/». Например, Иван/Иванов, а Вам хотелось бы вывести Иван-Иванов, то напишите
SELECT REPLACE (name, "/", "-") FROM table
и у Вас произойдет замена символов.
Данная функция заменяет только полное совпадение символов, если например Вы укажите «—» т.е. три тире она и будет искать только три тире, а каждое отдельное тире заменять не будет, в отличие от следующей функции.
TRANSLATE – строковая функция, которая заменяет все символы в строке, на те символы, которые Вы укажите. Исходя из названия функции, можно догадаться, что это полный перевод строки. Отличие данной функции от REPLACE в том, что она заменяет каждый символ, который Вы укажите, т.е. у Вас есть три символа, допустим абв и с помощью TRANSLATE Вы его можете заменить на abc таким образом у Вас а=a, б=b, в=c и по такому принципу будут заменяться все совпадения символов. А если Вы заменяли с помощью REPLACE, то у Вас искалось только полное совпадение символов абв расположенных подряд.
SUBSTR – данная функция, возвращает только тот диапазон символов, который Вы укажите. Другими словами, допустим, строка из 10 символов, а Вам все десять не нужны, а допустим, нужны только 3-8 (с третьего по восьмой ). С помощью данной функции Вы легко можете это сделать. Например, у Вас в базе есть какой-нибудь идентификатор, фиксированной длинны (типа: AA-BB-55-66-CC) и каждая комбинация символов что-то означает. И в один прекрасный момент Вам сказали вывести только 2 и 3 комбинацию символов, для этого вы пишите запрос следующего вида.
SELECT SUBSTR (ident, "4", "8") FROM table
т.е. мы выводим все символы, начиная с 4 и заканчивая 8, и после этого запроса у Вас выведется вот это:
Следующая функция также может пригодиться, это LENGTH – которая просто на всего считает количество символов в строке. Например, Вам нужно узнать, сколько символов в каждой ячейки столбца допустим «name», таблица следующего вида.
SELECT LENGTH (name) FROM tableпосле этого запроса Вы получите вот это.
| 4 |
| 6 |
| 7 |
Вот мы с Вами и рассмотрели основные строковые функции SQL. В следующих статьях мы продолжим изучение SQL.
Функции - это особый тип команды в наборе команд SQL, а каждый диалект имеет свою реализацию набора команд. В результате можно сказать, что функции - это команды, состоящие из одного слова и возвращающие одиночное значение. Значение функции может зависеть от входных параметров, как, например, в случае функции, вычисляющей среднее значение в списке значений в базе данных. Однако многие функции не используют никаких входных параметров например, функция, возвращающая текущее системное время - CURRENTJ1ME.
Стандарт ANSI поддерживает несколько полезных функций. В этой главе приводится описание этих функций, а также подробное описание и примеры по каждой из платформ. Кроме того, у каждой платформы есть длинный перечень своих собственных, внутренних функций, которые выходят за пределы стандарта SQL. В этой главе приводятся параметры и описания всех внутренних функций каждой из платформ.
Кроме того, большинство платформ имеют возможность создания пользовательских функций. За дополнительной информацией о пользовательских функциях обращайтесь к разделу «Инструкции CREATE/ALTER FUNCTION/PROCEDURE»
Существуют разные способы классификации функций. В следующих подразделах описываются важные различия, позволяющие понять, как работают функции.
Функции могут быть детерминированные и недетерминированные. Детерминированная функция всегда возвращает один и тот же результат при одном и том же наборе входных значений. Недетерминированные функции могут возвращать разные результаты при разных вызовах, даже если им передаются одинаковые входные значения.
Почему так важно, чтобы при одинаковых входных параметрах получались одинаковые результаты? Это важно потому, что это определяет способ использования функций в представлениях, пользовательских функциях и хранимых процедурах. Ограничения на разных платформах могут быть разными, но иногда в этих объектах можно использовать только детерминированные функции. Например, SQL Server может создавать индекс по выражению в столбце, если только это выражение не содержит недетерминированных функций. Правила и ограничения на разных платформах разные, потому обращайтесь при использовании функций к документации производителей.
Еще один способ классификации функций - по их возможности работы только с одной строкой, с коллекцией значений или с наборами строк. Агрегатные функции работают с коллекцией значений и возвращают одно суммарное значение. Скалярные функции возвращают одно значение, зависящее от скалярных входных аргументов. Некоторые скалярные функции, например CURRENTJTIME, не требуют никаких аргументов.
Оконные функции можно считать сходными с агрегатными функциями в том, что они могут работать сразу с несколькими строками. Различие в том, каким образом указываются эти строки. Агрегатные функции работают с наборами строк, указанными в предложении GROUP BY. В случае оконных функций набор строк указывается при каждом вызове функции и разные вызовы функции в пределах одного запроса могут работать с разными наборами строк.
Основные функциональные возможности языка SQL приведены ниже.
Определение данных. Эта функция SQLпредставляет собой описание структуры поддерживаемых данных и организацию реляционных отношений (таблиц). Для ее реализации предназначены операторы создания базы данных, создания таблиц и доступа к данным.
Создание базы данных . Для создания новой базы данных используется оператор CREATE DATABASE. В структуре оператора указывается имя создаваемой базы данных.
Создание таблиц. Базовая таблица создается с помощью оператора CREATE TABLE. В этом операторе указываются имена полей, типы данных для них, длина (для некоторых типов данных). В SQL используются следующие типы данных:
INTEGER – целое число;
CHAR – символьное значение;
VARCHAR – символьное значение, сохраняются только непустые символы;
DECIMAL – десятичное число;
FLOAT – число с плавающей запятой;
DOUBLE PRECISION – удвоенная точность с плавающей точкой;
DATETIME – дата и время;
BOOL – булевое значение.
В операторе создания таблицы указываются ограничения на значения столбцов и на таблицу. Возможные ограничения показаны в табл. 4.8
Таблица 4.8 Ограничения на определяемые данные
Для реляционной модели данных существенным является указания внешнего ключа(FOREIGNKEY). При объявлении внешних ключей необходимо наложить соответствующие ограничения на столбец, например, NOT NULL.
В SQL-предложении CHECK обозначает семантические ограничения, обеспечивающие целостность данных, чтобы, например, ограничить множество допустимых значения определенного столбца.
Нельзя использовать оператор создания таблицы несколько раз для одной и той же таблицы. Если после ее создания обнаружились неточности в ее определении, то внести изменения можно с помощью оператора ALTER TABLE. Этот оператор предназначен для изменения структуры существующей таблицы: можно удалить или добавить поле к существующей таблице.
Манипулирование данными. SQL позволяет пользователю или прикладной программе изменять содержимое базы данных путем вставки новых данных, удаления или модификации существующих данных.
Вставка новых данных является процедурой добавления строк в базу данных и выполняется с помощью оператора INSERT.
Модификация данных предполагает изменения значений в одном или нескольких столбцах таблицы и выполняется с помощью оператора UPDATE. Пример:
SET сумма=сумма+1000.00
WHERE сумма>0
Удаление строк из таблицы осуществляется с помощью оператора DELETE. Синтаксис оператора имеет вид:
FROM таблица
Предложение WHERE не является обязательным, однако, если его не включить, то будут удалены все записи таблицы. Полезно использовать оператор SELECT c тем же синтаксисом, что и оператор DELETE, чтобы предварительно проверить, какие записи будут удалены.
Обеспечение целостности данных. Язык SQL позволяет определить достаточно сложные ограничения целостности, удовлетворение которым будет проверяться при всех модификациях базы данных. Контроль за результатами транзакций, обработка возникающих ошибок и координирование параллельной работы с базой данных нескольких приложений или пользователей обеспечивается операторами COMMIT(фиксирует удачное окончание текущей транзакции и начало новой) и ROLLBACK (необходимость отката – автоматического восстановления состояния базы данных на начало транзакции)
Выборка данных – одна из важнейших функций базы данных, которой соответствует оператор SELECT. Пример использования оператора был рассмотрен в предыдущем разделе.
В SQL можно создавать вложенные последовательности запросов (подзапросы). Существуют определенные типы запросов, которые лучше реализовывать с помощью подзапросов. К таким запросам относятся так называемые проверки существования. Предположим, что требуется получить данные о студентах, которые не имеют оценку «семь баллов». Если будет возвращено пустое множество, то это означает лишь одно – у каждого студента есть, по крайней мере, одна такая оценка.
Связывание таблиц . Операторы языка SQL позволяют извлекать данные более чем из одной таблицы. Одна из возможностей сделать это заключается в связывании таблиц по одному общему полю.
В операторе SELECT должно присутствовать ограничение на совпадение значений определенного столбца (поля). Тогда из связанных таблиц будут извлекаться только те строки, в которых значения заданного столбца совпадают. Название столбца указывается только вместе с названием таблицы; в противном случае оператор будет неоднозначным.
Можно использовать другие типы связывания таблиц: оператор INTER JOIN (внутреннее соединение) обеспечивает присутствие в результирующем наборе записей, совпадающие значения в связанных полях. Внешние соединения (OUTER JOIN) позволяют включить в результат запроса все строки из одной таблицы и соответствующие им строки из другой
Управление доступом. SQL обеспечивает синхронизацию обработки базы данных различными прикладными программами, защиту данных от несанкционированного доступа.
Доступ к данным в многопользовательской среде регулируется с помощью операторов GRANT и REVOKE. В каждом операторе необходимо указать пользователя, объект (таблицу, представление), по отношению к которому задаются полномочия, и сами полномочия. Например, оператор GRANT задает пользователю Х возможность производить выборку данных из таблицы ТОВАР:
GRANT SELECT ON ТОВАР TO X
Оператор REVOKE аннулирует все предоставленные ранее полномочия.
Встраивание SQL в прикладные программы . Реальные приложения обычно написаны на других языках, генерирующих код на языке SQL и передающих их в СУБД в виде текста в формате ASCII.
Стандартом фирмы IBM для SQL-продуктов регламентировано использование встроенного языка SQL. При написании прикладной программы ее текст представляет собой смесь команд основного языка программирования (например, C, Pascal, Cobol, Fortran, Assembler) и команд SQL со специальным префиксом, например. ExecSQL. Структура SQL-предложений расширена для размещения переменных основного языка в SQL-конструкции.
SQL-процессор видоизменяет вид программы в соответствии с требованиями компилятора основного языка программирования. Функция компилятора состоит в трансляции (перевод) программы с исходного языка программирования на язык, близкий к машинному. После компиляции прикладная программа (приложение) представляет собой самостоятельный модуль.
Диалекты языка SQL
В современных реляционных СУБД для описания и манипулирования данными используются диалекты языка SQL. Подмножество языка SQL, позволяющее создавать и описывать БД, называется DDL (Data Definition Language).
Первоначально язык SQL назывался SEQUEL(Structured English Query Language), потом SEQUEL/2, а затем просто – SQL. Сегодня язык SQL –фактический стандарт для реляционных СУБД.
Первый стандарт языка появился в 1989 г. – SQL-89 и поддерживался практически всеми коммерческими реляционными СУБД. Он имел общий характер и допускал широкое толкование. Достоинствами SQL-89 можно считать стандартизацию синтаксиса и семантики операторов выборки и манипулирования данными, а также фиксацию средств ограничения целостности базы данных. Однако в нем отсутствовал такой важный раздел как манипулирование схемой базы данных. Неполнота стандарта SQL-89 привела к появлению в 1992г. следующей версии языка SQL.
SQL2 (или SQL-92) охватывает практически все необходимые проблемы: манипулирование схемой базы данных, управление транзакциями и сессиями, поддерживает архитектуры клиент-сервер или средства разработки приложений.
Дальнейшим шагом развития языка является вариант SQL 3. Эта версия языка дополняется механизмом триггеров, определением произвольного типа данных, объектным расширением.
В настоящее время существует три уровня языка: начальный, промежуточный и полный. Многие производители своих СУБД применяют собственные реализации SQL, основанные как минимум на начальном уровне соответствующего стандарта ANSI, и содержащие некоторые расширения, специфические для той или иной СУБД. В табл. 4.9 приведены примеры диалектов SQL.
Таблица 4.9 Диалекты языка SQL
| СУБД | Язык запросов |
| СУБД System R | SQL |
| DB2 | SQL |
| Access | SQL |
| SYBASE SQL Anywhere | Watcom-SQL |
| SYBASE SQL Server | Transact_SQL |
| My SQL | SQL |
| Oracle | PL/SQL |
В объектно-ориентированных БД используется язык объектных запросов OQL (Object Query Language). За основу языка OQL была взята команда SELECT языка SQL2 и добавлены возможность направлять запрос к объекту или коллекции объектов, а также возможность вызывать методы в рамках одного запроса.
Совместимость многих используемых диалектов SQL обусловливает совместимость СУБД. Так, СУБД SYBASE SQL Anywhere максимально, насколько это возможно для СУБД такого класса, совместима с СУБД SYBASE SQL Server. Одной из сторон такой совместимости является поддержка в SYBASE SQL Anywhere такого диалекта языка SQL как Transact-SQL . Этот диалект используется в SYBASE SQL Server и может применяться в SYBASE SQL Anywhere наряду с собственным диалектом языка SQL - Watcom-SQL .
1. Как можно классифицировать СУБД?
2. Какие модели баз данных существуют?
3. Что является основными элементами инфологических моделей?
4. Какие типы связей между сущностями существуют?
5. Что такое ER-диаграммы и для чего они используются?
6. Что позволяет делать процедура нормализации таблиц?
7. Назовите языковые и программные средства СУБД?
8. К каому типу относится СУБД MS Access?
9. Назовите основные объекты СУБД MS Access?
10. Для чего используются основные операторы языка SQL?
