лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас

• Название: Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
• Анкор: лекция
• Дата: 21.12.2022
• Размер: 7.88 Mb.
• Имя файла: Moiseenko_SQL.pdf
• Тип: Учебник; #857633
• страница: 14 из 47

часть III
). Следует отметить, что практически все рассматриваемые здесь задачи, составляющие первый обучающий этап тестирования на сайте, можно решить, пользуясь исключительно стандартными средствами. Это могут подтвердить многочисленные посетители сайта, которые в своей профессиональной деятельности используют различные СУБД от FoxPro до Oracle.
Описание синтаксиса операторов языка сопровождается многочисленными примерами запросов, которые адресуются к учебным базам данных, краткое описание которых приводится в
Приложении*1
. Кроме того, многие параграфы сопровождаются перечнем упражнений, рекомендуемых к решению для закрепления соответствующего материала. Формулировки всех заданий, которые составляют первый этап тестирования на сайте и которые выборочно разбираются в книге, приведены в
Приложении*2
Простой
оператор
SELECT
Оператор SELECT осуществляет выборку из базы данных и имеет наиболее сложную структуру среди всех операторов языка SQL. Практически любой пользователь баз данных в состоянии написать простейший оператор SELECT типа
1.
SELECT
*
FROM
PC;

который осуществляет выборку всех записей из объекта БД табличного типа с именем РС. При этом столбцы и строки результирующего набора не упорядочены. Чтобы упорядочить поля результирующего набора, их следует перечислить через запятую в нужном порядке после слова SELECT:
1.
SELECT
price, speed, hd, ram, cd, model, code
2.
FROM
PC;
Ниже приводится результат выполнения этого запроса.
price
speed
hd
ram
Cd
model
code
600
500 5
64 12x
1232 1
850
750 14 128 40x
1121 2
600
500 5
64 12x
1233 3
850
600 14 128 40x
1121 4
850
600 8
128 40x
1121 5
950
750 20 128 50x
1233 6
400
500 10 32 12x
1232 7
350
450 8
64 24x
1232 8
350
450 10 32 24x
1232 9
350
500 10 32 12x
1260 10
980
900 40 128 40x
1233 11
Вертикальную проекцию таблицы РС можно получить, если перечислить только необходимые поля. Например, чтобы получить информацию только о частоте процессора и объеме оперативной памяти компьютеров, следует выполнить запрос:

1.
SELECT
speed, ram
2.
FROM
PC; который вернет следующие данные:
speed
ram
500
64
750
128
500
64
600
128
600
128
750
128
500
32
450
64
450
32
500
32
900
128
Следует отметить, что вертикальная выборка может содержать дубликаты строк в том случае, если она не содержит потенциального ключа, однозначно определяющего запись. В таблице РС потенциальным ключом является поле code. Поскольку это поле отсутствует в запросе, в приведенном выше результирующем наборе имеются дубликаты строк (например, строки 1 и 3).

Если требуется получить только уникальные строки (скажем, нас интересуют только различные комбинации скорости процессора и объема памяти, а не характеристики всех имеющихся компьютеров), то можно использовать ключевое слово DISTINCT:
1.
SELECT
DISTINCT
speed, ram
2.
FROM
PC; что даст следующий результат:
speed
ram
450
32
450
64
500
32
500
64
600
128
750
128
900
128
Помимо DISTINCT может применяться также ключевое слово ALL (все строки), которое принимается по умолчанию.
Чтобы упорядочить строки результирующего набора, можно выполнить сортировку по любому количеству полей, указанных в предложении SELECT. Для этого используется предложение ORDER
BY список полей, являющееся всегда последним предложением в операторе SELECT. При этом в списке полей могут указываться как имена полей, так и их порядковые позиции в списке предложения SELECT. Так,

если требуется упорядочить результирующий набор по объему оперативной памяти в порядке убывания, можно записать:
1.
SELECT
DISTINCT
speed, ram
2.
FROM
PC
3.
ORDER
BY
ram
DESC
; или
1.
SELECT
DISTINCT
speed, ram
2.
FROM
PC
3.
ORDER
BY
2
DESC
;
Результат, приведенный ниже, будет, естественно, одним и тем же.
speed
ram
600
128
750
128
900
128
450
64
500
64
450
32
500
32
Сортировку можно проводить по возрастанию (параметр ASC принимается по умолчанию) или по убыванию (параметр DESC).
Примечание:

Не рекомендуется в приложениях использовать запросы с сортировкой
по номерам столбцов. Это связано с тем, что со временем структура
таблицы может измениться, например, в результате добавления/удаления
столбцов. Как следствие, запрос типа
1.
SELECT
*
2.
FROM
PC
3.
ORDER
BY
3
; может давать совсем другую последовательность или вообще вызывать ошибку, ссылаясь на отсутствующий столбец.
Сортировка по двум полям
1.
SELECT
DISTINCT
speed, ram
2.
FROM
PC
3.
ORDER
BY
ram
DESC
, speed
DESC
; даст следующий результат:
speed
ram
900
128
750
128
600
128
500
64
450
64
500
32

450
32
Горизонтальную выборку реализует предложение WHERE предикат, которое записывается после предложения FROM.
При этом в результирующий набор попадут только те строки из источника записей, для каждой из которых значение предиката равно TRUE. То есть предикат проверяется для каждой записи. Например, запрос «получить информацию о частоте процессора и объеме оперативной памяти для компьютеров с ценой ниже $500» можно сформулировать следующим образом:
1.
SELECT
DISTINCT
speed, ram
2.
FROM
PC
3.
WHERE
price <
500 4.
ORDER
BY
2
DESC
;
speed
Ram
450
64
450
32
500
32
В последнем запросе был применен предикат сравнения с использованием операции сравнения «<» (меньше чем). Кроме этой операции сравнения могут использоваться: «=» (равно), «>» (больше), «>=» (больше или равно), «<=»
(меньше или равно) и «<>» (не равно). Выражения в предикатах сравнения могут содержать константы и любые поля из таблиц, указанных в предложении FROM. Символьные строки и константы типа дата/время записываются в апострофах.
Примеры простых предикатов сравнения:

предикат
описание
price < 1000
Цена меньше 1000
type = ‘laptop’
Типом продукции является портативный компьютер
cd = ‘24x’
24-скоростной CD-ROM
color <> ’y’
Не цветной принтер
ram – 128 > 0
Объем оперативной памяти свыше 128 Мбайт
Price <= speed*2
Цена не превышает удвоенной частоты процессора
Сортировку можно выполнять даже по столбцам, отсутствующим в списке SELECT. Естественно, эти столбцы должны присутствовать на выходе предложения FROM. Например, чтобы вывести список моделей PC, упорядоченный по убыванию цены, можно написать
1.
SELECT
model
FROM
PC
2.
ORDER
BY
price
DESC
;
Обратите внимание, что сама цена (price) не выводится запросом.
Исключением является неоднозначная ситуация, возникающая при исключении дубликатов. Так запрос
1.
SELECT
DISTINCT
model
FROM
PC
2.
ORDER
BY
price
DESC
; уже вызовет ошибку:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
(Элементы ORDER BY должны входить в список выбора, если указывается
SELECT DISTINCT.)

По той же причине не будет работать запрос с группировкой
1.
SELECT
model
FROM
PC
2.
GROUP
BY
model
3.
ORDER
BY
price
DESC
;
Column "PC.price" is invalid in the ORDER BY clause because it is not contained
in either an aggregate function or the GROUP BY clause.
(Столбец "PC.price" недопустим в предложении ORDER BY, так как он не содержится в агрегатной функции или предложении GROUP BY.)
Однако если неоднозначность устранить (выполнить сортировку по какому- либо агрегатному значению для группы), то можно "подправить" запрос:
1.
SELECT
model
FROM
PC
2.
GROUP
BY
model
3.
ORDER
BY
MAX
(
price
)
DESC
;
Примечание:
Все приведенные здесь запросы (в том числе ошибочные) будут
работать под MySQL, которая сама устраняет неоднозначность.
Спросите как? Загляните в документацию MySQL. :-)
Сортировка по
дням
рождения
Такая сортировка может потребоваться, например, для установления очередности празднования дней рождения сотрудников. Особенность подобной сортировки заключается в том, что год не учитывается вовсе, а даты упорядочиваются сначала по месяцу, а потом по дню месяца.
Возьмём в качестве примера таблицу Battles, а именно, столбец date. Ясно, что сортировка просто

по дате (date) не даст желаемого результата из-за года
(так, например,
15-е ноября будет предшествовать 20-му октября):
1.
SELECT
date
2.
FROM
Battles
3.
ORDER
BY
date;
date
1941-05-25 00:00:00.000
1942-11-15 00:00:00.000
1943-12-26 00:00:00.000
1944-10-25 00:00:00.000
1962-10-20 00:00:00.000
1962-10-25 00:00:00.000
Для выполнения требуемой сортировки можно предложить два варианта
(
SQL
Server).
1. Использование функции
CONVERT
При этом способе мы преобразуем дату к текстовому представлению в формате "mm-dd"
1.
SELECT
CONVERT
(
CHAR
(
5
)
, date,
110
)
"mm-dd"
2.
FROM
Battles; по которому и выполним сортировку:

1.
SELECT
date
2.
FROM
Battles
3.
ORDER
BY
CONVERT
(
CHAR
(
5
)
,date,
110
)
;
date
1941-05-25 00:00:00.000
1962-10-20 00:00:00.000
1962-10-25 00:00:00.000
1944-10-25 00:00:00.000
1942-11-15 00:00:00.000
1943-12-26 00:00:00.000
2. Использование функций
MONTH и DAY
Здесь мы используем встроенные функции, которые возвращают компоненты даты - месяц (MONTH) и день (DAY) соответственно. По этим компонентам выполним сортировку:
1.
SELECT
date
2.
FROM
Battles
3.
ORDER
BY
MONTH
(
date
)
, DAY
(
date
)
;
Что касается производительности, то вы можете выбрать любой вариант, т.к. оптимизатор строит для них идентичные планы.
В заключение представим последний запрос в более наглядном виде, добавив в него еще и "виновника торжества":
1.
SELECT
DAY
(
date
)
BD_day, DATENAME
(
mm, date
)
BD_month, name

2.
FROM
Battles
3.
ORDER
BY
MONTH
(
date
)
, DAY
(
date
)
;
Предикаты I
Предикаты представляют собой выражения, принимающие истинностное значение. Они могут представлять собой как одно выражение, так и любую комбинацию из неограниченного количества выражений, построенную с помощью булевых операторов AND, OR или NOT. Кроме того, в этих комбинациях может использоваться SQL- оператор IS, а также круглые скобки для конкретизации порядка выполнения операций
Предикат в языке
SQL
может принимать одно из трех значений TRUE (истина), FALSE (ложь) или UNKNOWN (неизвестно).
Исключение составляют следующие предикаты: IS
NULL (отсутствие значения), EXISTS (существование), UNIQUE (
уникальность) и MATCH (совпадение), которые не могут принимать значение UNKNOWN.
Правила комбинирования всех трех истинностных значений легче запомнить, обозначив TRUE как
1, FALSE как
0 и UNKNOWN как 1/2 (где-то между истинным и ложным значениями) [
2
].
AND с двумя истинностными значениями дает минимум этих значений. Например, TRUE AND
UNKNOWN будет равно UNKNOWN.
OR с двумя истинностными значениями дает максимум этих значений. Например, FALSE OR
UNKNOWN будет равно UNKNOWN.
Отрицание истинностного значения равно 1 минус данное истинностное значение.

Например, NOT UNKNOWN будет равно UNKNOWN.
Логические операторы при отсутствии скобок, как и арифметические операторы, выполняются в соответствии с их старшинством.
Одноместная операция NOT имеет наивысший приоритет. В этом легко убедиться, если выполнить следующие два запроса.
1.
-- модели, не являющиеся ПК
2.
-- второй предикат ничего не меняет, т.к. он добавляет
условие,
3.
-- уже учтенное в первом предикате
4.
SELECT
maker, model, type
5.
FROM
Product
6.
WHERE
NOT
type=
'PC'
OR
type=
'Printer'
;
1.
-- модели производителя A, которые не являются ПК
2.
SELECT
maker, model, type
3.
FROM
Product
4.
WHERE
NOT
type=
'PC'
AND
maker=
'A'
;
Поменять порядок выполнения логических операторов можно при помощи скобок:
1.
-- модели, не являющиеся ПК или принтером, т.е. модели
ноутбуков в нашем случае
2.
SELECT
maker, model, type
3.
FROM
Product
4.
WHERE
NOT
(
type=
'PC'
OR
type=
'Printer'
)
;
1.
-- модели, которые не являются ПК, выпускаемыми
производителем A
2.
SELECT
maker, model, type

3.
FROM
Product
4.
WHERE
NOT
(
type=
'PC'
AND
maker=
'A'
)
;
Следующий приоритет имеет оператор AND. Сравните результаты следующих запросов.
1.
-- модели ПК, выпускаемые производителем A, и любые модели
производителя B
2.
SELECT
maker, model, type
3.
FROM
Product
4.
WHERE
type=
'PC'
AND
maker=
'A'
OR
maker=
'B'
;
1.
-- модели ПК, выпускаемые производителем A или
производителем B
2.
SELECT
maker, model, type
3.
FROM
Product
4.
WHERE
type=
'PC'
AND
(
maker=
'A'
OR
maker=
'B'
)
;
Примечание:
Если вы не уверены, что точно помните порядок выполнения логических
операторов, ставьте скобки.
Предикат в предложении WHERE выполняет реляционную операцию ограничения, т.е. строки, появляющиеся на выходе предложения FROM ограничиваются теми, для которых предикат дает значение TRUE.
Если cond1 и cond2 являются простыми условиями, то ограничение по предикату
cond1 AND cond2 эквивалентно пересечению ограничений по каждому из предикатов.
Ограничение по предикату
cond1 OR cond2

эквивалентно объединению ограничений по каждому из предикатов, а ограничение по предикату
NOT cond1 эквивалентно взятию разности
, когда от исходного отношения вычитается ограничение по предикату cond1.
Обратимся к примерам.
Получить информацию о моделях ПК производителя A.
Здесь cond1: maker = 'A' , cond2: type = 'pc'.
cond1 AND cond2
1.
SELECT
*
FROM
product
2.
WHERE
maker =
'A'
AND
type =
'pc'
;
Пересечение
1.
SELECT
*
FROM
product
2.
WHERE
maker =
'A'
3.
INTERSECT
4.
SELECT
*
FROM
product
5.
WHERE
type =
'pc'
;
Получить информацию о моделях производителей A и B.
Здесь cond1: maker = 'A' , cond2: maker = 'B'.
cond1 OR cond2

1.
SELECT
*
FROM
product
2.
WHERE
maker =
'A'
OR
maker =
'B'
;
Объединение
1.
SELECT
*
FROM
product
2.
WHERE
maker =
'A'
3.
UNION
4.
SELECT
*
FROM
product
5.
WHERE
maker =
'B'
;
В свою очередь, условия condX могут не быть простыми. Например,
Получить информацию о моделях ПК производителей A и B.
Решение
1.
SELECT
*
FROM
product
2.
WHERE
(
maker =
'A'
OR
maker =
'B'
)
AND
type =
'pc'
; можно выразить через пересечение
1.
SELECT
*
FROM
product
2.
WHERE
maker =
'A'
OR
maker =
'B'
3.
INTERSECT
4.
SELECT
*
FROM
product
5.
WHERE
type =
'pc'
; а его эквивалентную форму
1.
SELECT
*
FROM
product
2.
WHERE
(
maker =
'A'
AND
type =
'pc'
)

3.
OR
(
maker =
'B'
AND
type =
'pc'
)
; через объединение
1.
SELECT
*
FROM
product
2.
WHERE
maker =
'A'
AND
type =
'pc'
3.
UNION
4.
SELECT
*
FROM
product
5.
WHERE
maker =
'B'
AND
type =
'pc'
;
Найти модели, которые не являются ПК
Здесь cond1: type = 'pc'
NOT cond1
1.
SELECT
*
FROM
product
2.
WHERE
NOT
type =
'pc'
;
Разность
1.
SELECT
*
FROM
product
2.
EXCEPT
3.
SELECT
*
FROM
product
WHERE
type =
'pc'
;
Несколько слов о производительности
Если на столбцах, по которым выполняется ограничение нет индексов, при выполнении запроса будет выполнено сканирование таблицы. В первых вариантах решений такое сканирование будет выполнено один раз, в то время как в решениях на основе объединения, пересечения и разности запросов таблица сканируется дважды, плюс будет выполнена операция, сравнивающая наборы строк, возвращаемые каждым из запросов (например, Nested Loops).
Это делает запрос менее производительным, хотя, возможно, существуют

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