乐趣区

关于php:laravel-数据库操作

jiezi

一：数据库配置

数据库配置文件搁置在 config/database.php 文件中

1：根底配置 

'connections' => [
    'mysql' => [
        'driver' => 'mysql',   #数据库类型
        'host' => env('DB_HOST', '127.0.0.1'),# 数据库地址
        'port' => env('DB_PORT', '3306'),# 端口号
        'database' => env('DB_DATABASE', 'forge'),# 数据库名称
        'username' => env('DB_USERNAME', 'forge'),# 用户名
        'password' => env('DB_PASSWORD', ''),# 明码'unix_socket'=> env('DB_SOCKET',''),# 应用 socket 链接
        'charset' => 'utf8mb4',# 编码
        'collation' => 'utf8mb4_unicode_ci',# 字符集
        'prefix' => '',# 表前缀'strict'=> true,'engine' => null,
    ],
],

上述的 host,port,database….. 应用了 env 函数, 他应用的是.env 文件的配置项, 你也能够不必.env 文件配置项, 间接填写相干信息, 如果你应用了.env 文件配置项, 你须要批改.env 文件内容

2：多表配置 

'connections' => [
    'mysql' => [
        'driver' => 'mysql',   #数据库类型
        'host' => env('DB_HOST', '127.0.0.1'),# 数据库地址
        'port' => env('DB_PORT', '3306'),# 端口号
        'database' => env('DB_DATABASE', 'forge'),# 数据库名称
        'username' => env('DB_USERNAME', 'forge'),# 用户名
        'password' => env('DB_PASSWORD', ''),# 明码'unix_socket'=> env('DB_SOCKET',''),# 应用 socket 链接
        'charset' => 'utf8mb4',# 编码
        'collation' => 'utf8mb4_unicode_ci',# 字符集
        'prefix' => '',# 表前缀'strict'=> true,'engine' => null,
    ],
    'mysql001' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'port' => '3306',
        'database' => 'blog',
        'username' => 'root',
        'password' => 'root',
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '','strict'=> false,'engine' => null,
    ],
],

3：主从数据库配置 

'connections' => [
    'mysql' => [
        // 读库地址
        'read' => [
            'host' => [
                '192.168.1.1',
                '196.168.1.2',
            ],
        ],
        // 写库地址
        'write' => [
            'host' => ['196.168.1.3',],
        ],
        'driver' => 'mysql',   #数据库类型
        'database' => env('DB_DATABASE', 'forge'),# 数据库名称
        'username' => env('DB_USERNAME', 'forge'),# 用户名
        'password' => env('DB_PASSWORD', ''),# 明码'unix_socket'=> env('DB_SOCKET',''),# 应用 socket 链接
        'charset' => 'utf8mb4',# 编码
        'collation' => 'utf8mb4_unicode_ci',# 字符集
        'prefix' => '',# 表前缀'strict'=> true,'engine' => null,
    ],
],

二：数据库根底操作

1：数据查问 

$users = DB::select('select * from user'); #查问默认 mysql 的 user 表所有数据
$users = DB::select('select * from user where id = ?',[1]); #查问默认 mysql 的 user 表 id 等于 1 的数据
$users = DB::select('select * from user where id = ? and user_no = ?',[1,'001']);# 查问默认 mysql 的 user 表 id 等于 1 且 user_no 等于 001 的数据
$users = DB::select('select * from user where id = :id and user_no = :user_no',['id'=>1,'user_no'=>'001']);# 查问默认 mysql 的 user 表 id 等于 1 且 user_no 等于 001 的数据
$article = DB::connection('mysql001')->select('select * from article'); #查问 mysql001 库的 article 表数据
$article = DB::connection('mysql001')->select('select * from article where id = ?',[1]); #查问 mysql001 库的 article 表 id 等于 1 的数据
$article = DB::connection('mysql001')->select('select * from article where id = ? and column_no = ?',[1,'COL00005']);# 查问 mysql001 库的 article 表 id 等于 1 且 column_no 等于 COL00005 的数据
$article = DB::connection('mysql001')->select('select * from article where id = :id and column_no = :column_no',['id'=>1,'column_no'=>'COL00005']);# 查问 mysql001 库的 article 表 id 等于 1 且 column_no 等于 COL00005 的数据

2：数据插入 

DB::insert('insert into user (user_no,user_name) values (?, ?)', ['002','test']);# 向默认 mysql 的 user 表插入一条数据
DB::connection('mysql001')->insert('insert into article (title,column_no,content) values (?, ?, ?)',['test','COL00005','testcontent']);# 向 mysql001 库的 article 表插入一条数据

3：数据更新 

DB::update('update user set user_name = ? where id = ?', ['test',1]); #更新默认 mysql 的 user 表 id 等于 1 的 user_name 字段为 test
DB::connection('mysql001')->update('update article set title = ? where id = ?', ['test',1]);# 更新 mysql001 库的 article 表的 id 等于 1 的 title 字段为 test

4：数据删除 

DB::delete('delete from user where id = ?',[1]); 删除默认 mysql 的 user 表 id 等于 1 的数据
DB::connection('mysql001')->delete('delete from article where id = ?',[1]); 删除 mysql001 库的 article 表 id 等于 1 的数据

5：数据库事务 

DB::transaction(function () {//sql 操作});
DB::transaction(function () {//sql 操作}, 5);

传递第二个可选参数给 transaction 办法，该参数定义在产生死锁时应该从新尝试事务的次数。一旦尝试次数都用尽了，就会抛出一个异样

6：手动操作事务

如果你想要手动开始一个事务，并且可能齐全管制回滚和提交，那么你能够应用 beginTransaction 办法实现 

DB::beginTransaction();

回滚事务 

DB::rollBack();

提交事务 

DB::commit();

三：查问结构器

Laravel 的数据库查问结构器提供了一个不便的接口来创立及运行数据库查问语句。它能用来执行应用程序中的大部分数据库操作，且能在所有被反对的数据库系统中应用

1：数据查问

(1)：简略查问 

// 获取所有数据
$users = DB::table('user')->get();
// 获取 mysql001 库的 article 表数据
$article = DB::connection('mysql001')->table('article')->get();
// 获取一行数据
$users = DB::table('user')->where('id', 1)->first();
// 从记录中取出单个值。该办法将间接返回字段的值
$users = DB::table('user')->where('id', 1)->value('user_name');
// 获取一列的值
$users = DB::table('user')->pluck('user_name');
// 获取一列的值，并以指定字段为键值
$users = DB::table('user')->pluck('user_name','user_no');

(2)：分块查问

如果你须要操作数千条数据库记录，能够思考应用 chunk 办法。这个办法每次只取出一小块后果传递给 闭包 解决 

DB::table('user')->orderBy('id')->chunk(100, function ($users) {foreach ($users as $user) {//}
});

你能够从 闭包 中返回 false 来阻止进一步的分块的解决 

DB::table('user')->orderBy('id')->chunk(100, function ($users) {
    //
    return false;
});

如果要在分块后果时更新数据库记录，则块后果可能会和预计的返回后果不统一。因而，在分块更新记录时，最好应用 chunkById 办法。此办法将依据记录的主键主动对后果进行分页：

DB::table('users')->where('active', false)
    ->chunkById(100, function ($users) {foreach ($users as $user) {DB::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });

(3)：聚合办法

查问结构器还提供了各种聚合办法，比方 count, max，min，avg，还有 sum。你能够在结构查问后调用任何办法：

/ 查问数据条数
$users = DB::table('user')->count();
// 查问数据中最大的 price 值
$users = DB::table('goods')->max('price');
// 查问数据中 price 值的平均值
$users = DB::table('goods')->avg('price');

(4)：判断记录是否存在 

DB::table('orders')->where('finalized', 1)->exists();// 判断记录是否存在
return DB::table('orders')->where('finalized', 1)->doesntExist();// 判断记录是否不存在

(5)：selects

你并不会总是想从数据表中选出所有的字段，这时可应用 select 办法自定义一个 select 语句来指定查问的字段：

$users = DB::table('user')->select('name', 'email as user_email')->get();

distinct 办法容许你强制让查问返回不反复的后果：

$users = DB::table('user')->distinct()->get();

如果你已有一个查问结构器实例，并且心愿在现有的 select 语句中退出一个字段，则能够应用 addSelect 办法：

$query = DB::table('user')->select('name');
$users = $query->addSelect('age')->get();

(6)：原生表达式

有时候你可能须要在查问中应用原生表达式，应用 DB::raw 办法能够创立原生表达式 

$users = DB::table('user')
             ->select(DB::raw('count(*) as user_count, status'))
             ->where('status', '<>', 1)
             ->groupBy('status')
             ->get();

(7)：原生办法

能够应用以下的办法代替 DB::raw 将原生表达式插入查问的各个局部

  1. selectRaw

selectRaw 办法能够用来代替 select(DB::raw(…))。这个办法的第二个参数承受一个可选的绑定参数的数组：

$orders = DB::table('orders')
        ->selectRaw('price * ? as price_with_tax', [1.0825])
        ->get();
  1. whereRaw / orWhereRaw

能够应用 whereRaw 和 orWhereRaw 办法将原生的 where 语句注入到查问中。这些办法承受一个可选的绑定数组作为他们的第二个参数 

$orders = DB::table('orders')
        ->whereRaw('price > IF(state ="TX", ?, 100)', [200])
        ->get();
  1. havingRaw / orHavingRaw

havingRaw 和 orHavingRaw 办法可用于将原生字符串设置为 having 语句的值 

$orders = DB::table('orders')
        ->select('department', DB::raw('SUM(price) as total_sales'))
        ->groupBy('department')
        ->havingRaw('SUM(price) > 2500')
        ->get();
  1. orderByRaw

orderByRaw 办法可用于将原生字符串设置为 order by 语句的值 

$orders = DB::table('orders')
        ->orderByRaw('updated_at - created_at DESC')
        ->get();

(8)：连表查问 (join)

  1. Inner Join 语句 (内连贯)
$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();
  1. Left Join / Right Join 语句 (左连贯 / 右连贯)
$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();
$users = DB::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();
  1. Cross Join 语句 (穿插连贯)
$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();
  1. 高级 Join 语句

你能够指定更高级的 join 语句。比方传递一个 闭包 作为 join 办法的第二个参数。此 闭包 接管一个 JoinClause 对象，从而在其中指定 join 语句中指定束缚 

DB::table('users')
        ->join('contacts', function ($join) {$join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();
  1. 子连贯查问

你能够应用 joinSub，leftJoinSub 和 rightJoinSub 办法关联一个查问作为子查问。他们每一种办法都会接管三个参数：子查问，表别名和定义关联字段的闭包 

$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();
  1. Unions 查问

查问结构器还提供了将两个查问「合并」起来的快捷方式。例如，你能够先创立一个初始查问，并应用 union 办法将它与第二个查问进行合并 

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

(9)：where 语句 

//id 字段的值等于 100 的查问
$users = DB::table('users')->where('id', '=', 100)->get(); 等同于 $users = DB::table('users')->where('id', 100)->get();

//id 字段的值大于等于 100 的查问
$users = DB::table('users')
                ->where('id', '>=', 100)
                ->get();
                
//id 字段的值不等于 100 的查问
$users = DB::table('users')
                ->where('id', '<>', 100)
                ->get();
// 含糊查问
$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();
                
$users = DB::table('users')
                ->where('name', 'like', '%T')
                ->get();
                
$users = DB::table('users')
                ->where('name', 'like', '%T%')
                ->get();
                
//status 字段等于 1,subscribed 字段不等于 1 的查问
$users = DB::table('users')->where([['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

(10)：or 查问 

$users = DB::table('users')
        ->where('votes', '>', 100)
        ->orWhere('name', 'John')
        ->get();

(11)：其它 Where 语句

  1. whereBetween/whereNotBetween

whereBetween 验证字段的值介于两个值之间,whereNotBetween 验证字段的值不在两个值之间 

$users = DB::table('users')
        ->whereBetween('votes', [1, 100])->get();
$users = DB::table('users')
        ->whereNotBetween('votes', [1, 100])
        ->get();
  1. whereIn / whereNotIn

whereIn 办法验证字段的值在指定的数组内,whereNotIn 办法验证字段的值不在指定的数组内 

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();
$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();
  1. whereNull / whereNotNull

whereNull 办法验证字段的值为 NULL，whereNotNull 办法验证字段的值不为 NULL

$users = DB::table('users')
                    ->whereNull('updated_at')
                    ->get();
$users = DB::table('users')
                    ->whereNotNull('updated_at')
                    ->get();
  1. whereDate / whereMonth / whereDay / whereYear / whereTime

whereDate 办法用于比拟字段的值和日期,whereMonth 办法用于比拟字段的值与一年的特定月份,whereDay 办法用于比拟字段的值与特定的一个月的某一天,whereYear 办法用于比拟字段的值与特定年份,whereTime 办法用于比拟字段的值与特定的工夫 

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();
$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();
$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();
$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();
$users = DB::table('users')
                ->whereTime('created_at', '=', '11:20')
                ->get();
  1. whereColumn / orWhereColumn

whereColumn 办法用于验证两个字段是否相等 

$users = DB::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();

还能够将比拟运算符传递给该办法 

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

whereColumn 办法也能够传递一个蕴含多个条件的数组。这些条件将应用 and 运算符进行连贯 

$users = DB::table('users')
                ->whereColumn([['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ])->get();

(12)：参数分组 

DB::table('user')
            ->where('name', '=', 'test')
            ->orWhere(function ($query) {$query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

产生的 SQL

select * from user where name = 'test' or (votes > 100 and title <> 'Admin')

查问 user 表中 name 字段为 test 或者 (votes 字段大于 100 和 title 字段不等于 Admin)

(13)：Where Exists 语句

此办法承受一个 闭包 参数，此闭包要接管一个查问结构器实例 

DB::table('users')
            ->whereExists(function ($query) {$query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })
            ->get();

产生的 SQL

select * from user
where exists (select 1 from orders where orders.user_id = users.id)

(14)：JSON where 语句

本个性仅反对 MySQL 5.7+ 和 Postgres、SQL Server 2016 以及 SQLite 3.9.0 数据库。能够应用 -> 运算符来查问 JSON 列数据

通过我的测试, 如果 json 的键值为数组, 查问有效

例：在 user 表中 id 为 1 的 test 字段值为 {“a”: “abc”, “b”: “def”}

$users = DB::table('user')
            ->where('test->a', 'abc')
            ->get();

(15)：排序, 分组, 分页

  1. 排序

• orderBy 排序 

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

• latest / oldest 排序

按日期对查问后果排序, 默认状况下是对 created_at 字段进行排序。或者，你能够传递你想要排序的字段名称,latest 倒序 oldest 正序 

$user = DB::table('user')
                ->latest()
                ->first();
$user = DB::table('user')
                -oldest('create_time')
                ->first();

• inRandomOrder 排序

将查问后果随机排序, 例如，你能够应用这个办法获取一个随机用户 

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();
  1. 分组

groupBy / having 对查问后果进行分组，groupBy 分组，能够将单个参数或多个参数传递给 groupBy 办法，按一个字段或多个字段进行分组，having 是一个过滤申明，是在查问返回后果集当前对查问后果进行的过滤操作，having 办法的用法和 where 办法相似 

// 查问 user 表 id 大于 100 的数据并按 order_id 进行分组
$users = DB::table('user')
                ->groupBy('order_id')
                ->having('id', '>', 100)
                ->get();
// 查问 user 表 id 大于 100 的数据并按 order_id 和 status 进行分组
$users = DB::table('user')
                ->groupBy('order_id', 'status')
                ->having('id', '>', 100)
                ->get();
  1. 分页查问

• skip / take 分页查问 

$page = 10;// 每页显示条数
$num = 1;// 第几页
$user = DB::table('user')
    ->skip(($num - 1)*$page)->take($page)
    ->get();

• limit / offset 分页查问 

$page = 10;// 每页显示条数
$num = 1;// 第几页
$user = DB::table('user')
            ->offset(($num - 1) * $page)
            ->limit($page)
            ->get();

(16)：条件语句

只有当 when 办法的第一个参数为 true 时，闭包里的 where 语句才会执行。如果第一个参数是 false，这个闭包将不会被执行 

$role = $request->input('role');
$users = DB::table('users')
                ->when($role, function ($query) use ($role) {return $query->where('role_id', $role);
                })
                ->get();

咱们还能够将另一个闭包当作第三个参数传递给 when 办法。如果第一个参数的值为 false 时，这个闭包将执行，这就相当于默认值 

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

2: 数据插入

(1)：单条数据插入 

DB::table('user')->insert(
    [
        'user_no' => '003',
        'user_name' => 'abc'
    ]
);

(2)：多条数据插入 

DB::table('user')->insert(
    [
        [
            'user_no' => '003',
            'user_name' => 'abc'
        ],
        [
            'user_no' => '004',
            'user_name' => 'def'
        ]
    ]
);

(3)：插入返回数据 ID

若数据表存在自增的 ID，则能够应用 insertGetId 办法来插入记录而后获取其 ID

留神：应用 insertGetId, 一次只能插入一条数据 

$id = DB::table('user')->insertGetId(
    [
        'user_no' => '003',
        'user_name' => 'abc'
    ]
);

3：数据更新

(1)：根底数据更新 

DB::table('user')
    ->where('id', 1)
    ->update(['user_name' => 'test']);

(2)：JSON 数据更新

更新 JSON 字段时，应该应用 -> 语法来拜访 JSON 对象中的相应键。此操作只能在反对 JSON 字段的数据库上操作

例：user 表中一个字段 test, 在 id 为 1 的 test 字段值为 {“0”: “abc”, “1”: “def”}

DB::table('user')
    ->where('id', 1)
    ->update(['test->0' => '这是我批改的']);

(3)：自增自减更新 

// 自增 1
DB::table('users')->increment('votes');
// 自增 5
DB::table('users')->increment('votes', 5);
// 自减 1
DB::table('users')->decrement('votes');
// 自减 5
DB::table('users')->decrement('votes', 5);
// 将 name 字段为 john 的 votes 字段自增 1
DB::table('users')->increment('votes', 1, ['name' => 'John']);

4：数据删除

(1)：删除数据 

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

(2)：清空表

truncate 办法，将删除所有行，并重置主动递增 ID 为零 

DB::table('users')->truncate();
退出移动版