mysql问题积累示例

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

select * from drqq where advice_id is null;

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

update drqq setadvice_id=null where id='9';

3.mysql查询不为null的数据

select * from drlistwhere advice_id is not null;

4.mysql的单行注释用#

5.mysql将null转换为0

selectname,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 viewwork;//删除视图

create view workas 

selectadvice.name,advice.relation,advice.type,advice.dltstate,drqq.dqq,patient.visitstatefrom 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) aspsum,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;