基础查询 #

一、SELECT语句概述 #

1.1 基本语法 #

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

1.2 SELECT特点 #

text 
SELECT特点：
├── 支持多种投影方式
├── 支持复杂条件过滤
├── 支持聚合和分组
├── 支持排序和分页
└── 支持子查询

二、基本查询 #

2.1 查询所有字段 #

sql 
SELECT * FROM Person
SELECT FROM Person

2.2 查询指定字段 #

sql 
SELECT name, age, city FROM Person

2.3 使用别名 #

sql 
SELECT name AS fullName, age AS userAge FROM Person
SELECT name firstName, age userAge FROM Person

2.4 查询计算字段 #

sql 
SELECT name, age, age * 2 AS doubleAge FROM Person
SELECT name, firstName + ' ' + lastName AS fullName FROM Person

2.5 使用DISTINCT去重 #

sql 
SELECT DISTINCT city FROM Person
SELECT DISTINCT age, city FROM Person

2.6 查询记录数 #

sql 
SELECT COUNT(*) AS total FROM Person
SELECT COUNT(*) FROM Person WHERE age > 30

三、WHERE条件 #

3.1 比较运算符 #

sql 
SELECT FROM Person WHERE age = 30
SELECT FROM Person WHERE age != 30
SELECT FROM Person WHERE age > 30
SELECT FROM Person WHERE age >= 30
SELECT FROM Person WHERE age < 30
SELECT FROM Person WHERE age <= 30

3.2 逻辑运算符 #

sql 
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 > 18 AND (city = 'Beijing' OR city = 'Shanghai')

3.3 BETWEEN范围 #

sql 
SELECT FROM Person WHERE age BETWEEN 20 AND 40
SELECT FROM Person WHERE createdAt BETWEEN DATE('2024-01-01') AND DATE('2024-12-31')

3.4 IN列表 #

sql 
SELECT FROM Person WHERE city IN ('Beijing', 'Shanghai', 'Guangzhou')
SELECT FROM Person WHERE age IN (20, 25, 30, 35)
SELECT FROM Person WHERE @rid IN [#12:0, #12:1, #12:2]

3.5 LIKE模糊匹配 #

sql 
SELECT FROM Person WHERE name LIKE 'Tom%'
SELECT FROM Person WHERE name LIKE '%om%'
SELECT FROM Person WHERE name LIKE '_om'
SELECT FROM Person WHERE email LIKE '%@gmail.com'

通配符说明：

通配符 说明
% 匹配任意多个字符
_ 匹配单个字符

3.6 NULL判断 #

sql 
SELECT FROM Person WHERE email IS NULL
SELECT FROM Person WHERE email IS NOT NULL
SELECT FROM Person WHERE phone IS NULL AND email IS NOT NULL

3.7 正则表达式 #

sql 
SELECT FROM Person WHERE name MATCHES '^[A-Z][a-z]+$'
SELECT FROM Person WHERE email MATCHES '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'

四、ORDER BY排序 #

4.1 升序排序 #

sql 
SELECT FROM Person ORDER BY name ASC
SELECT FROM Person ORDER BY name

4.2 降序排序 #

sql 
SELECT FROM Person ORDER BY age DESC

4.3 多字段排序 #

sql 
SELECT FROM Person ORDER BY city ASC, age DESC
SELECT FROM Person ORDER BY status ASC, name ASC, age DESC

4.4 按表达式排序 #

sql 
SELECT name, age, age * 2 AS doubleAge FROM Person ORDER BY doubleAge DESC
SELECT name, firstName + ' ' + lastName AS fullName FROM Person ORDER BY fullName

4.5 NULL值排序 #

sql 
SELECT FROM Person ORDER BY email ASC NULLS FIRST
SELECT FROM Person ORDER BY email DESC NULLS LAST

五、LIMIT和SKIP分页 #

5.1 LIMIT限制结果 #

sql 
SELECT FROM Person LIMIT 10
SELECT FROM Person WHERE age > 30 LIMIT 5

5.2 SKIP跳过记录 #

sql 
SELECT FROM Person SKIP 10
SELECT FROM Person SKIP 10 LIMIT 10

5.3 分页查询 #

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

5.4 分页公式 #

text 
第N页: SKIP (N-1) * pageSize LIMIT pageSize

示例 (每页10条):
├── 第1页: SKIP 0 LIMIT 10
├── 第2页: SKIP 10 LIMIT 10
├── 第3页: SKIP 20 LIMIT 10
└── 第N页: SKIP (N-1)*10 LIMIT 10

六、聚合函数 #

6.1 COUNT计数 #

sql 
SELECT COUNT(*) AS total FROM Person
SELECT COUNT(email) AS withEmail FROM Person
SELECT COUNT(DISTINCT city) AS cityCount FROM Person

6.2 SUM求和 #

sql 
SELECT SUM(age) AS totalAge FROM Person
SELECT SUM(salary) AS totalSalary FROM Employee

6.3 AVG平均值 #

sql 
SELECT AVG(age) AS avgAge FROM Person
SELECT AVG(salary) AS avgSalary FROM Employee WHERE department = 'IT'

6.4 MIN和MAX #

sql 
SELECT MIN(age) AS minAge, MAX(age) AS maxAge FROM Person
SELECT MIN(salary) AS minSalary, MAX(salary) AS maxSalary FROM Employee

6.5 组合使用 #

sql 
SELECT 
    COUNT(*) AS total,
    AVG(age) AS avgAge,
    MIN(age) AS minAge,
    MAX(age) AS maxAge
FROM Person

七、GROUP BY分组 #

7.1 基本分组 #

sql 
SELECT city, COUNT(*) AS count FROM Person GROUP BY city
SELECT status, COUNT(*) AS count FROM Person GROUP BY status

7.2 多字段分组 #

sql 
SELECT city, status, COUNT(*) AS count FROM Person GROUP BY city, status

7.3 分组聚合 #

sql 
SELECT city, AVG(age) AS avgAge, COUNT(*) AS count 
FROM Person 
GROUP BY city

7.4 HAVING过滤 #

sql 
SELECT city, COUNT(*) AS count 
FROM Person 
GROUP BY city 
HAVING COUNT(*) > 10

7.5 分组排序 #

sql 
SELECT city, COUNT(*) AS count 
FROM Person 
GROUP BY city 
ORDER BY count DESC

八、使用函数 #

8.1 字符串函数 #

sql 
SELECT 
    name,
    UPPER(name) AS upperName,
    LOWER(name) AS lowerName,
    LENGTH(name) AS nameLength,
    TRIM(name) AS trimmedName,
    SUBSTRING(name, 0, 3) AS shortName
FROM Person

8.2 数学函数 #

sql 
SELECT 
    price,
    ROUND(price, 2) AS roundedPrice,
    CEIL(price) AS ceilPrice,
    FLOOR(price) AS floorPrice,
    ABS(price - 100) AS diff
FROM Product

8.3 日期函数 #

sql 
SELECT 
    createdAt,
    YEAR(createdAt) AS year,
    MONTH(createdAt) AS month,
    DAY(createdAt) AS day,
    HOUR(createdAt) AS hour
FROM Log

8.4 条件函数 #

sql 
SELECT name, age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 60 THEN 'Adult'
        ELSE 'Senior'
    END AS ageGroup
FROM Person
sql 
SELECT name, 
    IF(age >= 18, 'Adult', 'Minor') AS status,
    COALESCE(nickname, name, 'Unknown') AS displayName
FROM Person

九、链接查询 #

9.1 访问链接属性 #

sql 
SELECT name, employer.name AS companyName FROM Person
SELECT name, employer.name, employer.address.city FROM Person

9.2 链接条件 #

sql 
SELECT FROM Person WHERE employer.name = 'ABC Corp'
SELECT FROM Person WHERE employer.address.city = 'Beijing'

9.3 集合链接 #

sql 
SELECT name, friends.name AS friendNames FROM Person
SELECT name, SIZE(friends) AS friendCount FROM Person

十、集合查询 #

10.1 访问集合元素 #

sql 
SELECT name, hobbies[0] AS firstHobby FROM Person
SELECT name, hobbies FROM Person WHERE 'coding' IN hobbies

10.2 集合条件 #

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

10.3 集合大小 #

sql 
SELECT name, SIZE(hobbies) AS hobbyCount FROM Person
SELECT FROM Person WHERE SIZE(hobbies) > 3

10.4 MAP操作 #

sql 
SELECT name, preferences['theme'] AS theme FROM Person
SELECT FROM Person WHERE preferences['theme'] = 'dark'

十一、子查询 #

11.1 WHERE子查询 #

sql 
SELECT FROM Person WHERE employer IN (
    SELECT FROM Company WHERE industry = 'Tech'
)

11.2 FROM子查询 #

sql 
SELECT name, age FROM (
    SELECT FROM Person WHERE age > 30
) WHERE city = 'Beijing'

11.3 投影子查询 #

sql 
SELECT name, (SELECT COUNT(*) FROM Order WHERE customer = @rid) AS orderCount
FROM Person

十二、元数据查询 #

12.1 查询RID #

sql 
SELECT @rid, name FROM Person
SELECT FROM Person WHERE @rid = #12:0

12.2 查询类名 #

sql 
SELECT @rid, @class, name FROM Person
SELECT FROM Person WHERE @class = 'Employee'

12.3 查询版本 #

sql 
SELECT @rid, @version, name FROM Person

12.4 查询所有属性 #

sql 
SELECT @this FROM Person WHERE name = 'Tom'
SELECT properties(@this) FROM Person WHERE @rid = #12:0

十三、查询优化 #

13.1 使用索引 #

sql 
CREATE INDEX Person.email UNIQUE
SELECT FROM Person WHERE email = 'tom@example.com'

13.2 限制结果集 #

sql 
SELECT FROM Person WHERE city = 'Beijing' LIMIT 100

13.3 选择必要字段 #

sql 
SELECT name, email FROM Person WHERE status = 'active'

13.4 避免全表扫描 #

sql 
SELECT FROM Person WHERE @rid = #12:0
SELECT FROM Person WHERE email = 'tom@example.com'

十四、实际应用示例 #

14.1 用户列表查询 #

sql 
SELECT 
    id,
    username,
    email,
    status,
    createdAt
FROM User
WHERE status = 'active'
ORDER BY createdAt DESC
LIMIT 20

14.2 统计查询 #

sql 
SELECT 
    status,
    COUNT(*) AS count,
    AVG(age) AS avgAge
FROM User
GROUP BY status
ORDER BY count DESC

14.3 搜索查询 #

sql 
SELECT FROM Person 
WHERE name LIKE '%Tom%' 
   OR email LIKE '%tom%'
ORDER BY name ASC
LIMIT 50

14.4 分页查询 #

sql 
LET pageSize = 10;
LET pageNum = 1;
SELECT FROM Person 
WHERE status = 'active'
ORDER BY name ASC
SKIP ($pageNum - 1) * $pageSize 
LIMIT $pageSize

14.5 关联查询 #

sql 
SELECT 
    p.name,
    p.email,
    c.name AS companyName,
    c.industry
FROM Person p
LET company = (SELECT FROM Company WHERE @rid = p.employer)
WHERE $company.size() > 0

十五、总结 #

基础查询要点：

操作 语法 说明
选择字段 SELECT field 指定返回字段
条件过滤 WHERE 过滤记录
排序 ORDER BY 结果排序
分页 SKIP/LIMIT 分页查询
聚合 COUNT/SUM/AVG 聚合计算
分组 GROUP BY 分组统计

下一步，让我们学习高级查询！

最后更新：2026-03-27