Теория баз данных — Урок № 3- Запросы SELECT, INSERT, UPDATE, DELETE

1.

Запросы
SELECT, INSERT, UPDATE, DELETE
Чтение, добавление, изменение и удаление данных в MS SQL
Server (T-SQL) — от простейшей выборки до транзакций.
ALTER TABLE Students
ADD Grants INT;
UPDATE Students
SET Grants = 1000
WHERE StudentID = 3;
SELECT LastName, Grants FROM Students WHERE
Grants > 1000;
-- студенты со стипендией выше 1000

2.

Предложения SELECT и FROM
SELECT
FROM
считывает информацию из БД — после него
указывают имена столбцов через запятую (можно
вычисляемые результаты и функции агрегирования).
указывает источник: таблицу или список таблиц
через запятую.
SELECT LastName, FirstName, BirthDate, Grants,
Email
FROM Students;
▦ значения пяти столбцов для всех записей таблицы Students (БД University)
⊗ Запрос только с SELECT без FROM — синтаксическая
выполнение — кнопка Execute или клавиша F5 в SSMS
ошибка.
✦ результат — виртуальная таблица
ОБЩАЯ ФОРМА
SELECT columnName1, columnName2, ...
FROM tableName;
✦
*
А если * ?
SELECT * FROM Students;
Результат тот же, но запрос выполняется дольше: столбцы не
указаны явно, и СУБД тратит время на определение и получение
всех столбцов; при больших объёмах разница весьма заметна.

3.

Вычисляемые столбцы и псевдонимы
◆ В виртуальной таблице можно формировать новые столбцы и применять арифметику — реальные данные при этом не меняются.
01
✦ Склейка столбцов и псевдоним
› Символ + соединяет значения столбцов
› Без AS заголовок будет (No column
name)
SELECT FirstName + ' ' + LastName AS FullName,
BirthDate, Grants, Email
FROM Students;
› AS FullName задаёт понятное имя столбцу
02
✦
Арифметика над столбцом
“ Ректор спрашивает: как изменятся
стипендии, если их увеличить на 20
процентов?
› Если в имени столбца есть пробелы, название
берётся в квадратные скобки [ ]
SELECT FirstName + ' ' + LastName AS FullName, Grants *
1.2 AS [Plus 20 persent]
FROM Students;

4.

Приведение типов: CAST() и CONVERT()
⊗ Конкатенация текста с числовым столбцом Grants завершается ошибкой — недопустимое приведение типов данных: вещественное
значение нельзя склеить со строкой через +.
РЕШЕНИЕ
В T-SQL две стандартные функции приведения типов — CAST() и CONVERT(); они отличаются принимаемыми параметрами, но результат
одинаковый.
CAST()
SELECT 'Student ' + LastName + ' получает ' +
CAST(Grants AS nvarchar(10))
FROM Students;
✦
CONVERT()
SELECT 'Student ' + LastName + ' получает ' +
CONVERT(nvarchar(10), Grants)
FROM Students;
✦

5.

Операторы TOP и DISTINCT
↑ TOP — первые записи
С предложением SELECT возвращает первые записи либо в
количественном, либо в процентном отношении.
SELECT TOP 2 LastName, FirstName, BirthDate
FROM Students;
☰ DISTINCT — без повторов
Если поле содержит много повторяющихся значений,
DISTINCT перед именем столбца исключит все повторения из
результата.
✓ Задача: определить, какие имена есть у студентов,
исключив повторы.
→ Первые две записи таблицы
SELECT DISTINCT FirstName
FROM Students;
SELECT TOP 20 PERCENT LastName, FirstName,
BirthDate
FROM Students;
К АК Р АБ О Т АЕ Т D IS T IN C T
→
первые 20 процентов записей (ключевое
слово PERCENT после числа)
FirstName →
→
John
John
David
David
Jack
John
Jack
John

6.

Предложение WHERE — фильтрация данных
О Б Щ АЯ Ф О Р М А З АП Р О С А
SELECT columnName1, columnName2, ... FROM tableName WHERE condition;
О П ЕРАТО РЫ СРАВ Н ЕН И Я T - SQL
1
= Равно
<> Не равно
> Больше
>= Больше или равно
< Меньше
<= Меньше или равно
!> Не больше чем
!< Не меньше чем
Вывести фамилии и стипендии студентов, у которых стипендия
превышает 1000.
2
Студенты, в имени которых не больше четырёх символов —
функция в условии.
ПРИМЕР 1
ПРИМЕР 2 · ФУНКЦИЯ В УСЛОВИИ
SELECT LastName, Grants
FROM Students
WHERE Grants > 1000;
SELECT Id, LastName, FirstName, BirthDate,
Grants, Email
FROM Students
WHERE LEN(FirstName) <> 4;
✦ LEN() возвращает длину строки; это встроенная функция MS
SQL Server — она не входит в стандарт T-SQL и выполняется
самой СУБД.

7.

Логические операторы и проверка NULL
AND Истина — только если верны оба
OR Хотя бы одно из условий
Объединяет два условия. Студенты, родившиеся осенью:
Истина, если верно хотя бы одно условие. Чётный год рождения
или нечётный день:
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE YEAR (BirthDate) >= 9 AND MONTH(BirthDate) <= 11;
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE YEAR(BirthDate) % 2 = 0 OR DAY (BirthDate) % 2 <> 0;
✦ Функции MONTH(), YEAR(), DAY() извлекают части даты.
✦ % — деление по модулю.
IS NULL Обычные сравнения с NULL не работают
NOT
Операторы сравнения (=, <>, >, <) со значениями NULL не
работают. Студенты, которые не получают стипендию:
Все студенты, кроме фамилии Волкова:
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE Grants IS NULL;
⇄ Противоположность — IS NOT NULL: кто получает стипендию.
Противоположное условие
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE NOT LastName = 'Волкова';
⊘ NOT инвертирует условие: в результат попадут строки, где оно
ложно.

8.

Предложение ORDER BY — сортировка результата
О Б Щ АЯ Ф О Р М А З АП Р О С А
SELECT columnName1, columnName2, ...
FROM tableName
ORDER BY columnName1 ASC | DESC, ...;
DESC
↓ Descending — сортировка по
убыванию.
1
Список студентов, отсортированный по дате рождения.
ПРИМЕР 1
SELECT LastName, FirstName, BirthDate
FROM Students
ORDER BY BirthDate;
2 Несколько полей: сортировка по убыванию фамилии и по возрастанию
имени.
ASC
↑
Ascending — по возрастанию; это
сортировка по умолчанию — её
можно не указывать или указать
явно.
ПРИМЕР 2 · ДВА ПОЛЯ СОРТИРОВКИ
SELECT LastName, FirstName, BirthDate
FROM Students
ORDER BY LastName DESC, FirstName ASC;
→ Сначала идёт сортировка по фамилии; сортировка по имени запускается, только
когда фамилия совпала более чем в одной строке результата.

9.

Ключевые слова IN и BETWEEN
IN
Значение из множества
Задача: студенты по имени Дмитрий, Михаил или Елена.
БЫЛО · ГРОМОЗДКО, МНОГО OR
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE FirstName = 'Дмитрий' OR FirstName = 'Михаил' OR
FirstName = 'Елена';
BETWEEN Попадание в диапазон — границы включаются
Задача: студенты, родившиеся в 2003 году — два эквивалентных
запроса.
ЭКВИВАЛЕНТ ЧЕРЕЗ ОПЕРАТОРЫ СРАВНЕНИЯ
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE BirthDate >= '2003-01-01' AND BirthDate <= '2003-12-31';
ЭКВИВАЛЕНТ ЧЕРЕЗ BETWEEN
↓ СТАЛО · ЧИТАБЕЛЬНО
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE FirstName IN ('Дмитрий', 'Михаил', 'Елена');
✓ IN сравнивает значение столбца со множеством значений;
результат тот же.
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE BirthDate BETWEEN '2003-01-01' AND '2003-12-31';
▦ Даты — в порядке год-месяц-день, в одинарных кавычках:
такое представление определено драйвером ODBC.
ЕЩЁ ПРИМЕР · ФАМИЛИИ НЕ НАЧИНАЮТСЯ НА В, К
WHERE LastName NOT BETWEEN ‘В' AND ‘К';
⇅ BETWEEN работает и со строками — сравнение по кодам символов.

10.

Ключевое слово LIKE — поиск по шаблону
Для поиска по текстовым полям шаблон задают служебными символами.
СЛУЖЕБНЫЕ СИМВОЛЫ ШАБЛОНА
%
Любая последовательность символов — от 0
и более.
_
Любой одиночный символ.
[]
Последовательность или диапазон
возможных символов.
[^]
Последовательность или диапазон
символов, которые должны отсутствовать.
1 Фамилия начинается на В или К, а имя заканчивается на л.
ПРИМЕР 1
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE LastName LIKE '[ВК]%‘ AND FirstName LIKE '%л';
2 Вторая буква e-mail не входит в диапазон f–m.
П Р И М Е Р 2 · Р АЗ Б О Р П О С И М В О Л Ь Н О
SELECT StudentId, LastName, FirstName, BirthDate, Grants, Email
FROM Students
WHERE Email LIKE '_[^f-m]%';
_ Ровно один первый символ
[^f-m] Любой символ вне диапазона f–m

11.

Оператор INSERT — добавление записей
Раньше таблицы заполняли средствами SSMS; INSERT позволяет наполнять их программным путём — двумя способами.
1
С именами столбцов
INSERT INTO tableName (columnName1, columnName2, ...)
VALUES (value1, value2, ...);
2
Без имён столбцов
INSERT INTO tableName
VALUES (value1, value2, ...);
✓ Указываем как минимум все столбцы, которые не могут
принимать NULL — иначе SSMS выдаст ошибку:
⇅ Порядок столбцов в запросе может не совпадать с порядком в таблице.
✓ Значения указываются для всех столбцов — обязательно в
порядке их следования в таблице.
⊗ «Невозможно вставить NULL-значение в столбец BirthDate»
Перепутаете порядок значений — данные запишутся не в
свои столбцы.
ПР ИМ Е Р УР ОК А · Д ОБАВ ЛЯ Е М С ТУД Е НТК У, К ОТОРАЯ НЕ ПОЛУЧ АЕ Т С ТИПЕ НД ИЮ
INSERT INTO Students
VALUES ('Андрей', 'Смирновв', '2004-08-30', 'eg@net.eu', 2, NULL);
ⓘ NULL для стипендии указываем явно — иначе в таблицу запишутся неверные данные.

12.

Копирование данных: INSERT INTO SELECT и SELECT INTO
⎘ INSERT INTO SELECT — заполнить существующую таблицу
SELECT INTO — создать новую таблицу на
результатом запроса
основе существующей
ФОРМА · КОПИРУЕМ ВСЕ ДАННЫЕ
SELECT columnName1, columnName2, ...
INTO newTable
FROM existingTable
WHERE condition;
INSERT INTO destinationTable
SELECT * FROM sourceTable WHERE condition;
ФОРМА · С ЯВНЫМИ СТОЛБЦАМИ
INSERT INTO destinationTable (columnName1, columnName2, ...)
SELECT columnName1, columnName2, ...
FROM sourceTable WHERE condition;
Типы данных соответствующих столбцов должны совпадать;
▤ названия столбцов могут различаться.
existingTable Источник → newTable Создаётся запросом
Практика урока
1 Создали таблицу Temp —
студенты, родившиеся во
второй половине года:
MONTH(BirthDate) >= 7
2
SELECT INTO создал
таблицу Temp1 — студенты
с известным e-mail
3
Проверка — SELECT из
новых таблиц
4
Временные таблицы удалены
через Object Explorer: правый
клик по таблице → Delete →
OK

13.

Оператор UPDATE — изменение записей
Сценарий урока
Ректор университета решил выплачивать минимальную стипендию всем
студентам, а хорошо успевающим — повысить. Для этого выполняются
два запроса UPDATE.
1
ОБЩАЯ ФОРМА
↓
UPDATE tableName
SET columnName1 = value1, columnName2 = value2, ...
WHERE condition;
2
!
WHERE считаем обязательным — если не указать условие,
изменятся значения столбца по всей таблице.
ПРОВЕРКА РЕЗУЛЬТАТА
ЗАПРОС 1
Установить минимальную стипендию всем студентам
ЗАПРОС 2
Повысить стипендию хорошо успевающим
ВАЖНЫЙ ВЫВОД
Порядок выполнения запросов критичен: если сначала установить
стипендию отстающим, а затем повысить стипендию хорошо
успевающим, у отстающих студентов она вырастет вдвое.
SELECT LastName, FirstName, Grants FROM Students;

14.

Оператор DELETE — удаление записей
ОБЩАЯ ФОРМА
DELETE FROM tableName
WHERE condition;
✕ ОСТОРОЖНО
DELETE используют с особой осторожностью
— легко случайно удалить важную информацию. Если не
задать граничное условие WHERE, запрос удалит ВСЮ
информацию из текущей таблицы.
ПРИМЕР УРОКА
Удалить все записи о студентах с уникальным
идентификатором больше 9
DELETE FROM Students WHERE Id > 9;
✦ В демо урока удалены 2 записи — сообщение 2 rows affected; результат в вашей базе может отличаться.

15.

Понятие транзакции. Свойства ACID
◫
ЖИЗНЕННЫЙ ПРИМЕР
Классический пример — перевод денег со счёта на
счёт: деньги должны дойти до получателя, а если в
момент операции произойдёт сбой системы —
вернуться отправителю.
✓
ОПРЕДЕЛЕНИЕ
Транзакция — последовательность действий, выполняемых как
единое целое, с возможностью отмены каждого из них в случае
ошибки.
Четыре свойства ACID
A
✓
Atomicity
(Атомарность)
Целостность транзакции:
для успешного завершения
должны выполниться либо
все операции, либо ни
одна.
С
C
I
⛨
D
D
⤓
Consistency
Isolation
Durability
Целостность информации
независимо от того,
успешно ли завершилась
транзакция.
Все транзакции
выполняются параллельно
и не влияют друг на друга.
Сохранность всех данных
после фиксации успешной
транзакции, независимо
от сбоев системы.
(Согласованность)
(Изолированность)
(Надёжность)

16.

Типы транзакций и уровни изолированности
⇄ Три типа транзакций
◉ Явные
Явно указывается начало BEGIN TRAN / BEGIN
TRANSACTION, фиксация COMMIT TRAN и откат
ROLLBACK TRAN.
⊘ Неявные
Начало и конец не указываются; каждая инструкция —
ALTER TABLE CREATE DROP SELECT INSERT DELETE
UPDATE GRANT REVOKE OPEN FETCH TRUNCATE
TABLE — выполняется как отдельная транзакция.
✦ Четыре уровня изолированности
слабее ↓ строже
READ UNCOMMITTED
Позволяет читать изменённые, но незафиксированные данные; не
предотвращает ни одно из нарушений.
READ COMMITTED
по умолчанию
Не позволяет читать незафиксированные данные; используется по
умолчанию.
REPEATABLE READ
Никакая транзакция не может изменять данные, считанные текущей, до её
завершения; предотвращает неповторяемое чтение.
↻ Автоматические
Каждая успешная операция фиксируется, иначе
откатывается; режим переключается командой SET
IMPLICIT_TRANSACTIONS ON / OFF.
SERIALIZABLE
Другим транзакциям запрещено вставлять/изменять/удалять данные из
диапазона WHERE текущей транзакции; предотвращает и фантомы.
SQL Server — многопользовательская система. Четыре возможных нарушения: чтение незафиксированных данных, неповторяемое
ⓘ MS
чтение, фантомы, потерянные обновления. Для защиты применяются блокировки (6 типов ресурсов: база данных, таблица, экстент, страница,
ключ, строка).

17.

П РА К Т И Ч Е С К О Е З А Д А Н И Е
✚
Работаем с базой данных «Колледж» и таблицей Students. Заполните
таблицу произвольными данными оператором INSERT — не менее
10 записей — и напишите SQL-запросы.
1 Все студенты, находящиеся в колледже
7 Самый молодой студент и его возраст
2 Студенты определённой группы
8 Студенты любых трёх специальностей
3 Названия всех отделений групп
9 Студенты, фамилия которых начинается на букву Р
4 Студенты, которые на 4 курсе, — сортировка по возрастанию
10 Студенты определённого домена почты
даты рождения
5 Студенты у которых день рождения было в прошлом месяце
11 Студенты определённой специальности
— пригодится функция GETDATE()
6 Студенты у которых день рождение с октября по декабрь
прошлого года в группе 3
12 Переименовать группу; удалить всех студентов, поступивших
в прошлом году