2018-06-28

SELECT population.areacode, area.FullName,area.`Name`,population.familymaster,population.FamilyID,population.Relation,population.UserName,reservoirs.ConstructionArea,reservoirs.ReservoirName,population.Sex,population.IDCardNo FROM (`population` LEFT JOIN reservoirs ON population.Ennmcd = reservoirs.Ennmcd) LEFT JOIN area ON population.areacode = area.`Code` into outfile '/tmp/amu/test5.xls' CHARACTER SET gbk;

---

SELECT population.areacode, area.FullName,area.`Name`,population.familymaster,population.FamilyID,population.Relation,population.UserName,reservoirs.ConstructionArea,reservoirs.ReservoirName,population.Sex,population.IDCardNo FROM (`population` LEFT JOIN reservoirs ON population.Ennmcd = reservoirs.Ennmcd) LEFT JOIN area ON population.areacode = area.`Code` into outfile '/tmp/amu/test7.csv' CHARACTER SET utf8 fields terminated by ',' optiionally enclosed by '' escaped by '' lines terminated by '\r\n';

---

<?php
class ShuikuCommand extends CConsoleCommand{
/*

SELECT population.areacode, area.FullName,area.`Name`,population.familymaster,population.FamilyID,population.Relation,population.UserName,reservoirs.ConstructionArea,reservoirs.ReservoirName,population.Sex,population.IDCardNo FROM (`population` LEFT JOIN reservoirs ON population.Ennmcd = reservoirs.Ennmcd) LEFT JOIN area ON population.areacode = area.`Code` LIMIT 10 into outfile /tmp/amu/test.xls CHARACTER SET utf8 fields terminated by ',' optionally enclosed by '' escaped by '' lines terminated by '\r\n';
SELECT population.areacode,area.FullName,area.`Name`,population.familymaster,population.FamilyID,population.Relation,
population.Relation,population.UserName,population.Sex,population.IDCardNo FROM `population` LEFT JOIN area ON population.areacode = area.`Code`

SELECT population.areacode, area.FullName,area.`Name`,population.familymaster,population.FamilyID,population.Relation,population.UserName,reservoirs.ConstructionArea,reservoirs.ReservoirName,population.Sex,population.IDCardNo FROM (`population` LEFT JOIN reservoirs ON population.Ennmcd = reservoirs.Ennmcd) LEFT JOIN area ON population.areacode = area.`Code` LIMIT 10 into outfile '/tmp/amu/test6.csv' CHARACTER SET utf8 fields terminated by ',' optionally enclosed by '' escaped by '' lines terminated by '\r\n';
*/
    public function actionTest(){
        $db = Yii::app()->db;
        //$sql = "SELECT population.areacode,area.FullName,area.`Name`,population.familymaster,population.FamilyID,population.Relation,
//population.UserName,population.Sex,population.IDCardNo FROM `population` LEFT JOIN area ON population.areacode = area.`Code`";
        $sql = 'select * from population limit 1';
        $hData = $db->createCommand($sql)->queryRow();
        $qian = array(" ","　","\t","\n","\r");
        print_r($hData);
        $areacode = str_replace($qian, '', $hData['areacode']);     
        echo $areacode;
    }
    
    public function actionTest2(){
        $qian = array(" ","　","\t","\n","\r");
        $pdo = new \PDO('mysql:host=127.0.0.1;dbname=db_firewall','root','bd_123456');
        $pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);
        $pdo->exec('set names utf8');
        $sql = "select * from population";
        $rows = $pdo->query($sql,PDO::FETCH_ASSOC);
        while ( $row = $rows->fetch() ) {           
            $row['areacode'] = str_replace($qian, '', $row['areacode']);
            $update_sql = 'UPDATE population SET areacode=\''.$row['areacode'].'\' where ID ='.$row['ID'];
            echo $update_sql.PHP_EOL;
            $count = $pdo->exec($update_sql);
            echo $count.PHP_EOL;
        }       
    }

}

suricata规则帮助文档