Лабораторные работы БД. Отчет по лабораторной работе. 2 Используя ms access перенести полученную модель в бд, используя таблицы и схему данных
 Скачать 5.14 Mb.
|
|
Контрольные вопросы. 1. Для чего используется SQL Server Management Studio? Можно ли до- биться тех же целей без использования данной утилиты? 2. Опишите операцию подключения к серверу. 3. Перечислите операторы, используемые при создании базы данных с объяснением их действия. 4. Перечислите операторы, используемые при создании таблиц с объяс- нением их действия. 5. Как обратиться к нужной базе данных для выполнения действий с ней? 6. Необходимо ли сохранять коды запросов и почему? 7. Как в Microsoft SQL сервере создать поле-счетчик? Лабораторная работа №8(4) ОСВОЕНИЕ ПРОГРАММИРОВАНИЯ С ПОМОЩЬЮ ВСТРОЕННОГО ЯЗЫКА TRANSACT SQL В MICROSOFT SQL SERVER Цель работы – знакомство с основными принципами программирования в MS SQL Server средствами встроенного языка Transact SQL. Содержание работы: 1. Знакомство с правилами обозначения синтаксиса команд в справочной системе MS SQL Server. 2. Изучение правил написания программ на Transact SQL. 3. Изучение правил построения идентификаторов, правил объявления переменных и их типов. 4. Изучение работы с циклами и ветвлениями. 5. Изучение работы с переменными типа Table и Cursor. 6. Проработка всех примеров, анализ результатов их выполнения. 7. Выполнение индивидуальных заданий по вариантам. Пояснения к выполнению работы Для освоения программирования используем пример базы данных c названием DB_Books. При выполнении примеров и заданий обращайте внимание на соответствие названий БД, таблиц и других объектов проекта. Специальные знаки и простейшие операторы в Transact SQL Знак Назначение Знак Назначение * Знак умножения " " В них заключают строковые зна чения, если SET QUOTED_IDENTIFIER OFF - Знак вычитания ‘ ’ В них заключают строковые зна чения % Остаток от деления двух чисел <> Не равно + Знак сложения или конкатена ции (объединение двух строк в одну) [ ] Аналог кавычек, в них можно заключать названия идентифика торов, если в их названиях встречаются пробелы = Знак равенства или сравнения !< Не менее чем <= Меньше или равно !> Не более чем >= Больше или равно > Больше != Не равно < Меньше @ Ставится перед именем пере менной Разделяет родительские и подчи ненные объекты @@ Указывает на системные функции / Знак деления -- Однострочный комментарий или комментарий с текущей позиции и до конца строки /* */ Многострочный комментарий Идентификаторы – это имена объектов, на которые можно ссылаться в программе, написанной на языке Transact SQL. Первый символ может со стоять из букв английского алфавита или “_”, “@”, “#”. Остальные дополнительно из цифр и «$». Имя идентификатора не должно совпадать с зарезервированным словом. Для ограничителей идентификаторов при установленном параметре SET QUOTED_IDENTIFIER ON можно использовать как квадратные скобки, так и одинарные кавычки, а строковые значения только в одинарных кавычках (режим по умолчанию). Если использовать установленный параметр в режиме SET QUOTED_IDENTIFIER OFF, то в качестве ограничителей идентификаторов можно использовать только квадратные скобки, а строковые значения указываются в одинарных или двойных кавычках. Переменные используются для сохранения промежуточных данных в хранимых процедурах и функциях. Все переменные считаются локальными. Имя переменной должно начинаться с @. Объявление переменных Синтаксис в обозначениях MS SQL Server: DECLARE @имя_переменной_1 тип_переменной, …, @имя_переменной_N тип_переменной Если тип переменной предполагает указание размера, то используется следующий синтаксис для объявления переменных: DECLARE @имя_переменной_1 тип_переменной (размер), …, @имя_переменной_N тип_переменной(размер) Пример: DECLARE @a INT , @b NUMERIC ( 10 , 2 ) DECLARE @ str CHAR ( 20 ) Присвоение значений переменным и вывод значений на экран Присвоение с помощью SET – обычное присвоение, синтаксис: SET @имя_переменной = значение. Пример: DECLARE @a INT , @b NUMERIC ( 10 , 2 ) SET @a = 20 SET @b = ( @a + @a )/ 15 SELECT @b --вывод на экран результата Присвоение с помощью SELECT – помещение результата запроса в переменную. Если в результате выполнения запроса не будет возвращено ни одной строки, то значение переменной не меняется, т.е. остается старым. Пример: DECLARE @a INT SELECT @a = COUNT (*) FROM Authors Пример: DECLARE @ str CHAR ( 30 ) SELECT @ str = name FROM Authors В данном примере в переменную поместится последнее значение из результата запроса. Сочетание ключевых слов SET и SELECT Пример: DECLARE @a INT SET @a = ( SELECT COUNT (*) FROM Authors ) Работа с датой и временем Оператор SET DATEFORMAT dmy | ymd | mdy задает порядок следо вания компонентов даты. Пример: SET DATEF OR MAT dmy DECLARE @d DateTime SET @d = ’31.01.2005 13 : 23 : 15 ’ SET @d = @d + 1 SELECT @d Создание временной таблицы через переменную типа TABLE Объявляется через DECLARE с указанием в скобках столбцов табли цы, их типов, размеров, значений по умолчанию, а также индексов типа PRIMARY KEY или UNIQUE. Пример: DECLARE @mytable TABLE ( id INT , myname CHAR ( 20 ) DEFAULT ‘Введите имя’ ) INSERT INTO @mytable ( id ) VALUES ( 1 ) SELECT * FROM @mytable Пример: DECLARE @mytable TABLE ( id INT , myname CHAR ( 20 ) DEFAULT ‘Введите имя’ ) INSERT @mytable SELECT Code_publish, City FROM Publishing_house SELECT * FROM @mytable Преобразование типов переменных Функция CAST возвращает значение, преобразованное к указанному типу: CAST(@переменная или значение AS требуемый_тип_данных) Пример: DECLARE @d DateTime , @ str CHAR ( 20 ) SET @d = ’31.01.2005 13 : 23 : 15 ’ SET @ str = CAST ( @d AS CHAR ( 20 )) SELECT 2str Функция CONVERT возвращает значение, преобразованное к указан ному типу по заданному формату. Изучить дополнительно, по желанию. Операторские скобки BEGIN /* в них нельзя помещать команды, изменяющие структуры объектов БД. Операторские скобки должны содержать хотя бы один оператор. Требуются для конструкций поливариантных ветвлений, условных и циклических конструкций */ END Условная конструкция IF Синтаксис: IF условие Набор операторов1 ELSE Набор операторов2 Пример: DECLARE @a INT DECLARE @ str CHAR ( 30 ) SET @a = ( SELECT COUNT (*) FROM Authors ) IF @a > 10 BEGIN SET @ str = 'Количество авторов больше 10' SELECT @ str END ELSE BEGIN SET @ str = 'Количество авторов = ' + str ( @a ) SELECT @ str END Цикл WHILE Синтаксис: WHILE Условие Набор операторов1 BREAK Набор опреторов2 CONTINUE Конструкции BREAK и CONTINUE являются необязательными. Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выпол нения всех команд в теле, необходимо выполнить команду CONTINUE. Пример: DECLARE @a INT SET @a = 1 WHILE @a < 100 BEGIN @a - вывод на экран значения переменной IF ( @a > 40 ) AND ( @a < 50 ) BREAK --выход и выполнение 1-й команды за циклом ELSE SET @a = @a + rand ()* 10 CONTINUE END @a Объявление курсора CURSOR – это набор строк, являющийся результатом выполнения за проса. В один момент времени доступна лишь одна строка (текущая), по курсору можно передвигаться и получать доступ к элементарным данным. При объявлении курсора создается временная копия данных, которая сохраняется в БД tempdb. Динамический курсор – данные в курсоре могут быть изменены. Статический курсор – данные в курсоре не меняются. Стандартный способ объявления курсора, синтаксис в обозначениях DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] Примеры объявления курсоров: DECLARE MyCursor1 CURSOR FOR ( SELECT * FROM Authors ) /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим.*/ DECLARE MyCursor1 IN SENSITIVE CURSOR FOR ( SELECT * FROM Authors ) /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является статическим.*/ DECLARE MyCursor1 SCROLL CURSOR FOR ( SELECT * FROM Authors ) /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно в любом направлении. Курсор является динамическим.*/ DECLARE MyCursor1 IN SENSITIVE SCROLL CURSOR FOR ( SELECT * FROM Authors ) /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно в любом направлении. Курсор является статическим.*/ DECLARE MyCursor1 CURSOR FOR ( SELECT * FROM Authors ) FOR READ ONLY /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим. Данные доступны только для чтения.*/ DECLARE MyCursor1 CURSOR FOR ( SELECT * FROM Authors ) FOR UPDATE /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим. Данные курсора можно менять.*/ Операторы для работы с курсором Прежде чем обратиться к данным курсора, его нужно после объявления открыть. Синтаксис оператора OPEN в обозначениях MS SQL Server: OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name } Пример: DECLARE MyCursor1 CURSOR FOR ( SELECT * FROM Authors ) OPEN MyCursor1 После прекращения работы с курсором, его нужно закрыть. Курсор остается доступным для последующего использования в рамках процедуры или триггера, в котором он создан. Синтаксис оператора CLOSE в обозначениях MS SQL Server: CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name } Пример: DECLARE MyCursor1 CURSOR FOR ( SELECT * FROM Authors ) OPEN MyCursor1 --здесь операторы работы с курсором CLOSE MyCursor1 Если курсором больше не будут пользоваться, то его необходимо уничтожить и освободить переменную. Синтаксис оператора DEALLOCATE в обозначениях MS SQL Server: DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name } Пример: DECLARE MyCursor1 CURSOR FOR ( SELECT * FROM Authors ) OPEN MyCursor1 --здесь операторы работы с курсором CLOSE MyCursor1 DEALLOCATE MyCursor1 FETCH – оператор движения по записям курсора и извлечения данных те кущей записи в указанные переменные. Синтаксис оператора FETCH в обозначениях MS SQL Server: FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ] Пример: DECLARE MyCursor1 SCROLL CURSOR FOR ( SELECT * FROM Authors ) DECLARE @i BIGINT , @s CHAR ( 20 ) , @d smalldatetime OPEN MyCursor1 FETCH FIRST FROM MyCursor1 INTO @i, @s, @d @i @s @d CLOSE MyCursor1 DEALLOCATE MyCursor1 @@FETCH_STATUS – данная функция определяет признак конца или начала текущего курсора. Функция принимает одно из следующих значений: 0 – находимся в пределах курсора, не в конце; 1 – попытка выйти за пределы первой записи вверх (в никуда); 2 – попытка выйти за пределы последней записи вниз (в никуда). Пример: DECLARE MyCursor1 SCROLL CURSOR FOR ( SELECT * FROM Authors ) DECLARE @i BIGINT , @s CHAR ( 20 ) , @d smalldatetime OPEN MyCursor1 FETCH FIRST FROM MyCursor1 INTO @i, @s, @d WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM MyCursor1 INTO @i, @s, @d @i @s @d END CLOSE MyCursor1 DEALLOCATE MyCursor1 Встроенные функции Встроенные функции, имеющиеся в распоряжении пользователей при работе с SQL, можно условно разделить на следующие группы: математические функции; строковые функции; функции для работы с датой и временем; функции конфигурирования; функции системы безопасности; функции управления метаданными; статистические функции. Использование функций для работы со строковыми переменными Краткий обзор строковых функций Название функции Действие, выполняемое функцией ASCII Возвращает код ASCII левого символа строки CHAR По коду ASCII возвращает символ CHARINDEX Определяет порядковый номер символа, с которого начинается вхождение подстроки в строку DIFFERENCE Возвращает показатель совпадения строк LEFT Возвращает указанное число символов с начала строки LEN Возвращает длину строки LOWER Переводит все символы строки в нижний регистр LTRIM Удаляет пробелы в начале строки NCHAR Возвращает по коду символ Unicode PATINDEX Выполняет поиск подстроки в строке по указанному шаблону REPLACE Заменяет вхождения подстроки на указанное значение QUOTENAME Конвертирует строку в формат Unicode REPLICATE Выполняет тиражирование строки определенное число раз REVERSE Возвращает строку, символы которой записаны в обратном поряд ке RIGHT Возвращает указанное число символов с конца строки RTRIM Удаляет пробелы в конце строки SOUNDEX Возвращает код звучания строки SPACE Возвращает указанное число пробелов STR Выполняет конвертирование значения числового типа в символь ный формат STUFF Удаляет указанное число символов, заменяя новой подстрокой SUBSTRING Возвращает для строки подстроку указанной длины с заданного символа UNICODE Возвращает Unicode-код левого символа строки UPPER Переводит все символы строки в верхний регистр Использование функций для работы с числами Краткий обзор математических функций Название функции Действие, выполняемое функцией ABS Вычисляет абсолютное значение числа ACOS Вычисляет арккосинус ASIN Вычисляет арксинус ATAN Вычисляет арктангенс ATN2 Вычисляет арктангенс с учетом квадратов CEILING Выполняет округление вверх COS Вычисляет косинус угла COT Возвращает котангенс угла DEGREES Преобразует значение угла из радиан в градусы EXP Возвращает экспоненту FLOOR Выполняет округление вниз LOG Вычисляет натуральный логарифм LOG10 Вычисляет десятичный логарифм PI Возвращает значение "пи" POWER Возводит число в степень RADIANS Преобразует значение угла из градуса в радианы RAND Возвращает случайное число ROUND Выполняет округление с заданной точностью SIGN Определяет знак числа SIN Вычисляет синус угла SQUARE Выполняет возведение числа в квадрат SQRT Извлекает квадратный корень TAN Возвращает тангенс угла Использование функций для работы с типом дата/время Краткий обзор основных функций для работы с датой и временем Название функции Действие, выполняемое функцией DATEADD Добавляет к дате указанное значение дней, месяцев, часов и т.д. DATEDIFF Возвращает разницу между указанными частями двух дат DATENAME Выделяет из даты указанную часть и возвращает ее в символьном формате DATEPART Выделяет из даты указанную часть и возвращает ее в числовом формате DAY Возвращает число из указанной даты GETDATE Возвращает текущее системное время ISDATE Проверяет правильность выражения на соответствие одному из возможных форматов ввода даты MONTH Возвращает значение месяца из указанной даты YEAR Возвращает значение года из указанной даты MINUTE Возвращает значение минут из указанной даты/времени HOUR Возвращает значение часов из указанной даты/времени SECOND Возвращает значение секунд из указанной даты/времени |