Ejemplo n.º 1
0
$unionSql = $testTable->where(['score' => ['>', 60]])->query();
//SELECT * FROM `db_test` WHERE (`score` > 60)
$sql = $test2Table->union($unionSql)->where(['user_id' => ['<', 100]])->query();
var_dump($sql);
//SELECT * FROM `db_test2` WHERE (`user_id` < 100) UNION (SELECT * FROM `db_test` WHERE (`score` > 60) )
//子查询
$sql = $test2Table->stable('select * from db_test', 'test')->where('test.user_id = 1')->query();
var_dump($sql);
//SELECT * FROM ( select * from db_test )as test WHERE test.id = 1
//多表查询
$sql = $test2Table->stable(['db_test as t', 'db_user as u'])->where('t.id = u.user_id')->query();
var_dump($sql);
//SELECT * FROM ( db_test as t,db_user as u ) WHERE t.id = u.user_id
//join连接查询
// alias 设置主表别名
//demo1 字符串参数
$testTable = \sower\Loader::table('test');
$sql = $testTable->alias('t')->join('join db_test2 as t2 on t.id = t2.id')->query();
var_dump($sql);
//SELECT * FROM test as t join db_test2 as t2 on t.id = t2.id
// demo2 数组式传参
// $testTable->prefix 获取表前缀
// $testTable->table  获取表名(包括表前缀)
// 可以使用数据库配置的表前缀:
$sql = $testTable->alias('t')->join(['left join', $testTable->prefix . 'test2 as t2', 'on t.id = t2.id'])->query();
var_dump($sql);
//SELECT * FROM test as t LEFT JOIN (test2 as t2) on t.id = t2.id
//demo3 多表连接
$sql = $testTable->alias('t')->join([['left join', $testTable->prefix . 'test2 as t2', 'on t.id = t2.id'], ['left join', $testTable->prefix . 'test3 as t3', 'on t.id = t3.id']])->query();
var_dump($sql);
//ELECT * FROM test as t LEFT JOIN (test2 as t2) on t.id = t2.id LEFT JOIN (test3 as t3) on t.id = t3.id
Ejemplo n.º 2
0
//DELETE FROM `db_test` WHERE (`uid` = 1)
/**
 * where函数使用数组
 * 格式为:
 * $where = [
 * 	A => [S1,S2,S3] | F
 * ];
 *
 * 上面A的值可以为S的数组、也可以为F的字符串
 *
 * 数组的KEY、数组的VALUE是一个数组,值分别表示为[S1、S2、S3]
 * A: 表示数据库的字段名称
 * S1: 表示关系符、支持的关系有:
 * '=','<>','!=','<','<=','>','>=','LIKE','NOT BETWEEN','BETWEEN','NOT IN','IN',
 * 'NOT LIKE','LIKE','REGEXP','NOT REGEXP',
 *  不支持的关系符将默认为 '='
 * S2: 表示为查询字段的值
 * S3: 连接符、支持: 'AND'和'OR'
 * F:  如果为字符串、直接表示为字段A的值、默认连接符为 '='
 * (最后一个是后面条件的连接符可写 可不写)
 */
/**
 * 复杂一点的删除条件
 * 删除name为jack并且group大于1或者type为1、add_time asc 排序的前10条数据
 * 其中limit函数 也可以这样写  [ limit(10) ]
 */
$where = ['name' => ['=', 'jack', 'and'], 'group' => ['>', 1, 'or'], 'type' => '1'];
$test2Table = \sower\Loader::table('db_test');
$delete_sql3 = $test2Table->delete($where);
var_dump($delete_sql3);
//DELETE FROM `db_test` WHERE (`name` = 'jack') AND (`group` > 1) OR (`type` = '1')
Ejemplo n.º 3
0
$dbconfig = ['hostname' => ['127.0.0.1', '127.0.0.1', '127.0.0.1', '127.0.0.1'], 'database' => ['xiang_user', 'xiang_user_slave', 'xiang_user_slave2', 'xiang_user_slave3'], 'username' => ['root'], 'password' => ['root'], 'hostport' => ['3306'], 'charset' => ['utf8'], 'is_disperse' => true, 'rw_separate' => true, 'master_num' => 2];
//数据库配置
//设置默认数据库
\sower\Loader::setdb($dbconfig);
//一些例子
////查询将返回查询的数组数据
////如果SQL语句内包含 LIMIT 0,1  查询返回的数据将转为一维数组
////插入将会返回插入数据的ID
////更新将会返回影响的行数
//查询	查询用户等于1的数据
//第一步构建SQL
$sql = \sower\Loader::table('xiang_test')->query();
var_dump($sql);
//第二步执行SQL
//$data = $sower->database($dbconfig); //切换数据库(可以是分布式数据库配置),如果不传入$dbconfig 则使用实例化sower时传入的数据库配置
//$sql : 要执行的SQL语句
//$dbconfig: 选择的数据库配置,用户切换数据库,如果不传则使用实例化的默认数据库
$db = \sower\Loader::database();
//sower支持数据库重连
//是否 数据库对象超时失效 重新连接[默认true]
$db->isReconnect = true;
//是否 数据库连接失败尝试 重新连接[默认true]
$db->isAReconnect = true;
//数据库拒绝连接(2002错误)持续尝试连接[默认尝试重新连接间隔为180秒,设置为0不尝试重连,非CLI模式下无效]
$db->{$RCSleepTime} = 180;
//$db读写是分离的方法
//查询:$db->read($sql);
//插入或更新或者删除:$db->write($sql);
//查询
$result = $db->read($sql);
var_dump($result);