Функции Transact-SQL. Встроенные функции Импорт SQL-команд из файла.sql

В этой статье вы можете рассмотреть виды функций в языке SQL. Функции могут быть агрегатными или скалярными. Начнем…

COUNT()

COUNT() - функция возвращающая количество записей (строк) таблицы. Запись функции с указанием столбца вернет количество записей конкретного столбца за исключением NULL записей. Синтаксис функции:

COUNT(column_name);

Запись функции с указанием маски «*» вернет количество всех записей в таблице. Синтаксис:

COUNT(*);

Рассмотрим примеры. Имеется следующая таблица Universities:

Пример 1. Вывести число записей таблицы, используя функцию COUNT:

SELECT COUNT(*)
FROM Universities; // выведит 7

Пример 2. Найти количество университетов расположенных в Москве, используя функцию COUNT:

SELECT COUNT(*) FROM Universities WHERE Location = "Moscow"; // выведит 3

AVG()

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

AVG(column_name);

Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию AVG найти среднее число студентов (Students) всех университетов:

SELECT AVG(Students) FROM Universities; // выведит 23133

MIN()

MIN() - функция возвращающая минимальное значение столбца. Функция имеет следующий синтаксис:

MIN(column_name);

Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию MIN найти минимальное значение столбца Professores:

SELECT MIN(Professores) FROM Universities; // выведит 936

MAX()

MAX() - функция возвращающая максимальное значение столбца таблицы. Функция имеет следующий синтаксис:

MAX(column_name);

Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию MAX найти максимальное значение колонки Students:

SELECT MAX(Students) FROM Univerities; // выведит 57000

SUM()

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

SUM ( expression);

Параметр ALL - является параметром по умолчанию. Считается сумма всех строк. При указании параметра DISTINCT - происходит подсчет только уникальных значений.

Рассмотрим примеры. Возьмем нашу таблицу Universities. Пример 1. Используя функцию SUM найти количество студентов (Students) всех университетов в таблице:

SELECT SUM(Students) FROM Universities; // выведит 161935

Пример 2. Используя функцию SUM произвести подсчет уникальных значений столбца Faculties:

SELECT SUM(DISTINCT Faculties) FROM Universities; // выведит 117 (в столбце Faculties присутствует 2 одинаковых записи, под ID 1 и 5. Значение столбца под ID 5 не суммируется.)

ROUND()

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

ROUND(expression, length);

expression - название столбца или столбцов, а так же вещественное число. length - указывает точность округления для числа.

Рассмотрим пример. Возьмём таблицу Planets:

Пример. Используя функцию ROUND округлить столбец SunSeason с точностью до одного знака после запятой:

SELECT ROUND(SunSeason, 1) FROM Planets;

UCASE()

UCASE() - функция, возвращающая значения столбца или столбцов в верхнем регистре букв. В СУБД MS SQL Server аналогом UCASE() является функция UPPER с тем же синтаксисом:

UCASE(column_name)

Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. Вывести названия планет в верхнем регистре, у которых нет колец, используя функцию UCASE:

SELECT UCASE(PlanetName) FROM Planets WHERE HavingRings = "No";

LCASE()

LCASE() - функция, возвращающая значения столбца или столбцов в нижнем регистре букв. В СУБД MS SQL Server аналогом оператора SQL LCASE() является функция LOWER с тем же синтаксисом:

LCASE(column_name);

Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. С помощью функции LCASE вывести в нижнем регистре названия планет, у которых есть кольца:

SELECT LCASE(PlanetName) FROM Planets WHERE HavingRings = "Yes";

LEN()

LEN() - функция, возвращающая длину значения в поле записи. Функция исключает из подсчета конечные пробелы. Синтаксис:

LEN(column_name);

Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. Вывести первооткрывателя планеты (Opener) и длину названия открытым им планеты, с помощью функции LEN:

SELECT Opener, LEN(PlanetName) FROM Planets;

MID()

MID() - функция, выводящая определенное количество символов текстового поля таблицы. Синтаксис функции:

MID(colunm_name,start [,length]);

Параметр start задает позицию начального символа. Параметр length устанавливает количество символов для вывода начиная с позиции, указанной в параметре start.

Рассмотрим пример. Возьмём нашу старую таблицу Universities. Пример. Вывести первые 3 символа названия города (Location) с помощью функции MID:

SELECT MID(Location, 1, 3) FROM Universities;

NOW()

NOW() - функция, возвращающая системное время и дату. Синтаксис функции:

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

SELECT UniversityName, Students, NOW() AS CurDate FROM Universities;

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

В этом учебном пособии вы узнаете, как создавать и удалять функции в SQL Server (Transact-SQL) с помощью синтаксиса и примеров.

Описание

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

Create Function

Вы можете создавать свои собственные функции в SQL Server (Transact-SQL). Давайте рассмотрим подробнее.

Синтаксис

Синтаксис Functions в SQL Server (Transact-SQL):

CREATE FUNCTION function_name
([ @parameter [ AS ] datatype
[ = default ] [ READONLY ]
, @parameter [ AS ] datatype
[ = default ] [ READONLY ] ]
RETURNS return_datatype
[ WITH { ENCRYPTION
| SCHEMABINDING
| RETURNS NULL ON NULL INPUT
| CALLED ON NULL INPUT
| EXECUTE AS Clause ]
[ AS ]
BEGIN
executable_section
RETURN return_value
END;

Параметры или аргументы

schema_name — имя схемы, которой принадлежит эта функция.
function_name — наименование функции в SQL Server.
@parameter — один или несколько параметров, которые передаются в функцию.
type_schema_name — схема, которая владеет типом данных, если это применимо.
datatype — тип данных для @parameter .
default — значение по умолчанию для назначения параметру @parameter .
READONLY — это означает, что @parameter не может быть перезаписана функцией.
return_datatype — тип данных возвращаемого значения функции.
ENCRYPTION — это означает, что источник для функции не будет сохранен как обычный текст в системных представлениях SQL Server.
SCHEMABINDING — это означает, что базовые объекты не могут быть изменены, чтобы влиять на функцию.
RETURNS NULL ON NULL INPUT — это означает, что функция вернет NULL, если любые параметры имеют значение NULL, без необходимости выполнять функцию.
CALL ON NULL INPUT — это означает, что функция будет выполняться, даже если любые параметры имеют NULL.
EXECUTE AS — устанавливает контекст безопасности для выполнения функции.
return_value — значение, возвращаемое функцией.

Пример

Рассмотрим пример создания функции в SQL Server (Transact-SQL).
Ниже приведен простой пример функции:

Transact-SQL

CREATE FUNCTION ReturnSite (@site_id INT) RETURNS VARCHAR(50) AS BEGIN DECLARE @site_name VARCHAR(50); IF @site_id < 10 SET @site_name = "yandex.com"; ELSE SET @site_name = "google.com"; RETURN @site_name; END;

Эта функция называется ReturnSite . Она имеет один параметр, называемый @site_id , который является типом данных INT. Функция возвращает значение VARCHAR (50), указанное в предложении RETURNS.
Затем вы можете ссылаться на новую функцию ReturnSite следующим образом.

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

В объектно-ориентированных БД используется язык объектных запросов 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?

Последнее обновление: 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

Набор встроенных функций может изменяться в зависимости от версии СУБД одного производителя и также в СУБД различных производителей. Так, например, в СУБД SQLBase, Centure Inc. есть функция @ATAN2(X,Y) , которая возвращает арктангенс Y/X , но отсутствует функция SIGN(X) .

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

SELECT ENAME, SAL, COMM, FINE, SAL + COMM - FINE FROM EMPLOYEE ORDER BY DEPNO;

Арифметическое выражение SAL + COMM - FINE выводится как новая колонка в результирующей таблице, которая вычисляется в результате выполнения запроса. Такие колонки называют еще производными (вычисляемыми) атрибутами или полями.

Функции обработки строк

SQL предоставляет вам широкий набор функций для манипулирования со строковыми данными (конкатенация строк, CHR, LENGTH, INSTR и другие). Список основных функций для обработки строковых данных приведен в таблице 8.3 .

Названия одних и тех же функций могут отличаться в различных СУБД. Так, например, функция СУБД Oracle SUBSTR(S, pos, [, len]) в СУБД SQLBase называется @SUBSTRING(S, pos, len) . В СУБД SQLBase имеются функции, которых нет в СУБД Oracle (см.