Skip to main content

Simple SQL

SQL is easy to learn. Our SQL engine is mostly based on MySQL 5.7, albeit some changes may exist. For precise syntax and differences, see SQL compatibility. When a transaction happens, a transaction_id is generated for each data.

Following are some instances of typical development scenarios:Data Using

Data Querying Using SDK​

You can use the query() method to receive a list of results right away. For example, if you want to look up books by the author "Shermin Voshmgir.

cURL​

curl --location 'https://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book WHERE author = \"Shermin Voshmgir\""
}'

Python​

sql = prepare_sql("SELECT * FROM bookshop.book WHERE author=%s", 'Shermin Voshmgir')
db = client.db(mk)
db.query(sql)

Example of the SDK query() method's return structure.​

Querying data with the SDK's query() method in Python or JavaScript returns a list of map (dictionary) structures.

[
    {
        "_tx_id":"",
        "author":"Springer",
        "category":"science",
        "id":1,
        "name":"Theoretische Physik 2- Nichtrelativistische Quantentheorie",
        "price":100,
        "public_at":2005,
        "stock":10
    }
]

Simple Query​

You can use the SELECT... command. FROM .... To retrieve all data from the bookshop. book table, use the following statement.

cURL​

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book"
}'

Python​

sql = "SELECT * FROM bookshop.book"
db = client.db(mk)
db.query(sql)

Filtering Results​

If the number of returned results is excessive and not all of them are relevant, you can use the WHERE statement to filter the results and identify the appropriate portion.

To find books with the category "web3" among several books, for example.

cURL​

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book WHERE category = \"web3\""
}'

Python​

sql = prepare_sql("SELECT * FROM bookshop.book WHERE category=%s", 'web3')
db = client.db(mk)
db.query(sql)

To locate books with the author "Shermin Voshmgir" and the category "web3" among many books, for example.

cURL​

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book WHERE author = \"Shermin Voshmgir\" AND category = \"web3\""
}'

Python​

sql = "SELECT * FROM  bookshop.book WHERE author = 'Shermin Voshmgir' AND category = 'web3'"
db = client.db(mk)
db.query(sql)

For example, to identify books with prices between $10 and $20 among a large number of books, use BETWEEN AND.

cURL​

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book WHERE price BETWEEN 10 AND 20"
}'

Python​

sql = "SELECT * FROM  bookshop.book WHERE price BETWEEN 10 AND 20"
db = client.db(mk)
db.query(sql)

Sorting Results​

The ORDER BY statement can be used to sort the query results in the appropriate order.

To find the most recently published books, for example, use the following SQL statement to sort the data in the book table by the public_at column in descending (DESC) order.

cURL​

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book ORDER BY public_at DESC LIMIT 10"
}'

Python​

sql = "SELECT * FROM bookshop.book ORDER BY public_at DESC LIMIT 10"
db = client.db(mk)
db.query(sql)

Pagination​

LIMIT... OFFSET... can be used to return only a subset of the results. Note the arrangement of LIMIT and OFFSET for pagination.

cURL​

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book LIMIT 10 OFFSET 10"
}'

Python​

sql = "SELECT * FROM bookshop.book LIMIT 10 OFFSET 10"
db = client.db(mk)
db.query(sql)

Function Usage​

You can use count() function to calculate the number of results returned.

cURL​

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT COUNT(*) AS counts FROM  bookshop.book"
}'

Python​

sql = "SELECT COUNT(*) AS counts FROM bookshop.book"
db = client.db(mk)
db.query(sql)

You can use Concat() function to concatenate multiple columns into a single value.

cURL​

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT Concat(name, author) AS name_author FROM  bookshop.book"
}'

Python​

sql = "SELECT Concat(name, author) AS name_author FROM bookshop.book"
db = client.db(mk)
db.query(sql)