13 Лекция

Язык запросов к базам данным SQL

Управление реляционными БД

Использование реляционных БД возможно только при наличии эффективных средств управления данными.

В результате исследований был предложен ряд языков:

• SQL – структурированный язык запросов,

• QBE – Query By Example – запрос по образцу,

• QUEL - Query Language – язык запросов.

SQL включает все средства, распространен.

Язык запросов к базам данным SQL

- язык структурированных запросов (Structured Query Language).

• Является стандартом обмена информацией между программным продуктом и БД (ISO/IEC 9075:1992).

• Однако ни одна СУБД полностью не поддерживает стандарт SQL-92.

• Имеются диалекты этого языка.

Можно выделит две части языка:

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

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

Язык SQL отличается от языка программирования:

• не имеет структур управления,

• оперирует только имеющимися данными.

Процесс обработки запроса

SQL –запрос формируется и хранится в приложении, а выполняется непосредственно БД.

Передача SQL –запроса в БД и получение результат является функцией механизма доступа к БД.

В Delphi это протекает следующим способом:

Команды языка SQL

Select – отбор данных,

Create Table – создание новой таблицы,

Drop Table – удаление существующей таблицы,

Alter Table – модификация таблицы,

Create Index – создание нового индекса,

Drop Index – удаление индекса,

Update – модификация записей по условию

Insert – вставка записей по условию,

Delete – удаление записей по условию.

Структура SQL-запроса

SQL-запрос – текстовое выражение, описывающее инструкцию как получить (вставить, обновить, удалить) информацию.

Включает в себя:

• команды SELECT, INSERT, UPDATE, DELETE для получения, вставки, обновления и удаления записей в таблицах,

• ключевые слова для подробного описания операций,

• названия таблиц БД,

• название функций языка, арифметические операторы и операторы сравнения.

Оператор SELECT

SELECT <список_выбора>

[ INTO <новая_таблица> ]

FROM <исходная_таблица>

[ WHERE <условие_отбора> ]

[ GROUP BY <список полей> ]

[ HAVING <условие_отбора> ]

[ ORDER BY <список выражений> [ASC | DESC ] ]

Оператор SELECT (простая форма):

SELECT {* | All | Distinct field1,… fieldN}

FROM table1{, table2,… tableN}

Отношение Товары:

Выбор всех полей:

SELECT * FROM Товары

либо

SELECT all FROM Товары

Выбор отдельных полей:

SELECT Товар FROM Товары

SELECT Товар, Цена FROM Товары

Выбор неповторяющихся полей:

SELECT Distinct Товар FROM Товары

Оператор SELECT (задание условий)

SELECT <список_выбора>

FROM <исходная_таблица>

WHERE <условие_отбора>

WHERE используется для ограничения записей.

В качестве условия могут быть использованы:

• операторы сравнения (=, <>, >, >=, …..),

• логические операторы (is null, bettween.. …and, like, any , in,……),

• операторы объединения (and, or),

• операторы отрицания (not).

SELECT Товар, Цена

FROM Товары

Where Товар = 'Стол'

SELECT Товар, Цена

FROM Товары

Where Цена <= 5000 /// Where (Цена > 5000) and (Цена < 10000)

SELECT Товар, Цена

FROM Товары

Where Цена Between 5000 and 15000

Between различает порядок значений (первое должно быть наименьшим):

SELECT Товар, Цена

FROM Товары

Where Товар Between 'А' and 'С'

- Стол не будет выведен, т.к. сравнение идет и по длине строки.

SELECT Товар

FROM Товары

Where Страна IS NULL

– выборка записей, у которых не указана страна.

SELECT Товар

FROM Товары

Where Страна IN (РФ, Китай)

• выборка записей, у которых значение

поля Страна входит в список.

Опция Like применяется для сравнения значения поля с заданным шаблоном.

Для задания шаблонов используются:

% - заменяет последовательность символов любой (и нулевой) длины,

_ - заменяет единичный символ.

SELECT Товар

FROM Товары

Where Товар Like 'Д% ' – выводит записи начинающие с 'Д '

Примеры: имеем отношение

STUDENT (Kod, Fam, Im, Gruppa, Stip, Ocenka)

Запросы (использовать все варианты ответа):

Вывести всю информацию о студентах.

Вывести фамилии студентов и их оценки.

Вывести фамилии студентов (уникальные значения).

Вывести фамилии студентов, получающих стипендию (не получающих).

Вывести фамилии студентов, получивших 4 и 5.

Вывести фамилии студентов, имеющих имена Владимир и Анатолий.

Вывести фамилии студентов, имеющих фамилию от 'К ' до 'Р' включительно.

Вывести фамилии студентов, не имеющих оценки.

Функции агрегирования

Выполняют:

• COUNT – подсчет записей в столбце (с полем любого типа),

• SUM – сумму значений в столбце,

• MIN – минимальное значение в столбце,

• MAX – максимальное значение в столбце,

• AVG – среднее значение в столбце.

SELECT Count(*) FROM Товары

– определяет общее число записей.

SELECT Count (Distinct Товар )

FROM Товары

– определяет число наименований товара.

SELECT min(Цена) FROM Товары

- выводит минимальную стоимость товара.

GROUP BY – группирование результатов

SELECT и GROUP BY тесно связаны между собой.

Если в запросе есть группировка, то каждый элемент списка SELECT должен иметь единственное значение для всей группы.

В SELECT могут включаться:

• имена столбцов,

• агрегатные функции,

• константы,

• выражения.

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

Запросы:

Вывести количество студентов в каждой группе.

SELECT Gruppa, COUNT(*) AS kol

FROM student

GROUP BY Gruppa

Вывести сумму стипендии, выдаваемой каждой группе.

SELECT Gruppa, SUM(Stip) AS stipend

FROM student

GROUP BY Gruppa

Вывести среднее значение стипендии по группам:

SELECT Gruppa, AVG (Stip) AS sredn

FROM student

GROUP BY Gruppa

Если же надо определить min стоимости по всем группам товаров, то:

select Товар, min (Цена)

from Tovar

Group by Товар

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

Имена столбцов в Having должны присутствовать и в списке Group By или применяться в агрегатных функциях.

Имеем отношение S (ФИО, Дисциплина, Оценка).

Запрос: Вывести общее количество студентов, сдавших все экзамены (т.е.3).

Select ФИО

From s

Group by ФИО

Having count (Дисциплина) = 3

Использование вычисляемых полей

Пусть имеем дополнительное отношение: Продажи (Код_п, Код, Заказано, Продано), связанное по полю КОД с таблицей Товары.

Определить сумму продаж и сумму заказов.

Select Код, Заказано, Продано,

Цена*Продано AS Сумма_продаж

Цена*Заказано AS Сумма_заказа

From Продажи, Товары

(первые три поля можно и не показывать)

Упорядочивание результатов запроса

SELECT *

FROM Товары

ORDER BY Цена

SELECT *

FROM Товары

ORDER BY Цена DESC

Многотабличные запросы

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

При этой операции объединение информации из таблиц происходит посредством образования пар связанных строк, выбранных из каждой таблицы.

Таблицам можно присвоить имена-псевдонимы, что полезно для осуществления операции соединения таблицы с самой собою и в других ситуациях

Самый простой запрос:

SELECT *

FROM R1, R2 – соответствует декартову произведению таблиц R1, R2 .

A B

a b c d

1 2 2 4

2 1 3 3

SELECT * FROM A, B

SELECT *

FROM Товары, Продано

- будет построено «декартово произведение» таблиц

Поэтому перечисление таблиц используется с условием соединения записей из разных таблиц в предложении WHERE:

SELECT *

FROM A, B

WHERE a = c

Результат:

a b c d

2 1 2 4

Соединение таблиц

Соединение таблиц (вывод всех полей, для Стол)

Соединение таблиц (вывод некоторых полей)

Соединения равенства

SELECT table1.field1,… tableN.fieldN

FROM table1, … tableN

WHERE table1.common_field1= table2.common_field1

{AND table1.common_field2= table2.common_field2}

Запрос: Вывести сведения о клиентах и их покупках (сведения в таблице ПРОДАЖИ).

SELECT Клиенты.Фамилия, Продажи.Продано

FROM Клиенты, Продажи

WHERE

Клиенты.Код_клиента = Продажи.Код_клиента

Запрос: Вывести для каждого клиента суммарное количество покупок

SELECT Клиенты.Фамилия, SUM (Продажи.Продано) AS Количество

FROM Клиенты, Продажи

WHERE

Клиенты.Код_клиента = Продажи.Код_клиента

GROUP BY Клиенты.Фамилия

Выборка из 3 таблиц проводится аналогично.

Запрос: Вывести для каждого клиента суммарное количество покупок по видам товара

SELECT Клиенты.Фамилия, SUM (Продажи.Продано) AS Количество

FROM Клиенты, Продажи, Товар

WHERE (Клиенты.Код_клиента = Продажи.Код_клиента) and (Продажи.Код_товара=Товары.Код_товара)

GROUP BY Клиенты.Фамилия

Оператор INSERT

INSERT INTO <имя_таблицы>

(field1, … fieldN)

VALUES (значение1,….значениеN)

Пример:

Insert into Tovar

(Код, Товар, Цена, Производитель)

Values (5, 'Шкаф', 8700, 'КБК')

Поле Страна не указано в запросе и получит значение по умолчанию (здесь NULL).

Оператор DELETE

DELETE FROM <название_таблицы>

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

Пример:

• удаление всех записей таблицы:

DELETE FROM Tovar

- удаление записей с ценой < 3000:

DELETE FROM Tovar

WHERE Цена < 3000

Оператор UPDATE

UPDATE <название_таблицы>

SET <список_присвоений>

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

Пример: увеличить цену товара в 1.5 раза

Update Tovar

Set Цена=Цена*1.5

Пример: Установить название страны там, где она не указана.

Update Tovar Set Страна = 'США'

Where Страна is null

Использование SQL для сортировки

procedure TForm1.Button1Click(Sender : TObject);

Var s : string;

begin

Query1.Close;

Query1.SQL.Clear;

Query1.SQL.Add('Select * from stu.db');

case RadioGroup2.ItemIndex of

0: s := '';

1: s := 'DESC';

end;

Case radioGroup1.ItemIndex of

0: s := 'Order by Fio ' + s;

1: s := 'Order by Gr ' + s;

2: s := 'Order by Stip ' + s;

3: s := 'Order by Gru ' + s;

4: s := '';

end;

Query1.SQL.Add(s);

Query1.Open; end;

Использование SQL для группировки

procedure TForm1.Button2Click(Sender: TObject);

var s:string;

begin

Query1.Close;

Query1.SQL.Clear;

Query1.SQL.Add('Select * from stu.db');

Case RadioGroup3.ItemIndex of

0: s := 'Order by Gr ';

1: s := 'Order by Stip ';

2: s := 'Order by Gru ';

3: s := '';

end;

Query1.SQL.Add(s);

Query1.Open;

end;

Запросы к базе данных «Компьютерная фирма»:

Product (maker, model, type) – производитель, номер модели, тип (PC – ПК, Laptop – ноутбук, Printer- принтер).

PC (code, model, speed, ram, hd, cd, price) – код, модель, скорость процессора (МГц), общий объем памяти (Мб), размер диска (Гб), скорость считывающего устройства (12х,…).

Laptop (code, model, speed, ram, hd, screen, price) – аналогично РС, screen – размер экрана.

Printer (code, model, color, type, price)

Задачи по БД «Компьютерная фирма»

Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее $500. Вывести model, speed и hd

Select model, speed, hd from pc

where price < 500

Найдите производителей принтеров. Вывести maker.

Select distinct maker from product

where type = 'printer'

Найдите номер модели, объем памяти и размеры экранов портативных компьютеров, цена которых превышает $1000.

Select model, ram, screen from laptop

where price > 1000

Найдите все записи таблицы Printer для цветных принтеров.

Select * from printer

where color = 'y'

Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12х или 24х CD-приводы стоимостью менее $600.

Select model, speed, hd From PC

Where cd in ('12x', '24x') and price < 600

Для каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену компьютера с такой же скоростью. Вывести: speed, средняя цена.

Select speed, avg (price) FROM PC

Where speed >600

Group by speed

Найдите производителей ПК с процессором не менее 450 МГц.

Select distinct maker From Product, PC

Where Product.model=PC.model and speed>=450

Для каждого производителя найти средний размер экрана выпускаемого портативного компьютера. Вывести maker,средний размер экрана.

Select maker, avg(screen) From Laptop, Product

Where Product.model=Laptop.model

Group By maker

Найдите среднюю скорость ПК.

Select avg(speed) from pc

Найдите среднюю скорость портативных компьютеров дороже $1000.

Select avg(speed) from laptop

where price > 1000