技术笔记：mysqlidb类的使用方法汇总

mysqlidb类的使用方法汇总

========================================================

一、数据库链接

1、使用默认设置的 utf8 字符集进行简单初始化

$db = new MysqliDb ( ' host ' , ' username ' , ' password ' , ' databaseName ' );

2、高级初始化

$db = new MysqliDb (Array (

'host' => 'host',

'username' => 'username',

'password' => 'password',

'db'=> 'databaseName',

'port' => 3306,

'prefix' => 'my_',

'charset' => 'utf8'));

3、可以重用已经连接的 mysqli 对象

$mysqli = new mysqli ('host', 'username', 'password', 'databaseName');

$db = new MysqliDb ($mysqli);

4、如果您需要从另一个类或函数中获取已经创建的 mysqliDb 对象，请使用

function init () {

// db staying private here

$db = new MysqliDb ('host', 'username', 'password', 'databaseName');

}

...

function myfunc () {

// obtain db object created in init ()

$db = MysqliDb::getInstance();

...

}

=======================================================

二、数据插入

1、例子

$data = Array ("login" => "admin",

"firstName" => "John",

"lastName" => 'Doe');//写入数据组成数组

$id = $db->insert ('users', $data);//写入，并获得ID

if($id)

echo 'user was created. Id=' . $id;

2、调用函数写入

$data = Array (

'login' => 'admin',

'active' => true,

'firstName' => 'John',

'lastName' => 'Doe',

'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),//调用哈希函数加密

// password = SHA1('secretpassword+salt')

'createdAt' => $db->now(),//

// createdAt = NOW()

'expires' => $db->now('+1Y')

// expires = NOW() + interval 1 year

// Supported intervals 【s】econd, 【m】inute, 【h】hour, 【d】day, 【M】onth, 【Y】ear

);//写入数据组成数组

$id = $db->insert ('users', $data);//写入，并获得ID

if ($id)

echo 'user was created. Id=' . $id;

else

echo 'insert failed: ' . $db->getLastError();

3、插入重复键更新

$data = Array ("login" => "admin",

"firstName" => "John",

"lastName" => 'Doe',

"createdAt" => $db->now(),

"updatedAt" => $db->now(),

);//写入数据组成数组

$updateColumns = Array ("updatedAt");

$lastInsertId = "id";

$db->onDuplicate($updateColumns, $lastInsertId);

$id = $db->insert ('users', $data);

4、一次插入多个数据集

$data = Array(

Array ("login" => "admin",

"firstName" => "John",

"lastName" => 'Doe'

),

Array ("login" => "other",

"firstName" => "Another",

"lastName" => 'User',

"password" => "very_cool_hash"

)

);

$ids = $db->insertMulti('users', $data);

if(!$ids) {

echo 'insert failed: ' . $db->getLastError();

} else {

echo 'new users inserted with following id\'s: ' . implode(', ', $ids);

}

5、如果所有数据集只有相同的键，则可以简化

$data = Array(

Array ("admin", "John", "Doe"),

Array ("other", "Another", "User")

);//数值

$keys = Array("login", "firstName", "lastName");//键

$ids = $db->insertMulti('users', $data, $keys);

if(!$ids) {

echo 'insert failed: ' . $db->getLastError();

} else {

echo 'new users inserted with following id\'s: ' . implode(', ', $ids);

}

===================================================================

三、数据更新

1、常规更新

$data = Array (

'firstName' => 'Bobby',

'lastName' => 'Tables',

'editCount' => $db->inc(2),

// editCount = editCount + 2;

'active' => $db->not()

// active = !active;

);

$db->where ('id', 1);//更新条件

if ($db->update ('users', $data))

echo $db->count . ' records were updated';

else

echo 'update failed: ' . $db->getLastError();

// Gives:UPDATE users SET firstName='bobby' and lastName='' and ... where id=1;

2、也可以使用limit

$db->update ('users', $data, 10);

// Gives: UPDATE users SET ... LIMIT 10

===================================================

四、数据查询

1、

$users = $db->get('users'); //整表读取

$users = $db->get('users', 10); //整表读取10条数据

// Gives: select from users limit 10;

2、使用自定义列集进行选择。也可以使用函数

$cols = Array ("id", "name", "email");//读取的字段，组成数组

$users = $db->get ("users", null, $cols);//读取

if ($db->count > 0)

foreach ($users as $user) { //循环输出

print_r ($user);

}

// Gives: select id,name,email from users;

3、只读取一行数据

$db->where ("id", 1);//读取条件

$user = $db->getOne ("users");

echo $user【'id'】;

// Gives: select from users where id=1 limit 0,1;

$stats = $db->getOne ("users", "sum(id), count() as cnt");

echo "total ".$stats【'cnt'】. "users found";

// Gives: select sum(id), count() as cnt from users limit 0,1;

4、读取一列值或函数结果

$count = $db->getValue ("users", "count()");

echo "{$count} users found";

// Gives: select count() from users limit 0,1;

5、多行中选择一列值或函数结果

$logins = $db -> getValue( " users " , " login " , null );

//从用户中选择登录

$logins = $db -> getValue ( " users " , " login " , 5 );

//从用户限制中选择登录 5

foreach ( $logins as $login )

echo $login ;

// Gives: select login from users limit 0,5;

6、MysqliDb 可以以 3 种不同的格式返回结果：数组数组、对象数组和 Json 字符串。

要选择返回类型，请使用 ArrayBuilder()、ObjectBuilder() 和 JsonBuilder() 方法。

请注意 ArrayBuilder() 是默认返回类型

// 数组类型

$= $db->getOne("users");

echo $u【'login'】;

// 对象数组

$u = $db->ObjectBuilder()->getOne("users");

echo $u->login;

// Json数据

$json = $db->JsonBuilder()->getOne("users");

7、运行原始 SQL 查询

$users = $db->rawQuery('SELECT from users where id >= ?', Array (10));

foreach ($users as $user) {

print_r ($user);

}

8、运行一条原始 SQL 查询

$user = $db->rawQueryOne ('select from users where id=?', Array(10));

echo $user【'login'】;

// Object return type

$user = $db->ObjectBuilder()->rawQueryOne ('select from users where id=?', Array(10));

echo $user->login;

9、运行一条原始 SQL 查询值

$password = $db->rawQueryValue ('select password from users where //代码效果参考：http://www.lyjsj.net.cn/wz/art_23334.html

id=? limit 1', Array(10));

echo "Password is {$password}";

NOTE: for a rawQueryValue() to return string instead of an array 'limit 1' should be added to the end of the query.

10、更高级的例子

$params = Array(1, 'admin');

$users = $db->rawQuery("SELECT id, firstName, lastName FROM users WHERE id = ? AND login = ?", $params);

print_r($users); // //包含返回行的数组

// will handle any SQL query

$params = Array(10, 1, 10//代码效果参考：http://www.lyjsj.net.cn/wz/art_23332.html

, 11, 2, 10);

$q = "(

SELECT a FROM t1

WHERE a = ? AND B = ?

ORDER BY a LIMIT ?

) UNION (

SELECT a FROM t2

WHERE a = ? AND B = ?

ORDER BY a LIMIT ?

)";

$resutls = $db->rawQuery ($q, $params);

print_r ($results); //包含返回行的数组

=============================================

五、条件查询

where(), orWhere(), having() and orHaving()

1、常规条件

$db->where ('id', 1);

$db->where ('login', 'admin');

$results = $db->get ('users');

// Gives: SELECT FROM users WHERE id=1 AND login='admin';

$db->where ('id', 1);

$db->having ('login', 'admin');

$results = $db->get ('users');

// Gives: SELECT FROM users WHERE id=1 HAVING login='admin';

2、常规 == 运算符

// 错误

$db->where ('lastLogin', 'createdAt');

// 正确

$db->where //代码效果参考：http://www.lyjsj.net.cn/wz/art_23330.html

('lastLogin = createdAt');

$results = $db->get ('users');

// Gives: SELECT FROM users WHERE lastLogin = createdAt;

$db->where ('id', 50, ">=");

// or $db->where ('id', Array ('>=' => 50));

$results = $db->get ('users');

// Gives: SELECT FROM users WHERE id >= 50;

3、BETWEEN / NOT BETWEEN

$db->where('id', Array (4, 20), 'BETWEEN');

// or $db->where ('id', Array ('BETWEEN' => Array(4, 20)));

$results = $db->get('users');

// Gives: SELECT FROM users WHERE id BETWEEN 4 AND 20

4、IN / NOT IN:

$db->where('id', Array(1, 5, 27, -1, 'd'), 'IN');

// or $db->where('id', Array( 'IN' => Array(1, 5, 27, -1, 'd') ) );

$results = $db->get('users');

// Gives: SELECT FROM users WHERE id IN (1, 5, 27, -1, 'd');

5、OR CASE

$db->where ('firstName', 'John');

$db->orWhere ('firstName', 'Peter');

$results = $db->get ('users');

// Gives: SELECT FROM users WHERE firstName='John' OR firstName='peter'

6、NULL comparison:

$db->where ("lastName", NULL, 'IS NOT');

$results = $db->get("users");

// Gives: SELECT FROM users where lastName IS NOT NULL

7、使用raw where

$db->where ("id != companyId");

$db->where ("DATE(createdAt) = DATE(lastLogin)");

$results = $db->get("users");

$db->where ("(id = ? or id = ?)", Array(6,2));

$db->where ("login","mike")

$res = $db->get ("users");

// Gives: SELECT FROM users WHERE (id = 6 or id = 2) and login='mike';

8、查找匹配的总行数。简单的分页示例

$offset = 10;

$count = 15;

$users = $db->withTotalCount()->get('users', Array ($offset, $count));

echo "Showing {$count} from {$db->totalCount}";

9、您可以使用方法链接多次调用 where 而无需过度引用您的对象

$results = $db

->where('id', 1)

->where('login', 'admin')

->get('users');

=============================================

六、删除

$db->where('id', 1);

if($db->delete('users')) echo 'successfully deleted';

// Gives: delete users where id=1;

==============================================

七、排序

$db->orderBy("id","asc");

$db->orderBy("login","Desc");

$db->orderBy("RAND ()");

$results = $db->get('users');

// Gives: SELECT FROM users ORDER BY id ASC,login DESC, RAND ();

$db->orderBy('userGroup', 'ASC', array('superuser', 'admin', 'users'));

$db->get('users');

// Gives: SELECT FROM users ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC;

=========================================================

八、JOIN方法

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");

$db->where("u.id", 6);

$products = $db->get ("products p", null, "u.name, p.productName");

print_r ($products);

// Gives: S

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");

$db->joinWhere("users u", "u.tenantID", 5);

$products = $db->get ("products p", null, "u.name, p.productName");

print_r ($products);

// Gives: SELECT u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");

$db->joinOrWhere("users u", "u.tenantID", 5);

$products = $db->get ("products p", null, "u.name, p.productName");

print_r ($products);

// Gives: SELECT u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID OR u.tenantID = 5)

//复制属性

$db->where ("agentId", 10);//where条件

$db->where ("active", true);//where条件

$customers = $db->copy ();//复制属性,方便后面运行两条指令

//指令1

$res = $customers->get ("customers", Array (10, 10));

// SELECT FROM customers where agentId = 10 and active = 1 limit 10, 10

//指令2

$cnt = $db->getValue ("customers", "count(id)");

echo "total records found: " . $cnt;

// SELECT count(id) FROM users where agentId = 10 and active = 1

===================================================

九、子查询

$ids = $db->subQuery ();//子查询

$ids->where ("qty", 2, ">");//条件

$ids->get ("products", null, "userId");

$db->where ("id", $ids, 'in');

$res = $db->get ("users");

// Gives SELECT FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2)

//

$userIdQ = $db->subQuery ();

$userIdQ->where ("id", 6);

$userIdQ->getOne ("users", "name"),

$data = Array (

"productName" => "test product",

"userId" => $userIdQ,

"lastUpdated" => $db->now()

);

$id = $db->insert ("products", $data);

// Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());

*/