ГОУВПО «Воронежский государственный технический
университет»
Кафедра технологических и автоматизированных систем
электронного машиностроения
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
к лабораторной работе № 5
по курсам «Базы данных», «Управление данными»,
«Базы и банки данных» для студентов специальностей
очной формы обучения
Воронеж 2009
Составители: д-р техн. наук, проф. О.Н. Чопоров
д-р техн. наук, доц. К.А. Разинкин
канд. техн. наук В.Г. Мединцев
УДК 651.3
Методические указания к лабораторной работе № 5 «Введение в язык T-SQL» по курсам «Базы данных», «Управление данными», «Базы и банки данных» для студентов специальностей 230104, 230201 и направления 230200 очной формы обучения / ГОУ ВПО «Воронежский государственный технический университет»; сост. О.Н. Чопоров, К.А. Разинкин, В.Г. Мединцев. Воронеж, 2009. 26 с.
В работе рассматриваются основы языка структурированных запросов T-SQL. Рассматриваются категории команд SQL; основные команды SQL, применяемые для построения запроса; принципы создания запросов SQL различных типов в SQL Server 2000.
Методические указания подготовлены в электронном виде в текстовом редакторе MS Word и содержатся в файле “Lab5.doc”
Библиогр.: 1 назв.
Рецензент канд. техн. наук, доц. Э.И. Воробьев
Ответственный за выпуск зав. кафедрой, д-р техн. наук, проф. О.Н. Чопоров
Печатается по решению редакционно-издательского совета Воронежского государственного технического университета.
© ГОУ ВПО «Воронежский
государственный технический
университет», 2009
Целью лабораторной работы является изучение основ языка структурированных запросов T-SQL. Для достижения поставленной цели необходимо решить следующие задачи:
создать запросы на выборку из нескольких таблиц на языке SQL заданными критериями отбора;
создать запрос на выборку на языке SQL, содержащий статические (агрегатные функции);
создать запрос, осуществляющий объединение результатов двух и более запросов в один набор результатов, используя команду UNION.
1.2. В результате выполнения работы студенты должны знать:
категории команд SQL;
основные команды SQL, применяемые для построения запроса;
принципы создания запросов SQL различных типов в SQL Server 2000.
1.3. Используемые программно-аппаратные средства: персональный компьютер стандартной конфигурации; операционная система MS Windows 2000/XP; система управления базами данных Microsoft SQL Server 2000 Developer Edition; набор таблиц БД, созданных в процессе выполнения лабораторной работы № 1.
1.4. В процессе выполнения лабораторных работ студент должен:
изучить способы создания простых и сложных запросов;
осуществлять поиск в базах данных по различным критериям.
1.5. Перед выполнением лабораторных работ каждый студент обязан ознакомиться с правилами техники безопасности при работе в помещении с электронно-вычислительной техникой.
1.6. Указания по оформлению отчета
Отчет должен содержать постановку задачи, описание приемов работы с РСУБД SQL Server 2000, результаты выполнения работы, выводы.
1.7. Указания по сдаче зачета преподавателю
Для сдачи зачета необходимо
предъявить отчет;
ответить на контрольные вопросы.
SQL - это язык программирования и запросов к базам данных, он применяется для осуществления доступа к данным, для запросов к реляционным СУБД, для управления базами данных и их обновления. Стандарт SQL утвержден как Американским Национальным Институтом Стандартов (ANSI, American National Standards Institute), так и Международной организацией по стандартизации (ISO, International Organization for Standardization). ANSI - это организация промышленных и деловых групп, разрабатывающая стандарты для торговли и коммуникации в Соединенных Штатах. ANSI также является членом ISO и IEC (Международной электротехнической комиссии, International Electrotechnical Commission). ANSI публикует стандарты США, которые соответствуют международным стандартам. В 1992 году ISO и IEC опубликовали международный стандарт на SQL, который принято называть SQL-92. ANSI опубликовал в Соединенных Штатах соответствующий стандарт, ANSI SQL-92, который иногда называют ANSI SQL. Хотя разные реляционные СУБД применяют несколько различающиеся версии SQL, но большинство из них соответствуют стандарту SQL-92, определенному ANSI.
В SQL Server 2000 применяется диалект Transact SQL (T-SQL). Т-SQL - это усовершенствование стандартного языка программирования SQL. Первоначальный, основной SQL применяется для взаимодействия между приложениями и SQL Server. В T-SQL имеются вес возможности языков DDL и DML стандартного SQL, а кроме этого имеются также расширенные функции, системные хранимые процедуры и конструкции для программирования (такие, как IF and WHILE), обеспечивающие гораздо большую гибкость программирования.
Язык SQL содержит операторы; относящиеся к одному из двух основных языки программирования в составе SQL: DDL и DML. Язык DDL (data definition language, язык определения данных) применяется для определения объектов баз данных (таких как базы данных, таблиц и представлении) и для управления этими объектами. Операторы языка DDL обычно включают в себя команды CREATE, ALTEB и DROP для каждого из объектов, с которым производится работа. DML (data manipulation language, язык манипулирования данными) применяется для манипулирования данными, содержащимися в объектах базы данных, для чего применяются такие операторы, как SELECT, INSERT, UPDATE и DELETE. При помощи этих операторов можно соответственно выбирать строки с данными, вставлять новые строки, изменять имеющиеся и удалять ненужные.
Все ключевые слова SQL можно, в свою очередь, разделить на следующие категории.
• Команды. Представляют собой глаголы, определяющие действие, которое следует
выполнить. Например, SELECT, CREATE И ALTER.
• Условия, или квалификаторы (qualifiers). Ограничивают диапазон значений элементов, входящих в запрос, например, WHERE.
• Модификаторы, или предложения (clauses). Модифицируют выполнение инструкции, например, ORDER BY.
• Предикаты (predicates). Представляют собой выражения, такие как IN, ALL, ANY, SOME, LIKE и UNIQUE. Предикаты могут возвращать в качестве результата значения TRUE, FALSE и в некоторых случаях NULL (неизвестный результат). Эти три значения являются ключевыми словами SQL.
• Операторы. Такие операторы, как =, < или >, сравнивают значения и применяются для создания объединений в синтаксисе предложений WHERE или JOIN. Операторы также называют предикатами сравнения.
• Статистические функции (также называемые агрегатными). Возвращают одно результирующее значение, вычисленное на основании набора данных например COUNT(), МАХ() и MIN().
• Функции преобразования типа данных. Изменяют тип данных значения с одного на другой. Наиболее часто используемые функции преобразования — это CAST() и CONVERT().
• Другие ключевые слова (или зарезервированные слова), изменяющие действие команд или управляющие курсором (указателем текущей записи в наборе результатов запроса), с помощью которого выбираются отдельные строки запроса. Например, модификатор FOR XML [AUTO | RAW | EXPLICIT] языка T-SQL возвращает XML документ или подчиненный XML-документ вместо традиционного набора данных запроса на выборку (SELECT). Модификатор FOR XML не включен в ANSI SQL.
Наряду с операторами сравнения, применяемыми в выражениях языка SQL, его синтаксис предусматривает использование в качестве знаков пунктуации запятых, точек, двоеточий и точек с запятыми. Ниже приведён список используемых в языке T-SQL символов и знаков пунктуации.
• Запятые используются для разделения компонентов списка параметров. Так, например, перечень имен нескольких полей будет выглядеть следующим образом:
Название, Адрес, Город, Индекс.
• Квадратные скобки, в которые заключаются имена полей, необходимы только в том случае, когда в имени поля имеются пробелы или другие знаки пунктуации, не разрешенные в ANSI SQL, например,
[Название Компании]. Квадратные скобки могут также применяться для помещения в них имен, которые присваивают в бланке запроса вводным параметрам для функций и сохраненных (хранимых) процедур SQL Server.
• Точки используются для отделения имен объектов зависимого класса. Например, если в запрос включены поля из нескольких таблиц, точка используется для отделения имен таблиц от имен полей — Клиенты. [Название Компании]. Состоящие из четырех частей имена связанных таблиц в инструкциях FROM используют формат Сервер. БазаДанных. Схема. Таблица.
• Идентификаторы строки (также называемые разделителями) указывают значения символьных констант. В ANSI SQL требуется заключать значения строковых литералов в одинарные кавычки ('). Для обратной совместимости с SQL Server 7.0 и более ранними версиями язык T-SQL интерпретирует двойные кавычки как квадратные скобки.
• Символы подстановки. В ANSI SQL символы подстановки % и _ (подчеркивание) используются в инструкции LIKE.
• Идентификатор даты/времени. ANSI SQL принимает заключенные в одинарные кавычки значения даты в различных символьных форматах. Символ # в ANSI SQL не используется.
• Идентификаторы : и @ для переменных. ANSI SQL использует двоеточие (:) в качестве префикса для идентификации переменных, принимающих значения параметра. T-SQL использует символ @ для традиционных переменных (включая переменные, принимающие значения параметров) и @@ для переменных, чьи значения предоставляются SQL Server, таких как @@IDENTITY, которая возвращает текущее значение идентификации столбца таблицы.
• Символ ! (восклицательный знак) используется как синоним NOT в ANSI SQL. В качестве оператора "не равно" в ANSI SQL используется комбинация.
Структура запроса:
SELECT [ALL | DISTINCT] [TOP n [PERCENT] ] список_полей,
FROM имена_таблиц
[WHERE критерий_отбора]
[GROUP BY группируемые_поля]
[HAVING условие_для_результата ]
[ORDER BY столбцы_сортировки [ASC | DESC] ]
Данный запрос возвращает таблицу, содержащую выбранные столбцы (поля) из указанных строк (записей) таблицы-источника. В квадратных скобках указаны необязательные параметры.
Описание назначения всех элементов этой инструкции запроса на выборку.
• SELECT. Основная команда, определяющая запрос на выборку. В параметре список_полей указываются поля (столбцы), которые должны быть включены в результирующую таблицу запроса. При указании нескольких имен полей они разделяются запятыми.
Необязательные предикаты ALL и DISTINCT определяют способ отбора строк. При использовании первого из них в таблицу включаются все строки, соответствующие указанным далее условиям. Предикат DISTINCT исключает строки с повторяющимися данными.
Необязательный модификатор ТОР n [PERCENT] ограничивает результирующий набор записей запроса, возвращая только первые n записей или n процентов набора записей, удовлетворяющих запросу. ТОР n PERCENT являются зарезервированными словами T-SQL, а не ANSI SQL. Модификатор ТОР можно также использовать для ускорения вывода в том случае, когда необходимо отобразить только наиболее важные строки результирующего набора. Модификатор ТОР 100 PERCENT, возвращающий все строки, требуется для создания представлений SQL Server, которые вы смогли бы сортировать с помощью предложения ORDER BY.
• FROM имена_та6лиц. Определяет имена таблиц, из которых запрос должен отобрать данные. Имена таблиц разделяются запятыми. Перед именами полей, представленными в нескольких таблицах, необходимо добавлять имя таблицы.
• WHERE критерий_от6ора. Определяет условие для отбора записей указанных таблиц.
Компонент WHERE не обязательный, поэтому, если его не добавить, запрос возвратит все строки из таблицы, определенной элементом FROM имена_таблиц.
• GROUP BY условие_группировки. Предложение GROUP BY применяется после предложения WHERE и означает, что строки набора результатов должны быть сгруппированы в соответствии с данными в колонке группировки. Если в предложении SELECT используется агрегатная функция, то для каждой группы вычисляется и отображается в выводе итоговое агрегатное значение. Агрегатная функция выполняет вычисления и возвращает значение. В предложении GROUP BY в качестве колонок группировки должны быть заданы все колонки из списка выборки (кроме колонок, применяемых для агрегатных функций), в противном случае SQL Server выдаст сообщение об ошибке. Если бы это правило не соблюдалось, результаты нельзя было бы выдать в разумном виде, поскольку колонка, заданная в GROUP BY, должна группировать каждую колонку в списке выборки.
• HAVING условие_для_результата. Одно или несколько условий, налагаемых на конечный результат, полученный после выполнения группировки, вычисления или применения агрегатной функции. Условие HAVING является необязательным.
• ORDER BY столбцы_сортировки. Определяет порядок сортировки записей. Так же, как и предложение WHERE, ORDER BY, не является обязательным компонентом инструкции. Используя ключевые слова ASC или DESC, можно определить сортировку по возрастанию и по убыванию соответственно. Если порядок не указан явно, по умолчанию используется сортировка по возрастанию.
Пример:
SELECT TOP 100 PERCENT авторы.автор, авторы.[год рождения], авторы.[место рождения],
авторы.язык, авторы.[число произведений]
FROM авторы INNER JOIN книги ON авторы.автор = книги.автор
INNER JOIN издательства ON книги.издательство = издательства.издательство
WHERE (авторы.[год рождения] LIKE '12.%.%') AND (авторы.[число произведений] BETWEEN 1
AND 500)
ORDER BY авторы.автор
Запрос возвратит указанные в SELECT поля из указанных в FROM таблиц с критериями для отбора результатов по дате рождения (12 число) и числу произведений (от 1 до 500), и сортировкой по полю авторы.автор.
2.3.1 Критерии отбора.
Чтобы ограничить количество строк, выдаваемых в ответ на запрос, можно задать условия поиска и критерии отбора.
операции сравнения:
= Проверяется равенство двух выражений
!= Проверяется неравенство двух выражений
> Проверяется, что первое выражение больше второго
>= Проверяется, что первое выражение больше второго или равно ему
< Проверяется, что первое выражение меньше второго
<= Проверяется, что первое выражение меньше второго или равно ему
Пример:
SELECT автор, [год рождения]
FROM авторы
WHERE автор <> ‘Пушкин А.С.’
Запрос возвратит ФИО и год рождения всех авторов кроме Пушкина А.С.
логические операции: AND, OR, NOT
другие ключевые слова:
LIKE. Выражение LIKE шаблон. Применяется для поиска по соответствующему шаблону. Шаблоны являются строковыми выражениями, состоящими из символов и метасимволов. Метасимволы – это символы, имеющие общий смысл при использовании внутри строковых выражений.