Laravel5.1 数据库操作

2019-05-13 16:52:22 Albin Wong 1632

### 数据库操作
1. 支持的数据库类型 
    - MySQL
    - Postgres
    - SQLite
    - SQL Server
2. 数据库连接配置  
文件位置 `config/database.php`  
结果集的返回类型 `'fetch' => PDO::FETCH_ASSOC`  
.env环境快速配置
3. 数据库基本操作
    - 查询 `DB::select`
    - 插入 `DB::insert`
    - 更新 `DB::update`
    - 删除 `DB::delete`
    - 一般语句 `DB::statement('drop table users');`
    - 事务操作
        ```
        DB::beginTransaction
        DB::rollBack()
        DB::commit()
        ```
    - 操作多个数据库 `DB::connection('foo')->select(...)`

#### 构造器  
1. 增删改查 
    1. 插入 
    - 单条 `DB::table('users')->insert(['email' => 'john@example.com', 'votes' => 0]);`

- 多条 
    ```php
    DB::table('users')->insert([
        ['email' => 'taylor@example.com', 'votes' => 0],
        ['email' => 'dayle@example.com', 'votes' => 0]
    ]);
    ```
	
    - 获取id插入 `$id = DB::table('users')->insertGetId(['email'=>'john@example.com','votes'=>0]);`
    2. 更新 `DB::table('users')->where('id',1)->update(['votes'=>1]);`
    3. 删除 `DB::table('users')->where('votes', '<', 100)->delete();`
    4. 查询
    - 查询所有 `DB::table('users')->get()`
    - 查询单条 `DB::table('users')->first()`
    - 查询单条结果中的某个字段 `DB::table('user')->value('username')`
    - 获取一列数据 `DB::table('user')->lists('username')`

2. 连贯操作
    1. 设置字段 `DB::table('users')->select('name', 'email as user_email')->get()`
    2. 条件
    - `DB::table('users')->where('name','like','T%')->get()`
    - `DB::table('users')->where('votes', '>', 100)->orWhere('name', 'John')->get();`
    - `DB::table('users')->whereBetween('votes', [1, 100])->get()`
    - `DB::table('users')->whereIn('id', [1, 2, 3])->get()`
    3. 排序 `orderBy('name', 'desc')`
    4. 分页 `DB::table('users')->skip(10)->take(5)->get();`
    5. 连接表(join)

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

6. 计算
    - 总数 `DB::table('users')->count()`
    - 最大值 `DB::table('orders')->max('price')`
    - 平均值 `DB::table('orders')->avg('price')`

#### sql语句记录

```php
App/Providers/AppServiceProvider.php  boot方法中添加
DB::listen(function($sql, $bindings, $time){
     //写入sql
     file_put_contents('.sqls', "[".date('Y-m-d H:i:s')."]".$sql."\r\n", FILE_APPEND);
});
```

```php
Event::listen('illuminate.query',function($query){
     var_dump($query);
 });
```

(3) Comments

John Doe- Reply

February 26, 2013 at 1:18 pm
- saqib sarwar- Reply
  
  February 26, 2013 at 1:20 pm
  
  Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Investigationes demonstraverunt lectores legere me lius quod ii legunt saepius. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum.
John Doe- Reply

February 26, 2013 at 1:27 pm

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat.

Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum.

Your email address will not be published. Required fields are marked *

Name *

Email *

Website

Comment

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Laravel5.1 数据库操作

(3) Comments

John Doe- Reply

saqib sarwar- Reply

John Doe- Reply

Leave a Reply