Phero

Phero数据库查询框架【文档】

"Phero 数据库查询类库的使用"

Posted by Lerko on December 15, 2016

安装

git安装 git clone https://github.com/chenyingqiao/Phero.git

composer 安装 composer require lerko/p-hero

开始

在入口脚本注入PDO

方法1

use Phero\System\DI;
use Phero\Database as database;

$config[]="mysql:host=localhost;dbname=video;charset=utf8";//链接字符串
$config[]="root";//用户名
$config[]="Cyq19931115";//密码
DI::inj(database\Enum\DatabaseConfig::DatabaseConnect,$config);//注入

方法2

use Phero\System\DI;
use Phero\Database as database;

$dns = "mysql:host=localhost;dbname=video;charset=utf8";
DI::inj(database\Enum\DatabaseConfig::pdo_instance, new Phero\Database\PDO($dns, 'root', 'Cyq19931115'));

接下来就可以建立实体类 然后愉快的使用了

直接执行sql语句

  • exec(sql,bindData) 插入数据
  • query(sql,bindData) 读取数据
$help=new database\Realize\MysqlDbHelp();
$data=$help->query("select * from video_cat where id=:id ",["id",1]);
$effect=$help->exec("update video_cat set id=:id where name=:name",[
    ['id',1],
    ['name','视频']
]);

新建实体类

现在我们有几张表（都要use DbUnit）

video_cat表 (视频种类表)

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
name varchar(255) YES   NULL  

实体

namespace PheroTest\DatabaseTest\Unit;
use Phero\Database\DbUnit;

/**
 * @Table[alias=cat]
 */
class video_cat {
    use DbUnit;
    /**
     * [$id description]
     * @var [type]
     * @Field[alias=cat_id]
     */
    public $id;
    /**
     * [$name description]
     * @var [type]
     * @Field[alias=cat_name]
     */
    public $name;
}

video_user表 (用户表)

Field Type Null Key Default Extra
uid int(11) NO PRI NULL auto_increment
username varchar(255) YES   NULL  
password varchar(255) YES   NULL  

实体

namespace PheroTest\DatabaseTest\Unit;

use Phero\Database\DbUnit;

/**
 * @Table[alias=cd]
 */
class video_user {
    use DbUnit;
    /**
     * @Primary
     * @DbType[type=int]
     * @var [type]
     */
    public $uid;
    /**
     * @DbType[type=string]
     * @var [type]
     */
    public $username;
    /**
     * @DbType[type=string]
     * @var [type]
     */
    public $password;
}

video_course表 (视频课程表)

Field Type Null Key Default Extra
course_id int(11) NO PRI NULL auto_increment
name varchar(255) NO   NULL  
anthor varchar(255) NO   NULL  
cat_id int(11) NO   NULL  
direction_id int(11) NO   NULL  
difficulty_id int(11) NO   NULL  
intreduce text NO   NULL  
video_path varchar(255) NO   NULL  
cover varchar(255) NO   NULL  
create_time int(11) YES   NULL  
update_time int(11) YES   NULL  

实体

namespace PheroTest\DatabaseTest\Unit;
use Phero\Database\DbUnit;

/**
 * @Table[alias=course]
 */
class video_course {
    use DbUnit;

    public $course_id;
    public $name;
    public $anthor;
    public $cat_id;
    public $direction_id;
    public $difficulty_id;
    public $intreduce;
    public $video_path;
    public $cover;
    public $create_time;
    public $update_time;
}

注解

用法 参数
@Table[alias=###] 表别名
@Field[alias=###,type=[string,int]] 列的别名,列的类型

查询

查询所有列video_cat表中的列

$video_cat=new video_cat();
$video_user->select();//value就是video_cat查询出来的结果

相当于:
  select
    cat.uid,
    cat.username,
    cat.password
  from
    video_user as cat

查询表中的部分列

$video_cat=new video_cat(['id','name']);  //===>可以输入要的列
$value=$video_user->select();

相当于:
select
    cd.id,
    cd.name
from
    video_user as cd;

条件查询(where)

简单的where

    $video_user=new test\video_user(["uid","username"]);
    $video_user->order('uid',database\Enum\OrderType::desc);
    $video_user->whereOrEqGroupStart("uid", 4)->whereInGroupEnd("uid", [2, 3, 1]);
    $video_user->group("password");
    $video_user->having(["password","many_test"]);
    $video_user->select();

相当于:
select
    cd.uid,
    cd.username
from
    video_user as cd
where
    (
        cd.uid = 4 or cd.uid in(2,3,1)
    )
having
    cd.password = 'many_test'
order by
    cd.uid desc;

条件查询的组合

1. where\[Eq,Neq,In,Not_in,Between,Like,Lt,Lr,Gt,Ge\]([列],[值])
2. whereAnd\[Eq,Neq,In,Not_in,Between,Like,Lt,Lr,Gt,Ge\]([列],[值])
3. whereOr\[Eq,Neq,In,Not_in,Between,Like,Lt,Lr,Gt,Ge\]([列],[值])

关联查询

$video_cat=new video_cat();
/**
 * $.id=#.cat_id
 * 这个表达式标示两个表的联系
 * 其中
 *         $:表示 video_cat表
 *         #:表示 video_path表
 *          join可以嵌套使用
            如 A B C 三表
            A->join(B);
            B->join(C);
            A->select(); //完成3表链接
 * @type {[type]}
 */
$video_cat->join(new video_course(['course_id','name','video_path']),"$.id=#.cat_id");
$value=$video_cat->select();

插入

普通插入

//这是一种赋值方式
$video_user = new unit\video_user(["username" => "asdfs" . rand(), "password" => "1234" . rand()]);
//或者通过字段直接赋值
$video_user->username="fuck";
$video_user->password="123455";
$insert = $video_user->insert();

批量插入

$video_user = new unit\video_user(["username" => "asdfs" . rand(), "password" => "1234" . rand()]);
 $video_user2 = new unit\video_user(["username" => "asdfs" . rand(), "password" => "1234" . rand()]);
//批量插入
$entiy = [$video_user, $video_user2];
$model = new Model();
$model->insert($entiy);

事务插入

//这是一种赋值方式
$video_user = new unit\video_user(["username" => "asdfs" . rand(), "password" => "1234" . rand()]);
//或者通过字段直接赋值
$video_user->username="this is fuck";
$video_user->password="123455";
//true表示开启事务
$insert = $video_user->insert(true);
//提交事务
$video_user->commit();

更新

普通更新

$video_user = new unit\video_user(["username" => "asdfs", "password" => "1234"]);
$video_user->where(['uid', 4]);
$update = $video_user->update();

相当于
update
    video_user as cd
set
    username = asdfs,
    password = 1234
where
    cd.uid = 4;

删除

普通删除

$video_user = new unit\video_user();
$video_user->whereEq("uid",4);
$video_user->delete();

相当于
delete from video_user where uid = 4;

实例测试文件

require_once "vendor/autoload.php";

use PheroTest\DatabaseTest\Unit as unit;
use Phero\System\DI;
use Phero\Database as database;

/**
 * 注入pdo实例【没有注入将无法使用】
 * @var string
 */
$dns = "mysql:host=localhost;dbname=video;charset=utf8";
$config[]=$dns;
$config[]="root";
$config[]="Cyq19931115";
DI::inj(database\Enum\DatabaseConfig::DatabaseConnect,$config);

//DI::inj(database\Enum\DatabaseConfig::pdo_instance, new Phero\Database\PDO($dns, 'root', 'Cyq19931115'));


/******************************************
 * 测试普通单个查询
 * @var video_user
 *****************************************/
$video_user = new unit\video_user();
//$video_user->group("uid");
//$video_user->limit(2, 5);
$video_user->order('uid',database\Enum\OrderType::desc);
$video_user->group("password");
$video_user->having(["password","many_test"]);
//$video_user->fieldTemp(['username' => "SUBSTRING(? FROM 2) as subUsername", 'password' => "SUBSTRING(? FROM 2) as subPassword"]);
//// 设置列模板之后原来的列就会消失-----要如下手动添加
//$video_user->field(["username", "password"]);
//$video_user->field("count(case when username='ying' then username end) as 'asdf'");
//$video_user->BIN("password");
$video_user->whereOrEqGroupStart("uid", 4)->whereInGroupEnd("uid", [2, 3, 1]);
$user = $video_user->select();
//$user = $video_user->find();
var_dump($user);
$video_user->dumpSql();

/******************************************
 * 测试内外链接
 * @var video_user
 *****************************************/
$video_cat = new unit\video_cat(['id', 'name']); //===>可以输入要的列
$video_course = new unit\video_course(['course_id', 'name', 'video_path', "difficulty_id"]);
$video_cat->join($video_course, "$.id=#.cat_id");
$video_course->join(new unit\video_difficulty(['id', 'name']), "$.difficulty_id=#.id");
$video_cat_join_course = $video_cat->select();
var_dump($video_cat_join_course);
$video_cat->dumpSql();
var_dump($video_cat->getModel()->getError());

/******************************************
 * 测试删除
 * @var video_user
 *****************************************/
$video_user = new video_user();
$video_user->where(['uid', 8]);
var_dump($video_user->delete());

/******************************************
 * 测试插入
 * @var video_user
 *****************************************/
$video_user = new unit\video_user(["username" => "asdfs" . rand(), "password" => "1234" . rand()]);
$video_user->username="this is fuck";
$video_user->password="123455";
//true表示开启事务
$insert = $video_user->insert();
 //嵌套事务
$insert = $video_user->insert(true);
$video_user->commit();
 //嵌套事务
$video_user->commit();
var_dump($video_user->getError());
$video_user->dumpSql();
var_dump($insert);

/******************************************
 * 测试数据更新
 * @var video_user
 *****************************************/
$video_user = new unit\video_user(["username" => "asdfs" . rand(), "password" => "1234" . rand()]);
$video_user->where(['uid', 4]);
$update = $video_user->update();
var_dump($update);
var_dump($video_user->getModel()->getError());

/******************************************
 *测试数据替换
 * @var video_user
 *****************************************/
 $video_user = new unit\video_user(["username" => "asdfs" . rand(), "password" => "1234" . rand()]);
 $video_user2 = new unit\video_user(["username" => "asdfs" . rand(), "password" => "1234" . rand()]);
//批量插入
 $entiy = [$video_user, $video_user2];
 $model = new Model();
 $model->insert($entiy);
 var_dump($model->getError());
//ORM 插入
 $result = $video_user->replace();
 var_dump($result);

/******************************************
 *依赖注入测试
 * @var video_user
 *****************************************/
 $injectTest = new InjectTest();
 var_dump($injectTest);

/******************************************
 *手动设置数据源测试
 * @var video_user
 *****************************************/
$video_cat = new unit\video_cat(['id', 'name']); //===>可以输入要的列
$video_cat->field("course.id");
$video_cat->datasourse("(select * from video_course)", "course", "#.cat_id=$.id");
$data = $video_cat->select();
var_dump($data);
$video_cat->dumpSql();
echo $video_cat->fetchSql();
CATALOG
    FEATURED TAGS
    docker database server leetcode TCP mysql golang K8S 分布式 go Istio k8s istio 数据结构
    FRIENDS