ПБД (9) - Лекция №6 - SQL (продолжение)

...начало

Языки запросов

SQL

Синтаксис

Представления

CREATE VIEW(поле1, поле2, поле3) AS
    SELECT name, YEAR, stud
    FROM film;

Обновляемые представления - позволяют не только SELECT, но и изменение данных в таблице. Должно быть на основе одной таблицы и содержать обязательные поля.

Примеры

-- чёрно-белые фильмы с группировкой, условием отбора и сортировкой
SELECT name, MAX(YEAR), stud
FROM film
WHERE TYPE = 'bw' GROUP BY name -- группировка
HAVING MIN(YEAR) > 1930         -- условия отбора групп
ORDER BY name DESC;             -- сортировка
 
-- объединение
SELECT ...
UNION [ALL] ...
SELECT ...
 
-- пересечение
SELECT ...
INTERSECT [ALL] ...
SELECT ...
 
-- разность
SELECT ...
EXCEPT [ALL] ...
SELECT ...
 
-- без повторений
SELECT DISTINCT name
FROM film;

Языкы запросов к XML-документам

Пример XML-документа:

<?xml ... ?>
<db>
    <films>
        <f fid="f1" year=1995>
            <name>Собачье сердце</name>
            <role ra="11">Профессор</role>
            <role ra="22">Пёс</role>
            <stud>МФ</stud>
        </f>
        <f fid="f2" year=1993>
            <name>Терминатор</name>
            <role ra="3">Терминатор</role>
            <stud>WB</stud>
            <stud>F20</stud>
        </f>
    </films>
 
    <acts>
        <a inn="11">
            <pers fio="Иванов Иван Иванович">
                <addr>Москва, Ленина, 1</addr>
                <teatr>Ленком</teatr>
                <teatr>Большой театр</teatr>
                <stud>
                    <name>Мосфильм</name>
                    <addr>Москва, Комсомола, 2</addr>
                </stud>
            </pers>
        </a>
    </acts>
</db>

XPath

XML Path Language. Строит по документу дерево полуструктурированных данных.

Обозначения:

• // потомки;
• .. родители;
• . self.

Сокращённая запись пути до фильма:

/db/films/f/name

Полная запись:

/child::db/child::film/child::f

Сокращённая до фильма 1995 года:

/db/films/f/name[../@year="1995"]

Полная:

/child::db/child::film/child::f/child::name[parent(::)/attribute::year="1995"]

Оси

• child - потомки. Стоит по умолчанию, потому можно не указывать;
• attribute - атрибуты @;
• parent - родители;
• ancestor - все предки;
• ancestor-or-self - все предки или я сам;
• descendant - все потомки вне зависимости от уровня вложенности;
• descendant-or-self - все потомки или я сам;
• following - все следующие (без предков и потомков);
• following-sibling - все следующие на одном уровне за текущим;
• preceding - все предыдущие (без предков и потомков)
• preceding-sibling - все предыдущие на одном уровне перед текущим;
• namespace - пространство имён.

Функции

Имеется много встроенных функций. Основные:

• сount() - подсчёт;
• sum() - сумма;
• min() - минимум;
• max() - максимум;
• name() - строка;
• substring(str, start, size) - подстрока.

Примеры

Все потомки:

/db//f

Все элементы name:

/db//name

Все вложенные через два уровня:

//db/*/*/name

Третий фильм:

//f[3]

Третий фильм, если он снят в 1950 году:

//f[3][@year="1950"]

Все фильмы, снятые в 1950, и взять из них третий:

//f[@year="1950"][3]

Все актёры, которые работали в Ленкоме:

//a[./pers/teatr/text()="Ленком"]

Все родители для театра Ленком:

//teatr[text()="Ленком"]/parent::*

Все актёры, у которых указано более трёх театров или более двух адресов:

//a[count(teatr)>3 OR count(//addr)>2]

Все фильмы, в которых снимались актёры Ивановы Иваны Ивановичи:

//f[//a[pers/@fio="Иванов Иван Иванович"]/@inn=role/@ra]

XQuery

Всё ещё сложнее, чем в XPath - он его расширил. Есть условия, функции, вложенные циклы.

Примеры:

вот один:

let $a = 10, $b = 20, $c = "hello"
for $x in колонка
return $x
return <a>{$x}</a>

вот другой:

for $x in document("filename.xml")/db//a
let $c = $x/count(pers/teatr)
where $c > 2 and $a/pers/addr != "Москва"
return {$x, $c}
return <act fio={$x/pers/@fio}>

Формат вывода

Возврат элемента:

return $x

Возврат значения:

return data($x/pers/...)

Вложенные циклы

Как выглядит вложенный цикл:

for $x in document("filename.xml")//a,
    $y in $x//teatr
return <newel>{$a/@inn $y/text()}</newel>
order by $y/text()

Функции

Объявление функции:

declare function fun($var as XS:integer) as element(act)*
   {return element act {$var}}

Вызов функции:

return {fun(10)}