1122_sql_(2)_(1)

1.

• SQL — это структурированный язык запросов,
созданный для того, чтобы получать из базы
данных необходимую информацию. Если
описать схему работы SQL простыми словами,
то специалист формирует запрос и направляет
его в базу. Та в свою очередь обрабатывает эту
информацию, «понимает», что именно нужно
специалисту, и отправляет ответ.
• Данные хранятся в виде таблиц, они
структурированы и разложены по строкам и
столбцам, чтобы ими легче было оперировать.
Такой способ хранения информации называют
реляционными базами данных (от
англ. relation — «отношения»). Название
указывает на то, что объекты в такой базе
связаны определенными отношениями.

2.

Для чего нужен SQL
• SQL — это не язык программирования, поэтому написать приложение или сайт с его
помощью не получится, но при этом внутренняя работа сайта невозможна без
запросов. Поиск информации в Google — это тоже модель использования SQL.
Пользователь задает параметры, которые его интересуют, и отправляет запрос на
сервер; затем происходит магия и в поисковой выдаче появляются результаты,
соответствующие именно этому запросу.
SQL используют разные виды специалистов:
•Аналитики и продуктовые маркетологи. Знание SQL помогает этим специалистам не зависеть от
программистов, а самостоятельно получать и обрабатывать данные.
•Разработчики и тестировщики. С помощью SQL они могут самостоятельно проектировать базы для
быстрой и надежной работы с данными, улучшать с их помощью сайты и приложения.
•Руководители и менеджеры. SQL позволит специалистам на руководящих постах самостоятельно
обращаться к базам, контролировать работу компании и в реальном времени получать данные о
положении дел.

3.

Запросы
Пользователь → Клиент → Запрос → Система управления → База данных → Таблица с базами данных
Данные для работы с SQL хранятся в таблицах. На пути от пользователя к таблице
находится несколько посредников:
•Клиент — способ введения запроса. В случае с Google, например, клиентом будет
поисковая строка браузера, в которую пользователь вводит сформулированный
запрос.
•Система управления базами данных (СУБД) — комплекс программ, которые
позволяют управлять данными. Эта система помогает таблицам понять, чего хочет
пользователь, а пользователю — что ему отвечают таблицы.
•База данных — система хранения таблиц, в которой они связаны между собой.
База данных сама по себе не умеет манипулировать информацией — это просто
хранилище, где у каждого объекта есть свое место.

4.

Что такое база данных в SQL
Основные понятия реляционной модели:
1. Отношение — это сама таблица, она двумерная
и состоит из столбцов и строк.
2. Атрибут — столбец в таблице, который
содержит один конкретный параметр: название,
тип, дату, стоимость или другую характеристику
3. Домен — это допустимые значения для
каждого атрибута. Например, в столбце «Имя»
или «Название» значения должны представлять
собой набор буквенных символов, но они не могут
начинаться с «ь» или «ъ» и не могут быть
записаны числами.
4. Кортеж (строка или запись) — это табличная строка с порядковым номером, в которой содержится
информация об одном конкретном объекте.
5. Значение — элемент таблицы, который находится на пересечении столбцов и строк.
6. Ключ — это самый важный столбец в таблице, за счет этих значений и происходит взаимодействие в
реляционной базе данных, он связывает таблицы между собой.

5.

6.

Ключи бывают нескольких видов:
•Первичный ключ — идентификатор, такой
как индекс или артикул.
•Потенциальный ключ — другое уникальное
значение, которое может служить
идентификатором.
•Внешний ключ — столбец-ссылка,
используется для объединения двух таблиц,
каждое значение внешнего ключа
обязательно соответствует первичному
ключу в другой таблице.

7.

SQL-операторы
Работать с данными помогают операторы — определенные слова или символы, которые
используются для выполнения конкретной операции — например, для выбора из
множества по конкретному параметру.
Операторы в SQL делятся на несколько групп в соответствии с задачами, которые они
решают.
DDL (Data Definition Language) — операторы определения данных. Они работают с
объектами, то есть с целыми таблицами. Если базу нужно дополнить таблицей с новыми
данными или, наоборот, убрать одну из таблиц с ошибочными данными — используется
этот набор операторов.
•CREATE — создание объекта в базе данных
•ALTER — изменение объекта
•DROP — удаление объекта

8.

DML (Data Manipulation Language) — операторы манипуляции данными. Эти операторы уже
работают с содержимым таблиц — строками, атрибутами и значениями. С их помощью можно
вносить изменения в конкретное значение. Например, заменить поле в колонке «Фамилия» в
строке с данными сотрудницы компании посте того, как она вышла замуж. Или удалить строку с
данными уволенного сотрудника.
•SELECT — выбор данных в соответствии с условием
•INSERT — добавление новых данных
•UPDATE — изменение существующих данных
•DELETE — удаление данных
DCL (Data Control Language) — оператор определения доступа к данным. Он определяет, кто
из пользователей может отправлять запросы к базе, менять объекты и значения. Например,
можно отозвать доступ у сотрудника, перешедшего в другой отдел, а также открыть доступ к
базе новому маркетологу или разработчику.
•GRANT — предоставление доступа к объекту
•REVOKE — отзыв ранее выданного разрешения
•DENY — запрет, который является приоритетным над разрешением

9.

TCL (Transaction Control Language) — язык управления транзакциями. Транзакции — это набор
команд, которые выполняются поочередно. Если все команды выполнены, транзакция
считается успешной, а если где-то произошла ошибка — транзакция откатывается назад,
отменяя все выполненные команды. Наглядный пример такой транзакции — оплата онлайн,
когда банк просит сначала ввести сумму и получателя, затем проверить и подтвердить
операцию, а после ввести одноразовый код. На каждом из этих этапов оплату можно
отменить и транзакция откатится назад.
•BEGIN TRANSACTION — обозначение начала транзакции
•COMMIT TRANSACTION — изменение команд внутри транзакции
•ROLLBACK TRANSACTION — откат транзакции
•SAVE TRANSACTION — указание промежуточной точки сохранения внутри транзакции

10.

Сами по себе таблицы или база данных не способны
выполнять операции, а в СУБД можно создавать
новые таблицы, удалять ненужные данные,
настраивать ключи и обрабатывать запросы.
Основные задачи СУБД:
•поддержка языков баз данных;
•непосредственное управление данными;
•управление буферами оперативной памяти;
•управление транзакциями;
•резервное копирование и восстановление после
сбоев.
СУБД могут быть коммерческими или иметь открытый
код. Системы управления с открытым кодом можно
бесплатно использовать в проектах, а также
дополнять их документацию и совершенствовать
процесс работы с системой. Коммерческие СУБД
имеют платный доступ к полным версиям — как
правило, такие используют крупные корпорации.
https://sqliteonline.com/

11.

Cоздадим табличку с названием "Months", в которой будет три колонки:
•id - иными словами, порядковый номер месяца (целочисленный тип или int)
•name - название месяца (строка или varchar(10) (10 символов - максимальная длина
строки))
•days - число дней в конкретном месяце (целочисленный тип или int)
CREATE TABLE months (id int, name varchar(10), days int);
Также, когда создаются таблицы, принято добавлять так называемый primary key. Это колонка,
значения в которой уникальны. Чаще всего primary key колонкой является id, но в нашем
случае это может быть и name, так как имена всех месяцев уникальны

12.

Ввод данных
Добавим значения в табличку. Сделать это можно с помощью команды INSERT. Есть два
разных способа использовать INSERT:
Первый способ не подразумевает указания названий колонок, а лишь принимает значения в
том порядке, в котором они указаны в таблице.
INSERT INTO months VALUES (1,'January',31);
Первый способ короче второго, однако если в будущем мы захотим добавить
дополнительные колонки, все предыдущие запросы работать не будут. Для решения
данной проблемы следует использовать второй способ. Его суть в том, что перед вводом
данных мы указываем названия колонок
INSERT INTO months (id,name,days) VALUES (2,'February',29);

13.

Select
Данный запрос используется в случае, если нам нужно показать данные в таблице.
Наверное, самым простым примером использования SELECT будет следующий запрос:
SELECT * FROM months
Результатом данного запроса будет таблица со всеми данными в таблице months. Знак
звёздочки (*) означает то, что мы хотим показать все столбцы из таблицы без
исключений. Так как в базе данных обычно больше одной таблицы, нам необходимо
указывать название таблицы, данные из которой мы хотим посмотреть. Сделать это мы
можем, используя ключевое слово FROM.
Когда вам нужны лишь некоторые столбцы из таблицы, то вы можете указать их имена
через запятую вместо звёздочки.
SELECT name, days FROM months

14.

Также иногда нам нужно отсортировать выводимые данные. Для этого мы используем
ORDER BY "название столбца". ORDER BY имеет два модификатора: ASC (по возрастанию)
(по умолчанию) и DESC (по убыванию).
SELECT name, days FROM months ORDER BY days DESC
Where
Теперь мы знаем, как показать только конкретные столбцы, но что если мы хотим
включить в вывод лишь некоторые конкретные строки? Для этого мы используем
WHERE. Данное ключевое слово позволяет нам фильтровать данные по определённому
условию.
В следующем запросе мы выведем только те месяца, в которых 31 день
SELECT id, name, days
FROM months
WHERE days = 31;

15.

Задание №2
Cоздадим табличку с названием "Months", в которой будет три колонки: id(№- тип int – целое
число), name(название месяцев – тип строка(varchar)), days (количество дней в месяце тип int –
целое число)
CREATE TABLE months (id int, name varchar(10), days int, season varchar(10));
Вносим данные в табличку
Вариант внесения №1 (как мы вносили в прошлый раз) – вставляем все значения поэлементно
INSERT INTO months (id,name,days,season) VALUES (1,’January’,31, ’winter’);
INSERT INTO months (id,name,days,season) VALUES (2,'February’,28, ’winter’);
INSERT INTO months (id,name,days,season) VALUES (3,’March’,31, ’spring’);
INSERT INTO months (id,name,days,season) VALUES (4,’April’,30, ’spring’);
INSERT INTO months (id,name,days,season) VALUES (5,’May’,31, ’spring’);
INSERT INTO months (id,name,days,season) VALUES (6,’June’,30, ’summer’);
INSERT INTO months (id,name,days,season) VALUES (7,’July’,31, ’summer’);
INSERT INTO months (id,name,days,season) VALUES (8,’August’,31, ’summer’);
INSERT INTO months (id,name,days,season) VALUES (9,’September’,30, ’autumn’);
INSERT INTO months (id,name,days,season) VALUES (10,’October’,31, ’autumn’);
INSERT INTO months (id,name,days,season) VALUES (11,’November’,30, ’autumn’);
INSERT INTO months (id,name,days,season) VALUES (12,’December’,31’winter’);
Есть способ
попроще

16.

Вариант внесения №2 – вносим все значения сразу через запятую
INSERT INTO months
VALUES
(1,'January',31, 'winter'),
(2,'February',28, 'winter'),
(3,'March',31, 'spring'),
(4,'April',30, 'spring'),
(5,'May',31, 'spring'),
(6, 'June',30, 'summer'),
(7,'July',31, 'summer'),
(8,'August',31, 'summer'),
(9,'September',30, 'autumn'),
(10,'October',31, 'autumn'),
(11,'November',30, 'autumn'),
(12, 'December',31, 'winter');
Вносим в нашу таблицу лишнее значение
INSERT INTO months VALUES (63,’Aboba’, 1, ’???’);

17.

Оператор SQL DELETE и удаление данных с условием
Оператор SQL DELETE предназначен для удаления
данных из таблицы. Он имеет следующий синтаксис:
DELETE FROM ИМЯ_ТАБЛИЦЫ
WHERE УСЛОВИЕ
Можно удалить Абобу следующим образом:
DELETE FROM months
WHERE name='Aboba'
или:
DELETE FROM months
WHERE id=63
Если не указывать условие, из таблицы будут удалены все строки. Кроме того, следует
помнить, что могут быть удалены лишь строки с первичными ключами, на которые не
ссылаются внешние ключи в других таблицах
Можно удалить и несколько строк, если в условии применить оператор сравнения
"больше" или "меньше"
DELETE FROM months
WHERE id>12
Чтобы удалить все данные из таблицы months, достаточно написать следующий запрос:
DELETE FROM months

18.

И/ИЛИ
Условия в WHERE могут быть написаны с использованием логических операторов (AND/OR) и
математические операторы сравнения (=, <, >, <=, >=, <>).
Давайте выведем только те месяцы, число дней в которых равно 31 а id>=6
SELECT *
FROM months
WHERE days = 31 AND id >= 6
IN/BETWEEN/LIKE
Условия в WHERE могут быть записаны с использованием ещё нескольких команд, которыми
являются:
•IN - сравнивает значение в столбце с несколькими возможными значениями и возвращает
true, если значение совпадает хотя бы с одним значением
•BETWEEN - проверяет, находится ли значение в каком-то промежутке
•LIKE - ищет по шаблону

19.

К примеру, мы можем сделать запрос для вывода данных о сезонах:
SELECT * FROM months WHERE season IN (‘spring’,’summer');
Если мы хотим вывести значения месяцев, число дней в которых находится в промежутке от
30 до 31:
SELECT * FROM months WHERE days BETWEEN 30 AND 31
Также, если мы хотим вывести все месяцы, в названии которых есть буква 'R', мы можем
использовать следующую запись:
SELECT * FROM months WHERE name LIKE ‘%r%';
Знак % означает любую последовательность символов (0 символов тоже считается за
последовательность).
Если мы хотим вывести все месяцы, первая буква в названии которых - ‘R’ – ‘r%’

20.

ФУНКЦИИ
В SQL полно встроенных функций для выполнения разных операций. Наиболее часто
используемые:
•COUNT() - возвращает число строк
•SUM() - возвращает сумму всех полей с числовыми значениями в них
•AVG() - возвращает среднее значение среди строк
•MIN()/MAX() - возвращает минимальное/максимальное значение среди строк
Чтобы вывести минимальное количество дней:
SELECT MIN(days) FROM months;
Обратите внимание, что если вы напишете запрос, в котором вам, к примеру, нужно будет
вывести имя и среднее значение чего-либо, то вы получите ошибку на выводе.
SELECT name, AVG(days) FROM months

21.

ПРИСОЕДИНЕНИЕ ТАБЛИЦ
В сложных базах данных чаще всего у нас есть несколько связанных таблиц.
Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы
данных по совпадающему условию. Этот оператор существует только в реляционных базах
данных. Именно благодаря JOIN реляционные базы данных обладают такой мощной
функциональностью, которая позволяет вести не только хранение данных, но и их, хотя бы
простейший, анализ с помощью запросов. Разберём основные нюансы написания SQLзапросов с оператором JOIN, которые являются общими для всех СУБД (систем управления
базами данных). Для соединения двух таблиц оператор SQL JOIN имеет следующий
синтаксис:
SELECT ИМЕНА_СТОЛБЦОВ (1..N)
FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2
ON УСЛОВИЕ
Существует три типа объединения таблиц: INNER, LEFT, RIGHT. Все они связаны с тем, что
некоторым строкам в одной таблице может не найтись соответствующей строки во второй
таблице. В таком случае при использовании "INNER" из результатов запроса будет удалены
все строки, которым не нашлась соответствующая пара в другой таблице. Если же
использовать вместо "INNER" слово "LEFT" или "RIGHT", то будут удалены строки, которые не
нашли совпадение из первой (левой) или второй (правой) таблицы.

22.

Вообще, стандарт SQL выделяет
множество модификаторов JOIN:
•INNER JOIN – внутреннее
объединение таблиц.
•LEFT JOIN или LEFT OUTER JOIN – левое
внешнее объединение таблиц.
•RIGHT JOIN или RIGHT OUTER JOIN – правое
внешнее объединение таблиц.
•FULL JOIN – полное объединение таблиц.
•CROSS JOIN – перекрестное
объединение таблиц.
INNER JOIN (внутреннее соединение)
Запрос с оператором INNER JOIN предназначен
для соединения таблиц и вывода
результирующей таблицы, в которой данные
полностью пересекаются по условию,
указанному после ON.
Шпаргалка по SQL запросу SELECT с использованием JOIN

23.

Приведём пример запроса с объединением данных из двух таблиц. Для этого предположим,
что существует две таблицы. Первая таблица будет иметь название USERS и будет иметь
два столбца: ID и именами пользователей:
CREATE TABLE users (id int, name varchar(10));
INSERT INTO users
VALUES
(1,'Mouse'),
(2,'Cat');
Вторая таблица будет называться FOOD и будет содержать два столбца: USER_ID и NAME. В
этой таблице будет содержаться список любимых блюд пользователей из первой таблицы. В
столбце USER_ID содержится ID пользователя, а в столбце PRODUCT находится название
любимого блюда
CREATE TABLE food (user_id int, product varchar(10));
INSERT INTO food
VALUES
(1,'Cheese'),
(2,'Milk');

24.

Задание №3 INNER JOIN
Условимся что поле ID в таблице USERS и поле USER_ID в таблице FOOD являются первичными
ключами (то есть имеют уникальные значения, которые не повторяются). Теперь попробуем
использовать логику и найти любимое блюдо пользователя "Мышь", используя обе таблицы. Для
этого мы сначала посмотрим в первую таблицу и найдём ID пользователя под именем "Мышь", а затем
найдём название продукта под таким же ID во второй таблице. Объединяющие SQL запросы
работают по такой же логике: нужен столбец, в по которому таблицы могут быть объединены.
SELECT * FROM users INNER JOIN food ON users.id=food.user_id;
При использовании "INNER" из результатов запроса будет удалены все строки, которым не нашлась
соответствующая пара в другой таблице. Если же использовать вместо "INNER" слово "LEFT" или
"RIGHT", то будут удалены строки, которые не нашли совпадение из первой (левой) или второй
(правой) таблицы.
В результате выполнения этого SQL запроса мы получим таблицу с четырьмя столбцами:

25.

Предлагаем модифицировать запрос, потому что нам не нужны все четыре столбца.
Уберём столбцы ID и USER_ID. Для этого вместо * в команде SELECT поставим название
столбцов. Но необходимо сделать это, ставя сначала название таблицы и через точку
название столбца. Чтобы получилось так:
SELECT users.name, food.product
FROM users INNER JOIN food ON users.id=food.user_id;
Теперь результат будет компактнее. И благодаря уменьшенному количеству запрашиваемых
данных, результат будет получаться из базы быстрее:

26.

Для всех, кому интересно повысить свои навыки, оставляю ссылку на курс:
https://stepik.org/course/63054/info