Laravel Basic Study Notes (ii): Database operations

Database files used: Laravel Basic Learning Note (ii) SQL file 1) DB facade (original lookup) a) connect to the database;

Configuration database in Directory laravel/config/database.php

' Default ' = env (' db_connection ', ' MySQL '),

//default configuration is MySQL, so view connections in the corresponding MySQL item
' mysql ' = [
    ' Driver '    = ' mysql ',
    ' host ' +      env (' db_host ', ' localhost '),
    ' database '  = env (' Db_ DATABASE ', ' Forge '),
    ' username '  = env (' db_username ', ' Forge '),
    ' password '  = env (' Db_ PASSWORD ', '),
    ' charset ' = '   utf8 ',
    ' collation ' = ' utf8_unicode_ci ',
    ' prefix '    = > ',
    ' strict ' and    false,
    ' engine '    = null,
],

//The env here refers to Laravel/.env, Configuration content is:
db_host=127.0.0.1
db_database=laravel
db_username=root
db_password=root

b) Implement curd using DB facade;

DB Facade class use illuminate\support\facades\db must be called using DB Class Operations database
;

New
$bool = Db::insert (' INSERT into student (Name,age) VALUES (?,?) ',
    [' Imooc ', ' +] ');
Var_dump ($bool);

Update
$num = db::update (' update student set age =? WHERE name =? ',
    [+] ' Sean ']);
Var_dump ($num);

Query
$students = Db::select (' select * FROM student where id =? ',
    [1001]);
DD ($students);

Delete
$num = DB::d elete (' Delete from student where ID >? ',
    [1001]);
Var_dump ($num);

2) Query Builder a) Introduction to query Builder and new data

Add a new piece of data and get its id value
$id = db::table (' student ')->insertgetid (
    [' name ' = ' vision ', ' age ' =]
) ;
Var_dump ($id);

Added multiple data, returns a Boolean value indicating success or failure
$bool = db::table (' student ')->insert (['
    name ' = ' name1 ', ' age ' = 20],< c27/>[' name ' = ' name2 ', ' age ' = +],
]);
Var_dump ($bool);

b) updating data using the Query Builder

The query constructor updates the data and returns the number of modifications
$num = db::table (' student ')
   ->where (' id ', 1002)
   ->update ([' age ' =]);

No conditional default all self-increment one, returns the number of modifications
$num = db::table (' student ')->increment (' age ');     Self-increment
$num = db::table (' student ')->decrement (' age ');     Self-subtract

//plus conditional self-increment, returns the number of modifications
$num = db::table (' student ')->increment (' Age ', 3);
$num = db::table (' student ')->decrement (' Age ', 3);

Subtract 3 from the Age field with ID 1002 and change the Name field to IIMOOC, returning the number of modifications
$num = db::table (' student ')
   ->where (' id ', 1002)
   - >decrement (' Age ', 3, [' name ' = ' IIMOOC ']);

c) Using the Query Builder to delete data

Delete the data with ID 1007, return the modified quantity
$num = db::table (' student ')
    ->where (' id ', 1007)
    ->delete ();

Delete data with ID greater than or equal to 1005, return modified quantity
$num = db::table (' student ')
    ->where (' id ', ' >= ', 1005)
    ->delete () ;

Empty table data, no return data
db::table (' Student ')->truncate ();

d) Querying data using the Query Builder

Get () Get all data

$students = db::table (' student ')->get ();

First () Gets the data

$student = db::table (' student ')
    ->orderby (' id ', ' desc ')
    ->first ();

where () to add a query condition

$students = db::table (' student ')
    ->where (' id ', ' >= ', 1002)
    ->get ();

Whereraw () Multiple query criteria

$students = db::table (' student ')
    ->whereraw (' ID >=? and age >? ', [1001,])
    ->get ();

Pluck () The field specified in the result set

$names = db::table (' student ')
    ->pluck (' name ');

Lists () The field specified in the result set

$names = db::table (' student ')
    ->lists (' name ');
The results of the query are as follows
Array:5 [
  0 = "Name1"
  1 = "Name2"
  2 = "Name3"
  3 = "Name4"
  4 = "Name 5 "
]

//Specify ID as subscript
$names = db::table (' student ')
    ->lists (' name ', ' id ');
The results of the query are as follows
array:5 [
  1001 = "Name1"
  1002 = "Name2" 1003
  = "Name3" 1004
  = "Name4 "
  1005 =" Name5 "
]

Select () selects the specified field for querying

$students = db::table (' student ')
    ->select (' id ', ' name ', ' age ')
    ->get ();

Chunk () two data per query until the end

echo "<pre>";
Db::table (' Student ')->chunk (2, function ($students) {

    var_dump ($students);

    if (your condition) {
    //     return false;//execute under certain conditions can make the query stop
    //}

});

e) Aggregate functions in the Query Builder

The number of records in the statistics
$num = db::table (' student ')->count ();

Calculates the maximum
$max = db::table (' student ')->max (' age ');

Calculates the minimum value
$min = db::table (' student ')->min (' age ');

Calculated mean
$avg = db::table (' student ')->avg (' age ');

Calculation and
$sum = db::table (' student ')->sum (' age ');

3) Eloquent ORM a) Introduction to eloquent ORM, model creation and query data

Building the Model file laravel/app/student.php

<?php
namespace App;
Use Illuminate\database\eloquent\model;
Class Student extends Model
{
    //Specify Table name
    protected $table = ' Student ';
    Specify ID
    protected $primaryKey = ' id ';
}

Controller Path laravel/app/http/controllers/studentcontroller.php

Remember to call the model use
app\student;



All () query table all data
$students = Student::all ();

The Find () query specifies the id
$students = student::find (1001);

Findorfail ()     findorfail () failure will error, find () will return null
$students = Student::findorfail (1006);



The method of using the query constructor can also query the data
$students = Student::get ();
$students = student::where (' id ', ' > ', ' 1001 ')
    ->orderby (' age ', ' desc ')
    ->first ();
echo "<pre>";
Student::chunk (2, function ($students) {
    var_dump ($students);
});

Aggregation function
$num = Student::count ();
$max = student::where (' id ', ' > ', 1001)->max (' age ');
Var_dump ($max);

b) Use of new data, custom timestamp, and batch assignment in eloquent ORM

Controller Path laravel/app/http/controllers/studentcontroller.php
Some functions need to be opened in the model file to be able to use

1. New data
$student = new Student () using the model;
$student->name = ' sean2 ';
$student->age =;
$bool = $student->save ();


2. Use the model to find the specified field (Reference Model file settings 2)
$student = Student::find (1015);
Echo $student->created_at. "<br/>";
echo Date (' Y:m:d h:i:s ', $student->created_at);


3. Add data using the model's Create method (refer to model File Settings 3)
$student = student::create (
    [' id ' = ' 1 ', ' name ' = ' Imddooc ', ' age ' = ' Sex ' = ' 5 ']
);
Because the Id,sex field is not set here, inserting data does not assign a value of
DD ($student);


4. Firstorcreate () has a match, does not add, does not add data
$student = student::firstorcreate (
    [' name ' = = ' 555 ']
);


5. Firstornew ()   to find the user with a property, if not set up a new instance, you need to save your own call to save
$student = student::firstornew (
    [' name ' = ' 5555 ']
);
$bool = $student->save ();

Model File laravel/app/student.php

2. Setting the automatic maintenance timestamp will help us add the time of the field Created_at and Updated_at, and/or turn it on
by default, set to False to turn off public
$timestamps = true;

Set the time form for Created_at and Updated_at, default to the current year, such as "2017", set GetDateFormat to enable display timestamps
protected function GetDateFormat ()
{
    return time ();
}

When the default output Created_at and Updated_at, the time is automatically formatted, the asdatetime can be directly output
protected function Asdatetime ($val)
{
    return $val;
}


3. Specify a field that allows bulk assignment, only in order for the Create method to succeed, no setting to protected by default
$fillable = [' name ', ' age '];

Specifies a field that does not allow bulk assignment
protected $guarded = [];

c) Modifying data using eloquent ORM

Updating data by model
$student = student::find (1021);
$student->name = ' Kitty ';
$bool = $student->save ();       The name and Updated_at fields are modified
var_dump ($bool);


Batch update with query statements
$num = student::where (' id ', ' > ', 1020)->update (
    [' age ' =]
);
Var_dump ($num);

d) Delete data using eloquent ORM

//deleted through the model, if not the data will be error when deleting $student = Student::find (1021); $bool = $student->delete (); var_


Dump ($student);
deleted by primary key; no data returned int (0) $num = Student::d Estroy (1022);
$num = Student::d Estroy (1024,1025);
$num = Student::d Estroy ([1019,1020]);


Var_dump ($num);
Delete the data for the specified condition $num = student::where (' id ', ' > ', 1015)->delete (); Var_dump ($num);