Использование функций sql. Строковые функции SQL – примеры использования

В этом учебном пособии вы узнаете, как создавать и удалять функции в 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;

CREATE FUNCTION ReturnSite

(@ site_id INT )

RETURNS VARCHAR (50 )

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 следующим образом.

Последнее обновление: 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, которые должен знать каждый программист

Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных (RDBMS). В этой статье будет рассказано о часто используемых командах SQL, с которыми должен быть знаком каждый программист. Этот материал идеально подойдёт для тех, кто хочет освежить свои знания об SQL перед собеседованием на работу. Для этого разберите приведённые в статье примеры и вспомните, что проходили на парах по базам данных.

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

Настройка базы данных для примеров

Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql . После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):

Mysql -u root -p

Затем введите пароль.

Выполните следующую команду. Назовём базу данных «university»:

CREATE DATABASE university; USE university; SOURCE ; SOURCE

Команды для работы с базами данных

1. Просмотр доступных баз данных

SHOW DATABASES;

2. Создание новой базы данных

CREATE DATABASE;

3. Выбор базы данных для использования

USE ;

4. Импорт SQL-команд из файла.sql

SOURCE ;

5. Удаление базы данных

DROP DATABASE ;

Работа с таблицами

6. Просмотр таблиц, доступных в базе данных

SHOW TABLES;

7. Создание новой таблицы

CREATE TABLE ( , , PRIMARY KEY (), FOREIGN KEY () REFERENCES ());

Ограничения целостности при использовании CREATE TABLE

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

ячейка таблицы не может иметь значение NULL;
первичный ключ - PRIMARY KEY (col_name1, col_name2, …) ;
внешний ключ - FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.

Пример

Создайте таблицу «instructor»:

CREATE TABLE instructor (ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name));

8. Сведения о таблице

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

DESCRIBE ;

9. Добавление данных в таблицу

INSERT INTO (, , , …) VALUES (, , , …);

При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

INSERT INTO VALUES (, , , …);

10. Обновление данных таблицы

UPDATE SET = , = , ... WHERE ;

11. Удаление всех данных из таблицы

DELETE FROM ;

12. Удаление таблицы

DROP TABLE ;

Команды для создания запросов

13. SELECT

SELECT используется для получения данных из определённой таблицы:

SELECT , , … FROM ;

Следующей командой можно вывести все данные из таблицы:

SELECT * FROM ;

14. SELECT DISTINCT

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

SELECT DISTINCT , , … FROM ;

15. WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

SELECT , , … FROM WHERE ;

В запросе можно задавать следующие условия:

сравнение текста;
сравнение численных значений;
логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE:

SELECT * FROM course WHERE dept_name=’Comp. Sci.’; SELECT * FROM course WHERE credits>3; SELECT * FROM course WHERE dept_name="Comp. Sci." AND credits>3;

16. GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.

SELECT , , … FROM GROUP BY ;

Пример

Выведем количество курсов для каждого факультета:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name;

17. HAVING

Ключевое слово HAVING было добавлено в SQL потому, что WHERE не может быть использовано для работы с агрегатными функциями.

SELECT , , ... FROM GROUP BY HAVING

Пример

Выведем список факультетов, у которых более одного курса:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1;

18. ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .

SELECT , , … FROM ORDER BY , , … ASC|DESC;

Пример

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

SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC;

19. BETWEEN

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

SELECT , , … FROM WHERE BETWEEN AND ;

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000;

20. LIKE

Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.

Есть два свободных оператора, которые используются в LIKE:

% (ни одного, один или несколько символов);
_ (один символ).

SELECT , , … FROM WHERE LIKE ;

Пример

Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-»:

SELECT * FROM course WHERE title LIKE ‘%to%’; SELECT * FROM course WHERE course_id LIKE "CS-___";

21. IN

С помощью IN можно указать несколько значений для оператора WHERE:

SELECT , , … FROM WHERE IN (, , …);

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

SELECT * FROM student WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);

22. JOIN

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

SELECT , , … FROM JOIN ON = ;

Пример 1

Выведем список всех курсов и соответствующую информацию о факультетах:

SELECT * FROM course JOIN department ON course.dept_name=department.dept_name;

Пример 2

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

SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id;

Пример 3

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

SELECT course.course_id, title, dept_name, credits, prereq_id FROM prereq RIGHT OUTER JOIN course ON prereq.course_id=course.course_id;

23. View

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

Создание

CREATE VIEW AS SELECT , , … FROM WHERE ;

Удаление

DROP VIEW ;

Пример

Создадим view , состоящую из курсов с 3 кредитами:

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

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

COUNT (col_name) - возвращает количество строк;
SUM (col_name) - возвращает сумму значений в данном столбце;
AVG (col_name) - возвращает среднее значение данного столбца;
MIN (col_name) - возвращает наименьшее значение данного столбца;
MAX (col_name) - возвращает наибольшее значение данного столбца.

25. Вложенные подзапросы

Вложенные подзапросы - это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос.

Пример

Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:

SELECT DISTINCT course_id FROM section WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (SELECT course_id FROM section WHERE semester = ‘Spring’ AND year= 2010);

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

COUNT()

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

COUNT(column_name);

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

COUNT(*);

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

ID	UniversityName	Students	Faculties	Professores	Location	Site
1	Perm State National Research University	12400	12	1229	Perm	psu.ru
2	Saint Petersburg State University	21300	24	13126	Saint-Petersburg	spbu.ru
3	Novosibirsk State University	7200	13	1527	Novosibirsk	nsu.ru
4	Moscow State University	35100	39	14358	Moscow	msu.ru
5	Higher School of Economics	20335	12	1615	Moscow	hse.ru
6	Ural Federal University	57000	19	5640	Yekaterinburg	urfu.ru
7	National Research Nuclear University	8600	10	936	Moscow	mephi.ru

Пример 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:

ID	PlanetName	Radius	SunSeason	OpeningYear	HavingRings	Opener
1	Mars	3396	687.58	1659	No	Christiaan Huygens
2	Saturn	60268	10759.22	-	Yes	-
3	Neptune	24764	60190.91	1846	Yes	John Couch Adams
4	Mercury	2439	115.88	1631	No	Nicolaus Copernicus
5	Venus	6051	243.56	1610	No	Galileo Galilei

Пример. Используя функцию 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, а каждый диалект имеет свою реализацию набора команд. В результате можно сказать, что функции - это команды, состоящие из одного слова и возвращающие одиночное значение. Значение функции может зависеть от входных параметров, как, например, в случае функции, вычисляющей среднее значение в списке значений в базе данных. Однако многие функции не используют никаких входных параметров например, функция, возвращающая текущее системное время - CURRENTJ1ME.

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

Кроме того, большинство платформ имеют возможность создания пользовательских функций. За дополнительной информацией о пользовательских функциях обращайтесь к разделу «Инструкции CREATE/ALTER FUNCTION/PROCEDURE»

Типы функций

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

Детерминированные и недетерминированные функции

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

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

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

Еще один способ классификации функций - по их возможности работы только с одной строкой, с коллекцией значений или с наборами строк. Агрегатные функции работают с коллекцией значений и возвращают одно суммарное значение. Скалярные функции возвращают одно значение, зависящее от скалярных входных аргументов. Некоторые скалярные функции, например CURRENTJTIME, не требуют никаких аргументов.

Оконные функции

Оконные функции можно считать сходными с агрегатными функциями в том, что они могут работать сразу с несколькими строками. Различие в том, каким образом указываются эти строки. Агрегатные функции работают с наборами строк, указанными в предложении GROUP BY. В случае оконных функций набор строк указывается при каждом вызове функции и разные вызовы функции в пределах одного запроса могут работать с разными наборами строк.