StartMVC开发手册

可以快速上手的开发文档

手册目录

数据库操作（增删改查）

Startmvc支持通过“连贯操作”方式增/删/改/查数据，也支持原生sql语句操作。

操作数据库前，请配置好数据库信息config/database.php

数据查询select

# 示例1: 字符参数

$db->select('title, content')->table('test')->getAll();
# 输出: "SELECT title, content FROM test"
$db->select('title AS t, content AS c')->table('test')->getAll();
# 输出: "SELECT title AS t, content AS c FROM test"

# 示例2: 数组参数

$db->select(['title', 'content'])->table('test')->getAll();
# 输出: "SELECT title, content FROM test"
$db->select(['title AS t', 'content AS c'])->table('test')->getAll();
# 输出: "SELECT title AS t, content AS c FROM test"

查询功能select(min-最小, max-最大, sum-求和, avg-取平均, count-统计)

# 示例1:

$db->table('test')->max('price')->get();
# 输出: "SELECT MAX(price) FROM test"
//{ ["MAX(id)"]=> string(1) "4" }

# 示例2:

$db->table('test')->count('id', 'total_row')->get();
# 输出: "SELECT COUNT(id) AS total_row FROM test"

数据表table

### 用法1: 字符参数

$db->table('table');
# 输出: "SELECT * FROM table"
$db->table('table1, table2');
# 输出: "SELECT * FROM table1, table2"
$db->table('table1 AS t1, table2 AS t2');
# 输出: "SELECT * FROM table1 AS t1, table2 AS t2"

### 用法2: 数组参数

$db->table(['table1', 'table2']);
# 输出: "SELECT * FROM table1, table2"
$db->table(['table1 AS t1', 'table2 AS t2']);
# 输出: "SELECT * FROM table1 AS t1, table2 AS t2"

查询结果get/getValue和getAll

# get(): 返回一条记录，默认返回数组类型结果

# getValue(): 返回单条记录的字段值

# getAll(): 返回多条记录

get($type = null, $argument = null)

getValue($field) //$field为字段名

getAll($type = null, $argument = null)

$type=null 返回结果；$type=true 返回sql语句，方便调试用；

$argument=null 或 "array" 返回数组结果，$argument="obj" 返回对象结果，

$db->table('test')->getAll(); 
# 输出: "SELECT * FROM test"
$db->select('username')->table('users')->where('status', 1)->getAll();
# 输出: "SELECT username FROM users WHERE status='1'"
$db->select('title')->table('pages')->where('id', 17)->get(); 
# 输出: "SELECT title FROM pages WHERE id='17' LIMIT 1"

联合查询join

$db->table('test as t')->join('foo as f', 't.id', 'f.t_id')->where('t.status', 1)->getAll();
# 输出: "SELECT * FROM test as t JOIN foo as f ON t.id=f.t_id WHERE t.status='1'"
$this->db->table('article as a')->join('article_content as ac','a.id','ac.article_id')->select('a.id,a.title,a.keywords,a.description,a.update_time,ac.content')->where('a.id',$id)->get();

你可以使用以下7种方法：

  • join
  • left_join
  • right_join
  • inner_join
  • full_outer_join
  • left_outer_join
  • right_outer_join

用法:

$db->table('test as t')->leftJoin('foo as f', 't.id', 'f.t_id')->getAll();
# 输出: "SELECT * FROM test as t LEFT JOIN foo as f ON t.id=f.t_id"
$db->table('test as t')->fullOuterJoin('foo as f', 't.id', 'f.t_id')->getAll();
# 输出: "SELECT * FROM test as t FULL OUTER JOIN foo as f ON t.id=f.t_id"


条件设定where

$where = [
'name' => '小明',
'age' => 23,
'status' => 1
];
$db->table('test')->where($where)->get();
# 输出: "SELECT * FROM test WHERE name='小明' AND age='23' AND status='1' LIMIT 1"

# OR

$db->table('test')->where('active', 1)->getAll();
# 输出: "SELECT * FROM test WHERE active='1'"

# OR

$db->table('test')->where('age', '>=', 18)->getAll();
# 输出: "SELECT * FROM test WHERE age>='18'"

# 或OR

$db->table('test')->where('age = ? OR age = ?', [18, 20])->getAll();
# 输出: "SELECT * FROM test WHERE age='18' OR age='20'"

条件设定有以下4种方法使用;

  • where
  • orWhere
  • notWhere
  • orNotWhere
  • whereNull
  • whereNotNull

样本:

$db->table('test')->where('active', 1)->notWhere('auth', 1)->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND NOT auth = '1'"
# 或OR
$db->table('test')->where('age', 20)->orWhere('age', '>', 25)->getAll();
# 输出: "SELECT * FROM test WHERE age = '20' OR age > '25'"
$db->table('test')->whereNotNull('email')->getAll();
# 输出: "SELECT * FROM test WHERE email IS NOT NULL"

组合条件grouped

$db->table('users')
  ->grouped(function($q) {
    $q->where('country', 'TURKEY')->orWhere('country', 'ENGLAND');
})
->where('status', 1)
->getAll();
# 输出: "SELECT * FROM users WHERE (country='TURKEY' OR country='ENGLAND') AND status ='1'"

条件in

$db->table('test')->where('active', 1)->in('id', [1, 2, 3])->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND id IN ('1', '2', '3')"

有以下4个方法可以使用：

  • in
  • orIn
  • notIn
  • orNotIn

样本:

$db->table('test')->where('active', 1)->notIn('id', [1, 2, 3])->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND id NOT IN ('1', '2', '3')"
# 或OR
$db->table('test')->where('active', 1)->orIn('id', [1, 2, 3])->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' OR id IN ('1', '2', '3')"

findInSet

 $db->table('test')->where('active', 1)->findInSet('selected_ids', 1)->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND FIND_IN_SET (1, selected_ids)"

有4种方法你可以使用，如下：

  • findInSet
  • orFindInSet
  • notFindInSet
  • orNotFindInSet

Example:

$db->table('test')->where('active', 1)->notFindInSet('selected_ids', 1)->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND NOT FIND_IN_SET (1, selected_ids)"
# 或
$db->table('test')->where('active', 1)->orFindInSet('selected_ids', 1)->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' OR FIND_IN_SET (1, selected_ids)"


范围between

$db->table('test')->where('active', 1)->between('age', 18, 25)->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND age BETWEEN '18' AND '25'"

有以下4个方法可以使用：

between

orBetween

notBetween

orNotBetween

样本:

$db->table('test')->where('active', 1)->notBetween('age', 18, 25)->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND age NOT BETWEEN '18' AND '25'"
# 或OR
$db->table('test')->where('active', 1)->orBetween('age', 18, 25)->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' OR age BETWEEN '18' AND '25'"


模糊查询like

$db->table('test')->like('title', "%php%")->getAll();
# 输出: "SELECT * FROM test WHERE title LIKE '%php%'"

有以下4个方法可以使用：

like

orLike

notLike

orNotLike

样本:

$db->table('test')->where('active', 1)->notLike('tags', '%dot-net%')->getAll();
# 输出: "SELECT * FROM test WHERE active = '1' AND tags NOT LIKE '%dot-net%'"
# OR
$db->table('test')->like('bio', '%php%')->orLike('bio', '%java%')->getAll();
# 输出: "SELECT * FROM test WHERE bio LIKE '%php%' OR bio LIKE '%java%'"


分组查询groupBy

# 用法 1: 一个参数

$db->table('test')->where('status', 1)->groupBy('cat_id')->getAll();
# 输出: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id"

# 用法 1: 数组参数

$db->table('test')->where('status', 1)->groupBy(['cat_id', 'user_id'])->getAll();
# 输出: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id, user_id"


过滤分组having

$db->table('test')->where('status', 1)->groupBy('city')->having('COUNT(person)', 100)->getAll();
# 输出: "SELECT * FROM test WHERE status='1' GROUP BY city HAVING COUNT(person) > '100'"

# OR

$db->table('test')->where('active', 1)->groupBy('department_id')->having('AVG(salary)', '<=', 500)->getAll();
# 输出: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) <= '500'"

# OR

$db->table('test')->where('active', 1)->groupBy('department_id')->having('AVG(salary) > ? AND MAX(salary) < ?', [250, 1000])->getAll();
# 输出: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) > '250' AND MAX(salary) < '1000'"


排序orderBy

# 用法1: 单个参数

$db->table('test')->where('status', 1)->orderBy('id')->getAll();
# 输出: "SELECT * FROM test WHERE status='1' ORDER BY id ASC"
### OR
$db->table('test')->where('status', 1)->orderBy('id desc')->getAll();
# 输出: "SELECT * FROM test WHERE status='1' ORDER BY id desc"

# 用法1: 两个参数

$db->table('test')->where('status', 1)->orderBy('id', 'desc')->getAll();
# 输出: "SELECT * FROM test WHERE status='1' ORDER BY id DESC"

# 用法3: 随机Rand()

$db->table('test')->where('status', 1)->orderBy('rand()')->limit(10)->getAll();
# 输出: "SELECT * FROM test WHERE status='1' ORDER BY rand() LIMIT 10"


截取limit/偏移量offset

# 用法1: 一个参数

$db->table('test')->limit(10)->getAll();
# 输出: "SELECT * FROM test LIMIT 10"

# 用法2: 两个参数

$db->table('test')->limit(10, 20)->getAll();
# 输出: "SELECT * FROM test LIMIT 10, 20"

# 用法3: offset方法

$db->table('test')->limit(10)->offset(10)->getAll();
# 输出: "SELECT * FROM test LIMIT 10 OFFSET 10"


分页pagination

# 参数1: 每页数量

# 参数2: 当前页

$db->table('test')->pagination(15, 1)->getAll();
# 输出: "SELECT * FROM test LIMIT 15 OFFSET 0"
$db->table('test')->pagination(15, 2)->getAll();
# 输出: "SELECT * FROM test LIMIT 15 OFFSET 15"


添加insert

insert(array $data, $type = false)

默认返回最后插入的id号, 如果$type=true, 不插入数据,只返回sql语句,方便调试用.  

单条数据插入

$data = [
  'title' => 'test',
  'content' => '这是内容数据...',
  'time' => '2019-05-19 19:05:00',
  'status' => 1
];
$db->table('pages')->insert($data);
# 输出: "INSERT INTO test (title, content, time, status) VALUES ('test', '这是内容数据...', '2019-05-19 19:05:00', '1')"

返回值为新插入的id号

多条数据插入

$data = [
	[
 'title' => '这是标题2',
 'content' => '这是内容数据2...',
 'time' => time(),
 'status' => 1
	],
	[
 'title' => '这是标题2',
 'content' => '这是内容数据2...',
 'time' => time(),
 'status' => 1
	],
	[
 'title' => '这是标题3',
 'content' => '这是内容数据3...',
 'time' => time(),
 'status' => 1
	],


];
$this->db->table('article')->insert($data);

更新update

update(array $data, $type = false)

默认返回影响条数, 如果$type=true, 不更新数据,只返回sql语句,方便调试用.  

$data = [
'username' => 'xiaoming',
'password' => 'pass',
'activation' => 1,
'status' => 1
];
$db->table('users')->where('id', 10)->update($data);
# 输出: "UPDATE users SET username='xiaoming', password='pass', activation='1', status='1' WHERE id='10'"

删除delete

delete($type = false)

默认返回影响条数, 如果$type=true, 不更新数据,只返回sql语句,方便调试用.  

$db->table('test')->where("id", 17)->delete();
# 输出: "DELETE FROM test WHERE id = '17'"
//返回影响条数

# OR

$db->table('test')->delete();
# 输出: "TRUNCATE TABLE delete"

更新时间：2021-12-11 10:39:41