Laravel 数据库操作

2019-12-09 本文已影响0人 phpnet

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'
    ]
],

Laravel 数据库操作

mysql

DB

运行原始语句

查询构造器

ORM

mongodb

猜你喜欢

热点阅读