База данных. SQL. Lesson 10

1.

Занятие 10
База данных. SQL

2.

Структура Клиент-сервер
it-courses.by

3.

Примеры систем, работа которых
невозможна без баз данных
Банковские системы
Системы резервирования билетов
Системы автоматизации управления
Системы учета, биллинговые системы
Интернет-магазины
И т.д.
it-courses.by

4.

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

5.

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

6.

Реляционная БД
Реляционная БД –
это набор данных с предопределенными
связями между ними.
БД = набор таблиц
Каждая таблица = информация об объектах, представленных в БД
Каждый столбец (поле) = определенный тип данных
Каждая ячейка – значение атрибута
Каждая строка таблицы = набор связанных значений, относящихся к
одному объекту (сущности)
it-courses.by

7.

Реляционная БД – набор таблиц
it-courses.by

8.

Реляционная БД. Пример таблицы
it-courses.by

9.

Реляционная БД. Типы данных
• INTEGER- данные из целых чисел;
• FLOAT — данные из дробных чисел, так
называемые данные с плавающей точкой;
• CHAR, VARCHAR — текстовые типы данных
(символьные);
• LOGICAL — логический тип данных (да/нет);
• DATE/TIME — временные данные.
it-courses.by

10.

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

11.

Первичный и внешний ключи
it-courses.by

12.

Первичный и внешний ключи
it-courses.by

13.

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

14.

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

15.

Подключаемся к базе
MYSQL -u user –p – подключаемся к СУБД;
SHOW DATABASES – просмотр доступных баз данных;
SHOW TABLES – просмотр доступных таблиц в базе;
USE database_name – переключение на нужную базу.
it-courses.by

16.

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

17.

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

18.

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

19.

Пример запроса
Persons
LastName
Name
Adress
City
Polyakov
Denis
Lyibyanka, 25
Moscow
Ivanov
Mihail
Sadovaya, 17
Kazan'
Popandopulo
Ermak
Hutorskay, 4
Kiev
SELECT LastName FROM Persons;
LastName
Polyakov
Ivanov
Popandopulo
it-courses.by

20.

Основные операторы (operators)
Operator
Description
=
Equal
<> / !=
Not equal
> / >=
Grater / grater or equal
< / <=
Less / less or equal
Like
Search for a pattern (‘%’ and ‘_’)
in ()
To specify multiply values
Not
Inverts the value for Like, In
And
For mandatory creation union (1 and 2)
Or
For optional creation union (1 or 2)
Where
Specify certain value
Between
Selects values within a range (numbers, text, or dates)
it-courses.by

21.

Операторы сравнения (=, <>, <, <=, >, >=)
SELECT * FROM Customers
WHERE PostalCode = 44000
Limit 5;
it-courses.by

22.

Подстановочные. Wildcards
Wildcard
Description
*
All
%
Replace any number of symbols (including 0)
_
Replace 1 symbol
[abc]
With any of symbol from the amount -> A or B or C
it-courses.by

23.

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

24.

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

25.

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

26.

Основные команды. Like
SELECT * FROM Customers
WHERE PostalCode LIKE '440%'
limit 5;
it-courses.by

27.

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

28.

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

29.

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

30.

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

31.

Основные команды. Order by
Команда ORDER BY используется для сортировки результатов
запроса по одной или нескольким колонкам, по умолчанию
сортировка происходит по возрастанию числовых значений и в
алфавитном порядке строковых.
Если необходимо получить обратный порядок, используйте DESC
SELECT * FROM Customers
ORDER BY Country DESC;
.........
it-courses.by

32.

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

33.

Основные команды. Update
UPDATE Customers
SET CustomerName = 'Alfred Schmidt', City = 'Hamburg'
WHERE CustomerName = 'Alfreds Futterkiste’;
Если пропустить WHERE, все строки будут обновлены!
Alfred Schmidt
it-courses.by
Hamburg

34.

Основные команды. Delete
DELETE FROM Customers
DELETE FROM Customers;
WHERE CustomerName = 'Alfreds Fkiste'; все строки будут удалены!
it-courses.by

35.

Недостающие данные. Null
it-courses.by

36.

Тестирование нулевого значения
SELECT * FROM employees
where building is null
Для Null всегда используется IS вместо = или Like
it-courses.by

37.

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

38.

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

39.

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

40.

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

41.

Диапазон данных. Оператор IN
SELECT * FROM Customers
WHERE PostalCode IN (12209, 44000, 78000)
limit 5;
it-courses.by

42.

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

43.

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

44.

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

45.

Inner JOIN
Внутреннее объединение INNER JOIN
(синоним JOIN, ключевое слово INNER можно опустить)
Выбираются только совпадающие данные из объединяемых таблиц.
it-courses.by

46.

Inner JOIN
Есть 2 таблицы - Customers и Orders:
У обеих таблиц есть одинаковое поле Customer ID
it-courses.by

47.

Inner JOIN
Если нужно сделать выборку сразу из обеих таблиц, то их можно
объединить по общему полю:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
it-courses.by

48.

Left and Right JOIN
Чтобы получить данные, которые подходят по условию частично,
необходимо использовать
Внешнее объединение - OUTER JOIN
Такое объединение вернет данные из обеих таблиц
(совпадающие по условию объединения) ПЛЮС дополнит выборку
оставшимися данными из внешней таблицы,
которые по условию не подходят,
заполнив недостающие данные значением NULL
it-courses.by

49.

Left JOIN
Те же 2 таблицы - Customers и Orders (Customer ID)
Left table
Right table
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
В отличие от Join, Left Join выберет не только пересечение двух
таблиц, но и все значения для левой таблицы
it-courses.by

50.

Left JOIN
Right table Orders
Left table Customers
it-courses.by

51.

Right JOIN
Есть 2 таблицы – Orders и Employees:
У обеих таблиц есть одинаковое поле EmployeeID
it-courses.by

52.

Right JOIN
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Left table
it-courses.by
Right table

53.

UNION
Оператор UNION используется для объединения результата
2-х и более SELECT запросов
Тип данных в колонках, по которым делается select для Union
должен совпадать (текст, число...)
Union объединяет только уникальные (distinct) данные
it-courses.by

54.

UNION
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City
В Customers 91 запись. В Suppliers 29.
Результатом будет табличка
из 1 колонки и 94 записями.
Почему?
it-courses.by

55.

UNION All
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City
При добавлении
всего лишь одного слова ALL
в выражение,
количество строк
в итоговой табличке будет 120
it-courses.by

56.

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

57.

Агрегатные функции
AVG() - Cреднее значение
COUNT() - Количество строк
MAX() - Наибольшее значение
MIN() - Наименьшее значение
SUM() - Сумму
it-courses.by

58.

Агрегатные функции. Min
SELECT MIN(Price) AS SmallestPrice
FROM Products;
Выбрали и Переименовали столбец
it-courses.by

59.

Агрегатные функции. Count
SELECT COUNT(ProductID)
FROM Products
it-courses.by

60.

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

61.

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

62.

Скалярные функции
LENGTH() - Возвращает длину текстового поля в символах
ROUND() - Возвращает числовое значение, округленное до указанной
длины или точности
NOW() - Возвращает текущее время и дату
FORMAT() - Форматирование для определенного отображения поля
it-courses.by

63.

Скалярные функции. Length
SELECT CustomerName,
LENGTH(CustomerName) AS LengthOfName
FROM Customers;
it-courses.by

64.

Псевдонимы (Aliases)
В предыдущих слайдах вы заметили что для некоторых
Имен столбцов даются другие (свои) названия.
Они не меняют название столбца в таблице,
а выводятся только для пользователя.
Это Временные имена или Aliases
SELECT CustomerID AS ID,
CustomerName AS Customer
FROM Customers;
it-courses.by

65.

Практика
it-courses.by