Laravel之数据库

一.基本1.配置文件:config/database.php2.运行原生的sql查询$users = DB::select('select * from users where active = ?', [1]);$results = DB::select('select * from users where id = :id', ['id' => 1]);3.insertDB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);4.update$affected = DB::update('update users set votes = 100 where name = ?', ['John']);5.delete$deleted = DB::delete('delete from users');6.通用查询,不返回结果的一些查询,比如删除表DB::statement('drop table users');二.监听查询事件class AppServiceProvider extends ServiceProvider{	/**	* 启动所有应用服务	*	* @retu void	*/	public function boot()	{		DB::listen(function($sql, $bindings, $time) {		//		});	}	....}三.事务1.自动DB::transaction(function () {	DB::table('users')->update(['votes' => 1]);	DB::table('posts')->delete();});如果闭包函数中执行出错,事务回滚2.手动DB::beginTransaction()DB::rollBack();DB::commit();四.多数据库连接传递给connection 方法的连接名对应配置文件config/database.php 中相应的连接$users = DB::connection('foo')->select(...);$pdo = DB::connection()->getPdo();获取底层原生的pdo实例五.查询1.查询结果集(如果在配置文件中指定了表前缀,table方法中的表名可以不用带表前缀)a.获取所有记录,table,get$users = DB::table('users')->get();b.取出一行,first$users = DB::table('users')->first();c.获取部分结果,chunkDB::table('users')->chunk(100, function($users) {	foreach ($users as $user) {		//	}});返回 false 来中止组块的运行DB::table('users')->chunk(100, function($users) {	// 处理结果集...	retu false;});d.获取列值列表,lists$titles = DB::table('roles')->lists('title');foreach ($titles as $title) {	echo $title;}e.获取一行一列$value = DB::table('table')->where('id', 1)->pluck('name');指定键// 返回一个数组,name字段作为key,title字段作为value$roles = DB::table('roles')->lists('title', 'name');foreach ($roles as $name => $title) {	echo $title;}2.聚合函数$users = DB::table('users')->count();$price = DB::table('orders')->max('price');$price = DB::table('orders')->where('finalized', 1)->avg('price');3.指定字段$users = DB::table('users')->select('name', 'email as user_email')->get();4.不重复结果$users = DB::table('users')->distinct()->get();5.如果你已经有了一个查询构建器实例并且希望添加一个查询列到已存在的 select 子句，可以使用addSelect方法$query = DB::table('users')->select('name');$users = $query->addSelect('age')->get();6.原生语句$users = DB::table('users')->select(DB::raw('count(*) as user_count, status'))->where('status', '<>', 1)->groupBy('status')->get();六.连接1.内连接$users = DB::table('users')->join('contacts', 'users.id', '=', 'contacts.user_id')->join('orders', 'users.id', '=', 'orders.user_id')->select('users.*', 'contacts.phone', 'orders.price')->get();2.左连接$users = DB::table('users')->leftJoin('posts', 'users.id', '=', 'posts.user_id')->get();3.高级连接语句DB::table('users')->join('contacts', function ($join) {	$join->on('users.id', '=', 'contacts.user_id')->orOn(...);})->get();可以使用where风格的子句DB::table('users')->join('contacts', function ($join) {	$join->on('users.id', '=', 'contacts.user_id')->where('contacts.user_id', '>', 5);})->get();六,联合$first = DB::table('users')->whereNull('first_name');$users = DB::table('users')->whereNull('last_name')->union($first)->get();unionAll 方法也是有效的，并且和union 有同样的使用方法。七.where语句1.$users = DB::table('users')->where('votes', '=', 100)->get();如果是=,可以简写为$users = DB::table('users')->where('votes', 100)->get();可以使用其它操作符来编写where 子句$users = DB::table('users')	->where('votes', '>=', 100)	->get();$users = DB::table('users')	->where('votes', '<>', 100)	->get();$users = DB::table('users')	->where('name', 'like', 'T%')	->get();2.or$users = DB::table('users')	->where('votes', '>', 100)	->orWhere('name', 'John')	->get();3.and$users = DB::table('users')	->where('votes', '>', 100)	->where('name', 'John')	->get();4.whereBetween/whereNotBetween$users = DB::table('users')	->whereBetween('votes', [1, 100])->get();$users = DB::table('users')	->whereNotBetween('votes', [1, 100])	->get();5.whereIn/whereNotIn$users = DB::table('users')	->whereIn('id', [1, 2, 3])	->get();$users = DB::table('users')	->whereNotIn('id', [1, 2, 3])	->get();6.whereNull/whereNotNull$users = DB::table('users')	->whereNull('updated_at')	->get();$users = DB::table('users')	->whereNotNull('updated_at')	->get();6.高级wherea.参数分组DB::table('users')	->where('name', '=', 'John')	->orWhere(function ($query) {		$query->where('votes', '>', 100)->where('title', '<>', 'Admin');	})->get();等价于:select * from users where name = 'John' or (votes > 100 and title <> 'Admin')b.exists 语句DB::table('users')	->whereExists(function ($query) {	$query->select(DB::raw(1))	->from('orders')	->whereRaw('orders.user_id = users.id');})->get();等价于:select * from userswhere exists (select 1 from orders where orders.user_id = users.id)八.排序1.orderBy$users = DB::table('users')	->orderBy('name', 'desc')	->get();2.groupBy/having/havingRaw$users = DB::table('users')	->groupBy('account_id')	->having('account_id', '>', 100)->get();havingRaw 方法可以用于设置原生字符串作为having 子句的值，例如，我们要找到所有售价大于$2500 的部分：$users = DB::table('orders')->select('department', DB::raw('SUM(price) as total_sales'))->groupBy('department')->havingRaw('SUM(price) > 2500')->get();九,限定结果集(mysql的limit)skip / take$users = DB::table('users')->skip(10)->take(5)->get();十,Insert1.单条记录DB::table('users')->insert(['email' => 'john@example.com', 'votes' => 0]);2.多条记录DB::table('users')->insert([['email' => 'taylor@example.com', 'votes' => 0],['email' => 'dayle@example.com', 'votes' => 0]]);3.获取自增ID根据之前的插入的列值来获取$id = DB::table('users')->insertGetId(['email' => 'john@example.com', 'votes' => 0]);十一.更新1.普通DB::table('users')->where('id', 1)->update(['votes' => 1]);2.增加/减少,默认步长为1DB::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, ['name' => 'John']);十二.删除1.普通DB::table('users')->delete()2.whereDB::table('users')->where('votes', '<', 100)->delete();3.清空表DB::table('users')->truncate();十三.悲观锁/乐观锁(配合事务)DB::table('users')->where('votes', '>', 100)->sharedLock()->get();DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();