laravel学习记录——基本数据库增删改查操作

原生sql：

1.原生select查询

我装的是laravel5.7版本

 public function homeInfo()
 {
     //查询
     $name = 'lee';
     $user = DB::select('select * from `users` where `name` = ?',[$name]);
     dd($user);
 }
#也可以直接命名要绑定的参数
     $user = DB::select('select * from `users` where `name` = :name',['name'=>$name]);

查询结果是一个stdClass对象数组

2.原生insert插入

#若插入成功，则会返回true
$name = '农夫';
$email = 'nonfu@gmail.com';
$password = bcrypt('123456');
$flag = DB::insert('insert into `users` (`name`,`email`,`password`) value (?,?,?)',[$name,$email,$password]);
dd($flag);

然后看一下数据库，成功添加进去了

3.原生update更新

$name = Str::random(8);
$id =4;
$affectRows = DB::update('update `users` set `name` = ? where `id` = ?',[$name,$id]);
dd($affectRows);

4.原生delete删除

$id = 4;
$affectRows = DB::delete('delete from `users` where id = ?',[$id]);
dd($affectRows);

已经成功删除了

laravel自带的查询构造器

1.查询：

$name = 'lee';
$user = DB::table('users')->where('name',$name)->get();

查询的结果是一个stdClass对象数组

如果想要只查询单条数据

$user = DB::table('users')->where('name',$name)->first();

如果你只想查询指定字段的话

$name = 'lee';
$user = DB::table('users')->select('id', 'name', 'password')->where('name', $name)->first();

2.插入

$flag = DB::table('users')->insert([
			'name' => Str::random(10),
			'email' => Str::random(8) . '@gmail.com',
			'password' => bcrypt('123456')
		]);
dd($flag);

如果你需要插入时返回主键id

$flag = DB::table('users')->insertGetId([
			'name' => Str::random(10),
			'email' => Str::random(8) . '@gmail.com',
			'password' => bcrypt('123456')
		]);

一次插入多条

$flag = DB::table('users')->insert([
	['name' => Str::random(10), 'email' => Str::random(8) . '@gmail.com', 'password' => bcrypt('123456')],
	['name' => Str::random(10), 'email' => Str::random(8) . '@163.com', 'password' => bcrypt('123456')],
	['name' => Str::random(10), 'email' => Str::random(8) . '@qq.com', 'password' => bcrypt('123456')],
]);

3.更新

$client_id = 2;
$affectedRows = DB::table('client')->where('client_id', $id)->update(['name' => Str::random(8)]);

#where有第二个参数，如果不写的话默认第二个参数是‘=’
$affectedRows = DB::table('client')->where('client_id','>',$id)->update(['name' => Str::random(8)]);

某个字段增加数量

$affetctRows = DB::table('client')->where('client_id', 1)->increment('nums',5);//nums字段加5

4.删除

$client_id = 3;
$affectedRows = DB::table('client')->where('id', '>=', $id)->delete();

删除会返回影响行数

清空表并充值自增主键ID

$affectedRows = DB::table('client')->truncate();