laravel常用ORM

拼搏现实的明天。 2023-10-06 16:24 58阅读 0赞

## 介绍 ##

laravel的数据库查询构造器提供了一个方便用于创建和运行数据库查询。

laravel的数据库查询构造器使用pdo参数绑定来防止应用遭到sql注入攻击，不需要对绑定的参数过滤了。

配置  
select  
原生语句  
joins  
unions  
where条件  
ordering, grouping, limit & offset  
inserts  
updates  
deletes  
事务  
悲观锁  
调试

## 1 配置 ##

laravel与不同的数据库后端的数据库进行交互十分简单。  
配置文件：在config/database.php配置

### 数据库读写分离配置 ###

指定读数据库服务器：select查询语句读取数据。

指定写数据库服务器：insert,update,delete更新语句更新数据。

当你使用原生，查询构造器，查询模型都会遵从这个操作不同的数据库。

'mysql' => [
        'read' => [
            'host' => [
                '192.168.1.1',
                '196.168.1.2',
            ],
        ],
        'write' => [
            'host' => [
                '196.168.1.3',
             ],
        ],
        'sticky'    => true,
        'driver'    => 'mysql',
        'database'  => 'database',
        'username'  => 'root',
        'password'  => '',
        'charset'   => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix'    => '',
    ],

读取和写服务器相同的属性可以写在它们的最外层，当然也可以写在里面，例如host，username,password这些。

### 简单使用 ###

#### 使用不同配置连接数据库 ####

`$users = DB::connection('read')->select(...);`

#### 运行原生的sql语句 ####

方法：`select(),update(),insert(),delete(),statement()`  
第一个参数是sql语句，第二个参数绑定的数据。  
通过’ ? ‘,’ :name '来绑定数据，可以预防sql注入。

//查询
    DB::select(‘select * from users where active = ?’,[1]）;
    //更新
    DB::update('update users set votes = 100 where name = :name', ['name' => 'John'])
    //无返回操作
    DB::statement('drop table users')

###### 取值collection类型 ######

返回的值是collection类型，collection是model的集合，model是php标准类(stdClass)实例，model可以直接进行操作。

//取值多行
    $users = DB::table('tableName')->get();
    //取第一行
    $user = DB::table('tableName')->where('field', 'value')->first();
    $user = DB::table('users')->find(3);
    //取一行的某一个值
    $email = DB::table('tableName')->where('field', 'value')->value('email')  
    //取列
    $titles = DB::table('roles')->pluck('field');
    //一列为键，一列为值
    $roles = DB::table('roles')->pluck('fieldVaule', 'fieldKey');

###### 聚合方法 ######

例如：`count(), max(), min(), avg(), sum(),exist(), doesntExist()`  
示例：$users = DB::table(‘users’)->count();

## 2 select ##

# 指定查找需要的字段（列），建议每次都指定。
    select(fieldColumn1, fieldCoulum2....)
    #在使用select()之后，再增加指定列：addSelect(fieldColumn1, fieldCoulum2....)
    #取不同的值 ： distinct()
    DB::table('users')->select('name')->addSelect('age')->distinct()->get();

## 3 原生表达式 ##

#### 原生语句方法 ####

DB::raw();
    selectRaw()   （和方法select(DB::raw(...))是一样的效果）
    whereRaw() / orWhereRaw()
    orderByRaw()
    groupByRaw()
    havingRaw() / orHavingRaw()

##### 示例 #####

$users = DB::table('users')->select(DB::raw('count(*) as user_count, status'))
    ->whereRaw('age > ?',[25])
    ->groupBy('status')
    ->get();

注意原声表达式中有引号时的使用：

当原声语句中用到引号(’,")时，使用参数绑定’?'可能不会替换参数。 示例： 问题语句：

whereRaw("produce_excepts.created_by like '%?%' ",['Administrator']) //无法得到正确结果 
    # 正确写法：
    whereRaw("produce_excepts.created_by like ?",['%Administrator%']) 
    # 当然也可以放进去：
    whereRaw("produce_excepts.created_by like '%Administrator%' ")

## 4 表连接 JOIN ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]  
方法：内联join(), 左联leftJoin, 右连rightJoin , 外联crossJoin()

$users = DB::table('users')
                ->join('contacts', 'users.id', '=', 'contacts.user_id')
                ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
                ->select('users.*', 'contacts.phone', 'orders.price')
                ->get();

### join从句 ###

DB::table('users')
            ->join('contacts', function ($join) {
        
                $join->on('users.id', '=', 'contacts.user_id')
       ->orOn('users.id','=','contacts.urgency_user_id')
                     ->where('contacts.user_id', '>', 5);
            })
            ->get();

### join子查询连接 ###

将一个查询作为子查询进行连接

$latestPosts = DB::table('posts')
                       ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                       ->where('is_published', true)
                       ->groupBy('user_id');
    
    $users = DB::table('users')
            ->joinSub($latestPosts, 'latest_posts', function ($join) {
        
                $join->on('users.id', '=', 'latest_posts.user_id');
            })->get();

### join 联合 ###

方法：union(),unionAll ()

$first = DB::table('users')
                ->whereNull('first_name');
    
    $users = DB::table('users')
                ->whereNull('last_name')
                ->union($first)
                ->get();

## 5 where 条件 ##

//operator：>,<,=,!=, <>,like等
    where('field','operator','value') 
    where('field','value')等同where('field','=','value') 
    where([['field1','operator','value1'],['field2','operator','value2'],....])
    orWhere('field','operator','value')
    whereBetween ('field',[startValue, endValue])
    whereIn('field',[value1, value2, value3,...])
    whereNotIn('field',[value1, value2, value3,...])
    whereNull('field')
    whereNotNull('field')
    whereDate / whereMonth / whereDay / whereYear / whereTime
    whereColumn('field1','operator','field2') / orWhereColumn('field1','operator','field2')
    where(function($query){
        })
    whereExists()
    json字段的查询使用"->"： ->where('options->language', 'en')

### where从句 ###

$users = DB::table('users')
               ->where('name', '=', 'John')
               ->where(function ($query) {
        
                   $query->where('votes', '>', 100)
                         ->orWhere('title', '=', 'Admin');
               })
               ->get();
    //select * from users where name = 'John' and (votes > 100 or title = 'Admin')

### where子查询 ###

$users = User::where(function ($query) {
        
        $query->select('type')
            ->from('membership')
            ->whereColumn('user_id', 'users.id')
            ->orderByDesc('start_date')
            ->limit(1);
    }, 'Pro')->get();
    
    //sql语句
    select * from `users` where ‘Pro’ = (select type from membership where user_id=users.id order by start_date limit 1);

**注意laravel7以上版本这个才生效。**

### where Exists 从句 ###

$users = DB::table('users')
               ->whereExists(function ($query) {
        
                   $query->select(DB::raw(1))
                         ->from('orders')
                         ->whereRaw('orders.user_id = users.id');
               })
               ->get();
    
    //sql语句
    select * from users
    where exists (
        select 1 from orders where orders.user_id = users.id
    )

## 6 排序，分组，分页 ##

排序：orderBy('field','desc')
    按created_at字段排序 : latest()/oldest
                 随机排序: inRandomOrder()
                 重新排序： reorder()
                    聚集：groupBy() | having() | havingRaw()
                    跳过：skip()/take()
                    分页： offset(), limit()

## 7 条件语句 when ##

when相当于if存在执行不存在不执行。

$users = DB::table('users')->when($sortBy, function ($query) use ($sortBy) {
        
                        return $query->orderBy($sortBy);
                    }, function ($query) {
        
                        return $query->orderBy('name','desc');
                    }) ->get();

when() 第一个参数为true时，执行第二个参数闭包。为false时，执行第三个参数闭包。 第三个参数是可选的。

## 7 插入 insert ##

//插入一条记录
    insert(['field1' => 'value1', 'field2' => 'value2',....])
    //插入多条记录
    insert(['field1' => 'value1', 'field2' => 'value2',....],['field11' => 'value11', 'field12' => 'value12',....])
    //插入并返回id
    insertGetId(['field1' => 'value1', 'field2' => 'value2',....], 'id')
    //插入多条记录，并忽略重复记录错误。
    insertOrIgnore(['field1' => 'value1', 'field2' => 'value2',....],['field11' => 'value11', 'field12' => 'value12',....])

示例

DB::table('users')->insert([
        ['email' => 'taylor@example.com', 'votes' => 0],
        ['email' => 'dayle@example.com', 'votes' => 0]
    ]);

1、insert 插入后会返回 true or false；

2、create 插入成功后会返回插入的数据元；

3、firstOrCreate 插入成功后会返回插入的数据元，不过若存在时间戳，则会将时间戳转换为4个字符的字符串（int 为4个字符），仍需解决；

4、firstOrNew 插入后还需添加 save 才会保证插入成功，成功也返回插入的数据元；

5、new 一个 model，以对象的形式操作，最后 save，如：

$flight = new Flight;
    $flight->name = $request->name;
    $flight->save();

总结：

如果只需插入数据而不用获取插入后的结果，insert 即可；create、firstOrCreate、firstOrNew 这几种形式需对 model 的全部字段填入 fillable，才能保证插入完整，不然有些就会以默认值存在，不过默认值的话有时也比较方便；

### 删除 ###

delete()  
truncate()

### insert,create,save比较 ###

insert 可以返回id，通过insertGetId()。  
不会自动维护created\_at,updated\_at  
没有用到模型。

create 会自动维护created\_at,updated\_at等字段。  
create有用到模型。

save 会自动维护created\_at,updated\_at等字段。  
save是针对模型。

## 8 更新 update ##

语法：update(\[‘field1’ => ‘value1’, ‘field2’ => ‘value2’,…\])

$affected = DB::table('users')
                  ->where('id', 1)
                  ->update(['votes' => 1]);

存在更新，不存在插入

DB::table('users')
        ->updateOrInsert(
            ['email' => 'john@example.com', 'name' => 'John'],
            ['votes' => '2']
        );

如果users中存在email为 'john@example.com 的，名称为john的，则更新votes为2。  
否则，插入一条email为 'john@example.com 的，名称为john的，votes为2记录。

### 字段增加/减少 ###

字段值增加：increment（‘field’,‘value’,\[‘field1’ => ‘value1’, ‘field2’ => ‘value2’,…\]）  
字段值减少：decrement（‘field’,‘value’,\[‘field1’ => ‘value1’, ‘field2’ => ‘value2’,…\]）  
示例：`DB::table('users')->increment('votes', 1, ['name' => 'John']);`

## 9 事务 ##

方法一transaction()闭包方法：

DB::transaction(function(){
         
        DB::table('users')->update(['votes' => 1]); 
        DB::table('posts')->delete(); 
    })

可以设置第二个参数：发生死锁时尝试次数。

方法二手动控制：

DB::beginTransaction();
    DB::rollBack();
    DB::commit();

save(),update(),create()等方法都受事务控制的。

### 多库事务 ###

同一个服务器，不同数据库之间的事务机制不是同一个的。 同一服务器，多个数据库，事务机制：

DB::connection('sqlsrv_hd_ext')->beginTransaction(); 
    DB::connection('sqlsrv_hd')->beginTransaction();
    
    DB::connection('sqlsrv_hd_ext')->rollBack(); 
    DB::connection('sqlsrv_hd')->rollBack();
    
    DB::connection('sqlsrv_hd_ext')->commit(); 
    DB::connection('sqlsrv_hd')->commit();

注意：不同服务器之间的事务不能用这方法。

注意 ：  
\[toArray()\]方法的使用 toArray方法转化记录集合之后后，单个记录还是一个对象实体。变为：array(model01,model02…) 查找一条记录时find(),first(),不能用toArray()方法。如果需要转化为数组使用（array）类型强制转化。

## 10 锁 ##

锁对于并发事务，保证数据的一致性。

### 悲观锁（Pessimistic Lock） ###

每次去操作数据的时候都悲观的认为别人会修改，所以每次拿数据都会上锁，阻塞别人拿到数据。

### 共享锁:（sharedLock） ###

也可以叫做读共享锁。资源上了一把锁，但是有多把钥匙。其他用户可以读，但是不可以写。

sharedLock（）

示例：

`DB::table('users')->where('votes', '>', 100)->sharedLock()->get();`

上面这个查询等价于下面这条 SQL 语句：

`select * fromuserswherevotes > '100' lock in share mode`

### 排他锁：（lockForUpdate） ###

对于多个不同的事务，同一资源上了一把锁，只有一把钥匙。其他用户不可以读和写。

对于update,insert,delete语句会自动加排它锁。

lockForUpdate（）

示例：

使用 lockForUpdate 方法。“for update”锁避免选择行被其它共享锁修改或删除：

`DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();`

上面这个查询等价于下面这条 SQL 语句：

`select * fromuserswherevotes > '100' for update`

### 乐观锁 ###

每次操作数据的时候都乐观的认为别人不会修改，所以不上锁，先取数据，然后处理业务，在最后更新的时候去判断别人在这期间有没有修改数据，可以使用版本号来判断是否更改来实现。

## 10 调试 ##

获取原生的sql语句：toSql()  
返回调试信息并停止请求：dd()  
返回调试信息请求继续：dump()

DB::table('users')->where('votes', '>', 100)->toSql();  
    DB::table('users')->where('votes', '>', 100)->dd();

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210219115352330.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70