[1.20]-DB操作：数据库基本操作速查 | PhalApi(π框架) - PHP轻量级开源接口框架

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

-- 《天真的预兆》

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($id) {
        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)

// WHERE ((name = ? OR id = ?)) AND (note = ?) -- 'dogstar', '1', 'xxx'
#实现方式1：使用and拼接
$user->where('(name = ? OR id = ?)', 'dogstar', '1')->and('note = ?', 'xxx')
#实现方式2：只使用where，不用数组传参
$user->where('(name = ? OR id = ?) AND note = ?', 'dogstar', '1', 'xxx')
#实现方式3：只使用where，用下标为顺序数字的数组传参
$user->where('(name = ? OR id = ?) AND note = ?', array('dogstar', '1', 'xxx'))
#实现方式4：只使用where，用下标为标识符的数组传参
$user->where('(name = :name OR id = :id) AND note = :note', array(':name' => 'dogstar', ':id' => '1', ':note' => 'xxx'))

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%')

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

NULL判断查询：

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

对于非空的判断，则可以：

// WHERE (name IS NOT ?) LIMIT 1; -- NULL
$user->where('name IS NOT ?', 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);	更新异常时返回false，数据无变化时返回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官网接口说明

上一章 文档首页 下一章