lumen 配置数据库结果自动转数组_Laravel & Lumen 数据库操作速查

在Laravel中执行数据库操作有两种方式，一种是使用\DB外观对象的静态方法直接执行sql查询，另外一种是使用Model类的静态方法(实际上也是Facade的实现，使用静态访问方式访问Model的方法，内部采用了__callStatic魔术方法代理了对成员方法的访问。

查询操作

基本查询操作

使用sql语句执行select查询操作

$results = DB::select('select * from users where id = ?', [1]);

foreach ($results as $res) {

echo $res->name;

}

返回结果为数组，数组中每一个值为一个StdClass对象。

也可以使用命名绑定，推荐使用这种方式，更加清晰一些

$results = DB::select('select * from users where id = :id', ['id' => 1]);

从数据表中取得所有的数据列

$users = DB::table('users')->get();

foreach ($users as $user)

{

var_dump($user->name);

}

从表中查询单行/列

使用first方法返回单行数据，该方法返回的是一个stdClass对象

$user = DB::table('users')->where('name', 'John')->first();

echo $user->name;

如果只需要一列的值，则可以使用value方法直接获取单列的值

$email = DB::table('users')->where('name', 'John')->value('email');

从数据表中分块查找数据列

该方法用于数据表中有大量的数据的操作，每次从结果集中取出一部分，使用闭包函数进行处理，然后再处理下一部分，该命令一般用于Artisan命令行程序中处理大量数据。

DB::table('users')->chunk(100, function($users)

{

foreach ($users as $user)

{

//

}

});

在闭包函数中，如果返回false，则会停止后续的处理。

从数据表中查询某一列的列表

比如我们希望查询出角色表中所有的title字段值

$titles = DB::table('roles')->pluck('title');

foreach ($titles as $title) {

echo $title;

}

这里的pluck函数有两个参数

Collection pluck( string $column, string|null $key = null)

第一个参数为要查询的列，第二个参数是每一列的key

$roles = DB::table('roles')->pluck('title', 'name');

foreach ($roles as $name => $title) {

echo $title;

}

聚集函数

查询构造器也提供了一些聚集函数如count，max，min，avg，sum等

$users = DB::table('users')->count();

$price = DB::table('orders')->max('price');

$price = DB::table('orders')

->where('finalized', 1)

->avg('price');

指定select查询条件

查询指定的列

$users = DB::table('users')->select('name', 'email as user_email')->get();

如果已经指定了select，但是又希望再次添加一些字段，使用它addSelect方法

$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();

查询不同的结果distinct

$users = DB::table('users')->distinct()->get();

使用原生表达式

使用DB::raw方法可以向查询中注入需要的sql片段，但是非常不推荐使用该方法，用不好会 产生sql注入

$users = DB::table('users')

->select(DB::raw('count(*) as user_count, status'))

->where('status', '<>', 1)

->groupBy('status')

->get();

Join操作

内连接 Inner Join

使用join执行内连接操作，该函数第一个参数为要连接的表名，其它参数指定了连接约束

$users = DB::table('users')

->join('contacts', 'users.id', '=', 'contacts.user_id')

->join('orders', 'users.id', '=', 'orders.user_id')

->select('users.*', 'contacts.phone', 'orders.price')

->get();

左连接 Left Join

使用leftJoin方法执行左连接操作，参数和join一样

$users = DB::table('users')

->leftJoin('posts', 'users.id', '=', 'posts.user_id')

->get();

高级Join方法

如果join方法的约束条件比较复杂，可以使用闭包函数的方式指定

DB::table('users')

->join('contacts', function ($join) {

$join->on('users.id', '=', 'contacts.user_id')->orOn(...);

})

->get();

如果join约束中要使用列值与指定数组比较，则可以使用where和OrWhere方法

DB::table('users')

->join('contacts', function ($join) {

$join->on('users.id', '=', 'contacts.user_id')

->where('contacts.user_id', '>', 5);

})

->get();

Union操作

要使用union操作，可以先创建一个query，然后再使用union方法去绑定第二个query

$first = DB::table('users')

->whereNull('first_name');

$users = DB::table('users')

->whereNull('last_name')

->union($first)

->get();

同样，unionAll方法也是可以使用的，参数与union相同。

Where查询条件

简单的wehere条件

使用where方法为查询增加where条件，该函数一般需要三个参数：列名，操作符(任何数据库支持的操作符都可以)，列值。

$users = DB::table('users')->where('votes', '=', 100)->get();

$users = DB::table('users')->where('votes', 100)->get();

为了方便起见，如果只提供两个参数，则默认第二个参数为=，执行相等匹配。

$users = DB::table('users')

->where('votes', '>=', 100)

->get();

$users = DB::table('users')

->where('votes', '<>', 100)

->get();

$users = DB::table('users')

->where('name', 'like', 'T%')

->get();

where条件也可以使用数组提供：

$users = DB::table('users')->where([

['status','1'],

['subscribed','<>','1'],

])->get();

OR条件

如果where条件要使用or操作，则使用orWhere方法

$users = DB::table('users')

->where('votes', '>', 100)

->orWhere('name', 'John')

->get();

其它where条件

whereBetween / whereNotBetween

$users = DB::table('users')

->whereBetween('votes', [1, 100])->get();

$users = DB::table('users')

->whereNotBetween('votes', [1, 100])->get();

whereIn / whereNotIn

$users = DB::table('users')

->whereIn('id', [1, 2, 3])

->get();

$users = DB::table('users')

->whereNotIn('id', [1, 2, 3])

->get();

whereNull / whereNotNull

$users = DB::table('users')

->whereNull('updated_at')

->get();

$users = DB::table('users')

->whereNotNull('updated_at')

->get();

高级where条件

参数组(嵌套条件)

DB::table('users')

->where('name', '=', 'John')

->orWhere(function ($query) {

$query->where('votes', '>', 100)

->where('title', '<>', 'Admin');

})

->get();

上述代码等价于下列sql

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

whereExists (where exist)

DB::table('users')

->whereExists(function ($query) {

$query->select(DB::raw(1))

->from('orders')

->whereRaw('orders.user_id = users.id');

})

->get();

上述代码与下列sql等价

select * from users

where exists (

select 1 from orders where orders.user_id = users.id

)

JSON类型的列查询

MySQL 5.7和Postgres数据库中提供了新的数据类型json，对json提供了原生的支持，使用->可以对json列进行查询。

$users = DB::table('users')

->where('options->language', 'en')

->get();

$users = DB::table('users')

->where('preferences->dining->meal', 'salad')

->get();

Ordering, Grouping, Limit, & Offset

$users = DB::table('users')

->orderBy('name', 'desc')

->get();

$users = DB::table('users')

->groupBy('account_id')

->having('account_id', '>', 100)

->get();

$users = DB::table('orders')

->select('department', DB::raw('SUM(price) as total_sales'))

->groupBy('department')

->havingRaw('SUM(price) > 2500')

->get();

要限制查询返回的结果行数，或者是跳过指定行数的结果(OFFSET)，可以使用skip和take方法

$users = DB::table('users')->skip(10)->take(5)->get();

插入操作

使用sql语句执行插入

插入操作与select操作类似,使用insert函数

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);

基本插入操作

DB::table('users')->insert(

['email' => 'john@example.com', 'votes' => 0]

);

DB::table('users')->insert([

['email' => 'taylor@example.com', 'votes' => 0],

['email' => 'dayle@example.com', 'votes' => 0]

]);

如果希望插入后能够获取新增数据的id，则可以使用insertGetId方法

$id = DB::table('users')->insertGetId(

['email' => 'john@example.com', 'votes' => 0]

);

更新操作

使用sql语句执行更新操作

执行DB中的update后，会返回 操作影响的数据行数

DB::update('update users set votes = 100 where name = ?', ['John']);

基本更新操作

DB::table('users')

->where('id', 1)

->update(['votes' => 1]);

指定列的增减

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);

在执行自增/减操作的时候，也可以同时更新其它列

DB::table('users')->increment('votes', 1, ['name' => 'John']);

删除操作

使用sql执行删除

执行DB中的delete后，会返回 操作影响的数据行数

DB::delete('delete from users');

基本删除操作

DB::table('users')->delete();

DB::table('users')->where('votes', 'delete();

如果希望truncate整个表，则使用truncate方法

DB::table('users')->truncate();

悲观锁

使用sharedLock方法可以避免选定的行在事务提交之前被修改

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();

另外lockForUpdate方法可以避免其它的共享锁修改或者是选定

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

事务处理

使用transaction方法的callback函数执行事务处理

DB::transaction(function()

{

DB::table('users')->update(['votes' => 1]);

DB::table('posts')->delete();

});

在回调函数中，抛出任何异常都会导致事务回滚

如果需要手动管理事务，则使用如下函数

DB::beginTransaction();

DB::rollback();

DB::commit();

使用DB类的静态方法启用的事务不仅对普通sql查询有效，对Eloquent ORM同样有效，因为它内部也是调用了DB类的数据库连接。

查看日志记录

查看请求执行的sql日志记录，需要先执行enableQueryLog开启，然后执行getQueryLog获取

DB::connection()->enableQueryLog();

$queries = DB::getQueryLog();

其它操作

执行一般的sql语法

DB::statement('drop table users');

监听查找事件，可以用来对执行的sql进行记录

DB::listen(function($sql, $bindings, $time)

{

// $query->sql

// $query->bindings

// $query->time

});

获取某个数据库连接

$users = DB::connection('foo')->select(...);

如果还不能满足需求，可以获取PDO对象

$pdo = DB::connection()->getPdo();

这样不管什么操作都可以做了吧

另外含有两个方法，用于重新连接到指定数据库和断开连接

DB::reconnect('foo');

DB::disconnect('foo')d;