База данных. MySQL (Занятие 10)

SQL - structured query language -
«язык структурированных запросов»
SQL - это стандартный язык доступа и управления базами данных (БД)
Структурированный язык запросов - это стандартный язык доступа к БД, таким как SQL Server, Oracle, MySQL, Sybase и Access.
Программная инструкция для получения данных из БД, называется запросом к базе данных.
Основы MySQL
it-courses.by

Сервер приложений предоставляет возможность
использовать такие ресурсы сервера, как базы данных.
Например, динамическая страница содержит
запрос к БД. Выполнив этот запрос, сервер получит данные
из базы, и поместит их в HTML-код страницы
Если хранить данные в БД, то оформление сайта и конкретные данные будут разделены. Вместо того, чтобы создавать все страницы в виде отдельных HTML-файлов, пишутся только шаблоны. Таким образом можно обновить информацию в одном источнике и продублировать это изменение на всем веб-сайте без редактирования каждой страницы вручную.
Основы MySQL
it-courses.by

Сервер приложений не может непосредственно получить данные из БД, поскольку они используют специфические форматы хранения данных. Попытка получить их похожа на попытку открыть документ Microsoft Word с помощью текстового редактора Notepad. Поэтому для подключения к БД сервер приложений использует посредника - драйвер базы данных.
Драйвер базы данных - программный модуль, с помощью которого устанавливается взаимодействие между сервером приложений и базой данных
Основы MySQL
it-courses.by

После того, как драйвер установит соединение, выполняется запрос к базе, в результате чего формируется набор записей.
Набор записей – данные из одной или нескольких таблиц БД.
Набор записей возвращается серверу приложений, и он использует их для формирования страницы.
Основы MySQL
it-courses.by

SELECT - извлекает данные из таблицы БД
UPDATE - обновляет данные в таблице БД
DELETE - уничтожает данные в таблице БД
INSERT INTO- вставляет новые данные в таблицу БД
Основные MySQL запросы
it-courses.by

Пример запроса
it-courses.by
Persons
SELECT LastName FROM Persons;

Основные команды. Select
it-courses.by
SELECT * FROM Customers;
SELECT CustomerName, City
FROM Customers;

Выбор всех столбцов (SELECT *)
it-courses.by
SELECT * FROM Customers;

Основные команды (conditions)
it-courses.by

Подстановочные. Wildcards
it-courses.by

Условия поиска
it-courses.by
Сравнение.
Диапазон.
Роли уровня базы данных.
Соответствие шаблону.
Пустое значение.

Сравнительный Тест (=, <>, <, <=, >, >=)
it-courses.by

Сравнительный Тест (=, <>, <, <=, >, >=)
it-courses.by
SELECT * FROM Customers WHERE PostalCode = 44000 Limit 5;

Основные команды. Where
it-courses.by
SELECT *
FROM Customers
WHERE Country = 'Mexico';

Примеры запроса
it-courses.by

Основные команды. And & Or
it-courses.by
SELECT * FROM Customers
WHERE Country = 'Germany‘ AND City = 'Berlin';

Составные условия поиска (AND, OR, and NOT)
it-courses.by

Составные условия поиска (AND, OR, and NOT)
it-courses.by
SELECT * FROM Customers where Country = 'Sweden' or Country = 'Germany' limit 6;
SELECT * FROM Customers where Country = 'Sweden' and Country = 'Germany' limit 6;

Основные команды. And & Or
it-courses.by
SELECT * FROM Customers
WHERE City = 'Berlin‘ OR City = ‘London';

Основные команды. And & Or
it-courses.by
SELECT * FROM Customers
WHERE Country = ‘Mexico'
AND (City = ‘Mexico' OR City = ‘Hanalulu');

Основные команды. Order by
it-courses.by
The ORDER BY keyword is used to sort the result-set by one or more columns, it’s ascending by default.
If you want descending order use DESC keyword.
SELECT * FROM Customers
ORDER BY Country DESC;
.........

Сортировка результатов запроса
it-courses.by

Сортировка результатов запроса
it-courses.by
SELECT Distinct Country FROM Customers order by Country limit 6;
SELECT Distinct Country FROM Customers order by Country desc limit 5;

Основные команды. Insert into
it-courses.by
INSERT INTO Customers
(CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal‘ ,'Tom Erichsen‘ ,'Skagen 21','Stavanger','4006','Norway');

Основные команды. Update
it-courses.by
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Hamburg'
WHERE CustomerName = 'Alfreds Futterkiste';
If you skip the WHERE clause all rows would be updated!
Alfred Schmidt
Hamburg

Основные команды. Delete
it-courses.by
DELETE FROM Customers
WHERE CustomerName = 'Alfreds Fkiste';
DELETE FROM Customers;
All rows will be deleted!

Основные команды. Wildcards
it-courses.by
SELECT * FROM Customers
WHERE City LIKE 'ber%';
SELECT * FROM Customers
WHERE City LIKE '_erlin';
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

Тестирование Диапазона (BETWEEN)
it-courses.by

Основные команды. Between
it-courses.by
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';

Тестирование Диапазона (BETWEEN)
it-courses.by
SELECT * FROM Customers WHERE PostalCode BETWEEN 44000 and 78000 limit 5;

Тестирование соответствия шаблону (LIKE)
it-courses.by
Шаблоны знаков:
% - заменяет ноль или более символов;
_ - заменяет один символ;
[charlist] - устанавливает диапазоны символов для соответствия;
[^ charlist] или [! charlist] - устанавливает соответствие только символу, не указанному в скобках.

Тестирование соответствия шаблону (LIKE)
it-courses.by
SELECT * FROM Customers WHERE PostalCode LIKE '440%' limit 5;

Недостающие данные (нулевые значения)
it-courses.by

Тестирование нулевого значения
it-courses.by

Тестирование нулевого значения
it-courses.by
SELECT * FROM employees where building is null;

Тестирование роли уровня базы данных (IN)
it-courses.by

Тестирование роли уровня базы данных (IN)
it-courses.by
SELECT * FROM Customers WHERE PostalCode IN (12209, 44000, 78000) limit 5;

Основные команды. Distinct
it-courses.by
Distinct – команда для вывода уникальных имен (без повторов)
SELECT distinct Country
FROM Customers

Повторяющиеся строки (DISTINCT)
it-courses.by

Повторяющиеся строки (DISTINCT)
it-courses.by
SELECT City FROM Customers limit 4;
SELECT distinct City FROM Customers limit 4;

Основные команды. Drop
it-courses.by
Таблицы и базы данных можно удалить с помощью этой команды.
DROP statement.
DROP TABLE table_name;
DROP DATABASE database_name;

SQL JOIN
Выбирает значения из нескольких таблиц одновременно.
Наиболее часто используемых 4:
Inner Join
Left join
Right Join
Full Outer Join
Комплексные Select

Объединения (JOINS)
it-courses.by

JOIN это горизонтальное объединение
it-courses.by

SQL INNER JOIN – Вернет все записи из всех таблиц, где есть пересечение.
SELECT Product.ProductID, Orders.Quantity, Orders.OrderDate
FROM Orders
INNER JOIN Product
ON Product.ProductID=Orders.OrderID;
Комплексные Select

SQL FULL OUTER JOIN – Вернет все записи из обеих таблицы совместив записи, где есть пересечение.
SELECT Product.ProductID, Orders.Quantity, Orders.OrderDate
FROM Orders
FULL OUTER JOIN Product
ON Product.ProductID=Orders.OrderID;
Комплексные Select

Внутреннее vs Левое Объединение
it-courses.by

SQL LEFT JOIN – Вернет все записи из левой таблицы и записи из правой, где есть пересечение.
SELECT Product.ProductID, Orders.Quantity, Orders.OrderDate
FROM Orders
LEFT JOIN Product
ON Product.ProductID=Orders.OrderID;
Комплексные Select

SQL RIGHT JOIN – Вернет все записи из правой таблицы и записи из левой, где есть пересечение.
SELECT Product.ProductID, Orders.Quantity, Orders.OrderDate
FROM Orders
RIGHT JOIN Product
ON Product.ProductID=Orders.OrderID;
Комплексные Select

Объединение
it-courses.by

Нормализация баз данных
it-courses.by
это процесс организации данных в базе данных, включающий создание таблиц и установление отношений между ними в соответствии с правилами, которые обеспечивают защиту данных и делают базу данных более гибкой, устраняя избыточность и несогласованные зависимости.

Денормализация
it-courses.by
Улучшение производительности сложных запросов, которые выполняются долго за счет добавления необходимой избыточности данных;
Упрощение выполнения запросов на клиенте (вплоть до 1-й таблицы) ;
Улучшение эффективности работы клиента с базой;
Упрощает структуру для конечного пользователя.

Реляционная база данных
it-courses.by
Доступ и изменение данных в реляционной базе данных осуществляется при помощи выполнения команд на структурированном языке запросов (SQL).

SQL как средство общения с СУБД
it-courses.by

Структура
it-courses.by

Запросы
it-courses.by

Названия таблиц
it-courses.by

Названия колонок
it-courses.by

Используйте любую интегрированную среду разработки (IDE) для работы с SQL
it-courses.by

Встроенные функции
it-courses.by
Агрегатные функции выполняют вычисление на наборе значений в столбце и возвращают одиночное значение.
Скалярные функции SQL возвращают одно значение, основанное на входном значении.

Встроенные функции
it-courses.by
Полезные агрегатные функции:
AVG() - Возвращает среднее значение
COUNT() - Возвращает количество строк
MAX() - Возвращает наибольшее значение
MIN() - Возвращает наименьшее значение
SUM() - Возвращает сумму
Скалярные функции:
CHAR_LENGTH() - Возвращает длину текстового поля в символах.
ROUND() - Возвращает числовое значение, округленное до указанной длины или точности.
NOW() - Возвращает текущее время и дату.
FORMAT() - Форматирование для определенного отображения поля

Агрегатные функции
it-courses.by
SELECT count(*) FROM Employees;

Агрегатные функции (группировки и фильтрация)
it-courses.by

Агрегатные функции (GROUP BY Clause)
it-courses.by
SELECT Country, count(Country) FROM Customers Group by Country;
Оператор GROUP BY используется вместе с агрегатными функциями для группировки результата по признакам одного или более столбцов.

Агрегатные функции (HAVING Clause)
it-courses.by
Оператор HAVING был добавлен в SQL потому, что оператор WHERE не может быть использован вместе с агрегатной функцией.
SELECT Country, count(Country) FROM Customers Group by Country HAVING count(Country) > 8;