ThinkPHP数据库语句

给数据库取别名

• 使用alias()方法，给数据库取别名，这样调用的时候就不用重复创建实例对象

$obj=Db::name('cms_my')->alias('nt');
&obj->select();
&obj->find();

TP5数据查询

• 查询后使用数组展示

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])->column('name');

$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])->where('name','我的名字')->select();

• 查询后使用对象展示

只会查询一条数据

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->where('name','我的名字')
->find();

• 单独取出一个字段组成数组

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->column('name');

• 取出n个字段组成数组,并且下标改为id

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->column(['name','thumb'],'id');

• 只拿出一个字段

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->value('name');

• 指定输出几条数据

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->limit(3)
->select();

• 同一字段多个查询条件

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->where('name',['like','thinkphp%'],['like','%thinkphp'])
->where('id',['>',0],['<>',10],'or')
->find();

• 根据字段、时间排序

$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->order('sort', 'DESC')
->select();

• 清除上次查询的数据

因为是调用引用查询的数据，在第二次查询时候，会遗留上一次的查询条件，所以要清除掉。

// removeOption 的效率更好、准确
$obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->removeOption('id')
->removeOption('isdelete')
->select();

TP5数据插入

• 插入一条数据

$data = [
    'name' => $name,
    'pwd'  => $pwd,
    'create_time' => time()
];
Db::name('user')->insert($data);

//或者
Db::name('user')->data($data)->insert();

• mysql 的 REPLACE 写入

// 如果有唯一性索引，会删除之前的相同一条数据
Db::name('user')->insert($data, 'true');

• 写入成功后，返回id

Db::name('user')->insertGetId($data);
// 或
Db::name('user')->insert($data);
return Db::insertGetId();

• 批量写入

$dataAll = [
    [
      'name' => $name,
      'pwd'  => $pwd,
      'create_time' => time()
    ],
    [
      'name' => $name2,
      'pwd'  => $pwd2,
      'create_time' => time()
    ]
];
Db::name('user')->insert($dataAll);

TP5数据修改

• 修改数据

$id = 18;
$data = ['name'=>'小皮'];

Db::name('user')->where('id',$id)->updata($data);

• data()方法修改数据,俩边数据会合并，依次修改

$id = 18;
$data = ['name'=>'小皮'];

Db::name('user')->where('id',$id)
->data($data)
->updata(['pwd'=>'123']);

• 数据增值、减值

Db::name('user')->where('id',$id)
->inc('view')
->updata();

Db::name('user')->where('id',$id)
->dec('view', 3)
->updata();

• 使用MySQL的函数，更新 exp() 、 raw()

Db::name('user')->exp('email', 'UPPER(email)')->update()

$data=[
	'name' => '小白',
    'email' => Db::raw('UPPER(email)'),
    'proce' => Db::raw('price - 3')
    'id' => 28
];
Db::name('user')->update($data);

• 只修改单独一个字段

Db::name('user')->where('id',39)->setField('name','proce');

• 只修改单独一个字段,setInc()增值、setDec()减值

Db::name('user')->where('id',39)->setInc('price');

Tp5删除数据

• 删除一条数据

 // delete里面放删除的主建id
Db::name('user')->delete(39);

• 删除多条数据

 // delete里面放删除的主建id
Db::name('user')->delete([39,99,106]);

• 通过where来删除，比较常规

Db::name('user')->where('id',99)->delete();

• 删除表的所有数据

Db::name('user')->delete(true);

查询表达式

• 比较查询

有几种查询方式 <>, >, <, >=, <= 可以筛选出符合比较值的数据

Db::name('user')->where('id','<>',80)->select();

• 模糊查询

%name 查询任何开头只要结尾一样的 %name% 查询只要中间又符合的 name% 查询任何结尾只要开头一样的

Db::name('user')->where('name','like','%MyName%')->select();

// 多个查询条件,符合任何一个条件都会筛选出来
Db::name('user')->where('name','like',['%MyName%','shuai%'],'or')->select();
// 快捷方式 ———— 符合条件的所有
Db::name('user')->whereLike('name','%MyName%')->select();
// 快捷方式 ———— 不符合条件的所有
Db::name('user')->whereNotLike('name','%MyName%')->select();

• between查询取区间

Db::name('user')->where('id','between','10,20')->select();

// 多个查询条件,筛选出1到10之间
Db::name('user')->where('name','between',['1','10'],'or')->select();

// 快捷方式 ———— 符合条件的所有
Db::name('user')->whereBetween('name',[10,20])->select();
// 快捷方式 ———— 不符合条件的所有
Db::name('user')->whereNotBetween('name',[xiaom,huawei])->select();

• in查询选择固定几个值

// 选择id符合10，20的数据
Db::name('user')->where('id','in','10,20')->select();

// 快捷方式 ———— 符合条件的所有
Db::name('user')->whereIn('name','xiaom,huawei')->select();
// 快捷方式 ———— 不符合条件的所有
Db::name('user')->whereNotIn('name','10,20'])->select();

• 查询为null的数据

Db::name('user')->whereNull('name')->select();
Db::name('user')->whereNotNull('name')->select();

• 自定义查询语句

Db::name('user')->where('id','exp','IN(19,21,25))->select();

• 数组拼装方式查询

Db::name('user')->where([
	['gender','=','男'],
    ['age','=','15']
])->select();

• 复杂的数组拼装，通过变量传递

$map[] = ['gender','=','男'];
$map[] = ['price','in',[60,70,80]];
Db::name('user')
->where($map)
->select();

• 字符串形式传递，简单粗暴的查询方式

Db::name('user')
->where('gender="男" AND price IN (60,70,90)')
->select();

时间查询

• where时间查询

Db::name('user')->where('create_time','> time',2018-1-1)->select();
//或者
Db::name('user')->whereTime('create_time','>',2018-1-1)->select();

• 时间区间查询

Db::name('user')->where('create_time','between time',['2018-1-1','2018-1-5'])->select();
// 或者
Db::name('user')->whereBetween('create_time',['2018-1-1','2018-1-5'])->select();
// 再或
Db::name('user')->whereBetweenTime('create_time','2018-1-1','2018-1-5')->select();

• 快捷查出指定时间的数据

• $q_time 查找的参数可以是：
• 时间戳 1603785741
• 特指日期时间 last year（去年） year（今年） last month（上个月） week（这个星期） yesterday（昨天）。。。。
• 还可以简化为查询 'y'、'm'、'w'、'd' 今年、本月、本周、今天
• 查几个小时内的数据 '-2 hours'

$q_time = ‘1603785741’
$obj=Db::name('cms_my')->alias('nt');
$data_list = $obj->where(['nt.isdelete'=>0,'nt.status'=>1])
->whereTime('createtime', $q_time)
->select();

聚合、原生、子查询

• 查询数据数量

Db::name('user')->count();
// 加上字段会忽略空值
Db::name('user')->count('uid');

• 查询数据最大值

Db::name('user')->max('num'); 	     //100
Db::name('user')->max('num',false); //100.00

• 查询数据平均值

Db::name('user')->avg('num');

• 查询数据总和

Db::name('user')->sum('num');

• 联合子查询

$subQuery = Db::name('two')
->field('uid')
->where('gender','男')
->buildSql(true);

$result = Db::name('one')
->where('id','exp','IN'.$subQuery)
->select();

// 或者用闭包的形式
$result = Db::name('one')->where('id','in',function($query){
	$query->name('two')
          ->field('uid')
          ->where('gender','男');
})->select();

• 原生查询

Db::query('select * from tp_user');

Db::execute('update tp_user set username="吊毛" where id=29')

field查询

• 指定查询某几个字段

Db::name('user')->field('id','name','email')->select();

• 指定查询某几个字段，并且取别名

Db::name('user')
->field('id', 'name as username', 'email as  hhh')
->select();

• 指定查询某几个字段，并且使用MySQL函数

Db::name('user')
->field('id', 'SUM(num)')
->select();

• 指定查询某几个字段，并且使用MySQL函数,取出字段值的前五个字符

Db::name('user')
->field(['id', 'LEFT(email,5)'=>])
->select();

• 指定不查询某几个字段

Db::name('user')
->field('name,email', true)
->select();
// 或者
Db::name('user')
->field(['name','email'], true)
->select();

• 指定插入某几个字段

$data=[
	'username'=>'名字',
    'password'=>'123123',
    'gender'=>'girl',
    'details'=>'123@.com',
    ...
]
Db::name('user')
->field('name,details')
->insert($data);

分页展示

• 限制输出数据个数

Db::name('user')->limit(5)->select();

• limit分页模式，从第n条开始显示几条

limit来实现分页是很不理想，需要计算从第几条到第几条。

适合指定个数

Db::name('user')->limit(2,5)->select();//第一次分页第二条开始选五条数据
Db::name('user')->limit(6,5)->select();//第一次分页第六条开始选五条数据

• page分页模式

page实现分页比较理想，不需要计算直接指哪几条

适合分页

Db::name('user')->page(1,5)->select();//第一条到第五条数据
Db::name('user')->page(3,5)->select();//第十一条到第十五条数据

排序、统计

• order指定排序

desc降序、 asc升序。默认为asc

Db::name('user')->order('id','desc')->select();

• 多字段排序

先按照第一个排序方式排，再按后续的排序方式依次进行

Db::name('user')
->order(['create_time'=>'asc','age'=>'desc'])
->select();

• 分组统计

group把数据，根据字段分组统计出来。 如性别分组：男的总数、女的总数。返回俩个字段的统计

Db::name('user')
->field('gender',sum(price))
->group('gender')
->select();

• 多字段分组统计

group把数据，根据多个字段分组统计出来。 如发现指定字段不一样值的数据，会重新划分成另一个组里面

Db::name('user')
->field('gender',sum(price))
->group('gender,age')
->select();

• having筛选

主要是用于group分组之后再进行筛选的

$result = Db::name('user')
->field('gender,sum(price)')
->group('gender')
->having('SUM(price)>600')
->select();