Select желательные атрибуты from переменные-кортежи



Скачать 71.15 Kb.
Дата22.04.2016
Размер71.15 Kb.
SQL запросы.


  • Общий вид:

SELECT желательные атрибуты

FROM переменные-кортежи

WHERE условие на переменные-кортежи ;
В примерах будут использоваться соотношения со следующей структурой:
Beers(name, manf)

Bars(name, addr, license)

Drinkers(name, addr, phone)

Likes(drinker, beer)

Sells(bar, beer, price)

Frequents(drinker, bar)



Пример.
Какой сорт пива производится Anheuser-Busch?
Beers(name, manf)
SELECT name

FROM Beers

WHERE manf = 'Anheuser-Busch';


  • Замечание: строка заключается в одиночные кавычки.

Name



Bud


Bud Lite

Michelob
Формальная семантика SQL однотабличного запроса.




  1. Начать с отношения в опции FROM.

  2. Применить , используя условия в опции WHERE (получая в результате набор).

  3. Применить (расширенный оператор) , используя условия в опции SELECT (получая в результате набор) .



Эквивалентная операционная семантика.
Пусть есть переменная-кортеж для обозначения всех кортежей отношения. Для каждого кортежа:

  • Проверить выполняются ли условия в опции WHERE.

  • Если да, то вывести значения условий в SELECT.


«Звездочка» для задания всех атрибутов.

Beers(name, manf)


SELECT *

FROM Beers

WHERE manf = 'Anheuser-Busch';
name manf



Bud Anheuser-Busch


Bud Lite Anheuser-Busch

Michelob Anheuser-Busch




Переименование столбцов.
Beers(name, manf)
SELECT name AS beer

FROM Beers

WHERE manf = 'Anheuser-Busch';
Beer


Bud


Bud Lite

Michelob



Выражение в качестве значения столбца.

Sells(bar, beer, price)


SELECT bar, beer,

price*120 AS priceInYen

FROM Sells;
bar beer priceInYen


Joe's Bud 300

Sue's Miller 360





  • Маленькая хитрость: если хотите получить результат с одинаковой символьной строкой в качестве значений строк отношения, то используйте эту строку как константу в выражении:

Likes(drinker, beer)


SELECT drinker,

'likes Bud' AS whoLikesBud

FROM Likes

WHERE beer = 'Bud';


drinker whoLikesBud


Sally likes Bud

Fred likes Bud

Пример. Найти цену в Joe's Bar за сорт Bud.
Sells(bar, beer, price)
SELECT price

FROM Sells


WHERE bar = 'Joe''s Bar' AND beer = 'Bud';





  • Замечание: двумя одиночными кавычками в символьной строке обозначается одна кавычка.

  • В условии опции WHERE могут использоваться логические операторы AND, OR, NOT и скобки для изменения порядка.

  • Запомните: SQL не является чувствительным к регистру. Ключевые слова, например SELECT или AND, могут записываться как заглавными, так и строчными буквами.

  • Внутри кавычек строка размещается с учетом регистра.


Шаблоны.


  • % замешает любую строку.

  • _ замещает один символ строки.

  • «Атрибут LIKE шаблон" – это условие, которое является истинным, если строковое значение атрибута соответствует шаблону.

  • NOT LIKE используется для отрицания.



Пример.
Найти посетителей, чьи номера телефонов содержат 555 в качестве первых трех цифр городского номера.
Drinkers(name, addr, phone)
SELECT name

FROM Drinkers

WHERE phone LIKE '%555-_ _ _ _ ';


  • Замечание: шаблоны заключаются в кавычки, как строки.



Пустые указатели (NULL).
Вместо значения в компоненте кортежа.

  • Интерпретация - не точно «отсутствие значения».

  • Есть много причин, по которым допускается отсутствие значения. Например, «недопустимое значение».



Сравнение пустых указателей (NULL) и значений.


Пример.
bar beer price


Joe's bar Bud NULL


SELECT bar

FROM Sells

WHERE price < 2.00 OR price >= 2.00;
UNKNOWN OR UNKNOWN = UNKNOWN


  • Joe's Bar не будет включен в результат, даже если WHERE-условие - тавтология.



3-значная логика.
Если истина (TRUE) = 1, ложь (FALSE) = 0 и неизвестное (UNKNOWN) = 1/2.

То:


  • AND = min.

  • OR = max.

  • NOT (x) = 1-x.



Нарушение некоторых законов.
Пример: Закон исключения третьего, то есть,

P OR NOT p = TRUE



  • Для 3-значной логики: если p = unknown, то левая сторона = max (1/2, (1-1/2)) = 1/2  1.

  • Подобно алгебре множеств, нет никакого известного способа, чтобы привести все законы 3-значной логики в соответствие законам для наборов или 2-значной логики, соответственно.


Запросы к нескольким отношениям.


  • Список отношений в опции FROM.

  • Отношение-точка-атрибут – форма обращения к одинаковым атрибутам нескольких отношений.


Пример. Найти сорта пива, которые предпочитают частые посетители бара Joe's Bar.
Likes(drinker, beer)

Frequents(drinker, bar)


SELECT beer


FROM Frequents, Likes

WHERE bar = 'Joe''s Bar'

AND Frequents.drinker = Likes.drinker;

Общая семантика запросов к нескольким отношениям.
Та же самая, что и для запроса с одним отношением, но в начале выполняется операция произведение (product) для всех отношений, указанных в опции FROM.

Операционная семантика.
Рассмотрим переменную-кортеж для каждого отношения, указанного в опции FROM.


  • Представим, что эти переменные указывают на кортежи в соответствующих отношениях (типа вложенного цикла).

  • Если текущие значения переменных-кортежей обращают WHERE-условие в истину, то в результат выводятся значения аттрибутов из SELECT.

drinker bar drinker beer


Sally Joe’s

Sally







l f
Likes


Frequents

Явное использование переменных-кортежей.
Иногда необходимо обратиться к двум или более копиям отношения.

  • Переменные-кортежи используются как псевдонимы отношений.



Пример. Найти пары сортов пива, изготовленные тем же самым производителем.
Beers(name, manf)

SELECT b1.name, b2.name


FROM Beers b1, Beers b2

WHERE b1.manf = b2.manf AND b1.name < b2.name;




  • SQL допускает использование опции AS между отношением и переменной-кортежем, Oracle - нет.

  • Обратите внимание, что условие b1.name < b2.name необходимо, чтобы избежать создания пар, подобных (Bud, Bud) и одной и той же пары в различном порядке (Bud, Michelob) и (Michelob, Bud).



Подзапросы.
Результат запроса «SELECT-FROM-WHERE» может использоваться в опции WHERE другого запроса.
Самый простой случай: Подзапрос возвращает один кортеж.

Найти бары, которые продают сорт 'Miller' по той же цене, по которой бар Joe's Bar продает сорт ‘Bud’.


Sells(bar, beer, price)
SELECT bar

FROM Sells

WHERE beer = 'Miller'

AND price = ( SELECT price

FROM Sells

WHERE bar = 'Joe''s Bar'

AND beer = 'Bud'


);


  • Обратите внимание на правило области видимости данных: атрибут относится к наиболее близкому вложенному отношению с этим атрибутом.

  • Круглые скобки вокруг подзапроса обязательны.



Оператор IN.
«Кортеж IN отношение» - истинно, если кортеж присутствует в отношении.
Пример. Найти названия и изготовителей сортов, которые предпочитает Fred.
Beers(name, manf)

Likes(drinker, beer)


SELECT *

FROM Beers

WHERE name IN ( SELECT beer

FROM Likes

WHERE drinker = 'Fred'

);


  • Возможно также использование NOT IN.



EXISTS.
«EXISTS(отношение)» - истинно, если отношение не пусто.

Пример. Найти сорта пива, которые являются уникальными для их производителей.

Beers(name, manf)



SELECT name


FROM Beers b1

WHERE NOT EXISTS ( SELECT *

FROM Beers

WHERE manf = b1.manf

AND name <> b1.name

);


  • Правило области видимости данных: чтобы сослаться к внешнему отношению Beers во вложенном подзапросе, необходимо использовать переменную-кортеж для внешнего отношения, например b1, как в этом примере.

  • Подзапрос, который использует значения из внешнего запроса, называется коррелируемым (зависимым) подзапросом.


Кванторы.
ANY и ALL ведут себя, как квантор существования и квантор всеобщности, соответственно.
Предупреждение: в обычном языке, «any» и «all» кажутся синонимами, например, «I am fatter than any of you" и «I am fatter than all of you." Но в SQL это не так:
Пример. Найти сорт (сорта) пива, имеющие самую высокую цену.
Sells(bar, beer, price)
SELECT beer

FROM Sells



WHERE price >= ALL ( SELECT price

FROM Sells


);

Задание в классе:



Найти сорт (сорта) пива, не имеющие самую низкую цену.


База данных защищена авторским правом ©bezogr.ru 2016
обратиться к администрации

    Главная страница