17218

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

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

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

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

Русский

2013-06-30

68 KB

32 чел.

Лабораторная работа № 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?


 

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

30003. Организация ресторана. Роль повара – кондитера на предприятии общественного питания 956.86 KB
  Пищевая ценность блюд. Соблюдение санитарных требований правил личной гигиены при приготовлении блюд. Бракераж готовых блюд. Характер приготовления блюд русской кухни в значительной мере обусловлен особенностям русской печи которая в качестве очага столетиями с 14 века верно служила и богатому и простому народу.
30004. Применение оптимальных способов преобразования и регулирования социальных отношений и процессов в жизнедеятельности людей 118.18 KB
  Политические и социально-экономические катаклизмы, вызванные резким переходом страны от социалистической системы хозяйствования к капиталистической, создали благоприятную почву для роста преступности, коррупции, разрыву между доходами богатых и бедных
30006. Анализ коммуникационных связей и деятельности на предприятии в системе руководитель-подчиненный. Общая характеристика организации ООО «Весна» 380 KB
  Коммуникация это связующие нити объединяющие взаимозависимые части организации. Коммуникация является жизненно важной системой организации : если каким то образом ликвидировать потоки сообщений в организации то она прекратит свое существование. Коммуникация предоставляет средства для выработки и исполнения решений осуществления обратной связи и корректировки целей и процедур деятельности организации в соответствии с требованиями ситуации. Организационная коммуникацияэто процесс с помощью которого руководители развивают...
30007. Обоснование содержания экспериментальной Программы адаптивной физической реабилитации и методики использования ее средств на различных формах занятий для улучшения рессорных функций стопы при плоскостопии у детей среднего школьного возраста 328.06 KB
  1 Общая характеристика строения и видов нарушений свода стопы___6 1. Слабость мышц поддерживающих своды стопы является одним из условий нарушений нормального свода стопы что в конечном счёте может привести к патологическим изменениям не только стопы но и всего опорнодвигательного аппарата ОДА а также к нарушению сердечно сосудистой и нервной системам. Плоскостопие нарушает рессорные функции стопы почти пропадает амортизация и при ходьбе вся отдача встряска достается голени и тазобедренному суставу что может привести к...
30008. РАЗВИТИЕ ПОЗНАВАТЕЛЬНОГО ИНТЕРЕСА У УЧАЩИХСЯ МЛАДШЕГО ШКОЛЬНОГО ВОЗРАСТА ЧЕРЕЗ ПРОЕКТНУЮ ДЕЯТЕЛЬНОСТЬ 146.09 KB
  3 Методы приемы стимулирования познавательной и развивающей деятельности. МЕТОДОЛОГИЧЕСКИЕ ОСНОВЫ ИСПОЛЬЗОВАНИЯ ПРОЕКТНОЙ ДЕЯТЕЛЬНОСТИ В НАЧАЛЬНЫХ КЛАССАХ.2 Использование проектной деятельности на уроках в начальных классах. ЭКСПЕРИМЕНТАЛЬНОЕ ИССЛЕДОВАНИЕ ПО ВЫЯВЛЕНИЮ УРОВНЯ РАЗВИТИЯ ПОЗНАВАТЕЛЬНОГО ИНТЕРЕСА МЛАДШИХ ШКОЛЬНИКОВ ПРИ ИСПОЛЬЗОВАНИИ ПРОЕКТНОЙ ДЕЯТЕЛЬНОСТИ39 3.
30009. ОЦЕНКА СОРТОВ ЯБЛОНИ ПО УСТОЙЧИВОСТИ К БОЛЕЗНЯМ И ВРЕДИТЕЛЯМ В РУП «УЧХОЗ БГСХА» 139 KB
  Учет болезней плодовых культур. Учет вредителей плодовых культур. Плодоводство это возделывание плодовых культур дающих съедобные и пригодные для технической переработки плоды и ягоды. Культивирование плодовых деревьев кустарников и травянистых растений составляет предмет плодоводства.
30010. Технология выполнения технического обслуживания и ремонта передних управляемых мостов трактора МТЗ – 80/82 347.93 KB
  Самоблокируемый дифференциал переднего ведущего моста МТЗ-82 состоит из корпуса, представляющий собой две половины, в котором размещены конические шестерни полуосей, торцовые поверхности которых опираются на торцы нажимных чашек. Шестерни входят в зацепление с сателлитами
30011. Организация нестационарных форм социального обслуживания пожилых и инвалидов 76.25 KB
  Социальные проблемы лиц пожилого возраста и их отражение в государственной социальной политике 1. Социальные проблемы лиц пожилого возраста и их меры социальной поддержки инвалидов в Артемовском городском округе II. Анализ деятельности организации социальной защиты как фактора обеспечения экономической безопасности населения Артемовского городского округа на примере Управления Социальной защиты населения Артемовского городского округа.1 Общая характеристика современного состояния института социальной защиты...