Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа № 2
Тема: Организация запросов на выборку данных средствами SQL.
Цель работы: Изучить синтаксис инструкции SQL – SELECT, для создания запросов на выборку данных. Используя предложения WHERE задать условия отбора строк из таблиц определенных в предложении FROM. Изучить организацию выборки данных из нескольких таблиц БД.
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.
Часто возникает ситуация, когда выборку данных необходимо производить из отношения, которое является результатом соединения двух других таблиц.
Например, нужно получить из базы данных 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;
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:
3. Оформление отчета
4. Контрольные вопросы