Базы данных SQL. Язык SQL

1. БАЗЫ ДАННЫХ SQL

2. ЯЗЫК SQL

SQL (Structured Query Language) –
структурированный язык запросов.
70- е годы фирма IBM и ORACLE.
Язык предназначен для реляционных СУБД.
SQL в различной модификации есть во всех РСУБД,
но есть различия в диалекте языка и версии языка.

3.

SQL –
запрос
Данные
РСУБД
БД

4. Функции SQL:

1. Определение данных
2. Выборка данных
3. Обработка данных (изменение данных)
4. Управление доступом
5. Совместное использование данных
6. Целостность данных

5.

SQL — это специализированный непроцедурный
язык
Существует 2 вида SQL:
интерактивный
встроенный
Интерактивный применяется для выполнения действий с
БД с целью получить результат. Команда — результат.
Встроенный состоит из команд SQL, включенных в
программы, написанные на других языках Си, PASCAL,
PHP.
Выходные данные команд SQL заносятся в переменные
программы, в которую включены команды SQL.

6. Преимущества SQL

1. Независимость от конкретных СУБД
2.
Межплатформенная переносимость
3.
Наличие стандартов
4.
Построение на реляционной модели
5.
Поддержка архитектуры клиент-сервер
6.
Возможность динамического определения
данных (возможность расширения во время
работы с БД)
7.
Расширяемость и поддержка объектноориентированных технологий (добавление в
XML объектных технологий)

7. Преимущества SQL

8. Возможность доступа к данным в Интернете ( связующее
звено между сервером приложений и сервером БД )
9. Интеграция с языком Java (протокол JDBC)
(использования языка для хранения процедур и бизнес
логики)
10. Поддержка открытого кода (MYSQL c открытым кодом
LAMP, OC Linux, Веб сервер Apache, язык сценариев PHP)
11. Поддержка со стороны MS ( открытый стандартный доступ
к РБД - ODBC программный интерфейс

8. Стандарты языка

SQL1
1986 год первый стандарт ANSI и
ISО (International Standards Organization )
1989 год добавление средств,
обеспечивающих ссылочную целостность

9. Стандарты языка

SQL 2
1992 год превышает по объему стандарт 89 в 5 раз
Добавлены:
Коды ошибок
Стандартные типы данных (символьные строки переменной
длины, дата время, денежные единицы
Программный интерфейс встроенного SQL для некоторых
языков программирования
Динамический SQL
1995 год SQL/CLI интерфейсы ODBC (Open Database
Connectivity) и JDBC (Java Database Connectivity).
1996 год SQL/PSM (Persistent Stored Modules) способы
определения и использования хранимых процедур.

10. Стандарты языка

SQL 3
1999 год
модульная структура
SQL/Foundation - основная часть совместимости,
остальные модули на рассмотрение
разработчиков СУБД
Стандарты 2003 (ХМL), 2006,2008, 2011, 2016,
2023 годы
2000 года NoSQL (данные в виде текстового документа, аудиофайла
или публикации в интернете )
2010 года NewSQL
с поддержкой SQL в разной степени

11.

12. ТИПЫ ДАННЫХ ЯЗЫКА SQL

Версия My SQL 5.1
5 типов данных
Строковые типы - для хранения текста и
бинарных данных
1.1- CHAR (n) [ BINARY]
Строка фиксированной длины.
1.
n байтов - max длина 255. (2000 – зависит от
реализации языка)
Если длина, строки меньше n, то оставшееся
пространство заполняется пробелами. Текст помещается
в одиночные кавычки.
Если параметр n опущен, он считается равным 1.
Могут возникнуть проблемы с организацией сравнения
содержащихся в соответствующих полях данных.
Двойная кавычка интерпретируется как кавычка в тексте.

13. СТРОКОВЫЕ ТИПЫ

VARCHAR (n)
[ BINARY]
строки переменной длины
n – мах длина строки до 65 535
занимает память необходимую для размещения
реального значения текстового поля.
(используется, когда текст изменяется в больших
пределах)
Если параметр n опущен, значение по умолчанию
определяется реализацией
ПРИМЕЧАНИЕ: Модификатор [ BINARY] позволяет
перевести текст в бинарный тип

14. СТРОКОВЫЕ ТИПЫ

1.2 TINYTEXT строка до 255 символов
TEXTстрока до 65 535 символов
MEDIUMTEXTстрока до 16 777 215 символов
LONGTEXTстрока до 4 294 967 295 символов
ПРИМЕЧАНИЕ: при поиске в текстовых полях регистр не
учитывается

15. СТРОКОВЫЕ ТИПЫ

1.3 Бинарные типы
Binary Large Object – большой двоичный формат
TINYBLOB – до 255 байтов
BLOB – до 65 535 байтов
MEDIUMBLOB – до 16 777 215 байтов
LONGBLOB до 4 294 967 295 байтов
Используется например для фотографий
ПРИМЕЧАНИЕ: при поиске в бинарных полях учитывается
регистр символов

16.

Тип данных СУБД
Oracle версии12
Описание
Примеры
Аналог в СУБД
PostgreSQL от 9 в
Char (размер)
Набор символов
постоянной длины (от 1
до 2000)
code char(3),
sex char
char
varchar2
(размер)
Набор символов
переменной длины (от
1 до 4000)
last_name
varchar2(64)
varchar
nchar
(размер)
Набор Unicode
символов постоянной
длины (от 1 до 2000
байт)
ucode nchar(3),
usex nchar
nvarchar2
(размер)
Набор Unicode
символов
переменной длины (от 1
до
4000 байт)
ulast_name
varchar2(64)
Clob
Набор символов
произвольной длины
(есть ограничения,
зависящие от
физической
организации БД)
my_string clob
clob

17. СТРОКОВЫЕ ТИПЫ

1.4 Перечисления и множества
SET (‘Значение1’, ‘Значение2’, ……) поле может
содержать несколько полей из перечисленных.
1,2,3,4 или 8 байтов
Может быть указано до 64 значений
ENUM (‘Значение1’, ‘Значение2’, ……) поле может
содержать лишь одно из перечисленных значений
или NULL.
1 или 2 байта
Может быть указано до 65 535 значений

18. ЧИСЛОВЫЕ ТИПЫ

2. Для хранения чисел
TINYINT [(длина в символах)]
целые числа от -128 до 127 или от 0 до 255 занимает 1 байт
SMALLINT [(длина в символах)] целые числа от
- 32 768 до 32 767 или от 0 до 65 535. Занимает 2 байта
MEDIUMINT [(длина в символах)] целые числа от
- 8 388 608 до 8 388 607 или от 0 до 16 777 215 Занимает 3 байта
INT [(длина в символах)] или INTEGER
целое 4 - байтное число -2 147 483 648 до 2 147 483 647
BIGINT [(длина в символах)]
Целое 8 - байтное число
-2 в степени 63 до 2 в степени 63 - 1

19. ЧИСЛОВЫЕ ТИПЫ

DECIMAL для дробных чисел, которые нельзя
округлять (денежный)
Начиная с версии 5.0.3. данные хранятся в двоичном
виде, в котором десять двоичных цифр
записываются в 4 байтах
NUMERIC дробное число в виде строки
Примечание: если после типа указано слово
UNSIGNED – то число без знака

20.

Тип данных
СУБД Oracle
версии12
Описание
Примеры
Аналог в СУБД
PostgreSQL от 9 в
number(p, s)
или
numeric(p, s)
или
decimal(p, s)
Число, точностью в p
цифр (от 1 до 38) и
масштабом в s цифр
(от -84 до 127).
Занимает от 1 до 22
байт.
Точность – число
значащих цифр;
масштаб – число
цифр после
десятичной
запятой (точки)
f2 number(5, 10),
f3 number(2),
f4 number
decimal(p, s)

21. ЧИСЛОВЫЕ ТИПЫ

FLOAT [(длина в символах), (количество знаков после
запятой]
вещественные числа от +-1.175494351Е-38
до +-3.402823466Е+38.
Занимает 4 байта
DOUBLE [(длина в символах), (количество знаков после
запятой] или REAL
вещественные числа двойной точности. Занимает 8 байт
Округляются до указанной точности

22. ЧИСЛОВЫЕ ТИПЫ

BOOL или BOOLEAN – либо 0, либо 1
получаются в результате операций сравнения
True 1
False 0
Unknown
NULL
получается при сравнении с

23. 4. ДАТА И ВРЕМЯ

Календарные типы
DATА – дата в формате ‘гггг-мм-чч’ 2007-05-30
TIME - время в формате ‘чч:мм:сс ‘
могут иметь дробную часть 12:10:55.56
DATАTIME – дата и время в формате
‘гггг-мм-дд чч:мм:сс’
YEAR[(2I4)] год в двух или четырех символьном
формате
TIMESTAMP [(тип)] дата и время 10 позиций дата
пробел 8 позиций время, и м.б. 1 знак на
миллисекунды 19-20 знаков
От ‘1970-01-01 00:00:00’ - начало эпохи UNIX
до ‘2037-12-31 23:59:59’

24. Константы

25.

Правила задания имен:
• может содержать только символы, являющиеся
заглавными или строчными буквами
английского алфавита (A-Z и a-z), символы,
являющиеся цифрами (0-9) или следующие
специальные символы: «_», «#», «$»;
• первый символ имени объекта должен быть
буквой;
• длина имени объекта не должна превышать 30
символов;
• имя объекта не может совпадать с именами
других объектов принадлежащих тому же
пользователю;
• имя объекта не может совпадать с ключевыми
• словами.

26.

Составляющие языка SQL
Название
Назначение
Примеры
команд
DDL
(Data
Definition
Language)
Определение данных; т.е.
команды создания / удаления /
модификации объектов
CREATE
TABLE;
DROP TABLE;
ALTER TABLE;
RENAME
TABLE
DML
(Data
Manipulation
Language)
Манипуляция с данными;
т.е. команды, отвечающие за
наполнение объектов или выборку
из них
(например, добавление, удаление,
выборка или модификация строк
таблиц)
SELECT;
INSERT;
UPDATE;
DELETE

27.

Название
Назначение
Примеры
команд
DCL
(Data Control
Language)
разграничение прав доступа
GRANT;
REVOKE
TCL
(Transaction
Control
Language)
управление транзакциями
(например, применение и отмена
изменений, вызванных блоком
последовательно выполненных
команд)
SET
TRANSACTION;
COMMIT;
ROLLBACK;
SAVEPOINT

28. ТЕРМИНОЛОГИЯ SQL

Ключевые слова
Имеют специальное значение в языке и выделяются
заглавными буквами.
Команды
Инструкции, которые даются БД, состоят из предложений и
аргументов.
Предложение
Начинается с ключевого слова, по которому оно и называется.
Квадратные скобки
Выделяют необязательные части предложений,
I - или

29.

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

30.

NULL значения - обозначение того, что значение поля
неизвестно.
NOT NULL не может быть неизвестным.
Для первичных ключей недопустимы NULL значения.
Ключевое слово UNIQUE.
Для ограничения UNIQUE допустимы NULL значения.
Первичный ключ – это комбинация конструкций NOT
NULL и UNIQUE

31. Стандартные встроенные функции SQL

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

32.

В стандарте ANSI/ISO определено автоматическое
преобразование целых чисел в числа с
плавающей точкой (В одном выражении можно
использовать числовые данные разных типов)
Стандарт SQL определяет оператор конкатенации
строк в виде двух вертикальных черточек (II), и
используется в большинстве СУРБД.

33.

Встроенную функцию можно использовать в любом
месте инструкции SQL , в котором можно
использовать константу того же типа

34. Создание БД

CREATE DATABASE имя;
Получив команду сервер выдает:
(при успехе ) Query OK
Для использования БД команда
mysgl>USE имя БД;

35. Создание таблицы

Создание таблицы
- пустая таблица, не имеющая строк
CREATE TABLE имя;
Пример:
CREATE TABLE zakaz
(PRIMERY KEY (kod_pr integer),
pr_name char(50) NOT NULL,
firma char(15) NOT NULL,
stag integer);

36.

Ограничения
Тип
данных
Описание
NOT
NULL
Запрет на запись пустого значения (NULL).
UNIQUE
Запрет на запись повторяющихся значений.
PRIMARY
KEY
Задание первичного ключа таблицы.
CHECK
Запрет на добавление значений, не удовлетворяющих
ограничения целостности данных
определённому условию. Условие задаётся в виде функции
над множеством значений полей строки таблицы,
действующей на пространство {true|false}. Значение
выражения должно быть истина или ложь.
FOREIGN
KEY
Внешний ключ, контролирующий, чтобы значение данного
поля (комбинации полей) строки всегда соответствовало
одному из значений ключа другой таблицы.

37.

CHECK можно использовать для задания
набора допустимых для столбца значений.
Это ограничение состоит из ключевого слова
CHECK и заключенного в скобки предиката,
проверяющего столбец. Попытка занести в
такой столбец значение, которое делает
предикат ложным, будет отклонена.
DEFAULT – выражение, используемое в
столбце по умолчанию. С помощью этого
средства пользователь может указать
требуемые для работы ограничения или
условия заполнения таблиц.

38. Создать таблицу СОТРУДНИК с полями: код сотрудника (первичный ключ), ФИО, должность, стаж, дата рождения, зарплата, пол:

CREATE TABLE Worker
(IdWorker int NOT NULL PRIMARY KEY,
Name varchar(30) NOT NULL,
Position varchar(30) NOT NULL,
BirthDate smalldatetime NULL,
Salary int NOT NULL
Gender char(1) CHECK (Gender IN ('м', 'ж'));

39. Задание ограничения целостности в полной форме

«CONSTRAINT [FK_Заказы_Продавцы] FOREIGN KEY ([Код
продавца]) REFERENCES [Продавцы]([Код продавца]),»
, где: ([Код продавца]) – столбец «Код продавца» в таблице
«Заказы»
[Продавцы] – название таблицы «Продавцы»
Код
продавца
–
столбец
«Код
продавца»
«Продавцы»
CONSTRAINT - ограничения целостности данных
REFERENCES - ссылка
в
таблице

40.

CREATE TABLE people(
last_name varchar(64),
first_name varchar(64),
second_name varchar(64),
sex char NOT NULL CHECK(sex = 'f' OR sex = 'm'),
birthday date,
citizenship varchar2(3),
PRIMARY KEY(last_name, first_name, birthday),
CONSTRAINT code_fk FOREIGN KEY(citizenship)
REFERENCES countries(code) );
Ограничение целостности FOREIGN KEY, заданное в полной
форме

41.

CREATE TABLE people(
last_name varchar(64) NOT NULL,
first_name varchar(64),
second_name varchar(64),
sex char NOT NULL CHECK(sex = ‘м' OR sex = ‘ж'),
birthday date NOT NULL,
UNIQUE(last_name, first_name, second_name) );
Или PRIMARY KEY(last_name, first_name,
birthday)
Ограничение на отсутствие полных
однофамильцев на пол

42. Ограничение целостности FOREIGN KEY, заданное с помощью конструкции REFERENCES

CREATE TABLE people(
last_name varchar(64),
first_name varchar(64),
second_name varchar(64),
sex char NOT NULL CHECK(sex = 'f' OR sex = 'm'),
birthday date,
PRIMARY KEY(last_name, first_name, birthday),
citizenship varchar(3) REFERENCES
countries(code) );

43.

CREATE TABLE groups(
name varchar(16) NOT NULL,
study_year varchar(16) NOT NULL,
speciality varchar(8) NOT NULL,
PRIMARY KEY(name, study_year) );
CREATE TABLE people(
last_name varchar(64),
first_name varchar(64),
second_name varchar(64),
sex char NOT NULL CHECK(sex = 'f' OR sex = 'm'),
birthday date,
citizenship varchar(3) DEFAULT 'RUS' REFERENCES countries(code),
group_name varchar(16) NOT NULL,
study_year varchar(16) NOT NULL,
PRIMARY KEY(last_name, first_name, birthday),
FOREIGN KEY(group_name, study_year)
REFERENCES groups(name, study_year) );

44. ПРОСМОТР

Посмотреть список таблиц
mysgl>SHOW TABLES
Посмотреть структуру таблицы –
mysgl>DESCRIBE имя таблицы;

45. ВСТАВКА СТРОКИ

Строки таблицы вводятся с помощью команды
INSERT INTO имя таблицы ( поле1, поле2…) VALUES
(значение1, значение2,..);
Пример:
INSERT INTO Клиенты
сумма_заказа)
(имя, телефон,
VALUES (‘Иванов’,555-5555,300)

46. Удаление строк таблицы

DELETE FROM < имя таблицы> WHERE <условие> [ LIMIT < число
записей>] – удаление записей из таблицы.
Если условие не указано будет удалены все записи таблицы
Пример: DELETE FROM klient;
Примечание: счетчик не сбрасывается
Пример: DELETE FROM klient WHERE fio LIKE ‘A%’;
TRUNCATE < имя таблицы> ; Очистка таблицы и счетчика

47. Удаление таблицы и БД

DROP TABLE < имя таблицы> ; Удаление таблицы.
DROP DATABASE < имя БД> ; Удаление БД

48. Изменение таблицы

ALTER TABLE < имя таблицы>
< преобразование> ;
Параметр преобразование:
RENAME < новое имя таблицы> ;
ADD < имя нового поля> < тип > [FIRST I AFTER < имя поля> ]
По умолчанию в конец строки
ADD PRIMARY KEY < имя поля>
DROP PRIMARY KEY

49. Изменение структуры таблицы

CHАNGE < имя поля> < новое имя поля>
< новые параметры поля> - изменяет имя и
свойства столбца
MODIFY < имя поля> < тип данных>
Изменяет свойства столбца
DROP < имя поля> удаляет поле

50. Инструкции управления доступом

51.

52. ЯЗ(DQL)-язык запросов

Команда SELECT заменяет все
операторы РА .
Формирует временную таблицу
согласно запросу.

53. ЗАПРОСЫ

Запрос –это команда, направленная СУБД, которая
требует предоставить указанную информацию.
Полученную информацию (временная таблица)
можно посмотреть на экране, направить на печать,
сохранить в файле, использовать в качестве
исходной для других команд (подзапрос).
Запросы не изменяют исходных данных в таблицах.
(изменения – запросы действия)
Все запросы в SQL осуществляет команда SELECT

54. СИНТАКСИС КОМАНДЫ SELECT

SELECT
* I [ALL I DISTINCT] <СПИСОК ПОЛЕЙ,..>
FROM <ИМЯ ТАБЛИЦЫ…,.>[АЛИАС]
[WHERE <ПРЕДИКАТ>]
[GROUP BY <ПОЛЕ РЕЗУЛЬТАТА,..>]
[HAVING<ПРЕДИКАТ ДЛЯ ГРУППЫ,…>]
[ORDER BY<СПИСОК ПОЛЕЙ, ПО КОТОРЫМ ПРОИЗВОДИТСЯ
СОРТИРОВКА, …>];

55. СИНТАКСИС КОМАНДЫ SELECT

ALL – ВЫБИРАЮТСЯ ВСЕ СТРОКИ РЕЗУЛЬТАТА
DISTINCT – СТРОКИ БЕЗ ДУБЛЕЙ
*- В РЕЗУЛЬТАТЕ БУДУТ ВСЕ СТРОКИ ИСХОДНОЙ
ТАБЛИЦЫ
FROM – ИМЕНА ИСХОДНЫХ ТАБЛИЦ
АЛИАС – ВРЕМЕННЫЙ СИНОНИМ ИМЕНИ
ТАБЛИЦЫ, ОПРЕДЕЛЕННЫЙ ТОЛЬКО В ЭТОЙ
КОМАНДЕ
GROUP BY –ПОЛЯ ГРУППИРОВКИ
HAVING – УСЛОВИЯ НА КАЖДУЮ ГРУППИРОВКУ
ORDER BY – СОРТИРОВКА ПОЛЕЙ РЕЗУЛЬТАТА
ASC, DЕSC

56.

57. ПОКУПАТЕЛИ

58.

59. пример 1

Отображение всей таблицы
SELECT *
FROM Продавцы;
КОД_ПР
ПР_ИМЯ
ФИРМА
СТАЖ
101
ФЕДОРОВ
ЛИРА
12
102
СЕРГЕЕВ
СВИЯГА
13
104
МИХАЙЛОВ
ЛИРА
11
107
РОМАНОВ
БЕРМУДЫ
15
103
АЛЕКСЕЕВ
НИКА
10

60. Пример 2

Отображение определенных столбцов таблицы
SELECT Пр_Имя, Стаж
FROM Продавцы;
ПР_ИМЯ
СТАЖ
ФЕДОРОВ
12
СЕРГЕЕВ
13
МИХАЙЛОВ
11
РОМАНОВ
15
АЛЕКСЕЕВ
10

61. Пример 3

Перестановка столбцов
SELECT Дата, Код_пр, Код_пк, Стоимость
FROM Заказы;
ДАТА
КОД_ПР
КОД_ПК
СТОИМОСТЬ
10.03.2002
107
208
18,69
10.03.2002
101
201
767,19
10.03.2002
104
207
1900,10
10.03.2002
102
203
5160,45
10.03.2002
107
208
1098,16
10.04.2002
103
202
1713,23
10.04.2002
102
204
75,75
10.05.2002
101
206
4723,00

62.

пример 4
УСТРАНЕНИЕ ИЗБЫТОЧНОСТИ ДАННЫХ
SELECT Код_пр
КОД_ПР
FROM Заказы;
107
101
104
102
107
103
102
101
102
101

63.

SELECT DISTINCT Код_пр
FROM Заказы;
КОД_ПР
107
101
104
102

64. ПРЕДЛОЖЕНИЕ WHERE КОМАНДЫ SELECT

65. ПРЕДЛОЖЕНИЕ WHERE КОМАНДЫ SELECT

ОПРЕДЕЛЯЕТ УСЛОВИЕ ИЗВЛЕЧЕНИЯ СТРОК
ПРИМЕЧАНИЕ: Необязательно, чтобы столбец в предложении where
был в числе выходных данных
Пример 5
Определить имена продавцов фирмы Лира
SELECT Пр_Имя
FROM Продавцы
WHERE Фирма=’Лира’;
ФЕДОРОВ МИХАЙЛОВ

66.

Пример 6
Найти покупателей с рейтингом =100
SELECT *
FROM Покупатели
WHERE рейтинг = 100;
КОД_П
П_ИМЯ
ФИРМА РЕЙТИНГ КОД_ПР
201
ХАРИТО
НОВ
КЛЕМЕН
ТЬЕВ
ПЕТРОВ
ЛИРА
100
101
ЛИРА
100
101
НИКА
100
104
206
207

67.

БОЛЕЕ СЛОЖНЫЕ ПРЕДИКАТЫ
ОПЕРАЦИИ = > < >= <= <> AND OR NOT
Пример 7
Найти покупателей с рейтингом >200
SELECT *
FROM Покупатели
WHERE Рейтинг >200;
КОД_П
204
208
П_ИМЯ
ГРИГ
ЦЫПЛАКОВ
РЕЙТИНГ
300
300
КОД_ПР
102
107

68.

NOT
ДОЛЖЕН ПРЕДШЕСТВОВАТЬ
ОПЕРАЦИЯМ СРАВНЕНИЯ
NOT
ПРИМЕНЯЕТСЯ ТОЛЬКО
К ТОМУ ВЫРАЖЕНИЮ, КОТОРОЕ СЛЕДУЕТ
НЕПОСРЕДСТВЕННО ЗА НИМ
ПРИМЕР NOT СТАЖ>12

69.

WHERE NOT (Стаж<12 OR Фирма = ‘Ника’);
КОД_ПР
ПР_ИМЯ
ФИРМА
СТАЖ
104
МИХАЙЛОВ
ЛИРА
11
103
АЛЕКСЕЕВ
НИКА
10
101
ФЕДОРОВ
ЛИРА
12
102
СЕРГЕЕВ
ОКА
13
107
РОМАНОВ
БЕРМУДЫ
15

70. Специальные операторы предложения WHERE

IN
BETWEEN
LIKE
IS NULL

71. IN – ПРОВЕРКА НА СОВПАДЕНИЕ С ЛЮБЫМ ЭЛЕМЕНТОМ СПИСКА

Сравниваемый диапазон заключается в круглые
скобки, элементы списка разделяются запятыми,
порядок элементов безразличен.
Пример 11
SELECT пр_имя
FROM продавцы
WHERE фирма IN ('Бермуды','Лира');
IN или in
Федоров
Михайлов
Романов

72. пример 12

Определить покупателей с рейтингом 100 200
SELECT п_имя AS ФИО
FROM покупатели
WHERE код_пр in(100,200);
ФИО
Харламов
Гаврилов
Клементьев
Петров
можно использовать операцию NOT in(……)
NOT in(101,107,104) 103 102

73.

BETWEEN________AND_______
NOT BETWEEN________AND_______
Оператор задает границы, в которые должно попасть
значение, включая границы.
Начальное значение д.б. меньше конечного

74. пример 13

Определить продавцов со стажем в диапазоне от 10 до 12 лет
SELECT пр_имя
FROM продавцы
WHERE стаж between 10 and 12;
Федоров
Алексеев
Михайлов

75. Пример 14

Выбрать покупателей, фио которых попадают в
алфавитный диапазон от А до Г
SELECT п_имя
FROM покупатели
WHERE п_имя between 'А' and 'Г';
Гаврилов
Григ

76. LIKE ( NOT LIKE)

Сравнение строковых значений.
Применим к полям типа CHAR VARCHAR
Используется для поиска подстрок с применением
шаблонов
? Или (_) заменитель одного любого символа
Гр??
Григ
Грог
Гриб
* Или (%) последовательность символов
произвольной длины
# любая одиночная цифра от 0 до 9

77. Пример 15

Найти строки записей покупателей,
фио которых начинается с буквы Г
SELECT п_имя
FROM покупатели
WHERE п_имя LIKE 'Г???';
Грир
SELECT *
FROM покупатели
WHERE п_имя LIKE 'Г*';
Грир
Гаврилов

78. РАБОТА С NULL ЗНАЧЕНИЯМИ

NULL – значение поля в данный момент не
определено,
NULL м. б. размещено в поле любого типа.
Особенность NULL значения:
если NULL сравнивается с каким либо значением и с
NULL(адрес=NULL), то результат будет ложь

79. специальный оператор IS

Для выборки NULL значений из данных
where адрес IS NULL
Чтобы исключить неопределенные значения из
данных используется специальный оператор IS
NOT NULL
where адрес IS NOT NULL

80. ФУНКЦИИ АГРЕГИРОВАНИЯ (агрегатные, групповые, многострочные)

Функции агрегирования получают одну строку из нескольких
строк
подсчитывает количество строк или значений
поля, не являющихся NULL значениями
COUNT
SUM
вычисляет сумму выбранных значений данного
поля
вычисляет среднее значение выбранных
значений данного поля
AVG
вычисляет наибольшее из выбранных значений
данного поля
MAX
вычисляет наименьшее из выбранных значений
данного поля
MIN

81.

Примечание: функции SUM и AVG применяются
только для числовых данных
Использовать функции агрегирования в предложении
WHERE нельзя
Задание:
Вывести из таблицы заказы :
Количество заказов, среднюю стоимость заказа,
сумму заказа

82. Функции

Однострочные функции на основе одной строки, через
свои аргументы, получают одну новую строку – значение
функции.
Например, функция lower из одного текстового поля одной
строки получает одно новое текстовое значение (все буквы
исходного значения заменяются на эквивалентные в
нижнем регистре).
Многострочные функции на основе нескольких строк,
через свои аргументы, получают одну новую строку –
значение функции. Например, функция avg берет по
одному числовому полю из множества строк и находит
среднее значение.

83. Стандартные встроенные функции SQL

84. Стандартные встроенные функции SQL

85. Функции Postgre SQL

86.

87.

88.

89.

90. Стандартные встроенные функции SQL

String_agg(name, ‘ ‘)
Агрегатная функция, которая принимает все
выражения из строк и сцепляет их в одну строку.
Значения выражений неявно преобразуются в
строковые типы и затем сцепляются. Неявное
преобразование в строки выполняется по
существующим правилам преобразования типов
данных.

91.

92. Пример 16

Получить стоимость всех заказов
SELECT Sum(стоимость)
FROM заказы;
Expr 1000
26 658,4
Для переименования заголовка на суммарная
стоимость используется AS
SELECT Sum(стоимость) AS суммарная стоимость
FROM заказы;

93. Пример 17

Подсчитать количество продавцов
SELECT COUNT (код_пр) AS количество_продавцов
FROM Продавцы;

94.

В качестве аргументов агрегатных функций
могут использоваться арифметические
выражения
Пример 18
Получить из таблицы заказы мах стоимость
заказа в у.е.
SELECT max(стоимость/30) AS max
FROM заказы;

95. ПРЕДЛОЖЕНИЕ GROUP_BY

Выделяет группу полей с одинаковыми
значениями для применения к ним функций
агрегирования
Пример Группировки по одному полю
Определить наибольший по стоимости заказ для
каждого продавца
SELECT код_пр AS коды, max(стоимость) AS
max_стоимость
FROM заказы
GROUP BY код_пр;

96.

коды
max_стоимость
101
9891,88
102
5160,45
103
104
107
1713,23
1900,1
1098,16

97. Применение GROUP BY к нескольким полям

Пример 20
Определить наибольший по стоимости заказ,
выполненный каждым продавцом по датам
SELECT код_пр, дата, max(стоимость)
AS стоимость
FROM заказы
GROUP BY код_пр, дата;

98.

код_пр
101
101
101
102
102
102
103
104
107
дата
10.03.02
10.05.02
10.06.02
10.03.02
10.04.02
10.06.02
10.04.02
10.03.02
10.03.02
стоимость
767,19
4723
9891,88
5160,45
75,75
1309,95
1713,23
1900,1
1098,16

99. ПРЕДЛОЖЕНИЕ HAVING УСЛОВИЕ ДЛЯ ГРУППЫ

Пример 21
Определить наибольший по стоимости заказ
превышающий 3000.00,
сделанный каждым продавцом по датам
SELECT код_пр, дата, MAX(стоимость)
FROM заказы
GROUP BY код_пр, дата
HAVING max(стоимость)>3000.00;

100.

код_пр
101
дата
ДАТА=#мм.чч.гг#
Expr1002
10.05.02
4723
101
10.06.02
9891,88
102
10.03.02
5160,45

101. СОРТИРОВКА

Сортировать данные можно по одному или
нескольким столбцам (многоуровневая),
сортировка может быть по возрастанию значений и
убыванию.
По умолчанию сортировка по возрастанию ASC, по
убыванию DESC.

102. Пример 23

Из таблицы заказы вывести поля - код_пр, дата,
стоимость, отсортировать данные по возрастанию сортировка трехуровневая, то есть –сначала по
коду пр, внутри него по дате, внутри даты по
стоимости
SELECT код_пр, дата, стоимость
FROM заказы
ORDER BY код_пр, дата, стоимость;

103.

код_пр
101
101
101
102
102
102
102
103
104
107
дата
10.03.02
10.04.02
10.06.02
10.03.02
10.03.02
10.04.02
10.06.02
10.04.02
10.03.02
10.03.02
767,19
4723
9891,88
18,69
5160,45
75,75
1309,95
1713,23
1900,1
1098,16

104. Пример 24

Вывести из таблицы Продавцы фамилии продавцов,
стаж
сортировать данные по стажу по убыванию
SELECT пр_имя AS ФИО, стаж
FROM продавцы
ORDER BY стаж DESC;

105.

ФИО
Романов
Сергеев
Федоров
Михайлов
Алексеев
стаж
15
13
12
11
10

106.

107.

108. МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ

Количество исходных таблиц не ограничено.
(Некоторые маломощные СУБД ограничивают их количество.
В OLTP запрос обычно включает 1-2 таблицы )
Таблицы перечисляются в предложении FROM,
имена таблиц разделяются запятыми.
При
этом
будет
произведение.
строиться
их
декартово

109. МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ

1
0
9
Можно задавать условие соединения, используя
предложение WHERE.
Для соединения n таблиц требуется задать n – 1
условие.

110.

SELECT п_имя , пр_имя, код_пк, дата, стоимость
FROM покупатели, продавцы, заказы
WHERE покупатели.код_п=заказы.код_п AND
продавцы.код_пр=заказы.код_пр
Недостаток использования : потеря
производительности, т.к. строится декартово
произведение, затем выборка.

111.

В примерах с одной таблицей
в имени столбцов
имя таблицы можно опустить.
Если таблиц несколько, то сравниваемые столбцы
из разных таблиц, могут иметь одинаковые имена и
тогда необходимо указывать полное имя столбца с
указанием имени таблицы продавцы.код_пр
покупатели.код_пр для однозначности
определения
Полное имя столбца состоит из имени таблицы,
точки, имени столбца
Пример:
продавцы.код_пр
покупатели.рейтинг
заказы.дата

112. Псевдонимы таблиц

11
Таблицам как и полям можно задавать на 2время
формирования запроса альтернативные имена.
Ключевое слово AS носит необязательный характер.
SELECT *
FROM покупатели А, продавцы AS C
Псевдоним целесообразно делать, при соединении
таблицы с самой собой (алиас) или, если имена
таблиц имеют длинное имя .
Использовать в запросе исходные имена таблиц
после задания псевдонима нельзя!

113. Операции соединения JOIN

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

114. Внутреннее соединение INNER JOIN

( В некоторых СУБД INNER можно не писать)
Соединение строк таблиц по условию

115. Внутреннее соединение INNER

Т1 INNER JOIN Т2 ON – условие
При внутреннем соединении в результирующую
таблицу попадают записи исходных таблиц,
которые удовлетворяют условию.
Условие ON может содержать логические
операции

116.

1
1
6
Если
рассматривать
наборы
строк
соединяемых таблиц как множества, а
множество пар строк, для которых условие
соединения истинно, как
пересечение
исходных множеств, то INNER JOIN можно
интерпретировать следующим образом:

117. Пример

пример 26
Определить ФИО покупателей, сделавших
заказы.
Таблицы Покупатели и Заказы связаны
по значению поля код_п
Один покупатель может оформлять много
заказов
Такая связь называется состоянием ссылочной
целостности

118.

119.

1 вариант
SELECT покупатели.п_имя AS ФИО
FROM покупатели JOIN заказы
ON покупатели.код_п = заказы.код_п ;

120. USING

Если названия полей (условий соединения )
в таблицах носят одинаковые имена, то вместо
конструкции ON можно записать конструкцию
USING
SELECT покупатели.п_имя AS ФИО
FROM покупатели JOIN заказы
USING (код_п);

121. Пример

2 вариант
SELECT покупатели.п_имя AS ФИО
FROM покупатели, заказы
WHERE покупатели.код_п =заказы.код_п ;

122. NATURAL JOIN

NATURAL JOIN - естественное соединение (по
всем одинаковым столбцам)
Применима только при соединении таблиц по
условию равенства одноименных полей.
SELECT покупатели.п_имя AS ФИО
FROM покупатели NATURAL JOIN заказы ;

123. ВНЕШНИЕ СОЕДИНЕНИЯ

LEFT [OUTER] JOIN левое соединение
RIGHT [OUTER] JOIN правое соединение
При левом соединении записи таблицы
находящейся слева от операции, не
соответствующие указанному условию записям
таблицы, расположенной справа, сохраняются в
результате.
При правом соединении - записи правой таблицы, не
соответствующие указанному условию записям
левой таблицы, сохраняются в результате.

124. ВНЕШНИЕ СОЕДНЕНИЯ

LEFT
JOIN можно интерпретировать
следующим образом:
RIGHT JOIN правое соединение –
1
2
4

125. Внешнее левое соединение

Активность покупателей
SELECT покупатели.п_имя AS ФИО код_пк
FROM покупатели LEFT JOIN заказы
ON покупатели.код_п = заказы.код_п ;
ФИО
код_пк
Харламов
303
Гаврилов
NULL
Лавров
305
Грир
NULL
Клементьев NULL
Петров
302

126.

FULL JOIN полное соединение
CROSS JOIN перекрестное соединение
При полном соединении записи левой и правой
таблицы сохраняются в результате при выполнении
условия хотя бы в одной из таблиц.
При перекрестном соединении каждая строка левой
таблицы соединяется с каждой строкой правой
таблицы (декартово произведение).

127. СОЕДИНЕНИЕ ДВУХ КОПИЙ ОДНОЙ ТАБЛИЦЫ (АЛИАСЫ)

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

128. Комбинирование запросов

Большинство СУБД предлагают набор команд для
комбинирования нескольких запросов в один посредством
операций, эквивалентных операциям над множествами. В
PostgreSQL для этих целей есть операторы UNION, UNION
ALL, INTERSECT, INTERSECT ALL, EXCEPT (в некоторых
других СУБД MINUS) и EXCEPT ALL.

129.

Оператор UNION позволяет объединить два запроса в
один, если у них одинаковые (по типам данных) наборы
полей.
При
образуются
этом,
если
одинаковые
в
результате
строки,
то
объединения
UNION
уберёт
дубликаты
(UNION ALL не убирает дубликаты одинаковых записей).

130.

Оператор INTERSECT находит общие для двух запросов
строки, если у них одинаковые (по типам данных) наборы
полей.
Оператор EXCEPT находит строки, присутствующие в
одном запросе, но отсутствующие в другом, если у обоих
запросов одинаковые (по типам данных) наборы полей.

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

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

132. Вложенный запрос, как таблица

133. Вложенный запрос, как таблица

Пример 20. Вывести фамилии всех продавцов и номера
их выполненных заказов.
В итоге получим следующий запрос:
SELECT B.[Имя продавца], A.[Код заказа] FROM Заказы A
FULL JOIN Продавцы B ON A.[Код продавца] = B.[Код
продавца]
SELECT C.[Имя продавца], C.[Код заказа] FROM
(
SELECT * FROM Заказы A FULL JOIN Продавцы B ON
A.[Код продавца] = B.[Код продавца] ) C

134. Вложенный запрос, как поле

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

135. Вложенный запрос, как условие

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

136.

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

137.

SELECT *
FROM заказы
WHERE код_пр=
(SELECT код_пр
FROM продавцы
WHERE пр_имя='Сергеев');
В таблице продавцы ищется поле пр_имя =Сергеев
и запоминается соответствующий код_пр
(он является результатом внутреннего SELECT)=102
Далее работает внешний запрос .
Исходной таблицей является таблица заказы .
По предикату=102 полученному в подзапросе,
отбираются строки из таблицы заказы

138. Найти заказы продавца Сергеева

139. Установка PostgreSQL

1
3
9
Так как PostgreSQL распространяется на основе свободной лицензии
(PostgreSQL is released under the PostgreSQL License, a liberal Open Source license,
similar to the BSD or MIT licenses), то его можно свободно скачать с официального
сайта:
https://www.postgresql.org/download/ [4]
Сборки для windows представляются компанией EnterpriseDB (EDB):
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads [5]
На момент написания лекции текущая стабильная версия PostgreSQL – 14.3, а
в бета-доступе уже есть версия 15.
Если ставить PostgreSQL не хочется, то можно использовать online-ресурсы:
https://onecompiler.com/postgresql/