laravel mysql sum,Laravel 数据库操作

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 模型中使用

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'

]

],