Laravel之数据库

一.基本
1.配置文件:config/database.php

2.运行原生的sql查询
$users = DB::select('select * from users where active = ?', [1]);
$results = DB::select('select * from users where id = :id', ['id' => 1]);

3.insert
DB::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{
	/**
	* 启动所有应用服务
	*
	* @return 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.获取部分结果,chunk
DB::table('users')->chunk(100, function($users) {
	foreach ($users as $user) {
		//
	}
});

返回 false 来中止组块的运行
DB::table('users')->chunk(100, function($users) {
	// 处理结果集...
	return 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.高级where

a.参数分组
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 users
where 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();


十,Insert
1.单条记录
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.增加/减少,默认步长为1
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, ['name' => 'John']);


十二.删除
1.普通
DB::table('users')->delete()

2.where
DB::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();