Основы SQL для выражений запроса, применяемых в ArcGIS
В этом разделе описываются элементы обычных запросов, используемых в выражениях выборки в ArcGIS. Выражения запросов в ArcGIS используют общепринятый синтаксис SQL.
Внимание:Синтаксис SQL не работает при вычислении полей при помощи Калькулятора поля (Field Calculator).
Поля
Чтобы указать поле в выражении SQL, поставьте разделитель, в противном случае имя поля может быть истолковано двусмысленно, например если оно такое же, как резервное ключевое слово SQL.
Так как существует немало резервных ключевых слов, а новые слова могут появляться с каждой последующей версией, рекомендуется всегда заключать имена полей в разделители.
Разделители имени поля зависят от СУБД. При запросе к любым файловым данным – файловой базе геоданных, данным из базы геоданных ArcSDE, данным из класса пространственных объектов ArcIMS или подслоёв сервиса изображений, вы можете заключать имена полей в двойные кавычки:
"AREA"
При запросе к данным персональной базы геоданных, поля заключаются в квадратные скобки:
[AREA]
Для наборов растровых данных в персональной базе геоданных имена полей заключаются в двойные кавычки:
"AREA"
Для данных файловой базы геоданных вы можете заключить имена полей в двойные кавычки, но обычно это не требуется.
AREA
Строки
Строковые значения всегда заключаются в выражениях в одинарные кавычки. Например:
STATE_NAME = 'California'
- Строки в выражениях чувствительны к регистру. Для классов пространственных объектов и таблиц вы можете использовать функцию UPPER или LOWER для установки регистра при выборке. Например:
UPPER(STATE_NAME) = 'RHODE ISLAND'
- Строковые значения нечувствительны к регистру в классах пространственных объектов и таблицах персональных баз геоданных. Если необходимо, можете воспользоваться функциями UCASE и LCASE, которые эквивалентны UPPER и LOWER.
- Если строка содержит одинарную кавычку, вам в первую очередь потребуется использовать другую одинарную кавычку как символ экранирования. Например:
NAME = 'Alfie''s Trough'
Используемые вами групповые символы для выполнения поиска по части строки также зависят от того, к какому источнику данных выполняется запрос. Например в файловом источнике данных или базе геоданных ArcSDE, для поиска Mississippi и Missouri в названиях штатов США используется выражение:
STATE_NAME LIKE 'Miss%'
Символ процента (%) означает, что на этом месте может быть что угодно – один символ или сотня, или ни одного. Групповые символы, используемые при запросах к персональным базам геоданных:звездочка (*) для любого количества символов, и вопросительный знак (?) для одного символа.
Строковые функции могут использоваться для форматирования строк. Например функция LEFT вернет определенное количество символов начиная с левого края строки. Данный запрос вернет все штаты, начинающиеся на букву A:
LEFT(STATE_NAME,1) = 'A'
Список поддерживаемых функций вы найдете в документации по своей СУБД.
Числа
Точка (.) всегда используется в качестве десятичного разделителя, независимо от региональных настроек. В выражениях в качестве разделителя десятичных знаков нельзя использовать запятую.
Вы можете запрашивать цифровые значения, используя операторы равно (=), не равно (<>), больше (>), меньше (<), больше или равно (>=) и меньше или равно (<=), а также BETWEEN (между). Например:
POPULATION >= 5000
Числовые функции можно использовать для форматирования чисел. Например функция ROUND округлит до заданного количества десятичных знаков данные в файловой базе геоданных:
ROUND(SQKM,0) = 500
Список поддерживаемых числовых функций вы найдете в документации по своей СУБД.
Даты и время
Общие правила
В таких источниках данных, как база геоданных, даты хранятся в полях даты–времени. А в покрытиях ArcInfo и шейп-файлах этого нет.
Поэтому большинство из примеров синтаксиса запроса, представленных ниже, содержит ссылки на время. В некоторых случаях часть запроса, касающаяся времени, может быть без всякого вреда пропущена, когда известно, что поле содержит только даты; в других случаях её необходимо указывать, или запрос вернет синтаксическую ошибку.
Основное назначение формата даты в ArcMap – хранение дат, а не времени. Можно хранить в поле только время, когда связанная база данных на самом деле использует формат дата–время, но это не рекомендуется. Запросы по времени будут несколько громоздкими, например 12:30:05 p.m. будет храниться как '1899-12-30 12:30:05'.
Примечание:Даты хранятся в исходной базе данных относительно 30 декабря 1899 года, 00:00:00. Это действительно для всех источников данных, перечисленных здесь.
Цель этого подраздела – помочь вам в построении запросов по датам, но не по значениям времени. Когда со значением даты хранится не нулевое значение (например January 12, 1999, 04:00:00), то запрос по дате не возвратит данную запись, поскольку если вы задаете в запросе только дату для поля в формате дата–время, недостающие поля времени заполняются нулями, и выбраны будут только записи, время которых соответствует 12:00:00 полуночи.
Таблица атрибутов отображает дату и время в удобном для пользователя формате, согласно вашим региональным установкам, а не в формате исходной базы данных. В большинстве случаев это хорошо, но также имеются и некоторые недостатки такого способа отображения:
- Строка, отображаемая в SQL-запросе, может иметь только небольшое сходство со значением, показанным в таблице, особенно когда в нее входит время. Например время, введенное как 00:00:15, будет отображаться в атрибутивной таблице как 12:00:15 AM с региональными настройками Соединённых Штатов, а сопоставимый синтаксис запроса должен быть Datefield= '1899-12-30 00:00:15'.
- Атрибутивная таблица не имеет сведений об исходных данных, пока вы не сохраните изменения. Она сначала попытается отформатировать значения для соответствия её собственному формату, затем, поверх сохраненных изменений, она попытается подогнать получившиеся результаты для соответствия базе данных. По этой причине, вы можете вводить время в шейп-файл, но обнаружите, что оно удаляется при сохранении ваших изменений. Поле будет содержать значение '1899-12-30', которое будет отображаться как 12:00:00 AM или как-то подобно, в зависимости от ваших региональных настроек.
Синтаксис даты-времени для баз геоданных ArcSDE
Informix
Datefield = 'yyyy-mm-dd hh:mm:ss'
Часть запроса hh:mm:ss не может быть опущена, даже если она равна 00:00:00.
Oracle
Datefield = date 'yyyy-mm-dd'
Имейте в виду, что здесь записи, где время не равно нулю, не будут возвращены.
Альтернативный формат при запросах к датам в Oracle следующий:
Datefield = TO_DATE('yyyy-mm-dd hh:mm:ss','YYYY-MM-DD HH24:MI:SS')Второй параметр 'YYYY-MM-DD HH24:MI:SS' описывает используемый при запросах формат. Актуальный запрос будет выглядеть так:
Datefield = TO_DATE('2003-01-08 14:35:00','YYYY-MM-DD HH24:MI:SS')Вы можете использовать более короткую версию:
TO_DATE('2003-11-18','YYYY-MM-DD')И снова записи, где время не равно нулю, не будут возвращены.
SQL Server
Datefield = 'yyyy-mm-dd hh:mm:ss'
Часть запроса hh:mm:ss может быть опущена, когда в записях не установлено время.
Альтернативный формат следующий:
Datefield = 'mm/dd/yyyy'
IBM DB2
Datefield = TO_DATE('yyyy-mm-dd hh:mm:ss','YYYY-MM-DD HH24:MI:SS')Часть запроса hh:mm:ss не может быть опущена, даже если время равно 00:00:00.
PostgreSQL
Datefield = TIMESTAMP 'YYYY-MM-DD HH24:MI:SS' Datefield = TIMESTAMP 'YYYY-MM-DD'
Вы должны указать полностью временную метку при использовании запросов типа "равно", в противном случае не будет возвращено никаких записей. Вы можете успешно делать запросы со следующими выражениями, если запрашиваемая таблица содержит записи дат с точными временными метками (2007-05-29 00:00:00 или 2007-05-29 12:14:25):
select * from table where date = '2007-05-29 00:00:00';
или
select * from table where date = '2007-05-29 12:14:25';
При использовании других операторов, таких как больше, меньше, больше или равно, или меньше или равно, вам не надо указывать время, хотя и можно, для точности. Оба эти выражения сработают:
select * from table where date < '2007-05-29';
select * from table where date < '2007-05-29 12:14:25';
Файловые базы геоданных, шейп-файлы, покрытия и прочие файловые источники данных
Перед датами в файловых базах геоданных, шейп-файлах и покрытиях должно стоять date.
"Datefield" = date 'yyyy-mm-dd'
Файловые базы геоданных поддерживают использование времени в поле даты, поэтому его можно добавить в выражение:
"Datefield" = date 'yyyy-mm-dd hh:mm:ss'
Шейп-файлы и покрытия не поддерживают использование времени в поле даты.
Примечание:SQL, используемый в файловой базе геоданных, базируется на стандарте SQL-92.
Персональная база геоданных
Даты в персональных базах геоданных отделены при помощи знака решетки (#).
Например:
[Datefield] = #mm-dd-yyyy hh:mm:ss#
Это может быть сокращено до [Datefield] = #mm-dd-yyyy#.
Альтернативный формат
[Datefield] = #yyyy/mm/dd#
Известные ограничения
Построение запросов к датам, находящимся в левой части (первой таблице) соединения, работает только для файловых источников данных, таких как файловые базы геоданных, шейп-файлы и таблицы DBF. Однако, возможен обходной путь при работе с другими, не файловыми, источниками, такими как персональная база геоданных и данные ArcSDE, как описано ниже.
Запрос к датам левой части соединения будет выполнен успешно, если использовать ограниченную версию SQL, разработанную для файловых источников данных. Если вы не используете такой источник данных, можете перевести выражение для использования этого формата. Нужно обеспечить, чтобы выражение запроса включало поля из более чем одной присоединенной таблицы. Например если соединены класс пространственных объектов и таблица (FC1 и Table1), и оба они из персональной базы геоданных, следующее выражение или вернет данные, или не выполнится:
FC1.date = date #01/12/2001# FC1.date = date '01/12/2001'
Чтобы запрос был выполнен успешно, можно создать вот такой запрос:
FC1.date = date '01/12/2001' and Table1.OBJECTID > 0
Так как запрос включает поля из обеих таблиц, будет использована ограниченная версия SQL. В этом выражении Table1.OBJECTID всегда > 0 для записей, которые сопоставлены в процессе создания соединения, поэтому это выражение всегда верно для всех строк, содержащих сопоставления соединения.
Чтобы быть уверенным, что каждая запись с FC1.date = date '01/12/2001' выбрана, используйте следующий запрос:
FC1.date = date '01/12/2001' and (Table1.OBJECTID IS NOT NULL OR Table1.OBJECTID IS NULL)
Такой запрос будет выбирать все записи с FC1.date = date '01/12/2001', независимо от того, есть ли сопоставление при соединении для каждой отдельной записи.
Подзапросы
Примечание:Покрытия, шейп-файлы и прочие файловые источники данных, не относящиеся к базам геоданных, не поддерживают подзапросы. Подзапросы, выполняемые на версионных классах ArcSDE и таблицах не возвращают объекты, которые хранятся в дельта-таблицах. Файловые базы геоданных предоставляют ограниченную поддержку подзапросов, описанных в данном разделе, а базы геоданных персональные и ArcSDE предоставляют полную поддержку. Информацию обо всех возможностях подзапросов к базам геоданных персональным и ArcSDE ищите в документации по своей СУБД.
Подзапрос – это запрос, вложенный в другой запрос. Подзапросы могут использоваться в SQL-выражении для применения предикативных или агрегирующих функций, или для сравнения данных со значениями, хранящимися в другой таблице и т.п. Это может быть сделано с помощью ключевых слов IN или ANY. Например этот запрос выберет только те страны, которых нет в таблице indep_countries:
"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)
Этот запрос возвратит объекты, где GDP2006 больше, чем GDP2005 любых объектов, содержащихся в countries (странах):
"GDP2006" > (SELECT MAX("GDP2005") FROM countries)Для каждой записи в таблице, подзапросу может понадобиться проанализировать все данные целевой таблицы. Это может очень сильно замедлить выполнение запроса при больших объёмах данных.
Поддержка подзапросов в файловых базах геоданных ограничена следующим:
- IN предикат. Например:
"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)
- Скалярные подзапросы с операторами сравнения. Скалярный подзапрос возвращает одно значение. Например:
"GDP2006" > (SELECT MAX("GDP2005") FROM countries)Для файловых баз геоданных, набор функций AVG, COUNT, MIN, MAX и SUM может использоваться лишь в скалярных подзапросах. - EXISTS предикат. Например:
EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico')
Операторы
Ниже приведен полный список операторов, поддерживаемых файловыми базами геоданных, шейп-файлами, покрытиями и прочими файловыми источниками данных. Они также поддерживаются в персональных и ArcSDE базах геоданных, хотя для этих источников данных может требоваться иной синтаксис. Кроме нижеперечисленных операторов, персональные и ArcSDE базы геоданных поддерживают дополнительные возможности. Более подробную информацию см. в документации по своей СУБД.
Арифметические операторы
Для сложения, вычитания, умножения и деления числовых значений можно использовать арифметические операторы.
|
Оператор |
Описание |
|---|---|
|
* |
Арифметический оператор умножения |
|
/ |
Арифметический оператор деления |
|
+ |
Арифметический оператор сложения |
|
- |
Арифметический оператор вычитания |
Операторы сравнения
Операторы сравнения используются для сравнения одного выражения с другим.
|
Оператор |
Описание |
|---|---|
|
< |
Меньше . Может использоваться со строками (сравнение основывается на алфавитном порядке) и для числовых вычислений, а также дат. |
|
<= |
Меньше или равно. Может использоваться со строками (сравнение основывается на алфавитном порядке) и для числовых вычислений, а также дат. |
|
<> |
Не равно . Может использоваться со строками (сравнение основывается на алфавитном порядке) и для числовых вычислений, а также дат. |
|
> |
Больше . Может использоваться со строками (сравнение основывается на алфавитном порядке) и для числовых вычислений, а также дат. |
|
>= |
Больше или равно. Может использоваться со строками (сравнение основывается на алфавитном порядке) и для числовых вычислений, а также дат. Например это запрос возвратит все города, названия которых начинаются на буквы от M до Z: "CITY_NAME" >= 'M' |
|
[NOT] BETWEEN x AND y |
Выбирает запись, если её значение больше или равно x и меньше или равно y. Если впереди стоит отрицание NOT, выбирает записи, значения которых находятся за пределами указанного диапазона. Например это выражение выбирает все записи со значениями, которые больше или равны 1 и меньше или равны 10: "OBJECTID" BETWEEN 1 AND 10Вот эквивалент этого выражения: "OBJECTID" >= 1 AND OBJECTID <= 10Однако, выражение с оператором BETWEEN обрабатывается быстрее, если у вас поле проиндексировано. |
|
[NOT] EXISTS |
Возвращает TRUE (истинно), если подзапрос возвращает хотя бы одну запись; в противном случае возвращает FALSE (ложно). Например, данное выражение вернет TRUE, если поле OJBECTID содержит значение 50: EXISTS (SELECT * FROM parcels WHERE "OBJECTID" = 50)EXISTS поддерживается только в файловых, персональных и ArcSDE базах геоданных. |
|
[NOT] IN |
Выбирает запись, если она содержит одну из нескольких строк или значений в поле. Если впереди стоит NOT, выбирает запись, где нет таких строк или значений. Например, это выражение будет искать четыре разных названия штатов: "STATE_NAME" IN ('Alabama', 'Alaska', 'California', 'Florida') Для файловых, персональных и ArcSDE баз геоданных этот оператор также может применяться в подзапросе: "STATE_NAME" IN (SELECT "STATE_NAME" FROM states WHERE "POP" > 5000000) |
|
IS [NOT] NULL |
Выбирает запись, если там в определенном поле есть нулевое значение. Если перед NULL стоит NOT, выбирает запись, где в определенном поле есть какое-то значение. Например данное выражение выбирает все записи с отсутствующим значением численности населения: "POPULATION" IS NULL |
|
x [NOT] LIKE y [ESCAPE 'escape-character'] |
Используйте оператор LIKE (вместо оператора = ) с групповыми символами, если хотите построить запрос по части строки. В этом примере из названий штатов США запрос выберет Mississippi и Missouri: "STATE_NAME" LIKE 'Miss%'Символ процента (%) означает, что на этом месте может быть что угодно – один символ или сотня, или ни одного. Если вы хотите использовать групповой символ, обозначающий один любой символ, используйте символ подчёркивания (_). Следующий пример показывает выражение для выбора имен Catherine Smith и Katherine Smith: "OWNER_NAME" LIKE '_atherine Smith'Групповые символы (%) и(_) работают для любых данных на основе файлов или в многопользовательских базах геоданных. LIKE работает с данными символов с обеих сторон выражения. Если вам нужен доступ к несимвольным данным, используйте функцию CAST. Например, этот запрос возвращает числа, начинающиеся на 8, из целочисленного поля SCORE_INT: CAST ("SCORE_INT" AS VARCHAR) LIKE '8%' Для включения символа (%) или (_) в вашу строку поиска, используйте ключевое слово ESCAPE для указания другого символа вместо escape, который в свою очередь обозначает настоящий знак процента или подчёркивания. Например данное выражение возвращает все строки, содержащие 10%, такие как 10% DISCOUNT или A10%: "AMOUNT" LIKE '%10$%%' ESCAPE '$'Групповые символы, используемые при запросах к персональным базам геоданных:звездочка (*) для любого количества символов, и вопросительный знак (?) для одного символа. Знак решётки (#) также используется в качестве группового для замены одного символа (числовое значение). Например, данный запрос возвращает земельные участки с номерами A1, A2 и т.д. из персональной базы геоданных: [PARCEL_NUMBER] LIKE 'A#' |
Логические операторы
|
Оператор |
Описание |
|---|---|
|
AND |
Соединяет вместе два условия и выбирает запись, в которой оба условия являются истинными. Например, выполнение следующего запроса выберет все дома с площадью более 1 500 квадратных футов и гаражом на две и более машины: "AREA" > 1500 AND "GARAGE" > 2 |
|
OR |
Соединяет вместе два условия и выбирает запись, где истинно хотя бы одно условие. Например выполнение следующего запроса выберет все дома с площадью более 1,500 квадратных футов или гаражом на две и более машины: "AREA" > 1500 OR "GARAGE" > 2 |
|
NOT |
Выбирает записи, не соответствующие указанному выражению. Например это выражение выберет все штаты, кроме Калифорнии (California): NOT "STATE_NAME" = 'California' |
Операторы строковой операции
|
Оператор |
Описание |
|---|---|
|| |
Возвращает символьную строку, являющуюся результатом конкатенации двух или более строковых выражений. FIRST_NAME || MIDDLE_NAME || LAST_NAME |
Функции
Ниже приведен полный список функций, поддерживаемых файловыми базами геоданных, шейп-файлами, покрытиями и прочими файловыми источниками данных. Они также поддерживаются в персональных базах геоданных и базах геоданных ArcSDE, хотя в этих источниках данных может использоваться иной синтаксис или имена функций. Помимо этих функций, персональные базы геоданных и базы геоданных ArcSDE поддерживают дополнительные возможности. Более подробную информацию см. в документации по своей СУБД.
Функции дат
|
Функция |
Описание |
|---|---|
|
CURRENT_DATE |
Возвращает текущую дату. |
|
EXTRACT (extract_field FROM extract_source) |
Возвращает extract_fieldчасть из extract_source. Аргумент extract_source является выражением даты–времени. Аргументом extract_field может быть одно из следующих ключевых слов: YEAR (ГОД), MONTH (МЕСЯЦ), DAY (ДЕНЬ), HOUR (ЧАС), MINUTE (МИНУТА) или SECOND (СЕКУНДА). |
CURRENT TIME | Возвращает текущую дату. |
Строковые функции
Аргументы, указанные как string_exp, могут быть названиями столбцов, символьными строковыми постоянными или результатом другой скалярной функции, где исходные данные представлены символьным типом.
Аргументы, указанные как character_exp, являются символьными строками переменной длины.
Аргументы, указанные как start или length могут быть числовыми постоянными или результатами других скалярных функций, где исходные данные представлены числовым типом.
Строковые функции, перечисленные здесь, базируются на 1; то есть, первым символом в строке является символ 1.
|
Функция |
Описание |
|---|---|
|
CHAR_LENGTH(string_exp) |
Возвращает длину строкового выражения в символах. |
|
LOWER(string_exp) |
Возвращает строку, идентичную string_exp, в которой все символы верхнего регистра изменены на символы нижнего регистра. |
|
POSITION(character_exp IN character_exp) |
Возвращает место первого символьного выражения во втором символьном выражении. Результат – число с точностью, определяемой реализацией и коэффициентом кратности 0. |
|
SUBSTRING(string_exp FROM start FOR length) |
Возвращает символьную строку, извлекаемую из string_exp, начинающуюся с символа, номер которого определяется аргументом start, а длина строки составляет столько символов, сколько указано в аргументе length . |
|
TRIM(BOTH | LEADING | TRAILING trim_character FROM string_exp) |
Возвращает строку string_exp укороченную на количество символов, указанное в аргументе trim_character, с начала, с конца или с обоих концов строки. |
|
UPPER(string_exp) |
Возвращает строку, идентичную string_exp, в которой все символы нижнего регистра изменены на символы верхнего регистра. |
Числовые функции
Все числовые функции возвращают числовые значения.
Аргументы, обозначенные numeric_exp, float_exp или integer_exp могут быть именем столбца, результатом другой скалярной функции или числовой константой, где исходные данные могут быть представлены числовым типом.
|
Функция |
Описание |
|---|---|
|
ABS(numeric_exp) |
Возвращает абсолютное значение numeric_exp. |
|
ACOS(float_exp) |
Возвращает угол в радианах, равный арккосинусу float_exp. |
|
ASIN(float_exp) |
Возвращает угол в радианах, равный арксинусу float_exp. |
|
ATAN(float_exp) |
Возвращает угол в радианах, равный арктангенсу float_exp. |
|
CEILING(numeric_exp) |
Возвращает наименьшее целое значение, большее или равное numeric_exp. |
|
COS(float_exp) |
Возвращает косинус float_exp, где float_exp как угол, выраженный в радианах. |
|
FLOOR(numeric_exp) |
Возвращает наибольшее целое значение, меньшее или равное numeric_exp. |
|
LOG(float_exp) |
Возвращает натуральный логарифм float_exp. |
|
LOG10(float_exp) |
Возвращает логарифм по основанию 10 float_exp. |
|
MOD(integer_exp1, integer_exp2) |
Возвращает остаток от деления integer_exp1 на integer_exp2. |
|
POWER(numeric_exp, integer_exp) |
Возвращает значение numeric_exp в степени integer_exp. |
|
ROUND(numeric_exp, integer_exp) |
Возвращает numeric_exp, округленное до integer_exp знаков справа от десятичной точки. Если integer_exp отрицательное, numeric_exp округляется до |integer_exp| знаков слева от десятичной запятой. |
|
SIGN(numeric_exp) |
Возвращает указатель знака numeric_exp. Если numeric_exp меньше нуля, возвращается –1. Если numeric_exp равно нулю, возвращается 0. Если numeric_exp больше нуля, возвращается 1. |
|
SIN(float_exp) |
Возвращает синус float_exp, где float_exp – угол, выраженный в радианах. |
|
TAN(float_exp) |
Возвращает тангенс float_exp, где float_exp – угол в радианах. |
|
TRUNCATE(numeric_exp, integer_exp) |
Возвращает numeric_exp , усеченное до integer_exp знаков справа от десятичной запятой. Если integer_exp является отрицательным, numeric_exp усекается до |integer_exp| знаков слева от десятичной точки. |
Функция CAST
Функция CAST конвертирует значение в определенный тип данных. Синтаксис выглядит так:
CAST(exp AS data_type)
Аргумент exp может быть названием столбца, результатом другой скалярной функции или буквенным. Data_type может быть любым и указываться строчными или заглавными буквами: CHAR, VARCHAR, INTEGER, SMALLINT, REAL, DOUBLE, DATE, TIME, DATETIME, NUMERIC, или DECIMAL.
Более подробно о функции CAST см. CAST and CONVERT.