DB_2023_6

1.

Базы данных
(часть 6)
Киселев Денис Викторович

2.

SQL: операторы
Операторы определения данных
(Data Definition Language, DDL)
•CREATE – создание объекта БД
•ALTER – изменение структуры объекта БД
•DROP – удаление объекта БД
Операторы манипулирования данными
(Data Manipulation Language, DML)
•SELECT – выборка данных
•INSERT – добавление новых данных
•UPDATE – изменение данные
•DELETE – удаление данных

3.

SQL: операторы
Операторы определения доступа к данным
(Data Control Language, DCL)
•GRANT – предоставление пользователю (группе
пользователей) разрешения на определенные действия в БД
•REVOKE – отзыв ранее выданных разрешений
•DENY – запрет определенных действий, имеющий
приоритет над разрешением
Операторы управления транзакциями
(Transaction Control Language, TCL)
•COMMIT – фиксация транзакции
•ROLLBACK – откат изменений, сделанных в транзакции
•SAVEPOINT – создание точки сохранения в транзакции

4.

SQL: оператор выборки SELECT
SELECT столбцы
FROM таблицы
WHERE условие

5.

SQL: оператор выборки SELECT
SELECT столбцы FROM таблицы WHERE условие;
SELECT Фамилия, Имя FROM Студенты
WHERE Группа = ‘МП-21’;
SELECT Фамилия, Имя FROM Студенты
WHERE Группа <> ‘МП-21’;
SELECT Фамилия, Имя FROM Студенты
WHERE Группа != ‘МП-21’;
SELECT Фамилия, Имя FROM Студенты
WHERE Возраст >= 18;

6.

SQL: оператор выборки SELECT
SELECT столбцы FROM таблицы WHERE условие;
SELECT * FROM Студенты
WHERE Возраст BETWEEN 20 And 25;
SELECT * FROM Студенты
WHERE Группа BETWEEN ‘МП-21’ And ‘МП-29’;
SELECT * FROM Студенты WHERE Группа Like ‘МП-2%’;
SELECT * FROM Студенты WHERE Группа Like ‘МП-2_’;
SELECT * FROM Студенты WHERE Отчество Is Null;
SELECT * FROM Студенты WHERE Дата_рождения Is Not Null;

7.

SQL: оператор выборки SELECT
SELECT столбцы FROM таблицы WHERE условие;
SELECT * FROM Студенты
WHERE Группа = ‘МП-21’ And Группа = ‘МП-22’;
SELECT * FROM Студенты
WHERE Группа = ‘МП-21’ Or Группа = ‘МП-22’;
SELECT * FROM Студенты WHERE Not Группа = ‘МП-21’;
SELECT * FROM Студенты
WHERE Not (Группа = ‘МП-21’ And Возраст = 20);
SELECT * FROM Студенты WHERE Группа In (‘МП-21’, ‘МП-22’);
SELECT * FROM Студенты
WHERE (Группа = ‘МП-21’ And Возраст = 20)
Or (Группа = ‘МП-22’ And Возраст = 21);

8.

Логические выражения конструкции
WHERE
=
<>
<
>
>=
<=
Between… and…
Is Null (Is not Null)
In (…)
Like ‘…’
And
Or
Not

9.

SQL: оператор выборки SELECT
SELECT столбцы FROM таблицы
WHERE условие ORDER BY столбцы;
SELECT * FROM Студенты ORDER BY Фамилия, Имя
SELECT * FROM Студенты ORDER BY Фамилия ASC, Имя ASC
SELECT * FROM Студенты ORDER BY Фамилия DESC, Имя DESC
SELECT * FROM Студенты ORDER BY Фамилия, Имя DESC
SELECT Distinct столбцы
FROM таблицы WHERE условие;
SELECT Группа FROM Студенты;
SELECT Distinct Группа FROM Студенты;

10.

SQL: оператор выборки SELECT:
псевдонимы
SELECT
столбец_1 [псевдоним], столбец_2 As [псевдоним]
FROM таблица;
SELECT
Фамилия,
Фамилия As Фамилия_2,
Фамилия Фамилия_3,
FROM Студенты;

11.

SQL: оператор выборки SELECT:
псевдонимы
SELECT столбцы
FROM таблица_1 [псевдоним];
SELECT Фамилия, Студенты.Фамилия
FROM Студенты;
SELECT Фамилия, С.Фамилия
FROM Студенты As С;
SELECT Фамилия Ф FROM Студенты С;

12.

SQL: оператор выборки SELECT
выражения и функции
SELECT (Стипендия*10 + 1 000 000)/5 As Новая_стипендия
FROM Студенты;
SELECT Year(Date())-Year(Дата_рождения) FROM Студенты;
SELECT DateDiff(“yyyy”, Дата_рождения, Date()) FROM Студенты;
СУБД
Функция, возвращающая текущую дату
MS SQL Server
GETDATE(), SYSDATETIME()
MS Access
NOW(), DATE(), TIME()
Oracle
SYSDATE()

13.

SQL: оператор выборки SELECT:
группировка, агрегатные функции
SELECT столбцы, агрегатные функции
FROM таблицы GROUP BY столбцы;
SELECT Группа FROM Студенты GROUP BY Группа;
Номер
Имя
Группа
...
...
МП-21
...
...
МП-21
...
...
МП-21
...
...
МП-22
...
...
МП-22
...
...
МП-23
Группа
МП-21
МП-22
МП-23

14.

SQL: оператор выборки SELECT:
группировка, агрегатные функции
SELECT Группа, Count(*) FROM Студенты GROUP BY Группа;
Номер
Имя
Группа
...
...
МП-21
...
...
МП-21
...
...
МП-21
Номер
Имя
Группа
...
...
МП-22
...
...
МП-22
Номер
Имя
Группа
...
...
МП-23
Группа
Count(*)
МП-21
3
МП-22
2
МП-23
1

15.

SQL: оператор выборки SELECT:
группировка, агрегатные функции
SELECT столбцы, агрегатные функции
FROM таблицы GROUP BY столбцы;
Агрегатные функции:
• Count();
• Max();
• Min();
• Sum();
• Avg()
SELECT Группа FROM Студенты GROUP BY Группа;
SELECT Группа, Count(*) FROM Студенты GROUP BY Группа;
SELECT Группа, Sum(Стипендия) FROM Студенты
GROUP BY Группа;

16.

SQL: оператор выборки SELECT:
группировка, агрегатные функции
SELECT столбцы, агрегатные функции
FROM таблицы GROUP BY столбцы;
SELECT Номер, Предмет, Avg(Оценка) FROM Оценки
GROUP BY Номер, Предмет;
SELECT Группа, Гражданство, Count(*) FROM Студенты
GROUP BY Группа, Гражданство;
SELECT Группа, Фамилия, Count(*) FROM Студенты
GROUP BY Группа;

17.

SQL: оператор выборки SELECT:
группировка, агрегатные функции
SELECT столбцы, агрегатные функции
FROM таблицы GROUP BY столбцы;
SELECT Count(*) FROM Студенты;
SELECT Max(Дата_рождения) FROM Студенты;
SELECT Avg(Оценка) FROM Оценки;

18.

SQL: оператор выборки SELECT:
группировка, агрегатные функции
SELECT столбцы, агрегатные функции
FROM таблицы
WHERE условие
GROUP BY столбцы
HAVING условие;
SELECT Группа, Count(*) FROM Студенты
WHERE Институт = ‘МП’
GROUP BY Группа
HAVING Count(*)<20;
SELECT Группа, Count(*) FROM Студенты
WHERE Институт = ‘МП’
GROUP BY Группа
HAVING Avg(Возраст)>18;

19.

SQL: оператор выборки SELECT: запрос к
нескольким таблицам
Номер
Фамилия
Имя
Группа
Номер
Предмет
Семестр
Оценка
111
Иванов
Иван
МП-11
111
ОМА
1
3
112
Сидоров
Сидор
МП-11
111
ОФ
1
3
113
Петров
Петр
МП-12
112
ОФ
1
4
SELECT * FROM Студенты С, Оценки О
WHERE С.Номер=О.Номер
Номер
Фамилия
Имя
Группа
Номер
Предмет
Семестр
Оценка
111
Иванов
Иван
МП-11
111
ОМА
1
3
111
Иванов
Иван
МП-11
111
ОФ
1
3
111
Иванов
Иван
МП-11
112
ОФ
1
4
112
Сидоров
Сидор
МП-11
111
ОМА
1
3
112
Сидоров
Сидор
МП-11
111
ОФ
1
3
112
Сидоров
Сидор
МП-11
112
ОФ
1
4
113
Петров
Петр
МП-12
111
ОМА
1
3
113
Петров
Петр
МП-12
111
ОФ
1
3
113
Петров
Петр
МП-12
112
ОФ
1
4

20.

SQL: оператор выборки SELECT: запрос к
нескольким таблицам
SELECT * FROM Студенты С, Оценки О WHERE С.Номер = О.Номер;
SELECT * FROM Студенты С Inner Join Оценки О
On С.Номер = О.Номер;
(Внутреннее соединение)
SELECT * FROM Студенты С, Оценки О, Предметы П WHERE С.Номер
= О.Номер And О.Код_предмета = П.Код_предмета;
SELECT * FROM Студенты С Inner Join Оценки О
On С.Номер = О.Номер Inner Join Предметы П
On О.Код_предмета = П.Код_предмета;

21.

SQL: оператор выборки SELECT: запрос к
нескольким таблицам (внешнее соединение)
SELECT * FROM Студенты С Left Outer Join Оценки О
On С.Номер = О.Номер;
Номер
Фамилия
Имя
Группа
Номер
Предмет
Семестр
Оценка
111
Иванов
Иван
МП-11
111
ОМА
1
3
111
Иванов
Иван
МП-11
111
ОФ
1
3
112
Сидоров
Сидор
МП-11
112
ОФ
1
4
113
Петров
Петр
МП-12
NULL
NULL
NULL
NULL
SELECT * FROM Студенты С Right Outer Join Оценки О
On С.Номер = О.Номер;
SELECT * FROM Студенты С Full Outer Join Оценки О
On С.Номер = О.Номер;

22.

SQL: выборка иерархических данных
Список
Номер
Имя
Старший
1
А
NULL
2
Б
1
3
В
1
4
Г
1
5
Д
2
6
Е
2
Подчиненный
Руководитель
Б
А
В
А
Г
А
Д
Б
Е
Б
А
Б
Д
В
Г
Е
SELECT
Подчиненные.Имя,
Руководители.Имя
FROM Список as Подчиненные
INNER JOIN Список as Руководители
ON Руководители.Номер
= Т1. Подчиненные;

23.

SQL: выборка иерархических данных
Список
Номер
Имя
Старший
1
А
NULL
2
Б
1
3
В
1
4
Г
1
5
Д
2
6
Е
2
Узел 1
Узел 2
Узел 3
А
Б
Д
А
Б
Е
А
В
NULL
А
Г
NULL
А
Б
Д
В
Г
Е
SELECT Ур1.Имя, Ур2.Имя, Ур3.Имя
FROM Список Ур1
Left Join Список Ур2
On Ур1.Номер = Ур2.Старший
Left Join Список Ур3
On Ур2.Номер = Ур3.Старший
WHERE Ур1.Старший Is NULL;

24.

SQL: выборка иерархических данных
Список
А
Связи
Номер
Имя
Старший
Младший
1
А
1
2
2
Б
1
3
3
В
1
4
4
Г
2
5
5
Д
2
6
6
Е
Подчиненный
Руководитель
Б
А
В
А
Г
А
Д
Б
Е
Б
Б
Д
В
Г
Е
SELECT С2.Имя, С1.Имя
FROM Связи С Inner Join Список С1
On С1.Номер = С.Старший
Inner Join Список С2
On С2.Номер = С.Младший