Вот полный перечень функций работы со строками, взятый из BOL:
ASCII | NCHAR | SOUNDEX |
CHAR | PATINDEX | SPACE |
CHARINDEX | REPLACE | STR |
DIFFERENCE | QUOTENAME | STUFF |
LEFT | REPLICATE | SUBSTRING |
LEN | REVERSE | UNICODE |
LOWER | RIGHT | UPPER |
LTRIM | RTRIM |
Начнем с двух взаимно обратных функций - ASCII и CHAR .
Функция ASCII возвращает ASCII-код крайнего левого символа строкового выражения, являющегося аргументом функции.
Вот, например, как можно определить, сколько имеется разных букв, с которых начинаются названия кораблей в таблице Ships:
Следует отметить, что аналогичный результат можно получить проще с помощью еще одной функции - LEFT , которая имеет следующий синтаксис:
LEFT (<строковое выражение >, <целочисленное выражение >)
и вырезает заданное вторым аргументом число символов слева из строки, являющейся первым аргументом. Итак,
SELECT DISTINCT LEFT(name, 1) FROM Ships ORDER BY 1 |
А вот как, например, можно получить таблицу кодов всех алфавитных символов:
SELECT CHAR(ASCII("a")+ num-1) letter, ASCII("a")+ num - 1 FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y CROSS JOIN (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z ) x WHERE ASCII("a")+ num -1 BETWEEN ASCII("a") AND ASCII("z") |
Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье .
Как известно, коды строчных и прописных букв отличаются. Поэтому чтобы получить полный набор без переписывания запроса, достаточно просто дописать к вышеприведенному коду аналогичный:
Я полагаю, что не будет сложным добавить эту букву в таблицу, если потребуется.
Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции - CHARINDEX и PATINDEX . Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:
CHARINDEX (искомое_выражение , строковое_выражение [, стартовая_позиция ])
Здесь необязательный целочисленный параметр стартовая_позиция определяет позицию в строковом выражении, начиная с которой выполняется поиск искомого_выражения . Если этот параметр опущен, поиск выполняется от начала строкового_выражения . Например, запрос
Следует отметить, что если искомая подстрока либо строковое выражение есть NULL, то результатом функции тоже будет NULL.
Следующий пример определяет позиции первого и второго вхождения символа "a" в имени корабля "California"
SELECT CHARINDEX("a",name) first_a, CHARINDEX("a", name, CHARINDEX("a", name)+1) second_a FROM Ships WHERE name="California" |
Обратите внимание, что при определении второго символа в функции используется стартовая позиция, которой является позиция следующего за первой буквой "a" символа - CHARINDEX("a", name)+1. Правильность результата - 2 и 10 - легко проверить:-).
Функция PATINDEX имеет синтаксис:
PATINDEX ("%образец %" , строковое_выражение )
Главное отличие этой функции от CHARINDEX заключается в том, что поисковая строка может содержать подстановочные знаки - % и _. При этом концевые знаки "%" являются обязательными. Например, использование этой функции в первом примере будет иметь вид
Результат выполнения этого запроса выглядит следующим образом:
То, что в результате мы получим пустой результирующий набор, означает, что таких кораблей в базе данных нет. Давайте возьмем комбинацию значений - класс и имя корабля.
Соединение двух строковых значений в одно называется конкатенацией , и в SQL Server для этой операции используется знак "+" (в стандарте "||"). Итак,
А если строковое выражение будет содержать лишь одну букву? Запрос выведет ее. В этом легко убедиться, написав
Для извлечения данных из базы данных используется язык SQL. SQL - это язык программирования, который очень напоминает английский, но предназначен для программ управления базами данных. SQL используется в каждом запросе в Access.
Понимание принципов работы SQL помогает создавать более точные запросы и упрощает исправление запросов, которые возвращают неправильные результаты.
Это статья из цикла статей о языке SQL для Access. В ней описаны основы использования SQL для выборки данных и приведены примеры синтаксиса SQL.
SQL - это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных, таких как Microsoft Office Access, язык SQL используется для работы с данными. В отличие от многих языков программирования, SQL удобочитаем и понятен даже новичкам. Как и многие языки программирования, SQL является международным стандартом, признанным такими комитетами по стандартизации, как ISO и ANSI .
На языке SQL описываются наборы данных, помогающие получать ответы на вопросы. При использовании SQL необходимо применять правильный синтаксис. Синтаксис - это набор правил, позволяющих правильно сочетать элементы языка. Синтаксис SQL основан на синтаксисе английского языка и имеет много общих элементов с синтаксисом языка Visual Basic для приложений (VBA).
Например, простая инструкция SQL, извлекающая список фамилий контактов с именем Mary, может выглядеть следующим образом:
SELECT Last_Name
FROM Contacts
WHERE First_Name = "Mary";
Примечание: Язык SQL используется не только для выполнения операций над данными, но еще и для создания и изменения структуры объектов базы данных, например таблиц. Та часть SQL, которая используется для создания и изменения объектов базы данных, называется языком описания данных DDL. Язык DDL не рассматривается в этой статье. Дополнительные сведения см. в статье Создание и изменение таблиц или индексов с помощью запроса определения данных .
Инструкция SELECT служит для описания набора данных на языке SQL. Она содержит полное описание набора данных, которые необходимо получить из базы данных, включая следующее:
таблицы, в которых содержатся данные;
связи между данными из разных источников;
поля или вычисления, на основе которых отбираются данные;
условия отбора, которым должны соответствовать данные, включаемые в результат запроса;
необходимость и способ сортировки.
Инструкция SQL состоит из нескольких частей, называемых предложениями. Каждое предложение в инструкции SQL имеет свое назначение. Некоторые предложения являются обязательными. В приведенной ниже таблице указаны предложения SQL, используемые чаще всего.
Предложение SQL |
Описание |
Обязательное |
Определяет поля, которые содержат нужные данные. |
||
Определяет таблицы, которые содержат поля, указанные в предложении SELECT. |
||
Определяет условия отбора полей, которым должны соответствовать все записи, включаемые в результаты. |
||
Определяет порядок сортировки результатов. |
||
В инструкции SQL, которая содержит статистические функции, определяет поля, для которых в предложении SELECT не вычисляется сводное значение. |
Только при наличии таких полей |
|
В инструкции SQL, которая содержит статистические функции, определяет условия, применяемые к полям, для которых в предложении SELECT вычисляется сводное значение. |
Каждое предложение SQL состоит из терминов, которые можно сравнить с частями речи. В приведенной ниже таблице указаны типы терминов SQL.
Термин SQL |
Сопоставимая часть речи |
Определение |
Пример |
идентификатор |
существительное |
Имя, используемое для идентификации объекта базы данных, например имя поля. |
Клиенты.[НомерТелефона] |
оператор |
глагол или наречие |
Ключевое слово, которое представляет действие или изменяет его. |
|
константа |
существительное |
Значение, которое не изменяется, например число или NULL. |
|
выражение |
прилагательное |
Сочетание идентификаторов, операторов, констант и функций, предназначенное для вычисления одного значения. |
>= Товары.[Цена] |
Общий формат инструкций SQL:
SELECT field_1
FROM table_1
WHERE criterion_1
;
Примечания:
Access не учитывает разрывы строк в инструкции SQL. Несмотря на это, каждое предложение рекомендуется начинать с новой строки, чтобы инструкцию SQL было удобно читать как тому, кто ее написал, так и всем остальным.
Каждая инструкция SELECT заканчивается точкой с запятой (;). Точка с запятой может стоять как в конце последнего предложения, так и на отдельной строке в конце инструкции SQL.
В приведенном ниже примере показано, как в Access может выглядеть инструкция SQL для простого запроса на выборку.
1. Предложение SELECT
2. Предложение FROM
3. Предложение WHERE
Разберем пример по предложениям, чтобы понять, как работает синтаксис SQL.
SELECT , Company
Это предложение SELECT. Оно содержит оператор (SELECT), за которым следуют два идентификатора ("[Адрес электронной почты]" и "Компания").
Если идентификатор содержит пробелы или специальные знаки (например, "Адрес электронной почты"), он должен быть заключен в прямоугольные скобки.
В предложении SELECT не нужно указывать таблицы, в которых содержатся поля, и нельзя задать условия отбора, которым должны соответствовать данные, включаемые в результаты.
В инструкции SELECT предложение SELECT всегда стоит перед предложением FROM.
FROM Contacts
Это предложение FROM. Оно содержит оператор (FROM), за которым следует идентификатор (Контакты).
В предложении FROM не указываются поля для выборки.
WHERE City="Seattle"
Это предложение WHERE. Оно содержит оператор (WHERE), за которым следует выражение (Город="Ростов").
С помощью предложений SELECT, FROM и WHERE можно выполнять множество действий. Дополнительные сведения об использовании этих предложений см. в следующих статьях:
Как и в Microsoft Excel, в Access можно сортировать результаты запроса в таблице. Используя предложение ORDER BY, вы также можете указать способ сортировки результатов при выполнении запроса. Если используется предложение ORDER BY, оно должно находиться в конце инструкции SQL.
Предложение ORDER BY содержит список полей, для которых нужно выполнить сортировку, в том же порядке, в котором будут применена сортировка.
Предположим, например, что результаты сначала нужно отсортировать по полю "Компания" в порядке убывания, а затем, если присутствуют записи с одинаковым значением поля "Компания", - отсортировать их по полю "Адрес электронной почты" в порядке возрастания. Предложение ORDER BY будет выглядеть следующим образом:
ORDER BY Company DESC,
Примечание: По умолчанию Access сортирует значения по возрастанию (от А до Я, от наименьшего к наибольшему). Чтобы вместо этого выполнить сортировку значений по убыванию, необходимо указать ключевое слово DESC.
Дополнительные сведения о предложении ORDER BY см. в статье Предложение ORDER BY .
Иногда возникает необходимость работы со сводными данными, такими как итоговые продажи за месяц или самые дорогие товары на складе. Для этого в предложении SELECT к полю применяется агрегатная функция. Например, если в результате выполнения запроса нужно получить количество адресов электронной почты каждой компании, предложение SELECT может выглядеть следующим образом:
Возможность использования той или иной агрегатной функции зависит от типа данных в поле и нужного выражения. Дополнительные сведения о доступных агрегатных функциях см. в статье Статистические функции SQL .
При использовании агрегатных функций обычно необходимо создать предложение GROUP BY. В предложении GROUP BY указываются все поля, к которым не применяется агрегатная функция. Если агрегатные функции применяются ко всем полям в запросе, предложение GROUP BY создавать не нужно.
Предложение GROUP BY должно следовать сразу же за предложением WHERE или FROM, если предложение WHERE отсутствует. В предложении GROUP BY поля указываются в том же порядке, что и в предложении SELECT.
Продолжим предыдущий пример. Пусть в предложении SELECT агрегатная функция применяется только к полю [Адрес электронной почты], тогда предложение GROUP BY будет выглядеть следующим образом:
GROUP BY Company
Дополнительные сведения о предложении GROUP BY см. в статье Предложение GROUP BY .
Если необходимо указать условия для ограничения результатов, но поле, к которому их требуется применить, используется в агрегированной функции, предложение WHERE использовать нельзя. Вместо него следует использовать предложение HAVING. Предложение HAVING работает так же, как и WHERE, но используется для агрегированных данных.
Предположим, например, что к первому полю в предложении SELECT применяется функция AVG (которая вычисляет среднее значение):
SELECT COUNT(), Company
Если вы хотите ограничить результаты запроса на основе значения функции COUNT, к этому полю нельзя применить условие отбора в предложении WHERE. Вместо него условие следует поместить в предложение HAVING. Например, если нужно, чтобы запрос возвращал строки только в том случае, если у компании есть несколько адресов электронной почты, можно использовать следующее предложение HAVING:
HAVING COUNT()>1
Примечание: Запрос может включать и предложение WHERE, и предложение HAVING, при этом условия отбора для полей, которые не используются в статистических функциях, указываются в предложении WHERE, а условия для полей, которые используются в статистических функциях, - в предложении HAVING.
Дополнительные сведения о предложении HAVING см. в статье Предложение HAVING .
Оператор UNION используется для одновременного просмотра всех данных, возвращаемых несколькими сходными запросами на выборку, в виде объединенного набора.
Оператор UNION позволяет объединить две инструкции SELECT в одну. Объединяемые инструкции SELECT должны иметь одинаковое число и порядок выходных полей с такими же или совместимыми типами данных. При выполнении запроса данные из каждого набора соответствующих полей объединяются в одно выходное поле, поэтому выходные данные запроса имеют столько же полей, сколько и каждая инструкция SELECT по отдельности.
Примечание: В запросах на объединение числовой и текстовый типы данных являются совместимыми.
Используя оператор UNION, можно указать, должны ли в результаты запроса включаться повторяющиеся строки, если таковые имеются. Для этого следует использовать ключевое слово ALL.
Запрос на объединение двух инструкций SELECT имеет следующий базовый синтаксис:
SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;
Предположим, например, что имеется две таблицы, которые называются "Товары" и "Услуги". Обе таблицы содержат поля с названием товара или услуги, ценой и сведениями о гарантии, а также поле, в котором указывается эксклюзивность предлагаемого товара или услуги. Несмотря на то, что в таблицах "Продукты" и "Услуги" предусмотрены разные типы гарантий, основная информация одна и та же (предоставляется ли на отдельные продукты или услуги гарантия качества). Для объединения четырех полей из двух таблиц можно использовать следующий запрос на объединение:
SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;
Дополнительные сведения об объединении инструкций SELECT с помощью оператора UNION см. в статье
Программирование на T - SQL
Синтаксис и соглашения T-SQL
Правила формирования идентификаторов
Все объекты в SQL Server имеют имена (идентификаторы). Примерами объектов являются таблицы, представления, хранимые процедуры и т.д. Идентификаторы могут включать до 128 символов, в частности, буквы, символы _ @ $ # и цифры.
Первый символ всегда должен быть буквенным. Для переменных и временных таблиц используются специальные схемы именования. Имя объекта не может содержать пробелов и совпадать с зарезервированным ключевым словом SQL Server, независимо от используемого регистра символов. Путем заключения идентификаторов в квадратные скобки, в именах объектов можно использовать запрещенные символы.
Завершение инструкции
Стандарт ANSI SQL требует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языке T-SQL точка с запятой не обязательна.
Комментарии
Язык T-SQL допускает использование комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:
Это однострочный комментарий стиля ANSI
Также комментарии стиля ANSI могут вставляться в конце строки инструкции:
SELECT CityName – извлекаемые столбцы
FROM City – исходная таблица
WHERE IdCity = 1; -- ограничение на строки
Редактор SQL может применять и удалять комментарии во всех выделенных строках. Для этого нужно выбрать соответствующие команды в меню Правка или на панели инструментов.
Комментарии стиля языка С начинаются с косой черты и звездочки (/*) и заканчиваются теми же символами в обратной последовательности. Этот тип комментариев лучше использовать для комментирования блоков строк, таких как заголовки или большие тестовые запросы.
многострочного
комментария
Одним из главных достоинств комментариев стиля С является то, что многострочные запросы в них можно выполнять, даже не раскомментируя.
Пакеты T-SQL
Запросом называют одну инструкцию T-SQL, а пакетом - их набор. Вся последовательность инструкций пакета отправляется серверу из клиентских приложений как одна цельная единица.
SQL Server рассматривает весь пакет как рабочую единицу. Наличие ошибки хотя бы в одной инструкции приведет к невозможности выполнения всего пакета. В то же время грамматический разбор не проверяет имена объектов и схем, так как сама схема может измениться в процессе выполнения инструкции.
Файл сценария SQL и окно анализатора запросов (Query Analyzer) может содержать несколько пакетов. В данном случае все пакеты разделяют ключевые слова терминаторов. По умолчанию этим ключевым словом является GO, и оно должно быть единственным в строке. Все другие символы (даже комментарии) нейтрализуют разделитель пакета.
Отладка T-SQL
Когда редактор SQL обнаруживает ошибку, он отображает ее характер и номер строки в пакете. Дважды щелкнув на ошибке, можно сразу же переместиться к соответствующей строке.
В утилиту Management Studio версии SQL Server 2005 не включен отладчик языка T-SQL, - он присутствует в пакете Visual Studio.
SQL Server предлагает несколько команд, облегчающих отладку пакетов. В частности, команда PRINT отправляет сообщение без генерации результирующего набора данных. Команду PRINT можно использовать для отслеживания хода выполнения пакета. Когда анализатор запросов находится в режиме сетки, выполните следующий пакет:
SELECT CityName
FROM City
WHERE IdCity = 1;
PRINT "Контрольная точка" ;
Результирующий набор данных отобразится в сетке и будет состоять из одной строки. В то же время во вкладке «Сообщения» отобразится следующий результат:
(строк обработано: 1)
Контрольная точка
Переменные
Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:
DECLARE @Имя_Переменной Тип_Данных [,
@Имя_Переменной Тип_Данных, …]
Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символов Unicode, можно использовать следующую инструкцию:
DECLARE @UStr varchar (16)
Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLARE через запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменные a и b:
DECLARE
@a int ,
@b int
Область определения переменных (т.е. срок их жизни) распространяется только на текущий пакет. По умолчанию только что созданные переменные содержат пустые значения NULL и до включения в выражения должны быть инициализированы.
Задание значений переменных
В настоящее время в языке SQL предусмотрены два способа задания значения переменной - для этой цели можно использовать оператор SELECT или SET. С точки зрения выполняемых функций эти операторы действуют почти одинаково, не считая того, что оператор SELECT позволяет получить исходное присваиваемое значение из таблицы, указанной в операторе SELECT.
Оператор SET обычно используется для задания значений переменных в такой форме, какая более часто встречается в процедурных языках. В качестве типичных примеров применения этого оператора можно указать следующие:
SET @a = 1;
SET @b = @a * 1.5
Обратите внимание на то, что во всех этих операторах непосредственно осуществляются операции присваивания, в которых используются либо явно заданные значения, либо другие переменные. С помощью оператора SET невозможно присвоить переменной значение, полученное с помощью запроса; запрос должен быть выполнен отдельно и только после этого полученный результат может быть присвоен с помощью оператора SET. Например, попытка выполнения такого оператора вызывает ошибку:
DECLARE @c int
SET @c = COUNT (*) FROM City
SELECT @c
а следующий оператор выполняется вполне успешно:
DECLARE @c int
SET @c = (SELECT COUNT (*) FROM City)
SELECT @c
Оператор SELECT обычно используется для присваивания значений переменным, если источником информации, которая должна быть сохранена в переменной, является запрос. Например, действия, осуществляемые в приведенном выше коде, гораздо чаще реализуются с помощью оператора SELECT:
DECLARE @c int
SELECT @c = COUNT (*) FROM City
SELECT @c
Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).
Таким образом, можно, сформулировать следующее общепринятое соглашение по использованию того и другого оператора.
Оператор SET используется, если должна быть выполнена простая операция присваивания значения переменной, т.е. если присваиваемое значение уже задано явно в форме определенного значения или в виде какой-то другой переменной.
Использование переменных в запросах SQL
Одним из полезных свойств языка T-SQL является то, что переменные могут использоваться в запросах без необходимости создания сложных динамических строк, встраивающих переменные в программный код. Динамический SQL продолжает свое существование, но одиночное значение можно изменить проще - с помощью переменной.
Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:
DECLARE @IdProd int ;
SET @IdProd = 1;
SELECT
FROM Product
WHERE IdProd = @IdProd;
Глобальные системные переменные
В SQL Server имеется более тридцати глобальных переменных, не имеющих параметров, которые определяются и поддерживаются системой. Все глобальные переменные имеют префикс в виде двух символов @. Вы можете извлечь значение любой из них с помощью простого запроса SELECT, как в следующем примере:
SELECT @@CONNECTIONS
Здесь используется глобальная переменная @@CONNECTIONS для извлечения количества подключений к SQL Server со времени запуска программы.
Среди наиболее часто применяемых системных переменных можно отметить следующие:
! Следует отметить, что с версии SQL Server 2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.
Средства управления потоком команд. Программные конструкции
В языке T-SQL предусмотрена большая часть классических процедурных средств управления ходом выполнения программы, в т.ч. условная конструкция и циклы.
Оператор IF. . . ELSE
Операторы IF. . .ELSE действуют в языке T-SQL в основном так же, как и в любых других языках программирования. Общий синтаксис этого оператора имеет следующий вид:
IF Логическое выражение
SQL инструкция I BEGIN Блок SQL инструкций END
SQL инструкция | BEGIN Блок SQL инструкций END]
В качестве логического выражения может быть задано практически любое выражение, результат вычисления которого приводит к возврату булева значения.
Следует учитывать, что выполняемым по условию считается только тот оператор, который непосредственно следует за оператором IF (ближайшим к нему). Вместо одного оператора можно предусмотреть выполнение по условию нескольких операторов, объединив их в блок кода с помощью конструкции BEGIN…END.
В приведенном ниже примере условие IF не выполняется, что предотвращает выполнение следующего за ним оператора.
IF 1 = 0
PRINT "Первая строка"
PRINT "Вторая строка"
Необязательная команда ELSE позволяет задать инструкцию, которая будет выполнена в случае, если условие IF не будет выполнено. Подобно IF, оператор ELSE управляет только непосредственно следующей за ним командой или блоком кода заключенным между BEGIN…END.
Несмотря на то, что оператор IF выглядит ограниченным, его предложение условия может включать в себя мощные функции, подобно предложению WHERE. В частности это выражения IF EXISTS().
Выражение IF EXISTS() использует в качестве условия наличие какой-либо строки, возвращенной инструкцией SELECT. Так как ищутся любые строки, список столбцов в инструкции SELECT можно заменить звездочкой. Этот метод работает быстрее, чем проверка условия @@ROWCOUNT>0, потому что не требуется подсчет общего количества строк. Как только хотя бы одна строка удовлетворяет условию IF EXISTS(), запрос может продолжать выполнение.
В следующем примере выражение IF EXISTS используется для проверки наличия у клиента с кодом 1 каких-либо заказов перед удалением его из базы. Если по данному клиенту есть информация хотя бы по одному заказу, удаление не производится.
IF EXISTS (SELECT * FROM WHERE IdCust = 1)
PRINT "Невозможно удалить клиента поскольку в базе имеются связанные с ним записи"
ELSE
WHERE IdCust = 1
PRINT "Удаление произведено успешно"
Операторы WHILE, BREAK и CONTINUE
Оператор WHILE в языке SQL действует во многом так же, как и в других языках, с которыми обычно приходится работать программисту. По сути, в этом операторе до начала каждого прохода по циклу проверяется некоторое условие. Если перед очередным проходом по циклу проверка условия приводит к получению значения TRUE, осуществляется проход по циклу, в противном случае выполнение оператора завершается.
Оператор WHILE имеет следующий синтаксис:
WHILE Логическое выражение
SQL инструкция I
Блок SQL инструкций
Безусловно, с помощью оператора WHILE можно обеспечить выполнение в цикле только одного оператора (по аналогии с тем, как обычно используется оператор IF), но на практике конструкции WHILE, за которыми не следует блок BEGIN. . .END, соответствующий полному формату оператора, встречаются редко.
Оператор BREAK позволяет немедленно выйти из цикла, не ожидая того, как будет выполнен проход до конца цикла и произойдет повторная проверка условного выражения.
Оператор CONTINUE позволяет прервать отдельную итерацию цикла. Кратко можно описать действие оператора CONTINUE так, что он обеспечивает переход в начало цикла WHILE. Сразу после обнаружения оператора CONTINUE в цикле, независимо от того, где он находится, происходит переход в начало цикла и повторное вычисление условного выражения (а если значение этого выражения больше не равно TRUE, осуществляется выход из цикла).
Следующий короткий сценарий демонстрирует использование оператора WHILE для создания цикла:
DECLARE @Temp int ;
SET @Temp = 0;
WHILE @Temp < 3
BEGIN
PRINT @Temp;
SET @Temp = @Temp + 1;
Здесь в цикле целочисленная переменная @Temp увеличивается с 0 до 3 и на каждой итерации ее значение выводится на экран.
Оператор RETURN
Оператор RETURN используется для останова выполнения пакета, а следовательно, хранимой процедуры и триггера (рассматриваются в следующих лабораторных занятиях).
В первой части мы уже немного затронули язык DML, применяя почти весь набор его команд, за исключением команды MERGE.Рассказывать про DML я буду по своей последовательности выработанной на личном опыте. По ходу, так же постараюсь рассказать про «скользкие» места, на которые стоит акцентировать внимание, эти «скользкие» места, схожи во многих диалектах языка SQL.
Т.к. учебник посвящается широкому кругу читателей (не только программистам), то и объяснение, порой будет соответствующее, т.е. долгое и нудное. Это мое видение материала, которое в основном получено на практике в результате профессиональной деятельности.
Основная цель данного учебника, шаг за шагом, выработать полное понимание сути языка SQL и научить правильно применять его конструкции. Профессионалам в этой области, может тоже будет интересно пролистать данный материал, может и они смогут вынести для себя что-то новое, а может просто, будет полезно почитать в целях освежить память. Надеюсь, что всем будет интересно.
Т.к. DML в диалекте БД MS SQL очень сильно связан с синтаксисом конструкции SELECT, то я начну рассказывать о DML именно с нее. На мой взгляд конструкция SELECT является самой главной конструкцией языка DML, т.к. за счет нее или ее частей осуществляется выборка необходимых данных из БД.
Язык DML содержит следующие конструкции:
В данной части, мы рассмотрим, только базовый синтаксис команды SELECT, который выглядит следующим образом:
SELECT список_столбцов или *
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","[email protected]",2,1,NULL),
(1001,N"Петров П.П.","19831203","[email protected]",3,3,1003),
(1002,N"Сидоров С.С.","19760607","[email protected]",1,2,1000),
(1003,N"Андреев А.А.","19820417","[email protected]",4,3,1000)
Все, теперь мы готовы приступить к изучению языка DML.
Начнем с самой элементарной формы SELECT:
SELECT *
FROM Employees
В данном запросе мы просим вернуть все столбцы (на это указывает «*») из таблицы Employees – можно прочесть это как «ВЫБЕРИ все_поля ИЗ таблицы_сотрудники». В случае наличия кластерного индекса, возвращенные данные, скорее всего будут отсортированы по нему, в данном случае по колонке ID (но это не суть важно, т.к. в большинстве случаев сортировку мы будем указывать в явном виде сами при помощи ORDER BY …):
ID | Name | Birthday | PositionID | DepartmentID | HireDate | ManagerID | |
---|---|---|---|---|---|---|---|
1000 | Иванов И.И. | 1955-02-19 | [email protected] | 2 | 1 | 2015-04-08 | NULL |
1001 | Петров П.П. | 1983-12-03 | [email protected] | 3 | 3 | 2015-04-08 | 1003 |
1002 | Сидоров С.С. | 1976-06-07 | [email protected] | 1 | 2 | 2015-04-08 | 1000 |
1003 | Андреев А.А. | 1982-04-17 | [email protected] | 4 | 3 | 2015-04-08 | 1000 |
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 |
Запомните следующее, что в MS SQL работает следующая логика:
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 и научится правильно применять их, а оптимизация это уже отдельное искусство. Порой важнее, чтобы на руках просто был правильно написанный запрос, который возвращает правильные результат с предметной точки зрения, а его оптимизацией уже занимаются отдельные люди. Для начала вам нужно научиться просто правильно писать запросы, используя любые средства для достижения цели. Главная цель которую вы сейчас должны достичь – чтобы ваш запрос возвращал правильные результаты.
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.
Создадим для демонстрации временную таблицу 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
Здесь мы получили 4 строчки, т.к. повторяющихся комбинаций (DepartmentID, PositionID) в нашей таблице нет.
Создаем новые колонки 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 тоже допустимы все перечисленные выше псевдонимы столбцов, кроме […].
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 | Андреев Андрей | Андреев Андрей |
SELECT "Дата приема"=HireDate, -- помимо "…" и […] можно использовать "…" [Дата рождения]=Birthday, ZP=Salary FROM Employees
Использовать для задания псевдонима ключевое слово AS или же знак равенства, наверное, больше дело вкуса. Но при разборе чужих запросов, данные знания могут пригодиться.
Напоследок скажу, что для псевдонимов имена лучше задавать, используя только символы латиницы и цифры, избегая применения "…", "…" и […], то есть использовать те же правила, что мы использовали при наименовании таблиц. Дальше, в примерах я буду использовать только такие наименования и никаких "…", "…" и […].
И еще раз повторюсь, что любая операция с 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
Немного расскажу о функции 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
SELECT
LastName,
FirstName,
Salary
FROM Employees
ORDER BY LastName,FirstName -- упорядочить результат по 2-м столбцам – по Фамилии, и после по Имени
Для заметки. Для сортировки по возрастанию есть ключевое слово 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 | Сидоров | Сидор |
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
Обращайте на это внимание при переходе на ту или иную БД.
Вырезка из MSDN. TOP – ограничивает число строк, возвращаемых в результирующем наборе запроса до заданного числа или процентного значения. Если предложение TOP используется совместно с предложением ORDER BY, то результирующий набор ограничен первыми N строками отсортированного результата. В противном случае возвращаются первые 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"Николаев Н.Н.","[email protected]",3,3,1003,1500)
И введем еще одного сотрудника без указания должности и отдела с окладом 2000:
INSERT Employees(ID,Name,Email,PositionID,DepartmentID,ManagerID,Salary) VALUES(1005,N"Александров А.А.","[email protected]",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.
SELECT DISTINCT TOP 2
Salary
FROM Employees
ORDER BY Salary
Salary |
---|
1500 |
2000 |
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 |
Порядок применения команд к исходному набору Employees следующий:
Рассмотрим для наглядности пример:
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
Теперь для примера посчитаем бонус для всех сотрудников у которых указано значение 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:
Для каждого булева оператора можно привести таблицы истинности где дополнительно показано какой будет результат, когда условия могут быть равны NULL:
Есть следующие простые операторы сравнения, которые используются для формирования условий:
Плюс имеются 2 оператора для проверки значения/выражения на NULL:
IS NULL | Проверка на равенство NULL |
---|---|
IS NOT NULL | Проверка на неравенство NULL |
При построении сложных логических выражений используются круглые скобки:
((условие1 AND условие2) OR NOT(условие3 AND условие4 AND условие5)) OR (…)
Так же при помощи использования круглых скобок, можно изменить стандартную последовательность вычислений.
Здесь я постарался дать представление о булевой алгебре в достаточном для работы объеме. Как видите, чтобы писать условия посложнее без логики уже не обойтись, но ее здесь немного (AND, OR и NOT) и придумывали ее люди, так что все достаточно логично.
Проверяемое_значение BETWEEN начальное_ значение AND конечное_ значение
В роли значений могут выступать выражения.
Разберем на примере:
SELECT ID,Name,Salary
FROM Employees
WHERE Salary BETWEEN 2000 AND 3000 -- у кого ЗП в диапазоне 2000-3000
Собственно, 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 (значение1, значение2, …)
Думаю, проще показать на примере:
SELECT ID,Name,Salary
FROM Employees
WHERE PositionID IN(3,4) -- у кого должность равна 3 или 4
Т.е. по сути это аналогично следующему выражению:
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)
Но выполнив запрос, мы не получим ни одной строки, хотя мы ожидали увидеть следующее:
Опять же шутку здесь сыграло 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 строка_шаблон
В «строке_шаблон» могут применятся следующие специальные символы:
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.
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 свой набор функций по работе с датами и применяется свой подход по работе с ними.
В отличие от функции преобразования 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
Примечание.В первой части мы уже немного затронули язык DML, применяя почти весь набор его команд, за исключением команды MERGE.
С версии MS SQL 2008, можно использовать вместо конструкции:ms sql server Добавить метки
Рассказывать про DML я буду по своей последовательности выработанной на личном опыте. По ходу, так же постараюсь рассказать про «скользкие» места, на которые стоит акцентировать внимание, эти «скользкие» места, схожи во многих диалектах языка SQL.
Т.к. учебник посвящается широкому кругу читателей (не только программистам), то и объяснение, порой будет соответствующее, т.е. долгое и нудное. Это мое видение материала, которое в основном получено на практике в результате профессиональной деятельности.
Основная цель данного учебника, шаг за шагом, выработать полное понимание сути языка SQL и научить правильно применять его конструкции. Профессионалам в этой области, может тоже будет интересно пролистать данный материал, может и они смогут вынести для себя что-то новое, а может просто, будет полезно почитать в целях освежить память. Надеюсь, что всем будет интересно.
Т.к. DML в диалекте БД MS SQL очень сильно связан с синтаксисом конструкции SELECT, то я начну рассказывать о DML именно с нее. На мой взгляд конструкция SELECT является самой главной конструкцией языка DML, т.к. за счет нее или ее частей осуществляется выборка необходимых данных из БД.
Язык DML содержит следующие конструкции:
В данной части, мы рассмотрим, только базовый синтаксис команды SELECT, который выглядит следующим образом:
SELECT список_столбцов или *
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","[email protected]",2,1,NULL),
(1001,N"Петров П.П.","19831203","[email protected]",3,3,1003),
(1002,N"Сидоров С.С.","19760607","[email protected]",1,2,1000),
(1003,N"Андреев А.А.","19820417","[email protected]",4,3,1000)
Все, теперь мы готовы приступить к изучению языка DML.
Начнем с самой элементарной формы SELECT:
SELECT *
FROM Employees
В данном запросе мы просим вернуть все столбцы (на это указывает «*») из таблицы Employees – можно прочесть это как «ВЫБЕРИ все_поля ИЗ таблицы_сотрудники». В случае наличия кластерного индекса, возвращенные данные, скорее всего будут отсортированы по нему, в данном случае по колонке ID (но это не суть важно, т.к. в большинстве случаев сортировку мы будем указывать в явном виде сами при помощи ORDER BY …):
ID | Name | Birthday | PositionID | DepartmentID | HireDate | ManagerID | |
---|---|---|---|---|---|---|---|
1000 | Иванов И.И. | 1955-02-19 | [email protected] | 2 | 1 | 2015-04-08 | NULL |
1001 | Петров П.П. | 1983-12-03 | [email protected] | 3 | 3 | 2015-04-08 | 1003 |
1002 | Сидоров С.С. | 1976-06-07 | [email protected] | 1 | 2 | 2015-04-08 | 1000 |
1003 | Андреев А.А. | 1982-04-17 | [email protected] | 4 | 3 | 2015-04-08 | 1000 |
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 |
Запомните следующее, что в MS SQL работает следующая логика:
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 и научится правильно применять их, а оптимизация это уже отдельное искусство. Порой важнее, чтобы на руках просто был правильно написанный запрос, который возвращает правильные результат с предметной точки зрения, а его оптимизацией уже занимаются отдельные люди. Для начала вам нужно научиться просто правильно писать запросы, используя любые средства для достижения цели. Главная цель которую вы сейчас должны достичь – чтобы ваш запрос возвращал правильные результаты.
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.
Создадим для демонстрации временную таблицу 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
Здесь мы получили 4 строчки, т.к. повторяющихся комбинаций (DepartmentID, PositionID) в нашей таблице нет.
Создаем новые колонки 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 тоже допустимы все перечисленные выше псевдонимы столбцов, кроме […].
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 | Андреев Андрей | Андреев Андрей |
SELECT "Дата приема"=HireDate, -- помимо "…" и […] можно использовать "…" [Дата рождения]=Birthday, ZP=Salary FROM Employees
Использовать для задания псевдонима ключевое слово AS или же знак равенства, наверное, больше дело вкуса. Но при разборе чужих запросов, данные знания могут пригодиться.
Напоследок скажу, что для псевдонимов имена лучше задавать, используя только символы латиницы и цифры, избегая применения "…", "…" и […], то есть использовать те же правила, что мы использовали при наименовании таблиц. Дальше, в примерах я буду использовать только такие наименования и никаких "…", "…" и […].
И еще раз повторюсь, что любая операция с 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
Немного расскажу о функции 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
SELECT
LastName,
FirstName,
Salary
FROM Employees
ORDER BY LastName,FirstName -- упорядочить результат по 2-м столбцам – по Фамилии, и после по Имени
Для заметки. Для сортировки по возрастанию есть ключевое слово 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 | Сидоров | Сидор |
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
Обращайте на это внимание при переходе на ту или иную БД.
Вырезка из MSDN. TOP – ограничивает число строк, возвращаемых в результирующем наборе запроса до заданного числа или процентного значения. Если предложение TOP используется совместно с предложением ORDER BY, то результирующий набор ограничен первыми N строками отсортированного результата. В противном случае возвращаются первые 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"Николаев Н.Н.","[email protected]",3,3,1003,1500)
И введем еще одного сотрудника без указания должности и отдела с окладом 2000:
INSERT Employees(ID,Name,Email,PositionID,DepartmentID,ManagerID,Salary) VALUES(1005,N"Александров А.А.","[email protected]",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.
SELECT DISTINCT TOP 2
Salary
FROM Employees
ORDER BY Salary
Salary |
---|
1500 |
2000 |
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 |
Порядок применения команд к исходному набору Employees следующий:
Рассмотрим для наглядности пример:
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
Теперь для примера посчитаем бонус для всех сотрудников у которых указано значение 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:
Для каждого булева оператора можно привести таблицы истинности где дополнительно показано какой будет результат, когда условия могут быть равны NULL:
Есть следующие простые операторы сравнения, которые используются для формирования условий:
Плюс имеются 2 оператора для проверки значения/выражения на NULL:
IS NULL | Проверка на равенство NULL |
---|---|
IS NOT NULL | Проверка на неравенство NULL |
При построении сложных логических выражений используются круглые скобки:
((условие1 AND условие2) OR NOT(условие3 AND условие4 AND условие5)) OR (…)
Так же при помощи использования круглых скобок, можно изменить стандартную последовательность вычислений.
Здесь я постарался дать представление о булевой алгебре в достаточном для работы объеме. Как видите, чтобы писать условия посложнее без логики уже не обойтись, но ее здесь немного (AND, OR и NOT) и придумывали ее люди, так что все достаточно логично.
Проверяемое_значение BETWEEN начальное_ значение AND конечное_ значение
В роли значений могут выступать выражения.
Разберем на примере:
SELECT ID,Name,Salary
FROM Employees
WHERE Salary BETWEEN 2000 AND 3000 -- у кого ЗП в диапазоне 2000-3000
Собственно, 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 (значение1, значение2, …)
Думаю, проще показать на примере:
SELECT ID,Name,Salary
FROM Employees
WHERE PositionID IN(3,4) -- у кого должность равна 3 или 4
Т.е. по сути это аналогично следующему выражению:
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)
Но выполнив запрос, мы не получим ни одной строки, хотя мы ожидали увидеть следующее:
Опять же шутку здесь сыграло 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 строка_шаблон
В «строке_шаблон» могут применятся следующие специальные символы:
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.
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 свой набор функций по работе с датами и применяется свой подход по работе с ними.
В отличие от функции преобразования 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, можно использовать вместо конструкции: Добавить метки