17213

Простые запросы на выборку данных средствами SQL

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

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

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

Русский

2013-06-30

66.5 KB

48 чел.

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

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

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

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

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

 

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

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

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

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

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

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

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

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

Этот оператор всегда начинается с ключевого слова 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. Ключевое слово ALL указывает, что в результат необходимо включать все строки.

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

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

 

title

yearpub

publisher

 

 

 

 

 

 

 

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

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

    SELECT titles.title,titles.yearpub,publishers.publisher

        FROM titles,publishers

        WHERE titles.pub_id=publishers.pub_id;

 

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

 

    SELECT titles.title,titles.yearpub,publishers.publisher

        FROM titles,publishers

        WHERE titles.pub_id=publishers.pub_id AND

              titles.yearpub>1996;

 

Следует обратить внимание на то, что когда в разных таблицах присутствуют одноименные поля, то для устранения неоднозначности перед именем поля указывается имя таблицы и знак "." (точка). (Рекомендуется имя таблицы указывать всегда!)

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

 

 SELECT authors.author,titles.title,titles.yearpub,

       publishers.publisher

  FROM titles,publishers,titleauthors,authors

  WHERE titleauthors.au_id=authors.au_id AND

        titleauthors.title_id=titles.title_id AND

        titles.pub_id=publishers.pub_id AND

        titles.yearpub > 1996;

 

Альтернативный вариант слияния нескольких таблиц может использовать оператор

соединения таблиц непосредственно в предложении FROM. Существует три

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

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

набор только в том случае, если в связных атрибутах будут найдены одинаковые

значения;

LEFT JOIN – левое соединение, при котором все записи из первой (левой)

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

таблице нет соответствующих им записей;

RIGHT JOIN –правое соединение, при котором все записи из второй (правой)

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

нет соответствующих им записей.

 

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

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

 

SELECT authors.author,titles.title,titles.yearpub,

       publishers.publisher

 FROM ((titles INNER JOIN publishers ON

                          titles.pub_id=publishers.pub_id)

               INNER JOIN titleauthors ON

                          itleauthors.title_id=titles.title_id)

               INNER JOIN authors ON

                          titleauthors.au_id=authors.au_id

 WHERE titles.yearpub > 1996;

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;

 

4. Функции для работы с датой

 

В MS Access предусмотрен целый набор встроенных функций дат и времени Перечислим некоторые из них:

Date() - текущая дата, т е сегодняшнее число, месяц и год;

Dау(дата) - извлекает из даты день, например дата - 12,09,97, результат применения функции число 12;

Моnth(дата) - извлекает из даты месяц, например дата - 12,09,97, результат применения функции - число 9;

Уеаг(дата) - извлекает из даты год, например дата - 12-09,97, результат применения функции - число 97;

Weekday(дата) - извлекает из даты день недели в американской системе нумерации дней, а именно в примере - дата 12,09,97, результат применения функции - число 6, что соответствует пятнице,

DatePart(HHTepBan, дата) - здесь аргумент "интервал" - это сокращенное название нужного компонента даты, а дата - конкретное значение даты или имя поля с датой

Например:

DatePart("H",#12,09,97#) - день недели - 6, т е пятница,

DatePart("HH",#12,09,97#) - неделя года - 37,

DatePart("K",# 12,09,97#) - квартал года - 3

DatePart("a",#12,09,97#) - день -12,

DatePart("M",#12,09,97#) - месяц - 9, \ DatePart("rrrr",#12,09,97#) - год -1997

 

Пример запроса. Определить, сколько лет прошло с момента выхода статьи описавшей стандарт SQL (предположим, что название статьи ”Стандарт SQL”)

 

SELECT Month(Date()-yearpub)

       FROM titles INNER JOIN publishers ON

           titles.pub_id = publishers.pub_id

      WHERE  publisher = "Стандарт SQL";

 

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

 

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

Для просмотра результата выполнения запросов необходимо чтобы в таблицах были внесены данные соответствующими сформулированным запросам. При этом данные в запросах (даты, фамилии, количество и т.п.) могут быть изменены по факту внесенных данных в БД.

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

Например: SELECT COUNT(*) AS Количество_строк FROM titles;

 

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

 

Найти заказы сделанные в январе.

Найти изделия, которые поставляются в количестве не меньше 10, и не больше 100.

Получить список изделий получаемых заказчиком “з-д «Красный луч»”, цена которых более 50 тыс. грн.

Сколько деталей «Болт» по всем заказам получил заказчик “з-д «Красный луч»”.

Определить наименования деталей заказанных в период с 6/10/97 по 10/10/97, которые не заказывал з-д «Красный луч».

Получить список наименований изделий, поставки которых превышают 10 тыс.

На какую сумму заказано деталей з-дом «Красный луч»

Какие заказчики заказывали деталь «Болт».

Определить среднее количество поставок детали «Болт» за 1997 год.

Найти заказчика, заказавшего самую дорогую деталь.

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

Какие предложения SELECT являются обязательными?

Что задает предложение WHERE?

Какие типы соединений (JOIN) поддерживает инструкция SELECT?

Какая последовательность предложений инструкции SELECT?

В каком случае обязательно указывать имя таблицы перед именем поля?

Как формировать вычисляемые поля в SELECT?

Можно ли соединять более двух таблиц операцией JOIN?

Какой альтернативный синтаксис операции JOIN (с использованием WHERE) можно использовать для корректного выполнения запроса?


 

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

72474. Подшипники. Назначение и классификация 473.5 KB
  Подшипники служат опорами для валов и вращающихся осей. Они воспринимают радиальные и осевые нагрузки, приложенные к валу, и передают их на раму машины. При этом вал должен фиксироваться в определенном положении и вращаться вокруг заданной геометрической оси.
72475. ПОДШИПНИКИ КАЧЕНИЯ. ОБЩИЕ СВЕДЕНИЯ И КЛАССИФИКАЦИЯ 975.5 KB
  Применение подшипников качения позволило заменить трение скольжения трением качения. Конструкция подшипников качения позволяет изготовлять их в массовых количествах как стандартную продукцию что значительно снижает стоимость производства.
72476. ЧЕРВЯЧНЫЕ ПЕРЕДАЧИ 380 KB
  Существенное отличие червячной передачи от зубчатой заключается в том, что окружные скорости червяка и колеса не совпадают как по величине, так и по направлению. Они направлены друг к другу под углом перекрещивания.
72477. ОСОБЕННОСТИ РАСЧЕТА ПЛАНЕТАРНЫХ ПЕРЕДАЧ 307.5 KB
  Планетарными называют передачи, включающие в себя зубчатые колеса с перемещающимися осями (рис.10.1,а). Передача состоит из центрального колеса с наружными зубьями, центрального колеса b с внутренними зубьями и водила Н, но котором укреплены оси сателлитов g.
72478. ПЕРЕДАТОЧНОЕ ОТНОШЕНИЕ ОДНОСТУПЕНЧАТЫХ И МНОГОСТУПЕНЧАТЫХ ЗУБЧАТЫХ ПЕРЕДАЧ 181 KB
  Масса и габариты редуктора в значительной степени зависят от того, как распределено общее передаточное отношение по ступеням передачи. Лучшие показатели имеют редукторы, у которых диаметры колес (а не шестерен) всех ступеней близки между собой.
72479. МЕХАНИЧЕСКИЕ ПЕРЕДАЧИ 785.5 KB
  Передача состоит из двух шкивов закрепленных на валах и ремня охватывающего шкивы. В зависимости от формы поперечного сечения ремня различают: плоскоременную рис. Основные преимущества ременной передачи: возможность передачи движения на значительное расстояние до 15 м и более...
72480. ЗУБЧАТЫЕ ПЕРЕДАЧИ 740.5 KB
  По форме профиля зуба различают: эвольвентные и круговые передачи. Наиболее распространен эвольвентный профиль зуба, предложенный Эйлером в 1760 году. Он обладает целым рядом существенных, технологических и эксплутационных преимуществ.
72481. РАСЧЕТ ЦИЛИНДРИЧЕСКИХ ПЕРЕДАЧ 426.5 KB
  Преимущественное применение получили колеса с круговыми зубьями. Они менее чувствительны к нарушению точности взаимного расположения колес, их изготовление проще и производится на специальных станках для нарезания и шлифования этих колес в условиях как массового...
72482. ШПОНОЧНЫЕ, ЗУБЧАТЫЕ (ШЛИЦЕВЫЕ) И ПРОФИЛЬНЫЕ СОЕДИНЕНИЯ 540.5 KB
  Шпоночные зубчатые шлицевые и профильные соединения служат для закрепления деталей на осях и валах. Соединения нагружаются в основном вращающим моментом. Все основные виды шпонок можно разделить на клиновые и призматические. Первая группа шпонок образует напряженные а вторая ненапряженные соединения.