Sample : mysql - 迈思框架 MyStep Framework

Class: mysql

测试代码：

<?PHP
global $db;
$db->connect(0, 'mystep');
$db->reconnect(1, 'mystep_cms');

//select
$db->build('cms_news_cat')
    ->field('name')
    ->exists(['cms_website', 'web_id'], ['web_id', 'n=', 1])
    ->where('cat_id', 'n=', 1);
echo $db->select(1).';<br /><br />';

$db->build('[reset]');
$field = $db->getFields('cms_admin_cat');
$db->build('cms_admin_cat')->field($field)->where('id', 'n>', '10')->order('id', 1)->limit(5, 6);
echo $db->select(1).';<br /><br />';

$db->build('[reset]');
$db->build('cms_news_show')->where(
      array(
        array('news_id', 'n=', '1', 'or'),
        array(
          array('tag', 'like', '1', 'or'),
          array('tag', 'like', '2%', 'or'),
          array('tag', 'like', '%3', 'or'),
          'and'
        ),
        array(
          array('tag', 'like', '1', 'or'),
          array('tag', 'like', '2%', 'or'),
          array('tag', 'like', '%3', 'or'),
          'or'
        ),
        array('add_date', 'd>', array('now()', 'y-1'), 'or'),
        'and'
      ),
      'and'
    )->order('news_id', 1)->limit(5, 6);
$db->build('cms_news_detail', array(
                              'mode' => 'left',
                              'field' => 'news_id'
                            ))->field('sub_title,content')->where('page', 'n>=', '1')->order('page');
echo $db->select(1).';<br /><br />';

$db->build('[reset]');
$db->build('cms_news_show')->field('*')->where('subject', '<>', 'xxxx')
                                      ->where('news_id', 'n=', '1')
                                      ->where('add_date', 'd>', array('now()', 'y-1'))
                                      ->where('subject', 'f=', 'left(subject, 10)')
                                      ->where('tag', 'like', '1')
                                      ->where('news_id', 'nin', '1, 2, a3, 5a, 5')
                                      ->where('image')
                                      ->where('(isnull(style))')
                                      ->limit(5);
$db->build('cms_news_detail', array(
                              'mode' => 'left',
                              'field' => 'news_id'
                            ))->field('sub_title,content')->where('page', 'n>=', '1')->order('page');
echo $db->select(1).';<br /><br />';

$db->build('[reset]');
$db->build('table_name')->field(['field_name', 'count(*) as cnt'])
    ->group('field_name')
    ->group(['field_name', 'cnt>3'])
    ->group('field_name', ['cnt','n>',3])
    ->group(['field'=>'field_name,field2_name', 'having'=>'cnt>3']);
$db->build('tbl2', array(
    'mode' => 'left',
    'field' => 'news_id'
))->field('col1, col2');
echo $db->select(1).';<br /><br />';

//insert
$db->build('[reset]');
$db->build('cms_admin_cat')->field(array(
      'id' => 0,
      'pid' => '0',
      'name' => 'xxx',
      'file' => 'xxx',
      'path' => '',
      'web_id' => '0',
      'order' => '0',
      'comment' => '010101'
    ))
    ->values(0,0,'xxx','xxx','','000','000','111222')
    ->values(
        [0,0,'xxx','xxx','','001','001','111222'],
        [0,0,'xxx','xxx','','002','002','111222']
    );
echo $db->insert(1).';<br /><br />';
echo $db->replace(1).';<br /><br />';

//update
$db->build('[reset]');
$db->build('cms_news_show')
    ->field(array('path'=>''))
    ->where('news_id', 'n>', '10')
    ->order('news_id', 1)->limit(5, 6);
echo $db->update(1).';<br /><br />';
$db->build('cms_news_show')->reset();

$db->build('cms_news_show')->field(array(
                            'views' => 5,
                            'tag' => 'tag',
                            'add_date'=>'(now())'
                          ))->where('subject', '<>', 'xxxx')
                            ->where('news_id', 'n=', '1')
                            ->where('news_id', 'n=', '2', 'or')
                            ->where('add_date', 'd>', array('now()', 'y-1', ))
                            ->where('(isnull(style))')
                            ->where('tag', 'like', '1')
                            ->where('news_id', 'nin', '1, 2, a3, 5a, 5')
                            ->where('image');
$db->build('cms_news_detail', array(
                              'mode' => 'left',
                              'field' => 'news_id',
                              'field_join' => 't0.news_id'
                            ))->where('page', 'n>=', '1')->order('page');
echo $db->update(1).';<br /><br />';

//delete
$db->build('[reset]');
$db->build('cms_admin_cat')->where('id', 'n>', '38')->order('id', 1)->limit(5);
echo $db->delete(1).';<br /><br />';

$db->build('[reset]');
$db->build('cms_news_show')->where('subject', '<>', 'xxxx')
                            ->where('news_id', 'n=', '1')
                            ->where('add_date', 'd>', array('now()', 'y-1', ))
                            ->where('(isnull(style))')
                            ->where('tag', 'like', '1')
                            ->where('news_id', 'nin', '1, 2, a3, 5a, 5')
                            ->where('image');
$db->build('cms_news_detail', array(
                              'mode' => 'left',
                              'field' => 'news_id',
                              'field_join' => 't0.news_id'
                            ))->where('page', 'n>=', '1')->order('page');
echo $db->delete(1).';<br /><br />';

//echo $db->close();

//create
echo $db->create('my_db', '', 'db', 1).'<br /><br />';
echo $db->create('my_tbl', 'my_col_1, my_col_2', 'idx', 1).'<br /><br />';
echo $db->create('my_tbl', 'tbl2', 'tbl', 1).'<br /><br />';
echo $db->create('my_table', 'my_col char(1000)', 'tbl', 1).'<br /><br />';
echo $db->create('my_table', [
    'col' => [
        'id int identity(1,1)',
        'my_col_1 char(1000)',
        'my_col_2 int',
    ],
    'pri' => 'id',
    'uni' => 'my_col_2',
    'idx' => 'my_col_2',
    'charset' => 'GBK',
    'comment' => 'comments'
], 'tbl', 1);

运行结果：

select `name` from `cms_news_cat` as t0 where EXISTS (select 1 from `cms_website` where `cms_website`.`web_id`=t0.`web_id` and `web_id` = 1 ) and `cat_id` = 1;

select `id`,`pid`,`name`,`path`,`public`,`order`,`comment` from `cms_admin_cat` as t0 where `id` > 10 order by `id` desc limit 5,6;

select `t0`.*,`t1`.`sub_title`,`t1`.`content` from `cms_news_show` as t0 left join `cms_news_detail` as t1 using(`news_id`) where (t0.`news_id` = 1 and (t0.`tag` like '%1%' or t0.`tag` like '2%' or t0.`tag` like '%3') or (t0.`tag` like '%1%' or t0.`tag` like '2%' or t0.`tag` like '%3') or (add_date > 'now()' or add_date > 'y-1')) and `t1`.`page` >= 1 order by `t0`.`news_id` desc,`t1`.`page` limit 5,6;

select `t0`.*,`t1`.`sub_title`,`t1`.`content` from `cms_news_show` as t0 left join `cms_news_detail` as t1 using(`news_id`) where `t0`.`subject` <> 'xxxx' and `t0`.`news_id` = 1 and (t0.`add_date` > 'now()' and t0.`add_date` > 'y-1') and `t0`.`subject` = left(subject, 10) and `t0`.`tag` like '%1%' and `t0`.`news_id` in (1,2,0,5,5) and `t0`.`image` is not NULL and isnull(t0.`style`) and `t1`.`page` >= 1 order by `t1`.`page` limit 5;

select `t0`.`field_name`,count(*) as `cnt`,`t1`.`col1`,`t1`.`col2` from `table_name` as t0 left join `tbl2` as t1 using(`news_id`) group by t0.`field_name`, t0.`field2_name` having t0.`cnt` > 3 ;

insert LOW_PRIORITY into `cms_admin_cat` (`id`,`pid`,`name`,`file`,`path`,`web_id`,`order`,`comment`) values ('0','0','xxx','xxx','','0','0','010101'),('0','0','xxx','xxx','','000','000','111222'),('0','0','xxx','xxx','','001','001','111222'),('0','0','xxx','xxx','','002','002','111222');

replace into `cms_admin_cat` (`id`,`pid`,`name`,`file`,`path`,`web_id`,`order`,`comment`) values ('0','0','xxx','xxx','','0','0','010101'),('0','0','xxx','xxx','','000','000','111222'),('0','0','xxx','xxx','','001','001','111222'),('0','0','xxx','xxx','','002','002','111222');

update LOW_PRIORITY `cms_news_show` set `path`='' where `news_id` > 10 order by `news_id` desc limit 5,6;

update LOW_PRIORITY `cms_news_show` as t0 left join `cms_news_detail` as t1 on t1.`news_id`=`t0`.`news_id` set `t0`.`views`='5',`t0`.`tag`='tag',`t0`.`add_date`=now() where `t0`.`subject` <> 'xxxx' and `t0`.`news_id` = 1 or `t0`.`news_id` = 2 and (t0.`add_date` > 'now()' and t0.`add_date` > 'y-1') and isnull(t0.`style`) and `t0`.`tag` like '%1%' and `t0`.`news_id` in (1,2,0,5,5) and `t0`.`image` is not NULL and `t1`.`page` >= 1;

delete LOW_PRIORITY from `cms_admin_cat` where `id` > 38 order by `id` desc limit 5;

delete LOW_PRIORITY t0, t1 from `cms_news_show` as t0 left join `cms_news_detail` as t1 on t1.`news_id`=`t0`.`news_id` where `t0`.`subject` <> 'xxxx' and `t0`.`news_id` = 1 and (t0.`add_date` > 'now()' and t0.`add_date` > 'y-1') and isnull(t0.`style`) and `t0`.`tag` like '%1%' and `t0`.`news_id` in (1,2,0,5,5) and `t0`.`image` is not NULL and `t1`.`page` >= 1;

create database IF NOT EXISTS `my_db` default charset utf8mb4 COLLATE utf8mb4_unicode_ci

create index `idx_84cc64db00158853bd518312bffeb103` on my_tbl(my_col_1, my_col_2)

create table IF NOT EXISTS `my_tbl` like `tbl2`

create table IF NOT EXISTS `my_table` ( my_col char(1000) )ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

create table IF NOT EXISTS `my_table` ( `id` int identity(1,1), `my_col_1` char(1000), `my_col_2` int, INDEX idx_ac7ff60c2ac568784632d317a708c059(my_col_2), UNIQUE (`my_col_2`), PRIMARY KEY (`id`) )ENGINE=MyISAM DEFAULT CHARSET=GBK COMMENT='comments'