Лекция №1: Стандарты языка sql



страница3/13
Дата11.07.2014
Размер1.07 Mb.
ТипЛекция
1   2   3   4   5   6   7   8   9   ...   13

Лекция №3: Формирование запросов средствами языка SQL

Формирование запросов средствами языка SQL

Оператор SELECT


Оператор SELECT позволяет формировать запрос к базе данных. В результате выполнения этого оператора СУБД формирует результирующий набор (иногда также называемый набором данных). Если этот оператор был введен в интерактивном режиме взаимодействия с базой данных, то результат отображается в виде таблицы в текущем диалоговом окне. На рисунке 3.1. приведен пример выполнения оператора SELECT, извлекающего данные всех столбцов из таблицы dept.



Рис. 3.1. Выполнение оператора SELECT

Если оператор SELECT выполняется из приложения на другом языке программирования, то формируется результирующий набор, размещаемый в памяти приложения или сервера БД, а затем приложение извлекает данные из результирующего набора в свои переменные.

Оператор SELECT имеет в стандарте SQL92 следующее формальное описание:

SELECT [DISTINCT]
     { {function_agregate | expr [AS new_field_name] } .,:
     | specification.*
     | *
     [INTO list_variable]
     FROM {{ имя_таблицы [AS] [table_alias] [(field .,:)]}
               | {subquery [AS] subquery_alas [(field .,:)]}
               | union_table
               | constructor_of_table_value
               | {TABLE имя_таблицы [AS] alias [(field .,:)]}
          } .,:
     [WHERE condition]
     [GROUP BY {{ имя_таблицы | alias }.field} .,: {COLLATE name]]
      [HAVING condition]
     [{ UNION | INTERSECT | EXCEPT } [ALL]
       [CORRESPONDING [BY (field.,:)]]
       SELECT_operator | {TABLE имя_таблицы} | constructor_of_table_value      [ORDER BY] {{field_result [ASC|DESC]}.,:}
             |{{ integer [ASC|DESC]}.,:} ;

Для выполнения запроса требуется привилегия SELECT на все таблицы, участвующие в запросе.

После фразы SELECT указывается список выражений, определяющий значения, формируемые запросом. В самом простом случае список выражений является списком полей таблицы. Если требуется извлечение значений всех полей, то вместо списка полей можно указать символ *. Например:

SELECT * FROM tbl1;

Имя поля может быть квалифицировано именем таблицы, указываемым через точку. Например:

SELECT tbl1.f1, tbl2.f1 FROM tbl1, tbl2;

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

Фраза INTO используется только во встроенном SQL, указывая переменные, в которые записывается результат запроса. При этом формируемый результирующий набор может содержать только одну строку.

Фраза WHERE определяет условие, которому должны удовлетворять все строки, используемые для формирования результирующего набора.

Во всех операциях сравнения языка SQL применяется трехзначная логика (3VL). Предикат, указываемый фразой WHERE, может принимать одно из следующих значений: TRUE, FALSE или UNKNOWN. Значение UNKNOWN получается при сравнении значения NULL с любым другим значением, включая значение NULL.

Предикат содержит одно или несколько выражений, выполняющих сравнения. В выражениях могут участвовать имена столбцов, функции агрегирования, переменные встроенного SQL, параметры модульного SQL.

Кроме стандартных операторов сравнения, таких как =, <>, >, <, >=, <= могут быть использованы следующие операторы:



  • BETWEEN - возвращает TRUE, если значение находится в указанном диапазоне. Например:

x BETWEEN y AND z

эквивалентно выражению



(x<=z) AND (x>=y);

  • IN - совпадает с одним из перечисленных в списке. Например:

x IN (a,b,c);

  • LIKE - возвращает TRUE для значений, совпадающих с указанной подстрокой символов. Например:

x LIKE 'abc';

  • IS NULL - возвращает TRUE, если значение равно NULL. Этот предикат возвращает только значение TRUE или FALSE. Например:

x IS NULL;

  • EXISTS - предикат существования, возвращающий значение TRUE, если указанный в нем подзапрос содержит хотя бы одну строку. Например:

SELECT * FROM tbl1 t_out
     WHERE EXISTS (SELECT * FROM tbl1 t_in
          WHERE t_in.f1= t_out.f1);

  • UNIQUE - предикат уникальности, возвращающий значение TRUE, если указанный в нем подзапрос не содержит одинаковых строк;

  • MATCH - предикат совпадения, возвращающий значение TRUE, если при частичном совпадении (указана фраза PARTIAL) все значения сравниваемой строки равны NULL или существует хотя бы одна строка подзапроса, совпадающая с сравниваемой строкой.

  • OVERLAPS - предикат перекрытия, возвращающий значение TRUE, если сравниваемый период времени перекрывает другие указанные периоды. Тип сравниваемых данных может быть DATETIME или INTERVAL (допустим только для второго значения);

  • SOME, ANY или ALL - предикаты количественного сравнения, для которых существуют следующие правила:

    Вариант сравнения

    Предикат

    SOME

    ANY

    All

    Результат сравнения конструктора значений строки с каждой строкой из набора строк, полученных как подзапрос, равен TRUE

    TRUE

    TRUE

    TRUE

    Результат выполнения подзапроса не содержит строк для сравнения

    FALSE

    FALSE

    TRUE

    Результат сравнения конструктора значений строки с каждой строкой из набора строк, полученных как подзапрос, равен FALSE

    FALSE

    FALSE

    FALSE

    Хотя бы один из результатов сравнения конструктора значений строки со строкой из набора строк, полученных как подзапрос, равен TRUE

    TRUE

    TRUE

    UNKNOWN

  • Для примера обозначим строку запроса заключенной в скобки, а строки подзапроса разделенными пробелом. Так, следующий предикат вернет значение TRUE:

  • (10, 1)> ANY (12, 2 0, NULL 5, 20),

  • так как первая строка из подзапроса удовлетворяет условию. А предикат

  • (NULL, NULL) = ANY (12, 2 NULL, NULL 5, 20)

  • вернет значение UNKNOWN, так как сравнение NULL c NULL в результате дает UNKNOWN. Предикат

  • (10, 1)> ALL (12, 0 0, NULL 5, 20)

  • вернет значение FALSE, так как сравнение строки (10,1) с каждой строкой подзапроса возвращает значение FALSE.



Функции агрегирования

Фраза GROUP BY оператора SELECT применяется для определения группы строк, над которыми выполняются функции агрегирования. Если в операторе SELECT указана фраза GROUP BY, то все имена столбцов, указываемые в списке для определения создаваемого результирующего набора, должны быть указаны с функциями агрегирования, поскольку для каждой группы строк в результирующий набор будет включена только одна строка, содержащая значения полученные функциями агрегирования над данной группой строк.

К функциям агрегирования относятся следующие функции языка SQL:



  • COUNT - подсчет количества всех значений столбцов за исключением значения NULL и с учетом указания фраз ALL или DISTINCT;

  • COUNT (*) - подсчет количества всех значений столбцов в группе;

  • AVG - определение среднего значения;

  • SUM - подсчет суммы всех значении группы. Если при этом получаемое значение выходит за пределы суммируемого типа данных, то инициируется ошибка выполнения SQL-оператора;

  • MAX - определение максимального значения из группы;

  • MIN - определение минимального значения из группы.

Фраза HAVING оператора SELECT определяет предикат аналогично фразе WHERE, но применяемый к строкам, полученным в результате выполнения функций агрегирования.

Приведем пример выбора с применением групп. Столбец dno имеет всего три различных значения: 11, 22 и 33. Для каждой из трех групп находится минимальное и максимальное значение столбца f2:

     SELECT dno, MIN(f2), MAX(f2)
        FROM tbl1
        GROUP BY dno;

Результатом выполнения этого SQL-оператора будет формирование следующих строк:



DNO          

 

MIN(f2)          

 

MAX(f2)          







11

125

200

22

200

2300

33

100

150

При выборе с применением групп и с дополнительным ограничением на значение в столбце MIN(f2):

     SELECT dno, MIN(f2), MAX(f2)


        FROM tbl1
        GROUP BY dno
        HAVING MAX(f2) < 1000;

В результате выполнения этого SQL-оператора будут возвращены только две строки: первая и последняя:



DNO          

 

MIN(f2)          

 

MAX(f2)          







11

125

200

33

100

150
Упорядочивание результирующего набора

Фраза ORDER BY применяется для упорядочивания результирующего набора, которое выполняется в соответствии со значениями столбцов, указанных в списке после фразы ORDER BY. Сначала производится упорядочивание по первому указанному столбцу, потом по второму и т.д. При упорядочивании можно указать опцию ASC (по возрастанию) или DESC (по убыванию).

Например:

SELECT f1,f2 FROM tbl1 ORDER BY f2;

Создание таблиц по образцу

Стандарт SQL-99 вводит поддержку создания таблиц по образцу, позволяя копировать структуру таблицы, создавая новую таблицу, имеющую количество столбцов и их типы, полностью идентичные исходной таблице. Создаваемая по образцу таблица указывается фразой LIKE.

Например:

CREATE TABLE tbl2 LIKE tbl1;.

Соединение таблиц

Соединение одинаковых таблиц

Для соединения таблиц с одноименными столбцами или таблицы с самой собой используются алиасы, задаваемые во фразе FROM через пробел после имени таблицы.

Например:

select t1.f1, t1.f2, t2.f1, t2.f2 from tbl1 t1, tbl1 t2 where t1.f1= t2.f2;

Перекрестное соединение(CROSS JOIN)

Если фраза FROM определяет более одной таблицы или подзапроса, то все эти таблицы соединяются. По умолчанию объединенная таблица представляет собой перекрестное соединение (CROSS JOIN), называемое также декартовым произведением (Cartesian product).

Следующие два оператора эквивалентны:

SELECT tbl1.f1, tbl2.f1 FROM tbl1, tbl2;
SELECT tbl1.f1, tbl2.f1 FROM tbl1 CROSS JOIN tbl2;

Рассмотрим случай, когда таблицы tbl1 и tbl2 имеют строки, отображенные операторами SELECT на рисунке 3.2.





Рис. 3.2. Строки таблиц tbl1 и tbl2

Перекрестное соединение таблиц tbl1 и tbl2 оператором SELECT сформируют результирующий набор, отображаемый на рисунке 3.3.





Рис. 3.3. Перекрестное соединение (CROSS JOIN)

Таким образом, перекрестное соединение создает результирующий набор со всеми возможными комбинациями строк.

Соединения позволяют выполнять временное объединение данных, не предусмотренное схемой (родительскими и внешними ключами).

Соединяемые таблицы перечисляются через запятую во фразе FROM оператора SELECT.



Во фразе FROM можно использовать следующие операторы соединений:

  • CROSS JOIN - перекрестное соединение;

  • NATURAL JOIN - естественное соединение. Стандарт SQL определяет это соединение как результат объединения таблиц по всем одноименным столбцам. Естественное соединение может быть следующих типов:

    • INNER JOIN - внутреннее соединение, используется по умолчанию;

    • LEFT JOIN [OUTER] - левое внешнее соединение;

    • RIGHT JOIN[OUTER] - правое внешнее соединение;

    • FULL JOIN [OUTER] - полное внешнее соединение;

  • UNION JOIN - соединение объединения.
Внутреннее соединение (INNER JOIN)

При внутреннем естественном соединении группируются только те строки, значения которых по соединяемым (одноименным) столбцам совпадают. Результат внутреннего соединения двух таблиц показан на рисунке 3.4.



Рис. 3.4. Внутреннее соединение (INNER JOIN)
Внешнее левое соединение LEFT JOIN [OUTER]

При внешнем левом соединении в результирующий набор будут выбраны все строки из левой таблицы (указываемой первой). При совпадении значений по соединяемым (одноименным) столбцам значения второй таблицы заносятся в результирующий набор в соответствующие строки. При отсутствии совпадений в качестве значений второй таблицы проставляется значение NULL.



Рис. 3.5. Внешнее левое соединение LEFT JOIN [OUTER]
Внешнее правое соединение RIGHT JOIN [OUTER]

При внешнем правом соединении в результирующий набор будут выбраны все строки из правой таблицы (указываемой второй). При совпадении значений по соединяемым (одноименным) столбцам значения первой таблицы заносятся в результирующий набор в соответствующие строки (рис. 3.6.). При отсутствии совпадений в качестве значений первой таблицы проставляется значение NULL.



Рис. 3.6. Внешнее правое соединение RIGHT JOIN [OUTER]
Полное внешнее соединение FULL JOIN [OUTER]

При внешнем правом соединении в результирующий набор будут выбраны все строки, как из правой, так и из левой таблицы. При совпадении значений по соединяемым (одноименным) столбцам строка содержит значения как из левой, так и из правой таблицы (рис. 3.7.). В противном случае, вместо отсутствующих значений в столбцы таблицы (левой или правой) заносится значение NULL.



Рис. 3.7. Полное внешнее соединение FULL JOIN [OUTER]
Соединение по указываемым столбцам

Фраза USING позволяет выполнить естественное соединение по указываемым столбцам, что, в свою очередь, позволяет соединять таблицы, имеющие несколько одноименных столбцов, нужным образом (по одному или двум столбцам). Список столбцов, по которым выполняется соединение, указывается после фразы USING.

Например:

select t1.f1, t1.f2, t2.f1 from tbl1 t1 join tbl2 t2 using f2;

Соединение по предикату

Естественное соединение по указываемому предикату выполняется с помощью фразы ON. В результирующий набор выбираются строки, удовлетворяющие заданному условию. Этот способ соединения аналогичен соединению по предикату, указываемому фразой WHERE.

Например:

select t1.f1, t1.f2, t2.f1, t2.f2 from tbl1 t1 join tbl2 t2 on t1.f1= t2.f2;

1   2   3   4   5   6   7   8   9   ...   13

Похожие:

Лекция №1: Стандарты языка sql iconЯзык sql § Основные сведения о языке sql в этой главе рассмотрим подробно возможностей языка sql structured
Язык sql впервые был реализован фирмой ibm в начале 70-х годов. После этого были выпущены различные модификации этого языка и соответственно...
Лекция №1: Стандарты языка sql icon5 Введение в язык sql
В язык sql в этом разделе мы рассмотрим назначение языка sql, познакомимся с его историей и проанализируем причины, по которым он...
Лекция №1: Стандарты языка sql iconЛабораторная работа Использование редактора запросов sql borland sql explorer(TM) для создания запросов к данным с использованием языка sql. Требования к программному обеспечению
Лабораторная работа Использование редактора запросов sql borland sql explorer(TM) для создания запросов к данным с использованием...
Лекция №1: Стандарты языка sql iconЛекция Структура языка sql. Типы данных
В языке отсутствовали средства синхронизации доступа к объектам бд со стороны параллельно выполняемых транзакций: с самого начала...
Лекция №1: Стандарты языка sql iconЛабораторные работы по курсу "Введение в базы данных" (1-й семестр)
Лабораторные работы заключаются в изучении языка sql. Работы выполняются в sql plus под управлением системы Oracle
Лекция №1: Стандарты языка sql iconЛабораторная работа Работа с sql-сервером в качестве клиента ms access
Запрос sql — это запрос, создаваемый при помощи инструкций sql. Примерами запросов sql могут служить запросы на объединение, запросы...
Лекция №1: Стандарты языка sql iconРежим sql из меню
Этот язык позволяет также управлять обработкой запросов. Sql-запрос представляет собой последовательность инструкций, в которую могут...
Лекция №1: Стандарты языка sql iconArc-310: субд oracle 10g: Язык sql (40 часов) Краткое описание
Бд oracle (dba), он необходим для успешной сдачи сертификационного экзамена. В этом курсе изучаются как стандартные конструкции языка...
Лекция №1: Стандарты языка sql iconЛабораторная работа Основы разработки с sql azure
Субд microsoft sql server для Windows Azure. Работа с sql azure будет достаточно простой для большинства разработчиков, поскольку...
Лекция №1: Стандарты языка sql iconВопросы к экзамену по курсу «базы данных»
Компоненты субд. Применение sql для доступа к бд. Основные функции языка sql. Язык интерактивных запросов. Язык программирования...
Разместите кнопку на своём сайте:
ru.convdocs.org


База данных защищена авторским правом ©ru.convdocs.org 2016
обратиться к администрации
ru.convdocs.org