CI数据库参考-查询构造器类

客官°小女子只卖身不卖艺 2022-03-27 14:29 544阅读 0赞

## **一、查询** ##

下面的方法用来构建 **SELECT** 语句。

**1、$this->db->get()**

该方法执行 SELECT 语句并返回查询结果，可以得到一个表的所有数据，查询会被执行并被重置

$query = $this->db->get('mytable');  // Produces: SELECT * FROM mytable

第二和第三个参数用于设置 LIMIT 子句:

$query = $this->db->get('mytable', 10, 20);
    
    // Executes: SELECT * FROM mytable LIMIT 20, 10

结果都赋值给了一个 $query 变量，通过这个变量， 我们可以得到查询的结果

**2、$this->db->get\_where()**

这个get（）方法一样，但它提供了第二个参数可以让你添加一个 WHERE 子句， 而不是使用 db->where() 方法:

第二参数条件需要是数组格式

$query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset);

**3、$this->db->select()**

该方法用于编写查询语句中的 SELECT 子句:

$this->db->select('title, content, date');//测试发现传数组也可以
    $query = $this->db->get('mytable');
    
    // Executes: SELECT title, content, date FROM mytable

注：如果你要查询表的所有列，可以不用写这个函数，CodeIgniter 会自动查询所有列（SELECT \*）。

$this->db->select() 方法的第二个参数可选，如果设置为 FALSE，CodeIgniter 将不保护你的 表名和字段名，这在当你编写复合查询语句时很有用，不会破坏你编写的语句。

$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
    $query = $this->db->get('mytable');

**4、$this->db->select\_max()**

该方法用于编写查询语句中的 SELECT MAX(field) 部分，你可以使用第二个参数（可选）重命名结果字段。

$this->db->select_max('age');
    $query = $this->db->get('members');  // Produces: SELECT MAX(age) as age FROM members
    
    $this->db->select_max('age', 'member_age');
    $query = $this->db->get('members'); // Produces: SELECT MAX(age) as member_age FROM members

**  $this->db->select\_min()**

**  $this->db->select\_avg()**

**  $this->db->select\_sum()**

**5、$this->db->from()**

该方法用于编写查询语句中的 FROM 子句:

from后面接get或者只有get但在get里指定from表

$this->db->select('title, content, date');
    $this->db->from('mytable');
    $query = $this->db->get();  // Produces: SELECT title, content, date FROM mytable
    
    正如前面所说，查询中的 FROM 部分可以在方法 $this->db->get() 中指定，所以，你可以 选择任意一种你喜欢的方式。

**6、$this->db->join()**

该方法用于编写查询语句中的 JOIN 子句:

join后面还可以加where条件

$this->db->select('*');
    $this->db->from('blogs');
    $this->db->join('comments', 'comments.id = blogs.id');
    $query = $this->db->get();
    
    // Produces:
    // SELECT * FROM blogs JOIN comments ON comments.id = blogs.id

如果你的查询中有多个连接，你可以多次调用这个方法。

你可以传入第三个参数指定连接的类型，有这样几种选择：left，right，outer，inner，left outer 和 right outer 。

$this->db->join('comments', 'comments.id = blogs.id', 'left');
    // Produces: LEFT JOIN comments ON comments.id = blogs.id

## 二、搜索 ##

**1、$this->db->where()**

该方法提供了4中方式让你编写查询语句中的 WHERE 子句：

1.  **简单的 key/value 方式:**
    
    >     $this->db->where('name', $name); // Produces: WHERE name = 'Joe'
    > 
    > 注意自动为你加上了等号。
    > 
    > 如果你多次调用该方法，那么多个 WHERE 条件将会使用 AND 连接起来：
    > 
    >     $this->db->where('name', $name);
    >     $this->db->where('title', $title);
    >     $this->db->where('status', $status);
    >     // WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
2.  **自定义 key/value 方式:**
    
    > 为了控制比较，你可以在第一个参数中包含一个比较运算符：
    > 
    >     $this->db->where('name !=', $name);
    >     $this->db->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45
3.  **关联数组方式:**
    
    >     $array = array('name' => $name, 'title' => $title, 'status' => $status);
    >     $this->db->where($array);
    >     // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
    > 
    > 你也可以在这个方法里包含你自己的比较运算符：
    > 
    >     $array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);
    >     $this->db->where($array);
4.  **自定义字符串:**
    
    > 你可以完全手工编写 WHERE 子句:
    > 
    >     $where = "name='Joe' AND status='boss' OR status='active'";
    >     $this->db->where($where);

**     $this->db->or\_where()**

这个方法和上面的方法一样，只是多个 WHERE 条件之间使用 OR 进行连接:

$this->db->where('name !=', $name);
       $this->db->or_where('id >', $id);  // Produces: WHERE name != 'Joe' OR id > 50

**2、$this->db->where\_in()**

注意in的第二参数要是数组数据

该方法用于生成 WHERE IN 子句，多个子句之间使用 AND 连接

$names = array('Frank', 'Todd', 'James');
    $this->db->where_in('username', $names);
    // Produces: WHERE username IN ('Frank', 'Todd', 'James')

**$this->db->or\_where\_in()**

该方法用于生成 WHERE IN 子句，多个子句之间使用 OR 连接

$names = array('Frank', 'Todd', 'James');
    $this->db->or_where_in('username', $names);
    // Produces: OR username IN ('Frank', 'Todd', 'James')

**$this->db->where\_not\_in()**

该方法用于生成 WHERE NOT IN 子句，多个子句之间使用 AND 连接

$names = array('Frank', 'Todd', 'James');
    $this->db->where_not_in('username', $names);
    // Produces: WHERE username NOT IN ('Frank', 'Todd', 'James')

**$this->db->or\_where\_not\_in()**

该方法用于生成 WHERE NOT IN 子句，多个子句之间使用 OR 连接

$names = array('Frank', 'Todd', 'James');
    $this->db->or_where_not_in('username', $names);
    // Produces: OR username NOT IN ('Frank', 'Todd', 'James')

## 三、模糊搜索 ##

**1、$this->db->like()**

该方法用于生成 LIKE 子句，在进行搜索时非常有用。

1.  **简单 key/value 方式:**
    
    >     $this->db->like('title', 'match');
    >     // Produces: WHERE `title` LIKE '%match%' ESCAPE '!'
    > 
    > 如果你多次调用该方法，那么多个 WHERE 条件将会使用 AND 连接起来:
    > 
    >     $this->db->like('title', 'match');
    >     $this->db->like('body', 'match');
    >     // WHERE `title` LIKE '%match%' ESCAPE '!' AND  `body` LIKE '%match% ESCAPE '!'
    > 
    > 可以传入第三个可选的参数来控制 LIKE 通配符（%）的位置，可用选项有：'before'，'after' 和 'both' (默认为 'both')。
    > 
    >     $this->db->like('title', 'match', 'before');    // Produces: WHERE `title` LIKE '%match' ESCAPE '!'
    >     $this->db->like('title', 'match', 'after'); // Produces: WHERE `title` LIKE 'match%' ESCAPE '!'
    >     $this->db->like('title', 'match', 'both');  // Produces: WHERE `title` LIKE '%match%' ESCAPE '!'
2.  **关联数组方式:**
    
    >     $array = array('title' => $match, 'page1' => $match, 'page2' => $match);
    >     $this->db->like($array);
    >     // WHERE `title` LIKE '%match%' ESCAPE '!' AND  `page1` LIKE '%match%' ESCAPE '!' AND  `page2` LIKE '%match%' ESCAPE '!'

**$this->db->or\_like()**

这个方法和上面的方法一样，只是多个 WHERE 条件之间使用 OR 进行连接:

$this->db->like('title', 'match'); $this->db->or_like('body', $match);
    // WHERE `title` LIKE '%match%' ESCAPE '!' OR  `body` LIKE '%match%' ESCAPE '!'

**$this->db->not\_like()**

这个方法和 like() 方法一样，只是生成 NOT LIKE 子句:

$this->db->not_like('title', 'match');  // WHERE `title` NOT LIKE '%match% ESCAPE '!'

**$this->db->or\_not\_like()**

这个方法和 not\_like() 方法一样，只是多个 WHERE 条件之间使用 OR 进行连接:

$this->db->like('title', 'match');
    $this->db->or_not_like('body', 'match');
    // WHERE `title` LIKE '%match% OR  `body` NOT LIKE '%match%' ESCAPE '!'

**2、$this->db->group\_by()**

该方法用于生成 GROUP BY 子句:

$this->db->group_by("title"); // Produces: GROUP BY title

你也可以通过一个数组传入多个值:

$this->db->group_by(array("title", "date"));  // Produces: GROUP BY title, date

**3、$this->db->having()**

该方法用于生成 HAVING 子句，有下面两种不同的语法:

$this->db->having('user_id = 45');  // Produces: HAVING user_id = 45
    $this->db->having('user_id',  45);  // Produces: HAVING user_id = 45

你也可以通过一个数组传入多个值:

$this->db->having(array('title =' => 'My Title', 'id <' => $id));
    // Produces: HAVING title = 'My Title', id < 45

如果 CodeIgniter 自动转义你的查询，为了避免转义，你可以将第三个参数设置为 FALSE 。

$this->db->having('user_id',  45);  // Produces: HAVING `user_id` = 45 in some databases such as MySQL
    $this->db->having('user_id',  45, FALSE);  // Produces: HAVING user_id = 45

**$this->db->or\_having()**

该方法和 having() 方法一样，只是多个条件之间使用 OR 进行连接。

**4、$this->db->distinct()**

该方法用于向查询中添加 DISTINCT 关键字：

$this->db->distinct();
    $this->db->get('table'); // Produces: SELECT DISTINCT * FROM table

## ![20190122103721214.png][] ##

## **四、排序** ##

**1、$this->db->order\_by()**

该方法用于生成 ORDER BY 子句。

第一个参数为你想要排序的字段名，第二个参数用于设置排序的方向， 可选项有： ASC（升序），DESC（降序）和 RANDOM （随机）。

$this->db->order_by('title', 'DESC');
    // Produces: ORDER BY `title` DESC

第一个参数也可以是你自己的排序字符串:

$this->db->order_by('title DESC, name ASC');
    // Produces: ORDER BY `title` DESC, `name` ASC

如果需要根据多个字段进行排序，可以多次调用该方法。

$this->db->order_by('title', 'DESC');
    $this->db->order_by('name', 'ASC');
    // Produces: ORDER BY `title` DESC, `name` ASC

## **五、分页与计数** ##

**1、$this->db->limit()**

该方法用于限制你的查询返回结果的数量:

$this->db->limit(10)  // Produces: LIMIT 10

第二个参数可以用来设置偏移。

// Produces: LIMIT 20, 10 (in MySQL.  Other databases have slightly different syntax)
    $this->db->limit(10, 20);

**2、$this->db->count\_all\_results()**

该方法用于获取特定查询返回结果的数量，也可以使用查询构造器的这些方法： where()，or\_where()，like()，or\_like() 等等。举例:

echo $this->db->count_all_results('my_table');  // Produces an integer, like 25
    $this->db->like('title', 'match');
    $this->db->from('my_table');
    echo $this->db->count_all_results(); // Produces an integer, like 17

## 七、插入数据 ##

**1、$this->db->insert()**

插入一条数据，第一个参数为要插入的表名，第二个参数为要插入的数据，是个关联数组。

该方法根据你提供的数据生成一条 INSERT 语句并执行，它的参数是一个\*\*数组\*\* 或一个\*\*对象\*\*，下面是使用数组的例子:

$data = array(
        'title' => 'My title',
        'name' => 'My Name',
        'date' => 'My date'
    );
    
    $this->db->insert('mytable', $data);
    // Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

**2、$this->db->insert\_batch()**

使用CI框架的AR操作：insert\_batch()批量插入可以减少访问数据库的次数。一次访问即可。

插入多条数据（字段名要一致），第一个参数为要插入的表名，第二个参数为要插入的数据，是个二维数组。

$data = array(
        array(
            'title' => 'My title',
            'name' => 'My Name',
            'date' => 'My date'
        ),
        array(
            'title' => 'Another title',
            'name' => 'Another Name',
            'date' => 'Another date'
        )
    );
    
    $this->db->insert_batch('mytable', $data);
    // Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'),  ('Another title', 'Another name', 'Another date')

## 八、更新数据 ##

**1、$this->db->update()**

第一参数表名；第二参数更新数据，数组格式；第三参数可选字符串或数组条件

该方法根据你提供的数据生成一条 UPDATE 语句并执行，它的参数是一个 **数组** 或一个 **对象** ，下面是使用数组的例子:

$data = array(
        'title' => $title,
        'name' => $name,
        'date' => $date
    );
    
    $this->db->where('id', $id);
    $this->db->update('mytable', $data);
    // Produces:
    //
    //  UPDATE mytable
    //  SET title = '{$title}', name = '{$name}', date = '{$date}'
    //  WHERE id = $id

你应该注意到 $this->db->where() 方法的使用，它可以为你设置 WHERE 子句。 你也可以直接使用字符串形式设置 WHERE 子句:

$this->db->update('mytable', $data, "id = 4");

或者使用一个数组:

$this->db->update('mytable', $data, array('id' => $id));

**2、$this->db->update\_batch()**

其实就是将update ...set name='name1' where title=‘My title’

update ...set name='name2' where title='Another title'

多次更新写成一条sql语句。

该方法根据你提供的数据生成一条 UPDATE 语句并执行，它的参数是一个\*\*数组\*\* 或一个\*\*对象\*\*，下面是使用数组的例子:

$data = array(
       array(
          'title' => 'My title' ,
          'name' => 'My Name 2' ,
          'date' => 'My date 2'
       ),
       array(
          'title' => 'Another title' ,
          'name' => 'Another Name 2' ,
          'date' => 'Another date 2'
       )
    );
    //设置相同字段时（多维数组元素键名一致），条件不同时，将多条update...where利用一条sql实现
    总条件WHERE `title` IN ('My title','Another title')，根据不同的title值，设置相同字段不同的数据值
    * $data  二维数组
    * $key where语句的键，二维数组元素的某个键名，其键值组合为数组作为where in（）的数据，
    生成case when then else end 语句
    
    $this->db->update_batch('mytable', $data, 'title');
    
    // Produces:
    // UPDATE `mytable` SET `name` = CASE
    // WHEN `title` = 'My title' THEN 'My Name 2'
    // WHEN `title` = 'Another title' THEN 'Another Name 2'
    // ELSE `name` END,
    // `date` = CASE
    // WHEN `title` = 'My title' THEN 'My date 2'
    // WHEN `title` = 'Another title' THEN 'Another date 2'
    // ELSE `date` END
    // WHERE `title` IN ('My title','Another title')

第一个参数为要更新的表名，第二个参数为要更新的数据，是个二维数组，第三个 参数是 WHERE 语句的键。

取决于该方法的内部实现，在这个方法之后调用 affected\_rows() 方法 返回的结果可能会不正确。但是你可以直接使用该方法的返回值，代表了受影响的行数。

## 九、删除数据 ##

**1、$this->db->delete()**

该方法生成 DELETE 语句并执行。

$this->db->delete('mytable', array('id' => $id));  // Produces: DELETE FROM mytable WHERE id = $id

第一个参数为表名，第二个参数为 WHERE 条件。你也可以不用第二个参数， 使用 where() 或者 or\_where() 函数来替代它:

$this->db->where('id', $id);
    $this->db->delete('mytable');
    
    // Produces: DELETE FROM mytable WHERE id = $id

如果你想要从多个表中删除数据，你也可以将由多个表名构成的数组传给 delete() 方法。

$tables = array('table1', 'table2', 'table3');
    $this->db->where('id', '5');
    $this->db->delete($tables);

如果你想要删除一个表中的所有数据，可以使用 truncate() 或 empty\_table() 方法。

## N、重置查询构造器 ##