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