Structured Query Language (SQL) is a database query and programming language used to access data, and query, update, and manage relational database systems.
Using the Province_R dataset in sample data PopulationAndEconomy workspace as an example, this document explains how to build common SQL query expressions.
Numeric Queries
Common operators for numeric queries include "=", "<>", ">", "<", "<=", ">=", and "Between...and...".
Example: Province_R.Pop_2014 Between 5000 and 10000
Queries objects with 2014 population (Pop_2014 field value) between 50 million and 100 million.
Note:When querying double-precision numeric values, avoid directly using "=" operator due to precision issues that may prevent results from being returned.
String Queries
Use the "like" keyword to perform the following three types of string queries.
- Partial match with "%" wildcard:
Example: Province_R.Name like "山%" or Province_R.Name like '山%'
Queries objects whose Name field values start with "山" in the Province_R dataset.
- Exact match. Use single quotes for database datasources; both single and double quotes work for UDB/UDBX datasources:
Example: Province_R.Name like "北京" or Province_R.Name like '北京'
Queries objects with Name field value exactly matching "北京" in the Province_R dataset.
- Single-character match with "_" wildcard:
Example: Province_R.Name like '河__'
Queries objects whose Name field values start with "河" followed by exactly two characters in the Province_R dataset.
Specific Value Queries
Use the "in" keyword to query any value within a specified list.
- Example 1: Province_R.Name in ("北京市","上海市")
Queries objects with Name field values as "北京市" or "上海市" in the Province_R dataset.
- Example 2: Province_R.ColorID in (1,4)
Queries objects with ColorID field values as 1 or 4 in the Province_R dataset.
Note:For character-type fields, enclose query values in single or double quotes. Only single quotes can be used in database datasources.
Null Value Queries
Use "is NULL" keyword to query records with null values.
Example: Province_R.Name Is NULL
Queries objects with null values in the Name field of the Province_R dataset.
Compound Expressions
Combine conditional expressions using "and", "or", and "not" keywords.
Example: Province_R.GDP_2014 > 10000 and Province_R.IncomeLevel > 20000
Queries objects with 2014 GDP exceeding 1 trillion and per capita income exceeding 20,000.
Comparison Operators on Character Fields
Comparison operators like ">", "<", ">=", "<=". For database datasources, use single quotes for character field values.
Example: Province_R.Name >= "贵州省"
Queries objects whose Name field values start with letters from G to Z in the Province_R dataset.
Date Queries
Create a date-type field DataDate in the Province_R dataset to store statistical timestamps. Below are expressions for querying records where DataDate is later than 2019-08-10 across different datasources:
- SQL(SQL+) datasource: Province_R.DataDate > '2019-08-10 12:25:00'
- ORACLE datasource: Province_R.DataDate > TO_DATE('2019-08-10','YYYY-MM-DD')
- PostGIS datasource: "Province_R".datadate > '2019-08-10 12:25:00'
- PostgreSQL datasource: Province_R.datadate > '2019-08-10 0:0:0'
- UDB/UDBX datasource: "Province_R".datadate > '2019-08-10' (Date format: YYYY-MM-DD)
Boolean Value Queries
Boolean field values are True or False. Use 1 for True and 0 for False in SQL queries.
Example: Province_R.Coastal = 1
Queries objects where Coastal field value is True in the Province_R dataset. Coastal is a boolean-type field.
Derived Fields
Example: Province_R.SmArea / Province_R.SmArea
The query result table will display temporary fields storing search results. Use "as" to rename fields: Province_R.SmArea / Province_R.SmArea as Pop_Density, where "Pop_Density" is the temporary field.
If no alias is specified, the system uses the expression itself as the temporary field name.
Separate multiple derived field expressions with commas.
Related Topics
Constructing SQL Expression Queries