codecamp

DB操作:数据库基本操作速查

一花一世界,
一沙一天国,
君掌盛无边,
刹那含永劫。

-- 《天真的预兆》

1.20.1 前提

为了让大家更为明确数据库NotORM的使用,我们假设有以下数据库表:

-- ----------------------------
-- Table structure for `tbl_user`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_user`;
CREATE TABLE `tbl_user` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `note` varchar(45) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_user
-- ----------------------------
INSERT INTO `tbl_user` VALUES ('1', 'dogstar', '18', 'oschina', '2015-12-01 09:42:31');
INSERT INTO `tbl_user` VALUES ('2', 'Tom', '21', 'USA', '2015-12-08 09:42:38');
INSERT INTO `tbl_user` VALUES ('3', 'King', '100', 'game', '2015-12-23 09:42:42');

并且,假设我们已获得了tbl_user表对应的notorm实例$user,此NotORM表实例可从两种方式获得:

  • 1、使用原生态的notorm,即:$user = DI()->notorm->user
  • 2、使用PhalApi_Model_NotORM基类的方式,即:$user = $this->getORM(),如:
<?php
class Model_User extends PhalApi_Model_NotORM {
    protected function getTableName($id) {
        return 'user';
    }

    public function doSth() {
        $user = $this->getORM(); //获取NotORM表实例
    }
}

写在前面的话

NotORM的实例是有内部状态的,因为在开发过程中,需要特别注意何时需要保留状态(使用同一个实例)、何时不需要保留状态。即:

保留状态的写法:

$user = $notorm->user;  //获取一个新的实例
$user->where('age > ?', 18);
$user->where('name LIKE ?', '%dog%');  //相当于age > 18 AND name LIKE '%dog%'

不保留状态的写法:

$user = $notorm->user;  //获取一个新的实例
$user->where('age > ?', 18);

$user = $notorm->user;  //重新获取新的实例
$user->where('name LIKE ?', '%dog%');  //此时只有 name LIKE '%dog%'

关于这两者的使用场景,下面在进行说明时会特别提及。
下面,就让我们结合实例来尝试一下数据库的操作吧!

调试模式

(1)打印SQL语句

在请求接口时,当需要查看当前接口所执行的SQL语句时,可以添加参数&__sql__=1。如请求:

http://demo.phalapi.net/?service=User.getBaseInfo&user_id=1&__sql__=1

会返回类似这样:

[1 - 0.00035s]SELECT * FROM tbl_user WHERE (id = ?); -- 1<br>
{"ret":200,"data":{"code":0,"msg":"","info":{"id":"1","name":"dogstar","note":"oschina"}},"msg":""}

(2)显示数据库连接错误的详细信息

普通情况下,数据库连接失败时会这样提示:

{
    "ret": 500,
    "data": [],
    "msg": "服务器运行错误: 数据库db_demo连接失败"
}

考虑到生产环境不方便爆露服务器的相关信息,故这样简化提示。当在开发过程中,需要定位数据库连接失败的原因时,可使用debug调试模式。
开启调试模式的方法有两种:

  • 1、在请求接口时添加 &__debug__=1,开启本次调试
  • 2、将配置文件 ./Config/sys.php中的debug设置为true,开启全局调试

开启后,当再次失败时,会看到类似这样的提示:

{
    "ret": 500,
    "data": [],
    "msg": "服务器运行错误: 数据库db_demo连接失败,异常码:1045,错误原因:SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: NO)"
}

然后,便可根据具体的错误提示进行排查解决。

默认表名与指定表名

请注意:PhalApi v1.3.4 版本以上有效。

当继承PhalApi_Model_NotORM时,如编写Model_User类:

<?php
class Model_User extends PhalApi_Model_NotORM {
}

则默认的表名为:user。默认表名的自动匹配规则是:取Model_后面部分的字符全部转小写。转化后框架会另外加上表前缀。

在以下场景下,需要重写此方法以指定表名:

  • 自动匹配的表名与实际表名不符
  • 存在分表
  • Model类名不含有Model_

如,当Model_User类对应的表名为:my_user表时,可使用getTableName($id)重新指定表名:

<?php
class Model_User extends PhalApi_Model_NotORM {
    protected function getTableName($table) {
        return 'my_user'; 
    }
}

1.20.2 基本操作

(1)SELECT

单个字段:

// SELECT id FROM `tbl_user`
$user->select('id') 

多个字段获取:

// SELECT id, name, age FROM `tbl_user`
$user->select('id, name, age') 

字段别名获取:

// SELECT id, name, MAX(age) AS max_age FROM `tbl_user`
$user->select('id, name, MAX(age) AS max_age') 

全部字段(缺省)获取:

// SELECT * FROM `tbl_user`
$user->select('*') 

(2)WHERE

单个字段查询:

// WHERE id = 1
$user->where('id', 1)
$user->where('id = ?', 1)
$user->where(array('id', 1))

多个字段查询:

// WHERE id > 1 AND age > 18
$user->where('id > ?', 1)->where('age > ?', 18)
$user->and('id > ?', 1)->and('age > ?', 18)
$user->where('id > ? AND age > ?', 1, 18)
$user->where(array('id > ?' => 1, 'age > ?' => 10))

// WHERE name = 'dogstar' AND age = 18
$user->where(array('name' => 'dogstar', 'age' => 18))

// WHERE name = 'dogstar' OR age = 18
$user->or('name', 'dogstar')->or('age', 18)

IN查询:

// WHERE id IN (1, 2, 3)
$user->where('id', array(1, 2, 3))

// WHERE id NOT IN (1, 2, 3)
$user->where('NOT id', array(1, 2, 3))

// WHERE (id, age) IN ((1, 18), (2, 20))
$user->where('(id, age)', array(array(1, 18), array(2, 20)))

模糊匹配查询:

// WHERE name LIKE '%dog%'
$user->where('name LIKE ?', '%dog%')

NULL判断查询:

// WHERE (name IS NULL)
$user->where('name', null)

(3)ORDER BY

单个字段排序:

// ORDER BY age
$user->order('age')

// ORDER BY age DESC
$user->order('age DESC')

多个字段排序:

// ORDER BY id, age DESC
$user->order('id')->order('age DESC')
$user->order('id, age DESC')

(4)LIMIT

按数量限制:

// LIMIT 10
$user->limit(10)

按数量和偏移量限制(请注意:先偏移量、再数量):

// LIMIT 2,10
$user->limit(2, 10)

(5)GROUP BY和HAVING

不带HAVING:

// GROUP BY note
$user->group('note')

带HAVING:

// GROUP BY note HAVING age > 10
$user->group('note', 'age > 10')

1.20.3 CURD之查询类(Retrieve)

操作说明示例备注是否PhalApi新增
fetch()循环获取每一行while($row = $user->fetch()) { //... }
fetchOne()只获取第一行$row = $user->where('id', 1)->fetchOne();等效于fetchRow()
fetchRow()只获取第一行$row = $user->where('id', 1)->fetchRow();等效于fetchOne()
fetchPairs()获取键值对$row = $user->fetchPairs('id', 'name');第二个参数为空时,可取多个值,并且多条纪录
fetchAll()获取全部的行$rows = $user->where('id', array(1, 2, 3))->fetchAll();等效于fetchRows()
fetchRows()获取全部的行$rows = $user->where('id', array(1, 2, 3))->fetchRows();等效于fetchAll()
queryAll()复杂查询下获取全部的行,默认下以主键为下标$rows = $user->queryAll($sql, $parmas);等效于queryRows()
queryRows()复杂查询下获取全部的行,默认下以主键为下标$rows = $user->queryRows($sql, $parmas);等效于queryAll()
count()查询总数$total = $user->count('id');第一参数可省略
min()取最小值$minId = $user->min('id');
max()取最大值$maxId = $user->max('id');
sum()计算总和$sum = $user->sum('age');

循环获取每一行(多个字段):

// SELECT id, name FROM tbl_user WHERE (age > 18);
$user = $notorm->user->select('id, name')->where('age > 18');
while($row = $user->fetch()) {
     var_dump($row);
}

// 输出
array(2) {
  ["id"]=>
  string(1) "2"
  ["name"]=>
  string(3) "Tom"
}
array(2) {
  ["id"]=>
  string(1) "3"
  ["name"]=>
  string(4) "King"
}

循环获取每一行(单个字段):

// SELECT id, name FROM tbl_user WHERE (age > 18);
$user = $notorm->user->select('id, name')->where('age > 18');
while($row = $user->fetch('name')) { // 指定获取name这列,但此字段须在select里
     var_dump($row);
}

// 输出
string(3) "Tom"
string(4) "King"

循环获取每一行(错误的用法,注意!会死循环):

while($row = $notorm->user->select('id, name')->where('age > 18')->fetch('name')) {
     var_dump($row);
}

只获取第一行(多个字段):

// SELECT id, name FROM tbl_user WHERE (age > 18) LIMIT 1;
$rs = $user->select('id, name')->where('age > 18')->fetchOne();  //等同fetchRow()
var_dump($rs);

// 输出
array(2) {
  ["id"]=>
  string(1) "2"
  ["name"]=>
  string(3) "Tom"
}

只获取第一行(单个字段):

var_dump($user->fetchOne('name'));  // 输出 string(3) "Tom"
var_dump($user->fetchRow('name'));  // 等同输出 string(3) "Tom"

获取键值对(多个字段):

// SELECT id, name, age FROM tbl_user LIMIT 2;
$rs = $user->select('name, age')->limit(2)->fetchPairs('id'); //指定以ID为KEY
var_dump($rs);

// 输出
array(2) {
  [1]=>
  array(3) {
    ["id"]=>
    string(1) "1"
    ["name"]=>
    string(7) "dogstar"
    ["age"]=>
    string(2) "18"
  }
  [2]=>
  array(3) {
    ["id"]=>
    string(1) "2"
    ["name"]=>
    string(3) "Tom"
    ["age"]=>
    string(2) "21"
  }
}

获取键值对(单个字段):

// SELECT id, name FROM tbl_user LIMIT 2
var_dump($user->limit(2)->fetchPairs('id', 'name')); //通过第二个参数,指定VALUE的列

// 输出
array(2) {
  [1]=>
  string(7) "dogstar"
  [2]=>
  string(3) "Tom"
}

获取全部的行:

// SELECT * FROM tbl_user
var_dump($user->fetchAll());  //全部表数据输出,输出结果略,相当于$user->fetchRows()

复杂查询下获取全部的行(1.3.1及以前版本默认下以主键为下标,1.3.1以后的版本则默认采用数组方式):

// SELECT name FROM tbl_user WHERE age > :age LIMIT 1
$sql = 'SELECT name FROM tbl_user WHERE age > :age LIMIT 1';
$params = array(':age' => 18);
$rs = $user->queryAll($sql, $params);
var_dump($rs);

// 输出
array(1) {
  [0]=>
  array(1) {
    ["name"]=>
    string(3) "Tom"
  }
}

// 也可以这样。。。
$sql = 'SELECT name FROM tbl_user WHERE age > ? LIMIT 1';  //使用问号表示变量
$params = array(18);
$rs = $user->queryRows($sql, $params); //使用queryRows()别名
var_dump($rs);

请注意:使用上面这种方式进行查询,需要手动填写完整的表名字,并且需要通过某个表的实例来运行。

取最小值:

// SELECT MIN(age) FROM tbl_user
var_dump($user->min('age'));

// 输出
string(2) "18"

取最大值:

// SELECT MAX(age) FROM tbl_user
var_dump($user->max('age'));

// 输出
string(3) "100"

计算总和:

// SELECT SUM(age) FROM tbl_user
var_dump($user->sum('age'));

// 输出
string(3) "139"

1.20.4 CURD之插入类(Create)

操作说明示例备注是否PhalApi新增
insert()插入数据$user->insert($data);原生态操作需要再调用insert_id()获取插入的ID
insert_multi()批量插入$user->insert_multi($rows);可批量插入
insert_update()插入/更新接口签名:insert_update(array $unique, array $insert, array $update = array()不存时插入,存在时更新

插入数据

// INSERT INTO tbl_user (id, name, age, note) VALUES (4, 'PhalApi', 1, 'framework')
$data = array('id' => 4, 'name' => 'PhalApi', 'age' => 1, 'note' => 'framework');
$user->insert($data);
$id = $user->insert_id(); //必须是同一个实例,方能获取到新插入的行ID,且表必须设置了自增
var_dump($id); //新增的ID

//使用Model的写法
$model = new Model_User();
var_dump($model->insert($data)); //输出新增的ID

批量插入:

// INSERT INTO tbl_user (name, age, note) VALUES ('A君', 12, 'AA'), ('B君', 14, 'BB'), ('C君', 16, 'CC')
$rows = array(
    array('name' => 'A君', 'age' => 12, 'note' => 'AA'),
    array('name' => 'B君', 'age' => 14, 'note' => 'BB'),
    array('name' => 'C君', 'age' => 16, 'note' => 'CC'),
);
$rs = $user->insert_multi($rows);
var_dump($rs);

// 输出
int(3) //成功插入的条数

插入/更新:

// INSERT INTO tbl_user (id, name, age, note) VALUES (8, 'PhalApi', 1, 'framework') ON DUPLICATE KEY UPDATE age = 2
$unique = array('id' => 8);
$insert = array('id' => 8, 'name' => 'PhalApi', 'age' => 1, 'note' => 'framework');
$update = array('age' => 2);
$rs = $user->insert_update($unique, $insert, $update);
var_dump($rs); //输出影响的行数

1.20.5 CURD之更新类(Update)

操作说明示例备注是否PhalApi新增
update()更新数据$user->where('id', 1)->update($data);更新异常时返回fals,数据无变化时返回0,成功更新返回1

更新数据:

// UPDATE tbl_user SET age = 2 WHERE (name = 'PhalApi');
$data = array('age' => 2);
$rs = $user->where('name', 'PhalApi')->update($data);
var_dump($rs);

// 输出
int(1) //正常影响的行数
int(0) //无更新,或者数据没变化
boolean(false) //更新异常、失败

更新数据(+1):

// UPDATE tbl_user SET age = age + 1 WHERE (name = 'PhalApi')
$rs = $user->where('name', 'PhalApi')->update(array('age' => new NotORM_Literal("age + 1")));
var_dump($rs); //输出影响的行数

1.20.6 CURD之删除类(Delete)

操作说明示例备注是否PhalApi新增
delete()删除$user->where('id', 1)->delete();禁止无where条件的删除操作

按条件删除,返回影响的行数:

// DELETE FROM tbl_user WHERE (id = 404);
$user->where('id', 404)->delete();

注意: 如果是全表删除,框架将会禁止,并抛出异常。如:

// Exception: sorry, you can not delete the whole table
$user->delete();

参考

更多请参考 NotORM官网接口说明

DI服务速查:各资源服务一览表
类的自动加载:遵循PEAR包的命名规范
温馨提示
下载编程狮App,免费阅读超1000+编程语言教程
取消
确定
目录

八、视频教程

关闭

MIP.setData({ 'pageTheme' : getCookie('pageTheme') || {'day':true, 'night':false}, 'pageFontSize' : getCookie('pageFontSize') || 20 }); MIP.watch('pageTheme', function(newValue){ setCookie('pageTheme', JSON.stringify(newValue)) }); MIP.watch('pageFontSize', function(newValue){ setCookie('pageFontSize', newValue) }); function setCookie(name, value){ var days = 1; var exp = new Date(); exp.setTime(exp.getTime() + days*24*60*60*1000); document.cookie = name + '=' + value + ';expires=' + exp.toUTCString(); } function getCookie(name){ var reg = new RegExp('(^| )' + name + '=([^;]*)(;|$)'); return document.cookie.match(reg) ? JSON.parse(document.cookie.match(reg)[2]) : null; }