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

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

query() возвращает фрагмент XML документа, содержащий всё между начальным и конечным тегами элемента "label", включая и сам элемент.
1.
SELECT
name, xmlData.query
(
'/albums/album[2]/labels/label[1]'
)
AS
SecondAlbumLabel
2.
FROM
dbo.tArtist;
name
SecondAlbumLabel
Radiohead
Parlophone
Guns N’ Roses Geffen Records
Рассмотрим другой пример. Нужно найти такие альбомы, в описании которых есть слово "record".

1.
SELECT
name,
2. xmlData.query
(
'/albums/album[description[contains(.,
"record")]]'
)
AS
ContainsRecord
3.
FROM
dbo.tArtist;
Если детально разобрать выражение XPath, то можно его описать следующей фразой: найти в списке альбомов (albums) такие альбомы (album), у которых имеется описание (description), содержащее слово "record"
(contains(., "record")). Точка в функции contains() означает обращение к содержимому текущего элемента, в данном случае это элемент description.
name
ContainsRecord
Radiohead
Guns N’ Roses ...
Видим, что такой альбом нашёлся только у группы Guns N' Roses. Но у группы Radiohead в описании альбома OK Computer встречается слово "Records". Этот альбом не нашёлся, т.к. XQuery чувствителен к регистру.
Чтобы данный альбом тоже был найден, используем функцию lower-case(), приводящую обрабатываемый текст к нижнему регистру.
1.
SELECT
name,
2. xmlData.query
(
'/albums/album[description[contains(lower-case(.),
"record")]]'
)
3.
AS
ContainsRecord
4.
FROM
dbo.tArtist;
name
ContainsRecord
Radiohead
Guns N’ Roses ...

Метод value()
Метод value() позволяет извлекать из XML документа содержимое единичного элемента или атрибута с указанием его типа данных.
Если выражение XPath указывает на несколько узлов, то будет возвращено сообщение об ошибке. Например,
1.
SELECT
name,
2. xmlData.value
(
'/albums/album[2]/labels/label[1]/text()'
,
'varchar(100)'
)
3.
AS
SecondAlbumLabel
4.
FROM
dbo.tArtist;
XQuery [dbo.tArtist.xmlData.value()]: 'value()' requires a singleton (or empty
sequence), found operand of type 'xdt:untypedAtomic *'
Эта ошибка сообщает, что данное XPath-выражение может вернуть более одной сущности. Например, если бы в исходном XML документе было несколько элементов "albums" или более одного элемента "labels".
Чтобы исправить эту ошибку, замените выражение XPath на одно из следующих:

'/albums[1]/album[2]/labels[1]/label[1]/text()[1]'

'(/albums/album[2]/labels/label/text())[1]'
Результат будет одинаковым:
name
SecondAlbumLabel
Radiohead
Parlophone
Guns N’ Roses Geffen Records
Ещё один пример с типом данных "time". Допустим, мы хотим получить для каждого исполнителя название первого альбома, первой песни из этого альбома и её длительность.

1.
SELECT
name
2.
, xmlData.value
(
'/albums[1]/album[1]/@title'
,
'varchar(100)'
)
AS
FirstAlbum
3.
, xmlData.value
(
'/albums[1]/album[1]/song[1]/@title'
,
'varchar(100)'
)
AS
FirstSongTitle
4.
, xmlData.value
(
'/albums[1]/album[1]/song[1]/@length'
,
'time(0)'
)
AS
FirstSongLength
5.
FROM
dbo.tArtist;
name
FirstAlbum
FirstSongTitle
FirstSongLength
Radiohead
The King of Limbs
Bloom
05:15:00
Guns N' Roses
Use Your Illusion I
Right Next Door to Hell
03:02:00
К сожалению, поле "FirstSongLength" распозналось в формате чч:мм:сс, тогда как мы уверены, что в XML-документе время записано в формате мм:сс.
Чтобы исправить эту ошибку, можно использовать обычный синтаксис T-SQL, а можно воспользоваться XQuery:
1.
SELECT
name
AS
artist
2.
, xmlData.value
(
'/albums[1]/album[1]/@title'
,
'varchar(100)'
)
AS
FirstAlbum
3. , xmlData.value
(
'/albums[1]/album[1]/song[1]/@title'
,
'varchar(100)'
)
AS
FirstSongTitle
4.
, xmlData.value
(
'concat("00:",
/albums[1]/album[1]/song[1]/@length)'
,
'time(0)'
)
5.
AS
FirstSongLength
6.
FROM
dbo.tArtist;
artist
FirstAlbum
FirstSongTitle
FirstSongLength
Radiohead
The King of Limbs
Bloom
00:05:15
Guns N' Roses
Use Your Illusion I
Right Next Door to Hell
00:03:02

Метод exist()
Данный метод не используется для получения каких-либо данных из XML документа. Он используется только для того, чтобы убедиться, что в нём присутствуют требуемые данные. Метод exist() проверяет существование указанного выражения XPath.
В данном примере метод exist() будет использован для определения, есть ли определённая песня в списке композиций. Пусть песня, которую мы ищем, называется "Garden of Eden".
1.
SELECT
name, xmlData.exist
(
'/albums[1]/album/song[@title="Garden of
Eden"]'
)
AS
SongExists
2.
FROM
dbo.tArtist;
name
SongExists
Radiohead
0
Guns N' Roses 1
Как можно видеть, песня с таким названием присутствует среди композиций группы Guns N' Roses (1 эквивалентно 'True'), и отсутствует среди композиций группы Radiohead (0 эквивалентно 'False').
Теперь проверим наличие песни длительностью более 10 минут.
1.
SELECT
name
2. ,xmlData.exist
(
'/albums[1]/album/song[@length>"10:00
"]'
)
AS
LongSongExists
3.
FROM
dbo.tArtist;
name
SongExists
Radiohead
1
Guns N' Roses 1

Из полученного результата мы видим, что оба исполнителя имеют в своём репертуаре как минимум одну песню длительностью более 10 минут. Но это не так. На самом деле все песни группы Radiohead длятся менее 10 минут.
Проблема заключается в том, что значение атрибута @length по умолчанию принимает строковый тип данных. Получается, что мы сравнивали строки, а не время.
Чтобы решить эту проблему, мы можем преобразовать значение атрибута
@length и значение, с которым оно сравнивается, к типу данных time. Оба они должны быть приведены к формату "чч:мм:сс" до преобразования типов данных. Для этого будем использовать функцию xs:time(), принимающую на вход строку и возвращающую время.
1.
SELECT
name
2. , xmlData.exist
(
'
3.
/albums[1]/album/song/@length[
4.
(
5. if (string-length(.)
= 4)
6. then xs:time(concat("00:0", .))
7. else xs:time(concat("00:", .))
8.
)
9.
> xs:time("00:10:00")
10.
]'
)
AS
LongSongExists
11.
FROM
dbo.tArtist;
Теперь мы видим, что в репертуаре группы Radiohead нет ни одной песни длительностью более 10 минут, в то время как в репертуаре группы Guns N'
Roses есть как минимум одна такая песня.
name
SongExists
Radiohead
0
Guns N' Roses 1

В данном примере функция time() используется в пространстве имён xs. Все встроенные функции преобразования типов должны использоваться в этом пространстве имён (xs:string, xs:boolean, xs:decimal, xs:float, xs:double, xs:dateTime, xs:time, xs:date и т.д.). Прочие встроенные функции используются в пространстве имён fn, но его указание не обязательно. То есть string-length(.) и fn:string-length(.) эквивалентны.
Метод modify()
Метод modify() позволяет изменять значения непосредственно в XML- документе.
Так же, как и другие методы, он требует выражение XPath, чтобы понять, какое значение нужно изменить. Однако, в отличие от других методов, modify() работает с оператором UPDATE (и не работает с оператором
SELECT). Также, modify() может работать только с одним значением за раз. В математике и программировании это называется одноэлементным множеством.
Так как не существует никакого ограничения на количество элементов, содержащихся внутри другого элемента, выражение XPath может возвращать много дочерних элементов. Например, если имеется такой XML документ:

Monday
Tuesday
Wednesday

выражение XPath /week/day возвращает три элемента, которые не являются одноэлементным множеством:
Monday
Tuesday
Wednesday

Однако, если заменить выражение XPath на (/week/day)[1], то будет возвращён только один элемент "Monday".
Monday
Попробуем получить название первого лейбла альбома "OK Computer" группы "Radiohead".
1.
SELECT
xmlData.query
(
'(/albums/album[@title="OK
Computer"]/labels/label/text())[1]'
)
2.
AS
FirstLabelText
3.
FROM
dbo.tArtist
4.
WHERE
name =
'Radiohead'
;
FirstLabelText
Parlophone
Метод modify() имеет единственной целью изменение значений в XML- документе, что является полезной возможностью. Представьте, что XML- документ уже импортирован на
SQL
Server, и обнаружилась опечатка или нужно обновить только одно значение. Чтобы не загружать заново уже исправленный XML-документ, можно просто использовать метод modify() и изменить необходимые значения непосредственно в сохранённом XML- документе на SQL Server.
В следующем примере мы поменяем местами названия лейблов альбома "OK
Computer" группы "Radiohead". Первый лейбл должен быть заменён на "Capitol", а второй – на "Parlophone".
Метод modify() может быть использован в предложении SET оператора
UPDATE по отношению к переменной или столбцу типа данных XML.
1.
UPDATE
dbo.tArtist
2.
SET
xmlData.
MODIFY
(
'replace value of
3.
(/albums/album[@title="OK
Computer"]/labels/label/text())[1] with "Capitol"'
)
4.
WHERE
name =
'Radiohead'
;
1.
UPDATE
dbo.tArtist

2.
SET
xmlData.
MODIFY
(
'replace value of
3.
(/albums/album[@title="OK
Computer"]/labels/label/text())[2] with "Parlophone"'
)
4.
WHERE
name =
'Radiohead'
;
Отлично – исправленный запрос отработал корректно, что подтверждается сообщениями:
(1 row(s) affected)
(1 row(s) affected)
Теперь вернёмся к исходному запросу (с использованием оператора
SELECT) и выполним его, чтобы проверить, что названия лейблов были обновлены корректно.
1.
SELECT
xmlData.query
(
'/albums/album[@title="OK
Computer"]/labels'
)
2.
FROM
dbo.tArtist
3.
WHERE
name =
'Radiohead'
;

Capitol
Parlophone

Эта задача может быть решена по-другому. Можно поменять местами элементы "label" без изменения их значений. Просто вставим копию первого лейбла в конец списка лейблов.
1.
UPDATE
dbo.tArtist
2.
SET
xmlData.
MODIFY
(
'insert
(/albums/album[@title="OK
Computer"]/labels/label)[1] as last
3. into (/albums/album[@title="OK Computer"]/labels)[1]'
)
4.
WHERE
name =
'Radiohead'
;

После чего удалим первый лейбл.
1.
UPDATE
dbo.tArtist
2.
SET
xmlData.
MODIFY
(
'delete
(/albums/album[@title="OK
Computer"]/labels/label)[1]'
)
3.
WHERE
name =
'Radiohead'
;
Чтобы удостовериться, что лейблы поменялись местами корректно, выполним следующий запрос:
1.
SELECT
xmlData.query
(
'/albums/album[@title="OK
Computer"]/labels'
)
2.
FROM
dbo.tArtist
3.
WHERE
name =
'Radiohead'
;

Parlophone
Capitol

Теперь попробуем отметить песню "Perfect Crime" из альбома "Use Your
Illusion I" группы Guns N' Roses, как популярную. Для этого добавим в XML- документ в соответствующий элемент атрибут isPopular со значением 1.
1.
UPDATE
dbo.tArtist
2.
SET
xmlData.
MODIFY
(
'
3. insert attribute isPopular { "1" }
4. into (/albums[1]/album[@title="Use Your Illusion
I"]/song[@title="Perfect Crime"])[1]
5.
'
)
6.
WHERE
name =
'Guns N'' Roses'
;

В качестве ещё одного примера добавим информацию о дате выпуска и дате записи песни "Estranged" из альбома "Use Your Illusion II" группы Guns N'
Roses. Обратите внимание, что в данном примере в элемент добавляется сразу два атрибута.
1.
UPDATE
dbo.tArtist
2.
SET
xmlData.
MODIFY
(
'
3. insert (
4. attribute Recorded { "1991" },
5. attribute Released { "1994-01-17" }
6.
)
7. into (/albums[1]/album[@title="Use Your Illusion
II"]/song[@title="Estranged"])[1]
8.
'
)
9.
WHERE
name =
'Guns N'' Roses'
;
Метод nodes()
Разбивает XML-структуру на одно или несколько поддеревьев, в соответствии с указанным выражением XPath.
Выполним следующий запрос:
1.
SELECT
name
AS
artist
2. , album.query
(
'.'
)
AS
album
3.
FROM
dbo.tArtist A
4.
CROSS
APPLY
A.xmlData.nodes
(
'/albums[1]/album'
)
col
(
album
)
;
Данный запрос разобьет исходную структуру на строки, по количеству элементов "album" и вернет по две строки для каждого исполнителя:
artist
album
Radiohead
S...

Radiohead
Parlop...
Guns N'
Roses
G...
Guns N'
Roses
G...
Разберем текст запроса подробнее.
CROSS APPLY A.xmlData.nodes('/albums[1]/album') – разбивает каждую строку таблицы на столько строк, сколько элементов "album" было найдено. col – это имя производной таблицы, а album – это имя столбца. Они нужны будут для дальнейшей работы с результатами. album.query('.') – здесь осуществляется запрос к каждой строке результатов, при помощи псевдонима. Данный подзапрос просто выбирает все данные из поддерева.
Разберём другой пример. Допустим мы хотим получить в виде таблицы первые две песни каждого альбома для каждой из групп.
1.
SELECT
name
AS
artist
2. , song.value
(
'../@title'
,
'varchar(50)'
)
AS
album
3. , song.value
(
'@title'
,
'varchar(100)'
)
AS
song
4.
FROM
dbo.tArtist A
5.
CROSS
APPLY
A.xmlData.nodes
(
'/albums[1]/album/song[position()<=2]'
)
c ol
(
song
)
;
artist
album
song
Radiohead
The King of Limbs
Bloom
Radiohead
The King of Limbs
Morning Mr Magpie

Radiohead
OK Computer
Airbag
Radiohead
OK Computer
Paranoid Android
Guns N' Roses Use Your Illusion I
Right Next Door to Hell
Guns N' Roses Use Your Illusion I
Dust N' Bones
Guns N' Roses Use Your Illusion II Civil War
Guns N' Roses Use Your Illusion II 14 Years
[position()<=2] – указывает, что нам нужны только два первых элемента "song" внутри каждого элемента "album".
'../@title' – обращается к родительскому элементу и берёт его атрибут @title.
Результат отобразился корректно, но при больших объёмах данных такой запрос будет крайне медленным и неэффективным. Причина заключается в том, что для каждой песни среда выполнения запроса ищет родительский элемент и считывает его атрибут. Перепишем запрос следующим образом:
1.
SELECT
name
AS
artist
2. , album.value
(
'@title'
,
'varchar(50)'
)
AS
album
3. , song.value
(
'@title'
,
'varchar(100)'
)
AS
song
4.
FROM
dbo.tArtist A
5.
CROSS
APPLY
A.xmlData.nodes
(
'/albums[1]/album'
)
c1
(
album
)
6.
CROSS
APPLY
c1.album.nodes
(
'song[position()<=2]'
)
c2
(
song
)
;
Результат будет аналогичным, однако теперь логика запроса изменена следующим образом: сначала выбираются все альбомы, а потом для каждого альбома будут присоединяться песни.
Поскольку песен всегда будет намного больше, чем альбомов, выполнение данного запроса покажет существенный прирост производительности по сравнению с предыдущим.

Язык
определения
данных (SQL
DDL)
Язык определения данных (Data Definition Language) предназначен для создания, изменения и удаления объектов базы данных. Основными объектами реляционной базы данных являются таблицы. С них и начнем.
Таблицы бывают базовые (постоянные) и временные.
Временные таблицы существуют в течение сеанса пользователя, в котором он их создал. Если в этом сеансе таблицы не удаляются явно, то они будут удалены автоматически по завершении сеанса. Базовые таблицы предназначены для долговременного хранения информации в базе данных.
Создание
базовых
таблиц
Базовые таблицы создаются оператором CREATE TABLE:
1.
CREATE
TABLE
<имя таблицы>
(
<список спецификаций столбцов и ограничений>
)
;
Спецификация столбца включает имя столбца и тип данных значений, которые могут находиться в этом столбце. Кроме того, некоторые ограничения могут быть заданы не только отдельными спецификациями, но и в спецификации столбца. Примерами могут служить ограничения первичного и внешнего ключей (простых, не составных), а также ограничение NOT NULL.
Изучать аспекты языка, имеющие отношение к таблицам, мы будем на примерах таблиц учебных баз данных.

В таблице Product из схемы «
Компьютерная фирма
» имеется три столбца
– maker, model, type все строкового типа данных VARCHAR(N). Чтобы создать эту таблицу мы можем написать следующий оператор:
1.
CREATE
TABLE
Product
(
maker varchar
(
10
)
, model varchar
(
50
)
, type varchar
(
50
))
;
Значение N указывает максимальное число символов, которое могут содержать данные в данном столбце. VARCHAR является переменным типом, это означает, что если мы зададим значение с числом символов меньше N, то записано на диск будет именно заданное количество символов. Альтернативой служит точный строковый тип