Accenture tver atc java capability

1.

ACCENTURE TVER ATC
JAVA CAPABILITY
JAVA
SQL. DML. TCL
Copyright © 2021 Accenture All Rights Reserved

2.

Содержание
1. DML
2. SELECT
3. JOIN
4. Агрегация
5. INSERT
6. UPDATE
7. DELETE
8. Транзакции и TCL

3.

DML
DATA MANIPULATION
LANGUAGE
Предназначен для получения и изменения данных БД.
Основные команды:
SELECT - выбирает данные из таблицы
INSERT
- вставляет данные в таблицу
UPDATE - изменяет данные в таблице
DELETE - удаляет данные из таблицы
-- вставка строк в таблицу
INSERT INTO user VALUES(
1, 'Иван', 'Петров', '1990-04-15');
INSERT INTO user VALUES(
2, 'Петр', 'Иванов', '1988-02-10');
DML операции INSERT, UPDATE, DELETE проходят в
рамках транзакции БД.
-- изменение данных
UPDATE user
SET first_name = 'Петр'
WHERE id = 1;
Для сохранения выполненных изменений транзакция
должна быть подтверждена через TCL операцию
COMMIT.
Либо при выполнении TCL операции ROLLBACK
произойдет откат всех изменений и БД вернется в
свое исходное состояние. (подробнее механизм
транзакций будет рассмотрен в дальнейшем).
-- удаление строки данных
DELETE FROM user
WHERE id = 2;
-- подтверждение транзакции
COMMIT;
-- получение данных
SELECT *
FROM user
WHERE first_name = 'Петр';

4.

SELECT
Позволяет получить данные из БД
Основной синтакцис:
SELECT attr1, …, attrN
FROM <таблица>
WHERE <условие выборки>;
-- получение данных c условием
SELECT id, first_name, last_name
FROM user
WHERE first_name = 'Петр';
Секция WHERE является необязательной. Если ее
не указывать будут получены все записи таблицы.
-- получение всех строк таблицы
SELECT id, first_name, last_name
FROM user;
Для вывода всех атрибутов можно использовать
SELECT * вместо перечисления конкретных
атрибутов.
-- получение всех атрибутов
SELECT *
FROM user
WHERE id = 1;
Каждой таблице, участвующей в FROM, можно
задать псевдоним (alias), который можно
использовать при указании атрибутов в SELECT и
WHERE.
-- использование alias для таблицы
SELECT u.id
FROM user u
WHERE u.first_name = 'Иван';

5.

SELECT, WHERE секция
WHERE секция позволяет указать условие выборки данных.
Условие выборки представляет собой логическое
выражение (true/false), которое применяется для
каждой строки данных. Если условие
удовлетворяется, то строка данных попадает в
итоговую выборку.
Условия в WHERE могут объединяться логическими
операторами AND (И), OR (ИЛИ), NOT (НЕ).
Для практически для всех типов данных
поддерживаются операторы сравнения =, >, <, >=,
<=.
Оператор BETWEEN позволяет проверить
попадание значения атрибута в дипазон.
Оператор IN позволяет проверить вхождение
значение атрибута в перечень значений.
Оператор EXISTS позволяет проверить наличие
записей в целевом подзапросе.
ALTER TABLE user ADD COLUMN age INTEGER;
-- получение данных c условием
SELECT id, first_name, last_name
FROM user
WHERE first_name = 'Петр' AND age > 18;
-- BETWEEN
SELECT id, first_name, last_name
FROM user
WHERE age BETWEEN 14 AND 18;
-- IN
SELECT *
FROM user
WHERE id IN (1, 2, 3);
-- EXISTS, получить пользователей без счетов
SELECT u.*
FROM user u
WHERE NOT EXISTS
(SELECT 1 FROM account a // коррелированный
WHERE a.user_id = u.id); // подзапрос

6.

CROSS JOIN, декартово произведение
Представляет собой простейший вид JOIN двух таблиц.
Результатом является множество всех пересечений строк таблиц.
user
id (PK)
first_name
last_name
birth_date
age
document
id (PK)
issue_date
seria
num
user_id (FK)
-- сочетание всех пользователей и доков
SELECT u.first_name, u.last_name,
d.num, u.id, d.user_id
FROM user u, document d;
-- либо
SELECT u.first_name, u.last_name,
d.num, u.id, d.user_id
FROM user u CROSS JOIN document d;
id
first_name
last_name
age
birth_date
1
Иван
Иванов
31
01.01.1990
2
Петр
Петров
51
02.02.1970
3
Макар
Макаров
11
03.03. 2010
id
issue_date
seria
num
user_id
1
01.01.2010
1111
123456
1
2
02.02.2000
2222
345678
2
3
02.02.2020
3333
456789
2
#
first_name
last_name
num
u.id
d.user_id
1
Иван
Иванов
123456
1
1
2
Петр
Петров
123456
2
1
3
Макар
Макаров
123456
3
1
4
Иван
Иванов
345678
1
2
…
…
…
…
…
…
9
Макар
Макаров
456789
3
3

7.

INNER JOIN
Соединение данных двух таблиц по условию.
Основной синтакцис:
SELECT attr1, …, attrN
FROM <таблица1> JOIN <таблица2>
ON <условие соединения>
WHERE <условие выборки>;
В итоговую выборку не попадают строки данных,
по которым нет совпадений по условию
соединения.
-- CROSS JOIN + условие
SELECT u.first_name, u.last_name,
d.num, u.id, d.user_id
FROM user u, document d
WHERE u.id = d.user_id;
-- предпочтительная форма записи
SELECT u.first_name, u.last_name,
d.num, u.id, d.user_id
FROM user u JOIN document d
ON u.id = d.user_id;
id
first_name
last_name
age
birth_date
1
Иван
Иванов
31
01.01.1990
2
Петр
Петров
51
02.02.1970
3
Макар
Макаров
11
03.03. 2010
id
issue_date
seria
num
user_id
1
01.01.2010
1111
123456
1
2
02.02.2000
2222
345678
2
3
02.02.2020
3333
456789
2
#
first_name
last_name
num
u.id
d.user_id
1
Иван
Иванов
123456
1
1
2
Петр
Петров
345678
2
2
3
Петр
Петров
456789
2
2

8.

OUTER JOIN (LEFT/RIGHT/FULL)
Соединение данных двух таблиц по условию c сохранением данных без совпадения.
Основной синтакцис:
SELECT attr1, …, attrN
FROM <таблица1> LEFT JOIN <таблица2>
ON <условие соединения>
WHERE <условие выборки>;
В итоговую выборку попадают строки данных, по
которым нет совпадений по условию соединения
(LEFT – для левой таблицы, RIGHT – для правой,
FULL – для обоих).
id
first_name
last_name
age
birth_date
1
Иван
Иванов
31
01.01.1990
2
Петр
Петров
51
02.02.1970
3
Макар
Макаров
11
03.03. 2010
id
issue_date
seria
num
user_id
1
01.01.2010
1111
123456
1
2
02.02.2000
2222
345678
2
3
02.02.2020
3333
456789
2
Атрибуты таблиц, по которым не прошло
соеднинения возвращаются как NULL.
-- пользователи и документы
SELECT u.first_name, u.last_name,
d.num, u.id, d.user_id
FROM user u LEFT JOIN document d
ON u.id = d.user_id;
#
first_name
last_name
num
u.id
d.user_id
1
Иван
Иванов
123456
1
1
2
Петр
Петров
345678
2
2
3
Петр
Петров
456789
2
2
4
Макар
Макаров
NULL
3
NULL

9.

Графическое представление JOIN
user
document
INNER JOIN
user
document
RIGHT JOIN
user
document
LEFT JOIN
user
document
FULL JOIN

10.

SELECT, сортировка данных
Позволяет получить данные из БД
Основной синтакцис:
SELECT <attr1, …, attrN>
FROM <таблица>
WHERE <условие выборки>
ORDER BY <attr1 …, attrN> <ASC/DESC>;
По умолчанию используется порядок сортировки по
возрастанию. Можно явно указать требуемый
порядок сортировки:
ASC – по возврастанию.
DESC – по убыванию.
Секция ORDER BY является необязательной. Если
ее не указывать строки будут возвращены в
неопределенном порядке.
-- сортировка по возврастанию по фамилии
-- при равенстве фамилий по имени
SELECT id, first_name, last_name
FROM user
ORDER BY last_name, first_name;
-- сортировка по возрасту по убыванию
SELECT id, first_name, last_name
FROM user
ORDER BY age DESC;
-- сортировка по возрасту по убыванию
-- затем по фамилии
SELECT id, first_name, last_name
FROM user
ORDER BY age DESC, last_name ASC;

11.

Агрегация
Позволяет получить данные с помощью агрегационных методов.
Основной синтакцис:
SELECT <attr1, …, attrN>, aggr_method(attrM)
FROM <таблица>
WHERE <условие выборки>
GROUP BY <attr1, …, attrN>
HAVING <условие выборки по агрегации>
Основные методы:
COUNT – количество записей.
MAX/MIN – мин/макс значение.
AVG – среднее значение.
В секции SELECT без агрегации могут указываться
только атрибуты, которые указаны в GROUP BY.
Необязательная секция HAVING может
использоваться для фильтрации по агрегационным
результатам (на подобии WHERE).
-- средний возраст пользователей
SELECT AVG(age)
FROM user;
-- кол-во пользователей в разрезе фамилий
SELECT last_name, COUNT(1)
FROM user
GROUP BY last_name;
-- максимальная дата выдачи документа
SELECT u.id, u.first_name, u.last_name,
MAX(d.issue_date)
FROM user u LEFT JOIN document d
ON u.id = d.user_id
GROUP BY u.id, u.first_name, u.last_name;
-- фамилии повторяющиеся более 1 раза
SELECT last_name
FROM user
GROUP BY last_name
HAVING COUNT(1) > 1;

12.

INSERT
Позволяет вставить одну или несколько строк в целевую таблицу.
Основной синтакцис:
INSERT INTO <имя таблицы> (attr1, …, attrN)
VALUES (value1, …, valueN);
Список имен атрибутов в () может быть опущен. Тогда
СУБД будет учитывать порядок атрибутов в таблицы.
Но данный подход не является безопасным, особенно
если используются атрибуты со значением по
умолчанию.
При вставке СУБД проверяет все существующие
CONSTRAINTS на значения атрибутов таблицы. Если
проверки не пройдут, то запись вставлена не будет,
вернется ошибка.
Можно использовать конструкцию INSERT SELECT
для вставки сразу нескольких записей на основе
данных из другой таблицы.
-- без указания атрибутов
INSERT INTO user VALUES(
1, 'Иван', 'Петров', '1990-04-15’);
-- c указание атрибутов
INSERT INTO user(id, first_name, last_name)
VALUES(2, 'Петр', 'Иванов');
-- не пройдет по NOT NULL constraint
-- на атрибуте last_name
INSERT INTO user(id, first_name, last_name)
VALUES(2, 'Петр’, NULL);
-- INSERT SELECT
INSERT INTO user(id, first_name, last_name)
SELECT id, first_name, last_name
FROM bank_user;

13.

UPDATE
Позволяет обновить данные одной или нескольких строк в целевой таблице.
Основной синтакцис:
UPDATE <имя таблицы>
SET attr1 = value1, …, attrN = valueN
WHERE <условие выборки>;
Секция WHERE является необязательной. Если ее не
указывать будут обновлены все записи таблицы.
Для WHERE секции валидны все те же правила, что и
для WHERE при SELECT.
При обновлении СУБД проверяет все существующие
CONSTRAINTS на значения атрибутов таблицы. Если
проверки не пройдут, то записи обновлены не будут,
вернется ошибка.
-- изменение 1 атрибута
UPDATE user
SET first_name = 'Петр'
WHERE id = 1;
-- изменение нескольких атрибутов
UPDATE user
SET first_name = 'Иван', last_name = 'XXX'
WHERE first_name = 'Петр';
-- обновление всех записей
UPDATE user
SET birth_date = NULL;

14.

DELETE
Позволяет удалить строки в целевой таблице.
Основной синтакцис:
DELETE FROM <имя таблицы>
WHERE <условие выборки>;
Секция WHERE является необязательной. Если ее не
указывать будут удалены все записи таблицы.
Для WHERE секции валидны все те же правила, что и
для WHERE при SELECT.
При удалении СУБД проверяет все REFERENCIAL
CONSTRAINTS на удаляемые строки. Если на
удаляемые строки есть ссылки, то удаление
проведено не будет, вернется ошибка.
-- удаление строки данных
DELETE FROM user
WHERE id = 2;
-- удаление строк по условию
DELETE FROM user
WHERE birth_date IS NULL;
DELETE FROM user
WHERE last_name in ('Иванов', 'Петров');
-- удаление всех строк таблицы
DELETE FROM user;

15.

Транзакция и TCL
Группа последовательных операций с БД, которая представляет
собой логическую единицу работы с данными.`
Реляционные СУБД обычно поддерживают ACID транзакции:
Atomicy (атомарность)
Транзакция является цельной и не может быть выполнена
частично. Либо все ее операции будут выполнены, либо
не будет выполнено ни одной операции.
Conscistency (целостность)
Каждая транзакция фиксирует только допустимые
результаты, которые не нарушают согласованность
данных в БД. Обеспечивается как через CONSTRAINTS
так и через логику операций самой транзакции.
Isolation (изоляция)
Параллельные транзакции не влияют на выполнение друг
друга. Существуют разные уровни изоляции.
Durability (прочность)
Изменения подтвержденной транзакции сохраняются в
системе и не могут исчезнуть из-за какого-либо сбоя.
/* классический пример
банковская транзакция – перевод денег */
-- начало транзакции
UPDATE account
SET balance = balance – 100
WHERE acc_num = '2345084560834534';
UPDATE account
SET balance = balance + 100
WHERE acc_num = '4541087563832530’;
COMMIT; -- фиксируем транзакцию
-- пример отката изменений транзакции
UPDATE account
SET balance = balance – 100
WHERE acc_num = '2345084560834534’;
ROLLBACK;

16.

СПАСИБО
ЗА ВНИМАНИЕ
Copyright © 2021 Accenture All Rights Reserved