Введение в системы управления базами данных




Глава 5. Элементы языка SQL


    Глава 5. Элементы языка SQL
    Глава 5. Элементы языка SQL В данной главе рассматриваются элементы языка SQL (Structured Query Language). Текущая версия стандарта языка SQL принята в 1992 г. (Официальное название стандарта - Ме...
    Операторы SQL
    Операторы SQL Основу языка SQL составляют операторы, условно разбитые не несколько групп по выполняемым функциям. Можно выделить следующие группы операторов (перечислены не все операторы SQL):...
    Операторы DDL (Data Definition Language) - операторы определения объектов базы данных
    Операторы DDL (Data Definition Language) - операторы определения объектов базы данных CREATE SCHEMA - создать схему базы данных DROP SHEMA - удалить схему базы данных CREATE TABLE - создать таблиц...
    Операторы DML (Data Manipulation Language) - операторы манипулирования данными
    Операторы DML (Data Manipulation Language) - операторы манипулирования данными SELECT - отобрать строки из таблиц INSERT - добавить строки в таблицу UPDATE - изменить строки в таблице DELETE - уда...
    Операторы защиты и управления данными
    Операторы защиты и управления данными CREATE ASSERTION - создать ограничение DROP ASSERTION - удалить ограничение GRANT - предоставить привилегии пользователю или приложению на манипулирование объ...
    Примеры использования операторов манипулирования данными INSERT - вставка строк в таблицу
    INSERT - вставка строк в таблицу Пример 1 . Вставка одной строки в таблицу: INSERT INTO P (PNUM, PNAME) VALUES (4, "Иванов");...
    Пример 2
    Пример 2 . Вставка в таблицу нескольких строк, выбранных из другой таблицы (в таблицу TMP_TABLE вставляются данные о поставщиках из таблицы P, имеющие номера, большие 2): INSERT INTO TMP_TABLE (PN...
    UPDATE - обновление строк в таблице
    UPDATE - обновление строк в таблице...
    Пример 3
    Пример 3 . Обновление нескольких строк в таблице: UPDATE P SET PNAME = "Пушников" WHERE P.PNUM = 1;...
    DELETE - удаление строк в таблице
    DELETE - удаление строк в таблице...
    Пример 4
    Пример 4 . Удаление нескольких строк в таблице: DELETE FROM P WHERE P.PNUM = 1;...
    Пример 5
    Пример 5 . Удаление всех строк в таблице: DELETE FROM P;...
    Примеры использования оператора...
    Отбор данных из одной таблицы Пример 6 . Выбрать все данные из таблицы поставщиков (ключевые слова SELECT … FROM …): SELECT * FROM P; Замечание . В результате получим новую таблицу, содержащую пол...
    Пример 7
    Пример 7 . Выбрать все строки из таблицы поставщиков, удовлетворяющих некоторому условию (ключевое слово WHERE …): SELECT * FROM P WHERE P.PNUM 2; Замечание . В качестве условия в разделе WHERE мо...
    Пример 8
    Пример 8 . Выбрать некоторые колонки из исходной таблицы (указание списка отбираемых колонок): SELECT P.NAME FROM P; Замечание . В результате получим таблицу с одной колонкой, содержащую все наиме...
    Пример 9
    Пример 9 . Выбрать некоторые колонки из исходной таблицы, удалив из результата повторяющиеся строки (ключевое слово DISTINCT ): SELECT DISTINCT P.NAME FROM P; Замечание . Использование ключевого с...
    Пример 10
    Пример 10 . Использование скалярных выражений и переименований колонок в запросах (ключевое слово AS …): SELECT TOVAR.TNAME, TOVAR.KOL, TOVAR.PRICE, "=" AS EQU, TOVAR.KOL*TOVAR.PRICE AS SUMMA FROM...
    Таблица 1
    Таблица 1 TNAME KOL PRICE EQU SUMMA Болт 10 100 = 1000 Гайка 20 200 = 4000 Винт 30 300 = 9000...
    Пример 11
    Пример 11 .Упорядочение результатов запроса (ключевое слово ORDER BY …): SELECT PD.PNUM, PD.DNUM, PD.VOLUME FROM PD ORDER BY DNUM; В результате получим следующую таблицу, упорядоченную по полю DNU...
    Таблица 2
    Таблица 2 PNUM DNUM VOLUME 1 1 100 2 1 150 3 1 1000 1 2 200 2 2 250 1 3 300...
    Пример 12
    Пример 12 . Упорядочение результатов запроса по нескольким полям с возрастанием или убыванием (ключевые слова ASC , DESC ): SELECT PD.PNUM, PD.DNUM, PD.VOLUME FROM PD ORDER BY DNUM ASC, VOLUME DES...
    Таблица 3
    Таблица 3 PNUM DNUM VOLUME 3 1 1000 2 1 150 1 1 100 2 2 250 1 2 200 1 3 300 Замечание . Если явно не указаны ключевые слова ASC или DESC, то по умолчанию принимается упорядочение по возрастанию (A...
    Пример 13
    Пример 13 . Естественное соединение таблиц (способ 1 - явное указание условий соединения): SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME FROM P, PD WHERE P.PNUM = PD.PNUM; В результате получим новую...
    Таблица 4
    Таблица 4 PNUM PNAME DNUM VOLUME 1 Иванов 1 100 1 Иванов 2 200 1 Иванов 3 300 2 Петров 1 150 2 Петров 2 250 3 Сидоров 1 1000 Замечание . Соединяемые таблицы перечислены в разделе F...
    Пример 14
    Пример 14 . Естественное соединение таблиц (способ 2 - ключевые слова JOIN… USING… ): SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME FROM P JOIN PD USING PNUM; Замечание . Ключевое слово USING позволя...
    Пример 15
    Пример 15 . Естественное соединение таблиц (способ 3 - ключевое слово NATURAL JOIN ): SELECT P.PNUM, P.PNAME, PD.DNUM, PD.VOLUME FROM P NATURAL JOIN PD; Замечание . В разделе FROM не указано, по к...
    Пример 16
    Пример 16 . Естественное соединение трех таблиц: SELECT P.PNAME, D.DNAME, PD.VOLUME FROM P NATURAL JOIN PD NATURAL JOIN D; В результате получим следующую таблицу:...
    Таблица 5
    Таблица 5 PNAME DNAME VOLUME Иванов Болт 100 Иванов Гайка 200 Иванов Винт 300 Петров Болт 150 Петров Гайка 250 Сидоров Болт 1000...
    Пример 17
    Пример 17 . Прямое произведение таблиц: SELECT P.PNUM, P.PNAME, D.DNUM, D.DNAME FROM P, D; В результате получим следующую таблицу:...
    Таблица 6
    Таблица 6 PNUM PNAME DNUM DNAME 1 Иванов 1 Болт 1 Иванов 2 Гайка 1 Иванов 3 Винт 2 Петров 1 Болт 2 Петров 2 Гайка 2 Петров 3 Винт 3 Сидоров ...
    Пример 18
    Пример 18 . Соединение таблиц по произвольному условию. Рассмотрим таблицы поставщиков и деталей, которыми присвоен некоторый статую (см. пример 8 из предыдущей главы):...
    Таблица 7
    Таблица 7 PNUM PNAME PSTATUS 1 Иванов 4 2 Петров 1 3 Сидоров 2 Таблица 1 Отношение P (Поставщики)...
    Таблица 8
    Таблица 8 DNUM DNAME DSTATUS 1 Болт 3 2 Гайка 2 3 Винт 1 Таблица 2 Отношение D (Детали) Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает следующий запрос: SELECT P.PNUM...
    Таблица 9
    Таблица 9 PNUM PNAME PSTATUS DNUM DNAME DSTATUS 1 Иванов 4 1 Болт 3 1 Иванов 4 2 Гайка 2 1 Иванов 4 3 Винт 1 2 Петров 1 3 Винт 1 3 Сидоров 2 2 ...
    Использование имен корреляции (алиасов, псевдонимов)
    Использование имен корреляции (алиасов, псевдонимов) Иногда приходится выполнять запросы, в которых таблица соединяется сама с собой, или одна таблица соединяется дважды с другой таблицей. При это...
    Пример 19. Отобрать все пары поставщиков...
    Таблица 10 PNAME1 PSTATUS1 PNAME2 PSTATUS2 Иванов 4 Петров 1 Иванов 4 Сидоров 2 Сидоров 2 Петров 1...
    Пример 20
    Пример 20 . Рассмотрим ситуацию, когда некоторые поставщики (назовем их контрагенты) могут выступать как в качестве поставщиков деталей, так и в качестве получателей. Таблицы, хранящие данные могу...
    Таблица 11
    Таблица 11 Номер контрагента NUM Наименование контрагента NAME 1 Иванов 2 Петров 3 Сидоров Таблица 3 Отношение CONTRAGENTS...
    Таблица 12
    Таблица 12 Номер детали DNUMНаименование детали DNAME 1 Болт 2 Гайка 3 Винт Таблица 4 Отношение DETAILS (Детали)...
    Таблица 13
    Таблица 13 Номер поставщика PNUMНомер получателя CNUMНомер детали DNUMПоставляемое количество VOLUME 1 2 1 100 1 3 2 200 1 3 3 300 2 3 1 150 2 3 2 250 3 1 1 1000 Таблица...
    Таблица 14
    Таблица 14 Наименование поставщика PNAMEНаименование получателя CNAMEНаименование детали DNAMEПоставляемое количество VOLUME Иванов Петров Болт 100 Иванов Сидоров Гайка 200 Иванов Сидоров Винт 300...
    Пример 21
    Пример 21 . Получить общее количество поставщиков (ключевое слово COUNT ): SELECT COUNT(*) AS N FROM P; В результате получим таблицу с одним столбцом и одной строкой, содержащей количество строк и...
    Таблица 15
    Таблица 15 N 3...
    Пример 22
    Пример 22 . Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM , MAX , MIN , AVG ): SELECT SUM(PD.VOLUME) AS SM, MAX(PD.VOLUME) AS MX, MIN(PD.V...
    Таблица 16
    Таблица 16 SM MX MN AV 2000 1000 100 333.33333333...
    Пример 23
    Пример 23 . Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY …): SELECT PD.DNUM, SUM(PD.VOLUME) AS SM GROUP BY PD.DNUM; Этот запрос будет выполняться следующим...
    Таблица 17
    Таблица 17 DNUM SM 1 1250 2 450 3 300 Замечание . В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие...
    Пример 24
    Пример 24 . Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING …): Замечание . Условие, что суммарное поставляемое количество должно быть бол...
    Таблица 18
    Таблица 18 DNUM SM 1 1250 2 450 Замечание . В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя пер...
    Использование подзапросов
    Использование подзапросов Очень удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос....
    Пример 25
    Пример 25 . Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом): SELECT * FROM P WHERE P.STATYS (SELECT MAX(P.STATUS) FROM P);...
    Пример 26
    Пример 26 . Использование предиката IN . Получить список поставщиков, поставляющих деталь номер 2: SELECT * FROM P WHERE P.PNUM IN (SELECT DISTINCT PD.PNUM FROM PD WHERE PD.DNUM = 2); Замечание ....
    Пример 27
    Пример 27 . Использование предиката EXIST . Получить список поставщиков, поставляющих деталь номер 2: SELECT * FROM P WHERE EXIST (SELECT * FROM PD WHERE PD.PNUM = P.PNUM AND PD.DNUM = 2); Замечан...
    Пример 28
    Пример 28 . Использование предиката NOT EXIST . Получить список поставщиков, не поставляющих деталь номер 2: SELECT * FROM P WHERE NOT EXIST (SELECT * FROM PD WHERE PD.PNUM = P.PNUM AND PD.DNUM =...
    Пример 29
    Пример 29 . Получить имена поставщиков, поставляющих все детали: SELECT DISTINCT PNAME FROM P WHERE NOT EXIST (SELECT * FROM D WHERE NOT EXIST (SELECT * FROM PD WHERE PD.DNUM = D.DNUM AND PD.PNUM...
    Пример 30
    Пример 30 . Получить имена поставщиков, имеющих статус, больший 3 или поставляющих хотя бы одну деталь номер 2 (объединение двух подзапросов - ключевое слово UNION ): SELECT P.PNAME FROM P WHERE P...
    Пример 31
    Пример 31 . Получить имена поставщиков, имеющих статус, больший 3 и одновременно поставляющих хотя бы одну деталь номер 2 (пересечение двух подзапросов - ключевое слово INTERSECT ): SELECT P.PNAME...
    Пример 32
    Пример 32 . Получить имена поставщиков, имеющих статус, больший 3, за исключением тех, кто поставляет хотя бы одну деталь номер 2 (разность двух подзапросов - ключевое слово EXCEPT ): SELECT P.PNA...
    BNF-нотация
    BNF-нотация Опишем синтаксис оператора выборки данных (оператора SELECT) более точно. При описании синтаксиса операторов обычно используются условные обозначения, известные как стандартные формы Б...
    Синтаксис оператора выборки
    Синтаксис оператора выборки В довольно сильно упрощенном виде оператор выборки данных имеет следующий синтаксис (для некоторых элементов мы дадим не BNF-определения, а словесное описание): Операто...
    Синтаксис соединенных таблиц
    Синтаксис соединенных таблиц В разделе FROM оператора SELECT можно использовать соединенные таблицы. Пусть в результате некоторых операций мы получаем таблицы A и B. Такими операциями могут быть,...
    Синтаксис условных выражений раздела WHERE
    Синтаксис условных выражений раздела WHERE Условное выражение, используемое в разделе WHERE оператора SELECT должно вычисляться для каждой строки-кандидата, отбираемой оператором SELECT. Условное...
    Пример 33
    Пример 33 . Сравнение поля таблицы и скалярного значения:POSTAV.VOLUME 100...
    Пример 34
    Пример 34 . Сравнение двух сконструированных строк:(PD.PNUM, PD.DNUM) = (1, 25) Этот пример эквивалентен условному выражениюPD.PNUM = 1 AND PD.DNUM = 25 Предикат between ::= Конструктор значений с...
    Пример 35
    Пример 35 . PD.VOLUME BETWEEN 10 AND 100 Предикат in ::= Конструктор значений строки [ NOT ] IN {( Select-выражение ) | ( Выражение для вычисления значения .,..)}...
    Пример 36
    Пример 36 . P.PNUM IN (SELECT PD.PNUM FROM PD WHERE PD.DNUM=2)...
    Пример 37
    Пример 37 . P.PNUM IN (1, 2, 3, 5) Предикат like ::= Выражение для вычисления значения строки-поиска [ NOT ] LIKE Выражение для вычисления значения строки-шаблона [ ESCAPE Символ ] Замечание . Пре...
    Пример 38
    Пример 38 . P.PNUM = SOME (SELECT PD.PNUM FROM PD WHERE PD.DNUM=2) Предикат exist ::= EXIST ( Select-выражение ) Замечание . Предикат EXIST возвращает значение TRUE, если результат подзапроса (sel...
    Порядок выполнения оператора SELECT
    Порядок выполнения оператора SELECT Для того чтобы понять, как получается результат выполнения оператора SELECT, рассмотрим концептуальную схему его выполнения. Эта схема является именно концептуа...
    Стадия 1. Выполнение одиночного оператора SELECT
    Стадия 1. Выполнение одиночного оператора SELECT Если в операторе присутствуют ключевые слова UNION, EXCEPT и INTERSECT, то запрос разбивается на несколько независимых запросов, каждый из которых...
    Шаг 1 (FROM)
    Шаг 1 (FROM) . Вычисляется прямое декартовое произведение всех таблиц, указанных в обязательном разделе FROM. В результате шага 1 получаем таблицу A....
    Шаг 2 (WHERE)
    Шаг 2 (WHERE) . Если в операторе SELECT присутствует раздел WHERE, то сканируется таблица A, полученная при выполнении шага 1. При этом для каждой строки из таблицы A вычисляется условное выражени...
    Шаг 3 (GROUP BY)
    Шаг 3 (GROUP BY) . Если в операторе SELECT присутствует раздел GROUP BY, то строки таблицы B, полученной на втором шаге, группируются в соответствии со списком группировки, приведенным в разделе G...
    Шаг 4 (HAVING)
    Шаг 4 (HAVING) . Если в операторе SELECT присутствует раздел HAVING, то группы, не удовлетворяющие условному выражению, приведенному в разделе HAVING, исключаются. Если раздел HAVING опущен, то ср...
    Шаг 5 (SELECT)
    Шаг 5 (SELECT) . Каждая группа, полученная на шаге 4, генерирует одну строку результата следующим образом. Вычисляются все скалярные выражения, указанные в разделе SELECT. По правилам использовани...
    Стадия 2. Выполнение операций UNION, EXCEPT, INTERSECT
    Стадия 2. Выполнение операций UNION, EXCEPT, INTERSECT Если в операторе SELECT присутствовали ключевые слова UNION, EXCEPT и INTERSECT, то таблицы, полученные в результате выполнения 1-й стадии, о...
    Стадия 3. Упорядочение результата
    Стадия 3. Упорядочение результата Если в операторе SELECT присутствует раздел ORDER BY, то строки полученной на предыдущих шагах таблицы упорядочиваются в соответствии со списком упорядочения, при...
    Как на самом деле выполняется оператор SELECT
    Как на самом деле выполняется оператор SELECT Если внимательно рассмотреть приведенный выше концептуальный алгоритм вычисления результата оператора SELECT, то сразу понятно, что выполнять его непо...
    Шаг 1 (Синтаксический анализ)
    Шаг 1 (Синтаксический анализ) . Поступивший запрос подвергается синтаксическому анализу. На этом шаге определяется, правильно ли вообще (с точки зрения синтаксиса SQL) сформулирован запрос. В ходе...
    Шаг 2 (Преобразование в каноническую форму)
    Шаг 2 (Преобразование в каноническую форму) . Запрос во внутреннем представлении подвергается преобразованию в некоторую каноническую форму. При преобразовании к канонической форме используются ка...
    Шаг 3 (Генерация планов выполнения запроса и выбор оптимального плана)
    Шаг 3 (Генерация планов выполнения запроса и выбор оптимального плана) . На этом шаге оптимизатор генерирует множество возможных планов выполнения запроса. Каждый план строится как комбинация низк...
    Шаг 4. (Выполнение плана запроса)
    Шаг 4. (Выполнение плана запроса) . На этом шаге план, выбранный на предыдущем шаге, передается на реальное выполнение. Во многом качество конкретной СУБД определяется качеством ее оптимизатора. Х...
    Реализация реляционной алгебры средствами оператора SELECT (Реляционная полнота SQL)
    Реализация реляционной алгебры средствами оператора SELECT (Реляционная полнота SQL) Для того, чтобы показать, что язык SQL является реляционно полным, нужно показать, что любой реляционный операт...
    Оператор декартового произведения
    Оператор декартового произведения Реляционная алгебра: Оператор SQL: SELECT A.Поле1, A.Поле2, …, B.Поле1, B.Поле2, … FROM A, B; или SELECT A.Поле1, A.Поле2, …, B.Поле1, B.Поле2, … FROM A CROSS JOI...
    Оператор проекции
    Оператор проекции Реляционная алгебра: Оператор SQL: SELECT DISTINCT X, Y, …, Z FROM A;...
    Оператор выборки
    Оператор выборки Реляционная алгебра: , Оператор SQL: SELECT * FROM A WHERE c;...
    Оператор объединения
    Оператор объединения Реляционная алгебра: Оператор SQL: SELECT * FROM A UNION SELECT * FROM B;...
    Оператор вычитания
    Оператор вычитания Реляционная алгебра: Оператор SQL: SELECT * FROM A EXCEPT SELECT * FROM B Реляционный оператор переименования RENAME выражается при помощи ключевого слова AS в списке отбираемых...
    Оператор соединения
    Оператор соединения Реляционная алгебра: Оператор SQL: SELECT A.Поле1, A.Поле2, …, B.Поле1, B.Поле2, … FROM A, B WHERE c; или SELECT A.Поле1, A.Поле2, …, B.Поле1, B.Поле2, … FROM A CROSS JOIN B WH...
    Оператор пересечения
    Оператор пересечения Реляционная алгебра: Оператор SQL: SELECT * FROM A INTERSECT SELECT * FROM B;...
    Оператор деления
    Оператор деления Реляционная алгебра: Оператор SQL: SELECT DISTINCT A.X FROM A WHERE NOT EXIST (SELECT * FROM B WHERE NOT EXIST (SELECT * FROM A A1 WHERE A1.X = A.X AND A1.Y = B.Y)); Замечание . О...
    Выводы
    Выводы Фактически стандартным языком доступа к базам данных в настоящее время стал язык SQL (Structured Query Language). Язык SQL оперирует терминами, несколько отличающимися от терминов реляционн...








Начало