Laravel学习笔记基础系列--(二十)Laravel 使用原生SQL CURD

作者：温新

时间：2021-06-28

hi，我是温新，一名PHPer

说明：请自行配置好数据库的连接操作

有了前面两篇文章的准备且已经为数据表填充了数据，那么下面的操作就要进入到SQL查询了。

原生查询用的不多，这里，就直接使用路由进行演示。

介绍

关于查询，可以使用DB门面。select方法可用于原始语句操作。

定义路由

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 0, 0)">Route</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">get</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'sql'</span>, <span style="box-sizing: border-box;color: rgb(119, 0, 136)">function</span>(){  </span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">});</span>

演示的CURD操作都将在这里完成。

新增数据

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">// 结果返回 []</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 0, 0)">\Illuminate\Support\Facades\DB</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">select</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">"insert into users (name,email,password) values(?,?,?)"</span>,[<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'李四'</span>,<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'lisi@qq.com'</span>,<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'123456'</span>]);</span>

查询数据

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">// 查询刚刚插入的那一条数据</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">// 返回数据集合对象</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 85, 170)">$res</span> <span style="box-sizing: border-box;color: rgb(152, 26, 26)">=</span> <span style="box-sizing: border-box;color: rgb(0, 0, 0)">\Illuminate\Support\Facades\DB</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">select</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'select * from users where id = :id'</span>,[<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'id'</span><span style="box-sizing: border-box;color: rgb(152, 26, 26)">=></span><span style="box-sizing: border-box;color: rgb(17, 102, 68)">121</span>]);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 0, 0)">dd</span>(<span style="box-sizing: border-box;color: rgb(0, 85, 170)">$res</span>);</span>

修改数据

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">// 结果返回 []</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 0, 0)">\Illuminate\Support\Facades\DB</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">select</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'update users set nickname ="wangwu" where id = :id'</span>,[<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'id'</span><span style="box-sizing: border-box;color: rgb(152, 26, 26)">=></span><span style="box-sizing: border-box;color: rgb(17, 102, 68)">121</span>]);</span>

删除数据

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">// 结果返回 []</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 0, 0)">\Illuminate\Support\Facades\DB</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">select</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'delete from users where id = :id'</span>,[<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'id'</span><span style="box-sizing: border-box;color: rgb(152, 26, 26)">=></span><span style="box-sizing: border-box;color: rgb(17, 102, 68)">121</span>]);</span>

语义化方法

除了使用select方法进行原生sql的操作外，Laravel还提供了更加语义化的方法。

insert方法原生插入

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">// 插入成功返回 true</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 85, 170)">$res</span> <span style="box-sizing: border-box;color: rgb(152, 26, 26)">=</span> <span style="box-sizing: border-box;color: rgb(0, 0, 0)">\Illuminate\Support\Facades\DB</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">insert</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'insert into users (name,email,password) values (?,?,?)'</span>,[<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'李四'</span>,<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'lisi@qq.com'</span>,<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'123456'</span>]);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 0, 0)">dd</span>(<span style="box-sizing: border-box;color: rgb(0, 85, 170)">$res</span>);</span>

update方法原生修改

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">// 成功返回1，失败返回0</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 85, 170)">$res</span> <span style="box-sizing: border-box;color: rgb(152, 26, 26)">=</span> <span style="box-sizing: border-box;color: rgb(0, 0, 0)">\Illuminate\Support\Facades\DB</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">update</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'update users set nickname="wangwu" where id = :id'</span>,[<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'id'</span><span style="box-sizing: border-box;color: rgb(152, 26, 26)">=></span><span style="box-sizing: border-box;color: rgb(17, 102, 68)">122</span>]);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 0, 0)">dd</span>(<span style="box-sizing: border-box;color: rgb(0, 85, 170)">$res</span>);</span>

delete方法原生删除

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(0, 0, 0)">Route</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">get</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'sql'</span>, <span style="box-sizing: border-box;color: rgb(119, 0, 136)">function</span>(){</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">    <span style="box-sizing: border-box;color: rgb(170, 85, 0)">// 成功返回1，失败返回0</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">    <span style="box-sizing: border-box;color: rgb(0, 85, 170)">$res</span> <span style="box-sizing: border-box;color: rgb(152, 26, 26)">=</span> <span style="box-sizing: border-box;color: rgb(0, 0, 0)">\Illuminate\Support\Facades\DB</span>::<span style="box-sizing: border-box;color: rgb(0, 0, 0)">delete</span>(<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'delete from users where id = :id'</span>,[<span style="box-sizing: border-box;color: rgb(170, 17, 17)">'id'</span><span style="box-sizing: border-box;color: rgb(152, 26, 26)">=></span><span style="box-sizing: border-box;color: rgb(17, 102, 68)">122</span>]);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">    <span style="box-sizing: border-box;color: rgb(0, 0, 0)">dd</span>(<span style="box-sizing: border-box;color: rgb(0, 85, 170)">$res</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">});</span>

使用这样的原生查询，一般情况下用不到，因此当做一个了解即可。需要用的时候再查阅文档就行了。下一篇文章将进入查询的核心，查询构建器， 这才是查询的主战场。

我是温新

每天进步一点点，就一点点