phalcon数据库DB使用实例

程序员文章站 2022-05-31 10:07:08
...
整理了一下phalcon的db使用，写了个demo。
 $connection=$this->getDI()->get('db');

        // 特别注意，此表名必须自己手动添加表前缀，也就是这里写完整表名！！
        // 注意点2，这里返回一般都是数组。
        echo "<h1>循环打印，query，然后fetch，此时也能直接得到所有行数。</h1>";
        echo <<<html
<pre>
        \$connection=\$this->getDI()->get('db');
        \$sql = 'SELECT id, name FROM temp ORDER BY name limit 10 ';
        // 将SQL语句发送到数据库系统
        \$result = \$connection->query(\$sql);
        // 打印每个 robot name
        while (\$robot = \$result->fetch()) {
            echo \$robot['name'];
        }
        echo "，总记录数". \$result->numRows() ;
</pre>        
html;

        $sql = 'SELECT id, name FROM temp ORDER BY name limit 10 ';

        // 将SQL语句发送到数据库系统
        $result = $connection->query($sql);
        while ($robot = $result->fetch()) {
            echo $robot['name'];

        }
        echo "，总记录数". $result->numRows() ;
        echo "<hr>";



        // 获取数组中的所有行
        echo "<h1>直接获取所有行，最简单直白一步到位，fetchAll方法</h1>";
        echo <<<html
        <pre>
        \$sql = 'SELECT id, name FROM temp ORDER BY name limit 10 ';
        \$robots = \$connection->fetchAll(\$sql);
        foreach (\$robots as \$robot) {
            echo \$robot['name'];
        }
</pre>
html;

        $robots = $connection->fetchAll($sql);
        foreach ($robots as $robot) {
            echo $robot['name'];
        }
        echo "<hr>";

        // 只获得第一行
        echo "<h1>直接获取第一行，fetchOne方法</h1>";
        echo <<<html
        <pre>
        \$sql = 'SELECT id, name FROM temp ORDER BY name limit 10 ';
        \$robot = \$connection->fetchOne(\$sql);
        echo \$robot['name'];
</pre>
html;
        $robot = $connection->fetchOne($sql);
        echo $robot['name'];
        echo "<hr>";

        echo "<h1>select查询，占位符第一种，纯问号，最最简单</h1>";
        echo <<<html
<pre>
        \$sql    = 'SELECT * FROM temp WHERE name = ? ORDER BY name';
        \$result = \$connection->query(
            \$sql,
            [
                'x11',
            ]
        );
        echo "总记录数". \$result->numRows();
</pre>
html;

        $sql    = 'SELECT * FROM temp WHERE name = ? ORDER BY name';
        $result = $connection->query(
            $sql,
            [
                'x11',
            ]
        );
        echo "总记录数". $result->numRows();
        echo "<hr>";

        // 与命名占位符绑定
        echo "<h1>insert 插入，占位符第2种，命名符号,注意insert 也可以直接问号占位符</h1>";
        echo <<<html
<pre>
        \$sql     = 'INSERT INTO temp(name, year) VALUES (:name, :year)';
        \$name = 'Astro Boy'.time();
        \$success = \$connection->query(
            \$sql,
            [
                'name' => \$name,
                'year' => 1952,
            ]
        );
</pre>
html;
        $sql     = 'INSERT INTO temp(name, year) VALUES (:name, :year)';
        $name = 'Astro Boy'.time();
        $success = $connection->query(
            $sql,
            [
                'name' => $name,
                'year' => 1952,
            ]
        );

        $sql    = 'SELECT * FROM temp WHERE name = ? ORDER BY name';
        $result = $connection->query(
            $sql,
            [
                $name,
            ]
        );
        echo "总记录数". $result->numRows();
        echo "<hr><hr><hr><hr><hr><hr><br><br><br><br><br><br><br><br><br>";

        // 开始使用 modelsManager 组件
        echo "<h1>select，开始使用 modelsManager 组件,和phql，和toArray方法</h1>";
        echo <<<html
        <pre>
        \$phql = "SELECT * FROM Apps\Models\Entities\Temp WHERE id < :id:";
        \$robots = \$this->modelsManager->executeQuery(\$phql, ['id' => 3]);
        var_dump(\$robots->toArray());
</pre>
html;
        $phql = "SELECT * FROM Apps\Models\Entities\Temp WHERE id < :id:";
        $robots = $this->modelsManager->executeQuery($phql, ['id' =>3]);
        var_dump($robots->toArray());
        echo "<hr>";

//        try {
//            $phql2 = "SELECT * FROM Apps\Models\Entities\Temp LIMIT :number:";
//            $robots = $this->modelsManager->executeQuery(
//                $phql2,
//                ['number' => 10],
//                Column::BIND_PARAM_INT
//            );
//            var_dump( $robots->toArray() );
//        }catch (\Exception $e){
//            echo $e->getMessage();
//        }

        echo "<h1>select，开始使用 phalcon 专用带变量占位符,有坑，占位符number2:int不能相同。</h1>";
        echo <<<html
        <pre>
        \$phql = "SELECT * FROM Apps\Models\Entities\Temp LIMIT {number:int}";
        \$robots = \$this->modelsManager->executeQuery(
            \$phql,
            ['number' => 2]
        );
        var_dump(\$robots->toArray());
        
        \$phql = "SELECT * FROM Apps\Models\Entities\Temp WHERE name = {name:str}";
        \$robots = \$this->modelsManager->executeQuery(
            \$phql,
            ['name' => 'x11']
        );
        var_dump(\$robots->toArray());
        
        \$phql = "SELECT * FROM Apps\Models\Entities\Temp LIMIT {number2:int}";
        \$robots = \$this->modelsManager->executeQuery(
            \$phql,
            ['number2' => 2]
        );
        var_dump(\$robots->toArray());
        
        \$phql = "SELECT * FROM Apps\Models\Entities\Temp WHERE name = {name}";
        \$robots = \$this->modelsManager->executeQuery(
            \$phql,
            ['name' => 'x113']
        );
        var_dump(\$robots->toArray());
        
        \$phql = "SELECT * FROM Apps\Models\Entities\Temp WHERE id IN ({id:array})";
        \$robots = \$this->modelsManager->executeQuery(
            \$phql,
            ['id' => [1, 2, 3]]
        );
        var_dump(\$robots->toArray());
        
</pre>
html;
        $phql = "SELECT * FROM Apps\Models\Entities\Temp LIMIT {number:int}";
        $robots = $this->modelsManager->executeQuery(
            $phql,
            ['number' => 2]
        );
        var_dump($robots->toArray());
        echo "<hr>";

        $phql = "SELECT * FROM Apps\Models\Entities\Temp WHERE name = {name:str}";
        $robots = $this->modelsManager->executeQuery(
            $phql,
            ['name' => 'x11']
        );
        var_dump($robots->toArray());
        echo "<hr>";

        //dd(555);
        $phql = "SELECT * FROM Apps\Models\Entities\Temp LIMIT {number2:int}";
        $robots = $this->modelsManager->executeQuery(
            $phql,
            ['number2' => 2]
        );
        var_dump($robots->toArray());
        echo "<hr>";

        $phql = "SELECT * FROM Apps\Models\Entities\Temp WHERE name = {name}";
        $robots = $this->modelsManager->executeQuery(
            $phql,
            ['name' => 'x113']
        );
        var_dump($robots->toArray());
        echo "<hr>";

        $phql = "SELECT * FROM Apps\Models\Entities\Temp WHERE id IN ({id:array})";
        $robots = $this->modelsManager->executeQuery(
            $phql,
            ['id' => [1, 2, 3]]
        );
        var_dump($robots->toArray());
        echo "<hr>";



        echo "<h1>insert，专用函数插入数据，原始表名，动态生成必要的SQL（另一种语法）</h1>";
        echo <<<html
        <pre>
        
        // 方法：excute
        \$sql     = 'INSERT INTO `robots`(`name`, `year`) VALUES (?, ?)';
        \$success = \$connection->execute(
           \$sql,
           [
              'Astro Boy',
              1952,
           ]
        );
        
        // 动态生成必要的SQL，方法 insert
        \$success = \$connection->insert(
            'robots',
            [
                'Astro Boy',
                1952,
            ],
            [
                'name',
                'year',
            ],
        );
        
        
        // 方法：insertAsDict
        \$name = 'Astro Boy11'.time();
        \$success = \$connection->insertAsDict(
            'temp',
            [
                'name' => \$name,
                'year' => 1952,
            ]
        );
</pre>
html;
        $name = 'Astro Boy11'.time();
        $success = $connection->insertAsDict(
            'temp',
            [
                'name' => $name,
                'year' => 1952,
            ]
        );
        $sql    = 'SELECT * FROM temp WHERE name = ? ORDER BY name';
        $result = $connection->query(
            $sql,
            [
                $name,
            ]
        );
        echo "总记录数". $result->numRows();
        echo "<hr>";

        echo "<h1>update，原始更新，方法 execute</h1>";
        echo <<<html
        <pre>
        \$sql     = 'UPDATE temp SET `name` = 'Astro boy' WHERE `id` = 1';
        \$success = \$connection->execute(\$sql);
        echo '有\$success被更新';
</pre>
html;
        $sql     = "UPDATE temp SET `name` = 'Astro boy' WHERE `id` = 1";
        $success = $connection->execute($sql);
        echo "有{$success}被更新";
        echo "<hr>";

        echo "<h1>update，占位符原始更新，方法 execute</h1>";
        echo <<<html
        <pre>
        \$sql     = 'UPDATE temp SET name = ? WHERE id = ?';
        \$success = \$connection->execute(
            \$sql,
            [
                'Astro Boy'.time(),
                1,
            ]
        );
</pre>
html;
        $sql     = 'UPDATE temp SET name = ? WHERE id = ?';
        $success = $connection->execute(
            $sql,
            [
                'Astro Boy'.time(),
                1,
            ]
        );
        echo "有{$success}被更新";
        echo "<hr>";

        echo "<h1>update，专用函数方法 updateAsDict， 更新数据，原始表名，动态生成必要的SQL（另一种语法）</h1>";
        echo <<<html
        <pre>
        \$success = \$connection->updateAsDict(
            'temp',
            [
                'name' => 'New Astro Boy'.time(),
            ],
            [
                'conditions' => 'id = ?',
                'bind'       => [101],
                'bindTypes'  => [\\PDO::PARAM_INT], // Optional parameter
            ]
        );
</pre>
html;
        $success = $connection->updateAsDict(
            'temp',
            [
                'name' => 'New Astro Boy'.time(),
            ],
            [
                'conditions' => 'id = ?',
                'bind'       => [101],
                'bindTypes'  => [\PDO::PARAM_INT], // Optional parameter，可选，这行去掉也行。
            ]
        );
        echo "有{$success}被更新";
        echo "<hr>";
        
        
        echo <<<html
        <pre>
        // 使用原始SQL语句删除数据，方法 execute
        \$sql     = 'DELETE `robots` WHERE `id` = 101';
        \$success = \$connection->execute(\$sql);
        
        // 占位符
        \$sql     = 'DELETE `robots` WHERE `id` = ?';
        \$success = \$connection->execute(\$sql, [101]);
        
        // 动态生成必要的SQL，方法 delete
        \$success = \$connection->delete(
            'robots',
            'id = ?',
            [
                101,
            ]
        );
</pre>
html;

        echo "<hr>";
        echo "<h1>学习phql,方法先createQuery,再 execute</h1>";
        $query = $this->modelsManager->createQuery('SELECT * FROM '.Cars::class);
//        $query = $this->modelsManager->createQuery('SELECT * FROM '.Cars::class);
        $cars  = $query->execute();
        var_dump($cars->toArray());

        echo "<h1>学习phql,方法 直接executeQuery</h1>";
        $cars = $this->modelsManager->executeQuery('SELECT * FROM Apps\Models\Entities\Brands');
        var_dump($cars->toArray());

        echo "<h1>学习phql,方法 带绑定参数</h1>";
        $query = $this->modelsManager
            ->createQuery('SELECT * FROM Apps\Models\Entities\Brands where name= :name: ');
        $cars  = $query->execute(['name'=>'宝马']);
        var_dump($cars->toArray());

        echo "<h1>学习phql,方法 直接执行，带绑定参数</h1>";
        $cars = $this->modelsManager
            ->executeQuery('SELECT * FROM Apps\Models\Entities\Brands where name= :name: ',['name'=>'宝马']);
        var_dump($cars->toArray());

        echo "<h1>学习phql,方法 不查整个对象，查标量</h1>";
        $cars = $this->modelsManager->executeQuery(
            'SELECT b.name FROM 
              Apps\Models\Entities\Brands as b
              ORDER BY b.name'
            ,['name'=>'宝马']
        );
        var_dump($cars->toArray());

        echo "<h1>学习phql,查询标量和对象混合体</h1>";
        $phql = 'SELECT c.price*0.1 AS taxes, c.* FROM Apps\Models\Entities\Cars AS c ORDER BY c.name';

        $cars = $this->modelsManager->executeQuery($phql);
        foreach($cars as $v){
            echo "汽车名称：".$v->c->name.", 价格修正". $v->taxes."<br>";
        }

        echo "<h1>学习phql,使用外连接</h1>";
        $manager = $this->modelsManager;
        $phql = 'SELECT c.*, b.* FROM Apps\Models\Entities\Cars as c 
        LEFT JOIN Apps\Models\Entities\Brands as b';
        $cars = $manager->executeQuery($phql);
        foreach($cars as $v){
            echo "汽车名称：".$v->c->name.", 品牌名称". $v->b->name."<br>";
        }

        // 也可以手动设置 on 的条件。
        $phql = 'SELECT Cars.*, Brands.* FROM Cars INNER JOIN Brands ON Brands.id = Cars.brands_id';
        //$rows = $manager->executeQuery($phql);

        echo "<h1>使用聚合</h1>";
        // 所有车的价格是多少？
        $phql = 'SELECT SUM(price) AS summatory FROM Apps\Models\Entities\Cars';
        $row  = $manager->executeQuery($phql)->getFirst();
        echo $row['summatory'];

        echo "<h1>使用每个品牌有多少辆汽车,group by</h1>";
        // 每个品牌有多少辆汽车？
        $phql = 'SELECT b.name, COUNT(*) as count 
           FROM Apps\Models\Entities\Cars as c
            left join
            Apps\Models\Entities\Brands as b 
           on b.id = c.brand_id
           GROUP BY  b.name';
        $rows = $manager->executeQuery($phql);
        foreach ($rows as $row) {
            echo  $row->name,'有',  $row->count, "<br>";
        }
        
        echo "<h1>使用phal的批量更新，将触发事件，确认更新失败将全部失败。</h1>";
        $phql ="update Apps\Models\Entities\Cars SET price=5";
        $result = $manager->executeQuery($phql);
        if ($result->success() === false) {
            $messages = $result->getMessages();
            foreach ($messages as $message) {
                echo $message->getMessage()."<br>";
            }
        }

        echo "<h1>使用查询生成器,查全部</h1>";
        $robots = $this->modelsManager->createBuilder()
            ->from('Apps\Models\Entities\Cars')
            ->join('Apps\Models\Entities\Brands')
            ->orderBy('Apps\Models\Entities\Cars.id')
            ->limit(2, 0)
            ->getQuery()
            ->execute();
        var_dump($robots->toArray());

        echo "<h1>使用查询生成器,查一行</h1>";
        $robots = $this->modelsManager->createBuilder()
            ->from('Apps\Models\Entities\Cars')
            ->join('Apps\Models\Entities\Brands')
            ->orderBy('Apps\Models\Entities\Brands.name')
            ->getQuery()
            ->getSingleResult();
        var_dump($robots->toArray());

        echo "<h1>使用查询生成器,模拟实际后台带条件查询，占位符使用，在条件中由php拼接。</h1>";
        $builder = $this->modelsManager->createBuilder();
        $builder->from('Apps\Models\Entities\Cars')
            ->where('style = :style:', ['style' => 'style2']);
        $result = $builder->getQuery()->execute();
        var_dump($result->toArray());

        echo "<h1>使用查询生成器,模拟实际后台带条件查询，占位符使用，在查询中，由mysql拼接</h1>";
        $builder = $this->modelsManager->createBuilder();
        $builder->from('Apps\Models\Entities\Cars')
            ->where('style = :style:');
        $result = $builder->getQuery()->execute(['style' => 'style2']);
        var_dump($result->toArray());

        echo "<h1>转义保留字</h1>";
        $phql   = 'SELECT id, [Like] FROM Posts';
        
        
        echo "<h1>分页实现</h1>";
        $builder = $this->modelsManager->createBuilder()
            ->from('Apps\Models\Entities\Cars')
            ->orderBy('name');
        $options = [
            'builder' => $builder,
            'limit'   => 2,
            'page'    => 1,
            'adapter' => 'queryBuilder',
        ];

        $paginator = Factory::load($options);
        $page = $paginator->getPaginate();
        var_dump($page->items->toArray());
        echo "总共".$page->total_pages. '条记录<br>';
        echo "总共".$page->total_items. '页<br>';




        // 指定表名
//        public function initialize()
//    {
//        $this->setSource('toys_robot_parts');
//    }
        // onConstruct()方法

        // 模型新增
//        $robot = new Robots();
//
//        $result = $robot->create(
//            [
//                'type' => 'mechanical',
//                'name' => 'Astro Boy',
//                'year' => 1952,
//            ]
//        );
        // if $result===false;

        // 模型更新
        //  $result =      $robot->update(
//            [
//                'type' => 'mechanical',
//                'name' => 'Astro Boy',
//                'year' => 1952,
//            ]
//        );
        // if $result ===false;

        // findFirst标准写法。
//        $robot = Robots::findFirst(11);
//
//        if ($robot !== false) {




        // 新增记录，后，用如下方法获得主键
//        $robot->save();
//
//        echo 'The generated id is: ', $robot->id;
        // 另外，模型类，可以单独设置主键字段名称！

        // 必须设置此方法。
        //$this->useDynamicUpdate(true);

        // $this->setSchema('toys');
        // 模型可以映射到不同的库名。

        //关系中，使用魔术方法get是有好处的！！原因是可以 直接加条件再过滤！！
        // 定义关系时，竟然可以直接定义过滤条件！！
        // 定义关系时，可以设置成强制检查！！。这样比较好哎。

        // 模型关系批量处理，方便阿！
//        $robots->getParts()->update(
//            [
//                'stock'      => 100,
//                'updated_at' => time(),
//            ]
//        );


       // $result = $manager->executeQuery($phql);

//        CREATE TABLE cars (
//        id int(11) NOT NULL AUTO_INCREMENT,
//  name varchar(191) DEFAULT '' COMMENT '1',
//  brand_id int not null default 0 comment '品牌id',
//  price decimal(10,2) not null default 0 comment 'price',
//  year int not null default 0 comment '2',
//  style varchar(191) not null default '' comment '3',
//  type varchar(191) not null default '' comment '4',
//  PRIMARY KEY (id),
//  index brand_id(brand_id),
//  index type(type),
//  index style(style)
//) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='测试用表'

//        insert into cars(name,brand_id, price,year,style,type)values(
//        'ao1',1,10000,1974,'style1','style1'
//    );
//insert into cars(name,brand_id, price,year,style,type)values(
//        'ao2',1,20000,1974,'style2','style2'
//    );
//insert into cars(name,brand_id, price,year,style,type)values(
//        'b1',1,30000,2000,'style3','style3'
//    );
//insert into cars(name,brand_id, price,year,style,type)values(
//        'b2',1,40000,3000,'style4','style4'
//    );
phalcon数据库DB使用实例

PHP数据库万能引擎类adodb配置使用以及实例集锦

HTML5中indexedDB 数据库的使用实例

oracle数据库中impdp和expdp使用实例讲解

Android使用SQLite数据库的简单实例

详解Java使用sqlite 数据库如何生成db文件

python使用Berkeley DB数据库实例

纯Python开发的nosql数据库CodernityDB介绍和使用实例

php使用pdo连接mssql server数据库实例

MySQL db方式操作数据库的实例详解

php中使用session_set_save_handler()函数把session保存到MySQL数据库实例
