Лекция 4: SQL›екция_4.pdf · Курсовая работа 1)...

Лекция 4:Лекция 4:SQLSQL

Лектор — Николаев В.В.Лектор — Николаев В.В.2017 уч. год2017 уч. год

0. Введение0. Введение

Курсовая работаКурсовая работа

1) представление предметной области (4.03.2017)

2) ER-модель (13.04.2017)

3) Даталогическая модель (06.05.2017)

4) Итоговое построение БД (01.06.2017)

Модель:

● Типы данных

● Отображение MTM-связи

● Ключи

(!) SQL-код с ограничениями

Даталогическая модельДаталогическая модель

1. Язык SQL1. Язык SQL

● SQL (structured query language) — формальный непроцедурный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных.

● Основывается на исчислении кортежей.

● Разработан в 1970-е гг. в компании IBM.

● Первая СУБД, поддерживающая SQL — Oracle V2 (1979 г.).

О языкеО языке

Каждое предложение SQL — это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в базе.

Различают следующие типы запросов:

● запросы на создание или изменение в базе данных новых или существующих объектов;

● запросы на получение данных;

● запросы на добавление новых данных (записей);

● запросы на удаление данных;

● обращения к СУБД.

Особенности языкаОсобенности языка

Возвращает набор данных из БД, удовлетворяющих заданному условию.

Формат запроса:

SELECT список полей FROM список таблиц WHERE условия…

Оператор Оператор SELECTSELECT

Основные ключевые слова:

● WHERE — используется для определения, какие строки должны быть выбраны или включены в GROUP BY.

● GROUP BY — используется для объединения строк с общими значениями в элементы меньшего набора строк.

● HAVING — используется для определения, какие строки после GROUP BY должны быть выбраны.

● ORDER BY — используется для определения, какие столбцы используются для сортировки результирующего набора данных.

Оператор Оператор SELECTSELECT

● Позволяет группировать строки и использовать агрегатные функций (MAX, SUM, AVG, …).

● Необходимо, чтобы в SELECT были заданы только требуемые в выходном потоке столбцы, перечисленные в GROUP BY и/или агрегированные значения.

● Пример использования:

Запрос возвращает список людей с общей суммой их продаж:

SELECT ID, SUM(Value) FROM Sales GROUP BY ID;

ПараметрПараметр GROUP BY GROUP BY

ПараметрПараметр GROUP BY GROUP BY

SELECT ID, SUM(Value) FROM Sales GROUP BY ID;

ID Value

1 40

1 120

1 10

2 50

2 100

2 75

2 25

3 200

ID Value

1 170

2 250

3 200

Группа 1

Группа 2

Группа 3

● Позволяет указывать условия на результат агрегатных функций (MAX, SUM, AVG, …).

● Аналогичен WHERE за исключением того, что строки отбираются не по значениям столбцов, а строятся из значений столбцов, указанных в GROUP BY, и значений агрегатных функций, вычисленных для каждой группы, образованной GROUP BY.

● Необходимо, чтобы в SELECT были заданы только требуемые в выходном потоке столбцы, перечисленные в GROUP BY и/или агрегированные значения.

● Если параметр GROUP BY в SELECT не задан, HAVING применяется к «группе» всех строк таблицы.

ПараметрПараметр HAVING HAVING

ПараметрПараметр HAVING HAVING

● Пример использования:

Возвращает список идентификаторов отделов, продажи которых превысили 1500 долларов за 1 января 2017 года, вместе с суммами продаж за этот день:

SELECT DeptID, SUM(SaleAmount) FROM Sales WHERE SaleDate = '01-Jan-2017' GROUP BY DeptID HAVING SUM(SaleAmount) > 1500;

2. Функции и операторы2. Функции и операторы

● AND

● OR

● NOT

Логические операторыЛогические операторы

Три состояния:

true, false, NULL (неопределённое состояние)

Таблицы истинностиТаблицы истинности

AND и OR коммутативны: от перемены мест операндов результат не меняется

a b a AND b a OR b

TRUE TRUE TRUE TRUE

TRUE FALSE FALSE TRUE

TRUE NULL NULL TRUE

FALSE FALSE FALSE FALSE

FALSE NULL FALSE NULL

NULL NULL NULL NULL

a NOT a

TRUE FALSEFALSE TRUENULL NULL

Операторы сравненияОператоры сравнения

● Операторы бинарные

● Возвращают значения типа boolean

● Выдают NULL, когда любое из сравниваемых значений NULL

> Больше

< Меньше

>= Больше или равно

<= Меньше или равно

= Равно

<> или != Не равно

Предикаты сравненияПредикаты сравнения

a BETWEEN x AND y /* между */

a NOT BETWEEN x AND y /* не между */

a IS DISTINCT FROM b /* отличимо от */

a IS NOT DISTINCT FROM b /* неотличимо от */

выражение IS NULL /* эквивалентно NULL */

выражение IS NOT NULL /* не эквивалентно NULL */

a BETWEEN x AND y

?

a NOT BETWEEN x AND y

?

Предикаты через операторыПредикаты через операторы

a BETWEEN x AND y

a >= x AND a <= y

a NOT BETWEEN x AND y

a < x OR a > y

Предикаты через операторыПредикаты через операторы

Математические операторыМатематические операторы

Оп. Описание Пример

+ Сложение 5 + 7

- Вычитание 7 - 5

* Умножение 7 * 5

/ Деление (при целочисленном делении остаток отбрасывается)

8 / 4

% Остаток от деления 3 % 2

^ Возведение в степень 4.0 ^ 3.0

|/ Квадратный корень |/ 4

||/ Кубический корень ||/ 64

! Факториал 3 !

!! Факториал (префиксная форма) !! 3

Строковые функции и Строковые функции и операторыоператоры

string || string /* Конкатенация строк */

position(substring in string) /* Положение указанной подстроки */

substring(string from шаблон) /* Извлекает подстроку */

bit_length(string) /* Число бит в строке */

/* … */

Поиск по шаблонуПоиск по шаблону

● LIKE /* SQL */

● SIMILAR TO /* SQL:1999 */

● регулярные выражения в стиле POSIX

Поиск по шаблону: LIKEПоиск по шаблону: LIKE

● строка LIKE шаблон

● строка NOT LIKE шаблон

Выражение LIKE возвращает true, если строка соответствует заданному шаблону.

'abc' LIKE '_b_' /* true */

'abc' LIKE 'c' /* false */

ILIKE — регистро-независимый поиск

Поиск по шаблону: SIMILAR TOПоиск по шаблону: SIMILAR TO

● строка SIMILAR TO шаблон

● строка NOT SIMILAR TO шаблон

Выражение SIMILAR TO возвращает true, если строка соответствует заданному шаблону.

Поддерживает больше видов регулярных выражений, чем LIKE

Регулярные выражения POSIXРегулярные выражения POSIX

● ~ /* Проверяет соответствие регулярному выражению с учётом регистра */

● ~* /* Проверяет соответствие регулярному выражению без учёта регистра */

● !~ /* Проверяет несоответствие регулярному выражению с учётом регистра */

● !~* /* Проверяет несоответствие регулярному выражению без учёта регистра

*/


● Предоставляют более мощные средства поиска по шаблонам, чем операторы LIKE и SIMILAR TO

● Примеры:

'abc' ~ 'abc' /* ? */

'abc' ~ '(b|d)' /* ? */

'abc' ~ '^(b|c)' /* ? */


● Предоставляют более мощные средства поиска по шаблонам, чем операторы LIKE и SIMILAR TO

● Примеры:

'abc' ~ 'abc' /* true */

'abc' ~ '(b|d)' /* true */

'abc' ~ '^(b|c)' /* false */

if/then/else в других языках программирования:

CASE WHEN условие THEN результат

[WHEN ...]

[ELSE результат]

END

Условные выражения: СASEУсловные выражения: СASE

Условные выраженияУсловные выражения

1. CASE можно использовать везде, где допускаются выражения

2. Каждое условие представляет собой выражение, возвращающее boolean

3. Если не выполняются условия WHEN, значением CASE становится результат в ELSE

4. Если в 3. предложение ELSE отсутствует, результатом будет NULL

SELECT РЕЗ FROM ЭКЗАМЕН; РЕЗ--- 77 92 81SELECT РЕЗ, CASE WHEN РЕЗ>=91 THEN 'Отлично' WHEN РЕЗ>=75 THEN 'Хорошо' ... ELSE 'other' END FROM ЭКЗАМЕН;РЕЗ | case----+------- 77 | Хорошо 92 | Отлично 81 | Хорошо

Условные выраженияУсловные выражения

● CAST ( expression AS type ) /* SQL-синтаксис */

● expression::type /* PostgreSQL */

● typename ( expression ) /* через функцию */

Пример:

SELECT CAST(42 AS float8);

/* через функцию float8(int4) */

Преобразования типовПреобразования типов

CREATE CAST (source_type AS target_type) WITH FUNCTION function_name (argument_type [, ...])

● выражение IN (значение [, …])

Результат «true», если значение выражения равняется одному из значений выражений в правой части:

выражение = значение1

OR

выражение = значение2

OR ...

ININ

3. Запросы с использованием 3. Запросы с использованием нескольких таблицнескольких таблиц

Запросы с использованиемЗапросы с использованиемнескольких таблицнескольких таблиц

Вывести список всех студентов из России:

2 таблицы:

СТУДЕНТЫ(ИД, ФАМИЛИЯ, ГОРОД)

ГОРОДА(НАЗВ, СТРАНА)

Соединение с помощью фразыСоединение с помощью фразыWHEREWHERE

Вывести список всех студентов из России:

SELECT ФАМИЛИЯ, ГОРОД

FROM СТУДЕНТЫ, ГОРОДА

WHERE ГОРОДА.НАЗВ = СТУДЕНТЫ.ГОРОД AND ГОРОДА.СТРАНА = 'Россия';

Запросы с использованиемЗапросы с использованиемнескольких таблицнескольких таблиц

1.СУБД формирует строки декартова произведения таблиц, перечисленных во фразе FROM.

2.СУБД проверяет, удовлетворяют ли данные в сформированной строке условиям из фразы WHERE.

3.Если данные в строке удовлетворяют условию из фразы WHERE, то СУБД включает в ответ на запрос те поля строки, которые соответствуют столбцам, перечисленным во фразе SELECT.

Декартово произведение Декартово произведение таблицтаблиц

num | name-----+------ 1 | a 2 | b 3 | c

num | value-----+------- 1 | xxx 3 | yyy 5 | zzz

=> SELECT * FROM t1,t2; num | name | num | value-----+------+-----+------- 1 | a | 1 | xxx 1 | a | 3 | yyy 1 | a | 5 | zzz 2 | b | 1 | xxx 2 | b | 3 | yyy 2 | b | 5 | zzz 3 | c | 1 | xxx 3 | c | 3 | yyy 3 | c | 5 | zzz

t1: t2:

● Декартово произведение n таблиц — это таблица, содержащая все возможные строки s, такие, что s является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, ... и строки из n-й таблицы.

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

Декартово произведение Декартово произведение таблицтаблиц

5. Соединения (Joins)5. Соединения (Joins)

СоединенияСоединения

Начиная с SQL:1992 и для соединений таблиц принято использовать фразу JOIN.

● T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression

● T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )

● T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

Соединения (JOIN ON)Соединения (JOIN ON)

В предложении с ON указываются выражения логического типа. Пара строк из таблиц 1 и 2 соответствуют друг другу, если выражение ON возвращает для них true.

SELECT ФАМИЛИЯFROM СТУДЕНТЫJOIN ГОРОДА ON (ГОРОД = НАЗВ);

Позволяет сформировать эквисоединение таблиц

Соединения (JOIN USING)Соединения (JOIN USING)

● USING — сокращённая запись условия, когда с обеих сторон соединения столбцы имеют одинаковые имена. Например, запись соединения таблиц T1 и T2 с USING (at1, at2) формирует условие:

ON T1.at1 = T2.at1 AND T1.at2 = T2.at2

● При выводе JOIN USING исключаются избыточные столбцы.

SELECT ФАМИЛИЯFROM СТУДЕНТЫJOIN ОЛИМПИАДНИКИ USING (ИД);

Соединения (NATURAL JOIN)Соединения (NATURAL JOIN)

NATURAL — упрощённая форма USING: образует список USING из всех имён столбцов, существующих в обеих входных таблицах.

Если столбцов с одинаковыми именами нет, NATURAL работает как CROSS JOIN.

SELECT ФАМИЛИЯFROM СТУДЕНТЫNATURAL JOIN ОЛИМПИАДНИКИ;

● INNER JOIN

● LEFT OUTER JOIN

● RIGHT OUTER JOIN

● FULL OUTER JOIN

● CROSS JOIN

Виды соединенийВиды соединений

Для каждой строки из исходной таблицы T1 итоговая таблица включает строку для каждой строки из таблицы T2, если строка удовлетворяет условию соединения.

INNER JOININNER JOIN

INNER JOININNER JOIN

num | name-----+------ 1 | a 2 | b 3 | c


t1: t2:

=> SELECT * FROM t1 INNER JOIN t2 USING (num); num | name | value-----+------+------- 1 | a | xxx 3 | c | yyy

1.Сначала выполняется внутреннее соединение (INNER JOIN).

2.В результат добавляются все строки из таблицы T1, которым не соответствуют никакие строки из таблицы T2; вместо значений столбцов T2 вставляются NULL.

В результирующей таблице всегда будет минимум одна строка для каждой строки из T1.

LEFT OUTER JOINLEFT OUTER JOIN

LEFT OUTER JOINLEFT OUTER JOIN

num | name-----+------ 1 | a 2 | b 3 | c


t1: t2:

=> SELECT * FROM t1 LEFT JOIN t2 USING (num); num | name | value-----+------+------- 1 | a | xxx 2 | b | 3 | c | yyy

1.Сначала выполняется внутреннее соединение (INNER JOIN).


Это соединение является обратным к левому (LEFT JOIN): в результирующей таблице всегда будет минимум одна строка для каждой строки из таблицы T2.

RIGHT OUTER JOINRIGHT OUTER JOIN

RIGHT OUTER JOINRIGHT OUTER JOIN

num | name-----+------ 1 | a 2 | b 3 | c


t1: t2:

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num; num | name | num | value-----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy | | 5 | zzz

1.Сначала выполняется внутреннее соединение.



FULL OUTER JOINFULL OUTER JOIN

FULL OUTER JOINFULL OUTER JOIN

num | name-----+------ 1 | a 2 | b 3 | c


t1: t2:

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num; num | name | num | value-----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy | | 5 | zzz

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

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

CROSS JOINCROSS JOIN

CROSS JOINCROSS JOIN

num | name-----+------ 1 | a 2 | b 3 | c


=> SELECT * FROM t1 CROSS JOIN t2; num | name | num | value-----+------+-----+------- 1 | a | 1 | xxx 1 | a | 3 | yyy 1 | a | 5 | zzz 2 | b | 1 | xxx 2 | b | 3 | yyy 2 | b | 5 | zzz 3 | c | 1 | xxx 3 | c | 3 | yyy 3 | c | 5 | zzz

t1: t2:

5. Вложенные подзапросы5. Вложенные подзапросы

● Вложенный подзапрос (subquery) — предложение SELECT, которое заключено в круглые скобки и вложено в WHERE/HAVING часть другого SQL-предложения.

● Может содержать в своей WHERE (HAVING) - части другой вложенный подзапрос.

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

Вложенный подзапросВложенный подзапрос

● Простые вложенные подзапросы применяются для получения множества значений, которые проверяются в основном запросе:

SELECT ФАМИЛИЯ

FROM СТУДЕНТЫ

WHERE ГОРОД IN (

SELECT НАЗВ FROM ГОРОДА WHERE ГОРОДА.СТРАНА = 'Россия'

);

Простые вложенные подзапросыПростые вложенные подзапросы

● Обрабатываются "снизу вверх": первым обрабатывается вложенный подзапрос самого нижнего уровня.




SELECT НАЗВ FROM ГОРОДА WHERE ГОРОДА.СТРАНА = 'Россия'

);


Получаем список городов в России, используем их для

выполнения внешнего запроса

● Обрабатываются "снизу вверх": первым обрабатывается вложенный подзапрос самого нижнего уровня.




'Москва', 'Санкт-Петербург', ...

);


То же можно получить с помощью соединения:




SELECT НАЗВ FROM ГОРОДА

WHERE

ГОРОДА.СТРАНА = 'Россия'

);


SELECT ФАМИЛИЯFROM СТУДЕНТЫJOIN ГОРОДА ON (ГОРОД = НАЗВ) WHERE ГОРОДА.СТРАНА = 'Россия');

Вложенный подзапрос не может быть выполнен до обработки внешнего запроса:



WHERE EXISTS (

SELECT 1 FROM ОЛИМПИАДНИКИ WHERE ИД = СТУДЕНТЫ.ИД

);

Коррелированные вложенные Коррелированные вложенные подзапросыподзапросы

● Вложенный подзапрос зависит от значения СТУДЕНТЫ.ИД.

● Это значение изменяется по мере того, как СУБД проверяет строки таблицы СТУДЕНТЫ.



WHERE EXISTS (

SELECT 1 FROM ОЛИМПИАДНИКИ WHERE

ИД = СТУДЕНТЫ.ИД

);


1. СУБД проверяет первую строку таблицы СТУДЕНТЫ (ИД = 1). Значение СТУДЕНТЫ.ИД в этот момент = 1. СУБД обрабатывает внутренний запрос, получая в результате 1, если такой олимпиадник есть:



WHERE EXISTS (


ИД = СТУДЕНТЫ.ИД

);





WHERE EXISTS (


ИД = 1

);





WHERE EXISTS (

1

);


Включаем в результат

2. Система будет повторять обработку для каждой строки из таблицы СТУДЕНТЫ, пока не будут рассмотрены все строки таблицы СТУДЕНТЫ.


SELECT ФАМИЛИЯFROM СТУДЕНТЫWHERE EXISTS ( SELECT 1 FROM ОЛИМПИАДНИКИ WHERE ИД = 2);

● Обрабатываются СУБД в обратном порядке.

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

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


6. Операторы для работы со 6. Операторы для работы со вложенными подзапросамивложенными подзапросами

● выражение IN (подзапрос)

● Подзапрос должен возвращать ровно один столбец.

● Результат выражения сравнивается с каждым значением, возвращённым подзапросом.

● Результатом выражения IN будет «true», если значение выражения соответствует хотя бы одному значению, которое вернул подзапрос; «false» в противном случае (включая случай, когда подзапрос не возвращает строк).

ININ

● EXISTS (подзапрос)

● EXISTS принимает оператор SELECT (подзапрос)

● Если подзапрос возвращает хотя бы одну строку, то результатом EXISTS будет «true», а если не возвращает ни одной — «false».

EXISTSEXISTS

● Вывести фамилии студентов, попавших на комиссию?

СТУДЕНТЫ (ИД, ФАМИЛИЯ)

КУРСЫ (ИД, СТУД_ИД, РЕЗУЛЬТАТ)

EXISTSEXISTS

● EXISTS (подзапрос)



WHERE EXISTS (

SELECT 1 FROM КУРСЫ WHERE СТУДЕНТЫ.ИД = КУРСЫ.СТУД_ИД AND

РЕЗУЛЬТАТ < 60

);

EXISTSEXISTS

выражение оператор ANY (подзапрос)

выражение оператор SOME (подзапрос)

● Подзапрос должен возвращать ровно один столбец

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

● Результатом ANY будет «true», если хотя бы для одной строки условие истинно, и «false» в противном случае (в том числе, если подзапрос не возвращает строк).

ANY/SOMEANY/SOME

SELECT *

FROM Students

WHERE id = ANY

( SELECT stid FROM Exams );

ANY, примерANY, пример

выражение оператор ALL (подзапрос)

● Подзапрос должен возвращать ровно один столбец

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

● Результатом ALL будет «true», если условие истинно для всех строк (и когда подзапрос не возвращает строк), и «false», если находятся строки, для которых оно ложно.

● Результат будет NULL, если сравнение не возвращает false ни для одной из строк, но как минимум для одной результат сравнения NULL.

ALLALL

При подготовке презентации использовались материалы из:

● Введение в реляционные базы данных / В. В. Кириллов, Г. Ю. Громов, Издательство: BHV, 2009 г.

● Документация PostgreSQL: https://postgrespro.ru/docs/postgresql/9.4/index.html https://www.postgresql.org/docs/9.4/static/index.html

ЛитератураЛитература

Лицензия к документации PostgreSQL:Юридическое уведомлениеPostgreSQL © 1996-2015 — PostgreSQL Global Development Group.Postgres95 © 1994-5 — Регенты университета Калифорнии.

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

УНИВЕРСИТЕТ КАЛИФОРНИИ НИ В КОЕЙ МЕРЕ НЕ НЕСЁТ ОТВЕТСТВЕННОСТИ ЗА ПРЯМОЙ, КОСВЕННЫЙ, НАМЕРЕННЫЙ, СЛУЧАЙНЫЙ ИЛИ СПРОВОЦИРОВАННЫЙ УЩЕРБ, В ТОМ ЧИСЛЕ ПОТЕРЯННЫЕ ПРИБЫЛИ, СВЯЗАННЫЙ С ИСПОЛЬЗОВАНИЕМ ЭТОГО ПРОГРАММНОГО ПРОДУКТА И ЕГО ДОКУМЕНТАЦИИ, ДАЖЕ ЕСЛИ УНИВЕРСИТЕТ КАЛИФОРНИИ БЫЛ УВЕДОМЛЁН О ВОЗМОЖНОСТИ ТАКОГО УЩЕРБА.

УНИВЕРСИТЕТ КАЛИФОРНИИ ЯВНЫМ ОБРАЗОМ ОТКАЗЫВАЕТСЯ ОТ ЛЮБЫХ ГАРАНТИЙ, В ЧАСТНОСТИ ПОДРАЗУМЕВАЕМЫХ ГАРАНТИЙ КОММЕРЧЕСКОЙ ВЫГОДЫ ИЛИ ПРИГОДНОСТИ ДЛЯ КАКОЙ-ЛИБО ЦЕЛИ. НАСТОЯЩИЙ ПРОГРАММНЫЙ ПРОДУКТ ПРЕДОСТАВЛЯЕТСЯ В ВИДЕ "КАК ЕСТЬ", И УНИВЕРСИТЕТ КАЛИФОРНИИ НЕ ДАЁТ НИКАКИХ ОБЯЗАТЕЛЬСТВ ПО ЕГО ОБСЛУЖИВАНИЮ, ПОДДЕРЖКЕ, ОБНОВЛЕНИЮ, УЛУЧШЕНИЮ ЛИ МОДИФИКАЦИИ.

Лекция 4: SQL›екция_4.pdf · Курсовая работа 1)...

Documents

Transcript of Лекция 4: SQL›екция_4.pdf · Курсовая работа 1)...