数据库需要的条件:1.DAO(就是传统的Pdo方式,所以要PDO开启)
2.PHP5.4版本
CURD(增删改查)CUD是写操作 R是读操作
参数查询:防止sql注入
db组件在config/web.php的components配置中
//数据库配置 'db' => require(__DIR__ . '/db.php'),//db.php文件在同级目录config下//db.php文件
<?php return [ 'class' => 'yii\db\Connection',//class指向yii\db\Connection 'dsn' => 'mysql:host=localhost;dbname=test', 'username' => 'root', 'password' => '123456', 'charset' => 'utf8', ];?>
Connection.php位于vendor/yiisoft/yii2/db/Connection.php
在basic下创建entry/BasicUser.php文件
<?php namespace app\entry; class BasicUser{ //类名必须与文件名相同 const TABLE_NAME='test'; //添加数据(增) public function Add(){ //1.通过自定义sql语句方式 /* $db=\yii::$app->db; $cmd=$db->createCommand('INSERT INTO '.self::TABLE_NAME ."(username,password,nickname) VALUES('yyt','123456','土豆')" ); $cmd->execute(); //返回数据库表受影响的行数 return $db->lastInsertID; //返回插入的主键ID */ //2.通过面向对象方式 /* \yii::$app->db->createCommand()->insert( self::TABLE_NAME, ['username'=>'ykt', 'password'=>md5('123'), 'nickname'=>'机器猫'] )->execute() ; return \Yii::$app->db->lastInsertID; */ //3.多条记录同时插入 //自定义sql语句的多条记录同时插入 //insert into table_name(colum) VALUES(val1),(val2),(val3) \Yii::$app->db->createCommand()->batchInsert( self::TABLE_NAME, ['username','password','nickname'], [ ['雷军',md5(time()),'小米'], ['强东',md5(time()),'京东'] ] )->execute(); return \yii::$app->db->lastInsertID; } //删除数剧(删) public function Delete(){ //返回的是数据库表的受影响行数 return \yii::$app->db->createCommand()->delete( self::TABLE_NAME,//表名 ['id'=>3] //条件 )->execute(); } //更新数据(改) public function Update(){ return \yii::$app->db->createCommand()->update( self::TABLE_NAME, ['nickname'=>'哈哈'], //参数为关联数组 ['id'=>4] //条件 )->execute(); } //查询数据() public function GetList(){ return \yii::$app->db->createCommand('SELECT * FROM '.self::TABLE_NAME)->queryAll(); } public function GetInfo($username){ // return \yii::$app->db->createCommand('SELECT * FROM '.self::TABLE_NAME.' where username='.'"'.$username.'"')->queryAll(); //参数绑定,防止sql注入 bindvalues; $sql='SELECT * FROM '.self::TABLE_NAME." where username=:UN";//:UN是占位符,防止sql注入 $cmd=\yii::$app->db->createCommand($sql); $cmd->bindValues([':UN'=>$username]); return $cmd->queryAll(); } //事务的使用 public function Trans($money){ $db=\yii::$app->db; $trans=$db->beginTransaction();//开启事务 try{ /* * A账户向B账户转500 A-500;B+500,当A账户操作失败 * A账户减少500 */ //yyt-500 $yyt=$db->createCommand(' UPDATE '.self::TABLE_NAME.' SET rest=rest-:RM WHERE id=2'); $yyt->bindValues([':RM'=>$money]); $yyt->execute(); //ykt增加金额 $ykt=$db->createCommand(' UPDATE '.self::TABLE_NAME.' SET rest=rest+:RN WHERE id=4'); $ykt->bindValues([':RN'=>$money]); $ykt->execute(); $trans->commit(); return true; } catch(Exception $e){ $trans->rollBack(); return false; } } }在DataController.php中创建方法测试数据库操作
<?php namespace app\controllers; use app\entry\BasicUser; use yii\web\Controller; class DataController extends Controller{public function actionTry(){ //$res=(new BasicUser())->Add(); 增 //$res=(new BasicUser())->Delete(); 删 //$res=(new BasicUser())->Update(); 改 //$res=(new BasicUser())->GetList(); //$res=(new BasicUser())->GetInfo('ykt'); $res=(new BasicUser())->Trans(100); echo json_encode($res); } }