Основы Transact-SQL

Основы Transact-SQL

• • • • • РАЗДЕЛ 4. ЯЗЫК TRANSACT-SQL Тема 4. 1. Введение в Transact -SQL История развития SQL. Основные элементы языка: идентификаторы, константы, комментарии. Операторы. Типы данных. Функции Transact-SQL. Тема 4. 2. Язык описания данных (DDL) Языковые средства описания данных реляционных СУБД. Команды создания и управления базами данных и объектами баз данных. Тема 4. 3. Язык манипулирования данными (DML) Команда извлечения данных SELECT. Предложение WHERE. Предложения OPDER BY, GROUP BY и HAVING. Агрегатные функции. Оператор соединения таблиц JOIN Подзапросы. Связанные подзапросы. Команды вставки, удаления и изменения данных. Тема 4. 4. Создание и использование представлений Назначение представлений. Создание представлений. Команды управления представлением. Редактирование информации, связанной с представлением. Индексированные представления. Тема 4. 5. Средства разработки процедур в Transact-SQL Блоки операторов. Операторы IF, WHILE, BREAK и CONTINUE. Хранимые процедуры. Создание пользовательских хранимых процедур. Функции, определенные пользователем. Триггеры.

История развития языка SQL 1974 г. – IBM разработан язык доступа к реляционным данным SEQUEL (Structured English Query Language) для СУБД System/R 1979 г. – переименован в SQL 1986 г. – ANSI опубликовал первый стандарт по SQL (SQL 1) 1987 г. - разработан стандарт ISO 9075 -1987 1992 г. – ANSI разработал первый международный стандарт SQL-92 1999 г. – стандарт SQL 99 (SQL 3) (добавлены объектноориентированные возможности) Настоящее время – стандарт SQL: 2008 (шестая версия стандарта)

Основные элементы языка Комментарий -отображает текст, введенный пользователем. Комментарии могут вставляться отдельной строкой, добавляться в конец командной строки Transact-SQL или инструкции Transact. SQL. -- text_of_comment /* text_of_comment */

Идентификатор Существует два класса идентификаторов: Обычные идентификаторы и идентификаторы с разделителем. Обычные идентификаторы не разделяются при использовании в инструкциях языка Transact-SQL. SELECT * FROM группа WHERE курс = 4 Идентификаторы, не соответствующие правилам формата обычных идентификаторов, всегда необходимо использовать с разделителями. Идентификаторы с разделителем заключаются в двойные кавычки (") или квадратные скобки ([ ]). SELECT * FROM [общая ведомость] WHERE [код дисциплины] = 12 И обычные идентификаторы, и идентификаторы с разделителем могут содержать от 1 до 128 символов. Для локальных временных таблиц идентификатор может содержать не более 116 символов.

Правила для обычных идентификаторов 1. 2. 3. 4. 5. 6. Может включать любые символы, определенные стандартом Unicode 3. 2, кроме запрещенных. В идентификатор не должны входить слова, являющиеся зарезервированными. Запрещены символы: « » (пробел), «(» , «)» , «{» , «}» , «!» , «%» , «&» , «~» , «^» , «‑» , «» , «‘» (апостроф). Первым символом в идентификаторе может быть: – буква латинского или национального алфавита; – символ подчеркивания «_» . Для обозначения временных объектов разрешается использовать в качестве первого символа: – символ «@» в идентификаторах временных переменных и параметров; – символ «#» в идентификаторах временных таблиц и хранимых процедур. Transact-SQL не различает регистр. Например, идентификатор Фамилия соответствует идентификатору фамилия.

Обращение к объекту Полное имя объекта состоит из четырех идентификаторов: имени сервера, имени базы данных, имени схемы и имени объекта, которые отображаются в следующем формате: имя_сервера. [имя_базы_данных]. [имя_схемы]. имя_объекта | имя_базы_данных. [имя_схемы]. имя_объекта | имя_схемы. имя_объекта | имя_объекта Имена сервера, базы данных и схема называются квалификаторами имени объекта. Схема является коллекцией обхектов базы данных, которыми владеет один человек. Некоторые квалификаторы могут быть опущены, их позиции отмечаются точками, например: имя_базы_данных. . имя_объекта

Константы и переменные Константы - постоянные величины, значения которых не могут быть изменены. Можно использовать константы разных типов: • числовые – 10, 105. 25 • символьные (строковые) – ‘Иванов’, ‘МАТАН’. • Десятично-шестнадцатеричные – 0 х23657, 0 х. FF 0 A 12 Переменная – именованная область памяти определенного объема, которая может изменяться. Объявление переменной : DECLARE @имя_переменной тип_данных [, …] Для присвоение значения переменной: • SET @имя_переменной=значение • SELECT @имя_переменной=значение

Примеры: DECLARE @номер int, @фио Char(30) SET @номер=1 SET @фио=’Александрова’ SELECT @ номер=2, @фио=’Дегтева’ DECLARE @количество int SELECT @количество = COUNT(фамилия) FROM студент WHERE номер_группы=’ 1011’

Выражение - это совокупность операндов и операторов. - Арифметические операции (в порядке убывания приоритета выполнения): унарные (+) и (-), умножение (*) и деление (/), остаток от деления (%), сложение (+) и вычитание (-). - Строковые операторы - +. - Операторы сравнения: равно (=), больше (>), меньше (<), меньше или равно (<= или !>), больше или равно (>= или !<), не равно (!= или <>). - Логические операторы NOT, AND и OR.

Логические операторы NOT, AND и OR. Таблица истинности троичной логики a b Not a A and b a or b TRUE FALSE TRUE FALSE TRUE NULL FALSE NULL TRUE FALSE TRUE FALSE NULL TRUE FALSE NULL TRUE NULL FALSE NULL NULL

К логическим относятся также операторы: • BETWEEN – проверяет, лежит ли значение в указанном диапазоне; • LIKE – проверяет, соответствует ли значение указанному шаблону; • IN – возвращает TRUE, если значение входит в указанный список; • ALL – выполняет сравнение для набора данных. Если условие выполнено для всего набора данных, возвращает значение TRUE; • ANY – выполняет сравнение для набора данных. Если условие выполнено хотя бы для одного элемента из набора данных, возвращает значение TRUE; • EXIST – проверяет существование данных;

Типы данных Числовые типы: Целочисленные типы данных (общее название integer): • tinyint – однобайтовое двоичное целое число без знака. Диапазон значений: от 0 до 255; • smallint – двухбайтовое двоичное целое число со знаком. Диапазон значений: от – 215 до 215– 1; • integer – черырехбайтовое двоичное целое число со знаком. Диапазон значений: от – 231 до 231– 1; • bigint – восьмибайтовое двоичное целое число со знаком. Диапазон значений: от – 263 до 263– 1.

Нецелочисленные типы данных: Десятичные данные - хранятся в виде последовательности цифр. Decimal (p[, s]) и Numeric (p[, s]) – десятичные числа с фиксированной точностью и масштабом. где p – общее количество цифр числа; s – масштаб (количество десятичных знаков после запятой )(s <= p). Если s не указано, то дробная часть равна нулю. рmax = 38. Приблизительные типы используются для работы с данными, имеющими значения от очень малых величин до предельно больших: Float [(n)] – представляет тип с плавающей запятой. Диапазон значений: – 1, 79*10308 / +1, 79*10308, n – определяет количество разрядов мантиссы. Размер области памяти для данных данного типа зависит от выбранной точности. Если n имеет значение от 1 до 24, то используется 4 байта и поддерживается точность 7 цифр. Если n имеет значение от 25 до 53, то используется 8 байт и поддерживается точность 15 цифр; Real – представляет тип с плавающей запятой. Для хранения типа real – 4 б (n = 24). Диапазон значений: – 3, 40*1038/+3, 40*1038. Real представляет собой частный случай Float. Приблизительное значение не гарантирует точность представления значения, если мантисса значения > n, то значение будет округлено до n разрядов.

Денежные типы данных: Используются для хранения данных о денежных суммах. Позволяет хранить после запятой четыре знака. К денежным типам относятся: Money – для данных этого типа отводится 8 байт. Диапазон значений: – 922 337 203 685 477. 5808 / +922 337 203 685 477. 5808; Smallmoney – для данных этого типа отводится 4 байт. Диапазон значений: – 214 748. 3648 / +214 748. 3648.

Символьные и текстовые типы данных: • • Char(n) – строка фиксированной длины, используется для хранения набора символов длинной n (nmax = 8 000). Varchar(n) –строка переменной длины. Память для строки будет выделяться в соответствии с реальным размером строка. • типы Nchar(n) и Nvarchar(n) аналогичны типам Char и Varchar, отличаются тем, что предназначены для хранения символов Unicode, т. е. каждый символ занимает 2 байта, поэтому nmax = 4 000. Если необходимо указать тип строки, как Unicode, то перед строкой следует добавить символ N. Например, N’Stroka in Unicode Standart’ • Text – предназначен для хранения очень большого количества символов (до 231– 1). Для данных типа text память выделяется страницами (8 Кбайт). Ntext – предназначен для хранения текста большого объема в формате Unicode. •

Типы данных дата и время • • Datetime – 8 б (1. 1. 1753 – 31. 12. 9999 гг) Smalldatetime – 4 б (1. 1. 1900 - 31. 12. 2079 гг) Date - 3 б (1. 1. 0001 – 31. 12. 9999 гг) Time – 3 б

Функции преобразования данных CAST (значение AS тип_данных) CONVERT(тип_данных[(длина)], значение[, стиль]) значение - задает величину, которую необходимо преобразовать. тип_данных - определяет новый тип данных. стиль - позволяет определить формат преобразования. Этот аргумент может быть использован при преобразовании значений типа float, real, money, smallmoney, datetime, smalldatetime в символьные строки (char, varchar, nvarchar). Например: CONVERT(money, 42157. 54, 1) 42, 157. 54

Управляющие конструкции Transact-SQL BEGIN … END Выполняет группировку двух и более команд в единый блок. Объединенные в блок команды воспринимаются интерпретатором как одна команда. Формат: BEGIN команда_1 команда_2 : команда_n END Блоки BEGIN. . END могут быть вложенными. Ограничений на глубину вложенности нет.

Формат оператора IF IF логическое_условие блок_команд_1 [ ELSE блок_команд_2 ] Формат оператора CASE WHEN логическое_условие THEN блок_команд […] [ ELSE блок_команд ] END

Операторы WHILE, BREAK, CONTINUE Оператора WHILE позволяет организовать цикл. Формат оператора: WHILE логическое_условие BEGIN блок_команд [BREAK] блок_команд [CONTINUE] блок_команд END Оператор BREAK позволяет остановить работу и выйти из цикла. Оператор CONTINUE позволяет начать цикл заново, не дожидаясь выполнения всех команд цикла.

Язык манипулирования данными (DML- Data Manipulation Language ) Команда SELECT - извлечение данных SELECT [предикат] список_полей FROM имена_таблиц [WHERE критерий_поиска] [GROUP BY критерий_группировки ] [HAVING критерий_отбора] [ORDER BY критерий_столбца]

Раздел SELECT [{ALL|DISTINCT}] [TOP n [PERCENT]] список_полей 1) {[таблица. ]* | представление. * | псевдоним. *} SELECT f. [наименование факультета], g. * FROM факультет f, группа g WHERE f. [номер факультета]= g. [номер факультета] 2) {[таблица. ]поле | выражение } [AS псевдоним] SELECT [номер специальности], [стоимость обучения]*1. 2 AS [стоимость с НДС] FROM специальность 3) псевдоним = выражение SELECT [номер специальности], [стоимость с НДС]=[стоимость обучения]*1. 2 FROM специальность

ALL - выбор всех строк, удовлетворяющих условию отбора, и действует по умолчанию. DISTINCT - используется для исключения строк, содержащих повторяющиеся значения в поле. П р и м е р. Определить сведения о студентах каких групп содержатся в таблице Студент. SELECT DISTINCT [номер группы] FROM студент Аргумент TOP n [PERCENT]указывает на необходимость выбора не всех строк, а только первых n. PERCENT – указывает на необходимость выбора определенного процента от всех строк, удовлетворяющих условию. П р и м е р. Вывести сведения о пяти студентах из таблицы Cтудент. SELECT TOP 5 фамилия, [дата рождения] FROM студент

Раздел FROM таблица_1 псевдоним_1 [{INNER|{LEFT|RIGHT|FULL}[OUTER]} JOIN таблица_2 псевдоним_2 ON псевдоним _1. поле_1 оператор псевдоним _2. поле_2] П р и м е р. Сформировать список групп с указанием наименований факультетов. SELECT [наименование факультета], [номер группы] FROM факультет a inner join группа b on a. [номер факультета] =b. [номер факультета]

Пример: Создать запрос, формирующий следующие сведения о результатах сдачи экзаменов: номер группы, наименование дисциплины, фамилия, оценка SELECT FROM студент a INNER JOIN [общая ведомость] b ON a. [номер зачетной книжки] = b. [номер зачетной книжки]

Пример: Создать запрос, формирующий следующие сведения о результатах сдачи экзаменов: номер группы, дисциплина, фамилия, оценка SELECT FROM студент a INNER JOIN [общая ведомость] b ON a. [номер зачетной книжки] = b. [номер зачетной книжки] INNER JOIN дисциплина c ON b. [код дисциплины] = c. [код дисциплины]

Пример: Создать запрос, формирующий следующие сведения о результатах сдачи экзаменов: номер группы, дисциплина, фамилия, оценка SELECT [номер группы], [наименование дисциплины], фамилия, оценка FROM студент a INNER JOIN [общая ведомость] b ON a. [номер зачетной книжки] = b. [номер зачетной книжки] INNER JOIN дисциплина c ON b. [код дисциплины] = c. [код дисциплины]

Раздел WHERE Позволяет задать правило отбора строк WHERE критерий_отбора Критерий_отбора определяет логическое условие, при выполнении которого строка будет включена в результат запроса. В стандарте ANSY/ISO определены следующие условия отбора (предикаты):

Предикат сравнения – проверяемое значение сравнивается со значением выражения Пример: Вывести сведения о студентах, обучающихся в группе 3591. SELECT * FROM Студент WHERE [Номер группы]=3591 Пример: Вывести сведения о коммерческих студентах, обучающихся в группах 3591 и 3501. SELECT * FROM Студент WHERE Коммерческий = 1 AND ([Номер группы]=3591 OR [Номер группы]=3501)

Предикат «между» - проверяется, попадает ли значение в указанный диапазон Предикат «между» записывается с помощью оператора BETWEEN. Синтаксис предиката: выражение [NOT] BETWEEN значение 1 AND значение 2 значение 1 и значение 2 определяют начало и конец диапазона значений и могут быть заданы числовыми, или символьными константами. Пример: Вывести сведения о тех студентах, фамилии которых попадают в заданный алфавитный диапазон. SELECT * FROM Студент WHERE Фамилия BETWEEN 'К' AND 'Н' Пример: Вывести сведения о факультетах, имеющих номера с 1 до 5. SELECT * FROM Факультет WHERE [Номер факультета] BETWEEN 1 AND 5

Предикат «в» - проверяется, совпадает ли значение с одним из значений заданного множества Для записи предиката «в» используется оператор IN. Синтаксис оператора: выражение [NOT] IN {(значение 1, значение 2 [, …])| подзапрос} Пример: Вывести сведения о студентах, родившихся зимой. SELECT [Номер группы], Фамилия, [Дата рождения] FROM Студент WHERE MONTH([Дата рождения]) IN (12, 1, 2)

Предикат «как» - проверяется, соответствует ли строковое значение заданному шаблону Предикат «как» реализуется в языке оператором LIKE. Оператор LIKE выполняет проверку строкового значения (тип Char, Var. Char) на соответствие шаблону. Синтаксис оператора: выражение [NOT] LIKE шаблон Примеры: WHERE WHERE Фамилия Фамилия LIKE LIKE 'А%' '_е%' '[АБ]%' '[К-М]%' '[^АБ]%'

«NULL – предикат» - проверяется, не содержится ли в столбце значение NULL Синтаксис оператора: выражение IS [NOT] NULL Проверка на NULL не может возвращать в качестве результата значение NULL, она будет возвращать значение TRUE или FALSE. Например, вывести сведения о тех факультетах, для которых сведения о деканах неизвестны. SELECT * FROM Факультет WHERE Декан IS NULL

«Предикат существования» - проверяется, получены ли в результате работы подзапроса одна или несколько строк Предикат «как» реализуется в языке оператором EXISTS, который проверяет существование данных. Пример: Определить сведения о тех студентах, которые не сдавали экзамены. SELECT * FROM Студент а WHERE NOT EXISTS (SELECT * FROM [Общая ведомость] b WHERE a. [Номер зачетной книжки]=b. [Номер зачетной книжки])

Раздел GROUP BY Раздел содержит указания выполнять группировку строк таблиц по определенным критериям. Синтаксис раздела: GROUP BY критерий_группировки [HAVING критерий_отбора] Критерий_группировки - имя одного, или нескольких столбцов, которые называются группирующими столбцами. Они определяют, по какому признаку строки делятся на группы. Как правило группировка строк выполняется с целью получения итогов по значениям столбцов, которые называются группируемыми.

Функции агрегирования данных: AVG ([выражение | DISTINCT имя_столбца]) COUNT ({[DISTINCT] имя_столбца | * }) SUM ([выражение | DISTINCT имя_столбца]) MAX (выражение) MIN (выражение) Пример: Для каждой специальности определить количество коммерческих студентов в группе и итоговую оплату. select from студент a inner join группа b on a. [номер группы]=b. [номер группы] inner join специальность c on b. [номер специальности]=c. [номер специальности] where коммерческий=1

Функции агрегирования данных: AVG ([выражение | DISTINCT имя_столбца]) COUNT ({[DISTINCT] имя_столбца | * }) SUM ([выражение | DISTINCT имя_столбца]) MAX (выражение) MIN (выражение) Пример: Для каждой специальности определить количество коммерческих студентов в группе и итоговую оплату. Select from студент a inner join группа b on a. [номер группы]=b. [номер группы] inner join специальность c on b. [номер специальности]=c. [номер специальности] where коммерческий=1 group by [наименование специальности], a. [номер группы]

Функции агрегирования данных: AVG ([выражение | DISTINCT имя_столбца]) COUNT ({[DISTINCT] имя_столбца | * }) SUM ([выражение | DISTINCT имя_столбца]) MAX (выражение) MIN (выражение) Пример: Для каждой специальности определить количество коммерческих студентов в группе и итоговую оплату. select [наименование специальности], a. [номер группы from студент a inner join группа b on a. [номер группы]=b. [номер группы] inner join специальность c on b. [номер специальности]=c. [номер специальности] where коммерческий=1 group by [наименование специальности], a. [номер группы]

Функции агрегирования данных: AVG ([выражение | DISTINCT имя_столбца]) COUNT ({[DISTINCT] имя_столбца | * }) SUM ([выражение | DISTINCT имя_столбца]) MAX (выражение) MIN (выражение) Пример: Для каждой специальности определить количество коммерческих студентов в группе и итоговую оплату. select [наименование специальности], a. [номер группы], count(фамилия) as [количество студентов], sum([стоимость обучения])as [итого оплата по группе] from студент a inner join группа b on a. [номер группы]=b. [номер группы] inner join специальность c on b. [номер специальности]=c. [номер специальности] where коммерческий=1 group by [наименование специальности], a. [номер группы]

Раздел HAVING определяет условие отбора групп строк. GROUP BY критерий_группировки [HAVING критерий_отбора] Критерий_отбора - это логическое условие, определяющее строки каких групп включать в таблицу результатов запроса. Пример: Определить номера тех групп, в которых меньше 25 человек. SELECT [Номер группы], COUNT(Фамилия)AS [Количество студентов] FROM Студент GROUP BY [Номер группы] HAVING COUNT(Фамилия)< 25

Раздел ORDER BY Синтаксис раздела: ORDER BY критерий_столбца [{ASC|DESC}][, …] Критерий_столбца - это имя столбца запроса, по значениям которого будет упорядочена таблица результатов запроса. С помощью ключевых слов ASC и DESC можно указать порядок сортировки (ASC – по возрастанию значений, DESC – по убыванию значений). Пример: Найти сведения о студентах групп 3591 и 3501 и отсортировать их по возрастанию номеров групп, а внутри групп по убыванию дат рождения студентов. SELECT * FROM Студент WHERE [Номер группы]=3591 or [Номер группы]=3501 ORDER BY [Номер группы], [Дата рождения]DESC

Подзапросы Подзапрос (подчиненный запрос) – это запрос, содержащийся в предложении WHERE, HAVING или FROM другого (основного) запроса. Пример: SELECT [Номер группы] FROM Группа WHERE [Номер специальности] = (SELECT [Номер специальности] FROM Специальность WHERE [Наименование специальности] = ’Математические методы в экономике’)

Команда SELECT в подзапросе: 1) может возвращать таблицу результатов, состоящую только из одного значения столбца, или из столбца значений, или из нескольких столбцов; 2) не должна содержать раздел ORDER BY; 3) имя столбца подзапроса может являться ссылкой на столбец таблицы основного запроса. Такой столбец называется внешней (коррелирующей) ссылкой, а подзапрос называет связанным или коррелированным подзапросом.

Подзапросы в разделе WHERE Пример: Вывести сведения о тех группах, студенты которых обучаются на специальности «Управление качеством» . SELECT * FROM Группа WHERE [Номер специальности] = (SELECT [Номер специальности] FROM Специальность WHERE [Наименование специальности]= ’Управление качеством’) Пример: SELECT [Номер группы], Фамилия, [Номер зачетной книжки] FROM студент WHERE [Номер группы] IN (SELECT [Номер группы] FROM Группа WHERE [Номер специальности]= (SELECT [Номер специальности] FROM Специальность WHERE [Наименование специальности]= ‘Математические методы в экономике’)) Сформулируйте условие запроса!!!!!!

Коррелированный подзапрос Пример: Определить сведения о тех студентах, которые не сдавали экзамены. SELECT * FROM Студент а WHERE NOT EXISTS (SELECT * FROM [Общая ведомость] b WHERE a. [Номер зачетной книжки]=b. [Номер зачетной книжки])

Синтаксис многократного сравнения: выражение операция_сравнения {ANY|ALL} подзапрос • Оператор ANY выполняет проверку для каждого значения столбца, возвращенного подзапросом и, если условие выполнено хотя бы для одного значения из столбца, то возвращает значение TRUE. Select * from студент a Where 2= ANY (select оценка from [общая ведомость] d where d. [номер зачетной книжки] =a. [номер зачетной книжки] ) • Оператор ALL также выполняет проверку для каждого значения столбца, возвращенного подзапросом, однако возвращает значение TRUE, если условие выполнятся для всех значений столбца. Select * from студент a Where 5= ALL (select оценка from [общая ведомость] d where d. [номер зачетной книжки] =a. [номер зачетной книжки] )

Пример подзапроса в разделе HAVING: Подзапрос будет выполняться один раз для каждой группы строк основного запроса и его результат будет участвовать в отборе групп строк. SELECT Фамилия, AVG(CAST(Оценка AS DECIMAL(4, 2))) AS [Средний балл] FROM [Общая ведомость] v INNEG JOIN Студент s ON v. [Номер зачетной книжки]=s. [Номер зачетной книжки] GROUP BY Фамилия HAVING AVG(CAST(Оценка AS DECIMAL(4, 2)))> (SELECT AVG(CAST(Оценка AS DECIMAL(4, 2))) FROM [Общая ведомость])

Подзапросы в разделе From Select фамилия, оценка From студент a inner join (select b. [номер зачетной книжки], оценка From [общая ведомость] b inner join дисциплина c on b. [код дисциплины]=c. . [код дисциплины] where [наименование дисциплины]=‘Иностранный язык’) on a. [номер зачетной книжки] = b. [номер зачетной книжки]

Добавление данных Синтаксис команды: INSERT INTO таблица [(список_столбцов)] {VALUES (список_значений)|запрос } Пример: INSERT INTO Специальность ([Номер специальности], [наименование специальности], [Стоимость обучения]) VALUES (220501, 'Управление качеством', 35000)

INSERT INTO Специальность ([Номер специальности], [наименование специальности]) VALUES (220501, 'Управление качеством‘, ) INSERT INTO Специальность ([наименование специальности], [Номер специальности]) VALUES ('Управление качеством‘, 220501)

Пример: Создать временную таблицу, добавив в нее значения, полученные по запросу. Временная таблица должна содержать данные об оплате за обучение студентами группы 3591. DROP TABLE #Ved. Opl CREATE TABLE #Ved. Opl ( [Номер группы] [smallint] NOT NULL , [Фамилия] [char](15) NOT NULL, [Плата за обучение] [money] NOT NULL ) INSERT INTO #Ved. Opl SELECT c. [Номер группы], Фамилия, [Стоимость обучение] FROM (Студент c INNER JOIN Группа g ON c. [Номер группы]=g. [Номер группы]) INNER JOIN Специальность S ON g. [Номер специальности]=s. [Номер специальности] WHERE c. [номер группы]=3591 AND Коммерческий=1 SELECT * FROM #Ved. Opl

Добавление данных во вновь создаваемую таблицу на основании данных, полученных по запросу Синтаксис команды: SELECT список_столбцов INTO новая_таблица FROM список_таблиц Пример: SELECT c. [Номер группы], COUNT(Фамилия) as [Количество студентов] INTO [Список групп] FROM Студент c INNER JOIN Группа g ON c. [Номер группы]=g. [Номер группы]) INNER JOIN Специальность s ON g. [Номер специальности]=s. [Номер специальности] WHERE [Наименование специальности]=’Управление качеством’ GROUP BY c. [Номер группы] SELECT * FROM [Список групп]

Изменение данных Синтаксис таблицы: UPDATE имя_таблицы SET имя_столбца=новое_значение[, …] FROM имена_таблиц WHERE критерий_отбора Пример: Перевод студентов в другую группу UPDATE Судент SET [номер группы]= 3501 WHERE [номер группы]=3591 Пример: Перевод студента в другую группу UPDATE Судент SET [номер группы]= 3501 WHERE [номер группы]=3591 and [номер зачетной книжки] = ‘ 12036/09’

Удаление данных Синтаксис команды: DELETE таблица FROM имена_таблиц WHERE критерий_отбора Пример: Создать запрос на удаление данных из таблицы Студент. DELETE Студент Пример: Создать запрос на удаление из таблицы Студент сведений о студентах группы 2291. DELETE Студент WHERE [номер группы]=2291

Команды изменения с подзапросами DELETE [общая ведомость] WHERE [номер зачетной книжки] not in (select [номер зачетной книжки] from Студент)

Data Definition Language (DDL) – язык определения данных. Содержит команды: 1. Создание объекта: CREATE объект 2. Изменение объекта ALTER объект 2. Удаление объекта DROP объект Объекты: база данных, таблица, представление, индекс

Создание таблицы CREATE TABLE имя_таблицы ({ описание_столбца | имя_вычисляемого_столбца AS выражение | ограничения_целостности_уровня_таблицы} [, . . . ]) Описание столбца: имя_столбца тип_данных [(размер)] [ { DEFAULT значение_по_умолчанию | IDENTITY [(значение, шаг)] } ] [ { PRIMARY KEY | UNIQUE | NOT NULL | FOREIGN KEY REFERENCES имя_таблицы(имя_столбца) [ ON UPDATE {CASCADE | NO ACTION} ] [ ON DELETE {CASCADE | NO ACTION} ] } [ CHECK (логическое выражение)] ]

Пример : Необходимо разработать запросы на создание таблиц в соответствии с приведенной на слайде инфологической моделью БД.

Факультет является независимой таблицей, поэтому она создается первой : CREATE TABLE [факультет] ( [номер факультета] tinyint PRIMARY KEY , [наименование факультета] char(50) ) Специальность также является независимой таблицей, ее создаем второй: CREATE TABLE [специальность] ( [номер специальности] int PRIMARY KEY, [наименование специальности] char (60), [стоимость обучения] [money] )

Группа является зависимой от факультета и специальности таблицей. Столбцы номер факультета и номер специальности являются внешними ключами: CREATE TABLE [группа] ( [номер группы] smallint PRIMARY KEY, [номер специальности] int FOREIGN KEY REFERENCES специальность(номер специальности) , [номер факультета] tinyint FOREIGN KEY REFERENCES факультет(номер факультета) , [номер курса] tinyint )

Примеры: Команды создания таблиц БД Студент является зависимой от группы таблицей. Столбец номер группы является внешними ключами: CREATE TABLE [студент] ( [номер зачетной книжки] char(8) PRIMARY KEY , [номер группы] smallint NOT NULL FOREIGN KEY REFERENCES группа(номер группы) , [фамилия] char(15) NOT NULL , [дата рождения] datetime NOT NULL , [коммерческий] bit NOT NULL , [имя регистрации] char(9) )

Данные таблицы Общая ведомость зависят от таблиц студент и дисциплина. В этой таблице первичный ключ составной и каждый из столбцов первичного ключа является внешним ключом. CREATE TABLE [дисциплина] ( [код дисциплины] int PRIMARY KEY, [наименование дисциплины] char(50) ) CREATE TABLE [общая ведомость] ( [код дисциплины] int, [номер зачетной книжки] char(8), [оценка] [tinyint] NOT NULL , PRIMARY KEY ([код дисциплины], [номер зачетной книжки]), FOREIGN KEY ([код дисциплины]) REFERENCES [дисциплина] ([код дисциплины]), FOREIGN KEY ([номер зачетной книжки]) REFERENCES [студент] ([номер зачетной книжки]) )

Создание представлений Представление (view) – это виртуальная таблица, содержимое которой определяется запросом на извлечение данных SELECT. Команда создания представления: CREATE VIEW имя_представления AS команда SELECT Представления создают для: • группировки столбцов разных таблиц в виде одного объекта; • для ограниченного доступа пользователей к определенным строкам или определенным столбцам таблицы; • для просмотра информации, полученной в результате преобразования данных столбца. Поле представления может содержать агрегированную информацию (sum, max, min и т. д. )

Пример создания представления Задание: Создать представление для получения следующих сведений: группа, фамилия, дисциплина, оценка. CREATE VIEW [Результаты экзаменов] AS SELECT 'Группа'=s. [номер группы], [фамилия], 'Дисциплина'=[наименование дисциплины], [оценка] FROM [дисциплина] d INNER JOIN [общая ведомость] v ON d. [код дисциплины] = v. [код дисциплины] INNER JOIN [студент] s ON v. [номер зачетной книжки] = s. [номер зачетной книжки] В запросах к представлениям обращаются как к таблицам: SELECT * FROM [Результаты экзаменов] WHERE [номер группы]=3501

Пользовательские функции В SQL Server 2000 имеются следующие классы функций пользователя. SCALAR – эти функция возвращает обычное скалярное значение любого из типов данных. Функция может включать множество команд, которые объединяются в один блок с помощью конструкции BEGIN. . . END. INLINE –функция содержит всего одну команду – запрос SELECT и возвращает пользователю значения типа данных table. MULTISTATEMENT –функция возвращает пользователю значение типа данных table, содержащее набор данных. Тело функции может содержать множество команд Transact-SQL (таких, как INSERT, UPDATE и т. д. ), в результате выполнения которых и формируется набор данных.

Создание SCALAR - функции CREATE FUNCTION имя_функции (@переменная тип_данных [, …]) RETURNS тип_данных_возвращаемого_значения AS BEGIN Команды Transact-SQL RETURN возвращаемое_значение END Вызов функции выполняется в разделах SELECT, WHERE, HAVING

Пример: функция по номеру зачетной книжки определяет средний балл студента Create FUNCTION [Средний балл студента] (@номер_зачетной_книжки as char(8)) RETURNS decimal(3, 2) as BEGIN DECLARE @средний_балл decimal(3, 2) select @средний_балл =cast(avg(cast(оценка as decimal(3, 2))) as decimal(3, 2)) from [общая ведомость] where [номер зачетной книжки]=@номер_зачетной_книжки return @средний_балл END

Вызов SCALAR - функции SELECT фамилия, db. Admin. [Средний балл студента] ([номер зачетной книжки]) AS [средний балл] FROM студент WHERE [номер группы]=3501

Создание INLINE - функции CREATE FUNCTION имя_функции (@переменная тип_данных [, …]) RETURNS table AS RETURN (команда SELECT) Вызов INLINE – функции указывается в разделе FROM: SELECT FROM имя_пользователя. имя_функции

Пример создания INLINE - функции Create FUNCTION [Средние баллы группы] (@номер_группы smallint ) RETURNS table AS return (select фамилия , cast(avg(cast(оценка as decimal(3, 2))) as decimal(3, 2)) as [средний балл] from [общая ведомость] a inner join студент b on a. [номер зачетной книжки]=b. [номер зачетной книжки] where [номер группы]=@номер_группы group by фамилия)

Пример вызов INLINE - функции SELECT * FROM dbo. [Средние баллы группы] (3501)

Создание MULTISTATEMENT - функции CREATE FUNCTION имя_функции (@переменная тип_данных [, …]) RETURNS @переменная table(список_столбцов) AS BEGIN Команда Transact-SQL RETURN END Вызов MULTISTATEMENT – функции указывается в разделе FROM: SELECT FROM имя_пользователя. имя_функции

Пример создания MULTISTATEMENT - функции create FUNCTION [Результаты сессии студента] ( @student. ID char(8)) RETURNS @ved TABLE(фамилия char(15), [наименование дисциплины] char(40), оценка tinyint) AS BEGIN INSERT @ved SELECT фамилия, [наименование дисциплины], оценка FROM студент s inner join [общая ведомость] v on s. [номер зачетной книжки]= v. [номер зачетной книжки] inner join дисциплина d on v. [код дисциплины] = d. [код дисциплины] WHERE s. [номер зачетной книжки] = @student. ID RETURN END

Хранимые процедуры CREATE PROCEDURE имя_процедуры @переменная тип_данных [OUTPUT] [, …] AS Команда Transact-SQL Вызов процедуры: EXEC имя_процедуры @переменная=значение [, …] или имя_процедуры @переменная=значение [, …]

Пример создания хранимой процедуры create PROCEDURE [Сведения о факультетах] AS select [наименование факультета], count(distinct [номер специальности])as [количество специальностей], count(distinct b. [номер группы]) as [количество групп], count(фамилия) as [количество студентов] from факультет a inner join группа b on a. [номер факультета]=b. [номер факультета] inner join студент c on b. [номер группы]=c. [номер группы] group by [наименование факультета]

Пример создания хранимой процедуры с параметрами CREATE PROCEDURE [результаты экзамена группы] @disname varchar(40), @groupnumber smallint AS SELECT 'Группа'=s. [номер группы], [фамилия], 'Дисциплина'=[наименование дисциплины], [оценка] FROM [дисциплина] d INNER JOIN [общая ведомость] v ON d. [код дисциплины] = v. [код дисциплины] INNER JOIN [студент] s ON v. [номер зачетной книжки] = s. [номер зачетной книжки] WHERE d. [наименование дисциплины] = @disname AND s. [номер группы] = @groupnumber

Вызов процедуры [результаты экзамена группы] EXEC [результаты экзамена группы] ‘КСТ СУБД', ‘ 3591’ или EXEC [результаты экзамена группы] @disname=‘Численные методы', @groupnumber=‘ 3591' или EXEC [результаты экзамена группы] @groupnumber=‘ 3591', @disname=‘Философия'

Пример создания хранимой процедуры с выводным параметром CREATE PROCEDURE [средний балл студента] @номер_студента char(8), -- Это вводной параметр @средний_балл decimal(4, 2) OUTPUT -- Выв. параметр AS -- Получить средний балл для заданного номера -- зачетки и назначить его выводным параметром SELECT @средний_балл = CONVERT(decimal(4, 2), AVG( CAST([оценка] AS decimal(4, 2)))) FROM [общая ведомость] GROUP BY [номер зачетной книжки] HAVING [номер зачетной книжки]=@номер_студента RETURN

Пример вызова хранимой процедуры с выводным параметром DECLARE @средний_балл_студента decimal(4, 2) -- Выполнить процедуру с заданным номером зачетки -- и сохранить выводное значение в переменной EXECUTE [средний балл студента] '10005/01', @средний_балл = @средний_балл_студента OUTPUT -- Вывод значения, возвращенного процедурой: PRINT 'Средний балл студента с зач. № "10005/01": ' + convert(varchar(6), @средний_балл_студента)

Триггеры Триггер – это специальный тип хранимой процедуры, которая запускается автоматически при изменении какойлибо таблицы одним из трех операторов: UPDATE, INSERT или DELETE. Когда пользователь пытается выполнить изменение данных таблицы, сервер автоматически запускает триггер и только в том случае, если триггер завершает свою работу успешно, изменение данных фиксируется в таблице. Если триггер не выполняется, то не выполнится и команда изменения данных. SQL Server 2000 имеет два типа триггеров: AFTER |FOR-триггеры INSTEAD-OF-триггеры.

Создание триггеров CREATE TRIGGER имя_триггера ON имя_таблицы {FOR| INSTEAD OF} {INSERT |UPDATE | DELETE} AS команды Transact-SQL При активизации триггера SQL Server создает две временные таблицы deleted и inserted. Структуры этих таблиц полностью соответствуют структуре таблицы триггера. В таблице deleted размещаются копии строк, охваченные оператором DELETE или UPDATE. В таблице inserted размещаются копии строк, добавленных к таблице триггера оператором INSERT или UPDATE. При выполнении оператора UPDATE старые записи копируются в deleted, а новые – в таблицу триггера и в inserted. Значения таблиц deleted и inserted доступны только из триггера.

Пример AFTER FOR триггера При удалении данных о студенте из таблицы студент все его оценки переносятся в архивную ведомость и затем удаляются из общей ведомости: CREATE TRIGGER Delete. Stud ON [студент] FOR DELETE AS INSERT INTO [Архивная ведомость] SELECT v. [код дисциплины], v. [номер зачетной книжки], v. [оценка] FROM [общая ведомость] v, deleted d WHERE d. [номер зачетной книжки]= v. [номер зачетной книжки] PRINT 'Записи ведомости, перенесенные в архив: ' SELECT * FROM [Архивная ведомость] DELETE [общая ведомость] FROM [общая ведомость] WHERE [номер зачетной книжки] in (select [номер зачетной книжки] from deleted ) PRINT 'Записи ведомости удалены'

Пример instead of триггера При обновлении значения оценки необходимо выполнять проверку вводимого знаяения create trigger [update_проверка оценки] on [общая ведомость] instead of update as declare @оценка tinyint declare @НЗК char(8) declare @КД int select @КД=[код дисциплины], @НЗК=[номер зачетной книжки] from deleted select @оценка =оценка from inserted if @оценка <3 or @оценка >5 begin print 'ошибка при вводе оценки' ROLLBACK TRANSACTION end else update [общая ведомость] set [оценка]=@оценка where [код дисциплины]=@КД and [номер зачетной книжки]=@НЗК