常用sql语句

shw databases 查看所有数据库

show tables 查看数据表

create database[库名] 创建库

use [库名] 打开数据库

describe 表名 查看表结构

1、搜索  false可以写原生sql作为条件

$this->db->get('表名');

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

select('title, content, date'); 字段以，分割，

 

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

 

result()结果为二维数据

row_array() 结果为一维数组

2、聚合 最大值，最小值，平均值

$this->db->select_max('age');最大值

 SELECT MAX(age) as age FROM members，

$this->db->select_min('age'); 最小值
$query = $this->db->get('members');
 SELECT MIN(age) as age FROM members

$this->db->select_avg('age');
$query = $this->db->get('members');
// 產生： SELECT AVG(age) as age FROM members

 

3、连接  json，可以有多种连接方式 left、right、outer、inner、left outer 以及 right outer 

 

$this->db->join('comments', 'comments.id = blogs.id', 'left');

LEFT JOIN comments ON comments.id = blogs.id

 

4、条件where 可以多条并列，数组形式，键值对形式

 $this->db->where('name', $name);

$this->db->where('title', $title);

 

$this->db->where('status', $status); 

 

$array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);
 

$this->db->where($array);

$where = "name='Joe' AND status='boss' OR status='active'";
 

$this->db->where($where); 

where    IN,OR 条件查询

$names = array('Frank', 'Todd', 'James');
$this->db->where_in('username', $names);

 WHERE username IN ('Frank', 'Todd', 'James')

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

5、分页：

$this->db->get('表名', 10, 2);

$this->db->limit(10, 2);

select * from refund_msg limit 10 offset 2; 每页10条，从第2条开始

select * from refund_msg limit 2,10; 每页2条，从第10条开始

6、分组 group_by ,distinct, 排序order_by，条数count_all_results，count_all

$this->db->group_by(array("title", "date")); 

 GROUP BY title, date

$this->db->distinct();
$this->db->get('table');

SELECT DISTINCT * FROM table

$this->db->order_by("title", "desc");
$this->db->order_by("name", "asc"); 

ORDER BY title DESC, name ASC

$this->db->like('title', 'match');
$this->db->from('my_table');
echo $this->db->count_all_results();

7、插入，更新，删除

$this->db->set('field', 'field+1', FALSE);

$this->db->insert('mytable'); 

$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );

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

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

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

8、模糊查询  before，after,bath控制%的位置，默认bath不用写

$this->db->like('title', 'match', 'before'); 
 WHERE title LIKE '%match'

$this->db->like('title', 'match', 'after'); 
 WHERE title LIKE 'match%' 

$this->db->like('title', 'match', 'both'); 
 WHERE title LIKE '%match%'