Язык управления данными
1. Языки БД. Язык управления данными
2. Выборка данных
Для отбора строк и столбцов таблиц базы данных используется инструкция SELECT.
Синтаксис:
SELECT [ALL|DISTINCT] набор_атрибутов
FROM набор_отношений
[WHERE условие_отбора_строк]
[GROUP BY спецификация_группировки]
[HAVING спецификация_выбора_групп]
Рекомендуемые материалы
[ORDER BY спецификация_сортировки]
Ключевое слово ALL означает, что в результирующий набор строк включаются все строки, удовлетворяющие условиям запроса, в том числе и строки-дубликаты. Ключевое слово DISTINCT означает, что в результирующий запрос включаются только различные строки.
В разделе SELECT атрибуты могут указываться с помощью (*). Например X.* обозначает совокупность всех атрибутов отношения Х, а изолированная * – совокупность всех атрибутов всех отношений, фигурирующих в разделе FROM для создания запроса.
Таблицам могут быть присвоены имена – псевдонимы, что бывает полезно при соединении таблицы с самой собою или для доступа из вложенного подзапроса к текущей записи внешнего запроса. Псевдонимы задаются с помощью ключевого слова AS, которое может быть опущено.
Раздел FROM определяет таблицы или запросы, служащие источником данных. В случае если указано более одного имени таблицы, по умолчанию предполагается, что над перечисленными таблицами будет выполнена операция декартова произведения. Например, запрос
SELECT *
FROM А, В
соответствует декартову произведению отношений А и В. Для задания типа соединения таблиц в единый набор записей, из которого будет выбираться необходимая информация, в разделе FROM используются ключевые слова JOIN и ON. Ключевое слово JOIN и его параметры указывают соединяемые таблицы и методы соединения. Ключевое слово ON указывает общие для таблиц поля.
При внутреннем соединении таблиц (INNER JOIN или JOIN) сравниваются значения общих полей этих таблиц. В окончательный набор возвращаются только те записи, которые отвечают условиям соединения. Операция LEFT JOIN возвращает все строки из первой таблицы, соединенные с теми строками второй, для которых выполняется условие соединения.
Если во второй таблице таких строк нет, возвращаются NULL значения в строках второй таблицы. Аналогично, операция RIGHT JOIN возвращает все строки второй таблицы, соединенные с теми строками первой, для которых выполняется условие объединения.
Операции LEFT JOIN или RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.
Раздел WHERE задает условия отбора строк. Имена атрибутов, входящие в предложение WHERE могут не входить в набор атрибутов, перечисленных в предложении SELECT.
В выражении условий раздела WHERE могут быть использованы следующие предикаты:
§ Предикаты сравнения {=, >, <, >=, <=, <>. }.
§ Предикат BETWEEN A AND B. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона.
§ Предикат вхождения во множество IN (множество) истинен тогда, когда сравниваемое значение входит во множество заданных значений. При этом множество может быть задано простым перечислением или встроенным подзапросом. Одновременно существует противоположный предикат NOT IN (множество).
§ Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение.
§ Предикат сравнения с неопределенным значением IS NULL. Неопределенное значение интерпретируется в реляционной модели как значение, неизвестное в данный момент времени. Это значение при появлении некоторой дополнительной информации в любой момент времени может быть заменено некоторым конкретным значением.
§ Предикаты существования EXISTS и не существования NOT EXISTS.
Когда запрос включает предложение WHERE, СУБД просматривает всю таблицу по одной записи, чтобы определить является ли предикат истинным. Предикат может включать неограниченное число условий, содержащих булевы операторы. Стандартными булевыми операторами в SQL являются AND, OR и NOT.
Например, создать запросы для вывода:
· перечня адресов трёхкомнатных квартир, предлагаемых для продажи в ’Витебске’ (примеры запросов относятся к базе данных, структура которой приведена на Ошибка! Источник ссылки не найден.);
SELECT Property_no, Street, House, Flat
FROM PROPERTY
WHERE City= ’Витебске’ AND Rooms=3;
· списка отделений компании, которые предлагают трехкомнатные квартиры c телефонами;
SELECT BRANCH.Branch_no
FROM BRANCH INNER JOIN PROPERTY ON BRANCH.Branch_no=PROPERTY.Branch_no
WHERE (PROPERTY.Rooms=3) AND (PROPERTY.Ptel=’T’);
· списка шифров владельцев собственности (Owner_no), предлагающих несколько трехкомнатных квартир для продажи;
SELECT DISTINCT a.Owner_no
FROM PROPERTY a, PROPERTY b
WHERE a.Owner_no=b.Owner_no AND
a.Property_no<>b.Property_no AND
a.Rooms=3 AND b.Rooms=3;
В запросе используются псевдонимы a и b таблицы PROPERTY, так как для выполнения запроса необходимо оценить равенство поля Owner_no в двух экземплярах одной и той же таблицы.
Раздел GROUP BY используется для создания итоговых запросов. Итоговые запросы имеют одно общее свойство: в предложении SELECT таких запросов используется, по крайней мере, одна агрегатная функция (AVG, COUNT (количество непустых значений в данном столбце), SUM, MIN, MAX, FIRST (значение столбца из первой строки результирующего набора записей), LAST(значение столбца из последней строки результирующего набора записей) и др.). Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля.
Синтаксис: GROUP BY < имя_столбца>
Имя столбца – имя любого столбца из любой из упомянутой в разделе FROM таблицы.
Если GROUP BY расположено после WHERE создаются группы из строк, выбранных после применения раздела WHERE.
При включении раздела GROUP BY в инструкцию SELECT список полей должен состоять из итоговых функций SQL (AVG,COUNT, MAX,MIN,SUM и др.) и из имен столбцов, указанных в разделе GROUP BY. В раздел GROUP BY должны быть включены все атрибуты, входящие в раздел SELECT.
В предложение GROUP BY могут быть указаны одновременно несколько столбцов. Группы при этом определяются слева направо. Предложение GROUP BY автоматически устанавливает сортировку по возрастанию (если надо по убыванию – задать в ORDER BY).
Создать итоговые запросы:
· Вычисления средней зарплаты сотрудников по каждому из отделений компании;
SELECT STAFF.Branch_no, Avg(STAFF.Salary) AS Средняя_зарплата
FROM STAFF
GROUP BY Branch_no;
Подсчёта количества трехкомнатных квартир, предлагаемых в Витебске и Полоцке.
SELECT City, COUNT(*) AS Количество_квартир
FROM PROPERTY
WHERE (Rooms=3) AND ((City=’Витебск’) OR (City=’Полоцк’))
GROUP BY City;
Раздел HAVING задает условие отбора групп строк, которые включаются в таблицу, определяемую инструкцией SELECT.
Условия отбора применяется к столбцам, указанным в разделе GROUP BY, к столбцам итоговых функций или к выражениям, содержащим итоговые функции. Если некоторая группа не удовлетворяет условию отбора, она не попадает в набор записей.
Синтаксис: HAVING < условие_отбора>
Разница между HAVING и WHERE заключается в том, что условие отбора, заданное в разделе WHERE применяется к отдельным записям, перед их группировкой, а условие отбора раздела HAVING применяется к группам строк.
Если раздел GROUP BY находится перед HAVING, условие отбора применяется к каждой из групп, сформированных на основе совпадения значений в заданных столбцах. В случае отсутствия раздела GROUP BY условие отбора применяется ко всей таблице определенной инструкцией SELECT. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении обработки сформированных групп HAVING.
Например, вывести список и номера телефонов отделений, которые предлагают более одной трехкомнатной квартиры.
SELECT PROPERTY.Branch_no, BRANCH. Btel_no
FROM BRANCH, PROPERTY
WHERE PROPERTY.Branch_no=BRANCH.Branch_no AND PROPERTY.Rooms=3
GROUP BY PROPERTY.Branch_no
HAVING COUNT(*)>1;
Сортировка результатов запроса
В SQL имеются специальные средства, которые позволяют совершенствовать вывод запросов:
· размещение текста в выводе запроса:
SELECT имя_поля1, ‘текст’, имя_поля2 …
При этом все символы, в том числе и пробелы, вставляются в вывод, поэтому этот способ можно использовать для маркировки вывода вместе со вставляемыми комментариями.
· упорядочение полей вывода:
ORDER BY имя_поля ASC|DESC;
Если указывается несколько полей, то столбцы вывода упорядочиваются один внутри другого, при этом можно определить ASC (возрастание) или DESC (убывание).
Например, определить количество объектов, находящихся в ведении каждого из сотрудников компании c упорядочением отделений по убыванию:
SELECT STAFF.Branch_no, STAFF.Staff_no, Count(*) AS Count_Staff_no
FROM STAFF INNER JOIN PROPERTY ON STAFF.Staff_no = PROPERTY.Staff_no
GROUP BY STAFF.Branch_no, STAFF.Staff_no
ORDER BY STAFF.Branch_no DESC, STAFF.Staff_no;
3. Вложение запросов
Одни запросы могут быть размещены внутри других запросов. Обычно подчиненный запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст.
Часто бывает необходимо сравнивать значения в определенных столбцах со списком значений этого же столбца из другой таблицы или запроса. В подобных случаях используется ключевое слово IN (NOT IN).
Например, вывести список сотрудников, за которыми не закреплен ни один из объектов недвижимости.
SELECT *
FROM STAFF
WHERE Staff_no NOT IN (SELECT Staff_no FROM PROPERTY);
В подзапросах допускается использование агрегатных функций, например, для вывода списка трехкомнатных квартир, цена которых превышает среднюю цену трехкомнатной квартиры, используется запрос:
SELECT City, Street, House,Flat
FROM PROPERTY
WHERE Rooms=3
AND Selling_Price >(SELECT AVG(Selling_Price) FROM Property
WHERE Rooms=3);
Вывести список владельцев собственности, чьи объекты были осмотрены в определенный день:
SELECT OWNER.Owner_no, FName, LName
FROM OWNER INNER JOIN PROPERTY
ON PROPERTY.Owner_no=OWNER.Owner_no
WHERE PROPERTY.Property_no=(SELECT Property_no
FROM VIEWING
WHERE Date_View=’18.01.03’);
В таблице VIEWING будет найдена соответствующая дата и передана в предложение WHERE. После определения даты в основном запросе из таблицы PROPERTY будут отобраны записи, удовлетворяющие заданному условию.
(В данном примере предполагается, что подзапрос должен вернуть только одно значение).
Если подчиненный запрос возвращает более одного значения, использование запроса в таком виде приведет к ошибке. В тех случаях, когда подчиненный запрос возвращает более одной строки необходимо использовать следующие ключевые слова: ANY, SOME, ALL. Подчиненный запрос в этом случае должен возвращать один столбец.
ANY или SOME – возвращает TRUE, если заданное выражение является истинным для какой-нибудь из строк возвращаемой запросом.
ALL - возвращает TRUE, если заданное выражение является истинным для всех строк возвращаемой запросом.
Выведите список объектов собственности, которые были осмотрены покупателями (присутствуют в таблице VIEWING):
SELECT *
FROM PROPERTY
WHERE Property_no =ANY (SELECT Property_no
FROM VIEWING);
Этот же результат может быть получен с помощью оператора IN
SELECT Property_no
FROM PROPERTY
WHERE Property_no IN (SELECT Property_no
FROM VIEWING);
Оператор ALL работает таким образом, что предикат является верным, если каждое значение, выбранное подзапросом, удовлетворяет условию в предикате внешнего запроса.
Найти всех сотрудников, чья заработная плата выше заработной платы любого из сотрудников отделения компании под номером 3:
SELECT Staff_no, FName, LName, Salary
FROM STAFF
WHERE Salary > ALL (SELECT Salary FROM STAFF
WHERE Branch_no=3);
Использование оператора EXISTS
Оператор EXISTS проверяет, возвращает ли подчиненный запрос хотя бы одну строку. Для проверки противоположного значения используется предикат NOT EXISTS.
Выведите данные об объектах собственности из таблицы PROPERTY только в том случае, если хотя бы один из них был осмотрен покупателями, и было получено согласие на приобретение:
SELECT Property_no
FROM PROPERTY
WHERE EXISTS (SELECT Property_no FROM VIEWING
WHERE Comments=’согласен’);
4. Создание таблицы из набора результатов
При помощи оператора SELECT INTO можно поместить набор результатов запроса в новую таблицу. Кроме того, этот оператор позволяет создавать и заполнять новые таблицы, а также создавать временные таблицы. Запросы к временной таблице иногда оказываются проще тех, которые пришлось бы выполнять, обращаясь к нескольким таблицам или базам данных. Оператор SELECT INTO позволяет создать локальную или глобальную временную таблицу. Для локальных таблиц используются имена, начинающиеся с символа #, а для глобальных – с символа ##.
Например, создать таблицу, содержащую объекты собственности, находящиеся в городе Полоцке.
SELECT *
INTO ##PROPERTY_POLOCK
FROM PROPERTY
WHERE City=’Полоцк’;
5. Использование оператора UNION
Оператор UNION позволяет выполнить два запроса независимо друг от друга и объединить их результаты. Для того чтобы объединение прошло успешно, выходные запросы должны быть совместимы по объединению, то есть иметь одинаковое количество отбираемых столбцов, типы соответствующих столбцов должны совпадать.
В выходном запросе отсутствуют дублирующие друг друга строки. Если надо оставить все строки в запросе, то после UNION следует указать ALL.
Получить данные о двухкомнатных квартирах в Витебске и Полоцке. Исходные данные находятся в таблицах PROPERTY_POLOCK PROPERTY_VITEBSK, имеющих одинаковую структуру.
SELECT *
FROM PROPERTY_POLOCK
WHERE Rooms=2
UNION
SELECT *
FROM PROPERTY_VITEBSK
WHERE Rooms=2;
Обычно оператор UNION используют для объединения данных двух независимых таблиц с одинаковой структурой.
6. Запросы на модификацию данных
SQL позволяет не только создавать запросы, но и вносить изменения в данные. Для этого используются запросы на удаление, вставку и обновление данных.
Запросы на удаление
Удаление строк из таблицы можно осуществить с помощью оператора DELETE. Следует учитывать, что оператор удаляет только целые записи таблицы, а не индивидуальные значения того или иного поля.
Синтаксис:
DELETE
FROM таблица
WHERE условие_отбора
Например, удалить из таблицы STAFF все записи, относящиеся к владельцу собственности, у которого значение поля STAFF_no=10:
DELETE
FROM STAFF
WHERE STAFF_no=10;
В команде удаления возможно использование вложенного запроса. Это может быть необходимо в тех случаях, когда критерий, по которому выбираются данные, базируется на другой таблице.
Запросы на добавление
Ввод и добавление записей в SQL осуществляется с помощью оператора INSERT. Существует несколько вариантов вставки данных.
Вставка записей из другой таблицы
Оператор INSERT добавляет записи в уже существующую таблицу, вставляя в нее набор результатов оператора SELECT
Синтаксис запроса:
INSERT [INTO] имя_таблицы
SELECT список_выборки
FROM список_таблиц
WHERE условие_поиска
Добавление данных в указанные поля
Наиболее употребительный вариант команды INSERT INTO предусматривает добавление записи в существующую таблицу с указанием списка полей:
Синтаксис запроса:
INSERT INTO имя_таблицы (поле1, поле2,…)
VALUES (значение_поля1, значение_поля2…)
При этом если перечислены не все поля, то в не перечисленные поля автоматически устанавливается значение NULL.
Если задается полный список значений новой записи, форма записи становится более короткой, так как перечень заполняемых полей после имени таблицы может не задаваться. Порядок следования значений после служебного слова VALUES должен соответствовать структуре таблицы.
Синтаксис запроса:
INSERT INTO имя_таблицы
VALUES (список_значений)
Например, отобрать из таблицы РROРERTY объекты собственности, находящиеся в Витебске, и поместить их в таблицу РROРERTY_VITEBSK. Таблица РROРERTY_VITEBSK должна быть заранее создана командой CREATE TABLE.
INSERT INTO РROРERTY_VITEBSK
SELECT *
FROM РROРERTY
WHERE City =’Витебск’
Добавить данные в таблицу VIEWING:
INSERT INTO VIEWING (Date_View, Comments, Property_no, Buyer_no)
VALUES(‘31.03.03’,’согласен’,3000,4)
В INSERT можно использовать подзапросы.
Например, вставить в таблицу РROРERTY_VITEBSK данные только о проданных объектах:
INSERT INTO РROРERTY_VITEBSK
SELECT *
FROM РROРERTY
WHERE City =’Витебск’ AND РROРERTY_No = ANY(SELECT РROРERTY _No FROM VIEWING
WHERE Comments =’согласен’);
Запросы на обновление
Запрос на обновление реализуется с помощью оператора UPDATE. Оператор служит для изменения значений полей на основе заданного условия отбора.
Синтаксис запроса:
UPDATE имя_таблицы
SET имя_поля=выражение
WHERE условие_отбора
Например, снизить цены на квартиры, в которых не установлены телефоны на 1%:
UPDATE PROPERTY
SET Selling_Price= Selling_Price*0.99
WHERE Ptel=’-’;
В команде UPDATE могут быть использованы подзапросы.
Например, снизить цену в 2 раза на те объекты собственности, у которых поле Comments таблицы VIEWING содержит значение ’требует ремонта’:
UPDATE PROPERTY
SET Selling_Price= Selling_Price/2;
WHERE Property_no= (SELECT Property_no
FROM VIEWING
WHERE Comments =’требует ремонта’);
7. КОНТРОЛЬНЫЕ ВОПРОСЫ
1. Какие предложения может включать в себя инструкция SELECT?
2. Каким образом выполняется соединение таблиц при создании запросов на выборку?
3. В чем заключаются отличия между разделами HAVING и WHERE?
4. Какое предложение инструкции SELECT используется для сортировки данных?
В лекции "6 Моделирование поверхностей" также много полезной информации.
5. Какие предложения инструкции SELECT могут включать подчиненные запросы?
6. Какие предикаты могут использоваться для проверки условий, построенных с помощью подчиненных запросов? В каких случаях может быть использован предикат «= » ?
7. Какой оператор служит для создания новой таблицы из набора результатов?
8. Какой оператор используется для объединения таблиц, имеющих одинаковую структуру?
9. Назовите все виды запросов на модификацию данных.
10. Какие варианты оператора INSERT вам известны?