Последнее обновление: 29.07.2017

Для работы со строками в T-SQL можно применять следующие функции:

LEN : возвращает количество символов в строке. В качестве параметра в функцию передается строка, для которой надо найти длину:

SELECT LEN("Apple") -- 5

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

SELECT LTRIM(" Apple")

RTRIM : удаляет конечные пробелы из строки. В качестве параметра принимает строку:

SELECT RTRIM(" Apple ")

CHARINDEX : возвращает индекс, по которому находится первое вхождение подстроки в строке. В качестве первого параметра передается подстрока, а в качестве второго - строка, в которой надо вести поиск:

SELECT CHARINDEX("pl", "Apple") -- 3

PATINDEX : возвращает индекс, по которому находится первое вхождение определенного шаблона в строке:

SELECT PATINDEX("%p_e%", "Apple") -- 3

LEFT : вырезает с начала строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:

SELECT LEFT("Apple", 3) -- App

RIGHT : вырезает с конца строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:

SELECT RIGHT("Apple", 3) -- ple

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

SELECT SUBSTRING("Galaxy S8 Plus", 8, 2) -- S8

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

SELECT REPLACE("Galaxy S8 Plus", "S8 Plus", "Note 8") -- Galaxy Note 8

REVERSE : переворачивает строку наоборот:

SELECT REVERSE("123456789") -- 987654321

CONCAT : объединяет две строки в одну. В качестве параметра принимает от 2-х и более строк, которые надо соединить:

SELECT CONCAT("Tom", " ", "Smith") -- Tom Smith

LOWER : переводит строку в нижний регистр:

SELECT LOWER("Apple") -- apple

UPPER : переводит строку в верхний регистр

SELECT UPPER("Apple") -- APPLE

SPACE : возвращает строку, которая содержит определенное количество пробелов

Например, возьмем таблицу:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

И при извлечении данных применим строковые функции:

SELECT UPPER(LEFT(Manufacturer,2)) AS Abbreviation, CONCAT(ProductName, " - ", Manufacturer) AS FullProdName FROM Products ORDER BY Abbreviation

Встроенные функции 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 (тип_данных , значение )

Аргумент значение в функциях задает величину, которую необходимо преобразовать.

7.3. Команды языка определения данных

Язык определения данных содержит команды, которые предназначены для создания, изменения и удаления базы данных и ее объектов.

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

Создание новой таблицы выполняется командой 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 Студент

Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье.

Агрегатные функции

Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций:

AVG

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

MIN и MAX

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

SUM

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

COUNT

Подсчитывает количество значений, отличных от null в столбце. Функция count(*) является единственной агрегатной функцией, которая не выполняет вычисления над столбцами. Эта функция возвращает количество строк (независимо от того, содержат ли отдельные столбцы значения null).

COUNT_BIG

Аналогична функции count, с той разницей, что возвращает значение данных типа BIGINT.

Использование обычных агрегатных функций в инструкции SELECT будет рассматриваться в одной из следующих статей.

Скалярные функции

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

числовые функции;

функции даты;

строковые функции;

системные функции;

функции метаданных.

Эти типы функций рассматриваются в последующих разделах.

Числовые функции

Числовые функции языка Transact-SQL - это математические функции для модифицирования числовых значений. Список числовых функций и их краткое описание приводится в таблице ниже:

Числовые функции Transact-SQL

Функция	Синтаксис	Описание	Пример использования
ABS	ABS(n)	Возвращает абсолютное значение (т. е. отрицательные значения возвращаются, как положительные) числового выражения n.	SELECT ABS(-5.320) -- Вернет 5.320 SELECT ABS(8.90) -- Вернет 8.90
ACOS, ASIN, ATAN, ATN2	ACOS(n), ASIN(n), ATAN(n), ATN2(n, m)	Обратные тригонометрические функции, вычисляющие арккосинус, арксинус, арктангенс значения n (для ATN2 вычисляется арктангенс n/m). Исходные значения n, m и результат имеют тип данных FLOAT.
COS, SIN, TAN, COT	COS(n), SIN(n), TAN(n), COT(n)	Тригонометрические функции, вычисляющие косинус, синус, тангенс, котангенс значения n. Результат имеет тип данных FLOAT.
DEGREES, RADIANS	DEGREES(n), RADIANS(n)	Функция DEGREES преобразует радианы в градусы, RADIANS соответственно наоборот.	SELECT DEGREES(PI() / 4) -- Вернет 45 SELECT COS(RADIANS(60.0)) -- Вернет 0.5
CEILING	CEILING(n)	Округляет число до большего целого значения.	SELECT CEILING(-5.320) -- Вернет -5 SELECT CEILING(8.90) -- Вернет 9
ROUND	ROUND(n, p, [t])	Округляет значение n с точностью до p. Когда аргумент p положительное число, округляется дробная часть числа n, а когда отрицательное - целая часть. При использовании необязательного аргумента t, число n не округляется, а усекается (т.е. округляется в меньшую сторону).	SELECT ROUND(5.3208, 3) -- Вернет 5.3210 SELECT ROUND(125.384, -1) -- Вернет 130.000 SELECT ROUND(125.384, -1, 1) -- Вернет 120.000
FLOOR	FLOOR(n)	Округляет до меньшего целого значения.	SELECT FLOOR(5.88) -- Вернет 5
EXP	EXP(n)	Вычисляет значение e n .
LOG, LOG10	LOG(n), LOG10(n)	LOG(n) - вычисляет натуральный логарифм (т.е. с основанием e) числа n, LOG10(n) - вычисляет десятичный (с основанием 10) логарифм числа n.
PI	PI()	Возвращает значение π (3,1415).
POWER	POWER(x, y)	Вычисляет значение x y .
RAND	RAND()	Возвращает произвольное число типа FLOAT в диапазоне значений между 0 и 1.
ROWCOUNT_BIG	ROWCOUNT_BIG()	Возвращает количество строк таблицы, которые были обработаны последней инструкцией Transact-SQL, исполненной системой. Возвращаемое значение имеет тип BIGINT.
SIGN	SIGN(n)	Возвращает знак значения n в виде числа: +1, если положительное, -1, если отрицательное.
SQRT, SQUARE	SQRT(n), SQUARE(n)	SQRT(n) - вычисляет квадратный корень числа n, SQUARE(n) - возвращает квадрат аргумента n.

Функции даты

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

Функции даты Transact-SQL

Функция	Синтаксис	Описание	Пример использования
GETDATE	GETDATE()	Возвращает текущую системную дату и время.	SELECT GETDATE()
DATEPART	DATEPART (item, date)	Возвращает указанную в параметре item часть даты date в виде целого числа.	Вернет 1 (Январь) SELECT DATEPART(month, "01.01.2012") -- Вернет 4 (Wednesday) SELECT DATEPART(weekday, "02.01.2012")
DATENAME	DATENAME (item, date)	Возвращает указанную в параметре item часть даты date в виде строки символов.	Вернет January SELECT DATENAME(month, "01.01.2012") -- Вернет Wednesday SELECT DATENAME(weekday, "02.01.2012")
DATEDIFF	DATEDIFF (item, dat1, dat2)	Вычисляет разницу между двумя частями дат dat1 и dat2 и возвращает целочисленный результат в единицах, указанных в аргументе item.	Вернет 19 (19 лет промежуток между датами) SELECT DATEDIFF(year, "01.01.1990", "01.01.2010") -- Вернет 7305 (7305 дней промежуток между датами) SELECT DATEDIFF(day, "01.01.1990", "01.01.2010")
DATEADD	DATEADD (item, n, date)	Прибавляет n-е количество единиц, указанных в аргументе item к указанной дате date. (Значение аргумента n также может быть отрицательным.)	Прибавит 3 дня к текущей дате SELECT DATEADD(day, 3, GETDATE())

Строковые функции

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

Строковые функции Transact-SQL

Функция	Синтаксис	Описание	Пример использования
ASCII, UNICODE	ASCII(char), UNICODE(char)	Преобразовывает указанный символ в соответствующее целое число кода ASCII.	SELECT ASCII("W") -- 87 SELECT UNICODE("ю") -- 1102
CHAR, NCHAR	CHAR(int), NCHAR(int)	Преобразовывает код ASCII (или Unicode если NCHAR) в соответствующий символ.	SELECT CHAR(87) -- "W" SELECT NCHAR(1102) -- "ю"
CHARINDEX	CHARINDEX (str1, str2)	Возвращает начальную позицию вхождения подстроки str1 в строку str2. Если строка str2 не содержит подстроки str1, возвращается значение 0	Вернет 5 SELECT CHARINDEX ("морф", "полиморфизм")
DIFFERENCE	DIFFERENCE (str1, str2)	Возвращает целое число от 0 до 4, которое является разницей между значениями SOUNDEX двух строк str1 и str2. Метод SOUNDEX возвращает число, которое характеризует звучание строки. С помощью этого метода можно определить подобно звучащие строки. Работает только для символов ASCII.	Вернет 2 SELECT DIFFERENCE ("spelling", "telling")
LEFT, RIGHT	LEFT (str, length), RIGHT (str, length)	Возвращает количество первых символов строки str, заданное параметром length для LEFT и последние length символов строки str для функции RIGHT.	DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "Синх" SELECT LEFT(@str, 4) -- Вернет "зация" SELECT RIGHT(@str, 5)
LEN	LEN(str)	Возвращает количество символов (не количество байт) строки str, указанной в аргументе, включая конечные пробелы.
LOWER, UPPER	LOWER(str), UPPER(str)	Функция LOWER преобразовывает все прописные буквы строки str1 в строчные. Входящие в строку строчные буквы и иные символы не затрагиваются. Функция UPPER преобразовывает все строчные буквы строки str в прописные.	DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "СИНХРОНИЗАЦИЯ" SELECT UPPER(@str) -- Вернет "синхронизация" SELECT LOWER(@str)
LTRIM, RTRIM	LTRIM(str), RTRIM(str)	Функция LTRIM удаляет начальные пробелы в строке str, RTRIM соответственно удаляет пробелы в конце строки.
QUOTENAME	QUOTENAME (char_string)	Возвращает строку в кодировке Unicode с добавленными ограничителями, чтобы преобразовать строку ввода в действительный идентификатор с ограничителями.	DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "[Синхронизация]" SELECT QUOTENAME(@str)
PATINDEX	PATINDEX (%p%, expr)	Возвращает начальную позицию первого вхождения шаблона p в заданное выражение expr, или ноль, если данный шаблон не обнаружен.	Вернет 4 SELECT PATINDEX("%хро%", "Синхронизация")
REPLACE	REPLACE (str1, str2, str3)	Заменяет все вхождения подстроки str2 в строке str1 подстрокой str3.	Вернет "Десинхронизация" SELECT REPLACE("Синхронизация", "Синхр", "Десинхр")
REPLICATE	REPLICATE (str, i)	Повторяет i раз строку str.	Вернет "aBaBaBaBaB" SELECT REPLICATE("aB", 5)
REVERSE	REVERSE (str)	Выводит строку str в обратном порядке.	Вернет "яицазинорхниС" SELECT REVERSE("Синхронизация")
SOUNDEX	SOUNDEX (str)	Возвращает четырехсимвольный код soundex, используемый для определения похожести двух строк. Работает только для символов ASCII.
SPACE	SPACE (length)	Возвращает строку пробелов длиной, указанной в параметре length. Аналог REPLICATE(" ", length).
STR	STR (f[, len[, d]])	Преобразовывает заданное выражение с плавающей точкой f в строку, где len - длина строки, включая десятичную точку, знак, цифры и пробелы (по умолчанию равно 10), а d - число разрядов дробной части, которые нужно возвратить.	Вернет "3.14" SELECT STR (3.1415, 4, 2)
STUFF	STUFF (str1, a, length, str2)	Удаляет из строки str1 length-символов, начиная с позиции a, и вставляет на их место строку str2.	Note in a book SELECT STUFF("Notebook", 5, 0," in a ") -- Handbook SELECT STUFF("Notebook", 1, 4, "Hand")
SUBSTRING	SUBSTRING (str1, a, length)	Извлекает из строки str, начиная с позиции a, подстроку длиной length.

Системные функции

Системные функции языка Transact-SQL предоставляют обширную информацию об объектах базы данных. Большинство системных функций использует внутренний числовой идентификатор (ID), который присваивается каждому объекту базы данных при его создании. Посредством этого идентификатора система может однозначно идентифицировать каждый объект базы данных.

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

Системные функции Transact-SQL

Функция	Синтаксис	Описание	Пример использования
CAST	CAST (w AS type [(length)]	Преобразовывает выражение w в указанный тип данных type (если это возможно). Аргумент w может быть любым действительным выражением.	Вернет 3 SELECT CAST (3.1258 AS INT)
COALESCE	COALESCE (a1, a2)	Возвращает первое значение выражения из списка выражений a1, a2, ..., которое не является значением null.
COL_LENGTH	COL_LENGTH (obj, col)	Возвращает длину столбца col объекта базы данных (таблицы или представления) obj.	Вернет 4 SELECT COL_LENGTH ("Employee", "Id")
CONVERT	CONVERT (type[(length)], w)	Эквивалент функции CAST, но аргументы указываются по-иному. Может применяться с любым типом данных.
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Возвращает текущие дату и время.
CURRENT_USER	CURRENT_USER	Возвращает имя текущего пользователя.
DATALENGTH	DATALENGTH (z)	Возвращает число байтов, которые занимает выражение z.	Этот запрос возвращает длину каждого поля SELECT DATALENGTH(FirstName) FROM Employee
GETANSINULL	GETANSINULL ("dbname")	Возвращает 1, если использование значений null в базе данных dbname отвечает требованиям стандарта ANSI SQL.
ISNULL	ISNULL (expr, value)	Возвращает значение выражения expr, если оно не равно NULL; в противном случае возвращается значение value.
ISNUMERIC	ISNUMERIC (expr)	Определяет, имеет ли выражение expr действительный числовой тип.
NEWID	NEWID()	Создает однозначный идентификационный номер ID, состоящий из 16-байтовой двоичной строки, предназначенной для хранения значений типа данных UNIQUEIDENTIFIER.
NEWSEQUENTIALID	NEWSEQUENTIALID()	Создает идентификатор GUID, больший, чем любой другой идентификатор GUID, созданный ранее этой функцией на указанном компьютере. (Эту функцию можно использовать только как значение по умолчанию для столбца.)
NULLIF	NULLIF (expr1, expr2)	Возвращает значение null, если значения выражений expr1 и expr2 одинаковые.	Запрос возвращает NULL для проекта, -- у которого Number = "p1" SELECT NULLIF(Number, "p1") FROM Project
SERVERPROPERTY	SERVERPROPERTY (propertyname)	Возвращает информацию о свойствах сервера базы данных.
SYSTEM_USER	SYSTEM_USER	Возвращает ID текущего пользователя.
USER_ID	USER_ID ()	Возвращает идентификатор пользователя username. Если пользователь не указан, то возвращается идентификатор текущего пользователя.
USER_NAME	USER_NAME ()	Возвращает имя пользователя с указанным идентификатором id. Если идентификатор не указан, то возвращается имя текущего пользователя.

Функции метаданных

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

Функции метаданных Transact-SQL

Функция	Синтаксис	Описание	Пример использования
COL_NAME	COL_NAME (tab_id, col_id)	Возвращает имя столбца с указанным идентификатором col_id таблицы с идентификатором tab_id.	Вернет имя столбца "LastName" SELECT COL_NAME (OBJECT_ID("Employee"), 3)
COLUMNPROPERTY	COLUMNPROPERTY (id, col, property)	Возвращает информацию об указанном столбце.	Вернет значение свойства PRECISION -- для столбца Id таблицы Employee SELECT COLUMNPROPERTY (OBJECT_ID("Employee"), "Id", "precision")
DATABASEPROPERTY	DATABASEPROPERTY (database, property)	Возвращает значение свойства property базы данных database.	Вернет значение свойства IsNullConcat -- для базы данных SampleDb SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat")
DB_ID	DB_ID ()	Возвращает идентификатор базы данных db_name. Если имя базы данных не указано, то возвращается идентификатор текущей базы данных.
DB_NAME	DB_NAME ()	Возвращает имя базы данных, имеющей идентификатор db_id. Если идентификатор не указан, то возвращается имя текущей базы данных.
INDEX_COL	INDEX_COL (table, i, no)	Возвращает имя индексированного столбца таблицы table. Столбец указывается идентификатором индекса i и позицией no столбца в этом индексе.
INDEXPROPERTY	INDEXPROPERTY (obj_id, index_name, property)	Возвращает свойства именованного индекса или статистики для указанного идентификационного номера таблицы, имя индекса или статистики, а также имя свойства.
OBJECT_NAME	OBJECT_NAME (obj_id)	Возвращает имя объекта базы данных, имеющего идентификатор obj_id.	SELECT OBJECT_NAME(245575913);
OBJECT_ID	OBJECT_ID (obj_name)	Возвращает идентификатор объекта obj_name базы данных.	Вернет 245575913 - ID таблицы Employee SELECT OBJECT_ID("Employee")
OBJECTPROPERTY	OBJECTPROPERTY (obj_id, property)	Возвращает информацию об объектах из текущей базы данных.

Основные функциональные возможности языка 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?

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

Введение

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

Определяют несколько категорий операторов SQL:

• определение объектов базы данных;
• манипулирование значениями;
• защита и управление;
• параметры сеанса;
• информация о базе;
• статический SQL;
• динамический SQL.

Операторы SQL для манипулирования данными

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

имя таблицы (имя столбца 1, имя столбца 2)

VALUES (значение 1, значение 2).

Для использования оператора INSERT при нескольких значениях, применяется такой синтаксис:

имя таблицы 1 (имя столбца 1, имя столбца 2)

SELECT имя столбца 1, имя столбца 2

FROM имя таблицы 2

WHERE имя таблицы 2.имя столбца 1>2

Этот запрос выберет все данные из таблицы 2, которые больше 2 по столбцу 1 и вставит их в первую.

UPDATE. Как видно из названия, этот оператор SQL запроса обновляет данные в существующей таблице по определённому признаку.

UPDATE имя таблицы 1

SET имя столбца 2 = «Василий»

WHERE имя таблицы 1.имя столбца 1 = 1

Данная конструкция заполнит значением Василий все строки, в которых встретит цифру 1 в первом столбце.

Данные из таблицы. Можно указать какое-либо условие или же убрать все строки.

DELETE FROM имя таблицы

WHERE имя таблицы.имя столбца 1 = 1

Приведённый запрос удалит из базы все данные со значением один в первом столбце. А вот так можно очистить всю таблицу:

Оператор SELECT

Главное назначение SELECT — выборка данных по определенным условиям. Результатом его работы всегда является новая таблица с отобранными данными. Оператор MS может быть использован в массе различных запросов. Поэтому наряду с ним можно рассмотреть и другие смежные ключевые слова.

Для выбора всех данных из определённой таблицы используется знак «*».

FROM имя таблицы 1

Результатом работы данного запроса будет точная копия таблицы 1.

А здесь происходит выборка по условию WHERE, которое достаёт из таблицы 1 все значения, больше 2 в столбце 1.

FROM имя таблицы 1

WHERE имя таблицы 1.имя столбца 1 > 2

Также можно указать в выборке, что нужны только определённые столбцы.

SELECT имя таблицы 1.имя столбца 1

FROM имя таблицы 1

Результатом данного запроса будут все строки, со значениями из столбца 1. С помощью операторов MS SQL можно составить собственную таблицу, на ходу заменив, вычислив и подставив определённые значения.

имя таблицы 1.имя столбца 1

имя таблицы 1.имя столбца 2

имя таблицы 1.имя столбца 3

имя таблицы 1.имя столбца 2 * имя таблицы 1.имя столбца 3 AS SUMMA

FROM имя таблицы 1

Данный, на первый взгляд сложный запрос выполняет выборку всех значений из таблицы 1, затем создаёт новые колонки EQ и SUMMA. В первую заносит знак «+», во вторую произведение данных из столбца 2 и 3. Полученный результат можно представить в виде таблицы, для понимания как это работает:

При использовании оператора SELECT, можно сразу провести упорядочивание данных по какому-либо признаку. Для этого используется слово ORDER BY.

имя таблицы 1.имя столбца 1

имя таблицы 1.имя столбца 2

имя таблицы 1.имя столбца 3

FROM имя таблицы 1

ORDER BY имя столбца 2

Результирующая таблица будет выглядеть таким образом:

То есть все строки были установлены в таком порядке, чтобы в столбце 2 значения шли по возрастанию.

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

Таблица «Сотрудники»

Таблица «Зарплата»

Теперь нужно, как-то связав эти две таблицы получить общие значения. Используя основные операторы SQL сделать это можно так:

Сотрудники.Номер

Сотрудники.Имя

Зарплата.Ставка

Зарплата.Начислено

FROM Сотрудники, Зарплата

WHERE Сотрудники.Номер = Зарплата.Номер

Здесь происходит выборка из двух разных таблиц значений, объединённых по номеру. Результатом будет следующий набор данных:

Ещё немного о SELECT. Использование агрегатных функций

Один из основных операторов может производить некоторые вычисления при выборке. Для этого он использует определённые функции и формулы.

К примеру, чтобы получить количество записей из таблицы «Сотрудники», нужно использовать запрос:

SELECT COUNT (*) AS N

FROM Сотрудники

В результате получится таблица с одним значением и столбцом.

Можно применить такой запрос и посмотреть что получится:

SUM(Зарплата.Начислено) AS SUMMA

MAX(Зарплата.Начислено) AS MAX

MIN(Зарплата.Начислено) AS MIN

AVG(Зарплата.Начислено) AS SRED

FROM Зарплата

Итоговая таблица будет такой:

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

Объединение, пересечение и разности

Объединить несколько запросов в SQL

SELECT Сотрудники.Имя

FROM Сотрудники

WHERE Сотрудники.Номер = 1

SELECT Сотрудники.Имя

FROM Сотрудники, Зарплата

WHERE Зарплата.Номер = 1

При этом стоит учитывать, что при таком объединении таблицы должны быть совместимы. То есть иметь одинаковое количество столбцов.

Синтаксис оператора SELECT и порядок его обработки

Первым делом SELECT определяет область, из которой он будет брать данные. Для этого используется ключевое слово FROM. Если не указано, что именно выбрать.

Затем может присутствовать SQL оператор WHERE. С его помощью SELECT пробегает по всем строкам таблицы и проверяет данные на соответствие условию.

Если в запросе имеется GROUP BY, то происходит группировка значений по указанным параметрам.

Операторы для сравнения данных

Их имеется несколько типов. В SQL операторы сравнения могут проверять различные типы значений.

«=». Обозначает, как можно догадаться, равенство двух выражений. Например, он уже использовался в примерах выше - WHERE Зарплата.Номер = 1.

«>». Знак больше. Если значение левой части выражения больше, то возвращается логическое TRUE и условие считается выполненным.

«<». Знак меньше. Обратный предыдущему оператор.

Знаки «<=» и «>=». Отличается от простых операторов больше и меньше, тем, что при равенстве операндов условие также будет истинным.

LIKE

Перевести данное ключевое слово можно как «похожий». Оператор LIKE в SQL используется примерно по такому же принципу — выполняет запрос по шаблону. То есть он позволяет расширить выборку данных из базы используя регулярные выражения.

Например, поставлена такая задача: из уже известной базы «Сотрудники» получить всех людей, чьё имя заканчивается на «я». Тогда запрос можно составить так:

FROM Сотрудники

WHERE Имя LIKE `%я`

Знак процента в данном случае означает маску, то есть любой символ и их количество. А по букве «я» SQL определит что последний символ должен быть именно таким.

CASE

Данный оператор SQL Server представляет собой реализацию множественного выбора. Он напоминает конструкцию switch во многих языках программирования. Оператор CASE в SQL выполняет действие по нескольким условиям.

Например, нужно выбрать из таблицы «Зарплата» максимальное и минимальное значение.

Тогда запрос можно составить так:

FROM Зарплата

WHERE CASE WHEN SELECT MAX(Начислено) THEN Максимум

WHEN SELECT MIN(Начислено) THEN Минимум

В данном контексте система ищет максимальное и минимальное значение в столбце «Начислено». Затем с помощью END создаётся поле «итог», в которое будет заноситься «Максимум» или «Минимум» в зависимости от результата выполнения условия.

Кстати, в SQL имеется и более компактная форма CASE — COALESCE.

Операторы определения данных

Это вид позволяет проводить разнообразное изменение таблиц — создание, удаление, модификации и работу с индексами.

Первый из них, который стоит рассмотреть — CREATE TABLE. Он делает не что иное, как создаёт таблицу. Если просто набрать запрос CREATE TABLE, ничего не случится, так как нужно ещё указать несколько параметров.

Например, для создания уже знакомой таблицы «Сотрудники» нужно использовать команды:

CREATE TABLE Сотрудники

(Номер number(10) NOT NULL

Имя varchar(50) NOT NULL

Фамилия varchar(50) NOT NULL)

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

DROP TABLE

Выполняет одну простую задачу — удаление указанной таблицы. Имеет дополнительный параметр IF EXISTS. Он поглощает ошибку при удалении, если искомая таблица не существует. Пример использования:

DROP TABLE Сотрудники IF EXISTS.

CREATE INDEX

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

CREATE INDEX название_индекса

ON название_таблицы(название_столбца)

Используется данный оператор в T-SQL, Oracle, PL SQL и многих других интерпретациях технологиях.

ALTER TABLE

Очень функциональный оператор, обладающий многочисленными вариантами. В общем случае производит изменение структуры, определения и размещения таблиц. Используется оператор в Oracle SQL, Postgres и многих других.

ADD. Осуществляет добавление столбца в таблицу. Синтаксис его такой: ALTER TABLE название_таблицы ADD название_столбца тип_хранимых_данных. Может иметь параметр IF NOT EXISTS, что подавить ошибку, если создаваемый столбец уже есть;

DROP. Удаляет столбец. Также имеет ключ IF EXISTS, без которого сгенерируется ошибка, говорящая о том, что требуемый столбец отсутствует;

CHANGE. Служит для переименования имени поля в указанное. Пример использования: ALTER TABLE название_таблицы CHANGE старое_имя новое_имя;

MODIFY. Данная команда поможет сменить тип и дополнительные атрибуты определённого столбца. А используется он вот так: ALTER TABLE название_таблицы MODIFY название_столбца тип_данных атрибуты;

CREATE VIEW

В SQL имеется такое понятие, как представление. Вкратце, это некая виртуальная таблица с данными. Образуется она в результате выборки с помощью оператора языка SQL SELECT. Представления могут ограничивать доступ к базе данных, скрывать их, заменять реальные имена столбцов.

Процесс создания происходит с помощью простого запроса:

CREATE VIEW название представления AS SELECT FROM * название таблицы

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

Немного о функциях

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

COUNT. Производит подсчёт записей или строк в конкретной таблице. В качестве параметра можно указать имя столбца, тогда данные будут взяты из него. SELECT COUNT * FROM Сотрудники;

AVG. применяется только на столбцы с числовыми данными. Ее результатом является определение среднего арифметического всех значений;

MIN и MAX. Эти функции уже использовались в этой статье. Определяют они максимальное и минимальное значения из указанного столбца;

SUM. Все просто — функция вычисляет сумму значений столбца. Применяется исключительно для числового вида данных. Добавив в запрос параметр DISTINCT, будут суммироваться только уникальные значения;

ROUND. Функция округления десятичных дробных чисел. В синтаксисе используется название столбца и количество знаков после запятой;

LEN. Простая функция, вычисляющая длину значений столбца. Результатом будет новая таблица с указанием количества символов;

NOW. Это ключевое слово используется для вычисления текущей даты и времени.

Дополнительные операторы

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

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

BETWEEN. Очень удобный инструмент для выборки. Он указывает область значений, среди которых нужно получить данные. На вход принимает параметр от и до какого числа используется диапазон;.

NOT. Оператор придаёт противоположность выражению.

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

LIMIT. Устанавливает количество строк для вывода. Особенность оператора в том, что он всегда располагается в конце. Принимает один обязательный параметр и один опциональный. Первый указывает, сколько строк с выбранными данными нужно показать. А если используется второй, то оператор срабатывает как для диапазона значений.

UNION. Очень удобный оператор для объединения нескольких запросов. Он уже встречался среди примеров этой в этой статье. Можно вывести нужные строки из нескольких таблиц, объединив их UNION для более удобного использования. Синтаксис его такой: SELECT имя_столбца FROM имя_таблицы UNION SELECT имя_другого_столбца FROM имя_другой таблицы. В результате получится сводная таблица с объединёнными запросами.

PRIMARY KEY. Переводится как «первичный ключ». Собственно, именно такая терминология и используется в справочных материалах. Он означает уникальный идентификатор строки. Применяется, как правило, при создании таблицы для указания поля, которое и будет содержать его.

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

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

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

Ограничения. Если нужно получить из базы с тысячами строк всего лишь двух, то стоит использовать операторы типа LIMIT или TOP. Не нужно извлекать данные средствами языка разработки оболочки.

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

Сортировка. Если есть возможность применять упорядочивание в запросе, то есть силами СУБД, то нужно её использовать. Это позволит значительно сэкономить на ресурсах при работе программы или сервиса.

Много запросов. Если приходится вставлять множество записей последовательно, то для оптимизации следует задуматься о пакетной вставке данных одним запросом. Это также позволит увеличить производительность всей системы в целом.

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

Типы. Для экономии места и ресурсов нужно чутко относиться к видам используемых данных. Если есть возможность воспользоваться менее «тяжёлым» для памяти типом, то надо применять именно его. Например, если известно, что в данном поле числовое значение не будет превышать 255, то зачем использовать 4-байтный INT, если есть TINYINT в 1 байт.

Заключение

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

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

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