Difference between revisions of "SQL queries"
m (moved SQL Queries to SQL queries) |
|||
Line 1: | Line 1: | ||
− | A SQL query is composed normally by clausule SELECT and optionally FROM, WHERE and ORDER BY | + | {{TOCright}} __TOC__ |
+ | |||
+ | SQL, often referred to as Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra. | ||
+ | |||
+ | A SQL query is composed normally by clausule SELECT and optionally FROM, WHERE and ORDER BY. | ||
== SELECT == | == SELECT == | ||
Used to specigy a property list ( columns ) by the name it'll apppears in results. The character * indicates all properties are selected. | Used to specigy a property list ( columns ) by the name it'll apppears in results. The character * indicates all properties are selected. | ||
− | |||
== FROM == | == FROM == | ||
Line 10: | Line 13: | ||
'''selecting all keyword in all documents''' | '''selecting all keyword in all documents''' | ||
− | + | <source lang="sql"> | |
+ | SELECT okm:keywords FROM okm:document | ||
+ | </source> | ||
okm:keyword is a okm:document property. The nodes in repository has a hierarchical structura, the base type is nt:base. To searching in all nodes mus be used this type ( FROM nt:base ) | okm:keyword is a okm:document property. The nodes in repository has a hierarchical structura, the base type is nt:base. To searching in all nodes mus be used this type ( FROM nt:base ) | ||
− | |||
== WHERE == | == WHERE == | ||
Line 20: | Line 24: | ||
'''selecting all documents with keywords not empty''' | '''selecting all documents with keywords not empty''' | ||
− | + | <source lang="sql"> | |
− | + | SELECT okm:author, okm:keywords FROM okm:document WHERE okm:keywords <> '' | |
+ | </source> | ||
Operators evaluation order: | Operators evaluation order: | ||
− | () | + | * () |
− | operators: <, >, =, <=, >=, <>, LIKE, IS NULL, IS NOT NULL | + | * operators: <, >, =, <=, >=, <>, LIKE, IS NULL, IS NOT NULL |
− | functions: CONTAINS | + | * functions: CONTAINS |
− | logic opertors: NOT, AND, OR | + | * logic opertors: NOT, AND, OR |
− | |||
== LIKE == | == LIKE == | ||
Line 35: | Line 39: | ||
'''documents name starting with linux''' | '''documents name starting with linux''' | ||
− | + | <source lang="sql"> | |
− | + | SELECT * FROM okm:document WHERE okm:name LIKE 'linux%' | |
+ | </source> | ||
== CONTAINS == | == CONTAINS == | ||
Line 43: | Line 48: | ||
'''documents that contains jackrabbit''' | '''documents that contains jackrabbit''' | ||
− | + | <source lang="sql"> | |
+ | SELECT * FROM okm:resource WHERE CONTAINS(., 'jackrabbit') | ||
+ | </source> | ||
− | There's some limitation there's no equivalence with this | + | There's some limitation there's no equivalence with this XPath query in SQL. There's a limitation in SQL to using CONTAINS in a descendant query. |
− | |||
− | |||
+ | <source lang="xml"> | ||
+ | //element(*, okm:document)[jcr:contains(okm:content, 'linux')] | ||
+ | <source> | ||
== ORDER BY == | == ORDER BY == | ||
Line 54: | Line 62: | ||
'''documents that contains linux ordered by score''' | '''documents that contains linux ordered by score''' | ||
− | + | <source lang="sql"> | |
− | + | SELECT * FROM okm:document WHERE CONTAINS(., 'linux') ORDER BY jcr:score DESC | |
− | + | </source> | |
== Property jcr:path == | == Property jcr:path == | ||
Line 62: | Line 70: | ||
'''exact search''' | '''exact search''' | ||
− | + | <source lang="sql"> | |
− | + | jcr:path='/books/mybooks/EffectiveJava' | |
+ | </source> | ||
'''searching for children''' | '''searching for children''' | ||
− | + | <source lang="sql"> | |
− | + | jcr:path LIKE '/books/%' AND NOT jcr:path LIKE '/books/%/%' | |
+ | </source> | ||
'''descendants''' | '''descendants''' | ||
− | + | <source lang="sql"> | |
− | + | jcr:path LIKE '/books/mybooks/%' | |
− | + | </source> | |
− | |||
[[Category: Administration Guide]] | [[Category: Administration Guide]] | ||
[[Category: OKM Network]] | [[Category: OKM Network]] |
Revision as of 18:39, 13 April 2010
Contents |
SQL, often referred to as Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra.
A SQL query is composed normally by clausule SELECT and optionally FROM, WHERE and ORDER BY.
SELECT
Used to specigy a property list ( columns ) by the name it'll apppears in results. The character * indicates all properties are selected.
FROM
Defines the nodes type ( tables ) selected.
selecting all keyword in all documents
SELECT okm:keywords FROM okm:document
okm:keyword is a okm:document property. The nodes in repository has a hierarchical structura, the base type is nt:base. To searching in all nodes mus be used this type ( FROM nt:base )
WHERE
Used to filter selected rows by some criteria.
selecting all documents with keywords not empty
SELECT okm:author, okm:keywords FROM okm:document WHERE okm:keywords <> ''
Operators evaluation order:
- ()
- operators: <, >, =, <=, >=, <>, LIKE, IS NULL, IS NOT NULL
- functions: CONTAINS
- logic opertors: NOT, AND, OR
LIKE
Used for pattern purposes in where clausule
documents name starting with linux
SELECT * FROM okm:document WHERE okm:name LIKE 'linux%'
CONTAINS
Used to full text searching ( the indexed content )
documents that contains jackrabbit
SELECT * FROM okm:resource WHERE CONTAINS(., 'jackrabbit')
There's some limitation there's no equivalence with this XPath query in SQL. There's a limitation in SQL to using CONTAINS in a descendant query.
//element(*, okm:document)[jcr:contains(okm:content, 'linux')]
<source>
== ORDER BY ==
Used to ordering results. Normally used the jcr:score property.
'''documents that contains linux ordered by score'''
<source lang="sql">
SELECT * FROM okm:document WHERE CONTAINS(., 'linux') ORDER BY jcr:score DESC
Property jcr:path
Always it'll appear in results and indicates the full node path. jcr:path it can be used in queries like:
exact search
jcr:path='/books/mybooks/EffectiveJava'
searching for children
jcr:path LIKE '/books/%' AND NOT jcr:path LIKE '/books/%/%'
descendants
jcr:path LIKE '/books/mybooks/%'