Заметки по MySQL с примерами

Установим бесплатную MySQL, создадим БД, таблицы, заполним данными, сделаем выборку значений.

Скачать MySQL Server 8.0.12.0 для Windows можно по ссылке https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-8.0.12.0.msi

Даны 4 таблицы, с которыми мы будем работать:

01Имя_таблицы: Service

Описание_таблицы: Данные по договорам физических лиц

ID

CLI_ID

Number

ServType_ID

1001

10001

1-ТР

1

1002

10002

1-ТВ

2

1003

10003

2-ТР

1

1004

10001

3-ТР

1

1005

10003

2-ТВ

2

1006

10002

3-ТР

1

1007

10002

4-ТР

1

1008

10001

3-ТВ

2

1009

10001

5-ТР

1

1010

10003

6-ТР

1

где ServType_ID это вид валюты счета, указаный в след. таблице.

02Имя_таблицы:  Service_Type

Описание_таблицы: Типы договоров

ID

Name

1

Текущий Рублевый

2

Текущий Валютный

03Имя_таблицы: Client

Описание_таблицы: Данные клиентов

ID

Name

SecondName

Surname

Resident

10001

Василий

Васильев

Васин

1

10002

Иванов

Иван

Иванович

1

10003

Радик

Рустамович

Обоев

0

04Имя_таблицы:  Account

Описание_таблицы: Данные счетов клиентов

ID

Number

Serv_ID

DateOpen

DateClose

11

408178100000000000001

1001

2017-12-10

 

12

408178400000000000001

1002

2016-02-01

2017-01-02

13

408208100000000000001

1003

2016-01-18

 

14

408178100000000000002

1004

2012-04-08

2013-04-18

15

408209780000000000001

1005

2000-02-18

 

16

408178100000000000003

1006

2015-11-19

 

17

408178100000000000004

1007

2017-08-27

 

18

408179780000000000001

1008

2012-04-15

 

19

408178100000000000005

1009

2014-01-13

2014-01-14

20

408208100000000000002

1010

2014-01-08

 

Создаем БД
CREATE DATABASE base1;
Выбираем созданную БД
USE base1;
Создаем таблицу, на примере одной из таблицы Account, указываем столбцы с типом данных, которые будут заноситься в эти колонки. Остальные по аналогии.
CREATE TABLE account (id int(10), number VARCHAR(30), serv_id VARCHAR(10), dateopen DATE, dateclose DATE);
Заполняем таблицу  Account данными (если строка (запись) в столбце dateclose пустая, то ничего не пишем)
INSERT INTO account (id, number, serv_id, dateopen, dateclose) VALUES
('11','408178100000000000001', '1001', '2017-12-10'),
('12','408178400000000000001', '1002', '2016-02-01', '2017-01-02'),
...;
Если нужно добавить столбец (колонка) в имеющуюся таблицу
ALTER TABLE название_таблицы ADD название_столбца INT(11);
Или удалить столбец
ALTER TABLE название_таблицы DROP column название_столбца;
Если допустили ошибку, то ячейку в столбце можно обновить на новое значение с помощью поиска любого значения в строке. Обновим в таблице Account столбец Number где в строке столбца ID имеется значение 11
UPDATE account SET number = 408178100000000000001 WHERE id = 11;
Теперь посмотрим список таблиц в БД base1
SHOW tables;
Просмотрим таблицу Account
SELECT * FROM account;

Перейдем к заданиям:

1. Прошу предоставить выгрузку всех открытых более 3 лет рублевых счетов не резидентов. В выборку вывести: Фамилию клиента, номер договора, номер счета.
select client.secondname, service.number, account.number from client, service, account
where client.resident=0 and service.servtype_id=1 and account.dateopen>'2015-01-01' and account.dateclose IS NULL and client.id = service.CLI_ID and service.ID = account.serv_ID;

Разберем строки:

select client.secondname, service.number, account.number - здесь указываем столбцы, которые будем выводить -> таблица.столбец, такой синтаксис из-за того, что столбец number существует в разных таблицах;

from client, service, account - указываем из каких таблицы выводить данные;

where client.resident=0 and service.servtype_id=1 and account.dateopen>'2015-01-01' and account.dateclose IS NULL and client.id = service.CLI_ID and service.ID = account.serv_ID; - ставим значение выборки данных, где резидент=0 в строке с id в таблице client, рублевый счет=1 из таблицы Service в столбце servtype_id, из таблицы Account  по условию где дата в столбце dateopen больше 2015 года и нет закрытой даты в столбце dateclose, далее сравниваем отобранные строки из 3-х таблиц между собой: отобранную строку в таблице Client с резидентом, где столбец id сравниваем со столбцом cli_id в таблице Service, затем в Service столбец id сопоставляем с данными из таблицы Account с колонкой serv_id.

2. Необходимо проставить дату открытия счета на день большую по счетам клиентам, открывших валютный счет.

Выведем даты открытия счетов:

select service_type.name, service.number, account.dateopen from service_type, service, account
where service.servtype_id=2 and service_type.id = service.servtype_id and service.ID = account.serv_ID;

Делаем выборку по условию валютного счета из таблицы Service_Type и сравниваем строки с таблицей Service где имеется валютный счет=2, берем id из этой таблицы Service и сравниваем с таблицей Account, чтобы вывести даты открытия счетов.

Увеличим даты открытия счетов на 1 день:

update account, service_type, service set dateopen = ADDDATE(dateopen, INTERVAL 1 DAY)
where service.servtype_id=2 and service_type.id = service.servtype_id and service.ID = account.serv_ID;

И снова выведем даты открытия счетов, которые увеличили на 1 день:

3. Подсчитаем кол-во рублевых и валютных счетов из таблицы Service по отбору в колонке servtype_id:

>

SELECT servtype_id, COUNT(*) FROM service GROUP BY servtype_id;

mr. Mak

Остались вопросы? Пишите на почту admin@hzit.ru

Администратор сайта hzit.ru
Back to top