Популярные услуги

Язык управления данными

2021-03-09СтудИзба

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 вам известны?

Свежие статьи
Популярно сейчас
А знаете ли Вы, что из года в год задания практически не меняются? Математика, преподаваемая в учебных заведениях, никак не менялась минимум 30 лет. Найдите нужный учебный материал на СтудИзбе!
Ответы на популярные вопросы
Да! Наши авторы собирают и выкладывают те работы, которые сдаются в Вашем учебном заведении ежегодно и уже проверены преподавателями.
Да! У нас любой человек может выложить любую учебную работу и зарабатывать на её продажах! Но каждый учебный материал публикуется только после тщательной проверки администрацией.
Вернём деньги! А если быть более точными, то автору даётся немного времени на исправление, а если не исправит или выйдет время, то вернём деньги в полном объёме!
Да! На равне с готовыми студенческими работами у нас продаются услуги. Цены на услуги видны сразу, то есть Вам нужно только указать параметры и сразу можно оплачивать.
Отзывы студентов
Ставлю 10/10
Все нравится, очень удобный сайт, помогает в учебе. Кроме этого, можно заработать самому, выставляя готовые учебные материалы на продажу здесь. Рейтинги и отзывы на преподавателей очень помогают сориентироваться в начале нового семестра. Спасибо за такую функцию. Ставлю максимальную оценку.
Лучшая платформа для успешной сдачи сессии
Познакомился со СтудИзбой благодаря своему другу, очень нравится интерфейс, количество доступных файлов, цена, в общем, все прекрасно. Даже сам продаю какие-то свои работы.
Студизба ван лав ❤
Очень офигенный сайт для студентов. Много полезных учебных материалов. Пользуюсь студизбой с октября 2021 года. Серьёзных нареканий нет. Хотелось бы, что бы ввели подписочную модель и сделали материалы дешевле 300 рублей в рамках подписки бесплатными.
Отличный сайт
Лично меня всё устраивает - и покупка, и продажа; и цены, и возможность предпросмотра куска файла, и обилие бесплатных файлов (в подборках по авторам, читай, ВУЗам и факультетам). Есть определённые баги, но всё решаемо, да и администраторы реагируют в течение суток.
Маленький отзыв о большом помощнике!
Студизба спасает в те моменты, когда сроки горят, а работ накопилось достаточно. Довольно удобный сайт с простой навигацией и огромным количеством материалов.
Студ. Изба как крупнейший сборник работ для студентов
Тут дофига бывает всего полезного. Печально, что бывают предметы по которым даже одного бесплатного решения нет, но это скорее вопрос к студентам. В остальном всё здорово.
Спасательный островок
Если уже не успеваешь разобраться или застрял на каком-то задание поможет тебе быстро и недорого решить твою проблему.
Всё и так отлично
Всё очень удобно. Особенно круто, что есть система бонусов и можно выводить остатки денег. Очень много качественных бесплатных файлов.
Отзыв о системе "Студизба"
Отличная платформа для распространения работ, востребованных студентами. Хорошо налаженная и качественная работа сайта, огромная база заданий и аудитория.
Отличный помощник
Отличный сайт с кучей полезных файлов, позволяющий найти много методичек / учебников / отзывов о вузах и преподователях.
Отлично помогает студентам в любой момент для решения трудных и незамедлительных задач
Хотелось бы больше конкретной информации о преподавателях. А так в принципе хороший сайт, всегда им пользуюсь и ни разу не было желания прекратить. Хороший сайт для помощи студентам, удобный и приятный интерфейс. Из недостатков можно выделить только отсутствия небольшого количества файлов.
Спасибо за шикарный сайт
Великолепный сайт на котором студент за не большие деньги может найти помощь с дз, проектами курсовыми, лабораторными, а также узнать отзывы на преподавателей и бесплатно скачать пособия.
Популярные преподаватели
Нашёл ошибку?
Или хочешь предложить что-то улучшить на этой странице? Напиши об этом и получи бонус!
Бонус рассчитывается индивидуально в каждом случае и может быть в виде баллов или бесплатной услуги от студизбы.
Предложить исправление
Добавляйте материалы
и зарабатывайте!
Продажи идут автоматически
5057
Авторов
на СтудИзбе
456
Средний доход
с одного платного файла
Обучение Подробнее