laravel CURD

检索一个列值列表
DB::table("tablename")->lists('mobile'); //5.3 及以上版本  lists 改为  pluck

返回

[

"13455556666",

"13455556667",

"13455556668",

"13455556669",

]

指定一个自定义的键列返回的数组

\DB::table('tablename')->lists('mobile','email');

返回

[

"aa@sina.com"=>"13022223335",

"bb@sina.com"=>"13022223336",

"cc@sina.com"=>"13022223337",

]

检索表中的所有行

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

foreach ($users as $user)

{

    var_dump($user->name);

}

从表检索单个行

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

var_dump($user->name);

检索单行单列--返回指定字段

DB::table('users')->where('name', 'John')->pluck('name');
返回数组 非字符串
["Jone"] 并不是返回 "Jone"

指定一个Select子句

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

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

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

where

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

$users = DB::table('users')->where(['votes'=>100,'name'=>'zhangsan'])->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')->where('parent_id',1)->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();
//on 多个条件

DB::table('users')

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

　　{

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

　　})

     ->get();

DB::table('users') 　　->join('contacts', function($join) 　　{ 　　$join->on('users.id', '=', 'contacts.user_id') 　　->where('contacts.user_id', '>', 5); 　　}) 　　->get();

聚合

$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');

递增或递减一个列的值

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'));

Inserts



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

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

);

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

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

);

多个记录插入到表中

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

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

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

));

Updates

DB::table('users')

->where('id', 1)

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

Deletes

删除表中的记录

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

删除表中的所有记录

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

清空一个表

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

共享锁

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

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

更新“锁”

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

\DB::connection()->getPdo()->exec($sql)

事务 使用匿名函数 任何一个异常都会触发回滚

        return \DB::transaction(function() use($user_params, $staff_params) {

            //写管理员进入user表

            $user_id = DB::table(User::TABLE_NAME)

                ->insertGetId($user_params);

            if(!$user_id) throw new Exception("写入user表失败");

            $staff_params[Staff::DB_FIELD_USER_ID] = $user_id;

            $staff_id=DB::table(Staff::TABLE_NAME)

                ->insertGetId($staff_params);

            if(!$staff_id) throw new Exception("写入staff表失败");

            return $staff_id;

        });

直接使用语句

$ret = DB::select("select * from tablename where id=123"); //返回一个二维数组
或者

$ret = DB::select("select * from tablename where id=?",[123]); //返回一个二维数组

使用命名绑定来执行查询

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

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

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

$deleted = DB::delete('delete from users');

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

//chunk()每次查n条

$student=DB::table("vipinfo")->chunk(2,function($students){  //每次查2条

    var_dump($students);

    if(.......) return false;  //在满足某个条件下使用return就不会再往下查了

});

打印sql
\DB::connection()->enableQueryLog();#开启log

$aa = \DB::table(tablename)->first();

$log = \DB::getQueryLog();

dd($log);

//打印

array: [

   => array: [

    "query" => "select * from `fuli_xwc_merchant` limit 1"

    "bindings" => []

    "time" => 29.27

  ]

]

手动使用事务


如果你想要手动开始事务从而对回滚和提交有一个完整的控制，可以使用 DB 门面的beginTransaction 方法：

DB::beginTransaction();

你可以通过 rollBack 方法回滚事务：

DB::rollBack();

最后，你可以通过 commit 方法提交事务：

DB::commit();

注意：使用 DB 门面的事务方法还可以用于控制查询构建器和 Eloquent ORM 的事务。

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

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

            ->get();

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

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

            ->get();

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

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

            ->get();

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

    ['status', '=', '1'],

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

])->get();

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

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

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

                ->get();

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

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

　　null

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

                ->whereNull('updated_at')

                ->get();

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

                ->whereNotNull('updated_at')

                ->get();

　　从一张表中获取一行/一列

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

echo $user->name;//该方法将会返回单个 StdClass 对象：

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

DB:raw

\DB::table(\DB::raw("xl_channel_goods as a"))

           ->whereIn(ChannelGood::DB_FIELD_CHANNEL_GOODS_CODE, $codes)

            ->get([

                'a.*',

                \DB::raw("(select goods_name from xl_goods as b where a.goods_sn=b.goods_sn) as goods_name"),

                \DB::raw("(select group_concat(upc)  from xl_goods_upc as c where a.goods_sn=c.goods_sn group by c.goods_sn) as upc")

            ]);

巴特西

laravel CURD

最新文章

热门文章