mysql问题积累示例

1.mysql 查询字段为空用is null。

select * from drqq where advice_id is null;

2.修改mysql的字段为空用null

update drqq set advice_id=null where id='9';

3.mysql查询不为null的数据

select * from drlist where advice_id is not null;

4.mysql的单行注释用#

5.mysql将null转换为0

select name,ifnull(sum(visitstate=0),0) as pv0 from work

6.mysql将null转换掉

 model.Dqq = Convert.IsDBNull(dr["dqq"]).ToString();

  model.Visitstate = Convert.ToInt32(Convert.IsDBNull(dr["visitstate"]));   //去除null

7.mysql创建多表视图

drop view work;//删除视图

create view work as 

select advice.name,advice.relation,advice.type,advice.dltstate,drqq.dqq,patient.visitstate from advice

left join drqq on drqq.advice_id=advice.id

left join patient on patient.dr_id=drqq.id;//创建视图

8.mysql视图查询

select name,count(distinct dqq) as dsum,count(visitstate) as psum,ifnull(sum(visitstate=0),0) as pv0,ifnull(sum(visitstate=1),0) as pv1 from work

where relation=3 and type=4 and dltstate=1

group by name;