数据库操作

执?原?SQL

1 // 查询

2 $emp = DB :: select ( 'select * from employees where emp_no = 1' );

3 $emp = DB :: select ( 'select * from employees where emp_no = ? and gender = ?' ,

[ 1 , 'M' ]);

4 $emp = DB :: select ( 'select * from employees where emp_no = :empNo and gender =

:gender' ,[ 'empNo' => 1 , 'gender' => 'M' ]);

5 // 插?

6 DB :: insert ( 'insert into employees(first_name,last_name) values(?,?,?)' ,

[ 'Jack' , 'Ma' ]);

7 // 更新，返回受影响的?数

8 $affected = DB :: update ( 'update employees set gender = ? where emp_no = ?' ,

[ 'M' , 123 ]);

9 // 删除，返回被删除的?数

10 $deleted = DB :: delete ( 'delete from employees where first_name = ?' ,[ 'Jack' ]);

11 // 运?通?语句，不返回任何值

12 DB :: statement ( 'drop table employees' );

事务

1 // 如果事务闭包中抛出异常，事务将会?动回滚；如果闭包执?成功，事务将会?动提交：

2 DB :: transaction ( function (){

3 DB :: insert ( 'insert into employees(first_name,last_name) values(?,?,?)' ,

[ 'Jack' , 'Ma' ]);

4 $affected = DB :: update ( 'update employees set gender = ? where emp_no = ?' ,

[ 'M' , 123 ]);

5 });

6 // ?动开启事务

7 DB :: beginTransaction ();

8 // ?动回滚

9 DB :: rollBack ();

10 // ?动提交

11 DB :: commit (); 查询构建器

table()

通过DB??的table()函数来获取?个查询构建器实例。

get()

1 $emps = DB :: table ( 'employees' ). get ();

返回包含结果集额的Illuminate\Support\Collection，其中每?个结果都是 PHP 的 StdClass 对象实

例

fifirst()

1 $emp = DB :: table ( 'employees' ) -> first ();

从数据表中获取??数据

value()

1 $values = DB :: table ( 'employees' ) -> where ( 'emp_no' , '>=' , 499995 ) -

> value ( 'first_name' );

只获取第?条结果的某?列的数据

pluck()

与value类似，但是返回所有符合条件的记录列数组。

chunk()

1 DB :: table ( 'employees' ) -> where ( 'emp_no' , '>=' , 499980 ) -> orderby ( 'emp_no' ) -

> chunk ( 10 , function ( $emps ){

2 foreach ( $emps as $emp ) {

3 echo $emp -> first_name . '<br>' ;

4 }

5 });

将查询结果分块在回调函数中进?处理。

聚合函数 1 //count()

2 $result = DB:: table ( 'employees' ) -> where ( 'emp_no' , '>=' , 499980 ) -> count ();

3 //max()

4 $result = DB:: table ( 'salaries' ) -> max ( 'salary' );

5 //min()

6 $result = DB:: table ( 'salaries' ) -> min ( 'salary' );

7 //sum()

8 $result = DB:: table ( 'salaries' ) -> where ( 'emp_no' , '>=' , 499980 ) -> sum ( 'salary' );

9 //avg()

10 $result = DB:: table ( 'salaries' ) -> where ( 'emp_no' , '>=' , 499980 ) -> avg ( 'salary' );

exists()、doesntExist

1 $result = DB :: table ( 'employees' ) -> where ( 'emp_no' , '=' , '500000' ) -> exists ();

2 $result = DB :: table ( 'employees' ) -> where ( 'emp_no' , '=' , '500000' ) -> doesntExist ();

判断结果是否存在或不存在

select()，andSelect()

1 $result = DB :: table ( 'employees' ) -> where ( 'emp_no' , '>=' , '499980' ) -

> select ( 'first_name' , 'last_name' ) -> get ();

2 // 上?的查询等同于：

3 $query = DB :: table ( 'employees' ) -> where ( 'emp_no' , '>=' , '499980' ) -

> select ( 'first_name' );

4 $result = $query -> andSelect ( 'last_name' ) -> get ();

指定查询的列

distinct()

1 $result = DB :: table ( 'employees' ) -> where ( 'emp_no' , '>=' , '499980' ) -

> select ( 'first_name' , 'last_name' ) -> distinct () -> get ();

过滤重复结果

原?语句

1 DB :: Raw () 2 $result = DB :: table ( 'employees' ) -> select ( DB :: raw ( 'count(1) as num' )) -

> where ( 'emp_no' , '>=' , 499980 ' ) -> get ();

whereRaw()

1 $result = DB :: table ( 'employees' ) -> select ( DB :: raw ( 'count(1) as num' )) -

> whereRaw ( 'emp_no>=?' ,[ 499980 ]) -> get ();

此外还有orWhereRaw(),havingRaw(),orhavingRaw(),orderByRaw()

Join

join

1 $result = DB :: table ( 'employees' ) -

> join ( 'salaries' , 'employees.emp_no' , '=' , 'salaries.emp_no' ) -

> where ( 'employees.emp_no' , '>=' , '499980' ) -> get ();

leftJoin,rightJoin类似。

crossJoin():?成?个笛卡尔积

1 $users = DB :: table ( 'sizes' )

2 -> crossJoin ( 'colours' )

3 -> get ();

?级连接

1 $result = DB :: table ( 'employees' ) -> join ( 'salaries' , function ( $join ){

2 $join -> on ( 'employees.emp_no' , '=' , 'salaries.emp_no' ) -

> where ( 'employees.emp_no' , '>=' , '499980' );

3 }) -> get ();

join函数的第?个参数为?个闭包，该闭包将会接收?个 JoinClause 对象?于指定 join ?句约束。

?查询连接

1 $salaryQ = DB :: table ( 'salaries' ) -> where ( 'emp_no' , '>=' , '499980' );

2 $result = DB :: table ( 'employees' ) -> joinSub ( $salaryQ , 'salaries' , function ( $join ){

3 $join -> on ( 'employees.emp_no' , '=' , 'salaries.emp_no' );

4 }) -> get ();

5 return $result ; 可以使? joinSub、leftJoinSub 和 rightJoinSub ?法将查询和?个?查询进?连接，每个?法都接收

三个参数 —— ?查询、表别名和定义关联字段的闭包

Union

1 $emps = DB :: table ( 'employees' ) -> whereRaw ( 'emp_no between 499980 and 499990' );

2 $result = DB :: table ( 'employees' ) -> whereRaw ( 'emp_no > 499990' ) -> union ( $emps1 ) -

> get ();

Where ?句

简单where?句

1 // 添加?个条件

2 DB :: table ( 'employees' ) -> where ( 'emp_no' , '>=' , '499980' );

3 // 添加多个条件 , 传?维数组

4 DB :: table ( 'employees' ) -> where ([

5 [ 'emp_no' , '>=' , '499980' ],

6 [ 'gender' , '=' , 'F' ]

7 ]);

or语句

1 DB :: table ( 'employees' ) -> where ( 'emp_no' , '>=' , '499980' ) -

> orWhere ( 'emp_no' , '<' , '10' ) -> get ();

whereBetween

1 DB :: table ( 'employees' ) -> whereBetween ( 'emp_no' ,[499980,499999] ) -> get ();

whereNotBetween同理

whereIn,whereNotIn

1 DB :: table ( 'employees' ) -> whereIn ( 'emp_no' ,[11111,11112,11113] ) -> get ();

whereNotIn同理

whereNull,whereNotNull

1 DB :: table ( 'employees' ) -> whereNull ( 'birth_date' ) -> get ();

wherNotNull同理 whereDate / whereMonth / whereDay / whereYear / whereTime

insert()

获取?增ID

update()

delete()

Eloquent

定义模型

这将在项?中?成?个Employee.php，内容如下：

orderBy()

$result = DB :: table ( 'employees' ) -> whereIn ( 'emp_no' ,[ 11111 , 11112 , 11113 ]) -

> orderBy ( 'hire_date' , 'asc' , 'birth_date' , 'asc' ) -> get ();

1

2

groupBy(),having

DB :: table ( 'salaries' ) -> select ( 'emp_no' , 'salary' ) -> groupBy ( 'emp_no' , 'salary' ) -

> having ( 'emp_no' , '>' , '499990' ) -> get ();

1

1 DB :: table ( 'employees' ) -> insert ( [ 'firtst_name' => 'Jack' , 'last_name' => 'Ma' ] );

2 DB :: table ( 'employees' ) -> insert ( [

3 [ 'firtst_name' => 'Jack' , 'last_name' => 'Ma' ],

4 [ 'firtst_name' => 'Jack2' , 'last_name' => 'Ma' ]

5 ] );

$id = DB :: table ( 'employees' ) -

> insertGetId ([ 'firtst_name' => 'Jack' , 'last_name' => 'Ma' ]);

1

DB :: table ( 'employees' ) -> where ( 'id' ,1 ) -

> update ( [ 'last_name' => 'Ma' , 'gendger' => 'M' ] );

1

1 DB :: table ( 'employees' ) -> where ( 'id' ,1 ) -> delete ();

1 $ php artisan make :model Employee 1 <?php

2 namespace App ;

3 use Illuminate \ Database \ Eloquent \ Model ;

4 class Employee extends Model

5 {

6 //

7 }

此时Employee模型默认对应employees表（?写的模型类名复数格式）。

模型成员变量

$table:关联到模型的数据表

$primaryKey:主键名（默认为id)

$keyType:主键类型（默认为整形数据）

$incrementing:主键是否?增（默认为true)

$fifillable:可以被赋值的属性，不能与$guarded同时使?

$guarded:不会被赋值的属性，不能与$fifillable同时使?

获取

1 $emps = Employee :: all (); // 返回表中所有数据

2 $emps = Employee :: where ([[ 'last_name' , 'like' , 'A%' ],[ 'gender' , '=' , 'M' ]]) -

> orderby ( 'birth_date' , 'asc' ) -> limit ( 3 ) -> get (); // 条件查询

就是将Employee模型就是?个查询构建器，我们可以在模型上使?查询构建起的所有?法。

插?

1 public function store ( Request $request ){

2 $emp = new Employee ();

3 $emp -> first_name = $request -> input ( 'first_name' );

4 $emp -> last_name = $request -> input ( 'last_name' );

5 $emp -> birth_date = $request -> input ( 'birth_date' );

6 $emp -> hire_date = date ( 'Y-m-d' , time ());

7 $emp -> gender = $request -> input ( 'gender' );

8 var_dump ( $emp );

9 $emp -> save ();

10 }

更新 1 $emp = Employee :: find ( $emp_no ); // 先查询

2 $emp -> first_name = $request -> input ( 'first_name' );

3 $emp -> last_name = $request -> input ( 'last_name' );

4 $emp -> birth_date = $request -> input ( 'birth_date' );

5 $emp -> hire_date = $request -> input ( 'hire_date' );

6 $emp -> gender = $request -> input ( 'gender' );

7 $emp -> save (); // 更新到数据库

批量更新：

1 Employee :: where ( 'emp_no' , '>' , '500000' ) -> update ( [ 'hire_date' => '2020-05-10' ] );

删除

1 Employee :: find ( 1 ) -> delete (); // 按主键删除

2 Employee :: where ( 'last_name' , 'AAA' ) -> delete (); // 批量删除

3 Employee :: destroy ( 1 ); // 按主键删除

4 Employee :: destroy ([ 1 , 2 , 3 ]); // 批量删除

软删除

1 class Employee extends Model

2 {

3 use SoftDeletes ;

4 /**

5 * 应该被调整为?期的属性

6 *

7 * @ var array

8 */

9 protected $dates = [ 'deleted_at' ];

10 }

表中有deleted_at列，那么上?的配置将会软删除

关联关系

1 class Employee extends Model

2 {

3 // protected $table = 'employees_temp';

4 protected $primaryKey = 'emp_no' ;

5 public $timestamps = false ;

6 // ?对?，?个 Employee 关联?个 salary 7 public function salary (){

8 return $this -> hasOne ( 'App\Salary' , 'emp_no' , 'emp_no' );

9 }

10 // ?对多，?个 Employee 关联?个 title

11 public function titles (){

12 return $this -> hasMany ( 'App\Title' , 'emp_no' , 'emp_no' );

13 }

14 }

hasOne和hasMany的第?个参数是外键名，默认为?法名加_id后缀，第?个参数为主键名，或当前

模型的关联字段名称。

1 $emp = Employee :: find ( 1 );

2 $salary = $emp -> salary ;

3 $titles = $emp -> titles ;

逆向

1 class Salary extends Model

2 {

3 public function Employee (){

4 return $ this -> belongsTo ( 'App\Employee' , 'emp_no' , 'emp_no' );

5 }

6 }

belongsTo第?个参数是外键名，默认为当前模型名加_id后缀，第三个参数为主键名，默认为id。

1 $salary = Salary :: find ( 1 );

2 $emp = $salary -> emplioyee ;

多对多

1 class Employee extends Model

2 {

3 protected $primaryKey = 'emp_no' ;

4 public $timestamps = false ;

5 // ?个员?对应多个部?，?个部?有多个员?

6 public function departments (){

7 return $ this -> belongsToMany ( 'App\Department' , 'dept_emp' , 'emp_no' , 'dept_no' );

8 }

9 } 10 class Department extends Model

11 {

12 protected $primaryKey = 'dept_no' ;

13 }

除了employees和departments两张表之外，还有?张dept_emp关系表，在belongsToMany?法的第

?个参数中指定，不指定的话关系表名称默认为departments_employees（按?字?排序），

belongsToMany的第三个参数为关系表中关联employees表的外键名，默认为employee_id，第四个

参数为关系表中，关联departments表的外键，默认为department_id。

pivot：我们获取的每个Employee模型都会?动附上pivot属性，该属性是中间关系表模型。

关联插?、更新

1 $emp = Employee :: find ( 1 );

2 $emp -> titles () -> save ( new Title ([ 'title' => 'Manager' ])); // 插??条

3 $emp -> titles () -> saveMany ([ new Title ([ 'title' => 'Manager' ]), new

Title ([ 'title' => 'Developer' ]));