Transact-SQL (T-SQL) – процедурне розширення мови SQL, створене компанією Microsoft (для Microsoft
SQL Server) та Sybase (для Sybase ASE).
Типи даних (Transact-SQL)
інформація на сайті Microsoft1. Точні числа (Exact Numeric Types)
2. Приблизні числові типи (Approximate Numeric Types)
3. Дата та час (Date and Time Types)
4. Символьні рядки (Character Strings)
5. Символьні рядки в Юнікоді (Unicode Character Strings)
6. Двійкові дані (Binary Strings)
7. Інші типи даних (Other Data Types)
--Видео курс Transact SQL. Урок 1. Знакомство с SQL. Типы данных.
--https://youtu.be/GOvQUd6-ttE?t=1660
-- Однострочный комментарий.
/*
... Многострочный комментарий.
*/
--Data Types
----BIT инициализац=ия переменной F5
DECLARE @someBit bit = 0
PRINT @someBit
SET @someBit = 123
PRINT @someBit
--INTEGER
DECLARE @someInt int = 123
PRINT @someInt
--Ошибка арифметического переполнения при преобразовании expression к типу данных int.
SET @someInt = 2147483647 +1
PRINT @someInt
--DECIMAL (NUMERIC)
--124
DECLARE @someDec decimal = 123.56
PRINT @someDec
--123.56000
DECLARE @someDec decimal(8,5) = 123.56
PRINT @someDec
--123.56000
DECLARE @someDec decimal(16,2) = 1000000000.56
PRINT @someDec
PRINT LEN(@someDec)
--Ошибка арифметического переполнения при преобразовании numeric к типу данных numeric.
DECLARE @someDec decimal(8,5) = 5123.56
PRINT @someDec
--DATE
--2023-03-30 преобразование строки в дату, для английской локализации указать '03-30-2023'
DECLARE @someDate date = '30-03-2023'
PRINT @someDate
-- 'YYYYMMDD' универсальная запись которая не зависит от локализации
SET @someDate = '20230330'
PRINT @someDate
--DATETIME
--мар 30 2023 6:18PM
DECLARE @someDateTime datetime = '30-03-2023 18:18:56'
PRINT @someDateTime
-- 'YYYYMMDD' универсальная запись которая не зависит от локализации
SET @someDateTime = '20230330 18:18:56:123'
PRINT @someDateTime
--TIME
--2023-03-30 преобразование строки в дату, для английской локализации указать '03-30-2023'
DECLARE @someTime time = '18:18:56:123'
PRINT @someTime
-- 'YYYYMMDD' универсальная запись которая не зависит от локализации
SET @someTime = '18:18:56.1'
PRINT @someTime
--NCHAR
--'текст '
-- для юникода добавить перед N кавычками N'???????????'
DECLARE @someNChar nchar(15) = N'текст'
PRINT(@someNChar)
--NVARCHAR
--'текст'
DECLARE @someNVarChar nvarchar(15) = N'текст'
PRINT(@someNVarChar)
--Arithmetic operators
--Приоритет типов данных https://msdn.microsoft.com/library/ms190309(SQL.130).aspx
--+ сложение
DECLARE @x int = 123, @y int = 456
SELECT @x + @y
DECLARE @date datetime = '20230330 18:18:56:123'
SELECT @date d, @date + 1.25 d1_25
SELECT 'Hello, ' + 'world!'
--- вычитание
--* умножение
--/ деление
--смотреть в приоритет типов данных на сайте MS
--3 3.333333 3.333333
SELECT 10/3 "int",
10./3 "decimal",
10/3. "decimal"
--Обнаружена ошибка: деление на ноль.
SELECT 1/0
--% остаток от деления
--2 1.400000
SELECT 7%5 "остаток от деления",
7/5.
T-SQL SELECT
ALL, DISTINCT, TOP
SELECT SELECT [ ALL | DISTINCT ] [TOP (expression) [PERCENT] [ WITH TIES ] ]
select_list
FROM table
ALL – все строки (значение по умолчанию).
DISTINCT – только уникальные строки (без дубликатов).
TOP – первые строки. Количество задается числом или в процентах (от общего числа выводимых строк).
WITH TIES – указывает на то, что будут возвращены дополнительные строки из основного результирующего набора с тем же значением в столбцах ORDER BY, которые появляются как последние из TOP n (PERCENT) строк. Предложение TOP...WITH TIES может быть задано только в инструкциях SELECT, и только если указано предложение ORDER BY.
Логічний порядок обробки інструкції SELECT
1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE или WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP
Оператори порівняння
Результат виконання оператора порівняння має тип даних Boolean. Він може мати одне із трьох значень: TRUE, FALSE та UNKNOWN.
= дорівнює
<> або
!= Не дорівнює
> більше
< менше
>= більше чи дорівнює
<= менше чи дорівнює
!> не більше
!< не менше
Логічні оператори
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE или WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP
-- SELECT [ALL | DISTINCT] [TOP (expression) [PERCENT] [WHITH TIES]]
/****************************************************************************
************************ T R A N S A C T - S Q L ************************
*****************************************************************************
***** Lesson III ***** SELECT ************************
****************************************************************************/
-- DISTINCT - выборка уникальных строк (без дубликатов)
SELECT DISTINCT Department FROM Employees
-- TOP - выборка заданного числа строк
SELECT TOP 10 * FROM Employees
SELECT TOP 25 PERCENT * FROM Employees
-- ORDER BY - сортировка
SELECT * FROM Employees
ORDER BY LName
SELECT * FROM Employees
ORDER BY LName, FName
SELECT * FROM Employees
ORDER BY BirthDate
SELECT * FROM Employees
ORDER BY BirthDate DESC
SELECT * FROM Employees
ORDER BY 7, 3 -- указание номеров столбцов (а не названий)
-- WITH TIES - для включения строк, соответствующих значениям в последней строке
SELECT TOP 10 FName, LName, Salary FROM Employees
ORDER BY Salary DESC
SELECT TOP 10 WITH TIES FName, LName, Salary FROM Employees
ORDER BY Salary DESC
SELECT TOP 30 FName, LName, Salary FROM Employees
ORDER BY Salary DESC
-- SELECT ... INTO ... - сохранить результаты выборки в новой таблице
--SELECT EmployeesID, LName, Salary
SELECT ID, LName, Salary
INTO EmpSalaries -- или #EmpSalaries (во временную таблицу)
FROM Employees
SELECT * FROM EmpSalaries
ORDER BY Id
T-SQL CASE
/****************************************************************************
************************ T R A N S A C T - S Q L ************************
*****************************************************************************
***** Lesson III ***** SELECT ************************
****************************************************************************/
-- Выражение CASE
-- 1) простое выражение CASE
SELECT Id, LName, Salary,
CASE
WHEN Salary >= 8000 THEN 'chief'
WHEN Salary >= 5000 THEN 'manager'
WHEN Salary IS NULL THEN 'unknown'
ELSE 'worker'
END AS Position,
CASE
WHEN Salary >= 8000 THEN 'chief'
WHEN Salary >= 5000 THEN 'manager'
WHEN Salary IS NULL THEN 'unknown'
END AS Position2
FROM Employees
-- 2) поисковое выражение CASE
SELECT Id, LName, Department, Salary,
CASE Department --CASE
WHEN 'ADMINISTRATION & SUPPORT' THEN '100%' -- WHEN Department = 'ADMINISTRATION & SUPPORT' THEN '100%'
WHEN 'LAW' THEN '80%' -- ...
WHEN 'FINANCE & ACCOUNTING' THEN '70%'
ELSE '10%'
END AS [Bonus%],
Salary/100 *
CASE Department
WHEN 'ADMINISTRATION & SUPPORT' THEN 100
WHEN 'LAW' THEN 80
WHEN 'FINANCE & ACCOUNTING' THEN 70
ELSE 10
END AS Bonus,
(Salary/100 *
CASE Department
WHEN 'ADMINISTRATION & SUPPORT' THEN 100
WHEN 'LAW' THEN 80
WHEN 'FINANCE & ACCOUNTING' THEN 70
ELSE 10
END) + Salary AS [Salary & Bonus]
FROM Employees
-- IIF (начиная с SQL Server 2012)
SELECT Id, LName, Department, Salary,
IIF(Salary >= 6000, 'manager', 'woker') AS Position
,CASE WHEN Salary >= 6000 THEN 'manager' ELSE 'woker' END AS Position2
FROM Employees
ALTER TABLE Employees
ADD Gender bit
UPDATE Employees
SET Gender = IIF(Id > 50, 1, 0)
SELECT Id, LName,
IIF (Gender = 0, 'woman', 'man') AS Gender
FROM Employees
-- GROUP BY
SELECT Department, Gender FROM Employees
GROUP BY Department, Gender
SELECT DISTINCT Department, Gender FROM Employees
-- HAVING
SELECT Department FROM Employees
GROUP BY Department
HAVING Department LIKE 'L%'
T-SQL WHERE
/****************************************************************************
************************ T R A N S A C T - S Q L ************************
*****************************************************************************
***** Lesson III ***** SELECT ************************
****************************************************************************/
-- WHERE - условие выборки строк
--1. Ќахождение строки с помощью простого равенства
SELECT * FROM Employees
WHERE Salary = 10000
SELECT * FROM Employees
WHERE Department = 'sales'
-- Ћператоры сравнениЯ (=, <> или !=, >, <, >=, <=, !<, !>)
--2. Ќахождение строк с использованием оператора сравнениЯ
SELECT * FROM Employees
WHERE BirthDate > '19900101'
SELECT * FROM Employees
WHERE BirthDate !> '19900101'
-- ‹огические операторы (ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, SOME)
--3. Ќахождение строк, которые должны удовлетворять нескольким условиЯм
SELECT * FROM Employees
WHERE Department = 'sales' AND Salary >= 6000
--4. Ќахождение строк, удовлетворЯющих любому из нескольких условий
SELECT * FROM Employees
WHERE Department = 'sales' OR Department = 'supply'
SELECT * FROM Employees
--WHERE Department = 'sales' OR Department = 'supply' AND Salary >= 6000 -- not correct
WHERE Salary >= 6000 AND (Department = 'sales' OR Department = 'supply')
--5. IN - нахождение строк, находЯщихсЯ в списке значений
SELECT * FROM Employees
WHERE Department IN ('sales', 'supply', 'law', 'logistics')
SELECT * FROM Employees
WHERE Department NOT IN ('sales', 'supply', 'law', 'logistics') -- см. подзапросы
-- ALL, ANY | SOME, EXISTS - см. подзапросы
--6. BETWEEN - нахождение строк, содержащих значение, расположенное между двумЯ значениЯми
SELECT * FROM Employees
WHERE BirthDate >= '19900103' AND BirthDate <= '19920824'
ORDER BY BirthDate
SELECT * FROM Employees
WHERE BirthDate BETWEEN '19900103' AND '19920824'
ORDER BY BirthDate
--7. -- LIKE - нахождение строк, содержащих значение как часть строки
SELECT * FROM Employees
WHERE Department LIKE 'sales'
-- Wildcard Characters - подстановочные символы (%, _, [], [^])
--% найти все совпадения
SELECT * FROM Employees
WHERE Phone LIKE '063%'
--_ найдёт по одному символу "_" совпадением 2
SELECT * FROM Employees
WHERE Id LIKE '_2'
--[2,4]2 - выведет где первая или 2 или 4
SELECT * FROM Employees
WHERE Id LIKE '[2,4]2'
--[2-5]2 - выведет где первая между 2 и 5
SELECT * FROM Employees
WHERE Id LIKE '[2-5]2'
--^ все кроме указанных симыолов
SELECT * FROM Employees
WHERE Id LIKE '[^2-5]2'
--ESCAPE - самостоЯтельно
--SELECT * FROM Employees
--WHERE Address LIKE '%|%|%' {escape '|'}
--LIKE '\%AAA%' {escape '\'}
-- [%] рабочий вариант нахождения % в ячейке
SELECT * FROM Employees
WHERE Address LIKE '%[%]%'
--8. ‘равнение с NULL
SELECT * FROM Employees
WHERE Salary IS NULL
SELECT * FROM Employees
WHERE Salary IS NOT NULL
--неверно
SELECT * FROM Employees
WHERE Salary IN (4000, 7000, NULL) -- NULL не войдет
--верно для выборки с NULL
SELECT * FROM Employees
WHERE Salary IN (4000, 7000)
OR Salary IS NULL
T-SQL Built-in Functions. Scalar Functions
Імена деяких системних функцій Transact-SQL починаються з двох символів @ (наприклад, @@IDENTITY). Хоча в попередніх версіях сервера Microsoft SQL Server @@-функції називаються глобальними змінними, вони не є змінними і поводяться інакше.
/****************************************************************************
************************ T R A N S A C T - S Q L ************************
*****************************************************************************
***** Lesson IV ****** Scalar Functions ************************
****************************************************************************/
--GO - информирует об окончании пакета инструкций Transact-SQL.
--GO - это не инструкция Transact-SQL; эта команда распознается редактором SQL Server Management Studio.
--Пакет (batch) - это последовательность инструкций Transact-SQL и процедурных расширений,
--которые направляются системе базы данных для совместного их выполнения. Преимущество пакета
--над группой отдельных инструкций состоит в том, что одновременное исполнение всех инструкций
--позволяет получить значительное улучшение производительности.
--1. System Functions
--@@ERROR
DECLARE @myint int;
SET @myint = 'ABC';
GO
SELECT @@ERROR;
GO
BEGIN TRY
DECLARE @myint int;
SET @myint = 1/0
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
--@@IDENTITY - возвращает значение последнего вставленного идентификатора
--SCOPE_IDENTITY, IDENT_CURRENT - похожие функции
INSERT Students
VALUES
('Alex', 'Li', NULL, NULL);
GO
SELECT @@IDENTITY;
GO
--@@ROWCOUNT - возвращает число строк, затронутых при выполнении последней инструкции.
UPDATE Students
SET LName = 'Po'
WHERE Id = 6
GO
SELECT @@ROWCOUNT;
GO
DELETE Students
GO
SELECT @@ROWCOUNT;
GO
-- NEWID - cоздает уникальное значение типа uniqueidentifier.
DECLARE @myId uniqueidentifier
SET @myId = NEWID()
PRINT @myId
-- ISNUMERIC - определяет, имеет ли переданное выражение допустимый числовой тип (int, decimal и др.),
-- возвращает 1, если при оценке входного выражения получается допустимый числовой тип данных, иначе - 0.
SELECT
ISNUMERIC('33')
,ISNUMERIC(17)
,ISNUMERIC('3abc')
,ISNUMERIC(@myId)
,ISNUMERIC('+')
,ISNUMERIC('$')
-- ISNULL - заменяет значение NULL указанным замещающим значением.
SELECT Id, LName, ISNULL(Salary, 0.0) AS Salary
FROM Employees
DECLARE @myInt int;
SELECT 3 + @myInt, 3 + ISNULL(@myInt, 0)
-- COALESCE - возвращает первое не NULL значение из списка значений.
-- (переписывается оптимизатором запросов в выражение case)
Функції конфігурації (Transact-SQL) https://msdn.microsoft.com/ru-ru/library/ms173823.aspx
Conversion Functions (Transact-SQL) https://msdn.microsoft.com/en-us/library/hh231076.aspx
• CAST и CONVERT
• PARSE
• TRY_CAST и TRY_CONVERT
• TRY_PARSE
Функції роботи з курсорами (Transact-SQL) https://msdn.microsoft.com/ru-ru/library/ms186285.aspx
Функції дати та часу (Transact-SQL) https://msdn.microsoft.com/ru-ru/library/ms186724.aspx
• CURRENT_TIMESTAMP и GETDATE
•DATENAME, DATEPART, DAY, MONTH, YEAR
•DATEFROMPARTS, DATETIMEFROMPARTS, TIMEFROMPARTS
•DATEDIFF
•DATEADD
Функції JSON (Transact-SQL) https://msdn.microsoft.com/ru-ru/library/dn921900.aspx
--2. Conversion Functions
----CAST and CONVERT - преобразуют выражение одного типа данных в другой
SELECT 10/3,
10./3,
CAST(10 as decimal)/3,
CONVERT(decimal, 10)/3
SELECT CONVERT(datetime, '20170101 13:45')
SELECT
GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;
GO
--PARSE
--TRY_CAST, TRY_CONVERT, TRY_PARSE - работают как предыдущие, но в случае
--невозможности приведения возвращают NULL
SELECT TRY_CAST('12/31/2016' as date),
TRY_CAST('31/12/2016' as date),
TRY_CONVERT(date, '12/31/2016'),
TRY_CONVERT(date, '31/12/2016')
--5. Date and Time Functions
----CURRENT_TIMESTAMP и GETDATE
SELECT CURRENT_TIMESTAMP,
GETDATE()
USE ITVDNdb
GO
--DATENAME, DATEPART, DAY, MONTH, YEAR
--Найти сотрудников, которые радились в декабре
SELECT Id, LName, BirthDate
FROM Employees
WHERE DATEPART(MONTH, BirthDate) = 12
DECLARE @today date = GETDATE()
SELECT DATENAME(yy, @today),
DATENAME(MONTH, @today),
DATEPART(MONTH, @today),
DATEPART(QUARTER, @today),
DAY(@today) [DAY],
MONTH(@today) [MONTH],
YEAR(@today) [YEAR]
--DATEFROMPARTS, DATETIMEFROMPARTS, TIMEFROMPARTS
SELECT DATEFROMPARTS(2017, 05, 17),
DATETIMEFROMPARTS(2017, 05, 17, 04, 30, 12, 123),
TIMEFROMPARTS(04, 30, 12, 1234567, 7)
SELECT DATEDIFF(MONTH, '20160901', '20161201'),
DATEDIFF(MONTH, '20160831', '20161201'),
DATEADD(MONTH, -3, GETDATE())
SELECT Id, LName, BirthDate
FROM Employees
WHERE DATEDIFF(YEAR, BirthDate, GETDATE()) < 30
SELECT Id, LName, BirthDate
FROM Employees
WHERE BirthDate > DATEADD(YEAR,-30, GETDATE())
-- https://msdn.microsoft.com/en-us/library/hh213226.aspx
-- Mathematical Functions (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms177516.aspx
-- Metadata Functions (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187812.aspx
-- Security Functions (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms186236.aspx
-- 4. Mathematical Functions
SELECT SQRT(144), SQUARE(12), POWER(2, 8),
FLOOR (123.4),
FLOOR (123.6),
ROUND (123.4, 0),
ROUND (123.6, 0),
ROUND (123.4567, 3),
ROUND (123.4567, 3, 1),
PI()
--6. Logical Functions
----CHOOSE - возвращает элемент по указанному индексу из списка значений
SELECT Id, LName, BirthDate,
CHOOSE(MONTH(BirthDate), 'Winter','Winter', 'Spring','Spring','Spring','Summer','Summer',
'Summer','Autumn','Autumn','Autumn','Winter')
FROM Employees
--7. Metadata Functions
----OBJECT_ID и OBJECT_NAME
IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('Cars'))
BEGIN
CREATE TABLE Cars
(
Id int IDENTITY,
Name varchar(20)
)
END
GO
SELECT OBJECT_ID('Cars'),
OBJECT_NAME(OBJECT_ID('Cars'))
IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('Cars'))
BEGIN
DROP TABLE Cars
END
GO
-- Строковые функции (Transact-SQL) https://msdn.microsoft.com/ru-ru/library/ms181984.aspx
-- System Functions (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187786.aspx
-- System Statistical Functions (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms177520.aspx
-- Функции для работы с изображениями и текстом — TEXTPTR (Transact-SQL) https://msdn.microsoft.com/ru-ru/library/ms188353.aspx
--3. String Functions
----ASCII / UNICODE - возвращает код ASCII / UNICODE первого символа указанного
-- символьного выражения.
SELECT ASCII('hello') [ASCII],
UNICODE(N'быть') [UNICODE]
----CHAR / NCHAR - преобразует int код ASCII / UNICODE в символ.
SELECT CHAR(104) [CHAR],
NCHAR(1073) [NCHAR]
SELECT LEFT('abcdefg',2) [LEFT],
RIGHT('abcdefg',2) [RIGHT],
LOWER('ABCDEFG') [LOWER],
UPPER('abcdefg') [UPPER],
LEN('12345 ') [LEN],
REVERSE('12345') [REVERSE]
--STUFF, SUBSTRING
SELECT STUFF('abcdefg', 3, 2, '!!!') [STUFF],
SUBSTRING('abcdefg', 3, 2) [SUBSTRING]
PRINT LTRIM(' hello')
PRINT RTRIM('world ')
PRINT 'Hello,' + SPACE(5) + 'world'
PRINT REPLICATE('&', 7)
SELECT CHARINDEX('Two', 'One Two Three Two Four'),
CHARINDEX('Two', 'One Two Three Two Four', 6),
PATINDEX('%Th_ee%', 'One Two Three Two Four')
SELECT REPLACE('One Two Three Two Four', 'Two', '2')
SELECT N'Айседора' + ' ' + N'Дункан',
N'Айседора' + ' ' + NULL,
N'Айседора' + ' ' + ISNULL(NULL, ''),
CONCAT(N'Айседора', ' ', NULL)
--STRING_AGG, STRING_SPLIT - SQL Server 2016
SELECT value
FROM STRING_SPLIT('PRODUCTION & ENGINEERING|SUPPLY|LOGISTICS|
PLANNED-ECONOMIC|QUALITY ASSURANCE & CONTROL|ADMINISTRATION & SUPPORT|
MARKETING|HR MANAGEMENT|SALES|FINANCE & ACCOUNTING|LAW', '|')
T-SQL CASE
T-SQL CASE
© 2023 Калюта Віктор