Зарезервированные слова DISTINCTROW и DISTINCT

SQL (ˈɛsˈkjuˈɛl; англ. structured query language - «язык структурированных запросов») - декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных.

Соответствие стандартам SQL разных БД:

SQL (Structured Query Language - язык структурированных запросов). SQL является, прежде всего, информационно-логическим языком, предназначенным для описания хранимых данных, для извлечения хранимых данных и для модификации данных.

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

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

Язык SQL представляет собой совокупность операторов. Операторы SQL делятся на:

операторы определения данных (Data Definition Language, DDL) - язык описания схемы в ANSI, состоит из команд, которые создают объекты (таблицы, индексы, просмотры, и так далее) в базе данных (CREATE, DROP, ALTER и др.).

операторы манипуляции данными (Data Manipulation Language, DML) - это набор команд, которые определяют, какие значения представлены в таблицах в любой момент времени (INSERT, DELETE, SELECT, UPDATE и др.).

операторы определения доступа к данным (Data Control Language, DCL) - состоит из средств, которые определяют, разрешить ли пользователю выполнять определенные действия или нет (GRANT/REVOKE , LOCK/UNLOCK).

операторы управления транзакциями (Transaction Control Language, TCL)

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

Простой JOIN (=пересечение JOIN =INNER JOIN) - означает показывать только общие записи обоих таблиц. Каким образом записи считаются общими определяется полями в join- выражении. Например следующая запись: FROM t1 JOIN t2 ON t1. id = t2. id

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

LEFT JOIN (или LEFT OUTER JOIN) означает показывать все записи из левой таблицы (той, которая идет первой в join- выражении) независимо от наличия соответствующих записей в правой таблице. Если записей нет в правой таблицы устанавливается пустое значение NULL.

RIGHT JOIN (или RIGHT OUTER JOIN) действует в противоположность LEFT JOIN - показывает все записи из правой (второй) таблицы и только совпавшие из левой (первой) таблицы.

Другие виды JOIN объединений: MINUS - вычитание; FULL JOIN - полное объединение; CROSS JOIN - “Каждый с каждым” или операция декартова произведения.

INSERT IGNORE Если в команде INSERT со строками, имеющими много значений, указывается ключевое слово IGNORE, то все строки, имеющие дублирующиеся ключи PRIMARY или UNIQUE в этой таблице, будут проигнорированы и не будут внесены. Если не указывать IGNORE, то данная операция вставки прекращается при обнаружении строки, имеющей дублирующееся значение существующего ключа

Команда REPLACE отличается от INSERT только тем, что если в таблице имеется запись с таким же значением в индексированном поле (unique index), как у новой записи, то старая запись удаляется перед добавлением новой.

WordPress использование, настройка : в таблице wp_posts удалить все вхождения строки

DELETE FROM

[ WHERE | WHERE CURRENT OF (*только для вложения*) ];

DELETE FROM Peers; // удалит все содержимое таблицы Peers. DELETE FROM FinR where day Like "20120415%"; // DELETE FROM prices WHERE ratesheet_id NOT IN (SELECT id FROM ratesheets);

Изменение значения по умолчанию для колонки . Чтобы установить новое значение по умолчанию для колонки, используйте команду вида: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77 ; OR ALTER TABLE nases ALTER COLUMN zone SET DEFAULT "voip" ;

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

ALTER TABLE products ALTER COLUMN price DROP DEFAULT ;

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

Функция как значение по умолчанию для колонки . В этом случае столбец timetracking имеет тип данных timestamp и значит для нее значением по умолчанию можно задать встроенную функцию now() т.е. при добавлении новой строки в столбец будет записана текущая дата и время ALTER TABLE timetracking ALTER COLUMN date_wd SET DEFAULT now();

Добавление ограничения . Чтобы добавить какое-либо ограничение, используется табличный синтаксис определения этого ограничения. Например: ALTER TABLE products ADD CHECK (name ""); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

Чтобы добавить ограничение не-null, которое нельзя записать как ограничение на таблицу, используйте синтаксис:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

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

В стандартном SQL существует 5 агрегатных функций:

COUNT - функция возвращает количество строк, которые соответствует определенным критериям.,

SUM - возвращает сумму (общую) значений в определённом столбце. Строки столбцов со значениями NULL игнорируются функцией SUM.

AVG - среднее значение в столбце,

Функции агрегирования используются как имена полей в предложении запроса SELECT, но с одним исключением: имена полей применяются как аргументы. Функции SUM и AVG могут работать только с цифровыми полями. Функции COUNT, MAX, MIN работают как с цифровыми так и с символьными полями. При применении к символьным полям функции MAX и MIN могут работают с ASCII эквивалентами символов.

SELECT Count(Books.ID) AS [Количество Книг] FROM Books;

Использование CROUP BY позволяет применять агрегатные функции к группам записей.

SELECT Count(Books.ID) AS [Количество Книг] FROM Books GROUP BY [Писатель];

Представление (VIEW) - объект данных который не содержит никаких данных его владельца. Это - тип таблицы, чье содержание выбирается из других таблиц с помощью выполнения запроса.

Базовые таблицы - это таблицы, которые содержат данные. Однако имеется другой вид таблиц: - представления (VIEW). Представления - это таблицы чье содержание выбирается или получается из других таблиц. Они работают в запросах и операторах DML точно также как и основные таблицы, но не содержат никаких собственных данных. Представления - подобны окнам, через которые вы просматриваете информацию, которая фактически хранится в базовой таблице.

Команда CREATE VIEW . Представление создается командой CREATE VIEW. Она состоит из слов CREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ), имени представления которое нужно создать, слова AS (КАК), и далее запроса. Создадим представление Londonstaff: CREATE VIEW Londonstaff AS SELECT * FROM Salespeople WHERE city = "London";

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

Зарезервированные слова DISTINCTROW и DISTINCT в инструкции SELECT Jet SQL позволяют исключить из результирующего множества повторяющиеся строки. Разница между этими словами состоит в том, как при их использовании определяются повторяющиеся записи.

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

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

Замечание

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

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

• Создайте новый запрос в базе данных "Борей". Для этого раскройте список запросов окна базы данных и нажмите кнопку Создать (New Query). В списке диалогового окна Новый запрос (New Query) выделите значение Конструктор (Design View) и нажмите кнопку ОК.
• Добавьте в запрос таблицы "Клиенты" (Customers), "Заказы" (Orders), "Заказано" (Orders Details) и "Товары" (Products). Access автоматически создает необходимые связи между таблицами.
• Перетащите поле "Название" (CompanyName) из списка полей таблицы "Клиенты" в первый столбец бланка запроса. Выделите ячейку Сортировка (Sort) и выберите значение По возрастанию (Ascending).
• Перетащите поле "Марка" (ProductName) из списка полей таблицы "Товары" (Products) во второй столбец бланка запроса. Задайте в этом столбце также сортировку по возрастанию.
• Нажмите на панели инструментов кнопку Запуск (Run) для выполнения запроса.

Вы должны получить результирующее множество, которое содержит 2169 записей. При этом те клиенты, которые не сделали ни одного заказа, в результирующее множество.не попадут, однако клиенты, которые заказывали один и тот же товар несколько раз, могут попасть в таблицу несколько раз. Теперь добавим в инструкцию SQI, SELECT слово DISTINCTROW для зтого:

• Выберите команду меню Вид, Режим SQL (View, SQL View) или щелкните по стрелке на кнопке Вид (View) на панели управления и выберите элемент Режим SQL (SQL View). Откроется диалоговое окно, в котором выведена инструкция SQL, соответствующая построенному запросу.
• Введите слово DISTINCTROW сразу после слова SELECT. Запрос должен выглядеть гак, как показано на рис. 8.58.
• Нажмите кнопку Запуск (Run). Результирующее множество запроса будет включать 1695 записей.
• Снова выполните команду Вид, Режим SQL (View, SQL Mode) и замените в инструкции SQL СЛОВО DISTINCTRTOW СЛОВОМ DISTINCT.
• Нажмите кнопку Запуск (Run). Результирующее множество запроса содержит те же 1695 строк, что и при использовании ключевого слова DISTINCTROW.

Рис. 8.58.

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

Вместо того чтобы вносить эти модификаторы вручную в режиме SQL, можно просто установить соответствующие свойства запроса. Чтобы открыть диалоговое окно свойств запроса, щелкните правой кнопкой мыши по свободному полю в верхней панели окна Конструктора запроса и выберите из контекстного меню команду Свойства (Properties). Появляется диалоговое окно, представленное на рис. 8.57.

Два свойства в этом окне определяют использование модификаторов DISTINCTROW и DISTINCT: Уникальные значения (Unique Values) и Уникальные записи (Unique Rows). Свойство Уникальные значения соответствует модификатору DISTINCT, a свойство Уникальные записи - модификатору DISTINCTROW. Если вы попробуете установить эти значения, то увидите, что Access не позволит установить значения Да (Yes) для обоих свойств. Если одно из них имеет значение Да (Yes), то для второго автоматически устанавливается значение Нет (No). Если оба свойства имеют значение Нет (No), запрос будет включать в результирующий набор все записи.

Замечание

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

(По материалам статьи Neil Boyle на swynk.com "Speed up SELECT DISTINCT queries")

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

select DISTINCT
au_fname,
au_lname
from authors

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

select DISTINCT
au_fname,
au_lname
from authors a join titleAuthor t
on t.au_id = a.au_id

Здесь нам нужно видеть только уникальные имена авторов, которые написали книги. Запрос будет работать, как требуется, но мы можем повысить его эффективность, если перепишем его так:

select au_fname, au_lname
from authors a
where exists (
select *
from titleAuthor t
where t.au_id = a.au_id
)

Причина более быстрой отработки запроса в том, что предложение EXISTS возвратит имя сразу же, когда найдена первая книга, и никакие другие книги этого автора далее не будут сканироваться (мы уже получили имя автора, и это всё, что нам нужно). С другой стороны, запрос DISTINCT возвращает одну копию имени автора для, каждой его книги и продолжил бы работу, пока не обработал бы весь список и не отсёк дубликаты согласно предложению DISTINCT. Вы можете исследовать план выполнения каждого из представленных запросов, чтобы увидеть причину повышения эффективности последнего примера.
Повышение эффективности зависит от соотношения количества попаданий строк в LEFT и RIGHT (или INNER и OUTER) таблиц. Следующий запрос будет работать в любой SQL Server базе данных. Пробуйте вставить эти два запроса в Query Analyser и сравнить их планы выполнения, а именно, как соотносятся I/O этой пары запросов для различных базах данных. Второй запрос обычно будет более эффективным, хотя фактическая эффективность может меняться.

select DISTINCT o.name
from sysobjects o
join sysindexes i
on o.id = i.id
where o.type = "U"

select o.name
from sysobjects o
where o.type = "U"
and exists (
select 1
from sysindexes i
where o.id = i.id
)

Вы должны понять особенности объединения двух (или больше) таблиц, чтобы использовать это для эффективного выполнения. Следующие два запроса для базы данных Northwind предназначены для возврата ID заказчика, у которого установлена скидка больше чем 2 процента для любой позиции. На первый взгляд они будут вести себя так же, как в предыдущем примере (форматы запросов схожи), но полученные фактические результаты окажутся другими.

select DISTINCT customerID
from orders o
join od
on o.OrderID = od.OrderID
where discount > 0.02

select customerID from orders o
where exists (
select *
from od
where o.OrderID = od.OrderID
and discount > 0.02
)

Разница эффективности выполнения этих запросов в том, что OrderID, который определяет зависимость между двумя таблицами, не является именем заказчика. Второй запрос возвратит множество имён заказчика - одно для каждой позиции, полученной заказчиком. Пробуйте добавить столбец OrderID в список SELECT, чтобы увидеть это.

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

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

Подготовка таблиц

Представим, что у нас в базе данных хранится информация об обоях, представленная в двух таблицах. Это таблица Oboi (обои) с полями id (уникальный идентификатор), type (тип обоев - бумажные, виниловые и др.), color (цвет), struct (структура) и price (цена). И таблица Ostatki (остатки) с полями id_oboi (ссылка на уникальный идентификатор в таблице Oboi) и count (количество рулонов на складе).

Заполним таблицы данными. В таблицу с обоями добавим 9 записей:

В таблицу с остатками - также девять записей:

Приступим к описанию порядка использования distinct в SQL.

Аргумент distinct следует помещать сразу после ключевого слова Select в запросах. Он применяется сразу ко всем столбцам, указанным в предложении Select, потому что будет исключать из итогового результата запроса абсолютно идентичные строки. Таким образом, достаточно один раз указать при написании запроса SQL «select distinct». Исключение составляет использование distinct внутри агрегатных функций, что рассмотрим чуть позднее.

Следует помнить, что большинство СУБД и не распознает ваш запрос вида:

Здесь несколько раз указан рассматриваемый аргумент либо указан один раз, но перед вторым, третьим или иным выбираемым столбцом. Вы получите ошибку со ссылкой на неточности в синтаксисе.

Очевидно, что при грамотном построении структуры таблиц и их заполнении, внутри одной таблицы исключены ситуации, когда встречаются абсолютно идентичные строки. Поэтому выполнение запроса «Select distinct *» с выборкой из одной таблицы практически нецелесообразно.

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

Как видим, в таблице присутствуют дублирующиеся строки. Если же мы добавим в предложение Select distinct:

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

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

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

то получим всего 119, ведь обои под артикулами 3 и 7 находятся на складе в одинаковом количестве. Однако очевидно, что этот ответ неверен.

Чаще всего в SQL distinct применяется с функцией Count. Так, без труда мы можем узнать, сколько уникальных видов обоев у нас вообще есть:

И получить результат 5 - бумажные обычные и двухслойные, виниловые, тканевые и флизелиновые. Наверняка все видели рекламу типа: «Только у нас более 20 видов различных обоев!», под которой подразумевается, что в данном магазине не пара десятков рулонов всего, а обои самых разнообразных современных типов.

Интересно, что в одном запросе можно указывать несколько функций Count как с атрибутом distinct, так и без него. То есть это единственная ситуация, когда distinct в Select"е может присутствовать несколько раз.

От применения аргумента SQL distinct следует отказаться в одном из двух случаев:

Допустим, начальник просит вас вывести список обоев, которые у вас есть, с указанием всего двух столбцов - тип и цвет. По привычке вы указываете аргумент distinct:

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

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

Противоположность аргументу distinct - аргумент All. При его применении повторяющиеся строки сохраняются. Но поскольку по умолчанию СУБД так и считает, что нужно выводить все значения, то аргумент All - это скорее уточнитель, чем реальный функциональный аргумент.

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

Основы запросов 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 такого не позволяет.