关于php: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字段为testDB::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 userwhere 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)：自增自减更新

//自增1DB::table('users')->increment('votes');//自增5DB::table('users')->increment('votes', 5);//自减1DB::table('users')->decrement('votes');//自减5DB::table('users')->decrement('votes', 5);//将name字段为john的votes字段自增1DB::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();