Distinct sql примеры. WHERE – условие выборки строк. Задание псевдонимов для столбцов запроса
Зачастую при использовании SQL для выборки информации из таблиц, пользователь получает избыточные данные, заключающиеся в наличии абсолютно идентичных повторяющихся строк. Для исключения этой ситуации используется аргумент SQL distinct в предложении Select. В данной статье будут рассмотрены примеры использования данного аргумента, а также ситуации, в которых от применения аргумента лучше отказаться.
Прежде чем мы приступим к рассмотрению конкретных примеров, создадим в базе данных пару необходимых таблиц.
Подготовка таблицПредставим, что у нас в базе данных хранится информация об обоях, представленная в двух таблицах. Это таблица Oboi (обои) с полями id (уникальный идентификатор), type (тип обоев - бумажные, виниловые и др.), color (цвет), struct (структура) и price (цена). И таблица Ostatki (остатки) с полями id_oboi (ссылка на уникальный идентификатор в таблице Oboi) и count (количество рулонов на складе).
Заполним таблицы данными. В таблицу с обоями добавим 9 записей:
Бумажные | Мультиколор | Тисненые | ||
Бумажные двухслойные | ||||
Виниловые | Тисненые | |||
Флизелиновые | Тисненые | |||
Бумажные двухслойные | ||||
Бумажные | Мультиколор | |||
Виниловые | Коричневые | |||
Флизелиновые | Тисненые | |||
Тканевые | ||||
В таблицу с остатками - также девять записей:
Приступим к описанию порядка использования distinct в SQL.
Место distinct в предложении SelectАргумент distinct следует помещать сразу после ключевого слова Select в запросах. Он применяется сразу ко всем столбцам, указанным в предложении Select, потому что будет исключать из итогового результата запроса абсолютно идентичные строки. Таким образом, достаточно один раз указать при написании запроса SQL «select distinct». Исключение составляет использование distinct внутри агрегатных функций, что рассмотрим чуть позднее.
Следует помнить, что большинство СУБД и не распознает ваш запрос вида:
SELECT distinct Ostatki.Count, distinct Oboi.* INNER JOIN Ostatki ON Oboi.id = Ostatki.id_oboi |
Здесь несколько раз указан рассматриваемый аргумент либо указан один раз, но перед вторым, третьим или иным выбираемым столбцом. Вы получите ошибку со ссылкой на неточности в синтаксисе.
Применение distinct в стандартных запросахОчевидно, что при грамотном построении структуры таблиц и их заполнении, внутри одной таблицы исключены ситуации, когда встречаются абсолютно идентичные строки. Поэтому выполнение запроса «Select distinct *» с выборкой из одной таблицы практически нецелесообразно.
Представим ситуацию, когда нам необходимо узнать, какого типа есть у нас обои, сразу для удобства выполним сортировку по типу:
Как видим, в таблице присутствуют дублирующиеся строки. Если же мы добавим в предложение Select distinct:
Таким образом, если грамотно вносились данные в таблицы, то сразу по звонку или запросу покупателей мы сможем ответить, что жидких обоев, стеклообоев и акриловых обоев в наличии в магазине нет. Учитывая, что ассортимент в магазинах обычно не ограничивается одной сотней обоев, просмотреть перечень из неуникальных типов было бы довольно трудозатратно.
Применение distinct внутри агрегатных функцийАргумент можно использовать с любой агрегатной функцией. Но для Min и Max его применение не даст никакого эффекта, а при вычислении суммы или среднего значения редко можно представить ситуацию, когда не нужно было бы учитывать повторы.
Допустим, мы хотим узнать, насколько заполнен наш склад, и для этого отправляем запрос, вычисляющий общее количество рулонов на складе:
то получим всего 119, ведь обои под артикулами 3 и 7 находятся на складе в одинаковом количестве. Однако очевидно, что этот ответ неверен.
Чаще всего в SQL distinct применяется с функцией Count. Так, без труда мы можем узнать, сколько уникальных видов обоев у нас вообще есть:
SELECT count(distinct Oboi.type) |
И получить результат 5 - бумажные обычные и двухслойные, виниловые, тканевые и флизелиновые. Наверняка все видели рекламу типа: «Только у нас более 20 видов различных обоев!», под которой подразумевается, что в данном магазине не пара десятков рулонов всего, а обои самых разнообразных современных типов.
Интересно, что в одном запросе можно указывать несколько функций Count как с атрибутом distinct, так и без него. То есть это единственная ситуация, когда distinct в Select"е может присутствовать несколько раз.
Когда следует отказаться от применения аргументаОт применения аргумента SQL distinct следует отказаться в одном из двух случаев:
Допустим, начальник просит вас вывести список обоев, которые у вас есть, с указанием всего двух столбцов - тип и цвет. По привычке вы указываете аргумент distinct:
Может создаться впечатление, что бумажных обоев (обычных и двухслойных) у нас всего по одному виду, хотя на самом деле даже в нашей маленькой таблице их по два артикула (результат без distinct):
Поэтому, как и при написании любого запроса, с аргументом distinct надо быть аккуратным и грамотно решать вопрос с ее применением в зависимости от поставленной задачи.
Альтернатива distinctПротивоположность аргументу distinct - аргумент All. При его применении повторяющиеся строки сохраняются. Но поскольку по умолчанию СУБД так и считает, что нужно выводить все значения, то аргумент All - это скорее уточнитель, чем реальный функциональный аргумент.
Надеемся, что вам теперь понятно, когда применяется distinct (SQL). Описание дало вам полную информацию о целесообразности применения этого аргумента при решении разных задач. Ведь, как оказалось, даже такой простой аргумент в своем применении скрывает вполне ощутимую вероятность потерять некоторые данные и вывести неточную информацию.
SELECTОсновы запросов SELECT в Oracle SQL, базовый синтаксис запросов, список столбцов, псевдонимы и литералы, оператор q", ключевое слово DISTINCT
Основное средство для получения информации из базы данных - это запросы, то есть команда SELECT . Базовые возможности этой команды будут рассмотрены в этом разделе, другие, более сложные возможности - в следующих разделах. В каждом разделе будут указываться отличия по сравнению с аналогичными возможностями в Microsoft SQL Server .
Поскольку во всех главных базах данных поддерживается стандарт ANSI SQL , то базовые возможности и в Oracle , и в других базах данных (Microsoft SQL Server , IBM DB 2, Informix , Sybase , Microsoft Access ) будут схожи. Отличия - в частностях, которые стандарт SQL не определяет, но которые довольно существенны, и которые могут привести к сложностям для пользователей, которые привыкли создавать запросы к другим базам данных.
Базовый синтаксис команды SELECT в Oracle выглядит следующим образом:
SELECT [ DISTINCT ] список_столбцов FROM источник WHERE фильтр ORDER BY выражение_сортировки
Приведем пример такого запроса:
SELECT employee_id, first_name, last_name, hire_date FROM hr.employees WHERE hire_date < "01.01.2000" ORDER BY last_name
Как мы видим, пока в запросе не используются специальные возможности, он выглядит абсолютно стандартным и полностью идентичен аналогичным запросам, например, в SQL Server . Если нам нужно выбрать все столбцы из базы данных или представления, мы можем указать вместо списка столбцов звездочку:
SELECT * FROM hr.employees WHERE hire_date < "01.01.2000" ORDER BY last_name
Выводимое имя столбца можно поменять при помощи псевдонима (alias ). Для этого достаточно просто написать имя пседвонима сразу после названия столбца или использовать ключевое слово AS (оба синтаксиса полностью равнозначны):
SELECT last_name Фамилия FROM hr.employees
SELECT last_name AS Фамилия FROM hr.employees
Особенно удобно использовать псевдонимы для вычисляемых столбцов, для которых изначально имя не предусмотрено:
SELECT last_name AS Фамилия, salary*12 AS "Зарплата за год" FROM hr.employees
Если в псевдониме используются пробелы или какие-то служебные символы, то такой псевдоним нужно поместить в двойные кавычки.
Отметим и некоторые особенности, связанные со списком столбцов в Oracle . Если в SQL Server "проблемные" имена столбцов (с пробелами и зарезервированными словами) можно было помещать в квадратные скобки (а обычно и в двойные кавычки - в зависимости от настроек сеанса), то в Oracle и тот, и другой вариант вызовет ошибку.
В список столбцов в Oracle можно включать литералы (literals ). Литералы - это любое строковое значение, дата или число, которое помещается в список столбцов и при этом не является ни названием столбца, ни псевдонимом. Литералы будут повторяться для каждого возвращаемого столбца. Обычно они используются для создания пояснений в возвращаемом наборе результатов:
SELECT last_name AS Фамилия, "Зарплата за год: " , salary*12 FROM hr.employees
В этом примере "Зарплата за год: " - это литерал, который будет выводиться для каждой записи.
Строковые литералы и литералы с датой должны заключаться в одинарные кавычки. Литералы в виде числовых значений в кавычки не помещаются.
Отметим, что литералы часто сливаются в выводимых результатах с возвращаемыми из базы данных значениями при помощи оператора конкатенации (||). Про операторы Oracle будет рассказываться в разделах 2.2 , 2.3 , .
При работе с литералами иногда возникает проблема, связанная с тем, что литерал сам по себе может содержать зарезервированный символ, например, одинарные кавычки. Oracle , в отличие, например, от SQL Server , позволяет пользователю самостоятельно определять символ, который будет использоваться в качестве кавычек. Выглядеть такое переопределение может. например, так:
select department _ name , q "[ It " s assigned manager ID : ]" , manager _ id from departments
Попытка применить обычные одинарные кавычки для литерала приведет к ошибке, поскольку кавычка встречается внутри самого символьного значения литерала. Поэтому в этом примере в качестве кавычек выступают квадратные скобки.
Отметим некоторые особенности применения оператора q ":
- можно использовать как строчную букву q , так и заглавную (Q) ;
- после q должна идти обычная одинарная кавычка. Следующая одинарная кавчка определяет завершение области действия оператора q ;
- сразу после одинарной кавычкой должен идти символ, выбранный пользователем для применения в качестве кавычек. Этот символ может быть любым, за исключением пробела, перевода строки или табуляции (в том числе и одинарная кавычка). Если пользователь выбрал символ (, {, [ или . В других случаях закрытие кавычек производится при помощи того же символа, который использовался для их открытия.
И последний момент, связанный с базовым синтаксисом запросов. По умолчанию запрос возвращает из базы данных все значения, соответствующие параметрам запроса, даже если некоторые из возвращаемых значений будут полностью одинаковыми. Например, запрос
SELECT salary FROM hr . employees
вернет 107 строк, несмотря на то, что значительная часть значений будет повторяться.
Чтобы вернуть только уникальные значения (или наборы значений, если возвращается несколько столбцов), в запросе можно использовать ключевое слово DISTINCT :
SELECT DISTINCT salary FROM hr.employees
Такой запрос вернет уже 57 значений, при этом все значения будут уникальными.
Отличительной особенностью Oracle является то, что вместо DISTINCT можно использовать ключевое слово UNIQUE . SQL Server такого не позволяет.
В первой части мы уже немного затронули язык DML, применяя почти весь набор его команд, за исключением команды MERGE.Рассказывать про DML я буду по своей последовательности выработанной на личном опыте. По ходу, так же постараюсь рассказать про «скользкие» места, на которые стоит акцентировать внимание, эти «скользкие» места, схожи во многих диалектах языка SQL.
Т.к. учебник посвящается широкому кругу читателей (не только программистам), то и объяснение, порой будет соответствующее, т.е. долгое и нудное. Это мое видение материала, которое в основном получено на практике в результате профессиональной деятельности.
Основная цель данного учебника, шаг за шагом, выработать полное понимание сути языка SQL и научить правильно применять его конструкции. Профессионалам в этой области, может тоже будет интересно пролистать данный материал, может и они смогут вынести для себя что-то новое, а может просто, будет полезно почитать в целях освежить память. Надеюсь, что всем будет интересно.
Т.к. DML в диалекте БД MS SQL очень сильно связан с синтаксисом конструкции SELECT, то я начну рассказывать о DML именно с нее. На мой взгляд конструкция SELECT является самой главной конструкцией языка DML, т.к. за счет нее или ее частей осуществляется выборка необходимых данных из БД.
Язык DML содержит следующие конструкции:
- SELECT – выборка данных
- INSERT – вставка новых данных
- UPDATE – обновление данных
- DELETE – удаление данных
- MERGE – слияние данных
В данной части, мы рассмотрим, только базовый синтаксис команды 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 – оператор выборки данных Первым делом, для активного редактора запроса, сделаем текущей БД Test, выбрав ее в выпадающем списке или же командой «USE Test».Начнем с самой элементарной формы SELECT:
SELECT *
FROM Employees
В данном запросе мы просим вернуть все столбцы (на это указывает «*») из таблицы Employees – можно прочесть это как «ВЫБЕРИ все_поля ИЗ таблицы_сотрудники». В случае наличия кластерного индекса, возвращенные данные, скорее всего будут отсортированы по нему, в данном случае по колонке ID (но это не суть важно, т.к. в большинстве случаев сортировку мы будем указывать в явном виде сами при помощи ORDER BY …):
| 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 |
Вообще стоит сказать, что в диалекте MS SQL самая простая форма запроса SELECT может не содержать блока FROM, в этом случае вы можете использовать ее, для получения каких-то значений:
SELECT
5550/100*15,
SYSDATETIME(), -- получение системной даты БД
SIN(0)+COS(0)
| 825 | 2015-04-11 12:12:36.0406743 | 1 |
Обратите внимание, что выражение (5550/100*15) дало результат 825, хотя если мы посчитаем на калькуляторе получится значение (832.5). Результат 825 получился по той причине, что в нашем выражении все числа целые, поэтому и результат целое число, т.е. (5550/100) дает нам 55, а не (55.5).
Запомните следующее, что в 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
| 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):
| 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.DISTINCT – отброс строк дубликатов Ключевое слово DISTINCT используется для того чтобы отбросить из результата запроса строки дубликаты. Грубо говоря представьте, что сначала выполняется запрос без опции DISTINCT, а затем из результата выбрасываются все дубликаты. Продемонстрируем это для большей наглядности на примере:
Создадим для демонстрации временную таблицу CREATE TABLE #Trash(ID int NOT NULL PRIMARY KEY, Col1 varchar(10), Col2 varchar(10), Col3 varchar(10)) -- наполним данную таблицу всяким мусором INSERT #Trash(ID,Col1,Col2,Col3)VALUES (1,"A","A","A"), (2,"A","B","C"), (3,"C","A","B"), (4,"A","A","B"), (5,"B","B","B"), (6,"A","A","B"), (7,"A","A","A"), (8,"C","A","B"), (9,"C","A","B"), (10,"A","A","B"), (11,"A",NULL,"B"), (12,"A",NULL,"B") -- посмотрим что возвращает запрос без опции DISTINCT SELECT Col1,Col2,Col3 FROM #Trash -- посмотрим что возвращает запрос с опцией DISTINCT SELECT DISTINCT Col1,Col2,Col3 FROM #Trash -- удалим временную таблицу DROP TABLE #Trash
Наглядно это будет выглядеть следующим образом (все дубликаты помечены одним цветом):
Теперь давайте рассмотрим где это можно применить, на более практичном примере – вернем из таблицы Employees только уникальные идентификаторы отделов (т.е. узнаем ID отделов в которых числятся сотрудники):
SELECT DISTINCT DepartmentID
FROM Employees
Здесь мы получили 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
| 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
| Иванов Иван Иванович | 2015-04-08 | 1955-02-19 | 5000 |
| Петров Петр Петрович | 2015-04-08 | 1983-12-03 | 1500 |
| NULL | 2015-04-08 | 1976-06-07 | 2500 |
| NULL | 2015-04-08 | 1982-04-17 | 2000 |
Как видим заданные нами псевдонимы столбцов, отразились в заголовке результирующей таблицы. Собственно, это и есть основное предназначение псевдонимов столбцов.
Обратите внимание, т.к. у последних 2-х сотрудников не указано отчество (NULL значение), то результат выражения «LastName+" "+FirstName+" "+MiddleName» так же вернул нам NULL.
Для соединения (сложения, конкатенации) строк в MS SQL используется символ «+».
Запомним, что все выражения в которых участвует NULL (например, деление на NULL, сложение с NULL) будут возвращать NULL.
На заметку.В случае ORACLE для объединения строк используется оператор «||» и конкатенация будет выглядеть как «LastName||" "||FirstName||" "||MiddleName». Для ORACLE стоит отметить, что у него для строковых типов есть исключение, для них NULL и пустая строка "" это одно и тоже, поэтому в ORACLE такое выражение вернет для последних 2-х сотрудников «Сидоров Сидор » и «Андреев Андрей ». На момент версии ORACLE 12c, насколько я знаю, опции которая изменяет такое поведение нет (если не прав, прошу поправить меня). Здесь мне сложно судить хорошо это или плохо, т.к. в одних случаях удобнее поведение NULL-строки как в MS SQL, а в других как в ORACLE.
В ORACLE тоже допустимы все перечисленные выше псевдонимы столбцов, кроме […].
Для того чтобы не городить конструкцию с использованием функции ISNULL, в MS SQL мы можем применить функцию CONCAT. Рассмотрим и сравним 3 варианта:
SELECT
LastName+" "+FirstName+" "+MiddleName FullName1,
-- 2 варианта для замены NULL пустыми строками "" (получаем поведение как и в ORACLE)
ISNULL(LastName,"")+" "+ISNULL(FirstName,"")+" "+ISNULL(MiddleName,"") FullName2,
CONCAT(LastName," ",FirstName," ",MiddleName) FullName3
FROM Employees
| Иванов Иван Иванович | Иванов Иван Иванович | Иванов Иван Иванович |
| Петров Петр Петрович | Петров Петр Петрович | Петров Петр Петрович |
| NULL | Сидоров Сидор | Сидоров Сидор |
| NULL | Андреев Андрей | Андреев Андрей |
В MS SQL псевдонимы еще можно задавать при помощи знака равенства:
SELECT "Дата приема"=HireDate, -- помимо "…" и […] можно использовать "…" [Дата рождения]=Birthday, ZP=Salary FROM Employees
Использовать для задания псевдонима ключевое слово AS или же знак равенства, наверное, больше дело вкуса. Но при разборе чужих запросов, данные знания могут пригодиться.
Напоследок скажу, что для псевдонимов имена лучше задавать, используя только символы латиницы и цифры, избегая применения "…", "…" и […], то есть использовать те же правила, что мы использовали при наименовании таблиц. Дальше, в примерах я буду использовать только такие наименования и никаких "…", "…" и […].
Основные арифметические операторы SQLПриоритет выполнения арифметических операторов такой же, как и в математике. Если необходимо, то порядок применения операторов можно изменить используя круглые скобки - (a+b)*(x/(y-z)).
И еще раз повторюсь, что любая операция с NULL дает NULL, например: 10+NULL, NULL*15/3, 100/NULL – все это даст в результате NULL. Т.е. говоря просто неопределенное значение не может дать определенный результат. Учитывайте это при составлении запроса и при необходимости делайте обработку NULL значений функциями ISNULL, COALESCE:
SELECT
ID,Name,
Salary/100*BonusPercent AS Result1, -- без обработки NULL значений
Salary/100*ISNULL(BonusPercent,0) AS Result2, -- используем функцию ISNULL
Salary/100*COALESCE(BonusPercent,0) AS Result3 -- используем функцию COALESCE
FROM Employees
Немного расскажу о функции 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-м столбцам – по Фамилии, и после по Имени
Стоит отметить, что в предложении ORDER BY можно использовать и поля, которые не перечислены в предложении SELECT (кроме случая, когда используется DISTINCT, об этом случае я расскажу ниже). Для примера забегу немного вперед используя опцию TOP и покажу, как например, можно отобрать 3-х сотрудников у которых самая высокая ЗП, с учетом что саму ЗП в целях конфиденциальности я показывать не должен:
SELECT TOP 3 -- вернуть только 3 первые записи из всего результата
ID,LastName,FirstName
FROM Employees
ORDER BY Salary DESC -- сортируем результат по убыванию Заработной Платы
| 1000 | Иванов | Иван |
| 1002 | Сидоров | Сидор |
Конечно здесь есть случай, что у нескольких сотрудников может быть одинаковая ЗП и тут сложно сказать каких именно трех сотрудников вернет данный запрос, это уже нужно решать с постановщиком задачи. Допустим, после обсуждения с постановщиком данной задачи, вы согласовали и решили использовать следующий вариант – сделать дополнительную сортировку по полю даты рождения (т.е. молодым у нас дорога), а если и дата рождения у нескольких сотрудников может совпасть (ведь такое тоже не исключено), то можно сделать третью сортировку по убыванию значений ID (в последнюю очередь под выборку попадут те, у кого ID окажется максимальным – например, те кто был принят последним, допустим табельные номера у нас выдаются последовательно):
SELECT TOP 3 -- вернуть только 3 первые записи из всего результата ID,LastName,FirstName FROM Employees ORDER BY Salary DESC, -- 1. сортируем результат по убыванию Заработной Платы Birthday, -- 2. потом по Дате рождения ID DESC -- 3. и для полной однозначности результата добавляем сортировку по ID
Т.е. вы должны стараться чтобы результат запроса был предсказуемым, чтобы вы могли в случае разбора полетов объяснить почему в «черный список» попали именно эти люди, т.е. все было выбрано честно, по утверждённым правилам.
Сортировать можно так же используя разные выражения в предложении ORDER BY:
SELECT LastName,FirstName FROM Employees ORDER BY CONCAT(LastName," ",FirstName) -- используем выражение
Так же в ORDER BY можно использовать псевдонимы заданные для колонок:
SELECT CONCAT(LastName," ",FirstName) fi FROM Employees ORDER BY fi -- используем псевдоним
Стоит отметить что в случае использования предложения DISTINCT, в предложении ORDER BY могут использоваться только колонки, перечисленные в блоке SELECT. Т.е. после применения операции DISTINCT мы получаем новый набор данных, с новым набором колонок. По этой причине, следующий пример не отработает:
SELECT DISTINCT LastName,FirstName,Salary FROM Employees ORDER BY ID -- ID отсутствует в итоговом наборе, который мы получили при помощи DISTINCT
Т.е. предложение ORDER BY применяется уже к итоговому набору, перед выдачей результата пользователю.
Примечание 1. Так же в предложении ORDER BY можно использовать номера столбцов, перечисленных в SELECT:SELECT LastName,FirstName,Salary FROM Employees ORDER BY -- упорядочить в порядке 3 DESC, -- 1. убывания Заработной Платы 1, -- 2. по Фамилии 2 -- 3. по Имени
Для начинающих выглядит удобно и заманчиво, но лучше забыть и никогда не использовать такой вариант сортировки.
Если в данном случае (когда поля явно перечислены), такой вариант еще допустим, то для случая с использованием «*» такой вариант лучше никогда не применять. Почему – потому что, если кто-то, например, поменяет в таблице порядок столбцов, или удалит столбцы (и это нормальная ситуация), ваш запрос может так же работать, но уже неправильно, т.к. сортировка уже может идти по другим столбцам, и это коварно тем что данная ошибка может обнаружиться очень нескоро.
В случае, если бы столбы были явно перечислены, то в вышеуказанной ситуации, запрос либо бы продолжал работать, но также правильно (т.к. все явно определено), либо бы он просто выдал ошибку, что данного столбца не существует.
Так что можете смело забыть, о сортировке по номерам столбцов.
В 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
Обращайте на это внимание при переходе на ту или иную БД.
Обычно данное выражение используется с предложением ORDER BY и мы уже смотрели примеры, когда нужно было вернуть N-первых строк из результирующего набора.
Без ORDER BY обычно данное предложение применяется, когда нужно просто посмотреть на неизвестную нам таблицу, в которой может быть очень много записей, в этом случае мы можем, для примера, попросить вернуть нам только первые 10 строк, но для наглядности мы скажем только 2:
SELECT TOP 2 * FROM Employees
Так же можно указать слово PERCENT, для того чтобы вернулось соответствуй процент строк из результирующего набора:
SELECT TOP 25 PERCENT * FROM Employees
На моей практике чаше применяется именно выборка по количеству строк.
Так же с TOP можно использовать опцию WITH TIES, которая поможет вернуть все строки в случае неоднозначной сортировки, т.е. это предложение вернет все строки, которые равны по составу строкам, которые попадают в выборку TOP N, в итоге строк может быть выбрано больше чем N. Давайте для демонстрации добавим еще одного «Программиста» с окладом 1500:
INSERT Employees(ID,Name,Email,PositionID,DepartmentID,ManagerID,Salary) VALUES(1004,N"Николаев Н.Н.","[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.
А что же будет если применить одновременно предложения DISTINCT и TOP? На такие вопросы легко ответить, проводя эксперименты. В общем, не бойтесь и не ленитесь экспериментировать, т.к. большая часть познается именно на практике. Порядок слов в операторе SELECT следующий, первым идет DISTINCT, а после него идет TOP, т.е. если рассуждать логически и читать слева-направо, то первым применится отброс дубликатов, а потом уже по этому набору будет сделан TOP. Что-ж проверим и убедимся, что так и есть:
SELECT DISTINCT TOP 2
Salary
FROM Employees
ORDER BY Salary
| 1500 |
| 2000 |
Т.е. в результате мы получили 2 самые маленькие зарплаты из всех. Конечно может быть случай что ЗП для каких-то сотрудников может быть не указанной (NULL), т.к. схема нам это позволяет. Поэтому в зависимости от задачи принимаем решение либо обработать NULL значения в предложении ORDER BY, либо просто отбросить все записи, у которых Salary равна NULL, а для этого переходим к изучению предложения WHERE.WHERE – условие выборки строк Данное предложение служит для фильтрации записей по заданному условию. Например, выберем всех сотрудников работающих в «ИТ» отделе (его ID=3):
SELECT ID,LastName,FirstName,Salary
FROM Employees
WHERE DepartmentID=3 -- ИТ
ORDER BY LastName,FirstName
| 1004 | NULL | NULL | 1500 |
| 1003 | Андреев | Андрей | 2000 |
| 1001 | Петров | Петр | 1500 |
Предложение WHERE пишется до команды ORDER BY.
Порядок применения команд к исходному набору 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), а так же значение может быть внесено со знаком минус, ведь мы не накладывали на данное поле никаких ограничений.
Хорошо, рассказав о проблеме, нам пока сказали считать, что если (BonusPercent0
Булевы операторы и простые операторы сравнения Да, без математики здесь не обойтись, поэтому сделаем небольшой экскурс по булевым и простым операторам сравнения.Булевых операторов в языке SQL всего 3 – AND, OR и NOT:
Для каждого булева оператора можно привести таблицы истинности где дополнительно показано какой будет результат, когда условия могут быть равны NULL:
Есть следующие простые операторы сравнения, которые используются для формирования условий:
Плюс имеются 2 оператора для проверки значения/выражения на NULL:
| Проверка на равенство NULL |
| Проверка на неравенство NULL |
Приоритет: 1) Все операторы сравнения; 2) NOT; 3) AND; 4) OR.
При построении сложных логических выражений используются круглые скобки:
((условие1 AND условие2) OR NOT(условие3 AND условие4 AND условие5)) OR (…)
Так же при помощи использования круглых скобок, можно изменить стандартную последовательность вычислений.
Здесь я постарался дать представление о булевой алгебре в достаточном для работы объеме. Как видите, чтобы писать условия посложнее без логики уже не обойтись, но ее здесь немного (AND, OR и NOT) и придумывали ее люди, так что все достаточно логично.
Идем к завершению второй части Как видите даже про базовый синтаксис оператора SELECT можно говорить очень долго, но, чтобы остаться в рамках статьи, напоследок я покажу дополнительные логических операторы – BETWEEN, IN и LIKE.BETWEEN – проверка на вхождение в диапазонПроверяемое_значение 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=2000 AND Salary SET { | } .,. .< COLUMN name> = < VALUE expresslon> [ WHERE < predlcate> | WHERE CURRENT OF < cursor name> (* только для вложения* ) ] ; UPDATE peers SET zone= "voip" ; # обновить все строки в столбце zone таблицы peers UPDATE stat SET whp= "13x13x13" WHERE id = 1 ; UPDATE countries SET nm_ukr= ( SELECT del_countries. ukrainian FROM del_countries WHERE countries. nm_en= del_countries. english ) ;
WordPress использование, настройка : в таблице wp_posts удалить все вхождения строки
UPDATE wp_posts SET post_content = REPLACE (post_content, "" , "" ) ;DELETE FROM
