ОРГАНИЗАЦИЯ БАЗ ДАННЫХ И ЗНАНИЙ ТЕМА 5 СТРУКТУРИРОВАННЫЙ ЯЗЫК ЗАПРОСОВ SQL. - презентация

1 ОРГАНИЗАЦИЯ БАЗ ДАННЫХ И ЗНАНИЙ ТЕМА 5 СТРУКТУРИРОВАННЫЙ ЯЗЫК ЗАПРОСОВ SQL

2 Найти все книги, название которых начинается со слова "SQL": SELECT title FROM titles WHERE title LIKE 'SQL%'; В том случае, когда надо найти значение, которое само содержит один из символов шаблона, используют ключевое слово ESCAPE и. Литерал, следующий в шаблоне после ключевого символа, рассматривается как обычный символ, все последующие символы имеют обычное значение. надо найти ссылку на web-страницу, о которой известно, что в ее url содержится подстрока "my_works": SELECT site, url FROM wwwsites WHERE url LIKE ESCAPE Замечание: при выполнении оператора SELECT результирующее отношение может иметь несколько записей с одинаковыми значениями всех полей. Чтобы исключить повторяющиеся записи из выборки используется ключевое слово DISTINCT. Ключевое слово ALL указывает, что в результат необходимо включать все строки.

3 Часто бывает, что не для всех полей в записях определены конкретные значения. SQL учитывает такой вариант, позволяя вам вводить значение NULL в поле, вместо значения, что означает специальное маркирование поля, как не имеющее никакого значения для этой строки (или записи). SQL предоставляет специальный оператор IS, который используется с ключевым словом NULL, для размещения значения NULL. Найдем все записи в таблице Поставщиков с NULL значениями в PNAME столбце: SELECT * FROM Р WHERE PNAME IS NULL;

4 DML: Выборка из нескольких таблиц. Очень часто возникает ситуация, когда выборку данных надо производить из отношения, которое является результатом слияния (join) двух других отношений. Например, нам нужно получить из базы данных publications информацию о всех печатных изданиях в виде следующей таблицы: Для этого СУБД предварительно должна выполнить слияние таблиц titles и publishers, а только затем произвести выборку из полученного отношения. Для выполнения операции такого рода в операторе SELECT после ключевого слова FROM указывается список таблиц, по которым производится поиск данных. После ключевого слова WHERE указывается условие, по которому производится слияние. Для того, чтобы выполнить данный запрос, нужно дать команду: SELECT titles.title,titles.yearpub,publishers.publisher FROM titles,publishers WHERE titles.pub_id=publishers.pub_id; название_книги год_выпуска издательство

5 Пример, где одновременно задаются условия и слияния, и выборки (результат предыдущего запроса ограничивается изданиями после 1996 года): SELECT titles.title,titles.yearpub,publishers.publisher FROM titles,publishers WHERE titles.pub_id=publishers.pub_id AND titles.yearpub>1996; Следует обратить внимание на то, что когда в разных таблицах присутствуют одноименные поля, то для устранения неоднозначности перед именем поля указывается имя таблицы и знак "." (точка). (Хорошее правило: имя таблицы указывать всегда!) Естественно, имеется возможность производить слияние и более чем двух таблиц. Например, чтобы дополнить описанную выше выборку именами авторов книг необходимо составить оператор следующего вида: SELECT authors.author,titles.title,titles.yearpub,publishers.publisher FROM titles,publishers,titleauthors WHERE titleauthors.au_id=authors.au_id AND titleauthors.title_id=titles.title_id AND titles.pub_id=publishers.pub_id AND titles.yearpub > 1996; 1996;">

6 Естественное соединение таблиц (способ 1 - явное указание условий соединения): SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME FROM P, PD WHERE P.PNUM = PD.PNUM; В результате получим новую таблицу, в которой строки с данными о поставщиках соединены со строками с данными о поставках деталей: PNUMPNAMEDNUMVOLUME 1Иванов Иванов Иванов Петров Петров Сидоров Замечание. Соединяемые таблицы перечислены в разделе FROM оператора, условие соединения приведено в разделе WHERE. Раздел WHERE, помимо условия соединения таблиц, может также содержать и условия отбора строк.

7 Естественное соединение таблиц (способ 2 - ключевые слова JOIN…USING…): SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME FROM P JOIN PD USING PNUM; Замечание. Ключевое слово USING позволяет явно указать, по каким из общих колонок таблиц будет производиться соединение. Естественное соединение таблиц (способ 3 - ключевое слово NATURAL JOIN): SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME FROM P NATURAL JOIN PD; Замечание. В разделе FROM не указано, по каким полям производится соединение. NATURAL JOIN автоматически соединяет по всем одинаковым полям в таблицах. Естественное соединение трех таблиц: SELECT P.PNAME, D.DNAME, PD.VOLUME FROM P NATURAL JOIN PD NATURAL JOIN D; PNAMEDNAMEVOLUME Иванов Болт 100 Иванов Гайка 200 Иванов Винт 300 Петров Болт 150 Петров Гайка 250 Сидоров Болт 1000

8 Прямое произведение таблиц: SELECT P.PNUM, P.PNAME, D.DNUM, D.DNAME FROM P, D; В результате получим следующую таблицу: PNUMPNAMEDNUMDNAME 1Иванов 1Болт 1Иванов 2Гайка 1Иванов 3Винт 2Петров 1Болт 2Петров 2Гайка 2Петров 3Винт 3Сидоров 1Болт 3Сидоров 2Гайка 3Сидоров 3Винт Замечание. Т.к. не указано условие соединения таблиц, то каждая строка первой таблицы соединится с каждой строкой второй таблицы.

9 Соединение таблиц по произвольному условию. Рассмотрим таблицы поставщиков и деталей, которыми присвоен некоторый статус: Запрос: "какие поставщики имеют право поставлять какие детали?" SELECT P.PNUM, P.PNAME, P.PSTATUS, D.DNUM, D.DNAME, D.DSTATUS FROM P, D WHERE P.PSTATUS >= D.DSTATUS; PNUMPNAMEPSTATUS 1Иванов 4 2Петров 1 3Сидоров 2 DNUMDNAMEDSTATUS 1Болт 3 2Гайка 2 3Винт 1 Отношение P (Поставщики) Отношение D (Детали) PNUMPNAMEPSTATUSDNUMDNAMEDSTATUS 1Иванов 41Болт 3 1Иванов 42Гайка 2 1Иванов 43Винт 1 2Петров 13Винт 1 3Сидоров 22Гайка 2 3Сидоров 23Винт 1 В результате получим следующую таблицу: = D.DSTATUS; PNUMPNAMEPSTATUS 1Иванов 4 2Петров 1 3Сидоров 2 DNUMDNAMEDSTATUS 1Болт 3 2Гайка 2 3Винт 1 Отношение P (Поставщики) Отношение D (Детали) PNUMPNAMEPSTATUSDNUMDNAMEDSTATUS 1Иванов 41Болт 3 1Иванов 42Гайка 2 1Иванов 43Винт 1 2Петров 13Винт 1 3Сидоров 22Гайка 2 3Сидоров 23Винт 1 В результате получим следующую таблицу:">

10 Использование имен корреляции (алиасов, псевдонимов) Иногда приходится выполнять запросы, в которых таблица соединяется сама с собой, или одна таблица соединяется дважды с другой таблицей. При этом используются имена корреляции (алиасы, псевдонимы), которые позволяют различать соединяемые копии таблиц. Имена корреляции вводятся в разделе FROM и идут через пробел после имени таблицы. Имена корреляции должны использоваться в качестве префикса перед именем столбца и отделяются от имени столбца точкой. Если в запросе указываются одни и те же поля из разных экземпляров одной таблицы, они должны быть переименованы для устранения неоднозначности в именованиях колонок результатирующей таблицы. Определение имени корреляции действует только во время выполнения запроса.

11 Отобрать все пары поставщиков таким образом, чтобы первый поставщик в паре имел статус, больший статуса второго поставщика: SELECT P1. PNAME AS PNAME1, P1. PSTATUS AS PSTATUS1, P2. PNAME AS PNAME2, P2. PSTATUS AS PSTATUS2 FROM P P1, P P2 WHERE P1.PSTATUS1 > P2.PSTATUS2; В результате получим следующую таблицу: PNAME1PSTATUS1PNAME2PSTATUS2 Иванов 4Петров 1 Иванов 4Сидоров 2 2Петров 1

12 DML: Вычисления внутри SELECT. SQL позволяет выполнять различные арифметические операции над столбцами результирующего отношения. В конструкции можно использовать константы, функции и их комбинации с арифметическими операциями и скобками. Например, чтобы узнать сколько лет прошло с 1992 года до публикации той или иной книги можно выполнить команду: SELECT title, yearpub-1992 FROM titles WHERE yearpub > 1992; В арифметических выражения допускаются операции сложения (+), вычитания (-), деления (/), умножения (*), а также различные функции (COS, SIN, ABS - абсолютное значение и т.д.). Также в запрос можно добавить строковую константу: SELECT 'the title of the book is', title, yearpub-1992 FROM titles WHERE yearpub > 1992;

13 Использование скалярных выражений и переименований колонок в запросах (ключевое слово AS…): SELECT TOVAR.TNAME, TOVAR.KOL, TOVAR.PRICE, "=" AS EQU, TOVAR.KOL*TOVAR.PRICE AS SUMMA FROM TOVAR; В результате получим таблицу с колонками, которых не было в исходной таблице TOVAR: TNAMEKOLPRICEEQUSUMMA Болт 10100=1000 Гайка 20200=4000 Винт 30300=9000

14 Агрегатные функции. В SQL также функции совершают действия над совокупностью одинаковых полей в группе записей. Среди них: AVG( ) - среднее по всем значениям данного поля COUNT( ) или COUNT (*) - число записей или не-NULL значения полей которые выбрал запрос MAX( ) - максимальное из всех значений данного поля MIN( ) - минимальное из всех значений данного поля SUM( ) - сумма всех значений данного поля Замечание: каждая агрегирующая функция возвращает единственное значение. Примеры: определить дату публикации самой "древней" книги в БД: SELECT MIN(yearpub) FROM titles; подсчитать количество книг в БД: SELECT COUNT(*) FROM titles;

15 Получить общее количество поставщиков: SELECT COUNT(*) AS N FROM P; В результате получим таблицу с одним столбцом и одной строкой, содержащей количество строк из таблицы P: Область действия агрегатных функций ограничивается с помощью логических условий. Например: получить количество книг, в названии которых есть слово "SQL": SELECT COUNT(*) FROM titles WHERE title LIKE '%SQL%'; получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG): SELECT SUM(PD.VOLUME) AS SM, MAX(PD.VOLUME) AS MX, MIN(PD.VOLUME) AS MN, AVG(PD.VOLUME) AS AV FROM PD; В результате получим следующую таблицу с одной строкой: N 3 SMMXMNAV ,3333

16 DML: Групировка данных. Группировка данных в операторе SELECT осуществляется с помощью ключевого слова GROUP BY и ключевого слова HAVING, с помощью которого задаются условия разбиения записей на группы. GROUP BY неразрывно связано с агрегирующими функциями, без них оно практически не используется. GROUP BY разделяет таблицу на группы, а агрегирующая функция вычисляет для каждой из них итоговое значение. Примеры: Определить количество книг каждого издательства в БД: SELECT publishers.publisher, count(titles.title) FROM titles,publishers WHERE titles.pub_id=publishers.pub_id GROUP BY publisher;

17 получить суммарное поставляемое количество для каждой детали (ключевое слово GROUP BY…): SELECT PD.DNUM, SUM(PD.VOLUME) AS SM GROUP BY PD.DNUM; Последовательность выполнения запроса: сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. потом внутри каждой группы будет просуммировано поле VOLUME. от каждой группы в результатирующую таблицу будет включена одна строка: DNUMSM

18 Замечание. В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки. Следующий запрос выдаст синтаксическую ошибку: SELECT PD.PNUM, PD.DNUM, SUM(PD.VOLUME) AS SM GROUP BY PD.DNUM; Причина ошибки: в список отбираемых полей включено поле PNUM, которое не входит в раздел GROUP BY. И действительно, в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM. Из каждой группы строк будет сформировано по одной итоговой строке. При этом НЕТ однозначного ответа на вопрос, какое значение выбрать для поля PNUM в итоговой строке. Замечание. Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результатирующей таблице, невозможно.

19 HAVING работает следующим образом: сначала GROUP BY разбивает строки на группы, затем на полученные наборы накладываются условия HAVING. включить в результат только те издательтва, название которых оканчивается на подстроку "Press": SELECT publishers.publisher, count(titles.title) FROM titles,publishers WHERE titles.pub_id=publishers.pub_id GROUP BY publisher HAVING publisher LIKE '%Press'; Определить количество книг каждого издательства и убрать из запроса те издательства, которые имеют только одну книгу: SELECT publishers.publisher, count(titles.title) FROM titles,publishers WHERE titles.pub_id=publishers.pub_id GROUP BY publisher HAVING COUNT(*)>1; Различие между двумя этими вариантами использования HAVING: В первом варианте условие отбора записей помещены в раздел WHERE; Во втором варианте это сделать невозможно, поскольку WHERE не допускает использования агрегирующих функций. 1; Различие между двумя этими вариантами использования HAVING: В первом варианте условие отбора записей помещены в раздел WHERE; Во втором варианте это сделать невозможно, поскольку WHERE не допускает использования агрегирующих функций.">

20 Получить номера деталей, суммарное поставляемое количество которых превосходит 400: SELECT PD.DNUM, SUM(PD.VOLUME) AS SM GROUP BY PD.DNUM HAVING SUM(PD.VOLUME) > 400; В результате получим следующую таблицу: Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. _______________________________________________ Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY. DNUMSM

21 DML: Cортировка данных. Для сортировки данных, получаемых при помощи оператора SELECT служит ключевое слово ORDER BY. С его помощью можно сортировать результаты по любому столбцу или выражению, указанному в. Данные могут быть упорядочены как по возрастанию, так и по убыванию. Примеры: сортировать список авторов по алфавиту: SELECT author FROM authors ORDER BY author; получить список авторов, отсортированный по алфавиту, и список их публикаций, причем для каждого автора список книг сортируется по времени издания в обратном порядке (т.е. сначала более "свежие" книги, затем все более "древние"): SELECT authors.author,titles.title,titles.yearpub,publishers.publisher FROM authors,titles,publishers,titleauthors WHERE titleauthors.au_id=authors.au_id AND titleauthors.title_id=titles.title_id AND titles.pub_id=publishers.pub_id ORDER BY authors.author ASC, titles.yearpub DESC; Ключевое слово DESC задает здесь обратный порядок сортировки по полю yearpub, ключевое слов ASC (его можно опускать) - прямой порядок сортировки по полю author.

22 Упорядочение результатов запроса (ключевое слово ORDER BY…): SELECT PD.PNUM, PD.DNUM, PD.VOLUME FROM PD ORDER BY DNUM; В результате получим следующую таблицу, упорядоченную по полю DNUM: PNUMDNUMVOLUME Упорядочение результатов запроса по нескольким полям с возрастанием или убыванием (ключевые слова ASC, DESC): SELECT PD.PNUM, PD.DNUM, PD.VOLUME FROM PD ORDER BY DNUM ASC, VOLUME DESC; В результате получим таблицу, в которой строки идут в порядке возрастания значения поля DNUM, а строки, с одинаковым значением DNUM идут в порядке убывания значения поля VOLUME: PNUMDNUMVOLUME Замечание. Если явно не указаны ключевые слова ASC или DESC, то по умолчанию принимается упорядочение по возрастанию (ASC)

23 В SQL предусмотрена возможность выполнения операций реляционной алгебры: объединения, пересечения и разности DML: Операция объединения. В SQL предусмотрена возможность выполнения операции реляционной алгебры "ОБЪЕДИНЕНИЕ" (UNION) над отношениями, являющимися результатами оператора SELECT. Эти отношения должны быть определены по одной схеме, т.е. должны быть совместимы (иметь одинаковое количество столбцов и одинаковые типы столбцов в порядке их перечисления). Замечание. Не требуется, чтобы объединяемые таблицы имели бы одинаковые имена колонок. Это отличает операцию объединения запросов в SQL от операции объединения в реляционной алгебре. Наименования колонок в результатирующем запросе будут автоматически взяты из результата первого запроса в объединении.

24 Пример: получить все Интеренет-ссылки, хранимые в базе данных publications. Эти ссылки хранятся в таблицах publishers и wwwsites: SELECT publisher,url FROM publishers UNION SELECT site,url FROM wwwsites; Получить имена поставщиков, имеющих статус, больший 3 или поставляющих хотя бы одну деталь номер 2 (объединение двух подзапросов - ключевое слово UNION): SELECT P.PNAME FROM P WHERE P.STATUS > 3 UNION SELECT P.PNAME FROM P, PD WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;

25 Номер поставщика PNUM Наименование поставщика PNAME 1Иванов 2Петров 3Сидоров Номер детали DNUM Наименование детали DNAME 1Болт 2Гайка 3Винт Отношение P (Поставщики) Отношение D (Детали) Номер поставщика PNUM Номер детали DNUM Поставляемое количество VOLUME Отношение PD (Поставки)

26 DML: Операция пересечения. Получить имена поставщиков, имеющих статус, больший 3 и одновременно поставляющих хотя бы одну деталь номер 2 (пересечение двух подзапросов - ключевое слово INTERSECT): SELECT P.PNAME FROM P WHERE P.STATUS > 3 INTERSECT SELECT P.PNAME FROM P, PD WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2; DML: Операция вычитания. Получить имена поставщиков, имеющих статус, больший 3, за исключением тех, кто поставляет хотя бы одну деталь номер 2 (разность двух подзапросов - ключевое слово EXCEPT): SELECT P.PNAME FROM P WHERE P.STATUS > 3 EXCEPT SELECT P.PNAME FROM P, PD WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;

27 Номер контрагента NUM Наименование контрагента NAME 1Иванов 2Петров 3Сидоров Номер детали DNUM Наименование детали DNAME 1Болт 2Гайка 3Винт Номер поставщика PNUM Номер получателя CNUM Номер детали DNUM Поставляемое количество VOLUME Пример: Рассмотрим ситуацию, когда некоторые поставщики (назовем их контрагенты) могут выступать как в качестве поставщиков деталей, так и в качестве получателей. Таблицы, хранящие данные могут иметь следующий вид: Отношение CONTRAGENTS Отношение DETAILS (Детали) Отношение CD (Поставки)

28 В таблице CD (поставки) поля PNUM и CNUM являются внешними ключами, ссылающимися на потенциальный ключ NUM в таблице CONTRAGENTS. Ответ на вопрос "кто кому что в каком количестве поставляет" дается следующим запросом: SELECT P.NAME AS PNAME, C.NAME AS CNAME, DETAILS.DNAME, CD.VOLUME FROM CONTRAGENTS P, CONTRAGENTS C, DETAILS, CD WHERE P.NUM = CD.PNUM AND C.NUM = CD.CNUM AND D.DNUM = CD.DNUM; Наименование поставщика PNAME Наименование получателя CNAME Наименование детали DNAME Поставляемое количество VOLUME Иванов Петров Болт 100 Иванов Сидоров Гайка 200 Иванов Сидоров Винт 300 Петров Сидоров Болт 150 Петров Сидоров Гайка 250 Сидоров Иванов Болт 1000

29 Этот запрос может быть выражен другим способом (таких способов может быть большое количество): SELECT P.NAME AS PNAME, C.NAME AS CNAME, DETAILS.DNAME, CD.VOLUME FROM CONTRAGENTS P, CONTRAGENTS C, DETAILS NATURAL JOIN CD WHERE P.NUM = CD.PNUM AND C.NUM = CD.CNUM;

30 Использование представлений. В отличие от таблиц, которые реально хранятся в базе данных (базовых таблиц (base tables)), существует другой вид таблиц, называемых "представлениями"("представляемые таблицы"). Представление (view) - это таблица, содержимое которой берется из других таблиц посредством запроса. При этом новые копии данных не создаются Когда содержимое базовых таблиц меняется, СУБД автоматически перевыполняет запросы, создающие view, что приводит к соответствующи изменениям в представлениях. Представление определяется с помощью команды CREATE VIEW [,...] AS При этом должны соблюдаться следующие ограничения: –представление должно базироваться на единcтвенном запросе (UNION не допустимо) –выходные данные запроса, формирующего представление, должны быть не упорядочены (ORDER BY не допустимо)

31 Пример: Создать представление, хранящее информацию об авторах, их книгах и издателях этих книг: CREATE VIEW books AS SELECT authors.author,titles.title,titles.yearpub,publishers.publ isher FROM authors,titles,publishers,titleauthors WHERE titleauthors.au_id=authors.au_id AND titleauthors.title_id=titles.title_id AND titles.pub_id=publishers.pub_id Теперь любой пользователь, чьих прав на доступ к данному представлению достаточно, может осуществлять выборку данных из books. Например: SELECT titles FROM books WHERE author LIKE '%Date' SELECT author,count(title) FROM books GROUP BY author

32 (Права пользователей на доступ в представлениям назначаются также с помощью команд GRANT / REVOKE.) Использования представлений может значительно сократить накладные расходы на выполнение опреаций соединеия. В представлении может быть представлена информация, явно не хранимая ни в одной из базовых таблиц. Например, один из столбцов представления может быть вычисляемым: CREATE VIEW amount (publisher, books_count) AS SELECT publishers.publisher, count(titles.title) FROM titles,publishers WHERE titles.pub_id=publishers.pub_id GROUP BY publisher; Здесь использована возможность SQL присвоения новых имен столбцам представления. Для этого нужно указывать имена для всех столбцов. Тип данных столбца представления и его нулевой статус всегда зависят от того, как он был определен в базовой таблице (таблицах). В приведенном примере число изданий, осуществленных каждым издатетлем, будет хранится в столбце с именем books_count.

33 Запрос на выборку данных к представлению выглядит абсолютно аналогично запросу к любой другой таблице. Однако на изменение данных в представлении накладываются ограничения: Если представление основано на одной таблице, изменения данных в нем допускаются. При этом изменяются данные в связанной с ним таблице. Если представление основано более чем на одной таблице, то изменения данных в нем не допускаются, т.к. в большинстве случаев СУБД не может правильно восстановить схему базовых таблиц из схемы представления. Удаление представления производится с помощью оператора: DROP VIEW