Главная | Обратная связь | Поможем написать вашу работу!

Подзапросы -- Язык SQL разрешает использовать в других операторах языка DML подзапросы, которые являются внутренними запросами, определяемыми оператором SELECT.

Подзапрос - очень мощное средство языка SQL. Он позволяет строить сложные иерархии запросов, многократно выполняемые в процессе построения результирующего набора или выполнения одного из операторов изменения данных (DELETE, INSERT, UPDATE).

Пример SELECT * from tbl1 WHERE f2=(SELECT f2 FROM tbl2 WHERE f1=1);)

В данном операторе подзапрос всегда должен возвращать единственное значение, которое будет проверяться в предикате. Если подзапрос вернет более одного значения, то СУБД выдаст сообщение об ошибке выполнения SQL-оператора.
SELECT * from tbl1 WHERE (SELECT f2 FROM tbl2 WHERE f1=1) = f2;
Очень часто с подзапросами используются агрегирующие функции, предоставляющие возможность сформулировать условие типа "больше, чем среднее по группе".

SELECT f1,f2,f3 FROM tbl1 WHERE f2> (SELECT AVG(f2) FROM tbl1);

Если результатом подзапроса становится группа строк (это случается всегда, когда условие не гарантирует уникальности значения проверяемого предикатом внутреннего запроса), то следует использовать оператор IN, осуществляющий выбор одного значения из указываемого множества.

SELECT * from tbl1 WHERE f2 IN (SELECT f2 FROM tbl2 WHERE f1=1);

В этом случае предикат принимает значение TRUE, если хотя бы одно из значений возвращаемых подзапросом, удовлетворяет условию. Однако применение оператора IN имеет и некоторые смысловые недостатки: в запросе четко не определяется, сколько строк должны быть результатом выполнения запроса.

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

SELECT avg_f1, COUNT (f2) from tbl1 GROUP BY avg_f1 HAVING avg_f1 >(SELECT f1 FROM tbl1 WHERE f3='a1');

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

SELECT * from tbl1 t1WHERE f2 IN (SELECT f2 FROM tbl2 t2 WHERE t1.f3=t2.f3);

В данном случае для каждой строки таблицы tbl1 будет проверяться условие, что значение поля f2 совпадает со значением строки таблицы tbl2, где значение поля f3 равно значению поля f3 внешней таблицы (tbl1). Это простейший пример коррелированного подзапроса.

Очень часто требуется, чтобы подзапрос использовал те же данные, что и внешняя таблица. В этом случае обязательно применение алиасов.

SELECT * from tbl1 t_out WHERE f2< (SELECT AVG(f2) FROM tbl1 t_in WHERE t_out.f1= t_in.f1);

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

Пример: SELECT f1, COUNT(*), SUM(f2) from tbl1 t1 GROUP BY f1 HAVING SUM(f2)> (SELECT MIN(f2)*4 FROM tbl1 t1_inWHERE t1.f1=t1_in.f1);

В MySQL в команде SELECT функции могут использоваться в двух местах:

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

Пример: Выбрать название каждого события (event), а также его дату в удобном для чтения формате из всех событий, более свежих, чем указанная дата. Функция FROM_UnixTIME() преобразует стандартное значение времени Unix в читаемый вид.

SELECT name, FROM_UnixTIME(date) FROM events WHERE time > 90534323

Выбрать заглавие статьи, полный текст ее, и длину (в байтах) полного текста для всех статей, автор которых Stacie Sheldon. Функция LENGTHO возвращает длину заданной строки в символах.

SELECT title, text, LENGTH(text) FROM papers WHERE author = 'Stacie Sheldon'

- как часть предложения WHERE -- в этом виде функция заменяет место константы при вычислении в предложении WHERE. Значение функции используется при сравнении в каждой строке таблицы.

Пример:Случайным образом выбрать название объекта из общего числа 35. Функция RAND() генерирует случайное число между 0 и 1 (умножается на 34, чтобы сделать его между 0 и 34, и увеличивается на 1, чтобы сделать его между 1 и 35). Функция ROUND() возвращает данное число округленным до ближайшего целого, что приводит к целому числу между 1 и 35, которое должно соответствовать одному из чисел ID в таблице.

SELECT name FROM entries WHERE id = ROUND((RAND()*34) + 1)

Можно использовать функции одновременно в списке значений и предложении WHERE. В этом примере выбираются имя и дата всех событий, происшедших более суток назад. Функция UNIX_TIMESTAMP() без аргументов возвращает текущее время в формате Unix.

SELECT name, FROM_UnixTIME(date) FROM events WHERE time > (Unix_TIMESTAMP() - (60 * 60 * 24))

Функция может использовать значение поля таблицы. В этом примере возвращаются имена всех, кто использовал свое имя в качестве пароля. Функция ENCRYPTO возвращает зашифрованную в стиле пароля Unix заданную строку, используя 2-символьный ключ. Функция LEFT() возвращает п самых левых символов переданной строки.

SELECT name FROM people WHERE password = ENCRYPT(name, LEFT(name, 2))

Функции потока управления программой

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result...] [ELSE result] END,

CASE WHEN [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] END

В первом варианте возвращается значение result, если value=compare-value. Во втором результат для первого указанного условия condition, если оно истинно. Если соответствующая величина результата не определена, то возвращается значение result, указанное после оператора ELSE. Если часть ELSE в выражении отсутствует, возвращается NULL:

SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END; - > "one"

SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END; -> "true"

SELECT CASE BINARY "B" WHEN "a" THEN 1 WHEN "b" THEN 2 END; -> NULL

Тип возвращаемой величины будет такой же (INTEGER, DOUBLE или STRING), как и у первой возвращаемой величины (выражение после первого оператора THEN).

⇐ Предыдущая 8 9 10 11 121314 15 16 17 Следующая ⇒