laravel 数据库操作

一：数据库配置

数据库配置文件放置在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 将原生表达式插入查询的各个部分

selectRaw

selectRaw 方法可以用来代替 select(DB::raw(...))。这个方法的第二个参数接受一个可选的绑定参数的数组：

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

whereRaw / orWhereRaw

可以使用 whereRaw 和 orWhereRaw 方法将原生的 where 语句注入到查询中。这些方法接受一个可选的绑定数组作为他们的第二个参数

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

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();

orderByRaw

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

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

(8)：连表查询(join)

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();

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();

Cross Join 语句(交叉连接)

$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();

高级 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();

子连接查询

你可以使用 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();

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 语句

whereBetween/whereNotBetween

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

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

whereIn / whereNotIn

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

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

whereNull / whereNotNull

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

$users = DB::table('users')
                    ->whereNull('updated_at')
                    ->get();
$users = DB::table('users')
                    ->whereNotNull('updated_at')
                    ->get();

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();

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)：排序,分组, 分页

排序

• 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();

分组

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();

分页查询

• 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();

laravel 数据库操作

一：数据库配置

1：基础配置

2：多表配置

3：主从数据库配置

二：数据库基础操作

1：数据查询

2：数据插入

3：数据更新

4：数据删除

5：数据库事务

6：手动操作事务

三：查询构造器

1：数据查询

(1)：简单查询

(2)：分块查询

(3)：聚合方法

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

(5)：selects

(6)：原生表达式

(7)：原生方法

(8)：连表查询(join)

(9)：where语句

(10)：or查询

(11)：其它 Where 语句

(12)：参数分组

(13)：Where Exists 语句

(14)：JSON where 语句

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

(16)：条件语句

2:数据插入

(1)：单条数据插入

(2)：多条数据插入

(3)：插入返回数据ID

3：数据更新

(1)：基础数据更新

(2)：JSON数据更新

(3)：自增自减更新

4：数据删除

(1)：删除数据

(2)：清空表

huaweichenai

引用和评论

PHP基于Google Authenticator双因素身份验证实现动态码验证

在Java程序中监听mysql的binlog

这些年

一个用JavaScript生成思维导图(mindmap)的github repo

MySQL 回收表碎片实践教程

从零开始 Elasticsearh Docker 单机集群

如何使用Visual Studio Code调试PHP CLI应用和Web应用