Laravel framework database CURD operation, coherent operation summary, laravelcurd_PHP tutorial

Laravel framework database CURD operation and coherent operation summary, laravelcurd

1. Selects

Retrieve all rows in the table

Copy code The code is as follows:

$users = DB::table('users')->get();
foreach ($users as $user)
{
var_dump($user->name);
}

Retrieve a single row from the table

Copy code The code is as follows:

$user = DB::table('users')->where('name', 'John')->first();
var_dump($user->name);

Retrieve rows for a single column

Copy code The code is as follows:

$name = DB::table('users')->where('name', 'John')->pluck('name');

Retrieve a list of column values

Copy code The code is as follows:

$roles = DB::table('roles')->lists('title');

This method will return an array title. You can also specify a custom key column to return in the array

Copy code The code is as follows:

$roles = DB::table('roles')->lists('title', 'name');

Specify a Select clause

Copy code The code is as follows:

$users = DB::table('users')->select('name', 'email')->get();
​$users = DB::table('users')->distinct()->get();
​$users = DB::table('users')->select('name as user_name')->get();

Select clause added to an existing query $query = DB::table('users')->select('name');

Copy code The code is as follows:

$users = $query->addSelect('age')->get();

where

Copy code The code is as follows:

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

OR

Copy code The code is as follows:

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

Where Between

Copy code The code is as follows:

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

Where Not Between

Copy code The code is as follows:

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

Where In With An Array

Copy code The code is as follows:

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

Using Where Null To Find Records With Unset Values

Copy code The code is as follows:

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

Order By, Group By, And Having

Copy code The code is as follows:

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

Offset & Limit

Copy code The code is as follows:

$users = DB::table('users')->skip(10)->take(5)->get();

2. Connection

Joins

The query builder can also be used to write join statements. Take a look at the example below:

Basic Join Statement

Copy code The code is as follows:

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

左连接语句

复制代码 代码如下:

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

三、分组

　　有时候,您可能需要创建更高级的where子句,如“存在”或嵌套参数分组。Laravel query builder可以处理这些:

复制代码 代码如下:

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

　　上面的查询将产生以下SQL:

复制代码 代码如下:

　　select * from users where name = 'John' or (votes > 100 and title
<> 'Admin')
　　Exists Statements
　　DB::table('users')
　　->whereExists(function($query)
　　{
　　$query->select(DB::raw(1))
　　->from('orders')
　　->whereRaw('orders.user_id = users.id');
　　})
　　->get();

上面的查询将产生以下SQL:

复制代码 代码如下:

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

四、聚合

查询构建器还提供了各种聚合方法,如统计,马克斯,min,avg和总和。

Using Aggregate Methods

复制代码 代码如下:

$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->min('price');
$price = DB::table('orders')->avg('price');
$total = DB::table('users')->sum('votes');

Raw Expressions

有时您可能需要使用一个原始表达式的查询。这些表达式将注入的查询字符串,所以小心不要创建任何SQL注入点!创建一个原始表达式,可以使用DB:rawmethod:

Using A Raw Expression

复制代码 代码如下:

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

递增或递减一个列的值

复制代码 代码如下:

DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);

您还可以指定额外的列更新:

复制代码 代码如下:

　　DB::table('users')->increment('votes', 1, array('name' => 'John'));

Inserts

将记录插入表

复制代码 代码如下:

DB::table('users')->insert(
array('email' => 'john@example.com', 'votes' => 0)
);

将记录插入表自动增加的ID

如果表,有一个自动递增的id字段使用insertGetId插入一个记录和检索id:

复制代码 代码如下:

$id = DB::table('users')->insertGetId(
array('email' => 'john@example.com', 'votes' => 0)
);

Note: When using the PostgreSQL insertGetId method it is expected that the auto-incrementing column will be named "id".

Multiple records are inserted into the table

Copy code The code is as follows:

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

4. Updates

Update records in a table

Copy code The code is as follows:

DB::table('users')
->where('id', 1)
->update(array('votes' => 1));

5. Deletes

Delete records in table

Copy code The code is as follows:

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

Delete all records in the table

Copy code The code is as follows:

DB::table('users')->delete();

Delete a table

Copy code The code is as follows:

DB::table('users')->truncate();

6. Unions

The query builder also provides a quick way to "union" two queries:

Copy code The code is as follows:

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

The unionAll method can also be used, with the same method signature.

Pessimistic Locking

The query builder includes some "pessimistic locking" features to help you with your SELECT statements. Run the SELECT statement "Shared Lock", you can use the sharedLock method to query:

Copy code The code is as follows:

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

To update the "lock" in a SELECT statement, you can query using the lockForUpdate method:

Copy code The code is as follows:

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

7. Cache query

You can easily cache the results of a query using mnemonics:

Copy code The code is as follows:

$users = DB::table('users')->remember(10)->get();

In this example, the results of the query will be cached for ten minutes. When query results are cached, they are not run against the database and the results will be loaded from the default cache driver specified by your application. ​If you are using a driver that supports caching, you can also add tags to cache:

Copy code The code is as follows:

$users = DB::table('users')->cacheTags(array('people', 'authors'))->remember(10)->get();

php_laravel framework

280907494 Development group, there are many people doing this in the group.

PHP laravel framework, the first access entrance is normal, and an error is reported after refreshing

Turn on debug to see detailed errors

http://www.bkjia.com/PHPjc/874112.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/874112.htmlTechArticleLaravel framework database CURD operation, summary of coherent operation, laravelcurd 1. Selects retrieval table all rows copy code code is as follows : $users = DB::table('users')-get(); foreach ($u...