Методические указания по выполнению лабораторных работ по дисциплине “Базы данных”
|
Скачать 190.69 Kb.
|
|
ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ ДОНСКОЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ Кафедра «Программное обеспечение вычислительной техники и автоматизированных систем» Основы применения языка SQL МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ по дисциплине “Базы данных” Ростов-на-Дону, 2010 Составитель ассистент Жуков А.И. Основы применения языка SQL: Методические указания – Ростов н/Д: Издательский центр ДГТУ, 2010.-10 с. Рассматриваются практические аспекты выполнения запросов на языке SQL в реляционных базах данных на примере MS Access 2003. Рассмотрена методология написания SQL-инструкций относящихся к языку запросов, языку манипуляции данными и языку определения данных. Даны задания к лабораторным работам, помогающие закрепить на практике полученные знания. Методические указания предназначены для студентов специальностей 230401 «Прикладная математика», 230105 «Программное обеспечение вычислительной техники и автоматизированных систем», 090102 «Компьютерная безопасность», 010503 «Математическое обеспечение и администрирование информационных систем». Печатается по решению методической комиссии факультета «Информатика и вычислительная техника» Рецензент к.т.н., проф. Гранков М.В. Научный редактор д.т.н. проф. Нейдорф Р.А. В настоящей методической разработке рассматриваются практические аспекты применения SQL-инструкций для взаимодействия с системой управления реляционными базами данных (СУБД). Приведены примеры выполнения SQL-инструкций для СУБД MS Access, входящего в пакет MS Office (до версии 2003), а также сделаны замечания по вопросам применения SQL-инструкций в СУБД MySQL и PostgreSQL. Рассмотрены базовые возможности языка. Язык SQL Язык SQL (Structured Query Language) – стандартный язык запросов по работе с реляционными базами данных. Язык SQL появился после реляционной алгебры, и его прототип был разработан в конце 70-х годов в компании IBM Research. В силу своего широкого распространения постепенно стал стандартом «де-факто» для языков манипулирования данными в реляционной СУБД. SQL является непроцедурным языком и не содержит операторов управления, организации подпрограмм, ввода-вывода и т.п. В связи с этим SQL автономно не используется, обычно он погружен в среду встроенного языка программирования СУБД. Основным назначением языка SQL (как и других языков для работы с базами данных) является подготовка и выполнение запросов. В результате выборки данных из одной или нескольких таблиц может быть получено множество записей, называемое представлением - . Формально операторы языка SQL могут быть разделены на три группы, относительно своего функционального назначения:
Также в отдельные группы выделяют операторы определения доступа к данным (DCL) и операторы управления транзакциями (TCL), но их рассмотрение выходит за рамки данного курса. Важно отметить, что если операторы, относящиеся к первым двум группам переносимы из одной СУБД к другой, то форматы операторов из последней группы не всегда совпадают в различных СУБД. Далее рассмотрим каждую группу операторов подробнее. Оператор SELECT – выборка с фильтром Язык запросов в SQL состоит из единственного оператора – SELECT, Синтаксис которого имеет следующий вид: SELECT [ ALL| DISTINCT] <�Список полей>|* FROM <�Список таблиц> [WHERE <�Предикат-условие выборки или соединения>] [GROUP BY <�Список полей результата>] [HAVING <�Предикат-условие для группы>] [ORDER BY <�Список полей, по которым упорядочить вывод>]; Ключевое слово SELECT сообщает СУБД, что данная команда является запросом. Все запросы на выборку данных начинаются этим словом с последующим пробелом. Рассмотрим параметры, которые могут следовать за ним: FROM - указывает источник данных в виде списка таблиц, либо в виде объединений, либо в виде подзапроса с обязательным указанием псевдонима. WHERE – указывает условия для выборки в виде предиката. ALL – в результирующий набор попадут все возможные строки (в случае декартового произведения отношения – все возможные сочетания), а значит повторения исключены не будут. Используется по умолчанию. DISTINCT – противоположен ALL – в результирующем наборе не будет присутствовать два одинаковых кортежа, т.е. совпадающих по значениям своих атрибутов. Рассмотрим пример выполнения запроса требующего применения DISTINCT (пример 1). Пример 1. Дано: Отношение Студенты(ФИО, Номер_зк); и Группы (Номер_зк, Группа). Обозначим Студенты – R1, а Группы - R2. Считаем, что R2(Номер_зк) – внешний ключ на одноименный атрибут в отношении R1. Отношения R1 и R2 содержат следующие кортежи:
Надо: Написать запрос SQL выбирающий номера зачетных книжек тех студентов, которые обучались хотя бы в одной группе. Решение: SELECT r1.Номер_зк FROM r1,r2 WHERE r1.Номер_зк = r2.Номер_зк; Результат выполнения запроса:
Важно понять, что декартового произведение двух отношений в приведенном примере возвращает 12 кортежей, а применение условия оставляет 4 из них, 3 из которых совпадают в значениях своего единственного атрибута. Поэтому для того, чтобы убрать повторы, после SELECT необходимо написать ключевое слово DISTINCT. Рассмотрим подробнее предикат из секции WHERE. В нем описано условие для выбираемых кортежей, т.е. будут выбраны только те кортежи, для которых это условие совпадает, а именно поля Номер_зк совпадают по своему значению. При обращении к полям из разных отношений (таблиц), имеющим одинаковые названия внутри одного запроса необходимо указывать название таблицы (пример 1). ORDER BY – указывает по каким полям в какой очередности и в каком направлении (по возрастанию или по убыванию) проводить сортировку. Для указания направления используются ключевые слова ASC (по возрастанию) – используется по умолчанию и DESC (по убыванию). Пример 2. Дано: Отношение Оценки(ФИО, Дисциплина, Оценки); Группы (ФИО, Группа); и Дисциплины (Группа, Дисциплина). Обозначим Оценки – R3, Группы – R4, Дисциплины – R5.
Надо: Вывести все оценки студентов в алфавитном порядке по убыванию ФИО. Решение: SELECT r3.ФИО, r3.Оценка FROM r3 ORDER BY r3.ФИО DESC; В выражении условий раздела WHERE могут быть использованы следующие предикаты: Предикаты сравнения (=, <>, >, >=, <, <=), которые имеют традиционный смысл. Предикат Between A and B – принимает значения между А и В. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона. Одновременно в стандарте задан и противоположный предикат Not Between A and B. Предикат вхождения в множество IN (множество) истинен тогда, когда сравниваемое значение входит в множество заданных значений. При этом множество значений может быть задано простым перечислением или встроенным подзапросом. Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение, предикат истинен, если сравниваемое значение соответствует шаблону, и ложен в противном случае. Предикат NOT LIKE имеет противоположный смысл. Шаблон может содержать % (* для Access) для обозначения любого числа любых символов; _ (? для Access) для обозначения любого одного символа. Предикат сравнения с неопределенным значением IS NULL. Для выявления равенства значения некоторого атрибута неопределенному значению применяют специальные стандартные предикаты: <�имя атрибута> IS NULL и <�имя атрибута> IS NOT NULL Предикат существования EXIST и не существования NOT EXIST. Применяется во вложенных запросах для определения непустого или пустого множества, являющегося результатом выборки. Пример 3. Надо: Вывести оценки студентов (отношение R3 из примера 2), не проходивших итоговую аттестацию (NULL – значения), либо получивших неудовлетворительную оценку. Сортировка по полю ФИО по возрастанию. Решение: SELECT r3.ФИО, r3.Оценка FROM r3 WHERE (r3.Оценка IS NULL) OR (r3.Оценка < 3) ORDER BY r3.ФИО; В примере 3 скобки не являются обязательными. Оператор SELECT – группировка, использование функций СУБД и агрегатных функций В отношении выбираемых оператором SELECT кортежей язык SQL позволяет применять группировку. В разделе GROUP BY задается список полей группировки. GROUP BY группирует записи данных и объединяет в одну запись все записи данных, которые содержат идентичные значения в указанном поле (или полях). WHERE определяет, какие записи должны участвовать в группировании, т.е. фильтрует до группирования. Обратите внимание, что использование GROUP BY отличается от использования DISTINCT. Во втором случае будут отброшены кортежи, которые в текущем представлении совпадают по всем полям (из совпадающих записей остается только один кортеж). Операция группировки приводит исходное отношение к виду, когда ко всем полям, запрошенным на отображение и не указанным в выражении группировки, применяются агрегатные функции. Однако если агрегатные функции не определены, то внешне результаты запросов c GROUP BY и DISTINCT будут идентичны, но в первом случае запрос будет выполняться всегда за большее время. В разделе HAVING задаются предикаты-условия, накладываемые на каждую группу. HAVING используется для фильтрации записей, полученных в результате группировки. WHERE определяет, какие записи должны участвовать в группировании, т.е. фильтрует до группирования. HAVING определяет, какие из получившихся в результате группировки записей будут включены в результирующую выборку, т.е. фильтрует записи после группирования. Агрегатные функции SQL – функции, которые позволяют вычислять обобщенные групповые значения. При группировке все множество кортежей отношения разбивается на группы, в которых объединяются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки. Таблица 1 – Агрегатные функции SQL
После применения агрегатной функции принято давать имя возвращаемому значению. Для этого применяется оператор AS – задание псевдонима. Рассмотрим применение группировки и агрегатных функций на примере. Пример 4. Надо: Вывести количество оценок студентов (отношение R3 и R5 из примера 2) по группам для дисциплины «Моделирование». Выводить только те группы, где количество оценок больше 1. Решение: SELECT COUNT(*) AS count_mark, r5.Группа FROM r3,r5 WHERE r3.Дисциплина = r5.Дисциплина AND r3.Дисциплина = “Моделирование” GROUP BY r5.Группа HAVING COUNT(*) > 1; Кроме агрегатных функций допустимо включать в текст запроса SQL и функции, определенные в самой СУБД. В табл.2 приведены несколько функций допустимых к применению в среде MS Access 2003, однако в каждой СУБД существует свой набор таких функций, о которых можно узнать из страниц документации конкретного программного средства. Таблица 2 – Встроенные функции СУБД MS Access
Объединения в языке SQL Стандарт SQL2 расширил понятие условного объединения. В стандарте SQL1 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними. Внутреннее объединение (INNER JOIN) возвращает записи из двух таблиц, если значение первичного ключа первой таблицы соответствует значению внешнего ключа второй таблицы, связанной с первой. На практике время выполнения запросов с внутренним объединением существенно зависит от реализации СУБД, но, как правило, предпочтительнее использовать объединение в виде перечисления таблиц в секции FROM с перечислением условий объединения в секции WHERE. Левое и правое объединение (LEFT JOIN и RIGHT JOIN) в отличии от внутреннего объединения подразумевает возвращение всех кортежей из левого или правого отношения с дополнением несуществующих атрибутов пустыми значениями. Ключевое слово FULL определяет полное внешнее объединение, т.е. и LEFT, и RIGHT. Ключевое слово UNION позволяет объединить результаты выполнения двух запросов в том случае, если их кортежи совпадают соответственно по доменам своих атрибутов. Формат использования объединения следующий: <выражение объединения> - <�имя таблицы1> { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN { ON условие | [USING (список столбцов)]} <�имя таблицы2> <выражение запроса на объединение> - <�имя таблицы1> UNION JOIN <�имя таблицы2> Подчиненный запрос SQL предполагает возможность встраивания подзапроса в текст запроса с возможностью обращения к его полям по заданному псевдониму. Псевдоним может быть задан ключевым словом AS. Рассмотрим пример использования подчиненных запросов, а также объединений. Пример 5. Надо: Для студентов из отношения R1 (пример 1) определить групп из отношения R2 (пример 1): вывести номер зачетки и название группы. Если группа не определена вывести «Группа не известна». Добавить в выборку пары номер зачетки и группа, соответствующие тем студентам, которые обучались в двух и более группах. Решение: SELECT r1.Номер_зк, iif(r2.Группа is null;”Группа не определена”; r2.Группа) FROM r1 LEFT JOIN r2 on (r1.Номер_зк = r2.Номер_зк) UNION SELECT r2.Номер_зк, r2.Группа FROM r2 WHERE r2.Номер_зк in (SELECT r2.Номер_зк FROM r2 GROUP BY r2.Номер_зк HAVING COUNT(r2.Группа) > 1) Разберем приведенный пример подробнее. Запрос можно рассматривать как объединение результатов выполнения двух запросов (с помощью union). Первый из них использует левое внешнее объединение, так как в отношении r2 присутствуют не все номера зачетных книжек, которые определены в r1. Во втором используются подзапрос в секции WHERE для определения тех номеров зачеток, по которым есть два и более кортежа в отношении r2. Подчиненный запрос в данном случае возвращает множество номеров зачетных книжек, а оператор in возвращает TRUE только для тех номеров, которые принадлежат набору, который возвращает подзапрос. Перекрестный запрос Перекрестный запрос – способ группировки данных по двум измерениям, позволяющий отображать итоги в компактном результирующем наборе. В перекрестном запросе группировка выполняется по одному полю, а итоговая функция применяется к другому полю. Структура перекрестного запроса следующая: в конструкции TRANSFORM указывается поле и групповая функция, применяемая к нему, данное поле выводится на пересечении строк и столбцов; в конструкции GROUP BY указывается поле, по которому проводится группировка и которое выводится в качестве заголовков строк; в конструкции PIVOT указывается поле, значения которого выводятся в качестве заголовков столбцов. Пример 6. Надо: Для студентов из отношений R3, R4, R5 (пример 2) вычислить средние оценки по каждой дисциплине в каждой группе Решение: TRANSFORM Avg(R3.Оценка) AS [СредняяОценка] SELECT R5.Дисциплина FROM (R4 INNER JOIN R3 ON R4.ФИО = R3.ФИО) INNER JOIN R5 ON R4.Группа = R5.Группа GROUP BY R5.Дисциплина PIVOT R4.Группа; Операторы языка манипулирования данными К запросам языка манипуляции данными (Data Manipulation Language) относятся запросы на добавление, удаление и модификацию кортежей. Добавление кортежа производится командой: INSERT INTO имя_таблицы [(<�список столбцов>)] VALUES (<�список значений>) Список столбцов и список значений указываются через запятую, а значения добавляются в соответствующие столбцы. Если необходимо добавить кортеж целиком (т.е. значения есть для всех полей и их порядок совпадает с порядком полей в отношении), то описание списка столбцов можно опустить. Оператор удаления данных DELETE позволяет удалить одну или несколько строк из таблицы в соответствии с условиями, которые задаются для удаляемых строк. Синтаксис оператора DELETE следующий: DELETE FROM <�имя_таблицы> [WHERE <�условия_отбора>] Если условия отбора не задаются, то из таблицы удаляются все строки. Операция обновления данных UPDATE требуется тогда, когда происходят изменения данных, которые надо отразить в базе данных. Запрос на обновление может изменить сразу целую группу записей. Этот запрос состоит из трех частей:
Необязательный критерий WHERE, ограничивающий число записей, на которые воздействует запрос на обновление. В следующем примере приводится несколько запросов на добавление, изменение и удаление записей. Операторы языка определения данных Команды языка определения схемы данных представляют собой инструкции SQL, которые позволяют создавать и модифицировать элементы структуры базы данных. Например, используя SDL, можно создавать, удалять таблицы и изменять их структуру, создавать и удалять индексы. Создание таблицы. Оператор создания таблицы имеет следующий вид: CREATE TABLE <�имя таблицы> (<�имя столбца> <�тип данных> [NOT NULL] [,<�имя столбца> <�тип данных> [NOT NULL]]…) При создании таблицы для отдельных полей могут указываться некоторые дополнительные правила контроля вводимых в них значений. В табл. 3 перечислены типы данных, которые можно использовать при создании таблиц, используя Microsoft Jet DDL и предложение CREATE (СУБД Access). Таблица 3 – Типы данных полей, доступных в Access
Для удаления таблиц служит инструкция DROP TABLE <�имя таблицы> Для модификация структуры таблицы (добавление, удаление полей, изменения типов полей) используется оператор ALTER TABLE изменения структуры таблицы имеет следующий вид: ALTER TABLE <�имя таблицы>MODIFY | ADD | DROP <�имя поля> [<�тип данных>] Вид и формат операторов языка определения данных могуи существенно меняться при переходе от одной СУБД к другой, поэтому перед примененением описанных инструкций необходимо ознакомиться с документацией для конкретной СУБД. Задания на лабораторный практикум Задания выполняются в базе данных allauto.mdb, расположенной на сетевом диске кафедры «ПОВТиАС». Для сдачи лабораторной работы необходимо выполнить приведенные ниже задания по составлению SQL-инструкций. Задания выполняются в среде MS Access
Список литературы
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Похожие:
 |
Методические указания по выполнению лабораторных работ по дисциплине... Методические указания по выполнению лабораторных работ рассмотрены и утверждены на заседании кафедры «Безопасность труда и инженерная... |
 |
Методические указания для студентов по выполнению лабораторных и... Методические указания для студентов по выполнению лабораторных и практических работ |
|
Методические указания к практическим работам по дисциплине «Базы данных» Методические указания предназначены для проведения практических занятий по дисциплине «Базы данных», для специальности ксиК |
|
Методические указания по выполнению лабораторных работ Издательство Инженерная геодезия. Методические указания по выполнению лабораторных работ. Составители: Шешукова Л. В., Тютина Н. М., Клевцов Е.... |
|
Методические указания для выполнения лабораторных работ и «Базы данных» Лабораторная работа №1 «Организация хранения данных в субд ms access» |
|
Методические указания по выполнению лабораторных работ по дисциплине «Сметное дело» ... |
|
Методические указания «Аналитические запросы» по дисциплине «Постреляционные базы данных» Учебно-методические материалы «Аналитические запросы» представляют собой методические указания к лабораторным работам по дисциплине... |
|
Методические указания по выполнению практических и лабораторных работ... Методические указания предназначены для обучающихся по специальностям технического профиля 21. 02. 08 Прикладная геодезия |
|
Методические указания к выполнению лабораторных работ Омск 2006 П. С. Гладкий, Е. А. Костюшина, М. Е. Соколов, Проектирование баз данных: Методические указания к лабораторным работам. Омск: Издательство:... |
|
Методические указания по выполнению практических и лабораторных работ... Учебно-методическое пособие предназначенодля студентов 3 курса, обучающихся по профессии 23. 01. 03 Автомеханик. Пособие содержит... |
|
Методические указания по выполнению лабораторных работ по дисциплине... Государственное бюджетное профессиональное образовательное учреждение московской области |
|
Методические указания по выполнению лабораторных работ по дисциплине... Федерального государственного бюджетного образовательного учреждения высшего образования |
|
Методические указания по выполнению лабораторных работ по дисциплине... Федерального государственного бюджетного образовательного учреждения высшего образования |
|
Методические указания к выполнению лабораторных работ по дисциплине радиолокационные системы Лабораторная работа №1 «Изучение принципов построения штатной радиолокационной киа» |
|
Угловые измерения в геодезии методические указания к выполнению лабораторных... Занятия по изучению устройства теодолита, выполнению поверок и юстировок теодолита, а также по измерению горизонтальных и вертикальных... |
|
Методические указания по проведению лабораторных работ по дисциплине «Информатика» Методические указания по проведению лабораторных работ предназначены для студентов гоапоу «Липецкий металлургический колледж» технических... |