SQL语法基础 #

OrientDB SQL特点：
├── 兼容标准SQL语法
├── 扩展图查询功能
├── 支持JSON格式
├── 支持嵌套查询
├── 支持模式匹配
└── 支持遍历操作

命名规则：
├── 以字母或下划线开头
├── 可包含字母、数字、下划线
├── 区分大小写
├── 不能使用保留字
└── 建议使用有意义的名称

OrientDB保留字（部分）：
├── SELECT, FROM, WHERE, INSERT
├── UPDATE, DELETE, CREATE, DROP
├── AND, OR, NOT, NULL, TRUE, FALSE
├── ORDER, BY, GROUP, HAVING, LIMIT
├── INDEX, CLASS, CLUSTER, DATABASE
└── VERTEX, EDGE, GRAPH, TRAVERSE

SELECT `from`, `to` FROM `Order`
CREATE CLASS `User-Profile`

SELECT 'Hello World' AS message
SELECT "Double quotes" AS text
SELECT 'It''s a test' AS escaped

SELECT 42 AS integer_value
SELECT 3.14 AS float_value
SELECT 100L AS long_value
SELECT 2.5F AS float_explicit

SELECT true AS is_active
SELECT false AS is_deleted

SELECT DATE('2024-01-15') AS date_value
SELECT DATETIME('2024-01-15 10:30:00') AS datetime_value

SELECT NULL AS empty_value
SELECT name FROM Person WHERE email IS NULL

SELECT * FROM Person
WHERE age > 18

SELECT * FROM Person
WHERE age > 18
AND status = 'active'

SELECT [FROM] <target>
[WHERE <condition>]
[GROUP BY <field>]
[ORDER BY <field> [ASC|DESC]]
[LIMIT <maxRecords>]
[SKIP <skipRecords>]

SELECT * FROM Person

SELECT name, age FROM Person

SELECT name AS fullName, age AS userAge FROM Person

SELECT name, age * 2 AS doubleAge FROM Person
SELECT name, UPPER(name) AS upperName FROM Person

SELECT DISTINCT city FROM Person

SELECT * FROM Person WHERE age > 18 AND status = 'active'
SELECT * FROM Person WHERE age < 18 OR age > 60
SELECT * FROM Person WHERE NOT status = 'deleted'

SELECT * FROM Person WHERE age BETWEEN 18 AND 60
SELECT * FROM Person WHERE age IN (20, 30, 40)
SELECT * FROM Person WHERE name IN ['Tom', 'Jerry']

SELECT * FROM Person WHERE name LIKE 'T%'
SELECT * FROM Person WHERE name LIKE '%om%'
SELECT * FROM Person WHERE name LIKE '_om'

SELECT * FROM Person WHERE email IS NULL
SELECT * FROM Person WHERE email IS NOT NULL

SELECT * FROM Person WHERE name MATCHES '^[A-Z][a-z]+$'

SELECT * FROM Person ORDER BY name ASC
SELECT * FROM Person ORDER BY name

SELECT * FROM Person ORDER BY age DESC

SELECT * FROM Person ORDER BY city ASC, age DESC

SELECT * FROM Person LIMIT 10

SELECT * FROM Person SKIP 10 LIMIT 10

SELECT * FROM Person SKIP 0 LIMIT 10
SELECT * FROM Person SKIP 10 LIMIT 10
SELECT * FROM Person SKIP 20 LIMIT 10

SELECT firstName + ' ' + lastName AS fullName FROM Person
SELECT CONCAT(firstName, ' ', lastName) AS fullName FROM Person

SELECT * FROM Person WHERE 'reading' IN hobbies
SELECT * FROM Person WHERE hobbies CONTAINS 'reading'
SELECT * FROM Person WHERE hobbies CONTAINSALL ['reading', 'coding']
SELECT * FROM Person WHERE hobbies CONTAINSANY ['reading', 'coding']

SELECT * FROM Person WHERE @rid = #12:0
SELECT * FROM Person WHERE out('KNOWS') CONTAINS #13:0

SELECT name, 
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 60 THEN 'Adult'
        ELSE 'Senior'
    END AS ageGroup
FROM Person

SELECT name, IF(age >= 18, 'Adult', 'Minor') AS status FROM Person

SELECT COALESCE(nickname, name, 'Unknown') AS displayName FROM Person

SELECT NULLIF(score, 0) AS normalizedScore FROM Exam

SELECT {'name': 'Tom', 'age': 30} AS person

SELECT address.city FROM Person
SELECT address['city'] FROM Person

SELECT JSON_FROM_RECORD(@this) FROM Person
SELECT JSON_FROM_SET(hobbies) FROM Person

SELECT FROM metadata:schema
SELECT FROM (SELECT classes FROM metadata:schema) WHERE name = 'Person'

SELECT FROM metadata:indexes
SELECT FROM metadata:indexManager

SELECT FROM metadata:database
SELECT properties FROM metadata:database

BEGIN;
INSERT INTO Person SET name = 'Tom', age = 30;
INSERT INTO Person SET name = 'Jerry', age = 25;
COMMIT;

IF (SELECT COUNT(*) FROM Person WHERE name = 'Tom' = 0) {
    INSERT INTO Person SET name = 'Tom', age = 30;
}

LET persons = SELECT FROM Person WHERE status = 'active';
FOREACH ($person IN $persons) {
    UPDATE $person SET lastActive = sysdate();
}