这篇博文主要是对我自己用过的sql语句进行展示,然后不断完善,形成自己写sql语句的风格,并提高自己sql语句的运行速度。之前自己对左连接等一系列的sql语句都不是很熟悉,经过这几天的学习和项目中实际需求的使用,学会了一些sql语句。现在将我写的sql语句展示出来。主要分为两类,一类是通过yii框架里的sql语句写法,一类是正常的sql语句。
先说正常的sql语句吧。
1.首先是多表查询,先涉及到的是join方法,下面这条连接,有几个点需要注意,一个是group by的使用,group by的话,表示的是按照后面的那个关键字排序,如果遇到重复的,就会只取第一条信息。
还有如果在很多and的条件里面,有需要或的关系的话,可以向下面这个方法一样,使用括号括起来,然后写上or的语句,
还有要注意,多表查询的话,一定要注意两张表示靠什么关键字段连接的,如下面的sql语句,os.order_id = o.id 这个就是他们连接的依据。
SELECT os.city_id, o.id as order_id, o.c_id
From order_social_detail os JOIN order o
WHERE os.order_id = o.id
AND o.pay_status = 0
AND (social_type = 1 OR social_type = 5 OR social_type = 8)
GROUP BY o.id
2.说道join的方法的话,就一定得提left join 这种左连接了,以左边的表为主表,也就是前面的表。
如下面的sql语句,其本质和join是一样的,他的条件是用ON这个关键字然后加上 us.city_id = cd.area_id两种表关联的属性相连接的。
然后还可要注意的是,这个地方用了IN这个关键字,表示你搜索的东西在你的括号包含的东西里面。
SELECT cd.deadline_day FROM {{user_socialmanage}} us
LEFT JOIN {{city_data}} cd
ON us.city_id = cd.area_id
WHERE us.is_delete = 0
AND us.social_security_status IN ('0,1,3')
AND us.c_id = {$c_id}
GROUP BY us.city_name
3.下面这个sql语句主要说明是不等号的使用方式,还有小于等于的方式。
SELECT * FROM jy_order_social_detail
WHERE last_feedback_content != ''
AND c_id = {$c_id} and is_delete = 0
AND service_status <= 4
4.现在要讲的是,获取数据库表里面存的数据不为空的信息,用is not null这个关键词,如果是要为空的话,用is null。就是因为这个原因,所以数据最好不要存为null,避免歧义。
SELECT city_id From user_socialmanage
where (social_security_current_month is not null)
and social_security_status = 1
and is_delete = 0
5.count()的使用。下面的就是最基本的count()的使用方式了。
SELECT count(*) From user_socialmanage
where (social_security_status=0 or social_security_status=1 or social_security_status=3)
6.如果在yii框架里面,如果你想在sql语句中加入变量的话,可以采用分离,然后用. 点的方式来连接,也可以直接在sql语句中,用大括号将其变量包起来。
public function getServiceStatusByCid($params) {
//传进来的$parmas是数组,包括$service_status和c_id
$c_id = $params['c_id'];
$service_status = $params['service_status'];
$sql = "SELECT id From {{order_social_detail}} where service_status = {$service_status} and c_id = {$c_id} and is_delete = 0";
$command = Yii::app()->db->createCommand($sql);
$service_status_data = $command->queryAll();
//采用 . 的方式分离,这个要1注意的是,sql语句都要用双引号哦。
$sql = "SELECT id From {{order_social_detail}} where service_status = ". $service_status ." and c_id = ". $c_id ." and is_delete = 0";
$command = Yii::app()->db->createCommand($sql);
$service_status_data = $command->queryAll();
return $service_status_data;
}
7.在使用model层里面的searchs的方法的时候,要通过以下的方法进行sql语句里面的参数进行赋值。
$command = Yii::app()->db->createCommand($sql);
if ($where ['bind_value']) {
foreach ( $where ['bind_value'] as $key => $val ) {
$command->bindValue ( $key, $val ['value'], $val ['type'] );
}
}
$data = $command->queryAll();
return $data;
8.对时间进行格式化,比如在数据库里面存的日期为2015-10-02 00:00:00,这样datetime的形式,然后我只需要年月日的方式或者其他类型的方式的时候,可以使用DATE_FORMAT这个方法对时间进行优化。
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')
9.取当前时间比数据库里面存的时间大15天的情况的时候,sql语句有两种写法,一种是
SELECT * from jy_order_social_detail WHERE datediff(handle_time, date(NOW()))<10
另外一种
((unix_timestamp() - unix_timestamp(handle_time))/86400)>15
10.如果使用getwhere方法对sql语句进行组装的话,要小心in的操作,是in的话,就通过拼接进行组装
if (isset($condition['id_array'])) {
$where .= " AND o.id in (".$condition['id_array'].")";
}
- 关于排序的几个问题
1).排序的时候,先将不为空的取出,然后再对剩下的值进行升序排序
CASE WHEN IFNULL(times, '') = '' THEN 0 ELSE 1 END DESC, times ASC,
2) 通过判断是否有值,如果有值的排在前面
CASE WHEN o.last_feedback_content IS NULL THEN 1 ELSE 0
- LPAD这个方法是如果cd.deadline_day的值小于 2 位的话,自动加上一个 0
LPAD(cd.deadline_day, 2, "0")
13.这个就是将两个值拼接在一起
CONCAT(time, '-15')
8.在表里面插入一个新的字段
ALTER TABLE jy_sequence ADD start_time DATETIME
9.初始化字段为当前时间
UPDATE jy_sequence SET end_time = NOW()