17218

Организация запросов на выборку данных средствами SQL

Лабораторная работа

Информатика, кибернетика и программирование

Лабораторная работа № 2 Тема: Организация запросов на выборку данных средствами SQL. Цель работы: Изучить синтаксис инструкции SQL – SELECT для создания запросов на выборку данных. Используя предложения WHERE задать условия отбора строк из таблиц определенных в предложен

Русский

2013-06-30

68 KB

31 чел.

Лабораторная работа № 2

Тема: Организация запросов на выборку данных средствами SQL.

Цель работы: Изучить синтаксис инструкции SQLSELECT, для создания запросов на выборку данных. Используя предложения WHERE задать условия отбора строк из таблиц определенных в предложении FROM. Изучить организацию выборки данных из нескольких таблиц БД.

Структура лабораторной работы

  1.  Общие сведенья по работе запросами.
    1.  Выборка данных.
    2.  Выборка из нескольких таблиц.
    3.  Вычисления в нутрии SELECT.
  2.  Задание к лабораторной работе.
  3.  Оформление отчета.
  4.  Контрольные вопросы.

1. Общие сведенья по работе запросами

1.1. Выборка данных

Для извлечения записей из таблиц в SQL определен оператор SELECT. С помощью этой команды осуществляется не только операция реляционной алгебры "выборка" (селекция), но и предварительное соединение (join) двух и более таблиц. Это наиболее сложное и мощное средство SQL, полный синтаксис оператора SELECT имеет вид:

 

      SELECT [DISTINCT] <список_выбора>

           FROM <имя_таблицы>, ...

           [ WHERE <условие> ]

           [ GROUP BY <имя_столбца>,... ]

              [ HAVING <условие> ]

           [ORDER BY <имя_столбца> [ASC | DESC],... ]

 

Порядок предложений в операторе SELECT должен строго соблюдаться (например, GROUP BY должно всегда предшествовать ORDER BY), иначе это приведет к появлению ошибок.

Все примеры, приведенные ниже, касающиеся базы данных publications, можно выполнить самостоятельно, используя пример лабораторной работы № 1, поэтому результаты запросов на создание таблиц здесь не приводятся.

Выборка данных всегда начинается с ключевого слова SELECT. В конструкции <список_выбора> определяется столбец или столбцы, включаемые в результат. Он может состоять из имен одного или нескольких столбцов, или из одного символа « (звездочка), определяющего все столбцы. Элементы списка разделяются запятыми.

Пример: получить список всех авторов

   SELECT author FROM authors;

 

получить список всех полей таблицы authors:

 

   SELECT * FROM authors;

 

В том случае, когда необходимы не все записи, а только те, которые удовлетворяют некому условию, это условие можно указать после ключевого слова WHERE.

Например, найдем все книги, опубликованные после 1996 года:

 

   SELECT title FROM titles WHERE yearpub > 1996;

 

Допустим, что надо найти все публикации за интервал 1995 - 1997 гг. Это условие можно записать в виде:

 

   SELECT title FROM titles WHERE yearpub>=1995 AND yearpub<=1997;

 

В заключение заметим, что при выполнении оператора SELECT результирующее отношение может иметь несколько записей с одинаковыми значениями всех полей. Чтобы исключить повторяющиеся записи из выборки используется ключевое слово DISTINCT.

1.2. Выборка из нескольких таблиц

Часто возникает ситуация, когда выборку данных необходимо производить из отношения, которое является результатом соединения двух других таблиц.

Например, нужно получить из базы данных publications информацию обо всех печатных изданиях в виде следующей таблицы:

 

Title

Yearpub

Publisher

 

 

 

 

 

 

 

Для этого СУБД предварительно должна выполнить слияние таблиц titles и publishers, а только затем произвести выборку из полученного отношения.

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

SELECT a.title, a.yearpub, b.publisher

        FROM titles AS a, publishers AS b

        WHERE a.pub_id=b.pub_id;

 

Пример, где одновременно задаются условия и соединение, и выборки (запроса ограничивается изданиями после 1996 года):

 

SELECT a.title, a.yearpub, b.publisher

        FROM titles AS a, publishers AS b

        WHERE a.pub_id = b.pub_id AND

              a.yearpub > 1996;

 

Следует обратить внимание на то, что когда в разных таблицах присутствуют одноименные поля, то для устранения неоднозначности перед именем поля указывается имя таблицы и знак "." (точка). (Рекомендуется имя таблицы указывать всегда!). Имя таблицы может быть заменено альтернативным логическим именем – синонимом, для чего используется связка AS в разделе FROM (FROM titles AS a, publishers AS b).

Естественно, имеется возможность производить слияние и более чем двух таблиц.

Например, чтобы дополнить описанную выше выборку именами авторов книг необходимо составить оператор следующего вида:

 

 SELECT d.author, a.title, a.yearpub,

       b.publisher

  FROM titles AS a, publishers AS b,

       titleauthors AS c, authors AS d

  WHERE c.au_id = d.au_id AND

        c.title_id = a.title_id AND

        a.pub_id = b.pub_id AND

        a.yearpub > 1996;

 

Альтернативный вариант соединения нескольких таблиц может использовать оператор JOIN в оазжеде FROM. Существует три

варианта оператора:

INNER JOIN – соединение, при котором записи включаются в результирующий набор только в том случае, если в связных атрибутах будут найдены одинаковые значения;

LEFT JOIN – левое соединение, при котором все записи из первой (левой) таблицы включаются в результирующий набор, даже если во второй (правой) таблице нет соответствующих им записей;

RIGHT JOIN –правое соединение, при котором все записи из второй (правой) таблицы включаются в результирующий набор, даже если в первой (левой) таблице нет соответствующих им записей.

 

Например, предыдущий пример можно реализовать с использованием оператора

INNER JOIN следующим образом:

 

SELECT d.author, a.title, a.yearpub, b.publisher

 FROM ((titles AS a INNER JOIN publishers AS b ON

        a.pub_id = b.pub_id)

               INNER JOIN titleauthors AS c ON

               c.title_id = a.title_id)

               INNER JOIN authors AS d ON

                c.au_id = d.au_id

 WHERE a.yearpub > 1996;

1.3. Вычисления внутри SELECT.

SQL позволяет выполнять различные арифметические операции над столбцами результирующего отношения. В конструкции <список_выбора> можно использовать константы, функции и их комбинации с арифметическими операциями и скобками.

Например, чтобы узнать, сколько лет прошло с 1992 года (год принятия стандарта SQL-92) до публикации той или иной книги можно выполнить команду:

 

    SELECT title, yearpub-1992 FROM titles

   WHERE yearpub > 1992;

 

В арифметических выражениях допускаются операции сложения (+), вычитания (-), деления (/), умножения (*), а также различные функции (COS, SIN, ABS – абсолютное значение и др.).

В SQL также определены агрегатные функции, которые выполняют действия над группой одинаковых записей. Среди них выделяют:

AVG(<имя поля>) - среднее по всем значениям данного поля

COUNT(<имя поля>) или COUNT (*) - число строк

MAX(<имя поля>) - максимальное из всех значений данного поля

MIN(<имя поля>) - минимальное из всех значений данного поля

SUM(<имя поля>) - сумма всех значений данного поля

 

Следует учитывать, что каждая агрегирующая функция возвращает единственное значение.

Примеры:

определить дату публикации самой "древней" книги в базе данных

 

     SELECT MIN(yearpub) FROM titles;

 

подсчитать количество книг в базе данных:

 

     SELECT COUNT(*) FROM titles;

 

Область действия данных функции можно ограничить с помощью логического условия.

Например.

Найти количество книг, выпущенных после 2000 года:

 

     SELECT COUNT(*) FROM titles WHERE yearpub > 2000;

 

При ограничении данных полученных в результате применения агрегатных функций нужно использовать параметр HEVING, выполняющий те же функции что и параметр WHERE, но только с вычисленными значениями. Кроме этого, как правило, на практике группировка данных для применения агрегатной функции некоторого атрибута производится относительно другого атрибута. В этом случае необходимо использовать параметр GROUP BY для группировки данных, относительно которых будет выполняться операция агрегатной функции.

Например:

Найти книги, количество которых после 2000 года превысило 100000 экземпляров

     SELECT title, COUNT(yearpub) AS Количество 

          FROM titles WHERE yearpub > 2000

          GROUP BY title

          HEVING COUNT(yearpub)>100000;

Если в запросе необходимо выполнить сортировку данных необходимо использовать параметр ORDER BY с указанием атрибута, по которому выполняется сортировка. Можно выполнять сортировку в двух направлениях: по возрастанию – параметр ASC (выполняется по умолчанию) и по убыванию – параметр DESC.

Например:

Реализовать предыдущий запрос и при этом отсортировать название книг в обратном алфавитном порядке.

     SELECT title, COUNT(yearpub) AS Количество 

          FROM titles WHERE yearpub > 2000

          GROUP BY title

          HEVING COUNT(yearpub)>100000

          ORDER BY title DESC;

2. Задание к лабораторной работе

 

Замечания по ходу выполнения лабораторной работы.

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

 

Реализовать следующие запросы средствами SQL:

  1.  Отобразить все данные таблицы «Справочник материалов»
  2.  Получить список поставщиков;
  3.  Получить список деталей;
  4.  Получить список поставщиков и их адреса;
  5.  Получить список номеров деталей, наименования и цены;
  6.  Получить список наименований деталей и их цены;
  7.  Получить список номеров деталей и их наименования;
  8.  Получить соединение таблиц POST и POSTKA;
  9.  Получить соединение таблиц POSTKA и DETAL;
  10.  Получить соединение таблиц POST, POSTKA, DETAL и SP_MATER;
  11.  Перечислить поставщиков, поставляющих деталь Д1;
  12.  Вывести имена и адреса поставщиков, поставляющих детали Д1, или Д2;
  13.  Вывести имена и адреса поставщиков, поставляющих деталь «Гайка»;
  14.  Перечислить номера деталей, их наименования, материал и цены для поставщика «Джон»;
  15.  Вывести имена и адреса поставщиков, а также наименования деталей, материал, количество и цены для поставщиков «Джон» и «Коля»;
  16.  Вывести имена и адреса поставщиков, а также наименования деталей, материал, количество и цены для поставщиков с номерами «1» и «5», поставляющих детали Д2, или Д3, или Д6;
  17.  Перечислить имена, адреса, наименование деталей, материал, количество и цены для поставщиков, поставляющих «Чугунные» или «Стальные» детали;
  18.  Перечислить имена, адреса, наименование деталей, материал, количество и цены для поставщиков, поставляющих «Чугунные» или «Стальные» детали, цена которых не менее 3;
  19.   То же, но цена находится в пределах не менее 3 и не более 10.
  20.  Найти детали, которые поставляются в количестве не меньше 10, и не больше 100.
  21.  Получить список деталей поставляемых поставщиком “МАША”, цена которых более 3.
  22.  Сколько деталей «Болт» поставляется по всем заказам.
  23.  Получить список наименований деталей, суммарная поставка которых превышают 50.
  24.  На какую общую сумму поставлено деталей поставщиком «Коля»
  25.  Какие поставщики поставили деталь «Корпус» и «ФЛАНЕЦ».

3. Оформление отчета

  1.  Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.
  2.  Содержание должно включать Тему лабораторной работы и Ход ее выполнения.
  3.  Ход выполнения работы должен содержать все инструкции SQL (SELECT) реализованные в лабораторной работе, включая инструкции создания вычисляемых полей.
  4.  Вывод

4. Контрольные вопросы

  1.  Какие атрибуты называются соединимыми?
  2.  Операция проекции.
  3.  Операция селекции (выборки).
  4.  Операция соединения.
  5.  Виды операции соединения.
  6.  Соответствие специальных операций реляционной алгебры инструкции SELECT.
  7.  Какой будет результат, если при выполнении запроса к двум и более отношениям не использовать никакого условия соединении?
  8.  Какие типы соединений (JOIN) поддерживает инструкция SELECT?
  9.  В каком случае обязательно указывать имя таблицы перед именем поля?
  10.  Как формировать вычисляемые поля в SELECT?
  11.  Какой альтернативный синтаксис операции JOIN (с использованием WHERE) можно использовать для корректного выполнения запроса?
  12.  В чем отличие предложений WHERE и РУМШТП при задании условия выборки данных?
  13.  В каких случаях необходимо использовать параметр GROUP BY?


 

А также другие работы, которые могут Вас заинтересовать

12682. Работа с дискетами: форматирование, копирование в Windows 91.5 KB
  Лабораторная работа №5. Тема: Работа с дискетами: форматирование копирование. Цель работы: Научится выполнять обслуживание дисков с помощью служебных приложений Windows. Содержание работы Проанализировать работу Буфера обмена используя Таблицу символов. ...
12683. Работа с окнами. Переключение между программами в Windows 53 KB
  Лабораторная работа №6. Тема: Работа с окнами. Переключение между программами. Цель работы: Научиться работать с окнами изучить сочетания клавиш. Содержание работы Открыть несколько программ: калькулятор WordPad Проводник Paint. Изменить размеры окон. Ра
12684. Архивирование файлов. Программа WinRAR, WinZip 79.5 KB
  Лабораторная работа №7. Тема: Архивирование файлов. Программа WinRAR WinZip. Цель работы Научиться создавать и распаковывать архивы освоить приемы работы с программами архиваторами. Содержание работы Создать архив с помощью программы WinRAR который включает 56фай...
12685. Запись и воспроизведение звуков в Windows 76.5 KB
  Лабораторная работа 8. Тема: Запись и воспроизведение звуков. Цель: Научиться работать со звуком: записывать воспроизводить. Для записи воспроизведения и редактирования звуковых файлов в Windows используется программа Звукозапись. Для работы данной программы требу
12686. Установка программ в Windows 91.5 KB
  Лабораторная работа № 9. Тема: Установка программ. Цель: Научиться устанавливать различное программное обеспечение. Установка программ. В процессе работы с компьютером постоянно возникает необходимость устанавливать новые программы и удалять те программы котор...
12687. Файловые системы в Windows. Файловые системы FAT, FAT 32, NTFS 66.5 KB
  Лабораторная работа №. Тема: Файловые системы. Цель: Изучить файловые системы FAT FAT 32 NTFS Файловая система – это общая структура определяющая в операционной системе наименование сохранение и размещение файлов. Файловая система отводит место для зап
12688. Решение уравнений в пакете MatLab 925 KB
  Лабораторная работа №1. Решение задач в MatLab Цель лабораторной работы – закрепление практических навыков решения задач в среде математического пакета MatLab необходимых выполнения лабораторных работ по дисциплине. Этап I. Решение уравнений в пакете MatLab Запустить п
12689. ИСПЫТАНИЕ ЭФФЕКТИВНОСТИ И ПАСПОРТИЗАЦИЯ ВЕНТИЛЯЦИОННЫХ УСТАНОВОК 589.5 KB
  Отчет по лабораторной работе ИСПЫТАНИЕ ЭФФЕКТИВНОСТИ И ПАСПОРТИЗАЦИЯ ВЕНТИЛЯЦИОННЫХ УСТАНОВОК Цель работы: получить навыки проведения измерений необходимых для испытания оценки эффективности и паспортизации механической вентиляционной установки. При выпол...
12690. Испытание эффективности и паспортизации вентиляционных установок 236 KB
  Лабораторная работа № 4 по теме Испытание эффективности и паспортизации вентиляционных установок Цель работы: получить навыки проведения измерений необходимых для испытаний оценки эффективности и паспортизации механической вентиляционной установки. Описание...