mysql操作语句总结

        项目中经常用到sql语句进行数据增删查改操作，下面总结了一下：

比如 查找指定客户最近订单

根据id查询按日期降序排序取第一条。（降序排序取第一条）

SELECT * FROM t_customer_order WHERE cusId=#{cusId} ORDER BY orderdate DESC LIMIT 0,1;

 

 查询存在一个表而不在另一个表中的数据记录

select A.ID from A where A.ID not in (select ID from B)

 

查询某个字段为null的记录

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

 

SELECT
　　excute.i_id as id,
　　excute.i_plan_drill_id as drillId,
　　excute.i_status as status,
　　info.c_name as planName,
　　excute.c_alarm_source_name as alarmSourceName,
　　excute.c_alarm_event_name as alarmEventname,
　　excute.c_alarm_area as alarmArea,
　　excute.c_creator as creator,
　　excute.d_create_time as createTime
FROM
　　plan_excute excute,
　　plan_info info
WHERE
　　excute.i_plan_id = info.i_id
AND
　　excute.i_plan_drill_id is null
ORDER BY

    excute.d_create_time desc

 

  查询客户订单总金额（牵涉到多表关联查询）

这里牵涉到三个表，t_customer （客户表），t_customer_order（客户订单表），t_order_details（客户订单详情表），需用到左连接查询和分组查询

select  t1.name,SUM(t3.sum) as gx  from t_customer  t1 left join t_customer_order t2 on t1.id = t2.cusId left join t_order_details t3 on t2.id= t3.orderId group by t1.id

1、先左连接查询出所有关联记录

2、再根据字段分组查询所有记录

3、最后查询只关心的字段记录