Couchbase基础语法 #

一、N1QL概述 #

1.1 什么是N1QL #

N1QL（Non-First Normal Form Query Language）是Couchbase的查询语言，发音为"nickel"。它在SQL语法基础上进行了扩展，支持JSON文档的查询和操作。

1.2 N1QL与SQL对比 #

SQL概念	N1QL概念	说明
Database	Bucket	数据存储容器
Schema	Scope	命名空间
Table	Collection	数据集合
Row	Document	数据记录
Column	Field	字段/属性

1.3 基本语法结构 #

sql

SELECT [expression]
FROM [keyspace]
[WHERE condition]
[GROUP BY expression]
[HAVING condition]
[ORDER BY expression]
[LIMIT number]
[OFFSET number]

二、命名规范 #

2.1 Bucket命名 #

text

规则：
- 以字母开头
- 只包含字母、数字、下划线、连字符
- 长度1-100字符
- 不区分大小写

正确示例：
my-bucket
users_data
ProductCatalog

错误示例：
1bucket     (数字开头)
my bucket   (包含空格)
my.bucket   (包含点号)

2.2 Scope和Collection命名 #

text

规则：
- 以字母或下划线开头
- 只包含字母、数字、下划线
- 长度1-251字符
- 不区分大小写

正确示例：
_default
users
order_items

错误示例：
1scope
my-scope
my.scope

2.3 文档Key命名 #

text

推荐格式：
[类型]::[唯一标识]

示例：
user::001
product::SKU123
order::20240115001

2.4 字段命名 #

json

{
    "type": "user",
    "user_id": "001",
    "firstName": "张三",
    "created_at": "2024-01-15T10:30:00Z",
    "is_active": true
}

命名建议：

风格	示例	适用场景
snake_case	user_name	Python风格
camelCase	userName	JavaScript风格
PascalCase	UserName	类名风格

三、CBQ命令行工具 #

3.1 启动CBQ #

bash

/opt/couchbase/bin/cbq \
    --engine http://localhost:8091 \
    --user Administrator \
    --password your-password

3.2 连接指定Bucket #

bash

/opt/couchbase/bin/cbq \
    --engine http://localhost:8091 \
    --user Administrator \
    --password your-password \
    --bucket my-bucket

3.3 CBQ常用命令 #

sql

\HELP                    -- 显示帮助
\EXIT                    -- 退出CBQ
\SET                     -- 显示变量设置
\SET -field name value   -- 设置变量
\VERSION                 -- 显示版本
\ECHO text               -- 输出文本
\SOURCE filename         -- 执行文件中的语句

3.4 设置查询上下文 #

sql

\SET -query_context `my-bucket`.`my-scope`;

SELECT * FROM `my-collection` LIMIT 5;

四、数据定义语句 #

4.1 创建Scope #

sql

CREATE SCOPE `my-bucket`.`my-scope`;

CREATE SCOPE `my-bucket`.`my-scope` IF NOT EXISTS;

4.2 创建Collection #

sql

CREATE COLLECTION `my-bucket`.`my-scope`.`users`;

CREATE COLLECTION `my-bucket`.`my-scope`.`users` IF NOT EXISTS;

4.3 删除Collection #

sql

DROP COLLECTION `my-bucket`.`my-scope`.`users`;

DROP COLLECTION `my-bucket`.`my-scope`.`users` IF EXISTS;

4.4 删除Scope #

sql

DROP SCOPE `my-bucket`.`my-scope`;

DROP SCOPE `my-bucket`.`my-scope` IF EXISTS;

五、基本查询语句 #

5.1 SELECT基础 #

sql

SELECT * FROM `my-bucket`.`_default`.`_default` LIMIT 5;

SELECT name, email FROM `my-bucket`.`_default`.`_default` LIMIT 5;

SELECT name AS user_name, email AS user_email 
FROM `my-bucket`.`_default`.`_default` 
LIMIT 5;

5.2 指定文档Key查询 #

sql

SELECT * FROM `my-bucket`.`_default`.`_default` USE KEYS "user::001";

SELECT * FROM `my-bucket`.`_default`.`_default` USE KEYS ["user::001", "user::002"];

5.3 WHERE条件 #

sql

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

SELECT name, age, email 
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND age > 25;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND (age > 25 OR city = '北京');

5.4 排序和分页 #

sql

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY age DESC, name ASC
LIMIT 10 OFFSET 20;

六、运算符 #

6.1 比较运算符 #

运算符	说明	示例
=	等于	age = 25
!=, <>	不等于	status != ‘deleted’
>	大于	age > 18
<	小于	age < 60
>=	大于等于	score >= 60
<=	小于等于	price <= 100

6.2 逻辑运算符 #

sql

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND age >= 18 AND status = 'active';

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND (city = '北京' OR city = '上海');

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND NOT status = 'deleted';

6.3 特殊运算符 #

sql

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE age IS NOT NULL;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE age IS NULL;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE age IS MISSING;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE age IS NOT MISSING;

6.4 IN和BETWEEN #

sql

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE city IN ['北京', '上海', '广州', '深圳'];

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE age BETWEEN 18 AND 60;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE name NOT IN ['admin', 'root'];

6.5 LIKE模糊匹配 #

sql

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE name LIKE '张%';

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE email LIKE '%@gmail.com';

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE name LIKE '%三%';

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE name NOT LIKE '%测试%';

七、JSON路径表达式 #

7.1 访问嵌套字段 #

sql

SELECT 
    name,
    address.city,
    address.street
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

SELECT 
    name,
    address.`city.code`
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

7.2 数组访问 #

sql

SELECT 
    name,
    hobbies[0] AS first_hobby,
    hobbies[1] AS second_hobby
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

SELECT 
    name,
    ARRAY_LENGTH(hobbies) AS hobby_count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

八、内置函数 #

8.1 字符串函数 #

sql

SELECT 
    UPPER(name) AS upper_name,
    LOWER(email) AS lower_email,
    LENGTH(name) AS name_length,
    SUBSTR(name, 1, 2) AS short_name,
    CONCAT(name, ' - ', city) AS full_info
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

8.2 数值函数 #

sql

SELECT 
    ABS(score - 60) AS diff,
    ROUND(price, 2) AS rounded_price,
    CEIL(price) AS ceil_price,
    FLOOR(price) AS floor_price,
    POWER(age, 2) AS age_squared
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'product';

8.3 日期函数 #

sql

SELECT 
    created_at,
    DATE_PART(created_at, 'year') AS year,
    DATE_PART(created_at, 'month') AS month,
    DATE_PART(created_at, 'day') AS day,
    DATE_FORMAT_STR(created_at, 'YYYY-MM-DD') AS formatted_date
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

8.4 聚合函数 #

sql

SELECT 
    COUNT(*) AS total_users,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age,
    SUM(salary) AS total_salary
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

8.5 数组函数 #

sql

SELECT 
    name,
    ARRAY_LENGTH(hobbies) AS hobby_count,
    ARRAY_CONTAINS(hobbies, '编程') AS likes_coding,
    ARRAY_AGG(name) AS all_names
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

九、CASE表达式 #

9.1 简单CASE #

sql

SELECT 
    name,
    CASE status
        WHEN 'active' THEN '活跃'
        WHEN 'inactive' THEN '非活跃'
        WHEN 'deleted' THEN '已删除'
        ELSE '未知'
    END AS status_text
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

9.2 搜索CASE #

sql

SELECT 
    name,
    age,
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 30 THEN '青年'
        WHEN age BETWEEN 31 AND 50 THEN '中年'
        ELSE '老年'
    END AS age_group
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

十、注释 #

10.1 单行注释 #

sql

SELECT * FROM `my-bucket`.`_default`.`_default` -- 这是单行注释
WHERE type = 'user';

// 这也是单行注释
SELECT name, email FROM `my-bucket`.`_default`.`_default`;

10.2 多行注释 #

sql

/*
 * 这是多行注释
 * 可以跨越多行
 */
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

十一、元数据查询 #

11.1 查询所有Bucket #

sql

SELECT * FROM system:buckets;

11.2 查询所有Scope #

sql

SELECT * FROM system:scopes
WHERE bucket_id = 'my-bucket';

11.3 查询所有Collection #

sql

SELECT * FROM system:keyspaces
WHERE bucket_id = 'my-bucket';

11.4 查询索引信息 #

sql

SELECT * FROM system:indexes
WHERE keyspace_id = 'my-bucket';

十二、总结 #

N1QL语法要点：

类别	关键字/运算符
查询	SELECT, FROM, WHERE, ORDER BY, LIMIT
比较	=, !=, <, >, <=, >=
逻辑	AND, OR, NOT
特殊	IS NULL, IS MISSING, IN, BETWEEN, LIKE
聚合	COUNT, SUM, AVG, MAX, MIN

最佳实践：

使用有意义的文档Key命名
统一字段命名风格
为常用查询字段创建索引
使用参数化查询防止注入
合理使用LIMIT避免全表扫描

下一步，让我们学习Couchbase的数据类型！