thinkphp5.0 Common SQL

Native notation

introduction of use/think/db;

Query is used for querying other execute
Inserting records

$result = Db::execute (' INSERT into Sb_ad (Ad_name, ad_content, status) VALUES (1, "456", 1) ');
Dump ($result);
Update record
$result = Db::execute (' update sb_ad set ad_name = ' Framework ' where ad_id = 1 ');
Dump ($result);
Querying data
$result = Db::query (' select * from sb_ad where ad_id = 1 ');
Print_r ($result);
Delete data
$result = Db::execute (' Delete from sb_ad where ad_id = 2 ');
Dump ($result);
Other operations
Show Database list
$result = Db::query (' Show tables from Tpshop1 ');
Print_r ($result);
Clear Data table
$result = Db::execute (' TRUNCATE table Sb_ad ');
Dump ($result);

$data = db::name (' user ')->select ();
$data = db::name (' user ')->where (' id ', 1)->find ();
$data = db::name (' user ')->where (' id ', 1)->order (' Paixu ', ' DESC ')->limit (4)->select ();

Query: The Where, order, and limit methods here are called chain-action methods
Db::table (' Think_user ')->where (' status ', 1)->order (' Create_time ')->limit ()->select ();
Inquire
Db::table (' Think_user ')->where (' id ', 1)->field (' Id,name,email ')->find ();
Delete
Db::table (' Think_user ')->where (' status ', 1)->where (' id ', 1)->delete ();

Db::table (' Think_user ')->where (' name ', ' like ', '%thinkphp ')->whereor (' title ', ' Like ', '%thinkphp ')->find ( );


Sub Query Lee Find
1.
Print SQL statement when the parameter of the Select method is false, it means that no query is returned but only the build SQL
$subQuery 2= db::table (' Ssc_admin_user ')
->field (' Id,name ')
->where (' id ', ' > ', 10)
->select (FALSE);
SELECT ' id ', ' name ' from ' Ssc_admin_user ' WHERE ' id ' > 10

2. Using the Fetchsql method to print the SQL statement the Fetchsql method means that the SQL statement is returned without querying, and not only supports SELECT, but supports all curd queries.


$subQuery = db::table (' Think_user ')->field (' Id,name ')->where (' id ', ' > ', ten)->fetchsql (true)->select ();
Output SELECT ' id ', ' name ' from ' Think_user ' WHERE ' id ' > 10


$subQuery = db::table (' Think_user ')->field (' Id,name ')->where (' id ', ' > ', Ten)->buildsql ();
Output SELECT ' id ', ' name ' from ' Think_user ' WHERE ' id ' > 10

4. Using closures to construct subqueries
Queries such as In/not in and exists/not exists can use closures directly as subqueries, for example:
Db::table (' Think_user ')
->where (' id ', ' in ', function ($query) {
$query->table (' Think_profile ')->where (' status ', 1)->field (' id ');
})
->select ();

The generated SQL statement is
SELECT * from ' Think_user ' where ' id ' in (SELECT ' id ' from ' think_profile ' where ' status ' = 1)

Db::table (' Think_user ')
->where (function ($query) {
$query->table (' Think_profile ')->where (' status ', 1);
}, ' exists ')
->find ();
The resulting SQL statement is


SELECT * from ' Think_user ' where EXISTS (select * from ' think_profile ' where ' status ' = 1)

JSON usage


Db::table (' think_artist ')
->alias (' a ')
->join (' Think_work w ', ' a.id = w.artist_id ')
->join (' Think_card c ', ' a.card_id = C.id ')
->select ();
The table name can also be a subquery
$subsql = db::table (' think_work ')->where ([' Status ' =>1])->field (' Artist_id,count (ID) count ')->group (' artist_id ')->buildsql ();
Db::table (' Think_user ')->alias (' A ')->join ([$subsql = ' W '], ' a.artist_id = w.artist_id ')->select ();
Because BuildSQL returns a statement with (), there is no need to add () at both ends.


Get the value of a field or a column
Get a user's points
User::where (' id ', ten)->value (' score ');
Get all values for a column
User::where (' status ', 1)->column (' name ');
Indexed by ID
User::where (' status ', 1)->column (' name ', ' id ');
User::where (' status ', 1)->column (' Id,name '); The GetField with TP3

thinkphp5.0 Common SQL