Методические указания к выполнению лабораторных работ Омск 2006 омский государственный университет факультет компьютерных наук

Рекомендуемая литература

1. 
   Коголовский М.Р. Энциклопедия технологий баз данных: Эволюция технологий; Технологии и стандарты; Инфраструктура; Терминология – М.: Финансы и статистика, 2002. – 800 с.
   
2. 
   Кренке Д. Теория и практика построения баз данных, 9-е издание: Пер. с англ. – СПб.: «Питер», 2005. – 864 с.
   
3. 
   Питер Колетски, Д-р Поль Дорси. Oracle Desiner. Настольная книга пользователя, 2-е издание – М.: Издательство «ЛОРИ», 1999. – 592 с.
   
4. 
   Хомоненко А.Д., Цыганков В.М., Мальцев М.Г. Базы данных: Учебник для высших учебных заведений, 5-е издание – М.: Бином-Пресс; СПб.: КОРОНА принт, 2006. – 736 с.
   
5. 
   http://www.idefinfo.ru (описания стандартов проектирования)
   
6. 
   http://www.intuit.ru/department/database/basedbw/3/3.html (описание CASEStudio).
   
7. 
   http://www.interface.ru/oracle/des2000x.html (Описание Oracle Designer).
   

Методические рекомендации
по выполнению лабораторной работы № 3

Таблица 3. Инструкции языка SQL

Вид	Инструкция	Назначение
Data Definition Language (DDL)	CREATE TABLE	Создание таблицы
	DROP TABLE	Удаление таблицы
	ALTER TABLE	Изменение структуры таблицы
	CREATE INDEX	Создание индекса
	DROP INDEX	Удаление индекса
	CREATE VIEW	Создание представления
	DROP VIEW	Удаление представления
Data Manipulation Language (DML)	SELECT	Выборка записей
	UPDATE	Изменение записей
	INSERT	Вставка записей
	DELETE	Удаление записей

Таблица 4. Агрегирующие функции

Агрегирующая функция	Результат	Примечание
SUM([DISTINCT] выражение)	Сумма [различных] значений	только для числовых выражений, NULL значения игнорируются
AVG([DISTINCT] выражение)	Средняя величина [различных] значений	только для числовых выражений, NULL значения игнорируются
COUNT([DISTINCT] выражение)	Количество [различных] ненулевых значений	для всех типов выражений, NULL значения игнорируются
COUNT(*)	Количество выбранных строк	считают и NULL значения
MAX(выражение)	Максимальное значение	для всех типов выражений, NULL значения игнорируются
MIN(выражение)	Минимальное значение	для всех типов выражений, NULL значения игнорируются

Рассмотрим на примерах использование основных SQL инструкций.

Пример 1.

Для добавления новой таблицы в базу данных, используется инструкция CREATE TABLE.

CREATE TABLE films (

film_id INTEGER NOT NULL,

film_name VARCHAR(100) NOT NULL,

film_time time,

film_director VARCHAR(50) NOT NULL,

film_actors VARCHAR(255),

film_year INTEGER NOT NULL,

PRIMARY KEY (film_id))

Эта инструкция присваивает новой таблице имя FILMS и определяет для каждого ее столбца имя и тип данных, хранимых в нем.
Пример 2.

Для изменения структуры уже определенных таблиц используется инструкция ALTER TABLE.

ALTER TABLE film_distributions ADD FOREIGN KEY (film_id) REFERENCES films(film_id) ON DELETE CASCADE
Пример 3.

Для удаления таблицы из базы данных используют инструкцию DROP TABLE

DROP TABLE films
Пример 4.

Для выборки данных во всех SQL-запросах используется инструкция SELECT.

SELECT * FROM films

WHERE films.film_year=1999

Результатом выборки будет список фильмов вышедших в 1999 году:

FILM_ID	FILM_NAME	FILM_TIME	FILM_DIRECTOR	FILM_ACTORS	FILM_YEAR
5	Ghost Dog: The Way of the Samurai	116	Jim Jarmusch	Forest Whitaker,John Tormey,Cliff Gorman,Henry Silva,Isaach de Bankole,Frank Minucci	1999
13	Man on the moon	118	Milos Forman	Jim Carrey,Danny DeVito,Courtney Love,Paul Giamatti,Vincent Schiavelli	1999

Для построения выборки из нескольких таблиц используется два способа соединения отношений:

1. 
   Соединение по одноименным атрибутам с помощью условия WHERE;
   
2. 
   Соединение двух таблиц с помощью внешнего соединения LEFT (RIGHT, FULL) OUTER JOIN.
   

Рассмотрим и сравним следующие два запроса.
Пример 5.

SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date

FROM rented_films LEFT OUTER JOIN returned_rented_films ON returned_rented_films.rent_id=rented_films.rent_id,

clients

WHERE clients.client_id=rented_films.client_id

CLIENT_FIO	RENT_START_DATE	RENT_END_DATE
Соколов Михаил Евгеньевич	28.06.2006	29.08.2006
Тимкина Наталья Дмитриевна	12.09.2006	13.09.2006
Колосов Антон Павлович	11.10.2005	13.10.2005
Соколов Михаил Евгеньевич	01.05.2006	02.05.2006
Гладкий Петр Сергеевич	05.06.2006	06.06.2006
Гладкий Петр Сергеевич	13.09.2006	NULL

SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date

FROM rented_films,

returned_rented_films,

clients

WHERE clients.client_id=rented_films.client_id AND

returned_rented_films.rent_id=rented_films.rent_id

CLIENT_FIO	RENT_START_DATE	RENT_END_DATE
Тимкина Наталья Дмитриевна	12.09.2006	13.09.2006
Соколов Михаил Евгеньевич	28.06.2006	29.08.2006
Соколов Михаил Евгеньевич	28.06.2006	29.08.2006
Соколов Михаил Евгеньевич	01.05.2006	02.05.2006
Гладкий Петр Сергеевич	05.06.2006	06.06.2006
Колосов Антон Павлович	11.10.2005	13.10.2005

Механизм работы этих двух способов соединения несколько различен. В случае соединения через условие WHERE будет возвращено столько записей, сколько имеют совпадения по одноименному связующему атрибуту. При использовании OUTER JOIN количество записей в выборке будет равно количеству записей в таблице слева от JOIN. Каждой записи таблицы слева будет сопоставлена, согласно заданному условию, запись из таблицы справа, если же соответствующей записи из правой таблицы нет, то будет сопоставлено NULL-значение.
Пример 6.

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

Следующий пример создает представление данных client_list основанное на предыдущем запросе.

CREATE VIEW client_list AS

SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date

FROM rented_films LEFT OUTER JOIN returned_rented_films ON returned_rented_films.rent_id=rented_films.rent_id, clients

WHERE clients.client_id=rented_films.client_id

Пример 7.

Пример использования функций агрегирования (выборка с группировкой).

SELECT film_name,cnt_clients

FROM films,

(SELECT film_id, COUNT(DISTINCT client_id) AS cnt_clients

FROM rented_films GROUP BY film_id) cnt

WHERE films.film_id=cnt.film_id

ORDER BY film_name

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

Пример 8.

Для добавления новой информации в базу данных в языке SQL используется инструкция INSERT.

INSERT INTO medium_type_directory (medium_type) VALUES ('dvd')

В таблицу medium_type_directory добавлена новая запись.

Пример 9.

Инструкция DELETE удаляет какую-либо информацию из базы данных.

DELETE FROM clients WHERE client_id=15

В примере выполняется удаление записи о клиенте с client_id равным 15.

Пример 9.

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

UPDATE clients

SET client_phone_number=’795-55-78-48’

WHERE client_fio=’John N. Doe’

В данном примере у клиента John N. Doe будет изменен номер телефона.