Yii2数据库查询汇总

基础查询

Customer::find()->one();    此方法返回一条数据;

Customer::find()->all();    此方法返回所有数据;

Customer::find()->count();    此方法返回记录的数量;

Customer::find()->average();    此方法返回指定列的平均值;

Customer::find()->min();    此方法返回指定列的最小值 ;

Customer::find()->max();    此方法返回指定列的最大值 ;

Customer::find()->scalar();    此方法返回值的第一行第一列的查询结果;

Customer::find()->column();    此方法返回查询结果中的第一列的值;

Customer::find()->exists();    此方法返回一个值指示是否包含查询结果的数据行;

Customer::find()->batch(10);  每次取10条数据 

Customer::find()->each(10);  每次取10条数据,迭代查询 

//根据sql语句查询:查询name=test的客户
Customer::model()->findAllBySql("select * from customer where name = test"); 

//根据主键查询:查询主键值为1的数据
Customer::model()->findByPk(1); 

//根据条件查询(该方法是根据条件查询一个集合,可以是多个条件,把条件放到数组里面) 
Customer::model()->findAllByAttributes(['username'=>'admin']); 

//子查询
$subQuery = (new Query())->select('COUNT(*)')->from('customer');
// SELECT `id`, (SELECT COUNT(*) FROM `customer`) AS `count` FROM `customer`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('customer');

//关联查询:查询客户表(customer)关联订单表(orders),条件是status=1,客户id为1,从查询结果的第5条开始,查询10条数据
$data = (new Query())
    ->select('*')
    ->from('customer')
    ->join('LEFT JOIN','orders','customer.id = orders.customer_id')
    ->where(['status'=>'1','customer.id'=>'1'])
    ->offset(5)
    ->limit(10)
    ->all()

继续阅读Yii2数据库查询汇总

Yii2 注册表单验证规则

public function rules()
    {
        return [
            ['username', 'filter', 'filter' => 'trim'],
            ['username', 'required'],
            ['username', 'unique', 'targetClass' => '\common\models\User', 'message' => '该用户名已经被占用.'],
            ['username', 'string', 'min' => 4, 'max' => 12],
            ['username','match','pattern'=>'/^[a-zA-Z0-9_]+$/','message'=>'{attribute}只能由英文字母、数字、下划线组成'],

            ['realname', 'filter', 'filter' => 'trim'],
            ['realname', 'required'],
            ['realname', 'string', 'min' => 2, 'max' => 4],
            ['realname','match','pattern'=>'/^[\x{4e00}-\x{9fa5}]+$/u','message'=>'{attribute}必须为中文汉字'],

            ['organization_name', 'required'],
            ['organization_name', 'filter', 'filter' => 'trim'],
            ['organization_name', 'string', 'min'=>4,'max' => 20],

            ['email', 'required'],
            ['email', 'email'],
            ['email', 'unique', 'targetClass' => '\common\models\User', 'message' => '该电子邮箱已经被占用.'],

            ['mobile', 'required'],
            ['mobile', 'integer'],
            ['mobile','match','pattern'=>'/^1[0-9]{10}$/','message'=>'{attribute}必须为1开头的11位纯数字'],
            ['mobile', 'string', 'min'=>11,'max' => 11],
            ['mobile', 'unique', 'targetClass' => '\common\models\User', 'message' => '该手机号码已经被占用.'],

            ['smsCode', 'required'],
            ['smsCode', 'integer'],
            ['smsCode', 'string', 'min'=>4,'max' => 4],
            //['smsCode', 'required','requiredValue'=>$this->getSmsCode(),'message'=>'手机验证码输入错误'],

            ['password', 'required'],
            ['password', 'string', 'min' => 6,'max' => 16,'message'=>'{attribute}位数为6至16位'],
            ['password2','compare','compareAttribute'=>'password','message'=>'两次密码不一致']
        ];
            ['nickname','match','pattern'=>'/^[a-zA-Z0-9_\x{4e00}-\x{9fa5}]/u','message'=>'{attribute}只能为字母下划线和汉字'],]    }

 

yii2 路由重写

Yii配置

<?php
return [
    'components' => [
        'urlManager' => [
            'enablePrettyUrl' => true, //启用URL美化
            'showScriptName' => false, //禁用 index.php
            'suffix' => '.html', //启用后缀
            'rules' => [
            ],
        ]
    ],
];
'rules' => [
      '<controller:\w+>/<action:\w+>/<pid:\d+>'=>'<controller>/<action>'
 ]
<controller:\w+> //这是指匹配控制器
<action:\w+> //这是指匹配控制器内的方法
<pid:\d+> //这里指获取相应的请求参数的key \Yii::$app-&gt;request->get('pid');

 Nginx配置

在配置文件增加

if (!-e $request_filename){  
rewrite ^/(.*) /index.php last;  
}

继续阅读yii2 路由重写