INSERT #Trash(ID,Col1,Col2,Col3)VALUES

(1,'A','A','A'), (2,'A','B','C'), (3,'C','A','B'), (4,'A','A','B'),

(5,'B','B','B'), (6,'A','A','B'), (7,'A','A','A'), (8,'C','A','B'),

(9,'C','A','B'), (10,'A','A','B'), (11,'A',NULL,'B'), (12,'A',NULL,'B')

 

-- посмотрим что возвращает запрос без опции DISTINCT

SELECT Col1,Col2,Col3

FROM #Trash

 

-- посмотрим что возвращает запрос с опцией DISTINCT

SELECT DISTINCT Col1,Col2,Col3

FROM #Trash

 

-- удалим временную таблицу

DROP TABLE #Trash

 

Наглядно это будет выглядеть следующим образом (все дубликаты помечены одним цветом):

Теперь давайте рассмотрим где это можно применить, на более практичном примере – вернем из таблицы Employees только уникальные идентификаторы отделов (т.е. узнаем ID отделов в которых числятся сотрудники):

SELECT DISTINCT DepartmentID

FROM Employees

 

DepartmentID

Здесь мы получили три строки, т.к. 2 сотрудника у нас числятся в одном отделе (ИТ).

Теперь узнаем в каких отделах, какие должности фигурируют:

SELECT DISTINCT DepartmentID,PositionID

FROM Employees

 

DepartmentID	PositionID

Здесь мы получили 4 строчки, т.к. повторяющихся комбинаций (DepartmentID, PositionID) в нашей таблице нет.

Ненадолго вернемся к DDL

Так как данных для демонстрационных примеров начинает не хватать, а рассказать хочется более обширно и понятно, то давайте чуть расширим нашу таблицу Employess. К тому же немного вспомним DDL, как говорится «повторение – мать учения», и плюс снова немного забежим вперед и применим оператор UPDATE:

-- создаем новые колонки

ALTER TABLE Employees ADD

LastName nvarchar (30), -- фамилия

FirstName nvarchar (30), -- имя

MiddleName nvarchar (30), -- отчество

Salary float, -- и конечно же ЗП в каких-то УЕ

BonusPercent float -- процент для вычисления бонуса от оклада

GO

 

-- наполняем их данными (некоторые данные намерено пропущены)

UPDATE Employees

SET

LastName= N 'Иванов',FirstName= N 'Иван',MiddleName= N 'Иванович',

Salary=5000,BonusPercent= 50

WHERE ID =1000 -- Иванов И.И.

 

UPDATE Employees

SET

LastName= N 'Петров',FirstName= N 'Петр',MiddleName= N 'Петрович',

Salary=1500,BonusPercent= 15

WHERE ID =1001 -- Петров П.П.

 

UPDATE Employees

SET

LastName= N 'Сидоров',FirstName= N 'Сидор',MiddleName=NULL,

Salary=2500,BonusPercent=NULL

WHERE ID =1002 -- Сидоров С.С.

 

UPDATE Employees

SET

LastName= N 'Андреев',FirstName= N 'Андрей',MiddleName=NULL,

Salary=2000,BonusPercent= 30

WHERE ID =1003 -- Андреев А.А.

 

Убедимся, что данные обновились успешно:

SELECT *

FROM Employees

 

ID	Name	…	LastName	FirstName	MiddleName	Salary	BonusPercent
	Иванов И.И.		Иванов	Иван	Иванович
	Петров П.П.		Петров	Петр	Петрович
	Сидоров С.С.		Сидоров	Сидор	NULL		NULL
	Андреев А.А.		Андреев	Андрей	NULL

 

Задание псевдонимов для столбцов запроса

Думаю, здесь будет проще показать, чем написать:

SELECT

-- даем имя вычисляемому столбцу

LastName+' '+FirstName+' '+MiddleName AS ФИО,

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

HireDate AS "Дата приема",

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

Birthday AS [Дата рождения],

-- слово AS не обязательно

Salary ZP

FROM Employees

 

ФИО	Дата приема	Дата рождения	ZP
Иванов Иван Иванович	2015-04-08	1955-02-19
Петров Петр Петрович	2015-04-08	1983-12-03
NULL	2015-04-08	1976-06-07
NULL	2015-04-08	1982-04-17

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

Обратите внимание, т.к. у последних 2-х сотрудников не указано отчество (NULL значение), то результат выражения «LastName+' '+FirstName+' '+MiddleName» так же вернул нам NULL.

Для соединения (сложения, конкатенации) строк в MS SQL используется символ «+».

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

На заметку.
В случае ORACLE для объединения строк используется оператор «||» и конкатенация будет выглядеть как «LastName||' '||FirstName||' '||MiddleName». Для ORACLE стоит отметить, что у него для строковых типов есть исключение, для них NULL и пустая строка '' это одно и тоже, поэтому в ORACLE такое выражение вернет для последних 2-х сотрудников «Сидоров Сидор» и «Андреев Андрей». На момент версии ORACLE 12c, насколько я знаю, опции которая изменяет такое поведение нет (если не прав, прошу поправить меня). Здесь мне сложно судить хорошо это или плохо, т.к. в одних случаях удобнее поведение NULL-строки как в MS SQL, а в других как в ORACLE.

В ORACLE тоже допустимы все перечисленные выше псевдонимы столбцов, кроме […].

Для того чтобы не городить конструкцию с использованием функции ISNULL, в MS SQL мы можем применить функцию CONCAT. Рассмотрим и сравним 3 варианта:

SELECT

LastName+' '+FirstName+' '+MiddleName FullName1,

-- 2 варианта для замены NULL пустыми строками '' (получаем поведение как и в ORACLE)

ISNULL (LastName,'')+' '+ ISNULL (FirstName,'')+' '+ ISNULL (MiddleName,'') FullName2,

CONCAT (LastName,' ',FirstName,' ',MiddleName) FullName3

FROM Employees

 

FullName1	FullName2	FullName3
Иванов Иван Иванович	Иванов Иван Иванович	Иванов Иван Иванович
Петров Петр Петрович	Петров Петр Петрович	Петров Петр Петрович
NULL	Сидоров Сидор	Сидоров Сидор
NULL	Андреев Андрей	Андреев Андрей

В MS SQL псевдонимы еще можно задавать при помощи знака равенства:

SELECT

'Дата приема'=HireDate, -- помимо "…" и […] можно использовать '…'

[Дата рождения]=Birthday,

ZP=Salary

FROM Employees

 

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

Напоследок скажу, что для псевдонимов имена лучше задавать, используя только символы латиницы и цифры, избегая применения '…', "…" и […], то есть использовать те же правила, что мы использовали при наименовании таблиц. Дальше, в примерах я буду использовать только такие наименования и никаких '…', "…" и […].

Основные арифметические операторы SQL

 

Оператор	Действие
+	Сложение (x+y) или унарный плюс (+x)
-	Вычитание (x-y) или унарный минус (-x)
*	Умножение (x*y)
/	Деление (x/y)
%	Остаток от деления (x%y). Для примера 15%10 даст 5

Приоритет выполнения арифметических операторов такой же, как и в математике. Если необходимо, то порядок применения операторов можно изменить используя круглые скобки — (a+b)*(x/(y-z)).

И еще раз повторюсь, что любая операция с NULL дает NULL, например: 10+NULL, NULL*15/3, 100/NULL – все это даст в результате NULL. Т.е. говоря просто неопределенное значение не может дать определенный результат. Учитывайте это при составлении запроса и при необходимости делайте обработку NULL значений функциями ISNULL, COALESCE:

SELECT

ID, Name,

Salary/100*BonusPercent AS Result1, -- без обработки NULL значений

Salary/100* ISNULL (BonusPercent,0) AS Result2, -- используем функцию ISNULL

Salary/100* COALESCE (BonusPercent,0) AS Result3 -- используем функцию COALESCE

FROM Employees

 

ID	Name	Result1	Result2	Result3
	Иванов И.И.
	Петров П.П.
	Сидоров С.С.	NULL
	Андреев А.А.
	Николаев Н.Н.	NULL
	Александров А.А.	NULL

 

Немного расскажу о функции COALESCE:

COALESCE (expr1, expr2,..., exprn) - Возвращает первое не NULL значение из списка значений.

 

Пример:

SELECT COALESCE (f1, f1*f2, f2*f3) val -- в данном случае вернется третье значение

FROM (SELECT null f1, 2 f2, 3 f3) q

 

В основном, я сосредоточусь на рассказе конструкций языка DML и по большей части не буду рассказывать о функциях, которые будут встречаться в примерах. Если вам непонятно, что делает та или иная функция поищите ее описание в интернет, можете даже поискать информацию сразу по группе функций, например, задав в поиске Google «MS SQL строковые функции», «MS SQL математические функции» или же «MS SQL функции обработки NULL». Информации по функциям очень много, и вы ее сможете без труда найти. Для примера, в библиотеке MSDN, можно узнать больше о функции COALESCE:

Вырезка из MSDN Сравнение COALESCE и CASE

Выражение COALESCE — синтаксический ярлык для выражения CASE. Это означает, что код COALESCE(expression1,...n) переписывается оптимизатором запросов как следующее выражение CASE:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

 

Для примера рассмотрим, как можно воспользоваться остатком от деления (%). Данный оператор очень полезен, когда требуется разбить записи на группы. Например, вытащим всех сотрудников, у которых четные табельные номера (ID), т.е. те ID, которые делятся на 2:

SELECT ID,Name

FROM Employees

WHERE ID %2=0 -- остаток от деления на 2 равен 0

 

ID	Name
	Иванов И.И.
	Николаев Н.Н.
	Сидоров С.С.

 

ORDER BY – сортировка результата запроса

Предложение ORDER BY используется для сортировки результата запроса.

SELECT

LastName,

FirstName,

Salary

FROM Employees

ORDER BY LastName,FirstName -- упорядочить результат по 2-м столбцам – по Фамилии, и после по Имени

 

LastName	FirstName	Salary
Андреев	Андрей
Иванов	Иван
Петров	Петр
Сидоров	Сидор

После имя поля в предложении ORDER BY можно задать опцию DESC, которая служит для сортировки этого поля в порядке убывания:

SELECT LastName,FirstName,Salary

FROM Employees

ORDER BY -- упорядочить в порядке

Salary DESC, -- 1. убывания Заработной Платы

LastName, -- 2. по Фамилии

FirstName -- 3. по Имени

 

LastName	FirstName	Salary
Иванов	Иван
Сидоров	Сидор
Андреев	Андрей
Петров	Петр

 

Для заметки. Для сортировки по возрастанию есть ключевое слово ASC, но так как сортировка по возрастанию применяется по умолчанию, то про эту опцию можно забыть (я не помню случая, чтобы я когда-то использовал эту опцию).

 

Стоит отметить, что в предложении ORDER BY можно использовать и поля, которые не перечислены в предложении SELECT (кроме случая, когда используется DISTINCT, об этом случае я расскажу ниже). Для примера забегу немного вперед используя опцию TOP и покажу, как например, можно отобрать 3-х сотрудников у которых самая высокая ЗП, с учетом что саму ЗП в целях конфиденциальности я показывать не должен:

SELECT TOP 3 -- вернуть только 3 первые записи из всего результата

ID,LastName,FirstName

FROM Employees

ORDER BY Salary DESC -- сортируем результат по убыванию Заработной Платы

 

ID	LastName	FirstName
	Иванов	Иван
	Сидоров	Сидор

Конечно здесь есть случай, что у нескольких сотрудников может быть одинаковая ЗП и тут сложно сказать каких именно трех сотрудников вернет данный запрос, это уже нужно решать с постановщиком задачи. Допустим, после обсуждения с постановщиком данной задачи, вы согласовали и решили использовать следующий вариант – сделать дополнительную сортировку по полю даты рождения (т.е. молодым у нас дорога), а если и дата рождения у нескольких сотрудников может совпасть (ведь такое тоже не исключено), то можно сделать третью сортировку по убыванию значений ID (в последнюю очередь под выборку попадут те, у кого ID окажется максимальным – например, те кто был принят последним, допустим табельные номера у нас выдаются последовательно):

SELECT TOP 3 -- вернуть только 3 первые записи из всего результата

ID,LastName,FirstName

FROM Employees

ORDER BY

Salary DESC, -- 1. сортируем результат по убыванию Заработной Платы

Birthday, -- 2. потом по Дате рождения

ID DESC -- 3. и для полной однозначности результата добавляем сортировку по ID

 

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

Сортировать можно так же используя разные выражения в предложении ORDER BY:

SELECT LastName,FirstName

FROM Employees

ORDER BY CONCAT (LastName,' ',FirstName) -- используем выражение

 

Так же в ORDER BY можно использовать псевдонимы заданные для колонок:

SELECT CONCAT (LastName,' ',FirstName) fi

FROM Employees

ORDER BY fi -- используем псевдоним

 

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

SELECT DISTINCT

LastName,FirstName,Salary

FROM Employees

ORDER BY ID -- ID отсутствует в итоговом наборе, который мы получили при помощи DISTINCT

 

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

Примечание 1. Так же в предложении ORDER BY можно использовать номера столбцов, перечисленных в SELECT:

SELECT LastName,FirstName,Salary

FROM Employees

ORDER BY -- упорядочить в порядке

3 DESC, -- 1. убывания Заработной Платы

1, -- 2. по Фамилии

2 -- 3. по Имени

 

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

Если в данном случае (когда поля явно перечислены), такой вариант еще допустим, то для случая с использованием «*» такой вариант лучше никогда не применять. Почему – потому что, если кто-то, например, поменяет в таблице порядок столбцов, или удалит столбцы (и это нормальная ситуация), ваш запрос может так же работать, но уже неправильно, т.к. сортировка уже может идти по другим столбцам, и это коварно тем что данная ошибка может обнаружиться очень нескоро.

В случае, если бы столбы были явно перечислены, то в вышеуказанной ситуации, запрос либо бы продолжал работать, но также правильно (т.к. все явно определено), либо бы он просто выдал ошибку, что данного столбца не существует.

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

 

Примечание 2.
В MS SQL при сортировке по возрастанию NULL значения будут отображаться первыми.

SELECT BonusPercent FROM Employees ORDER BY BonusPercent

 

Соответственно при использовании DESC они будут в конце