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

Как думать на SQL?

Если вы похожи на меня, то согласитесь: SQL — это одна из тех штук, которые на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить каждый простой запрос, чтобы найти правильный синтаксис.

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

Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо.

Легко запомнить то, что интуитивно понятно, и с помощью этого руководства я надеюсь снизить порог входа в SQL для новичков, а уже опытным предложить по-новому взглянуть на SQL.

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

1. Три волшебных слова

2. Наша база

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

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

3. Простой запрос

Давайте начнем с простого запроса: нам нужны имена и идентификаторы (id) всех книг, написанных автором “Dan Brown”

Запрос будет таким:

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

3.1 FROM — откуда берем данные

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

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

3.2 WHERE — какие данные показываем

WHERE просто-напросто ведет себя как фильтр строк, которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author — это “Dan Brown”.

3.3 SELECT — как показываем данные

Весь запрос можно визуализировать с помощью простой диаграммы:

4. Соединения (джойны)

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

borrowings JOIN books ON borrowings.bookid=books.bookid — это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц «books» и «borrowings», в которых значения bookid совпадают. Результатом такого слияния будет:

А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3.

Давайте попробуем чуть более сложное соединение с двумя таблицами.

Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.

На этот раз давайте пойдем снизу вверх:

Шаг Step 1 — откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:

Результат соединения можно увидеть по ссылке.

Шаг 2 — какие данные показываем? Нас интересуют только те данные, где автор книги — “Dan Brown”

Шаг 3 — как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:

Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос:

Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим.

5. Агрегирование

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

Давайте продолжим наш пример, в котором появляются повторяющиеся имена. Видно, что Ellen Horton взяла больше одной книги, но это не самый лучший способ показать эту информацию. Можно сделать другой запрос:

Что даст нам нужный результат:

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

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

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

6. Подзапросы

Подзапросы это обычные SQL-запросы, встроенные в более крупные запросы. Они делятся на три вида по типу возвращаемого результата.

6.1 Двумерная таблица

Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов — использовать подзапросы:

6.2 Одномерный массив

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

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

1. Получаем список авторов с количеством книг больше 3. Дополняя наш прошлый пример:

Можно записать как: [‘Robin Sharma’, ‘Dan Brown’]

2. Теперь используем этот результат в новом запросе:

Это то же самое, что:

6.3 Отдельные значения

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

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

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

Теперь, наконец, можно написать весь запрос:

Это то же самое, что:

7. Операции записи

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

7.1 Update

Синтаксис запроса UPDATE семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT ‘ом, мы задаем знаения SET ‘ом.

Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:

7.2 Delete

7.3 Insert

8. Проверка

Вот он в более удобном для чтения виде:

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

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

Источник

SQL урок 3. Запросы sql INNER JOIN (объединение таблиц)

Выборка из нескольких таблиц (неявная операция соединения)

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

Рассмотрим пример неявной операции соединения:

SELECT DISTINCT pc.Номер, Производитель FROM pc, product WHERE pc.Номер = product.Номер AND Цена

SELECT DISTINCT A.Номер AS модель1, B.Номер AS модель2 FROM pc AS A, pc AS B WHERE A.Цена = B.Цена AND A.Номер A.Номер используется для того, чтобы не выводились одинаковые пары, отличающиеся только перестановкой номера:

Результат:

Запросы sql INNER JOIN

В предложении FROM может использоваться явная операция соединения двух и более таблиц.

Разберем пример. Имеем две таблицы: teachers (учителя) и lessons (уроки):

SELECT t.name,t.code,l.course FROM teachers t INNER JOIN lessons l ON t.id=l.tid

В запросе буквы l и t являются псевдонимами таблиц lessons (l) и teachers (t).

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

Запросы sql OUTER JOIN

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

Рисунок относится к объединению типа Left Outer Join:

SELECT t.name, t.code, l.course FROM teachers t LEFT OUTER JOIN lessons l ON t.id = l.tid

С тем же примером (выбрать имена учителей и курсы, которые они ведут) фильтрация по RIGHT OUTER JOIN вернет полный список уроков по курсам (правая таблица) и сопоставленных учителей. Но так как нет таких уроков, которые бы не соответствовали определенным учителям, то выборка будет состоять только из двух строк:

SELECT t.name, t.code, l.course FROM teachers t RIGHT OUTER JOIN lessons l ON t.id = l.tid

Выбрать фамилии студентов и их оценки по Word. В случае отсутствия оценки, все равно выводить фамилию.

В приведенных примерах можно вводить фильтры для более точной фильтрации:

SELECT t.name, t.code, l.course FROM teachers t LEFT OUTER JOIN lessons l ON t.id = l.tid WHERE l.tid IS NULL

Объединение с подзапросом

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

SELECT t1.*, t2.* from left_table t1 left join (select * from right_table where some_column = 1 limit 1) t2 ON t1.id = t2.join_id

SELECT t1.*, t2.* from left_table t1 inner join (select * from right_table where some_column = 1 limit 1) t2 ON t1.id = t2.join_id

SELECT t1.*, t2.* from teachers t1 inner join (select * from lessons where course = «php» limit 1) t2 ON t1.id = t2.tid

Разберем еще один пример:

Так как в таблице product находятся данные не только по компьютерам, то мы использовали подзапрос, при этом сохранив внутреннее соединение таблиц.
Результат:

Источник

Соединение таблиц или действие оператора SQL JOIN на примерах

Оператор JOIN используется для соединения двух или нескольких таблиц. Соединение таблиц может быть внутренним (INNER) или внешним (OUTER), причем внешнее соединение может быть левым (LEFT), правым (RIGHT) или полным (FULL). Далее на примере двух таблиц рассмотрим различные варианты их соединения.

Синтаксис соединения таблиц оператором JOIN имеет вид:

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

Допустим есть две таблицы (Auto слева и Selling справа), в каждой по четыре записи. Одна таблица содержит названия марок автомобилей (Auto), вторая количество проданных автомобилей (Selling):

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

1. Внутреннее соединение (INNER JOIN) означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Обычно используется для объединения записей, которые есть и в первой и во второй таблице, т. е. получения пересечения таблиц:

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

Ключевое слово INNER в запросе можно опустить.

В итоге запрос отбирает и соединяет те записи, у которых значение поля id в обоих таблицах совпадает.

2. Внешнее соединение (OUTER JOIN) бывает нескольких видов. Первым рассмотрим полное внешнее объединение (FULL OUTER JOIN), которое объединяет записи из обоих таблиц (если условие объединения равно true) и дополняет их всеми записями из обоих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL. Граф выборки записей будет иметь вид:

Переходим к запросу:

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

Ключевое слово OUTER можно опустить.

3. Левое внешнее объединение (LEFT OUTER JOIN). В этом случае получаем все записи удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней таблицы, которые не удовлетворяют условию объединения. Граф выборки:

Запрос также можно писать без ключевого слова OUTER.

В итоге здесь мы получили все записи таблицы Auto. Записи для которых были найдены совпадения по полю id в таблице Selling соединяются, для остальных недостающие поля заполняются значением NULL.

Еще существует правое внешнее объединение (RIGHT OUTER JOIN). Оно работает точно также как и левое объединение, только в качестве внешней таблицы будет использоваться правая (в нашем случае таблица Selling или таблица Б на графе).

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

4. Получить все записи из таблицы А, которые не имеют объединения из таблицы Б. Граф:

То есть в нашем случае, нам надо получить все автомобили из таблицы Auto, которые не имеют продаж в таблице Selling.

5. И последний вариант, получить все записи из таблицы А и Таблицы Б, которые не имеют объединений. Граф:

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

На этом все, до новых встреч на страницах блога.

Источник

Соединение таблиц во фразе WHERE по равенству значений столбцов различных таблиц

Соединение таблиц может быть указано во фразе WHERE или во фразе FROM. Сначала рассмотрим первый вариант. Большинство запросов, имеющих несколько таблиц во фразе FROM, содержат фразу WHERE, в которой указаны условия, попарно сравнивающие столбцы из различных таблиц. Такое условие называется условием соединения. В этом случае SQL предполагает сцепление только тех пар строк из разных таблиц, для которых условие соединения принимает истинное значение. Фраза WHERE помимо условия соединения может также содержать другие условия, каждое из которых ссылается на столбцы соединенной таблицы. Эти условия производят отбор строк соединенной таблицы.

Если таблицы соединяются по равенству значений пары столбцов (группы столбцов) из различных таблиц, такая операция называется соединением таблиц по равенству. Соединение по равенству позволяет соединить только те пары строк, которые действительно взаимосвязаны друг с другом. Так, например, мы можем соединить таблицы городов и клиентов по условию City.IdCity = Customer.IdCity. В таком варианте мы соединяем таблицы осмысленно, так как каждая строка таблицы Customer соединяется только с одной строкой соответствующего города. На базе таблиц City и Customer мы получаем таблицу со столбцами из обеих таблиц, имеющую строки с понятным смыслом. Можно также сказать, что в таблицу Customer вместо столбца IdCity мы вставляем все характеристики (столбцы) соответствующего города из таблицы City. Соединение таблиц используется, когда необходимо вывести значения столбцов:

· одной таблицы, но отвечающих условию, заданному на другой таблице.

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

SELECT FName, LName, CityName

FROM Customer, City

WHERE Customer.IdCity = City.IdCity

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

SELECT Customer.FName, Customer.LName, City.CityName

FROM Customer, City

WHERE Customer.IdCity = City.IdCity

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

SELECT k.FName, k.LName, c.CityName

FROM Customer k, City c

WHERE k.IdCity = c.IdCity

Следующий запрос отбирает всех клиентов из Казани с фамилией Иванов

SELECT K.IdCust, k.FName

FROM Customer k, City c

WHERE k.IdCity = c.IdCity AND k.LName = ‘Иванов’ AND c.CityName = ‘Казань’

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

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

Запрос: Список всех клиентов, которые когда-либо заказывали товар с кодом 1.

SELECT DISTINCT c.IdCust, c.FName, c.LName

FROM Customer c, [Order] o, OrdItem oi

WHERE c.IdCust = o.IdCust AND o.IdOrd = oi.IdOrd AND oi.IdProd = 1

Сформулируем общую процедуру составления многотабличного запроса.

1. Определить множество таблиц, необходимых для ответа на запрос. В это множество должны входить таблицы, на столбцах которых сформулированы условия, а также те, столбцы которых необходимо вывести. Это так называемые базовые таблицы запроса.

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

3. Во фразе FROM перечислить необходимые таблицы.

4. Во фразе WHERE соединить таблицы запроса и при необходимости задать условия отбора строк в базовых таблицах запроса.

5. Во фразе SELECT перечислить выводимые столбцы.

Задание для самостоятельной работы: Cформулируйте запрос, возвращающий список товаров в заданном заказе (по заданному IdOrd). Результат должен включать следующие поля: название товара, цена, количество, стоимость.

Соединения с использованием фразы FROM

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

таблица [INNER | [OUTER]] JOIN таблица

Внутреннее соединение

В операторе JOIN внутреннее соединение указывается ключевым словом INNER (впрочем, его можно опустить, так как соединение двух таблиц является внутренним по умолчанию). Условие соединения указывается после ключевого слова ON. В этом случае внутреннее соединение с помощью фразы FROM JOIN очень похоже на соединение с использованием фразы WHERE. Запишем первый пример с предыдущего раздела с использование оператора JOIN.

Запрос: Список всех клиентов с указанием названий городов, в которых они проживают

SELECT FName, LName, CityName

FROM Customer k JOIN

City c ON k.IdCity = c.IdCity

При соединении с использованием фразы FROM дополнительное условие можно для увеличения наглядности запроса помещать во фразу WHERE. В этом случае второй пример с предыдущего раздела примет такой вид.

Запрос: Список всех клиентов из Казани с фамилией Иванов

SELECT K.IdCust, k.FName

FROM Customer k INNER JOIN

City c ON k.IdCity = c.IdCity

WHERE k.LName = ‘Иванов’ AND c.CityName = ‘Казань’

Внешнее соединение

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

С помощью специальных ключевых слов LEFT OUTER, RIGHT OUTER и FULL OUTER, написанных перед JOIN, можно выполнить соответственно левое, правое и полное соединение. В SQL-выражении запроса таблица, указанная слева от оператора JOIN, называется левой, а указанная справа от него — правой.

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

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

Соответственно левое и правое внешние соединения различаются только порядком следования таблиц.

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

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

SELECT c.CityName, a.CountCity

FROM City c LEFT OUTER JOIN

(SELECT IdCity, COUNT(*) AS CountCity

GROUP BY IdCity) a ON c.IdCity = a.IdCity

ORDER BY c.CityName

Если в данном запросе заменить левое внешнее соединение на внутреннее, то из результата будут потеряны города, из которых нет ни одного клиента (проверьте это заменив LEFT OUTER JOIN на INNER JOIN и объясните причину разницы). Обратите внимание, что таблица City соединяется не с таблицей, а с подзапросом, которому задан псевдоним a.

Задание для самостоятельной работы: Сформулируйте на языке SQL запросы на выборку следующих данных (с использование оператора JOIN для соединения таблиц):

· список всех товаров, которые когда-либо заказывал заданный клиент;

· список всех клиентов, не имеющих ни одного заказа.

Множественные операции

Множественные операции выполняются над наборами записей (таблицами), полученными в результате запросов, и, в свою очередь, возвращают таблицу.

В стандарте SQL множественные операции имеют следующий синтаксис:

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

· UNION — все строки таблиц, возвращенных обоими запросами;

· INTERSECT — только те строки, которые имеются в таблицах обоих запросов;

· EXCEPT — только те строки таблицы первого запроса, которых нет среди строк таблицы второго запроса.

Запросы, содержащие множественные операторы, называются составными.

Стандарт SQL определяет следующие правила относительно повторяющихся строк в таблицах.

· базовые таблицы не могут содержать повторяющихся строк (это принципиальное требование реляционной модели данных);

· результирующие таблицы запросов могут содержать повторяющиеся строки, если это не запрещено ключевым словом DISTINCT во фразе SELECT;

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

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

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

· обе таблицы должны иметь одинаковое количество столбцов;

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

Объединение наборов записей

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

3anpoc1 UNION Запрос2;

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

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

Запрос: Объединение с исключением дублирующих строк

Запрос: Объединение с сохранением дубликатов

Пересечение наборов записей

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

Запрос1 INTERSECT Запрос2;

Разность наборов записей

Для получения записей, содержащихся в одном наборе и отсутствующих в другом, служит оператор EXCEPT(за исключением):

Запрос1 ЕХCЕРТ Запрос2;

Лабораторная работа №5: Основы Transact SQL: Добавление, изменение и удаление данных в таблицах

Запросы, рассмотренные ранее, были направлены на то, чтобы получить данные, содержащиеся в существующих таблицах базы данных. Главным ключевым словом таких запросов на выборку данных является SELECT. Запросы на выборку данных всегда возвращают виртуальную таблицу, которая отсутствует в базе данных и создается временно лишь для того, чтобы представить выбранные данные пользователю. При создании и дальнейшем сопровождении базы данных обычно возникает задача добавления новых и удаления ненужных записей, а также изменения содержимого ячеек таблицы. В SQL для этого предусмотрены операторы INSERT (вставить), DELETE (удалить) и UPDATE (изменить). Запросы, начинающиеся с этих ключевых слов, не возвращают данные в виде виртуальной таблицы, а изменяют содержимое уже существующих таблиц базы данных. Запросы на модификацию (добавление, удаление и изменение) данных могут содержать вложенные запросы на выборку данных из той же самой таблицы или из других таблиц, однако сами не могут быть вложены в другие запросы. Таким образом, операторы INSERT, DELETE и UPDATE в SQL-выражении могут находиться только в самом начале.

Добавление новых записей

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

· INSERT INTO имяТаблицы VALUES (списокЗначений)

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

· INSERT INTO имяТаблицы (списокСтолбцов) VALUES (списокЗначений)

вставляет запись в указанную таблицу и вводит в заданные столбцы значения из указанного списка. При этом в первый столбец из списокСтолбцов вводится первое значение из списокЗначений, во второй столбец — второе значение и т. д. Порядок имен столбцов в списке может отличаться от их порядка, заданного при создании таблицы. Столбцы, которые не указаны в списке, заполняются значением NULL. Рекомендуется использовать именно данную форму оператора INSERT. Следующий запрос добавляет новую запись в справочник городов.

INSERT INTO City(CityName)

Обратите внимание, что столбец IdCity не задается, поскольку он является счетчиком и заполняется СУБД автоматически.

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

INSERT INTO City(CityName)

Удаление записей

Для удаления записей из таблицы применяется оператор DELETE:

DELETE FROM имяТаблицы WHERE условие;

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

Следующий запрос удаляет записи из таблицы Customer, в которой значение столбца LName равно ‘Иванов’:

DELETE FROM Customer

WHERE LName = ‘Иванов’

Если таблица содержатся сведения о нескольких клиентах с фамилией Иванов, то все они будут удалены.

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

DELETE FROM Customer

WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName = ‘Москва’)

Transact-SQL расширяет стандартный SQL, позволяя использовать в инструкции DELETE еще одно предложение FROM. Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк. Оно позволяет задавать данные из второго FROM и удалять соответствующие строки из таблицы в первом предложении FROM. В частности предыдущий запрос может быть переписан следующим образом

DELETE FROM Customer

FROM Customer k INNER JOIN

City c ON k.IdCity = c.IdCity AND c.CityName = ‘Москва’

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

FROM Customer k INNER JOIN

City c ON k.IdCity = c.IdCity AND c.CityName = ‘Москва’

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

DELETE FROM Product

Задание для самостоятельной работы: Сформулируйте на языке SQL запрос на удаление всех заказов, не имеющих в составе ни одного товара (т. е. все пустые заказы).

Изменение данных

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

UPDATE имяТаблицы
SET имяСтолбца = значение
WHERE условие;

За ключевым словом SET (установить) следует выражение равенства, в левой части которого указывается имя столбца, а в правой — выражение, значение которого следует сделать значением данного столбца. Эти установки будут выполнены в тех записях, которые удовлетворяют условию в операторе WHERE.

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

Например, следующий запрос изменяет фамилию и имя клиента с кодом 5.

SET FName=’Иван’, LName=’Иванов’

Использование оператора WHERE в инструкции UPDATE не обязательно. Если он отсутствует, то указанные в SET изменения будут произведены для всех записей таблицы.

Так же как и в инструкции DELETE условие в операторе WHERE инструкции UPDATE может содержать подзапросы, в том числе и связанные.

Transact-SQL расширяет стандартный SQL, позволяя использовать в инструкции UPDATE предложение FROM (по аналогии с DELETE). Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания обновляемых строк.

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

Предположим, что требуется сделать 5% скидку по тем заказам клиентов, суммарная стоимость которых превышает 1000. Для этого следует изменить значения столбца Price, просто умножить их на 0,95. Однако эти изменения должны быть выполнены, только если суммарная стоимость заказа превышает 1000. Таким образом, в качестве критерия обновления записей в таблице OrdItem может быть задан запрос возвращающий список всех заказов с суммарной стоимостью более 1000.

SET Price = Price * 0.95

FROM OrdItem o INNER JOIN

HAVING SUM(Qty*Price) > 1000) a ON o.IdOrd = a.IdOrd

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

FROM OrdItem o INNER JOIN

HAVING SUM(Qty*Price) > 1000) a ON o.IdOrd = a.IdOrd

Задание для самостоятельной работы: Сформулируйте на языке SQL запрос имитирующий поступление на склад новой партий определенного товара (Обновление столбца InStock в таблице Product).

Источник