README

本文档为使用 DB 类执行各种数据库操作提供了手册。

1. 初始化

首先，您需要使用 DB::getDBM() 初始化数据库连接。

$dbConfig = [
    'host' => 'localhost',
    'database' => 'test',
    'user' => 'root',
    'password' => '111111'
];
DB::getDBM($dbConfig);

2. 创建表

您可以使用 DB::createTable() 创建一个表。

DB::createTable('users', [
    'name' => [
        'type' => 'string',
        'length' => 100,
        'nullable' => false,
        'default' => ''
    ],
    'email' => [
        'type' => 'string',
        'length' => 255,
        'nullable' => false
    ],
    'age' => [
        'type' => 'integer',
        'nullable' => true
    ],
    'balance' => [
        'type' => 'decimal',
        'length' => [10, 2],
        'default' => 0.00
    ],
    'status' => [
        'type' => 'enum',
        'length' => ['active', 'disabled'],
        'default' => 'active'
    ],
    'vip' => [
        'type' => 'boolean',
        'default' => false
    ],
    'last_login' => [
        'type' => 'datetime',
        'nullable' => true
    ],
    'login_count' => [
        'type' => 'integer',
        'default' => 0
    ],
    'created_at' => [
        'type' => 'datetime',
        'default' => 'CURRENT_TIMESTAMP'
    ]
]);

3. 插入数据

您可以使用 DB::insert() 向表中插入数据。

DB::insert('users', [
    'name' => '张三',
    'email' => 'zhangsan@example.com',
    'age' => 25,
    'balance' => 150.50,
    'status' => 'active',
    'vip' => true,
    'last_login' => '2023-12-01 10:00:00',
    'login_count' => 15,
    'created_at' => '2023-01-15 08:30:00'
]);

4. 查询数据

4.1. 简单 OR 条件

balance < 100 OR status = 'blocked'

$users = DB::select('users', '*', [
    'or' => [
        'balance[<]' => 100,
        'status' => 'blocked'
    ]
]);

4.2. 复杂嵌套条件

status = 'active' AND (id IN (1,2,3) OR name LIKE '%Admin%')

$users = DB::select('users', '*', [
    'status' => 'active',
    'or' => [
        'id[in]' => [1, 2, 3],
        'name[~]' => '%Admin%'
    ]
]);

4.3. 多层嵌套条件

status = 'active' AND (balance > 100 OR (age < 30 AND vip = 1))

$users = DB::select('users', '*', [
    'status' => 'active',
    'or' => [
        'balance[>]' => 100,
        'and' => [
            'age[<]' => 30,
            'vip' => 1
        ]
    ]
]);

4.4. 多个 OR 条件

age > 30 OR balance < 100 OR name LIKE '%张%' OR vip = 1

$users = DB::select('users', '*', [
    'or' => [
        'age[>]' => 30,
        'balance[<]' => 100,
        'name[~]' => '%张%',
        'vip' => 1
    ]
]);

4.5. 其他运算符

age BETWEEN 25 AND 35 AND vip != 1

$users = DB::select('users', '*', [
    'age[between]' => [25, 35],
    'vip[!]' => 1
]);

5. 更新数据

将 balance < 100 或 login_count < 5 的用户的 status 更新为 inactive。

$affected = DB::update('users', ['status' => 'inactive'], [
    'or' => [
        'balance[<]' => 100,
        'login_count[<]' => 5
    ]
]);

6. 删除数据

删除 status = 'inactive' AND (created_at < '2023-03-01' OR (vip = 0 AND balance < 60)) 的用户。

$deleteCondition = [
    'status' => 'inactive',
    'or' => [
        'created_at[<]' => '2023-03-01',
        'and' => [
            'vip' => 0,
            'balance[<]' => 60
        ]
    ]
];
DB::delete('users', $deleteCondition);

7. 分页查询

获取用户表的第二页，每页2条用户。

$result = DB::pager('users', '*', [], 2, 2, ['orderBy' => ['id' => 'asc']]);

8. 事务

在事务中插入一个新用户并更新一个现有用户。

DB::transaction(function () {
    DB::insert('users', [
        'name' => '事务用户',
        'email' => 'transaction@example.com',
        'age' => 99,
        'balance' => 1000,
        'status' => 'active',
        'vip' => true
    ]);

    DB::update('users', ['balance' => 9999], ['id' => 1]);
});

9. 原生 SQL 查询

9.1. 执行原生 SQL 查询

$users = DB::sql("SELECT * FROM users WHERE age > ?", [30]);

9.2. 获取最近执行的 SQL 查询

DB::select('users', '*', ['age[>]' => 30]);
$lastSql = DB::getSql();
// $lastSql 将包含: SELECT * FROM `users` WHERE `age` > 30

evephp / cycle_orm

Maintainers

Details