Руководство по transact sql

Примечание. Для тестовой базы данных рекомендуется использовать систему контейнеризации и контейнер с MySQL сервером. Большинство запросов, описанных в статье можно выполнять непосредственно в командной строке контейнера с небольшой поправкой: в MySQL нет директивы GO, а команды должны заканчиваться точкой с запятой, то есть символом «;». Или, как вариант, можно поставить контейнер с Microsoft SQL сервером. Подробности описаны в статье Использование Docker для MySQL сервера.

• Введение
• Определение
• Опытная база данных
• Элементы синтаксиса
  • Директивы сценария
  • Комментарии
  • Типы данных
  • Идентификаторы
  • Переменные
  • Операторы
  • Cистемные функции
  • Выражения
  • Управление выполнением сценария
  • Динамическое конструирование выражений
• Выборка данных
• Группировка данных
• Соединение таблиц
• Изменение данных
• Хранимые процедуры и функции
• Производительность

SQL (Structured Query Language) — это универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных (язык структурированных запросов).

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

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

Базы данных и спецификации SQL

Тип базы данных	Спецификация SQL
Microsoft SQL	Transact-SQL
Microsoft Jet/Access	Jet SQL
MySQL	SQL/PSM (SQL/Persistent Stored Module)
Oracle	PL/SQL (Procedural Language/SQL)
IBM DB2	SQL PL (SQL Procedural Language)
InterBase/Firebird	PSQL (Procedural SQL)

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

Transact-SQL — это процедурное расширение языка SQL компаний Microsoft. SQL был расширен такими дополнительными возможностями как:

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

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

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

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

-- Создание базы данных
USE master
CREATE DATABASE TestDatabase
GO
-- Создание таблиц
USE TestDatabase
CREATE TABLE Users (UserID int PRIMARY KEY, UserName nvarchar(40),
 UserSurname nvarchar(40), DepartmentID int, PositionID int)
CREATE TABLE Departments (DepartmentID int PRIMARY KEY,
 DepartmentName nvarchar(40))
CREATE TABLE Positions (PositionID int PRIMARY KEY,
 PositionName nvarchar(40), BaseSalary money)
CREATE TABLE [Local Customers] (CustomerID int PRIMARY KEY,
 CustomerName nvarchar(40), CustomerAddress nvarchar(255))
CREATE TABLE [Local Orders] (OrderID int PRIMARY KEY,
 CustomerID int, UserID int, [Description] text)
GO
-- Заполнение таблиц
USE TestDatabase
INSERT Users VALUES (1, 'Ivan', 'Petrov', 1, 1)
INSERT Users VALUES (2, 'Ivan', 'Sidorov', 1, 2)
INSERT Users VALUES (3, 'Petr', 'Ivanov', 1, 2)
INSERT Users VALUES (4, 'Nikolay', 'Petrov', 1, 3)
INSERT Users VALUES (5, 'Nikolay', 'Ivanov', 2, 1)
INSERT Users VALUES (6, 'Sergey', 'Sidorov', 2, 3)
INSERT Users VALUES (7, 'Andrey', 'Bukin', 2, 2)
INSERT Users VALUES (8, 'Viktor', 'Rybakov', 4, 1)
INSERT Departments VALUES (1, 'Production')
INSERT Departments VALUES (2, 'Distribution')
INSERT Departments VALUES (3, 'Purchasing')
INSERT Positions VALUES (1, 'Manager', 1000)
INSERT Positions VALUES (2, 'Senior analyst', 650)
INSERT [Local Customers] VALUES (1, 'Alex Company', '606443, Russia, Bor, Lenina str., 15')
INSERT [Local Customers] VALUES (2, 'Potrovka', '115516, Moscow, Promyshlennaya str., 1')
INSERT [Local Orders] VALUES (1, 1, 1, 'Special parts')
GO

Примечание. В Microsoft SQL Server 2000 запросы выполняются в приложении Query Analyzer. В Microsoft SQL Server 2005 запросы выполняются в SQL Server Management Studio.

В результате работы сценария на SQL сервере будет создана база данных TestDatabase с пятью пользовательскими таблицами: Users, Departments, Positions, Local Customers, Local Orders.

Директивы сценария — это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO — сигнализирует SQL-серверу об окончании сценария, EXEC (или EXECUTE) — выполняет процедуру или скалярную функцию.

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

• — — строковый комментарий исключает из выполнения только одну строку, перед которой стоят два минуса.
• /* */ — блоковый комментарий исключает из выполнения целый блок команд, заключенный в указанную конструкцию.

Как и в языках программирования, в SQL существуют различные типы данных для хранения переменных:

• Числа — для хранения числовых переменных (int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
• Даты — для хранения даты и времени (datetime, smalldatetime).
• Символы — для хранения символьных данных (char, nchar, varchar, nvarchar).
• Двоичные — для хранения бинарных данных (binary, varbinary, bit).
• Большеобъемные — типы данных для хранения больших бинарных данных (text, ntext, image).
• Специальные — указатели (cursor), 16-байтовое шестнадцатиричное число, которое используется для GUID (uniqueidentifier), штамп изменения строки (timestamp), версия строки (rowversion), таблицы (table).

Примечание. Для использования русских символов (не ASCII кодировки) испольюзуются типы данных с приставкой «n» (nchar, nvarchar, ntext), которые кодируют символы двумя байтами. Иначе говоря, для работы с Unicode используются типы данных с «n».

Примечание. Для данных переменной длины используются типы данных с приставкой «var». Типы данных без приставки «var» имеют фиксированную длину области памяти, неиспользованная часть которой заполняется пробелами или нулями.

Идентификаторы — это специальные символы, которые используются с переменными для идентифицирования их типа или для группировки слов в переменную. Типы идентификаторов:

• @ — идентификатор локальной переменной (пользовательской).
• @@ — идентификатор глобальной переменной (встроенной).
• # — идентификатор локальной таблицы или процедуры.
• ## — идентификатор глобальной таблицы или процедуры.
• [ ] — идентификатор группировки слов в переменную.

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

Объявление переменной выполняется командой DECLARE, задание значения переменной осуществляется либо командой SET, либо SELECT:

USE TestDatabase
-- Объявление переменных
DECLARE @EmpID int, @EmpName varchar(40)
-- Задание значения переменной @EmpID
SET @EmpID = 1
-- Задание значения переменной @EmpName
SELECT @EmpName = UserName FROM Users WHERE UserID = @EmpID
-- Вывод переменной @EmpName в результат запроса
SELECT @EmpName AS [Employee Name]
GO

Примечание. В этом примере используется группировка слов в переменную — конструкция [Employee Name] воспринимается как одна переменная, так как слова заключены в квадратные скобки.

Операторы — это специальные команды, предназначенные для выполнения простых операций над переменными:

• Арифметические операторы: «*» — умножить, «/» — делить, «%» — модуль от деления, «+» — сложить , «-» — вычесть, «()» — скобки.
• Операторы сравнения: «=» — равно, «>» — больше, «<» — меньше, «>=» — больше или равно, «<=» меньше или равно, «<>» — не равно.
• Операторы соединения: «+» — соединение строк.
• Логические операторы: «AND» — и, «OR» — или , «NOT» — не.

Спецификация Transact-SQl значительно расширяет стандартные возможности SQL благодаря встроенным функциям:

• Агрегативные функции- функции, которые работают с коллекциями значений и выдают одно значение. Типичные представители: AVG — среднее значение колонки, SUM — сумма колонки, MAX — максимальное значение колонки, COUNT — количество элементов колонки.
• Скалярные функции- это функции, которые возвращают одно значение, работая со скалярными данными или вообще без входных данных. Типичные представители: DATEDIFF — разница между датами, ABS — модуль числа, DB_NAME — имя базы данных, USER_NAME — имя текущего пользователя, LEFT — часть строки слева.
• Функции-указатели- функции, которые используются как ссылки на другие данные. Типичные представители: OPENXML — указатель на источник данных в виде XML-структуры, OPENQUERY — указатель на источник данных в виде другого запроса.

Примечание. Полный список функций можно найти в справке к SQL серверу.

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

Пример:

USE TestDatabase
-- Использование агрегативной функции для подсчета средней зарплаты
SELECT AVG(BaseSalary) AS [Average salary] FROM Positions
GO
-- Использование скалярной функции для получения имени базы данных
SELECT DB_NAME() AS [Database name]
GO
-- Использование скалярной функции для получения имени текущего пользователя
DECLARE @MyUser char(30)
SET @MyUser = USER_NAME()
SELECT 'The current user''s database username is: '+ @MyUser
GO
-- Использование функции-указателя для получения данных с другого сервера
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM owner.titles')
GO

Выражение — это комбинация символов и операторов, которая получает на вход скалярную величину, а на выходе дает другую величину или исполняет какое-то действие. В Transact-SQL выражения делятся на 3 типа: DDL, DCL и DML.

• DDL (Data Definition Language)- используются для создания объектов в базе данных. Основные представители данного класса: CREATE — создание объектов, ALTER — изменение объектов, DROP — удаление объектов.
• DCL (Data Control Language)- предназначены для назначения прав на объекты базы данных. Основные представители данного класса: GRANT — разрешение на объект, DENY — запрет на объект, REVOKE — отмена разрешений и запретов на объект.
• DML (Data Manipulation Language)- используются для запросов и изменения данных. Основные представители данного класса: SELECT — выборка данных, INSERT — вставка данных, UPDATE — изменение данных, DELETE — удаление данных.

Пример:

USE TestDatabase
-- Использование DDL
CREATE TABLE TempUsers (UserID int, UserName nvarchar(40), DepartmentID int)
GO
-- Использование DCL
GRANT SELECT ON Users TO public
GO
-- Использование DML
SELECT UserID, UserName + ' ' + UserSurname AS [User Full Name] FROM Users
GO
-- Использование DDL
DROP TABLE TempUsers
GO

В Transact-SQL существуют специальные команды, которые позволяют управлять потоком выполнения сценария, прерывая его или направляя в нужную логику.

• Блок группировки — структура, объединяющая список выражений в один логический блок (BEGIN … END).
• Блок условия — структура, проверяющая выполнения определенного условия (IF … ELSE).
• Блок цикла — структура, организующая повторение выполнения логического блока (WHILE … BREAK … CONTINUE).
• Переход — команда, выполняющая переход потока выполнения сценария на указанную метку (GOTO).
• Задержка — команда, задерживающая выполнение сценария (WAITFOR)
• Вызов ошибки — команда, генерирующая ошибку выполнения сценария (RAISERROR)

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

Обычно сценарии пишутся как универсальное средство для выполнения стандартных задач, поэтому в них применяется динамическое конструирование логики — в запросы и команды вставляются переменные, а не конкретные названия объектов, что позволяет быстро изменять параметры скрипта.

Пример:

USE master
-- Задание динамических данных
DECLARE @dbname varchar(30), @tablename varchar(30), @column varchar(30)
SET @dbname = 'TestDatabase'
SET @tablename = 'Positions'
SET @column = 'BaseSalary'
-- Использование динамических данных
EXECUTE ('USE ' + @dbname + ' SELECT AVG(' + @column + ')
 AS [Average salary] FROM ' + @tablename)
GO

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

SELECT [ALL или DISTINCT] <названия колонок или *> FROM <название таблицы>

По умолчанию в команде SELECT используется параметр ALL, который можно не указывать. Если в команде указать параметр DISTINCT, то в результат попадут только уникальные (неповторяющиеся) записи из выборки.

Для того, чтобы изменить имена объектов в командах к SQL-серверу, используется команда AS. Использование этой команды помогает сокращать длину строки запроса, а так же получать результат в более удобочитаемом виде.

Пример:

-- Выбрать все записи из таблицы Local Customers
SELECT * FROM [Local Customers]

CustomerID	CustomerName	CustomerAddress
1	Alex Company	606443, Russia, Bor, Lenina str., 15′)
2	Potrovka	115516, Moscow, Promyshlennaya str., 1

-- Выбрать записи колонки DepartmentName из таблицы Departments
-- и назвать результирующую колонку Department Name
SELECT DepartmentName AS 'Department Name' FROM Departments

Department Name
Production
Distribution
Purchasing

-- Выбрать уникальные записи колонки UserName из таблицы Users
SELECT DISTINCT UserName FROM Users

UserName
Andrey
Ivan
Nikolay
Petr
Sergey
Viktor

Фильтрация данных осуществляется с помощью команды WHERE, в которой используются следующие операторы и команды сравнения: =, <, >, <=, >=, <>, LIKE, NOT LIKE, AND, OR, NOT, BETWEEN, NOT BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL. В общем виде команда SELECT с фильтром выглядит так:

SELECT [ALL или DISTINCT] <названия колонок или *> FROM <название таблицы>
 WHERE <условие>

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

• % — любое количество символов;
• _ — один символ;
• [] — любой символ, указанный в скобках;
• [^] — любой символ, не указанный в скобках.

-- Выбрать все записи из таблицы Users, где DepartmentID = 1
SELECT * FROM Users WHERE DepartmentID = 1

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Petr	Ivanov	1	2
4	Nikolay	Petrov	1	3

-- Выбрать все записи из таблицы Users, у кого в имени есть буква A
SELECT * FROM Users WHERE UserName LIKE '%a%'

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
4	Nikolay	Petrov	1	3
5	Nikolay	Ivanov	2	1
7	Andrey	Bukin	2	2

-- Выбрать все записи из таблицы Users, у кого в имени вторая буква не V
SELECT * FROM Users WHERE UserName LIKE '_[^v]%'

UserID	UserName	UserSurname	DepartmentID	PositionID
3	Petr	Ivanov	1	2
4	Nikolay	Petrov	1	3
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2
8	Viktor	Rybakov	4	1

-- Выбрать записи колонок UserName и UserSurname из таблицы Users, у кого
-- PositionID между 2 и 3, результирующую колонку назвать Full name.
SELECT [UserName] + ' ' + [UserSurname] AS 'Full name' FROM Users
 WHERE PositionID BETWEEN 2 AND 3

Full name
Ivan Sidorov
Petr Ivanov
Nikolay Petrov
Sergey Sidorov
Andrey Bukin

Фильтрация позволяет использовать подзапросы, то есть конструировать запрос из нескольких подзапросов:

-- Выбрать записи колонки PositionID из таблицы Positions, где BaseSalary < 600
SELECT PositionID FROM Positions WHERE BaseSalary < 600

PositionID
3

-- Выбрать все записи из таблицы Users, у кого оклад не меньше 600
SELECT * FROM Users WHERE PositionID NOT IN
 (SELECT PositionID FROM Positions WHERE BaseSalary < 600)

UserID	UserName	UserSurname	DepartmentID	PositionID
4	Nikolay	Petrov	1	3
6	Sergey	Sidorov	2	3

-- Выбрать все записи из таблицы Users, у кого имя Ivan или Andrey
SELECT * FROM Users WHERE UserName IN ('Ivan', 'Andrey')

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
7	Andrey	Bukin	2	2

-- Сосчитать суммарную зарплату отдела с идентификатором 1
DECLARE @DepID int
SET @DepID = 1
SELECT DepartmentName AS 'Department name',
   (SELECT SUM(Positions.BaseSalary) FROM Positions
      INNER JOIN Users ON Users.PositionID = Positions.PositionID
      WHERE Users.DepartmentID = @DepID
   ) AS 'Summary salary'
 FROM Departments
 WHERE DepartmentID = @DepID

Department name	Summary salary
Production	2700.0000

Для сортировки данных в выборке используется командаORDER BY, но следует учесть, что эта команда не сортирует данные типа text, ntext и image. По умолчанию сортировка производится по возрастанию, поэтому параметр ASC в этом случае можно не указывать:

SELECT [ALL или DISTINCT] <названия колонок или *> FROM <название таблицы>
 WHERE <условие> ORDER BY <названия колонок> [ASC или DESC]

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

SELECT [ALL или DISTINCT] TOP [количество строк] <названия колонок или *>
 FROM <название таблицы> WHERE <условие>
 ORDER BY <названия колонок> [ASC или DESC]

Внутри запроса можно проводить вычисления над полученными данными. Для этого используюся функции агрегирования:

• AVG(колонка) — среднее значение колонки;
• COUNT(колонка) — количество не NULL элементов колонки;
• COUNT(*) — количество элементов запроса;
• MAX(колонка) — максимальное значение в колонке;
• MIN(колонка) — минимальное значение в колонке;
• SUM(колонка) — сумма значений в колонке.

Примеры использования команд ORDER, TOP и функций агрегирования:

-- Выбрать 3 первые уникальные записи колонки UserName из таблицы Users,
-- отсортированных по возрастанию UserName
SELECT DISTINCT TOP 3 UserName FROM Users ORDER BY UserName

UserName
Andrey
Ivan
Nikolay

-- Выбрать 15 процентов строк из таблицы Users
SELECT TOP 15 PERCENT * FROM Users

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2

-- Найти величину максимального оклада в организации
SELECT MAX(BaseSalary) FROM Positions

(No column name)
1000.0000

-- Найти должности, у которых максимальный оклад в организации
	SELECT * FROM Positions
	 WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions)

PositionID	PositionName	BaseSalary
1	Manager	1000.0000

-- Найти сотрудников, у кого максимальный оклад в организации  
SELECT * FROM Users
 WHERE PositionID IN (SELECT PositionID FROM Positions
 WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions))

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
5	Nikolay	Ivanov	2	1
8	Viktor	Rybakov	4	1

-- Найти количество сотрудников, у кого максимальный оклад в организации  
SELECT COUNT(*) FROM Users
 WHERE PositionID IN (SELECT PositionID FROM Positions
 WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions))

(No column name)
3

SQL позволяет производить группировку данных по определенным полям таблицы. Чтобы сгруппировать данные по какому-нибудь параметру, в SQL-запросе необходимо написать команду GROUP BY, в которой указать имя колонки, по которой производится группировка. Колонки, упомянутые в команде GROUP BY, должны присутствовать в команде SELECT, а так же команда SELECT должна содержать функцию агрегирования, которая будет применена к сгруппированным данным.

-- Найти количество работников в каждом отделе (сгруппировать работников по
-- идентификатору отделов и сосчитать количество записей в каждой группе)
SELECT DepartmentID, COUNT(UserID) AS 'Number of users'
 FROM Users GROUP BY DepartmentID

DepartmentID	Number of users
1	4
2	3
4	1

Чтобы отфильтровать строки в запросе с группировкой применяется специальная команда HAVING, в которой указывается условие фильтрации. Колонки, по которым производится фильтрация, должны присутствовать в команде GROUP BY. Команда HAVING может использоваться и без GROUP BY, в этом случае она работает аналогично команде WHERE, но она разрешает применять в условиях фильтрации только функции агрегирования.

-- Найти количество работников в первом отделе (сгруппировать работников по
-- идентификатору отделов, сосчитать количество записей в каждой группе и
-- вывести в результат только отдел с идентификатором равным 1)
SELECT DepartmentID, COUNT(UserID) AS 'Number of users'
 FROM Users GROUP BY DepartmentID HAVING DepartmentID = 1

DepartmentID	Number of users
1	4

Команда группировки может дополняться оператором WITH ROLLUP, который дополняет результат группировки сводной строкой с суммой значений колонок.

-- Найти количество работников в каждом отделе (сгруппировать работников по
-- идентификатору отделов и сосчитать количество записей в каждой группе),
-- а также сосчитать общее количество работников
SELECT DepartmentID, COUNT(UserID) AS 'Number of users'
 FROM Users GROUP BY DepartmentID WITH ROLLUP

DepartmentID	Number of users
1	4
2	3
4	1
NULL	8

-- Найти количество работников с определенной должностью в каждом отделе
-- (сгруппировать работников по идентификатору должностей и отделов и
-- сосчитать количество записей в каждой группе), а также сосчитать
-- количество работников в каждом отделе и общее количество работников
SELECT DepartmentID, PositionID, COUNT(UserID) AS 'Number of users'
 FROM Users GROUP BY DepartmentID, PositionID WITH ROLLUP

DepartmentID	PositionID	Number of users
1	1	1
1	2	2
1	3	1
1	NULL	4
2	1	1
2	2	1
2	3	1
2	NULL	3
4	1	1
4	NULL	1
NULL	NULL	8

Команда группировки также может дополняться оператором WITH CUBE, который дополняет формирует всевозможные комбинации из группируемых колонок: если есть N колонок, то получится 2^N комбинаций.

-- Найти количество работников с определенной должностью в каждом отделе
-- (сгруппировать работников по идентификатору должностей и отделов и
-- сосчитать количество записей в каждой группе), а также сосчитать
-- количество работников по каждой должности, по каждому отделу и
-- общее количество работников
SELECT DepartmentID, PositionID, COUNT(UserID) AS 'Number of users'
 FROM Users GROUP BY DepartmentID, PositionID WITH CUBE

DepartmentID	PositionID	Number of users
1	1	1
1	2	2
1	3	1
1	NULL	4
2	1	1
2	2	1
2	3	1
2	NULL	3
4	1	1
4	NULL	1
NULL	NULL	8
NULL	1	3
NULL	2	3
NULL	3	2

Функция агрегирования GROUPING позволяет определить, была ли запись добавлена командами ROLLUP и CUBE, или это запись получена из источника данных.

-- Найти количество работников в каждом отделе (сгруппировать работников по
-- идентификатору отделов и сосчитать количество записей в каждой группе)
-- а так же пометить дополнительные строки, несуществующие в источнике данных
SELECT DepartmentID, COUNT(UserID) AS 'Number of users',
 GROUPING(DepartmentID) AS 'Added row'
 FROM Users GROUP BY DepartmentID WITH ROLLUP

DepartmentID	Number of users	Added row
1	4	0
2	3	0
4	1	0
NULL	8	1

Еще одна команда группировки COMPUTE позволяет группировать данные и выводить по ним отчет в разные таблицы. То есть команда GROUP BY с операторами ROLLUP и CUBE группирует данные и дописывает в таблицу дополнительны строки с отчетом, а команда COMPUTE группирует данные, разрывая исходную таблицу на несколько подтаблиц, а также формирует подтаблицы с отчетами. Команда COMPUTE может использоваться в двух режимах:

• как простая функция агрегирования, выводящая результат в отдельную таблицу;
• с параметром BY как команда группировки, разрезающая таблицу на несколько подтаблиц

Команда COMPUTE с параметром BY может использоваться только совместно с командой ORDER BY, причем столбцы сортировки должны совпадать со столбцами группировки.

-- Вывести таблицу пользователей компании, а также посчитать их количество
	SELECT * FROM Users COMPUTE COUNT(UserID)

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Petr	Ivanov	1	2
4	Nikolay	Petrov	1	3
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2
8	Viktor	Rybakov	4	1

cnt
8

-- Найти количество работников в каждом отделе (сгруппировать работников по
-- идентификатору отделов и сосчитать количество записей в каждой группе)
SELECT * FROM Users ORDER BY DepartmentID COMPUTE COUNT(UserID) BY DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Petr	Ivanov	1	2
4	Nikolay	Petrov	1	3

cnt
4

UserID	UserName	UserSurname	DepartmentID	PositionID
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2

cnt
3

UserID	UserName	UserSurname	DepartmentID	PositionID
8	Viktor	Rybakov	4	1

cnt
1

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

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

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

-- Найти всех пользователей с именем Ivan и соединить результат с
-- результатом от запроса "Найти всех пользователей с фамилией Petrov"
-- дублирующие записи исключить
SELECT * FROM Users WHERE UserName = 'Ivan'
UNION
SELECT * FROM Users WHERE UserSurname = 'Petrov'

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
4	Nikolay	Petrov	1	3

-- Найти всех пользователей с именем Ivan и соединить результат с
-- результатом от запроса "Найти всех пользователей с фамилией Petrov"
-- дублирующие записи сохранить
SELECT * FROM Users WHERE UserName = 'Ivan'
UNION ALL
SELECT * FROM Users WHERE UserSurname = 'Petrov'

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
1	Ivan	Petrov	1	1
4	Nikolay	Petrov	1	3

Горизонтальное соединение производится путем сцепки нескольких таблиц по ключевым колонкам. Самое простое горизонтальное соединение выполняется с помощью команды INNER JOIN, которая сцепляет таблицы, выбирая строки по ключевому полю, которое встречается в обоих таблицах.

SELECT [ALL или DISTINCT] <названия колонок или *> FROM <таблица_1>
	 INNER JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Чтобы выполнить сцепление по всем полям левой таблицы, независимо, есть ли такие записи в правой таблице, необходимо использовать команду LEFT JOIN. Эта команда соединяет таблицы, выбирая все строки из левой таблицы, а отсутствующие данные правой таблицы заполняются значением NULL.

SELECT [ALL или DISTINCT] <названия колонок или *> FROM <таблица_1>
 LEFT JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Команда RIGHT JOIN аналогична предыдущей, разница заключается лишь в том, что она соединяет таблицы, выбирая все строки из правой таблицы, а отсутствующие данные левой таблицы заполняются значением NULL.

SELECT [ALL или DISTINCT] <названия колонок или *> FROM <таблица_1>
 RIGHT JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Команда FULL JOIN объединяет в себе левое и правое сцепление, то есть она соединяет таблицы, выбирая строки из обоих таблиц, а отсутствующие данные заполняются значением NULL.

SELECT [ALL или DISTINCT] <названия колонок или *> FROM <таблица_1>
 FULL JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Последняя и редкоиспользуемая команда соединения таблиц — это CROSS JOIN. Эта команда сцепляет таблицы без использования ключевого поля, а результат — это комбинация из всевозможных строк исходных таблиц.

SELECT [ALL или DISTINCT] <названия колонок или *> FROM <таблица_1>
 CROSS JOIN таблица_2

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

SELECT * FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution

SELECT * FROM Users LEFT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
8	Viktor	Rybakov	4	1	NULL	NULL

SELECT * FROM Users RIGHT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
NULL	NULL	NULL	NULL	NULL	3	Purchasing

SELECT * FROM Users FULL JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
NULL	NULL	NULL	NULL	NULL	3	Purchasing
8	Viktor	Rybakov	4	1	NULL	NULL

SELECT * FROM Users CROSS JOIN Departments

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Petr	Ivanov	1	2	1	Production
4	Nikolay	Petrov	1	3	1	Production
5	Nikolay	Ivanov	2	1	1	Production
6	Sergey	Sidorov	2	3	1	Production
7	Andrey	Bukin	2	2	1	Production
8	Viktor	Rybakov	4	1	1	Production
1	Ivan	Petrov	1	1	2	Distribution
2	Ivan	Sidorov	1	2	2	Distribution
3	Petr	Ivanov	1	2	2	Distribution
4	Nikolay	Petrov	1	3	2	Distribution
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
8	Viktor	Rybakov	4	1	2	Distribution
1	Ivan	Petrov	1	1	3	Purchasing
2	Ivan	Sidorov	1	2	3	Purchasing
3	Petr	Ivanov	1	2	3	Purchasing
4	Nikolay	Petrov	1	3	3	Purchasing
5	Nikolay	Ivanov	2	1	3	Purchasing
6	Sergey	Sidorov	2	3	3	Purchasing
7	Andrey	Bukin	2	2	3	Purchasing
8	Viktor	Rybakov	4	1	3	Purchasing

SELECT dpt.DepartmentName AS 'Department',
 usr.UserName + ' ' + usr.UserSurname AS 'User name',
 pos.PositionName AS 'Position'
 FROM Users AS usr
 LEFT JOIN Departments AS dpt ON usr.DepartmentID = dpt.DepartmentID
 LEFT JOIN Positions AS pos ON usr.PositionID = pos.PositionID
 ORDER BY dpt.DepartmentID, pos.PositionID

Department	User name	Position
NULL	Viktor Rybakov	Manager
Production	Ivan Petrov	Manager
Production	Ivan Sidorov	Senior analyst
Production	Petr Ivanov	Senior analyst
Production	Nikolay Petrov	Analyst
Distribution	Nikolay Ivanov	Manager
Distribution	Andrey Bukin	Senior analyst
Distribution	Sergey Sidorov	Analyst

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

За транзакции в Transact-SQL отвечает структура BEGIN TRANSACTION … COMMIТ TRANSACTION. Эту структуру использовать необязательно, но тогда все команды сценария являются необратимыми, то есть нельзя сделать «откат» к предыдущему состоянию. Полная структура блока транзакций:

BEGIN TRANSACTION [имя транзакции]
 [операции]
COMMIТ TRANSACTION [имя транзакции] или ROLLBACK TRANSACTION [имя транзакции]

Ниже приведен пример использования этого блока:

-- Установить всем сотрудникам новый оклад
BEGIN TRANSACTION TR1
 UPDATE Positions SET BaseSalary = 2500000000000000
 IF @@ERROR <> 0
   BEGIN
     RAISERROR('Error, transaction not completed!',16,-1)
     ROLLBACK TRANSACTION TR1
   END
 ELSE
   COMMIT TRANSACTION TR1

Для вставки данных в таблицы SQL-сервера используется команда INSERT INTO:

INSERT INTO [название таблицы] (колонки) VALUES ([значения колонок])

Вторая часть комнады является необязательной для MS SQL Server 2003, но MS JET SQL без этого слова будет выдавать ошибку синтаксиса. Вставка обычно производиться целострочно, то есть в комнаде указываются все колонки таблицы и значения, которые нужно в них занести. Если же колонка имеет значение по умолчанию или разрешает пустое значения, то в команде вставки эту колонку можно не указывать. Команда INSERT INTO также разрешает указывать вносимые данные не по порядку следования колонок, но в этом случае нужно обозначить используемый порядок колонок.

-- В таблицу Users вставить строку с данными UserID = 9, UserName = 'Nikolay',
-- UserSurname = 'Gryzlov', DepartmentID = 4, PositionID = 2.
INSERT INTO Users VALUES (9, 'Nikolay', 'Gryzlov', 4, 2)
-- В таблицу Users вставить строку с данными UserID = 10, UserName = 'Nikolay',
-- UserSurname = 'Kozin', DepartmentID - значение по умолчанию, PositionID - не указано.
INSERT Users VALUES (10, 'Nikolay', 'Kozin', DEFAULT, NULL)
-- В таблицу Users вставить строку с данными UserName = 'Angrey', UserSurname = 'Medvedev',
-- UserID = 11, остальные значения по умолчанию
INSERT INTO Users (UserName, UserSurname, UserID) VALUES ('Angrey', 'Medvedev', 11)

Для того, чтобы изменить значение ячейки таблицы, используется команда UPDATE:

UPDATE [название таблицы] SET [имя колонки]=[значение колонок] WHERE [условие]

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

-- Установить всем должностям зарплату в 2000 единиц.
UPDATE Positions SET BaseSalary = 2000
-- Должностям с идентификатором 1 установить зарплату в 2500 единиц.
UPDATE Positions SET BaseSalary = 2500 WHERE PositionID = 1
-- Должностям с идентификатором 2 уменьшить зарплату на 30%.
UPDATE Positions SET BaseSalary = BaseSalary * 0.7 WHERE PositionID = 2
-- Установить всем должностям зарплату, равную (30 000 разделить на количество
-- сотрудников в организации)
UPDATE Positions SET BaseSalary = 30000 / (SELECT COUNT(UserID) FROM Users)

Удаление данных производится командой DELETE:

DELETE FROM [название таблицы] WHERE [условие]

Удаление данных обычно производится по какому-то критерию. Так как удаление данных — это достаточно опасная операция, то перед выполнением такой команды лучше всего произвести тестовую выборку командой SELECT, которая выведет в результат те данные, которые будут стерты. Если это то, что требуется, тогда можно смело заменять SELECT на DELETE и выполнять удаление данных.

-- Удалить пользователя с идентификатором 10
-- В режиме отладки рекомедуется использовать команду SELECT,
-- чтобы знать, какие данные будут стерты:
-- SELECT UserID FROM Users WHERE UserID = 10
DELETE FROM Users WHERE UserID = 10
-- Удалить всех польователей отдела Production
DELETE Users FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID
 WHERE Departments.DepartmentName = 'Production'
-- Удалить всех пользователей
DELETE FROM Users

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

Более быстрая команда для очистки таблицы — это TRUNCATE TABLE.

TRUNCATE TABLE [название таблицы]

Пример удаления всех данных:

-- Очистить таблицу Users
TRUNCATE TABLE Users

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

-- Создать временную таблицу #TempTable, в которую скопировать содержание
-- колонки UserName таблицы Users
SELECT UserName INTO #TempTable FROM Users
-- Выбрать все записи временной таблицы #TempTable
SELECT * FROM #TempTable

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

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

Пример создания хранимой процедуры и хранимой функции:

-- Создание функции обновления зарплат
CREATE PROCEDURE usp_UpdateSalary AS UPDATE Positions SET BaseSalary = 2000
GO
-- Создание функции получения имени пользователя
CREATE FUNCTION usf_GetName (@UserID int) RETURNS varchar(255)
BEGIN
 IF @UserID IS NULL
   SET @UserID = 1
 RETURN (SELECT UserName + ' ' + UserSurname FROM Users WHERE UserID = @UserID)
END
GO
-- Обновление зарплат
EXEC TestDatabase.dbo.usp_UpdateSalary
-- Получение имени пользователя с идентификатором 2
SELECT TestDatabase.dbo.usf_GetName(2)

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

• производительность;
• общая логика для всез запросов;
• уменьшение трафика;
• безопасность — доступ пользователю дается не к таблице, а к процедуре;

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

• Избегать NOT — команды отрицания выполняются в несколько этапов, что увеличивает нагрузку на сервер.
• Избегать LIKE — этот оператор сравнения применяет более мягкие шаблоны сравнения, чем оператор =, что увеличивает необходимое число этапов фильтрации.
• Применять точные шаблоны поиска — применение подстановочных символов увеличивает время выполнения запроса, так как для проверки всех вариантов подстановки требуется дополнительные ресурсы сервера.
• Избегать ORDER — команда сортировки требует упорядочивания строк таблицы вывода, что задерживает получение результата.

Виталий Бочкарев

Схема элементов синтаксиса MS SQL

В данной статье описаны основы t-sql и примеры использования основных программируемых элементов, а именно: функций, определенных пользователем (UDF), триггеры, хранимые процедуры, описаны курсоры, циклы, описан принцип обработки ошибок.


Просто об объектах T-SQL:

• Таблица — хранит данные.
• Вьюха — содержит в себе сложный запрос к одной или нескольким таблицам, вьюхам, табличным функциям. В отличие от таблиц, не хранит в себе данные. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную, локальную временную таблицу нельзя создавать в теле вьюхи. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
• Табличная функция — похожа на вьюху, но основное отличие — принимает входные параметры и в зависимости от этих параметров запускается обработка данных, которая возращает тот или иной набор данных. Нельзя использовать внутри функции операции обработки данных (Insert, Update, Delete) над физическими таблицами. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
• Скалярная функция — Принимает входные параметры, использует запросы к таблицам, вьюхам, табличным функциям и возращает одно значение (заданного типа). Нельзя использовать внутри функции операции обработки данных (Insert, Update, Delete) над физическими таблицами. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
• Хранимая процедура — набор действий над данными. В основном требуется для того, чтобы взять данные из таблиц, вьюх, табличных функций, обработать данные и положить их в целевую таблицу. Можно использовать операции обработки данных (Insert, Update, Delete). Для хранения промежуточных результатов в хранимой процедуре используются как временные таблицы (в основном локальные), так и табличные переменные. Эффективней использовать временные таблицы.

В данной статье будут подробно рассмотрены основные объекты, создаваемые на языке T-SQL, которые покрывают 80% всех задач по обработке данных (триггеры, хранимые процедуры, табличные функции):

Таблицы базы данных, в которых хранятся собственно данные, содержат:

• cтроки: каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта;
• cтолбцы: каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер

Представления (виртуальные таблицы) или Views — создаются для отображения данных из таблиц.
Подобно реальным таблицам, Views содержат именованные столбцы и строки с данными. Для конечных пользователей представление выглядит как таблица, но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект

Хранимые процедуры (Stored Procedures) – это группа команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое
Хранимые процедуры (Stored Procedure) являются программами, хранящимися в базе данных и выполняющими различные действия, обычно с данными из базы данных, хотя процедуры могут и не осуществлять никаких обращений к базе. К хранимым процедурам могут обращаться любые программы, работающие с базой данных, к ним также могут обращаться и другие хранимые процедуры и триггеры. Допустима рекурсия, когда хранимая процедура обращается к самой себе. Хранимые процедуры выполняются на стороне сервера, а не на стороне клиента. Во многих случаях это может резко снизить сетевой трафик при решении различных задач работы с большой по объему базой данных и повысить производительность системы.

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

Функции, определенные пользователем (user defined functions, UDF) — это конструкции, содержащие исполняемый код. Функция выполняет какие-либо действия над данными и возвращает некоторое значение/набор данных. К функциям можно обращаться из триггеров, хранимых процедура и из других программных компонентов.

Немного о циклах, условиях в T-SQL

IF … ELSE

Пример использования IF

Использование ELSE

Пример

Временная таблица и табличная переменная

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

• Локальная временная таблица имеет префикс в виде одной решетки ‘#local_temp_table’ — видна в текущем соединении пользователя и удаляется, как только пользователь отсоединился от экземпляра (instance) MS SQL Server.
• Глобальная временная таблица имеет префикс в виде двух решеток ‘##global_temp_table’ — видна для любого пользователя после ее создания и удаляется, когда все пользователи, ссылающиеся на таблицу, отсоединятся от экземпляра MS SQL Server.

Создание локальной временной таблицы (Local Temp Tables)

Пример

Создание глобальной временной таблицы (Global Temp Tables)

Создание табличной переменной (Table Variables)

Пример

Переменные

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

Локальные переменные

Локальные переменные служат для хранения временной информации. Локальные переменные объявляются командой DECLARE и существуют лишь на время выполнения пакета. После завершения пакета вы уже не сможете обратиться к ним. Значения переменных задаются командой SELECT или SET. Если значение присваивается одной переменной, эти команды эквивалентны. Команда SELECT может присвоить значения сразу нескольким переменным. В следующем примере команда DECLARE объявляет две переменные, одна из которых задается командой SELECT, а другая — командой SET. Затем происходит чтение и вывод значения обеих переменных:

Вы можете присваивать значения нескольким переменным в одной команде SELECT и объявлять несколько переменных в одной команде DECLARE. Начиная с SQL Server 2008, можно присваивать значение переменной прямо в команде DECLARE. Например:

А также вы можете использовать математические операции типа +=, -= и подобные, которые прибавляют, вычитают некое значение от переменной, которой потом и присваивается вычисленный результат. Например:

Глобальные переменные

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

Пример вывода значения локальной или глобальной переменной:

Объявление и инициализация переменной

Для того, чтобы использовать переменную, ее сначала нужно объявить, а затем можно присвоить ей значение.

Использование TRY … CATCH — обработка ошибок

Конструкция TRY…CATCH позволяет перехватывать все ошибки исполнения кода. За блоком TRY сразу же должен следовать блок CATCH. Если ошибки в блоке TRY не возникают, то после выполнения последней инструкции в блоке TRY управление передается инструкции, расположенной сразу после инструкции END CATCH. Если же в коде, заключенном в блоке TRY, происходит ошибка, управление передается первой инструкции в соответствующем блоке CATCH. Если инструкция END CATCH является последней инструкцией хранимой процедуры или триггера, управление передается обратно инструкции, вызвавшей эту хранимую процедуру или триггер.

Когда код в блоке CATCH завершен, управление передается инструкции, стоящей сразу после инструкции END CATCH. Ошибки, обнаруженные в блоке CATCH, не передаются в вызывающее приложение. Если какие-либо сведения об ошибке должны быть возвращены в приложение, код в блоке CATCH должен выполнить передачу этой ошибки, используя любые доступные механизмы, такие как результирующие наборы инструкции SELECT либо инструкции RAISERROR и PRINT.

Синтаксис:

Функции по работе с ошибками:

• ERROR_NUMBER() — возвращает номер ошибки вне зависимости от числа запусков и места запуска в пределах блока CATCH.
• ERROR_SEVERITY() — При вызове в блоке CATCH возвращает серьезность сообщения об ошибке, вызвавшего запуск блока CATCH. Возвращает значение NULL в случае вызова вне блока CATCH.
• ERROR_STATE() — При вызове в блоке CATCH возвращает номер состояния сообщения об ошибке, вызвавшей запуск блока CATCH.
• ERROR_PROCEDURE() — Возвращает имя хранимой процедуры или триггера, в которых произошла ошибка, вызвавшая запуск блока CATCH конструкции TRY…CATCH.
• ERROR_LINE() — Возвращает номер строки, в которой возникла ошибка, приведшая к активации блока CATCH конструкции TRY…CATCH.
• ERROR_MESSAGE() — При вызове в блоке CATCH возвращает полный текст сообщения об ошибке, запустившей блок CATCH. Текст содержит значения подставляемых параметров, таких как длина, имена объектов или время.

Пример:

Использование цикла WHILE

Инструкция WHILE повторяет инструкцию или блок инструкций до тех пор, пока указанное условие выполняется.

Пример:

Курсор

Курсор (current set of record) – временный набор строк, которые можно перебирать последовательно, с первой до последней. При работе с курсорами используются следующие команды.

Объявление курсора: Любой курсор создается на основе некоторого оператора SELECT.

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

Чтение следующей строки из курсора:

Пример использования курсора

WITH конструкция в MS SQL

Для того, чтобы обработать данные обычным запросом SQL, необходимо приложить «умственные усилия», чтобы запрос оптимально работал, чтобы выдавал правильный результат, чтобы был понятен другим членам команды. Это требует значительных временных ресурсов, что при работе не всегда есть. На помощь может прийти конструкция WITH.
Суть: мы разбиваем единую задачу на блоки, каждый блок реализуется подзапросом. Созданные подзапросы можно использовать в последующих подзапросах. Созданные подзапросы (все или некоторые) используются в главном запросе, который и возращает результат.

Схема WITH конструкции:

Пример WITH конструкции для MS SQL:

Схема использования конструкции WITH в VIEW (во вьюхе)

Триггеры

Триггеры – это предварительно определенное действие или последовательность действий, автоматически осуществляемых при выполнении операций обновления, добавления или удаления данных.
Исключительно важно в этом определении слово «автоматически». Ни пользователь, ни приложение не могут активизировать триггер, он выполняется автоматически, когда пользователь или приложение выполняют с базой данных определенные действия.
Триггер – это специальный вид хранимой процедуры. Триггеры обеспечивают проверку любых изменений на корректность, прежде чем эти изменения будут приняты.
Каждый триггер привязывается к конкретной таблице. Все производимые им модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушения целостности данных происходит откат этой транзакции. Тем самым внесение изменений запрещается. Отменяются также все изменения, уже сделанные триггером.
Создать триггер может только владелец базы данных. Это ограничение позволяет избежать случайного изменения структуры таблиц, способов связи с ними других объектов и т.п.

Компоненты триггера

1. Ограничения, для реализации которых создается триггер.
2. Событие, которое будет характеризовать возникновение ситуации, требующей проверки ограничений. Триггерные события чаще всего связаны с изменением состояния базы данных и состоят из вставки, удаления и обновления строк в таблице. События могут учитываться и дополнительные условия (например, добавление записи только с отрицательным значением).
3. Предусмотренное действие осуществляется за счет выполнения процедуры или последовательности процедур, с помощью которых реализуется логика, требуемая для реализации ограничений.

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

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

Типы триггеров

Существует три типа триггеров:

1. Insert – определяет действия, которые будут выполняться после добавления новой записи в таблицу.
2. Update – определяет действия, которые будут выполняться после изменения записи таблицы.
3. Delete – определяет действия, которые будут выполняться после удаления записи из таблиц.

Часто в СУБД определяется большее число событий, с которыми можно связать триггеры. Например, до вставки, после вставки, до изменения, после изменения и т.д.

Структура триггера

Примеры триггеров

Хранимые процедуры

Хранимая процедура (Stored procedure) – программа, которая выполняется внутри базы данных и может предпринимать сложные действия на основе информации, задаваемой пользователем. Поскольку хранимые процедуры выполняются непосредственно на сервере базы данных, обеспечивается более высокое быстродействие, чем при выполнении тех же операций средствами клиента базы данных.
Хранимая процедура объединяет запросы и процедурную логику (операторы присваивания, логического ветвления и т.п.) и хранится в базе данных.
Одна процедура может быть использована в любом количестве клиентских приложений, что позволяет существенно сэкономить трудозатраты на создание прикладного программного обеспечения и эффективно применять стратегию повторного использования кода. Так же, как и любые процедуры в стандартных языках программирования, хранимые процедуры могут иметь входные и выходные параметры или не иметь их.
Преимущества выполнения в базе данных хранимых процедур вместо отдельных команд Transact SQL:

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

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

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

Структура хранимой процедуры

Пример хранимой процедуры

Функции, определенные пользователем (UDF)

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

• хранимые процедуры
• функции, определенные пользователем (User Defined Functions, UDF)

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

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

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

• Делают возможным использование модульного программирования;
• Позволяют ускорить выполнение;
• Позволяют уменьшить сетевой трафик.

Виды табличных функций

• Табличная функция — функция, в которой задается структура результирующей таблицы. В теле функции выполняется запрос, который наполняет эту результирующую таблицу данными. После чего, возвращается эта таблица.
• Inline-табличная функция — данный вид функции содержит комбинированный запрос (SELECT), который возвращает результирующий набор данных (таблицу). Обычно строится в формате WITH … SELECT …
• Скалярная функция — Функция, которая возвращает 1 значение. Значение может иметь различный формат данных.

Структура табличной функции

Пример табличной функции

Структура Inline табличной функции

Пример Inline табличной функции

Создание User-Defined Scalar Functions (скалярных функций)

Структура скалярной функции

Синтаксис

Пример

Использование OPENQUERY с набором данных (RESULT SET)

«Фиктивный» SELECT запрос для вызова хранимой процедуры из табличной функции

Если Вам каким-то чудом потребовалось вызвать хранимую процедуру из табличной функции, то можно создать некоторый фиктивный запрос к хранимой процедуре, а на выход процедуры после завершения всех действий над таблицами (INSERT, UPDATE, DELETE) подать 1 техническую строку. Данный метод является вынужденным и не совсем корректным, но в критических ситуациях может помочь. А так, лучше всегда избегайте OPENQUERY для вызова объектов, т.к. теряется прозрачность зависимостей объекта.

Некоторые полезные скрипты для MS SQL

Получить выполняемые скрипты на сервере MS SQL

Получить дату и время модификации объектов в MS SQL

Получить настроенные Linked Servers

Настроить права доступа к объекту MS SQL

Общий синтаксис:

Рабочий пример:

Получить «негодяев», которые держат таблицу/вьюху и т.п. объекты

«Убить» сессии

Объекты, которые используют OPENQUERY

Дополнительные ресурсы

• Справочник Transact-SQL – основы для новичков
• Упражнения по SQL

T-SQL – Обзор

В 1970-х годах продукт под названием «SEQUEL», язык структурированного английского запроса, разработанный IBM, а затем SEQUEL, был переименован в «SQL», что означает «язык структурированных запросов».

В 1986 году SQL был одобрен ANSI (Американский национальный институт стандартов), а в 1987 году – ISO (Международная организация стандартов).

SQL – это язык структурных запросов, который является общим языком баз данных для всех продуктов СУБД. Различные поставщики СУБД разработали свой собственный язык баз данных, расширив SQL для своих собственных СУБД.

T-SQL расшифровывается как Transact Structure Query Language, который является продуктом Microsoft и является расширением языка SQL.

пример

MS SQL Server – SQL T-SQL

ORACLE – SQL PL-SQL

T-SQL – типы данных

Тип данных SQL Server – это атрибут, который определяет типы данных любого объекта. Каждый столбец, переменная и выражение имеют связанный тип данных в SQL Server. Эти типы данных можно использовать при создании таблиц. Вы можете выбрать конкретный тип данных для столбца таблицы в зависимости от ваших требований.

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

Точные числовые типы

Тип	От	к
BIGINT	-9.223.372.036.854.775.808	9.223.372.036.854.775.807
ИНТ	-2147483648	2147483647
SMALLINT	-32768	32767
TINYINT	0	255
немного	0	1
десятичный	-10 ^ 38 +1	10 ^ 38 –1
числовой	-10 ^ 38 +1	10 ^ 38 –1
Деньги	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

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

Приблизительные числовые типы

Тип	От	к
терка	-1,79E + 308	1,79E + 308
реальный	-3.40E + 38	3.40E + 38

Типы даты и времени

Тип	От	к
datetime (точность 3.33 миллисекунды)	1 января 1753 г.	31 декабря 9999 г.
smalldatetime (с точностью до 1 минуты)	1 января 1900 г.	6 июня 2079 г.
дата (с точностью до 1 дня. Представлено в SQL Server 2008)	1 января 0001	31 декабря 9999 г.
datetimeoffset (точность 100 наносекунд. Представлено в SQL Server 2008)	1 января 0001	31 декабря 9999 г.
datetime2 (точность 100 наносекунд. Представлено в SQL Server 2008 )	1 января 0001	31 декабря 9999 г.
время (точность 100 наносекунд. Представлено в SQL Server 2008 )	00: 00: 00,0000000	23: 59: 59,9999999

datetime (точность 3.33 миллисекунды)

smalldatetime (с точностью до 1 минуты)

дата (с точностью до 1 дня. Представлено в SQL Server 2008)

datetimeoffset (точность 100 наносекунд. Представлено в SQL Server 2008)

datetime2 (точность 100 наносекунд. Представлено в SQL Server 2008 )

время (точность 100 наносекунд. Представлено в SQL Server 2008 )

Строки символов

Sr.No	Тип и описание
1	голец Не символьные данные Unicode фиксированной длины с максимальной длиной 8000 символов.
2	VARCHAR Данные не-Unicode переменной длины, содержащие не более 8000 символов.
3	Varchar (макс) Данные переменной длины, отличные от Unicode, с максимальной длиной 231 символ (введено в SQL Server 2005).
4	текст Данные не-Unicode переменной длины с максимальной длиной 2 147 483 647 символов

голец

Не символьные данные Unicode фиксированной длины с максимальной длиной 8000 символов.

VARCHAR

Данные не-Unicode переменной длины, содержащие не более 8000 символов.

Varchar (макс)

Данные переменной длины, отличные от Unicode, с максимальной длиной 231 символ (введено в SQL Server 2005).

текст

Данные не-Unicode переменной длины с максимальной длиной 2 147 483 647 символов

Строки символов Unicode

Sr.No	Тип и описание
1	NCHAR Данные Unicode фиксированной длины с максимальной длиной 4000 символов.
2	NVARCHAR Данные Unicode переменной длины с максимальной длиной 4000 символов.
3	Нварчар (макс) Данные Unicode переменной длины с максимальной длиной 2 30 символов (введено в SQL Server 2005).
4	NTEXT Данные Unicode переменной длины с максимальной длиной 1 073 741 823 символа.

NCHAR

Данные Unicode фиксированной длины с максимальной длиной 4000 символов.

NVARCHAR

Данные Unicode переменной длины с максимальной длиной 4000 символов.

Нварчар (макс)

Данные Unicode переменной длины с максимальной длиной 2 ³⁰ символов (введено в SQL Server 2005).

NTEXT

Данные Unicode переменной длины с максимальной длиной 1 073 741 823 символа.

Двоичные строки

Sr.No	Тип и описание
1	двоичный Двоичные данные фиксированной длины с максимальной длиной 8000 байт.
2	VARBINARY Двоичные данные переменной длины с максимальной длиной 8000 байтов.
3	VARBINARY (макс) Двоичные данные переменной длины с максимальной длиной 2 31 байт (введено в SQL Server 2005).
4	образ Двоичные данные переменной длины с максимальной длиной 2 147 483 647 байт.

двоичный

Двоичные данные фиксированной длины с максимальной длиной 8000 байт.

VARBINARY

Двоичные данные переменной длины с максимальной длиной 8000 байтов.

VARBINARY (макс)

Двоичные данные переменной длины с максимальной длиной 2 ³¹ байт (введено в SQL Server 2005).

образ

Двоичные данные переменной длины с максимальной длиной 2 147 483 647 байт.

Другие типы данных

• sql_variant – хранит значения различных типов данных, поддерживаемых SQL Server, кроме text, ntext и timestamp.

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

• uniqueidentifier – хранит глобальный уникальный идентификатор (GUID).

• xml – хранит данные XML. Вы можете хранить экземпляры XML в столбце или переменной (введено в SQL Server 2005).

• курсор – ссылка на курсор.

• таблица – хранит набор результатов для последующей обработки.

• ierarchyid – переменная длина, системный тип данных, используемый для представления положения в иерархии (введено в SQL Server 2008).

sql_variant – хранит значения различных типов данных, поддерживаемых SQL Server, кроме text, ntext и timestamp.

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

uniqueidentifier – хранит глобальный уникальный идентификатор (GUID).

xml – хранит данные XML. Вы можете хранить экземпляры XML в столбце или переменной (введено в SQL Server 2005).

курсор – ссылка на курсор.

таблица – хранит набор результатов для последующей обработки.

ierarchyid – переменная длина, системный тип данных, используемый для представления положения в иерархии (введено в SQL Server 2008).

T-SQL – создание таблиц

Создание базовой таблицы включает в себя наименование таблицы и определение ее столбцов и типа данных каждого столбца.

Инструкция SQL Server CREATE TABLE используется для создания новой таблицы.

Синтаксис

Ниже приведен основной синтаксис оператора CREATE TABLE:

CREATE TABLE table_name( 
   column1 datatype, 
   column2 datatype, 
   column3 datatype, 
   ..... 
   columnN datatype, 
   PRIMARY KEY( one or more columns ));

CREATE TABLE – это ключевое слово, сообщающее системе баз данных, что вы хотите сделать. В этом случае вы хотите создать новую таблицу. Уникальное имя или идентификатор таблицы следует за оператором CREATE TABLE. Затем в скобках указывается список, определяющий каждый столбец таблицы и тип данных. Синтаксис становится более понятным для следующего примера.

Копия существующей таблицы может быть создана с использованием комбинации оператора CREATE TABLE и оператора SELECT. Вы можете проверить полную информацию в Создать таблицу, используя другую таблицу.

пример

В этом примере давайте создадим таблицу CUSTOMERS с ID в качестве первичного ключа, а NOT NULL – это ограничения, показывающие, что эти поля не могут быть NULL при создании записей в этой таблице.

CREATE TABLE CUSTOMERS( 
   ID   INT              NOT NULL, 
   NAME VARCHAR (20)     NOT NULL, 
   AGE  INT              NOT NULL, 
   ADDRESS  CHAR (25) , 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID));

Вы можете проверить, была ли ваша таблица создана успешно, просмотрев сообщение, отображаемое сервером SQL, в противном случае вы можете использовать следующую команду:

exec sp_columns CUSTOMERS

Приведенная выше команда производит следующий вывод.

TABLE_QUALIFIER   TABLE_OWNER   TABLE_NAME   COLUMN_NAME   DATA_TYPE   TYPE_NAME
   PRECISION   LENGTH SCALE   RADIX   NULLABLE   REMARKS   COLUMN_DEF   SQL_DATA_TYPE 
   SQL_DATETIME_SUB   CHAR_OCTET_LENGTH   ORDINAL_POSITION   IS_NULLABLE   SS_DATA_TYPE
   
TestDB    dbo    CUSTOMERS   ID        4    int      10   4    0      10     0
   NULL   NULL   4   NULL    NULL      1    NO       56 
   
TestDB    dbo    CUSTOMERS   NAME      12   varchar  20   20   NULL   NULL   0
   NULL   NULL   12   NULL   20        2    NO       39
  
TestDB    dbo    CUSTOMERS   AGE       4    int      10   4    0      10     0
   NULL   NULL   4   NULL    NULL      3    NO       56 
 
TestDB    dbo    CUSTOMERS   ADDRESS   1    char     25   25   NULL   NULL   1
   NULL   NULL   1   NULL    25   4    YES  39  

TestDB    dbo    CUSTOMERS   SALARY    3    decimal  18   20   2      10     1
   NULL   NULL   3   NULL    NULL      5    YES      106 

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

T-SQL – удаленные таблицы

Оператор DROP TABLE SQL Server используется для удаления определения таблицы и всех данных, индексов, триггеров, ограничений и спецификаций разрешений для этой таблицы.

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

Синтаксис

Ниже приведен основной синтаксис оператора DROP TABLE:

DROP TABLE table_name;

пример

Давайте сначала проверим таблицу CUSTOMERS, а затем удалим ее из базы данных –

Exec sp_columns CUSTOMERS;

Приведенная выше команда показывает следующую таблицу.

TABLE_QUALIFIER   TABLE_OWNER   TABLE_NAME   COLUMN_NAME   DATA_TYPE   TYPE_NAME
   PRECISION   LENGTH SCALE   RADIX   NULLABLE   REMARKS   COLUMN_DEF   SQL_DATA_TYPE 
   SQL_DATETIME_SUB   CHAR_OCTET_LENGTH   ORDINAL_POSITION   IS_NULLABLE   SS_DATA_TYPE
   
TestDB    dbo    CUSTOMERS   ID        4   int        10   4    0      10     0
   NULL   NULL   4   NULL    NULL      1   NO         56 
   
TestDB    dbo    CUSTOMERS   NAME      12  varchar    20   20   NULL   NULL   0
   NULL   NULL   12   NULL   20        2   NO         39
  
TestDB    dbo    CUSTOMERS   AGE       4   int        10   4    0      10     0
   NULL   NULL   4   NULL    NULL      3   NO         56 
 
TestDB    dbo    CUSTOMERS   ADDRESS   1   char       25   25   NULL   NULL   1
   NULL   NULL   1   NULL    25        4   YES        39  

TestDB    dbo    CUSTOMERS   SALARY   3   decimal     18   20   2      10     1
   NULL   NULL   3   NULL    NULL     5   YES         106 

Таблица CUSTOMERS доступна в базе данных, поэтому давайте ее отбросим. Ниже приведена команда для того же.

DROP TABLE CUSTOMERS; 
Command(s) completed successfully.

С помощью приведенной выше команды вы не получите никаких строк.

Exec sp_columns CUSTOMERS; 
No rowsdata will be displayed 

T-SQL – оператор INSERT

Оператор INSERT INTO SQL Server используется для добавления новых строк данных в таблицу в базе данных.

Синтаксис

Ниже приведены два основных синтаксиса оператора INSERT INTO.

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]   
VALUES (value1, value2, value3,...valueN); 

Где column1, column2, … columnN – это имена столбцов в таблице, в которые вы хотите вставить данные.

Вам не нужно указывать имя столбца (-ов) в запросе SQL, если вы добавляете значения для всех столбцов таблицы. Но убедитесь, что порядок значений в том же порядке, что и столбцы в таблице. Ниже приведен синтаксис SQL INSERT INTO –

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

пример

Следующие операторы создадут шесть записей в таблице CUSTOMERS –

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 
 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

Синтаксис

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

INSERT INTO CUSTOMERS VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

Все приведенные выше операторы приведут к следующим записям в таблице CUSTOMERS –

ID  NAME       AGE         ADDRESS              SALARY 
1   Ramesh     32          Ahmedabad            2000.00 
2   Khilan     25          Delhi                1500.00 
3   kaushik    23          Kota                 2000.00 
4   Chaitali   25          Mumbai               6500.00 
5   Hardik     27          Bhopal               8500.00 
6   Komal      22          MP                   4500.00 
7   Muffy      24          Indore               10000.00 

Заполните одну таблицу, используя другую таблицу

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

INSERT INTO first_table_name  
   SELECT column1, column2, ...columnN  
      FROM second_table_name 
      [WHERE condition];

T-SQL – оператор SELECT

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

Синтаксис

Ниже приведен основной синтаксис оператора SELECT –

SELECT column1, column2, columnN FROM table_name;

Где, column1, column2 … это поля таблицы, значения которых вы хотите получить. Если вы хотите получить все поля, доступные в этом поле, вы можете использовать следующий синтаксис:

SELECT * FROM table_name;

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи:

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           2000.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000.00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              8500.00 
6   Komal      22        MP                  4500.00 
7   Muffy      24        Indore              10000.00 

Следующая команда является примером, который извлекает поля ID, Имя и Зарплата клиентов, доступных в таблице CUSTOMERS –

SELECT ID, NAME, SALARY FROM CUSTOMERS; 

Приведенная выше команда выдаст следующий вывод.

ID  NAME          SALARY 
1   Ramesh        2000.00 
2   Khilan        1500.00 
3   kaushik       2000.00 
4   Chaitali      6500.00 
5   Hardik        8500.00 
6   Komal         4500.00 
7   Muffy         10000.00 

Если вы хотите получить все поля таблицы CUSTOMERS, используйте следующий запрос:

SELECT * FROM CUSTOMERS;

Выше будет производить следующий вывод.

ID  NAME       AGE       ADDRESS              SALARY 
1   Ramesh     32        Ahmedabad            2000.00 
2   Khilan     25        Delhi                1500.00 
3   kaushik    23        Kota                 2000.00 
4   Chaitali   25        Mumbai               6500.00 
5   Hardik     27        Bhopal               8500.00 
6   Komal      22        MP                   4500.00 
7   Muffy      24        Indore               10000.00 

T-SQL – оператор UPDATE

Запрос SQL Server UPDATE используется для изменения существующих записей в таблице.

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

Синтаксис

Ниже приведен основной синтаксис запроса UPDATE с предложением WHERE.

UPDATE table_name 
SET column1 = value1, column2 = value2...., columnN = valueN 
WHERE [condition];

Вы можете объединить N условий с помощью операторов И или ИЛИ.

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи:

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           2000.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000.00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              8500.00 
6   Komal      22        MP                  4500.00 
7   Muffy      24        Indore              10000.00 

Следующая команда является примером, который обновил бы АДРЕС для клиента с идентификатором 6 –

UPDATE CUSTOMERS 
SET ADDRESS = 'Pune' 
WHERE ID = 6; 

Таблица CUSTOMERS теперь будет иметь следующие записи –

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           2000.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000.00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              8500.00 
6   Komal      22        Pune                4500.00 
7   Muffy      24        Indore              10000.00 

Если вы хотите изменить все значения столбцов ADDRESS и SALARY в таблице CUSTOMERS, вам не нужно использовать предложение WHERE. ОБНОВЛЕНИЕ запроса будет следующим:

UPDATE CUSTOMERS 
SET ADDRESS = 'Pune', SALARY = 1000.00;

Таблица CUSTOMERS теперь будет иметь следующие записи.

ID  NAME       AGE       ADDRESS          SALARY 
1   Ramesh     32        Pune             1000.00 
2   Khilan     25        Pune             1000.00 
3   kaushik    23        Pune             1000.00 
4   Chaitali   25        Pune             1000.00 
5   Hardik     27        Pune             1000.00 
6   Komal      22        Pune             1000.00 
7   Muffy      24        Pune             1000.00 

T-SQL – УДАЛИТЬ Заявление

Запрос SQL Server DELETE используется для удаления существующих записей из таблицы.

Вы должны использовать предложение WHERE с запросом DELETE, чтобы удалить выбранные строки, в противном случае все записи будут удалены.

Синтаксис

Ниже приведен основной синтаксис запроса DELETE с предложением WHERE.

DELETE FROM table_name 
WHERE [condition]; 

Вы можете объединить N условий с помощью операторов И или ИЛИ.

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи:

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

Следующая команда является примером, который УДАЛИТ клиента, чей идентификатор 6 –

DELETE FROM CUSTOMERS 
WHERE ID = 6;

Таблица CUSTOMERS теперь будет иметь следующие записи.

ID  NAME       AGE       ADDRESS              SALARY 
1   Ramesh     32        Ahmedabad            2000.00 
2   Khilan     25        Delhi                1500.00 
3   kaushik    23        Kota                 2000.00 
4   Chaitali   25        Mumbai               6500.00 
5   Hardik     27        Bhopal               8500.00 
7   Muffy      24        Indore               10000.00 

Если вы хотите УДАЛИТЬ все записи из таблицы CUSTOMERS, вам не нужно использовать предложение WHERE. УДАЛИТЬ запрос будет следующим:

DELETE FROM CUSTOMERS;

Таблица CUSTOMERS теперь не будет иметь никакой записи.

T-SQL – предложение WHERE

Предложение MS SQL Server WHERE используется для указания условия при извлечении данных из одной таблицы или объединении с несколькими таблицами.

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

Предложение WHERE используется не только в операторе SELECT, но также в операторе UPDATE, DELETE и т. Д., Что мы рассмотрим в последующих главах.

Синтаксис

Ниже приведен основной синтаксис оператора SELECT с предложением WHERE –

SELECT column1, column2, columnN  
FROM table_name 
WHERE [condition]

Вы можете указать условие, используя сравнение или логические операторы, такие как>, <, =, LIKE, NOT и т. Д. Следующий пример прояснит эту концепцию.

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи:

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

Следующая команда представляет собой пример, который извлекает поля ID, Name и Salary из таблицы CUSTOMERS, где заработная плата превышает 2000.

SELECT ID, NAME, SALARY  
FROM CUSTOMERS 
WHERE SALARY > 2000;

Приведенная выше команда выдаст следующий вывод.

ID  NAME       SALARY 
4   Chaitali   6500.00 
5   Hardik     8500.00 
6   Komal      4500.00 
7   Muffy      10000.00

Следующая команда является примером, который извлекает поля ID, Имя и Зарплата из таблицы CUSTOMERS для клиента с именем «Hardik». Важно отметить, что все строки следует указывать в одинарных кавычках (”), тогда как числовые значения следует указывать без кавычек, как в примере выше –

SELECT ID, NAME, SALARY  
FROM CUSTOMERS 
WHERE NAME = 'Hardik';

Приведенная выше команда выдаст следующий вывод.

ID  NAME     SALARY 
5   Hardik   8500.00 

T-SQL – предложение LIKE

Предложение MS SQL Server LIKE используется для сравнения значения с аналогичными значениями с использованием подстановочных операторов. В сочетании с оператором LIKE используются два подстановочных знака:

• Знак процента (%)
• Подчеркивание (_)

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

Синтаксис

Ниже приведен основной синтаксис% и _.

SELECT *column-list FROM table_name 
WHERE column LIKE 'XXXX%' 
 
or   

SELECT *column-list FROM table_name 
WHERE column LIKE '%XXXX%'  

or  

SELECT *column-list FROM table_name 
WHERE column LIKE 'XXXX_'  

or  

SELECT *column-list FROM table_name 
WHERE column LIKE '_XXXX'  

or  

SELECT  *column-list FROM table_name 
WHERE column LIKE '_XXXX_' 

Вы можете объединить N условий с помощью операторов И или ИЛИ. XXXX может быть любым числовым или строковым значением.

пример

Ниже приведен ряд примеров, показывающих, где часть WHERE имеет другое предложение LIKE с операторами «%» и «_».

Sr.No	Заявление и описание
1	Где заработная плата, как “200%” Находит любые значения, которые начинаются с 200
2	ГДЕ НАЛОГОВАЯ НРАВИТСЯ “% 200%” Находит любые значения, которые имеют 200 в любой позиции
3	ГДЕ НАЛОГОВАЯ НРАВИТСЯ ‘_00%’ Находит любые значения, которые имеют 00 во второй и третьей позиции
4	ГДЕ НАЛОГОВАЯ НРАВИТСЯ ‘2 _% _%’ Находит любые значения, которые начинаются с 2 и имеют длину не менее 3 символов
5	ГДЕ НАГРАДНАЯ НРАВИТСЯ ‘% 2’ Находит любые значения, которые заканчиваются на 2
6	ГДЕ НАЛОГОВАЯ НРАВИТСЯ ‘_2% 3’ Находит любые значения, которые имеют 2 во второй позиции и заканчиваются на 3
7	Где заработная плата, как “2___3” Находит любые значения в пятизначном числе, которые начинаются с 2 и заканчиваются на 3

Где заработная плата, как “200%”

Находит любые значения, которые начинаются с 200

ГДЕ НАЛОГОВАЯ НРАВИТСЯ “% 200%”

Находит любые значения, которые имеют 200 в любой позиции

ГДЕ НАЛОГОВАЯ НРАВИТСЯ ‘_00%’

Находит любые значения, которые имеют 00 во второй и третьей позиции

ГДЕ НАЛОГОВАЯ НРАВИТСЯ ‘2 _% _%’

Находит любые значения, которые начинаются с 2 и имеют длину не менее 3 символов

ГДЕ НАГРАДНАЯ НРАВИТСЯ ‘% 2’

Находит любые значения, которые заканчиваются на 2

ГДЕ НАЛОГОВАЯ НРАВИТСЯ ‘_2% 3’

Находит любые значения, которые имеют 2 во второй позиции и заканчиваются на 3

Где заработная плата, как “2___3”

Находит любые значения в пятизначном числе, которые начинаются с 2 и заканчиваются на 3

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

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

SELECT * FROM CUSTOMERS 
WHERE SALARY LIKE '200%'; 

Приведенная выше команда выдаст следующий вывод.

ID   NAME     AGE     ADDRESS       SALARY 
1    Ramesh   32      Ahmedabad     2000.00 
3    kaushik  23      Kota          2000.00

T-SQL – предложение ORDER BY

Предложение MS SQL Server ORDER BY используется для сортировки данных в порядке возрастания или убывания на основе одного или нескольких столбцов. В некоторых запросах на сортировку базы данных по умолчанию в порядке возрастания.

Синтаксис

Ниже приведен основной синтаксис предложения ORDER BY.

SELECT column-list  
FROM table_name  
[WHERE condition]  
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

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

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи:

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

Следующая команда является примером, который сортирует результат в порядке возрастания по ИМЯ и ЗАПИСЬ.

SELECT * FROM CUSTOMERS 
   ORDER BY NAME, SALARY 

Приведенная выше команда выдаст следующий вывод.

ID  NAME       AGE       ADDRESS           SALARY 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 
3   kaushik    23        Kota              2000.00 
2   Khilan     25        Delhi             1500.00 
6   Komal      22        MP                4500.00 
7   Muffy      24        Indore            10000.00 
1   Ramesh     32        Ahmedabad         2000.00 

Следующая команда является примером, который сортирует результат в порядке убывания по ИМЯ.

SELECT * FROM CUSTOMERS 
   ORDER BY NAME DESC

Приведенная выше команда даст следующий результат –

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00
7   Muffy      24        Indore             10000.00  
6   Komal      22        MP                 4500.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00
5   Hardik     27        Bhopal             8500.00
4   Chaitali   25        Mumbai             6500.00  

T-SQL – предложение GROUP BY

Предложение SQL Server GROUP BY используется совместно с оператором SELECT для организации идентичных данных в группы.

Предложение GROUP BY следует за предложением WHERE в инструкции SELECT и предшествует предложению ORDER BY.

Синтаксис

Ниже приведен основной синтаксис предложения GROUP BY. Предложение GROUP BY должно соответствовать условиям в предложении WHERE и должно предшествовать предложению ORDER BY, если оно используется.

SELECT column1, column2 
FROM table_name 
WHERE [ conditions ] 
GROUP BY column1, column2 
ORDER BY column1, column2 

пример

Предположим, что таблица CUSTOMERS содержит следующие записи:

ID  NAME       AGE       ADDRESS          SALARY 
1   Ramesh     32        Ahmedabad        2000.00 
2   Khilan     25        Delhi            1500.00 
3   kaushik    23        Kota             2000.00 
4   Chaitali   25        Mumbai           6500.00 
5   Hardik     27        Bhopal           8500.00 
6   Komal      22        MP               4500.00 
7   Muffy      24        Indore           10000.00 

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

SELECT NAME, SUM(SALARY) as [sum of salary] FROM CUSTOMERS 
   GROUP BY NAME;

Приведенная выше команда выдаст следующий вывод.

NAME        sum of salary 
Chaitali    6500.00 
Hardik      8500.00 
kaushik     2000.00 
Khilan      1500.00 
Komal       4500.00 
Muffy       10000.00 
Ramesh      2000.00

Давайте теперь рассмотрим следующую таблицу CUSTOMERS, имеющую следующие записи с повторяющимися именами.

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00 
2   Khilan     25        Delhi             1500.00 
3   kaushik    23        Kota              2000.00 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 
6   Komal      22        MP                4500.00 
7   Muffy      24        Indore            10000.00 

Если мы хотим узнать общую сумму заработной платы по каждому клиенту, то следующим будет запрос GROUP BY.

SELECT NAME, SUM(SALARY) as [sum of salary] FROM CUSTOMERS 
   GROUP BY NAME 

Приведенная выше команда выдаст следующий вывод.

NAME        sum of salary 
Hardik      8500.00 
kaushik     8500.00 
Komal       4500.00 
Muffy       10000.00 
Ramesh      3500.00 

T-SQL – предложение DISTINCT

Ключевое слово DISTINCT для MS SQL Server используется вместе с оператором SELECT, чтобы исключить все дублирующиеся записи и извлечь только уникальные записи.

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

Синтаксис

Ниже приведен основной синтаксис ключевого слова DISTINCT для устранения дублирующихся записей.

SELECT DISTINCT column1, column2,.....columnN  
FROM table_name 
WHERE [condition] 

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

Давайте посмотрим, как следующий запрос SELECT возвращает дубликаты записей заработной платы.

SELECT SALARY FROM CUSTOMERS 
   ORDER BY SALARY 

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

SALARY 
1500.00 
2000.00 
2000.00 
4500.00 
6500.00 
8500.00 
10000.00

Давайте теперь используем ключевое слово DISTINCT с вышеупомянутым запросом SELECT и посмотрим результат.

SELECT DISTINCT SALARY FROM CUSTOMERS 
   ORDER BY SALARY 

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

SALARY 
1500.00 
2000.00 
4500.00 
6500.00 
8500.00 
10000.00 

T-SQL – Соединение таблиц

Предложение MS SQL Server Joins используется для объединения записей из двух или более таблиц в базе данных. JOIN – это средство для объединения полей из двух таблиц с использованием значений, общих для каждой.

Рассмотрим следующие две таблицы: (а) таблица CUSTOMERS выглядит следующим образом –

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           2000.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000.00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              8500.00 
6   Komal      22        MP                  4500.00 
7   Muffy      24        Indore              10000.00 

(б) Другая таблица – это ЗАКАЗЫ в следующем порядке:

OID  DATE                       CUSTOMER_ID        AMOUNT 
100  2009-10-08 00:00:00.000    3                  1500.00 
101  2009-11-20 00:00:00.000    2                  1560.00 
102  2009-10-08 00:00:00.000    3                  3000.00 
103  2008-05-20 00:00:00.000    4                  2060.00 

Давайте объединим эти две таблицы в нашем операторе SELECT следующим образом:

SELECT ID, NAME, AGE, AMOUNT 
   FROM CUSTOMERS, ORDERS 
   WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID 
OR 
SELECT A.ID, A.NAME, A.AGE, B.AMOUNT 
   FROM CUSTOMERS A inner join  ORDERS B on A.ID = B.Customer_ID 

Приведенная выше команда выдаст следующий вывод.

ID   NAME      AGE    AMOUNT 
2    Khilan    25     1560.00 
3    kaushik   23     1500.00 
3    kaushik   23     3000.00 
4    Chaitali  25     2060.00 

Заметно, что соединение выполняется в предложении WHERE. Для объединения таблиц можно использовать несколько операторов, например =, <,>, <>, <=,> =,! =, BETWEEN, LIKE и NOT; все они могут быть использованы для объединения таблиц. Однако наиболее распространенным оператором является символ равенства.

MS SQL Server Типы соединения –

В MS SQL Server доступны разные типы объединений –

• INNER JOIN – возвращает строки, если в обеих таблицах есть совпадение.

• LEFT JOIN – возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.

• RIGHT JOIN – возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.

• FULL JOIN – возвращает строки, если в одной из таблиц есть совпадение.

• SELF JOIN – используется для соединения таблицы с самим собой, как если бы эта таблица была двумя таблицами, временно переименовывая хотя бы одну таблицу в операторе MS SQL Server.

• CARTESIAN JOIN – Возвращает декартово произведение наборов записей из двух или более объединенных таблиц.

INNER JOIN – возвращает строки, если в обеих таблицах есть совпадение.

LEFT JOIN – возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.

RIGHT JOIN – возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.

FULL JOIN – возвращает строки, если в одной из таблиц есть совпадение.

SELF JOIN – используется для соединения таблицы с самим собой, как если бы эта таблица была двумя таблицами, временно переименовывая хотя бы одну таблицу в операторе MS SQL Server.

CARTESIAN JOIN – Возвращает декартово произведение наборов записей из двух или более объединенных таблиц.

T-SQL – подзапросы

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

Подзапросы могут использоваться с операторами SELECT, INSERT, UPDATE и DELETE вместе с такими операторами, как =, <,>,> =, <=, IN, BETWEEN и т. Д.

Есть несколько правил, которым должны следовать подзапросы:

• Вы должны заключить подзапрос в скобки.

• Подзапрос должен включать в себя предложение SELECT и предложение FROM.

• Подзапрос может включать необязательные предложения WHERE, GROUP BY и HAVING.

• Подзапрос не может включать предложения COMPUTE или FOR BROWSE.

• Вы можете включить предложение ORDER BY, только если включено предложение TOP.

• Вы можете вкладывать подзапросы до 32 уровней.

Вы должны заключить подзапрос в скобки.

Подзапрос должен включать в себя предложение SELECT и предложение FROM.

Подзапрос может включать необязательные предложения WHERE, GROUP BY и HAVING.

Подзапрос не может включать предложения COMPUTE или FOR BROWSE.

Вы можете включить предложение ORDER BY, только если включено предложение TOP.

Вы можете вкладывать подзапросы до 32 уровней.

Подзапросы с оператором SELECT

Синтаксис

Подзапросы чаще всего используются с оператором SELECT. Ниже приведен основной синтаксис.

SELECT column_name [, column_name ] 
FROM   table1 [, table2 ] 
WHERE  column_name OPERATOR 
   (SELECT column_name [, column_name ] 
   FROM table1 [, table2 ] 
   [WHERE]) 

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

Давайте применим следующий подзапрос с оператором SELECT.

SELECT *  
   FROM CUSTOMERS
   WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)

Приведенная выше команда выдаст следующий вывод.

ID  NAME       AGE       ADDRESS          SALARY 
4   Chaitali   25        Mumbai           6500.00 
5   Hardik     27        Bhopal           8500.00 
7   Muffy      24        Indore           10000.00 

Подзапросы с оператором INSERT

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

Синтаксис

Ниже приведен основной синтаксис.

INSERT INTO table_name [ (column1 [, column2 ]) ] 
   SELECT [ *|column1 [, column2 ] 
   FROM table1 [, table2 ] 
   [ WHERE VALUE OPERATOR ]

пример

Рассмотрим таблицу CUSTOMERS_BKP с такой же структурой, что и таблица CUSTOMERS. Ниже приведен синтаксис для копирования полной таблицы CUSTOMERS в CUSTOMERS_BKP.

INSERT INTO CUSTOMERS_BKP 
   SELECT * FROM CUSTOMERS  
   WHERE ID IN (SELECT ID FROM CUSTOMERS)

Подзапросы с оператором UPDATE

Подзапрос может использоваться вместе с оператором UPDATE. Можно использовать один или несколько столбцов в таблице при использовании подзапроса с оператором UPDATE.

Синтаксис

Ниже приведен основной синтаксис.

UPDATE table 
SET column_name = new_value 
[ WHERE OPERATOR [ VALUE ] 
   (SELECT COLUMN_NAME 
   FROM TABLE_NAME) 
   [ WHERE) ] 

пример

Предположим, у нас есть таблица CUSTOMERS_BKP, которая является резервной копией таблицы CUSTOMERS.

Следующий пример команды обновляет SALARY в таблице CUSTOMERS в 0,25 раза для всех клиентов, чей возраст больше или равен 27.

UPDATE CUSTOMERS 
   SET SALARY = SALARY * 0.25 
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )

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

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           500.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000.00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              2125.00 
6   Komal      22        MP                  4500.00 
7   Muffy      24        Indore              10000.00 

Подзапросы с оператором DELETE

Подзапрос может использоваться вместе с оператором DELETE, как и любые другие операторы, упомянутые выше.

Синтаксис

Ниже приведен основной синтаксис.

DELETE FROM TABLE_NAME 
[ WHERE OPERATOR [ VALUE ] 
   (SELECT COLUMN_NAME 
   FROM TABLE_NAME) 
   [ WHERE) ] 

пример

Предположим, у нас есть таблица CUSTOMERS_BKP, которая является резервной копией таблицы CUSTOMERS.

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

DELETE FROM CUSTOMERS 
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >=27 )

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

ID  NAME       AGE       ADDRESS          SALARY 
2   Khilan     25        Delhi            1500.00 
3   kaushik    23        Kota             2000.00 
4   Chaitali   25        Mumbai           6500.00  
6   Komal      22        MP               4500.00 
7   Muffy      24        Indore           10000.00 

T-SQL – хранимые процедуры

Хранимая процедура MS SQL Server используется для экономии времени на написание кода снова и снова путем его сохранения в базе данных, а также для получения требуемого результата путем передачи параметров.

Синтаксис

Ниже приведен основной синтаксис создания хранимой процедуры.

Create procedure <procedure_Name> 
As 
Begin 
<SQL Statement> 
End 
Go

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

Следующая команда является примером, который извлекает все записи из таблицы CUSTOMERS в базе данных Testdb.

CREATE PROCEDURE SelectCustomerstabledata 
AS 
SELECT * FROM Testdb.Customers 
GO

Приведенная выше команда выдаст следующий вывод.

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00 
2   Khilan     25        Delhi             1500.00 
3   kaushik    23        Kota              2000.00 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 
6   Komal      22        MP                4500.00 
7   Muffy      24        Indore            10000.00 

T-SQL – Транзакции

Транзакция – это единица работы, выполняемая с базой данных. Транзакции – это единицы или последовательности работы, выполняемые в логическом порядке, либо вручную пользователем, либо автоматически какой-либо программой базы данных.

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

Практически вы объедините множество SQL-запросов в группу и выполните все их вместе как часть транзакции.

Свойства сделок

Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID –

• Атомарность – гарантирует, что все операции внутри рабочего блока успешно завершены; в противном случае транзакция прерывается в точке сбоя, а предыдущие операции возвращаются в прежнее состояние.

• Согласованность – Гарантирует, что база данных корректно меняет состояние после успешно принятой транзакции.

• Изоляция – позволяет транзакциям работать независимо друг от друга и быть прозрачными друг для друга.

• Долговечность – Гарантирует, что результат или результат совершенной транзакции сохраняется в случае сбоя системы.

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

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

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

Долговечность – Гарантирует, что результат или результат совершенной транзакции сохраняется в случае сбоя системы.

Контроль транзакций

Для управления транзакциями используются следующие команды:

• COMMIT – сохранить изменения.

• ROLLBACK – откат изменений.

• SAVEPOINT – Создает точки в группах транзакций, в которых выполняется ROLLBACK.

• SET TRANSACTION – помещает имя в транзакцию.

COMMIT – сохранить изменения.

ROLLBACK – откат изменений.

SAVEPOINT – Создает точки в группах транзакций, в которых выполняется ROLLBACK.

SET TRANSACTION – помещает имя в транзакцию.

Команды управления транзакциями используются только с командами DML INSERT, UPDATE и DELETE. Их нельзя использовать при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.

Чтобы использовать команды управления транзакциями в MS SQL Server, мы должны начинать транзакцию с команды ‘begin trans’ или начинать транзакцию, в противном случае эти команды не будут работать.

Команда COMMIT

Команда COMMIT – это команда транзакций, используемая для сохранения изменений, вызванных транзакцией, в базу данных. Эта команда сохраняет все транзакции в базе данных с момента последней команды COMMIT или ROLLBACK.

Синтаксис

Ниже приведен синтаксис команды COMMIT.

COMMIT; 

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00 
2   Khilan     25        Delhi             1500.00 
3   kaushik    23        Kota              2000.00 
4   Chaitali   25        Mumbai            6500.00 
5   Hardik     27        Bhopal            8500.00 
6   Komal      22        MP                4500.00 
7   Muffy      24        Indore            10000.00 

В следующем примере команды удаляются записи из таблицы, имеющие возраст = 25, а затем фиксируются изменения в базе данных.

Begin Tran 
DELETE FROM CUSTOMERS 
   WHERE AGE = 25 
COMMIT 

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

ID  NAME       AGE       ADDRESS           SALARY 
1   Ramesh     32        Ahmedabad         2000.00
3   kaushik    23        Kota              2000.00
5   Hardik     27        Bhopal            8500.00 
6   Komal      22        MP                4500.00 
7   Muffy      24        Indore            10000.00 

Команда ROLLBACK

Команда ROLLBACK – это команда транзакций, используемая для отмены транзакций, которые еще не были сохранены в базе данных. Эта команда может использоваться только для отмены транзакций с момента выполнения последней команды COMMIT или ROLLBACK.

Синтаксис

Ниже приведен синтаксис команды ROLLBACK.

ROLLBACK

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00 

Следующий пример команды удалит записи из таблицы, имеющие возраст = 25, а затем откатит изменения в базе данных.

Begin Tran 
DELETE FROM CUSTOMERS 
   WHERE AGE = 25; 
ROLLBACK

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

ID  NAME       AGE       ADDRESS          SALARY 
1   Ramesh     32        Ahmedabad        2000.00 
2   Khilan     25        Delhi            1500.00 
3   kaushik    23        Kota             2000.00 
4   Chaitali   25        Mumbai           6500.00 
5   Hardik     27        Bhopal           8500.00 
6   Komal      22        MP               4500.00 
7   Muffy      24        Indore           10000.00 

Команда SAVEPOINT

SAVEPOINT – это точка в транзакции, когда вы можете откатить транзакцию до определенной точки без отката всей транзакции.

Синтаксис

Ниже приведен синтаксис команды SAVEPOINT.

SAVE TRANSACTION SAVEPOINT_NAME

Эта команда служит только для создания SAVEPOINT среди операторов транзакций. Команда ROLLBACK используется для отмены группы транзакций.

Ниже приведен синтаксис отката к SAVEPOINT.

ROLLBACK TO SAVEPOINT_NAME

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

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи:

ID  NAME       AGE       ADDRESS          SALARY 
1   Ramesh     32        Ahmedabad        2000.00 
2   Khilan     25        Delhi            1500.00 
3   kaushik    23        Kota             2000.00 
4   Chaitali   25        Mumbai           6500.00 
5   Hardik     27        Bhopal           8500.00 
6   Komal      22        MP               4500.00 
7   Muffy      24        Indore           10000.00 

Ниже приводится серия операций –

Begin Tran 
SAVE Transaction SP1 
Savepoint created. 
DELETE FROM CUSTOMERS WHERE ID = 1  
1 row deleted. 
SAVE Transaction SP2 
Savepoint created. 
DELETE FROM CUSTOMERS WHERE ID = 2 
1 row deleted.
SAVE Transaction SP3 
Savepoint created. 
DELETE FROM CUSTOMERS WHERE ID = 3 
1 row deleted.

Три удаления уже произошли, однако мы передумали и решили откатиться к SAVEPOINT, которую мы определили как SP2. Поскольку SP2 был создан после первого удаления, последние два удаления отменены –

ROLLBACK Transaction SP2 
Rollback complete. 

Обратите внимание, что произошло только первое удаление, так как мы вернулись к SP2.

SELECT * FROM CUSTOMERS 

6 строк выбрано.

ID  NAME       AGE       ADDRESS          SALARY 
2   Khilan     25        Delhi        1500.00 
3   kaushik    23        Kota             2000.00 
4   Chaitali   25        Mumbai           6500.00 
5   Hardik     27        Bhopal           8500.00 
6   Komal      22        MP               4500.00 
7   Muffy      24        Indore           10000.00 

Команда SET TRANSACTION

Команда SET TRANSACTION может использоваться для запуска транзакции базы данных. Эта команда используется для указания признаков для следующей транзакции.

Синтаксис

Ниже приводится синтаксис для SET TRANSACTION.

SET TRANSACTION ISOLATION LEVEL <Isolationlevel_name>

T-SQL – индексы

Индексы – это специальные таблицы поиска, которые поисковая система базы данных может использовать для ускорения поиска данных. Проще говоря, индекс – это указатель на данные в таблице. Индекс в базе данных очень похож на индекс в конце книги.

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

Индекс помогает ускорить запросы SELECT и предложения WHERE, но замедляет ввод данных с помощью операторов UPDATE и INSERT. Индексы могут быть созданы или удалены без влияния на данные.

Создание индекса включает в себя инструкцию CREATE INDEX, которая позволяет указать имя индекса, указать таблицу и столбец или столбцы для индексации и указать, находится ли индекс в порядке возрастания или убывания.

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

Команда CREATE INDEX

Ниже приведен основной синтаксис CREATE INDEX.

Синтаксис

CREATE INDEX index_name ON table_name

Одноколонные индексы

Индекс с одним столбцом – это индекс, который создается на основе только одного столбца таблицы. Ниже приведен основной синтаксис.

Синтаксис

CREATE INDEX index_name 
ON table_name (column_name)

пример

CREATE INDEX singlecolumnindex 
ON customers (ID)

Уникальные индексы

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

Синтаксис

CREATE UNIQUE INDEX index_name 
on table_name (column_name)

пример

CREATE UNIQUE INDEX uniqueindex 
on customers (NAME)

Композитные индексы

Составной индекс – это индекс двух или более столбцов таблицы. Ниже приведен основной синтаксис.

Синтаксис

CREATE INDEX index_name on table_name (column1, column2) 

пример

CREATE INDEX compositeindex 
on customers (NAME, ID)

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

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

Неявные индексы

Неявные индексы – это индексы, которые автоматически создаются сервером базы данных при создании объекта. Индексы автоматически создаются для ограничений первичного ключа и уникальных ограничений.

Команда DROP INDEX

Индекс можно удалить с помощью команды MS SQL SERVER DROP. При отбрасывании индекса следует соблюдать осторожность, поскольку производительность может быть замедлена или улучшена.

Синтаксис

Ниже приведен основной синтаксис.

DROP INDEX tablename.index_name

Когда избегать индексов?

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

• Индексы не должны использоваться на маленьких столах.

• Таблицы с частыми крупными пакетными обновлениями или операциями вставки не должны индексироваться.

• Индексы не должны использоваться для столбцов, которые содержат большое количество значений NULL.

• Столбцы, которыми часто манипулируют, не должны индексироваться.

Индексы не должны использоваться на маленьких столах.

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

Индексы не должны использоваться для столбцов, которые содержат большое количество значений NULL.

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

T-SQL – Функции

MS SQL Server имеет много встроенных функций для выполнения обработки строковых или числовых данных. Ниже приведен список всех полезных встроенных функций SQL –

• Функция СЧЕТА SQL Server – агрегатная функция СЧЕТА SQL Server используется для подсчета количества строк в таблице базы данных.

• Функция SQL Server MAX – Функция агрегата SQL Server MAX позволяет выбрать максимальное (максимальное) значение для определенного столбца.

• Функция SQL Server MIN – агрегатная функция SQL Server MIN позволяет выбрать минимальное (минимальное) значение для определенного столбца.

• Функция SQL Server AVG – Функция агрегирования SQL Server AVG выбирает среднее значение для определенного столбца таблицы.

• Функция SQL Server SUM – агрегатная функция SQL Server SUM позволяет выбрать итоговое значение для числового столбца.

• Функция SQL Server SQRT – используется для создания квадратного корня из заданного числа.

• Функция SQL Server RAND – используется для генерации случайного числа с помощью команды SQL.

• Функция CONCAT для SQL Server – используется для объединения нескольких параметров в один параметр.

• Числовые функции SQL Server – полный список функций SQL, необходимых для работы с числами в SQL.

• Строковые функции SQL Server – полный список функций SQL, необходимых для работы со строками в SQL.

Функция СЧЕТА SQL Server – агрегатная функция СЧЕТА SQL Server используется для подсчета количества строк в таблице базы данных.

Функция SQL Server MAX – Функция агрегата SQL Server MAX позволяет выбрать максимальное (максимальное) значение для определенного столбца.

Функция SQL Server MIN – агрегатная функция SQL Server MIN позволяет выбрать минимальное (минимальное) значение для определенного столбца.

Функция SQL Server AVG – Функция агрегирования SQL Server AVG выбирает среднее значение для определенного столбца таблицы.

Функция SQL Server SUM – агрегатная функция SQL Server SUM позволяет выбрать итоговое значение для числового столбца.

Функция SQL Server SQRT – используется для создания квадратного корня из заданного числа.

Функция SQL Server RAND – используется для генерации случайного числа с помощью команды SQL.

Функция CONCAT для SQL Server – используется для объединения нескольких параметров в один параметр.

Числовые функции SQL Server – полный список функций SQL, необходимых для работы с числами в SQL.

Строковые функции SQL Server – полный список функций SQL, необходимых для работы со строками в SQL.

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

Строковые функции MS SQL Server могут применяться к строковому значению или возвращать строковое значение или числовые данные.

Ниже приведен список функций String с примерами.

ASCII ()

Значение кода Ascii будет выводиться для символьного выражения.

пример

Следующий запрос даст значение кода Ascii для данного символа.

Select ASCII ('word') 

СИМВОЛ ()

Символ будет выводиться для заданного кода или целого числа Ascii.

пример

Следующий запрос даст символ для данного целого числа.

Select CHAR(97)

NCHAR ()

Символ Unicode будет выводиться для данного целого числа.

пример

Следующий запрос даст символ Unicode для данного целого числа.

Select NCHAR(300)

CHARINDEX ()

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

пример

Следующий запрос даст начальную позицию символа «G» для данного строкового выражения «KING».

Select CHARINDEX('G', 'KING')

ОСТАВИЛ()

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

пример

Следующий запрос выдаст строку «WORL», как указано 4 числа символов для данной строки «WORLD».

Select LEFT('WORLD', 4)

ПРАВО()

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

пример

Следующий запрос выдаст строку ‘DIA’ как упомянутое количество символов для данной строки ‘INDIA’.

Select RIGHT('INDIA', 3)

SUBSTRING ()

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

пример

Следующие запросы будут давать строки «WOR», «DIA», «ING», как мы упоминали (1,3), (3,3) и (2,3), в качестве значений начала и длины соответственно для данных строк «WORLD» , «Индия» и «Король».

Select SUBSTRING ('WORLD', 1,3) 
Select SUBSTRING ('INDIA', 3,3) 
Select SUBSTRING ('KING', 2,3)

LEN ()

Количество символов будет выводиться для данного строкового выражения.

пример

Следующий запрос даст 5 для строкового выражения ‘HELLO’.

Select LEN('HELLO') 

НИЖНИЙ ()

Строчная строчная строка будет выводиться для данных данной строки.

пример

Следующий запрос выдаст «sqlserver» для символьных данных «SQLServer».

Select LOWER('SQLServer') 

ВЕРХНИЙ ()

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

пример

Следующий запрос выдаст SQLSERVER для символьных данных SqlServer.

Select UPPER('SqlServer')

LTRIM ()

Строковое выражение будет выводиться для заданных строковых данных после удаления начальных пробелов.

пример

Следующий запрос даст «МИР» для символьных данных «МИР».

Select LTRIM('   WORLD')

RTRIM ()

Строковое выражение будет выводиться для заданных строковых данных после удаления конечных пробелов.

пример

Следующий запрос даст «ИНДИЯ» для символьных данных «ИНДИЯ».

Select RTRIM('INDIA   ') 

REPLACE ()

Строковое выражение придет в качестве вывода для данных строковых данных после замены всех вхождений указанного символа указанным символом.

пример

Следующий запрос выдаст строку ‘KNDKA’ для данных строки ‘INDIA’.

Select REPLACE('INDIA', 'I', 'K')

REPLICATE ()

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

пример

Следующий запрос даст строку «WORLDWORLD» для данных строки «WORLD».

Select REPLICATE('WORLD', 2)

ЗАДНИЙ ХОД()

Обратное строковое выражение придет как выход для заданных строковых данных.

пример

Следующий запрос выдаст строку ‘DLROW’ для данных строки ‘WORLD’.

Select REVERSE('WORLD')

SOUNDEX ()

Возвращает четырехсимвольный код (SOUNDEX) для оценки сходства двух заданных строк.

пример

Следующий запрос даст ‘S530’ для строк ‘Smith’, ‘Smyth’.

Select SOUNDEX('Smith'), SOUNDEX('Smyth')

РАЗНИЦА ()

Целочисленное значение будет получено как результат заданных двух выражений.

пример

Следующий запрос даст 4 для выражений «Смит», «Смит».

Select Difference('Smith','Smyth') 

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

ПРОСТРАНСТВО()

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

пример

Следующий запрос даст «Я люблю Индию».

Select 'I'+space(1)+'LOVE'+space(1)+'INDIA'

ВЕЩЕСТВО ()

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

пример

Следующий запрос выдаст строку ‘AIJKFGH’ для данных строки ‘ABCDEFGH’ в соответствии с заданным начальным символом и длиной как 2 и 4 соответственно и ‘IJK’ в качестве указанной целевой строки.

Select STUFF('ABCDEFGH', 2,4,'IJK') 

STR ()

Символьные данные поступят как выходные данные для данных числовых данных.

пример

Следующий запрос даст 187,37 для данного 187,369 на основе заданной длины как 6 и десятичной как 2.

Select STR(187.369,6,2) 

UNICODE ()

Целочисленное значение придет как выход для первого символа данного выражения.

пример

Следующий запрос даст 82 для выражения ‘RAMA’.

Select UNICODE('RAMA') 

QUOTENAME ()

Данная строка будет выводиться с указанным разделителем.

пример

Следующий запрос выдаст «RAMA» для данной строки «RAMA», так как мы указали двойную кавычку в качестве разделителя.

Select QUOTENAME('RAMA','"') 

PATINDEX ()

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

пример

Следующий запрос даст 1 для «ИНДИИ».

Select PATINDEX('I%','INDIA') 

ФОРМАТ()

Данное выражение будет выводиться в указанном формате.

пример

Следующий запрос даст «понедельник, 16 ноября 2015 г.» для функции getdate в соответствии с указанным форматом, где «D» обозначает название дня недели.

SELECT FORMAT ( getdate(), 'D') 

CONCAT ()

Одна строка будет выводиться после объединения указанных значений параметров.

пример

Следующий запрос даст «A, B, C» для заданных параметров.

Select CONCAT('A',',','B',',','C') 

T-SQL – функции даты

Ниже приведен список функций даты в MS SQL Server.

GETDATE ()

Он вернет текущую дату вместе со временем.

Синтаксис

Синтаксис для вышеуказанной функции –

GETDATE()

пример

Следующий запрос вернет текущую дату и время в MS SQL Server.

Select getdate() as currentdatetime

DATEPART ()

Он вернет часть даты или времени.

Синтаксис

Синтаксис для вышеуказанной функции –

DATEPART(datepart, datecolumnname)

пример

Пример 1. Следующий запрос вернет часть текущей даты в MS SQL Server.

Select datepart(day, getdate()) as currentdate

Пример 2. Следующий запрос вернет часть текущего месяца в MS SQL Server.

Select datepart(month, getdate()) as currentmonth

DATEADD ()

Он будет отображать дату и время путем сложения или вычитания даты и времени.

Синтаксис

Синтаксис для вышеуказанной функции –

DATEADD(datepart, number, datecolumnname)

пример

Следующий запрос вернет дату и время после 10 дней с текущей даты и времени в MS SQL Server.

Select dateadd(day, 10, getdate()) as after10daysdatetimefromcurrentdatetime 

DATEDIFF ()

Он будет отображать дату и время между двумя датами.

Синтаксис

Синтаксис для вышеуказанной функции –

DATEDIFF(datepart, startdate, enddate)

пример

Следующий запрос вернет разницу часов между 2015-11-16 и 2015-11-11 датами в MS SQL Server.

Select datediff(hour, 2015-11-16, 2015-11-11) as 
differencehoursbetween20151116and20151111 

ПЕРЕРАБАТЫВАТЬ()

Он будет отображать дату и время в разных форматах.

Синтаксис

Синтаксис для вышеуказанной функции –

CONVERT(datatype, expression, style)

пример

Следующие запросы вернут дату и время в другом формате в MS SQL Server.

SELECT CONVERT(VARCHAR(19),GETDATE()) 
SELECT CONVERT(VARCHAR(10),GETDATE(),10) 
SELECT CONVERT(VARCHAR(10),GETDATE(),110)

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

Числовые функции MS SQL Server могут быть применены к числовым данным и будут возвращать числовые данные.

Ниже приведен список числовых функций с примерами.

АБС ()

Абсолютное значение будет выходным для числового выражения.

пример

Следующий запрос даст абсолютное значение.

Select ABS(-22) 

ACOS ()

Значение арккосинуса придет как выход для указанного числового выражения.

пример

Следующий запрос даст значение арккосинуса 0.

Select ACOS(0) 

КАК В()

Значение синусоидальной дуги будет выводиться для указанного числового выражения.

пример

Следующий запрос даст значение арксинуса 0.

Select ASIN(0)

ЗАГАР()

Значение арктангенса будет выводиться для указанного числового выражения.

пример

Следующий запрос даст значение арктангенса 0.

Select ATAN(0) 

ATN2 ()

Значение арктангенса во всех четырех квадрантах будет выводиться для указанного числового выражения.

пример

Следующий запрос даст значение арктангенса во всех четырех квадрантах 0.

Select ATN2(0, -1) 

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           2000.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000.00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              8500.00 
6   Komal      22        MP                  4500.00 
7   Muffy      24        Indore              10000.00 

МЕЖДУ()

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

пример

Следующий запрос даст следующий вывод.

SELECT salary from customers where salary between 2000 and 8500

Выход

salary 
2000.00 
2000.00 
6500.00 
8500.00 
4500.00

MIN ()

Минимальное значение придет как выход из данного выражения.

пример

Следующий запрос даст «1500,00» для данного выражения «зарплата» из таблицы клиентов.

Select MIN(salary)from CUSTOMERS

МАКСИМУМ()

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

пример

Следующий запрос выдаст «10000,00» для данного выражения «зарплата» из таблицы клиентов.

Select MAX(salary)from CUSTOMERS

SQRT ()

Квадратный корень данного числового выражения придет как выходной.

пример

Следующий запрос даст 2 для данного 4 числового выражения.

Select SQRT(4) 

ЧИСЛО ПИ()

Значение PI придет как выходной.

пример

Следующий запрос даст 3.14159265358979 для значения PI.

Select PI() 

ПОТОЛОК ()

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

пример

Следующий запрос даст 124 для данного значения 123.25.

Select CEILING(123.25) 

ЭТАЖ()

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

пример

Следующий запрос даст 123 для данного значения 123.25.

Select FLOOR(123.25) 

ЖУРНАЛ()

Натуральный логарифм данного выражения придет в качестве вывода.

пример

Следующий запрос даст 0 для данного 1 значения.