Базы данных Тема 3 1 Основы SQL Запросы

Зарегистрируйтесь, чтобы просмотреть полный документ!

Базы данных Тема 3. 1 Основы SQL. Запросы

Реляционная алгебра Основные операции Селекция R 2 = σр (R 1), R 2 и R 1 — таблицы, σ — операция селекции, р — условие отбора. Оклады2 = σОклад >16000 (Оклады)

Реляционная алгебра Основные операции Проекция R 2 = Пf 1, f 2, … , fn (R 1) f 1, f 2, . . . , fn— список извлекаемых из исходн. табл. столбцов. Оклады2 = ПФамилия, Имя, Отчество, Оклад (Оклады)

Реляционная алгебра Основные операции Декартово произведение Сотрудники Зарплата_Сотрудники = Сотрудники х Зарплата

Реляционная алгебра § Операции реляционной алгебры применимы к реляционным отношениям (таблицам). Результатом выполнения операции реляционной алгебры также является отношение, построенное на основе одной или нескольких исходных отношений. Существует пять основных и три вспомогательных операции реляционной алгебры, которые могут быть выражены через основные. § Селекция. (будем обозначать эту операцию знаком σр ). Это унарная операция (выполняемая над одним отношением), результатом которой является подмножество кортежей исходного отношения, соответствующих условиям, накладываемым на значения определенных атрибутов. Это одна из наиболее часто используемых операций в SQL. § Проекция (будем обозначать эту операцию знаком П). Это унарная операция для выбора подмножества атрибутов из исходного отношения. Она может уменьшить мощность результирующего отношения за счет исключения одинаковых кортежей. n Декартово произведение. Это бинарная операция над разносхемными отношениями, соответствующая определению декартова произведения для реляционной модели данных.

Реляционная алгебра Основные операции Операция объединения R = R 1 U R 2 Названия = ПНазвание книги (Книги) U ПНазвание книги (Книги 2)

Реляционная алгебра § § § Объединением двух односхемных отношений R 1 и R 2 называется отношение R, которое включает все кортежи обоих отношений без повторов. Замечание. Имена полей односхемных отношений могут быть разными, достаточно, чтобы совпадало количество полей и типы данных соответствующих полей. В качестве примера операции объединения рассмотрим две таблицы: Книги и Книги 2. Таблицы содержат названия книг некоторого автора, изданных в двух разных издательствах. Задачей объединения является получение сводного списка книг автора, изданных двумя издательствами. Внимательно рассмотрим две представленные таблицы. Отметим следующие особенности: Ø в таблицах имеются данные об одной и той же книге; Ø схемы таблиц не совпадают; Ø результат объединения должен содержать только список названий. Для получения нужной нам таблицы (названия) следует выполнить две проекции и одно объединение. Обратите внимание, что в результирующей таблице отсутствуют тождественные строки, как следовало из определения операции объединения.

Реляционная алгебра Основные операции Разность R = R 1 - R 2 Дети = ПФамилия, Имя, Отчество, Номер отдела (Дети 1) – ПФамилия, Имя, Отчество, Номер отдела (Дети 2)

Реляционная алгебра § Разностью односхемных отношений R 1 и R 2 называется множество кортежей R 1, не входящих в R 2. § Рассмотрим пример. Пусть таблица Дети 1 содержит список работников некоторого учреждения, имеющих детей. Таблица Дети 2 содержит список работников, которые имеют несовершеннолетних детей. Требуется получить список работников учреждения, которые имеют совершеннолетних детей. Таблицы имеют различную схему, так что в любом случае для получения результирующей таблицы Дети без проекции нам не обойтись. §

Реляционная алгебра Вспомогательные операции Пересечение и Соединение R = R 1 R 2 = R 1 – (R 1 – R 2) R 1 c R 2 = σc (R 1 x R 2), где с – условие соединения. Зарплата_Сотрудники = Сотрудники х Зарплата

Реляционная алгебра Вспомогательные операции Соединение R 1 c R 2 = σc (R 1 x R 2), где с – условие соединения. Зарплата_Сотрудники = σТабельный_номер_1=Табельный_номер_2 (Сотрудники х Зарплата)

Реляционная алгебра § Пересечением двух односхемных отношений R 1 и R 2 является кортежей, принадлежащих обоим отношениям. Это можно выразить через разность. § Соединение. Эта операция определяет подмножество декартова произведения двух разносхемных отношений. Кортеж декартова произведения в ходит в результирующее отношение, если для атрибутов разных исходных отношений выполняется некоторое условие соединения. Если условием является равенство значений двух атрибутов исходных отношений, такая операция называется эквисоединением. Естественным соединением называется эквисоединение по одинаковым атрибутам исходных отношений. § подмножество

Реляционная алгебра Вспомогательные операции Деление Звонки Телефоны Звонки / Телефоны

Реляционная алгебра § При помощи операции деления можно получить записи из первого отношения, которые совпадают (связаны) с записями из второго отношения. При этом производится дополнительная проекция, так что в результирующем отношении отсутствуют столбцы, которые имеются во втором отношении. § Для уяснения операции деления рассмотрим следующие таблицы. В таблице Звонки содержатся телефоны некоторого района, назовем его А, и телефоны, куда был сделан звонок с первых телефонов. В таблице Телефоны содержится список телефонов района В. Требуется получить список телефонов района А, с которых были сделаны звонки на телефоны района В. § Итак, нами были рассмотрены типичные операции реляционной алгебры. Все рассмотренные операции можно поделить на традиционные для множеств операции (объединение, пересечение, разность, декартово произведе ние) и специальные операции (выборка, проекция, соединение, деление). Интересно соотнести операции реляционной алгебры и типичные действия над таблицами, которые привыкли выполнять все, кто работал с реляционными базами данных: добавление новых строк в таблицу, удаление строк из таблицы, обновление строк в таблице. Добавление строк выполняется операцией объединения. Удаление строк выполняется операцией реляционного вычитания (разности). Операция обновления (изменение содержимого столбцов у группы строк) осуществляется посредством выполнения вначале операции реляционного вычитания, а затем операцией объединения. § § §

SQL Команда SELECT • Синтаксис команды SELECT: SELECT [DISTINCT | ALL] {* | [<выражение для столбца> [AS <псевдоним>]] [, …]} FROM <имя таблицы> [AS <псевдоним>]] [, …] [ WHERE <условие>] [ [ GROUP BY <список столбцов>] [ HAVING <условие на агрегатные значения>] ] [ ORDER BY <список столбцов>]

SQL n n n § Команда SELECT предназначена для того, чтобы извлекать данные из одной или нескольких таблиц реляционной базы данных. Эта команда осуществляет сразу несколько операций: Ø операцию проекции; Ø селекцию строк из таблицы; Ø различные виды соединения таблиц; Ø объединение нескольких выборок; Ø группировку выбранных строк; Ø групповые операции (агрегирование); Ø другие операции над набором строк. Кроме того, результат выполнения команды SELECT (т. е. набор строк) может быть использован в дальнейшем для операций модификации (INSERT, DELETE, UPDATE). Порядок выполнения команд в операторе SELECT не соответствует синтаксису их записи, который ближе к естественному языку. Порядок выполнения команд в операторе SELECT : n FROM n WHERE n GROUP BY n HAVING n SELECT n ORDER BY.

SQL Команда SELECT База данных «Экзамен»

SQL Команда SELECT • Пример 1. Самый простой запрос: SELECT * FROM Студенты • Пример 2. Запрос с указанием столбцов результирующей таблицы: SELECT ФИО, id_гpyппы FROM Студенты • Пример 3. Удаление дублирующих строк: SELECT DISTINCT ФИО FROM Студенты • Пример 4. Использование условия отбора: SELECT ФИО, id_гpyппы FROM Студенты WHERE id >10

SQL n n Раздел where команды select позволяет осуществлять отбор строк в результирующий набор. После выполнения запроса (пример 4) в результирующий набор строк попадут только те строки, для которых выполняется условие id > 10. Условие может состоять из нескольких простых условий, соединенных друг с другом логическими операциями and и or. В условиях можно использовать следующие логические операции сравнения: = (равно), > (больше), < (меньше), => (больше или равно), <= (меньше или равно), <> (не равно). При формулировке условия можно использовать скобки. Кроме этого, можно пользоваться операцией отрицания not: not ((id>10) and (id<20)), что эквивалентно id<=10 OR id>=20. Замечание. Когда мы говорили о null, то ввели понятие трехзначной логики — логики, в которой кроме значений true и false есть также значение unknown. Положения трехзначной логики полностью относятся к стандарту языка SQL Для того чтобы упредить необходимость использования трехзначной логики следует использовать операторы is null и case.

SQL Команда SELECT Операторы, используемые в разделе WHERE • Пример 5. Запрос с условием: SELECT * FROM Оценки WHERE Дата='1. 02. 2009' OR Дата='10. 02. 2009' OR Дата='13. 02. 2009' • Пример 6. Пример использования оператора IN : SELECT * FROM Оценки WHERE Дата IN ('1. 02. 2009', '10. 02. 2009', '13. 02. 2009') • Пример 7. Пример использования оператора BETWEEN: SELECT * FROM Оценки WHERE Оценка BETWEEN 3 AND 5 • Пример 8. Поиск строк по шаблону: SELECT ФИО FROM Оценки WHERE ФИО LIKE 'Р%ин _. _. '

SQL n n n Оператор IN позволяет ввести в условие понятие множества и тем самым сократить запись этого условия. В запросе (пример 5) должно проверяться довольно длинное условие. Используя оператор IN, можно значительно сократить запись условия (пример 6). Как видим из листинга (пример 6), множество задается путем перечисления его элементов через запятую. Оператор IN работает так, что операнд, стоящий слева от него, сравнивается по очереди со всеми элементами множества. Если хотя бы в одном случае условие равенства будет выполнено, то всему условию будет присвоено значение TRUE. Оператор BETWEEN ПОХОЖ на оператор IN, но здесь вместо множества фигурирует диапазон значений (пример 7). Оператор LIKE. Этот оператор используется для поиска подстроки в текстовых данных. Для поиска применяются специальные шаблоны. Для оператора LIKE выделены два типа шаблонов: Ø символ подчеркивания (_) обозначает любой отдельный символ; Ø символ % обозначает произвольное количество символов, включая и нулевые. В результате выполнения запроса (пример 8) будут выведены значения столбца ФИО. В частности, указанному шаблону будут удовлетворять значения столбца ФИО Родин А. А. И Рознин К. К.

SQL Команда SELECT Операторы, используемые в разделе WHERE • Пример 9. Использование оператора IS NULL : SELECT id, id_гpyппы, ФИО FROM Студенты WHERE ФИО IS NOT NULL • Пример 10. Пример с оператором CASE : SELECT id, id_гpyппы CASE WHEN ФИО IS NULL THEN 'Отсутствуют данные' ELSE ФИО END FROM Студенты

SQL Команда SELECT Имена столбцов набора и вычисляемые столбцы • Пример 11. Переименование столбцов: SELECT ФИО AS 'Фамилия', id_группы AS 'Идентификатор группы' FROM Студенты WHERE id >10 • Пример 12. Вычисляемые столбцы : SELECT 'Группа '+UPPER(Группа) AS 'Номер группы' FROM Группы

SQL n n Столбцам, указываемым в разделе SELECT, присваивается по умолчанию то имя, которое они имели в исходной таблице. Но имя это можно поменять с помощью ключевого слова AS. В результате выполнения запроса (пример 11) мы получим набор строк (таблицу), столбцы которого имеют имена Фамилия и Идентификатор группы. Кстати, ссылаться в дальнейшем на столбцы набора можно и с помощью исходных, и посредством новых имен. На столбцы, представленные в разделе SELECT, можно воздействовать с помощью встроенных функций, а также создавать новые (вычисляемые) столбцы (пример 12). В результате выполнения запроса мы получим список групп. Обратите внимание на встроенную функцию UPPER (), которая переводит все символы строки в верхний регистр.

SQL Команда SELECT Сортировка результата выполнения запроса • Пример 13. Сортировка результата запроса: SELECT * FROM Студенты WHERE ФИО IS NOT NULL ORDER BY ASC ФИО • Пример 14. Сортировка по двум столбцам: SELECT id_группы, ФИО FROM Студенты WHERE id>10 ORDER BY id_группы ASC, ФИО DESC

SQL n n n Результат запроса может быть упорядочен. Для этого существует специальный раздел команды SELECT — ORDER BY. В разделе ORDER BY предусмотрены две опции: ASC — упорядочение по возрастанию (опция действует по умолчанию), DESC — упорядочение по убыванию. В качестве параметров упорядочения можно указывать несколько столбцов через запятую. В этом случае упорядочение осуществляется вначале по первому в списке столбцу, затем в группе равных значений первого столбца упорядочение происходит по второму столбцу, далее в группе, где совпадают значения двух первых столбцов, строки упорядочиваются по третьему в списке столбцу и т. д. Стандарт SQL предусматривает возможность указания в разделе ORDER BY не имен столбцов, а их номеров по порядку из раздела SELECT. ЗАМЕЧАНИЕ. Возможность использования номеров вместо имен продиктовано тем фактом, что для некоторых столбцов могут быть не определены имена. Такие столбцы образуются при помощи выражений, встроенных функций, а также агрегирующих функций

SQL Команда SELECT Агрегирующие функции • Перечень агрегирующих функций по стандарту 1992 года

SQL n n Стандартом в языке SQL предусмотрены агрегирующие функции. Такие функции используются только в запросах и действуют на группу строк. В качестве аргумента такие функции принимают один из столбцов набора, полученного при помощи команды запроса SELECT, И возвращают статистическую итоговую величину. Обратите внимание на функцию COUNT(). Подсчет количества значений в каком либо столбце и количества строк— по сути одно и то же. Так что COUNT(имя_столбца) и COUNT (*) должны приводить к одному и тому же результату.

SQL Команда SELECT Агрегирующие функции • Пример 15. Вычисление среднего значения: SELECT AVG(CAST(Оценка AS FLOAT)) AS 'Средняя оценка', COUNT(*) AS 'Всего оценок' FROM Оценки • Результат выполнения запроса с агрегирующими функциями:

SQL n n Обратите также внимание, что в запросе (пример 15) используется встроенная функция преобразования типов CAST. Нам понадобилась эта функция для того, чтобы привести тип столбца Оценка к типу FLOAT, В противном случае средняя величина была бы выражена целым числом. Агрегирующие функции могут содержать также опции ALL И DISTINCT. ОПЦИЯ ALL действует по умолчанию и означает, что в расчете должны участвовать все значения из набора. Опция DISTINCT отбрасывает все дублирующие значения (в том числе и NULL). Например, запрос SELECT COUNT (DISTINCT оценка) FROM оценки возвратит значение 4 — четыре оценки: 5, 4, 3, 2 (хотя оценок 13).

SQL Команда SELECT Группировка строк запроса • Пример 16. Пример использования группировки : SELECT id_факультет AS 'Идентификатор факультета', COUNT(*) AS 'Количество групп' FROM Группы GROUP BY id_факультет • Результат выполнения запроса с группировкой:

SQL n n В результате запроса мы получаем набор строк, состоящий из одного или более столбцов. Значения, стоящие в том или ином столбце, могут повторяться. Повторяющиеся значения в каком либо столбце позволяют объединять строки с одинаковым значением столбца в группы. В команде SELECT имеется возможность обрабатывать такие группы. Для этого существуют разделы GROUP BY И HAVING. Обратимся к таблице Группы в базе данных «Экзамен» . Поставим задачу получить список идентификаторов факультетов и количество групп на них. Данная проблема легко решается с помощью предложения GROUP BY и агрегирующей функцией COUNT (ПРИМЕР 16). Наличие в запросе раздела GROUP BY приводит к тому, что агрегирующая функция воздействует па каждую группу в отдельности. Предложение GROUP BY может содержать не один, а целый список столбцов. Группировка таким образом осуществляется по группе столбцов (в группу входят строки с одинаковым значением всех столбцов списка). Но в этом случае список в разделе GROUP BY должен соответствовать списку столбцов в разделе SELECT. Исключение составляют только столбцы, формируемые при помощи агрегирующих функций.

SQL Команда SELECT Группировка строк запроса • Пример 17. Пример ошибочного запроса : SELECT id_факультет AS 'Идентификатор факультета', COUNT(*) AS 'Количество групп' FROM Группы WHERE COUNT(*)>20 GROUP BY id_факультет • Пример 18. Пример использования команды HAVING: SELECT id_ факультет AS 'Идентификатор факультета', COUNT(*) AS 'Количество групп' FROM Группы GROUP BY id_факультет HAVING COUNT(*)>20

SQL n n n Пусть требуется, чтобы в список (пример 16) попали только факультеты, количество групп в которых превышает 20. Напрашивающееся решение (пример 17) является ошибочным, поскольку раздел WHERE обрабатывается раньше, чем раздел GROUP BY. Правильным решением поставленной задачи будет запрос из примера 18. Раздел HAVING формируется по тем же принципам, что и раздел WHERE. Однако здесь имеется существенная особенность: раздел HAVING может ссылаться только на столбцы, значения которых одинаковы для группы (например, столбцы, перечисленные в разделе GROUP BY), либо на агрегирующие функции (как в нашем случае).

Контрольные вопросы 1. Основные операции реляционной алгебры: назначение и примеры. 2. Вспомогательные операции реляционной алгебры: назначение и примеры. 3. SQL: команда select: синтаксис и порядок выполнения. Примеры однотабличных запросов с использованием WHERE и IN, BETWEEN, LIKE, IS NULL и NOT в условии. 4. SQL: команда select: объясните назначение команд order by, group by и having. Примеры однотабличных запросов с использованием этих команд. 5. SQL: команда select: назначение агрегирующих функций. . Примеры однотабличных запросов с использованием COUNT, SUM, AVG, MAX, MIN.

Скачать презентацию Базы данных Тема 3 1 Основы SQL Запросы

3 1 Основы SQL. Запросы.ppt

Количество слайдов: 35