DB操作：數(shù)據(jù)庫(kù)基本操作速查

一花一世界，
一沙一天國(guó)，
君掌盛無邊，
剎那含永劫。

-- 《天真的預(yù)兆》

1.20.1 前提

為了讓大家更為明確數(shù)據(jù)庫(kù)NotORM的使用，我們假設(shè)有以下數(shù)據(jù)庫(kù)表：

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

并且，假設(shè)我們已獲得了tbl_user表對(duì)應(yīng)的notorm實(shí)例$user，此NotORM表實(shí)例可從兩種方式獲得：

• 1、使用原生態(tài)的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表實(shí)例
    }
}

寫在前面的話

NotORM的實(shí)例是有內(nèi)部狀態(tài)的，因?yàn)樵陂_發(fā)過程中，需要特別注意何時(shí)需要保留狀態(tài)（使用同一個(gè)實(shí)例）、何時(shí)不需要保留狀態(tài)。即：

保留狀態(tài)的寫法：

$user = $notorm->user;  //獲取一個(gè)新的實(shí)例
$user->where('age > ?', 18);
$user->where('name LIKE ?', '%dog%');  //相當(dāng)于age > 18 AND name LIKE '%dog%'

不保留狀態(tài)的寫法：

$user = $notorm->user;  //獲取一個(gè)新的實(shí)例
$user->where('age > ?', 18);

$user = $notorm->user;  //重新獲取新的實(shí)例
$user->where('name LIKE ?', '%dog%');  //此時(shí)只有 name LIKE '%dog%'

關(guān)于這兩者的使用場(chǎng)景，下面在進(jìn)行說明時(shí)會(huì)特別提及。
下面，就讓我們結(jié)合實(shí)例來嘗試一下數(shù)據(jù)庫(kù)的操作吧！

調(diào)試模式

(1)打印SQL語句

在請(qǐng)求接口時(shí)，當(dāng)需要查看當(dāng)前接口所執(zhí)行的SQL語句時(shí)，可以添加參數(shù)&__sql__=1。如請(qǐng)求：

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

會(huì)返回類似這樣：

[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)顯示數(shù)據(jù)庫(kù)連接錯(cuò)誤的詳細(xì)信息

普通情況下，數(shù)據(jù)庫(kù)連接失敗時(shí)會(huì)這樣提示：

{
    "ret": 500,
    "data": [],
    "msg": "服務(wù)器運(yùn)行錯(cuò)誤: 數(shù)據(jù)庫(kù)db_demo連接失敗"
}

考慮到生產(chǎn)環(huán)境不方便爆露服務(wù)器的相關(guān)信息，故這樣簡(jiǎn)化提示。當(dāng)在開發(fā)過程中，需要定位數(shù)據(jù)庫(kù)連接失敗的原因時(shí)，可使用debug調(diào)試模式。
開啟調(diào)試模式的方法有兩種：

• 1、在請(qǐng)求接口時(shí)添加 &__debug__=1，開啟本次調(diào)試
• 2、將配置文件 ./Config/sys.php中的debug設(shè)置為true，開啟全局調(diào)試

開啟后，當(dāng)再次失敗時(shí)，會(huì)看到類似這樣的提示：

{
    "ret": 500,
    "data": [],
    "msg": "服務(wù)器運(yùn)行錯(cuò)誤: 數(shù)據(jù)庫(kù)db_demo連接失敗，異常碼：1045，錯(cuò)誤原因：SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: NO)"
}

然后，便可根據(jù)具體的錯(cuò)誤提示進(jìn)行排查解決。

默認(rèn)表名與指定表名

請(qǐng)注意：PhalApi v1.3.4 版本以上有效。

當(dāng)繼承PhalApi_Model_NotORM時(shí)，如編寫Model_User類：

<?php
class Model_User extends PhalApi_Model_NotORM {
}

則默認(rèn)的表名為：user。默認(rèn)表名的自動(dòng)匹配規(guī)則是：取Model_后面部分的字符全部轉(zhuǎn)小寫。轉(zhuǎn)化后框架會(huì)另外加上表前綴。

在以下場(chǎng)景下，需要重寫此方法以指定表名：

• 自動(dòng)匹配的表名與實(shí)際表名不符
• 存在分表
• Model類名不含有Model_

如，當(dāng)Model_User類對(duì)應(yīng)的表名為：my_user表時(shí)，可使用getTableName($id)重新指定表名：

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

1.20.2 基本操作

(1)SELECT

單個(gè)字段：

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

多個(gè)字段獲?。?/h4>

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

字段別名獲?。?/h4>

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

全部字段（缺?。┇@?。?/h4>

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

(2)WHERE

單個(gè)字段查詢：

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

多個(gè)字段查詢：

// 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

單個(gè)字段排序：

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

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

多個(gè)字段排序：

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

(4)LIMIT

按數(shù)量限制：

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

按數(shù)量和偏移量限制（請(qǐng)注意：先偏移量、再數(shù)量）：

// 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）

循環(huán)獲取每一行（多個(gè)字段）：

// 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"
}

循環(huán)獲取每一行（單個(gè)字段）：

// 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"

循環(huán)獲取每一行（錯(cuò)誤的用法，注意！會(huì)死循環(huán)）：

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

只獲取第一行（多個(gè)字段）：

// 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"
}

只獲取第一行（單個(gè)字段）：

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

獲取鍵值對(duì)（多個(gè)字段）：

// 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"
  }
}

獲取鍵值對(duì)（單個(gè)字段）：

// SELECT id, name FROM tbl_user LIMIT 2
var_dump($user->limit(2)->fetchPairs('id', 'name')); //通過第二個(gè)參數(shù)，指定VALUE的列

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

獲取全部的行：

// SELECT * FROM tbl_user
var_dump($user->fetchAll());  //全部表數(shù)據(jù)輸出，輸出結(jié)果略，相當(dāng)于$user->fetchRows()

復(fù)雜查詢下獲取全部的行（1.3.1及以前版本默認(rèn)下以主鍵為下標(biāo)，1.3.1以后的版本則默認(rèn)采用數(shù)組方式）：

// 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';  //使用問號(hào)表示變量
$params = array(18);
$rs = $user->queryRows($sql, $params); //使用queryRows()別名
var_dump($rs);

請(qǐng)注意：使用上面這種方式進(jìn)行查詢，需要手動(dòng)填寫完整的表名字，并且需要通過某個(gè)表的實(shí)例來運(yùn)行。

取最小值：

// 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"

計(jì)算總和：

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

// 輸出
string(3) "139"

1.20.4 CURD之插入類（Create）

插入數(shù)據(jù)

// 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(); //必須是同一個(gè)實(shí)例，方能獲取到新插入的行ID，且表必須設(shè)置了自增
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) //成功插入的條數(shù)

插入/更新：

// 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); //輸出影響的行數(shù)

1.20.5 CURD之更新類（Update）

更新數(shù)據(jù)：

// 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) //正常影響的行數(shù)
int(0) //無更新，或者數(shù)據(jù)沒變化
boolean(false) //更新異常、失敗

更新數(shù)據(jù)（+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); //輸出影響的行數(shù)

1.20.6 CURD之刪除類（Delete）

按條件刪除，返回影響的行數(shù)：

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

注意： 如果是全表刪除，框架將會(huì)禁止，并拋出異常。如：

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

參考

更多請(qǐng)參考 NotORM官網(wǎng)接口說明