Цель и задачи практического занятия

Цель работы – освоить навыки применения и разработки SQL-запросов для базы данных.

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

- Освоить принципы построения запросов.

- Разработать программный модуль обработки данных.

План практического занятия

 

1) Ознакомится теоретической справкой.

2) Разработать СУБД «Книги».

3) Сформировать и выполнить запрос на языке SQL по варианту.

4) Оформить отчет.

5) Защитить работу преподавателю.

Задание на работу (рабочее задание)

 

Разработать БД «Книги», включающую обработку следующих запросов:

ВАРИАНТЫ:

 

Вариант №1

 

1) Найти книги, авторы которых совпадают

2) Ввести данные об издательстве с добавлением недостающего города

3) Выдать список издательств, у которых нет книг в заданном году

 

Вариант № 2

 

1) Найти книги автора, которые были изданы в определенном издательстве

2) Добавить книгу в БД, если ее там нет

3) Отсортировать издательства по количеству книг

 

Вариант № 3

 

1) Всех авторов, которые издавались в определенном году

2) Ввести данные о книге с добавлением недостающих авторов

3) Вывести список книг, отсортированных по издательству и по автору

 

Вариант № 4

 

1) Всех авторов, которые издавались в определенном издательстве

2) Ввести данные о книге с добавлением недостающих издательств

3) Отсортировать авторов по количеству книг

 

Вариант №5

 

1) Найти книги, у которых более одного автора

2) Удалить данные об издательстве и о всех книгах, там изданных.

3) Выдать список издательств, у которых есть книги в заданном году

 

Вариант № 6

 

1) Найти все издательства в конкретном городе

2) Изменить в записях книг одного издательства на другое издательство

3) Выдать список книг, экземпляры которых изданы в разных издательствах

 

Вариант № 7

 

1) Выдать все книги, изданные в последние 5 лет

2) Удалить книги конкретного автора

3) Вывести список книг конкретного автора с расчетом % личного участия

 

Вариант № 8

 

1) Всех авторов, которые издавались в определенном городе

2) Ввести данные о новом издательстве книги с добавлением недостающих издательств

3) Отсортировать книги сначала по издательству, потом году издательства

 

Вариант №9

 

1) Найти книги конкретного автора и посчитать их количество

2) Выполнить коррекцию записи о книге

3) Выдать список издательств, у которых нет книг за последние 5 лет

 

Вариант № 10

 

1) Посчитать количество книг автора по издательствам

2) Удалить автора, если у него нет книг

3) Выдать список издательств, у которых есть книги за последние 5 лет

 

Вариант № 11

 

1) Посчитать количество книг автора по годам

2) Удалить издательство, если в нем не издавали книг

3) Отсортировать книги данного издательства по годам издательства

 

Вариант № 12

 

1) Посчитать количество книг по годам и издательствам

2) Удалить из базы книги старше 10 лет

3) Подсчитать количество книг данного издательства по годам издательства

 

Методические указания

3.1 Язык запросов SQL

SQL символизирует собой Структурированный Язык Запросов. Это - язык, который дает Вам возможность создавать и работать в реляционных базах данных, являющихся наборами связанной информации, сохраняемой в таблицах.

Информационное пространство становится более унифицированным. Это привело к необходимости создания стандартного языка, который мог бы использоваться в большом количестве различных видов компьютерных сред. Стандартный язык позволит пользователям, знающим один набор команд, использовать их для создания, нахождения, изменения и передачи информации - независимо от того, работают ли они на персональном компьютере, сетевой рабочей станции, или на универсальной ЭВМ.

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

Элегантность и независимость от специфики компьютерных технологий, а также его поддержка лидерами промышленности в области технологии реляционных баз данных, сделало SQL (и, вероятно, в течение обозримого будущего оставит его) основным стандартным языком. По этой причине, любой, кто хочет работать с базами данных 90-х годов, должен знать SQL.

Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов) и в данное время также принимается ISO (Международной Организацией по Стандартизации). Однако, большинство коммерческих программ баз данных расширяют SQL без уведомления ANSI, добавляя различные особенности в этот язык, которые, как они считают, будут весьма полезны. Иногда они несколько нарушают стандарт языка, хотя хорошие идеи имеют тенденцию развиваться и вскоре становиться стандартами " рынка " сами по себе в силу полезности своих качеств.

На данном уроке мы будем, в основном, следовать стандарту ANSI, но одновременно иногда будет показывать и некоторые наиболее общие отклонения от его стандарта.

Точное описание особенностей языка приводится в документации на СУБД, которую Вы используете. SQL системы InterBase 4.0 соответствует стандарту ANSI-92 и частично стандарту ANSI-III.

Состав языка SQL

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

Поэтому, в язык SQL в качестве составных частей входят:

язык манипулирования данными (Data Manipulation Language, DML)

язык определения данных (Data Definition Language, DDL)

язык управления данными (Data Control Language, DCL).

Подчеркнем, что это не отдельные языки, а различные команды одного языка. Такое деление проведено только лишь с точки зрения различного функционального назначения этих команд.

Язык манипулирования данными используется, как это следует из его названия, для манипулирования данными в таблицах баз данных. Он состоит из 4 основных команд:

SELECT	(выбрать)
INSERT	(вставить)
UPDATE	(обновить)
DELETE	(удалить)

Язык определения данных используется для создания и изменения структуры базы данных и ее составных частей - таблиц, индексов, представлений (виртуальных таблиц), а также триггеров и сохраненных процедур. Основными его командами являются:

1. CREATE DATABASE	(создать базу данных)
2. CREATE TABLE	(создать таблицу)
3. CREATE VIEW	(создать виртуальную таблицу)
4. CREATE INDEX	(создать индекс)
5. CREATE TRIGGER	(создать триггер)
6. CREATE PROCEDURE	(создать сохраненную процедуру)
7. ALTER DATABASE	(модифицировать базу данных)
8. ALTER TABLE	(модифицировать таблицу)
9. ALTER VIEW	(модифицировать виртуальную таблицу)
10. ALTER INDEX	(модифицировать индекс)
11. ALTER TRIGGER	(модифицировать триггер)
12. ALTER PROCEDURE	(модифицировать сохраненную процедуру)
13. DROP DATABASE	(удалить базу данных)
14. DROP TABLE	(удалить таблицу)
15. DROP VIEW	(удалить виртуальную таблицу)
16. DROP INDEX	(удалить индекс)
17. DROP TRIGGER	(удалить триггер)
18. DROP PROCEDURE	(удалить сохраненную процедуру)

Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать "язык управления доступом". Он состоит из двух основных команд:

GRANT	(дать права)
REVOKE	(забрать права)

С точки зрения прикладного интерфейса существуют две разновидности команд SQL:

интерактивный SQL

встроенный SQL.

Интерактивный SQL используется в специальных утилитах (типа WISQL или DBD), позволяющих в интерактивном режиме вводить запросы с использованием команд SQL, посылать их для выполнения на сервер и получать результаты в предназначенном для этого окне. Встроенный SQL используется в прикладных программах, позволяя им посылать запросы к серверу и обрабатывать полученные результаты, в том числе комбинируя set-ориентированный и record-ориентированный подходы.

Мы не будем приводить точный синтаксис команд SQL, вместо этого мы рассмотрим их на многочисленных примерах, что намного более важно для понимания SQL, чем точный синтаксис, который можно посмотреть в документации на Вашу СУБД.

Итак, начнем с рассмотрения команд языка манипулирования данными.

Реляционные операции. Команды языка манипулирования данными

Наиболее важной командой языка манипулирования данными является команда SELECT. За кажущейся простотой ее синтаксиса скрывается огромное богатство возможностей. Нам важно научиться использовать это богатство!

На данном уроке предполагается, если не оговорено противное, что все команды языка SQL вводятся интерактивным способом. В качестве информационной основы для примеров мы будем использовать базу данных "Служащие предприятия" (employee.gdb), входящую в поставку Delphi и находящуюся (по умолчанию) в поддиректории \IBLOCAL\EXAMPLES.

Начнем с базовых операций реляционных баз данных. Таковыми являются:

выборка (Restriction)

проекция (Projection)

соединение (Join)

объединение (Union)

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

SELECT * FROM country

Получить все строки таблицы Country

COUNTRY	CURRENCY
USA	Dollar
England	Pound
Canada	CdnDlr
Switzerland	SFranc
Japan	Yen
Italy	Lira
France	FFranc
Germany	D-Mark
Australia	ADollar
Hong Kong	HKDollar
Netherlands	Guilder
Belgium	BFranc
Austria	Schilling
Fiji	Fdollar

В этом примере и далее - для большей наглядности - все зарезервированные слова языка SQL будем писать большими буквами. Красным цветом будем записывать предложения SQL, а светло-синим - результаты выполнения запросов.

SELECT * FROM country

WHERE currency = "Dollar".

Получить подмножество строк таблицы Country,удовлетворяющее условию Currency = "Dollar"

Результат последней операции выглядит следующим образом:

COUNTRY    CURRENCY

=============== ==========

USA        Dollar

Операция проекции позволяет выделить подмножество столбцов таблицы. Например:

SELECT currency FROM country.

Получить список денежных единиц

CURRENCY

==========

Dollar

Pound

CdnDlr

SFranc

Yen

Lira

FFranc

D-Mark

ADollar

HKDollar

Guilder

BFranc

Schilling

FDollar

На практике очень часто требуется получить некое подмножество столбцов и строк таблицы, т.е. выполнить комбинацию Restriction и Projection. Для этого достаточно перечислить столбцы таблицы и наложить ограничения на строки. SELECT currency FROM country WHERE country = "Japan".

Найти денежную единицу Японии

CURRENCY

==========

Yen

SELECT first_name, last_name

FROM employee

WHERE first_name = "Roger"

Получить фамилии работников, которых зовут "Roger"

FIRST_NAME	LAST_NAME
Roger	De Souza
Roger	Reeves

Эти примеры иллюстрируют общую форму команды SELECT в языке SQL (для одной таблицы):

SELECT	(выбрать) специфицированные поля
FROM	(из) специфицированной таблицы
WHERE	(где) некоторое специфицированное условие является истинны

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

SELECT first_name, last_name, proj_name

FROM employee, project

WHERE emp_no = team_leader

Получить список руководителей проектов

FIRST_NAME	LAST_NAME	PROJ_NAME
Ashok	Ramanathan	Video Database
Pete	Fisher	DigiPizza
Chris	Papadopoulos	AutoMap
Bruce	Young	MapBrowser port
Mary S.	MacDonald	Marketing project 3

Операция объединения позволяет объединять результаты отдельных запросов по нескольким таблицам в единую результирующую таблицу. Таким образом, предложение UNION объединяет вывод двух или более SQL-запросов в единый набор строк и столбцов.

SELECT first_name, last_name, job_country

FROM employee

WHERE

UNION

SELECT contact_first, contact_last, country customer

Получить список работников и заказчиков, проживающих во Франции

FIRST_NAME	LAST_NAME	JOB_COUNTRY
Jacques	Glon	France
Michelle	Roche	France

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

SELECT	[DISTINCT] список_выбираемых_элементов (полей)
FROM	список_таблиц (или представлений)
[WHERE	предикат]
[GROUP BY	поле (или поля) [HAVING предикат]]
[UNION	другое_выражение_Select]
[ORDER BY	поле (или поля) или номер (номера)];

 

Отметим, что под предикатом понимается некоторое специфицированное условие (отбора), значение которого имеет булевский тип. Квадратные скобки означают необязательность использования дополнительных конструкций команды. Точка с запятой является стандартным терминатором команды. Отметим, что в WISQL и в компоненте TQuery ставить конечный терминатор не обязательно. При этом там, где допустим один пробел между элементами, разрешено ставить любое количество пробелов и пустых строк - выполняя желаемое форматирование для большей наглядности.

Гибкость и мощь языка SQL состоит в том, что он позволяет объединить все операции реляционной алгебры в одной конструкции, "вытаскивая" таким образом любую требуемую информацию, что очень часто и происходит на практике.

Команда SELECT

Простейшие конструкции команды SELECT

Итак, начнем с рассмотрения простейших конструкций языка SQL. После такого рассмотрения мы научимся:

- назначать поля, которые должны быть выбраны

- назначать к выборке "все поля"

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

- подставлять собственные заголовки полей в результирующей таблице

- производить вычисления в списке выбираемых элементов

- использовать литералы в списке выбираемых элементов

- ограничивать число возвращаемых строк

- формировать сложные условия поиска, используя реляционные и логические операторы

- устранять одинаковые строки из результата.

- Список выбираемых элементов может содержать следующее:

- имена полей

- *

- вычисления

- литералы

- функции

- агрегирующие конструкции

Список полей

SELECT first_name, last_name, phone_no

FROM phone_list

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

FIRST_NAME	LAST_NAME	PHONE_NO
Terri	Lee	(408) 555-1234
Oliver H.	Bender	(408) 555-1234
Mary S.	MacDonald	(415) 555-1234
Michael	Yanowski	(415) 555-1234
Robert	Nelson	(408) 555-1234
Kelly	Brown	(408) 555-1234
Stewart	Hall	(408) 555-1234
...

Отметим, что PHONE_LIST - это виртуальная таблица (представление), созданная в InterBase и основанная на информации из двух таблиц - EMPLOYEE и DEPARTMENT. Она не показана на рис.1, однако, как мы уже указывали в общей структуре команды SELECT, к ней можно обращаться так же, как и к "настоящей" таблице.

Все поля

SELECT *

FROM phone_list

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

EMP_NO	FIRST_NAME	LAST_NAME	PHONE_EXT	LOCATION	PHONE_NO
12	Terri	Lee	256	Monterey	(408) 555-1234
105	Oliver H.	Bender	255	Monterey	(408) 555-1234
85	Mary S.	MacDonald	477	San Francisco	(415) 555-1234
127	Michael	Yanowski	492	San Francisco	(415) 555-1234
2	Robert	Nelson	250	Monterey	(408) 555-1234
109	Kelly	Brown	202	Monterey	(408) 555-1234
14	Stewart	Hall	227	Monterey	(408) 555-1234
...