laravel5 数据库ORM 链式操作使用文档(✿◡‿◡)

2016-11-01 分类：后端之路阅读(1853) 评论(0)

继续上次在Laravel外独立使用Eloquent 之后, 继续研究一下laravel的数据库ORM

公司项目使用原生sql去查询数据,危险重重,之前刚来公司时候简单封装了了一个数据库链式操作的类,现在也满足不了业务需求了,于是,以后可能就要用laravel 这个ORM了

大概代码如下: 包含几乎所有的数据库操作实例,以及事务,

/**
 * Created by PhpStorm.
 * User: xiangming
 * Date: 2016/11/1
 * Time: 15:29
 */
include_once 'db.php';
//包含Eloquent的初始化文件
use Illuminate\Database\Capsule\Manager as DB;
$result = DB::table('user')->first(); //检索一行
$result = DB::table('user')->pluck('uid');//检索单个列的行(取出一行的uid)
$result = DB::table('user')->lists('uid');//检索一个列值列表(取出所有的uid)
$result = DB::table('user')->select('uid')->get();

//where
$users = DB::table('users')->where('votes', '>', 100)->get();
//OR
$users = DB::table('users')->where('votes', '>', 100)->orWhere('name', 'John')->get();
//Where Between
$users = DB::table('users')->whereBetween('votes', array(1, 100))->get();
//Where Not Between
$users = DB::table('users')->whereNotBetween('votes', array(1, 100))->get();
//Where In With An Array
$users = DB::table('users')->whereIn('id', array(1, 2, 3))->get();
$users = DB::table('users')->whereNotIn('id', array(1, 2, 3))->get();
//Using Where Null To Find Records With Unset Values
$users = DB::table('users')->whereNull('updated_at')->get();
//Order By, Group By, And Having
$users = DB::table('users')->orderBy('name', 'desc')->groupBy('count')->having('count', '>', 100)->get();
//Offset & Limit
$users = DB::table('users')->skip(10)->take(5)->get();
//Joins
DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.id', 'contacts.phone', 'orders.price')
    ->get();
//左连接语句
DB::table('users')->leftJoin('posts', 'users.id', '=', 'posts.user_id')->get();
DB::table('users')->join('contacts', function($join){
    $join->on('users.id', '=', 'contacts.user_id')->orOn('');
})->get();
DB::table('users')->join('contacts', function($join) {
    $join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})->get();
//分组
DB::table('users')
    ->where('name', '=', 'John')
    ->orWhere(function($query) {
        $query->where('votes', '>',100)->where('title', '<>', 'Admin');
    })->get();
//聚合
//查询构建器还提供了各种聚合方法,如统计,马克斯,min,avg和总和。Using Aggregate Methods
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->min('price');
$price = DB::table('orders')->avg('price');
$total = DB::table('users')->sum('votes');
//Raw Expressions
$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();
//递增或递减一个列的值
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, array('name' => 'John'));
//将记录插入表
DB::table('users')->insert(
    array('email' => 'john@example.com', 'votes' => 0)
);
//将记录插入表自动增加的ID
$id = DB::table('users')->insertGetId(
    array('email' => 'john@example.com', 'votes' => 0)
);
//注意:当使用PostgreSQL insertGetId方法预计,自增列被命名为“id”
//多个记录插入到表中
DB::table('users')->insert(array(
    array('email' => 'taylor@example.com', 'votes' => 0),
    array('email' => 'dayle@example.com', 'votes' => 0),
));
//更新一个表中的记录
DB::table('users')
    ->where('id', 1)
    ->update(array('votes' => 1));
//Deletes
DB::table('users')->where('votes', '<', 100)->delete();
//Unions
//查询构建器还提供了一种快速的方法来“联盟”两个查询:
$first = DB::table('users')->whereNull('first_name');
$users = DB::table('users')->whereNull('last_name')->union($first)->get();
//unionAll方法也可以,有相同的方法签名。
DB::table('users')->where('votes', '>',
    100)->sharedLock()->get();
//更新“锁”在一个SELECT语句,您可以使用lockForUpdate方法查询:
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
//缓存查询
$users = DB::table('users')->remember(10)->get();
//十分钟被缓存
$users = DB::table('users')->cacheTags(array('people', 'authors'))->remember(10)->get();

//事务
DB::beginTransaction();
DB::rollback();
DB::commit();
DB::transaction(function(){
    DB::table('users')->update(['votes' => 1]);
    DB::table('posts')->delete();
});

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

/**

* Created by PhpStorm.

* User: xiangming

* Date: 2016/11/1

* Time: 15:29

include_once 'db.php';

//包含Eloquent的初始化文件

use Illuminate\Database\Capsule\Manager as DB;

$result = DB::table('user')->first(); //检索一行

$result = DB::table('user')->pluck('uid');//检索单个列的行(取出一行的uid)

$result = DB::table('user')->lists('uid');//检索一个列值列表(取出所有的uid)

$result = DB::table('user')->select('uid')->get();

//where

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

//OR

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

//Where Between

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

//Where Not Between

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

//Where In With An Array

$users = DB::table('users')->whereIn('id', array(1, 2, 3))->get();

$users = DB::table('users')->whereNotIn('id', array(1, 2, 3))->get();

//Using Where Null To Find Records With Unset Values

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

//Order By, Group By, And Having

$users = DB::table('users')->orderBy('name', 'desc')->groupBy('count')->having('count', '>', 100)->get();

//Offset & Limit

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

//Joins

DB::table('users')

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

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

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

->get();

//左连接语句

DB::table('users')->leftJoin('posts', 'users.id', '=', 'posts.user_id')->get();

DB::table('users')->join('contacts', function($join){

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

})->get();

DB::table('users')->join('contacts', function($join) {

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

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

})->get();

//分组

DB::table('users')

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

->orWhere(function($query) {

$query->where('votes', '>',100)->where('title', '<>', 'Admin');

})->get();

//聚合

//查询构建器还提供了各种聚合方法,如统计,马克斯,min,avg和总和。Using Aggregate Methods

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

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

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

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

$total = DB::table('users')->sum('votes');

//Raw Expressions

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

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

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

->groupBy('status')

->get();

//递增或递减一个列的值

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, array('name' => 'John'));

//将记录插入表

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

array('email' => 'john@example.com', 'votes' => 0)

);

//将记录插入表自动增加的ID

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

array('email' => 'john@example.com', 'votes' => 0)

);

//注意:当使用PostgreSQL insertGetId方法预计,自增列被命名为“id”

//多个记录插入到表中

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

array('email' => 'taylor@example.com', 'votes' => 0),

array('email' => 'dayle@example.com', 'votes' => 0),

));

//更新一个表中的记录

DB::table('users')

->where('id', 1)

->update(array('votes' => 1));

//Deletes

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

//Unions

//查询构建器还提供了一种快速的方法来“联盟”两个查询:

$first = DB::table('users')->whereNull('first_name');

$users = DB::table('users')->whereNull('last_name')->union($first)->get();

//unionAll方法也可以,有相同的方法签名。

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

100)->sharedLock()->get();

//更新“锁”在一个SELECT语句,您可以使用lockForUpdate方法查询:

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

//缓存查询

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

//十分钟被缓存

$users = DB::table('users')->cacheTags(array('people', 'authors'))->remember(10)->get();

//事务

DB::beginTransaction();

DB::rollback();

DB::commit();

DB::transaction(function(){

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

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

});

雅荷心语博客 -心之所向便是光

联系我们关于我们

一	二	三	四	五	六	日
« 六
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

相关推荐

雅荷心语博客 -心之所向便是光