Изучение особенностей СУБД MS SQL и языка запросов Transact-SQL

Размещено на http://www.allbest.ru/

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ЖЕЛЕЗНОДОРОЖНОГО ТРАНСПОРТА

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

«ОМСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ» (ОмГУПС (ОмИИТ))

Кафедра «Информационная безопасность»

ОТЧЕТ

по технологической практике 1

Место прохождения технологической практики:

АО «ОмскРТС»

Студент гр. 23с

___________ А.В. Ильин

Руководитель технологической практики

доцент кафедры ИБ Мызникова Т. А.

Оценка: _____________________

________________Т.А. Мызникова

2015/2016 учебный год

Содержание

• Введение
• 1. Изучение РСУБД Microsoft SQL Server
• 2. Изучение средств языка запросов Transact-SQL
• 3. Написание программы для сбора статистики изменений в базе данных Omni-US
• Библиографический список
• Приложение

Введение

Я проходил практику в АО «ОмскРТС», располагающегося по адресу: ул. Партизанская, 10. Практика проходила в отделе информационных технологий с 18.07.16г. по 31 .07.16г. под непосредственным руководством руководителя направления сбытовых информационных систем Заславского М.В. За время практики я ознакомился с особенностями СУБД MS SQL и языком запросов Transact-SQL. Полученные в результате прохождения практики знания и данные представлены в отчете.

Отчет состоит из четырех разделов. В первом разделе дана общая характеристика СУБД MS SQL Server. Второй раздел посвящен изучению средств языка Transact-SQL. Здесь рассматриваются основные задачи, функции языка запросов. В третьем разделе описывается разработка программы, написанная в ходе прохождения технологической практики. По итогу прохождения практики выдана характеристика, которая приложена к отчету.

1. Изучение РСУБД Microsoft SQL Server

программный запрос база данные

Microsoft SQL Server -- система управления реляционными базами данных (РСУБД), разработанная корпорацией Microsoft. Основной используемый язык запросов -- Transact-SQL, создан совместно Microsoft и Sybase. Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. Используется для работы с базами данных размером от персональных до крупных баз данных масштаба предприятия; конкурирует с другими СУБД в этом сегменте рынка.

Таблица 1 -- История выпусков SQL Server

Версия	Год	Название	Кодовое имя
1.0 (OS/2)	1989	SQL Server 1.0 (16 bit)	Ashton-Tate / MS SQL Server
1.1 (OS/2)	1991	SQL Server 1.1 (16 bit)	-
4.21 (WinNT)	1993	SQL Server 4.21	SQLNT
6.0	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7.0	1998	SQL Server 7.0	Sphinx
-	1999	SQL Server 7.0 OLAP Tools	Palato mania
8.0	2000	SQL Server 2000	Shiloh
8.0	2003	SQL Server 2000 64-bit	Liberty
9.0	2005	SQL Server 2005	Yukon
10.0	2008	SQL Server 2008	Katmai
10.25	2010	Azure SQL DB	Cloud Database or CloudDB
10.50	2010	SQL Server 2008 R2	Kilimanjaro (aka KJ)
11.0	2012	SQL Server 2012	Denali
12.0	2014	SQL Server In-Memory OLTP	Hekaton
14	2016	SQL Server 2016

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

SQL Server 2014 также предоставляет новые решения для аварийного восстановления, резервного копирования и гибридной архитектуры в Windows Azure, позволяя клиентам использовать существующие навыки работы с локальными функциями, опирающимися на возможности глобальных центров обработки данных Microsoft. Кроме того, в SQL Server 2014 используются новые возможности Windows Server 2012 и Windows Server 2012 R2, дающие несравненную масштабируемость для приложений баз данных в физических и виртуальных средах.

2. Изучение средств языка запросов Transact-SQL

Transact-SQL (T-SQL) -- процедурное расширение языка SQL, созданное компанией Microsoft (для Microsoft SQL Server) и Sybase (для Sybase ASE).

SQL был расширен такими дополнительными возможностями как:

управляющие операторы;

локальные и глобальные переменные;

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

поддержка аутентификации Microsoft Windows.

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

SELECT *

FROM SD_Conn_Points AS scp

JOIN SD_Subscr AS ss

ON ss.F_Conn_Points = scp.LINK

JOIN PE.FD_Charges AS fc

ON fc.F_Subscr = ss.LINK

WHERE ss.B_EE = 0 AND fc.N_Period IN (201601, 201602, 201603)

Листинг 1 -- Пример запроса на языке Transact-SQL

Директивы сценария -- это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO -- информирует программы SQL Server об окончании пакета инструкций Transact-SQL, EXEC (или EXECUTE) -- выполняет процедуру или скалярную функцию.

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

-- -- строковый комментарий исключает из выполнения только одну строку, перед которой стоят два минуса.

/* */ -- блоковый комментарий исключает из выполнения целый блок команд, заключённый в указанную конструкцию.

Как и в языках программирования, в SQL существуют различные типы данных для хранения переменных:

Числа -- для хранения числовых переменных (bit, int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).

Даты -- для хранения даты и времени (datetime, smalldatetime).

Символы -- для хранения символьных данных (char, nchar, varchar, nvarchar).

Двоичные -- для хранения бинарных данных (binary, varbinary).

Большеобъемные -- типы данных для хранения больших бинарных данных (text, ntext, image).

Специальные -- указатели (cursor), 16-байтовое шестнадцатеричное число, которое используется для GUID (uniqueidentifier), штамп изменения строки (timestamp), версия строки (rowversion), таблицы (table).

Примечание. Для использования русских символов (не ASCII кодировки) используются типы данных с приставкой «n» (nchar, nvarchar, ntext), которые кодируют символы двумя байтами. Иначе говоря, для работы с Unicode используются типы данных с «n» (от слова national).

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

Идентификаторы -- это специальные символы, которые используются с переменными для идентифицирования их типа или для группировки слов в переменную. Типы идентификаторов:

@ -- идентификатор локальной переменной (пользовательской).

@@ -- идентификатор глобальной переменной (встроенной).

# -- идентификатор локальной таблицы или процедуры.

## -- идентификатор глобальной таблицы или процедуры.

[ ] -- идентификатор группировки слов в переменную.

Переменные используются в сценариях и для хранения временных данных. Чтобы работать с переменной, её нужно объявить, при том объявление должно быть осуществлено в той транзакции, в которой выполняется команда, использующая эту переменную. Иначе говоря, после завершения транзакции, то есть после команды GO, переменная уничтожается.

Объявление переменной выполняется командой DECLARE, задание значения переменной осуществляется либо командой SET, либо SELECT.

DECLARE @sum1 MONEY = 0;

DECLARE @sum2 MONEY = 0;

DECLARE @sum3 MONEY = 0;

DECLARE @oto MONEY = 0;

DECLARE @gvs MONEY = 0;

DECLARE @peni MONEY = 0;

DECLARE cursor1 CURSOR

FOR

SELECT SUM(CASE WHEN fsi.C_Code = 31 THEN fcd.N_Amount ELSE 0 END) AS 'Отопление',

SUM(CASE WHEN fsi.C_Code = 32 THEN fcd.N_Amount ELSE 0 END) AS 'ГВС',

SUM(CASE WHEN fsi.C_Code = 1123 THEN fcd.N_Amount ELSE 0 END) AS 'Пени'

FROM SD_Conn_Points AS scp

JOIN SD_Subscr AS ss

ON ss.F_Conn_Points = scp.LINK

JOIN PE.FD_Charges AS fc

ON fc.F_Subscr = ss.LINK

JOIN pe.FVT_Charge_Details AS fcd

ON fcd.F_Charges = fc.LINK

JOIN FS_Sale_Items AS fsi

ON fsi.LINK = fcd.F_Sale_Items

WHERE ss.B_EE = 0

AND fc.N_Period IN (201601, 201602, 201603)

AND fsi.C_Code IN (31, 32, 1123)

GROUP BY

scp.C_Address_Short

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @oto, @gvs, @peni

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @sum1 = @sum1 + @oto

SELECT @sum2 = @sum2 + @gvs

SELECT @sum3 = @sum3 + @peni

FETCH NEXT FROM cursor1 INTO @oto, @gvs, @peni

END

SELECT 'Отопление: ' + CAST(@sum1 AS VARCHAR(15)) + ' руб'

+ 'ГВС: ' + CAST(@sum2 AS VARCHAR(15)) + ' руб'

+ 'Пени: ' + CAST(@sum3 AS VARCHAR(15)) + ' руб'

CLOSE cursor1

Листинг 2 -- Пример запроса с использованием переменных

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

Хранимые процедуры похожи на определяемые пользователем функции (UDF). Основное различие заключается в том, что пользовательские функции можно использовать как и любое другое выражение в SQL запросе, в то время как хранимые процедуры должны быть вызваны с помощью функции CALL или EXECUTE.

Хранимые процедуры могут возвращать множества результатов, то есть результаты запроса SELECT. Такие множества результатов могут обрабатываться, используя курсоры, другими сохраненными процедурами, возвращая указатель результирующего множества, либо же приложениями. Хранимые процедуры могут также содержать объявленные переменные для обработки данных и курсоров, которые позволяют организовать цикл по нескольким строкам в таблице. Стандарт SQL предоставляет для работы выражения IF, LOOP, REPEAT, CASE и многие другие. Хранимые процедуры могут принимать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, где переменная объявлена.

Реализация хранимых процедур варьируется от одной СУБД к другой. Большинство крупных поставщиков баз данных поддерживают их в той или иной форме. В зависимости от СУБД, хранимые процедуры могут быть реализованы на различных языках программирования, таких, как SQL, Java, C или C++. Хранимые процедуры написанные не на SQL могут самостоятельно выполнять SQL-запросы, а могут и не выполнять. Все более широкое использование хранимых процедур привело к появлению процедурных элементов в языке SQL стандарта SQL:1999 и SQL: 2003 в части SQL/PSM. Это сделало SQL императивным языком программирования. Большинство СУБД предлагают собственные проприетарные и расширения производителя, сверх SQL/PSM.

USE [OmnisOFCopy]

GO

/ Object: UserDefinedFunction [dbo].[CF_Date_Begin] Script Date: 21.07.2016 10:10:49 /

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER FUNCTION [dbo].[CF_Show_Charges]

(

)

RETURNS VARCHAR(100)

WITH

EXECUTE AS CALLER

AS

BEGIN

DECLARE @sum1 MONEY = 0;

DECLARE @sum2 MONEY = 0;

DECLARE @sum3 MONEY = 0;

DECLARE @oto MONEY = 0;

DECLARE @gvs MONEY = 0;

DECLARE @peni MONEY = 0;

DECLARE cursor1 CURSOR

FOR

SELECT SUM(CASE WHEN fsi.C_Code = 31 THEN fcd.N_Amount ELSE 0 END) AS 'Отопление',

SUM(CASE WHEN fsi.C_Code = 32 THEN fcd.N_Amount ELSE 0 END) AS 'ГВС',

SUM(CASE WHEN fsi.C_Code = 1123 THEN fcd.N_Amount ELSE 0 END) AS 'Пени'

FROM SD_Conn_Points AS scp

JOIN SD_Subscr AS ss

ON ss.F_Conn_Points = scp.LINK

JOIN PE.FD_Charges AS fc

ON fc.F_Subscr = ss.LINK

JOIN pe.FVT_Charge_Details AS fcd

ON fcd.F_Charges = fc.LINK

JOIN FS_Sale_Items AS fsi

ON fsi.LINK = fcd.F_Sale_Items

WHERE ss.B_EE = 0

AND fc.N_Period IN (201601, 201602, 201603)

AND fsi.C_Code IN (31, 32, 1123)

GROUP BY

scp.C_Address_Short

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @oto, @gvs, @peni

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @sum1 = @sum1 + @oto

SELECT @sum2 = @sum2 + @gvs

SELECT @sum3 = @sum3 + @peni

FETCH NEXT FROM cursor1 INTO @oto, @gvs, @peni

END

--SELECT 'Отопление: ' + CAST(@sum1 AS VARCHAR(15)) + ' руб'

-- + ' ГВС: ' + CAST(@sum2 AS VARCHAR(15)) + ' руб'

-- + ' Пени: ' + CAST(@sum3 AS VARCHAR(15)) + ' руб'

CLOSE cursor1

RETURN 'Отопление: ' + CAST(@sum1 AS VARCHAR(15)) + ' руб'

+ ' ГВС: ' + CAST(@sum2 AS VARCHAR(15)) + ' руб'

+ ' Пени: ' + CAST(@sum3 AS VARCHAR(15)) + ' руб'

END

GO

Листинг 3 -- Пример реализации хранимой процедуры

3. Написание программы для сбора статистики изменений в базе данных Omni-US.

Результатом изучения СУБД MS SQL стала разработка программы для сбора статистики в базе. Реализация программы написана на языке Python с использованием графического фреймворка wx. Выбор языка связан с рекомендацией руководителя практики от предприятия использовать именно этот язык программирования.

Рисунок 1 -- Внешний вид программы

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

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

По нажатию второй кнопки выведется статистика изменения функций или процедур по пользователям, а так же произойдет последующее сохранение в html-файл.



Рисунок 2 -- Список изменений за определенный период

Рисунок 3 -- Таблица изменений по пользователям

Заключение

В ходе выполнения работы был изучена СУБД MS SQL Server, язык обработки запросов Transact-SQL. Результатом работы стала программа сбора статистики изменений в базе данных Omni-US.

Библиографический список

1 Электронный ресурс: https://ru.wikipedia.org/wiki/Microsoft_SQL_Server

2 Электронный ресурс: https://ru.wikipedia.org/wiki/Transact-SQL

3 Электронный ресурс: http://omni-us.ru/

4 Электронный ресурс: https://msdn.microsoft.com/ru-ru/library/bb545450.aspx

Приложение A

Программа для сбора статистики изменений в базе данных Omni-US

# -*- coding: cp1251

import wx

from wx.lib import masked

from sqlalchemy import create_engine

DBAPI = 'mssql+pyodbc'

DBSERVER = 'dbomnius.enrg.local'

DBNAME = 'SysUse'

DRIVER = 'SQL+Server+Native+Client+11.0'

CONNECTION = '%s://%s/%s?driver=%s' % (DBAPI, DBSERVER, DBNAME, DRIVER)

engine = create_engine(CONNECTION, convert_unicode=True, encoding='utf-8', legacy_schema_aliasing=False)

class AppFrame(wx.Frame):

def __init__(self, parent, title):

wx.Frame.__init__(self, parent, title=title, size=(900, 480),

style=wx.DEFAULT_FRAME_STYLE & ~(wx.RESIZE_BORDER | wx.MAXIMIZE_BOX))

panel = wx.Panel(self)

wx.StaticText(panel, label='Период', pos=(50, 5))

wx.StaticText(panel, label='С:', pos=(20, 30))

wx.StaticText(panel, label='По:', pos=(20, 55))

wx.StaticText(panel, label='Тип изменения:', pos=(40, 150))

self.begin_date = masked.TextCtrl(panel, -1, '2014-11-14', mask='####-##-##', pos=(50, 25), size=(80, -1))

self.end_date = masked.TextCtrl(panel, -1, '2014-11-16', mask='####-##-##', pos=(50, 50), size=(80, -1))

self.button_1 = wx.Button(panel, wx.ID_OK, label='Поехали', pos=(20, 80), size=(140, 50))

self.button_2 = wx.Button(panel, wx.ID_OK, label='Показать статистику\nизменений по пользователям',

pos=(20, 200), size=(140, 80))

self.textbox = wx.TextCtrl(panel, pos=(180, 10), size=(700, 400),

style=wx.TE_MULTILINE | wx.TE_READONLY | wx.TE_DONTWRAP)

self.methods = ['All', 'ALTER', 'CREATE', 'DROP']

self.func = wx.ComboBox(panel, value='All', pos=(20, 170), size=(140, -1), choices=self.methods,

style=wx.CB_READONLY)

self.button_1.Bind(wx.EVT_BUTTON, self.onClick_1)

self.button_2.Bind(wx.EVT_BUTTON, self.onClick_2)

self.Show(True)

def onClick_2(self, e):

file = open('log.html', 'w')

file.write(

"<!DOCTYPE html>\n <html lang=\"en\">\n<head>\n<meta http-equiv=\"content-type\" "

"content=\"text/html;charset=cp1251\" />\n<title>Лог</title>\n</head>"

"<body>\n")

begin = str(self.begin_date.Value)

end = str(self.end_date.Value)

self.textbox.Clear()

with engine.connect() as con:

rs = con.execute(

"SELECT l.LoginName, COUNT(l.LoginName) "

"FROM dbo.Log AS l "

"WHERE l.D_Date "

"BETWEEN '" + begin + "T00:00:00.000' AND '" + end + "T23:59:59.999'"

" GROUP BY l.LoginName")

data = rs.fetchall()

self.textbox.AppendText("Количество изменений с " + begin + " по " + end + ":\n")

file.write("Количество изменений с " + begin + " по " + end + ":\n<br><table border=\"2\">\n")

file.write("<tr><th>Пользователь</th><th>Количество</th></tr>\n")

for item in data:

self.textbox.AppendText(item[0] + ' ' + str(item[1]) + "\n")

file.write("<tr align=\"center\"><td>" + item[0] + "</td>" + "<td>" + str(item[1]) + "</td></tr>\n")

file.write("</table></body>\n</html>")

file.close()

def onClick_1(self, e):

begin = str(self.begin_date.Value)

end = str(self.end_date.Value)

type = str(self.func.Value)

self.textbox.Clear()

if type == 'All':

with engine.connect() as con:

rs = con.execute(

"SELECT X_Data.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(50)'),"

"EventType,"

"ObjectName"

" FROM dbo.Log AS l"

" WHERE l.D_Date BETWEEN '" + begin + "T00:00:00.000' AND '" + end + "T23:59:59.999'")

data = rs.fetchall()

for item in data:

self.textbox.AppendText(

item[0].split('T')[0] + ' ' + item[0].split('T')[1] + ' ' + item[1] + ' ' + item[2].decode(

'cp1251') + "\n")

else:

with engine.connect() as con:

rs = con.execute(

"SELECT X_Data.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(50)'),"

"EventType,"

"ObjectName"

" FROM dbo.Log AS l"

" WHERE l.D_Date "

"BETWEEN '" + begin + "T00:00:00.000' AND '" + end + "T23:59:59.999' "

"AND l.EventType LIKE '" + type + "%'")

data = rs.fetchall()

for item in data:

self.textbox.AppendText(

item[0].split('T')[0] + ' ' + item[0].split('T')[1] + ' ' + item[1] + ' ' + item[2].decode(

'cp1251') + "\n")

app = wx.App(False)

frame = AppFrame(None, 'Лог базы данных')

app.MainLoop()

engine.dispose()

Размещено на Allbest.ru

...