#1 jake

在SpeedPHP框架中，通过spModel直接使用SQL语句的函数有findSql和runSql，以下我们分别介绍它们的一些实用例子：
一般来说，运行findSql和runSql的对象不是很重要，当然我们推荐使用和查询相关的数据库模型类作为执行成员函数的对象。
以下只是举例说明函数的使用方法，SQL语句来自网络，仅为讲解之用。
findSql

子查询

$obj->findSql("select tbl_student.* from tbl_student, (select student_id from tbl_score where examdate = '2010-10-12') sc where sc.student_id = tbl_student.student_id group by tbl_student.student_id");

UNION联合查询

$obj->findSql("(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10)");

JOIN连接查询

$obj->findSql("SELECT t1.id,t2.id,t3.id FROM t1,(  t2 LEFT JOIN t3 ON (t3.id=t1.id) ) WHERE t1.id=t2.id");



runSql

增删改create，update，delete

$obj->runSql("INSERT INTO table_name (col1, col2) VALUES(15, col1*2)");

$obj->runSql("UPDATE table_name SET col1=100, col2 = col1*2 WHERE col3 > 100");

$obj->runSql("DELETE table_name where col1 > 30 AND col2 < 50");

建表，改变表

$obj->runSql("CREATE TABLE tbl_topic(tid int NOT NULL AUTO_INCREMENT,topic VARCHAR(200) NOT NULL,clicks BIGINT NOT NULL DEFAULT 0,PRIMARY KEY (tid)) DEFAULT CHARSET utf8");

$obj->runSql("ALTER TABLE t1 RENAME t2");

其他操作

$obj->runSql("REPLACE INTO mysql.user (Host,User,Password) VALUES('%','username',PASSWORD('password')) ");


2012-08-04 17:00:26