mysql
DB
运行原始语句
- select 查找
// 参数绑定
$users = DB::select('select * from users where active = ?', [1]);
// 命名绑定
$results = DB::select('select * from users where id = :id', ['id' => 1]);
- insert 插入
// 返回受影响的行数
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
- update 更新
// 返回受影响的行数
$affected = DB::update('update users set votes = 100 where name = ?', ['John']);
- delect 删除
// 返回受影响的行数
$deleted = DB::delete('delete from users');
- 运行一般声明
不返回任何参数
DB::statement('drop table users');
- 数据库事务
// 闭包事务
DB::transaction(function () {
DB::table('users')->update(['votes' => 1]);
DB::table('posts')->delete();
});
// 手动事务
// 开启事务
DB::beginTransaction();
// 事务还原
DB::rollBack();
// 事务提交
DB::commit();
- 使用多数据库连接
$users = DB::connection('foo')->select(...);
查询构造器
- 查询 user 表所有记录
$users = DB::table('users')->get();
- 查询单条记录
$user = DB::table('users')->where('name', 'John')->first();
$email = DB::table('users')->where('name', 'John')->value('email');
- 将结果切块
DB::table('users')->chunk(1, function($users) {
foreach ($users as $user) {
if($user->id === 1){
echo $user->name;
return false;
}
}
});
- 获取某个字段值列表
// 返回包含单个字段值的数组
$nameArr = DB::table('users')->lists('email');
// 返回的数组中指定自定义的键值字段
$users = DB::table('users')->lists('email','name');
- 聚合操作（count、max、min、avg、以及 sum）
$users = DB::table('users')->count();
$price = DB::table('orders')->where('finalized', 1)->max('price');
- 取出数据表中的部分字段
$users = DB::table('users')->select('name', 'email as user_email')->get();
- 去除重复的值
$time = DB::table('users')->select('created_at')->distinct()->get();
- 在原有查询构造器实例的select子句中增加一个字段
$query = DB::table('users')->select('name');
$users = $query->addSelect('email')->get();
- 原始表达式
$users = DB::table('users')->select(DB::raw('count(*) as user_count, status'))->where('status', '<>', 1)->groupBy('status')->get();
- Inner Join 内链接
$users = DB::table('users')->join('tasks', 'users.id', '=', 'tasks.user_id')->select('users.*', 'tasks.name as taskname')->get();
- Left Join 外链接
$users = DB::table('users')->leftJoin('tasks', 'users.id', '=', 'tasks.user_id')->select('users.*', 'tasks.name as tname')->where('users.name', 'jorly')->get();
- 高级的 Join 语法
$users = DB::table('users')->join('tasks', function ($join) {
$join->on('users.id', '=', 'tasks.user_id')->where('users.id', '=', 1);
})->get();
- union 和 unionAll 联合查询
$first = DB::table('users')->where('id',1);
$users = DB::table('users')->where('id',2)->union($first)->get();
- where 和 orWhere 查询
$users = DB::table('users')->where('votes', 100)->get();
$users = DB::table('users')->where('id', '=', 1)->orWhere('name', 'jorly')->get();
- whereBetween 和 whereNotBetween 查询
$users = DB::table('users')->whereBetween('id', [2, 5])->get();
$users = DB::table('users')->whereNotBetween('id', [2, 5])->get();
- whereIn 和 whereNotIn 查询
$users = DB::table('users')->whereIn('id', [2, 5])->get();
$users = DB::table('users')->whereNotIn('id', [2, 5])->get();
- whereNull 和 whereNotNull 查询
$users = DB::table('users')->whereNull('updated_at')->get();
$users = DB::table('users')->whereNotNull('updated_at')->get();
- where 分组查询
// select * from users where name = 'John' or (votes > 100 and title <> 'Admin')
DB::table('users')->where('name', '=', 'John') ->orWhere(function ($query) {
$query->where('votes', '>', 100)->where('title', '<>', 'Admin');
})->get();
- orderBy 排序查询
$users = $users = DB::table('users')->orderBy('id', 'desc')->get();
- groupBy、having 与 havingRaw
$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();
- skip 与 take
$tasks = DB::table('tasks')->skip(1)->take(2)->get();
- insert 插入数据
$tasks = DB::table('tasks')->insert([
['user_id' => 3, 'name' => 'demo1'],
['user_id' => 3, 'name' => 'demo2']
]);
- 插入记录并获取自增ID
$tasks = DB::table('tasks')->insertGetId(['user_id' => 4, 'name' => 'task1']);
- update 更新数据
$result = DB::table('users')->where('id', 1)->update(['name' => 'ljh', 'email' => 'lijinhe@hudong.com']);
- 递增或递减
$result = DB::table('tasks')->where('id', 10)->increment('user_id', 5);
DB::table('users')->increment('votes', 1, ['name' => 'John']);
ORM
- 通过模型 Flight 查询 flights 表所有的记录
$flights = Flight::all();
- 模型中可使用任可查询构造器
$flights = Flight::where('active', 1)->orderBy('name', 'desc')->take(10)->get();
- 分块结果
Flight::chunk(200, function ($flights) {
foreach ($flights as $flight) {}
});
- 取回单个模型
$flight = Flight::find(1);
$flight = Flight::where('active', 1)->first();
- 「未找到」异常
$model = Flight::findOrFail(1);
$model = Flight::where('legs', '>', 100)->firstOrFail();
- 聚合函数
$count = Flight::where('active', 1)->count();
- 数据添加
$flight = new Flight;
$flight->name = $request->name;
$flight->save();
// 需要定义 $fillable 或 $guarded
$flight = Flight::create(['name' => 'Flight 10']);
// 当结果不存在时创建
$flight = Flight::firstOrCreate(['name' => 'Flight 10']);
// 当结果不存在时实例化
$flight = Flight::firstOrNew(['name' => 'Flight 10']);
$flight->save();
- 数据更新
$flight = Flight::find(1);
$flight->name = 'New Flight Name';
$flight->save();
Flight::where('active', 1)->where('destination', 'San Diego')->update(['delayed' => 1]);
- 删除数据
$flight = Flight::find(1);
$flight->delete();
// 通过主键来删除
Flight::destroy(1);
Flight::destroy([1, 2, 3]);
Flight::destroy(1, 2, 3);
// 通过查找来删除
$deletedRows = Flight::where('active', 0)->delete();
- 软删除（假删除）
// 需要使用 Illuminate\Database\Eloquent\SoftDeletes trait 并添加 deleted_at 字段到你的 $dates 属性上
// 需要被转换成日期的属性
protected $dates = ['deleted_at'];
mongodb
- 使用 composer 进行安装
$ composer require jenssegers/mongodb
- 在 config/app.php 注册 ServiceProvider 和 Facade（Laravel 5.5+ 无需手动注册）
'providers' => [
// ...
Jenssegers\Mongodb\MongodbServiceProvider::class,
],
'aliases' => [
// ...
'Moloquent' => Jenssegers\Mongodb\Eloquent\Model::class,
],
- 在 Eloquent 模型中使用
<?php
namespace App\Models;
use Jenssegers\Mongodb\Eloquent\Model;
use Jenssegers\Mongodb\Eloquent\HybridRelations;
class User extends Model
{
use HybridRelations;
// 连接的数据
protected $connection = 'mongodb';
// 与模型关联的集
protected $collection = 'user';
// 定义文档的主键
protected $primaryKey = '_id';
// 不可被批量赋值的属
protected $guarded = [];
// 附加到访问器模型的数
protected $appends = [];
// 应该被转换成原生类型的属
protected $casts = [
'data' => 'array'
];
}
关联模型使用示例：
$books = $user->books()->sortBy('title');
- 修改 config/database.php 默认连接方式
'default' => env('DB_CONNECTION', 'mongodb')
- 增加 mongodb 连接方式
'mongodb' => [
'driver' => 'mongodb',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', 27017),
'database' => env('DB_DATABASE'),
'username' => env('DB_USERNAME'),
'password' => env('DB_PASSWORD'),
'options' => [
'database' => 'admin'
]
],
