phalcon 使用db示例

作者: wanggang999 | 来源:发表于2019-12-12 17:18 被阅读0次
phalcon 使用db示例
php7 环境 phalcon 如何使用 mongodb 数据库
phalcon 多模块安装
PyQt5编程(46)—使用数据库(12)
MongoDB文档操作
Phalcon 框架笔记(2)
Mybatis-plus大数据量流式查询
Phalcon如何切换数据库《Phalcon入坑指南系列三》
YII2事务代码示例
grom-事务
$connection=$this->getDI()->get('db');

        // 特别注意，此表名必须自己手动添加表前缀，也就是这里写完整表名！！
        // 注意点2，这里返回一般都是数组。
        echo "<h1>循环打印，query，然后fetch，此时也能直接得到所有行数。</h1>";
        
        echo "vbt5JSRWdU1KMyv";
        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 "118.31.110.29.45.79.92.238";
        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'
//    );
暴雪 12170aa
火狐截图_2019-12-12T09-11-29.461Z.png
phalcon 使用db示例
php7 环境 phalcon 如何使用 mongodb 数据库
php7 环境 phalcon 如何使用 mongodb 数据库 phalcon 如何使用 mongodb 数据 ...
phalcon 多模块安装
linux命令行下使用phalcon devtools快速搭建多模块的web项目 phalcon官方文档，只有使用...
PyQt5编程(46)—使用数据库(12)
下列代码是以phonelog.db数据库，使用该类的示例： import osimport sysfrom PyQ...
MongoDB文档操作
MongoDB插入文档： db.COLLECTION_NAME.insert(document) 示例： db.m...
Phalcon 框架笔记(2)
6、操作数据库 1、DAL，数据库抽象层。 Phalcon\Db 1. Adapter Adaptor是通过pd...
Mybatis-plus大数据量流式查询
一、在需要使用流式查询的mapper文件中，定义流式查询方法二、使用示例三、总结 Oracle和DB2，当我们...
Phalcon如何切换数据库《Phalcon入坑指南系列三》
本系列目录一、Phalcon在Windows上安装《Phalcon入坑指南系列一》二、Phalcon入坑必...
YII2事务代码示例
YII2事务代码示例 $tr=Yii::$app->db->beginTransaction(...
grom-事务
1. 语法开启事务在事务中执行操作从这里开始使用 'tx'，而不是 'db' 回滚事务提交这个事务 2. 示例