Sql бд: Обзор основных SQL запросов

Содержание

SQL запросы быстро. Часть 1 / Хабр

Введение

Язык SQL очень прочно влился в жизнь бизнес-аналитиков и требования к кандидатам благодаря простоте, удобству и распространенности. Из собственного опыта могу сказать, что наиболее часто SQL используется для формирования выгрузок, витрин (с последующим построением отчетов на основе этих витрин) и администрирования баз данных. И поскольку повседневная работа аналитика неизбежно связана с выгрузками данных и витринами, навык написания SQL запросов может стать фактором, из-за которого кандидат или получит преимущество, или будет отсеян. Печальная новость в том, что не каждый может рассчитывать получить его на студенческой скамье. Хорошая новость в том, что в изучении SQL нет ничего сложного, это быстро, а синтаксис запросов прост и понятен. Особенно это касается тех, кому уже доводилось сталкиваться с более сложными языками.

Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join’ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.

Практика

Введение в синтаксис будет рассмотрено на примере открытой базы данных, предназначенной специально для практики SQL. Чтобы твое обучение прошло максимально эффективно, открой ссылку ниже в новой вкладке и сразу запускай приведенные примеры, это позволит тебе лучше закрепить материал и самостоятельно поработать с синтаксисом.

Кликнуть здесь

После перехода по ссылке можно будет увидеть сам редактор запросов и вывод данных в центральной части экрана, список таблиц базы данных находится в правой части.

Структура sql-запросов

Общая структура запроса выглядит следующим образом:
SELECT ('столбцы или * для выбора всех столбцов; обязательно') FROM ('таблица; обязательно') WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно') GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно') HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно') ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')

Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS’ом.
SELECT, FROM

SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.

Выбрать все (обозначается как *) из таблицы Customers:

SELECT * FROM Customers

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:
SELECT CustomerID, CustomerName FROM Customers
WHERE

WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

select * from Customers
WHERE City = 'London'

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):
select * from Customers where City IN ('London', 'Berlin')
select * from Customers
where City NOT IN ('Madrid', 'Berlin','Bern')

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:
select * from Customers
where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15
select * from Customers
where City in ('London', 'Berlin') OR CustomerID > 4

GROUP BY

GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).

При использовании GROUP BY обязательно:

  1. перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
  2. агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.

Группировка количества клиентов по городу:
select City, count(CustomerID) from Customers
GROUP BY City

Группировка количества клиентов по стране и городу:
select Country, City, count(CustomerID) from Customers
GROUP BY Country, City

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:
select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails GROUP BY ProductID

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:

select City, count(CustomerID) from Customers
WHERE Country = 'Germany'
GROUP BY City

Переименование столбца с агрегацией с помощью оператора AS. По умолчанию название столбца с агрегацией равно примененной агрегатной функции, что далее может быть не очень удобно для восприятия.
select City, count(CustomerID) AS Number_of_clients from Customers
group by City

HAVING

HAVING — необязательный элемент запроса, который отвечает за фильтрацию на уровне сгруппированных данных (по сути, WHERE, но только на уровень выше).

Фильтрация агрегированной таблицы с количеством клиентов по городам, в данном случае оставляем в выгрузке только те города, в которых не менее 5 клиентов:


select City, count(CustomerID) from Customers
group by City
HAVING count(CustomerID) >= 5 

В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:


select City, count(CustomerID) as number_of_clients from Customers
group by City
HAVING number_of_clients >= 5

Пример запроса, содержащего WHERE и HAVING. В данном запросе сначала фильтруется исходная таблица по пользователям, рассчитывается количество клиентов по городам и остаются только те города, где количество клиентов не менее 5:

select City, count(CustomerID) as number_of_clients from Customers
WHERE CustomerName not in ('Around the Horn','Drachenblut Delikatessend')
group by City
HAVING number_of_clients >= 5

ORDER BY

ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.

Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:


select * from Customers
ORDER BY City

Осуществлять сортировку можно и по нескольким столбцам, в этом случае сортировка происходит по порядку указанных столбцов:

select * from Customers
ORDER BY Country, City

По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:

select * from Customers
order by CustomerID DESC

Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:
select * from Customers order by Country DESC, City

JOIN

JOIN — необязательный элемент, используется для объединения таблиц по ключу, который присутствует в обоих таблицах. Перед ключом ставится оператор ON.

Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:

select * from Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Нередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,
select * from Orders join Customers on Orders.CustomerID = Customers.CustomerID where Customers.CustomerID >10

Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:

Другие типы JOIN’ов можно увидеть на замечательной картинке ниже:


В следующей части подробнее поговорим о типах JOIN’ов и вложенных запросах.

При возникновении вопросов/пожеланий, всегда прошу обращаться!

Исследование БД и СУБД с помощью T-SQL / Хабр

Предисловие


Приветствую вновь тебя, уважаемый читатель Хабра!

Когда свои реализованные идеи, опыт, а также всю ту информацию, что не дает покоя, оформляешь в публикации, рано или поздно приходит логическая точка всему ранее написанному потоку информации. Эта статья будет отличаться от всех ранее опубликованных мною своей нестрогостью и более свободным стилем изложения текста, а также она завершит изложение всего моего накопленного опыта по MS SQL Server.

Данная статья является дополнением к статье Исследуем базы данных с помощью T-SQL, а также вкратце рассказывает о созданной базе данных по администрированию SRV и о проектах-утилитах, которые предназначены помочь в работе DBA MS SQL Server.

Некоторые полезные представления для исследования БД и СУБД в целом


Для определения размера встроенных таблиц, можно создать следующее представление [inf].[vInnerTableSize]:Реализация представления [inf].[vInnerTableSize]
USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [inf].[vInnerTableSize] as
--размеры встроенных таблиц
select object_name(p.[object_id]) as [Name]
	 , SUM(a.[total_pages]) as TotalPages
	 , SUM(p.[rows]) as CountRows
	 , cast(SUM(a.[total_pages]) * 8192/1024. as decimal(18, 2)) as TotalSizeKB
from sys.partitions as p
inner join sys.allocation_units as a on p.[partition_id]=a.[container_id]
left outer join sys.internal_tables as it on p.[object_id]=it.[object_id]
where OBJECTPROPERTY(p.[object_id], N'IsUserTable')=0
group by object_name(p.[object_id])
--order by p.[rows] desc;
GO

С помощью данного представления можно контролировать рост системных таблиц во избежании их чрезмерного роста.

С помощью системных представлений [sys].[sql_logins] и [sys].[syslogins] можно получить логины для скульных и виндовых входов.

Также интересны следующие системные представления для задач Агента экземпляра MS SQL Server:

1) [msdb].[dbo].[sysjobactivity] — активные задачи
2) [msdb].[dbo].[sysjobhistory] — история выполнения заданий
3) [msdb].[dbo].[sysjobs_view] и [msdb].[dbo].[sysjobservers] — задания
4) [msdb].[dbo].[sysjobschedules] — расписания заданий
5) [msdb].[dbo].[sysjobsteps] — шаги заданий
6) [msdb].[dbo].[sysjobstepslogs] — логирование шагов заданий

Также для того, чтобы знать для каких расписаний назначено более одной задачи, достаточно создать следующее представление [inf].[vScheduleMultiJobs]:

Реализация представления [inf].[vScheduleMultiJobs]
USE [БД для администрирования]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [inf].[vScheduleMultiJobs] as
with sh as(
  SELECT schedule_id
  FROM [inf].[vJobSchedules]
  group by schedule_id
  having count(*)>1
)
select *
from msdb.dbo.sysschedules as s
where exists(select top(1) 1 from sh where sh.schedule_id=s.schedule_id)
GO

Данное представление позволит избежать необдуманное изменение в расписании для одной задачи, чтобы не вызвать изменения для другой.

Чтобы получить информацию об описании объектов БД, можно воспользоваться расширенными свойствами (системное представление [sys].[extended_properties]). Для удобства можно создать следующие представления:
1) [inf].[vObjectDescription]:

Реализация представления [inf].[vObjectDescription]
USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [inf].[vObjectDescription] as
select
SCHEMA_NAME(obj.[schema_id]) as SchemaName
,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName
,obj.[type] as [Type]
,obj.[type_desc] as [TypeDesc]
,ep.[value] as ObjectDescription
from sys.objects as obj
left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id]
											 and ep.[minor_id]=0
											 and ep.[name]='MS_Description'
where obj.[is_ms_shipped]=0
and obj.[parent_object_id]=0
GO

2) Описания для объектов, у которых есть родители — с помощью представления [inf].[vObjectInParentDescription]:Реализация представления [inf].[vObjectInParentDescription]
USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [inf].[vObjectInParentDescription] as
select
SCHEMA_NAME(obj.[schema_id]) as SchemaName
,QUOTENAME(object_schema_name(obj.[parent_object_id]))+'.'+quotename(object_name(obj.[parent_object_id])) as ParentObjectName
,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName
,obj.[type] as [Type]
,obj.[type_desc] as [TypeDesc]
,ep.[value] as ObjectDescription
from sys.all_objects as obj
left outer join sys.extended_properties as ep on obj.[parent_object_id]=ep.[major_id]
											 and ep.[minor_id]=obj.[object_id]
											 and ep.[name]='MS_Description'
where obj.[is_ms_shipped]=0
and obj.[parent_object_id]<>0
GO

3) Описания для параметров — с помощью представления [inf].[vParameterDescription]:Реализация представления [inf].[vParameterDescription]
USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [inf].[vParameterDescription] as
select
SCHEMA_NAME(obj.[schema_id]) as SchemaName
,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(object_name(obj.[object_id])) as ParentObjectName
,p.[name] as ParameterName
,obj.[type] as [Type]
,obj.[type_desc] as [TypeDesc]
,ep.[value] as ParameterDescription
from sys.parameters as p
inner join sys.objects as obj on p.[object_id]=obj.[object_id]
left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id]
											 and ep.[minor_id]=p.[parameter_id]
											 and ep.[name]='MS_Description'
where obj.[is_ms_shipped]=0
GO

4) Описания столбцов таблиц — с помощью представления [inf].[vColumnTableDescription]:Реализация представления [inf].[vColumnTableDescription]
USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [inf].[vColumnTableDescription] as
select 
SCHEMA_NAME(t.schema_id) as SchemaName
,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName
,c.[name] as ColumnName
,ep.[value] as ColumnDescription
from sys.tables as t
inner join sys.columns as c on c.[object_id]=t.[object_id]
left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id]
											 and ep.[minor_id]=c.[column_id]
											 and ep.[name]='MS_Description'
where t.[is_ms_shipped]=0;
GO

5) Описания столбцов представлений — с помощью представления [inf].[vColumnViewDescription]:Реализация представления [inf].[vColumnViewDescription]
USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [inf].[vColumnViewDescription] as
select 
SCHEMA_NAME(t.schema_id) as SchemaName
,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName
,c.[name] as ColumnName
,ep.[value] as ColumnDescription
from sys.views as t
inner join sys.columns as c on c.[object_id]=t.[object_id]
left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id]
											 and ep.[minor_id]=c.[column_id]
											 and ep.[name]='MS_Description'
where t.[is_ms_shipped]=0;
GO

6) Описания схем БД — с помощью представления [inf].[vSchemaDescription]:Реализация представления [inf].[vSchemaDescription]
USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [inf].[vSchemaDescription] as
select
SCHEMA_NAME(t.schema_id) as SchemaName
--,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName
,ep.[value] as SchemaDescription
from sys.schemas as t
left outer join sys.extended_properties as ep on t.[schema_id]=ep.[major_id]
											 and ep.[minor_id]=0
											 and ep.[name]='MS_Description'
GO

Чтобы добавить или отредактировать расширенные свойства для документирования объектов БД, лучше пользоваться сторонними утилитами (например, я использую dbForge).
Однако, также это можно сделать следующими запросами:Примеры создания описаний для объектов БД
--здесь создаем описание для параметра @ObjectID ф-ии dbo.GetPlansObject
--аналогично делается и для параметров хранимых процедур
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = N'Идентификатор объекта', 
@level0type = N'SCHEMA', 
@level0name = N'dbo', 
@level1type = N'FUNCTION', 
@level1name = N'GetPlansObject', 
@level2type = N'PARAMETER', 
@level2name = N'@ObjectID';

--здесь создаем описание для ф-ии dbo.GetPlansObject
--аналогично делается и для хранимых процедур, триггеров
EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
@value=N'Возвращает все планы заданного объекта', 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'FUNCTION',
@level1name=N'GetPlansObject';

--здесь создаем описание для представления inf.vColumnTableDescription
--аналогично делается и для таблиц
EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
@value=N'Описание столбцов таблиц', 
@level0type=N'SCHEMA',
@level0name=N'inf', 
@level1type=N'VIEW',
@level1name=N'vColumnTableDescription';

--здесь создаем описание для столбца TEST_GUID таблицы dbo.TABLE
--аналогично делается и для столбца представления
EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
@value=N'Идентификатор записи (глобальный)', 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'TEST', 
@level2type=N'COLUMN',
@level2name=N'TEST_GUID';

--здесь создаем описание для схемы rep
EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
@value=N'Объекты схемы rep содержат информацию для отчетов' , 
@level0type=N'SCHEMA',
@level0name=N'rep';

--здесь создается описание для БД
EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
@value=N'База данных для администрирования
Версия для MS SQL Server 2016-2017 (также полностью или частично поддерживается MS SQL Server 2012-2014).
Поддержка всех версий до версии MS SQL Server 2012 может быть не на достаточном уровне для использования в производственной среде.
Необходимые типовые задания см. в ХП inf.InfoAgentJobs.';

Для того, чтобы изменить или удалить описание, достаточно воспользоваться хранимыми процедурами sp_updateextendedproperty и sp_dropextendedproperty соответственно.

Также будут полезны следующие системные представления в рамках исследования всей СУБД:

1) [sys].[dm_os_performance_counters] — значения счетчиков производительности

2) [sys].[dm_os_schedulers] — планировщики заданий

3) [sys].[configurations] — сведения о конфигурации

4) чтобы сопоставить идентификаторы сеанса с идентификаторами потока Windows, можно создать следующее представление [inf].[vSessionThreadOS]:

Реализация представления [inf].[vSessionThreadOS]
USE [БД для администрирования]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [inf].[vSessionThreadOS] as
/*
		 Представление возвращает информацию, связывающую идентификатор сеанса с идентификатором потока Windows.
		 За производительностью потока можно наблюдать в системном мониторе Windows.
		 Запрос не возвращает идентификаторы сеансов, которые в настоящий момент находятся в ждущем режиме.
*/
SELECT STasks.session_id, SThreads.os_thread_id
    FROM sys.dm_os_tasks AS STasks
    INNER JOIN sys.dm_os_threads AS SThreads
        ON STasks.worker_address = SThreads.worker_address
    WHERE STasks.session_id IS NOT NULL;
GO

5) чтобы узнать о проблемах с количеством файлов БД tempdb, можно создать следующее представление [inf].[vServerProblemInCountFilesTempDB]:Реализация представления [inf].[vServerProblemInCountFilesTempDB]
USE [БД для администрирования]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [inf].[vServerProblemInCountFilesTempDB]
as
/*
	http://sqlcom.ru/dba-tools/tempdb-in-sql-server-2016/
	Можно узнать есть ли у проблемы с количеством файлов tempdb.
	Этим запросом пытаемся найти latch на системные страницы PFS, GAM, SGAM в базе данных tempdb.
	Если запрос ничего не возвращает или возвращает строки только с «Is Not PFS, GAM, or SGAM page», то скорее всего текущая нагрузка не требует увеличения файлов tempdb
*/
Select session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
		ResourceType = Case
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
			When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
			When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
			Else 'Is Not PFS, GAM, or SGAM page'
			End
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%' 
GO

6) чтобы узнать о проблемах с временем записи данных в БД tempdb, можно создать следующее представление [srv].[vStatisticsIOInTempDB]:Реализация представления [srv].[vStatisticsIOInTempDB]
USE [БД для администрирования]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [srv].[vStatisticsIOInTempDB] as
/*
	Если время записи данных (avg_write_stall_ms) меньше 5 мс, то это значит хороший уровень производительности. 
	Между 5 и 10 мс  — приемлемый уровень. 
	Более 10 мс — низкая производительность, необходимо сделать детальный анализ, имеются проблемы с вводом-выводом для временной базы данных
	https://minyurov.com/2016/07/24/mssql-tempdb-opt/
*/
SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files 
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'
GO

7) для удобства вывода информации о последних сделанных резервных копиях всех БД, можно создать следующее представление [inf].[vServerLastBackupDB]:Реализация представления [inf].[vServerLastBackupDB]
USE [БД для администрирования]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [inf].[vServerLastBackupDB] as
with backup_cte as
(
    select
        bs.[database_name],
        backup_type =
            case bs.[type]
                when 'D' then 'database'
                when 'L' then 'log'
                when 'I' then 'differential'
                else 'other'
            end,
        bs.[first_lsn],
		bs.[last_lsn],
		bs.[backup_start_date],
		bs.[backup_finish_date],
		cast(bs.[backup_size] as decimal(18,3))/1024/1024 as BackupSizeMb,
        rownum = 
            row_number() over
            (
                partition by bs.[database_name], type 
                order by bs.[backup_finish_date] desc
            ),
		LogicalDeviceName = bmf.[logical_device_name],
		PhysicalDeviceName = bmf.[physical_device_name],
		bs.[server_name],
		bs.[user_name]
    FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf 
        ON [bs].[media_set_id] = [bmf].[media_set_id]
)
select
    [server_name] as [ServerName],
	[database_name] as [DBName],
	[user_name] as [USerName],
    [backup_type] as [BackupType],
	[backup_start_date] as [BackupStartDate],
    [backup_finish_date] as [BackupFinishDate],
	[BackupSizeMb], --размер без сжатия
	[LogicalDeviceName],
	[PhysicalDeviceName],
	[first_lsn] as [FirstLSN],
	[last_lsn] as [LastLSN]
from backup_cte
where rownum = 1;
GO

8) аналогичное представление [inf].[vServerBackupDB] можно создать для получения информации о всех резервных копиях:
Реализация представления [inf].[vServerBackupDB]
USE [БД для администрирования]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [inf].[vServerBackupDB] as
with backup_cte as
(
    select
        bs.[database_name],
        backup_type =
            case bs.[type]
                when 'D' then 'database'
                when 'L' then 'log'
                when 'I' then 'differential'
                else 'other'
            end,
        bs.[first_lsn],
		bs.[last_lsn],
		bs.[backup_start_date],
		bs.[backup_finish_date],
		cast(bs.[backup_size] as decimal(18,3))/1024/1024 as BackupSizeMb,
		LogicalDeviceName = bmf.[logical_device_name],
		PhysicalDeviceName = bmf.[physical_device_name],
		bs.[server_name],
		bs.[user_name]
    FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf 
        ON [bs].[media_set_id] = [bmf].[media_set_id]
)
select
    [server_name] as [ServerName],
	[database_name] as [DBName],
	[user_name] as [USerName],
    [backup_type] as [BackupType],
	[backup_start_date] as [BackupStartDate],
    [backup_finish_date] as [BackupFinishDate],
	[BackupSizeMb], --размер без сжатия
	[LogicalDeviceName],
	[PhysicalDeviceName],
	[first_lsn] as [FirstLSN],
	[last_lsn] as [LastLSN]
from backup_cte;
GO

9) также можно улучшить представление по статистике ожиданий (из статьи Статистика ожиданий SQL Server’а или пожалуйста, скажите мне, где болит), чтобы убрать выводимые дублирующие строки в виде представления [inf].[vWaits]:Реализация представления [inf].[vWaits]
USE [БД для администрирования]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [inf].[vWaits] as
WITH [Waits] AS
    (SELECT
        [wait_type], --имя типа ожидания
        [wait_time_ms] / 1000.0 AS [WaitS],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
        [signal_wait_time_ms] / 1000.0 AS [SignalS],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
        [waiting_tasks_count] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
    )
, ress as (
	SELECT
	    [W1].[wait_type] AS [WaitType],
	    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
	    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
	    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
	    [W1].[WaitCount] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
	    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
	    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
	    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
	    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
	FROM [Waits] AS [W1]
	INNER JOIN [Waits] AS [W2]
	    ON [W2].[RowNum] <= [W1].[RowNum]
	GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
	    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
	HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95 -- percentage threshold
)
SELECT [WaitType]
      ,MAX([Wait_S]) as [Wait_S]
      ,MAX([Resource_S]) as [Resource_S]
      ,MAX([Signal_S]) as [Signal_S]
      ,MAX([WaitCount]) as [WaitCount]
      ,MAX([Percentage]) as [Percentage]
      ,MAX([AvgWait_S]) as [AvgWait_S]
      ,MAX([AvgRes_S]) as [AvgRes_S]
      ,MAX([AvgSig_S]) as [AvgSig_S]
  FROM ress
  group by [WaitType];
GO

БД SRV для администрирования БД и СУБД в целом


До момента написания своих статей, мною была создана БД SRV, которая модифицировалась и дополнялась с учетом полученного опыта и знаний.
Также были разработаны и другие проекты-утилиты в помощь администратору баз данных на C#.NET.

Доступ к проектам здесь.
В корне лежит файл “Описание”, где вкратце описан каждый проект.
Данные решения открыты и распространяются свободно.

Также благодаря вам, уважаемые читатели Хабра, которые оставляли обратную связь в форме комментариев и сообщений, удалось улучшить проект по БД SRV. За что вам большое спасибо!

Но важно отметить, что существующие подходы и решения, описанные во внешних источниках, следует внимательно анализировать, т.к. для вашей задачи данные методы могут не подойти. Необходимо обращать особое внимание на отличие параметров и условий вашей задачи от задачи, которая решается в примере: нагрузка, объём обрабатываемой информации, частота, специфика бизнес-задачи и т.п. Например, процедура, которая работает 40 минут подходит для вызова раз в сутки, но если процесс необходимо запускать с большей частотой, то такое решение может не подойти.

Найдя свой уникальный подход к конкретной задаче, не забывайте поделиться им! Таким образом вы пополните «глобальную базу знаний», которая облегчает поиск решений и идей для новых задач.

Итоги


Были рассмотрены еще некоторые полезные системные представления MS SQL Server, в том числе и для самодокументирования в форме расширенных свойств.

Размышления и идеи


Как вы уже заметили, MS SQL Server уже на достаточном уровне поддерживает NoSQL в виде графовых таблиц (с MS SQL Server 2017) и документоориентированных данных (XML, а с MS SQL Server 2016 и JSON).

Однако, как отмечал еще в 70-х годах 20-го века Эдгар Франк Кодд (по источнику [1]), в реляционной модели можно рассмотреть не простое отношение. Т. е. можно как встраивать одну таблицу в другую, так и наследовать от одной таблицы другую (напомню, что таблица — это отношение в реляционной модели). Наследование таблиц реализовано в некоторых СУБД, например, в том же PostgreSQL. Но мне не доводилось видеть реализацию вложений. Если реализовать и вложения и наследование, а также заложить механизм обработки этих сложных отношений, то получится СУБД, которая обобщает форматы JSON и XML и полностью покрывает так называемую технологию NoSQL (аналог перегрузки операторов в языках программирования, но в СУБД-индексы, агрегация, статистика, обслуживание и т д для таких отношений). Более того, она, возможно, покроет в достаточной степени и все другие модели данных, хотя и будет обрабатываться декларативным языком запросов SQL с некоторыми определенными расширениями и определениями для сложных отношений.

Видя, как быстро развивается MS SQL Server, стоит надеяться на то, что когда-нибудь он придет к реализации сложных отношений и покроет все их разновидности. И тогда пожелания и дальновидность создателя реляционной алгебры будут воплощены в жизнь, а в реляционной модели специалисты откроют совершенно иные аспекты создания различных информационных баз и хранилищ данных.

Источники:


» «Высоко-нагруженные приложения. Программирование, масштабирование, поддержка», СПб.: Питер, 2018 Клеппман М. [1]
» Статистика ожиданий SQL Server’а или пожалуйста, скажите мне, где болит
» Исследуем базы данных с помощью T-SQL
» Документация по SQL
» Улучшения tempdb в SQL Server 2016
» Оптимизация временной БД (tempdb)
» Стандарт оформления T-SQL
» Утилиты для MS SQL Server DBA
» dbForge
» PostgreSQL (наследование)
» MS SQL Server 2017 (графы)
» JSON в MS SQL Server 2016-2017

База данных SQl или NoSQL: какую выбрать для проекта?

Масштабируемость. Вертикальная, то есть при росте нагрузки растет производительность сервера. Если в базу поступает большой объем данных, рано или поздно наступит порог вертикального масштабирования — сервер не сможет далее увеличивать производительность. Тогда понадобится горизонтальное масштабирование — параллельная обработка данных в кластере серверов.

В больших распределенных системах это может привести к тому, что общая производительность системы упадет, так как нужно поддерживать согласованность данных в нескольких узлах. Это не значит, что СУБД на SQL не подходят для больших проектов — они поддерживают кластеризацию, просто нужно приложить усилия, чтобы настроить систему. Либо использовать базы данных в облаке — там можно получить уже настроенные и надежно работающие кластеры в несколько кликов.

Самые известные SQL-базы данных

MySQL — одна из самых популярных open source реляционных баз данных. Подходит небольшим и средним проектам, которым нужен недорогой и надежный инструмент работы с данными. Поддерживает множество типов таблиц, есть огромное количество плагинов и расширений, облегчающих работу с системой.

Отличается простой установкой, может быть интегрирована с другими СУБД, также интеграция с MySQL есть в любой CMS, фреймворке, языке программирования. Среди минусов — не все задачи выполняет автоматически, если что-то нужное не включено в функционал, придется потратить время на доработку, нет встроенной поддержки OLAP.

MySQL доступна как облачный сервис — в облаке не нужно тратить много времени на развертывание и конфигурацию СУБД. MySQL server стоит выбрать на старте бизнеса, чтобы тестировать гипотезы с минимальными затратами или для небольших проектов как транзакционную базу данных общего назначения.

PostgreSQL — вторая по популярности open source SQL СУБД. У нее много встроенных функций и дополнений, в том числе для масштабирования в кластер и шардинга таблиц. Подходит, если важна сохранность данных, предполагается их сложная структура. Позволяет работать со структурированными данными, но поддерживает JSON/BSON, что дает некоторую гибкость в схеме данных.

Отличается стабильностью, ее практически невозможно вывести из строя или что-то сломать в таблицах.

Из минусов — сложность конфигурации требует от пользователей некоторого опыта. Также скорость работы может падать во время проведения пакетных операций или при запросах на чтение.

PostgreSQL также можно развернуть в облаке — в отличие от MySQL, она подходит для крупных и масштабных проектов. Кроме того, ее стоит выбрать, если недопустимы ошибки в данных или есть особые требования к базе данных, например поддержка геоданных. Различные расширения PostgreSQL позволяют реализовать многие специализированные запросы.

Нереляционные базы данных, или базы данных NoSQL

Особенности. В отличие от реляционных, в нереляционных базах данных схема данных является динамической и может меняться в любой момент времени. К данным сложнее получить доступ, то есть найти внутри базы что-то нужное — с таблицей это просто, достаточно знать координаты ячейки. Зато такие СУБД отличаются производительностью и скоростью. Физические объекты в NoSQL обычно можно хранить прямо в том виде, в котором с ними потом работает приложение.

Базы данных NoSQL подходят для хранения больших объемов неструктурированной информации, а также хороши для быстрой разработки и тестирования гипотез.

В них можно хранить данные любого типа и добавлять новые в процессе работы.

Масштабируемость. NoSQL базы имеют распределенную архитектуру, поэтому хорошо масштабируются горизонтально и отличаются высокой производительностью. Технологии NoSQL могут автоматически распределять данные по разным серверам. Это повышает скорость чтения данных в распределенной среде.

Четыре вида нереляционных баз данных

Документоориентированные базы данных — данные хранятся в коллекциях документов, обычно с использованием форматов JSON, XML или BSON. Одна запись может содержать столько данных, сколько нужно, в любом типе данных (или типах) — ограничений нет. Внутри одного документа есть внутренняя структура, однако, она может отличаться от одного документа к другому. Также документы можно вкладывать друг в друга.

То есть вместо столбцов и строк мы описываем все данные в одном документе. Если нам нужно было бы добавить новые данные в таблицу реляционной базы данных, пришлось бы изменять ее схему данных. В случае с документами нужно только добавить в них дополнительные пары ключ-значение.

Пример такой базы данных: MongoDB.

Вот так будет выглядеть хранение данных в отдельных документах вместо таблицы со столбцами и строками:

10 лучших инструментов для разработки и администрирования MySQL / ХабрМногие компании создают различные многофункциональные приложения для облегчения управления, разработки и администрирования баз данных.

Большинство реляционных баз данных, за исключением MS Access, состоят из двух отдельных компонентов: «back-end», где хранятся данные и «front-end» — пользовательский интерфейс для взаимодействия с данными. Этот тип конструкции достаточно умный, так как он распараллеливает двухуровневую модель программирования, которая отделяет слой данных от пользовательского интерфейса и позволяет сконцентрировать рынок ПО непосредственно на улучшении своих продуктов. Эта модель открывает двери для третьих сторон, которые создают свои приложения для взаимодействия с различными базами данных.

В Интернете каждый может найти много продуктов для разработки и администрирования баз данных MySQL. Мы решили собрать 10 самых популярных инструментов в одной статье, чтобы вы смогли сэкономить свое время.

1. Workbench

Первое место, по праву принадлежит инструменту Workbench (разработка компании Sun Systems/Oracle), который может работать на платформах Microsoft Windows, Mac OS X и Linux. Workbench объединяет в себе разработку и администрирование баз данных и является преемником DBDesigner4.

MySQL Workbench распространяется под свободной лицензией — Community Edition и с ежегодной оплачиваемой подпиской — Standard Edition. Последняя включает в себя дополнительные возможности, которые способны существенно улучшить производительность, как разработчиков, так и администраторов баз данных.

Скачать Workbench можно здесь dev.mysql.com/downloads/workbench

Стоимость — бесплатно.

Что делает Workbench популярным?

  • возможность представить модель БД в графическом виде, а также редактирование данных в таблице;
  • наличие простого и функционального механизма по созданию связей между полями таблиц, среди которых реализована связь «многие-ко-многим» с возможностью создания таблицы связей;
  • функция Reverse Engineering позволяет восстанавливать структуру таблиц и связей из той, которая была реализована ранее и хранится на сервере БД;
  • наличие редактора SQL-запросов, который дает возможность при отправке на сервер получать ответ в табличном виде и другие возможности.
2. Navicat

Второе место занимает Navicat (разработка компании PremiumSoft CyberTech Ltd) — инструмент для разработки и администрирования баз данных, который работает на любом сервере MySQL, начиная с версии 3.21. Для MySQL, Navicat доступен для работы на платформах Microsoft Windows, Mac OS X и Linux.

Подробнее о Navicat вы можете узнать здесь www.navicat.com/en/products/navicat_mysql/mysql_overview.html

Стоимость продукта варьируется от 199 до 379 долл. США.

Что делает Navicat популярным?

  • наличие визуального конструктора запросов;
  • возможность импорта, экспорта и резервного копирования данных;
  • возможность создавать отчеты;
  • SSH и HTTP туннелинг;
  • миграция и синхронизация данных и структуры;
  • инструмент для планирования задач и другие возможности.
3. PHPMyAdmin

PHPMyAdmin — бесплатное приложение с открытым кодом, предназначенное для администрирования СУБД MySQL. PHPMyAdmin представляет собой веб-интерфейс с помощью которого можно администрировать сервер MySQL, запускать команды и просматривать содержимое таблиц и БД через браузер.

Скачать PHPMyAdmin можно здесь www.phpmyadmin.net/home_page

Стоимость — бесплатно.

Что делает PHPMyAdmin популярным?

  • возможность управлять СУБД MySQL без непосредственного ввода SQL команд;
  • как панель управления PHPMyAdmin предоставляет возможность администрирования выделенных БД;
  • интенсивное развитие;
  • возможность интегрировать PHPMyAdmin в собственные разработки благодаря лицензии GNU General Public License и другие возможности.
4. dbForge Studio for MySQL

dbForge Studio for MySQL — инструмент, представляющий интерес как для пользователей MySQL, так и для разработчиков БД. С его помощью вы сумеете легко автоматизировать рутинную работу и сэкономить время. Сегодня dbForge Studio for MySQL представлен в трех редакциях: Express, Standard и Professional, что позволяет выбрать тот инструмент, который нужен именно вам. Пользоваться dbForge Studio for MySQL можно как коммерческой, так и бесплатной версией.

Ознакомиться с возможностями dbForge Studio for MySQL вы можете здесь www.devart.com/ru/dbforge/mysql/studio

Существует как бесплатная, так и платная версии, цена последней составляет 49,95 долл. США (стандартное издание ) и 99,99 долл. США (профессиональное издание).

Что делает dbForge Studio популярным?

  • наличие средств для централизованного администрирования;
  • инструменты для сравнения БД;
  • визуальный профилировщик запросов;
  • возможность управлять привилегиями пользователей;
  • наличие Дизайнера БД, который позволяет строить визуальные диаграммы;
  • улучшенная работа с проектами БД и другие возможности.
5. HeidiSQL

HeidiSQL — бесплатный инструмент для управления базами данных. Достойная альтернатива PHPMyAdmin, которая позволяет создавать и редактировать таблицы, представления, триггеры, процедура, а также просматривать и редактировать данные. Также HeidiSQL предоставляет возможность экспорта данных как в SQL файл, так и в буфер обмена на других серверах.

Скачать HeidiSQL можно здесь Сайт: www.heidisql.com

Стоимость — бесплатно.

Что делает HeidiSQL популярным?

  • возможность подключаться к серверу с помощью командной строки;
  • возможность пакетной оптимизации и восстановления таблиц;
  • возможность редактирования столбцов, индексов и внешних ключей таблиц, редактирование тела и параметров SQL процедур, триггеров и др.;
  • простое форматирование неупорядоченных SQL;
  • синхронизация таблицы между разными базами данных и другие возможности.
6. SQL Maestro для MySQL

SQL Maestro для MySQL — инструмент для администрирования, разработки и управления наиболее востребованных СУБД. Удобный графический интерфейс дает возможность выполнять SQL запросы и скрипты, управлять привилегиями пользователей, экспортировать и создавать резервные копии данных.

Ознакомиться с возможностями и купить SQL Maestro для MySQL можно здесь www.sqlmaestro.com/products/mysql

В зависимости от выбранной лицензии и варианта использования, стоимость данного инструмента варьируется от 99 до 1949 долл. США.

Что делает SQL Maestro для MySQL популярным?

  • поддержка версий MySQL сервера с версии 3.23;
  • наличие конструктора баз данных;
  • возможность редактирование, группировки, сортировки и фильтрации данных;
  • визуальный конструктор запросов;
  • SSH и HTTP туннелинг;
  • BLOB-редактор и другие возможности.
7. EMS SQL Manager для MySQL

EMS SQL Manager для MySQL — инструмент для разработки и администрирования баз данных, который поддерживает различные функции MySQL и работает со всеми версиями MySQL старше 3.23. С его помощью у вас есть возможность визуально редактировать, импортировать и экспортировать БД, выполнять сценарии SQL, управлять привилегиями пользователей, визуально проектировать базы данных MySQL.

Подробнее ознакомиться и приобрести EMS SQL Manager для MySQL можно здесь www.sqlmanager.net./ru/products/studio/mysql

Существует платная и бесплатная версии приложения. Последняя имеет ряд функциональных ограничений. Стоимость платной версии варьируется в пределах 95 – 245 долл. США.

Что делает EMS SQL Manager for MySQLпопулярным?

  • поддержка данных UTF8;
  • простое управление различными объектами MySQL;
  • совместимость со всеми версиями с 3.23 по 6.0 включительно;
  • наличие графических и текстовых инструментов для формирования запросов;
  • SSH и HTTP туннелинг;
  • удобный конструктор отчетов и другие возможности.
8. SQLyog

SQLyog — один из наиболее мощных инструментов, который сочетает в себе возможности MySQL Administrator, PHPMyAdmin и некоторые другие инструменты для администрирования и разработки баз данных. SQLyog работает на платформах Microsoft Windows, Windows NT. и Linux с помощью Wine.

Подробнее ознакомиться и приобрести SQLyog можно здесь www.webyog.com/en/index.php

Доступна как бесплатная, так и платная версия SQLyog. Стоимость платной версии — от 99 до 1499 долл. США (варьируется в зависимости от количества пользователей и лицензии, с поддержкой или без нее).

Что делает SQLyog популярным?

  • удобный конструктор запросов;
  • возможность синхронизации данных;
  • поддержка юникода;
  • SSH и HTTP, HTTPS туннелинг;
  • «умное» автозавершение работы;
  • интеллектуальное дополнение кода и другие возможности.
9. DBTools Manager

DBTools Manager — приложение для управления данными, с встроенной поддержкой MySQL, PostgreSQL, MSAccess, MSSQL Server, Oracle и других БД. Поддерживаемые платформы: Windows 2000, XP, Vista, 7.

DBTools Manager представлен в бесплатном (Standard) и платном варианте (Enterprise). Стоимость составляет 69.90 долл. США за одну лицензию, при покупке нескольких лицензий предусмотрены скидки.

Подробнее ознакомиться и приобрести DBTools Manager можно здесь www.dbtools.com.br/EN/dbmanagerpro

Что делает DBTools Manager популярным?

  • управление базами данных, таблицами;
  • наличие редактора запросов;
  • наличие мастера создания форм и отчетов;
  • возможность импорта и экспорта данных из различных источников, среди которых MSAccess, MSExcel, Paradox, FoxPro, DBF, ODBC таблицы, текстовые и XML файлы;
  • конструктор диаграмм и другие возможности.
10. MyDB Studio

MyDB Studio — бесплатный инструмент для администрирования БД MySQL, который позволяет создавать, редактировать и удалять записи, таблицы и базы данных. Работает исключительно на платформе Windows.

Скачать MyDB Studio можно здесь www.mydb-studio.com

Стоимость — бесплатно.

Что делает MyDB Studio популярным?

  • возможность подключаться к неограниченному количеству баз данных;
  • возможность подключения по SSH каналам;
  • создание откатов и экспорт БД в различные форматы;
  • возможность переноса, резервного копирования, также восстановления БД и другие возможности.
Руководство по проектированию реляционных баз данных (1-3 часть из 15) [перевод]Перевод цикла из 15 статей о проектировании баз данных.
Информация предназначена для новичков.
Помогло мне. Возможно, что поможет еще кому-то восполнить пробелы.

Другие части: 4-6, 7-9, 10-13, 14-15.

Руководство по проектированию баз данных.
1. Вступление.

Если вы собираетесь создавать собственные базы данных, то неплохо было бы придерживаться правил проектирования баз данных, так как это обеспечит долговременную целостность и простоту обслуживания ваших данных. Данное руководство расскажет вам что представляют из себя базы данных и как спроектировать базу данных, которая подчиняется правилам проектирования реляционных баз данных.

Базы данных – это программы, которые позволяют сохранять и получать большие объемы связанной информации. Базы данных состоят из таблиц, которые содержат информацию. Когда вы создаете базу данных необходимо подумать о том, какие таблицы вам нужно создать и какие связи существуют между информацией в таблицах. Иначе говоря, вам нужно подумать о проекте вашей базы данных. Хороший проект базы данных, как было сказано ранее, обеспечит целостность данных и простоту их обслуживания.
Структурированный язык запросов (SQL).

База данных создается для хранения в ней информации и получения этой информации при необходимости. Это значит, что мы должны иметь возможность помещать, вставлять (INSERT) информацию в базу данных и мы хотим иметь возможность делать выборку информации из базы данных (SELECT).
Язык запросов к базам данных был придуман для этих целей и был назван Структурированный язык запросов или SQL. Операции вставки данных (INSERT) и их выборки (SELECT) – части этого самого языка. Ниже приведен пример запроса на выборку данных и его результат.

SQL – большая тема для повествования и его рассмотрение выходит за рамки данного руководства. Данная статья строго сфокусирована на изложении процесса проектирования баз данных. Позднее, в отдельном руководстве, я расскажу об основах SQL.

Реляционная модель.

В этом руководстве я покажу вам как создавать реляционную модель данных. Реляционная модель – это модель, которая описывает как организовать данные в таблицах и как определить связи между этими таблицами.

Правила реляционной модели диктуют, как информация должна быть организована в таблицах и как таблицы связаны друг с другом. В конечном счете результат можно предоставить в виде диаграммы базы данных или, если точнее, диаграммы «сущность-связь», как на рисунке (Пример взят из MySQL Workbench).

Примеры.

В качестве примеров в руководстве я использовал ряд приложений.

РСУБД.

РСУБД, которую я использовал для создания таблиц примеров – MySQL. MySQL – наиболее популярная РСУБД и она бесплатна.

Утилита для администрирования БД.

После установки MySQL вы получаете только интерфейс командной строки для взаимодействия с MySQL. Лично я предпочитаю графический интерфейс для управления моими базами данных. Я часто использую SQLyog. Это бесплатная утилита с графическим интерфейсом. Изображения таблиц в данном руководстве взяты оттуда.

Визуальное моделирование.

Существует отличное бесплатное приложение MySQL Workbench. Оно позволяет спроектировать вашу базу данных графически. Изображения диаграмм в руководстве сделаны в этой программе.

Проектирование независимо от РСУБД.

Важно знать, что хотя в данном руководстве и приведены примеры для MySQL, проектирование баз данных независимо от РСУБД. Это значит, что информация применима к реляционным базам данных в общем, не только к MySQL. Вы можете применить знания из этого руководства к любым реляционным базам данных, подобным Mysql, Postgresql, Microsoft Access, Microsoft Sql or Oracle.

В следующей части я коротко расскажу об эволюции баз данных. Вы узнаете откуда взялись базы данных и реляционная модель данных.

2. История.

В 70-х – 80-х годах, когда компьютерные ученые все еще носили коричневые смокинги и очки с большими, квадратными оправами, данные хранились бесструктурно в файлах, которые представляли собой текстовый документ с данными, разделенными (обычно) запятыми или табуляциями.

Так выглядели профессионалы в сфере информационных технологий в 70-е. (Слева внизу находится Билл Гейтс).

Текстовые файлы и сегодня все еще используются для хранения малых объемов простой информации. Comma-Separated Values (CSV) — значения, разделённые запятыми, очень популярны и широко поддерживаются сегодня различным программным обеспечением и операционными системами. Microsoft Excel – один из примеров программ, которые могут работать с CSV–файлами. Данные, сохраненные в таком файле могут быть считаны компьютерной программой.

Выше приведен пример того, как такой файл мог бы выглядеть. Программа, производящая чтение данного файла, должна быть уведомлена о том, что данные разделены запятыми. Если программа хочет выбрать и вывести категорию, в которой находится урок ‘Database Design Tutorial’, то она должна строчка за строчкой производить чтение до тех пор, пока не будут найдены слова ‘Database Design Tutorial’ и затем ей нужно будет прочитать следующее за запятой слово для того, чтобы вывести категорию Software.

Таблицы баз данных.

Чтение файла строчка за строчкой не является очень эффективным. В реляционной базе данных данные хранятся в таблицах. Таблица ниже содержит те же самые данные, что и файл. Каждая строка или “запись” содержит один урок. Каждый столбец содержит какое-то свойство урока. В данном случае это заголовок (title) и его категория (category).

Компьютерная программа могла бы осуществить поиск в столбце tutorial_id данной таблицы по специфическому идентификатору tutorial_id для того, чтобы быстро найти соответствующие ему заголовок и категорию. Это намного быстрее, чем поиск по файлу строка за строкой, подобно тому, как это делает программа в текстовом файле.

Современные реляционные базы данных созданы так, чтобы позволять делать выборку данных из специфических строк, столбцов и множественных таблиц, за раз, очень быстро.

История реляционной модели.

Реляционная модель баз данных была изобретена в 70-х Эдгаром Коддом (Ted Codd), британским ученым. Он хотел преодолеть недостатки сетевой модели баз данных и иерархической модели. И он очень в этом преуспел. Реляционная модель баз данных сегодня всеобще принята и считается мощной моделью для эффективной организации данных.

Сегодня доступен широкий выбор систем управления базами данных: от небольших десктопных приложений до многофункциональных серверных систем с высокооптимизированными методами поиска. Вот некоторые из наиболее известных систем управления реляционными базами данных (РСУБД):

Oracle – используется преимущественно для профессиональных, больших приложений.
Microsoft SQL server – РСУБД компании Microsoft. Доступна только для операционной системы Windows.
Mysql – очень популярная РСУБД с открытым исходным кодом. Широко используется как профессионалами, так и новичками. Что еще нужно?! Она бесплатна.
IBM – имеет ряд РСУБД, наиболее известна DB2.
Microsoft Access – РСУБД, которая используется в офисе и дома. На самом деле – это больше, чем просто база данных. MS Access позволяет создавать базы данных с пользовательским интерфейсом.
В следующей части я расскажу кое-что о характеристиках реляционных баз данных.

3. Характеристики реляционных баз данных.

Реляционные базы данных разработаны для быстрого сохранения и получения больших объемов информации. Ниже приведены некоторые характеристики реляционных баз данных и реляционной модели данных.
Использование ключей.

Каждая строка данных в таблице идентифицируется уникальным “ключом”, который называется первичным ключом. Зачастую, первичный ключ это автоматически увеличиваемое (автоинкрементное) число (1,2,3,4 и т.д). Данные в различных таблицах могут быть связаны вместе при использовании ключей. Значения первичного ключа одной таблицы могут быть добавлены в строки (записи) другой таблицы, тем самым, связывая эти записи вместе.

Используя структурированный язык запросов (SQL), данные из разных таблиц, которые связаны ключом, могут быть выбраны за один раз. Для примера вы можете создать запрос, который выберет все заказы из таблицы заказов (orders), которые принадлежат пользователю с идентификатором (id) 3 (Mike) из таблицы пользователей (users). О ключах мы поговорим далее, в следующих частях.


Столбец id в данной таблице является первичным ключом. Каждая запись имеет уникальный первичный ключ, часто число. Столбец usergroup (группы пользователей) является внешним ключом. Судя по ее названию, она видимо ссылается на таблицу, которая содержит группы пользователей.

Отсутствие избыточности данных.

В проекте базы данных, которая создана с учетом правил реляционной модели данных, каждый кусочек информации, например, имя пользователя, хранится только в одном месте. Это позволяет устранить необходимость работы с данными в нескольких местах. Дублирование данных называется избыточностью данных и этого следует избегать в хорошем проекте базы данных.
Ограничение ввода.

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


Когда вы создаете таблицу базы данных вы предоставляете тип данных для каждого столбца. К примеру, varchar – это тип данных для небольших фрагментов текста с максимальным количеством знаков, равным 255, а int – это числа.

Помимо типов данных РСУБД позволяет вам еще больше ограничить возможные для ввода данные. Например, ограничить длину или принудительно указать на уникальность значения записей в данном столбце. Последнее ограничение часто используется для полей, которые содержат регистрационные имена пользователей (логины), или адреса электронной почты.

Эти ограничения дают вам контроль над целостностью ваших данных и предотвращают ситуации, подобные следующим:

— ввод адреса (текста) в поле, в котором вы ожидаете увидеть число
— ввод индекса региона с длинной этого самого индекса в сотню символов
— создание пользователей с одним и тем же именем
— создание пользователей с одним и тем же адресом электронной почты
— ввод веса (числа) в поле дня рождения (дата)

Поддержание целостности данных.

Настраивая свойства полей, связывая таблицы между собой и настраивая ограничения, вы можете увеличить надежность ваших данных.
Назначение прав.

Большинство РСУБД предлагают настройку прав доступа, которая позволяет назначать определенные права определенным пользователям. Некоторые действия, которые могут быть позволены или запрещены пользователю: SELECT (выборка), INSERT (вставка), DELETE (удаление), ALTER (изменение), CREATE (создание) и т.д. Это операции, которые могут быть выполнены с помощью структурированного языка запросов (SQL).
Структурированный язык запросов (SQL).

Для того, чтобы выполнять определенные операции над базой данных, такие, как сохранение данных, их выборка, изменение, используется структурированный язык запросов (SQL). SQL относительно легок для понимания и позволяет в т.ч. и уложненные выборки, например, выборка связанных данных из нескольких таблиц с помощью оператора SQL JOIN. Как и упоминалось ранее, SQL в данном руководстве обсуждаться не будет. Я сосредоточусь на проектировании баз данных.

То, как вы спроектируете базу данных будет оказывать непосредственное влияние на запросы, которые вам будет необходимо выполнить, чтобы получить данные из базы данных. Это еще одна причина, почему вам необходимо задуматься о том, какой должна быть ваша база. С хорошо спроектированной базой данных ваши запросы могут быть чище и проще.

Переносимость.

Реляционная модель данных стандартна. Следуя правилам реляционной модели данных вы можете быть уверены, что ваши данные могут быть перенесены в другую РСУБД относительно просто.

Как говорилось ранее, проектирование базы данных – это вопрос идентификации данных, их связи и помещение результатов решения данного вопроса на бумагу (или в компьютерную программу). Проектирование базы данных независимо от РСУБД, которую вы собираетесь использовать для ее создания.

В следующей части подробнее рассмотрим первичные ключи.

Взаимодействие R с базами данных на примере Microsoft SQL Server и других СУБД / Хабр

Поскольку львиная доля бизнес информации храниться в базах данных. На каком бы языке программирования вы не писали, вам придётся производить различные действия с ними.

В этой статье я расскажу о двух интерфейса для работы с базами данных в R. Большая часть примеров демонстрируют работу с Microsoft SQL Server, тем не менее все примеры кода будут работать и с другими базами данных, такими как: MySQL, PostgreSQL, SQLite, ClickHouse, Google BigQuery и др.


Если вы интересуетесь анализом данных, и в частности языком R, возможно вам будут интересны мои telegram и youtube каналы. Большая часть контента которых посвящена языку R.



Для того, что бы повторить все описанные в статье примеры работы с СУБД вам потребуется перечисленное ниже, бесплатное программное обеспечение:


  1. Язык R;
  2. Среда разработки RStudio;
  3. Система Управления Базами Данных, на выбор:
    3.1. Microsoft SQL Server
    3.2. MySQL
    3.3. PostgreSQL

Пакет DBI является наиболее популярным и удобным способом взаимодействия с базами данных в R.

DBI предоставляет вам набор функций, с помощью которых вы можете управлять базами данных. Но для подключения к базам данных требуется установка дополнительных пакетов, которые являются драйверами к различным системам управления базами данных (СУБД).


Список основных функций DBI


  • dbConnect — подключение к базе данных;
  • dbWriteTable — запись таблицы в базу данных;
  • dbReadTable — загрузка таблицы из базы данных;
  • dbGetQuery — загрузка результата выполнения запроса;
  • dbSendQuery — отправка запроса к базе данных;
  • dbFetch — извлечение элементов из набора результатов;
  • dbExecute — выполнение запросов на обновление / удаление / вставку данных в таблицы;
  • dbGetInfo — запрос информацию о результате запроса или подключении;
  • dbListFields — запрос списка полей таблицы;
  • dbListTables — запрос списка таблиц базы данных;
  • dbExistsTable — проверка наличия таблицы в базе данных;
  • dbRemoveTable — удаление таблицы из базы данных;
  • dbDisconnect — разрыв отсоединения с базы данных.

Подключение к базам данных

Для взаимодействия с базами данных предварительно к ним необходимо подключиться. В зависимости от СУБД с которой вы планируете работать вам потребуется дополнительный пакет, ниже перечень наиболее часто используемых.


  • odbc — Драйвер для подключения через ODBC интерфейс;
  • RSQLite — Драйвер к SQLite;
  • RMySQL / RMariaDB — Драйвер к СУБД MySQL и MariaDB;
  • RPostgreSQL — Драйвер к PosrtgreSQL;
  • bigrquery — Драйвер к Google BigQuery;
  • RClickhouse / clickhouse — Драйвер к ClickHouse;
  • RMSSQL — Драйвер к Microsoft SQL Server (MS SQL), на момент написания статьи присутствует только на GitHub.

Пакет DBI поставляется с базовой комплектацией R, но пакеты, которые являются драйверами к базам данных необходимо устанавливать с помощью команды install.packages("название драйвера").

Для установки пакетов с GitHub вам также понадобится дополнительный пакет — devtools. Например пакет RMSSQL на данный момент не опубликован в основном репозитории R пакетов, для его установки воспользуйтесь следующим кодом:

install.packages("devtools")
devtools::install_github("bescoto/RMSSQL")

Пример подключения к Microsoft SQL Server с помощью пакета odbc

Перед использованием любого пакета в R сессии его предварительно необходимо подключить с помощью функции library("название пакета").

Я неспроста выбрал Microsoft SQL Server в качестве основной СУБД на которой будет приведена большая часть примеров этой статьи. Дело в том, что это достаточно популярная база данных, но при этом она до сих пор не имеет драйвера для подключения из R опубликованного на CRAN.

Но к счастью SQL Server, как и практически любая другая база имеет ODBC (англ. Open Database Connectivity) интерфейс для подключения. Для подключения к СУБД через ODBC интерфейс в R есть ряд пакетов. Первым мы рассмотрим подключение через пакет odbc.


Простое подключение к БД через odbc интерфейс
# установка пакета odbc
install.packages("odbc")

# подключение пакета
library(odbc)

# подключение к MS SQL
con <- dbConnect(drv = odbc(),
                 Driver   = "SQL Server",
                 Server   = "localhost",
                 Database = "mybase",
                 UID      = "my_username",
                 PWD      = "my_password",
                 Port     = 1433)

В функцию dbConnect() вам необходимо первым аргументом drv передать функцию, которая является драйвером для подключения к СУБД (odbc()). Такие функции обычно называются также, как и СУБД, и поставляются с пакетами которые являются драйверами для DBI.

Далее необходимо перечислить параметры подключения. Для подключения к MS SQL через ODBC необходимо задать следующие параметры:


  • Driver — Название ODBC драйвера;
  • Server — IP адрес SQL сервера;
  • Database — Название базы данных к которой необходимо подключиться;
  • UID — Имя пользователя базы данных;
  • PWD — Пароль;
  • Port — Порт для подключения, у SQL Server по умолчанию порт 1433.

ODBC драйвер для подключения к Microsoft SQL Server включен в комплектацию Windows, но он может иметь и другое название. Посмотреть список установленных драйверов можно в Администраторе источника данных ODBC. Запустить администратор источника данных в Windows 10 можно по следующему пути:


  • 32-разрядной версии: %systemdrive%\Windows\SysWoW64\Odbcad32.exe
  • 64-разрядной версии: %systemdrive%\Windows\System32\Odbcad32.exe

Получить список всех установленных на вашем ПК драйверов также можно с помощью функции odbcListDrivers().

   name                                  attribute        value                                   
   <chr>                                 <chr>            <chr>                                   
 1 SQL Server                            APILevel         2                                       
 2 SQL Server                            ConnectFunctions YYY                                     
 3 SQL Server                            CPTimeout        60                                      
 4 SQL Server                            DriverODBCVer    03.50                                   
 5 SQL Server                            FileUsage        0                                       
 6 SQL Server                            SQLLevel         1                                       
 7 SQL Server                            UsageCount       1                                       
 8 MySQL ODBC 5.3 ANSI Driver            UsageCount       1                                       
 9 MySQL ODBC 5.3 Unicode Driver         UsageCount       1                                       
10 Simba ODBC Driver for Google BigQuery Description      Simba ODBC Driver for Google BigQuery2.0
# ... with 50 more rows

Скачать ODBC драйвера для других СУБД можно по следующим ссылкам:


Для различных СУБД название параметров для подключения могут быть другими, например:


  • PostgreSQL / MySQL / MariaDB — user, password, host, port, dbname;
  • GoogleBigQuery — project, dataset;
  • ClickHouse — user, password, db, port, host;

С помощью администратора источника данных ODBC вы можете запустить мастер для создания ODBC источника данных. Для этого достаточно открыть администратор, перейти на вкладку «Пользовательский DSN» и нажать кнопку «Добавить…».

При создании источника данных используя администратор вы присваиваете ему имя, DSN (Data Source Name).

В примере выше мы создали источник данных с DSN «my_test_source». Теперь мы можем использовать этот источник для подключения к Microsoft SQL Server, и не указывать в коде остальные параметры подключения.


Подключение к БД через odbc интерфейс с использованием DSN
# подключение через DSN
con <- dbConnect(odbc(),
                DSN = "my_test_source",
                UID = "my_username",
                PWD = "my_password")

Посмотреть имена всех созданных на вашем ПК источников данных ODBC можно с помощью функции odbcListDataSources().

   name            description                          
   <chr>           <chr>                                
 1 BQ              Simba ODBC Driver for Google BigQuery
 2 BQ_main         Simba ODBC Driver for Google BigQuery
 3 BQ ODBC         Simba ODBC Driver for Google BigQuery
 4 OLX             Simba ODBC Driver for Google BigQuery
 5 Multicharts     Simba ODBC Driver for Google BigQuery
 6 PostgreSQL35W   PostgreSQL Unicode(x64)              
 7 hillel_bq       Simba ODBC Driver for Google BigQuery
 8 blog_bq         Simba ODBC Driver for Google BigQuery
 9 MyClientMSSQL   SQL Server                           
10 local_mssql     SQL Server                           
11 MSSQL_localhost SQL Server                           
12 my_test_source  SQL Server                           
13 Google BigQuery Simba ODBC Driver for Google BigQuery

Пример подключения к Microsoft SQL Server с помощью пакета RMSSQL

RMSSQL не опубликован на CRAN, поэтому установить его можно с GitHub с помощью пакета devtools.

install.packages("devtools")
devtools::install_github("bescoto/RMSSQL")

Пример подключения с помощью DBI драйвера RMSSQL
# подключение требуемых пакетов
library(RJDBC)
library(RMSSQL)
library(DBI)

# через RMSSQL
con <- dbConnect(MSSQLServer(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = 'my_password', 
                 dbname   = "mybase")

В большинстве случаев, используя для работы с базами данных пакет DBI, вы будете подключаться именно таким способом. Т.е. устанавливать один из требуемых пакетов — драйверов, передавая в качестве значения аргумента drv функции dbConnect, функцию — драйвер для подключения к нужной вам СУБД.


Пример подключения к MySQL, PostgreSQL, SQLite и BigQuery
# подключение к MySQL
library(RMySQL)

con <- dbConnect(MySQL(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = 'my_password', 
                 dbname   = "mybase",
                 host     = "localhost")

# подключение к PostrgeSQL
library(RPostgreSQL)

con <- dbConnect(PostgreSQL(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = 'my_password', 
                 dbname   = "mybase",
                 host     = "localhost")

# подключение к PostrgeSQL
library(RPostgreSQL)

con <- dbConnect(PostgreSQL(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = 'my_password', 
                 dbname   = "mybase",
                 host     = "localhost")

# Подключение к SQLite
library(RSQLite)

# connection or create base
con <- dbConnect(drv = SQLite(),
                 "localhost.db")

# Подключение к Google BigQuery
library(bigrquery)

con <- dbConnect(drv     = bigquery(),
                 project = "my_proj_id",
                 dataset = "dataset_name")

Как скрыть пароли от базы данных в R скриптах

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

Если все ваши скрипты развёрнуты и запускаются исключительно локально на вашем ПК, и он при этом защищён паролем, то скорее всего никакой проблемы в этом не будет. Но если вы совместно с кем то работаете на одном сервере то хранение паролей от баз данных в тексте ваших скриптов не лучшее решение.

В любой операционной системе есть утилита для управления учётными данными. Например, в Windows это диспетчер учетных данных (Credential Manager). Добавить в это хранилище пароль который вы используете для подключения к базе данных можно через пакет keyring. Пакет кроссплатформенный и приведённый пример будет работать в любой операционной системе, как минимум на Windows, MacOS и Linux.

# install.packages("keyring")

# подключаем пакет
library(keyring)
library(RMSSQL)

# создаём ключ
key_set_with_value(service = "mssql", 
                   username = "my_username",
                   password = "my_password")

# подключение через RMSSQL
con <- dbConnect(MSSQLServer(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = key_get("mssql", "my_username"), 
                 dbname   = "mybase")

Т.е. с помощью функции key_set_with_value() вы добавляете пароль в хранилище учётных данных, а с помощью key_get() запрашиваете его, при этом запросить пароль может только тот пользователь который добавил его в хранилище. С помощью keyring можно хранить пароли не только от баз данных, но и от любых сервисов, а так же авторизационные токены при работе с API.


Создание таблиц и запись в базу данных

Запись в базу данных осуществляется функцией dbWriteTable().

Аргументы функции dbWriteTable():

Жирным шрифтом выделены обязательные аргументы, курсивом — не обязательные


  • conn — объект подключения к СУБД, созданный с помощью функции dbConnect;
  • name — название таблицы в СУБД, в которую будут записаны данные;
  • value — таблица (объект класса data.frame / data.table / tibble_frame) в R, данные из которого будут записаны в СУБД;
  • row.names — Добавляет в таблицу столбец row_names, с номерами строк, по умолчанию имеет значение FALSE.
  • overwrite — Перезаписывать таблицу, если таблица с именем указанным в аргументе name уже присутвует в СУБД, по умолчанию имеет значение FALSE;
  • append — Дописывать данные, если таблица с именем указанным в аргументе name уже присутвует в СУБД, по умолчанию имеет значение FALSE;
  • field.types — Принимает на вход именованный вектор, и задаёт тип данных в каждом поле при записи в СУБД, по умолчанию имеет значение NULL;
  • temporary — Позволяет создавать временные таблицы в СУБД, которые будут доступны до момента разрыва соединения с базой, по умолчанию имеет значение FALSE.

Пример записи данных в СУБД через DBI
# подключаем пакет
library(odbc)
# соединяемся с базой через DSN
con <- dbConnect(odbc(),
                DSN = "my_test_source",
                UID = "my_username",
                PWD = "my_password")
# создаём в базе таблицу iris, и записываем в неё данные из встроенного в R набора iris
dbWriteTable(conn  = con,
             name  = "iris", 
             value = iris)

# разрыв соединения с БД
dbDisconnect(con)

Для просмотра таблиц в базе данных служит функция dbListTables(), для удаления таблиц dbRemoveTable()


Пример запроса списка таблиц и удаления таблицы из СУБД
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# просмотр списка таблиц
dbListTables(con)
# удаление таблицы iris
dbRemoveTable(con, "iris")

# разрыв соединения с БД
dbDisconnect(con)

Чтение данных из СУБД

С помощью DBI вы можете запрашивать либо таблицы целиком, либо результат выполнения вашего SQL запроса. Для выполнения этих операций используются функции dbReadTable() и dbGetQuery().


Пример запроса таблицы iris из СУБД
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# загружзка табоицы iris в объект iris
dbiris <- dbReadTable(con, "iris")

# разрыв соединения с БД
dbDisconnect(con)

Пример загрузки результата выполнения SQL из СУБД
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# Запрашиваем результат выполнения запроса
setosa <- dbGetQuery(con,
                     "SELECT * FROM iris WHERE Species = 'setosa'")

# разрыв соединения с БД
dbDisconnect(con)

Манипулирование данными в СУБД (DML)

Рассмотренная выше функция dbGetQuery() используется исключительно для запросов на выборку данных (SELECT).

Для операций манипуляций с данными, таких как UPDATE, INSERT, DELETE, в DBI существует функция dbExecute().


Пример кода для манипуляции данными в СУБД
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# Вставка строк (INSERT)
dbExecute(con, 
          "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])
           VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")

# Обновление данных (UPDATE)
dbExecute(con, 
          "UPDATE iris
           SET [Species] = 'old_value'
           WHERE row_names = 51")

# Удаление строк из таблицы (DELETE)
dbExecute(con, "DELETE FROM iris WHERE row_names = 51")

# разрыв соединения с БД
dbDisconnect(con)

Транзакции в СУБД


Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, commit) либо отмена изменений (откат, rollback). Применительно к БД транзакция это нескольких запросов, которые трактуются как единый запрос.

Цитата из статьи «Транзакции и механизмы их контроля»

Транзакция инкапсулирует несколько операторов SQL в элементарную единицу. В DBI начало транзакции инициируется с помощью dbBegin() и далее либо подтверждается с помощью dbCommit(), либо отменяется с помощью dbRollback(). В любом случае СУБД гарантирует, что: либо все, либо ни одно из утверждений не будут применены к данным.

Для примера, давайте в ходе транзакции добавим в таблицу iris 51 строку, далее изменим значение Sepal.Width в 5 строке, и удалим 43 строку из таблицы.


Пример кода проведения транзакции
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# запрашиваем значения до внесения изменений
dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")

#   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1         5          5.0         3.6          1.4         0.2     setosa
# 2        43          4.4         3.2          1.3         0.2     setosa
# 3        51          7.0         3.2          4.7         1.4 versicolor

# инициируем начало транзакции
dbBegin(con)

# добавляе строку
dbExecute(con, 
          "INSERT INTO iris 
          (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])
           VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")

# меняем строку 
dbExecute(con, 
          "UPDATE iris
           SET [Sepal.Width] = 8
           WHERE row_names = 5")

# удаляем строку 43
dbExecute(con, "DELETE FROM iris WHERE row_names = 43")

# подтверждаем транзакцию
dbCommit(con)

# запрашиваем значения после внесения изменений
dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")

#   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1         5            5         8.0          1.4         0.2     setosa
# 2        51            7         3.2          4.7         1.4 versicolor
# 3        51            5         3.3          1.7         0.3 new_values

Пример кода отмены транзакции
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# запрашиваем значения до внесения изменений
dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")

#   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1         5          5.0         3.6          1.4         0.2     setosa
# 2        43          4.4         3.2          1.3         0.2     setosa
# 3        51          7.0         3.2          4.7         1.4 versicolor

# инициируем начало транзакции
dbBegin(con)

# добавляе строку
dbExecute(con, 
          "INSERT INTO iris 
          (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])
           VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")

# меняем строку 
dbExecute(con, 
          "UPDATE iris
           SET [Sepal.Width] = 8
           WHERE row_names = 5")

# удаляем строку 43
dbExecute(con, "DELETE FROM iris WHERE row_names = 43")

# отменяем транзакцию
dbRollback(con)

# запрашиваем значения после внесения изменений
dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")

#   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1         5          5.0         3.6          1.4         0.2     setosa
# 2        43          4.4         3.2          1.3         0.2     setosa
# 3        51          7.0         3.2          4.7         1.4 versicolor

Пакет RODBC предоставляет автономный интерфейс для подключения и работы с СУБД через ODBC интерфейс.

RODBC не совместим с DBI, т.е. вы не можете использовать объект подключения созданный с помощью RODBC в функциях предоставляемых пакетом DBI.


Основные функции пакета RODBC


  • odbcConnect — Подключение к СУБД через DSN;
  • odbcDriverConnect — Подключение к базе через строку подключения;
  • sqlQuery — Отправка запроса в СУБД, и получение результата его выполнения. Поддерживает запросы любого типа: SELECT, UPDATE, INSERT, DELETE.
  • sqlFetch — Получить целиком таблицу из СУБД;
  • sqlTables — Получить список таблиц в базе.
  • sqlSave — Создание новой таблицы в базе данных, или добавление новых данных в уже существующую таблицу;
  • sqlUpdate — Обновление данных в таблице которая уже существует в СУБД;
  • sqlDrop — Удаление таблицы в СУБД;
  • odbcClose — Завершение соединения с СУБД.

Пример работы с RODBC

С моей точки зрения RODBC менее функционален чем DBI, но в нём есть все необходимые функции для работы с СУБД.


Пример взаимодействия с СУБД через RODBC
# подключение пакета
library(RODBC)
# строка подключения
con_string <- odbcDriverConnect(connection = "Driver=SQL Server;Server=localhost;Database=mybase;UID=my_username;PWD=my_password;Port=1433")
# подключение через DSN
con_dsn    <- odbcConnect(dsn = "my_test_source",
                          uid = "my_username",
                          pwd = "my_password")

# создание таблицы в базе
sqlSave(con_dsn,
        dat       = iris,
        tablename = "iris")

# дописать строки в табдицу iris
sqlSave(con_dsn,
        dat       = iris,
        tablename = "iris",
        append    = TRUE)

# запрашиваем первые 4 строки
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

# вносим изменение в данные в R
iris[1, 5] <- "virginica"

# обновляем табицу в СУБД
sqlUpdate(con_dsn,
          dat       = iris,
          tablename = "iris")

# запрашиваем первые 4 строки после изменения данных
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

# удаление таблицы
sqlDrop(con_dsn, sqtable = "iris")

# разрыв соеденения с базой
odbcCloseAll()

По умолчанию транзакционность в RODBC выключена. Управление транзакциями осуществляется двумя функциями.


  • odbcSetAutoCommit — Переключение между обычным и транзакционным режимом работы с СУБД;
  • odbcEndTran — Подтверждение или отмена транзакции.

Включение и отключение транзакционного режима осуществляется функцией odbcSetAutoCommit с помощью аргумента autoCommit.


Примре работы в транзакционном режиме в RODBC
# подключение пакета
library(RODBC)
# подключение через DSN
con_dsn    <- odbcConnect(dsn = "my_test_source",
                          uid = "my_username",
                          pwd = "my_password")

# создание таблицы в базе
sqlSave(con_dsn,
        dat       = iris,
        tablename = "iris")

# включение транзакционного режима
odbcSetAutoCommit(con_dsn, autoCommit = FALSE)

# запрашиваем первые 4 строки
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

#   rownames SepalLength SepalWidth PetalLength PetalWidth Species
# 1        1         5.1        3.5         1.4        0.2  setosa
# 2        2         4.9        3.0         1.4        0.2  setosa
# 3        3         4.7        3.2         1.3        0.2  setosa
# 4        4         4.6        3.1         1.5        0.2  setosa

# вносим изменение в данные в R
iris[1, 5] <- "virginica"

# обновляем табицу в СУБД
sqlUpdate(con_dsn,
          dat       = iris,
          tablename = "iris")

# запрашиваем первые 4 строки
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

#   rownames SepalLength SepalWidth PetalLength PetalWidth   Species
# 1        1         5.1        3.5         1.4        0.2 virginica
# 2        2         4.9        3.0         1.4        0.2    setosa
# 3        3         4.7        3.2         1.3        0.2    setosa
# 4        4         4.6        3.1         1.5        0.2    setosa

# отменяем изменения
odbcEndTran(con_dsn, commit = FALSE)

# запрашиваем первые 4 строки
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

#   rownames SepalLength SepalWidth PetalLength PetalWidth Species
# 1        1         5.1        3.5         1.4        0.2  setosa
# 2        2         4.9        3.0         1.4        0.2  setosa
# 3        3         4.7        3.2         1.3        0.2  setosa
# 4        4         4.6        3.1         1.5        0.2  setosa

# обновляем табицу в СУБД
sqlUpdate(con_dsn,
          dat       = iris,
          tablename = "iris")

# применяем изменения
odbcEndTran(con_dsn, commit = TRUE)

# запрашиваем первые 4 строки после изменения данных
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

#   rownames SepalLength SepalWidth PetalLength PetalWidth   Species
# 1        1         5.1        3.5         1.4        0.2 virginica
# 2        2         4.9        3.0         1.4        0.2    setosa
# 3        3         4.7        3.2         1.3        0.2    setosa
# 4        4         4.6        3.1         1.5        0.2    setosa

# разрыв соеденения с базой
odbcClose(con_dsn)

Два описанных в статье метода работы с базами данных на языке R, DBI и RODBC, достаточно универсальны, и будут работать практически с любой СУБД.

Единственная разница в работе между различными СУБД заключается в процессе подключения. Для большинства популярных СУБД существуют отдельные пакеты которые являются драйверами. Для остальных СУБД необходимо настраивать подключение через ODBC интерфейс используя пакеты odbc или RODBC. Все остальные манипуляции, вне зависимости от выбранной вами СУБД, будут неизменны. Исключением является отправка SQL запросов, в зависимости от SQL диалекта который поддерживается СУБД с которой вы работаете.

P.S.
Если вы дочитали стататью, то наверняка интересуетесь языком R, в таком случае думаю вам будет интересен мой телеграм и youtube каналы, большая часть контента которых посвящена языку R. Подписывайтесь.

SQL или NoSQL — вот в чём вопрос / Блог компании RUVDS.com / ХабрВсе мы знаем, что в мире технологий баз данных существует два основных направления: SQL и NoSQL, реляционные и нереляционные базы данных. Различия между ними заключаются в том, как они спроектированы, какие типы данных поддерживают, как хранят информацию.

Реляционные БД хранят структурированные данные, которые обычно представляют объекты реального мира. Скажем, это могут быть сведения о человеке, или о содержимом корзины для товаров в магазине, сгруппированные в таблицах, формат которых задан на этапе проектирования хранилища.

Нереляционные БД устроены иначе. Например, документо-ориентированные базы хранят информацию в виде иерархических структур данных. Речь может идти об объектах с произвольным набором атрибутов. То, что в реляционной БД будет разбито на несколько взаимосвязанных таблиц, в нереляционной может храниться в виде целостной сущности.

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

Какую технологию выбрать? Ответ на этот вопрос зависит от особенностей проекта, о котором идёт речь.

О выборе SQL-баз данных


Не существует баз данных, которые подойдут абсолютно всем. Именно поэтому многие компании используют и реляционные, и нереляционные БД для решения различных задач. Хотя NoSQL-базы стали популярными благодаря быстродействию и хорошей масштабируемости, в некоторых ситуациях предпочтительными могут оказаться структурированные SQL-хранилища. Вот две причины, которые могут послужить поводом для выбора SQL-базы:
  1. Необходимость соответствия базы данных требованиям ACID (Atomicity, Consistency, Isolation, Durability — атомарность, непротиворечивость, изолированность, долговечность). Это позволяет уменьшить вероятность неожиданного поведения системы и обеспечить целостность базы данных. Достигается подобное путём жёсткого определения того, как именно транзакции взаимодействуют с базой данных. Это отличается от подхода, используемого в NoSQL-базах, которые ставят во главу угла гибкость и скорость, а не 100% целостность данных.
  2. Данные, с которыми вы работаете, структурированы, при этом структура не подвержена частым изменением. Если ваша организация не находится в стадии экспоненциального роста, вероятно, не найдётся убедительных причин использовать БД, которая позволяет достаточно вольно обращаться с типами данных и нацелена на обработку огромных объёмов информации.

О выборе NoSQL-баз данных


Если есть подозрения, что база данных может стать узким местом некоего проекта, основанного на работе с большими объёмами информации, стоит посмотреть в сторону NoSQL-баз, которые позволяют то, чего не умеют реляционные БД.

Вот возможности, которые стали причиной популярности таких NoSQL баз данных, как MongoDB, CouchDB, Cassandra, HBase:

  1. Хранение больших объёмов неструктурированной информации. База данных NoSQL не накладывает ограничений на типы хранимых данных. Более того, при необходимости в процессе работы можно добавлять новые типы данных.
  2. Использование облачных вычислений и хранилищ. Облачные хранилища — отличное решение, но они требуют, чтобы данные можно было легко распределить между несколькими серверами для обеспечения масштабирования. Использование, для тестирования и разработки, локального оборудования, а затем перенос системы в облако, где она и работает — это именно то, для чего созданы NoSQL базы данных.
  3. Быстрая разработка. Если вы разрабатываете систему, используя agile-методы, применение реляционной БД способно замедлить работу. NoSQL базы данных не нуждаются в том же объёме подготовительных действий, которые обычно нужны для реляционных баз.

В следующем разделе рассмотрим некоторые различия между технологиями SQL и NoSQL. А именно, сначала взглянем на простой пример, показывающий фундаментальное различие двух подходов к организации баз данных, потом поговорим о масштабируемости и индексации данных. А в итоге остановимся на примере большой CRM-системы, нуждающейся в высокой производительности хранилища данных.

SQL и NoSQL


Начнём с некоторых ключевых концепций реляционных и нереляционных баз данных. Ниже показана база данных, содержащая сведения о взаимоотношениях людей. Вариант a — это бессхемная структура, построенная в виде графа, характерная для NoSQL-решений. Вариант b показывает, как те же данные можно представить в структурированном виде, типичном для SQL.

Два варианта представления данных

Бессхемность означает, что два документа в структуре данных NoSQL не должны иметь одинаковые поля и могут хранить данные разных типов. Вот, например, массив объектов, набор полей которых не совпадает.

var cars = [
{ Model: "BMW", Color: "Red", Manufactured: 2016 },
{ Model: "Mercedes", Type: "Coupe", Color: "Black", Manufactured: "1-1-2017" }
];

При реляционном подходе данные надо хранить в заранее спроектированной структуре, из которой эти данные потом можно извлекать. Например, используя оператор JOINпри выборке из двух таблиц:
SELECT Orders.OrderID, Customers.Name, Orders.Date
FROM Orders
INNER JOIN Customers
ON Orders.CustID = Customers.CustID

Как более продвинутый пример, для демонстрации того, когда SQL предпочтительнее NoSQL, рассмотрим особенности применения в NoSQL-базах алгоритмов уплотнения. Проблема заключается в том, что в некоторых NoSQL-базах (например, в CouchDB и HBase) постоянно приходится формировать так называемые sstables — строковые таблицы в формате ключ-значение, отсортированные по ключу. В такие таблицы, которые сохраняются на диск, данные попадают из таблиц, хранящихся в памяти, при их переполнении и в других ситуациях. При интенсивной работе с базой создание таблиц, со временем, приводит к тому, что подсистема ввода-вывода устройства хранения данных становится узким местом для операций чтения данных. Как результат, чтение в NoSQL-базе происходит медленнее, чем запись, что сводит на нет одно из главных преимуществ нереляционных баз данных. Именно для того, чтобы уменьшить этот эффект, системы NoSQL используют, в фоновом режиме, алгоритмы уплотнения данных, пытаясь объединить множество таблиц в одну. Но и сама по себе эта операция весьма ресурсоёмка, система работает под повышенной нагрузкой.

Масштабируемость


Одно из основных различий рассматриваемых технологий заключается в том, что NoSQL-базы лучше поддаются масштабированию. Например, в MongoDB имеется встроенная поддержка репликации и шардинга (горизонтального разделения данных) для обеспечения масштабируемости. Хотя масштабирование поддерживается и в SQL-базах, это требует гораздо больших затрат человеческих и аппаратных ресурсов.
Тип хранилища данных
Сценарий использования
Пример
Рекомендации
Хранилище типа ключ-значение
Подходит для простых приложений, с одним типом объектов, в ситуациях, когда поиск объектов выполняют лишь по одному атрибуту.
Интерактивное обновление домашней страницы пользователя в Facebook.
Рекомендовано знакомство с технологией memcached.
Если приходится искать объекты по нескольким атрибутам, рассмотрите вариант перехода к хранилищу, ориентированному на документы.
Хранилище, ориентированное на документы
Подходит для хранения объектов различных типов.
Транспортное приложение, оперирующее данными о водителях и автомобилях, работая с которым надо искать объекты по разным полям, например — имя или дата рождения водителя, номер прав, транспортное средство, которым он владеет.
Подходит для приложений, в ходе работы с которыми допускается реализация принципа «согласованность в конечном счёте» с ограниченными атомарностью и изоляцией. Рекомендуется применять механизм кворумного чтения для обеспечения своевременной атомарной непротиворечивости.
Система хранения данных с расширяемыми записями
Более высокая пропускная способность и лучшие возможности параллельной обработки данных ценой слегка более высокой сложности, нежели у хранилищ, ориентированных на документы.
Приложения, похожие на eBay. Вертикальное и горизонтальное разделение данных для хранения информации клиентов.
Для упрощения разделения данных используются HBase или Hypertable.
Масштабируемая RDBMS
Использование семантики ACID освобождает программистов от необходимости работать на достаточно низком уровне, а именно, отвечать за блокировки и непротиворечивость данных, обрабатывать устаревшие данные, коллизии.
Приложения, которым не требуются обновления или слияния данных, охватывающие множество узлов.
Стоит обратить внимание на такие системы, как MySQL Cluster, VoltDB, Clustrix, ориентированные на улучшенное масштабирование.

Более подробное сравнение SQL и NoSQL можно найти в этом материале. Вот его основные положения. А именно, были проведены испытания трёх основных характеристик систем: параллельная обработка данных, работа с хранилищами информации, репликация данных. Возможности параллельной обработки оценивались путём анализа механизмов блокировки, управления параллельным доступом на основе многоверсионности, и ACID. Тестирование хранилищ охватывало и физические носители, и хранилища использующие оперативную память. Репликацию испытывали в синхронном и асинхронном режимах.

Используя данные, полученные в ходе испытаний, авторы делают выводы о том, что SQL-базы с возможностью кластеризации показали многообещающие результаты производительности в расчёте на один узел, и, кроме того, обладают способностью масштабируемости, что даёт системам RDBMS преимущество перед NoSQL за счёт полного соответствия принципам ACID.

Индексация


В системах RDBMS индексация используется для ускорения операций извлечения данных из баз. Отсутствие индекса означает, что таблица должна быть просмотрена целиком для того, чтобы выполнить запрос на чтение.

И в SQL, и в NoSQL-базах индексы служат одной и той же цели — ускорить и оптимизировать извлечение данных. Но то, как именно они работают — различается из-за разных архитектур баз данных и особенностей хранения информации в базе. В то время, как SQL-индексы представлены в виде B-деревьев, которые отражают иерархическую структуру реляционных данных, в NoSQL базах данных они указывают на документы, или на части документов, между которыми, в основном, нет никаких отношений. Вот подробный материал на эту тему.

CRM-системы


CRM-приложения — это один из лучших примеров систем, для которых характерны огромные объёмы ежедневно обрабатываемых данных и очень большое количество транзакций. Все разработчики таких приложений используют и SQL, и NoSQL базы данных. И, хотя большая часть данных транзакций всё ещё хранится в SQL-базах, применение находят общедоступные системы класса DBaaS (data-base-as-a-service, база данных как сервис), наподобие AWS DynamoDB и Azure DocumentDB, в результате, серьёзная нагрузка по обработке данных может быть перенесена в облачные NoSQL-базы.

В то время, как использование подобных служб освобождает разработчика от решения задач по обслуживанию хранилищ, это, кроме того, область, где NoSQL базы применяются для того, для чего они, в основном, и были созданы, например, для глубинного анализа данных. Объёмы информации, хранимой в огромных CRM-системах финансовых и телекоммуникационных компаний, было бы практически невозможно проанализировать, используя инструменты вроде SAS или R. Это потребовало бы огромных аппаратных ресурсов.

Главное преимущество таких систем — использование неструктурированных данных, похожих на документы. Такие данные могут подаваться на вход статистических моделей, которые дают компаниям возможность выполнять различные виды анализа. CRM-приложения, кроме того, являются весьма удачным примером, в котором две системы баз данных выступают не конкурентами, а существуют в гармонии, играя каждая свою роль в большой архитектуре управления данными.

Итоги


Занимаясь поиском системы управления базами данных, можно выбрать одну технологию, а позже, уточнив требования, переключиться на что-то другое. Однако, разумное планирование позволит сэкономить немало времени и средств.

Вот признаки проектов, для которых идеально подойдут SQL-базы:

  • Имеются логические требования к данным, которые могут быть определены заранее.
  • Очень важна целостность данных.
  • Нужна основанная на устоявшихся стандартах, хорошо зарекомендовавшая себя технология, используя которую можно рассчитывать на большой опыт разработчиков и техническую поддержку.

А вот свойства проектов, для которых подойдёт что-то из сферы NoSQL:
  • Требования к данным нечёткие, неопределённые, или развивающиеся с развитием проекта.
  • Цель проекта может корректироваться со временем, при этом важна возможность немедленного начала разработки.
  • Одни из основных требований к базе данных — скорость обработки данных и масштабируемость.

В итоге хочется сказать, что в современном мире нет противостояния между реляционными и нереляционными базами данных. Вместо этого стоит говорить об их совместном использовании для решения задач, на которых та или иная технология показывает себя лучше всего. Кроме того, всё сильнее наблюдается интеграция этих технологий друг в друга. Например, Microsoft, Oracle и Teradata сейчас предлагают некоторые формы интеграции с Hadoop для подключения аналитических инструментов, основанных на SQL, к миру неструктурированных больших данных.

Уважаемые читатели, а вам приходилось выбирать системы управления базами данных для собственных проектов? Если да — поделитесь пожалуйста опытом, расскажите, что и почему вы в итоге выбрали.

Что такое база данных? Что такое SQL?

Guru99
  • Home
  • Тестирование

      9000 9000
    • J0005
    • 000
    • Ручное тестирование
    • Мобильное тестирование
    • S000S0005 Управление тестированием
    • S0005
  • SAP

      • Назад
      • ABAP
      • APO 9000 5
      • Новичок
      • Базис
      • БПК
      • 9000 9000
      • Все 9000
        • Назад
        • PI / PO
        • PP
        • SD
        • Solution Manager
        • SAPUI5
        • Безопасность
        • Successfactors
        • SAP Обучение
    • веб

        • Назад
        • Apache
        • Android
        • AngularJS
        • ASP.Чистая
        • C
        • C #
        • C ++
        • CodeIgniter
        • СУБД
        • Назад
        • Java
        • JavaScript
        • JSP
        • Kotlin
        • M000 M000 js
        • Back
        • Perl
        • PHP
        • PL / SQL
        • PostgreSQL
        • Python
        • ReactJS
        • Ruby & Rails
        • Scala
        • SQL5000
        • SQL000
        • UML
        • VB.Net
        • VBScript
        • Веб-сервисы
        • WPF
    • Необходимо учиться!

        • Назад
        • Учет
        • Алгоритмы
        • Blockchain
        • Бизнес-аналитик
        • Сложение Сайт
        • CCNA
        • Cloud Computing
        • COBOL
        • Compiler Design
        • Embedded Systems
        • Назад
        • Ethical Hacking
        • Excel Учебники
        • Go Программирование
        • IoT
        • ITIL
        • Дженкинс
        • MIS
        • Networking
        • Операционная система
        • Prep
        • Назад
        • PMP
        • Photoshop Управление
        • Проект
        • Отзывы
        • Salesforce
        • SEO
        • Разработка программного обеспечения
        • VBA
    • Big Data

        • Назад
        • AWS
        • BigData
        • Cassandra
        • Cognos
        • Складирование данных
        • 000000000 HBB000500040005000 HB
        • MongoDB
        • NiFi
        • OBIEE
        • Pentaho
        • Назад
    ,
    20 лучших инструментов управления SQL (база данных) в 2020 году [бесплатно / платно] Guru99
    • Home
    • Testing

        • Back
        • Agile Testing
        • BugZilla
        • Cucumber
        • Testing
        • Testing
        • Тестирование базы данных
        • Тестирование базы данных
        • Jmeter
        • JIRA
        • Назад
        • JUnit
        • LoadRunner
        • Ручное тестирование
        • Mobile Тестирование
        • Mantis
        • Почтальон
        • QTP
        • Назад
        • Quality Center (ALM)
        • RPA
        • Тестирование SAP
        • Selenium
        • SoapUI
        • Управление тестированием
        • TestLink
    • SAP

        • Назад
        • ABAP
        • APO
        • Новичок
        • Базис
        • BODS
        • BI
        • BPC
        • CO
        • Назад
        • CRM
        • HMS
        • кристалл
        • кристаллов
        • 50005 кристаллов кристаллов
        • QM
        • Расчет заработной платы
        • Назад
        • PI / PO
        • PP
        • SD
        • SAPUI5
        • Безопасность
        • Solution Manager
        • Successfactors
        • SAP Обучение
    • веб

        • Назад
        • Apache
        • Android
        • AngularJS
        • ASP.Чистая
        • C
        • C #
        • C ++
        • CodeIgniter
        • СУБД
        • Назад
        • Java
        • JavaScript
        • JSP
        • Kotlin
        • M000 M000 js
        • Back
        • Perl
        • PHP
        • PL / SQL
        • PostgreSQL
        • Python
        • ReactJS
        • Ruby & Rails
        • Scala
        • SQL5000
        • SQL000
        • UML
        • VB.Net
        • VBScript
        • Веб-сервисы
        • WPF
    • Необходимо учиться!

        • Назад
        • Учет
        • Алгоритмы
        • Blockchain
        • Бизнес-аналитик
        • Сложение Сайт
        • CCNA
        • Cloud Computing
        • COBOL
        • Compiler Design
        • Embedded Systems
        • Назад
        • Ethical Hacking
        • Excel Учебники
        • Go Программирование
        • IoT
        • ITIL
        • Дженкинс
        • MIS
        • Networking
        • Операционная система
        • Prep
        • Назад
        • PMP
        • Photoshop Управление
        • Проект
        • Отзывы
        • Salesforce
        • SEO
        • Разработка программного обеспечения
        • VBA
    • Big Data

        • Назад
        • AWS
        • BigData
        • Cassandra
        • Cognos
        • Складирование данных
        • 000000000 HBB000500040005000 HB
        • MongoDB
        • NiFi
        • OBIEE
        • Pentaho
        • Назад
    ,

    SQL Введение


    SQL — это стандартный язык для доступа к базам данных и управления ими.


    Что такое SQL?

    • SQL означает язык структурированных запросов
    • SQL позволяет обращаться к базам данных и манипулировать ими
    • SQL стал стандартом Американского национального института стандартов (ANSI) в 1986 году и Международной организации по стандартизации (ИСО) в 1987

    Что может делать SQL?

    • SQL может выполнять запросы к базе данных
    • SQL может извлекать данные из базы данных
    • SQL может вставлять записи в базу данных
    • SQL может обновлять записи в базе данных
    • SQL может удалять записи из базы данных
    • SQL может создавать новые базы данных
    • SQL может создавать новые таблицы в базе данных
    • SQL может создавать хранимые процедуры в базе данных
    • SQL может создавать представления в базе данных
    • SQL может устанавливать разрешения для таблиц, процедур и представлений

    SQL это стандарт — НО….

    Хотя SQL является стандартом ANSI / ISO, существуют разные версии языка SQL.

    Однако, чтобы соответствовать стандарту ANSI, все они поддерживают, по крайней мере, основные команды (такие как SELECT, UPDATE, DELETE, INSERT, WHERE) аналогичным образом.

    Примечание: Большинство программ баз данных SQL также имеют собственные проприетарные расширения в дополнение к стандарту SQL!


    Использование SQL на вашем веб-сайте

    Для создания веб-сайта, который показывает данные из базы данных, вам потребуется:

    • Программа базы данных RDBMS (т.е.е. MS Access, SQL Server, MySQL)
    • Для использования языка сценариев на стороне сервера, таких как PHP или ASP
    • Чтобы использовать SQL, чтобы получить нужные данные
    • Чтобы использовать HTML / CSS для оформления страницы

    RDBMS

    СУБД

    означает систему управления реляционными базами данных.

    СУБД

    является основой для SQL и для всех современных систем баз данных, таких как MS SQL Server, IBM DB2, Oracle, MySQL и Microsoft Access.

    Данные в СУБД хранятся в объектах базы данных, называемых таблицами.Таблица представляет собой набор связанных записей данных и состоит из столбцов и строк.

    Посмотрите на таблицу «Клиенты»:

    Каждая таблица разбита на более мелкие объекты, называемые полями. Поля в таблица Customers состоит из CustomerID, CustomerName, ContactName, Address, Город, Индекс и Страна. Поле — это столбец в таблице, предназначенный для конкретная информация о каждой записи в таблице.

    Запись, также называемая строкой, представляет собой каждую отдельную запись в таблице.Например, в приведенной выше таблице «Клиенты» 91 запись. Запись является горизонтальная сущность в таблице.

    Столбец — это вертикальная сущность в таблице, которая содержит всю информацию связан с конкретным полем в таблице.


    ,

    SQL Tutorial

    SQL — это стандартный язык для хранения, обработки и извлечения данных в базах данных.

    Наш учебник по SQL научит вас, как использовать SQL в: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres и другие системы баз данных.

    Начните изучать SQL сейчас »

    примеров в каждой главе

    С помощью нашего онлайн-редактора SQL вы можете редактировать операторы SQL и нажимать кнопку, чтобы просмотреть результат.

    Нажмите на кнопку «Попробуйте сами», чтобы увидеть, как это работает.


    Упражнения SQL



    SQL Примеры

    Учись на примерах! Этот учебник дополняет все пояснения поясняющими примерами.

    См. Все примеры SQL


    SQL Quiz Test

    Проверьте свои навыки SQL в W3Schools!

    Запустите SQL Quiz!


    Ссылки на SQL

    В W3Schools вы найдете полную ссылку на ключевые слова и функции:

    Справочник по ключевым словам SQL

    MYSQL Функции

    Функции SQLServer

    MS Access Функции

    SQL Quick Reference


    типов данных SQL

    Типы данных и диапазоны для Microsoft Access, MySQL и SQL Server.

    Типы данных SQL


    экзамен по SQL — получите диплом!

    W3Schools Certification

    Онлайн-сертификация W3Schools

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

    Уже выпущено более 25 000 сертификатов!

    Получите сертификат »

    Сертификат HTML подтверждает ваши знания HTML.

    Сертификат CSS документирует ваше знание продвинутого CSS.

    Сертификат JavaScript подтверждает ваши знания JavaScript и HTML DOM.

    Сертификат Python подтверждает ваши знания Python.

    Сертификат jQuery подтверждает ваши знания о jQuery.

    Сертификат SQL подтверждает ваши знания SQL.

    Сертификат PHP подтверждает ваши знания PHP и MySQL.

    Сертификат XML документирует ваши знания XML, XML DOM и XSLT.

    Сертификат Bootstrap документирует ваши знания о среде Bootstrap.


    ,
  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *