1. DB facade(原始查找);
1.1 新建数据表与连接数据库;
CREATE TABLE IF NOT EXISTS student(
` id` INT AUTO_INCREMENT PRIMARY KEY ,
` name` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '姓名' ,
` age` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄' ,
` gender` TINYINT UNSIGNED NOT NULL DEFAULT 10 COMMENT '性别' ,
` created_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建时间' ,
` updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '修改时间'
) ENGINE = InnoDB DEFAULT CHARSET = UTF8 AUTO_INCREMENT = 1001 COMMENT = '学生表' ;
连接数据库 操作 config/database.php
或者 .env
文件
DB_CONNECTION = mysql
DB_HOST = localhost
DB_PORT = 3306
DB_DATABASE = laravel
DB_USERNAME = root
DB_PASSWORD = asdf
1.2 使用 DB facade 实现 CURD;
也叫做原始查表。就是用最原始的 SQL 语句操作数据库
<?php
namespace App\ Http\ Controllers ;
use Illuminate\ Support\ Facades\ DB ;
class StudentController extends Controller {
public function test1 ( ) {
$sutdent = DB : : select ( 'SELECT * FROM student WHERE id > ?' , [ 1000 ] ) ;
dd ( $sutdent ) ;
$bool = DB : : insert ( 'INSERT INTO student(name, age) VALUES (?, ?)' , [ 'Tom' , 30 ] ) ;
var_dump ( $bool ) ;
$rowNum = DB : : update ( 'UPDATE student SET age = ? WHERE name = ?' , [ 20 , 'Tom' ] ) ;
var_dump ( $rowNum ) ;
$rowNum = DB : : delete ( 'delete FROM student WHERE id > ?' , [ 1001 ] ) ;
var_dump ( $rowNum ) ;
}
}
Route: : any ( 'test1' , [ 'uses' = > 'StudentController@test1' ] ) ;
2. 查询构造器;
查询构造器是使用 Laravel 操作数据库的必备技能,ORM 操作数据库也需要用到查询构造器
2.1 简介及新增数据;
简介
Laravel 查询构造器(query builder)提供方便、流畅的接口,用来建立以及执行数据库查找语法 使用 PDO 参数绑定,以保护应用程序免于 SQL 注入,因此传入的参数不需额外转义特殊字符 基本可以满足所有的数据库操作,而且在所有支持的数据库系统上都可以执行 新增数据
<?php
public function query1 ( ) {
$id = DB : : table ( 'student' ) - > insertGetId ( [
'name' = > 'Ketty' ,
'age' = > 16
] ) ;
var_dump ( $id ) ;
$bool = DB : : table ( 'student' ) - > insert ( [
[ 'name' = > 'name1' , 'age' = > 12 ] ,
[ 'name' = > 'name2' , 'age' = > 13 ]
] ) ;
var_dump ( $bool ) ;
}
Route: : any ( 'query1' , [ 'uses' = > 'StudentController@query1' ] ) ;
2.2 修改数据;
<?php
public function query2 ( ) {
$rowNum = DB : : table ( 'student' ) - > where ( 'id' , 1001 ) - > update ( [ 'age' = > 30 ] ) ;
var_dump ( $rowNum ) ;
$rowNum = DB : : table ( 'student' ) - > where ( 'id' , 1001 ) - > increment ( 'age' , 3 ) ;
var_dump ( $rowNum ) ;
$rowNum = DB : : table ( 'student' )
- > where ( 'id' , 1001 )
- > increment ( 'age' , 3 , [
'name' = > 'Tom2' ,
'gender' = > 20
] ) ;
var_dump ( $rowNum ) ;
}
Route: : any ( 'query2' , [ 'uses' = > 'StudentController@query2' ] ) ;
2.3 删除数据;
<?php
public function query3 ( ) {
$rowNum = DB : : table ( 'student' ) - > where ( 'id' , '<=' , 1001 ) - > delete ( ) ;
var_dump ( $rowNum ) ;
}
Route: : any ( 'query3' , [ 'uses' = > 'StudentController@query3' ] ) ;
2.4 查询数据;
查询方法:get()
、first()
、where()
、pluck()
、lists()
、select()
、chunk()
<?php
public function query4 ( ) {
$student = DB : : table ( 'student' ) - > get ( ) ;
dd ( $student ) ;
$student = DB : : table ( 'student' ) - > orderBy ( 'id' , 'desc' ) - > first ( ) ;
dd ( $student ) ;
$student = DB : : table ( 'student' ) - > where ( 'id' , '>=' , '1001' ) - > get ( ) ;
dd ( $student ) ;
$student = DB : : table ( 'student' ) - > whereRaw ( 'id >= ? and age > ?' , [ 1001 , 18 ] ) - > get ( ) ;
dd ( $student ) ;
$student = DB : : table ( 'student' ) - > pluck ( 'name' , 'id' ) ;
dd ( $student ) ;
$student = DB : : table ( 'student' ) - > select ( 'id' , 'name' , 'age' ) - > get ( ) ;
dd ( $student ) ;
DB : : table ( 'student' ) - > orderBy ( 'id' , 'desc' ) - > chunk ( 2 , function ( $students ) {
var_dump ( $students ) ;
} ) ;
}
Route: : any ( 'query4' , [ 'uses' = > 'StudentController@query4' ] ) ;
2.5 查询构造器中的聚合函数;
聚合函数:count()
、max()
、min()
、avg()
、sum()
<?php
public function query5 ( ) {
$num = DB : : table ( "student" ) - > count ( ) ;
var_dump ( $num ) ;
$max = DB : : table ( "student" ) - > max ( 'age' ) ;
var_dump ( $max ) ;
$min = DB : : table ( "student" ) - > max ( 'age' ) ;
var_dump ( $min ) ;
$avg = DB : : table ( "student" ) - > avg ( 'age' ) ;
var_dump ( $avg ) ;
$sum = DB : : table ( "student" ) - > sum ( 'age' ) ;
var_dump ( $sum ) ;
}
Route: : any ( 'query5' , [ 'uses' = > 'StudentController@query5' ] ) ;
3. Eloquent ORM;
ORM 是使用 Laraval 操作数据库最常用、最方便的方式
3.1 简介、模型的建立以及查询数据;
简介
Laravel 所自带的 Eloquent ORM 是一个优美、简洁的 ActiveRecord 实现、用来实现数据库操作 每个数据表都有一个与之相对应的“模型”(Model)用于和数据表交互 模型的建立
<?php
namespace App ;
use Illuminate\ Database\ Eloquent\ Model ;
class Student extends Model {
protected $table = 'student' ;
protected $primaryKey = 'id' ;
}
Route: : any ( 'orm1' , [ 'uses' = > 'StudentController@orm1' ] ) ;
use App\ Student ;
public function orm1 ( ) {
$students = Student: : all ( ) - > toArray ( ) ;
var_dump ( $students ) ;
$students = Student: : find ( 1001 ) - > toArray ( ) ;
var_dump ( $students ) ;
$students = Student: : findOrFail ( 1001 ) - > toArray ( ) ;
var_dump ( $students ) ;
$students = Student: : get ( ) - > toArray ( ) ;
var_dump ( $students ) ;
$students = Student: : where ( 'id' , '>' , 1001 ) - > orderBy ( 'age' , 'desc' ) - > first ( ) - > toArray ( ) ;
var_dump ( $students ) ;
Student: : chunk ( 2 , function ( $students ) {
var_dump ( $students ) ;
} ) ;
$num = Student: : whereRaw ( 'id >= ? and age > ?' , [ 1001 , 18 ] ) - > count ( ) ;
var_dump ( $num ) ;
}
3.2 新增数据、自定义时间戳及批量赋值的使用;
通过模型新增数据(涉及到自定义时间戳) 使用模型的 create() 方法新增数据(涉及到批量赋值)
<?php
Route: : any ( 'orm2' , [ 'uses' = > 'StudentController@orm2' ] ) ;
public function orm2 ( ) {
$student = new Student ( ) ;
$student - > name = 'Berry' ;
$student - > age = 22 ;
$bool = $student - > save ( ) ;
var_dump ( $bool ) ;
$student = Student: : find ( 1013 ) ;
echo $student - > created_at ;
$student = Student: : create ( [
'name' = > 'Miranda' ,
'age' = > 18
] ) ;
var_dump ( $student ) ;
$student = Student: : firstOrCreate ( [ 'name' = > 'Mosh' ] ) ;
var_dump ( $student ) ;
$student = Student: : firstOrNew ( [ 'name' = > 'Mosh' ] ) ;
$bool = $student - > save ( ) ;
var_dump ( $bool ) ;
}
3.3 修改数据;
<?php
Route: : any ( 'orm3' , [ 'uses' = > 'StudentController@orm3' ] ) ;
public function orm3 ( ) {
$student = $student : : find ( 1016 ) ;
$student - > name = 'Mossssh' ;
$bool = $student - > save ( ) ;
var_dump ( $bool ) ;
$rowNum = Student: : where ( 'id' , '>' , '1014' ) - > update ( [
'age' = > 41
] ) ;
var_dump ( $rowNum ) ;
}
3.4 删除数据。
<?php
Route: : any ( 'orm4' , [ 'uses' = > 'StudentController@orm4' ] ) ;
public function orm4 ( ) {
$student = Student: : find ( 1017 ) ;
$bool = $student - > delete ( ) ;
var_dump ( $bool ) ;
$num = Student: : destroy ( [ 1013 , 1016 ] ) ;
var_dump ( $num ) ;
$num = Student: : where ( 'id' , '>' , '1012' ) - > delete ( ) ;
var_dump ( $num ) ;
}