Skip to content
Permalink
master
Switch branches/tags
Go to file
 
 
Cannot retrieve contributors at this time

鏁版嵁搴

1. 閰嶇疆

鍙互閫氳繃渚濊禆娉ㄥ叆鐨勬柟寮忥紝瀵规暟鎹簱杩涜閰嶇疆銆

  1. 鍦ㄩ渶瑕佹暟鎹簱鐨勭被涓姞鍏ヤ緷璧栨敞鍏ヤ唬鐮侊細

    use PhpBoot\DB\DB;
    use PhpBoot\DI\Traits\EnableDIAnnotations;
    
    class Books
    {
        use EnableDIAnnotations; //鍚敤閫氳繃@inject鏍囪娉ㄥ叆渚濊禆
    
        /**
         * @inject
         * @var DB
         */
        private $db;
        
        public function getBooks()...
    }

    妗嗘灦鍦ㄥ疄渚嬪寲Books鍚庯紝鏍规嵁@inject娉ㄩ噴, 鑷姩缁欏睘鎬$db璧嬪硷紝鍏堕昏緫绛変环浜:

    $books->db = $app->get(DB::class);
  2. 淇敼鏁版嵁搴撻厤缃

    鍦 config.php 涓姞鍏ヤ互涓嬮厤缃紙鏁版嵁搴撳湴鍧绛夐渶鏍规嵁瀹為檯鎯呭喌淇敼锛夛細

    'DB.connection'=> 'mysql:dbname=phpboot-example;host=127.0.0.1',
    'DB.username'=> 'root',
    'DB.password'=> 'root',
    'DB.options' => [],

2. 缂栧啓 SQL

涓嬮潰灏嗛氳繃瀹炵幇 createBook銆乨eleteBook銆乽pdateBook銆乫indBooks 鏂规硶锛屾紨绀篿nsert銆乨elete銆乽pdate銆乻elect 鐨勪娇鐢ㄣ

2.1 INSERT

public function createBook(Book $book)
{
    $newId = $this->db->insertInto('books')
        ->values([
	        'name'=>$book->name,
	        'brief'=>$book->brief,
	        ...
        ])
        ->exec()
        ->lastInsertId(); 
    return $newId;
}

2.2 DELETE

public function deleteBook($id)
{
    $this->db->deleteFrom('books')
        ->where(['id'=>$id])
        ->exec();
}

2.3 UPDATE

public function updateBook(Book $book)
{
    $this->db->update('books')
        ->set([
	        'name'=>$book->name,
	        'brief'=>$book->brief,
	        ...
        ])
        ->where(['id'=>$book->id])
        ->exec(); 
}

2.4 SELECT

public function findBooks($name, $offsit, $limit)
{
    $books = $this->db->select('*')
        ->from('books')
        ->where('name LIKE ?', "%$name%")
        ->orderBy('id')
        ->limit($offsit, $limit)->get();
        
    return $books;
}

3. 楂樼骇鐢ㄦ硶

涓婅堪绀轰緥灞曠ず浜PhpBoot\DB鐨勫熀纭鐢ㄦ硶锛PhpBoot\DB鍚屾椂涔熸敮鎸佹洿澶嶆潅鐨凷QL銆

3.1 澶嶆潅 WHERE

绫讳技 SQL WHERE a=1 OR (b=2 and c=3), 鍙互浠ヤ笅浠g爜瀹炵幇:

->where(['a'=>1])
->orWhere(function(ScopedQuery $query){
    $query->where(['b'=>2, 'c'=>3])
})

涓婇潰渚嬪瓙涓紝ScopedQuery 涓繕鑳藉啀宓屽 ScopedQuery

3.2 JOIN

$db->select('books.*', DB::raw('authors.name as author'))
    ->from('books')
    ->where(['books.id'=>1])
    ->leftJoin('authors')->on('books.authorId = authors.id')
    ->get()

3.3 WHERE ... IN ...

浣跨敤PDO鏃讹紝WHERE IN鐨勯澶勭悊鏂瑰紡寰堜笉鏂逛究锛岄渶瑕佷负IN鐨勫厓绱犻鐣欐暟閲忕浉绛夌殑?, 姣斿锛

$pdo->prepare(
    'SELECT * FROM table WHERE a IN (?,?,?)'
)->execute([1,2,3])

鑰屼娇鐢PhpBoot\DB鍙互瑙e喅杩欎釜闂锛

$db->select()->from('table')->where('a IN (?)', [1,2,3]);

3.4 浣跨敤 SQL 鍑芥暟

榛樿鎯呭喌涓嬶紝妗嗘灦浼氬杈撳叆鍋氳浆鎹, 濡備細鍦ㄨ〃鍚嶅拰鍒楀悕澶栧姞涓``锛屼細鎶婂彉閲忎綔涓虹粦瀹氬鐞嗭紝姣斿涓嬮潰鐨勮鍙

$db->select('count(*) AS count')
    ->from('table')
    ->where(['time'=>['>'=>'now()']]);

绛変环 鐨 SQL锛

SELECT `count(*) AS count` FROM `table` where `time` > 'now()'

濡傛灉甯屾湜妗嗘灦涓嶅仛杞崲锛岄渶瑕佷娇鐢DB::raw(),姣斿锛

$db->select(DB::raw('count(*) AS count'))
    ->from('table')
    ->where(['time'=>['>'=>DB::raw('now()')]]);

涓庝笅闈 SQL 绛変环

SELECT count(*) AS count FROM `table` where `time` > now()

3.5 瀛愭煡璇

涓嬮潰浠g爜婕旂ず瀛愭煡璇㈢敤娉曪細

$parent = $db->select()->from('table1')->where('a=1');
$child = $db->select()->from($parent);

涓庝笅闈 SQL 绛変环

SELECT * FROM (SELECT * FROM `table1` WHERE a=1)

3.6 浜嬪姟

$db->transaction(
    function(DB $db){
        $db->update('table1')->...
        $db->update('table1')->...
    }
)

浜嬪姟鍏佽宓屽锛屼絾鍙湁鏈澶栧眰鐨勪簨鍔¤捣浣滅敤锛屽唴閮ㄥ祵濂楃殑浜嬪姟涓庢渶澶栧眰浜嬪姟灏嗚褰撳仛鍚屼竴涓簨鍔°

4. 浣跨敤澶氫釜鏁版嵁搴

PhpBoot 涓DB 绫诲畾涔変簡榛樿鐨勬瀯閫犳柟寮忥紝褰㈠紡濡備笅锛

DB::class => \DI\factory([DB::class, 'connect'])
    ->parameter('dsn', \DI\get('DB.connection'))
    ->parameter('username', \DI\get('DB.username'))
    ->parameter('password', \DI\get('DB.password'))
    ->parameter('options', \DI\get('DB.options')),

鎵浠ュ鏋滀綘鐨勪笟鍔″彧浣跨敤杩炴帴涓涓暟鎹簱锛屽彧闇瑕佸DB.connection, DB.username ,DB.password, DB.options杩涜閰嶇疆鍗冲彲銆備絾鏈夌殑鏃跺欏彲鑳介渶瑕佸鍦ㄥ簲鐢ㄤ腑杩炴帴涓嶅悓鐨勬暟鎹簱锛岃繖鏃跺彲浠ラ氳繃渚濊禆娉ㄥ叆閰嶇疆澶氫釜搴擄紝濡傦細

  1. 鍏堥厤缃彟涓涓暟鎹簱杩炴帴

    'another_db' => \DI\factory([DB::class, 'connect'])
        ->parameter('dsn', 'mysql:dbname=phpboot-example;host=127.0.0.1')
        ->parameter('username', 'root')
        ->parameter('password', 'root')
        ->parameter('options', [])
  2. 鍦ㄩ渶瑕佺殑鍦版柟娉ㄥ叆姝よ繛鎺

    use PhpBoot\DB;
    
    class Books
    {
        use EnableDIAnnotations; //鍚敤閫氳繃@inject鏍囪娉ㄥ叆渚濊禆
        /**
         * @inject another_db
         * @var DB
        */
        private $db2;
    }