Couchbase基础查询 #

一、SELECT语句 #

1.1 基本语法 #

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

1.2 查询所有字段 #

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

1.3 查询指定字段 #

sql 
SELECT name, email, age
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
LIMIT 5;

1.4 使用别名 #

sql 
SELECT 
    name AS user_name,
    email AS user_email,
    age AS user_age
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

1.5 查询结果结构 #

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

结果：

json 
[
    {
        "name": "张三",
        "email": "zhangsan@example.com",
        "city": "北京"
    }
]

二、WHERE条件 #

2.1 基本条件 #

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

SELECT * 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 = '北京');

2.2 比较运算符 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default` WHERE age = 25;
SELECT * FROM `my-bucket`.`_default`.`_default` WHERE age != 25;
SELECT * FROM `my-bucket`.`_default`.`_default` WHERE age > 25;
SELECT * FROM `my-bucket`.`_default`.`_default` WHERE age < 25;
SELECT * FROM `my-bucket`.`_default`.`_default` WHERE age >= 25;
SELECT * FROM `my-bucket`.`_default`.`_default` WHERE age <= 25;

2.3 逻辑运算符 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' 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';

2.4 IN和NOT IN #

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

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE status NOT IN ['deleted', 'banned'];

2.5 BETWEEN #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE age BETWEEN 20 AND 30;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

2.6 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 '%测试%';

通配符说明：

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

2.7 NULL和MISSING #

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

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

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

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

三、USE KEYS查询 #

3.1 单个Key查询 #

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

3.2 多个Key查询 #

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

3.3 与WHERE结合 #

sql 
SELECT name, email
FROM `my-bucket`.`_default`.`_default`
USE KEYS ['user::001', 'user::002']
WHERE status = 'active';

四、ORDER BY排序 #

4.1 基本排序 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY name;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY name ASC;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY age DESC;

4.2 多字段排序 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY city ASC, age DESC;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY status ASC, created_at DESC;

4.3 嵌套字段排序 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY address.city;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order'
ORDER BY payment.amount DESC;

4.4 NULL值排序 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY phone IS NULL, phone;

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY phone IS NOT NULL DESC, phone;

五、LIMIT和OFFSET #

5.1 限制结果数量 #

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

5.2 分页查询 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
ORDER BY name
LIMIT 10 OFFSET 0;

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

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

5.3 分页计算 #

text 
页码从1开始：
OFFSET = (页码 - 1) × 每页数量

第1页：LIMIT 10 OFFSET 0
第2页：LIMIT 10 OFFSET 10
第3页：LIMIT 10 OFFSET 20

六、聚合函数 #

6.1 COUNT #

sql 
SELECT COUNT(*) AS total_users
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

SELECT COUNT(email) AS users_with_email
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

SELECT COUNT(DISTINCT city) AS unique_cities
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

6.2 SUM #

sql 
SELECT SUM(amount) AS total_sales
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order';

SELECT SUM(quantity * price) AS total_revenue
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order_item';

6.3 AVG #

sql 
SELECT AVG(age) AS avg_age
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

SELECT AVG(rating) AS avg_rating
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'review';

6.4 MAX和MIN #

sql 
SELECT 
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

SELECT 
    MAX(created_at) AS latest_order,
    MIN(created_at) AS earliest_order
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order';

6.5 组合使用 #

sql 
SELECT 
    COUNT(*) AS total,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    MAX(amount) AS max_amount,
    MIN(amount) AS min_amount
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order';

七、GROUP BY分组 #

7.1 基本分组 #

sql 
SELECT city, COUNT(*) AS user_count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
GROUP BY city;

7.2 多字段分组 #

sql 
SELECT city, status, COUNT(*) AS count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
GROUP BY city, status;

7.3 HAVING过滤 #

sql 
SELECT city, COUNT(*) AS user_count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
GROUP BY city
HAVING COUNT(*) > 100;

SELECT status, AVG(amount) AS avg_amount
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order'
GROUP BY status
HAVING AVG(amount) > 1000;

7.4 分组排序 #

sql 
SELECT city, COUNT(*) AS user_count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
GROUP BY city
ORDER BY user_count DESC
LIMIT 10;

八、DISTINCT去重 #

8.1 基本去重 #

sql 
SELECT DISTINCT city
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

SELECT DISTINCT status
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order';

8.2 多字段去重 #

sql 
SELECT DISTINCT city, status
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

8.3 与聚合结合 #

sql 
SELECT COUNT(DISTINCT city) AS city_count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

九、嵌套字段查询 #

9.1 访问嵌套对象 #

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

SELECT *
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND address.city = '北京';

9.2 访问数组元素 #

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

SELECT *
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND hobbies[0] = '编程';

9.3 数组长度 #

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

十、常用函数 #

10.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,
    TRIM(name) AS trimmed_name
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

10.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';

10.3 日期函数 #

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

10.4 条件函数 #

sql 
SELECT 
    name,
    IFNULL(phone, 'N/A') AS phone,
    IFMISSING(email, 'unknown@example.com') AS email,
    IFMISSINGORNULL(address, '未设置') AS address
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

十一、查询优化 #

11.1 使用索引 #

sql 
CREATE INDEX idx_users_city 
ON `my-bucket`.`_default`.`_default`(city)
WHERE type = 'user';

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND city = '北京';

11.2 避免全表扫描 #

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

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE LOWER(name) = '张三';

11.3 使用覆盖索引 #

sql 
CREATE INDEX idx_users_name_email 
ON `my-bucket`.`_default`.`_default`(name, email)
WHERE type = 'user';

SELECT name, email
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND name = '张三';

十二、SDK查询 #

12.1 Python SDK #

python 
from couchbase.cluster import Cluster, ClusterOptions
from couchbase.auth import PasswordAuthenticator
from couchbase.options import QueryOptions

cluster = Cluster(
    'couchbase://localhost',
    ClusterOptions(PasswordAuthenticator('Administrator', 'password'))
)

result = cluster.query('''
    SELECT * FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'user' AND city = $city
    LIMIT 10
''', QueryOptions(named_parameters={'city': '北京'}))

for row in result.rows():
    print(row)

12.2 Node.js SDK #

javascript 
const couchbase = require('couchbase');

const cluster = new couchbase.Cluster('couchbase://localhost', {
    username: 'Administrator',
    password: 'password'
});

const query = `
    SELECT * FROM \`my-bucket\`.\`_default\`.\`_default\`
    WHERE type = 'user' AND city = $1
    LIMIT 10
`;

const { rows } = await cluster.query(query, {
    parameters: ['北京']
});

rows.forEach(row => console.log(row));

12.3 Java SDK #

java 
import com.couchbase.client.java.*;
import com.couchbase.client.java.query.*;

Cluster cluster = Cluster.connect(
    "localhost",
    ClusterOptions.clusterOptions("Administrator", "password")
);

String query = "SELECT * FROM `my-bucket`.`_default`.`_default` " +
               "WHERE type = 'user' AND city = $city LIMIT 10";

QueryResult result = cluster.query(query,
    QueryOptions.queryOptions()
        .parameters(JsonObject.create().put("city", "北京"))
);

result.rowsAsObject().forEach(System.out::println);

十三、总结 #

基础查询要点：

子句 说明
SELECT 选择字段
FROM 指定keyspace
WHERE 过滤条件
GROUP BY 分组
HAVING 分组过滤
ORDER BY 排序
LIMIT 限制数量
OFFSET 偏移量

最佳实践：

  1. 始终添加WHERE条件过滤type
  2. 使用索引加速查询
  3. 合理使用LIMIT避免大量数据返回
  4. 使用参数化查询防止注入
  5. 选择需要的字段而非SELECT *

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

最后更新：2026-03-27