Laravel Basic Study Notes (ii): Database operations

Source: Internet
Author: User
Tags echo date table name

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); 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.