«SQL 10 минут на урок Бен Форта 3-е издание Издательский дом Вильямс Москва • Санкт-Петербург • Киев 2006 ББК 32.973.26-018.2.75 Ф80 УДК 681.3.07 Издательский дом “Вильямс” Главный редактор С.Н. Тригуб Зав. редакцией В. ...»

Освой

самостоятельно

SQL

10 минут на урок

Бен Форта

3-е издание

Издательский дом "Вильямс"

Москва • Санкт-Петербург • Киев

2006

ББК 32.973.26-018.2.75

Ф80

УДК 681.3.07

Издательский дом “Вильямс”

Главный редактор С.Н. Тригуб

Зав. редакцией В. Р. Гинзбург Перевод с английского и редакция B.C. Гусева По общим вопросам обращайтесь в Издательский дом “Вильямс” по адресу:

[email protected], http://www.williamspublishing.com 115419, Москва, а/я 783; 03150, Киев, а/я Форта, Бен.

Ф80 Освой самостоятельно SQL. 10 минут на урок, 3-е издание. : Пер. с англ. — М. :

Издательский дом “Вильямс”, 2006. — 288 с. : ил. — Парал. тит. англ.

ISBN 5-8459-0827-2 (рус.) Данная книга поможет вам в кратчайшие сроки освоить SQL — самый популярный язык баз данных. Начиная с простых запросов на выборку данных, автор урок за уроком рассматривает все более сложные темы, такие как использование операций объединения, подзапросы, хранимые процедуры, индексы, триггеры и ограничения. На изучение материала каждого урока вам потребуется не более 10 минут. Благодаря этой книге вы быстро научитесь самостоятельно составлять запросы к базам данных на языке SQL без чьей-либо помощи.

Примеры, приведенные в книге, будут работать во всех наиболее популярных СУБД — IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle, PostgreSQL и Sybase Adaptive Server.

ББК 32.973.26-018.2. Все названия программных продуктов являются зарегистрированными торговыми марками соответствующих фирм.

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

Authorized translation from the English language edition published by Sams Publishing Copyright © All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from the Publisher.

Russian language edition is published by Williams Publishing House according to the Agreement with R&I Enterprises International, Copyright © ISBN 5-8459-0827-2 (рус.) © Издательский дом “Вильямс”, ISBN 0-672-32567-5 (англ.) © Sams Publishing, Оглавление Введение Урок 1. Что такое SQL Урок 2. Выборка данных Урок 3. Сортировка выбранных данных Урок 4. Фильтрация данных Урок 5. Расширенная фильтрация данных Урок 6. Использование метасимволов для фильтрации Урок 7. Создание вычисляемых полей Урок 8. Использование функций манипулирования данными Урок 9. Суммирование данных Урок 10. Итоговые данные Урок 11. Использование подзапросов Урок 12. Объединение таблиц Урок 13. Создание расширенных объединений Урок 14. Комбинированные запросы Урок 15. Добавление данных Урок 16. Обновление и удаление данных Урок 17. Создание таблиц и работа с ними Урок 18. Использование представлений Урок 19. Работа с хранимыми процедурами Урок 20. Обработка транзакций Урок 21. Использование курсоров Урок 22. Расширенные возможности SQL Приложение А. Сценарии демонстрационных таблиц Приложение Б. Работа с популярными приложениями Приложение В. Синтаксис операторов SQL Приложение Г. Использование типов данных SQL Приложение Д. Зарезервированные слова SQL Предметный указатель Содержание Введение Урок 1. Что такое SQL Урок 2. Выборка данных Урок 3. Сортировка выбранных данных Урок 4. Фильтрация данных Урок 5. Расширенная фильтрация данных Урок 6. Использование метасимволов для фильтрации Использование логического оператора LIKE Метасимвол “символ подчеркивания” (_) Урок 7. Создание вычисляемых полей Урок 8. Использование функций манипулирования данными Урок 9. Суммирование данных Статистические вычисления для отдельных значений Урок 10. Итоговые данные Урок 11. Использование подзапросов Использование подзапросов в качестве вычисляемых полей Урок 12. Объединение таблиц Урок 13. Создание расширенных объединений Использование объединений со статистическими функциями Урок 14. Комбинированные запросы Урок 15. Добавление данных Урок 16. Обновление и удаление данных Урок 17. Создание таблиц и работа с ними Урок 18. Использование представлений Использование представлений для упрощения сложных объединений Использование представлений для переформатирования выбранных данных Использование представлений для фильтрации нежелательных данных Урок 19. Работа с хранимыми процедурами Урок 20. Обработка транзакций Урок 21. Использование курсоров Урок 22. Расширенные возможности SQL Приложение А. Сценарии демонстрационных таблиц Загрузка готового к работе MDB-файла для Microsoft Access Приложение Б. Работа с популярными приложениями Приложение В. Синтаксис операторов SQL Приложение Г. Использование типов данных SQL Приложение Д. Зарезервированные слова SQL Предметный указатель Об авторе Бен Форта — главный технический специалист компании Macromedia, за его плечами лет работы в компьютерной индустрии, включая разработку продуктов, их поддержку и распространение, а также обучение пользованию ими. Бен Форта — автор таких бестселлеров, как ColdFusion Web Application Construction Kit и Advanced ColdFusion Development, Sams Teach Yourself Regular Expressions in 10 Minutes (Освой самостоятельно регулярные выражения. 10 минут на урок, Издательский дом “Вильямс”), а также книг по Flash, Java, WAP, Windows 2000 и другим технологиям. У него огромный опыт в разработке баз данных, он обеспечил их поддержку в нескольких очень популярных программных пакетах. Он часто читает лекции и пишет статьи для Internet, посвященные технологиям баз данных. Бен родился в Лондоне, там же получил образование, затем учился в Нью-Йорке и Лос-Анджелесе. Сейчас он живет в г. Оук-Парк, штат Мичиган, со своей женой Марси и семью детьми. Бену можно написать по адресу [email protected] и посетить его Web-узел по адресу http://www.forta.com.

';

Благодарности Спасибо команде из Sams за многолетнюю поддержку. Особую благодарность хочу выразить Майку Стивенсу (Mike Stephens) и Марку Ренфроу (Mark Renfrow) за то, что они провели это новое издание книги от идеи до реальности (при этом иногда им приходилось вести и меня самого).

Спасибо читателям, которые написали отзывы по первым двум изданиям этой книги. К счастью, большинство из этих отзывов были положительными, и все они были оценены.

Улучшения и изменения в этой редакции были сделаны именно благодаря вашим замечаниям.

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

Ждем ваших отзывов!

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

Мы ждем ваших комментариев и надеемся на них. Вы можете прислать нам бумажное или электронное письмо либо просто посетить наш Web-сервер и оставить свои замечания там. Одним словом, любым удобным для вас способом дайте нам знать, нравится вам эта книга или нет, а также выскажите свое мнение о том, как сделать наши книги более интересными для вас.

Посылая письмо или сообщение, не забудьте указать название книги и ее авторов, а также ваш обратный адрес. Мы внимательно ознакомимся с вашим мнением и обязательно учтем его при отборе и подготовке к изданию последующих книг. Наши координаты:

E-mail: [email protected] WWW: http://www.williamspublishing.com Адреса для писем:

из России: 115419, Москва, а/я из Украины: 03150, Киев, а/я SQL является самым популярным языком баз данных. Не важно, кто вы — разработчик приложений, администратор баз данных, Web-дизайнер или пользователь пакета Microsoft Office, — хорошее практическое знание SQL поможет вам взаимодействовать с базами данных.

Эта книга была написана из необходимости. Несколько лет я вел курс по разработке Webприложений, и студенты постоянно просили порекомендовать им книгу по SQL.

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

Итак, не найдя ни одной книги, которую я бы мог порекомендовать, я вложил весь опыт преподавания в книгу, которую вы держите в руках. Данная книга поможет вам быстро освоить SQL. Начнем мы с простой выборки данных, затем перейдем к более сложным темам, таким как использование операций объединения, подзапросы, хранимые процедуры, индексы, триггеры и ограничения. Обучение будет проходить методично, систематично и просто — на каждый урок вам потребуется не более 10 минут.

Третья редакция этой книги уже помогла изучить SQL сотням тысяч пользователей, теперь пришел ваш черед. Переходите к первому уроку и приступайте к работе. Вы быстро научитесь писать первоклассные SQL-запросы.

Для кого эта книга Эта книга для вас, если вы новичок в SQL;

хотите быстро научиться использовать SQL;

хотите научиться использовать SQL в разрабатываемых вами приложениях;

хотите самостоятельно составлять запросы к базам данных на SQL без чьей-либо помощи.

СУБД, используемые в этой книге В большинстве случаев SQL, который описывается в этой книге, можно применять в любой системе управления базой данных (СУБД, Database Management System — DBMS).

Однако, так как не все реализации SQL идентичны, в книге особенно внимательно будут рассмотрены следующие СУБД (при необходимости будут даваться специальные инструкции или примечания).

Microsoft Access Microsoft SQL Server MySQL Oracle PostgreSQL Sybase Adaptive Server Примеры баз данных и SQL-сценарии будут работать во всех этих СУБД.

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

Текст, который вы вводите, и текст, который должен появиться на экране, представлены моноширинным шрифтом.

Он выглядит так, как на вашем экране.

Переменные и выражения-аргументы приведены моноширинным курсивным шрифтом.

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

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

В примечаниях находится интересная информация, относящаяся к обсуждаемой теме.

В подсказке вы найдете полезный совет или более быстрый способ что-либо выполнить.

В предупреждении вы узнаете о возможных проблемах и научитесь избегать неприятных ситуаций.

Под данной пиктограммой вы найдете определения новых базовых понятий.

ВВОД Пиктограммой “Ввод” обозначен код, который вы можете ввести самостоятельно.

ВЫВОД Пиктограмма “Вывод” указывает на информацию, которая выдается после запуска программы.

Анализ Пиктограмма “Анализ” указывает на то, что далее следует пошаговый комментарий к коду.

На этом уроке вы узнаете, что такое SQL и что с его помощью можно сделать.

Основы баз данных Тот факт, что вы читаете книгу по SQL, говорит о том, что вам так или иначе необходимо работать с базами данных. Язык SQL предназначен именно для этого, поэтому перед тем, как перейти к его рассмотрению, очень важно, чтобы вы познакомились с некоторыми основными понятиями технологии баз данных.

Хотите вы этого или нет, но вы постоянно пользуетесь базами данных. Каждый раз, когда вы выбираете имя в адресной книге электронной почты, вы используете базу данных.

Если вы что-то ищете при помощи поискового сервера в Internet, вы используете базу данных. Когда вы регистрируетесь в локальной сети на работе, вы вводите свое имя и пароль, которые затем сравниваются со значениями, хранящимися в базе данных. И даже когда вы используете свою пластиковую карту в банкомате, вы используете базу данных при проверке PIN-кода и остатка на счету.

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

Основные понятия Ниже приведены очень краткие определения основных понятий баз данных. Они предназначены либо для того, чтобы напомнить вам о том, что вы уже знали, либо чтобы дать вам основные представления, если вы новичок в базах данных. Понимание баз данных является очень важной частью при изучении SQL, поэтому рекомендую найти хорошую книгу по основам баз данных и постоянно пополнять свои знания в данном предмете.

Что такое база данных Термин база данных используется в самых разных аспектах, но мы (а тем более с точки зрения SQL) будем считать базу данных набором сведений, хранящихся некоторым упорядоченным способом. Проще всего рассматривать базу данных как шкаф для хранения документов. Шкаф — это просто физическое местоположение для хранения данных, независимо от того, что это за данные и как они упорядочены.

База данных Контейнер (обычно файл или группа файлов) для хранения упорядоченных данных.

Неправильное использование приводит к путанице Люди используют термин база данных для обозначения программного обеспечения базы данных. Это неправильное использование термина часто ведет к путанице. На самом деле программное обеспечение баз данных называется системой управления базами данных (СУБД). База данных — это хранилище, созданное и управляемое посредством СУБД.

База данных может быть файлом, хранящимся на жестком диске, а может и не являться таковым. Но чаще всего это несущественно, так как вы все равно никогда не обращаетесь к базе данных напрямую, для доступа к ней вы всегда используете СУБД.

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

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

Таблица Структурированный список данных определенного типа.

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

Лучше создать две таблицы для каждого из списков.

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

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

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

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

Схема Информация о базе данных, компоновке и свойствах.

Столбцы и типы данных Таблицы состоят из столбцов, в которых находятся отдельные фрагменты информации таблицы.

Столбец Одно поле таблицы. Все таблицы состоят из одного или нескольких столбцов.

Чтобы лучше понять это, представьте себе таблицы базы данных в виде сетки, наподобие электронных таблиц. В каждом столбце этой сетки находится определенная часть информации. Например, в таблице клиентов в одном столбце находится номер клиента, в другом — его имя. Адрес, город, область, почтовый индекс — все это находится в отдельных столбцах.

Распределение данных Очень важно распределить данные по нескольким столбцам. Например, название города, области (штата) и почтовый индекс (для США это ZIP-код) всегда должны быть в отдельных столбцах. Это позволяет отсортировать или отфильтровать данные по определенным столбцам (например, чтобы найти всех клиентов из определенной области или города). Если названия города и области хранятся в одном столбце, будет очень сложно отсортировать или отфильтровать данные по области.

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

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

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

Совместимость типов данных Типы данных и их названия являются одним из основных источников несовместимости в SQL. Основные типы данных обычно поддерживаются всеми СУБД, в отличие от некоторых расширенных типов. Более того, иногда вы будете сталкиваться с тем фактом, что один и тот же тип данных в разных СУБД называется по-разному. К сожалению, с этим ничего нельзя поделать, но помнить об этом при создании схем таблиц необходимо.

Строки Данные в таблице хранятся в строках; каждая запись хранится в своей строке.

Возвращаясь к сравнению с сеткой, можно сказать, что ее вертикальные столбцы являются столбцами таблицы, а горизонтальные строки — строками таблицы.

Например, в таблице клиентов информация о каждом клиенте хранится в отдельной строке. Число строк в таблице равно числу записей о клиентах.

Запись в таблице.

Записи или строки?

Часто пользователи баз данных упоминают о записях, имея в виду строки. Обычно эти два термина взаимозаменяемы, но термин строка технически более правилен.

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

Первичный ключ Столбец (или набор столбцов), значения которого уникально идентифицируют каждую строку таблицы.

Этот столбец (или набор столбцов), уникально идентифицирующий каждую строку таблицы, называется первичный ключ. Первичный ключ используется для определения конкретной строки. Без него выполнять обновление или удаление строк таблицы было бы очень затруднительно, так как не было бы никакой гарантии, что мы изменяем нужные строки.

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

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

Две разные строки не могут иметь одно и то же значение первичного ключа.

Каждая строка должна иметь определенное значение первичного ключа (столбцы первичного ключа не могут иметь значения NULL).

Значения в столбце первичного ключа не могут быть изменены.

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

Существует еще один важный тип ключа, который называется внешний ключ, но к нему мы вернемся в уроке 12, “Объединение таблиц”.

Что такое SQL?

SQL — это аббревиатура выражения Structured Query Language (язык структурированных запросов). SQL был специально разработан для взаимодействия с базами данных.

В отличие от других языков (разговорных, таких как английский, или языков программирования, например Java или Visual Basic), SQL состоит всего из нескольких слов. И сделано это умышленно. SQL был создан для решения одной задачи, с которой он вполне справляется, — предоставлять простой и эффективный способ считывания и записи информации в базу данных.

Каковы же преимущества SQL?

SQL не относится к числу патентованных языков, используемых разработчиками определенных баз данных. Почти все большие СУБД поддерживают SQL, поэтому знание этого языка позволит вам взаимодействовать практически с любой базой данных.

SQL легко изучить. Его немногочисленные операторы состоят из простых английских слов.

Несмотря на кажущуюся простоту, SQL является очень мощным языком; разумно пользуясь его элементами, можно выполнять очень сложные операции с базами данных.

Именно поэтому стоит изучить SQL.

Расширения SQL Многие разработчики СУБД расширили возможности SQL, введя в язык дополнительные операторы или инструкции. Эти расширения необходимы для выполнения дополнительных функций или для упрощения выполнения определенных операций. И хотя часто они очень полезны, эти расширения привязаны к определенной СУБД и редко поддерживаются более чем одним разработчиком.

Стандартный SQL поддерживается комитетом стандартов ANSI, и соответственно называется ANSI SQL. Все крупные СУБД и даже те, у которых есть собственные расширения, поддерживают ANSI SQL. Отдельные же реализации носят собственные имена (PL-SQL, Transact-SQL и т.д.).

Чаще всего в этой книге упоминается именно ANSI SQL. В редких случаях, когда используется SQL, относящийся к определенной СУБД, об этом говорится отдельно.

Попробуйте сами Подобно изучению любого другого языка, чтобы изучить SQL, лучше всего попробовать его использовать на практике. Для этого вам понадобится база данных и приложение, из которого можно выполнять SQL-запросы.

Во всех уроках этой книги используются настоящие SQL-операторы и настоящие таблицы базы данных. В приложении А, “Сценарии демонстрационных таблиц”, описываются примеры таблиц и приводятся советы по их получению (или созданию), чтобы можно было выполнять инструкции каждого урока. В приложении Б, “Работа с популярными приложениями”, описываются действия, необходимые для запуска SQL в различных приложениях. Перед тем как перейти к следующему уроку, прочитайте эти два приложения, чтобы подготовиться к дальнейшим действиям.

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

На этом уроке вы узнаете, как использовать оператор SELECT для выборки одного или нескольких столбцов данных из таблицы.

Оператор SELECT Как уже говорилось в уроке 1, “Что такое SQL”, SQL-операторы являются обычными английскими терминами. Эти термины называются ключевыми словами, и каждый SQLоператор состоит из одного или нескольких ключевых слов. Наиболее часто вы будете использовать оператор SELECT. Он предназначен для выборки информации из таблиц.

Ключевое слово Зарезервированное слово, являющееся частью языка SQL. Никогда не называйте таблицу или столбец таким словом. В приложении Д “Зарезервированные слова SQL” перечислены некоторые из наиболее часто используемых ключевых слов.

Чтобы при помощи оператора SELECT извлечь данные из таблицы, нужно указать как минимум две вещи — что вы хотите выбрать и откуда.

Рассматриваемые примеры В примерах SQL-операторов (а также полученных с их помощью результатов) в этой книге используются файлы данных, описанные в приложении А, “Сценарии демонстрационных таблиц”. Если вы хотите самостоятельно выполнить действия, указанные в примерах (очень рекомендуем это делать), обратитесь к приложению А, в котором вы найдете инструкции по загрузке или созданию этих файлов.

Очень важно понимать, что SQL — это язык, а не приложение. Метод ввода SQLоператоров и вывод результатов их выполнения различен для разных приложений. Чтобы помочь вам приспособить примеры к вашей СУБД, в приложении Б, “Работа с популярными приложениями” объясняется, как выполнять команды, приведенные в этой книге, в нескольких популярных программах и средах разработки. А если вам нужно приложение, которое поможет вам выполнять примеры, там же вы найдете рекомендации по его выбору.

Выборка отдельных столбцов Начнем с простого SQL-оператора SELECT:

ВВОД SELECT prod_name FROM Products;

Анализ В приведенном выше операторе используется оператор SELECT для выборки одного столбца под названием prod_name из таблицы Products. Искомое имя столбца указывается сразу после ключевого слова SELECT, а ключевое слово FROM указывает на имя таблицы, из которой выбираются данные. Результат выполнения этого оператора будет следующий:

ВЫВОД prod_name Fish bean bag toy Bird bean bag toy Rabbit bean bag toy 8 inch teddy bear 12 inch teddy bear 18 inch teddy bear Raggedy Ann King doll Queen doll Неотсортированные данные Если вы попробовали выполнить этот запрос самостоятельно, то заметили, что данные были отображены в ином порядке. В этом случае не нужно волноваться — так и должно быть. Если результаты запроса не отсортированы явным образом (это мы обсудим в следующем уроке), то данные будут возвращены без особого порядка. Это может быть порядок, в котором данные были занесены в таблицу, или какой-либо другой порядок.

Главное, чтобы ваш запрос возвращал одно и то же число строк.

Простой оператор SELECT, который использовался в предыдущем примере, возвращает все строки таблицы. Данные не фильтруются (как это делается при возвращении подмножества данных) и не сортируются. Эту тему мы обсудим в следующих нескольких уроках.

Используйте пробелы Все лишние пробелы в SQL-операторе при обработке пропускаются. Поэтому SQLоператор может быть записан как в одной длинной строке, так и разбит на несколько строк. Большинство SQL-разработчиков разбивают операторы на несколько строк, чтобы их было легче читать и отлаживать.

Завершение операторов Несколько SQL-операторов должны быть разделены точкой с запятой (символом ;). В большинстве СУБД не требуется вставлять точку с запятой после единственного оператора, но если в вашем конкретном случае СУБД выдает ошибку, вам придется это делать. Несомненно, при желании можно всегда добавлять точку с запятой, она никому не будет мешать, даже если этот символ не обязателен. Исключением является СУБД Sybase Adaptive Server, которая “не любит” SQL-операторы, заканчивающиеся символом ;.

SQL-операторы и регистр Важно отметить, что SQL-операторы нечувствительны к регистру, поэтому операторы SELECT, select и Select эквивалентны. Многие SQL-разработчики используют верхний регистр для всех ключевых слов SQL и нижний регистр для имен столбцов и таблиц, чтобы код легче читался. Однако будьте внимательны: SQL-операторы не зависят от регистра, в отличие от имен таблиц, столбцов и значений (которые зависят от СУБД и ее конфигурации).

Выборка нескольких столбцов Для выборки из таблицы нескольких столбцов используется тот же оператор SELECT.

Отличие состоит в том, что после ключевого слова SELECT необходимо через запятую указать несколько имен столбцов.

Будьте внимательны с запятыми При перечислении нескольких столбцов вставляйте между ними запятые, но не после последнего столбца в списке. Это приведет к ошибке.

В следующем операторе SELECT из таблицы Products выбираются три столбца:

ВВОД SELECT prod_id, prod_name, prod_price FROM Products;

Анализ Как и в предыдущем примере, в этом операторе для выборки данных из таблицы Products используется оператор SELECT. В этом примере перечислены три имени столбца, разделенные запятыми. Результат обработки этого оператора показан ниже:

ВЫВОД Представление данных Как видно из предыдущего результата, SQL-операторы обычно возвращают “сырые”, неотформатированные данные. Форматирование данных является проблемой представления, а не выборки. Поэтому представление (например, отображение приведенных выше цен в виде определенной суммы с правильно расставленными десятичными запятыми) обычно зависит от приложения, посредством которого отображаются данные. Просто выбранные данные (без форматирования) используются редко.

Выборка всех столбцов Помимо возможности осуществлять выборку определенных столбцов (одного или нескольких), при помощи оператора SELECT можно запросить все столбцы, не перечисляя каждый из них. Для этого вместо имен столбцов вставляется групповой символ “звездочка” (*). Это делается следующим образом.

ВВОД SELECT * FROM Products;

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

Однако SQL-данные редко выводятся в том виде, в каком они хранятся в базе данных.

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

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

Резюме В этом уроке мы рассмотрели порядок использования SQL-оператора SELECT для выборки одного, нескольких и всех столбцов таблицы. Далее мы научимся сортировать данные, полученные в результате выборки.

На этом уроке вы узнаете, как использовать предложение ORDER BY оператора SELECT для сортировки полученных в результате выборки данных.

Сортировка данных Из последнего урока вы узнали, что следующий SQL-оператор возвращает один столбец из таблицы базы данных. Но взгляните на результат: данные выводятся в полном беспорядке.

ВВОД SELECT prod_name FROM Products;

ВЫВОД prod_name Fish bean bag toy Bird bean bag toy Rabbit bean bag toy 8 inch teddy bear 12 inch teddy bear 18 inch teddy bear Raggedy Ann King doll Queen doll Вообще-то выбранные данные отображаются не в полном беспорядке. При отсутствии сортировки данные обычно выводятся в том порядке, в котором они находятся в таблице.

Это может быть порядок, в котором они изначально добавлялись в таблицу. Однако если данные впоследствии обновлялись или удалялись, порядок будет зависеть от того, как СУБД будет использовать оставшееся свободное место. В результате вы не можете (и не должны) полагаться на порядок сортировки, если вы не контролируете его. В теории реляционных баз данных говорится, что последовательность выбранных данных не имеет смысла, если не был специально указан порядок сортировки.

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

Примером может служить предложение FROM оператора SELECT, которое мы использовали в предыдущем уроке.

Для точной сортировки выбранных при помощи оператора SELECT данных используется предложение ORDER BY. В этом предложении указывается имя одного или нескольких столбцов, по которым и сортируются результаты. Взгляните на следующий пример:

ВВОД SELECT prod_name FROM Products ORDER BY prod_name;

Анализ Это выражение идентично предыдущему, за исключением предложения ORDER BY, которое указывает системе управления базой данных отсортировать данные в алфавитном порядке по столбцу prod_name. Результат применения этого выражения будет следующим:

ВЫВОД prod_name 12 inch teddy bear 18 inch teddy bear 8 inch teddy bear Bird bean bag toy Fish bean bag toy King doll Queen doll Rabbit bean bag toy Raggedy Ann Местоположение предложения ORDER BY При использовании предложения ORDER BY убедитесь, что оно указано последним в операторе SELECT. Использование предложений в неправильном порядке ведет к появлению сообщений об ошибках.

Сортировка по невыбранным столбцам Чаще всего столбцы, используемые в предложении ORDER BY, отображаются на экране.

Но это не всегда бывает так, данные могут сортироваться и по столбцу, который не выбирается этим запросом.

Сортировка по нескольким столбцам Часто бывает необходимо отсортировать данные по нескольким столбцам. Например, если вы выводите список служащих, вам может понадобиться отсортировать его по имени и фамилии сотрудника (сначала по фамилии, а затем с каждой фамилией по имени). Это может быть полезным, если в компании есть несколько служащих с одинаковыми фамилиями.

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

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

ВВОД SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;

ВЫВОД Важно понимать, что при сортировке по нескольким столбцам порядок сортировки будет таким, который указан в запросе. Другими словами, в примере, приведенном выше, продукция сортируется по столбцу prod_name, только если существует несколько строк с одинаковыми значениями prod_price. Если никакие значения столбца prod_price не совпадают, данные по столбцу prod_name сортироваться не будут.

Сортировка по положению столбца Порядок сортировки можно указать не только по именам столбцов, но и по относительному положению столбца (проще говоря — по номеру столбца). Чтобы лучше понять это, рассмотрим пример:

ВВОД SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;

ВЫВОД Анализ Как видите, результат выполнения запроса идентичен предыдущему примеру. Разница только в предложении ORDER BY. Здесь мы не указывали имена столбцов, вместо этого было оговорено их относительное положение в указанном списке SELECT. Предложение ORDER BY 2 означает сортировку по второму столбцу списка SELECT, а именно по столбцу prod_price. Предложение ORDER BY 2, 3 означает сортировку по столбцу prod_price, а затем по столбцу prod_name.

Основное преимущество данного метода заключается в том, что не нужно несколько раз набирать в запросе имена столбцов. Однако имеются и недостатки. Во-первых, неконкретное перечисление столбцов повышает вероятность того, что вы случайно укажете не тот столбец. Во-вторых, можно случайно сменить порядок данных при изменении списка SELECT (при этом забыв внести соответствующие изменения в предложение ORDER BY). И наконец, очевидно, нельзя использовать этот метод для сортировки по столбцам, не указанным в списке SELECT.

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

Указание направления сортировки Сортировка данных не ограничена порядком по возрастанию (от А до Я). Несмотря на то что этот порядок является порядком по умолчанию, в предложении ORDER BY также можно использовать порядок по убыванию (от Я до А). Для этого необходимо указать ключевое слово DESC.

В следующем примере продукция сортируется по цене в убывающем порядке (вначале идут самые дорогие товары).

ВВОД SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;

ВЫВОД Но что, если производится сортировка по нескольким столбцам? В следующем примере продукция сортируется по цене в убывающем порядке (вначале самые дорогие), плюс по названию продукта:

ВВОД SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;

ВЫВОД Анализ Ключевое слово DESC применяется только к тому столбцу, после которого оно указано. В предыдущем примере ключевое слово DESC было указано для столбца prod_price, но не для prod_name. Таким образом, столбец prod_price отсортирован в порядке убывания, а столбец prod_name в обычном, возрастающем порядке.

Сортировка по убыванию по нескольким столбцам Если вы хотите отсортировать данные в порядке убывания по нескольким столбцам, укажите для каждого из них ключевое слово DESC.

Следует упомянуть, что DESC — это сокращение от DESCENDING, можно использовать оба ключевых слова. Противоположным словом для DESC является ASC (ASCENDING), которое можно указывать для сортировки по возрастанию. Однако на практике слово ASC обычно не применяется, поскольку такой порядок используется по умолчанию (он предполагается, если не указано ни ASC, ни DESC).

Чувствительность к регистру и порядок сортировки При сортировке текстовых данных А это то же самое, что и а? И а идет перед Б или после я? Это не теоретические вопросы, ответ на них зависит от настройки базы данных.

При лексикографическом порядке сортировки А считается идентичным а, и такое поведение является обычным для большинства систем управления базами данных. Однако в некоторых СУБД администратор может при необходимости это поведение изменить.

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

Вам придется обратиться к администратору базы данных.

Резюме Этот урок был посвящен сортировке выбранных данных при помощи предложения ORDER BY оператора SELECT. Это предложение, которое должно быть последним в операторе SELECT, можно использовать для сортировки данных по одному или нескольким столбцам.

На этом уроке вы узнаете, как использовать предложение WHERE оператора SELECT для указания предложений поиска.

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

В операторе SELECT данные фильтруются путем указания критерия поиска в предложении WHERE. Предложение WHERE указывается сразу после названия таблицы (предложения FROM) следующим образом:

ВВОД SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

Анализ Этот оператор извлекает два столбца из таблицы товаров, но показывает не все строки, а только те, значение в столбце prod_price которых равно 3.49:

ВЫВОД prod_name prod_price Fish bean bag toy 3. Bird bean bag toy 3. Rabbit bean bag toy 3. В этом примере используется простая проверка на равенство: сначала проверяется, существует ли в столбце указанное значение, а затем данные фильтруются соответствующим образом. Однако SQL позволяет использовать не только проверку на равенство.

Требовательная PostgreSQL СУБД PostgreSQL имеет строгие правила, управляющие значениями, передающимися в SQL-операторы, особенно это касается чисел с десятичными дробями. Таким образом, предыдущий пример может и не работать в PostgreSQL. Чтобы он заработал, необходимо точно указать, что 3.49 — это “правильное” число, включив в предложение WHERE его тип. Для этого замените = 3.49 на = decimal '3.49'.

Фильтрация в SQL и в приложении Данные также могут быть отфильтрованы на уровне приложения. Для этого посредством оператора SELECT осуществляется выборка большего количества данных, чем на самом деле необходимо для клиентского приложения, а затем клиентский код обрабатывает полученные данные для извлечения только нужных строк.

Как правило, этот метод не приветствуется. Базы данных оптимизированы для быстрой и эффективной фильтрации. Заставляя клиентское приложение выполнять работу базы данных, вы значительно ухудшаете его производительность, а также затрудняете его корректное масштабирование. Кроме того, если данные фильтруются у клиента, сервер отправляет ненужные данные по сети, тем самым занимая лишнюю полосу канала.

Положение предложения WHERE При использовании обоих предложений, ORDER BY и WHERE, убедитесь, что предложение ORDER BY следует за предложением WHERE, иначе возникнет ошибка.

(Более подробно предложение ORDER BY описывается в уроке 3.) Операции в предложении WHERE В первом предложении WHERE, которое мы рассмотрели, проводилась проверка на равенство, т.е. определялось, содержится ли в столбце указанное значение. SQL поддерживает весь спектр условных (логических) операций, которые приведены в табл.

4.1.

Таблица 4.1. Операции в предложении WHERE Операция Описание BETWEE Между двумя указанными значениями IS NULL Значение NULL Совместимость операций Некоторые из операций, приведенных в табл. 4.1, повторяются (например, < > — это то же самое, что и ! =). Выполнение операции ! < (не меньше чем) дает такой же результат, что и > = (больше или равно). Однако заметьте: не все из этих операций поддерживаются всеми СУБД. Обратитесь к документации вашей СУБД, чтобы точно знать, какие логические операции она поддерживает.

Проверка одного значения Мы рассмотрели пример проверки на равенство. Теперь рассмотрим примеры использования других операций.

В первом примере выводятся названия товаров, стоимость которых не превышает $10:

ВВОД SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;

ВЫВОД Fish bean bag toy 3. Bird bean bag toy 3. Rabbit bean bag toy 3. 8 inch teddy bear 5. 12 inch teddy bear 8. В следующем выражении выбираются все товары, которые стоят $10 и меньше (результат будет такой же, как и в первом примере, так как в базе данных нет товаров, которые бы стоили ровно $10):

ВВОД SELECT prod_name, prod_price FROM Products WHERE prod_price < = 10;

Проверка на несовпадения В этом примере выводятся товары, не изготовленные фирмой DLL01.

ВВОД SELECT vend_id, prod_price FROM Products WHERE vend_id < > 'DLL01';

ВЫВОД Когда использовать кавычки Если вы внимательно рассмотрите выражения в предыдущих предложениях WHERE, то заметите, что некоторые значения заключены в одинарные кавычки, а некоторые — нет.

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

Ниже приведен тот же пример, только здесь уже используется операция ! =, вместо < >:

ВВОД SELECT vend_id, prod_price FROM Products WHERE vend_id ! = 'DLL01';

Операции ! = и < > обычно взаимозаменяемы. Однако не во всех СУБД поддерживаются обе формы операции неравенства. Например, в Microsoft Access поддерживается операция < > и не поддерживается ! =. Если у вас возникли сомнения по поводу своей СУБД, обратитесь к ее документации.

Проверка на диапазон значений Для поиска диапазона значений можно использовать операцию BETWEEN. Ее синтаксис немного отличается от других операций предложения WHERE, так как для нее требуются два значения: начальное и конечное. Например, операцию BETWEEN можно использовать для поиска товаров, цена которых находится в промежутке между $5 и $10, или всех дней, которые попадают в диапазон между указанными начальным и конечным числами.

В следующем примере демонстрируется использование операции BETWEEN для выборки всех товаров, цена которых выше $5 и ниже $10:

ВВОД SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;

ВЫВОД 8 inch teddy bear 5. 12 inch teddy bear 8. Анализ Как видно из этого примера, при использовании операции BETWEEN нужно указывать два значения — меньшее и большее из выбранного диапазона. Эти два значения должны быть разделены ключевым словом AND. При этом выбираются все значения из диапазона, включая указанные начальное и конечное значения.

Проверка на отсутствие значения После создания таблицы разработчик может указать, допустимо ли, чтобы в отдельных ее столбцах не содержались никакие значения. Когда в столбце не содержится никакого значения, это значит, что в нем содержится значение NULL.

Отсутствие какого-либо значения, в отличие от поля, содержащего или 0, или пустую строку, или просто несколько пробелов.

Для оператора SELECT предусмотрена специальная форма предложения WHERE, которая используется для проверки значений NULL в столбцах и содержит проверку IS NULL.

Синтаксис выглядит следующим образом:

ВВОД SELECT prod_name FROM Products WHERE prod_price IS NULL;

Это выражение возвращает список товаров без цены (поле prod_price пустое, а не с ценой 0), а поскольку таковых нет, никаких данных мы не получим. Однако в таблице Vendors есть столбцы со значениями NULL — в столбце vend_state будет содержаться NULL, если не указан никакой штат (в случае, когда адресат находится за пределами Соединенных Штатов):

ВВОД SELECT vend_id FROM Vendors WHERE vend_state IS NULL;

ВЫВОД vend_id FNG JTS Особые операции СУБД Во многих СУБД набор операций расширен дополнительными фильтрами. Обратитесь к документации вашей СУБД за дополнительной информацией.

Резюме В этом уроке рассказывалось о том, как отфильтровывать возвращаемые данные при помощи предложения WHERE оператора SELECT. Теперь вы знаете, как можно проверить данные на равенство, неравенство, наличие значений больше чем и меньше чем, диапазон значений, а также на значение NULL.

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

Комбинирование предложений WHERE Все предложения WHERE, представленные в уроке 4, “Фильтрация данных”, отфильтровывают данные с использованием одного критерия. Чтобы увеличить уровень контроля над фильтром в SQL, можно использовать несколько предложений WHERE. Эти предложения допустимо использовать двумя способами: в виде предложений AND или OR.

Оператор Специальное ключевое слово, используемое для объединения или изменения предложений внутри предложения WHERE. Также известны под названием логические операторы 1.

Использование ключевого слова AND Чтобы отфильтровать данные по более чем одному столбцу, необходимо воспользоваться ключевым словом AND для добавления предложений в предложение WHERE. Вот как это делается:

ВВОД SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price < = 4;

Анализ Посредством данного оператора извлекается название продукции и цена для всех товаров, изготовленных производителем DLL01, с ценой $4 и меньше. Предложение WHERE в операторе SELECT состоит из двух предложений, а ключевое слово AND используется для их объединения. Ключевое слово AND указывает системе управления базой данных возвращать только те строки, которые удовлетворяют всем перечисленным предложениям. Если продукт изготовлен производителем DLL01, но стоит больше $4, он не попадет в результаты. Аналогично, товары, которые стоят меньше $4 и изготовлены отличными от указанного производителями, также не будут выведены. Данные, выданные в результате выполнения этой SQL-инструкции, будут выглядеть так:

Термин statement в русскоязычной литературе по языку SQL принято переводить как оператор (и мы следовали этой традиции во всех предыдущих уроках), хотя точнее его следовало бы переводить как инструкция. В данном случае речь идет именно об операторах (operator). — Прим. ред.

ВЫВОД Ключевое слово, используемое в предложении WHERE для того, чтобы возвращались только те строки, которые удовлетворяют всем указанным предложениям.

Использование ключевого слова OR Действие ключевого слова OR противоположно действию ключевого слова AND.

Ключевое слово OR указывает системе управления базой данных выбирать только те строки, которые удовлетворяют хотя бы одному предложению. На самом деле в большинстве лучших СУБД второе предложение даже не рассматривается в предложении OR WHERE, если удовлетворено первое предложение. (Если первое предложение выполнено, строка будет выведена независимо от второго предложения.) Взгляните на следующий оператор SELECT:

ВВОД SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' Анализ Посредством этого SQL-оператора выбираются названия товаров и их цены для всех продуктов, изготовленных одним из указанных производителей. Ключевое слово OR указывает СУБД использовать какое-то одно предложение, а не сразу два. Если бы здесь использовалось ключевое слово AND, мы бы не получили никаких данных. После выполнения этого SQL-запроса мы получим следующие данные:

ВЫВОД Fish bean bag toy 3. Bird bean bag toy 3. Rabbit bean bag toy 3. 8 inch teddy bear 5. 12 inch teddy bear 8. 18 inch teddy bear 11. Ключевое слово, применяемое в предложении WHERE для того, чтобы возвращались все строки, удовлетворяющие любому из указанных предложений.

Порядок обработки Предложения WHERE могут содержать любое количество логических операторов AND и OR. Комбинируя их, можно создавать сложные фильтры.

Однако при комбинировании ключевых слов AND и OR возникает одна проблема.

Рассмотрим следующий пример. Необходимо вывести список всех изготовленных производителями DLL01 и BRS01 товаров, цена которых $10 и выше. В следующей инструкции SELECT используется комбинация ключевых слов AND и OR для формулирования предложения WHERE:

ВВОД SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price > = 10;

ВЫВОД Rabbit bean bag toy 3. 18 inch teddy bear 11. Анализ Взгляните на результат. В четырех возвращенных строках значатся цены ниже $10 — очевидно, строки не были отфильтрованы так, как надо. Что же произошло? Причина в порядке обработки. SQL (как и большинство других языков) вначале обрабатывает логические операторы AND, а потом уже логические операторы OR. Когда SQL “видит” такое предложение WHERE, он его считывает так: выбрать все продукты, которые стоят $10 и больше, изготовленные производителем BRS01, и все продукты, изготовленные производителем DLL01 независимо от их цены. Другими словами, так как приоритет у логического оператора AND выше, были объединены “не те” операторы.

Решение этой проблемы состоит в использовании скобок для точного группирования необходимых логических операторов. Взгляните на следующий оператор SELECT и его выходные данные:

ВВОД SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS0l') AND prod_price >= 10;

ВЫВОД 18 inch teddy bear 11. Анализ Единственным отличием между предыдущим выражением и этим являются скобки, в которые заключены первые два предложения оператора WHERE. Поскольку скобки имеют еще больший приоритет, чем логические операторы AND и OR, СУБД вначале обрабатывает условие OR внутри скобок. Соответственно, SQL-оператор будет пониматься так: выбрать все продукты, изготовленные либо производителем DLL01, либо производителем BRS01, которые стоят $10 и больше, а это именно то, что нужно.

Использование скобок в предложениях WHERE Когда бы вы ни использовали предложения WHERE С ключевыми словами AND И OR, всегда вставляйте скобки, чтобы точно сгруппировать логические операторы. Не полагайтесь на порядок обработки по умолчанию, даже если он подразумевает необходимый вам результат. Нет никаких недостатков в использовании скобок, кроме того, вы всегда будете застрахованы от неопределенностей.

Использование ключевого слова IN Ключевое слово IN используется для указания диапазона условий, любое из которых может быть выполнено. При этом значения, заключенные в скобки, перечисляются через запятую. Рассмотрим следующий пример:

ВВОД SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name ВЫВОД 12 inch teddy bear 8. 18 inch teddy bear 11. 8 inch teddy bear 5. Bird bean bag toy 3. Fish bean bag toy 3. Rabbit bean bag toy 3. Анализ Инструкция SELECT осуществляет выборку всех товаров, изготовленных производителями DLL01 и BRS01. После ключевого слова IN следует список значений через запятую, а весь список заключен в скобки.

Если вы подумаете, что ключевое слово IN выполняет ту же функцию, что и OR, то будете совершенно правы. Следующий SQL-запрос выполняет ту же функцию, что и предыдущий:

ВВОД SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;

ВЫВОД 12 inch teddy bear 8. 18 inch teddy bear 11. 8 inch teddy bear 5. Bird bean bag toy 3. Fish bean bag toy 3. Rabbit bean bag toy 3. Зачем же нужно ключевое слово IN? Его преимущества следующие.

При работе с длинными списками необходимых значений синтаксис логического оператора IN гораздо легче читать.

При использовании ключевого слова IN гораздо легче управлять порядком обработки (так как используется меньшее количество операторов).

Логические операторы IN почти всегда быстрее обрабатываются, чем списки логических операторов OR.

Самое большое преимущество логического оператора IN в том, что в данном операторе может содержаться еще одна инструкция SELECT, а это позволяет создавать очень динамичные предложения WHERE. Более подробно вы об этом узнаете в уроке 11, "Использование подзапросов".

Ключевое слово, используемое в предложении WHERE для указания списка значений, обрабатываемых так же, как это делается в случае применения ключевого слова OR.

Использование ключевого слова NOT Логический оператор NOT предложения WHERE служит для выполнения только одной функции – отрицать все предложения, следующие за ним. Поскольку NOT никогда не используется сам по себе (а только вместе с другими логическими операторами), его синтаксис немного отличается от синтаксиса остальных операторов. В отличие от них, NOT вставляется перед названием столбца, значения которого нужно отфильтровать, а не после.

Ключевое слово, применяемое в предложении WHERE для отрицания какого-то условия.

В следующем примере демонстрируется использование логического оператора NOT. Чтобы извлечь список продуктов, изготовленных всеми производителями, кроме DLL01, можно потребовать выполнить следующее:

ВВОД SELECT prod_name FROM Produces WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

ВЫВОД prod_name 12 inch teddy bear 18 inch teddy bear 8 inch teddy bear King doll Queen doll Анализ Здесь логический оператор NOT отрицает предложение, следующее за ним.

Поэтому СУБД извлекает не те значения vend_id, которые совпадают с DLL01, а все остальные.

Предыдущий запрос можно было также выполнить при помощи операции :

ВВОД SELECT prod_name FROM Products WHERE vend_id о 'DLL01' ORDER BY prod_name,ВЫВОД prod_name 12 inch teddy bear 18 inch teddy bear 8 inch teddy bear King doll Queen doll Анализ Зачем же использовать логический оператор NOT? Конечно, для таких простых предложений WHERE, какие мы здесь рассматриваем, этот оператор не обязателен. Он полезен в более сложных предложениях. Например, для нахождения всех строк, которые не совпадают со списком критериев, можно использовать логический оператор NOT в паре с ключевым словом IN.

Форма логического оператора NOT, который здесь описывается, не поддерживается в СУБД MySQL. В MySQL NOT используется только для отрицания вхождений EXISTS (т.е.

как NOT EXISTS).

Резюме В этом уроке вы узнали, как нужно комбинировать предложения WHERE с логическими операторами AND и OR. Вы также узнали, как следует управлять порядком обработки и как использовать ключевые слова IN и NOT.

Использование метасимволов для фильтрации В этом уроке вы узнаете, что такое метасимволы, как их использовать и как выполнять поиск с применением метасимволов и логического оператора LIKE для фильтрации выводимых данных.

Использование логического оператора LIKE Все предыдущие операторы, которые мы рассмотрели, производили фильтрацию по известным значениям. Они искали совпадения по одному или нескольким значениям, более чем и менее чем известное значение или диапазон значений. При этом везде искалось известное значение. Однако фильтрация данных таким способом не всегда работает. Например, как бы вы искали продукты, в названии которых содержатся слова bean bag? Этого нельзя сделать при помощи простых операций сравнения, здесь на помощь приходит поиск с использованием метасимволов. При помощи метасимволов можно создавать условия поиска данных. В этом примере, для того чтобы найти все продукты, в названии которых содержатся слова bean bag, необходимо составить шаблон поиска, позволяющий найти текст bean bag в любом месте названия продукта.

Метасимволы Специальные символы, применяемые для поиска части значения.

Шаблон поиска Условие поиска, состоящее из текста, метасимволов и любой их комбинации.

Метасимволы сами по себе являются символами, которые имеют в условии WHERE специальное значение. В SQL поддерживаются метасимволы нескольких типов.

Чтобы применять метасимволы в условиях поиска, необходимо использовать ключевое слово LIKE. Оно сообщает СУБД, что следующий шаблон для поиска необходимо сравнивать с использованием метасимволов, а не искать точные совпадения.

Предикат Когда оператор не является оператором? Тогда, когда он является предикатом.

Технически, LIKE – это предикат, а не оператор. Конечный результат остается тем же, просто не пугайтесь этого термина, если вы встретите его в документации по SQL.

Поиск с использованием метасимволов может осуществляться только в текстовых полях (строках), нельзя использовать метасимволы при поиске полей с нетекстовым типом данных.

Метасимвол "знак процента" (%) Наиболее часто используемый метасимвол – знак процента (%). В строке поиска % означает найти все вхождения любого символа. Например, чтобы найти все продукты, названия которых начинаются со слова Fish, можно выполнить следующий запрос:

ВВОД SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';

ВЫВОД prod_id prod_name BNBG01 Fish bean bag toy Анализ В этом примере используется шаблон поиска 'Fish.%'. При выполнении этого условия возвращаются все значения, которые начинаются с символов Fish. Знак % указывает СУБД принимать все символы после слова Fish независимо от их количества.

Метасимволы Microsoft Access Если вы работаете в Microsoft Access, необходимо использовать символ * вместо символа Зависимость от регистра Ваша СУБД и ее конфигурация могут влиять на то, что поиск будет зависеть от регистра.

В этом случае по строке 'fish%' значение Fish bean bag toy не будет найдено.

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

ВВОД SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';

ВЫВОД prod_id prod_name BNBG01 Fish bean bag toy BNBG02 Bird bean bag toy BNBG03 Rabbit bean bag toy Анализ Шаблон поиска ' %bean bag%' означает найти все значения, содержащие bean bag в любом месте названия, независимо от количества символов перед или после указанного текста.

Метасимвол можно также использовать внутри шаблона поиска, хотя это редко бывает полезным. В следующем примере производится поиск всех продуктов, которые начинаются на F и заканчиваются на y:

ВВОД SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y';

Важно отметить, что помимо поиска одного или нескольких символов, знак % также означает и отсутствие символов в указанном месте шаблона поиска.

Следите за замыкающими пробелами Многие СУБД, включая Microsoft Access, заполняют содержимое поля пробелами.

Например, если столбец рассчитан на 50 символов, а в нем вставлен текст Fish bean bag toy (17 символов), то, чтобы заполнить столбец, в него может быть добавлено еще пробела. Обычно это не влияет на данные или их использование, но может негативно отразиться на предыдущем SQL-выражении. По условию WHERE prod_name LIKE 'F%y' будут найдены только те значения prod_name, которые начинаются на F и заканчиваются на у. Если значение заполнено пробелами, оно не будет заканчиваться на у, и значение Fish bean bag toy не будет извлечено. Одним из простых решений может быть добавление второго символа % в шаблон поиска: ' F%y%', после чего будут учитываться символы (пробелы) после буквы у. Но лучше "отрезать" пробелы при помощи функций, которые обсуждаются в уроке 8, "Использование функций манипулирования данными".

Метасимвол "символ подчеркивания" (_) Еще одним полезным метасимволом является символ подчеркивания (_). Символ подчеркивания используется так же, как и %, но при этом учитывается не много символов, а только один.

Метасимволы в Microsoft Access Если вы работаете в Microsoft Access, вам нужно использовать знак ? вместо символа _.

Взгляните на этот пример.

ВВОД SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE ' inch teddy bear';

Следите за замыкающими пробелами Как и в предыдущем примере, возможно, понадобится добавить метасимвол % в шаблон, чтобы пример работал.

ВЫВОД prod_id prod_name BNBG02 12 inch teddy bear BNBG03 18 inch teddy bear Анализ В шаблоне поиска этого предложения WHERE использованы два метасимвола, затем следует текст. В результате были выбраны только те строки, которые удовлетворяли шаблону поиска: по двум символам подчеркивания было найдено число 12 в первой строке и 18 во второй. Продукт 8 inch teddy bear не был найден, так как в шаблоне поиска требуется два совпадения, а не одно. Для сравнения, в следующем выражении SELECT используется метасимвол %, вследствие чего извлекаются три названия товара:

ВВОД SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '% inch teddy bear';

ВЫВОД prod_id prod_name BNBG01 8 inch teddy bear BNBG02 12 inch teddy bear BNBG03 18 inch teddy bear В отличие от знака %, который подразумевает также отсутствие символов, знак _ всегда означает один символ – не более и не менее.

Метасимвол "квадратные скобки" ([ ]) Метасимвол "квадратные скобки" ([ ]) используется для указания набора символов, каждый из которых должен совпадать со значением, причем точно в указанном месте (в местоположении метасимвола).

Наборы не всегда поддерживаются В отличие от метасимволов, описанных ранее, использование квадратных скобок для создания наборов многими СУБД не поддерживается. Наборы поддерживаются в СУБД Microsoft Access, Microsoft SQL Server и Sybase Adaptive Server. Обратитесь к документации по вашей СУБД, чтобы определить, поддерживаются ли в ней наборы.

Например, чтобы найти все контакты людей, имена которых начинаются на букву J или M, необходимо сделать следующее:

ВВОД SELECT prod_id, prod_name FROM Customers WHERE cust_contact LIKE '[JM]% ' ORDER BY cust_contact ВЫВОД cust_contact Jim Jones John Smith Michelle Green Анализ Условие WHERE в этом выражении выглядит как '[JM]%'. В этом шаблоне поиска используются два разных метасимвола. По метасимволам [JM] производится поиск всех контактных лиц, имена которых начинаются на одну из указанных в скобках букв, но при этом учитывается только один символ. Поэтому все имена длиннее одного символа не будут извлечены. По метасимволу %, следующему после [JM], производится поиск любого количества символов после первой буквы, что и приводит к требуемому результату.

Можно использовать метасимвол, выполняющий противоположное действие, добавив перед ним символ ^. Например, в следующем примере выбираются все имена, которые не начинаются с буквы J или М (в отличие от предыдущего примера):

ВВОД SELECT prod_id, prod_name FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact Противоположные наборы в Microsoft Access Если вы работаете в Microsoft Access и требуется создать противоположный набор, необходимо использовать символ ! вместо ^, поэтому указывайте [!JM], а не [^JM].

Конечно, можно достичь того же результата, воспользовавшись логическим оператором NOT. Единственным преимуществом символа ^ является более простой синтаксис при выполнении нескольких предложений WHERE.

Внимание!

Метасимвол ([ ]) поддерживается не всеми СУБД. Обратитесь к документации по вашей СУБД, чтобы определить, поддерживается ли этот метасимвол.

Советы по использованию метасимволов Как видите, метасимволы в SQL – это очень мощный механизм. Но за эту мощь приходится платить: поиск с использованием метасимволов требует больше времени на обработку, чем любые другие виды поиска, которые мы обсуждали ранее. Ниже приведены несколько советов по использованию метасимволов.

Не злоупотребляйте метасимволами. Если можно использовать другой оператор поиска, воспользуйтесь им.

При использовании метасимволов старайтесь по возможности не вставлять их в начало шаблона поиска. Шаблоны поиска, начинающиеся с метасимволов, обрабатываются медленнее всего.

Внимательно следите за местоположением метасимволов. Если они находятся не на своем месте, будут извлечены не те данные.

Исходя из всего вышесказанного, можно заключить, что метасимволы очень важны и очень полезны при поиске – вы часто будете ими пользоваться.

Резюме В этом уроке рассказывалось о том, что такое метасимволы и как их использовать в условиях WHERE. Теперь вы знаете, что метасимволы нужно использовать осторожно, не следует злоупотреблять ими.

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

Что такое вычисляемые поля Данные, хранимые в таблицах базы данных, обычно бывают представлены не в таком виде, который необходим 'для ваших приложений. Вот несколько примеров.

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

Город, штат и ZIP-код хранятся в отдельных столбцах (как и должно быть), но для программы печати почтовых наклеек необходима эта информация в одном, корректно сформированном поле.

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

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

Вам необходимы общая сумма, среднее значение или результаты других расчетов, основанные на данных, имеющихся в таблице.

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

Именно здесь помогут вычисляемые поля. В отличие от всех выбранных нами ранее столбцов, вычисляемых полей на самом деле в таблице базы данных нет. Они создаются "на лету" SQL-оператором SELECT.

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

Важно отметить, что только база данных "знает", какие столбцы в операторе SELECT являются реальными столбцами таблицы, а какие – вычисляемыми полями. С точки зрения клиента (например, вашего приложения), данные вычисляемого поля возвращаются точно так же, как и данные из любого другого столбца.

Клиентское или серверное форматирование?

Многие преобразования и изменения форматов, которые могут быть выполнены посредством SQL-операторов, могут быть также выполнены и клиентским приложением.

Однако, как правило, эти операции гораздо быстрее выполняются на сервере базы данных, чем у клиента, так как СУБД предназначена, кроме всего, для быстрого и эффективного выполнения операций такого типа.

Конкатенация полей Чтобы продемонстрировать работу вычисляемых полей, рассмотрим простой пример – создание заголовка, состоящего из двух столбцов.

В таблице Vendors содержится название поставщика и его адрес. Предположим, что вам необходимо создать отчет по поставщику и указать его адрес как часть его имени в виде имя {адрес).

В отчете должно быть одно значение, а данные в таблице хранятся в двух столбцах:

vend_name и vend_country. Кроме того, значение vend_country необходимо заключить в скобки, которых нет в таблице базы данных. Выражение SELECT, которое возвращает имена поставщиков и адреса, довольно простое, но как создать комбинированное значение?

Конкатенация Комбинирование значений (путем присоединения их друг к другу) для получения одного "длинного" значения.

Для этого необходимо соединить два значения. В SQL-выражении SELECT можно выполнить конкатенацию двух столбцов при помощи специального оператора. В зависимости от СУБД это может быть знак "плюс" (+) или две вертикальные черточки (| |).

В СУБД Access, SQL Server и Sybase для конкатенации используется знак +. В СУБД DB2, Oracle, PostgreSQL и Sybase используется знак | |. Более подробную информацию ищите в документации по вашей СУБД. Вообще-то | | – более предпочтительный оператор конкатенации, так что он поддерживается все большим и большим количеством СУБД.

Ниже приведен пример использования знака "плюс" (применяется синтаксис, принятый в большинстве СУБД).

ВВОД SELECT vend_name + ' (' + vend_country + ') FROM Vendors ORDER BY vend_name;

ВЫВОД Bear Emporium (USA Doll House Inc. (USA ) Fun and Games (England ) Furball Inc. (USA ) Jouets et ours (France ) Ниже приведена та же инструкция, но с использованием оператора | | ВВОД SELECT vend_name | | ' (' | | vend_country | | ') ' FROM Vendors ORDER BY vend_name;

vend_country ВЫВОД Doll House Inc. (USA ) Jouets et ours (France ) Анализ В предыдущих операторах SELECT была выполнена конкатенация следующих элементов:

имя, хранящееся в столбце vend_name;

строка, содержащая пробел и открывающую круглую скобку;

название штата, хранящееся в столбце vend_country;

строка, содержащая закрывающую круглую скобку.

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

Конкатенация в MySQL В MySQL не поддерживается конкатенация при помощи оператора + или | |. Здесь необходимо использовать функцию CONCAT (), в которой указывается список элементов, по отношению к которым необходимо выполнить конкатенацию. При использовании функции CONCAT () первая строка примера выглядела бы так:

SELECT CONCAT(vend_name, ' (', vend_country, ')') В MySQL поддерживается использование оператора | |, но не для конкатенации. В MySQL | | является эквивалентом логического оператора OR, a && – эквивалентом логического оператора AND.

Взгляните еще раз на результат, полученный после применения оператора SELECT.

Два столбца, объединенные в вычисляемое поле, заполнены пробелами. Во многих базах данных (но не во всех) сохраненный текст дополняется пробелами до ширины столбца.

Чтобы выбрать правильно отформатированные данные, необходимо убрать добавленные пробелы. Это можно сделать при помощи SQL-функции RTRIM () следующим образом:

ВВОД SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ') ' FROM Vendors ORDER BY vend_name;

ВЫВОД Bear Emporium (USA) Beras R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France) Ниже приведено это же выражение, но с использованием оператора | |:

ВВОД SELECT RTRIM(vend_name) | | ' (' | | RTRIM(vend_country) | | ') ' FROM Vendors ORDER BY vend_name;

ВЫВОД Bear Emporium (USA) Beras R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets ec ours (France) Анализ Функция RTRIM () отбрасывает все пробелы справа от указанного значения. При использовании функции RTRIM () каждый отдельный столбец обрабатывается корректно.

Город, штат указываются через запятую и пробел, а штат и ZIP-код – через пробел.

Функции TRIM В большинстве СУБД поддерживаются как функция RTRIM () (которая, как мы увидели, "обрезает" правую часть строки), так и LTRIMO (которая удаляет левую часть строки), а также TRIM() (которая "обрезает" строку слева и справа).

Использование псевдонимов Оператор SELECT, который использовался для конкатенации полей имени и адреса, как видите, справился со своей задачей. Но как же называется новый вычисляемый столбец? По правде говоря – никак, это просто значение. Этого может быть достаточно, если вы просматриваете результаты в программе тестирования SQL-запросов, однако столбец без названия нельзя использовать в клиентском приложении, так как клиент не сможет к нему обратиться.

Для решения этой проблемы в SQL была включена поддержка псевдонимов.

Псевдоним – это альтернативное имя для поля или значения. Псевдонимы присваиваются при помощи ключевого слова AS. Взгляните на следующий оператор SELECT:

ВВОД SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ') ' AS vend_title FROM Vendors ORDER BY vend_name;

ВЫВОД vend_title Bear Emporium (USA) Beras R Us (USA) Doll House Inc.

(USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France) Ниже приведена эта же инструкция, но с использованием оператора | |:

ВВОД SELECT RTRIM(vend_name) | | ' (' | | RTRIM(vend_country) | | ')' AS vend_title FROM Vendors ORDER BY vend_name;

ВЫВОД vend_title Bear Emporium (USA) Beras R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France) Анализ Сам по себе этот оператор SELECT ничем не отличается от предыдущего, за исключением того, что вычисляемое поле указывается после текста AS vend_title. Таким образом, SQL создает вычисляемое поле, содержащее результат вычислений, под названием vend_title. Как видите, результат остается тем же, но столбец теперь носит имя vend_title и любое клиентское приложение может обращаться к нему по имени, как если бы это был реальный столбец таблицы.

Другое использование псевдонимов Псевдонимы можно использовать и по-другому. Часто псевдонимы используются для переименования столбца, если в реальном названии присутствуют недопустимые символы (например, пробелы) или если название сложное и трудночитаемое.

Имена псевдонимов Псевдонимом может служить как одно слово, так и целая строка. Если используется строка, она должна быть заключена в кавычки. В принципе, так делать можно, хотя и не рекомендуется. Многословные имена, несомненно, удобнее читать, но они создают множество проблем для многих клиентских приложений. Таким образом, наиболее часто псевдонимы используются для переименования многословных названий столбцов в однословные.

Производные столбцы Псевдонимы иногда называют "производные столбцы", но, независимо оттого, какой термин вы будете использовать, означают они одно и то же.

Выполнение математических вычислений Еще одним способом использования вычисляемых полей является выполнение математических операций над выбранными данными. Рассмотрим пример. В таблице Orders хранятся все полученные заказы, а в таблице Order Items содержатся наименования продуктов для каждого заказа. Следующий SQL-оператор осуществляет выборку всех продуктов в заказе номер 20008:

ВВОД SELECT prod_id, quantity, item_price FROM OrderItems WHERE order_nam = 20008;

ВЫВОД prod_id quantity item_price В столбце item_price содержится цена на продукт для каждой записи, имеющейся в заказе. Чтобы узнать полную цену (цена за один продукт, умноженная на количество продуктов в заказе), необходимо сделать следующее:

ВВОД SELECT prod_id, item_price quantity*item_price AS expanded_price FROM Orderlterns WHERE order_nam = 20008;

ВЫВОД prod id quantity item_price expanded_price Анализ Столбец expanded_price, показанный в предыдущем результате, является вычисляемым полем; вычисление было простым: quantity*item_price. Теперь клиентское приложение может использовать этот новый вычисляемый столбец, как и любой другой в таблице.

В SQL поддерживаются основные математические операции, перечисленные в табл. 7.1. Кроме того, для управления порядком обработки можно использовать круглые скобки. В уроке 5, "Расширенная фильтрация данных", рассказывается о порядке обработки.

Таблица 7.1. Математические операции в SQL Операция писание Резюме В этом уроке вы узнали, что такое вычисляемые поля и как их можно создавать.

Были рассмотрены примеры использования вычисляемых полей для конкатенации строк и выполнения математических операций. Кроме того, вы узнали, как следует создавать и использовать псевдонимы так, чтобы ваше приложение могло обращаться к вычисляемым полям.

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

Что такое функция Как и в большинстве других языков программирования, в SQL поддерживается использование функций для манипулирования данными. Функции – это операции, которые обычно производятся над данными, чаще всего для облегчения преобразований и манипулирования.

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

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

В отличие от SQL-операторов (например, SELECT), которые в основном поддерживаются всеми СУБД одинаково, в разных СУБД могут применяться различные функции. Только некоторые функции в различных СУБД выполняются одинаково. И хотя все типы функций обычно доступны в каждой СУБД, реализация этих функций может значительно отличаться. Чтобы стало понятно, насколько это может быть проблематичным, в табл. 8. перечислены три наиболее часто используемые функции и их синтаксис в различных СУБД.

Таблица 8.1. Различия в функциях СУБД Выборка части строки В Access используется функция MID (). В DB2, Oracle Преобразование типа данных В Access и Oracle используются несколько функций, по Получение текущей даты В Access используется функция NOW (). В DB2 и Как видите, в отличие от SQL-операторов, SQL-функции не относятся к числу переносимых. Это означает, что код, который вы напишете для одной реализации SQL, может не работать в другой.

Переносимый код Код, который может работать в разных системах.

Учитывая переносимость кода, многие SQL-программисты стараются не использовать зависящие от реализации функции. Несмотря на то что это довольно благородная и в чем-то идеальная позиция, она не всегда вписывается в интересы приложения с точки зрения производительности. Ему приходится использовать другие методы выполнения того, что СУБД сделала бы более эффективно.

Стоит ли использовать функции?

Итак, вы пытаетесь решить, использовать функции или нет. Это решение зависит от вас, и здесь нет правильного или неправильного выбора. Если вы решили использовать функции, дописывайте подробные комментарии к коду, чтобы в будущем вы (или другой разработчик) могли узнать, для какой реализации SQL писался данный код.

Использование функций В большинстве реализаций SQL поддерживаются следующие типы функций.

Текстовые функции; используются для управления текстовыми строками (например, для обрезания или заполнения значений и преобразования значений в верхний или нижний регистр).

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

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

Системные функции; возвращают информацию, специфичную для используемой СУБД (например, возвращают регистрационную информацию пользователя).

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

Функции манипулирования текстом В примере функций манипулирования текстом в седьмом уроке функция RTRIM () использовалась для удаления пробелов в конце значения столбца. Ниже приведен еще один пример, в котором используется функция UPPER ():

ВВОД SELECT vend_name UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;

ВЫВОД vend_name vend_name_upcase Bear Emporium BEAR EMPORIUM Doll House Inc. DOLL HOUSE INC.

Fun and Games FUN AND GAMES Furball Inc. FURBALL INC.

Jouets et ours JOUETS ET OURS Анализ Функция UPPER () преобразует текст в верхний регистр и, таким образом, в этом примере имя каждого изготовителя перечислено дважды: первый раз в таком виде, в ка– ом оно хранится в таблице Vendors, а второй раз – будучи преобразованным в верхний регистр, в виде столбца vend_name_upcase.

В табл. 8.2 перечислены наиболее часто используемые функции манипулирования текстом.

Один элемент из табл. 8.2 требует более подробного объяснения. SOUNDEX – это алгоритм, преобразующий текстовую строку в буквенно-цифровой шаблон, описывающий фонетическое представление данного текста. Функция SOUNDEX берет в расчет похожие по звучанию буквы и слоги, позволяя сравнивать строки не по тому, как они пишутся, а по тому, как они звучат. Хотя SOUNDEX не подпадает под основные концепции SQL, большинство СУБД осуществляют поддержку этой функции.

Таблица 8.2. Наиболее часто используемые функции манипулирования текстом LEFT () (или функция подстроки) Возвращает символы из левой части LENGTH (а также DATALENGTH () ИЛИ Возвращает длину строки LEN () ) LTRIM () (LCASE () в Access) Удаляет пробелы в левой части строки RIGHTO (или функция подстроки) Возвращает символы из правой части UPPER () (UCASE в Access) Преобразует текст строки в верхний Поддержка SOTJNDEX Функция SOUNDEXO не поддерживается Microsoft Access или PostgreSQL, поэтому следующий пример не будет работать в этих СУБД.

Ниже приведен пример использования функции SOUNDEX (). Клиент Kids Place находится в таблице Customers и имеет контактное лицо Michelle Green. Но что, если это опечатка и на самом деле контактное лицо пишется как Michael Green? Очевидно, поиск по корректному имени ничего не даст, это показано ниже:

ВВОД SELECT cust_name cust_contract FROM Customers WHERE cust_contract = 'Michael Green' ВЫВОД cust_name cust_contract А теперь попробуйте выполнить поиск при помощи функции SOUNDEX (), чтобы найти все имена контактных лиц, которые звучат как Michael Green:

ВВОД SELECT cust_name cust_contract FROM Customers WHERE SOUNDEX(cust_contract) = SOUNDEX('Michael Green ');

ВЫВОД cust_name cust_contract Kids Place Michelle Green Анализ В этом примере в предложении WHERE используется функция SOUNDEX () для преобразования значения столбца cust_contact и искомой строки в их SOUNDEXзначения. Так как Michael Green и Michelle Green звучат одинаково, их SOUNDEXзначения совпадут и предложение WHERE корректно отфильтрует необходимые данные.

Функции манипулирования датой и временем Дата и время хранятся в таблицах с использованием соответствующих типов данных, каждая СУБД использует свои собственные типы. Значения даты и времени хранятся в специальном формате, поэтому их можно быстро и эффективно сохранить или отфильтровать, а также сохранить физическое пространство на диске.

Формат, в котором хранятся дата и время, обычно нельзя использовать в приложениях, поэтому почти всегда используются функции даты и времени для чтения, расширения и манипулирования этими значениями. Функции манипулирования датой и временем являются одними из наиболее важных функций в SQL. К сожалению, они меньше всего поддаются переносу на другие платформы и реализации SQL.

Чтобы продемонстрировать процедуру использования функции манипулирования датой и временем, приведем простой пример. В таблице Orders все заказы хранятся с датой заказа. Чтобы извлечь список всех заказов, сделанных в 2004 году, в SQL Server и Sybase необходимо выполнить следующее:

ВВОД SELECT order_num FROM Orders WHERE DATEPART(yy, order_date) = 2004;

ВЫВОД order_num В Access используйте следующую версию примера:

ВВОД SELECT order_num FROM Orders WHERE DATEPART ('yyyy', order_date) = 2004;

Анализ В этом примере (в версиях для SQL Server и Sybase и в Access) используется функция DATEPART (), которая, как видно из названия, возвращает только часть даты. В функции DATEPART() используются два параметра: часть, подлежащая возвращению, и дата, из которой эта часть возвращается. В рассматриваемом примере функция DATEPART () из столбца order_column возвращает только год. Путем сравнения полученного значения со значением 2004 предложение WHERE выбирает только те заказы, которые были сделаны в этом году.

Ниже приведена версия данного примера для PostgreSQL, в которой используется похожая функция DATE_PART ():

ВВОД SELECT order_num FROM Orders WHERE DATE_PART('year', order_date) = 2004;

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

ВВОД SELECT order_num FROM Orders WHERE YEAR(order_date) = 2004;

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

Рассмотрим пример:

ВВОД SELECT order_num FROM Orders WHERE to_number(to_char(order_date, 'YY')) = 2004;

Анализ В этом примере функция to_char () используется для извлечения части даты, а функция to_number() – для преобразования этой части в числовое значение, чтобы его можно было сравнить со значением 2004.

Тех же результатов можно добиться при помощи оператора BETWEEN:

ВВОД SELECT order_num FROM Orders WHERE order_date BETWEEN to_date('01-JAN-2004') AND to date('31-DEC-2004');

Анализ В этом примере функция Oracle to_date () используется для преобразования двух строк в даты. В одной содержится дата I января 2004, а в другой – 31 декабря 2004.

Стандартный оператор BETWEEN используется для поиска всех заказов, сделанных в период между этими двумя датами. Этот код не будет работать в SQL Server, так как в этой СУБД не поддерживается функция to_date (). Однако если заменить функцию to_date () функцией DATAPART (), этот оператор можно будет использовать.

Даты в формате ДД-МММ-ГГГГ (как в предыдущих примерах) системой Oracle обычно обрабатываются, даже если они не приведены к тому виду, как при использовании функции to_date (). Однако для надежности лучше всегда использовать эту функцию.

В приведенных примерах выбиралась и использовалась только часть даты (год).

Чтобы выбрать заказы по месяцу, необходимо сделать то же самое, указав ключевое слово AND для сравнения месяца и года.

СУБД обычно могут выполнять гораздо больше действий, чем просто выборка части даты. В большинстве из них присутствуют функции для сравнения дат, выполнения простых арифметических операций с датами, опции форматирования дат и многое другое.

Но, как вы уже заметили, функции манипулирования датой и временем различны для разных СУБД. Обратитесь к документации по своей СУБД и уточните, какие функции манипулирования датой и временем в ней поддерживаются.

Функции для манипулирования числами Числовые функции предназначены для манипулирования числовыми данными. Эти функции используются только для алгебраических, тригонометрических и геометрических вычислений, поэтому они используются не так часто, как функции манипулирования датой и временем.

По иронии судьбы среди всех функций в большинстве СУБД именно числовые функции наиболее стандартизированы. В табл. 8.3 перечислены наиболее часто используемые функции манипулирования числовыми данными.

Таблица 8.3. Наиболее часто используемые функции манипулирования числами Функция Описание ABS () Возвращает абсолютное значение числа COS () Возвращает косинус указанного угла ЕХР () Возвращает экспоненту указанного числа PI () Возвращает значение числа пи SIN () Возвращает синус указанного угла SQRT () Возвращает квадратный корень указанного числа TAN () Возвращает тангенс указанного угла Обратитесь к документации по вашей СУБД, чтобы определить, какие функции манипулирования числовыми данными она поддерживает.

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

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

определение числа строк в таблице (либо числа строк, которые удовлетворяют какому-то условию или содержат определенное значение);

получение суммы по набору строк в таблице;

поиск наибольшего, наименьшего и среднего значений из столбца таблицы (из всех или из каких-то конкретных строк).

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

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

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

Таблица 9.1. Статистические SQL-функции Функция Описание AVG () Возвращает среднее значение столбца COUNT () Возвращает число строк в столбце МАХ () Возвращает самое большое значение в столбце MIN () Возвращает самое маленькое значение в столбце SUM () Возвращает сумму значений столбца Способы использования каждой из этих функций рассматриваются в следующих разделах.

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

В первом примере функция AVG () исользуется для возвращения средней цены для всех продуктов таблицы Products:

ВВОД SELECT AVG(prod_price) AS avg_price FROM Products;

ВЫВОД avg_price 6. Анализ Выражение SELECT, приведенное выше, возвращает одно значение, avg_price, в котором содержится средняя цена всех продуктов таблицы Products. Здесь avg_price – это псевдоним, описанный в уроке 7, "Создание вычисляемых полей."

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

ВВОД SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';