【三十三】thinkphp之SQL查询语句(全)
一:字符串条件查询
//直接实例化Model $user=M('user1'); var_dump($user->where ('id=1 OR age=55')->select());
最终生成的sql语句为:SELECT * FROM `user1` WHERE ( id=1 OR age=55 )
PS:where 查询方法里面只要包含条件即可,多个条件加上 AND 等连接符即可
二:使用索引数组作为查询条件
$user=M('user1'); $condition['age']='55'; $condition['name']='zs'; // 索引数组查询的默认逻辑关系是 AND,如果想改变为 OR,可以使用_logic 定义查询逻辑 $condition['_logic'] = 'OR'; var_dump($user->where($condition)->select());
最终生成的sql语句为:SELECT * FROM `user1` WHERE `id` = ‘1’ AND `name` = 'zs'
三:使用对象方式查询
<?php namespace Home\Controller; use Think\Controller; use Think\stdClass; class EleController extends Controller { $user=M('user1'); $condition=new \stdClass; $condition->id = '1'; var_dump($user->where($condition)->select()); }
最终生成的sql语句为:SELECT * FROM `user1` WHERE `id` = ‘1’
四:表达式查询
查询表达式格式:$map['字段名'] = array('表达式','查询条件');
$user=M('user1'); $map['age'] = array('eq', 55); //where 为 age=55 var_dump($user->where($map)->select());
五:快捷查询
//使用相同查询条件 $user = M('user1'); $map['name|email'] = 'a'; //'|'换成'&'变成AND var_dump($user->where($map)->select()); // 不同字段不同查询条件 //使用不同查询条件 $user = M('user1'); $map['name&email'] =array('a','test@qq.com','_multi'=>true); var_dump($user->where($map)->select());
第一条查询的结果:SELECT * FROM `user1` WHERE ( `name` = 'a' OR `email` = 'a' )
第二条查询的结果:SELECT * FROM `user1` WHERE ( (`name` = 'a') AND (`email` = 'test@qq.com') )
六:区间查询
// 区间查询 $user = M('user1'); $map['id'] = array(array('gt', 1), array('lt', 4)); var_dump($user->where($map)->select()); //第三个参数设置逻辑OR $user = M('User1'); $map['id'] = array(array('gt', 1), array('lt', 4), 'OR'); var_dump($user->where($map)->select() }
七:组合查询
组合查询是基于索引数组查询方式的一个扩展性查询,添加了字符串查询(_string)、复合查询(_complex)、请求字符串查询(_query),由于采用的是索引数组,重复的会被覆盖。
//字符串查询(_string) $user = M('user1'); $map['name'] = array('eq', 'zs'); $map['_string'] ='age="30" AND email="zs@qq.com"'; var_dump($user->where($map)->select()); //请求字符串查询(_query) $user = M('user1'); $map['id'] = array('eq', "1"); $map['_query'] ='name=zs&email=zs@qq.com&_logic=OR'; var_dump($user->where($map)->select()); //复合查询(_complex) $user = M('user1'); $where['name'] = array('like', 'z'); $where['id'] = 1; $where['_logic'] = 'OR'; $map['_complex'] = $where; $map['id'] = 3; $map['_logic'] = 'OR'; var_dump($user->where($map)->select());
第一条查询语句:SELECT * FROM `user1` WHERE `name` = 'zs' AND ( age="30" AND email="zs@qq.com" )
第二条查询语句:SELECT * FROM `user1` WHERE `name` = 'zs' AND ( age="30" AND email="zs@qq.com" ) AND ( `name` = 'zs' OR `email` = 'zs@qq.com' )
第三条查询语句:SELECT * FROM `user1` WHERE `name` = 'zs' OR ( age="30" AND email="zs@qq.com" ) OR ( `name` = 'zs' OR `email` = 'zs@qq.com' ) OR ( `name` LIKE 'z' OR `id` = 1 )
八:统计查询
//数据总条数 //SHOW COLUMNS FROM `user1` $user = M('user1'); var_dump($user->count()); //字段总条数,遇到NULL不统计 //SELECT COUNT(*) AS tp_count FROM `user1` LIMIT 1 $user = M('user1'); var_dump($user->count('email')); //最大值 //SELECT MAX(id) AS tp_max FROM `user1` LIMIT 1 $user = M('user1'); var_dump($user->max('id')); //最小值 //SELECT MIN(id) AS tp_min FROM `user1` LIMIT 1 $user = M('user1'); var_dump($user->min('id')); //平均值 //SELECT AVG(id) AS tp_avg FROM `user1` LIMIT 1 $user = M('user1'); var_dump($user->avg('id')); //求总和 //SELECT SUM(id) AS tp_sum FROM `user1` LIMIT 1 $user = M('user1'); var_dump($user->sum('id'));
九:动态查询
// 1.getBy 动态查询 //查找email=xiaoin@163.com的数据 //SELECT * FROM `user1` LIMIT 1 $user = M('user1'); var_dump($user->getByemail('zs@qq.com')); // 2.getFieldBy 动态查询 //通过user得到相对应id值 //SELECT `id` FROM `user1` LIMIT 1 $user = M('user1'); var_dump($user->getFieldByUser('ls', 'id'));
十:SQL查询
// 1.query 读取 //查询结果集,如果采用分布式读写分离,则始终在读服务器执行 //SELECT * FROM user1 $user = M('user1'); var_dump($user->query('SELECT * FROM user1')); // 2.execute写入 //更新和写入,如果采用分布式读写分离,则始终在写服务器执行 //UPDATE user1 set name="xuexi" WHERE id="1"; $user = M('user1'); var_dump($user->execute('UPDATE user1 set name="xuexi" WHERE id="1";'));
十一:连贯查询
通过连贯操作可以有效的提供数据存取的代码清晰度和开发效率,并且支持所有的 CURD 操作
//连贯操作 //PS:这里的 where、order 和 limit 方法都是连贯操作方法,所以它们都能返回$user本身,可以互换位置。而 select 方法不是连贯方法,需要放在最后,用以显示数据集。 //SELECT * FROM `user1` WHERE ( id in (1,2,3,4) ) LIMIT 2 $user=M('user1'); var_dump($user->where('id in (1,2,3,4)')->limit(2)->select()); //数组操作 //SELECT * FROM `user1` WHERE id in (1,2,3,4) LIMIT 2 $user = M('user1'); var_dump($user->select(array('where'=>'id in (1,2,3,4)', 'limit'=>'2', ))); // CURD处理 // SELECT * FROM `user1` WHERE ( id=1 ) LIMIT 1 // DELETE FROM `user1` WHERE ( id=2 ) $user = M('user1'); var_dump($user->where('id=1')->find()); var_dump($user->where('id=2')->delete());
1.where(支持字符串条件、数组条件(推荐用法)和多次调用。)
//字符串方式 //SELECT * FROM `user1` WHERE ( id=6 ) var_dump($user->where('id=6')->select()); //索引数组方式 // SELECT * FROM `user1` WHERE `id` = 6 $map['id']=6; var_dump($user->where($map)->select()); //多次调用方式 SELECT * FROM `user1` WHERE `id` = 6 AND ( name="test" ) $map['id']=array('eq',6); var_dump($user->where($map)->where('name="test"')->select());
2.order 用于对结果集排序
// 倒序 // SELECT * FROM `user1` ORDER BY id desc var_dump($user->order('id desc')->select()); // 第二排序 // SELECT * FROM `user1` ORDER BY id desc,age desc var_dump($user->order('id desc,age desc')->select()); //数组形式,防止字段和mysql关键字冲突 // SELECT * FROM `user1` ORDER BY `id` DESC,`email` DESC $map['id']=1; var_dump($user->order(array('id'=>'DESC','email'=>'DESC'))->select());
3.feild(feild 方法可以返回或操作字段,可以用于查询和写入操作。)
// 只显示id和name两个字段 // SELECT `id`,`name` FROM `user1` var_dump($user->field('id,name')->select()); //使用SQL函数和别名 // SELECT SUM(id) as count,`name` FROM `user1` var_dump($user->field('SUM(id) as count,name')->select()); // 使用数组参数结合SQL函数 // SELECT `id`,LEFT(name,1) AS `left_user` FROM `user1` var_dump($user->field(array('id','LEFT(name,1)'=>'left_user'))->select()); //获取所有字段 // SELECT * FROM `user1` var_dump($user->field()->select()); //用于写入 $user->field('name,email')->create();
4.limit(主要用于指定查询和操作的数量)
//限制结果集数量 // SELECT * FROM `user1` LIMIT 2 var_dump($user->limit(2)->select()); //分页查询 // SELECT * FROM `user1` LIMIT 1,2 var_dump($user->limit(1,2)->select());
5.page(page 方法完全用于分页查询)
//page分页 // SELECT * FROM `user1` LIMIT 3,3 var_dump($user->page(2,3)->select());
6.table(用于数据表操作,主要是切换数据表或多表操作)
//切换数据表 // SELECT * FROM `test_user` var_dump($user->table('test_user')->select());
7.alias (用于设置数据表别名)
// 设置别名 // SELECT * FROM user1 a var_dump($user->alias('a')->select());
8.group(用于对结合函数统计的结果集分组)
// 分组统计 // SELECT `name`,max(id) FROM `user1` GROUP BY id var_dump($user->field('name,max(id)')->group('id')->select());
9.having(用于配合 group 方法完成从分组的结果中再筛选数据)
//分组统计结合having // SELECT `user`,max(id) FROM `user1` GROUP BY id HAVING id>2 var_dump($user->field('user,max(id)')->group('id')->having('id>2')->select());
10.comment (用于对 SQL 语句进行注释)
//SQL注释 // SELECT * FROM `user1` /* test */ var_dump($user->comment('test')->select());
命名空间
命名范围其实就是将 SQL 语句封装在模型定义类里,而不在控制器里。
在Model下新建一个User1Model.class.php文件
<?php namespace Home\Model; use Think\Model; class User1Model extends Model { //定义属性 protected $_scope = array( //属性名必须是_scope 'sql1'=>array( 'where'=>array('id'=>1), ), 'sql2'=>array( 'order'=>'date DESC', 'limit'=>2, ), 'default'=>array( 'where'=>array('id'=>2), ), ); }
命名范围支持的属性有:where、field、order、table、limit、page、having、group、lock、distinct、cache
在Controller目录下新建一个User1Controller.class.php文件
<?php namespace Home\Controller; use Think\Controller; use Home\Model\User1Model; class User1Controller extends Controller { public function model(){ // ps:一定要填入表名,user1 // 他会先去model文件夹找有没有user1model // 如果有就应用 // 如果没有,会直接引用model基类 $user=D('user1'); // sql语句:SELECT * FROM `user1` WHERE `id` = 1 var_dump($user->scope('sql1')->select()); } }