ThinkPHP database statement

Alias the database

Use the alias() method to alias the database so that you don’t have to create instance objects repeatedly when called

$obj=Db::name('cms_my')->alias('nt');
&obj->select();
&obj->find();
Copy the code

TP5 Data query

Use array display after query

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])->column('name');

$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])->where('name'.'My Name')->select();
Copy the code

Use object display after query

Only one piece of data will be queried

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->where('name'.'My Name')
->find();
Copy the code

Take a single field and form an array

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->column('name');
Copy the code

Fetch n fields to form an array and change the subscript to ID

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->column(['name'.'thumb'].'id');
Copy the code

Just pull out one field

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->value('name');
Copy the code

Specifies how many pieces of data to output

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->limit(3)
->select();
Copy the code

Multiple query criteria for the same field

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->where('name'['like'.'thinkphp%'], ['like'.'%thinkphp'])
->where('id'['>'.0], ['< >'.10].'or')
->find();
Copy the code

Sort by field and time

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->order('sort'.'DESC')
->select();
Copy the code

Clear the data of the last query

Because it is called reference query data, in the second query, will be left over the previous query conditions, so it should be cleared.

// removeOption is more efficient and accurate
$obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->removeOption('id')
->removeOption('isdelete')
->select();
Copy the code

TP5 data insertion

Insert a piece of data

$data = [
    'name'= >$name.'pwd'= >$pwd.'create_time' => time()
];
Db::name('user')->insert($data);

/ / or
Db::name('user')->data($data)->insert();
Copy the code

Mysql REPLACE writes

// If there is a unique index, the same data before will be deleted
Db::name('user')->insert($data.'true');
Copy the code

After the write succeeds, the id is returned

Db::name('user')->insertGetId($data);
/ / or
Db::name('user')->insert($data);
return Db::insertGetId();
Copy the code

Batch write

$dataAll= [['name'= >$name.'pwd'= >$pwd.'create_time' => time()
    ],
    [
      'name'= >$name2.'pwd'= >$pwd2.'create_time' => time()
    ]
];
Db::name('user')->insert($dataAll);
Copy the code

TP5 data is modified

Modify the data

$id = 18;
$data = ['name'= >'small leather'];

Db::name('user')->where('id'.$id)->updata($data);
Copy the code

The data() method modifies the data, and the two sides of the data are merged and modified in sequence

$id = 18;
$data = ['name'= >'small leather'];

Db::name('user')->where('id'.$id)
->data($data)
->updata(['pwd'= >'123']);
Copy the code

Data appreciation and depreciation

Db::name('user')->where('id'.$id)
->inc('view')
->updata();

Db::name('user')->where('id'.$id)
->dec('view'.3)
->updata();
Copy the code

Exp (), raw();

Db::name('user')->exp('email'.'UPPER(email)')->update()

$data= ['name'= >'white'.'email' => Db::raw('UPPER(email)'),
    'proce' => Db::raw('price - 3')
    'id'= >28
];
Db::name('user')->update($data);
Copy the code

Modify only a single field

Db::name('user')->where('id'.39)->setField('name'.'proce');
Copy the code

Modify only a single field,setInc() increment and setDec() decrement

Db::name('user')->where('id'.39)->setInc('price');
Copy the code

Tp5 deletes data

Delete a piece of data

 // delete to delete the main build ID
Db::name('user')->delete(39);
Copy the code

Deleting Multiple Pieces of Data

 // delete to delete the main build ID
Db::name('user')->delete([39.99.106]);
Copy the code

Delete by where, more general

Db::name('user')->where('id'.99)->delete();
Copy the code

Delete all data from the table

Db::name('user')->delete(true);
Copy the code

Query expression

Comparison of the query

There are several query methods <>, >, <, >=, <= to filter out data that matches the comparison value

Db::name('user')->where('id'.'< >'.80)->select();
Copy the code

Fuzzy query

%name% search %name% search %name% search % search % search %name% search % search % search % search % search % search % search % search % search %

Db::name('user')->where('name'.'like'.'%MyName%')->select();

// Multiple query criteria will be filtered if any criteria are met
Db::name('user')->where('name'.'like'['%MyName%'.'shuai%'].'or')->select();
// Shortcuts ———— all eligible
Db::name('user')->whereLike('name'.'%MyName%')->select();
// Shortcuts ———— do not meet the conditions of all
Db::name('user')->whereNotLike('name'.'%MyName%')->select();
Copy the code

Between Indicates the range of the query

Db::name('user')->where('id'.'between'.'10, 20')->select();

// Select from 1 to 10
Db::name('user')->where('name'.'between'['1'.'10'].'or')->select();

// Shortcuts ———— all eligible
Db::name('user')->whereBetween('name'[10.20])->select();
// Shortcuts ———— do not meet the conditions of all
Db::name('user')->whereNotBetween('name',[xiaom,huawei])->select();
Copy the code

The IN query selects a fixed number of values

// select the data whose id matches 10,20
Db::name('user')->where('id'.'in'.'10, 20')->select();

// Shortcuts ———— all eligible
Db::name('user')->whereIn('name'.'xiaom,huawei')->select();
// Shortcuts ———— do not meet the conditions of all
Db::name('user')->whereNotIn('name'.'10, 20'])->select();
Copy the code

Query null data

Db::name('user')->whereNull('name')->select();
Db::name('user')->whereNotNull('name')->select();
Copy the code

Custom query statement

Db::name('user')->where('id'.'exp'.'IN,21,25) (19) - > select ();Copy the code

Array assembly method query

Db::name('user')->where([
	['gender'.'='.'male'],
    ['age'.'='.'15']
])->select();
Copy the code

Complex array assembly, passed by variable

$map[] = ['gender'.'='.'male'];
$map[] = ['price'.'in'[60.70.80]];
Db::name('user')
->where($map)
->select();
Copy the code

String form transfer, simple and rough query

Db::name('user')
->where('gender=" male "AND price IN (60,70,90)')
->select();
Copy the code

The query time

Where time query

Db::name('user')->where('create_time'.'> time'.2018-1-1)->select();
/ / or
Db::name('user')->whereTime('create_time'.'>'.2018-1-1)->select();
Copy the code

Time interval query

Db::name('user')->where('create_time'.'between time'['2018-1-1'.'2018-1-5'])->select();
/ / or
Db::name('user')->whereBetween('create_time'['2018-1-1'.'2018-1-5'])->select();
/ / or again
Db::name('user')->whereBetweenTime('create_time'.'2018-1-1'.'2018-1-5')->select();
Copy the code

Quickly find data at a specified time

$q_time finds the following parameters:

The time stamp 1603785741

Last year (last year) last month week Yesterday…

Can also be simplified to query ‘y’, ‘m’, ‘w’, ‘d’ this year, this month, this week, today

Look up data within a few hours’ -2 hours’

$q_time= '1603785741'$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'= >0.'nt.status'= >1])
->whereTime('createtime'.$q_time)
->select();
Copy the code

Aggregation, native, sub-query

Query data quantity

Db::name('user')->count();
// Adding fields ignores null values
Db::name('user')->count('uid');
Copy the code

Querying the maximum value of data

Db::name('user')->max('num'); 	     / / 100
Db::name('user')->max('num'.false); / / 100.00
Copy the code

Average value of query data

Db::name('user')->avg('num');
Copy the code

Querying the Sum of Data

Db::name('user')->sum('num');
Copy the code

Joint subquery

$subQuery = Db::name('two')
->field('uid')
->where('gender'.'male')
->buildSql(true);

$result = Db::name('one')
->where('id'.'exp'.'IN'.$subQuery)
->select();

// Or use closure form
$result = Db::name('one')->where('id'.'in'.function($query){
	$query->name('two')
          ->field('uid')
          ->where('gender'.'male');
})->select();
Copy the code

Native queries

Db::query('select * from tp_user');

Db::execute('update tp_user set username=" update tp_user set username=")
Copy the code

The field query

Specifies the fields to be queried

Db::name('user')->field('id'.'name'.'email')->select();
Copy the code

Specifies the fields to be queried and alias names

Db::name('user')
->field('id'.'name as username'.'email as hhh')
->select();
Copy the code

Specify fields to query, and use MySQL functions

Db::name('user')
->field('id'.'SUM(num)')
->select();
Copy the code

Select the first five characters of a field value using MySQL

Db::name('user')
->field(['id'.'LEFT(email,5)'=>])
->select();
Copy the code

Specifies that certain fields are not queried

Db::name('user')
->field('name,email'.true)
->select();
/ / or
Db::name('user')
->field(['name'.'email'].true)
->select();
Copy the code

Specifies how many fields to insert

$data= ['username'= >'name'.'password'= >'123123'.'gender'= >'girl'.'details'= >'[email protected]'. ]  Db::name('user')
->field('name,details')
->insert($data);
Copy the code

Paging display

Limit the number of output data

Db::name('user')->limit(5)->select();
Copy the code

Limit paging mode, showing several entries starting at the NTH

Limit is not ideal for pagination, requiring counting from column to column.

Suitable for specified number

Db::name('user')->limit(2.5)->select();// Select 5 data from the second page on the first page
Db::name('user')->limit(6.5)->select();// select 5 data from the first page
Copy the code

Page mode

Page is ideal for pagination, without calculating which items to refer to directly

Suitable for paging

Db::name('user')->page(1.5)->select();// Data items 1 through 5
Db::name('user')->page(3.5)->select();// Data from articles 11 to 15
Copy the code

Sorting, statistics

Order specifies a sort.

Desc descending order, ASC ascending order. The default for asc

Db::name('user')->order('id'.'desc')->select();
Copy the code

Multifield sort

First according to the first sorting way, and then according to the subsequent sorting way in turn

Db::name('user')
->order(['create_time'= >'asc'.'age'= >'desc'])
->select();
Copy the code

Grouping statistics

Group groups data according to field statistics. Such as gender grouping: male total, female total. Returns statistics for two fields

Db::name('user')
->field('gender',sum(price))
->group('gender')
->select();
Copy the code

Multi-field group statistics

Group Groups data according to multiple fields. If different values are found in the specified field, the data will be divided into another group

Db::name('user')
->field('gender',sum(price))
->group('gender,age')
->select();
Copy the code

Having screening

It is mainly used for group filtering after grouping

$result = Db::name('user')
->field('gender,sum(price)')
->group('gender')
->having('SUM(price)>600')
->select();
Copy the code

Alias the database

TP5 Data query

TP5 data insertion

TP5 data is modified

Tp5 deletes data

Query expression

The query time

Aggregation, native, sub-query

The field query

Paging display

Sorting, statistics

Related Posts

Set up a single page application using Vue + Flask

The Future pattern adds Callback and Promise patterns

Warm the old message queue ActiveMQ