17213

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

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

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

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

Русский

2013-06-30

66.5 KB

44 чел.

Лабораторная работа № 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) можно использовать для корректного выполнения запроса?


 

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

63853. Этническая идентичность как компонент трансгрессии социокультурного пространства 57.5 KB
  Этническая идентичность как компонент трансгрессии социокультурного пространства Современное общество характеризуется постоянными изменениями открытиями и новшествами которые постепенно меняют мир до неузнаваемости. Данные изменения могут происходить с такими крупными структурами как устройство...
63854. Гендерные стереотипы в меняющемся обществе 21.85 KB
  Гендерные стереотипы в меняющемся обществе Взаимодействие между людьми не строится на общепризнанных в соответствующей культуре образцах не может протекать вне закономерностей социального восприятия. Гендерные стереотипы представляют собой культурно и социально-обусловленные мнения...
63855. Коммуникации в социокультурной динамике: коммуникативные практики посткризисного региона Дагестан как зеркало социокультурных изменений 177 KB
  Затем мы провели контент-анализ на основании заголовков и общего содержания записей. При этом мы ограничились лишь последними 10 записями каждого блога так как этого количества с одной стороны достаточно для того чтобы определить соотношение предметов обсуждения по их содержанию а с другой стороны это именно...
63856. Трансформация ритуалов в современном обществе 30.67 KB
  Ритуалы это своеобразные камни для строительства упорядоченного образа жизни. Соблюдение повседневных ритуалов помогает нам структурировать свой день и делит трудные периоды на мелкие части. Но всегда ли мы задумываемся об истинном смысле ритуалов На первый взгляд не так уж много ритуалов наполняют нашу жизнь...
63857. Трансформация социокультурного пространства в рамках экономической системы 52.5 KB
  На современном этапе развития социально-экономической формации возникает потребность в изучении и анализе социального пространства с междисциплинарных позиций в силу возрастающего влияния на все сферы жизни общества. Базовыми характеристиками современного общества выступают...
63858. Социальная идентичность: гендерный аспект 54 KB
  Социологическое определение идентичности отражает в свою очередь ее непосредственное отношение к личности и обществу. В своей книге Детство и общество Эриксон писал: Я могу попытаться более явно представить суть идентичности только рассмотрев ее с разных точек зрения.
63859. Экономические условия и политические аспекты трансформации социокультурного пространства в истории России 59.5 KB
  Если говорить о категориях институциональной жизни общества то можно выделить множество сфер которые подвергаются изменениям. Оно определяет общие ориентиры поведения людей совокупность социокультурных норм и ценностей общества которые и определяют это поведение.
63860. Анализ организационно-экономических основ инвестиционной деятельности муниципального образования 24.12 KB
  В условиях ограниченности собственных финансовых средств требующихся для реализации планов социально-экономического развития муниципальных образований а также отдельных целевых программ необходимо привлекать инвестиции аккумулировать сбережения и накопления граждан...
63861. Проблемы защиты коренных народов 44 KB
  В Приангарье проживает 1950 человек которые относятся к категории коренных малочисленных народов КМН 1272 эвенка и 678 тофаларов по переписи населения 2010 года. Постановлением Правительства Иркутской области от 1 апреля 2013 года № 106пп утверждена долгосрочная целевая программа...