Workerman MySQL组件Connection用法总结

一、初始化连接

$db = new \workerman\mysql\connection('host', 'port', 'user', 'password', 'db_name');

二、获取所有数据

$db->select('id,sex')->from('persons')->where('sex= :sex and id = :id')->bindvalues(array('sex'=>'m', 'id' => 1))->query();
//等价于
$db->select('id,sex')->from('persons')->where("sex= 'm' and id = 1")->query();
//等价于
$db->query("select id,sex from `persons` where sex='m' and id = 1");

三、获取一行数据

$db->select('id,sex')->from('persons')->where('sex= :sex')->bindvalues(array('sex'=>'m'))->row();
//等价于
$db->select('id,sex')->from('persons')->where("sex= 'm' ")->row();
//等价于
$db->row("select id,sex from `persons` where sex='m'");

四、获取一列数据

$db->select('id')->from('persons')->where('sex= :sex')->bindvalues(array('sex'=>'m'))->column();
//等价于
$db->select('id')->from('persons')->where("sex= 'f' ")->column();
//等价于
$db->column("select `id` from `persons` where sex='m'");

五、获取单个值

$db->select('id')->from('persons')->where('sex= :sex')->bindvalues(array('sex'=>'m'))->single();
//等价于
$db->select('id')->from('persons')->where("sex= 'f' ")->single();
//等价于
$db->single("select id from `persons` where sex='m'");

六、复杂查询

$db->select('*')->from('table1')->innerjoin('table2','table1.uid = table2.uid')->where('age > :age')->groupby(array('aid'))->having('foo="foo"')->orderbyasc/*orderbydesc*/(array('did'))
->limit(10)->offset(20)->bindvalues(array('age' => 13));
// 等价于
$db->query('select * from `table1` inner join `table2` on `table1`.`uid` = `table2`.`uid`
where age > 13 group by aid having foo="foo" order by did limit 10 offset 20');

七、插入数据

$insert_id = $db->insert('persons')->cols(array(
    'firstname'=>'abc',
    'lastname'=>'efg',
    'sex'=>'m',
    'age'=>13))->query();
等价于
$insert_id = $db->query("insert into `persons` ( `firstname`,`lastname`,`sex`,`age`)
values ( 'abc', 'efg', 'm', 13)");

八、更新数据

$row_count = $db->update('persons')->cols(array('sex'))->where('id=1')
->bindvalue('sex', 'f')->query();
// 等价于
$row_count = $db->update('persons')->cols(array('sex'=>'f'))->where('id=1')->query();
// 等价于
$row_count = $db->query("update `persons` set `sex` = 'f' where id=1");

九、删除数据

$row_count = $db->delete('persons')->where('id=9')->query();
// 等价于
$row_count = $db->query("delete from `persons` where id=9");

十、事务处理

$db->begintrans();
....
$db->committrans(); // or $db->rollbacktrans();