模型的使用
$m = new Model('User');//$m = M('User')等同于 new Model,User是表名,U要大写,跟数据库大小写无关
$m->select();
使用模型的实例可以对数据进行操作,操作的工作一般就是对数据库进行增删改查 CURD
增 -C Create $m->add()
$m = M('User');
$m->user_name="绿";
$m->age="50";
$m->money="45";
$id = $m->add();//返回的是添加数据的id
//INSERT INTO `tt_user` (`user_name`,`age`,`money`) VALUES ('绿',50,'45');
删 -D Delete $m->delete()
$id = $m->delete(4);
//DELETE FROM `tt_user` WHERE `id` = 8;
$id = $m->where('money=30')->delete();//带条件使用where()
//DELETE FROM `tt_user` WHERE `money` = 30;
改 -U Updata $m->save()
$m = M('User');
$data[id]='4';
$data[user_name]="卢俊";
$data[money]="79";
$id = $m->save($data);//返回值是受影响行数
//UPDATE `tt_user` SET `user_name`='卢俊',`money`='79' WHERE `id` = 4;
查 -R Read $m->select()
一、普通查询
a.字符串查询
$arr = $m->select();
//SELECT * FROM `tt_user`;
$arr = $m->find(2);//只可以查询单条数据
//SELECT * FROM `tt_user` WHERE `id` = 2;
$arr = $m->where('age=50')->getField('user_name');//查询单个字段
//SELECT `user_name` FROM `tt_user` WHERE ( age=50 );
$arr = $m->where('age between 31 and 49')->select();
//SELECT * FROM `tt_user` WHERE ( age between 31 and 49 )
b.数组查询
$data[age]=50;
$data[user_name]='绿俊';
$data[_logic]='or';//默认是and,如果使用or关系,需要添加数组值
$arr = $m->where($data)->select();
//SELECT * FROM `tt_user` WHERE `age` =50 OR `user_name` = '绿俊'
二、表达式查询方式
1.大小等于
$data[age]=array('GT','30');
//SELECT * FROM `tt_user` WHERE `age` > '30';
$arr = $m->where($data)->select();
EQ等于
NEQ不等于
GT大于
EGT大于等于
LT小于
ELT小于等于
2.LIKE 模糊查询
$data[user_name]=array('like','%张');
$arr = $m->where($data)->select();
//SELECT * FROM `tt_user` WHERE (`user_name` LIKE '%张');
NOTLIKE 模糊反向查询,NOTLIKE中间没有空格
$data[user_name]=array('notlike','%张');
$arr = $m->where($data)->select();
//SELECT * FROM `tt_user` WHERE (`user_name` NOTLIKE '%张');
注意:一个字段要匹配多个通配符
$data[user_name]=array('like',array('%张','晶%'),'and');//默认关系是or,可以通过设置第三个参数改变
$arr = $m->where($data)->select();
SELECT * FROM `tt_user` WHERE (`user_name` LIKE '%张' AND `user_name` LIKE '晶%');
3.BETWEEN 范围查询
$data[age]=array('between',array('31','49'));
//SELECT * FROM `tt_user` WHERE `age` BETWEEN '31' AND '49';
$data[age]=array('not between',array('31','49'));//使用not between 一定要空格
//SELECT * FROM `tt_user` WHERE `age` NOT BETWEEN '31' AND '49';
4.IN 选择查询 NOT IN
$data[user_name]=array('in',array('张三','晶晶'));
//SELECT * FROM `tt_user` WHERE `user_name` IN ('张三','晶晶');
三、区间查询
$data[age]=array(array('GT','30'),array('LT','50'));//默认关系是and ,可以通过设置第三个产生改变
//SELECT * FROM `tt_user` WHERE ( `age` > '30' AND `age` < '50' );
四、统计查询
1.avg 获取平均数
$data[id] = array('GT','3');
$arr = $m->where($data)->avg('money');
//SELECT AVG(money) AS tp_avg FROM `tt_user` WHERE `id` > '3'
2.count 获取个数
3.max 获取最大数
4.min 获取最小数
5.sum 获取总数
五、SQL直接查询
$mm = M();
1.query主要来用处理读取数据的 成功返回结果集,失败返回 false
$arr = $mm->query("select * from tt_user");
2.execute主要来用处理写入数据的 成功返回影响行数,失败返回0
$arr = $mm->execute("insert into tt_user (user_name,age,money) values ('兰焜','30','40000')");