tp获取sql_【三十三】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'

三：使用对象方式查询

namespace Home\Controller;useThink\Controller;useThink\stdClass;class EleController extendsController

{$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文件

namespace Home\Model;useThink\Model;class User1Model extendsModel

{//定义属性

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文件

namespace Home\Controller;useThink\Controller;useHome\Model\User1Model;class User1Controller extendsController {public functionmodel(){//ps：一定要填入表名，user1

// 他会先去model文件夹找有没有user1model

// 如果有就应用

// 如果没有，会直接引用model基类

$user=D('user1');//sql语句：SELECT * FROM `user1` WHERE `id` = 1

var_dump($user->scope('sql1')->select());

}

}