创建staff表
DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
`staff_id` int(4) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(30) NOT NULL COMMENT '姓名',
`sex` int(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别0男1女',
`age` tinyint(4) NOT NULL DEFAULT '25' COMMENT '年龄',
`salary` int(6) unsigned NOT NULL DEFAULT '3000' COMMENT '工资',
`delete_time` int(10) NOT NULL,
`enter_time` int(10) NOT NULL,
PRIMARY KEY (`staff_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;
<?php
namespace app\index\controller;
use think\Db;
class Query{
//读操作返回的都是二维数组 没有满足条件的记录 返回的是一个空数组
//写操作返回的事受影响的记录数量，如果没有返回0
public function find(){
//查询单条记录
$res =Db::table('staff')->where('staff_id',40)->find();
dump($res);
}
public function select(){
//查询满足条件的多条记录
$res = Db::table('staff')
// ->field(['name'=>'姓名','salary'=>'工资'])
->where('salary','>','3000')/**查询条件**/
->order('salary desc')/**排序**/
->select();
dump($res);
}
public function insert(){
$data = [
'name'=>'huhu',
'sex'=>0,
'age'=>49,
'salary'=>5300,
];
//$num=Db::table('staff')->insert($data);
$num=Db::table('staff')->data($data)->insert();
$id=Db::getLastInsID();
return $num ?'添加成功,id='.$id :'没有记录被添加';
//新增多条记录
$data= [
['name'=>'zhangfei','sex'=>0,'age'=>'48','salary'=>6000],
['name'=>'guanyu','sex'=>0,'age'=>'49','salary'=>5000],
['name'=>'liubei','sex'=>0,'age'=>'50','salary'=>7000]
];
$num=Db::table('staff')->data($data)->insertAll();
return $num;
}
public function update(){
//不允许无条件更新
$data=['salary'=>Db::raw('salary+1')];
$res= Db::table('staff')
->where('salary','>=','9000')
->data($data)
->update();
return $res;
$num=Db::table('staff')
->update(['sex'=>1,'staff_id'=>79]);//条件明确的话
}
public function delete(){
//不允许无条件删除
//删除一条
$num=Db::table('staff')
->delete(79);
//删除多条
$num=Db::table('staff')
->delete([1,2]);
//条件删除
$num = Db::table('staff')
->where('salary','>',1000)
->delete();
}
}
?>
