|
|
|
|
Запросы:
Выбор Данных из Таблицы
Команда select (выбор) используется для извлечения данных из таблицы. Эту команду можно использовать для выбора данных как по строкам, так по столбцам из одной или нескольких таблиц. В данной главе рассматриваются следующие темы: · Выбор данных по всем столбцам таблицы. · Выбор данных по указанным столбцам таблицы. · Изменение формы представления результатов в операторе выбора путем переименования заголовков столбцов и добаления символьных строк. · Включение простых вычисляемых величин в оператор выбора. · Устранение одинаковых строк с помощью команды distinct (различные). · Использование конструкции from (из) для указания таблиц и вьюверов (views). · Использование в конструкции where (где) операций сравнения, логических операций, а также операций between (между), in (в), any (любой) и like (как). · Использование значений null (неопределенный) и not null (определенный). В этой главе основное внимание уделено простым запросам выбора данных из одной таблицы. Опытные пользователи могут найти для себя полезную информацию, касающуюся более сложных способов выбора, в последующих главах этого руководства. Запрос это обращение к базе данных с целью получения результирующих данных. Этот процесс также называется нахождением данных. Все SQL запросы выражаются через оператор выбора (select). Этот оператор можно использовать как для выбора записей (строк) из одной или нескольких таблиц, так и для построения проекций (projections), т.е. выбора данных по некоторому подмножеству атрибутов (столбцов) из одной или нескольких таблиц. В упрощенном виде оператор select можно записать следующим образом: select список_выбора from список_таблиц where условия_выбора После ключевого слова select указываются атрибуты (столбцы), по которым осуществляется выбор данных. После ключевого слова from указываются таблицы, из которых происходит выбор данных по указанным атрибутам. После ключевого слова where указываются условия, по которым выбираются записи (строки) из таблиц. Например, в следующем операторе select из таблицы authors (авторы) выбираютя имена и фамилии писателей, живуших в Окленде. select au_fname,
au_lname from authors where city =
“Oakland” Результаты этого запроса могут иметь, например, следующий вид:
(Выбрано 5 строк) Синтаксис оператора select может быть и проще и сложнее по сравнению с приведенным выше примером. Проще, потому что единственным обязательным словом в этом операторе является само слово select. Конструкция from почти всегда присутствует в операторе выбора, но, строго говоря, она необходима только при выборе данных из таблиц. Конструкция where является необязательной, как и все остальные конструкции. С другой стороны, полный синтаксис оператора select включает следующие ключевые слова и фразы: select [all |
distinct] список_выбора [into
[[database.] owner.] название_таблицы] [from [[database.] owner.] { название_таблицы | название_вьювера [(index название_индекса [prefetch размер] [lru | mru] ) ] } [holdlock | noholdlock] [shared] [,[[database.] owner.] { название_таблицы | название_вьювера [(index название_индекса [prefetch размер] [lru | mru] ) ] } [ holdlock | noholdlock ] [shared] ]
... ] [where условия_выбора ] [group by [all] итоговое_выражение [, итоговое_выражение ] ... ] [having условия_поиска ] [order by { [[database.] owner.] { название_таблицы. | название_вьювера. } ] название_столбца | номер_списка_выбора | выражение } [ask | desc] [, { [[database.] owner.] { название_таблицы. | название_вьювера. } ] название_столбца | номер_списка_выбора | выражение } [ask | desc] ... ] [compute row_agregate
(название_столбца) [, row_agregate (название_столбца) ] ... [by название_столбца [, название_столбца] ... ]] [for {read only | update [of список_названий_столбцов] } ] [at isolation {read uncommitted | read
committed | serializable} ] [for browse] Конструкции в операторе выбора должны следовать в указанном здесь порядке. Другими словами, если оператор включает конструкции group by (группировка) и order by (сортировка), то конструкция group by должна предшествовать конструкции order by. Как отмечается в разделе “Идентификаторы”, название объектов базы данных должны дополняться (уточняться) в тех случаях, когда они имеют одинаковые имена и непонятно на какой из них указывает данное название. Например, если несколько столбцов (атрибутов) называются name (имя), то name должно быть дополнено либо названием базы данных, либо именем владельца, либо названием таблицы. Поскольку в этой главе рассматриваются в основном простые запросы, обращающиеся к одной таблице, то названия атрибутов обычно не будут дополняться названиями базы данных, именем владельца или именем таблицы, откуда они взяты. Эти компоненты не указываются, чтобы сделать примеры более наглядными, но следует помнить, что включение правильных уточнителей никогда не приведет к ошибке. В следующих разделах этой главы более подробно анализируется синтаксис оператора выбора. В этой главе рассматриваются лишь некоторые конструкции и ключевые слова, составляющие оператор select. Конструкции group by, having (имеющие), order by и compute (вычислить) будут рассмотрены в третьей главе “Итоговые значения, Группировка и Сортировка Результатов Запроса”. Конструкция into (в) описывается в главе 7 “Создание Баз данных и Таблиц”. Конструкция at isolation (изоляция) будет описана в главе 17 “Транзакции: Сохранение Целостности Данных и Восстановление”. Ключевые слова holdlock, noholdlock и shared (которые связаны с блокировкой доступа в SQL Сервере) и ключевое слово index (индекс) описываются в Руководство по оптимизации и настройке SQL Сервера. Замечание: Конструкция for browse не рассматривается в данном руководстве. Она используется только в DB-Libraryä- приложениях. Детали этой конструкции описываются в руководстве Open Client DB-Library/C Reference Manual. Список выбора часто состоит из последовательности названий столбцов, разделенных запятыми, или из звездочки, указывающей на выбор по всем столбцам в порядке их следования в таблице. Однако, в этом списке могут находиться одно или несколько выражений, разделенных запятыми, которые могут быть константами, названиями столбцов, функциями, подзапросами, или любые их комбинации, соединенные между собой арифметическими или битовыми операциями и скобками. Общий синтаксис для списка выбора выглядит следующим образом: select
expression [, expression]... from table_list Если хотя бы одно название таблицы или название столбца не является допустимым идентификатором, то необходимо установить опцию set quoted_identifier и заключить это название в двойные кавычки. Звездочка (*) имеет особое значение в операторах select. Она указывает на выбор данных по всем столбцам во всех таблицах, указанных в предложении from. Звездочку следует использовать для экономии времени и уменьшения числа ошибок, когда необходимо просмотреть все столбцы в таблице. Оператор выбора в этом случае имеет следующий общий вид: select * from table_list Поскольку оператор select * выбирает данные из всех столбцов таблицы, то любые изменения в структуре таблицы, такие как добавление, удаление или переименования столбцов автоматически изменяют результаты оператора select *. Явное указание столбцов позволяет более точно контролировать результаты запросов. С помощью следующего оператора выбираются все столбцы таблицы publishers (издатели) и выводятся в том порядке, в каком они расположены в этой таблице. Поскольку здесь не указан ограничитель where (где), то в результат включается каждая строка таблицы. select * from publishers Результат может выглядеть следующим образом:
(Выбрано 3 строки) Такой же результат будет получен, если по порядку указать
названия всех столбцов после ключевого слова select: select pub_id,
pub_name, city, state from publishers В запросе можно использовать звездочку (*) несколько раз: select *,* from publishers В результате каждое название столбца и все данные в столбце будут выведены дважды. Как и название столбца, звездочка может дополняться названием таблицы, что показано в следующем запросе: select
publishers.* from
publishers Для выбора данных только из определенных столбцов таблицы, нужно использовать следующий синтаксис: select column_
name[, column_name]... from table_name Каждое название столбца должно быть отделено от предшествующего запятой. Порядок, в котором названия столбцов указываются в запросе, определяет порядок, в котором данные из этих столбцов будут выводиться в результате запроса. Следующие два примера показывают, как изменять порядок следования столбцов. В каждом из них выводятся наименования издателей и их идентификационные номера из трех строк таблицы publishers. В первом примере сначала выводится номер из столбца pub_id, а затем наименование из столбца pub_name, а во втором - наоборот. В обоих примерах по существу выводится одинаковая информация, но форма ее представления различна. select pub_id,
pub_name from
publishers
(Выбрано 3 строки) select
pub_name, pub_id from
publishers
(3 строки выведены) В процессе вывода результатов запроса по умолчанию предполагается, что заголовок каждого столбца совпадает с его названием в таблице. Однако это заголовок можно изменить, используя одно из следующих предложений: column_heading= column_name или column_name _column_heading или column_name _as_ column_heading Этой заменой целесообразно пользоваться, когда измененное название столбца является более удобным для чтения. Например, для изменения названия столбца pub_name на "Publishers" в предыдущем запросе можно воспользоваться одним из следующих операторов: select
Publisher = pub_name, pub_id from
publishers select
pub_name Publisher, pub_id from
publishers select
pub_name as Publisher, pub_id from publishers Результат будет выглядеть следующим образом:
(3 строки
выведены) В заголовке столбца можно использовать любые символы, даже пробелы, если его заключить в кавычки. При этом не обязательно включать опцию quoted_identifier. Если заголовок столбца не ограничен кавычками, то он должен подчиняться правилу, действующему для идентификаторов. Оба из следующих запросов: select "Publisher's Name" = pub_name from publishers и select pub_name "Publisher's Name" from publishers порождают одинаковый результат:
Кроме того, в заголовках столбцов можно также использовать зарезервированные слова языка Transact-SQL, заключенные в кавычки. Например, в следующем запросе зарезервированное слово sum используется как заголовок столбца: select
"sum" = sum(total_sales) from titles Длина заголовка столбца, заключенного в кавычки, не должны превышать 30 байтов. Замечание: Перед использованием
кавычек в названиях столбцов в операторах
create table, alter table, select into, create view необходимо включить опцию set quoted_identifier. Как было сказано выше, оператор select выбирает данные из таблиц, указанных в предложении from (из). Кроме этого, в результат запроса можно включать символьные строки. Для этого строку символов нужно заключить в апострофы или кавычки и отделить от других элементов списка выбора запятыми. Если в символьной строке встречается апостроф, то эту строку необходимо заключить в кавычки, иначе апостроф будет интерпретироваться как признак начала или конца строки. Ниже приведен пример запроса, перед результатами которого выводится символьная строка: select
"The publisher's name is", Publisher = pub_name from
publishers
(Выбраны 3 строки) Над числовыми данными из столбцов, указанных в операторе выбора, можно выполнять вычисления с использованием числовых констант. В приведенной
ниже таблице указаны арифметические операции, которые можно выполнять над
данными. Информацию о логических операциях над битами можно посмотреть в Справочном руководстве SQL Сервера.
Таблица 2-1: Арифметические операции Арифметические операции - сложение, вычитание, умножение и деление - выполняются над данными любого числового типа: int , smallint, tinyint, numeric, decimal, float, money. Операция взятия по модулю не может использоваться для данных типа money. Взятие по модулю это целочисленная операция, которая двум целым числам сопоставляет остаток от деления первого на второе. Например, 21%9 = 3, поскольку частное от деления 21 на 9 равняется 2, а остаток 3. Некоторые арифметические операции могут также выполняться над данными типа datetime (дата, время) с помощью функций, работающих с датами. Эти функции приводятся в главе 10 "Использование встроенных функций в запросах". Все вышеперечисленные операции могут использоваться в списке выбора в любой комбинации с названиями столбцов и числовыми константами. Например, чтобы увидеть увеличенные на 100 процентов объемы продаж книг из таблицы titles, достаточно выполнить следующий запрос: select
title_id, total_sales, total_sales * 2 from titles Результаты будут выглядеть следующим образом:
(Выбрано 18 строк) Следует обратить внимание на пустые значения (NULL) в столбце total_sales и вычисляемом столбце. Пустое значение не имеет точно определенной величины, поэтому выполнение любой арифметической операции над пустым значением приводит снова к пустому значению. Столбцу с вычисленным значением можно дать заголовок "proj_sale" (план продаж): select
title_id, total_sales, proj_sales = total_sales*2 from titles Можно также добавить символьные строки "Current sales=" и "Projected sales are" в оператор select. Столбец, из которого выбирались исходные значения, не обязательно включать в список выбора. Например, в приведенных примерах столбец total_sales показан только для сравнения его значений со значениями вычисленного столбца total_sales*2. Для того, чтобы увидеть только вычисленные значения, необходимо выполнить следующий запрос: select
title_id, total_sales*2 from titles Можно также
выполнять арифметические операции непосредственно над значениями данных в
указанных столбцах без использования констант. Например: select title_id, total_sales * price from titles
(Выбрано 18 строк) Наконец, исходные данные для вычисления можно выбирать из нескольких таблиц. В главах, где рассмотрены соединение таблиц и подзапросы, дается более полная инфомацию о том, как работать с запросами из нескольких таблиц, а здесь приведем лишь небольшой пример. Следующий запрос
вычисляет сумму, вырученную от продажи книги по психологии, по количеству
проданных экземпляров (столбец qty в
таблице salesdetail), и их цене
(столбец price из таблицы titles): select
salesdetail.title_id, stor_id, qty * price from titles,
salesdetail where
titles.title_id = salesdetail.title_id and
titles.title_id = "PS2106"
(Выбрано 3 строки) Если в арифметическом выражении присутствуют несколько операций, то первыми выполняются умножение, деление и взятие по модулю, а затем сложение и вычитание. Если все операции в арифметическом выражении имеют одинаковое старшинство, то они выполняются слева направо. Выражения, взятые в скобки, вычисляются в первую очередь. Например, в следующем операторе select общее количество проданных книг умножается на их цену для вычисления вырученной суммы, а затем из этой суммы вычитается половина авторского аванса. Первым вычисляется произведение чисел из столбцов total_sales и price, затем аванс делится пополам и результат деления вычитается из полученного произведения. select
title_id, total_sales * price - advance / 2 from titles Чтобы избежать недоразумений, следует использовать скобки. Следующий запрос порождает такой же результат как и в предыдущий, но выглядит более простым для понимания: select
title_id, (total_sales * price) - (advance / 2) from titles
(Выбрано 18 строк) Скобки можно использовать для изменения порядка выполнения операций; тогда первыми, будут выполняться действия в скобках. Если встречаются вложенные скобки, то выполнение начинается с самых внутренних скобок. В следующем примере с помощью скобок изменен порядок выполнения операций, для того чтобы вычитание выполнялось перед делением. select
title_id , (total_sales * price - advance) / 2 from titles
(Выбрано 18 строк) Когда в списке
выбора имеются текстовые (text) и
графические (image) данные, то
ограничение на длину выходных результатов зависит от значения глобальной
переменной @@textsize. Значение,
установленное по умолчанию для этой переменной, зависит от системных программ,
которые обеспечивают доступ к SQL-серверу, и для утилиты ISQL оно равно 32К.
Значение этой переменной можно изменять с помощью команды set (установить): set textsize 25 После этой установки в запросах будут выводиться только первые 25 байтов в столбцах с текстовыми данными. Замечание: Когда производится выбор графических данных, то в результат включаются символы "0х", которые указывают на то, что данные представлены в шестнадцатиричном виде. Эти два символа необходимо учитывать при установке значения глобальной переменной @@textsize. Для установки первоначального значения (32К) глобальной переменной @@textsize следует использовать оператор: set textsize 0 По умолчанию в результат включается полный текст, если его длина меньше чем значение переменной @@textsize. Более подробная информации о текстовых и графических типах данных дается в главе 6 "Использование и Создание Типов Данных". Команда readtext (читать текст) обеспечивает
другой способ выбора текстовых и графических значений. В этой команде в
качестве аргументов указываются название таблицы и столбца, текстовый
указатель, начальное смещение внутри текста и количество символов или байтов,
которые необходимо выбрать. В следующем примере выводятся 6 символов из столбца copy в таблице blurbs: declare @val
varbinary (16) select @val =
textptr(copy) from blurbs where au_id =
"648-92-1872" readtext
blurbs.copy @val 2 6 using chars В этом примере команда readtext выводит с 3 по 8 символы из столбца copy, поскольку смещение равно 2. Полный синтаксис команды readtext имеет следующий вид: readtext
[[database.]owner.]table_name.column_name
text_ptr offset size [holdlock] [using {bytes
I chars I characters}] [at isolation
{read uncommited I read commited I serializable Функция textptr (текстовый указатель) возвращает 16-байтовую двоичную строку. Необходимо объявить локальную переменную для текстового указателя, а затем использовать эту переменную в команде readtext. Флаг holdlock (защелка) фиксирует текстовое значение до окончания текущей транзакции. Другие пользователи могут только читать этот текст, не изменяя его. Конструкция at isolation описана в главе 17, "Транзакции: Сохранение Целостности Данных и Восстановление”. При использовании многобайтовых символов опция using позволяет уточнить в команде readtext как интерпретировать величину смещения в байтах или в символах. Для указания смещения в символах можно использвать как chars, так и characters. Эта опция игнорируется при использовании однобайтовых символов или графических значений (команда readtext читает графические данные только по байтам). Если опция using отсутствует, то по умолчанию смещение задается в байтах. SQL сервер должен определить количество байтов, которые нужно послать клиенту (пользователю) в ответ на команду readtext. Когда смещение и размер указаны в байтах, то определение количества возвращаемых байтов не представляет труда. Когда смещение и размер указаны в символах, то SQL-сервер должен выполнить дополнительный шаг для вычисления количества байтов выводимого для клиента текста. В результате, во втором случае запрос может выполняться медленнее, поэтому использование опции using characters полезно только тогда, когда SQL-сервер использует множество многобайтовых символов. Эта опция гарантирует, что команда readtext не выдаст только часть символов. Когда смещение указывается в байтах, SQL-сервер может встретить только часть кода символа в начале или в конце текста, который нужно вернуть. Если такая ситуация возникла, то перед выдачей текста пользователю сервер заменяет каждый неполный символ знаком вопроса. Команду readtext нельзя использовать для выбора текстовых и графических данных во вьюверах. Итак, список выбора может содержать звездочку * (выбор всех столбцов в том порядке, в каком они создавались), названия столбцов, перечисленных в любом порядке, символьные строки, заголовки столбцов и выражения, содержащие арифметические операции. Можно также включить сюда аггрегирующие функции, о которых упоминалось в этой главе в разделе о конструкции группировки (group by), и в главе 3 "Подведение итогов, Группировка и Сортировка Результатов Запроса". Приведем здесь еще несколько примеров операторов выбора, обращающихся к демонстрационной базе данных pubs2: 1. select
titles.* from titles 2. select Name
= au_fname, Surname = au_lname from authors 3. select
Sales = total_sales * price, ToAuthor = advance, ToPublisher = (total_sales * price) - advance from titles 4. select
'Social security #', au_id from authors 5. select
this_year = advance, next_year = advance + advance/10, third_year = advance/2, 'for book title #', title_id from titles 6.select
'Total income is', Revenue = price * total_sales, 'for', Book# = title_id from titles Необязательное ключевое слово distinct (различные) исключает повторяющиеся строки из результата выполнения оператора выбора. Если слово distinct не было указано, то в результат попадают все строки, включая повторяющиеся. Такой же результат получится, если в начале списка выбора указано ключевое слово all (все). Таким образом, по умолчанию в начале списка выбора подразумевается ключевое слово all. Например, если выбираются все идентификационные коды писателей из таблицы titleauthor без ключевого слова distinct , то в результат попадут следующие строки: select au_id from
titleauthor
(Выбрано 25 строк) Из этого результата видно, что некоторые строки повторяются. Дублирование можно исключить с помощью ключевого слова distinct и получить только различные номера: select
distinct au_id from
titleauthor
(Выбрано 19 строк) Замечание: Для совместимости с другими реализациями языка SQL, допускается использование ключевого слова all для явного указания на выбор всех строк. Однако, как правило, нет необходимости в использовании этого слова, поскольку выбор "всех строк" подразумевается по умолчанию. При указании слова distinct пустые значения (null) рассматриваются как повторяющиеся. Другими словами, если в операторе выбора указано слово distinct, то возвращается всегда не более одного пустого значения, независисмо от того сколько пустых значений встречается в базе данных. Конструкция from (из) необходима в каждом операторе select, выбирающем данные из таблиц или вьюверов. Она используется для перечисления всех таблиц и вьюверов, содержащих столбцы, указанные в списке выбора и в конструкции where (где). Если конструкция from содержит более одной таблицы или вьювера, то их названия разделяются запятыми. Максимальное количество таблиц и вьюверов, которое можно указать в запросе, равно 16. Это число включает таблицы, перечисленные в конструкции from, базовые таблицы, указанные в определении вьювера, все таблицы, упомянутые в подзапросах, а также все таблицы, которые выбираются по ограничениям ссылочной целостности. Синтаксис конструкции from выглядит следующим образом: select select
_list [from [[database.]owner.]{table_name I
view_name} [holdlock I noholdlock]
[shared] [,[[database.]owner.]{table_name I
view_name} [holdlock I noholdlock] [shared]]...
] Названия таблиц могут иметь длину от 1 до 30 байтов. В качестве первого символа можно использовать буквы, а также символы @, #, или _. Следующие символы могут быть цифрами, буквами,или символами: @, #, $, _,¥или £. Названия временных таблиц должны либо начинаться с символа # (номер), если они созданы вне базы данных tempbd, либо предваряться префиксом “tempbd..”. Если временная таблица создается вне базы tempbd, то ее название не должно превышать 13 байтов в длину, поскольку SQL-сервер добавляет к названиям временных таблиц внутренний числовой суффикс для того, чтобы это название было уникальным. Дополнительную информацию о названиях можно посмотреть в главе 7, ”Создание Баз Данных и Таблиц”. В конструкции from полное название для таблиц и вьюверов выглядит следующим образом: database.owner.table_name database.owner.view_name Полное название необходимо указывать только в том случае, когда может возникнуть неопределнность относительно названий. Для экономии времени можно указывать в списке выбора сокращенные (correlation) названия таблиц. Эти сокращенные названия должны быть также указаны в конструкции from после названия таблицы, наример: select
p.pub_id, p.pub_name from publishers p Во всех остальных конструкциях этого оператора, например в конструкции where, при обращениях к этой таблице должны использоваться сокращенные названия. Сокращенные названия не могут начинаться с цифры. Конструкция where в операторе select определяет критерий (условие) для отбора строк. Общий формат этой конструкции имеет следующий вид: select
select_list from table_list where search_conditions Условия отбора (search conditions) или ограничители, в
конструкции where включают: · операции сравнения (=, <, >, и т.д.) where advance * 2 > total _sales * price ·
Интервалы попадания (between и not between)
where total_sales between 4095
and 12000 · списки принадлежности (in ,not in) ·
where state in (“CA”, “IN”, “MD”) ·
вхождение подстрок (like и not like) where phone not like
“415%” · неопределенные значения (is null и is not null) where advance is null · комбинации вышеприведенных условий через логические операции (and,or) where advance < 5000 or total_sales between 2000 and 2500 Кроме того, ключевое слово where может вводить: · Условия соединения (см.главу 4:”Соединения: Выбор Данных из Нескольких Таблиц”) · Подзапросы (см.главу 5:”Подзапросы: Использование Запросов в Других Запросах”. Замечание: Единственной операцией отбора, которую можно использовать в конструкции where для текстовых данных (text), является операция вхождение подстрок like (или not like.) Полный список всех допустимых условий отбора можно посмотреть в разделах: “Допустимые Условия” и “Конструкция clause” в Руководстве пользователя по SQL-серверу. В языке Transact-SQL используются следующие операции сравнения:
Таблица 2-2: Операции
сравнения языка SQL Синтаксис этих операций выглядит следующим образом: where expression
comparison_operator expression где expression (выражение) может быть константой, названием столбца, функцией, подзапросом или любой их комбинацией, соединенных арифметическими или логическими операциями. При сравнении символьных данных оператор < означает меньше по лексикографическому (словарному) порядку, а > означает больше по этому же порядку. (Чтобы увидеть лексикографический порядок сортировки, используемый вашим SQL-сервером, необходимо использовать системную процедуру sp_helpsort). При сравнении пробелы в конце строк игнорируются. Например, “Dirk” (без пробела) означает то же самое, что и “Dirk “ (с пробелом). При сравнении дат < означает раньше, а > означает позже. Нужно заключать в апострофы и кавычки данные типа char, nchar, varchar, nvarchar, text, datetime. См. главу 8: ”Добавление, Изменение и Уничтожение Данных” об информации относительно данных типа datetime (дата, время). Ниже приведены примеры операторов select, использующих операции сравнения: select * from
titleauthor where
royaltyper < 50 select
authors.au_lname, authors.au_fname from authors whereau_lname
> ‘McBadden’ select au_id,
phone from authors where phone
!=‘415 658-9932’ select
title_id, newprice = price * $1.15 from
pubs2..titles where advance
> 5000 Операция not (не) означает логическое отрицание выражения. Следующие два запроса находят все книги по психологии и бизнесу, выплаченный аванс по которым не превышает $ 5 500. Однако, следует обратить внимание на различное расположение операции логического отрицания (not) и операции сравнения не больше (!>). select
title_id, type, advance from titles where (type =
“buseness” or type = “psychology”) and not
advance > 5500 select
title_id, type, advance from titles where (type =
“buseness” or type = “psychology”) and advance
!> 5500
title_id type advance
(Выбраны 6 строк) Ключевое слово between (между) можно использовать для задания интервала значений, в котором указываются нижняя и верхняя границы отсечения. Например, для выбора всех книг, объем продажи которых составил величину от 4 095 до 12 000 экземпляров включительно, следует выполнить запрос: select
title_id, total_sales from titles where
total_sales between 4095 and 12000
title_id
total_sales
(5 строк найдено) Следует заметить, что книги объем продаж которых точно равен 4095 также включены в результат. Если бы имелись книги с объемом продаж 12000, то они также были бы включены в результат запроса. Можно определить открытый интервал с исключенными концами с помощью операций меньше (<) и больше (>). Такой же запрос с исключением концов интервала приводит к следующему результату: select
title_id, total_sales from titles where
total_sales > 4095 and total_sales < 12000
title_id
total_sales
(Выбрана 1 строка) Операция not between (не между) отбирает все значения, которые не попадают в указанный интервал. Например, для нахождения всех книг, число продаж которых не попадает в интервал от 4095 до 12000, следует выполнить запрос: select
title_id, total_sales from titles where
total_sales not between 4095 and 12000
(Выбрано 11 строк) Вхождение в список ( in и not in)
Ключевое слово in (в) позволяет выбирать значения, которые входят в указанный список значений. Например, если не пользоваться операцией in, то для выбора всех писаталей, проживающих в Калифорнии, Индиане, или Мэриленде, можно выполнить следующий запрос: select
au_lname, state from authors where state =
‘CA’ or state = ‘IN’ or state = ‘MD’ Однако, с помощью ключевого слова in можно получить тот же результат более экономно. Элементы списка, следующие за ключевым словом in, должны быть разделены запятыми и заключены в скобки. select
au_lname, state from authors where state
in( ‘CA’, ‘IN’, ‘MD’) Результат этих запросов будет следующим:
(Выбрано 17 строк) Однако, может быть самым важным является использование
ключевого слова in во вложенных
запросах, известных также как подзапросы. Полное описание подзапросов можно
посмотреть в главе 5: ”Подзапросы: Использование Запросов Внутри Других
Запросов”. В следующем примере иллюстрируется использование ключевого слова in во вложенном запросе. Предположим, что надо узнать фамилии писателей, которые получили меньше 50 процентов общего гонорара за книгу, соавторами которой они являлись. Таблица authors (авторы) содержит имена и фамилии писателей , а таблица titleauthor дает информацию о гонорарах. Соединяя эти таблицы с помощью ключевого слова in, можно получить результат без указания этих таблиц в конструкции from. Следующий запрос можно проинтерпретировать следующим образом: в таблице titleauthor найти номера всех авторов в столбце au_id, которые получили менее 50 процентов гонорара за некоторую книгу. Затем нужно выбрать из таблицы authors имена и фамилии тех авторов, номера которых удовлетворяют предыдущему запросу к таблице titleauthor. В результате будут отобраны несколько писателей, попадающих в эту категорию. select
au_lname, au_fname from authors where au_id in
(select au_id from titleauthor where royaltyper <50)
(Выбрано 7 строк) Операция not in (не в) отбирает авторов, которые не попали в список. Следующий запрос находит писателей, которые получили не менее 50 процентов гонорара по крайней мере за одну книгу. select
au_lname, au_fname from authors where au_id
not in (select au_id from titleauthor where royaltyper <50)
Ключевое слово like (как)
используется для выбора данных, которые содержат указанную текстовую подстроку.
Эта операция используется с полями типа char,
nchar, varchar, nvarchar, binary, varbynary, text, и datetime . Данные в столбце (поле) сравниваются на “совпадение” с указанным шаблоном, который может содержать следующие специальные символы:
Таблица 2-3:
Специальные символы, используемые при сравнении строк Текстовые данные в столбце могут сравниваться с константами, переменными, или данными из других столбцов, содержащих указанные в таблице маскирующие (wildcard) символы. Когда используются константы, то сравниваемые строки заключаются в кавычки. Например, используя операцию like можно выполнить следующие действия с таблицей authors: · Операция like “Mc%” находит все фамилии, которые начинаются с приставки “Мс” (McBadden). · Операция like “%inger” находит все фамилии, которые заканчиваются суффиксом “inger” (Ringer,Stringer) · Операция like “%en%” находит все фамилии, содержащие подстроку ”en” (Bennet, Green, McBadden). · Операция like “_heryl” находит все имена из шести букв, которые заканчиваются на “heryl” (Cheryl). · Операция like “[CK]ars[eo]n” находит фамилии “Carsen”, “Karsen” “Carson”, и“Karson”(Carson). · Операция like “[M-Z]inger” находит все фамилии, заканчивающиеся на “inger” и начинающиеся с любой из букв от M до Z (Ringer). · Операция like “M[^c]%] находит все фамилии, которые начинаются с буквы ”M”, и не содержат вторую букву “c”. Следующий запрос выбирает из таблицы authors все номера телефонов, которые начинаются кодом 415: select phone from authors where phone
like “415%” Операцию not like (не как) можно использовать с теми же маскирующими символами. Например, чтобы найти все номера телефонов из таблицы authors, которые не начинаются с кода 415, можно выполнить один из следующих двух запросов: select phone from authors where phone
not like “415%” select phone from authors where not
phone like “415%” Операция like является
единственной операцией, которую можно использовать в условии отбора where (где) для текстовых полей. Следующий запрос находит все строки в
таблице blurbs, которые в столбце copy содержат подслово “computer”: select * from
blurbs where copy
like “%computer%” Маскирующие символы (символы замены) интерпретируются как
обычные символы, если они используются без операции like. В этом случае они в точности представляют свои литеральные
значения. В следующем запросе выбираются все номера телефонов, которые состоят
только из четырех символов “415%”. Здесь не будут выбираться номера телефонов,
которые начинаются с кода 415. select phone from authors where phone = “415%” Маскирующие символы можно использовать в строках как обычные символы (литеры), по которым происходит сравнение. Существуют два способа использования маскирующих символов как литер в операции like: с помощью квадратных скобок и конструкции escape (пропуск). Как уже отмечалось, шаблон поиска может содержать маскирующие символы и быть значением переменной или содержимым некоторого столбца. Более подробно об операции like и о маскирующих символах (включая информацию об использовании операции like для строк символов из мультибайтовых алфавитов и поиска подстрок независимо от регистра) рассказывается в Справочном Руководстве по SQL Серверу. Маскирующие символы, к которым относятся символы процентов, подчеркивания и открывающая квадратная скобка, следует заключать в квадратные скобки для того, чтобы они интерпретировались как обычные символы. Закрывающая квадратная скобка не требует для себя специальных скобок, поэтому ее можно использовать саму по себе. Для использования дефиса в качестве литеры, а не в качестве указателя интервала, следует указать его первым внутри квадратных скобок.
Таблица 2-4:
Использование квадратных скобок для маскирующих символов Конструкция escape (Стандарт SQL)
Конструкция escape (отмена) позволяет пропускать некоторые
символы в строке, задаваемой в операции like.
При пропуске символов действуют следующие правила:
Таблица 2-5:
Использование конструкции escape · Аргументом конструкции escape должна быть строка, состоящая из одного символа. Пропускаемым символом может быть любой символ из стандартного набора. Если будет указана строка, состоящая из более чем одного символа, то возникает ошибка SQLSTATE и SQL-сервер выдает сообщение об ошибке. Например, следующие конструкции escape вызывают сообщения об ошибке: like “%XX_%” escape “XX” like “%XX_%X_%” escape “XX” ·
Конструкция escape
действует только внутри операции like,
где она указана, и не действует на другие операции like, содержащиеся в том же операторе. · Единственными символами, которые можно указывать после пропускаемого символа, являются маскирующие символы (_, %, [, ], или [^]) или сам пропускающийся символ. Если пропускаемый символ появляется два раза, то второе его вхождение не игнорируется. Таким образом, если строка может содержать два подряд пропускаемых символа, то в шаблоне следует указать 4 этих символа подряд (см. 5-й пример в таблице 2-5). Если после пропускаемого символа указан символ другого типа, не относящийся к вышеуказанным, то возникает ошибка SQLSTATE и выдается сообщение об ошибке. Например, следующие конструкции escape вызывают сообщение об ошибке: like “P%X%%X” escape “X” like “%X%%Xd_%” escape “X” like “%?X%” escape “?” like “_e%&u%” escape “&” Конструкция escape сохраняет свое действие внутри квадратных скобок в отличие от маскирующих символов, таких как подчеркивание, знак процентов и открывающая квадратная скобка. Не рекомендуется использовать маскирующие символы в качестве пропускаемых по следующим причинам: · Если указывается подчеркивание (_) или знак процентов (%) в качестве пропускаемых символов, то они теряют свое специальное значение в операции like и пропускаются как обычные символы. · Если указывается открывающая или закрывающая квадратные скобки в качестве пропускаемых символов, то они теряют свое специальное значение в операции like, которое они имеют в языке TRANSACT-SQL. · Если указывается дефис (-) или [^] в качестве пропускаемых символов, то они теряют свое специальное значение , которое обычно приписывается им внутри квадратных скобок, и пропускаются как обычные символы. Концевые пробелы, указанные после знака “%” в операции like сводятся к одному концевому пробелу. Например, операция like “% ” (процент, сопровождаемый двумя пробелами) будет иметь положительный результат сравнения со всеми строками “Х “ (один пробел); “Х “ (два пробела); и вообще со сторокой, в которой указано любое число концевых пробелов. Маскирующие символы могут использоваться в названиях столбцов в таблице и в названиях столбцов в операции like. В демонстрационной базе данных pubs2 есть таблица, называемая special_discount, в которой указываются скидки при продаже отдельных видов книг.
В следующем запросе используется маскирующий символ в столбце id_type этой таблицы в конструкции where: select
title_id, discount, price, price - (price * discount/100) from
special_discount, titles where title_id
like id_type На этот запрос выдаются следующие результаты:
(Выбрано 12 строк) Это позволяет проводить сложный поиск подстрок без использования цепочки or (или) предложений. Когда вводятся или ищутся символьные данные и даты (типа char, nchar, varchar, nvarchar, datetime и smalldatetime), их нужно заключать в одинарные или двойные кавычки. Замечание: Если опция quoted_identifier (идентификатор в кавычках) включена, не следует использовать двойные кавычки для выделения символьных строк и дат, поскольку SQL-Сервер может принять их за идентификаторы. В этом случае следует использовать одинарные кавычки (апостроф). Есть два способа для включения знака кавычек в символьную строку. Первый способ заключается в использовании двух подряд расположенных кавычек. Например, если строка начинается с апострофа и необходимо ввести еще один апостроф внутри строки, то в этом месте следует ввести два подряд апострофа: ‘I don’’t
understand.’ Следующий пример иллюстрирует использование двойных кавычек: “He said, “”It
is not really confusing.””” Второй способ заключается в использовании внутри строки кавычек другого типа. Другими словами, внутри строки, заключенной в двойные кавычки, нужно использовать одинарные и наоборот. Здесь приведены несколько примеров: ‘George said,
“There must be a better way.”’ “Isn’t there a
better way?” ‘George asked,
“Isn’’t there a better way?”’ Чтобы продолжить строку, которая выходит за край строки экрана, можно вставить обратную косую наклонную черту (\) перед тем как перейти к следующей строке. Когда в таблице встречается значение NULL, то это означает, что данное значение еще не определено. Значение данных в этом столбце “неопределено” или “недоступно”. Значение NULL не означает нулевого значения (числовой величины) или “пробела” (символьное значение). Более того, неопределенное значение позволяет отличить нулевое значение в числовых столбцах и пробел в текстовых столбцах от отсутствия всякого значения в этих столбцах. Значение NULL можно указать как значение поля в тех столбцах, где допускается неопределенное значение. Это можно сделать оператором create table (создать таблицу) двумя способами: · Если не указано никакого значения данных, то SQL-сервер автоматически вставляет значение NULL. · Пользователь может явно набрать слово “NULL” или “null” без одинарных или двойных кавычек. Если слово “NULL” введено в текстовое поле в одинарных или двойных кавычках, то оно будет рассматриваться как строка, а не как неопределенное значение. При выводе результатов запроса неопределенные значения указываются словом NULL в соответствующих позициях. Например, в столбце advance таблицы titles допускаются неопределенные значения. Просмотрев этот столбец, можно сказать, была ли предусмотрена невыплата аванса по соглашению с автором ( см. нулевое значение в столбце advance в строке MC2222) или размер аванса не был известен в момент заполнения таблицы ( см. значение NULL в строке MC3026). select
title_id, type, advance from titles where pub_id =
“0877”
(Выбрано 7 строк) Transact-SQL интерпретирует неопределенные значения различным образом в зависимости от выполняемых операций и типов сравниваемых величин. Указанные ниже операторы возвращают следующие результаты при сравнении с неопределенным значением NULL: · = (равно) возвращает все строки, содержащие NULL. · != или <> (не равно) возвращает все строки, которые не содержат NULL. Однако, когда установлена опция ansinull для соответствия стандарту SQL, операции = и != не возвращают никаких результатов при сравнении с NULL. Независимо от этой опции операции <, <=, !<, >, >=, !> никогда не возвращают результатов при сравнении с неопределенным значением NULL. SQL-сервер может распознать неопределенное значение в столбце. Таким образом, равенство column1 = NULL может быть истинным. Однако, сравнение where column1 > NULL не имеет смысла, поскольку NULL означает “имеет неизвестную величину”. Нет никаких оснований предполагать, что две неопределенных величины одинаковы. Эти правила применимы также к сравнению данных из столбцов, указанных в конструкции where, при объединении двух таблиц. Если конструкция (предложение) имеет вид “where column1 = column2”, то строки содержащие неопределенные значения не попадут в результат. Неопределенные [определенные] значение можно выбирать из базы данных с помощью конструкции: where
column_name is [not] null Если попытаться найти неопределенное значение в столбце данных, имеющих тип NOT NULL, то SQL-сервер выдаст сообщение об ошибке. Некоторые строки в таблице titles могут содержать неопределенные значения. Например, при вводе информации о книге Psychology of Computer Cooking (Психология компьютерной кулинарии) указывается ее название, идентификационный номер книги, предполагаемый издатель. Но поскольку контракт с автором еще не заключен, то в столбцах price, advance, royalty, total_sales, notes сначала появятся неопределенные значения. Так как неопределенное значение не дает положительного результата сравнения ни с какой величиной, то в следующем запросе, выбирающем книги, по котороым был выплачен умеренный аванс (меньше $5 000), не появится вышеназванная книга по компьютерной кулинарии с номером MC3026. select
title_id, advance from titles where advance
< $5000
(Выбрано 5 строк) Ниже приведен запрос, выбирающий книги, за которые был выплачен аванс меньше $5 000 или имеющих неопределенное значение в столбце advance (аванс): select
title_id, advance from titles where advance
< $5000 or advance is null
(Выбрано 7 строк) В главе 7 “Создание баз`данных и таблиц” можно посмотреть
дополнительную информацию о неопределенном значении NULL в операторе create table (создание таблицы) и о
соотношении между неопределенным значением NULL и значениями по умолчанию. В
главе 8 “Добавление, Изменение и Удаление Данных” можно посмотреть
дополнительную информацию о вставке неопределенных значений в таблицу. См.
также раздел “Неопределенные Значения” в Справочном
Руководстве по SQL-серверу. Логические операции and (и), or (или) и not (не) используются для составления сложных условий отбора в конструкции where (где). Операция and (и) соединяет два или больше условий в одно составное условие, которое является истинным, когда все входящие в него условия являются истинными. Например, в следующем запросе выбираются все строки, в которых в столбце фамилий авторов встречается фамилия Ringer, а в столбце имен встречается имя Anne. Строка с именем и фамилией Albert Ringer разумеется не появится в результате этого запроса. select * from authors where au_lname
= ‘Ringer’ and au_fname = ‘Anne’ Операция or (или)
соединяет два или больше условий в одно составное условие, которое является
истинным, когда, по крайней мере одно,
из этих условий является истинным. В следующем запросе выбираются строки,
содержащие имена Anne или Ann в столбце au_fname. select * from authors where au_fname
= ‘Anne’ or au_fname = ‘Ann’ Операция not (не) отрицает условие, которое следует за ней. В следующем запросе выбираются все авторы, которые не живут в штате Калифорния: select * from
authors where not
state = ‘CA’ Арифметические и битовые (bitwice) операции выполняются
перед логическими операциями. Если в операторе имеется несколько логических
операций, то сначала выполняется отрицание (not), затем конъюнкция (and) и,
наконец, дизъюнкция (or). Информацию о битовых операциях можно получить в Справочном руководстве SQL сервера. Например, в следующем запросе выбираются все книги по бизнесу из таблицы titles независимо от выплаченного аванса и все книги по психологии, по которым был выплачен аванс, больший чем $ ,500. Условие на аванс относится только к книгам по психологии, поскольку операция and будет выполняться перед операцией or. select
title_id, type, advance from titles where type =
“business” or type = “psychology” and advance > 5500
(Выбрано 6 строк) Можно изменить смысл этого запроса, добавив скобки в логическое выражение, чтобы выполнить сначала операцию or. В этом случае будут выбираться все книги по бизнесу и психологии, по которым был выплачен аванс больший чем 5 500 долларов: select
title_id, type, advance from titles where (type =
“business” or type = “psychology”) and advance > 5500
(Выбрано 3
строки)
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Дизайн: Piton Alien
|