Какие инструкции могут быть использованы в операторах выборки данных

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

Синтаксис оператора SELECT использует следующие основные предложения:

SELECT <список столбцов>
FROM <список таблиц>
[WHERE <условие выбора строк>]
[GROUP BY <условие группировки>]
[HAVING <условие выбора групп>]
[ORDER BY <условие сортировки>]

Кратко пояснить смысл предложений оператора SELECT можно следующим образом:

  • SELECT — выбрать данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями
  • FROM — из перечисленных таблиц, в которых расположены эти столбцы
  • WHERE — где строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк
  • GROUP BY — группируя по указанному перечню столбцов с тем, чтобы получить для каждой группы единственное значение
  • HAVING — имея в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп
  • ORDER BY — сортируя по указанному перечню столбцов

Как видно из синтаксиса рассматриваемого оператора, обязательными являются только два первых предложения: SELECT и FROM.

Рассмотрим каждое предложение оператора SELECT.

Спонсор поста

База данных для примеров

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

Постановка задачи: пусть требуется разработать БД для предметной области «Поставка деталей»!

Требуется хранить следующую информацию:

  • О поставщиках (P) pnum, pname
  • О деталях (D) pnum, dname, dprice
  • О поставках (PD) volume

Значения таблицы P

pnum pname
1 Иванов
2 Петров
3 Сидоров
4 Кузнецов

Значения таблицы D

pnum dname dprice
1 Болт 10
2 Гайка 20
3 Винт 30

Значения таблицы PD

pnum dnum volume
1 1 100
1 2 100
1 3 300
2 1 150
1 2 250
3 1 1000

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

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

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

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

Предложение FROM

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

Пример 1.
Вывести список наименований деталей из таблицы D (“Детали”).

SELECT dname
FROM D

Пример 2.
Получить всю информацию из таблицы D (“Детали”).

Получить результат можно двумя способами:

  • Явным указанием всех столбцов таблицы.

    SELECT dnum, dname, dprice
FROM D

  • Полный список столбцов таблицы заменяет символ *.

    SELECT *
FROM D

В результате и первого и второго запроса получаем новую таблицу, представляющую собой полную копию таблицы D (“Детали”).

Можно осуществить выбор отдельных столбцов и их перестановку.

Пример 3.
Получить информацию о наименовании и номере поставщика.

SELECT pname, pnum
FROM P

Пример 4.
Определить номера поставщиков, которые поставляют детали в настоящее время (то есть номера тех поставщиков, которые присутствуют в таблице PD (“Поставки”)).

SELECT pnum
FROM PD

Результат:

pnum
1
1
1
2
2
3

Дополнительно о SELECT

Теперь, когда мы научились делать простые запросы с SELECT и FROM, можно ненадолго снова вернуться к SELECT.

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

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

Агрегатная функция записывается в следующем виде: <имя функции>(<имя столбца>)

Пользователю доступны следующие агрегатные функции:

  • SUM ‑ вычисляет сумму множества значений указанного столбца;
  • COUNT ‑ вычисляет количество значений указанного столбца;
  • MIN / MAX ‑ определяет минимальное/максимальное значение в указанном столбце;
  • AVG ‑ вычисляет среднее арифметическое значение множества значений столбца;
  • FIRST / LAST ‑ определяет первое/последнее значение в указанном столбце.

Пример 5.
Определить общий объем поставляемых деталей.

SELECT SUM(volume)
FROM PD
Expr1000
2000

Вычисляемые столбцы

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

При вычислении результатов любой агрегатной функции СУБД сначала исключает все NULL-значения, после чего требуемая операция применяется к оставшимся значениям.

Для функции COUNT возможен особый вариант использования — COUNT(*). Его назначение состоит в подсчете всех строк в результирующей таблице, включая NULL-значения.

Следует запомнить, что агрегатные функции нельзя вкладывать друг в друга. Такая конструкция работать не будет: MAX(SUM(VOLUME))

Переименование столбца

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

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

SELECT SUM(volume) AS SUM
FROM PD
Sum
2000

Пример 6.
Определить количество поставщиков, которые поставляют детали в настоящее время.

SELECT COUNT(pnum) AS COUNT
FROM PD
Count
6

Несмотря на то, что реальное число поставщиков деталей в таблице PD равно 3, СУБД возвращает число 6. Такой результат объясняется тем, что СУБД подсчитывает все строки в таблице PD, не обращая внимание на то, что в строках есть одинаковые значения.

Операция DISTINCT

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

SELECT COUNT(DISTINCT pnum) AS COUNT
FROM PD
Count
3

DISTINCT можно задать только один раз для одного предложения SELECT.

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

Операция TOP

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

Для этого используется операция TOP, которая записывается в предложении SELECT следующим образом: SELECT TOP N [PERCENT] <список столбцов>

Пример 7.
Определить номера первых двух деталей таблицы D.

SELECT TOP 2 dnum
FROM D

Стандарт SQL требует, чтобы при сортировке NULL-значения трактовались либо как превосходящие, либо как уступающие по сравнению со всеми остальными значениями. Так как конкретный вариант стандартом не оговаривается, то в зависимости от используемой СУБД при сортировке NULL-значения следуют до или после остальных значений. В MS SQL Server NULL-значения считаются уступающими по сравнению с остальными значениями.

Предложение WHERE

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

Типы условий выбора:

  • Сравнение значений атрибутов со скалярными выражениями, другими атрибутами или результатами вычисления выражений.
  • Проверка значения на принадлежность множеству.
  • Проверка значения на принадлежность диапазону.
  • Проверка строкового значения на соответствие шаблону.
  • Проверка на наличие null-значения.

Сравнение

В языке SQL используются традиционные операции сравнения =,<>,<,<=,>,>=.

В качестве условия в предложении WHERE можно использовать сложные логические выражения, использующие атрибуты таблиц, константы, скобки, операции AND, OR, отрицание NOT.

Пример 8.
Определить номера деталей, поставляемых поставщиком с номером 2.

SELECT dnum
FROM PD
WHERE pnum = 2

Пример 9.
Получить информацию о поставщиках Иванов и Петров.

SELECT *
FROM P
WHERE pname='Иванов' OR pname='Петров'

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

Проверка на принадлежность множеству

Операция IN проверяет, принадлежит ли значение атрибута заданному множеству.

Пример 10.
Получить информацию о поставщиках ‘Иванов’ и ‘Петров’.

SELECT *
FROM P
WHERE pname IN ('Иванов','Петров')

Пример 11.
Получить информацию о деталях с номерами 1 и 2.

SELECT *
FROM D
WHERE dnum IN (1, 2)

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

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

Пример 12.
Определить номера деталей, с ценой от 10 до 20 рублей.

SELECT dnum
FROM D
WHERE dprice BETWEEN 10 AND 20

Пример 13.
Вывести наименования поставщиков, начинающихся с букв от ‘К’ по ‘П’.

SELECT pname
FROM P
WHERE pname BETWEEN 'К' AND 'Р'

Сравнение символов

Буква Р в условии запроса объясняется тем, что строки сравниваются посимвольно. Для каждого символа при этом определяется код. Для нашего случая справедливо условие: П < Петров < Р

Проверка строкового значения на соответствие шаблону

Операция LIKE используется для поиска подстрок. Значения столбца, указываемого перед служебным словом LIKE сравниваются с задаваемым после него шаблоном. Форматы шаблонов различаются в конкретных СУБД.

Для СУБД MS SQL Server:

  • Символ % заменяет любое количество любых символов.
  • Символ _ заменяет один любой символ.
  • [<множество символов>] ‑ вместо символа строки может быть подставлен один любой символ из множества возможных, указанных в ограничителях.
  • [^<множество символов>] ‑ вместо символа строки может быть подставлен любой из символов кроме символов из множества, указанного в ограничителях.

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

Пример 14.
Вывести фамилии поставщиков, начинающихся с буквы И.

SELECT pname
FROM P
WHERE pname LIKE 'И%'

Пример 15.
Вывести фамилии поставщиков, начинающихся с букв от К по П.

SELECT pname
FROM P
WHERE dname LIKE '[К-П]%'

Проверка на наличие null-значения

Операции IS NULL и IS NOT NULL используются для сравнения значения атрибута со значением NULL.

Пример 16.
Определить наименования деталей, для которых не указана цена.

SELECT dname
FROM D
WHERE dprice IS NULL

Пример 17.
Определить номера поставщиков, для которых указано наименование.

SELECT pnum
FROM P
WHERE pname IS NOT NULL

Предложение GROUP BY

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

Обычно предложение GROUP BY применяют, если формулировка задачи содержит фразу «для каждого…», «каждому..» и т.п.

Пример 18.
Определить суммарный объем деталей, поставляемых каждым поставщиком.

SELECT pnum, SUM(VOLUME) AS SUM
FROM PD
GROUP BY pnum
pnum sum
1 600
2 400
3 1000

Выполнение запроса можно описать следующим образом: СУБД разбивает таблицу PD на три группы, в каждую из групп помещаются строки с одинаковым значением номера поставщика. Затем к каждой из полученных групп применяется агрегатная функция SUM, что дает единственное итоговое значение для каждой группы.

Рассмотрим два похожих примера. В примере 19 определяется минимальный объем поставки каждого поставщика. В примере 20 определяется объем минимальной поставки среди всех поставщиков.

Пример 19:

SELECT pnum, MIN(VOLUME) AS MIN
FROM PD
GROUP BY pnum

Пример 20:

SELECT MIN(VOLUME) AS MIN
FROM P

Результаты запросов представлены в следующей таблице:

pnum min max
1 100 100
2 150
3 1000

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

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

Однако в предложении GROUP BY могут быть указаны имена столбцов, не перечисленные в списке вывода после ключевого слова SELECT.

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

Пример 21.
Для каждой из деталей с номерами 1 и 2 определить количество поставщиков, которые их поставляют, а также суммарный объем поставок деталей.

SELECT dnum, COUNT(pnum) AS COUNT, SUM(volume) AS SUM
FROM PD
WHERE dnum=1 OR dnum=2
GROUP BY dnum

Результат запроса:

dnum COUNT SUM
1 3 1250
2 2 450

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

Предложение HAVING

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

Выполнение предложения HAVING сходно с выполнением предложения WHERE. Но предложение WHERE исключает строки до того, как выполняется группировка, а предложение HAVING — после. Поэтому предложение HAVING может содержать агрегатные функции, а предложение WHERE — не может.

Пример 22.
Определить номера поставщиков, поставляющих в сумме более 500 деталей.

SELECT pnum, SUM(volume) AS SUM
FROM PD
GROUP BY pnum
HAVING SUM(volume) > 500
pnum SUM
1 600
3 1000

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

SELECT pnum, COUNT(dnum) AS COUNT
FROM PD
GROUP BY pnum
HAVING COUNT(dnum) = 1
pnum SUM
3 1

Предложение ORDER BY

При выполнении запроса СУБД возвращает строки в случайном порядке. Предложение ORDER BY позволяет упорядочить выходные данные запроса в соответствии со значениями одного или нескольких выбранных столбцов.

Можно задать возрастающий — ASC (от слова Ascend) или убывающий — DESC (от слова Descend) порядок сортировки. По умолчанию принят возрастающий порядок сортировки.

Пример 24.
Отсортировать таблицу PD в порядке возрастания номеров поставщиков, а строки с одинаковыми значениями pnum отсортировать в порядке убывания объема поставок.

SELECT pnum, volume, dnum
FROM PD
ORDER BY pnum ASC, volume DESC
pnum volume dnum
1 300 3
1 200 2
1 100 1
2 250 2
2 150 1
3 1000 1

Операцию TOP удобно применять после сортировки результирующего набора с помощью предложения ORDER BY.

Пример 25.
Определить номера первых двух деталей с наименьшей стоимостью.

SELECT TOP 2 dnum
FROM D
ORDER BY dprice ASC

Следует отметить, что если в таблице D будут две детали без указания цены, то именно их и отобразит предыдущий запрос. Поэтому при наличии NULL-значений их необходимо исключать с помощью предложения WHERE.

SELECT TOP 2 dnum
FROM D
WHERE dprice IS NOT NULL
ORDER BY dprice ASC

Заключение

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

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

Синтаксис оператора SELECT содержит несколько предложений, из которых обязательными являются только SELECT и FROM. Остальные предложения, такие как WHERE, GROUP BY, HAVING и ORDER BY, могут использоваться по желанию для уточнения выборки данных.

Источник

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

Выборка – это
обращение к БД с целью извлечь данные
в виде, удобном для пользователя. Для
выборки применяются запросы к БД. Иногда
в SQL выделяют даже раздел, который
называют языком запросов к данным DQL
(Data Query Language). Фактически этот раздел
языка ANSI SQL представлен только одной
командой – SELECT. Но эта команда достаточно
обширна. Она является ядром языка SQL. и
используется для реализации операций
проекции, ограничения, расширения.

Для пользователя
РБД оператор SELECT является, пожалуй,
одним из наиболее главных и полезных
операторов языка SQL. Этот оператор
позволяет производить:

  • выбор
    данных (отбор записей и полей);

  • вычисления
    и сравнения;

  • упорядочение
    записей при выводе содержимого таблиц;

  • группирование
    данных и применение к этим группам
    специальных групповых операций.

Источником данных
(ИД) для запроса могут быть РТ или ранее
созданные запросы.

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

В
большинстве случаев с набором записей
можно работать точно так же, как с
таблицей: можно просматривать, выбирать
и даже обновлять информацию. Однако в
отличие от реальной таблицы, этот набор
записей физически не существует в БД.
Запрос с точки зрения пользователя
можно рассматривать как шаблон, который
создает набор записей из ИД только во
время своего выполнения. При отсутствии
данных результат представляет пустой
набор записей.

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

основная часть

SELECT [предикат]
<список полей>
выбрать

FROM <список
ИД>

из

дополнительные
части

[WHERE <спецификация
выбора записей>]
где

[[GROUP BY <спецификация
группировки>]
группируя по

[HAVING <спецификация
выбора групп>]]
имея

[ORDER BY <спецификация
сортировки>]
упорядочить по.

Рассмотрим синтаксис
инструкции по частям. При этом учтем
следующее:

  • инструкция языка
    SQL – это предложение (команда, оператор);

  • отдельные составные
    части инструкции (список полей,
    спецификация) – это опции предложения;

  • любая спецификация
    – это фраза, отвечающая требованиям
    синтаксиса предложения.

Из синтаксиса
видно, что

  • основная
    часть команды SELECT …FROM обязательна;

  • опция
    предикат необязательна

  • дополнительные
    части WHERE,
    GROUP
    BY,
    ORDER
    BY
    н    еобязательны,
    они следуют за FROM;

  • опция
    HAVING не может применяться без GROUP BY.

Рассмотрим опцию

SELECT [предикат]
<список полей>.

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

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

Предикат::=

[
[ALL]
все
(обычно по умолчанию)

|
DISTINCT позволяет отобрать различные
записи, исключает

записи,
содержащие повторяющиеся данные в
отдель-

ных
полях; в
результат включаются только уникаль-

ные
значения каждого из полей, указанных в
списке

| TOP
N ]
отображение N первых записей.

Список
полей предназначен
для определения
тех полей, которіе отражаются в результате.

<Список полей>
::=

{ эл_SELECT
элемент списка

[, эл2_SELECT] …}

Отметим, что для
разделения элементов списка используются
запятые.

Рассмотрим
синтаксис конструкции для элемента
списка

эл_SELECT::=

[ИД.]

*|
отбор всех полей

| значение |
SQL_функция | системная_переменная

В свою очередь

Значение ::=

[ИД.] поле
имя
поля

[AS псевдоним]
заголовок
поля

[,[ИД.] полеК
имя
К-ого поля

[AS псевдонимК] …
] заголовок
К-ого поля

|
(выражение)

|
переменная

|
константа

Текстовые
константы должны заключаться в апострофы
или двойные кавычки.

Выражение::=
({[[+] |–] {значение | функция_СУБД}[+|–|*|**]}
… )

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

В качестве
функции_СУБД могут также применяться
специальные групповые (агрегирующие,
статистические)
SQL-функции,
которые
определяют одно значение по множеству
значений поля-аргумента.

SQL_функция ::=

{SUM
сумма

|
AVG
среднее
значение

|
MIN минимальное
значение

|
MAX максимальное
значение

|
COUNT} количество

(
[[ALL]

|
DISTINCT]

[ИД.]
поле ) аргумент

| ([ALL] выражение).

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

Ключевое
слово DISTINCT используется для исключения
полей-дубликатов перед применением
функций. Для функций МАХ и MIN это слово
излишне.

Для подсчета всех
без исключения записей в таблице, включая
дубликаты, используется специальная
функция

SQL_функция ::=
COUNT(*).

С
этой функцией слово
DISTINCT не допускается.

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

  • при
    наличии слова DISTINCT эти записи не
    учитываются;

  • функция
    COUNT возвратит значение нуль;

  • функция
    COUNT (*) обработает все записи так же, как
    обычные значения;

  • другие
    функции обычно возвращают Null-значение.

Опция
FROM <список
ИД>

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

Рассмотрим
примеры применения команды SELECT c опциями
SELECT и FROM.
В
качестве списка
ИД
будем
рассматривать
только один ИД,
а именно тС.
Таким образом,

Список
ИД::= тС.

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

Итак,
наша
первая
команда выборки на
SQL

10) SELECT *

FROM
тС → выборка
всех сотрудников, выводятся
все поля и все записи из тС, порядок
вывода полей соответствует структуре
тС, результат приведен в табл.
3.2.

Определение
“выборка сотрудников” конечно
означает
не выборку самих сотрудников, а выборку
информации о них.

Так как в качестве
ИД взята тС, опция FROM
тС будет присутствовать во всех командах
данного подраздела

Приведем примеры
реализации операции проекции.

11)
SELECT Код_с, Фам, Имя, От FROM тС

12)
SELECT Фам
FROM тС

13)
SELECT DISTINCT Фам
FROM тС

Приведем примеры
реализации операции расширения путем
формирования
вычисляемых полей.

Рассчитаем
возраст сотрудников по формуле

год(дата_текущая)
– год(д_рожд).

14)
SELECT Код_с, Фам,

(год(дата_текущая)
– год(д_рожд)) “ вычисление возраста

FROM
тС

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

В
данном предложении год( ) – это встроенная
функция конкректной СУБД. Обычно эта
функция реализуется как year( ), а текущая
дата – как date().

Таким
образом обычно
для реальных
СУБД

15)
SELECT Код_с,
Фам,

year(date())
– year(д_рожд)

FROM
тС

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

16)
SELECT Код_с, Фам,

’возраст=’,
year(date()) – year(д_рожд)

FROM
тС

Наиболее
удобный результат дает команда

17)
SELECT Код_с, Фам,

year(date())
– year(д_рожд) Аs Возраст

FROM
тС

18)
SELECT Фам,
Сount(Код_с)
FROM тС

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

19)
SELECT Count(Код_с)
FROM тС.

10

20)
SELECT Count(Код_с)
AS КвоС,

10

Max([Д_рожд])
AS MaxДр,

Count([Д_ув])
AS КвоУвол,
3

Min([Д_ув])
AS MinДув,

FROM
тС.

Рассмотрим
синтаксис других опций.

Сначала
рассмотрим опцию, которая используется
для упорядочения записей

ORDER BY <спецификация
сортировки>.

Спецификация
сортировки задается фразой, определяющей
список полей для упорядочения. Фраза
имеет следующий синтаксис:

<спецификация
сортировки>::=

{[ИД.]
поле | ном_элем_SELECT}

[[ASC]
| DESC]

[,{[ИД.]
поле2 | ном_элем_SELECT2}

[[ASC]
| DESC]] …

Рассмотрим примеры
применения
опции
ORDER.

21)
SELECT Код_с, Фам, Имя, От FROM тС

ORDER
BY Код_с

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

22)
SELECT Код_с, Фам, Имя, От FROM тС

ORDER
BY Фам, Имя, От.

Для
реализации операции ограничения
за
счет задания условий
отбора
(выбора)
записей
используется
опция

WHERE <спецификация
выбора записей>.

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

<спецификация
выбора записей>::=

[NOT]
WHERE_усл1 [[AND|OR][NOT] WHERE_усл2]…

Как видно из
синтаксиса, критерий
отбора строк может формироваться из
одного условия или из нескольких условий,
соединенных логическими операторами
AND, OR, [NOT].

Для случая двух
условий назначение логических операторов
следующее:

AND
– должны удовлетворяться оба условия
;

OR
– должно удовлетворяться одно из
условий;

AND NOT – должно
удовлетворяться первое условие (усл1)
и не должно второе условие (усл2);

OR NOT – или должно
удовлетворяться усл1 или не должно
удовлетворяться усл2.

При отборе существует
приоритет AND над OR: сначала выполняются
все операции AND и только после этого
выполняются операции OR.

Для условия отбора
можно записать следубщий синтаксис

WHERE_усл
::=

знач
{ = | <> | < | <= | | = }

{
знач | (подзапрос) }

знач_1
[NOT] BETWEEN знач_2
AND знач_3
между

знач
[NOT] IN { ( конст [,конст]… ) | (подзапрос) }
принадлежит

знач
IS [NOT] NULL
не определено

[ИД.]
поле [NOT] LIKE ‘строка_символов’
похоже на

[ESCAPE
‘символ’] не включает

EXISTS
(подзапрос)
существует

При сравнении
обычно действуют следующие правила
обработки условий:

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

  • строки символов
    сравниваются в соответствии с их
    представлением в коде, используемом в
    конкретной СУБД, например, в коде ASCII;

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

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

Рассмотрим примеры
применения опции WHERE.

23)
SELECT Фам,
Д_рожд
FROM тС

WHERE
Д_рожд = ’01-01-1980′
символьная строка

24)
SELECT Фам,
Д_рожд
FROM тС

WHERE
Д_рожд
BETWEEN ’01-01-1980′ AND ’31-12-1980′

В
операторе BETWEEN знач_2 должно быть меньше
или равно знач_3.

25)
SELECT Фам FROM тС

WHERE
Д_рожд
= ’01-01-1980′ AND Город
= “Донецк”;

26)
SELECT * FROM тС

WHERE
Город= “Донецк” OR Город= “Макеевка”.

В последнем примере
для краткой записи последовательности
отдельных сравнений, соединенных
операторами OR, можно применить форму
IN

27) SELECT
* FROM тС

WHERE
Город
IN (“Донецк”,
“Макеевка”).

При использовании
в условии формы LIKE ‘строка_символов’
интерпретация зависит от заданных
символов так:

  • символ _
    (подчеркивание) заменяет любой одиночный
    символ;

  • символ % (процент)
    заменяет любую последовательность из
    N символов, где N может быть нулем;

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

Например

28)
SELECT * FROM тС

WHERE
Город
LIKE “М%”

Очень редко, но в
поля вносятся знаки “_” и “%”. В этом
случае для их поиска применяются
дополнительные escape-символы, которые
должны предшествовать знакам.

Рассмотрим
образец поиска вида

LIKE
‘_/_а’ ESCAPE ‘/’.

В
этом выражении символ ‘/’ объявлен
escape-символом. Первый символ “_” в
заданном шаблоне поиска будет
соответствовать, как и ранее, любому
символу в проверяемой строке. Второй
символ “_”, следующий после escape-символа,
будет интерпретироваться как обычное
подчеркивание. Аналогично, символ ‘а’
будет интерпретироваться как буква а.

Для проверки
содержимого поля на наличие в нем
Null-значения
предназначены специальные операторы
IS NUll
(является пустым) и IS NOT NULL (является не
пустым). Другие операторы сравнения
использовать нельзя.

29) SELECT * FROM тС

WHERE Д_ув
Like ‘ %’ результат
Null

30) SELECT
* FROM тС

WHERE
Д_ув
IS NULL

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

31)
SELECT Фам,
COUNT(Код_с)
FROM тС

WHERE
Город
= “Донецк”
ошибка

32)
SELECT COUNT(Код_с)
As Количество
FROM тС

WHERE
Город = “Донецк”

Рассмотрим опцию,
которая применяется для группировки
записей

GROUP BY <спецификация
группировки>.

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

<спецификация
группировки>::=

[ИД.]
поле имя
поля

[,[ИД.]
полеK] … имя
K-го поля

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

33)
SELECT Фам
FROM тС
GROUP BY Фам

В
результате
этой команды происходит
исключение записей-дубликатов.

34)
SELECT Город
FROM тС
GROUP BY Город.

К
группам данных можно применить
агрегирующие SQL-функции. Для этого их
нужно указать в списке полей вывода.
Применение SQL-функций приводит к замене
всех значений группы на единственное
значение, определенной SQL-функцией
(сумма, количество и т.п.). Группирование
записей позволяет реализовать реляционную
операцию подведения итогов.

35)
SELECT Фам, Count(Код_с) AS Кол FROM тС

GROUP
BY Фам

В
этой
команде
происходит
группировка
по полю Фам, которое включено в список
полей вывода.

36)
SELECT Город,
COUNT(Код_с)
FROM тС

GROUP
BY Город

Надо
учитывать, что опция GROUP BY не предполагает
упорядочение. Поэтому рекомендуется
одновременно с ней применять и опцию
ORDER BY.

37)
SELECT Город,
COUNT(Код_с)
FROM тС

GROUP
BY Город

ORDER
BY Город

Рассмотрим
опцию
отбора групп записей:

HAVING <спецификация
выбора групп>.

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

<спецификация
выбора групп>::=

[NOT]
HAVING_усл
[[AND|OR][NOT] HAVING_усл2]…

Синтаксис HAVING_усл
почти не отличается от синтаксиса
WHERE_усл. Только наряду со значениями в
этих условиях могут использоваться
SQL-функции.

HAVING_усл
::=

знач
{ = | < | < | <= | | = }

{
знач | (подзапрос) | SQL_функция }

{знач_1
| SQL_функция_1} [NOT] BETWEEN

{знач_2
| SQL_функция_2} AND {знач_3 | SQL_функция_3}

{знач
| SQL_функция} [NOT] IN { ( конст [,конст]… ) |
(подзапрос) }

{знач
| SQL_функция}
IS [NOT] NULL

[табл.]
поле [NOT] LIKE ‘строка_символов’ [ESCAPE
‘символ’]

EXISTS
(подзапрос)

Рассмотрим
пример с опцией HAVING.

38) SELECT
* FROM тС

GROUP
BY Город

HAVING COUNT (*) < 5

Мы рассмотрели
команды с одним ИД – тС.

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

В
списке источников команды
SELECT
должны
быть перечислены
все ИД, из которых берутся поля для
формирования запроса:

Список ИД
::={таблица | запрос |
представление}
[псевдоним]

[,{таблица2
| запрос2 | представление2} [псевдоним2]]
…

где
псевдоним служит для временного (на
момент выполнения запроса) переименования
и (или) создания рабочей копии ИД.

При включении в
запрос полей нескольких ИД небходимо
указывать полные имена полей в виде
<имя
ИД>. <имя
поля>,
например, тС.Код_с.

При
включении в запрос нескольких ИД
можно
нестрого утверждать, что опция FROM
соответствует их произведению.
Следовательно,
можно сделать
вывод,
что команда SELECT
языка
SQL
реализует значительную часть операций
РА. При этом можно определить
следующее
соответствие
опций команды SELECT
и
операций РА:

  • SELECT
    ↔
    проекция
    и расширение;

  • FROM
    ↔ произведение;

  • WHERE
    ↔ ограничение.

Иными
словами, совместная
инструкция SELECT-FROM-WHERE представляет собой
проекцию выборки произведения.

В общем случае
выборка может
содержать объединение нескольких других
ранее созданных выборок. Стандарт SQL
дает следующую расширенную формулировку:

Оператор SELECT
::=

подзапрос

[UNION

объединить

[ALL]
подзапрос] …

В
качестве подзапросов могут участвовать
объекты, перечисленные в списке ИД.
Команда допускает общую опцию сортировки
ORDER
BY.

Соседние файлы в папке Обработка РБД-relalg

  • #
  • #
  • #
  • #
  • #
  • #
  • #
  • #
  • #
Источник



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



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

В статье рассказывается:

  1. Суть выборки данных
  2. Выборка данных через оператор SELECT
  3. Группировка данных при выборке
  4. Нюансы выборки данных из ORM систем

  5. Пройди тест и узнай, какая сфера тебе подходит:
    айти, дизайн или маркетинг.

    Бесплатно от Geekbrains

Суть выборки данных

Любая реляционная СУБД имеет такую функцию, как выборка данных (команда SELECT). Она является одной из самых востребованных, но при этом и сложнейших в плане синтаксиса. Однако, при всей сложности и объёмности предложений SQL, выборка данных из базы не представляет какой-то проблемы.

Суть выборки данных

Суть выборки данных

Чтобы успешно произвести выборку, необходимо чётко понимать, какая последовательность ключевых слов в запросе необходима и каким будет результат по каждому ключевому слову. Мы будем рассматривать примеры по мере усложнения. Начнём с самых простых случаев выборки данных из базы и пока не будем использовать какие-либо клаузулы или предикаты (уточняющие фразы) для определения условий, фильтрации данных в выборке и сортировке отфильтрованных значений.

Приступая к работе с выборками данных, всегда помните одно важное правило: команда SELECT в SQL-запросе всегда вернёт вам данные в формате таблицы. И неважно, насколько сложный у вас запрос. SQLite и любая другая РСУБД будет возвращать результат выборки данных в виде таблицы.

Кроме того, необходимо располагать ключевые слова в правильном порядке:

  • Начинаем с ключевого слова SELECT.
  • После него идут круглые скобки, где мы указываем колонки, из которых нам необходимо получить значения.
  • Затем следует ключевое слово FROM.
  • Пишем имя таблицы, к которой обращаемся за данными.
  • Прописываем остальные ключевые слова (тоже в строгой последовательности, но сейчас не будем останавливаться на этом подробно, дабы не запутаться).

pdf иконка

Топ-30 самых востребованных и высокооплачиваемых профессий 2023

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

doc иконка

Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка

Только проверенные нейросети с доступом из России и свободным использованием

pdf иконка

ТОП-100 площадок для поиска работы от GeekBrains

Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽

Уже скачали 22662 pdf иконка

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

Выборка данных через оператор SELECT

Элементами оператора SELECT в SQL являются блоки, определяющие параметры выражения.

Для MySQL обязательный блок — первый, сам SELECT.

Всего в SELECT есть три блока:

  • Собственно SELECT: те данные, которые мы хотим получить из базы. В каком-то смысле аналогичен переименованию и проекции в реляционной алгебре.
  • FROM: устанавливает диапазон данных в выборке (сообщает, откуда начинать выбирать). По аналогии с реляционной алгеброй это аргумент операции.
  • WHERE: обязательное условие выборки данных, которому они должны соответствовать. В реляционной алгебре подобное называется операцией выборки.

Блок SELECT

Наподобие проекции:

SELECT col1, col2, …

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

Наподобие переименования:

SELECT col1 as name1, col2 as name2, …

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

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

SELECT ‘Hello World!’ as Hello;

То получим следующую выборку:

При наличии в данных таблицы operands

запрос к базе будет иметь вид:

SELECT a, b, a+b as c FROM operands

Результат:

a b c
1 10 11
2 15 17
3 20 23

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

Скачать
файл

Блок FROM

Этот блок используется для того, чтобы уточнить аргумент SELECT. Если брать самые простые случаи, то во FROM указывают имя таблицы (отношения).

Блок FROM

Блок FROM

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

SELECT a+b FROM (SELECT 1 as a, 2 as b) as tbl1;

Дарим скидку от 60%
на обучение «Аналитик больших данных» до 01 октября

Уже через 9 месяцев сможете устроиться на работу с доходом от 150 000 рублей

Забронировать скидку


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

Предположим, у нас есть таблица bin:

По запросу в базу

SELECT * FROM bin b1 JOIN bin b2 JOIN bin b3;

получим декартово произведение bin×bin×bin:

a a a
0 0 0
1 0 0
0 1 0
1 1 0
0 0 1
1 0 1
0 1 1
1 1 1

JOIN и является оператором декартова произведения. Есть несколько вариантов JOIN: INNER JOIN, используемый по умолчанию, NATURAL, OUTER RIGHT JOIN, OUTER LEFT JOIN, OUTER FULL JOIN.

Блок WHERE

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

К примеру, по запросу:

SELECT * FROM bin WHERE a>0;

вы получите:

Помните, что любое переименование осуществляется только после выборки. Поэтому, например, выражение.

Только до 28.09

Скачай подборку материалов, чтобы гарантированно найти работу в IT за 14 дней

Список документов:


ТОП-100 площадок для поиска работы от GeekBrains


20 профессий 2023 года, с доходом от 150 000 рублей


Чек-лист «Как успешно пройти собеседование»

Чтобы получить файл, укажите e-mail:

Введите e-mail, чтобы получить доступ к документам

Подтвердите, что вы не робот,
указав номер телефона:

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


Уже скачали 52300

SELECT a as b FROM bin WHERE b>0;

неправильное: здесь блок WHERE видит только аргумент FROM, а переименование — ещё не видит.

Исходя из этого правила, блок WHERE можно использовать только совместно с блоком FROM, и выражение вроде:

SELECT 1 WHERE TRUE;

просто не будет работать.

Но, если очень нужно, можно задействовать dual («пустую» таблицу, из которой напрямую ничего нельзя выбрать — вернётся ошибка):

SELECT 1 FROM dual WHERE TRUE;

Этот вариант вполне рабочий.

Dual можно указывать, если по синтаксису SQL требуется именно таблица.

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

Группировка данных при выборке

Чтобы сгруппировать данные в SELECT-запросе при формировании выборки, применяют конструкцию group by, где перечисляются те же колонки таблицы, что и в SELECT. Рассмотрим пример выборки данных в таблицу bills по группам:

— все счета в таблице

create table bills(

id integer,

d date, — дата выставления счета

summ double precision ,— сумма счета

constraint pk_bills primary key (id)

);

— вставляем данные

insert into bills

values(1, date ‘2008-01-01’, 5.5);

insert into bills

values(2, date ‘2008-02-01’, 3.14);

insert into bills

values(3, date ‘2008-03-01’, 10.14);

insert into bills

values(4, date ‘2008-01-01’, 7.2);

insert into bills

values(5, date ‘2008-02-01’, 6.4);

insert into bills

values(6, date ‘2008-03-01’, 2.5);

commit;

— выводим данные в сгруппированном виде

select t.d, t.summ from bills t

group by t.d, t.summ

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

  • avg([DISTINCT|ALL] column) — среднее арифметическое по всей выбранной колонке;
  • count(*|[DISTINCT|ALL] соlumn) — число элементов в выборке данныхлибо в группе, которую определяет указанная колонка;
  • sum([DISTINCT | ALL] соlumn) — сумма всех значений в выбранной колонке;
  • max(соlumn) — максимальное значение в колонке;
  • min(соlumn) — минимальное значение в колонке.

С помощью ключевого слова DISTINCT можно убрать из колонки повторяющиеся значения. ALL означает, что нужно по умолчанию обработать все значения. Ключевое слово * используется, когда поля со значением null тоже нужно обрабатывать.

Следите за тем, чтобы в вашем коде для MySQL не было пробелов между скобкой и названием функции.

Рассмотрим случай, когда выбираемыми данными являются агрегатные функции. Если такая функция применяется без group by, то она охватит абсолютно все элементы выборки; в противном же случае — будет использована для каждой группы данных по отдельности. Как бы то ни было, в SELECT групповые колонки таблицы не должны смешиваться с негрупповыми.

— статистика по всем месяцам года

select count(*) as «количество записей

max(t.summ) as «макс. сумма»,

min(t.summ) as «мин. сумма»,

avg(t.summ) as «средняя сумма»,

sum(t.summ) as «общая сумма»

from bills t;

— статистика по каждому конкретному месяцу

select t.d as «месяц», count(1) as «количество записей»,

max(t.summ) as «макс. сумма»,

min(t.summ) as «мин. сумма»,

avg(t.summ) as «средняя сумма»,

sum(t.summ) as «общая сумма»

from bills t

group by t.d

Условные выражения и конструкция having (отбирающая группу) тоже могут содержать агрегатные функции.

Группировка данных при выборке

Группировка данных при выборке

— выбираем те группы элементов, чья общая сумма превышает 12

select t.d as «месяц», count(*) as «количество записей»,

max(t.summ) as «макс. сумма»,

min(t.summ) as «мин. сумма»,

avg(t.summ) as «средняя сумма»,

sum(t.summ) as «общая сумма»

from bills t

group by t.d

having sum(t.summ)>12

Выборка данных любого объёма представляет собой их множество. А это значит, что над ней можно производить операции для множества, а именно:

  • UNION — объединять в итоговой выборке данных элементы двух запросов;
  • INTERSECT — выводить только пересекающиеся записи (которые соответствуют обоим запросам);
  • EXCEPT — исключать из конечной выборки элементы, присутствующие лишь в первом запросе.

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

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

Допускаются только данные простых типов в столбцах (то есть, никаких blob и т. п.).

В MySQL5 есть только поддержка UNION. Oracle отличается тем, что EXCEPT в ней используется для иных целей, а исключение записей производится командой MINUS.

— from dual работает только в Oracle

— в MySQL запросы не могут быть заключены в круглые скобки.

select 1 as i from dual

UNION

select 2 as i from dual

UNION — можно также применить INTERSECT и EXCEPT

select 2 as i from dual

UNION

select 3 as i from dual;

Нюансы выборки данных из ORM систем

При работе с моделями данных, содержащими только одну сущность, никаких сложностей с ORM не возникает. Разберём простой пример. Предположим, у нас есть сущность Пользователь (User) с двумя атрибутами — именем (Name) и ID.

public class User {

@Id

@GeneratedValue

private int id;

private String name;

//Getters and Setters here

}

Как же вытащить из базы данных экземпляр данной сущности? Очень просто: с помощью одного метода объекта EntityManager:

EntityManager em = entityManagerFactory.createEntityManager();

User user = em.find(User.class, id);

А вот в случае, когда есть отношение «один-ко-многим», всё становится намного интереснее:

public class User {

@Id

@GeneratedValue

private int id;

private String name;

@OneToMany

private List<Address> addresses;

//Getters and Setters here

}

Наверное, вы уже задаётесь вопросом, а нужно ли делать выборку данных по адресам, извлекая экземпляр пользователя. Верный ответ — по-разному: если эти адреса нам нужны, то да, делаем, если нет — то нет. Как правило, в ORM доступны два способа выбрать зависимую запись: жадный и ленивый. Последний применяется по умолчанию во многих ORM. Однако если ваш код выглядит вот так:

EntityManager em = entityManagerFactory.createEntityManager();

User user = em.find(User.class, 1);

em.close();

System.out.println(user.getAddresses().get(0));

то вы получите исключение “LazyInitException”. Оно всегда вызывает недоумение у начинающих программистов, испытывающих недостаток опыта работы с ORM. Пора вводить новые понятия — сессия в транзакции, Detached и Attached экземпляры сущности.

Нам нужно присоединить сущность к сессии, чтобы зависимые данные оказались в выборке. Казалось бы, самое простое решение — не закрывать транзакции сразу. Но оно порождает другую проблему: транзакции удлиняются, и риск взаимной блокировки растёт. Попробовать сократить транзакции? Это возможно, однако множество коротких транзакций порождает ситуацию, когда стая крохотных комариков способна закусать огромного медведя.

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

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

Но можно пойти иным путём и просто сменить тип выборки:

public class User {

@Id

@GeneratedValue

private int id;

private String name;

@OneToMany(fetch = FetchType.EAGER)

private List<Address> addresses;

//Getters and Setters here

}

Не то чтобы это сильно помогло. Конечно, мы обойдётся без надоевшего LazyInit и постоянных проверок на то, прикреплена ли сущность к сессии. Но вот проблем с производительностью таким образом не решим: даже если адреса нам требуются не всегда, мы всё равно каждый раз их запрашиваем из памяти сервера.

Усталость от ORM и переход на другие фреймворки — нередкое явление среди бэкенд-разработчиков. Многие выбирают Spring JDBC, в котором реляционные данные можно преобразовать в объектные, причём в полуавтоматическом режиме. Необходимо писать запросы под каждую ситуацию, где требуется та или иная совокупность атрибутов. А если нужны одни и те же структуры данных, то код можно переиспользовать.

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

String name = this.jdbcTemplate.queryForObject(

«select name from t_user where id = ?»,

new Object[]{1L}, String.class);

Хотя можно выбрать и объект, как обычно:

User user = this.jdbcTemplate.queryForObject(

«select id, name from t_user where id = ?»,

new Object[]{1L},

new RowMapper<User>() {

public User mapRow(ResultSet rs, int rowNum) throws SQLException {

User user = new User();

user.setName(rs.getString(«name»));

user.setId(rs.getInt(«id»));

return user;

}

});

Если дописать в этот код ещё несколько строк и грамотно составить запрос к SQL (так, чтобы исключить проблему n+1 запроса), то можно получить и список адресов, необходимых пользователю.

Квантовый компьютер: что такое и зачем нужны

Читайте также

Подведём итог всего вышесказанного. Запросы к БД позволяют осуществлять операции выборки данных, их фильтрации, сортировки. Посредством запроса в базу можно делать расчёты, объединять данные из нескольких таблиц, удалять, редактировать, добавлять записи в таблицу. Типов запросов довольно много, и это делает запрос гибким мощным инструментом, подходящим для различных нужд (тип выбирается по назначению запроса).

Источник

Команда SELECT позволяет получить данные из базы. Существует возможность задать различные фильтры и лимиты на выборку. Попробуем привести несколько примеров SQL запросов с ограничением выборки в таблице USERS, в которой содержатся данные пользователей.

Условие WHERE в SQL запросе

Получение всех записей в таблице в одном запросе — это очень редкий случай в реальных проектах. Зачастую нужна либо одна запись, либо диапазон, к примеру из 10 или 100 записей либо отвечающее определённому условию. Такую выборку можно сделать с помощью команды условия WHERE в SQL запросе (слово WHERE переводится с английского как «ГДЕ»).

Сравнение (=, !=, <, >, <=, >=)

Продемонстрируем это условие на ограничении выборки по ID пользователя. Приведём сразу несколько примеров запросов:

SELECT * FROM `USERS` WHERE `ID` = 2;
SELECT * FROM `USERS` WHERE `ID` != 2;
SELECT * FROM `USERS` WHERE `ID` < 2;
SELECT * FROM `USERS` WHERE `ID` <= 2;
SELECT * FROM `USERS` WHERE `ID` > 2;
SELECT * FROM `USERS` WHERE `ID` >= 2;

Как можно догадаться по математическим символам в этих запросах, выборка ограничена по ID пользователя (по целому числу). При каждом условии может возвращаться разное количество строк из таблицы. К примеру, если указано «WHERE `ID` = 2», то вернётся только одна строка, потому что поле «ID» зачастую уникально (то есть у столбца установлено свойство «PRIMARY KEY»). Если в запросе есть символ неравенства «!=» или сравнения «<, >, <=, >=», то в результатах выборки может присутствовать сразу несколько строк из таблицы.

Поиск подстроки (LIKE) и полное соответствие (=)

Знак равенства «=» можно использовать в SQL запросах не только для чисел, но и для строк. Представим что нам нужно получить выборку из базы, в которой будут содержаться данные о пользователе с именем «Мышь». Запрос получится такой:

SELECT * FROM `USERS` WHERE `NAME` = 'Мышь';

В результате мы получим все строки, в которых в столбце имени пользователя «NAME» содержится строка «Мышь». Обратите внимание, что совпадение должно быть полным. То есть в выборку не попадут пользователи, имена которых «Мышь серая», «Мышь белая», «Мышь чёрная». Чтобы выбрать и этих пользователей, необходимо сделать текстовый поиск по значению столбца. Для этого используется команда «LIKE» (в переводе с английского этот предлог звучит как «ПОДОБНО», «ВРОДЕ» или «СЛОВНО»).

С помощью команды «LIKE» можно искать подстроку в столбце. Чтобы сделать это поставьте знак процента «%» с той стороны подстроки, с которой могут находиться другие символы. К примеру:

SELECT * FROM `USERS` WHERE `NAME` LIKE 'Мышь%';

В результаты выборки попадёт не только пользователь с именем «Мышь», но и «Мышь серая», «Мышь белая», «Мышь чёрная». Если поставить знак процента ещё и до подстроки:

SELECT * FROM `USERS` WHERE `NAME` LIKE '%Мышь%';

то в выборку попадут не только все предыдущие результаты, но и пользователь с именем «Большая мышь».

LIKE делает поиск независимо от регистра. То есть результаты от ‘%мышь%’ и ‘%МЫШЬ%’ будут одинаковыми.

Логика «и» (AND) и «или» (OR)

Бывают случаи, когда необходимо задать несколько ограничений, связанных логикой. К примеру, если надо выбрать пользователей с ID от 2 до 5, то можно использовать условие с «AND»:

SELECT * FROM `USERS` WHERE `ID` >= 2 AND `ID` < 5;

Количество условий и «AND» неограниченно:

SELECT * FROM `USERS` WHERE `ID` >= 2 AND `ID` < 5 AND `NAME` LIKE 'Мышь%';

Существует возможность использовать логику «ИЛИ» благодаря условию «OR». Продемонстрируем это:

SELECT * FROM `USERS` WHERE `ID` < 2 OR `ID` > 5;

С помощью круглых скобок ( ) можно группировать условия OR и AND:

SELECT * FROM `USERS` WHERE (`ID` >= 2 AND `ID` < 5) OR (`ID` > 10 AND `NAME` LIKE 'Мышь%');

Выбор определённых столбцов в SELECT

В этой статье во всех SELECT запросах к базе запрашивались все поля. Потому что после слова SELECT стояла звёздочка *. Но чем больше объём данных вы выборке, тем медленнее база данных возвращает ответ. Поэтому старайтесь запрашивать у базы только то, что будете использовать. К примеру, если нужно получить только ID пользователя и имя ‘NAME’, то перечислите эти поля через запятую после слова SELECT: 

SELECT `ID`, `NAME` FROM `USERS` WHERE `ID` <= 5;

Базы данных сайтов не приспособлены к получению больших выборок. Быстрее всего они работают на объёмах до 100 строк. Если попробовать запросить 100 000 строк из базы и указать вместо конкретных полей *, то можно будет увидеть значительное падение производительности. А чем медленнее загружается ваш сайт, тем меньше посетителей на него будут заходить. Поэтому всегда старайтесь оптимизировать свои запросы к базе.

Источник

Вступление и DDL – Data Definition Language (язык описания данных)

Часть первая — habrahabr.ru/post/255361

DML – Data Manipulation Language (язык манипулирования данными)

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

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

Т.к. учебник посвящается широкому кругу читателей (не только программистам), то и объяснение, порой будет соответствующее, т.е. долгое и нудное. Это мое видение материала, которое в основном получено на практике в результате профессиональной деятельности.

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

Т.к. DML в диалекте БД MS SQL очень сильно связан с синтаксисом конструкции SELECT, то я начну рассказывать о DML именно с нее. На мой взгляд конструкция SELECT является самой главной конструкцией языка DML, т.к. за счет нее или ее частей осуществляется выборка необходимых данных из БД.

Язык DML содержит следующие конструкции:

  • SELECT – выборка данных
  • INSERT – вставка новых данных
  • UPDATE – обновление данных
  • DELETE – удаление данных
  • MERGE – слияние данных

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

SELECT [DISTINCT] список_столбцов или *
FROM источник
WHERE фильтр
ORDER BY выражение_сортировки

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

Также в рамках этой части, я еще расскажу о предложении TOP. Это предложение я намерено не указал в базовом синтаксисе, т.к. оно реализуется по-разному в разных диалектах языка SQL.

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

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

Примеры будут показываться на БД Test, которая была создана при помощи DDL+DML в первой части.

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

Скрипт создания БД Test

-- создание БД
CREATE DATABASE Test
GO

-- сделать БД Test текущей
USE Test
GO

-- создаем таблицы справочники
CREATE TABLE Positions(
  ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY,
  Name nvarchar(30) NOT NULL
)

CREATE TABLE Departments(
  ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY,
  Name nvarchar(30) NOT NULL
)
GO

-- заполняем таблицы справочники данными
SET IDENTITY_INSERT Positions ON
INSERT Positions(ID,Name)VALUES
(1,N'Бухгалтер'),
(2,N'Директор'),
(3,N'Программист'),
(4,N'Старший программист')
SET IDENTITY_INSERT Positions OFF
GO

SET IDENTITY_INSERT Departments ON
INSERT Departments(ID,Name)VALUES
(1,N'Администрация'),
(2,N'Бухгалтерия'),
(3,N'ИТ')
SET IDENTITY_INSERT Departments OFF
GO

-- создаем таблицу с сотрудниками
CREATE TABLE Employees(
  ID int NOT NULL,
  Name nvarchar(30),
  Birthday date,
  Email nvarchar(30),
  PositionID int,
  DepartmentID int,
  HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME(),
  ManagerID int,
CONSTRAINT PK_Employees PRIMARY KEY (ID),
CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID),
CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID),
CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID),
CONSTRAINT UQ_Employees_Email UNIQUE(Email),
CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999),
INDEX IDX_Employees_Name(Name)
)
GO

-- заполняем ее данными
INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES
(1000,N'Иванов И.И.','19550219','i.ivanov@test.tt',2,1,NULL),
(1001,N'Петров П.П.','19831203','p.petrov@test.tt',3,3,1003),
(1002,N'Сидоров С.С.','19760607','s.sidorov@test.tt',1,2,1000),
(1003,N'Андреев А.А.','19820417','a.andreev@test.tt',4,3,1000)

Все, теперь мы готовы приступить к изучению языка DML.

SELECT – оператор выборки данных

Первым делом, для активного редактора запроса, сделаем текущей БД Test, выбрав ее в выпадающем списке или же командой «USE Test».

Начнем с самой элементарной формы SELECT:

SELECT *
FROM Employees

В данном запросе мы просим вернуть все столбцы (на это указывает «*») из таблицы Employees – можно прочесть это как «ВЫБЕРИ все_поля ИЗ таблицы_сотрудники». В случае наличия кластерного индекса, возвращенные данные, скорее всего будут отсортированы по нему, в данном случае по колонке ID (но это не суть важно, т.к. в большинстве случаев сортировку мы будем указывать в явном виде сами при помощи ORDER BY …):

ID Name Birthday Email PositionID DepartmentID HireDate ManagerID
1000 Иванов И.И. 1955-02-19 i.ivanov@test.tt 2 1 2015-04-08 NULL
1001 Петров П.П. 1983-12-03 p.petrov@test.tt 3 3 2015-04-08 1003
1002 Сидоров С.С. 1976-06-07 s.sidorov@test.tt 1 2 2015-04-08 1000
1003 Андреев А.А. 1982-04-17 a.andreev@test.tt 4 3 2015-04-08 1000

Вообще стоит сказать, что в диалекте MS SQL самая простая форма запроса SELECT может не содержать блока FROM, в этом случае вы можете использовать ее, для получения каких-то значений:

SELECT
	5550/100*15,
	SYSDATETIME(), -- получение системной даты БД
	SIN(0)+COS(0)

(No column name) (No column name) (No column name)
825 2015-04-11 12:12:36.0406743 1

Обратите внимание, что выражение (5550/100*15) дало результат 825, хотя если мы посчитаем на калькуляторе получится значение (832.5). Результат 825 получился по той причине, что в нашем выражении все числа целые, поэтому и результат целое число, т.е. (5550/100) дает нам 55, а не (55.5).

Запомните следующее, что в MS SQL работает следующая логика:

  • Целое / Целое = Целое (т.е. в данном случае происходит целочисленное деление)
  • Вещественное / Целое = Вещественное
  • Целое / Вещественное = Вещественное

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

SELECT
  123/10, -- 12
  123./10, -- 12.3
  123/10. -- 12.3

Здесь (123.) = (123.0), просто в данном случае 0 можно отбросить и оставить только точку.

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

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

Для преобразования полей можно использовать функцию CAST или CONVERT. Для примера воспользуемся полем ID, оно у нас типа int:

SELECT
  ID,
  ID/100, -- здесь произойдет целочисленное деление
  CAST(ID AS float)/100, -- используем функцию CAST для преобразования в тип float
  CONVERT(float,ID)/100, -- используем функцию CONVERT для преобразования в тип float
  ID/100. -- используем преобразование за счет указания что знаменатель вещественное число
FROM Employees

ID (No column name) (No column name) (No column name) (No column name)
1000 10 10 10 10.000000
1001 10 10.01 10.01 10.010000
1002 10 10.02 10.02 10.020000
1003 10 10.03 10.03 10.030000

На заметку. В БД ORACLE синтаксис без блока FROM недопустим, там для этой цели используется системная таблица DUAL, которая содержит одну строку:

SELECT
	5550/100*15, -- а в ORACLE результат будет равен 832.5
	sysdate,
	sin(0)+cos(0)
FROM DUAL

Примечание. Имя таблицы во многих РБД может предваряться именем схемы:

SELECT *
FROM dbo.Employees -- dbo – имя схемы

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

Определение схемы в разных БД может отличатся, где-то схема непосредственно связанна с пользователем БД, т.е. в данном случае можно сказать, что схема и пользователь – это синонимы и все создаваемые в схеме объекты по сути являются объектами данного пользователя. В MS SQL схема – это независимая логическая единица, которая может быть создана сама по себе (см. CREATE SCHEMA).

По умолчанию в базе MS SQL создается одна схема с именем dbo (Database Owner) и все создаваемые объекты по умолчанию создаются именно в данной схеме. Соответственно, если мы в запросе указываем просто имя таблицы, то она будет искаться в схеме dbo текущей БД. Если мы хотим создать объект в конкретной схеме, мы должны будем так же предварить имя объекта именем схемы, например, «CREATE TABLE имя_схемы.имя_таблицы(…)».

В случае MS SQL имя схемы может еще предваряться именем БД, в которой находится данная схема:

SELECT *
FROM Test.dbo.Employees -- имя_базы.имя_схемы.таблица

Такое уточнение бывает полезным, например, если:

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

Схема – очень удобное средство, которое полезно использовать при разработке архитектуры БД, а особенно крупных БД.

Так же не забываем, что в тексте запроса мы можем использовать как однострочные «— …», так и многострочные «/* … */» комментарии. Если запрос большой и сложный, то комментарии могут очень помочь, вам или кому-то другому, через некоторое время, вспомнить или разобраться в его структуре.

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

SELECT ID,Name
FROM Employees

Т.е. здесь мы говорим, что нам из таблицы нужно вернуть только поля ID и Name. Результат будет следующим (кстати оптимизатор здесь решил воспользоваться индексом, созданным по полю Name):

ID Name
1003 Андреев А.А.
1000 Иванов И.И.
1001 Петров П.П.
1002 Сидоров С.С.

На заметку. Порой бывает полезным посмотреть на то как осуществляется выборка данных, например, чтобы выяснить какие индексы используются. Это можно сделать если нажать кнопку «Display Estimated Execution Plan – Показать расчетный план» или установить «Include Actual Execution Plan – Включить в результат актуальный план выполнения запроса» (в данном случае мы сможем увидеть уже реальный план, соответственно, только после выполнения запроса):

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

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

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

При перечислении колонок их можно предварять именем таблицы, находящейся в блоке FROM:

SELECT Employees.ID,Employees.Name
FROM Employees

Но такой синтаксис обычно использовать неудобно, т.к. имя таблицы может быть длинным. Для этих целей обычно задаются и применяются более короткие имена – псевдонимы (alias):

SELECT emp.ID,emp.Name
FROM Employees AS emp

или

SELECT emp.ID,emp.Name
FROM Employees emp -- ключевое слово AS можно отпустить (я предпочитаю такой вариант)

Здесь emp – псевдоним для таблицы Employees, который можно будет использоваться в контексте данного оператора SELECT. Т.е. можно сказать, что в контексте этого оператора SELECT мы задаем таблице новое имя.

Конечно, в данном случае результаты запросов будут точно такими же как и для «SELECT ID,Name FROM Employees». Для чего это нужно будет понятно дальше (даже не в этой части), пока просто запоминаем, что имя колонки можно предварять (уточнять) либо непосредственно именем таблицы, либо при помощи псевдонима. Здесь можно использовать одно из двух, т.е. если вы задали псевдоним, то и пользоваться нужно будет им, а использовать имя таблицы уже нельзя.

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

DISTINCT – отброс строк дубликатов

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

-- создадим для демонстрации временную таблицу
CREATE TABLE #Trash(
  ID int NOT NULL PRIMARY KEY,
  Col1 varchar(10),
  Col2 varchar(10),
  Col3 varchar(10)
)

-- наполним данную таблицу всяким мусором
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
1
2
3

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

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

SELECT DISTINCT DepartmentID,PositionID
FROM Employees

DepartmentID PositionID
1 2
2 1
3 3
3 4

Здесь мы получили 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
1000 Иванов И.И. Иванов Иван Иванович 5000 50
1001 Петров П.П. Петров Петр Петрович 1500 15
1002 Сидоров С.С. Сидоров Сидор NULL 2500 NULL
1003 Андреев А.А. Андреев Андрей NULL 2000 30

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

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

SELECT
  -- даем имя вычисляемому столбцу
  LastName+' '+FirstName+' '+MiddleName AS ФИО,
  -- использование двойных кавычек, т.к. используется пробел
  HireDate AS "Дата приема",
  -- использование квадратных скобок, т.к. используется пробел
  Birthday AS [Дата рождения],
  -- слово AS не обязательно
  Salary ZP
FROM Employees

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

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

Обратите внимание, т.к. у последних 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
1000 Иванов И.И. 2500 2500 2500
1001 Петров П.П. 225 225 225
1002 Сидоров С.С. NULL 0 0
1003 Андреев А.А. 600 600 600
1004 Николаев Н.Н. NULL 0 0
1005 Александров А.А. NULL 0 0

Немного расскажу о функции 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
1000 Иванов И.И.
1004 Николаев Н.Н.
1002 Сидоров С.С.

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

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

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

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

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

SELECT LastName,FirstName,Salary
FROM Employees
ORDER BY -- упорядочить в порядке
  Salary DESC, -- 1. убывания Заработной Платы
  LastName, -- 2. по Фамилии
  FirstName -- 3. по Имени

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

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

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

SELECT TOP 3 -- вернуть только 3 первые записи из всего результата
  ID,LastName,FirstName
FROM Employees
ORDER BY Salary DESC -- сортируем результат по убыванию Заработной Платы

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

Конечно здесь есть случай, что у нескольких сотрудников может быть одинаковая ЗП и тут сложно сказать каких именно трех сотрудников вернет данный запрос, это уже нужно решать с постановщиком задачи. Допустим, после обсуждения с постановщиком данной задачи, вы согласовали и решили использовать следующий вариант – сделать дополнительную сортировку по полю даты рождения (т.е. молодым у нас дорога), а если и дата рождения у нескольких сотрудников может совпасть (ведь такое тоже не исключено), то можно сделать третью сортировку по убыванию значений 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 они будут в конце

SELECT BonusPercent FROM Employees ORDER BY BonusPercent DESC

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

SELECT BonusPercent FROM Employees ORDER BY ISNULL(BonusPercent,100)

В ORACLE для этой цели предусмотрены 2 опции NULLS FIRST и NULLS LAST (применяется по умолчанию). Например:

SELECT BonusPercent FROM Employees ORDER BY BonusPercent DESC NULLS LAST

Обращайте на это внимание при переходе на ту или иную БД.

TOP – возврат указанного числа записей

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

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

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

SELECT TOP 2
  *
FROM Employees

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

SELECT TOP 25 PERCENT
  *
FROM Employees

На моей практике чаше применяется именно выборка по количеству строк.

Так же с TOP можно использовать опцию WITH TIES, которая поможет вернуть все строки в случае неоднозначной сортировки, т.е. это предложение вернет все строки, которые равны по составу строкам, которые попадают в выборку TOP N, в итоге строк может быть выбрано больше чем N. Давайте для демонстрации добавим еще одного «Программиста» с окладом 1500:

INSERT Employees(ID,Name,Email,PositionID,DepartmentID,ManagerID,Salary)
VALUES(1004,N'Николаев Н.Н.','n.nikolayev@test.tt',3,3,1003,1500)

и введем еще одного сотрудника без указания должности и отдела с окладом 2000:

INSERT Employees(ID,Name,Email,PositionID,DepartmentID,ManagerID,Salary)
VALUES(1005,N'Александров А.А.','a.alexandrov@test.tt',NULL,NULL,1000,2000)

Теперь давайте выберем при помощи опции WITH TIES всех сотрудников, у которых оклад совпадает с окладами 3-х сотрудников, с самым маленьким окладом (надеюсь дальше будет понятно, к чему я клоню):

SELECT TOP 3 WITH TIES
  ID,Name,Salary
FROM Employees
ORDER BY Salary

Здесь хоть и указано TOP 3, но запрос вернул 4 записи, т.к. значение Salary которое вернуло TOP 3 (1500 и 2000) оказалось у 4-х сотрудников. Наглядно это работает примерно следующим образом:

На заметку.
В разных БД TOP реализуется разными способами, в MySQL для этого есть предложение LIMIT, в котором дополнительно можно задать начальное смещение.

В ORACLE 12c, тоже ввели свой аналог совмещающий функциональность TOP и LIMIT – ищите по словам «ORACLE OFFSET FETCH». До версии 12c для этой цели обычно использовался псевдостолбец ROWNUM.

А что же будет если применить одновременно предложения DISTINCT и TOP? На такие вопросы легко ответить, проводя эксперименты. В общем, не бойтесь и не ленитесь экспериментировать, т.к. большая часть познается именно на практике. Порядок слов в операторе SELECT следующий, первым идет DISTINCT, а после него идет TOP, т.е. если рассуждать логически и читать слева-направо, то первым применится отброс дубликатов, а потом уже по этому набору будет сделан TOP. Что-ж проверим и убедимся, что так и есть:

SELECT DISTINCT TOP 2
  Salary
FROM Employees
ORDER BY Salary

Salary
1500
2000

Т.е. в результате мы получили 2 самые маленькие зарплаты из всех. Конечно может быть случай что ЗП для каких-то сотрудников может быть не указанной (NULL), т.к. схема нам это позволяет. Поэтому в зависимости от задачи принимаем решение либо обработать NULL значения в предложении ORDER BY, либо просто отбросить все записи, у которых Salary равна NULL, а для этого переходим к изучению предложения WHERE.

WHERE – условие выборки строк

Данное предложение служит для фильтрации записей по заданному условию. Например, выберем всех сотрудников работающих в «ИТ» отделе (его ID=3):

SELECT ID,LastName,FirstName,Salary
FROM Employees
WHERE DepartmentID=3 -- ИТ
ORDER BY LastName,FirstName

ID LastName FirstName Salary
1004 NULL NULL 1500
1003 Андреев Андрей 2000
1001 Петров Петр 1500

Предложение WHERE пишется до команды ORDER BY.

Порядок применения команд к исходному набору Employees следующий:

  1. WHERE – если указано, то первым делом из всего набора Employees идет отбор только удовлетворяющих условию записей
  2. DISTINCT – если указано, то отбрасываются все дубликаты
  3. ORDER BY – если указано, то делается сортировка результата
  4. TOP – если указано, то из отсортированного результата возвращается только указанное число записей

Рассмотрим для наглядности пример:

SELECT DISTINCT TOP 1
  Salary
FROM Employees
WHERE DepartmentID=3
ORDER BY Salary

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

Стоит отметить, что проверка на NULL делается не знаком равенства, а при помощи операторов IS NULL и IS NOT NULL. Просто запомните, что на NULL при помощи оператора «=» (знак равенства) сравнивать нельзя, т.к. результат выражения будет так же равен NULL.

Например, выберем всех сотрудников, у которых не указан отдел (т.е. DepartmentID IS NULL):

SELECT ID,Name
FROM Employees
WHERE DepartmentID IS NULL

ID Name
1005 Александров А.А.

Теперь для примера посчитаем бонус для всех сотрудников у которых указано значение BonusPercent (т.е. BonusPercent IS NOT NULL):

SELECT ID,Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE BonusPercent IS NOT NULL

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

Хорошо, рассказав о проблеме, нам пока сказали считать, что если (BonusPercent<=0 или BonusPercent IS NULL), то это означает что у сотрудника так же нет бонуса. Для начала, как нам сказали, так и сделаем, реализуем это при помощи логического оператора OR и NOT:

SELECT ID,Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE NOT(BonusPercent<=0 OR BonusPercent IS NULL)

Т.е. здесь мы начали изучать булевы операторы. Выражение в скобках «(BonusPercent<=0 OR BonusPercent IS NULL)» проверяет на то что у сотрудника нет бонуса, а NOT инвертирует это значение, т.е. говорит «верни всех сотрудников которые не сотрудники у которых нет бонуса».

Так же данное выражение можно переписать и сразу сказав сразу «верни всех сотрудников, у которых есть бонус» выразив это выражением (BonusPercent>0 и BonusPercent IS NOT NULL):

SELECT ID,Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE BonusPercent>0 AND BonusPercent IS NOT NULL

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

SELECT ID,Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE ISNULL(BonusPercent,0)>0

Булевы операторы и простые операторы сравнения

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

Булевых операторов в языке SQL всего 3 – AND, OR и NOT:

AND логическое И. Ставится между двумя условиями (условие1 AND условие2). Чтобы выражение вернуло True, нужно, чтобы истинными были оба условия
OR логическое ИЛИ. Ставится между двумя условиями (условие1 OR условие2). Чтобы выражение вернуло True, достаточно, чтобы истинным было только одно условие
NOT инвертирует условие/логическое_выражение. Накладывается на другое выражение (NOT логическое_выражение) и возвращает True, если логическое_выражение = False и возвращает False, если логическое_выражение = True

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

Есть следующие простые операторы сравнения, которые используются для формирования условий:

Условие Значение
= Равно
< Меньше
> Больше
<= Меньше или равно
>= Больше или равно
<>
!=		 Не равно

Плюс имеются 2 оператора для проверки значения/выражения на NULL:

IS NULL Проверка на равенство NULL
IS NOT NULL Проверка на неравенство NULL

Приоритет: 1) Все операторы сравнения; 2) NOT; 3) AND; 4) OR.

При построении сложных логических выражений используются круглые скобки:

((условие1 AND условие2) OR NOT(условие3 AND условие4 AND условие5)) OR (…)

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

Здесь я постарался дать представление о булевой алгебре в достаточном для работы объеме. Как видите, чтобы писать условия посложнее без логики уже не обойтись, но ее здесь немного (AND, OR и NOT) и придумывали ее люди, так что все достаточно логично.

Идем к завершению второй части

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

BETWEEN – проверка на вхождение в диапазон

Этот оператор имеет следующий вид:

проверяемое_значение [NOT] BETWEEN начальное_ значение AND конечное_ значение

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

Разберем на примере:

SELECT ID,Name,Salary
FROM Employees
WHERE Salary BETWEEN 2000 AND 3000 -- у кого ЗП в диапазоне 2000-3000

ID Name Salary
1002 Сидоров С.С. 2500
1003 Андреев А.А. 2000
1005 Александров А.А. 2000

Собственно, BETWEEN это упрощенная запись вида:

SELECT ID,Name,Salary
FROM Employees
WHERE Salary>=2000 AND Salary<=3000 -- все у кого ЗП в диапозоне 2000-3000

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

SELECT ID,Name,Salary
FROM Employees
WHERE Salary NOT BETWEEN 2000 AND 3000 -- аналогично выражению NOT(Salary>=2000 AND Salary<=3000)

Соответственно, в случае использования BETWEEN, IN, LIKE вы можете так же объединять их с другими условиями при помощи AND и OR:

SELECT ID,Name,Salary
FROM Employees
WHERE Salary BETWEEN 2000 AND 3000 -- у кого ЗП в диапазоне 2000-3000
  AND DepartmentID=3 -- учитывать сотрудников только отдела 3

IN – проверка на вхождение в перечень значений

Этот оператор имеет следующий вид:

проверяемое_значение [NOT] IN (значение1, значение2, …)

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

SELECT ID,Name,Salary
FROM Employees
WHERE PositionID IN(3,4) -- у кого должность равна 3 или 4

ID Name Salary
1001 Петров П.П. 1500
1003 Андреев А.А. 2000
1004 Николаев Н.Н. 1500

Т.е. по сути это аналогично следующему выражению:

SELECT ID,Name,Salary
FROM Employees
WHERE PositionID=3 OR PositionID=4 -- у кого должность равна 3 или 4

В случае NOT это будет аналогично (получим всех кроме тех, кто из отдела 3 и 4):

SELECT ID,Name,Salary
FROM Employees
WHERE PositionID NOT IN(3,4) -- аналогично выражению NOT(PositionID=3 OR PositionID=4)

Так же запрос с NOT IN можно выразить и через AND:

SELECT ID,Name,Salary
FROM Employees
WHERE PositionID<>3 AND PositionID<>4 -- равносильно PositionID NOT IN(3,4)

Учтите, что искать NULL значения при помощи конструкции IN не получится, т.к. проверка NULL=NULL вернет так же NULL, а не True:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID IN(1,2,NULL) -- NULL записи не войдут в результат

В этом случае разбивайте проверку на несколько условий:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID IN(1,2) -- 1 или 2
   OR DepartmentID IS NULL -- или NULL

Или же можно написать что-то вроде:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE ISNULL(DepartmentID,-1) IN(1,2,-1) -- если вы уверены, что в нет и не будет департамента с ID=-1

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

Так же стоит упомянуть еще более коварную ошибку, связанную с NULL, которую можно допустить при использовании конструкции NOT IN. Для примера, давайте попробуем выбрать всех сотрудников, кроме тех, у которых отдел равен 1 или у которых отдел вообще не указан, т.е. равен NULL. В качестве решения напрашивается вариант:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID NOT IN(1,NULL)

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

ID Name DepartmentID
1001 Петров П.П. 3
1002 Сидоров С.С. 2
1003 Андреев А.А. 3
1004 Николаев Н.Н. 3

Опять же шутку здесь сыграло NULL указанное в списке значений.

Разберем почему в данном случае возникла логическая ошибка. Разложим запрос при помощи AND:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID<>1
  AND DepartmentID<>NULL -- проблема из-за этой проверки на NULL - это условие всегда вернет NULL

Правое условие (DepartmentID<>NULL) нам всегда здесь даст неопределенность, т.е. NULL. Теперь вспомним таблицу истинности для оператора AND, где (TRUE AND NULL) дает NULL. Т.е. при выполнении левого условия (DepartmentID<>1) из-за неопределенного правого условия в результате мы получим неопределенное значение всего выражения (DepartmentID<>1 AND DepartmentID<>NULL), поэтому строка не войдет в результат.

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

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID NOT IN(1) -- или в данном случае просто DepartmentID<>1
  AND DepartmentID IS NOT NULL -- и отдельно проверяем на NOT NULL

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

LIKE – проверка строки по шаблону

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

Этот оператор имеет следующий вид:

проверяемая_строка [NOT] LIKE строка_шаблон [ESCAPE отменяющий_символ]

В «строке_шаблон» могут применятся следующие специальные символы:

  1. Знак подчеркивания «_» — говорит, что на его месте может стоять любой единичный символ
  2. Знак процента «%» — говорит, что на его месте может стоять сколько угодно символов, в том числе и ни одного

Рассмотрим примеры с символом «%» (на практике, кстати он чаще применяется):

SELECT ID,Name
FROM Employees
WHERE Name LIKE 'Пет%' -- у кого имя начинается с букв "Пет"

SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '%ов' -- у кого фамилия оканчивается на "ов"

SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '%ре%' -- у кого фамилия содержит сочетание "ре"

Рассмотрим примеры с символом «_»:

SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '_етров' -- у кого фамилия состоит из любого первого символа и последующих букв "етров"

SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '____ов' -- у кого фамилия состоит из четырех любых символов и последующих букв "ов"

При помощи ESCAPE можно задать отменяющий символ, который отменяет проверяющее действие специальных символов «_» и «%». Данное предложение используется, когда в строке нужно непосредственно проверить наличие знака процента или знака подчеркивания.

Для демонстрации ESCAPE давайте занесем в одну запись мусор:

UPDATE Employees
SET
  FirstName='Это_мусор, содержащий %'
WHERE ID=1005

И посмотрим, что вернут следующие запросы:

SELECT *
FROM Employees
WHERE FirstName LIKE '%!%%' ESCAPE '!' -- строка содержит знак "%"

SELECT *
FROM Employees
WHERE FirstName LIKE '%!_%' ESCAPE '!' -- строка содержит знак "_"

В случае, если требуется проверить строку на полное совпадение, то вместо LIKE лучше использовать просто знак «=»:

SELECT *
FROM Employees
WHERE FirstName='Петр'

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

В ORACLE для поиска по регулярным выражениям применяется функция REGEXP_LIKE.

Немного о строках

В случае проверки строки на наличие Unicode символов, нужно будет ставить перед кавычками символ N, т.е. N’…’. Но так как у нас в таблице все символьные поля в формате Unicode (тип nvarchar), то для этих полей можно всегда использовать такой формат. Пример:

SELECT ID,Name
FROM Employees
WHERE Name LIKE N'Пет%'

SELECT ID,LastName
FROM Employees
WHERE LastName=N'Петров'

Если делать правильно, при сравнении с полем типа varchar (ASCII) нужно стараться использовать проверки с использованием ‘…’, а при сравнении поля с типом nvarchar (Unicode) нужно стараться использовать проверки с использованием N’…’. Это делается для того, чтобы избежать в процессе выполнения запроса неявных преобразований типов. То же самое правило используем при вставке (INSERT) значений в поле или их обновлении (UPDATE).

При сравнении строк стоит учесть момент, что в зависимости от настройки БД (collation), сравнение строк может быть, как регистро-независимым (когда ‘Петров’=’ПЕТРОВ’), так и регистро-зависимым (когда ‘Петров'<>’ПЕТРОВ’).
В случае регистро-зависимой настройки, если требуется сделать поиск без учета регистра, то можно, например, сделать предварительное преобразование правого и левого выражения в один регистр – верхний или нижний:

SELECT ID,Name
FROM Employees
WHERE UPPER(Name) LIKE UPPER(N'Пет%') -- или LOWER(Name) LIKE LOWER(N'Пет%') 

SELECT ID,LastName
FROM Employees
WHERE UPPER(LastName)=UPPER(N'Петров') -- или LOWER(LastName)=LOWER(N'Петров')

Немного о датах

При проверке на дату, вы можете использовать, как и со строками одинарные кавычки ‘…’.

Вне зависимости от региональных настроек в MS SQL можно использовать следующий синтаксис дат ‘YYYYMMDD’ (год, месяц, день слитно без пробелов). Такой формат даты MS SQL поймет всегда:

SELECT ID,Name,Birthday
FROM Employees
WHERE Birthday BETWEEN '19800101' AND '19891231' -- сотрудники 80-х годов
ORDER BY Birthday

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

SELECT ID,Name,Birthday
FROM Employees
WHERE Birthday BETWEEN DATEFROMPARTS(1980,1,1) AND DATEFROMPARTS(1989,12,31)
ORDER BY Birthday

Так же есть аналогичная функция DATETIMEFROMPARTS, которая служит для задания Даты и Времени (для типа datetime).

Еще вы можете использовать функцию CONVERT, если требуется преобразовать строку в значение типа date или datetime:

SELECT
  CONVERT(date,'12.03.2015',104),
  CONVERT(datetime,'2014-11-30 17:20:15',120)

Значения 104 и 120, указывают какой формат даты используется в строке. Описание всех допустимых форматов вы можете найти в библиотеке MSDN задав в поиске «MS SQL CONVERT».

Функций для работы с датами в MS SQL очень много, ищите «ms sql функции для работы с датами».

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

Немного о числах и их преобразованиях

Информация этого раздела наверно больше будет полезна ИТ-специалистам. Если вы таковым не являетесь, а ваша цель просто научится писать запросы для получения из БД необходимой вам информации, то такие тонкости вам возможно и не понадобятся, но в любом случае можете бегло пройтись по тексту и взять что-то на заметку, т.к. если вы взялись за изучение SQL, то вы уже приобщаетесь к ИТ.

В отличие от функции преобразования CAST, в функции CONVERT можно задать третий параметр, который отвечает за стиль преобразования (формат). Для разных типов данных может использоваться свой набор стилей, которые могут повлиять на возвращаемый результат. Использование стилей мы уже затрагивали при рассмотрении преобразования строки функцией CONVERT в типы date и datetime.

Подробней про функции CAST, CONVERT и стили можно почитать в MSDN – «Функции CAST и CONVERT (Transact-SQL)»: msdn.microsoft.com/ru-ru/library/ms187928.aspx

Для упрощения примеров здесь будут использованы инструкции языка Transact-SQL – DECLARE и SET.

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

DECLARE @min_int int SET @min_int=-2147483648
DECLARE @max_int int SET @max_int=2147483647

SELECT
  -- (-2147483648)
  @min_int,CAST(@min_int AS float),CONVERT(float,@min_int),

  -- 2147483647
  @max_int,CAST(@max_int AS float),CONVERT(float,@max_int),

  -- numeric(16,6)
  @min_int/1., -- (-2147483648.000000)
  @max_int/1. -- 2147483647.000000

Возможно не стоило указывать способ неявного преобразования, получаемого делением на (1.), т.к. желательно стараться делать явные преобразования, для большего контроля типа получаемого результата. Хотя, в случае, если мы хотим получить результат типа numeric, с указанным количеством цифр после запятой, то мы можем в MS SQL применить трюк с умножением целого значения на (1., 1.0, 1.00 и т.д):

DECLARE @int int SET @int=123

SELECT
  @int*1., -- numeric(12, 0) - 0 знаков после запятой
  @int*1.0, -- numeric(13, 1) - 1 знак
  @int*1.00, -- numeric(14, 2) - 2 знака

  -- хотя порой лучше сделать явное преобразование
  CAST(@int AS numeric(20, 0)), -- 123
  CAST(@int AS numeric(20, 1)), -- 123.0
  CAST(@int AS numeric(20, 2))  -- 123.00

В некоторых случаях детали преобразования могут быть действительно важны, т.к. они влияют на правильность полученного результата, например, в случае, когда делается преобразование числового значения в строку (varchar). Рассмотрим примеры по преобразованию значений типа money и float в varchar:

-- поведение при преобразовании money в varchar
DECLARE @money money
SET @money = 1025.123456789 -- произойдет неявное преобразование в 1025.1235, т.к. тип money хранит только 4 цифры после запятой

SELECT
  @money, -- 1025.1235
  -- по умолчанию CAST и CONVERT ведут себя одинаково (т.е. грубо говоря применяется стиль 0)
  CAST(@money as varchar(20)), -- 1025.12
  CONVERT(varchar(20), @money), -- 1025.12
  CONVERT(varchar(20), @money, 0), -- 1025.12 (стиль 0 - без разделителя тысячных и 2 цифры после запятой (формат по умолчанию))

  CONVERT(varchar(20), @money, 1), -- 1,025.12 (стиль 1 - используется разделитель тысячных и 2 цифры после запятой)
  CONVERT(varchar(20), @money, 2) -- 1025.1235 (стиль 2 - без разделителя и 4 цифры после запятой)

-- поведение при преобразовании float в varchar
DECLARE @float1 float SET @float1 = 1025.123456789
DECLARE @float2 float SET @float2 = 1231025.123456789

SELECT
  @float1, -- 1025.123456789
  @float2, -- 1231025.12345679
  -- по умолчанию CAST и CONVERT ведут себя одинаково (т.е. грубо говоря применяется стиль 0)
  -- стиль 0 - Не более 6 разрядов. По необходимости используется экспоненциальное представление чисел
  -- при преобразовании в varchar здесь творятся действительно страшные вещи
  CAST(@float1 as varchar(20)), -- 1025.12
  CONVERT(varchar(20), @float1), -- 1025.12
  CONVERT(varchar(20), @float1, 0), -- 1025.12

  CAST(@float2 as varchar(20)), -- 1.23103e+006
  CONVERT(varchar(20), @float2), -- 1.23103e+006
  CONVERT(varchar(20), @float2, 0), -- 1.23103e+006
  
  -- стиль 1 - Всегда 8 разрядов. Всегда используется экспоненциальное представление чисел.
  -- этот стиль для float тоже не очень точен
  CONVERT(varchar(20), @float1, 1), -- 1.0251235e+003
  CONVERT(varchar(20), @float2, 1), -- 1.2310251e+006

  -- стиль 2 - Всегда 16 разрядов. Всегда используется экспоненциальное представление чисел.
  -- здесь с точностью уже получше
  CONVERT(varchar(30), @float1, 2), -- 1.025123456789000e+003 - OK
  CONVERT(varchar(30), @float2, 2) -- 1.231025123456789e+006 - OK

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

Если нужно явно контролировать точность до определенного знака, более 4-х, то для хранения данных, порой лучше использовать тип decimal/numeric. Если хватает 4-х знаков, то можно использовать и тип money – он примерно соотвествует numeric(20,4).

-- decimal и numeric
DECLARE @money money SET @money = 1025.123456789 -- 1025.1235
DECLARE @float1 float SET @float1 = 1025.123456789
DECLARE @float2 float SET @float2 = 1231025.123456789

DECLARE @numeric numeric(28,9) SET @numeric = 1025.123456789

SELECT
  CAST(@numeric as varchar(20)), -- 1025.12345679
  CONVERT(varchar(20), @numeric), -- 1025.12345679

  CAST(@money as numeric(28,9)), -- 1025.123500000
  CAST(@float1 as numeric(28,9)), -- 1025.123456789
  CAST(@float2 as numeric(28,9)) -- 1231025.123456789

Примечание.
С версии MS SQL 2008, можно использовать вместо конструкции:

DECLARE @money money
SET @money = 1025.123456789

Более короткий синтаксис инициализации переменных:

DECLARE @money money = 1025.123456789

Заключение второй части

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

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

Удачи в изучении и применении на практике данного языка.

Часть третья — habrahabr.ru/post/255825

Источник

Понравилась статья? Поделить с друзьями:
  • Как сделать из лего машину маленькую инструкция
  • Октолипен инструкция по применению цена отзывы аналоги цена ампулы
  • Ферум лек уколы инструкция по применению цена отзывы врачей
  • Засор канализации в квартире как устранить своими руками пошаговая инструкция
  • Таблетки венарус показания к применению инструкция