Laravel框架-DB数据库操作

执行数据库原生语句：

方法内直接写mysql的语句

1. 新增 DB::insert()
2. 查找 DB::select()
3. 更新 DB::update()
4. 删除 DB::delete()
5. 其他操作 DB::statement()

例：

//route路由部分:
//原生查询
Route::get('admin/dbselect','admin\DbController@select');
Route::get('admin/dbinsert','admin\DbController@insert');
Route::get('admin/dbupdate/{id}','admin\DbController@update');
Route::get('admin/dbdelete/{id}','admin\DbController@delete');
Route::get('admin/dbshow/{tablename}','admin\DbController@show');

<?php 
namespace app\Http\Controllers\admin;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class DbController
{
	public function select()
	{
		//$res = DB::select("SELECT * FROM user WHERE id=?",[1]);
		$res = DB::select("SELECT * FROM user WHERE id<=:id",[2]);
		dump($res);
	}
	public function insert()
	{
		$res =DB::insert("INSERT user SET id=:id,username=:username,
			password=:password,phone=:phone,email=:email",[
			'id'=>2,
			'username'=>'dy',
			'password'=>'123456789',
			'email'=>'144524487@qq.com',
			'phone'=>'110'
		]);
		dump($res);
	}
	public function update(Request $request)
	{
		$id = $request->route('id');
		$res = DB::update("UPDATE user SET username=:username WHERE id=:id",
			['dgg',$id]);
		dump($res);
	}
	public function delete(Request $request)
	{
		$id = $request->route('id');
		$res = DB::DELETE("DELETE FROM user WHERE id=:id",
			[$id]);
		dump($res);
	}
	public function show(Request $request){
		$tablename = $request->route('tablename');
		//$res = DB::statement("DESC ".$tablename);
		$res = DB::select("DESC ".$tablename);
		dump($res);
	}
}
?>

数据库构造器：

常用的方法：
1. 链式方法:
select order groupby offset limit
2. 查询条件:
where whereBetween whereIn
whereNull whereDate whereColumn
3.生成结果的末端方法：
get first find value pluck chunk
count max min avg sum exists tosql
4 写操作方法:
insert、 insertGetId：新增后返回新增的id
update、 increment/decrement:某字段值的自增/自减
delete、 truncate：清空表自增id重置

例：

//route路由部分
//数据库构造器
Route::get('home/dbselect','home\DbController@select');
Route::get('home/dbinsert','home\DbController@insert');
Route::get('home/dbupdate/{id}','home\DbController@update');
Route::get('home/dbdelete/{id}','home\DbController@delete');

<?php
namespace app\Http\Controllers\home;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class DbController{
	//查操作
	public function select(){
		//get()   获取全部记录
		$res = DB::table('user')->get();
		//first() 获取第一条记录
		$res = DB::table('user')->first();
		//find()  获取主键id为2的记录
		$res = DB::table('user')->find('2');
		//获取某一个字段
		$res = DB::table('user')->where('id','2')->value('username');
		//获取某一列
		$res = DB::table('user')->pluck('phone','username');
		$res = DB::table('user')->where('id','<','3')->pluck('username');
		//分页
		//跳过两条记录 查询出一条
		$res = DB::table('user')->skip(2)->take('1')->get();
		$res = DB::table('user')->offset(2)->limit(1)->get();
		//条件 and
		$res = DB::table('user')->where([
			['phone','=','110'],['username','=','lsy']
		])->tosql();
		//条件 or 
		$res = DB::table('user')->orwhere([
			['phone','=','110'],['username','=','lsy']
		])->tosql();
		$res = DB::table('user')->whereRow('phone = 110')->tosql();
		//between
		$res = DB::table('user')->whereBetween('id',[2,8])->get();
		//in
		$res = DB::table('user')->whereIn('id',[3,0,7])->get();
		//随机查询
		$res = DB::table('user')->inRandomOrder()->first();
		//设置查询的字段
		$res = DB::table('user')->select('id','phone')
				->addSelect('email')->get();
		$res = DB::table('user')->selectRaw('id,email,phone')->get();
		dump($res);
	}
	//新增 
	public function insert(){
		$data = [
				'username'=>'ll',
				'password'=>'666',
				'email'=>'14478@qq.com',
				'phone'=>'120'];
		//$res = DB::table('user')->insert($data);
		$res = DB::table('user')->insertGetId($data);
		var_dump($res);
	}
	//更新
	public function update(Request $request){
		$id = $request->route('id');
		$res = DB::table('user')->where('id',$id)->update(['username'=>'黄蓉']);
		var_dump($res);
	}
	//删除
    public function delete(Request $request){
		$id = $request->route('id');
		$res = DB::table('user')->where('id',$id)->delete();
		var_dump($res);
	}
}
?>